IF OBJECT_ID('UFD_EXP_CTB_MOV_BC_LANC_DESP') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_BC_LANC_DESP
	END
	
GO 	
--SELECT * FROM  UFD_EXP_CTB_MOV_BC_LANC_DESP (1,99,'2017-01-01','2017-02-10')
CREATE FUNCTION UFD_EXP_CTB_MOV_BC_LANC_DESP(@CD_EMP		INT,
											@CD_FILIAL	INT,
											@DT_INI			DATETIME,
											@DT_FIM			DATETIME)		
RETURNS @RS_RETURN TABLE(
			CD_EMP					INT,
			CD_FILIAL				INT,
			CD_RAT_DESP				INT,
			VLR_DESP				MONEY,
			CD_CNT					INT,
			CD_DESP_REC				INT, 
			DS_DESP_REC				VARCHAR(100), 
			HIST_LANC				VARCHAR(200), 
			DS_HIST					VARCHAR(200), 
			NR_CNT_DESP				VARCHAR(50),
			CD_CNT_CTB_RED_DESP		INT,
			RATEIO_CENTRO_CUSTO		INT,
			CD_CTB_CNT_CONTA		VARCHAR(50),
			CD_CTB_CNT_RED_CONTA	INT,
			DS_CENTRO_CTB			VARCHAR(100), 
			CNT_CENTRO_CTB			VARCHAR(50),
			VLR_RAT_DESP			MONEY,
			TP_RECUPERA_GLOSA		INT,
			DT_LANCTO				DATETIME,
			CD_BC_LANC				INT)
						
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 
			BC_LANC.CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			PG_DESP_RAT.CD_RAT_DESP,
			PG_DESP_RAT.VLR_DESP AS VLR_DESP,
			BC_LANC.CD_CNT,
			PG_DESP_RAT.CD_DESP_REC, 
			DS_DESP_REC, 
			BC_LANC.HIST_LANC, DS_HIST, 
			GLB_DESP_REC.NR_CNT as NR_CNT_DESP,
			GLB_DESP_REC.CD_CNT_CTB_RED as CD_CNT_CTB_RED_DESP,
			GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
			BC_CONTAS.CD_CTB_CNT as CD_CTB_CNT_CONTA,
			BC_CONTAS.CD_CTB_CNT_RED as CD_CTB_CNT_RED_CONTA,
			ISNULL(CTB_CENTRO_CUSTO.DS_CENTRO_CTB,'') AS DS_CENTRO_CTB, 
			ISNULL(CTB_CENTRO_CUSTO.CNT_CENTRO_CTB,'') AS CNT_CENTRO_CTB,
			ISNULL(PG_DESP_RAT_CTB_CENTRO_CUSTO.VLR_RAT_DESP,0) AS VLR_RAT_DESP,
			ISNULL(RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_EMP,0) AS TP_RECUPERA_GLOSA,
			BC_LANC.DT_LANCTO,
			BC_LANC.CD_BC_LANC
		FROM 
			CTB_CENTRO_CUSTO 
			INNER JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO ON 
				CTB_CENTRO_CUSTO.CD_EMP = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP AND
				CTB_CENTRO_CUSTO.CD_CENTRO_CTB = PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_CENTRO_CTB 
			RIGHT OUTER JOIN BC_LANC 
			INNER JOIN PG_DESP_RAT_BC_LANC ON 
				BC_LANC.CD_EMP = PG_DESP_RAT_BC_LANC.CD_EMP AND
				BC_LANC.CD_BC_LANC = PG_DESP_RAT_BC_LANC.CD_BC_LANC 
			INNER JOIN PG_DESP_RAT ON 
				PG_DESP_RAT_BC_LANC.CD_EMP = PG_DESP_RAT.CD_EMP AND
				PG_DESP_RAT_BC_LANC.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP 
			LEFT JOIN RC_CLI_CONV_GLOSA_PG_DESP_RAT ON
				PG_DESP_RAT.CD_EMP		= RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_EMP AND
				PG_DESP_RAT.CD_RAT_DESP = RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_RAT_DESP 
			INNER JOIN GLB_DESP_REC ON 
				PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP AND
				PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC 							
			INNER JOIN BC_CONTAS ON 
				BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND 
				BC_LANC.CD_CNT = BC_CONTAS.CD_CNT ON
				PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP AND
				PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP = PG_DESP_RAT.CD_EMP						    
			INNER JOIN BC_CONTAS_PRC_GRP_ECON ON
				BC_LANC.CD_CNT = BC_CONTAS_PRC_GRP_ECON.CD_CNT 
				AND BC_CONTAS_PRC_GRP_ECON.CD_GRP_ECON = @CD_GRP_ECON			
						
		WHERE 
			BC_LANC.CD_EMP			=  @CD_EMP		
			AND BC_LANC.DT_LANCTO	BETWEEN @DT_INI AND @DT_FIM 
			AND BC_CONTAS.NAO_EXPORT_CONTABIL =0
			AND GLB_DESP_REC.IG_EXP_CTB=0
		    
		ORDER BY BC_LANC.DT_LANCTO, BC_LANC.CD_CNT, PG_DESP_RAT.CD_DESP_REC, DS_DESP_REC, BC_LANC.HIST_LANC

	RETURN		
		
END		