CREATE OR REPLACE PROCEDURE INCLUI_CLASSE_VOO(
  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 PLANOVOO.DTHRPLAVOOPV%TYPE,
  P_CDCLASSE     IN CLASSE.CDCLASSE%TYPE,
  P_CDOPERADOR   IN SERVPLANO.CDUSRRESPSP%TYPE
) AS
  V_EXISTECICLO      NUMBER;
  V_CDSERVICO        SERVICOCIA.CDSERVICO%TYPE;
  V_QTTOTPAX         EQUIPCIA.QTTOTPAX%TYPE;
  V_QTSERVFIXSP      SERVPLANO.QTSERVFIXSP%TYPE;
  V_QTSESBYPRSP      SERVPLANO.QTSESBYPRSP%TYPE;
  V_QTSESOLICSP      SERVPLANO.QTSESOLICSP%TYPE;
  V_NRPROPAXSP       SERVPLANO.NRPROPAXSP%TYPE;
  V_VRPELOADSP       SERVPLANO.VRLOADSESP%TYPE;
  V_VRPREDOLAR       PRECOSECIA.VRPRESERVD%TYPE;
  V_VRPRECORRENTE    PRECOSECIA.VRPRESERV%TYPE;
  V_NRITEMPLANO      SERVPLANO.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          PLANOVOO.CDEQUIP%TYPE;
  V_SGPREFIXO        PLANOVOO.SGPREFIXO%TYPE;
  V_IDTIPMANUAL      SERVICOCIA.IDTIPMANUAL%TYPE;
  V_IDTPPRECO        SERVICOCIA.IDTPPRECO%TYPE;
  V_IDFATSESP        SERVICOCIA.IDFATSE%TYPE;
  V_VRPRECORR_SBY    SERVPLANO.VRPRUSBYSP%TYPE;
  V_VRPREDOLAR_SBY   SERVPLANO.VRPRUSBYSP%TYPE;
  V_VRPRUSBYSP       SERVPLANO.VRPRUSBYSP%TYPE;
  V_QTSERFATSP       SERVPLANO.QTSERFATSP%TYPE;
  V_QTPAXSBY_CLA     EQUIPCIA.QTTOTPAX%TYPE;
  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     PLANOVOO.IDSITABASPV%TYPE;
  V_SITUACAO         PLANOVOO.IDSITABASPV%TYPE;
  V_IDTPMANUTSP      SERVPLANO.IDTPMANUTSP%TYPE;
  V_DTHRABASTPV      PLANOVOO.DTHRABASTPV%TYPE;
  -- ================================================================ --
  --   CURSOR UTILIZADO DA INSERCAO DE UMA CLASSE NO PLANO DE VOO     --
  -- ================================================================ --
  CURSOR C_SERVICOCIA IS
   SELECT PA.CDSERVICO , PA.CDCLASSE   , PA.NRPROPAX , PA.VRLOADSE , PA.VRPESOSE,
          PA.QTSESTBY  , PA.QTSERV     , PA.SGPREFIXO, PA.CDEQUIP  , PA.CDCICLO ,
          PA.IDPROPSERV, PA.IDTIPMANUAL, PA.IDTPPRECO, PA.IDFATSE
   FROM   PAD_VOOPREFEQUI PA
   WHERE (PA.CDFILIAL     = P_CDFILIAL ) AND
         (PA.CDCIA        = P_CDCIA    ) AND
         (PA.NRVOO        = P_NRVOO    ) AND
         (PA.CDTRECHO     = P_CDTRECHO ) AND
         (PA.IDSITSERVICO = '1'        ) AND
         ((PA.CDCLASSE    = P_CDCLASSE) OR (P_CDCLASSE = 'TODA'));


BEGIN

   IF P_CDCLASSE <> 'TODA' THEN
      SELECT NVL(MAX(IDSITABASPV),0)
      INTO   V_SITUACAO
      FROM   PLANOVOO
      WHERE  CDFILIAL     = P_CDFILIAL AND
             CDCIA        = P_CDCIA    AND
             NRVOO        = P_NRVOO    AND
             CDTRECHO     = P_CDTRECHO AND
             DTHRPLAVOOPV = P_DTHRPLAVOOPV;

      IF V_SITUACAO = '3' THEN
        raise_application_error( -20502, 'Man_N_Perm', false);
        /*Manutecao nao permitida. O voo esta cancelado.', false);*/

      END IF;
   END IF;

