IF OBJECT_ID('UFD_EXP_CTB_MOV_CHQ_DEST_ENV_RECTO') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_CHQ_DEST_ENV_RECTO
	END
	
GO 
 
CREATE FUNCTION UFD_EXP_CTB_MOV_CHQ_DEST_ENV_RECTO( @CD_EMP		 INT,
													@CD_FILIAL	 INT,
													@DT_INI		 DATETIME,
													@DT_FIM		 DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP				INT,
			CD_FILIAL			INT,
			NR_CHQ				INT, 
			SR_CHQ				VARCHAR(2), 
			ANO_CHQ				INT, 
			VLR_CHQ				MONEY,
			VLR_RECB			MONEY,
			VLR_JUROS			MONEY,
			VLR_DESP			MONEY,
			TP_DEST				INT,  
			CD_CNT				INT,
			DT_RECB				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,
			CHQ_LANC.CD_FILIAL,
			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_RECB,
			CHQ_DEST_ENV_CPL.VLR_JUROS,
			CHQ_DEST_ENV_CPL.VLR_DESP,
			CHQ_DEST.TP_DEST,  
			BC_LANC.CD_CNT,
			CHQ_DEST_ENV_CPL.DT_RECB
	    
		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_LANC_CPL ON 
				CHQ_DEST_ENV_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND
				CHQ_DEST_ENV_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
		    
			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_CHQ_LANC_CPL ON 
				CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP AND
				CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ 
				
			INNER JOIN CHQ_LANC ON 
				CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC.CD_EMP AND 
				CHQ_LANC_CHQ_LANC_CPL.ANO = CHQ_LANC.ANO AND
				CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC = CHQ_LANC.CD_CHQ_LANC
		        
			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
	        
		WHERE 
			CHQ_DEST_ENV_CPL.CD_EMP 		= @CD_EMP
			AND CHQ_LANC.CD_FILIAL  		IN (SELECT CD_FILIAL 
												FROM PRC_GRP_ECON_PRC_FILIAL
												WHERE CD_GRP_ECON = @CD_GRP_ECON)
			AND CHQ_DEST_ENV_CPL.VLR_RECB 	> 0
			AND CHQ_DEST_ENV_CPL.DT_RECB 	BETWEEN @DT_INI AND @DT_FIM
			AND TP_DEST = 1
		
		ORDER BY CHQ_DEST_ENV_CPL.DT_RECB, CHQ_DEST_ENV.CD_CNT_ORIG
			
	RETURN
			
END			