[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_AHL_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_AHL_ODS_LOAD AS
2 /* $Header: MSCLAHLB.pls 120.37.12020000.2 2012/11/16 08:22:04 swundapa ship $*/
3 v_sql_stmt VARCHAR2(32767);
4 lv_tbl VARCHAR2(30);
5 lv_cursor_stmt VARCHAR2(5000);
6 lv_retcode NUMBER;
7 lv_errbuf VARCHAR2(240);
8 v_sub_str VARCHAR2(32767):=NULL;
9 PROCEDURE LOAD_VISITS IS
10 CURSOR c1 IS
11 SELECT msv.VISIT_ID,
12 msv.VISIT_NAME,
13 msv.VISIT_DESC,
14 nvl(msv.VISIT_START_DATE,sysdate) VISIT_START_DATE,
15 nvl(msv.VISIT_END_DATE,sysdate) VISIT_END_DATE,
16 msv.ORGANIZATION_ID,
17 msv.VISIT_TYPE,
18 msv.VISIT_PRIORITY,
19 msv.VISIT_STATUS,
20 msv.VISIT_NUMBER,
21 msv.SR_INSTANCE_ID
22 FROM MSC_ST_VISITS msv
23 WHERE msv.deleted_flag = MSC_UTIL.SYS_NO
24 AND msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
25
26 CURSOR c_del IS
27 SELECT msv.VISIT_ID,
28 msv.ORGANIZATION_ID,
29 msv.SR_INSTANCE_ID
30 FROM MSC_ST_VISITS msv
31 WHERE msv.deleted_flag = MSC_UTIL.SYS_YES
32 AND msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
33
34 BEGIN
35 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_VISITS ');
36
37 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
38 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
39 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
40 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
41 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_VISITS',
42 MSC_CL_COLLECTION.v_instance_id,
43 -1);
44 ELSE
45 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
46 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_VISITS',
47 MSC_CL_COLLECTION.v_instance_id,
48 null,
49 v_sub_str);
50 END IF;
51 END IF;
52
53 BEGIN
54 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
55 lv_tbl:= 'VISITS_'||MSC_CL_COLLECTION.v_instance_code;
56 ELSE
57 lv_tbl:= 'MSC_VISITS';
58 END IF;
59 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
60 'In Procedure LOAD_VISITS lv_tbl'||lv_tbl);
61
62 lv_cursor_stmt:=
63 'INSERT INTO '||lv_tbl
64 ||'( VISIT_ID,'
65 ||'VISIT_NAME,'
66 ||'VISIT_DESC,'
67 ||' VISIT_START_DATE,'
68 ||'VISIT_END_DATE,'
69 ||'ORGANIZATION_ID,'
70 ||'VISIT_TYPE,'
71 ||'VISIT_PRIORITY,'
72 ||'VISIT_STATUS,'
73 ||'VISIT_NUMBER,'
74 ||'REFRESH_ID,'
75 ||'SR_INSTANCE_ID,'
76 ||'LAST_UPDATE_DATE,'
77 ||'LAST_UPDATED_BY,'
78 ||'CREATION_DATE,'
79 ||'CREATED_BY) '
80 ||'SELECT msv.VISIT_ID,'
81 ||'msv.VISIT_NAME,'
82 ||'msv.VISIT_DESC,'
83 ||'nvl(msv.VISIT_START_DATE,sysdate),'
84 ||'nvl(msv.VISIT_END_DATE,sysdate),'
85 ||'msv.ORGANIZATION_ID,'
86 ||'msv.VISIT_TYPE,'
87 ||'msv.VISIT_PRIORITY,'
88 ||'msv.VISIT_STATUS,'
89 ||'msv.VISIT_NUMBER,'
90 ||':v_last_collection_id,'
91 ||':v_instance_id,'
92 ||':v_current_date,'
93 ||':v_current_user,'
94 ||':v_current_date,'
95 ||':v_current_user'
96 ||' FROM MSC_ST_VISITS msv '
97 ||'WHERE msv.deleted_flag = '||MSC_UTIL.SYS_NO
98 ||' AND msv.SR_INSTANCE_ID= '
99 ||MSC_CL_COLLECTION.v_instance_id;
100
101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
102 'In Procedure LOAD_VISITS lv_cursor_stmt: '|| lv_cursor_stmt);
103 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
104 MSC_CL_COLLECTION.v_is_partial_refresh) OR
105 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
106 EXECUTE IMMEDIATE lv_cursor_stmt using
107 MSC_CL_COLLECTION.v_last_collection_id,
108 MSC_CL_COLLECTION.v_instance_id,
109 MSC_CL_COLLECTION.v_current_date,
110 MSC_CL_COLLECTION.v_current_user,
111 MSC_CL_COLLECTION.v_current_date,
112 MSC_CL_COLLECTION.v_current_user;
113 END IF;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
118 RAISE;
119 END;
120
121 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
122 (MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
123
124 FOR c_rec IN c_del LOOP
125 BEGIN
126
127 DELETE MSC_WO_MILESTONES
128 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
129 AND VISIT_ID = c_rec.visit_id
130 AND ORGANIZATION_ID = c_rec.organization_id;
131
132 DELETE MSC_VISITS
133 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
134 AND VISIT_ID = c_rec.visit_id
135 AND ORGANIZATION_ID = c_rec.organization_id;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
140 'An error has occurred during deletion of Visits.');
141 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
142 RAISE;
143 END;
144 END LOOP;
145
146 COMMIT;
147
148 FOR c_rec IN c1 LOOP
149
150 UPDATE MSC_VISITS
151 SET
152 VISIT_DESC = c_rec.VISIT_DESC,
153 VISIT_NAME = c_rec.VISIT_NAME,
154 VISIT_START_DATE = c_rec.VISIT_START_DATE,
155 VISIT_END_DATE = c_rec.VISIT_END_DATE,
156 VISIT_TYPE = c_rec.VISIT_TYPE,
157 VISIT_PRIORITY = c_rec.VISIT_PRIORITY,
158 VISIT_STATUS = c_rec.VISIT_STATUS,
159 VISIT_NUMBER = c_rec.VISIT_NUMBER,
160 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
161 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
162 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
163 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
164 AND VISIT_ID = c_rec.VISIT_ID
165 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
166
167 IF SQL%NOTFOUND THEN
168
169 lv_cursor_stmt:=
170 'INSERT INTO '||lv_tbl
171 ||'( VISIT_ID,'
172 ||'VISIT_NAME,'
173 ||'VISIT_DESC,'
174 ||'VISIT_START_DATE,'
175 ||'VISIT_END_DATE,'
176 ||'ORGANIZATION_ID,'
177 ||'VISIT_TYPE,'
178 ||'VISIT_PRIORITY,'
179 ||'VISIT_STATUS,'
180 ||'VISIT_NUMBER,'
181 ||'REFRESH_ID,'
182 ||'SR_INSTANCE_ID,'
183 ||'LAST_UPDATE_DATE,'
184 ||'LAST_UPDATED_BY,'
185 ||'CREATION_DATE,'
186 ||'CREATED_BY) '
187 ||'values ( :VISIT_ID,'
188 ||':VISIT_NAME,'
189 ||':VISIT_DESC,'
190 ||':VISIT_START_DATE,'
191 ||':VISIT_END_DATE,'
192 ||':ORGANIZATION_ID,'
193 ||':VISIT_TYPE,'
194 ||':VISIT_PRIORITY,'
195 ||':VISIT_STATUS,'
196 ||':VISIT_NUMBER,'
197 ||':v_last_collection_id,'
198 ||':v_instance_id,'
199 ||':v_current_date,'
200 ||':v_current_user,'
201 ||':v_current_date,'
202 ||':v_current_user)';
203
204 EXECUTE IMMEDIATE lv_cursor_stmt using
205 c_rec.VISIT_ID,
206 c_rec.VISIT_NAME,
207 c_rec.VISIT_DESC,
208 c_rec.VISIT_START_DATE,
209 c_rec.VISIT_END_DATE,
210 c_rec.ORGANIZATION_ID,
211 c_rec.VISIT_TYPE,
212 c_rec.VISIT_PRIORITY,
213 c_rec.VISIT_STATUS,
214 c_rec.VISIT_NUMBER,
215 MSC_CL_COLLECTION.v_last_collection_id,
216 MSC_CL_COLLECTION.v_instance_id,
217 MSC_CL_COLLECTION.v_current_date,
218 MSC_CL_COLLECTION.v_current_user,
219 MSC_CL_COLLECTION.v_current_date,
220 MSC_CL_COLLECTION.v_current_user;
221 END IF;
222 END LOOP;
223 END IF;
224
225
226 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
227 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
228 lv_retcode,
229 'MSC_VISITS',
230 MSC_CL_COLLECTION.v_INSTANCE_CODE,
231 MSC_UTIL.G_ERROR);
232
233 IF lv_retcode = MSC_UTIL.G_ERROR THEN
234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
235 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
236 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
237 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238 END IF;
239 END IF;
240 COMMIT;
241
242 END LOAD_VISITS;
243
244 PROCEDURE LOAD_WO_ATTRIBUTES IS
245
246 CURSOR c_del IS
247 SELECT mswa.SUPPLY_ID,
248 mswa.VISIT_ID,
249 mswa.ORGANIZATION_ID,
250 mswa.SR_INSTANCE_ID
251 FROM MSC_ST_WO_ATTRIBUTES mswa
252 WHERE mswa.deleted_flag = MSC_UTIL.SYS_YES
253 AND mswa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
254
255 lv_supplies_tbl VARCHAR2(30);
256 type ref_cur is ref cursor;
257 c_ref_cur ref_cur;
258 TYPE cursorRec IS RECORD (
259 SUPPLY_ID MSC_ST_WO_ATTRIBUTES.SUPPLY_ID%type,
260 PRODUCES_TO_STOCK MSC_ST_WO_ATTRIBUTES.PRODUCES_TO_STOCK%type,
261 SERIAL_NUM MSC_ST_WO_ATTRIBUTES.SERIAL_NUM%type,
262 VISIT_ID MSC_ST_WO_ATTRIBUTES.VISIT_ID%type,
263 VISIT_NAME MSC_ST_WO_ATTRIBUTES.VISIT_NAME%type,
264 PARAMETER1 MSC_ST_WO_ATTRIBUTES.PARAMETER1%type,
265 PARAMETER2 MSC_ST_WO_ATTRIBUTES.PARAMETER2%type,
266 PARAMETER3 MSC_ST_WO_ATTRIBUTES.PARAMETER3%type,
267 PARAMETER4 MSC_ST_WO_ATTRIBUTES.PARAMETER4%type,
268 PARAMETER5 MSC_ST_WO_ATTRIBUTES.PARAMETER5%type,
269 PARAMETER6 MSC_ST_WO_ATTRIBUTES.PARAMETER6%type,
270 PARAMETER7 MSC_ST_WO_ATTRIBUTES.PARAMETER7%type,
271 PARAMETER8 MSC_ST_WO_ATTRIBUTES.PARAMETER8%type,
272 PARAMETER9 MSC_ST_WO_ATTRIBUTES.PARAMETER9%type,
273 MASTER_WO MSC_ST_WO_ATTRIBUTES.MASTER_WO%type,
274 PREV_MILESTONE MSC_ST_WO_ATTRIBUTES.PREV_MILESTONE%type,
275 NEXT_MILESTONE MSC_ST_WO_ATTRIBUTES.NEXT_MILESTONE%type,
276 ORGANIZATION_ID MSC_ST_WO_ATTRIBUTES.ORGANIZATION_ID%type,
277 ITEM_ALTERNATES_EXIST MSC_ST_WO_ATTRIBUTES.ITEM_ALTERNATES_EXIST%type,
278 PRODUCT_CLASSIFICATION MSC_ST_WO_ATTRIBUTES.PRODUCT_CLASSIFICATION%type,
279 MAINTENANCE_REQT MSC_ST_WO_ATTRIBUTES.MAINTENANCE_REQT%type,
280 WIP_ENTITY_ID MSC_ST_WO_ATTRIBUTES.WIP_ENTITY_ID%type
281 );
282
283 c_rec cursorRec;
284 BEGIN
285
286 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_ATTRIBUTES ');
287
288 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
289 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
290 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
291 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
292 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
293 MSC_CL_COLLECTION.v_instance_id,
294 -1);
295 ELSE
296 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
300 v_sub_str);
297 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
298 MSC_CL_COLLECTION.v_instance_id,
299 null,
301 END IF;
302 END IF;
303
304
305 BEGIN
306 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
307 lv_tbl:= 'WO_ATTRIBUTES_'||MSC_CL_COLLECTION.v_instance_code;
308 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
309 ELSE
310 lv_tbl:= 'MSC_WO_ATTRIBUTES';
311 lv_supplies_tbl:= 'MSC_SUPPLIES';
312 END IF;
313
314 lv_cursor_stmt:=
315 'INSERT INTO '||lv_tbl
316 ||'(SUPPLY_ID,'
317 ||'PRODUCES_TO_STOCK, '
318 ||'SERIAL_NUM, '
319 ||'VISIT_ID, '
320 ||'VISIT_NAME, '
321 ||'PARAMETER1, '
322 ||'PARAMETER2, '
323 ||'PARAMETER3, '
324 ||'PARAMETER4, '
325 ||'PARAMETER5, '
326 ||'PARAMETER6, '
327 ||'PARAMETER7, '
331 ||'PREV_MILESTONE, '
328 ||'PARAMETER8, '
329 ||'PARAMETER9, '
330 ||'MASTER_WO, '
332 ||'NEXT_MILESTONE, '
333 ||'ORGANIZATION_ID,'
334 ||'ITEM_ALTERNATES_EXIST, '
335 ||'FLEET_HEADER_ID,'
336 ||'PRODUCT_CLASSIFICATION, '
337 -- ||'Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
338 ||'MAINTENANCE_REQT, '
339 ||'WIP_ENTITY_ID, '
340 ||'REFRESH_ID, '
341 ||'SR_INSTANCE_ID, '
342 ||'LAST_UPDATE_DATE, '
343 ||'LAST_UPDATED_BY, '
344 ||'CREATION_DATE, '
345 ||'CREATED_BY) '
346 ||'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
347 ||'mswa.PRODUCES_TO_STOCK, '
348 ||'mswa.SERIAL_NUM, '
349 ||'mswa.VISIT_ID, '
350 ||'mswa.VISIT_NAME, '
351 ||'mswa.PARAMETER1, '
352 ||'mswa.PARAMETER2, '
353 ||'mswa.PARAMETER3, '
354 ||'mswa.PARAMETER4, '
355 ||'mswa.PARAMETER5, '
356 ||'mswa.PARAMETER6, '
357 ||'mswa.PARAMETER7, '
358 ||'mswa.PARAMETER8, '
359 ||'mswa.PARAMETER9, '
360 ||'mswa.MASTER_WO, '
361 ||'mswa.PREV_MILESTONE, '
362 ||'mswa.NEXT_MILESTONE, '
363 ||'mswa.ORGANIZATION_ID, '
364 ||'mswa.ITEM_ALTERNATES_EXIST, '
365 ||'mswa.FLEET_HEADER_ID,'
366 ||'mswa.PRODUCT_CLASSIFICATION, '
367 -- ||'mswa.Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
368 ||'mswa.MAINTENANCE_REQT, '
369 ||'mswa.WIP_ENTITY_ID, '
370 ||':v_last_collection_id, '
371 ||':v_instance_id, '
372 ||':v_current_date, '
373 ||':v_current_user, '
374 ||':v_current_date, '
375 ||':v_current_user '
376 ||' FROM MSC_ST_WO_ATTRIBUTES mswa, '
377 || lv_supplies_tbl||' ms'
378 ||' WHERE mswa.DELETED_FLAG = '||MSC_UTIL.SYS_NO
379 ||' AND mswa.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
380 ||' AND ms.SR_INSTANCE_ID(+)= mswa.SR_INSTANCE_ID'
381 ||' AND ms.ORGANIZATION_ID(+)= mswa.ORGANIZATION_ID'
382 ||' AND ms.DISPOSITION_ID(+)= mswa.WIP_ENTITY_ID*2'
383 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
384 ||' AND ms.ORDER_TYPE = 70' ;
385
386 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
387 MSC_CL_COLLECTION.v_is_partial_refresh) OR
388 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
389 EXECUTE IMMEDIATE lv_cursor_stmt using
390 MSC_CL_COLLECTION.v_last_collection_id,
391 MSC_CL_COLLECTION.v_instance_id,
392 MSC_CL_COLLECTION.v_current_date,
393 MSC_CL_COLLECTION.v_current_user,
394 MSC_CL_COLLECTION.v_current_date,
395 MSC_CL_COLLECTION.v_current_user;
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
401 RAISE;
402 END;
403
404 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
405 (MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
406 FOR c1 IN c_del LOOP
407
408 BEGIN
409 DELETE MSC_WO_ATTRIBUTES
410 WHERE SR_INSTANCE_ID= c1.sr_instance_id
411 AND VISIT_ID = c1.visit_id
412 AND SUPPLY_ID = c1.supply_id
413 AND ORGANIZATION_ID = c1.organization_id;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
418 'An error has occurred during deletion of WO attributes.');
419 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
420 RAISE;
421 END;
422
423 END LOOP;
424 COMMIT;
425
426 lv_cursor_stmt:= 'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
427 ||'mswa.PRODUCES_TO_STOCK, '
428 ||'mswa.SERIAL_NUM, '
429 ||'mswa.VISIT_ID, '
430 ||'mswa.VISIT_NAME, '
431 ||'mswa.PARAMETER1, '
432 ||'mswa.PARAMETER2, '
433 ||'mswa.PARAMETER3, '
434 ||'mswa.PARAMETER4, '
435 ||'mswa.PARAMETER5, '
436 ||'mswa.PARAMETER6, '
437 ||'mswa.PARAMETER7, '
438 ||'mswa.PARAMETER8, '
439 ||'mswa.PARAMETER9, '
440 ||'mswa.MASTER_WO, '
441 ||'mswa.PREV_MILESTONE, '
442 ||'mswa.NEXT_MILESTONE, '
443 ||'mswa.ORGANIZATION_ID, '
444 ||'mswa.ITEM_ALTERNATES_EXIST, '
445 ||'mswa.PRODUCT_CLASSIFICATION, '
446 -- ||'mswa.Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
450 || lv_supplies_tbl||' ms'
447 ||'mswa.MAINTENANCE_REQT, '
448 ||'mswa.WIP_ENTITY_ID '
449 ||' FROM MSC_ST_WO_ATTRIBUTES mswa, '
451 ||' WHERE mswa.DELETED_FLAG = '||MSC_UTIL.SYS_NO
452 ||' AND mswa.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
453 ||' AND ms.SR_INSTANCE_ID(+)= mswa.SR_INSTANCE_ID'
454 ||' AND ms.ORGANIZATION_ID(+)= mswa.ORGANIZATION_ID'
455 ||' AND ms.DISPOSITION_ID(+)= mswa.WIP_ENTITY_ID*2'
456 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
457 ||' AND ms.ORDER_TYPE = 70' ;
458
459 OPEN c_ref_cur FOR lv_cursor_stmt;
460 LOOP
461 fetch c_ref_cur into c_rec;
462 exit when c_ref_cur%notfound;
463 UPDATE MSC_WO_ATTRIBUTES
464 SET
465 SERIAL_NUM = c_rec.SERIAL_NUM,
466 PRODUCES_TO_STOCK = c_rec.PRODUCES_TO_STOCK,
467 VISIT_ID = c_rec.VISIT_ID,
468 VISIT_NAME = c_rec.VISIT_NAME,
469 PARAMETER1 = c_rec.PARAMETER1,
470 PARAMETER2 = c_rec.PARAMETER2,
471 PARAMETER3 = c_rec.PARAMETER3,
472 PARAMETER4 = c_rec.PARAMETER4,
473 PARAMETER5 = c_rec.PARAMETER5,
474 PARAMETER6 = c_rec.PARAMETER6,
475 PARAMETER7 = c_rec.PARAMETER7,
476 PARAMETER8 = c_rec.PARAMETER8,
477 PARAMETER9 = c_rec.PARAMETER9,
478 MASTER_WO = c_rec.MASTER_WO,
479 PREV_MILESTONE = c_rec.PREV_MILESTONE,
480 NEXT_MILESTONE = c_rec.NEXT_MILESTONE,
481 ITEM_ALTERNATES_EXIST = c_rec.ITEM_ALTERNATES_EXIST,
482 PRODUCT_CLASSIFICATION = c_rec.PRODUCT_CLASSIFICATION,
483 -- Operating_Fleet = c_rec.Operating_Fleet,/* remove the above line and uncomment this line for bug fix# 12428753 */
484 MAINTENANCE_REQT = c_rec.MAINTENANCE_REQT,
485 WIP_ENTITY_ID = c_rec.WIP_ENTITY_ID,
486 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
487 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
488 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
489 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
490 AND SUPPLY_ID = c_rec.SUPPLY_ID
491 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
492
493 IF SQL%NOTFOUND THEN
494
495 lv_cursor_stmt:=
496 'INSERT INTO '||lv_tbl
497 ||'(SUPPLY_ID,'
498 ||'PRODUCES_TO_STOCK, '
499 ||'SERIAL_NUM, '
500 ||'VISIT_ID, '
501 ||'VISIT_NAME, '
502 ||'PARAMETER1, '
503 ||'PARAMETER2, '
504 ||'PARAMETER3, '
505 ||'PARAMETER4, '
506 ||'PARAMETER5, '
507 ||'PARAMETER6, '
508 ||'PARAMETER7, '
509 ||'PARAMETER8, '
510 ||'PARAMETER9, '
511 ||'MASTER_WO, '
512 ||'PREV_MILESTONE, '
513 ||'NEXT_MILESTONE, '
514 ||'ORGANIZATION_ID,'
515 ||'ITEM_ALTERNATES_EXIST, '
516 ||'PRODUCT_CLASSIFICATION, '
517 -- ||'Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
518 ||'MAINTENANCE_REQT, '
519 ||'WIP_ENTITY_ID, '
520 ||'REFRESH_ID, '
521 ||'SR_INSTANCE_ID, '
522 ||'LAST_UPDATE_DATE, '
523 ||'LAST_UPDATED_BY, '
524 ||'CREATION_DATE, '
525 ||'CREATED_BY) '
526 ||'values ( :SUPPLY_ID,'
527 ||':PRODUCES_TO_STOCK, '
528 ||':SERIAL_NUM, '
529 ||':VISIT_ID, '
530 ||':VISIT_NAME, '
531 ||':PARAMETER1, '
532 ||':PARAMETER2, '
533 ||':PARAMETER3, '
534 ||':PARAMETER4, '
535 ||':PARAMETER5, '
536 ||':PARAMETER6, '
537 ||':PARAMETER7, '
538 ||':PARAMETER8, '
539 ||':PARAMETER9, '
540 ||':MASTER_WO, '
541 ||':PREV_MILESTONE, '
542 ||':NEXT_MILESTONE, '
543 ||':ORGANIZATION_ID, '
544 ||':ITEM_ALTERNATES_EXIST, '
548 ||':WIP_ENTITY_ID, '
545 ||':PRODUCT_CLASSIFICATION, '
546 -- ||':Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
547 ||':MAINTENANCE_REQT, '
549 ||':v_last_collection_id, '
550 ||':v_instance_id, '
551 ||':v_current_date, '
552 ||':v_current_user, '
553 ||':v_current_date, '
554 ||':v_current_user) ';
555
556 EXECUTE IMMEDIATE lv_cursor_stmt using
557 c_rec.SUPPLY_ID,
558 c_rec.PRODUCES_TO_STOCK,
559 c_rec.SERIAL_NUM,
560 c_rec.VISIT_ID,
561 c_rec.VISIT_NAME,
562 c_rec.PARAMETER1,
563 c_rec.PARAMETER2,
564 c_rec.PARAMETER3,
565 c_rec.PARAMETER4,
566 c_rec.PARAMETER5,
567 c_rec.PARAMETER6,
568 c_rec.PARAMETER7,
569 c_rec.PARAMETER8,
570 c_rec.PARAMETER9,
571 c_rec.MASTER_WO,
572 c_rec.PREV_MILESTONE,
573 c_rec.NEXT_MILESTONE,
574 c_rec.ORGANIZATION_ID,
575 c_rec.ITEM_ALTERNATES_EXIST,
576 c_rec.PRODUCT_CLASSIFICATION,
580 MSC_CL_COLLECTION.v_last_collection_id,
577 -- c_rec.Operating_Fleet, /* remove the above line and uncomment this line for bug fix# 12428753 */
578 c_rec.MAINTENANCE_REQT,
579 c_rec.WIP_ENTITY_ID,
581 MSC_CL_COLLECTION.v_instance_id,
582 MSC_CL_COLLECTION.v_current_date,
583 MSC_CL_COLLECTION.v_current_user,
584 MSC_CL_COLLECTION.v_current_date,
585 MSC_CL_COLLECTION.v_current_user;
586 END IF;
587 END LOOP;
588 END IF;
589
590 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
591 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
592 lv_retcode,
593 'MSC_WO_ATTRIBUTES',
594 MSC_CL_COLLECTION.v_INSTANCE_CODE,
595 MSC_UTIL.G_ERROR);
596
597 IF lv_retcode = MSC_UTIL.G_ERROR THEN
598 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
599 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
600 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
601 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
602 END IF;
603
604 END IF;
605 COMMIT;
606
607 END LOAD_WO_ATTRIBUTES;
608
609 PROCEDURE LOAD_WORK_BREAKDOWN_STRUCT IS
610 CURSOR c1 IS
611 SELECT mswbs.PARAMETER_NAME,
612 mswbs.DISPLAY_NAME,
613 mswbs.ORGANIZATION_ID,
614 mswbs.SR_INSTANCE_ID
615 FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
616 WHERE mswbs.deleted_flag = MSC_UTIL.SYS_NO
617 AND mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
618
619 CURSOR c_del IS
620 SELECT mswbs.PARAMETER_NAME,
621 mswbs.DISPLAY_NAME,
622 mswbs.ORGANIZATION_ID,
623 mswbs.SR_INSTANCE_ID
624 FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
625 WHERE mswbs.deleted_flag = MSC_UTIL.SYS_YES
626 AND mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
627
628 BEGIN
629 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
630 'In Procedure LOAD_WORK_BREAKDOWN_STRUCT ');
631
632 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
633 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
634 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
635 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
636 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WORK_BREAKDOWN_STRUCT',
637 MSC_CL_COLLECTION.v_instance_id,
638 -1);
639 ELSE
640 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
641 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WORK_BREAKDOWN_STRUCT',
642 MSC_CL_COLLECTION.v_instance_id,
643 null,
644 v_sub_str);
645 END IF;
646 END IF;
647
648
649 BEGIN
650 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
651 lv_tbl:= 'WORK_BREAKDOWN_STRUCT_'
655 END IF;
652 ||MSC_CL_COLLECTION.v_instance_code;
653 ELSE
654 lv_tbl:= 'MSC_WORK_BREAKDOWN_STRUCT';
656
657 lv_cursor_stmt:=
658 'INSERT INTO '||lv_tbl
659 ||'(PARAMETER_NAME, '
660 ||' DISPLAY_NAME, '
661 ||' ORGANIZATION_ID, '
662 ||' REFRESH_ID, '
663 ||' SR_INSTANCE_ID, '
664 ||' LAST_UPDATE_DATE, '
665 ||' LAST_UPDATED_BY, '
666 ||' CREATION_DATE, '
667 ||' CREATED_BY) '
668 ||'SELECT mswbs.PARAMETER_NAME,'
669 ||'mswbs.DISPLAY_NAME, '
670 ||'mswbs.ORGANIZATION_ID,'
671 ||':v_last_collection_id, '
672 ||':v_instance_id, '
673 ||':v_current_date, '
674 ||':v_current_user, '
675 ||':v_current_date, '
676 ||':v_current_user '
677 ||' FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs '
678 ||'WHERE mswbs.deleted_flag ='|| MSC_UTIL.SYS_NO
679 ||' AND mswbs.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id;
680
681 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
682 MSC_CL_COLLECTION.v_is_partial_refresh) OR
683 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
684 EXECUTE IMMEDIATE lv_cursor_stmt using
685 MSC_CL_COLLECTION.v_last_collection_id,
686 MSC_CL_COLLECTION.v_instance_id,
687 MSC_CL_COLLECTION.v_current_date,
688 MSC_CL_COLLECTION.v_current_user,
689 MSC_CL_COLLECTION.v_current_date,
690 MSC_CL_COLLECTION.v_current_user;
691 END IF;
692 EXCEPTION
693 WHEN OTHERS THEN
694 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
695 RAISE;
696 END;
697
698 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
699 MSC_CL_COLLECTION.v_is_legacy_refresh THEN
700 FOR c_rec IN c_del LOOP
701 BEGIN
702 DELETE MSC_WORK_BREAKDOWN_STRUCT
703 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
704 AND PARAMETER_NAME = c_rec.parameter_name
705 AND ORGANIZATION_ID = c_rec.organization_id;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
710 'An error has occurred during deletion of WBS.');
711 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
712 RAISE;
713 END;
714
715 END LOOP;
716 COMMIT;
717 FOR c_rec IN c1 LOOP
718 UPDATE MSC_WORK_BREAKDOWN_STRUCT
719 SET DISPLAY_NAME = c_rec.DISPLAY_NAME,
720 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
721 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
722 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
723 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
724 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
725 AND PARAMETER_NAME = c_rec.PARAMETER_NAME;
726
727 IF SQL%NOTFOUND THEN
728
729 lv_cursor_stmt:=
730 'INSERT INTO '||lv_tbl
731 ||'(PARAMETER_NAME, '
732 ||' DISPLAY_NAME, '
733 ||' ORGANIZATION_ID, '
734 ||' REFRESH_ID, '
735 ||' SR_INSTANCE_ID, '
736 ||' LAST_UPDATE_DATE, '
737 ||' LAST_UPDATED_BY, '
738 ||' CREATION_DATE, '
739 ||' CREATED_BY) '
740 ||'values (:PARAMETER_NAME,'
741 ||':DISPLAY_NAME, '
742 ||':ORGANIZATION_ID,'
743 ||':v_last_collection_id, '
744 ||':v_instance_id, '
745 ||':v_current_date, '
746 ||':v_current_user, '
747 ||':v_current_date, '
748 ||':v_current_user) ';
749
750 EXECUTE IMMEDIATE lv_cursor_stmt using
751 c_rec.PARAMETER_NAME,
752 c_rec.DISPLAY_NAME,
753 c_rec.ORGANIZATION_ID,
754 MSC_CL_COLLECTION.v_last_collection_id,
755 MSC_CL_COLLECTION.v_instance_id,
756 MSC_CL_COLLECTION.v_current_date,
757 MSC_CL_COLLECTION.v_current_user,
758 MSC_CL_COLLECTION.v_current_date,
759 MSC_CL_COLLECTION.v_current_user;
760 END IF;
761 END LOOP;
762 END IF;
763
764
765 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
766 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
767 lv_retcode,
768 'MSC_WORK_BREAKDOWN_STRUCT',
769 MSC_CL_COLLECTION.v_INSTANCE_CODE,
770 MSC_UTIL.G_ERROR);
771
772 IF lv_retcode = MSC_UTIL.G_ERROR THEN
773 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
774 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
775 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
776 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
780
777 END IF;
778 END IF;
779 COMMIT;
781 END LOAD_WORK_BREAKDOWN_STRUCT ;
782
783 PROCEDURE LOAD_WO_TASK_HIERARCHY IS
784
785 CURSOR c_del IS
786 SELECT mswth.CURR_SUPPLY_ID,
787 mswth.NEXT_SUPPLY_ID,
788 mswth.ORGANIZATION_ID,
789 mswth.SR_INSTANCE_ID
790 FROM MSC_ST_WO_TASK_HIERARCHY mswth
791 WHERE mswth.deleted_flag = MSC_UTIL.SYS_YES
792 AND mswth.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
793 lv_supplies_tbl VARCHAR2(30);
794 type ref_cur is ref cursor;
795 c_ref_cur ref_cur;
796 TYPE cursorRec IS RECORD (
797 CURR_SUPPLY_ID MSC_ST_WO_TASK_HIERARCHY.CURR_SUPPLY_ID%type,
798 NEXT_SUPPLY_ID MSC_ST_WO_TASK_HIERARCHY.NEXT_SUPPLY_ID%type,
799 PRECEDENCE_CONSTRAINT MSC_ST_WO_TASK_HIERARCHY.PRECEDENCE_CONSTRAINT%type,
800 MIN_SEPARATION MSC_ST_WO_TASK_HIERARCHY.MIN_SEPARATION%type,
801 MIN_SEP_TIME_UNIT MSC_ST_WO_TASK_HIERARCHY.MIN_SEP_TIME_UNIT%type,
802 MAX_SEPARATION MSC_ST_WO_TASK_HIERARCHY.MAX_SEPARATION%type,
803 MAX_SEP_TIME_UNIT MSC_ST_WO_TASK_HIERARCHY.MAX_SEP_TIME_UNIT%type,
804 ORGANIZATION_ID MSC_ST_WO_TASK_HIERARCHY.ORGANIZATION_ID%type,
805 TASK_LINK_ID MSC_ST_WO_TASK_HIERARCHY.TASK_LINK_ID%type,
806 CURRENT_WIP_ENTITY_ID MSC_ST_WO_TASK_HIERARCHY.CURRENT_WIP_ENTITY_ID%type,
807 NEXT_WIP_ENTITY_ID MSC_ST_WO_TASK_HIERARCHY.NEXT_WIP_ENTITY_ID%type
808 );
809
810 c_rec cursorRec;
811 BEGIN
812 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
813 'In Procedure LOAD_WO_TASK_HIERARCHY ');
814
815 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
816 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
817 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
818 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
819 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',
820 MSC_CL_COLLECTION.v_instance_id, -1);
821 ELSE
822 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
823 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',
824 MSC_CL_COLLECTION.v_instance_id,
825 null,
826 v_sub_str);
827 END IF;
828 END IF;
829
830
831 BEGIN
832 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
833 lv_tbl:= 'WO_TASK_HIERARCHY_'||MSC_CL_COLLECTION.v_instance_code;
834 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
835 ELSE
836 lv_tbl:= 'MSC_WO_TASK_HIERARCHY';
837 lv_supplies_tbl:= 'MSC_SUPPLIES';
838 END IF;
839
840 lv_cursor_stmt:=
841 'INSERT INTO '||lv_tbl
842 ||'(CURR_SUPPLY_ID,'
843 ||'NEXT_SUPPLY_ID, '
844 ||'PRECEDENCE_CONSTRAINT, '
845 ||'MIN_SEPARATION, '
846 ||'MIN_SEP_TIME_UNIT, '
847 ||'MAX_SEPARATION, '
848 ||'MAX_SEP_TIME_UNIT, '
849 ||'ORGANIZATION_ID, '
850 ||'TASK_LINK_ID, '
851 ||'CURRENT_WIP_ENTITY_ID, '
852 ||'NEXT_WIP_ENTITY_ID, '
853 ||'REFRESH_ID, '
854 ||'SR_INSTANCE_ID, '
855 ||'LAST_UPDATE_DATE, '
856 ||'LAST_UPDATED_BY, '
857 ||'CREATION_DATE, '
858 ||'CREATED_BY) '
859 ||'SELECT NVL(ms1.TRANSACTION_ID,-1) CURR_SUPPLY_ID, '
860 ||'NVL(ms2.TRANSACTION_ID,-1) NEXT_SUPPLY_ID, '
861 ||'mswth.PRECEDENCE_CONSTRAINT, '
862 ||'mswth.MIN_SEPARATION, '
863 ||'mswth.MIN_SEP_TIME_UNIT, '
864 ||'mswth.MAX_SEPARATION, '
865 ||'mswth.MAX_SEP_TIME_UNIT, '
866 ||'mswth.ORGANIZATION_ID, '
867 ||'mswth.TASK_LINK_ID, '
868 ||'mswth.CURRENT_WIP_ENTITY_ID, '
869 ||'mswth.NEXT_WIP_ENTITY_ID, '
873 ||':v_current_user, '
870 ||':v_last_collection_id, '
871 ||':v_instance_id, '
872 ||':v_current_date, '
874 ||':v_current_date, '
875 ||':v_current_user '
876 ||' FROM MSC_ST_WO_TASK_HIERARCHY mswth, '
877 || lv_supplies_tbl||' ms1,'
878 || lv_supplies_tbl||' ms2'
882 ||' AND ms1.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
879 ||' WHERE mswth.deleted_flag = '|| MSC_UTIL.SYS_NO
880 ||' AND mswth.SR_INSTANCE_ID= '
881 || MSC_CL_COLLECTION.v_instance_id
883 ||' AND ms2.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
884 ||' AND ms1.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
885 ||' AND ms2.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
886 ||' AND ms1.DISPOSITION_ID(+)=mswth.CURRENT_WIP_ENTITY_ID*2'
887 ||' AND ms2.DISPOSITION_ID(+)=mswth.NEXT_WIP_ENTITY_ID*2'
888 ||' AND ms1.MAINTENANCE_OBJECT_SOURCE = 2'
889 ||' AND ms1.MAINTENANCE_OBJECT_SOURCE = '
890 ||' ms2.MAINTENANCE_OBJECT_SOURCE'
891 ||' AND ms1.ORDER_TYPE = 70'
892 ||' AND ms1.ORDER_TYPE = ms2.ORDER_TYPE' ;
893
894 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
895 MSC_CL_COLLECTION.v_is_partial_refresh) OR
896 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
897 EXECUTE IMMEDIATE lv_cursor_stmt using
898 MSC_CL_COLLECTION.v_last_collection_id,
899 MSC_CL_COLLECTION.v_instance_id,
900 MSC_CL_COLLECTION.v_current_date,
901 MSC_CL_COLLECTION.v_current_user,
902 MSC_CL_COLLECTION.v_current_date,
903 MSC_CL_COLLECTION.v_current_user;
904 END IF;
905 EXCEPTION
906 WHEN OTHERS THEN
907 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
908 RAISE;
909 END;
910
911 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
912 MSC_CL_COLLECTION.v_is_legacy_refresh THEN
913 FOR c_rec IN c_del LOOP
914 BEGIN
915 DELETE MSC_WO_TASK_HIERARCHY
916 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
917 AND CURR_SUPPLY_ID = c_rec.curr_supply_id
918 AND NEXT_SUPPLY_ID = c_rec.next_supply_id
919 AND ORGANIZATION_ID = c_rec.organization_id;
920 EXCEPTION
921 WHEN OTHERS THEN
922 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
923 'An error has occurred during deletion of WO task hierarchy.');
924 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
925 RAISE;
926 END;
927 END LOOP;
928 COMMIT;
929
930 lv_cursor_stmt:= 'SELECT NVL(ms1.TRANSACTION_ID,-1) CURR_SUPPLY_ID, '
931 ||'NVL(ms2.TRANSACTION_ID,-1) NEXT_SUPPLY_ID, '
932 ||'mswth.PRECEDENCE_CONSTRAINT, '
933 ||'mswth.MIN_SEPARATION, '
934 ||'mswth.MIN_SEP_TIME_UNIT, '
935 ||'mswth.MAX_SEPARATION, '
936 ||'mswth.MAX_SEP_TIME_UNIT, '
937 ||'mswth.ORGANIZATION_ID, '
938 ||'mswth.TASK_LINK_ID, '
939 ||'mswth.CURRENT_WIP_ENTITY_ID, '
940 ||'mswth.NEXT_WIP_ENTITY_ID '
941 ||' FROM MSC_ST_WO_TASK_HIERARCHY mswth, '
942 || lv_supplies_tbl||' ms1,'
943 || lv_supplies_tbl||' ms2'
944 ||' WHERE mswth.deleted_flag = '|| MSC_UTIL.SYS_NO
945 ||' AND mswth.SR_INSTANCE_ID= '
946 || MSC_CL_COLLECTION.v_instance_id
947 ||' AND ms1.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
948 ||' AND ms2.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
949 ||' AND ms1.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
950 ||' AND ms2.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
951 ||' AND ms1.DISPOSITION_ID(+)=mswth.CURRENT_WIP_ENTITY_ID*2'
952 ||' AND ms2.DISPOSITION_ID(+)=mswth.NEXT_WIP_ENTITY_ID*2'
953 ||' AND ms1.MAINTENANCE_OBJECT_SOURCE = 2'
954 ||' AND ms1.MAINTENANCE_OBJECT_SOURCE = '
955 ||' ms2.MAINTENANCE_OBJECT_SOURCE'
956 ||' AND ms1.ORDER_TYPE = 70'
957 ||' AND ms1.ORDER_TYPE = ms2.ORDER_TYPE' ;
958
959 OPEN c_ref_cur FOR lv_cursor_stmt;
960 LOOP
961 fetch c_ref_cur into c_rec;
962 exit when c_ref_cur%notfound;
963 UPDATE MSC_WO_TASK_HIERARCHY
964 SET
965 PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
966 MIN_SEPARATION = c_rec.MIN_SEPARATION,
967 MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
968 MAX_SEPARATION = c_rec.MAX_SEPARATION,
969 MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
970 TASK_LINK_ID = c_rec.TASK_LINK_ID,
971 CURRENT_WIP_ENTITY_ID = c_rec.CURRENT_WIP_ENTITY_ID,
972 NEXT_WIP_ENTITY_ID = c_rec.NEXT_WIP_ENTITY_ID,
973 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
974 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
975 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
976 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
977 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
978 AND CURR_SUPPLY_ID = c_rec.CURR_SUPPLY_ID
979 AND NEXT_SUPPLY_ID = c_rec.NEXT_SUPPLY_ID;
980
981 IF SQL%NOTFOUND THEN
982
983 lv_cursor_stmt:=
984 'INSERT INTO '||lv_tbl
985 ||'(CURR_SUPPLY_ID,'
986 ||'NEXT_SUPPLY_ID, '
990 ||'MAX_SEPARATION, '
987 ||'PRECEDENCE_CONSTRAINT, '
988 ||'MIN_SEPARATION, '
989 ||'MIN_SEP_TIME_UNIT, '
991 ||'MAX_SEP_TIME_UNIT, '
992 ||'ORGANIZATION_ID, '
993 ||'TASK_LINK_ID, '
994 ||'CURRENT_WIP_ENTITY_ID, '
998 ||'LAST_UPDATE_DATE, '
995 ||'NEXT_WIP_ENTITY_ID, '
996 ||'REFRESH_ID, '
997 ||'SR_INSTANCE_ID, '
999 ||'LAST_UPDATED_BY, '
1000 ||'CREATION_DATE, '
1001 ||'CREATED_BY) '
1002 ||'values (:CURR_SUPPLY_ID, '
1003 ||':NEXT_SUPPLY_ID, '
1004 ||':PRECEDENCE_CONSTRAINT, '
1005 ||':MIN_SEPARATION, '
1006 ||':MIN_SEP_TIME_UNIT, '
1007 ||':MAX_SEPARATION, '
1008 ||':MAX_SEP_TIME_UNIT, '
1009 ||':ORGANIZATION_ID, '
1010 ||':TASK_LINK_ID, '
1011 ||':CURRENT_WIP_ENTITY_ID, '
1012 ||':NEXT_WIP_ENTITY_ID, '
1013 ||':v_last_collection_id, '
1014 ||':v_instance_id, '
1015 ||':v_current_date, '
1016 ||':v_current_user, '
1017 ||':v_current_date, '
1018 ||':v_current_user) ';
1019
1020 EXECUTE IMMEDIATE lv_cursor_stmt using
1021 c_rec.CURR_SUPPLY_ID,
1022 c_rec.NEXT_SUPPLY_ID,
1023 c_rec.PRECEDENCE_CONSTRAINT,
1024 c_rec.MIN_SEPARATION,
1025 c_rec.MIN_SEP_TIME_UNIT,
1026 c_rec.MAX_SEPARATION,
1027 c_rec.MAX_SEP_TIME_UNIT,
1028 c_rec.ORGANIZATION_ID,
1029 c_rec.TASK_LINK_ID,
1030 c_rec.CURRENT_WIP_ENTITY_ID,
1031 c_rec.NEXT_WIP_ENTITY_ID,
1032 MSC_CL_COLLECTION.v_last_collection_id,
1033 MSC_CL_COLLECTION.v_instance_id,
1034 MSC_CL_COLLECTION.v_current_date,
1035 MSC_CL_COLLECTION.v_current_user,
1036 MSC_CL_COLLECTION.v_current_date,
1037 MSC_CL_COLLECTION.v_current_user;
1038 END IF;
1039 END LOOP;
1040 END IF;
1041
1042
1043 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1044 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1045 lv_retcode,
1046 'MSC_WO_TASK_HIERARCHY',
1047 MSC_CL_COLLECTION.v_INSTANCE_CODE,
1048 MSC_UTIL.G_ERROR);
1049
1050 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1051 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1052 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1053 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1054 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1055 END IF;
1056
1057 END IF;
1058 COMMIT;
1059
1060 END LOAD_WO_TASK_HIERARCHY;
1061
1062 PROCEDURE LOAD_WO_OPERATION_REL IS
1063 CURSOR c_del IS
1064 SELECT mswor.SUPPLY_ID,
1065 mswor.FROM_OP_SEQ_NUM,
1066 mswor.FROM_OP_RES_SEQ_NUM,
1067 mswor.TO_OP_SEQ_NUM,
1068 mswor.TO_OP_RES_SEQ_NUM,
1069 mswor.ORGANIZATION_ID,
1070 mswor.SR_INSTANCE_ID
1071 FROM MSC_ST_WO_OPERATION_REL mswor
1072 WHERE mswor.deleted_flag = MSC_UTIL.SYS_YES
1073 AND mswor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1074
1075 lv_supplies_tbl VARCHAR2(30);
1076 type ref_cur is ref cursor;
1077 c_ref_cur ref_cur;
1078 TYPE cursorRec IS RECORD (
1079 SUPPLY_ID MSC_ST_WO_OPERATION_REL.SUPPLY_ID%type,
1080 PRECEDENCE_CONSTRAINT MSC_ST_WO_OPERATION_REL.PRECEDENCE_CONSTRAINT%type,
1081 MIN_SEPARATION MSC_ST_WO_OPERATION_REL.MIN_SEPARATION%type,
1082 MIN_SEP_TIME_UNIT MSC_ST_WO_OPERATION_REL.MIN_SEP_TIME_UNIT%type,
1083 MAX_SEPARATION MSC_ST_WO_OPERATION_REL.MAX_SEPARATION%type,
1084 MAX_SEP_TIME_UNIT MSC_ST_WO_OPERATION_REL.MAX_SEP_TIME_UNIT%type,
1085 FROM_OP_SEQ_NUM MSC_ST_WO_OPERATION_REL.FROM_OP_SEQ_NUM%type,
1086 FROM_OP_RES_SEQ_NUM MSC_ST_WO_OPERATION_REL.FROM_OP_RES_SEQ_NUM%type,
1087 FROM_OP_DESC MSC_ST_WO_OPERATION_REL.FROM_OP_DESC%type,
1088 TO_OP_SEQ_NUM MSC_ST_WO_OPERATION_REL.TO_OP_SEQ_NUM%type,
1089 TO_OP_RES_SEQ_NUM MSC_ST_WO_OPERATION_REL.TO_OP_RES_SEQ_NUM%type,
1090 TO_OP_DESC MSC_ST_WO_OPERATION_REL.TO_OP_DESC%type,
1091 ORGANIZATION_ID MSC_ST_WO_OPERATION_REL.ORGANIZATION_ID%type,
1092 WORKORDER_ID MSC_ST_WO_OPERATION_REL.WORKORDER_ID%type,
1093 WIP_ENTITY_ID MSC_ST_WO_OPERATION_REL.WIP_ENTITY_ID%type
1094 );
1095
1096 c_rec cursorRec;
1097 BEGIN
1098 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1099 'In Procedure LOAD_WO_OPERATION_REL ');
1100
1104 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1101 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
1102 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1103 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
1105 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
1106 MSC_CL_COLLECTION.v_instance_id,
1107 -1);
1108 ELSE
1109 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1110 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
1111 MSC_CL_COLLECTION.v_instance_id,
1112 null,
1113 v_sub_str);
1114 END IF;
1115 END IF;
1116
1117
1118 BEGIN
1119 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1120 lv_tbl:= 'WO_OPERATION_REL_'||MSC_CL_COLLECTION.v_instance_code;
1121 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1122 ELSE
1123 lv_tbl:= 'MSC_WO_OPERATION_REL';
1124 lv_supplies_tbl:= 'MSC_SUPPLIES';
1125 END IF;
1126
1127 lv_cursor_stmt:=
1128 'INSERT INTO '||lv_tbl
1129 ||'(SUPPLY_ID,'
1130 ||'PRECEDENCE_CONSTRAINT, '
1131 ||'MIN_SEPARATION, '
1132 ||'MIN_SEP_TIME_UNIT, '
1133 ||'MAX_SEPARATION, '
1134 ||'MAX_SEP_TIME_UNIT, '
1135 ||'FROM_OP_SEQ_NUM, '
1136 ||'FROM_OP_RES_SEQ_NUM, '
1137 ||'FROM_OP_DESC, '
1138 ||'TO_OP_SEQ_NUM, '
1139 ||'TO_OP_RES_SEQ_NUM, '
1140 ||'TO_OP_DESC, '
1141 ||'ORGANIZATION_ID, '
1142 ||'WORKORDER_ID, '
1143 ||'WIP_ENTITY_ID, '
1144 ||'REFRESH_ID, '
1145 ||'SR_INSTANCE_ID, '
1146 ||'LAST_UPDATE_DATE, '
1147 ||'LAST_UPDATED_BY, '
1148 ||'CREATION_DATE, '
1149 ||'CREATED_BY) '
1150 ||'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
1151 ||'mswor.PRECEDENCE_CONSTRAINT, '
1152 ||'mswor.MIN_SEPARATION, '
1153 ||'mswor.MIN_SEP_TIME_UNIT, '
1154 ||'mswor.MAX_SEPARATION, '
1155 ||'mswor.MAX_SEP_TIME_UNIT, '
1156 ||'mswor.FROM_OP_SEQ_NUM, '
1157 ||'mswor.FROM_OP_RES_SEQ_NUM, '
1158 ||'mswor.FROM_OP_DESC, '
1159 ||'mswor.TO_OP_SEQ_NUM, '
1160 ||'mswor.TO_OP_RES_SEQ_NUM, '
1161 ||'mswor.TO_OP_DESC, '
1162 ||'mswor.ORGANIZATION_ID, '
1163 ||'mswor.WORKORDER_ID, '
1164 ||'mswor.WIP_ENTITY_ID, '
1165 ||':v_last_collection_id, '
1166 ||':v_instance_id, '
1167 ||':v_current_date, '
1168 ||':v_current_user, '
1169 ||':v_current_date, '
1170 ||':v_current_user '
1171 ||' FROM MSC_ST_WO_OPERATION_REL mswor,'
1172 || lv_supplies_tbl||' ms'
1173 ||' WHERE mswor.deleted_flag = '||MSC_UTIL.SYS_NO
1174 ||' AND mswor.SR_INSTANCE_ID= '
1175 ||MSC_CL_COLLECTION.v_instance_id
1176 ||' AND ms.SR_INSTANCE_ID(+)= mswor.SR_INSTANCE_ID'
1177 ||' AND ms.ORGANIZATION_ID(+)= mswor.ORGANIZATION_ID'
1178 ||' AND ms.DISPOSITION_ID(+)= mswor.WIP_ENTITY_ID*2'
1179 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
1180 ||' AND ms.ORDER_TYPE = 70' ;
1181
1182 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
1183 MSC_CL_COLLECTION.v_is_partial_refresh) OR
1184 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
1185 EXECUTE IMMEDIATE lv_cursor_stmt using
1186 MSC_CL_COLLECTION.v_last_collection_id,
1187 MSC_CL_COLLECTION.v_instance_id,
1188 MSC_CL_COLLECTION.v_current_date,
1189 MSC_CL_COLLECTION.v_current_user,
1190 MSC_CL_COLLECTION.v_current_date,
1191 MSC_CL_COLLECTION.v_current_user;
1192 END IF;
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1196 RAISE;
1197 END;
1198
1199 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
1200 MSC_CL_COLLECTION.v_is_legacy_refresh THEN
1201
1202 FOR c_rec IN c_del LOOP
1203 BEGIN
1204 DELETE MSC_WO_OPERATION_REL
1205 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
1206 AND SUPPLY_ID = c_rec.supply_id
1207 AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
1208 AND nvl(FROM_OP_RES_SEQ_NUM,-1) = nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
1209 AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
1210 AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1)
1211 AND ORGANIZATION_ID = c_rec.organization_id;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
1215 'An error has occurred during deletion of WO operation Relation.');
1219 END LOOP;
1216 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1217 RAISE;
1218 END;
1220 COMMIT;
1221
1222 lv_cursor_stmt:= 'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
1223 ||'mswor.PRECEDENCE_CONSTRAINT, '
1224 ||'mswor.MIN_SEPARATION, '
1225 ||'mswor.MIN_SEP_TIME_UNIT, '
1226 ||'mswor.MAX_SEPARATION, '
1227 ||'mswor.MAX_SEP_TIME_UNIT, '
1228 ||'mswor.FROM_OP_SEQ_NUM, '
1229 ||'mswor.FROM_OP_RES_SEQ_NUM, '
1230 ||'mswor.FROM_OP_DESC, '
1231 ||'mswor.TO_OP_SEQ_NUM, '
1232 ||'mswor.TO_OP_RES_SEQ_NUM, '
1233 ||'mswor.TO_OP_DESC, '
1234 ||'mswor.ORGANIZATION_ID, '
1235 ||'mswor.WORKORDER_ID, '
1236 ||'mswor.WIP_ENTITY_ID '
1237 ||' FROM MSC_ST_WO_OPERATION_REL mswor,'
1238 || lv_supplies_tbl||' ms'
1239 ||' WHERE mswor.deleted_flag = '||MSC_UTIL.SYS_NO
1240 ||' AND mswor.SR_INSTANCE_ID= '
1241 ||MSC_CL_COLLECTION.v_instance_id
1242 ||' AND ms.SR_INSTANCE_ID(+)= mswor.SR_INSTANCE_ID'
1243 ||' AND ms.ORGANIZATION_ID(+)= mswor.ORGANIZATION_ID'
1244 ||' AND ms.DISPOSITION_ID(+)= mswor.WIP_ENTITY_ID*2'
1245 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
1246 ||' AND ms.ORDER_TYPE = 70' ;
1247
1248 OPEN c_ref_cur FOR lv_cursor_stmt;
1249 LOOP
1250 fetch c_ref_cur into c_rec;
1251 exit when c_ref_cur%notfound;
1252 UPDATE MSC_WO_OPERATION_REL
1253 SET
1254 PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
1255 FROM_OP_DESC = c_rec.FROM_OP_DESC,
1256 TO_OP_DESC = c_rec.TO_OP_DESC,
1257 MIN_SEPARATION = c_rec.MIN_SEPARATION,
1258 MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
1259 MAX_SEPARATION = c_rec.MAX_SEPARATION,
1260 MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
1261 WORKORDER_ID = c_rec.WORKORDER_ID,
1262 WIP_ENTITY_ID = c_rec.WIP_ENTITY_ID,
1263 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
1264 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1265 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1266 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1267 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
1268 AND SUPPLY_ID = c_rec.SUPPLY_ID
1269 AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
1270 AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
1271 AND nvl(FROM_OP_RES_SEQ_NUM,-1)=nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
1272 AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1);
1273
1274 IF SQL%NOTFOUND THEN
1275 lv_cursor_stmt:=
1276 'INSERT INTO '||lv_tbl
1277 ||'(SUPPLY_ID,'
1278 ||'PRECEDENCE_CONSTRAINT, '
1279 ||'MIN_SEPARATION, '
1280 ||'MIN_SEP_TIME_UNIT, '
1281 ||'MAX_SEPARATION, '
1282 ||'MAX_SEP_TIME_UNIT, '
1283 ||'FROM_OP_SEQ_NUM, '
1284 ||'FROM_OP_RES_SEQ_NUM, '
1285 ||'FROM_OP_DESC, '
1286 ||'TO_OP_SEQ_NUM, '
1287 ||'TO_OP_RES_SEQ_NUM, '
1288 ||'TO_OP_DESC, '
1289 ||'ORGANIZATION_ID, '
1290 ||'WORKORDER_ID, '
1291 ||'WIP_ENTITY_ID, '
1292 ||'REFRESH_ID, '
1293 ||'SR_INSTANCE_ID, '
1294 ||'LAST_UPDATE_DATE, '
1295 ||'LAST_UPDATED_BY, '
1296 ||'CREATION_DATE, '
1297 ||'CREATED_BY) '
1298 ||'values (:SUPPLY_ID,'
1299 ||':PRECEDENCE_CONSTRAINT, '
1300 ||':MIN_SEPARATION, '
1301 ||':MIN_SEP_TIME_UNIT, '
1302 ||':MAX_SEPARATION, '
1303 ||':MAX_SEP_TIME_UNIT, '
1304 ||':FROM_OP_SEQ_NUM, '
1305 ||':FROM_OP_RES_SEQ_NUM, '
1306 ||':FROM_OP_DESC, '
1307 ||':TO_OP_SEQ_NUM, '
1308 ||':TO_OP_RES_SEQ_NUM, '
1309 ||':TO_OP_DESC, '
1310 ||':ORGANIZATION_ID, '
1311 ||':WORKORDER_ID, '
1312 ||':WIP_ENTITY_ID, '
1313 ||':v_last_collection_id, '
1314 ||':v_instance_id, '
1315 ||':v_current_date, '
1316 ||':v_current_user, '
1317 ||':v_current_date, '
1318 ||':v_current_user) ';
1319
1320 EXECUTE IMMEDIATE lv_cursor_stmt using
1321 c_rec.SUPPLY_ID,
1322 c_rec.PRECEDENCE_CONSTRAINT,
1323 c_rec.MIN_SEPARATION,
1324 c_rec.MIN_SEP_TIME_UNIT,
1325 c_rec.MAX_SEPARATION,
1326 c_rec.MAX_SEP_TIME_UNIT,
1330 c_rec.TO_OP_SEQ_NUM,
1327 c_rec.FROM_OP_SEQ_NUM,
1328 c_rec.FROM_OP_RES_SEQ_NUM,
1329 c_rec.FROM_OP_DESC,
1331 c_rec.TO_OP_RES_SEQ_NUM,
1332 c_rec.TO_OP_DESC,
1333 c_rec.ORGANIZATION_ID,
1334 c_rec.WORKORDER_ID,
1335 c_rec.WIP_ENTITY_ID,
1336 MSC_CL_COLLECTION.v_last_collection_id,
1337 MSC_CL_COLLECTION.v_instance_id,
1338 MSC_CL_COLLECTION.v_current_date,
1339 MSC_CL_COLLECTION.v_current_user,
1340 MSC_CL_COLLECTION.v_current_date,
1341 MSC_CL_COLLECTION.v_current_user;
1342 END IF;
1343 END LOOP;
1344 END IF;
1345
1346
1347 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1348 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1349 lv_retcode,
1350 'MSC_WO_OPERATION_REL',
1351 MSC_CL_COLLECTION.v_INSTANCE_CODE,
1352 MSC_UTIL.G_ERROR);
1353
1354 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1355 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1356 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1357 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1358 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1359 END IF;
1360
1361 END IF;
1362 commit;
1363
1364 END LOAD_WO_OPERATION_REL;
1365
1366 PROCEDURE LOAD_WO_MILESTONES IS
1367 CURSOR c1 IS
1368 SELECT mswm.MILESTONE,
1369 mswm.MILESTONE_DESC,
1370 mswm.VISIT_ID,
1371 mswm.ORGANIZATION_ID,
1372 mswm.EARLIEST_START_DATE,
1373 mswm.TARGET_COMPLETION_DATE,
1374 mswm.SR_INSTANCE_ID
1375 FROM MSC_ST_WO_MILESTONES mswm
1376 WHERE mswm.deleted_flag = MSC_UTIL.SYS_NO
1377 AND mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1378
1379 CURSOR c_del IS
1380 SELECT mswm.MILESTONE,
1381 mswm.VISIT_ID,
1382 mswm.ORGANIZATION_ID,
1383 mswm.SR_INSTANCE_ID
1384 FROM MSC_ST_WO_MILESTONES mswm
1385 WHERE mswm.deleted_flag = MSC_UTIL.SYS_YES
1386 AND mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1387
1388 BEGIN
1389 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1390 'In Procedure LOAD_WO_MILESTONES ');
1391
1392 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
1393 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1394 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
1395 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1396 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_MILESTONES',
1397 MSC_CL_COLLECTION.v_instance_id,
1398 -1);
1399 ELSE
1400 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1401 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_MILESTONES',
1402 MSC_CL_COLLECTION.v_instance_id,
1403 null,
1404 v_sub_str);
1405 END IF;
1406 END IF;
1407
1408
1409 BEGIN
1410 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1411 lv_tbl:= 'WO_MILESTONES_'||MSC_CL_COLLECTION.v_instance_code;
1412 ELSE
1413 lv_tbl:= 'MSC_WO_MILESTONES';
1414 END IF;
1415
1416 lv_cursor_stmt:=
1417 'INSERT INTO '||lv_tbl
1418 ||'(MILESTONE, '
1419 ||'MILESTONE_DESC, '
1420 ||'VISIT_ID, '
1421 ||'ORGANIZATION_ID, '
1422 ||'EARLIEST_START_DATE, '
1423 ||'TARGET_COMPLETION_DATE, '
1424 ||'REFRESH_ID, '
1425 ||'SR_INSTANCE_ID, '
1426 ||'LAST_UPDATE_DATE, '
1427 ||'LAST_UPDATED_BY, '
1428 ||'CREATION_DATE, '
1429 ||'CREATED_BY) '
1430 ||'SELECT mswm.MILESTONE,'
1431 ||'mswm.MILESTONE_DESC, '
1432 ||'mswm.VISIT_ID, '
1433 ||'mswm.ORGANIZATION_ID, '
1434 ||'mswm.EARLIEST_START_DATE, '
1435 ||'mswm.TARGET_COMPLETION_DATE, '
1436 ||':v_last_collection_id, '
1437 ||':v_instance_id, '
1438 ||':v_current_date, '
1439 ||':v_current_user, '
1440 ||':v_current_date, '
1441 ||':v_current_user '
1442 ||' FROM MSC_ST_WO_MILESTONES mswm '
1443 ||' WHERE mswm.deleted_flag = '||MSC_UTIL.SYS_NO
1444 ||' AND mswm.SR_INSTANCE_ID= '
1445 ||MSC_CL_COLLECTION.v_instance_id;
1446
1447 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
1448 MSC_CL_COLLECTION.v_is_partial_refresh) OR
1449 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
1450 EXECUTE IMMEDIATE lv_cursor_stmt using
1451 MSC_CL_COLLECTION.v_last_collection_id,
1452 MSC_CL_COLLECTION.v_instance_id,
1453 MSC_CL_COLLECTION.v_current_date,
1454 MSC_CL_COLLECTION.v_current_user,
1455 MSC_CL_COLLECTION.v_current_date,
1459 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
1456 MSC_CL_COLLECTION.v_current_user;
1457 END IF;
1458
1460 MSC_CL_COLLECTION.v_is_legacy_refresh THEN
1461 FOR c_rec IN c_del LOOP
1462 BEGIN
1463 DELETE MSC_WO_MILESTONES
1464 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
1465 AND MILESTONE = c_rec.milestone
1466 AND VISIT_ID = c_rec.visit_id
1467 AND ORGANIZATION_ID = c_rec.organization_id;
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
1471 'An error has occurred during deletion of Milestones.');
1472 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1473 RAISE;
1474 END;
1475 END LOOP;
1476 COMMIT;
1477 FOR c_rec IN c1 LOOP
1478 UPDATE MSC_WO_MILESTONES
1479 SET
1480 MILESTONE_DESC = c_rec.MILESTONE_DESC,
1481 EARLIEST_START_DATE = c_rec.EARLIEST_START_DATE,
1482 TARGET_COMPLETION_DATE = c_rec.TARGET_COMPLETION_DATE,
1483 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
1484 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1485 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1486 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1487 AND VISIT_ID = c_rec.VISIT_ID
1488 AND MILESTONE = c_rec.MILESTONE
1489 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1490
1491 IF SQL%NOTFOUND THEN
1492
1493 lv_cursor_stmt:=
1494 'INSERT INTO '||lv_tbl
1495 ||'(MILESTONE, '
1496 ||'MILESTONE_DESC, '
1497 ||'VISIT_ID, '
1498 ||'ORGANIZATION_ID, '
1499 ||'EARLIEST_START_DATE, '
1500 ||'TARGET_COMPLETION_DATE, '
1501 ||'REFRESH_ID, '
1502 ||'SR_INSTANCE_ID, '
1503 ||'LAST_UPDATE_DATE, '
1504 ||'LAST_UPDATED_BY, '
1505 ||'CREATION_DATE, '
1506 ||'CREATED_BY) '
1507 ||'values (:MILESTONE,'
1508 ||':MILESTONE_DESC, '
1509 ||':VISIT_ID, '
1510 ||':ORGANIZATION_ID, '
1511 ||':EARLIEST_START_DATE, '
1512 ||':TARGET_COMPLETION_DATE, '
1513 ||':v_last_collection_id,'
1514 ||':v_instance_id,'
1515 ||':v_current_date,'
1516 ||':v_current_user,'
1517 ||':v_current_date,'
1518 ||':v_current_user)';
1519
1520 EXECUTE IMMEDIATE lv_cursor_stmt using
1521 c_rec.MILESTONE,
1522 c_rec.MILESTONE_DESC,
1523 c_rec.VISIT_ID,
1524 c_rec.ORGANIZATION_ID,
1525 c_rec.EARLIEST_START_DATE,
1526 c_rec.TARGET_COMPLETION_DATE,
1527 MSC_CL_COLLECTION.v_last_collection_id,
1528 MSC_CL_COLLECTION.v_instance_id,
1529 MSC_CL_COLLECTION.v_current_date,
1530 MSC_CL_COLLECTION.v_current_user,
1531 MSC_CL_COLLECTION.v_current_date,
1532 MSC_CL_COLLECTION.v_current_user;
1533 END IF;
1534 END LOOP;
1535 END IF;
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1540 RAISE;
1541 END;
1542 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1543 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1544 lv_retcode,
1545 'MSC_WO_MILESTONES',
1546 MSC_CL_COLLECTION.v_INSTANCE_CODE,
1547 MSC_UTIL.G_ERROR);
1548
1549 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1550 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1551 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1552 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1553 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1554 END IF;
1555
1556 END IF;
1557 commit;
1558
1559 END LOAD_WO_MILESTONES;
1560
1561 PROCEDURE LOAD_WO_SUB_COMP IS
1562 CURSOR c1 IS
1563 SELECT MIL1.INVENTORY_ITEM_ID PRIMARY_COMPONENT_ID,
1564 MIL2.INVENTORY_ITEM_ID ALTERNATE_COMPONENT_ID,
1565 sub_comp.RANK,
1566 sub_comp.OP_SEQ_NUM,
1567 sub_comp.RATIO,
1568 sub_comp.PLAN_ID
1569 FROM MSC_ST_WO_SUB_COMP sub_comp
1570 , MSC_ITEM_ID_LID MIL1
1571 , MSC_ITEM_ID_LID MIL2
1572 WHERE sub_comp.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1573 AND MIL1.sr_instance_id = sub_comp.sr_instance_id
1574 AND MIL1.SR_INVENTORY_ITEM_ID = sub_comp.PRIMARY_COMPONENT_ID
1575 AND MIL2.sr_instance_id = sub_comp.sr_instance_id
1576 AND MIL2.SR_INVENTORY_ITEM_ID = sub_comp.ALTERNATE_COMPONENT_ID;
1577
1578 CURSOR c_del IS
1579 SELECT TRANSACTION_ID,
1580 ORGANIZATION_ID,
1581 SR_INSTANCE_ID
1582 FROM MSC_ST_WO_SUB_COMP
1583 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1584 lv_supplies_tbl VARCHAR2(30);
1585 BEGIN
1586
1590 -- Hence, we need to delete from this table for all types of
1587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_SUB_COMP ');
1588 -- We do not support net-change collection for the table msc_wo_sub_comp
1589 -- at this time.
1591 -- collection (targeted or net-change)
1592
1593 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
1594 MSC_CL_COLLECTION.v_is_partial_refresh OR
1595 MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
1596
1597 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
1598 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1599 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_SUB_COMP',
1600 MSC_CL_COLLECTION.v_instance_id,
1601 -1);
1602 ELSE
1603 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1604 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_SUB_COMP',
1605 MSC_CL_COLLECTION.v_instance_id,
1606 null,
1607 v_sub_str);
1608 END IF;
1609 END IF;
1610
1611 COMMIT;
1612
1613 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1614 lv_tbl:= 'WO_SUB_COMP_'||MSC_CL_COLLECTION.v_instance_code;
1615 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1616 ELSE
1617 lv_tbl:= 'MSC_WO_SUB_COMP';
1618 lv_supplies_tbl:= 'MSC_SUPPLIES';
1619 END IF;
1620 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1621 'In Procedure LOAD_WO_SUB_COMP, lv_tbl: '||lv_tbl);
1622
1623 lv_cursor_stmt:=
1624 'INSERT INTO '||lv_tbl
1625 ||'(ALTERNATE_COMPONENT_ID,'
1626 ||'TRANSACTION_ID,'
1627 ||'OP_SEQ_NUM,'
1628 ||'ORGANIZATION_ID,'
1629 ||'PLAN_ID,'
1630 ||'PRIMARY_COMPONENT_ID,'
1631 ||'RANK,'
1632 ||'RATIO,'
1633 ||'REQUEST_ID,'
1634 ||'REFRESH_ID,'
1635 ||'SR_INSTANCE_ID,'
1636 ||'LAST_UPDATE_DATE,'
1637 ||'LAST_UPDATED_BY,'
1638 ||'CREATION_DATE,'
1639 ||'CREATED_BY) '
1640 ||'SELECT mil2.inventory_item_id,'
1641 ||'NVL(ms.TRANSACTION_ID,-1) TRANSACTION_ID,'
1642 ||'msw.OP_SEQ_NUM,'
1643 ||'msw.ORGANIZATION_ID,'
1644 ||'-1 ,'
1645 ||'mil1.inventory_item_id,'
1646 ||'msw.RANK,'
1647 ||'msw.RATIO,'
1648 ||'msw.REQUEST_ID,'
1649 ||':v_last_collection_id,'
1650 ||':v_instance_id,'
1651 ||':v_current_date,'
1652 ||':v_current_user,'
1653 ||':v_current_date,'
1654 ||':v_current_user'
1655 ||' FROM MSC_ST_WO_SUB_COMP msw, '
1656 ||lv_supplies_tbl ||' ms, '
1657 ||' msc_item_id_lid MIL1,'
1658 ||' msc_item_id_lid MIL2 '
1659 ||'WHERE msw.SR_INSTANCE_ID= '
1660 ||MSC_CL_COLLECTION.v_instance_id
1661 ||' AND ms.plan_id = -1'
1662 ||' AND ms.SR_INSTANCE_ID = msw.SR_INSTANCE_ID'
1663 ||' AND ms.ORGANIZATION_ID = msw.ORGANIZATION_ID'
1664 ||' AND ms.DISPOSITION_ID = msw.WIP_ENTITY_ID'
1665 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
1666 ||' AND ms.ORDER_TYPE = 70'
1667 ||' AND MIL1.sr_instance_id = msw.sr_instance_id'
1668 ||' AND MIL1.SR_INVENTORY_ITEM_ID = msw.PRIMARY_COMPONENT_ID'
1669 ||' AND MIL2.sr_instance_id = msw.sr_instance_id'
1670 ||' AND MIL2.SR_INVENTORY_ITEM_ID = msw.ALTERNATE_COMPONENT_ID';
1671
1672
1673 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1674 'In Procedure LOAD_WO_SUB_COMP lv_cursor_stmt: '|| lv_cursor_stmt);
1675 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
1676 MSC_CL_COLLECTION.v_is_partial_refresh OR
1677 MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
1678 EXECUTE IMMEDIATE lv_cursor_stmt using
1679 MSC_CL_COLLECTION.v_last_collection_id,
1680 MSC_CL_COLLECTION.v_instance_id,
1681 MSC_CL_COLLECTION.v_current_date,
1682 MSC_CL_COLLECTION.v_current_user,
1683 MSC_CL_COLLECTION.v_current_date,
1684 MSC_CL_COLLECTION.v_current_user;
1685 END IF;
1686
1687
1688 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1689 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1690 lv_retcode,
1691 'MSC_WO_SUB_COMP',
1692 MSC_CL_COLLECTION.v_INSTANCE_CODE,
1693 MSC_UTIL.G_ERROR);
1694
1695 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1696 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1697 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1698 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1699 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1700 END IF;
1701 END IF;
1702 COMMIT;
1703
1704 END LOAD_WO_SUB_COMP;
1705
1706
1707 PROCEDURE LOAD_CMRO_FORECAST_SUPPLIES
1708 IS
1709
1710 lv_supplies_tbl VARCHAR2(30);
1711 lv_cursor_stmt VARCHAR2(32767);
1712 lv_sql_stmt VARCHAR2(32767);
1713
1714 BEGIN
1715 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1716 'In Procedure LOAD_CMRO_FORECAST_SUPPLIES ');
1717
1718 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1719 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1720 ELSE
1721 lv_supplies_tbl:= 'MSC_SUPPLIES';
1725 'SELECT '
1722 END IF;
1723
1724 lv_cursor_stmt:=
1726 ||' -1,'
1727 ||' MSC_SUPPLIES_S.NEXTVAL,'
1728 ||' md.DESIGNATOR_ID,'
1729 ||' ms.ORGANIZATION_ID,'
1730 ||' ms.NEW_WIP_START_DATE,'
1731 ||' ms.NEW_SCHEDULE_DATE,'
1732 ||' ''AGGR-'''||'||MSC_SUPPLIES_AGGR_WO_S.NEXTVAL,'-- CHECK FOR SEQ AND CHANGE THIS mnagilla
1733 ||' t1.INVENTORY_ITEM_ID,' -- mnagilla check for translation
1734 ||' ms.ORDER_TYPE,'
1735 ||' ms.COLL_ORDER_TYPE,'
1736 ||' ms.MAINTENANCE_OBJECT_SOURCE,'
1737 ||' ms.NEW_ORDER_QUANTITY,'
1738 ||' ms.to_be_exploded,'
1739 ||' ms.FIRM_PLANNED_TYPE,' --firm_planned_type
1740 ||' 17,'
1741 ||' ms.SR_INSTANCE_ID,'
1742 ||' ms.PRODUCT_CLASSIFICATION,'
1743 ||' ms.OPERATING_FLEET ,'
1744 ||' ms.MAINTENANCE_REQUIREMENT,'
1745 ||' ms.SOURCE_ITEM_ID,'
1746 ||' :v_last_collection_id,'
1747 ||' :v_current_date,'
1748 ||' :v_current_user,'
1749 ||' :v_current_date,'
1750 ||' :v_current_user '
1751 ||' FROM MSC_ITEM_ID_LID t1,'
1752 ||' MSC_ST_SUPPLIES ms,'
1753 ||' MSC_DESIGNATORS md'
1754 ||' WHERE t1.SR_INVENTORY_ITEM_ID= -1003'
1755 ||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1756 ||' AND ms.DELETED_FLAG = '|| MSC_UTIL.SYS_NO
1757 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md.SRC_SIM_FCST_ID'
1758 ||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1759 ||' AND md.DESIGNATOR_TYPE = 13'
1760 ||' AND md.organization_id = -23453'
1761 ||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1762 ||' AND ms.ORDER_TYPE = 92'
1763 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
1764 ;
1765
1766 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1767
1768 lv_sql_stmt:=
1769 'INSERT INTO '||lv_supplies_tbl
1770 ||' ( PLAN_ID,'
1771 ||' TRANSACTION_ID,'
1772 ||' SCHEDULE_DESIGNATOR_ID,'
1773 ||' ORGANIZATION_ID,'
1774 ||' NEW_WIP_START_DATE,'
1775 ||' NEW_SCHEDULE_DATE,'
1776 ||' ORDER_NUMBER,'
1777 ||' INVENTORY_ITEM_ID,'
1778 ||' ORDER_TYPE,'
1779 ||' COLL_ORDER_TYPE,'
1780 ||' MAINTENANCE_OBJECT_SOURCE,'
1781 ||' NEW_ORDER_QUANTITY,'
1782 ||' to_be_exploded,'
1783 ||' FIRM_PLANNED_TYPE, '
1784 ||' WIP_STATUS_CODE,'
1785 ||' SR_INSTANCE_ID, '
1786 ||' PRODUCT_CLASSIFICATION,'
1787 ||' OPERATING_FLEET ,'
1788 ||' MAINTENANCE_REQUIREMENT,'
1789 ||' SOURCE_ITEM_ID,'
1790 ||' REFRESH_NUMBER, '
1791 ||' LAST_UPDATE_DATE, '
1792 ||' LAST_UPDATED_BY, '
1793 ||' CREATION_DATE, '
1794 ||' CREATED_BY) '
1795 || lv_cursor_stmt;
1796
1797 BEGIN
1798
1799 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1800 'The Insert statement is: ' || lv_sql_stmt);
1801
1802 SAVEPOINT Load_cmro_sup;
1803 EXECUTE IMMEDIATE lv_sql_stmt
1804 USING
1805 MSC_CL_COLLECTION.v_last_collection_id,
1806 MSC_CL_COLLECTION.v_current_date,
1807 MSC_CL_COLLECTION.v_current_user,
1808 MSC_CL_COLLECTION.v_current_date,
1809 MSC_CL_COLLECTION.v_current_user;
1810
1811 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1812 'The number of rows inserted is: ' || SQL%ROWCOUNT);
1813 COMMIT;
1814 RETURN;
1815
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818
1819 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_CMRO_FORECAST_SUPPLIES>>');
1820 IF lv_sql_stmt IS NOT NULL THEN
1821 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
1822 END IF;
1823 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1824
1825 ROLLBACK WORK TO SAVEPOINT Load_cmro_sup;
1826 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
1827
1828 END;
1829
1830 END IF;
1831
1832 END LOAD_CMRO_FORECAST_SUPPLIES;
1833
1834 PROCEDURE LOAD_CMRO_FORECAST_DEMANDS
1835 IS
1836
1837 lv_cursor_stmt VARCHAR2(32767);
1838 lv_tbl VARCHAR2(30);
1839 lv_sql_stmt VARCHAR2(32767);
1840 lv_supplies_tbl VARCHAR2(30);
1841
1842 BEGIN
1843 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1844 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1845 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1846 ELSE
1847 lv_tbl:= 'MSC_DEMANDS';
1848 lv_supplies_tbl:= 'MSC_SUPPLIES';
1849 END IF;
1850
1851
1852 lv_cursor_stmt:=
1853 'SELECT '
1854 ||' -1,'
1855 ||' MSC_DEMANDS_S.NEXTVAL,'
1856 ||' ms.TRANSACTION_ID DISPOSITION_ID ,'
1857 ||' substr(ms.ORDER_NUMBER, 1,62) ORDER_NUMBER, '
1858 ||' ms.SCHEDULE_DESIGNATOR_ID,'
1859 ||' md.ORGANIZATION_ID,'
1860 ||' t1.INVENTORY_ITEM_ID,'
1861 ||' ms.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
1862 ||' md.USING_REQUIREMENT_QUANTITY,'
1863 ||' md.USING_ASSEMBLY_DEMAND_DATE,'
1864 ||' md.SR_INSTANCE_ID,'
1865 ||' md.DEMAND_TYPE, '
1866 ||' md.ORIGINATION_TYPE,'
1867 ||' md.MAINTENANCE_OBJECT_SOURCE,'
1868 ||' 1,'
1872 ||' :v_current_date,'
1869 ||' :v_last_collection_id,'
1870 ||' :v_current_date,'
1871 ||' :v_current_user,'
1873 ||' :v_current_user '
1874 ||' FROM MSC_ITEM_ID_LID t1,'
1875 ||' MSC_DESIGNATORS md1 ,'
1876 ||' MSC_ST_DEMANDS md,'
1877 || lv_supplies_tbl||' ms'
1878 ||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 2'
1879 --||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
1880 ||' AND ms.ORDER_TYPE = 92 '
1881 ||' AND md.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
1882 ||' AND t1.SR_INVENTORY_ITEM_ID= md.inventory_item_id '
1883 ||' AND t1.sr_instance_id= '|| MSC_CL_COLLECTION.v_instance_id
1884 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
1885 ||' AND md.MAINTENANCE_OBJECT_SOURCE = 2'
1886 ||' AND ms.SOURCE_ITEM_ID = md.USING_ASSEMBLY_ITEM_ID'
1887 ||' AND nvl(md.OPERATING_FLEET ,1)= nvl(ms.OPERATING_FLEET,1)'
1888 ||' AND md.MAINTENANCE_REQUIREMENT = ms.MAINTENANCE_REQUIREMENT'
1889 ||' AND md.ORIGINATION_TYPE = 92'
1890 ||' AND ms.PLAN_ID = -1'
1891 ||' AND ms.NEW_WIP_START_DATE = md.USING_ASSEMBLY_DEMAND_DATE'
1892 ||' AND ms.ORGANIZATION_ID = md.ORGANIZATION_ID'
1893 ||' AND md.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
1894 ||' AND md.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
1895 ||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1896 ||' AND md1.DESIGNATOR_TYPE = 13'
1897 ||' AND md1.organization_id = -23453'
1898 ;
1899
1900 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1901
1902 lv_sql_stmt:=
1903 'INSERT INTO '||lv_tbl
1904 ||' ( PLAN_ID,'
1905 ||' DEMAND_ID,'
1906 ||' DISPOSITION_ID,'
1907 ||' ORDER_NUMBER,'
1908 ||' SCHEDULE_DESIGNATOR_ID,'
1909 ||' ORGANIZATION_ID,'
1910 ||' INVENTORY_ITEM_ID,'
1911 ||' USING_ASSEMBLY_ITEM_ID,'
1912 ||' USING_REQUIREMENT_QUANTITY,'
1913 ||' USING_ASSEMBLY_DEMAND_DATE, '
1914 ||' SR_INSTANCE_ID, '
1915 ||' DEMAND_TYPE, '
1916 ||' ORIGINATION_TYPE, '
1917 ||' MAINTENANCE_OBJECT_SOURCE,'
1918 ||' OP_SEQ_NUM,'
1919 ||' REFRESH_NUMBER, '
1920 ||' LAST_UPDATE_DATE, '
1921 ||' LAST_UPDATED_BY, '
1922 ||' CREATION_DATE, '
1923 ||' CREATED_BY) '
1924 || lv_cursor_stmt ;
1925 BEGIN
1926
1927 SAVEPOINT Load_cmro_dem;
1928 EXECUTE IMMEDIATE lv_sql_stmt
1929 USING
1930 MSC_CL_COLLECTION.v_last_collection_id,
1931 MSC_CL_COLLECTION.v_current_date,
1932 MSC_CL_COLLECTION.v_current_user,
1933 MSC_CL_COLLECTION.v_current_date,
1934 MSC_CL_COLLECTION.v_current_user;
1935
1936 COMMIT;
1937 RETURN;
1938
1939 EXCEPTION
1940 WHEN OTHERS THEN
1941
1942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_CMRO_FORECAST_DEMANDS>>');
1943 IF lv_sql_stmt IS NOT NULL THEN
1944 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
1945 END IF;
1946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1947
1948 ROLLBACK WORK TO SAVEPOINT Load_cmro_dem;
1949 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
1950
1951 END;
1952
1953 END IF;
1954
1955
1956 END LOAD_CMRO_FORECAST_DEMANDS;
1957
1958 PROCEDURE LOAD_CMRO_FORECAST_RR
1959 IS
1960
1961 lv_tbl VARCHAR2(30);
1962 lv_cursor_stmt VARCHAR2(32767);
1963 lv_supplies_tbl VARCHAR2(30);
1964 lv_sql_stmt VARCHAR2(32767);
1965
1966 BEGIN
1967
1968 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1969 lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
1970 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1971 ELSE
1972 lv_tbl:= 'MSC_RESOURCE_REQUIREMENTS';
1973 lv_supplies_tbl:= 'MSC_SUPPLIES';
1974 END IF;
1975
1976
1977 lv_cursor_stmt:=
1978 'SELECT '
1979 ||' -1, '
1980 ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
1981 ||' ms.TRANSACTION_ID ,'
1982 ||' mrr.DEPARTMENT_ID,'
1983 ||' mrr.ORGANIZATION_ID,'
1984 ||' mrr.RESOURCE_ID,'
1985 ||' mrr.ASSIGNED_UNITS,'
1986 ||' mrr.OPERATION_HOURS_REQUIRED,'
1987 ||' mrr.TOUCH_TIME,'
1988 ||' mrr.UNADJUSTED_RESOURCE_HOURS,'
1989 ||' mrr.OPERATION_HOURS_REQUIRED,'
1990 ||' ms.NEW_WIP_START_DATE START_DATE,'
1991 ||' 92,'
1992 ||' mrr.SR_INSTANCE_ID,'
1993 ||' LAST_DAY(NEW_WIP_START_DATE) END_DATE,'
1994 ||' mrr.MAINTENANCE_OBJECT_SOURCE,'
1995 ||' 1,'
1996 ||' 1,'
1997 ||' 1,'
1998 ||' :v_last_collection_id,'
1999 ||' :v_current_date,'
2000 ||' :v_current_user,'
2001 ||' :v_current_date,'
2002 ||' :v_current_user '
2003 ||' FROM MSC_ST_RESOURCE_REQUIREMENTS mrr, '
2004 ||' MSC_DESIGNATORS md1 ,'
2005 || lv_supplies_tbl||' ms'
2006 ||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 2'
2007 --||' AND mrr.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
2011 ||' AND mrr.MAINTENANCE_REQUIREMENT = ms.MAINTENANCE_REQUIREMENT'
2008 ||' AND ms.ORDER_TYPE = 92'
2009 ||' AND mrr.MAINTENANCE_OBJECT_SOURCE = 2'
2010 ||' AND nvl(mrr.OPERATING_FLEET,1) = nvl(ms.OPERATING_FLEET,1)'
2012 --||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
2013 ||' AND ms.PLAN_ID = -1'
2014 ||' AND mrr.supply_type = 92'
2015 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
2016 ||' AND ms.SOURCE_ITEM_ID =mrr.INVENTORY_ITEM_ID'
2017 ||' AND ms.NEW_WIP_START_DATE = mrr.START_DATE'
2018 ||' AND ms.ORGANIZATION_ID = mrr.ORGANIZATION_ID'
2019 ||' AND mrr.SR_INSTANCE_ID='|| MSC_CL_COLLECTION.v_instance_id
2020 ||' AND mrr.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
2021 ||' AND mrr.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
2022 ||' AND mrr.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
2023 ||' AND md1.DESIGNATOR_TYPE = 13'
2024 ||' AND md1.organization_id = -23453';
2025
2026
2027 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2028
2029 lv_sql_stmt:= 'INSERT INTO '||lv_tbl
2030 ||' ( PLAN_ID,'
2031 ||' TRANSACTION_ID,'
2032 ||' SUPPLY_ID,'
2033 ||' DEPARTMENT_ID,'
2034 ||' ORGANIZATION_ID,'
2035 ||' RESOURCE_ID,'
2036 ||' ASSIGNED_UNITS, '
2037 ||' RESOURCE_HOURS,'
2038 ||' TOUCH_TIME,'
2039 ||' UNADJUSTED_RESOURCE_HOURS,'
2040 ||' TOTAL_RESOURCE_HOURS,'
2041 ||' START_DATE,'
2042 ||' SUPPLY_TYPE,'
2043 ||' SR_INSTANCE_ID, '
2044 ||' END_DATE,'
2045 ||' MAINTENANCE_OBJECT_SOURCE,'
2046 ||' OPERATION_SEQ_NUM,'
2047 ||' RESOURCE_SEQ_NUM,'
2048 ||' SCHEDULE_FLAG,'
2049 ||' REFRESH_NUMBER, '
2050 ||' LAST_UPDATE_DATE, '
2051 ||' LAST_UPDATED_BY, '
2052 ||' CREATION_DATE, '
2053 ||' CREATED_BY) '
2054 || lv_cursor_stmt ;
2055
2056
2057 BEGIN
2058
2059 SAVEPOINT Load_cmro_res;
2060 EXECUTE IMMEDIATE lv_sql_stmt
2061 USING
2062 MSC_CL_COLLECTION.v_last_collection_id,
2063 MSC_CL_COLLECTION.v_current_date,
2064 MSC_CL_COLLECTION.v_current_user,
2065 MSC_CL_COLLECTION.v_current_date,
2066 MSC_CL_COLLECTION.v_current_user;
2067
2068 COMMIT;
2069 RETURN;
2070
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073
2074 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_CMRO_FORECAST_RR>>');
2075 IF lv_sql_stmt IS NOT NULL THEN
2076 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
2077 END IF;
2078 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2079
2080 ROLLBACK WORK TO SAVEPOINT Load_cmro_res;
2081 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
2082
2083 END;
2084
2085 END IF;--MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2086
2087 END LOAD_CMRO_FORECAST_RR;
2088
2089 PROCEDURE LOAD_SUPPLY_RESERVATIONS IS
2090 CURSOR c1 IS
2091 SELECT TRANSACTION_ID,
2092 DISPOSITION_TYPE,
2093 ORGANIZATION_ID,
2094 RESERVED_QUANTITY,
2095 SUPPLY_SOURCE_TYPE_ID,
2096 SUPPLY_SOURCE_HEADER_ID,
2097 DEMAND_SOURCE_LINE_ID,
2098 RESERVATION_TYPE,
2099 REQUIREMENT_DATE,
2100 SUBINVENTORY,
2101 TASK_ID,
2102 PROJECT_ID
2103 FROM MSC_ST_RESERVATIONS
2104 WHERE deleted_flag = MSC_UTIL.SYS_NO
2105 AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
2106
2107 CURSOR c_del IS
2108 SELECT TRANSACTION_ID,
2109 ORGANIZATION_ID,
2110 SR_INSTANCE_ID
2111 FROM MSC_ST_RESERVATIONS
2112 WHERE deleted_flag = MSC_UTIL.SYS_YES
2113 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2114 BEGIN
2115 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_SUPPLY_RESERVATIONS ');
2116 -- This code is being commented out as we are handling CMRO reservations in MSC_CL_DEMAND_ODS_LOAD.LOAD_HARD_RESERVATION
2117 /*
2118 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
2119 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2120 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
2121 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2122 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS',
2123 MSC_CL_COLLECTION.v_instance_id,
2124 -1);
2125 ELSE
2126 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
2127 ||' AND RESERVATION_TYPE in (15,16,17)';
2128 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS',
2129 MSC_CL_COLLECTION.v_instance_id,
2130 -1,
2131 v_sub_str);
2132 END IF;
2133 END IF;
2134
2135 FOR c_rec IN c_del LOOP
2136 BEGIN
2137
2138 DELETE MSC_RESERVATIONS
2139 WHERE TRANSACTION_ID = c_rec.TRANSACTION_ID
2140 AND SR_INSTANCE_ID = c_rec.sr_instance_id
2141 AND ORGANIZATION_ID = c_rec.organization_id
2142 AND RESERVATION_TYPE in (15,16,17);
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2150 END;
2147 'An error has occurred during deletion of MSC_RESERVATIONS.');
2148 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2149 RAISE;
2151 END LOOP;
2152
2153 COMMIT;
2154
2155 BEGIN
2156
2157 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
2158 'In Procedure LOAD_SUPPLY_RESERVATIONS ');
2159
2160 lv_cursor_stmt:=
2161 'INSERT INTO MSC_RESERVATIONS'
2162 ||'(TRANSACTION_ID,'
2163 ||'DISPOSITION_ID,'
2164 ||'DISPOSITION_TYPE,'
2165 ||'ORGANIZATION_ID,'
2166 ||'INVENTORY_ITEM_ID,'
2167 ||'RESERVATION_TYPE,'
2168 ||'REQUIREMENT_DATE,'
2169 ||'RESERVED_QUANTITY,'
2170 ||'RESERVATION_DATE,'
2171 ||'SUBINVENTORY,'
2172 ||'TASK_ID,'
2173 ||'PROJECT_ID,'
2174 ||'SUPPLY_SOURCE_TYPE_ID,'
2175 ||'SUPPLY_SOURCE_HEADER_ID,'
2176 ||'DEMAND_SOURCE_LINE_ID,'
2177 ||'NONNET_QUANTITY_RESERVED,'
2178 ||'PLAN_ID,'
2179 ||'REFRESH_NUMBER,'
2180 ||'SR_INSTANCE_ID,'
2181 ||'LAST_UPDATE_DATE,'
2182 ||'LAST_UPDATED_BY,'
2183 ||'CREATION_DATE,'
2184 ||'CREATED_BY) '
2185 ||'SELECT ms.TRANSACTION_ID,'
2186 ||'ms.DISPOSITION_ID,'
2187 ||'ms.DISPOSITION_TYPE,'
2188 ||'ms.ORGANIZATION_ID,'
2189 ||'mil.INVENTORY_ITEM_ID,'
2190 ||'ms.RESERVATION_TYPE,'
2191 ||'ms.REQUIREMENT_DATE,'
2192 ||'ms.RESERVED_QUANTITY,'
2193 ||'ms.REQUIREMENT_DATE,'
2194 ||'ms.SUBINVENTORY,'
2195 ||'ms.TASK_ID,'
2196 ||'ms.PROJECT_ID,'
2197 ||'ms.SUPPLY_SOURCE_TYPE_ID,'
2198 ||'ms.SUPPLY_SOURCE_HEADER_ID,'
2199 ||'ms.DEMAND_SOURCE_LINE_ID,'
2200 ||'0,'
2201 ||'-1,'
2202 ||':v_last_collection_id,'
2203 ||':v_instance_id,'
2204 ||':v_current_date,'
2205 ||':v_current_user,'
2206 ||':v_current_date,'
2207 ||':v_current_user'
2208 ||' FROM MSC_ST_RESERVATIONS ms,'
2209 ||' MSC_ITEM_ID_LID mil '
2210 ||' WHERE ms.SR_INSTANCE_ID= '
2211 ||MSC_CL_COLLECTION.v_instance_id
2212 ||' AND mil.sr_instance_id = ms.sr_instance_id'
2213 ||' AND mil.SR_INVENTORY_ITEM_ID = ms.INVENTORY_ITEM_ID';
2214
2215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
2216 'In Procedure LOAD_SUPPLY_RESERVATIONS lv_cursor_stmt: '|| lv_cursor_stmt);
2217 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
2218 MSC_CL_COLLECTION.v_is_partial_refresh) OR
2219 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
2220 EXECUTE IMMEDIATE lv_cursor_stmt using
2221 MSC_CL_COLLECTION.v_last_collection_id,
2222 MSC_CL_COLLECTION.v_instance_id,
2223 MSC_CL_COLLECTION.v_current_date,
2224 MSC_CL_COLLECTION.v_current_user,
2225 MSC_CL_COLLECTION.v_current_date,
2226 MSC_CL_COLLECTION.v_current_user;
2227 END IF;
2228
2229 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
2230 MSC_CL_COLLECTION.v_is_partial_refresh) OR
2231 (MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2232
2233 FOR c_rec IN c1 LOOP
2234 UPDATE MSC_RESERVATIONS
2235 SET
2236 DISPOSITION_TYPE = c_rec.DISPOSITION_TYPE,
2237 REQUIREMENT_DATE = c_rec.REQUIREMENT_DATE,
2238 SUBINVENTORY = c_rec.SUBINVENTORY,
2239 TASK_ID = c_rec.TASK_ID,
2240 PROJECT_ID = c_rec.PROJECT_ID,
2241 RESERVED_QUANTITY = c_rec.RESERVED_QUANTITY,
2242 SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
2243 SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
2244 DEMAND_SOURCE_LINE_ID = c_rec.DEMAND_SOURCE_LINE_ID,
2245 RESERVATION_TYPE = c_rec.RESERVATION_TYPE,
2246 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
2247 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2248 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2249 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2250 AND TRANSACTION_ID = c_rec.TRANSACTION_ID
2251 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
2252 AND RESERVATION_TYPE in (15,16,17);
2253 END LOOP;
2254 END IF;
2255
2256 EXCEPTION
2257 WHEN OTHERS THEN
2258 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2259 RAISE;
2260 END;
2261
2262 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2263 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2264 lv_retcode,
2265 'MSC_RESERVATIONS',
2266 MSC_CL_COLLECTION.v_INSTANCE_CODE,
2270 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2267 MSC_UTIL.G_ERROR);
2268
2269 IF lv_retcode = MSC_UTIL.G_ERROR THEN
2271 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2272 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2273 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2274 END IF;
2275 END IF;
2276 COMMIT;
2277 */
2278 END LOAD_SUPPLY_RESERVATIONS;
2279 PROCEDURE LOAD_CLOSED_CMRO_WOS IS
2280 CURSOR c1 IS
2281 SELECT t1.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
2282 t1.INVENTORY_ITEM_ID asset_item_id,
2283 ms.visit_id,
2284 ms.ORGANIZATION_ID,
2285 ms.DISPOSITION_ID,
2286 ms.ORDER_NUMBER,
2287 ms.NEW_ORDER_QUANTITY,
2288 ms.NEW_SCHEDULE_DATE,
2289 ms.EXPECTED_SCRAP_QTY,
2290 ms.QTY_SCRAPPED,
2291 ms.QTY_COMPLETED,
2292 ms.FIRM_PLANNED_TYPE,
2293 ms.NEW_WIP_START_DATE,
2294 ms.ORDER_TYPE,
2295 ms.PROJECT_ID,
2296 ms.TASK_ID,
2297 ms.SCHEDULE_GROUP_ID,
2298 ms.BUILD_SEQUENCE,
2299 ms.LINE_ID,
2300 ms.ALTERNATE_BOM_DESIGNATOR,
2301 ms.ALTERNATE_ROUTING_DESIGNATOR,
2302 ms.UNIT_NUMBER,
2303 ms.WIP_STATUS_CODE,
2304 ms.DEMAND_CLASS,
2305 ms.COPRODUCTS_SUPPLY,
2306 ms.REQUESTED_START_DATE,
2307 ms.REQUESTED_COMPLETION_DATE,
2308 ms.SCHEDULE_PRIORITY,
2309 ms.ACTUAL_START_DATE,
2310 ms.CFM_ROUTING_FLAG,
2311 ms.WIP_START_QUANTITY,
2312 ms.MAINTENANCE_OBJECT_SOURCE,
2313 ms.DESCRIPTION,
2314 ms.MAINTENANCE_OBJECT_TYPE,
2315 ms.REFRESH_ID,
2316 ms.SR_INSTANCE_ID
2317 FROM MSC_ST_SUPPLIES ms,
2318 MSC_ITEM_ID_LID t1
2319 WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID
2320 AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID
2321 AND ms.deleted_flag = MSC_UTIL.SYS_NO
2322 AND ms.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2323 AND ms.order_type=90;
2324
2325 CURSOR c_del IS
2326 SELECT DISPOSITION_ID,
2327 ORDER_TYPE,
2328 ORGANIZATION_ID,
2329 SR_INSTANCE_ID
2330 FROM MSC_ST_SUPPLIES
2331 WHERE deleted_flag = MSC_UTIL.SYS_YES
2332 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2333 AND order_type=90;
2334
2335 BEGIN
2336 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
2337 'In Procedure LOAD_CLOSED_CMRO_WOS ');
2338 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2339 lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2340 ELSE
2341 lv_tbl:= 'MSC_SUPPLIES';
2342 END IF;
2343
2344 BEGIN
2345 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
2346 lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2347 ELSE
2348 lv_tbl:= 'MSC_SUPPLIES';
2349 END IF;
2350
2351 lv_cursor_stmt:= ' SELECT '
2352 ||' -1, MSC_SUPPLIES_S.NEXTVAL,'
2353 ||' t1.INVENTORY_ITEM_ID,'
2354 ||' t1.INVENTORY_ITEM_ID,'
2355 ||' ms.visit_id,'
2356 ||' ms.ORGANIZATION_ID,'
2357 ||' ms.DISPOSITION_ID,'
2358 ||' ms.ORDER_NUMBER,'
2359 ||' ms.NEW_ORDER_QUANTITY,'
2360 ||' ms.NEW_SCHEDULE_DATE,'
2361 ||' ms.EXPECTED_SCRAP_QTY,'
2362 ||' ms.QTY_SCRAPPED,'
2363 ||' ms.QTY_COMPLETED,'
2364 ||' ms.FIRM_PLANNED_TYPE,'
2365 ||' ms.NEW_WIP_START_DATE,'
2366 ||' ms.ORDER_TYPE,'
2367 ||' ms.PROJECT_ID,'
2368 ||' ms.TASK_ID,'
2369 ||' ms.SCHEDULE_GROUP_ID,'
2370 ||' ms.BUILD_SEQUENCE,'
2371 ||' ms.LINE_ID,'
2372 ||' ms.ALTERNATE_BOM_DESIGNATOR,'
2373 ||' ms.ALTERNATE_ROUTING_DESIGNATOR,'
2374 ||' ms.UNIT_NUMBER,'
2375 ||' ms.WIP_STATUS_CODE,'
2376 ||' ms.DEMAND_CLASS,'
2377 ||' ms.COPRODUCTS_SUPPLY,'
2378 ||' ms.REQUESTED_START_DATE,'
2379 ||' ms.REQUESTED_COMPLETION_DATE,'
2380 ||' ms.SCHEDULE_PRIORITY,'
2381 ||' ms.ACTUAL_START_DATE,'
2382 ||' ms.CFM_ROUTING_FLAG,'
2383 ||' ms.WIP_START_QUANTITY,'
2384 ||' ms.MAINTENANCE_OBJECT_SOURCE,'
2385 ||' ms.DESCRIPTION,'
2386 ||' ms.MAINTENANCE_OBJECT_TYPE,'
2387 ||' :v_last_collection_id,'
2388 ||' :v_instance_id,'
2389 ||' :v_current_date, '
2390 ||' :v_current_user, '
2391 ||' :v_current_date, '
2392 ||' :v_current_user '
2393 ||' FROM MSC_ITEM_ID_LID t1, '
2394 ||' MSC_ST_SUPPLIES ms '
2395 ||'WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID '
2396 ||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID '
2397 ||' AND ms.deleted_flag = '||MSC_UTIL.SYS_NO
2398 ||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2399 ||' AND ms.order_type=90';
2400
2401 v_sql_stmt:=
2402 'INSERT INTO '||lv_tbl
2403 ||' ( PLAN_ID, TRANSACTION_ID,'
2404 ||' INVENTORY_ITEM_ID,'
2405 ||' asset_item_id,'
2406 ||' visit_id,'
2410 ||' NEW_ORDER_QUANTITY,'
2407 ||' ORGANIZATION_ID,'
2408 ||' DISPOSITION_ID,'
2409 ||' ORDER_NUMBER,'
2411 ||' NEW_SCHEDULE_DATE,'
2412 ||' EXPECTED_SCRAP_QTY,'
2413 ||' QTY_SCRAPPED,'
2414 ||' QTY_COMPLETED,'
2415 ||' FIRM_PLANNED_TYPE,'
2416 ||' NEW_WIP_START_DATE,'
2417 ||' ORDER_TYPE,'
2418 ||' PROJECT_ID,'
2419 ||' TASK_ID,'
2420 ||' SCHEDULE_GROUP_ID,'
2421 ||' BUILD_SEQUENCE,'
2422 ||' LINE_ID,'
2423 ||' ALTERNATE_BOM_DESIGNATOR,'
2424 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2425 ||' UNIT_NUMBER,'
2426 ||' WIP_STATUS_CODE,'
2427 ||' DEMAND_CLASS,'
2428 ||' COPRODUCTS_SUPPLY,'
2429 ||' REQUESTED_START_DATE,'
2430 ||' REQUESTED_COMPLETION_DATE,'
2431 ||' SCHEDULE_PRIORITY,'
2432 ||' ACTUAL_START_DATE,'
2433 ||' CFM_ROUTING_FLAG,'
2434 ||' WIP_START_QUANTITY,'
2435 ||' MAINTENANCE_OBJECT_SOURCE,'
2436 ||' DESCRIPTION,'
2437 ||' MAINTENANCE_OBJECT_TYPE,'
2438 ||' REFRESH_NUMBER,'
2439 ||' SR_INSTANCE_ID,'
2440 ||' LAST_UPDATE_DATE, '
2441 ||' LAST_UPDATED_BY, '
2442 ||' CREATION_DATE, '
2443 ||' CREATED_BY) '||lv_cursor_stmt;
2444
2445
2446 IF (MSC_CL_COLLECTION.v_is_complete_refresh or
2447 MSC_CL_COLLECTION.v_is_partial_refresh) OR
2448 (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
2449 EXECUTE IMMEDIATE v_sql_stmt using
2450 MSC_CL_COLLECTION.v_last_collection_id,
2451 MSC_CL_COLLECTION.v_instance_id,
2452 MSC_CL_COLLECTION.v_current_date,
2453 MSC_CL_COLLECTION.v_current_user,
2454 MSC_CL_COLLECTION.v_current_date,
2455 MSC_CL_COLLECTION.v_current_user;
2456 END IF;
2457
2458 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
2459 MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2460 FOR c_rec IN c_del LOOP
2461 BEGIN
2462 DELETE MSC_SUPPLIES
2463 WHERE PLAN_ID= -1
2464 AND SR_INSTANCE_ID= c_rec.sr_instance_id
2465 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
2466 AND ORDER_TYPE= c_rec.ORDER_TYPE
2467 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2471 'An error has occurred during deletion of ordertype 90 supplies.');
2472 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2473 RAISE;
2474 END;
2475 END LOOP;
2476 COMMIT;
2477
2478 FOR c_rec in c1
2479 LOOP
2480 UPDATE MSC_SUPPLIES
2481 SET
2482 NEW_ORDER_QUANTITY = c_rec.NEW_ORDER_QUANTITY,
2483 NEW_SCHEDULE_DATE = c_rec.NEW_SCHEDULE_DATE,
2484 EXPECTED_SCRAP_QTY = c_rec.EXPECTED_SCRAP_QTY,
2485 QTY_SCRAPPED = c_rec.QTY_SCRAPPED,
2486 QTY_COMPLETED = c_rec.QTY_COMPLETED,
2487 FIRM_PLANNED_TYPE = c_rec.FIRM_PLANNED_TYPE,
2488 NEW_WIP_START_DATE = c_rec.NEW_WIP_START_DATE,
2489 PROJECT_ID = c_rec.PROJECT_ID,
2490 TASK_ID = c_rec.TASK_ID,
2491 SCHEDULE_GROUP_ID = c_rec.SCHEDULE_GROUP_ID,
2492 BUILD_SEQUENCE = c_rec.BUILD_SEQUENCE,
2493 LINE_ID = c_rec.LINE_ID,
2494 ALTERNATE_BOM_DESIGNATOR = c_rec.ALTERNATE_BOM_DESIGNATOR,
2495 ALTERNATE_ROUTING_DESIGNATOR = c_rec.ALTERNATE_ROUTING_DESIGNATOR,
2496 UNIT_NUMBER = c_rec.UNIT_NUMBER,
2497 WIP_STATUS_CODE = c_rec.WIP_STATUS_CODE,
2498 DEMAND_CLASS = c_rec.DEMAND_CLASS,
2499 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
2500 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
2501 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
2502 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
2503 ACTUAL_START_DATE = c_rec.ACTUAL_START_DATE,
2504 CFM_ROUTING_FLAG = c_rec.CFM_ROUTING_FLAG,
2505 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
2506 DESCRIPTION = c_rec.DESCRIPTION
2507 WHERE PLAN_ID= -1
2508 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2509 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
2510 AND ORDER_TYPE= c_rec.ORDER_TYPE
2511 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
2512
2513 IF SQL%NOTFOUND THEN
2514 v_sql_stmt:='INSERT INTO '||lv_tbl
2515 ||' ( PLAN_ID,TRANSACTION_ID,'
2519 ||' ORGANIZATION_ID,'
2516 ||' INVENTORY_ITEM_ID,'
2517 ||' asset_item_id,'
2518 ||' visit_id,'
2520 ||' DISPOSITION_ID,'
2521 ||' ORDER_NUMBER,'
2522 ||' NEW_ORDER_QUANTITY,'
2523 ||' NEW_SCHEDULE_DATE,'
2524 ||' EXPECTED_SCRAP_QTY,'
2525 ||' QTY_SCRAPPED,'
2526 ||' QTY_COMPLETED,'
2527 ||' FIRM_PLANNED_TYPE,'
2528 ||' NEW_WIP_START_DATE,'
2529 ||' ORDER_TYPE,'
2530 ||' PROJECT_ID,'
2531 ||' TASK_ID,'
2532 ||' SCHEDULE_GROUP_ID,'
2533 ||' BUILD_SEQUENCE,'
2534 ||' LINE_ID,'
2535 ||' ALTERNATE_BOM_DESIGNATOR,'
2536 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2537 ||' UNIT_NUMBER,'
2538 ||' WIP_STATUS_CODE,'
2539 ||' DEMAND_CLASS,'
2540 ||' COPRODUCTS_SUPPLY,'
2541 ||' REQUESTED_START_DATE,'
2542 ||' REQUESTED_COMPLETION_DATE,'
2543 ||' SCHEDULE_PRIORITY,'
2544 ||' ACTUAL_START_DATE,'
2545 ||' CFM_ROUTING_FLAG,'
2546 ||' WIP_START_QUANTITY,'
2547 ||' MAINTENANCE_OBJECT_SOURCE,'
2548 ||' DESCRIPTION,'
2549 ||' MAINTENANCE_OBJECT_TYPE,'
2550 ||' REFRESH_NUMBER,'
2551 ||' SR_INSTANCE_ID,'
2552 ||' LAST_UPDATE_DATE, '
2553 ||' LAST_UPDATED_BY, '
2554 ||' CREATION_DATE, '
2555 ||' CREATED_BY) '
2556 ||' values ( -1,MSC_SUPPLIES_S.NEXTVAL,'
2557 ||' :v_INVENTORY_ITEM_ID,'
2558 ||' :v_asset_item_id,'
2559 ||' :v_visit_id,'
2560 ||' :v_ORGANIZATION_ID,'
2561 ||' :v_DISPOSITION_ID,'
2562 ||' :v_ORDER_NUMBER,'
2563 ||' :v_NEW_ORDER_QUANTITY,'
2564 ||' :v_NEW_SCHEDULE_DATE,'
2565 ||' :v_EXPECTED_SCRAP_QTY,'
2566 ||' :v_QTY_SCRAPPED,'
2567 ||' :v_QTY_COMPLETED,'
2568 ||' :v_FIRM_PLANNED_TYPE,'
2569 ||' :v_NEW_WIP_START_DATE,'
2570 ||' :v_ORDER_TYPE,'
2571 ||' :v_PROJECT_ID,'
2572 ||' :v_TASK_ID,'
2573 ||' :v_SCHEDULE_GROUP_ID,'
2574 ||' :v_BUILD_SEQUENCE,'
2575 ||' :v_LINE_ID,'
2576 ||' :v_ALTERNATE_BOM_DESIGNATOR,'
2577 ||' :v_ALTERNATE_ROUTING_DESIGNATOR,'
2578 ||' :v_UNIT_NUMBER,'
2579 ||' :v_WIP_STATUS_CODE,'
2580 ||' :v_DEMAND_CLASS,'
2581 ||' :v_COPRODUCTS_SUPPLY,'
2582 ||' :v_REQUESTED_START_DATE,'
2583 ||' :v_REQUESTED_COMPLETION_DATE,'
2584 ||' :v_SCHEDULE_PRIORITY,'
2585 ||' :v_ACTUAL_START_DATE,'
2586 ||' :v_CFM_ROUTING_FLAG,'
2587 ||' :v_WIP_START_QUANTITY,'
2588 ||' :v_MAINTENANCE_OBJECT_SOURCE,'
2589 ||' :v_DESCRIPTION,'
2590 ||' :v_MAINTENANCE_OBJECT_TYPE,'
2591 ||' :v_last_collection_id,'
2592 ||' :v_instance_id,'
2593 ||' :v_current_date, '
2594 ||' :v_current_user, '
2595 ||' :v_current_date, '
2596 ||' :v_current_user )';
2597
2598 EXECUTE IMMEDIATE v_sql_stmt using
2599 c_rec.INVENTORY_ITEM_ID,
2600 c_rec.asset_item_id,
2601 c_rec.visit_id,
2602 c_rec.ORGANIZATION_ID,
2603 c_rec.DISPOSITION_ID,
2604 c_rec.ORDER_NUMBER,
2605 c_rec.NEW_ORDER_QUANTITY,
2606 c_rec.NEW_SCHEDULE_DATE,
2607 c_rec.EXPECTED_SCRAP_QTY,
2608 c_rec.QTY_SCRAPPED,
2609 c_rec.QTY_COMPLETED,
2610 c_rec.FIRM_PLANNED_TYPE,
2611 c_rec.NEW_WIP_START_DATE,
2612 c_rec.ORDER_TYPE,
2613 c_rec.PROJECT_ID,
2614 c_rec.TASK_ID,
2615 c_rec.SCHEDULE_GROUP_ID,
2616 c_rec.BUILD_SEQUENCE,
2617 c_rec.LINE_ID,
2618 c_rec.ALTERNATE_BOM_DESIGNATOR,
2619 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
2620 c_rec.UNIT_NUMBER,
2621 c_rec.WIP_STATUS_CODE,
2622 c_rec.DEMAND_CLASS,
2623 c_rec.COPRODUCTS_SUPPLY,
2624 c_rec.REQUESTED_START_DATE,
2625 c_rec.REQUESTED_COMPLETION_DATE,
2626 c_rec.SCHEDULE_PRIORITY,
2627 c_rec.ACTUAL_START_DATE,
2628 c_rec.CFM_ROUTING_FLAG,
2629 c_rec.WIP_START_QUANTITY,
2630 c_rec.MAINTENANCE_OBJECT_SOURCE,
2631 c_rec.DESCRIPTION,
2632 c_rec.MAINTENANCE_OBJECT_TYPE,
2636 MSC_CL_COLLECTION.v_current_user,
2633 MSC_CL_COLLECTION.v_last_collection_id,
2634 MSC_CL_COLLECTION.v_instance_id,
2635 MSC_CL_COLLECTION.v_current_date,
2637 MSC_CL_COLLECTION.v_current_date,
2638 MSC_CL_COLLECTION.v_current_user;
2639 END IF;
2640 END LOOP;
2641 END IF;
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2646 RAISE;
2647 END;
2648
2649 commit;
2650 EXCEPTION
2651 WHEN OTHERS THEN
2652
2653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in ods load -LOAD_CLOSED_CMRO_WOS '||SQLERRM);
2654 RAISE;
2655 END LOAD_CLOSED_CMRO_WOS;
2656
2657
2658 PROCEDURE LOAD_OSP_SUPPLY IS
2659
2660
2661 lv_supplies_tbl VARCHAR2(30);
2662 lv_cursor_stmt VARCHAR2(32767);
2663 lv_sql_stmt VARCHAR2(32767);
2664
2665 BEGIN
2666 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
2667 'In Procedure LOAD_OSP_SUPPLY ');
2668
2669 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
2670 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2671 ELSE
2672 lv_supplies_tbl:= 'MSC_SUPPLIES';
2673 END IF;
2674
2675 lv_cursor_stmt:=
2676 'SELECT'
2677 ||' -1,'
2678 ||' MSC_SUPPLIES_S.NEXTVAL,'
2679 ||' ms.ORGANIZATION_ID,'
2680 --||' ms.NEW_WIP_START_DATE,'
2681 ||' ms.NEW_SCHEDULE_DATE,'
2682 ||' ms.NEW_ORDER_QUANTITY,'
2683 ||' ms.ORDER_NUMBER,'
2684 ||' t1.INVENTORY_ITEM_ID,'
2685 ||' ms.ORDER_TYPE,'
2686 ||' ms.COLL_ORDER_TYPE,'
2687 ||' ms.FIRM_PLANNED_TYPE,' --firm_planned_type
2688 ||' tp.TP_ID,'
2689 ||' tps.TP_SITE_ID,'
2690 ||' ms.PRODUCES_TO_STOCK,'
2691 ||' ms.ITEM_TYPE_ID,'
2692 ||' ms.ITEM_TYPE_VALUE,'
2693 ||' ms.SR_INSTANCE_ID,'
2694 ||' :v_last_collection_id,'
2695 ||' :v_current_date,'
2696 ||' :v_current_user,'
2697 ||' :v_current_date,'
2698 ||' :v_current_user '
2699 ||' FROM MSC_ST_SUPPLIES ms,'
2700 ||' MSC_ITEM_ID_LID t1,'
2701 ||' MSC_TP_ID_LID tp,'
2702 ||' MSC_TP_SITE_ID_LID tps'
2703 ||' WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID'
2704 ||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
2705 ||' AND ms.deleted_flag = ' || MSC_UTIL.SYS_NO
2706 ||' AND ms.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
2707 ||' AND ms.order_type=74'
2708 ||' AND ms.COLL_ORDER_TYPE = 974'
2709 ||' AND tp.SR_TP_ID(+)= ms.SUPPLIER_ID'
2710 ||' AND tp.SR_INSTANCE_ID(+)= ms.SR_INSTANCE_ID'
2711 ||' AND tps.SR_TP_SITE_ID(+)= ms.SUPPLIER_SITE_ID'
2712 ||' AND tps.SR_INSTANCE_ID(+)= ms.SR_INSTANCE_ID' ;
2713
2714 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2715
2716 lv_sql_stmt:=
2717 'INSERT INTO '||lv_supplies_tbl
2718 ||' ( PLAN_ID,'
2719 ||' TRANSACTION_ID,'
2720 ||' ORGANIZATION_ID,'
2721 --||' NEW_WIP_START_DATE,'
2722 ||' NEW_SCHEDULE_DATE,'
2723 ||' NEW_ORDER_QUANTITY,'
2724 ||' ORDER_NUMBER,'
2725 ||' INVENTORY_ITEM_ID,'
2726 ||' ORDER_TYPE,'
2727 ||' COLL_ORDER_TYPE,'
2728 ||' FIRM_PLANNED_TYPE, '
2729 ||' SUPPLIER_ID,'
2730 ||' SUPPLIER_SITE_ID,'
2731 ||' PRODUCES_TO_STOCK,'
2732 ||' ITEM_TYPE_ID,'
2733 ||' ITEM_TYPE_VALUE,'
2734 ||' SR_INSTANCE_ID, '
2735 ||' REFRESH_NUMBER, '
2736 ||' LAST_UPDATE_DATE, '
2737 ||' LAST_UPDATED_BY, '
2738 ||' CREATION_DATE, '
2739 ||' CREATED_BY) '
2740 || lv_cursor_stmt;
2741
2742 BEGIN
2743
2744 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
2745 'The Insert statement is: ' || lv_sql_stmt);
2746
2747
2748 EXECUTE IMMEDIATE lv_sql_stmt
2749 USING
2750 MSC_CL_COLLECTION.v_last_collection_id,
2751 MSC_CL_COLLECTION.v_current_date,
2752 MSC_CL_COLLECTION.v_current_user,
2753 MSC_CL_COLLECTION.v_current_date,
2754 MSC_CL_COLLECTION.v_current_user;
2755
2756 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
2757 'The number of rows inserted is: ' || SQL%ROWCOUNT);
2758 COMMIT;
2759 RETURN;
2760
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763
2764 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_OSP_SUPPLY>>');
2765 IF lv_sql_stmt IS NOT NULL THEN
2766 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
2767 END IF;
2768 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2769
2770 END;
2771
2772 END IF;
2773
2774 END LOAD_OSP_SUPPLY;
2775
2776 END MSC_CL_AHL_ODS_LOAD;