/****** Object:  View [dbo].[V_EST_NF_ENT_CONTABIL]    Script Date: 12/27/2013 10:21:12 ******/
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

--select * from V_EST_NF_ENT_CONTABIL where cd_emp = 1 and dt_cad ='2018-12-11' and cd_filial = 14
CREATE VIEW [dbo].[V_EST_NF_ENT_CONTABIL] AS
--Versao 2464
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,
	VLR_NF,
	ISNULL(round(
	CONVERT(FLOAT,V_EST_NF_ENT_CAB.VLR_NF)/CONVERT(FLOAT,SUM(DISTINCT dbo.PG_DESP_RAT.VLR_DESP))
	
	* SUM(DISTINCT dbo.PG_DESP_RAT.VLR_DESP),2),0) 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
	--CASE WHEN TP_MOV = 21 THEN 0 ELSE VLR_ICMS_ITENS END AS VLR_ICMS_ITENS,
	--CASE WHEN TP_MOV = 21 THEN 0 ELSE VLR_ICMS_SUBS_ITENS END AS VLR_ICMS_SUBS_ITENS,

	,ISNULL(RSIMPOSTO.VLR_ICMS_ITENS, 0)         AS VLR_ICMS_ITENS
	,ISNULL(RSIMPOSTO.VLR_ICMS_SUBS_ITENS, 0)         AS VLR_ICMS_SUBS_ITENS,

	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
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 ( SELECT  
						V_EST_ANALISE_CFOP.CD_EMP
						,V_EST_ANALISE_CFOP.CD_FILIAL
						,V_EST_ANALISE_CFOP.CD_NF
			   ,SUM(VLR_ICMS) VLR_ICMS_ITENS , SUM(VLR_ICMS_SUBS) VLR_ICMS_SUBS_ITENS
	           FROM V_EST_ANALISE_CFOP GROUP BY
			   V_EST_ANALISE_CFOP.CD_EMP
						,V_EST_ANALISE_CFOP.CD_FILIAL
						,V_EST_ANALISE_CFOP.CD_NF ) RSIMPOSTO
			  ON
			  
			 V_EST_NF_ENT_CAB.CD_EMP = RSIMPOSTO.CD_EMP AND
			V_EST_NF_ENT_CAB.CD_FILIAL = RSIMPOSTO.CD_FILIAL AND
			V_EST_NF_ENT_CAB.CD_NF = RSIMPOSTO.CD_NF

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 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
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,
	RSIMPOSTO.VLR_ICMS_ITENS,	
	RSIMPOSTO.VLR_ICMS_SUBS_ITENS,
	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  