[Home] [Help]
36: lv_index_tablespace VARCHAR2(30);
37: lv_storage_clause VARCHAR2(200);
38: BEGIN
39:
40: lv_csd_schema := MSC_UTIL.GET_SCHEMA_NAME(512);
41: MSC_UTIL.GET_STORAGE_PARAMETERS('CSD_REPAIRS',
42: lv_csd_schema,
43: lv_tablespace,
44: lv_index_tablespace,
37: lv_storage_clause VARCHAR2(200);
38: BEGIN
39:
40: lv_csd_schema := MSC_UTIL.GET_SCHEMA_NAME(512);
41: MSC_UTIL.GET_STORAGE_PARAMETERS('CSD_REPAIRS',
42: lv_csd_schema,
43: lv_tablespace,
44: lv_index_tablespace,
45: lv_storage_clause);
46:
47: lv_sql_stmt:= 'CREATE INDEX CSD_REPAIRS_N11 ON CSD_REPAIRS (inventory_org_id ,'
48: || 'last_update_date ,REPAIR_MODE ) TABLESPACE '
49: ||lv_index_tablespace;
50: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
51: BEGIN
52: Csd_Repairs_Util.create_csd_index(lv_sql_stmt,'CSD_REPAIRS_N11');
53: lv_status :=1 ;
54: EXCEPTION
60: /*Index already exists*/
61: lv_status :=1 ;
62: ELSE
63: lv_status := 2 ;
64: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
65: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
66: END IF;
67: END ;
68: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
64: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
65: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
66: END IF;
67: END ;
68: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
69: MSC_UTIL.GET_STORAGE_PARAMETERS('CSP_REPAIR_PO_HEADERS',
70: lv_csp_schema,
71: lv_tablespace,
72: lv_index_tablespace,
65: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
66: END IF;
67: END ;
68: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
69: MSC_UTIL.GET_STORAGE_PARAMETERS('CSP_REPAIR_PO_HEADERS',
70: lv_csp_schema,
71: lv_tablespace,
72: lv_index_tablespace,
73: lv_storage_clause);
75: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N1 ON CSP_REPAIR_PO_HEADERS'
76: || '(WIP_id, REPAIR_PO_HEADER_ID, '
77: || 'INVENTORY_ITEM_ID) TABLESPACE '
78: ||lv_index_tablespace;
79: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
80: BEGIN
81: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N1');
82: lv_status :=1 ;
83: EXCEPTION
89: /*Index already exists*/
90: lv_status :=1 ;
91: ELSE
92: lv_status := 2 ;
93: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
94: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
95: END IF;
96: END ;
97:
97:
98: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N2 ON CSP_REPAIR_PO_HEADERS'
99: || '( purchase_order_header_id) TABLESPACE '
100: ||lv_index_tablespace;
101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
102: BEGIN
103: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N2');
104: lv_status :=1 ;
105: EXCEPTION
111: /*Index already exists*/
112: lv_status :=1 ;
113: ELSE
114: lv_status := 2 ;
115: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
116: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
117: END IF;
118: END ;
119:
119:
120: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N3 ON CSP_REPAIR_PO_HEADERS'
121: || '( requisition_line_id ) TABLESPACE '
122: ||lv_index_tablespace;
123: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
124: BEGIN
125: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N3');
126: lv_status :=1 ;
127: EXCEPTION
133: /*Index already exists*/
134: lv_status :=1 ;
135: ELSE
136: lv_status := 2 ;
137: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
138: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
139: END IF;
140: END ;
141: lv_status :=1 ;
177: NULL,
178: FALSE); -- sub request
179:
180: commit;
181: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '
182: ||lv_request_id_drop||' :Checks for Snapshots which have changed and Drops them');
183: wait_for_request(lv_request_id_drop, 10, lv_out);
184:
185: if lv_success THEN
188:
189: if lv_success THEN
190: lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink||'(''MSC_SRP_ENABLED''),''N'')'
191: || ' from dual ';
192: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
193: execute immediate lv_sql_stmt into lv_srp_enabled_flag ;
194: IF lv_srp_enabled_flag='Y' THEN
195: CREATE_INDEX(lv_out);
196: if lv_out = 2 THEN
209: NULL,
210: NULL,
211: FALSE); -- sub request
212: commit;
213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsm||' :Creates WSM Snapshot Logs and Snapshots');
214:
215: lv_request_id_bom := FND_REQUEST.SUBMIT_REQUEST(
216: 'MSC',
217: 'MSCBOMSN',
218: NULL,
219: NULL,
220: FALSE); -- sub request
221: commit;
222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_bom||' :Creates BOM Snapshot Logs and Snapshots');
223:
224: lv_request_id_inv := FND_REQUEST.SUBMIT_REQUEST(
225: 'MSC',
226: 'MSCINVSN',
228: NULL,
229: FALSE); -- sub request
230: commit;
231:
232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_bom||' :Creates CSP Snapshot Logs and Snapshots');
233: lv_request_id_inv := FND_REQUEST.SUBMIT_REQUEST(
234: 'MSC',
235: 'MSCCSPSN',
236: NULL,
236: NULL,
237: NULL,
238: FALSE); -- sub request
239: commit;
240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_inv||' :Creates INV Snapshot Logs and Snapshots');
241:
242: lv_request_id_mrp := FND_REQUEST.SUBMIT_REQUEST(
243: 'MSC',
244: 'MSCMRPSN',
245: NULL,
246: NULL,
247: FALSE); -- sub request
248: commit;
249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_mrp||' :Creates MRP Snapshot Logs and Snapshots');
250:
251: lv_request_id_pox := FND_REQUEST.SUBMIT_REQUEST(
252: 'MSC',
253: 'MSCPOXSN',
254: NULL,
255: NULL,
256: FALSE); -- sub request
257: commit;
258: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_pox||' :Creates PO Snapshot Logs and Snapshots');
259:
260: lv_request_id_ont := FND_REQUEST.SUBMIT_REQUEST(
261: 'MSC',
262: 'MSCONTSN',
263: NULL,
264: NULL,
265: FALSE); -- sub request
266: commit;
267: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ont||' :Creates OE Snapshot Logs and Snapshots');
268:
269: lv_request_id_wsh := FND_REQUEST.SUBMIT_REQUEST(
270: 'MSC',
271: 'MSCWSHSN',
272: NULL,
273: NULL,
274: FALSE); -- sub request
275: commit;
276: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsh||' :Creates WSH Snapshot Logs and Snapshots');
277:
278: IF MRP_CL_FUNCTION.CHECK_AHL_VER = 1 THEN
279:
280: lv_request_id_ahl := FND_REQUEST.SUBMIT_REQUEST(
283: NULL,
284: NULL,
285: FALSE); -- sub request
286: commit;
287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ahl||' :Creates AHL Snapshot Logs and Snapshots');
288:
289: /* ds change: change start */
290: lv_request_id_eam := FND_REQUEST.SUBMIT_REQUEST(
291: 'MSC',
293: NULL,
294: NULL,
295: FALSE); -- sub request
296: commit;
297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_eam||' :Creates EAM Snapshot Logs and Snapshots');
298:
299: /* ds change: change end */
300:
301:
318: NULL,
319: NULL,
320: FALSE); -- sub request
321: commit;
322: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wip||' :Creates WIP Snapshot Logs and Snapshots');
323:
324: if lv_out = 2 THEN lv_success := FALSE ; end if;
325: end if;
326:
373: NULL,
374: NULL,
375: FALSE); -- sub request
376: commit;
377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_syn||' :Creates Synonyms used by Collections Process');
378:
379: wait_for_request(lv_request_id_syn, 10, lv_out);
380: if lv_success THEN
381: if lv_out = 2 THEN lv_success := FALSE ; end if;
388: NULL,
389: NULL,
390: FALSE); -- sub request
391: commit;
392: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_view||' :Creates Views used by Collections Process');
393:
394: lv_request_id_trig := FND_REQUEST.SUBMIT_REQUEST(
395: 'MSC',
396: 'MSCTRIGS',
397: NULL,
398: NULL,
399: FALSE); -- sub request
400: commit;
401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trig||' :Creates Triggers used by Collections Process');
402: wait_for_request(lv_request_id_view, 10, lv_out);
403: if lv_success THEN
404: if lv_out = 2 THEN lv_success := FALSE ; end if;
405: end if;
421:
422: IF lv_success AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN
423:
424: begin
425: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to No ');
426:
427: UPDATE FND_PROFILE_OPTION_VALUES
428: SET PROFILE_OPTION_VALUE = 'N'
429: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
428: SET PROFILE_OPTION_VALUE = 'N'
429: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
430: FROM FND_PROFILE_OPTIONS
431: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
432: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
433: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value No indicates that the Collection Setup Objects have been applied');
434: COMMIT;
435: return TRUE;
436:
429: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
430: FROM FND_PROFILE_OPTIONS
431: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
432: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
433: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value No indicates that the Collection Setup Objects have been applied');
434: COMMIT;
435: return TRUE;
436:
437: EXCEPTION
436:
437: EXCEPTION
438:
439: WHEN OTHERS THEN
440: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
441: end;
442: ELSE
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
439: WHEN OTHERS THEN
440: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
441: end;
442: ELSE
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
440: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
441: end;
442: ELSE
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
441: end;
442: ELSE
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
442: ELSE
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
459:
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
459:
460: return false;
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
459:
460: return false;
461:
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
459:
460: return false;
461:
462: END IF;
465: EXCEPTION
466:
467: WHEN OTHERS THEN
468:
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
470: return FALSE;
471: END SETUP_SOURCE_OBJECTS;
472: /* NEW Patching Strategy */
473: --3967634 added function CHECK_INSTALL
603: ERRBUF := 'NO_USER_DEFINED';
604: RETURN;
605: END;
606:
607: IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
608: MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
609: l_user_id,
610: -1, --l_resp_id,
611: -1 --l_application_id
604: RETURN;
605: END;
606:
607: IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
608: MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
609: l_user_id,
610: -1, --l_resp_id,
611: -1 --l_application_id
612: );
610: -1, --l_resp_id,
611: -1 --l_application_id
612: );
613: ELSE
614: RETCODE:= MSC_UTIL.G_ERROR;
615: ERRBUF := lv_log_msg;
616: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
617: RETURN;
618: END IF;
612: );
613: ELSE
614: RETCODE:= MSC_UTIL.G_ERROR;
615: ERRBUF := lv_log_msg;
616: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
617: RETURN;
618: END IF;
619:
620: END IF;
727: AND owner = lv_owner;
728:
729: BEGIN
730:
731: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
732:
733: FOR c1 in c_query_tables(lv_mrp_schema)
734: LOOP
735: BEGIN
738: EXECUTE IMMEDIATE lv_sql_stmt;
739:
740: EXCEPTION
741: WHEN OTHERS THEN
742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
743: END;
744: END LOOP;
745:
746: EXCEPTION
744: END LOOP;
745:
746: EXCEPTION
747: WHEN OTHERS THEN
748: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
749:
750: END PURGE_OBSOLETE_DATA;
751:
752: /*NEW PATCHING STRATEGY */
909: AND ROWNUM = 1;
910:
911: IF pNUM_OF_ROWS > 0 THEN
912:
913: lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
914: || ' where rownum < 2 ';
915: EXECUTE IMMEDIATE lv_sel_snp_stmt INTO lv_num_snp_rows;
916: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' more than .. '||lv_num_snp_rows);
917:
912:
913: lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
914: || ' where rownum < 2 ';
915: EXECUTE IMMEDIATE lv_sel_snp_stmt INTO lv_num_snp_rows;
916: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' more than .. '||lv_num_snp_rows);
917:
918: IF lv_num_snp_rows > 0 THEN
919: lv_sel_sql_stmt := ' select count(*) from '||lv_base_schema|| '.'||lv_mlog_tab_name
920: || ' where rownum <= :p1 ';
921: EXECUTE IMMEDIATE lv_sel_sql_stmt
922: INTO lv_num_of_log_rows
923: USING pNUM_OF_ROWS;
924:
925: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
926: ||' more than .. '||lv_num_of_log_rows);
927: END IF;
928: END IF;
929:
928: END IF;
929:
930: exception
931: when others then
932: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
933: raise;
934: end;
935:
936: IF (pNUM_OF_ROWS <= lv_num_of_log_rows) OR (lv_num_snp_rows = 0) THEN
937:
938: begin
939: lv_trnc_sql_stmt := 'TRUNCATE TABLE '||lv_base_schema|| '.' ||lv_mlog_tab_name;
940: EXECUTE IMMEDIATE lv_trnc_sql_stmt;
941: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
943: exception
944: when others then
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
938: begin
939: lv_trnc_sql_stmt := 'TRUNCATE TABLE '||lv_base_schema|| '.' ||lv_mlog_tab_name;
940: EXECUTE IMMEDIATE lv_trnc_sql_stmt;
941: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
943: exception
944: when others then
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
941: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
943: exception
944: when others then
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
948: RETURN TRUE; --so that the MView can do a complete refresh.
949: end;
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
943: exception
944: when others then
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
948: RETURN TRUE; --so that the MView can do a complete refresh.
949: end;
950:
943: exception
944: when others then
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
948: RETURN TRUE; --so that the MView can do a complete refresh.
949: end;
950:
951: -- refresh the snapshot
954:
955: ELSE
956: -- this condition will return FALSE , indicating no need to truncate the log and refresh the snapshot
957: lv_status := FALSE;
958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
959: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
960: ||' = '||lv_num_of_log_rows);
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962: ||') is less than the thresold entered for truncating Logs: '
955: ELSE
956: -- this condition will return FALSE , indicating no need to truncate the log and refresh the snapshot
957: lv_status := FALSE;
958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
959: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
960: ||' = '||lv_num_of_log_rows);
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962: ||') is less than the thresold entered for truncating Logs: '
963: || pNUM_OF_ROWS);
957: lv_status := FALSE;
958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
959: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
960: ||' = '||lv_num_of_log_rows);
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962: ||') is less than the thresold entered for truncating Logs: '
963: || pNUM_OF_ROWS);
964: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
960: ||' = '||lv_num_of_log_rows);
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962: ||') is less than the thresold entered for truncating Logs: '
963: || pNUM_OF_ROWS);
964: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
966:
967: END IF; -- If the truncate condition matches
968:
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962: ||') is less than the thresold entered for truncating Logs: '
963: || pNUM_OF_ROWS);
964: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
966:
967: END IF; -- If the truncate condition matches
968:
969: RETURN lv_status;
969: RETURN lv_status;
970:
971: EXCEPTION
972: WHEN OTHERS THEN
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
974: RETURN FALSE;
975: END TRUNC_SNAP_LOG;
976:
977: /*
1009: THEN
1010: RETURN TRUE;
1011: END IF;
1012: */
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'start - snapshot_data_correct');
1014: -- Add the where clause which are used in snapshot definition
1015: IF (p_snapshot = 'MTL_SUPPLY_SN') THEN
1016: lv_where_clause := ' ITEM_ID IS NOT NULL ';
1017: ELSIF (p_snapshot = 'MTL_SYS_ITEMS_SN') THEN
1066: END IF;
1067:
1068: lv_base_schema := p_base_schema;
1069: lv_master_tbl := p_table;
1070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_master_tbl - ' || p_base_schema || '.' || lv_master_tbl);
1071:
1072: EXECUTE IMMEDIATE
1073: ' SELECT LOG_TABLE FROM ALL_SNAPSHOT_LOGS '
1074: || ' WHERE MASTER = :lv_master_tbl AND '
1075: || ' LOG_OWNER = :p_schema AND '
1076: || ' ROWNUM = 1'
1077: INTO lv_mlog_tab_name
1078: USING upper(lv_master_tbl), upper(lv_base_schema);
1079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_mlog_tab_name - ' || lv_mlog_tab_name);
1080:
1081: EXECUTE IMMEDIATE
1082: ' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
1083: INTO lv_tab_count;
1080:
1081: EXECUTE IMMEDIATE
1082: ' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
1083: INTO lv_tab_count;
1084: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_tab_count - ' || lv_tab_count);
1085:
1086: EXECUTE IMMEDIATE
1087: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1088: INTO lv_snap_count;
1083: INTO lv_tab_count;
1084: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_tab_count - ' || lv_tab_count);
1085:
1086: EXECUTE IMMEDIATE
1087: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1088: INTO lv_snap_count;
1089: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snap_count - ' || lv_snap_count);
1090:
1091: EXECUTE IMMEDIATE
1085:
1086: EXECUTE IMMEDIATE
1087: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1088: INTO lv_snap_count;
1089: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snap_count - ' || lv_snap_count);
1090:
1091: EXECUTE IMMEDIATE
1092: ' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
1093: INTO lv_log_count;
1090:
1091: EXECUTE IMMEDIATE
1092: ' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
1093: INTO lv_log_count;
1094: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_log_count - ' || lv_log_count);
1095:
1096: IF ((lv_tab_count <> 0) AND (lv_snap_count = 0))
1097: OR
1098: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1095:
1096: IF ((lv_tab_count <> 0) AND (lv_snap_count = 0))
1097: OR
1098: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1099: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning false');
1100: RETURN FALSE; -- Error out since the snapshot is not having any rows
1101: ELSE
1102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning true');
1103: RETURN TRUE; -- snapshot data is correct - so continue the refresh
1098: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1099: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning false');
1100: RETURN FALSE; -- Error out since the snapshot is not having any rows
1101: ELSE
1102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning true');
1103: RETURN TRUE; -- snapshot data is correct - so continue the refresh
1104: END IF;
1105:
1106: EXCEPTION
1104: END IF;
1105:
1106: EXCEPTION
1107: WHEN OTHERS THEN
1108: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1109: RETURN FALSE; -- Error out
1110: END SNAPSHOT_DATA_CORRECT;
1111:
1112: /*------------------------------------------------------------
1138: lv_message := lv_message || RPAD (pStatus, 10, ' ');
1139: lv_message := lv_message || RPAD (to_char(pElapsed_Time,'99990.9'),
1140: 10, ' ');
1141:
1142: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_message);
1143:
1144: END Log_Snap_Ref_status;
1145:
1146: /*------------------------------------------------------------
1185: BEGIN
1186:
1187: -- First Copy the input snapshot strings into local variables.
1188:
1189: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1191:
1192: lv_total_snapshots := length(lv_refresh_param);
1193:
1186:
1187: -- First Copy the input snapshot strings into local variables.
1188:
1189: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1191:
1192: lv_total_snapshots := length(lv_refresh_param);
1193:
1194: FOR i IN 1..lv_total_snapshots LOOP
1241: lv_refresh_mode,
1242: 'ORA'||to_char(SQLCODE),
1243: lv_elapsed_mins);
1244:
1245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a Complete Refresh of the snapshot.');
1246: BEGIN
1247:
1248: lv_task_start_time := SYSDATE;
1249: if (v_database_version >= 10) then
1274: 'C',
1275: 'ORA'||to_char(SQLCODE),
1276: lv_elapsed_mins);
1277:
1278: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Complete Refresh of: '||
1279: lv_snapshot_name);
1280: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1281: ROLLBACK;
1282: RETCODE := G_ERROR;
1276: lv_elapsed_mins);
1277:
1278: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Complete Refresh of: '||
1279: lv_snapshot_name);
1280: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1281: ROLLBACK;
1282: RETCODE := G_ERROR;
1283: ERRBUF := SQLERRM;
1284: RETURN;
1286: ELSE
1287: ROLLBACK;
1288: RETCODE := G_ERROR;
1289: ERRBUF := SQLERRM;
1290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshot : '||
1291: lv_snapshot_name || ' , Mode : ' ||
1292: lv_refresh_mode);
1293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1294: RETURN;
1289: ERRBUF := SQLERRM;
1290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshot : '||
1291: lv_snapshot_name || ' , Mode : ' ||
1292: lv_refresh_mode);
1293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1294: RETURN;
1295: END IF;
1296: END;
1297: END LOOP;
1317:
1318: ROLLBACK;
1319: RETCODE := G_ERROR;
1320: ERRBUF := SQLERRM;
1321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : '||
1322: pSnapshot_List || ' , Mode : ' ||
1323: pRefresh_Param);
1324: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1325: RETURN;
1320: ERRBUF := SQLERRM;
1321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : '||
1322: pSnapshot_List || ' , Mode : ' ||
1323: pRefresh_Param);
1324: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1325: RETURN;
1326: END;
1327:
1328: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1324: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1325: RETURN;
1326: END;
1327:
1328: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
1330:
1331: RETCODE := G_SUCCESS;
1332: RETURN;
1325: RETURN;
1326: END;
1327:
1328: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
1330:
1331: RETCODE := G_SUCCESS;
1332: RETURN;
1333:
1355: l_request_id number;
1356: l_canc_req_retval number;
1357:
1358: BEGIN
1359: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling pending/running snapshots');
1360: FOR j IN 1..p_req_id.COUNT LOOP
1361: l_request_id:=p_req_id(j);
1362:
1363: l_call_status := FND_CONCURRENT.GET_REQUEST_STATUS
1370: l_dev_status,
1371: l_message);
1372:
1373: IF l_dev_phase <> 'COMPLETE' THEN
1374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling request - ' || l_request_id);
1375: l_canc_req_retval := FND_AMP_PRIVATE.cancel_request (l_request_id, l_message);
1376: COMMIT;
1377:
1378: IF l_canc_req_retval = 0 THEN
1375: l_canc_req_retval := FND_AMP_PRIVATE.cancel_request (l_request_id, l_message);
1376: COMMIT;
1377:
1378: IF l_canc_req_retval = 0 THEN
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling request, ' || l_request_id);
1380: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error - ' || l_message);
1381: END IF;
1382:
1383: END IF;
1376: COMMIT;
1377:
1378: IF l_canc_req_retval = 0 THEN
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling request, ' || l_request_id);
1380: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error - ' || l_message);
1381: END IF;
1382:
1383: END IF;
1384:
1382:
1383: END IF;
1384:
1385: END LOOP;
1386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelled pending/running snapshots');
1387:
1388: EXCEPTION
1389: WHEN OTHERS THEN
1390: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling requests - ' || SQLERRM);
1386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelled pending/running snapshots');
1387:
1388: EXCEPTION
1389: WHEN OTHERS THEN
1390: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling requests - ' || SQLERRM);
1391: END cancel_submitted_requests;
1392:
1393:
1394:
1479:
1480:
1481: lv_snapshot_grp_str := '';
1482:
1483: IF (lv_po_flag = MSC_UTIL.SYS_YES) THEN /* Added lv_reserves_flag for Bug 6144734 */
1484: lv_snapshot_grp_str := lv_snapshot_grp_str || '''PO'',' ;
1485: END IF; -- lv_po_flag
1486:
1487: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1483: IF (lv_po_flag = MSC_UTIL.SYS_YES) THEN /* Added lv_reserves_flag for Bug 6144734 */
1484: lv_snapshot_grp_str := lv_snapshot_grp_str || '''PO'',' ;
1485: END IF; -- lv_po_flag
1486:
1487: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1488: lv_snapshot_grp_str := lv_snapshot_grp_str || '''USUD'',' ;
1489: END IF; -- lv_po_flag
1490:
1491: IF (lv_item_flag = MSC_UTIL.SYS_YES) THEN
1487: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1488: lv_snapshot_grp_str := lv_snapshot_grp_str || '''USUD'',' ;
1489: END IF; -- lv_po_flag
1490:
1491: IF (lv_item_flag = MSC_UTIL.SYS_YES) THEN
1492: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ITEM'',' ;
1493: END IF; -- lv_item_flag
1494:
1495: -- SRP Changes
1492: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ITEM'',' ;
1493: END IF; -- lv_item_flag
1494:
1495: -- SRP Changes
1496: IF (lv_item_subs_falg = MSC_UTIL.SYS_YES) THEN
1497: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ISUB'',' ;
1498: END IF;
1499:
1500:
1497: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ISUB'',' ;
1498: END IF;
1499:
1500:
1501: IF (lv_oh_flag = MSC_UTIL.SYS_YES) THEN
1502: lv_snapshot_grp_str := lv_snapshot_grp_str || '''OH'',' ;
1503: END IF; -- lv_oh_flag
1504:
1505: /* IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1501: IF (lv_oh_flag = MSC_UTIL.SYS_YES) THEN
1502: lv_snapshot_grp_str := lv_snapshot_grp_str || '''OH'',' ;
1503: END IF; -- lv_oh_flag
1504:
1505: /* IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1506: lv_snapshot_grp_str := lv_snapshot_grp_str || '''RES'',' ;
1507: END IF; -- lv_reserves_flag
1508: */
1509: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1505: /* IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1506: lv_snapshot_grp_str := lv_snapshot_grp_str || '''RES'',' ;
1507: END IF; -- lv_reserves_flag
1508: */
1509: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1510: lv_snapshot_grp_str := lv_snapshot_grp_str || '''BOM'',' ;
1511: END IF; -- lv_bom_flag
1512:
1513: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1509: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1510: lv_snapshot_grp_str := lv_snapshot_grp_str || '''BOM'',' ;
1511: END IF; -- lv_bom_flag
1512:
1513: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1514: lv_snapshot_grp_str := lv_snapshot_grp_str || '''MRP'',' ;
1515: END IF; -- lv_mps_flag or lv_mds_flag
1516:
1517: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1513: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1514: lv_snapshot_grp_str := lv_snapshot_grp_str || '''MRP'',' ;
1515: END IF; -- lv_mps_flag or lv_mds_flag
1516:
1517: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1518: lv_snapshot_grp_str := lv_snapshot_grp_str || '''FCST'',' ;
1519: END IF; -- lv_forecast_flag
1520:
1521: IF (lv_wip_flag = MSC_UTIL.SYS_YES OR lv_iro_flag = MSC_UTIL.SYS_YES OR lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN -- Changed For Bug 5909379 SRP Internal Repairs
1517: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1518: lv_snapshot_grp_str := lv_snapshot_grp_str || '''FCST'',' ;
1519: END IF; -- lv_forecast_flag
1520:
1521: IF (lv_wip_flag = MSC_UTIL.SYS_YES OR lv_iro_flag = MSC_UTIL.SYS_YES OR lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN -- Changed For Bug 5909379 SRP Internal Repairs
1522: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WIP'',''EAM'',' ;
1523:
1524: IF (lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN /* For Bug 5937835 */
1525: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ERO'',' ;
1520:
1521: IF (lv_wip_flag = MSC_UTIL.SYS_YES OR lv_iro_flag = MSC_UTIL.SYS_YES OR lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN -- Changed For Bug 5909379 SRP Internal Repairs
1522: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WIP'',''EAM'',' ;
1523:
1524: IF (lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN /* For Bug 5937835 */
1525: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ERO'',' ;
1526: END IF ;
1527:
1528: END IF; -- lv_wip_flag
1526: END IF ;
1527:
1528: END IF; -- lv_wip_flag
1529:
1530: IF (lv_supplier_cap_flag = MSC_UTIL.SYS_YES or lv_supplier_cap_flag = ASL_YES_RETAIN_CP) THEN
1531: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SCAP'',' ;
1532: END IF; -- lv_supplier_cap_flag
1533:
1534: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1530: IF (lv_supplier_cap_flag = MSC_UTIL.SYS_YES or lv_supplier_cap_flag = ASL_YES_RETAIN_CP) THEN
1531: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SCAP'',' ;
1532: END IF; -- lv_supplier_cap_flag
1533:
1534: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1535: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SRSP'',' ;
1536: END IF; -- lv_supplier_resp_flag
1537:
1538: IF ((lv_sales_order_flag = MSC_UTIL.SYS_YES) OR (g_REFRESH_TYPE = 'I') OR (lv_reserves_flag = MSC_UTIL.SYS_YES)) THEN /* added lv_sales_order_flag for bug 6144734 */
1534: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1535: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SRSP'',' ;
1536: END IF; -- lv_supplier_resp_flag
1537:
1538: IF ((lv_sales_order_flag = MSC_UTIL.SYS_YES) OR (g_REFRESH_TYPE = 'I') OR (lv_reserves_flag = MSC_UTIL.SYS_YES)) THEN /* added lv_sales_order_flag for bug 6144734 */
1539: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ONT'',' ;
1540: END IF; -- lv_reserves_flag or lv_sales_order_flag -- or Incremental
1541:
1542: IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1538: IF ((lv_sales_order_flag = MSC_UTIL.SYS_YES) OR (g_REFRESH_TYPE = 'I') OR (lv_reserves_flag = MSC_UTIL.SYS_YES)) THEN /* added lv_sales_order_flag for bug 6144734 */
1539: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ONT'',' ;
1540: END IF; -- lv_reserves_flag or lv_sales_order_flag -- or Incremental
1541:
1542: IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1543: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WSH'',' ;
1544: END IF; -- lv_trip_flag
1545:
1546: IF lv_snapshot_grp_str = '' or lv_snapshot_grp_str is NULL THEN
1546: IF lv_snapshot_grp_str = '' or lv_snapshot_grp_str is NULL THEN
1547: NULL;
1548: ELSE
1549: lv_snapshot_grp_str := '(' || substr(lv_snapshot_grp_str,1,length(lv_snapshot_grp_str) -1 ) || ' ) ' ;
1550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_grp_str);
1551: END IF;
1552: ELSE
1553:
1554: lv_snapshot_grp_str := '(''' ||pSnapshot_str || ''')';
1565: begin
1566: if exp = '1' then
1567: val := 1;
1568: else
1569: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Evaluating -- ' || exp);
1570: begin
1571: execute immediate ' select ' || exp || ' from dual ' into val;
1572: exception when others then
1573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1569: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Evaluating -- ' || exp);
1570: begin
1571: execute immediate ' select ' || exp || ' from dual ' into val;
1572: exception when others then
1573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1575: val :=0;
1576: end;
1577: end if;
1570: begin
1571: execute immediate ' select ' || exp || ' from dual ' into val;
1572: exception when others then
1573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1575: val :=0;
1576: end;
1577: end if;
1578: if val =1 then
1625: lv_cusros_str varchar2(500);
1626:
1627: BEGIN
1628:
1629: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Start: split_refresh');
1630:
1631: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
1632:
1633: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1630:
1631: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
1632:
1633: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
1635: ELSE
1636:
1637: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1638:
1633: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
1635: ELSE
1636:
1637: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1638:
1639: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1641:
1636:
1637: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1638:
1639: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1641:
1642: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1644:
1638:
1639: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1641:
1642: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1644:
1645: lv_cusros_str := ' select mview_name, existance_check
1646: from msc_coll_snapshots_v
1639: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1641:
1642: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1644:
1645: lv_cusros_str := ' select mview_name, existance_check
1646: from msc_coll_snapshots_v
1647: where mview_name in ' || lv_snapshot_groups_str || '
1674:
1675: COMMIT;
1676:
1677: IF v_request_id = 0 THEN
1678: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in launching program to refresh snapshot, ' || lv_snapshot_name);
1679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error message - ' || SQLERRM);
1680: cancel_submitted_requests(lv_req_id);
1681: close c_snap;
1682: RETURN FALSE;
1675: COMMIT;
1676:
1677: IF v_request_id = 0 THEN
1678: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in launching program to refresh snapshot, ' || lv_snapshot_name);
1679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error message - ' || SQLERRM);
1680: cancel_submitted_requests(lv_req_id);
1681: close c_snap;
1682: RETURN FALSE;
1683: ELSE
1681: close c_snap;
1682: RETURN FALSE;
1683: ELSE
1684: lv_req_id(lv_num_of_snap) := v_request_id;
1685: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted request ' || lv_req_id(i) || ', to refresh snapshot: ' || lv_snapshot_name);
1686: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
1687: END IF;
1688:
1689: EXIT WHEN v_request_id = 0;
1682: RETURN FALSE;
1683: ELSE
1684: lv_req_id(lv_num_of_snap) := v_request_id;
1685: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted request ' || lv_req_id(i) || ', to refresh snapshot: ' || lv_snapshot_name);
1686: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
1687: END IF;
1688:
1689: EXIT WHEN v_request_id = 0;
1690: end if; --eval (lv_existance_check)
1701:
1702: IF lv_out = 2 THEN
1703: lv_failed_req_id := lv_req_id(j);
1704:
1705: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR : Please see the log files of request, ' || lv_failed_req_id || ', for details');
1706: cancel_submitted_requests(lv_req_id);
1707:
1708: RETURN FALSE;
1709: END IF;
1709: END IF;
1710:
1711: END LOOP;
1712:
1713: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed all snapshots');
1714: END IF;
1715: RETURN TRUE;
1716:
1717: EXCEPTION
1716:
1717: EXCEPTION
1718: WHEN OTHERS THEN
1719: ROLLBACK;
1720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : ' || SQLERRM);
1721:
1722: IF lv_req_id.COUNT > 0 THEN
1723: cancel_submitted_requests(lv_req_id);
1724: END IF;
1822: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
1823: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
1824: EXCEPTION
1825: WHEN OTHERS THEN
1826: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
1828: v_database_version := 9;
1829: --lv_db_version := v_version_9i;
1830: --RAISE;
1823: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
1824: EXCEPTION
1825: WHEN OTHERS THEN
1826: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
1828: v_database_version := 9;
1829: --lv_db_version := v_version_9i;
1830: --RAISE;
1831: END;
1838: v_cp_enabled := SYS_YES;
1839: v_refresh_type := pREFRESH_TYPE;
1840: lv_DEGREE := LEAST(lv_DEGREE,10);
1841:
1842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1844:
1845: dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1846:
1839: v_refresh_type := pREFRESH_TYPE;
1840: lv_DEGREE := LEAST(lv_DEGREE,10);
1841:
1842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1844:
1845: dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1846:
1847: SELECT MRP_AP_REFRESH_S.NEXTVAL
1913: IF (lv_setup_source_objs = 1) THEN
1914: IF SETUP_SOURCE_OBJECTS = FALSE THEN
1915: RAISE SOURCE_SETUP_ERROR;
1916: ELSE
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source set up completed successfully');
1918: END IF;
1919: END IF;
1920:
1921: BEGIN
1938: BEGIN
1939:
1940: IF split_refresh (lv_refresh_mode, pSNAPSHOT_NAME) = TRUE THEN
1941:
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1939:
1940: IF split_refresh (lv_refresh_mode, pSNAPSHOT_NAME) = TRUE THEN
1941:
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1940: IF split_refresh (lv_refresh_mode, pSNAPSHOT_NAME) = TRUE THEN
1941:
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1941:
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949:
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949:
1950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949:
1950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1951:
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949:
1950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1951:
1952: IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949:
1950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1951:
1952: IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
1953:
1954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a fast refresh of all snapshots...');
1950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1951:
1952: IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
1953:
1954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a fast refresh of all snapshots...');
1955: BEGIN
1956:
1957: lv_refresh_param := replace(lv_refresh_param, 'C', 'F');
1958: lv_refresh_param := replace(lv_refresh_param, '?', 'F');
1962: RETCODE := G_SUCCESS;
1963:
1964: EXCEPTION
1965: WHEN OTHERS THEN
1966: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1963:
1964: EXCEPTION
1965: WHEN OTHERS THEN
1966: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1964: EXCEPTION
1965: WHEN OTHERS THEN
1966: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1972: END IF;
1965: WHEN OTHERS THEN
1966: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1972: END IF;
1973: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
1966: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1972: END IF;
1973: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
1974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
1969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1972: END IF;
1973: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
1974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
1975: handle_ORA_12034 (ERRBUF,
1976: RETCODE,
1977: lv_snapshot_str,
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971: RAISE;
1972: END IF;
1973: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
1974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
1975: handle_ORA_12034 (ERRBUF,
1976: RETCODE,
1977: lv_snapshot_str,
1978: lv_refresh_param,
1984: ELSE
1985: ROLLBACK;
1986: RETCODE:= G_ERROR;
1987: ERRBUF:= SQLERRM;
1988: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, MSC_UTIL.G_ERROR_STACK);
1989: RAISE;
1990: END IF;
1991: END ;
1992:
2002: WHEN OTHERS THEN
2003: ROLLBACK;
2004: RETCODE:= G_ERROR;
2005: ERRBUF:= SQLERRM;
2006: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2007: RAISE;
2008: END;
2009:
2010:
2009:
2010:
2011: IF lv_initialization_flag = 1 THEN
2012:
2013: lv_sql_stmt:= 'TRUNCATE TABLE '|| MSC_UTIL.G_MRP_SCHEMA||'.MRP_AD_SUPPLY';
2014: EXECUTE IMMEDIATE lv_sql_stmt;
2015:
2016: END IF;
2017:
2047: EXCEPTION
2048: WHEN NO_DATA_FOUND THEN
2049: RETCODE:= G_ERROR;
2050: ERRBUF := 'Please verify Setup DBLinks setup in Source Database';
2051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2052: WHEN OTHERS THEN
2053: RETCODE:= G_ERROR;
2054: ERRBUF:= SQLERRM;
2055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2052: WHEN OTHERS THEN
2053: RETCODE:= G_ERROR;
2054: ERRBUF:= SQLERRM;
2055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2056: RAISE;
2057: END;
2058:
2059: IF (pREFRESH_TYPE = 'T' ) THEN
2085: FROM fnd_application
2086: WHERE application_id=714;
2087:
2088: IF (CHECK_INSTALL(lv_flm_appl_short_name)) THEN
2089: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Populating Flow Demand, Complete Refresh Flow: ' ||
2090: to_char (lv_complete_ref_flow));
2091:
2092: lv_task_start_time := SYSDATE;
2093:
2102: lv_wfd_err_msg);
2103: END IF;
2104:
2105: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2107:
2108: EXCEPTION
2109: WHEN OTHERS THEN
2110: NULL;
2120: NULL;
2121: ELSE
2122: IF (v_explode_ato = 'Y') THEN
2123: -- explode ATO only if the profile is YES
2124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding SMC demand, LRN: ' || to_char (v_lrn));
2125:
2126: lv_task_start_time := SYSDATE;
2127:
2128: lv_standard_ret :=
2130:
2131: lv_elapsed_mins :=
2132: CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2133:
2134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2135:
2136: IF lv_standard_ret = 2 THEN
2137: RAISE CONFIG_BOM_NOT_FOUND;
2138: END IF;
2143: EXCEPTION
2144: WHEN CONFIG_BOM_NOT_FOUND THEN
2145: RETCODE:= G_WARNING;
2146: ERRBUF := 'Please check the warning message in the logfile';
2147: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2148: WHEN OTHERS THEN
2149: NULL;
2150: END;
2151: COMMIT;
2166: end if;
2167: end loop;
2168: EXCEPTION
2169: WHEN OTHERS THEN
2170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' An error occured in building the item name from KFV');
2171: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2172: lv_item_name_kfv := 'x.SEGMENT1';
2173: END;
2174:
2167: end loop;
2168: EXCEPTION
2169: WHEN OTHERS THEN
2170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' An error occured in building the item name from KFV');
2171: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2172: lv_item_name_kfv := 'x.SEGMENT1';
2173: END;
2174:
2175:
2241:
2242:
2243: /* SITE TO REGION MAPPING */
2244: IF (pCALLING_MODULE = G_COLLECTIONS ) AND ( pREFRESH_TYPE='C' OR pREFRESH_TYPE='P') THEN
2245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'before calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2246: IF MRP_CL_FUNCTION.MAP_REGION_TO_SITE(max_lrd)=1 THEN NULL; END IF;
2247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'after calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2248: END IF;
2249:
2243: /* SITE TO REGION MAPPING */
2244: IF (pCALLING_MODULE = G_COLLECTIONS ) AND ( pREFRESH_TYPE='C' OR pREFRESH_TYPE='P') THEN
2245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'before calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2246: IF MRP_CL_FUNCTION.MAP_REGION_TO_SITE(max_lrd)=1 THEN NULL; END IF;
2247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'after calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2248: END IF;
2249:
2250: RETCODE:= G_SUCCESS;
2251: ERRBUF:= null;
2248: END IF;
2249:
2250: RETCODE:= G_SUCCESS;
2251: ERRBUF:= null;
2252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refresh Snapshot process completed successfully');
2253: return;
2254:
2255: EXCEPTION
2256: WHEN SOURCE_SETUP_ERROR THEN
2253: return;
2254:
2255: EXCEPTION
2256: WHEN SOURCE_SETUP_ERROR THEN
2257: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Setting Up Source Objects');
2258: RETCODE:= G_ERROR;
2259:
2260: ERRBUF:= SQLERRM;
2261:
2259:
2260: ERRBUF:= SQLERRM;
2261:
2262: WHEN INDIVIDUAL_REFRESH_ERROR THEN
2263: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2264: RETCODE:= G_ERROR;
2265: ERRBUF:= SQLERRM;
2266:
2267: WHEN TRUNCATE_LOG_ERROR THEN
2266:
2267: WHEN TRUNCATE_LOG_ERROR THEN
2268: RETCODE:= G_ERROR;
2269: ERRBUF:= SQLERRM;
2270: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2271:
2272: WHEN OTHERS THEN
2273:
2274: ROLLBACK;
2276: RETCODE:= G_ERROR;
2277:
2278: ERRBUF:= SQLERRM;
2279:
2280: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2281:
2282: END REFRESH_SNAPSHOT;
2283:
2284: FUNCTION GET_REFRESH_TYPE ( p_base_schema IN VARCHAR2,
2323: /* launch the snapshot in complete mode without erroring out.*/
2324: lv_refresh_type := 'C';
2325: END IF;
2326:
2327: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Optimal refresh type found:' ||lv_refresh_type );
2328: return lv_refresh_type;
2329:
2330: END GET_REFRESH_TYPE;
2331:
2346: lv_base_schema VARCHAR2(30);
2347:
2348: lv_refresh_param VARCHAR2(1);
2349: lv_snapshot_name VARCHAR2(100) := pSNAPSHOT_NAME;
2350: lv_snap_str VARCHAR2(150) := MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2351: BEGIN
2352:
2353: BEGIN
2354: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
2354: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
2355: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
2356: EXCEPTION
2357: WHEN OTHERS THEN
2358: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2359: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
2360: v_database_version := 9;
2361: --lv_db_version := v_version_9i;
2362: --RAISE;
2355: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
2356: EXCEPTION
2357: WHEN OTHERS THEN
2358: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2359: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
2360: v_database_version := 9;
2361: --lv_db_version := v_version_9i;
2362: --RAISE;
2363: END;
2371: v_refresh_number := pCURRENT_LRN;
2372: END IF;
2373:
2374: IF (pREFRESH_MODE = 1) THEN -- fast
2375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, fast');
2376: lv_refresh_param := 'F';
2377: ELSIF (pREFRESH_MODE = 2) THEN --complete
2378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, complete');
2379: lv_refresh_param := 'C';
2374: IF (pREFRESH_MODE = 1) THEN -- fast
2375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, fast');
2376: lv_refresh_param := 'F';
2377: ELSIF (pREFRESH_MODE = 2) THEN --complete
2378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, complete');
2379: lv_refresh_param := 'C';
2380: ELSE -- automatic or force
2381: SELECT master_table, erp_product_code
2382: INTO lv_base_table_name,lv_erp_product_code
2382: INTO lv_base_table_name,lv_erp_product_code
2383: FROM MSC_COLL_SNAPSHOTS_V
2384: WHERE mview_name = lv_snapshot_name;
2385:
2386: lv_base_schema := MSC_UTIL.GET_SCHEMA_NAME(lv_erp_product_code);
2387:
2388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Master-Table = '|| lv_base_schema || '.'|| lv_base_table_name);
2389:
2390: --If logs are truncated, do Complete refresh, else let system decide best refresh method
2384: WHERE mview_name = lv_snapshot_name;
2385:
2386: lv_base_schema := MSC_UTIL.GET_SCHEMA_NAME(lv_erp_product_code);
2387:
2388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Master-Table = '|| lv_base_schema || '.'|| lv_base_table_name);
2389:
2390: --If logs are truncated, do Complete refresh, else let system decide best refresh method
2391: IF pREFRESH_MODE = 3 AND TRUNC_SNAP_LOG(p_NUMBER_OF_ROWS,lv_base_schema,
2392: lv_base_table_name,lv_snapshot_name,pDEGREE) THEN
2397: END IF; -- pREFRESH_MODE
2398:
2399: --Refreshing the snapshot
2400: BEGIN
2401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Refreshing the snapshot : ' || lv_snap_str || ' in ' || lv_refresh_param || ' mode with degree ' || pDEGREE);
2402: if (v_database_version >= 10) then
2403: DBMS_MVIEW.REFRESH(LIST => lv_snap_str,
2404: METHOD => lv_refresh_param,
2405: atomic_refresh => FALSE,
2410: parallelism => pDEGREE);
2411: end if;
2412: EXCEPTION
2413: WHEN OTHERS THEN
2414: MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2411: end if;
2412: EXCEPTION
2413: WHEN OTHERS THEN
2414: MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2412: EXCEPTION
2413: WHEN OTHERS THEN
2414: MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2420: RAISE;
2413: WHEN OTHERS THEN
2414: MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2420: RAISE;
2421: END IF;
2414: MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2420: RAISE;
2421: END IF;
2422:
2415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2420: RAISE;
2421: END IF;
2422:
2423:
2420: RAISE;
2421: END IF;
2422:
2423:
2424: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
2425: handle_ORA_12034 (ERRBUF,
2426: RETCODE,
2427: lv_snap_str,
2428: lv_refresh_param,
2445: EXECUTE IMMEDIATE ' SELECT DECODE(last_refresh_type,''COMPLETE'',''C'', ''F'') '
2446: || ' FROM all_mviews WHERE mview_name = :lv_snapshot_name '
2447: || ' AND owner = :lv_snap_schema '
2448: INTO lv_refresh_param -- overwrite existing value with the actual refresh done...
2449: USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
2450:
2451: END IF;
2452:
2453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2449: USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
2450:
2451: END IF;
2452:
2453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2456:
2457:
2450:
2451: END IF;
2452:
2453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2456:
2457:
2458: IF lv_refresh_param = 'C' THEN
2451: END IF;
2452:
2453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2456:
2457:
2458: IF lv_refresh_param = 'C' THEN
2459: UPDATE MSC_COLL_SNAPSHOTS_V
2460: SET complete_refresh_timestamp = to_char(sysdate,'YYYY-MM-DD HH:MI:SS')
2461: WHERE MVIEW_NAME = lv_snapshot_name ;
2462:
2463: COMMIT;
2464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'successfully updated the complete refresh time in fnd_lookup_values');
2465: END IF;
2466:
2467: EXCEPTION
2468: WHEN OTHERS THEN
2468: WHEN OTHERS THEN
2469: ROLLBACK;
2470: RETCODE:= G_ERROR;
2471: ERRBUF:= SQLERRM;
2472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error refreshing individual snapshot : ' || lv_snap_str);
2473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '|| ERRBUF);
2474: RAISE;
2475: END REFRESH_SINGLE_SNAPSHOT;
2476:
2469: ROLLBACK;
2470: RETCODE:= G_ERROR;
2471: ERRBUF:= SQLERRM;
2472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error refreshing individual snapshot : ' || lv_snap_str);
2473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '|| ERRBUF);
2474: RAISE;
2475: END REFRESH_SINGLE_SNAPSHOT;
2476:
2477: PROCEDURE DROP_SNAPSHOT(
2496:
2497: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
2498:
2499: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
2500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
2501: ELSE
2502:
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
2504:
2499: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
2500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
2501: ELSE
2502:
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
2504:
2505: lv_cusror_str := ' select mview_name, existance_check
2506: from msc_coll_snapshots_v
2507: where mview_name in ' || lv_snapshot_groups_str || '
2513: fetch c_snap into lv_snapshot_name,lv_existance_check;
2514: exit when c_snap%notfound;
2515: if eval(lv_existance_check) then
2516:
2517: lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2518:
2519: EXECUTE IMMEDIATE lv_sql_stmt;
2520: --Droping the Synonym and Trigger on this snapshot
2521: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2517: lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2518:
2519: EXECUTE IMMEDIATE lv_sql_stmt;
2520: --Droping the Synonym and Trigger on this snapshot
2521: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2522: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2524: drop_count := drop_count + 1;
2525: else
2518:
2519: EXECUTE IMMEDIATE lv_sql_stmt;
2520: --Droping the Synonym and Trigger on this snapshot
2521: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2522: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2524: drop_count := drop_count + 1;
2525: else
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2519: EXECUTE IMMEDIATE lv_sql_stmt;
2520: --Droping the Synonym and Trigger on this snapshot
2521: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2522: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2524: drop_count := drop_count + 1;
2525: else
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2527: end if;
2522: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2524: drop_count := drop_count + 1;
2525: else
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2527: end if;
2528: END LOOP;
2529:
2530: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2527: end if;
2528: END LOOP;
2529:
2530: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2531: END IF;
2532: if drop_count > 0 then
2533: begin
2534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
2530: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2531: END IF;
2532: if drop_count > 0 then
2533: begin
2534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
2535:
2536: UPDATE FND_PROFILE_OPTION_VALUES
2537: SET PROFILE_OPTION_VALUE = 'Y'
2538: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2537: SET PROFILE_OPTION_VALUE = 'Y'
2538: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2539: FROM FND_PROFILE_OPTIONS
2540: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
2541: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
2542: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
2543: COMMIT;
2544:
2545: EXCEPTION
2538: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2539: FROM FND_PROFILE_OPTIONS
2540: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
2541: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
2542: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
2543: COMMIT;
2544:
2545: EXCEPTION
2546:
2544:
2545: EXCEPTION
2546:
2547: WHEN OTHERS THEN
2548: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
2549: end;
2550: end if;
2551: RETCODE:= G_SUCCESS;
2552:
2554: WHEN OTHERS THEN
2555: ROLLBACK;
2556: RETCODE:= G_ERROR;
2557: ERRBUF:= SQLERRM;
2558: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2559: END DROP_SNAPSHOT;
2560:
2561:
2562: PROCEDURE check_MV_cont_ref_type(p_MV_name in varchar2,
2584: select 1
2585: into lv_MV_complete_refresh
2586: from msc_coll_snapshots_v
2587: where mview_name = p_MV_name
2588: and existance_check = MSC_UTIL.SYS_YES
2589: and nvl(complete_refresh_timestamp,p_last_tgt_cont_coll_time) > p_last_tgt_cont_coll_time;
2590: EXCEPTION
2591: WHEN NO_DATA_FOUND THEN
2592: lv_MV_complete_refresh := 0;
2591: WHEN NO_DATA_FOUND THEN
2592: lv_MV_complete_refresh := 0;
2593: END;
2594: IF lv_MV_complete_refresh = 1 THEN
2595: entity_flag := MSC_UTIL.SYS_TGT;
2596: RETURN;
2597: END IF;
2598: END IF;
2599:
2619: and rownum < :num_thr' ;
2620: EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_snap USING 1+((lv_Num_del + lv_Num_new)/(p_coll_thresh/100));
2621:
2622: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
2623: entity_flag := MSC_UTIL.SYS_NO;
2624: ELSIF lv_Num_snap =0 THEN
2625: entity_flag := MSC_UTIL.SYS_TGT;
2626: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
2627: entity_flag := MSC_UTIL.SYS_TGT;
2621:
2622: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
2623: entity_flag := MSC_UTIL.SYS_NO;
2624: ELSIF lv_Num_snap =0 THEN
2625: entity_flag := MSC_UTIL.SYS_TGT;
2626: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
2627: entity_flag := MSC_UTIL.SYS_TGT;
2628: ELSE
2629: entity_flag := MSC_UTIL.SYS_INCR;
2623: entity_flag := MSC_UTIL.SYS_NO;
2624: ELSIF lv_Num_snap =0 THEN
2625: entity_flag := MSC_UTIL.SYS_TGT;
2626: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
2627: entity_flag := MSC_UTIL.SYS_TGT;
2628: ELSE
2629: entity_flag := MSC_UTIL.SYS_INCR;
2630: END IF;
2631:
2625: entity_flag := MSC_UTIL.SYS_TGT;
2626: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
2627: entity_flag := MSC_UTIL.SYS_TGT;
2628: ELSE
2629: entity_flag := MSC_UTIL.SYS_INCR;
2630: END IF;
2631:
2632:
2633:
2631:
2632:
2633:
2634: EXCEPTION WHEN OTHERS THEN
2635: entity_flag := MSC_UTIL.SYS_INCR;
2636: p_ret_code := MSC_UTIL.G_ERROR;
2637: p_err_buf := p_err_buf || ' ' ||p_MV_name ;
2638: END check_MV_cont_ref_type;
2639:
2632:
2633:
2634: EXCEPTION WHEN OTHERS THEN
2635: entity_flag := MSC_UTIL.SYS_INCR;
2636: p_ret_code := MSC_UTIL.G_ERROR;
2637: p_err_buf := p_err_buf || ' ' ||p_MV_name ;
2638: END check_MV_cont_ref_type;
2639:
2640:
2659:
2660: lv_cusros_str varchar2(500);
2661:
2662: lv_MV_complete_refresh number :=0 ;
2663: lv_entity_decided number := msc_util.sys_no;
2664: lv_entity_incr number := msc_util.sys_no;
2665: BEGIN
2666:
2667: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
2660: lv_cusros_str varchar2(500);
2661:
2662: lv_MV_complete_refresh number :=0 ;
2663: lv_entity_decided number := msc_util.sys_no;
2664: lv_entity_incr number := msc_util.sys_no;
2665: BEGIN
2666:
2667: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
2668: IF p_last_tgt_cont_coll_time is NOT NULL THEN
2670: Execute immediate '
2671: select 1
2672: from msc_coll_snapshots_v
2673: where snapshot_group_string = ''' || p_entity_name || '''
2674: and existance_check = ' || MSC_UTIL.SYS_YES || '
2675: and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
2676: and nvl(complete_refresh_timestamp,:vDate) > :vDate
2677: and rownum < 2'
2678: into lv_MV_complete_refresh
2671: select 1
2672: from msc_coll_snapshots_v
2673: where snapshot_group_string = ''' || p_entity_name || '''
2674: and existance_check = ' || MSC_UTIL.SYS_YES || '
2675: and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
2676: and nvl(complete_refresh_timestamp,:vDate) > :vDate
2677: and rownum < 2'
2678: into lv_MV_complete_refresh
2679: using p_last_tgt_cont_coll_time, p_last_tgt_cont_coll_time; -- tobe enhanced
2681: WHEN NO_DATA_FOUND THEN
2682: lv_MV_complete_refresh := 0;
2683: END;
2684: IF lv_MV_complete_refresh = 1 THEN
2685: entity_flag := MSC_UTIL.SYS_TGT;
2686: RETURN;
2687: END IF;
2688: END IF;
2689:
2688: END IF;
2689:
2690: lv_cusros_str := ' select mview_name, ad_table_name, existance_check
2691: from msc_coll_snapshots_v
2692: where check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
2693: and snapshot_group_string = ''' || p_entity_name || '''' ;
2694:
2695: OPEN c_snap for lv_cusros_str;
2696:
2708: p_last_tgt_cont_coll_time,
2709: p_ret_code,
2710: p_err_buf);
2711:
2712: IF entity_flag = MSC_UTIL.SYS_TGT THEN
2713: lv_entity_decided := msc_util.sys_yes;
2714: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
2715: lv_entity_incr := msc_util.sys_yes;
2716: END IF;
2709: p_ret_code,
2710: p_err_buf);
2711:
2712: IF entity_flag = MSC_UTIL.SYS_TGT THEN
2713: lv_entity_decided := msc_util.sys_yes;
2714: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
2715: lv_entity_incr := msc_util.sys_yes;
2716: END IF;
2717:
2710: p_err_buf);
2711:
2712: IF entity_flag = MSC_UTIL.SYS_TGT THEN
2713: lv_entity_decided := msc_util.sys_yes;
2714: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
2715: lv_entity_incr := msc_util.sys_yes;
2716: END IF;
2717:
2718: /* if the last MV suggested no collection,
2711:
2712: IF entity_flag = MSC_UTIL.SYS_TGT THEN
2713: lv_entity_decided := msc_util.sys_yes;
2714: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
2715: lv_entity_incr := msc_util.sys_yes;
2716: END IF;
2717:
2718: /* if the last MV suggested no collection,
2719: this need to be updated with previous result. */
2716: END IF;
2717:
2718: /* if the last MV suggested no collection,
2719: this need to be updated with previous result. */
2720: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
2721: entity_flag := MSC_UTIL.SYS_INCR;
2722: END IF;
2723:
2724: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
2717:
2718: /* if the last MV suggested no collection,
2719: this need to be updated with previous result. */
2720: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
2721: entity_flag := MSC_UTIL.SYS_INCR;
2722: END IF;
2723:
2724: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
2725: end if; --eval (lv_existance_check)
2720: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
2721: entity_flag := MSC_UTIL.SYS_INCR;
2722: END IF;
2723:
2724: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
2725: end if; --eval (lv_existance_check)
2726: END LOOP;
2727:
2728: EXCEPTION
2726: END LOOP;
2727:
2728: EXCEPTION
2729: WHEN OTHERS THEN
2730: p_ret_code := MSC_UTIL.G_ERROR;
2731: p_err_buf := SQLERRM;
2732: END check_entity_cont_ref_type;
2733:
2734:
2751: NULL,
2752: NULL,
2753: FALSE);
2754: commit;
2755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Setup Views used by Collections Process');
2756: --
2757: -- Item
2758: indx := indx + 1;
2759: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2762: NULL,
2763: NULL,
2764: FALSE);
2765: commit;
2766: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Item Views used by Collections Process');
2767: --
2768: -- BOM
2769: indx := indx + 1;
2770: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2773: NULL,
2774: NULL,
2775: FALSE);
2776: commit;
2777: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates BOM Views used by Collections Process');
2778: --
2779: -- Routing
2780: indx := indx + 1;
2781: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2784: NULL,
2785: NULL,
2786: FALSE);
2787: commit;
2788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Routing Views used by Collections Process');
2789: --
2790: -- WIP
2791: indx := indx + 1;
2792: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2795: NULL,
2796: NULL,
2797: FALSE);
2798: commit;
2799: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates WIP Views used by Collections Process');
2800: --
2801: -- Demand
2802: indx := indx + 1;
2803: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2806: NULL,
2807: NULL,
2808: FALSE);
2809: commit;
2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Demand Views used by Collections Process');
2811: --
2812:
2813: -- Supply
2814: indx := indx + 1;
2818: NULL,
2819: NULL,
2820: FALSE);
2821: commit;
2822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Supply Views used by Collections Process');
2823: --
2824: -- Other
2825: indx := indx + 1;
2826: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2829: NULL,
2830: NULL,
2831: FALSE);
2832: commit;
2833: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Other Views used by Collections Process');
2834: --
2835: -- Repair Order
2836: indx := indx + 1;
2837: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2840: NULL,
2841: NULL,
2842: FALSE);
2843: commit;
2844: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Repair Order Views used by Collections Process');
2845: --
2846: for i in 1 .. lv_request_id_views.last
2847: loop
2848: wait_for_request(lv_request_id_views(i), 10, lv_out);
2847: loop
2848: wait_for_request(lv_request_id_views(i), 10, lv_out);
2849: if lv_out = 2 THEN
2850: ERRBUF := 'Error in creating Source Views';
2851: RETCODE := MSC_UTIL.G_ERROR;
2852: EXIT;
2853: end if;
2854: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'id-'||lv_request_id_views(i));
2855: end loop;
2850: ERRBUF := 'Error in creating Source Views';
2851: RETCODE := MSC_UTIL.G_ERROR;
2852: EXIT;
2853: end if;
2854: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'id-'||lv_request_id_views(i));
2855: end loop;
2856:
2857: EXCEPTION
2858: WHEN OTHERS THEN
2855: end loop;
2856:
2857: EXCEPTION
2858: WHEN OTHERS THEN
2859: RETCODE := MSC_UTIL.G_ERROR;
2860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2861: RAISE;
2862:
2863: END CREATE_SOURCE_VIEWS;
2856:
2857: EXCEPTION
2858: WHEN OTHERS THEN
2859: RETCODE := MSC_UTIL.G_ERROR;
2860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2861: RAISE;
2862:
2863: END CREATE_SOURCE_VIEWS;
2864:
2880: NULL,
2881: NULL,
2882: FALSE);
2883: commit;
2884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Item Triggers used by Collections Process');
2885: --
2886: -- BOM
2887: indx := indx + 1;
2888: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2891: NULL,
2892: NULL,
2893: FALSE);
2894: commit;
2895: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates BOM Triggers used by Collections Process');
2896: --
2897: -- Routing
2898: indx := indx + 1;
2899: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2902: NULL,
2903: NULL,
2904: FALSE);
2905: commit;
2906: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Routing Triggers used by Collections Process');
2907: --
2908: -- WIP
2909: indx := indx + 1;
2910: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2913: NULL,
2914: NULL,
2915: FALSE);
2916: commit;
2917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates WIP Triggers used by Collections Process');
2918: --
2919: -- Demand
2920: indx := indx + 1;
2921: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2924: NULL,
2925: NULL,
2926: FALSE);
2927: commit;
2928: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Demand Triggers used by Collections Process');
2929: --
2930:
2931: -- Supply
2932: indx := indx + 1;
2936: NULL,
2937: NULL,
2938: FALSE);
2939: commit;
2940: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Supply Triggers used by Collections Process');
2941: --
2942: -- Other
2943: indx := indx + 1;
2944: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2947: NULL,
2948: NULL,
2949: FALSE);
2950: commit;
2951: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Other Triggers used by Collections Process');
2952: --
2953: -- Repair Order
2954: indx := indx + 1;
2955: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2958: NULL,
2959: NULL,
2960: FALSE);
2961: commit;
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Repair Order Triggers used by Collections Process');
2963: --
2964: for i in 1 .. lv_request_id_trigs.last
2965: loop
2966: wait_for_request(lv_request_id_trigs(i), 10, lv_out);
2965: loop
2966: wait_for_request(lv_request_id_trigs(i), 10, lv_out);
2967: if lv_out = 2 THEN
2968: ERRBUF := 'Error in creating Source Triggers';
2969: RETCODE := MSC_UTIL.G_ERROR;
2970: EXIT;
2971: end if;
2972: end loop;
2973:
2972: end loop;
2973:
2974: EXCEPTION
2975: WHEN OTHERS THEN
2976: RETCODE := MSC_UTIL.G_ERROR;
2977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2978: RAISE;
2979:
2980: END CREATE_SOURCE_TRIGGERS;
2973:
2974: EXCEPTION
2975: WHEN OTHERS THEN
2976: RETCODE := MSC_UTIL.G_ERROR;
2977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2978: RAISE;
2979:
2980: END CREATE_SOURCE_TRIGGERS;
2981: