CREATE OR REPLACE PROCEDURE RETORNA_CICLOM( 
  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_NRSERVICOCIAFILI   IN     SERVICOCIAFILI.NRSERVICOCIAFILI%TYPE, 
  P_CDCLASSE           IN     CLASSE.CDCLASSE%TYPE, 
  P_CDCICLO_GER        IN OUT CICLOCIA.CDCICLO%TYPE 
) 
 
AS 
 
  V_EXISTECICLO   NUMBER; 
  V_EXISTECIA     NUMBER; 
  V_CDCICLO       CICLOCIA.CDCICLO%TYPE; 
  V_CDCICLO_UNICO CICLOCIA.CDCICLO%TYPE; 
  V_SGCIA         COMPANHIA.SGCIA%TYPE; 
 
BEGIN 
  /* VERIFICA CICLO UNICO */
  SELECT COUNT(CDCIA) 
    INTO   V_EXISTECIA 
    FROM   CIAFILI 
    WHERE  CDFILIAL = P_CDFILIAL AND 
           CDCIA    = P_CDCIA; 
  IF (V_EXISTECIA <> 0) THEN 
    SELECT CO.SGCIA,CF.CDCICLO 
    INTO   V_SGCIA, V_CDCICLO_UNICO 
    FROM   CIAFILI CF ,COMPANHIA CO 
    WHERE  CF.CDFILIAL = P_CDFILIAL AND 
           CF.CDCIA    = P_CDCIA    AND 
           CF.CDCIA    = CO.CDCIA; 
  END IF; 
  IF V_CDCICLO_UNICO = P_CDCICLO_GER THEN
    RETURN;
  END IF;

  P_CDCICLO_GER := NULL;
  
  SELECT COUNT(CDCICLO) 
  INTO   V_EXISTECICLO 
  FROM   FRCICLOSERM 
  WHERE (CDFILIAL   = P_CDFILIAL    ) AND 
        (CDCIA      = P_CDCIA       ) AND 
        (NRVOO      = P_NRVOO       ) AND 
        (CDTRECHO   = P_CDTRECHO    ) AND 
        (NRSERVICOCIAFILI  = P_NRSERVICOCIAFILI   ) AND 
        (CDCLASSE = P_CDCLASSE) AND
        (DTCICLOSER = P_DTHRPLAVOOPV); 
 
  IF (V_EXISTECICLO <> 0) THEN
    SELECT MAX(CDCICLO) 
      INTO P_CDCICLO_GER
      FROM FRCICLOSERM 
      WHERE (CDFILIAL   = P_CDFILIAL    ) AND 
            (CDCIA      = P_CDCIA       ) AND 
            (NRVOO      = P_NRVOO       ) AND 
            (CDTRECHO   = P_CDTRECHO    ) AND 
            (NRSERVICOCIAFILI  = P_NRSERVICOCIAFILI   ) AND 
            (CDCLASSE = P_CDCLASSE) AND
            (DTCICLOSER = P_DTHRPLAVOOPV);
  END IF; 
 
   
  IF (V_EXISTECICLO = 0) THEN 
    SELECT COUNT(CDCICLO) 
    INTO   V_EXISTECICLO 
    FROM   FRCICLOVCLA 
    WHERE (CDFILIAL    = P_CDFILIAL) AND 
          (CDCIA       = P_CDCIA   ) AND 
          (NRVOO       = P_NRVOO   ) AND 
          (CDTRECHO    = P_CDTRECHO) AND 
          (CDCLASSE    = P_CDCLASSE) AND 
          (DTCICLOVCLA = P_DTHRPLAVOOPV); 
    IF (V_EXISTECICLO <> 0) THEN 
      SELECT CDCICLO 
      INTO   P_CDCICLO_GER 
      FROM   FRCICLOVCLA 
      WHERE (CDFILIAL    = P_CDFILIAL) AND 
            (CDCIA       = P_CDCIA   ) AND 
            (NRVOO       = P_NRVOO   ) AND 
            (CDTRECHO    = P_CDTRECHO) AND 
            (CDCLASSE    = P_CDCLASSE) AND 
            (DTCICLOVCLA = P_DTHRPLAVOOPV); 
    ELSE 
      SELECT COUNT(CDCICLO) 
      INTO   V_EXISTECICLO 
      FROM   FRCICLOVOO 
      WHERE (CDFILIAL   = P_CDFILIAL) AND 
            (CDCIA      = P_CDCIA   ) AND 
            (NRVOO      = P_NRVOO   ) AND 
            (CDTRECHO   = P_CDTRECHO) AND 
            (DTCICLOVOO = P_DTHRPLAVOOPV); 
      IF (V_EXISTECICLO <> 0) THEN 
        SELECT CDCICLO 
        INTO   P_CDCICLO_GER 
        FROM   FRCICLOVOO 
        WHERE (CDFILIAL   = P_CDFILIAL) AND 
              (CDCIA      = P_CDCIA   ) AND 
              (NRVOO      = P_NRVOO   ) AND 
              (CDTRECHO   = P_CDTRECHO) AND 
              (DTCICLOVOO = P_DTHRPLAVOOPV); 
      ELSE 
        SELECT COUNT(CDCICLO) 
        INTO   V_EXISTECICLO 
        FROM   FRCICLOCIA 
        WHERE (CDFILIAL   = P_CDFILIAL) AND 
              (CDCIA      = P_CDCIA   ) AND 
              (DTCICLOCIA = P_DTHRPLAVOOPV); 
        IF (V_EXISTECICLO <> 0) THEN 
          SELECT CDCICLO 
          INTO   P_CDCICLO_GER 
          FROM   FRCICLOCIA 
          WHERE (CDFILIAL   = P_CDFILIAL) AND 
                (CDCIA      = P_CDCIA   ) AND 
                (DTCICLOCIA = P_DTHRPLAVOOPV); 
        END IF; 
      END IF; 
    END IF; 
  END IF; 
 
  IF P_CDCICLO_GER IS NULL THEN 
    SELECT COUNT(CDCIA) 
    INTO   V_EXISTECIA 
    FROM   CIAFILI 
    WHERE  CDFILIAL = P_CDFILIAL AND 
           CDCIA    = P_CDCIA; 
    IF (V_EXISTECIA <> 0) THEN 
      SELECT CO.SGCIA,CF.CDCICLO 
      INTO   V_SGCIA, V_CDCICLO_UNICO 
      FROM   CIAFILI CF ,COMPANHIA CO 
      WHERE  CF.CDFILIAL = P_CDFILIAL AND 
             CF.CDCIA    = P_CDCIA    AND 
             CF.CDCIA    = CO.CDCIA; 
    END IF; 
    IF V_CDCICLO_UNICO IS NULL THEN 
       RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00001' || '$$' || V_SGCIA || 'APPERRMSGEND', TRUE); 
        
    ELSE 
      P_CDCICLO_GER := V_CDCICLO_UNICO; 
    END IF; 
  END IF; 
END; 
/