SBO 部分SQL查询-爱ERP网

SBO 部分SQL查询

作者：johnny 文章来源：本站原创点击数：更新时间：2008-7-9

上一篇文章：你一定要知道的SBO开发五大原则

产品生产主计划
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