CREATE OR REPLACE PROCEDURE SIMULA_GERAPLAVOO(
    P_CDFILIAL   IN VARCHAR2,
    P_CDCIA      IN VARCHAR,
    P_NRVOO      IN VARCHAR2,
    P_DATAINI    IN DATE,
    P_DATAFIN    IN DATE,
    P_CDOPERADOR IN VARCHAR2,
    P_NRORG      IN NUMBER,
    P_NRERROSIMULAPLAVOO OUT NUMBER )
AS
  V_NRLISTAERROS NUMBER;
BEGIN
  FOR CUR_DATA IN
  (SELECT DTDATA FROM DATA WHERE DTDATA BETWEEN P_DATAINI AND P_DATAFIN
  )
  LOOP
    FOR CUR_VOOS IN
    (SELECT V.SGPREFIXO,
            VT.CDFILIAL,
            CO.SGCIA,
            CO.CDTPCIA,
            VT.CDCIA,
            CO.SGCIA || '-' || VT.NRVOO AS CIAVOO, VT.NRORG, VT.NRVOO, VT.CDTRECHO, EQ.CDEQUIP, EQ.DSEQUIP, EQ.SGEQUIP,
            BO.SGBASE ||' - ' ||BD.SGBASE AS DSTRECHO, BO.SGBASE SGBASEORI, 
            BD.SGBASE SGBASEDES, 
            SUBSTR(TD.HRETD, 1, 2) ||':' ||SUBSTR(TD.HRETD,3,2) AS HRPRODVOO, 
            SUBSTR(TD.HRETD,1,2) ||':' ||SUBSTR(TD.HRETD,3,2) AS HRDECOLVOO, 
            CUR_DATA.DTDATA AS DTDECOLVOO, 
            SUBSTR(TD.HRETA,1,2) ||':' ||SUBSTR(TD.HRETA,3,2) AS HRPOUSOVOO
       FROM VOOTRECHO VT
            INNER JOIN VOO V ON V.CDFILIAL = VT.CDFILIAL AND
                                V.CDCIA = VT.CDCIA AND
                                V.NRVOO = VT.NRVOO
            INNER JOIN VOOTRECHODATA TD ON TD.CDFILIAL = VT.CDFILIAL AND                      
                                           TD.CDCIA = VT.CDCIA AND
                                           TD.NRVOO = VT.NRVOO AND
                                           TD.CDTRECHO = VT.CDTRECHO AND
                                           (CUR_DATA.DTDATA
                                            BETWEEN TD.DTINIVIGENCIA
                                            AND NVL(TD.DTFIMVIGENCIA, CUR_DATA.DTDATA))
            INNER JOIN COMPANHIA CO ON CO.CDCIA = VT.CDCIA
            INNER JOIN BASE BO ON BO.CDBASE = VT.CDBASEORI
            INNER JOIN BASE BD ON BD.CDBASE = VT.CDBASEDES
            LEFT JOIN CIAFILI CF ON CF.CDFILIAL = V.CDFILIAL AND
                                    CF.CDCIA = V.CDCIA            
           LEFT JOIN EQUIPAMENTO EQ ON EQ.CDEQUIP = TD.CDEQUIP
     WHERE VT.CDFILIAL = P_CDFILIAL        
       AND VOO_DECOLA_DATA(VT.CDFILIAL, VT.CDCIA, VT.NRVOO, VT.CDTRECHO, CUR_DATA.DTDATA) = 'S'
       AND NOT EXISTS 
           (SELECT 1
              FROM PLANOVOOM PV         
             WHERE PV.CDFILIAL = VT.CDFILIAL
               AND PV.NRVOO = VT.NRVOO
               AND PV.CDCIA = VT.CDCIA
               AND PV.CDTRECHO = VT.CDTRECHO
               AND PV.DTHRPLAVOOPV = CUR_DATA.DTDATA)   
       AND (VERIF_VALOR_LISTA(VT.CDCIA, P_CDCIA) IS NOT NULL)
       AND (VERIF_VALOR_LISTA(VT.CDCIA
                              ||'|'
                              ||VT.NRVOO
                              ||'|'
                              ||VT.CDTRECHO, P_NRVOO) IS NOT NULL)

    )
    LOOP
      V_NRLISTAERROS := NULL;
      BEGIN
        /*Inclui equipamento*/
        DELETE FROM PLANOVOOEQUIP 
         WHERE CDFILIAL = P_CDFILIAL
           AND CDCIA = CUR_VOOS.CDCIA
           AND NRVOO = CUR_VOOS.NRVOO
           AND CDTRECHO = CUR_VOOS.CDTRECHO
           AND DTHRPLAVOOPV = CUR_DATA.DTDATA;

        INSERT INTO PLANOVOOEQUIP
        (CDFILIAL, CDCIA, NRVOO, 
         CDTRECHO, DTHRPLAVOOPV, CDEQUIP, 
         NRORG, NRORGULTATU, NRORGINCLUSAO,
         DTINCLUSAO, DTULTATU,
         CDOPERINCLUSAO, CDOPERULTATU, IDATIVO)
        VALUES
        (P_CDFILIAL, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, 
         CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, CUR_VOOS.CDEQUIP,
         P_NRORG,P_NRORG,P_NRORG,
         SYSDATE, SYSDATE,
         P_CDOPERADOR, P_CDOPERADOR, 'S');        

        /*Gera previsao maxima de passageiros*/
        GERA_PREVISAO_PAXM(P_CDFILIAL, NULL, NULL, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, '1', 'P', 100, NULL, NULL, CUR_VOOS.CDEQUIP, CUR_VOOS.SGPREFIXO, P_CDOPERADOR, P_NRORG);
        GERA_PREVISAO_PAXM(P_CDFILIAL, NULL, NULL, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, '3', 'P', 100, NULL, NULL, CUR_VOOS.CDEQUIP, CUR_VOOS.SGPREFIXO, P_CDOPERADOR, P_NRORG);
        /*Gera plano de voo*/
        GERA_PLANO_VOOM(P_CDFILIAL, P_CDOPERADOR, '3', CUR_VOOS.CDFILIAL, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, P_NRORG, V_NRLISTAERROS);
      EXCEPTION
      WHEN OTHERS THEN
        INSERE_ERROSSIMULAPLAVOO(P_CDFILIAL, P_NRERROSIMULAPLAVOO, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, SQLERRM, P_CDOPERADOR, P_NRORG);
      END;
      /*Grava a lista de erros do plano de voo*/
      FOR CUR_ERROS IN
      (SELECT DISTINCT DSERRO
      FROM LISTAERROS
      WHERE CDFILIAL = P_CDFILIAL
      AND NRLISTA    = V_NRLISTAERROS
      )
      LOOP
        /*Grava a lista de erros do plano de voo*/
        INSERE_ERROSSIMULAPLAVOO(P_CDFILIAL, P_NRERROSIMULAPLAVOO, CUR_VOOS.CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, CUR_DATA.DTDATA, CUR_ERROS.DSERRO, P_CDOPERADOR, P_NRORG);
      END LOOP;
      /*Limpa as geracoes de plano de voo*/
      ROLLBACK;
    END LOOP;
  END LOOP;
END;
/