/* 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, '#Com_N_Exit'||'     '||P_CDCIA||'*', false);
         /*Companhia '||P_CDCIA||' nao existente.*/

   END IF;

   IF P_CDCLASSE = 'TODA' THEN /* RE-GERACAO DO PLANO DE VOO */
     V_IDTPMANUTSP := '1F';
     SELECT COUNT(*)
     INTO   V_EXISTE_DELIVERY
     FROM  SERVPLANO SP,DELIVERY DE
     WHERE (sp.CDFILIAL     = P_CDFILIAL    ) AND
           (SP.CDCIA        = P_CDCIA       ) AND
           (SP.NRVOO        = P_NRVOO       ) AND
           (SP.CDTRECHO     = P_CDTRECHO    ) AND
           (SP.DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
           (SP.CDFILIAL     = DE.CDFILIAL   ) AND
           (SP.NRDELIVERY   = DE.NRDELIVERY ) AND
           (DE.IDSITDEL      <> '3');

     IF V_EXISTE_DELIVERY > 0 THEN
       raise_application_error( -20502, 'Re_Pla_voo', false);
       /*Atencao! Re-geracao do plano de voo nao permitida. Existem deliveries ja emitidos para este voo.'*/

     ELSE
       DELETE FROM SERVPLANO
       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 PLANOVOO
       SET    IDREGERACAO = '2'
       WHERE (CDFILIAL     = P_CDFILIAL    ) AND
             (CDCIA        = P_CDCIA       ) AND
             (NRVOO        = P_NRVOO       ) AND
             (CDTRECHO     = P_CDTRECHO    ) AND
             (DTHRPLAVOOPV = P_DTHRPLAVOOPV);
     END IF;
   ELSE
     V_IDTPMANUTSP := '1E';  /* INCLUSAO DE UMA CLASSE */
   END IF;

   SELECT DISTINCT CDEQUIP,SGPREFIXO,DTHRABASTPV
   INTO   V_CDEQUIP, V_SGPREFIXO, V_DTHRABASTPV
   FROM   PLANOVOO
   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   PREVISAOPAX
       WHERE (CDFILIAL      = P_CDFILIAL) AND
             (CDCIA         = P_CDCIA)    AND
             (NRVOO         = P_NRVOO)    AND
             (CDTRECHO      = P_CDTRECHO) AND
             (DTHRPLAVOOPV  = P_DTHRPLAVOOPV)) LOOP

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

      UPDATE PREVISAOPAX
      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_SGPREFIXO , V_CDEQUIP   , V_CDCICLO_SE,
      V_IDPROPSERV , V_IDTIPMANUAL, V_IDTPPRECO , V_IDFATSESP;
     EXIT WHEN C_SERVICOCIA%NOTFOUND;

     -- ============================================================ --
     -- 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_CICLO(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   PREVISAOPAX
       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 */

         RETORNA_AGRUPAMENTO(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDCLASSE,
                             V_CDEQUIP,V_SGPREFIXO,V_CDAGRDECO);

         SELECT QTTOTPAXAT,QTPAXSBY
         INTO   V_QTTOTPAX, V_QTPAXSBY_CLA
         FROM   PREVISAOPAX
         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_QTSESBYPRSP <> 0 ) AND (V_QTSESBYPRSP IS NOT NULL) THEN
           V_QTSESBYPRSP := V_QTSESBYPRSP;
         ELSE
           V_QTSESBYPRSP := V_QTPAXSBY_CLA;
         END IF;
         IF (V_QTSESBYPRSP IS NULL) THEN
           V_QTSESBYPRSP := 0;
         END IF;

         -- =========================================================== --
         -- Calculo das quantidades                                     --
         -- =========================================================== --
         CALCULA_QUANTIDADES(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDSERVICO,
                             V_CDCLASSE, V_CDCICLO_SE,V_QTTOTPAX,
                             V_IDPROPSERV,V_VRPELOADSP,V_NRPROPAXSP,
                             V_QTSERVFIXSP, V_QTSESOLICSP, V_QTSESBYPRSP);

         -- =========================================================== --
         -- TESTA O TIPO DE PRECO (1 - UNITARIO OU 2 - TOTAL PARA       --
         -- CALCULAR A QUANTIDADE A FATURAR                             --
         -- =========================================================== --
         IF (V_IDTPPRECO = '2') and     /* PRECO TOTAL */
            ((V_QTSERVFIXSP+V_QTSESOLICSP) > 0) THEN
            V_QTSERFATSP := 1;
         ELSE
            V_QTSERFATSP := V_QTSERVFIXSP + V_QTSESOLICSP;
         END IF;

         -- =========================================================== --
         -- Calculo dos precos                                          --
         -- =========================================================== --

         CALCULA_PRECO('N',P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO, V_CDSERVICO,
                       /*V_CDCLASSE,V_CDCICLO_SE,*/P_DTHRPLAVOOPV,V_DTHRABASTPV,
                       V_QTTOTPAX,V_QTSERFATSP,NULL,V_VRPRECORRENTE,V_VRPREDOLAR);

         -- =========================================================== --
         -- Calculo do preco do stand-by                                --
         -- =========================================================== --
         IF  (V_QTSESBYPRSP > 0) THEN
           /* RETIRADO A PEDIDO DO RODRIGO EM 03/04/2000
           CALCULA_PRECO(P_CDCIA, V_CDSERVICO, P_DTHRPLAVOOPV,
                         V_QTSESBYPRSP,V_VRPRECORR_SBY,V_VRPREDOLAR_SBY,NULL);*/
           CALCULA_PRECO('N',P_CDFILIAL,P_CDCIA, P_NRVOO,P_CDTRECHO,V_CDSERVICO,
                         /*V_CDCLASSE, V_CDCICLO_SE,*/P_DTHRPLAVOOPV,V_DTHRABASTPV,
                         V_QTTOTPAX,V_QTSERFATSP,NULL,V_VRPRECORR_SBY,V_VRPREDOLAR_SBY);

           IF V_IDTIPMANUAL = '1' THEN
             V_VRPRUSBYSP := V_VRPRECORR_SBY;
           ELSE
             V_VRPRUSBYSP := V_VRPREDOLAR_SBY;
           END IF;
         ELSE
           V_VRPRUSBYSP := 0;
         END IF;

         -- =========================================================== --
         -- GRAVA NA TABELA  SERVPLANO                                  --
         -- =========================================================== --
         SELECT MAX(NRITEMPLANSP)
         INTO   V_NRITEMPLANO
         FROM   SERVPLANO
         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 SERVPLANO
            (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  , CDSERVICO    , /*CDCLASSE    , CDCICLO    ,*/ VRPRUDOLSESP, VRPRURESESP,
            IDTIPSCBOSP , VRLOADSESP   , NRPROPAXSP  ,
            VRPRUSBYSP  , IDTIPMANUALSP, IDTPMANUTSP , IDTPPRECOSP, IDFATSESP)
          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_IDPROPSERV, V_VRPELOADSP, V_NRPROPAXSP,
             V_VRPRUSBYSP, V_IDTIPMANUAL,V_IDTPMANUTSP,V_IDTPPRECO,V_IDFATSESP);
         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;

 /* update a plano voo para ajustar a situacao do plano de voo */
   SELECT COUNT(*)
   INTO   V_PENDENTE
   FROM   SERVPLANO
   WHERE  CDFILIAL     = P_CDFILIAL AND
          CDCIA        = P_CDCIA    AND
          NRVOO        = P_NRVOO    AND
          CDTRECHO     = P_CDTRECHO AND
          DTHRPLAVOOPV = P_DTHRPLAVOOPV AND
          IDSITSERSP   = '1';
   IF V_PENDENTE > 0 THEN
     V_SITUACAO_VOO := '1';
   ELSE
     V_SITUACAO_VOO := '2';
   END IF;

   UPDATE PLANOVOO
   SET    IDSITABASPV  = V_SITUACAO_VOO
   WHERE  CDFILIAL     = P_CDFILIAL AND
          CDCIA        = P_CDCIA    AND
          NRVOO        = P_NRVOO    AND
          CDTRECHO     = P_CDTRECHO AND
          DTHRPLAVOOPV = P_DTHRPLAVOOPV;
END;
/