[Home] [Help]
49: lv_index_tablespace VARCHAR2(30);
50: lv_storage_clause VARCHAR2(200);
51: BEGIN
52:
53: lv_csd_schema := MSC_UTIL.GET_SCHEMA_NAME(512);
54: MSC_UTIL.GET_STORAGE_PARAMETERS('CSD_REPAIRS',
55: lv_csd_schema,
56: lv_tablespace,
57: lv_index_tablespace,
50: lv_storage_clause VARCHAR2(200);
51: BEGIN
52:
53: lv_csd_schema := MSC_UTIL.GET_SCHEMA_NAME(512);
54: MSC_UTIL.GET_STORAGE_PARAMETERS('CSD_REPAIRS',
55: lv_csd_schema,
56: lv_tablespace,
57: lv_index_tablespace,
58: lv_storage_clause);
59:
60: lv_sql_stmt:= 'CREATE INDEX CSD_REPAIRS_N11 ON CSD_REPAIRS (inventory_org_id ,'
61: || 'last_update_date ,REPAIR_MODE ) TABLESPACE '
62: ||lv_index_tablespace;
63: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
64: BEGIN
65: Csd_Repairs_Util.create_csd_index(lv_sql_stmt,'CSD_REPAIRS_N11');
66: lv_status :=1 ;
67: EXCEPTION
73: /*Index already exists*/
74: lv_status :=1 ;
75: ELSE
76: lv_status := 2 ;
77: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
78: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
79: END IF;
80: END ;
81: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
77: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
78: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
79: END IF;
80: END ;
81: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
82: MSC_UTIL.GET_STORAGE_PARAMETERS('CSP_REPAIR_PO_HEADERS',
83: lv_csp_schema,
84: lv_tablespace,
85: lv_index_tablespace,
78: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
79: END IF;
80: END ;
81: lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
82: MSC_UTIL.GET_STORAGE_PARAMETERS('CSP_REPAIR_PO_HEADERS',
83: lv_csp_schema,
84: lv_tablespace,
85: lv_index_tablespace,
86: lv_storage_clause);
88: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N1 ON CSP_REPAIR_PO_HEADERS'
89: || '(WIP_id, REPAIR_PO_HEADER_ID, '
90: || 'INVENTORY_ITEM_ID) TABLESPACE '
91: ||lv_index_tablespace;
92: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
93: BEGIN
94: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N1');
95: lv_status :=1 ;
96: EXCEPTION
102: /*Index already exists*/
103: lv_status :=1 ;
104: ELSE
105: lv_status := 2 ;
106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
107: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
108: END IF;
109: END ;
110:
110:
111: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N2 ON CSP_REPAIR_PO_HEADERS'
112: || '( purchase_order_header_id) TABLESPACE '
113: ||lv_index_tablespace;
114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
115: BEGIN
116: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N2');
117: lv_status :=1 ;
118: EXCEPTION
124: /*Index already exists*/
125: lv_status :=1 ;
126: ELSE
127: lv_status := 2 ;
128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
129: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
130: END IF;
131: END ;
132:
132:
133: lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N3 ON CSP_REPAIR_PO_HEADERS'
134: || '( requisition_line_id ) TABLESPACE '
135: ||lv_index_tablespace;
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
137: BEGIN
138: CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N3');
139: lv_status :=1 ;
140: EXCEPTION
146: /*Index already exists*/
147: lv_status :=1 ;
148: ELSE
149: lv_status := 2 ;
150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
151: raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
152: END IF;
153: END ;
154: lv_status :=1 ;
192: NULL,
193: FALSE); -- sub request
194:
195: commit;
196: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '
197: ||lv_request_id_drop||' :Checks for Snapshots which have changed and Drops them');
198: wait_for_request(lv_request_id_drop, 10, lv_out);
199:
200: if lv_success THEN
203:
204: if lv_success THEN
205: lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink||'(''MSC_SRP_ENABLED''),''N'')'
206: || ' from dual ';
207: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
208: execute immediate lv_sql_stmt into lv_srp_enabled_flag ;
209: IF lv_srp_enabled_flag='Y' THEN
210: CREATE_INDEX(lv_out);
211: if lv_out = 2 THEN
224: NULL,
225: NULL,
226: FALSE); -- sub request
227: commit;
228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsm||' :Creates WSM Snapshot Logs and Snapshots');
229:
230: lv_request_id_bom := FND_REQUEST.SUBMIT_REQUEST(
231: 'MSC',
232: 'MSCBOMSN',
233: NULL,
234: NULL,
235: FALSE); -- sub request
236: commit;
237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_bom||' :Creates BOM Snapshot Logs and Snapshots');
238:
239: lv_request_id_inv := FND_REQUEST.SUBMIT_REQUEST(
240: 'MSC',
241: 'MSCINVSN',
242: NULL,
243: NULL,
244: FALSE); -- sub request
245: commit;
246: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_inv||' :Creates INV Snapshot Logs and Snapshots');
247:
248: lv_request_id_csp := FND_REQUEST.SUBMIT_REQUEST(
249: 'MSC',
250: 'MSCCSPSN',
251: NULL,
252: NULL,
253: FALSE); -- sub request
254: commit;
255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_csp||' :Creates CSP Snapshot Logs and Snapshots');
256:
257: lv_request_id_mrp := FND_REQUEST.SUBMIT_REQUEST(
258: 'MSC',
259: 'MSCMRPSN',
260: NULL,
261: NULL,
262: FALSE); -- sub request
263: commit;
264: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_mrp||' :Creates MRP Snapshot Logs and Snapshots');
265:
266: lv_request_id_pox := FND_REQUEST.SUBMIT_REQUEST(
267: 'MSC',
268: 'MSCPOXSN',
269: NULL,
270: NULL,
271: FALSE); -- sub request
272: commit;
273: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_pox||' :Creates PO Snapshot Logs and Snapshots');
274:
275: lv_request_id_ont := FND_REQUEST.SUBMIT_REQUEST(
276: 'MSC',
277: 'MSCONTSN',
278: NULL,
279: NULL,
280: FALSE); -- sub request
281: commit;
282: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ont||' :Creates OE Snapshot Logs and Snapshots');
283:
284: lv_request_id_wsh := FND_REQUEST.SUBMIT_REQUEST(
285: 'MSC',
286: 'MSCWSHSN',
287: NULL,
288: NULL,
289: FALSE); -- sub request
290: commit;
291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsh||' :Creates WSH Snapshot Logs and Snapshots');
292:
293: IF MRP_CL_FUNCTION.CHECK_AHL_VER = 1 THEN
294:
295: lv_request_id_ahl := FND_REQUEST.SUBMIT_REQUEST(
298: NULL,
299: NULL,
300: FALSE); -- sub request
301: commit;
302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ahl||' :Creates AHL Snapshot Logs and Snapshots');
303:
304: /* ds change: change start */
305: lv_request_id_eam := FND_REQUEST.SUBMIT_REQUEST(
306: 'MSC',
308: NULL,
309: NULL,
310: FALSE); -- sub request
311: commit;
312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_eam||' :Creates EAM Snapshot Logs and Snapshots');
313:
314: /* ds change: change end */
315:
316:
333: NULL,
334: NULL,
335: FALSE); -- sub request
336: commit;
337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wip||' :Creates WIP Snapshot Logs and Snapshots');
338:
339: if lv_out = 2 THEN lv_success := FALSE ; end if;
340: end if;
341:
392: NULL,
393: NULL,
394: FALSE); -- sub request
395: commit;
396: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_syn||' :Creates Synonyms used by Collections Process');
397:
398: wait_for_request(lv_request_id_syn, 10, lv_out);
399: if lv_success THEN
400: if lv_out = 2 THEN lv_success := FALSE ; end if;
407: NULL,
408: NULL,
409: FALSE); -- sub request
410: commit;
411: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_view||' :Creates Views used by Collections Process');
412:
413: lv_request_id_trig := FND_REQUEST.SUBMIT_REQUEST(
414: 'MSC',
415: 'MSCTRIGS',
416: NULL,
417: NULL,
418: FALSE); -- sub request
419: commit;
420: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trig||' :Creates Triggers used by Collections Process');
421: wait_for_request(lv_request_id_view, 10, lv_out);
422: if lv_success THEN
423: if lv_out = 2 THEN lv_success := FALSE ; end if;
424: end if;
441: IF lv_success THEN
442: -- AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN --9396359
443:
444: begin
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to No ');
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Region Site Mapping not being called here ');
447:
448: UPDATE FND_PROFILE_OPTION_VALUES
449: SET PROFILE_OPTION_VALUE = 'N'
442: -- AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN --9396359
443:
444: begin
445: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to No ');
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Region Site Mapping not being called here ');
447:
448: UPDATE FND_PROFILE_OPTION_VALUES
449: SET PROFILE_OPTION_VALUE = 'N'
450: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
449: SET PROFILE_OPTION_VALUE = 'N'
450: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
451: FROM FND_PROFILE_OPTIONS
452: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value No indicates that the Collection Setup Objects have been applied');
455: COMMIT;
456: return TRUE;
457:
450: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
451: FROM FND_PROFILE_OPTIONS
452: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
453: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value No indicates that the Collection Setup Objects have been applied');
455: COMMIT;
456: return TRUE;
457:
458: EXCEPTION
457:
458: EXCEPTION
459:
460: WHEN OTHERS THEN
461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
462: end;
463: ELSE
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
460: WHEN OTHERS THEN
461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
462: end;
463: ELSE
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
462: end;
463: ELSE
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
462: end;
463: ELSE
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
463: ELSE
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
464: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
466: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
467: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
468: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
469: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
473: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_csp);
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
481:
474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
481:
482:
475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
481:
482:
483: return false;
476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
481:
482:
483: return false;
484:
488: EXCEPTION
489:
490: WHEN OTHERS THEN
491:
492: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
493: return FALSE;
494: END SETUP_SOURCE_OBJECTS;
495: /* NEW Patching Strategy */
496: --3967634 added function CHECK_INSTALL
631: ERRBUF := 'NO_USER_DEFINED';
632: RETURN;
633: END;
634:
635: IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
636: MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
637: l_user_id,
638: -1, --l_resp_id,
639: -1 --l_application_id
632: RETURN;
633: END;
634:
635: IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
636: MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
637: l_user_id,
638: -1, --l_resp_id,
639: -1 --l_application_id
640: );
638: -1, --l_resp_id,
639: -1 --l_application_id
640: );
641: ELSE
642: RETCODE:= MSC_UTIL.G_ERROR;
643: ERRBUF := lv_log_msg;
644: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
645: RETURN;
646: END IF;
640: );
641: ELSE
642: RETCODE:= MSC_UTIL.G_ERROR;
643: ERRBUF := lv_log_msg;
644: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
645: RETURN;
646: END IF;
647:
648: END IF;
755: AND owner = lv_owner;
756:
757: BEGIN
758:
759: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
760:
761: FOR c1 in c_query_tables(lv_mrp_schema)
762: LOOP
763: BEGIN
766: EXECUTE IMMEDIATE lv_sql_stmt;
767:
768: EXCEPTION
769: WHEN OTHERS THEN
770: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771: END;
772: END LOOP;
773:
774: EXCEPTION
772: END LOOP;
773:
774: EXCEPTION
775: WHEN OTHERS THEN
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
777:
778: END PURGE_OBSOLETE_DATA;
779:
780: /*NEW PATCHING STRATEGY */
937: AND ROWNUM = 1;
938:
939: IF pNUM_OF_ROWS > 0 THEN
940:
941: lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
942: || ' where rownum < 2 ';
943: EXECUTE IMMEDIATE lv_sel_snp_stmt INTO lv_num_snp_rows;
944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' more than .. '||lv_num_snp_rows);
945:
940:
941: lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
942: || ' where rownum < 2 ';
943: EXECUTE IMMEDIATE lv_sel_snp_stmt INTO lv_num_snp_rows;
944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' more than .. '||lv_num_snp_rows);
945:
946: IF lv_num_snp_rows > 0 THEN
947: lv_sel_sql_stmt := ' select count(*) from '||lv_base_schema|| '.'||lv_mlog_tab_name
948: || ' where rownum <= :p1 ';
949: EXECUTE IMMEDIATE lv_sel_sql_stmt
950: INTO lv_num_of_log_rows
951: USING pNUM_OF_ROWS;
952:
953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
954: ||' more than .. '||lv_num_of_log_rows);
955: END IF;
956: END IF;
957:
956: END IF;
957:
958: exception
959: when others then
960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
961: raise;
962: end;
963:
964: IF (pNUM_OF_ROWS <= lv_num_of_log_rows) OR (lv_num_snp_rows = 0) THEN
965:
966: begin
967: lv_trnc_sql_stmt := 'TRUNCATE TABLE '||lv_base_schema|| '.' ||lv_mlog_tab_name;
968: EXECUTE IMMEDIATE lv_trnc_sql_stmt;
969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
971: exception
972: when others then
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
966: begin
967: lv_trnc_sql_stmt := 'TRUNCATE TABLE '||lv_base_schema|| '.' ||lv_mlog_tab_name;
968: EXECUTE IMMEDIATE lv_trnc_sql_stmt;
969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
971: exception
972: when others then
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
971: exception
972: when others then
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
976: RETURN TRUE; --so that the MView can do a complete refresh.
977: end;
970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
971: exception
972: when others then
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
976: RETURN TRUE; --so that the MView can do a complete refresh.
977: end;
978:
971: exception
972: when others then
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
976: RETURN TRUE; --so that the MView can do a complete refresh.
977: end;
978:
979: -- refresh the snapshot
982:
983: ELSE
984: -- this condition will return FALSE , indicating no need to truncate the log and refresh the snapshot
985: lv_status := FALSE;
986: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
987: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
988: ||' = '||lv_num_of_log_rows);
989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
990: ||') is less than the thresold entered for truncating Logs: '
983: ELSE
984: -- this condition will return FALSE , indicating no need to truncate the log and refresh the snapshot
985: lv_status := FALSE;
986: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
987: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
988: ||' = '||lv_num_of_log_rows);
989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
990: ||') is less than the thresold entered for truncating Logs: '
991: || pNUM_OF_ROWS);
985: lv_status := FALSE;
986: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
987: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
988: ||' = '||lv_num_of_log_rows);
989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
990: ||') is less than the thresold entered for truncating Logs: '
991: || pNUM_OF_ROWS);
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
993: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
988: ||' = '||lv_num_of_log_rows);
989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
990: ||') is less than the thresold entered for truncating Logs: '
991: || pNUM_OF_ROWS);
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
993: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
994:
995: END IF; -- If the truncate condition matches
996:
989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
990: ||') is less than the thresold entered for truncating Logs: '
991: || pNUM_OF_ROWS);
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
993: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
994:
995: END IF; -- If the truncate condition matches
996:
997: RETURN lv_status;
997: RETURN lv_status;
998:
999: EXCEPTION
1000: WHEN OTHERS THEN
1001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1002: RETURN FALSE;
1003: END TRUNC_SNAP_LOG;
1004:
1005: /*
1037: THEN
1038: RETURN TRUE;
1039: END IF;
1040: */
1041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'start - snapshot_data_correct');
1042: -- Add the where clause which are used in snapshot definition
1043: IF (p_snapshot = 'MTL_SUPPLY_SN') THEN
1044: lv_where_clause := ' ITEM_ID IS NOT NULL ';
1045: ELSIF (p_snapshot = 'MTL_SYS_ITEMS_SN') THEN
1094: END IF;
1095:
1096: lv_base_schema := p_base_schema;
1097: lv_master_tbl := p_table;
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_master_tbl - ' || p_base_schema || '.' || lv_master_tbl);
1099:
1100: EXECUTE IMMEDIATE
1101: ' SELECT LOG_TABLE FROM ALL_SNAPSHOT_LOGS '
1102: || ' WHERE MASTER = :lv_master_tbl AND '
1103: || ' LOG_OWNER = :p_schema AND '
1104: || ' ROWNUM = 1'
1105: INTO lv_mlog_tab_name
1106: USING upper(lv_master_tbl), upper(lv_base_schema);
1107: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_mlog_tab_name - ' || lv_mlog_tab_name);
1108:
1109: EXECUTE IMMEDIATE
1110: ' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
1111: INTO lv_tab_count;
1108:
1109: EXECUTE IMMEDIATE
1110: ' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
1111: INTO lv_tab_count;
1112: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_tab_count - ' || lv_tab_count);
1113:
1114: EXECUTE IMMEDIATE
1115: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1116: INTO lv_snap_count;
1111: INTO lv_tab_count;
1112: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_tab_count - ' || lv_tab_count);
1113:
1114: EXECUTE IMMEDIATE
1115: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1116: INTO lv_snap_count;
1117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snap_count - ' || lv_snap_count);
1118:
1119: EXECUTE IMMEDIATE
1113:
1114: EXECUTE IMMEDIATE
1115: ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1116: INTO lv_snap_count;
1117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snap_count - ' || lv_snap_count);
1118:
1119: EXECUTE IMMEDIATE
1120: ' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
1121: INTO lv_log_count;
1118:
1119: EXECUTE IMMEDIATE
1120: ' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
1121: INTO lv_log_count;
1122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_log_count - ' || lv_log_count);
1123:
1124: IF ((lv_tab_count <> 0) AND (lv_snap_count = 0))
1125: OR
1126: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1123:
1124: IF ((lv_tab_count <> 0) AND (lv_snap_count = 0))
1125: OR
1126: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning false');
1128: RETURN FALSE; -- Error out since the snapshot is not having any rows
1129: ELSE
1130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning true');
1131: RETURN TRUE; -- snapshot data is correct - so continue the refresh
1126: (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning false');
1128: RETURN FALSE; -- Error out since the snapshot is not having any rows
1129: ELSE
1130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning true');
1131: RETURN TRUE; -- snapshot data is correct - so continue the refresh
1132: END IF;
1133:
1134: EXCEPTION
1132: END IF;
1133:
1134: EXCEPTION
1135: WHEN OTHERS THEN
1136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1137: RETURN FALSE; -- Error out
1138: END SNAPSHOT_DATA_CORRECT;
1139:
1140: /*------------------------------------------------------------
1166: lv_message := lv_message || RPAD (pStatus, 10, ' ');
1167: lv_message := lv_message || RPAD (to_char(pElapsed_Time,'99990.9'),
1168: 10, ' ');
1169:
1170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_message);
1171:
1172: END Log_Snap_Ref_status;
1173:
1174: /*------------------------------------------------------------
1213: BEGIN
1214:
1215: -- First Copy the input snapshot strings into local variables.
1216:
1217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pSnapshot_List: '||
1220: pSnapshot_List);
1221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pRefresh_Param: '||
1214:
1215: -- First Copy the input snapshot strings into local variables.
1216:
1217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pSnapshot_List: '||
1220: pSnapshot_List);
1221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pRefresh_Param: '||
1222: pRefresh_Param);
1215: -- First Copy the input snapshot strings into local variables.
1216:
1217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pSnapshot_List: '||
1220: pSnapshot_List);
1221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pRefresh_Param: '||
1222: pRefresh_Param);
1223:
1217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pSnapshot_List: '||
1220: pSnapshot_List);
1221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pRefresh_Param: '||
1222: pRefresh_Param);
1223:
1224: -- BUG 9684665
1225: -- Need to initialize the variables: lv_snapshot_str and lv_refresh_param
1228: lv_refresh_param := pRefresh_Param;
1229:
1230: lv_total_snapshots := length(lv_refresh_param);
1231:
1232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_total_snapshots: '||
1233: to_char(lv_total_snapshots));
1234:
1235: FOR i IN 1..lv_total_snapshots LOOP
1236:
1275: EXCEPTION
1276: WHEN OTHERS THEN
1277:
1278: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1279: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1280:
1281: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
1282: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
1283: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN --bug 8420469
1277:
1278: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1279: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1280:
1281: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
1282: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
1283: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN --bug 8420469
1284:
1285: Log_Snap_Ref_status (lv_snapshot_name,
1278: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1279: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1280:
1281: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
1282: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
1283: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN --bug 8420469
1284:
1285: Log_Snap_Ref_status (lv_snapshot_name,
1286: lv_refresh_mode,
1279: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1280:
1281: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
1282: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
1283: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN --bug 8420469
1284:
1285: Log_Snap_Ref_status (lv_snapshot_name,
1286: lv_refresh_mode,
1287: 'ORA'||to_char(SQLCODE),
1286: lv_refresh_mode,
1287: 'ORA'||to_char(SQLCODE),
1288: lv_elapsed_mins);
1289:
1290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a Complete Refresh of the snapshot.');
1291: BEGIN
1292:
1293: lv_task_start_time := SYSDATE;
1294: if (v_database_version >= 10) then
1319: 'C',
1320: 'ORA'||to_char(SQLCODE),
1321: lv_elapsed_mins);
1322:
1323: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Complete Refresh of: '||
1324: lv_snapshot_name);
1325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1326: ROLLBACK;
1327: RETCODE := G_ERROR;
1321: lv_elapsed_mins);
1322:
1323: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Complete Refresh of: '||
1324: lv_snapshot_name);
1325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1326: ROLLBACK;
1327: RETCODE := G_ERROR;
1328: ERRBUF := SQLERRM;
1329: RETURN;
1331: ELSE
1332: ROLLBACK;
1333: RETCODE := G_ERROR;
1334: ERRBUF := SQLERRM;
1335: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshot : '||
1336: lv_snapshot_name || ' , Mode : ' ||
1337: lv_refresh_mode);
1338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1339: RETURN;
1334: ERRBUF := SQLERRM;
1335: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshot : '||
1336: lv_snapshot_name || ' , Mode : ' ||
1337: lv_refresh_mode);
1338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1339: RETURN;
1340: END IF;
1341: END;
1342: END LOOP;
1362:
1363: ROLLBACK;
1364: RETCODE := G_ERROR;
1365: ERRBUF := SQLERRM;
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : '||
1367: pSnapshot_List || ' , Mode : ' ||
1368: pRefresh_Param);
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1370: RETURN;
1365: ERRBUF := SQLERRM;
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : '||
1367: pSnapshot_List || ' , Mode : ' ||
1368: pRefresh_Param);
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1370: RETURN;
1371: END;
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1370: RETURN;
1371: END;
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
1375:
1376: RETCODE := G_SUCCESS;
1377: RETURN;
1370: RETURN;
1371: END;
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
1375:
1376: RETCODE := G_SUCCESS;
1377: RETURN;
1378:
1400: l_request_id number;
1401: l_canc_req_retval number;
1402:
1403: BEGIN
1404: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling pending/running snapshots');
1405: FOR j IN 1..p_req_id.COUNT LOOP
1406: l_request_id:=p_req_id(j);
1407:
1408: l_call_status := FND_CONCURRENT.GET_REQUEST_STATUS
1415: l_dev_status,
1416: l_message);
1417:
1418: IF l_dev_phase <> 'COMPLETE' THEN
1419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling request - ' || l_request_id);
1420: l_canc_req_retval := FND_AMP_PRIVATE.cancel_request (l_request_id, l_message);
1421: COMMIT;
1422:
1423: IF l_canc_req_retval = 0 THEN
1420: l_canc_req_retval := FND_AMP_PRIVATE.cancel_request (l_request_id, l_message);
1421: COMMIT;
1422:
1423: IF l_canc_req_retval = 0 THEN
1424: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling request, ' || l_request_id);
1425: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error - ' || l_message);
1426: END IF;
1427:
1428: END IF;
1421: COMMIT;
1422:
1423: IF l_canc_req_retval = 0 THEN
1424: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling request, ' || l_request_id);
1425: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error - ' || l_message);
1426: END IF;
1427:
1428: END IF;
1429:
1427:
1428: END IF;
1429:
1430: END LOOP;
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelled pending/running snapshots');
1432:
1433: EXCEPTION
1434: WHEN OTHERS THEN
1435: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling requests - ' || SQLERRM);
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelled pending/running snapshots');
1432:
1433: EXCEPTION
1434: WHEN OTHERS THEN
1435: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling requests - ' || SQLERRM);
1436: END cancel_submitted_requests;
1437:
1438:
1439:
1530: lv_cmro_closed_wo_flag;
1531:
1532: lv_snapshot_grp_str := '';
1533:
1534: IF (lv_po_flag = MSC_UTIL.SYS_YES) THEN /* Added lv_reserves_flag for Bug 6144734 */
1535: lv_snapshot_grp_str := lv_snapshot_grp_str || '''PO'',' ;
1536: END IF; -- lv_po_flag
1537:
1538: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1534: IF (lv_po_flag = MSC_UTIL.SYS_YES) THEN /* Added lv_reserves_flag for Bug 6144734 */
1535: lv_snapshot_grp_str := lv_snapshot_grp_str || '''PO'',' ;
1536: END IF; -- lv_po_flag
1537:
1538: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1539: lv_snapshot_grp_str := lv_snapshot_grp_str || '''USUD'',' ;
1540: END IF; -- lv_po_flag
1541:
1542: IF (lv_item_flag = MSC_UTIL.SYS_YES) THEN
1538: IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1539: lv_snapshot_grp_str := lv_snapshot_grp_str || '''USUD'',' ;
1540: END IF; -- lv_po_flag
1541:
1542: IF (lv_item_flag = MSC_UTIL.SYS_YES) THEN
1543: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ITEM'',' ;
1544: END IF; -- lv_item_flag
1545:
1546: -- SRP Changes
1543: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ITEM'',' ;
1544: END IF; -- lv_item_flag
1545:
1546: -- SRP Changes
1547: IF (lv_item_subs_falg = MSC_UTIL.SYS_YES) THEN
1548: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ISUB'',' ;
1549: END IF;
1550:
1551:
1548: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ISUB'',' ;
1549: END IF;
1550:
1551:
1552: IF (lv_oh_flag = MSC_UTIL.SYS_YES) THEN
1553: lv_snapshot_grp_str := lv_snapshot_grp_str || '''OH'',' ;
1554: END IF; -- lv_oh_flag
1555:
1556: IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1552: IF (lv_oh_flag = MSC_UTIL.SYS_YES) THEN
1553: lv_snapshot_grp_str := lv_snapshot_grp_str || '''OH'',' ;
1554: END IF; -- lv_oh_flag
1555:
1556: IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1557: lv_snapshot_grp_str := lv_snapshot_grp_str || '''RES'',' ;
1558: END IF; -- lv_reserves_flag
1559:
1560: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1556: IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1557: lv_snapshot_grp_str := lv_snapshot_grp_str || '''RES'',' ;
1558: END IF; -- lv_reserves_flag
1559:
1560: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1561: lv_snapshot_grp_str := lv_snapshot_grp_str || '''BOM'',' ;
1562: END IF; -- lv_bom_flag
1563:
1564: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1560: IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1561: lv_snapshot_grp_str := lv_snapshot_grp_str || '''BOM'',' ;
1562: END IF; -- lv_bom_flag
1563:
1564: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1565: lv_snapshot_grp_str := lv_snapshot_grp_str || '''MRP'',' ;
1566: END IF; -- lv_mps_flag or lv_mds_flag
1567:
1568: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1564: IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1565: lv_snapshot_grp_str := lv_snapshot_grp_str || '''MRP'',' ;
1566: END IF; -- lv_mps_flag or lv_mds_flag
1567:
1568: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1569: lv_snapshot_grp_str := lv_snapshot_grp_str || '''FCST'',' ;
1570: END IF; -- lv_forecast_flag
1571:
1572: 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
1568: IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1569: lv_snapshot_grp_str := lv_snapshot_grp_str || '''FCST'',' ;
1570: END IF; -- lv_forecast_flag
1571:
1572: 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
1573: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WIP'',''EAM'',''AHLSCH'',' ;
1574:
1575: IF (lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN /* For Bug 5937835 */
1576: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ERO'',' ;
1571:
1572: 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
1573: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WIP'',''EAM'',''AHLSCH'',' ;
1574:
1575: IF (lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN /* For Bug 5937835 */
1576: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ERO'',' ;
1577: END IF ;
1578:
1579: END IF; -- lv_wip_flag
1577: END IF ;
1578:
1579: END IF; -- lv_wip_flag
1580:
1581: IF (lv_supplier_cap_flag = MSC_UTIL.SYS_YES or lv_supplier_cap_flag = ASL_YES_RETAIN_CP) THEN
1582: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SCAP'',' ;
1583: END IF; -- lv_supplier_cap_flag
1584:
1585: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1581: IF (lv_supplier_cap_flag = MSC_UTIL.SYS_YES or lv_supplier_cap_flag = ASL_YES_RETAIN_CP) THEN
1582: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SCAP'',' ;
1583: END IF; -- lv_supplier_cap_flag
1584:
1585: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1586: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SRSP'',' ;
1587: END IF; -- lv_supplier_resp_flag
1588:
1589: 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 */
1585: IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1586: lv_snapshot_grp_str := lv_snapshot_grp_str || '''SRSP'',' ;
1587: END IF; -- lv_supplier_resp_flag
1588:
1589: 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 */
1590: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ONT'',''AHLSCH'',' ;
1591: END IF; -- lv_reserves_flag or lv_sales_order_flag -- or Incremental
1592:
1593: IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1589: 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 */
1590: lv_snapshot_grp_str := lv_snapshot_grp_str || '''ONT'',''AHLSCH'',' ;
1591: END IF; -- lv_reserves_flag or lv_sales_order_flag -- or Incremental
1592:
1593: IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1594: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WSH'',' ;
1595: END IF; -- lv_trip_flag
1596:
1597: IF (lv_notes_flag = MSC_UTIL.SYS_YES) THEN
1593: IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1594: lv_snapshot_grp_str := lv_snapshot_grp_str || '''WSH'',' ;
1595: END IF; -- lv_trip_flag
1596:
1597: IF (lv_notes_flag = MSC_UTIL.SYS_YES) THEN
1598: lv_snapshot_grp_str := lv_snapshot_grp_str || '''NOTES'',' ;
1599: END IF; -- lv_notes_flag
1600:
1601: IF (lv_cmro_flag = MSC_UTIL.SYS_YES OR lv_cmro_closed_wo_flag = MSC_UTIL.SYS_YES) THEN
1597: IF (lv_notes_flag = MSC_UTIL.SYS_YES) THEN
1598: lv_snapshot_grp_str := lv_snapshot_grp_str || '''NOTES'',' ;
1599: END IF; -- lv_notes_flag
1600:
1601: IF (lv_cmro_flag = MSC_UTIL.SYS_YES OR lv_cmro_closed_wo_flag = MSC_UTIL.SYS_YES) THEN
1602: lv_snapshot_grp_str := lv_snapshot_grp_str || '''CMRO'',' ;
1603: END IF; -- lv_cmro_flag
1604:
1605: IF (lv_eam_flag = MSC_UTIL.SYS_YES) THEN
1601: IF (lv_cmro_flag = MSC_UTIL.SYS_YES OR lv_cmro_closed_wo_flag = MSC_UTIL.SYS_YES) THEN
1602: lv_snapshot_grp_str := lv_snapshot_grp_str || '''CMRO'',' ;
1603: END IF; -- lv_cmro_flag
1604:
1605: IF (lv_eam_flag = MSC_UTIL.SYS_YES) THEN
1606: lv_snapshot_grp_str := lv_snapshot_grp_str || '''EAM'',' ;
1607: END IF; -- lv_eam_flag
1608:
1609: IF lv_snapshot_grp_str = '' or lv_snapshot_grp_str is NULL THEN
1609: IF lv_snapshot_grp_str = '' or lv_snapshot_grp_str is NULL THEN
1610: NULL;
1611: ELSE
1612: lv_snapshot_grp_str := '(' || substr(lv_snapshot_grp_str,1,length(lv_snapshot_grp_str) -1 ) || ' ) ' ;
1613: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_grp_str);
1614: END IF;
1615: ELSE
1616:
1617: lv_snapshot_grp_str := '(''' ||pSnapshot_str || ''')';
1628: begin
1629: if exp = '1' then
1630: val := 1;
1631: else
1632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Evaluating -- ' || exp);
1633: begin
1634: execute immediate ' select ' || exp || ' from dual ' into val;
1635: exception when others then
1636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Evaluating -- ' || exp);
1633: begin
1634: execute immediate ' select ' || exp || ' from dual ' into val;
1635: exception when others then
1636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1637: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1638: val :=0;
1639: end;
1640: end if;
1633: begin
1634: execute immediate ' select ' || exp || ' from dual ' into val;
1635: exception when others then
1636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1637: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1638: val :=0;
1639: end;
1640: end if;
1641: if val =1 then
1672: lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink
1673: ||'(''MSC_ASCP_IGNORE_CMRO_EAM_WO''),1)'
1674: || ' from dual ';
1675:
1676: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
1677: execute immediate lv_sql_stmt into lv_usaf_prof_flag ;
1678:
1679: IF lv_usaf_prof_flag = 1 THEN
1680: return 0;
1731: lv_cusros_str varchar2(500);
1732:
1733: BEGIN
1734:
1735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Start: split_refresh');
1736:
1737: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
1738:
1739: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1736:
1737: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
1738:
1739: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
1741: ELSE
1742:
1743: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1744:
1739: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
1741: ELSE
1742:
1743: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1744:
1745: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1747:
1742:
1743: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1744:
1745: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1747:
1748: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1750:
1744:
1745: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1747:
1748: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1750:
1751: lv_cusros_str := ' select mview_name, existance_check
1752: from msc_coll_snapshots_v
1745: lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1747:
1748: lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1750:
1751: lv_cusros_str := ' select mview_name, existance_check
1752: from msc_coll_snapshots_v
1753: where mview_name in ' || lv_snapshot_groups_str || '
1784:
1785: COMMIT;
1786:
1787: IF v_request_id = 0 THEN
1788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in launching program to refresh snapshot, ' || lv_snapshot_name);
1789: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error message - ' || SQLERRM);
1790: cancel_submitted_requests(lv_req_id);
1791: close c_snap;
1792: RETURN FALSE;
1785: COMMIT;
1786:
1787: IF v_request_id = 0 THEN
1788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in launching program to refresh snapshot, ' || lv_snapshot_name);
1789: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error message - ' || SQLERRM);
1790: cancel_submitted_requests(lv_req_id);
1791: close c_snap;
1792: RETURN FALSE;
1793: ELSE
1791: close c_snap;
1792: RETURN FALSE;
1793: ELSE
1794: lv_req_id(lv_num_of_snap) := v_request_id;
1795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted request ' || lv_req_id(i) || ', to refresh snapshot: ' || lv_snapshot_name);
1796: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
1797: END IF;
1798:
1799: EXIT WHEN v_request_id = 0;
1792: RETURN FALSE;
1793: ELSE
1794: lv_req_id(lv_num_of_snap) := v_request_id;
1795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted request ' || lv_req_id(i) || ', to refresh snapshot: ' || lv_snapshot_name);
1796: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
1797: END IF;
1798:
1799: EXIT WHEN v_request_id = 0;
1800: END IF; -- g_REFRESH_TYPE
1813: wait_for_request(lv_req_id(j), 30, lv_out);
1814:
1815: IF lv_out = 2 THEN
1816: lv_failed_req_id := lv_req_id(j);
1817: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR : Please see the log files of request, ' || lv_failed_req_id || ', for details');
1818: cancel_submitted_requests(lv_req_id);
1819:
1820: RETURN FALSE;
1821: END IF;
1821: END IF;
1822:
1823: END LOOP;
1824:
1825: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed all snapshots');
1826: END IF; -- g_REFRESH_TYPE
1827: END IF;
1828: RETURN TRUE;
1829:
1829:
1830: EXCEPTION
1831: WHEN OTHERS THEN
1832: ROLLBACK;
1833: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : ' || SQLERRM);
1834:
1835: IF lv_req_id.COUNT > 0 THEN
1836: cancel_submitted_requests(lv_req_id);
1837: END IF;
1941: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
1942: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
1943: EXCEPTION
1944: WHEN OTHERS THEN
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
1947: v_database_version := 9;
1948: --lv_db_version := v_version_9i;
1949: --RAISE;
1942: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
1943: EXCEPTION
1944: WHEN OTHERS THEN
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
1947: v_database_version := 9;
1948: --lv_db_version := v_version_9i;
1949: --RAISE;
1950: END;
1957: v_cp_enabled := SYS_YES;
1958: v_refresh_type := pREFRESH_TYPE;
1959: lv_DEGREE := LEAST(lv_DEGREE,10);
1960:
1961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The degree of parallelism for Refreshing snapshots is set to: ' || lv_DEGREE); --8761596
1964: --dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1965:
1958: v_refresh_type := pREFRESH_TYPE;
1959: lv_DEGREE := LEAST(lv_DEGREE,10);
1960:
1961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The degree of parallelism for Refreshing snapshots is set to: ' || lv_DEGREE); --8761596
1964: --dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1965:
1966: SELECT MRP_AP_REFRESH_S.NEXTVAL
1959: lv_DEGREE := LEAST(lv_DEGREE,10);
1960:
1961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The degree of parallelism for Refreshing snapshots is set to: ' || lv_DEGREE); --8761596
1964: --dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1965:
1966: SELECT MRP_AP_REFRESH_S.NEXTVAL
1967: INTO v_refresh_number
2012: EXECUTE IMMEDIATE lv_sql_stmt
2013: INTO v_apps_lrn, v_last_IBUC_coll_date ;
2014: lv_sql_stmt:= 'select FND_PROFILE.VALUE'||v_dblink||'(''MSC_COLL_TIME_WINDOW_IBUC_HISTORY'') '
2015: || ' from dual ';
2016: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
2017: execute immediate lv_sql_stmt into v_window ;
2018: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Value of the Time Window Profile ' ||v_window);
2019:
2020: lv_sql_stmt := ' SELECT nvl(min(so_lrn),'||to_char(v_apps_lrn)||')'
2014: lv_sql_stmt:= 'select FND_PROFILE.VALUE'||v_dblink||'(''MSC_COLL_TIME_WINDOW_IBUC_HISTORY'') '
2015: || ' from dual ';
2016: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
2017: execute immediate lv_sql_stmt into v_window ;
2018: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Value of the Time Window Profile ' ||v_window);
2019:
2020: lv_sql_stmt := ' SELECT nvl(min(so_lrn),'||to_char(v_apps_lrn)||')'
2021: ||' FROM msc_instance_orgs'||v_dblink
2022: ||' WHERE sr_instance_id = '||pINSTANCE_ID;
2037: IF (lv_setup_source_objs = 1) THEN
2038: IF SETUP_SOURCE_OBJECTS = FALSE THEN
2039: RAISE SOURCE_SETUP_ERROR;
2040: ELSE
2041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source set up completed successfully');
2042: END IF;
2043: END IF;
2044:
2045:
2065:
2066: END;
2067: */
2068:
2069: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before update of mrp_ap_apps_instances_all');
2070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_name is '||lv_snapshot_name);
2071: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pCALLING_MODULE is '|| pCALLING_MODULE);
2072:
2073: IF (( lv_snapshot_name = 'COLL SNAPSHOTS' ) and
2066: END;
2067: */
2068:
2069: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before update of mrp_ap_apps_instances_all');
2070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_name is '||lv_snapshot_name);
2071: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pCALLING_MODULE is '|| pCALLING_MODULE);
2072:
2073: IF (( lv_snapshot_name = 'COLL SNAPSHOTS' ) and
2074: ( pCALLING_MODULE = G_COLLECTIONS)) THEN
2067: */
2068:
2069: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before update of mrp_ap_apps_instances_all');
2070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_name is '||lv_snapshot_name);
2071: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'pCALLING_MODULE is '|| pCALLING_MODULE);
2072:
2073: IF (( lv_snapshot_name = 'COLL SNAPSHOTS' ) and
2074: ( pCALLING_MODULE = G_COLLECTIONS)) THEN
2075: -- If all the Snapshots set RESOURCE START TIME
2089:
2090:
2091: EXECUTE IMMEDIATE lv_sql_stmt2 INTO lv_ibuc_history_flag;
2092:
2093: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2094:
2095: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'IBUC HISTORY flag'||lv_ibuc_history_flag );
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'REFRESH TYPE '||v_refresh_type );
2097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'LAST IBUC COLL DATE'||v_last_IBUC_coll_date );
2091: EXECUTE IMMEDIATE lv_sql_stmt2 INTO lv_ibuc_history_flag;
2092:
2093: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2094:
2095: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'IBUC HISTORY flag'||lv_ibuc_history_flag );
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'REFRESH TYPE '||v_refresh_type );
2097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'LAST IBUC COLL DATE'||v_last_IBUC_coll_date );
2098:
2099: v_ibuc_request_id := MRP_CL_FUNCTION.IB_CONTRACTS( v_refresh_type , v_last_IBUC_coll_date ,v_window );
2092:
2093: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2094:
2095: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'IBUC HISTORY flag'||lv_ibuc_history_flag );
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'REFRESH TYPE '||v_refresh_type );
2097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'LAST IBUC COLL DATE'||v_last_IBUC_coll_date );
2098:
2099: v_ibuc_request_id := MRP_CL_FUNCTION.IB_CONTRACTS( v_refresh_type , v_last_IBUC_coll_date ,v_window );
2100:
2093: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2094:
2095: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'IBUC HISTORY flag'||lv_ibuc_history_flag );
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'REFRESH TYPE '||v_refresh_type );
2097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'LAST IBUC COLL DATE'||v_last_IBUC_coll_date );
2098:
2099: v_ibuc_request_id := MRP_CL_FUNCTION.IB_CONTRACTS( v_refresh_type , v_last_IBUC_coll_date ,v_window );
2100:
2101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted Request '||v_ibuc_request_id ||
2097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'LAST IBUC COLL DATE'||v_last_IBUC_coll_date );
2098:
2099: v_ibuc_request_id := MRP_CL_FUNCTION.IB_CONTRACTS( v_refresh_type , v_last_IBUC_coll_date ,v_window );
2100:
2101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted Request '||v_ibuc_request_id ||
2102: 'For calculating the IBUC history');
2103: COMMIT ;
2104:
2105: END IF ;
2108:
2109: BEGIN
2110: IF split_refresh (lv_refresh_mode, pSNAPSHOT_NAME) = TRUE THEN
2111: IF (pREFRESH_TYPE <> 'I') THEN
2112: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
2113: ELSE
2114: -- For complete/targeted collection, we need to
2115: -- incement the sequence once again so that the refresh of
2116: -- of all snapshots together gets a higher sequence number
2121: FROM DUAL;
2122:
2123:
2124: END IF;
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2122:
2123:
2124: END IF;
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2123:
2124: END IF;
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2131:
2124: END IF;
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2131:
2132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2131:
2132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
2133: ELSE
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
2127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2131:
2132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
2133: ELSE
2134: ROLLBACK;
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
2129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
2130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
2131:
2132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
2133: ELSE
2134: ROLLBACK;
2135: RETCODE:= G_ERROR;
2136: ERRBUF:= SQLERRM;
2137: RAISE INDIVIDUAL_REFRESH_ERROR;
2138: END IF;
2139:
2140: /*IBUC*/
2141: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2142: wait_for_request(v_ibuc_request_id ,30, lv_out_ibuc);
2143: if lv_out_ibuc = 2 THEN
2144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request '||v_ibuc_request_id || ' For calculating the IBUC history is failed');
2145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message');
2140: /*IBUC*/
2141: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2142: wait_for_request(v_ibuc_request_id ,30, lv_out_ibuc);
2143: if lv_out_ibuc = 2 THEN
2144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request '||v_ibuc_request_id || ' For calculating the IBUC history is failed');
2145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message');
2146: RETCODE:= G_ERROR;
2147: RETURN;
2148: end if;
2141: IF lv_ibuc_history_flag = MSC_UTIL.SYS_YES THEN
2142: wait_for_request(v_ibuc_request_id ,30, lv_out_ibuc);
2143: if lv_out_ibuc = 2 THEN
2144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request '||v_ibuc_request_id || ' For calculating the IBUC history is failed');
2145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message');
2146: RETCODE:= G_ERROR;
2147: RETURN;
2148: end if;
2149: END IF;
2150:
2151: --IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
2152: IF ( pCALLING_MODULE = G_COLLECTIONS ) THEN
2153:
2154: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a fast refresh of all snapshots...');
2155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a force refresh of all snapshots...');
2156: BEGIN
2157:
2158: -- Bug 9449340 do force fresh,if fast is available,fast refresh,if not complete refresh.
2151: --IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
2152: IF ( pCALLING_MODULE = G_COLLECTIONS ) THEN
2153:
2154: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a fast refresh of all snapshots...');
2155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a force refresh of all snapshots...');
2156: BEGIN
2157:
2158: -- Bug 9449340 do force fresh,if fast is available,fast refresh,if not complete refresh.
2159: lv_refresh_param := replace(lv_refresh_param, 'F', '?');
2157:
2158: -- Bug 9449340 do force fresh,if fast is available,fast refresh,if not complete refresh.
2159: lv_refresh_param := replace(lv_refresh_param, 'F', '?');
2160:
2161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_str: '
2162: || lv_snapshot_str);
2163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_refresh_param: '
2164: || lv_refresh_param);
2165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_DEGREE: '
2159: lv_refresh_param := replace(lv_refresh_param, 'F', '?');
2160:
2161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_str: '
2162: || lv_snapshot_str);
2163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_refresh_param: '
2164: || lv_refresh_param);
2165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_DEGREE: '
2166: || to_char(lv_DEGREE));
2167: DBMS_MVIEW.REFRESH ( lv_snapshot_str,lv_refresh_param,parallelism =>lv_DEGREE,atomic_refresh => TRUE);
2161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snapshot_str: '
2162: || lv_snapshot_str);
2163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_refresh_param: '
2164: || lv_refresh_param);
2165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_DEGREE: '
2166: || to_char(lv_DEGREE));
2167: DBMS_MVIEW.REFRESH ( lv_snapshot_str,lv_refresh_param,parallelism =>lv_DEGREE,atomic_refresh => TRUE);
2168: COMMIT;
2169:
2182: || ' SET complete_refresh_timestamp = to_char(sysdate,''YYYY-MM-DD HH24:MI:SS'') '
2183: || ' WHERE MVIEW_NAME in ( '
2184: || ' SELECT mview_name from all_mviews WHERE mview_name in (' || lv_snapshot_str_tmp || ') '
2185: || ' and last_refresh_type= ''COMPLETE'' AND owner = '
2186: || '''' || MSC_UTIL.G_APPS_SCHEMA || '''' || ')' ;
2187:
2188: EXECUTE IMMEDIATE lv_update_cr_stmt;
2189: COMMIT;
2190:
2187:
2188: EXECUTE IMMEDIATE lv_update_cr_stmt;
2189: COMMIT;
2190:
2191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Done with update complete refresh MViews timestamp');
2192:
2193: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Done with refresh.');
2194:
2195: RETCODE := G_SUCCESS;
2189: COMMIT;
2190:
2191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Done with update complete refresh MViews timestamp');
2192:
2193: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Done with refresh.');
2194:
2195: RETCODE := G_SUCCESS;
2196:
2197: EXCEPTION
2195: RETCODE := G_SUCCESS;
2196:
2197: EXCEPTION
2198: WHEN OTHERS THEN
2199: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
2200: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2201: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2196:
2197: EXCEPTION
2198: WHEN OTHERS THEN
2199: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
2200: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2201: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2197: EXCEPTION
2198: WHEN OTHERS THEN
2199: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
2200: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2201: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2205: END IF;
2198: WHEN OTHERS THEN
2199: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
2200: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2201: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2199: MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
2200: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2201: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2207: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2207: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2208: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
2210: handle_ORA_12034 (ERRBUF,
2203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2204: RAISE;
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2207: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2208: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
2210: handle_ORA_12034 (ERRBUF,
2211: RETCODE,
2204: RAISE;
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2207: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2208: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
2210: handle_ORA_12034 (ERRBUF,
2211: RETCODE,
2212: lv_snapshot_str,
2205: END IF;
2206: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2207: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2208: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
2210: handle_ORA_12034 (ERRBUF,
2211: RETCODE,
2212: lv_snapshot_str,
2213: lv_refresh_param,
2231: WHEN OTHERS THEN
2232: ROLLBACK;
2233: RETCODE:= G_ERROR;
2234: ERRBUF:= SQLERRM;
2235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2236: RAISE;
2237: END;
2238:
2239: /*
2240: removing the truncation of table --- BUG 12601292
2241:
2242: IF lv_initialization_flag = 1 THEN
2243:
2244: lv_sql_stmt:= 'TRUNCATE TABLE '|| MSC_UTIL.G_MRP_SCHEMA||'.MRP_AD_SUPPLY';
2245: EXECUTE IMMEDIATE lv_sql_stmt;
2246:
2247: END IF;
2248: */
2279: EXCEPTION
2280: WHEN NO_DATA_FOUND THEN
2281: RETCODE:= G_ERROR;
2282: ERRBUF := 'Please verify Setup DBLinks setup in Source Database';
2283: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2284: WHEN OTHERS THEN
2285: RETCODE:= G_ERROR;
2286: ERRBUF:= SQLERRM;
2287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2283: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2284: WHEN OTHERS THEN
2285: RETCODE:= G_ERROR;
2286: ERRBUF:= SQLERRM;
2287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2288: RAISE;
2289: END;
2290:
2291: IF (pREFRESH_TYPE = 'T' ) THEN
2317: FROM fnd_application
2318: WHERE application_id=714;
2319:
2320: IF (CHECK_INSTALL(lv_flm_appl_short_name)) THEN
2321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Populating Flow Demand, Complete Refresh Flow: ' ||
2322: to_char (lv_complete_ref_flow));
2323:
2324: lv_task_start_time := SYSDATE;
2325:
2334: lv_wfd_err_msg);
2335: END IF;
2336:
2337: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2339:
2340: EXCEPTION
2341: WHEN OTHERS THEN
2342: NULL;
2352: NULL;
2353: ELSE
2354: IF (v_explode_ato = 'Y') THEN
2355: -- explode ATO only if the profile is YES
2356: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding SMC demand, LRN: ' || to_char (v_lrn));
2357:
2358: lv_task_start_time := SYSDATE;
2359:
2360: lv_standard_ret :=
2362:
2363: lv_elapsed_mins :=
2364: CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2365:
2366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2367:
2368: IF lv_standard_ret = 2 THEN
2369: RAISE CONFIG_BOM_NOT_FOUND;
2370: END IF;
2375: EXCEPTION
2376: WHEN CONFIG_BOM_NOT_FOUND THEN
2377: RETCODE:= G_WARNING;
2378: ERRBUF := 'Please check the warning message in the logfile';
2379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2380: WHEN OTHERS THEN
2381: NULL;
2382: END;
2383: COMMIT;
2398: end if;
2399: end loop;
2400: EXCEPTION
2401: WHEN OTHERS THEN
2402: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' An error occured in building the item name from KFV');
2403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2404: lv_item_name_kfv := 'x.SEGMENT1';
2405: END;
2406:
2399: end loop;
2400: EXCEPTION
2401: WHEN OTHERS THEN
2402: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' An error occured in building the item name from KFV');
2403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2404: lv_item_name_kfv := 'x.SEGMENT1';
2405: END;
2406:
2407:
2483: Exception
2484: When Others Then
2485: lv_map_region_during_coll := 1;
2486: End;
2487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Calling Module: '|| pCALLING_MODULE);
2488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Map Region Site during collection : '
2489: || lv_map_region_during_coll );
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Collection refresh type: '
2491: || pREFRESH_TYPE);
2484: When Others Then
2485: lv_map_region_during_coll := 1;
2486: End;
2487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Calling Module: '|| pCALLING_MODULE);
2488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Map Region Site during collection : '
2489: || lv_map_region_during_coll );
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Collection refresh type: '
2491: || pREFRESH_TYPE);
2492: IF (pCALLING_MODULE = G_COLLECTIONS) THEN
2486: End;
2487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Calling Module: '|| pCALLING_MODULE);
2488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Map Region Site during collection : '
2489: || lv_map_region_during_coll );
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Collection refresh type: '
2491: || pREFRESH_TYPE);
2492: IF (pCALLING_MODULE = G_COLLECTIONS) THEN
2493: Begin
2494:
2514: Exception
2515: WHEN OTHERS THEN
2516: lv_sourcing_flag := 0;
2517: End;
2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of lv_sourcing_flag:'
2519: ||lv_sourcing_flag);
2520: END IF; --- pCALLING_MODULE
2521:
2522: /* Before calling the function, check if sourcing actually is required */
2528: AND lv_sourcing_flag = 1 )
2529: OR
2530: ( pCALLING_MODULE = G_MANUAL
2531: AND lv_map_region_during_coll = SYS_NO )) THEN
2532: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2533: 'before calling MRP_MAP_REG_SITE.MAP_REGION_TO_SITE');
2534:
2535: SELECT max(last_update_date)
2536: INTO max_lrd
2539: /* msx_lrd may have null value or a valid value */
2540:
2541: lv_task_start_time := SYSDATE;
2542:
2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2544: 'Calling function MAP_REGION_TO_SITE with max_lrd:'
2545: ||max_lrd);
2546:
2547: IF MRP_CL_FUNCTION.MAP_REGION_TO_SITE(max_lrd)=1 THEN NULL; END IF;
2546:
2547: IF MRP_CL_FUNCTION.MAP_REGION_TO_SITE(max_lrd)=1 THEN NULL; END IF;
2548:
2549: lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2551: 'Time consumed for calling map_region_to_site:'
2552: || lv_elapsed_mins);
2553:
2554: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2551: 'Time consumed for calling map_region_to_site:'
2552: || lv_elapsed_mins);
2553:
2554: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2555: 'after calling MRP_MAP_REG_SITE.MAP_REGION_TO_SITE');
2556:
2557: END IF;
2558:
2569: 'BEGIN'
2570: ||' :lv_out := MRP_CL_FUNCTION.LONG_TEXT '||';'
2571: ||'END;';
2572:
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before calling mrpclhab' ||lv_temp_stmt);
2574: EXECUTE IMMEDIATE lv_temp_stmt
2575: USING OUT lv_out;
2576:
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Status returned ' ||lv_out);
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before calling mrpclhab' ||lv_temp_stmt);
2574: EXECUTE IMMEDIATE lv_temp_stmt
2575: USING OUT lv_out;
2576:
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Status returned ' ||lv_out);
2578:
2579: END IF;
2580:
2581: /*****Adddition of function(LONG_TEXT) for bug 12359111 by ngaddamp ends here /
2589: BEGIN
2590: lv_sql_stmt := ' SELECT cmro '
2591: ||' FROM msc_coll_parameters'||v_dblink
2592: ||' WHERE instance_id = :v_INSTANCE_ID';
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'pINSTANCE_ID='||pINSTANCE_ID||' pINSTANCE_code='||pINSTANCE_code||' lv_sql_stmt for wo_sub_comp-'||lv_sql_stmt);
2594: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_cmro_flag using nvl(g_INSTANCE_ID,0);
2595: EXCEPTION
2596: WHEN OTHERS THEN
2597: lv_cmro_flag := 0;
2597: lv_cmro_flag := 0;
2598: END;
2599:
2600: IF lv_cmro_flag =1 THEN
2601: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2602: 'Calling function POPULATE_WO_SUB_COMP');
2603:
2604:
2605: IF MRP_CL_FUNCTION.POPULATE_WO_SUB_COMP = 1 THEN NULL; END IF;
2603:
2604:
2605: IF MRP_CL_FUNCTION.POPULATE_WO_SUB_COMP = 1 THEN NULL; END IF;
2606:
2607: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2608: 'after calling POPULATE_WO_SUB_COMP');
2609: END IF;
2610: END IF;
2611:
2613:
2614:
2615: RETCODE:= G_SUCCESS;
2616: ERRBUF:= null;
2617: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refresh Snapshot process completed successfully');
2618: return;
2619:
2620: EXCEPTION
2621: WHEN SOURCE_SETUP_ERROR THEN
2618: return;
2619:
2620: EXCEPTION
2621: WHEN SOURCE_SETUP_ERROR THEN
2622: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Setting Up Source Objects');
2623: RETCODE:= G_ERROR;
2624:
2625: ERRBUF:= SQLERRM;
2626:
2624:
2625: ERRBUF:= SQLERRM;
2626:
2627: WHEN INDIVIDUAL_REFRESH_ERROR THEN
2628: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2629: RETCODE:= G_ERROR;
2630: ERRBUF:= SQLERRM;
2631:
2632: WHEN TRUNCATE_LOG_ERROR THEN
2631:
2632: WHEN TRUNCATE_LOG_ERROR THEN
2633: RETCODE:= G_ERROR;
2634: ERRBUF:= SQLERRM;
2635: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2636:
2637: WHEN OTHERS THEN
2638:
2639: ROLLBACK;
2641: RETCODE:= G_ERROR;
2642:
2643: ERRBUF:= SQLERRM;
2644:
2645: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2646:
2647: END REFRESH_SNAPSHOT;
2648:
2649: FUNCTION GET_REFRESH_TYPE ( p_base_schema IN VARCHAR2,
2692: IF p_snapshot_name = 'FND_DOCS_LONG_TEXT_SN' THEN
2693: lv_refresh_type := 'C';
2694: END IF;
2695:
2696: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Optimal refresh type found:' ||lv_refresh_type );
2697: return lv_refresh_type;
2698:
2699: END GET_REFRESH_TYPE;
2700:
2719:
2720: lv_ref_num NUMBER;
2721: lv_refresh_param VARCHAR2(1);
2722: lv_snapshot_name VARCHAR2(100) := pSNAPSHOT_NAME;
2723: lv_snap_str VARCHAR2(150) := MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2724: BEGIN
2725:
2726: BEGIN
2727: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
2726: BEGIN
2727: DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
2728: v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
2729:
2730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Database Version: ' || lv_db_version);
2731:
2732: EXCEPTION
2733: WHEN OTHERS THEN
2734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Database Version: ' || lv_db_version);
2731:
2732: EXCEPTION
2733: WHEN OTHERS THEN
2734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
2736: v_database_version := 9;
2737: --lv_db_version := v_version_9i;
2738: --RAISE;
2731:
2732: EXCEPTION
2733: WHEN OTHERS THEN
2734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
2736: v_database_version := 9;
2737: --lv_db_version := v_version_9i;
2738: --RAISE;
2739: END;
2755: v_refresh_number := pCURRENT_LRN;
2756: END IF;
2757:
2758: IF (pREFRESH_MODE = G_REFRESH_MODE_FAST) THEN -- fast
2759: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, fast');
2760: lv_refresh_param := 'F';
2761: IF lv_snapshot_name = 'FND_DOCS_LONG_TEXT_SN' THEN
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'FND_DOCS_LONG_TEXT_SN snapshot will be refreshed in Complete Mode');
2763: lv_refresh_param := 'C';
2758: IF (pREFRESH_MODE = G_REFRESH_MODE_FAST) THEN -- fast
2759: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, fast');
2760: lv_refresh_param := 'F';
2761: IF lv_snapshot_name = 'FND_DOCS_LONG_TEXT_SN' THEN
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'FND_DOCS_LONG_TEXT_SN snapshot will be refreshed in Complete Mode');
2763: lv_refresh_param := 'C';
2764: END IF;
2765:
2766: -- BUG 12601292
2783: END;
2784: IF (lv_initialization_flag = 1 ) THEN
2785: lv_refresh_param := 'C';
2786:
2787: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2788: 'Doing a complete refresh of mrp_sn_supply.');
2789: END IF;
2790: END IF;
2791:
2789: END IF;
2790: END IF;
2791:
2792: ELSIF (pREFRESH_MODE = G_REFRESH_MODE_COMPLETE) THEN --complete
2793: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, complete');
2794: lv_refresh_param := 'C';
2795:
2796: ELSIF (pREFRESH_MODE = G_REFRESH_MODE_FORCE) THEN --Force ,code insert for bug 14006179
2797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, Force');
2793: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, complete');
2794: lv_refresh_param := 'C';
2795:
2796: ELSIF (pREFRESH_MODE = G_REFRESH_MODE_FORCE) THEN --Force ,code insert for bug 14006179
2797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, Force');
2798: lv_refresh_param := '?';
2799:
2800: ELSE -- automatic or force
2801: SELECT master_table, erp_product_code
2802: INTO lv_base_table_name,lv_erp_product_code
2803: FROM MSC_COLL_SNAPSHOTS_V
2804: WHERE mview_name = lv_snapshot_name;
2805:
2806: lv_base_schema := MSC_UTIL.GET_SCHEMA_NAME(lv_erp_product_code);
2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Master-Table = '|| lv_base_schema || '.'|| lv_base_table_name);
2809:
2810: --If logs are truncated, do Complete refresh, else let system decide best refresh method
2804: WHERE mview_name = lv_snapshot_name;
2805:
2806: lv_base_schema := MSC_UTIL.GET_SCHEMA_NAME(lv_erp_product_code);
2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Master-Table = '|| lv_base_schema || '.'|| lv_base_table_name);
2809:
2810: --If logs are truncated, do Complete refresh, else let system decide best refresh method
2811: IF pREFRESH_MODE = G_REFRESH_MODE_AUTOMATIC AND TRUNC_SNAP_LOG(p_NUMBER_OF_ROWS,lv_base_schema,
2812: lv_base_table_name,lv_snapshot_name,pDEGREE) THEN
2817: END IF; -- pREFRESH_MODE
2818:
2819: --Refreshing the snapshot
2820: BEGIN
2821: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Refreshing the snapshot : ' || lv_snap_str || ' in ' || lv_refresh_param || ' mode with degree ' || pDEGREE);
2822: if (v_database_version >= 10) then
2823: DBMS_MVIEW.REFRESH(LIST => lv_snap_str,
2824: METHOD => lv_refresh_param,
2825: atomic_refresh => FALSE,
2831:
2832: end if;
2833: /*
2834: select LAST_REFRESH_TYPE into lv_last_refresh_type from dba_Mviews where owner||'.'||mview_name=lv_snap_str;
2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_last_refresh_type in round1 is'|| lv_last_refresh_type || 'Materialized VIew name is : ' || lv_snap_str);
2836: */
2837: EXCEPTION
2838: WHEN OTHERS THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_last_refresh_type in round1 is'|| lv_last_refresh_type || 'Materialized VIew name is : ' || lv_snap_str);
2836: */
2837: EXCEPTION
2838: WHEN OTHERS THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2836: */
2837: EXCEPTION
2838: WHEN OTHERS THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2837: EXCEPTION
2838: WHEN OTHERS THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2838: WHEN OTHERS THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...' );
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_STACK );
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2848: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...' );
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2848: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2849: RAISE;
2842: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2848: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2849: RAISE;
2850: END IF;
2843: --MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2848: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2849: RAISE;
2850: END IF;
2851:
2844: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2845: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2846: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2848: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2849: RAISE;
2850: END IF;
2851:
2852:
2849: RAISE;
2850: END IF;
2851:
2852:
2853: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2854: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2855: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2856: handle_ORA_12034 (ERRBUF,
2857: RETCODE,
2850: END IF;
2851:
2852:
2853: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2854: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2855: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2856: handle_ORA_12034 (ERRBUF,
2857: RETCODE,
2858: lv_snap_str,
2851:
2852:
2853: IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0
2854: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12052') > 0
2855: OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12057') > 0 THEN
2856: handle_ORA_12034 (ERRBUF,
2857: RETCODE,
2858: lv_snap_str,
2859: lv_refresh_param,
2876: EXECUTE IMMEDIATE ' SELECT DECODE(last_refresh_type,''COMPLETE'',''C'', ''F'') '
2877: || ' FROM all_mviews WHERE mview_name = :lv_snapshot_name '
2878: || ' AND owner = :lv_snap_schema '
2879: INTO lv_refresh_param -- overwrite existing value with the actual refresh done...
2880: USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
2881:
2882: END IF;
2883:
2884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2880: USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
2881:
2882: END IF;
2883:
2884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2886: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2887:
2888:
2881:
2882: END IF;
2883:
2884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2886: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2887:
2888:
2889: IF lv_refresh_param = 'C' THEN
2882: END IF;
2883:
2884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2886: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2887:
2888:
2889: IF lv_refresh_param = 'C' THEN
2890: UPDATE MSC_COLL_SNAPSHOTS_V
2891: SET complete_refresh_timestamp = to_char(sysdate,'YYYY-MM-DD HH:MI:SS')
2892: WHERE MVIEW_NAME = lv_snapshot_name ;
2893:
2894: COMMIT;
2895: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'successfully updated the complete refresh time in fnd_lookup_values');
2896: END IF;
2897:
2898: -- BUG 12601292
2899: -- Need to truncate the ad table mrp_ad_supply when the
2900: -- snapshot mtl_supply_sn is refreshed in complete mode
2901:
2902: IF lv_initialization_flag = 1 THEN
2903:
2904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2905: 'Truncating AD table: MRP_AD_SUPPLY');
2906: lv_sql_stmt:= 'TRUNCATE TABLE '||
2907: MSC_UTIL.G_MRP_SCHEMA||'.MRP_AD_SUPPLY';
2908: EXECUTE IMMEDIATE lv_sql_stmt;
2903:
2904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
2905: 'Truncating AD table: MRP_AD_SUPPLY');
2906: lv_sql_stmt:= 'TRUNCATE TABLE '||
2907: MSC_UTIL.G_MRP_SCHEMA||'.MRP_AD_SUPPLY';
2908: EXECUTE IMMEDIATE lv_sql_stmt;
2909:
2910: END IF;
2911:
2913: WHEN OTHERS THEN
2914: ROLLBACK;
2915: RETCODE:= G_ERROR;
2916: ERRBUF:= SQLERRM;
2917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error refreshing individual snapshot : ' || lv_snap_str);
2918: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '|| ERRBUF);
2919: RAISE;
2920: END REFRESH_SINGLE_SNAPSHOT;
2921:
2914: ROLLBACK;
2915: RETCODE:= G_ERROR;
2916: ERRBUF:= SQLERRM;
2917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error refreshing individual snapshot : ' || lv_snap_str);
2918: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '|| ERRBUF);
2919: RAISE;
2920: END REFRESH_SINGLE_SNAPSHOT;
2921:
2922: /*Modified the below procedure to use the AD API in ebr env for drop snapshot w.r.t bug 12964052
2919: RAISE;
2920: END REFRESH_SINGLE_SNAPSHOT;
2921:
2922: /*Modified the below procedure to use the AD API in ebr env for drop snapshot w.r.t bug 12964052
2923: This procedure will drop the valid coll snapshots created using MSC_UTIL.create_snap2 in the apps schema
2924:
2925: */
2926: PROCEDURE DROP_SNAPSHOT(
2927: ERRBUF OUT NOCOPY VARCHAR2,
2945:
2946: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
2947:
2948: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
2949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
2950: ELSE
2951:
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
2953:
2948: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
2949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
2950: ELSE
2951:
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
2953:
2954: lv_cusror_str := ' select mview_name, existance_check
2955: from msc_coll_snapshots_v
2956: where mview_name in ' || lv_snapshot_groups_str || '
2962: fetch c_snap into lv_snapshot_name,lv_existance_check;
2963: exit when c_snap%notfound;
2964: if eval(lv_existance_check) then
2965:
2966: lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2967:
2968: ad_mv.do_mv_ddl(
2969: an_operation_i => ad_mv.mv_drop,
2970: as_mview_name_i => lv_snapshot_name,
2972: ab_execute_i => TRUE
2973: );
2974:
2975: --Droping the Synonym and Trigger on this snapshot
2976: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2977: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2979: drop_count := drop_count + 1;
2980: else
2973: );
2974:
2975: --Droping the Synonym and Trigger on this snapshot
2976: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2977: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2979: drop_count := drop_count + 1;
2980: else
2981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2974:
2975: --Droping the Synonym and Trigger on this snapshot
2976: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2977: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2979: drop_count := drop_count + 1;
2980: else
2981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2982: end if;
2977: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2979: drop_count := drop_count + 1;
2980: else
2981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2982: end if;
2983: END LOOP;
2984:
2985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2982: end if;
2983: END LOOP;
2984:
2985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2986: END IF;
2987: if drop_count > 0 then
2988: begin
2989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
2985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2986: END IF;
2987: if drop_count > 0 then
2988: begin
2989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
2990:
2991: UPDATE FND_PROFILE_OPTION_VALUES
2992: SET PROFILE_OPTION_VALUE = 'Y'
2993: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2992: SET PROFILE_OPTION_VALUE = 'Y'
2993: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2994: FROM FND_PROFILE_OPTIONS
2995: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
2996: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
2997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
2998: COMMIT;
2999:
3000: EXCEPTION
2993: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2994: FROM FND_PROFILE_OPTIONS
2995: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
2996: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
2997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
2998: COMMIT;
2999:
3000: EXCEPTION
3001:
2999:
3000: EXCEPTION
3001:
3002: WHEN OTHERS THEN
3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
3004: end;
3005: end if;
3006: RETCODE:= G_SUCCESS;
3007:
3009: WHEN OTHERS THEN
3010: ROLLBACK;
3011: RETCODE:= G_ERROR;
3012: ERRBUF:= SQLERRM;
3013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3014: END DROP_SNAPSHOT;
3015:
3016: /*Created the below procedure with 'Execute Immediate' code for drop snapshot w.r.t bug 12964052
3017: This procedure retains the original behaviour of DROP_SNAPSHOT before modifying it w.r.t bug 12964052
3014: END DROP_SNAPSHOT;
3015:
3016: /*Created the below procedure with 'Execute Immediate' code for drop snapshot w.r.t bug 12964052
3017: This procedure retains the original behaviour of DROP_SNAPSHOT before modifying it w.r.t bug 12964052
3018: This procedure drops snapshots in apps schema for snapshots created using MSC_UTIL.create_snap*/
3019: PROCEDURE DROP_SNAPSHOT_EI(
3020: ERRBUF OUT NOCOPY VARCHAR2,
3021: RETCODE OUT NOCOPY NUMBER,
3022: p_snapshot_str IN VARCHAR2)
3038:
3039: lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
3040:
3041: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
3042: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
3043: ELSE
3044:
3045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
3046:
3041: IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
3042: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
3043: ELSE
3044:
3045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
3046:
3047: lv_cusror_str := ' select mview_name, existance_check
3048: from msc_coll_snapshots_v
3049: where mview_name in ' || lv_snapshot_groups_str || '
3055: fetch c_snap into lv_snapshot_name,lv_existance_check;
3056: exit when c_snap%notfound;
3057: if eval(lv_existance_check) then
3058:
3059: lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
3060:
3061: EXECUTE IMMEDIATE lv_sql_stmt;
3062: --Droping the Synonym and Trigger on this snapshot
3063: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3059: lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
3060:
3061: EXECUTE IMMEDIATE lv_sql_stmt;
3062: --Droping the Synonym and Trigger on this snapshot
3063: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3064: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3066: drop_count := drop_count + 1;
3067: else
3060:
3061: EXECUTE IMMEDIATE lv_sql_stmt;
3062: --Droping the Synonym and Trigger on this snapshot
3063: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3064: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3066: drop_count := drop_count + 1;
3067: else
3068: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3061: EXECUTE IMMEDIATE lv_sql_stmt;
3062: --Droping the Synonym and Trigger on this snapshot
3063: MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3064: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3066: drop_count := drop_count + 1;
3067: else
3068: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3069: end if;
3064: MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3066: drop_count := drop_count + 1;
3067: else
3068: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3069: end if;
3070: END LOOP;
3071:
3072: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
3068: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
3069: end if;
3070: END LOOP;
3071:
3072: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
3073: END IF;
3074: if drop_count > 0 then
3075: begin
3076: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
3072: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
3073: END IF;
3074: if drop_count > 0 then
3075: begin
3076: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
3077:
3078: UPDATE FND_PROFILE_OPTION_VALUES
3079: SET PROFILE_OPTION_VALUE = 'Y'
3080: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
3079: SET PROFILE_OPTION_VALUE = 'Y'
3080: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
3081: FROM FND_PROFILE_OPTIONS
3082: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
3083: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
3084: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
3085: COMMIT;
3086:
3087: EXCEPTION
3080: WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
3081: FROM FND_PROFILE_OPTIONS
3082: WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
3083: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
3084: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
3085: COMMIT;
3086:
3087: EXCEPTION
3088:
3086:
3087: EXCEPTION
3088:
3089: WHEN OTHERS THEN
3090: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
3091: end;
3092: end if;
3093: RETCODE:= G_SUCCESS;
3094:
3096: WHEN OTHERS THEN
3097: ROLLBACK;
3098: RETCODE:= G_ERROR;
3099: ERRBUF:= SQLERRM;
3100: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3101: END DROP_SNAPSHOT_EI;
3102: /*Addition w.r.t bug 12964052,EBR changes ends here */
3103:
3104: PROCEDURE check_MV_cont_ref_type(p_MV_name in varchar2,
3121: v_sql_stmt Varchar2(2000);
3122:
3123: BEGIN
3124:
3125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_MV_cont_ref_type');
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3122:
3123: BEGIN
3124:
3125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_MV_cont_ref_type');
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3123: BEGIN
3124:
3125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_MV_cont_ref_type');
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3124:
3125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_MV_cont_ref_type');
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_MV_cont_ref_type');
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3133:
3126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_ad_table_name is '||p_ad_table_name);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3133:
3134:
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3133:
3134:
3135: IF p_last_tgt_cont_coll_time is NOT NULL THEN
3128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_MV_name is '||p_MV_name);
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3133:
3134:
3135: IF p_last_tgt_cont_coll_time is NOT NULL THEN
3136: BEGIN
3141: and nvl(to_date(complete_refresh_timestamp,'YYYY-MM-DD HH:MI:SS'),p_last_tgt_cont_coll_time) > p_last_tgt_cont_coll_time;
3142:
3143: if NOT eval(lv_existance_check) then
3144: --Snapshot doesn't exist. So no collection required!!..
3145: entity_flag := MSC_UTIL.SYS_NO;
3146: RETURN;
3147: end if;
3148:
3149: EXCEPTION
3150: WHEN NO_DATA_FOUND THEN
3151: lv_MV_complete_refresh := 0;
3152: END;
3153: IF lv_MV_complete_refresh = 1 THEN
3154: entity_flag := MSC_UTIL.SYS_TGT;
3155: RETURN;
3156: END IF;
3157: END IF;
3158:
3177: and organization_id ' || p_org_str ;
3178: END IF ;
3179: EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_new;
3180:
3181: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_new is '||lv_Num_new);
3182: IF (p_MV_name = 'MTL_SYS_ITEMS_SN') THEN
3183: v_sql_stmt := 'select count(*)
3184: from ' || p_MV_name || '
3185: where item_rn <= ' || p_entity_lrn || '
3195:
3196:
3197: EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_snap USING 1+((lv_Num_del + lv_Num_new)/(p_coll_thresh/100));
3198:
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_del is '||lv_Num_del);
3200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_snap is '||lv_Num_snap);
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of v_sql_stmt is '||v_sql_stmt);
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3196:
3197: EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_snap USING 1+((lv_Num_del + lv_Num_new)/(p_coll_thresh/100));
3198:
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_del is '||lv_Num_del);
3200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_snap is '||lv_Num_snap);
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of v_sql_stmt is '||v_sql_stmt);
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3204: entity_flag := MSC_UTIL.SYS_NO;
3197: EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_snap USING 1+((lv_Num_del + lv_Num_new)/(p_coll_thresh/100));
3198:
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_del is '||lv_Num_del);
3200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_snap is '||lv_Num_snap);
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of v_sql_stmt is '||v_sql_stmt);
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3204: entity_flag := MSC_UTIL.SYS_NO;
3205: ELSIF lv_Num_snap =0 THEN
3198:
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_del is '||lv_Num_del);
3200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_snap is '||lv_Num_snap);
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of v_sql_stmt is '||v_sql_stmt);
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3204: entity_flag := MSC_UTIL.SYS_NO;
3205: ELSIF lv_Num_snap =0 THEN
3206: entity_flag := MSC_UTIL.SYS_TGT;
3200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_Num_snap is '||lv_Num_snap);
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of v_sql_stmt is '||v_sql_stmt);
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3204: entity_flag := MSC_UTIL.SYS_NO;
3205: ELSIF lv_Num_snap =0 THEN
3206: entity_flag := MSC_UTIL.SYS_TGT;
3207: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
3208: entity_flag := MSC_UTIL.SYS_TGT;
3202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3203: IF lv_Num_new = 0 and lv_Num_del = 0 THEN
3204: entity_flag := MSC_UTIL.SYS_NO;
3205: ELSIF lv_Num_snap =0 THEN
3206: entity_flag := MSC_UTIL.SYS_TGT;
3207: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
3208: entity_flag := MSC_UTIL.SYS_TGT;
3209: ELSE
3210: entity_flag := MSC_UTIL.SYS_INCR;
3204: entity_flag := MSC_UTIL.SYS_NO;
3205: ELSIF lv_Num_snap =0 THEN
3206: entity_flag := MSC_UTIL.SYS_TGT;
3207: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
3208: entity_flag := MSC_UTIL.SYS_TGT;
3209: ELSE
3210: entity_flag := MSC_UTIL.SYS_INCR;
3211: END IF;
3212:
3206: entity_flag := MSC_UTIL.SYS_TGT;
3207: ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
3208: entity_flag := MSC_UTIL.SYS_TGT;
3209: ELSE
3210: entity_flag := MSC_UTIL.SYS_INCR;
3211: END IF;
3212:
3213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3214:
3209: ELSE
3210: entity_flag := MSC_UTIL.SYS_INCR;
3211: END IF;
3212:
3213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3214:
3215:
3216: EXCEPTION WHEN OTHERS THEN
3217: entity_flag := MSC_UTIL.SYS_INCR;
3213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3214:
3215:
3216: EXCEPTION WHEN OTHERS THEN
3217: entity_flag := MSC_UTIL.SYS_INCR;
3218: p_ret_code := MSC_UTIL.G_ERROR;
3219: p_err_buf := p_err_buf || ' ' ||p_MV_name ;
3220: END check_MV_cont_ref_type;
3221:
3214:
3215:
3216: EXCEPTION WHEN OTHERS THEN
3217: entity_flag := MSC_UTIL.SYS_INCR;
3218: p_ret_code := MSC_UTIL.G_ERROR;
3219: p_err_buf := p_err_buf || ' ' ||p_MV_name ;
3220: END check_MV_cont_ref_type;
3221:
3222:
3241:
3242: lv_cusros_str varchar2(500);
3243:
3244: lv_MV_complete_refresh number :=0 ;
3245: lv_entity_decided number := msc_util.sys_no;
3246: lv_entity_incr number := msc_util.sys_no;
3247: BEGIN
3248:
3249:
3242: lv_cusros_str varchar2(500);
3243:
3244: lv_MV_complete_refresh number :=0 ;
3245: lv_entity_decided number := msc_util.sys_no;
3246: lv_entity_incr number := msc_util.sys_no;
3247: BEGIN
3248:
3249:
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3246: lv_entity_incr number := msc_util.sys_no;
3247: BEGIN
3248:
3249:
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3247: BEGIN
3248:
3249:
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3248:
3249:
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3249:
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3257: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
3250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Start of function check_entity_cont_ref_type');
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3257: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
3258: IF p_last_tgt_cont_coll_time is NOT NULL THEN
3251: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_name is '||p_entity_name);
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3257: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
3258: IF p_last_tgt_cont_coll_time is NOT NULL THEN
3259: BEGIN
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag is '||entity_flag);
3253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_entity_lrn is '||p_entity_lrn);
3254: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_org_str is '||p_org_str);
3255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_coll_thresh is '||p_coll_thresh);
3256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of p_last_tgt_cont_coll_time is '||p_last_tgt_cont_coll_time);
3257: /*Check if any MV for this entity has undergone a complete refresh after last coll*/
3258: IF p_last_tgt_cont_coll_time is NOT NULL THEN
3259: BEGIN
3260: Execute immediate '
3260: Execute immediate '
3261: select 1
3262: from msc_coll_snapshots_v
3263: where snapshot_group_string = ''' || p_entity_name || '''
3264: and existance_check = to_char(' || MSC_UTIL.SYS_YES || ')
3265: and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
3266: and to_date(nvl(complete_refresh_timestamp,to_char(:vDate,''YYYY-MM-DD HH:MI:SS'')),''YYYY-MM-DD HH:MI:SS'') > :vDate
3267: and rownum < 2'
3268: into lv_MV_complete_refresh
3261: select 1
3262: from msc_coll_snapshots_v
3263: where snapshot_group_string = ''' || p_entity_name || '''
3264: and existance_check = to_char(' || MSC_UTIL.SYS_YES || ')
3265: and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
3266: and to_date(nvl(complete_refresh_timestamp,to_char(:vDate,''YYYY-MM-DD HH:MI:SS'')),''YYYY-MM-DD HH:MI:SS'') > :vDate
3267: and rownum < 2'
3268: into lv_MV_complete_refresh
3269: using p_last_tgt_cont_coll_time, p_last_tgt_cont_coll_time; -- tobe enhanced
3271: WHEN NO_DATA_FOUND THEN
3272: lv_MV_complete_refresh := 0;
3273: END;
3274: IF lv_MV_complete_refresh = 1 THEN
3275: entity_flag := MSC_UTIL.SYS_TGT;
3276: RETURN;
3277: END IF;
3278: END IF;
3279:
3278: END IF;
3279:
3280: lv_cusros_str := ' select mview_name, ad_table_name, existance_check
3281: from msc_coll_snapshots_v
3282: where check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
3283: and snapshot_group_string = ''' || p_entity_name || '''' ;
3284:
3285: OPEN c_snap for lv_cusros_str;
3286:
3298: p_last_tgt_cont_coll_time,
3299: p_ret_code,
3300: p_err_buf);
3301:
3302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3303: IF entity_flag = MSC_UTIL.SYS_TGT THEN
3304: lv_entity_decided := msc_util.sys_yes;
3305: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
3306: lv_entity_incr := msc_util.sys_yes;
3299: p_ret_code,
3300: p_err_buf);
3301:
3302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3303: IF entity_flag = MSC_UTIL.SYS_TGT THEN
3304: lv_entity_decided := msc_util.sys_yes;
3305: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
3306: lv_entity_incr := msc_util.sys_yes;
3307: END IF;
3300: p_err_buf);
3301:
3302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3303: IF entity_flag = MSC_UTIL.SYS_TGT THEN
3304: lv_entity_decided := msc_util.sys_yes;
3305: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
3306: lv_entity_incr := msc_util.sys_yes;
3307: END IF;
3308:
3301:
3302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3303: IF entity_flag = MSC_UTIL.SYS_TGT THEN
3304: lv_entity_decided := msc_util.sys_yes;
3305: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
3306: lv_entity_incr := msc_util.sys_yes;
3307: END IF;
3308:
3309: /* if the last MV suggested no collection,
3302: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag returned by check_MV_cont_ref_type is '||entity_flag);
3303: IF entity_flag = MSC_UTIL.SYS_TGT THEN
3304: lv_entity_decided := msc_util.sys_yes;
3305: ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
3306: lv_entity_incr := msc_util.sys_yes;
3307: END IF;
3308:
3309: /* if the last MV suggested no collection,
3310: this need to be updated with previous result. */
3307: END IF;
3308:
3309: /* if the last MV suggested no collection,
3310: this need to be updated with previous result. */
3311: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
3312: entity_flag := MSC_UTIL.SYS_INCR;
3313: END IF;
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3308:
3309: /* if the last MV suggested no collection,
3310: this need to be updated with previous result. */
3311: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
3312: entity_flag := MSC_UTIL.SYS_INCR;
3313: END IF;
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_decided in check_entity_cont_ref_type is '||lv_entity_decided);
3311: IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
3312: entity_flag := MSC_UTIL.SYS_INCR;
3313: END IF;
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_decided in check_entity_cont_ref_type is '||lv_entity_decided);
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_incr in check_entity_cont_ref_type is '||lv_entity_incr);
3318: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
3319: end if; --eval (lv_existance_check)
3312: entity_flag := MSC_UTIL.SYS_INCR;
3313: END IF;
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_decided in check_entity_cont_ref_type is '||lv_entity_decided);
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_incr in check_entity_cont_ref_type is '||lv_entity_incr);
3318: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
3319: end if; --eval (lv_existance_check)
3320: END LOOP;
3313: END IF;
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_decided in check_entity_cont_ref_type is '||lv_entity_decided);
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_incr in check_entity_cont_ref_type is '||lv_entity_incr);
3318: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
3319: end if; --eval (lv_existance_check)
3320: END LOOP;
3321:
3314:
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of entity_flag in check_entity_cont_ref_type is '||entity_flag);
3316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_decided in check_entity_cont_ref_type is '||lv_entity_decided);
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, 'Value of lv_entity_incr in check_entity_cont_ref_type is '||lv_entity_incr);
3318: EXIT WHEN lv_entity_decided = msc_util.sys_yes;
3319: end if; --eval (lv_existance_check)
3320: END LOOP;
3321:
3322: EXCEPTION
3320: END LOOP;
3321:
3322: EXCEPTION
3323: WHEN OTHERS THEN
3324: p_ret_code := MSC_UTIL.G_ERROR;
3325: p_err_buf := SQLERRM;
3326: END check_entity_cont_ref_type;
3327:
3328:
3345: NULL,
3346: NULL,
3347: FALSE);
3348: commit;
3349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Setup Views used by Collections Process');
3350: --
3351: -- Item
3352: indx := indx + 1;
3353: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3356: NULL,
3357: NULL,
3358: FALSE);
3359: commit;
3360: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Item Views used by Collections Process');
3361: --
3362: -- BOM
3363: indx := indx + 1;
3364: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3367: NULL,
3368: NULL,
3369: FALSE);
3370: commit;
3371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates BOM Views used by Collections Process');
3372: --
3373: -- Routing
3374: indx := indx + 1;
3375: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3378: NULL,
3379: NULL,
3380: FALSE);
3381: commit;
3382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Routing Views used by Collections Process');
3383: --
3384: -- WIP
3385: indx := indx + 1;
3386: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3389: NULL,
3390: NULL,
3391: FALSE);
3392: commit;
3393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates WIP Views used by Collections Process');
3394: --
3395: -- Demand
3396: indx := indx + 1;
3397: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3400: NULL,
3401: NULL,
3402: FALSE);
3403: commit;
3404: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Demand Views used by Collections Process');
3405: --
3406:
3407: -- Supply
3408: indx := indx + 1;
3412: NULL,
3413: NULL,
3414: FALSE);
3415: commit;
3416: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Supply Views used by Collections Process');
3417: --
3418: -- Other
3419: indx := indx + 1;
3420: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3423: NULL,
3424: NULL,
3425: FALSE);
3426: commit;
3427: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Other Views used by Collections Process');
3428: --
3429: -- Repair Order
3430: indx := indx + 1;
3431: lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
3434: NULL,
3435: NULL,
3436: FALSE);
3437: commit;
3438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Repair Order Views used by Collections Process');
3439: --
3440: -- CMRO
3441: IF CHECK_USAF_FLAG = 1 THEN
3442: indx := indx + 1;
3446: NULL,
3447: NULL,
3448: FALSE);
3449: commit;
3450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates CMRO Views used by Collections Process');
3451: END IF;
3452: --
3453: --EAM
3454: IF CHECK_USAF_FLAG = 1 THEN
3459: NULL,
3460: NULL,
3461: FALSE);
3462: commit;
3463: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates EAM Views used by Collections Process');
3464: END IF;
3465: --
3466: for i in 1 .. lv_request_id_views.last
3467: loop
3467: loop
3468: wait_for_request(lv_request_id_views(i), 10, lv_out);
3469: if lv_out = 2 THEN
3470: ERRBUF := 'Error in creating Source Views';
3471: RETCODE := MSC_UTIL.G_ERROR;
3472: EXIT;
3473: end if;
3474: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'id-'||lv_request_id_views(i));
3475: end loop;
3470: ERRBUF := 'Error in creating Source Views';
3471: RETCODE := MSC_UTIL.G_ERROR;
3472: EXIT;
3473: end if;
3474: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'id-'||lv_request_id_views(i));
3475: end loop;
3476:
3477: EXCEPTION
3478: WHEN OTHERS THEN
3475: end loop;
3476:
3477: EXCEPTION
3478: WHEN OTHERS THEN
3479: RETCODE := MSC_UTIL.G_ERROR;
3480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
3481: RAISE;
3482:
3483: END CREATE_SOURCE_VIEWS;
3476:
3477: EXCEPTION
3478: WHEN OTHERS THEN
3479: RETCODE := MSC_UTIL.G_ERROR;
3480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
3481: RAISE;
3482:
3483: END CREATE_SOURCE_VIEWS;
3484:
3500: NULL,
3501: NULL,
3502: FALSE);
3503: commit;
3504: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Item Triggers used by Collections Process');
3505: --
3506: -- BOM
3507: indx := indx + 1;
3508: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3511: NULL,
3512: NULL,
3513: FALSE);
3514: commit;
3515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates BOM Triggers used by Collections Process');
3516: --
3517: -- Routing
3518: indx := indx + 1;
3519: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3522: NULL,
3523: NULL,
3524: FALSE);
3525: commit;
3526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Routing Triggers used by Collections Process');
3527: --
3528: -- WIP
3529: indx := indx + 1;
3530: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3533: NULL,
3534: NULL,
3535: FALSE);
3536: commit;
3537: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates WIP Triggers used by Collections Process');
3538: --
3539: -- Demand
3540: indx := indx + 1;
3541: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3544: NULL,
3545: NULL,
3546: FALSE);
3547: commit;
3548: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Demand Triggers used by Collections Process');
3549: --
3550:
3551: -- Supply
3552: indx := indx + 1;
3556: NULL,
3557: NULL,
3558: FALSE);
3559: commit;
3560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Supply Triggers used by Collections Process');
3561: --
3562: -- Other
3563: indx := indx + 1;
3564: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3567: NULL,
3568: NULL,
3569: FALSE);
3570: commit;
3571: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Other Triggers used by Collections Process');
3572: --
3573: -- Repair Order
3574: indx := indx + 1;
3575: lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
3578: NULL,
3579: NULL,
3580: FALSE);
3581: commit;
3582: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Repair Order Triggers used by Collections Process');
3583: --
3584: -- CMRO
3585: IF CHECK_USAF_FLAG = 1 THEN
3586: indx := indx + 1;
3590: NULL,
3591: NULL,
3592: FALSE);
3593: commit;
3594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates CMRO Triggers used by Collections Process');
3595: END IF;
3596:
3597: --
3598: --EAM
3604: NULL,
3605: NULL,
3606: FALSE);
3607: commit;
3608: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates EAM Triggers used by Collections Process');
3609: END IF;
3610: --
3611: for i in 1 .. lv_request_id_trigs.last
3612: loop
3612: loop
3613: wait_for_request(lv_request_id_trigs(i), 10, lv_out);
3614: if lv_out = 2 THEN
3615: ERRBUF := 'Error in creating Source Triggers';
3616: RETCODE := MSC_UTIL.G_ERROR;
3617: EXIT;
3618: end if;
3619: end loop;
3620:
3619: end loop;
3620:
3621: EXCEPTION
3622: WHEN OTHERS THEN
3623: RETCODE := MSC_UTIL.G_ERROR;
3624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
3625: RAISE;
3626:
3627: END CREATE_SOURCE_TRIGGERS;
3620:
3621: EXCEPTION
3622: WHEN OTHERS THEN
3623: RETCODE := MSC_UTIL.G_ERROR;
3624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
3625: RAISE;
3626:
3627: END CREATE_SOURCE_TRIGGERS;
3628: