[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXT_PURGE
Source
1 PACKAGE BODY ICX_POR_EXT_PURGE AS
2 /* $Header: ICXEXTPB.pls 120.1 2006/01/10 12:01:03 sbgeorge noship $*/
3
4 --------------------------------------------------------------
5 -- Type and Cursor --
6 --------------------------------------------------------------
7
8 TYPE tClassification IS RECORD (
9 rt_category_id NUMBER,
10 key ICX_CAT_CATEGORIES_TL.KEY%TYPE,
11 type NUMBER);
12
13 TYPE tClassCursorType IS REF CURSOR;
14
15 TYPE tItem IS RECORD (
16 rt_item_id NUMBER);
17
18 TYPE tItemCursorType IS REF CURSOR;
19
20 --------------------------------------------------------------
21 -- Global Variables --
22 --------------------------------------------------------------
23 gRtCategoryIds DBMS_SQL.NUMBER_TABLE;
24 gCategoryKeys DBMS_SQL.VARCHAR2_TABLE;
25 gCategoryTypes DBMS_SQL.NUMBER_TABLE;
26 gRtItemIds DBMS_SQL.NUMBER_TABLE;
27
28 gCompletedCount PLS_INTEGER := 0;
29
30 -- Bug 2001770
31 gExceptionOccured BOOLEAN := FALSE;
32 gRestartTime PLS_INTEGER := 0;
33
34 --------------------------------------------------------------
35 -- Global PL/SQL Tables --
36 --------------------------------------------------------------
37
38 PROCEDURE clearTables(pMode IN VARCHAR2) IS
39 BEGIN
40 IF (pMode IN ('ALL', 'CLASS')) THEN
41 gRtCategoryIds.DELETE;
42 gCategoryKeys.DELETE;
43 gCategoryTypes.DELETE;
44 END IF;
45
46 IF (pMode IN ('ALL', 'ITEM')) THEN
47 gRtItemIds.DELETE;
48 END IF;
49 END;
50
51 --------------------------------------------------------------
52 -- Snap Shots --
53 --------------------------------------------------------------
54
55 FUNCTION snapShot(pIndex IN PLS_INTEGER,
56 pMode IN VARCHAR2) RETURN varchar2 IS
57 xShot varchar2(2000) := 'SnapShot('||pMode||')['||pIndex||']--';
58 BEGIN
59 IF (pMode = 'CLASS') THEN
60 xShot := xShot || ' gRtCategoryIds: ' ||
61 ICX_POR_EXT_UTL.getTableElement(gRtCategoryIds, pIndex) || ',';
62 xShot := xShot || ' gCategoryKeys: ' ||
63 ICX_POR_EXT_UTL.getTableElement(gCategoryKeys, pIndex) || ',';
64 xShot := xShot || ' gCategoryTypes: ' ||
65 ICX_POR_EXT_UTL.getTableElement(gCategoryTypes, pIndex);
66 ELSIF (pMode = 'ITEM') THEN
67 xShot := xShot || ' gRtItemIds: ' ||
68 ICX_POR_EXT_UTL.getTableElement(gRtItemIds, pIndex);
69 END IF;
70
71 RETURN xShot;
72 END snapShot;
73
74 --------------------------------------------------------------
75 -- Delete Procedures --
76 --------------------------------------------------------------
77
78 -- Delete categories
79 PROCEDURE deleteCategories IS
80
81 CURSOR cCatItems(p_rt_category_id IN NUMBER) IS
82 SELECT rt_item_id
83 FROM icx_cat_category_items
84 WHERE rt_category_id = p_rt_category_id;
85
86 xRtItemIds DBMS_SQL.NUMBER_TABLE;
87 xErrLoc PLS_INTEGER := 100;
88
89 BEGIN
90
91 IF (gCategoryKeys.COUNT = 0) THEN
92 RETURN;
93 END IF;
94
95 IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
96 FOR i in 1..gCategoryKeys.COUNT LOOP
97 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
98 snapShot(i, 'CLASS'));
99 END LOOP;
100 END IF;
101
102 xErrLoc := 100;
103 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
104 ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_DATA_SOURCES');
105 END IF;
106 FORALL i IN 1..gCategoryKeys.COUNT
107 DELETE FROM icx_por_category_data_sources
108 WHERE category_key = gCategoryKeys(i)
109 AND external_source = 'Oracle';
110
111 ICX_POR_EXT_UTL.extCommit;
112
113 xErrLoc := 200;
114 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
115 ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_ORDER_MAP');
116 END IF;
117 FORALL i IN 1..gRtCategoryIds.COUNT
118 DELETE FROM icx_por_category_order_map
119 WHERE rt_category_id = gRtCategoryIds(i)
120 AND external_source = 'Oracle';
121
122 ICX_POR_EXT_UTL.extCommit;
123
124 xErrLoc := 300;
125 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
126 ICX_POR_EXT_UTL.debug('Delete from ICX_POR_TABLE_OF_CONTENTS_TL');
127 END IF;
128 FORALL i IN 1..gRtCategoryIds.COUNT
129 DELETE FROM icx_por_table_of_contents_tl
130 WHERE child = gRtCategoryIds(i);
131
132 ICX_POR_EXT_UTL.extCommit;
133
134 xErrLoc := 400;
135 FOR i IN 1..gRtCategoryIds.COUNT LOOP
136
137 xErrLoc := 410;
138 OPEN cCatItems(gRtCategoryIds(i));
139
140 LOOP
141 xErrLoc := 420;
142 xRtItemIds.DELETE;
143
144 xErrLoc := 430;
145 FETCH cCatItems
146 BULK COLLECT INTO xRtItemIds
147 LIMIT ICX_POR_EXT_UTL.gCommitSize;
148 EXIT WHEN xRtItemIds.COUNT = 0;
149
150 -- For Category, need also delete all assoicated item records
151 -- For Template Header, only delete that category_item association
152 IF (gCategoryTypes(i) = ICX_POR_EXT_CLASS.CATEGORY_TYPE) THEN
153
154 xErrLoc := 440;
155 ICX_POR_DELETE_CATALOG.setCommitSize(ICX_POR_EXT_UTL.gCommitSize);
156 xErrLoc := 445;
157 ICX_POR_DELETE_CATALOG.deleteCommonTables(xRtItemIds,
158 ICX_POR_DELETE_CATALOG.CATITEM_TABLE_LAST);
159
160 ELSIF (gCategoryTypes(i) = ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE) THEN
161
162 xErrLoc := 450;
163 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
164 ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORY_ITEMS');
165 END IF;
166 FORALL j IN 1..xRtItemIds.COUNT
167 DELETE FROM icx_cat_category_items
168 WHERE rt_item_id = xRtItemIds(j)
169 AND rt_category_id = gRtCategoryIds(i);
170
171 END IF;
172
173 xErrLoc := 460;
174 ICX_POR_EXT_UTL.extCommit;
175
176 END LOOP;
177
178 xErrLoc := 480;
179 CLOSE cCatItems;
180
181 END LOOP;
182
183 xErrLoc := 500;
184 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
185 ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORIES_TL');
186 END IF;
187 FORALL i IN 1..gRtCategoryIds.COUNT
188 DELETE FROM icx_cat_categories_tl
189 WHERE rt_category_id = gRtCategoryIds(i);
190
191 xErrLoc := 600;
192 ICX_POR_EXT_UTL.extAFCommit;
193
194 xErrLoc := 700;
195 clearTables('CLASS');
196 EXCEPTION
197 when others then
198 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.deleteCategories-'||
199 xErrLoc||' '||SQLERRM);
200 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, 'CLASS'));
201
202 ICX_POR_EXT_UTL.extRollback;
203
204 IF (cCatItems%ISOPEN) THEN
205 CLOSE cCatItems;
206 END IF;
207
208 raise ICX_POR_EXT_UTL.gException;
209 END deleteCategories;
210
211 -- Delete Items
212 procedure deleteItems IS
213
214 xErrLoc PLS_INTEGER := 100;
215
216 BEGIN
217 IF (gRtItemIds.COUNT = 0) THEN
218 RETURN;
219 END IF;
220
221 if (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) then
222 FOR i in 1..gRtItemIds.COUNT LOOP
223 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
224 snapShot(i, 'ITEM'));
225 END LOOP;
226 end if;
227
228 xErrLoc := 200;
229 ICX_POR_DELETE_CATALOG.setCommitSize(ICX_POR_EXT_UTL.gCommitSize);
230
231 xErrLoc := 300;
232 ICX_POR_DELETE_CATALOG.deleteCommonTables(gRtItemIds,
233 ICX_POR_DELETE_CATALOG.ITEM_TABLE_LAST);
234
235 xErrLoc := 500;
236 clearTables('ITEM');
237 exception
238 when others then
239 ICX_POR_EXT_UTL.extRollback;
240
241 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.deleteItems-'||
242 xErrLoc||' '||SQLERRM);
243
244 raise ICX_POR_EXT_UTL.gException;
245 end deleteItems;
246
247 -- Process batch data
248 PROCEDURE processBatchData(pType IN VARCHAR2,
249 pMode IN VARCHAR2) IS
250 xErrLoc PLS_INTEGER := 100;
251 x_return_err varchar2(2000);
252
253 BEGIN
254 xErrLoc := 100;
255
256 IF (pType = 'CLASS' AND
257 (pMode = 'OUTLOOP' OR
258 gRtCategoryIds.COUNT >= ICX_POR_EXT_UTL.gCommitSize))
259 THEN
260 xErrLoc := 200;
261 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
262 'Process batch category purge -- Pending[' ||
263 gRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');
264
265 gCompletedCount := gCompletedCount + gRtCategoryIds.COUNT;
266 deleteCategories;
267 END IF;
268
269 xErrLoc := 300;
270 IF (pType = 'ITEM' AND
271 (pMode = 'OUTLOOP' OR
272 gRtItemIds.COUNT >= ICX_POR_EXT_UTL.gCommitSize))
273 THEN
274 xErrLoc := 400;
275 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
276 'Process batch item purge -- Pending[' ||
277 gRtItemIds.COUNT || '], Completed[' || gCompletedCount || ']');
278
279 gCompletedCount := gCompletedCount + gRtItemIds.COUNT;
280 deleteItems;
281 END IF;
282
283 xErrLoc := 500;
284 EXCEPTION
285 when OTHERS then
286 -- rollback;
287 ICX_POR_EXT_UTL.extRollback;
288
289 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.processBatchData-'||
290 xErrLoc||' '||SQLERRM);
291 raise ICX_POR_EXT_UTL.gException;
292 END processBatchData;
293
294 --------------------------------------------------------------
295 -- Purge Classfication Data --
296 --------------------------------------------------------------
297 -- Open classification cursor
298 PROCEDURE openClassCursor(pMode IN PLS_INTEGER,
299 pInvCatId IN NUMBER,
300 pCursor IN OUT NOCOPY tClassCursorType)
301 IS
302 xCategorySetId NUMBER;
303 xValidateFlag VARCHAR2(1);
304 xStructureId NUMBER;
305 xString VARCHAR2(4000);
306 xErrLoc PLS_INTEGER := 100;
307
308 BEGIN
309 xErrLoc := 100;
310
311 IF (pMode = NORMAL_MODE) THEN
312 xErrLoc := 200;
313 -- get category set info
314 SELECT category_set_id,
315 validate_flag,
316 structure_id
317 INTO xCategorySetId,
318 xValidateFlag,
319 xStructureId
320 FROM mtl_default_sets_view
321 WHERE functional_area_id = 2;
322
323 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
324 'Category Set Information[category_set_id: ' || xCategorySetId ||
325 ', validate_flag: ' || xValidateFlag ||
326 ', structure_id: ' || xStructureId || ']');
327
328 xErrLoc := 210;
329 xString :=
330 'SELECT cat.rt_category_id, cat.key, cat.type ' ||
331 'FROM icx_cat_categories_tl cat ' ||
332 'WHERE title = ''Oracle'' ' ||
333 'AND language = ''' || ICX_POR_EXTRACTOR.gBaseLang || ''' ' ||
334 'AND ((type = '||ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE||' AND '||
335 ' NOT EXISTS (SELECT ''active template header'' ';
336
337 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
338 xString := xString ||
339 'FROM ipo_reqexpress_headers_all templ ';
340 ELSE
341 xString := xString ||
342 'FROM po_reqexpress_headers_all templ ';
343 END IF;
344
345 xString := xString ||
346 'WHERE templ.express_name||''_tmpl'' = cat.key ' ||
347 'AND (NVL(inactive_date,SYSDATE+1)>SYSDATE))) OR ' ||
348 ' (type = '||ICX_POR_EXT_CLASS.CATEGORY_TYPE||' AND '||
349 ' NOT EXISTS (SELECT ''active oracle category'' ';
350
351 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
352 xString := xString ||
353 'FROM imtl_categories_kfv mck ';
354 ELSE
355 xString := xString ||
356 'FROM mtl_categories_kfv mck ';
357 END IF;
358
359 IF (xValidateFlag = 'Y') THEN
360 xErrLoc := 220;
361 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
362 xString := xString ||
363 ', imtl_category_set_valid_cats mcsvc ';
364 ELSE
365 xString := xString ||
366 ', mtl_category_set_valid_cats mcsvc ';
367 END IF;
368 xString := xString ||
369 'WHERE mcsvc.category_set_id = :category_set_id ' ||
370 'AND mcsvc.category_id = TO_NUMBER(cat.key) ' ||
371 'AND mcsvc.category_id = mck.category_id ';
372 ELSE
373 xErrLoc := 240;
374 xString := xString ||
375 'WHERE mck.category_id = TO_NUMBER(cat.key) ';
376 END IF;
377
378 xErrLoc := 260;
379 xString := xString ||
380 'AND mck.structure_id = :structure_id ' ||
381 'AND mck.web_status = ''Y'' ' ||
382 'AND NVL(mck.start_date_active,SYSDATE)<=SYSDATE ' ||
383 'AND SYSDATE<NVL(mck.end_date_active,SYSDATE+1) ' ||
384 'AND SYSDATE<NVL(mck.disable_date,SYSDATE+1)))) ';
385
386 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
387 xString := xString ||
388 'AND cat.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID;
389 END IF;
390
391 xErrLoc := 270;
392 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
393 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
394 'Query for category purge: ' || xString);
395 END IF;
396
397 IF xValidateFlag = 'Y' THEN
398 xErrLoc := 280;
399 OPEN pCursor FOR xString USING xCategorySetId,xStructureId;
400 ELSE
401 xErrLoc := 290;
402 OPEN pCursor FOR xString USING xStructureId;
403 END IF;
404
405 ELSIF (pMode = ALL_MODE) THEN
406 xErrLoc := 300;
407 OPEN pCursor FOR
408 SELECT cat.rt_category_id, cat.key, cat.type
409 FROM icx_cat_categories_tl cat
410 WHERE cat.title = 'Oracle'
411 AND cat.language = ICX_POR_EXTRACTOR.gBaseLang
412 AND NOT EXISTS (SELECT 'Bulkloaded items'
413 FROM icx_cat_items_b i,
414 icx_cat_category_items ci
415 WHERE ci.rt_category_id = cat.rt_category_id
416 AND ci.rt_item_id = i.rt_item_id
417 AND NVL(i.extractor_updated_flag, 'N') = 'N');
418 ELSIF (pMode = CATEGORY_MODE) THEN
419 xErrLoc := 300;
420 OPEN pCursor FOR
421 SELECT cat.rt_category_id, cat.key, cat.type
422 FROM icx_cat_categories_tl cat
423 WHERE cat.title = 'Oracle'
424 AND cat.language = ICX_POR_EXTRACTOR.gBaseLang
425 AND key = to_char(pInvCatId)
426 AND NOT EXISTS (SELECT 'Bulkloaded items'
427 FROM icx_cat_items_b i,
428 icx_cat_category_items ci
429 WHERE ci.rt_category_id = cat.rt_category_id
430 AND ci.rt_item_id = i.rt_item_id
431 AND NVL(i.extractor_updated_flag, 'N') = 'N');
432
433 END IF;
434
435 xErrLoc := 500;
436 EXCEPTION
437 when others then
438 ICX_POR_EXT_UTL.extRollback;
439
440 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.openClassCursor-'||
441 xErrLoc||' '||SQLERRM);
442 raise ICX_POR_EXT_UTL.gException;
443 END openClassCursor;
444
445 -- Main procedure to purge classification data
446 PROCEDURE purgeClassificationData(pMode IN PLS_INTEGER,
447 pInvCatId IN NUMBER)
448 IS
449
450 xRtCategoryIds DBMS_SQL.NUMBER_TABLE;
451 xKeys DBMS_SQL.VARCHAR2_TABLE;
452 xTypes DBMS_SQL.NUMBER_TABLE;
453
454 xErrLoc PLS_INTEGER := 100;
455 cClass tClassCursorType;
456
457 xPendingCount PLS_INTEGER := 0;
458
459 BEGIN
460 xErrLoc := 100;
461 gCompletedCount := 0;
462
463 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
464 'Purge oracle classification data');
465
466 xErrLoc := 120;
467 openClassCursor(pMode, pInvCatId, cClass);
468
469 xErrLoc := 140;
470
471 clearTables('CLASS');
472
473 LOOP
474 xErrLoc := 200;
475
476 xRtCategoryIds.DELETE;
477 xKeys.DELETE;
478 xTypes.DELETE;
479
480 IF (ICX_POR_EXT_UTL.getDatabaseVersion < 9.0) THEN
481 xErrLoc := 150;
482 EXIT WHEN cClass%NOTFOUND;
483 -- Oracle 8i doesn't support BULK Collect from dynamic SQL
484 FOR i IN 1..ICX_POR_EXT_UTL.gCommitSize LOOP
485 FETCH cClass
486 INTO xRtCategoryIds(i), xKeys(i), xTypes(i);
487 EXIT WHEN cClass%NOTFOUND;
488 END LOOP;
489 ELSE
490 xErrLoc := 200;
491 FETCH cClass
492 BULK COLLECT INTO xRtCategoryIds, xKeys, xTypes
493 LIMIT ICX_POR_EXT_UTL.gCommitSize;
494 EXIT WHEN xRtCategoryIds.COUNT = 0;
495 END IF;
496
497 xErrLoc := 240;
498 FOR i IN 1..xRtCategoryIds.COUNT LOOP
499 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
500 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
501 'Classification[rt_category_id: '|| xRtCategoryIds(i) ||
502 ', key: '|| xKeys(i) || ', type: '|| xTypes(i) ||']');
503 END IF;
504
505 xErrLoc := 300;
506 xPendingCount := gRtCategoryIds.COUNT + 1;
507 gRtCategoryIds(xPendingCount) := xRtCategoryIds(i);
508 gCategoryKeys(xPendingCount) := xKeys(i);
509 gCategoryTypes(xPendingCount) := xTypes(i);
510
511 END LOOP;
512
513 xErrLoc := 500;
514 -- move classification data
515 processBatchData('CLASS', 'INLOOP');
516
517 END LOOP;
518
519 xErrLoc := 600;
520 -- process remaining
521 processBatchData('CLASS', 'OUTLOOP');
522
523 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
524 'Total deleted categories: ' || gCompletedCount);
525
526 xErrLoc := 700;
527 CLOSE cClass;
528
529 xErrLoc := 900;
530 gRestartTime := 0;
531 gExceptionOccured := false;
532
533 EXCEPTION
534 when ICX_POR_EXT_UTL.gException then
535 -- rollback;
536 ICX_POR_EXT_UTL.extRollback;
537
538 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.purgeClassificationData-'||
539 xErrLoc);
540 raise ICX_POR_EXT_UTL.gException;
541 when others then
542 -- rollback;
543 ICX_POR_EXT_UTL.extRollback;
544
545 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.purgeClassificationData-'||
546 xErrLoc||' '||SQLERRM);
547
548 -- Bug 2001770
549 -- handle exception ORA-01555: snapshot too old: rollback segment
550 -- number 9 with name "RBS08" too small
551 if (SQLCODE = -01555) then
552 if (gRestartTime < 3) then
553 gRestartTime := gRestartTime + 1;
554 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
555 'Restart ICX_POR_EXT_PURGE.purgeClassificationData ' ||
556 gRestartTime || ' times');
557 gExceptionOccured := true;
558 -- Bug 2305219, zxzhang, 05/06/2002
559 -- purgeClassificationData;
560 purgeClassificationData(pMode, pInvCatId);
561 else
562 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
563 'Restart ICX_POR_EXT_PURGE.purgeClassificationData too many times');
564 raise ICX_POR_EXT_UTL.gException;
565 end if;
566 else
567 raise ICX_POR_EXT_UTL.gException;
568 end if;
569
570 END purgeClassificationData;
571
572 --------------------------------------------------------------
573 -- Purge Item Data --
574 --------------------------------------------------------------
575 -- Open item cursor
576 PROCEDURE openItemCursor(pMode IN PLS_INTEGER,
577 pInvCatItemId IN NUMBER,
578 pType IN VARCHAR2,
579 pCursor IN OUT NOCOPY tItemCursorType)
580 IS
581 xErrLoc PLS_INTEGER := 100;
582 xString VARCHAR2(4000);
583
584 BEGIN
585 xErrLoc := 100;
586
587 IF (pMode = NORMAL_MODE) THEN
588 xErrLoc := 200;
589
590 IF (pType = 'DELETED_ITEMS') THEN
591 xErrLoc := 240;
592 xString :=
593 'SELECT i.rt_item_id ' ||
594 'FROM icx_cat_items_b i ' ||
595 'WHERE i.extractor_updated_flag = ''Y'' ' ||
596 'AND i.internal_item_id is not null ';
597
598 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
599 xString := xString ||
600 'AND i.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID||' ';
601 END IF;
602
603 xString := xString ||
604 'AND NOT EXISTS (SELECT ''item deleted from item master'' ';
605 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
606 xString := xString ||
607 'FROM imtl_system_items_kfv m, '||
608 'ifinancials_system_params_all fsp ';
609 ELSE
610 xString := xString ||
611 'FROM mtl_system_items_kfv m, ' ||
612 'financials_system_params_all fsp ';
613 END IF;
614 xString := xString ||
615 'WHERE m.inventory_item_id = i.internal_item_id ' ||
616 'AND i.org_id = fsp.org_id ' ||
617 'AND fsp.inventory_organization_id = m.organization_id) ';
618
619 ELSIF (pType = 'INVALID_CATGORY_ITEMS') THEN
620 xErrLoc := 260;
621 xString :=
622 'SELECT i.rt_item_id ' ||
623 'FROM icx_cat_items_b i, ' ||
624 'icx_cat_item_prices p1 ' ||
625 'WHERE i.extractor_updated_flag = ''Y'' ' ||
626 'AND i.internal_item_id is not null ';
627
628 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
629 xString := xString ||
630 'AND i.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID||' ';
631 END IF;
632
633 xString := xString ||
634 -- Check for invalid item category association
635 'AND i.rt_item_id = p1.rt_item_id ' ||
636 'AND NOT((p1.contract_line_id <> -2) OR (p1.template_line_id <> -2)) ' ||
637 'AND NOT EXISTS (SELECT ''Invalid item category association'' ';
638 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
639 xString := xString ||
640 'FROM imtl_item_categories m, '||
641 'ifinancials_system_params_all fsp, ';
642 ELSE
643 xString := xString ||
644 'FROM mtl_item_categories m, ' ||
645 'financials_system_params_all fsp, ';
646 END IF;
647 xString := xString ||
648 'icx_cat_item_prices p ' ||
649 'WHERE i.rt_item_id = p.rt_item_id ' ||
650 'AND m.category_id = p.mtl_category_id ' ||
651 'AND m.inventory_item_id = i.internal_item_id ' ||
652 'AND i.org_id = fsp.org_id ' ||
653 'AND fsp.inventory_organization_id = m.organization_id) ';
654 END IF;
655
656 xErrLoc := 270;
657 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.INFO_LEVEL THEN
658 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
659 'Query for item purge: ' || xString);
660 END IF;
661
662 xErrLoc := 280;
663 OPEN pCursor FOR xString;
664
665 ELSIF (pMode = ALL_MODE) THEN
666 xErrLoc := 300;
667 OPEN pCursor FOR
668 SELECT i.rt_item_id
669 FROM icx_cat_items_b i
670 WHERE i.extractor_updated_flag = 'Y';
671
672 ELSIF (pMode = CATEGORY_MODE) THEN
673 xErrLoc := 400;
674 OPEN pCursor FOR
675 SELECT distinct i.rt_item_id
676 FROM icx_cat_items_b i,
677 icx_cat_item_prices p
678 WHERE p.mtl_category_id = pInvCatItemId
679 AND p.rt_item_id = i.rt_item_id
680 AND i.extractor_updated_flag = 'Y';
681
682 ELSIF (pMode = ITEM_MODE) THEN
683 xErrLoc := 500;
684 OPEN pCursor FOR
685 SELECT i.rt_item_id
686 FROM icx_cat_items_b i
687 WHERE i.internal_item_id = pInvCatItemId
688 AND i.extractor_updated_flag = 'Y';
689
690 END IF;
691
692 xErrLoc := 600;
693 EXCEPTION
694 when others then
695 ICX_POR_EXT_UTL.extRollback;
696
697 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.openItemCursor-'||
698 xErrLoc||' '||SQLERRM);
699 raise ICX_POR_EXT_UTL.gException;
700 END openItemCursor;
701
702 -- Process item records
703 PROCEDURE processItemRecords(pCursor IN tItemCursorType)
704 IS
705 xRtItemIds DBMS_SQL.NUMBER_TABLE;
706
707 xErrLoc PLS_INTEGER := 100;
708 xPendingCount PLS_INTEGER := 0;
709
710 BEGIN
711 xErrLoc := 100;
712 clearTables('ITEM');
713
714 LOOP
715 xErrLoc := 200;
716
717 xRtItemIds.DELETE;
718
719 IF (ICX_POR_EXT_UTL.getDatabaseVersion < 9.0) THEN
720 xErrLoc := 150;
721 EXIT WHEN pCursor%NOTFOUND;
722 -- Oracle 8i doesn't support BULK Collect from dynamic SQL
723 FOR i IN 1..ICX_POR_EXT_UTL.gCommitSize LOOP
724 FETCH pCursor
725 INTO xRtItemIds(i);
726 EXIT WHEN pCursor%NOTFOUND;
727 END LOOP;
728 ELSE
729 xErrLoc := 200;
730 FETCH pCursor
731 BULK COLLECT INTO xRtItemIds
732 LIMIT ICX_POR_EXT_UTL.gCommitSize;
733 EXIT WHEN xRtItemIds.COUNT = 0;
734 END IF;
735
736 xErrLoc := 240;
737 FOR i IN 1..xRtItemIds.COUNT LOOP
738 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
739 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
740 'Item[rt_item_id: '|| xRtItemIds(i) || ']');
741 END IF;
742
743 xErrLoc := 300;
744 xPendingCount := gRtItemIds.COUNT + 1;
745 gRtItemIds(xPendingCount) := xRtItemIds(i);
746
747 END LOOP;
748
749 xErrLoc := 500;
750 -- process item data
751 processBatchData('ITEM', 'INLOOP');
752
753 END LOOP;
754
755 xErrLoc := 600;
756 -- process remaining
757 processBatchData('ITEM', 'OUTLOOP');
758
759 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
760 'Total deleted items: ' || gCompletedCount);
761
762 xErrLoc := 700;
763
764 EXCEPTION
765 when others then
766 ICX_POR_EXT_UTL.extRollback;
767
768 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.processItemRecords-'||
769 xErrLoc||' '||SQLERRM);
770 raise ICX_POR_EXT_UTL.gException;
771 END processItemRecords;
772
773 -- Main procedure to purge classification data
774 PROCEDURE purgeItemData (pMode IN PLS_INTEGER,
775 pInvCatItemId IN NUMBER)
776 IS
777
778 xRtItemIds DBMS_SQL.NUMBER_TABLE;
779 xPriceRowIds DBMS_SQL.UROWID_TABLE;
780 xMtlCategoryIds DBMS_SQL.NUMBER_TABLE;
781
782 xErrLoc PLS_INTEGER := 100;
783 cItem tItemCursorType;
784
785 xPendingCount PLS_INTEGER := 0;
786
787 BEGIN
788 xErrLoc := 100;
789 gCompletedCount := 0;
790
791 IF (pMode = NORMAL_MODE) THEN
792
793 xErrLoc := 120;
794 openItemCursor(pMode, pInvCatItemId, 'DELETED_ITEMS', cItem);
795
796 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
797 'Purge items deleted from item masters');
798
799 xErrLoc := 140;
800 processItemRecords(cItem);
801
802 CLOSE cItem;
803
804 xErrLoc := 160;
805 openItemCursor(pMode, pInvCatItemId, 'INVALID_CATGORY_ITEMS', cItem);
806
807 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
808 'Purge items with invalid association in MTL_ITEM_CATEGORIES');
809
810 xErrLoc := 180;
811 processItemRecords(cItem);
812
813 CLOSE cItem;
814
815 ELSE
816
817 xErrLoc := 200;
818 openItemCursor(pMode, pInvCatItemId, NULL, cItem);
819
820 xErrLoc := 220;
821 processItemRecords(cItem);
822
823 CLOSE cItem;
824 END IF;
825
826 gRestartTime := 0;
827 gExceptionOccured := false;
828
829 xErrLoc := 300;
830 ICX_POR_EXT_ITEM.cleanupPrices;
831 EXCEPTION
832 WHEN ICX_POR_EXT_UTL.gException THEN
833 ICX_POR_EXT_UTL.extRollback;
834
835 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.purgeItemData-'||
836 xErrLoc);
837 raise ICX_POR_EXT_UTL.gException;
838 WHEN OTHERS THEN
839 ICX_POR_EXT_UTL.extRollback;
840
841 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.purgeItemData-'||
842 xErrLoc||' '||SQLERRM);
843
844 -- Bug 2001770
845 -- handle exception ORA-01555: snapshot too old: rollback segment
846 -- number 9 with name "RBS08" too small
847 if (SQLCODE = -01555) then
848 if (gRestartTime < 3) then
849 gRestartTime := gRestartTime + 1;
850 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
851 'Restart ICX_POR_EXT_PURGE.purgeItemData ' ||
852 gRestartTime || ' times');
853 gExceptionOccured := true;
854
855 purgeItemData(pMode, pInvCatItemId);
856 else
857 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
858 'Restart ICX_POR_EXT_PURGE.purgeItemData too many times');
859 raise ICX_POR_EXT_UTL.gException;
860 end if;
861 else
862 raise ICX_POR_EXT_UTL.gException;
863 end if;
864
865 END purgeItemData;
866
867 END ICX_POR_EXT_PURGE;