以下是我目前有關BOM中的所有SQL，你可能需要再修改一下，因為計算BOM需要較長的時間，所以有些過程寫進了自己新建的TABLE.
go
if exists (select 1 from sysobjects where name='fmProc_Bom_DoPItem')drop procedure fmProc_Bom_DoPItem
go
if exists(select 1 from sysobjects where name='fmProc_Bom_GetPItem')drop procedure fmProc_Bom_GetPItem
/*
功能:抓取所有用到此產品內碼的父產品內碼
作者:Victor
日期:2007-8-25
*/
go
create procedure fmProc_Bom_DoPItem
(
@fitemid int
)
as
set nocount on
declare @pitem int
declare pm_cur cursor local for
--取得用此產品編號的父產品 可以修改條件來篩選想要的記錄
select b.fitemid from icbom b inner join (select * from icbomchild where fitemid=@fitemid and FMaterielType=371) a
on a.finterid=b.finterid inner join t_icitem c on b.fitemid=c.fitemid where b. fusestatus=1072 and b.fbomtype=0 and b.fcancellation=0
and c.ferpclsid=2
open pm_cur
fetch pm_cur into @pitem
if @@cursor_rows=0
begin
close pm_cur
deallocate pm_cur
return
end
while @@fetch_status=0
begin
insert into #temp(fitemid) values(@pitem)
exec fmProc_Bom_DoPItem @pitem
fetch next from pm_cur into @pitem
end
close pm_cur
deallocate pm_cur
go
create procedure fmProc_Bom_GetPItem --取此類物料的所有父物料 fmProc_Bom_GetPItem 2606
(
@fitem int
)
as
set nocount on
create table #temp(fitemid int)
exec fmProc_Bom_DoPItem @fitem
select * from #temp
drop table #temp
go
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'fmFun_Base_UnitTrans') DROP function fmFun_Base_UnitTrans
/*
功能:單位數據轉換
作者:Victor
日期:2007-8-25
*/
GO
create function fmFun_Base_UnitTrans(@unit1 as int,@unit2 as int)
--單位轉換 1@unit1=?@unit2
returns decimal(12,5)
begin
if (select count(distinct funitgroupid) from t_measureunit where fmeasureunitid in (@unit1,@unit2))=2 return -1
declare @g int,@du int
select @g=funitgroupid from t_measureunit where fmeasureunitid=@unit1
select @du=fdefaultunitid from t_unitgroup where funitgroupid=@g
declare @ret decimal(12,5)
declare @u1d decimal(12,5),@u2d decimal(12,5)
select @u1d=fcoefficient from t_measureunit where fmeasureunitid=@unit1
select @u2d=fcoefficient from t_measureunit where fmeasureunitid=@unit2
if @du=@u1d
set @ret=@u2d/@u1d
else
set @ret=@u1d/@u2d
return @ret
end
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'fmProc_Bom_Cal') DROP procedure fmProc_Bom_Cal
GO
/*-- =============================================
功能: 累加性的BOM計算,不能單獨調用，只能在 fmProc_Bom_CalQty中調用
參數:
返回:數據集
作者:Victor
日期:2007-08-23
最後作者:
最後日期:
范例:
-- =============================================*/
create procedure fmProc_Bom_Cal --累加性的BOM計算
(
@pid int, @cc bit, @qtymond decimal(12,5) ,@level int,@unitid int
)
as
set nocount on
declare @curlevel int
set @curlevel=@level+1
declare @bomid int,@sonpid int,@scrap decimal(12,5),@qty int,@sonqty decimal(12,5),@unit int,@punit int,@sqty decimal(12,5)
if not exists(select 1 from icbom where fitemid=@pid and fusestatus=1072 and fbomtype=0 and fcancellation=0)return
select @bomid=finterid,@qty=fqty,@punit=funitid from icbom where fitemid=@pid and fusestatus=1072 and fbomtype=0 and fcancellation=0
if @level=0 insert into #retv(curLevel,BomId,ParentPid,SonPid,UnitId,Fqty,FPqty,Fsqty)values(0,@bomid,@pid,@pid,@punit,1,@qty,@qty)
set @qtymond=@qtymond* dbo.fmfun_base_unittrans(@punit,@unitid) --轉換單位
declare pm_cur cursor local for select fitemid,fauxqty/@qty*@qtymond,fscrap,funitid,fauxqty from icbomchild where finterid=@bomid and FMaterielType=371
open pm_cur
fetch pm_cur into @sonpid,@sonqty,@scrap,@unit,@sqty
while @@fetch_status=0
begin
if @cc=1 set @sonqty=@sonqty*(1+@scrap/100)
insert into #retv(curLevel,BomId,ParentPid,SonPid,UnitId,Fqty,FPqty,Fsqty) values(@curlevel,@bomid,@pid,@sonpid,@unit,@sonqty,@qty,@sqty)
exec fmProc_Bom_Cal @sonPid,@cc,@sonqty,@curlevel,@unit
fetch next from pm_cur into @sonpid,@sonqty,@scrap,@unit,@sqty
end
close pm_cur
deallocate pm_cur
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'fmProc_Bom_CalQty') DROP procedure fmProc_Bom_CalQty
GO
/*-- =============================================
功能: 計算子物料在成品物料中的總用量
參數:
返回:數據集
作者:Victor
日期:2007-08-23
最後作者:
最後日期:
范例: fmProc_Bom_CalQty 1,15484,0
-- =============================================*/
create procedure fmProc_Bom_CalQty
(
@sonitem int, --子物料
@parentitem int, --成品物料
@considescrap bit --是否考慮損耗
)
as
declare @level int,@unitid int
set nocount on
set @level=0
-- if not exists(select * from tempdb.dbo.sysobjects where name='#retv')
-- begin
create table #retv
(
curLevel int , --BOM層次級
BomId int, --BOM內碼
ParentPid int , --父物料內碼
SonPid int , --子物料內碼
UnitId int, --單位
Fqty decimal(12,5) , --數量
FPQty decimal(12,5), --BOM中父物料的數量，設計數量
FSQty decimal(12,5) --標准數量
)
-- end
select @unitid=funitid from t_icitem where fitemid=@parentitem
exec fmProc_Bom_Cal @parentitem,@considescrap,1,@level,@unitid
if @sonitem =-1
select * from #retv
else
select * from #retv where sonpid=@sonitem
drop table #retv
go
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'fmProc_Bom_GetQtyAll') DROP procedure fmProc_Bom_GetQtyAll
GO
/*-- =============================================
功能: 計算子物料在成品物料中的總用量
參數:
返回:數據集
作者:Victor
日期:2007-08-23
最後作者:
最後日期:
范例: fmProc_Bom_GetQtyAll '','5.0.0.LDPE.160AT+RTS4968(100)'
*/
create procedure fmProc_Bom_GetQtyAll
(
@pid1 varchar(80),
@pid2 varchar(80),
@cc bit=0
)
as
declare @sonitem int,@parentitem int
set nocount on
select @sonitem=fitemid from t_icitem where fnumber=@pid1
if @sonitem is null set @sonitem=-1
select @parentitem=fitemid from t_icitem where fnumber=@pid2
create table #tmp
(
curLevel int ,
BomId int,
ParentPid int,
SonPid int,
UnitId int,
Fqty decimal(12,5),
fpqty decimal(12,5),
fsqty decimal(12,5)
)
if @parentitem is not null
insert into #tmp exec fmProc_Bom_CalQty @sonitem,@parentitem,@cc
-- insert into #tmp exec fmProc_Bom_CalQty -1,@parentitem,0
-- select * from #tmp
create table #tmpv
(
FItemId int,
FUnitId int,
FQty decimal(12,5)
)
if @sonitem =-1
insert into #tmpv select SonPid,UnitId,Fqty from #tmp
else
insert into #tmpv select sonPid,UnitId,Fqty from #tmp where SonPid=@sonitem
-- select * from #tmpv
drop table #tmp
update a set a.funitid=b.funitid,a.fqty=a.fqty* dbo.fmfun_base_unittrans(a.funitid,b.funitid) from #tmpv a
inner join t_icitem b on a.fitemid=b.fitemid
select b.fnumber,b.fname,b.fmodel,c.fname,a.total from ( select fitemid,funitid, sum(fqty) as total from #tmpv group by fitemid,funitid )a
inner join t_icitem b on a.fitemid=b.fitemid inner join t_measureunit c on a.funitid=c.fmeasureunitid where b.fnumber<>@pid2 order by b.fnumber
drop table #tmpv
go
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'fmProc_Bom_GetItemQty') DROP procedure fmProc_Bom_GetItemQty
GO
/*-- =============================================
功能: 計算子物料在成品物料中的總用量 按產品內碼 只適於1對1的情形
參數:
返回:數據集
作者:Victor
日期:2007-08-23
最後作者:
最後日期:
范例: fmProc_Bom_GetItemQty @sonpid,@ppid,0
*/
create procedure fmProc_Bom_GetItemQty
(
@sonitem int,
@parentitem int,
@cc bit=0
)
as
set nocount on
create table #tmp
(
curLevel int ,
BomId int,
ParentPid int,
SonPid int,
UnitId int,
Fqty decimal(12,5),
fpqty decimal(12,5),
fsqty decimal(12,5)
)
if @parentitem is not null
insert into #tmp exec fmProc_Bom_CalQty @sonitem,@parentitem,@cc
-- insert into #tmp exec fmProc_Bom_CalQty -1,@parentitem,0
-- select * from #tmp
create table #tmpv
(
FItemId int,
FUnitId int,
FQty decimal(12,5),
FPItemId int
)
insert into #tmpv(FItemId,FUnitId,Fqty,FPItemId) select SonPid,UnitId,Fqty,ParentPid from #tmp
-- select * from #tmpv
drop table #tmp
update a set a.funitid=b.funitid,a.fqty=a.fqty* dbo.fmfun_base_unittrans(a.funitid,b.funitid) from #tmpv a
inner join t_icitem b on a.fitemid=b.fitemid
/*
insert into fmtempdb0824(fnumber,fname,fmodel,funit,fqty,fparent) --插入一張物料表中在別處定義的
select b.fnumber,b.fname,b.fmodel,c.fname,a.total,d.fnumber from
( select fitemid,funitid, FPItemId,sum(fqty) as total from #tmpv group by fitemid,funitid,FPItemId )a
inner join t_icitem b on a.fitemid=b.fitemid inner join t_measureunit c on a.funitid=c.fmeasureunitid
inner join t_Icitem d on a.FPItemId=d.fitemid
where b.fitemid<>@parentitem order by b.fnumber
*/
insert into fmtempdb0824(fsonpid,fparentpid,funitid,fqty)
select fitemid, FPItemId,funitid,sum(fqty) from #tmpv where fitemid<>fpitemid group by fitemid,funitid,FPItemId
drop table #tmpv
return 1
go