[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_CTX_DESC
Source
1 PACKAGE BODY ICX_POR_CTX_DESC AS
2 -- $Header: ICXCGCDB.pls 115.22 2004/07/14 13:17:41 sosingha ship $
3
4 -- Previous releases of iProcurement had p_rebuildAll meaning, to rebuild
5 -- ctx column for all language or just the language for which item was
6 -- loaded. Eitherway the rebuild always happened. With just one ctx column
7 -- with this release, the p_rebuildAll is not useful. Always rebuild the
8 -- ctx_desc column to be backward compatible with the scripts that had
9 -- been released with calls to populateCtxDescAll!
10 --
11 -- The populateCtxDescAll is called only from Extractor and ECManager, in both
12 -- cases the log type is concurrent, hence the default value is "CONCURRENT".
13 --
14 PROCEDURE populateCtxDescAll(p_jobno IN INTEGER DEFAULT 0,
15 p_rebuildAll in VARCHAR2 DEFAULT 'Y',
16 p_log_type in VARCHAR2 DEFAULT 'CONCURRENT') IS
17 xErrLoc INTEGER := 0; -- execution location for error trapping
18 items_tl_cv item_source_cv_type;
19 items_tl_csr item_source_cv_type;
20
21 vRowids dbms_sql.urowid_table;
22 BEGIN
23 xErrLoc := 100;
24
25 IF (p_jobno <= 0) THEN
26 populateDescAll(p_log_type);
27 ELSE
28 OPEN items_tl_cv for
29 SELECT tl.rowid, tl.rt_item_id, tl.language
30 FROM icx_cat_items_tlp tl
31 WHERE tl.request_id = p_jobno;
32
33 xErrLoc := 300;
34 populateCtxDescBaseAtt(items_tl_cv,'Y', 'Y', NULL, 'ROWID', 'CONCURRENT');
35 xErrLoc := 330;
36 CLOSE items_tl_cv;
37
38 xErrLoc := 350;
39 OPEN items_tl_cv for
40 SELECT tl.rowid, tl.rt_item_id, tl.language
41 FROM icx_cat_items_tlp tl
42 WHERE tl.request_id = p_jobno;
43
44 xErrLoc := 400;
45 populateCtxDescBuyerInfo(items_tl_cv, 'Y', 'Y', null, 'ROWID', 'CONCURRENT');
46 xErrLoc := 500;
47 CLOSE items_tl_cv;
48
49 xErrLoc := 600;
50 populateCategoryAttribsByJob(p_jobno, 'N', 'N', 'CONCURRENT');
51
52 xErrLoc := 700;
53
54 -- Update the icx_cat_items_tlp.ctx_desc column so that rebuild index
55 -- will pick up the changes. Master table index column need to be
56 -- updated in order for the detail table changes to be effective
57 OPEN items_tl_csr for
58 SELECT rowid FROM icx_cat_items_tlp
59 where request_id = p_jobno;
60
61 xErrLoc := 720;
62
63 --Debugging
64 icx_por_ext_utl.debug('about to update icx_cat_items_tlp.ctx_desc');
65 xErrLoc := 730;
66 LOOP
67 FETCH items_tl_csr BULK COLLECT INTO
68 vRowids
69 LIMIT BATCH_SIZE;
70
71 EXIT WHEN vRowids.COUNT = 0;
72 xErrLoc := 750;
73
74 FORALL i IN 1..vRowids.COUNT
75 UPDATE ICX_CAT_ITEMS_TLP
76 SET CTX_DESC = null
77 WHERE rowid = vRowids(i);
78
79 xErrLoc := 800;
80
81 --Bug#2849869: added due to the 8i issue.
82 IF (vRowids.COUNT < BATCH_SIZE) THEN
83 EXIT;
84 END IF;
85 xErrLoc := 900;
86
87 END LOOP;
88 xErrLoc := 910;
89 --Debugging
90 icx_por_ext_utl.debug('done updating icx_cat_items_tlp.ctx_desc');
91 xErrLoc := 920;
92
93 CLOSE items_tl_csr;
94
95 xErrLoc := 930;
96 --Debugging
97 icx_por_ext_utl.debug('start to rebuild index');
98 xErrLoc := 940;
99 -- rebuild the intermedia or context indexes
100 ICX_POR_INTERMEDIA_INDEX.rebuild_index;
101 xErrLoc := 1000;
102 --Debugging
103 icx_por_ext_utl.debug('rebuild index done');
104 END IF;
105 xErrLoc := 1010;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 icx_por_ext_utl.debug(icx_por_ext_utl.DEBUG_LEVEL,
110 'Exception at ICX_POR_CTX_DESC.populateCtxDescAll('||
111 xErrLoc || '), ' || SQLERRM);
112 rollback;
113 ICX_POR_EXT_UTL.printStackTrace;
114 ICX_POR_EXT_UTL.closeLog;
115
116 IF items_tl_cv%ISOPEN THEN
117 CLOSE items_tl_cv;
118 END IF;
119
120 RAISE_APPLICATION_ERROR (-20000,
121 'Exception at ICX_POR_CTX_DESC.populateCtxDescAll('||xErrLoc|| '), '||SQLERRM );
122 END populateCtxDescAll;
123
124 PROCEDURE populateDescAll(errbuf OUT NOCOPY VARCHAR2,
125 retcode OUT NOCOPY VARCHAR2,
126 p_log_type in VARCHAR2 DEFAULT 'CONCURRENT')
127 IS
128 BEGIN
129 populateDescAll(p_log_type);
130 END;
131
132 PROCEDURE populateDescAll(p_log_type in VARCHAR2 DEFAULT 'CONCURRENT')
133 IS
134 v_sql varchar2(255);
135 BEGIN
136 --Debugging added icx_por_ext_utl.debug
137 v_sql := 'truncate table '|| ICX_POR_EXT_UTL.getIcxSchema ||'.icx_cat_items_ctx_tlp';
138 icx_por_ext_utl.debug('populateDescAll, about to truncate icx_cat_items_ctx_tlp');
139 EXECUTE IMMEDIATE v_sql;
140 icx_por_ext_utl.debug('populateDescAll, icx_cat_items_ctx_tlp truncated ');
141
142 ICX_POR_INTERMEDIA_INDEX.drop_index;
143 icx_por_ext_utl.debug('populateDescAll, drop_index done ');
144
145 populateBaseAttributes('N', 'N', p_log_type);
146 icx_por_ext_utl.debug('populateDescAll, populateBaseAttributes done ');
147
148 populateBuyerInfo('N','N', p_log_type);
149 icx_por_ext_utl.debug('populateDescAll, populateBuyerInfo done ');
150
151 populateCategoryAttributes('N', 'N', p_log_type);
152 icx_por_ext_utl.debug('populateDescAll, populateCategoryAttributes done ');
153
154 ICX_POR_INTERMEDIA_INDEX.create_index;
155 icx_por_ext_utl.debug('populateDescAll, create_index done ');
156
157 -- disable the intermedia index concurrent program once it has been run successfully
158 -- we will use FND API FND_PROGRAM.ENABLE_PROGRAM for achieving the same
159 icx_por_ext_utl.debug('populateDescAll, disabling intermedia index concurrent program to prevent re-run');
160 fnd_program.enable_program('ICXCICRI', 'ICX', 'N');
161 icx_por_ext_utl.debug('populateDescAll, intermedia index concurrent program disabled ');
162
163 END;
164
165 /*
166 ** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
167 ** Procedure : populateBaseAttributes
168 ** Synopsis : Update the ctx_<lang> for all the items in a given job.
169 ** ActionPlan: 1. Collect root descriptors and local descriptors from
170 ** cursors defined above.
171 ** 2. Concatenate all from 1 into a 'allSelectList' and
172 ** 3. Update ctx_<lang> in icx_cat_items_ctx_tlp with 2 .
173 ** 4. Update ctx_desc in icx_cat_items_tlp with null : Bug#3329169
174 ** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
175 */
176
177 PROCEDURE populateBaseAttributes(pDeleteYN IN VARCHAR2 DEFAULT 'Y',
178 pUpdateYN IN VARCHAR2 DEFAULT 'Y', p_log_type IN VARCHAR2 DEFAULT 'LOADER')
179 IS
180
181 xErrLoc PLS_INTEGER := 0;
182 getItemsTlCur item_source_cv_type;
183
184 -- Bug # 3329169
185 items_tl_csr item_source_cv_type;
186 vRowids dbms_sql.urowid_table;
187
188 BEGIN
189 xErrLoc := 100;
190
191 --Debugging
192 --icx_por_track_validate_job_s.log('populateBaseAttributes', p_log_type);
193
194 OPEN getItemsTlCur FOR
195 SELECT rowid,rt_item_id,language
196 FROM icx_cat_items_tlp;
197
198 xErrLoc := 200;
199
200 populateCtxDescBaseAtt(getItemsTlCur, pDeleteYN, pUpdateYN, NULL, 'ROWID', p_log_type);
201 xErrLoc := 230;
202
203 CLOSE getItemsTlCur;
204
205 xErrLoc := 300;
206 -- Bug#3329169
207 -- Update the icx_cat_items_tlp.ctx_desc column so that rebuild index
208 -- will pick up the changes. Master table index column need to be
209 -- updated in order for the detail table changes to be effective
210 OPEN items_tl_csr FOR
211 SELECT rowid FROM icx_cat_items_tlp;
212
213 xErrLoc := 320;
214 LOOP
215 FETCH items_tl_csr BULK COLLECT INTO
216 vRowids
217 LIMIT BATCH_SIZE;
218 EXIT WHEN vRowids.COUNT = 0;
219
220 xErrLoc := 340;
221 FORALL i IN 1..vRowids.COUNT
222 UPDATE ICX_CAT_ITEMS_TLP
223 SET CTX_DESC = null
224 WHERE rowid = vRowids(i);
225
226 xErrLoc := 360;
227 IF (vRowids.COUNT < BATCH_SIZE) THEN
228 EXIT;
229 END IF;
230 END LOOP;
231
232 xErrLoc := 380;
233 CLOSE items_tl_csr;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 ROLLBACK;
238
239 IF getItemsTlCur%ISOPEN THEN
240 CLOSE getItemsTlCur;
241 END IF;
242
243 IF items_tl_csr%ISOPEN THEN
244 CLOSE items_tl_csr;
245 END IF;
246
247 RAISE_APPLICATION_ERROR (-20000,
248 'Exception at ICX_POR_CTX_DESC.populateBaseAttributes('||xErrLoc|| '), '||SQLERRM );
249
250 END populateBaseAttributes;
251
252 PROCEDURE populateCategoryAttribsByJob( pJobNum IN INTEGER DEFAULT 0,
253 pDeleteYN IN VARCHAR2 DEFAULT 'Y',
254 pUpdateYN IN VARCHAR2 DEFAULT 'Y',
255 p_log_type IN VARCHAR2 DEFAULT 'LOADER')
256 IS
257
258 vCategoryId INTEGER := 0;
259 vLang FND_LANGUAGES.language_code%TYPE;
260 xErrLoc PLS_INTEGER;
261 vItemSourceCursor NUMBER;
262 vSqlString VARCHAR2(4000);
263
264
265 -- Get all the categories for a given language
266 -- that has atleast one searchable attribute
267 -- and has atleast one item loaded by the given job.
268
269 CURSOR getCatWithSearchCur(p_lang IN VARCHAR2, p_jobNum IN INTEGER) is
270 SELECT cat.rt_category_id
271 FROM icx_cat_categories_tl cat
272 WHERE cat.language = p_lang
273 AND cat.rt_category_id <> 0
274 AND cat.type = 2
275 AND exists (select 'X'
276 from icx_cat_descriptors_tl des
277 where des.rt_category_id = cat.rt_category_id
278 and des.language = cat.language
279 and des.searchable = 1)
280 AND exists (select 'X'
281 from icx_cat_category_items cit, icx_cat_items_b it
282 where cit.rt_category_id = cat.rt_category_id
283 and cit.rt_item_id = it.rt_item_id
284 and it.request_id = p_jobNum);
285 BEGIN
286 xErrLoc:=100;
287
288 --Debugging
289 --icx_por_track_validate_job_s.log('populateCategoryAttribsByJob', p_log_type);
290 --Debugging
291 icx_por_ext_utl.debug('start of populateCategoryAttribsByJob');
292 xErrLoc := 110;
293
294 BEGIN
295 SELECT language_code
296 INTO vLang
297 FROM fnd_languages
298 WHERE installed_flag = 'B';
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 vLang := 'US';
302 END;
303
304 -- For Category Attributes
305 -- Loop thru the Category Cursor and get the Insert and Update Tables
306 xErrLoc:=200;
307 FOR catRec in getCatWithSearchCur(vLang, pJobNum)
308 LOOP
309 vCategoryId := catRec.rt_category_id;
310 vItemSourceCursor := DBMS_SQL.OPEN_CURSOR;
311 xErrLoc := 220;
312 -- OEX_IP_PORTING
313 vSqlString := 'SELECT tlp.rowid,tlp.rt_item_id,tlp.language FROM ICX_CAT_EXT_ITEMS_TLP tlp, icx_cat_items_b it where tlp.rt_category_id = :category_id and it.rt_item_id=tlp.rt_item_id and it.request_id=:request_id';
314 xErrLoc := 230;
315 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
316 xErrLoc := 235;
317 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':category_id',vCategoryId);
318 xErrLoc := 240;
319 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':request_id',pJobNum);
320 xErrLoc := 245;
321 populateCtxDescCatAtt(vCategoryId, vItemSourceCursor,pDeleteYN,
322 pUpdateYN, NULL, 'ROWID', p_log_type);
323 xErrLoc:=260;
324 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
325 xErrLoc:=280;
326 END LOOP;
327 xErrLoc:=300;
328 --Debugging
329 icx_por_ext_utl.debug('populateCategoryAttribsByJob done');
330 xErrLoc := 1001;
331 EXCEPTION
332 WHEN OTHERS THEN
333 ROLLBACK;
334
335 IF (DBMS_SQL.IS_OPEN(vItemSourceCursor)) THEN
336 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
337 END IF;
338
339 RAISE_APPLICATION_ERROR (-20000,
340 'Exception at ICX_POR_CTX_DESC.populateCategoryAttribsByJob('||xErrLoc|| '), catId: ' || vCategoryId || ' Error: ' ||SQLERRM );
341 END populateCategoryAttribsByJob;
342
343 PROCEDURE populateCategoryAttributes( pDeleteYN IN VARCHAR2 DEFAULT 'Y',
344 pUpdateYN IN VARCHAR2 DEFAULT 'Y',
345 p_log_type IN VARCHAR2 DEFAULT 'LOADER')
346 IS
347
348 vCategoryId INTEGER := 0;
349 vLang FND_LANGUAGES.language_code%TYPE;
350 xErrLoc PLS_INTEGER;
351 vItemSourceCursor NUMBER;
352 vSqlString VARCHAR2(4000);
353
354 -- Get all the categories for a given language
355 -- that has atleast one searchable attribute
356 -- and has atleast one item classified under it
357
358 CURSOR getCatWithSearchCur(p_lang IN VARCHAR2) is
359 SELECT cat.rt_category_id
360 FROM icx_cat_categories_tl cat
361 WHERE cat.language = p_lang
362 AND cat.rt_category_id <> 0
363 AND cat.type = 2
364 AND exists (select 'X'
365 from icx_cat_descriptors_tl des
366 where des.rt_category_id = cat.rt_category_id
367 and des.language = cat.language
368 and des.searchable = 1)
369 AND exists (select 'X'
370 from icx_cat_category_items cit
371 where cit.rt_category_id = cat.rt_category_id);
372 BEGIN
373 xErrLoc:=100;
374 --Debugging
375 icx_por_ext_utl.debug('start of populateCategoryAttributes ');
376 xErrLoc := 101;
377 BEGIN
378 SELECT language_code
379 INTO vLang
380 FROM fnd_languages
381 WHERE installed_flag = 'B';
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 vLang := 'US';
385 END;
386
387 -- For Category Attributes
388 -- Loop thru the Category Cursor and get the Insert and Update Tables
389 xErrLoc:=200;
390 FOR catRec in getCatWithSearchCur(vLang)
391 LOOP
392 vCategoryId := catRec.rt_category_id;
393 vItemSourceCursor := DBMS_SQL.OPEN_CURSOR;
394 xErrLoc := 220;
395 -- OEX_IP_PORTING
396 -- Add "and rt_item_id = :rt_item_ids". Bind array of the
397 -- item ids table
398 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP where rt_category_id = :category_id';
399 xErrLoc := 230;
400 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
401 xErrLoc := 235;
402 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':category_id',vCategoryId);
403 xErrLoc := 240;
404 populateCtxDescCatAtt(vCategoryId, vItemSourceCursor,pDeleteYN,
405 pUpdateYN, NULL, 'ROWID', p_log_type);
406 xErrLoc:=260;
407 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
408 xErrLoc:=280;
409 END LOOP;
410 xErrLoc:=300;
411 --Debugging
412 icx_por_ext_utl.debug('populateCategoryAttributes done');
413 xErrLoc := 1001;
414 EXCEPTION
415 WHEN OTHERS THEN
416 ROLLBACK;
417
418 IF (DBMS_SQL.IS_OPEN(vItemSourceCursor)) THEN
419 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
420 END IF;
421
422 RAISE_APPLICATION_ERROR (-20000,
423 'Exception at ICX_POR_CTX_DESC.populateCategoryAttributes('||xErrLoc|| '), catId: ' || vCategoryId || ' Error: ' ||SQLERRM );
424 END populateCategoryAttributes;
425
426 /* The calling procedure is responsible for opening and closing the cursor*/
427 PROCEDURE populateCtxDescBaseAtt(pItemSourceCv IN item_source_cv_type,
428 pDeleteYN IN VARCHAR2 DEFAULT 'Y',
429 pUpdateYN IN VARCHAR2 DEFAULT 'Y',
430 pLanguage IN VARCHAR2 DEFAULT NULL,
431 pSourceType IN VARCHAR2 DEFAULT 'ROWID',
432 p_log_type IN VARCHAR2 DEFAULT 'LOADER') IS
433 xErrLoc INTEGER := 0; -- execution location for error trapping
434 vInsertSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
435 vUpdateSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
436 vRowidTab DBMS_SQL.UROWID_TABLE;
437 vItemIdTab DBMS_SQL.NUMBER_TABLE;
438 vLangTab DBMS_SQL.VARCHAR2_TABLE;
439 c_handle NUMBER ;
440 c_status PLS_INTEGER;
441 vSqlString VARCHAR2(4000);
442 p_lang VARCHAR2(10) := NULL;
443
444 BEGIN
445
446 xErrLoc := 0;
447 --Debugging
448 icx_por_ext_utl.debug('start to process populateCtxDescBaseAtt');
449 xErrLoc := 10;
450 IF (pSourceType = 'ROWID') THEN
451 ICX_POR_CTX_SQL_PKG.build_ctx_sql(0,
452 ICX_POR_CTX_SQL_PKG.ROWID_WHERE_CLAUSE, pLanguage, vInsertSqlTab, vUpdateSqlTab);
453 ElSE
454 ICX_POR_CTX_SQL_PKG.build_ctx_sql(0,
455 ICX_POR_CTX_SQL_PKG.ITEMID_WHERE_CLAUSE, pLanguage, vInsertSqlTab, vUpdateSqlTab);
456 END IF;
457
458 xErrLoc := 15;
459 --Debugging
460 icx_por_ext_utl.debug('populateCtxDescBaseAtt, vUpdateSqlTab count:' ||
461 to_char(vUpdateSqlTab.COUNT) );
462
463 xErrLoc := 11;
464
465 LOOP
466 vRowidTab.DELETE;
467 vItemIdTab.DELETE;
468 vLangTab.DELETE;
469
470 xErrLoc := 100;
471 IF (pSourceType = 'ROWID') THEN
472 FETCH pItemSourceCv BULK COLLECT INTO vRowidTab,vItemIdTab,vLangTab LIMIT BATCH_SIZE;
473 ELSE
474 FETCH pItemSourceCv BULK COLLECT INTO vItemIdTab,vLangTab LIMIT BATCH_SIZE;
475 END IF;
476
477 xErrLoc := 110;
478 --Debugging
479 icx_por_ext_utl.debug('populateCtxDescBaseAtt, vItemIdTab count:' ||
480 to_char(vItemIdTab.COUNT) );
481
482 xErrLoc := 200;
483 IF vItemIdTab.COUNT = 0 THEN
484 EXIT;
485 END IF;
486
487 xErrLoc := 300;
488
489 -- delete exisiting rows from icx_cat_items_ctx_tlp
490 IF (pDeleteYN = 'Y') THEN
491 FORALL i in 1..vItemIdTab.COUNT
492 DELETE FROM icx_cat_items_ctx_tlp
493 WHERE rt_item_id = vItemIdTab(i)
494 AND language = vLangTab(i)
495 AND (sequence < 5000 OR sequence = 10000 OR sequence = 15000);
496 END IF;
497
498 xErrLoc := 400;
499 -- SqlTab Loop
500 FOR i in 1..vUpdateSqlTab.COUNT LOOP
501 xErrLoc := xErrLoc + 10;
502 vSqlString := vUpdateSqlTab(i);
503 c_handle:=DBMS_SQL.OPEN_CURSOR;
504 DBMS_SQL.PARSE(c_handle, vSqlString, DBMS_SQL.NATIVE);
505
506 IF (i = vUpdateSqlTab.COUNT - 1) THEN
507 -- This is the <orgid> line
508 DBMS_SQL.BIND_VARIABLE(c_handle,':p_sequence',10000);
509 ELSIF (i = vUpdateSqlTab.COUNT) THEN
510 -- This is the </orgid> line
511 DBMS_SQL.BIND_VARIABLE(c_handle,':p_sequence',15000);
512 ELSE
513 DBMS_SQL.BIND_VARIABLE(c_handle,':p_sequence',i);
514 END IF;
515
516 DBMS_SQL.BIND_VARIABLE(c_handle,':action_name','SYNC');
517 DBMS_SQL.BIND_VARIABLE(c_handle,':p_system_action','SYNC');
518
519 IF (pSourceType = 'ROWID') THEN
520 DBMS_SQL.BIND_ARRAY(c_handle,':p_rowid',vRowidTab);
521 ELSE
522 DBMS_SQL.BIND_ARRAY(c_handle,':p_item_id',vItemIdTab);
523 END IF;
524
525 IF (NOT pLanguage IS NULL) THEN
526 DBMS_SQL.BIND_VARIABLE(c_handle,':p_language',pLanguage);
527 DBMS_SQL.BIND_ARRAY(c_handle, ':language_array', vLangTab);
528 DBMS_SQL.BIND_VARIABLE(c_handle,':p_language_section','<lang>'||pLanguage||'</lang>');
529 END IF;
530
531 c_status := DBMS_SQL.EXECUTE(c_handle);
532 DBMS_SQL.CLOSE_CURSOR(c_handle);
533 xErrLoc := xErrLoc + 10;
534 END LOOP; -- SqlTab Loop
535
536 xErrLoc := 500;
537
538 COMMIT;
539 END LOOP;
540 xErrLoc := 1001;
541 --Debugging
542 icx_por_ext_utl.debug('populateCtxDescBaseAtt done');
543 EXCEPTION
544 WHEN OTHERS THEN
545 ROLLBACK;
546
547 IF (DBMS_SQL.IS_OPEN(c_handle)) THEN
548 DBMS_SQL.CLOSE_CURSOR(c_handle);
549 END IF;
550
551 RAISE_APPLICATION_ERROR (-20000,
552 'Exception at ICX_POR_CTX_DESC.populateCtxDescBaseAtt('||xErrLoc||
553 '), '||SQLERRM );
554 END populateCtxDescBaseAtt;
555
556 /* The calling procedure is responsible for opening and closing the cursor.
557 pSourceType = 'ITEMID' doesn't work properly now.
558 */
559 PROCEDURE populateCtxDescCatAtt(pCategoryId IN NUMBER,
560 pItemSourceCursor IN NUMBER,
561 pDeleteYN IN VARCHAR2 DEFAULT 'Y',
562 pUpdateYN IN VARCHAR2 DEFAULT 'Y',
563 pLanguage IN VARCHAR2 DEFAULT NULL,
564 pSourceType IN VARCHAR2 DEFAULT 'ROWID',
565 p_log_type IN VARCHAR2 DEFAULT 'LOADER')
566 IS
567 p_lang VARCHAR2(10) := NULL;
568 vCInsertSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
569 vCUpdateSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
570 vCRowidTab DBMS_SQL.UROWID_TABLE;
571 vCLangTab DBMS_SQL.VARCHAR2_TABLE;
572 vCItemIdTab DBMS_SQL.NUMBER_TABLE;
573 c_handle NUMBER;
574 c_status PLS_INTEGER;
575 vSqlString VARCHAR2(4000);
576 vTableName VARCHAR2(100);
577 vIndex NUMBER:=1;
578 vCStatus NUMBER;
579 xErrLoc PLS_INTEGER;
580 BEGIN
581 xErrLoc := 100;
582 --Debugging
583 icx_por_ext_utl.debug('start of populateCtxDescCatAtt for pCategoryId:' ||
584 to_char(pCategoryId) );
585 xErrLoc := 101;
586 vCInsertSqlTab.DELETE;
587 vCUpdateSqlTab.DELETE;
588
589 xErrLoc := 110;
590 IF(pSourceType = 'ROWID') THEN
591 xErrLoc := 120;
592 ICX_POR_CTX_SQL_PKG.build_ctx_sql(pCategoryId,
593 ICX_POR_CTX_SQL_PKG.ROWID_WHERE_CLAUSE, pLanguage,
594 vCInsertSqlTab, vCUpdateSqlTab);
595 xErrLoc := 140;
596 ELSE
597 xErrLoc := 160;
598 ICX_POR_CTX_SQL_PKG.build_ctx_sql(pCategoryId,
599 ICX_POR_CTX_SQL_PKG.ITEMID_WHERE_CLAUSE, pLanguage,
600 vCInsertSqlTab, vCUpdateSqlTab);
601 xErrLoc := 180;
602 END IF;
603
604 xErrLoc := 190;
605 --Debugging
606 icx_por_ext_utl.debug('populateCtxDescCatAtt, vCUpdateSqlTab count:' ||
607 to_char(vCUpdateSqlTab.COUNT) );
608
609
610 -----------------------------------------------------------------------
611 -- Build a cursor with the category ID added the SQL
612 -- Define PL/SQL tables that will hold the value fetched
613 -----------------------------------------------------------------------
614 --vCursor := DBMS_SQL.OPEN_CURSOR;
615 --DBMS_SQL.PARSE(vCursor,'SELECT rowid,rt_item_id,language FROM '||
616 -- vTableName, DBMS_SQL.NATIVE);
617 xErrLoc := 200;
618 IF(pSourceType = 'ROWID') THEN
619 xErrLoc := 220;
620 DBMS_SQL.DEFINE_ARRAY(pItemSourceCursor,1,vCRowidTab,BATCH_SIZE,vIndex);
621 DBMS_SQL.DEFINE_ARRAY(pItemSourceCursor,2,vCItemIdTab,BATCH_SIZE,vIndex);
622 DBMS_SQL.DEFINE_ARRAY(pItemSourceCursor,3,vCLangTab,BATCH_SIZE,vIndex);
623 xErrLoc := 240;
624 ELSE
625 xErrLoc := 260;
626 DBMS_SQL.DEFINE_ARRAY(pItemSourceCursor,1,vCItemIdTab,BATCH_SIZE,vIndex);
627 DBMS_SQL.DEFINE_ARRAY(pItemSourceCursor,2,vCLangTab,BATCH_SIZE,vIndex);
628 xErrLoc := 280;
629 END IF;
630
631 xErrLoc := 300;
632 vCStatus := DBMS_SQL.EXECUTE(pItemSourceCursor);
633 -----------------------------------------------------------------------
634 -- Loop thru the cursor
635 -- FETCH_ROWS will fetch the next set of rows and fill the PL/SQL tables
636 -- Loop thru the UpdateSQL tab and for each sql
637 -- Bind in the values from the PL/SQL tables
638 -----------------------------------------------------------------------
639
640 xErrLoc := 400;
641 LOOP
642
643 vCStatus := DBMS_SQL.FETCH_ROWS(pItemSourceCursor);
644 IF(pSourceType = 'ROWID') THEN
645 DBMS_SQL.COLUMN_VALUE(pItemSourceCursor,1,vCRowidTab);
646 DBMS_SQL.COLUMN_VALUE(pItemSourceCursor,2,vCItemIdTab);
647 DBMS_SQL.COLUMN_VALUE(pItemSourceCursor,3,vCLangTab);
648 ELSE
649 DBMS_SQL.COLUMN_VALUE(pItemSourceCursor,1,vCItemIdTab);
650 DBMS_SQL.COLUMN_VALUE(pItemSourceCursor,2,vCLangTab);
651 END IF;
652
653 xErrLoc := 310;
654 --Debugging
655 icx_por_ext_utl.debug('populateCtxDescCatAtt, vCItemIdTab count:' ||
656 to_char(vCItemIdTab.COUNT) );
657
658 xErrLoc := 320;
659 IF (vCItemIdTab.COUNT = 0) THEN
660 EXIT;
661 END IF;
662 xErrLoc := 500;
663 IF (pDeleteYN = 'Y') THEN
664 -- Delete the rows for the categories
665 xErrLoc := xErrLoc + 10;
666 -- dbms_sql.column_value will keep incrementing the indexes of the
667 -- pl/sql tables, so need to use FIRST..LAST instead of 1..COUNT
668 FORALL i IN vCItemIdTab.FIRST..vCItemIdTab.LAST
669 DELETE FROM icx_cat_items_ctx_tlp
670 WHERE rt_item_id = vCItemIdTab(i)
671 AND sequence >= 5000
672 AND sequence < 10000
673 AND language = vCLangTab(i);
674 END IF;
675
676 xErrLoc := 600;
677 FOR i in 1..vCUpdateSqlTab.COUNT LOOP
678 xErrLoc := xErrLoc + 10;
679 vSqlString := vCUpdateSqlTab(i);
680 c_handle:=DBMS_SQL.OPEN_CURSOR;
681 DBMS_SQL.PARSE(c_handle, vSqlString, DBMS_SQL.NATIVE);
682 DBMS_SQL.BIND_VARIABLE(c_handle, ':p_sequence',i+5000);
683 DBMS_SQL.BIND_VARIABLE(c_handle,':current_category_id',pCategoryId);
684 DBMS_SQL.BIND_VARIABLE(c_handle,':p_category_id',pCategoryId);
685 DBMS_SQL.BIND_VARIABLE(c_handle, ':action_name','SYNC');
686 DBMS_SQL.BIND_VARIABLE(c_handle, ':p_system_action','SYNC');
687 IF(pSourceType = 'ROWID') THEN
688 DBMS_SQL.BIND_ARRAY(c_handle, ':p_rowid',vCRowidTab);
689 ELSE
690 DBMS_SQL.BIND_ARRAY(c_handle, ':p_item_id',vCItemIdTab);
691 END IF;
692
693 IF (NOT pLanguage IS NULL) THEN
694 DBMS_SQL.BIND_VARIABLE(c_handle,':p_language',pLanguage);
695 DBMS_SQL.BIND_ARRAY(c_handle, ':language_array', vCLangTab);
696 DBMS_SQL.BIND_VARIABLE(c_handle,':p_language_section','<lang>'||pLanguage||'</lang>');
697 END IF;
698
699 c_status := DBMS_SQL.EXECUTE(c_handle);
700 DBMS_SQL.CLOSE_CURSOR(c_handle);
701 END LOOP;
702
703 xErrLoc := 700;
704 IF (pUpdateYN = 'Y') THEN
705 IF(pLanguage IS NULL) THEN
706 FOR language_row IN installed_languages_cur LOOP
707 p_lang := language_row.language_code;
708
709 vSqlString := 'UPDATE icx_cat_items_tlp SET ctx_desc ' ||
710 ' = ''1'' WHERE rt_item_id = :p_item_id AND '||
711 ':curr_lang = :p_lang AND language = :p_lang';
712
713 c_handle := DBMS_SQL.OPEN_CURSOR;
714 DBMS_SQL.PARSE(c_handle, vSqlString, DBMS_SQL.NATIVE);
715 DBMS_SQL.BIND_ARRAY(c_handle, ':p_item_id', vCItemIdTab);
716 DBMS_SQL.BIND_VARIABLE(c_handle, ':curr_lang', p_lang);
717 DBMS_SQL.BIND_ARRAY(c_handle, ':p_lang', vCLangTab);
718 c_status := DBMS_SQL.EXECUTE(c_handle);
719 DBMS_SQL.CLOSE_CURSOR(c_handle);
720 END LOOP;
721 ELSE
722 vSqlString := 'UPDATE icx_cat_items_tlp SET ctx_desc ' ||
723 ' = ''1'' WHERE rt_item_id = :p_item_id AND '||
724 'language = :p_lang';
725
726 c_handle := DBMS_SQL.OPEN_CURSOR;
727 DBMS_SQL.PARSE(c_handle, vSqlString, DBMS_SQL.NATIVE);
728 DBMS_SQL.BIND_ARRAY(c_handle, ':p_item_id', vCItemIdTab);
729 DBMS_SQL.BIND_VARIABLE(c_handle, ':p_lang', pLanguage);
730 c_status := DBMS_SQL.EXECUTE(c_handle);
731 DBMS_SQL.CLOSE_CURSOR(c_handle);
732 END IF;
733 END IF;
734
735 xErrLoc := 800;
736 COMMIT;
737 vCRowidTab.DELETE;
738 vCLangTab.DELETE;
739 vCItemIdTab.DELETE;
740 EXIT when vCStatus <> BATCH_SIZE;
741 END LOOP;
742 xErrLoc := 1001;
743 --Debugging
744 icx_por_ext_utl.debug('populateCtxDescCatAtt done');
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 ROLLBACK;
749
750 IF (DBMS_SQL.IS_OPEN(c_handle)) THEN
751 DBMS_SQL.CLOSE_CURSOR(c_handle);
752 END IF;
753
754 RAISE_APPLICATION_ERROR (-20000,
755 'Exception at ICX_POR_CTX_DESC.populateCtxDescCatAtt('||xErrLoc||
756 '), '||SQLERRM );
757 END populateCtxDescCatAtt;
758 /*
759 ** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
760 ** Procedure : populateCtxDescLang
761 ** Synopsis : Update the ctx_<lang> for one item in a Lang.
762 ** This is called from Item.insert() and Item.update().
763 */
764
765 PROCEDURE populateCtxDescLang(p_item_id IN NUMBER,
766 p_category_id IN NUMBER,
767 p_lang IN VARCHAR2 DEFAULT NULL,
768 p_log_type IN VARCHAR2 DEFAULT 'LOADER') IS
769
770 xErrLoc PLS_INTEGER := 0; -- execution location for error trapping
771 vItemSourceCv item_source_cv_type;
772 vItemSourceCursor NUMBER;
773 vSqlString VARCHAR2(4000);
774 vCatTableExists PLS_INTEGER:=0;
775 vSearchableExists PLS_INTEGER:=0;
776 BEGIN
777
778 xErrLoc := 11;
779 --Debugging
780 icx_por_ext_utl.debug('start of populateCtxDescLang for itemId:' ||
781 to_char(p_item_id) ||', categoryId:' ||
782 to_char(p_category_id) ||', lang:' ||p_lang);
783 xErrLoc := 12;
784
785 populateCtxDescBaseAtt(vItemSourceCv, 'Y', 'Y', NULL, 'ITEMID', 'LOADER');
786
787 -- base attributes
788 xErrLoc := 100;
789 OPEN vItemSourceCv FOR
790 SELECT rowid,rt_item_id, language
791 FROM ICX_CAT_ITEMS_TLP
792 WHERE RT_ITEM_ID= p_item_id;
793 xErrLoc := 200;
794
795 IF (p_lang is NULL) THEN -- update item
796 xErrLoc := 220;
797 populateCtxDescBaseAtt(vItemSourceCv, 'Y', 'N', NULL, 'ROWID', p_log_type);
798 xErrLoc := 240;
799 ELSE -- create new item, no need to delete or update
800 xErrLoc := 260;
801 populateCtxDescBaseAtt(vItemSourceCv, 'N', 'N', p_lang, 'ROWID', p_log_type);
802 xErrLoc := 280;
803 END IF;
804
805 xErrLoc := 300;
806 CLOSE vItemSourceCv;
807
808 -- category attributes
809 xErrLoc := 400;
810 vItemSourceCursor := DBMS_SQL.OPEN_CURSOR;
811 xErrLoc := 450;
812
813 IF (p_lang is NULL) THEN -- update item
814 xErrLoc := 500;
815 BEGIN
816 SELECT 1
817 INTO vCatTableExists
818 FROM DUAL
819 WHERE EXISTS
820 (SELECT 1
821 FROM ICX_CAT_DESCRIPTORS_TL
822 WHERE RT_CATEGORY_ID=p_category_id
823 AND CLASS = 'POM_CAT_ATTR');
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 NULL;
827 END;
828
829 xErrLoc := 520;
830 -- OEX_IP_PORTING
831 IF(vCatTableExists = 1) THEN
832 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP' ||
833 ' WHERE rt_item_id=:item_id and rt_category_id=:category_id';
834 ELSE
835 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_ITEMS_TLP' ||
836 ' WHERE rt_item_id=:item_id';
837 END IF;
838
839 xErrLoc := 540;
840 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
841 xErrLoc := 560;
842 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':item_id',p_item_id);
843 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':category_id',p_category_id);
844
845 xErrLoc := 580;
846 populateCtxDescCatAtt(p_category_id, vItemSourceCursor,'Y',
847 'Y', p_lang, 'ROWID', p_log_type);
848 xErrLoc := 600;
849 ELSE -- insert item
850 xErrLoc := 620;
851 BEGIN
852 SELECT 1
853 INTO vSearchableExists
854 FROM DUAL
855 WHERE EXISTS
856 (SELECT 1
857 FROM ICX_CAT_DESCRIPTORS_TL
858 WHERE RT_CATEGORY_ID=p_category_id
859 AND CLASS = 'POM_CAT_ATTR'
860 AND SEARCHABLE = 1);
861 EXCEPTION
862 WHEN NO_DATA_FOUND THEN
863 NULL;
864 END;
865
866 xErrLoc := 640;
867 -- only need to do the following if the category has searchable attribute
868 IF(vSearchableExists = 1) THEN
869 xErrLoc := 680;
870 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_C'||p_category_id||'_TL' ||
871 ' WHERE rt_item_id=:item_id';
872 xErrLoc := 700;
873 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
874 xErrLoc := 720;
875 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor,':item_id',p_item_id);
876
877 xErrLoc := 740;
878 populateCtxDescCatAtt(p_category_id, vItemSourceCursor,'N',
879 'N', p_lang, 'ROWID');
880 xErrLoc := 760;
881 END IF;
882 xErrLoc := 800;
883 END IF;
884
885 xErrLoc := 900;
886 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
887 xErrLoc := 1000;
888 --Debugging
889 icx_por_ext_utl.debug('populateCtxDescLang done');
890 xErrLoc := 1001;
891 EXCEPTION
892 WHEN OTHERS THEN
893 ROLLBACK;
894
895 IF (vItemSourceCv%ISOPEN) THEN
896 CLOSE vItemSourceCv;
897 END IF;
898
899 IF (DBMS_SQL.IS_OPEN(vItemSourceCursor)) THEN
900 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
901 END IF;
902
903 RAISE_APPLICATION_ERROR (-20000,
904 'Exception at ICX_POR_CTX_DESC.populateCtxDescLang('||xErrLoc||
905 '), '||SQLERRM );
906 END populateCtxDescLang;
907
908 -- sosingha Bug#3460478: 10g fix for getting the major and minor db version
909 PROCEDURE rebuild_indexes IS
910 version number := 0;
911 xErrLoc integer := 0;
912 majorReleasePos NUMBER := 0;
913 minorReleasePos NUMBER := 0;
914 versionString varchar2(30) := null;
915 compatibility varchar2(30) := null;
916 majorVersion varchar2(10) := null;
917 minorVersion varchar2(10) := null;
918 plsqlBlock varchar2(1000) := null;
919 cursorID integer := 0;
920 result integer := 0;
921 BEGIN
922
923 xErrLoc := 10;
924 dbms_utility.db_version(versionString, compatibility);
925 --Debugging
926 --icx_por_track_validate_job_s.log('*******<<<>>>*** REBUILD_INDEX', 'LOADER');
927
928 xErrLoc := 11;
929 --Debugging
930 icx_por_ext_utl.debug('*******<<<>>>*** REBUILD_INDEX');
931 xErrLoc := 20;
932 /*
933 majorVersion := substr(versionString, 1, 1);
934 minorVersion := substr(versionString, 3, 1);
935 */
936 select instr(versionString, '.') into majorReleasePos from dual;
937 select instr(substr(versionString,majorReleasePos), '.')
938 into minorReleasePos from dual;
939
940 xErrLoc := 30;
941 majorVersion := substr(versionString, 1, majorReleasePos-1);
942 minorVersion := substr(versionString, majorReleasePos+1, minorReleasePos);
943
944 xErrLoc := 40;
945 version := to_number(majorVersion) + (to_number(minorVersion) / 10);
946
947 xErrLoc := 50;
948 cursorID := DBMS_SQL.open_cursor;
949 xErrLoc := 120;
950 IF version >= 8.1 THEN
951 --smallya Bug: 1713602 commented out the old package call and replaced it with the new one 04/06/2001--
952 --plsqlBlock := 'BEGIN icx_por_intermedia_index.rebuild_index; END;';
953 plsqlBlock := 'BEGIN ICX_POR_INTERMEDIA_INDEX.rebuild_index; END;';
954 /*icx_por_intermedia_index.rebuild_index;*/
955 ELSE
956 plsqlBlock := 'BEGIN icx_item_context_index_create.rebuild_item_context(''N''); END;';
957 /*icx_item_context_index_create.rebuild_item_context('N');*/
958 END IF;
959 xErrLoc := 121;
960 --Debugging
961 icx_por_ext_utl.debug('plsql call made in rebuild_indexes:' || plsqlBlock);
962 xErrLoc := 122;
963 dbms_sql.parse(cursorID, plsqlBlock, dbms_sql.NATIVE);
964 xErrLoc := 123;
965 --Debugging
966 icx_por_ext_utl.debug('start to rebuild_indexes');
967 xErrLoc := 130;
968 result := DBMS_SQL.execute(cursorID);
969 xErrLoc := 133;
970 --Debugging
971 icx_por_ext_utl.debug('rebuild_indexes done');
972 xErrLoc := 170;
973 dbms_sql.close_cursor(cursorID);
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 RAISE_APPLICATION_ERROR(-20000,
978 'Exception at ICX_POR_CTX_DESC.rebuild_indexes(' ||
979 xErrLoc||'), '|| SQLERRM || ' #### VARIABLES ####' ||
980 ' versionString = ' || versionString ||
981 ' majorVersion = ' || majorVersion ||
982 ' minorVersion = ' || minorVersion ||
983 ' version = ' || version);
984 END rebuild_indexes;
985
986 /*
987 ** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
988 ** Procedure : populateBuyerInfo
989 ** Synopsis : Update the ctx_<lang> to include buyer id info for all the items
990 ** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
991 */
992 PROCEDURE populateBuyerInfo( pDeleteYN IN VARCHAR2 DEFAULT 'Y',
993 pUpdateYN IN VARCHAR2 DEFAULT 'Y', p_log_type IN VARCHAR2 default 'LOADER')
994 IS
995 xErrLoc PLS_INTEGER := 0;
996 getItemsTlCur item_source_cv_type;
997 BEGIN
998 xErrLoc := 11;
999 --Debugging
1000 icx_por_ext_utl.debug('start of populateBuyerInfo');
1001
1002 xErrLoc := 100;
1003
1004 OPEN getItemsTlCur FOR
1005 SELECT rowid, rt_item_id, language
1006 FROM icx_cat_items_tlp;
1007
1008 xErrLoc := 200;
1009
1010 populateCtxDescBuyerInfo(getItemsTlCur, pDeleteYN, pUpdateYN, null, 'ROWID', p_log_type);
1011
1012 CLOSE getItemsTlCur;
1013 xErrLoc := 101;
1014 --Debugging
1015 icx_por_ext_utl.debug('populateBuyerInfo done');
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 ROLLBACK;
1020
1021 IF getItemsTlCur%ISOPEN THEN
1022 CLOSE getItemsTlCur;
1023 END IF;
1024
1025 RAISE_APPLICATION_ERROR (-20000,
1026 'Exception at ICX_POR_CTX_DESC.populateBuyerInfo('||xErrLoc||'
1027 ), '||SQLERRM );
1028 END populateBuyerInfo;
1029
1030 /* The calling procedure is responsible for opening and closing the cursor*/
1031 PROCEDURE populateCtxDescBuyerInfo(pItemSourceCv IN item_source_cv_type,
1032 pDeleteYN IN VARCHAR2 DEFAULT 'Y',
1033 pUpdateYN IN VARCHAR2 DEFAULT 'Y',
1034 pLanguage IN VARCHAR2 DEFAULT NULL,
1035 pSourceType IN VARCHAR2 DEFAULT 'ROWID',
1036 p_log_type IN VARCHAR2 DEFAULT 'LOADER') is
1037 xErrLoc INTEGER := 0; -- execution location for error trapping
1038 vRowidTab DBMS_SQL.UROWID_TABLE;
1039 vItemIdTab DBMS_SQL.NUMBER_TABLE;
1040 vLangTab DBMS_SQL.VARCHAR2_TABLE;
1041 c_handle NUMBER ;
1042 c_status PLS_INTEGER;
1043 vSqlString VARCHAR2(4000);
1044 v_sequence PLS_INTEGER := 10001;
1045 p_lang VARCHAR2(10) := NULL;
1046 BEGIN
1047
1048 xErrLoc := 0;
1049 --Debugging
1050 icx_por_ext_utl.debug('start to process populateCtxDescBuyerInfo');
1051 xErrLoc := 11;
1052
1053 LOOP
1054 vRowidTab.DELETE;
1055 vItemIdTab.DELETE;
1056 vLangTab.DELETE;
1057
1058 xErrLoc := 100;
1059
1060 IF (pSourceType = 'ROWID') THEN
1061 FETCH pItemSourceCv BULK COLLECT INTO vRowidTab,vItemIdTab,vLangTab
1062 LIMIT BATCH_SIZE;
1063 ELSE
1064 FETCH pItemSourceCv BULK COLLECT INTO vItemIdTab,vLangTab
1065 LIMIT BATCH_SIZE;
1066 END IF;
1067
1068 xErrLoc := 110;
1069 --Debugging
1070 icx_por_ext_utl.debug('populateCtxDescBuyerInfo vItemIdTab count:' ||
1071 to_char(vItemIdTab.COUNT) );
1072
1073 xErrLoc := 200;
1074
1075 IF vItemIdTab.COUNT = 0 THEN
1076 EXIT;
1077 END IF;
1078
1079 xErrLoc := 300;
1080
1081 -- delete exisiting Buyerid rows from icx_cat_items_ctx_tlp
1082 IF (pDeleteYN = 'Y') THEN
1083 FORALL i in 1..vItemIdTab.COUNT
1084 DELETE FROM icx_cat_items_ctx_tlp
1085 WHERE rt_item_id = vItemIdTab(i)
1086 AND language = vLangTab(i)
1087 AND sequence > 10000
1088 AND sequence < 15000;
1089 END IF;
1090
1091 IF (pSourceType = 'ROWID') THEN
1092
1093 FORALL i IN 1..vRowIdTab.COUNT
1094 INSERT INTO icx_cat_items_ctx_tlp
1095 (rt_item_id,language,sequence,ctx_desc,org_id)
1096 SELECT tl.rt_item_id, tl.language, v_sequence,
1097 to_char(pll.org_id), pll.org_id
1098 FROM icx_cat_items_tlp tl,
1099 (SELECT distinct org_id FROM icx_cat_item_prices
1100 WHERE rt_item_id = vItemIdTab(i)
1101 ) pll
1102 WHERE tl.rowid = vRowIdTab(i);
1103 ELSE
1104
1105 FORALL i IN 1..vItemIdTab.COUNT
1106 INSERT INTO icx_cat_items_ctx_tlp
1107 (rt_item_id,language,sequence,ctx_desc,org_id)
1108 SELECT tl.rt_item_id, tl.language, v_sequence,
1109 to_char(pll.org_id), pll.org_id
1110 FROM icx_cat_items_tlp tl,
1111 (SELECT distinct org_id FROM icx_cat_item_prices
1112 WHERE rt_item_id = vItemIdTab(i)
1113 ) pll
1114 WHERE tl.rt_item_id = vItemIdTab(i)
1115 AND tl.language = vLangTab(i);
1116
1117 END IF;
1118
1119 COMMIT;
1120
1121 END LOOP;
1122 xErrLoc := 1001;
1123 --Debugging
1124 icx_por_ext_utl.debug('populateCtxDescBuyerInfo done');
1125
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 ROLLBACK;
1129
1130 IF (DBMS_SQL.IS_OPEN(c_handle)) THEN
1131 DBMS_SQL.CLOSE_CURSOR(c_handle);
1132 END IF;
1133
1134 RAISE_APPLICATION_ERROR (-20000,
1135 'Exception at ICX_POR_CTX_DESC.populateCtxDescBuyerInfo('||xErrLoc || '), '||SQLERRM );
1136 END populateCtxDescBuyerInfo;
1137
1138
1139 END ICX_POR_CTX_DESC;