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,'20160803','20160803')   
--versao 2471  

CREATE FUNCTION [dbo].[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
)
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
                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
                    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
                         , ISNULL(VLR_BASE_PIS, 0) AS VLR_BASE_PIS_COFINS
                         , 0 AS RECARGA_DINH
                         , 0 AS REGARGA_CARTAO
                         , 0 AS REGARGA_CHQ
                         , 0 AS VLR_SERVICO
                    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
                    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
                    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
   
                    -- consulta base: SELECT TOP 1 * FROM V_PDV_CUPOM_NAO_FISCAL_REC_DIVERSOS WHERE
                ) rs
                GROUP BY CD_EMP
                       , CD_FILIAL
                       , DT_VD
                       , CD_SC
                       , DS_SC
                       , CD_CTB_CNT_RED
                       , CD_CTB_CNT;
         RETURN;
     END;