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

--SELECT * FROM UFD_RC_ADM_CARTAO_MOV_BAIXA (1,1,'2017-12-05','2017-12-05')						
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_MOV_BAIXA]
(@CD_EMP      INT
, @CD_GRP_ECON INT
, @DT_INI      DATETIME
, @DT_FIM      DATETIME
)
RETURNS @LANC_EXTRATO TABLE
(CD_EMP        INT
, CD_FILIAL     INT
, CD_CLI        INT
, NM_FANT       VARCHAR(100)
, VLR_SLD       MONEY
, VLR_SLD_ANT   MONEY
, VLR_SLD_VENC  MONEY
, CD_CNT        INT
, NR_CNT        VARCHAR(100)
, DS_MODALIDADE VARCHAR(40)
)
AS
	 BEGIN
		 DECLARE @VLR_SLD MONEY;
		 DECLARE @VLR_SLD_ANT MONEY;
		 DECLARE @RS_MOV_CARTAO TABLE
(CD_EMP       INT
, CD_FILIAL    INT
, CD_CLI       INT
, NM_FANT      VARCHAR(100)
, VLR_SLD      MONEY
, VLR_SLD_ANT  MONEY
, VLR_SLD_VENC MONEY
, CD_CNT       INT
, MODALIDADE   INT
);
		 DECLARE @TABLE_AUX TABLE
(cd_emp      INT
, cd_filial   INT
, CD_CLI      INT
, modalidade  INT
, VLR_SLD     MONEY
, CD_GRP_ECON INT
);
		 INSERT INTO @TABLE_AUX
		 SELECT
				CD_EMP
			  , CD_FILIAL
			  , CD_CLI
			  , MODALIDADE
			  , ISNULL(SUM(VLR_DEB), 0) - ISNULL(SUM(VLR_CRED), 0)
			  , CD_GRP_ECON
		 FROM
(
	SELECT
		   CD_EMP
		 , CD_FILIAL
		 , CD_CLI
		 , MODALIDADE
		 , CASE
			   WHEN TP_LANC = 0 THEN VLR_LANC
			   ELSE 0
		   END AS VLR_CRED
		 , CASE
			   WHEN TP_LANC = 1 THEN VLR_LANC
			   ELSE 0
		   END AS VLR_DEB
		 , CD_GRP_ECON
	FROM
		RC_ADM_CARTAO_MOV
	WHERE  CD_EMP = @CD_EMP
		   AND CD_GRP_ECON = @CD_GRP_ECON
		   AND RC_ADM_CARTAO_MOV.DT_VENCTO < @DT_INI
) s
		 GROUP BY
				  CD_FILIAL
				, MODALIDADE
				, CD_CLI
				, CD_EMP
				, CD_GRP_ECON;
		 INSERT INTO @RS_MOV_CARTAO 
--Valores anteriores ou seja saldo restante de outros recebimento
		 SELECT
				s.CD_EMP
			  , ISNULL(s.CD_FILIAL, 0) AS CD_FILIAL
			  , s.CD_CLI
			  , NM_FANT
			  , VLR_SLD VLR_SLD
			  , VLR_SLD VLR_SLD_ANT
			  , 0 -- vlr_saldo_venc  
			  , s.CD_CNT
			  , MODALIDADE
		 FROM
(
	SELECT
		   AUX.CD_EMP
		 , AUX.CD_FILIAL
		 , AUX.CD_CLI
		 , ISNULL(CD_CNT, 0) AS CD_CNT
		 , VLR_SLD AS VLR_SLD
		 , AUX.VLR_SLD AS VLR_SLD_ANT
		 , AUX.MODALIDADE
	FROM
		RC_ADM_CARTAO_RC_CLI_BC_CONTAS
		INNER JOIN @TABLE_AUX AUX ON RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_EMP = AUX.CD_EMP
									 AND RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_GRP_ECON = AUX.CD_GRP_ECON
									 AND RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_CLI = AUX.CD_CLI
	WHERE  AUX.CD_GRP_ECON = @CD_GRP_ECON
) s
INNER JOIN RC_CLI ON s.CD_EMP = RC_CLI.CD_EMP
					 AND s.CD_CLI = RC_CLI.CD_CLI
		 UNION ALL
