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

CREATE VIEW V_EST_NF_ENT_CONTABIL AS

SELECT 
	V_EST_NF_ENT_CAB.CD_EMP,
	V_EST_NF_ENT_CAB.CD_FILIAL,
	V_EST_NF_ENT_CAB.DT_CAD,
	V_EST_NF_ENT_CAB.DT_ENT,
	V_EST_NF_ENT_CAB.CD_FORN,
	V_EST_NF_ENT_CAB.NM_ORIG AS RZ_FORN,
	'' AS HIST,
	ISNULL(PG_DESP_RAT.CD_DESP_REC,0) AS CD_DESP_REC,
	ISNULL(NR_CNT,'') AS NR_CNT,
	ISNULL(CD_CNT_CTB_RED,0) AS CD_CNT_CTB_RED,
	ISNULL(DS_DESP_REC,'') AS DS_DESP_REC,
	V_EST_NF_ENT_CAB.VLR_NF,
 

	CASE WHEN ISNULL(SUM(PG_DESP_RAT.VLR_DESP),0) = 0 THEN   V_EST_NF_ENT_CAB.VLR_NF  ELSE
	SUM(PG_DESP_RAT.VLR_DESP) END AS VLR_RAT_DESP,

	ISNULL(GLB_DESP_REC.RATEIO_CENTRO_CUSTO,0) AS RATEIO_CENTRO_CUSTO,
	V_EST_NF_ENT_CAB.NR_NF,
	NM_FANT,
	VLR_BASE_PIS_COFINS AS VLR_BASE_PIS_COFINS,
	V_EST_NF_ENT_CAB.TP_MOV,
	TP_MOV_DS,
	V_EST_NF_ENT_CAB.DEST_ORIG,	
	CONTASPAGAR,	
	CASE WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 1 THEN
	  ISNULL((SELECT     
			CD_CTB_CNT
		FROM          
			PG_FORN_CNT_CTB
		WHERE      
			PG_FORN_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			PG_FORN_CNT_CTB.CD_FORN = V_EST_NF_ENT_CAB.CD_FORN),'')
	WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 2 THEN
	  ISNULL((SELECT     
				CD_CTB_CNT
		FROM          
				PRC_FILIAL_CNT_CTB
		WHERE      
			PRC_FILIAL_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			PRC_FILIAL_CNT_CTB.CD_FILIAL = V_EST_NF_ENT_CAB.CD_FORN AND
			PRC_FILIAL_CNT_CTB.TP_CNT_CTB = 42 ),'')
	WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 0 THEN
	  ISNULL((SELECT     
			CD_CTB_CNT
		FROM          
			RC_CLI_CNT_CTB
		WHERE      
			RC_CLI_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			RC_CLI_CNT_CTB.CD_CLI = V_EST_NF_ENT_CAB.CD_FORN AND 
			RC_CLI_CNT_CTB.TP_CNT_CTB=0),'')
	ELSE 
		'' 
	END AS CD_CTB_CNT,
	CASE WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 1 THEN
	  ISNULL((SELECT     
			 CD_CTB_CNT_RED
		FROM          
			PG_FORN_CNT_CTB
		WHERE      
			PG_FORN_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			PG_FORN_CNT_CTB.CD_FORN = V_EST_NF_ENT_CAB.CD_FORN),0)
	WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 2 THEN
	  ISNULL((SELECT     
				 CD_CTB_CNT_RED
		FROM          
				PRC_FILIAL_CNT_CTB
		WHERE      
			PRC_FILIAL_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			PRC_FILIAL_CNT_CTB.CD_FILIAL = V_EST_NF_ENT_CAB.CD_FORN AND
			PRC_FILIAL_CNT_CTB.TP_CNT_CTB = 42 ),0)
	WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 0 THEN
	  ISNULL((SELECT     
			 CD_CTB_CNT_RED
		FROM          
			RC_CLI_CNT_CTB
		WHERE      
			RC_CLI_CNT_CTB.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP AND 
			RC_CLI_CNT_CTB.CD_CLI = V_EST_NF_ENT_CAB.CD_FORN AND 
			RC_CLI_CNT_CTB.TP_CNT_CTB=0),0)
	ELSE 
		'0' 
	END AS  CD_CTB_CNT_RED,
	FLAG_CONTABILIZAR_COMO_DESPESAS,
	V_EST_NF_ENT_CAB.CD_MOV_OP AS CD_MOV_OP,
	NR_DP_PAG	 AS NR_DP,
	ANO_DP_PAG AS ANO_DP,
	ISNULL(PG_CRED.CD_PG_CRED,0) AS PG_CRED
	,SUM(ITENS_IMPOSTO.VLR_ICMS_ITENS) AS VLR_ICMS_ITENS
	,SUM(ITENS_IMPOSTO.VLR_ICMS_ITENS_DARE) AS VLR_ICMS_SUBS_ITENS
