?? 生產管理系統實例程序.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 + -