 IF EXISTS
(
    SELECT *
    FROM sys.views
    WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_CAB]')
)
    DROP VIEW [dbo].[V_PDV_VD_CAB];
GO

--  SELECT TOP 2 * FROM V_PDV_VD_CAB    
CREATE VIEW [dbo].[V_PDV_VD_CAB]

AS
     SELECT CD_EMP,
            CD_FILIAL,
            CD_CX,
            DT_VD,
            CD_VD,
            ST_VD,
            NR_ECF,
            VLR_VD,
            CGC_CPF,
            NR_SERIE_IMP,
            CRO,
            CRZ,
            SUM(VLR_CUPONS_CANCELADOS_ISSQN) AS VLR_CUPONS_CANCELADOS_ISSQN,
            SUM(VLR_CUPONS_CANCELADOS_ICMS) AS VLR_CUPONS_CANCELADOS_ICMS,
            SUM(VLR_IT_CANCEL_ISSQN) AS VLR_IT_CANCEL_ISSQN,
            SUM(VLR_IT_CANCEL_ICMS) AS VLR_IT_CANCEL_ICMS,
            SUM(VLR_BASE_ICMS) AS VLR_BASE_ICMS,
            SUM(VLR_ICMS) AS VLR_ICMS,
            SUM(VLR_DESC) AS VLR_DESC,
            SUM(VLR_DESC_IT) AS VLR_DESC_IT,
            SUM(VLR_ISSQN) AS VLR_ISSQN,
            SUM(VLR_ISENTAS) AS VLR_ISENTAS,
            SUM(VLR_N_INCIDENCIA) AS VLR_N_INCIDENCIA,
            SUM(VLR_SUBST_TRIB) AS VLR_SUBST_TRIB,
            SUM(VLR_PIS) AS VLR_PIS,
            SUM(VLR_COFINS) AS VLR_COFINS,
            SUM(QTDE_ITENS) AS QTDE_ITENS,
            SUM(VLR_FECOP) AS VLR_FECOP,
            SUM(VLR_BASE_PIS) AS VLR_BASE_PIS,
            SUM(VLR_BASE_COFINS) AS VLR_BASE_COFINS,
		  SUM(VLR_PROD_LIQ) AS VLR_PROD_LIQ,
		  SUM(VLR_PROD_BRUTO) AS VLR_PROD_BRUTO
     FROM
