CREATE OR REPLACE PROCEDURE ESTORNA_LANC_CONTABIL(
   P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE,
   P_NRLANCCONT IN LANCCONT.NRLANCCONT%TYPE)

AS
   V_DSHISTLANC       LANCCONT.DSHISTLANC%TYPE;
   V_DSHISTLANCEST    LANCCONT.DSHISTLANC%TYPE;
   V_FATOR            NUMBER;
   V_NRLANCCONT       LANCCONT.NRLANCCONT%TYPE;
   V_NRLANCCONTEST    LANCCONT.NRLANCCONT%TYPE;
   V_NRSEQLANCCONT    VARCHAR2(20);
   V_NRSEQLANCCONTEST VARCHAR2(20);
   V_IDGERESTLANCONT  VARCHAR2(01);
BEGIN
   BEGIN
      SELECT NVL(IDGERESTLANCONT,'N')
        INTO V_IDGERESTLANCONT
        FROM CONTOPECTBL
       WHERE CDEMPRESA  = P_CDEMPRESA;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
        V_IDGERESTLANCONT := NULL;
   END;
   V_FATOR := -1;
   IF NVL(V_IDGERESTLANCONT,'N') = 'S' THEN
      FOR C_LANC IN (SELECT CDEMPRESA, NRLANCCONT, NRSEQLANC, CDCONTCTBL, CDHISTPADR,
                            DTLANCCONT, CDDOCUCONT, IDTIPOLANC, DSHISTLANC, VRLANCAMENT,
                            IDLANCCOME, CDCONTCOME, IDLANCSALDO, IDLANCINTE,
                            IDLANCMANI, CDOPERINS, IDSISTEMA
                      FROM LANCCONT
                     WHERE CDEMPRESA  = P_CDEMPRESA
                       AND NRLANCCONT = P_NRLANCCONT
                       AND IDLANCINTE = 'I')LOOP
          IF V_NRLANCCONT IS NULL THEN
             NOVO_CODIGO_NT('LANCCONTABIL' || C_LANC.CDEMPRESA || TO_CHAR(C_LANC.DTLANCCONT,'YYYYMM'),V_NRSEQLANCCONT,1);
             V_NRLANCCONT := TO_CHAR(C_LANC.DTLANCCONT,'YYYYMM')||SUBSTR(V_NRSEQLANCCONT,15,6);
          END IF;

          IF V_NRLANCCONTEST IS NULL THEN
             NOVO_CODIGO_NT('LANCCONTABIL' || C_LANC.CDEMPRESA || TO_CHAR(C_LANC.DTLANCCONT,'YYYYMM'),V_NRSEQLANCCONTEST,1);
             V_NRLANCCONTEST := TO_CHAR(C_LANC.DTLANCCONT,'YYYYMM')||SUBSTR(V_NRSEQLANCCONTEST,15,6);
          END IF;
          /*ESTORNA LANÇAMENTO QUE SERÁ EXCLUÍDO*/
          V_DSHISTLANC    := SUBSTR('ESTORNO REF. LANC:'||' - '||V_NRLANCCONTEST||' - '||C_LANC.DSHISTLANC,1,200);

          V_DSHISTLANCEST := SUBSTR('LANÇAMENTO ESTORNADO PELO LANÇAMENTO - '||V_NRLANCCONT||' - '||C_LANC.DSHISTLANC,1,200);

         INSERT INTO LANCCONT
               (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDCONTCTBL, CDHISTPADR,
                DTLANCCONT, CDDOCUCONT, IDTIPOLANC, DSHISTLANC, VRLANCAMENT,
                IDLANCCOME, CDCONTCOME, IDLANCSALDO, IDLANCINTE, IDLANCMANI,
                CDOPERINS, DTULTATULAN, IDSALDLANCO, IDSALDLANGE, IDSISTEMA)
         VALUES
               (C_LANC.CDEMPRESA, V_NRLANCCONT, C_LANC.NRSEQLANC, C_LANC.CDCONTCTBL, C_LANC.CDHISTPADR,
                C_LANC.DTLANCCONT, C_LANC.CDDOCUCONT, 'C', V_DSHISTLANC, C_LANC.VRLANCAMENT * V_FATOR,
                C_LANC.IDLANCCOME, C_LANC.CDCONTCOME, 'N', C_LANC.IDLANCINTE,'N',
                C_LANC.CDOPERINS, SYSDATE, 'N', 'N', C_LANC.IDSISTEMA);

        INSERT INTO LANCCONT
               (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDCONTCTBL, CDHISTPADR,
                DTLANCCONT, CDDOCUCONT, IDTIPOLANC, DSHISTLANC, VRLANCAMENT,
                IDLANCCOME, CDCONTCOME, IDLANCSALDO, IDLANCINTE, IDLANCMANI,
                CDOPERINS, DTULTATULAN, IDSALDLANCO, IDSALDLANGE, IDSISTEMA)
         VALUES
               (C_LANC.CDEMPRESA, V_NRLANCCONTEST, C_LANC.NRSEQLANC, C_LANC.CDCONTCTBL, C_LANC.CDHISTPADR,
                C_LANC.DTLANCCONT, C_LANC.CDDOCUCONT, 'C', V_DSHISTLANCEST, C_LANC.VRLANCAMENT,
                C_LANC.IDLANCCOME, C_LANC.CDCONTCOME, 'N', C_LANC.IDLANCINTE,'N',
                C_LANC.CDOPERINS, SYSDATE, 'N', 'N', C_LANC.IDSISTEMA);

         FOR C_LANCCUST IN (SELECT CDEMPRESA, NRLANCCONT, NRSEQLANC, CDFILIAL,
                                   CDCENTCUST, VRLANCCUST, DTCOMPCUSTO, CDCONTCTBL
                              FROM LANCCCUSTO
                             WHERE CDEMPRESA  = C_LANC.CDEMPRESA
                               AND NRLANCCONT = C_LANC.NRLANCCONT
                               AND NRSEQLANC  = C_LANC.NRSEQLANC
                               AND CDCONTCTBL = C_LANC.CDCONTCTBL)LOOP
           INSERT INTO LANCCCUSTO
               (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDFILIAL,
                CDCENTCUST, VRLANCCUST, DTCOMPCUSTO)
           VALUES
               (C_LANCCUST.CDEMPRESA, V_NRLANCCONT, C_LANCCUST.NRSEQLANC, C_LANCCUST.CDFILIAL,
                C_LANCCUST.CDCENTCUST, C_LANCCUST.VRLANCCUST * V_FATOR, C_LANCCUST.DTCOMPCUSTO);
           INSERT INTO LANCCCUSTO
               (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDFILIAL,
                CDCENTCUST, VRLANCCUST, DTCOMPCUSTO)
           VALUES
               (C_LANCCUST.CDEMPRESA, V_NRLANCCONTEST, C_LANCCUST.NRSEQLANC, C_LANCCUST.CDFILIAL,
                C_LANCCUST.CDCENTCUST, C_LANCCUST.VRLANCCUST, C_LANCCUST.DTCOMPCUSTO);
         END LOOP;
      END LOOP;
   END IF;
END;