生产管理系统实例程序.sql

这是一个用VS2003平台C#的生产管理系统

	CONSTRAINT [PK_mrp物料需求计算] PRIMARY KEY  CLUSTERED 
	(
		[物料编号],
		[年份],
		[计划期]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mrp物料需求历史] WITH NOCHECK ADD 
	CONSTRAINT [PK_mrp物料需求历史] PRIMARY KEY  CLUSTERED 
	(
		[物料编号],
		[年份],
		[计划期]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mrp物料需求历史] ADD 
	CONSTRAINT [mrp物料需求历史_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[mrp物料需求计算] ADD 
	CONSTRAINT [mrp物料需求计算_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[mrp物料需求计算结果] ADD 
	CONSTRAINT [mrp物料需求计算结果_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[主生产计划] ADD 
	CONSTRAINT [主生产计划_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[主生产计划历史] ADD 
	CONSTRAINT [主生产计划历史_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[主需求计划] ADD 
	CONSTRAINT [主需求计划_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[主需求计划历史] ADD 
	CONSTRAINT [主需求计划历史_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[物料清单] ADD 
	CONSTRAINT [物料清单_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

---------------------下面是间接使用的数据表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[库存库]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[库存库]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[采购订单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[采购订单]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[生产订单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[生产订单]
GO
CREATE TABLE [dbo].[库存库] (
	[货号] [char] (14) NOT NULL ,
	[批号] [varchar] (20) NOT NULL ,
	[部门] [varchar] (20) NOT NULL ,
	[组别] [varchar] (20) NOT NULL ,
	[货位] [varchar] (20) NULL ,
	[库存数量] [decimal] (15,2) NOT NULL ,
	[库存金额] [decimal] (15,2) NOT NULL ,
	[库存单价] [decimal] (15,2) NOT NULL ,
	[最新进价] [decimal] (15,2) NULL ,
	[扣率] [decimal] (15,2) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[采购订单] (
	[编号] [char] (14) NOT NULL primary key,
	[合同编号] [char] (14) NULL ,
	[单据号] [char] (14) NULL ,
	[申请编号] [char] (14) NULL ,
	[供货商号] [char] (10) NULL ,
	[物料编号] [char] (14) NULL ,
	[订货数量] [decimal](24, 4) NULL ,
	[进价] [money] NULL ,
	[不含税价] [money] NULL ,
	[税率] [decimal](5, 4) NULL ,
	[税额] [money] NULL ,
	[税价合计] [money] NULL ,
	[交货方式] [varchar] (20) NULL ,
	[订货日期] [int] NULL ,
	[年份] [int] NULL ,
	[计划期] [int] NULL ,
	[订货地点] [varchar] (20) NULL ,
	[订货人] [char] (8) NULL ,
	[送货日期] [int] NULL ,
	[送货数量] [real] NULL ,
	[剩余数量] [decimal](18, 4) NULL ,
	[有效起日] [int] NULL ,
	[有效止日] [int] NULL ,
	[记帐日期] [int] NULL ,
	[记帐人] [char] (8) NULL ,
	[审单人] [char] (8) NULL ,
	[作废标记] [char] (1) NULL ,
	[打印标记] [char] (1) NULL ,
	[订单状态] [char] (6) NULL ,
	[订单说明] [varchar] (40) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[生产订单] (
	[MPS编号] [char] (14) NULL ,
	[编号] [char] (14) Not NULL Primary key,
	[单据号] [char] (14) NULL ,
	[生产批号] [char] (20) NULL ,
	[物料编号] [char] (14) NULL ,
	[年份] [int] NULL ,
	[计划期] [int] NULL ,
	[数量] [decimal] (15,2) NULL ,
	[生产部门] [char] (10) NULL ,
	[下达日期] [int] NULL ,
	[要求完成日期] [int] NULL ,
	[完成数量] [decimal] (15,2) NULL ,
	[剩余数量] [decimal] (15,2) NULL ,
	[记帐人] [varchar] (10) NULL ,
	[记帐日期] [int] NULL ,
	[审核人] [varchar] (10) NULL ,
	[审核日期] [int] NULL ,
	[状态] [char] (10) NULL ,
	[备注] [varchar] (40) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[库存库] WITH NOCHECK ADD 
	CONSTRAINT [DF__库存库__库存数量__6C43F744] DEFAULT (0) FOR [库存数量],
	CONSTRAINT [DF__库存库__库存金额__6D381B7D] DEFAULT (0) FOR [库存金额],
	CONSTRAINT [DF__库存库__库存单价__6E2C3FB6] DEFAULT (0) FOR [库存单价],
	CONSTRAINT [库存库_货号部门组别_FK] PRIMARY KEY  CLUSTERED 
	(
		[货号],
		[批号],
		[部门],
		[组别]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[生产订单] WITH NOCHECK ADD 
	CONSTRAINT [DF_生产订单_完成数量] DEFAULT (0) FOR [完成数量],
	CONSTRAINT [DF_生产订单_剩余数量] DEFAULT (0) FOR [剩余数量]
GO

ALTER TABLE [dbo].[生产订单] ADD 
	CONSTRAINT [生产订单_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

ALTER TABLE [dbo].[采购订单] ADD 
	CONSTRAINT [采购订单_物料编号_fk] FOREIGN KEY 
	(
		[物料编号]
	) REFERENCES [dbo].[物料主文件] (
		[物料编号]
	)
GO

-------------------------存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_mrp结果发布]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_mrp结果发布]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_mrp计算]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_mrp计算]
GO

--------mrp计算存储过程
CREATE PROCEDURE sf_mrp计算 ( @考虑库存等  bit=1, 
	@考虑提前期 bit=1, @考虑损耗 bit=1)
AS
begin tran
	--清除
	delete from mrp物料需求计算
	-- 更新物料清单底层码,计算时使用
	update 物料清单 set 低层码 = 0 where 父项编号 = 0
	-- 循环10次,根据情况,这里默认bom物料的层级不超过10级
	declare @i int
	select @i = 1
	while (@i<=10)
	begin 
		update 物料清单 set 低层码 = b.低层码+1 from 物料清单 as a,
			(select * from 物料清单) as b 
			where a.父项编号 = b.物料编号
		select @i = @i + 1
	end
	--重新生成物料需求计算表,并计算毛需求
	-- 低层码代表物料的层级,使用他来循环
	insert into mrp物料需求计算(物料编号,年份,计划期,毛需求)
		select 物料编号,年份,计划期,sum(MPS数量)
		FROM 主生产计划 group by 物料编号,年份,计划期
	select @i = 1
	while (@i<=10)
	begin 
		-- 没有的先查入记录,因为一个物料可以对应多个父项
		insert into mrp物料需求计算(物料编号,年份,计划期,毛需求)
			select distinct b.物料编号,年份,计划期,0
			FROM mrp物料需求计算 as a, 物料清单 as b
			where rtrim(b.物料编号)+convert(char(4),年份)+
			convert(char(2),计划期)	not in(select rtrim(物料编号)+
			convert(char(4),年份)+convert(char(2),计划期) 
			from mrp物料需求计算)
			and b.父项编号=a.物料编号 and b.低层码=@i
		-- 计算毛需求
		-- 注意考虑损耗的情况
		update mrp物料需求计算 set 毛需求 = c.毛需求 + d.需求
			from mrp物料需求计算 as c, 
				(select b.物料编号,年份,计划期,
				sum((a.毛需求*b.需要数量)/(1-(case when @考虑损耗=1 then 
				isnull(损耗率,0) else 0 end))) as 需求
				FROM mrp物料需求计算 as a, 物料清单 as b
				where b.父项编号=a.物料编号 and b.低层码=@i
				group by b.物料编号,a.年份,a.计划期
			) as d
			where c.物料编号=d.物料编号 and c.年份=d.年份 and c.计划期=d.计划期

		select @i = @i + 1
	end
	--------------- 不考虑提前期的毛需求计算完成
	-- 考虑提前期,计算提前期
	--if (@考虑提前期=1) 
	--begin
		-- 这里不在计算,具体计算方法为将当前计划期的数量移到提前期
		-- 注意跨年度计划期的
	--end

	if (@考虑库存等=1)      
	begin   
		-- 第一次计算,设置期初库存
		-- 这里统一设置起初库存为100
		-- 实际系统应该和库存库及上期情况结合
		update mrp物料需求计算 set 期初库存=100 

		update mrp物料需求计算 set 预计入库 = isnull(预计入库,0) + 
			数量 from mrp物料需求计算 as a, (select sum(剩余数量) as 数量, 
				物料编号,年份,计划期 from 采购订单 
				group by 物料编号,年份,计划期) as b, 物料主文件 as c
			where a.物料编号=b.物料编号 and a.年份=b.年份 
			and a.计划期=b.计划期 and a.物料编号 = c.物料编号 
			and c.计划类别='wg'    -- 外购产品
  
		update mrp物料需求计算 set 预计入库 = isnull(预计入库,0) + 
			数量 from mrp物料需求计算 as a, (select sum(剩余数量) as 数量, 
				物料编号,年份,计划期 from 生产订单 
				group by 物料编号,年份,计划期) as b, 物料主文件 as c
			where a.物料编号=b.物料编号 and a.年份=b.年份 
			and a.计划期=b.计划期 and a.物料编号 = c.物料编号 
			and c.计划类别='bcp'    -- 半成品
		
		-- 这里计算预计出库的情况   
		update mrp物料需求计算 set 预计出库 = isnull(预计出库,0)
   	end

	-- 按照计划期循环计算各个计划期的物料需求
	declare @年份 int, @计划期 int
	declare mycur cursor for select 年份,计划期 from mrp物料需求计算
		order by 年份,计划期
	open mycur
	fetch next from mycur into @年份, @计划期
	WHILE (@@FETCH_STATUS = 0 )
	BEGIN
		-- 如果不是最小的计划期, 计算期初库存
		-- 注意跨年度的计算,这里先不考虑
		update mrp物料需求计算 set 期初库存=b.预计库存 from 
			mrp物料需求计算  as a, (select * from mrp物料需求计算) 
				as b where a.年份=@年份 and a.计划期=@计划期
				and a.年份=b.年份 and a.计划期=b.计划期+1 and a.物料编号=b.物料编号
		
		-- 计算净需求
		update mrp物料需求计算 set 预计库存 = isnull(期初库存,0) - 
			isnull(毛需求,0) + isnull(预计入库,0) - isnull(预计出库,0)
			where 年份=@年份 and 计划期=@计划期
		update mrp物料需求计算 set 净需求=0  where 年份=@年份 and 计划期=@计划期
		-- 预期(期末库存)小于0的存在净需求
		update mrp物料需求计算 set 净需求=-预计库存, 预计库存=0 
			where 年份=@年份 and 计划期=@计划期 and 预计库存<0
		fetch next from mycur into @年份, @计划期
	END
	close mycur
	deallocate mycur
commit
GO

CREATE PROCEDURE sf_mrp结果发布
AS
begin tran
	insert into mrp物料需求历史(发布时间, 物料编号, 年份, 计划期,
		期初库存, 毛需求, 预计入库, 预计出库, 预计库存, 净需求, 
		计划产出, 计划投入) select getdate(), 物料编号, 
		年份, 计划期, 期初库存, 毛需求, 预计入库, 预计出库, 预计库存, 
		净需求, 计划产出, 计划投入 from mrp物料需求计算
	delete from mrp物料需求计算
	insert into 主生产计划历史 select * from 主生产计划
	delete from 主生产计划
commit
GO