CREATE OR REPLACE PROCEDURE CTB_BCO_TRANSF(
   P_NRTRANBANC IN ITTRANSFBANC.NRTRANBANC%TYPE,
   P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE,
   P_DATA       IN TRANSFBANC.DTTRANBANC%TYPE,
   P_CDFILIPROC IN FILIAL.CDFILIAL%TYPE,
   P_NRLANCCTB OUT LANCCONT.NRLANCCONT%TYPE)
AS
   V_DSHISTLANC LANCCONT.DSHISTLANC%TYPE;
   V_NRLANCTEMP NOVOCODIGO.NRSEQUENCIAL%TYPE;
   V_DTLANCCTBTEMP TRANSFBANC.DTTRANBANC%TYPE;
   V_PARTIDA    INTEGER;
   V_CONTA      LANCCONT.CDCONTCTBL%TYPE;
   V_CDHISTPADR HISTPADR.CDHISTPADR%TYPE;
   V_CDHISTPADRC HISTPADR.CDHISTPADR%TYPE;
   V_COUNTPART  INTEGER;
   V_COUNT      INTEGER;
   V_CDCENTCUST CENTCUST.CDCENTCUST%TYPE;
   V_IDCONTCCUS VARCHAR2(01);
   V_IDCCUS VARCHAR2(01);
   V_IDCOME VARCHAR2(01);
   V_CDFILIAL VARCHAR2(04);
