IF OBJECT_ID('P_EXP_CTB_MOV_BC_LANC_TRANSF') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_BC_LANC_TRANSF;
    END;
GO
--exec P_EXP_CTB_MOV_BC_LANC_TRANSF 1,1,'20170127','20170127',0
CREATE PROCEDURE P_EXP_CTB_MOV_BC_LANC_TRANSF(@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_DT_LANCTO DATETIME;
            DECLARE @RS_CD_BC_LANC INT;
            DECLARE @RS_VALORORIGEM MONEY;
            DECLARE @RS_VALORDESTINO MONEY;
            DECLARE @RS_CONTAORIGEM INT;
            DECLARE @RS_CONTADESTINO INT;
            DECLARE @RS_CONTAORIGEMAUX INT;
            DECLARE @RS_CONTADESTINOAUX INT;
			DECLARE @RS_HIST_LANC VARCHAR(200);
            DECLARE @RET_CD_CTB_CNT_ORIG VARCHAR(100);
            DECLARE @RET_CD_CTB_CNT_RED_ORIG INT;
            DECLARE @RET_DS_CNT_ORIG VARCHAR(100);
            DECLARE @RET_NR_CNT_ORIG VARCHAR(50);
            DECLARE @RET_TP_CNT_ORIG INT;
            DECLARE @RET_TITULAR_CNT_ORIG VARCHAR(200);
            DECLARE @RET_CD_CTB_CNT_DEST VARCHAR(100);
            DECLARE @RET_CD_CTB_CNT_RED_DEST INT;
            DECLARE @RET_DS_CNT_DEST VARCHAR(100);
            DECLARE @RET_NR_CNT_DEST VARCHAR(50);
            DECLARE @RET_TP_CNT_DEST INT;
            DECLARE @RET_TITULAR_CNT_DEST VARCHAR(200);
            DECLARE @HIST VARCHAR(200);
            DECLARE @DESC_CONTA_DEST VARCHAR(100);
            DECLARE @DESC_CONTA_ORIG VARCHAR(100)
			DECLARE @CD_BC 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
                     , DT_LANCTO
                     , CD_BC_LANC
                     , VALORORIGEM
                     , VALORDESTINO
                     , CONTAORIGEM
                     , CONTADESTINO
                     , CONTAORIGEMAUX
                     , CONTADESTINOAUX
					 , HIST_LANC
                FROM UFD_EXP_CTB_MOV_BC_LANC_TRANSF(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
				--- FROM UFD_EXP_CTB_MOV_BC_LANC_TRANSF(1, 1, '20240601', '20240603') WHERE VALORORIGEM = 700
                ORDER BY DT_LANCTO
                       , CONTAORIGEM
                       , CONTADESTINO;
									
            --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_LANCTO, @RS_CD_BC_LANC, @RS_VALORORIGEM, @RS_VALORDESTINO, @RS_CONTAORIGEM, @RS_CONTADESTINO, @RS_CONTAORIGEMAUX, @RS_CONTADESTINOAUX,@RS_HIST_LANC;				
					 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VALORORIGEM > 0
                        BEGIN
                            SELECT @RET_CD_CTB_CNT_ORIG = CD_CTB_CNT
                                 , @RET_CD_CTB_CNT_RED_ORIG = CD_CTB_CNT_RED
                                 , @RET_DS_CNT_ORIG = DS_CNT
                                 , @RET_NR_CNT_ORIG = NR_CNT
                                 , @RET_TP_CNT_ORIG = TP_CNT
                                 , @RET_TITULAR_CNT_ORIG = TITULAR_CNT
								 , @CD_BC = CD_BC
                            FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CONTAORIGEM);
                            SELECT @RET_CD_CTB_CNT_DEST = CD_CTB_CNT
                                 , @RET_CD_CTB_CNT_RED_DEST = CD_CTB_CNT_RED
                                 , @RET_DS_CNT_DEST = DS_CNT
                                 , @RET_NR_CNT_DEST = NR_CNT
                                 , @RET_TP_CNT_DEST = TP_CNT
                                 , @RET_TITULAR_CNT_DEST = TITULAR_CNT
                            FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CONTADESTINO);
                            IF UPPER(@RET_DS_CNT_ORIG) = UPPER(@RET_NR_CNT_ORIG)
                                BEGIN
                                    SET @DESC_CONTA_ORIG = @RET_DS_CNT_ORIG;
                                END;
                            ELSE
                                BEGIN
                                    SET @DESC_CONTA_ORIG = @RET_DS_CNT_ORIG+' - '+@RET_NR_CNT_ORIG;
                                END;
                            IF UPPER(@RET_DS_CNT_DEST) = UPPER(@RET_NR_CNT_DEST)
                                BEGIN
                                    SET @DESC_CONTA_DEST = @RET_DS_CNT_DEST;
                                END;
                            ELSE
                                BEGIN
                                    SET @DESC_CONTA_DEST = @RET_DS_CNT_DEST+' - '+@RET_NR_CNT_DEST;
                                END;
                            IF @RET_TP_CNT_ORIG = 4
                                BEGIN
                                    SET @HIST = 'RECBTO  REF. ADIANT. DA COLIGADA: '+@RET_TITULAR_CNT_ORIG+' - CONTA: '+@DESC_CONTA_ORIG;
                                END;
                            ELSE
                                BEGIN
                                    IF @RET_TP_CNT_DEST = 4
                                        BEGIN
                                            SET @HIST = 'PAGO ADIANTAMENTO CONF. RECIBO A: '+@RET_TITULAR_CNT_DEST+' - CONTA: '+@DESC_CONTA_DEST;
                                        END;
                                    ELSE
                                    IF @RET_TP_CNT_DEST = 7
                                        BEGIN
                                            SET @HIST = 'PAGO MUTUO CONF. RECIBO A: '+@RET_TITULAR_CNT_DEST+' - CONTA: '+@DESC_CONTA_DEST;
                                        END;
                                    ELSE
                                        BEGIN
                                           -- SET @HIST = 'TRANSFERENCIA DA CONTA: '+@DESC_CONTA_ORIG+' PARA: '+@DESC_CONTA_DEST;
										  SET @HIST = @RS_HIST_LANC
                                        END;;
                                END;
								
								SET @HIST = REPLACE (@HIST,'	',' ')

                            ------------------------------------------------------------------------------------------------------------------
                            --TRANSFERENCIAS/MUTUOS/ADIANTAMENTOS - DEBITO -  CONTA BANCO DESTINO
                            ------------------------------------------------------------------------------------------------------------------
                            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, CD_BC)

                                   SELECT @RS_CD_EMP AS CD_EMP
                                        , @CD_FILIAL AS CD_FILIAL_EXPORT
                                        , @RS_CD_FILIAL AS CD_FILIAL
                                        , 28 AS CD_MOVIMENTO
                                        , 'TRANSFERENCIAS BANCARIAS/MUTUOS/ADIANTAMENTOS' AS MOVIMENTO
                                        , '' AS OPERACAO
                                        , 'TRANSFERENCIAS/MUTUOS/ADIANTAMENTOS - DEBITO' AS OPERACAO_DEBITO
                                        , 0 AS CD_CNT_RED_CREDITO
                                        , '' AS CD_CNT_CREDITO
                                        , @RET_CD_CTB_CNT_RED_DEST AS CD_CNT_RED_DEBITO
                                        , @RET_CD_CTB_CNT_DEST AS CD_CNT_DEBITO
                                        , @RS_DT_LANCTO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VALORDESTINO AS VLR_LANC
                                        , 'D' AS TP_TRANSA
                                        , 0 AS RATEIO_CENTRO_CUSTO
                                        , '' AS DS_CENTRO_CUSTO
                                        , 0 AS CNT_CENTRO_CUSTO
                                        , 0 AS VLR_CENTRO_CUSTO
                                        , 'CNT BANC' AS ORIG_CONTA
                                        , @RS_CONTADESTINO AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										, '' AS NR_DOC
										, @RS_CD_BC_LANC AS CD_BC_LANC
										, @CD_BC AS CD_BC;	
                            ------------------------------------------------------------------------------------------------------------------
                            --TRANSFERENCIAS/MUTUOS/ADIANTAMENTOS - DEBITO -  CONTA BANCO ORIGEM
                            ------------------------------------------------------------------------------------------------------------------
                            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
                                        , 28 AS CD_MOVIMENTO
                                        , 'TRANSFERENCIAS BANCARIAS/MUTUOS/ADIANTAMENTOS' AS MOVIMENTO
                                        , 'TRANSFERENCIAS/MUTUOS/ADIANTAMENTOS - CREDITO' AS OPERACAO
                                        , '' AS OPERACAO_DEBITO
                                        , @RET_CD_CTB_CNT_RED_ORIG AS CD_CNT_RED_CREDITO
                                        , @RET_CD_CTB_CNT_ORIG AS CD_CNT_CREDITO
                                        , 0 AS CD_CNT_RED_DEBITO
                                        , '' AS CD_CNT_DEBITO
                                        , @RS_DT_LANCTO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VALORORIGEM 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
                                        , 'CNT BANC' AS ORIG_CONTA
                                        , @RS_CONTAORIGEM AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										, '' AS NR_DOC
										, @RS_CD_BC_LANC AS CD_BC_LANC;	
                        END;								

                    ------------------------------------------------------------------------------------------------------------------
                    --Próximo registro				
                    ------------------------------------------------------------------------------------------------------------------
                    --Atribuindo valores do select nas variáveis
                    FETCH NEXT FROM RS_RESULT INTO @RS_CD_EMP, @RS_CD_FILIAL, @RS_DT_LANCTO, @RS_CD_BC_LANC, @RS_VALORORIGEM, @RS_VALORDESTINO, @RS_CONTAORIGEM, @RS_CONTADESTINO, @RS_CONTAORIGEMAUX, @RS_CONTADESTINOAUX,@RS_HIST_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;