IF OBJECT_ID('UFD_EXP_CTB_MOV_CHQ_DEVO') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_CHQ_DEVO
	END
	
GO
  
 
CREATE FUNCTION UFD_EXP_CTB_MOV_CHQ_DEVO(@CD_EMP		 INT,
										 @CD_FILIAL		 INT,
										 @DT_INI		 DATETIME,
										 @DT_FIM		 DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP		INT,
			CD_FILIAL	INT,
			DT_DEVO		DATETIME,
			CD_CNT_CRED		INT, 
			CD_CNT_DEB		INT, 
			VLR_DEVO	MONEY, 
			NR_CHQ		INT, 
			SR_CHQ		VARCHAR(2), 
			ANO_CHQ		INT,
			CD_DEVO        INT,
			CD_BC_LANC_CRED     INT,
			CD_BC_LANC_DEB     INT)			
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 CD_EMP,
	   CD_FILIAL,
	   DT_DEVO,
	   	   SUM(CD_CNT_CRED)CD_CNT_CRED,
	   SUM(CD_CNT_DEB)CD_CNT_DEB,

	   VLR_DEVO,
	   NR_CHQ,
	   SR_CHQ,
	   ANO_CHQ,
	   CD_DEVO,

	   SUM(CD_BC_LANC_CRED)CD_BC_LANC_CRED,
	   SUM(CD_BC_LANC_DEB)CD_BC_LANC_DEB
 FROM(
		SELECT 
			BC_LANC.CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			CHQ_DEVO.DT_DEVO,
			BC_LANC.CD_CNT CD_CNT_CRED, 
			0 AS CD_CNT_DEB, 
			BC_LANC.VLR_LANC AS VLR_DEVO, 
			CHQ_LANC_CPL.NR_CHQ, 
			CHQ_LANC_CPL.SR_CHQ, 
			CHQ_LANC_CPL.ANO_CHQ,
			CHQ_DEVO.CD_DEVO,
			BC_LANC.CD_BC_LANC AS CD_BC_LANC_CRED,
			0                  AS CD_BC_LANC_DEB
			
		FROM 
			BC_LANC 
			INNER JOIN BC_LANC_CHQ_DEVO ON 
				BC_LANC.CD_EMP = BC_LANC_CHQ_DEVO.CD_EMP AND
				BC_LANC.CD_BC_LANC = BC_LANC_CHQ_DEVO.CD_BC_LANC 
			INNER JOIN CHQ_DEVO ON 
				BC_LANC_CHQ_DEVO.CD_EMP = CHQ_DEVO.CD_EMP AND 
				BC_LANC_CHQ_DEVO.CD_DEVO = CHQ_DEVO.CD_DEVO 
			INNER JOIN CHQ_LANC_CPL ON 
				CHQ_DEVO.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
				CHQ_DEVO.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
			INNER JOIN BC_CONTAS_PRC_GRP_ECON ON 
				BC_LANC.CD_EMP = BC_CONTAS_PRC_GRP_ECON.CD_EMP AND
				BC_LANC.CD_CNT = BC_CONTAS_PRC_GRP_ECON.CD_CNT
	    
		WHERE 
			BC_LANC.CD_EMP = @CD_EMP
			AND CHQ_DEVO.DT_DEVO BETWEEN @DT_INI AND @DT_FIM
			AND BC_CONTAS_PRC_GRP_ECON.CD_GRP_ECON = @CD_GRP_ECON 
			AND BC_LANC.TP_LANC = 0
	    UNION 


	    	SELECT 
			BC_LANC.CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			CHQ_DEVO.DT_DEVO,
			0 AS CD_CNT_CRED, 
			BC_LANC.CD_CNT CD_CNT_DEB, 
			BC_LANC.VLR_LANC AS VLR_DEVO, 
			CHQ_LANC_CPL.NR_CHQ, 
			CHQ_LANC_CPL.SR_CHQ, 
			CHQ_LANC_CPL.ANO_CHQ,
			CHQ_DEVO.CD_DEVO,
			0                  AS CD_BC_LANC_CRED,
			BC_LANC.CD_BC_LANC AS CD_BC_LANC_DEB
			
		FROM 
			BC_LANC 
			INNER JOIN BC_LANC_CHQ_DEVO ON 
				BC_LANC.CD_EMP = BC_LANC_CHQ_DEVO.CD_EMP AND
				BC_LANC.CD_BC_LANC = BC_LANC_CHQ_DEVO.CD_BC_LANC 
			INNER JOIN CHQ_DEVO ON 
				BC_LANC_CHQ_DEVO.CD_EMP = CHQ_DEVO.CD_EMP AND 
				BC_LANC_CHQ_DEVO.CD_DEVO = CHQ_DEVO.CD_DEVO 
			INNER JOIN CHQ_LANC_CPL ON 
				CHQ_DEVO.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
				CHQ_DEVO.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
			INNER JOIN BC_CONTAS_PRC_GRP_ECON ON 
				BC_LANC.CD_EMP = BC_CONTAS_PRC_GRP_ECON.CD_EMP AND
				BC_LANC.CD_CNT = BC_CONTAS_PRC_GRP_ECON.CD_CNT
	    
		WHERE 
			BC_LANC.CD_EMP = @CD_EMP
			AND CHQ_DEVO.DT_DEVO BETWEEN @DT_INI AND @DT_FIM
			AND BC_CONTAS_PRC_GRP_ECON.CD_GRP_ECON = @CD_GRP_ECON
			AND BC_LANC.TP_LANC = 1 ) RS 
			GROUP BY CD_EMP,CD_FILIAL,DT_DEVO,VLR_DEVO,NR_CHQ,SR_CHQ,ANO_CHQ,CD_DEVO
	    
		ORDER BY DT_DEVO,ANO_CHQ,NR_CHQ,SR_CHQ
				 
	RETURN
	
END	