IF OBJECT_ID('P_EXP_CTB_MOV_DESP') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EXP_CTB_MOV_DESP
	END

GO
 
CREATE PROCEDURE P_EXP_CTB_MOV_DESP(@CD_EMP		INT,
								   @CD_FILIAL	INT,
								   @DT_INI		DATETIME,
								   @DT_FIM		DATETIME,
								   @FLAG_LIMPAR INT = 0)		
	
WITH ENCRYPTION AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN
	
	DECLARE @RS_CD_EMP				INT 
	DECLARE @RS_CD_FILIAL			INT 		
	DECLARE @RS_DT_MOV				DATETIME 		
	DECLARE @RS_CD_RAT_DESP			INT 
	DECLARE @RS_CD_CNT_CTB_RED		VARCHAR(50) 
	DECLARE @RS_CD_CNT_CTB			VARCHAR(50) 
	DECLARE @RS_DS_DESP_REC			VARCHAR(100)
	DECLARE @RS_RATEIO_CENTRO_CUSTO	INT
	DECLARE @RS_CD_DESP_REC			INT
	DECLARE @RS_VLR_LIQ				MONEY
	DECLARE @RS_CNT_CENTRO_CTB      VARCHAR(100)
	DECLARE @RS_DS_CENTRO_CTB		VARCHAR(100)
	DECLARE @RS_VLR_RAT_DESP		MONEY
    DECLARE @RS_CD_FILIAL_LANC 		INT
	DECLARE @RS_CD_ADM_CARTAO		INT
	
	
    DECLARE @TOT_RS_VLR_LIQ			MONEY    
	DECLARE @RS_DT_MOV_ANT			DATETIME
	DECLARE @RS_CD_FILIAL_ANT		INT
	DECLARE @RS_CD_FILIAL_ANT_2		INT
	DECLARE @NR_CONTROLE          AS INT
	DECLARE @NR_CNT_RED INT
	DECLARE @NR_CNT VARCHAR(50)
	DECLARE @NM_ADM VARCHAR(15)
	
	SET @TOT_RS_VLR_LIQ = 0
    
	------------------------------------------------------------------------------------------------------------------------------
	--CRIANDO A TABELA TEMPORARIA ##CTB_EXP_MOV
	------------------------------------------------------------------------------------------------------------------------------
	EXEC P_EXP_CTB_MOV_CRIAR_CTB_EXP_MOV @FLAG_LIMPAR
	
	DECLARE RS_RESULT
	CURSOR FOR 	
		SELECT
			CD_EMP,
			CD_FILIAL,
			DT_MOV,
			CD_RAT_DESP,
			CD_CNT_CTB_RED,
			CD_CNT_CTB,
			DS_DESP_REC,
			RATEIO_CENTRO_CUSTO,
			CD_DESP_REC,
			VLR_LIQ,
			CNT_CENTRO_CTB,
			DS_CENTRO_CTB,
			VLR_RAT_DESP,
			CD_FILIAL_LANC,
			CD_ADM_CARTAO
		FROM UFD_EXP_CTB_MOV_DESP(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
		--FROM UFD_EXP_CTB_MOV_DESP(1, 1, '20170301', '20170301')
		ORDER BY 
			CD_FILIAL,
			DT_MOV
		

		--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_CD_RAT_DESP,
				@RS_CD_CNT_CTB_RED,
				@RS_CD_CNT_CTB,
				@RS_DS_DESP_REC,
				@RS_RATEIO_CENTRO_CUSTO,
				@RS_CD_DESP_REC,
				@RS_VLR_LIQ,
				@RS_CNT_CENTRO_CTB,
				@RS_DS_CENTRO_CTB,
				@RS_VLR_RAT_DESP,
				@RS_CD_FILIAL_LANC,
				@RS_CD_ADM_CARTAO
 
		SET @NR_CONTROLE =0  

		--Iniciando laço
		WHILE @@FETCH_STATUS = 0
			BEGIN
				
				IF @NR_CONTROLE= 0 
				    BEGIN 
					   SET @NR_CONTROLE =	@RS_CD_RAT_DESP	
				    END		
				------------------------------------------------------------------------------------------------------------------
				--ICMS A RECUPERAR - Movimento de Despesas
				------------------------------------------------------------------------------------------------------------------								
				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,
								@RS_CD_FILIAL					AS CD_FILIAL_EXPORT,
								@RS_CD_FILIAL_LANC					AS CD_FILIAL,
								8								AS CD_MOVIMENTO,
								'DESPESAS'						AS MOVIMENTO,
								'LANCTO DESPESAS - DEBITO'			AS OPERACAO,
								'LANCTO DESPESAS - DEBITO'			AS OPERACAO_DEBITO,
								0								AS CD_CNT_RED_CREDITO,
								''								AS CD_CNT_CREDITO,
								@RS_CD_CNT_CTB_RED					AS CD_CNT_RED_DEBITO,
								@RS_CD_CNT_CTB						AS CD_CNT_DEBITO,
								@RS_DT_MOV						AS DT_MOV,
								'DESPESAS - CONF.COMPROV.FIL.: ' + RIGHT('000' + CAST(@RS_CD_FILIAL_LANC AS VARCHAR), 3) + ' - ' + @RS_DS_DESP_REC AS HIST,
								@RS_VLR_LIQ						AS VLR_LANC,
								'D'								AS TP_TRANSA,
								@RS_RATEIO_CENTRO_CUSTO				AS RATEIO_CENTRO_CUSTO,
								@RS_DS_CENTRO_CTB					AS DS_CENTRO_CUSTO,
								@RS_CNT_CENTRO_CTB					AS CNT_CENTRO_CUSTO,
								@RS_VLR_RAT_DESP					AS VLR_CENTRO_CUSTO,
								'DESP\REC'						AS ORIG_CONTA,
								@RS_CD_DESP_REC					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;
								
				SET @TOT_RS_VLR_LIQ = @TOT_RS_VLR_LIQ + @RS_VLR_LIQ
				
				-- SET @RS_CD_FILIAL_ANT	= @RS_CD_FILIAL
				SET @RS_CD_FILIAL_ANT	= @RS_CD_FILIAL_LANC
				SET @RS_DT_MOV_ANT		= @RS_DT_MOV 
				SET @RS_CD_FILIAL_ANT_2	= @RS_CD_FILIAL
				
				--Próximo registro				
				FETCH NEXT FROM RS_RESULT INTO 
						@RS_CD_EMP,
						@RS_CD_FILIAL,
						@RS_DT_MOV,
						@RS_CD_RAT_DESP,
						@RS_CD_CNT_CTB_RED,
						@RS_CD_CNT_CTB,
						@RS_DS_DESP_REC,
						@RS_RATEIO_CENTRO_CUSTO,
						@RS_CD_DESP_REC,
						@RS_VLR_LIQ,
						@RS_CNT_CENTRO_CTB,
						@RS_DS_CENTRO_CTB,
						@RS_VLR_RAT_DESP,
						@RS_CD_FILIAL_LANC,
						@RS_CD_ADM_CARTAO

																				
				IF 	@RS_CD_FILIAL_LANC <> @RS_CD_FILIAL_ANT OR @RS_DT_MOV <> @RS_DT_MOV_ANT OR @@FETCH_STATUS <> 0
					BEGIN 
						------------------------------------------------------------------------------------------------------------------
						--ICMS A RECUPERAR - Ctb_Cnt_Caixa
						------------------------------------------------------------------------------------------------------------------
						SET @NR_CNT_RED = 0
						SET @NR_CNT = ''
						SET @NM_ADM = ''
						
						IF @RS_CD_ADM_CARTAO > 0
						BEGIN
						
							SELECT @NR_CNT_RED = ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT_RED,0) 
								  ,@NR_CNT = ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT,'')
								  ,@NM_ADM = ISNULL(left(RC_CLI.RZ_CLI,15),'')
							FROM RC_CLI
							INNER JOIN RC_ADM_CARTAO_RC_CLI ON
							RC_CLI.CD_EMP = RC_ADM_CARTAO_RC_CLI.CD_EMP
							AND RC_CLI.CD_CLI = RC_ADM_CARTAO_RC_CLI.CD_CLI
							LEFT JOIN RC_CLI_CNT_CTB ON
							RC_CLI.CD_EMP = RC_CLI_CNT_CTB.CD_EMP
							AND RC_CLI.CD_CLI = RC_CLI_CNT_CTB.CD_CLI
							AND TP_CNT_CTB = 0
							WHERE RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO = @RS_CD_ADM_CARTAO
						END
						
						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		@CD_EMP								AS CD_EMP,
											@RS_CD_FILIAL_ANT_2					AS CD_FILIAL_EXPORT,
											@RS_CD_FILIAL_ANT					AS CD_FILIAL,
											8									AS CD_MOVIMENTO,
											'DESPESAS'							AS MOVIMENTO,
											'LANCTO DESPESAS - CREDITO'			AS OPERACAO,
											''									AS OPERACAO_DEBITO,
											@NR_CNT_RED 						AS CD_CNT_RED_CREDITO,
											@NR_CNT 							AS CD_CNT_CREDITO,
											0									AS CD_CNT_RED_DEBITO,
											''									AS CD_CNT_DEBITO,
											@RS_DT_MOV_ANT						AS DT_MOV,
											
											CASE 
												WHEN @RS_CD_ADM_CARTAO > 0 
												THEN 'DESPESAS - '+@NM_ADM+''
												ELSE 'DESPESAS - CAIXA FILIAL: ' + RIGHT('000' + CAST(@RS_CD_FILIAL_ANT AS VARCHAR), 3)
											END AS HIST,
											--'DESPESAS - CAIXA FILIAL: ' + RIGHT('000' + CAST(@RS_CD_FILIAL_ANT AS VARCHAR), 3) AS HIST,
											@TOT_RS_VLR_LIQ						AS VLR_LANC,
											'C'									AS TP_TRANSA,
											0									AS RATEIO_CENTRO_CUSTO,
											''									AS DS_CENTRO_CUSTO,
											''									AS CNT_CENTRO_CUSTO,
											0									AS VLR_CENTRO_CUSTO,
											CASE 
												WHEN @RS_CD_ADM_CARTAO > 0 
												THEN '' 
												ELSE 'FILIAIS' 
											END AS ORIG_CONTA,
											0									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;
										
						SET @TOT_RS_VLR_LIQ = 0
						SET  @NR_CONTROLE =	0
					END																			

			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					
  