ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 最新,准确的多层BOM展开sql代码

最新,准确的多层BOM展开sql代码

原创 Linux操作系统 作者:alpheren 时间:2009-01-07 16:03:13 0 删除 编辑
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bomt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [dbo].[bomt]
  GO
  
  CREATE TABLE [dbo].[bomt] (
  [parent_item] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [itemname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [sl] [numeric](19, 6) NULL ,
  [dw] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [ck] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [Price] [numeric](19, 6) NULL ,
  [jghb] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [pl] [smallint] NULL ,
  [ceci] [int] NULL ,
  [fhf] [char] (1) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [scbs] [char] (2) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [path] [nvarchar] (800) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [gw] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [gx] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [cardcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [gysn] [nvarchar] (200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [gc] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [hw] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [itemwm] [nvarchar] (200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [tzs] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [scf] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [flf] [nvarchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
  [mjg] [numeric](18, 6) NULL ,
  [mje] [numeric](18, 6) NULL ,
  [je] [numeric](18, 6) NULL ,
  [xjg] [numeric](18, 6) NULL ,
  [rjg] [numeric](18, 6) NULL ,
  [rje] [numeric](18, 6) NULL ,
  [bje] [numeric](18, 6) NULL
  ) ON [PRIMARY]
  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 T0 INNER JOIN ITT1 T1 ON T0.Code = T1.Father) a, bomt b,oitt c,oitm i
   where b.parent_item=c.code
   and c.code=a.father
   and a.code= i.itemcode
   and b.ceci=@l-1
   end

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/99072/viewspace-531655/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-01

  • 博文量
    95
  • 访问量
    131044