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

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 250428
'Job.................:  
'Nome................: [V_PG_DESP_RAT_CONTAS_PAGAR_CONTABIL]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 28/04/2025
'Criado por..........: 
'Alterado por........: Marcos Barros
'Versionado por......: Marcos Barros
'=======================================================================================
 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 AS DT_EMIS2,
	CASE WHEN ISNULL(PRC_EMP_CONFIG.VALOR ,0)  = 1 THEN  dbo.PG_CRED.DT_CAD ELSE dbo.PG_CRED.DT_EMIS END AS DT_EMIS ,
	--COALESCE(PG_CRED.DT_CAD, PG_CRED.DT_EMIS) AS 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 

	LEFT JOIN  PRC_EMP_CONFIG ON  
    PG_CRED.CD_EMP = PRC_EMP_CONFIG.CD_EMP 
    AND PRC_EMP_CONFIG.CD_CHAVE = 'DATA_LANCAMENTO_CONTAS_A_PAGAR' 

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,
	PRC_EMP_CONFIG.VALOR
GO


