IF EXISTS (SELECT* FROM SYS.VIEWS WHERE NAME = 'V_ENVIA_PRODUTOS_FISCOREDUCAOZ')
BEGIN
	DROP VIEW  V_ENVIA_PRODUTOS_FISCOREDUCAOZ;
END 
GO 

CREATE VIEW  V_ENVIA_PRODUTOS_FISCOREDUCAOZ
AS 
	SELECT  
	    G.CD_EMP
		,G.CD_FILIAL
		,G.CD_CX
		,CASE WHEN G.TPTOTALIZADORISENTO = '' THEN (G.TPTOTALIZADOR + G.PERCIMPOSTO)
			  WHEN G.TPTOTALIZADOR = 'S' THEN ((G.TPTOTALIZADORISENTO + G.TPTOTALIZADOR) + '1')
			  ELSE (G.TPTOTALIZADORISENTO + '1')
		  END									AS TOTALIZADOR
		,G.DS_PROD
		,G.CD_PROD
		,G.CODIGO_PRODUTO
		,SUM(G.QT_IT)							AS QT_IT
		,G.UNIDADE
		,ROUND(SUM(G.VLR_IT), 4)				AS VLR_IT
		,G.DT_VD
		,G.TP_PROD
		,ROUND(SUM(G.VLR_TOT_LIQ_IT), 4)		AS VLR_TOT_LIQ_IT
		,ROUND(SUM(G.VLR_TOT_LIQ_IT_CANCEL), 4) AS VLR_TOT_LIQ_IT_CANCEL
		,ROUND(SUM(G.VLR_DESC_IT), 4)			AS VLR_DESC_IT
		,G.NR_CEST
		,G.NR_NCM
	FROM (
		SELECT A.CD_EMP
			  ,A.CD_FILIAL
			  ,A.CD_CX
			  ,B.CD_TRIB_FC
			  ,D.DS_PROD
			  ,B.CD_PROD
			  ,COALESCE(( SELECT top 1 E.CD_BARRA
			  			    FROM EST_PROD_CD_BARRA E
			  			   WHERE B.CD_PROD = E.CD_PROD), '0000000000000') AS CODIGO_PRODUTO
			  ,SUM(B.QT_IT) AS QT_IT
			  ,COALESCE(F.DS_UN, 'UN', F.DS_UN)				AS UNIDADE
			  ,ROUND(SUM(B.VLR_IT) / COUNT(B.VLR_IT), 4)	AS VLR_IT
			  ,ROUND(SUM(B.VLR_DESC_TOT_RAT), 4)					AS VLR_DESC_IT
			  ,A.DT_VD
			  ,H.TP_PROD
			  ,CASE B.ST_IT WHEN 1 THEN ROUND(SUM(B.VLR_IT * B.QT_IT - B.VLR_DESC_TOT_RAT), 4)
			  				ELSE 0 
				END AS VLR_TOT_LIQ_IT
			  ,CASE B.ST_IT WHEN 2 THEN ROUND(SUM(B.VLR_IT * B.QT_IT - B.VLR_DESC_TOT_RAT), 4)
			  				ELSE 0
			  	END AS VLR_TOT_LIQ_IT_CANCEL
			  ,D.NR_CEST
			  ,D.NR_NCM
			  ,REPLACE(FORMAT(B.TX_ICMS_IT, '00.00'), '.', '') AS PERCIMPOSTO
			  ,CASE WHEN H.TP_PROD = 10 THEN 'S'
			  		ELSE 'T'
			  	END AS TPTOTALIZADOR
			  ,CASE WHEN B.CD_TRIB_FC = '040'OR TRIM(B.CD_TRIB_FC) = '050' THEN 'I'
			  		WHEN TRIM(B.CD_TRIB_FC) = '041' THEN 'N'
			  		WHEN TRIM(B.CD_TRIB_FC) = '060' OR TRIM(B.CD_TRIB_FC) = '010' THEN 'F'
			  		ELSE ''
			  	END AS TPTOTALIZADORISENTO
		  FROM PDV_VD A
			   JOIN PDV_VD_IT B ON A.CD_EMP    = B.CD_EMP
							   AND A.CD_FILIAL = B.CD_FILIAL
							   AND A.CD_VD     = B.CD_VD
			   JOIN EST_PROD D ON B.CD_EMP  = D.CD_EMP 
							  AND B.CD_PROD = D.CD_PROD
			   LEFT JOIN EST_PROD_UN_MED F ON D.CD_UN_VD = F.CD_CTR_UN
			   LEFT JOIN EST_PROD_CPL H ON B.CD_EMP    = H.CD_EMP
									   AND B.CD_FILIAL = H.CD_FILIAL
									   AND B.CD_PROD   = H.CD_PROD
									   
		 GROUP BY  B.CD_TRIB_FC
			      ,D.DS_PROD
			      ,B.CD_PROD
			      ,F.DS_UN
			      ,A.DT_VD
			      ,A.CD_EMP
			      ,A.CD_FILIAL
			      ,A.CD_CX
			      ,H.TP_PROD
			      ,B.ST_IT
			      ,D.NR_CEST
			      ,D.NR_NCM
			      ,B.TX_ICMS_IT
		 
		
		) G
	GROUP BY G.TPTOTALIZADOR
		,G.TPTOTALIZADORISENTO
		,G.DS_PROD
		,G.CD_PROD
		,G.CODIGO_PRODUTO
		,G.UNIDADE
		,G.DT_VD
		,G.CD_EMP
		,G.CD_FILIAL
		,G.CD_CX
		,G.TP_PROD
		,G.NR_CEST
		,G.NR_NCM
		,G.PERCIMPOSTO;
