CREATE OR REPLACE
PROCEDURE GERA_CHECKLIST
(
   P_CDFILIAL       IN FILIAL.CDFILIAL%TYPE,
   P_NRLISTACIAVOO  IN LISTA.NRLISTA%TYPE,
   P_DTVOOINI       IN PLANOVOO.DTHRPLAVOOPV%TYPE,
   P_DTVOOFIN       IN PLANOVOO.DTHRPLAVOOPV%TYPE,
   P_IDGERCHECKLIST IN VARCHAR2
)
AS
V_IDDIADASEMANA  VARCHAR2(1);
V_IDVOODOM       VARCHAR2(1);
V_IDVOOSEG       VARCHAR2(1);
V_IDVOOTER       VARCHAR2(1);
V_IDVOOQUA       VARCHAR2(1);
V_IDVOOQUI       VARCHAR2(1);
V_IDVOOSEX       VARCHAR2(1);
V_IDVOOSAB       VARCHAR2(1);
V_DTULTSEM       DATE;

V_CDFILIAL    VARCHAR2(4);
V_CDCIA       VARCHAR2(4);
V_NRVOO       VARCHAR2(5);
V_CDTRECHO    VARCHAR2(4);
V_CDCLASSE    VARCHAR2(4);
V_DTVOO       DATE;
V_QTPREVPAX   FLOAT;
V_CDCICLO     VARCHAR2(4);
V_CICLO_UNICO VARCHAR2(4);

V_NRPROPAXSE      PADVOOTRE.NRPROPAXVOO%TYPE;
V_VRPELOADSE      PADVOOTRE.VRLOADSEVOO%TYPE;
V_QTSERVFIX       PADVOOTRE.QTSERVOO%TYPE;
V_QTSESOLICSP     SERVPLANO.QTSESOLICSP%TYPE;

V_QTSTANDBY       FLOAT;

V_CDMANCIA  MANUALCIA.CDMANCIA%TYPE;

V_COUNTPREVPAX INTEGER;

