CREATE OR REPLACE FUNCTION F_RETORNA_AGRUPAMENTO(
  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_CDCLASSE     IN  CLASSE.CDCLASSE%TYPE,
  P_CDEQUIP      IN  VOO.CDEQUIP%TYPE,
  P_SGPREFIXO    IN  PREFIXOCIA.SGPREFIXO%TYPE) RETURN VARCHAR2

AS

  V_CONTAGR  NUMBER;
  V_CDAGRDEL AGRDELIVERY.CDAGRDEL%TYPE;
  V_SGCLASSE CLASSE.SGCLASSE%TYPE;
  V_SGEQUIP VARCHAR2(10);
BEGIN
  V_CDAGRDEL := NULL;
  SELECT COUNT(*)
  INTO   V_CONTAGR
  FROM   CONFIGVOO
  WHERE (CDFILIAL = P_CDFILIAL) AND
        (CDCIA    = P_CDCIA   ) AND
        (NRVOO    = P_NRVOO   ) AND
        (CDTRECHO = P_CDTRECHO) AND
        (CDCLASSE = P_CDCLASSE);
  IF V_CONTAGR > 0 THEN
    SELECT CDAGRDEL
    INTO   V_CDAGRDEL
    FROM   CONFIGVOO
    WHERE (CDFILIAL = P_CDFILIAL) AND
          (CDCIA    = P_CDCIA   ) AND
          (NRVOO    = P_NRVOO   ) AND
          (CDTRECHO = P_CDTRECHO) AND
          (CDCLASSE = P_CDCLASSE);
  END IF;
  IF V_CDAGRDEL IS NULL THEN
    SELECT COUNT(*)
    INTO  V_CONTAGR
    FROM  CONFIGPREF
    WHERE (CDFILIAL  = P_CDFILIAL ) AND
          (CDCIA     = P_CDCIA    ) AND
          (SGPREFIXO = P_SGPREFIXO) AND
          (CDCLASSE  = P_CDCLASSE );
    IF V_CONTAGR > 0 THEN
      SELECT CDAGRDEL
      INTO   V_CDAGRDEL
      FROM   CONFIGPREF
      WHERE (CDFILIAL  = P_CDFILIAL ) AND
            (CDCIA     = P_CDCIA    ) AND
            (SGPREFIXO = P_SGPREFIXO) AND
            (CDCLASSE  = P_CDCLASSE );
    END IF;
    IF V_CDAGRDEL IS NULL THEN
      SELECT COUNT(*)
      INTO   V_CONTAGR
      FROM   CONFIGEQUI
      WHERE (CDFILIAL = P_CDFILIAL) AND
            (CDCIA    = P_CDCIA   ) AND
            (CDEQUIP  = P_CDEQUIP ) AND
            (CDCLASSE = P_CDCLASSE);
      IF V_CONTAGR > 0 THEN
        SELECT CDAGRDEL
        INTO   V_CDAGRDEL
        FROM   CONFIGEQUI
        WHERE (CDFILIAL = P_CDFILIAL) AND
              (CDCIA    = P_CDCIA   ) AND
              (CDEQUIP  = P_CDEQUIP ) AND
              (CDCLASSE = P_CDCLASSE);
      END IF;
    END IF;
  END IF;

  IF V_CDAGRDEL IS NULL THEN
     SELECT MAX(SGCLASSE) INTO V_SGCLASSE FROM CLASSE WHERE CDCLASSE = P_CDCLASSE;
     SELECT MAX(SGEQUIP) INTO V_SGEQUIP FROM EQUIPAMENTO WHERE CDEQUIP = P_CDEQUIP;
     /*Agrupamento de delivery nao encontrado para classe %s nas configuracoes do voo %s ou do equipamento %s ou do prefixo %s*/
     RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00029' || '$$' || V_SGCLASSE || '$$' || P_NRVOO || '$$' || V_SGEQUIP || '$$' || V_SGEQUIP || 'APPERRMSGEND', TRUE);
  END IF;
  RETURN V_CDAGRDEL;
END;
/