CREATE OR REPLACE FUNCTION RECE_UTIL_TCO
(P_CDFILAUXPRAT IN VARCHAR2,
 P_CDPRATO IN VARCHAR2,
 P_CDFILIAL IN VARCHAR2,
 P_NRSERVICOPROPOSTA IN VARCHAR2,
 P_NRPADRAOQUALIDADE IN NUMBER,  
 P_CDREGIAO IN VARCHAR2,
 P_NRCARDAPIOTRAB IN NUMBER) RETURN VARCHAR2
AS
   V_EXISTE NUMBER := 0;
   V_CDFILIAL VARCHAR2(4);
   V_CDSERVFILI VARCHAR2(5);
BEGIN
   IF P_NRCARDAPIOTRAB IS NOT NULL THEN
      SELECT COUNT(1)
        INTO V_EXISTE
        FROM RECEPRATCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
         AND CDFILAUXPRAT = P_CDFILAUXPRAT
         AND CDPRATO = P_CDPRATO;
      IF V_EXISTE > 0 THEN
         RETURN 'C';
      END IF;
   END IF;
   IF V_EXISTE = 0 THEN
      IF P_NRPADRAOQUALIDADE IS NOT NULL THEN
         IF P_CDREGIAO IS NOT NULL THEN
            SELECT COUNT(1)
              INTO V_EXISTE
              FROM RECEREGIAO
             WHERE CDFILAUXPRAT = P_CDFILAUXPRAT
               AND CDPRATO = P_CDPRATO
               AND NRPADRAOQUALIDADE = P_NRPADRAOQUALIDADE
               AND CDREGIAO = P_CDREGIAO;
            IF V_EXISTE > 0 THEN
               RETURN 'R';
            END IF;
         END IF;
         
         IF V_EXISTE = 0 THEN
            SELECT COUNT(1)
              INTO V_EXISTE
              FROM RECEPADQUALI
             WHERE CDFILAUXPRAT = P_CDFILAUXPRAT
               AND CDPRATO = P_CDPRATO
               AND NRPADRAOQUALIDADE = P_NRPADRAOQUALIDADE;
            IF V_EXISTE > 0 THEN
               RETURN 'Q';
            END IF;
         END IF;
      END IF;

      IF V_EXISTE = 0 AND P_NRSERVICOPROPOSTA IS NOT NULL THEN
         SELECT MAX(CDFILIAL), MAX(CDSERVFILI)
           INTO V_CDFILIAL, V_CDSERVFILI
           FROM SERVICOPROPOSTA
          WHERE NRSERVICOPROPOSTA = P_NRSERVICOPROPOSTA;

         IF V_CDFILIAL IS NOT NULL AND V_CDSERVFILI IS NOT NULL THEN
            SELECT COUNT(1)
              INTO V_EXISTE
              FROM RECESERV
             WHERE CDFILAUXPRAT = P_CDFILAUXPRAT
               AND CDPRATO = P_CDPRATO
               AND CDFILIAL = V_CDFILIAL
               AND CDSERVFILI = V_CDSERVFILI;
            IF V_EXISTE > 0 THEN
               RETURN 'S';
            END IF;   
         END IF;
      END IF;

      IF V_EXISTE = 0 AND P_CDFILIAL IS NOT NULL THEN
         SELECT COUNT(1)
           INTO V_EXISTE
           FROM RECEFILI
          WHERE CDFILAUXPRAT = P_CDFILAUXPRAT
            AND CDPRATO = P_CDPRATO
            AND CDFILIAL = P_CDFILIAL;
         IF V_EXISTE > 0 THEN
            RETURN 'F';
         END IF;
      END IF;
      
      IF V_EXISTE = 0 THEN
         SELECT COUNT(1)
           INTO V_EXISTE
           FROM RECEPADR
          WHERE CDFILAUXPRAT = P_CDFILAUXPRAT
            AND CDPRATO = P_CDPRATO;
         IF V_EXISTE > 0 THEN
            RETURN 'P';
         END IF;
      END IF;
   END IF;

   RETURN NULL;
END;
/