帆软盘点表导出by工厂线边仓.rdl

 

通过优化 这个sql 由原来3.47秒 变成 10秒出结果

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 线边仓,库存标识