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

CREATE VIEW [dbo].[V_PG_DESP_RAT_CONTAS_CENTRO_CUSTO_PAGAR_CONTABIL]
AS
SELECT     
	dbo.PG_CRED.CD_EMP, 
	dbo.PG_CRED.CD_FILIAL, 
	dbo.PG_DESP_RAT.CD_DESP_REC, 
	dbo.PG_CRED.DT_CAD,
	dbo.PG_CRED.PARC,
	dbo.CTB_CENTRO_CUSTO.CD_CENTRO_CTB,  
     dbo.CTB_CENTRO_CUSTO.CNT_CENTRO_CTB, 
	dbo.CTB_CENTRO_CUSTO.DS_CENTRO_CTB, 
	SUM(DISTINCT dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO.VLR_RAT_DESP) AS VLR_RAT_CENTRO_CUSTO, 
	dbo.PG_CRED.HIST, 
	dbo.PG_CRED.CD_FORN,
	dbo.PG_CRED.NR_TIT,
	isnull(PG_CRED_NF.NR_NF,0) AS NR_NF,
	PG_CRED.NR_DP,
	PG_CRED.ANO,
	CD_CENTRO_CTB_REDU,
	PG_CRED.CD_PG_CRED,
	GLB_DESP_REC.CD_CNT_CTB_RED
FROM         
	dbo.CTB_CENTRO_CUSTO INNER JOIN dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO ON 
	dbo.CTB_CENTRO_CUSTO.CD_EMP = dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP AND 
    dbo.CTB_CENTRO_CUSTO.CD_CENTRO_CTB = dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB 
	INNER JOIN dbo.PG_CRED 
	INNER JOIN dbo.PG_DESP_RAT_PG_CRED ON 
	dbo.PG_DESP_RAT_PG_CRED.CD_EMP = dbo.PG_CRED.CD_EMP AND 
    dbo.PG_DESP_RAT_PG_CRED.CD_FILIAL = dbo.PG_CRED.CD_FILIAL AND 
	dbo.PG_DESP_RAT_PG_CRED.NR_DP = dbo.PG_CRED.NR_DP AND 
    dbo.PG_DESP_RAT_PG_CRED.ANO = dbo.PG_CRED.ANO 
	INNER 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 ON 
	dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP = dbo.PG_DESP_RAT.CD_EMP AND 
    dbo.PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP = dbo.PG_DESP_RAT.CD_RAT_DESP
	LEFT OUTER JOIN PG_CRED_NF ON
	dbo.PG_CRED.CD_EMP =  dbo.PG_CRED_NF.CD_EMP  AND 
    dbo.PG_CRED.CD_FILIAL = dbo.PG_CRED_NF.CD_FILIAL AND 
	dbo.PG_CRED.NR_DP = dbo.PG_CRED_NF.NR_DP  AND 
    dbo.PG_CRED.ANO  = dbo.PG_CRED_NF.ANO 
	INNER JOIN V_PG_CRED_NF_QTDE_DOC_RELACIONADO ON 
	PG_CRED_NF.CD_EMP = V_PG_CRED_NF_QTDE_DOC_RELACIONADO.CD_EMP AND 
    PG_CRED_NF.CD_FILIAL = V_PG_CRED_NF_QTDE_DOC_RELACIONADO.CD_FILIAL AND 
    PG_CRED_NF.NR_DP = V_PG_CRED_NF_QTDE_DOC_RELACIONADO.NR_DP AND 
    PG_CRED_NF.ANO = V_PG_CRED_NF_QTDE_DOC_RELACIONADO.ANO
	INNER 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
WHERE     
	dbo.PG_CRED.STS_DP <> 3
	AND PG_CRED.FLAG_NAO_EXP_PROVISIONAMENTO_CONTABILIDADE=0
GROUP BY 
	dbo.PG_DESP_RAT.CD_DESP_REC, 
	dbo.CTB_CENTRO_CUSTO.CNT_CENTRO_CTB, 
	dbo.CTB_CENTRO_CUSTO.DS_CENTRO_CTB, 
	dbo.PG_CRED.CD_FILIAL, 
	dbo.PG_CRED.CD_EMP, 
	dbo.PG_CRED.DT_CAD, 
	dbo.PG_CRED.HIST, 
	dbo.PG_CRED.CD_FORN,
	PG_CRED_NF.NR_NF,
	dbo.PG_CRED.NR_TIT,
	dbo.PG_CRED.NR_DP,
	dbo.PG_CRED.ANO,
	dbo.CTB_CENTRO_CUSTO.CD_CENTRO_CTB,
	CD_CENTRO_CTB_REDU,
	dbo.PG_CRED.PARC,
	PG_CRED.CD_PG_CRED,GLB_DESP_REC.CD_CNT_CTB_RED
 


