IF OBJECT_ID('UFD_EXP_CTB_MOV_COMIS_CARTAO') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_COMIS_CARTAO
	END
	
GO	
  
CREATE FUNCTION UFD_EXP_CTB_MOV_COMIS_CARTAO(  @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_ADM_CARTAO		MONEY,
		VLR_CARTAO			MONEY,
		VLR_COMISSAO		MONEY,
		TX_ADM				MONEY,
		CD_CLI				INT)		
AS
BEGIN       

	INSERT INTO @RS_RETURN              
		--CARTOES DEBITOS
		SELECT
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_EMP,
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_FILIAL,
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.DT_MOV,
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_ADM_CARTAO,
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.VLR_CARTAO,
			ISNULL(PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.VLR_CARTAO,0) * (ISNULL(RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA,0)/100) AS VLR_COMISSAO,
			RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA AS TX_ADM,
			RC_ADM_CARTAO_RC_CLI.CD_CLI
		    
		FROM
			RC_ADM_CARTAO 
			INNER JOIN PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO ON 
				RC_ADM_CARTAO.CD_EMP		= PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_EMP AND
				RC_ADM_CARTAO.CD_ADM_CARTAO = PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_ADM_CARTAO 
			INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON 
				PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_EMP		= PRC_GRP_ECON_PRC_FILIAL.CD_EMP 
				AND PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL 
			INNER JOIN RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS ON 
				RC_ADM_CARTAO.CD_EMP				= RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_EMP AND
				RC_ADM_CARTAO.CD_ADM_CARTAO			= RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_ADM_CARTAO AND
				PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_GRP_ECON 			
			INNER JOIN RC_ADM_CARTAO_RC_CLI ON 
				RC_ADM_CARTAO.CD_EMP		= RC_ADM_CARTAO_RC_CLI.CD_EMP AND
				RC_ADM_CARTAO.CD_ADM_CARTAO = RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO 
			INNER JOIN RC_CLI ON 
				RC_ADM_CARTAO_RC_CLI.CD_EMP = RC_CLI.CD_EMP AND 
				RC_ADM_CARTAO_RC_CLI.CD_CLI = RC_CLI.CD_CLI
	  
		WHERE
			PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_EMP		= @CD_EMP
			AND PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.CD_FILIAL = @CD_FILIAL
			AND PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO.DT_MOV	BETWEEN @DT_INI AND @DT_FIM		    
	            
		UNION ALL 
	    
		-- CARTÃO CREDITO
		SELECT 
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_EMP,
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_FILIAL,
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.DT_MOV,
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_ADM_CARTAO,
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.VLR_CARTAO,
			ISNULL(PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.VLR_CARTAO,0) * (ISNULL(RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA,0)/100) AS VLR_COMISSAO,
			RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA AS TX_ADM,
			RC_ADM_CARTAO_RC_CLI.CD_CLI
	    
		FROM
			RC_ADM_CARTAO     
			INNER JOIN PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO ON 
					RC_ADM_CARTAO.CD_EMP = PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_EMP AND
					RC_ADM_CARTAO.CD_ADM_CARTAO = PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_ADM_CARTAO 
			INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON 
					PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_EMP		= PRC_GRP_ECON_PRC_FILIAL.CD_EMP 
					AND PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL 
			INNER JOIN RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS ON 
					RC_ADM_CARTAO.CD_EMP				= RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_EMP AND
					RC_ADM_CARTAO.CD_ADM_CARTAO			= RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_ADM_CARTAO AND
					PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_GRP_ECON
			INNER JOIN RC_ADM_CARTAO_RC_CLI ON 
					RC_ADM_CARTAO.CD_EMP = RC_ADM_CARTAO_RC_CLI.CD_EMP AND
					RC_ADM_CARTAO.CD_ADM_CARTAO = RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO 
			INNER JOIN RC_CLI ON 
					RC_ADM_CARTAO_RC_CLI.CD_EMP = RC_CLI.CD_EMP AND 
					RC_ADM_CARTAO_RC_CLI.CD_CLI = RC_CLI.CD_CLI
			    
		WHERE
			PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_EMP			= @CD_EMP
			AND PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.CD_FILIAL	= @CD_FILIAL
			AND PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO.DT_MOV		BETWEEN @DT_INI AND @DT_FIM 
		    
	RETURN
		    
END		    