CREATE OR REPLACE PROCEDURE COPIA_VIGENCIA_VOO
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOOORI IN VARCHAR2,
P_CDTRECHOORI IN VARCHAR2,
P_NRSEQVOOTRECHODATA IN NUMBER,
P_VOOSDEST IN VARCHAR2,
P_DTINI IN DATE,
P_DTFIN IN DATE,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER,
P_NRLISTAERROS OUT NUMBER,
P_SOBRESCREVE IN VARCHAR2 DEFAULT 'N'
)
AS
V_DTINIVIGENCIA DATE;
V_DTFIMVIGENCIA DATE;
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_HRETA VARCHAR2(4);
V_HRKRT VARCHAR2(4);
V_HRKDT VARCHAR2(4);
V_HRETD VARCHAR2(4);
V_CDEQUIP VARCHAR2(4);
V_ERRO_VIGENCIA VARCHAR2(1);
V_SEQ VARCHAR2(20);
BEGIN
   SELECT MAX(DTINIVIGENCIA), MAX(DTFIMVIGENCIA),
          MAX(IDVOODOM), MAX(IDVOOSEG), MAX(IDVOOTER),
          MAX(IDVOOQUA), MAX(IDVOOQUI), MAX(IDVOOSEX),
          MAX(IDVOOSAB), MAX(HRETA), MAX(HRKRT), MAX(HRKDT),
          MAX(HRETD), MAX(CDEQUIP)
     INTO V_DTINIVIGENCIA,V_DTFIMVIGENCIA,
          V_IDVOODOM,V_IDVOOSEG,V_IDVOOTER,
          V_IDVOOQUA,V_IDVOOQUI,V_IDVOOSEX,
          V_IDVOOSAB,V_HRETA,V_HRKRT,V_HRKDT,
          V_HRETD,V_CDEQUIP
     FROM VOOTRECHODATA
    WHERE CDFILIAL = P_CDFILIAL
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOOORI
      AND CDTRECHO = P_CDTRECHOORI
      AND NRSEQVOOTRECHODATA = P_NRSEQVOOTRECHODATA;
   
   FOR CUR_VOOS IN
   (
      SELECT C.SGCIA||'-'||V.NRVOO||' '||BO.SGBASE||'-'||BD.SGBASE AS VOO, V.NRVOO, V.CDTRECHO
        FROM VOOTRECHO V
             INNER JOIN COMPANHIA C ON C.CDCIA = V.CDCIA
             INNER JOIN BASE BO ON BO.CDBASE = V.CDBASEORI
             INNER JOIN BASE BD ON BD.CDBASE = V.CDBASEDES
       WHERE V.CDFILIAL = P_CDFILIAL
         AND V.CDCIA = P_CDCIA
         AND VERIF_VALOR_LISTA(NRVOO||'|'||CDTRECHO, P_VOOSDEST) IS NOT NULL
   ) LOOP
      V_ERRO_VIGENCIA := 'N';

      /*Se vigencia for igual a origem, nao permite sobrescrever a vigencia*/
      IF CUR_VOOS.NRVOO = P_NRVOOORI AND
         CUR_VOOS.CDTRECHO = P_CDTRECHOORI AND 
         P_DTINI = V_DTINIVIGENCIA AND
         (P_DTFIN = V_DTFIMVIGENCIA OR (P_DTFIN IS NULL AND  V_DTFIMVIGENCIA IS NULL)) AND
         P_SOBRESCREVE = 'S' THEN
         CONTINUE;
      END IF;

      FOR CUR_VIGDEST IN
      (
      SELECT V.NRSEQVOOTRECHODATA, V.DTINIVIGENCIA, V.DTFIMVIGENCIA
        FROM VOOTRECHODATA V
       WHERE V.CDFILIAL = P_CDFILIAL
         AND V.CDCIA = P_CDCIA
         AND V.NRVOO = CUR_VOOS.NRVOO
         AND V.CDTRECHO = CUR_VOOS.CDTRECHO
         AND ( (V.DTINIVIGENCIA BETWEEN P_DTINI AND P_DTFIN)
               OR (V.DTFIMVIGENCIA BETWEEN P_DTINI AND P_DTFIN)
               OR (P_DTINI BETWEEN DTINIVIGENCIA AND DTFIMVIGENCIA)
               OR (P_DTFIN BETWEEN DTINIVIGENCIA AND DTFIMVIGENCIA)
               OR (P_DTFIN IS NULL AND V.DTINIVIGENCIA >= P_DTINI)
               OR (V.DTFIMVIGENCIA IS NULL AND (P_DTFIN IS NULL OR  P_DTINI >= V.DTINIVIGENCIA) ) )
      ) LOOP
         IF P_SOBRESCREVE = 'S' THEN
            DELETE FROM VOOTRECHODATADIA
             WHERE CDFILIAL = P_CDFILIAL
               AND CDCIA = P_CDCIA
               AND NRVOO = CUR_VOOS.NRVOO
               AND CDTRECHO = CUR_VOOS.CDTRECHO
               AND NRSEQVOOTRECHODATA = CUR_VIGDEST.NRSEQVOOTRECHODATA;

            DELETE FROM VOOTRECHODATA
             WHERE CDFILIAL = P_CDFILIAL
               AND CDCIA = P_CDCIA
               AND NRVOO = CUR_VOOS.NRVOO
               AND CDTRECHO = CUR_VOOS.CDTRECHO
               AND NRSEQVOOTRECHODATA = CUR_VIGDEST.NRSEQVOOTRECHODATA;
         ELSE
           /*Vigencia informada para o voo $param[0] sobrepoe a vigencia de $param[1] ate $param[2] */
           INSERE_LISTA_ERROS(P_CDFILIAL, 
                              'APPERRMSGSTR' || 'APP-00154' || '$$' ||
                              CUR_VOOS.VOO || '$$' ||
                              TO_CHAR(CUR_VIGDEST.DTINIVIGENCIA, 'DD/MM/YYYY') || '$$' ||
                              CASE WHEN CUR_VIGDEST.DTFIMVIGENCIA IS NULL THEN '' ELSE TO_CHAR(CUR_VIGDEST.DTFIMVIGENCIA, 'DD/MM/YYYY') END
                              || 'APPERRMSGEND',
                              P_CDOPERADOR, P_NRORG, P_NRLISTAERROS);
           V_ERRO_VIGENCIA := 'S';
         END IF;
      END LOOP;
      
      IF V_ERRO_VIGENCIA = 'S' THEN
         CONTINUE;
      END IF;

      NOVO_CODIGO_NT('VOOTRECHODATA', V_SEQ);


      INSERT INTO VOOTRECHODATA
      (
      CDFILIAL, CDCIA, NRVOO, CDTRECHO, NRSEQVOOTRECHODATA, 
      DTINIVIGENCIA, DTFIMVIGENCIA,
      IDVOODOM,IDVOOSEG,IDVOOTER,
      IDVOOQUA,IDVOOQUI,IDVOOSEX,
      IDVOOSAB,HRETA,HRKRT,HRKDT,HRETD, CDEQUIP,
      NRORG,DTINCLUSAO,CDOPERINCLUSAO,
      DTULTATU,NRORGINCLUSAO,NRORGULTATU,
      CDOPERULTATU,IDATIVO
      )
      VALUES
      (
      P_CDFILIAL, P_CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, TO_NUMBER(V_SEQ),
      P_DTINI, P_DTFIN,
      V_IDVOODOM,V_IDVOOSEG,V_IDVOOTER,
      V_IDVOOQUA,V_IDVOOQUI,V_IDVOOSEX,
      V_IDVOOSAB,V_HRETA,V_HRKRT,V_HRKDT,V_HRETD, V_CDEQUIP,
      P_NRORG,SYSDATE,P_CDOPERADOR,
      SYSDATE,P_NRORG,P_NRORG,
      P_CDOPERADOR,'S'
      );

      /*Copia a vigencia por dia*/
      IF P_DTINI = V_DTINIVIGENCIA AND
         (P_DTFIN = V_DTFIMVIGENCIA OR (P_DTFIN IS NULL AND  V_DTFIMVIGENCIA IS NULL)) THEN
        FOR CUR_DIAS IN
        (SELECT DTDIA
           FROM VOOTRECHODATADIA
          WHERE CDFILIAL = P_CDFILIAL
            AND CDCIA = P_CDCIA
            AND NRVOO = P_NRVOOORI
            AND CDTRECHO = P_CDTRECHOORI
            AND NRSEQVOOTRECHODATA = P_NRSEQVOOTRECHODATA) LOOP
        
          INSERT INTO VOOTRECHODATADIA
          (CDFILIAL, CDCIA,NRVOO,CDTRECHO,NRSEQVOOTRECHODATA, DTDIA,
           NRORG,DTINCLUSAO,CDOPERINCLUSAO,DTULTATU,NRORGINCLUSAO,NRORGULTATU,CDOPERULTATU,IDATIVO)
          VALUES
          (P_CDFILIAL, P_CDCIA, CUR_VOOS.NRVOO, CUR_VOOS.CDTRECHO, TO_NUMBER(V_SEQ), CUR_DIAS.DTDIA,
           P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE, P_NRORG, P_NRORG, P_CDOPERADOR, 'S');

        END LOOP;
      END IF;

   END LOOP;
END;
/