/****** Object:  View [dbo].[V_PG_DESP_RAT_LANCTO]    Script Date: 08/14/2012 15:58:16 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DESP_RAT_LANCTO]'))
DROP VIEW [dbo].[V_PG_DESP_RAT_LANCTO]
GO
GO
--SELECT * FROM V_PG_DESP_RAT_LANCTO WHERE MES_ANO_REF = '2012-05-01' AND CD_DESP_REC = 36 ORDER BY CD_DESP_REC
CREATE VIEW [dbo].[V_PG_DESP_RAT_LANCTO] AS
SELECT 
	DESP.CD_EMP,
	DESP.CD_FILIAL, 
	DESP.MES_ANO_REF,
	DESP.DT_VENCTO,
	DESP.CD_DESP_REC,
	DESP.NR_CNT,
	DESP.DS_DESP_REC,
	DESP.VLR_DESP,
	DESP.PERC_DESP,
	DESP.VLR_DESP_PAGO,
	CCUSTO.CD_CENTRO_CTB,
	CCUSTO.DS_CENTRO_CTB,
	CCUSTO.VLR_RAT_DESP,
	CCUSTO.RATEIO_CENTRO_CUSTO,
	CCUSTO.TIPO_LANCTO,
	CCUSTO.DOC_ORIGEM NR_DOC_ORIG
FROM
	(SELECT     
		V_PG_DESP_RAT.CD_EMP, 
		V_PG_DESP_RAT.CD_FILIAL, 
		V_PG_DESP_RAT.DOC_ORIGEM, 
		V_PG_DESP_RAT.MES_ANO_REF,
		V_PG_DESP_RAT.CD_RAT_DESP,	
		V_PG_DESP_RAT.CD_DESP_REC, 
		V_PG_DESP_RAT.TIPO_LANCTO, 
		ISNULL(PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB,0) AS CD_CENTRO_CTB, 
		ISNULL(CTB_CENTRO_CUSTO.DS_CENTRO_CTB,'RATEIO DE CENTRO DE CUSTO NÃO INFORMADO') AS DS_CENTRO_CTB, 
		ISNULL(SUM(PG_DESP_RAT_CTB_CENTRO_CUSTO.VLR_RAT_DESP),0) AS VLR_RAT_DESP,
		V_PG_DESP_RAT.RATEIO_CENTRO_CUSTO
		
	FROM         
		(SELECT	
			V_PG_DESP_RAT.CD_EMP, 
			V_PG_DESP_RAT.CD_FILIAL, 
			V_PG_DESP_RAT.DOC_ORIGEM, 
			V_PG_DESP_RAT.MES_ANO_REF,
			V_PG_DESP_RAT.CD_RAT_DESP,	
			V_PG_DESP_RAT.CD_DESP_REC, 
			V_PG_DESP_RAT.TIPO_LANCTO, 
			V_PG_DESP_RAT.RATEIO_CENTRO_CUSTO,
			V_PG_DESP_RAT.CD_CENTRO_CTB
		FROM	
			V_PG_DESP_RAT
		GROUP BY
			V_PG_DESP_RAT.CD_EMP, 
			V_PG_DESP_RAT.CD_FILIAL, 
			V_PG_DESP_RAT.DOC_ORIGEM, 
			V_PG_DESP_RAT.MES_ANO_REF,
			V_PG_DESP_RAT.CD_RAT_DESP,	
			V_PG_DESP_RAT.CD_DESP_REC, 
			V_PG_DESP_RAT.TIPO_LANCTO, 
			V_PG_DESP_RAT.RATEIO_CENTRO_CUSTO,
			V_PG_DESP_RAT.CD_CENTRO_CTB) V_PG_DESP_RAT 
		LEFT OUTER JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO ON
		V_PG_DESP_RAT.CD_EMP = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP AND 
		V_PG_DESP_RAT.CD_CENTRO_CTB = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB  AND 
		V_PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP
		LEFT OUTER JOIN CTB_CENTRO_CUSTO ON 
		PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP = CTB_CENTRO_CUSTO.CD_EMP AND 
		PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB = CTB_CENTRO_CUSTO.CD_CENTRO_CTB 
	GROUP BY 
		V_PG_DESP_RAT.CD_EMP, 
		PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB, 
		CTB_CENTRO_CUSTO.DS_CENTRO_CTB, 
		V_PG_DESP_RAT.CD_FILIAL, 
		V_PG_DESP_RAT.CD_DESP_REC,		
		V_PG_DESP_RAT.CD_RAT_DESP,	
		V_PG_DESP_RAT.DOC_ORIGEM, 
		V_PG_DESP_RAT.TIPO_LANCTO, 
		V_PG_DESP_RAT.MES_ANO_REF,
		V_PG_DESP_RAT.RATEIO_CENTRO_CUSTO) CCUSTO INNER JOIN

	(SELECT     
		V_PG_DESP_RAT.CD_EMP,
		V_PG_DESP_RAT.CD_FILIAL, 
		V_PG_DESP_RAT.CD_RAT_DESP,
		V_PG_DESP_RAT.MES_ANO_REF,		
		MAX(V_PG_DESP_RAT.DT_VENCTO) AS DT_VENCTO,
		V_PG_DESP_RAT.CD_DESP_REC, 
		V_PG_DESP_RAT.NR_CNT, 
		V_PG_DESP_RAT.DS_DESP_REC, 
		PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB,
		SUM (V_PG_DESP_RAT.VLR_DESP ) VLR_DESP ,
		SUM (V_PG_DESP_RAT.PERC_DESP) AS PERC_DESP, 				
		SUM (V_PG_DESP_RAT.VLR_DESP_PAGO) AS VLR_DESP_PAGO
	FROM         
		V_PG_DESP_RAT 
		LEFT OUTER JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO ON 
		V_PG_DESP_RAT.CD_EMP = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP AND 
		V_PG_DESP_RAT.CD_CENTRO_CTB = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB  AND 
		V_PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP
	GROUP BY 
		V_PG_DESP_RAT.CD_DESP_REC,
		V_PG_DESP_RAT.CD_FILIAL,  
		V_PG_DESP_RAT.NR_CNT, 
		V_PG_DESP_RAT.DS_DESP_REC,
		PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB, 
		V_PG_DESP_RAT.MES_ANO_REF,
		V_PG_DESP_RAT.CD_RAT_DESP,

		V_PG_DESP_RAT.CD_EMP) DESP ON
		DESP.CD_EMP = CCUSTO.CD_EMP AND
		DESP.CD_FILIAL = CCUSTO.CD_FILIAL AND
		DESP.MES_ANO_REF = CCUSTO.MES_ANO_REF AND
		DESP.CD_RAT_DESP = CCUSTO.CD_RAT_DESP AND
		DESP.CD_CENTRO_CTB = CCUSTO.CD_CENTRO_CTB AND
		DESP.CD_DESP_REC = CCUSTO.CD_DESP_REC