[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXT_CLASS
Source
1 PACKAGE BODY ICX_POR_EXT_CLASS AS
2 /* $Header: ICXEXTCB.pls 115.28 2004/03/31 18:46:06 vkartik ship $*/
3
4 --------------------------------------------------------------
5 -- Type and Cursor --
6 --------------------------------------------------------------
7
8 TYPE tClassification IS RECORD (
9 category_id NUMBER,
10 category_name ICX_CAT_CATEGORIES_TL.CATEGORY_NAME%TYPE,
11 language VARCHAR2(4),
12 source_lang VARCHAR2(4),
13 rt_category_id NUMBER,
14 old_category_name ICX_CAT_CATEGORIES_TL.CATEGORY_NAME%TYPE);
15
16 TYPE tCursorType IS REF CURSOR;
17
18 --------------------------------------------------------------
19 -- Global Variables --
20 --------------------------------------------------------------
21 gLastCategoryKey icx_cat_categories_TL.KEY%TYPE;
22 gLastRtCategoryId NUMBER := -1;
23 gCompletedCount PLS_INTEGER := 0;
24
25 --------------------------------------------------------------
26 -- Global Tables --
27 --------------------------------------------------------------
28 gAddRtCategoryIds DBMS_SQL.NUMBER_TABLE;
29 gAddCategoryKeys DBMS_SQL.VARCHAR2_TABLE;
30 gAddCategoryNames DBMS_SQL.VARCHAR2_TABLE;
31 gAddLanguages DBMS_SQL.VARCHAR2_TABLE;
32 gAddSourceLangs DBMS_SQL.VARCHAR2_TABLE;
33 gAddNewRtCategoryIds DBMS_SQL.NUMBER_TABLE;
34 gAddNewCategoryKeys DBMS_SQL.VARCHAR2_TABLE;
35
36 gUpdateRtCategoryIds DBMS_SQL.NUMBER_TABLE;
37 gUpdateCategoryKeys DBMS_SQL.VARCHAR2_TABLE;
38 gUpdateCategoryNames DBMS_SQL.VARCHAR2_TABLE;
39 gUpdateLanguages DBMS_SQL.VARCHAR2_TABLE;
40 gUpdateSourceLangs DBMS_SQL.VARCHAR2_TABLE;
41
42 --------------------------------------------------------------
43 -- Global PL/SQL Tables --
47 BEGIN
44 --------------------------------------------------------------
45
46 PROCEDURE clearTables(pMode IN VARCHAR2) IS
48 IF (pMode IN ('ALL', 'ADD')) THEN
49 gAddRtCategoryIds.DELETE;
50 gAddCategoryKeys.DELETE;
51 gAddCategoryNames.DELETE;
52 gAddLanguages.DELETE;
53 gAddSourceLangs.DELETE;
54 gAddNewRtCategoryIds.DELETE;
55 gAddNewCategoryKeys.DELETE;
56 END IF;
57
58 IF (pMode IN ('ALL', 'UPDATE')) THEN
59 gUpdateRtCategoryIds.DELETE;
60 gUpdateCategoryKeys.DELETE;
61 gUpdateCategoryNames.DELETE;
62 gUpdateLanguages.DELETE;
63 gUpdateSourceLangs.DELETE;
64 END IF;
65 END;
66
67 --------------------------------------------------------------
68 -- Snap Shots --
69 --------------------------------------------------------------
70
71 FUNCTION snapShot(pIndex IN PLS_INTEGER,
72 pMode IN VARCHAR2) RETURN varchar2 IS
73 xShot varchar2(2000) := 'SnapShot('||pMode||')['||pIndex||']--';
74 BEGIN
75 IF (pMode = 'ADD') THEN
76 xShot := xShot || ' gAddRtCategoryId: ' ||
77 ICX_POR_EXT_UTL.getTableElement(gAddRtCategoryIds, pIndex) || ',';
78 xShot := xShot || ' gAddCategoryKey: '||
79 ICX_POR_EXT_UTL.getTableElement(gAddCategoryKeys, pIndex) || ',';
80 xShot := xShot || ' gAddCategoryName: '||
81 ICX_POR_EXT_UTL.getTableElement(gAddCategoryNames, pIndex) || ',';
82 xShot := xShot || ' gAddLanguage: '||
83 ICX_POR_EXT_UTL.getTableElement(gAddLanguages, pIndex) || ',';
84 xShot := xShot || ' gAddSourceLang: '||
85 ICX_POR_EXT_UTL.getTableElement(gAddSourceLangs, pIndex);
86 ELSIF (pMode = 'ADDNEW') THEN
87 xShot := xShot || ' gAddNewRtCategoryId: '||
88 ICX_POR_EXT_UTL.getTableElement(gAddNewRtCategoryIds, pIndex) || ',';
89 xShot := xShot || ' gAddNewCategoryKey: ' ||
90 ICX_POR_EXT_UTL.getTableElement(gAddNewCategoryKeys, pIndex);
91 ELSIF (pMode = 'UPDATE') THEN
92 xShot := xShot || ' gUpdateRtCategoryId: ' ||
93 ICX_POR_EXT_UTL.getTableElement(gUpdateRtCategoryIds, pIndex) || ',';
94 xShot := xShot || ' gUpdateCategoryKey: ' ||
95 ICX_POR_EXT_UTL.getTableElement(gUpdateCategoryKeys, pIndex) || ',';
96 xShot := xShot || ' gUpdateCategoryName: ' ||
97 ICX_POR_EXT_UTL.getTableElement(gUpdateCategoryNames, pIndex) || ',';
98 xShot := xShot || ' gUpdateLanguage: ' ||
99 ICX_POR_EXT_UTL.getTableElement(gUpdateLanguages, pIndex) || ',';
100 xShot := xShot || ' gUpdateSourceLang: ' ||
101 ICX_POR_EXT_UTL.getTableElement(gUpdateSourceLangs, pIndex);
102 END IF;
103
104 RETURN xShot;
105 END snapShot;
106
107 --------------------------------------------------------------
108 -- Sync Category Procedures --
109 --------------------------------------------------------------
110
111 -- Add categories
112 PROCEDURE addCategories(pType IN PLS_INTEGER) IS
113 xErrLoc PLS_INTEGER := 100;
114 xMode VARCHAR2(20) := 'ADD';
115
116 CURSOR cRebuildItems(p_rt_category_id IN NUMBER,
117 p_language IN VARCHAR2) IS
118 SELECT rowid
119 FROM icx_cat_items_tlp
120 WHERE primary_category_id = p_rt_category_id
121 AND language = p_language;
122
123 xRowIds DBMS_SQL.UROWID_TABLE;
124
125 BEGIN
126
127 IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
128 FOR i in 1..gAddRtCategoryIds.COUNT LOOP
129 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL, snapShot(i, 'ADD'));
130 END LOOP;
131 FOR i in 1..gAddNewRtCategoryIds.COUNT LOOP
132 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL, snapShot(i, 'ADDNEW'));
133 END LOOP;
134 END IF;
135
136 xErrLoc := 200;
137
138 -- Add categories
139 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
140 ICX_POR_EXT_UTL.debug('Insert icx_cat_categories_tl');
141 END IF;
142
143 FORALL i IN 1..gAddRtCategoryIds.COUNT
144 INSERT INTO icx_cat_categories_tl(
145 rt_category_id, category_name, key, title, type, language,
146 source_lang, upper_category_name, upper_key, section_map,
147 created_by, creation_date, last_updated_by, last_update_date,
148 last_update_login, request_id, program_application_id,
149 program_id, program_update_date)
150 VALUES(gAddRtCategoryIds(i),gAddCategoryNames(i),
151 gAddCategoryKeys(i), 'Oracle', pType,
152 gAddLanguages(i), gAddSourceLangs(i),
153 upper(gAddCategoryNames(i)), upper(gAddCategoryKeys(i)),
154 rpad('0', 300, 0),
155 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
156 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
157 ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
158 ICX_POR_EXTRACTOR.gProgramApplicationId,
159 ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
160
161 -- Duplicate template headers accross all installed languages
162 -- For categories, we don't replicate, just take it as is
163 IF (pType = TEMPLATE_HEADER_TYPE) THEN
164 -- template header
165 xErrLoc := 300;
166 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
167 ICX_POR_EXT_UTL.debug('Replicate template headers accross ' ||
168 'installed languages');
169 END IF;
170 -- Replicate accross installed langs
171 FORALL i IN 1..gAddRtCategoryIds.COUNT
172 INSERT INTO icx_cat_categories_tl(
173 rt_category_id, category_name, key, title, type, language,
174 source_lang, upper_category_name, upper_key, section_map,
178 SELECT gAddRtCategoryIds(i), gAddCategoryNames(i),
175 created_by, creation_date, last_updated_by, last_update_date,
176 last_update_login, request_id, program_application_id,
177 program_id, program_update_date)
179 gAddCategoryKeys(i), 'Oracle', TEMPLATE_HEADER_TYPE,
180 language_code, gAddLanguages(i),
181 upper(gAddCategoryNames(i)), upper(gAddCategoryKeys(i)),
182 rpad('0', 300, 0),
183 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
184 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
185 ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
186 ICX_POR_EXTRACTOR.gProgramApplicationId,
187 ICX_POR_EXTRACTOR.gProgramId, SYSDATE
188 FROM fnd_languages
189 WHERE installed_flag = 'I';
190
191 END IF;
192
193 xErrLoc := 400;
194 xMode := 'ADDNEW';
195
196 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
197 ICX_POR_EXT_UTL.debug('Create ICX_POR_CATEGORY_DATA_SOURCES records');
198 END IF;
199
200 -- Creates ICX_POR_CATEGORY_DATA_SOURCES records
201
202 -- Bug#3011247 : srmani - Adding back the Not Exists Clause.
203 -- Workaround, as Bulktable cannot be referenced inside a not exists clause.
204
205 FORALL i IN 1..gAddRtCategoryIds.COUNT
206 INSERT INTO icx_por_category_data_sources (
207 -- Bug: 3291430 - Also populate rt_category_id with value from icx_cat_categories_tl
208 rt_category_id,
209 category_key,
210 external_source, external_source_key,
211 created_by, creation_date, last_updated_by, last_update_date,
212 last_update_login, request_id, program_application_id,
213 program_id, program_update_date)
214 SELECT rt_category_id, key,'Oracle', key,
215 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
216 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
217 ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
218 ICX_POR_EXTRACTOR.gProgramApplicationId,
219 ICX_POR_EXTRACTOR.gProgramId, SYSDATE
220 FROM icx_cat_categories_tl
221 WHERE
222 rt_category_id = gAddRtCategoryIds(i) and
223 language = gAddLanguages(i) and
224 not exists (select 1
225 from icx_por_category_data_sources
226 where external_source = 'Oracle'
227 and external_source_key = key);
228
229 xErrLoc := 500;
230 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
231 ICX_POR_EXT_UTL.debug('Create ICX_POR_CATEGORY_ORDER_MAP records');
232 END IF;
233
234 -- Creates ICX_POR_CATEGORY_ORDER_MAP records
235 IF (pType = CATEGORY_TYPE) THEN
236 FORALL i IN 1..gAddNewRtCategoryIds.COUNT
237 INSERT INTO icx_por_category_order_map (
238 rt_category_id, external_source, external_source_key,
239 created_by, creation_date, last_updated_by,
240 last_update_date, last_update_login)
241 VALUES(gAddNewRtCategoryIds(i), 'Oracle', gAddNewCategoryKeys(i),
242 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
243 ICX_POR_EXTRACTOR.gUserId, SYSDATE,
244 ICX_POR_EXTRACTOR.gLoginId);
245 END IF;
246
247 xErrLoc := 600;
248 ICX_POR_EXT_UTL.extAFCommit;
249 clearTables('ADD');
250
251 EXCEPTION
252 when others then
253 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.addCategories-'||
254 xErrLoc||' '||SQLERRM);
255 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
256 -- rollback;
257 ICX_POR_EXT_UTL.extRollback;
258
259 raise ICX_POR_EXT_UTL.gException;
260
261 END addCategories;
262
263 -- Update categories
264 PROCEDURE updateCategories(pType IN PLS_INTEGER) IS
265 xErrLoc PLS_INTEGER := 100;
266 xMode VARCHAR2(20) := 'UPDATE';
267
268 CURSOR cRebuildItems(p_rt_category_id IN NUMBER,
269 p_language IN VARCHAR2) IS
270 SELECT rowid
271 FROM icx_cat_items_tlp
272 WHERE primary_category_id = p_rt_category_id
273 AND language = p_language;
274
275 xRowIds DBMS_SQL.UROWID_TABLE;
276
277 BEGIN
278
279 IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
280 FOR i in 1..gUpdateRtCategoryIds.COUNT LOOP
281 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL, snapShot(i, xMode));
282 END LOOP;
283 END IF;
284
285 xErrLoc := 100;
286 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
287 ICX_POR_EXT_UTL.debug('Set rebuild job num');
288 END IF;
289 -- set rebuild_job since category name is changed
290
291 IF (pType = CATEGORY_TYPE) THEN
292 -- Only consider regular category, not template because
293 -- the intermedia index string doesn't contain template name
294 -- Primary category only store regular category
295
296 FOR i IN 1..gUpdateRtCategoryIds.COUNT LOOP
297
298 OPEN cRebuildItems(gUpdateRtCategoryIds(i), gUpdateLanguages(i));
299
300 LOOP
301 xErrLoc := 200;
302 xRowIds.DELETE;
303
304 xErrLoc := 220;
305 FETCH cRebuildItems
306 BULK COLLECT INTO xRowIds
307 LIMIT ICX_POR_EXT_UTL.gCommitSize;
308 EXIT WHEN xRowIds.COUNT = 0;
309
310 xErrLoc := 240;
311 FORALL j IN 1..xRowIds.COUNT
312 UPDATE icx_cat_items_tlp
313 SET primary_category_name = gUpdateCategoryNames(i),
317 request_id = ICX_POR_EXTRACTOR.gRequestId,
314 last_updated_by = ICX_POR_EXTRACTOR.gUserId,
315 last_update_date = SYSDATE,
316 last_update_login = ICX_POR_EXTRACTOR.gLoginId,
318 program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
319 program_id = ICX_POR_EXTRACTOR.gProgramId,
320 program_update_date = SYSDATE
321 WHERE rowid = xRowIds(j);
322
323 ICX_POR_EXT_UTL.extAFCommit;
324
325 END LOOP;
326
327 xErrLoc := 300;
328 CLOSE cRebuildItems;
329 END LOOP;
330
331 END IF;
332
333 xErrLoc := 400;
334 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
335 ICX_POR_EXT_UTL.debug('Update ICX_CAT_CATEGORIES_TL');
336 END IF;
337
338 FORALL i IN 1..gUpdateRtCategoryIds.COUNT
339 UPDATE icx_cat_categories_tl
340 SET category_name = gUpdateCategoryNames(i),
341 upper_category_name = upper(gUpdateCategoryNames(i)),
342 source_lang = gUpdateSourceLangs(i),
343 last_updated_by = ICX_POR_EXTRACTOR.gUserId,
344 last_update_date = SYSDATE,
345 last_update_login = ICX_POR_EXTRACTOR.gLoginId,
346 request_id = ICX_POR_EXTRACTOR.gRequestId,
347 program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
348 program_id = ICX_POR_EXTRACTOR.gProgramId,
349 program_update_date = SYSDATE
350 WHERE rt_category_id = gUpdateRtCategoryIds(i)
351 AND language = gUpdateLanguages(i);
352
353 ICX_POR_EXT_UTL.extAFCommit;
354
355 xErrLoc := 500;
356 clearTables(xMode);
357
358 EXCEPTION
359 when others then
360 IF (cRebuildItems%ISOPEN) THEN
361 CLOSE cRebuildItems;
362 END IF;
363
364 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.updateCategories-'||
365 xErrLoc||' '||SQLERRM);
366 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
367
368 ICX_POR_EXT_UTL.extRollback;
369
370 raise ICX_POR_EXT_UTL.gException;
371
372 END updateCategories;
373
374 -- Process batch data
375 PROCEDURE processBatchData(pType IN PLS_INTEGER,
376 pMode IN VARCHAR2) IS
377 xErrLoc PLS_INTEGER := 100;
378
379 BEGIN
380 xErrLoc := 100;
381
382 IF (pMode = 'OUTLOOP' OR
383 -- Since we will commit the changes as a transaction in addCategories,
384 -- we need to re-calculate the correct commit size
385 (pType = CATEGORY_TYPE AND
386 (ICX_POR_EXT_UTL.gCommitSize < 3 OR
387 gAddRtCategoryIds.COUNT >= ICX_POR_EXT_UTL.gCommitSize/3)) OR
388 (pType = TEMPLATE_HEADER_TYPE AND
389 (ICX_POR_EXT_UTL.gCommitSize <
390 ICX_POR_EXTRACTOR.gInstalledLanguageCount+2 OR
391 gAddRtCategoryIds.COUNT >=
392 ICX_POR_EXT_UTL.gCommitSize/
393 (ICX_POR_EXTRACTOR.gInstalledLanguageCount+2))))
394 THEN
395 xErrLoc := 200;
396 IF (pType = CATEGORY_TYPE) THEN
397 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
398 'Process batch category extract -- Pending[Insert: ' ||
399 gAddRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');
400 ELSE
401 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
402 'Process batch template header extract -- Pending[Insert: ' ||
403 gAddRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');
404 END IF;
405
406 gCompletedCount := gCompletedCount + gAddRtCategoryIds.COUNT;
407 addCategories(pType);
408 END IF;
409
410 IF (pMode = 'OUTLOOP' OR
411 gUpdateRtCategoryIds.COUNT >= ICX_POR_EXT_UTL.gCommitSize)
412 THEN
413 xErrLoc := 300;
414 IF (pType = CATEGORY_TYPE) THEN
415 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
416 'Process batch category extract -- Pending[Update: ' ||
417 gUpdateRtCategoryIds.COUNT || '], Completed[' ||
418 gCompletedCount || ']');
419 ELSE
420 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
421 'Process batch template header extract -- Pending[Update: ' ||
422 gUpdateRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');
423 END IF;
424
425 gCompletedCount := gCompletedCount + gUpdateRtCategoryIds.COUNT;
426 updateCategories(pType);
427 END IF;
428
429 xErrLoc := 400;
430 EXCEPTION
431 when ICX_POR_EXT_UTL.gException then
432 -- rollback;
433 ICX_POR_EXT_UTL.extRollback;
434
435 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.processBatchData-'||
436 xErrLoc);
437 raise ICX_POR_EXT_UTL.gException;
438 when others then
439 -- rollback;
440 ICX_POR_EXT_UTL.extRollback;
441
442 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.processBatchData-'||
443 xErrLoc||' '||SQLERRM);
444 raise ICX_POR_EXT_UTL.gException;
445 END processBatchData;
446
447 --------------------------------------------------------------
448 -- Extract Classfication Data --
449 --------------------------------------------------------------
450 -- Open classification cursor
451 PROCEDURE openClassCursor(pType IN PLS_INTEGER,
452 pCursor IN OUT NOCOPY tCursorType)
453 IS
454 xCategorySetId NUMBER;
455 xValidateFlag VARCHAR2(1);
456 xStructureId NUMBER;
457 xLastRunDate DATE;
458 xString VARCHAR2(4000);
459 xErrLoc PLS_INTEGER := 100;
460
461 BEGIN
462 xErrLoc := 100;
463
464 IF (pType = CATEGORY_TYPE) THEN
465 xErrLoc := 200;
466 -- get category set info
467 select category_set_id,
468 validate_flag,
469 structure_id
473 from mtl_default_sets_view
470 into xCategorySetId,
471 xValidateFlag,
472 xStructureId
474 where functional_area_id = 2;
475
476 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
477 'Category Set Information[category_set_id: ' || xCategorySetId ||
478 ', validate_flag: ' || xValidateFlag ||
479 ', structure_id: ' || xStructureId || ']');
480
481 xErrLoc := 210;
482 xLastRunDate := ICX_POR_EXTRACTOR.gLoaderValue.categories_last_run_date;
483
484 xErrLoc := 220;
485 xString :=
486 'select distinct mck.category_id category_id, ' ||
487 ' nvl(mctl.description, mck.concatenated_segments) category_name, ' ||
488 ' mctl.language language, ' ||
489 ' mctl.source_lang source_lang, ' ||
490 ' icat.rt_category_id rt_category_id, ' ||
491 ' icat2.category_name old_category_name ';
492
493 xErrLoc := 230;
494 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
495 xString := xString ||
496 'from imtl_categories_kfv mck, ' ||
497 ' imtl_categories_tl mctl, ' ||
498 ' icx_cat_categories_tl icat, ' ||
499 ' icx_cat_categories_tl icat2 ';
500 IF xValidateFlag = 'Y' THEN
501 xString := xString ||
502 ', imtl_category_set_valid_cats mcsvc ';
503 END IF;
504 ELSE
505 xString := xString ||
506 'from mtl_categories_kfv mck, ' ||
507 ' mtl_categories_tl mctl, ' ||
508 ' icx_cat_categories_tl icat, ' ||
509 ' icx_cat_categories_tl icat2 ';
510 IF xValidateFlag = 'Y' THEN
511 xString := xString ||
512 ', mtl_category_set_valid_cats mcsvc ';
513 END IF;
514 END IF;
515
516 xErrLoc := 240;
517 xString := xString ||
518 'where mck.structure_id = :structure_id ' ||
519 'and mck.web_status = ''Y'' ' ||
520 'and nvl(mck.start_date_active, sysdate) <= sysdate ' ||
521 'and sysdate < nvl(mck.end_date_active, sysdate+1) ' ||
522 'and sysdate < nvl(mck.disable_date, sysdate+1) ' ||
523 'and GREATEST(mck.last_update_date, mctl.last_update_date';
524 IF xValidateFlag = 'Y' THEN
525 xString := xString || ', mcsvc.last_update_date';
526 END IF;
527 xString := xString ||
528 ') > NVL(:last_run_date, mck.last_update_date-1) ' ||
529 'and mctl.category_id = mck.category_id ' ||
530 'and mctl.language in (select language_code ' ||
531 ' from fnd_languages ' ||
532 ' where installed_flag in (''B'', ''I'')) ' ||
533 'and to_char(mctl.category_id) = icat.key (+) ' ||
534 'and to_char(mctl.category_id) = icat2.key (+) ' ||
535 'and mctl.language = icat2.language (+) ';
536
537 xErrLoc := 260;
538 IF xValidateFlag = 'Y' THEN
539 xErrLoc := 270;
540 xString := xString ||
541 'and mcsvc.category_set_id = :category_set_id ' ||
542 'and mcsvc.category_id = mck.category_id ';
543 END IF;
544
545 xErrLoc := 280;
546 xString := xString || 'order by 1 ';
547
548 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
549 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
550 'Query for category extraction: ' || xString);
551 END IF;
552
553 xErrLoc := 300;
554 IF xValidateFlag = 'Y' THEN
555 xErrLoc := 320;
556 OPEN pCursor FOR xString USING xStructureId, xLastRunDate,
557 xCategorySetId;
558 ELSE
559 xErrLoc := 340;
560 OPEN pCursor FOR xString USING xStructureId, xLastRunDate;
561 END IF;
562 ELSIF (pType = TEMPLATE_HEADER_TYPE) THEN
563 xErrLoc := 400;
564 if (ICX_POR_EXTRACTOR.gLoaderValue.load_internal_item = 'Y' and
565 ICX_POR_EXTRACTOR.gLoaderValue.template_headers_last_run_date >
566 ICX_POR_EXTRACTOR.gLoaderValue.internal_item_last_run_date) then
567 xLastRunDate :=
568 ICX_POR_EXTRACTOR.gLoaderValue.internal_item_last_run_date;
569 else
570 xLastRunDate :=
571 ICX_POR_EXTRACTOR.gLoaderValue.template_headers_last_run_date;
572 end if;
573
574 xErrLoc := 420;
575 xString :=
576 'select distinct to_number(NULL) category_id, ' ||
577 ' templates.express_name category_name, ' ||
578 ' to_char(NULL) language, ' ||
579 ' to_char(NULL) source_lang, ' ||
580 ' icat.rt_category_id rt_category_id, ' ||
581 ' icat.category_name old_category_name ';
582
583 xErrLoc := 430;
584 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
585 xString := xString ||
586 'from ipo_reqexpress_headers_all templates, ' ||
587 ' icx_cat_categories_tl icat ';
588 ELSE
589 xString := xString ||
590 'from po_reqexpress_headers_all templates, ' ||
591 ' icx_cat_categories_tl icat ';
592 END IF;
593
594 xErrLoc := 440;
595 xString := xString ||
596 'where templates.last_update_date > NVL(:last_run_date, ' ||
597 ' templates.last_update_date-1) ' ||
598 'and NVL(templates.inactive_date, sysdate+1) > sysdate ' ||
599 'and exists (select -1 ';
600
601 xErrLoc := 450;
602 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
603 xString := xString ||
604 'from ipo_reqexpress_lines_all tlines ';
605 ELSE
606 xString := xString ||
607 'from po_reqexpress_lines_all tlines ';
608 END IF;
609
610 xErrLoc := 460;
611 xString := xString ||
612 'where tlines.express_name = templates.express_name ' ||
613 'and (templates.org_id is null and ' ||
614 ' tlines.org_id is null or ' ||
618 'and templates.express_name||''_tmpl'' = icat.key (+) ' ||
615 ' templates.org_id = tlines.org_id) ' ||
616 'and (:load_internal_item = ''Y'' or ' ||
617 ' tlines.source_type_code = ''VENDOR'')) ' ||
619 'and icat.language (+) = :base_lang ';
620
621 xErrLoc := 470;
622
623 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
624 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
625 'Query for template header extraction: ' || xString);
626 END IF;
627
628 xErrLoc := 500;
629 OPEN pCursor FOR xString USING xLastRunDate,
630 ICX_POR_EXTRACTOR.gLoaderValue.load_internal_item,
631 ICX_POR_EXTRACTOR.gBaseLang;
632 END IF;
633
634 xErrLoc := 600;
635 EXCEPTION
636 when ICX_POR_EXT_UTL.gException then
637 -- rollback;
638 ICX_POR_EXT_UTL.extRollback;
639
640 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.openClassCursor-'||
641 xErrLoc);
642 raise ICX_POR_EXT_UTL.gException;
643 when others then
644 -- rollback;
645 ICX_POR_EXT_UTL.extRollback;
646
647 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.openClassCursor-'||
648 xErrLoc||' '||SQLERRM);
649 raise ICX_POR_EXT_UTL.gException;
650 END openClassCursor;
651
652 -- Process classification records
653 PROCEDURE processClassRecords(pType IN PLS_INTEGER,
654 pCursor IN tCursorType)
655 IS
656 xRtCategoryId number;
657
658 xCategoryIds DBMS_SQL.NUMBER_TABLE;
659 xCategoryNames DBMS_SQL.VARCHAR2_TABLE;
660 xLanguages DBMS_SQL.VARCHAR2_TABLE;
661 xSourceLangs DBMS_SQL.VARCHAR2_TABLE;
662 xRtCategoryIds DBMS_SQL.NUMBER_TABLE;
663 xOldCategoryNames DBMS_SQL.VARCHAR2_TABLE;
664
665 xErrLoc PLS_INTEGER := 100;
666 xPendingCount PLS_INTEGER := 0;
667
668 BEGIN
669
670 xErrLoc := 100;
671 clearTables('ALL');
672
673 LOOP
674 xErrLoc := 120;
675
676 xCategoryIds.DELETE;
677 xCategoryNames.DELETE;
678 xLanguages.DELETE;
679 xSourceLangs.DELETE;
680 xRtCategoryIds.DELETE;
681 xOldCategoryNames.DELETE;
682
683 IF (ICX_POR_EXT_UTL.getDatabaseVersion < 9.0) THEN
684 xErrLoc := 150;
685 EXIT WHEN pCursor%NOTFOUND;
686 -- Oracle 8i doesn't support BULK Collect from dynamic SQL
687 FOR i IN 1..ICX_POR_EXT_UTL.gCommitSize LOOP
688 FETCH pCursor
689 INTO xCategoryIds(i), xCategoryNames(i),
690 xLanguages(i), xSourceLangs(i),
691 xRtCategoryIds(i), xOldCategoryNames(i);
692 EXIT WHEN pCursor%NOTFOUND;
693 END LOOP;
694 ELSE
695 xErrLoc := 200;
696 FETCH pCursor
697 BULK COLLECT INTO xCategoryIds, xCategoryNames,
698 xLanguages, xSourceLangs,
699 xRtCategoryIds, xOldCategoryNames
700 LIMIT ICX_POR_EXT_UTL.gCommitSize;
701 EXIT WHEN xCategoryIds.COUNT = 0;
702 END IF;
703
704 xErrLoc := 240;
705 FOR i IN 1..xCategoryIds.COUNT LOOP
706 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
707 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
708 'Classification[category_id: '|| xCategoryIds(i) ||
709 ', category_name: '|| xCategoryNames(i) ||
710 ', language: '|| xLanguages(i) ||
711 ', source_lang: '|| xSourceLangs(i) ||
712 ', rt_category_id: '|| xRtCategoryIds(i) ||
713 ', old_category_name: '|| xOldCategoryNames(i) || ']');
714 END IF;
715
716 xErrLoc := 300;
717 IF (xRtCategoryIds(i) IS NULL) THEN
718 -- Add action
719 xErrLoc := 310;
720
721 IF (pType = CATEGORY_TYPE) THEN
722 -- Check do we have rows for other languages?
723 IF (gLastRtCategoryId <> -1 and
724 gLastCategoryKey = to_char(xCategoryIds(i))) THEN
725 xRtCategoryID := gLastRtCategoryId;
726 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
727 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
728 'Found category id with other language:' || xRtCategoryId);
729 END IF;
730 ELSE
731 xErrLoc := 330;
732 select icx_por_categoryid.nextval
733 into xRtCategoryId
734 from dual;
735 xErrLoc := 350;
736 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
737 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
738 'Create a new category id:' || xRtCategoryId);
739 END IF;
740 xPendingCount := gAddNewRtCategoryIds.COUNT + 1;
741 gAddNewRtCategoryIds(xPendingCount) := xRtCategoryId;
742 gAddNewCategoryKeys(xPendingCount) := to_char(xCategoryIds(i));
743
744 gLastCategoryKey := to_char(xCategoryIds(i));
745 gLastRtCategoryId := xRtCategoryId;
746 END IF;
747 ELSIF (pType = TEMPLATE_HEADER_TYPE) THEN
748 xErrLoc := 360;
749 select icx_por_categoryid.nextval
750 into xRtCategoryId
751 from dual;
752 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
753 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
754 'Create new category id:' || xRtCategoryId);
755 END IF;
756 xPendingCount := gAddNewRtCategoryIds.COUNT + 1;
757 gAddNewRtCategoryIds(xPendingCount) := xRtCategoryId;
758 gAddNewCategoryKeys(xPendingCount) := xCategoryNames(i)||'_tmpl';
759 END IF;
760
761 xErrLoc := 380;
765 gAddCategoryKeys(xPendingCount) := to_char(xCategoryIds(i));
762 xPendingCount := gAddRtCategoryIds.COUNT + 1;
763 gAddRtCategoryIds(xPendingCount) := xRtCategoryId;
764 IF (pType = CATEGORY_TYPE) THEN
766 ELSIF (pType = TEMPLATE_HEADER_TYPE) THEN
767 gAddCategoryKeys(xPendingCount) := xCategoryNames(i)||'_tmpl';
768 END IF;
769 gAddCategoryNames(xPendingCount) := xCategoryNames(i);
770 IF (pType = CATEGORY_TYPE) THEN
771 gAddLanguages(xPendingCount) := xLanguages(i);
772 gAddSourceLangs(xPendingCount) := xSourceLangs(i);
773 ELSIF (pType = TEMPLATE_HEADER_TYPE) THEN
774 gAddLanguages(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;
775 gAddSourceLangs(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;
776 END IF;
777
778 ELSE
779 IF (xOldCategoryNames(i) IS NULL) THEN
780 -- Translate for category
781 xErrLoc := 310;
782 xPendingCount := gAddRtCategoryIds.COUNT + 1;
783 gAddRtCategoryIds(xPendingCount) := xRtCategoryIds(i);
784 gAddCategoryKeys(xPendingCount) := to_char(xCategoryIds(i));
785 gAddCategoryNames(xPendingCount) := xCategoryNames(i);
786 gAddLanguages(xPendingCount) := xLanguages(i);
787 gAddSourceLangs(xPendingCount) := xSourceLangs(i);
788 ELSE
789 -- Update action
790 IF (xOldCategoryNames(i) <> xCategoryNames(i)) then
791 -- Only when name changed, we need to update
792 xErrLoc := 400;
793 xPendingCount := gUpdateCategoryKeys.COUNT + 1;
794 gUpdateCategoryKeys(xPendingCount) := xCategoryNames(i)||'_tmpl';
795 gUpdateCategoryNames(xPendingCount) := xCategoryNames(i);
796 gUpdateRtCategoryIds(xPendingCount) := xRtCategoryIds(i);
797 IF (pType = CATEGORY_TYPE) THEN
798 gUpdateLanguages(xPendingCount) := xLanguages(i);
799 gUpdateSourceLangs(xPendingCount) := xSourceLangs(i);
800 ELSIF (pType = TEMPLATE_HEADER_TYPE) THEN
801 gUpdateLanguages(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;
802 gUpdateSourceLangs(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;
803 END IF;
804 ELSE
805 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
806 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
807 'Category name is the same, no action needed');
808 END IF;
809 END IF;
810 END IF;
811 END IF;
812 END LOOP;
813
814 xErrLoc := 500;
815 -- move classification data
816 processBatchData(pType, 'INLOOP');
817
818 END LOOP;
819
820 xErrLoc := 600;
821 -- process remaining
822 processBatchData(pType, 'OUTLOOP');
823
824 IF (pType = CATEGORY_TYPE) THEN
825 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
826 'Total processed categories: ' || gCompletedCount);
827 ELSE
828 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
829 'Total processed template headers: ' || gCompletedCount);
830 END IF;
831
832 EXCEPTION
833 when ICX_POR_EXT_UTL.gException then
834 -- rollback;
835 ICX_POR_EXT_UTL.extRollback;
836
837 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.processClassRecords-'||
838 xErrLoc);
839 raise ICX_POR_EXT_UTL.gException;
840 when others then
841 -- rollback;
842 ICX_POR_EXT_UTL.extRollback;
843
844 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.processClassRecords-'||
845 xErrLoc||' '||SQLERRM);
846 raise ICX_POR_EXT_UTL.gException;
847 END processClassRecords;
848
849 -- Main procedure
850 PROCEDURE extractClassificationData
851 IS
852 xErrLoc PLS_INTEGER := 100;
853 cClass tCursorType;
854
855 BEGIN
856 xErrLoc := 100;
857 IF (ICX_POR_EXTRACTOR.gLoaderValue.load_categories = 'Y') THEN
858
859 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
860 'Extract oracle categories');
861
862 xErrLoc := 120;
863 openClassCursor(CATEGORY_TYPE, cClass);
864
865 xErrLoc := 140;
866 processClassRecords(CATEGORY_TYPE, cClass);
867
868 xErrLoc := 160;
869 CLOSE cClass;
870
871 xErrLoc := 180;
872 ICX_POR_EXTRACTOR.setLastRunDates('CATEGORY');
873 END IF; -- load_categories
874
875 xErrLoc := 200;
876 gCompletedCount := 0;
877
878 IF (ICX_POR_EXTRACTOR.gLoaderValue.load_template_headers = 'Y') THEN
879
880 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
881 'Extract template headers');
882
883 xErrLoc := 220;
884 openClassCursor(TEMPLATE_HEADER_TYPE, cClass);
885
886 xErrLoc := 240;
887 processClassRecords(TEMPLATE_HEADER_TYPE, cClass);
888
889 xErrLoc := 260;
890 CLOSE cClass;
891
892 xErrLoc := 280;
893 ICX_POR_EXTRACTOR.setLastRunDates('TEMPLATE_HEADER');
894 END IF; -- load_template_headers
895
896 xErrLoc := 300;
897
898 EXCEPTION
899 when ICX_POR_EXT_UTL.gException then
900 -- rollback;
901 ICX_POR_EXT_UTL.extRollback;
902
903 IF (cClass%ISOPEN) THEN
904 CLOSE cClass;
905 END IF;
906
907 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.extractClassificationData-'||
908 xErrLoc);
909 raise ICX_POR_EXT_UTL.gException;
910 when others then
911 -- rollback;
912 ICX_POR_EXT_UTL.extRollback;
913
914 IF (cClass%ISOPEN) THEN
915 CLOSE cClass;
916 END IF;
917
918 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.extractClassificationData-'||
919 xErrLoc||' '||SQLERRM);
920 raise ICX_POR_EXT_UTL.gException;
921 END extractClassificationData;
922
923
924 END ICX_POR_EXT_CLASS;