CREATE OR REPLACE PROCEDURE GERA_PLANOVOOM_RETROBILL
(
   P_CDFILIAL IN VARCHAR2,
   P_NRRETROBILL IN NUMBER,
   P_DTHRPLAVOOPV IN DATE,
   P_CDOPERADOR IN VARCHAR2,
   P_NRORG IN NUMBER DEFAULT 1
)
AS
   V_CDFILIAL VARCHAR2(4);
   V_CDCIA VARCHAR2(4);
   V_IDSITRETROBILL VARCHAR2(1);
   V_NRVOORETROBILL VARCHAR2(5);
   V_CDTRECHO VARCHAR2(4);
   V_VRRETROBILL FLOAT;
   V_QTHRKDTCAR VARCHAR2(4);
   V_QTHRKDTDES VARCHAR2(4);
   V_CDEQUIP VARCHAR2(4);
   V_SGPREFIXO VARCHAR2(10);
   V_HRDECOLVOO VARCHAR2(4);
   V_HRPOUSOVOO VARCHAR2(4);
   V_IDTIPAJUSFAT VARCHAR2(1);
   V_DATAHORA DATE;
   V_STABASVOO VARCHAR2(1);
   V_QTHRDIFGMT NUMBER(2);
   V_PLANOVOO_JA_GERADO DATE;
   V_CDAGRDEL VARCHAR2(4);
   V_CDBASEORI VARCHAR2(4);
   V_CDBASEDES VARCHAR2(4);
