IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DESP_RAT_CONTAS_CENTRO_CUSTO_BANCARIO_CONTABIL]'))
DROP VIEW [dbo].[V_PG_DESP_RAT_CONTAS_CENTRO_CUSTO_BANCARIO_CONTABIL]
GO
GO
GO
--V_PG_DESP_RAT_CONTAS_CENTRO_CUSTO_BANCARIO_CONTABIL
CREATE VIEW [dbo].[V_PG_DESP_RAT_CONTAS_CENTRO_CUSTO_BANCARIO_CONTABIL]
AS
SELECT 
	PG_DESP_RAT.CD_EMP,
	PG_DESP_RAT.CD_RAT_DESP,
	PG_DESP_RAT.CD_DESP_REC,
	GLB_DESP_REC.NR_CNT,
	GLB_DESP_REC.DS_DESP_REC, 
	BC_LANC.CD_BC_LANC,
	BC_LANC.DT_LANCTO,
	SUM( CASE WHEN ISNULL(C.VLR_RAT_DESP,0) > 0 THEN
			(convert(float,PG_DESP_RAT.VLR_DESP) * (convert(float,C.VLR_RAT_DESP) / convert(float,PG_DESP_RAT.VLR_DESP)*100)/100)
		ELSE 
			PG_DESP_RAT.VLR_DESP  
		END) AS VLR_DESP,
	SUM(DISTINCT PG_DESP_RAT.PERC_DESP) AS PERC_DESP,
	SUM((convert(float,PG_DESP_RAT.VLR_DESP) * (convert(float,C.VLR_RAT_DESP) / convert(float,PG_DESP_RAT.VLR_DESP)*100)/100))   VLR_DESP_PAGO,	
	PG_DESP_RAT.MES_ANO_REF,
	PG_DESP_RAT.CD_FILIAL,
	PG_DESP_RAT_BC_LANC.CD_BC_LANC AS DOC_ORIGEM,
	BC_LANC.CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	C.CD_CENTRO_CTB,
	CTB_CENTRO_CUSTO.DS_CENTRO_CTB,
	CTB_CENTRO_CUSTO.CNT_CENTRO_CTB,
	CD_CENTRO_CTB_REDU,
	GLB_DESP_REC.CD_CNT_CTB_RED
FROM 
	PG_DESP_RAT 
	INNER JOIN GLB_DESP_REC ON 
	PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP AND 
	PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC 
	INNER JOIN PG_DESP_RAT_BC_LANC ON 
	PG_DESP_RAT.CD_EMP = PG_DESP_RAT_BC_LANC.CD_EMP AND 
	PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_BC_LANC.CD_RAT_DESP 
	LEFT JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO C ON
	PG_DESP_RAT.CD_EMP  = C.CD_EMP AND 
	PG_DESP_RAT.CD_RAT_DESP  = C.CD_RAT_DESP
	LEFT JOIN CTB_CENTRO_CUSTO ON 
	   CTB_CENTRO_CUSTO.CD_EMP = C.CD_EMP AND 
	   CTB_CENTRO_CUSTO.CD_CENTRO_CTB = C.CD_CENTRO_CTB
	INNER JOIN BC_LANC ON 
	PG_DESP_RAT_BC_LANC.CD_EMP = BC_LANC.CD_EMP AND 
	PG_DESP_RAT_BC_LANC.CD_BC_LANC = BC_LANC.CD_BC_LANC 
	--WHERE PG_DESP_RAT.cd_rat_desp =  198084   
GROUP BY
	PG_DESP_RAT.CD_EMP,
	PG_DESP_RAT.CD_RAT_DESP,
	PG_DESP_RAT.CD_DESP_REC,
	GLB_DESP_REC.NR_CNT,
	GLB_DESP_REC.DS_DESP_REC, 
	BC_LANC.DT_LANCTO,
	PG_DESP_RAT.MES_ANO_REF,
	PG_DESP_RAT.CD_FILIAL,
	PG_DESP_RAT_BC_LANC.CD_BC_LANC,
	BC_LANC.CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	C.CD_CENTRO_CTB,
	PG_DESP_RAT.MES_ANO_REF,
	BC_LANC.CD_BC_LANC,
	CTB_CENTRO_CUSTO.DS_CENTRO_CTB,
	CTB_CENTRO_CUSTO.CNT_CENTRO_CTB,
	CD_CENTRO_CTB_REDU,
	GLB_DESP_REC.CD_CNT_CTB_RED

GO