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

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

zanglinfeng 2007-04-18 10:14:37

ps_comp（子料号）
ps_pty_per（单位用量，即一个父料号使用的单位数量，比如ps_qty_per是2.8的话就相当于要做1个ps_par需要用2.8个的ps_comp） .

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

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

...全文
1435 点赞 收藏 44

44 条回复

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

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

1.产品/材料表

2.产品结构表(BOM)

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

dephibegin 2007-04-20

li_net 2007-04-19

playwarcraft 2007-04-19

benjoy 2007-04-19

xujexy 2007-04-19

fredyj 2007-04-19
mark 学习

jackeyabc 2007-04-19

CrazyFor 2007-04-19

http://topic.csdn.net/t/20030120/12/1375432.html

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

MS-SQL Server

3.3w+

MS-SQL Server相关内容讨论专区

2007-04-18 10:14