BOMTable BOM主表
BOMID,BomName
BOM bom明细表
BOMID(BOM编码),ITEMID(小BOM或者材料编码),BOMQTY(用量)
bomStock Bom库存表
Bomid stock
现BOM用量已经通过递归求得。如何求BOM需求量(BOM用量-库存)比如:A由2个B和一个ROW1组成,B库存为1,ROW1库存为0。现在要做两个A。
则需要 2*2-1=3个B 和 1个ROW1
BOMID,BomName
BOM bom明细表
BOMID(BOM编码),ITEMID(小BOM或者材料编码),BOMQTY(用量)
bomStock Bom库存表
Bomid stock
现BOM用量已经通过递归求得。如何求BOM需求量(BOM用量-库存)比如:A由2个B和一个ROW1组成,B库存为1,ROW1库存为0。现在要做两个A。
则需要 2*2-1=3个B 和 1个ROW1
ALTER FUNCTION [dbo].[GetBomDosageMul]
(@itemno nvarchar(200),@plancount int)
RETURNs @t_bom TABLE (ItemNo nvarchar(200),
PartNo nvarchar(200), PerQty numeric(10,4), level int)
AS
BEGIN
DECLARE @level int
DECLARE @r int SET @level=1
SET @r=0 INSERT INTO @t_bom
SELECT bomid,ItemID,BOMQty,@level
FROM BOM
WHERE bomid=@itemno SET @r=@@rowcount WHILE @r>0
BEGIN
INSERT INTO @t_bom
SELECT bomid,Itemid,c.bomQty*p.PerQty,@level+1
FROM BOM c,@t_bom p
WHERE c.bomid=p.PartNo
AND p.level=@level SET @r=@@rowcount
SET @level=@level+1
END update @t_bom set PerQty = PerQty * @plancount
INSERT INTO @t_bom
SELECT @itemno,PartNo,sum(PerQty),-1
FROM @t_bom
GROUP BY PartNo DELETE FROM @t_bom WHERE level<>-1 RETURN END
bomid,itemid,perqty,inventqty,level
* * * 10 -1
2:输入各库存数(inventqty)针对@t_bom1计算各层各物料的需求
3:@t_bom1(保留各层结果)计算各层的计划数@qty
3.1输入成品的计划数,计算第二的计划数 perqty*@qty
3.2第二层的需求量:@qty=perqty*@qty-库存(如果库存>perqty*@qty,同时记下库存数A,修改原库存数:
inventqty=inventqty-perqty@@qty)
3.3第三层的计划数=第二层的需求量*perqty,第三层的需求量=第三层的计划数-库存(如果库存>perqty*@qty,同时记下库存数A,修改原库存数:
inventqty=inventqty-perqty@@qty)这样子的想法,还不知道怎么更新库存啊。
(@itemno nvarchar(200),@PlanCount int)
RETURNs @t_bom TABLE (ItemNo nvarchar(200),
PartNo nvarchar(200), PerQty numeric(10,4), level int)
AS
BEGIN
DECLARE @level int
DECLARE @r int SET @level=1
SET @r=0 INSERT INTO @t_bom
SELECT bomid,ItemID,BOMQty*@PlanCount- b.stock,@level
FROM BOM a,stock b
WHERE bomid=@itemno
and a.bomid = b.itemno and a.itemid = b.partnoSET @r=@@rowcount WHILE @r>0
BEGIN
INSERT INTO @t_bom
SELECT bomid,Itemid,c.bomQty*p.PerQty-b.stock,@level+1
FROM BOM c,@t_bom p ,stock b
WHERE c.bomid=p.PartNo
AND p.level=@level
and c.itemid = b.PartNo SET @r=@@rowcount SET @level=@level+1
END RETURN
END 这个函数在算第2层BOM时,有相同材料用在不同的BOM上时数据就错了。它会多减库存。现在不知道如何更新库存
(@itemno nvarchar(200),@plancount int)
RETURNs @t_bom TABLE
(ItemNo nvarchar(200),
PartNo nvarchar(200), PerQty numeric(10,4), level int)
AS
BEGIN
DECLARE @level int
DECLARE @r int SET @level=1
SET @r=0 INSERT INTO @t_bom
SELECT bomid,Itemid,bomQty,@level
FROM BOM
WHERE bomid=@itemno SET @r=@@rowcount WHILE @r>0
BEGIN
INSERT INTO @t_bom
SELECT bomid,Itemid,c.bomQty*p.PerQty,@level+1
FROM BOM c,@t_bom p
WHERE c.bomid=p.PartNo
AND p.level=@level SET @r=@@rowcount
SET @level=@level+1
END update @t_bom set PerQty = PerQty * @plancountINSERT INTO @t_bom
SELECT @itemno,PartNo,sum(PerQty),-1
FROM @t_bom A
GROUP BY PartNo
-----------------MODI BY duanzhi1984------------------
INSERT INTO @t_bom
select ItemNo,PartNo,PerQty-stock,10 from @t_bom A
join bomStock B ON A.PartNo=B.itemID AND level=-1
DELETE FROM @t_bom WHERE level<>10---------------------MODI BY duanzhi1984----------------------RETURN END