SBO部分SQL查询奉献

推荐原创

johnsuny 2007-07-02 18:52:06 博主文章分类：我的工作 ©著作权

©著作权归作者所有：来自51CTO博客作者johnsuny的原创作品，请联系作者获取转载授权，否则将追究法律责任

产品生产主计划

SELECT T0.MsnCode AS '计划单号', T1.ItemCode, T2.itemname AS '产品名称',T1.Quantity as '生产数量', T2.onhand,T2.onorder as '已下单',T1.StartDate, T1.EndDate, T1.BaseDocNum as '订单号',T1.BaseDue as '完工日期',T1.ParentCode as '产品名' FROM OMSN T0
INNER JOIN MSN3 T1 ON T0.AbsEntry = T1.AbsEntry
INNER JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE
WHERE (T0.MsnCode = N'[%0]' OR '[%0]' = '') AND (T1.ItemCode = N'[%1]' OR '[%1]' = '') AND (T1.StartDate = CONVERT(DATETIME, '[%2]', 112) OR '[%2]' = '') AND T2.TREETYPE='p'

材料领用计划

SELECT T0.MsnCode AS '计划单号', T1.ItemCode, T2.itemname AS '产品名称',T1.Quantity as '生产数量', T2.onhand,t2.Onorder as '已订购',T1.StartDate, T1.EndDate, T1.BaseDocNum as '订单号',T1.BaseDue as '完工日期',T1.ParentCode as '产品名' FROM OMSN T0
INNER JOIN MSN3 T1 ON T0.AbsEntry = T1.AbsEntry
INNER JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE
WHERE (T0.MsnCode = N'[%0]' OR '[%0]' = '') AND (T1.ItemCode = N'[%1]' OR '[%1]' = '') AND (T1.StartDate = CONVERT(DATETIME, '[%2]', 112) OR '[%2]' = '') AND T2.TREETYPE='N'

多层BOM

－－－－－－－－中间表－－－
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 COLLATE SQL_Latin1_General_CP850_CI_AS
            and c.code=a.father COLLATE SQL_Latin1_General_CP850_CI_AS
            and a.code= i.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
            and b.ceci=@l-1
      end
-- =============================================
-- 计算bom的准确用量
-- =============================================
DECLARE @yl decimal(18,6),@path nvarchar(800),@cd int,@cec int

DECLARE boms CURSOR
FOR SELECT sl, path ,len(path),ceci FROM bomt where scbs='P' and ceci>0 -----获取是生产件的数据，根据层数逐阶展开

--DECLARE @count smallint
--SELECT @count = 1

OPEN boms
FETCH NEXT FROM boms INTO @yl,@path,@cd,@cec
WHILE (@@fetch_status <> -1)
BEGIN
-------------更新用量
UPDATE bomt
SET sl=@yl*sl
WHERE ceci=@cec+1 ------制定下一层，即限制它的第一阶子件，而不往下阶延伸
                     and left(path,@cd)=@path

                FETCH NEXT FROM boms INTO @yl,@path,@cd,@cec

END

CLOSE boms
DEALLOCATE boms

--------显示
select ceci as 层次,parent_item as 物料, itemname as 物料名称, @bz*sl as 标准设计数量, dw as 单位, ck as 仓库, fhf as 发货方法,scbs as BOM类型 from bomt order by path

end
GO