[Home] [Help]
PACKAGE BODY: APPS.AS_CLASSIFICATION_HOOKS
Source
1 PACKAGE BODY AS_CLASSIFICATION_HOOKS as
2 /* $Header: asxccihb.pls 115.7 2002/11/06 00:39:49 appldev ship $ */
3
4
5 procedure update_selectable_flag(p_class_category in varchar2) is
6 begin
7
8 update as_hz_class_code_denorm denorm
9 set selectable_flag = 'N'
10 where denorm.class_category = p_class_category
11 and selectable_flag = 'Y'
12 and exists (select 'x'
13 from hz_class_code_relations rel1,
14 hz_class_code_relations rel2,
15 fnd_lookup_values lv
16 where (rel1.sub_class_code = denorm.class_code
17 or lv.lookup_code = rel1.sub_class_code)
18 and rel2.class_code = denorm.class_code
19 and lv.lookup_type = denorm.class_category
20 and denorm.class_category= rel1.class_category
21 and denorm.class_category= rel2.class_category
22 and denorm.class_category = p_class_category);
23 Exception
24 When Others then
25 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Upd sel flag:' || sqlerrm);
26 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_selectable_flag');
27 end;
28
29 procedure update_class_category_post(p_class_category in varchar2,
30 p_category_meaning in varchar2,
31 p_allow_leaf_node_only_flag in varchar2) is
32
33 begin
34 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Update Category Post Start');
35
36 update as_hz_class_code_denorm
37 set class_category_meaning = p_category_meaning
38 where class_category = p_class_category
39 and language = userenv('LANG')
40 and class_category_meaning <> p_category_meaning;
41
42 if p_allow_leaf_node_only_flag ='Y'
43 then
44 update_selectable_flag(p_class_category);
45 else
46 update as_hz_class_code_denorm denorm
47 set selectable_flag = 'Y'
48 where denorm.class_category = p_class_category
49 and selectable_flag = 'N';
50 end if;
51
52 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Update Category Post End');
53
54 Exception
55 When Others then
56 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Upd Category Post:' || sqlerrm);
57 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_class_category_post');
58
59
60 end;
61
62
63 procedure register_Lookup_code_post(p_lookup_type in varchar2,
64 p_lookup_code in varchar2,
65 p_meaning in varchar2,
66 p_description in varchar2,
67 p_enabled_flag in varchar2,
68 p_start_date_active in date,
69 p_end_date_active in date) is
70 begin
71
72 INSERT INTO AS_HZ_CLASS_CODE_DENORM(
73 CLASS_CATEGORY,
74 CLASS_CATEGORY_MEANING,
75 CLASS_CODE,
76 CLASS_CODE_MEANING,
77 CLASS_CODE_DESCRIPTION,
78 ANCESTOR_CODE,
79 ANCESTOR_MEANING,
80 LANGUAGE,
81 CONCAT_CLASS_CODE,
82 CONCAT_CLASS_CODE_MEANING,
83 CODE_LEVEL,
84 START_DATE_ACTIVE,
85 END_DATE_ACTIVE,
86 ENABLED_FLAG,
87 SELECTABLE_FLAG,
88 SEGMENT1,
89 SEGMENT2,
90 SEGMENT3,
91 SEGMENT4,
92 SEGMENT5,
93 SEGMENT6,
94 SEGMENT7,
95 SEGMENT8,
96 SEGMENT9,
97 SEGMENT10,
98 SEGMENT1_MEANING,
99 SEGMENT2_MEANING,
100 SEGMENT3_MEANING,
101 SEGMENT4_MEANING,
102 SEGMENT5_MEANING,
103 SEGMENT6_MEANING,
104 SEGMENT7_MEANING,
105 SEGMENT8_MEANING,
106 SEGMENT9_MEANING,
107 SEGMENT10_MEANING,
108 CREATED_BY,
109 CREATION_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN,
112 LAST_UPDATE_DATE,
113 REQUEST_ID,
114 PROGRAM_ID,
115 PROGRAM_APPLICATION_ID,
116 PROGRAM_UPDATE_DATE
117 ) select
118 p_lookup_type,
119 meaning,
120 p_lookup_code,
121 p_meaning,
122 p_description,
123 p_lookup_code,
124 p_meaning,
125 userenv('LANG'),
126 p_lookup_code,
127 p_meaning,
128 1,
129 p_start_date_active,
130 p_end_date_active,
131 p_enabled_flag,
132 'Y',
133 'not used',
134 NULL,
135 NULL,
136 NULL,
137 NULL,
138 NULL,
139 NULL,
140 NULL,
141 NULL,
142 NULL,
143 'not used',
144 NULL,
145 NULL,
146 NULL,
147 NULL,
148 NULL,
149 NULL,
150 NULL,
151 NULL,
152 NULL,
153 NVL(FND_GLOBAL.USER_ID,-1),
154 SYSDATE,
155 NVL(FND_GLOBAL.USER_ID,-1),
156 NVL(FND_GLOBAL.LOGIN_ID,-1),
157 SYSDATE,
158 FND_GLOBAL.CONC_REQUEST_ID,
159 FND_GLOBAL.CONC_PROGRAM_ID,
160 FND_GLOBAL.PROG_APPL_ID,
161 SYSDATE
162 from fnd_lookup_types_vl
163 where lookup_type = p_lookup_type;
164
165 Exception
166 When Others then
167 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Reg lookup code Post:' || sqlerrm);
168 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'register_lookup_code_post');
169
170 end;
171
172 -- private function
173 function get_class_code_level(p_class_category in varchar2, p_class_code in varchar2) return
174 varchar2 is
175 cursor get_code_level_csr is
176 select code_level
177 from as_hz_class_code_denorm
178 where class_category = p_class_category
179 and class_code = p_class_code
180 and ancestor_code = p_class_code
181 and language = userenv('LANG');
182
183 l_code_level number;
184 begin
185 open get_code_level_csr;
186 fetch get_code_level_csr into l_code_level;
187 close get_code_level_csr;
188
189 return l_code_level;
190 end;
191
192 function get_code_meaning(p_type in varchar2, p_code in varchar2, p_language in varchar2) return varchar2 is
193 cursor get_code_meaning_csr is
194 select meaning
195 from fnd_lookup_values
196 where lookup_type = p_type
197 and language = p_language
198 and lookup_code = p_code;
199
200 l_meaning varchar2(80);
201 begin
202 open get_code_meaning_csr;
203 fetch get_code_meaning_csr into l_meaning;
204 if get_code_meaning_csr%FOUND
205 then
206 close get_code_meaning_csr;
207 return(l_meaning);
208 else
209 close get_code_meaning_csr;
210 return null;
211 end if;
212
213
214 end;
215 function get_concat_meaning(p_type in varchar2, p_curr_code in varchar2,p_language in varchar2) return varchar2 is
216
217 cursor get_class_code_hierarchy_csr(p_type varchar2,p_curr_code varchar2) is
218
219 select a.class_code
220 from (select class_code, sub_class_code from hz_class_code_relations
221 where
222 sysdate between start_date_active and nvl(end_date_active,sysdate)
223 and class_category = p_type) a
224 start with sub_class_code = p_curr_code
225 connect by sub_class_code = prior class_code;
226
227
228 l_class_code varchar2(30);
229 l_sub_class_code varchar2(30);
230 l_level number;
231 l_curr_code varchar2(30);
232 l_concat_code varchar2(2000);
233 l_concat_meaning varchar2(2000);
234
235 begin
236
237 l_concat_meaning := get_code_meaning(p_type,p_curr_code,p_language);
238 open get_class_code_hierarchy_csr(p_type,p_curr_code);
239 loop
240 fetch get_class_code_hierarchy_csr into l_class_code;
241 exit when get_class_code_hierarchy_csr%NOTFOUND;
242 l_concat_meaning := get_code_meaning(p_type,l_class_code,p_language) ||'/'||l_concat_meaning;
243 end loop;
244 close get_class_code_hierarchy_csr;
245 return l_concat_meaning;
246 end;
247 function get_concat_code(p_class_category in varchar2,p_curr_code in varchar2) return varchar2 is
248
249 cursor get_class_code_hierarchy_csr is
250 select a.class_code
251 from (select class_code, sub_class_code from hz_class_code_relations
252 where
253 sysdate between start_date_active and nvl(end_date_active,sysdate)
254 and class_category = p_class_category) a
255 start with sub_class_code = p_curr_code
256 connect by sub_class_code = prior class_code;
257
258
259 l_class_code varchar2(30);
260 l_sub_class_code varchar2(30);
261 l_level number;
262 l_curr_code varchar2(30);
263 l_concat_code varchar2(2000);
264
265 begin
266
267 l_concat_code := p_curr_code;
268 open get_class_code_hierarchy_csr;
269 loop
270 fetch get_class_code_hierarchy_csr into l_class_code;
271 exit when get_class_code_hierarchy_csr%NOTFOUND;
272 l_concat_code:= l_class_code ||'/'||l_concat_code;
273 end loop;
274 close get_class_code_hierarchy_csr;
275 return l_concat_code;
276 end;
277
278 procedure update_concate_meaning(p_class_category in varchar2, p_curr_code in varchar2) is
279 cursor get_language_csr is
280 select language
281 from fnd_lookup_types_tl
282 where lookup_type = p_class_category;
283 l_language varchar2(30);
284 l_concat_meaning varchar2(2000);
285 begin
286 open get_language_csr;
287 loop
288 fetch get_language_csr into l_language;
289 l_concat_meaning := get_concat_meaning(p_class_category,p_curr_code,l_language);
290 update as_hz_class_code_denorm
291 set concat_class_code_meaning = l_concat_meaning
292 where class_category = p_class_category
293 and class_code = p_curr_code
294 and language = l_language;
295 exit when get_language_csr%NOTFOUND;
296 end loop;
297 close get_language_csr;
298 end;
299
300
301 procedure update_Lookup_code_post(p_lookup_type in varchar2,
302 p_lookup_code in varchar2,
303 p_meaning in varchar2,
304 p_description in varchar2,
305 p_enabled_flag in varchar2,
306 p_start_date_active in date,
307 p_end_date_active in date) is
308
309 cursor get_code_meaning_csr is
310 select class_code_meaning
311 from as_hz_class_code_denorm
312 where class_category = p_lookup_type
313 and class_code = p_lookup_code
314 and ancestor_code = p_lookup_code
315 and language = userenv('LANG');
316 l_old_meaning varchar2(80);
317
318 begin
319 open get_code_meaning_csr;
320 fetch get_code_meaning_csr into l_old_meaning;
321 close get_code_meaning_csr;
322
323 update as_hz_class_code_denorm
324 set enabled_flag = p_enabled_flag,
325 start_date_active = p_start_date_active,
326 end_date_active = p_end_date_active
327 where class_category = p_lookup_type
328 and class_code = p_lookup_code;
329
330 update as_hz_class_code_denorm
331 set ancestor_meaning = p_meaning
332 where class_category = p_lookup_type
333 and ancestor_code = p_lookup_code
334 and language = userenv('LANG');
335
336 -- Code meaning change related to language.
337 -- Therefore, need to be handled seperately
338 -- segment1 to 4 only used in total mode, not online mode,data change
339 -- will not be maintained.
340
341 update as_hz_class_code_denorm
342 set
343 class_code_meaning = p_meaning,
344 class_code_description = p_description,
345 segment1_meaning = 'not used',
346 segment2_meaning = null,
347 segment3_meaning = null,
348 segment4_meaning = null
349 where class_category = p_lookup_type
350 and class_code = p_lookup_code
351 and language = userenv('LANG')
352 and (class_code_meaning <> p_meaning
353 or nvl(class_code_description, '#@#') <> nvl(p_description, '#@#'));
354
355 update as_hz_class_code_denorm
356 set
357 concat_class_code_meaning = replace(concat_class_code_meaning,l_old_meaning, p_meaning)
358 where class_category = p_lookup_type
359 and instr(concat_class_code_meaning,l_old_meaning)>0
360 and language = userenv('LANG');
361
362 Exception
363 When Others then
364 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Upd lookup code Post:' || sqlerrm);
365 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_lookup_code_post');
366
367 end;
368
369 -- private procedure
370 procedure insert_current_code_relation(p_class_category in varchar2,
371 p_class_code in varchar2,
372 p_sub_class_code in varchar2
373 ) is
374 cursor get_leaf_node_flag_csr is
375 select allow_leaf_node_only_Flag
376 from hz_class_categories
377 where class_category = p_class_category;
378 l_concat_code varchar2(500);
379 l_concat_meaning varchar2(2000);
380 l_leaf_node_flag varchar2(1);
381 l_code_level number;
382 begin
383 l_concat_code := get_concat_code(p_class_category,p_sub_class_code);
384
385 l_code_level := get_class_code_level(p_class_category,p_class_code);
386 if l_code_level =1
387 then
388
389 -- update those rows inserted in register_lookup_code_post with code relations
393 update as_hz_class_code_denorm ccd
390 -- Due to language concern and func can't be called in the update,
391 -- concat meaning will be handled seperately.
392
394 set code_level = 2,
395 concat_class_code = l_concat_code
396 where class_category = p_class_category
397 and class_code = p_sub_class_code;
398
399 insert into as_hz_class_code_denorm (
400 class_category,
401 class_category_meaning,
402 class_code,
403 class_code_meaning,
404 class_code_description,
405 ancestor_code,
406 ancestor_meaning,
407 language,
408 concat_class_code,
409 concat_class_code_meaning,
410 code_level,
411 start_date_active,
412 end_date_active,
413 enabled_flag,
414 selectable_flag,
415 segment1,
416 segment2,
417 segment3,
418 segment4,
419 segment5,
420 segment6,
421 segment7,
422 segment8,
423 segment9,
424 segment10,
425 segment1_meaning,
426 segment2_meaning,
427 segment3_meaning,
428 segment4_meaning,
429 segment5_meaning,
430 segment6_meaning,
431 segment7_meaning,
432 segment8_meaning,
433 segment9_meaning,
434 segment10_meaning,
435 created_by,
436 creation_date,
437 last_updated_by,
438 last_update_login,
439 last_update_date,
440 request_id,
441 program_id,
442 program_application_id,
443 program_update_date
444 )
445 select
446 class_category,
447 class_category_meaning,
448 p_sub_class_code,
449 lv.meaning,
450 lv.description,
451 ancestor_code,
452 ancestor_meaning,
453 lv.language,
454 l_concat_code,
455 'temp-meaning',
456 2,
457 lv.start_date_active,
458 lv.end_date_active,
459 lv.enabled_flag,
460 'Y',
461 'not used',
462 NULL,
463 NULL,
464 NULL,
465 NULL,
466 NULL,
467 NULL,
468 NULL,
469 NULL,
470 NULL,
471 'not used',
472 NULL,
473 NULL,
474 NULL,
475 NULL,
476 NULL,
477 NULL,
478 NULL,
479 NULL,
480 NULL,
481 nvl(fnd_global.user_id,-1),
482 sysdate,
483 nvl(fnd_global.user_id,-1),
484 nvl(fnd_global.login_id,-1),
485 sysdate,
486 fnd_global.conc_request_id,
487 fnd_global.conc_program_id,
488 fnd_global.prog_appl_id,
489 sysdate
490 from
491 as_hz_class_code_denorm denorm,
492 fnd_lookup_values lv
493 where
494 denorm.class_category = lv.lookup_type
495 and denorm.language = lv.language
496 and denorm.class_category = p_class_category
497 and denorm.class_code = p_class_code
498 and lv.lookup_code = p_sub_class_code
499 and denorm.code_level = 1;
500
501 elsif l_code_level =2
502 then
503
504 -- update those rows inserted in register_lookup_code_post with code relations
505 update as_hz_class_code_denorm ccd
506 set code_level = 3,
507 concat_class_code = l_concat_code
508 where class_code = p_sub_class_code
509 and class_category = p_class_category;
510
511 insert into as_hz_class_code_denorm (
512 class_category,
513 class_category_meaning,
514 class_code,
515 class_code_meaning,
516 class_code_description,
517 ancestor_code,
518 ancestor_meaning,
519 language,
520 concat_class_code,
521 concat_class_code_meaning,
522 code_level,
523 start_date_active,
524 end_date_active,
525 enabled_flag,
526 selectable_flag,
527 segment1,
528 segment2,
529 segment3,
530 segment4,
531 segment5,
532 segment6,
533 segment7,
534 segment8,
535 segment9,
536 segment10,
537 segment1_meaning,
538 segment2_meaning,
539 segment3_meaning,
540 segment4_meaning,
541 segment5_meaning,
542 segment6_meaning,
543 segment7_meaning,
544 segment8_meaning,
545 segment9_meaning,
546 segment10_meaning,
547 created_by,
548 creation_date,
549 last_updated_by,
550 last_update_login,
551 last_update_date,
552 request_id,
553 program_id,
554 program_application_id,
555 program_update_date
556 )
557 select
558 class_category,
559 class_category_meaning,
560 p_sub_class_code,
561 lv.meaning,
562 lv.description,
563 ancestor_code,
564 ancestor_meaning,
565 lv.language,
566 l_concat_code,
567 'temp-meaning',
568 3,
569 lv.start_date_active,
570 lv.end_date_active,
571 lv.enabled_flag,
572 'Y',
573 'not used',
574 NULL,
575 NULL,
576 NULL,
577 NULL,
578 NULL,
579 NULL,
580 NULL,
581 NULL,
582 NULL,
583 'not used',
584 NULL,
585 NULL,
586 NULL,
587 NULL,
588 NULL,
589 NULL,
590 NULL,
591 NULL,
592 NULL,
593 nvl(fnd_global.user_id,-1),
594 sysdate,
598 fnd_global.conc_request_id,
595 nvl(fnd_global.user_id,-1),
596 nvl(fnd_global.login_id,-1),
597 sysdate,
599 fnd_global.conc_program_id,
600 fnd_global.prog_appl_id,
601 sysdate
602 from
603 as_hz_class_code_denorm denorm,
604 fnd_lookup_values lv
605 where
606 denorm.class_category = lv.lookup_type
607 and denorm.language = lv.language
608 and denorm.class_category = p_class_category
609 and denorm.class_code = p_class_code
610 and lv.lookup_code = p_sub_class_code
611 and denorm.code_level = 2;
612
613 elsif l_code_level =3
614 then
615
616 -- update those rows inserted in register_lookup_code_post with code relations
617 update as_hz_class_code_denorm ccd
618 set code_level = 4,
619 concat_class_code = l_concat_code
620 where class_code = p_sub_class_code
621 and class_category = p_class_category;
622
623 insert into as_hz_class_code_denorm (
624 class_category,
625 class_category_meaning,
626 class_code,
627 class_code_meaning,
628 class_code_description,
629 ancestor_code,
630 ancestor_meaning,
631 language,
632 concat_class_code,
633 concat_class_code_meaning,
634 code_level,
635 start_date_active,
636 end_date_active,
637 enabled_flag,
638 selectable_flag,
639 segment1,
640 segment2,
641 segment3,
642 segment4,
643 segment5,
644 segment6,
645 segment7,
646 segment8,
647 segment9,
648 segment10,
649 segment1_meaning,
650 segment2_meaning,
651 segment3_meaning,
652 segment4_meaning,
653 segment5_meaning,
654 segment6_meaning,
655 segment7_meaning,
656 segment8_meaning,
657 segment9_meaning,
658 segment10_meaning,
659 created_by,
660 creation_date,
661 last_updated_by,
662 last_update_login,
663 last_update_date,
664 request_id,
665 program_id,
666 program_application_id,
667 program_update_date
668 )
669 select
670 class_category,
671 class_category_meaning,
672 p_sub_class_code,
673 lv.meaning,
674 lv.description,
675 ancestor_code,
676 ancestor_meaning,
677 lv.language,
678 l_concat_code,
679 'temp-meaning',
680 4,
681 lv.start_date_active,
682 lv.end_date_active,
683 lv.enabled_flag,
684 'Y',
685 segment1,
686 NULL,
687 NULL,
688 NULL,
689 NULL,
690 NULL,
691 NULL,
692 NULL,
693 NULL,
694 NULL,
695 segment1_meaning,
696 NULL,
697 NULL,
698 NULL,
699 NULL,
700 NULL,
701 NULL,
702 NULL,
703 NULL,
704 NULL,
705 nvl(fnd_global.user_id,-1),
706 sysdate,
707 nvl(fnd_global.user_id,-1),
708 nvl(fnd_global.login_id,-1),
709 sysdate,
710 fnd_global.conc_request_id,
711 fnd_global.conc_program_id,
712 fnd_global.prog_appl_id,
713 sysdate
714 from
715 as_hz_class_code_denorm denorm,
716 fnd_lookup_values lv
717 where
718 denorm.class_category = lv.lookup_type
719 and denorm.language = lv.language
720 and denorm.class_category = p_class_category
721 and denorm.class_code = p_class_code
722 and lv.lookup_code = p_sub_class_code
723 and denorm.code_level = 3;
724 end if;
725
726 -- Due to language concern, meaning needs to be handled seperately.
727 update_concate_meaning(p_class_category,p_sub_class_code);
728
729 open get_leaf_node_flag_csr;
730 fetch get_leaf_node_flag_csr into l_leaf_node_flag;
731 close get_leaf_node_flag_csr;
732 if l_leaf_node_flag = 'Y'
733 then
734 update_selectable_flag(p_class_category);
735 end if;
736 end;
737
738 procedure create_class_code_rel_post(p_class_category in varchar2,
739 p_class_code in varchar2,
740 p_sub_class_code in varchar2,
741 p_start_date_active in date,
742 p_end_date_active in date
743 ) is
744 l_code_level number;
745 begin
746 l_code_level := get_class_code_level(p_class_category,p_class_code);
747 if l_code_level < 4
748 and sysdate between p_start_date_active and nvl(p_end_date_active,sysdate)
749 then
750 insert_current_code_relation(p_class_category,p_class_code,p_sub_class_code);
751 end if;
752
753 -- no code level > 4 case, since we only allow 4 level
754 if l_code_level = 4
755 then
756 -- delete extra data entered from register_lookup_code_post where level > 4 for sub code.
757 delete
758 from as_hz_class_code_denorm
759 where class_category = p_class_category
760 and class_code = p_sub_class_code
761 and code_level = 1;
762 end if;
763
764 Exception
765 When Others then
766 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Cre code rel Post:' || sqlerrm);
767 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'create_class_code_rel_post');
768
769 end;
770
774 p_end_date_active in date) is
771 procedure update_class_code_rel_post(p_class_category in varchar2,
772 p_class_code in varchar2,
773 p_sub_class_code in varchar2,
775 cursor get_sub_sub_class_code is
776 select sub_class_code
777 from hz_class_code_relations
778 where class_category = p_class_category
779 and p_class_code = p_sub_class_code
780 and sysdate between start_date_active and nvl(end_date_active,sysdate);
781
782 cursor get_concat_meaning_csr is
783 select lookup_code,language,as_classification_hooks.get_concat_meaning(lookup_type,lookup_code,language)
784 from fnd_lookup_values
785 where lookup_type = p_class_category;
786
787 cursor get_concat_code_csr is
788 select lookup_code,as_classification_hooks.get_concat_code(lookup_type,lookup_code)
789 from fnd_lookup_values
790 where lookup_type = p_class_category;
791
792 cursor code_relation_exist_csr is
793 select 'x'
794 from as_hz_class_code_denorm
795 where class_category = p_class_category
796 and class_code = p_sub_class_code
797 and ancestor_code = p_class_code
798 and language = userenv('LANG');
799
800 l_code_level number;
801 l_sub_sub_code varchar2(30);
802 l_concat_meaning varchar2(2000);
803 l_concat_code varchar2(500);
804 l_class_code varchar2(30);
805 l_language varchar2(30);
806 l_tmp varchar2(1);
807 begin
808
809 open get_concat_code_csr;
810 loop
811 fetch get_concat_code_csr into l_class_code,l_concat_code;
812 exit when get_concat_code_csr%NOTFOUND;
813 update as_hz_class_code_denorm
814 set concat_class_code = l_concat_code
815 where class_category = p_class_category
816 and class_code = l_class_code;
817
818 end loop;
819 close get_concat_code_csr;
820 open get_concat_meaning_csr;
821 loop
822 fetch get_concat_meaning_csr into l_class_code,l_language,l_concat_meaning;
823 exit when get_concat_meaning_csr%NOTFOUND;
824 update as_hz_class_code_denorm
825 set concat_class_code_meaning = l_concat_meaning
826 where class_category = p_class_category
827 and class_code = l_class_code
828 and language = l_language;
829 end loop;
830 close get_concat_meaning_csr;
831
832 l_code_level := get_class_code_level(p_class_category,p_sub_class_code);
833 if nvl(p_end_date_active,sysdate) < sysdate
834 then
835 -- end date a relationship will not affect any rows with code_level=1
836 -- since level = 1 means self relationship only
837
838 if l_code_level =2
839 then
840 -- for example, code relation from parent to child like 1->2->3->4,
841 -- if end date relation between 1 and 2, the hierarchy will look like 1, 2->3->4
842 -- code relation 1->2, 1->3, 1->4 need to be deleted
843
844 delete
845 from as_hz_class_code_denorm
846 where class_category = p_class_category
847 and ancestor_code = p_class_code
848 and class_code <> p_class_code;
849
850 update as_hz_class_code_denorm
851 set code_level = code_level-1
852 where class_category = p_class_category
853 and code_level <> 1;
854
855 -- if end date relation between 2 and 3, the hierarchy will look like 1->2,3->4
856 -- code relation 1->3,1->4, 2->3,2->4 need to be deleted.
857 -- since maximum level = 4, we only need to set 3 to level 1 and 4 to level 2
858 elsif l_code_level = 3
859 then
860 open get_sub_sub_class_code;
861 fetch get_sub_sub_class_code into l_sub_sub_code;
862 close get_sub_sub_class_code;
863
864 delete
865 from as_hz_class_code_denorm
866 where class_category = p_class_category
867 and ancestor_code <> p_sub_class_code
868 and class_code = p_sub_class_code
869 or (class_code = l_sub_sub_code and ancestor_code not in (l_sub_sub_code,p_sub_class_code));
870
871 update as_hz_class_code_denorm
872 set code_level = 1
873 where class_category = p_class_category
874 and class_code = p_sub_class_code;
875
876 update as_hz_class_code_denorm
877 set code_level = 2
878 where class_category = p_class_category
879 and class_code = l_sub_sub_code;
880
881 -- if end date relation between 3 and 4, the hierarchy will look like 1->2->3, 4
882 -- relation 1->4,2->4,3->4 need to be deleted
883
884 elsif l_code_level = 4
885 then
886 delete
887 from as_hz_class_code_denorm
888 where class_category = p_class_category
889 and class_code = p_sub_class_code
890 and ancestor_code<>p_sub_class_code;
891
892 update as_hz_class_code_denorm
893 set code_level = 1
894 where class_category = p_class_category
895 and class_code = p_sub_class_code
899 end if;
896 and ancestor_code = p_sub_class_code;
897
898 end if;
900
901 -- if end_date is change to active again
902 if nvl(p_end_date_active,sysdate) >= sysdate
903 then
904 open code_relation_exist_csr;
905 fetch code_relation_exist_csr into l_tmp;
906 if code_relation_exist_csr%NOTFOUND
907 then
908 insert_current_code_relation(p_class_category,p_class_code,p_sub_class_code);
909 end if;
910 close code_relation_exist_csr;
911 end if;
912 Exception
913 When Others then
914 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,'Error in Upd code rel Post:' || sqlerrm);
915 FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_class_code_rel_post');
916
917 end;
918
919 END AS_CLASSIFICATION_HOOKS;