IF EXISTS
(
    SELECT 1
    FROM SYS.VIEWS
    WHERE NAME = 'V_RC_DEB_CLI'
)
    BEGIN
        DROP VIEW V_RC_DEB_CLI;
    END;
GO

CREATE VIEW V_RC_DEB_CLI
AS
	SELECT ext_cli.CD_CLI,
		   RC.RZ_CLI,
		   (RC.END_CLI+' Nº '+ISNULL(CAST(RC.NUMERO AS VARCHAR), 0)) AS END_CLI,
		   GLB_REG.DS_REG,
		   ISNULL(cli_cob.INSTRUCAO_4, '') AS INSTRUCAO,
		   (RC.DDD_TEL+' - '+RC.TEL) AS TEL,
		   (RC.DDD_TEL_COB+' - '+RC.TEL_COB) AS TEL_COB,
		   (RC.DDD_FAX+' - '+RC.FAX) AS FAX,
		   RC.BAIRRO,
		   cid.DS_CID,
		   ext_cli.CD_FILIAL,
		   ext_cli.NR_FATURA,
		   ext_cli.DT_LANCTO,
		   ext_cli.DT_FECHA,
		   ext_cli.DT_VENCTO,
		   ext_cli.PARCELA,
		   ext_cli.VLR_LIQUIDO,
		   ext_cli.VLR_ENCARGOS,
		   ext_cli.VLR_TOTAL,
		   ext_cli.DS_TP_LANC,
		   ext_cli.NR_ECF,
		   ext_cli.CD_CX,
		   ext_cli.TP_REG,
		   usu.NM_USU,
		   usu.DT_ULT_ALT,
		   DATEDIFF(DAY, DT_VENCTO, GETDATE()) AS DIA_ATRASO,
		   VERBA_COMERCIAL,
		   (CASE
				WHEN AG.DT_AGENDA IS NULL
				THEN ''
				ELSE AG.DT_AGENDA
			END) DT_AGENDA,
		   ISNULL(AG.OBS_COBRANCA, '') AS OBS_COBRANCA,
		   ISNULL(RC.CEP, '') AS CEP,
		   ISNULL(BLQ.DS_MOT_BLOQ_CLI, '') AS MOTIVO_BLOQUEIO,
		   RC.CD_REGI
	FROM (SELECT 
			RC_DEB.NR_FATURA,
			RC_DEB.CD_EMP,
			RC_DEB.CD_CLI,
			RC_DEB.DT_DP AS DT_LANCTO,
			NULL AS DT_FECHA,
			(CASE WHEN RC_DEB.DT_PRORROG IS NULL THEN RC_DEB.DT_VENCTO ELSE RC_DEB.DT_PRORROG END) AS DT_VENCTO,
			CAST(RC_DEB.PARC AS VARCHAR) AS PARCELA,
			RC_DEB.SLD_DP AS VLR_LIQUIDO,
			(SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB))  AS VLR_ENCARGOS,
			RC_DEB.SLD_DP + (SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)) - (VLR_DESC - (SELECT DBO.F_DESC_ABATIDO(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB))) AS VLR_TOTAL,
			'FATURA' AS DS_TP_LANC,
			RC_DEB.CD_FILIAL,
			RC_DEB.CD_RC_DEB,
			0 AS NR_ECF,
			0 AS CD_CX,		
			0 AS TP_REG,
			0 AS PARC_COM_PGTO,
			CASE WHEN CG_FND_VERBA_RC_DEB_DUP_NR_CTR.NR_DP IS NULL THEN
				'NAO'
			ELSE
				'SIM'
			END AS VERBA_COMERCIAL,
			VLR_DESC - (SELECT DBO.F_DESC_ABATIDO(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)) VLR_DESC
		FROM
			RC_DEB 
			
			INNER JOIN V_RC_DEB_VLR_SLD ON 
			RC_DEB.CD_EMP= V_RC_DEB_VLR_SLD.CD_EMP
			AND RC_DEB.CD_FILIAL =  V_RC_DEB_VLR_SLD.CD_FILIAL
			AND RC_DEB.CD_RC_DEB = V_RC_DEB_VLR_SLD.CD_RC_DEB
			
			LEFT OUTER JOIN CG_FND_VERBA_RC_DEB_DUP_NR_CTR ON
			RC_DEB.CD_EMP = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.CD_EMP AND
			RC_DEB.CD_FILIAL = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.CD_FILIAL AND
			RC_DEB.NR_DP = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.NR_DP AND
			RC_DEB.ANO = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.ANO
		WHERE 
			RC_DEB.CD_EMP = 1
			--AND RC_DEB.CD_CLI = (CASE WHEN @CD_CLI = 0 THEN RC_DEB.CD_CLI ELSE @CD_CLI END)
			AND RC_DEB.STS_DP IN (0,2)
			AND V_RC_DEB_VLR_SLD.VLR_SLD  > 0	 

		UNION ALL

		SELECT	
			CAST(PARC.CD_CTR AS VARCHAR), 
			PARC.CD_EMP,
			PARC.CD_CLI, 
			PARC.DT_LANC_CRED DT_LANCTO, 
			PARC.DT_FECHA_CRED DT_FECHA, 
			PARC.DT_VENCTO_CRED DT_VENCTO, 
			CAST(PARC.PARC_CRED AS VARCHAR) + '/' + CAST(PARC.QT_PARC_VD_CRED AS VARCHAR) AS PARCELA, 
			PARC.SLD_PARC_CRED AS VLR_LIQUIDO,
			0 AS VLR_ENCARGOS, 
			PARC.SLD_PARC_CRED AS VLR_TOTAL,	
			TP_LANC.DS_TP_LANC,
			PDV_VD.CD_FILIAL,
			NULL AS CD_RC_DEB,
			ISNULL(PDV_VD.NR_ECF,0) AS NR_ECF,
			ISNULL(PDV_VD.CD_CX,0) AS CD_CX,
			(CASE WHEN PARC.DT_FECHA_CRED <= CLI.DT_PROX_FECHA THEN 2 ELSE 1 END) AS TP_REG,
			CASE WHEN ISNULL((SELECT 
						TOP 1 RC_CREDIARIO_PARC_CRED.CD_CTR 
					FROM
						RC_CREDIARIO_PARC_CRED 
					WHERE
						PARC.CD_CTR = RC_CREDIARIO_PARC_CRED.CD_CTR),0) > 0 THEN 
				1
			ELSE
				0
			END PARC_COM_PGTO,
			'NAO' AS VERBA_COMERCIAL,
			0 as VLR_DESC
		FROM RC_CREDIARIO_PARC PARC INNER JOIN RC_CLI_CREDIARIO CLI ON
				PARC.CD_EMP		= CLI.CD_EMP
				AND PARC.CD_CLI	= CLI.CD_CLI
			INNER JOIN RC_CREDIARIO_TP_LANC TP_LANC ON
				PARC.CD_TP_LANC = TP_LANC.CD_TP_LANC
			INNER JOIN RC_CREDIARIO_PARC_PDV_VD PARC_PDV ON
				PARC.CD_CTR		= PARC_PDV.CD_CTR
			INNER JOIN PDV_VD ON 
				PARC_PDV.CD_EMP			= PDV_VD.CD_EMP
				AND PARC_PDV.CD_FILIAL	= PDV_VD.CD_FILIAL
				AND PARC_PDV.CD_VD		= PDV_VD.CD_VD
				AND PDV_VD.ST_VD=0
		WHERE 
			PARC.CD_EMP = 1
			--AND PARC.CD_CLI = (CASE WHEN @CD_CLI = 0 THEN PARC.CD_CLI ELSE @CD_CLI END) 
			AND PARC.STS_LANC_CRED IN (0,2,5)
			AND PARC.SLD_PARC_CRED > 0)
			 AS ext_cli
		 INNER JOIN RC_CLI AS RC ON ext_cli.CD_EMP = RC.CD_EMP
									AND ext_cli.CD_CLI = RC.CD_CLI
		 LEFT JOIN RC_CLI_COBRANCA AS cli_cob ON RC.CD_EMP = cli_cob.CD_EMP
												 AND RC.CD_CLI = cli_cob.CD_CLI
		 LEFT JOIN
	(
		SELECT CD_EMP,
			   CD_CLI,
			   DT_AGENDA,
			   OBS_COBRANCA
		FROM RC_CLI_COBRANCA_AGENDA
		WHERE STS_COB = 0
	) AG ON AG.CD_EMP = RC.CD_EMP
			AND AG.CD_CLI = RC.CD_CLI
		 INNER JOIN GLB_REG ON RC.CD_EMP = GLB_REG.CD_EMP
							   AND RC.CD_REGI = GLB_REG.CD_REG
		 INNER JOIN GLB_USU AS usu ON usu.CD_USU = RC.CD_USU
		 INNER JOIN GLB_CID AS cid ON RC.CD_CID = cid.CD_CID
		 LEFT JOIN RC_CLI_CREDIARIO CRED ON CRED.CD_EMP = RC.CD_EMP
											AND Cred.CD_CLI = RC.CD_CLI
		 LEFT JOIN RC_MOT_BLOQ_CLI BLQ ON BLQ.CD_MOT_BLOQ_CLI = Cred.CD_MOT_BLOQ_CLI