1 PACKAGE BODY adx_prf_pkg AS
2 /* $Header: ADXPRFB.pls 120.4.12010000.2 2009/01/08 11:57:37 rdamodar ship $ */
3
4
5 --
6 -- PRINTLN (Internal)
7 -- Print messages as needed
8 -- IN
9 -- msg
10 --
11 procedure PRINTLN(msg in varchar2) is
12 begin
13 dbms_output.put_line(msg);
14 end;
15
16 --
17 -- set_profile
18 -- Set Profile options via AutoConfig
19 -- IN
20 -- p_application_id
21 -- p_profile_option_name
22 -- p_level_id
23 -- p_level_value
24 -- p_profile_value
25 -- p_level_value_app_id
26 -- p_context_name
27 -- p_update_only
28 -- p_insert_only
29 -- p_level_value2
30 --
31 PROCEDURE set_profile(p_application_id in number,
32 p_profile_option_name in varchar2,
33 p_level_id in number,
34 p_level_value in number,
35 p_profile_value in varchar2,
36 p_level_value_app_id in number,
37 p_context_name in varchar2,
38 p_update_only in boolean default FALSE,
39 p_insert_only in boolean default FALSE,
40 p_level_value2 in varchar2 default NULL)
41 IS
42 old_pv_rec fnd_profile_option_values%rowtype;
43 level_value_app_id_temp number;
44 level_name varchar2(30);
45 update_flag varchar2(1);
46 insert_flag varchar2(1);
47 ret_value boolean;
48
49 BEGIN
50 begin
51 if (p_level_value2 is NULL) then
52 select *
53 into old_pv_rec
54 from fnd_profile_option_values
55 where (application_id, profile_option_id) in (
56 select application_id, profile_option_id
57 from fnd_profile_options
58 where application_id = p_application_id
59 and profile_option_name = p_profile_option_name)
60 and level_id = p_level_id;
61 else
62 select *
63 into old_pv_rec
64 from fnd_profile_option_values
65 where (application_id, profile_option_id) in (
66 select application_id, profile_option_id
67 from fnd_profile_options
68 where application_id = p_application_id
69 and profile_option_name = p_profile_option_name)
70 and level_id = p_level_id
71 and level_value = p_level_value
72 and level_value2 = p_level_value2;
73 end if;
74 exception
75 when no_data_found then null;
76 end;
77
78 declare
79 tablename varchar2(100);
80 begin
81 select tname into tablename from tab where tname ='ADX_PRE_AUTOCONFIG' and tabtype in ('TABLE', 'SYNONYM');
82 exception WHEN NO_DATA_FOUND THEN
83 execute immediate 'create table ADX_PRE_AUTOCONFIG (
84 application_id number,
85 profile_option_name varchar2(100),
86 level_id number,
87 level_value number,
88 profile_value varchar2(300),
89 level_value_app_id number,
90 context_name varchar2(100),
91 update_only varchar2(1),
92 insert_only varchar2(1),
93 level_value2 varchar2(100)
94 )';
95 end;
96
97 -- Add the column LEVEL_VALUE2 to the table ADX_PRE_AUTOCONFIG if
98 -- the table does not have this column and
99 -- the profile value is to be updated at the SERVRESP level
100 declare
101 columnname varchar2(100);
102 begin
103 if (p_level_value2 is not NULL) then
104 select column_name into columnname from user_tab_columns where table_name like 'ADX_PRE_AUTOCONFIG' and column_name like 'LEVEL_VALUE2';
105 end if;
106 exception WHEN NO_DATA_FOUND THEN
107 execute immediate 'alter table ADX_PRE_AUTOCONFIG
108 add (level_value2 varchar2(100))';
109 end;
110 level_value_app_id_temp := p_level_value_app_id;
111 if level_value_app_id_temp = '' then
112 level_value_app_id_temp := NULL;
113 end if;
114
115 -- convert parameters to type expected by FND API
116 if (p_level_id = 10001) then
117 level_name := 'SITE';
118 elsif (p_level_id = 10002) then
119 level_name := 'APPL';
120 elsif (p_level_id = 10003) then
121 level_name := 'RESP';
122 elsif (p_level_id = 10004) then
123 level_name := 'USER';
124 elsif (p_level_id = 10005) then
125 level_name := 'SERVER';
126 elsif (p_level_id = 10006) then
127 level_name := 'ORG';
128 elsif (p_level_id = 10007) then
129 level_name := 'SERVRESP';
130 end if;
131
132 -- Setting update_only and insert_only based on the values passed
133 if (p_insert_only = FALSE) then
134 insert_flag := 'F';
135 if (p_update_only = FALSE)then
136 update_flag := 'F';
137 else
138 update_flag := 'T';
139 end if;
140 else
141 insert_flag := 'T';
142 if (p_update_only = FALSE) then
143 update_flag := 'F';
144 else
145 update_flag := 'T';
146 end if;
147 end if;
148
149 --
150 -- Update the ADX_PRE_AUTOCONFIG table for rollback
151 -- purposes.
152 --
153 declare
154 str varchar2(700);
155 countrow number;
156 begin
157 -- If the value is to be set at the SITE level
158 if (p_level_value2 is NULL) then
159 str := 'select count(*)
160 from ADX_PRE_AUTOCONFIG where
161 application_id=:1 and
162 profile_option_name=:2 and
163 level_id=:3 and
164 level_value=:4 and
165 context_name=:5 and
166 update_only= :6 and
167 insert_only= :7';
168 execute immediate str INTO countrow
169 using p_application_id, p_profile_option_name, p_level_id,
170 p_level_value, p_context_name,update_flag,insert_flag;
171
172 if (countrow>0) then -- update table since row already exist
173 str := 'update ADX_PRE_AUTOCONFIG set
174 profile_value=:1 where
175 application_id=:2 and profile_option_name=:3 and
176 level_id=:4 and level_value=:5 and
177 context_name=:6 and update_only=:7 and
178 insert_only=:8';
179 execute immediate str
180 using old_pv_rec.profile_option_value, p_application_id,
181 p_profile_option_name, p_level_id, p_level_value,
182 p_context_name, update_flag, insert_flag;
183
184 else -- insert new row into table since one does not exist yet
185 str:='insert into ADX_PRE_AUTOCONFIG
186 (application_id, profile_option_name,
187 level_id,level_value,profile_value,
188 level_value_app_id,context_name,
189 update_only,insert_only)
190 values(:1, :2, :3, :4, :5, :6, :7, :8, :9)';
191 execute immediate str
192 using p_application_id, p_profile_option_name, p_level_id,
193 p_level_value, old_pv_rec.profile_option_value,
194 level_value_app_id_temp, p_context_name, update_flag,
195 insert_flag;
196 end if;
197
198 -- Call FND API to update/insert the profile option value in the
199 -- fnd_profile_option_values table
200 -- Note that for setting profiles at the SITE level
201 -- the last 3 parameters defined in the SAVE api are not required.
202
203 ret_value := FND_PROFILE.SAVE(p_profile_option_name,
204 p_profile_value,
205 level_name);
206 else
207 str:= 'select count(*)
208 from ADX_PRE_AUTOCONFIG where
209 application_id=:1 and
210 profile_option_name=:2 and
211 level_id=:3 and
212 level_value=:4 and
213 level_value_app_id=:5 and
214 context_name=:6 and
215 update_only= :7 and
216 insert_only= :8 and
217 level_value2= :9';
218 execute immediate str INTO countrow
219 using p_application_id, p_profile_option_name, p_level_id,
220 p_level_value, level_value_app_id_temp, p_context_name,
221 update_flag,insert_flag, p_level_value2;
222
223 if (countrow>0) then -- update table since row already exist
224 str := 'update ADX_PRE_AUTOCONFIG set
225 profile_value=:1 where
226 application_id=:2 and profile_option_name=:3 and
227 level_id=:4 and level_value=:5 and
228 level_value_app_id=:6 and
229 context_name=:7 and update_only=:8 and
230 insert_only=:9 and level_value2=:10';
231 execute immediate str
232 using old_pv_rec.profile_option_value, p_application_id,
233 p_profile_option_name, p_level_id, p_level_value,
234 level_value_app_id_temp, p_context_name, update_flag,
235 insert_flag, p_level_value2;
236
237 else -- insert new row into table since one does not exist yet
238 str:='insert into ADX_PRE_AUTOCONFIG
239 values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
240 execute immediate str
241 using p_application_id, p_profile_option_name, p_level_id,
242 p_level_value, old_pv_rec.profile_option_value,
243 level_value_app_id_temp, p_context_name, update_flag,
244 insert_flag, p_level_value2;
245 end if;
246
247 -- Call FND API to update/insert the profile option value in the
248 -- fnd_profile_option_values table
249
250 ret_value := FND_PROFILE.SAVE(p_profile_option_name,
251 p_profile_value,
252 level_name,
253 p_level_value,
254 p_level_value_app_id,
255 p_level_value2);
256 end if;
257
258 end; -- of begin block
259
260 if ret_value = TRUE then
261 println(' ');
262 println('[ ' || p_profile_option_name || ' ]');
263 println(' Application Id : '||p_application_id);
264 println(' Profile Value : '||p_profile_value);
265 println(' Level Name: '||level_name);
266 println(' INFO : Updated/created profile option value.');
267 println('. ');
268 else
269 println(' ');
270 println('[ ' || p_profile_option_name || ' ]');
271 println(' Application Id : '||p_application_id);
272 println(' Profile Value : '||p_profile_value);
273 println(' Level Name : '||level_name);
274 println(' INFO : Error updating/creating profile option value.');
275 println('. ');
276 end if;
277
278 END; -- set_profile()
279
280
281 END adx_prf_pkg;
[Home] [Help]