[Home] [Help]
PACKAGE BODY: APPS.OKS_SETUPRPT_PVT
Source
1 PACKAGE BODY OKS_SETUPRPT_PVT AS
2 /* $Header: OKSSETPB.pls 120.0 2005/05/25 18:38:50 appldev noship $ */
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 ) return varchar2 is
9 -- p_lvl_val_appl_id number default NULL) return varchar2 is
10
11
12
13 CURSOR get_sql IS
14 select substr(replace(substr(upper(fpovl.sql_validation),instr(upper(fpovl.sql_validation),'SELECT',1,1)-1,
15 instr(upper(fpovl.sql_validation),'COLUMN',1,1) -
16 instr(upper(fpovl.sql_validation),'SELECT',1,1)),'\',''),2,
17 (length(replace(substr(upper(fpovl.sql_validation),instr(upper(fpovl.sql_validation),'SELECT',1,1)-1,
18 instr(upper(fpovl.sql_validation),'COLUMN',1,1) -
19 instr(upper(fpovl.sql_validation),'SELECT',1,1)),'\','')) -2 ))SQL_VALID,
20 fpov.profile_option_value PROF_VAL
21 from fnd_profile_options_vl fpovl,
22 fnd_profile_option_values fpov
23 where fpovl.profile_option_id IN (
24 select profile_option_id
25 from fnd_profile_options_vl
26 where application_id = 515)
27 and fpov.profile_option_id = fpovl.profile_option_id
28 and fpovl.USER_VISIBLE_FLAG = 'Y'
29 and fpov.profile_option_id = p_profile_option_id
30 and fpov.level_id = p_level_id
31 order by fpovl.profile_option_id,fpov.level_id,fpov.level_value;
32
33 v_sql_valid varchar2(4000);
34 v_whr_loc number;
35 v_sel_loc number;
36 v_from_loc number;
37 v_orderby_loc number;
38 v_into_loc number;
39 v_col_loc number;
40 v_visoval_loc number;
41 v_profoval_loc number;
42 v_visoval varchar2(1000);
43 v_profoval varchar2(240) :='NO_VAL';
44 v_profoval_num number;
45 v_where_ins varchar2(2000);
46 v_where_ins_into varchar2(2000);
47 i number := 1;
48
49 L_COUNTER NUMBER := 0;
50
51 BEGIN
52
53 FOR sql_rec IN get_sql LOOP
54 L_COUNTER := L_COUNTER + 1;
55 --dbms_output.put_line('IN LOOP '|| L_COUNTER);
56 v_profoval := sql_rec.prof_val;
57 v_sql_valid := sql_rec.sql_valid;
58
59
60 if v_profoval is not null and v_sql_valid is not null then
61 select instr(v_sql_valid,'INTO') into v_into_loc from dual;
62 select instr(v_sql_valid,':VISIBLE_OPTION_VALUE') into v_visoval_loc from dual;
63 select instr(v_sql_valid,':PROFILE_OPTION_VALUE') into v_profoval_loc from dual;
64 select instr(v_sql_valid,'WHERE',-1,1) into v_whr_loc from dual;
65 select instr(v_sql_valid,'FROM',1,1) into v_from_loc from dual;
66 select instr(v_sql_valid,'ORDER',1,1) into v_orderby_loc from dual;
67
68
69 if v_profoval_loc < v_visoval_loc then
70 if v_whr_loc <> 0 then
71 v_where_ins := replace(v_sql_valid,'WHERE','WHERE '||
72 substr(v_sql_valid,8,instr(v_sql_valid,',',1,1)-1-7)||' = '||''''||v_profoval||''''||' and');
73 v_where_ins := substr(v_sql_valid,1,v_whr_loc-1)||'WHERE '||
74 substr(v_sql_valid,8,instr(v_sql_valid,',',1,1)-1-7)||' = '||''''||v_profoval||''''||' and'||
75 substr(v_sql_valid,v_whr_loc+5,length(v_sql_valid));
76
77 else
78
79 if v_orderby_loc <> 0 then
80 v_where_ins := substr(v_sql_valid,1,instr(v_sql_valid,UPPER('order'),1)-1)||' WHERE '||
81 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' '||substr(v_sql_valid,instr(v_sql_valid,UPPER('order'),1));
82 else
83 v_where_ins := v_sql_valid ||'WHERE '||
84 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' and';
85 end if ;
86 end if;
87 else
88
89
90 if v_whr_loc <> 0 then
91 v_where_ins := replace(v_sql_valid,'WHERE','WHERE '||
92 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' and');
93 else
94 if v_orderby_loc <> 0 then
95 v_where_ins := substr(v_sql_valid,1,instr(v_sql_valid,UPPER('order'),1)-1)||' WHERE '||
96 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' '||substr(v_sql_valid,instr(v_sql_valid,UPPER('order'),1));
97 else
98 v_where_ins := v_sql_valid ||' WHERE '||
99 substr(v_sql_valid,instr(v_sql_valid,',',1,1)+1,v_into_loc-1-instr(v_sql_valid,',',1,1))||' = '||''''||v_profoval||''''||' ';
100
101
102 end if ;
103 end if ;
104 end if;
105
106 v_where_ins_into := substr(v_where_ins,v_into_loc,v_from_loc-v_into_loc);
107 v_where_ins := replace(v_where_ins,substr(v_where_ins,v_into_loc,v_from_loc-v_into_loc),'');
108 -- v_where_ins := substr(v_where_ins,1,instr(v_where_ins,'"',-1,1)-1);
109
110
111
112 begin
113
114
115 if v_profoval_loc < v_visoval_loc then
116 execute immediate v_where_ins into v_profoval,v_visoval;
117 null;
118 else
119 execute immediate v_where_ins into v_visoval,v_profoval;
120 null;
121 end if;
122
123 exception
124 when others then
125 v_profoval := NULL;
126 v_visoval := NULL;
127 end;
128
129
130 end if;
131
132 i := i +1 ;
133 END LOOP;
134
135 if v_visoval is not null then
136 return v_visoval;
137 else
138 return v_profoval;
139 end if;
140
141 v_sql_valid :=null;
142 v_whr_loc :=null;
143 v_sel_loc :=null;
144 v_from_loc :=null;
145 v_into_loc :=null;
146 v_col_loc :=null;
147 v_visoval_loc :=null;
148 v_profoval_loc :=null;
149 v_visoval :=null;
150 v_profoval :=null;
151 v_profoval_num :=null;
152 v_where_ins :=null;
153 v_where_ins_into :=null;
154
155 EXCEPTION
156 when others then return v_profoval;
157 END;
158
159
160 END OKS_SETUPRPT_PVT;