CREATE OR REPLACE PROCEDURE INCLUI_CLASSE_VOOM(
  P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
  P_CDCIA        IN COMPANHIA.CDCIA%TYPE,
  P_NRVOO        IN VOO.NRVOO%TYPE,
  P_CDTRECHO     IN VOOTRECHO.CDTRECHO%TYPE,
  P_DTHRPLAVOOPV IN PLANOVOOM.DTHRPLAVOOPV%TYPE,
  P_CDCLASSE     IN CLASSE.CDCLASSE%TYPE,
  P_CDOPERADOR   IN SERVPLANOM.CDUSRRESPSP%TYPE,
  P_NRORG IN NUMBER DEFAULT 1
) AS
  V_EXISTECICLO      NUMBER;
  V_CDSERVICO        SERVICOCIA.CDSERVICO%TYPE;
  V_QTTOTPAX         FLOAT;
  V_QTSERVFIXSP      SERVPLANOM.QTSERVFIXSP%TYPE;
  V_QTSESBYPRSP      SERVPLANOM.QTSESBYPRSP%TYPE;
  V_QTSESOLICSP      SERVPLANOM.QTSESOLICSP%TYPE;
  V_NRPROPAXSP       SERVPLANOM.NRPROPAXSP%TYPE;
  V_VRPELOADSP       SERVPLANOM.VRLOADSESP%TYPE;
  V_VRPREDOLAR       FLOAT;
  V_VRPRECORRENTE    FLOAT;
  V_NRITEMPLANO      SERVPLANOM.NRITEMPLANSP%TYPE;
  V_VRPESOSE         SERVICOCIA.VRPESOSE%TYPE;
  V_CDAGRDECO        CONFIGVOO.CDAGRDEL%TYPE;
  V_RETORNA_CDCICLO  CICLOCIA.CDCICLO%TYPE;
  V_CDCICLO_PREVISAO CICLOCIA.CDCICLO%TYPE;
  V_CICLO_UNICO      CICLOCIA.CDCICLO%TYPE;
  V_CDCICLO_SE       CICLOCIA.CDCICLO%TYPE;
  V_CDEQUIP          PLANOVOOM.CDEQUIP%TYPE;
  V_SGPREFIXO        PLANOVOOM.SGPREFIXO%TYPE;
  V_IDTIPMANUAL      SERVICOCIA.IDTIPMANUAL%TYPE;
  V_IDTPPRECO        SERVICOCIA.IDTPPRECO%TYPE;
  V_IDFATSESP        SERVICOCIA.IDFATSE%TYPE;
  V_VRPRECORR_SBY    SERVPLANOM.VRPRUSBYSP%TYPE;
  V_VRPREDOLAR_SBY   SERVPLANOM.VRPRUSBYSP%TYPE;
  V_VRPRUSBYSP       SERVPLANOM.VRPRUSBYSP%TYPE;
  V_QTSERFATSP       SERVPLANOM.QTSERFATSP%TYPE;
  V_QTPAXSBY_CLA     FLOAT;
  V_CDCLASSE         CLASSE.CDCLASSE%TYPE;
  V_IDPROPSERV       PADVOOTRE.IDPROPSEVOO%TYPE;
  V_EXISTECIA        NUMBER;
  V_TESTE            NUMBER;
  V_EXISTE_DELIVERY  NUMBER;
  V_PENDENTE         NUMBER;
  V_SITUACAO_VOO     PLANOVOOM.IDSITABASPV%TYPE;
  V_SITUACAO         PLANOVOOM.IDSITABASPV%TYPE;
  V_IDTPMANUTSP      SERVPLANOM.IDTPMANUTSP%TYPE;
  V_DTHRABASTPV      PLANOVOOM.DTHRABASTPV%TYPE;
  V_IDPROPORCAO      VARCHAR2(1);
  V_QTPROPORCAO      FLOAT;
  V_ITEM_TAB_PRECO NUMBER;
  V_NRTABPRECO VARCHAR2(3);
  V_NRSEQPRECO NUMBER;
  V_SGCIA COMPANHIA.SGCIA%TYPE;
  V_SERVICO VARCHAR2(2000);
  V_DSSERVICOCIAFILI VARCHAR2(250);
  V_SGCLASSE CLASSE.SGCLASSE%TYPE;
  V_IDCICLO CICLO.IDCICLO%TYPE;
  V_IDCALCPROPDLCOM VARCHAR2(1);
  V_QTDE_PENDENTE NUMBER;
  V_QTDE_EMITIDO NUMBER;
  V_QTDE_CANCELADO NUMBER;
  V_QTDE_TOTAL NUMBER;
  V_QTTOTPAX_PAXSBY FLOAT;
  V_QTSERVFATU FLOAT;
  V_MSGERRO VARCHAR2(2000);
  -- ================================================================ --
  --   CURSOR UTILIZADO DA INSERCAO DE UMA CLASSE NO PLANO DE VOO     --
  -- ================================================================ --
  CURSOR C_SERVICOCIA IS
   SELECT PA.NRSERVICOCIAFILI AS CDSERVICO , PA.CDCLASSE, PA.NRPROPAX AS NRPROPAX , PA.VRLOADSE AS VRLOADSE , SG.VRPESOSE,
          PA.QTSESTBY AS QTSESTBY  , PA.QTSERV AS QTSERV, PA.CDCICLO ,
          PA.IDPROPSERV AS IDPROPSERV, SF.IDTIPMANUAL, SF.IDTPPRECO, SF.IDFATSE,
          SF.DSSERVICOCIAFILI, CL.SGCLASSE, CI.IDCICLO
   FROM   PAD_VOOEQUIPAMENTO PA
          INNER JOIN SERVICOCIAFILI SF ON SF.CDFILIAL = PA.CDFILIAL AND
                                          SF.CDCIA = PA.CDCIA AND
                                          SF.NRSERVICOCIAFILI = PA.NRSERVICOCIAFILI
          INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = SF.NRSERVICOGEN
          INNER JOIN CLASSE CL ON CL.CDCLASSE = PA.CDCLASSE
          INNER JOIN CICLO CI ON CI.CDCICLO = PA.CDCICLO
   WHERE (PA.CDFILIAL     = P_CDFILIAL ) AND
         (PA.CDCIA        = P_CDCIA    ) AND
         (PA.NRVOO        = P_NRVOO    ) AND
         (PA.CDTRECHO     = P_CDTRECHO ) AND
         (PA.DTDATA       = P_DTHRPLAVOOPV) AND
         (SF.IDSITSERVICO = '1'        ) AND
         ((PA.IDORIGEM = 'V') OR (PA.CDEQUIP = V_CDEQUIP)) AND
         ((PA.CDCLASSE    = P_CDCLASSE) OR (P_CDCLASSE = 'TODA'));

