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