[Home] [Help]
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,
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:
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',
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,
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);
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;
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',
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;
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;
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;
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,
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
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,
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,
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,
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,
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;
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;
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:
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
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
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:
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
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;
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:
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
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,
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;
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;
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;
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;
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:
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,
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);
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);
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;
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:
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;
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',
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,
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);
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;
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;
297: 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;
297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
298: MSC_CL_COLLECTION.v_instance_id,
299: null,
300: v_sub_str);
301: END IF;
294: -1);
295: ELSE
296: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
298: MSC_CL_COLLECTION.v_instance_id,
299: null,
300: v_sub_str);
301: END IF;
302: 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';
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';
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;
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'
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,
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,
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,
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,
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;
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;
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:
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
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
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
448: ||'mswa.WIP_ENTITY_ID '
449: ||' FROM MSC_ST_WO_ATTRIBUTES mswa, '
450: || lv_supplies_tbl||' ms'
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'
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
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;
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:
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
576: c_rec.PRODUCT_CLASSIFICATION,
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,
580: MSC_CL_COLLECTION.v_last_collection_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,
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,
580: MSC_CL_COLLECTION.v_last_collection_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;
578: c_rec.MAINTENANCE_REQT,
579: c_rec.WIP_ENTITY_ID,
580: MSC_CL_COLLECTION.v_last_collection_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;
579: c_rec.WIP_ENTITY_ID,
580: MSC_CL_COLLECTION.v_last_collection_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;
580: MSC_CL_COLLECTION.v_last_collection_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;
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:
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,
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);
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);
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:
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;
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 ');
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',
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,
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);
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;
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',
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;
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;
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_'
652: ||MSC_CL_COLLECTION.v_instance_code;
653: ELSE
654: lv_tbl:= 'MSC_WORK_BREAKDOWN_STRUCT';
648:
649: BEGIN
650: IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
651: lv_tbl:= 'WORK_BREAKDOWN_STRUCT_'
652: ||MSC_CL_COLLECTION.v_instance_code;
653: ELSE
654: lv_tbl:= 'MSC_WORK_BREAKDOWN_STRUCT';
655: END IF;
656:
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
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,
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,
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,
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,
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;
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;
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
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
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);
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
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
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
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;
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:
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
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,
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;
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;
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;
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;
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:
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,
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);
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);
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;
777: END IF;
778: END IF;
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;
777: END IF;
778: END IF;
779: COMMIT;
780:
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 (
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',
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);
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
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',
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,
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;
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;
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';
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';
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;
877: || lv_supplies_tbl||' ms1,'
878: || lv_supplies_tbl||' ms2'
879: ||' WHERE mswth.deleted_flag = '|| MSC_UTIL.SYS_NO
880: ||' AND mswth.SR_INSTANCE_ID= '
881: || MSC_CL_COLLECTION.v_instance_id
882: ||' AND ms1.SR_INSTANCE_ID(+)=mswth.SR_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'
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,
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,
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,
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,
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;
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;
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
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
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);
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
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
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'
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
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
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;
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:
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,
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;
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;
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;
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;
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:
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,
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);
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);
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:
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;
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;
1097: BEGIN
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1099: 'In Procedure LOAD_WO_OPERATION_REL ');
1100:
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 =
1104: MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1105: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
1099: 'In Procedure LOAD_WO_OPERATION_REL ');
1100:
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 =
1104: MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1105: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
1106: MSC_CL_COLLECTION.v_instance_id,
1099: 'In Procedure LOAD_WO_OPERATION_REL ');
1100:
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 =
1104: MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1105: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
1106: MSC_CL_COLLECTION.v_instance_id,
1107: -1);
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 =
1104: MSC_UTIL.G_ALL_ORGANIZATIONS THEN
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;
1102: MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1103: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
1104: MSC_UTIL.G_ALL_ORGANIZATIONS THEN
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',
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;
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;
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';
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';
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;
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'
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,
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,
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,
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,
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;
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;
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
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
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);
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
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
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'
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
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
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
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
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,
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;
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;
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;
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;
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:
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,
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);
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);
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:
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;
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 ');
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',
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,
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);
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;
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',
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;
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;
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;
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:
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
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,
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,
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,
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,
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,
1456: MSC_CL_COLLECTION.v_current_user;
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,
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
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,
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
1458:
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,
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
1458:
1459: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
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,
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
1458:
1459: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
1460: MSC_CL_COLLECTION.v_is_legacy_refresh THEN
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
1458:
1459: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
1460: MSC_CL_COLLECTION.v_is_legacy_refresh THEN
1461: FOR c_rec IN c_del LOOP
1462: BEGIN
1463: DELETE MSC_WO_MILESTONES
1456: MSC_CL_COLLECTION.v_current_user;
1457: END IF;
1458:
1459: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR
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
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
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
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;
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:
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,
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;
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;
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;
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;
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:
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,
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);
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);
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:
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;
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;
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:
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_SUB_COMP ');
1589: -- at this time.
1590: -- Hence, we need to delete from this table for all types of
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 =
1590: -- Hence, we need to delete from this table for all types of
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
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',
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);
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;
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',
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;
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;
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';
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';
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;
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'
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,
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,
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,
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,
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;
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;
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:
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:
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
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,
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);
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);
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;
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:
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';
1722: END IF;
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';
1722: END IF;
1723:
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:
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,'
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;
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:
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,
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);
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;
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';
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';
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;
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'
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)'
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,'
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;
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:
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;
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;
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:
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';
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';
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;
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'
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,'
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;
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:
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;
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;
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:
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
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,
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: /*
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',
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,
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);
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
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)';
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;
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;
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,
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,
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,
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,
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,
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;
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;
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:
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
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
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
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
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
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
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
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);
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;
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,
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,
2267: MSC_UTIL.G_ERROR);
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,
2267: MSC_UTIL.G_ERROR);
2268:
2269: IF lv_retcode = MSC_UTIL.G_ERROR THEN
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
2270: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
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;
2269: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2270: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
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: */
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,
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,
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;
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:
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;
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:
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
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,
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,
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,
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,
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;
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;
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:
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
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
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
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
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:
2629: c_rec.WIP_START_QUANTITY,
2630: c_rec.MAINTENANCE_OBJECT_SOURCE,
2631: c_rec.DESCRIPTION,
2632: c_rec.MAINTENANCE_OBJECT_TYPE,
2633: MSC_CL_COLLECTION.v_last_collection_id,
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2630: c_rec.MAINTENANCE_OBJECT_SOURCE,
2631: c_rec.DESCRIPTION,
2632: c_rec.MAINTENANCE_OBJECT_TYPE,
2633: MSC_CL_COLLECTION.v_last_collection_id,
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2638: MSC_CL_COLLECTION.v_current_user;
2631: c_rec.DESCRIPTION,
2632: c_rec.MAINTENANCE_OBJECT_TYPE,
2633: MSC_CL_COLLECTION.v_last_collection_id,
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2638: MSC_CL_COLLECTION.v_current_user;
2639: END IF;
2632: c_rec.MAINTENANCE_OBJECT_TYPE,
2633: MSC_CL_COLLECTION.v_last_collection_id,
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2638: MSC_CL_COLLECTION.v_current_user;
2639: END IF;
2640: END LOOP;
2633: MSC_CL_COLLECTION.v_last_collection_id,
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2638: MSC_CL_COLLECTION.v_current_user;
2639: END IF;
2640: END LOOP;
2641: END IF;
2634: MSC_CL_COLLECTION.v_instance_id,
2635: MSC_CL_COLLECTION.v_current_date,
2636: MSC_CL_COLLECTION.v_current_user,
2637: MSC_CL_COLLECTION.v_current_date,
2638: MSC_CL_COLLECTION.v_current_user;
2639: END IF;
2640: END LOOP;
2641: END IF;
2642:
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;
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:
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'
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,'
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;
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:
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,
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);
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;