/****** Object:  UserDefinedFunction [dbo].[UFD_RC_DEB_CLIENTE]    Script Date: 04/29/2014 23:12:42 ******/
IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RC_DEB_CLIENTE]')
			AND type IN (
				N'FN'
				,N'IF'
				,N'TF'
				,N'FS'
				,N'FT'
				)
		)
	DROP FUNCTION [dbo].[UFD_RC_DEB_CLIENTE]
GO

--SELECT * FROM UFD_RC_DEB_CLIENTE(1,520479,1)
CREATE FUNCTION [dbo].[UFD_RC_DEB_CLIENTE] (
	@CD_EMP INT = 1
	,@CD_CLI INT = 0
	,@FLAG_DOC INT = 0
	)
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 á receber em geral em nome do cliente;
	--	1 = Parcelas com vencimento futuro;
	--	2 = Parcelas com vencimento no próximo fechamento.
	------------------------------------------------------------------------------------
	--Valores para o campo @FLAG_DOC
	------------------------------------------------------------------------------------
	--	0 = Apenas documentos com saldo em aberto
	--	1 = todos os tipos de documentos até mesmo ja quitados
	DECLARE @STS_FAT VARCHAR(10)
	DECLARE @STS_PARC VARCHAR(10)

	IF @FLAG_DOC = 0
	BEGIN
		SET @STS_FAT = '0,2'
		SET @STS_PARC = '0,2,5'
	END
	ELSE
	BEGIN
		SET @STS_FAT = '0,1,2'
		SET @STS_PARC = '0,1,2,5'
	END

	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.VLR_DP AS VLR_PARC_CRED
		,ISNULL((
				SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)
				), 0) AS VLR_JUROS
		,V_RC_DEB_VLR_SLD.VLR_SLD 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 'NÃO'
			ELSE 'SIM'
			END AS VERBA_COMERCIAL
		,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
	RIGHT OUTER 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
	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 (
			SELECT ID
			FROM UFD_PRM_IN_STRING(@STS_FAT)
			)
		AND RC_DEB.SLD_DP > (
			CASE 
				WHEN @FLAG_DOC = 0
					THEN 0
				ELSE - 1
				END
			)
	
	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
		,'NÃO' 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
	LEFT JOIN RC_CREDIARIO_TP_LANC TP_LANC ON PARC.CD_TP_LANC = TP_LANC.CD_TP_LANC
	LEFT JOIN RC_CREDIARIO_PARC_PDV_VD PARC_PDV ON PARC.CD_CTR = PARC_PDV.CD_CTR
	LEFT 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
	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 (
			SELECT ID
			FROM UFD_PRM_IN_STRING(@STS_PARC)
			)
		AND PARC.SLD_PARC_CRED > (
			CASE 
				WHEN @FLAG_DOC = 0
					THEN 0
				ELSE - 1
				END
			)

	RETURN
END
GO


