IF EXISTS( SELECT  * FROM  SYS.OBJECTS WHERE NAME = 'UFD_RC_ADM_CARTAO_VENDA_DEBITO_PARC')
    BEGIN
        DROP FUNCTION  UFD_RC_ADM_CARTAO_VENDA_DEBITO_PARC
    END;
GO
GO
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_VENDA_DEBITO_PARC](@CD_EMP    INT
															  , @CD_FILIAL VARCHAR(1500)
															  , @CD_CLI    VARCHAR(1500)
															  , @DT_INI    DATETIME
															  , @DT_FIM    DATETIME
															  , @tpData		tinyint
															  , @StsMov		tinyint)

RETURNS @VENDAS_DEB TABLE
                          (CD_FILIAL          INT
                        , CD_CLI             INT
                        , RZ_CLI             VARCHAR(225)
                        , DT_VD_ADM          DATETIME
                        , VLR_CARTAO         MONEY
                        , TX_ADM             MONEY
                        , QTDE_DIAS_VENCTO   INT
                        , DT_VENCTO          DATETIME
                        , VLR_RECBTO_BRUTO   MONEY
                        , VLR_TAXA_ADM       MONEY
                        , VLR_RECBTO_LIQUIDO MONEY
                        , STS_FECHA          INT
                        , TIPO_TRANSACAO     INT)
