· 4 years ago · Jul 21, 2021, 12:44 AM
1-- ----------------------------
2-- Table structure for OPM_WO_ACT
3-- ----------------------------
4IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OPM_WO_ACT]') AND type IN ('U'))
5 DROP TABLE [dbo].[OPM_WO_ACT]
6GO
7
8CREATE TABLE [dbo].[OPM_WO_ACT] (
9 [RECID] bigint NOT NULL,
10 [RECTIMESTAMP] datetime DEFAULT (getdate()) NOT NULL,
11 [RECSTATUS] int DEFAULT ((0)) NOT NULL,
12 [BATCH_ORDER] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
13 [OPERATION_SEQUENCE] int NOT NULL,
14 [ACTIVITY_SEQUENCE] int NOT NULL,
15 [ACTIVITY_NAME] varchar(255) COLLATE Latin1_General_CI_AS NULL
16)
17GO
18
19ALTER TABLE [dbo].[OPM_WO_ACT] SET (LOCK_ESCALATION = TABLE)
20GO
21
22
23-- ----------------------------
24-- Table structure for OPM_WO_HEADER
25-- ----------------------------
26IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OPM_WO_HEADER]') AND type IN ('U'))
27 DROP TABLE [dbo].[OPM_WO_HEADER]
28GO
29
30CREATE TABLE [dbo].[OPM_WO_HEADER] (
31 [RECID] bigint NOT NULL,
32 [RECTIMESTAMP] datetime DEFAULT (getdate()) NOT NULL,
33 [RECSTATUS] int DEFAULT ((0)) NOT NULL,
34 [TIMESTAMP] varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
35 [ID] varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
36 [TYPE] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
37 [SOURCE] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
38 [PLANT] varchar(5) COLLATE Latin1_General_CI_AS NOT NULL,
39 [BATCH_ORDER] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
40 [PRODUCT] varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
41 [BATCH_NUMBER] varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
42 [RECIPE] varchar(150) COLLATE Latin1_General_CI_AS NOT NULL,
43 [FORMULA] varchar(150) COLLATE Latin1_General_CI_AS NOT NULL,
44 [STATUS] int DEFAULT ((0)) NOT NULL,
45 [PLANNED_START] datetime NOT NULL,
46 [PLANNED_COMPLETION] datetime NOT NULL
47)
48GO
49
50ALTER TABLE [dbo].[OPM_WO_HEADER] SET (LOCK_ESCALATION = TABLE)
51GO
52
53
54-- ----------------------------
55-- Table structure for OPM_WO_OPR
56-- ----------------------------
57IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OPM_WO_OPR]') AND type IN ('U'))
58 DROP TABLE [dbo].[OPM_WO_OPR]
59GO
60
61CREATE TABLE [dbo].[OPM_WO_OPR] (
62 [RECID] bigint NOT NULL,
63 [RECTIMESTAMP] datetime DEFAULT (getdate()) NOT NULL,
64 [RECSTATUS] int DEFAULT ((0)) NOT NULL,
65 [BATCH_ORDER] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
66 [OPERATION_SEQUENCE] int NOT NULL,
67 [OPERATION_NAME] varchar(255) COLLATE Latin1_General_CI_AS NULL,
68 [OPERATION_VERSION] int NULL
69)
70GO
71
72ALTER TABLE [dbo].[OPM_WO_OPR] SET (LOCK_ESCALATION = TABLE)
73GO
74
75
76-- ----------------------------
77-- Table structure for OPM_WO_RES
78-- ----------------------------
79IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OPM_WO_RES]') AND type IN ('U'))
80 DROP TABLE [dbo].[OPM_WO_RES]
81GO
82
83CREATE TABLE [dbo].[OPM_WO_RES] (
84 [RECID] bigint NOT NULL,
85 [RECTIMESTAMP] datetime DEFAULT (getdate()) NOT NULL,
86 [RECSTATUS] int DEFAULT ((0)) NOT NULL,
87 [BATCH_ORDER] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
88 [OPERATION_SEQUENCE] int NOT NULL,
89 [ACTIVITY_SEQUENCE] int NOT NULL,
90 [RESOURCE_SEQUENCE] int NOT NULL,
91 [RESOURCE_NAME] varchar(255) COLLATE Latin1_General_CI_AS NULL
92)
93GO
94
95ALTER TABLE [dbo].[OPM_WO_RES] SET (LOCK_ESCALATION = TABLE)
96GO
97
98
99-- ----------------------------
100-- Primary Key structure for table OPM_WO_ACT
101-- ----------------------------
102ALTER TABLE [dbo].[OPM_WO_ACT] ADD CONSTRAINT [PK__OPM_WO_A__492D426E5A820417] PRIMARY KEY CLUSTERED ([BATCH_ORDER], [OPERATION_SEQUENCE], [ACTIVITY_SEQUENCE])
103WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
104ON [PRIMARY]
105GO
106
107
108-- ----------------------------
109-- Primary Key structure for table OPM_WO_HEADER
110-- ----------------------------
111ALTER TABLE [dbo].[OPM_WO_HEADER] ADD CONSTRAINT [PK__OPM_WO_H__A9A5B3BBF4C767D4] PRIMARY KEY CLUSTERED ([BATCH_ORDER])
112WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
113ON [PRIMARY]
114GO
115
116
117-- ----------------------------
118-- Primary Key structure for table OPM_WO_OPR
119-- ----------------------------
120ALTER TABLE [dbo].[OPM_WO_OPR] ADD CONSTRAINT [PK__OPM_WO_O__492D426EB66C90FF] PRIMARY KEY CLUSTERED ([BATCH_ORDER], [OPERATION_SEQUENCE])
121WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
122ON [PRIMARY]
123GO
124
125
126-- ----------------------------
127-- Primary Key structure for table OPM_WO_RES
128-- ----------------------------
129ALTER TABLE [dbo].[OPM_WO_RES] ADD CONSTRAINT [PK__OPM_WO_R__492D426E0675A6F9] PRIMARY KEY CLUSTERED ([BATCH_ORDER], [OPERATION_SEQUENCE], [ACTIVITY_SEQUENCE], [RESOURCE_SEQUENCE])
130WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
131ON [PRIMARY]
132GO
133
134
135-- ----------------------------
136-- Foreign Keys structure for table OPM_WO_ACT
137-- ----------------------------
138ALTER TABLE [dbo].[OPM_WO_ACT] ADD CONSTRAINT [FK__OPM_WO_AC__BATCH__1ED998B2] FOREIGN KEY ([BATCH_ORDER], [OPERATION_SEQUENCE]) REFERENCES [dbo].[OPM_WO_OPR] ([BATCH_ORDER], [OPERATION_SEQUENCE]) ON DELETE CASCADE ON UPDATE NO ACTION
139GO
140
141
142-- ----------------------------
143-- Foreign Keys structure for table OPM_WO_OPR
144-- ----------------------------
145ALTER TABLE [dbo].[OPM_WO_OPR] ADD CONSTRAINT [FK__OPM_WO_OP__BATCH__1A14E395] FOREIGN KEY ([BATCH_ORDER]) REFERENCES [dbo].[OPM_WO_HEADER] ([BATCH_ORDER]) ON DELETE CASCADE ON UPDATE NO ACTION
146GO
147
148
149-- ----------------------------
150-- Foreign Keys structure for table OPM_WO_RES
151-- ----------------------------
152ALTER TABLE [dbo].[OPM_WO_RES] ADD CONSTRAINT [FK__OPM_WO_RES__29572725] FOREIGN KEY ([BATCH_ORDER], [OPERATION_SEQUENCE], [ACTIVITY_SEQUENCE]) REFERENCES [dbo].[OPM_WO_ACT] ([BATCH_ORDER], [OPERATION_SEQUENCE], [ACTIVITY_SEQUENCE]) ON DELETE NO ACTION ON UPDATE NO ACTION
153GO
154
155