create or replace PROCEDURE GERA_DREGERENCIAL
   (P_CDEMPRESA   IN TMPCONTABGER.CDEMPRESATMP%TYPE,
    P_NRLISTA     IN LISTA.NRLISTA%TYPE,
    P_CDTIPODEMO  IN TMPCONTABGER.CDTIPODEMOTMP%TYPE,
    P_DTINICIAL   IN DATE,
    P_DTFINAL     IN DATE,
    P_NRCONTADOR  IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_VRTXADMCENT IN FLOAT DEFAULT 0,
    P_NRCONTABGER OUT VARCHAR) AS
   V_VRCONTDATA   INTEGER;
   V_INSERT_TEXT  VARCHAR2(3500);
   C_INSERT       INTEGER;
   V_RESULT       INTEGER;
   V_COUNT        INTEGER;
   V_CDCHAVE      VARCHAR(04);
   V_VRRESULTADO  FLOAT;
   V_SALDITVAR    FLOAT;
   V_VRDIVISAO1   FLOAT;
   V_VRDIVISAO2   FLOAT;
   V_VRPARCELA1   FLOAT;
   V_VRPARCELA2   FLOAT;
   V_VRTXADMCENT  FLOAT;
   V_NRCONTABGER  VARCHAR2(20);
BEGIN
   V_VRCONTDATA := 0;
   IF NOT P_NRCONTADOR IS NULL THEN
      V_NRCONTABGER := P_NRCONTADOR;
      DELETE
        FROM TMPCONTABGER
       WHERE NRCONTABGER = P_NRCONTADOR;
   ELSE
      NOVO_CODIGO('GERA_DREGERENCIAL', V_NRCONTABGER);
      V_NRCONTABGER := SUBSTR(V_NRCONTABGER, 11, 10);
   END IF;
    FOR F IN (
      SELECT L.CDLISTA AS CDFILIAL, F.CDAREAGERE
        FROM LISTA L, FILIAL F
       WHERE L.NRLISTA = P_NRLISTA
         AND L.CDLISTA = F.CDFILIAL
       ORDER BY CDLISTA
   ) LOOP
      V_VRCONTDATA := 0;
      FOR V IN (
         SELECT DISTINCT C.CDCONTCTBL, I.CDITEMSTRU
           FROM ITEMAPUCTB I, CONTCTBL C
          WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO ||'%'
            AND I.IDTIPOORIG = 'S'
            AND I.CDTIPODEMO = P_CDTIPODEMO
            AND C.IDTIPOCONT = 'A'
          ORDER BY C.CDCONTCTBL
      ) LOOP
         INSERT INTO TMPCONTABGER
            (NRCONTABGER, CDEMPRESATMP, CDFILIALTMP, CDTIPODEMOTMP,
             CDITEMSTRUTMP, CDCONTCTBLTMP,
             DTREFETMP01, DTREFETMP02, DTREFETMP03, DTREFETMP04, DTREFETMP05, DTREFETMP06,
             DTREFETMP07, DTREFETMP08, DTREFETMP09, DTREFETMP10, DTREFETMP11, DTREFETMP12,
             VRREFETMP01, VRREFETMP02, VRREFETMP03, VRREFETMP04, VRREFETMP05, VRREFETMP06,
             VRREFETMP07, VRREFETMP08, VRREFETMP09, VRREFETMP10, VRREFETMP11, VRREFETMP12)
         VALUES
            (V_NRCONTABGER, P_CDEMPRESA, F.CDFILIAL, P_CDTIPODEMO,
             V.CDITEMSTRU, V.CDCONTCTBL,
             P_DTINICIAL, ADD_MONTHS(P_DTINICIAL, 1), ADD_MONTHS(P_DTINICIAL, 2), ADD_MONTHS(P_DTINICIAL, 3),
             ADD_MONTHS(P_DTINICIAL, 4), ADD_MONTHS(P_DTINICIAL, 5), ADD_MONTHS(P_DTINICIAL, 6),
             ADD_MONTHS(P_DTINICIAL, 7), ADD_MONTHS(P_DTINICIAL, 8), ADD_MONTHS(P_DTINICIAL, 9),
             ADD_MONTHS(P_DTINICIAL, 10), ADD_MONTHS(P_DTINICIAL, 11),
             0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
      END LOOP;
      FOR I IN (
         SELECT DISTINCT I.CDITEMSTRU
           FROM ITEMDEMOF D, ITEMAPUCTB I,
                (SELECT DISTINCT T.CDTIPODEMOTMP, T.CDITEMSTRUTMP
                   FROM TMPCONTABGER T
                  WHERE T.CDEMPRESATMP = P_CDEMPRESA
                    AND T.NRCONTABGER = V_NRCONTABGER
                    AND T.CDFILIALTMP = F.CDFILIAL) T
          WHERE SUBSTR(I.CDORIGDEMO,1,2) = T.CDTIPODEMOTMP(+)
            AND SUBSTR(I.CDORIGDEMO,3,4) = T.CDITEMSTRUTMP(+)
            AND I.CDTIPODEMO = D.CDTIPODEMO
            AND I.CDITEMSTRU = D.CDITEMSTRU
            AND I.IDTIPOORIG = 'V'
          ORDER BY I.CDITEMSTRU
      ) LOOP
         INSERT INTO TMPCONTABGER
            (NRCONTABGER, CDEMPRESATMP, CDFILIALTMP, CDTIPODEMOTMP,
             CDITEMSTRUTMP, CDCONTCTBLTMP,
             DTREFETMP01, DTREFETMP02, DTREFETMP03, DTREFETMP04, DTREFETMP05, DTREFETMP06,
             DTREFETMP07, DTREFETMP08, DTREFETMP09, DTREFETMP10, DTREFETMP11, DTREFETMP12,
             VRREFETMP01, VRREFETMP02, VRREFETMP03, VRREFETMP04, VRREFETMP05, VRREFETMP06,
             VRREFETMP07, VRREFETMP08, VRREFETMP09, VRREFETMP10, VRREFETMP11, VRREFETMP12)
         VALUES
            (V_NRCONTABGER, P_CDEMPRESA, F.CDFILIAL, P_CDTIPODEMO,
             I.CDITEMSTRU, '0000000000',
             P_DTINICIAL, ADD_MONTHS(P_DTINICIAL, 1), ADD_MONTHS(P_DTINICIAL, 2), ADD_MONTHS(P_DTINICIAL, 3),
             ADD_MONTHS(P_DTINICIAL, 4), ADD_MONTHS(P_DTINICIAL, 5), ADD_MONTHS(P_DTINICIAL, 6),
             ADD_MONTHS(P_DTINICIAL, 7), ADD_MONTHS(P_DTINICIAL, 8), ADD_MONTHS(P_DTINICIAL, 9),
             ADD_MONTHS(P_DTINICIAL, 10), ADD_MONTHS(P_DTINICIAL, 11),
             0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
      END LOOP;
      FOR D IN (
         SELECT DISTINCT DTMES
           FROM DATA
          WHERE DTMES BETWEEN P_DTINICIAL AND P_DTFINAL
          ORDER BY DTMES
      ) LOOP
         V_VRCONTDATA := V_VRCONTDATA + 1;
         FOR V IN (
            SELECT LC.CDCONTCTBL, I.CDITEMSTRU, SUM(VRLANCCUST) AS VRSALDCONT
              FROM LANCCONT L, LANCCCUSTO LC, ITEMAPUCTB I
             WHERE L.CDEMPRESA = P_CDEMPRESA
               AND L.CDEMPRESA = LC.CDEMPRESA
               AND L.NRLANCCONT = LC.NRLANCCONT
               AND L.NRSEQLANC = LC.NRSEQLANC
               AND LC.CDFILIAL = F.CDFILIAL
               AND LC.DTCOMPCUSTO BETWEEN D.DTMES AND LAST_DAY(D.DTMES)
               AND LC.CDCONTCTBL LIKE I.CDORIGDEMO ||'%'
               AND I.IDTIPOORIG = 'S'
               AND I.CDTIPODEMO = P_CDTIPODEMO
             GROUP BY LC.CDCONTCTBL, I.CDITEMSTRU
             ORDER BY LC.CDCONTCTBL
         ) LOOP
               V_INSERT_TEXT :=                  'UPDATE TMPCONTABGER';
               V_INSERT_TEXT := V_INSERT_TEXT || '   SET DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||' = :P_DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||',';
               V_INSERT_TEXT := V_INSERT_TEXT || '       VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||' = :P_VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0');
               V_INSERT_TEXT := V_INSERT_TEXT || ' WHERE NRCONTABGER = :P_NRCONTABGER';
               V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDEMPRESATMP = :P_CDEMPRESATMP';
               V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDFILIALTMP = :P_CDFILIALTMP';
               V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDTIPODEMOTMP = :P_CDTIPODEMOTMP';
               V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDITEMSTRUTMP = :P_CDITEMSTRUTMP';
               V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDCONTCTBLTMP = :P_CDCONTCTBLTMP';
               C_INSERT := DBMS_SQL.OPEN_CURSOR;
               DBMS_SQL.PARSE(C_INSERT, V_INSERT_TEXT, DBMS_SQL.V7);
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0') , D.DTMES);
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0') , V.VRSALDCONT);
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_NRCONTABGER' , V_NRCONTABGER );
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDEMPRESATMP' , P_CDEMPRESA );
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDFILIALTMP' , F.CDFILIAL );
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDTIPODEMOTMP' , P_CDTIPODEMO );
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDITEMSTRUTMP' , V.CDITEMSTRU );
               DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDCONTCTBLTMP' , V.CDCONTCTBL );
               V_RESULT := DBMS_SQL.EXECUTE(C_INSERT);
               DBMS_SQL.CLOSE_CURSOR(C_INSERT);
         END LOOP;
         V_CDCHAVE := '0';
         V_VRRESULTADO := 0;
         FOR I IN (
            SELECT I.CDITEMSTRU, I.CDORIGDEMO, D.IDOPERACAO, I.CDSEGUAPUR, D.NMITEMSTRU,
                   NVL(T.VRREFETMP,0) AS VRREFETMP
              FROM ITEMDEMOF D, ITEMAPUCTB I,
                   (SELECT T.CDTIPODEMOTMP, T.CDITEMSTRUTMP,
                           SUM(DECODE(V_VRCONTDATA,
                                      1, T.VRREFETMP01,
                                      2, T.VRREFETMP02,
                                      3, T.VRREFETMP03,
                                      4, T.VRREFETMP04,
                                      5, T.VRREFETMP05,
                                      6, T.VRREFETMP06,
                                      7, T.VRREFETMP07,
                                      8, T.VRREFETMP08,
                                      9, T.VRREFETMP09,
                                     10, T.VRREFETMP10,
                                     11, T.VRREFETMP11,
                                     12, T.VRREFETMP12)) AS VRREFETMP
                      FROM TMPCONTABGER T
                     WHERE T.CDEMPRESATMP = P_CDEMPRESA
                       AND T.NRCONTABGER = V_NRCONTABGER
                       AND T.CDFILIALTMP = F.CDFILIAL
                       AND DECODE(V_VRCONTDATA,
                                  1, T.DTREFETMP01,
                                  2, T.DTREFETMP02,
                                  3, T.DTREFETMP03,
                                  4, T.DTREFETMP04,
                                  5, T.DTREFETMP05,
                                  6, T.DTREFETMP06,
                                  7, T.DTREFETMP07,
                                  8, T.DTREFETMP08,
                                  9, T.DTREFETMP09,
                                 10, T.DTREFETMP10,
                                 11, T.DTREFETMP11,
                                 12, T.DTREFETMP12) = D.DTMES
                     GROUP BY T.CDTIPODEMOTMP, T.CDITEMSTRUTMP) T
             WHERE SUBSTR(I.CDORIGDEMO,1,2) = T.CDTIPODEMOTMP(+)
               AND SUBSTR(I.CDORIGDEMO,3,4) = T.CDITEMSTRUTMP(+)
               AND I.CDTIPODEMO = P_CDTIPODEMO
               AND I.CDTIPODEMO = D.CDTIPODEMO
               AND I.CDITEMSTRU = D.CDITEMSTRU
               AND I.IDTIPOORIG = 'V'
             ORDER BY I.CDITEMSTRU, I.CDSEGUAPUR
         ) LOOP
            IF (V_CDCHAVE <> I.CDITEMSTRU) OR ((I.CDORIGDEMO = '0065') AND (I.CDORIGDEMO = '0067')) THEN
               V_VRRESULTADO := I.VRREFETMP;
               IF I.CDITEMSTRU <> '0071' THEN
                  SELECT SUM(DECODE(V_VRCONTDATA,
                                    1, T.VRREFETMP01,
                                    2, T.VRREFETMP02,
                                    3, T.VRREFETMP03,
                                    4, T.VRREFETMP04,
                                    5, T.VRREFETMP05,
                                    6, T.VRREFETMP06,
                                    7, T.VRREFETMP07,
                                    8, T.VRREFETMP08,
                                    9, T.VRREFETMP09,
                                   10, T.VRREFETMP10,
                                   11, T.VRREFETMP11,
                                   12, T.VRREFETMP12)) AS VRREFETMP
                    INTO V_SALDITVAR
                    FROM ITEMAPUCTB C, TMPCONTABGER T
                   WHERE C.CDTIPODEMO = P_CDTIPODEMO
                     AND C.CDITEMSTRU = I.CDITEMSTRU
                     AND T.CDTIPODEMOTMP = SUBSTR(C.CDORIGDEMO,1,2)
                     AND T.CDITEMSTRUTMP = SUBSTR(C.CDORIGDEMO,3,4)
                     AND T.CDEMPRESATMP = P_CDEMPRESA
                     AND T.NRCONTABGER = V_NRCONTABGER
                     AND T.CDFILIALTMP = F.CDFILIAL
                     AND C.IDTIPOORIG = 'V'
                     AND (((T.CDITEMSTRUTMP <> '0065') AND (T.CDITEMSTRUTMP <> '0067')) OR (I.CDITEMSTRU = '0070'))
                     AND EXISTS(SELECT IT.CDTIPODEMO
                                FROM ITEMAPUCTB IT
                                WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                                  AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                                  AND IT.IDTIPOORIG = 'V')
                     AND DECODE(V_VRCONTDATA,
                                1, T.DTREFETMP01,
                                2, T.DTREFETMP02,
                                3, T.DTREFETMP03,
                                4, T.DTREFETMP04,
                                5, T.DTREFETMP05,
                                6, T.DTREFETMP06,
                                7, T.DTREFETMP07,
                                8, T.DTREFETMP08,
                                9, T.DTREFETMP09,
                               10, T.DTREFETMP10,
                               11, T.DTREFETMP11,
                               12, T.DTREFETMP12) = D.DTMES;
               ELSE
                  SELECT SUM(DECODE(V_VRCONTDATA,
                                    1, T.VRREFETMP01,
                                    2, T.VRREFETMP02,
                                    3, T.VRREFETMP03,
                                    4, T.VRREFETMP04,
                                    5, T.VRREFETMP05,
                                    6, T.VRREFETMP06,
                                    7, T.VRREFETMP07,
                                    8, T.VRREFETMP08,
                                    9, T.VRREFETMP09,
                                   10, T.VRREFETMP10,
                                   11, T.VRREFETMP11,
                                   12, T.VRREFETMP12)) AS VRREFETMP
                    INTO V_SALDITVAR
                    FROM ITEMAPUCTB C, TMPCONTABGER T
                   WHERE C.CDTIPODEMO = P_CDTIPODEMO
                     AND C.CDITEMSTRU = I.CDITEMSTRU
                     AND T.CDTIPODEMOTMP = SUBSTR(C.CDORIGDEMO,1,2)
                     AND T.CDITEMSTRUTMP = SUBSTR(C.CDORIGDEMO,3,4)
                     AND SUBSTR(C.CDORIGDEMO,3,4) = SUBSTR(I.CDORIGDEMO,3,4)
                     AND T.CDEMPRESATMP = P_CDEMPRESA
                     AND T.NRCONTABGER = V_NRCONTABGER
                     AND T.CDFILIALTMP = F.CDFILIAL
                     AND C.IDTIPOORIG = 'V'
                     AND (((T.CDITEMSTRUTMP <> '0065') AND (T.CDITEMSTRUTMP <> '0067')) OR (I.CDITEMSTRU = '0070'))
                     AND EXISTS(SELECT IT.CDTIPODEMO
                                FROM ITEMAPUCTB IT
                                WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                                  AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                                  AND IT.IDTIPOORIG = 'V')
                     AND DECODE(V_VRCONTDATA,
                                1, T.DTREFETMP01,
                                2, T.DTREFETMP02,
                                3, T.DTREFETMP03,
                                4, T.DTREFETMP04,
                                5, T.DTREFETMP05,
                                6, T.DTREFETMP06,
                                7, T.DTREFETMP07,
                                8, T.DTREFETMP08,
                                9, T.DTREFETMP09,
                               10, T.DTREFETMP10,
                               11, T.DTREFETMP11,
                               12, T.DTREFETMP12) = D.DTMES;
               END IF;

               IF V_SALDITVAR IS NOT NULL THEN
                  V_VRRESULTADO := V_VRRESULTADO + NVL(V_SALDITVAR,0);
               END IF;
               IF I.IDOPERACAO = 'D' THEN
                  V_VRDIVISAO1 := V_VRRESULTADO;
                  V_VRDIVISAO2 := 0;
               END IF;
               IF I.IDOPERACAO = 'M' THEN
                  V_VRPARCELA1 := V_VRRESULTADO;
                  V_VRPARCELA2 := 0;
               END IF;
            ELSE
               IF I.IDOPERACAO = 'S' THEN
                  V_VRRESULTADO := V_VRRESULTADO + I.VRREFETMP;
               ELSIF I.IDOPERACAO = 'U' THEN
                  V_VRRESULTADO := V_VRRESULTADO - I.VRREFETMP;
               ELSIF I.IDOPERACAO = 'M' THEN
                  IF I.NMITEMSTRU = 'TAR' THEN
                      SELECT NVL(MAX(VRTXDESPREGE),0)
                        INTO V_VRPARCELA2
                        FROM TXDESPREGE
                       WHERE CDAREAGERE = F.CDAREAGERE
                         AND DTTXDESPREGE = D.DTMES;
                     IF V_VRPARCELA2 = 0 THEN
                        SELECT NVL(MAX(VRTXDESPREGE),0)
                          INTO V_VRPARCELA2
                          FROM TXDESPREGE
                         WHERE CDAREAGERE = F.CDAREAGERE
                           AND DTTXDESPREGE = ADD_MONTHS(D.DTMES, -1);
                     END IF;
                     IF (V_VRPARCELA1 <> 0) AND (V_VRPARCELA2 <> 0) THEN
                        V_VRRESULTADO := (V_VRPARCELA1 * (V_VRPARCELA2/100)) * (-1);
                     ELSE
                        V_VRRESULTADO := 0;
                     END IF;
                  ELSIF I.NMITEMSTRU = 'TAC' THEN
                     IF (V_VRPARCELA1 <> 0) THEN
                        SELECT NVL(MAX(VRTXDESPREGE),0)
                          INTO V_VRTXADMCENT
                          FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                         WHERE T.CDAREAGERE = FL.CDAREAGERE
                           AND FL.CDSUPERVISOR = O.CDOPERADOR
                           AND O.NMOPERADOR = 'TAC'
                           AND DTTXDESPREGE = D.DTMES;
                        IF V_VRTXADMCENT = 0 THEN
                           SELECT NVL(MAX(VRTXDESPREGE),0)
                             INTO V_VRTXADMCENT
                             FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                            WHERE T.CDAREAGERE = FL.CDAREAGERE
                              AND FL.CDSUPERVISOR = O.CDOPERADOR
                              AND O.NMOPERADOR = 'TAC'
                              AND DTTXDESPREGE = ADD_MONTHS(D.DTMES, -1);
                        END IF;
                        V_VRRESULTADO := (V_VRPARCELA1 * (V_VRTXADMCENT/100)) * (-1);

                     ELSE
                        V_VRRESULTADO := 0;
                     END IF;
                  ELSE
                     IF (V_VRPARCELA1 <> 0) AND (V_VRPARCELA2 <> 0) THEN
                        V_VRRESULTADO := (V_VRPARCELA1 * V_VRPARCELA2);
                     ELSE
                        V_VRRESULTADO := 0;
                     END IF;
                  END IF;
               ELSIF I.IDOPERACAO = 'D' THEN
                  IF V_VRDIVISAO1 = 0 THEN
                     V_VRDIVISAO1 := ABS(I.VRREFETMP);
                  ELSE
                     IF ABS(I.VRREFETMP) = 0 THEN
                        IF I.CDITEMSTRU <> '0071' THEN
                           SELECT (NVL(SUM(DECODE(V_VRCONTDATA,
                                                     1, T.VRREFETMP01,
                                                     2, T.VRREFETMP02,
                                                     3, T.VRREFETMP03,
                                                     4, T.VRREFETMP04,
                                                     5, T.VRREFETMP05,
                                                     6, T.VRREFETMP06,
                                                     7, T.VRREFETMP07,
                                                     8, T.VRREFETMP08,
                                                     9, T.VRREFETMP09,
                                                    10, T.VRREFETMP10,
                                                    11, T.VRREFETMP11,
                                                    12, T.VRREFETMP12)),0)) AS VRREFETMP
                             INTO V_VRDIVISAO2
                             FROM ITEMAPUCTB C, TMPCONTABGER T
                            WHERE C.CDTIPODEMO = P_CDTIPODEMO
                              AND C.CDITEMSTRU = I.CDITEMSTRU
                              AND T.CDTIPODEMOTMP = SUBSTR(I.CDORIGDEMO,1,2)
                              AND T.CDITEMSTRUTMP = SUBSTR(I.CDORIGDEMO,3,4)
                              AND T.CDEMPRESATMP = P_CDEMPRESA
                              AND T.NRCONTABGER = V_NRCONTABGER
                              AND T.CDFILIALTMP = F.CDFILIAL
                              AND C.IDTIPOORIG = 'V'
                              AND C.CDORIGDEMO = I.CDORIGDEMO
                              AND EXISTS(SELECT IT.CDTIPODEMO
                                         FROM ITEMAPUCTB IT
                                         WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                                           AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                                           AND IT.IDTIPOORIG = 'V')
                              AND DECODE(V_VRCONTDATA,
                                         1, T.DTREFETMP01,
                                         2, T.DTREFETMP02,
                                         3, T.DTREFETMP03,
                                         4, T.DTREFETMP04,
                                         5, T.DTREFETMP05,
                                         6, T.DTREFETMP06,
                                         7, T.DTREFETMP07,
                                         8, T.DTREFETMP08,
                                         9, T.DTREFETMP09,
                                        10, T.DTREFETMP10,
                                        11, T.DTREFETMP11,
                                        12, T.DTREFETMP12) = D.DTMES;
                        ELSE
                           SELECT (NVL(SUM(DECODE(V_VRCONTDATA,
                                                     1, T.VRREFETMP01,
                                                     2, T.VRREFETMP02,
                                                     3, T.VRREFETMP03,
                                                     4, T.VRREFETMP04,
                                                     5, T.VRREFETMP05,
                                                     6, T.VRREFETMP06,
                                                     7, T.VRREFETMP07,
                                                     8, T.VRREFETMP08,
                                                     9, T.VRREFETMP09,
                                                    10, T.VRREFETMP10,
                                                    11, T.VRREFETMP11,
                                                    12, T.VRREFETMP12)),0)) AS VRREFETMP
                             INTO V_VRDIVISAO2
                             FROM ITEMAPUCTB C, TMPCONTABGER T
                            WHERE C.CDTIPODEMO = P_CDTIPODEMO
                              AND C.CDITEMSTRU = I.CDITEMSTRU
                              AND T.CDTIPODEMOTMP = SUBSTR(I.CDORIGDEMO,1,2)
                              AND T.CDITEMSTRUTMP = SUBSTR(I.CDORIGDEMO,3,4)
                              AND SUBSTR(C.CDORIGDEMO,3,4) = SUBSTR(I.CDORIGDEMO,3,4)
                              AND T.CDEMPRESATMP = P_CDEMPRESA
                              AND T.NRCONTABGER = V_NRCONTABGER
                              AND T.CDFILIALTMP = F.CDFILIAL
                              AND C.IDTIPOORIG = 'V'
                              AND C.CDORIGDEMO = I.CDORIGDEMO
                              AND EXISTS(SELECT IT.CDTIPODEMO
                                         FROM ITEMAPUCTB IT
                                         WHERE IT.CDTIPODEMO = T.CDTIPODEMOTMP
                                           AND IT.CDITEMSTRU = T.CDITEMSTRUTMP
                                           AND IT.IDTIPOORIG = 'V')
                              AND DECODE(V_VRCONTDATA,
                                         1, T.DTREFETMP01,
                                         2, T.DTREFETMP02,
                                         3, T.DTREFETMP03,
                                         4, T.DTREFETMP04,
                                         5, T.DTREFETMP05,
                                         6, T.DTREFETMP06,
                                         7, T.DTREFETMP07,
                                         8, T.DTREFETMP08,
                                         9, T.DTREFETMP09,
                                        10, T.DTREFETMP10,
                                        11, T.DTREFETMP11,
                                        12, T.DTREFETMP12) = D.DTMES;
                        END IF;
                     ELSE
                        V_VRDIVISAO2 := (I.VRREFETMP);
                     END IF;
                  END IF;
                  IF (V_VRDIVISAO1 <> 0) AND (V_VRDIVISAO2 <> 0) THEN
                     V_VRRESULTADO := ((V_VRDIVISAO2) / ABS(V_VRDIVISAO1)) * 100;
                  ELSE
                     V_VRRESULTADO := 0;
                  END IF;
               END IF;
            END IF;
            V_INSERT_TEXT :=                  'UPDATE TMPCONTABGER';
            V_INSERT_TEXT := V_INSERT_TEXT || '   SET DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||' = :P_DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||',';
            V_INSERT_TEXT := V_INSERT_TEXT || '       VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0')||' = :P_VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0');
            V_INSERT_TEXT := V_INSERT_TEXT || ' WHERE NRCONTABGER = :P_NRCONTABGER';
            V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDEMPRESATMP = :P_CDEMPRESATMP';
            V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDFILIALTMP = :P_CDFILIALTMP';
            V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDTIPODEMOTMP = :P_CDTIPODEMOTMP';
            V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDITEMSTRUTMP = :P_CDITEMSTRUTMP';
            V_INSERT_TEXT := V_INSERT_TEXT || '   AND CDCONTCTBLTMP = :P_CDCONTCTBLTMP';
            C_INSERT := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(C_INSERT, V_INSERT_TEXT, DBMS_SQL.V7);
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_DTREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0') , D.DTMES);
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_VRREFETMP'||LPAD(TO_CHAR(V_VRCONTDATA),2,'0') , V_VRRESULTADO );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_NRCONTABGER' , V_NRCONTABGER );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDEMPRESATMP' , P_CDEMPRESA );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDFILIALTMP' , F.CDFILIAL );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDTIPODEMOTMP' , P_CDTIPODEMO );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDITEMSTRUTMP' , I.CDITEMSTRU );
            DBMS_SQL.BIND_VARIABLE(C_INSERT, ':P_CDCONTCTBLTMP' , '0000000000' );
            V_RESULT := DBMS_SQL.EXECUTE(C_INSERT);
            DBMS_SQL.CLOSE_CURSOR(C_INSERT);
            V_CDCHAVE := I.CDITEMSTRU;
         END LOOP;
      END LOOP;
      COMMIT;
   END LOOP;
   P_NRCONTABGER :=  V_NRCONTABGER;
END;
/