• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

200分再向高手请教BOM的单位用量计算问题

zanglinfeng 2007-04-18 10:14:37
表结构:

表名ps_mstr
字段:ps_par(父料号)
ps_comp(子料号)
ps_pty_per(单位用量,即一个父料号使用的单位数量,比如ps_qty_per是2.8的话就相当于要做1个ps_par需要用2.8个的ps_comp) .

业务说明:
是产品结构表,比如说成品是FG001,
下面有半成品SFG001,SFG002,SFG003
半成品SFG001下面有中间件WIP001,WIP002;SFG002下有WIP003,WIP004,WIP005;SFG003下面有WIP006,

最后WIP001下面有原材料RAW001,RAW002,WIP002下面有原材料RAW003,WIP003下面有原材料RAW004,RAW005等等。

FG001
|
---------------------------------------------------------------------
| | |
SFG001 SFG002 SFG003
| |
------------------- ---------------------
| | | | |
WIP001 WIP002 WIP003 WIP004 WIP005 WIP006
| | |
RAW001 RAW002 RAW003 RAW004,RAW005 ............



当然这只是一个产品结构,这个粒子只有4层,实际层次可能更多,最多的10多层。
某个半成品或者原材料可能被多个成品用到。

举例数据:
ps_par ps_comp ps_qty_per
FG001 SFG001 1
FG001 SFG002 1
FG001 SFG003 1
SFG001 WIP001 2
SFG001 WIP002 2
SFG002 WIP003 3
SFG002 WIP004 3
SFG002 WIP005 2
SFG003 WIP006 3
WIP001 RAW001 2.66
WIP001 RAW002 2.33
WIP002 RAW003 3.21
WIP003 RAW004 1.89
WIP003 RAW005 1.86


现在需要计算由成品到指定原材料的单位用量,即比如需要计算到成品FG001需要使用多少的RAW001,RAW002,RAW003?

...全文
1435 点赞 收藏 44
写回复
44 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zanglinfeng 2007-04-23
谢谢各位!结贴
回复
QQ454831 2007-04-20
set nocount on
declare @ps_mstr table (ps_par varchar(10),ps_comp varchar(10),qty int)
insert into @ps_mstr
select 'FG001', 'SFG001', 3 union all
select 'FG001' , 'SFG002', 4 union all
select 'FG001' ,'SFG003', 5 union all
select 'SFG001', 'WIP001', 6 union all
select 'SFG001' ,'WIP002', 2 union all
select 'SFG002' ,'WIP001', 3 union all
select 'SFG002' ,'WIP002', 4 union all
select 'SFG002' ,'WIP003', 6 union all
select 'SFG003' ,'WIP001', 7 union all
select 'WIP001' ,'RAW001', 8 union all
select 'WIP001' ,'RAW002' , 9 union all
select 'WIP002' ,'RAW003' , 6 union all
select 'WIP003' ,'RAW004' , 7 union all
select 'WIP003' ,'RAW001' , 8



declare @par varchar(50)

set @par='fg001'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp


set @par='sfg001'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp

set @par='sfg002'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp


set @par='sfg003'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp

set @par='wip001'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp


set @par='wip002'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp


set @par='wip003'
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp



/*
ps_par
--------------------------------------------------
fg001

ps_comp qty
---------- -----------
RAW001 712
RAW002 585
RAW003 132
RAW004 168

ps_par
--------------------------------------------------
sfg001

ps_comp qty
---------- -----------
RAW001 48
RAW002 54
RAW003 12

ps_par
--------------------------------------------------
sfg002

ps_comp qty
---------- -----------
RAW001 72
RAW002 27
RAW003 24
RAW004 42

ps_par
--------------------------------------------------
sfg003

ps_comp qty
---------- -----------
RAW001 56
RAW002 63

ps_par
--------------------------------------------------
wip001

ps_comp qty
---------- -----------
RAW001 8
RAW002 9

ps_par
--------------------------------------------------
wip002

ps_comp qty
---------- -----------
RAW003 6

ps_par
--------------------------------------------------
wip003

ps_comp qty
---------- -----------
RAW001 8
RAW004 7



*/
回复
dephibegin 2007-04-20
楼上的好长
回复
yuemb369 2007-04-20
基本思想:有成品(PNC)逐层往下分解,数据存放在临时表中,相同PNC的数量在#flatbom里没有则新加,有则更新数量,@Level是表示层次的变量。这个存储过程是现在系统正在用的,运行良好,这里取得是符合条件的一组成品分解,最终形成的是多条成品的结果,存放在flatbom这张表中,字段PNCDate表示这次分解的结果范围字段,你可以根据自己的需要只取一条分解,其他的字段是其他逻辑判断用的,与你的问题没什么关系。

