1 PACKAGE BODY ICX_POR_SCHEMA_UPLOAD AS
2 /* $Header: ICXSULDB.pls 115.26 2004/08/03 00:56:56 kaholee ship $*/
3
4 -- Default number of characters for columns created as VARCHAR2
5 DEFAULT_COLUMN_SIZE CONSTANT Number := 700;
6
7 gLastTableName VARCHAR2(30) := null;
8 gLastTableExist VARCHAR2(1) := 'N';
9 BATCH_SIZE NUMBER:= 10000;
10 -------------------------------------------------------------------------
11 -- Rebuild Index --
12 -------------------------------------------------------------------------
13 --
14 -- Copied from ICXCGCDB.pls
15 --
16
17 --
18 -- Cursor to fetch intalled languages
19 --
20 CURSOR installed_languages_csr IS
21 select language_code
22 from fnd_languages
23 where installed_flag in ('B', 'I');
24
25 /**
26 ** Procedure : populate_ctx_desc_indexes
27 ** Synopsis : Update the ctx_<lang> columns for items belong to
28 ** those categories which own rebuild_flags or their
29 ** local descriptors' rebuild_flags are set to 'Y.'
30 **
31 ** Parameter: p_request_id - number of the job to rebuild
32 **/
33
34 PROCEDURE populate_ctx_desc_indexes(p_request_id IN INTEGER := -1) IS
35
36 xErrLoc NUMBER := 0; -- execution location for error trapping
37 rebuildBase NUMBER :=0;
38 vCategoryId NUMBER;
39 vItemSourceCursor NUMBER;
40 vSqlString VARCHAR2(4000);
41 items_tl_cv ICX_POR_CTX_DESC.Item_Source_cv_Type;
42
43 CURSOR CatIdCur(p_request_id IN NUMBER) is
44 SELECT distinct catid
45 FROM (
46 SELECT
47 dl.rt_category_id catid
48 FROM icx_cat_descriptors_tl dl
49 WHERE dl.request_id = p_request_id
50 --OEX_IP_PORTING AND dl.class = 'ICX_CAT_ATTR'
51 AND dl.rebuild_flag = 'Y'
52 AND dl.rt_category_id<>0
53 UNION
54 SELECT ctl.rt_category_id catid
55 FROM icx_cat_categories_tl ctl
56 where ctl.request_id = p_request_id
57 AND ctl.rebuild_flag in ('D','B')
58 AND ctl.rt_category_id<>0
59 );
60 BEGIN
61
62 xErrLoc := 100;
63 -- check if we need to rebuild ctx str for base attribute
64 BEGIN
65 SELECT 1
66 INTO rebuildBase
67 FROM dual
68 WHERE EXISTS
69 (SELECT 1
70 FROM icx_cat_descriptors_tl
71 WHERE request_id = p_request_id
72 AND rebuild_flag = 'Y'
73 AND rt_category_id = 0)
74 --OEX_IP_PORTING AND class = 'ICX_BASE_ATTR')
75 OR EXISTS
76 (SELECT 1
77 FROM icx_cat_categories_tl
78 WHERE request_id = p_request_id
79 AND rebuild_flag = 'D'
80 AND rt_category_id = 0);
81 EXCEPTION
82 when no_data_found then
83 null;
84 END;
85
86 if (rebuildBase = 1) then
87 xErrLoc := 120;
88 ICX_POR_CTX_DESC.populateBaseAttributes('Y','Y');
89 xErrLoc := 130;
90 else
91 xErrLoc := 150;
92 -- no need to do the following for changed category names
93 -- if we've already done with the base attributes
94
95 -- get sql table for base attributes
96 OPEN items_tl_cv FOR
97 SELECT tl.rowid, tl.rt_item_id, tl.language
98 FROM icx_cat_items_tlp tl,
99 icx_cat_categories_tl ctl
100 where tl.primary_category_id = ctl.rt_category_id
101 and tl.language = ctl.language
102 and ctl.request_id = p_request_id
103 AND ctl.rebuild_flag in ('C','B')
104 AND ctl.rt_category_id<>0;
105
106 xErrLoc := 200;
107 ICX_POR_CTX_DESC.populateCtxDescBaseAtt(items_tl_cv,'Y','Y',NULL,'ROWID');
108
109 xErrLoc := 300;
110 CLOSE items_tl_cv;
111 end if; --if (rebuildBase = 1)
112
113 xErrLoc := 400;
114 -- rebuild changed searchable category attributes
115 -- category attribute loop
116 -- need to rebuild index because of deletion of searchable category attributes
117 xErrLoc := 500;
118 FOR catRec IN CatIdCur(p_request_id)
119 LOOP
120 vCategoryId := catRec.catid;
121 vItemSourceCursor := DBMS_SQL.OPEN_CURSOR;
122 xErrLoc := 520;
123
124 --Changes for populate_ctx_desc_indexes to not throw invalid ROWID exception
125 --vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_ITEMS_TLP '||
126 -- ' WHERE primary_category_id = :catid';
127 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP '||
128 ' WHERE rt_category_id = :catid';
129 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
130 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor, ':catid', vCategoryId);
131 /*
132 IF (vCatTableExists = 1) THEN
133 --STO_CHECK: to get list of items affected by a category/descriptor change
134 -- cant we just use the category items table...do we need
135 -- icx_por_c<blah> table ??
136 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_C'||vCategoryId||'_TL';
137 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
138 ELSE
139 vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_ITEMS_TL '||
140 ' WHERE primary_category_id = :catid)';
141 DBMS_SQL.PARSE(vItemSourceCursor, vSqlString, DBMS_SQL.NATIVE);
142 DBMS_SQL.BIND_VARIABLE(vItemSourceCursor, ':catid', vCategoryId);
143 END IF;
144 */
145
146 xErrLoc := 540;
147 ICX_POR_CTX_DESC.populateCtxDescCatAtt(vCategoryId, vItemSourceCursor,'Y',
148 'Y', NULL, 'ROWID');
149 xErrLoc:=560;
150 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
151 xErrLoc:=580;
152 END LOOP;
153
154 xErrLoc := 600;
155
156 -- reset rebuild_flag
157 UPDATE icx_cat_categories_tl
158 SET rebuild_flag = NULL
159 WHERE request_id = p_request_id;
160
161 xErrLoc := 700;
162 UPDATE icx_cat_descriptors_tl
163 SET rebuild_flag = NULL
164 WHERE request_id = p_request_id;
165 COMMIT;
166
167 -- rebuild the intermedia or context indexes
168 xErrLoc := 800;
169 ICX_POR_INTERMEDIA_INDEX.rebuild_index;
170 xErrLoc := 900;
171 COMMIT;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 ROLLBACK;
176
177 IF(DBMS_SQL.IS_OPEN(vItemSourceCursor)) THEN
178 DBMS_SQL.CLOSE_CURSOR(vItemSourceCursor);
179 END IF;
180
181 RAISE_APPLICATION_ERROR
182 (-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.populate_ctx_desc_indexes('||
183 xErrLoc||'), '|| SQLERRM);
184
185 END populate_ctx_desc_indexes;
186
187 /**
188 ** Procedure : populate_ctx_desc_indexes
189 ** Synopsis : Overloaded version. Contains 2 extra out parameters
190 ** which are used by Concurrent program.
191 ** No other functional change
192 ** Update the ctx_<lang> columns for items belong to
193 ** those categories which own rebuild_flags or their
194 ** local descriptors' rebuild_flags are set to 'Y.'
195 **
196 ** Parameter: p_request_id - number of the job to rebuild
197 **/
198
199 PROCEDURE populate_ctx_desc_indexes(errbuf OUT NOCOPY VARCHAR2,
200 retcode OUT NOCOPY VARCHAR2,
201 p_request_id IN INTEGER := -1) IS
202 BEGIN
203 retcode := 0;
204 errbuf := '';
205
206 populate_ctx_desc_indexes(p_request_id);
207 EXCEPTION
208 WHEN OTHERS THEN
209 retcode := 2;
210 errbuf := SQLERRM;
211 raise;
212 END populate_ctx_desc_indexes;
213
214
215 -------------------------------------------------------------------------
216 -- ADD ACTION --
217 -------------------------------------------------------------------------
218
219 /**
220 ** Proc : add_child_category
221 ** Desc : Add a category as a child of another category.
222 ** If this child category is already a child of other
223 ** category, this relationship will be remained.
224 ** Also this parent category should be navigation type.
225 **/
226 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
227 PROCEDURE add_child_category (p_parent_id IN NUMBER,
228 p_child_id IN NUMBER,
229 p_user_id IN NUMBER,
230 p_login_id IN NUMBER)
231 IS
232
233 xErrLoc INTEGER := 0;
234
235 xType NUMBER;
236 xTableName VARCHAR2(30);
237 xViewName VARCHAR2(30);
238 xStatement VARCHAR2(500);
239
240 BEGIN
241
242 xErrLoc := 100;
243
244 select type
245 into xType
246 from icx_cat_categories_tl
247 where rt_category_id = p_parent_id
248 and rownum = 1;
249
250 xErrLoc := 200;
251
252 -- Only navigate category can be parent
253 -- pcreddy # bug # 2959731 : ROOT will be a parent for the newly created
254 -- NAVIGATION_TYPE categories
255 if (p_parent_id <> 0 AND xType <> NAVIGATION_TYPE) then
256 return;
257 end if;
258
259 xErrLoc := 300;
260
261 --Changes for userId, loginId
262 INSERT INTO icx_cat_browse_trees
263 (parent_category_id, child_category_id,
264 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
265 CREATED_BY, CREATION_DATE)
266 SELECT p_parent_id, p_child_id,
267 p_login_id, p_user_id, sysdate, p_user_id, sysdate
268 FROM DUAL
269 WHERE NOT EXISTS (SELECT NULL
270 FROM icx_cat_browse_trees
271 WHERE parent_category_id = p_parent_id
272 AND child_category_id = p_child_id);
273 xErrLoc := 400;
274 -- pcreddy # bug 2959731 :
275 -- when the parent is ROOT no need of doing any other operations
276 IF (p_parent_id <> 0) THEN
277 -- 1775192
278 -- we also need to remove the child who is originally
279 -- a top level navigational category
280 DELETE FROM icx_cat_browse_trees
281 WHERE parent_category_id = 0
282 AND child_category_id = p_child_id;
283
284 xErrLoc := 500;
285 -- Add entry for parent category into toc table
286 -- with its parentid=0: For Browse category to work
287 -- Bug#1681042: Only Exception: If the parent is already a child dont add
288 --Changes for userId, loginId
289 INSERT INTO icx_cat_browse_trees
290 (PARENT_CATEGORY_ID, CHILD_CATEGORY_ID,
291 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
292 CREATED_BY, CREATION_DATE)
293 SELECT 0, p_parent_id, p_login_id, p_user_id, sysdate, p_user_id, sysdate
294 FROM DUAL
295 WHERE NOT EXISTS (SELECT NULL
296 FROM icx_cat_browse_trees
297 WHERE child_category_id = p_parent_id);
298 END IF;
299
300 xErrLoc := 600;
301
302 COMMIT;
303 EXCEPTION
304 WHEN OTHERS THEN
305 ROLLBACK;
306
307 RAISE_APPLICATION_ERROR(-20000,
308 'Exception at ICX_POR_SCHEMA_UPLOAD.add_child_category('
309 || xErrLoc || '): ' || SQLERRM);
310
311 END add_child_category;
312
313 /**
314 ** Proc : delete_child_category
315 ** Desc : Delete a category as a child of another category.
316 ** Also this parent category should be navigation type.
317 **/
318
319 PROCEDURE delete_child_category (p_parent_id IN NUMBER,
320 p_child_id IN NUMBER)
321 IS
322
323 xErrLoc INTEGER := 0;
324
325 xType NUMBER;
326 xTableName VARCHAR2(30);
327 xViewName VARCHAR2(30);
328 xStatement VARCHAR2(500);
329
330 BEGIN
331
332 xErrLoc := 100;
333
334 select type
335 into xType
336 from icx_cat_categories_tl
337 where rt_category_id = p_parent_id
338 and rownum = 1;
339
340 xErrLoc := 200;
341
342 -- Only navigate category can be parent
343 if (xType <> NAVIGATION_TYPE) then
344 return;
345 end if;
346
347 xErrLoc := 300;
348
349 DELETE FROM ICX_CAT_BROWSE_TREES
350 WHERE PARENT_CATEGORY_ID = p_parent_id
351 AND CHILD_CATEGORY_ID = p_child_id;
352
353 xErrLoc := 500;
354
355 COMMIT;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 ROLLBACK;
360
361 RAISE_APPLICATION_ERROR(-20000,
362 'Exception at ICX_POR_SCHEMA_UPLOAD.delete_child_category('
363 || xErrLoc || '): ' || SQLERRM);
364
365 END delete_child_category;
366
367 /**
368 ** Proc : create_category
369 ** Desc : Create a new category with the specified name + key.
370 ** If parent is specified, a new link will be created.
371 ** This method assumes all parameters are validated, and
372 ** it will create rows for each installed language.
373 **/
374 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
375 PROCEDURE create_category (p_category_id OUT NOCOPY NUMBER,
376 p_key IN VARCHAR2,
377 p_name IN VARCHAR2,
378 p_description IN VARCHAR2,
379 p_type IN NUMBER,
380 p_language IN VARCHAR2,
381 p_parent_id IN NUMBER DEFAULT -1,
382 p_request_id IN NUMBER DEFAULT -1,
383 p_user_id IN NUMBER,
384 p_login_id IN NUMBER)
385 IS
386
387 xErrLoc INTEGER := 0;
388
389 xTableName VARCHAR2(30);
390 xViewName VARCHAR2(30);
391 xStatement VARCHAR2(500);
392
393 BEGIN
394 xErrLoc := 100;
395
396 select icx_por_categoryid.nextval
397 into p_category_id
398 from DUAL;
399
400 xErrLoc := 200;
401
402 FOR language_row IN installed_languages_csr LOOP
403
404 xErrLoc := 300;
405
406 --Changes for userId, loginId
407 INSERT INTO ICX_CAT_CATEGORIES_TL
408 (RT_CATEGORY_ID, LANGUAGE, SOURCE_LANG, CATEGORY_NAME,
409 DESCRIPTION, TYPE, KEY, UPPER_KEY, TITLE, CREATED_BY,
410 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
411 LAST_UPDATE_LOGIN, UPPER_CATEGORY_NAME, REQUEST_ID, REBUILD_FLAG,SECTION_MAP)
412 VALUES
413 (p_category_id, language_row.language_code, p_language, p_name,
414 p_description, p_type, p_key, upper(p_key), NULL, p_user_id,
415 SYSDATE, p_user_id, SYSDATE, p_login_id, upper(p_name), p_request_id, 'N',
416 lpad('0','300','0'));
417
418 END LOOP;
419
420 xErrLoc := 350;
421 if (p_parent_id <> -1) then
422
423 xErrLoc := 400;
424 --Changes for userId, loginId
425 add_child_category(p_parent_id, p_category_id, p_user_id, p_login_id);
426
427 elsif (p_type = NAVIGATION_TYPE) then
428
429 -- Bug#2959731: pcreddy- Add the navigational category to root by default.
430 xErrLoc := 450;
431 add_child_category(0, p_category_id, p_user_id, p_login_id);
432
433 end if;
434
435 xErrLoc := 500;
436
437 COMMIT;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 ROLLBACK;
442
443 RAISE_APPLICATION_ERROR(-20000,
444 'Exception at ICX_POR_SCHEMA_UPLOAD.create_category('
445 || xErrLoc || '): ' || SQLERRM);
446
447 END create_category;
448
449 /**
450 ** Proc : create_descriptor
451 ** Desc : Create a new local descriptor within a category.
452 ** A dynamic category table will be created if it doesn't
453 ** exist, and a new column is added to this table.
454 ** This method assumes everything is validated before
455 ** calling. And it will create rows for each installed language.
456 **/
457 /* OEX_IP_PORTING: added 3 p-arameters for section tag, storedintable, storedincolumn */
458 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
459 PROCEDURE create_descriptor (p_descriptor_id OUT NOCOPY NUMBER,
460 p_key IN VARCHAR2,
461 p_name IN VARCHAR2 DEFAULT NULL,
462 p_description IN VARCHAR2 DEFAULT NULL,
463 p_type IN VARCHAR2 DEFAULT
464 TEXT_TYPE,
465 p_sequence IN NUMBER,
466 p_search_results_visible IN VARCHAR2 DEFAULT NO,
467 p_item_detail_visible IN VARCHAR2 DEFAULT NO,
468 p_searchable IN VARCHAR2 DEFAULT NO,
469 p_required IN VARCHAR2 DEFAULT NO,
470 p_refinable IN VARCHAR2 DEFAULT NO,
471 p_multivalue IN VARCHAR2 DEFAULT NO,
472 p_default_value IN VARCHAR2 DEFAULT NULL,
473 p_language IN VARCHAR2,
474 p_category_id IN NUMBER,
475 p_request_id IN NUMBER DEFAULT -1,
476 p_section_tag OUT NOCOPY NUMBER,
477 p_stored_in_table OUT NOCOPY VARCHAR2,
478 p_stored_in_column OUT NOCOPY VARCHAR2,
479 p_user_id IN NUMBER,
480 p_login_id IN NUMBER )
481 IS
482
483 xErrLoc INTEGER := 0;
484
485 xColumnType VARCHAR2(30);
486 xTableName VARCHAR2(30);
487 xStatement VARCHAR2(500);
488 xDummyDescId NUMBER;
489
490 xRebuildFlag VARCHAR2(1) := 'N';
491
492 l_type VARCHAR2(1);
493 l_sequence NUMBER;
494 l_search_results_visible VARCHAR2(1);
495 l_item_detail_visible VARCHAR2(1);
496 l_searchable VARCHAR2(1);
497 l_required VARCHAR2(1);
498 l_refinable VARCHAR2(1);
499 l_multivalue VARCHAR2(1);
500
501 BEGIN
502
503 IF (p_type IS NULL) THEN
504 l_type := TEXT_TYPE; --Default is text type: Bug#2611529
505 ElSE
506 l_type := p_type;
507 END IF;
508
509 IF (p_sequence IS NULL) THEN
510 select floor(max(sequence))+1 into l_sequence
511 from icx_cat_descriptors_tl
512 where rt_category_id = p_category_id
513 and language = p_language;
514 --OEX_IP_PORTING and class in ('ICX_BASE_ATTR','IPD_BASE_ATTR', 'POM_PRICE_ATTR', 'ICX_CAT_ATTR');
515 ElSE
516 l_sequence :=p_sequence;
517 END IF;
518
519 IF (p_search_results_visible IS NULL) THEN
520 l_search_results_visible := NO;
521 ELSE
522 l_search_results_visible := p_search_results_visible;
523 END IF;
524
525 IF (p_item_detail_visible IS NULL) THEN
526 l_item_detail_visible := YES;
527 ELSE
528 l_item_detail_visible := p_item_detail_visible;
529 END IF;
530
531 IF (p_searchable IS NULL) THEN
532 l_searchable := NO;
533 ELSE
534 l_searchable := p_searchable;
535 END IF;
536
537 IF (p_required IS NULL) THEN
538 l_required := NO;
539 ELSE
540 l_required := p_required;
541 END IF;
542
543 IF (p_refinable IS NULL) THEN
544 l_refinable := NO;
545 ElSE
546 l_refinable := p_refinable;
547 END IF;
548
549 IF (p_multivalue IS NULL) THEN
550 l_multivalue := NO;
551 ELSE
552 l_multivalue := p_multivalue;
553 END IF;
554
555 IF (l_multivalue IS NULL or l_multivalue = NO) THEN
556 xErrLoc := 100;
557
558 -- Bug 1404129, zxzhang
559 -- Disable editing root category
560 -- jinwang : enable to edit root category attribute
561 if (p_category_id < 0) then
562 return;
563 end if;
564
565 xErrLoc := 200;
566
567 -- Get the new descriptor id
568
569 select icx_por_descriptorid.nextval
570 into p_descriptor_id
571 from dual;
572
573 xErrLoc := 300;
574
575 if (l_type = TEXT_TYPE or l_type = TRANSLATABLE_TEXT_TYPE) then
576 xColumnType := 'VARCHAR2(' || to_char(DEFAULT_COLUMN_SIZE) || ')';
577 elsif (l_type = NUMERIC_TYPE or l_type = INTEGER_TYPE) then
578 xColumnType := 'NUMBER';
579 elsif (l_type = DATE_TYPE) then
580 xColumnType := 'DATE';
581 else
582 xColumnType := 'VARCHAR2(' || to_char(DEFAULT_COLUMN_SIZE) || ')';
583 end if;
584
585 xErrLoc := 400;
586
587
588 xErrLoc := 600;
589
590 -- Reset category view
591 -- Commented out, as is not needed in Exchange, only in IP
592 -- reset_category_view(p_category_id);
593
594 xErrLoc := 700;
595
596 -- Insert into icx_cat_descriptors_tl table
597
598 xErrLoc := 750;
599
600 --Changes for userId, loginId
601 create_descriptor_metadata (p_descriptor_id, p_key, p_name,
602 p_description, l_type, l_sequence,
603 l_search_results_visible, l_item_detail_visible,
604 l_searchable, l_required, l_refinable, l_multivalue,
605 p_default_value, p_language, p_category_id,
606 p_request_id, xRebuildFlag, xDummyDescId,
607 p_user_id, p_login_id);
608
609 xErrLoc := 800;
610
611 -- update the icx_cat_schema_versions
612 inc_schema_change_version(p_category_id);
613
614 xErrLoc := 900;
615
616 -- OEX_IP_PORTING: Added l_type as parameter
617 assign_section_tag(p_category_id, p_descriptor_id, p_section_tag, p_stored_in_table, p_stored_in_column, l_type );
618
619 END IF;
620
621 COMMIT;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 ROLLBACK;
626
627 RAISE_APPLICATION_ERROR(-20000,
628 'Exception at ICX_POR_SCHEMA_UPLOAD.create_descriptor('
629 || xErrLoc || '): ' || SQLERRM);
630
631 END create_descriptor;
632
633
634 /**
635 ** Proc : create_descriptor_metadata
636 ** Desc : Insert a new local descriptor into icx_cat_descriptors_tl.
637 ** This method simply pulls out the metadata section of a descriptor
638 ** that gets inserted into ICX_DESCRIPTORS_TL. This is done to
639 ** separate the insertion of data, from the creation of a dynamic
640 ** table. And is called directly in online category creation.
641 ** This method assumes everything is validated before
642 ** calling. And it will create rows for each installed language.
643 **/
644 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
645 PROCEDURE create_descriptor_metadata (p_descriptor_id IN NUMBER,
646 p_key IN VARCHAR2,
647 p_name IN VARCHAR2,
648 p_description IN VARCHAR2,
649 p_type IN VARCHAR2,
650 p_sequence IN NUMBER,
651 p_search_results_visible IN VARCHAR2,
652 p_item_detail_visible IN VARCHAR2,
653 p_searchable IN VARCHAR2,
654 p_required IN VARCHAR2,
655 p_refinable IN VARCHAR2,
656 p_multivalue IN VARCHAR2,
657 p_default_value IN VARCHAR2,
658 p_language IN VARCHAR2,
659 p_category_id IN NUMBER,
660 p_request_id IN NUMBER DEFAULT -1,
661 p_rebuild_flag IN VARCHAR2,
662 p_descriptor_id_out OUT NOCOPY NUMBER,
663 p_user_id IN NUMBER,
664 p_login_id IN NUMBER)
665 IS
666
667 xErrLoc INTEGER := 0;
668 xClass VARCHAR2(20) := NULL;
669
670 BEGIN
671
672 p_descriptor_id_out := p_descriptor_id;
673
674 -- if the p_descriptor_id = -1, then it is being created by adding
675 -- a new descriptor to an existing category, via online editing
676 -- so we must generate the id here.
677 -- in the other case it has already been generated from the PL/SQL
678 -- create_descriptor that calls it, unlike the edit case
679 if (p_descriptor_id = -1) then
680 select icx_por_descriptorid.nextval
681 into p_descriptor_id_out
682 from dual;
683 end if;
684
685 -- set the class value
686 IF (p_category_id = 0) THEN
687 xClass := 'ICX_BASE_ATTR';
688 ELSE
689 xClass := 'ICX_CAT_ATTR';
690 END IF;
691
692 xErrLoc := 100;
693
694 --Changes for userId, loginId
695 INSERT INTO icx_cat_descriptors_tl
696 (RT_DESCRIPTOR_ID, RT_CATEGORY_ID, LANGUAGE, SOURCE_LANG,
697 DESCRIPTOR_NAME, DESCRIPTION, TYPE, KEY, TITLE, SEQUENCE,
698 DEFAULTVALUE, MULTI_VALUE_TYPE, MULTI_VALUE_KEY,
699 REQUIRED, REFINABLE, SEARCHABLE, SEARCH_RESULTS_VISIBLE,
700 ITEM_DETAIL_VISIBLE , CREATED_BY, CREATION_DATE,
701 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
702 request_id, REBUILD_FLAG, MULTIVALUE,CUSTOMIZATION_LEVEL, CLASS)
703 SELECT p_descriptor_id_out, p_category_id,
704 fnd_languages.language_code, p_language,
705 p_name,
706 decode(p_description,'#DEL',null,p_description),
707 to_number(p_type), p_key, NULL, p_sequence,
708 p_default_value, NULL, NULL,
709 p_required, p_refinable, to_number(p_searchable), p_search_results_visible,
710 p_item_detail_visible, p_user_id, sysdate,
711 p_user_id, sysdate, p_login_id, p_request_id, p_rebuild_flag, p_multivalue,
712 DECODE(p_type, URL_TYPE, '111011', DATE_TYPE, '111011', '111111'),
713 xClass
714 FROM fnd_languages
715 WHERE installed_flag in ('B', 'I');
716
717 xErrLoc := 200;
718
719 COMMIT;
720 EXCEPTION
721 WHEN OTHERS THEN
722 ROLLBACK;
723
724 RAISE_APPLICATION_ERROR(-20000,
725 'Exception at ICX_POR_SCHEMA_UPLOAD.create_descriptor_metadata('
726 || xErrLoc || '): ' || SQLERRM);
727
728 END create_descriptor_metadata;
729
730
731 -------------------------------------------------------------------------
732 -- UPDATE ACTION --
733 -------------------------------------------------------------------------
734
735 /**
736 ** Proc : update_category
737 ** Desc : Update an existing category for a sepcified language.
738 ** If parent is specified, a new link will be created.
739 **/
740 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
741 PROCEDURE update_category (p_category_id IN NUMBER,
742 p_language IN VARCHAR2,
743 p_name IN VARCHAR2 DEFAULT NULL,
744 p_description IN VARCHAR2 DEFAULT NULL,
745 p_type IN NUMBER DEFAULT -1,
746 p_parent_id IN NUMBER DEFAULT -1,
747 p_request_id IN NUMBER DEFAULT -1,
748 p_user_id IN NUMBER,
749 p_login_id IN NUMBER)
750 IS
751
752 xErrLoc INTEGER := 0;
753 xRebuildFlag VARCHAR2(1) := 'N';
754
755 xContinue BOOLEAN := TRUE;
756 xCommitSize INTEGER := 2500;
757 xLangArray DBMS_SQL.VARCHAR2_TABLE;
758 CURSOR translateLangCsr IS
759 SELECT language
760 FROM icx_cat_categories_tl
761 WHERE rt_category_id = p_category_id
762 and type = p_type
763 and source_lang = p_language
764 and source_lang <> language
765 UNION
766 SELECT p_language FROM DUAL;
767
768 BEGIN
769
770 xErrLoc := 100;
771
772 -- Bug 1404129, zxzhang
773 -- Disable editing root category
774 if (p_category_id <= 0) then
775 return;
776 end if;
777
778 xErrLoc := 110;
779
780 OPEN translateLangCsr;
781 FETCH translateLangCsr BULK COLLECT into xLangArray;
782 CLOSE translateLangCsr;
783
784 xErrLoc := 120;
785 FOR i in 1..xLangArray.COUNT LOOP
786 xErrLoc := 130;
787
788 -- rebuild flag
789 -- 'C': catagory name changed
790 -- 'D': searchable descriptor deleted
791 -- 'B': both 'C' and 'D'
792 --Changes for userId, loginId
793 UPDATE ICX_CAT_CATEGORIES_TL
794 SET CATEGORY_NAME = NVL(p_name, CATEGORY_NAME),
795 UPPER_CATEGORY_NAME = NVL(upper(p_name), UPPER_CATEGORY_NAME),
796 DESCRIPTION = decode(p_description,'#DEL',null, null, DESCRIPTION,p_description),
797 SOURCE_LANG = p_language,
798 LAST_UPDATED_BY = p_user_id,
799 LAST_UPDATE_DATE = sysdate,
800 LAST_UPDATE_LOGIN = p_login_id,
801 REQUEST_ID = p_request_id,
802 REBUILD_FLAG = decode(p_name,CATEGORY_NAME,
803 --p_name=CATEGORY_NAME, no change
804 rebuild_flag,
805 --p_name is null which means category name is not changed
806 null,
807 rebuild_flag,
808 --p_name<>CATEGORY_NAME, category name changed
809 decode(rebuild_flag,'D','B','B','B','C'))
810 WHERE RT_CATEGORY_ID = p_category_id
811 AND LANGUAGE = xLangArray(i);
812
813 xErrLoc := 140;
814
815 -- update icx_cat_items_tlp only if it's a genus category
816 IF (p_type = 2) THEN
817 -- set the commit size
818 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
819 xContinue := TRUE;
820 WHILE xContinue LOOP
821 xErrLoc := 150;
822 -- Add update for primary_category_name in icx_cat_items_tlp;
823 UPDATE ICX_CAT_ITEMS_TLP
824 SET primary_category_name = p_name
825 WHERE primary_category_id = p_category_id
826 AND language = xLangArray(i)
827 AND primary_category_name <> p_name
828 AND rownum <= xCommitSize ;
829
830 xErrLoc := 160;
831 IF ( SQL%ROWCOUNT < xCommitSize ) THEN
832 xContinue := FALSE;
833 END IF;
834
835 xErrLoc := 170;
836 COMMIT;
837 END LOOP;
838 END IF;
839 END LOOP;
840 xErrLoc := 180;
841
842 -- commented out since type can not be changed.
843 /*if (p_type <> -1) then
844 UPDATE ICX_CAT_CATEGORIES_TL
845 SET TYPE = decode(p_type, -1, TYPE, p_type),
846 LAST_UPDATED_BY = 0,
847 LAST_UPDATE_DATE = sysdate,
848 LAST_UPDATE_LOGIN = 0,
849 REQUEST_ID = p_request_id
850 WHERE RT_CATEGORY_ID = p_category_id;
851 end if;
852 */
853 xErrLoc := 200;
854
855 if (p_parent_id <> -1) then
856
857 xErrLoc := 300;
858 --Changes for userId, loginId
859 add_child_category(p_parent_id, p_category_id, p_user_id, p_login_id);
860
861 end if;
862
863 xErrLoc := 400;
864
865 COMMIT;
866 EXCEPTION
867 WHEN OTHERS THEN
868 ROLLBACK;
869
870 RAISE_APPLICATION_ERROR(-20000,
871 'Exception at ICX_POR_SCHEMA_UPLOAD.update_category('
872 || xErrLoc || '): ' || SQLERRM);
873
874 END update_category;
875
876 /**
877 ** Proc : update_descriptor
878 ** Desc : Update a existing local descriptor for a specified language
879 ** within a category.
880 **/
881 /* OEX_IP_PORTING: added 3 p-arameters for section tag, storedintable, storedincolumn */
882 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
883 PROCEDURE update_descriptor (p_descriptor_id IN NUMBER,
884 p_language IN VARCHAR2,
885 p_name IN VARCHAR2 DEFAULT NULL,
886 p_description IN VARCHAR2 DEFAULT NULL,
887 p_default_value IN VARCHAR2 DEFAULT NULL,
888 p_sequence IN VARCHAR2 DEFAULT NULL,
889 p_search_results_visible IN VARCHAR2 DEFAULT NULL,
890 p_item_detail_visible IN VARCHAR2 DEFAULT NULL,
891 p_searchable IN VARCHAR2 DEFAULT NULL,
892 p_required IN VARCHAR2 DEFAULT NULL,
893 p_refinable IN VARCHAR2 DEFAULT NULL,
894 p_multivalue IN VARCHAR2 DEFAULT NULL,
895 p_request_id IN NUMBER DEFAULT -1,
896 p_section_tag OUT NOCOPY NUMBER,
897 p_stored_in_table OUT NOCOPY VARCHAR2 ,
898 p_stored_in_column OUT NOCOPY VARCHAR2 ,
899 p_user_id IN NUMBER,
900 p_login_id IN NUMBER)
901 IS
902
903 xErrLoc INTEGER := 0;
904 xCategoryID NUMBER;
905 -- OEX_IP_PORTING
906 xType VARCHAR2(1) := NULL;
907
908 xRebuildFlag VARCHAR2(1) := 'N';
909 xSearchable VARCHAR2(1) := NULL;
910
911 BEGIN
912
913 xErrLoc := 100;
914
915 -- added type in select list..need this for assign_section_tag
916 -- you need the type to determine the stored_in_column and store it
917 -- in icx_cat_descriptors_tl, which is done in the
918 -- assign_section_tag procedure
919 select rt_category_id, to_char(searchable), to_char(type)
920 into xCategoryID, xSearchable, xType
921 from icx_cat_descriptors_tl
922 where rt_descriptor_id = p_descriptor_id
923 --OEX_IP_PORTING AND class IN ('ICX_BASE_ATTR','IPD_BASE_ATTR', 'POM_PRICE_ATTR', 'ICX_CAT_ATTR')
924 and rownum = 1;
925
926 xErrLoc := 200;
927
928 -- Bug 1404129, zxzhang
929 -- Disable editing root category
930 -- jinwang
931 -- enable to edit root category descriptor
932 if (xCategoryID < 0) then
933 return;
934 end if;
935
936 xErrLoc := 240;
937
938 if (p_searchable IS NOT NULL AND p_searchable <> xSearchable) then
939 xRebuildFlag := 'Y';
940 end if;
941
942 --Changes for userId, loginId
943 UPDATE icx_cat_descriptors_tl
944 SET DESCRIPTOR_NAME = NVL(p_name, DESCRIPTOR_NAME),
945 DESCRIPTION = decode(p_description,'#DEL',null, null, DESCRIPTION,p_description),
946 SOURCE_LANG = p_language,
947 CREATION_DATE = sysdate,
948 LAST_UPDATED_BY = p_user_id,
949 LAST_UPDATE_DATE = sysdate,
950 LAST_UPDATE_LOGIN = p_login_id,
951 REQUEST_ID = p_request_id
952 WHERE RT_DESCRIPTOR_ID = p_descriptor_id
953 AND LANGUAGE = p_language;
954
955 -- update the icx_cat_schema_versions
956 inc_schema_change_version(xCategoryID);
957
958 xErrLoc := 150;
959
960 -- Update the following for all languages
961
962 if (p_default_value is not null or
963 p_search_results_visible is not null or
964 p_item_detail_visible is not null or
965 p_searchable is not null or
966 p_required is not null or
967 p_multivalue is not null or
968 p_refinable is not null) then
969 UPDATE icx_cat_descriptors_tl
970 SET DEFAULTVALUE = NVL(p_default_value, DEFAULTVALUE),
971 SEQUENCE = decode(p_sequence,null,sequence,'#DEL',null,to_number(p_sequence)),
972 SEARCH_RESULTS_VISIBLE = TO_NUMBER(NVL(p_search_results_visible,
973 SEARCH_RESULTS_VISIBLE)),
974 ITEM_DETAIL_VISIBLE = TO_NUMBER(NVL(p_item_detail_visible,
975 ITEM_DETAIL_VISIBLE)),
976 SEARCHABLE = TO_NUMBER(NVL(p_searchable, SEARCHABLE)),
977 REQUIRED = TO_NUMBER(NVL(p_required, REQUIRED)),
978 MULTIVALUE = TO_NUMBER(NVL(p_multivalue, MULTIVALUE)),
979 REFINABLE = TO_NUMBER(NVL(p_refinable, REFINABLE)),
980 CREATION_DATE = sysdate,
981 LAST_UPDATED_BY = p_user_id,
982 LAST_UPDATE_DATE = sysdate,
983 LAST_UPDATE_LOGIN = p_login_id,
984 REQUEST_ID = p_request_id,
985 REBUILD_FLAG = xRebuildFlag
986 WHERE RT_DESCRIPTOR_ID = p_descriptor_id;
987
988 xErrLoc := 200;
989
990 end if;
991
992 xErrLoc := 400;
993
994 COMMIT;
995 EXCEPTION
996 WHEN OTHERS THEN
997 ROLLBACK;
998
999 RAISE_APPLICATION_ERROR(-20000,
1000 'Exception at ICX_POR_SCHEMA_UPLOAD.update_descriptor('
1001 || xErrLoc || '): ' || SQLERRM);
1002
1003 END update_descriptor;
1004
1005
1006 -------------------------------------------------------------------------
1007 -- DELETE ACTION --
1008 -------------------------------------------------------------------------
1009
1010 /**
1011 ** Proc : delete_category
1012 ** Desc : Delete category from icx_cat_categories_tl;
1013 ** Delete local descriptors from icx_cat_descriptors_tl;
1014 ** Dlete items from icx_por_items, icx_por_items_tl and
1015 ** icx_cat_category_items;
1016 ** Delete links from icx_cat_browse_trees;
1017 **/
1018 PROCEDURE delete_category (p_category_id IN NUMBER)
1019 IS
1020
1021 xErrLoc INTEGER := 0;
1022
1023 l_count NUMBER := 0;
1024 l_type NUMBER := 0;
1025
1026 CURSOR toc_children(p_category_id number) IS
1027 select child_category_id from icx_cat_browse_trees
1028 where parent_category_id = p_category_id;
1029
1030
1031 BEGIN
1032
1033 xErrLoc := 100;
1034
1035 -- Bug 1404129, zxzhang
1036 -- Disable editing root category
1037 if (p_category_id <= 0) then
1038 return;
1039 end if;
1040
1041 delete from icx_cat_categories_tl
1042 where rt_category_id = p_category_id;
1043
1044 xErrLoc := 200;
1045
1046 delete from icx_cat_descriptors_tl
1047 where rt_category_id = p_category_id;
1048
1049 xErrLoc := 300;
1050
1051 delete from icx_cat_browse_trees
1052 where child_category_id = p_category_id;
1053
1054 xErrLoc := 700;
1055 -- OEX_IP_PORTING: Check
1056 -- drop_category_table(p_category_id);
1057
1058 xErrLoc := 800;
1059 FOR c_child IN toc_children(p_category_id) LOOP
1060
1061 delete from icx_cat_browse_trees
1062 where child_category_id = c_child.child_category_id
1063 and parent_category_id = p_category_id;
1064 xErrLoc := 810;
1065
1066 select count(*) into l_count
1067 from icx_cat_browse_trees
1068 where child_category_id = c_child.child_category_id;
1069 xErrLoc := 820;
1070
1071 if ( l_count = 0 ) then
1072 SELECT type INTO l_type
1073 FROM icx_cat_categories_tl
1074 WHERE rt_category_id = c_child.child_category_id
1075 AND language = USERENV('LANG');
1076 xErrLoc := 830;
1077
1078 -- only if the child is a navigational category
1079 if ( l_type = 1 ) then
1080 INSERT INTO icx_cat_browse_trees
1081 (parent_category_id, child_category_id,
1082 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
1083 CREATED_BY, CREATION_DATE)
1084 VALUES
1085 (0, c_child.child_category_id, 0, 0, sysdate, 0, sysdate);
1086 end if;
1087 xErrLoc := 840;
1088 end if;
1089
1090 END LOOP;
1091
1092 COMMIT;
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 ROLLBACK;
1096
1097 RAISE_APPLICATION_ERROR(-20000,
1098 'Exception at ICX_POR_SCHEMA_UPLOAD.delete_category('
1099 || xErrLoc || '): ' || SQLERRM);
1100
1101 END delete_category;
1102
1103
1104 /**
1105 ** Proc : delete_category_tree
1106 ** Desc : Navigate the subtree, delete the whole subtree and items
1107 ** associated.
1108 **/
1109
1110 PROCEDURE delete_category_tree (p_category_id IN NUMBER)
1111 IS
1112
1113 xErrLoc INTEGER := 0;
1114
1115 --
1116 -- Cursot to fetch child categories
1117 --
1118 CURSOR child_categories_csr (p_category_id NUMBER) IS
1119 select distinct cat.rt_category_id,
1120 cat.type
1121 from icx_cat_categories_tl cat,
1122 icx_cat_browse_trees toc
1123 where toc.parent_category_id = p_category_id
1124 and toc.child_category_id = cat.rt_category_id;
1125
1126 BEGIN
1127
1128 xErrLoc := 100;
1129
1130 delete_category(p_category_id);
1131
1132 xErrLoc := 200;
1133
1134 FOR categories IN child_categories_csr(p_category_id) LOOP
1135 xErrLoc := 300;
1136
1137 -- Only navigate category has subtree
1138 if (categories.type = NAVIGATION_TYPE) then
1139 xErrLoc := 400;
1140 delete_category_tree(categories.rt_category_id);
1141 xErrLoc := 450;
1142 elsif (categories.type = GENUS_TYPE) then
1143 xErrLoc := 500;
1144 delete_category(categories.rt_category_id);
1145 xErrLoc := 550;
1146 end if;
1147
1148 END LOOP;
1149
1150 COMMIT;
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 ROLLBACK;
1154
1155 RAISE_APPLICATION_ERROR(-20000,
1156 'Exception at ICX_POR_SCHEMA_UPLOAD.delete_category_tree('
1157 || xErrLoc || '): ' || SQLERRM);
1158
1159 END delete_category_tree;
1160
1161
1162 /**
1163 ** Proc : delete_descriptor
1164 ** Desc : Delete the local descriptor within a category.
1165 ** If no local descriptors for this category, the
1166 ** dynamic table and view will be dropped.
1167 **/
1168 --Bug#3027134 Added who columns in icx_cat_deleted_attributes
1169 --as part of ECM OA Rewrite
1170 --So add two parameters for user_id and login_id to delete_descriptors
1171 --to populate the who columns in icx_cat_deleted_attributes.
1172
1173 PROCEDURE delete_descriptor (p_descriptor_id IN NUMBER,
1174 p_request_id IN NUMBER DEFAULT -1,
1175 p_user_id IN NUMBER,
1176 p_login_id IN NUMBER)
1177 IS
1178
1179 xErrLoc INTEGER := 0;
1180
1181 xCategoryID NUMBER;
1182
1183 xColumnName VARCHAR2(30);
1184 xColumnType VARCHAR2(30);
1185 xTableName VARCHAR2(30);
1186 xStatement VARCHAR2(500);
1187 xStoredInColumn VARCHAR2(30);
1188 xStoredInTable VARCHAR2(30);
1189 xSearchable VARCHAR2(1);
1190 xType VARCHAR2(30);
1191
1192 BEGIN
1193
1194 xErrLoc := 100;
1195
1196 select rt_category_id, to_char(searchable), to_char(type),
1197 stored_in_table, stored_in_column
1198 into xCategoryID, xSearchable, xType,
1199 xStoredInTable, xStoredInColumn
1200 from icx_cat_descriptors_tl
1201 where rt_descriptor_id = p_descriptor_id
1202 --OEX_IP_PORTING AND class IN ('ICX_BASE_ATTR', 'IPD_BASE_ATTR', 'POM_PRICE_ATTR', 'ICX_CAT_ATTR')
1203 and rownum = 1;
1204
1205 xErrLoc := 200;
1206
1207 -- Prevent from deleting the seeded root descriptors.
1208 if (p_descriptor_id <= NUM_SEEDED_DESCRIPTORS) then
1209 return;
1210 end if;
1211
1212 xErrLoc := 240;
1213
1214 release_section_tag(xCategoryID, p_descriptor_id);
1215
1216 xErrLoc := 400;
1217
1218 delete from icx_cat_descriptors_tl
1219 where rt_descriptor_id = p_descriptor_id;
1220
1221 xErrLoc := 600;
1222
1223 -- update the icx_cat_schema_versions
1224 inc_schema_change_version(xCategoryID);
1225 xErrLoc := 700;
1226
1227 -- rebuild flag
1228 -- 'C': catagory name changed
1229 -- 'D': searchable descriptor deleted
1230 -- 'B': both 'C' and 'D'
1231
1232 -- rebuild index if the deleted descriptor is searchable
1233 if (xSearchable = YES) then
1234 update icx_cat_categories_tl
1235 set rebuild_flag = decode(rebuild_flag,'C','B','B','B','D'),
1236 REQUEST_ID =p_request_id
1237 where rt_category_id = xCategoryID;
1238 end if;
1239
1240 xErrLoc := 800;
1241
1242 -- populate the deleted_descriptors table so that the descriptor column
1243 -- values will be erased in the items_tlp/ext_items_tlp table later.
1244 --Bug#3027134 Added who columns in icx_cat_deleted_attributes
1245 --as part of ECM OA Rewrite
1246 --So populate the who columns in icx_cat_deleted_attributes.
1247 insert into icx_cat_deleted_attributes
1248 (rt_category_id, rt_descriptor_id, stored_in_table, stored_in_column,
1249 last_update_login, last_updated_by, last_update_date, created_by,
1250 creation_date)
1251 values (xCategoryID, p_descriptor_id, xStoredInTable, xStoredInColumn,
1252 p_login_id, p_user_id, sysdate, p_user_id, sysdate);
1253
1254 xErrLoc := 900;
1255
1256 COMMIT;
1257
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 ROLLBACK;
1261
1262 RAISE_APPLICATION_ERROR(-20000,
1263 'Exception at ICX_POR_SCHEMA_UPLOAD.delete_descriptor('
1264 || xErrLoc || '): ' || SQLERRM);
1265
1266 END delete_descriptor;
1267
1268 -------------------------------------------------------------------------
1269 -- Validation --
1270 -------------------------------------------------------------------------
1271
1272 PROCEDURE validate_descriptor(p_request_id IN OUT NOCOPY NUMBER,
1273 p_line_number IN NUMBER,
1274 p_user_action IN VARCHAR2,
1275 p_system_action OUT NOCOPY VARCHAR2,
1276 p_language IN VARCHAR2,
1277 p_descriptor_id OUT NOCOPY NUMBER,
1278 p_key IN VARCHAR2,
1279 p_name IN VARCHAR2,
1280 p_type IN VARCHAR2,
1281 p_description IN VARCHAR2,
1282 p_required IN VARCHAR2,
1283 p_sequence IN VARCHAR2,
1284 p_searchable IN VARCHAR2,
1285 p_multivalue IN VARCHAR2,
1286 p_itemdetailvisible IN VARCHAR2,
1287 p_searchResultsVisible IN VARCHAR2,
1288 p_owner_key IN VARCHAR2,
1289 p_owner_name IN VARCHAR2,
1290 p_owner_id OUT NOCOPY NUMBER,
1291 p_is_valid OUT NOCOPY VARCHAR2) IS
1292 l_progress VARCHAR2(10) := '000';
1293 l_return_val VARCHAR2(1) := 'Y';
1294 l_num_val NUMBER := 0;
1295 l_current_type VARCHAR2(30) := NULL;
1296 l_name VARCHAR2(250) := NULL;
1297 l_sequence NUMBER := -1;
1298 l_searchvisible VARCHAR2(1) := NULL;
1299 l_searchable VARCHAR2(1):= NULL;
1300 l_detailvisible VARCHAR2(1) := NULL;
1301 l_required VARCHAR2(1) := NULL;
1302 l_class VARCHAR2(30) := NULL;
1303 l_multivalue VARCHAR2(1) := NULL;
1304 l_customizelevel VARCHAR2(30) := NULL;
1305 l_tableName VARCHAR2(30);
1306 l_columnName VARCHAR2(30);
1307 l_statement VARCHAR2(500);
1308
1309 l_cursor INTEGER := 0;
1310 l_count NUMBER := -1;
1311 result NUMBER;
1312
1313 BEGIN
1314
1315 p_descriptor_id := NULL;
1316 p_owner_id := NULL;
1317
1318 IF (p_user_action IN ('SYNC', 'DELETE')) THEN
1319 -- Check key is not null and is unique
1320 l_progress := '001_0';
1321
1322 -- Check owner is not null
1323 IF (p_owner_key IS NULL AND p_owner_name IS NULL) THEN
1324 InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_KEY', 'ICX_POR_INVALID_CATEGORY',
1325 p_line_number);
1326 l_return_val := 'N';
1327 ELSE
1328
1329 IF (p_owner_key IS NOT NULL) THEN
1330 BEGIN
1331 l_progress := '002_1';
1332
1333 SELECT rt_category_id INTO p_owner_id
1334 FROM icx_cat_categories_tl
1335 WHERE upper_key = UPPER(p_owner_key)
1336 AND language = p_language
1337 AND type IN (0,2);
1338
1339 EXCEPTION
1340 WHEN no_data_found THEN
1341 InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_KEY',
1342 'ICX_POR_INVALID_CATEGORY', p_line_number);
1343 l_return_val := 'N';
1344 END;
1345 END IF;
1346
1347 IF (p_owner_name IS NOT NULL) THEN
1348 BEGIN
1349 l_progress := '002_3';
1350
1351 SELECT rt_category_id INTO p_owner_id
1352 FROM icx_cat_categories_tl
1353 WHERE upper_category_name = UPPER(p_owner_name)
1354 AND language = p_language
1355 AND type IN (0,2);
1356
1357 EXCEPTION
1358 WHEN no_data_found THEN
1359 InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_NAME',
1360 'ICX_POR_INVALID_CATEGORY', p_line_number);
1361 l_return_val := 'N';
1362 END;
1363 END IF;
1364
1365 END IF;
1366
1367 IF (p_key IS NULL) THEN
1368 InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'ICX_POR_CAT_FIELD_REQUIRED', p_line_number);
1369 l_return_val := 'N';
1370
1371 -- Validate the rest as an ADD action
1372 IF (p_user_action = 'SYNC') THEN
1373 p_system_action := 'ADD';
1374 ELSIF (p_user_action = 'DELETE') THEN
1375 p_system_action := 'DELETE';
1376 END IF;
1377
1378 ELSE
1379
1380 IF (p_owner_id IS NOT NULL) THEN
1381
1382 BEGIN
1383 l_progress := '002_4';
1384
1385 SELECT rt_descriptor_id, rt_category_id, to_char(type),
1386 descriptor_name, sequence, search_results_visible,
1387 to_char(searchable), item_detail_visible, class,
1388 to_char(required), multivalue, customization_level
1389 INTO p_descriptor_id, p_owner_id, l_current_type,
1390 l_name, l_sequence, l_searchvisible, l_searchable,
1391 l_detailvisible, l_class, l_required, l_multivalue,
1392 l_customizelevel
1393 FROM icx_cat_descriptors_tl
1394 WHERE UPPER(key) = UPPER(p_key)
1395 AND language = p_language
1396 AND rt_category_id = p_owner_id
1397 --OEX_IP_PORTING AND class in('ICX_BASE_ATTR','IPD_BASE_ATTR','POM_PRICE_ATTR','ICX_CAT_ATTR')
1398 AND rownum = 1;
1399
1400 IF (p_user_action = 'SYNC') THEN
1401 p_system_action := 'UPDATE';
1402 ELSIF (p_user_action = 'DELETE') THEN
1403 p_system_action := 'DELETE';
1404 END IF;
1405
1406 EXCEPTION
1407 WHEN no_data_found THEN
1408 IF (p_user_action = 'SYNC') THEN
1409 p_system_action := 'ADD';
1410 ELSIF (p_user_action = 'DELETE') THEN
1411 p_system_action := 'DELETE';
1412 InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_CAT_INVALID_ATTRIB',
1413 p_line_number);
1414 l_return_val := 'N';
1415 END IF;
1416 END;
1417
1418 ELSE
1419 IF (p_user_action = 'SYNC') THEN
1420 p_system_action := 'ADD';
1421 ELSIF (p_user_action = 'DELETE') THEN
1422 p_system_action := 'DELETE';
1423 END IF;
1424 END IF;
1425
1426 END IF;
1427
1428 ELSE
1429 p_system_action := p_user_action;
1430 END IF;
1431
1432 IF (p_system_action = 'ADD') THEN
1433 -- Add Action
1434
1435 -- Check name is not null
1436 IF (p_name IS NULL) THEN
1437 InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_CAT_FIELD_REQUIRED',
1438 p_line_number);
1439 l_return_val := 'N';
1440 ELSE
1441 -- Check uniqueness if owner is known
1442 IF (p_owner_id IS NOT NULL) THEN
1443
1444 BEGIN
1445 l_progress := '003_1';
1446 l_num_val := 0;
1447
1448 IF (p_owner_id = 0) THEN
1449 -- check uniqueness against all the descriptor names
1450 SELECT 1 INTO l_num_val
1451 FROM dual WHERE EXISTS (
1452 SELECT 1 FROM icx_cat_descriptors_tl
1453 WHERE UPPER(descriptor_name) = UPPER(p_name));
1454 --AND language = p_language
1455 --OEX_IP_PORTING AND class in('ICX_BASE_ATTR','IPD_BASE_ATTR','POM_PRICE_ATTR','ICX_CAT_ATTR'));
1456 ELSE
1457 -- check uniqueness against root descriptors and the
1458 -- descriptors inside that particular category
1459 SELECT 1 INTO l_num_val
1460 FROM dual WHERE EXISTS (
1461 SELECT 1 FROM icx_cat_descriptors_tl
1462 WHERE UPPER(descriptor_name) = UPPER(p_name)
1463 AND (rt_category_id = p_owner_id OR rt_category_id = 0));
1464 --AND language = p_language
1465 --OEX_IP_PORTING AND class in('ICX_BASE_ATTR','IPD_BASE_ATTR','POM_PRICE_ATTR','ICX_CAT_ATTR'));
1466 END IF;
1467
1468 InsertError(p_request_id,'ICX_POR_ATTRIB_NAME','ICX_POR_ATTRIB_NAME_UNIQUE_M',
1469 p_line_number);
1470 l_return_val := 'N';
1471 EXCEPTION
1472 WHEN no_data_found THEN
1473 null;
1474 END;
1475
1476 END IF;
1477
1478 END IF;
1479
1480 -- jinwang:
1481 -- check the uniqueness of the key
1482 IF (p_owner_id IS NOT NULL AND p_key IS NOT NULL) THEN
1483 BEGIN
1484 l_progress := '003_2';
1485 l_num_val := 0;
1486
1487 IF (p_owner_id = 0) THEN
1488 -- check uniqueness against all the descriptor keys
1489 SELECT 1 INTO l_num_val
1490 FROM dual WHERE EXISTS (
1491 SELECT 1 FROM icx_cat_descriptors_tl
1492 WHERE UPPER(key) = UPPER(p_key)
1493 AND language = p_language);
1494 ELSE
1495 -- check uniqueness against root descriptors and the
1496 -- descriptors inside that particular category
1497 SELECT 1 INTO l_num_val
1498 FROM dual WHERE EXISTS (
1499 SELECT 1 FROM icx_cat_descriptors_tl
1500 WHERE UPPER(key) = UPPER(p_key)
1501 AND (rt_category_id = p_owner_id OR rt_category_id = 0)
1502 AND language = p_language);
1503 END IF;
1504
1505 --Code will mostly not come here !!!!!CHECK!!!!!
1506 InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_ATTRIB_KEY_UNIQUE_M',
1507 p_line_number);
1508 l_return_val := 'N';
1509 EXCEPTION
1510 WHEN no_data_found THEN
1511 null;
1512 END;
1513
1514 END IF;
1515
1516 l_progress := '004';
1517
1518 -- jinwang
1519 -- validate rule: can add REQUIRED base attribute only if no item exists
1520 -- in the exchange;
1521 -- can add REQUIRED category attribute only if no item exists
1522 -- in that category;
1523 IF (p_required = YES AND p_owner_id IS NOT NULL AND p_key IS NOT NULL) THEN
1524 BEGIN
1525 l_num_val := 0;
1526
1527 IF (p_owner_id = 0) THEN
1528 SELECT 1 INTO l_num_val
1529 FROM dual WHERE EXISTS (
1530 SELECT 1 FROM icx_cat_category_items);
1531
1532 ELSE
1533 SELECT 1 INTO l_num_val
1534 FROM dual WHERE EXISTS (
1535 SELECT 1 FROM icx_cat_category_items
1536 WHERE rt_category_id = p_owner_id);
1537
1538 END IF;
1539
1540 -- smallya 04/13/2001 added if else condition to display different messages
1541 -- for base and category attributes , bug number : 1736317
1542 IF (p_owner_id = 0) THEN
1543 InsertError(p_request_id,'ICX_POR_CAT_ATTRIB_REQUIRED','ICX_POR_BASE_N_ADD_REQD',p_line_number);
1544 ELSE
1545 InsertError(p_request_id,'ICX_POR_CAT_ATTRIB_REQUIRED','ICX_POR_CAT_N_ADD_REQD',p_line_number);
1546 END IF;
1547
1548 l_return_val := 'N';
1549 EXCEPTION
1550 WHEN no_data_found THEN
1551 null;
1552 END;
1553 END IF;
1554
1555 -- jinwang
1556 -- validate rule: Number of Base Attriutes < 100 (per descriptor TYPE)
1557 l_num_val := 0;
1558
1559 SELECT COUNT(*) INTO l_num_val
1560 FROM icx_cat_descriptors_tl
1561 WHERE rt_category_id = 0
1562 AND language = p_language
1563 AND to_char(type) = p_type; -- OEX_IP_PORTING
1564
1565 -- OEX_IP_PORTING
1566 IF (l_num_val >= 100 AND p_owner_id IS NOT NULL AND p_owner_id = 0) THEN
1567 InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_BASE_ATT_NUM_EXCEED',
1568 p_line_number);
1569 l_return_val := 'N';
1570 END IF;
1571
1572 -- jinwang
1573 -- validate rule: Number of Category attribute < 50 per type, per category
1574 IF (p_owner_id IS NOT NULL AND p_owner_id <> 0) THEN
1575 l_num_val := 0;
1576
1577 SELECT COUNT(*) INTO l_num_val
1578 FROM icx_cat_descriptors_tl
1579 WHERE rt_category_id = p_owner_id
1580 AND language = p_language
1581 AND to_char(type) = p_type; -- OEX_IP_PORTING
1582
1583 IF (l_num_val >= 50) THEN
1584 InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_CAT_ATT_NUM_EXCEED',
1585 p_line_number);
1586 l_return_val := 'N';
1587 END IF;
1588 END IF;
1589
1590 ELSIF (p_system_action = 'UPDATE') THEN
1591 -- Update Action
1592
1593 -- If specified, check name is not already used by another category
1594 -- We only check the other categories so the user can specify the same name
1595 -- during update
1596
1597 -- jinwang l_progress := '005_0';
1598 -- validate rule: can not update any pricing attributes
1599 -- Commenting Out as not supported in IP
1600 /*****
1601 IF (l_class = 'POM_PRICE_ATTR') THEN
1602 InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'POM_CAT_CHANGE_PRICE_ATTR',
1603 p_line_number);
1604 l_return_val := 'N';
1605 ELSE
1606 ******/
1607
1608 IF (p_name IS NOT NULL) THEN
1609
1610 -- No need to check whether p_owner_id is null coz in that case
1611 -- p_system_action will be 'ADD'
1612
1613 BEGIN
1614 l_progress := '005_1';
1615 l_num_val := 0;
1616
1617 IF (p_owner_id = 0) THEN
1618 -- check uniqueness against all the other descriptor names
1619 SELECT 1 INTO l_num_val
1620 FROM dual WHERE EXISTS (
1621 SELECT 1 FROM icx_cat_descriptors_tl
1622 WHERE UPPER(descriptor_name) = UPPER(p_name)
1623 AND rt_descriptor_id <> p_descriptor_id);
1624 --AND language = p_language
1625 ELSE
1626 -- check uniqueness against root descriptors and the other
1627 -- descriptors inside that particular category
1628 SELECT 1 INTO l_num_val
1629 FROM dual WHERE EXISTS (
1630 SELECT 1 FROM icx_cat_descriptors_tl
1631 WHERE UPPER(descriptor_name) = UPPER(p_name)
1632 AND (rt_category_id = p_owner_id OR rt_category_id = 0)
1633 AND rt_descriptor_id <> p_descriptor_id);
1634 --AND language = p_language
1635 END IF;
1636
1637 InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_ATTRIB_NAME_UNIQUE_M',
1638 p_line_number);
1639 l_return_val := 'N';
1640
1641 EXCEPTION
1642 WHEN no_data_found THEN
1643 -- name is unique
1644 null;
1645 END;
1646
1647 END IF;
1648
1649 l_progress := '005_2';
1650
1651 -- jinwang
1652 l_progress := '005_3';
1653
1654 -- jinwang
1655 -- validate rule: type can not be updated
1656 IF (p_type IS NOT NULL AND p_type <> l_current_type) THEN
1657 -- Cannot update type
1658 InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_CHANGE_TYPE',
1659 p_line_number);
1660 l_return_val := 'N';
1661 END IF;
1662
1663 l_progress := '005_4';
1664 ELSE
1665 --Bug#2743930
1666 --IF (SUBSTR(l_customizelevel,1,1) = 0) THEN
1667 -- Check name is not null
1668 IF (p_descriptor_id IS NOT NULL AND
1669 p_descriptor_id <= NUM_SEEDED_DESCRIPTORS) THEN
1670 IF (p_name IS NOT NULL) THEN
1671 InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_CAT_DELETE_RESERVED',
1672 p_line_number);
1673 l_return_val := 'N';
1674 ELSIF (p_key IS NOT NULL) THEN
1675 InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'ICX_POR_CAT_DELETE_RESERVED',
1676 p_line_number);
1677 l_return_val := 'N';
1678 END IF;
1679 END IF;
1680 END IF;
1681
1682 l_progress := '010';
1683
1684 p_is_valid := l_return_val;
1685
1686 IF(p_owner_id IS NULL) THEN
1687 p_owner_id := -1;
1688 END IF;
1689
1690 COMMIT;
1691 EXCEPTION
1692 WHEN OTHERS then
1693 RAISE_APPLICATION_ERROR
1694 (-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.validate_descriptor(ErrLoc = ' || l_progress ||') ' ||
1695 'SQL Error : ' || SQLERRM);
1696 END validate_descriptor;
1697
1698 PROCEDURE validate_category(p_request_id IN OUT NOCOPY NUMBER,
1699 p_line_number IN NUMBER,
1700 p_user_action IN VARCHAR2,
1701 p_system_action OUT NOCOPY VARCHAR2,
1702 p_language IN VARCHAR2,
1703 p_category_id OUT NOCOPY NUMBER,
1704 p_key IN VARCHAR2,
1705 p_name IN VARCHAR2,
1706 p_type IN VARCHAR2,
1707 p_type_value OUT NOCOPY VARCHAR2,
1708 p_owner_key IN VARCHAR2,
1709 p_owner_name IN VARCHAR2,
1710 p_owner_id OUT NOCOPY NUMBER,
1711 p_is_valid OUT NOCOPY VARCHAR2) IS
1712 l_progress VARCHAR2(10) := '000';
1713 l_return_val VARCHAR2(1) := 'Y';
1714 l_num_val NUMBER := 0;
1715 l_action VARCHAR2(10) := 'ADD';
1716 l_current_type NUMBER := NULL;
1717 BEGIN
1718
1719 p_owner_id := NULL;
1720 p_type_value := NULL;
1721 p_category_id := NULL;
1722
1723 IF (p_user_action IN ('SYNC', 'DELETE')) THEN
1724 -- Check key is not null
1725 l_progress := '000';
1726
1727 IF (p_key IS NULL) THEN
1728 InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_FIELD_REQUIRED',
1729 p_line_number);
1730 l_return_val := 'N';
1731
1732 -- Validate the rest as in an ADD action
1733 IF (p_user_action = 'DELETE') THEN
1734 p_system_action := 'DELETE';
1735 ELSE
1736 p_system_action := 'ADD';
1737 END IF;
1738
1739 ELSE
1740
1741 l_progress := '000_1';
1742
1743 BEGIN
1744 SELECT rt_category_id, type INTO p_category_id, l_current_type
1745 FROM icx_cat_categories_tl
1746 WHERE upper_key = UPPER(p_key)
1747 AND language = p_language
1748 AND rownum = 1;
1749
1750 IF (p_user_action = 'SYNC') THEN
1751 p_system_action := 'UPDATE';
1752 ELSIF (p_user_action = 'DELETE') THEN
1753 p_system_action := 'DELETE';
1754 END IF;
1755
1756 EXCEPTION
1757 WHEN no_data_found THEN
1758 IF (p_user_action = 'SYNC') THEN
1759 p_system_action := 'ADD';
1760 ELSIF (p_user_action = 'DELETE') THEN
1761 p_system_action := 'DELETE';
1762 InsertError(p_request_id,'ICX_POR_CATEGORY_KEY','ICX_POR_INVALID_CATEGORY',
1763 p_line_number);
1764 l_return_val := 'N';
1765 END IF;
1766 END;
1767
1768 END IF;
1769
1770 ELSE
1771 p_system_action := p_user_action;
1772 END IF;
1773
1774 IF (p_system_action = 'ADD') THEN
1775 -- Add Action
1776
1777 l_progress := '003';
1778
1779 -- Check name is not null and is unique
1780 IF (p_name IS NULL) THEN
1781 InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_FIELD_REQUIRED',
1782 p_line_number);
1783 l_return_val := 'N';
1784 ELSE
1785 l_progress := '004';
1786 l_num_val := 0;
1787
1788 SELECT count(1) INTO l_num_val
1789 FROM icx_cat_categories_tl
1790 WHERE upper_category_name = UPPER(p_name);
1791 --AND language = p_language
1792
1793 IF (l_num_val > 0) THEN
1794 InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_NAME_UNIQUE_M',
1795 p_line_number);
1796 l_return_val := 'N';
1797 END IF;
1798
1799 END IF;
1800
1801 l_progress := '005';
1802
1803 -- Default type to GENUS
1804 IF (p_type IS NULL) THEN
1805 null;
1806 -- InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_FIELD_REQUIRED', p_line_number);
1807 -- l_return_val := 'N';
1808 END IF;
1809
1810 ELSIF (p_system_action = 'UPDATE') THEN
1811 -- Update Action
1812
1813 -- No need to check if key exists, since if it doesn't it would have
1814 -- been an 'ADD' action
1815
1816 -- If specified, check name is not already used by another category
1817 -- We only check the other categories so the user can specify the same name
1818 -- during update
1819
1820 l_progress := '006';
1821
1822 IF (p_name IS NOT NULL) THEN
1823 l_num_val := 0;
1824
1825 SELECT count(1) INTO l_num_val
1826 FROM icx_cat_categories_tl
1827 WHERE upper_category_name = UPPER(p_name)
1828 --AND language = p_language
1829 AND rt_category_id <> p_category_id;
1830
1831 IF (l_num_val > 0) THEN
1832 InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_NAME_UNIQUE_M',
1833 p_line_number);
1834 l_return_val := 'N';
1835 END IF;
1836
1837 END IF;
1838
1839 -- jinwang
1840 -- validate rule: root category can not be updated
1841 IF (p_category_id = 0) THEN
1842 InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_ROOT_DELETE',
1843 p_line_number);
1844 l_return_val := 'N';
1845 END IF;
1846
1847 ELSIF (p_system_action = 'DELETE') THEN
1848 -- jinwang
1849 -- validate rule: root category can not be deleted
1850 IF (p_key IS NOT NULL AND p_category_id = 0) THEN
1851 InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_ROOT_DELETE',
1852 p_line_number);
1853 l_return_val := 'N';
1854 END IF;
1855 BEGIN
1856
1857 l_progress := '006_1';
1858
1859 -- check if there are any items in this category
1860 SELECT 1 INTO l_num_val
1861 FROM dual
1862 WHERE EXISTS (SELECT 1 FROM icx_cat_category_items
1863 WHERE rt_category_id = p_category_id);
1864
1865 InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_HAS_ITEMS',
1866 p_line_number);
1867 l_return_val := 'N';
1868
1869 EXCEPTION
1870 WHEN no_data_found THEN
1871 -- No items for this category
1872 null;
1873 END;
1874
1875 END IF;
1876
1877 -- Check type
1878 l_progress := '010';
1879
1880 -- Check to see if type is valid
1881 IF (p_user_action = 'SYNC' AND p_type IS NOT NULL) THEN
1882 IF (p_type IN ('0','1','2')) THEN
1883 p_type_value := p_type;
1884 ELSIF (p_language = 'US') THEN
1885 -- Hard code in the validation for speed
1886 IF (UPPER(p_type) = 'ROOT') THEN
1887 p_type_value := '0';
1888 ELSIF (UPPER(p_type) = 'BROWSING') THEN
1889 p_type_value := '1';
1890 ELSIF (UPPER(p_type) = 'NAVIGATION') THEN
1891 p_type_value := '1';
1892 ELSIF (UPPER(p_type) = 'ITEM') THEN
1893 p_type_value := '2';
1894 ELSIF (UPPER(p_type) = 'GENUS') THEN
1895 p_type_value := '2';
1896 ELSE
1897 InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_INVALID_TYPE',
1898 p_line_number);
1899 l_return_val := 'N';
1900 END IF;
1901
1902 ELSE
1903
1904 BEGIN
1905 SELECT lookup_code INTO p_type_value
1906 FROM fnd_lookup_values
1907 WHERE lookup_type = 'ICX_CAT_TYPE'
1908 AND UPPER(meaning) = UPPER(p_type)
1909 AND language = p_language;
1910 l_progress := '100';
1911
1912 EXCEPTION
1913 WHEN no_data_found THEN
1914 InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_INVALID_TYPE',
1915 p_line_number);
1916 l_return_val := 'N';
1917 END;
1918 END IF;
1919
1920 l_progress := '200';
1921 IF (p_system_action = 'UPDATE') THEN
1922 IF (p_type_value is not null and l_current_type <> p_type_value) THEN
1923 -- Cannot update type
1924 InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_CHANGE_TYPE',
1925 p_line_number);
1926 l_return_val := 'N';
1927 END IF;
1928 END IF;
1929 ELSIF (p_system_action IN ('UPDATE')) THEN
1930 p_type_value := l_current_type;
1931 END IF;
1932 l_progress := '500';
1933
1934 p_is_valid := l_return_val;
1935
1936 COMMIT;
1937
1938 EXCEPTION
1939 WHEN others THEN
1940 RAISE_APPLICATION_ERROR
1941 (-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.validate_category(ErrLoc = ' || l_progress ||') ' ||
1942 'SQL Error : ' || SQLERRM);
1943 END validate_category;
1944
1945 PROCEDURE validate_hier_relationship(p_request_id IN OUT NOCOPY NUMBER,
1946 p_line_number IN NUMBER,
1947 p_user_action IN VARCHAR2,
1948 p_system_action OUT NOCOPY VARCHAR2,
1949 p_language IN VARCHAR2,
1950 p_parent_key IN VARCHAR2,
1951 p_parent_name IN VARCHAR2,
1952 p_parent_id OUT NOCOPY NUMBER,
1953 p_child_key IN VARCHAR2,
1954 p_child_name IN VARCHAR2,
1955 p_child_id OUT NOCOPY NUMBER,
1956 p_is_valid OUT NOCOPY VARCHAR2) IS
1957 l_progress VARCHAR2(10) := '000';
1958 l_return_val VARCHAR2(1) := 'Y';
1959 l_num_val NUMBER := -1;
1960 l_parent_type NUMBER := null;
1961 BEGIN
1962 p_parent_id := NULL;
1963 p_child_id := NULL;
1964
1965 IF (p_user_action = 'SYNC' OR p_user_action = 'DELETE') THEN
1966 -- Check parent is not null
1967 l_progress := '000';
1968
1969 IF (p_user_action = 'SYNC') THEN
1970 -- SYNC is equivalent to add
1971 p_system_action := 'ADD';
1972 ELSE
1973 p_system_action := 'DELETE';
1974 END IF;
1975
1976 IF (p_parent_key IS NULL AND p_parent_name IS NULL) THEN
1977 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
1978 'ICX_POR_CAT_FIELD_REQUIRED',
1979 p_line_number);
1980 l_return_val := 'N';
1981 ELSE
1982
1983 l_progress := '010';
1984
1985 IF (p_parent_key IS NOT NULL) THEN
1986 BEGIN
1987 l_progress := '020';
1988
1989 -- check if parent key exists
1990 SELECT rt_category_id, type INTO p_parent_id, l_parent_type
1991 FROM icx_cat_categories_tl
1992 WHERE upper_key = UPPER(p_parent_key)
1993 AND language = p_language;
1994
1995 IF (l_parent_type = 2) THEN
1996 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
1997 'ICX_POR_CAT_GENUS_PARENT', p_line_number);
1998 l_return_val := 'N';
1999 END IF;
2000
2001 EXCEPTION
2002 WHEN no_data_found THEN
2003 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
2004 'ICX_POR_INVALID_CATEGORY', p_line_number);
2005 l_return_val := 'N';
2006 END;
2007
2008 END IF;
2009
2010 l_progress := '030';
2011
2012 IF (p_parent_name IS NOT NULL) THEN
2013 BEGIN
2014 l_progress := '040';
2015
2016 -- check if parent key exists
2017 SELECT rt_category_id, type INTO l_num_val, l_parent_type
2018 FROM icx_cat_categories_tl
2019 WHERE upper_category_name = UPPER(p_parent_name)
2020 AND language = p_language;
2021
2022 IF (p_parent_id IS NOT NULL) THEN
2023
2024 IF (p_parent_id <> l_num_val) THEN
2025 -- Key and Name points to different category
2026 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
2027 'ICX_POR_CAT_REL_DIFF_KEY_NAME',
2028 p_line_number);
2029 l_return_val := 'N';
2030 END IF;
2031
2032 ELSE
2033 p_parent_id := l_num_val;
2034 END IF;
2035
2036 IF (l_parent_type = 2) THEN
2037 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
2038 'ICX_POR_CAT_GENUS_PARENT', p_line_number);
2039 l_return_val := 'N';
2040 END IF;
2041
2042 EXCEPTION
2043 WHEN no_data_found THEN
2044 InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
2045 'ICX_POR_INVALID_CATEGORY', p_line_number);
2046 l_return_val := 'N';
2047 END;
2048
2049 END IF;
2050
2051 END IF;
2052
2053 l_progress := '050';
2054
2055 IF (p_child_key IS NULL AND p_child_name IS NULL) THEN
2056 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY', 'ICX_POR_CAT_FIELD_REQUIRED',
2057 p_line_number);
2058 l_return_val := 'N';
2059 ELSE
2060
2061 l_progress := '060';
2062
2063 IF (p_child_key IS NOT NULL) THEN
2064 BEGIN
2065 -- check if child key exists
2066 SELECT rt_category_id INTO p_child_id
2067 FROM icx_cat_categories_tl
2068 WHERE upper_key = UPPER(p_child_key)
2069 AND language = p_language;
2070
2071 EXCEPTION
2072 WHEN no_data_found THEN
2073 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
2074 'ICX_POR_INVALID_CATEGORY', p_line_number);
2075 l_return_val := 'N';
2076 END;
2077
2078 END IF;
2079
2080 l_progress := '070';
2081
2082 IF (p_child_name IS NOT NULL) THEN
2083 BEGIN
2084 -- check if child key exists
2085 SELECT rt_category_id INTO l_num_val
2086 FROM icx_cat_categories_tl
2087 WHERE upper_category_name = UPPER(p_child_name)
2088 AND language = p_language;
2089
2090 IF (p_child_id IS NOT NULL) THEN
2091
2092 IF (p_child_id <> l_num_val) THEN
2093 -- Key and Name points to different category
2094 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
2095 'ICX_POR_CAT_REL_DIFF_KEY_NAME', p_line_number);
2096 l_return_val := 'N';
2097 END IF;
2098
2099 ELSE
2100 p_child_id := l_num_val;
2101 END IF;
2102
2103 EXCEPTION
2104 WHEN no_data_found THEN
2105 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
2106 'ICX_POR_INVALID_CATEGORY', p_line_number);
2107 l_return_val := 'N';
2108 END;
2109
2110 END IF;
2111
2112 END IF;
2113
2114 IF (p_child_id IS NOT NULL AND p_parent_id IS NOT NULL) THEN
2115
2116 IF (p_system_action = 'ADD') THEN
2117 IF (p_child_id = p_parent_id) THEN
2118 -- Bug 1546149, zxzhang, Jan-02-00
2119 IF (p_child_key IS NOT NULL) THEN
2120 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
2121 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
2122 ELSE
2123 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
2124 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
2125 END IF;
2126 l_return_val := 'N';
2127 END IF;
2128 ELSIF (p_system_action = 'DELETE') THEN
2129
2130 BEGIN
2131 SELECT 1 INTO l_num_val
2132 FROM dual
2133 WHERE exists (SELECT 1 FROM icx_cat_browse_trees
2134 WHERE parent_category_id = p_parent_id
2135 AND child_category_id = p_child_id);
2136
2137 EXCEPTION
2138 WHEN no_data_found THEN
2139 -- Bug 1546149, zxzhang, Jan-02-00
2140 IF (p_child_key IS NOT NULL) THEN
2141 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
2142 --ErrMsg 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
2143 'ICX_POR_CAT_REL_NO_CHILD', p_line_number);
2144 ELSE
2145 InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
2146 --ErrMsg 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
2147 'ICX_POR_CAT_REL_NO_CHILD', p_line_number);
2148 END IF;
2149 l_return_val := 'N';
2150 END;
2151
2152 END IF;
2153
2154 END IF;
2155
2156 END IF;
2157
2158 p_is_valid := l_return_val;
2159
2160 COMMIT;
2161 EXCEPTION
2162 WHEN OTHERS then
2163 RAISE_APPLICATION_ERROR
2164 (-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.validate_hier_relationship(ErrLoc = ' || l_progress ||') ' || 'SQL Error : ' || SQLERRM);
2165 END validate_hier_relationship;
2166
2167
2168 PROCEDURE InsertError(p_request_id in out NOCOPY number,
2169 p_descriptor_key in varchar2,
2170 p_message_name in varchar2,
2171 p_line_number in number
2172 ) IS
2173 l_progress varchar2(10) := '000';
2174 BEGIN
2175 l_progress := '001';
2176
2177 if (p_request_id is null) then
2178 l_progress := '002';
2179 SELECT icx_por_batch_jobs_s.nextval
2180 INTO p_request_id
2181 FROM sys.dual;
2182 end if;
2183
2184 l_progress := '004';
2185 INSERT into icx_por_failed_line_messages (
2186 job_number,
2187 descriptor_key,
2188 message_name,
2189 line_number
2190 ) VALUES (
2191 p_request_id,
2192 p_descriptor_key,
2193 p_message_name,
2194 p_line_number
2195 );
2196
2197 l_progress := '005';
2198 COMMIT;
2199 EXCEPTION
2200 WHEN others THEN
2201 -- Debug('[InsertError-'||l_progress||'] '||SQLERRM);
2202 RAISE_APPLICATION_ERROR
2203 (-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.InsertError(ErrLoc = ' || l_progress ||') ' ||
2204 'SQL Error : ' || SQLERRM);
2205 END InsertError;
2206
2207 /**
2208 ** Proc : release_section_tag
2209 ** Desc : Called when a descriptor is to be deleted or made not searchable
2210 ** SHOULD BE CALLED BEFORE THE DESCRIPTOR IS ACTUALLY DELETED
2211 ** Before calling this the rows in icx_cat_categories_tl with the
2212 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
2213 ** to avoid concurrent access to the SECTION_MAP column. The calling
2214 ** code is responsible for committing the changes.
2215 ** Parameters:
2216 ** p_category_id - category to be modified
2217 ** p_descriptor_id - descriptor to be modified
2218 **/
2219 PROCEDURE release_section_tag(p_category_id IN NUMBER,
2220 p_descriptor_id IN NUMBER) IS
2221 v_bit_position PLS_INTEGER := 0;
2222 v_section_map VARCHAR2(300) := NULL;
2223 xErrLoc PLS_INTEGER;
2224 BEGIN
2225 xErrLoc := 100;
2226
2227 -- Find the section map, we can use any row with p_category_id
2228 SELECT section_map INTO v_section_map
2229 FROM icx_cat_categories_tl
2230 WHERE rt_category_id = p_category_id AND ROWNUM = 1;
2231
2232 xErrLoc := 200;
2233
2234 SELECT section_tag INTO v_bit_position
2235 FROM icx_cat_descriptors_tl
2236 WHERE rt_descriptor_id = p_descriptor_id
2237 AND ROWNUM = 1;
2238
2239 IF v_bit_position IS NULL THEN
2240 -- There is no section tag assigned, no need to do anything
2241 RETURN;
2242 END IF;
2243
2244 xErrLoc := 300;
2245
2246 UPDATE icx_cat_descriptors_tl SET section_tag = NULL
2247 WHERE rt_descriptor_id = p_descriptor_id;
2248
2249 IF p_category_id > 0 THEN
2250 v_bit_position := v_bit_position - 5000;
2251 elsif p_descriptor_id >= NUM_SEEDED_DESCRIPTORS then
2252 v_bit_position := v_bit_position - 1000;
2253 END IF;
2254
2255 v_section_map := substr(v_section_map,1,v_bit_position-1) || '0' ||
2256 substr(v_section_map,v_bit_position+1);
2257
2258 xErrLoc := 400;
2259
2260 UPDATE icx_cat_categories_tl SET section_map = v_section_map
2261 WHERE rt_category_id = p_category_id;
2262
2263 COMMIT;
2264 EXCEPTION
2265 WHEN OTHERS THEN
2266 ROLLBACK;
2267 RAISE_APPLICATION_ERROR(-20000,'Exception at ICX_POR_SCHEMA_UPLOAD.release_section_tag(' || xErrLoc || '): ' || SQLERRM);
2268 END release_section_tag;
2269
2270
2271 /**
2272 ** Proc : assign_section_tag
2273 ** Desc : Assigns a section tag to a given searchable descriptor. If the
2274 ** descriptor is already assigned a tag then the assigned tag will
2275 ** be returned.
2276 ** Before calling this the rows in icx_cat_categories_tl with the
2277 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
2278 ** to avoid concurrent access to the SECTION_MAP column. The calling
2279 ** code is responsible for committing the changes.
2280 ** Parameters:
2281 ** p_category_id IN NUMBER - category to be modified
2282 ** p_descriptor_id IN NUMBER - descriptor to be modified
2283 ** p_section_tag OUT NUMBER - section tag assigned
2284 **/
2285 PROCEDURE assign_section_tag(p_category_id IN NUMBER,
2286 p_descriptor_id IN NUMBER,
2287 p_section_tag OUT NOCOPY NUMBER,
2288 p_stored_in_table OUT NOCOPY VARCHAR2,
2289 p_stored_in_column OUT NOCOPY VARCHAR2,
2290 p_type IN VARCHAR2
2291 ) IS
2292 v_section_map VARCHAR2(300);
2293 l_section_tag NUMBER;
2294 l_stored_in_column VARCHAR2(30);
2295 l_stored_in_table VARCHAR2(30);
2296 l_column_prefix VARCHAR2(10);
2297 xErrLoc PLS_INTEGER := 0;
2298 BEGIN
2299
2300 -- Check whether a section tag is already assigned
2301 SELECT section_tag, stored_in_table, stored_in_column
2302 INTO l_section_tag, l_stored_in_table, l_stored_in_column
2303 FROM icx_cat_descriptors_tl
2304 WHERE rt_descriptor_id = p_descriptor_id
2305 AND rownum = 1;
2306
2307 IF (p_section_tag IS NOT NULL) THEN
2308 -- Section tag already assigned, just return
2309 -- OEX_IP_PORTING
2310 p_section_tag := l_section_tag;
2311 p_stored_in_table := l_stored_in_table;
2312 p_stored_in_column := l_stored_in_column;
2313 RETURN;
2314 END IF;
2315
2316 xErrLoc := 100;
2317
2318 -- We already do the check for max attributes per type in validate_descriptor
2319 -- You would never reach here if num descriptors per type is >100
2320 -- validate_descriptor would have thrown the error,
2321 -- ICX_POR_CAT_ATT_NUM_EXCEED or ICX_POR_BASE_ATT_NUM_EXCEED
2322 if (p_type = 0) then
2323 SELECT section_map, INSTR(section_map,'0', 1, 1) INTO v_section_map, p_section_tag
2324 FROM icx_cat_categories_tl
2325 WHERE rt_category_id = p_category_id
2326 AND rownum = 1;
2327 elsif (p_type = 1) then
2328 SELECT section_map, INSTR(section_map,'0', 101, 1) INTO v_section_map, p_section_tag
2329 FROM icx_cat_categories_tl
2330 WHERE rt_category_id = p_category_id
2331 AND rownum = 1;
2332 elsif (p_type =2) then
2333 SELECT section_map, INSTR(section_map,'0', 201, 1) INTO v_section_map, p_section_tag
2334 FROM icx_cat_categories_tl
2335 WHERE rt_category_id = p_category_id
2336 AND rownum = 1;
2337 end if;
2338
2339
2340 xErrLoc := 110;
2341
2342 IF (p_section_tag > 0) THEN
2343
2344 -- identifythe stored_in_table and stored_in_column for the
2345 -- descriptor..
2346 -- The stored in table depends on category_id
2347 -- and stored in column depends on descriptor type
2348 if (p_category_id = 0) then
2349 l_stored_in_table := 'ICX_CAT_ITEMS_TLP';
2350 l_column_prefix := 'BASE';
2351 else
2352 l_stored_in_table := 'ICX_CAT_EXT_ITEMS_TLP';
2353 l_column_prefix := 'CAT';
2354 end if;
2355
2356 -- if section tag is 1, then the stored_in_column is
2357 -- TEXT_<CAT/BASE>_ATTRIBUTE_1
2358 -- if section tag is 101, then the stored_in_column is
2359 -- NUM_<CAT/BASE>_ATTRIBUTE_1
2360 -- if section tag is 201, then the stored_in_column is
2361 -- TL_TEXT_<CAT/BASE>_ATTRIBUTE_1
2362 -- So subtract the right amount to get the number that should suffixed to the
2363 -- the stored in column(Done below).
2364 if (p_type = TEXT_TYPE) then
2365 l_stored_in_column := 'TEXT_'||l_column_prefix||'_ATTRIBUTE'||to_char(p_section_tag);
2366 elsif (p_type = NUMERIC_TYPE) then
2367 p_section_tag := p_section_tag - 100;
2368 l_stored_in_column := 'NUM_'||l_column_prefix||'_ATTRIBUTE'||to_char(p_section_tag);
2369 p_section_tag := p_section_tag + 100;
2370 else
2371 p_section_tag := p_section_tag - 200;
2372 l_stored_in_column := 'TL_TEXT_'||l_column_prefix||'_ATTRIBUTE'||to_char(p_section_tag);
2373 p_section_tag := p_section_tag + 200;
2374 end if;
2375
2376 xErrLoc := 120;
2377
2378 v_section_map := substr(v_section_map,1,p_section_tag-1) || '1' ||
2379 substr(v_section_map,p_section_tag+1);
2380
2381 xErrLoc := 130;
2382
2383 IF (p_category_id > 0) THEN
2384 p_section_tag := p_section_tag + 5000;
2385 ELSE
2386 p_section_tag := p_section_tag + 1000;
2387 END IF;
2388
2389 xErrLoc := 200;
2390
2391 UPDATE icx_cat_categories_tl SET section_map = v_section_map
2392 WHERE rt_category_id = p_category_id;
2393
2394 xErrLoc := 300;
2395
2396 UPDATE icx_cat_descriptors_tl SET section_tag = p_section_tag,
2397 stored_in_table = l_stored_in_table,
2398 stored_in_column = l_stored_in_column
2399 WHERE rt_descriptor_id = p_descriptor_id;
2400
2401 xErrLoc := 300;
2402
2403 -- OEX_IP_PORTING
2404 p_stored_in_table := l_stored_in_table;
2405 p_stored_in_column := l_stored_in_column;
2406
2407 END IF;
2408
2409 COMMIT;
2410 EXCEPTION
2411 WHEN OTHERS THEN
2412 ROLLBACK;
2413 RAISE_APPLICATION_ERROR(-20000,'Exception at ICX_POR_SCHEMA_UPLOAD.assign_section_tag(' || xErrLoc || '): ' || SQLERRM);
2414 END assign_section_tag;
2415
2416 /**
2417 ** Proc : assign_all_section_tags
2418 ** Desc : Assigns section tags to all searchable descriptors of a given
2419 ** category. This is intended to be called during the upgrade to 6.2
2420 ** or when batch update of a category is needed
2421 ** Before calling this the rows in icx_cat_categories_tl with the
2422 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
2423 ** to avoid concurrent access to the SECTION_MAP column. The calling
2424 ** code is responsible for committing the changes.
2425 ** Parameters:
2426 ** p_category_id - category to be modified
2427 **/
2428 PROCEDURE assign_all_section_tags(p_category_id IN NUMBER) IS
2429 v_section_map VARCHAR2(300);
2430 v_start PLS_INTEGER;
2431 v_section_tag PLS_INTEGER;
2432 -- OEX_IP_PORTING
2433 v_stored_section_tag NUMBER;
2434 v_base_language fnd_languages.language_code%TYPE;
2435 v_offset PLS_INTEGER;
2436 v_bit_position PLS_INTEGER;
2437 v_typeOffset PLS_INTEGER;
2438 v_type PLS_INTEGER;
2439 xErrLoc PLS_INTEGER;
2440 l_stored_in_column VARCHAR2(30);
2441 l_stored_in_table VARCHAR2(30);
2442
2443 -- Get the descriptor id..
2444 -- Needed to determine the section tag values
2445 -- Assign section map/section tag only for non-seeded attributes
2446 CURSOR get_assigned_descriptors(x_category_id NUMBER, x_language VARCHAR2) IS
2447 SELECT rt_descriptor_id, section_tag, type FROM icx_cat_descriptors_tl
2448 WHERE rt_category_id = x_category_id
2449 AND language = x_language
2450 AND section_tag IS NOT NULL
2451 AND rt_descriptor_id >1000
2452 ORDER BY rt_descriptor_id
2453 ;
2454
2455 CURSOR get_unassigned_descriptors(x_category_id NUMBER,x_language VARCHAR2) IS
2456 SELECT rt_descriptor_id, type FROM icx_cat_descriptors_tl
2457 WHERE rt_category_id = x_category_id
2458 AND language = x_language
2459 AND section_tag IS NULL
2460 AND rt_descriptor_id >1000
2461 ORDER BY rt_descriptor_id
2462 ;
2463
2464 BEGIN
2465 v_section_map := LPAD('0',300,'0');
2466 -- Category attributes start with section tag=5000
2467 IF (p_category_id <> 0) THEN
2468 v_offset := 5000;
2469 END IF;
2470
2471 xErrLoc := 100;
2472
2473 SELECT language_code INTO v_base_language
2474 FROM fnd_languages WHERE installed_flag = 'B';
2475
2476 /*
2477 * Step 1: Generate the bitmap based on all searchable attributes that already
2478 * have a section tag. This takes care of deleted attributes or attributes
2479 * changed to not searchable since the corresponding bits will be cleared
2480 */
2481 xErrLoc := 200;
2482
2483 FOR rec IN get_assigned_descriptors(p_category_id,v_base_language) LOOP
2484 v_type := rec.type;
2485 -- Unseeded base attributes start with section tag=1000
2486 if(p_category_id = 0 AND rec.rt_descriptor_id > NUM_SEEDED_DESCRIPTORS) then
2487 v_offset := 1000;
2488 elsif(rec.rt_descriptor_id <= NUM_SEEDED_DESCRIPTORS) then
2489 v_offset := 0;
2490 end if;
2491
2492 xErrLoc := 220;
2493
2494 v_bit_position := rec.section_tag - v_offset;
2495 v_section_map := substr(v_section_map,1,v_bit_position-1) || '1' || substr(v_section_map,v_bit_position+1);
2496 xErrLoc := 230;
2497
2498 if(rec.rt_descriptor_id > 1000) then
2499 get_stored_in_values(rec.rt_descriptor_id, p_category_id, rec.type, rec.section_tag,
2500 l_stored_in_table, l_stored_in_column);
2501 xErrLoc := 240;
2502
2503 UPDATE icx_cat_descriptors_tl
2504 SET stored_in_column = l_stored_in_column,
2505 stored_in_table = l_stored_in_table
2506 WHERE rt_descriptor_id = rec.rt_descriptor_id
2507 AND (stored_in_column is null OR stored_in_table is null);
2508 END IF;
2509
2510 END LOOP;
2511
2512 /*
2513 * Step 2: For each searchable attribute that does not have a tag assigned,
2514 * get the first unused tag and assign to it
2515 */
2516 xErrLoc := 300;
2517 v_start := 1;
2518
2519 FOR rec IN get_unassigned_descriptors(p_category_id,v_base_language) LOOP
2520 v_type := rec.type;
2521
2522 -- Within the 300 characters, the first 100 is reserved for text, 101-200 is
2523 -- reserved for number and 201-300 is reserved for translated text: this
2524 -- information is stored in v_typeOffset
2525 -- v_section_tag := INSTR(v_section_map,'0',v_start);
2526 if (v_type = 0) then
2527 v_section_tag := INSTR(v_section_map,'0', 1, 1);
2528 elsif (v_type = 1) then
2529 v_section_tag := INSTR(v_section_map,'0', 101, 1);
2530 elsif (v_type = 2) then
2531 v_section_tag := INSTR(v_section_map,'0', 201, 1);
2532 end if;
2533 v_section_map := substr(v_section_map,1,v_section_tag-1) || '1' ||
2534 substr(v_section_map,v_section_tag+1);
2535
2536 -- OEX_IP_PORTING: Set the section tag based on seeded/category attribute
2537 -- unseeded root descriptors
2538 if(p_category_id <> 0 ) then
2539 v_stored_section_tag := v_section_tag + 5000;
2540 elsif(p_category_id = 0 AND rec.rt_descriptor_id > NUM_SEEDED_DESCRIPTORS) then
2541 v_stored_section_tag := v_section_tag + 1000;
2542 else
2543 v_stored_section_tag := v_section_tag;
2544 end if;
2545
2546 get_stored_in_values(rec.rt_descriptor_id, p_category_id, rec.type, v_stored_section_tag,
2547 l_stored_in_table, l_stored_in_column);
2548
2549 UPDATE icx_cat_descriptors_tl
2550 SET section_tag = v_stored_section_tag,
2551 stored_in_column = l_stored_in_column,
2552 stored_in_table = l_stored_in_table
2553 WHERE rt_descriptor_id = rec.rt_descriptor_id;
2554 END LOOP;
2555
2556 xErrLoc := 400;
2557
2558 UPDATE icx_cat_categories_tl SET section_map = v_section_map
2559 WHERE rt_category_id = p_category_id;
2560
2561 xErrLoc := 500;
2562 COMMIT;
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 ROLLBACK;
2566 RAISE_APPLICATION_ERROR(-20000,'Exception at ICX_POR_SCHEMA_UPLOAD.assign_all_section_tags(' || xErrLoc || '): ' || SQLERRM);
2567 END assign_all_section_tags;
2568
2569 /**
2570 ** Proc : get_stored_in_values
2571 ** Desc : Formulates the stored_in_table, stored_in_column
2572 ** Parameters:
2573 ** p_descriptor_id - rt_descriptor_id
2574 ** p_category_id - rt_category_id
2575 ** type - type
2576 ** p_section_tag - section_tag
2577 **/
2578 PROCEDURE get_stored_in_values(p_descriptor_id IN NUMBER,
2579 p_category_id IN NUMBER,
2580 p_type IN VARCHAR2,
2581 p_section_tag IN NUMBER,
2582 p_stored_in_table OUT NOCOPY VARCHAR2,
2583 p_stored_in_column OUT NOCOPY VARCHAR2) IS
2584
2585 l_stored_in_column VARCHAR2(30);
2586 l_stored_in_table VARCHAR2(30);
2587 l_column_prefix VARCHAR2(10);
2588 v_section_tag NUMBER := p_section_tag;
2589 xErrLoc PLS_INTEGER := 0;
2590 BEGIN
2591 xErrLoc := 100;
2592
2593 xErrLoc := 200;
2594 IF (p_section_tag > 0) THEN
2595 -- identifythe stored_in_table and stored_in_column for the
2596 -- descriptor..
2597 -- The stored in table depends on category_id
2598 -- and stored in column depends on descriptor type
2599
2600 if (p_category_id = 0) then
2601 l_stored_in_table := 'ICX_CAT_ITEMS_TLP';
2602 l_column_prefix := 'BASE';
2603
2604 if (p_descriptor_id > NUM_SEEDED_DESCRIPTORS) then
2605 v_section_tag := v_section_tag - 1000;
2606 end if;
2607 else
2608 l_stored_in_table := 'ICX_CAT_EXT_ITEMS_TLP';
2609 l_column_prefix := 'CAT';
2610 v_section_tag := v_section_tag - 5000;
2611 end if;
2612 -- if section tag is 1, then the stored_in_column is
2613 -- TEXT_<CAT/BASE>_ATTRIBUTE_1
2614 -- if section tag is 101, then the stored_in_column is
2615 -- NUM_<CAT/BASE>_ATTRIBUTE_1
2616 -- if section tag is 201, then the stored_in_column is
2617 -- TL_TEXT_<CAT/BASE>_ATTRIBUTE_1
2618 -- So subtract the right amount to get the number that should suffixed to the
2619 -- the stored in column(Done below).
2620 if (p_type = TEXT_TYPE) then
2621 l_stored_in_column := 'TEXT_'||l_column_prefix||'_ATTRIBUTE'||to_char(v_section_tag);
2622 elsif (p_type = NUMERIC_TYPE) then
2623 v_section_tag := v_section_tag - 100;
2624 l_stored_in_column := 'NUM_'||l_column_prefix||'_ATTRIBUTE'||to_char(v_section_tag);
2625 else
2626 v_section_tag := v_section_tag - 200;
2627 l_stored_in_column := 'TL_TEXT_'||l_column_prefix||'_ATTRIBUTE'||to_char(v_section_tag);
2628 end if;
2629 xErrLoc := 200;
2630
2631 -- OEX_IP_PORTING
2632 p_stored_in_table := l_stored_in_table;
2633 p_stored_in_column := l_stored_in_column;
2634
2635 END IF;
2636
2637 EXCEPTION
2638 WHEN OTHERS THEN
2639 ROLLBACK;
2640 RAISE_APPLICATION_ERROR(-20000,'Exception at ICX_POR_SCHEMA_UPLOAD.get_stored_in_values(' || xErrLoc || '): ' || SQLERRM);
2641 END get_stored_in_values;
2642
2643 PROCEDURE save_failed_category(p_request_id IN NUMBER,
2644 p_line_number IN NUMBER,
2645 p_action IN VARCHAR2,
2646 p_key IN VARCHAR2,
2647 p_name IN VARCHAR2,
2648 p_type IN VARCHAR2,
2649 p_description IN VARCHAR2,
2650 p_owner_key IN VARCHAR2,
2651 p_owner_name IN VARCHAR2) IS
2652 l_progress VARCHAR2(5) := '100';
2653 BEGIN
2654
2655 --Bug#2729328
2656 --IF p_key IS NOT NULL THEN
2657 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2658 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2659 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_KEY', p_key);
2660 --END IF;
2661
2662 --Bug#2729328
2663 l_progress := '200';
2664
2665 --IF p_name IS NOT NULL THEN
2666 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2667 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2668 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_NAME', p_name);
2669 --END IF;
2670
2671 l_progress := '300';
2672
2673 IF p_type IS NOT NULL THEN
2674 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2675 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2676 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_CATEGORY_TYPE', p_type);
2677 END IF;
2678
2679 l_progress := '400';
2680
2681 IF p_description IS NOT NULL THEN
2682 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2683 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2684 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_DESC', p_description);
2685 END IF;
2686
2687 l_progress := '500';
2688
2689 IF p_owner_key IS NOT NULL THEN
2690 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2691 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2692 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_ATTRIB_OWNER_KEY', p_owner_key);
2693 END IF;
2694
2695 l_progress := '600';
2696
2697 IF p_owner_name IS NOT NULL THEN
2698 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2699 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2700 p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_ATTRIB_OWNER_NAME', p_owner_name);
2701 END IF;
2702
2703 l_progress := '700';
2704 COMMIT;
2705 EXCEPTION
2706 WHEN OTHERS then
2707 RAISE_APPLICATION_ERROR
2708 (-20000, 'Exception at icx_por_track_validate_job_s.save_failed_category(ErrLoc = ' || l_progress ||') ' ||
2709 'SQL Error : ' || SQLERRM);
2710 END save_failed_category;
2711
2712 -- jinwang:
2713 -- add searchresultsvisible, required, multivalues to
2714 -- the parameter set.
2715 PROCEDURE save_failed_descriptor(p_request_id IN NUMBER,
2716 p_line_number IN NUMBER,
2717 p_action IN VARCHAR2,
2718 p_key IN VARCHAR2,
2719 p_name IN VARCHAR2,
2720 p_type IN VARCHAR2,
2721 p_description IN VARCHAR2,
2722 p_owner_key IN VARCHAR2,
2723 p_owner_name IN VARCHAR2,
2724 p_sequence IN VARCHAR2,
2725 p_default_value IN VARCHAR2,
2726 p_searchable IN VARCHAR2,
2727 p_itemdetailvisible IN VARCHAR2,
2728 p_searchresultsvisible IN VARCHAR2,
2729 p_required IN VARCHAR2,
2730 p_multivalue IN VARCHAR2,
2731 p_errortype IN VARCHAR2) IS
2732 l_progress VARCHAR2(5) := '100';
2733 BEGIN
2734
2735 IF p_key IS NOT NULL THEN
2736 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2737 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2738 p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_KEY', p_key);
2739 END IF;
2740
2741 l_progress := '200';
2742
2743 IF p_name IS NOT NULL THEN
2744 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2745 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2746 p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_NAME', p_name);
2747 END IF;
2748
2749 l_progress := '300';
2750
2751 IF p_type IS NOT NULL THEN
2752 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2753 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2754 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_CATEGORY_TYPE', p_type);
2755 END IF;
2756
2757 l_progress := '400';
2758
2759 IF p_description IS NOT NULL THEN
2760 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2761 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2762 p_line_number, p_action, p_errortype, 'ICX_POR_CATEGORY_DESC', p_description);
2763 END IF;
2764
2765 l_progress := '500';
2766
2767 -- Bug 1383537 - Use ICX_POR_CAT_ATTRIB_OWNER_KEY and ICX_POR_CAT_ATTRIB_OWNER_NAME
2768 -- instead of ICX_POR_CAT_ATTRIB_OWNER to avoid unique index violation.
2769 IF p_owner_key IS NOT NULL THEN
2770 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2771 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2772 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_OWNER_KEY',
2773 p_owner_key);
2774 END IF;
2775
2776 l_progress := '600';
2777
2778 IF p_owner_name IS NOT NULL THEN
2779 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2780 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2781 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_OWNER_NAME',
2782 p_owner_name);
2783 END IF;
2784
2785 l_progress := '700';
2786
2787 IF p_sequence IS NOT NULL THEN
2788 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2789 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2790 p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_SEQ', p_sequence);
2791 END IF;
2792
2793 l_progress := '800';
2794
2795 IF p_default_value IS NOT NULL THEN
2796 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2797 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2798 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_DEFAULT',
2799 p_default_value);
2800 END IF;
2801
2802 l_progress := '900';
2803
2804 IF p_searchable IS NOT NULL THEN
2805 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2806 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2807 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_SEARCHABLE',
2808 p_searchable);
2809 END IF;
2810
2811 l_progress := '1000';
2812
2813 IF p_itemdetailvisible IS NOT NULL THEN
2814 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2815 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2816 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTR_DETAILVISIBLE',
2817 p_itemdetailvisible);
2818 END IF;
2819
2820 l_progress := '1100';
2821
2822 IF p_searchresultsvisible IS NOT NULL THEN
2823 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2824 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2825 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_SEARCH_VISIBLE',
2826 p_searchresultsvisible);
2827 END IF;
2828
2829 l_progress := '1200';
2830
2831 --!!!!!CHECK!!!!!
2832 IF p_required IS NOT NULL THEN
2833 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2834 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2835 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_REQUIRED',
2836 p_required);
2837 END IF;
2838
2839 l_progress := '1300';
2840
2841 IF p_multivalue IS NOT NULL THEN
2842 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2843 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2844 p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_MULTIVALUE',
2845 p_multivalue);
2846
2847 END IF;
2848
2849 COMMIT;
2850 EXCEPTION
2851 WHEN OTHERS then
2852 RAISE_APPLICATION_ERROR
2853 (-20000, 'Exception at icx_por_track_validate_job_s.save_failed_descriptor(ErrLoc = ' || l_progress ||') ' ||
2854 'SQL Error : ' || SQLERRM);
2855 END save_failed_descriptor;
2856
2857 PROCEDURE save_failed_hier_relationship(p_request_id IN NUMBER,
2858 p_line_number IN NUMBER,
2859 p_action IN VARCHAR2,
2860 p_parent_key IN VARCHAR2,
2861 p_parent_name IN VARCHAR2,
2862 p_child_key IN VARCHAR2,
2863 p_child_name IN VARCHAR2) IS
2864 l_progress VARCHAR2(5) := '100';
2865 BEGIN
2866
2867 IF p_parent_key IS NOT NULL THEN
2868 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2869 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2870 p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_PARENT_KEY',
2871 p_parent_key);
2872 END IF;
2873
2874 l_progress := '110';
2875
2876 IF p_parent_name IS NOT NULL THEN
2877 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2878 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2879 p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_PARENT_NAME',
2880 p_parent_name);
2881 END IF;
2882
2883 l_progress := '120';
2884
2885 IF p_child_key IS NOT NULL THEN
2886 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2887 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2888 p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_CHILD_KEY',
2889 p_child_key);
2890 END IF;
2891
2892 l_progress := '110';
2893
2894 IF p_child_name IS NOT NULL THEN
2895 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
2896 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
2897 p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_CHILD_NAME',
2898 p_child_name);
2899 END IF;
2900
2901 COMMIT;
2902 EXCEPTION
2903 WHEN OTHERS then
2904 RAISE_APPLICATION_ERROR
2905 (-20000, 'Exception at icx_por_track_validate_job_s.save_failed_hier_relationship(ErrLoc = ' || l_progress ||') ' ||
2906 'SQL Error : ' || SQLERRM);
2907 END save_failed_hier_relationship;
2908
2909 /* this is added for bug 2108372
2910 the procedure itself will do nothing.
2911 it will be called when starting a shema bulk load job.
2912 */
2913 PROCEDURE prepare_job is
2914 BEGIN
2915 null;
2916 END prepare_job;
2917
2918 PROCEDURE inc_schema_change_version(p_category_id IN NUMBER) is
2919 xErrLoc NUMBER := 0;
2920 xAttribId NUMBER := 0;
2921 BEGIN
2922 xErrLoc := 100;
2923 if(p_category_id = 0) then
2924 xAttribId := ROOT_ATTRIB_ID;
2925 else
2926 xAttribId := LOCAL_ATTRIB_ID;
2927 end if;
2928
2929 xErrLoc := 200;
2930
2931 update icx_cat_schema_versions
2932 set version=version+1
2933 where descriptor_set_id=xAttribId;
2934
2935 xErrLoc := 300;
2936
2937 COMMIT;
2938 EXCEPTION
2939 WHEN OTHERS THEN
2940 ROLLBACK;
2941
2942 RAISE_APPLICATION_ERROR(-20000,
2943 'Exception at ICX_POR_SCHEMA_UPLOAD.inc_schema_change_version('
2944 || xErrLoc || '): ' || SQLERRM);
2945 END inc_schema_change_version;
2946
2947 -- Bug#2323821
2948 -- Added new IN parameter line_number and use the same to insert
2949 -- into failed_lines and failed_line_messages instead of 1, as 1 causes
2950 -- not to display this line if there is a failure in ADMIN section also
2951
2952 PROCEDURE fail_root_descriptor_section(p_request_id IN OUT NOCOPY NUMBER,
2953 p_action IN VARCHAR2,
2954 p_line_number IN NUMBER) IS
2955 l_progress VARCHAR2(5) := '100';
2956 BEGIN
2957
2958 INSERT INTO icx_por_failed_lines (job_number,
2959 line_number,
2960 action,
2961 row_type,
2962 descriptor_key,
2963 descriptor_value)
2964 VALUES (p_request_id,
2965 p_line_number,
2966 p_action,
2967 'DESCRIPTOR',
2968 'ICX_POR_ROOT_SECTION',
2969 'root desc section');
2970
2971 l_progress := '200';
2972 -- Bug#2094215
2973 -- Currently we have dont have root descriptor row type.
2974 -- And for all schema errors we display key: value <Message>
2975 -- If we want to display just the message for descriptor row type
2976 -- then you have to have a mismatch in descriptor key between
2977 -- failed line and failed line messages table. Once you have a different
2978 -- page for the schema rejected lines then we wont be displaying the
2979 -- key:value <message> format.
2980 InsertError(p_request_id, 'ICX_POR_ROOT_SECTION1',
2981 'ICX_POR_ROOT_PROFILE_OFF',
2982 p_line_number);
2983
2984 l_progress := '300';
2985
2986 COMMIT;
2987 EXCEPTION
2988 WHEN OTHERS then
2989 RAISE_APPLICATION_ERROR
2990 (-20000, 'Exception at icx_por_track_validate_job_s.fail_root_descriptor_section(ErrLoc = ' || l_progress ||') ' ||
2991 'SQL Error : ' || SQLERRM);
2992 END fail_root_descriptor_section;
2993
2994 --
2995 -- Set the descriptor columns in the items_tlp/ext_items_tlp table to null
2996 -- when the descriptor is deleted
2997 --
2998 PROCEDURE sync_deleted_descriptors
2999 IS
3000 --Bug#3072827
3001 --Removed the rt_descriptor_id from the cursor and add a distinct
3002 --When descriptors are deleted from online, we add CR jobs which process
3003 --sync_deleted_descriptors, so there could be a scenario, when rt_category_id,
3004 --stored_in_table and stored_in_column are same
3005 --with rt_descriptor_id different in icx_cat_deleted_attributes.
3006 --In the above case without distinct, the sync_deleted_descriptors will
3007 --fail with exception 'ORA-00957:duplicate column name'
3008 CURSOR deleted_descriptors_csr IS
3009 SELECT distinct rt_category_id, stored_in_table, stored_in_column
3010 from icx_cat_deleted_attributes order by stored_in_table, rt_category_id;
3011 vRtCategoryIds dbms_sql.number_table;
3012 --Bug#3072827 vRtDescriptorIds dbms_sql.number_table;
3013 vStoredInColumns dbms_sql.varchar2_table;
3014 vStoredInTables dbms_sql.varchar2_table;
3015 xErrLoc NUMBER := 100;
3016 update_tlp_sql_string VARCHAR2(4000) := null;
3017 update_tlp_set_string VARCHAR2(4000) := null;
3018 update_tlp_value_string VARCHAR2(4000) := null;
3019 update_exttlp_sql_string VARCHAR2(4000) := null;
3020 update_exttlp_set_string VARCHAR2(4000) := null;
3021 update_exttlp_value_string VARCHAR2(4000) := null;
3022 numBaseAttribs NUMBER :=0;
3023 numCatAttribs NUMBER :=0;
3024 v_cursor_id NUMBER;
3025 nextCategoryId NUMBER :=0;
3026 result NUMBER;
3027 v_sql varchar2(255);
3028
3029 BEGIN
3030
3031 xErrLoc := 100;
3032 numBaseAttribs := 0;
3033 numCatAttribs := 0;
3034
3035 xErrLoc := 200;
3036 OPEN deleted_descriptors_csr;
3037
3038 LOOP
3039 vRtCategoryIds.DELETE;
3040 --Bug#3072827 vRtDescriptorIds.DELETE;
3041 vStoredInColumns.DELETE;
3042 vStoredInTables.DELETE;
3043
3044 xErrLoc := 300;
3045
3046 FETCH deleted_descriptors_csr BULK COLLECT INTO
3047 vRtCategoryIds, vStoredInTables, vStoredInColumns
3048 LIMIT BATCH_SIZE;
3049
3050 --Bug#3072827
3051 EXIT WHEN vRtCategoryIds.COUNT = 0;
3052
3053 xErrLoc := 400;
3054
3055 FOR i IN 1..vRtCategoryIds.COUNT LOOP
3056
3057 if i < vRtCategoryIds.COUNT then
3058 nextCategoryId := vRtCategoryIds(i+1);
3059 end if;
3060
3061
3062 -- No change in category, continue forming the update statement.
3063 -- Processing Root descriptors.
3064 IF vRtCategoryIds(i) = 0 THEN
3065 if (numBaseAttribs = 0) then
3066 update_tlp_set_string := vStoredInColumns(i);
3067 update_tlp_value_string := ' null';
3068 else
3069 update_tlp_set_string := update_tlp_set_string || ' , '|| vStoredInColumns(i) ;
3070 update_tlp_value_string := update_tlp_value_string || ' , '|| 'null';
3071 end if;
3072 numBaseAttribs := numBaseAttribs+1;
3073 xErrLoc := 500;
3074 ELSE
3075 -- Processing the local descriptors.
3076 -- You are now going to see all the local descriptors(due to order by)
3077
3078 if (numCatAttribs = 0) then
3079 update_exttlp_set_string := vStoredInColumns(i);
3080 update_exttlp_value_string := ' null';
3081 else
3082 update_exttlp_set_string := update_exttlp_set_string || ' , '|| vStoredInColumns(i) ;
3083 update_exttlp_value_string := update_exttlp_value_string || ' , '|| 'null';
3084 end if;
3085 numCatAttribs := numCatAttribs+1;
3086 END IF;
3087
3088 -- Once we have read the category process it...
3089 -- Category changed in the list, means a new category is read, so
3090 -- process the category and its descriptors...
3091 -- process even when end of list is reached..
3092 IF (i = vRtCategoryIds.COUNT OR nextCategoryId <> vRtCategoryIds(i)) THEN
3093 v_cursor_id := DBMS_SQL.open_cursor;
3094
3095 -- Process the category read.
3096 xErrLoc := 700;
3097
3098 IF vRtCategoryIds(i) = 0 THEN
3099 update_tlp_sql_string := 'UPDATE ICX_CAT_ITEMS_TLP SET ';
3100 update_tlp_sql_string := update_tlp_sql_string ||' ('||update_tlp_set_string||' ) = (select ' || update_tlp_value_string|| ' from dual) where primary_category_id = :cat_id';
3101 xErrLoc := 750;
3102 DBMS_SQL.parse(v_cursor_id, update_tlp_sql_string, dbms_sql.native);
3103 -- Reset the values for reading next set of Base attributes for
3104 -- the next category
3105 numBaseAttribs := 0;
3106 update_tlp_set_string := null;
3107 update_tlp_value_string := null;
3108 ELSE
3109 -- Process the category attributes just read for this category
3110 update_exttlp_sql_string := 'UPDATE ICX_CAT_EXT_ITEMS_TLP SET ';
3111 update_exttlp_sql_string := update_exttlp_sql_string||' ('||update_exttlp_set_string||' ) = (select ' || update_exttlp_value_string|| ' from dual) where rt_category_id = :cat_id';
3112 xErrLoc := 750;
3113 DBMS_SQL.parse(v_cursor_id, update_exttlp_sql_string, dbms_sql.native);
3114 -- Reset the values for reading next set of category attributes for
3115 -- the next category
3116 numCatAttribs := 0;
3117 update_exttlp_set_string := null;
3118 update_exttlp_value_string := null;
3119 END IF;
3120
3121 xErrLoc := 800;
3122 DBMS_SQL.bind_variable(v_cursor_id, ':cat_id', vRtCategoryIds(i));
3123 result := DBMS_SQL.execute(v_cursor_id);
3124 DBMS_SQL.close_cursor(v_cursor_id);
3125
3126 END IF;
3127
3128 xErrLoc := 600+i;
3129 END LOOP;
3130
3131 xErrLoc := 1000;
3132
3133 COMMIT;
3134 --Bug#3072827
3135 IF (vRtCategoryIds.COUNT < BATCH_SIZE) THEN
3136 EXIT;
3137 END IF;
3138 END LOOP;
3139 CLOSE deleted_descriptors_csr;
3140
3141 xErrLoc := 1400;
3142
3143 -- do not hardcode "icx." get the schema name dynamically..
3144 v_sql := 'TRUNCATE TABLE ' || ICX_POR_EXT_UTL.getIcxSchema ||'.icx_cat_deleted_attributes';
3145 execute immediate v_sql;
3146
3147 xErrLoc := 1500;
3148
3149 COMMIT;
3150 EXCEPTION
3151 WHEN OTHERS THEN
3152 ROLLBACK;
3153
3154 RAISE_APPLICATION_ERROR(-20000,
3155 'Exception at ICX_POR_SCHEMA_UPLOAD.sync_deleted_descriptors('
3156 || xErrLoc || '): ' || SQLERRM);
3157
3158 END sync_deleted_descriptors;
3159
3160 /** Proc : update_items_for_category
3161 ** Desc : Overloaded method for concurrent program executable.
3162 ** Executable for the Category Rename Concurrent Program.
3163 ** See : update_items_for_category [below]
3164 **/
3165 PROCEDURE update_items_for_category (
3166 errbuf OUT NOCOPY VARCHAR2,
3167 retcode OUT NOCOPY VARCHAR2,
3168 p_category_name IN VARCHAR2,
3169 p_category_id IN NUMBER,
3170 p_language IN VARCHAR2,
3171 p_request_id IN NUMBER DEFAULT -1)
3172 IS
3173 BEGIN
3174 retcode := 0;
3175 errbuf := '';
3176
3177 update_items_for_category(p_category_name, p_category_id,
3178 p_language, p_request_id);
3179 EXCEPTION
3180 WHEN OTHERS THEN
3181 retcode := 2;
3182 errbuf := SQLERRM;
3183 raise;
3184 END update_items_for_category;
3185
3186 /**
3187 ** Proc : update_items_for_category
3188 ** Desc : Update primary_category_name in items_tlp with the category name for a sepcified language.
3189 **/
3190 PROCEDURE update_items_for_category (p_category_name IN VARCHAR2,
3191 p_category_id IN NUMBER,
3192 p_language IN VARCHAR2,
3193 p_request_id IN NUMBER DEFAULT -1)
3194 IS
3195
3196 xErrLoc INTEGER := 0;
3197 xContinue BOOLEAN := TRUE;
3198 xCommitSize INTEGER := 2500;
3199 xLangArray ECM_LANG_ARRAY;
3200 CURSOR translateLangCsr IS
3201 SELECT language
3202 FROM icx_cat_categories_tl
3203 WHERE rt_category_id = p_category_id
3204 and type = 2
3205 and source_lang = p_language
3206 and source_lang <> language
3207 UNION
3208 SELECT p_language FROM DUAL;
3209 BEGIN
3210 --Set the commit size
3211 xErrLoc := 100;
3212 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
3213
3214 OPEN translateLangCsr;
3215 FETCH translateLangCsr BULK COLLECT into xLangArray;
3216 CLOSE translateLangCsr;
3217
3218 xErrLoc := 110;
3219 FOR i in 1..xLangArray.COUNT LOOP
3220 xErrLoc := 110;
3221 xContinue := TRUE;
3222
3223 xErrLoc := 120;
3224 WHILE xContinue LOOP
3225 UPDATE icx_cat_items_tlp
3226 SET primary_category_name = p_category_name,
3227 request_id = p_request_id
3228 WHERE primary_category_id=p_category_id
3229 AND language = xLangArray(i)
3230 AND nvl(request_id, -1) <> p_request_id
3231 AND rownum <= xCommitSize;
3232
3233 xErrLoc := 130;
3234 IF ( SQL%ROWCOUNT < xCommitSize ) THEN
3235 xContinue := FALSE;
3236 END IF;
3237
3238 xErrLoc := 140;
3239 COMMIT;
3240 END LOOP;
3241 END LOOP;
3242 xErrLoc := 150;
3243 COMMIT;
3244
3245 xErrLoc := 160;
3246 populate_ctx_desc_indexes(p_request_id);
3247
3248 EXCEPTION
3249 WHEN OTHERS THEN
3250 ROLLBACK;
3251
3252 RAISE_APPLICATION_ERROR(-20000,
3253 'Exception at ICX_POR_SCHEMA_UPLOAD.update_items_for_category('
3254 || xErrLoc || '): ' || SQLERRM);
3255
3256 END update_items_for_category;
3257
3258 /**
3259 ** Proc : handle_delete_descriptors
3260 ** Desc : Overloaded version. Handles the plsql call required
3261 ** when a descritpor is deleted from ecmanager
3262 **/
3263 PROCEDURE handle_delete_descriptors (
3264 errbuf OUT NOCOPY VARCHAR2,
3265 retcode OUT NOCOPY VARCHAR2,
3266 p_searchable IN NUMBER,
3267 p_rename_category_done IN VARCHAR2,
3268 p_category_name IN VARCHAR2,
3269 p_rt_category_id IN NUMBER,
3270 p_language IN VARCHAR2,
3271 p_request_id IN NUMBER DEFAULT -1)
3272 IS
3273 BEGIN
3274 retcode := 0;
3275 errbuf := '';
3276
3277 handle_delete_descriptors(p_searchable,
3278 p_rename_category_done,
3279 p_category_name,
3280 p_rt_category_id,
3281 p_language,
3282 p_request_id);
3283 EXCEPTION
3284 WHEN OTHERS THEN
3285 retcode := 2;
3286 errbuf := SQLERRM;
3287 raise;
3288
3289 END handle_delete_descriptors;
3290
3291 /**
3292 ** Proc : handle_delete_descriptors
3293 ** Desc : Handles the plsql call required when a descritpor is deleted from ecmanager
3294 **/
3295 PROCEDURE handle_delete_descriptors (p_searchable IN NUMBER,
3296 p_rename_category_done IN VARCHAR2,
3297 p_category_name IN VARCHAR2,
3298 p_rt_category_id IN NUMBER,
3299 p_language IN VARCHAR2,
3300 p_request_id IN NUMBER DEFAULT -1)
3301 IS
3302 xErrLoc INTEGER := 0;
3303 BEGIN
3304
3305 xErrLoc := 100;
3306 sync_deleted_descriptors;
3307
3308 xErrLoc := 120;
3309 IF ( p_rename_category_done = 'Y' ) THEN
3310 update_items_for_category(p_category_name, p_rt_category_id, p_language, p_request_id);
3311 ELSIF ( p_searchable = 1) THEN
3312 populate_ctx_desc_indexes(p_request_id);
3313 END IF;
3314
3315 EXCEPTION
3316 WHEN OTHERS THEN
3317 ROLLBACK;
3318
3319 RAISE_APPLICATION_ERROR(-20000,
3320 'Exception at ICX_POR_SCHEMA_UPLOAD.handle_delete_descriptors('
3321 || xErrLoc || '): ' || SQLERRM);
3322
3323 END handle_delete_descriptors;
3324
3325 END ICX_POR_SCHEMA_UPLOAD;
[Home] [Help]