IF OBJECT_ID('UFD_EXP_CTB_MOV_DESP') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_DESP
	END
GO	
 
CREATE FUNCTION UFD_EXP_CTB_MOV_DESP(  @CD_EMP		INT,
									   @CD_FILIAL	INT,
									   @DT_INI		DATETIME,
									   @DT_FIM		DATETIME)		

RETURNS @RS_RETURN TABLE(
		CD_EMP				INT, 
		CD_FILIAL			INT, 		
		DT_MOV				DATETIME, 		
		CD_RAT_DESP			INT, 
		CD_CNT_CTB_RED		VARCHAR(50), 
		CD_CNT_CTB			VARCHAR(50), 
		DS_DESP_REC			VARCHAR(100),
		RATEIO_CENTRO_CUSTO	INT,
		CD_DESP_REC			INT,
		VLR_LIQ				MONEY, 
		CNT_CENTRO_CTB      VARCHAR(100),
		DS_CENTRO_CTB		VARCHAR(100), 
		VLR_RAT_DESP		MONEY)		
AS
BEGIN

    INSERT INTO @RS_RETURN 
		SELECT 
			PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_EMP,
			PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_FILIAL,
			PG_DESP_RAT_PDV_VD_FECHA_MOV.DT_MOV, 
			PG_DESP_RAT.CD_RAT_DESP, 
			GLB_DESP_REC.CD_CNT_CTB_RED, 
			GLB_DESP_REC.NR_CNT AS CD_CNT_CTB, 
			GLB_DESP_REC.DS_DESP_REC,
			GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
			GLB_DESP_REC.CD_DESP_REC,
			PG_DESP_RAT.VLR_DESP AS VLR_LIQ, 
			ISNULL(CTB_CENTRO_CUSTO.CNT_CENTRO_CTB,'') AS CNT_CENTRO_CTB, 
			ISNULL(CTB_CENTRO_CUSTO.DS_CENTRO_CTB,'') AS DS_CENTRO_CTB, 
			ISNULL(PG_DESP_RAT_CTB_CENTRO_CUSTO.VLR_RAT_DESP,0) AS VLR_RAT_DESP
			
		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 GLB_DESP_REC 
			INNER JOIN PG_DESP_RAT ON 
				GLB_DESP_REC.CD_EMP = PG_DESP_RAT.CD_EMP AND
				GLB_DESP_REC.CD_DESP_REC = PG_DESP_RAT.CD_DESP_REC 
			INNER JOIN PG_DESP_RAT_PDV_VD_FECHA_MOV ON 
				PG_DESP_RAT.CD_EMP = PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_EMP AND
				PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_RAT_DESP ON
				PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP = PG_DESP_RAT.CD_EMP AND
				PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP
	    
		WHERE 
			PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_EMP			= 1 
			AND PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_FILIAL  = @CD_FILIAL
			AND PG_DESP_RAT_PDV_VD_FECHA_MOV.DT_MOV		BETWEEN @DT_INI AND @DT_FIM 

    UNION ALL

    SELECT 
	   PG_DESP_RAT.CD_EMP,
	   PG_DESP_RAT.CD_FILIAL,
	   RC_ADM_CARTAO_MOV.DT_LANC AS DT_MOV, 
	   PG_DESP_RAT.CD_RAT_DESP, 
	   GLB_DESP_REC.CD_CNT_CTB_RED, 
	   GLB_DESP_REC.NR_CNT AS CD_CNT_CTB, 
	   GLB_DESP_REC.DS_DESP_REC,
	   GLB_DESP_REC.RATEIO_CENTRO_CUSTO,
	   GLB_DESP_REC.CD_DESP_REC,
	   PG_DESP_RAT.VLR_DESP AS VLR_LIQ, 
	   ISNULL(CTB_CENTRO_CUSTO.CNT_CENTRO_CTB,'') AS CNT_CENTRO_CTB, 
	   ISNULL(CTB_CENTRO_CUSTO.DS_CENTRO_CTB,'') AS DS_CENTRO_CTB, 
	   ISNULL(PG_DESP_RAT_CTB_CENTRO_CUSTO.VLR_RAT_DESP,0) AS VLR_RAT_DESP
			
    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 GLB_DESP_REC 
	   INNER JOIN PG_DESP_RAT ON 
		  GLB_DESP_REC.CD_EMP = PG_DESP_RAT.CD_EMP AND
		  GLB_DESP_REC.CD_DESP_REC = PG_DESP_RAT.CD_DESP_REC 
	   INNER JOIN PG_DESP_RAT_RC_ADM_CARTAO_MOV ON 
		  PG_DESP_RAT.CD_EMP = PG_DESP_RAT_RC_ADM_CARTAO_MOV.CD_EMP AND
		  PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_RC_ADM_CARTAO_MOV.CD_RAT_DESP ON
		  PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_EMP = PG_DESP_RAT.CD_EMP AND
		  PG_DESP_RAT_CTB_CENTRO_CUSTO.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP
	   INNER JOIN RC_ADM_CARTAO_MOV ON 
		  RC_ADM_CARTAO_MOV.CD_CTR= PG_DESP_RAT_RC_ADM_CARTAO_MOV.CD_CTR
    WHERE 
	   PG_DESP_RAT.CD_EMP			= 1 
	   AND PG_DESP_RAT.CD_FILIAL  = @CD_FILIAL
	   AND RC_ADM_CARTAO_MOV.DT_LANC BETWEEN @DT_INI AND @DT_FIM  

	
	RETURN 
	
END	