IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RC_DEB_CLI_PERIODO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RC_DEB_CLI_PERIODO]
GO


CREATE FUNCTION [dbo].[UFD_RC_DEB_CLI_PERIODO]( @CD_EMP INT,
									   @CD_CLI	INT,
									   @DTINI DATETIME,
									   @DTFIM DATETIME	)

RETURNS @RC_DEB_CLI TABLE  (NR_FATURA		VARCHAR(20),
							CD_EMP			INT,
							CD_CLI			INT,
							DT_LANCTO		DATETIME,
							DT_FECHA		DATETIME,
							DT_VENCTO		DATETIME,
							PARCELA			VARCHAR(10),
							VLR_LIQUIDO		MONEY,
							VLR_ENCARGOS	MONEY,
							VLR_TOTAL		MONEY,
							DS_TP_LANC		VARCHAR(30),
							CD_FILIAL		INT,
							CD_RC_DEB		INT,
							NR_ECF			INT,
							CD_CX			INT,
							TP_REG			INT,
							PARC_COM_PGTO	INT,
							VERBA_COMERCIAL	varchar(10),
							VLR_DESC		MONEY )
AS
BEGIN

------------------------------------------------------------------------------------
--Valores para o campo TP_REG
------------------------------------------------------------------------------------
--	0 = Fatura/Contas a receber em geral em nome do cliente;
--	1 = Parcelas com vencimento futuro;
--	2 = Parcelas com vencimento no proximo fechamento.
	
	INSERT INTO @RC_DEB_CLI
	SELECT 
		RC_DEB.NR_FATURA,
		RC_DEB.CD_EMP,
		RC_DEB.CD_CLI,
		RC_DEB.DT_DP AS DT_LANC_CRED,
		NULL AS DT_FECHA_CRED,
		(CASE WHEN RC_DEB.DT_PRORROG IS NULL THEN RC_DEB.DT_VENCTO ELSE RC_DEB.DT_PRORROG END) AS DT_VENCTO_CRED,
		CAST(RC_DEB.PARC AS VARCHAR) AS PARC_CRED,
		RC_DEB.SLD_DP AS VLR_PARC_CRED,
		(SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB))  AS VLR_JUROS,
 		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,
		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 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 = @CD_EMP
		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 RC_DEB.SLD_DP > 0	 
        AND  (CASE WHEN RC_DEB.DT_PRORROG IS NULL THEN RC_DEB.DT_VENCTO ELSE RC_DEB.DT_PRORROG END) BETWEEN @DTINI AND @DTFIM
	UNION ALL

	SELECT	
		CAST(PARC.CD_CTR AS VARCHAR), 
		PARC.CD_EMP,
		PARC.CD_CLI, 
		PARC.DT_LANC_CRED, 
		PARC.DT_FECHA_CRED, 
		PARC.DT_VENCTO_CRED, 
		CAST(PARC.PARC_CRED AS VARCHAR) + '/' + CAST(PARC.QT_PARC_VD_CRED AS VARCHAR) AS PARC_CRED, 
		PARC.SLD_PARC_CRED AS VLR_PARC_CRED,
		0 AS VLR_JUROS, 
		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,
		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
	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 = @CD_EMP
		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
		AND PARC.DT_VENCTO_CRED BETWEEN @DTINI AND @DTFIM
RETURN 
END