[Home] [Help]
PACKAGE BODY: APPS.OKS_PROFILES_VISIBLE
Source
1 PACKAGE BODY OKS_PROFILES_VISIBLE AS
2 /* $Header: OKSSETPB.pls 115.4 2002/05/24 10:36:20 pkm ship $ */
3
4 FUNCTION profile_visible_value( p_application_id number,
5 p_profile_option_id number,
6 p_level_id number,
7 p_level_value number,
8 p_lvl_val_appl_id number default NULL) return varchar2 is
9
10 CURSOR get_sql IS
11 select replace(substr(upper(fpovl.sql_validation),instr(upper(fpovl.sql_validation),'SELECT',1,1)-1,
12 instr(upper(fpovl.sql_validation),'COLUMN',1,1) -
13 instr(upper(fpovl.sql_validation),'SELECT',1,1)),'\','') SQL_VALID,
14 fpov.profile_option_value PROF_VAL
15 from fnd_profile_options_vl fpovl,
16 fnd_profile_option_values fpov
17 where fpovl.profile_option_id = p_profile_option_id
18 and fpov.profile_option_id = fpovl.profile_option_id
19 and fpov.level_id = p_level_id
20 and fpov.application_id = p_application_id
21 and fpov.level_value = p_level_value
22 and nvl(fpov.level_value_application_id,-99) = nvl(nvl(p_lvl_val_appl_id,fpov.level_value_application_id),-99)
23 --and fpovl.application_id = 515
24 order by fpovl.profile_option_id,fpov.level_id,fpov.level_value;
25
26
27 v_sql_valid varchar2(4000);
28 v_whr_loc number;
29 v_sel_loc number;
30 v_from_loc number;
31 v_into_loc number;
32 v_col_loc number;
33 v_visoval_loc number;
34 v_profoval_loc number;
35 v_visoval varchar2(1000);
36 v_profoval varchar2(240) :='NO_VAL';
37 v_profoval_num number;
38 v_where_ins varchar2(2000);
39 v_where_ins_into varchar2(2000);
40 i number := 1;
41
42 BEGIN
43
44 FOR sql_rec IN get_sql
45 LOOP
46 v_profoval := sql_rec.prof_val;
47 v_sql_valid := sql_rec.sql_valid;
48
49 if v_profoval is not null and v_sql_valid is not null then
50 select instr(v_sql_valid,'INTO') into v_into_loc from dual;
51 select instr(v_sql_valid,':VISIBLE_OPTION_VALUE') into v_visoval_loc from dual;
52 select instr(v_sql_valid,':PROFILE_OPTION_VALUE') into v_profoval_loc from dual;
53 select instr(v_sql_valid,'WHERE',-1,1) into v_whr_loc from dual;
54 select instr(v_sql_valid,'FROM',1,1) into v_from_loc from dual;
55 --select instr(v_sql_valid,'COLUMN') into v_col_loc from dual;
56 --v_sql_valid := replace(v_sql_valid,substr(v_sql_valid,v_col_loc-1),'');
57 if v_profoval_loc < v_visoval_loc then
58
59 v_where_ins := replace(v_sql_valid,'WHERE','WHERE '||
60 substr(v_sql_valid,8,instr(v_sql_valid,',',1,1)-1-7)||' = '||''''||v_profoval||''''||' and');
61 v_where_ins := substr(v_sql_valid,1,v_whr_loc-1)||'WHERE '||
62 substr(v_sql_valid,8,instr(v_sql_valid,',',1,1)-1-7)||' = '||''''||v_profoval||''''||' and'||
63 substr(v_sql_valid,v_whr_loc+5,length(v_sql_valid));
64
65 else
66
67 v_where_ins := replace(v_sql_valid,'WHERE','WHERE '||
68 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' and');
69 end if;
70
71 v_where_ins_into := substr(v_where_ins,v_into_loc,v_from_loc-v_into_loc);
72 v_where_ins := replace(v_where_ins,substr(v_where_ins,v_into_loc,v_from_loc-v_into_loc),'');
73 v_where_ins := substr(v_where_ins,2,instr(v_where_ins,'"',-1,1)-2);
74
75
76 begin
77 if v_profoval_loc < v_visoval_loc then
78 execute immediate v_where_ins into v_profoval,v_visoval;
79 null;
80 else
81 execute immediate v_where_ins into v_visoval,v_profoval;
82 null;
83 end if;
84 exception
85 when others then null;
86 end;
87
88
89 end if;
90
91 i := i +1 ;
92 END LOOP;
93
94 if v_visoval is not null then
95 return v_visoval;
96 else
97 return v_profoval;
98 end if;
99
100 v_sql_valid :=null;
101 v_whr_loc :=null;
102 v_sel_loc :=null;
103 v_from_loc :=null;
104 v_into_loc :=null;
105 v_col_loc :=null;
106 v_visoval_loc :=null;
107 v_profoval_loc :=null;
108 v_visoval :=null;
109 v_profoval :=null;
110 v_profoval_num :=null;
111 v_where_ins :=null;
112 v_where_ins_into :=null;
113
114 EXCEPTION
115 when others then return v_profoval;
116 end;
117
118
119 END OKS_PROFILES_VISIBLE;