?? 生產(chǎn)管理系統(tǒng)實(shí)例程序.sql

?? 生產(chǎn)管理系統(tǒng)。vb.net sql2
?? SQL
?? 第 1 頁(yè) / 共 2 頁(yè)
字號(hào):
12 下一頁(yè)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求計(jì)算_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求計(jì)算] DROP CONSTRAINT mrp物料需求計(jì)算_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求計(jì)算結(jié)果_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求計(jì)算結(jié)果] DROP CONSTRAINT mrp物料需求計(jì)算結(jié)果_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求歷史_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[mrp物料需求歷史] DROP CONSTRAINT mrp物料需求歷史_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[物料清單_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[物料清單] DROP CONSTRAINT 物料清單_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生產(chǎn)計(jì)劃_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主生產(chǎn)計(jì)劃] DROP CONSTRAINT 主生產(chǎn)計(jì)劃_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生產(chǎn)計(jì)劃歷史_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主生產(chǎn)計(jì)劃歷史] DROP CONSTRAINT 主生產(chǎn)計(jì)劃歷史_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主需求計(jì)劃_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主需求計(jì)劃] DROP CONSTRAINT 主需求計(jì)劃_物料編號(hào)_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主需求計(jì)劃歷史_物料編號(hào)_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[主需求計(jì)劃歷史] DROP CONSTRAINT 主需求計(jì)劃歷史_物料編號(hào)_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].[主需求計(jì)劃]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[主需求計(jì)劃]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主需求計(jì)劃歷史]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[主需求計(jì)劃歷史]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生產(chǎn)計(jì)劃]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[主生產(chǎn)計(jì)劃]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主生產(chǎn)計(jì)劃歷史]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[主生產(chǎn)計(jì)劃歷史]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求計(jì)算]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mrp物料需求計(jì)算]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrp物料需求計(jì)算結(jié)果]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mrp物料需求計(jì)算結(jié)果]
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].[物料狀態(tài)類別]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[物料狀態(tài)類別]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[物料計(jì)劃類別]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[物料計(jì)劃類別]
GO