FROM 
	V_EST_NF_ENT_CAB  
	LEFT OUTER JOIN PG_DESP_RAT_PG_CRED ON
	V_EST_NF_ENT_CAB.CD_EMP = PG_DESP_RAT_PG_CRED.CD_EMP AND
	V_EST_NF_ENT_CAB.CD_FILIAL = PG_DESP_RAT_PG_CRED.CD_FILIAL AND
	V_EST_NF_ENT_CAB.NR_DP_PAG = PG_DESP_RAT_PG_CRED.NR_DP AND 
	V_EST_NF_ENT_CAB.ANO_DP_PAG = PG_DESP_RAT_PG_CRED.ANO  	
	LEFT OUTER JOIN dbo.PG_DESP_RAT ON 
	dbo.PG_DESP_RAT.CD_EMP = dbo.PG_DESP_RAT_PG_CRED.CD_EMP AND 
    dbo.PG_DESP_RAT.CD_RAT_DESP = dbo.PG_DESP_RAT_PG_CRED.CD_RAT_DESP 
	LEFT OUTER JOIN dbo.GLB_DESP_REC ON 
	dbo.GLB_DESP_REC.CD_EMP = dbo.PG_DESP_RAT.CD_EMP AND 
    dbo.GLB_DESP_REC.CD_DESP_REC = dbo.PG_DESP_RAT.CD_DESP_REC	
	LEFT OUTER JOIN PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT ON
	V_EST_NF_ENT_CAB.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
	V_EST_NF_ENT_CAB.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
	V_EST_NF_ENT_CAB.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF	
	LEFT OUTER JOIN PDV_VD_EST_NF_ENT ON
	V_EST_NF_ENT_CAB.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP AND
	V_EST_NF_ENT_CAB.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL AND
	V_EST_NF_ENT_CAB.CD_NF = PDV_VD_EST_NF_ENT.CD_NF
	OUTER APPLY (
			SELECT FILIAL.REGIME_TRIBUTARIO, EST_TRIB.ICMS, GLB_MOV_OP.TP_MOV, EST_TRIB.SUBS_TRIB, 
				CASE WHEN ISNULL(FILIAL.REGIME_TRIBUTARIO,0) = 1  THEN
				 0
				ELSE
					CASE WHEN (EST_TRIB.ICMS = 1 OR GLB_MOV_OP.TP_MOV IN (58)) OR (EST_TRIB.SUBS_TRIB = 1 AND V_EST_NF_ENT_CAB.TP_MOV IN(11)) THEN
		
						SUM(IT.VLR_ICMS_IT)
					ELSE
						0
					END															  
				END VLR_ICMS_ITENS,
			0 AS VLR_ICMS_ITENS_DARE
		FROM EST_NF_ENT_IT IT
		INNER JOIN EST_TRIB ON IT.CD_TRIB = EST_TRIB.CD_TRIB
		INNER JOIN GLB_MOV_OP ON V_EST_NF_ENT_CAB.CD_MOV_OP = GLB_MOV_OP.CD_MOV_OP
		INNER JOIN PRC_FILIAL FILIAL ON FILIAL.CD_FILIAL = IT.CD_FILIAL AND FILIAL.CD_EMP = IT.CD_EMP
		LEFT OUTER JOIN EST_NF_ENT_DAR_IT ON
											IT.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP AND
											IT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL AND
											IT.CD_NF = EST_NF_ENT_DAR_IT.CD_NF AND
											IT.CD_PROD = EST_NF_ENT_DAR_IT.CD_PROD
		WHERE IT.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP
		AND IT.CD_FILIAL = V_EST_NF_ENT_CAB.CD_FILIAL
		AND IT.CD_NF = V_EST_NF_ENT_CAB.CD_NF
		AND EST_NF_ENT_DAR_IT.CD_NF IS NULL
		GROUP BY FILIAL.REGIME_TRIBUTARIO, EST_TRIB.ICMS, GLB_MOV_OP.TP_MOV, EST_TRIB.SUBS_TRIB

		UNION

		SELECT FILIAL.REGIME_TRIBUTARIO, EST_TRIB.ICMS, GLB_MOV_OP.TP_MOV, EST_TRIB.SUBS_TRIB, 0 AS VLR_ICMS_ITENS,
				SUM(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS) AS VLR_ICMS_ITENS_DARE
		FROM EST_NF_ENT_IT IT
		INNER JOIN EST_TRIB ON IT.CD_TRIB = EST_TRIB.CD_TRIB
		INNER JOIN GLB_MOV_OP ON V_EST_NF_ENT_CAB.CD_MOV_OP = GLB_MOV_OP.CD_MOV_OP
		INNER JOIN PRC_FILIAL FILIAL ON FILIAL.CD_FILIAL = IT.CD_FILIAL AND FILIAL.CD_EMP = IT.CD_EMP
		INNER JOIN EST_NF_ENT_DAR_IT ON
											IT.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP AND
											IT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL AND
											IT.CD_NF = EST_NF_ENT_DAR_IT.CD_NF AND
											IT.CD_PROD = EST_NF_ENT_DAR_IT.CD_PROD
		WHERE IT.CD_EMP = V_EST_NF_ENT_CAB.CD_EMP
		AND IT.CD_FILIAL = V_EST_NF_ENT_CAB.CD_FILIAL
		AND IT.CD_NF = V_EST_NF_ENT_CAB.CD_NF
		GROUP BY FILIAL.REGIME_TRIBUTARIO, EST_TRIB.ICMS, GLB_MOV_OP.TP_MOV, EST_TRIB.SUBS_TRIB
	
	) ITENS_IMPOSTO
	LEFT OUTER JOIN PG_CRED ON  PG_CRED.CD_EMP = PG_DESP_RAT_PG_CRED.CD_EMP
								AND PG_CRED.CD_FILIAL = PG_DESP_RAT_PG_CRED.CD_FILIAL 
								AND PG_CRED.NR_DP =PG_DESP_RAT_PG_CRED.NR_DP
								AND PG_CRED.ANO = PG_DESP_RAT_PG_CRED.ANO
								AND PG_CRED.PARC = 1
