
/****** Object:  UserDefinedFunction [dbo].[UFD_RC_ADM_CARTAO_MOV_EXTRATO]    Script Date: 08/17/2012 10:42:01 ******/

IF EXISTS
(
	SELECT
		   *
	FROM
		sys.objects
	WHERE  object_id = OBJECT_ID(N'[dbo].[UFD_RC_ADM_CARTAO_MOV_SLD]')
		   AND type IN(N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION
	 [dbo].[UFD_RC_ADM_CARTAO_MOV_SLD];
GO
--SELECT DBO.UFD_RC_ADM_CARTAO_MOV_SLD(110343,1,'2016-08-01', 0,0)  
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_MOV_SLD]
(@CD_CLI      INT
, @CD_GRP_ECON INT
, @DT_INI      DATETIME
, @TP_DATA     INT
, @MODALIDADE  INT
)
RETURNS MONEY
AS
	 BEGIN
		 DECLARE @VLR_SLD_ANT MONEY;
		 IF @TP_DATA = 0
			 BEGIN
				 SELECT
						@VLR_SLD_ANT = isnull(SUM(VLR_DEB), 0) - ISNULL(SUM(VLR_CRED), 0)
				 FROM
						(
							SELECT
								   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
							FROM
								RC_ADM_CARTAO_MOV
							WHERE  CD_CLI = @CD_CLI
								   AND CD_GRP_ECON = @CD_GRP_ECON
								   AND RC_ADM_CARTAO_MOV.DT_LANC < @DT_INI
								   AND RC_ADM_CARTAO_MOV.MODALIDADE = @MODALIDADE
						) s;
			 END;
			 ELSE
			 BEGIN
				 SELECT
						@VLR_SLD_ANT = isnull(SUM(VLR_DEB), 0) - ISNULL(SUM(VLR_CRED), 0)
				 FROM
						(
							SELECT
								   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
							FROM
								RC_ADM_CARTAO_MOV
							WHERE  CD_EMP = 1
								   AND CD_CLI = @CD_CLI
								   AND CD_GRP_ECON = @CD_GRP_ECON
								   AND RC_ADM_CARTAO_MOV.DT_VENCTO < @DT_INI
								   AND RC_ADM_CARTAO_MOV.MODALIDADE = @MODALIDADE
						) s;
			 END;
		 RETURN(@VLR_SLD_ANT);
	 END;  
  
  
  
  