/****** Object:  UserDefinedFunction [dbo].[UFD_BC_LANC_EXTRATO_SLD]    Script Date: 10/09/2012 14:18:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BC_LANC_EXTRATO_SLD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_BC_LANC_EXTRATO_SLD]
GO  

/*DECLARE @CD_EMP INT
DECLARE @CD_CNT INT
DECLARE @DT_INI DATETIME
DECLARE @TP_LANC INT


SET @CD_EMP = 1
SET @CD_CNT = 3
SET @DT_INI = '2004-04-10'
SET @TP_LANC = 1*/
--SELECT DBO.UFD_BC_LANC_EXTRATO_SLD(1,3,'2004-04-10',0)
CREATE FUNCTION [dbo].[UFD_BC_LANC_EXTRATO_SLD](
--CREATE FUNCTION UFD_BC_LANC_EXTRATO_SLD(
													@CD_EMP	INT,
													@CD_CNT	INT,
													@DT_INI	DATETIME,
													@TP_COMPES INT --0 Não Compensado; 1 - Compensado
													)													
RETURNS MONEY AS
BEGIN

DECLARE @VLR_CRED MONEY
DECLARE @VLR_DEB  MONEY
DECLARE @RS_BC_LANC TABLE (
									CD_EMP INT, 
									CD_CNT INT, 
									CD_BC_LANC INT,  
									TP_LANC INT, 
									CD_DOC INT, 
									VLR_LANC MONEY)
SET @VLR_CRED=0
SET @VLR_DEB=0

INSERT INTO @RS_BC_LANC
	SELECT 
		CD_EMP,
		CD_CNT,
		CD_BC_LANC,
		TP_LANC,
		CD_DOC,
		VLR_LANC
	FROM
		BC_LANC
	WHERE
		CD_EMP = @CD_EMP
		AND CD_CNT = @CD_CNT
		AND DT_LANCTO < @DT_INI

INSERT INTO @RS_BC_LANC
	SELECT 
		BC_LANC.CD_EMP,
		BC_LANC.CD_CNT,
		0 AS CD_BC_LANC,
		0,
		0,
		BC_LANC.VLR_LANC
	FROM
		BC_LANC INNER JOIN BC_LANC_PDV_VD_FECHA_MOV_TES ON
		BC_LANC.CD_EMP = BC_LANC_PDV_VD_FECHA_MOV_TES.CD_EMP AND
		BC_LANC.CD_BC_LANC = BC_LANC_PDV_VD_FECHA_MOV_TES.CD_BC_LANC_DEB
		INNER JOIN BC_CONTAS ON
		BC_LANC.CD_CNT = BC_CONTAS.CD_CNT 
	WHERE
		BC_LANC.CD_EMP = @CD_EMP
		AND BC_LANC.CD_CNT = @CD_CNT
		AND BC_LANC.DT_LANCTO < @DT_INI
		AND BC_CONTAS.CNT_CX_PD=1

IF @TP_COMPES = 1 
		DELETE FROM @RS_BC_LANC
			FROM
					@RS_BC_LANC RS_BC_LANC,
					GLB_DOC
			WHERE
				RS_BC_LANC.CD_EMP = GLB_DOC.CD_EMP AND
				RS_BC_LANC.CD_DOC = GLB_DOC.CD_DOC AND
				GLB_DOC.TP_DOC= 6
				AND NOT EXISTS(
								SELECT
									CD_BC_LANC
								FROM
									V_BC_CHQ_LANC_COMPENSADO
								WHERE
									V_BC_CHQ_LANC_COMPENSADO.CD_EMP = RS_BC_LANC.CD_EMP AND
									V_BC_CHQ_LANC_COMPENSADO.CD_BC_LANC = RS_BC_LANC.CD_BC_LANC AND
									V_BC_CHQ_LANC_COMPENSADO.CD_CNT = RS_BC_LANC.CD_CNT)
SET @VLR_CRED = ISNULL((SELECT
	SUM(VLR_LANC) AS VLR_LANC
FROM
	@RS_BC_LANC
WHERE
	TP_LANC = 0),0)

SET @VLR_DEB = ISNULL((SELECT
									SUM(VLR_LANC) AS VLR_LANC
							  FROM
									@RS_BC_LANC
							  WHERE
									TP_LANC = 1),0)


RETURN (SELECT @VLR_CRED - @VLR_DEB	)
END
