create or replace view st_view as
select sst.s_id,
       sst.mdr,
       sst.goc,
       sst.s_item,
       sst.b_item,
       sst.s_ityp,
       je.jkey || '/' || je.ed_no o_cde,
       sst.i_code,
       je.j_tle title,	
       je. js_tle sub,
       je.jsh_tle short,
       je.j_pbs pubsr,
       sst.created_user,
       sst.created_date,
       sst.mod_user,
       sst.mod_date,
       myp_func('pubs', je.p_stat, sst.mdr, '', 'trm') status,
       je.s_date as s_date,
       sst.o_id o_id,
       je.med med,
       je.med2 med2,
       je.rkey as rkey,
       null as ibn,
       null as ibn13,
       null as o_no,
       null as pn_al,
       to_number(null) as im_no
  from si_tble sst, j_edi je
where sst.s_id = je.s_id
   and sst.s_ityp = 'je'
union
select distinct sst.s_id,
                sst.mdr,
                sst.goc,
                sst.s_item,
                sst.b_item,
                sst.s_ityp,
                decode(pi.ibn,
                       null,
                       pi.o_no,
                       pi.ibn || '-' || pi.ibn_ctrl) o_cde,
                sst.i_cde,
                pe.tle title,
                pe.subtle sub,
                pe.sh_tle short,
                pe.p_hse pubsr,
                sst.create_user,
                sst.create_date,
                sst.mod_user,
                sst.mod_date,
                myp_stat.status,
                null as s_date,
                sst.o_id o_id,
                pe.med med,
                pe.med2 med2,
                null as rkey,
                decode(who_func.al_cd1,
                       'y',
                       decode(pi.ibn,
                              null,
                              null,
                              pi.ibn || '-' || pi.ibn_ctrl),
                       decode(pi.ibn_old,
                              null,
                              null,
                              pi.ibn_old || '-' || pi.ibn_ctrl_old)) as ibn,
                decode(pi.ibn,
                       null,
                       null,
                       pi.ibn || '-' || pi.ibn_ctrl) as ibn13,
                pi.o_no as o_no,
                pi.pn_al as pn_al,
                pi.im_no as im_no
  from si_tble sst,
       p_imp pi,
       p_edit pe,
       (select myp.mdr, myp.code, myp.trm as status
          from myp mdt
         where myp.type = 'stat') myp_stat,
       (select myp.mdr, myp.al_cd1
          from myp mdt
         where myp.type = 'who'
           and myp.code = 'ibn13') who_func
where pi.im_id = sst.o_id
   and sst.s_ityp in ('prd', 'osp')
   and pi.ed_id = pe.ed_id
   and (myp_stat.mdr is null or myp_stat.mdr = sst.mdr)
   and myp_stat.code(+) = pi.status
   and (who_func.mdr is null or who_func.mdr = sst.mdr);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.18       0.19          0          8          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.18       0.19          0         11          0           0

Rows     Row Source Operation
-------  ---------------------------------------------------
....................
...................
....................
   1   VIEW  ST_VIEW (cr=9 pr=0 pw=0 time=454 us cost=9 size=666 card=1)
      1    SORT UNIQUE (cr=9 pr=0 pw=0 time=452 us cost=9 size=2538 card=2)
...........
..............
............