IF OBJECT_ID('UFD_EXP_CTB_MOV_BC_LANC_TRANSF') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_BC_LANC_TRANSF
	END
	
GO 
 	
 
--SELECT * FROM  UFD_EXP_CTB_MOV_BC_LANC_TRANSF (1,904,'2014-10-03','2014-10-03')
CREATE FUNCTION UFD_EXP_CTB_MOV_BC_LANC_TRANSF(  @CD_EMP		 INT,
												 @CD_FILIAL		 INT,
												 @DT_INI		 DATETIME,
												 @DT_FIM		 DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP			INT,
			CD_FILIAL		INT,
			DT_LANCTO		DATETIME,
			CD_BC_LANC		INT, 
			VALORORIGEM		MONEY,
			VALORDESTINO	MONEY,
			CONTAORIGEM		INT,
			CONTADESTINO	INT,
			CONTAORIGEMAUX	INT,
			CONTADESTINOAUX INT)
			
AS
BEGIN       

	DECLARE @BLOQUEIO_TRANSACAO_GRUPO_ECONOMICO INT 
	DECLARE @CD_GRP_ECON			INT
	
	SET @BLOQUEIO_TRANSACAO_GRUPO_ECONOMICO = ISNULL((SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP,'BLOQUEIO_TRANSACAO_GRUPO_ECONOMICO',0,0)),0)    
		
	SET @CD_GRP_ECON = (SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
						FROM PRC_FILIAL
							INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON
								PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP AND 
								PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
						WHERE
						    PRC_FILIAL.CD_EMP = @CD_EMP
						    AND PRC_FILIAL.CD_FILIAL = @CD_FILIAL
						    AND PRC_FILIAL.FILIAL_MTZ_GRP_ECON = 1)
	
	IF ISNULL(@CD_GRP_ECON,0) = 0 
		BEGIN
			RETURN
		END

	INSERT INTO @RS_RETURN 		
		SELECT 
			DISTINCT 
			BC_LANC_TRANSF.CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			ORIGEM.DT_LANCTO,
			ORIGEM.CD_BC_LANC, 
			ORIGEM.VLR_LANC AS VALORORIGEM,
			DESTINO.VLR_LANC AS VALORDESTINO,
			BC_LANC_TRANSF.CD_CNT_ORIG AS CONTAORIGEM,
			BC_LANC_TRANSF.CD_CNT_DEST AS CONTADESTINO,
			ORIGEM.CD_CNT AS CONTAORIGEMAUX,
			DESTINO.CD_CNT AS CONTADESTINOAUX

		FROM 
			BC_LANC_TRANSF 
			INNER JOIN BC_LANC ORIGEM ON 
				BC_LANC_TRANSF.CD_EMP = ORIGEM.CD_EMP AND 
				BC_LANC_TRANSF.CD_BC_LANC_ORIG = ORIGEM.CD_BC_LANC 
			INNER JOIN BC_LANC DESTINO ON 
				BC_LANC_TRANSF.CD_EMP = DESTINO.CD_EMP AND 
				BC_LANC_TRANSF.CD_BC_LANC_DEST = DESTINO.CD_BC_LANC 
			INNER JOIN
				BC_CONTAS_PRC_GRP_ECON ON 
				BC_CONTAS_PRC_GRP_ECON.CD_EMP = BC_LANC_TRANSF.CD_EMP AND
				BC_CONTAS_PRC_GRP_ECON.CD_CNT = BC_LANC_TRANSF.CD_CNT_ORIG 

		WHERE
			ORIGEM.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM 
			AND BC_LANC_TRANSF.CD_EMP = @CD_EMP 
			AND BC_CONTAS_PRC_GRP_ECON.CD_GRP_ECON = @CD_GRP_ECON
		
		ORDER BY CONTAORIGEM, CONTADESTINO, CONTAORIGEMAUX, CONTADESTINOAUX
		
	
	IF @BLOQUEIO_TRANSACAO_GRUPO_ECONOMICO <> '1'
		BEGIN	
			INSERT INTO @RS_RETURN 			
				SELECT 
					DISTINCT 
					BC_LANC_TRANSF.CD_EMP,
					@CD_FILIAL AS CD_FILIAL,
					ORIGEM.DT_LANCTO, 
					ORIGEM.CD_BC_LANC, 
					ORIGEM.VLR_LANC AS VALORORIGEM,
					DESTINO.VLR_LANC AS VALORDESTINO,
					BC_LANC_TRANSF.CD_CNT_ORIG AS CONTAORIGEM,
					BC_LANC_TRANSF.CD_CNT_DEST AS CONTADESTINO,
					ORIGEM.CD_CNT AS CONTAORIGEMAUX,
					DESTINO.CD_CNT AS CONTADESTINOAUX

				FROM 
					BC_LANC_TRANSF 
					INNER JOIN BC_LANC ORIGEM ON 
					   BC_LANC_TRANSF.CD_EMP = ORIGEM.CD_EMP AND 
					   BC_LANC_TRANSF.CD_BC_LANC_ORIG = ORIGEM.CD_BC_LANC 
					INNER JOIN BC_LANC DESTINO ON 
					   BC_LANC_TRANSF.CD_EMP = DESTINO.CD_EMP AND 
					   BC_LANC_TRANSF.CD_BC_LANC_DEST = DESTINO.CD_BC_LANC 
					INNER JOIN BC_CONTAS_PRC_GRP_ECON ON 
					   BC_CONTAS_PRC_GRP_ECON.CD_EMP = BC_LANC_TRANSF.CD_EMP AND
					   BC_CONTAS_PRC_GRP_ECON.CD_CNT = BC_LANC_TRANSF.CD_CNT_DEST 

				WHERE
					ORIGEM.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM 
					AND BC_LANC_TRANSF.CD_EMP = @CD_EMP
					AND BC_CONTAS_PRC_GRP_ECON.CD_GRP_ECON = @CD_GRP_ECON
					AND NOT EXISTS (SELECT 
										r.CD_BC_LANC
									FROM
										@RS_RETURN r 
									WHERE
										ORIGEM.CD_EMP = r.CD_EMP AND
										ORIGEM.CD_BC_LANC = r.CD_BC_LANC)
				ORDER BY CONTAORIGEM, CONTADESTINO, CONTAORIGEMAUX, CONTADESTINOAUX
		END
		
	RETURN
END		