WHERE
	PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF IS NULL --Nao pega devolucao de vendas por requisicao (novo modo)
	AND PDV_VD_EST_NF_ENT.CD_NF IS NULL --Nao pega devolucao de vendas sem requisicao (modo antigo)
	AND V_EST_NF_ENT_CAB.TP_MOV <>CASE
    WHEN V_EST_NF_ENT_CAB.DEST_ORIG = 1
    THEN -1
    ELSE 11 END --Ignorar devolucao de vendas
	AND V_EST_NF_ENT_CAB.FLAG_N_EXPORTAR_CONTABIL = 0
	and V_EST_NF_ENT_CAB.cd_emp = 1
GROUP BY
	V_EST_NF_ENT_CAB.CD_FILIAL,
	V_EST_NF_ENT_CAB.DT_CAD,
	V_EST_NF_ENT_CAB.DT_ENT,
	V_EST_NF_ENT_CAB.CD_FORN,
	V_EST_NF_ENT_CAB.NM_ORIG,
	PG_DESP_RAT.CD_DESP_REC,
	NR_CNT,
	CD_CNT_CTB_RED,
	DS_DESP_REC,		
	PG_DESP_RAT.CD_DESP_REC,
	NR_CNT,
	CD_CNT_CTB_RED,
	DS_DESP_REC,
	V_EST_NF_ENT_CAB.TP_MOV,
	TP_MOV_DS,
	V_EST_NF_ENT_CAB.DS_MOV_OP,
	V_EST_NF_ENT_CAB.DEST_ORIG,
	VLR_BASE_PIS_COFINS,
	CONTASPAGAR,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	V_EST_NF_ENT_CAB.NR_NF,
	NM_FANT,
	V_EST_NF_ENT_CAB.VLR_NF,
	V_EST_NF_ENT_CAB.CD_EMP,
	FLAG_CONTABILIZAR_COMO_DESPESAS,
	V_EST_NF_ENT_CAB.CD_MOV_OP,
	NR_DP_PAG	 ,
	ANO_DP_PAG,  
	PG_CRED.CD_PG_CRED