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

GO

CREATE VIEW [dbo].[V_APURA_ICMS]
AS
	SELECT
		CD_EMP
		,CD_NF
		,CD_FILIAL
		,NR_DOC
		,DT_ENT_SAI  
		,TIPO
		,SUM(VLR_CONTABIL)			VLR_CONTABIL
		,SUM(VLR_BASE_ICMS_CAB)		AS VLR_BASE_ICMS_CAB
		,SUM(VLR_ICMS_CAB)			VLR_ICMS_CAB
		,SUM(VLR_ISENTAS_CAB)		VLR_ISENTAS_CAB
		,SUM(VLR_OUTRAS_DESP_CAB)	VLR_OUTRAS_DESP_CAB
		,SUM(VLR_IPI_CAB)			VLR_IPI_CAB
		,SUM(VLR_ICMS_SUBS_CAB)		VLR_ICMS_SUBS_CAB
		,SUM(OUTRAS_ICMS)			AS OUTRAS_ICMS
		,SUM(VLR_FECOP)			AS VLR_FECOP
		,SUM(VLR_ICMS_DESONERADO)     AS VLR_ICMS_DESONERADO
		,SUM (BASE_ICMS_SIMPLES) AS BASE_ICMS_SIMPLES
		,MAX(isnull(ALIQ_SIMPLES,0)) AS ALIQ_SIMPLES
		,SUM (VLR_ICMS_SIMPLES) AS VLR_ICMS_SIMPLES
		,MAX(FLAG_VLR_ICMS_DESONERADO_DESCONTO) AS FLAG_VLR_ICMS_DESONERADO_DESCONTO
	FROM
		(SELECT
			 EST_NF_ENT_IT.CD_EMP
			 ,EST_NF_ENT_IT.CD_NF                                             AS CD_NF
			 ,0                                                               AS TIPO
			 ,EST_NF_ENT_IT.CD_FILIAL
			 ,EST_NF_ENT.NR_NF                                                AS NR_DOC
			 ,EST_NF_ENT_IT.DT_ENT												AS DT_ENT_SAI
			 ,CASE WHEN ISNULL(EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN SUM(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT)
				ELSE SUM(( EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT ) - EST_NF_ENT_IT.VLR_DESC)
				END                                                             AS VLR_CONTABIL
			 ,CASE WHEN EST_TRIB.ICMS = 0 THEN 0
				ELSE SUM(EST_NF_ENT_IT.VLR_BASE_ICMS)
				END                                                             AS VLR_BASE_ICMS_CAB
			 ,CASE WHEN EST_TRIB.ICMS = 0 THEN 0
				ELSE SUM(EST_NF_ENT_IT.VLR_ICMS_IT)
				END                                                             AS VLR_ICMS_CAB
			 ,CASE WHEN EST_TRIB.ISENTO = 1 THEN
					CASE WHEN ISNULL(EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN SUM(EST_NF_ENT_IT.VLR_TOT_IT)
					ELSE SUM(EST_NF_ENT_IT.VLR_TOT_IT - EST_NF_ENT_IT.VLR_DESC)
					END
				ELSE 0
				END                                                             AS VLR_ISENTAS_CAB
			 --,SUM(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS)                          AS VLR_OUTRAS_DESP_CAB

			 --Criando o valor outros
            ,CASE
                WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0
                    OR EST_TRIB.ICMS = 1
                THEN 0
                ELSE CASE
                         WHEN SUM((ROUND(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT, 2) - EST_NF_ENT_IT.VLR_DESC)) > 0
                         THEN SUM((ROUND(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT, 2) - EST_NF_ENT_IT.VLR_DESC))
                         ELSE 0
                     END
            END + CASE
                      WHEN EST_TRIB.FLAG_OUTROS = 0
                      THEN 0
                      ELSE CASE
                               WHEN SUM((ROUND(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT, 2) - EST_NF_ENT_IT.VLR_DESC)) > 0
                               THEN SUM((ROUND(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT, 2) - EST_NF_ENT_IT.VLR_DESC))
                               ELSE 0
                           END
                  END + 
			   SUM(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS) + SUM(EST_NF_ENT_IT.VLR_ICMS_SUBS) + ISNULL(SUM(EST_NF_ENT_IT.VLR_ICMS_DESONERADO), 0) AS VLR_OUTRAS_DESP_CAB

			 ,SUM(EST_NF_ENT_IT.VLR_IPI)                                      VLR_IPI_CAB
			 ,SUM(EST_NF_ENT_IT.VLR_ICMS_SUBS)                                VLR_ICMS_SUBS_CAB
			 ,CASE WHEN EST_TRIB.ICMS_RD = 1 THEN
					CASE WHEN ISNULL(EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN SUM(EST_NF_ENT_IT.VLR_TOT_IT - EST_NF_ENT_IT.VLR_BASE_ICMS)
					ELSE SUM(EST_NF_ENT_IT.VLR_TOT_IT - EST_NF_ENT_IT.VLR_DESC - EST_NF_ENT_IT.VLR_BASE_ICMS)
					END
				ELSE 0
				END                                                             AS OUTRAS_ICMS
			 ,SUM (EST_NF_ENT_IT.VLR_BASE_SUBS * ISNULL(PERC_FECOP, 0)) / 100 AS VLR_FECOP
			 ,SUM (ISNULL(EST_NF_ENT_IT.VLR_ICMS_DESONERADO,0)) VLR_ICMS_DESONERADO
			 ,CASE WHEN ISNULL( VLR_CRED_BASE_SN, 0 ) > 0 THEN
				SUM(VLR_TOT_IT) 
			  ELSE 0 END AS BASE_ICMS_SIMPLES
			 , MAX(isnull(VLR_CRED_BASE_SN,0)) AS ALIQ_SIMPLES
			 , CASE WHEN ISNULL( VLR_CRED_BASE_SN, 0 ) > 0 THEN
				SUM(VLR_CRED_ICMS_SN) 
			   ELSE 0 END AS VLR_ICMS_SIMPLES
			 ,ISNULL(EST_NF_ENT.FLAG_VLR_ICMS_DESONERADO_DESCONTO,0)		AS FLAG_VLR_ICMS_DESONERADO_DESCONTO

		 FROM
			 EST_NF_ENT
			 INNER JOIN EST_NF_ENT_IT ON
				 EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
				 EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
				 EST_NF_ENT.CD_NF	= EST_NF_ENT_IT.CD_NF AND
				 EST_NF_ENT.DT_ENT	= EST_NF_ENT_IT.DT_ENT
			 INNER JOIN GLB_NT_OP ON
				 EST_NF_ENT_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
			 INNER JOIN GLB_MOV_OP ON
				 EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
			 INNER JOIN EST_TRIB ON
				 EST_NF_ENT_IT.CD_TRIB = EST_TRIB.CD_TRIB
		 GROUP  BY
			 EST_NF_ENT_IT.CD_EMP
			 ,EST_NF_ENT_IT.CD_NF
			 ,EST_NF_ENT_IT.DT_ENT
			 ,EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO
			 ,EST_NF_ENT_IT.CD_FILIAL
			 ,EST_NF_ENT.NR_NF
			 ,EST_TRIB.ICMS
			 ,EST_TRIB.ISENTO
			 ,EST_TRIB.ICMS_RD			 
			 ,VLR_CRED_BASE_SN
			 ,EST_TRIB.FLAG_OUTROS
			 ,EST_TRIB.SUBS_TRIB , EST_TRIB.SUBS_TRIB_ANT,
			 EST_NF_ENT.FLAG_VLR_ICMS_DESONERADO_DESCONTO
		 UNION
		 SELECT
			 EST_NF_SAI_IT.CD_EMP
			 ,EST_NF_SAI_IT.CD_NF                                                                                                     AS CD_NF
			 ,1                                                                                                                    AS TIPO
			 ,EST_NF_SAI_IT.CD_FILIAL
			 ,EST_NF_SAI.NF_NF                                                                                                     AS NR_DOC
			 ,EST_NF_SAI_IT.DT_EMI_NF
			 ,SUM(( EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT ) - EST_NF_SAI_IT.VLR_DESC_IT)                                      AS VLR_CONTABIL
			 ,SUM(EST_NF_SAI_IT.VLR_BASE_ICMS_IT)                                                                                  AS VLR_BASE_ICMS_CAB
			 ,SUM(EST_NF_SAI_IT.VLR_ICMS_IT)                                                                                       AS VLR_ICMS_CAB
			 ,CASE WHEN EST_TRIB.ISENTO = 1 THEN
					CASE WHEN ISNULL(FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN SUM(EST_NF_SAI_IT.VLR_TOT_IT)
					ELSE SUM(EST_NF_SAI_IT.VLR_TOT_IT - VLR_DESC_IT)
					END
				ELSE 0
				END                                                                                                                  AS VLR_ISENTAS_CAB
			 ,SUM(EST_NF_SAI_IT.VLR_OUTROS_DESPESAS)                                                                               AS VLR_OUTRAS_DESP_CAB
			 ,
			 -- SUM( ROUND(ISNULL(((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT) - dbo.EST_NF_SAI_IT.VLR_DESC_IT) * EST_NF_SAI_IT.PERC_IPI, 0)/100,2))   AS  VLR_IPI_CAB ,
			 SUM(ROUND(ISNULL(( EST_NF_SAI_IT.VLR_TOT_IT - dbo.EST_NF_SAI_IT.VLR_DESC_IT ) * EST_NF_SAI_IT.PERC_IPI, 0) / 100, 2)) AS VLR_IPI_CAB
			 ,SUM(EST_NF_SAI_IT.VLR_ICMS_SUBS)                                                                                     VLR_ICMS_SUBS_CAB
			 ,0                                                                                                                    AS OUTRAS_ICMS
			 ,SUM (EST_NF_SAI_IT.VLR_BASE_SUBS * ISNULL(PERC_FECOP, 0)) / 100 AS VLR_FECOP
			 ,SUM (ISNULL(EST_NF_SAI_IT.VLR_ICMS_DESONERADO,0)) VLR_ICMS_DESONERADO
			 ,0 AS BASE_ICMS_SIMPLES
			 ,0 AS ALIQ_SIMPLES
			 ,0 VLR_ICMS_SIMPLES
			 ,ISNULL(EST_NF_SAI.FLAG_VLR_ICMS_DESONERADO_DESCONTO,0)		AS FLAG_VLR_ICMS_DESONERADO_DESCONTO

		 FROM
			 EST_NF_SAI
			 INNER JOIN EST_NF_SAI_IT ON
				 EST_NF_SAI.CD_EMP		= EST_NF_SAI_IT.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL	= EST_NF_SAI_IT.CD_FILIAL AND
				 EST_NF_SAI.CD_NF		= EST_NF_SAI_IT.CD_NF AND
				 EST_NF_SAI.DT_EMI_NF	= EST_NF_SAI_IT.DT_EMI_NF
			 INNER JOIN GLB_NT_OP ON
				 EST_NF_SAI_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
			 INNER JOIN GLB_MOV_OP ON
				 EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
			 INNER JOIN EST_TRIB ON
				 EST_NF_SAI_IT.CD_TRIB = EST_TRIB.CD_TRIB
			 LEFT OUTER JOIN PDV_VD_EST_NF_SAI ON
				 EST_NF_SAI.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND
				 EST_NF_SAI.CD_NF = PDV_VD_EST_NF_SAI.CD_NF
		 GROUP  BY
			 EST_NF_SAI_IT.CD_EMP
			 ,EST_NF_SAI_IT.CD_NF
			 ,EST_NF_SAI_IT.CD_FILIAL
			 ,EST_NF_SAI.NF_NF
			 ,EST_TRIB.ICMS
			 ,EST_TRIB.ISENTO
			 ,EST_NF_SAI_IT.DT_EMI_NF
			 ,FLAG_NF_PROD_TOTAL_BRUTO
			 ,PDV_VD_EST_NF_SAI.CD_NF
			 ,EST_NF_SAI.FLAG_VLR_ICMS_DESONERADO_DESCONTO) TEMP
	GROUP  BY
		CD_EMP
		,CD_NF
		,CD_FILIAL
		,DT_ENT_SAI
		,NR_DOC
		,TIPO

GO 