V_QTPAXCLASSE FLOAT;
BEGIN
   FOR C_PERIODO IN (SELECT DTDATA
                       FROM DATA
                      WHERE DTDATA BETWEEN P_DTVOOINI AND P_DTVOOFIN) LOOP

      SELECT TO_CHAR(C_PERIODO.DTDATA, 'D')
        INTO V_IDDIADASEMANA
        FROM DUAL;

      IF V_IDDIADASEMANA = '1' THEN
         V_IDVOODOM := '1';
      ELSE
         V_IDVOODOM := '';
      END IF;

      IF v_IDDIADASEMANA = '2' THEN
         V_IDVOOSEG := '1';
      ELSE
         V_IDVOOSEG := '';
      END IF;

      IF V_IDDIADASEMANA = '3' THEN
         V_IDVOOTER := '1';
      ELSE
         V_IDVOOTER := '';
      END IF;

      IF V_IDDIADASEMANA = '4' THEN
         V_IDVOOQUA := '1';
      ELSE
         V_IDVOOQUA := '';
      END IF;

      IF V_IDDIADASEMANA = '5' THEN
         V_IDVOOQUI := '1';
      ELSE
         V_IDVOOQUI := '';
      END IF;

      IF V_IDDIADASEMANA = '6' THEN
         V_IDVOOSEX := '1';
      ELSE
         V_IDVOOSEX := '';
      END IF;

      IF V_IDDIADASEMANA = '7' THEN
         V_IDVOOSAB := '1';
      ELSE
         V_IDVOOSAB := '';
      END IF;

      /*DATA DA SEMANA ANTERIOR*/
      SELECT C_PERIODO.DTDATA - 7
        INTO V_DTULTSEM
        FROM DUAL;

      /*SELECIONA TODOS OAS COMPANHIAS, VOOS E TRECHOS  PARA A GERACAO DA PREVISAO*/

      FOR C_CIAVOO IN (SELECT VT.CDFILIAL, VT.CDCIA, VT.NRVOO, VT.CDTRECHO
                           FROM VOO VO, VOOTRECHO VT
                          WHERE (VO.CDFILIAL  = VT.CDFILIAL) AND
                                (VO.CDCIA     = VT.CDCIA   ) AND
                                (VO.NRVOO     = VT.NRVOO   ) AND
                                (IDSITVOO     = '1'        ) AND
                                (VO.CDFILIAL  = P_CDFILIAL ) AND
                                ((IDTIPVOO = '1') OR ((IDTIPVOO = '2') AND(C_PERIODO.DTDATA BETWEEN DTINIVIGVOO AND DTFIMVIGVOO))) AND
                                ((IDVOODOM = V_IDVOODOM) OR
                                 (IDVOOSEG = V_IDVOOSEG) OR
                                 (IDVOOTER = V_IDVOOTER) OR
                                 (IDVOOQUA = V_IDVOOQUA) OR
                                 (IDVOOQUI = V_IDVOOQUI) OR
                                 (IDVOOSEX = V_IDVOOSEX) OR
                                 (IDVOOSAB = V_IDVOOSAB)) AND
                                 ((EXISTS(SELECT NRLISTA
                                            FROM LISTA
                                           WHERE ((CDLISTA = VT.CDCIA)
                                              OR  (CDLISTA = VT.CDCIA||VT.NRVOO||VT.CDTRECHO))
                                             AND (NRLISTA = P_NRLISTACIAVOO ))) OR ('T' = P_NRLISTACIAVOO))) LOOP
         /*SELECIONA O MANUAL DA COMPANHIA*/
          BEGIN
          SELECT CDMANCIA
            INTO V_CDMANCIA
            FROM MANUALCIA MA
           WHERE (CDFILIAL   = C_CIAVOO.CDFILIAL)
             AND (CDCIA      = C_CIAVOO.CDCIA)
             AND (IDTPMANUAL = 'M')
             AND (IDMANUALPROD = 'S' );
          EXCEPTION
            WHEN  NO_DATA_FOUND THEN
             V_CDMANCIA := NULL;
          END;

         BEGIN
              /*SELECIONA A CONFIGURACAO DE PASSAGEIROS DA SEMANA ANTERIOR*/

            SELECT CDFILIAL, CDCIA, NRVOO, CDTRECHO
              INTO V_CDFILIAL, V_CDCIA, V_NRVOO, V_CDTRECHO
              FROM PLANOVOO
             WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
               AND (CDCIA        = C_CIAVOO.CDCIA   )
               AND (NRVOO        = C_CIAVOO.NRVOO   )
               AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
               AND (DTHRPLAVOOPV = V_DTULTSEM       )
               AND ('G'          = P_IDGERCHECKLIST )
               AND (NOT EXISTS(SELECT CDFILIAL
                                 FROM PREVPAXPROD PP
                                WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                                  AND (CDCIA        = C_CIAVOO.CDCIA   )
                                  AND (NRVOO        = C_CIAVOO.NRVOO   )
                                  AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                                  AND (DTVOO        = C_PERIODO.DTDATA)));


            /*INSERE A CONFIGURACAO DE PASSAGEIROS DA SEMANA ANTERIOR NA TABELA DE PREVISAO PARA PRODUCAO*/

            INSERT INTO PREVPAXPROD
               (CDFILIAL, CDCIA, NRVOO, CDTRECHO, CDCLASSE, DTVOO, QTPREVPAXPROD)
               (SELECT CDFILIAL, CDCIA, NRVOO, CDTRECHO, CDCLASSE, C_PERIODO.DTDATA, NVL(QTTOTPAXAT,0) + NVL(QTPAXSBY,0)
                  FROM PREVISAOPAX
                 WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                   AND (CDCIA        = C_CIAVOO.CDCIA   )
                   AND (NRVOO        = C_CIAVOO.NRVOO   )
                   AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                   AND (DTHRPLAVOOPV = V_DTULTSEM       )
                   AND (NOT EXISTS(SELECT CDFILIAL
                                     FROM PREVPAXPROD PP
                                    WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                                      AND (CDCIA        = C_CIAVOO.CDCIA   )
                                      AND (NRVOO        = C_CIAVOO.NRVOO   )
                                      AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                                      AND (DTVOO        = C_PERIODO.DTDATA))));
             /*INSERE OSSERVICOS DO VOO*/
            INSERT INTO SERVPREVPAXPROD
               (CDFILIAL, CDCIA, NRVOO, CDTRECHO,
                CDSERVICO, CDCLASSE, DTVOO, QTSERVPREVPROD)
               (SELECT SP.CDFILIAL, SP.CDCIA, SP.NRVOO, SP.CDTRECHO,
                       SP.CDSERVICO, PR.CDCLASSE, C_PERIODO.DTDATA, SUM(SP.QTATUAPRODSP)
                 FROM SERVPLANO SP, PRODUTO PR
                WHERE (SP.CDFILIAL      = C_CIAVOO.CDFILIAL)
                  AND (SP.CDCIA         = C_CIAVOO.CDCIA    )
                  AND (SP.NRVOO         = C_CIAVOO.NRVOO    )
                  AND (SP.CDTRECHO      = C_CIAVOO.CDTRECHO )
                  AND (SP.DTHRPLAVOOPV  = V_DTULTSEM        )
                  AND (SP.CDSERVICO     = PR.CDPRODUTO)
                  AND NOT EXISTS(SELECT CDSERVICO
                                   FROM SERVPREVPAXPROD SPP
                                  WHERE (SPP.CDFILIAL  = SP.CDFILIAL  )
                                    AND (SPP.CDCIA     = SP.CDCIA     )
                                    AND (SPP.NRVOO     = SP.NRVOO     )
                                    AND (SPP.CDTRECHO  = SP.CDTRECHO  )
                                    AND (SPP.CDCLASSE  = PR.CDCLASSE  )
                                    AND (SPP.DTVOO     = C_PERIODO.DTDATA)
                                    AND (SPP.CDSERVICO = SP.CDSERVICO))
                GROUP BY SP.CDFILIAL, SP.CDCIA, SP.NRVOO,
                         SP.CDTRECHO, SP.CDSERVICO, PR.CDCLASSE);
               /*SELECIONA OS SERVICOS DO VOO*/
               FOR C_SERVICO2 IN (SELECT SP.CDFILIAL, SP.CDCIA, SP.NRVOO, SP.CDTRECHO, SP.CDCLASSE, SP.DTVOO, SP.CDSERVICO,
                                         NVL(PA.IDPROPSERV,'5') IDPROPSERV, NVL(PA.QTSERV, SP.QTSERVPREVPROD) QTSERV,
                                         NVL(PA.VRLOADSE,0) VRLOADSE, NVL(PA.NRPROPAX,0) NRPROPAX
                                    FROM SERVPREVPAXPROD SP, PAD_VOOPREFEQUI PA
                                   WHERE (SP.CDFILIAL      = C_CIAVOO.CDFILIAL )
                                     AND (SP.CDCIA         = C_CIAVOO.CDCIA    )
                                     AND (SP.NRVOO         = C_CIAVOO.NRVOO    )
                                     AND (SP.CDTRECHO      = C_CIAVOO.CDTRECHO )
                                     AND (SP.DTVOO         = C_PERIODO.DTDATA  )
                                     AND (SP.CDFILIAL      = PA.CDFILIAL    (+))
                                     AND (SP.CDCIA         = PA.CDCIA    (+))
                                     AND (SP.NRVOO         = PA.NRVOO       (+))
                                     AND (SP.CDTRECHO      = PA.CDTRECHO    (+))
                                     AND (SP.CDSERVICO     = PA.CDSERVICO   (+))) LOOP

                  /*SELECIONA TODOS OS SUBGRUPOS PERTENCENTES AO SERVICO SELECIONADO POR 'C_SERVICO2'*/
                  FOR C_SUBGRUPO2 IN (SELECT CP.CDPRODINSUMO CDSUBGRUPO
                                       FROM ESPEPROD EP, COMPPROD CP, PRODUTO PR
                                      WHERE (EP.CDFILIAL     = C_SERVICO2.CDFILIAL)
                                        AND (EP.CDCIA        = C_SERVICO2.CDCIA   )
                                        AND (EP.IDTPMANUAL   = 'M'                )
                                        AND (EP.CDMANCIA     = V_CDMANCIA         )
                                        AND (EP.CDPRODUTO    = CP.CDPRODUTO       )
                                        AND (EP.CDESPEPROD   = CP.CDESPEPROD      )
                                        AND (CP.CDPRODINSUMO = PR.CDPRODUTO       )
                                        AND (PR.IDPRODCOMISS = '2'                )
                                        AND (EP.CDPRODUTO    = C_SERVICO2.CDSERVICO)) LOOP
                      /*INSERE OS SUBGRUPOS SELECIONADOS POR 'C_SUBGRUPO2'*/
                      INSERT INTO SGPREVPAXPROD
                         (CDFILIAL, CDCIA, NRVOO, CDTRECHO,
                          CDCLASSE, DTVOO, CDSERVICO, CDSUBGRUPO,
                          IDPROPSGPROD, VRPROPAXPROD, VRLOADSGPROD, QTSUBGRUPPROD )
                      VALUES
                         (C_SERVICO2.CDFILIAL, C_SERVICO2.CDCIA, C_SERVICO2.NRVOO, C_SERVICO2.CDTRECHO,
                         C_SERVICO2.CDCLASSE, C_PERIODO.DTDATA, C_SERVICO2.CDSERVICO, C_SUBGRUPO2.CDSUBGRUPO,
                         C_SERVICO2.IDPROPSERV, C_SERVICO2.NRPROPAX, C_SERVICO2.VRLOADSE, C_SERVICO2.QTSERV);

                      IF C_SERVICO2.IDPROPSERV = '3' THEN

                         DELETE FROM PROPSGPROD
                         WHERE CDSERVICO  = C_SERVICO2.CDSERVICO
                           AND CDSUBGRUPO = C_SUBGRUPO2.CDSUBGRUPO;
                           
                         INSERT INTO PROPSGPROD
                            (CDSERVICO, CDSUBGRUPO, QTPAXINI, QTPAXFIN, QTSUBGRUPO)
                            (SELECT CDSERVICO, C_SUBGRUPO2.CDSUBGRUPO, QTPAXDE, QTPAXATE, QTSERVICO 
                               FROM PROPSERVICO
                               WHERE CDSERVICO = C_SERVICO2.CDSERVICO
                                 AND CDFILIAL   = C_SERVICO2.CDFILIAL
                                 AND CDCIA      = C_SERVICO2.CDCIA
                                 AND NRVOO      = C_SERVICO2.NRVOO
                                 AND CDTRECHO   = C_SERVICO2.CDTRECHO);

                      END IF;

                       /*INSERE AS RECEITAS*/
                       INSERT INTO RECEPREVPAXPROD
                          (CDFILIAL, CDCIA, NRVOO,
                           CDTRECHO, CDCLASSE, DTVOO,
                           CDSERVICO, CDSUBGRUPO, CDRECEITA, QTRECEITA)
                          (SELECT C_SERVICO2.CDFILIAL, C_SERVICO2.CDCIA, C_SERVICO2.NRVOO,
                                  C_SERVICO2.CDTRECHO, C_SERVICO2.CDCLASSE, C_PERIODO.DTDATA,
                                  C_SERVICO2.CDSERVICO, C_SUBGRUPO2.CDSUBGRUPO, CDPRODINSUMO, QTINSUMO
                             FROM COMPPROD CP, ESPEPROD EP,  PRODUTO PR, PRODUTO PR1
                            WHERE (EP.CDFILIAL      = C_SERVICO2.CDFILIAL   ) AND
                                  (EP.CDCIA         = C_SERVICO2.CDCIA      ) AND
                                  (EP.CDMANCIA      = V_CDMANCIA           ) AND
                                  (CP.CDPRODUTOAUX  = C_SERVICO2.CDSERVICO  ) AND
                                  (CP.CDPRODUTO     = C_SUBGRUPO2.CDSUBGRUPO) AND
                                  (CP.CDPRODINSUMO  = PR.CDPRODUTO         ) AND
                                  (PR.IDPRODCOMISS  = '3'                  ) AND
                                  (CP.CDPRODUTOAUX  = EP.CDPRODUTO         ) AND
                                  (CP.CDESPPRODAUX  = EP.CDESPEPROD        ) AND
                                  (PR1.CDPRODUTO    = EP.CDPRODUTO         ));

                  END LOOP;
               END LOOP;


         /*SE NAO HOUVER GERACAO DE VOO PARA A SEMANA ANTERIOR OU O PARAMETRO DE GERACAO DO CHECKLIST ESTIVER INDICANDO MANUTENCAO'M',
         OU ATULIZACAO DAS QUANTIDADES DE SERVICOS PELA ALTERACAO DA QUANTIDADE DE PAX NAS CLASSES 'C',
         A PROCEDURE IRA EXECUTAR A EXCECAO  */
         EXCEPTION
            WHEN NO_DATA_FOUND THEN


            IF (P_IDGERCHECKLIST = 'M') OR
               (P_IDGERCHECKLIST = 'C') THEN

               /*TESTA SE JA HOUVE GERACAO DE CHECKLIST PARA O VOO SELECIONADO*/

               SELECT COUNT(NRVOO)
                 INTO V_COUNTPREVPAX
                 FROM PREVPAXPROD
                WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                  AND (CDCIA        = C_CIAVOO.CDCIA   )
                  AND (NRVOO        = C_CIAVOO.NRVOO   )
                  AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                  AND (DTVOO        = C_PERIODO.DTDATA);

               /*SE HOUVE ...*/
               IF V_COUNTPREVPAX > 0 THEN
                  /*SELECIONA AS CLASSES E QUANTIDADES DE PAX, AS PRECONFIGURADAS(CONFIGVOO)
                  E AS GRAVADAS(PREVPAXPROD)*/
                  FOR C_CLASSEMANU IN(SELECT PP.CDFILIAL, PP.CDCIA, PP.NRVOO, PP.CDTRECHO,
                                         PP.CDCLASSE, PP.DTVOO, PP.QTPREVPAXPROD,
                                         CV.QTPAXVOO, CV.QTPAXSBYVOO
                                    FROM PREVPAXPROD PP, CONFIGVOO CV
                                   WHERE (PP.CDFILIAL     = C_CIAVOO.CDFILIAL)
                                     AND (PP.CDCIA        = C_CIAVOO.CDCIA   )
                                     AND (PP.NRVOO        = C_CIAVOO.NRVOO   )
                                     AND (PP.CDTRECHO     = C_CIAVOO.CDTRECHO)
                                     AND (PP.DTVOO        = C_PERIODO.DTDATA )
                                     AND (PP.CDFILIAL     = CV.CDFILIAL      )
                                     AND (PP.CDCIA        = CV.CDCIA         )
                                     AND (PP.NRVOO        = CV.NRVOO         )
                                     AND (PP.CDTRECHO     = CV.CDTRECHO      )
                                     AND (PP.CDCLASSE     = CV.CDCLASSE      ))LOOP

                     /*ALTERA PARA O MAXIMO A QUNTIDADE DE PAX*/
                     IF P_IDGERCHECKLIST = 'M' THEN
                        UPDATE PREVPAXPROD
                           SET QTPREVPAXPROD = NVL(C_CLASSEMANU.QTPAXVOO,0) + NVL(C_CLASSEMANU.QTPAXSBYVOO,0)
                         WHERE (CDFILIAL     = C_CLASSEMANU.CDFILIAL)
                           AND (CDCIA        = C_CLASSEMANU.CDCIA   )
                           AND (NRVOO        = C_CLASSEMANU.NRVOO   )
                           AND (CDTRECHO     = C_CLASSEMANU.CDTRECHO)
                           AND (CDCLASSE     = C_CLASSEMANU.CDCLASSE)
                           AND (DTVOO        = C_PERIODO.DTDATA);
                     END IF;

                    /*SE FOR CORRECAO DE QUANTIDADES PELA ALTERACAO DE CLASSE, SERA UTILIZADO A NOVA QUANTIDADE
                      INFORMADA, SE NAO A QUANTIDADE MAXIMA PRECONFIGURADA*/
                     IF P_IDGERCHECKLIST = 'C' THEN
                        V_QTPAXCLASSE := C_CLASSEMANU.QTPREVPAXPROD;
                     ELSE
                        V_QTPAXCLASSE := NVL(C_CLASSEMANU.QTPAXVOO,0);
                     END IF;

                     /*SELECIONA OS SERVICOS DA CLASSE*/
                     FOR C_SERVICOMANU IN (SELECT PA.CDFILIAL, PA.CDCIA, PA.NRVOO, PA.CDTRECHO,
                                              PA.CDSERVICO, PA.CDCLASSE, PA.CDCICLO,
                                              PA.QTSERV, PA.IDPROPSERV, PA.VRLOADSE, PA.NRPROPAX,
                                              PA.QTSESTBY
                                         FROM PAD_VOOPREFEQUI PA, SERVPREVPAXPROD SP
                                        WHERE (PA.CDFILIAL      = SP.CDFILIAL             )
                                          AND (PA.CDCIA         = SP.CDCIA                )
                                          AND (PA.NRVOO         = SP.NRVOO                )
                                          AND (PA.CDTRECHO      = SP.CDTRECHO             )
                                          AND (PA.CDCLASSE      = SP.CDCLASSE             )
                                          AND (PA.CDSERVICO     = SP.CDSERVICO            )
                                          AND (PA.IDSITSERVICO  = '1'                     )
                                          AND (PA.CDFILIAL      = C_CLASSEMANU.CDFILIAL   )
                                          AND (PA.CDCIA         = C_CLASSEMANU.CDCIA      )
                                          AND (PA.NRVOO         = C_CLASSEMANU.NRVOO      )
                                          AND (PA.CDTRECHO      = C_CLASSEMANU.CDTRECHO   )
                                          AND (PA.CDCLASSE      = C_CLASSEMANU.CDCLASSE   )) LOOP


                        V_VRPELOADSE := C_SERVICOMANU.VRLOADSE;
                        V_NRPROPAXSE := C_SERVICOMANU.NRPROPAX;
                        V_QTSERVFIX  := C_SERVICOMANU.QTSERV;

                        /*CALCULO DO STANDBY: SE NAO HOUVER STANDBY PARA A CLASSE CALCULAR PELO PRODUTO */
                        IF C_CLASSEMANU.QTPAXSBYVOO > 0 THEN
                           V_QTSTANDBY := C_CLASSEMANU.QTPAXSBYVOO;
                        ELSE
                           V_QTSTANDBY := C_SERVICOMANU.QTSESTBY;
                        END IF;

                        /*CALCULO DE QUANTIDADES*/
                        CALCULA_QUANTIDADES(C_SERVICOMANU.CDFILIAL, C_SERVICOMANU.CDCIA, C_SERVICOMANU.NRVOO,                                          C_SERVICOMANU.CDTRECHO,C_SERVICOMANU.CDSERVICO,
                                            C_SERVICOMANU.CDCLASSE, C_SERVICOMANU.CDCICLO, V_QTPAXCLASSE,
                                            C_SERVICOMANU.IDPROPSERV, V_VRPELOADSE,V_NRPROPAXSE,
                                            V_QTSERVFIX, V_QTSESOLICSP, V_QTSTANDBY); 

                        /*ALTERA A QUANTIDADE SE SERVICOS*/
                        UPDATE SERVPREVPAXPROD
                           SET QTSERVPREVPROD = V_QTSERVFIX + V_QTSTANDBY + V_QTSESOLICSP
                         WHERE (CDFILIAL  = C_SERVICOMANU.CDFILIAL )
                           AND (CDCIA     = C_SERVICOMANU.CDCIA    )
                           AND (NRVOO     = C_SERVICOMANU.NRVOO    )
                           AND (CDTRECHO  = C_SERVICOMANU.CDTRECHO )
                           AND (CDCLASSE  = C_SERVICOMANU.CDCLASSE )
                           AND (DTVOO     = C_PERIODO.DTDATA       )
                           AND (CDSERVICO = C_SERVICOMANU.CDSERVICO);
                     END LOOP;
                  END LOOP;
               END IF;
            -- END IF;
            ELSE

            /*SELECIONA AS CLASSES DO VOO*/
            FOR C_CLASSE IN(SELECT CDFILIAL, CDCIA, NRVOO, CDTRECHO, CDCLASSE, QTPAXVOO, QTPAXSBYVOO
                              FROM CONFIGVOO
                             WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                               AND (CDCIA        = C_CIAVOO.CDCIA   )
                               AND (NRVOO        = C_CIAVOO.NRVOO   )
                               AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                               AND (NOT EXISTS(SELECT CDFILIAL
                                                 FROM PREVPAXPROD PP
                                                WHERE (CDFILIAL     = C_CIAVOO.CDFILIAL)
                                                  AND (CDCIA        = C_CIAVOO.CDCIA   )
                                                  AND (NRVOO        = C_CIAVOO.NRVOO   )
                                                  AND (CDTRECHO     = C_CIAVOO.CDTRECHO)
                                                  AND (DTVOO        = C_PERIODO.DTDATA)))) LOOP
               /*INSERE OS VOOS E AS CLASSE NA TABELA*/
               INSERT INTO PREVPAXPROD
                  (CDFILIAL, CDCIA, NRVOO, CDTRECHO,
                   CDCLASSE, DTVOO, QTPREVPAXPROD)
               VALUES
                  (C_CLASSE.CDFILIAL, C_CLASSE.CDCIA, C_CLASSE.NRVOO, C_CLASSE.CDTRECHO,
                   C_CLASSE.CDCLASSE, C_PERIODO.DTDATA, NVL(C_CLASSE.QTPAXVOO,0) + NVL(C_CLASSE.QTPAXSBYVOO,0));

               /*SELECIONA P CICLO UNICO*/
               BEGIN
                  SELECT CDCICLO
                    INTO V_CICLO_UNICO
                    FROM CIAFILI
                   WHERE (CDFILIAL = C_CIAVOO.CDFILIAL)
                     AND (CDCIA    = C_CIAVOO.CDCIA);
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_CICLO_UNICO := NULL;
               END;

               /*SERVICOS DA CLASSE SELECIONADA PELO CURSOR 'C_CLASSE' */
               FOR C_SERVICO IN (SELECT PA.CDFILIAL, PA.CDCIA, PA.NRVOO, PA.CDTRECHO,
                                 PA.CDSERVICO, PA.CDCLASSE, PA.CDCICLO,
                                 PA.QTSERV, PA.IDPROPSERV, PA.VRLOADSE, PA.NRPROPAX,
                                 PA.QTSESTBY
                            FROM PAD_VOOPREFEQUI PA
                           WHERE (PA.IDSITSERVICO  = '1'               )
                             AND (PA.CDFILIAL      = C_CLASSE.CDFILIAL       )
                             AND (PA.CDCIA         = C_CLASSE.CDCIA          )
                             AND (PA.NRVOO         = C_CLASSE.NRVOO          )
                             AND (PA.CDTRECHO      = C_CLASSE.CDTRECHO       )
                             AND (PA.CDCLASSE      = C_CLASSE.CDCLASSE       )) LOOP

                  RETORNA_CICLO(C_SERVICO.CDFILIAL, C_SERVICO.CDCIA, C_SERVICO.NRVOO, C_SERVICO.CDTRECHO,
                                C_PERIODO.DTDATA, C_SERVICO.CDSERVICO, C_SERVICO.CDCLASSE ,V_CDCICLO );

                  IF (C_SERVICO.CDCICLO = V_CDCICLO) OR (C_SERVICO.CDCICLO = V_CICLO_UNICO ) THEN

                     V_VRPELOADSE := C_SERVICO.VRLOADSE;
                     V_NRPROPAXSE := C_SERVICO.NRPROPAX;
                     V_QTSERVFIX  := C_SERVICO.QTSERV;

                     /*CALCULO DO STANDBY: SE NAO HOUVER STANDBY PARA A CLASSE CALCULAR PELO PRODUTO */
                     IF C_CLASSE.QTPAXSBYVOO > 0 THEN
                        V_QTSTANDBY := C_CLASSE.QTPAXSBYVOO;
                     ELSE
                        V_QTSTANDBY := C_SERVICO.QTSESTBY;
                     END IF;
                     
                     /*CALCULO DE QUANTIDADES*/
                     CALCULA_QUANTIDADES(C_SERVICO.CDFILIAL, C_SERVICO.CDCIA, C_SERVICO.NRVOO, C_SERVICO.CDTRECHO,C_SERVICO.CDSERVICO,
                                         C_SERVICO.CDCLASSE, C_SERVICO.CDCICLO, NVL(C_CLASSE.QTPAXVOO,0),
                                         C_SERVICO.IDPROPSERV, V_VRPELOADSE,V_NRPROPAXSE,
                                         V_QTSERVFIX, V_QTSESOLICSP, V_QTSTANDBY );

                  /*INSERE OS SERVICOS NA TABELA SERPAXPROD*/
                     INSERT INTO SERVPREVPAXPROD
                        (CDFILIAL, CDCIA, NRVOO, CDTRECHO,
                         CDCLASSE, DTVOO, CDSERVICO,
                         QTSERVPREVPROD)
                     VALUES
                        (C_SERVICO.CDFILIAL, C_SERVICO.CDCIA, C_SERVICO.NRVOO, C_SERVICO.CDTRECHO,
                         C_SERVICO.CDCLASSE, C_PERIODO.DTDATA, C_SERVICO.CDSERVICO,
                         V_QTSERVFIX + V_QTSTANDBY + V_QTSESOLICSP);

                  /*SELECIONA TODOS OS SUBRUPOS PERTENCENTES AO SERVICO SLECIONADO PELO CURSOR  'C_SERVICO'*/
                     FOR C_SUBGRUPO IN (SELECT CP.CDPRODINSUMO CDSUBGRUPO
                                        FROM ESPEPROD EP, COMPPROD CP, PRODUTO PR
                                       WHERE (EP.CDFILIAL     = C_SERVICO.CDFILIAL)
                                         AND (EP.CDCIA        = C_SERVICO.CDCIA   )
                                         AND (EP.IDTPMANUAL   = 'M'               )
                                         AND (EP.CDMANCIA     = V_CDMANCIA        )
                                         AND (EP.CDPRODUTO    = CP.CDPRODUTO      )
                                         AND (EP.CDESPEPROD   = CP.CDESPEPROD     )
                                         AND (CP.CDPRODINSUMO = PR.CDPRODUTO      )
                                         AND (PR.IDPRODCOMISS = '2'               )
                                         AND (EP.CDPRODUTO    = C_SERVICO.CDSERVICO)) LOOP
                      /*INSERE OS SUBGRUPOS*/
                     INSERT INTO SGPREVPAXPROD
                        (CDFILIAL, CDCIA, NRVOO, CDTRECHO,
                         CDCLASSE, DTVOO, CDSERVICO, CDSUBGRUPO,
                         IDPROPSGPROD, VRPROPAXPROD, VRLOADSGPROD, QTSUBGRUPPROD)
                     VALUES
                        (C_SERVICO.CDFILIAL, C_SERVICO.CDCIA, C_SERVICO.NRVOO, C_SERVICO.CDTRECHO,
                         C_SERVICO.CDCLASSE, C_PERIODO.DTDATA, C_SERVICO.CDSERVICO, C_SUBGRUPO.CDSUBGRUPO,
                         C_SERVICO.IDPROPSERV, C_SERVICO.NRPROPAX, C_SERVICO.VRLOADSE, C_SERVICO.QTSERV);

                      IF C_SERVICO.IDPROPSERV = '3' THEN

                         DELETE FROM PROPSGPROD
                          WHERE CDSERVICO  = C_SERVICO.CDSERVICO
                            AND CDSUBGRUPO = C_SUBGRUPO.CDSUBGRUPO;
                           
                         INSERT INTO PROPSGPROD
                            (CDSERVICO, CDSUBGRUPO, QTPAXINI, QTPAXFIN, QTSUBGRUPO)
                            (SELECT CDSERVICO, C_SUBGRUPO.CDSUBGRUPO, QTPAXDE, QTPAXATE, QTSERVICO 
                               FROM PROPSERVICO
                              WHERE CDSERVICO = C_SERVICO.CDSERVICO
                                AND CDFILIAL   = C_SERVICO.CDFILIAL
                                AND CDCIA      = C_SERVICO.CDCIA
                                AND NRVOO      = C_SERVICO.NRVOO
                                AND CDTRECHO   = C_SERVICO.CDTRECHO);

                      END IF;

                       /*INSERE AS RECEITAS*/
                     INSERT INTO RECEPREVPAXPROD
                        (CDFILIAL, CDCIA, NRVOO,
                         CDTRECHO, CDCLASSE, DTVOO,
                         CDSERVICO, CDSUBGRUPO, CDRECEITA, QTRECEITA)
                        (SELECT C_SERVICO.CDFILIAL, C_SERVICO.CDCIA, C_SERVICO.NRVOO,
                                C_SERVICO.CDTRECHO, C_SERVICO.CDCLASSE, C_PERIODO.DTDATA,
                                C_SERVICO.CDSERVICO, C_SUBGRUPO.CDSUBGRUPO, CDPRODINSUMO, QTINSUMO
                           FROM COMPPROD CP, ESPEPROD EP,  PRODUTO PR, PRODUTO PR1
                          WHERE (EP.CDFILIAL      = C_SERVICO.CDFILIAL   ) AND
                                (EP.CDCIA         = C_SERVICO.CDCIA      ) AND
                                (EP.CDMANCIA      = V_CDMANCIA           ) AND
                                (CP.CDPRODUTOAUX  = C_SERVICO.CDSERVICO  ) AND
                                (CP.CDPRODUTO     = C_SUBGRUPO.CDSUBGRUPO) AND
                                (CP.CDPRODINSUMO  = PR.CDPRODUTO         ) AND
                                (PR.IDPRODCOMISS  = '3'                  ) AND
                                (CP.CDPRODUTOAUX  = EP.CDPRODUTO         ) AND
                                (CP.CDESPPRODAUX  = EP.CDESPEPROD        ) AND
                                (PR1.CDPRODUTO    = EP.CDPRODUTO         ));
                     END LOOP;
                  END IF;
               END LOOP;
            END LOOP;
            END IF;
         END;
      END LOOP;
   END LOOP;
   COMMIT;
END;
/