CREATE OR REPLACE PROCEDURE GERA_PLANO_VOOM(
  P_CDFILIPROC     IN VARCHAR2,
  P_CDOPERADOR     IN SERVPLANOM.CDUSRRESPSP%TYPE,
  P_TPPREVISAO     IN PLANOVOOM.IDTPPREVISAO%TYPE,
  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_NRORG IN NUMBER DEFAULT 1,
  P_NRLISTAERROS OUT NUMBER
 )AS
  V_CDCLASSE        CLASSE.CDCLASSE%TYPE;
  V_SGCLASSE        CLASSE.SGCLASSE%TYPE;
  V_SGPREFIXO       PREFIXOCIA.SGPREFIXO%TYPE;
  V_CDCICLO_SE      CICLOCIA.CDCICLO%TYPE;
  V_IDCICLO         VARCHAR2(10);
  V_RETORNA_CDCICLO CICLOCIA.CDCICLO%TYPE;
  V_CICLO_UNICO     CICLOCIA.CDCICLO%TYPE;
  V_NRSERVICOCIAFILI SERVICOCIAFILI.NRSERVICOCIAFILI%TYPE;
  V_IDSERVESP       SERVICOCIAFILI.IDSERVESP%TYPE;
  V_IDTIPMANUAL     SERVICOCIAFILI.IDTIPMANUAL%TYPE;
  V_IDTPPRECO       SERVICOCIAFILI.IDTPPRECO%TYPE;
  V_IDFATSE         SERVICOCIAFILI.IDFATSE%TYPE;
  V_NRPROPAXSE      PADVOOTREM.NRPROPAXVOO%TYPE;
  V_VRPELOADSE      PADVOOTREM.VRLOADSEVOO%TYPE;
  V_QTSERVSTBY      PADVOOTREM.QTSESTBYVOO%TYPE;
  V_QTSESBYPRSP     PADVOOTREM.QTSESTBYVOO%TYPE;
  V_QTSERVFIX       PADVOOTREM.QTSERVOO%TYPE;
  V_QTSERFATSP      SERVPLANOM.QTSERFATSP%TYPE;
  V_NRITEMPLANO     SERVPLANOM.NRITEMPLANSP%TYPE;
  V_QTSESOLICSP     SERVPLANOM.QTSESOLICSP%TYPE;
  V_QTSESBYPRSP_INC SERVPLANO.QTSESBYPRSP%TYPE;
  V_VRPESOSE        SERVCICLO.VRPESOSE%TYPE;
  V_QTTOTPAX        EQUIPCIA.QTTOTPAX%TYPE;
  V_PAXSBY          EQUIPCIA.QTTOTPAX%TYPE;
  V_CDEQUIP         EQUIPCIA.CDEQUIP%TYPE;  
  V_IDABASATEN      VOO.IDABASATEN%TYPE;
  V_STABASVOO       VOO.IDABASATEN%TYPE;
  V_QTHRKDTCAR      VOO.QTHRKDTCAR%TYPE;
  V_QTHRKDTDES      VOO.QTHRKDTDES%TYPE;
  V_IDTIPAJUSFAT    VOOTRECHO.IDTIPAJUSFAT%TYPE;
  V_HRDECOLVOO      VOOTRECHO.HRDECOLVOO%TYPE;
  V_HRPOUSOVOO      VOOTRECHO.HRPOUSOVOO%TYPE;
  V_QTSERVICO       PROPSERVICOM.QTSERVICO%TYPE;
  V_VRPREDOLAR      FLOAT;
  V_VRPRECORRENTE   FLOAT;
  V_VRPRECORRENTE_AUX FLOAT;
  V_VRPRECORR_SBY   FLOAT;
  V_VRPREDOLAR_SBY  FLOAT;
  V_VRPRUSBYSP      FLOAT;
  V_QTHRDIFGMT      PARAMCOMIS.QTHRDIFGMT%TYPE;
  V_IDCALCPROPDLCOM PARAMCOMIS.IDCALCPROPDLCOM%TYPE;
  V_IDBREAKGERA     PARAMGERAL.IDTPGERBREAK%TYPE;
  V_NRDECITOTANF    PARAMGERAL.NRDECITOTANF%TYPE;
  V_IDPROPSERV      PADVOOTREM.IDPROPSEVOO%TYPE;
  V_DSSERVICO       VARCHAR2(250);
  V_SGCIA           COMPANHIA.SGCIA%TYPE;
  V_CDAGRDEL        AGRDELIVERY.CDAGRDEL%TYPE;
  V_QTPROPORCAO     FLOAT;
  V_EXISTECIA       NUMBER;
  V_EXISTE_EQUIP    NUMBER;
  V_TESTE           NUMBER;
  V_EXISTECICLO     NUMBER;
  V_DATAHORA        DATE;
  V_SEQ VARCHAR2(20);
  V_IDVINCULOMANUAL VARCHAR2(1);
  V_MSGERRO VARCHAR2(2000); 

  V_SERVICO VARCHAR2(4000);

  V_NRTABPRECO     TABPRECOSEM.NRTABPRECO%TYPE;
  V_PROP            NUMBER;
  V_1PORPAX         NUMBER;
  V_ERRO_TABPRECO VARCHAR2(1);
  V_ERRO_CONEXAOSUBGRU VARCHAR2(1);
  V_ERRO_QUANTIDADE VARCHAR2(1);
  V_NRSEQPRECO NUMBER;  
  V_QTTOTPAX_PAXSBY FLOAT;
  V_QTSERVFATU FLOAT;
  V_QUANTIDADE FLOAT;

  V_CONTAGR  NUMBER;
  V_ITEM_TAB_PRECO NUMBER;   
  V_IDPROPORCAO VARCHAR2(1);

  V_CDBASEORI VARCHAR2(4); 
  V_CDBASEDES VARCHAR2(4); 
  
  V_CDMANCIA VARCHAR2(4);

  V_NRSERVICOCIAFILIMENU NUMBER; 
  V_CDSUBGRU VARCHAR2(10); 
  V_QTSUBGRU FLOAT; 
  V_IDPROPSUBSEMAM VARCHAR2(1); 
  V_QTPROPSUBSEMAM FLOAT;
  V_NUM NUMBER;
  V_IDSERVICO VARCHAR2(1);
  V_SERVICO_VINCULADO NUMBER; 
  V_QTSERVICOS FLOAT;
  V_EXISTE_PLANOVOOM NUMBER;
  -- ===================================================================== --
  --                        DECLARACAO DE CURSOR                           --
  -- ===================================================================== --
  --   OS CURSORES C_CONFIGVOO, C_CONFIGPREF, C_CONFIGEQUI E C_CLASSECIA   --
  --   SAO UTILIZADOS PARA RECUPERAR A CLASSE, QTDE DE PASSAGEIROS E       --
  --   QTDE DE STANDBY, COM O OBJETIVO DE ATUALIZAR PREVISAOPAX, NA        --
  --   SEGUINTE ORDEM: PROCURAR EM CONFIGVOO SE NAO EXISTIR PESQUISAR EM   --
  --   CONFIGPREF CASO NAO ECONTRE PROCURAR EM CONFIGEQUI SE NAO ENCONTRAR --
  --   EM NENHUMA DAS TABELAS MENCIONADAS INSERIR PARA CADA CLASSE NA      --
  --   CLASSECIA PARA A COMPANHIA INFORMADA COM 0(ZERO) PARA PAX E STANDBY --
  -- ===================================================================== --
  CURSOR CUR_PAD_VOOPREFEQUI IS
   SELECT PA.NRSERVICOCIAFILI AS CDSERVICO, PA.CDCLASSE, PA.NRPROPAX,
          PA.VRLOADSE , SF.IDTIPMANUAL, SF.IDFATSE,
          SE.VRPESOSE , PA.QTSESTBY   , PA.QTSERV,
          SF.IDTPPRECO,
          PA.CDCICLO  , PA.IDPROPSERV , SF.DSSERVICOCIAFILI AS DSSERVICO,
          CL.SGCLASSE, CI.IDCICLO, SG.IDSERVICO
   FROM   PAD_VOOEQUIPAMENTO PA, SERVICOCIAFILI SF, SERVCICLO SE, CLASSE CL, CICLO CI, SERVICOGEN SG
   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
         (PA.CDFILIAL     = SF.CDFILIAL ) AND
         (PA.CDCIA        = SF.CDCIA    ) AND
         (PA.CDCLASSE = CL.CDCLASSE) AND
         (PA.CDCICLO = CI.CDCICLO) AND
         (PA.NRSERVICOCIAFILI = SF.NRSERVICOCIAFILI) AND
         ((PA.IDORIGEM = 'V') OR (PA.CDEQUIP = V_CDEQUIP)) AND
         (SF.IDSITSERVICO = '1'         ) AND
         (SE.CDFILIAL      = PA.CDFILIAL) AND
         (SE.CDCIA         = PA.CDCIA   ) AND
         (SE.NRSERVICOCIAFILI = PA.NRSERVICOCIAFILI) AND
         (SE.CDCLASSE         = PA.CDCLASSE) AND
         (SE.CDCICLO          = PA.CDCICLO) AND
         (SG.NRSERVICOGEN = SF.NRSERVICOGEN);
         
