CREATE OR REPLACE FUNCTION PRATO_RECEITA_ATIVA(
   P_CDPRATO IN PRATSERV.CDPRATO%TYPE,
   P_CDFILAUXPRAT IN PRATSERV.CDFILAUXPRAT%TYPE,
   P_CDFILIAL IN PRATSERV.CDFILIAL%TYPE,
   P_CDSERVFILI IN PRATSERV.CDSERVFILI%TYPE,
   P_IDTIPOREC IN VARCHAR2 DEFAULT NULL,
   P_CDFILCENTPME IN FILIAL.CDFILIAL%TYPE DEFAULT NULL,
   P_CDTIPOCARD   IN TIPOCARD.CDTIPOCARD%TYPE DEFAULT NULL
   ) RETURN VARCHAR2
AS
   V_IDATIVO VARCHAR2(1);
   V_IDTIPOREC VARCHAR2(1);
BEGIN
   SELECT NVL(MAX(IDATIVO), 'S')
      INTO V_IDATIVO
   FROM PRATO
   WHERE CDPRATO = P_CDPRATO
     AND CDFILAUXPRAT = P_CDFILAUXPRAT;

   IF (V_IDATIVO = 'N') THEN
        RETURN('N');
   ELSE
      IF P_IDTIPOREC IS NULL THEN
         BEGIN
            SELECT DISTINCT IDTIPO
              INTO V_IDTIPOREC
            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 'T' AS IDTIPO
                    FROM RECETPCARD
                   WHERE (RECETPCARD.CDFILCENTPME = P_CDFILCENTPME)
                     AND (RECETPCARD.CDTIPOCARD   = P_CDTIPOCARD)
                     AND (RECETPCARD.CDFILAUXPRAT = P_CDFILAUXPRAT)
                     AND (RECETPCARD.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))

                  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))
                    AND NOT EXISTS (SELECT CDPRATO
                                FROM RECETPCARD
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILCENTPME = P_CDFILCENTPME)
                                 AND (CDTIPOCARD   = P_CDTIPOCARD))
                 );
         EXCEPTION
            WHEN OTHERS THEN
              V_IDTIPOREC := NULL;
         END;
      ELSE
         V_IDTIPOREC := P_IDTIPOREC;
      END IF;
      IF V_IDTIPOREC = 'T' THEN
         SELECT NVL(MAX(IDATIVO), 'S')
               INTO V_IDATIVO
           FROM PRATTPCARD
          WHERE (CDFILCENTPME = P_CDFILCENTPME)
            AND (CDTIPOCARD   = P_CDTIPOCARD)
            AND (CDFILAUXPRAT = P_CDFILAUXPRAT)
            AND (CDPRATO      = P_CDPRATO);

            RETURN(V_IDATIVO);
      ELSE
         IF V_IDTIPOREC = 'S' THEN
            SELECT NVL(MAX(IDATIVO), 'S')
               INTO V_IDATIVO
            FROM PRATSERV
            WHERE CDPRATO = P_CDPRATO
              AND CDFILAUXPRAT = P_CDFILAUXPRAT
              AND CDFILIAL = P_CDFILIAL
              AND CDSERVFILI = P_CDSERVFILI;

            RETURN(V_IDATIVO);
         ELSE
            IF (V_IDTIPOREC = 'F') OR (V_IDTIPOREC = 'L') THEN
               SELECT NVL(MAX(IDATIVO), 'S')
                  INTO V_IDATIVO
               FROM PRATFILI
               WHERE CDPRATO = P_CDPRATO
                 AND CDFILAUXPRAT = P_CDFILAUXPRAT
                 AND CDFILIAL = P_CDFILIAL;

               RETURN(V_IDATIVO);
            ELSE
               IF V_IDTIPOREC = 'P' THEN
                  SELECT NVL(MAX(IDATIVO), 'S')
                     INTO V_IDATIVO
                  FROM PRATPADR
                  WHERE CDPRATO = P_CDPRATO
                    AND CDFILAUXPRAT = P_CDFILAUXPRAT;

                  RETURN(V_IDATIVO);
               ELSE
                  RETURN('S');
               END IF;
            END IF;
         END IF;
      END IF;
   END IF;
END;