(
    SELECT DISTINCT
           PDV_VD.CD_EMP,
           PDV_VD.CD_FILIAL,
           PDV_VD.CD_VD,
           PDV_VD.ST_VD,
           PDV_VD.CD_CX,
           PDV_VD.DT_VD,
           PDV_VD.NR_ECF,
           PDV_VD.VLR_VD,
           PDV_VD.CGC_CPF,
           PDV_CX.NR_SERIE_IMP,
          ISNULL ( mapa.CONT_REINICIO ,0 ) AS cro,
          ISNULL( mapa.CONT_RED_Z ,0) AS CRZ,
           CASE
               WHEN PDV_VD.ST_VD = 1
                    AND TIPO_IMPOSTO = 1
                    AND TP_PROD = 10
               THEN SUM(ISNULL((I.QT_IT * I.VLR_IT), 0) * Isnull(TX_ICMS_IT, 0) / 100)
               ELSE 0
           END VLR_CUPONS_CANCELADOS_ISSQN,
           CASE
               WHEN PDV_VD.ST_VD = 1
                    AND TIPO_IMPOSTO = 0
                    AND TP_PROD <> 10
               THEN SUM(ISNULL((I.QT_IT * I.VLR_IT), 0) * Isnull(TX_ICMS_IT, 0) / 100)
               ELSE 0
           END AS VLR_CUPONS_CANCELADOS_ICMS,
           CASE
               WHEN PDV_VD.ST_VD = 0
                    AND I.ST_IT = 2
                    AND TIPO_IMPOSTO = 1
                    AND TP_PROD = 10
               THEN SUM(ISNULL((I.QT_IT * I.VLR_IT), 0) * Isnull(TX_ICMS_IT, 0) / 100)
               ELSE 0
           END AS VLR_IT_CANCEL_ISSQN,
           CASE
               WHEN PDV_VD.ST_VD = 0
                    AND I.ST_IT = 2
                    AND TIPO_IMPOSTO = 0
                    AND TP_PROD <> 10
               THEN SUM(ISNULL((I.QT_IT * I.VLR_IT), 0) * Isnull(TX_ICMS_IT, 0) / 100)
               ELSE 0
           END AS VLR_IT_CANCEL_ICMS,

           CASE
               WHEN PDV_VD.ST_VD = 0
                    AND I.ST_IT = 1
               THEN SUM(ROUND(ISNULL((I.QT_IT * I.VLR_IT), 0) * I.TX_DESC / 100,2))
               ELSE 0
           END AS VLR_DESC_IT,  

           CASE
               WHEN PDV_VD.ST_VD = 0
                    AND I.ST_IT = 1
               THEN SUM(I.VLR_DESC_TOT_RAT)
               ELSE 0
           END AS VLR_DESC,
 
	CASE WHEN SUM(ISNULL(I.VLR_BASE_CALC ,0) )= 0 THEN 
		
		CASE  WHEN  ISNULL(I.TIPO_IMPOSTO, 0) = 1 then
			0
		 ELSE 
						CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO IN (1,2) AND LTRIM(RIGHT(I.CD_TRIB_FC, 2))='500' THEN
							0  --  SELECT REGIME_TRIBUTARIO, * FROM PRC_FILIAL WHERE CD_FILIAL = 7
						ELSE
							CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) IN ('00','20','02') THEN 
								SUM( I.TOT_IT - I.VLR_DESC_TOT_RAT ) 
							ELSE 0
							END
						END
    
		END 
	ELSE
		SUM(I.VLR_BASE_CALC )
	END 	AS VLR_BASE_ICMS ,
   
   CASE WHEN SUM(ISNULL(I.VLR_ICMS ,0) )= 0 THEN  
    
		CASE  WHEN  ISNULL(I.TIPO_IMPOSTO, 0) = 1 then
			0	
		ELSE
				CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO IN (1,2) AND I.CD_TRIB_FC='500' THEN
					0
				ELSE
					CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) IN ('00','20','02') THEN
						CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) = '00' THEN 
						 SUM ( I.TOT_IT - I.VLR_DESC_TOT_RAT ) * ( I.TX_ICMS_IT / 100.0000 )
						ELSE 
						SUM ( ( I.TOT_IT - VLR_DESC_TOT_RAT ) * ( I.TX_RED_IT / 100.0000 ) ) * ( I.TX_ICMS_IT / 100.0000 )
						
						END
					ELSE 0 END
		END				 
				END  
	ELSE  
	
	SUM(I.VLR_ICMS) END 
			AS VLR_ICMS,
  
           CASE
               WHEN RIGHT(I.CD_TRIB_FC, 2) IN('00', '20')
    AND ISNULL(I.TIPO_IMPOSTO, 0) = 1
    AND TP_PROD = 10
    AND I.ST_IT = 1
               THEN SUM((I.TOT_IT - I.VLR_DESC_TOT_RAT) * (I.TX_ICMS_IT / 100))
               ELSE 0
           END AS VLR_ISSQN,
           (CASE
                WHEN RIGHT(I.CD_TRIB_FC, 2) = '40'
                THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
                ELSE 0
            END) AS VLR_ISENTAS,
           (CASE
                WHEN RIGHT(I.CD_TRIB_FC, 2) = '41'
                THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
                ELSE 0
            END) AS VLR_N_INCIDENCIA,
           (CASE
                WHEN RIGHT(I.CD_TRIB_FC, 2) IN('60', '10', '70')
                THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
                ELSE 0
            END) AS VLR_SUBST_TRIB,
           CASE
               WHEN VLR_PIS > 0
               THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
               ELSE 0
           END AS VLR_BASE_PIS,
           VLR_PIS,
           CASE
               WHEN VLR_COFINS > 0
               THEN SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT)
               ELSE 0
           END AS VLR_BASE_COFINS,
           VLR_COFINS,
           SUM(QT_IT) QTDE_ITENS,
           (CASE
                WHEN RIGHT(I.CD_TRIB_FC, 2) IN('00', '20')
                THEN SUM((I.TOT_IT - I.VLR_DESC_TOT_RAT) * (ISNULL(I.PERC_FECOP, 0) / 100))
                ELSE 0
            END) AS VLR_FECOP,
		  SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) VLR_PROD_LIQ,

		  CASE
               WHEN PDV_VD.ST_VD = 0
                    AND I.ST_IT = 1
               THEN SUM(ROUND(ISNULL((I.QT_IT * I.VLR_IT), 0),2))
               ELSE 0
           END AS VLR_PROD_BRUTO		   

    FROM PDV_VD
         INNER JOIN PDV_VD_IT I ON I.CD_EMP = PDV_VD.CD_EMP
                                   AND I.CD_FILIAL = PDV_VD.CD_FILIAL
                                   AND I.CD_VD = PDV_VD.CD_VD
         INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
                              AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
                              AND PDV_VD.CD_CX = PDV_CX.CD_CX
		 INNER JOIN PRC_FILIAL ON PDV_VD.CD_EMP  = PRC_FILIAL.CD_EMP
		 AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
         LEFT JOIN PDV_MAPA_RESUMO MAPA ON MAPA.CD_EMP = PDV_VD.CD_EMP
                                           AND MAPA.CD_FILIAL = PDV_VD.CD_FILIAL
                                           AND MAPA.DT_ECF = PDV_VD.DT_VD
                                           AND MAPA.CD_CX = PDV_VD.CD_CX
		 
    WHERE  I.ST_IT = 1
	 
           
    GROUP BY PDV_VD.CD_EMP,
             PDV_VD.CD_FILIAL,
             PDV_VD.CD_CX,
             PDV_VD.DT_VD,
             PDV_VD.NR_ECF,
             PDV_VD.VLR_VD,
             PDV_VD.CD_VD,
             PDV_VD.ST_VD,
             I.TIPO_IMPOSTO,
             I.TP_PROD,
             i.ST_IT,
             I.CD_TRIB_FC,
             PDV_VD.CGC_CPF,
             PDV_CX.NR_SERIE_IMP,
             mapa.CONT_REINICIO,
             mapa.CONT_RED_Z,
             PDV_VD.CD_VD,
             PDV_VD.ST_VD,
             VLR_PIS,
             VLR_COFINS,
             I.TX_RED_IT,PRC_FILIAL.REGIME_TRIBUTARIO ,I.TX_ICMS_IT,I.VLR_BASE_CALC 
) AS TEMP
     GROUP BY CD_EMP,
              CD_FILIAL,
              CD_CX,
              DT_VD,
              NR_ECF,
              VLR_VD,
              CGC_CPF,
              NR_SERIE_IMP,
              CRO,
              CRZ,
              CD_VD,
              ST_VD;
 


