IF OBJECT_ID('UFD_EXP_CTB_MOV_CHQ_DEST_ENV') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_CHQ_DEST_ENV
	END
	
GO 
 
 
CREATE FUNCTION UFD_EXP_CTB_MOV_CHQ_DEST_ENV(@CD_EMP		 INT,
											 @CD_FILIAL 	 INT,
											 @DT_INI		 DATETIME,
											 @DT_FIM		 DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP					INT,
			CD_FILIAL				INT,
			CD_CNT_ORIG 			INT,
			NR_CHQ					INT, 
			SR_CHQ					VARCHAR(2), 
			ANO_CHQ					INT,
			VLR_CHQ					MONEY,
			VLR_JUROS				MONEY,
			VLR_DESP				MONEY,
			DT_ENV					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,
			CHQ_DEST_ENV.CD_CNT_ORIG, 
			CHQ_LANC_CPL.NR_CHQ, 
			CHQ_LANC_CPL.SR_CHQ, 
			CHQ_LANC_CPL.ANO_CHQ,
			CHQ_LANC_CPL.VLR_CHQ,
			CHQ_DEST_ENV_CPL.VLR_JUROS,
			CHQ_DEST_ENV_CPL.VLR_DESP,
			CHQ_DEST_ENV.DT_ENV
	    
		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 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 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 CHQ_DEST_ENV.DT_ENV	BETWEEN @DT_INI AND @DT_FIM 
	    
	   ORDER BY CHQ_DEST_ENV.DT_ENV, CHQ_DEST_ENV.CD_CNT_ORIG
   
   RETURN
   
END   