MRP计算速度本来就慢,我的软件经过测试,计算100个订单,每个订单大概有物料20个,
总共要发时间15-20分钟.超时过期的解决办法设定ADO的CommandTimeOut=0即可.
下面是我的BOM表结构:
--BOM主表(ICBOM)
CREATE TABLE dbo.ICBOM
(
FInterID int NOT NULL, --内码
FBOMNumber varchar(300) NOT NULL, --BOM表编号
FUseStatus int DEFAULT 1073 NULL, --使用状态
FVersion varchar(300) DEFAULT '1.0' NOT NULL, --版本号
FParentID int NULL, --父结点
FItemID int NOT NULL, --物料内码
FQty decimal(28,10) NOT NULL, --数量
FYield decimal(28,10) NULL, --成品率
FCheckerID int NULL, --审核人
FCheckDate datetime NULL, --审核日期
FOperatorID int NULL, --操作人员
FEnterTime datetime NOT NULL, --建立日期
FStatus smallint NOT NULL, --状态
FCancellation bit DEFAULT 0 NOT NULL, --作废标记
FTranType int DEFAULT 50 NOT NULL, --单据类型
FRoutingID int DEFAULT 0 NOT NULL, --默认工艺路线
FNote varchar(300) DEFAULT '' NOT NULL, --备注
CONSTRAINT Prm_ICBOM
PRIMARY KEY CLUSTERED (FInterID)
ON [PRIMARY]
)
CREATE UNIQUE INDEX Unq_ICBOM
ON dbo.ICBOM(FBOMNumber)
ON [PRIMARY]
go
CREATE TRIGGER [ICBOM_DEL] ON [dbo].[ICBOM] FOR DELETE AS
DELETE a FROM ICBomChild a,DELETED b WHERE a.FInterID=b.FInterID
--BOM表从表
CREATE TABLE dbo.ICBOMChild
(
FInterID int NOT NULL, --内码
FEntryID int NOT NULL, --序号
FItemID int NOT NULL, --物料内码
FAuxQty decimal(28,10) DEFAULT 0 NOT NULL, --用量
FQty decimal(28,10) DEFAULT 0 NOT NULL, --基本单位用量
FScrap decimal(28,10) NOT NULL, --损耗率
FOperID int DEFAULT 0 NOT NULL, --工序
FMachinePos varchar(300) NULL, --工位
FNote varchar(300) NULL, --备注
FMaterielType int DEFAULT 371 NOT NULL, --物料类型
FOffSetDay decimal(28,10) DEFAULT 0 NOT NULL, --提前期偏置
FBackFlush int DEFAULT 1059 NOT NULL, --是否倒冲
FStockID int DEFAULT 0 NULL, --仓库
CONSTRAINT Prm_ICBOMChild
PRIMARY KEY CLUSTERED (FInterID,FEntryID)
ON [PRIMARY]
)
CREATE TRIGGER [t_ICBomChild] ON [dbo].[ICBOMChild]
FOR INSERT,UPDATE AS
UPDATE a SET a.FQty=b.FAuxQty FROM ICBomChild a,INSERTED b
WHERE a.FInterID=b.FInterID AND a.FEntryID=b.FEntryID
--BOM多阶展开的代码
CREATE procedure PlanMutiBOMExpand
(
@BOMInterID int, --BOM表内码
@Order smallint, --排序方式 0 自然顺序 ，1 物料代码，2 物料名称
@Succeed smallint output, --是否成功
@Reason varchar(400) output --失败原因
)
AS
set nocount on
declare
@BOMMaxLevel smallint,
@BOMLevel smallint,
@SelectRows int,
@ParentBOMInterID int,
@FBOMInterID int,
@ParentNeedQty decimal(28,14),
@ParentIndex int,
@ParentItemID int,
@ParentHaveMrp int,
@ParentLevelString varchar(200),
@ParentBomLevel int,
@FItemType int
--创建临时表
create table #MutiData(
FIndex int identity, --内码
FEntryID int, --序号
FBOMInterID int, --BOM内码
FItemID int, --物料内码
FNeedQty decimal(28,14) default(0) NULL, --需求量
FBOMLevel int NULL, --阶次
FParentID int default(0) NULL, --父结点内码
FRate decimal(28,14) default(0) NULL, --比率
FScrap decimal(28,14) default(0) NULL, --损耗率
FHaveMrp smallint default(0) NULL, --展开标记
FItemType int NULL, --项的类型
FLevelstring varchar(50) NULL, --显示阶次
FOperID int NULL, --工序
FMachinePos varchar(300) NULL, --工位
FNote varchar(300) NULL, --备注
FMaterielType int NULL, --子项类型
FOffSetDay decimal(28,10) NULL, --提前期偏置
FBackFlush int NULL, --是否倒冲
FStockID int NULL --仓库
)
create table #MutiParentItem(
FIndex int identity,
FEntryID int,
FBOMInterID int,
FItemID int,
FNeedQty decimal(28,14) default(0) NULL,
FBOMLevel int NULL,
FParentID int default(0) NULL,
FRate decimal(28,14) default(0) NULL,
FScrap decimal(28,14) default(0) NULL,
FHaveMrp smallint default(0) NULL,
FItemType int,
FLevelstring varchar(50) NULL,
FOperID int NULL,
FMachinePos varchar(300) NULL,
FNote varchar(300) NULL,
FMaterielType int NULL,
FOffSetDay decimal(28,10) NULL,
FBackFlush int NULL,
FStockID int NULL
)
select @BOMMaxLevel=50
select @BOMLevel=0
select @selectRows=0
select @Succeed=0
select @Reason=''
--把要展开的BOM表放入临时表#MutiParentItem中
insert into #MutiParentItem(FBOMInterID,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType)
select a.FInterID,a.FItemID,a.FQty,0,0,b.FErpClsID FItemtype from ICBOM a inner join t_ICITem b on a.FItemID=b.FItemID
where (b.FErpClsID=2 or b.FErpClsID=3 or b.FErpClsID=5) and a.FInterID=@BOMInterID
select top 1 @FBOMInterID=u1.FBOMInterID,@ParentIndex=u1.FIndex,@ParentItemID=u1.FItemID,@ParentNeedQty=u1.FNeedQty,
@ParentBOMLevel=u1.FBOMLevel,@ParentHaveMrp=u1.FHaveMrp,@ParentLevelString=u1.FLevelString,@FItemType=FItemType
from #MutiParentItem u1 order by FIndex desc
select @SelectRows=count(*) from #MutiParentItem
while(@BOMLevel<@BOMMaxLevel and @SelectRows>0)
begin
set @SelectRows=0
select top 1 @Parentindex=u1.FIndex,@ParentItemID=u1.FItemID,@ParentNeedQty=u1.FNeedQty,@ParentBOMLevel=u1.FBOMLevel,
@FItemType=FItemType,@ParentHaveMrp=u1.FHaveMrp,@ParentLevelString=u1.FLevelString
from #MutiParentItem u1 order by FIndex desc
if @ParentHaveMrp>0
begin
insert into #mutidata(FEntryID,FBOMInterID,FItemID,FNeedQty,FBOMLevel,FParentID,FRate,FScrap,FLevelString,FItemType,
FOperID,FMachinePos,FNote,FMaterielType,FOffSetDay,FBackFlush,FStockID)
select u1.FEntryID,u1.FBOMInterID,u1.FItemID,u1.FNeedQty,u1.FBOMLevel,u1.FParentiD,u1.FRate,u1.FScrap,
u1.FLevelstring,u1.FItemType,u1.FOperID,u1.FMachinePos,u1.FNote,u1.FMaterielType,u1.FOffSetDay,u1.FBackFlush,u1.FStockID
from #MutiParentItem u1 where u1.FIndex=@ParentIndex
delete from #MutiParentItem where FIndex=@ParentIndex
end else
begin
update #MutiParentItem set FHaveMrp=1 where FIndex=@ParentIndex
set @SelectRows=0
set @BOMLevel=@ParentBOMLevel+1
if @BOMLevel=50
select @ParentHaveMrp=1
if @Order=0 --自然排序
insert into #MutiParentItem(FEntryID,FBOMInterID,FItemID,FNeedQty,FBOMLevel,FParentiD,FRate,FScrap,FItemType,
FOperID,FMachinePos,FNote,FMaterielType,FOffSetDay,FBackFlush,FStockID)
select v2.FEntryID,v2.FInterID,v2.FItemID,convert(decimal(28,14),@ParentNeedQty)*convert(decimal(28,14),(v2.FQty/v1.FQty)) FNeedQty,
(@BOMLevel) FBOMLevel,@ParentIndex FPartentID,v2.FQty/v1.FQty FRate,v2.FScrap,t1.FErpClsID FItemType,
v2.FOperID,v2.FMachinePos,v2.FNote,v2.FMaterielType,v2.FOffSetDay,v2.FBackFlush,v2.FStockID
from ICBOM v1,ICBOMChild v2,t_ICItem t1
where v1.FItemID=@ParentItemID and v2.FInterID=v1.FInterID and v1.FUseStatus=1072 and t1.FItemID=v2.FitemID
else
if @Order=1
insert into #MutiParentItem(FEntryID,FBOMInterID,FItemID,FNeedQty,FBOMLevel,FParentiD,FRate,FScrap,FItemType,
FOperID,FMachinePos,FNote,FMaterielType,FOffSetDay,FBackFlush,FStockID)
select v2.FEntryID,v2.FInterID,v2.FItemID,convert(decimal(28,14),@ParentNeedQty)*convert(decimal(28,14),(v2.FQty/v1.FQty)) FNeedQty,
(@BOMLevel) FBOMLevel,@ParentIndex FPartentID,v2.FQty/v1.FQty FRate,v2.FScrap,t1.FErpClsID FItemType,
v2.FOperID,v2.FMachinePos,v2.FNote,v2.FMaterielType,v2.FOffSetDay,v2.FBackFlush,v2.FStockID
from ICBOM v1,ICBOMChild v2,t_ICItem t1
where v1.FItemID=@ParentItemID and v2.FInterID=v1.FInterID and v1.FUseStatus=1072 and t1.FItemID=v2.FitemID
order by t1.FNumber
else
insert into #MutiParentItem(FEntryID,FBOMInterID,FItemID,FNeedQty,FBOMLevel,FParentiD,FRate,FScrap,FItemType,
FOperID,FMachinePos,FNote,FMaterielType,FOffSetDay,FBackFlush,FStockID)
select v2.FEntryID,v2.FInterID,v2.FItemID,convert(decimal(28,14),@ParentNeedQty)*convert(decimal(28,14),(v2.FQty/v1.FQty)) FNeedQty,
(@BOMLevel) FBOMLevel,@ParentIndex FPartentID,v2.FQty/v1.FQty FRate,v2.FScrap,t1.FErpClsID FItemType,
v2.FOperID,v2.FMachinePos,v2.FNote,v2.FMaterielType,v2.FOffSetDay,v2.FBackFlush,v2.FStockID
from ICBOM v1,ICBOMChild v2,t_ICItem t1
where v1.FItemID=@ParentItemID and v2.FInterID=v1.FInterID and v1.FUseStatus=1072 and t1.FItemID=v2.FitemID
order by t1.FName
end
select @SelectRows=count(*) from #MutiParentItem
end
if @BOMLevel>=50
begin
set @Succeed=1
set @Reason='BOM结构不安全，可能出现嵌套！BOM展开无法继续！'
end
update #Mutidata set FLevelstring=replicate('.',FBOMLevel)+convert(varchar(4),FBOMLevel)
select a.FLevelstring,a.FItemID,b.FShortNumber FItemIDNumber,b.FName,b.FModel,b.FUnitID,c.FName FUnitIDName,a.FNeedQty,a.FRate,a.FScrap,
a.FOperID,d.FName FOperIDName,a.FMachinePos,a.FNote,a.FMaterielType,e.FName FMaterielTypeName,
a.FOffSetDay,a.FBackFlush, f.FName FBackFlushName,a.FStockID,g.FName FStockIDName,b.FUseState,h.FName FUseStateName
from #MutiData a
LEFT JOIN t_ICItem b on a.FItemID=b.FItemID
LEFT JOIN t_MeasureUnit c on b.FUnitID=c.FItemID
LEFT JOIN t_SubMessage d on a.FOperID=d.FInterID
Left Join t_SubMessage e on a.FMaterielType=e.FInteriD
Left Join t_SubMessage f on a.FBackFlush=f.FInterID
left join t_Stock g on a.FStockID=g.FItemID
Left join t_SubMessage h on b.FUseState=h.FInterID
where a.FLevelstring<>'0' order by FIndex desc
drop table #MutiParentitem
drop table #MutiData
return
参考了K3的代码
