with a as
(select a.handle, a.site 工厂, sl.storage_location||'-'||sl.DESCRIPTION 线边仓, a.inventory_id 库存标识, it.item 物料, it2.description 物料描述,
a.qty_on_hand 现存量, a.original_qty 原数量, nv2.data_attr 工单号, it.unit_of_measure 单位, b3.data_attr as 库存状态,
b.data_attr as 容器, b2.data_attr as 接受时间, iad5.data_attr wbs_num, iad4.data_attr so_num,
iad3.data_attr so_line_num, cf.value error_proofing_sign
from inventory a, item_t it2, item it, storage_location sl, inventory_assy_data b, inventory_assy_data b2,
inventory_assy_data b3, inventory_assy_data nv2, inventory_assy_data iad3, inventory_assy_data iad4,
inventory_assy_data iad5, custom_fields cf
where it2.item_bo = it.handle and (qty_on_hand>0 or (qty_on_hand<=0 and :showzore=1))
and it.handle = a.item_bo
and it.handle = cf.handle(+)
and cf."ATTRIBUTE"(+) = 'ERROR_PROOFING_SIGN'
and a.storage_location_bo = sl.handle(+)
and (a.handle = b.inventory_bo(+) and b.data_field(+) = 'CONTAINER_CODE')
and (a.handle = b2.inventory_bo(+) and b2.data_field(+) = 'ACTUAL_RECEIVE_TIME')
and (a.handle = b3.inventory_bo(+) and b3.data_field(+) = 'INVENTORY_STATUS')
and (a.handle = nv2.inventory_bo(+) and nv2.data_field(+) = 'SHOP_ORDER')
and (a.handle = iad3.inventory_bo(+) and iad3.data_field(+) = 'SO_LINE_NUM')
and (a.handle = iad4.inventory_bo(+) and iad4.data_field(+) = 'SO_NUM')
and (a.handle = iad5.inventory_bo(+) and iad5.data_field(+) = 'WBS_NUM')
and (a.site = :site or :site = '所有')
and exists (select 1
from storage_location tt
where a.storage_location_bo = tt.handle
and tt.STORAGE_LOCATION in (:location)))
select a.* ,regexp_substr(status_bo, '[^,]+', 1, 2) 配送工单状态
from a,shop_order so
where (库存状态 = 'B' or 库存状态 = 'G' or 库存状态 = 'J') and so.shop_order(+) = 工单号
and not exists (select 1 from z_weighing_record x where 物料 = regexp_substr(x.item_bo, '[^,]+', 1, 2) and (x.site = :site or :site = '所有') ) order by 线边仓,库存标识
标签：点表,handle,attr,线边,bo,assy,inventory,rdl,data From： https://www.cnblogs.com/hlm750908/p/17273981.html