/****** Object:  View [dbo].[V_PG_DESP_RAT]    Script Date: 08/14/2012 16:24:58 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DESP_RAT]'))
DROP VIEW [dbo].[V_PG_DESP_RAT]
GO
GO

--SELECT * FROM V_PG_DESP_RAT where DOC_ORIGEM = 1105121
CREATE VIEW [dbo].[V_PG_DESP_RAT] 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,
	PG_CRED.DT_EMIS,
	SUM( CASE WHEN ISNULL(C.VLR_RAT_DESP,0) > 0 AND C.VLR_RAT_DESP>0 THEN
			CASE WHEN C.VLR_RAT_DESP > 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
				0
			END 
		ELSE 
			PG_DESP_RAT.VLR_DESP  
		END) AS VLR_DESP,
	SUM(DISTINCT PG_DESP_RAT.PERC_DESP) AS PERC_DESP,
	SUM((CASE WHEN PG_CRED.VLR_BAIXA > 0 AND C.VLR_RAT_DESP > 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 
			0 
		END))   VLR_DESP_PAGO,
	PG_DESP_RAT.MES_ANO_REF,
	'PAGAR' AS TIPO_LANCTO,
	PG_DESP_RAT_PG_CRED.CD_FILIAL,
	PG_DESP_RAT_PG_CRED.NR_DP AS DOC_ORIGEM,
	C.CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	C.CD_CENTRO_CTB,
	PG_CRED.DT_VENCTO,
	V_PG_CRED_NR_PARC.QT_PARC 
FROM
	(SELECT 
			CD_EMP, 
			CD_FILIAL, 
			NR_DP, 
			ANO, 
			DT_EMIS, 
			MAX(DT_VENCTO) AS DT_VENCTO, 
			SUM((VLR_BAIXA + VLR_JUROS_DEB) - (VLR_DESC_DEB + VLR_IMP_DEB)) AS VLR_BAIXA,
			SUM(V_PG_CRED.VLR_DP) AS VLR_DP
	FROM V_PG_CRED 
		 WHERE 
			V_PG_CRED.STS_DP <> 3
		 GROUP BY CD_EMP, CD_FILIAL, NR_DP, ANO, DT_EMIS) AS PG_CRED 

		INNER JOIN PG_DESP_RAT_PG_CRED ON
			PG_CRED.CD_EMP = PG_DESP_RAT_PG_CRED.CD_EMP AND 
			PG_CRED.CD_FILIAL = PG_DESP_RAT_PG_CRED.CD_FILIAL AND 
			PG_CRED.NR_DP = PG_DESP_RAT_PG_CRED.NR_DP AND 
			PG_CRED.ANO = PG_DESP_RAT_PG_CRED.ANO 		
		INNER JOIN PG_DESP_RAT ON
			PG_DESP_RAT_PG_CRED.CD_EMP = PG_DESP_RAT.CD_EMP AND 
			PG_DESP_RAT_PG_CRED.CD_RAT_DESP = PG_DESP_RAT.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
		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 V_PG_CRED_NR_PARC ON
		PG_CRED.CD_EMP = V_PG_CRED_NR_PARC.CD_EMP AND
		PG_CRED.CD_FILIAL = V_PG_CRED_NR_PARC.CD_FILIAL AND
		PG_CRED.NR_DP = V_PG_CRED_NR_PARC.NR_DP AND
		PG_CRED.ANO  = V_PG_CRED_NR_PARC.ANO
WHERE
	C.VLR_RAT_DESP > 0 
GROUP BY 
	PG_DESP_RAT.CD_EMP, VLR_DP,
	PG_DESP_RAT.CD_RAT_DESP, 
	PG_DESP_RAT.CD_DESP_REC,
	GLB_DESP_REC.NR_CNT,
	GLB_DESP_REC.DS_DESP_REC,
	PG_CRED.DT_EMIS,
	PG_DESP_RAT.MES_ANO_REF,	
	PG_DESP_RAT_PG_CRED.CD_FILIAL,
	PG_DESP_RAT_PG_CRED.NR_DP,
	C.CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,	
	C.CD_CENTRO_CTB,
	PG_CRED.DT_VENCTO,
	V_PG_CRED_NR_PARC.QT_PARC
	
UNION ALL  
      
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.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,
	'BANCÁRIO' AS TIPO_LANCTO,
	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,
	PG_DESP_RAT.MES_ANO_REF,
	1 AS QT_PARC
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
	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 
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
UNION ALL 

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, 
	RC_CLI_CONV_GLOSA_PG_DESP_RAT.DT_LANC,
	PG_DESP_RAT.VLR_DESP,
	PG_DESP_RAT.PERC_DESP,
	PG_DESP_RAT.VLR_DESP VLR_DESP_PAGO,
	PG_DESP_RAT.MES_ANO_REF,
	'GLOSA CONVÊNIO' AS TIPO_LANCTO,
	PG_DESP_RAT.CD_FILIAL,
	0 AS DOC_ORIGEM,
	RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	C.CD_CENTRO_CTB,
	PG_DESP_RAT.MES_ANO_REF,
	1 AS QT_PARC
FROM 
	RC_CLI_CONV_GLOSA_PG_DESP_RAT 
	INNER JOIN PG_DESP_RAT ON 
	RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_EMP = PG_DESP_RAT.CD_EMP AND 
	RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT.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
	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 

UNION ALL 

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, 
	PG_DESP_RAT_PDV_VD_FECHA_MOV.DT_MOV, 
	PG_DESP_RAT.VLR_DESP, 
	PG_DESP_RAT.PERC_DESP,
	PG_DESP_RAT.VLR_DESP  VLR_DESP_PAGO, 
	PG_DESP_RAT.MES_ANO_REF,
	'FECHAMENTO DE CAIXA' AS TIPO_LANCTO,
	PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_FILIAL,
	0 AS DOC_ORIGEM,
	0 AS CD_USU,
	GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	C.CD_CENTRO_CTB,
	PG_DESP_RAT.MES_ANO_REF,
	1 AS QT_PARC
FROM 
	PG_DESP_RAT_PDV_VD_FECHA_MOV 
	INNER JOIN PG_DESP_RAT ON  
	PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_EMP = PG_DESP_RAT.CD_EMP AND 
	PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_RAT_DESP = PG_DESP_RAT.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
	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

GO


