[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_CATG_PVT
Source
1 PACKAGE BODY ICX_CAT_POPULATE_CATG_PVT AS
2 /* $Header: ICXVPPCB.pls 120.3 2006/06/26 23:22:34 sbgeorge noship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_POPULATE_CATG_PVT';
6 TYPE g_item_csr_type IS REF CURSOR;
7
8 gTotalRowCount PLS_INTEGER:= 0;
9
10 -- Insert into icx_por_category_data_sources and icx_por_category_order_map
11 gInsMapRtCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
12 gInsMapCategoryKeyTbl DBMS_SQL.VARCHAR2_TABLE;
13 gInsMapLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
14
15 -- Insert into icx_cat_categories_tl
16 gInsRtCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
17 gInsCategoryKeyTbl DBMS_SQL.VARCHAR2_TABLE;
18 gInsCategoryNameTbl DBMS_SQL.VARCHAR2_TABLE;
19 gInsLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
20 gInsSourceLangTbl DBMS_SQL.VARCHAR2_TABLE;
21
22 -- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
23 gInsPOCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
24
25 -- Update icx_cat_categories_tl
26 gUpdRtCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
27 gUpdCategoryNameTbl DBMS_SQL.VARCHAR2_TABLE;
28 gUpdLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
29 gUpdSourceLangTbl DBMS_SQL.VARCHAR2_TABLE;
30
31 -- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
32 gDelPoCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
33
34 PROCEDURE clearTables
35 ( p_action_mode IN VARCHAR2
36 )
37 IS
38 BEGIN
39 IF (p_action_mode IN ('ALL', 'INSERT_MAPPING')) THEN
40 -- Insert into icx_por_category_data_sources and icx_por_category_order_map
41 gInsMapRtCategoryIdTbl.DELETE;
42 gInsMapCategoryKeyTbl.DELETE;
43 gInsMapLanguageTbl.DELETE;
44 END IF;
45
46 IF (p_action_mode IN ('ALL', 'INSERT_CATEGORY')) THEN
47 -- Insert into icx_cat_categories_tl
48 gInsRtCategoryIdTbl.DELETE;
49 gInsCategoryKeyTbl.DELETE;
50 gInsCategoryNameTbl.DELETE;
51 gInsLanguageTbl.DELETE;
52 gInsSourceLangTbl.DELETE;
53 END IF;
54
55 IF (p_action_mode IN ('ALL', 'INSERT_ITEM_CATEGORY')) THEN
56 -- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
57 gInsPOCategoryIdTbl.DELETE;
58 END IF;
59
60 IF (p_action_mode IN ('ALL', 'UPDATE_CATEGORY')) THEN
61 -- Update icx_cat_categories_tl
62 gUpdRtCategoryIdTbl.DELETE;
63 gUpdCategoryNameTbl.DELETE;
64 gUpdLanguageTbl.DELETE;
65 gUpdSourceLangTbl.DELETE;
66 END IF;
67
68 IF (p_action_mode IN ('ALL', 'DELETE_CATEGORY')) THEN
69 -- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
70 gDelPoCategoryIdTbl.DELETE;
71 END IF;
72
73 END clearTables;
74
75 /* Function is for debugging only */
76 FUNCTION logPLSQLTableRow
77 ( p_index IN NUMBER ,
78 p_action_mode IN VARCHAR2
79 )
80 RETURN VARCHAR2
81 IS
82 l_string VARCHAR2(4000);
83 BEGIN
84 l_string := 'logPLSQLTableRow('||p_action_mode||')['||p_index||']--';
85 IF (p_action_mode = 'INSERT_MAPPING') THEN
86 -- Insert into icx_por_category_data_sources and icx_por_category_order_map
87 l_string := l_string || ' gInsMapRtCategoryIdTbl: ' ||
88 ICX_CAT_UTIL_PVT.getTableElement(gInsMapRtCategoryIdTbl, p_index) || ', ';
89 l_string := l_string || ' gInsMapCategoryKeyTbl: ' ||
90 ICX_CAT_UTIL_PVT.getTableElement(gInsMapCategoryKeyTbl, p_index) || ', ';
91 l_string := l_string || ' gInsMapLanguageTbl: ' ||
92 ICX_CAT_UTIL_PVT.getTableElement(gInsMapLanguageTbl, p_index) || ', ';
93 END IF;
94
95 IF (p_action_mode = 'INSERT_CATEGORY') THEN
96 -- Insert into icx_cat_categories_tl
97 l_string := l_string || ' gInsRtCategoryIdTbl: ' ||
98 ICX_CAT_UTIL_PVT.getTableElement(gInsRtCategoryIdTbl, p_index) || ', ';
99 l_string := l_string || ' gInsCategoryKeyTbl: ' ||
100 ICX_CAT_UTIL_PVT.getTableElement(gInsCategoryKeyTbl, p_index) || ', ';
101 l_string := l_string || ' gInsCategoryNameTbl: ' ||
102 ICX_CAT_UTIL_PVT.getTableElement(gInsCategoryNameTbl, p_index) || ', ';
103 l_string := l_string || ' gInsLanguageTbl: ' ||
104 ICX_CAT_UTIL_PVT.getTableElement(gInsLanguageTbl, p_index) || ', ';
105 l_string := l_string || ' gInsSourceLangTbl: ' ||
106 ICX_CAT_UTIL_PVT.getTableElement(gInsSourceLangTbl, p_index) || ', ';
107 END IF;
108
109 IF (p_action_mode IN ('ALL', 'INSERT_ITEM_CATEGORY')) THEN
110 -- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
111 l_string := l_string || ' gInsPOCategoryIdTbl: ' ||
112 ICX_CAT_UTIL_PVT.getTableElement(gInsPOCategoryIdTbl, p_index) || ', ';
113 END IF;
114
115 IF (p_action_mode = 'UPDATE_CATEGORY') THEN
116 -- Update icx_cat_categories_tl
117 l_string := l_string || ' gUpdRtCategoryIdTbl: ' ||
118 ICX_CAT_UTIL_PVT.getTableElement(gUpdRtCategoryIdTbl, p_index) || ', ';
119 l_string := l_string || ' gUpdCategoryNameTbl: ' ||
120 ICX_CAT_UTIL_PVT.getTableElement(gUpdCategoryNameTbl, p_index) || ', ';
121 l_string := l_string || ' gUpdLanguageTbl: ' ||
122 ICX_CAT_UTIL_PVT.getTableElement(gUpdLanguageTbl, p_index) || ', ';
123 l_string := l_string || ' gUpdSourceLangTbl: ' ||
124 ICX_CAT_UTIL_PVT.getTableElement(gUpdSourceLangTbl, p_index) || ', ';
125 END IF;
126
127 IF (p_action_mode = 'DELETE_CATEGORY') THEN
128 -- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
129 l_string := l_string || ' gDelPoCategoryIdTbl: ' ||
130 ICX_CAT_UTIL_PVT.getTableElement(gDelPoCategoryIdTbl, p_index) || ', ';
131 END IF;
132
133 RETURN l_string;
134
135 END logPLSQLTableRow;
136
137 PROCEDURE logPLSQLTableRow
138 ( p_api_name IN VARCHAR2 ,
139 p_log_level IN NUMBER ,
140 p_index IN NUMBER ,
141 p_action_mode IN VARCHAR2
142 )
143 IS
144 l_log_string VARCHAR2(4000);
145 l_err_loc PLS_INTEGER;
146 l_module_name VARCHAR2(80);
147 BEGIN
148 l_err_loc := 100;
149 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 l_err_loc := 200;
151 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, p_api_name);
152
153 l_err_loc := 300;
154 l_log_string := 'logPLSQLTableRow('||p_action_mode||')['||p_index||']--';
155 FND_LOG.string(p_log_level, l_module_name, l_log_string);
156
157 l_err_loc := 400;
158 IF (p_action_mode = 'INSERT_MAPPING') THEN
159 l_err_loc := 500;
160 -- Insert into icx_por_category_data_sources and icx_por_category_order_map
161 l_log_string := ' gInsMapRtCategoryIdTbl['||p_index||']: ' ||
162 ICX_CAT_UTIL_PVT.getTableElement(gInsMapRtCategoryIdTbl, p_index) || ', ';
163 FND_LOG.string(p_log_level, l_module_name, l_log_string);
164
165 l_log_string := ' gInsMapCategoryKeyTbl['||p_index||']: ' ||
166 ICX_CAT_UTIL_PVT.getTableElement(gInsMapCategoryKeyTbl, p_index) || ', ';
167 FND_LOG.string(p_log_level, l_module_name, l_log_string);
168
169 l_log_string := ' gInsMapLanguageTbl['||p_index||']: ' ||
170 ICX_CAT_UTIL_PVT.getTableElement(gInsMapLanguageTbl, p_index) || ', ';
171 FND_LOG.string(p_log_level, l_module_name, l_log_string);
172 END IF;
173
174 l_err_loc := 600;
175
176 IF (p_action_mode = 'INSERT_CATEGORY') THEN
177 l_err_loc := 700;
178 -- Insert into icx_cat_categories_tl
179 FND_LOG.string(p_log_level, l_module_name, l_log_string);
180
181 l_log_string := ' gInsRtCategoryIdTbl['||p_index||']: ' ||
182 ICX_CAT_UTIL_PVT.getTableElement(gInsRtCategoryIdTbl, p_index) || ', ';
183 FND_LOG.string(p_log_level, l_module_name, l_log_string);
184
185 l_log_string := ' gInsCategoryKeyTbl['||p_index||']: ' ||
186 ICX_CAT_UTIL_PVT.getTableElement(gInsCategoryKeyTbl, p_index) || ', ';
187 FND_LOG.string(p_log_level, l_module_name, l_log_string);
188
189 l_log_string := ' gInsCategoryNameTbl['||p_index||']: ' ||
190 ICX_CAT_UTIL_PVT.getTableElement(gInsCategoryNameTbl, p_index) || ', ';
191 FND_LOG.string(p_log_level, l_module_name, l_log_string);
192
193 l_log_string := ' gInsLanguageTbl['||p_index||']: ' ||
194 ICX_CAT_UTIL_PVT.getTableElement(gInsLanguageTbl, p_index) || ', ';
195 FND_LOG.string(p_log_level, l_module_name, l_log_string);
196
197 l_log_string := ' gInsSourceLangTbl['||p_index||']: ' ||
198 ICX_CAT_UTIL_PVT.getTableElement(gInsSourceLangTbl, p_index) || ', ';
199 FND_LOG.string(p_log_level, l_module_name, l_log_string);
200 END IF;
201
202 l_err_loc := 800;
203
204 IF (p_action_mode = 'INSERT_ITEM_CATEGORY') THEN
205 l_err_loc := 900;
206 -- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
207 FND_LOG.string(p_log_level, l_module_name, l_log_string);
208
209 l_log_string := ' gInsPOCategoryIdTbl['||p_index||']: ' ||
210 ICX_CAT_UTIL_PVT.getTableElement(gInsPOCategoryIdTbl, p_index) || ', ';
211 FND_LOG.string(p_log_level, l_module_name, l_log_string);
212 END IF;
213
214 l_err_loc := 900;
215
216 IF (p_action_mode = 'UPDATE_CATEGORY') THEN
217 l_err_loc := 1000;
218 -- Update icx_cat_categories_tl
219 FND_LOG.string(p_log_level, l_module_name, l_log_string);
220
221 l_log_string := ' gUpdRtCategoryIdTbl['||p_index||']: ' ||
222 ICX_CAT_UTIL_PVT.getTableElement(gUpdRtCategoryIdTbl, p_index) || ', ';
223 FND_LOG.string(p_log_level, l_module_name, l_log_string);
224
225 l_log_string := ' gUpdCategoryNameTbl['||p_index||']: ' ||
226 ICX_CAT_UTIL_PVT.getTableElement(gUpdCategoryNameTbl, p_index) || ', ';
227 FND_LOG.string(p_log_level, l_module_name, l_log_string);
228
229 l_log_string := ' gUpdLanguageTbl['||p_index||']: ' ||
230 ICX_CAT_UTIL_PVT.getTableElement(gUpdLanguageTbl, p_index) || ', ';
231 FND_LOG.string(p_log_level, l_module_name, l_log_string);
232
233 l_log_string := ' gUpdSourceLangTbl['||p_index||']: ' ||
234 ICX_CAT_UTIL_PVT.getTableElement(gUpdSourceLangTbl, p_index) || ', ';
235 FND_LOG.string(p_log_level, l_module_name, l_log_string);
236 END IF;
237
238 l_err_loc := 1100;
239
240 IF (p_action_mode = 'DELETE_CATEGORY') THEN
241 l_err_loc := 1200;
242 -- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
243 FND_LOG.string(p_log_level, l_module_name, l_log_string);
244
245 l_log_string := ' gDelPoCategoryIdTbl['||p_index||']: ' ||
246 ICX_CAT_UTIL_PVT.getTableElement(gDelPoCategoryIdTbl, p_index) || ', ';
247 FND_LOG.string(p_log_level, l_module_name, l_log_string);
248 END IF;
249
250 l_err_loc := 1300;
251 END IF;
252
253 END logPLSQLTableRow;
254
255 PROCEDURE addCategories
256 IS
257 l_api_name CONSTANT VARCHAR2(30) := 'addCategories';
258 l_err_loc PLS_INTEGER;
259 l_action_mode VARCHAR2(80);
260 BEGIN
261 l_err_loc := 100;
262 l_action_mode := 'INSERT_CATEGORY';
263 -- Insert into icx_cat_categories_tl
264 FORALL i IN 1..gInsRtCategoryIdTbl.COUNT
265 INSERT INTO icx_cat_categories_tl(
266 rt_category_id, category_name, key, title, type, language,
267 source_lang, upper_category_name, upper_key, section_map,
268 last_update_login, last_updated_by, last_update_date,
269 created_by, creation_date, request_id,
270 program_application_id, program_id, program_login_id)
271 VALUES(gInsRtCategoryIdTbl(i), gInsCategoryNameTbl(i),
272 gInsCategoryKeyTbl(i), 'Oracle', 2,
273 gInsLanguageTbl(i), gInsSourceLangTbl(i),
274 upper(gInsCategoryNameTbl(i)), upper(gInsCategoryKeyTbl(i)),
275 rpad('0', 300, 0),
276 ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
277 ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate, ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
278 ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
279
280 l_err_loc := 200;
281 IF (gInsRtCategoryIdTbl.COUNT > 0) THEN
282 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
284 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
285 'Num. of rows inserted into categories_tl:' ||SQL%ROWCOUNT);
286 END IF;
287 END IF;
288
289 l_err_loc := 300;
290 clearTables(l_action_mode);
291
292 l_err_loc := 400;
293 l_action_mode := 'INSERT_MAPPING';
294 -- Insert into icx_por_category_data_sources
295 FORALL i in 1..gInsMapRtCategoryIdTbl.COUNT
296 INSERT INTO icx_por_category_data_sources (
297 rt_category_id, category_key, external_source, external_source_key,
298 last_update_login, last_updated_by, last_update_date,
299 created_by, creation_date, request_id,
300 program_application_id, program_id)
301 SELECT rt_category_id, key, 'Oracle', key,
302 ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
303 ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate, ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
304 ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id
305 FROM icx_cat_categories_tl
306 WHERE rt_category_id = gInsMapRtCategoryIdTbl(i)
307 AND language = gInsMapLanguageTbl(i)
308 AND NOT EXISTS (SELECT 1
309 FROM icx_por_category_data_sources
310 WHERE external_source = 'Oracle'
311 AND external_source_key = key);
312
313 l_err_loc := 500;
314 IF (gInsMapRtCategoryIdTbl.COUNT > 0) THEN
315 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
316 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
317 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
318 'Num. of rows inserted into category_data_sources:' ||SQL%ROWCOUNT);
319 END IF;
320 END IF;
321
322 l_err_loc := 600;
323 -- Insert into icx_por_category_order_map
324 FORALL i IN 1..gInsMapRtCategoryIdTbl.COUNT
325 INSERT INTO icx_por_category_order_map (
326 rt_category_id, external_source, external_source_key,
327 last_update_login, last_updated_by, last_update_date,
328 created_by, creation_date)
329 VALUES(gInsMapRtCategoryIdTbl(i), 'Oracle', gInsMapCategoryKeyTbl(i),
330 ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
331 ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate);
332
333 l_err_loc := 700;
334 IF (gInsMapRtCategoryIdTbl.COUNT > 0) THEN
335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
337 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
338 'Num. of rows inserted into category_order_map:' ||SQL%ROWCOUNT);
339 END IF;
340 END IF;
341
342 l_err_loc := 800;
343 clearTables(l_action_mode);
344
345 l_err_loc := 900;
346 EXCEPTION
347 WHEN OTHERS THEN
348 logPLSQLTableRow(l_api_name, FND_LOG.LEVEL_UNEXPECTED, SQL%ROWCOUNT+1, l_action_mode);
349 ICX_CAT_UTIL_PVT.logUnexpectedException(
350 G_PKG_NAME, l_api_name,
351 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
352 RAISE;
353 END addCategories;
354
355 PROCEDURE addItemCategories
356 IS
357
358 l_api_name CONSTANT VARCHAR2(30) := 'addItemCategories';
359 l_err_loc PLS_INTEGER;
360 l_action_mode VARCHAR2(80);
361 BEGIN
362 l_err_loc := 100;
363 l_action_mode := 'INSERT_ITEM_CATEGORY';
364 -- If the category was added to the valid cats after assigning master items to the category
365 -- Or if the category was re-activated
366 -- then addItemCategories should also take care of populating the master items for the category.
367 IF ( gInsPOCategoryIdTbl.COUNT > 0 ) THEN
368 l_err_loc := 200;
369 ICX_CAT_POPULATE_MI_PVT.populateCategoryItems(gInsPOCategoryIdTbl);
370 END IF;
371
372 l_err_loc := 300;
373 clearTables(l_action_mode);
374
375 l_err_loc := 400;
376 EXCEPTION
377 WHEN OTHERS THEN
378 logPLSQLTableRow(l_api_name, FND_LOG.LEVEL_UNEXPECTED, SQL%ROWCOUNT+1, l_action_mode);
379 ICX_CAT_UTIL_PVT.logUnexpectedException(
380 G_PKG_NAME, l_api_name,
381 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
382 RAISE;
383 END addItemCategories;
384
385 -- Call this one when category name is updated from ECM UI.
386 PROCEDURE updateCategories
387 IS
388
389 l_api_name CONSTANT VARCHAR2(30) := 'updateCategories';
390 l_continue BOOLEAN := TRUE;
391 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
392 l_err_loc PLS_INTEGER;
393 l_action_mode VARCHAR2(80);
394 l_searchable NUMBER;
395 l_section_tag NUMBER;
396 l_row_count PLS_INTEGER;
397 BEGIN
398 l_err_loc := 100;
399 l_action_mode := 'UPDATE_CATEGORY';
400
401 l_err_loc := 200;
402 FOR i IN 1..gUpdRtCategoryIdTbl.COUNT LOOP
403 l_continue := TRUE;
404 l_err_loc := 300;
405 WHILE l_continue LOOP
406 l_err_loc := 400;
407 l_rowid_tbl.DELETE;
408
409 l_err_loc := 500;
410 UPDATE icx_cat_items_ctx_hdrs_tlp
411 SET ctx_desc = null,
412 ip_category_name = gUpdCategoryNameTbl(i),
413 last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
414 last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
415 last_update_date = sysdate,
416 internal_request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
417 request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
418 program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
419 program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
420 program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
421 WHERE ip_category_id = gUpdRtCategoryIdTbl(i)
422 AND language = gUpdLanguageTbl(i)
423 AND ip_category_name <> gUpdCategoryNameTbl(i)
424 AND rownum <= ICX_CAT_UTIL_PVT.g_batch_size
425 RETURNING rowid BULK COLLECT INTO l_rowid_tbl;
426
427 l_err_loc := 600;
428 l_row_count := SQL%ROWCOUNT;
429 IF (l_row_count = 0) THEN
430 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
431 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
432 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
433 'No rows updated in icx_cat_ctx_hdrs_tlp for category rename');
434 END IF;
435 EXIT;
436 ELSIF (l_row_count < ICX_CAT_UTIL_PVT.g_batch_size) THEN
437 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
438 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
439 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
440 'Num. of rows updated in icx_cat_ctx_hdrs_tlp for category rename:' ||
441 l_row_count);
442 END IF;
443 l_err_loc := 700;
444 l_continue := FALSE;
445 END IF;
446
447 l_err_loc := 800;
448 IF (l_searchable IS NULL) THEN
449 l_err_loc := 900;
450 ICX_CAT_BUILD_CTX_SQL_PVT.checkIfAttributeIsSrchble
451 ('SHOPPING_CATEGORY', l_searchable, l_section_tag);
452 END IF;
453
454 l_err_loc := 1000;
455 IF (l_searchable = 1) THEN
456 FORALL j IN 1..l_rowid_tbl.COUNT
457 UPDATE icx_cat_items_ctx_dtls_tlp dtls
458 SET ctx_desc = (SELECT '<' ||to_char(l_section_tag) ||'>' ||
459 gUpdCategoryNameTbl(i) || '</' ||to_char(l_section_tag) ||'>'
460 FROM icx_cat_items_ctx_hdrs_tlp hdrs
461 WHERE hdrs.rowid = l_rowid_tbl(j)
462 AND hdrs.po_line_id = dtls.po_line_id
463 AND hdrs.req_template_name = dtls.req_template_name
464 AND hdrs.req_template_line_num = dtls.req_template_line_num
465 AND hdrs.inventory_item_id = dtls.inventory_item_id
466 AND hdrs.org_id = dtls.org_id
467 AND hdrs.language = dtls.language)
468 WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow
469 AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
470 WHERE hdrs.po_line_id = dtls.po_line_id
471 AND hdrs.req_template_name = dtls.req_template_name
472 AND hdrs.req_template_line_num = dtls.req_template_line_num
473 AND hdrs.inventory_item_id = dtls.inventory_item_id
474 AND hdrs.org_id = dtls.org_id
475 AND hdrs.language = dtls.language
476 AND hdrs.rowid = l_rowid_tbl(j) );
477
478 IF (l_rowid_tbl.COUNT > 0) THEN
479 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
480 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
481 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
482 'Num. of rows updated in icx_cat_ctx_dtls_tlp for category rename:' ||
483 SQL%ROWCOUNT);
484 END IF;
485 END IF;
486 ELSE
487 l_err_loc := 1200;
488 -- Must log
489 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
491 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
492 'Shopping Category attribute is not searchable, ' ||
493 'so no changes needed in icx_cat_items_ctx_dtls_tlp; ' ||
494 'l_searchable:' || l_searchable || ', l_section_tag:' || l_section_tag );
495 END IF;
496 END IF; -- IF (l_searchable = 1) THEN
497
498 l_err_loc := 1100;
499 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
500 COMMIT;
501 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
502 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
503 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
504 'Commit done.');
505 END IF;
506 ELSE
507 l_err_loc := 1200;
508 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
509 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
510 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
511 'Commit not done.');
512 END IF;
513 END IF;
514 END LOOP;
515 l_err_loc := 1300;
516 END LOOP;
517
518 l_err_loc := 1400;
519 -- Update icx_cat_categories_tl
520 FORALL i IN 1..gUpdRtCategoryIdTbl.COUNT
521 UPDATE icx_cat_categories_tl
522 SET category_name = gUpdCategoryNameTbl(i),
523 upper_category_name = upper(gUpdCategoryNameTbl(i)),
524 source_lang = gUpdSourceLangTbl(i),
525 last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
526 last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
527 last_update_date = sysdate,
528 request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
529 program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
530 program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
531 program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
532 WHERE rt_category_id = gUpdRtCategoryIdTbl(i)
533 AND language = gUpdLanguageTbl(i);
534
535 l_err_loc := 1500;
536 IF (gUpdRtCategoryIdTbl.COUNT > 0) THEN
537 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
538 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
539 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
540 'Num. of rows updated in icx_cat_categories_tl:' ||SQL%ROWCOUNT);
541 END IF;
542 END IF;
543
544 l_err_loc := 1600;
545 clearTables(l_action_mode);
546 EXCEPTION
547 WHEN OTHERS THEN
548 logPLSQLTableRow(l_api_name, FND_LOG.LEVEL_UNEXPECTED, SQL%ROWCOUNT+1, l_action_mode);
549 ICX_CAT_UTIL_PVT.logUnexpectedException(
550 G_PKG_NAME, l_api_name,
551 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
552 RAISE;
553 END updateCategories;
554
555 -- Call this one when category is deleted from ECM UI.
556 PROCEDURE deleteCategories
557 IS
558
559 l_api_name CONSTANT VARCHAR2(30) := 'deleteCategories';
560 l_continue BOOLEAN := TRUE;
561 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
562 l_req_template_name_tbl DBMS_SQL.VARCHAR2_TABLE;
563 l_req_template_line_num_tbl DBMS_SQL.NUMBER_TABLE;
564 l_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
565 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
566 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
567 l_err_loc PLS_INTEGER;
568 l_action_mode VARCHAR2(80);
569 l_row_count PLS_INTEGER;
570 BEGIN
571 l_err_loc := 100;
572 l_action_mode := 'DELETE_CATEGORY';
573 -- When a category is deleted / is no longer active,
574 -- then we only delete the master items that are under the po category
575 -- and not delete the ip category, mappings and from hierarchy
576 -- because we should still be able to update the existing document lines
577 -- that existed in the category using pdoi.
578 -- The user cannot create new lines in the
579 -- category, which will be validated during pdoi and online doc creation.
580
581 FOR i IN 1..gDelPoCategoryIdTbl.COUNT LOOP
582 l_err_loc := 200;
583 l_continue := TRUE;
584 WHILE l_continue LOOP
585 l_err_loc := 300;
586 l_po_line_id_tbl.DELETE;
587 l_req_template_name_tbl.DELETE;
588 l_req_template_line_num_tbl.DELETE;
589 l_inventory_item_id_tbl.DELETE;
590 l_org_id_tbl.DELETE;
591 l_language_tbl.DELETE;
592
593 l_err_loc := 400;
594 -- Category deletion, in R12 we only delete the master items
595 DELETE FROM icx_cat_items_ctx_hdrs_tlp
596 WHERE po_category_id = gDelPoCategoryIdTbl(i)
597 AND source_type = 'MASTER_ITEM'
598 AND rownum <= ICX_CAT_UTIL_PVT.g_batch_size
599 RETURNING po_line_id, req_template_name, req_template_line_num,
600 inventory_item_id, org_id, language
601 BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
602 l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
603
604 l_err_loc := 500;
605 l_row_count := SQL%ROWCOUNT;
606 IF (l_row_count < ICX_CAT_UTIL_PVT.g_batch_size) THEN
607 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
609 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
610 'Num. of rows deleted from icx_cat_ctx_hdrs_tlp for category delete:' ||
611 l_row_count);
612 END IF;
613 l_continue := FALSE;
614 END IF;
615
616 l_err_loc := 600;
617 FORALL j IN 1..l_po_line_id_tbl.COUNT
618 DELETE FROM icx_cat_items_ctx_dtls_tlp
619 WHERE po_line_id = l_po_line_id_tbl(j)
620 AND req_template_name = l_req_template_name_tbl(j)
621 AND req_template_line_num = l_req_template_line_num_tbl(j)
622 AND inventory_item_id = l_inventory_item_id_tbl(j)
623 AND org_id = l_org_id_tbl(j)
624 AND language = l_language_tbl(j);
625
626 IF (l_po_line_id_tbl.COUNT > 0) THEN
627 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
629 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
630 'Num. of rows delete from icx_cat_ctx_dtls_tlp for category delete:' ||
631 SQL%ROWCOUNT);
632 END IF;
633 END IF;
634
635 l_err_loc := 700;
636 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
637 COMMIT;
638 l_err_loc := 800;
639 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
640 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
641 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
642 'Commit done. after deleting items for the category');
643 END IF;
644 ELSE
645 l_err_loc := 900;
646 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
648 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
649 'Commit not done. after deleting items for the category');
650 END IF;
651 END IF;
652 END LOOP;
653 END LOOP;
654
655 l_err_loc := 1000;
656 clearTables(l_action_mode);
657 EXCEPTION
658 WHEN OTHERS THEN
659 logPLSQLTableRow(l_api_name, FND_LOG.LEVEL_UNEXPECTED, SQL%ROWCOUNT+1, l_action_mode);
660 ICX_CAT_UTIL_PVT.logUnexpectedException(
661 G_PKG_NAME, l_api_name,
662 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
663 RAISE;
664 END deleteCategories;
665
666 PROCEDURE populateCategoryTables
667 ( p_mode IN VARCHAR2
668 )
669 IS
670 l_err_loc PLS_INTEGER;
671 l_api_name CONSTANT VARCHAR2(30) := 'populateCategoryTables';
672 BEGIN
673 l_err_loc := 100;
674 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
676 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
677 'Enter populateCategoryTables(' || p_mode ||')gTotalRowCount: ' || gTotalRowCount);
678 END IF;
679
680 l_err_loc := 200;
681 IF (p_mode = 'OUTLOOP' OR gTotalRowCount >= ICX_CAT_UTIL_PVT.g_batch_size) THEN
682 l_err_loc := 300;
683 gTotalRowCount := 0;
684
685 l_err_loc := 400;
686 IF (gInsRtCategoryIdTbl.COUNT > 0) THEN
687 addCategories;
688 ELSE
689 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
691 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
692 ' No category insert done.');
693 END IF;
694 END IF;
695
696 l_err_loc := 400;
697 IF (gInsPOCategoryIdTbl.COUNT > 0) THEN
698 addItemCategories;
699 ELSE
700 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
702 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
703 ' addItemCategories not called.');
704 END IF;
705 END IF;
706
707 l_err_loc := 500;
708 IF (gUpdRtCategoryIdTbl.COUNT > 0) THEN
709 updateCategories;
710 ELSE
711 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
712 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
713 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
714 ' No category update done.');
715 END IF;
716 END IF;
717
718 l_err_loc := 600;
719 IF (gDelPoCategoryIdTbl.COUNT > 0) THEN
720 deleteCategories;
721 ELSE
722 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
724 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
725 ' No category delete done.');
726 END IF;
727 END IF;
728
729 l_err_loc := 700;
730 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
731 COMMIT;
732 l_err_loc := 800;
733 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
734 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
735 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
736 'Commit done.');
737 END IF;
738 ELSE
739 l_err_loc := 900;
740 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
742 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
743 'Commit not done.');
744 END IF;
745 END IF;
746 l_err_loc := 1100;
747
748 END IF; --(p_mode = 'OUTLOOP' OR gTotalRowCount >= ICX_CAT_UTIL_PVT.g_batch_size)
749 l_err_loc := 1200;
750 EXCEPTION
751 WHEN OTHERS THEN
752 ICX_CAT_UTIL_PVT.logUnexpectedException(
753 G_PKG_NAME, l_api_name,
754 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
755 RAISE;
756 END populateCategoryTables;
757
758 FUNCTION validateCategoryName
759 ( p_rt_category_id IN NUMBER ,
760 p_category_name IN VARCHAR2
761 )
762 RETURN BOOLEAN
763 IS
764 l_category_name_is_valid BOOLEAN;
765 l_num_val PLS_INTEGER;
766 BEGIN
767 IF (p_rt_category_id IS NULL) THEN
768 SELECT count(1)
769 INTO l_num_val
770 FROM icx_cat_categories_tl
771 WHERE upper_category_name = UPPER(p_category_name);
772 ELSE
773 SELECT count(1)
774 INTO l_num_val
775 FROM icx_cat_categories_tl
776 WHERE upper_category_name = UPPER(p_category_name)
777 AND rt_category_id <> p_rt_category_id;
778 END IF;
779
780 IF (l_num_val > 0) THEN
781 RETURN FALSE;
782 ELSE
783 RETURN TRUE;
784 END IF;
785 END validateCategoryName;
786
787 PROCEDURE processCategory
788 ( p_catg_csr IN g_item_csr_type
789 )
790 IS
791 l_api_name CONSTANT VARCHAR2(30) := 'processCategory';
792 l_err_loc PLS_INTEGER;
793 l_batch_count PLS_INTEGER;
794 l_row_count PLS_INTEGER;
795 l_category_status PLS_INTEGER;
796 l_rt_category_id NUMBER;
797 l_prev_category_key NUMBER := -1;
798 l_prev_rt_category_id NUMBER := -1;
799 l_prev_category_name mtl_categories_tl.description%TYPE := '-1';
800 l_index PLS_INTEGER;
801 l_category_name_is_valid BOOLEAN := FALSE;
802
803 ----- Start of declaring columns selected in the cursor -----
804 l_mtl_category_id_tbl DBMS_SQL.NUMBER_TABLE;
805 l_mtl_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
806 l_mtl_language_tbl DBMS_SQL.VARCHAR2_TABLE;
807 l_mtl_source_lang_tbl DBMS_SQL.VARCHAR2_TABLE;
808 l_rt_category_id_tbl DBMS_SQL.NUMBER_TABLE;
809 l_old_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
810 l_end_date_active_tbl DBMS_SQL.DATE_TABLE;
811 l_disable_date_tbl DBMS_SQL.DATE_TABLE;
812 l_system_date_tbl DBMS_SQL.DATE_TABLE;
813
814 ------ End of declaring columns selected in the cursor ------
815
816 BEGIN
817 l_err_loc := 100;
818 l_batch_count := 0;
819 l_row_count := 0;
820 LOOP
821 l_err_loc := 200;
822 l_mtl_category_id_tbl.DELETE;
823 l_mtl_category_name_tbl.DELETE;
824 l_mtl_language_tbl.DELETE;
825 l_mtl_source_lang_tbl.DELETE;
826 l_rt_category_id_tbl.DELETE;
827 l_old_category_name_tbl.DELETE;
828 l_end_date_active_tbl.DELETE;
829 l_disable_date_tbl.DELETE;
830 l_system_date_tbl.DELETE;
831
832 l_err_loc := 300;
833 FETCH p_catg_csr BULK COLLECT INTO
834 l_mtl_category_id_tbl, l_mtl_category_name_tbl, l_mtl_language_tbl,
835 l_mtl_source_lang_tbl, l_rt_category_id_tbl, l_old_category_name_tbl,
836 l_end_date_active_tbl, l_disable_date_tbl, l_system_date_tbl
837 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
838
839 l_err_loc := 400;
840 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
842 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
843 'Num. of rows fetched: ' || to_char(l_mtl_category_id_tbl.COUNT));
844 END IF;
845
846 l_err_loc := 500;
847 EXIT WHEN l_mtl_category_id_tbl.COUNT = 0;
848
849 l_err_loc := 600;
850 l_batch_count := l_batch_count + 1;
851
852 l_err_loc := 700;
853 l_row_count := l_row_count + l_mtl_category_id_tbl.COUNT;
854
855 FOR i in 1..l_mtl_category_id_tbl.COUNT LOOP
856 l_err_loc := 800;
857 -- First get the status of the current Category line
858 l_category_status := ICX_CAT_POPULATE_STATUS_PVT.getCategoryStatus
859 (l_end_date_active_tbl(i), l_disable_date_tbl(i), l_system_date_tbl(i));
860
861 l_err_loc := 900;
862 IF (l_category_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE) THEN
863 l_err_loc := 1000;
864 -- Check for the category name uniqueness in ip
865 IF (l_prev_category_key <> l_mtl_category_id_tbl(i) OR
866 l_prev_category_name <> l_mtl_category_name_tbl(i))
867 THEN
868 l_category_name_is_valid :=
869 validateCategoryName(l_rt_category_id_tbl(i), l_mtl_category_name_tbl(i));
870
871 IF (NOT l_category_name_is_valid) THEN
872 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
873 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
874 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
875 'Row, with l_mtl_category_id_tbl:' || l_mtl_category_id_tbl(i) ||
876 ', l_rt_category_id_tbl:' || l_rt_category_id_tbl(i) ||
877 ', l_mtl_category_name_tbl:' || l_mtl_category_name_tbl(i) ||
878 ', l_old_category_name_tbl:' || l_old_category_name_tbl(i) ||
879 '; will not be processed as Category name already exists in IP');
880 END IF;
881 END IF;
882 END IF;
883
884 IF (l_rt_category_id_tbl(i) IS NULL) THEN
885 -- Get the next rt_category_id from the sequence only once for all the language rows of a category
886 IF (l_prev_category_key = l_mtl_category_id_tbl(i)) THEN
887 l_err_loc := 1100;
888 l_rt_category_id := l_prev_rt_category_id;
889 ELSE
890 l_err_loc := 1200;
891 SELECT icx_por_categoryid.nextval
892 INTO l_rt_category_id
893 FROM dual;
894
895 l_err_loc := 1300;
896 -- The pl/sql table used to create mappings. Need only one row for a language
897 -- the mapping rows will be populated only if this was a create category action
898 IF (g_DML_TYPE = ICX_CAT_POPULATE_CATG_PVT.g_DML_INSERT_TYPE AND
899 g_auto_create_shop_catg = 'Y' AND
900 l_category_name_is_valid)
901 THEN
902 l_err_loc := 1400;
903 gTotalRowCount := gTotalRowCount + 2;
904 l_index := gInsMapRtCategoryIdTbl.COUNT + 1;
905 gInsMapRtCategoryIdTbl(l_index) := l_rt_category_id;
906 gInsMapCategoryKeyTbl(l_index) := to_char(l_mtl_category_id_tbl(i));
907 gInsMapLanguageTbl(l_index) := l_mtl_language_tbl(i);
908 END IF;
909
910 gTotalRowCount := gTotalRowCount + 1;
911 l_index := gInsPOCategoryIdTbl.COUNT + 1;
912 gInsPOCategoryIdTbl(l_index) := l_mtl_category_id_tbl(i);
913 END IF;
914 IF (g_auto_create_shop_catg = 'Y' AND
915 l_category_name_is_valid)
916 THEN
917 l_err_loc := 1600;
918 -- Add new category only if the profile is set to Yes
919 gTotalRowCount := gTotalRowCount + 1;
920 l_index := gInsRtCategoryIdTbl.COUNT + 1;
921 gInsRtCategoryIdTbl(l_index) := l_rt_category_id;
922 gInsCategoryKeyTbl(l_index) := to_char(l_mtl_category_id_tbl(i));
923 gInsCategoryNameTbl(l_index) := l_mtl_category_name_tbl(i);
924 gInsLanguageTbl(l_index) := l_mtl_language_tbl(i);
925 gInsSourceLangTbl(l_index) := l_mtl_source_lang_tbl(i);
926 END IF;
927 ELSE -- IF (l_rt_category_id_tbl(i) IS NULL) THEN
928 l_err_loc := 1700;
929 IF (l_old_category_name_tbl(i) IS NULL) THEN
930 IF (g_auto_create_shop_catg = 'Y' AND
931 l_category_name_is_valid)
932 THEN
933 l_err_loc := 1800;
934 -- Translation row added for the category
935 gTotalRowCount := gTotalRowCount + 1;
936 l_index := gInsRtCategoryIdTbl.COUNT + 1;
937 gInsRtCategoryIdTbl(l_index) := l_rt_category_id_tbl(i);
938 gInsCategoryKeyTbl(l_index) := to_char(l_mtl_category_id_tbl(i));
939 gInsCategoryNameTbl(l_index) := l_mtl_category_name_tbl(i);
940 gInsLanguageTbl(l_index) := l_mtl_language_tbl(i);
941 gInsSourceLangTbl(l_index) := l_mtl_source_lang_tbl(i);
942 END IF;
943 ELSE
944 l_err_loc := 1900;
945 -- Update of the category
946 IF (l_mtl_category_name_tbl(i) <> l_old_category_name_tbl(i) AND
947 l_category_name_is_valid)
948 THEN
949 l_err_loc := 2000;
950 gTotalRowCount := gTotalRowCount + 1;
951 l_index := gUpdRtCategoryIdTbl.COUNT + 1;
952 gUpdRtCategoryIdTbl(l_index) := l_rt_category_id_tbl(i);
953 gUpdCategoryNameTbl(l_index) := l_mtl_category_name_tbl(i);
954 gUpdLanguageTbl(l_index) := l_mtl_language_tbl(i);
955 gUpdSourceLangTbl(l_index) := l_mtl_source_lang_tbl(i);
956 ELSE
957 l_err_loc := 2100;
958 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
959 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
960 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
961 'Row #:' || i ||
962 ', with l_mtl_category_id_tbl:' || l_mtl_category_id_tbl(i) ||
963 ', l_rt_category_id_tbl:' || l_rt_category_id_tbl(i) ||
964 ', l_mtl_category_name_tbl:' || l_mtl_category_name_tbl(i) ||
965 ', l_old_category_name_tbl:' || l_old_category_name_tbl(i) ||
966 '; Category name is the same, no action needed');
967 END IF;
968 END IF; -- IF (l_mtl_category_name_tbl(i) <> l_old_category_name_tbl(i)) THEN
969 END IF; -- IF (l_old_category_name_tbl(i) IS NULL) THEN
970 END IF; -- IF (l_rt_category_id_tbl(i) IS NULL) THEN
971 ELSE -- l_category_status IS NOT VALID
972 l_err_loc := 2200;
973 IF (l_prev_category_key <> l_mtl_category_id_tbl(i)) THEN
974 -- Category is not valid any more and needs to be deleted
975 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
976 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
977 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
978 'Row #:' || i ||
979 ', with l_mtl_category_id_tbl:' || l_mtl_category_id_tbl(i) ||
980 ', l_rt_category_id_tbl:' || l_rt_category_id_tbl(i) ||
981 ', l_end_date_active_tbl:' || l_end_date_active_tbl(i) ||
982 ', l_disable_date_tbl:' || l_disable_date_tbl(i) ||
983 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
984 '; is invalid and has to be deleted');
985 END IF;
986 gTotalRowCount := gTotalRowCount + 1;
987 l_index := gDelPoCategoryIdTbl.COUNT + 1;
988 gDelPoCategoryIdTbl(l_index) := l_mtl_category_id_tbl(i);
989 END IF;
990 END IF; -- IF (l_category_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE) THEN
991
992 l_err_loc := 1500;
993 l_prev_category_key := l_mtl_category_id_tbl(i);
994 l_prev_rt_category_id := l_rt_category_id;
995 l_prev_category_name := l_mtl_category_name_tbl(i);
996
997 l_err_loc := 2300;
998 populateCategoryTables('INLOOP');
999 END LOOP; --FOR LOOP of l_mtl_category_id_tbl
1000
1001 l_err_loc := 2400;
1002 EXIT WHEN l_mtl_category_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1003 END LOOP; --Cursor loop
1004
1005 l_err_loc := 2500;
1006 populateCategoryTables('OUTLOOP');
1007
1008 l_err_loc := 2600;
1009 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1011 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1012 ' done; '||
1013 'Total num. of batches processed:' ||l_batch_count ||
1014 ', Total num. of rows processed:' ||l_row_count);
1015 END IF;
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 ICX_CAT_UTIL_PVT.logUnexpectedException(
1019 G_PKG_NAME, l_api_name,
1020 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1021 RAISE;
1022 END processCategory;
1023
1024 PROCEDURE openCategoryCursor
1025 ( P_CATEGORY_ID IN NUMBER
1026 )
1027 IS
1028 l_api_name CONSTANT VARCHAR2(30) := 'openCategoryCursor';
1029 l_err_loc PLS_INTEGER;
1030 l_catg_csr g_item_csr_type;
1031 BEGIN
1032 l_err_loc := 100;
1033 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1034 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1035 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1036 'Processing cursor:' || l_api_name ||
1037 '; p_category_id:' || p_category_id ||
1038 ', g_structure_id:' || ICX_CAT_UTIL_PVT.g_structure_id);
1039 END IF;
1040
1041 l_err_loc := 200;
1042 --First close the cursor
1043 IF (l_catg_csr%ISOPEN) THEN
1044 l_err_loc := 200;
1045 CLOSE l_catg_csr;
1046 END IF;
1047
1048 l_err_loc := 300;
1049 OPEN l_catg_csr FOR
1050 SELECT DISTINCT mck.category_id category_id,
1051 nvl(mctl.description, mck.concatenated_segments) category_name,
1052 mctl.language language, mctl.source_lang source_lang,
1053 icat.rt_category_id rt_category_id, icat2.category_name old_category_name,
1054 nvl(mck.end_date_active, SYSDATE+1), nvl(mck.disable_date, SYSDATE+1),
1055 SYSDATE system_date
1056 FROM mtl_categories_kfv mck,
1057 mtl_categories_tl mctl,
1058 icx_cat_categories_tl icat,
1059 icx_cat_categories_tl icat2
1060 WHERE mck.category_id = P_CATEGORY_ID
1061 AND mck.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
1062 AND mctl.category_id = mck.category_id
1063 AND mctl.language IN (SELECT language_code FROM fnd_languages WHERE installed_flag IN ('B', 'I'))
1064 AND to_char(mctl.category_id) = icat.key (+)
1065 AND to_char(mctl.category_id) = icat2.key (+)
1066 AND mctl.language = icat2.language (+)
1067 ORDER BY 1;
1068
1069 l_err_loc := 400;
1070 processCategory(l_catg_csr);
1071
1072 l_err_loc := 500;
1073 CLOSE l_catg_csr;
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 ICX_CAT_UTIL_PVT.logUnexpectedException(
1078 G_PKG_NAME, l_api_name,
1079 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1080 RAISE;
1081 END openCategoryCursor;
1082
1083 PROCEDURE openValidCategorySetCursor
1084 ( P_CATEGORY_ID IN NUMBER
1085 )
1086 IS
1087 l_api_name CONSTANT VARCHAR2(30) := 'openValidCategorySetCursor';
1088 l_err_loc PLS_INTEGER;
1089 l_catg_csr g_item_csr_type;
1090 BEGIN
1091 l_err_loc := 100;
1092 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1094 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1095 'Processing cursor:' || l_api_name ||
1096 '; p_category_id:' || p_category_id ||
1097 ', g_structure_id:' || ICX_CAT_UTIL_PVT.g_structure_id ||
1098 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id);
1099 END IF;
1100
1101 l_err_loc := 150;
1102 --First close the cursor
1103 IF (l_catg_csr%ISOPEN) THEN
1104 l_err_loc := 200;
1105 CLOSE l_catg_csr;
1106 END IF;
1107
1108 l_err_loc := 300;
1109 OPEN l_catg_csr FOR
1110 SELECT DISTINCT mck.category_id category_id,
1111 nvl(mctl.description, mck.concatenated_segments) category_name,
1112 mctl.language language, mctl.source_lang source_lang,
1113 icat.rt_category_id rt_category_id, icat2.category_name old_category_name,
1114 nvl(mck.end_date_active, SYSDATE+1), nvl(mck.disable_date, SYSDATE+1),
1115 SYSDATE system_date
1116 FROM mtl_categories_kfv mck,
1117 mtl_categories_tl mctl,
1118 mtl_category_set_valid_cats mcsvc,
1119 icx_cat_categories_tl icat,
1120 icx_cat_categories_tl icat2
1121 WHERE mck.category_id = P_CATEGORY_ID
1122 AND mck.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
1123 AND mctl.category_id = mck.category_id
1124 AND mctl.language IN (SELECT language_code FROM fnd_languages WHERE installed_flag IN ('B', 'I'))
1125 AND to_char(mctl.category_id) = icat.key (+)
1126 AND to_char(mctl.category_id) = icat2.key (+)
1127 AND mctl.language = icat2.language (+)
1128 AND mcsvc.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
1129 AND mcsvc.category_id = mck.category_id
1130 ORDER BY 1;
1131
1132 l_err_loc := 400;
1133 processCategory(l_catg_csr);
1134
1135 l_err_loc := 500;
1136 CLOSE l_catg_csr;
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 ICX_CAT_UTIL_PVT.logUnexpectedException(
1141 G_PKG_NAME, l_api_name,
1142 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1143 RAISE;
1144 END openValidCategorySetCursor;
1145
1146 PROCEDURE populateCategoryChange
1147 ( P_CATEGORY_NAME IN VARCHAR2 ,
1148 P_CATEGORY_ID IN NUMBER
1149 )
1150 IS
1151 l_api_name CONSTANT VARCHAR2(30) := 'populateCategoryChange';
1152 l_err_loc PLS_INTEGER;
1153 BEGIN
1154 l_err_loc := 100;
1155 ICX_CAT_UTIL_PVT.setBatchSize;
1156
1157 l_err_loc := 300;
1158 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1159
1160 l_err_loc := 400;
1161 setAutoCreateShopCatg;
1162
1163 -- TODO: Check this comment
1164 -- Don't need to process items in the category, due to the following reasons:
1165 -- 1. Category may have just been created, so no item assignment is done so far.
1166 -- 2. If the category is updated, even if the category was not present in iProcurement
1167 -- we donot create mapping for the category, so items cannot be processed.
1168
1169 IF (ICX_CAT_UTIL_PVT.g_validate_flag = 'N') THEN
1170 l_err_loc := 500;
1171 openCategoryCursor(P_CATEGORY_ID);
1172 ELSE
1173 l_err_loc := 600;
1174 openValidCategorySetCursor(P_CATEGORY_ID);
1175 END IF;
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 ICX_CAT_UTIL_PVT.logUnexpectedException(
1179 G_PKG_NAME, l_api_name,
1180 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1181 RAISE;
1182 END populateCategoryChange;
1183
1184 PROCEDURE populateValidCategorySetInsert
1185 ( P_CATEGORY_ID IN NUMBER
1186 )
1187 IS
1188 l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetInsert';
1189 l_err_loc PLS_INTEGER;
1190 BEGIN
1191 l_err_loc := 100;
1192 ICX_CAT_UTIL_PVT.setBatchSize;
1193
1194 l_err_loc := 300;
1195 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1196
1197 l_err_loc := 400;
1198 setAutoCreateShopCatg;
1199
1200 l_err_loc := 600;
1201 openValidCategorySetCursor(P_CATEGORY_ID);
1202
1203 l_err_loc := 700;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 ICX_CAT_UTIL_PVT.logUnexpectedException(
1207 G_PKG_NAME, l_api_name,
1208 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1209 RAISE;
1210 END populateValidCategorySetInsert;
1211
1212 PROCEDURE populateValidCategorySetUpdate
1213 ( P_OLD_CATEGORY_ID IN NUMBER ,
1214 P_NEW_CATEGORY_ID IN NUMBER
1215 )
1216 IS
1217 l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetUpdate';
1218 l_err_loc PLS_INTEGER;
1219 BEGIN
1220 l_err_loc := 100;
1221 ICX_CAT_UTIL_PVT.setBatchSize;
1222
1223 l_err_loc := 300;
1224 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1225
1226 l_err_loc := 400;
1227 setAutoCreateShopCatg;
1228
1229 l_err_loc := 500;
1230 populateValidCategorySetDelete(P_OLD_CATEGORY_ID);
1231
1232 l_err_loc := 600;
1233 -- TODO: Check this comment
1234 -- ICX_CAT_POPULATE_CATG_PVT.g_DML_TYPE is used to decide whether to create the mapping or not
1235 -- So when a validate category set is updated with a new category it is really a
1236 -- valid category set insert.
1237 ICX_CAT_POPULATE_CATG_PVT.g_DML_TYPE := ICX_CAT_POPULATE_CATG_PVT.g_DML_INSERT_TYPE;
1238
1239 l_err_loc := 700;
1240 openValidCategorySetCursor(P_NEW_CATEGORY_ID);
1241
1242 l_err_loc := 800;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 ICX_CAT_UTIL_PVT.logUnexpectedException(
1246 G_PKG_NAME, l_api_name,
1247 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1248 RAISE;
1249 END populateValidCategorySetUpdate;
1250
1251 PROCEDURE populateValidCategorySetDelete
1252 ( P_CATEGORY_ID IN NUMBER
1253 )
1254 IS
1255 l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetDelete';
1256 l_err_loc PLS_INTEGER;
1257 l_index PLS_INTEGER;
1258 BEGIN
1259 l_err_loc := 100;
1260 ICX_CAT_UTIL_PVT.setBatchSize;
1261
1262 l_err_loc := 200;
1263 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1264
1265 l_err_loc := 300;
1266 gDelPoCategoryIdTbl.DELETE;
1267
1268 l_err_loc := 400;
1269 l_index := gDelPoCategoryIdTbl.COUNT + 1;
1270 gDelPoCategoryIdTbl(l_index) := P_CATEGORY_ID;
1271
1272 l_err_loc := 500;
1273 deleteCategories;
1274
1275 l_err_loc := 600;
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 ICX_CAT_UTIL_PVT.logUnexpectedException(
1279 G_PKG_NAME, l_api_name,
1280 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1281 RAISE;
1282 END populateValidCategorySetDelete;
1283
1284 PROCEDURE setAutoCreateShopCatg
1285 IS
1286 l_api_name CONSTANT VARCHAR2(30) := 'setAutoCreateShopCatg';
1287 l_err_loc PLS_INTEGER;
1288 BEGIN
1289 l_err_loc := 100;
1290 fnd_profile.get('POR_AUTO_CREATE_SHOPPING_CAT', g_auto_create_shop_catg);
1291 IF (g_auto_create_shop_catg IS NULL) THEN
1292 l_err_loc := 200;
1293 g_auto_create_shop_catg := 'N';
1294 END IF;
1295
1296 l_err_loc := 300;
1297 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1298 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1299 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1300 'Auto create shop category set to:' || g_auto_create_shop_catg);
1301 END IF;
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 ICX_CAT_UTIL_PVT.logUnexpectedException(
1305 G_PKG_NAME, l_api_name,
1306 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1307 g_auto_create_shop_catg := 'N';
1308 END setAutoCreateShopCatg;
1309
1310 END ICX_CAT_POPULATE_CATG_PVT;