CREATE OR REPLACE PROCEDURE AUMENPERC
(P_TABPRECO   IN PRECOSECIA.NRTABPRECO%TYPE,
 P_SERVICO    IN VARCHAR,
 P_TSERVICO   IN VARCHAR,
 P_AUMENPERC  IN NUMBER,
 P_CIA        IN COMPANHIA.CDCIA%TYPE,
 P_FILIAL     IN FILIAL.CDFILIAL%TYPE,
 P_NMTABPRE   IN TABPRECOSE.NMTABPRECO%TYPE,
 P_DTINITAB   IN TABPRECOSE.DTINIVIGTAB%TYPE,
 P_DTFIMTAB   IN TABPRECOSE.DTFIMVIGTAB%TYPE,
 P_OPERADOR   IN OPERADOR.CDOPERADOR%TYPE,
 P_GERATAB    IN VARCHAR)
IS
 V_CDSEQLS       NUMBER;
 V_CDSEQLP       NUMBER;
 V_VALORREAL     PRECOSECIA.VRPRESERV%TYPE;
 V_VALORDOLAR    PRECOSECIA.VRPRESERVD%TYPE;
 V_NRTABPRECO    TABPRECOSE.NMTABPRECO%TYPE;
 V_TESTVR        NUMBER;
 V_TESTVD        NUMBER;
 V_NRDECITOTANF  PARAMGERAL.NRDECITOTANF%TYPE;

BEGIN

   SELECT NVL(NRDECITOTANF, 2)
   INTO V_NRDECITOTANF
   FROM PARAMGERAL;

   IF (P_GERATAB = 'N') THEN
      FOR CUR_PERC IN (
         SELECT CDCIA,CDFILIAL,NRTABPRECO,CDSERVICO,NRSEQPRECO,VRPRESERV,VRPRESERVD,
                ((P_AUMENPERC/100)+1)*VRPRESERV  AS VALORREAL,
                ((P_AUMENPERC/100)+1)*VRPRESERVD AS VALORDOLAR
         FROM PRECOSECIA
         WHERE (CDCIA      = P_CIA            ) AND
               (CDFILIAL   = P_FILIAL         ) AND
               (NRTABPRECO = P_TABPRECO       ) AND
               (INSTR(P_SERVICO, CDSERVICO||DTINIVIGSE||DTFIMVIGSE )>0 OR P_TSERVICO = 'T')) LOOP

         NOVO_CODIGO('LOGPRECOSE'||P_FILIAL||P_CIA||P_TABPRECO||CUR_PERC.CDSERVICO,V_CDSEQLS,1);

         INSERT INTO LOGPRECOSE
           (CDCIALS,CDFILILS,NRTABPRECOLS,CDSERVICOLS,NRSEQPRECOLS,CDSEQLS,
            VRPREANTLS,VRPREDANTLS,VRPREATULS,VRPREDATULS,CDOPERLS,
            DTHRINCLPS)
         VALUES
           (CUR_PERC.CDCIA,CUR_PERC.CDFILIAL,CUR_PERC.NRTABPRECO,CUR_PERC.CDSERVICO,CUR_PERC.NRSEQPRECO,V_CDSEQLS,
            ROUND(CUR_PERC.VRPRESERV, V_NRDECITOTANF),ROUND(CUR_PERC.VRPRESERVD, 2), ROUND(CUR_PERC.VALORREAL, V_NRDECITOTANF), ROUND(CUR_PERC.VALORDOLAR, 2),
            P_OPERADOR,SYSDATE);



         UPDATE PRECOSECIA
         SET VRPRESERV  = ROUND(CUR_PERC.VALORREAL, V_NRDECITOTANF),
             VRPRESERVD = ROUND(CUR_PERC.VALORDOLAR, 2)
         WHERE (CDCIA      = P_CIA              ) AND
               (CDFILIAL   = P_FILIAL           ) AND
               (NRTABPRECO = P_TABPRECO         ) AND
               (CDSERVICO  = CUR_PERC.CDSERVICO ) AND
               (NRSEQPRECO = CUR_PERC.NRSEQPRECO);

         FOR CUR_PROP IN (
            SELECT CDCIA,CDFILIAL,NRTABPRECO,CDSERVICO,NRSEQPRECO,QTPAXINICIAL,QTPAXFINAL,
                   VRPRESERPRO, ((P_AUMENPERC/100)+1)*VRPRESERPRO  AS VRPROREAL,
                   VRPRESERDPRO,((P_AUMENPERC/100)+1)*VRPRESERDPRO AS VRPRODOLAR,
                   P_OPERADOR, SYSDATE
            FROM PROPRECOSE
            WHERE (CDCIA      = P_CIA              ) AND
                  (CDFILIAL   = P_FILIAL           ) AND
                  (NRTABPRECO = P_TABPRECO         ) AND
                  (CDSERVICO  = CUR_PERC.CDSERVICO ) AND
                  (NRSEQPRECO = CUR_PERC.NRSEQPRECO)) LOOP

            NOVO_CODIGO('LOGPROPRECO'||P_FILIAL||P_CIA||P_TABPRECO||CUR_PROP.QTPAXINICIAL||CUR_PROP.QTPAXFINAL||CUR_PROP.CDSERVICO,V_CDSEQLP,1);

            INSERT INTO LOGPROPRECO
              (CDCIALP,CDFILILP,NRTABPRECOLP,CDSERVICOLP,NRSEQPRECOLP,QTPAXINILP,
               QTPAXFIMLP,CDSEQLP,VRPREANTLP,VRPREATULP,VRPREDANTLP,VRPREDATULP,
               CDOPERLPRE,DTHRINCLPRE)
            VALUES
              (CUR_PROP.CDCIA,CUR_PROP.CDFILIAL,CUR_PROP.NRTABPRECO,CUR_PROP.CDSERVICO,CUR_PROP.NRSEQPRECO,CUR_PROP.QTPAXINICIAL,
               CUR_PROP.QTPAXFINAL,V_CDSEQLP, ROUND(CUR_PROP.VRPRESERPRO, V_NRDECITOTANF), ROUND(CUR_PROP.VRPROREAL, V_NRDECITOTANF),
               ROUND(CUR_PROP.VRPRESERDPRO, 2), ROUND(CUR_PROP.VRPRODOLAR, 2),P_OPERADOR,SYSDATE);
         END LOOP;

         UPDATE PROPRECOSE
         SET VRPRESERPRO  = ROUND(((P_AUMENPERC/100)+1)*VRPRESERPRO, V_NRDECITOTANF),
             VRPRESERDPRO = ROUND(((P_AUMENPERC/100)+1)*VRPRESERDPRO, 2)
         WHERE (CDCIA      = CUR_PERC.CDCIA     ) AND
               (CDFILIAL   = CUR_PERC.CDFILIAL  ) AND
               (NRTABPRECO = CUR_PERC.NRTABPRECO) AND
               (CDSERVICO  = CUR_PERC.CDSERVICO ) AND
               (NRSEQPRECO = CUR_PERC.NRSEQPRECO);

      END LOOP;

   ELSE
      NOVO_CODIGO('TABELA/PRECO'||P_FILIAL||P_CIA,V_NRTABPRECO,1);

      INSERT INTO TABPRECOSE
        (CDCIA,CDFILIAL,NRTABPRECO,NMTABPRECO,DTINIVIGTAB,
         DTFIMVIGTAB,DTINCTAB,CDOPERINCTAB)
      VALUES
        (P_CIA,P_FILIAL,SUBSTR(V_NRTABPRECO,18,3),P_NMTABPRE,P_DTINITAB,
         P_DTFIMTAB,SYSDATE,P_OPERADOR);

      INSERT INTO PRECOSECIA
        (CDCIA,CDFILIAL,NRTABPRECO,CDSERVICO,NRSEQPRECO,DTHRINCPRESE,VRPRESERV,
         VRPRESERVD,VRCUSTOSE,VRCUSTOSEE,CDOPERPRESE,DTINIVIGSE,DTFIMVIGSE)
      SELECT CDCIA,CDFILIAL,SUBSTR(V_NRTABPRECO,18,3),PS.CDSERVICO, PS.NRSEQPRECO,SYSDATE,
             ROUND(((P_AUMENPERC/100)+1)*VRPRESERV, V_NRDECITOTANF),
             ROUND(((P_AUMENPERC/100)+1)*VRPRESERVD, 2),
             VRCUSTOSE,VRCUSTOSEE,P_OPERADOR,TO_DATE(NULL),TO_DATE(NULL)
      FROM PRECOSECIA PS,
           (SELECT
              MAX(NRSEQPRECO) NRSEQPRECO,
              CDSERVICO
            FROM
               PRECOSECIA
            WHERE
               (CDCIA      = P_CIA     ) AND
               (CDFILIAL   = P_FILIAL  ) AND
               (NRTABPRECO = P_TABPRECO)
            GROUP BY
               CDSERVICO) M
      WHERE (CDCIA      = P_CIA     ) AND
            (CDFILIAL   = P_FILIAL  ) AND
            (NRTABPRECO = P_TABPRECO) AND
            (PS.NRSEQPRECO = M.NRSEQPRECO ) AND
            (PS.CDSERVICO = M.CDSERVICO   ) AND
            (INSTR(P_SERVICO, PS.CDSERVICO)>0 OR P_TSERVICO = 'T');

      INSERT INTO PROPRECOSE
        (CDCIA,CDFILIAL,NRTABPRECO,CDSERVICO,NRSEQPRECO,QTPAXINICIAL,QTPAXFINAL,
         VRPRESERPRO,VRPRESERDPRO,DTHRINCPRPRO,CDOPERPRPRO)
      SELECT P_CIA,P_FILIAL,SUBSTR(V_NRTABPRECO,18,3),PP.CDSERVICO,PP.NRSEQPRECO,QTPAXINICIAL,QTPAXFINAL,
             ROUND(((P_AUMENPERC/100)+1)*VRPRESERPRO,V_NRDECITOTANF),ROUND(((P_AUMENPERC/100)+1)*VRPRESERDPRO, 2),SYSDATE,
             P_OPERADOR
      FROM PROPRECOSE PP,
           (SELECT
              MAX(NRSEQPRECO) NRSEQPRECO,
              CDSERVICO
            FROM
               PRECOSECIA
            WHERE
               (CDCIA      = P_CIA     ) AND
               (CDFILIAL   = P_FILIAL  ) AND
               (NRTABPRECO = P_TABPRECO)
            GROUP BY
               CDSERVICO) M
      WHERE (CDCIA      = P_CIA     ) AND
            (CDFILIAL   = P_FILIAL  ) AND
            (NRTABPRECO = P_TABPRECO) AND
            (PP.NRSEQPRECO = M.NRSEQPRECO ) AND
            (PP.CDSERVICO = M.CDSERVICO   ) AND
            (INSTR(P_SERVICO, PP.CDSERVICO)>0 OR P_TSERVICO = 'T');

   END IF;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('AUMENPERC','5.01.024'); END;
/

