IF OBJECT_ID('UFD_EXP_CTB_MOV_BC_LANC_CHQ_DEST_ENV_CPL') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_BC_LANC_CHQ_DEST_ENV_CPL
	END
	
GO  
 
CREATE FUNCTION UFD_EXP_CTB_MOV_BC_LANC_CHQ_DEST_ENV_CPL(@CD_EMP		 INT,
														@CD_FILIAL 		 INT,
														@DT_INI		 DATETIME,
														@DT_FIM		 DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP			INT,
			CD_FILIAL		INT,
			DT_RECB			DATETIME,
			CD_CNT_ORIG		INT, 
			CD_CNT_DEPO		INT,
			CD_CNT			INT, 
			NR_CHQ			INT, 
			SR_CHQ			VARCHAR(2), 
			ANO_CHQ			INT,
			VLR_CHQ			MONEY,
			VLR_RECB		MONEY,
			VLR_JUROS		MONEY,
			VLR_DESP		MONEY,
			VLR_DESC        MONEY,
			CD_CHQ          INT,
			VLR_LANC        MONEY,
			DT_LANCTO		DATETIME)			
AS
BEGIN       

	DECLARE @CD_GRP_ECON			INT
	
	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 
			CHQ_DEST_ENV_CPL.CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			DT_RECB,
			CHQ_DEPOS.CD_CNT_ORIG, 
			CHQ_DEPOS.CD_CNT AS CD_CNT_DEPO,
			BC_LANC.CD_CNT, 
			NR_CHQ, 
			SR_CHQ, 
			ANO_CHQ,
			VLR_CHQ,
			VLR_RECB,
			VLR_JUROS,
			VLR_DESP,
			ISNULL(CHQ_DEST_ENV_CPL.VLR_DESC,0) AS VLR_DESC,
			CHQ_LANC_CPL.CD_CHQ,
			BC_LANC.VLR_LANC,
			BC_LANC.DT_LANCTO

		FROM 
			CHQ_DEST_ENV_CPL
			INNER JOIN CHQ_DEST_ENV ON
				CHQ_DEST_ENV_CPL.CD_EMP      = CHQ_DEST_ENV.CD_EMP 
				AND CHQ_DEST_ENV_CPL.ANO_ENV = CHQ_DEST_ENV.ANO_ENV 
				AND CHQ_DEST_ENV_CPL.CD_ENV  = CHQ_DEST_ENV.CD_ENV 
			INNER JOIN CHQ_DEST ON
				CHQ_DEST_ENV.CD_EMP			 = CHQ_DEST.CD_EMP 
				AND CHQ_DEST_ENV.CD_DEST     = CHQ_DEST.CD_DEST 
			INNER JOIN BC_LANC_CHQ_DEST_ENV_CPL ON
				CHQ_DEST_ENV_CPL.CD_EMP	     = BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP 
				AND CHQ_DEST_ENV_CPL.ANO_ENV = BC_LANC_CHQ_DEST_ENV_CPL.ANO_ENV 
				AND CHQ_DEST_ENV_CPL.CD_ENV  = BC_LANC_CHQ_DEST_ENV_CPL.CD_ENV 
				AND CHQ_DEST_ENV_CPL.CD_CHQ  = BC_LANC_CHQ_DEST_ENV_CPL.CD_CHQ 
			INNER JOIN BC_LANC ON
				BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP			= BC_LANC.CD_EMP 
				AND BC_LANC_CHQ_DEST_ENV_CPL.CD_BC_LANC = BC_LANC.CD_BC_LANC 
			INNER JOIN CHQ_LANC_CPL ON
				CHQ_LANC_CPL.CD_EMP      = CHQ_DEST_ENV_CPL.CD_EMP 
				AND CHQ_LANC_CPL.CD_CHQ  = CHQ_DEST_ENV_CPL.CD_CHQ 
			LEFT OUTER JOIN CHQ_DEPOS_CPL ON
				CHQ_DEPOS_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP 
				AND CHQ_DEPOS_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
			LEFT OUTER JOIN CHQ_DEPOS ON
				CHQ_DEPOS.CD_EMP		= CHQ_DEPOS_CPL.CD_EMP 
				AND CHQ_DEPOS.CD_DEPOS	= CHQ_DEPOS_CPL.CD_DEPOS 
				AND CHQ_DEPOS.DT_DEPOS	= (SELECT 
											   MAX(DT_DEPOS) 
										   FROM 
											   CHQ_DEPOS 
										   WHERE 
											   CHQ_DEPOS.CD_EMP = CHQ_DEPOS_CPL.CD_EMP AND
											   CHQ_DEPOS.CD_DEPOS = CHQ_DEPOS_CPL.CD_DEPOS)

		WHERE 
			CHQ_DEST_ENV_CPL.CD_EMP = @CD_EMP 
			AND VLR_RECB > 0  
			AND CHQ_DEPOS.CD_CNT IN (SELECT CD_CNT FROM BC_CONTAS_PRC_GRP_ECON WHERE CD_EMP = @CD_EMP AND CD_GRP_ECON = @CD_GRP_ECON)
			AND DT_RECB BETWEEN @DT_INI AND @DT_FIM
			AND TP_DEST = 0
	    
		ORDER BY DT_RECB, CHQ_DEST_ENV.CD_CNT_ORIG
		

	RETURN
		
END		