BEGIN
   BEGIN
      SELECT NRLCTBTRAN
        INTO V_NRLANCTEMP
        FROM TRANSFBANC
       WHERE CDEMPRESA  = P_CDEMPRESA
         AND NRTRANBANC = P_NRTRANBANC;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRLANCTEMP := NULL;
   END;
   V_DTLANCCTBTEMP := P_DATA;

   IF V_NRLANCTEMP IS NOT NULL THEN
      /* DELETE RESPONSAVEL POR DELETAR OS LANCAMENTOS NA LANCCONT COM O MESMO SEQUENCIAL DO LANCAMENTO NO BCO, POSTERIORMENTE IRA RECONTABILIZAR O MESMO */
      DELETE
        FROM LANCCCUSTO
       WHERE NRLANCCONT = V_NRLANCTEMP
         AND CDEMPRESA  = P_CDEMPRESA;
      DELETE
        FROM LANCCONT
       WHERE NRLANCCONT = V_NRLANCTEMP
         AND CDEMPRESA  = P_CDEMPRESA;
   END IF;

   BEGIN
      SELECT COUNT(*)
        INTO V_COUNT
        FROM PARAMCTBL
       WHERE V_DTLANCCTBTEMP >= DTINTCTBBCO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_COUNT := 0;
   END;

   IF V_COUNT <> 0 THEN
      DELETE
        FROM ERROCTBTEMP
       WHERE CDEMPRESA     = P_CDEMPRESA
         AND NRLANCCTBTEMP = V_NRLANCTEMP;

      DELETE
        FROM LANCCTBTEMP
       WHERE CDEMPRESA     = P_CDEMPRESA
         AND NRLANCCTBTEMP = V_NRLANCTEMP;

      DELETE
        FROM RATECTBTEMP
       WHERE CDEMPRESA     = P_CDEMPRESA
         AND NRLANCCTBTEMP = V_NRLANCTEMP;

      SELECT CDHISTPADR
        INTO V_CDHISTPADR
        FROM HISTINTEGRA
       WHERE CDOPERACAO = 'TRANSF';

      V_DSHISTLANC := CTB_HIST_BCO('', P_NRTRANBANC, V_CDHISTPADR, P_CDEMPRESA, 'T');

      IF V_NRLANCTEMP IS NULL THEN
      /* PEGA UM NOVO NUMERO DE LANCAMENTO CONTABIL, CASO O LANCAMENTO NO BCO SEJA NOVO */
         NOVO_CODIGO_NT('LANCCONTABIL' || P_CDEMPRESA || TO_CHAR(V_DTLANCCTBTEMP,'YYYYMM'), V_NRLANCTEMP);
         V_NRLANCTEMP := TO_CHAR(V_DTLANCCTBTEMP,'YYYYMM')||SUBSTR(V_NRLANCTEMP, 15, 6);
      END IF;

      V_PARTIDA := 1;
      V_IDCONTCCUS := 'N';
      BEGIN
         SELECT IDCONTGERE
           INTO V_IDCONTCCUS
           FROM CONTOPECTBL
          WHERE CDEMPRESA  = P_CDEMPRESA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDCONTCCUS := NULL;
      END;

      FOR ITTRANSF IN
         (SELECT I.CDEMPRESA, I.NRTRANBANC,  I.CDREDUCONT, I.NRMOVBANC, I.IDMOVITBANC,
                 I.VRMOVITBANC, I.NRCHEQUE,  C.CDBANCO, C.CDAGENCIA, C.CDCONTCORR,
                 C.DSCONTCORR, B.CDPICTCONT, B.CDBANCCNAB, B.CDPICTAGEN
            FROM ITTRANSFBANC I, CONTCORR C,  BANCO B
           WHERE I.CDEMPRESA  = P_CDEMPRESA    AND
                I.NRTRANBANC = P_NRTRANBANC  AND
                I.CDREDUCONT = C.CDREDUCONT  AND
                C.CDBANCO    = B.CDBANCO) LOOP

         /*CASO A TRANSFERENCIA ENVOLVA OPERACAO COM CHEQUE BUSCA O HISTORICO DE CHEQUE*/
         IF ITTRANSF.NRCHEQUE IS NOT NULL THEN
            BEGIN
               SELECT CDHISTPADR
                 INTO V_CDHISTPADRC
                 FROM HISTINTEGRA
                WHERE CDOPERACAO = 'TRANSC';
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                 V_CDHISTPADRC := NULL;
            END;
         END IF;
         V_DSHISTLANC := CTB_HIST_BCO('', P_NRTRANBANC, NVL(V_CDHISTPADRC,V_CDHISTPADR), P_CDEMPRESA, 'T');
         SELECT C.CDCONTCTBL
           INTO V_CONTA
           FROM CONTCORR C, BANCO B
          WHERE C.CDBANCO  = B.CDBANCO           AND
                CDREDUCONT = ITTRANSF.CDREDUCONT AND
                CDEMPRESA  = P_CDEMPRESA;

         /* VERIFICA SE JA EXISTE LANCAMENTO PARA A CONTA CONTABIL, SE EXISTIR, O SISTEMA UTILIZARA OU NAO O PARAMETRO
            DE ACUMULAR VALOR NOS LANCAMENTOS DE INTEGRACAO, DA TELA PARAMETRO CONTABIL */
         SELECT COUNT(*)
           INTO V_COUNTPART
           FROM LANCCTBTEMP
          WHERE CDEMPRESA = P_CDEMPRESA
            AND NRLANCCTBTEMP = V_NRLANCTEMP
            AND CDCONTCTBL = V_CONTA
            AND CDLANCCTBCOME = ITTRANSF.CDREDUCONT;

         BUSCA_TPCOMERCIAL(V_CONTA, V_IDCOME, V_IDCCUS);
         /*PARTIDA DE DEBTO*/
         IF ITTRANSF.IDMOVITBANC = 'C' THEN
            IF V_COUNTPART = 0 THEN
               INSERT INTO LANCCTBTEMP
                  (CDEMPRESA,  NRSEQCTBTEMP, CDCONTCTBL, CDHISTPADR, DSHISTCTB, DTLANCCTBTEMP,
                   VRLANCCTBTEMP, IDLANCCTBCOME, CDLANCCTBCOME, CDDOCUTEMP, NRLANCCTBTEMP, IDSISTEMA)
               VALUES
                  (P_CDEMPRESA, LPAD(TO_CHAR(V_PARTIDA), 5, '0'), V_CONTA, NVL(V_CDHISTPADRC,V_CDHISTPADR), V_DSHISTLANC, V_DTLANCCTBTEMP,
                   ITTRANSF.VRMOVITBANC * -1, 'B', ITTRANSF.CDREDUCONT, P_NRTRANBANC, V_NRLANCTEMP, 'BCO');
                IF V_IDCONTCCUS = 'S' THEN
                   IF V_IDCCUS <> 'O' THEN
                      V_CDFILIAL := NULL;
                      BEGIN
                         SELECT CDFILIAL
                           INTO V_CDFILIAL
                           FROM CONTCORR
                          WHERE CDREDUCONT = ITTRANSF.CDREDUCONT;
                      EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                            V_CDFILIAL := NULL;
                      END;
                      IF V_CDFILIAL IS NULL THEN
                         V_CDFILIAL := P_CDFILIPROC;
                      END IF;
                      V_CDCENTCUST := BUSCA_CCUSTO(V_CDFILIAL, P_CDEMPRESA);
                      INSERT INTO RATECTBTEMP
                         (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDFILIAL,
                          CDCENTCUST, DTCOMPCTBTEMP, VRRATECTBTEMP, NRSEQRATTEMP)
                      VALUES
                         (P_CDEMPRESA, V_NRLANCTEMP, LPAD(TO_CHAR(V_PARTIDA), 5, '0'), V_CDFILIAL,
                          V_CDCENTCUST, V_DTLANCCTBTEMP, ITTRANSF.VRMOVITBANC * -1, '00001');
                   END IF;
                END IF;
            ELSE
               UPDATE LANCCTBTEMP
                  SET VRLANCCTBTEMP = VRLANCCTBTEMP + (ITTRANSF.VRMOVITBANC * -1)
                WHERE CDEMPRESA = P_CDEMPRESA
                  AND NRLANCCTBTEMP = V_NRLANCTEMP
                  AND CDCONTCTBL = V_CONTA;

                IF V_IDCONTCCUS = 'S' THEN
                   IF V_IDCCUS <> 'O' THEN
                      UPDATE RATECTBTEMP
                         SET VRRATECTBTEMP = VRRATECTBTEMP + (ITTRANSF.VRMOVITBANC * -1)
                       WHERE CDEMPRESA = P_CDEMPRESA
                         AND NRLANCCTBTEMP = V_NRLANCTEMP
                         AND NRSEQCTBTEMP = LPAD(TO_CHAR(V_PARTIDA), 5, '0')
                         AND CDFILIAL = P_CDFILIPROC
                         AND CDCENTCUST = V_CDCENTCUST;
                   END IF;
                END IF;
            END IF;
         ELSE
            IF V_COUNTPART = 0 THEN
               INSERT INTO LANCCTBTEMP
                  (CDEMPRESA,  NRSEQCTBTEMP, CDCONTCTBL, CDHISTPADR, DSHISTCTB, DTLANCCTBTEMP,
                   VRLANCCTBTEMP, IDLANCCTBCOME, CDLANCCTBCOME, CDDOCUTEMP, NRLANCCTBTEMP, IDSISTEMA)
               VALUES
                  (P_CDEMPRESA, LPAD(TO_CHAR(V_PARTIDA), 5, '0'), V_CONTA, NVL(V_CDHISTPADRC,V_CDHISTPADR), V_DSHISTLANC, V_DTLANCCTBTEMP,
                   ITTRANSF.VRMOVITBANC, 'B', ITTRANSF.CDREDUCONT, P_NRTRANBANC, V_NRLANCTEMP, 'BCO');
               IF V_IDCONTCCUS = 'S' THEN
                  IF V_IDCCUS <> 'O' THEN
                     V_CDFILIAL := NULL;
                     BEGIN
                        SELECT CDFILIAL
                          INTO V_CDFILIAL
                          FROM CONTCORR
                         WHERE CDREDUCONT = ITTRANSF.CDREDUCONT;
                     EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                           V_CDFILIAL := NULL;
                     END;
                     IF V_CDFILIAL IS NULL THEN
                        V_CDFILIAL := P_CDFILIPROC;
                     END IF;
                     V_CDCENTCUST := BUSCA_CCUSTO(V_CDFILIAL, P_CDEMPRESA);
                     INSERT INTO RATECTBTEMP
                        (CDEMPRESA, NRLANCCTBTEMP, NRSEQCTBTEMP, CDFILIAL,
                         CDCENTCUST, DTCOMPCTBTEMP, VRRATECTBTEMP, NRSEQRATTEMP)
                     VALUES
                        (P_CDEMPRESA, V_NRLANCTEMP, LPAD(TO_CHAR(V_PARTIDA), 5, '0'), V_CDFILIAL,
                         V_CDCENTCUST, V_DTLANCCTBTEMP, ITTRANSF.VRMOVITBANC, '00001');
                  END IF;
               END IF;
            ELSE
               UPDATE LANCCTBTEMP
                  SET VRLANCCTBTEMP = VRLANCCTBTEMP + ITTRANSF.VRMOVITBANC
                WHERE CDEMPRESA = P_CDEMPRESA
                  AND NRLANCCTBTEMP = V_NRLANCTEMP
                  AND CDCONTCTBL = V_CONTA;

               IF V_IDCONTCCUS = 'S' THEN
                  IF V_IDCCUS <> 'O' THEN
                     UPDATE RATECTBTEMP
                        SET VRRATECTBTEMP = VRRATECTBTEMP + ITTRANSF.VRMOVITBANC
                      WHERE CDEMPRESA = P_CDEMPRESA
                        AND NRLANCCTBTEMP = V_NRLANCTEMP
                        AND NRSEQCTBTEMP = LPAD(TO_CHAR(V_PARTIDA), 5, '0')
                        AND CDFILIAL = P_CDFILIPROC
                        AND CDCENTCUST = V_CDCENTCUST;
                  END IF;
               END IF;
            END IF;
         END IF;
         V_PARTIDA := V_PARTIDA + 1;
      END LOOP;

      UPDATE TRANSFBANC
         SET NRLCTBTRAN = V_NRLANCTEMP
       WHERE CDEMPRESA  = P_CDEMPRESA
         AND NRTRANBANC = P_NRTRANBANC;

      /*VERIFICACAO DE ERROS NAS PARTIDAS E NO LANCAMENTO CONTABIL*/
      VERIFICA_ERRO_CTB(V_NRLANCTEMP, P_CDEMPRESA);

      /* GRAVA O LANCAMENTO TEMPORARIO SE NAO CONTER ERRO NA LANCCONT E LANCCUSTO, APOS GRAVAR IRA APAGAR O LANCAMENTO TEMPORARIO */
      GRAVA_LANC_CTB(V_NRLANCTEMP, P_CDEMPRESA);

      P_NRLANCCTB := V_NRLANCTEMP;
   END IF;
END;
/