40: -- Standard Start of API savepoint
41: SAVEPOINT populateItemChange_sp;
42:
43: l_err_loc := 300;
44: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
45: l_start_date := sysdate;
46: l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
47: '; Parameter List: p_api_version:' || p_api_version ||
48: ', p_commit:' || p_commit ||
70: l_err_loc := 600;
71: ICX_CAT_UTIL_PVT.setCommitParameter(P_COMMIT);
72:
73: /* START OF TO BE REMOVED */
74: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75: l_err_loc := 650;
76: SELECT COUNT(*)
77: INTO l_tmp_count
78: FROM mtl_system_items_kfv mi
78: FROM mtl_system_items_kfv mi
79: WHERE mi.inventory_item_id = p_inventory_item_id
80: AND mi.organization_id = p_organization_id;
81:
82: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
83: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
84: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
85: 'Num. of rows from mi for the given inventory_item_id and organization_id:' ||
86: l_tmp_count);
79: WHERE mi.inventory_item_id = p_inventory_item_id
80: AND mi.organization_id = p_organization_id;
81:
82: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
83: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
84: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
85: 'Num. of rows from mi for the given inventory_item_id and organization_id:' ||
86: l_tmp_count);
87: END IF;
92: WHERE mtl.inventory_item_id = p_inventory_item_id
93: AND mtl.organization_id = p_organization_id
94: AND mtl.language = mtl.source_lang;
95:
96: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
98: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
99: 'Num. of rows from mtl for the given inventory_item_id and organization_id:' ||
100: l_tmp_count);
93: AND mtl.organization_id = p_organization_id
94: AND mtl.language = mtl.source_lang;
95:
96: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
98: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
99: 'Num. of rows from mtl for the given inventory_item_id and organization_id:' ||
100: l_tmp_count);
101: END IF;
106: WHERE mic.inventory_item_id = p_inventory_item_id
107: AND mic.organization_id = p_organization_id
108: AND mic.category_set_id = 2;
109:
110: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
112: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
113: 'Num. of rows from mic for the given inventory_item_id and organization_id:' ||
114: l_tmp_count);
107: AND mic.organization_id = p_organization_id
108: AND mic.category_set_id = 2;
109:
110: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
112: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
113: 'Num. of rows from mic for the given inventory_item_id and organization_id:' ||
114: l_tmp_count);
115: END IF;
140: COMMIT;
141: l_err_loc := 1200;
142: -- Call the rebuild index
143: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
144: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
146: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
147: 'Rebuild indexes called.');
148: END IF;
141: l_err_loc := 1200;
142: -- Call the rebuild index
143: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
144: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
146: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
147: 'Rebuild indexes called.');
148: END IF;
149: ELSE
147: 'Rebuild indexes called.');
148: END IF;
149: ELSE
150: l_err_loc := 1300;
151: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
152: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
153: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
154: 'p_commit is false, so Rebuild indexes is not called in.');
155: END IF;
148: END IF;
149: ELSE
150: l_err_loc := 1300;
151: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
152: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
153: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
154: 'p_commit is false, so Rebuild indexes is not called in.');
155: END IF;
156: END IF;
155: END IF;
156: END IF;
157: l_err_loc := 1400;
158:
159: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
160: l_end_date := sysdate;
161: ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
162: l_api_name || ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
163: END IF;
166: BEGIN
167: ROLLBACK TO populateItemChange_sp;
168: EXCEPTION
169: WHEN OTHERS THEN
170: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
172: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
173: 'ROLLBACK TO the savepoint caused the exception -->'
174: || SQLERRM);
167: ROLLBACK TO populateItemChange_sp;
168: EXCEPTION
169: WHEN OTHERS THEN
170: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
172: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
173: 'ROLLBACK TO the savepoint caused the exception -->'
174: || SQLERRM);
175: END IF;
210:
211: l_err_loc := 300;
212: l_start_date := sysdate;
213:
214: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215: l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
216: ', p_api_version:' || p_api_version ||
217: ', p_commit:' || p_commit ||
218: ', p_request_id:' || p_request_id ||
235: ICX_CAT_UTIL_PVT.setCommitParameter(P_COMMIT);
236:
237: l_err_loc := 650;
238: -- Log the values from mtl_bulkload_item_recs:
239: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
240: ICX_CAT_UTIL_PVT.logMtlItemBulkloadRecsData(p_request_id);
241: END IF;
242:
243: l_err_loc := 700;
256: l_err_loc := 950;
257: ICX_CAT_POPULATE_MI_PVT.populateItemCatgChange(NULL, NULL, NULL, P_REQUEST_ID, P_ENTITY_TYPE);
258: ELSE
259: l_err_loc := 1000;
260: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
261: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
262: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
263: 'Invalid entity_type:' || P_ENTITY_TYPE);
264: END IF;
257: ICX_CAT_POPULATE_MI_PVT.populateItemCatgChange(NULL, NULL, NULL, P_REQUEST_ID, P_ENTITY_TYPE);
258: ELSE
259: l_err_loc := 1000;
260: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
261: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
262: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
263: 'Invalid entity_type:' || P_ENTITY_TYPE);
264: END IF;
265: END IF;
271: COMMIT;
272: l_err_loc := 1300;
273: -- Call the rebuild index
274: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
275: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
277: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
278: 'Rebuild indexes called.');
279: END IF;
272: l_err_loc := 1300;
273: -- Call the rebuild index
274: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
275: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
277: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
278: 'Rebuild indexes called.');
279: END IF;
280: ELSE
278: 'Rebuild indexes called.');
279: END IF;
280: ELSE
281: l_err_loc := 1400;
282: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
284: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
285: 'p_commit is false, so Rebuild indexes is not called.');
286: END IF;
279: END IF;
280: ELSE
281: l_err_loc := 1400;
282: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
284: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
285: 'p_commit is false, so Rebuild indexes is not called.');
286: END IF;
287: END IF;
287: END IF;
288:
289: l_err_loc := 1500;
290: l_end_date := sysdate;
291: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292: ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
293: ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
294: END IF;
295: EXCEPTION
297: BEGIN
298: ROLLBACK TO populateBulkItemChange_sp;
299: EXCEPTION
300: WHEN OTHERS THEN
301: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
303: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
304: 'ROLLBACK TO the savepoint caused the exception -->'
305: || SQLERRM);
298: ROLLBACK TO populateBulkItemChange_sp;
299: EXCEPTION
300: WHEN OTHERS THEN
301: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
303: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
304: 'ROLLBACK TO the savepoint caused the exception -->'
305: || SQLERRM);
306: END IF;
344:
345: l_err_loc := 300;
346: l_start_date := sysdate;
347:
348: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349: l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
350: ', p_api_version:' || p_api_version ||
351: ', p_commit:' || p_commit ||
352: ', p_dml_type:' || p_dml_type ||
375: ICX_CAT_UTIL_PVT.getPurchasingCategorySetInfo;
376:
377: l_err_loc := 700;
378: IF (ICX_CAT_UTIL_PVT.g_category_set_id <> P_CATEGORY_SET_ID) THEN
379: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380: FND_LOG.string(FND_LOG.LEVEL_EVENT,
381: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
382: 'returning; g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id );
383: END IF;
376:
377: l_err_loc := 700;
378: IF (ICX_CAT_UTIL_PVT.g_category_set_id <> P_CATEGORY_SET_ID) THEN
379: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380: FND_LOG.string(FND_LOG.LEVEL_EVENT,
381: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
382: 'returning; g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id );
383: END IF;
384: RETURN;
406: COMMIT;
407: l_err_loc := 1400;
408: -- Call the rebuild index
409: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
410: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
412: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
413: 'Rebuild indexes called.');
414: END IF;
407: l_err_loc := 1400;
408: -- Call the rebuild index
409: ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
410: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
412: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
413: 'Rebuild indexes called.');
414: END IF;
415: ELSE
413: 'Rebuild indexes called.');
414: END IF;
415: ELSE
416: l_err_loc := 1500;
417: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
419: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
420: 'p_commit is false, so Rebuild indexes is not called.');
421: END IF;
414: END IF;
415: ELSE
416: l_err_loc := 1500;
417: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
419: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
420: 'p_commit is false, so Rebuild indexes is not called.');
421: END IF;
422: END IF;
422: END IF;
423:
424: l_err_loc := 1600;
425: l_end_date := sysdate;
426: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427: ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
428: ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
429: END IF;
430: EXCEPTION
432: BEGIN
433: ROLLBACK TO populateItemCategoryChange_sp;
434: EXCEPTION
435: WHEN OTHERS THEN
436: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
438: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
439: 'ROLLBACK TO the savepoint caused the exception -->'
440: || SQLERRM);
433: ROLLBACK TO populateItemCategoryChange_sp;
434: EXCEPTION
435: WHEN OTHERS THEN
436: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
438: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
439: 'ROLLBACK TO the savepoint caused the exception -->'
440: || SQLERRM);
441: END IF;