-------------------------------------------
CREATE TABLE [dbo].[物料主文件] (
	[物料編號(hào)] [char] (14) NOT NULL ,
	[條碼] [char] (14) NULL ,
	[物料名稱] [varchar] (50) NOT NULL ,
	[拼音編碼] [char] (10) NULL ,
	[計(jì)量單位] [char] (10) NOT NULL ,
	[規(guī)格型號(hào)] [char] (20) NULL ,
	[計(jì)劃類別] [char] (5) NULL ,
	[狀態(tài)類別] [char] (5) NULL ,
	[歸屬類別] [char] (5) NULL ,
	[價(jià)值類別] [char] (1) NULL ,
	[物料特性a] [varchar] (40) NULL ,
	[物料特性b] [varchar] (40) NULL ,
	[物料特性c] [varchar] (40) NULL ,
	[物料特性d] [varchar] (40) NULL ,
	[長(zhǎng)] [decimal](18, 3) NULL ,
	[寬] [decimal](18, 3) NULL ,
	[高] [decimal](18, 3) NULL ,
	[凈重] [decimal](18, 3) NULL ,
	[品牌] [char] (10) NULL ,
	[顏色] [char] (10) NULL ,
	[等級(jí)] [char] (10) NULL ,
	[進(jìn)貨提前期] [int]  NULL ,
	[準(zhǔn)備周期] [int]  NULL ,
	[生產(chǎn)周期] [int]  NULL ,
	[最小包裝量] [decimal](18, 3) NULL ,
	[最低銷售量] [decimal](18, 3) NULL ,
	[批量] [decimal](18, 4) NULL ,
	[最高庫(kù)存] [decimal](18, 3) NULL ,
	[最低庫(kù)存] [decimal](18, 3) NULL ,
	[庫(kù)存期限] [int] NULL ,
	[錄入者] [char] (8) NULL ,
	[錄入日期] [int] NULL ,
	[低層碼] [int] NULL ,
	[指定供貨商] [char] (10) NULL ,
	[定貨策略] [char] (10) NULL ,
	[生產(chǎn)車(chē)間] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[物料清單] (
	[父項(xiàng)編號(hào)] [char] (14) NOT NULL ,
	[物料編號(hào)] [char] (14) NOT NULL ,
	[需要數(shù)量] [decimal](18, 4) NOT NULL ,
	[領(lǐng)料車(chē)間] [char] (10) NULL ,
	[領(lǐng)料庫(kù)房] [char] (10) NULL ,
	[損耗率] [decimal](5, 4) NULL ,
	[bom狀態(tài)] [char] (1) NULL ,
	[bom信息狀態(tài)] [char] (1) NULL ,
	[審核者] [char] (8) NULL ,
	[審核日期] [int] NULL ,
	[其它事項(xiàng)] [text] NULL ,
	[低層碼] [int] NULL ,
	[展開(kāi)標(biāo)記](méi) [bit] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[工廠日歷] (
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[開(kāi)始日期] [int] NOT NULL ,
	[結(jié)束日期] [int] NOT NULL ,
	[備注] [varchar] (255) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[主需求計(jì)劃] (
	[編號(hào)] [char] (14) NULL ,
	[物料編號(hào)] [char] (14) NOT NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[開(kāi)始日期] [int] NULL ,
	[結(jié)束日期] [int] NULL ,
	[需求數(shù)量] [decimal] (15,2) NOT NULL ,
	[記帳人] [varchar] (10) NULL ,
	[修改日期] [int] NULL ,
	[狀態(tài)] [char] (10) NULL default '有效',
	[備注] [varchar] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[主需求計(jì)劃歷史] (
	[編號(hào)] [char] (14) NULL ,
	[物料編號(hào)] [char] (14) NOT NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[開(kāi)始日期] [int] NULL ,
	[結(jié)束日期] [int] NULL ,
	[需求數(shù)量] [decimal] (15,2) NULL ,
	[記帳人] [varchar] (10) NULL ,
	[修改日期] [int] NULL ,
	[狀態(tài)] [char] (10) NULL ,
	[備注] [varchar] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[主生產(chǎn)計(jì)劃] (
	[物料編號(hào)] [char] (14) NOT NULL ,
	[編號(hào)] [char] (14) NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[開(kāi)始日期] [int] NULL ,
	[結(jié)束日期] [int] NULL ,
	[期初庫(kù)存] [decimal] (15,2) NULL ,
	[需求數(shù)量] [decimal] (15,2) NULL ,
	[MPS數(shù)量] [decimal] (15,2) NOT NULL ,
	[生產(chǎn)單數(shù)量] [decimal] (15,2) NULL ,
	[預(yù)計(jì)庫(kù)存] [decimal] (15,2) NULL ,
	[記帳人] [varchar] (10) NULL ,
	[修改日期] [int] NULL ,
	[審核人] [varchar] (10) NULL ,
	[審核日期] [int] NULL ,
	[狀態(tài)] [char] (10) NULL default '有效',
	[備注] [varchar] (40) NULL 
) ON [PRIMARY]
GO

-- 對(duì)主生產(chǎn)計(jì)劃進(jìn)行編號(hào)
CREATE trigger INSERT_主生產(chǎn)計(jì)劃
	on 	主生產(chǎn)計(jì)劃
	for	INSERT
AS
	declare @bh1 varchar(16)
	declare @bh2 varchar(16)
	select @bh1 = isnull(max(編號(hào)),'MPS0000000000') from 主生產(chǎn)計(jì)劃
	select @bh2 = isnull(max(編號(hào)),'MPS0000000000') from 主生產(chǎn)計(jì)劃歷史
	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 主生產(chǎn)計(jì)劃 set 編號(hào)='MPS' + @no where 編號(hào) is null
GO

CREATE TABLE [dbo].[主生產(chǎn)計(jì)劃歷史] (
	[物料編號(hào)] [char] (14) NOT NULL ,
	[編號(hào)] [char] (14) NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[開(kāi)始日期] [int] NULL ,
	[結(jié)束日期] [int] NULL ,
	[期初庫(kù)存] [decimal] (15,2) NULL ,
	[需求數(shù)量] [decimal] (15,2) NULL ,
	[MPS數(shù)量] [decimal] (15,2) NULL ,
	[生產(chǎn)單數(shù)量] [decimal] (15,2) NULL ,
	[預(yù)計(jì)庫(kù)存] [decimal] (15,2) NULL ,
	[記帳人] [varchar] (10) NULL ,
	[修改日期] [int] NULL ,
	[審核人] [varchar] (10) NULL ,
	[審核日期] [int] NULL ,
	[狀態(tài)] [char] (10) NULL ,
	[備注] [varchar] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mrp物料需求計(jì)算] (
	[物料編號(hào)] [char] (14) NOT NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[期初庫(kù)存] [decimal](18, 4) NULL ,
	[毛需求] [decimal](18, 4) NULL ,
	[預(yù)計(jì)入庫(kù)] [decimal](18, 4) NULL ,
	[預(yù)計(jì)出庫(kù)] [decimal](18, 4) NULL ,
	[預(yù)計(jì)庫(kù)存] [decimal](18, 4) NULL ,
	[凈需求] [decimal](18, 4) NULL ,
	[計(jì)劃產(chǎn)出] [decimal](18, 4) NULL ,
	[計(jì)劃投入] [decimal](18, 4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mrp物料需求歷史] (
	[發(fā)布編號(hào)] [char] (20) NULL ,
	[發(fā)布時(shí)間] [datetime] NULL ,
	[物料編號(hào)] [char] (14) NOT NULL ,
	[年份] [int] NOT NULL ,
	[計(jì)劃期] [int] NOT NULL ,
	[期初庫(kù)存] [decimal](18, 4) NULL ,
	[毛需求] [decimal](18, 4) NULL ,
	[預(yù)計(jì)入庫(kù)] [decimal](18, 4) NULL ,
	[預(yù)計(jì)出庫(kù)] [char] (10) NULL ,
	[預(yù)計(jì)庫(kù)存] [decimal](18, 4) NULL ,
	[凈需求] [decimal](18, 4) NULL ,
	[計(jì)劃產(chǎn)出] [decimal](18, 4) NULL ,
	[計(jì)劃投入] [decimal](18, 4) NULL 
) ON [PRIMARY]
GO

--- 這個(gè)視圖是為了查詢時(shí)顯示物料名稱可以用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.物料編號(hào)=b.物料編號(hào)
go

CREATE TABLE [dbo].[物料狀態(tài)類別] (
	[類別代碼] [char] (5) NOT NULL Primary key,
	[狀態(tài)描述] [char] (20) NOT NULL ,
	[說(shuō)明] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[物料計(jì)劃類別] (
	[類別代碼] [char] (5) NOT NULL Primary key,
	[類別名稱] [char] (20) NOT NULL 
) ON [PRIMARY]
GO
-----------------------------------------
ALTER TABLE [dbo].[物料主文件] WITH NOCHECK ADD 
	CONSTRAINT [DF__物料主文件__進(jìn)貨提前期__160F4887] DEFAULT (0) FOR [進(jìn)貨提前期],
	CONSTRAINT [DF__物料主文件__準(zhǔn)備周期__17036CC0] DEFAULT (0) FOR [準(zhǔn)備周期],
	CONSTRAINT [DF__物料主文件__生產(chǎn)周期__17F790F9] DEFAULT (0) FOR [生產(chǎn)周期],
	CONSTRAINT [PK__物料主文件__151B244E] PRIMARY KEY  CLUSTERED 
	(
		[物料編號(hào)]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[物料清單] WITH NOCHECK ADD 
	CONSTRAINT [DF_物料清單_展開(kāi)標(biāo)記](méi) DEFAULT (0) FOR [展開(kāi)標(biāo)記](méi),
	CONSTRAINT [PK_物料清單] PRIMARY KEY  CLUSTERED 
	(
		[父項(xiàng)編號(hào)],
		[物料編號(hào)]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[工廠日歷] WITH NOCHECK ADD 
	CONSTRAINT [PK__工廠日歷__1F98B2C1] PRIMARY KEY  CLUSTERED 
	(
		[年份],
		[計(jì)劃期]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[主需求計(jì)劃] WITH NOCHECK ADD 
	CONSTRAINT [PK_主需求計(jì)劃] PRIMARY KEY  CLUSTERED 
	(
		[物料編號(hào)],
		[年份],
		[計(jì)劃期]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[主需求計(jì)劃歷史] WITH NOCHECK ADD 
	CONSTRAINT [PK_主需求計(jì)劃歷史] PRIMARY KEY  CLUSTERED 
	(
		[物料編號(hào)],
		[年份],
		[計(jì)劃期]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[主生產(chǎn)計(jì)劃] WITH NOCHECK ADD 
	CONSTRAINT [PK_主生產(chǎn)計(jì)劃] PRIMARY KEY  CLUSTERED 
	(
		[年份],
		[計(jì)劃期],
		[物料編號(hào)]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[主生產(chǎn)計(jì)劃歷史] WITH NOCHECK ADD 
	CONSTRAINT [PK_主生產(chǎn)計(jì)劃歷史] PRIMARY KEY  CLUSTERED 
	(
		[年份],
		[計(jì)劃期],
		[物料編號(hào)]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mrp物料需求計(jì)算] WITH NOCHECK ADD
12 下一頁(yè)
?? 文件大小 567 K
?? 上傳用戶 uimeet
?? 所屬分類 SQL Server
??? 相關(guān)標(biāo)簽

#sql2 #net #vb #生產(chǎn)管理
?? 快捷鍵說(shuō)明

復(fù)制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號(hào) Ctrl + =
減小字號(hào) Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? 生產(chǎn)管理系統(tǒng)實(shí)例程序.sql

?? 快捷鍵說(shuō)明
