IF OBJECT_ID('P_EXP_CTB_MOV_COMIS_CARTAO') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EXP_CTB_MOV_COMIS_CARTAO
	END
 
GO
    
CREATE PROCEDURE P_EXP_CTB_MOV_COMIS_CARTAO(@CD_EMP		INT,
										   @CD_FILIAL	INT,
										   @DT_INI		DATETIME,
										   @DT_FIM		DATETIME,
										   @FLAG_LIMPAR INT = 0)		
	
WITH ENCRYPTION AS 

BEGIN TRY
BEGIN
SET NOCOUNT ON;	
	DECLARE @RS_CD_EMP					INT
	DECLARE @RS_CD_FILIAL				INT 		
	DECLARE @RS_DT_MOV   				DATETIME					
	DECLARE @RS_VLR_COMISSAO			MONEY
	DECLARE @RS_CD_CLI					INT	
	DECLARE @RS_RZ_CLI   				VARCHAR(100)
	DECLARE @RS_CD_CTB_CNT_RED_NORMAL	INT
	DECLARE @RS_CD_CTB_CNT_NORMAL		VARCHAR(50)
	DECLARE @RS_CD_CTB_CNT_RED_COMIS	INT
	DECLARE @RS_CD_CTB_CNT_COMIS		VARCHAR(50)	
	
	DECLARE @RET_DS_CENTRO_CUSTO		VARCHAR(100)
	DECLARE @RET_CNT_CENTRO_CUSTO		INT
	DECLARE @NR_CONTROLE			VARCHAR (12)
	
	------------------------------------------------------------------------------------------------------------------------------
	--CRIANDO A TABELA TEMPORARIA ##CTB_EXP_MOV
	------------------------------------------------------------------------------------------------------------------------------
	EXEC P_EXP_CTB_MOV_CRIAR_CTB_EXP_MOV @FLAG_LIMPAR	
	
	DECLARE RS_RESULT
	CURSOR FOR 	
		SELECT
			T.CD_EMP,
			T.CD_FILIAL,
			T.DT_MOV,			
			SUM(T.VLR_COMISSAO) AS VLR_COMISSAO,			
			T.CD_CLI,
			RC_CLI.RZ_CLI,
			ISNULL(CTB_NORMAL.CD_CTB_CNT_RED,0) AS CD_CTB_CNT_RED_NORMAL,
			ISNULL(CTB_NORMAL.CD_CTB_CNT,'')	AS CD_CTB_CNT_NORMAL,
			ISNULL(CTB_COMIS.CD_CTB_CNT_RED,0)	AS CD_CTB_CNT_RED_COMIS,
			ISNULL(CTB_COMIS.CD_CTB_CNT,'')		AS CD_CTB_CNT_COMIS			
			
		--FROM UFD_EXP_CTB_MOV_COMIS_CARTAO(1, 3, '20170416', '20170416') T
		FROM UFD_EXP_CTB_MOV_COMIS_CARTAO(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM) T

				INNER JOIN RC_CLI ON
					T.CD_EMP		= RC_CLI.CD_EMP
					AND T.CD_CLI	= RC_CLI.CD_CLI
				LEFT OUTER JOIN RC_CLI_CNT_CTB CTB_NORMAL ON 
					CTB_NORMAL.CD_EMP			= RC_CLI.CD_EMP
					AND CTB_NORMAL.CD_CLI		= RC_CLI.CD_CLI
					AND CTB_NORMAL.TP_CNT_CTB	= 0			
				LEFT OUTER JOIN RC_CLI_CNT_CTB CTB_COMIS ON 
					CTB_COMIS.CD_EMP			= RC_CLI.CD_EMP
					AND CTB_COMIS.CD_CLI		= RC_CLI.CD_CLI
					AND CTB_COMIS.TP_CNT_CTB	= 1			
		GROUP BY 
			T.CD_EMP,
			T.CD_FILIAL,
			T.DT_MOV,									
			T.CD_CLI,
			RC_CLI.RZ_CLI,
			ISNULL(CTB_NORMAL.CD_CTB_CNT_RED,0),
			ISNULL(CTB_NORMAL.CD_CTB_CNT,''),
			ISNULL(CTB_COMIS.CD_CTB_CNT_RED,0),
			ISNULL(CTB_COMIS.CD_CTB_CNT,'')
		

		--Abrindo cursor
		OPEN RS_RESULT
		
		--Atribuindo valores do select nas variáveis
		FETCH NEXT FROM RS_RESULT INTO 						
					@RS_CD_EMP,					
					@RS_CD_FILIAL,				
					@RS_DT_MOV,   				
					@RS_VLR_COMISSAO,			
					@RS_CD_CLI,					
					@RS_RZ_CLI,   				
					@RS_CD_CTB_CNT_RED_NORMAL,	
					@RS_CD_CTB_CNT_NORMAL,		
					@RS_CD_CTB_CNT_RED_COMIS,	
					@RS_CD_CTB_CNT_COMIS		
					 

		--Iniciando laço
		WHILE @@FETCH_STATUS = 0
			BEGIN	
			
				------------------------------------------------------------------------------------------------------------------
				--DOACAO DEBITO - CLIENTE CARTAO
				------------------------------------------------------------------------------------------------------------------

				SET @NR_CONTROLE = RIGHT('000'+CAST(@RS_CD_FILIAL AS VARCHAR(3)),3) +  
								RIGHT('00'+ CAST( DAY( @RS_DT_MOV) AS VARCHAR),2) + 
								 RIGHT('00'+  CAST( MONTH(@RS_DT_MOV) AS VARCHAR),2) + 
								  RIGHT('00'+ CAST( YEAR(@RS_DT_MOV)AS VARCHAR),2) + 
								   RIGHT('000'+ CAST(@RS_CD_CLI AS VARCHAR),3)

				SELECT 					
					@RET_DS_CENTRO_CUSTO		= DS_CENTRO_CTB,
					@RET_CNT_CENTRO_CUSTO		= CNT_CENTRO_CTB
				FROM UFD_BUSCA_CONTA_FILIAIS (@RS_CD_EMP, @RS_CD_FILIAL, 9 )
				
				INSERT INTO ##CTB_EXP_MOV  

									   	 (CD_EMP,CD_FILIAL_EXPORT, CD_FILIAL,  CD_MOVIMENTO, MOVIMENTO, OPERACAO, OPERACAO_DEBITO,
										  CD_CNT_RED_CREDITO, CD_CNT_CREDITO, CD_CNT_RED_DEBITO, CD_CNT_DEBITO, DT_MOV, HIST,
										  VLR_LANC, TP_TRANSA, RATEIO_CENTRO_CUSTO, DS_CENTRO_CUSTO, CNT_CENTRO_CUSTO, VLR_CENTRO_CUSTO,
										  ORIG_CONTA, COD_CONTA,NM_PROCEDURE,TIPO_LANCTO, CD_EVENTO, NR_DP, ANO, NR_DOC, CD_BC_LANC)

					SELECT		@RS_CD_EMP							AS CD_EMP,
								@CD_FILIAL							AS CD_FILIAL_EXPORT,
								@RS_CD_FILIAL						AS CD_FILIAL,
								10									AS CD_MOVIMENTO,
								'COMISSOES DE CARTAO'				AS MOVIMENTO,
								''									AS OPERACAO,
								'COMISSOES - DEBITO'				AS OPERACAO_DEBITO,
								0									AS CD_CNT_RED_CREDITO,
								''									AS CD_CNT_CREDITO,
								@RS_CD_CTB_CNT_RED_COMIS			AS CD_CNT_RED_DEBITO,
								@RS_CD_CTB_CNT_COMIS				AS CD_CNT_DEBITO,
								@RS_DT_MOV							AS DT_MOV,
								'VALOR REF. COMISSOES - CARTAO: ' + CAST(@RS_CD_CLI AS VARCHAR) + ' - ' + @RS_RZ_CLI AS HIST,
								@RS_VLR_COMISSAO					AS VLR_LANC,
								'D'									AS TP_TRANSA,
								1									AS RATEIO_CENTRO_CUSTO,
								@RET_DS_CENTRO_CUSTO				AS DS_CENTRO_CUSTO,
								@RET_CNT_CENTRO_CUSTO				AS CNT_CENTRO_CUSTO,
								@RS_VLR_COMISSAO					AS VLR_CENTRO_CUSTO,
								'CLIENTES COMIS'					AS ORIG_CONTA,
								@RS_CD_CLI							AS COD_CONTA,
								OBJECT_NAME(@@PROCID)				AS NM_PROCEDURE	
								, 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
								, 0  AS NR_DP
								, 0  AS ANO
								, @NR_CONTROLE AS NR_DOC
								, '' AS CD_BC_LANC;

				------------------------------------------------------------------------------------------------------------------
				--DOACAO CREDITO - CONTAS DAS ENTIDADES
				------------------------------------------------------------------------------------------------------------------
				INSERT INTO ##CTB_EXP_MOV  

								    (CD_EMP,CD_FILIAL_EXPORT, CD_FILIAL,  CD_MOVIMENTO, MOVIMENTO, OPERACAO, OPERACAO_DEBITO,
								    CD_CNT_RED_CREDITO, CD_CNT_CREDITO, CD_CNT_RED_DEBITO, CD_CNT_DEBITO, DT_MOV, HIST,
								    VLR_LANC, TP_TRANSA, RATEIO_CENTRO_CUSTO, DS_CENTRO_CUSTO, CNT_CENTRO_CUSTO, VLR_CENTRO_CUSTO,
								    ORIG_CONTA, COD_CONTA,NM_PROCEDURE,TIPO_LANCTO, CD_EVENTO, NR_DP, ANO, NR_DOC, CD_BC_LANC)

					SELECT		@RS_CD_EMP							AS CD_EMP,
								@CD_FILIAL							AS CD_FILIAL_EXPORT,
								@RS_CD_FILIAL						AS CD_FILIAL,
								10									AS CD_MOVIMENTO,
								'COMISSOES DE CARTAO'				AS MOVIMENTO,
								'COMISSOES - CREDITO'				AS OPERACAO,
								''									AS OPERACAO_DEBITO,
								@RS_CD_CTB_CNT_RED_NORMAL			AS CD_CNT_RED_CREDITO,
								@RS_CD_CTB_CNT_NORMAL 				AS CD_CNT_CREDITO,
								0									AS CD_CNT_RED_DEBITO,
								''									AS CD_CNT_DEBITO,
								@RS_DT_MOV							AS DT_MOV,
								'VALOR REF. COMISSOES - CARTAO: ' + CAST(@RS_CD_CLI AS VARCHAR) + ' - ' + @RS_RZ_CLI AS HIST,
								@RS_VLR_COMISSAO					AS VLR_LANC,
								'C'									AS TP_TRANSA,
								0									AS RATEIO_CENTRO_CUSTO,
								''									AS DS_CENTRO_CUSTO,
								0									AS CNT_CENTRO_CUSTO,
								0									AS VLR_CENTRO_CUSTO,
								'CLIENTES'							AS ORIG_CONTA,
								@RS_CD_CLI							AS COD_CONTA,
								OBJECT_NAME(@@PROCID)				AS NM_PROCEDURE
								, 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
								, 0  AS NR_DP
								, 0  AS ANO
								, @NR_CONTROLE AS NR_DOC
								, '' AS CD_BC_LANC;
	
												
				------------------------------------------------------------------------------------------------------------------
				--Próximo registro				
				------------------------------------------------------------------------------------------------------------------
				FETCH NEXT FROM RS_RESULT INTO 
							@RS_CD_EMP,					
							@RS_CD_FILIAL,				
							@RS_DT_MOV,   				
							@RS_VLR_COMISSAO,			
							@RS_CD_CLI,					
							@RS_RZ_CLI,   				
							@RS_CD_CTB_CNT_RED_NORMAL,	
							@RS_CD_CTB_CNT_NORMAL,		
							@RS_CD_CTB_CNT_RED_COMIS,	
							@RS_CD_CTB_CNT_COMIS		

			END
		
		CLOSE RS_RESULT
		DEALLOCATE RS_RESULT	
END

END TRY
BEGIN CATCH
	CLOSE RS_RESULT
	DEALLOCATE RS_RESULT	

	INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro)
		SELECT
			ERROR_NUMBER() AS numero,
			ERROR_SEVERITY() AS severidade,
			ERROR_STATE() AS estado,
			ERROR_PROCEDURE() AS rotina,
			ERROR_LINE() AS linha,
			ERROR_MESSAGE() AS mensagem,
			GETDATE() AS dtErro;	
END CATCH					
  