[Home] [Help]
PACKAGE BODY: APPS.ADX_PRF_PKG
Source
1 PACKAGE BODY adx_prf_pkg AS
2 /* $Header: ADXPRFB.pls 120.7 2011/09/22 08:31:41 sbandla 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 APPLSYS.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
96 execute immediate 'create synonym ADX_PRE_AUTOCONFIG for APPLSYS.ADX_PRE_AUTOCONFIG';
97 end;
98
99 level_value_app_id_temp := p_level_value_app_id;
100 if level_value_app_id_temp = '' then
101 level_value_app_id_temp := NULL;
102 end if;
103
104 -- convert parameters to type expected by FND API
105 if (p_level_id = 10001) then
106 level_name := 'SITE';
107 elsif (p_level_id = 10002) then
108 level_name := 'APPL';
109 elsif (p_level_id = 10003) then
110 level_name := 'RESP';
111 elsif (p_level_id = 10004) then
112 level_name := 'USER';
113 elsif (p_level_id = 10005) then
114 level_name := 'SERVER';
115 elsif (p_level_id = 10006) then
116 level_name := 'ORG';
117 elsif (p_level_id = 10007) then
118 level_name := 'SERVRESP';
119 end if;
120
121 -- Setting update_only and insert_only based on the values passed
122 if (p_insert_only = FALSE) then
123 insert_flag := 'F';
124 if (p_update_only = FALSE)then
125 update_flag := 'F';
126 else
127 update_flag := 'T';
128 end if;
129 else
130 insert_flag := 'T';
131 if (p_update_only = FALSE) then
132 update_flag := 'F';
133 else
134 update_flag := 'T';
135 end if;
136 end if;
137
138 --
139 -- Update the ADX_PRE_AUTOCONFIG table for rollback
140 -- purposes.
141 --
142 declare
143 str varchar2(700);
144 countrow number;
145 begin
146 -- If the value is to be set at the SITE level
147 if (p_level_value2 is NULL) then
148 str := 'select count(*)
149 from ADX_PRE_AUTOCONFIG where
150 application_id=:1 and
151 profile_option_name=:2 and
152 level_id=:3 and
153 level_value=:4 and
154 context_name=:5 and
155 update_only= :6 and
156 insert_only= :7';
157 execute immediate str INTO countrow
158 using p_application_id, p_profile_option_name, p_level_id,
159 p_level_value, p_context_name,update_flag,insert_flag;
160
161 if (countrow>0) then -- update table since row already exist
162 str := 'update ADX_PRE_AUTOCONFIG set
163 profile_value=:1 where
164 application_id=:2 and profile_option_name=:3 and
165 level_id=:4 and level_value=:5 and
166 context_name=:6 and update_only=:7 and
167 insert_only=:8';
168 execute immediate str
169 using old_pv_rec.profile_option_value, p_application_id,
170 p_profile_option_name, p_level_id, p_level_value,
171 p_context_name, update_flag, insert_flag;
172
173 else -- insert new row into table since one does not exist yet
174 str:='insert into ADX_PRE_AUTOCONFIG
175 (application_id, profile_option_name,
176 level_id,level_value,profile_value,
177 level_value_app_id,context_name,
178 update_only,insert_only)
179 values(:1, :2, :3, :4, :5, :6, :7, :8, :9)';
180 execute immediate str
181 using p_application_id, p_profile_option_name, p_level_id,
182 p_level_value, old_pv_rec.profile_option_value,
183 level_value_app_id_temp, p_context_name, update_flag,
184 insert_flag;
185 end if;
186
187 -- Call FND API to update/insert the profile option value in the
188 -- fnd_profile_option_values table
189 -- Note that for setting profiles at the SITE level
190 -- the last 3 parameters defined in the SAVE api are not required.
191
192 ret_value := FND_PROFILE.SAVE(p_profile_option_name,
193 p_profile_value,
194 level_name);
195 else
196 str:= 'select count(*)
197 from ADX_PRE_AUTOCONFIG where
198 application_id=:1 and
199 profile_option_name=:2 and
200 level_id=:3 and
201 level_value=:4 and
202 level_value_app_id=:5 and
203 context_name=:6 and
204 update_only= :7 and
205 insert_only= :8 and
206 level_value2= :9';
207 execute immediate str INTO countrow
208 using p_application_id, p_profile_option_name, p_level_id,
209 p_level_value, level_value_app_id_temp, p_context_name,
210 update_flag,insert_flag, p_level_value2;
211
212 if (countrow>0) then -- update table since row already exist
213 str := 'update ADX_PRE_AUTOCONFIG set
214 profile_value=:1 where
215 application_id=:2 and profile_option_name=:3 and
216 level_id=:4 and level_value=:5 and
217 level_value_app_id=:6 and
218 context_name=:7 and update_only=:8 and
219 insert_only=:9 and level_value2=:10';
220 execute immediate str
221 using old_pv_rec.profile_option_value, p_application_id,
222 p_profile_option_name, p_level_id, p_level_value,
223 level_value_app_id_temp, p_context_name, update_flag,
224 insert_flag, p_level_value2;
225
226 else -- insert new row into table since one does not exist yet
227 str:='insert into ADX_PRE_AUTOCONFIG
228 values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
229 execute immediate str
230 using p_application_id, p_profile_option_name, p_level_id,
231 p_level_value, old_pv_rec.profile_option_value,
232 level_value_app_id_temp, p_context_name, update_flag,
233 insert_flag, p_level_value2;
234 end if;
235
236 -- Call FND API to update/insert the profile option value in the
237 -- fnd_profile_option_values table
238
239 ret_value := FND_PROFILE.SAVE(p_profile_option_name,
240 p_profile_value,
241 level_name,
242 p_level_value,
243 p_level_value_app_id,
244 p_level_value2);
245 end if;
246
247 end; -- of begin block
248
249 if ret_value = TRUE then
250 println(' ');
251 println('[ ' || p_profile_option_name || ' ]');
252 println(' Application Id : '||p_application_id);
253 println(' Profile Value : '||p_profile_value);
254 println(' Level Name: '||level_name);
255 println(' INFO : Updated/created profile option value.');
256 println('. ');
257 else
258 println(' ');
259 println('[ ' || p_profile_option_name || ' ]');
260 println(' Application Id : '||p_application_id);
261 println(' Profile Value : '||p_profile_value);
262 println(' Level Name : '||level_name);
263 println(' INFO : Error updating/creating profile option value.');
264 println('. ');
265 end if;
266
267 END; -- set_profile()
268
269
270 END adx_prf_pkg;