出站报工 扣库存

支持 wbs ,按单物料 ,

物料接收才可使用,

需扫码上料必须扫码上料,

Swr组件物料必须扫码上料,无论此物料系统设定是否需要扫码,可扣料范围是 条码带swr编号 和 无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, warehouse, q_or_e, wbs, sales_order, so_line, swrline, erp_step, sum_qty,

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, erp_control_key_group, operation /*, qty, work_center, back_flush, error_proofing_sign,shop_order_type */

from a

order by erp_step, component_gbo),

b as /*查询所有符合条件库存*/

(select x.site, x.inventory, x.item, x.warehouse, nvl(x.wbs_num, ' ') wbs_num, nvl(x.so_num, ' ') so_num,

nvl(to_number(x.so_line_num), 0) so_line_num, nvl(x.swr_num, ' ') swr_num, nvl(x.mrb_num, ' ') mrb_num,

x.qty_on_hand, nvl(x.lot_number, ' ') lot_number, a1.scan_check, bind.resrce, bind.created_date_time bindtime,

x.actual_receive_time

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 b.*, a1.erp_step

from b, a1

where (a1.component_gbo(+) = b.item and a1.wbs(+) = b.wbs_num and a1.sales_order(+) = b.so_num and

a1.so_line(+) = b.so_line_num)

and exists (select 1

from a1

where (a1.component_gbo = b.item and a1.wbs = b.wbs_num and a1.sales_order = b.so_num and

a1.so_line = b.so_line_num)) /*EQ库存匹配*/

and ((b.scan_check = 1 and b.resrce is not null) or b.scan_check = 0)) /*匹配扫码上料*/

select * from a1 order by erp_step

bom

库存