CREATE OR REPLACE PROCEDURE TROCA_EQUIPM(
  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_DTHRABASTPV  IN PLANOVOOM.DTHRABASTPV%TYPE,
  P_CDOPERADOR   IN SERVPLANOM.CDUSRRESPSP%TYPE,
  P_CDEQUIP IN VARCHAR2,
  P_NRORG IN NUMBER DEFAULT 1
) AS
  V_EXISTECICLO     NUMBER;
  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_CDAGRDEDE       CLASSE.CDCLASSE%TYPE;
  V_RETORNA_CDCICLO CICLOCIA.CDCICLO%TYPE;
  V_CICLO_UNICO     CICLOCIA.CDCICLO%TYPE;  
  V_SGPREFIXO       PLANOVOOM.SGPREFIXO%TYPE;
  V_IDTIPMANUAL     SERVICOCIA.IDTIPMANUAL%TYPE;
  V_IDTPPRECO       SERVICOCIA.IDTPPRECO%TYPE;
  V_VRPRECORR_SBY   SERVPLANOM.VRPRUSBYSP%TYPE;
  V_VRPREDOLAR_SBY  SERVPLANOM.VRPRUSBYSP%TYPE;
  V_VRPRUSBYSP      SERVPLANOM.VRPRUSBYSP%TYPE;
  V_QTSERFATSP      SERVPLANOM.QTSERFATSP%TYPE;
  V_PENDENTE        NUMBER;
  V_SITUACAO_VOO    PLANOVOOM.IDSITABASPV%TYPE;
  V_SITUACAO        PLANOVOOM.IDSITABASPV%TYPE;
  V_DTHRABASTPV     PLANOVOOM.DTHRABASTPV%TYPE;
  V_IDFATSE         SERVICOCIA.IDFATSE%TYPE;
  V_NRDECITOTANF    PARAMGERAL.NRDECITOTANF%TYPE;
  V_EXISTESERVICO   NUMBER;
  V_QTSESBYPRSP_INC FLOAT;  
  V_CONTADOR NUMBER;
  V_IDCALCPROPDLCOM VARCHAR2(1);
  V_VRPRECORRENTE_AUX FLOAT;
  V_TESTE NUMBER;
  V_IDPROPORCAO      VARCHAR2(1);
  V_QTPROPORCAO      FLOAT;
  V_ITEM_TAB_PRECO NUMBER;
  V_NRTABPRECO VARCHAR2(3);
  V_NRSEQPRECO NUMBER;
  V_QTTOTPAX_PAXSBY FLOAT;
  V_QTSERVFATU FLOAT;
