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

GO

--select * from V_PDV_VD_CUPOM_TRIBUTOS where cd_emp = 1 and cd_filial = 300 and dt_vd = '2017-07-05'
CREATE VIEW [DBO].[V_PDV_VD_CUPOM_TRIBUTOS]
WITH ENCRYPTION
AS
	SELECT
		TRIB.CD_EMP
		,TRIB.CD_FILIAL
		,TRIB.CD_VD
		,TRIB.CD_PROD
		,TRIB.CD_IT
		,TRIB.CD_CX
		,TRIB.DT_VD
		,CONVERT(MONEY, SUM(TRIB.VLR_SUBST_TRIB))         AS VLR_SUBST_TRIB
		,CONVERT(MONEY, SUM(TRIB.VLR_ISENTAS))            AS VLR_ISENTAS
		,CONVERT(MONEY, SUM(TRIB.VLR_ISENTAS_ISSQN))      AS VLR_ISENTAS_ISSQN
		,CONVERT(MONEY, SUM(TRIB.VLR_N_INCIDENCIA))       AS VLR_N_INCIDENCIA
		,CONVERT(MONEY, SUM(TRIB.VLR_N_INCIDENCIA_ISSQN)) AS VLR_N_INCIDENCIA_ISSQN
		,CONVERT(MONEY, SUM(TRIB.VLR_BASE_CALC_ICMS))     AS VLR_BASE_CALC_ICMS
		,CONVERT(MONEY, ROUND(SUM(TRIB.VLR_IMPOSTO),2)) AS VLR_IMPOSTO
		,CONVERT(MONEY, SUM(BASE_ISSQN)) AS BASE_ISSQN
		,CONVERT(MONEY, ISNULL(SUM(VLR_ISSQN),0)) AS VLR_ISSQN
		,CONVERT(MONEY, ISNULL(SUM(VLR_TOT_TRIB-(TRIB.VLR_N_INCIDENCIA_ISSQN+TRIB.VLR_ISENTAS_ISSQN+BASE_ISSQN)),0)) AS VLR_VENDA_LIQUIDA
	FROM
		(SELECT
			 TRIB_TOT.CD_EMP
			 ,TRIB_TOT.CD_FILIAL
			 ,TRIB_TOT.CD_VD
			 ,TRIB_TOT.CD_IT
			 ,TRIB_TOT.CD_PROD
			 ,TRIB_TOT.CD_CX
			 ,TRIB_TOT.DT_VD
			 ,CASE WHEN TIPO_IMPOSTO = 0 THEN
					CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2) in ('60','10','70') THEN 
					   TRIB_TOT.VLR_TOT_TRIB
					ELSE 
					   0
					END
				ELSE 
				    0
				END AS VLR_SUBST_TRIB
			 ,CASE WHEN TIPO_IMPOSTO = 0 THEN
					CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2) IN  ('40','03') THEN TRIB_TOT.VLR_TOT_TRIB
					ELSE 0
					END
				ELSE 0
				END AS VLR_ISENTAS
			 ,CASE WHEN TIPO_IMPOSTO = 1 THEN
					CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2)  IN  ('40','03') THEN TRIB_TOT.VLR_TOT_TRIB
					ELSE 0
					END
				ELSE 0
				END AS VLR_ISENTAS_ISSQN
			 ,CASE WHEN TIPO_IMPOSTO = 0 THEN
					CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2) = '41' THEN TRIB_TOT.VLR_TOT_TRIB
					ELSE 0
					END
				ELSE 0
				END AS VLR_N_INCIDENCIA
			 ,CASE WHEN TIPO_IMPOSTO = 1 THEN
					CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2) = '41' THEN TRIB_TOT.VLR_TOT_TRIB
					ELSE 0
					END
				ELSE 0
				END AS VLR_N_INCIDENCIA_ISSQN
			 ,CASE WHEN TIPO_IMPOSTO = 0 THEN
					CASE WHEN REGIME_TRIBUTARIO IN (1,2) AND TRIB_TOT.CD_TRIB_FC='500' THEN
						0
					ELSE
						CASE WHEN RIGHT(TRIB_TOT.CD_TRIB_FC, 2) IN ('00','20','02') THEN TRIB_TOT.BASE_ICMS
						ELSE 0
						END
					END
				ELSE 0
				END AS VLR_BASE_CALC_ICMS
			 ,CASE WHEN TIPO_IMPOSTO = 0 THEN TRIB_TOT.VLR_IMPOSTO
				ELSE 0
				END VLR_IMPOSTO
			 ,VALOR_REDUCAO
			 ,VLR_ISSQN
			 ,BASE_ISSQN,
			 VLR_TOT_TRIB
		 FROM
			 (SELECT
					PDV_VD.CD_EMP
					,PDV_VD.CD_FILIAL
					,PDV_VD.CD_VD
					,PDV_VD.CD_CX
					,PDV_VD.DT_VD
					,PDV_VD_IT.CD_PROD
					,PDV_VD_IT.CD_IT
					,PDV_VD_IT.CD_TRIB_FC
					,ISNULL(TIPO_IMPOSTO, 0) TIPO_IMPOSTO
					,PRC_FILIAL.REGIME_TRIBUTARIO
				     ,( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) AS VLR_TOT_TRIB
					,CASE WHEN (LTRIM(RIGHT(PDV_VD_IT.CD_TRIB_FC, 2)) IN ('00','20','02')) AND
							COALESCE(tipo_imposto, 0) = 0 THEN 
							 ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT )
					 ELSE 
					   0
					 END AS BASE_ICMS
					,CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO IN (1,2) AND PDV_VD_IT.CD_TRIB_FC='500' THEN
					0
					ELSE
						CASE WHEN ISNULL(tipo_imposto, 0) = 1  AND LTRIM(RIGHT(PDV_VD_IT.CD_TRIB_FC, 2)) = '00' THEN
						 ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT )
					 ELSE 
					   0
					 END 
					 END AS BASE_ISSQN
					,CASE WHEN LTRIM(RIGHT(PDV_VD_IT.CD_TRIB_FC, 2)) = '20' AND
						ISNULL(tipo_imposto, 0) = 0 THEN ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) - ( ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_RED_IT / 100.0000 ) )
					 ELSE 
					   0
					 END AS VALOR_REDUCAO
					,CASE WHEN ISNULL(tipo_imposto, 0) = 1 THEN ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_ICMS_IT / 100.0000 )
					 ELSE
						 CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO IN (1,2) AND PDV_VD_IT.CD_TRIB_FC='500' THEN
						 0
						 ELSE
						 CASE WHEN LTRIM(RIGHT(PDV_VD_IT.CD_TRIB_FC, 2)) IN ('00','20','02') THEN
							 CASE WHEN LTRIM(RIGHT(PDV_VD_IT.CD_TRIB_FC, 2)) = '00' AND
										COALESCE(tipo_imposto, 0) = 0 THEN ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_ICMS_IT / 100.0000 )
							 ELSE ( ( PDV_VD_IT.TOT_IT - VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_RED_IT / 100.0000 ) ) * ( PDV_VD_IT.TX_ICMS_IT / 100.0000 )
							 END
						 ELSE 0
						 END
						 END
					 END  AS VLR_IMPOSTO
					,( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) TOT_IT
					,CASE WHEN ISNULL(tipo_imposto, 0) = 1 THEN 
													   ( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT ) * ( PDV_VD_IT.TX_ICMS_IT / 100.0000 )
												    ELSE
													   0
												    END  AS VLR_ISSQN
				FROM
					PDV_VD_IT
					JOIN PDV_VD ON
						PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND
						PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
						PDV_VD.CD_VD = PDV_VD_IT.CD_VD
					INNER JOIN PRC_FILIAL ON
					PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP AND
					PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
				WHERE
				 PDV_VD.ST_VD    = 0 AND
				 PDV_VD_IT.ST_IT = 1
				 AND ISNULL(PDV_VD.TIPO,0) = 0
			 ) TRIB_TOT
			 ) TRIB
	GROUP  BY
		TRIB.CD_EMP
		,TRIB.CD_FILIAL
		,TRIB.CD_VD
		,TRIB.CD_IT
		,TRIB.CD_PROD
		,TRIB.CD_CX
		,TRIB.DT_VD 
