View of Oracle

 

create or replace view lxx_lj_out as
select a."PROD_ORDER",a."YZ_QTY",a."WZ_QTY",a."F_QTY",a."G_QTY",a."MTRL_CODE",a."SHAPE",a."DIA",a."SOURCE_ID",a."NEW_ID",b.sale_order,b.sale_item,d.name_cust, c.pick_up_no,c.pk_date,e.shp_date,e.prod_seq_date||e.prod_seq1||e.prod_seq2 work_num
from ( select * from (
                    select prod_order,sum(decode(loc_code,'L0XX',qty,0)) yz_qty
                           ,sum(decode(loc_code,'L010',qty,0)) wz_qty
                           ,sum(decode(loc_code,'L0ZZ',qty,0)) f_qty
                           ,sum(decode(loc_code,'L009',qty,0)) g_qty
                           ,mtrl_code,shape,dia,id source_id,new_id
                    from wip104t
                    where confirm='Y'
                    group by prod_order,mtrl_code,shape,dia,id,new_id
                    )
      where yz_qty<>0
         or wz_qty<>0
         or f_qty<>0
         or g_qty<>0 ) a,

    prp500m b,
    customer d,
    wip104c c,
    prp503t3 e

where a.prod_order=b.sale_order||'_'||b.sale_item
  and b.cust_no=d.no_customer
  and a.source_id=c.id
  and a.source_id=e.bdl_id