1 package body FND_PROFILE_HIERARCHY_PKG as
2 /* $Header: AFPOMPHB.pls 120.1.12020000.2 2012/11/27 14:36:31 srinnakk ship $ */
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
328 begin
329 x_profile_value_c(x_prof_ind) := x_profile_value;
330
331 if( g_type = TYPE_SERVRESP_2_SECURITY or g_type = TYPE_SECURITY_2_SERVRESP) then
332 x_level_value_c(x_prof_ind) := x_level_value;
336 if ( g_type = TYPE_SERVRESP_2_SERVER ) then
333 x_level_val_appl_id_c(x_prof_ind):= x_level_value_appl_id;
334 end if;
335
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 /* below two variables have been created to hold the index information
440 * for insert and update collections seperately, if the same index is used there is
441 * high possbility for the collection to have null elements in between */
442 l_prof_ind_upd number:=0; --- added for bug 15919372
443 l_prof_ind_ins number:=0; --- added for bug 15919372
444 l_is_insertable boolean;
445 begin
446 FOR from_rec IN pov(x_appl_id, x_prof_id, g_from_lev_id) LOOP
447
448 if ( is_from_row_valid(
449 from_rec.level_value,
450 from_rec.level_value_application_id,
451 from_rec.level_value2
452 )
453 )
454 then
455 l_is_insertable := TRUE;
456 /* l_prof_ind := l_prof_ind +1; commented for bug 15919372 */
457 FOR to_rec IN pov_4_update(x_appl_id, x_prof_id, g_to_lev_id) LOOP
458
462 from_rec.level_value_application_id,
459 if ( is_row_updatable (
460 from_rec.level_value,
461 to_rec.level_value,
463 to_rec.level_value_application_id,
464 from_rec.level_value2,
465 to_rec.level_value2
466 )
467 ) then
468 --- below line has been added for bug 15919372
469 l_prof_ind_upd := l_prof_ind_upd +1;
470 add_rows(g_prof_val_4_update,
471 g_lev_val_4_update,
472 g_lev_val_appl_4_update,
473 from_rec.profile_option_value,
474 to_rec.level_value,
475 to_rec.level_value_application_id,
476 to_rec.level_value2,
477 l_prof_ind_upd,
478 UPDATE_ONLY
479 );
480
481 l_is_insertable := FALSE;
482 exit;
483 end if;
484 END LOOP;
485
486 if (l_is_insertable) then
487 ---- below line has been added for bug 15919372
488 l_prof_ind_ins := l_prof_ind_ins +1;
489 add_rows(g_prof_val_4_insert,
490 g_lev_val_4_insert,
491 g_lev_val_appl_4_insert,
492 from_rec.profile_option_value,
493 from_rec.level_value,
494 from_rec.level_value_application_id,
495 from_rec.level_value2,
496 l_prof_ind_ins,
497 INSERT_ONLY);
498 end if;
499
500 end if;
501 END LOOP;
502 end collect_all_rows;
503
504 /*
505 ** The procedure carries a profile value and other who attributes when
506 ** its hierarchy type is changed. The source and target hierarchy
507 ** types should be from the set (SECURITY, SERVER, SERVRESP).
508 ** Any other hierarchy switch is ignored. The following hierarchy
509 ** switches are possible:
510 **
511 ** 1. SECURITY TO SERVRESP
512 ** In this switch all the profile values at level 10003 are considered
513 ** for carring forward to level 10007.
514 ** 2. SERVER TO SERVRESP
515 ** In this switch all the profile values at level 10005 are considered
516 ** for carring forward to level 10007.
517 ** 3. SERVRESP TO SECURITY
518 ** In this switch all the profile values at level 10007 are considered
519 ** for carring forward to level 10003.
520 ** 4. SERVRESP TO SERVER
521 ** In this switch all the profile values at level 10007 are considered
522 ** for carring forward to level 10005.
523 **
524 ** what profile values are carried is controlled by the parameter X_MODE.
525 ** profile option value rows can be either updatable rows or insertable rows.
526 **
527 ** when a profile has rows existing at the target hierarchy level, they are called
528 ** updatable rows. For example, when a profile hierarchy switch is from
529 ** SECURITY to SERVRESP, all rows in FND_PROFILE_OPTION_VALUES for this profile
530 ** are considered updatable if there exist a valid LEVEL_VALUE2 value at level 10007.
531 **
532 ** Insertable rows are all rows at source hierarchy level minus rows considered as
533 ** updatable.
534 **
535 ** 1. UPDATE_ONLY
536 ** In this mode profile option value and who columns of updatable rows are updated
537 ** from the similar rows at the source hierarchy level.
538 ** 2. INSERT_ONLY
539 ** In this mode profile option value and who columns of insertable rows are inserted
540 ** at the target hierarchy level. Updatable rows are untouched.
541 ** 3. INSERT_UPDATE
542 ** This mode is combination of both (1) and (2).
543 */
544 procedure carry_profile_values(
545 X_PROFILE_OPTION_NAME in VARCHAR2,
546 X_APPLICATION_ID in NUMBER,
547 X_PROFILE_OPTION_ID in NUMBER,
548 X_TO_HIERARCHY_TYPE in VARCHAR2,
549 X_LAST_UPDATE_DATE in DATE,
550 X_LAST_UPDATED_BY in NUMBER,
551 X_CREATION_DATE in DATE,
552 X_CREATED_BY in NUMBER,
553 X_LAST_UPDATE_LOGIN in NUMBER,
554 X_MODE in NUMBER default INSERT_UPDATE
555 )
556 is
557 begin
558 reset;
559 set_type(X_PROFILE_OPTION_NAME,X_TO_HIERARCHY_TYPE);
560
561 if (g_type = TYPE_IGNORE) then
562 return;
563 end if;
564
565 if(X_MODE = INSERT_UPDATE or X_MODE = UPDATE_ONLY) then
566 collect_all_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
567 end if;
568
569 if(X_MODE = INSERT_ONLY) then
570 collect_insertable_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
571 end if;
572
573 if(
574 (X_MODE = INSERT_UPDATE or X_MODE= UPDATE_ONLY)
575 and
576 (g_prof_val_4_update.first is not null)
577 ) then
578
579 FORALL rec in g_prof_val_4_update.first .. g_prof_val_4_update.last
580 update fnd_profile_option_values
581 set profile_option_value = g_prof_val_4_update(rec),
582 last_update_date = x_last_update_date,
583 last_update_login = x_last_update_login,
584 last_updated_by = x_last_updated_by
585 where level_id = g_to_lev_id
586 and application_id = x_application_id
587 and profile_option_id = x_profile_option_id
588 and level_value =
589 decode( g_type, TYPE_SERVER_2_SERVRESP, -1,g_lev_val_4_update(rec))
590 and nvl(level_value_application_id,-11111) =
591 nvl(decode(g_type, TYPE_SERVER_2_SERVRESP,-1,
592 TYPE_SERVRESP_2_SERVER, null,
593 g_lev_val_appl_4_update(rec)
594 ), -11111
595 )
596 and nvl(level_value2, -11111) =
597 nvl(decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_update(rec),
598 TYPE_SECURITY_2_SERVRESP, -1,
599 null
600 ), -11111
601 );
602
603 end if;
604
605 if(
606 (X_MODE = INSERT_UPDATE or X_MODE= INSERT_ONLY)
607 and
608 (g_prof_val_4_insert.first is not null)
609 )
610 then
611 FORALL rec in g_prof_val_4_insert.first .. g_prof_val_4_insert.last
612 insert into fnd_profile_option_values (
613 APPLICATION_ID,
614 PROFILE_OPTION_ID,
615 LEVEL_ID,
616 LEVEL_VALUE,
617 LAST_UPDATE_DATE,
618 LAST_UPDATED_BY,
619 CREATION_DATE,
620 CREATED_BY,
621 LAST_UPDATE_LOGIN,
622 PROFILE_OPTION_VALUE,
623 LEVEL_VALUE_APPLICATION_ID,
624 LEVEL_VALUE2
625 ) values (
626 X_APPLICATION_ID,
627 X_PROFILE_OPTION_ID,
628 g_to_lev_id,
629 decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
630 g_lev_val_4_insert(rec)
631 ),
632 X_LAST_UPDATE_DATE,
633 X_LAST_UPDATED_BY,
634 X_CREATION_DATE,
635 X_CREATED_BY,
636 X_LAST_UPDATE_LOGIN,
637 g_prof_val_4_insert(rec),
638 decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
639 TYPE_SERVRESP_2_SERVER,null,
640 g_lev_val_appl_4_insert(rec)
641 ),
642 decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_insert(rec),
643 TYPE_SECURITY_2_SERVRESP, -1,
644 null
645 )
646 );
647 end if;
648 end carry_profile_values;
649
650
651 /* ------------------ FOR DEBUGGING --------------------- */
652
653 procedure display ( value in out nocopy profile_value, name varchar2)
654 is
655 l_ind binary_integer;
656 begin
657 l_ind := value.first;
658 while (l_ind is not null)
659 loop
660 dbms_output.put_line(name||'['||l_ind||']:'|| value(l_ind));
661 l_ind:= value.next(l_ind);
662 end loop;
663
664 end;
665 procedure display ( value in out nocopy profile_level, name varchar2)
666 is
667 l_ind binary_integer;
668 begin
669 l_ind := value.first;
670 while (l_ind is not null)
671 loop
672 dbms_output.put_line(name||'['||l_ind||']:'|| value(l_ind));
673 l_ind:= value.next(l_ind);
674 end loop;
675
676 end;
677
678 end FND_PROFILE_HIERARCHY_PKG;
[Home] [Help]