CREATE OR REPLACE PROCEDURE TROCA_EQUIP(
  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_QTTOTPAX     IN EQUIPCIA.QTTOTPAX%TYPE,
  P_QTPAXSBY_CLA IN EQUIPCIA.QTTOTPAX%TYPE,
  P_CDOPERADOR   IN SERVPLANO.CDUSRRESPSP%TYPE,
  P_CDSERVICO    IN SERVPLANO.CDSERVICO%TYPE,
  P_CDCICLO      IN CICLOCIA.CDCICLO%TYPE,
  P_IDPROPSE     IN PADVOOTRE.IDPROPSEVOO%TYPE,
  P_QTSERFIX     IN PADVOOTRE.QTSERVOO%TYPE    DEFAULT 0,
  P_VRLOADSE     IN PADVOOTRE.VRLOADSEVOO%TYPE DEFAULT 0,
  P_NRPROPAXSE   IN PADVOOTRE.NRPROPAXVOO%TYPE DEFAULT 0,
  P_QTSERVSTBY   IN SERVPLANO.QTSESBYPRSP%TYPE DEFAULT 0
) AS
  V_EXISTECICLO     NUMBER;
  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_CDAGRDEDE       CLASSE.CDCLASSE%TYPE;
  V_RETORNA_CDCICLO CICLOCIA.CDCICLO%TYPE;
  V_CICLO_UNICO     CICLOCIA.CDCICLO%TYPE;
  V_CDEQUIP         PLANOVOO.CDEQUIP%TYPE;
  V_SGPREFIXO       PLANOVOO.SGPREFIXO%TYPE;
  V_IDTIPMANUAL     SERVICOCIA.IDTIPMANUAL%TYPE;
  V_IDTPPRECO       SERVICOCIA.IDTPPRECO%TYPE;
  V_VRPRECORR_SBY   SERVPLANO.VRPRUSBYSP%TYPE;
  V_VRPREDOLAR_SBY  SERVPLANO.VRPRUSBYSP%TYPE;
  V_VRPRUSBYSP      SERVPLANO.VRPRUSBYSP%TYPE;
  V_QTSERFATSP      SERVPLANO.QTSERFATSP%TYPE;
  V_PENDENTE        NUMBER;
  V_SITUACAO_VOO    PLANOVOO.IDSITABASPV%TYPE;
  V_SITUACAO        PLANOVOO.IDSITABASPV%TYPE;
  V_DTHRABASTPV     PLANOVOO.DTHRABASTPV%TYPE;
  V_IDFATSE         SERVICOCIA.IDFATSE%TYPE;
  V_EXISTESERVICO   NUMBER;

BEGIN

   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;

/******************* TROCA DE EQUIPAMENTO *********************/
   SELECT SE.VRPESOSE,SE.IDTIPMANUAL,SE.IDTPPRECO,SE.IDFATSE
   INTO   V_VRPESOSE ,V_IDTIPMANUAL ,V_IDTPPRECO ,V_IDFATSE
   FROM   SERVICOCIA SE
   WHERE  SE.CDSERVICO = P_CDSERVICO;

   -- ============================================================ --
   -- BUSCA O AGRUPAMENTO DE CORPO (DELIVERY)                      --
   -- ============================================================ --
   SELECT 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;

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

   -- ============================================================ --
   -- 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
      SELECT CDCICLO
      INTO   V_CICLO_UNICO
      FROM   CIAFILI
      WHERE  CDFILIAL = P_CDFILIAL AND
             CDCIA    = P_CDCIA;
   END IF;

   -- ==================================================== --
   -- RETORNA CICLO VIGENTE NA DATA                        --
   -- ==================================================== --
   V_RETORNA_CDCICLO := P_CDCICLO;

   RETORNA_CICLO(P_CDFILIAL,P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, P_CDSERVICO,
                 P_CDCLASSE,V_RETORNA_CDCICLO);

   IF (P_CDCICLO = V_RETORNA_CDCICLO) OR (P_CDCICLO = V_CICLO_UNICO) THEN
   BEGIN
     V_QTSERVFIXSP := P_QTSERFIX;
     V_VRPELOADSP  := P_VRLOADSE;
     V_NRPROPAXSP  := P_NRPROPAXSE;

     SELECT NVL(MAX(NRITEMPLANSP)+1,1)
     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);

     -- ============================================================ --
     -- GARANTIR QUE PESO E O STAND-BY NAO ESTEJAM NULOS             --
     -- ============================================================ --
     V_QTSESBYPRSP := P_QTSERVSTBY;
     IF (V_VRPESOSE   IS NULL) THEN
       V_VRPESOSE  := 0;
     END IF;

     -- ============================================================ --
     -- UTILIZO O 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 := P_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,P_CDSERVICO,
                         P_CDCLASSE,P_CDCICLO, P_QTTOTPAX,
                         P_IDPROPSE,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 do preco do servico                       --
     -- ================================================= --

     CALCULA_PRECO('N',P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,P_CDSERVICO,
                   /*P_CDCLASSE,P_CDCICLO,*/P_DTHRPLAVOOPV,V_DTHRABASTPV,
                   P_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, P_CDSERVICO, P_DTHRPLAVOOPV,
                      V_QTSESBYPRSP,V_VRPRECORR_SBY,V_VRPREDOLAR_SBY,NULL);*/
        CALCULA_PRECO('N',P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,P_CDSERVICO,
                      /*P_CDCLASSE,P_CDCICLO,*/P_DTHRPLAVOOPV,V_DTHRABASTPV,
                      P_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                        --
     -- ================================================= --

     IF (V_VRPESOSE    IS NULL) THEN V_VRPESOSE   := 0; END IF;
     IF (V_QTSESBYPRSP IS NULL) THEN V_QTSESBYPRSP:= 0; END IF;
     IF (V_QTSERVFIXSP IS NULL) THEN V_QTSERVFIXSP:= 0; END IF;

     SELECT COUNT(*)
     INTO V_EXISTESERVICO
     FROM SERVPLANO
     WHERE
        (CDFILIAL     =  P_CDFILIAL) AND
        (CDCIA        =  P_CDCIA) AND
        (NRVOO        =  P_NRVOO) AND
        (CDTRECHO     =  P_CDTRECHO) AND
        (DTHRPLAVOOPV =  P_DTHRPLAVOOPV) AND
        (IDSITSERSP   <> '3') AND
        (CDSERVICO    =  P_CDSERVICO);

     IF V_EXISTESERVICO = 0 THEN
        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, DTHRCANCSESP,
            CDAGRDECO,   CDAGRDEDE,    IDSEREXTSP,  CDSERVICO,   VRPRUDOLSESP,
            VRPRURESESP, IDTIPSCBOSP,  VRLOADSESP,  NRPROPAXSP,
            VRPRUSBYSP,  IDTIPMANUALSP, IDTPMANUTSP,
            IDTPPRECOSP, IDFATSESP/*,CDCLASSE,CDCICLO*/)
          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,
            V_CDAGRDECO, /*V_CDAGRDEDE*/P_CDCLASSE, '2', P_CDSERVICO, V_VRPREDOLAR,
            V_VRPRECORRENTE, P_IDPROPSE, V_VRPELOADSP,V_NRPROPAXSP,
            V_VRPRUSBYSP,V_IDTIPMANUAL, '1C',
            V_IDTPPRECO,V_IDFATSE/*,P_CDCLASSE,P_CDCICLO*/);
        END IF;
     END IF;
   END;
   END IF;

/* 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;