对应:PNC-ps_par DirectMaterialID-ps_comp




create PROCEDURE usp_ExpandMultiBOMToFlatBOM
@CurDate datetime,
@WarehouseIDJDE varchar(10)
AS

DELETE FROM FlatBOM WHERE CONVERT(DATETIME,PNCDate,110)= CONVERT(DATETIME,@CurDate,110)
AND PNC IN (SELECT DISTINCT PNC FROM PNCLIST WHERE (Category='COLD' OR Category='WET'))

DECLARE @CurPNC VarChar(28)
DECLARE @CurBM VarChar(28)
DECLARE @CurBMUnit VarChar(8)
DECLARE @CurBMStockingType VarChar(1)
DECLARE @CurBMStockingTypePre VarChar(1)
DECLARE @CurBMQty numeric(10,4)
DECLARE @Line VarChar(28)
DECLARE @Level int
DECLARE @ChildCount int
--创建临时表存放拆分时所需的空间
CREATE TABLE #TempBOM (DirectMaterialID VarChar(28),Level int,UnitMeasure varchar(8),Quantity numeric(10,4),Stockingtype varchar(1),StockingtypePre varchar(1))
CREATE TABLE #TempFlatBOM (PNCDate datetime,PNC varchar(28),DirectMaterialID varchar(28),UnitMeasure varchar(8),Quantity numeric(10,4))
--读取当前日期的物料信息
SELECT *,' ' AS WarehouseID into #BOM FROM DWRDB..BOM WHERE WareHouseIDJDE IS NULL

INSERT into #BOM SELECT *,LTRIM(WarehouseIDJDE) FROM DWRDB..BOM
WHERE substitutepart=0
and rtrim(ltrim(WareHouseIDJDE))=@WarehouseIDJDE
and convert(char(10),@CurDate,112)>=convert(char(10),begindate,112)
and convert(char(10),@CurDate,112)<=convert(char(10),enddate,112)

CREATE INDEX tempbomindex ON #TempBOM(DirectMaterialID,Level)
CREATE INDEX bomindex ON #BOM (ParentItemID,WarehouseID)
CREATE INDEX flatbomindex ON #TempFlatBOM (PNC,DirectMaterialID)

SELECT * INTO #PNCLIST FROM PNCLIST WHERE PNC IS NULL

INSERT INTO #PNCLIST SELECT * FROM PNCLIST WHERE (Category='COLD' OR Category='WET') AND
YEAR(@CurDate)>=Effectiveyear AND YEAR(@CurDate)<=Expiredyear AND SHOWINPPVREPORT=1 ORDER BY PNC
--读取成品列表,并开始进行循环处理
DECLARE bom_cursor CURSOR FOR
SELECT DISTINCT PNC FROM #PNCLIST
OPEN bom_cursor
FETCH NEXT FROM bom_cursor
INTO @CurPNC
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempBOM VALUES(@CurPNC,1,'',1,'S','S')
SELECT @Level = 1,@CurBMQty = 1,@CurBMStockingTypePre = 'S'

