1 PACKAGE BODY ICX_CAT_SCHEMA_UPLOAD_PVT AS
2 /* $Header: ICXVSULB.pls 120.12 2006/08/03 03:03:21 kaholee noship $*/
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
19 ----------------------------- by sudsubra --------------------
20
21 -- global cursor to select all installed languages
22 -- used in mutliple procedures
23 CURSOR installed_languages_csr IS
24 SELECT language_code
25 FROM fnd_languages
26 WHERE installed_flag IN ('B', 'I');
27
28 -- Procedure the get the nullify values for the different datatypes
29 -- this returns the values for varchar, number and date
30 PROCEDURE get_nullify_values
31 (
32 x_nullify_char OUT NOCOPY VARCHAR2,
33 x_nullify_num OUT NOCOPY NUMBER,
34 x_nullify_date OUT NOCOPY DATE
35 )
36 IS
37 l_err_loc PLS_INTEGER;
38 BEGIN
39 l_err_loc := 100;
40
41 x_nullify_char := PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR;
42 x_nullify_num := PO_PDOI_CONSTANTS.g_NULLIFY_NUM;
43 x_nullify_date := PO_PDOI_CONSTANTS.g_NULLIFY_DATE;
44
45 l_err_loc := 200;
46 EXCEPTION
47 WHEN OTHERS THEN
48 RAISE_APPLICATION_ERROR
49 (-20000,
50 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.get_nullify_values(' ||
51 l_err_loc || '), ' || SQLERRM);
52 END get_nullify_values;
53
54 -- Procedure to validate the descriptor before it gets created
55 -- this method is only called from item load
56 PROCEDURE validate_descriptor_for_create
57 (
58 p_key IN VARCHAR2,
59 p_name IN VARCHAR2,
60 p_type IN VARCHAR2,
61 p_cat_id IN NUMBER,
62 p_language IN VARCHAR2,
63 x_is_valid OUT NOCOPY VARCHAR2,
64 x_error OUT NOCOPY VARCHAR2
65 )
66 IS
67 l_err_loc PLS_INTEGER;
68 l_num_val PLS_INTEGER := 0;
69 l_start_pos PLS_INTEGER;
70 l_end_pos PLS_INTEGER;
71 l_first_zero_pos PLS_INTEGER;
72 BEGIN
73
74 x_is_valid := 'Y';
75
76 l_err_loc := 100;
77
78 BEGIN
79 IF (p_cat_id = 0) THEN
80 -- root descriptor
81 -- check that descriptor key has to be unique across all root and category
82 -- descriptor keys
83 l_err_loc := 110;
84 SELECT 1
85 INTO l_num_val
86 FROM DUAL
87 WHERE EXISTS (SELECT 1
88 FROM icx_cat_attributes_tl
89 WHERE UPPER(key) = UPPER(p_key)
90 AND language = p_language);
91 ELSE
92 l_err_loc := 120;
93 -- category descriptor
94 -- check that descriptor key has to be unique across all root descriptor
95 -- keys and descriptor keys within the category
96 SELECT 1
97 INTO l_num_val
98 FROM DUAL
99 WHERE EXISTS (SELECT 1
100 FROM icx_cat_attributes_tl
101 WHERE UPPER(key) = UPPER(p_key)
102 AND language = p_language
103 AND (rt_category_id = p_cat_id OR
104 rt_category_id = 0));
105 END IF;
106
107 -- found a descriptor, error error out
108 l_err_loc := 150;
109 x_error := 'ICX_CAT_ATTRIBUTE_NOT_UNIQUE';
110 x_is_valid := 'N';
111 EXCEPTION
112 WHEN no_data_found THEN
113 l_err_loc := 170;
114 null;
115 END;
116
117
118 IF (x_is_valid = 'Y') THEN
119 l_err_loc := 200;
120
121 -- now we check for uniqueness of name
122 -- if we are trying to add a root descriptor then name has to be unique
123 -- across all categories
124 -- else it just has to be unique in that category and root category
125 BEGIN
126 IF (p_cat_id = 0) THEN
127 l_err_loc := 220;
128 -- for base descriptor
129 SELECT 1
130 INTO l_num_val
131 FROM DUAL
132 WHERE EXISTS (SELECT 1
133 FROM icx_cat_attributes_tl
134 WHERE UPPER(attribute_name) = UPPER(p_name)
135 AND language = p_language);
136 ELSE
137 l_err_loc := 230;
138 -- for category descriptor
139 SELECT 1
140 INTO l_num_val
141 FROM DUAL
142 WHERE EXISTS (SELECT 1
143 FROM icx_cat_attributes_tl
144 WHERE UPPER(attribute_name) = UPPER(p_name)
145 AND language = p_language
146 AND (rt_category_id = p_cat_id OR
147 rt_category_id = 0));
148 END IF;
149
150 l_err_loc := 250;
151 -- if it found a name then error
152 x_error := 'ICX_CAT_ATTRIBUTE_NOT_UNIQUE';
153 x_is_valid := 'N';
154
155 EXCEPTION
156 WHEN no_data_found THEN
157 -- we are fine
158 l_err_loc := 270;
159 NULL;
160 END;
161 END IF;
162
163 l_err_loc := 300;
164
165 -- now we check to see if the max number of descriptors has been reached
166 -- for this type
167 -- for base this is 100 (other than the seeded ones), for category 50
168 IF (x_is_valid = 'Y') THEN
169 IF (p_cat_id = 0) THEN
170 -- for base
171 l_err_loc := 310;
172 SELECT COUNT(*)
173 INTO l_num_val
174 FROM icx_cat_attributes_tl
175 WHERE rt_category_id = 0
176 AND language = p_language
177 AND to_char(type) = p_type
178 AND attribute_id > 100;
179
180 l_err_loc := 320;
181
182 -- make sure the section_map is fine for creating a new base descriptor
183 IF p_type = 0 THEN
184 l_start_pos := 1;
185 l_end_pos := 100;
186 ELSIF p_type = 1 THEN
187 l_start_pos := 101;
188 l_end_pos := 200;
189 ELSIF p_type = 2 THEN
190 l_start_pos := 201;
191 l_end_pos := 300;
192 END IF;
193
194 l_err_loc := 325;
195
196 SELECT instr(section_map, '0', l_start_pos, 1)
197 INTO l_first_zero_pos
198 FROM icx_cat_categories_tl
199 WHERE rt_category_id = 0
200 AND language = p_language;
201
202 l_err_loc := 330;
203
204 IF (l_num_val >= 100 OR l_first_zero_pos > l_end_pos) THEN
205 l_err_loc := 330;
206 x_error := 'ICX_CAT_BASE_ATT_NUM_EXCEEDED';
207 x_is_valid := 'N';
208 END IF;
209 ELSE
210 -- for category
211 l_err_loc := 340;
212 SELECT COUNT(*)
213 INTO l_num_val
214 FROM icx_cat_attributes_tl
215 WHERE rt_category_id = p_cat_id
216 AND language = p_language
217 AND to_char(type) = p_type;
218
219 l_err_loc := 345;
220
221 -- make sure the section_map is fine for creating a new category descriptor
222 IF p_type = 0 THEN
223 l_start_pos := 1;
224 l_end_pos := 50;
225 ELSIF p_type = 1 THEN
226 l_start_pos := 101;
227 l_end_pos := 150;
228 ELSIF p_type = 2 THEN
229 l_start_pos := 201;
230 l_end_pos := 250;
231 END IF;
232
233 l_err_loc := 350;
234
235 SELECT instr(section_map, '0', l_start_pos, 1)
236 INTO l_first_zero_pos
237 FROM icx_cat_categories_tl
238 WHERE rt_category_id = p_cat_id
239 AND language = p_language;
240
241 l_err_loc := 355;
242
243 IF (l_num_val >= 50 OR l_first_zero_pos > l_end_pos) THEN
244 l_err_loc := 360;
245 x_error := 'ICX_CAT_CAT_ATT_NUM_EXCEED';
246 x_is_valid := 'N';
247 END IF;
248
249 END IF;
250 END IF;
251
252 l_err_loc := 400;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 RAISE_APPLICATION_ERROR
257 (-20000,
258 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.validate_descriptor_for_create(' ||
259 l_err_loc || '), ' || SQLERRM);
260
261 END validate_descriptor_for_create;
262
263 -- procedure to save the failed category into the failed lines table
264 PROCEDURE save_failed_category
265 (
266 p_request_id IN NUMBER,
267 p_line_number IN NUMBER,
268 p_action IN VARCHAR2,
269 p_key IN VARCHAR2,
270 p_name IN VARCHAR2,
271 p_type IN VARCHAR2,
272 p_description IN VARCHAR2
273 )
274 IS
275 l_err_loc PLS_INTEGER;
276 BEGIN
277
278 l_err_loc := 100;
279
280 insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
281 'ICX_CAT_KEY', p_key);
282
283 l_err_loc := 200;
284
285 insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
286 'ICX_CAT_NAME', p_name);
287
288 l_err_loc := 300;
289
290 insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
291 'ICX_CAT_TYPE', p_type);
292
293 l_err_loc := 400;
294
295 insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
296 'ICX_CAT_DESCRIPTION', p_description);
297
298 l_err_loc := 500;
299
300 COMMIT;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 ROLLBACK;
305 RAISE_APPLICATION_ERROR
306 (-20000,
307 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.save_failed_category(' ||
308 l_err_loc || '), ' || SQLERRM);
309 END save_failed_category;
310
311 -- procedure to save the failed descriptor into the failed lines table
312 PROCEDURE save_failed_descriptor
313 (
314 p_request_id IN NUMBER,
315 p_line_number IN NUMBER,
316 p_action IN VARCHAR2,
317 p_key IN VARCHAR2,
318 p_name IN VARCHAR2,
319 p_type IN VARCHAR2,
320 p_description IN VARCHAR2,
321 p_owner_key IN VARCHAR2,
322 p_owner_name IN VARCHAR2,
323 p_sequence IN VARCHAR2,
324 p_searchable IN VARCHAR2,
325 p_item_detail_visible IN VARCHAR2,
326 p_search_results_visible IN VARCHAR2
327 )
328 IS
329 l_err_loc PLS_INTEGER;
330 BEGIN
331
332 l_err_loc := 100;
333
334 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
335 'ICX_CAT_KEY', p_key);
336
337 l_err_loc := 200;
338
339 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
340 'ICX_CAT_NAME', p_name);
341
342 l_err_loc := 300;
343
344 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
345 'ICX_CAT_TYPE', p_type);
346
347 l_err_loc := 400;
348
349 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
350 'ICX_CAT_DESCRIPTION', p_description);
351
352 l_err_loc := 500;
353
354 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
355 'ICX_CAT_OWNER_KEY', p_owner_key);
356
357 l_err_loc := 600;
358
359 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
360 'ICX_CAT_OWNER_NAME', p_owner_name);
361
362 l_err_loc := 700;
363
364 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
365 'ICX_CAT_SEQUENCE', p_sequence);
366
367 l_err_loc := 800;
368
369 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
370 'ICX_CAT_SEARCHABLE', p_searchable);
371
372 l_err_loc := 900;
373
374 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
375 'ICX_CAT_ITEM_DETAIL_VISIBLE', p_item_detail_visible);
376
377 l_err_loc := 1000;
378
379 insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
380 'ICX_CAT_SEARCH_RESULTS_VISIBLE', p_search_results_visible);
381
382 l_err_loc := 1100;
383
384 COMMIT;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 ROLLBACK;
389 RAISE_APPLICATION_ERROR
390 (-20000,
391 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.save_failed_descriptor(' ||
392 l_err_loc || '), ' || SQLERRM);
393 END save_failed_descriptor;
394
395 -- procedure to save the failed relationships into the failed lines table
396 PROCEDURE save_failed_relationship
397 (
398 p_request_id IN NUMBER,
399 p_line_number IN NUMBER,
400 p_action IN VARCHAR2,
401 p_parent_key IN VARCHAR2,
402 p_parent_name IN VARCHAR2,
403 p_child_key IN VARCHAR2,
404 p_child_name IN VARCHAR2
405 )
406 IS
407 l_err_loc PLS_INTEGER;
408 BEGIN
409
410 l_err_loc := 100;
411
412 insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
413 'ICX_CAT_PARENT_KEY', p_parent_key);
414
415 l_err_loc := 200;
416
417 insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
418 'ICX_CAT_PARENT_NAME', p_parent_name);
419
420 l_err_loc := 300;
421
422 insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
423 'ICX_CAT_CHILD_KEY', p_child_key);
424
425 l_err_loc := 400;
426
427 insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
428 'ICX_CAT_CHILD_NAME', p_child_name);
429
430 l_err_loc := 500;
431
432 COMMIT;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 ROLLBACK;
437 RAISE_APPLICATION_ERROR
438 (-20000,
439 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.save_failed_relationship(' ||
440 l_err_loc || '), ' || SQLERRM);
441 END save_failed_relationship;
442
443 -- inserts a row into the failed lines table
444 PROCEDURE insert_failed_line
445 (
446 p_request_id IN NUMBER,
447 p_line_number IN NUMBER,
448 p_action IN VARCHAR2,
449 p_row_type IN VARCHAR2,
450 p_descriptor_key IN VARCHAR2,
451 p_descriptor_value IN VARCHAR2
452 )
453 IS
454 l_err_loc PLS_INTEGER;
455 BEGIN
456 l_err_loc := 100;
457
458 INSERT INTO icx_por_failed_lines
459 (job_number, line_number, action, row_type, descriptor_key, descriptor_value,
460 request_id, program_id, program_application_id, program_login_id)
461 VALUES (p_request_id, p_line_number, p_action, p_row_type, p_descriptor_key,
462 p_descriptor_value, p_request_id, fnd_global.conc_program_id,
463 fnd_global.prog_appl_id, fnd_global.conc_login_id);
464
465 l_err_loc := 200;
466 COMMIT;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 ROLLBACK;
471 RAISE_APPLICATION_ERROR
472 (-20000,
473 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_line(' ||
474 l_err_loc || '), ' || SQLERRM);
475 END insert_failed_line;
476
477 -- inserts a row into the failed messages table
478 PROCEDURE insert_failed_message
479 (
480 p_request_id IN NUMBER,
481 p_descriptor_key IN VARCHAR2,
482 p_message_name IN VARCHAR2,
483 p_line_number IN NUMBER
484 )
485 IS
486 l_err_loc PLS_INTEGER;
487 BEGIN
488 l_err_loc := 100;
489
490 INSERT INTO icx_por_failed_line_messages
491 (job_number, descriptor_key, message_name, line_number, request_id, program_id,
492 program_application_id, program_login_id)
493 VALUES (p_request_id, p_descriptor_key, p_message_name, p_line_number, p_request_id,
494 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
495
496 l_err_loc := 200;
497 COMMIT;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 ROLLBACK;
502 RAISE_APPLICATION_ERROR
503 (-20000,
504 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_message(' ||
505 l_err_loc || '), ' || SQLERRM);
506 END insert_failed_message;
507
508
509 -- procedure to create a category
510 -- assumes that the parameters are valid
511 -- called from schema load
512 PROCEDURE create_category
513 (
514 x_category_id OUT NOCOPY NUMBER,
515 p_key IN VARCHAR2,
516 p_name IN VARCHAR2,
517 p_description IN VARCHAR2,
518 p_type IN NUMBER,
519 p_language IN VARCHAR2,
520 p_request_id IN NUMBER,
521 p_user_id IN NUMBER,
522 p_login_id IN NUMBER
523 )
524 IS
525 l_err_loc PLS_INTEGER;
526 BEGIN
527
528 l_err_loc := 100;
529
530 -- xxx why is the sequence icx_por_categoryid
531 SELECT icx_por_categoryid.nextval
532 INTO x_category_id
533 FROM dual;
534
535 l_err_loc := 200;
536
537 FOR language_row IN installed_languages_csr LOOP
538
539 l_err_loc := 300;
540
541 INSERT INTO icx_cat_categories_tl
542 (rt_category_id, language, source_lang, category_name, description, type,
543 key, upper_key, upper_category_name, request_id, rebuild_flag, section_map,
544 created_by, creation_date, last_updated_by, last_update_date, last_update_login,
545 program_id, program_application_id, program_login_id)
546 VALUES (x_category_id, language_row.language_code, p_language, p_name,
547 p_description, p_type, p_key, upper(p_key), upper(p_name), p_request_id,
548 'N', lpad('0', '300', '0'), p_user_id, sysdate, p_user_id, sysdate, p_login_id,
549 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
550
551 END LOOP;
552
553 l_err_loc := 400;
554
555 COMMIT;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 ROLLBACK;
560 RAISE_APPLICATION_ERROR
561 (-20000,
562 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.create_category(' ||
563 l_err_loc || '), ' || SQLERRM);
564 END create_category;
565
566 -- procedure to update a category
567 -- assumes that the parameters are valid
568 -- called from schema load
569 PROCEDURE update_category
570 (
571 p_category_id IN NUMBER,
572 p_language IN VARCHAR2,
573 p_name IN VARCHAR2,
574 p_description IN VARCHAR2,
575 p_type IN NUMBER,
576 p_request_id IN NUMBER,
577 p_user_id IN NUMBER,
578 p_login_id IN NUMBER
579 )
580 IS
581 l_err_loc PLS_INTEGER;
582
583 l_lang_table DBMS_SQL.VARCHAR2_TABLE;
584
585 CURSOR translate_category_lang_csr
586 IS
587 SELECT language
588 FROM icx_cat_categories_tl
589 WHERE rt_category_id = p_category_id
590 and type = p_type
591 and source_lang = p_language
592 and source_lang <> language
593 UNION
594 SELECT p_language FROM dual;
595
596 BEGIN
597
598 l_err_loc := 100;
599
600 -- bulk fetch all languages to update the category for
601 OPEN translate_category_lang_csr;
602 FETCH translate_category_lang_csr BULK COLLECT into l_lang_table;
603 CLOSE translate_category_lang_csr;
604
605 l_err_loc := 200;
606
607 -- we update category name and description and source lang
608 -- only for those langauages that were sourced from current lang
609 -- and have not themselves been translated
610 -- only description can be specified as #DEL
611 FORALL i in 1..l_lang_table.COUNT
612 UPDATE icx_cat_categories_tl
613 SET category_name = nvl(p_name, category_name),
614 upper_category_name = nvl(upper(p_name), upper_category_name),
615 description = decode(p_description, '#DEL', null, null, description, p_description),
616 source_lang = p_language,
617 last_updated_by = p_user_id,
618 last_update_date = sysdate,
619 last_update_login = p_login_id,
620 request_id = p_request_id,
621 rebuild_flag = decode(p_name, category_name, rebuild_flag, null, rebuild_flag, 'Y'),
622 program_id = fnd_global.conc_program_id,
623 program_application_id = fnd_global.prog_appl_id,
624 program_login_id = fnd_global.conc_login_id
625 WHERE rt_category_id = p_category_id
626 AND language = l_lang_table(i);
627
628 l_err_loc := 300;
629
630 -- we need to update the category name in icx_cat_items_ctx_hdrs_tlp
631 -- if it is an item category
632
633 IF (p_type = 2) THEN
634 FORALL i in 1..l_lang_table.COUNT
635 UPDATE icx_cat_items_ctx_hdrs_tlp
636 SET ip_category_name = p_name
637 WHERE ip_category_id = p_category_id
638 AND language = l_lang_table(i);
639 END IF;
640
641 l_err_loc := 400;
642
643 COMMIT;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 ROLLBACK;
648 RAISE_APPLICATION_ERROR
649 (-20000,
650 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_category(' ||
651 l_err_loc || '), ' || SQLERRM);
652 END update_category;
653
654
655 -- procedure to delete a category
656 -- assumes that the parameters are valid
657 -- called from schema load
658 PROCEDURE delete_category
659 (
660 p_category_id IN NUMBER,
661 p_language IN VARCHAR2,
662 p_request_id IN NUMBER,
663 p_user_id IN NUMBER,
664 p_login_id IN NUMBER
665 )
666 IS
667 l_err_loc PLS_INTEGER;
668
669 l_child_cat_table DBMS_SQL.NUMBER_TABLE;
670 l_child_cat_type_table DBMS_SQL.VARCHAR2_TABLE;
671 l_parent_cat_count_table DBMS_SQL.NUMBER_TABLE;
672
673 CURSOR category_children_csr
674 IS
675 SELECT browse.child_category_id, cat.type, count(*)
676 FROM icx_cat_browse_trees browse, icx_cat_categories_tl cat
677 WHERE browse.child_category_id IN
678 (SELECT child_category_id
679 FROM icx_cat_browse_trees
680 WHERE parent_category_id = p_category_id)
681 AND browse.child_category_id = cat.rt_category_id
682 AND language = p_language
683 GROUP BY browse.child_category_id, cat.type;
684 BEGIN
685
686 l_err_loc := 100;
687 -- first check to see if it is a root category
688 -- do not allow delete to root category
689 IF (p_category_id > 0) THEN
690
691 l_err_loc := 200;
692 -- we check if category can be deleted
693 -- this is checked in validate, but we do it here just for sanity
694 -- and to prevent data corruption
695 IF (can_category_be_deleted(p_category_id) = 0) THEN
696
697 l_err_loc := 300;
698 -- now we can safely begin the delete category process
699
700 -- first delete the category itself
701 DELETE FROM icx_cat_categories_tl
702 WHERE rt_category_id = p_category_id;
703
704 l_err_loc := 310;
705
706 -- then we delete the attributes for that category
707 DELETE FROM icx_cat_attributes_tl
708 WHERE rt_category_id = p_category_id;
709
710 l_err_loc := 320;
711
712 --delete the mappings for the item category.
713 DELETE FROM icx_por_category_order_map
714 WHERE rt_category_id = p_category_id;
715
716 l_err_loc := 320;
717
718 -- xxx make sure we can use category id here
719 -- and do not need to use key
720 DELETE FROM icx_por_category_data_sources
721 WHERE rt_category_id = p_category_id;
722
723 -- now delete the link from this category to its parents
724 DELETE FROM icx_cat_browse_trees
725 WHERE child_category_id = p_category_id;
726
727 -- bulk fetch all children of this category
728 OPEN category_children_csr;
729 FETCH category_children_csr
730 BULK COLLECT INTO l_child_cat_table, l_child_cat_type_table, l_parent_cat_count_table;
731 CLOSE category_children_csr;
732
733 -- now delete all the links from this category to its children
734 FORALL i in 1..l_child_cat_table.COUNT
735 DELETE FROM icx_cat_browse_trees
736 WHERE parent_category_id = p_category_id
737 AND child_category_id = l_child_cat_table(i);
738
739 -- now for all those children of this category that we are deleting
740 -- if the type is 1 and the child category has no other parents
741 -- i.e. parent count is 1 then we put the child under the root
742 -- xxx why are we using 0 instead of ids? for this
743 -- we were doing this in 11.5.10 now changed
744 FORALL i in 1..l_child_cat_table.COUNT
745 INSERT INTO icx_cat_browse_trees
746 (parent_category_id, child_category_id, created_by, creation_date,
747 last_updated_by, last_update_date, last_update_login, request_id,
748 program_id, program_application_id, program_login_id)
749 SELECT 0, l_child_cat_table(i), p_user_id, sysdate, p_user_id, sysdate, p_login_id, p_request_id,
750 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
751 FROM dual
752 WHERE l_child_cat_type_table(i) = 1
753 AND l_parent_cat_count_table(i) = 1;
754
755 END IF; -- can_category_be...
756 END IF; -- p_category_id > 0
757
758 l_err_loc := 400;
759
760 COMMIT;
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 ROLLBACK;
765 RAISE_APPLICATION_ERROR
766 (-20000,
767 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_category(' ||
768 l_err_loc || '), ' || SQLERRM);
769 END delete_category;
770
771 -- procedure to validate a category
772 PROCEDURE validate_category
773 (
774 p_key IN VARCHAR2,
775 p_name IN VARCHAR2,
776 p_type IN VARCHAR2,
777 p_language IN VARCHAR2,
778 p_request_id IN NUMBER,
779 p_line_number IN NUMBER,
780 p_user_action IN VARCHAR2,
781 x_is_valid OUT NOCOPY VARCHAR2,
782 x_system_action OUT NOCOPY VARCHAR2,
783 x_category_id OUT NOCOPY NUMBER,
784 x_converted_type OUT NOCOPY VARCHAR2
785 )
786 IS
787 l_err_loc PLS_INTEGER;
788 l_current_type NUMBER;
789 l_count PLS_INTEGER := 0;
790 BEGIN
791
792 l_err_loc := 100;
793
794 x_is_valid := 'Y';
795
796 -- first thing is key should not be null
797 IF (p_key is null) THEN
798 -- key is null, error regardless of action
799 l_err_loc := 110;
800 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_KEY_REQUIRED',
801 p_line_number);
802 x_is_valid := 'N';
803 ELSE
804
805 -- key is provided
806
807 -- now we try to derive system action and at the same time get the
808 -- category id, type for existing category for update and delete
809 -- possible user actions can be only 'SYNC' and 'DELETE'
810 -- this is validated in the DTD
811 -- we need to derive system action only for SYNC
812 l_err_loc := 200;
813
814 BEGIN
815
816 SELECT rt_category_id, type
817 INTO x_category_id, l_current_type
818 FROM icx_cat_categories_tl
819 WHERE upper_key = upper(p_key)
820 AND language = p_language
821 AND rownum = 1;
822
823 l_err_loc := 210;
824
825 -- if this is successful that means we found the category
826 -- if it was sync it is an UPDATE, else we are ok for delete
827 IF (p_user_action = 'SYNC') THEN
828 l_err_loc := 230;
829 x_system_action := 'UPDATE';
830 ELSE
831 l_err_loc := 240;
832 x_system_action := 'DELETE';
833 END IF;
834
835 EXCEPTION
836 WHEN no_data_found THEN
837 l_err_loc := 250;
838
839 -- this means we did not find a category
840 -- now if this is DELETE then it is an error
841 -- if it was sync it is an ADD
842 IF (p_user_action = 'DELETE') THEN
843 l_err_loc := 260;
844 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_DOES_NOT_EXIST',
845 p_line_number);
846 x_is_valid := 'N';
847 x_system_action := p_user_action;
848 ELSIF (p_user_action = 'SYNC') THEN
849 l_err_loc := 270;
850 x_system_action := 'ADD';
851 END IF;
852 END;
853 END IF;
854
855 l_err_loc := 300;
856
857 -- next we will validate type for add and update
858 IF (x_is_valid = 'Y') THEN
859
860 l_err_loc := 310;
861
862
863
864 -- next we validate type since this is similar for add and update
865 IF (x_system_action IN ('ADD', 'UPDATE')) THEN
866 -- we default type to 2 if add and not provided
867 IF (x_system_action = 'ADD' AND p_type is null) THEN
868 l_err_loc := 320;
869 x_converted_type := '2';
870 ELSIF (p_type IN ('0', '1', '2')) THEN
871 l_err_loc := 340;
872 x_converted_type := p_type;
873 ELSIF (p_type is not null) THEN
874 l_err_loc := 350;
875
876 BEGIN
877 SELECT lookup_code INTO x_converted_type
878 FROM fnd_lookup_values
879 WHERE lookup_type = 'ICX_CAT_TYPE'
880 AND UPPER(meaning) = UPPER(p_type)
881 AND language = p_language;
882
883 EXCEPTION
884 WHEN no_data_found THEN
885 l_err_loc := 360;
886 insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_INVALID_CAT_TYPE',
887 p_line_number);
888 x_is_valid := 'N';
889 END;
890 END IF;
891 END IF;
892 END IF;
893
894 -- now we have found system action, now we validate based on it
895 -- we continue validation only if valid
896 IF (x_is_valid = 'Y') THEN
897 l_err_loc := 400;
898 IF (x_system_action = 'ADD') THEN
899
900 l_err_loc := 500;
901
902 -- for add we do the following validations
903 -- 1. The name must be non-null and unique
904
905 IF (p_name IS NULL) THEN
906 l_err_loc := 510;
907 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CATEGORY_NAME_REQUIRED',
908 p_line_number);
909 x_is_valid := 'N';
910 ELSE
911 l_err_loc := 520;
912 SELECT count(1) INTO l_count
913 FROM icx_cat_categories_tl
914 WHERE upper_category_name = UPPER(p_name);
915
916 l_err_loc := 530;
917
918 IF (l_count > 0) THEN
919 l_err_loc := 540;
920 insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_ADD',
921 p_line_number);
922 x_is_valid := 'N';
923 END IF;
924 END IF;
925
926 ELSIF (x_system_action = 'UPDATE') THEN
927
928 l_err_loc := 600;
929
930 -- for update we do the following validations
931 -- 1. Cannot update root category
932 IF (x_category_id = 0) THEN
933 l_err_loc := 630;
934 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_UPD_ROOT_CAT',
935 p_line_number);
936 x_is_valid := 'N';
937 END IF;
938 -- 2. The name if provided must be unique among other categories
939
940 IF (p_name IS NOT NULL) THEN
941 l_err_loc := 610;
942 SELECT count(1) INTO l_count
943 FROM icx_cat_categories_tl
944 WHERE upper_category_name = UPPER(p_name)
945 and rt_category_id <> x_category_id;
946
947 l_err_loc := 620;
948
949 IF (l_count > 0) THEN
950 l_err_loc := 630;
951 insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_UPD',
952 p_line_number);
953 x_is_valid := 'N';
954 END IF;
955 END IF;
956
957 l_err_loc := 630;
958
959 -- 2. Type must match the current type
960 IF (x_converted_type IS NOT NULL AND x_converted_type <> l_current_type) THEN
961 l_err_loc := 630;
962 insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_CAT_TYPE',
963 p_line_number);
964 x_is_valid := 'N';
965 END IF;
966
967 ELSIF (x_system_action = 'DELETE') THEN
968
969 l_err_loc := 700;
970 -- for delete we do the following validations
971 -- 1. Cannot update root category
972 IF (x_category_id = 0) THEN
973 l_err_loc := 630;
974 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_ROOT_CAT',
975 p_line_number);
976 x_is_valid := 'N';
977 END IF;
978 -- 2. The category must not be referenced on any documents or master items
979 -- Here we ignore name and type since we just match on key
980 IF (can_category_be_deleted(x_category_id) = 1) THEN
981 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_HAS_ITEMS',
982 p_line_number);
983 x_is_valid := 'N';
984 END IF;
985
986 END IF;
987
988 END IF;
989
990 l_err_loc := 800;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 RAISE_APPLICATION_ERROR
995 (-20000,
996 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.validate_category(' ||
997 l_err_loc || '), ' || SQLERRM);
998 END validate_category;
999
1000
1001
1002 -- function to check if category can be deleted
1003 -- a category can be deleted if it is not referenced on any documents and master items
1004 FUNCTION can_category_be_deleted
1005 (
1006 p_ip_category_id IN NUMBER
1007 )
1008 RETURN NUMBER
1009 IS
1010 l_err_loc PLS_INTEGER;
1011 l_category_referenced PLS_INTEGER;
1012 BEGIN
1013 l_err_loc := 100;
1014
1015 -- assume not referenced at first
1016 l_category_referenced := 0;
1017
1018 BEGIN
1019 SELECT 1
1020 INTO l_category_referenced
1021 FROM dual
1022 WHERE EXISTS (SELECT 1
1023 FROM icx_cat_items_ctx_hdrs_tlp
1024 WHERE ip_category_id = p_ip_category_id);
1025
1026 l_err_loc := 150;
1027
1028 EXCEPTION
1029 WHEN no_data_found THEN
1030 NULL;
1031 END;
1032
1033 l_err_loc := 200;
1034
1035 -- now check the po tables
1036 -- first the transaction table
1037
1038 BEGIN
1039 SELECT 1
1040 INTO l_category_referenced
1041 FROM dual
1042 WHERE EXISTS (SELECT 1
1043 FROM po_lines_all
1044 WHERE ip_category_id = p_ip_category_id);
1045
1046 l_err_loc := 250;
1047
1048 EXCEPTION
1049 WHEN no_data_found THEN
1050 NULL;
1051 END;
1052
1053 l_err_loc := 300;
1054
1055 -- then the draft table
1056
1057 BEGIN
1058 SELECT 1
1059 INTO l_category_referenced
1060 FROM dual
1061 WHERE EXISTS (SELECT 1
1062 FROM po_lines_draft_all
1063 WHERE ip_category_id = p_ip_category_id);
1064
1065 l_err_loc := 350;
1066
1067 EXCEPTION
1068 WHEN no_data_found THEN
1069 NULL;
1070 END;
1071
1072 -- now we check the sourcing tables
1073 l_err_loc := 400;
1074
1075 BEGIN
1076 SELECT 1
1077 INTO l_category_referenced
1078 FROM dual
1079 WHERE EXISTS (SELECT 1
1080 FROM pon_auction_item_prices_all
1081 WHERE ip_category_id = p_ip_category_id);
1082
1083 l_err_loc := 450;
1084
1085 EXCEPTION
1086 WHEN no_data_found THEN
1087 NULL;
1088 END;
1089
1090 l_err_loc := 500;
1091
1092 BEGIN
1093 SELECT 1
1094 INTO l_category_referenced
1095 FROM dual
1096 WHERE EXISTS (SELECT 1
1097 FROM pon_item_prices_interface
1098 WHERE ip_category_id = p_ip_category_id);
1099
1100 l_err_loc := 550;
1101
1102 EXCEPTION
1103 WHEN no_data_found THEN
1104 NULL;
1105 END;
1106
1107 l_err_loc := 600;
1108
1109 BEGIN
1110 SELECT 1
1111 INTO l_category_referenced
1112 FROM dual
1113 WHERE EXISTS (SELECT 1
1114 FROM pon_auc_items_interface
1115 WHERE ip_category_id = p_ip_category_id);
1116
1117 l_err_loc := 650;
1118
1119 EXCEPTION
1120 WHEN no_data_found THEN
1121 NULL;
1122 END;
1123
1124 -- now we also need to check for category name in the sourcing interface table
1125
1126 l_err_loc := 700;
1127
1128 BEGIN
1129 SELECT 1
1130 INTO l_category_referenced
1131 FROM dual
1132 WHERE EXISTS (SELECT 1
1133 FROM pon_item_prices_interface
1134 WHERE ip_category_name IN (SELECT category_name
1135 FROM icx_cat_categories_tl
1136 WHERE rt_category_id = p_ip_category_id));
1137
1138 l_err_loc := 750;
1139
1140 EXCEPTION
1141 WHEN no_data_found THEN
1142 NULL;
1143 END;
1144
1145 l_err_loc := 800;
1146
1147 RETURN l_category_referenced;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 RAISE_APPLICATION_ERROR
1151 (-20000,
1152 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_category_be_deleted(' ||
1153 l_err_loc || '), ' || SQLERRM);
1154 END can_category_be_deleted;
1155
1156
1157 -- procedure to add a relationship
1158 -- assumes that the categories to be related are valid
1159 PROCEDURE add_relationship
1160 (
1161 p_parent_id IN NUMBER,
1162 p_child_id IN NUMBER,
1163 p_user_id IN NUMBER,
1164 p_login_id IN NUMBER,
1165 p_request_id IN NUMBER,
1166 p_line_number IN NUMBER,
1167 p_action IN VARCHAR2
1168 )
1169 IS
1170 l_err_loc PLS_INTEGER;
1171 l_type NUMBER;
1172 BEGIN
1173
1174 l_err_loc := 100;
1175
1176 SELECT type
1177 INTO l_type
1178 FROM icx_cat_categories_tl
1179 WHERE rt_category_id = p_parent_id
1180 AND rownum = 1;
1181
1182 l_err_loc := 110;
1183
1184 -- for sanity only proceed if parent is root or parent is of type 1
1185 IF (p_parent_id = 0 OR l_type = 1) THEN
1186
1187 l_err_loc := 120;
1188
1189
1190 -- now we insert the relationship, if it does not already exist
1191 INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
1192 created_by, creation_date, last_updated_by, last_update_date, last_update_login,
1193 request_id, program_id, program_application_id, program_login_id)
1194 SELECT p_parent_id, p_child_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
1195 p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
1196 FROM dual
1197 WHERE NOT EXISTS (SELECT 1
1198 FROM icx_cat_browse_trees
1199 WHERE parent_category_id = p_parent_id
1200 AND child_category_id = p_child_id);
1201
1202 l_err_loc := 130;
1203
1204 IF (p_parent_id <> 0) THEN
1205
1206 l_err_loc := 140;
1207 -- if parent is not root we need to do more stuff
1208 -- first we remove any link from the child to root if it exists
1209 DELETE FROM icx_cat_browse_trees
1210 WHERE parent_category_id = 0
1211 AND child_category_id = p_child_id;
1212
1213 l_err_loc := 150;
1214
1215 -- if the parent is does not have a parent
1216 -- then we need to attach it to the root
1217 INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
1218 created_by, creation_date, last_updated_by, last_update_date, last_update_login,
1219 request_id, program_id, program_application_id, program_login_id)
1220 SELECT 0, p_parent_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
1221 p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
1222 FROM dual
1223 WHERE NOT EXISTS (SELECT 1
1224 FROM icx_cat_browse_trees
1225 WHERE child_category_id = p_parent_id);
1226
1227 l_err_loc := 160;
1228
1229 END IF;
1230
1231 l_err_loc := 170;
1232
1233 END IF;
1234
1235 l_err_loc := 180;
1236
1237 COMMIT;
1238
1239 l_err_loc := 190;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 ROLLBACK;
1244 RAISE_APPLICATION_ERROR
1245 (-20000,
1246 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.add_relationship(' ||
1247 l_err_loc || '), ' || SQLERRM);
1248 END add_relationship;
1249
1250
1251 -- procedure to delete a relationship
1252 -- assumes that the categories to be related are valid
1253 PROCEDURE delete_relationship
1254 (
1255 p_parent_id IN NUMBER,
1256 p_child_id IN NUMBER,
1257 p_request_id IN NUMBER,
1258 p_line_number IN NUMBER,
1259 p_action IN VARCHAR2
1260 )
1261 IS
1262 l_err_loc PLS_INTEGER;
1263 l_type NUMBER;
1264 BEGIN
1265
1266 l_err_loc := 100;
1267
1268 -- xxx should we add a link from child to root if no parent exists
1269
1270 SELECT type
1271 INTO l_type
1272 FROM icx_cat_categories_tl
1273 WHERE rt_category_id = p_parent_id
1274 AND rownum = 1;
1275
1276 l_err_loc := 110;
1277
1278 -- for sanity only proceed if parent is root or parent is of type 1
1279 IF (p_parent_id = 0 OR l_type = 1) THEN
1280
1281 l_err_loc := 120;
1282 DELETE FROM icx_cat_browse_trees
1283 WHERE parent_category_id = p_parent_id
1284 AND child_category_id = p_child_id;
1285
1286 l_err_loc := 130;
1287 END IF;
1288
1289 l_err_loc := 140;
1290
1291 COMMIT;
1292
1293 l_err_loc := 150;
1294
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 ROLLBACK;
1298 RAISE_APPLICATION_ERROR
1299 (-20000,
1300 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_relationship(' ||
1301 l_err_loc || '), ' || SQLERRM);
1302 END delete_relationship;
1303
1304
1305 -- procedure to delete a relationship
1306 -- assumes that the categories to be related are valid
1307 PROCEDURE validate_relationship
1308 (
1309 p_parent_key IN VARCHAR2,
1310 p_parent_name IN VARCHAR2,
1311 p_child_key IN VARCHAR2,
1312 p_child_name IN VARCHAR2,
1313 p_language IN VARCHAR2,
1314 p_request_id IN NUMBER,
1315 p_line_number IN NUMBER,
1316 p_user_action IN VARCHAR2,
1317 x_is_valid OUT NOCOPY VARCHAR2,
1318 x_system_action OUT NOCOPY VARCHAR2,
1319 x_parent_id OUT NOCOPY NUMBER,
1320 x_child_id OUT NOCOPY NUMBER
1321 )
1322 IS
1323 l_err_loc PLS_INTEGER;
1324 l_parent_type NUMBER;
1325 l_parent_id_from_name NUMBER;
1326 l_child_id_from_name NUMBER;
1327 l_count PLS_INTEGER;
1328 BEGIN
1329
1330 l_err_loc := 100;
1331
1332 x_is_valid := 'Y';
1333
1334 -- user action has to be sync or delete
1335 -- this is validated by the DTD
1336
1337 -- first get system action
1338 IF (p_user_action = 'SYNC') THEN
1339 -- sync is basically add
1340 l_err_loc := 110;
1341 x_system_action := 'ADD';
1342 ELSE
1343 l_err_loc := 120;
1344 x_system_action := 'DELETE';
1345 END IF;
1346
1347 l_err_loc := 130;
1348
1349 -- now first we try to get the parent category from key and name
1350 IF (p_parent_key IS NULL AND p_parent_name IS NULL) THEN
1351
1352 l_err_loc := 140;
1353 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY', 'ICX_CAT_PARENT_KEY_NAME_REQD',
1354 p_line_number);
1355 x_is_valid := 'N';
1356
1357 END IF;
1358
1359 l_err_loc := 150;
1360
1361 IF (x_is_valid = 'Y') THEN
1362
1363 l_err_loc := 160;
1364
1365 IF (p_parent_key IS NOT NULL) THEN
1366
1367 l_err_loc := 170;
1368 -- there is a key, try to get the category from the key
1369
1370 BEGIN
1371
1372 SELECT rt_category_id, type
1373 INTO x_parent_id, l_parent_type
1374 FROM icx_cat_categories_tl
1375 WHERE upper_key = UPPER(p_parent_key)
1376 AND language = p_language;
1377
1378 l_err_loc := 180;
1379
1380 EXCEPTION
1381 WHEN no_data_found THEN
1382 l_err_loc := 190;
1383 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
1384 'ICX_CAT_PARENT_KEY_NOT_EXIST', p_line_number);
1385 x_is_valid := 'N';
1386 END;
1387
1388 END IF;
1389
1390 l_err_loc := 200;
1391
1392 IF (p_parent_name IS NOT NULL) THEN
1393
1394 l_err_loc := 210;
1395 -- there is a name, try to get the category from the name
1396
1397 BEGIN
1398
1399 SELECT rt_category_id, type
1400 INTO l_parent_id_from_name, l_parent_type
1401 FROM icx_cat_categories_tl
1402 WHERE upper_category_name = UPPER(p_parent_name)
1403 AND language = p_language;
1404
1405 l_err_loc := 220;
1406
1407 EXCEPTION
1408 WHEN no_data_found THEN
1409 l_err_loc := 230;
1410 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
1411 'ICX_CAT_PARENT_NAME_NOT_EXIST', p_line_number);
1412 x_is_valid := 'N';
1413 END;
1414
1415 END IF;
1416
1417 l_err_loc := 240;
1418
1419 -- now if both were provided then we need to compare the categories they got
1420 IF (x_parent_id IS NOT NULL) THEN
1421 IF (l_parent_id_from_name IS NOT NULL AND x_parent_id <> l_parent_id_from_name) THEN
1422 l_err_loc := 250;
1423 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
1424 'ICX_CAT_PARENT_KEY_NAME_DIFF', p_line_number);
1425 x_is_valid := 'N';
1426 END IF;
1427 ELSE
1428 x_parent_id := l_parent_id_from_name;
1429 END IF;
1430
1431 l_err_loc := 260;
1432
1433 -- also either way we have to make sure that the parent category is a
1434 -- browsing category or the root category
1435 IF (l_parent_type = 2) THEN
1436 l_err_loc := 270;
1437 IF (p_parent_key IS NOT NULL) THEN
1438 l_err_loc := 280;
1439 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
1440 'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);
1441 x_is_valid := 'N';
1442 ELSE
1443 l_err_loc := 290;
1444 insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
1445 'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);
1446 x_is_valid := 'N';
1447 END IF;
1448 END IF;
1449
1450 END IF;
1451
1452
1453 l_err_loc := 300;
1454
1455 -- now we do the same for the child
1456 IF (p_child_key IS NULL AND p_child_name IS NULL) THEN
1457
1458 l_err_loc := 310;
1459 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY', 'ICX_CAT_CHILD_KEY_NAME_REQD',
1460 p_line_number);
1461 x_is_valid := 'N';
1462
1463 END IF;
1464
1465 l_err_loc := 320;
1466
1467 IF (x_is_valid = 'Y') THEN
1468
1469 l_err_loc := 330;
1470
1471 IF (p_child_key IS NOT NULL) THEN
1472
1473 l_err_loc := 340;
1474 -- there is a key, try to get the category from the key
1475
1476 BEGIN
1477
1478 SELECT rt_category_id
1479 INTO x_child_id
1480 FROM icx_cat_categories_tl
1481 WHERE upper_key = UPPER(p_child_key)
1482 AND language = p_language;
1483
1484 l_err_loc := 350;
1485
1486 EXCEPTION
1487 WHEN no_data_found THEN
1488 l_err_loc := 360;
1489 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
1490 'ICX_CAT_CHILD_KEY_NOT_EXIST', p_line_number);
1491 x_is_valid := 'N';
1492 END;
1493
1494 END IF;
1495
1496 l_err_loc := 370;
1497
1498 IF (p_child_name IS NOT NULL) THEN
1499
1500 l_err_loc := 380;
1501 -- there is a name, try to get the category from the name
1502
1503 BEGIN
1504
1505 SELECT rt_category_id
1506 INTO l_child_id_from_name
1507 FROM icx_cat_categories_tl
1508 WHERE upper_category_name = UPPER(p_child_name)
1509 AND language = p_language;
1510
1511 l_err_loc := 390;
1512
1513 EXCEPTION
1514 WHEN no_data_found THEN
1515 l_err_loc := 400;
1516 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
1517 'ICX_CAT_CHILD_NAME_NOT_EXIST', p_line_number);
1518 x_is_valid := 'N';
1519 END;
1520
1521 END IF;
1522
1523 l_err_loc := 410;
1524
1525 -- now if both were provided then we need to compare the categories they got
1526 IF (x_child_id IS NOT NULL) THEN
1527 IF (l_child_id_from_name IS NOT NULL AND x_child_id <> l_child_id_from_name) THEN
1528 l_err_loc := 420;
1529 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
1530 'ICX_CAT_CHILD_KEY_NAME_DIFF', p_line_number);
1531 x_is_valid := 'N';
1532 END IF;
1533 ELSE
1534 x_child_id := l_child_id_from_name;
1535 END IF;
1536
1537 END IF;
1538
1539 l_err_loc := 500;
1540
1541 -- now if we have got both parent and child category, we do some more validations
1542
1543 IF (x_child_id IS NOT NULL AND x_parent_id IS NOT NULL) THEN
1544
1545 l_err_loc := 510;
1546
1547 IF (x_system_action = 'ADD') THEN
1548
1549 l_err_loc := 520;
1550 -- for add parent and child have to be different
1551
1552 IF (x_child_id = x_parent_id) THEN
1553
1554 l_err_loc := 530;
1555
1556 IF (p_child_key IS NOT NULL) THEN
1557 l_err_loc := 540;
1558 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
1559 'ICX_CAT_SAME_PARENT_CHILD', p_line_number);
1560 x_is_valid := 'N';
1561 ELSE
1562 l_err_loc := 550;
1563 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
1564 'ICX_CAT_SAME_PARENT_CHILD', p_line_number);
1565 x_is_valid := 'N';
1566 END IF;
1567 END IF;
1568
1569 l_err_loc := 552;
1570
1571 -- now we check to see if this relationship will add a cycle
1572 SELECT COUNT(*)
1573 INTO l_count
1574 FROM icx_cat_browse_trees
1575 WHERE child_category_id = x_parent_id
1576 START WITH parent_category_id = x_child_id
1577 CONNECT BY NOCYCLE PRIOR child_category_id = parent_category_id;
1578
1579 l_err_loc := 554;
1580
1581 IF (l_count > 0) THEN
1582 -- there will be a cycle
1583 -- so error
1584 IF (p_child_key IS NOT NULL) THEN
1585 l_err_loc := 556;
1586 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
1587 'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);
1588 x_is_valid := 'N';
1589 ELSE
1590 l_err_loc := 558;
1591 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
1592 'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);
1593 x_is_valid := 'N';
1594 END IF;
1595 END IF;
1596
1597 ELSE
1598 -- action is delete
1599 -- the relationship that we want to delete has to exist
1600 l_err_loc := 560;
1601
1602 BEGIN
1603 SELECT 1 INTO l_count
1604 FROM dual
1605 WHERE EXISTS (SELECT 1 FROM icx_cat_browse_trees
1606 WHERE parent_category_id = x_parent_id
1607 AND child_category_id = x_child_id);
1608
1609 l_err_loc := 570;
1610
1611 EXCEPTION
1612 WHEN no_data_found THEN
1613 l_err_loc := 580;
1614 IF (p_child_key IS NOT NULL) THEN
1615 l_err_loc := 590;
1616 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
1617 'ICX_CAT_REL_NOT_EXISTS', p_line_number);
1618 x_is_valid := 'N';
1619 ELSE
1620 l_err_loc := 600;
1621 insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
1622 'ICX_CAT_REL_NOT_EXISTS', p_line_number);
1623 x_is_valid := 'N';
1624 END IF;
1625 END;
1626 END IF;
1627
1628 END IF;
1629
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 RAISE_APPLICATION_ERROR
1633 (-20000,
1634 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.validate_relationship(' ||
1635 l_err_loc || '), ' || SQLERRM);
1636 END validate_relationship;
1637
1638
1639 -- procedure to create a descriptr
1640 -- this assumes that everything has been validated
1641 PROCEDURE create_descriptor
1642 (
1643 p_key IN VARCHAR2,
1644 p_name IN VARCHAR2,
1645 p_description IN VARCHAR2,
1646 p_type IN VARCHAR2,
1647 p_sequence IN NUMBER,
1648 p_search_results_visible IN VARCHAR2,
1649 p_item_detail_visible IN VARCHAR2,
1650 p_searchable IN VARCHAR2,
1651 p_language IN VARCHAR2,
1652 p_category_id IN NUMBER,
1653 p_request_id IN NUMBER,
1654 p_user_id IN NUMBER,
1655 p_login_id IN NUMBER,
1656 x_descriptor_id OUT NOCOPY NUMBER,
1657 x_stored_in_table OUT NOCOPY VARCHAR2,
1658 x_stored_in_column OUT NOCOPY VARCHAR2,
1659 x_section_tag OUT NOCOPY NUMBER
1660 )
1661 IS
1662 l_err_loc PLS_INTEGER;
1663 l_type VARCHAR2(1);
1664 l_sequence NUMBER;
1665 l_search_results_visible VARCHAR2(1);
1666 l_item_detail_visible VARCHAR2(1);
1667 l_searchable VARCHAR2(1);
1668 BEGIN
1669
1670 l_err_loc := 100;
1671
1672 -- first we default type to text if null
1673 IF (p_type IS NULL) THEN
1674 l_err_loc := 110;
1675 l_type := g_TEXT_TYPE;
1676 ELSE
1677 l_err_loc := 115;
1678 l_type := p_type;
1679 END IF;
1680
1681 l_err_loc := 120;
1682
1683 -- next default sequence if not provided
1684 -- sequence defaults to the floor of the max current sequence + 1
1685 -- for that category and language
1686 -- xxx why language
1687 IF (p_sequence IS NULL) THEN
1688 l_err_loc := 130;
1689 SELECT floor(nvl(max(sequence), 0)) + 1
1690 INTO l_sequence
1691 FROM icx_cat_attributes_tl
1692 WHERE rt_category_id = p_category_id
1693 AND language = p_language;
1694 ELSE
1695 l_err_loc := 135;
1696 l_sequence := p_sequence;
1697 END IF;
1698
1699 l_err_loc := 140;
1700 -- Now default SRV, IDV and Searchable
1701 IF (p_search_results_visible IS NULL) THEN
1702 l_err_loc := 150;
1703 l_search_results_visible := g_NO;
1704 ELSE
1705 l_err_loc := 155;
1706 l_search_results_visible := p_search_results_visible;
1707 END IF;
1708
1709 l_err_loc := 160;
1710
1711 IF (p_item_detail_visible IS NULL) THEN
1712 l_err_loc := 170;
1713 l_item_detail_visible := g_YES;
1714 ELSE
1715 l_err_loc := 175;
1716 l_item_detail_visible := p_item_detail_visible;
1717 END IF;
1718
1719 l_err_loc := 170;
1720
1721 IF (p_searchable IS NULL) THEN
1722 l_err_loc := 180;
1723 l_searchable := g_NO;
1724 ELSE
1725 l_err_loc := 185;
1726 l_searchable := p_searchable;
1727 END IF;
1728
1729 l_err_loc := 190;
1730
1731 -- now get the descriptor id from the sequence
1732 -- xxx change to icx_cat_attributes_s
1733 SELECT icx_por_descriptorid.nextval
1734 INTO x_descriptor_id
1735 FROM dual;
1736
1737 l_err_loc := 200;
1738 -- now we create the descriptor
1739
1740 INSERT INTO icx_cat_attributes_tl (attribute_id, rt_category_id, language,
1741 source_lang, attribute_name, description, type, key, sequence, searchable,
1742 search_results_visible, item_detail_visible, request_id, rebuild_flag,
1743 created_by, creation_date, last_updated_by, last_update_date, last_update_login,
1744 program_id, program_application_id, program_login_id)
1745 SELECT x_descriptor_id, p_category_id, fnd_languages.language_code, p_language,
1746 p_name, decode(p_description, '#DEL', null, p_description), to_number(l_type),
1747 p_key, l_sequence, to_number(l_searchable), l_search_results_visible,
1748 l_item_detail_visible, p_request_id, 'N', p_user_id, sysdate,
1749 p_user_id, sysdate, p_login_id, fnd_global.conc_program_id,
1750 fnd_global.prog_appl_id, fnd_global.conc_login_id
1751 FROM fnd_languages
1752 WHERE installed_flag IN ('B', 'I');
1753
1754 l_err_loc := 210;
1755
1756 -- since we are creating a descriptor
1757 -- we update the schema version for search
1758 inc_schema_change_version(p_category_id, p_request_id, p_user_id, p_login_id);
1759
1760 l_err_loc := 220;
1761
1762 -- finally we assign a section tag for the descriptor
1763 assign_section_tag(p_category_id, x_descriptor_id, l_type, x_section_tag,
1764 x_stored_in_table, x_stored_in_column, p_request_id);
1765
1766 l_err_loc := 230;
1767
1768 COMMIT;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 ROLLBACK;
1773 RAISE_APPLICATION_ERROR
1774 (-20000,
1775 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.create_descriptor(' ||
1776 l_err_loc || '), ' || SQLERRM);
1777 END create_descriptor;
1778
1779 -- procedure to increment the schema version
1780 PROCEDURE inc_schema_change_version
1781 (
1782 p_category_id IN NUMBER,
1783 p_request_id IN NUMBER,
1784 p_user_id IN NUMBER,
1785 p_login_id IN NUMBER
1786 )
1787 IS
1788 l_err_loc PLS_INTEGER;
1789 l_attribute_id NUMBER;
1790 BEGIN
1791
1792 l_err_loc := 100;
1793
1794 IF (p_category_id = 0) THEN
1795 l_err_loc := 110;
1796 l_attribute_id := g_ROOT_ATTRIB_ID;
1797 ELSE
1798 l_err_loc := 120;
1799 l_attribute_id := g_LOCAL_ATTRIB_ID;
1800 END IF;
1801
1802 l_err_loc := 130;
1803
1804 -- now update the appropriate row
1805 UPDATE icx_cat_schema_versions
1806 SET version = version + 1,
1807 last_updated_by = p_user_id,
1808 last_update_date = sysdate,
1809 last_update_login = p_login_id,
1810 request_id = p_request_id,
1811 program_id = fnd_global.conc_program_id,
1812 program_application_id = fnd_global.prog_appl_id,
1813 program_login_id = fnd_global.conc_login_id
1814 WHERE descriptor_set_id = l_attribute_id;
1815
1816 l_err_loc := 140;
1817
1818 COMMIT;
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 ROLLBACK;
1823 RAISE_APPLICATION_ERROR
1824 (-20000,
1825 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.inc_schema_change_version(' ||
1826 l_err_loc || '), ' || SQLERRM);
1827 END inc_schema_change_version;
1828
1829
1830 -- Procedure to assign the section tag to a given descriptor
1831 -- If the descriptor is already assigned a section tag it will be returned
1832 -- xxx we are not doing select for update now?
1833 -- Before calling this the rows in icx_cat_categories_tl with the
1834 -- given rt_category_id should be locked thru a SELECT...FOR UPDATE
1835 -- to avoid concurrent access to the SECTION_MAP column. The calling
1836 -- code is responsible for committing the changes.
1837 PROCEDURE assign_section_tag
1838 (
1839 p_category_id IN NUMBER,
1840 p_descriptor_id IN NUMBER,
1841 p_type IN VARCHAR2,
1842 p_section_tag OUT NOCOPY NUMBER,
1843 p_stored_in_table OUT NOCOPY VARCHAR2,
1844 p_stored_in_column OUT NOCOPY VARCHAR2,
1845 p_request_id IN NUMBER
1846 )
1847 IS
1848 l_err_loc PLS_INTEGER;
1849 l_section_map VARCHAR2(300);
1850 l_col_prefix VARCHAR2(5);
1851 BEGIN
1852
1853 l_err_loc := 100;
1854
1855 -- First check whether a section tag is already assigned
1856 SELECT section_tag, stored_in_table, stored_in_column
1857 INTO p_section_tag, p_stored_in_table, p_stored_in_column
1858 FROM icx_cat_attributes_tl
1859 WHERE attribute_id = p_descriptor_id
1860 AND rownum = 1;
1861
1862 l_err_loc := 110;
1863
1864 -- only proceed if no section tag assigned
1865 IF (p_section_tag IS NULL) THEN
1866 l_err_loc := 120;
1867
1868 -- now get the section map and section tag based on the type
1869 IF (p_type = 0) THEN
1870
1871 l_err_loc := 120;
1872 SELECT section_map, INSTR(section_map, '0', 1, 1)
1873 INTO l_section_map, p_section_tag
1874 FROM icx_cat_categories_tl
1875 WHERE rt_category_id = p_category_id
1876 AND rownum = 1;
1877
1878 ELSIF (p_type = 1) THEN
1879
1880 l_err_loc := 130;
1881 SELECT section_map, INSTR(section_map, '0', 101, 1)
1882 INTO l_section_map, p_section_tag
1883 FROM icx_cat_categories_tl
1884 WHERE rt_category_id = p_category_id
1885 AND rownum = 1;
1886
1887 ELSIF (p_type = 2) THEN
1888
1889 l_err_loc := 140;
1890 SELECT section_map, INSTR(section_map, '0', 201, 1)
1891 INTO l_section_map, p_section_tag
1892 FROM icx_cat_categories_tl
1893 WHERE rt_category_id = p_category_id
1894 AND rownum = 1;
1895 END IF;
1896
1897 -- only proceed if we get a valid section tag (should always happen)
1898 l_err_loc := 150;
1899 IF (p_section_tag > 0) THEN
1900
1901 l_err_loc := 160;
1902
1903 -- now we do the stored in table and column
1904 -- for stored in column we have either <TYPE>_<COLPREFIX>_ATTRIBUTE<i>
1905
1906 -- identify the column prefix
1907 IF (p_category_id = 0) THEN
1908 l_err_loc := 170;
1909 l_col_prefix := 'BASE';
1910 ELSE
1911 l_err_loc := 180;
1912 l_col_prefix := 'CAT';
1913 END IF;
1914
1915 l_err_loc := 190;
1916
1917 -- now use type to determine the stored in table and column
1918 IF (p_type = g_TEXT_TYPE) THEN
1919
1920 l_err_loc := 200;
1921
1922 p_stored_in_table := 'PO_ATTRIBUTE_VALUES';
1923 p_stored_in_column := 'TEXT_' || l_col_prefix || '_ATTRIBUTE' || to_char(p_section_tag);
1924 ELSIF (p_type = g_NUMERIC_TYPE) THEN
1925
1926 l_err_loc := 210;
1927 p_stored_in_table := 'PO_ATTRIBUTE_VALUES';
1928 p_section_tag := p_section_tag - 100;
1929 p_stored_in_column := 'NUM_'|| l_col_prefix || '_ATTRIBUTE' || to_char(p_section_tag);
1930 p_section_tag := p_section_tag + 100;
1931 ELSE
1932
1933 l_err_loc := 220;
1934 p_stored_in_table := 'PO_ATTRIBUTE_VALUES_TLP';
1935 p_section_tag := p_section_tag - 200;
1936 p_stored_in_column := 'TL_TEXT_' || l_col_prefix || '_ATTRIBUTE' || to_char(p_section_tag);
1937 p_section_tag := p_section_tag + 200;
1938 END IF;
1939
1940 l_err_loc := 230;
1941 -- now we compute the new section map
1942
1943 l_section_map := substr(l_section_map, 1, p_section_tag - 1) || '1' ||
1944 substr(l_section_map, p_section_tag + 1);
1945
1946 l_err_loc := 235;
1947 -- we also need to start section tag off appropriately
1948 IF (p_category_id > 0) THEN
1949 -- for category attributes
1950 p_section_tag := p_section_tag + 5000;
1951 ELSE
1952 -- for base attributes
1953 p_section_tag := p_section_tag + 1000;
1954 END IF;
1955
1956 l_err_loc := 240;
1957 -- now we update the category table with the section map
1958
1959 UPDATE icx_cat_categories_tl
1960 SET section_map = l_section_map,
1961 request_id = p_request_id,
1962 program_id = fnd_global.conc_program_id,
1963 program_application_id = fnd_global.prog_appl_id,
1964 program_login_id = fnd_global.conc_login_id
1965 WHERE rt_category_id = p_category_id;
1966
1967 l_err_loc := 250;
1968 -- now we update the attributes table with the section tag,
1969 -- stored_in_table and stored_in_column
1970 UPDATE icx_cat_attributes_tl
1971 SET section_tag = p_section_tag,
1972 stored_in_table = p_stored_in_table,
1973 stored_in_column = p_stored_in_column,
1974 request_id = p_request_id,
1975 program_id = fnd_global.conc_program_id,
1976 program_application_id = fnd_global.prog_appl_id,
1977 program_login_id = fnd_global.conc_login_id
1978 WHERE attribute_id = p_descriptor_id;
1979
1980 l_err_loc := 260;
1981
1982 END IF;
1983
1984 l_err_loc := 270;
1985
1986 END IF;
1987
1988 l_err_loc := 280;
1989
1990 EXCEPTION
1991 WHEN OTHERS THEN
1992 RAISE_APPLICATION_ERROR
1993 (-20000,
1994 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.assign_section_tag(' ||
1995 l_err_loc || '), ' || SQLERRM);
1996 END assign_section_tag;
1997
1998
1999 -- Procedure to release the section tag to a given descriptor
2000 -- should be called before the descriptor is actually deleted
2001 -- xxx we are not doing select for update now?
2002 -- Before calling this the rows in icx_cat_categories_tl with the
2003 -- given rt_category_id should be locked thru a SELECT...FOR UPDATE
2004 -- to avoid concurrent access to the SECTION_MAP column. The calling
2005 -- code is responsible for committing the changes.
2006 PROCEDURE release_section_tag
2007 (
2008 p_category_id IN NUMBER,
2009 p_descriptor_id IN NUMBER,
2010 p_request_id IN NUMBER
2011 )
2012 IS
2013 l_err_loc PLS_INTEGER;
2014 l_section_map VARCHAR2(300);
2015 l_section_tag PLS_INTEGER;
2016 l_bit_position PLs_INTEGER;
2017 BEGIN
2018
2019 l_err_loc := 100;
2020
2021 -- first get the existing section map
2022 SELECT section_map
2023 INTO l_section_map
2024 FROM icx_cat_categories_tl
2025 WHERE rt_category_id = p_category_id
2026 AND rownum = 1;
2027
2028 l_err_loc := 110;
2029
2030 -- now get the existing section tag
2031 SELECT section_tag
2032 INTO l_section_tag
2033 FROM icx_cat_attributes_tl
2034 WHERE attribute_id = p_descriptor_id
2035 AND rownum = 1;
2036
2037 l_err_loc := 120;
2038
2039 -- only proceed if there is a section tag assigned
2040 IF (l_section_tag IS NOT NULL) THEN
2041
2042 l_err_loc := 130;
2043
2044 -- now nullify the section tag in attributes table
2045 UPDATE icx_cat_attributes_tl
2046 SET section_tag = null,
2047 request_id = p_request_id,
2048 program_id = fnd_global.conc_program_id,
2049 program_application_id = fnd_global.prog_appl_id,
2050 program_login_id = fnd_global.conc_login_id
2051 WHERE attribute_id = p_descriptor_id;
2052
2053 l_err_loc := 140;
2054
2055 -- now compute the bit position
2056 IF (p_category_id > 0) THEN
2057 l_bit_position := l_section_tag - 5000;
2058 ELSIF (p_descriptor_id >= g_NUM_SEEDED_DESCRIPTORS) THEN
2059 l_bit_position := l_section_tag - 1000;
2060 END IF;
2061
2062 -- now use bit position to compute new section map
2063 l_err_loc := 150;
2064
2065 l_section_map := substr(l_section_map, 1, l_bit_position - 1) || '0' ||
2066 substr(l_section_map, l_bit_position + 1);
2067
2068 l_err_loc := 150;
2069 -- now update the section map in the categories table
2070
2071 UPDATE icx_cat_categories_tl
2072 SET section_map = l_section_map,
2073 request_id = p_request_id,
2074 program_id = fnd_global.conc_program_id,
2075 program_application_id = fnd_global.prog_appl_id,
2076 program_login_id = fnd_global.conc_login_id
2077 WHERE rt_category_id = p_category_id;
2078
2079 l_err_loc := 160;
2080
2081 END IF;
2082
2083 l_err_loc := 170;
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 RAISE_APPLICATION_ERROR
2088 (-20000,
2089 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.release_section_tag(' ||
2090 l_err_loc || '), ' || SQLERRM);
2091 END release_section_tag;
2092
2093
2094 -- procedure to update a descriptr
2095 -- this assumes that everything has been validated
2096 PROCEDURE update_descriptor
2097 (
2098 p_descriptor_id IN NUMBER,
2099 p_name IN VARCHAR2,
2100 p_description IN VARCHAR2,
2101 p_category_id IN VARCHAR2,
2102 p_sequence IN NUMBER,
2103 p_search_results_visible IN VARCHAR2,
2104 p_item_detail_visible IN VARCHAR2,
2105 p_searchable IN VARCHAR2,
2106 p_language IN VARCHAR2,
2107 p_request_id IN NUMBER,
2108 p_user_id IN NUMBER,
2109 p_login_id IN NUMBER
2110 )
2111 IS
2112 l_err_loc PLS_INTEGER;
2113 l_searchable VARCHAR2(1);
2114 l_rebuild_flag VARCHAR2(1) := 'N';
2115 BEGIN
2116
2117 l_err_loc := 100;
2118
2119 -- first select the current value of searchable
2120
2121 SELECT to_char(searchable)
2122 INTO l_searchable
2123 FROM icx_cat_attributes_tl
2124 WHERE attribute_id = p_descriptor_id
2125 AND rownum = 1;
2126
2127 -- if searchable has changed then we need to rebuild
2128 l_err_loc := 110;
2129 IF (p_searchable IS NOT NULL AND p_searchable <> l_searchable) THEN
2130 l_err_loc := 120;
2131 l_rebuild_flag := 'Y';
2132 END IF;
2133
2134 -- now update the attributes table
2135
2136 l_err_loc := 120;
2137 -- first we update the translatable attributes only in the current lang
2138 -- i.e. name and description
2139 UPDATE icx_cat_attributes_tl
2140 SET attribute_name = nvl (p_name, attribute_name),
2141 description = decode(p_description, '#DEL', null, null, description, p_description),
2142 source_lang = p_language,
2143 last_updated_by = p_user_id,
2144 last_update_date = sysdate,
2145 last_update_login = p_login_id,
2146 request_id = p_request_id,
2147 program_id = fnd_global.conc_program_id,
2148 program_application_id = fnd_global.prog_appl_id,
2149 program_login_id = fnd_global.conc_login_id
2150 WHERE attribute_id = p_descriptor_id
2151 AND language = p_language;
2152
2153 l_err_loc := 130;
2154
2155 -- now update searchable, SRV and IDV and sequence in all languages
2156 UPDATE icx_cat_attributes_tl
2157 SET sequence = decode (p_sequence, '#DEL', null, null, sequence, p_sequence),
2158 searchable = to_number(nvl(p_searchable, searchable)),
2159 search_results_visible = to_number(nvl(p_search_results_visible, search_results_visible)),
2160 item_detail_visible = to_number(nvl(p_item_detail_visible, item_detail_visible)),
2161 rebuild_flag = l_rebuild_flag,
2162 last_updated_by = p_user_id,
2163 last_update_date = sysdate,
2164 last_update_login = p_login_id,
2165 request_id = p_request_id,
2166 program_id = fnd_global.conc_program_id,
2167 program_application_id = fnd_global.prog_appl_id,
2168 program_login_id = fnd_global.conc_login_id
2169 WHERE attribute_id = p_descriptor_id;
2170
2171 l_err_loc := 140;
2172
2173 -- now increment the schema version
2174 inc_schema_change_version(p_category_id, p_request_id, p_user_id, p_login_id);
2175
2176 l_err_loc := 150;
2177
2178 COMMIT;
2179
2180 EXCEPTION
2181 WHEN OTHERS THEN
2182 ROLLBACK;
2183 RAISE_APPLICATION_ERROR
2184 (-20000,
2185 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_descriptor(' ||
2186 l_err_loc || '), ' || SQLERRM);
2187 END update_descriptor;
2188
2189
2190 -- procedure to delete a descriptr
2191 -- this assumes that everything has been validated
2192 PROCEDURE delete_descriptor
2193 (
2194 p_descriptor_id IN NUMBER,
2195 p_request_id IN NUMBER,
2196 p_user_id IN NUMBER,
2197 p_login_id IN NUMBER
2198 )
2199 IS
2200 l_err_loc PLS_INTEGER;
2201 l_category_id NUMBER;
2202 l_searchable VARCHAR2(1);
2203 BEGIN
2204
2205 l_err_loc := 100;
2206
2207 -- for sanity do not allow seeded root descriptor deletion
2208 IF (p_descriptor_id > g_NUM_SEEDED_DESCRIPTORS) THEN
2209
2210 l_err_loc := 110;
2211
2212 -- also for sanity check if descriptor is deletable
2213 IF (can_descriptor_be_deleted(p_descriptor_id) = 0) THEN
2214
2215 l_err_loc := 120;
2216
2217 -- select some information about the descriptor
2218 SELECT rt_category_id, to_char(searchable)
2219 INTO l_category_id, l_searchable
2220 FROM icx_cat_attributes_tl
2221 WHERE attribute_id = p_descriptor_id
2222 AND rownum = 1;
2223
2224 l_err_loc := 130;
2225
2226 -- then release the section tag before deleting the descriptor
2227 release_section_tag(l_category_id, p_descriptor_id, p_request_id);
2228
2229 l_err_loc := 140;
2230
2231 -- now delete the descriptor
2232 DELETE from icx_cat_attributes_tl
2233 WHERE attribute_id = p_descriptor_id;
2234
2235 l_err_loc := 150;
2236
2237 -- increment the schema version
2238 inc_schema_change_version(l_category_id, p_request_id, p_user_id, p_login_id);
2239
2240 l_err_loc := 160;
2241
2242 END IF;
2243
2244 l_err_loc := 190;
2245
2246 END IF;
2247
2248 l_err_loc := 200;
2249
2250 COMMIT;
2251
2252 EXCEPTION
2253 WHEN OTHERS THEN
2254 ROLLBACK;
2255 RAISE_APPLICATION_ERROR
2256 (-20000,
2257 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_descriptor(' ||
2258 l_err_loc || '), ' || SQLERRM);
2259 END delete_descriptor;
2260
2261
2262 -- function to check if descriptor can be deleted
2263 -- a descriptor can be deleted if it has no values for any documents
2264 -- we assume that this will be called only for deletable attributes
2265 -- i.e. those that have stored_in_table and stored_in_column as not null
2266 FUNCTION can_descriptor_be_deleted
2267 (
2268 p_descriptor_id IN NUMBER
2269 )
2270 RETURN NUMBER
2271 IS
2272 l_err_loc PLS_INTEGER;
2273 l_descriptor_referenced PLS_INTEGER;
2274 l_stored_in_table VARCHAR2(30);
2275 l_stored_in_column VARCHAR2(30);
2276 l_draft_stored_in_table VARCHAR2(30);
2277 l_category_id NUMBER;
2278 BEGIN
2279 l_err_loc := 100;
2280
2281 -- first select stored in table and stored in column
2282 SELECT rt_category_id, stored_in_table, stored_in_column
2283 INTO l_category_id, l_stored_in_table, l_stored_in_column
2284 FROM icx_cat_attributes_tl
2285 WHERE attribute_id = p_descriptor_id
2286 AND rownum = 1;
2287
2288 -- now assume that the descriptor is not referenced
2289 l_err_loc := 200;
2290 l_descriptor_referenced := 0;
2291
2292 l_err_loc := 300;
2293
2294 -- now check in the po transaction table
2295 BEGIN
2296 EXECUTE IMMEDIATE
2297 'SELECT 1 '||
2298 'FROM dual ' ||
2299 'WHERE EXISTS ( ' ||
2300 'SELECT 1 ' ||
2301 'FROM ' || l_stored_in_table ||
2302 ' WHERE ' || l_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
2303 ' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
2304 INTO l_descriptor_referenced;
2305
2306 l_err_loc := 350;
2307
2308 EXCEPTION
2309 WHEN no_data_found THEN
2310 NULL;
2311 END;
2312
2313 l_err_loc := 300;
2314
2315 -- now check in the po draft table
2316 IF (l_stored_in_table = 'PO_ATTRIBUTE_VALUES') THEN
2317 l_err_loc := 325;
2318 l_draft_stored_in_table := 'PO_ATTRIBUTE_VALUES_DRAFT';
2319 ELSE
2320 -- it is tlp
2321 l_err_loc := 350;
2322 l_draft_stored_in_table := 'PO_ATTRIBUTE_VALUES_TLP_DRAFT';
2323 END IF;
2324
2325 l_err_loc := 400;
2326
2327 BEGIN
2328 EXECUTE IMMEDIATE
2329 'SELECT 1 '||
2330 'FROM dual ' ||
2331 'WHERE EXISTS ( ' ||
2332 'SELECT 1 ' ||
2333 'FROM ' || l_draft_stored_in_table ||
2334 ' WHERE ' || l_draft_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
2335 ' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
2336 INTO l_descriptor_referenced;
2337
2338 l_err_loc := 450;
2339
2340 EXCEPTION
2341 WHEN no_data_found THEN
2342 NULL;
2343 END;
2344
2345 -- now we check the sourcing tables
2346 l_err_loc := 500;
2347
2348 BEGIN
2349 SELECT 1
2350 INTO l_descriptor_referenced
2351 FROM dual
2352 WHERE EXISTS (SELECT 1
2353 FROM pon_auction_attributes
2354 WHERE ip_descriptor_id = p_descriptor_id
2355 AND ip_category_id = l_category_id);
2356
2357 l_err_loc := 550;
2358
2359 EXCEPTION
2360 WHEN no_data_found THEN
2361 NULL;
2362 END;
2363
2364 l_err_loc := 600;
2365
2366 BEGIN
2367 SELECT 1
2368 INTO l_descriptor_referenced
2369 FROM dual
2370 WHERE EXISTS (SELECT 1
2371 FROM pon_auc_attributes_interface
2372 WHERE ip_descriptor_id = p_descriptor_id
2373 AND ip_category_id = l_category_id);
2374
2375 l_err_loc := 650;
2376
2377 EXCEPTION
2378 WHEN no_data_found THEN
2379 NULL;
2380 END;
2381
2382 l_err_loc := 700;
2383
2384 BEGIN
2385 SELECT 1
2386 INTO l_descriptor_referenced
2387 FROM dual
2388 WHERE EXISTS (SELECT 1
2389 FROM pon_attributes_interface
2390 WHERE ip_descriptor_id = p_descriptor_id
2391 AND ip_category_id = l_category_id);
2392
2393 l_err_loc := 750;
2394
2395 EXCEPTION
2396 WHEN no_data_found THEN
2397 NULL;
2398 END;
2399
2400 l_err_loc := 800;
2401
2402 RETURN l_descriptor_referenced;
2403 EXCEPTION
2404 WHEN OTHERS THEN
2405 RAISE_APPLICATION_ERROR
2406 (-20000,
2407 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_descriptor_be_deleted(' ||
2408 l_err_loc || '), ' || SQLERRM);
2409 END can_descriptor_be_deleted;
2410
2411
2412 -- procedure to validate a descriptor
2413 PROCEDURE validate_descriptor
2414 (
2415 p_key IN VARCHAR2,
2416 p_name IN VARCHAR2,
2417 p_description IN VARCHAR2,
2418 p_type IN VARCHAR2,
2419 p_owner_key IN VARCHAR2,
2420 p_owner_name IN VARCHAR2,
2421 p_language IN VARCHAR2,
2422 p_sequence IN VARCHAR2,
2423 p_searchable IN VARCHAR2,
2424 p_search_results_visible IN VARCHAR2,
2425 p_item_detail_visible IN VARCHAR2,
2426 p_request_id IN NUMBER,
2427 p_line_number IN NUMBER,
2428 p_user_action IN VARCHAR2,
2429 x_is_valid OUT NOCOPY VARCHAR2,
2430 x_system_action OUT NOCOPY VARCHAR2,
2431 x_descriptor_id OUT NOCOPY NUMBER,
2432 x_owner_id OUT NOCOPY NUMBER
2433 )
2434 IS
2435 l_err_loc PLS_INTEGER;
2436 l_owner_id_from_name NUMBER;
2437 l_num_val NUMBER;
2438 l_current_type VARCHAR2(1);
2439 l_start_pos PLS_INTEGER;
2440 l_end_pos PLS_INTEGER;
2441 l_first_zero_pos PLS_INTEGER;
2442 BEGIN
2443
2444 l_err_loc := 100;
2445
2446 x_is_valid := 'Y';
2447
2448 -- first we check the owner key and name
2449 -- since we need to determine the owner first to know what the descriptor is
2450
2451 IF (p_owner_key IS NULL AND p_owner_name IS NULL) THEN
2452 l_err_loc := 110;
2453 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_OWNER_KEY_NAME_REQD',
2454 p_line_number);
2455 x_is_valid := 'N';
2456 ELSE
2457
2458 l_err_loc := 120;
2459
2460 -- check if owner key is provided
2461 IF (p_owner_key IS NOT NULL) THEN
2462
2463 BEGIN
2464 l_err_loc := 130;
2465
2466 SELECT rt_category_id
2467 INTO x_owner_id
2468 FROM icx_cat_categories_tl
2469 WHERE upper_key = UPPER(p_owner_key)
2470 AND language = p_language
2471 AND type IN (0,2);
2472
2473 EXCEPTION
2474 WHEN no_data_found THEN
2475 l_err_loc := 140;
2476 insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
2477 'ICX_CAT_INVALID_OWNER_KEY', p_line_number);
2478 x_is_valid := 'N';
2479 END;
2480 END IF;
2481
2482 l_err_loc := 150;
2483
2484 -- now check if owner name is provided
2485 IF (p_owner_name IS NOT NULL) THEN
2486
2487 BEGIN
2488
2489 l_err_loc := 160;
2490
2491 SELECT rt_category_id
2492 INTO l_owner_id_from_name
2493 FROM icx_cat_categories_tl
2494 WHERE upper_category_name = UPPER(p_owner_name)
2495 AND language = p_language
2496 AND type IN (0,2);
2497
2498 EXCEPTION
2499 WHEN no_data_found THEN
2500 insert_failed_message(p_request_id, 'ICX_CAT_OWNER_NAME',
2501 'ICX_CAT_INVALID_OWNER_NAME', p_line_number);
2502 x_is_valid := 'N';
2503 END;
2504 END IF;
2505
2506 -- now if both were provided then we need to compare the categories they got
2507 IF (x_owner_id IS NOT NULL) THEN
2508 IF (l_owner_id_from_name IS NOT NULL AND x_owner_id <> l_owner_id_from_name) THEN
2509 l_err_loc := 420;
2510 insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
2511 'ICX_CAT_OWNER_KEY_NAME_DIFF', p_line_number);
2512 x_is_valid := 'N';
2513 END IF;
2514 ELSE
2515 l_err_loc := 430;
2516 -- we set actual id to temp id
2517 x_owner_id := l_owner_id_from_name;
2518 END IF;
2519
2520 END IF;
2521
2522 l_err_loc := 440;
2523 -- now we check the key to see if it is provided
2524 -- key is required for both sync and delete
2525 IF (p_key IS NULL) THEN
2526 l_err_loc := 450;
2527 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_KEY_REQD',
2528 p_line_number);
2529 x_is_valid := 'N';
2530 END IF;
2531
2532
2533
2534 l_err_loc := 460;
2535
2536 -- now we do rest of validations only if is valid so far
2537 -- i.e. we have a valid owner id and a non-null key
2538 -- also action has to be either SYNC or DELETE.. validated by DTD
2539
2540 IF (x_is_valid = 'Y') THEN
2541
2542 BEGIN
2543
2544 l_err_loc := 460;
2545
2546 -- we try to get the descriptor from the database
2547 -- for this key and category
2548 SELECT attribute_id, to_char(type)
2549 INTO x_descriptor_id, l_current_type
2550 FROM icx_cat_attributes_tl
2551 WHERE UPPER(key) = UPPER(p_key)
2552 AND language = p_language
2553 AND rt_category_id = x_owner_id
2554 AND rownum = 1;
2555
2556 l_err_loc := 470;
2557
2558 -- we found the key, set system action to ADD if it was SYNC
2559 -- DELETE is fine .. we found the row
2560 IF (p_user_action = 'SYNC') THEN
2561 l_err_loc := 480;
2562 x_system_action := 'UPDATE';
2563 ELSIF (p_user_action = 'DELETE') THEN
2564 l_err_loc := 490;
2565 x_system_action := 'DELETE';
2566 END IF;
2567
2568 EXCEPTION
2569 WHEN no_data_found THEN
2570 l_err_loc := 500;
2571 -- we did not find the key for this category
2572 -- so for SYNC we set system action to ADD
2573 -- for delete, we did not get the row, so error
2574 IF (p_user_action = 'SYNC') THEN
2575 l_err_loc := 510;
2576 x_system_action := 'ADD';
2577 ELSIF (p_user_action = 'DELETE') THEN
2578 l_err_loc := 210;
2579 x_system_action := 'DELETE';
2580 insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_DOES_NOT_EXIST',
2581 p_line_number);
2582 x_is_valid := 'N';
2583 END IF;
2584 END;
2585
2586 l_err_loc := 530;
2587 END IF;
2588
2589 l_err_loc := 540;
2590 -- now we have got system action, so we validate based on system action
2591
2592 IF (x_is_valid = 'Y') THEN
2593 l_err_loc := 550;
2594 IF (x_system_action = 'ADD') THEN
2595 -- first we need to make sure this key does not exist elsewhere
2596 -- if we are trying to create a root descriptor then it should not
2597 -- exist in any category else it should not exist in current category
2598 -- and root
2599 l_err_loc := 551;
2600 BEGIN
2601 IF (x_owner_id = 0) THEN
2602 l_err_loc := 552;
2603 SELECT 1
2604 INTO l_num_val
2605 FROM DUAL
2606 WHERE EXISTS (SELECT 1
2607 FROM icx_cat_attributes_tl
2608 WHERE UPPER(key) = UPPER(p_key)
2609 AND language = p_language);
2610 ELSE
2611 l_err_loc := 553;
2612 SELECT 1
2613 INTO l_num_val
2614 FROM DUAL
2615 WHERE EXISTS (SELECT 1
2616 FROM icx_cat_attributes_tl
2617 WHERE UPPER(key) = UPPER(p_key)
2618 AND language = p_language
2619 AND (rt_category_id = x_owner_id OR
2620 rt_category_id = 0));
2621 END IF;
2622
2623 -- found a descriptor, error error out
2624 l_err_loc := 555;
2625 insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_KEY_NONUNIQUE',
2626 p_line_number);
2627 x_is_valid := 'N';
2628 EXCEPTION
2629 WHEN no_data_found THEN
2630 l_err_loc := 555;
2631 null;
2632 END;
2633
2634
2635 l_err_loc := 559;
2636 -- for add Name is required
2637 IF (p_name IS NULL) THEN
2638 l_err_loc := 560;
2639 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_NAME_REQD',
2640 p_line_number);
2641 x_is_valid := 'N';
2642 ELSE
2643 l_err_loc := 570;
2644
2645 BEGIN
2646
2647 -- now we check for uniqueness of name
2648 -- if we are trying to add a root descriptor then name has to be unique
2649 -- across all categories
2650 -- else it just has to be unique in that category and root category
2651 IF (x_owner_id = 0) THEN
2652 l_err_loc := 580;
2653 -- for base descriptor
2654 SELECT 1
2655 INTO l_num_val
2656 FROM DUAL
2657 WHERE EXISTS (SELECT 1
2658 FROM icx_cat_attributes_tl
2659 WHERE UPPER(attribute_name) = UPPER(p_name)
2660 AND language = p_language);
2661 ELSE
2662 l_err_loc := 590;
2663 -- for category descriptor
2664 SELECT 1
2665 INTO l_num_val
2666 FROM DUAL
2667 WHERE EXISTS (SELECT 1
2668 FROM icx_cat_attributes_tl
2669 WHERE UPPER(attribute_name) = UPPER(p_name)
2670 AND language = p_language
2671 AND (rt_category_id = x_owner_id OR
2672 rt_category_id = 0));
2673 END IF;
2674
2675 l_err_loc := 600;
2676 -- if it found a name then error
2677 insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_ADD',
2678 p_line_number);
2679 x_is_valid := 'N';
2680
2681 EXCEPTION
2682 WHEN no_data_found THEN
2683 -- we are fine
2684 l_err_loc := 610;
2685 null;
2686 END;
2687
2688 l_err_loc := 620;
2689 -- now we check to see if the max number of descriptors has been reached
2690 -- for this type
2691 -- for base this is 100(other than the seeded attributes), for category 50
2692
2693 IF (x_owner_id = 0) THEN
2694 -- for base
2695 l_err_loc := 630;
2696 SELECT COUNT(*)
2697 INTO l_num_val
2698 FROM icx_cat_attributes_tl
2699 WHERE rt_category_id = 0
2700 AND language = p_language
2701 AND to_char(type) = p_type
2702 AND attribute_id > 100;
2703
2704 l_err_loc := 640;
2705
2706 -- make sure the section_map is fine for creating a new base descriptor
2707 IF p_type = 0 THEN
2708 l_start_pos := 1;
2709 l_end_pos := 100;
2710 ELSIF p_type = 1 THEN
2711 l_start_pos := 101;
2712 l_end_pos := 200;
2713 ELSIF p_type = 2 THEN
2714 l_start_pos := 201;
2715 l_end_pos := 300;
2716 END IF;
2717
2718 l_err_loc := 645;
2719
2720 SELECT instr(section_map, '0', l_start_pos, 1)
2721 INTO l_first_zero_pos
2722 FROM icx_cat_categories_tl
2723 WHERE rt_category_id = 0
2724 AND language = p_language;
2725
2726 l_err_loc := 650;
2727
2728 IF (l_num_val >= 100 OR l_first_zero_pos > l_end_pos) THEN
2729 l_err_loc := 655;
2730 insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_BASE_ATT_NUM_EXCEED',
2731 p_line_number);
2732 x_is_valid := 'N';
2733 END IF;
2734 ELSE
2735 -- for category
2736 l_err_loc := 660;
2737 SELECT COUNT(*)
2738 INTO l_num_val
2739 FROM icx_cat_attributes_tl
2740 WHERE rt_category_id = x_owner_id
2741 AND language = p_language
2742 AND to_char(type) = p_type;
2743
2744 l_err_loc := 670;
2745
2746 -- make sure the section_map is fine for creating a new category descriptor
2747 IF p_type = 0 THEN
2748 l_start_pos := 1;
2749 l_end_pos := 50;
2750 ELSIF p_type = 1 THEN
2751 l_start_pos := 101;
2752 l_end_pos := 150;
2753 ELSIF p_type = 2 THEN
2754 l_start_pos := 201;
2755 l_end_pos := 250;
2756 END IF;
2757
2758 l_err_loc := 672;
2759
2760 SELECT instr(section_map, '0', l_start_pos, 1)
2761 INTO l_first_zero_pos
2762 FROM icx_cat_categories_tl
2763 WHERE rt_category_id = x_owner_id
2764 AND language = p_language;
2765
2766 l_err_loc := 675;
2767
2768 IF (l_num_val >= 50 OR l_first_zero_pos > l_end_pos) THEN
2769 l_err_loc := 680;
2770 insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_CAT_ATT_NUM_EXCEED',
2771 p_line_number);
2772 x_is_valid := 'N';
2773 END IF;
2774
2775 END IF; -- end IF (x_owner_id...
2776
2777 END IF; -- IF (p_name...
2778
2779 ELSIF (x_system_action = 'UPDATE') THEN
2780
2781 l_err_loc := 690;
2782
2783 BEGIN
2784
2785 -- for update we check for uniqueness of name similar to add
2786 -- if we are trying to add a root descriptor then name has to be unique
2787 -- across all categories
2788 -- else it just has to be unique in that category and root category
2789 -- in addition we only check other descriptors than the one we are updating
2790 -- since we can update a descriptor to a same name
2791 IF (x_owner_id = 0) THEN
2792 l_err_loc := 700;
2793 -- for base descriptor
2794 SELECT 1
2795 INTO l_num_val
2796 FROM dual
2797 WHERE EXISTS (SELECT 1
2798 FROM icx_cat_attributes_tl
2799 WHERE UPPER(attribute_name) = UPPER(p_name)
2800 AND attribute_id <> x_descriptor_id);
2801 ELSE
2802 l_err_loc := 710;
2803 -- for category descriptor
2804 SELECT 1
2805 INTO l_num_val
2806 FROM dual
2807 WHERE EXISTS (SELECT 1
2808 FROM icx_cat_attributes_tl
2809 WHERE UPPER(attribute_name) = UPPER(p_name)
2810 AND (rt_category_id = x_owner_id OR
2811 rt_category_id = 0)
2812 AND attribute_id <> x_descriptor_id);
2813 END IF;
2814
2815 l_err_loc := 720;
2816 -- if it found a name then error
2817 insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_UPD',
2818 p_line_number);
2819 x_is_valid := 'N';
2820
2821 EXCEPTION
2822 WHEN no_data_found THEN
2823 -- we are fine
2824 l_err_loc := 730;
2825 null;
2826 END;
2827
2828 l_err_loc := 740;
2829
2830 -- now we validate that the type cannot be updated
2831 IF (p_type IS NOT NULL AND p_type <> l_current_type) THEN
2832 -- Cannot update type
2833 l_err_loc := 750;
2834 insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_DES_TYPE',
2835 p_line_number);
2836 x_is_valid := 'N';
2837 END IF;
2838
2839 l_err_loc := 752;
2840
2841 -- now certain properties i.e. sequence, Searchable, SRV, IDV
2842 -- cannot be updated for certain seeded base attributes
2843 -- we validate those now
2844
2845 IF (upper(p_key) IN ('THUMBNAIL_IMAGE', 'PICTURE', 'UOM', 'CURRENCY',
2846 'FUNCTIONAL_CURRENCY', 'LONG_DESCRIPTION') AND
2847 p_sequence is not null) THEN
2848
2849 l_err_loc := 753;
2850 -- cannot change sequence for these
2851 -- we say if user provided a sequence, we error
2852 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SEQUENCE',
2853 p_line_number);
2854 x_is_valid := 'N';
2855
2856 END IF;
2857
2858 l_err_loc := 754;
2859
2860 IF (upper(p_key)IN ('PURCHASING_CATEGORY', 'THUMBNAIL_IMAGE', 'SUPPLIER', 'SUPPLIER_SITE',
2861 'PICTURE', 'UOM', 'PRICE', 'CURRENCY', 'FUNCTIONAL_PRICE',
2862 'FUNCTIONAL_CURRENCY', 'ATTACHMENT_URL', 'SUPPLIER_URL',
2863 'MANUFACTURER_URL') AND
2864 p_searchable = '1') THEN
2865
2866 l_err_loc := 755;
2867 -- cannot change searchable for these
2868 -- and default is 0 so we just check for 1
2869 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRCHABLE',
2870 p_line_number);
2871 x_is_valid := 'N';
2872
2873 END IF;
2874
2875 l_err_loc := 757;
2876
2877 IF (upper(p_key) = 'PICTURE' AND
2878 p_search_results_visible = '1') THEN
2879
2880 l_err_loc := 760;
2881 -- cannot make PICTURE SRV
2882 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRV',
2883 p_line_number);
2884 x_is_valid := 'N';
2885
2886 END IF;
2887
2888 l_err_loc := 762;
2889
2890 ELSIF (x_system_action = 'DELETE') THEN
2891
2892 l_err_loc := 770;
2893 -- for delete we first check to see that the seeded base descriptors
2894 -- cannot be deleted
2895 IF(x_descriptor_id <= g_NUM_SEEDED_DESCRIPTORS) THEN
2896 l_err_loc := 780;
2897 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_SEEDED_DESC',
2898 p_line_number);
2899 x_is_valid := 'N';
2900 END IF;
2901
2902 l_err_loc := 790;
2903
2904 -- now we check to see if descriptor can be deleted
2905 -- descriptor can be deleted if there are no items referencing it
2906 IF (can_descriptor_be_deleted(x_descriptor_id) = 1) THEN
2907 l_err_loc := 800;
2908 insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESC_HAS_ITEMS',
2909 p_line_number);
2910 x_is_valid := 'N';
2911 END IF;
2912
2913 END IF; -- IF (p_system_action... no other actions possible
2914
2915 END IF; -- IF (p
2916
2917 EXCEPTION
2918 WHEN OTHERS THEN
2919 RAISE_APPLICATION_ERROR
2920 (-20000,
2921 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.validate_descriptor(' ||
2922 l_err_loc || '), ' || SQLERRM);
2923 END validate_descriptor;
2924
2925
2926 -- procedure to delete old jobs from the tables
2927 -- (icx_por_batch_jobs, icx_cat_batch_jobs, icx_por_failed_line_messages,
2928 -- icx_por_failed_lines, icx_por_contract_references, icx_cat_parse_errors)
2929 PROCEDURE purge_loader_tables
2930 IS
2931 l_err_loc PLS_INTEGER;
2932 l_number_of_days NUMBER;
2933 l_commit_size NUMBER;
2934 l_job_number_tbl DBMS_SQL.NUMBER_TABLE;
2935 l_continue BOOLEAN := TRUE;
2936 BEGIN
2937
2938 l_err_loc := 100;
2939
2940 -- get the POR_LOAD_PURGE_BEYOND_DAYS and POR_LOAD_PURGE_COMMIT_SIZE profiles
2941 fnd_profile.get('POR_LOAD_PURGE_BEYOND_DAYS', l_number_of_days);
2942 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', l_commit_size);
2943
2944 l_err_loc := 110;
2945
2946 -- this loop is to used to purge tables for pre R12 jobs
2947 WHILE l_continue LOOP
2948
2949 l_err_loc := 120;
2950
2951 DELETE FROM icx_por_batch_jobs
2952 WHERE submission_datetime <= (sysdate - l_number_of_days)
2953 AND rownum <= l_commit_size
2954 RETURNING job_number BULK COLLECT INTO l_job_number_tbl;
2955
2956 l_err_loc := 130;
2957
2958 IF (SQL%ROWCOUNT < l_commit_size) THEN
2959 l_err_loc := 140;
2960 l_continue := FALSE;
2961 END IF;
2962
2963 l_err_loc := 150;
2964
2965 FORALL i IN 1..l_job_number_tbl.COUNT
2966 DELETE FROM icx_por_failed_line_messages
2967 WHERE job_number = l_job_number_tbl(i);
2968
2969 l_err_loc := 160;
2970
2971 FORALL i IN 1..l_job_number_tbl.COUNT
2972 DELETE FROM icx_por_failed_lines
2973 WHERE job_number = l_job_number_tbl(i);
2974
2975 l_err_loc := 170;
2976
2977 FORALL i IN 1..l_job_number_tbl.COUNT
2978 DELETE FROM icx_por_contract_references
2979 WHERE job_number = l_job_number_tbl(i);
2980
2981 l_err_loc := 180;
2982
2983 COMMIT;
2984 END LOOP;
2985
2986 l_err_loc := 200;
2987
2988 l_continue := TRUE;
2989 l_job_number_tbl.DELETE;
2990
2991 -- this loop is to used to purge tables for R12 jobs
2992 WHILE l_continue LOOP
2993
2994 l_err_loc := 210;
2995
2996 DELETE FROM icx_cat_batch_jobs
2997 WHERE submission_datetime <= (sysdate - l_number_of_days)
2998 AND rownum <= l_commit_size
2999 RETURNING job_number BULK COLLECT INTO l_job_number_tbl;
3000
3001 l_err_loc := 220;
3002
3003 IF (SQL%ROWCOUNT < l_commit_size) THEN
3004 l_err_loc := 230;
3005 l_continue := FALSE;
3006 END IF;
3007
3008 l_err_loc := 240;
3009
3010 FORALL i IN 1..l_job_number_tbl.COUNT
3011 DELETE FROM icx_por_failed_line_messages
3012 WHERE job_number = l_job_number_tbl(i);
3013
3014 l_err_loc := 250;
3015
3016 FORALL i IN 1..l_job_number_tbl.COUNT
3017 DELETE FROM icx_por_failed_lines
3018 WHERE job_number = l_job_number_tbl(i);
3019
3020 l_err_loc := 260;
3021
3022 FORALL i IN 1..l_job_number_tbl.COUNT
3023 DELETE FROM icx_cat_parse_errors
3024 WHERE request_id = l_job_number_tbl(i);
3025
3026 l_err_loc := 270;
3027
3028 COMMIT;
3029 END LOOP;
3030
3031 l_err_loc := 280;
3032
3033 EXCEPTION
3034 WHEN OTHERS THEN
3035 ROLLBACK;
3036 RAISE_APPLICATION_ERROR
3037 (-20000,
3038 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.purge_loader_tables(' ||
3039 l_err_loc || '), ' || SQLERRM);
3040 END purge_loader_tables;
3041
3042 -- procedure to populate the ctx desc for schema load
3043 -- this will handle the following cases
3044 -- 1. category name change
3045 -- 2. Change of descriptor searchability
3046 PROCEDURE populate_ctx_desc
3047 (
3048 p_request_id IN NUMBER
3049 )
3050 IS
3051 l_err_loc PLS_INTEGER;
3052
3053 l_category_id NUMBER;
3054 l_descriptor_key VARCHAR2(250);
3055 l_searchable NUMBER;
3056
3057 -- cursor to handle category name change
3058 CURSOR populate_category_csr
3059 IS
3060 SELECT rt_category_id, category_name, language
3061 FROM icx_cat_categories_tl
3062 WHERE request_id = p_request_id
3063 AND rebuild_flag = 'Y';
3064
3065 -- cursor to handle special descriptor update
3066 CURSOR populate_special_descs_csr
3067 IS
3068 SELECT key, searchable
3069 FROM icx_cat_attributes_tl
3070 WHERE request_id = p_request_id
3071 AND rebuild_flag = 'Y'
3072 AND rt_category_id = 0
3073 AND key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
3074 'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
3075 'SHOPPING_CATEGORY');
3076
3077 -- cursor that handles the descriptor update for regular (non-special) descriptors
3078 CURSOR populate_regular_descs_csr
3079 IS
3080 SELECT distinct rt_category_id
3081 FROM icx_cat_attributes_tl
3082 WHERE request_id = p_request_id
3083 AND rebuild_flag = 'Y'
3084 AND key NOT IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
3085 'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
3086 'SHOPPING_CATEGORY');
3087 BEGIN
3088
3089 l_err_loc := 100;
3090
3091 -- set the batch size
3092 ICX_CAT_UTIL_PVT.setBatchSize;
3093
3094 l_err_loc := 103;
3095
3096 -- set commit to true
3097 ICX_CAT_UTIL_PVT.setCommitParameter(FND_API.G_TRUE);
3098
3099 l_err_loc := 105;
3100
3101
3102 -- first we get all the categories whose name has changed
3103 -- and call populate for those
3104 FOR category_row IN populate_category_csr LOOP
3105
3106 l_err_loc := 110;
3107
3108 ICX_CAT_POPULATE_CTXSTRING_PVT.handleCategoryRename(category_row.rt_category_id,
3109 category_row.category_name, category_row.language);
3110
3111 END LOOP;
3112
3113 -- now we get all the special descriptors that have their searchable property changed
3114 l_err_loc := 200;
3115
3116 FOR special_descriptor_row IN populate_special_descs_csr LOOP
3117
3118 l_err_loc := 210;
3119
3120 -- for special descriptors we have individual rows so we call it one
3121 -- descriptor at a time
3122
3123 ICX_CAT_POPULATE_CTXSTRING_PVT.rePopulateBaseAttributes(special_descriptor_row.key,
3124 special_descriptor_row.searchable);
3125
3126 END LOOP;
3127
3128 -- now we get all thed distinct categories for which
3129 -- non-special descriptors have been updated
3130 l_err_loc := 300;
3131
3132 FOR descriptor_cat_row IN populate_regular_descs_csr LOOP
3133
3134 l_err_loc := 310;
3135
3136 -- now get the category id
3137 l_category_id := descriptor_cat_row.rt_category_id;
3138
3139 l_err_loc := 320;
3140
3141 IF (l_category_id = 0) THEN
3142 -- handle update of non-special root descriptors
3143 -- since these are not special we will just call it once for root category
3144 -- and pass nulls as the parameters
3145
3146 l_err_loc := 330;
3147
3148 ICX_CAT_POPULATE_CTXSTRING_PVT.rePopulateBaseAttributes(null, null);
3149
3150 ELSE
3151 -- handle update of category descriptors for this category
3152 -- this is called one category at a time
3153
3154 l_err_loc := 340;
3155
3156 ICX_CAT_POPULATE_CTXSTRING_PVT.rePopulateCategoryAttributes(l_category_id);
3157
3158 END IF;
3159
3160 END LOOP;
3161
3162 l_err_loc := 400;
3163
3164 -- finally we rebuild the index
3165 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
3166
3167 l_err_loc := 500;
3168
3169 -- reset rebuild flag to null
3170 UPDATE icx_cat_categories_tl
3171 SET rebuild_flag = null
3172 WHERE rebuild_flag is not null;
3173
3174 l_err_loc := 600;
3175
3176 -- reset rebuild flag to null
3177 UPDATE icx_cat_attributes_tl
3178 SET rebuild_flag = null
3179 WHERE rebuild_flag is not null;
3180
3181 l_err_loc := 700;
3182
3183 COMMIT;
3184
3185 EXCEPTION
3186 WHEN OTHERS THEN
3187 ROLLBACK;
3188 RAISE_APPLICATION_ERROR
3189 (-20000,
3190 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.populate_ctx_desc(' ||
3191 l_err_loc || '), ' || SQLERRM);
3192 END populate_ctx_desc;
3193
3194 -- methods for online schema
3195 -- submitted through concurrent programs
3196
3197 -- method to populate the ctx desc for category rename
3198 PROCEDURE populate_for_cat_rename
3199 (
3200 x_errbuf OUT NOCOPY VARCHAR2,
3201 x_retcode OUT NOCOPY NUMBER,
3202 p_category_id IN NUMBER,
3203 p_category_name IN VARCHAR2,
3204 p_language IN VARCHAR2
3205 )
3206 IS
3207 l_err_loc PLS_INTEGER;
3208 l_request_id NUMBER;
3209
3210 BEGIN
3211
3212 l_err_loc := 100;
3213
3214 -- get the concurrent request ID
3215 l_request_id := fnd_global.conc_request_id;
3216
3217 l_err_loc := 150;
3218
3219 update_job_status(l_request_id, 'RUNNING');
3220
3221 l_err_loc := 200;
3222
3223 ICX_CAT_POPULATE_CTXSTRING_PVT.handleCategoryRename(p_category_id,
3224 p_category_name, p_language);
3225
3226 l_err_loc := 300;
3227
3228 update_job_status(l_request_id, 'COMPLETED');
3229
3230 l_err_loc := 400;
3231
3232 x_retcode := 0;
3233 x_errbuf := '';
3234
3235 l_err_loc := 500;
3236
3237 EXCEPTION
3238 WHEN OTHERS THEN
3239 ROLLBACK;
3240 x_retcode := 2;
3241 x_errbuf := 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.populate_for_cat_rename(' ||
3242 l_err_loc || '), ' || SQLERRM;
3243 update_job_status(l_request_id, 'ERROR');
3244 END populate_for_cat_rename;
3245
3246 -- method to populate the ctx_desc for a searchability change
3247 PROCEDURE populate_for_searchable_change
3248 (
3249 x_errbuf OUT NOCOPY VARCHAR2,
3250 x_retcode OUT NOCOPY NUMBER,
3251 p_attribute_id IN NUMBER,
3252 p_attribute_key IN VARCHAR2,
3253 p_category_id IN NUMBER,
3254 p_searchable IN NUMBER
3255 )
3256 IS
3257 l_err_loc PLS_INTEGER;
3258 l_request_id NUMBER;
3259
3260 BEGIN
3261
3262 l_err_loc := 100;
3263
3264 -- get the concurrent request ID
3265 l_request_id := fnd_global.conc_request_id;
3266
3267 l_err_loc := 150;
3268
3269 update_job_status(l_request_id, 'RUNNING');
3270
3271 l_err_loc := 200;
3272
3273 ICX_CAT_POPULATE_CTXSTRING_PVT.handleSearchableFlagChange(p_attribute_id,
3274 p_attribute_key, p_category_id, p_searchable);
3275
3276 l_err_loc := 300;
3277
3278 update_job_status(l_request_id, 'COMPLETED');
3279
3280 l_err_loc := 400;
3281
3282 x_retcode := 0;
3283 x_errbuf := '';
3284
3285 l_err_loc := 500;
3286
3287 EXCEPTION
3288 WHEN OTHERS THEN
3289 ROLLBACK;
3290 x_retcode := 2;
3291 x_errbuf := 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.populate_for_searchable_change(' ||
3292 l_err_loc || '), ' || SQLERRM;
3293 update_job_status(l_request_id, 'ERROR');
3294
3295 END populate_for_searchable_change;
3296
3297 -- method to update the status of a job
3298 PROCEDURE update_job_status
3299 (
3300 p_job_number IN NUMBER,
3301 p_job_status IN VARCHAR2
3302 )
3303 IS
3304 l_err_loc PLS_INTEGER;
3305
3306 BEGIN
3307
3308 l_err_loc := 100;
3309
3310 UPDATE icx_cat_batch_jobs
3311 SET job_status = p_job_status,
3312 last_updated_by = fnd_global.user_id,
3313 last_update_date = sysdate,
3314 last_update_login = fnd_global.login_id
3315 WHERE job_number = p_job_number;
3316
3317 l_err_loc := 200;
3318
3319 COMMIT;
3320
3321 l_err_loc := 300;
3322
3323 EXCEPTION
3324 WHEN OTHERS THEN
3325 ROLLBACK;
3326 RAISE_APPLICATION_ERROR
3327 (-20000,
3328 'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(' ||
3329 l_err_loc || '), ' || SQLERRM);
3330 END update_job_status;
3331
3332 END ICX_CAT_SCHEMA_UPLOAD_PVT;
[Home] [Help]