
/****** Object:  View [dbo].[V_RC_DEB_CLIENTES]    Script Date: 03/28/2013 15:22:54 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_RC_DEB_CLIENTES]'))
DROP VIEW [dbo].[V_RC_DEB_CLIENTES]
GO

/****** Object:  View [dbo].[V_RC_DEB_CLIENTES]    Script Date: 03/28/2013 15:22:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[V_RC_DEB_CLIENTES] AS
SELECT     
	V_RC_DEB_VLR_SLD.CD_EMP, 
	V_RC_DEB_VLR_SLD.CD_FILIAL, 
	V_RC_DEB_VLR_SLD.CD_RC_DEB,
	RC_CLI.CD_CTB_CNT, 
	RC_CLI.CD_CLI, 
	RC_CLI.RZ_CLI, 
	V_RC_DEB_VLR_SLD.NR_DP, 
	V_RC_DEB_VLR_SLD.PARC, 
	V_RC_DEB_VLR_SLD.ANO, 
	V_RC_DEB_VLR_SLD.DT_DP, 
	V_RC_DEB_VLR_SLD.DT_VENCTO, 
	V_RC_DEB_VLR_SLD.STS_DP,
	ROUND(V_RC_DEB_VLR_SLD.VLR_DP,2) AS VLR_DP, 
	ROUND(V_RC_DEB_VLR_SLD.VLR_SLD,2) AS VLR_SLD,
	ISNULL(V_RC_DEB_VLR_SLD.VL_DESC,0) AS VL_DESC,
	ISNULL(GLOSA.VLR_GLOSA,0) AS VLR_GLOSA,
	ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) AS VLR_IMP,
	CASE WHEN ISNULL((SELECT 
							TOP 1 HABILITADO
					  FROM	
							PRC_PR_SISTEMAS
					  WHERE
							CD_SISTEMA=12 AND HABILITADO=1),0) = 0 THEN 	
						
						CASE WHEN DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate()) > 0 THEN
							CASE WHEN RC_CLI_COBRANCA.PERC_MORA_DIA > 0 THEN
								(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)) * RC_CLI_COBRANCA.PERC_MORA_DIA) / 100) * DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate())
							ELSE
								(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0)- ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0))* PRC_FILIAL.MORA_BOL) / 100) * DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate())
							END
						ELSE
							0
						END 
	ELSE
		(SELECT DBO.UFD_RC_DEB_VL_JUR(V_RC_DEB_VLR_SLD.CD_EMP, V_RC_DEB_VLR_SLD.CD_FILIAL, V_RC_DEB_VLR_SLD.CD_RC_DEB))	
	END	AS VLR_JUROS,
	CASE WHEN ISNULL((SELECT 
							TOP 1 HABILITADO
					  FROM	
							PRC_PR_SISTEMAS
					  WHERE
							CD_SISTEMA=12 AND HABILITADO=1),0) = 0 THEN 	

				CASE WHEN DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate()) > 0 THEN
					CASE WHEN  RC_CLI_COBRANCA.TAXA_MULTA > 0 THEN
						(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0)- ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)) * ISNULL(RC_CLI_COBRANCA.TAXA_MULTA,0)) / 100) 
					ELSE
						(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0)- ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)) * ISNULL(PRC_FILIAL.MULTA_BOL,0)) / 100) 
					END
				ELSE 
					0
				END 
	ELSE
		0
	END	 AS VLR_MULTA,

	CASE WHEN DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate()) > 0 THEN
		CASE WHEN ISNULL(RC_CLI_COBRANCA.PERC_MORA_DIA,0) > 0 THEN
			V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) + (((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0)- ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)) * ISNULL(RC_CLI_COBRANCA.TAXA_MULTA,0)) / 100) + 
			(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0))* ISNULL(RC_CLI_COBRANCA.PERC_MORA_DIA,0)) / 100) * DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate())
		ELSE
			V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) + (((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0))* ISNULL(RC_CLI_COBRANCA.TAXA_MULTA,0)) / 100) + 
			(((V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0)- ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)) * PRC_FILIAL.MORA_BOL) / 100) * DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate())
		END
	ELSE
		V_RC_DEB_VLR_SLD.VLR_SLD - ISNULL(GLOSA.VLR_GLOSA,0) - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0)
	END AS VLR_TOTAL,

	DATEDIFF(day,V_RC_DEB_VLR_SLD.DT_VENCTO,getdate()) AS DIAS_ABERTO,
	CASE WHEN RC_CLI_CONV_VINC_RC_CLI.CD_CLI  IS NULL THEN
		'DUPLICATA'
	ELSE
		'CONVENIO'
	END AS TIPO_CLIENTE,
	CASE WHEN RC_CLI_CONV.CONV_HABILITADO IS NULL THEN
		''
	ELSE
		CASE WHEN RC_CLI_CONV.CONV_HABILITADO = 0  THEN
			'ATIVO'
		ELSE
			'INATIVO'
		END
	END AS STS_CONVENIO,
	V_RC_DEB_VLR_SLD.VLR_PGTO
FROM         
	V_RC_DEB_VLR_SLD

	INNER JOIN RC_CLI ON
		V_RC_DEB_VLR_SLD.CD_EMP = RC_CLI.CD_EMP
		AND V_RC_DEB_VLR_SLD.CD_CLI = RC_CLI.CD_CLI

	LEFT OUTER JOIN 
				(SELECT        
					DISTINCT CD_EMP,        
					CD_CLI  
				 FROM    
					RC_CLI_CONV_VINC_RC_CLI v) RC_CLI_CONV_VINC_RC_CLI ON
		RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
		RC_CLI.CD_CLI = RC_CLI_CONV_VINC_RC_CLI.CD_CLI 
	
	LEFT OUTER JOIN RC_DEB_RC_CLI_CONV ON
		V_RC_DEB_VLR_SLD.CD_EMP = RC_DEB_RC_CLI_CONV.CD_EMP AND
		V_RC_DEB_VLR_SLD.CD_FILIAL = RC_DEB_RC_CLI_CONV.CD_FILIAL AND
		V_RC_DEB_VLR_SLD.CD_RC_DEB = RC_DEB_RC_CLI_CONV.CD_RC_DEB 

	LEFT OUTER JOIN RC_CLI_CONV ON
		RC_DEB_RC_CLI_CONV.CD_EMP = RC_CLI_CONV.CD_EMP AND
		RC_DEB_RC_CLI_CONV.CD_CONV = RC_CLI_CONV.CD_CONV

	INNER JOIN PRC_FILIAL ON
		V_RC_DEB_VLR_SLD.CD_EMP = PRC_FILIAL.CD_EMP
		AND V_RC_DEB_VLR_SLD.CD_FILIAL = PRC_FILIAL.CD_FILIAL

	LEFT JOIN RC_CLI_COBRANCA ON
		RC_CLI.CD_EMP = RC_CLI_COBRANCA.CD_EMP
		AND RC_CLI.CD_CLI = RC_CLI_COBRANCA.CD_CLI

	LEFT JOIN (
		SELECT
			RC_VD_CONV_RC_DEB_GLOSA.CD_EMP,
			RC_VD_CONV_RC_DEB_GLOSA.CD_FILIAL,
			RC_VD_CONV_RC_DEB_GLOSA.CD_RC_DEB,
			SUM(RC_VD_CONV.VLR_CONV) AS VLR_GLOSA
		FROM
			RC_VD_CONV_RC_DEB_GLOSA
			
			INNER JOIN RC_VD_CONV ON
				RC_VD_CONV_RC_DEB_GLOSA.CD_EMP = RC_VD_CONV.CD_EMP AND
				RC_VD_CONV_RC_DEB_GLOSA.CD_CTR = RC_VD_CONV.CD_CTR
		GROUP BY
			RC_VD_CONV_RC_DEB_GLOSA.CD_EMP,
			RC_VD_CONV_RC_DEB_GLOSA.CD_FILIAL,
			RC_VD_CONV_RC_DEB_GLOSA.CD_RC_DEB)
		GLOSA ON
		V_RC_DEB_VLR_SLD.CD_EMP = GLOSA.CD_EMP
		AND V_RC_DEB_VLR_SLD.CD_FILIAL = GLOSA.CD_FILIAL
		AND V_RC_DEB_VLR_SLD.CD_RC_DEB = GLOSA.CD_RC_DEB
	    
		LEFT OUTER JOIN V_RC_DEB_IMPOSTO ON 
		V_RC_DEB_VLR_SLD.CD_EMP = V_RC_DEB_IMPOSTO.CD_EMP AND 
        V_RC_DEB_VLR_SLD.CD_FILIAL = V_RC_DEB_IMPOSTO.CD_FILIAL AND 
        V_RC_DEB_VLR_SLD.CD_RC_DEB = V_RC_DEB_IMPOSTO.CD_RC_DEB 
	





GO


