/****** Object:  View [dbo].[V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL]    Script Date: 07/15/2013 09:31:43 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL]'))
DROP VIEW [dbo].[V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL]
GO
--select * from V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL WHERE NR_NF = 1351
CREATE VIEW [dbo].[V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL]
AS
SELECT     
	dbo.PG_CRED.CD_EMP, 
	dbo.PG_CRED.CD_FILIAL, 
	dbo.PG_CRED.DT_CAD, 
	dbo.PG_CRED.DT_EMIS, 
	dbo.PG_CRED.CD_FORN, 
	dbo.PG_FORN.RZ_FORN, 
    dbo.PG_CRED.HIST, 
	dbo.PG_DESP_RAT.CD_DESP_REC, 
	dbo.GLB_DESP_REC.NR_CNT, 
	dbo.GLB_DESP_REC.CD_CNT_CTB_RED, 
    dbo.GLB_DESP_REC.DS_DESP_REC, 
	SUM(DISTINCT dbo.PG_DESP_RAT.VLR_DESP) AS VLR_RAT_DESP, 
	dbo.GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	isnull(PG_CRED_NF.NR_NF,0) AS NR_NF,
	PG_CRED.NR_DP,
	PG_CRED.ANO,
    PG_CRED.CD_PG_CRED AS CD_PG_CRED,
	MAX(ISNULL(PG_CRED.NR_TIT,'')) AS NR_TIT,
    dbo.PG_CRED.PARC, 
	PG_CRED.VLR_DP
FROM         
	dbo.PG_CRED INNER JOIN dbo.PG_FORN ON 
	dbo.PG_CRED.CD_EMP = dbo.PG_FORN.CD_EMP AND 
	dbo.PG_CRED.CD_FORN = dbo.PG_FORN.CD_FORN 
	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 
	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
	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 
	LEFT OUTER JOIN PG_CRED_EST_NF_ENT ON
	dbo.PG_CRED.CD_EMP =  dbo.PG_CRED_EST_NF_ENT.CD_EMP  AND 
    dbo.PG_CRED.CD_FILIAL = dbo.PG_CRED_EST_NF_ENT.CD_FILIAL AND 
	dbo.PG_CRED.NR_DP = dbo.PG_CRED_EST_NF_ENT.NR_DP  AND 
    dbo.PG_CRED.ANO  = dbo.PG_CRED_EST_NF_ENT.ANO 
WHERE     
	dbo.PG_CRED.STS_DP <> 3
	AND GLB_DESP_REC.IG_EXP_CTB=0
	AND PG_CRED_EST_NF_ENT.ANO IS NULL
	and PG_CRED.FLAG_NAO_EXP_PROVISIONAMENTO_CONTABILIDADE=0
	
GROUP BY 
	dbo.PG_CRED.CD_FORN, 
	    PG_CRED.CD_PG_CRED,
	dbo.PG_CRED.HIST, 
	dbo.PG_DESP_RAT.CD_DESP_REC, 
	dbo.GLB_DESP_REC.NR_CNT, 
    dbo.GLB_DESP_REC.CD_CNT_CTB_RED, 
	dbo.GLB_DESP_REC.DS_DESP_REC, 
	dbo.PG_FORN.RZ_FORN, 
    dbo.GLB_DESP_REC.RATEIO_CENTRO_CUSTO, 
	dbo.PG_CRED.CD_EMP, 
	dbo.PG_CRED.CD_FILIAL, 
	dbo.PG_CRED.DT_CAD,
	dbo.PG_CRED.DT_EMIS,
	PG_CRED_NF.NR_NF,
	PG_CRED.NR_DP,
	PG_CRED.ANO,
	PG_CRED.PARC ,
	PG_CRED.VLR_DP