BEGIN
   /*PARAMETROS*/
  SELECT MAX(IDCALCPROPDLCOM)
    INTO V_IDCALCPROPDLCOM
    FROM PARAMCOMIS
   WHERE (CDFILIAL = P_CDFILIAL);

/* INCLUI UMA CLASSE EM UM VOO OU REGERA OS SERVICOS DO PLANO DE VOO */
   SELECT COUNT(*)
   INTO V_EXISTECIA
   FROM   COMPANHIA
   WHERE  CDCIA = P_CDCIA;
   IF V_EXISTECIA <= 0 THEN
     raise_application_error( -20502, 'APPERRMSGSTR' || 'APP-00037' || '$$' || NVL(P_CDCIA, ' ') || 'APPERRMSGEND', false);
         /*Companhia '||P_CDCIA||' nao existente.*/
   END IF;

   IF P_CDCLASSE = 'TODA' THEN /* RE-GERACAO DO PLANO DE VOO */
     V_IDTPMANUTSP := '1F';
     VALIDA_NF_DELIVERYS_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO,
                                   P_DTHRPLAVOOPV, 'TODAS', 'N', P_NRORG);
     DELETE FROM CONEXAOSUBGRUPO
     WHERE (CDFILIAL     = P_CDFILIAL    ) AND
           (CDCIA        = P_CDCIA       ) AND
           (NRVOO        = P_NRVOO       ) AND
           (CDTRECHO     = P_CDTRECHO    ) AND
           (DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
           EXISTS (SELECT 1
                     FROM SERVPLANOM S 
                    WHERE S.CDFILIAL = CONEXAOSUBGRUPO.CDFILIAL
                      AND S.CDCIA = CONEXAOSUBGRUPO.CDCIA
                      AND S.NRVOO = CONEXAOSUBGRUPO.NRVOO
                      AND S.CDTRECHO = CONEXAOSUBGRUPO.CDTRECHO
                      AND S.DTHRPLAVOOPV = CONEXAOSUBGRUPO.DTHRPLAVOOPV
                      AND (S.NRDELIVERY IS NULL
                      OR S.IDSITSERSP = '3'));

     DELETE FROM SERVPLANOM
     WHERE (CDFILIAL     = P_CDFILIAL    ) AND
           (CDCIA        = P_CDCIA       ) AND
           (NRVOO        = P_NRVOO       ) AND
           (CDTRECHO     = P_CDTRECHO    ) AND
           (DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
           (NRDELIVERY IS NULL OR IDSITSERSP = '3');

     UPDATE PLANOVOOM
     SET    IDREGERACAO = '2'
     WHERE (CDFILIAL     = P_CDFILIAL    ) AND
           (CDCIA        = P_CDCIA       ) AND
           (NRVOO        = P_NRVOO       ) AND
           (CDTRECHO     = P_CDTRECHO    ) AND
           (DTHRPLAVOOPV = P_DTHRPLAVOOPV);
   ELSE
     V_IDTPMANUTSP := '1E';  /* INCLUSAO DE UMA CLASSE */
   END IF;

   SELECT DISTINCT CDEQUIP,SGPREFIXO,DTHRABASTPV
   INTO   V_CDEQUIP, V_SGPREFIXO, V_DTHRABASTPV
   FROM   PLANOVOOM
   WHERE (CDFILIAL     = P_CDFILIAL    ) AND
         (CDCIA        = P_CDCIA       ) AND
         (NRVOO        = P_NRVOO       ) AND
         (CDTRECHO     = P_CDTRECHO    ) AND
         (DTHRPLAVOOPV = P_DTHRPLAVOOPV);


   --ALTERA_PREFEQUI('1',P_CDFILIAL,P_CDCIA,P_NRVOO,V_CDEQUIP,V_SGPREFIXO);

   -- ============================================================ --
   -- BUSCA  O CICLO UNICO DA COMPANHIA                            --
   -- ============================================================ --
   SELECT COUNT(*)
   INTO   V_EXISTECICLO
   FROM   CIAFILI
   WHERE  CDFILIAL = P_CDFILIAL AND
          CDCIA    = P_CDCIA;
   IF V_EXISTECICLO <> 0 THEN
   BEGIN
      SELECT CDCICLO
      INTO   V_CICLO_UNICO
      FROM   CIAFILI
      WHERE  CDFILIAL = P_CDFILIAL AND
             CDCIA    = P_CDCIA;
   END;
   END IF;

   FOR CUR_PREVPAX IN
      (SELECT CDCLASSE
       FROM   PREVISAOPAXM
       WHERE (CDFILIAL      = P_CDFILIAL) AND
             (CDCIA         = P_CDCIA)    AND
             (NRVOO         = P_NRVOO)    AND
             (CDTRECHO      = P_CDTRECHO) AND
             (DTHRPLAVOOPV  = P_DTHRPLAVOOPV)) LOOP

      V_CDCICLO_PREVISAO := NULL;
      -- ========================================================= --
      -- BUSCAR O CICLO VIGENTE NESTA DATA                         --
      -- ========================================================= --
      RETORNA_CICLOM(P_CDFILIAL,P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV,
                    '',CUR_PREVPAX.CDCLASSE,V_CDCICLO_PREVISAO);

      UPDATE PREVISAOPAXM
      SET    CDCICLO = V_CDCICLO_PREVISAO
      WHERE (CDFILIAL      = P_CDFILIAL)     AND
            (CDCIA         = P_CDCIA)        AND
            (NRVOO         = P_NRVOO)        AND
            (CDTRECHO      = P_CDTRECHO)     AND
            (DTHRPLAVOOPV  = P_DTHRPLAVOOPV) AND
            (CDCLASSE      = CUR_PREVPAX.CDCLASSE);

   END LOOP;

   OPEN C_SERVICOCIA;
   LOOP
     FETCH C_SERVICOCIA
      INTO
      V_CDSERVICO  , V_CDCLASSE   , V_NRPROPAXSP, V_VRPELOADSP, V_VRPESOSE  ,
      V_QTSESBYPRSP, V_QTSERVFIXSP, V_CDCICLO_SE,
      V_IDPROPSERV , V_IDTIPMANUAL, V_IDTPPRECO , V_IDFATSESP,
      V_DSSERVICOCIAFILI, V_SGCLASSE, V_IDCICLO;

     EXIT WHEN C_SERVICOCIA%NOTFOUND;

     V_SERVICO := V_DSSERVICOCIAFILI||'$$'||V_SGCLASSE||'$$'||V_IDCICLO;

     -- ============================================================ --
     -- GARANTIR QUE TODAS AS QUANTIDADES, LOADINGS E                --
     -- PROPORCAO NAO ESTEJAM NULAS                                  --
     -- ============================================================ --
     IF (V_VRPESOSE   IS NULL) THEN
       V_VRPESOSE  := 0;
     END IF;

     -- ============================================================ --
     -- BUSCAR O CICLO VIGENTE NESTA DATA                            --
     -- ============================================================ --
     V_RETORNA_CDCICLO := V_CDCICLO_SE;

     RETORNA_CICLOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV,
                   V_CDSERVICO,V_CDCLASSE,V_RETORNA_CDCICLO);

     IF (V_CDCICLO_SE = V_RETORNA_CDCICLO) OR (V_CDCICLO_SE = V_CICLO_UNICO) THEN
     BEGIN       
       SELECT COUNT(*)
         INTO V_TESTE
         FROM PREVISAOPAXM
        WHERE (CDFILIAL     = P_CDFILIAL) AND
              (CDCIA        = P_CDCIA)    AND
              (NRVOO        = P_NRVOO)    AND
              (CDTRECHO     = P_CDTRECHO) AND
              (CDCLASSE     = V_CDCLASSE) AND
              (DTHRPLAVOOPV = P_DTHRPLAVOOPV);
       IF V_TESTE > 0 THEN  /* SE EXISTE PREVISAO DE PAX PARA A CLASSE */
         
         V_CDAGRDECO := F_RETORNA_AGRUPAMENTO(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDCLASSE,
                             V_CDEQUIP,V_SGPREFIXO);

         SELECT QTTOTPAXAT,QTPAXSBY
         INTO   V_QTTOTPAX, V_QTPAXSBY_CLA
         FROM   PREVISAOPAXM
         WHERE  (CDFILIAL     = P_CDFILIAL) AND
                (CDCIA        = P_CDCIA)    AND
                (NRVOO        = P_NRVOO)    AND
                (CDTRECHO     = P_CDTRECHO) AND
                (CDCLASSE     = V_CDCLASSE) AND
                (DTHRPLAVOOPV = P_DTHRPLAVOOPV);

         -- ========================================================== --
         -- UTILIZO STANDBY INFORMADO NA GERACAO DO PLANO DE VOO OU NA --
         -- INCLUSAO DE UMA CLASSE NO PLANO;SE ESTE NAO FOR INFORMADO, --
         -- PEGO O STANDBY DA VIEW                                     --
         -- ========================================================== --
         IF (V_QTPAXSBY_CLA <> 0 ) AND (V_QTPAXSBY_CLA IS NOT NULL) THEN
           V_QTSESBYPRSP := V_QTPAXSBY_CLA;
         ELSE
           V_QTSESBYPRSP := V_QTSESBYPRSP;
         END IF;
         IF (V_QTSESBYPRSP IS NULL) THEN
           V_QTSESBYPRSP := 0;
         END IF;

         CONEXAO_COM_SUBGRU_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, 
                                       V_CDSERVICO, V_CDCLASSE, V_CDCICLO_SE, V_DTHRABASTPV, 
                                       'S', NULL, P_CDOPERADOR, P_NRORG, V_MSGERRO);
         IF V_MSGERRO IS NOT NULL THEN
            FOR CUR_MSERROS IN
                     (
                      SELECT REGEXP_SUBSTR(V_MSGERRO,'[^'||'>><<'||']+', 1, LEVEL) AS MSG FROM DUAL
                        CONNECT BY REGEXP_SUBSTR(V_MSGERRO, '[^'||'>><<'||']+', 1, LEVEL) IS NOT NULL
                     ) LOOP
               RAISE_APPLICATION_ERROR(-20000, CUR_MSERROS.MSG);           
            END LOOP;
         END IF;
        
         -- =========================================================== --
         -- Calculo das quantidades                                     --
         -- =========================================================== --
         V_IDPROPORCAO := NULL;
         CALCULA_QUANTIDADESM(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDSERVICO,
                            V_CDCLASSE,V_RETORNA_CDCICLO,V_DTHRABASTPV, V_QTTOTPAX,
                            V_IDPROPORCAO, V_QTSESOLICSP, V_QTSESBYPRSP,
                            V_NRPROPAXSP, V_VRPELOADSP, V_QTSERVFIXSP, V_QTSERFATSP);

         V_QTTOTPAX_PAXSBY := CASE WHEN V_IDCALCPROPDLCOM = 'S' THEN V_QTTOTPAX+V_QTSESBYPRSP 
                                   ELSE V_QTTOTPAX 
                              END;
         V_QTSERVFATU := CASE WHEN V_IDCALCPROPDLCOM = 'S' THEN (V_QTSERVFIXSP + V_QTSESOLICSP)+V_QTSESBYPRSP 
                              ELSE (V_QTSERVFIXSP + V_QTSESOLICSP) 
                         END;

         CALCULA_PRECOM('N',P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDSERVICO,V_CDCLASSE, V_RETORNA_CDCICLO,
                           P_DTHRPLAVOOPV,V_DTHRABASTPV, V_QTTOTPAX_PAXSBY,V_QTSERVFATU,NULL,
                           V_VRPRECORRENTE,V_VRPREDOLAR);

         V_VRPRUSBYSP := CASE WHEN V_QTSESBYPRSP > 0 THEN 
                                 CASE WHEN V_IDTIPMANUAL = '1' THEN V_VRPRECORRENTE
                                      ELSE V_VRPREDOLAR
                                 END 
                              ELSE 0 
                         END;
         
         -- =========================================================== --
         -- GRAVA NA TABELA  SERVPLANOM                                  --
         -- =========================================================== --
         SELECT MAX(NRITEMPLANSP)
         INTO   V_NRITEMPLANO
         FROM   SERVPLANOM
         WHERE (CDFILIAL     = P_CDFILIAL)     AND
               (CDCIA        = P_CDCIA)        AND
               (NRVOO        = P_NRVOO)        AND
               (CDTRECHO     = P_CDTRECHO)     AND
               (DTHRPLAVOOPV = P_DTHRPLAVOOPV);
         IF (V_NRITEMPLANO IS NOT NULL) THEN
            V_NRITEMPLANO := V_NRITEMPLANO+1;
         ELSE
            V_NRITEMPLANO:= 1;
         END IF;         

         IF (V_QTSERVFIXSP+V_QTSESOLICSP+V_QTSESBYPRSP) > 0 THEN

           INSERT INTO SERVPLANOM
            (CDFILIAL   , CDCIA        , NRITEMPLANSP, NRVOO      , CDTRECHO    , DTHRPLAVOOPV,
            NRDELIVERY  , IDSITSERSP   , IDSTKDTSP   , CDUSRRESPSP, QTSESOLICSP , QTANTAPRODSP,
            QTSESBYPRSP , QTSESBYUTSP  , QTCORTSERVSP, QTSERVFIXSP,
            QTATUAPRODSP, QTSERFATSP   ,
            QTREALPROD  , DTHRFEPRODSP , VRPESOSESP  , DTHRINCSESP,
            DTHRTRASESP , DTHRCANCSESP , CDAGRDECO   , CDAGRDEDE  ,
            IDSEREXTSP  , NRSERVICOCIAFILI, CDCLASSE, CDCICLO, VRPRUDOLSESP, VRPRURESESP,
            IDTIPSCBOSP , VRLOADSESP   , NRPROPAXSP  ,
            VRPRUSBYSP  , IDTIPMANUALSP, IDTPMANUTSP , IDTPPRECOSP, IDFATSESP,
            NRORG, NRORGINCLUSAO, NRORGULTATU,
            CDOPERINCLUSAO, CDOPERULTATU,
            DTINCLUSAO, DTULTATU
            )
          VALUES
            (P_CDFILIAL ,P_CDCIA, V_NRITEMPLANO, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV,
             NULL, '1', '1',   P_CDOPERADOR, V_QTSESOLICSP, 0,
             V_QTSESBYPRSP, 0, 0, V_QTSERVFIXSP,
             (V_QTSERVFIXSP+V_QTSESBYPRSP+V_QTSESOLICSP),V_QTSERFATSP,
             (V_QTSERVFIXSP+V_QTSESBYPRSP+V_QTSESOLICSP),NULL, V_VRPESOSE, SYSDATE,
             NULL, NULL, V_CDAGRDECO, V_CDCLASSE,
             '1', V_CDSERVICO, V_CDCLASSE, V_CDCICLO_SE, V_VRPREDOLAR,V_VRPRECORRENTE,
             V_IDPROPORCAO, V_VRPELOADSP, V_NRPROPAXSP,
             V_VRPRUSBYSP, V_IDTIPMANUAL,V_IDTPMANUTSP,V_IDTPPRECO,V_IDFATSESP,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);

          CONEXAO_COM_SUBGRU_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, 
                                       V_CDSERVICO, V_CDCLASSE, V_CDCICLO_SE, V_DTHRABASTPV, 
                                       'N', V_NRITEMPLANO, P_CDOPERADOR, P_NRORG, V_MSGERRO);
         END IF;
       END IF; /* END DA CLASSE */
     END;      /* END DO BEGIN DO CICLO */
     END IF;   /* ENDIF DO CICLO */
   END LOOP;

   -- =========================================================== --
   -- VOLTA O EQUIPAMENTO E PREFIXO DO VOO                        --
   -- =========================================================== --
   --ALTERA_PREFEQUI('2',P_CDFILIAL,P_CDCIA,P_NRVOO,NULL,NULL);

   CLOSE C_SERVICOCIA;

   /* Revalida o status do plano de voo */
   REVALIDA_STATUS_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, 
                             P_DTHRPLAVOOPV, P_CDOPERADOR, P_NRORG);
END;
/