MRP考虑通知和待出 考虑暂收和待入 两个选项BUG
通知和待出也是如此,因为插入库存脚本没有扣除待出数量
1,考虑暂收和待入时,从采购/委外/制造 在途中扣除了暂收和待入,但未在库存中增加暂收入待入
2,考虑通知和待出时,从增加了通知和待出数据,但在库存中扣作通知和待出。 --插入库存的脚本:
INSERT INTO omrp_orig21(sheet_no,state_type,part_no,diff_code,qty,cust_no)
SELECT 'O1911270002','1',ov_scmmaterial.part_no,ov_scmmaterial.dif_flag,SUM(ov_scmmaterial.total_factqty),''
FROM ov_scmmaterial,
obas_wh
WHERE ov_scmmaterial.wh_no =obas_wh.wh_no AND
obas_wh.wh_mrp ='1' AND
obas_wh.org_no ='0'
GROUP BY ov_scmmaterial.part_no,ov_scmmaterial.dif_flag
HAVING SUM(ov_scmmaterial.total_factqty)<>0
--插入在途的脚本: INSERT INTO omrp_orig22(sheet_no,usable_date,state_type, part_no,diff_code,way_qty, from_sheet_no,from_lot_no,good_rate,cust_no,ord_lot) SELECT 'O1911270002',opur_pur2.affirm_date,CASE WHEN obas_sheet_type.sheet_kind ='PUR05' THEN '1' WHEN obas_sheet_type.sheet_kind ='SFC01' THEN '2' WHEN obas_sheet_type.sheet_kind ='SUB03' THEN '3' END , opur_pur2.part_no,opur_pur2.dif_flag,(CASE WHEN '1'='0' THEN ROUND(opur_pur2.unit_rate*(opur_pur2.sheet_qty- opur_pur2.in_qty- ISNULL(opur_pur2.scrab_qty,0)+ opur_pur2.intl_qty),obas_part1.qty_round) ELSE ROUND(opur_pur2.unit_rate*(opur_pur2.sheet_qty- opur_pur2.in_qty- ISNULL(opur_pur2.scrab_qty,0)+ opur_pur2.intl_qty- opur_pur2.send_tempqty- opur_pur2.spc_tempqty- opur_pur2.in_tempqty), obas_part1.qty_round )END) , opur_pur2.sheet_no,opur_pur2.sheet_lot,opur_pur2.good_rate,ISNULL((SELECT a.cust_no FROM opur_plan1 AS a, opur_plan2 AS b , obas_sheet_type AS c WHERE a.sheet_no =b.sheet_no AND a.sheet_type =c.sheet_type AND c.iscustplan_sw ='1' AND c.act_sw ='1' AND a.org_no ='0' AND b.sheet_lot =opur_pur2.plan_lot),'' ), ISNULL(opur_pur2.ord_lot,'') FROM opur_pur2 , opur_pur1 , obas_sheet_type , obas_part1 WHERE opur_pur2.sheet_no =opur_pur1.sheet_no AND opur_pur2.sheet_sta ='1' AND obas_sheet_type.sheet_type =opur_pur1.sheet_type AND obas_sheet_type.receive_sta ='1' AND obas_sheet_type.plan_sta ='1' AND obas_sheet_type.act_sw ='1' AND opur_pur1.org_no =@org_no AND opur_pur2.part_no =obas_part1.part_no AND obas_part1.org_no =@org_no AND (CASE WHEN '1'='0' THEN ROUND(opur_pur2.unit_rate*(opur_pur2.sheet_qty- opur_pur2.in_qty- ISNULL(opur_pur2.scrab_qty,0)+ opur_pur2.intl_qty),obas_part1.qty_round) ELSE ROUND(opur_pur2.unit_rate*(opur_pur2.sheet_qty- opur_pur2.in_qty- ISNULL(opur_pur2.scrab_qty,0)+ opur_pur2.intl_qty- opur_pur2.send_tempqty- opur_pur2.spc_tempqty- opur_pur2.in_tempqty), obas_part1.qty_round)END)>0
--插入通知和待出 INSERT INTO omrp_orig21(sheet_no,state_type,part_no,diff_code,ord_lot,qty, out_date,cust_no) SELECT 'O1911270002','2',osal_out2.part_no,osal_out2.dif_flag,osal_out2.co_lot, convert(numeric(18,8),(osal_out2.sheet_qty+osal_out2.sheet_qty1)osal_out2.unit_rate), osal_out1.sheet_date,'' FROM osal_out2, osal_out1, osal_ord2, obas_sheet_type WHERE osal_out2.sheet_no=osal_out1.sheet_no AND osal_out1.org_no ='0' AND osal_out1.sheet_sta ='0' AND osal_out2.co_lot =osal_ord2.sheet_lot AND obas_sheet_type.sheet_type =osal_out1.sheet_type AND obas_sheet_type.receive_sta ='1' AND obas_sheet_type.act_sw ='1' AND convert(numeric(18,8),(osal_out2.sheet_qty+osal_out2.sheet_qty1) osal_out2.unit_rate)>0
INSERT INTO omrp_orig21 (sheet_no,state_type,part_no,diff_code,ord_lot,qty,out_date,cust_no)
SELECT 'O1911270002','3',osal_out_notice2.part_no,osal_out_notice2.dif_flag,osal_out_notice2.co_lot,
convert(numeric(18,8),(osal_out_notice2.sheet_qty+
osal_out_notice2.sheet_qty1-
osal_out_notice2.out_temqty-
osal_out_notice2.out_temqty1-
osal_out_notice2.out_qty-
osal_out_notice2.out_qty1) *
osal_out_notice2.unit_rate),osal_out_notice1.sheet_date,''
FROM osal_out_notice1,
osal_out_notice2,
osal_ord2,
obas_sheet_type
WHERE osal_out_notice2.sheet_no=osal_out_notice1.sheet_no AND
osal_out_notice1.org_no ='0' AND
osal_out_notice1.sheet_sta ='1' and
osal_out_notice2.co_lot =osal_ord2.sheet_lot and
obas_sheet_type.sheet_type =osal_out_notice1.sheet_type and
obas_sheet_type.receive_sta ='1' and
obas_sheet_type.act_sw ='1' and
convert(numeric(18,8),(osal_out_notice2.sheet_qty+
osal_out_notice2.sheet_qty1-
osal_out_notice2.out_temqty-
osal_out_notice2.out_temqty1-
osal_out_notice2.out_qty-
osal_out_notice2.out_qty1) *
osal_out_notice2.unit_rate)> 0