支持 wbs ,按单物料 ,
物料接收才可使用,
需扫码上料必须扫码上料,
无需扫码上料的 带swr 编号的优先使用
By工序 区分线边仓口库存
with a as /*查工单指定工段所有bom物料,扫码上料否 EQ */
(select x.site, x.shop_order, x.component_gbo, x.qty, x.erp_step, r.work_center, x.operation, ow.warehouse, x.back_flush,
x.q_or_e, x.qty * 13 sum_qty, nvl(x.sales_order, ' ') sales_order, nvl(to_number(x.so_line), 0) so_line,
nvl(x.wbs, ' ') wbs, x.erp_control_key_group, it.scan_check, it.error_proofing_sign, so.shop_order_type,
swt.swrline
from bom x, routing r, operation_warehouse ow, item it, shop_order so, swrtable swt
where r.erp_step = x.erp_step
and r.shop_order = x.shop_order
and so.shop_order = x.shop_order
and (ow.work_center(+) = r.work_center and ow.operation(+) = r.operation) /*串工序线边仓*/
and (it.item(+) = x.component_gbo and it.site(+) = x.site) /*串查 扫码上料*/
and (swt.site(+) = x.site and swt.shop_order(+) = x.shop_order and swt.item(+) = x.component_gbo) /*串swr*/
and x.shop_order = '222700094510'
and x.back_flush = 'X'
and x.enabled = 1 /*有效物料*/
and x.back_flush = 'X' /*只取反冲物料*/
and x.erp_control_key_group = 1 /*当前工段物料*/
order by x.erp_step, x."SEQUENCE"),
a1 as
(select site, shop_order, component_gbo, /*qty,*/ erp_step, /*work_center,*/ operation, warehouse, /*back_flush,*/
q_or_e, sum_qty, wbs, sales_order, so_line, erp_control_key_group, /*error_proofing_sign,*/
/*shop_order_type,*/
case /*工单类型为SP04orSP12时，盘装料(PD)的组件物料走不上料(N)的逻辑*/
when shop_order_type in ('SP04', 'SP12' /*, 'SP01'*/) and error_proofing_sign = 'P' then
0
else
a.scan_check
end scan_check, swrline
from a),
b as /*查询所有符合条件库存*/
(select x.site, x.inventory, x.item, x.warehouse, a1.scan_check, bind.resrce, x.qty_on_hand,
nvl(x.lot_number, ' ') lot_number, nvl(x.so_num, ' ') so_num, nvl(to_number(x.so_line_num), 0) so_line_num,
nvl(x.wbs_num, ' ') wbs_num, nvl(x.swr_num, ' ') swr_num, nvl(x.mrb_num, ' ') mrb_num
from inventory x, item_resource_bind bind, a1
where x.inventory_type = '122' /*原材料*/
and x.enabled = 1 /*有效物料*/
and x.qty_on_hand > 0 /*库存>0*/
and exists (select 1 from a1 where (a1.component_gbo = x.item and a1.warehouse = x.warehouse)) /*指定物料,线边仓*/
and a1.component_gbo(+) = x.item
and bind.inventory(+) = x.inventory /*匹配扫码上料*/
order by x.item, x.inventory),
b1 as
(select *
from b
where exists (select 1 from a1 where (a1.wbs = b.wbs_num and a1.sales_order = b.so_num and a1.so_line = b.so_line_num))
and ((scan_check = 1 and resrce is not null) or scan_check = 0))
select * from b1
标签：shop,库存,报工,a1,num,so,出站,nvl,order From： https://www.cnblogs.com/hlm750908/p/17052108.html