BEGIN

   SELECT NVL(MAX(IDSITABASPV),0), MAX(DTHRABASTPV)
   INTO   V_SITUACAO, V_DTHRABASTPV
   FROM   PLANOVOOM
   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
     /*'Manutecao nao permitida. O voo esta cancelado.', false);*/
     RAISE_APPLICATION_ERROR( -20502, 'APPERRMSGSTR' || 'APP-00015' || 'APPERRMSGEND', FALSE);    
   END IF;     

   SELECT COUNT(1)
     INTO V_CONTADOR
     FROM PADEQUIPM P
          INNER JOIN SERVICOCIAFILI S ON S.CDFILIAL = P.CDFILIAL AND
                                         S.CDCIA = P.CDCIA AND
                                         S.NRSERVICOCIAFILI = P.NRSERVICOCIAFILI
          INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = S.NRSERVICOGEN
          INNER JOIN (SELECT CDCLASSE, QTTOTPAXAT, QTPAXSBY
                        FROM   PREVISAOPAXM
                        WHERE  (CDFILIAL     = P_CDFILIAL) AND
                               (CDCIA        = P_CDCIA   ) AND
                               (NRVOO        = P_NRVOO   ) AND
                               (CDTRECHO     = P_CDTRECHO) AND                               
                               (DTHRPLAVOOPV = P_DTHRPLAVOOPV)) PPX ON PPX.CDCLASSE = P.CDCLASSE
          LEFT JOIN CIAFILI CI ON CI.CDFILIAL = P.CDFILIAL AND
                                  CI.CDCIA = P.CDCIA 
    WHERE P.CDFILIAL = P_CDFILIAL
      AND P.CDCIA = P_CDCIA
      AND P.CDEQUIP = P_CDEQUIP
      AND P.NRSEQPADEQUIPMDATA = ITEM_VIGENTE_PADEQUIPM(P_CDFILIAL, P_CDCIA, P_CDEQUIP, P_DTHRPLAVOOPV)
      AND S.IDSITSERVICO = '1'
      AND SG.IDSERVICO IN ('2','3')      
      AND (P.CDCICLO = F_RETORNA_CICLOM(P.CDFILIAL, P.CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, P.NRSERVICOCIAFILI, P.CDCLASSE) OR
          (P.CDCICLO = CI.CDCICLO));

   IF NVL(V_CONTADOR, 0) = 0 THEN
      /* Alteracao nao permitida. Este equipamento nao possui configuracao de handling / miscelanea.  */
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00047' || 'APPERRMSGEND' , TRUE);
   END IF;

   UPDATE SERVPLANOM
      SET IDSITSERSP   = '3',
          DTHRCANCSESP = SYSDATE,
          DTULTATU = SYSDATE,
          CDOPERULTATU = P_CDOPERADOR,
          NRORGULTATU = P_NRORG
    WHERE  (CDFILIAL     = P_CDFILIAL    ) AND
           (CDCIA        = P_CDCIA       ) AND
           (NRVOO        = P_NRVOO       ) AND
           (CDTRECHO     = P_CDTRECHO    ) AND
           (DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
           (IDSITSERSP   = '1'          ) AND
           (NRDELIVERY IS NULL          ) AND
           (EXISTS (SELECT 1
                      FROM SERVICOCIAFILI S
                           INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = S.NRSERVICOGEN
                     WHERE S.CDFILIAL = SERVPLANOM.CDFILIAL
                       AND S.CDCIA = SERVPLANOM.CDCIA
                       AND S.NRSERVICOCIAFILI = SERVPLANOM.NRSERVICOCIAFILI
                       AND SG.IDSERVICO IN ('2','3') ));

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

   SELECT NVL(NRDECITOTANF, 2)
     INTO V_NRDECITOTANF
     FROM PARAMGERAL
     WHERE
        (CDFILIAL = P_CDFILIAL);
   
   FOR C_SERVICOS_MISC_HAND IN
   (SELECT P.NRSERVICOCIAFILI, P.CDCLASSE,
          S.IDTIPMANUAL, S.IDFATSE,
          NVL(SC.VRPESOSE, 0) AS VRPESOSE, P.QTSERVOO AS QTSERV,
          S.IDTPPRECO,
          P.CDCICLO , S.DSSERVICOCIAFILI AS DSSERVICO,          
          CL.SGCLASSE, CI.IDCICLO,
          PPX.QTTOTPAXAT AS QTTOTPAX, PPX.QTPAXSBY
     FROM PADEQUIPM P
          INNER JOIN SERVICOCIAFILI S ON S.CDFILIAL = P.CDFILIAL AND
                                         S.CDCIA = P.CDCIA AND
                                         S.NRSERVICOCIAFILI = P.NRSERVICOCIAFILI
          INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = S.NRSERVICOGEN
          INNER JOIN SERVCICLO SC ON SC.CDFILIAL = S.CDFILIAL AND
                                     SC.CDCIA = S.CDCIA AND
                                     SC.NRSERVICOCIAFILI = S.NRSERVICOCIAFILI AND
                                     SC.CDCLASSE = P.CDCLASSE AND
                                     SC.CDCICLO = P.CDCICLO
          INNER JOIN CLASSE CL ON CL.CDCLASSE = P.CDCLASSE
          INNER JOIN CICLO CI ON CI.CDCICLO = P.CDCICLO                                     
          INNER JOIN (SELECT CDCLASSE, QTTOTPAXAT, QTPAXSBY
                        FROM   PREVISAOPAXM
                        WHERE  (CDFILIAL     = P_CDFILIAL) AND
                               (CDCIA        = P_CDCIA   ) AND
                               (NRVOO        = P_NRVOO   ) AND
                               (CDTRECHO     = P_CDTRECHO) AND                               
                               (DTHRPLAVOOPV = P_DTHRPLAVOOPV)) PPX ON PPX.CDCLASSE = P.CDCLASSE
          LEFT JOIN CIAFILI CI ON CI.CDFILIAL = P.CDFILIAL AND
                                  CI.CDCIA = P.CDCIA 
    WHERE P.CDFILIAL = P_CDFILIAL
      AND P.CDCIA = P_CDCIA
      AND P.CDEQUIP = P_CDEQUIP
      AND S.IDSITSERVICO = '1'
      AND SG.IDSERVICO IN ('2','3')      
      AND (P.CDCICLO = F_RETORNA_CICLOM(P.CDFILIAL, P.CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, P.NRSERVICOCIAFILI, P.CDCLASSE) OR
          (P.CDCICLO = CI.CDCICLO))
    ORDER BY NRSERVICOCIAFILI    
       
   ) LOOP     
      
      BEGIN
        SELECT NVL(MAX(NRITEMPLANSP)+1,1)
        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);

        -- ============================================================ --
        -- GARANTIR QUE PESO E O STAND-BY NAO ESTEJAM NULOS             --
        -- ============================================================ --
        
        V_QTSESBYPRSP := NVL(C_SERVICOS_MISC_HAND.QTPAXSBY, 0);
        

        -- =========================================================== --
         -- Calculo das quantidades                                     --
         -- =========================================================== --
         V_IDPROPORCAO := NULL;
         V_QTSERVFIXSP := C_SERVICOS_MISC_HAND.QTSERV;
         V_NRPROPAXSP := NULL;
         V_VRPELOADSP := NULL;

         CALCULA_QUANTIDADESM(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,C_SERVICOS_MISC_HAND.NRSERVICOCIAFILI,
                            C_SERVICOS_MISC_HAND.CDCLASSE,C_SERVICOS_MISC_HAND.CDCICLO,V_DTHRABASTPV, C_SERVICOS_MISC_HAND.QTTOTPAX,
                            V_IDPROPORCAO, V_QTSESOLICSP, V_QTSESBYPRSP,
                            V_NRPROPAXSP, V_VRPELOADSP, V_QTSERVFIXSP, V_QTSERFATSP);

         V_QTTOTPAX_PAXSBY := CASE WHEN V_IDCALCPROPDLCOM = 'S' THEN C_SERVICOS_MISC_HAND.QTTOTPAX+V_QTSESBYPRSP 
                                   ELSE C_SERVICOS_MISC_HAND.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,C_SERVICOS_MISC_HAND.NRSERVICOCIAFILI,C_SERVICOS_MISC_HAND.CDCLASSE, C_SERVICOS_MISC_HAND.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;

        V_CDAGRDECO := F_RETORNA_AGRUPAMENTO(P_CDFILIAL,P_CDCIA,P_NRVOO,P_CDTRECHO,C_SERVICOS_MISC_HAND.CDCLASSE,
                            P_CDEQUIP,V_SGPREFIXO);

        -- =========================================================== --
        -- 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;

        IF (V_QTSERVFIXSP+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,
             C_SERVICOS_MISC_HAND.NRSERVICOCIAFILI, C_SERVICOS_MISC_HAND.CDCLASSE,C_SERVICOS_MISC_HAND.CDCICLO,
             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, C_SERVICOS_MISC_HAND.VRPESOSE, SYSDATE,
             NULL, NULL,  V_CDAGRDECO, C_SERVICOS_MISC_HAND.CDCLASSE,
             '1', V_VRPREDOLAR, V_VRPRECORRENTE_AUX,
             V_IDPROPORCAO, V_VRPELOADSP, V_NRPROPAXSP,
             V_VRPRUSBYSP,C_SERVICOS_MISC_HAND.IDTIPMANUAL, '1A',C_SERVICOS_MISC_HAND.IDTPPRECO,C_SERVICOS_MISC_HAND.IDFATSE,
             P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE, P_NRORG, P_NRORG, P_CDOPERADOR, 'S');

         END IF;       
      END;      
   END LOOP;

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