WHILE @Level > 0
BEGIN
IF EXISTS (SELECT * FROM #TempBOM WHERE Level = @Level)
BEGIN
SELECT @CurBM = DirectMaterialID,@CurBMQty=sum(Quantity),@CurBMUnit=UnitMeasure,
@CurBMStockingType=Stockingtype,@CurBMStockingTypePre=StockingtypePre
FROM #TempBOM
WHERE Level = @Level
group by DirectMaterialID,UnitMeasure,Stockingtype,StockingtypePre

--1.P&H底下所有非P&H的子项,全部不保留,并且不再分解
--2.P&H所有P&H的子项,如果还有子项,则不保留,但继续分解
--3.P&H底下所有P&H的子项,如果没有子项,则保留

IF @WarehouseIDJDE<>''
SELECT @ChildCount=COUNT(ParentItemID) FROM #BOM
WHERE ParentItemID = @CurBM AND WarehouseID=@WarehouseIDJDE
ELSE
SELECT @ChildCount=COUNT(ParentItemID) FROM #BOM
WHERE ParentItemID = @CurBM AND (WarehouseID='412MNU' OR WarehouseID='197DM')


if ( @ChildCount>0)
BEGIN
IF (SELECT count(pncdate) FROM #TempFlatBOM WHERE PNC=@CurPNC and DirectMaterialID=@CurBM )=0
INSERT into #TempFlatBOM values(@CurDate,@CurPNC,@CurBM,@CurBMUnit,@CurBMQty)
ELSE
UPDATE #TempFlatBOM SET Quantity=Quantity+@CurBMQty
WHERE PNC=@CurPNC and DirectMaterialID=@CurBM
IF @curpnc = '925028308' and @curbm = '209139000'
END

DELETE FROM #TempBOM
WHERE Level = @Level AND DirectMaterialID = @CurBM
and UnitMeasure=@CurBMUnit and Stockingtype=@CurBMStockingType

IF @WarehouseIDJDE<>''
INSERT #TempBOM
SELECT ChildItemID,@Level + 1,UnitMeasure,(CASE QuantityType WHEN 'F' THEN Quantity WHEN '%' THEN Quantity*@CurBMQty/100 ELSE Quantity*@CurBMQty END),
Stockingtype,@CurBMStockingType--@CurBMStockingTypePre
FROM #BOM
WHERE ParentItemID = @CurBM AND WarehouseID=@WarehouseIDJDE
AND PATINDEX('%'+Stockingtype+'%',(CASE WHEN @CurBMStockingType='P' OR @CurBMStockingType='H'
THEN 'PH' ELSE Stockingtype end))>0
ELSE
INSERT #TempBOM
SELECT ChildItemID,@Level + 1,UnitMeasure,(CASE QuantityType WHEN 'F' THEN Quantity WHEN '%' THEN Quantity*@CurBMQty/100 ELSE Quantity*@CurBMQty END),
Stockingtype,@CurBMStockingType--@CurBMStockingTypePre
FROM #BOM
WHERE ParentItemID = @CurBM AND (WarehouseID='412MNU' OR WarehouseID='197DM')
AND PATINDEX('%'+Stockingtype+'%',(CASE WHEN @CurBMStockingType='P' OR @CurBMStockingType='H'
THEN 'PH' ELSE Stockingtype end))>0

IF @@ROWCOUNT > 0
SELECT @Level = @Level + 1
END
ELSE
BEGIN
SELECT @Level = @Level - 1
END
END
FETCH NEXT FROM bom_cursor INTO @CurPNC
END
CLOSE bom_cursor
DEALLOCATE bom_cursor

--删除那部分PNC与子物料号相同的记录
DELETE FROM #TempFlatBOM WHERE PNC=DirectMaterialID
INSERT INTO FlatBOM SELECT * FROM #TempFlatBOM
----*****结果显示
SELECT * FROM FlatBOM where PNCDate=@CurDate order by PNC,DirectMaterialID

DROP INDEX #TempBOM.tempbomindex
DROP INDEX #TempFlatBOM.flatbomindex
DROP INDEX #BOM.bomindex
DROP TABLE #PNCLIST
DROP TABLE #BOM
DROP TABLE #TempBOM
DROP TABLE #TempFlatBOM
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

回复
云中客 2007-04-20
如果有其他的功能或要求,可以在这两个表中增加相应的字段,来达到相应的要求
回复
云中客 2007-04-20
实现此功能,建议不要使用一个表来实现
简单的话可以使用两个表来实现:
1.产品/材料表
此表包括产品/半成品/材料....的基础信息
材料编码,材料名称,单位,...........

2.产品结构表(BOM)
此表中的BOM编码是组成方式为:01 0101 010101....,以此类推,这样可以达到有限级的要求(只要将BOM编码的设为一定长度.此处为每级占两位,则10级共占20位)
BOM编码,材料编码,级数......

3.在执行查询和统计时,由于产品结构的关系由BOM编码直接关联,所以统计/查询时会方便很多
例如:
BOM编码 材料编码 级数 数量
---------------------------------
01 FG001 0
0101 SFG001 1
0102 SFG002 1
010101 WIP001 2
010102 WIP002 2
01010101 RAW001 3 2.66
01010101 RAW002 3 2.33
01010201 RAW001 3 2.66
01010201 RAW002 3 2.33

此时查询统计时可以如此操作:

1)查询统计产品FG001的使用材料数
Select 材料编码,Sum(数量) From BOM表 WHere Bom编码 like '01%'
这样将统计出所有结构中的产品或半产品或材料的数量,如果仅统计材料的数量,可以在材料表中为材料设置标记,以区分是产品还是半产品或是材料
这里的语句只是一个简单的说明,具体执行时可能会复杂一些

回复
QQ454831 2007-04-20
用2005的递归遍历很简单,不用写函数
回复
dephibegin 2007-04-20
也就是个遍历树寻找所有树叶的问题,简单的很
如果写的太复杂难看,看来星星多点的还是厉害些,呵呵
回复
li_net 2007-04-19
学习
回复
playwarcraft 2007-04-19
呵呵,我們公司的bom,最多10層,不過結構比這個複雜點,還需要考慮item以及虛假的phantom
回复
benjoy 2007-04-19
呵呵,我记得是用存储过程来做,将BOM的父子插入表中,反复循环直到无子料为止,N年前负责过这个功能.
回复
xujexy 2007-04-19
你的BOM原形数据,是测试数据吗?
实际工厂使用中没有这么多层BOM,并且每个层次的设计是有讲究的。
我想只有大型垂直化生产的企业才有可能有这么多层次。
回复
fredyj 2007-04-19
mark 学习
回复
jackeyabc 2007-04-19
收藏;有时间再看
回复
CrazyFor 2007-04-19
展BOM的东东

我N年前的一个贴子,有兴趣可以去看一下,可以实现树型目录的大部份功能,两个自定义函数
http://topic.csdn.net/t/20030120/12/1375432.html

可以实现:
得到ID为X的所有下层类别ID串
查询ID为X的所有下层记录
得到ID为X顶层ID
得到ID为X所在枝的所有ID
得到ID为X到顶层的ID串
按照树顺序排序
回复
IT_zen 2007-04-19
libin_ftsafe(子陌红尘) (

是哪位高人?各位介绍一下。
回复
幫忙頂了
回复
playwarcraft 2007-04-18
create table ps_mstr(ps_par varchar(10),ps_comp varchar(10),qty numeric(9,2))
insert into ps_mstr
select 'FG001', 'SFG001', 1 union all
select 'FG001' , 'SFG002', 1 union all
select 'FG001' ,'SFG003', 1 union all
select 'SFG001', 'WIP001', 2 union all
select 'SFG001' ,'WIP002', 2 union all
select 'SFG002' ,'WIP003', 3 union all
select 'SFG002' ,'WIP004', 3 union all
select 'SFG002' ,'WIP005', 2 union all
select 'SFG003' ,'WIP006', 3 union all
select 'WIP001' ,'RAW001', 2.66 union all
select 'WIP001' ,'RAW002' , 2.33 union all
select 'WIP002' ,'RAW003' , 3.21 union all
select 'WIP003' ,'RAW004' , 1.89 union all
select 'WIP003' ,'RAW005' , 1.86

Go
Create function f_cid(@ps_par varchar(10))
returns @t_level table(ps_par varchar(10),ps_comp varchar(10),qty numeric(9,2),level int)
as
begin
declare @level int
set @level=1
insert into @t_level select ps_par,ps_comp,qty,@level from ps_mstr where ps_par=@ps_par
while @@rowcount>0
begin
set @level=@level+1
insert into @t_level select a.ps_par, a.ps_comp,a.qty*b.qty,@level
from ps_mstr a,@t_level b
where a.ps_par=b.ps_comp
and b.level=@level-1
end
return
end

Go
--查找FG001下介所有用料數量
select * from f_cid('FG001') a
/*
ps_par ps_comp qty level
---------- ---------- ----------- -----------
FG001 SFG001 1.00 1
FG001 SFG002 1.00 1
FG001 SFG003 1.00 1
SFG001 WIP001 2.00 2
SFG001 WIP002 2.00 2
SFG002 WIP003 3.00 2
SFG002 WIP004 3.00 2
SFG002 WIP005 2.00 2
SFG003 WIP006 3.00 2
WIP001 RAW001 5.32 3
WIP001 RAW002 4.66 3
WIP002 RAW003 6.42 3
WIP003 RAW004 5.67 3
WIP003 RAW005 5.58 3
*/

--查找FG001最下介所有用料數量
select * from f_cid('FG001') a where not exists(select 1 from ps_mstr where ps_par=a.ps_comp)
/*
ps_par ps_comp qty level
---------- ---------- ----------- -----------
SFG002 WIP004 3.00 2
SFG002 WIP005 2.00 2
SFG003 WIP006 3.00 2
WIP001 RAW001 5.32 3
WIP001 RAW002 4.66 3
WIP002 RAW003 6.42 3
WIP003 RAW004 5.67 3
WIP003 RAW005 5.58 3
*/

drop table ps_mstr
drop function f_cid
回复
playwarcraft 2007-04-18
這個是由上往下展,套用之前帖子里的那2個function中的另一個(查找所有子結點的),差不多就可以解決了
回复
子陌红尘 2007-04-18
以上是一个Example.
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2007-04-18 10:14
社区公告
暂无公告