--Valor que esta vencendo no periodo
		 SELECT
				v.CD_EMP
			  , v.CD_FILIAL
			  , v.CD_CLI
			  , NM_FANT
			  , isnull(SUM(v.VLR_DEB), 0) - ISNULL(SUM(v.VLR_CRED), 0) AS VLR_SLD
			  , 0
			  , isnull(SUM(v.VLR_DEB), 0) - ISNULL(SUM(v.VLR_CRED), 0) * -1 AS VLR_VENC -- alterei aqui
			  , v.CD_CNT
			  , MODALIDADE
		 FROM
(
	SELECT
		   RC_ADM_CARTAO_MOV.CD_EMP
		 , RC_ADM_CARTAO_MOV.CD_FILIAL
		 , RC_ADM_CARTAO_MOV.CD_CLI
		 , ISNULL(CD_CNT, 0) AS CD_CNT
		 , CASE
			   WHEN TP_LANC = 0 THEN VLR_LANC * -1
			   ELSE 0
		   END AS VLR_CRED
		 , CASE
			   WHEN TP_LANC = 1 THEN VLR_LANC
			   ELSE 0
		   END AS VLR_DEB
		 , MODALIDADE
	FROM
		RC_ADM_CARTAO_MOV
		LEFT OUTER JOIN RC_ADM_CARTAO_RC_CLI_BC_CONTAS ON RC_ADM_CARTAO_MOV.CD_CLI = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_CLI
														  AND RC_ADM_CARTAO_MOV.CD_GRP_ECON = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_GRP_ECON
	WHERE  RC_ADM_CARTAO_MOV.CD_GRP_ECON = @CD_GRP_ECON
		   AND RC_ADM_CARTAO_MOV.DT_VENCTO BETWEEN @DT_INI AND @DT_FIM
) v
INNER JOIN RC_CLI ON v.CD_EMP = RC_CLI.CD_EMP
					 AND v.CD_CLI = RC_CLI.CD_CLI
		 GROUP BY
				  v.CD_EMP
				, v.CD_FILIAL
				, v.CD_CLI
				, NM_FANT
				, v.CD_CNT
				, MODALIDADE;
		 INSERT INTO @LANC_EXTRATO
		 SELECT
				m.CD_EMP
			  , ISNULL(m.CD_FILIAL, 0) AS CD_FILIAL
			  , m.CD_CLI
			  , m.NM_FANT
			  , SUM(VLR_SLD) AS VLR_SLD
			  , SUM(VLR_SLD_ANT) AS VLR_SLD_ANT
			  , SUM(VLR_SLD_VENC) AS VLR_SLD_VENC
			  , m.CD_CNT
			  , ISNULL(BC_BANCO.DS_BC, '')+' '+ISNULL(BC_CONTAS.NR_CNT, '') AS NR_CNT
			  , CASE
					WHEN MODALIDADE = 0 THEN 'CREDITO'
					ELSE 'DEBITO'
				END AS DS_MODALIDADE
		 FROM
			 @RS_MOV_CARTAO m
			 LEFT OUTER JOIN BC_CONTAS ON m.CD_EMP = BC_CONTAS.CD_EMP
										  AND m.CD_CNT = BC_CONTAS.CD_CNT
			 LEFT OUTER JOIN BC_BANCO ON BC_CONTAS.CD_BC = BC_BANCO.CD_BC
		 GROUP BY
				  m.CD_EMP
				, m.CD_FILIAL
				, m.CD_CLI
				, m.NM_FANT
				, m.CD_CNT
				, ISNULL(BC_BANCO.DS_BC, '')+' '+ISNULL(BC_CONTAS.NR_CNT, '')
				, MODALIDADE;
		 RETURN;
	 END;
