基于MySQL递归方法with recursive,展开一个主计划项目,获得各子项物料的毛需求和需求日期。其中,存储过程参数p_mpsid是主计划项目id,p_maxlev是允许的BOM最大层数。date_lead是自定义函数,用于计算子项的需求日期。这个需求日期是基于工厂日历的需求日期。
/*
毛需求计算程序 test_decompose(p_mpsid int,p_maxlev int)
其中,p_mpsid 是主计划ID,p_maxlev是允许的BOM最大层数。
programed by fuerquan,copyright 2023,2030
*/
CREATE PROCEDURE test_decompose (p_mpsid int,p_maxlev int)
BEGIN
with recursive gross_required (mps_id, master_no, is_merge,
item_no, qty, lead_time, date_req, lv, item_name, measure)
as (
select m.mps_id, m.master_no, m.is_merge, m.product_no, m.qty,
i.lead_time, m.due_date, 1, m.product_name, m.measure
from yewPP.master_schedules m, yewTE.items i
where i.item_no = m.product_no and mps_id = p_mpsid
union all
select g.mps_id, g.master_no, g.is_merge, b.component_no,
g.qty*b.qty_per,
b.lead_time, date_lead(g.date_req, g.lead_time), lv+1,
b.component_name, b.measure
from gross_required g, yewTE.v_bills b
where (b.item_no = g.item_no) and g.lv = lv and lv <= p_maxlev
)
select lv BOM层, item_no 物料编码, item_name 名称, lead_time 提前期,
qty 需求数量, measure 单位, date_req 需求日期
from gross_required;
END