[Home] [Help]
PACKAGE BODY: APPS.FND_PROFILE_HIERARCHY_PKG
Source
1 package body FND_PROFILE_HIERARCHY_PKG as
2 /* $Header: AFPOMPHB.pls 120.1 2005/07/02 04:13:07 appldev noship $ */
3
4 /*
5 ** HIERARCHY_SWITCH_TYPE constants
6 */
7
8 TYPE_SECURITY_2_SERVRESP CONSTANT INTEGER := 1;
9 TYPE_SERVRESP_2_SECURITY CONSTANT INTEGER := 2;
10 TYPE_SERVER_2_SERVRESP CONSTANT INTEGER := 3;
11 TYPE_SERVRESP_2_SERVER CONSTANT INTEGER := 4;
12 TYPE_IGNORE CONSTANT INTEGER := -1;
13
14
15 /*
16 ** ROW TYPE
17 ROW_INSERTABLE CONSTANT INTEGER := 1;
18 ROW_UPDATABLE CONSTANT INTEGER := 2;
19 ROW_IGNORABLE CONSTANT INTEGER := 3;
20
21 */
22
23 /*
24 * Global types to hold the fetched values.
25 */
26
27 TYPE profile_value is TABLE OF FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE
28 INDEX BY BINARY_INTEGER;
29 TYPE profile_level is TABLE OF NUMBER
30 INDEX BY BINARY_INTEGER;
31
32 /*
33 ** Although similar definitions for CURSOR, One is used for present
34 ** hierarchy type and the other is used for target hierarchy.
35 ** Using the same cursor raises in a nested for loop, cursor_already_open.
36 */
37 CURSOR pov(appl_id number, prof_id number, lev_id number) IS
38 SELECT profile_option_value, level_value, level_value_application_id, level_value2
39 FROM fnd_profile_option_values
40 WHERE application_id = appl_id
41 AND profile_option_id = prof_id
42 AND level_id = lev_id;
43
44
45 /*
46 ** Collection to hold profile values at target hierarchy level
47 */
48 g_prof_val_4_update profile_value;
49
50 /*
51 ** Collection to hold profile values at source hierarchy level
52 */
53 g_prof_val_4_insert profile_value;
54
55 /*
56 ** Collection to hold profile level values at target hierarchy level
57 */
58 g_lev_val_4_update profile_level;
59
60 /*
61 ** Collection to hold profile level values at source hierarchy level
62 */
63 g_lev_val_4_insert profile_level;
64
65 /*
66 ** Collection to hold profile level application id values at target hierarchy level
67 */
68 g_lev_val_appl_4_update profile_level;
69
70 /*
71 ** Collection to hold profile level application id values at source hierarchy level
72 */
73 g_lev_val_appl_4_insert profile_level;
74
75 /*
76 ** define variable to hold the HIERARCHY_SWITCH_TYPE
77 */
78 g_type integer;
79
80 /*
81 ** 10003, if g_type = TYPE_SECURITY_2_SERVRESP
82 ** 10007, if g_type = TYPE_SERVRESP_2_SECURITY
83 ** 10005, if g_type = TYPE_SERVER_2_SERVRESP
84 ** 10007, if g_type = TYPE_SERVRESP_2_SERVER
85 **
86 */
87 g_from_lev_id number;
88
89 /*
90 ** 10007, if g_type = TYPE_SECURITY_2_SERVRESP
91 ** 10003, if g_type = TYPE_SERVRESP_2_SECURITY
92 ** 10007, if g_type = TYPE_SERVER_2_SERVRESP
93 ** 10005, if g_type = TYPE_SERVRESP_2_SERVER
94 **
95 */
96 g_to_lev_id number;
97
98
99 /*
100 * RESET - AOL Internal only.
101 * resets the global internal package variables.
102 */
103 procedure reset
104 is
105 begin
106 g_lev_val_4_update.delete;
107 g_lev_val_4_insert.delete;
108 g_lev_val_appl_4_update.delete;
109 g_lev_val_appl_4_insert.delete;
110 g_prof_val_4_update.delete;
111 g_prof_val_4_insert.delete;
112
113 g_type := null;
114 g_from_lev_id := null;
115 g_to_lev_id :=null;
116
117 end reset;
118
119
120 /*
121 ** SET_TYPE - AOL Internal only
122 **
123 ** Sets g_type, g_from_lev_id and g_to_lev_id values.
124 ** If the switch is ignorable, type is set to TYPE_IGNORABLE.
125 ** A hierarchy switch is ignorable if either source or destination
126 ** hierarchy types are not in (SERVRESP, SERVER, SECURITY) and one of
127 ** source or target hierarchy levels is not SERVRESP.
128 */
129 procedure set_type( x_profile_option_name varchar2,
130 x_hierarchy_type varchar2
131 )
132 is
133 l_db_hierarchy_type varchar2(8);
134 non_existant_profile exception;
135 begin
136 select hierarchy_type into l_db_hierarchy_type
137 from fnd_profile_options
138 where profile_option_name = x_profile_option_name;
139
140 if( upper(x_hierarchy_type) = 'SERVRESP') then
141 if ( upper(l_db_hierarchy_type) = 'SECURITY') then
142 g_type := TYPE_SECURITY_2_SERVRESP;
143 g_from_lev_id := 10003;
144 g_to_lev_id := 10007;
145 return;
146 end if;
147
148 if ( upper(l_db_hierarchy_type) = 'SERVER') then
149 g_type := TYPE_SERVER_2_SERVRESP;
150 g_from_lev_id := 10005;
151 g_to_lev_id := 10007;
152 return;
153 end if;
154 end if;
155
156 if( upper(l_db_hierarchy_type) = 'SERVRESP') then
157 if ( upper(x_hierarchy_type) = 'SECURITY') then
158 g_type := TYPE_SERVRESP_2_SECURITY;
159 g_from_lev_id := 10007;
160 g_to_lev_id := 10003;
161 return;
162 end if;
163 if ( upper(x_hierarchy_type) = 'SERVER') then
164 g_type := TYPE_SERVRESP_2_SERVER;
165 g_from_lev_id := 10007;
166 g_to_lev_id := 10005;
167 return;
168 end if;
169 end if;
170
171 g_type := TYPE_IGNORE;
172
173 exception
174 when no_data_found then
175 -- g_type := TYPE_IGNORE;
176 raise non_existant_profile;
177 end set_type;
178
179
180 /*
181 ** IS_FROM_ROW_VALID - AOL Internal only.
182 **
183 ** returns true if the row at source hierarchy level
184 ** has valid values at LEVEL_VALUE, LEVEL_VALUE_APPLICATION_ID and
185 ** LEVEL_VALUE2.
186 **/
187 function is_from_row_valid( x_lev_val number,
188 x_lev_val_appl number,
189 x_lev_val2 number
190 )
191 return boolean
192 is
193 begin
194 if( g_type = TYPE_SERVRESP_2_SECURITY) then
195 if(x_lev_val2 = -1)
196 then
197 return TRUE;
198 end if;
199 end if;
200
201 if( g_type = TYPE_SECURITY_2_SERVRESP) then
202 if(x_lev_val2 is null)
203 then
204 return TRUE;
205 end if;
206 end if;
207
208 if( g_type = TYPE_SERVRESP_2_SERVER) then
209 if( x_lev_val = -1) and ( x_lev_val_appl = -1)
210 then
211 return TRUE;
212 end if;
213 end if;
214 if( g_type = TYPE_SERVER_2_SERVRESP) then
215 if( x_lev_val2 is null ) and ( x_lev_val_appl is null )
216 then
217 return TRUE;
218 end if;
219 end if;
220
221 return FALSE;
222
223 end is_from_row_valid;
224
225 /*
226 ** IS_TO_ROW_VALID - AOL Internal only.
227 **
228 ** returns true if the row at target hierarchy level
229 ** has valid values at LEVEL_VALUE, LEVEL_VALUE_APPLICATION_ID and
230 ** LEVEL_VALUE2.
231 **/
232 function is_to_row_valid( x_lev_val number,
233 x_lev_val_appl number,
234 x_lev_val2 number
235 )
236 return boolean
237 is
238 begin
239
240 if( g_type = TYPE_SERVRESP_2_SECURITY) then
241 if (x_lev_val2 is null)
242 then
243 return TRUE;
244 end if;
245 end if;
246
247 if( g_type = TYPE_SECURITY_2_SERVRESP) then
248 if (x_lev_val2 = -1 )
249 then
250 return TRUE;
251 end if;
252 end if;
253
254 if( g_type = TYPE_SERVRESP_2_SERVER) then
255 if( x_lev_val2 is null and x_lev_val_appl is null)
256 then
257 return TRUE;
258 end if;
259 end if;
260 if( g_type = TYPE_SERVER_2_SERVRESP) then
261 if( x_lev_val = -1 and x_lev_val_appl = -1 )
262 then
263 return TRUE;
264 end if;
265 end if;
266
267 return FALSE;
268 end is_to_row_valid;
269
270 /*
271 ** IS_ROW_UPDATABLE - AOL Internal only.
272 **
273 ** returns true if the profile option value
274 ** at the target hierarchy level can be considered
275 ** updatable.
276 **/
277 function is_row_updatable( x_from_lev_val number,
278 x_to_lev_val number,
279 x_from_lev_val_appl number,
280 x_to_lev_val_appl number,
281 x_from_lev_val2 number,
282 x_to_lev_val2 number
283 )
284 return boolean
285 is
286 begin
287 if( is_to_row_valid ( x_to_lev_val,x_to_lev_val_appl, x_to_lev_val2) )then
288 if(g_type = TYPE_SERVRESP_2_SECURITY or g_type = TYPE_SECURITY_2_SERVRESP) then
289 if (x_from_lev_val = x_to_lev_val and x_from_lev_val_appl = x_to_lev_val_appl)
290 then
291 return TRUE;
292 end if;
293 end if;
294
295 if(g_type = TYPE_SERVRESP_2_SERVER ) then
296 if (x_from_lev_val2 = x_to_lev_val) then
297 return TRUE;
298 end if;
299 end if;
300
301 if(g_type = TYPE_SERVER_2_SERVRESP ) then
302 if (x_from_lev_val = x_to_lev_val2) then
303 return TRUE;
304 end if;
305 end if;
306 end if;
307
308 return FALSE;
309
310 end is_row_updatable;
311
312
313 /*
314 ** ADD_ROWS - AOL INTERNAL ONLY
315 ** The procedure separates insertable and updatable rows and
316 ** collects them into global collections.
317 */
318 procedure add_rows(x_profile_value_c in out nocopy profile_value ,
319 x_level_value_c in out nocopy profile_level,
320 x_level_val_appl_id_c in out nocopy profile_level,
321 x_profile_value varchar2,
322 x_level_value number,
323 x_level_value_appl_id number,
324 x_level_value2 number,
325 x_prof_ind number,
326 x_mode number)
327 is
331 if( g_type = TYPE_SERVRESP_2_SECURITY or g_type = TYPE_SECURITY_2_SERVRESP) then
328 begin
329 x_profile_value_c(x_prof_ind) := x_profile_value;
330
332 x_level_value_c(x_prof_ind) := x_level_value;
333 x_level_val_appl_id_c(x_prof_ind):= x_level_value_appl_id;
334 end if;
335
336 if ( g_type = TYPE_SERVRESP_2_SERVER ) then
337 if( x_mode = INSERT_ONLY) then
338 x_level_value_c(x_prof_ind) := x_level_value2;
339 x_level_val_appl_id_c(x_prof_ind):= null;
340 end if;
341
342 if( x_mode = UPDATE_ONLY) then
343 x_level_value_c(x_prof_ind) := x_level_value;
344 x_level_val_appl_id_c(x_prof_ind):= null;
345 end if;
346 end if;
347
348 if ( g_type = TYPE_SERVER_2_SERVRESP ) then
349 if( x_mode = INSERT_ONLY) then
350 x_level_value_c(x_prof_ind) := x_level_value;
351 x_level_val_appl_id_c(x_prof_ind):= -1;
352 end if;
353
354 if( x_mode = UPDATE_ONLY) then
355 x_level_value_c(x_prof_ind) := x_level_value2;
356 x_level_val_appl_id_c(x_prof_ind):= -1;
357 end if;
358 end if;
359 end add_rows;
360
361 /*
362 ** COLLECT_INSERTABLE_ROWS - AOL INTERNAL ONLY
363 ** The procedure collects insertable rows.
364 */
365 procedure collect_insertable_rows(x_appl_id number,
366 x_prof_id number
367 )
368 is
369 cursor pov_to(appl_id number, prof_id number, lev_id number) is
370 select profile_option_value, level_value, level_value_application_id, level_value2
371 from fnd_profile_option_values
372 where application_id = appl_id
373 and profile_option_id = prof_id
374 and level_id = lev_id;
375
376 l_row_type number;
377 l_prof_ind number:=0;
378 l_is_insertable boolean;
379 begin
380 FOR from_rec IN pov(x_appl_id, x_prof_id, g_from_lev_id) LOOP
381 if ( is_from_row_valid(
382 from_rec.level_value,
383 from_rec.level_value_application_id,
384 from_rec.level_value2
385 )
386 ) then
387 l_is_insertable := TRUE;
388 l_prof_ind := l_prof_ind +1;
389
390 FOR to_rec IN pov_to(x_appl_id, x_prof_id, g_to_lev_id) LOOP
391 if ( is_row_updatable (
392 from_rec.level_value,
393 to_rec.level_value,
394 from_rec.level_value_application_id,
395 to_rec.level_value_application_id,
396 from_rec.level_value2,
397 to_rec.level_value2
398 )
399 )
400 then
401 l_is_insertable := FALSE;
402 exit;
403 end if;
404
405 END LOOP;
406
407 if (l_is_insertable) then
408 add_rows(g_prof_val_4_insert,
409 g_lev_val_4_insert,
410 g_lev_val_appl_4_insert,
411 from_rec.profile_option_value,
412 from_rec.level_value,
413 from_rec.level_value_application_id,
414 from_rec.level_value2,
415 l_prof_ind,
416 INSERT_ONLY);
417 end if;
418 end if;
419 END LOOP;
420 end collect_insertable_rows;
421
422 /*
423 ** COLLECT_ALL_ROWS - AOL INTERNAL ONLY
424 ** The procedure collects insertable and updatable rows.
425 */
426 procedure collect_all_rows(x_appl_id number,
427 x_prof_id number
428 )
429 is
430 cursor pov_4_update(appl_id number, prof_id number, lev_id number) is
431 select profile_option_value, level_value, level_value_application_id, level_value2
432 from fnd_profile_option_values
433 where application_id = appl_id
434 and profile_option_id = prof_id
435 and level_id = lev_id
436 for update;
437
438 l_row_type number;
439 l_prof_ind number:=0;
440 l_is_insertable boolean;
441 begin
442 FOR from_rec IN pov(x_appl_id, x_prof_id, g_from_lev_id) LOOP
443
444 if ( is_from_row_valid(
445 from_rec.level_value,
446 from_rec.level_value_application_id,
447 from_rec.level_value2
448 )
449 )
450 then
451 l_is_insertable := TRUE;
452 l_prof_ind := l_prof_ind +1;
453 FOR to_rec IN pov_4_update(x_appl_id, x_prof_id, g_to_lev_id) LOOP
454
455 if ( is_row_updatable (
456 from_rec.level_value,
457 to_rec.level_value,
458 from_rec.level_value_application_id,
459 to_rec.level_value_application_id,
460 from_rec.level_value2,
461 to_rec.level_value2
462 )
463 ) then
464 add_rows(g_prof_val_4_update,
465 g_lev_val_4_update,
466 g_lev_val_appl_4_update,
467 from_rec.profile_option_value,
468 to_rec.level_value,
469 to_rec.level_value_application_id,
470 to_rec.level_value2,
471 l_prof_ind,
472 UPDATE_ONLY
473 );
474
475 l_is_insertable := FALSE;
476 exit;
477 end if;
478 END LOOP;
479
480 if (l_is_insertable) then
481 add_rows(g_prof_val_4_insert,
482 g_lev_val_4_insert,
483 g_lev_val_appl_4_insert,
484 from_rec.profile_option_value,
485 from_rec.level_value,
486 from_rec.level_value_application_id,
487 from_rec.level_value2,
488 l_prof_ind,
489 INSERT_ONLY);
490 end if;
491
492 end if;
493 END LOOP;
494 end collect_all_rows;
495
496 /*
497 ** The procedure carries a profile value and other who attributes when
498 ** its hierarchy type is changed. The source and target hierarchy
499 ** types should be from the set (SECURITY, SERVER, SERVRESP).
500 ** Any other hierarchy switch is ignored. The following hierarchy
501 ** switches are possible:
502 **
503 ** 1. SECURITY TO SERVRESP
504 ** In this switch all the profile values at level 10003 are considered
505 ** for carring forward to level 10007.
506 ** 2. SERVER TO SERVRESP
507 ** In this switch all the profile values at level 10005 are considered
508 ** for carring forward to level 10007.
509 ** 3. SERVRESP TO SECURITY
510 ** In this switch all the profile values at level 10007 are considered
511 ** for carring forward to level 10003.
512 ** 4. SERVRESP TO SERVER
513 ** In this switch all the profile values at level 10007 are considered
514 ** for carring forward to level 10005.
515 **
516 ** what profile values are carried is controlled by the parameter X_MODE.
517 ** profile option value rows can be either updatable rows or insertable rows.
518 **
519 ** when a profile has rows existing at the target hierarchy level, they are called
520 ** updatable rows. For example, when a profile hierarchy switch is from
521 ** SECURITY to SERVRESP, all rows in FND_PROFILE_OPTION_VALUES for this profile
522 ** are considered updatable if there exist a valid LEVEL_VALUE2 value at level 10007.
523 **
524 ** Insertable rows are all rows at source hierarchy level minus rows considered as
525 ** updatable.
526 **
527 ** 1. UPDATE_ONLY
528 ** In this mode profile option value and who columns of updatable rows are updated
529 ** from the similar rows at the source hierarchy level.
530 ** 2. INSERT_ONLY
531 ** In this mode profile option value and who columns of insertable rows are inserted
532 ** at the target hierarchy level. Updatable rows are untouched.
533 ** 3. INSERT_UPDATE
534 ** This mode is combination of both (1) and (2).
535 */
536 procedure carry_profile_values(
537 X_PROFILE_OPTION_NAME in VARCHAR2,
538 X_APPLICATION_ID in NUMBER,
539 X_PROFILE_OPTION_ID in NUMBER,
540 X_TO_HIERARCHY_TYPE in VARCHAR2,
541 X_LAST_UPDATE_DATE in DATE,
542 X_LAST_UPDATED_BY in NUMBER,
543 X_CREATION_DATE in DATE,
544 X_CREATED_BY in NUMBER,
545 X_LAST_UPDATE_LOGIN in NUMBER,
549 begin
546 X_MODE in NUMBER default INSERT_UPDATE
547 )
548 is
550 reset;
551 set_type(X_PROFILE_OPTION_NAME,X_TO_HIERARCHY_TYPE);
552
553 if (g_type = TYPE_IGNORE) then
554 return;
555 end if;
556
557 if(X_MODE = INSERT_UPDATE or X_MODE = UPDATE_ONLY) then
558 collect_all_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
559 end if;
560
561 if(X_MODE = INSERT_ONLY) then
562 collect_insertable_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
563 end if;
564
565 if(
566 (X_MODE = INSERT_UPDATE or X_MODE= UPDATE_ONLY)
567 and
568 (g_prof_val_4_update.first is not null)
569 ) then
570
571 FORALL rec in g_prof_val_4_update.first .. g_prof_val_4_update.last
572 update fnd_profile_option_values
573 set profile_option_value = g_prof_val_4_update(rec),
574 last_update_date = x_last_update_date,
575 last_update_login = x_last_update_login,
576 last_updated_by = x_last_updated_by
577 where level_id = g_to_lev_id
578 and application_id = x_application_id
579 and profile_option_id = x_profile_option_id
580 and level_value =
581 decode( g_type, TYPE_SERVER_2_SERVRESP, -1,g_lev_val_4_update(rec))
582 and nvl(level_value_application_id,-11111) =
583 nvl(decode(g_type, TYPE_SERVER_2_SERVRESP,-1,
584 TYPE_SERVRESP_2_SERVER, null,
585 g_lev_val_appl_4_update(rec)
586 ), -11111
587 )
588 and nvl(level_value2, -11111) =
589 nvl(decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_update(rec),
590 TYPE_SECURITY_2_SERVRESP, -1,
591 null
592 ), -11111
593 );
594
595 end if;
596
597 if(
598 (X_MODE = INSERT_UPDATE or X_MODE= INSERT_ONLY)
599 and
600 (g_prof_val_4_insert.first is not null)
601 )
602 then
603 FORALL rec in g_prof_val_4_insert.first .. g_prof_val_4_insert.last
604 insert into fnd_profile_option_values (
605 APPLICATION_ID,
606 PROFILE_OPTION_ID,
607 LEVEL_ID,
608 LEVEL_VALUE,
609 LAST_UPDATE_DATE,
610 LAST_UPDATED_BY,
611 CREATION_DATE,
612 CREATED_BY,
613 LAST_UPDATE_LOGIN,
614 PROFILE_OPTION_VALUE,
615 LEVEL_VALUE_APPLICATION_ID,
616 LEVEL_VALUE2
617 ) values (
618 X_APPLICATION_ID,
619 X_PROFILE_OPTION_ID,
620 g_to_lev_id,
621 decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
622 g_lev_val_4_insert(rec)
623 ),
624 X_LAST_UPDATE_DATE,
625 X_LAST_UPDATED_BY,
626 X_CREATION_DATE,
627 X_CREATED_BY,
628 X_LAST_UPDATE_LOGIN,
629 g_prof_val_4_insert(rec),
630 decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
631 TYPE_SERVRESP_2_SERVER,null,
632 g_lev_val_appl_4_insert(rec)
633 ),
634 decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_insert(rec),
635 TYPE_SECURITY_2_SERVRESP, -1,
636 null
637 )
638 );
639 end if;
640 end carry_profile_values;
641
642
643 /* ------------------ FOR DEBUGGING --------------------- */
644
645 procedure display ( value in out nocopy profile_value, name varchar2)
646 is
647 l_ind binary_integer;
648 begin
649 l_ind := value.first;
650 while (l_ind is not null)
651 loop
652 dbms_output.put_line(name||'['||l_ind||']:'|| value(l_ind));
653 l_ind:= value.next(l_ind);
654 end loop;
655
656 end;
657 procedure display ( value in out nocopy profile_level, name varchar2)
658 is
659 l_ind binary_integer;
660 begin
661 l_ind := value.first;
662 while (l_ind is not null)
663 loop
664 dbms_output.put_line(name||'['||l_ind||']:'|| value(l_ind));
665 l_ind:= value.next(l_ind);
666 end loop;
667
668 end;
669
670 end FND_PROFILE_HIERARCHY_PKG;