IF OBJECT_ID('UFD_PDV_VD_IT_RATEIO_POR_SECAO_CONTABIL') IS NOT NULL
    BEGIN
        DROP FUNCTION UFD_PDV_VD_IT_RATEIO_POR_SECAO_CONTABIL;
    END;
GO	
  
-- Select *From UFD_PDV_VD_IT_RATEIO_POR_SECAO_CONTABIL(1,5,'20241016','20241016')   

CREATE FUNCTION [UFD_PDV_VD_IT_RATEIO_POR_SECAO_CONTABIL]
(@CD_EMP    INT,
 @CD_FILIAL INT,
 @DT_INI    DATETIME,
 @DT_FIM    DATETIME
)
RETURNS @RS_PROD_VD TABLE
(CD_EMP                    INT,
 CD_FILIAL                 INT,
 DT_VD                     DATETIME,
 CD_SC                     INT,
 DS_SC                     VARCHAR(150),
 CD_CTB_CNT_RED            INT,
 CD_CTB_CNT                VARCHAR(100),
 VLR_VD                    MONEY,
 VLR_ICMS                  MONEY,
 VLR_BASE_PIS_COFINS       MONEY,
 RECARGA_DINH              MONEY,
 RECARGA_CARTAO            MONEY,
 RECARGA_CHQ               MONEY,
 VLR_SERVICO               MONEY,
 VLR_LINX_PROMO_N_FISCAL   MONEY,
 VLR_LINX_PROMO_FISCAL     MONEY,
 RECEB_TERCEIROS_DINH      MONEY,
 RECEB_TERCEIROS_CARTAO    MONEY,
 RECEB_RPS_DINH            MONEY,
 RECEB_RPS_CARTAO          MONEY,
 VLR_FRETE                 MONEY
)
AS
     BEGIN
         INSERT INTO @RS_PROD_VD
                SELECT CD_EMP
                     , CD_FILIAL
                     , DT_VD
                     , CD_SC
                     , DS_SC
                     , CD_CTB_CNT_RED
                     , CD_CTB_CNT
                     , SUM(VLR_VD) VLR_VD
                     , SUM(VLR_ICMS) VLR_ICMS
                     , SUM(VLR_BASE_PIS_COFINS) VLR_BASE_PIS_COFINS
                     , SUM(RECARGA_DINH) RECARGA_DINH
                     , SUM(RECARGA_CARTAO) RECARGA_CARTAO
                     , SUM(RECARGA_CHQ) RECARGA_CHQ
                     , SUM(VLR_SERVICO) VLR_SERVICO
					 , SUM(VLR_LINX_PROMO_N_FISCAL)VLR_LINX_PROMO_N_FISCAL
					 , SUM(VLR_LINX_PROMO_FISCAL)VLR_LINX_PROMO_FISCAL
					 , SUM(RECEB_TERCEIROS_DINH)RECEB_TERCEIROS_DINH
					 , SUM(RECEB_TERCEIROS_CARTAO)RECEB_TERCEIROS_CARTAO
					 , SUM(RECEB_RPS_DINH)RECEB_RPS_DINH
					 , SUM(RECEB_RPS_CARTAO)RECEB_RPS_CARTAO 
			         , SUM(VLR_FRETE) VLR_FRETE
                FROM
                (
                    SELECT I.CD_EMP
                         , I.CD_FILIAL
                         , I.DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , CASE
                               WHEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0
                               THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
                               ELSE 0
                           END AS VLR_VD
    
                           --ISNULL(PDV_VD.VLR_LIQ_VD,0) as VLR_VD,  
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
						 , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , SUM(I.VLR_FRETE_RAT) VLR_FRETE
                    FROM
                         --  SELECT * FROM PDV_vD_IT WHERE CD_FILIAL = 3  AND DT_VD BETWEEN '20160803' AND '20160803' AND VLR_DESC_TOT_RAT > 0           
                         PDV_VD_IT I
                         INNER JOIN PDV_VD V
                         ON I.CD_EMP = V.CD_EMP
                            AND I.CD_FILIAL = V.CD_FILIAL
                            AND I.CD_VD = V.CD_VD
                    WHERE I.CD_EMP = @CD_EMP
                          AND I.CD_FILIAL = @CD_FILIAL
                          AND I.DT_VD BETWEEN @DT_INI AND @DT_FIM
                          AND I.ST_IT = 1
                          AND V.ST_VD = 0
                          AND ISNULL(TIPO_IMPOSTO, 0) = 0
                    GROUP BY I.CD_EMP
                           , I.CD_FILIAL
                           , I.DT_VD
                    UNION ALL
                    SELECT PDV_VD.CD_EMP
                         , PDV_VD.CD_FILIAL
                         , PDV_VD.DT_VD
                         , 0
                         , ''
                         , 0
                         , ''
                         , 0
                         , CASE WHEN ISNULL(TX_RED_IT,0) > 0 AND RIGHT (PDV_VD_IT.CD_TRIB_FC, 2) = '20' THEN
							((PDV_VD_IT.TOT_IT - VLR_DESC_TOT_RAT ) * (TX_ICMS_IT * TX_RED_IT) / 100.0000 ) / 100.0000
						 ELSE
							( PDV_VD_IT.TOT_IT - VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_ICMS_IT / 100.0000 ) 

						 END AS VLR_ICMS
                         , CASE WHEN PIS_COFINS = 1 THEN  ISNULL(VLR_BASE_PIS, 0) ELSE 0 END AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS REGARGA_CARTAO
                         , 0 AS REGARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
						 , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
                    FROM
                         PDV_VD_IT
                         INNER JOIN PDV_VD
                         ON PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP
                            AND PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL
                            AND PDV_VD_IT.CD_VD = PDV_VD.CD_VD 
                    WHERE PDV_VD.CD_EMP = @CD_EMP
                          AND PDV_VD.CD_FILIAL = @CD_FILIAL
                          AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
                          AND PDV_VD.ST_VD = 0
                          AND PDV_VD_IT.ST_IT = 1
                          AND ISNULL(TIPO_IMPOSTO, 0) = 0
                    UNION ALL
                    SELECT CD_EMP
                         , CD_FILIAL
                         , DT_CUPOM AS DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , VLR_DINH AS RECARGA_DINH
                         , VLR_CARTAO AS RECARGA_CARTAO
                         , VLR_CHQS AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
						 , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
                    FROM PDV_CUPOM_NAO_FISCAL
                    WHERE PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
                          AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL
                          AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
                          AND TP_CUPOM_NAO_FISCAL = 3
                          AND ST_CUPOM NOT IN(1, 2, 3) 

                    -- SERVIÇO
                    UNION ALL
                    SELECT I.CD_EMP
                         , I.CD_FILIAL
                         , I.DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
    
                           --ISNULL(PDV_VD.VLR_LIQ_VD,0) as VLR_VD,  
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , CASE
                               WHEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0
                               THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
                               ELSE 0
                           END AS VLR_SERVICO
                         , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
					     , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
                    FROM
                         PDV_VD_IT I
                         INNER JOIN PDV_VD V
                         ON I.CD_EMP = V.CD_EMP
                            AND I.CD_FILIAL = V.CD_FILIAL
                            AND I.CD_VD = V.CD_VD
                    WHERE I.CD_EMP = @CD_EMP
                          AND I.CD_FILIAL = @CD_FILIAL
                          AND I.DT_VD BETWEEN @DT_INI AND @DT_FIM
                          AND I.ST_IT = 1
                          AND V.ST_vD = 0
                          AND ISNULL(TIPO_IMPOSTO, 0) = 1
                    GROUP BY I.CD_EMP
                           , I.CD_FILIAL
                           , I.DT_VD
                  UNION ALL
				  
				  --Linx Promo Não Fiscal
				    SELECT CD_EMP
                         , CD_FILIAL
                         , DT_VENDA AS DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , SUM(VALOR) AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
					     , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
                    FROM PDV_CUPOM_NAO_FISCAL_CARTAO_LINX_PROMO
                    WHERE PDV_CUPOM_NAO_FISCAL_CARTAO_LINX_PROMO.CD_EMP = @CD_EMP
                          AND PDV_CUPOM_NAO_FISCAL_CARTAO_LINX_PROMO.CD_FILIAL = @CD_FILIAL
                          AND PDV_CUPOM_NAO_FISCAL_CARTAO_LINX_PROMO.DT_VENDA BETWEEN @DT_INI AND @DT_FIM

					GROUP BY  CD_EMP
					, CD_FILIAL
					, DT_VENDA
					
					UNION ALL
				  --Linx Promo Fiscal
				    SELECT CD_EMP
                         , CD_FILIAL
                         , DT_FECHA_CX AS DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , SUM(VLR_NCC) AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
					     , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
                    FROM PDV_VD_DEV_NCC
                    WHERE PDV_VD_DEV_NCC.CD_EMP = @CD_EMP
                          AND PDV_VD_DEV_NCC.CD_FILIAL = @CD_FILIAL
                          AND PDV_VD_DEV_NCC.DT_FECHA_CX BETWEEN @DT_INI AND @DT_FIM
						  AND PDV_VD_DEV_NCC.TIPO_NCC = 12

					GROUP BY  CD_EMP
					, CD_FILIAL
					, DT_FECHA_CX
                    -- consulta base: SELECT TOP 1 * FROM V_PDV_CUPOM_NAO_FISCAL_REC_DIVERSOS WHERE
					
					--
					UNION ALL
                    SELECT PDV_CUPOM_NAO_FISCAL.CD_EMP
                         , PDV_CUPOM_NAO_FISCAL.CD_FILIAL
                         , DT_CUPOM AS DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 ,SUM(PDV_CUPOM_NAO_FISCAL.VLR_DINH) AS RECEB_TERCEIROS_DINH
						 , 0 AS RECEB_TERCEIROS_CARTAO
					     , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
						FROM PDV_CUPOM_NAO_FISCAL
						INNER JOIN PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP
							AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_FILIAL
							AND PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_CTR
						INNER JOIN EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP = EST_RECB_PDV.CD_EMP
							AND PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_RECB_PDV = EST_RECB_PDV.CD_RECB_PDV
						WHERE PDV_CUPOM_NAO_FISCAL.VLR_DINH > 0
						  and PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
                          AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL
                          AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
						  AND PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL <> 3
                       GROUP BY PDV_CUPOM_NAO_FISCAL.CD_EMP
                         , PDV_CUPOM_NAO_FISCAL.CD_FILIAL
                         , DT_CUPOM 
						 
				 UNION ALL
                    SELECT PDV_CUPOM_NAO_FISCAL.CD_EMP
                         , PDV_CUPOM_NAO_FISCAL.CD_FILIAL
                         , DT_CUPOM AS DT_VD
                         , 0 AS CD_SC
                         , '' AS DS_SC
                         , 0 AS CD_CTB_CNT_RED
                         , '' AS CD_CTB_CNT
                         , 0 AS VLR_VD
                         , 0 AS VLR_ICMS
                         , 0 AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS RECARGA_CARTAO
                         , 0 AS RECARGA_CHQ
                         , 0 AS VLR_SERVICO
						 , 0 AS VLR_LINX_PROMO_N_FISCAL
						 , 0 AS VLR_LINX_PROMO_FISCAL
						 , 0 AS RECEB_TERCEIROS_DINH
						 ,SUM(PDV_CUPOM_NAO_FISCAL.VLR_CARTAO) AS RECEB_TERCEIROS_CARTAO
					     , 0 AS RECEB_RPS_DINH
						 , 0 AS RECEB_RPS_CARTAO
						 , 0 AS VLR_FRETE
						FROM PDV_CUPOM_NAO_FISCAL
						INNER JOIN PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP
							AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_FILIAL
							AND PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_CTR
						INNER JOIN EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP = EST_RECB_PDV.CD_EMP
							AND PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_RECB_PDV = EST_RECB_PDV.CD_RECB_PDV
						WHERE PDV_CUPOM_NAO_FISCAL.VLR_CARTAO > 0
						and PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
                          AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL
                          AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
                       GROUP BY PDV_CUPOM_NAO_FISCAL.CD_EMP
                         , PDV_CUPOM_NAO_FISCAL.CD_FILIAL
                         , DT_CUPOM 
					 		 
				UNION ALL
					SELECT PDV_CUPOM_NAO_FISCAL.CD_EMP
							, PDV_CUPOM_NAO_FISCAL.CD_FILIAL
							, DT_CUPOM AS DT_VD
							, 0 AS CD_SC
							, '' AS DS_SC
							, 0 AS CD_CTB_CNT_RED
							, '' AS CD_CTB_CNT
							, 0 AS VLR_VD
							, 0 AS VLR_ICMS
							, 0 AS VLR_BASE_PIS_COFINS
							, 0 AS RECARGA_DINH
							, 0 AS RECARGA_CARTAO
							, 0 AS RECARGA_CHQ
							, 0 AS VLR_SERVICO
							, 0 AS VLR_LINX_PROMO_N_FISCAL
							, 0 AS VLR_LINX_PROMO_FISCAL
							, 0 AS RECEB_TERCEIROS_DINH
							, 0 AS RECEB_TERCEIROS_CARTAO
							, SUM(PDV_CUPOM_NAO_FISCAL.VLR_DINH) AS RECEB_RPS_DINH
							, 0 AS RECEB_RPS_CARTAO
						    , 0 AS VLR_FRETE
						FROM PDV_CUPOM_NAO_FISCAL
						 
						WHERE PDV_CUPOM_NAO_FISCAL.VLR_DINH > 0
						    AND PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
							AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL
							AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
							AND TP_CUPOM_NAO_FISCAL = 7 
							AND ST_CUPOM = 0
						GROUP BY PDV_CUPOM_NAO_FISCAL.CD_EMP
							, PDV_CUPOM_NAO_FISCAL.CD_FILIAL
							, DT_CUPOM   
						 
					
				UNION ALL
					SELECT PDV_CUPOM_NAO_FISCAL.CD_EMP
							, PDV_CUPOM_NAO_FISCAL.CD_FILIAL
							, DT_CUPOM AS DT_VD
							, 0 AS CD_SC
							, '' AS DS_SC
							, 0 AS CD_CTB_CNT_RED
							, '' AS CD_CTB_CNT
							, 0 AS VLR_VD
							, 0 AS VLR_ICMS
							, 0 AS VLR_BASE_PIS_COFINS
							, 0 AS RECARGA_DINH
							, 0 AS RECARGA_CARTAO
							, 0 AS RECARGA_CHQ
							, 0 AS VLR_SERVICO
							, 0 AS VLR_LINX_PROMO_N_FISCAL
							, 0 AS VLR_LINX_PROMO_FISCAL
							, 0 AS RECEB_TERCEIROS_DINH
							, 0 AS RECEB_TERCEIROS_CARTAO
							, 0 AS RECEB_RPS_DINH
							, SUM(PDV_CUPOM_NAO_FISCAL.VLR_CARTAO)  AS RECEB_RPS_CARTAO
						    , 0 AS VLR_FRETE
						FROM PDV_CUPOM_NAO_FISCAL
						 
						WHERE PDV_CUPOM_NAO_FISCAL.VLR_CARTAO > 0
						    AND PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
							AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL
							AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
							AND TP_CUPOM_NAO_FISCAL = 7 
							AND ST_CUPOM = 0
						GROUP BY PDV_CUPOM_NAO_FISCAL.CD_EMP
							, PDV_CUPOM_NAO_FISCAL.CD_FILIAL
							, DT_CUPOM   
	   

                ) rs
                GROUP BY CD_EMP
                       , CD_FILIAL
                       , DT_VD
                       , CD_SC
                       , DS_SC
                       , CD_CTB_CNT_RED
                       , CD_CTB_CNT;
         RETURN;
     END;

 