📄 生产管理系统实例程序.sql
字号:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求计算_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求计算] DROP CONSTRAINT mrp物料需求计算_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求计算结果_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求计算结果] DROP CONSTRAINT mrp物料需求计算结果_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求历史_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求历史] DROP CONSTRAINT mrp物料需求历史_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[物料清单_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[物料清单] DROP CONSTRAINT 物料清单_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生产计划_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主生产计划] DROP CONSTRAINT 主生产计划_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生产计划历史_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主生产计划历史] DROP CONSTRAINT 主生产计划历史_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主需求计划_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主需求计划] DROP CONSTRAINT 主需求计划_物料编号_fk
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主需求计划历史_物料编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主需求计划历史] DROP CONSTRAINT 主需求计划历史_物料编号_fk
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
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
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].[mrp物料需求计算]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mrp物料需求计算]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求计算结果]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mrp物料需求计算结果]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求历史]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mrp物料需求历史]
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 ,
[条码] [char] (14) NULL ,
[物料名称] [varchar] (50) NOT NULL ,
[拼音编码] [char] (10) NULL ,
[计量单位] [char] (10) NOT NULL ,
[规格型号] [char] (20) NULL ,
[计划类别] [char] (5) NULL ,
[状态类别] [char] (5) NULL ,
[归属类别] [char] (5) NULL ,
[价值类别] [char] (1) NULL ,
[物料特性a] [varchar] (40) NULL ,
[物料特性b] [varchar] (40) NULL ,
[物料特性c] [varchar] (40) NULL ,
[物料特性d] [varchar] (40) NULL ,
[长] [decimal](18, 3) NULL ,
[宽] [decimal](18, 3) NULL ,
[高] [decimal](18, 3) NULL ,
[净重] [decimal](18, 3) NULL ,
[品牌] [char] (10) NULL ,
[颜色] [char] (10) NULL ,
[等级] [char] (10) NULL ,
[进货提前期] [int] NULL ,
[准备周期] [int] NULL ,
[生产周期] [int] NULL ,
[最小包装量] [decimal](18, 3) NULL ,
[最低销售量] [decimal](18, 3) NULL ,
[批量] [decimal](18, 4) NULL ,
[最高库存] [decimal](18, 3) NULL ,
[最低库存] [decimal](18, 3) NULL ,
[库存期限] [int] NULL ,
[录入者] [char] (8) NULL ,
[录入日期] [int] NULL ,
[低层码] [int] NULL ,
[指定供货商] [char] (10) NULL ,
[定货策略] [char] (10) NULL ,
[生产车间] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[物料清单] (
[父项编号] [char] (14) NOT NULL ,
[物料编号] [char] (14) NOT NULL ,
[需要数量] [decimal](18, 4) NOT NULL ,
[领料车间] [char] (10) NULL ,
[领料库房] [char] (10) NULL ,
[损耗率] [decimal](5, 4) NULL ,
[bom状态] [char] (1) NULL ,
[bom信息状态] [char] (1) NULL ,
[审核者] [char] (8) NULL ,
[审核日期] [int] NULL ,
[其它事项] [text] NULL ,
[低层码] [int] NULL ,
[展开标记] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[工厂日历] (
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[开始日期] [int] NOT NULL ,
[结束日期] [int] NOT NULL ,
[备注] [varchar] (255) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[主需求计划] (
[编号] [char] (14) NULL ,
[物料编号] [char] (14) NOT NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[开始日期] [int] NULL ,
[结束日期] [int] NULL ,
[需求数量] [decimal] (15,2) NOT NULL ,
[记帐人] [varchar] (10) NULL ,
[修改日期] [int] NULL ,
[状态] [char] (10) NULL default '有效',
[备注] [varchar] (40) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[主需求计划历史] (
[编号] [char] (14) NULL ,
[物料编号] [char] (14) NOT NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[开始日期] [int] NULL ,
[结束日期] [int] NULL ,
[需求数量] [decimal] (15,2) NULL ,
[记帐人] [varchar] (10) NULL ,
[修改日期] [int] NULL ,
[状态] [char] (10) NULL ,
[备注] [varchar] (40) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[主生产计划] (
[物料编号] [char] (14) NOT NULL ,
[编号] [char] (14) NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[开始日期] [int] NULL ,
[结束日期] [int] NULL ,
[期初库存] [decimal] (15,2) NULL ,
[需求数量] [decimal] (15,2) NULL ,
[MPS数量] [decimal] (15,2) NOT NULL ,
[生产单数量] [decimal] (15,2) NULL ,
[预计库存] [decimal] (15,2) NULL ,
[记帐人] [varchar] (10) NULL ,
[修改日期] [int] NULL ,
[审核人] [varchar] (10) NULL ,
[审核日期] [int] NULL ,
[状态] [char] (10) NULL default '有效',
[备注] [varchar] (40) NULL
) ON [PRIMARY]
GO
-- 对主生产计划进行编号
CREATE trigger INSERT_主生产计划
on 主生产计划
for INSERT
AS
declare @bh1 varchar(16)
declare @bh2 varchar(16)
select @bh1 = isnull(max(编号),'MPS0000000000') from 主生产计划
select @bh2 = isnull(max(编号),'MPS0000000000') from 主生产计划历史
if(@bh2>@bh1) select @bh1 = @bh2
declare @no varchar(10)
select @no = substring(@bh1,4,10)
select @no = convert(char(10),convert(int,@no)+1)
select @no = REPLICATE('0',10-LEN(@no))+@no
update 主生产计划 set 编号='MPS' + @no where 编号 is null
GO
CREATE TABLE [dbo].[主生产计划历史] (
[物料编号] [char] (14) NOT NULL ,
[编号] [char] (14) NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[开始日期] [int] NULL ,
[结束日期] [int] NULL ,
[期初库存] [decimal] (15,2) NULL ,
[需求数量] [decimal] (15,2) NULL ,
[MPS数量] [decimal] (15,2) 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
CREATE TABLE [dbo].[mrp物料需求计算] (
[物料编号] [char] (14) NOT NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[期初库存] [decimal](18, 4) NULL ,
[毛需求] [decimal](18, 4) NULL ,
[预计入库] [decimal](18, 4) NULL ,
[预计出库] [decimal](18, 4) NULL ,
[预计库存] [decimal](18, 4) NULL ,
[净需求] [decimal](18, 4) NULL ,
[计划产出] [decimal](18, 4) NULL ,
[计划投入] [decimal](18, 4) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[mrp物料需求历史] (
[发布编号] [char] (20) NULL ,
[发布时间] [datetime] NULL ,
[物料编号] [char] (14) NOT NULL ,
[年份] [int] NOT NULL ,
[计划期] [int] NOT NULL ,
[期初库存] [decimal](18, 4) NULL ,
[毛需求] [decimal](18, 4) NULL ,
[预计入库] [decimal](18, 4) NULL ,
[预计出库] [char] (10) NULL ,
[预计库存] [decimal](18, 4) NULL ,
[净需求] [decimal](18, 4) NULL ,
[计划产出] [decimal](18, 4) NULL ,
[计划投入] [decimal](18, 4) NULL
) ON [PRIMARY]
GO
--- 这个视图是为了查询时显示物料名称可以用table控件,而不必使用query控件
if exists (select * from sysobjects where id = object_id(N'[dbo].[vmrp物料需求历史]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vmrp物料需求历史]
GO
create view vmrp物料需求历史
as
select a.*, b.物料名称 from mrp物料需求历史 as a,
物料主文件 as b where a.物料编号=b.物料编号
go
CREATE TABLE [dbo].[物料状态类别] (
[类别代码] [char] (5) NOT NULL Primary key,
[状态描述] [char] (20) NOT NULL ,
[说明] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[物料计划类别] (
[类别代码] [char] (5) NOT NULL Primary key,
[类别名称] [char] (20) NOT NULL
) ON [PRIMARY]
GO
-----------------------------------------
ALTER TABLE [dbo].[物料主文件] WITH NOCHECK ADD
CONSTRAINT [DF__物料主文件__进货提前期__160F4887] DEFAULT (0) FOR [进货提前期],
CONSTRAINT [DF__物料主文件__准备周期__17036CC0] DEFAULT (0) FOR [准备周期],
CONSTRAINT [DF__物料主文件__生产周期__17F790F9] DEFAULT (0) FOR [生产周期],
CONSTRAINT [PK__物料主文件__151B244E] PRIMARY KEY CLUSTERED
(
[物料编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[物料清单] WITH NOCHECK ADD
CONSTRAINT [DF_物料清单_展开标记] DEFAULT (0) FOR [展开标记],
CONSTRAINT [PK_物料清单] PRIMARY KEY CLUSTERED
(
[父项编号],
[物料编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[工厂日历] WITH NOCHECK ADD
CONSTRAINT [PK__工厂日历__1F98B2C1] PRIMARY KEY CLUSTERED
(
[年份],
[计划期]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[主需求计划] WITH NOCHECK ADD
CONSTRAINT [PK_主需求计划] PRIMARY KEY CLUSTERED
(
[物料编号],
[年份],
[计划期]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[主需求计划历史] WITH NOCHECK ADD
CONSTRAINT [PK_主需求计划历史] PRIMARY KEY CLUSTERED
(
[物料编号],
[年份],
[计划期]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[主生产计划] WITH NOCHECK ADD
CONSTRAINT [PK_主生产计划] PRIMARY KEY CLUSTERED
(
[年份],
[计划期],
[物料编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[主生产计划历史] WITH NOCHECK ADD
CONSTRAINT [PK_主生产计划历史] PRIMARY KEY CLUSTERED
(
[年份],
[计划期],
[物料编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mrp物料需求计算] WITH NOCHECK ADD
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -