CREATE OR REPLACE FUNCTION RETORNA_PARTICIPANTE_SPED(
   P_COD_PART   IN VARCHAR2) RETURN VARCHAR
AS
  V_COD_PART    VARCHAR2(500);
  V_CHAVEREG    VARCHAR2(50);
BEGIN
   V_CHAVEREG := NULL;
   CASE SUBSTR(P_COD_PART,1,1)
      WHEN 'F' THEN
         BEGIN
            SELECT REPLACE(REPLACE(PARTICIPANTE,'_'),P_COD_PART) AS COD_PART
              INTO V_COD_PART
              FROM ENDERECO_PARTICIPANTE_SPED E, FORNECEDOR F
             WHERE 'F'||F.CDFORNECED = P_COD_PART
               AND F.NRINSJURFORN   = E.INSCRICAO
               AND INSTR(E.PARTICIPANTE,P_COD_PART) > 0;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COD_PART   := NULL;
         END;

         IF V_COD_PART IS NOT NULL THEN
            BEGIN
               SELECT T.CDCHAVEREG
                 INTO V_CHAVEREG
                 FROM TMP_REGDADOSSPED T
                WHERE INSTR(V_COD_PART,T.CDCHAVEREG) > 0
                  AND T.CDREGISTRO = '0150';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CHAVEREG := NULL;
            END;
            RETURN V_CHAVEREG;
         ELSE
            RETURN V_CHAVEREG;
         END IF;
      WHEN 'C' THEN
         BEGIN
            SELECT REPLACE(REPLACE(PARTICIPANTE,'_'),P_COD_PART) AS COD_PART
              INTO V_COD_PART
              FROM ENDERECO_PARTICIPANTE_SPED E, CLIENTE C
             WHERE 'C'||C.CDCLIENTE = P_COD_PART
               AND C.NRINSJURCLIE   = E.INSCRICAO
               AND INSTR(E.PARTICIPANTE,P_COD_PART) > 0;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COD_PART   := NULL;
         END;

         IF V_COD_PART IS NOT NULL THEN
            BEGIN
               SELECT T.CDCHAVEREG
                 INTO V_CHAVEREG
                 FROM TMP_REGDADOSSPED T
                WHERE INSTR(V_COD_PART,T.CDCHAVEREG) > 0
                  AND T.CDREGISTRO = '0150';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CHAVEREG := NULL;
            END;
            RETURN V_CHAVEREG;
         ELSE
            RETURN V_CHAVEREG;
         END IF;
      WHEN 'L' THEN
         BEGIN
            SELECT REPLACE(REPLACE(PARTICIPANTE,'_'),P_COD_PART) AS COD_PART
              INTO V_COD_PART
              FROM ENDERECO_PARTICIPANTE_SPED E, FILIAL L
             WHERE 'L'||L.CDFILIAL = P_COD_PART
               AND L.NRINSJURFILI  = E.INSCRICAO
               AND INSTR(E.PARTICIPANTE,P_COD_PART) > 0;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COD_PART   := NULL;
         END;

         IF V_COD_PART IS NOT NULL THEN
            BEGIN
               SELECT T.CDCHAVEREG
                 INTO V_CHAVEREG
                 FROM TMP_REGDADOSSPED T
                WHERE INSTR(V_COD_PART,T.CDCHAVEREG) > 0
                  AND T.CDREGISTRO = '0150';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CHAVEREG := NULL;
            END;
            RETURN V_CHAVEREG;
         ELSE
            RETURN V_CHAVEREG;
         END IF;
   ELSE
      RETURN V_CHAVEREG;
   END CASE;
EXCEPTION
    WHEN OTHERS THEN BEGIN
       RAISE_APPLICATION_ERROR(-20501,P_COD_PART||SQLERRM(SQLCODE),TRUE);
    END;
END;