最新，准确的多层BOM展开sql代码
if exists (select * from dbo.sysobjects where> drop table . GOCREATE TABLE . (
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(19, 6) NULL ,
(8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(19, 6) NULL ,
(3) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
NULL ,
NULL ,
(1) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(2) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(800) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
(18, 6) NULL ,
(18, 6) NULL ,
(18, 6) NULL ,
(18, 6) NULL ,
(18, 6) NULL ,
(18, 6) NULL ,
(18, 6) NULL
) ON
GO
－－－－－－－－－－－存储过程
CREATE proc BOM
@mj nvarchar(20)
as
begin
delete from bomt
declare @l int,@bz int
set @l=0
INSERT INTO bomt
(parent_item, itemname, itemwm,sl, dw, ck, Price, jghb, ceci, scbs, path)
select a.itemcode ,a.itemname,a.FrgnName,1,a.InvntryUom,a.DfltWH,a.LstEvlPric,'RMB',@l,a.TreeType,right(space(20)+a.itemcode,20)
from oitm a
where a.itemcode=@mj
----
set @bz=(select T1.Qauntity from oitt t1 where t1.code=@mj)
while @@rowcount>0
begin
set @l=@l+1
INSERT INTO bomt
(parent_item, itemname,itemwm, sl, dw, ck, Price, jghb, ceci, scbs, path,pl,fhf,hw)
select i.itemcode,i.itemname,i.FrgnName,a.Quantity, i.InvntryUom,a.Warehouse, a.Price, a.Currency,@l,i.TreeType,path+','+str(a.ChildNum,3)+right(space(20)+i.itemcode,20),a.PriceList,a.IssueMthd,i.sww
from (SELECT T1.Father, T1.ChildNum, T1.Code, T1.Quantity/ T0.Qauntity as Quantity , T1.Warehouse, T1.Price, T1.Currency, T1.PriceList, T1.Comment, T1.LogInstanc, T1.Uom,t1.IssueMthd FROM OITT T0INNER JOIN ITT1 T1 ON T0.Code = T1.Father) a, bomt b,oitt c,oitm i
whereb.parent_item=c.code
and c.code=a.father
and a.code= i.itemcode
and b.ceci=@l-1
end
页:
[1]