BEGIN
  SELECT MAX(SGCIA) INTO V_SGCIA FROM COMPANHIA WHERE CDCIA = P_CDCIA;

  SELECT COUNT(1)
    INTO V_EXISTE_PLANOVOOM
    FROM PLANOVOOM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND NRVOO = P_NRVOO
     AND CDTRECHO = P_CDTRECHO
     AND DTHRPLAVOOPV = P_DTHRPLAVOOPV;

  IF V_EXISTE_PLANOVOOM > 0 THEN
     /*Operacao bloqueada. Ja existe plano de voo gerado para o voo $param[0] na data $param[2].*/
     RAISE_APPLICATION_ERROR(-20000, 'APPERRMSGSTR' || 'APP-00083' || '$$' || V_SGCIA || ' - ' || P_NRVOO || '$$' || TO_CHAR(P_DTHRPLAVOOPV, 'DD/MM/YYYY') || 'APPERRMSGEND');
  END IF;

  BEGIN
  SELECT VO.CDEQUIP   , VO.SGPREFIXO , VO.QTHRKDTCAR  , VO.QTHRKDTDES,
         VT.HRDECOLVOO, VT.HRPOUSOVOO, VT.IDTIPAJUSFAT, VO.IDABASATEN, 
         VO.IDVINCULOMANUAL, CDBASEORI, CDBASEDES
  INTO   V_CDEQUIP    , V_SGPREFIXO  , V_QTHRKDTCAR   , V_QTHRKDTDES,
         V_HRDECOLVOO , V_HRPOUSOVOO , V_IDTIPAJUSFAT , V_IDABASATEN,
         V_IDVINCULOMANUAL, V_CDBASEORI, V_CDBASEDES
  FROM   VOOTRECHO VT, VOO VO, VOOTRECHODATA VTD
  WHERE (VT.CDFILIAL = P_CDFILIAL ) AND
        (VT.CDCIA    = P_CDCIA    ) AND
        (VT.NRVOO    = P_NRVOO    ) AND
        (VT.CDTRECHO = P_CDTRECHO ) AND
        (VO.CDFILIAL = VT.CDFILIAL) AND
        (VO.CDCIA    = VT.CDCIA   ) AND
        (VO.NRVOO    = VT.NRVOO   ) AND
        (VTD.CDFILIAL = VT.CDFILIAL) AND
        (VTD.CDCIA = VT.CDCIA) AND
        (VTD.NRVOO = VT.NRVOO) AND
        (VTD.CDTRECHO = VT.CDTRECHO) AND
        (P_DTHRPLAVOOPV BETWEEN VTD.DTINIVIGENCIA AND NVL(VTD.DTFIMVIGENCIA, P_DTHRPLAVOOPV) );
  EXCEPTION WHEN NO_DATA_FOUND THEN
    /*Voo ou trecho informado nao encontrado.*/
    RAISE_APPLICATION_ERROR(-20000,'APPERRMSGSTR' || 'APP-00020' || 'APPERRMSGEND');
  END;

  IF (V_IDTIPAJUSFAT = '1') OR (V_IDTIPAJUSFAT IS NULL) THEN
    V_DATAHORA := P_DTHRPLAVOOPV;
  ELSIF (V_IDTIPAJUSFAT = '2') THEN BEGIN
    IF V_HRDECOLVOO IS NULL THEN
       V_DATAHORA:= P_DTHRPLAVOOPV;
    ELSE BEGIN
       SELECT NVL(MAX(QTHRDIFGMT),-3)
       INTO V_QTHRDIFGMT
       FROM PARAMCOMIS
       WHERE (CDFILIAL = P_CDFILIAL);

       IF (TO_NUMBER(SUBSTR(V_HRDECOLVOO,1,2))-V_QTHRDIFGMT) > 23 THEN
          V_DATAHORA:= P_DTHRPLAVOOPV+1;
       ELSIF (TO_NUMBER(SUBSTR(V_HRDECOLVOO,1,2))-V_QTHRDIFGMT) < 0 THEN
          V_DATAHORA:= P_DTHRPLAVOOPV-1;
       ELSE
          V_DATAHORA:= P_DTHRPLAVOOPV;
       END IF;
    END;
    END IF;
  END;
  END IF;

  -- ============================================================ --
  -- 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 MAX(CDCICLO), MAX(IDVINCULOMANUAL)
    INTO   V_CICLO_UNICO, V_IDVINCULOMANUAL
    FROM   CIAFILI
    WHERE  CDFILIAL = P_CDFILIAL AND
           CDCIA    = P_CDCIA;
  END;
  END IF;  

  /* ===============================================================================
  FAZ AS VALIDACOES DA GERACAO
  ==================================================================================
  */
  P_NRLISTAERROS := NULL;
  
  /*PARAMETROS*/
  SELECT MAX(IDCALCPROPDLCOM)
    INTO V_IDCALCPROPDLCOM
    FROM PARAMCOMIS
   WHERE (CDFILIAL = P_CDFILIAL);

  -- ========================================================= --
  -- GARANTIR QUE TODAS AS QUANTIDADES NAO ESTEJAM NULAS       --
  -- ========================================================= --
  UPDATE PADVOOTREM
  SET    QTSESTBYVOO = 0
  WHERE (QTSESTBYVOO IS NULL);

  UPDATE PADVOOTREM
  SET    QTSERVOO = 0
  WHERE (QTSERVOO IS NULL);

  -- ========================================================= --
  -- TROCA O VOO NA TABELA DE VOO, PELO VOO INFORMADO NA HORA  --
  -- DA GERACAO DO PLANO, SE                                   --
  -- ========================================================= --
  SELECT COUNT(*)
  INTO   V_EXISTE_EQUIP
  FROM   PREVPAXTEMPM PT, VOO VO
  WHERE  (PT.CDFILIAL     = P_CDFILIAL    ) AND
         (PT.CDCIAPPT     = P_CDCIA       ) AND
         (PT.NRVOOPPT     = P_NRVOO       ) AND
         (PT.CDTRECHOPPT  = P_CDTRECHO    ) AND
         (PT.DTHRPLAPPT   = P_DTHRPLAVOOPV) AND
         (ROWNUM          = 1             ) AND
         (PT.CDFILIAL     = VO.CDFILIAL   ) AND
         (PT.CDCIAPPT     = VO.CDCIA      ) AND
         (PT.NRVOOPPT     = VO.NRVOO      ) AND
         ((PT.CDEQUIPPPT   <> VO.CDEQUIP  ) OR
          (PT.SGPREFIXOPPT||'-' <> VO.SGPREFIXO||'-'));

  IF V_EXISTE_EQUIP > 0 THEN
    SELECT CDEQUIPPPT, SGPREFIXOPPT
    INTO   V_CDEQUIP,V_SGPREFIXO
    FROM   PREVPAXTEMPM
    WHERE  (CDFILIAL    = P_CDFILIAL    ) AND
           (CDCIAPPT    = P_CDCIA       ) AND
           (NRVOOPPT    = P_NRVOO       ) AND
           (CDTRECHOPPT = P_CDTRECHO    ) AND
           (DTHRPLAPPT  = P_DTHRPLAVOOPV) AND
           (ROWNUM      = 1);

    /*ALTERA_PREFEQUI('1',P_CDFILIAL,P_CDCIA,P_NRVOO,V_CDEQUIP,V_SGPREFIXO);*/
      UPDATE VOO
      SET CDEQUIPTMP   = CDEQUIP,
          SGPREFIXOTMP = SGPREFIXO
      WHERE (CDFILIAL  = P_CDFILIAL) AND
            (CDCIA     = P_CDCIA   ) AND
            (NRVOO     = P_NRVOO   );

      UPDATE VOO
      SET CDEQUIP     = V_CDEQUIP,
          SGPREFIXO   = V_SGPREFIXO
      WHERE (CDFILIAL = P_CDFILIAL) AND
            (CDCIA    = P_CDCIA   ) AND
            (NRVOO    = P_NRVOO   );
  END IF;

  V_STABASVOO := '1';
  

  -- ======================================================= --
  -- INSERCAO DA TABELA PLANOVOO                             --
  -- ======================================================= --
  INSERT INTO PLANOVOOM
    (CDFILIAL,CDCIA, NRVOO, CDTRECHO, DTHRPLAVOOPV, CDEQUIP, SGPREFIXO,
    HRPREVDECPV, HRPREVPOUPV, QTHRKDTCARPV, QTHRKDTDESPV,
    DTHRABASTPV, IDSITABASPV, DTHRINCPV,
    CDUSRRESPPV, CDPOSPISTAPV, IDTPPREVISAO, IDREGERACAO, CDBASEORI, CDBASEDES,
    NRORG, DTINCLUSAO, CDOPERINCLUSAO, DTULTATU, NRORGINCLUSAO, NRORGULTATU, CDOPERULTATU, IDATIVO)
  VALUES
    (P_CDFILIAL,P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, V_CDEQUIP, V_SGPREFIXO,
    V_HRDECOLVOO, V_HRPOUSOVOO, V_QTHRKDTCAR, V_QTHRKDTDES,
    V_DATAHORA, V_STABASVOO, SYSDATE,
    P_CDOPERADOR, '', P_TPPREVISAO, '1', V_CDBASEORI, V_CDBASEDES,
    P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE, P_NRORG, P_NRORG, P_CDOPERADOR, 'S');

  -- ==================================================== --
  -- INSERCAO DA TABELA PREVISAOPAX                       --
  -- ==================================================== --
  INSERT INTO PREVISAOPAXM
    (CDFILIAL,CDCIA, NRVOO, DTHRPLAVOOPV, CDTRECHO, CDCLASSE,
    QTTOTPAXAN, QTTOTPAXAT, QTPAXSBY, CDUSRRESPPP, 
    NRORG, DTINCLUSAO, CDOPERINCLUSAO, DTULTATU, NRORGINCLUSAO, NRORGULTATU, CDOPERULTATU, IDATIVO)
  SELECT CDFILIAL, CDCIAPPT, NRVOOPPT, P_DTHRPLAVOOPV, CDTRECHOPPT, CDCLASSEPPT,
          0, QTTOTPAXATPPT, QTPAXSBYPPT, P_CDOPERADOR, 
          NRORG, DTINCLUSAO, CDOPERINCLUSAO, DTULTATU, NRORGINCLUSAO, NRORGULTATU, CDOPERULTATU, IDATIVO
  FROM   PREVPAXTEMPM
  WHERE (CDFILIAL    = P_CDFILIAL    ) AND
        (CDCIAPPT    = P_CDCIA       ) AND
        (NRVOOPPT    = P_NRVOO       ) AND
        (CDTRECHOPPT = P_CDTRECHO    ) AND
        (DTHRPLAPPT  = P_DTHRPLAVOOPV);

  UPDATE PREVISAOPAXM
  SET    QTPAXSBY      = NULL
  WHERE (CDFILIAL      = P_CDFILIAL    ) AND
        (CDCIA         = P_CDCIA       ) AND
        (NRVOO         = P_NRVOO       ) AND
        (CDTRECHO      = P_CDTRECHO    ) AND
        (DTHRPLAVOOPV  = P_DTHRPLAVOOPV) AND
        (QTPAXSBY      = 0             );

  BEGIN
     SELECT NVL(NRDECITOTANF, 2)
     INTO V_NRDECITOTANF
     FROM PARAMGERAL
     WHERE
        (CDFILIAL = P_CDFILIAL);
  EXCEPTION
     WHEN No_Data_Found THEN
      V_NRDECITOTANF := NULL;
  END;

  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_RETORNA_CDCICLO := NULL;
    
    -- ========================================================= --
    -- BUSCAR O CICLO VIGENTE NESTA DATA                         --
    -- ========================================================= --
    BEGIN
      RETORNA_CICLOM(P_CDFILIAL,P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV,
                    '',CUR_PREVPAX.CDCLASSE,V_RETORNA_CDCICLO);
    EXCEPTION
      WHEN OTHERS THEN
        INSERE_LISTA_ERROS(P_CDFILIPROC, SQLERRM, P_CDOPERADOR, P_NRORG, P_NRLISTAERROS);
    END;


    UPDATE PREVISAOPAXM
    SET    CDCICLO = V_RETORNA_CDCICLO
    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;

  /* LOOP NA TABELA DE SERVICOS DO VOO */
  OPEN CUR_PAD_VOOPREFEQUI;
  LOOP
    BEGIN
      FETCH
        CUR_PAD_VOOPREFEQUI
      INTO
        V_NRSERVICOCIAFILI , V_CDCLASSE   , V_NRPROPAXSE,
        V_VRPELOADSE, V_IDTIPMANUAL, V_IDFATSE,
        V_VRPESOSE  , V_QTSERVSTBY , V_QTSERVFIX,
        V_IDTPPRECO , 
        V_CDCICLO_SE, V_IDPROPSERV , V_DSSERVICO, V_SGCLASSE, V_IDCICLO, V_IDSERVICO;
      EXIT WHEN CUR_PAD_VOOPREFEQUI%NOTFOUND;

      V_SERVICO := V_DSSERVICO||'$$'||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;
      IF (V_QTSERVSTBY IS NULL) THEN
        V_QTSERVSTBY:= 0;
      END IF;

      V_QTSESBYPRSP_INC := 0;

      -- ============================================================ --
      -- 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_NRSERVICOCIAFILI,V_CDCLASSE,V_RETORNA_CDCICLO);

      IF (V_CDCICLO_SE = V_RETORNA_CDCICLO) OR (V_CDCICLO_SE = V_CICLO_UNICO) THEN
        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 A CLASSE DO SERVICO EXISTE NA PREVISAO DE PAX */
          V_CDAGRDEL := F_RETORNA_AGRUPAMENTO(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_CDCLASSE,
                                                  V_CDEQUIP,V_SGPREFIXO);

          SELECT NVL(MAX(QTTOTPAXAT), 0), NVL(MAX(QTPAXSBY), 0)
          INTO   V_QTTOTPAX, V_PAXSBY
          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 O STANDBY INFORMADO NA GERACAO DO PLANO DE VOO, SE   --
          -- ESTE NAO FOR INFORMADO , PEGO O STANDBY DA VIEW              --
          -- ============================================================ --
          IF (V_PAXSBY <> 0) AND (V_PAXSBY IS NOT NULL) THEN
             V_QTSESBYPRSP := V_PAXSBY;
          ELSE
             V_QTSESBYPRSP := V_QTSERVSTBY;
          END IF;

          -- =========================================================== --
          -- GRAVA NA TABELA  SERVPLANO/RELPLANOVOO                      --
          -- =========================================================== --
          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;

          -- =========================================================== --
          -- Verifica vinculo com subgrupo (se a companhia obrigar)      --
          -- =========================================================== --               
          V_ERRO_CONEXAOSUBGRU := 'N';
          V_MSGERRO := NULL;
          CONEXAO_COM_SUBGRU_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, 
                                       V_NRSERVICOCIAFILI, V_CDCLASSE, V_CDCICLO_SE, V_DATAHORA, 
                                       '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
                INSERE_LISTA_ERROS(P_CDFILIPROC, CUR_MSERROS.MSG, P_CDOPERADOR, P_NRORG, P_NRLISTAERROS);             
             END LOOP;
             V_ERRO_CONEXAOSUBGRU := 'S';
          END IF;

          IF V_ERRO_CONEXAOSUBGRU = 'N' THEN
            -- =========================================================== --
            -- Calculo das quantidades                                     --
            -- =========================================================== --                       
            V_IDPROPORCAO := NULL; -- PEGAR DA CONFIG DA TABELA DE PRECO
            CALCULA_QUANTIDADESM(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_NRSERVICOCIAFILI,
                                V_CDCLASSE,V_CDCICLO_SE,V_DATAHORA, V_QTTOTPAX,
                                V_IDPROPORCAO, V_QTSESOLICSP, V_QTSESBYPRSP,
                                V_NRPROPAXSE, V_VRPELOADSE, V_QTSERVFIX, 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_QTSERVFIX + V_QTSESOLICSP)+V_QTSESBYPRSP 
                                 ELSE (V_QTSERVFIX + V_QTSESOLICSP) 
                            END;

            CALCULA_PRECOM('N',P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,V_NRSERVICOCIAFILI,V_CDCLASSE, V_CDCICLO_SE,
                              P_DTHRPLAVOOPV,V_DATAHORA, 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;
          

            IF (V_QTSERVFIX+V_QTSESOLICSP+V_QTSESBYPRSP) > 0 THEN

               IF (V_NRDECITOTANF = 0) THEN
                  V_VRPRECORRENTE_AUX := ROUND(V_VRPRECORRENTE,0);
               ELSE
                  V_VRPRECORRENTE_AUX := V_VRPRECORRENTE;
               END IF;

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

               CONEXAO_COM_SUBGRU_PLANOVOOM(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, 
                                          V_NRSERVICOCIAFILI, V_CDCLASSE, V_CDCICLO_SE, V_DATAHORA, 
                                          'N', V_NRITEMPLANO, P_CDOPERADOR, P_NRORG, V_MSGERRO);

            END IF;        
          END IF;

        END IF; /* END DA CLASSE */

      END IF;  /* ENDIF DO CICLO */
    EXCEPTION
       WHEN OTHERS THEN
          INSERE_LISTA_ERROS(P_CDFILIPROC, SQLERRM, P_CDOPERADOR, P_NRORG, P_NRLISTAERROS);          
    END;

  END LOOP;

  CLOSE CUR_PAD_VOOPREFEQUI;

  IF P_NRLISTAERROS IS NULL THEN
    DELETE FROM PREVPAXTEMPM
     WHERE CDFILIAL = P_CDFILIAL 
       AND CDCIAPPT = P_CDCIA
       AND NRVOOPPT = P_NRVOO 
       AND CDTRECHOPPT = P_CDTRECHO 
       AND DTHRPLAPPT = P_DTHRPLAVOOPV;

    -- =========================================================== --
    -- VOLTA O EQUIPAMENTO E PREFIXO DO VOO                        --
    -- =========================================================== --
    IF V_EXISTE_EQUIP > 0 THEN
        UPDATE VOO
        SET CDEQUIP      = CDEQUIPTMP,
            SGPREFIXO    = SGPREFIXOTMP
        WHERE (CDFILIAL  = P_CDFILIAL) AND
              (CDCIA     = P_CDCIA   ) AND
              (NRVOO     = P_NRVOO   );

        UPDATE VOO
        SET CDEQUIPTMP   = NULL,
            SGPREFIXOTMP = NULL
        WHERE (CDFILIAL  = P_CDFILIAL) AND
              (CDCIA     = P_CDCIA   ) AND
              (NRVOO     = P_NRVOO   );

      /*ALTERA_PREFEQUI('2',P_CDFILIAL,P_CDCIA,P_NRVOO,NULL,NULL);*/

    END IF;
    
    SELECT MAX(DTHRULTGERA)
    INTO   V_DATAHORA
    FROM   PARAMCOMIS
    WHERE  CDFILIAL = P_CDFILIAL;
    IF (P_DTHRPLAVOOPV > V_DATAHORA) OR (V_DATAHORA IS NULL) THEN
       UPDATE PARAMCOMIS
       SET    DTHRULTGERA = P_DTHRPLAVOOPV
       WHERE  CDFILIAL = P_CDFILIAL;
    END IF;
  ELSE
    DELETE FROM CONEXAOSUBGRUPO
     WHERE CDFILIAL = P_CDFILIAL
       AND CDCIA = P_CDCIA
       AND NRVOO = P_NRVOO
       AND CDTRECHO = P_CDTRECHO
       AND DTHRPLAVOOPV = P_DTHRPLAVOOPV;

    DELETE FROM SERVPLANOM
     WHERE CDFILIAL = P_CDFILIAL
       AND CDCIA = P_CDCIA
       AND NRVOO = P_NRVOO
       AND CDTRECHO = P_CDTRECHO
       AND DTHRPLAVOOPV = P_DTHRPLAVOOPV;
    
    DELETE FROM PREVISAOPAXM
     WHERE CDFILIAL = P_CDFILIAL
       AND CDCIA = P_CDCIA
       AND NRVOO = P_NRVOO
       AND CDTRECHO = P_CDTRECHO
       AND DTHRPLAVOOPV = P_DTHRPLAVOOPV;

    DELETE FROM PLANOVOOM       
     WHERE CDFILIAL = P_CDFILIAL
       AND CDCIA = P_CDCIA
       AND NRVOO = P_NRVOO
       AND CDTRECHO = P_CDTRECHO
       AND DTHRPLAVOOPV = P_DTHRPLAVOOPV;
  END IF;
END;
/