AS
     BEGIN
        INSERT INTO @VENDAS_DEB
        SELECT 
                PDV_VD_TEF.CD_FILIAL
                , RC_CLI.CD_CLI
                , RC_CLI.RZ_CLI
                , PDV_VD_TEF.DT_VD_ADM
                , SUM(PDV_VD_TEF.VL_CARTAO) AS VLR_CARTAO
                , ISNULL(V.PERC_TAXA, -1) AS TX_ADM
                , DATEDIFF(DAY,  PDV_VD_TEF.DT_VD_ADM , PARC.DT_VENCTO )AS QTDE_DIAS_VENCTO
                , PARC.DT_VENCTO AS DT_VENCTO
                , SUM(PARC.VLR_PARC_CARTAO)							AS VLR_RECBTO_BRUTO
                , SUM(PARC.VLR_TX)									AS VLR_TAXA_ADM
                , SUM(PARC.VLR_PARC_CARTAO) - SUM(PARC.VLR_TX)		AS VLR_RECBTO_LIQUIDO
                , CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD IS NULL THEN 0 ELSE 1 END STS_FECHA
                , 0 TP_CUPOM
        FROM 
                PDV_VD_TEF
                INNER JOIN PDV_VD ON PDV_VD_TEF.CD_EMP = PDV_VD.CD_EMP
                                    AND PDV_VD_TEF.CD_VD = PDV_VD.CD_VD
                                    AND PDV_VD_TEF.CD_FILIAL = PDV_VD.CD_FILIAL
				INNER JOIN PDV_VD_TEF_PARC PARC ON 
						PDV_VD_TEF.CD_EMP	 = PARC.CD_EMP
					AND PDV_VD_TEF.CD_VD	 = PARC.CD_VD
					AND PDV_VD_TEF.CD_FILIAL = PARC.CD_FILIAL
					AND PDV_VD_TEF.CD_CTR	 = PARC.CD_CTR

                INNER LOOP JOIN
					(SELECT  CD_EMP , CD_FILIAL 
						FROM  PRC_FILIAL 
						WHERE CD_EMP = @CD_EMP
							AND CD_FILIAL IN( SELECT  ID FROM  UFD_PRM_IN_STRING(@CD_FILIAL) )) F ON 
					PDV_VD_TEF.CD_EMP = F.CD_EMP
                AND PDV_VD_TEF.CD_FILIAL = F.CD_FILIAL

                INNER LOOP JOIN
					(SELECT  CD_EMP , CD_CLI , CD_ADM_CARTAO
					 FROM  RC_ADM_CARTAO_RC_CLI
					WHERE CD_EMP = @CD_EMP
							AND CD_CLI IN ( SELECT  ID FROM  UFD_PRM_IN_STRING(@CD_CLI))) C ON 							
					PDV_VD_TEF.CD_EMP = C.CD_EMP
                AND PDV_VD_TEF.CD_ADM_CARTAO = C.CD_ADM_CARTAO


                INNER JOIN RC_ADM_CARTAO ON PDV_VD_TEF.CD_EMP = RC_ADM_CARTAO.CD_EMP
                                            AND PDV_VD_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO
                INNER JOIN RC_CLI ON C.CD_EMP = RC_CLI.CD_EMP
                                    AND C.CD_CLI = RC_CLI.CD_CLI
                LEFT OUTER JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS V ON PDV_VD_TEF.CD_EMP = V.CD_EMP
                                                                        AND PDV_VD_TEF.CD_FILIAL = V.CD_FILIAL
                                                                        AND PDV_VD_TEF.CD_ADM_CARTAO = V.CD_ADM_CARTAO
                
                LEFT JOIN PDV_VD_CANCEL_TEF ON PDV_VD_TEF.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP
                                            AND PDV_VD_TEF.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL
                                            AND PDV_VD_TEF.CD_VD = PDV_VD_CANCEL_TEF.CD_VD
                LEFT JOIN RC_ADM_CARTAO_MOV_LOTE_PDV_VD ON PDV_VD_TEF.CD_EMP = RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_EMP
                                                        AND PDV_VD_TEF.CD_FILIAL = RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_FILIAL
                                                        AND PDV_VD_TEF.CD_VD = RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD
        WHERE PDV_VD_TEF.CD_EMP = 1
                AND PDV_VD_TEF.DT_VD_ADM BETWEEN @DT_INI AND @DT_FIM
                AND PDV_VD_TEF.VL_CARTAO > 0
                AND PDV_VD.ST_VD = 0
                AND ISNULL(PDV_VD_TEF.CANCEL_TEF, 0) <> 1
                AND RC_ADM_CARTAO.TP_ADM IN(1, 3)
				AND PDV_VD_CANCEL_TEF.CD_VD IS NULL
				and CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD IS NULL THEN 0 ELSE 1 END =@StsMov 
        GROUP BY 
                RC_CLI.CD_CLI
            , RC_CLI.RZ_CLI
            , V.PERC_TAXA
            , PDV_VD_TEF.DT_VD_ADM
            , PARC.DT_VENCTO 
            , RC_ADM_CARTAO.PARC 
            , PDV_VD_TEF.CD_FILIAL
            , RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD
        UNION ALL
        SELECT 
              PDV_CUPOM_NAO_FISCAL.CD_FILIAL
            , RC_CLI.CD_CLI
            , RC_CLI.RZ_CLI
            , PDV_CUPOM_NAO_FISCAL.DT_CUPOM
            , SUM(PDV_RECBTO_TEF.VL_CARTAO) AS VLR_CARTAO
            , ISNULL(V.PERC_TAXA, -1) AS TX_ADM
            , DATEDIFF (DAY,PDV_CUPOM_NAO_FISCAL.DT_CUPOM ,  PARC.DT_VENCTO) AS QTDE_DIAS_VENCTO
            , PARC.DT_VENCTO			AS DT_VENCTO
            , SUM(PARC.VLR_PARC_CARTAO) AS VLR_RECBTO_BRUTO
            , SUM(PARC.VLR_PARC_CARTAO) * (ISNULL(V.PERC_TAXA, 0) / 100 ) AS VLR_TAXA_ADM
            , SUM(PARC.VLR_PARC_CARTAO) - (SUM(PARC.VLR_PARC_CARTAO) * (ISNULL(V.PERC_TAXA, 0) / 100 ))  AS VLR_RECBTO_LIQUIDO
            , CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF IS NULL THEN 0 ELSE 1 END STS_FECHA
            , 1 TP_CUPOM
		FROM 
            PDV_RECBTO_TEF
            INNER JOIN PDV_CUPOM_NAO_FISCAL_TEF ON PDV_RECBTO_TEF.CD_EMP = PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP
                                                AND PDV_RECBTO_TEF.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL
                                                AND PDV_RECBTO_TEF.CD_RECTO_TEF = PDV_CUPOM_NAO_FISCAL_TEF.CD_RECTO_TEF
            INNER JOIN PDV_CUPOM_NAO_FISCAL ON PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP = PDV_CUPOM_NAO_FISCAL.CD_EMP
                                            AND PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL = PDV_CUPOM_NAO_FISCAL.CD_FILIAL
                                            AND PDV_CUPOM_NAO_FISCAL_TEF.CD_CTR = PDV_CUPOM_NAO_FISCAL.CD_CTR

			INNER JOIN PDV_RECBTO_TEF_PARC PARC ON 
			PDV_RECBTO_TEF.CD_EMP		= PARC.CD_EMP AND 
			PDV_RECBTO_TEF.CD_FILIAL	= PARC.CD_FILIAL AND 
			PDV_RECBTO_TEF.CD_RECTO_TEF = PARC.CD_RECTO_TEF

            INNER JOIN RC_ADM_CARTAO ON PDV_RECBTO_TEF.CD_EMP = RC_ADM_CARTAO.CD_EMP
                                        AND PDV_RECBTO_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO

            LEFT OUTER JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS V ON PDV_RECBTO_TEF.CD_EMP = V.CD_EMP
                                                                    AND PDV_RECBTO_TEF.CD_FILIAL = V.CD_FILIAL
                                                                    AND PDV_RECBTO_TEF.CD_ADM_CARTAO = V.CD_ADM_CARTAO
			INNER LOOP JOIN ( SELECT  CD_EMP , CD_FILIAL
								FROM  PRC_FILIAL
								WHERE CD_EMP = @CD_EMP
									AND CD_FILIAL IN(SELECT ID FROM  UFD_PRM_IN_STRING(@CD_FILIAL))) F ON 
						   PDV_RECBTO_TEF.CD_EMP = F.CD_EMP
                       AND PDV_RECBTO_TEF.CD_FILIAL = F.CD_FILIAL

            INNER LOOP JOIN (SELECT 
									   CD_EMP
									 , CD_CLI
									 , CD_ADM_CARTAO
								FROM 
									 RC_ADM_CARTAO_RC_CLI
								WHERE CD_EMP = @CD_EMP
		                          AND CD_CLI IN(SELECT ID FROM UFD_PRM_IN_STRING(@CD_CLI) )) C ON 
						RC_ADM_CARTAO.CD_EMP = c.CD_EMP
						AND RC_ADM_CARTAO.CD_ADM_CARTAO = c.CD_ADM_CARTAO
            INNER JOIN RC_CLI ON c.CD_EMP = RC_CLI.CD_EMP
                                AND c.CD_CLI = RC_CLI.CD_CLI 
            LEFT JOIN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF ON PDV_RECBTO_TEF.CD_EMP = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_EMP
                                                            AND PDV_RECBTO_TEF.CD_FILIAL = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_FILIAL
                                                            AND PDV_RECBTO_TEF.CD_RECTO_TEF = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF
		WHERE PDV_CUPOM_NAO_FISCAL.CD_EMP = 1
				AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
				AND PDV_RECBTO_TEF.VL_CARTAO > 0
				AND PDV_CUPOM_NAO_FISCAL.ST_CUPOM = 0
				AND RC_ADM_CARTAO.TP_ADM IN(1, 3)
				and CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF IS NULL THEN 0 ELSE 1 END  =@StsMov 
		GROUP BY 
                PDV_CUPOM_NAO_FISCAL.CD_FILIAL
            , RC_CLI.CD_CLI
            , RC_CLI.RZ_CLI
            , PDV_CUPOM_NAO_FISCAL.DT_CUPOM
            , V.PERC_TAXA 
			, PARC.DT_VENCTO 
            , RC_ADM_CARTAO.PARC
            , RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF;
         RETURN;
     END;