CREATE OR REPLACE VIEW ORIGDESTNF AS (
   SELECT N.CDFILIAL, N.NRLANCTONF,
          N.CDPAISDEST, N.SGESTADEST, N.CDMUNIDEST, ED.CDESTAEXPO AS CDESTADEST,
          N.CDPAISORIG, N.SGESTAORIG, N.CDMUNIORIG, EO.CDESTAEXPO AS CDESTAORIG,
          PO.NMPAIS AS NMPAISORIG,
          MO.NMMUNICIPIO AS NMMUNIORIG,
          PD.NMPAIS AS NMPAISDEST,
          MD.NMMUNICIPIO AS NMMUNIDEST,
          N.CDPAISMOVI, N.SGESTAMOVI, N.CDMUNIMOVI,
          N.CDPAISCFOP, N.SGESTACFOP, N.CDMUNICFOP,
          E.CDESTAEXPO, M.CDMUNIEXPO, M.IDMUNIALC
   FROM PAIS PO, PAIS PD, MUNICIPIO MO, MUNICIPIO MD, ESTADO E, MUNICIPIO M,
        ESTADO EO, ESTADO ED,
        (SELECT N.CDFILIAL, N.NRLANCTONF,
                NVL(EF.CDPAIS, NVL(EC.CDPAIS, EL.CDPAIS)) AS CDPAISDEST,
                NVL(EF.SGESTADO, NVL(EC.SGESTADO, EL.SGESTADO)) AS SGESTADEST,
                NVL(EF.CDMUNICIPIO, NVL(EC.CDMUNICIPIO, EL.CDMUNICIPIO)) AS CDMUNIDEST,
                IE.CDPAIS AS CDPAISORIG,
                IE.SGESTADO AS SGESTAORIG,
                IE.CDMUNICIPIO AS CDMUNIORIG,
                NVL(EF.CDPAIS, NVL(EC.CDPAIS, EL.CDPAIS)) AS CDPAISMOVI,
                NVL(EF.SGESTADO, NVL(EC.SGESTADO, EL.SGESTADO)) AS SGESTAMOVI,
                NVL(EF.CDMUNICIPIO, NVL(EC.CDMUNICIPIO, EL.CDMUNICIPIO)) AS CDMUNIMOVI,
                NVL(FCF.CDPAIS, NVL(CCF.CDPAIS, LCF.CDPAIS)) AS CDPAISCFOP,
                NVL(FCF.SGESTADO, NVL(CCF.SGESTADO, LCF.SGESTADO)) AS SGESTACFOP,
                NVL(FCF.CDMUNICIPIO, NVL(CCF.CDMUNICIPIO, LCF.CDMUNICIPIO)) AS CDMUNICFOP
         FROM NOTAFISCAL N, INSCRESTAD IE,
              FORNECEDOR F, CLIENTE C, FILIAL L,
              ENDEFORN EF, ENDECLIE EC, ENDEFILI EL,
              ENDEFORN FCF, ENDECLIE CCF, ENDEFILI LCF
         WHERE N.CDEMPRESA  = IE.CDEMPRESA      AND
               N.CDINSCESTA = IE.CDINSCESTA     AND
               N.CDFORNECED = F.CDFORNECED(+)   AND
               F.CDFORNECED = EF.CDFORNECED(+)  AND
               NVL(F.IDENDEMISFOR, 'P') = EF.IDTPENDEFORN(+) AND
               N.CDCLIENTE  = C.CDCLIENTE(+)    AND
               C.CDCLIENTE  = EC.CDCLIENTE(+)   AND
               NVL(C.IDENDEMISCLI, 'P') = EC.IDTPENDECLIE(+) AND
               N.CDFILIRECE = L.CDFILIAL(+)     AND
               L.CDFILIAL   = EL.CDFILIAL(+)    AND
               NVL(L.IDENDEMISFIL, 'P') = EL.IDTPENDEFILI(+) AND
               N.IDENTRSAIDOP = 'S'             AND
               F.CDFORNECED = FCF.CDFORNECED(+) AND
               NVL(F.IDENDCFOPFOR, 'P') = FCF.IDTPENDEFORN(+) AND
               C.CDCLIENTE  = CCF.CDCLIENTE(+)  AND
               NVL(C.IDENDCFOPCLI, 'P') = CCF.IDTPENDECLIE(+) AND
               L.CDFILIAL   = LCF.CDFILIAL(+)   AND
               NVL(L.IDENDCFOPFIL, 'P') = LCF.IDTPENDEFILI(+)
         UNION ALL
         SELECT N.CDFILIAL, N.NRLANCTONF,
                IE.CDPAIS AS CDPAISDEST,
                IE.SGESTADO AS SGESTADEST,
                IE.CDMUNICIPIO AS CDMUNIDEST,
                NVL(EF.CDPAIS, NVL(EC.CDPAIS, EL.CDPAIS)) AS CDPAISORIG,
                NVL(EF.SGESTADO, NVL(EC.SGESTADO, EL.SGESTADO)) AS SGESTAORIG,
                NVL(EF.CDMUNICIPIO, NVL(EC.CDMUNICIPIO, EL.CDMUNICIPIO)) AS CDMUNIORIG,
                NVL(EF.CDPAIS, NVL(EC.CDPAIS, EL.CDPAIS)) AS CDPAISMOVI,
                NVL(EF.SGESTADO, NVL(EC.SGESTADO, EL.SGESTADO)) AS SGESTAMOVI,
                NVL(EF.CDMUNICIPIO, NVL(EC.CDMUNICIPIO, EL.CDMUNICIPIO)) AS CDMUNIMOVI,
                NVL(FCF.CDPAIS, NVL(CCF.CDPAIS, LCF.CDPAIS)) AS CDPAISCFOP,
                NVL(FCF.SGESTADO, NVL(CCF.SGESTADO, LCF.SGESTADO)) AS SGESTACFOP,
                NVL(FCF.CDMUNICIPIO, NVL(CCF.CDMUNICIPIO, LCF.CDMUNICIPIO)) AS CDMUNICFOP
         FROM NOTAFISCAL N, INSCRESTAD IE,
              FORNECEDOR F, CLIENTE C, FILIAL L,
              ENDEFORN EF, ENDECLIE EC, ENDEFILI EL,
              ENDEFORN FCF, ENDECLIE CCF, ENDEFILI LCF
         WHERE N.CDEMPRESA  = IE.CDEMPRESA      AND
               N.CDINSCESTA = IE.CDINSCESTA     AND
               N.CDFORNECED = F.CDFORNECED(+)   AND
               F.CDFORNECED = EF.CDFORNECED(+)  AND
               NVL(F.IDENDEMISFOR, 'P') = EF.IDTPENDEFORN(+) AND
               N.CDCLIENTE  = C.CDCLIENTE(+)    AND
               C.CDCLIENTE  = EC.CDCLIENTE(+)   AND
               NVL(C.IDENDEMISCLI, 'P') = EC.IDTPENDECLIE(+) AND
               N.CDFILIEMIT = L.CDFILIAL(+)     AND
               L.CDFILIAL   = EL.CDFILIAL(+)    AND
               NVL(L.IDENDEMISFIL, 'P') = EL.IDTPENDEFILI(+) AND
               N.IDENTRSAIDOP = 'E'             AND
               F.CDFORNECED = FCF.CDFORNECED(+) AND
               NVL(F.IDENDCFOPFOR, 'P') = FCF.IDTPENDEFORN(+) AND
               C.CDCLIENTE  = CCF.CDCLIENTE(+)  AND
               NVL(C.IDENDCFOPCLI, 'P') = CCF.IDTPENDECLIE(+) AND
               L.CDFILIAL   = LCF.CDFILIAL(+)   AND
               NVL(L.IDENDCFOPFIL, 'P') = LCF.IDTPENDEFILI(+)) N
   WHERE N.CDPAISDEST = PD.CDPAIS      AND
         N.CDPAISDEST = ED.CDPAIS      AND
         N.SGESTADEST = ED.SGESTADO    AND
         N.CDPAISDEST = MD.CDPAIS      AND
         N.SGESTADEST = MD.SGESTADO    AND
         N.CDMUNIDEST = MD.CDMUNICIPIO AND
         N.CDPAISORIG = PO.CDPAIS      AND
         N.CDPAISORIG = EO.CDPAIS      AND
         N.SGESTAORIG = EO.SGESTADO    AND
         N.CDPAISORIG = MO.CDPAIS      AND
         N.SGESTAORIG = MO.SGESTADO    AND
         N.CDMUNIORIG = MO.CDMUNICIPIO AND
         N.CDPAISMOVI = M.CDPAIS       AND
         N.SGESTAMOVI = M.SGESTADO     AND
         N.CDMUNIMOVI = M.CDMUNICIPIO  AND
         N.CDPAISCFOP = E.CDPAIS       AND
         N.SGESTACFOP = E.SGESTADO)
/

