CREATE OR REPLACE FUNCTION RECEITA_UTILIZADA(
   P_CDPRATO      IN PRATO.CDPRATO%TYPE,
   P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
   P_CDSERVFILI   IN SERVICO.CDSERVFILI%TYPE) RETURN VARCHAR2 IS

   V_IDTIPO     VARCHAR2(1);
BEGIN
    BEGIN
       SELECT DISTINCT IDTIPO
         INTO V_IDTIPO
         FROM (SELECT 'S' AS IDTIPO
                 FROM RECESERV
                WHERE (RECESERV.CDFILAUXPRAT = P_CDFILAUXPRAT)
                  AND (RECESERV.CDPRATO      = P_CDPRATO)
                  AND (RECESERV.CDFILIAL     = P_CDFILIAL)
                  AND (RECESERV.CDSERVFILI   = P_CDSERVFILI)
              UNION ALL
               SELECT 'F' AS IDTIPO
                 FROM RECEFILI
                WHERE (RECEFILI.CDFILAUXPRAT = P_CDFILAUXPRAT)
                  AND (RECEFILI.CDPRATO      = P_CDPRATO)
                  AND (RECEFILI.CDFILIAL     = P_CDFILIAL)
                  AND NOT EXISTS (SELECT CDPRATO
                                    FROM RECESERV
                                   WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                     AND (CDPRATO      = P_CDPRATO)
                                     AND (CDFILIAL     = P_CDFILIAL)
                                     AND (CDSERVFILI   = P_CDSERVFILI))
              UNION ALL
               SELECT 'P' AS IDTIPO
                 FROM RECEPADR
                WHERE (RECEPADR.CDFILAUXPRAT = P_CDFILAUXPRAT)
                  AND (RECEPADR.CDPRATO      = P_CDPRATO)
                  AND NOT EXISTS (SELECT CDPRATO
                                    FROM RECEFILI
                                   WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                     AND (CDPRATO      = P_CDPRATO)
                                     AND (CDFILIAL     = P_CDFILIAL))
                  AND NOT EXISTS (SELECT CDPRATO
                                    FROM RECESERV
                                   WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                     AND (CDPRATO      = P_CDPRATO)
                                     AND (CDFILIAL     = P_CDFILIAL)
                                     AND (CDSERVFILI   = P_CDSERVFILI)));
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END;

   RETURN(V_IDTIPO);
END;
/