CREATE OR REPLACE FUNCTION RETORNA_DADO_PARTICIPANTE_NF (
   P_IDORIGDESTNF IN NOTAFISCAL.IDORIGDESTNF%TYPE,
   P_CHAVE        IN VARCHAR,
   P_IDVALOR      IN VARCHAR) RETURN VARCHAR
AS
   V_IDTPINSJUR  CLIENTE.IDTPIJURCLIE%TYPE;
   V_NRINSJUR    CLIENTE.NRINSJURCLIE%TYPE;
   V_NMRAZSOC    CLIENTE.NMRAZSOCCLIE%TYPE;
   V_NMMUNICIPIO MUNICIPIO.NMMUNICIPIO%TYPE;
   V_SGESTADO    ESTADO.SGESTADO%TYPE;
BEGIN
   IF P_IDORIGDESTNF = 'C' THEN
      IF P_IDVALOR IN ('T','I','R') THEN
          BEGIN
             SELECT IDTPIJURCLIE, NRINSJURCLIE, NMRAZSOCCLIE
               INTO V_IDTPINSJUR, V_NRINSJUR, V_NMRAZSOC
               FROM CLIENTE
              WHERE CDCLIENTE = P_CHAVE;
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                V_IDTPINSJUR := NULL;
                V_NRINSJUR   := NULL;
                V_NMRAZSOC   := NULL;
          END;
      END IF;
      IF P_IDVALOR IN ('M','E') THEN
         BEGIN
            SELECT M.NMMUNICIPIO, M.SGESTADO
              INTO V_NMMUNICIPIO, V_SGESTADO
              FROM MUNICIPIO M, ENDECLIE E
             WHERE E.CDCLIENTE    = P_CHAVE
               AND E.IDTPENDECLIE = 'P'
               AND E.CDPAIS       = M.CDPAIS
               AND E.SGESTADO     = M.SGESTADO
               AND E.CDMUNICIPIO  = M.CDMUNICIPIO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NMMUNICIPIO := NULL;
               V_SGESTADO    := NULL;
         END;
      END IF;
   ELSIF P_IDORIGDESTNF = 'F' THEN
      IF P_IDVALOR IN ('T','I','R') THEN
         BEGIN
            SELECT IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN
              INTO V_IDTPINSJUR, V_NRINSJUR, V_NMRAZSOC
              FROM FORNECEDOR
             WHERE CDFORNECED = P_CHAVE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDTPINSJUR := NULL;
               V_NRINSJUR   := NULL;
               V_NMRAZSOC   := NULL;
         END;
      END IF;
      IF P_IDVALOR IN ('M','E') THEN
         BEGIN
            SELECT M.NMMUNICIPIO, M.SGESTADO
              INTO V_NMMUNICIPIO, V_SGESTADO
              FROM MUNICIPIO M, ENDEFORN E
             WHERE E.CDFORNECED   = P_CHAVE
               AND E.IDTPENDEFORN = 'P'
               AND E.CDPAIS       = M.CDPAIS
               AND E.SGESTADO     = M.SGESTADO
               AND E.CDMUNICIPIO  = M.CDMUNICIPIO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NMMUNICIPIO := NULL;
               V_SGESTADO    := NULL;
         END;
      END IF;
   ELSIF P_IDORIGDESTNF = 'L' THEN
      IF P_IDVALOR IN ('T','I','R') THEN
         BEGIN
            SELECT IDTPIJURFILI, NRINSJURFILI, NMRAZSOCFILI
              INTO V_IDTPINSJUR, V_NRINSJUR, V_NMRAZSOC
              FROM FILIAL
             WHERE CDFILIAL = P_CHAVE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDTPINSJUR := NULL;
               V_NRINSJUR   := NULL;
               V_NMRAZSOC   := NULL;
         END;
      END IF;

      IF P_IDVALOR IN ('M','E') THEN
         BEGIN
            SELECT M.NMMUNICIPIO, M.SGESTADO
              INTO V_NMMUNICIPIO, V_SGESTADO
              FROM MUNICIPIO M, ENDEFILI E
             WHERE E.CDFILIAL     = P_CHAVE
               AND E.IDTPENDEFILI = 'P'
               AND E.CDPAIS       = M.CDPAIS
               AND E.SGESTADO     = M.SGESTADO
               AND E.CDMUNICIPIO  = M.CDMUNICIPIO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NMMUNICIPIO := NULL;
               V_SGESTADO    := NULL;
         END;
      END IF;
   END IF;
   IF P_IDVALOR = 'T' THEN 
      RETURN V_IDTPINSJUR;
   ELSIF P_IDVALOR = 'I' THEN 
      RETURN V_NRINSJUR;
   ELSIF P_IDVALOR = 'R' THEN
      RETURN V_NMRAZSOC;
   ELSIF P_IDVALOR = 'M' THEN
      RETURN V_NMMUNICIPIO;
   ELSIF P_IDVALOR = 'E' THEN
      RETURN V_SGESTADO;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('RETORNA_DADO_PARTICIPANTE_NF','6.49.004'); END;
/
