eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ICX_POR_DELETE_CATALOG

Source


1 PACKAGE BODY ICX_POR_DELETE_CATALOG AS
2 /* $Header: ICXDELCB.pls 115.7 2004/06/10 10:43:56 sosingha ship $*/
3 
4 
5 /**
6  ** Global varibles
7  **/
8 gReturnError	VARCHAR2(4000) := NULL;
9 gCommitSize	NUMBER := 2000;
10 
11 -- ORA-01555: snapshot too old: rollback segment number  with name "" too small
12 snap_shot_too_old EXCEPTION;
13 PRAGMA EXCEPTION_INIT(snap_shot_too_old, -1555);
14 
15 PROCEDURE setCommitSize(pCommitSize	IN PLS_INTEGER) IS
16 BEGIN
17   gCommitSize := pCommitSize;
18 END setCommitSize;
19 
20 /**
21  ** Proc : delete_items_in_category
22  ** Procedure called when a category is deleted from ecmanager.
23  ** Desc : Deletes the items and prices in the category id specified
24  **        Deletes the category related info from icx_cat_browse_trees,
25  **        icx_por_category_data_sources, icx_por_category_order_map
26  **/
27 PROCEDURE delete_items_in_category (
28                                     errbuf            OUT NOCOPY VARCHAR2,
29                                     retcode           OUT NOCOPY VARCHAR2,
30                                     p_rt_category_id   IN NUMBER,
31                                     p_category_key     IN VARCHAR2)
32 IS
33 BEGIN
34    retcode := 0;
35    errbuf := '';
36 
37    delete_items_in_category(p_rt_category_id, p_category_key);
38 
39 EXCEPTION
40    WHEN OTHERS THEN
41       retcode := 2;
42       errbuf  := SQLERRM;
43       raise;
44 END delete_items_in_category;
45 
46 /**
47  ** Proc : delete_items_in_category
48  ** Procedure called when a category is deleted from ecmanager.
49  ** Desc : Deletes the items and prices in the category id specified
50  **        Deletes the category related info from icx_cat_browse_trees,
51  **        icx_por_category_data_sources, icx_por_category_order_map
52  **/
53 PROCEDURE delete_items_in_category (p_rt_category_id        IN NUMBER,
54                                     p_category_key          IN VARCHAR2)
55 IS
56   --TYPE CursorType       IS REF CURSOR;
57   --item_cursor           CursorType;
58 
59   CURSOR item_cursor(c_rt_category_id IN NUMBER) IS
60    SELECT rt_item_id
61    FROM   icx_cat_category_items
62    WHERE  rt_category_id = c_rt_category_id;
63 
64   l_rt_item_ids         DBMS_SQL.NUMBER_TABLE;
65 
66   xCommitSize           PLS_INTEGER := 2000;
67   xErrLoc               PLS_INTEGER := 0;
68 BEGIN
69 
70   -- Set Log level to NOLOG_LEVEL, so no log file will be generated for ECManager
71   --ICX_POR_EXT_UTL.setDebugLevel(ICX_POR_EXT_UTL.NOLOG_LEVEL);
72 
73   -- Set commit size
74   xErrLoc := 100;
75   fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
76   setCommitSize(xCommitSize);
77 
78   xErrLoc := 120;
79   OPEN item_cursor(p_rt_category_id);
80 
81   xErrLoc := 150;
82   LOOP
83     xErrLoc := 200;
84     BEGIN
85     FETCH item_cursor
86     BULK COLLECT INTO l_rt_item_ids
87     LIMIT gCommitSize;
88 
89     EXIT WHEN l_rt_item_ids.COUNT = 0;
90 
91     xErrLoc := 240;
92     deleteCommonTables(l_rt_item_ids, CATITEM_TABLE_LAST);
93     commit;
94     l_rt_item_ids.DELETE;
95 
96    xErrLoc := 300;
97    EXCEPTION
98       WHEN snap_shot_too_old THEN
99         ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
100         'ORA-01555: snapshot too old: caught at '||
101         'ICX_POR_DELETE_CATALOG.delete_items_in_category(' || xErrLoc|| ')'||     ', sqlerrm:' ||sqlerrm ||
102         '; so close the cursor and reopen the cursor-');
103         xErrLoc := 400;
104         COMMIT;
105         IF (item_cursor%ISOPEN) THEN
106             xErrLoc := 500;
107             CLOSE item_cursor;
108             xErrLoc := 600;
109             OPEN item_cursor(p_rt_category_id);
110         END IF;
111     END;
112     END LOOP;
113 
114   xErrLoc := 700;
115   deleteCategoryRelatedInfo(p_rt_category_id, p_category_key);
116   commit;
117 
118   xErrLoc := 800;
119   IF (item_cursor%ISOPEN) THEN
120     CLOSE item_cursor;
121   END IF;
122 
123 EXCEPTION
124   WHEN OTHERS THEN
125     gReturnError := gReturnError ||
126       'ICX_POR_DELETE_CATALOG.delete_items_in_category(' ||
127       xErrLoc|| '): '||sqlerrm;
128     raise_application_error(-20000,gReturnError);
129 END delete_items_in_category;
130 
131 /**
132  ** Proc : delete_items_in_catalog
133  ** Desc : Deletes the items and prices in the catalog name specified
134  **/
135 PROCEDURE delete_items_in_catalog (p_catalog_name        IN VARCHAR2)
136 IS
137   --TYPE CursorType       IS REF CURSOR;
138   --item_cursor           CursorType;
139 
140   CURSOR item_cursor(c_catalog_name IN VARCHAR2) IS
141     SELECT rt_item_id
142     FROM   icx_cat_items_b
143     WHERE  catalog_name = c_catalog_name
144     AND    extractor_updated_flag = 'N';
145 
146   l_rt_item_ids         DBMS_SQL.NUMBER_TABLE;
147 
148   xCommitSize           PLS_INTEGER := 2000;
149   xErrLoc               PLS_INTEGER := 0;
150 BEGIN
151 
152   -- Set Log level to NOLOG_LEVEL, so no log file will be generated for ECManager
153   --ICX_POR_EXT_UTL.setDebugLevel(ICX_POR_EXT_UTL.NOLOG_LEVEL);
154 
155   -- Set commit size
156   xErrLoc := 100;
157   fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
158   setCommitSize(xCommitSize);
159 
160   xErrLoc := 120;
161   open item_cursor(p_catalog_name);
162 
163   xErrLoc := 150;
164   LOOP
165     xErrLoc := 200;
166     BEGIN
167     FETCH item_cursor
168     BULK COLLECT INTO l_rt_item_ids
169     LIMIT gCommitSize;
170 
171     EXIT WHEN l_rt_item_ids.COUNT = 0;
172 
173     xErrLoc := 240;
174     deleteCommonTables(l_rt_item_ids);
175     commit;
176     l_rt_item_ids.DELETE;
177 
178     xErrLoc := 400;
179     EXCEPTION
180       WHEN snap_shot_too_old THEN
181       ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
182       'ORA-01555: snapshot too old: caught at '||
183       'ICX_POR_DELETE_CATALOG.delete_items_in_catalog(' || xErrLoc|| ')'||
184       ', sqlerrm:' ||sqlerrm ||
185       '; so close the cursor and reopen the cursor-');
186       xErrLoc := 500;
187       COMMIT;
188       IF (item_cursor%ISOPEN) THEN
189           xErrLoc := 600;
190           CLOSE item_cursor;
191           xErrLoc := 700;
192           OPEN item_cursor(p_catalog_name);
193       END IF;
194     END;
195 
196   END LOOP;
197 
198   IF (item_cursor%ISOPEN) THEN
199      CLOSE item_cursor;
200   END IF;
201 
202 EXCEPTION
203   WHEN OTHERS THEN
204     gReturnError := gReturnError ||
205       'ICX_POR_DELETE_CATALOG.delete_items_in_catalog(' ||
206       xErrLoc|| '): '||sqlerrm;
207     raise_application_error(-20000,gReturnError);
208 END delete_items_in_catalog;
209 
210 /**
211  ** Proc : delete_supplier_catalog_opUnit
212  ** Desc : Deletes the catalog for the supplier and Operating Unit specified.
213  **/
214 PROCEDURE delete_supplier_catalog_opUnit (p_supplier IN VARCHAR2,
215                                           p_operating_unit_id IN NUMBER DEFAULT -2)
216 IS
217   --TYPE CursorType	IS REF CURSOR;
221                      c_operating_unit_id IN NUMBER) IS
218   --item_cursor		CursorType;
219 
220   CURSOR item_cursor(c_supplier IN VARCHAR2,
222     SELECT rt_item_id
223     FROM   icx_cat_items_b
224     WHERE  supplier = c_supplier
225     AND    org_id = c_operating_unit_id
226     AND    extractor_updated_flag = 'N';
227 
228   l_rt_item_ids		DBMS_SQL.NUMBER_TABLE;
229 
230   xCommitSize		PLS_INTEGER := 2000;
231   xErrLoc 	    	PLS_INTEGER := 0;
232 BEGIN
233 
234   -- Set Log level to NOLOG_LEVEL, so no log file will be generated for ECManager
235   --ICX_POR_EXT_UTL.setDebugLevel(ICX_POR_EXT_UTL.NOLOG_LEVEL);
236 
237   -- Set commit size
238   xErrLoc := 100;
239   fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
240   setCommitSize(xCommitSize);
241 
242   xErrLoc := 120;
243   OPEN item_cursor(p_supplier, p_operating_unit_id);
244 
245   xErrLoc := 150;
246   LOOP
247     xErrLoc := 200;
248     BEGIN
249     FETCH item_cursor
250     BULK COLLECT INTO l_rt_item_ids
251     LIMIT gCommitSize;
252 
253     EXIT WHEN l_rt_item_ids.COUNT = 0;
254 
255     xErrLoc := 240;
256     deleteCommonTables(l_rt_item_ids);
257     commit;
258     l_rt_item_ids.DELETE;
259 
260    xErrLoc := 400;
261    EXCEPTION
262      WHEN snap_shot_too_old THEN
263        ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
264        'ORA-01555: snapshot too old: caught at '||
265        'ICX_POR_DELETE_CATALOG.delete_supplier_catalog_opUnit(' || xErrLoc|| ')'||     ', sqlerrm:' ||sqlerrm ||
266        '; so close the cursor and reopen the cursor-');
267        xErrLoc := 500;
268        COMMIT;
269        IF (item_cursor%ISOPEN) THEN
270            xErrLoc := 600;
271            CLOSE item_cursor;
272            xErrLoc := 700;
273            OPEN item_cursor(p_supplier, p_operating_unit_id);
274        END IF;
275    END;
276 
277    END LOOP;
278    IF (item_cursor%ISOPEN) THEN
279        CLOSE item_cursor;
280    END IF;
281 
282 EXCEPTION
283   WHEN OTHERS THEN
284     gReturnError := gReturnError ||
285       'ICX_POR_DELETE_CATALOG.delete_supplier_catalog_opUnit(' ||
286       xErrLoc|| '): '||sqlerrm;
287     raise_application_error(-20000,gReturnError);
288 END delete_supplier_catalog_opUnit;
289 
290 /**
291  ** Proc : delPriceHistory
292  ** Desc : Deletes the data from ICX_CAT_PRICE_HISTORY
293  **/
294 PROCEDURE delPriceHistory(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
295 IS
296   xErrLoc	PLS_INTEGER := 0;
297   xContinue	BOOLEAN := TRUE;
298 
299 BEGIN
300   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_PRICE_HISTORY');
301   xErrLoc := 100;
302   WHILE xContinue LOOP
303     FORALL i IN 1..pRtItemIds.COUNT
304       DELETE FROM icx_cat_price_history
305       WHERE  rt_item_id = pRtItemIds(i)
306       AND    rownum <= gCommitSize ;
307 
308     xErrLoc := 200;
309     ICX_POR_EXT_UTL.extCommit;
310     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
311        xContinue := FALSE;
312     END IF;
313   END LOOP;
314 EXCEPTION
315   WHEN OTHERS THEN
316     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delPriceHistory-'||
317       xErrLoc||' '||SQLERRM);
318     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
319       pRtItemIds(SQL%ROWCOUNT+1));
320     ICX_POR_EXT_UTL.extRollback;
321     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delPriceHistory(' ||
322       xErrLoc|| '): '||sqlerrm;
323     raise_application_error(-20000,gReturnError);
324 END delPriceHistory;
325 
326 /**
327  ** Proc : delItemsTLP
328  ** Desc : Deletes the data from ICX_CAT_ITEMS_TLP
329  **/
330 PROCEDURE delItemsTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
331 IS
332   xErrLoc	PLS_INTEGER := 0;
333   xContinue	BOOLEAN := TRUE;
334 
335 BEGIN
336   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_TLP');
337   xErrLoc := 100;
338   WHILE xContinue LOOP
339     FORALL i IN 1..pRtItemIds.COUNT
340       DELETE FROM icx_cat_items_tlp
341       WHERE  rt_item_id = pRtItemIds(i)
342       AND    rownum <= gCommitSize ;
343 
344     xErrLoc := 200;
345     ICX_POR_EXT_UTL.extCommit;
346     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
347        xContinue := FALSE;
348     END IF;
349   END LOOP;
350 EXCEPTION
351   WHEN OTHERS THEN
352     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsTLP-'||
353       xErrLoc||' '||SQLERRM);
354     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
355       pRtItemIds(SQL%ROWCOUNT+1));
356     ICX_POR_EXT_UTL.extRollback;
357     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsTLP(' ||
358       xErrLoc|| '): '||sqlerrm;
359     raise_application_error(-20000,gReturnError);
360 END delItemsTLP;
361 
362 /**
363  ** Proc : delExtItemsTLP
364  ** Desc : Deletes the data from ICX_CAT_EXT_ITEMS_TLP
365  **/
366 PROCEDURE delExtItemsTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
367 IS
368   xErrLoc	PLS_INTEGER := 0;
369   xContinue	BOOLEAN := TRUE;
370 
371 BEGIN
372   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_EXT_ITEMS_TLP');
373   xErrLoc := 100;
374   WHILE xContinue LOOP
375     FORALL i IN 1..pRtItemIds.COUNT
376       DELETE FROM icx_cat_ext_items_tlp
377       WHERE  rt_item_id = pRtItemIds(i)
378       AND    rownum <= gCommitSize ;
379 
380     xErrLoc := 200;
381     ICX_POR_EXT_UTL.extCommit;
382     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
383        xContinue := FALSE;
384     END IF;
385   END LOOP;
386 EXCEPTION
387   WHEN OTHERS THEN
391       pRtItemIds(SQL%ROWCOUNT+1));
388     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delExtItemsTLP-'||
389       xErrLoc||' '||SQLERRM);
390     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
392     ICX_POR_EXT_UTL.extRollback;
393     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delExtItemsTLP(' ||
394       xErrLoc|| '): '||sqlerrm;
395     raise_application_error(-20000,gReturnError);
399  ** Proc : delItemsCtxTLP
396 END delExtItemsTLP;
397 
398 /**
400  ** Desc : Deletes the data from ICX_CAT_ITEMS_CTX_TLP
401  **/
402 PROCEDURE delItemsCtxTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
403 IS
404   xErrLoc	PLS_INTEGER := 0;
405   xContinue	BOOLEAN := TRUE;
406 
407 BEGIN
408   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_CTX_TLP');
409   xErrLoc := 100;
410   WHILE xContinue LOOP
411     FORALL i IN 1..pRtItemIds.COUNT
412       DELETE FROM icx_cat_items_ctx_tlp
413       WHERE  rt_item_id = pRtItemIds(i)
414       AND    rownum <= gCommitSize ;
415 
416     xErrLoc := 200;
417     ICX_POR_EXT_UTL.extCommit;
418     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
419        xContinue := FALSE;
420     END IF;
421   END LOOP;
422 EXCEPTION
423   WHEN OTHERS THEN
424     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsCtxTLP-'||
425       xErrLoc||' '||SQLERRM);
426     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
427       pRtItemIds(SQL%ROWCOUNT+1));
428     ICX_POR_EXT_UTL.extRollback;
429     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsCtxTLP(' ||
430       xErrLoc|| '): '||sqlerrm;
431     raise_application_error(-20000,gReturnError);
432 END delItemsCtxTLP;
433 
434 /**
435  ** Proc : delFavoriteList
436  ** Desc : Deletes the data from POR_FAVORITE_LIST_LINES
437  **/
438 PROCEDURE delFavoriteList(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
439 IS
440   xErrLoc	PLS_INTEGER := 0;
441   xContinue	BOOLEAN := TRUE;
442 
443 BEGIN
444   ICX_POR_EXT_UTL.debug('Delete from POR_FAVORITE_LIST_LINES');
445   xErrLoc := 100;
446   WHILE xContinue LOOP
447     FORALL i IN 1..pRtItemIds.COUNT
448       DELETE FROM por_favorite_list_lines
449       WHERE  rt_item_id = pRtItemIds(i)
450       AND    rownum <= gCommitSize ;
451 
452     xErrLoc := 200;
453     ICX_POR_EXT_UTL.extCommit;
454     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
455        xContinue := FALSE;
456     END IF;
457   END LOOP;
458 EXCEPTION
459   WHEN OTHERS THEN
460     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delFavoriteList-'||
461       xErrLoc||' '||SQLERRM);
462     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
463       pRtItemIds(SQL%ROWCOUNT+1));
464     ICX_POR_EXT_UTL.extRollback;
465     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delFavoriteList(' ||
466       xErrLoc|| '): '||sqlerrm;
467     raise_application_error(-20000,gReturnError);
468 END delFavoriteList;
469 
470 /**
471  ** Proc : delCategoryItems
472  ** Desc : Deletes the data from ICX_CAT_CATEGORY_ITEMS
473  **/
474 PROCEDURE delCategoryItems(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
475 IS
476   xErrLoc	PLS_INTEGER := 0;
477   xContinue	BOOLEAN := TRUE;
478 
479 BEGIN
480   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORY_ITEMS');
481   xErrLoc := 100;
482   WHILE xContinue LOOP
483     FORALL i IN 1..pRtItemIds.COUNT
484       DELETE FROM icx_cat_category_items
485       WHERE  rt_item_id = pRtItemIds(i)
486       AND    rownum <= gCommitSize ;
487 
488     xErrLoc := 200;
489     ICX_POR_EXT_UTL.extCommit;
490     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
491        xContinue := FALSE;
492     END IF;
493   END LOOP;
494 EXCEPTION
495   WHEN OTHERS THEN
496     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delCategoryItems-'||
497       xErrLoc||' '||SQLERRM);
498     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
499       pRtItemIds(SQL%ROWCOUNT+1));
500     ICX_POR_EXT_UTL.extRollback;
501     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delCategoryItems(' ||
502       xErrLoc|| '): '||sqlerrm;
503     raise_application_error(-20000,gReturnError);
504 END delCategoryItems;
505 
506 /**
507  ** Proc : delPriceLists
508  ** Desc : Deletes the data from ICX_CAT_PRICE_LISTS
509  **/
510 PROCEDURE delPriceLists(pPriceListIds	IN DBMS_SQL.NUMBER_TABLE)
511 IS
512   xErrLoc	PLS_INTEGER := 0;
513 BEGIN
514   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_PRICE_LISTS');
515   xErrLoc := 100;
516   FORALL i IN 1..pPriceListIds.COUNT
517     DELETE FROM icx_cat_price_lists pl
518     WHERE  pl.price_list_id = pPriceListIds(i)
522   xErrLoc := 200;
519     AND    NOT EXISTS (SELECT 'price line'
520                        FROM   icx_cat_item_prices p
521                        WHERE  p.price_list_id = pl.price_list_id);
523   ICX_POR_EXT_UTL.extCommit;
524 EXCEPTION
525   WHEN OTHERS THEN
526     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delPriceLists-'||
527       xErrLoc||' '||SQLERRM);
528     ICX_POR_EXT_UTL.pushError('Index: '|| SQL%ROWCOUNT+1);
529     ICX_POR_EXT_UTL.extRollback;
530     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delPriceLists(' ||
531       xErrLoc|| '): '||sqlerrm;
532     raise_application_error(-20000,gReturnError);
533 END delPriceLists;
534 
535 /**
536  ** Proc : delItemPrices
537  ** Desc : Deletes the data from ICX_CAT_ITEM_PRICES
538  **/
539 PROCEDURE delItemPrices(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
540 IS
541   xErrLoc	PLS_INTEGER := 0;
542   xContinue	BOOLEAN := TRUE;
543   xPriceListIds	DBMS_SQL.NUMBER_TABLE;
544 
545 BEGIN
546   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEM_PRICES');
547   xErrLoc := 100;
548   WHILE xContinue LOOP
549     xPriceListIds.DELETE;
550 
551     xErrLoc := 140;
552     FORALL i IN 1..pRtItemIds.COUNT
553       DELETE FROM icx_cat_item_prices
554       WHERE  rt_item_id = pRtItemIds(i)
555       AND    rownum <= gCommitSize
556       RETURNING price_list_id BULK COLLECT INTO xPriceListIds;
557 
558     xErrLoc := 200;
559     ICX_POR_EXT_UTL.extCommit;
560     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
561        xContinue := FALSE;
562     END IF;
563 
564     xErrLoc := 300;
565     delPriceLists(xPriceListIds);
566   END LOOP;
567 EXCEPTION
568   WHEN OTHERS THEN
569     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemPrices-'||
570       xErrLoc||' '||SQLERRM);
571     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
572       pRtItemIds(SQL%ROWCOUNT+1));
573     ICX_POR_EXT_UTL.extRollback;
574     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemPrices(' ||
575       xErrLoc|| '): '||sqlerrm;
576     raise_application_error(-20000,gReturnError);
577 END delItemPrices;
578 
579 /**
580  ** Proc : delItemsB
581  ** Desc : Deletes the data from ICX_CAT_ITEMS_B
582  **/
586   xContinue	BOOLEAN := TRUE;
583 PROCEDURE delItemsB(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
584 IS
585   xErrLoc	PLS_INTEGER := 0;
587 
588 BEGIN
589   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_B');
590   xErrLoc := 100;
591   WHILE xContinue LOOP
592     FORALL i IN 1..pRtItemIds.COUNT
593       DELETE FROM icx_cat_items_b
594       WHERE  rt_item_id = pRtItemIds(i)
595       AND    rownum <= gCommitSize;
596 
597     xErrLoc := 200;
598     ICX_POR_EXT_UTL.extCommit;
599     IF ( SQL%ROWCOUNT < gCommitSize ) THEN
600        xContinue := FALSE;
601     END IF;
602   END LOOP;
603 EXCEPTION
604   WHEN OTHERS THEN
605     ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsB-'||
606       xErrLoc||' '||SQLERRM);
607     ICX_POR_EXT_UTL.pushError('pRtItemIds('||SQL%ROWCOUNT+1||'): '||
608       pRtItemIds(SQL%ROWCOUNT+1));
609     ICX_POR_EXT_UTL.extRollback;
610     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsB(' ||
611       xErrLoc|| '): '||sqlerrm;
612     raise_application_error(-20000,gReturnError);
613 END delItemsB;
614 
615 /**
616  ** Proc : deleteCommonTables
617  ** Desc : Deletes the data from common tables used by Extractor and DeleteCatalog
618  **/
619 PROCEDURE deleteCommonTables(pRtItemIds 	IN dbms_sql.number_table,
620                              pDeleteOrder 	IN PLS_INTEGER DEFAULT ITEM_TABLE_LAST)
621 IS
622   xErrLoc PLS_INTEGER := 0;
623 
624 BEGIN
625   xErrLoc := 100;
626   delPriceHistory(pRtItemIds);
627   xErrLoc := 120;
628   delItemsTLP(pRtItemIds);
629   xErrLoc := 140;
630   delExtItemsTLP(pRtItemIds);
631   xErrLoc := 160;
632   delItemsCtxTLP(pRtItemIds);
633   xErrLoc := 180;
634   delFavoriteList(pRtItemIds);
635 
636   -- Based on pDeleteOrder, we have to delete ICX_CAT_ITEMS_B
637   -- before ICX_CAT_CATEGORY_ITEMS, or the other way.
638   -- The reason: if pRtItemIds is seleced based on ICX_CAT_ITEMS_B,
639   -- we have to delete ICX_CAT_ITEMS_B last, otherwise if exception
640   -- happens, next time we won't get correct pRtItemIds, which would
641   -- cause data corruption. Same reason for ICX_CAT_CATEGORY_ITEMS.
642   IF (pDeleteOrder = ITEM_TABLE_LAST) THEN
643     xErrLoc := 200;
644     delCategoryItems(pRtItemIds);
645     xErrLoc := 240;
646     delItemPrices(pRtItemIds);
647     xErrLoc := 260;
648     delItemsB(pRtItemIds);
649   ELSIF (pDeleteOrder = CATITEM_TABLE_LAST) THEN
650     xErrLoc := 300;
651     delItemPrices(pRtItemIds);
652     xErrLoc := 340;
653     delItemsB(pRtItemIds);
654     xErrLoc := 360;
655     delCategoryItems(pRtItemIds);
656   END IF;
657 
658   xErrLoc := 400;
659   ICX_POR_EXT_UTL.extAFCommit;
660 
661 EXCEPTION
662   WHEN OTHERS THEN
663     ICX_POR_EXT_UTL.extRollback;
664 
665     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.deleteCommonTables(' ||
666       xErrLoc||'): '||sqlerrm;
667     raise_application_error(-20000,gReturnError);
668 END deleteCommonTables;
669 
670 /**
671  ** Proc : deleteCategoryRelatedInfo
672  ** Desc : Deletes the data from ICX_CAT_BROWSE_TREES,
673  **        ICX_POR_CATEGORY_ORDER_MAP, ICX_POR_CATEGORY_DATA_SOURCES
674  **/
675 PROCEDURE deleteCategoryRelatedInfo(pRtCategoryId  IN NUMBER,
676                                     pCategoryKey   IN VARCHAR2)
677 IS
678   xErrLoc       PLS_INTEGER := 0;
679   xContinue     BOOLEAN := TRUE;
680 
681 BEGIN
682   ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_BROWSE_TREES');
683   --Delete the item category from browse trees if it existed in any toc.
684   xErrLoc := 100;
685   DELETE FROM icx_cat_browse_trees
686   WHERE  child_category_id = pRtCategoryId;
687 
691   DELETE FROM icx_por_category_order_map
688   ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_ORDER_MAP');
689   --Delete the mapping for the item category.
690   xErrLoc := 200;
692   WHERE  rt_category_id = pRtCategoryId;
693 
694   ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_DATA_SOURCES');
695   --Delete the mapping for the item category.
696   xErrLoc := 300;
697   DELETE FROM icx_por_category_data_sources
698   WHERE  category_key = pCategoryKey;
699 
700 EXCEPTION
701   WHEN OTHERS THEN
702     gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.deleteCategoryRelatedInfo(' ||
703       xErrLoc|| '): '||sqlerrm;
704     raise_application_error(-20000,gReturnError);
705 END deleteCategoryRelatedInfo;
706 
707 END ICX_POR_DELETE_CATALOG;