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.DT_EMI_NF
	,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
	,V_EST_NF_ENT_CAB.BX_EST
	,V_EST_NF_ENT_CAB.ATU_EST
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
LEFT OUTER JOIN PRC_EMP_CONFIG ON V_EST_NF_ENT_CAB.CD_EMP = PRC_EMP_CONFIG.CD_EMP
	AND PRC_EMP_CONFIG.CD_CHAVE = 'DATA_LANCAMENTO_CONTAS_A_PAGAR'
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
	,PRC_EMP_CONFIG.VALOR
	,V_EST_NF_ENT_CAB.DT_EMI_NF
	,V_EST_NF_ENT_CAB.BX_EST
	,V_EST_NF_ENT_CAB.ATU_EST
GO