1 package body FND_PROFILE_OPTION_VALUES_PKG as
2 /* $Header: AFPOMPVB.pls 120.2.12010000.1 2008/07/25 14:19:48 appldev ship $ */
3
4 function GET_HIERARCHY_TYPE(
5 X_PROFILE_OPTION_ID in NUMBER,
6 X_APPLICATION_ID in NUMBER)
7 return varchar2 is
8 L_HIERARCHY_TYPE VARCHAR2(8);
9 begin
10
11 select HIERARCHY_TYPE
12 into L_HIERARCHY_TYPE
13 from FND_PROFILE_OPTIONS
14 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
15 and APPLICATION_ID = X_APPLICATION_ID;
16
17 if SQL%NOTFOUND then
18 raise no_data_found;
19 end if;
20
21 return L_HIERARCHY_TYPE;
22
23 end GET_HIERARCHY_TYPE;
24
25 /* This procedure is used to insert a row into fnd_profile_option_values.
26 ** Due to the nature of profile option values having levels and granular
27 ** values associated to its levels, this routine distinguishes between
28 ** these levels to ensure data integrity.
29 */
30 procedure INSERT_ROW (
31 X_ROWID in out nocopy VARCHAR2,
32 X_APPLICATION_ID in NUMBER,
33 X_PROFILE_OPTION_ID in NUMBER,
34 X_LEVEL_ID in NUMBER,
35 X_LEVEL_VALUE in NUMBER,
36 X_CREATION_DATE in DATE,
37 X_CREATED_BY in NUMBER,
38 X_LAST_UPDATE_DATE in DATE,
39 X_LAST_UPDATED_BY in NUMBER,
40 X_LAST_UPDATE_LOGIN in NUMBER,
41 X_PROFILE_OPTION_VALUE in VARCHAR2,
42 X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
43 X_LEVEL_VALUE2 in NUMBER
44 ) is
45
46 -- Site level cursor
47 cursor S is select ROWID from FND_PROFILE_OPTION_VALUES
48 where APPLICATION_ID = X_APPLICATION_ID
49 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
50 and LEVEL_ID = X_LEVEL_ID
51 and LEVEL_VALUE = 0;
52
53 -- Application/Server/Org level cursor
54 cursor ARSO is select ROWID from FND_PROFILE_OPTION_VALUES
55 where APPLICATION_ID = X_APPLICATION_ID
56 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
57 and LEVEL_ID = X_LEVEL_ID
58 and LEVEL_VALUE = X_LEVEL_VALUE
59 and LEVEL_VALUE_APPLICATION_ID is null
60 and LEVEL_VALUE2 is null;
61
62 -- Responsibility level cursor
63 cursor R is select ROWID from FND_PROFILE_OPTION_VALUES
64 where APPLICATION_ID = X_APPLICATION_ID
65 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
66 and LEVEL_ID = X_LEVEL_ID
67 and LEVEL_VALUE = X_LEVEL_VALUE
68 and LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID
69 and LEVEL_VALUE2 is null;
70
71 -- ServResp level cursor
72 cursor SR is select ROWID from FND_PROFILE_OPTION_VALUES
73 where APPLICATION_ID = X_APPLICATION_ID
74 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
75 and LEVEL_ID = X_LEVEL_ID
76 and LEVEL_VALUE = X_LEVEL_VALUE
77 and LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID
78 and LEVEL_VALUE2 = X_LEVEL_VALUE2;
79
80 L_HIERARCHY_TYPE VARCHAR2(8);
81 L_PROFILE_OPTION_NAME VARCHAR2(80);
82 profile_option_value_too_large EXCEPTION;
83
84 begin
85
86 -- If profile option value being set is > 240 characters, then raise the
87 -- profile_option_value_too_large exception.
88 if length(X_PROFILE_OPTION_VALUE) > 240 then
89 raise profile_option_value_too_large;
90 end if;
91
92 L_HIERARCHY_TYPE := FND_PROFILE_OPTION_VALUES_PKG.GET_HIERARCHY_TYPE
93 (X_PROFILE_OPTION_ID, X_APPLICATION_ID);
94
95 /* Being conservative here and wanting to make sure that levels get
96 profile option values inserted correctly. For example, if, by some
97 chance that, a site-level profile option value is being inserted with
98 a non-null level_value (which does not apply), the level_value is
99 overriden as well as any other non-applicable columns on insertion.
100 */
101
102 if (X_LEVEL_ID = 10001) then
103 -- Site level
104 insert into FND_PROFILE_OPTION_VALUES (
105 APPLICATION_ID,
106 PROFILE_OPTION_ID,
107 LEVEL_ID,
108 LEVEL_VALUE,
109 LEVEL_VALUE_APPLICATION_ID,
110 PROFILE_OPTION_VALUE,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN,
114 CREATION_DATE,
115 CREATED_BY,
116 LEVEL_VALUE2
117 ) values (
118 X_APPLICATION_ID,
119 X_PROFILE_OPTION_ID,
120 X_LEVEL_ID,
121 0, -- LEVEL_VALUE = 0 for Site level
122 NULL, -- LEVEL_VALUE_APPLICATION_ID is not applicable
123 X_PROFILE_OPTION_VALUE,
124 X_LAST_UPDATE_DATE,
125 X_LAST_UPDATED_BY,
126 X_LAST_UPDATE_LOGIN,
127 X_CREATION_DATE,
128 X_CREATED_BY,
129 NULL -- LEVEL_VALUE2 is not applicable
130 );
131
132 open S;
133 fetch S into X_ROWID;
134 if (S%notfound) then
135 close S;
136 raise no_data_found;
137 end if;
138 close S;
139
140 elsif (X_LEVEL_ID = 10007 and L_HIERARCHY_TYPE = 'SERVRESP') then
141 -- ServResp level
142 insert into FND_PROFILE_OPTION_VALUES (
143 APPLICATION_ID,
144 PROFILE_OPTION_ID,
145 LEVEL_ID,
146 LEVEL_VALUE,
147 LEVEL_VALUE_APPLICATION_ID,
148 PROFILE_OPTION_VALUE,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN,
152 CREATION_DATE,
153 CREATED_BY,
154 LEVEL_VALUE2
155 ) values (
156 X_APPLICATION_ID,
157 X_PROFILE_OPTION_ID,
158 X_LEVEL_ID,
159 X_LEVEL_VALUE,
160 X_LEVEL_VALUE_APPLICATION_ID,
161 X_PROFILE_OPTION_VALUE,
162 X_LAST_UPDATE_DATE,
163 X_LAST_UPDATED_BY,
164 X_LAST_UPDATE_LOGIN,
165 X_CREATION_DATE,
166 X_CREATED_BY,
167 X_LEVEL_VALUE2
168 );
169
170 open SR;
171 fetch SR into X_ROWID;
172 if (SR%notfound) then
173 close SR;
174 raise no_data_found;
175 end if;
176 close SR;
177
178 elsif ((X_LEVEL_ID = 10006 and L_HIERARCHY_TYPE = 'ORG') or
179 (X_LEVEL_ID = 10005 and L_HIERARCHY_TYPE = 'SERVER') or
180 (X_LEVEL_ID = 10004) or
181 (X_LEVEL_ID = 10002 and L_HIERARCHY_TYPE = 'SECURITY')) then
182 -- Appl/Resp/Server/Org levels
183 insert into FND_PROFILE_OPTION_VALUES (
184 APPLICATION_ID,
185 PROFILE_OPTION_ID,
186 LEVEL_ID,
187 LEVEL_VALUE,
188 LEVEL_VALUE_APPLICATION_ID,
189 PROFILE_OPTION_VALUE,
190 LAST_UPDATE_DATE,
191 LAST_UPDATED_BY,
192 LAST_UPDATE_LOGIN,
193 CREATION_DATE,
194 CREATED_BY,
195 LEVEL_VALUE2
196 ) values (
197 X_APPLICATION_ID,
198 X_PROFILE_OPTION_ID,
199 X_LEVEL_ID,
200 X_LEVEL_VALUE,
201 NULL,
202 X_PROFILE_OPTION_VALUE,
203 X_LAST_UPDATE_DATE,
204 X_LAST_UPDATED_BY,
205 X_LAST_UPDATE_LOGIN,
206 X_CREATION_DATE,
207 X_CREATED_BY,
208 NULL -- LEVEL_VALUE2 is not applicable
209 );
210
211 open ARSO;
212 fetch ARSO into X_ROWID;
213 if (ARSO%notfound) then
214 close ARSO;
215 raise no_data_found;
216 end if;
217 close ARSO;
218
219 elsif (X_LEVEL_ID = 10003 and L_HIERARCHY_TYPE = 'SECURITY') then
220
221 -- Resp level
222 insert into FND_PROFILE_OPTION_VALUES (
223 APPLICATION_ID,
224 PROFILE_OPTION_ID,
225 LEVEL_ID,
226 LEVEL_VALUE,
227 LEVEL_VALUE_APPLICATION_ID,
228 PROFILE_OPTION_VALUE,
229 LAST_UPDATE_DATE,
230 LAST_UPDATED_BY,
231 LAST_UPDATE_LOGIN,
232 CREATION_DATE,
233 CREATED_BY,
234 LEVEL_VALUE2
235 ) values (
236 X_APPLICATION_ID,
237 X_PROFILE_OPTION_ID,
238 X_LEVEL_ID,
239 X_LEVEL_VALUE,
240 X_LEVEL_VALUE_APPLICATION_ID,
241 X_PROFILE_OPTION_VALUE,
242 X_LAST_UPDATE_DATE,
243 X_LAST_UPDATED_BY,
244 X_LAST_UPDATE_LOGIN,
245 X_CREATION_DATE,
246 X_CREATED_BY,
247 NULL -- LEVEL_VALUE2 is not applicable
248 );
249
250 open R;
251 fetch R into X_ROWID;
252 if (R%notfound) then
253 close R;
254 raise no_data_found;
255 end if;
256 close R;
257
258 end if;
259 exception
260 when profile_option_value_too_large then
261
262 select PROFILE_OPTION_NAME
263 into L_PROFILE_OPTION_NAME
264 from FND_PROFILE_OPTIONS
265 where APPLICATION_ID = X_APPLICATION_ID
266 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID;
267
268 fnd_message.set_name('FND', 'FND_PROFILE_OPTION_VAL_TOO_LRG');
269 fnd_message.set_token('PROFILE_OPTION_NAME', L_PROFILE_OPTION_NAME);
270 fnd_message.set_token('PROFILE_OPTION_VALUE', X_PROFILE_OPTION_VALUE);
271 app_exception.raise_exception;
272
273 end INSERT_ROW;
274
275 /* This procedure is used to update profile option values at a given level,
276 * (if it applies). If the profile fails to update, it means that there is
277 * no row to update. If that occurs, INSERT_ROW is called to insert the
278 * profile option value.
279 */
280 procedure UPDATE_ROW (
281 X_APPLICATION_ID in NUMBER,
282 X_PROFILE_OPTION_ID in NUMBER,
283 X_LEVEL_ID in NUMBER,
284 X_LEVEL_VALUE in NUMBER,
285 X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
286 X_LEVEL_VALUE2 in NUMBER,
287 X_PROFILE_OPTION_VALUE in VARCHAR2,
288 X_LAST_UPDATE_DATE in DATE,
289 X_LAST_UPDATED_BY in NUMBER,
290 X_LAST_UPDATE_LOGIN in NUMBER
291 ) is
292
293 L_ROWID varchar2(20);
294 L_HIERARCHY_TYPE VARCHAR2(8);
295 L_PROFILE_OPTION_NAME VARCHAR2(80);
296 profile_option_value_too_large EXCEPTION;
297
298 begin
299
300 -- If profile option value being set is > 240 characters, then raise the
301 -- profile_option_value_too_large exception.
302 if length(X_PROFILE_OPTION_VALUE) > 240 then
303 raise profile_option_value_too_large;
304 end if;
305
306 L_HIERARCHY_TYPE := FND_PROFILE_OPTION_VALUES_PKG.GET_HIERARCHY_TYPE
307 (X_PROFILE_OPTION_ID, X_APPLICATION_ID);
308
309 if (X_LEVEL_ID = 10007 and L_HIERARCHY_TYPE = 'SERVRESP') then
310 /* ServResp U P D A T E */
311 update FND_PROFILE_OPTION_VALUES
312 set PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE,
313 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
314 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
315 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
316 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
317 and APPLICATION_ID = X_APPLICATION_ID
318 and LEVEL_ID = X_LEVEL_ID
319 and LEVEL_VALUE = X_LEVEL_VALUE
320 and LEVEL_VALUE2 = X_LEVEL_VALUE2
321 and (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
322 or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);
323 elsif ((X_LEVEL_ID = 10006 and L_HIERARCHY_TYPE = 'ORG') or
324 (X_LEVEL_ID = 10005 and L_HIERARCHY_TYPE = 'SERVER') or
325 (X_LEVEL_ID = 10004) or
326 (X_LEVEL_ID = 10003 and L_HIERARCHY_TYPE = 'SECURITY') or
327 (X_LEVEL_ID = 10002 and L_HIERARCHY_TYPE = 'SECURITY') or
328 (X_LEVEL_ID = 10001)) then
329 /* U P D A T E */
330 update FND_PROFILE_OPTION_VALUES
331 set PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE,
332 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
333 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
334 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
335 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
336 and APPLICATION_ID = X_APPLICATION_ID
337 and LEVEL_ID = X_LEVEL_ID
338 and LEVEL_VALUE = X_LEVEL_VALUE
339 and (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
340 or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);
341 end if;
342
343 if SQL%NOTFOUND then
344 /* I N S E R T */
345 FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW(
346 L_ROWID,
347 X_APPLICATION_ID,
348 X_PROFILE_OPTION_ID,
349 X_LEVEL_ID,
350 X_LEVEL_VALUE,
351 sysdate, -- X_CREATION_DATE
352 X_LAST_UPDATED_BY, -- X_CREATED_BY
353 sysdate, -- X_LAST_UPDATE_DATE
354 X_LAST_UPDATED_BY,
355 X_LAST_UPDATE_LOGIN,
356 X_PROFILE_OPTION_VALUE,
357 X_LEVEL_VALUE_APPLICATION_ID,
358 X_LEVEL_VALUE2
359 );
360 end if;
361 exception
362 when profile_option_value_too_large then
363
364 select PROFILE_OPTION_NAME
365 into L_PROFILE_OPTION_NAME
366 from FND_PROFILE_OPTIONS
367 where APPLICATION_ID = X_APPLICATION_ID
368 and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID;
369
370 fnd_message.set_name('FND', 'FND_PROFILE_OPTION_VAL_TOO_LRG');
371 fnd_message.set_token('PROFILE_OPTION_NAME', L_PROFILE_OPTION_NAME);
372 fnd_message.set_token('PROFILE_OPTION_VALUE', X_PROFILE_OPTION_VALUE);
373 app_exception.raise_exception;
374
375 end UPDATE_ROW;
376
377 /* Overloaded UPDATE_ROW */
378 procedure UPDATE_ROW(
379 X_APPLICATION_ID in NUMBER,
380 X_PROFILE_OPTION_ID in NUMBER,
381 X_LEVEL_ID in NUMBER,
382 X_LEVEL_VALUE in NUMBER,
383 X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
384 X_PROFILE_OPTION_VALUE in VARCHAR2,
385 X_LAST_UPDATE_DATE in DATE,
386 X_LAST_UPDATED_BY in NUMBER,
387 X_LAST_UPDATE_LOGIN in NUMBER
388 ) is
389
390 begin
391
392 if (X_LEVEL_ID <> 10007) then
393 /* Call UPDATE_ROW passing NULL for LEVEL_VALUE2 if
394 level_id <> 10007
395 */
396 UPDATE_ROW(
397 X_APPLICATION_ID,
398 X_PROFILE_OPTION_ID,
399 X_LEVEL_ID,
400 X_LEVEL_VALUE,
401 X_LEVEL_VALUE_APPLICATION_ID,
402 NULL,
403 X_PROFILE_OPTION_VALUE,
404 X_LAST_UPDATE_DATE,
405 X_LAST_UPDATED_BY,
406 X_LAST_UPDATE_LOGIN);
407 end if;
408
409 end UPDATE_ROW;
410
411 procedure DELETE_ROW (
412 X_APPLICATION_ID in NUMBER,
413 X_PROFILE_OPTION_ID in NUMBER,
414 X_LEVEL_ID in NUMBER,
415 X_LEVEL_VALUE in NUMBER,
416 X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
417 X_LEVEL_VALUE2 in NUMBER
418 ) is
419
420 L_HIERARCHY_TYPE VARCHAR2(8);
421
422 begin
423
424 L_HIERARCHY_TYPE := FND_PROFILE_OPTION_VALUES_PKG.GET_HIERARCHY_TYPE
425 (X_PROFILE_OPTION_ID, X_APPLICATION_ID);
426
427 if (X_LEVEL_ID = 10007 and L_HIERARCHY_TYPE = 'SERVRESP') then
428
429 /* ServResp D E L E T E */
430 delete from FND_PROFILE_OPTION_VALUES
431 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
432 and APPLICATION_ID = X_APPLICATION_ID
433 and LEVEL_ID = X_LEVEL_ID
434 and LEVEL_VALUE = X_LEVEL_VALUE
435 and LEVEL_VALUE2 = X_LEVEL_VALUE2
436 and (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
437 or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);
438
439 elsif ((X_LEVEL_ID = 10006 and L_HIERARCHY_TYPE = 'ORG') or
440 (X_LEVEL_ID = 10005 and L_HIERARCHY_TYPE = 'SERVER') or
441 (X_LEVEL_ID = 10004) or
442 (X_LEVEL_ID = 10002 and L_HIERARCHY_TYPE = 'SECURITY') or
443 (X_LEVEL_ID = 10001)) then
444
445 /* D E L E T E */
446 delete from FND_PROFILE_OPTION_VALUES
447 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
448 and APPLICATION_ID = X_APPLICATION_ID
449 and LEVEL_ID = X_LEVEL_ID
450 and LEVEL_VALUE = X_LEVEL_VALUE
451 and (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
452 or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);
453
454 elsif (X_LEVEL_ID = 10003 and L_HIERARCHY_TYPE = 'SECURITY') then
455
456 /* D E L E T E */
457 delete from FND_PROFILE_OPTION_VALUES
458 where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
459 and APPLICATION_ID = X_APPLICATION_ID
460 and LEVEL_ID = X_LEVEL_ID
461 and LEVEL_VALUE = X_LEVEL_VALUE
462 and (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
463 or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);
464
465 end if;
466
467 if (SQL%NOTFOUND) then
468 raise NO_DATA_FOUND;
469 end if;
470
471 end DELETE_ROW;
472
473 /* Overloaded DELETE_ROW */
474 procedure DELETE_ROW(
475 X_APPLICATION_ID in NUMBER,
476 X_PROFILE_OPTION_ID in NUMBER,
477 X_LEVEL_ID in NUMBER,
478 X_LEVEL_VALUE in NUMBER,
479 X_LEVEL_VALUE_APPLICATION_ID in NUMBER
480 ) is
481
482 begin
483
484 if (X_LEVEL_ID <> 10007) then
485 /* Call DELETE_ROW passing NULL for LEVEL_VALUE2 if
486 level_id <> 10007
487 */
488 DELETE_ROW (
489 X_APPLICATION_ID,
490 X_PROFILE_OPTION_ID,
491 X_LEVEL_ID,
492 X_LEVEL_VALUE,
493 X_LEVEL_VALUE_APPLICATION_ID,
494 NULL);
495 end if;
496
497 end DELETE_ROW;
498
499 /* This procedure is only going to be called from
500 * FND_PROFILE_OPTIONS_PKG.DELETE_ROW which deletes profile option
501 * definitions. This procedure ensures that there will be no dangling
502 * references in FND_PROFILE_OPTION_VALUES to the profile option being
503 * deleted, i.e. if a profile is being deleted, it should have no rows
504 * for profile option values.
505 */
506 procedure DELETE_PROFILE_OPTION_VALUES (X_PROFILE_OPTION_NAME in VARCHAR2) is
507 L_PROFILE_OPTION_ID number;
508 L_APPLICATION_ID number;
509 begin
510 -- Obtain the profile_option_id and application_id of the profile
511 -- option being deleted using the profile option name.
512 select profile_option_id, application_id
513 into L_PROFILE_OPTION_ID, L_APPLICATION_ID
514 from fnd_profile_options
515 where profile_option_name = X_PROFILE_OPTION_NAME;
516
517 -- If the given profile option does not exist, then raise no_data_found;
518 if (SQL%NOTFOUND) then
519 raise NO_DATA_FOUND;
520 end if;
521
522 -- Delete all rows with the profile_option_id, application_id
523 -- combination
524 delete from fnd_profile_option_values
525 where profile_option_id = L_PROFILE_OPTION_ID
526 and application_id = L_APPLICATION_ID;
527
528 -- It is possible for a profile option to not have any profile option
529 -- values. So if the delete raises SQL%NOTFOUND, it is
530 -- transparently handled.
531 if (SQL%NOTFOUND) then
532 NULL;
533 end if;
534
535 end DELETE_PROFILE_OPTION_VALUES;
536
537 end FND_PROFILE_OPTION_VALUES_PKG;
[Home] [Help]