IF OBJECT_ID('P_EXP_CTB_MOV_BC_LANC_DESP') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_BC_LANC_DESP;
    END;
GO
--  exec P_EXP_CTB_MOV_BC_LANC_DESP 1,1,'20170127','20170127',0
CREATE PROCEDURE P_EXP_CTB_MOV_BC_LANC_DESP(@CD_EMP      INT,
                                            @CD_FILIAL   INT,
                                            @DT_INI      DATETIME,
                                            @DT_FIM      DATETIME,
                                            @FLAG_LIMPAR INT      = 0)
WITH ENCRYPTION
AS
    BEGIN TRY
	SET NOCOUNT ON;
        BEGIN
            DECLARE @RS_CD_EMP INT;
            DECLARE @RS_CD_FILIAL INT;
            DECLARE @RS_CD_RAT_DESP INT;
            DECLARE @RS_VLR_DESP MONEY;
            DECLARE @RS_CD_CNT INT;
            DECLARE @RS_CD_DESP_REC INT;
            DECLARE @RS_DS_DESP_REC VARCHAR(100);
            DECLARE @RS_HIST_LANC VARCHAR(150);
            DECLARE @RS_DS_HIST VARCHAR(150);
            DECLARE @RS_NR_CNT_DESP VARCHAR(50);
            DECLARE @RS_CD_CNT_CTB_RED_DESP INT;
            DECLARE @RS_RATEIO_CENTRO_CUSTO INT;
            DECLARE @RS_CD_CTB_CNT_CONTA VARCHAR(50);
            DECLARE @RS_CD_CTB_CNT_RED_CONTA INT;
            DECLARE @RS_DS_CENTRO_CTB VARCHAR(100);
            DECLARE @RS_CNT_CENTRO_CTB VARCHAR(50);
            DECLARE @RS_VLR_RAT_DESP MONEY;
            DECLARE @RS_TP_RECUPERA_GLOSA INT;
            DECLARE @RS_DT_LANCTO DATETIME;
            DECLARE @RET_CD_CTB_CNT VARCHAR(100);
            DECLARE @RET_CD_CTB_CNT_RED INT;
            DECLARE @RET_DS_CNT VARCHAR(100);
            DECLARE @RET_NR_CNT VARCHAR(50);
            DECLARE @RET_TP_CNT INT;
            DECLARE @RET_TITULAR_CNT VARCHAR(200);
            DECLARE @HIST VARCHAR(200);
			DECLARE @RS_CD_BC_LANC INT;
            ------------------------------------------------------------------------------------------------------------------------------
            --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
                     , CD_RAT_DESP
                     , VLR_DESP
                     , CD_CNT
                     , CD_DESP_REC
                     , DS_DESP_REC
                     , HIST_LANC
                     , DS_HIST
                     , NR_CNT_DESP
                     , CD_CNT_CTB_RED_DESP
                     , RATEIO_CENTRO_CUSTO
                     , CD_CTB_CNT_CONTA
                     , CD_CTB_CNT_RED_CONTA
                     , DS_CENTRO_CTB
                     , CNT_CENTRO_CTB
                     , VLR_RAT_DESP
                     , TP_RECUPERA_GLOSA
                     , DT_LANCTO
				 , CD_BC_LANC
                FROM UFD_EXP_CTB_MOV_BC_LANC_DESP(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
                ORDER BY CD_EMP
                       , CD_FILIAL
                       , DT_LANCTO;
									

            --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_CD_RAT_DESP, @RS_VLR_DESP, @RS_CD_CNT, @RS_CD_DESP_REC, 
										   @RS_DS_DESP_REC, @RS_HIST_LANC, @RS_DS_HIST, @RS_NR_CNT_DESP, @RS_CD_CNT_CTB_RED_DESP, 
										   @RS_RATEIO_CENTRO_CUSTO, @RS_CD_CTB_CNT_CONTA, @RS_CD_CTB_CNT_RED_CONTA, @RS_DS_CENTRO_CTB, 
										   @RS_CNT_CENTRO_CTB, @RS_VLR_RAT_DESP, @RS_TP_RECUPERA_GLOSA, @RS_DT_LANCTO,@RS_CD_BC_LANC;				
					 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VLR_DESP > 0
                        BEGIN
                            IF EXISTS
                            (
                                SELECT T.CD_RAT_DESP
                                FROM RC_CLI_CONV_GLOSA_PG_DESP_RAT T
                                WHERE T.CD_RAT_DESP = @RS_CD_RAT_DESP
                            )
                                BEGIN
                                    SELECT @RET_CD_CTB_CNT = CD_CTB_CNT
                                         , @RET_CD_CTB_CNT_RED = CD_CTB_CNT_RED
                                         , @RET_DS_CNT = DS_CNT
                                         , @RET_NR_CNT = NR_CNT
                                         , @RET_TP_CNT = TP_CNT
                                         , @RET_TITULAR_CNT = TITULAR_CNT
                                    FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                                    SET @HIST = 'DESPESA COM GLOSA NAO RECUPERADA DO CONVENIO: '+@RS_HIST_LANC+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
									SET @HIST = REPLACE (@HIST,'	',' ')	
                                    ------------------------------------------------------------------------------------------------------------------
                                    --DESPESAS PERDA/GLOSA - DEBITO - DESPESAS / CONTA BANCO / Ctb_Cnt_Glosas_Convenio
                                    ------------------------------------------------------------------------------------------------------------------
                                   
							 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
                                                , 19 AS CD_MOVIMENTO
                                                , 'PERDAS - GLOSA' AS MOVIMENTO
                                                , 'PERDAS - GLOSA - CREDITO' AS OPERACAO
                                                , 'PERDAS - GLOSA - DEBITO' AS OPERACAO_DEBITO
                                                , 0 AS CD_CNT_RED_CREDITO
                                                , '' AS CD_CNT_CREDITO
                                                , @RS_CD_CNT_CTB_RED_DESP AS CD_CNT_RED_DEBITO
                                                , @RS_NR_CNT_DESP AS CD_CNT_DEBITO
                                                , @RS_DT_LANCTO AS DT_MOV
                                                , @HIST AS HIST
                                                , ROUND(@RS_VLR_RAT_DESP, 2) 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
                                                , ROUND(@RS_VLR_RAT_DESP, 2) AS VLR_CENTRO_CUSTO
                                                , 'FILIAIS' AS ORIG_CONTA
                                                , 23 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_DOC
												, @RS_CD_BC_LANC;
                                END;
                            ELSE
                                BEGIN
                                    SELECT @RET_CD_CTB_CNT = CD_CTB_CNT
                                         , @RET_CD_CTB_CNT_RED = CD_CTB_CNT_RED
                                         , @RET_DS_CNT = DS_CNT
                                         , @RET_NR_CNT = NR_CNT
                                         , @RET_TP_CNT = TP_CNT
                                         , @RET_TITULAR_CNT = TITULAR_CNT
                                    FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                                    SET @HIST = @RS_DS_HIST+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
									SET @HIST = REPLACE (@HIST,'	',' ')							
                                    ------------------------------------------------------------------------------------------------------------------
                                    --MOVIMENTO BANCARIO - DEBITO - DESPESAS / CONTA BANCO
                                    ------------------------------------------------------------------------------------------------------------------
                                    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
                                                , 20 AS CD_MOVIMENTO
                                                , 'MOVIMENTO BANCARIO - DESPESAS' AS MOVIMENTO
                                                , '' AS OPERACAO
                                                , 'MOVIMENTO BANCARIO - DEBITO' AS OPERACAO_DEBITO
                                                , 0 AS CD_CNT_RED_CREDITO
                                                , '' AS CD_CNT_CREDITO
                                                , @RS_CD_CNT_CTB_RED_DESP AS CD_CNT_RED_DEBITO
                                                , @RS_NR_CNT_DESP AS CD_CNT_DEBITO
                                                , @RS_DT_LANCTO AS DT_MOV
                                                , @HIST AS HIST
                                                , ROUND(@RS_VLR_DESP, 2) 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
                                                , ROUND(@RS_VLR_RAT_DESP, 2) AS VLR_CENTRO_CUSTO
                                                , 'DESPESAS' 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_DOC
												, @RS_CD_BC_LANC;																								
							
							
                                    ------------------------------------------------------------------------------------------------------------------
                                    --MOVIMENTO BANCARIO - DEBITO - DESPESAS / CONTA BANCO
                                    ------------------------------------------------------------------------------------------------------------------
                                     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
                                                , 20 AS CD_MOVIMENTO
                                                , 'MOVIMENTO BANCARIO - DESPESAS' AS MOVIMENTO
                                                , 'MOVIMENTO BANCARIO - CREDITO' AS OPERACAO
                                                , '' AS OPERACAO_DEBITO
                                                , @RET_CD_CTB_CNT_RED AS CD_CNT_RED_CREDITO
                                                , @RET_CD_CTB_CNT AS CD_CNT_CREDITO
                                                , 0 AS CD_CNT_RED_DEBITO
                                                , '' AS CD_CNT_DEBITO
                                                , @RS_DT_LANCTO AS DT_MOV
                                                , @HIST AS HIST
                                                , ROUND(@RS_VLR_DESP, 2) AS VLR_LANC
                                                , 'C' AS TP_TRANSA

									   -- trecho alterado

                                                --, @RS_RATEIO_CENTRO_CUSTO AS RATEIO_CENTRO_CUSTO
                                                --, @RS_DS_CENTRO_CTB AS DS_CENTRO_CUSTO
                                                --, @RS_CNT_CENTRO_CTB AS CNT_CENTRO_CUSTO
                                                --, ROUND(@RS_VLR_RAT_DESP, 2) AS VLR_CENTRO_CUSTO

									   , 0 AS RATEIO_CENTRO_CUSTO
                                                , 0 AS DS_CENTRO_CUSTO
                                                , '' AS CNT_CENTRO_CUSTO
                                                , 0 AS VLR_CENTRO_CUSTO

                                                , 'DESPESAS' 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_DOC
												, @RS_CD_BC_LANC;
                                END;
                        END;							

                    ------------------------------------------------------------------------------------------------------------------
                    --Próximo registro				
                    ------------------------------------------------------------------------------------------------------------------
                    --Atribuindo valores do select nas variáveis
                    FETCH NEXT FROM RS_RESULT INTO @RS_CD_EMP, @RS_CD_FILIAL, @RS_CD_RAT_DESP, @RS_VLR_DESP, @RS_CD_CNT, @RS_CD_DESP_REC, 
												   @RS_DS_DESP_REC, @RS_HIST_LANC, @RS_DS_HIST, @RS_NR_CNT_DESP, @RS_CD_CNT_CTB_RED_DESP, 
												   @RS_RATEIO_CENTRO_CUSTO, @RS_CD_CTB_CNT_CONTA, @RS_CD_CTB_CNT_RED_CONTA, @RS_DS_CENTRO_CTB, 
												   @RS_CNT_CENTRO_CTB, @RS_VLR_RAT_DESP, @RS_TP_RECUPERA_GLOSA, @RS_DT_LANCTO,@RS_CD_BC_LANC;
                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;