[Home] [Help]
PACKAGE BODY: APPS.HZ_MIXNM_WEBUI_UTILITY
Source
1 PACKAGE BODY HZ_MIXNM_WEBUI_UTILITY AS
2 /*$Header: ARHXWUTB.pls 120.3 2005/01/20 16:05:40 dmmehta noship $ */
3
4 --------------------------------------
5 -- private global constants
6 --------------------------------------
7
8 -- rule type
9
10 G_USER_CREATE_RULE_TYPE CONSTANT VARCHAR2(30) := 'USER_CREATE_RULE';
11 G_USER_OVERWRITE_RULE_TYPE CONSTANT VARCHAR2(30) := 'USER_OVERWRITE_RULE';
12
13 G_ATTRIBUTE_GROUP_NAME_TAB VARCHARList;
14 G_ATTRIBUTE_GROUP_ISTART_TAB IDList;
15 G_ATTRIBUTE_GROUP_IEND_TAB IDList;
16 G_ATTRIBUTE_GROUP_ID_TAB IDList;
17 G_ATTRIBUTE_GROUP_LOADED VARCHAR2(1) := 'N';
18
19 --------------------------------------
20 -- private procedures and functions
21 --------------------------------------
22
23 /**
24 * PRIVATE FUNCTION Get_Index
25 *
26 * DESCRIPTION
27 * Linear search on a given list for given value. Return the
28 * l_index if the value is in the list. Otherwise, return 0.
29 *
30 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
31 *
32 * ARGUMENTS
33 * IN:
34 * p_list VARCHAR2 list.
35 * p_value Value wants to search for.
36 *
37 * NOTES
38 *
39 * MODIFICATION HISTORY
40 *
41 * 03-01-2002 Jianying Huang o Created.
42 */
43
44 FUNCTION Get_Index (
45 p_list IN VARCHARList,
46 p_value IN VARCHAR2
47 ) RETURN NUMBER IS
48 BEGIN
49
50 FOR i IN 1..p_list.COUNT LOOP
51 IF p_value = p_list(i) THEN
52 RETURN i;
53 END IF;
54 END LOOP;
55 RETURN 0;
56
57 END Get_Index;
58
59 /**
60 * PRIVATE PROCEDURE Load_Group
61 *
62 * DESCRIPTION
63 * Load attribute grouping
64 *
65 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
66 *
67 * ARGUMENTS
68 *
69 * NOTES
70 *
71 * MODIFICATION HISTORY
72 *
73 * 03-01-2002 Jianying Huang o Created.
74 */
75
76 PROCEDURE Load_Group (p_entity IN VARCHAR2) IS
77
78 CURSOR c_group IS
79 SELECT attribute_group_name, entity_attr_id
80 FROM hz_entity_attributes
81 WHERE attribute_name IS NOT NULL
82 AND entity_name = p_entity
83 ORDER BY attribute_group_name;
84
85 l_group VARCHARList;
86 l_group_id IDList;
87 j NUMBER := 1;
88 total NUMBER := 0;
89
90 BEGIN
91
92 IF G_ATTRIBUTE_GROUP_LOADED = 'Y' THEN
93 RETURN;
94 END IF;
95
96 OPEN c_group;
97 FETCH c_group BULK COLLECT INTO l_group, G_ATTRIBUTE_GROUP_ID_TAB;
98 CLOSE c_group;
99
100 FOR i IN 1..l_group.COUNT+1 LOOP
101 IF i = l_group.COUNT+1 OR
102 (i > 1 AND
103 l_group(i-1) <> l_group(i))
104 THEN
105 IF total > 1 THEN
106 G_ATTRIBUTE_GROUP_NAME_TAB(j) := l_group(i-1);
107 G_ATTRIBUTE_GROUP_ISTART_TAB(j) := i-total;
108 G_ATTRIBUTE_GROUP_IEND_TAB(j) := i-1;
109 j := j + 1;
110 END IF;
111
112 IF i = l_group.COUNT+1 THEN
113 EXIT;
114 END IF;
115
116 total := 0;
117 END IF;
118 total := total + 1;
119 END LOOP;
120
121 IF (p_entity = 'HZ_ORGANIZATION_PROFILES') THEN
122 G_ATTRIBUTE_GROUP_LOADED := 'O';
123 ELSIF (p_entity = 'HZ_PERSON_PROFILES') THEN
124 G_ATTRIBUTE_GROUP_LOADED := 'P';
125 END IF;
126
127 END Load_Group;
128
129 /**
130 * PROCEDURE Get_NameListInAGroup
131 *
132 * DESCRIPTION
133 * Return the attribute list in a given group.
134 *
135 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
136 *
137 * ARGUMENTS
138 * IN:
139 * p_group_name Group name.
140 * OUT:
141 * x_group_id Attribute id list in a group.
142 *
143 * NOTES
144 *
145 * MODIFICATION HISTORY
146 *
147 * 07-23-2001 Jianying Huang o Created.
148 *
149 */
150
151 PROCEDURE Get_NameListInAGroup (
152 p_group_name IN VARCHAR2,
153 x_group_id OUT NOCOPY IDList,
154 p_entity_attr_id IN NUMBER
155 ) IS
156
157 l_index NUMBER;
158 j NUMBER := 1;
159 l_entity VARCHAR2(30);
160
161 CURSOR entity_name IS
162 SELECT entity_name
163 FROM hz_entity_attributes
164 WHERE entity_attr_id = p_entity_attr_id;
165
166 BEGIN
167
168 OPEN entity_name;
169 FETCH entity_name into l_entity;
170 CLOSE entity_name;
171
172 IF G_ATTRIBUTE_GROUP_LOADED = 'N' THEN
173 Load_Group(l_entity);
174 ELSIF (l_entity = 'HZ_ORGANIZATION_PROFILES' and G_ATTRIBUTE_GROUP_LOADED <> 'O') THEN
175 Load_Group(l_entity);
176 ELSIF (l_entity = 'HZ_PERSON_PROFILES' and G_ATTRIBUTE_GROUP_LOADED <> 'P') THEN
177 Load_Group(l_entity);
178 END IF;
179
180 IF p_group_name IS NULL THEN
181 RETURN;
182 END IF;
183
184 l_index := Get_Index(G_ATTRIBUTE_GROUP_NAME_TAB, p_group_name);
185 IF l_index > 0 THEN
186 FOR i IN G_ATTRIBUTE_GROUP_ISTART_TAB(l_index)..G_ATTRIBUTE_GROUP_IEND_TAB(l_index) LOOP
187 x_group_id(j) := G_ATTRIBUTE_GROUP_ID_TAB(i);
188 j := j + 1;
189 END LOOP;
190 END IF;
191
192 END Get_NameListInAGroup;
193
194 /**
195 * PRIVATE PROCEDURE Process_Group
196 *
197 * DESCRIPTION
198 * Processing attribute group.
199 *
200 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
201 *
202 * ARGUMENTS
203 * IN:
204 * p_entity_attr_id_tab Entity / attribute id list.
205 * p_attribute_group_name_tab A list of attribute group name.
206 * p_flag_tab A list of creation / overwrite flags for each
207 * entity / attribute.
208 * OUT:
209 * x_entity_attr_id_tab Entity / attribute id list.
210 * x_flag_tab A list of creation / overwrite flags for each
211 * entity / attribute.
212 *
213 * NOTES
214 *
215 * MODIFICATION HISTORY
216 *
217 * 03-01-2002 Jianying Huang o Created.
218 */
219
220 PROCEDURE Process_Group (
221 p_entity_attr_id_tab IN IDList,
222 p_attribute_group_name_tab IN VARCHARList,
223 p_flag_tab IN VARCHARList,
224 p_os_tab IN VARCHARList,
225 x_entity_attr_id_tab OUT NOCOPY IDList,
226 x_flag_tab OUT NOCOPY VARCHARList,
227 x_os_tab OUT NOCOPY VARCHARList
228 ) IS
229
230 l_group_id IDList;
231 l_entity_attr_id NUMBER;
232 l_flag VARCHAR2(1);
233 l_start NUMBER;
234 k NUMBER := 1;
235 l_os VARCHAR2(30);
236
237 BEGIN
238
239 x_entity_attr_id_tab := p_entity_attr_id_tab;
240 x_flag_tab := p_flag_tab;
241 x_os_tab := p_os_tab;
242
243 FOR i IN 1..p_attribute_group_name_tab.COUNT LOOP
244 Get_NameListInAGroup (
245 p_group_name => p_attribute_group_name_tab(i),
246 x_group_id => l_group_id ,
247 p_entity_attr_id => p_entity_attr_id_tab(i));
248
249 IF l_group_id.COUNT <> 0 THEN
250 l_entity_attr_id := p_entity_attr_id_tab(i);
251 l_flag := p_flag_tab(i);
252 l_start := x_entity_attr_id_tab.COUNT;
253 l_os := p_os_tab(i);
254 k := 1;
255
256 FOR j IN 1..l_group_id.COUNT LOOP
257 IF l_group_id(j) <> l_entity_attr_id THEN
258 x_entity_attr_id_tab(l_start+k) := l_group_id(j);
259 x_flag_tab(l_start+k) := l_flag;
260 x_os_tab(l_start+k) := l_os;
261 k := k + 1;
262 END IF;
263 END LOOP;
264 END IF;
265 END LOOP;
266
267 END Process_Group;
268
269 /**
270 * PRIVATE PROCEDURE Get_Group
271 *
272 * DESCRIPTION
273 * Get grouping info.
274 *
275 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
276 *
277 * ARGUMENTS
278 * IN:
279 * p_attribute_group_name Attribute group name.
280 * OUT:
281 * x_entity_attr_id_tab Entity / attribute id list.
282 *
283 * NOTES
284 *
285 * MODIFICATION HISTORY
286 *
287 * 03-01-2002 Jianying Huang o Created.
288 */
289 /*
290 PROCEDURE Get_Group (
291 p_attribute_group_name IN VARCHAR2,
292 x_entity_attr_id_tab OUT NOCOPY IDList
293 ) IS
294
295 l_group_id IDList;
296
297 BEGIN
298
299 Get_NameListInAGroup (
300 p_group_name => p_attribute_group_name,
301 x_group_id => l_group_id );
302
303 IF l_group_id.COUNT <> 0 THEN
304 FOR i IN 1..l_group_id.COUNT LOOP
305 x_entity_attr_id_tab(i) := l_group_id(i);
306 END LOOP;
307 END IF;
308
309 END Get_Group;
310 */
311 /**
312 * PRIVATE PROCEDURE Get_Group
313 *
314 * DESCRIPTION
315 * Get grouping info.
316 *
317 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
318 *
319 * ARGUMENTS
320 * IN:
321 * p_entity_attr_id_tab Entity / attribute id list.
322 * p_attribute_group_name_tab A list of attribute group name.
323 * entity / attribute.
324 * OUT:
325 * x_entity_attr_id_tab Entity / attribute id list.
326 *
327 * NOTES
328 *
329 * MODIFICATION HISTORY
330 *
331 * 03-01-2002 Jianying Huang o Created.
332 */
333
334 PROCEDURE Get_Group (
335 p_entity_attr_id_tab IN IDList,
336 p_attribute_group_name_tab IN VARCHARList,
337 x_entity_attr_id_tab OUT NOCOPY IDList
338 ) IS
339
340 l_group_id IDList;
341 l_entity_attr_id NUMBER;
342 l_start NUMBER;
343 k NUMBER := 1;
344
345 BEGIN
346
347 x_entity_attr_id_tab := p_entity_attr_id_tab;
348
349 FOR i IN 1..p_attribute_group_name_tab.COUNT LOOP
350 Get_NameListInAGroup (
351 p_group_name => p_attribute_group_name_tab(i),
352 x_group_id => l_group_id,
353 p_entity_attr_id => p_entity_attr_id_tab(i));
354
355 IF l_group_id.COUNT <> 0 THEN
356 l_entity_attr_id := p_entity_attr_id_tab(i);
357 l_start := x_entity_attr_id_tab.COUNT;
358 k := 1;
359
360 FOR j IN 1..l_group_id.COUNT LOOP
361 IF l_group_id(j) <> l_entity_attr_id THEN
362 x_entity_attr_id_tab(l_start+k) := l_group_id(j);
363 k := k + 1;
364 END IF;
365 END LOOP;
366 END IF;
367 END LOOP;
368
369 END Get_Group;
370
371 --------------------------------------
372 -- public procedures and functions
373 --------------------------------------
374
375 /**
376 * PROCEDURE Create_Rule
377 *
378 * DESCRIPTION
379 * Create an user creation / overwrite rule.
380 *
381 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
382 *
383 * ARGUMENTS
384 * IN:
385 * p_rule_type Rule type. 'USER_CREATE_RULE' is for user create rule.
386 * 'USER_OVERWRITE_RULE is for user overwrite rule.
387 * p_rule_name Rule name.
388 * p_entity_attr_id_tab Entity / attribute id list.
389 * p_attribute_group_name_tab A list of attribute group name.
390 * p_flag_tab A list of creation / overwrite flags for each
391 * entity / attribute.
392 * IN/OUT:
393 * p_rule_id Rule id.
394 *
395 * NOTES
396 *
397 * MODIFICATION HISTORY
398 *
399 * 03-01-2002 Jianying Huang o Created.
400 */
401
402 PROCEDURE Create_Rule (
403 p_rule_type IN VARCHAR2,
404 p_rule_id IN OUT NOCOPY NUMBER,
405 p_rule_name IN VARCHAR2,
406 p_entity_attr_id_tab IN IDList,
407 p_attribute_group_name_tab IN VARCHARList,
408 p_flag_tab IN VARCHARList,
409 p_os_tab IN VARCHARList
410 ) IS
411
412 l_entity_attr_id_tab IDList;
413 l_flag_tab VARCHARList;
414 l_os_tab VARCHARList;
415
416 BEGIN
417
418 -- create a new rule if rule_id is not passed in.
419
420 IF p_rule_id IS NULL THEN
421 SELECT hz_ext_data_rules_tl_s.NEXTVAL
422 INTO p_rule_id
423 FROM DUAL;
424
425 HZ_EXT_DATA_RULES_PKG.INSERT_ROW (
426 p_rule_id, p_rule_type, p_rule_name );
427 END IF;
428
429 IF p_rule_type = G_USER_CREATE_RULE_TYPE THEN
430
431 -- create user create rule.
432
433 FORALL i IN 1..p_entity_attr_id_tab.COUNT
434 INSERT INTO hz_user_create_rules (
435 rule_id,
436 entity_attr_id,
437 creation_flag,
438 created_by,
439 creation_date,
440 last_update_login,
441 last_update_date,
442 last_updated_by
443 ) VALUES (
444 p_rule_id,
445 p_entity_attr_id_tab(i),
446 p_flag_tab(i),
447 hz_utility_v2pub.created_by,
448 SYSDATE,
449 hz_utility_v2pub.last_update_login,
450 SYSDATE,
451 hz_utility_v2pub.last_updated_by
452 );
453
454 ELSIF p_rule_type = G_USER_OVERWRITE_RULE_TYPE THEN
455
456 -- process attribute group
457
458 Process_Group (
459 p_entity_attr_id_tab => p_entity_attr_id_tab,
460 p_attribute_group_name_tab => p_attribute_group_name_tab,
461 p_flag_tab => p_flag_tab,
462 p_os_tab => p_os_tab,
463 x_entity_attr_id_tab => l_entity_attr_id_tab,
464 x_flag_tab => l_flag_tab,
465 x_os_tab => l_os_tab );
466
467 -- create user overwrite rule.
468
469 FORALL i IN 1..l_entity_attr_id_tab.COUNT
470 INSERT INTO hz_user_overwrite_rules (
471 rule_id,
472 entity_attr_id,
473 overwrite_flag,
474 created_by,
475 creation_date,
476 last_update_login,
477 last_update_date,
478 last_updated_by,
479 orig_system
480 ) VALUES (
481 p_rule_id,
482 l_entity_attr_id_tab(i),
483 l_flag_tab(i),
484 hz_utility_v2pub.created_by,
485 SYSDATE,
486 hz_utility_v2pub.last_update_login,
487 SYSDATE,
488 hz_utility_v2pub.last_updated_by,
489 l_os_tab(i)
490 );
491
492 END IF;
493
494 END Create_Rule;
495
496 /**
497 * PROCEDURE Update_Rule
498 *
499 * DESCRIPTION
500 * Update an user creation / overwrite rule.
501 *
502 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
503 *
504 * ARGUMENTS
505 * IN:
506 * p_rule_type Rule type. 'USER_CREATE_RULE' is for user create rule.
507 * 'USER_OVERWRITE_RULE is for user overwrite rule.
508 * p_rule_id Rule id.
509 * p_rule_name Rule name.
510 * p_entity_attr_id_tab Entity / attribute id list.
511 * p_attribute_group_name_tab A list of attribute group name.
512 * p_flag_tab A list of creation / overwrite flags for each
513 * entity / attribute.
514 *
515 * NOTES
516 *
517 * MODIFICATION HISTORY
518 *
519 * 03-01-2002 Jianying Huang o Created.
520 */
521
522 PROCEDURE Update_Rule (
523 p_rule_type IN VARCHAR2,
524 p_rule_id IN NUMBER,
525 p_rule_name IN VARCHAR2,
526 p_entity_attr_id_tab IN IDList,
527 p_attribute_group_name_tab IN VARCHARList,
528 p_flag_tab IN VARCHARList,
529 p_os_tab IN VARCHARList
530 ) IS
531
532 l_entity_attr_id_tab IDList;
533 l_flag_tab VARCHARList;
534 l_os_tab VARCHARList;
535
536 BEGIN
537
538 -- update an existing rule. Only rule name is updateable.
539
540 IF p_rule_name IS NOT NULL THEN
541 HZ_EXT_DATA_RULES_PKG.UPDATE_ROW (
542 p_rule_id, p_rule_name );
543 END IF;
544
545 IF p_rule_type = G_USER_CREATE_RULE_TYPE THEN
546
547 -- update user create rule.
548
549 FORALL i IN 1..p_entity_attr_id_tab.COUNT
550 UPDATE hz_user_create_rules
551 SET creation_flag = p_flag_tab(i),
552 last_update_login = hz_utility_v2pub.last_update_login,
553 last_update_date = SYSDATE,
554 last_updated_by = hz_utility_v2pub.last_updated_by
555 WHERE rule_id = p_rule_id
556 AND creation_flag <> p_flag_tab(i)
557 AND entity_attr_id = p_entity_attr_id_tab(i);
558
559 ELSIF p_rule_type = G_USER_OVERWRITE_RULE_TYPE THEN
560
561 -- process attribute group
562 Process_Group (
563 p_entity_attr_id_tab => p_entity_attr_id_tab,
564 p_attribute_group_name_tab => p_attribute_group_name_tab,
565 p_flag_tab => p_flag_tab,
566 p_os_tab => p_os_tab,
567 x_entity_attr_id_tab => l_entity_attr_id_tab,
568 x_flag_tab => l_flag_tab,
569 x_os_tab => l_os_tab );
570
571 -- update user overwrite rule.
572
573 FOR i IN 1..l_entity_attr_id_tab.COUNT LOOP
574 IF l_flag_tab(i) = 'N' THEN
575 DELETE hz_user_overwrite_rules
576 WHERE entity_attr_id = l_entity_attr_id_tab(i)
577 AND orig_system = l_os_tab(i)
578 AND rule_id = p_rule_id;
579
580 UPDATE hz_user_overwrite_rules
581 SET last_update_date = SYSDATE
582 WHERE entity_attr_id = l_entity_attr_id_tab(i)
583 AND rule_id = p_rule_id
584 AND ROWNUM = 1;
585
586 ELSIF l_flag_tab(i) = 'Y' THEN
587 INSERT INTO hz_user_overwrite_rules (
588 rule_id,
589 entity_attr_id,
590 overwrite_flag,
591 created_by,
592 creation_date,
593 last_update_login,
594 last_update_date,
595 last_updated_by,
596 orig_system
597 ) VALUES (
598 p_rule_id,
599 l_entity_attr_id_tab(i),
600 l_flag_tab(i),
601 hz_utility_v2pub.created_by,
602 SYSDATE,
603 hz_utility_v2pub.last_update_login,
604 SYSDATE,
605 hz_utility_v2pub.last_updated_by,
606 l_os_tab(i)
607 );
608 END IF;
609 END LOOP;
610 /*
611 FORALL i IN 1..l_entity_attr_id_tab.COUNT
612 UPDATE hz_user_overwrite_rules
613 SET overwrite_flag = l_flag_tab(i),
614 last_update_login = hz_utility_v2pub.last_update_login,
615 last_update_date = SYSDATE,
616 last_updated_by = hz_utility_v2pub.last_updated_by
617 WHERE rule_id = p_rule_id
618 AND entity_attr_id = l_entity_attr_id_tab(i);
619 */
620 END IF;
621
622 END Update_Rule;
623
624 /**
625 * PROCEDURE Copy_Rule
626 *
627 * DESCRIPTION
628 * Copy an user creation / overwrite rule.
629 *
630 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
631 *
632 * ARGUMENTS
633 * IN:
634 * p_rule_type Rule type. 'USER_CREATE_RULE' is for user create rule.
635 * 'USER_OVERWRITE_RULE is for user overwrite rule.
636 * p_rule_id Rule id.
637 * p_rule_name Rule name.
638 * OUT:
639 * x_new_rule_id New rule id.
640 *
641 * NOTES
642 *
643 * MODIFICATION HISTORY
644 *
645 * 03-01-2002 Jianying Huang o Created.
646 */
647
648 PROCEDURE Copy_Rule (
649 p_rule_type IN VARCHAR2,
650 p_rule_id IN NUMBER,
651 p_rule_name IN VARCHAR2,
652 x_new_rule_id OUT NOCOPY NUMBER
653 ) IS
654 BEGIN
655
656 -- create a new rule.
657
658 SELECT hz_ext_data_rules_tl_s.NEXTVAL
659 INTO x_new_rule_id
660 FROM DUAL;
661
662 HZ_EXT_DATA_RULES_PKG.INSERT_ROW (
663 x_new_rule_id, p_rule_type, p_rule_name );
664
665 IF p_rule_type = G_USER_CREATE_RULE_TYPE THEN
666
667 -- copy user create rule.
668
669 INSERT INTO hz_user_create_rules (
670 rule_id,
671 entity_attr_id,
672 creation_flag,
673 created_by,
674 creation_date,
675 last_update_login,
676 last_update_date,
677 last_updated_by
678 )
679 SELECT
680 x_new_rule_id,
681 entity_attr_id,
682 creation_flag,
683 hz_utility_v2pub.created_by,
684 SYSDATE,
685 hz_utility_v2pub.last_update_login,
686 SYSDATE,
687 hz_utility_v2pub.last_updated_by
688 FROM hz_user_create_rules
689 WHERE rule_id = p_rule_id;
690
691 INSERT INTO hz_user_overwrite_rules (
692 rule_id,
693 entity_attr_id,
694 overwrite_flag,
695 created_by,
696 creation_date,
697 last_update_login,
698 last_update_date,
699 last_updated_by,
700 orig_system
701 )
702 SELECT
703 x_new_rule_id,
704 entity_attr_id,
705 overwrite_flag,
706 hz_utility_v2pub.created_by,
707 SYSDATE,
708 hz_utility_v2pub.last_update_login,
709 SYSDATE,
710 hz_utility_v2pub.last_updated_by,
711 orig_system
712 FROM hz_user_overwrite_rules
713 WHERE rule_id = p_rule_id;
714
715
716 ELSIF p_rule_type = G_USER_OVERWRITE_RULE_TYPE THEN
717
718 -- copy user overwrite rule.
719
720 INSERT INTO hz_user_overwrite_rules (
721 rule_id,
722 entity_attr_id,
723 overwrite_flag,
724 created_by,
725 creation_date,
726 last_update_login,
727 last_update_date,
728 last_updated_by,
729 orig_system
730 )
731 SELECT
732 x_new_rule_id,
733 entity_attr_id,
734 overwrite_flag,
735 hz_utility_v2pub.created_by,
736 SYSDATE,
737 hz_utility_v2pub.last_update_login,
738 SYSDATE,
739 hz_utility_v2pub.last_updated_by,
740 orig_system
741 FROM hz_user_overwrite_rules
742 WHERE rule_id = p_rule_id;
743
744 END IF;
745
746 END Copy_Rule;
747
748 /**
749 * PROCEDURE Delete_Rule
750 *
751 * DESCRIPTION
752 * Delete an user creation / overwrite rule.
753 *
754 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
755 *
756 * ARGUMENTS
757 * IN:
758 * p_rule_type Rule type. 'USER_CREATE_RULE' is for user create rule.
759 * 'USER_OVERWRITE_RULE is for user overwrite rule.
760 * p_rule_id Rule id.
761 *
762 * NOTES
763 *
764 * MODIFICATION HISTORY
765 *
766 * 03-01-2002 Jianying Huang o Created.
767 */
768
769 PROCEDURE Delete_Rule (
770 p_rule_type IN VARCHAR2,
771 p_rule_id IN NUMBER
772 ) IS
773
774 l_profile_option_name VARCHAR(300);
775
776 BEGIN
777
778 IF p_rule_type = G_USER_CREATE_RULE_TYPE THEN
779
780 DELETE hz_user_create_rules
781 WHERE rule_id = p_rule_id;
782
783 DELETE hz_user_overwrite_rules
784 WHERE rule_id = p_rule_id;
785
786 l_profile_option_name := 'HZ_USER_DATA_CREATION_RULE';
787
788 ELSIF p_rule_type = G_USER_OVERWRITE_RULE_TYPE THEN
789
790 DELETE hz_user_overwrite_rules
791 WHERE rule_id = p_rule_id;
792
793 l_profile_option_name := 'HZ_USER_OVERWRITE_RULE';
794
795 END IF;
796
797 DELETE fnd_profile_option_values
798 WHERE profile_option_id = (
799 SELECT profile_option_id
800 FROM fnd_profile_options
801 WHERE profile_option_name = l_profile_option_name )
802 AND profile_option_value = to_char(p_rule_id);
803
804 -- delete the rule.
805
806 HZ_EXT_DATA_RULES_PKG.DELETE_ROW(p_rule_id);
807
808 END Delete_Rule;
809
810 /**
811 * PROCEDURE Update_ThirdPartyRule
812 *
813 * DESCRIPTION
814 * Update the third party rule.
815 *
816 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
817 *
818 * ARGUMENTS
819 * IN:
820 * p_rule_exists 'Y'/'N' indicator to indicate if the rule
821 * for certain profile exists.
822 * p_entity_attr_id_tab Entity / attribute id list.
823 * p_attribute_group_name_tab A list of attribute group name.
824 * p_flag_tab A list of overwrite flags for each attribute.
825 *
826 * NOTES
827 *
828 * MODIFICATION HISTORY
829 *
830 * 03-01-2002 Jianying Huang o Created.
831 */
832
833 PROCEDURE Update_ThirdPartyRule (
834 p_rule_exists IN VARCHAR2,
835 p_entity_attr_id_tab IN IDList,
836 p_attribute_group_name_tab IN VARCHARList,
837 p_flag_tab IN VARCHARList,
838 p_os_tab IN VARCHARList
839 ) IS
840
841 l_entity_attr_id_tab IDList;
842 l_flag_tab VARCHARList;
843 l_os_tab VARCHARList;
844
845 BEGIN
846
847 -- process attribute group
848
849 Process_Group (
850 p_entity_attr_id_tab => p_entity_attr_id_tab,
851 p_attribute_group_name_tab => p_attribute_group_name_tab,
852 p_flag_tab => p_flag_tab,
853 p_os_tab => p_os_tab,
854 x_entity_attr_id_tab => l_entity_attr_id_tab,
855 x_flag_tab => l_flag_tab,
856 x_os_tab => l_os_tab );
857
858 IF p_rule_exists = 'N' THEN
859
860 -- new rule. Insert.
861 FORALL i IN 1..l_entity_attr_id_tab.COUNT
862 INSERT INTO hz_thirdparty_rule (
863 entity_attr_id,
864 overwrite_flag,
865 created_by,
866 creation_date,
867 last_update_login,
868 last_update_date,
869 last_updated_by,
870 orig_system
871 )
872 VALUES (
873 l_entity_attr_id_tab(i),
874 l_flag_tab(i),
875 hz_utility_v2pub.created_by,
876 SYSDATE,
877 hz_utility_v2pub.last_update_login,
878 SYSDATE,
879 hz_utility_v2pub.last_updated_by,
880 l_os_tab(i)
881 );
882
883 ELSE
884
885 -- old rule. Update.
886 FOR i IN 1..l_entity_attr_id_tab.COUNT LOOP
887 IF l_flag_tab(i) = 'N' THEN
888 DELETE hz_thirdparty_rule
889 WHERE entity_attr_id = l_entity_attr_id_tab(i)
890 AND orig_system = l_os_tab(i);
891 ELSIF l_flag_tab(i) = 'Y' THEN
892 INSERT INTO hz_thirdparty_rule (
893 entity_attr_id,
894 overwrite_flag,
895 created_by,
896 creation_date,
897 last_update_login,
898 last_update_date,
899 last_updated_by,
900 orig_system
901 )
902 VALUES (
903 l_entity_attr_id_tab(i),
904 l_flag_tab(i),
905 hz_utility_v2pub.created_by,
906 SYSDATE,
907 hz_utility_v2pub.last_update_login,
908 SYSDATE,
909 hz_utility_v2pub.last_updated_by,
910 l_os_tab(i)
911 );
912 END IF;
913 END LOOP;
914 /*
915 UPDATE hz_thirdparty_rule
916 SET overwrite_flag = l_flag_tab(i),
917 last_update_login = hz_utility_v2pub.last_update_login,
918 last_update_date = SYSDATE,
919 last_updated_by = hz_utility_v2pub.last_updated_by
920 WHERE entity_attr_id = l_entity_attr_id_tab(i);
921 */
922 END IF;
923
924 END Update_ThirdPartyRule;
925
926 /**
927 * PROCEDURE Set_DataSources
928 *
929 * DESCRIPTION
930 * Set the data sources for a list of attributes.
931 *
932 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
933 *
934 * ARGUMENTS
935 * IN:
936 * p_entity_attr_id_tab Entity / attribute id list.
937 * p_attribute_group_name_tab A list of attribute group name.
938 * p_range_tab The l_index range of each attribute.
939 * p_data_sources_tab The list of data sources for each attributes.
940 * p_ranking_tab The list of data sources ranking for each attributes.
941 *
942 * NOTES
943 *
944 * MODIFICATION HISTORY
945 *
946 * 03-01-2002 Jianying Huang o Created.
947 */
948
949 PROCEDURE Set_DataSources (
950 p_entity_attr_id_tab IN IDList,
951 p_attribute_group_name_tab IN VARCHARList,
952 p_range_tab IN IDList,
953 p_data_sources_tab IN VARCHARList,
954 p_ranking_tab IN IDList
955 ) IS
956
957 l_start NUMBER := 1;
958 l_end NUMBER;
959 l_entity_attr_id_tab IDList;
960
961 BEGIN
962
963 FOR i IN 1..p_entity_attr_id_tab.COUNT LOOP
964
965 -- process attribute group
966
967 Get_NameListInAGroup (
968 p_group_name => p_attribute_group_name_tab(i),
969 x_group_id => l_entity_attr_id_tab,
970 p_entity_attr_id => p_entity_attr_id_tab(i));
971
972 IF l_entity_attr_id_tab.COUNT = 0 THEN
973 l_entity_attr_id_tab(1) := p_entity_attr_id_tab(i);
974 END IF;
975
976 FOR j IN 1..l_entity_attr_id_tab.COUNT LOOP
977 l_end := p_range_tab(i);
978
979 -- set data sources and ranking.
980
981 FOR k IN l_start..l_end LOOP
982 UPDATE hz_select_data_sources
983 SET ranking = p_ranking_tab(k),
984 last_update_login = hz_utility_v2pub.last_update_login,
985 last_update_date = SYSDATE,
986 last_updated_by = hz_utility_v2pub.last_updated_by
987 WHERE entity_attr_id = l_entity_attr_id_tab(j)
988 AND content_source_type = p_data_sources_tab(k);
989 END LOOP;
990
991 -- mark the attribute as been updated.
992
993 UPDATE hz_entity_attributes
994 SET updated_flag = 'Y',
995 last_update_login = hz_utility_v2pub.last_update_login,
996 last_update_date = SYSDATE,
997 last_updated_by = hz_utility_v2pub.last_updated_by
998 WHERE entity_attr_id = l_entity_attr_id_tab(j);
999
1000 END LOOP;
1001
1002 l_start := l_end + 1;
1003 l_entity_attr_id_tab.DELETE;
1004 END LOOP;
1005
1006 END Set_DataSources;
1007
1008 /**
1009 * PROCEDURE Get_DataSourcesForAGroup
1010 *
1011 * DESCRIPTION
1012 * Get the data source setup for a group of attributes.
1013 *
1014 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1015 *
1016 * ARGUMENTS
1017 * IN:
1018 * p_entity_type Entity type.
1019 * p_entity_attr_id_tab Entity / attribute id list.
1020 * x_has_same_setup 'Y'/'N' indicator for if the attributes have
1021 * the same data source setup.
1022 * x_data_sources_tab The list of data sources meaning for all of the attributes.
1023 * x_meaning_tab The list of data sources for all of the attributes.
1024 * x_ranking_tab The list of data sources ranking for all of the attributes.
1025 *
1026 * NOTES
1027 *
1028 * MODIFICATION HISTORY
1029 *
1030 * 03-01-2002 Jianying Huang o Created.
1031 */
1032
1033 PROCEDURE Get_DataSourcesForAGroup (
1034 p_entity_type IN VARCHAR2,
1035 p_entity_attr_id_tab IN IDList,
1036 x_has_same_setup OUT NOCOPY VARCHAR2,
1037 x_data_sources_tab OUT NOCOPY VARCHARList,
1038 x_meaning_tab OUT NOCOPY VARCHARList,
1039 x_ranking_tab OUT NOCOPY IDList
1040 ) IS
1041
1042 CURSOR c_common_data_sources (
1043 p_entity_attr_id NUMBER
1044 ) IS
1045 SELECT s.content_source_type, s.ranking,
1046 o.orig_system_name meaning
1047 FROM hz_select_data_sources s, hz_orig_systems_vl o
1048 WHERE s.entity_attr_id = p_entity_attr_id
1049 and o.orig_system = s.content_source_type
1050 ORDER BY ranking;
1051
1052 j NUMBER;
1053 c NUMBER;
1054 l_count NUMBER;
1055 l_different VARCHAR2(1) := 'N';
1056 str VARCHAR2(255);
1057 l_sql VARCHAR2(1000);
1058 l_data_sources_old VARCHARList;
1059 l_meaning_old VARCHARList;
1060 l_data_sources_tab dbms_sql.Varchar2_Table;
1061 l_meaning_tab dbms_sql.Varchar2_Table;
1062 l_id_tab dbms_sql.Number_Table;
1063
1064 BEGIN
1065
1066 -- find the data sources for all of the attributes in the given group.
1067
1068 j := 0; str := '';
1069 /*
1070 FOR i IN 1..p_entity_attr_id_tab.COUNT LOOP
1071 j := j + 1; str := str||TO_CHAR(p_entity_attr_id_tab(i))||',';
1072 IF j = 15 OR i = p_entity_attr_id_tab.COUNT
1073 THEN
1074 BEGIN
1075 str := SUBSTR(str, 1, LENGTH(str)-1);
1076
1077 l_sql := 'SELECT ''Y'' '||
1078 'FROM ( '||
1079 ' SELECT COUNT(*) total '||
1080 ' FROM hz_select_data_sources '||
1081 ' WHERE entity_attr_id IN ('||str||') '||
1082 ' GROUP BY content_source_type';
1083 --IF p_entity_type = 'profile' THEN
1084 l_sql := l_sql||', ranking';
1085 --END IF;
1086 l_sql := l_sql||
1087 ') '||
1088 'WHERE total <> '||j||' '||
1089 'AND ROWNUM = 1';
1090 EXECUTE IMMEDIATE l_sql INTO l_different;
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 l_different := 'N';
1094 END;
1095
1096 IF l_different = 'Y' THEN
1097 EXIT;
1098 END IF;
1099
1100 j := 1; str := TO_CHAR(p_entity_attr_id_tab(i))||',';
1101 END IF;
1102 END LOOP;
1103
1104 -- if the attributes have different setup, return all of them.
1105
1106 IF l_different = 'Y' THEN
1107 j := 0; str := '';
1108 FOR i IN 1..p_entity_attr_id_tab.COUNT LOOP
1109 j := j + 1; str := str||TO_CHAR(p_entity_attr_id_tab(i))||',';
1110 IF j = 15 OR i = p_entity_attr_id_tab.COUNT
1111 THEN
1112 str := SUBSTR(str, 1, LENGTH(str)-1);
1113
1114 c := dbms_sql.open_cursor;
1115 l_sql := 'SELECT UNIQUE content_source_type, '||
1116 ' hz_utility_v2pub.Get_LookupMeaning( '||
1117 ' ''AR_LOOKUPS'', ''CONTENT_SOURCE_TYPE'', '||
1118 ' content_source_type ) meaning '||
1119 'FROM hz_select_data_sources '||
1120 'WHERE entity_attr_id IN ('||str||') ';
1121 dbms_sql.parse(c, l_sql, dbms_sql.native);
1122 dbms_sql.define_array(c, 1, l_data_sources_tab, 30, 1);
1123 dbms_sql.define_array(c, 2, l_meaning_tab, 30, 1);
1124 l_count := dbms_sql.execute(c);
1125 l_count := dbms_sql.fetch_rows(c);
1126 dbms_sql.column_value(c, 1, l_data_sources_tab);
1127 dbms_sql.column_value(c, 2, l_meaning_tab);
1128 dbms_sql.close_cursor(c);
1129
1130 IF l_data_sources_old.COUNT = 0 THEN
1131 FOR i IN 1..l_data_sources_tab.COUNT LOOP
1132 l_data_sources_old(i) := l_data_sources_tab(i);
1133 l_meaning_old(i) := l_meaning_tab(i);
1134 END LOOP;
1135 ELSE
1136 FOR k IN 1..l_data_sources_tab.COUNT LOOP
1137 IF Get_Index(l_data_sources_old, l_data_sources_tab(k)) = 0 THEN
1138 l_data_sources_old(l_data_sources_old.COUNT+1) := l_data_sources_tab(k);
1139 l_meaning_old(l_data_sources_old.COUNT+1) := l_meaning_tab(k);
1140 END IF;
1141 END LOOP;
1142 END IF;
1143
1144 j := 1; str := TO_CHAR(p_entity_attr_id_tab(i))||',';
1145 END IF;
1146
1147 END LOOP;
1148
1149 x_data_sources_tab := l_data_sources_old;
1150 x_meaning_tab := l_meaning_old;
1151 x_has_same_setup := 'N';
1152
1153 ELSE
1154 */
1155 -- if attributes have the same setup, we can use the setup of one attribute.
1156
1157 OPEN c_common_data_sources(p_entity_attr_id_tab(1));
1158 FETCH c_common_data_sources BULK COLLECT INTO
1159 x_data_sources_tab, x_ranking_tab, x_meaning_tab;
1160 CLOSE c_common_data_sources;
1161 -- END IF;
1162 if p_entity_attr_id_tab.COUNT > 1 then
1163 x_has_same_setup := 'N';
1164 else
1165 x_has_same_setup := 'Y';
1166 end if;
1167
1168 END Get_DataSourcesForAGroup;
1169
1170 /**
1171 * PROCEDURE Set_DataSourcesForAGroup
1172 *
1173 * DESCRIPTION
1174 * Set the data sources for a list of attributes.
1175 *
1176 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1177 *
1178 * ARGUMENTS
1179 * IN:
1180 * p_entity_attr_id_tab Entity / attribute id list.
1181 * p_attribute_group_name_tab A list of attribute group name.
1182 * p_data_sources_tab The list of data sources for each attributes.
1183 * p_ranking_tab The list of data sources ranking for each attributes.
1184 *
1185 * NOTES
1186 *
1187 * MODIFICATION HISTORY
1188 *
1189 * 03-01-2002 Jianying Huang o Created.
1190 */
1191
1192 PROCEDURE Set_DataSourcesForAGroup (
1193 p_entity_attr_id_tab IN IDList,
1194 p_attribute_group_name_tab IN VARCHARList,
1195 p_data_sources_tab IN VARCHARList,
1196 p_ranking_tab IN IDList
1197 ) IS
1198
1199 l_entity_attr_id_tab IDList;
1200
1201 BEGIN
1202
1203 -- process attribute group
1204
1205 Get_Group (
1206 p_entity_attr_id_tab => p_entity_attr_id_tab,
1207 p_attribute_group_name_tab => p_attribute_group_name_tab,
1208 x_entity_attr_id_tab => l_entity_attr_id_tab );
1209
1210 FOR i IN 1..l_entity_attr_id_tab.COUNT LOOP
1211
1212 -- set the data source for all of the attributes in the given group.
1213
1214 FORALL j IN 1..p_data_sources_tab.COUNT
1215 UPDATE hz_select_data_sources
1216 SET ranking = p_ranking_tab(j),
1217 last_update_login = hz_utility_v2pub.last_update_login,
1218 last_update_date = SYSDATE,
1219 last_updated_by = hz_utility_v2pub.last_updated_by
1220 WHERE entity_attr_id = l_entity_attr_id_tab(i)
1221 AND content_source_type = p_data_sources_tab(j);
1222
1223 -- mark the attribute as been updated.
1224
1225 UPDATE hz_entity_attributes
1226 SET updated_flag = 'Y',
1227 last_update_login = hz_utility_v2pub.last_update_login,
1228 last_update_date = SYSDATE,
1229 last_updated_by = hz_utility_v2pub.last_updated_by
1230 WHERE entity_attr_id = l_entity_attr_id_tab(i);
1231
1232 END LOOP;
1233
1234 END Set_DataSourcesForAGroup;
1235
1236 END HZ_MIXNM_WEBUI_UTILITY;