[Home] [Help]
PACKAGE BODY: APPS.MTH_SEED_DATA_PKG
Source
1 PACKAGE BODY MTH_SEED_DATA_PKG AS
2 /*$Header: mthseedb.pls 120.6.12020000.1 2012/07/24 16:11:57 sasuren noship $ */
3
4 PROCEDURE POPULATE_SEED_DATA
5 AS
6
7 BEGIN
8
9 mth_util_pkg.log_msg('POPULATE_SEED_DATA start', mth_util_pkg.G_DBG_PROC_FUN_START);
10 mth_util_pkg.log_msg('Inserting MTH_DIM_HIERARCHY ', mth_util_pkg.G_DBG_USER_INFO);
11
12 merge INTO MTH_DIM_HIERARCHY t
13 USING
14 (
15 SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Resource Group Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_RG' HIER_INTERNAL_NAME, 3 NUMBER_OF_LEVEL FROM DUAL UNION ALL
16 SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Department Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_DEPT' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
17 SELECT -1 DIMENSION_ID, -3 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Equipment Hierarchy 01' HIERARCHY_NAME, 'MTH_EQUIP_HIER1' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
18 SELECT -1 DIMENSION_ID, -4 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Equipment Hierarchy 02' HIERARCHY_NAME, 'MTH_EQUIP_HIER2' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
19 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Facility Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_FACILITY' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
20 SELECT -2 DIMENSION_ID, -5 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 01' HIERARCHY_NAME, 'MTH_ITEM_HIER1' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
21 SELECT -2 DIMENSION_ID, -6 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 02' HIERARCHY_NAME, 'MTH_ITEM_HIER2' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
22 SELECT -2 DIMENSION_ID, -7 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 03' HIERARCHY_NAME, 'MTH_ITEM_HIER3' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
23 SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Gregorian Calendar' HIERARCHY_NAME, 'MTH_GREGORIAN CALENDAR' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
24 SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Fiscal Calendar' HIERARCHY_NAME, 'MTH_FISCAL_CALENDAR' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
25 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'MTH 445 Period Calendar' HIERARCHY_NAME, 'MTH_445_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
26 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'MTH 544 Period Calendar' HIERARCHY_NAME, 'MTH_544_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
27 SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 'TIME' DIMENSION_NAME, '13 Period Calendar' HIERARCHY_NAME, 'MTH_13_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
28 SELECT -3 DIMENSION_ID, -13 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Custom Calendar' HIERARCHY_NAME, 'MTH_CUSTOM_CALENDAR' HIER_INTERNAL_NAME, NULL FROM DUAL
29 ) s
30 ON (s.DIMENSION_NAME = t.DIMENSION_NAME AND s.HIERARCHY_NAME=t.HIERARCHY_NAME)
31 WHEN matched THEN
32 UPDATE SET
33 LAST_UPDATE_DATE = SYSDATE,
34 LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
35 HIER_INTERNAL_NAME = s.HIER_INTERNAL_NAME,
36 NUMBER_OF_LEVEL = s.NUMBER_OF_LEVEL
37 WHEN NOT matched THEN
38 INSERT
39 (t.DIMENSION_ID,
40 t.HIERARCHY_ID,
41 t.DIMENSION_NAME,
42 t.HIERARCHY_NAME,
43 t.CREATION_DATE,
44 t.LAST_UPDATE_DATE,
45 t.CREATION_SYSTEM_ID,
46 t.LAST_UPDATE_SYSTEM_ID,
47 t.HIER_INTERNAL_NAME,
48 t.NUMBER_OF_LEVEL)
49 VALUES
50 (s.DIMENSION_ID,
51 s.HIERARCHY_ID,
52 s.DIMENSION_NAME,
53 s.HIERARCHY_NAME,
54 SYSDATE,
55 SYSDATE,
56 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
57 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
58 s.HIER_INTERNAL_NAME,
59 s.NUMBER_OF_LEVEL);
60
61 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
62 mth_util_pkg.log_msg('Inserting MTH_DIM_LEVEL_LOOKUP ', mth_util_pkg.G_DBG_USER_INFO);
63
64 merge INTO MTH_DIM_LEVEL_LOOKUP t
65 USING
66 (
67
68 SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 8 LEVEL_NUM, 'RESOURCE GROUP' LEVEL_TYPE, 'RESOURCE GROUP' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
69 SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
70 SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 9 LEVEL_NUM, 'RESOURCE' LEVEL_TYPE, 'RESOURCE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
71 SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
72 SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 7 LEVEL_NUM, 'SITE' LEVEL_TYPE, 'SITE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
73 SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 9 LEVEL_NUM, 'RESOURCE' LEVEL_TYPE, 'RESOURCE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
74 SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 8 LEVEL_NUM, 'DEPARTMENT' LEVEL_TYPE, 'DEPARTMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
75 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
76 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 8 LEVEL_NUM, 'FLR' LEVEL_TYPE, 'FLR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
77 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 6 LEVEL_NUM, 'SITE' LEVEL_TYPE, 'SITE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
78 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 7 LEVEL_NUM, 'BLD' LEVEL_TYPE, 'BLD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
79 SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 9 LEVEL_NUM, 'FS' LEVEL_TYPE, 'FS' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
80 SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
81 SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 3 LEVEL_NUM, 'MONTH' LEVEL_TYPE, 'MONTH' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
82 SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
83 SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
84 SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 1 LEVEL_NUM, 'FISCAL YEAR' LEVEL_TYPE, 'FISCAL YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
85 SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 3 LEVEL_NUM, 'MONTH' LEVEL_TYPE, 'MONTH' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
86 SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
87 SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 2 LEVEL_NUM, 'FISCAL QUARTER' LEVEL_TYPE, 'FISCAL QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
88 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
89 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
90 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
91 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
92 SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
93 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
94 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
95 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
96 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
97 SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
98 SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
99 SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
100 SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
101 SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL
102
103 ) s
104
105 ON (
106 s.DIMENSION_ID = t.DIMENSION_ID AND
107 s.HIERARCHY_ID = t.HIERARCHY_ID AND
108 s.LEVEL_NUM = t.LEVEL_NUM
109 )
110
111 WHEN matched THEN
112 UPDATE SET
113 LEVEL_TYPE = s.LEVEL_TYPE,
114 LAST_UPDATE_DATE = SYSDATE,
115 LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
116 LEVEL_NAME = s.LEVEL_NAME
117
118 WHEN NOT matched THEN
119 INSERT
120 (t.DIMENSION_ID,
121 t.HIERARCHY_ID,
122 t.LEVEL_NUM,
123 t.LEVEL_TYPE,
124 t.CREATION_DATE,
125 t.LAST_UPDATE_DATE,
126 t.CREATION_SYSTEM_ID,
127 t.LAST_UPDATE_SYSTEM_ID,
128 t.LEVEL_NAME)
129 VALUES(
130 s.DIMENSION_ID,
131 s.HIERARCHY_ID,
132 s.LEVEL_NUM,
133 s.LEVEL_TYPE,
134 SYSDATE,
135 SYSDATE,
136 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
137 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
138 s.LEVEL_NAME);
139
140 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
141 mth_util_pkg.log_msg('Inserting MTH_ENTITIES ', mth_util_pkg.G_DBG_USER_INFO);
142
143
144 merge INTO MTH_ENTITIES t
145 USING
146 (
147
148 SELECT 'Shift' MTH_ALIAS, 1 ID FROM DUAL UNION ALL
149 SELECT 'Workorder' MTH_ALIAS, 2 ID FROM DUAL UNION ALL
150 SELECT 'Workorder Segment' MTH_ALIAS, 3 ID FROM DUAL UNION ALL
151 SELECT 'Item' MTH_ALIAS, 4 ID FROM DUAL UNION ALL
152 SELECT 'Status' MTH_ALIAS, 5 ID FROM DUAL UNION ALL
153 SELECT 'Completed Quantity' MTH_ALIAS, 6 ID FROM DUAL UNION ALL
154 SELECT 'Equipment' MTH_ALIAS, 7 ID FROM DUAL UNION ALL
155 SELECT 'Equipment Downtime Reason' MTH_ALIAS, 8 ID FROM DUAL UNION ALL
156 SELECT 'Output Quantity' MTH_ALIAS, 9 ID FROM DUAL UNION ALL
157 SELECT 'Rejected Quantity' MTH_ALIAS, 10 ID FROM DUAL UNION ALL
158 SELECT 'Rework Quantity' MTH_ALIAS, 11 ID FROM DUAL UNION ALL
159 SELECT 'Scrap Quantity' MTH_ALIAS, 12 ID FROM DUAL UNION ALL
160 SELECT 'Scrap Reason' MTH_ALIAS, 13 ID FROM DUAL UNION ALL
161 SELECT 'Recipe Number' MTH_ALIAS, 14 ID FROM DUAL UNION ALL
162 SELECT 'Recipe Version' MTH_ALIAS, 15 ID FROM DUAL UNION ALL
163 SELECT 'Equipment Uptime' MTH_ALIAS, 16 ID FROM DUAL UNION ALL
164 SELECT 'Equipment Idle Reason' MTH_ALIAS, 17 ID FROM DUAL UNION ALL
165 SELECT 'Equipment Cycles' MTH_ALIAS, 18 ID FROM DUAL UNION ALL
166 SELECT 'Equipment Fault' MTH_ALIAS, 19 ID FROM DUAL
167
168 ) s
169 ON (s.ID = t.ID)
170 WHEN matched THEN
171 UPDATE SET
172
173 MTH_ALIAS = s.MTH_ALIAS,
174 LAST_UPDATE_DATE = SYSDATE,
175 LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
176
177 WHEN NOT matched THEN
178 INSERT
179 (t.MTH_ALIAS,
180 t.ID,
181 t.CREATION_DATE,
182 t.LAST_UPDATE_DATE,
183 t.CREATION_SYSTEM_ID,
184 t.LAST_UPDATE_SYSTEM_ID)
185 VALUES
186 (s.MTH_ALIAS,
187 s.ID,
188 SYSDATE,
189 SYSDATE,
190 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
191 MTH_UTIL_PKG.MTH_UA_GET_VAL()
192 );
193
194
195 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
196 mth_util_pkg.log_msg('Inserting MTH_EQUIP_ENTITIES_MST ', mth_util_pkg.G_DBG_USER_INFO);
197
198 merge INTO MTH_EQUIP_ENTITIES_MST t
199 USING
200 (
201 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() ENTITY_PK_KEY, MTH_UTIL_PKG.MTH_UA_GET_VAL() ENTITY_PK, MTH_UTIL_PKG.MTH_UA_GET_MEANING() ENTITY_NAME, MTH_UTIL_PKG.MTH_UA_GET_MEANING() ENTITY_TYPE, MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY FROM DUAL
202 )s
203 ON (s.ENTITY_PK_KEY = t.ENTITY_PK_KEY)
204
205 WHEN matched THEN
206 UPDATE SET
207
208 ENTITY_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
209 ENTITY_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
210 ENTITY_TYPE = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
211 SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
212 LAST_UPDATE_DATE = SYSDATE,
213 LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
214
215 WHEN NOT matched THEN
216 INSERT
217 (t.ENTITY_PK_KEY,
218 t.ENTITY_PK,
219 t.ENTITY_NAME,
220 t.ENTITY_TYPE,
221 t.SYSTEM_FK_KEY,
222 t.CREATION_DATE,
223 t.LAST_UPDATE_DATE,
224 t.CREATION_SYSTEM_ID,
225 t.LAST_UPDATE_SYSTEM_ID)
226 VALUES
227 (s.ENTITY_PK_KEY,
228 s.ENTITY_PK,
229 s.ENTITY_NAME,
230 s.ENTITY_TYPE,
231 s.SYSTEM_FK_KEY,
232 SYSDATE,
233 SYSDATE,
234 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
235 MTH_UTIL_PKG.MTH_UA_GET_VAL());
236
237 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
238 mth_util_pkg.log_msg('Inserting MTH_EQUIPMENTS_D ', mth_util_pkg.G_DBG_USER_INFO);
239
240 merge INTO MTH_EQUIPMENTS_D t
241 USING
242 (
243 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() EQUIPMENT_PK_KEY, MTH_UTIL_PKG.MTH_UA_GET_VAL() EQUIPMENT_PK, MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY, MTH_UTIL_PKG.MTH_UA_GET_MEANING() EQUIPMENT_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY FROM DUAL
244 )s
245 ON (s.EQUIPMENT_PK_KEY = t.EQUIPMENT_PK_KEY )
246
247 WHEN matched THEN
248 UPDATE SET
249
250 t.EQUIPMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
251 t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
252 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
253 t.EQUIPMENT_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
254 t.LAST_UPDATE_DATE = SYSDATE,
255 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
256
257 WHEN NOT MATCHED THEN
258 INSERT(
259 t.EQUIPMENT_PK_KEY,
260 t.EQUIPMENT_PK,
261 t.PLANT_FK_KEY,
262 t.SYSTEM_FK_KEY,
263 t.EQUIPMENT_NAME,
264 t.CREATION_DATE,
265 t.LAST_UPDATE_DATE,
266 t.CREATION_SYSTEM_ID,
267 t.LAST_UPDATE_SYSTEM_ID)
268 VALUES
269 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
270 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
271 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
272 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
273 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
274 SYSDATE,
275 SYSDATE,
276 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
277 MTH_UTIL_PKG.MTH_UA_GET_VAL());
278
279 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
280 mth_util_pkg.log_msg('Inserting MTH_ITEM_CATEGORIES_D ', mth_util_pkg.G_DBG_USER_INFO);
281
282 merge INTO MTH_ITEM_CATEGORIES_D t
283 USING
284 (
285 SELECT
286 MTH_UTIL_PKG.MTH_UA_GET_VAL() CATEGORY_PK_KEY,
290 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
287 MTH_UTIL_PKG.MTH_UA_GET_VAL() CATEGORY_PK,
288 MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
289 MTH_UTIL_PKG.MTH_UA_GET_MEANING() CATEGORY_NAME,
291 NULL SEGMENT1,
292 NULL SEGMENT2,
293 NULL SEGMENT3,
294 NULL SEGMENT4,
295 NULL SEGMENT5,
296 NULL SEGMENT6,
297 NULL SEGMENT7,
298 NULL SEGMENT8,
299 NULL SEGMENT9,
300 NULL SEGMENT10,
301 NULL SEGMENT11,
302 NULL SEGMENT12,
303 NULL SEGMENT13,
304 NULL SEGMENT14,
305 NULL SEGMENT15,
306 NULL SEGMENT16,
307 NULL SEGMENT17,
308 NULL SEGMENT18,
309 NULL SEGMENT19,
310 NULL SEGMENT20
311
312 FROM DUAL CONSTANT
313 )s
314 ON (s.CATEGORY_PK_KEY = t.CATEGORY_PK_KEY )
315
316 WHEN matched THEN
317 UPDATE SET
318
319 t.CATEGORY_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
320 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
321 t.CATEGORY_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
322 t.SEGMENT1= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
323 t.SEGMENT2= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
324 t.SEGMENT3= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
325 t.SEGMENT4= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
326 t.SEGMENT5= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
327 t.SEGMENT6= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
328 t.SEGMENT7= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
329 t.SEGMENT8= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
330 t.SEGMENT9= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
331 t.SEGMENT10= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
332 t.SEGMENT11= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
333 t.SEGMENT12= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
334 t.SEGMENT13= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
335 t.SEGMENT14= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
336 t.SEGMENT15= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
337 t.SEGMENT16= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
338 t.SEGMENT17= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
339 t.SEGMENT18= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
340 t.SEGMENT19= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
341 t.SEGMENT20= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
342 t.LAST_UPDATE_DATE = SYSDATE,
343 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
344
345 WHEN NOT MATCHED THEN
346 INSERT(
347 t.CATEGORY_PK_KEY,
348 t.CATEGORY_PK,
349 t.SYSTEM_FK_KEY,
350 t.CATEGORY_NAME,
351 t.SEGMENT1,
352 t.SEGMENT2,
353 t.SEGMENT3,
354 t.SEGMENT4,
355 t.SEGMENT5,
356 t.SEGMENT6,
357 t.SEGMENT7,
358 t.SEGMENT8,
359 t.SEGMENT9,
360 t.SEGMENT10,
361 t.SEGMENT11,
362 t.SEGMENT12,
363 t.SEGMENT13,
364 t.SEGMENT14,
365 t.SEGMENT15,
366 t.SEGMENT16,
367 t.SEGMENT17,
368 t.SEGMENT18,
369 t.SEGMENT19,
370 t.SEGMENT20,
371 t.CREATION_DATE,
372 t.LAST_UPDATE_DATE,
373 t.CREATION_SYSTEM_ID,
374 t.LAST_UPDATE_SYSTEM_ID)
375 VALUES
376 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
377 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
378 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
379 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
380
381 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
382 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
383 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
384 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
385 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
386 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
387 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
388 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
389 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
390 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
391 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
392 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
393 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
394 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
395 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
396 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
397 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
398 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
399 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
400 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
401 SYSDATE,
402 SYSDATE,
403 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
404 MTH_UTIL_PKG.MTH_UA_GET_VAL());
405
406 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
407 mth_util_pkg.log_msg('Inserting MTH_ITEM_COST ', mth_util_pkg.G_DBG_USER_INFO);
408
409 merge INTO MTH_ITEM_COST t
410 USING
411 (
412 SELECT
413 MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
414 TO_DATE('1900-01-01', 'YYYY-MM-DD') FROM_DATE,
415 NULL TO_DATE,
416 0 COST,
417 NULL COST_ELEMENT,
418 1 ISCURRENT,
419 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
420 SYSDATE CREATION_DATE,
421 SYSDATE LAST_UPDATE_DATE,
422 MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID,
423 MTH_UTIL_PKG.MTH_UA_GET_VAL() LAST_UPDATE_SYSTEM_ID,
424 NULL USER_ATTR1,
425 NULL USER_ATTR2,
426 NULL USER_ATTR3,
427 NULL USER_ATTR4,
428 NULL USER_ATTR5,
429 NULL CREATED_BY,
430 NULL LAST_UPDATE_LOGIN,
431 NULL LAST_UPDATED_BY
432 FROM DUAL
433 )s
434 ON (t.ITEM_FK_KEY = s.ITEM_FK_KEY)
435
436 WHEN matched THEN
437 UPDATE SET
438 t.FROM_DATE = s.FROM_DATE,
439 t.COST = s.COST,
440 t.ISCURRENT = s.ISCURRENT,
441 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
442 t.LAST_UPDATE_DATE = SYSDATE,
443 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
444
445 WHEN NOT MATCHED THEN
446 INSERT(
447 t.ITEM_FK_KEY,
448 t.FROM_DATE,
449 t.COST,
450 t.ISCURRENT,
451 t.SYSTEM_FK_KEY,
452 t.CREATION_DATE,
453 t.LAST_UPDATE_DATE,
454 t.CREATION_SYSTEM_ID,
455 t.LAST_UPDATE_SYSTEM_ID)
456
460 s.COST,
457 VALUES
458 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
459 s.FROM_DATE,
461 s.ISCURRENT,
462 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
463 SYSDATE,
464 SYSDATE,
465 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
466 MTH_UTIL_PKG.MTH_UA_GET_VAL());
467
468 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
469 mth_util_pkg.log_msg('Inserting MTH_ITEMS_D ', mth_util_pkg.G_DBG_USER_INFO);
470
471 merge INTO MTH_ITEMS_D t
472 USING
473 (
474 SELECT
475 MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_PK_KEY,
476 MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_PK,
477 '0' PRIMARY_UOM,
478 MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
479 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
480 MTH_UTIL_PKG.MTH_UA_GET_MEANING() ITEM_NAME
481 FROM DUAL CONSTANT
482 )s
483 ON (t.ITEM_PK_KEY = s.ITEM_PK_KEY)
484
485 WHEN matched THEN
486 UPDATE SET
487
488 t.ITEM_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
489 t.PRIMARY_UOM = s.PRIMARY_UOM,
490 t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
491 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
492 t.ITEM_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
493 t.LAST_UPDATE_DATE = SYSDATE,
494 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
495
496 WHEN NOT MATCHED THEN
497 INSERT(
498
499 t.ITEM_PK_KEY,
500 t.ITEM_PK,
501 t.PRIMARY_UOM,
502 t.PLANT_FK_KEY,
503 t.SYSTEM_FK_KEY,
504 t.ITEM_NAME,
505 t.CREATION_DATE,
506 t.LAST_UPDATE_DATE,
507 t.CREATION_SYSTEM_ID,
508 t.LAST_UPDATE_SYSTEM_ID)
509 VALUES
510 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
511 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
512 s.PRIMARY_UOM,
513 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
514 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
515 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
516 SYSDATE,
517 SYSDATE,
518 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
519 MTH_UTIL_PKG.MTH_UA_GET_VAL());
520
521 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
522 mth_util_pkg.log_msg('Inserting MTH_LOOKUPS_D ', mth_util_pkg.G_DBG_USER_INFO);
523
524 merge INTO MTH_LOOKUPS_D t
525 USING
526 (
527 SELECT 'MTH_BATCH_TERM_IND_L' LOOKUP_TYPE, 0 LOOKUP_CODE, 'No' REPORTING_CODE FROM DUAL UNION ALL
528 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 1 LOOKUP_CODE, 'Unreleased' REPORTING_CODE FROM DUAL UNION ALL
529 SELECT 'MTH_BATCH_TERM_IND_L' LOOKUP_TYPE, 1 LOOKUP_CODE, 'Yes' REPORTING_CODE FROM DUAL UNION ALL
530 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 2 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
531 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 3 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
532 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 4 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
533 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 5 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
534 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 6 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
535 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 7 LOOKUP_CODE, 'Cancel' REPORTING_CODE FROM DUAL UNION ALL
536 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 8 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
537 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 9 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
538 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 10 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
539 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 11 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
540 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 12 LOOKUP_CODE, 'Close' REPORTING_CODE FROM DUAL UNION ALL
541 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 13 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
542 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 14 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
543 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 15 LOOKUP_CODE, 'Cancel' REPORTING_CODE FROM DUAL UNION ALL
544 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 16 LOOKUP_CODE, 'Terminated' REPORTING_CODE FROM DUAL UNION ALL
545 SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 17 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL
546 )s
547
548 ON (
549 t.LOOKUP_TYPE = s.LOOKUP_TYPE AND
550 t.LOOKUP_CODE = s.LOOKUP_CODE
551 )
552
553 WHEN matched THEN
554 UPDATE SET
555
556 t.LAST_UPDATE_DATE = SYSDATE,
557 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
558 t.REPORTING_CODE = s.REPORTING_CODE
559
560 WHEN NOT MATCHED THEN
561 INSERT(
562 t.LOOKUP_TYPE,
563 t.LOOKUP_CODE,
564 t.CREATION_DATE,
565 t.LAST_UPDATE_DATE,
566 t.CREATION_SYSTEM_ID,
567 t.LAST_UPDATE_SYSTEM_ID,
568 t.REPORTING_CODE)
569 VALUES(
570 s.LOOKUP_TYPE,
571 s.LOOKUP_CODE,
572 SYSDATE,
573 SYSDATE,
574 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
575 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
576 s.REPORTING_CODE);
577
578 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
579 mth_util_pkg.log_msg('Inserting MTH_PLANTS_D ', mth_util_pkg.G_DBG_USER_INFO);
580
581 merge INTO MTH_PLANTS_D t
582 USING
583 (
584 SELECT
585 MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_PK_KEY,
586 MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_PK,
587 'USD' CURRENCY_CODE,
588 1 SOURCE,
589 MTH_UTIL_PKG.MTH_UA_GET_MEANING() PLANT_NAME,
590 0 GRAVEYARD_SHIFT_FLAG,
591 TO_DATE ('01-01-1900','DD-MM-YYYY' ) FROM_DATE
592 FROM
593 DUAL
594 )s
595 ON (t.PLANT_PK_KEY = s.PLANT_PK_KEY)
596
597 WHEN matched THEN
598 UPDATE SET
599
600 t.PLANT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
601 t.CURRENCY_CODE = s.CURRENCY_CODE,
605 t.PLANT_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
602 t.GRAVEYARD_SHIFT_FLAG = s.GRAVEYARD_SHIFT_FLAG,
603 t.FROM_DATE = s.FROM_DATE,
604 t.SOURCE = s.SOURCE,
606 t.LAST_UPDATE_DATE = SYSDATE,
607 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
608
609 WHEN NOT MATCHED THEN
610 INSERT
611 (
612 t.PLANT_PK_KEY,
613 t.PLANT_PK,
614 t.CURRENCY_CODE,
615 t.SOURCE,
616 t.PLANT_NAME,
617 t.CREATION_DATE,
618 t.LAST_UPDATE_DATE,
619 t.CREATION_SYSTEM_ID,
620 t.LAST_UPDATE_SYSTEM_ID,
621 t.GRAVEYARD_SHIFT_FLAG,
622 t.FROM_DATE)
623 VALUES
624 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
625 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
626 s.CURRENCY_CODE,
627 s.SOURCE,
628 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
629 SYSDATE,
630 SYSDATE,
631 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
632 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
633 s.GRAVEYARD_SHIFT_FLAG,
634 s.FROM_DATE);
635
636 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
637 mth_util_pkg.log_msg('Inserting MTH_PROD_SUB_SEGMENTS_F ', mth_util_pkg.G_DBG_USER_INFO);
638
639 merge INTO MTH_PROD_SUB_SEGMENTS_F t
640 USING
641 (
642 SELECT
643 MTH_UTIL_PKG.MTH_UA_GET_VAL() SUB_SEGMENT_PK_KEY,
644 MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_FK_KEY,
645 MTH_UTIL_PKG.MTH_UA_GET_VAL() SUB_SEGMENT_PK,
646 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
647 MTH_UTIL_PKG.MTH_UA_GET_MEANING() DESCRIPTION
648
649 FROM
650 DUAL CONSTANT
651 )s
652 ON (t.SUB_SEGMENT_PK_KEY = s.SUB_SEGMENT_PK_KEY)
653
654 WHEN matched THEN
655 UPDATE SET
656
657 t.SEGMENT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
658 t.SUB_SEGMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
659 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
660 t.LAST_UPDATE_DATE = SYSDATE,
661 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
662 t.DESCRIPTION = s.DESCRIPTION
663
664 WHEN NOT MATCHED THEN
665 INSERT(
666 t.SUB_SEGMENT_PK_KEY,
667 t.SEGMENT_FK_KEY,
668 t.SUB_SEGMENT_PK,
669 t.SYSTEM_FK_KEY,
670 t.CREATION_DATE,
671 t.LAST_UPDATE_DATE,
672 t.CREATION_SYSTEM_ID,
673 t.LAST_UPDATE_SYSTEM_ID,
674 t.DESCRIPTION
675 )
676 VALUES
677 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
678 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
679 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
680 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
681 SYSDATE,
682 SYSDATE,
683 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
684 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
685 s.DESCRIPTION
686 );
687
688 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
689 mth_util_pkg.log_msg('Inserting MTH_PRODUCTION_SCHEDULES_F ', mth_util_pkg.G_DBG_USER_INFO);
690
691 merge INTO MTH_PRODUCTION_SCHEDULES_F t
692 USING
693 (
694 SELECT
695 MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_PK_KEY,
696 MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_PK,
697 MTH_UTIL_PKG.MTH_UA_GET_MEANING() WORKORDER_NAME,
698 1 WORKORDER_TYPE,
699 1 STATUS_CODE,
700 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY
701
702 FROM
703 DUAL CONSTANT
704 )s
705 ON (t.WORKORDER_PK_KEY = s.WORKORDER_PK_KEY)
706
707 WHEN matched THEN
708 UPDATE SET
709
710 t.WORKORDER_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
711 t.WORKORDER_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
712 t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
713 t.WORKORDER_TYPE = s.WORKORDER_TYPE,
714 t.STATUS_CODE = s.STATUS_CODE,
715 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
716 t.LAST_UPDATE_DATE = SYSDATE,
717 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
718
719 WHEN NOT MATCHED THEN
720 INSERT(
721 t.WORKORDER_PK_KEY,
722 t.WORKORDER_PK,
723 t.WORKORDER_NAME,
724 t.PLANT_FK_KEY,
725 t.WORKORDER_TYPE,
726 t.STATUS_CODE,
727 t.SYSTEM_FK_KEY,
728 t.CREATION_DATE,
729 t.LAST_UPDATE_DATE,
730 t.CREATION_SYSTEM_ID,
731 t.LAST_UPDATE_SYSTEM_ID)
732 VALUES
733 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
734 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
735 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
736 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
737 s.WORKORDER_TYPE,
738 s.STATUS_CODE,
739 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
740 SYSDATE,
741 SYSDATE,
742 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
743 MTH_UTIL_PKG.MTH_UA_GET_VAL()
744 );
745
746 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
747 mth_util_pkg.log_msg('Inserting MTH_PRODUCTION_SEGMENTS_F ', mth_util_pkg.G_DBG_USER_INFO);
748
749 merge INTO MTH_PRODUCTION_SEGMENTS_F t
750 USING
751 (
752 SELECT
753 MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_FK_KEY,
754 MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_PK_KEY,
755 MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_PK,
756 MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
757 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
758 MTH_UTIL_PKG.MTH_UA_GET_MEANING() WORKORDER_NAME,
759 1 WORKORDER_TYPE,
760 1 STATUS_CODE
761 FROM
762 DUAL CONSTANT
763 )s
764 ON (t.SEGMENT_PK_KEY = s.SEGMENT_PK_KEY)
765
766 WHEN matched THEN
767 UPDATE SET
768
769 t.WORKORDER_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
770 t.SEGMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
771 t.ITEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
772 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
773 t.LAST_UPDATE_DATE = SYSDATE,
777 INSERT(
774 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
775
776 WHEN NOT MATCHED THEN
778 t.WORKORDER_FK_KEY,
779 t.SEGMENT_PK_KEY,
780 t.SEGMENT_PK,
781 t.ITEM_FK_KEY,
782 t.SYSTEM_FK_KEY,
783 t.CREATION_DATE,
784 t.LAST_UPDATE_DATE,
785 t.CREATION_SYSTEM_ID,
786 t.LAST_UPDATE_SYSTEM_ID)
787 VALUES
788 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
789 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
790 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
791 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
792 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
793 SYSDATE,
794 SYSDATE,
795 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
796 MTH_UTIL_PKG.MTH_UA_GET_VAL()
797 );
798
799 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
800 mth_util_pkg.log_msg('Inserting MTH_RESOURCE_COST ', mth_util_pkg.G_DBG_USER_INFO);
801
802 merge INTO MTH_RESOURCE_COST t
803 USING
804 (
805 SELECT
806 MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_FK_KEY,
807 TO_DATE ('1900-01-01', 'YYYY-MM-DD') FROM_DATE,
808 0 COST,
809 1 ISCURRENT,
810 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY
811 FROM
812 DUAL
813 )s
814 ON (t.RESOURCE_FK_KEY = s.RESOURCE_FK_KEY)
815
816 WHEN matched THEN
817 UPDATE SET
818
819 t.FROM_DATE = s.FROM_DATE,
820 t.COST = t.COST,
821 t.ISCURRENT = t.ISCURRENT,
822 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
823 t.LAST_UPDATE_DATE = SYSDATE,
824 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
825
826 WHEN NOT MATCHED THEN
827 INSERT
828 (
829 t.RESOURCE_FK_KEY,
830 t.FROM_DATE,
831 t.COST,
832 t.ISCURRENT,
833 t.SYSTEM_FK_KEY,
834 t.CREATION_DATE,
835 t.LAST_UPDATE_DATE,
836 t.CREATION_SYSTEM_ID,
837 t.LAST_UPDATE_SYSTEM_ID)
838 VALUES
839 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
840 s.FROM_DATE,
841 s.COST,
842 s.ISCURRENT,
843 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
844 SYSDATE,
845 SYSDATE,
846 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
847 MTH_UTIL_PKG.MTH_UA_GET_VAL());
848
849 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
850 mth_util_pkg.log_msg('Inserting MTH_RESOURCES_D ', mth_util_pkg.G_DBG_USER_INFO);
851
852 merge INTO MTH_RESOURCES_D t
853 USING
854 (
855 SELECT
856 MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_PK_KEY,
857 MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_PK,
858 MTH_UTIL_PKG.MTH_UA_GET_MEANING() RESOURCE_NAME,
859 MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
860 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
861 'MACHINE' RESOURCE_TYPE
862 FROM
863 DUAL
864 )s
865 ON (t.RESOURCE_PK_KEY = s.RESOURCE_PK_KEY)
866
867 WHEN matched THEN
868 UPDATE SET
869
870 t.RESOURCE_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
871 t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
872 t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
873 t.RESOURCE_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
874 t.LAST_UPDATE_DATE = SYSDATE,
875 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
876 t.RESOURCE_TYPE = s.RESOURCE_TYPE
877
878 WHEN NOT MATCHED THEN
879 INSERT
880 (
881 t.RESOURCE_PK_KEY,
882 t.RESOURCE_PK,
883 t.PLANT_FK_KEY,
884 t.SYSTEM_FK_KEY,
885 t.RESOURCE_NAME,
886 t.CREATION_DATE,
887 t.LAST_UPDATE_DATE,
888 t.CREATION_SYSTEM_ID,
889 t.LAST_UPDATE_SYSTEM_ID,
890 t.RESOURCE_TYPE)
891 VALUES
892 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
893 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
894 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
895 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
896 MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
897 SYSDATE,
898 SYSDATE,
899 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
900 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
901 s.RESOURCE_TYPE);
902
903 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
904 mth_util_pkg.log_msg('Inserting MTH_SYSTEMS_SETUP ', mth_util_pkg.G_DBG_USER_INFO);
905
906 merge INTO MTH_SYSTEMS_SETUP t
907 USING
908 (
909 SELECT
910 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_PK_KEY,
911 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_PK,
912 MTH_UTIL_PKG.MTH_UA_GET_MEANING() SYSTEM_NAME,
913 'OTH' SYSTEM_TYPE
914 FROM
915 DUAL
916 )s
917 ON (t.SYSTEM_PK_KEY = s.SYSTEM_PK_KEY)
918
919 WHEN matched THEN
920 UPDATE SET
921
922 t.SYSTEM_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
923 t.SYSTEM_TYPE = s.SYSTEM_TYPE,
924 t.LAST_UPDATE_DATE = SYSDATE,
925 t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
926 t.SYSTEM_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING()
927
928 WHEN NOT MATCHED THEN
929 INSERT
930 (
931 t.SYSTEM_PK_KEY,
932 t.SYSTEM_PK,
933 t.SYSTEM_TYPE,
934 t.CREATION_DATE,
935 t.LAST_UPDATE_DATE,
936 t.CREATION_SYSTEM_ID,
937 t.LAST_UPDATE_SYSTEM_ID,
938 t.SYSTEM_NAME)
939 VALUES
940 (MTH_UTIL_PKG.MTH_UA_GET_VAL(),
941 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
942 s.SYSTEM_TYPE,
943 SYSDATE,
944 SYSDATE,
945 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
946 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
947 MTH_UTIL_PKG.MTH_UA_GET_MEANING()
948 );
949
950 mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
951
952 COMMIT;
953
954 mth_util_pkg.log_msg('POPULATE_SEED_DATA end', mth_util_pkg.G_DBG_PROC_FUN_END);
955 EXCEPTION
956 WHEN OTHERS THEN
957 mth_util_pkg.log_msg('Exception OTHERS in POPULATE_SEED_DATA', mth_util_pkg.G_DBG_EXCEPTION);
958 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
959 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
960 END POPULATE_SEED_DATA;
961
962 END MTH_SEED_DATA_PKG;