CREATE OR REPLACE FUNCTION USR_ORIGDESTNF
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_ENTRADA    IN NOTAFISCAL.IDSTATUS%TYPE) RETURN VARCHAR
AS
   V_IDORIG      VARCHAR2(01);
   V_SGESTADO    VARCHAR2(02);
   V_CDMUNIIBGE  VARCHAR2(07);
BEGIN
   BEGIN
      SELECT N.IDORIGDESTNF
      INTO V_IDORIG
      FROM NOTAFISCAL N
      WHERE N.CDFILIAL     = P_CDFILIAL     AND
            N.NRLANCTONF   = P_NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDORIG := NULL;
   END;

   /* cliente */
   IF (V_IDORIG = 'C') THEN
      BEGIN
         SELECT NVL(M.CDMUNICIBGE, E.CDMUNICIPIO), E.SGESTADO
           INTO V_CDMUNIIBGE, V_SGESTADO
           FROM NOTAFISCAL N, CLIENTE C, ENDECLIE E, ESTADO ET, MUNICIPIO M
          WHERE N.CDFILIAL     = P_CDFILIAL      AND
                N.NRLANCTONF   = P_NRLANCTONF    AND
                N.CDCLIENTE    = C.CDCLIENTE     AND
                E.CDCLIENTE    = C.CDCLIENTE     AND
                E.CDPAIS       = ET.CDPAIS       AND
                E.SGESTADO     = ET.SGESTADO     AND
                E.CDPAIS       = M.CDPAIS        AND
                E.SGESTADO     = M.SGESTADO      AND
                E.CDMUNICIPIO  = M.CDMUNICIPIO   AND
                E.IDTPENDECLIE = 'P';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDMUNIIBGE := NULL;
            V_SGESTADO   := NULL;
      END;
   END IF;

   /* fornecedor */
   IF (V_IDORIG = 'F') THEN
      BEGIN
         SELECT NVL(M.CDMUNICIBGE, E.CDMUNICIPIO), E.SGESTADO
           INTO V_CDMUNIIBGE, V_SGESTADO
           FROM NOTAFISCAL N, FORNECEDOR F, ENDEFORN E, ESTADO ET, MUNICIPIO M
          WHERE N.CDFILIAL     = P_CDFILIAL      AND
                N.NRLANCTONF   = P_NRLANCTONF    AND
                N.CDFORNECED   = F.CDFORNECED    AND
                E.CDFORNECED   = F.CDFORNECED    AND
                E.CDPAIS       = ET.CDPAIS       AND
                E.SGESTADO     = ET.SGESTADO     AND
                E.CDPAIS       = M.CDPAIS        AND
                E.SGESTADO     = M.SGESTADO      AND
                E.CDMUNICIPIO  = M.CDMUNICIPIO   AND
                E.IDTPENDEFORN = 'P';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDMUNIIBGE := NULL;
            V_SGESTADO   := NULL;
      END;
   END IF;

   /* filial */
   IF (V_IDORIG = 'L') THEN
      BEGIN
         SELECT NVL(M.CDMUNICIBGE, E.CDMUNICIPIO), E.SGESTADO
           INTO V_CDMUNIIBGE, V_SGESTADO
           FROM NOTAFISCAL N, FILIAL FI, ENDEFILI E, ESTADO ET, MUNICIPIO M
          WHERE N.CDFILIAL     = P_CDFILIAL      AND
                N.NRLANCTONF   = P_NRLANCTONF    AND
                N.CDFILIAL     = FI.CDFILIAL     AND
                E.CDFILIAL     = N.CDFILIRECE    AND
                E.CDPAIS       = ET.CDPAIS       AND
                E.SGESTADO     = ET.SGESTADO     AND
                E.CDPAIS       = M.CDPAIS        AND
                E.SGESTADO     = M.SGESTADO      AND
                E.CDMUNICIPIO  = M.CDMUNICIPIO   AND
                E.IDTPENDEFILI = 'P';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDMUNIIBGE := NULL;
            V_SGESTADO   := NULL;
      END;
   END IF;
   IF P_ENTRADA = 'E' THEN
      RETURN(V_SGESTADO);
   END IF;
   IF P_ENTRADA = 'I' THEN
      RETURN(V_CDMUNIIBGE);
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ORIGDESTNF','4.07.272'); END;
/