BEGIN
   /* Busca dados do retrobill */
   SELECT MAX(R.CDFILIAL), MAX(R.CDCIA),  MAX(R.IDSITRETROBILL), MAX(V.NRVOO), MIN(VT.CDTRECHO), MAX(R.VRRETROBILL),
          MAX(CDEQUIP), MAX(V.SGPREFIXO), MAX(V.QTHRKDTCAR), MAX(V.QTHRKDTDES), MAX(R.DTHRPLAVOOPV), MAX(CF.CDAGRDELRETROBILL)
     INTO V_CDFILIAL, V_CDCIA, V_IDSITRETROBILL, V_NRVOORETROBILL, V_CDTRECHO, V_VRRETROBILL,
          V_CDEQUIP, V_SGPREFIXO, V_QTHRKDTCAR, V_QTHRKDTDES, V_PLANOVOO_JA_GERADO, V_CDAGRDEL
     FROM RETROBILL R
          INNER JOIN CIAFILI CF ON CF.CDFILIAL = R.CDFILIAL AND
                                   CF.CDCIA = R.CDCIA
          LEFT JOIN VOO V ON V.CDFILIAL = CF.CDFILIAL AND
                             V.CDCIA = CF.CDCIA AND
                             V.NRVOO = CF.NRVOORETROBILL AND
                             V.IDSITVOO = '1'
          LEFT JOIN VOOTRECHO VT ON VT.CDFILIAL = V.CDFILIAL AND
                                    VT.CDCIA = V.CDCIA AND
                                    VT.NRVOO = V.NRVOO AND
                                    VT.IDSITVOOTRE = '1'
    WHERE R.CDFILIAL = P_CDFILIAL
      AND R.NRRETROBILL = P_NRRETROBILL;

    SELECT MAX(NVL(VT.HRDECOLVOO, '0000')), MAX(NVL(VT.HRPOUSOVOO, '0000')), MAX(VT.IDTIPAJUSFAT),
           MAX(CDBASEORI), MAX(CDBASEDES)
      INTO V_HRDECOLVOO, V_HRPOUSOVOO, V_IDTIPAJUSFAT, V_CDBASEORI, V_CDBASEDES
      FROM VOOTRECHO VT
     WHERE VT.CDFILIAL = V_CDFILIAL
       AND VT.CDCIA = V_CDCIA
       AND VT.NRVOO = V_NRVOORETROBILL
       AND VT.CDTRECHO = V_CDTRECHO;

    V_STABASVOO := '1';

   /* Determina data de movimento do voo */
   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 = V_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;

   IF V_VRRETROBILL > 0.00 THEN
     /*Plano de voo ja gerado para retrobill informado.*/
     IF V_PLANOVOO_JA_GERADO IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00024' || 'APPERRMSGEND', True);
     END IF;

     /* Nao ha valor de cobranca a ser gerado
     IF (V_VRRETROBILL <= 0.00) THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00025' || 'APPERRMSGEND', True);
     END IF;*/

     /* Retrobill informado nao esta aprovado
     IF (NVL(V_IDSITRETROBILL, 'P') =  'P') THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00026' || 'APPERRMSGEND', True);
     END IF;  */

     /* Voo retrobill nao parametrizado para a companhia*/
     IF V_NRVOORETROBILL IS NULL THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00027' || 'APPERRMSGEND', True);
     END IF;
     
     /*Agrupamento de delivery do retrobill nao parametrizado para a companhia. */
     IF V_CDAGRDEL IS NULL THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00032' || 'APPERRMSGEND', True);
     END IF;

     /* Nao ha trecho ativo para o voo retrobill.*/
     IF V_CDTRECHO IS NULL THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00028' || 'APPERRMSGEND', True);
     END IF;
     /* Insere plano de voo */
     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
      (V_CDFILIAL, V_CDCIA, V_NRVOORETROBILL, V_CDTRECHO, P_DTHRPLAVOOPV, V_CDEQUIP, V_SGPREFIXO,
      V_HRDECOLVOO, V_HRPOUSOVOO, V_QTHRKDTCAR, V_QTHRKDTDES,
      V_DATAHORA, V_STABASVOO, SYSDATE,
      P_CDOPERADOR, '', '4', '1', V_CDBASEORI, V_CDBASEDES,
      P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE, P_NRORG, P_NRORG, P_CDOPERADOR, 'S');

     /* Insere previsao de passageiros no plano de voo */
     INSERT INTO PREVISAOPAXM
     (CDFILIAL,CDCIA, NRVOO, DTHRPLAVOOPV, CDTRECHO, CDCLASSE,
     QTTOTPAXAN, QTTOTPAXAT, QTPAXSBY, CDUSRRESPPP,
     NRORG, DTINCLUSAO, CDOPERINCLUSAO, DTULTATU,
     NRORGINCLUSAO, NRORGULTATU, CDOPERULTATU, IDATIVO)
     SELECT DISTINCT V_CDFILIAL, V_CDCIA, V_NRVOORETROBILL, P_DTHRPLAVOOPV, V_CDTRECHO, CDCLASSE,
            0.00, 1, 0, P_CDOPERADOR,
            P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE,
            P_NRORG, P_NRORG, P_CDOPERADOR, 'S'
       FROM ITRETROBILL
      WHERE NRRETROBILL = P_NRRETROBILL;

     /* Insere os servicos do retrobill no plano de voo, com o preco medio dos servicos */
     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, NRORGINCLUSAO, NRORGULTATU,
      DTINCLUSAO, DTULTATU,
      CDOPERINCLUSAO, CDOPERULTATU, IDATIVO)
      SELECT I.CDFILIAL, I.CDCIA, ROWNUM AS NRITEMPLANSP, V_NRVOORETROBILL, V_CDTRECHO, P_DTHRPLAVOOPV,
             I.NRSERVICOCIAFILI, I.CDCLASSE, I.CDCICLO,
             NULL, '1', '1', P_CDOPERADOR, I.QTSERVICO, 0.00,
             0.00, 0.00, 0.00, 0.00,
             I.QTSERVICO, I.QTSERVICO,
             I.QTSERVICO, NULL, SG.VRPESOSE, SYSDATE,
             NULL, NULL,
             V_CDAGRDEL,
             I.CDCLASSE,
             '1',
             CASE WHEN SCF.IDTIPMANUAL = '1' THEN 0.00 ELSE ROUND(I.VRFINALRETROBILL/I.QTSERVICO, 2) END,
             CASE WHEN SCF.IDTIPMANUAL = '2' THEN 0.00 ELSE ROUND(I.VRFINALRETROBILL/I.QTSERVICO, 2) END,
             '5', 100.00, 1,
             0.00, SCF.IDTIPMANUAL, '1A', '1', SCF.IDFATSE,
             P_NRORG, P_NRORG, P_NRORG,
             SYSDATE, SYSDATE,
             P_CDOPERADOR, P_CDOPERADOR, 'S'
        FROM ITRETROBILLCONS I
             INNER JOIN SERVICOCIAFILI SCF ON SCF.CDFILIAL = I.CDFILIAL AND
                                              SCF.CDCIA = I.CDCIA AND
                                              SCF.NRSERVICOCIAFILI = I.NRSERVICOCIAFILI
             INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = SCF.NRSERVICOGEN
       WHERE I.CDFILIAL = P_CDFILIAL
         AND I.NRRETROBILL = P_NRRETROBILL;
   END IF;

   UPDATE RETROBILL
      SET NRVOO = V_NRVOORETROBILL,
          CDTRECHO = V_CDTRECHO,
          DTHRPLAVOOPV = P_DTHRPLAVOOPV,
          IDSITRETROBILL = 'A',
          CDOPERULTATU = P_CDOPERADOR,
          DTULTATU = SYSDATE
    WHERE CDFILIAL = P_CDFILIAL
      AND NRRETROBILL = P_NRRETROBILL;
END;
/