[Home] [Help]
PACKAGE BODY: APPS.FND_PROFILE_OPTIONS_PKG
Source
1 package body FND_PROFILE_OPTIONS_PKG as
2 /* $Header: AFPOMPOB.pls 120.4 2008/01/24 16:14:07 pdeluna ship $ */
3
4 -- ### OVERLOADED!
5 procedure INSERT_ROW (
6 X_ROWID in out NOCOPY VARCHAR2,
7 X_PROFILE_OPTION_NAME in VARCHAR2,
8 X_APPLICATION_ID in NUMBER,
9 X_PROFILE_OPTION_ID in NUMBER,
10 X_WRITE_ALLOWED_FLAG in VARCHAR2,
11 X_READ_ALLOWED_FLAG in VARCHAR2,
12 X_USER_CHANGEABLE_FLAG in VARCHAR2,
13 X_USER_VISIBLE_FLAG in VARCHAR2,
14 X_SITE_ENABLED_FLAG in VARCHAR2,
15 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
16 X_APP_ENABLED_FLAG in VARCHAR2,
17 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
18 X_RESP_ENABLED_FLAG in VARCHAR2,
19 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
20 X_USER_ENABLED_FLAG in VARCHAR2,
21 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
22 X_START_DATE_ACTIVE in DATE,
23 X_SQL_VALIDATION in VARCHAR2,
24 X_END_DATE_ACTIVE in DATE,
25 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
26 X_DESCRIPTION in VARCHAR2,
27 X_CREATION_DATE in DATE,
28 X_CREATED_BY in NUMBER,
29 X_LAST_UPDATE_DATE in DATE,
30 X_LAST_UPDATED_BY in NUMBER,
31 X_LAST_UPDATE_LOGIN in NUMBER,
32 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
33 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
34 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
35 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
36 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
37 X_SERVERRESP_ENABLED_FLAG in VARCHAR2,
38 X_SERVERRESP_UPD_ALLOW_FL in VARCHAR2
39 ) is
40
41 L_PROFILE_OPTION_NAME VARCHAR2(80) := UPPER(X_PROFILE_OPTION_NAME);
42 cursor C is select ROWID from FND_PROFILE_OPTIONS
43 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME;
44 begin
45
46 insert into FND_PROFILE_OPTIONS (
47 APPLICATION_ID,
48 PROFILE_OPTION_ID,
49 PROFILE_OPTION_NAME,
50 WRITE_ALLOWED_FLAG,
51 READ_ALLOWED_FLAG,
52 USER_CHANGEABLE_FLAG,
53 USER_VISIBLE_FLAG,
54 SITE_ENABLED_FLAG,
55 SITE_UPDATE_ALLOWED_FLAG,
56 APP_ENABLED_FLAG,
57 APP_UPDATE_ALLOWED_FLAG,
58 RESP_ENABLED_FLAG,
59 RESP_UPDATE_ALLOWED_FLAG,
60 USER_ENABLED_FLAG,
61 USER_UPDATE_ALLOWED_FLAG,
62 START_DATE_ACTIVE,
63 SQL_VALIDATION,
64 END_DATE_ACTIVE,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN,
70 HIERARCHY_TYPE,
71 SERVER_ENABLED_FLAG,
72 SERVER_UPDATE_ALLOWED_FLAG,
73 ORG_ENABLED_FLAG,
74 ORG_UPDATE_ALLOWED_FLAG,
75 SERVERRESP_ENABLED_FLAG,
76 SERVERRESP_UPDATE_ALLOWED_FLAG
77 ) values (
78 X_APPLICATION_ID,
79 X_PROFILE_OPTION_ID,
80 L_PROFILE_OPTION_NAME,
81 X_WRITE_ALLOWED_FLAG,
82 X_READ_ALLOWED_FLAG,
83 X_USER_CHANGEABLE_FLAG,
84 X_USER_VISIBLE_FLAG,
85 X_SITE_ENABLED_FLAG,
86 X_SITE_UPDATE_ALLOWED_FLAG,
87 X_APP_ENABLED_FLAG,
88 X_APP_UPDATE_ALLOWED_FLAG,
89 X_RESP_ENABLED_FLAG,
90 X_RESP_UPDATE_ALLOWED_FLAG,
91 X_USER_ENABLED_FLAG,
92 X_USER_UPDATE_ALLOWED_FLAG,
93 X_START_DATE_ACTIVE,
94 X_SQL_VALIDATION,
95 X_END_DATE_ACTIVE,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN,
101 X_HIERARCHY_TYPE,
102 X_SERVER_ENABLED_FLAG,
103 X_SERVER_UPDATE_ALLOWED_FLAG,
104 X_ORG_ENABLED_FLAG,
105 X_ORG_UPDATE_ALLOWED_FLAG,
106 X_SERVERRESP_ENABLED_FLAG,
107 X_SERVERRESP_UPD_ALLOW_FL
108 );
109
110 insert into FND_PROFILE_OPTIONS_TL (
111 PROFILE_OPTION_NAME,
112 USER_PROFILE_OPTION_NAME,
113 DESCRIPTION,
114 CREATED_BY,
115 CREATION_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_DATE,
118 LAST_UPDATE_LOGIN,
119 LANGUAGE,
120 SOURCE_LANG
121 ) select
122 L_PROFILE_OPTION_NAME,
123 X_USER_PROFILE_OPTION_NAME,
124 X_DESCRIPTION,
125 X_CREATED_BY,
126 X_CREATION_DATE,
127 X_LAST_UPDATED_BY,
128 X_LAST_UPDATE_DATE,
129 X_LAST_UPDATE_LOGIN,
130 L.LANGUAGE_CODE,
131 userenv('LANG')
132 from FND_LANGUAGES L
133 where L.INSTALLED_FLAG in ('I', 'B')
134 and not exists
135 (select NULL
136 from FND_PROFILE_OPTIONS_TL T
137 where T.PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
138 and T.LANGUAGE = L.LANGUAGE_CODE);
139
140 open c;
141 fetch c into X_ROWID;
142 if (c%notfound) then
143 close c;
144 raise no_data_found;
145 end if;
146 close c;
147
148 end INSERT_ROW;
149
150 -- ### OVERLOADED!
151 procedure INSERT_ROW (
152 X_ROWID in out NOCOPY VARCHAR2,
153 X_PROFILE_OPTION_NAME in VARCHAR2,
154 X_APPLICATION_ID in NUMBER,
155 X_PROFILE_OPTION_ID in NUMBER,
156 X_WRITE_ALLOWED_FLAG in VARCHAR2,
157 X_READ_ALLOWED_FLAG in VARCHAR2,
158 X_USER_CHANGEABLE_FLAG in VARCHAR2,
159 X_USER_VISIBLE_FLAG in VARCHAR2,
160 X_SITE_ENABLED_FLAG in VARCHAR2,
161 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
162 X_APP_ENABLED_FLAG in VARCHAR2,
163 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
164 X_RESP_ENABLED_FLAG in VARCHAR2,
165 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
166 X_USER_ENABLED_FLAG in VARCHAR2,
167 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
168 X_START_DATE_ACTIVE in DATE,
169 X_SQL_VALIDATION in VARCHAR2,
170 X_END_DATE_ACTIVE in DATE,
171 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
172 X_DESCRIPTION in VARCHAR2,
173 X_CREATION_DATE in DATE,
174 X_CREATED_BY in NUMBER,
175 X_LAST_UPDATE_DATE in DATE,
176 X_LAST_UPDATED_BY in NUMBER,
177 X_LAST_UPDATE_LOGIN in NUMBER,
178 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
179 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
180 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
181 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
182 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N'
183 ) is
184 begin
185 fnd_profile_options_pkg.INSERT_ROW(
186 X_ROWID => X_ROWID,
187 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME,
188 X_APPLICATION_ID => X_APPLICATION_ID,
189 X_PROFILE_OPTION_ID => X_PROFILE_OPTION_ID,
190 X_WRITE_ALLOWED_FLAG => X_WRITE_ALLOWED_FLAG,
191 X_READ_ALLOWED_FLAG => X_READ_ALLOWED_FLAG,
192 X_USER_CHANGEABLE_FLAG => X_USER_CHANGEABLE_FLAG,
193 X_USER_VISIBLE_FLAG => X_USER_VISIBLE_FLAG,
194 X_SITE_ENABLED_FLAG => X_SITE_ENABLED_FLAG,
195 X_SITE_UPDATE_ALLOWED_FLAG => X_SITE_UPDATE_ALLOWED_FLAG,
196 X_APP_ENABLED_FLAG => X_APP_ENABLED_FLAG,
197 X_APP_UPDATE_ALLOWED_FLAG => X_APP_UPDATE_ALLOWED_FLAG,
198 X_RESP_ENABLED_FLAG => X_RESP_ENABLED_FLAG,
199 X_RESP_UPDATE_ALLOWED_FLAG => X_RESP_UPDATE_ALLOWED_FLAG,
200 X_USER_ENABLED_FLAG => X_USER_ENABLED_FLAG,
201 X_USER_UPDATE_ALLOWED_FLAG => X_USER_UPDATE_ALLOWED_FLAG,
202 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
203 X_SQL_VALIDATION => X_SQL_VALIDATION,
204 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
205 X_USER_PROFILE_OPTION_NAME => X_USER_PROFILE_OPTION_NAME,
206 X_DESCRIPTION => X_DESCRIPTION,
207 X_CREATION_DATE => X_CREATION_DATE,
208 X_CREATED_BY => X_CREATED_BY,
209 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
210 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
211 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
212 X_HIERARCHY_TYPE => X_HIERARCHY_TYPE,
213 X_SERVER_ENABLED_FLAG => X_SERVER_ENABLED_FLAG,
214 X_SERVER_UPDATE_ALLOWED_FLAG => X_SERVER_UPDATE_ALLOWED_FLAG,
215 X_ORG_ENABLED_FLAG => X_ORG_ENABLED_FLAG,
216 X_ORG_UPDATE_ALLOWED_FLAG => X_ORG_UPDATE_ALLOWED_FLAG,
217 X_SERVERRESP_ENABLED_FLAG => 'N',
218 X_SERVERRESP_UPD_ALLOW_FL => 'N'
219 );
220 end INSERT_ROW;
221
222 -- ### OVERLOADED!
223 procedure LOCK_ROW (
224 X_PROFILE_OPTION_NAME in VARCHAR2,
225 X_APPLICATION_ID in NUMBER,
226 X_PROFILE_OPTION_ID in NUMBER,
227 X_WRITE_ALLOWED_FLAG in VARCHAR2,
228 X_READ_ALLOWED_FLAG in VARCHAR2,
229 X_USER_CHANGEABLE_FLAG in VARCHAR2,
230 X_USER_VISIBLE_FLAG in VARCHAR2,
231 X_SITE_ENABLED_FLAG in VARCHAR2,
232 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
233 X_APP_ENABLED_FLAG in VARCHAR2,
234 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
235 X_RESP_ENABLED_FLAG in VARCHAR2,
236 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
237 X_USER_ENABLED_FLAG in VARCHAR2,
238 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
239 X_START_DATE_ACTIVE in DATE,
240 X_SQL_VALIDATION in VARCHAR2,
241 X_END_DATE_ACTIVE in DATE,
242 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
243 X_DESCRIPTION in VARCHAR2,
244 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
245 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
246 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
247 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
248 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N'
249 ) is
250 begin
251 fnd_profile_options_pkg.LOCK_ROW(
252 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME,
253 X_APPLICATION_ID => X_APPLICATION_ID,
254 X_PROFILE_OPTION_ID => X_PROFILE_OPTION_ID,
255 X_WRITE_ALLOWED_FLAG => X_WRITE_ALLOWED_FLAG,
256 X_READ_ALLOWED_FLAG => X_READ_ALLOWED_FLAG,
257 X_USER_CHANGEABLE_FLAG => X_USER_CHANGEABLE_FLAG,
258 X_USER_VISIBLE_FLAG => X_USER_VISIBLE_FLAG,
259 X_SITE_ENABLED_FLAG => X_SITE_ENABLED_FLAG,
260 X_SITE_UPDATE_ALLOWED_FLAG => X_SITE_UPDATE_ALLOWED_FLAG ,
261 X_APP_ENABLED_FLAG => X_APP_ENABLED_FLAG,
262 X_APP_UPDATE_ALLOWED_FLAG => X_APP_UPDATE_ALLOWED_FLAG,
263 X_RESP_ENABLED_FLAG => X_RESP_ENABLED_FLAG,
264 X_RESP_UPDATE_ALLOWED_FLAG => X_RESP_UPDATE_ALLOWED_FLAG,
265 X_USER_ENABLED_FLAG => X_USER_ENABLED_FLAG,
266 X_USER_UPDATE_ALLOWED_FLAG => X_USER_UPDATE_ALLOWED_FLAG,
267 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
268 X_SQL_VALIDATION => X_SQL_VALIDATION,
269 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
270 X_USER_PROFILE_OPTION_NAME => X_USER_PROFILE_OPTION_NAME,
271 X_DESCRIPTION => X_DESCRIPTION,
272 X_HIERARCHY_TYPE => X_HIERARCHY_TYPE,
273 X_SERVER_ENABLED_FLAG => X_SERVER_ENABLED_FLAG,
274 X_SERVER_UPDATE_ALLOWED_FLAG => X_SERVER_UPDATE_ALLOWED_FLAG,
275 X_ORG_ENABLED_FLAG => X_ORG_ENABLED_FLAG,
276 X_ORG_UPDATE_ALLOWED_FLAG => X_ORG_UPDATE_ALLOWED_FLAG,
277 X_SERVERRESP_ENABLED_FLAG => 'N',
278 X_SERVERRESP_UPD_ALLOW_FL => 'N'
279 );
280 end LOCK_ROW;
281
282 -- ### OVERLOADED!
283 procedure LOCK_ROW (
284 X_PROFILE_OPTION_NAME in VARCHAR2,
285 X_APPLICATION_ID in NUMBER,
286 X_PROFILE_OPTION_ID in NUMBER,
287 X_WRITE_ALLOWED_FLAG in VARCHAR2,
288 X_READ_ALLOWED_FLAG in VARCHAR2,
289 X_USER_CHANGEABLE_FLAG in VARCHAR2,
290 X_USER_VISIBLE_FLAG in VARCHAR2,
291 X_SITE_ENABLED_FLAG in VARCHAR2,
292 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
293 X_APP_ENABLED_FLAG in VARCHAR2,
294 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
295 X_RESP_ENABLED_FLAG in VARCHAR2,
296 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
297 X_USER_ENABLED_FLAG in VARCHAR2,
298 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
299 X_START_DATE_ACTIVE in DATE,
300 X_SQL_VALIDATION in VARCHAR2,
301 X_END_DATE_ACTIVE in DATE,
302 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
303 X_DESCRIPTION in VARCHAR2,
304 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
305 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
306 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
307 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
308 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
309 X_SERVERRESP_ENABLED_FLAG in VARCHAR2,
310 X_SERVERRESP_UPD_ALLOW_FL in VARCHAR2
311 ) is
312
313 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(X_PROFILE_OPTION_NAME);
314 cursor c is select
315 APPLICATION_ID,
316 PROFILE_OPTION_ID,
317 WRITE_ALLOWED_FLAG,
318 READ_ALLOWED_FLAG,
319 USER_CHANGEABLE_FLAG,
320 USER_VISIBLE_FLAG,
321 SITE_ENABLED_FLAG,
322 SITE_UPDATE_ALLOWED_FLAG,
323 APP_ENABLED_FLAG,
324 APP_UPDATE_ALLOWED_FLAG,
325 RESP_ENABLED_FLAG,
326 RESP_UPDATE_ALLOWED_FLAG,
327 USER_ENABLED_FLAG,
328 USER_UPDATE_ALLOWED_FLAG,
329 START_DATE_ACTIVE,
330 SQL_VALIDATION,
331 END_DATE_ACTIVE,
332 HIERARCHY_TYPE,
333 SERVER_ENABLED_FLAG,
334 SERVER_UPDATE_ALLOWED_FLAG,
335 ORG_ENABLED_FLAG,
336 ORG_UPDATE_ALLOWED_FLAG,
337 SERVERRESP_ENABLED_FLAG,
338 SERVERRESP_UPDATE_ALLOWED_FLAG
339 from FND_PROFILE_OPTIONS
340 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
341 for update of PROFILE_OPTION_NAME nowait;
342 recinfo c%rowtype;
343
344 cursor c1 is select
345 USER_PROFILE_OPTION_NAME,
346 DESCRIPTION
347 from FND_PROFILE_OPTIONS_TL
348 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
349 and LANGUAGE = userenv('LANG')
350 for update of PROFILE_OPTION_NAME nowait;
351 tlinfo c1%rowtype;
352
353 begin
354 open c;
355 fetch c into recinfo;
356 if (c%notfound) then
357 close c;
358 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
359 app_exception.raise_exception;
360 end if;
361 close c;
362 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
363 AND (recinfo.PROFILE_OPTION_ID = X_PROFILE_OPTION_ID)
364 AND (recinfo.WRITE_ALLOWED_FLAG = X_WRITE_ALLOWED_FLAG)
365 AND (recinfo.READ_ALLOWED_FLAG = X_READ_ALLOWED_FLAG)
366 AND (recinfo.USER_CHANGEABLE_FLAG = X_USER_CHANGEABLE_FLAG)
367 AND (recinfo.USER_VISIBLE_FLAG = X_USER_VISIBLE_FLAG)
368 AND (recinfo.SITE_ENABLED_FLAG = X_SITE_ENABLED_FLAG)
369 AND (recinfo.SITE_UPDATE_ALLOWED_FLAG = X_SITE_UPDATE_ALLOWED_FLAG)
370 AND (recinfo.APP_ENABLED_FLAG = X_APP_ENABLED_FLAG)
371 AND (recinfo.APP_UPDATE_ALLOWED_FLAG = X_APP_UPDATE_ALLOWED_FLAG)
372 AND (recinfo.RESP_ENABLED_FLAG = X_RESP_ENABLED_FLAG)
373 AND (recinfo.RESP_UPDATE_ALLOWED_FLAG = X_RESP_UPDATE_ALLOWED_FLAG)
374 AND (recinfo.USER_ENABLED_FLAG = X_USER_ENABLED_FLAG)
375 AND (recinfo.USER_UPDATE_ALLOWED_FLAG = X_USER_UPDATE_ALLOWED_FLAG)
376 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
377 AND ((recinfo.SQL_VALIDATION = X_SQL_VALIDATION)
378 OR ((recinfo.SQL_VALIDATION is null) AND (X_SQL_VALIDATION is null)))
379 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
383 AND (recinfo.SERVER_UPDATE_ALLOWED_FLAG = X_SERVER_UPDATE_ALLOWED_FLAG)
380 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
381 AND (recinfo.HIERARCHY_TYPE = X_HIERARCHY_TYPE)
382 AND (recinfo.SERVER_ENABLED_FLAG = X_SERVER_ENABLED_FLAG)
384 AND (recinfo.ORG_ENABLED_FLAG = X_ORG_ENABLED_FLAG)
385 AND (recinfo.ORG_UPDATE_ALLOWED_FLAG = X_ORG_UPDATE_ALLOWED_FLAG)
386 AND (recinfo.SERVERRESP_ENABLED_FLAG = X_SERVERRESP_ENABLED_FLAG)
387 AND (recinfo.SERVERRESP_UPDATE_ALLOWED_FLAG = X_SERVERRESP_UPD_ALLOW_FL)
388 ) then
389 null;
390 else
391 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
392 app_exception.raise_exception;
393 end if;
394
395 open c1;
396 fetch c1 into tlinfo;
397 if (c1%notfound) then
398 close c1;
399 return;
400 end if;
401 close c1;
402
403 if ( (tlinfo.USER_PROFILE_OPTION_NAME = X_USER_PROFILE_OPTION_NAME)
404 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
405 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
406 ) then
407 null;
408 else
409 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
410 app_exception.raise_exception;
411 end if;
412
413 return;
414
415 end LOCK_ROW;
416
417 --Forward declaration
418 PROCEDURE UPDATE_APPL_ID_PK_COLUMNS( x_profile_option_name varchar2,
419 x_profile_id varchar2,
420 x_appl_id varchar2);
421
422 -- Bug 5060938.
423 -- This api is used to propagate updation of profile from Application1 to
424 -- Application2 to Fnd_Profile_cat_Options table.
425 -- Drop this api after finding out a way to create Foreign_Key and
426 -- Primary_Key info in FND_FOREIGN_KEYS and FND_PRIMARY_KEYS AOL meta data
427 -- through fnd_profile_cat_options.xdf.
428 PROCEDURE UPDATE_CAT_OPTIONS_APPL_ID( x_profile_option_name varchar2,
429 x_profile_id varchar2,
430 x_appl_id number);
431
432 -- ### OVERLOADED!
433 procedure UPDATE_ROW (
434 X_PROFILE_OPTION_NAME in VARCHAR2,
435 X_APPLICATION_ID in NUMBER,
436 X_PROFILE_OPTION_ID in NUMBER,
437 X_WRITE_ALLOWED_FLAG in VARCHAR2,
438 X_READ_ALLOWED_FLAG in VARCHAR2,
439 X_USER_CHANGEABLE_FLAG in VARCHAR2,
440 X_USER_VISIBLE_FLAG in VARCHAR2,
441 X_SITE_ENABLED_FLAG in VARCHAR2,
442 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
443 X_APP_ENABLED_FLAG in VARCHAR2,
444 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
445 X_RESP_ENABLED_FLAG in VARCHAR2,
446 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
447 X_USER_ENABLED_FLAG in VARCHAR2,
448 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
449 X_START_DATE_ACTIVE in DATE,
450 X_SQL_VALIDATION in VARCHAR2,
451 X_END_DATE_ACTIVE in DATE,
452 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
453 X_DESCRIPTION in VARCHAR2,
454 X_LAST_UPDATE_DATE in DATE,
455 X_LAST_UPDATED_BY in NUMBER,
456 X_LAST_UPDATE_LOGIN in NUMBER,
457 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
458 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
459 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
460 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
461 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
462 X_SERVERRESP_ENABLED_FLAG in VARCHAR2,
463 X_SERVERRESP_UPD_ALLOW_FL in VARCHAR2,
464 X_HIERARCHY_SWITCH_MODE in FND_PROFILE_HIERARCHY_PKG.SWITCH_MODE
465 ) is
466 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(X_PROFILE_OPTION_NAME);
467 begin
468
469 update_appl_id_pk_columns(L_PROFILE_OPTION_NAME,x_profile_option_id,x_application_id);
470
471 /** Remove this call after finding a way to create Foreign_Key info in AOL
472 ** Meta data (Fnd_Foreign_Key and Fnd_Foreign_key_Columns table) using
473 ** Fnd_Profile_Cat_Options.xdf. Right now though foreign key info is
474 ** available in fnd_profile_cat_options.xdf, data is not uploaded into
475 ** FND_FOREIGN_KEYS, FND_PRIMARY_KEYS (and it's COLUMN's table) when the
476 ** above xdf is uploaded.
477 **/
478 UPDATE_CAT_OPTIONS_APPL_ID(L_PROFILE_OPTION_NAME,x_profile_option_id,x_application_id);
479
480 FND_PROFILE_HIERARCHY_PKG.carry_profile_values(
481 X_PROFILE_OPTION_NAME => L_PROFILE_OPTION_NAME,
482 X_APPLICATION_ID => X_APPLICATION_ID,
483 X_PROFILE_OPTION_ID => X_PROFILE_OPTION_ID,
484 X_TO_HIERARCHY_TYPE => X_HIERARCHY_TYPE,
485 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
486 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
487 X_CREATION_DATE => X_LAST_UPDATE_DATE,
488 X_CREATED_BY => X_LAST_UPDATED_BY,
489 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
490 X_MODE => X_HIERARCHY_SWITCH_MODE
491 );
492
493 update FND_PROFILE_OPTIONS set
494 APPLICATION_ID = X_APPLICATION_ID,
495 PROFILE_OPTION_ID = X_PROFILE_OPTION_ID,
496 WRITE_ALLOWED_FLAG = X_WRITE_ALLOWED_FLAG,
497 READ_ALLOWED_FLAG = X_READ_ALLOWED_FLAG,
498 USER_CHANGEABLE_FLAG = X_USER_CHANGEABLE_FLAG,
499 USER_VISIBLE_FLAG = X_USER_VISIBLE_FLAG,
503 APP_UPDATE_ALLOWED_FLAG = X_APP_UPDATE_ALLOWED_FLAG,
500 SITE_ENABLED_FLAG = X_SITE_ENABLED_FLAG,
501 SITE_UPDATE_ALLOWED_FLAG = X_SITE_UPDATE_ALLOWED_FLAG,
502 APP_ENABLED_FLAG = X_APP_ENABLED_FLAG,
504 RESP_ENABLED_FLAG = X_RESP_ENABLED_FLAG,
505 RESP_UPDATE_ALLOWED_FLAG = X_RESP_UPDATE_ALLOWED_FLAG,
506 USER_ENABLED_FLAG = X_USER_ENABLED_FLAG,
507 USER_UPDATE_ALLOWED_FLAG = X_USER_UPDATE_ALLOWED_FLAG,
508 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
509 SQL_VALIDATION = X_SQL_VALIDATION,
510 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
511 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
512 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
513 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
514 HIERARCHY_TYPE = X_HIERARCHY_TYPE,
515 SERVER_ENABLED_FLAG = X_SERVER_ENABLED_FLAG,
516 SERVER_UPDATE_ALLOWED_FLAG = X_SERVER_UPDATE_ALLOWED_FLAG,
517 ORG_ENABLED_FLAG = X_ORG_ENABLED_FLAG,
518 ORG_UPDATE_ALLOWED_FLAG = X_ORG_UPDATE_ALLOWED_FLAG,
519 SERVERRESP_ENABLED_FLAG = X_SERVERRESP_ENABLED_FLAG,
520 SERVERRESP_UPDATE_ALLOWED_FLAG = X_SERVERRESP_UPD_ALLOW_FL
521 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME;
522
523 if (sql%notfound) then
524 raise no_data_found;
525 end if;
526
527 update FND_PROFILE_OPTIONS_TL set
528 USER_PROFILE_OPTION_NAME = X_USER_PROFILE_OPTION_NAME,
529 DESCRIPTION = X_DESCRIPTION,
530 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
531 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
532 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
533 SOURCE_LANG = userenv('LANG')
534 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
535 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
536
537 if (sql%notfound) then
538 raise no_data_found;
539 end if;
540
541 end UPDATE_ROW;
542
543 -- ### OVERLOADED!
544 procedure UPDATE_ROW (
545 X_PROFILE_OPTION_NAME in VARCHAR2,
546 X_APPLICATION_ID in NUMBER,
547 X_PROFILE_OPTION_ID in NUMBER,
548 X_WRITE_ALLOWED_FLAG in VARCHAR2,
549 X_READ_ALLOWED_FLAG in VARCHAR2,
550 X_USER_CHANGEABLE_FLAG in VARCHAR2,
551 X_USER_VISIBLE_FLAG in VARCHAR2,
552 X_SITE_ENABLED_FLAG in VARCHAR2,
553 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
554 X_APP_ENABLED_FLAG in VARCHAR2,
555 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
556 X_RESP_ENABLED_FLAG in VARCHAR2,
557 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
558 X_USER_ENABLED_FLAG in VARCHAR2,
559 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
560 X_START_DATE_ACTIVE in DATE,
561 X_SQL_VALIDATION in VARCHAR2,
562 X_END_DATE_ACTIVE in DATE,
563 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
564 X_DESCRIPTION in VARCHAR2,
565 X_LAST_UPDATE_DATE in DATE,
566 X_LAST_UPDATED_BY in NUMBER,
567 X_LAST_UPDATE_LOGIN in NUMBER,
568 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
569 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
570 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
571 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
572 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N'
573
574 ) is
575
576 begin
577
578 fnd_profile_options_pkg.update_row(
579 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME,
580 X_APPLICATION_ID => X_APPLICATION_ID,
581 X_PROFILE_OPTION_ID => X_PROFILE_OPTION_ID,
582 X_WRITE_ALLOWED_FLAG => X_WRITE_ALLOWED_FLAG,
583 X_READ_ALLOWED_FLAG => X_READ_ALLOWED_FLAG,
584 X_USER_CHANGEABLE_FLAG => X_USER_CHANGEABLE_FLAG,
585 X_USER_VISIBLE_FLAG => X_USER_VISIBLE_FLAG,
586 X_SITE_ENABLED_FLAG => X_SITE_ENABLED_FLAG,
587 X_SITE_UPDATE_ALLOWED_FLAG => X_SITE_UPDATE_ALLOWED_FLAG,
588 X_APP_ENABLED_FLAG => X_APP_ENABLED_FLAG,
589 X_APP_UPDATE_ALLOWED_FLAG => X_APP_UPDATE_ALLOWED_FLAG,
590 X_RESP_ENABLED_FLAG => X_RESP_ENABLED_FLAG,
591 X_RESP_UPDATE_ALLOWED_FLAG => X_RESP_UPDATE_ALLOWED_FLAG,
592 X_USER_ENABLED_FLAG => X_USER_ENABLED_FLAG,
593 X_USER_UPDATE_ALLOWED_FLAG => X_USER_UPDATE_ALLOWED_FLAG,
594 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
595 X_SQL_VALIDATION => X_SQL_VALIDATION,
596 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
597 X_USER_PROFILE_OPTION_NAME => X_USER_PROFILE_OPTION_NAME,
598 X_DESCRIPTION => X_DESCRIPTION,
599 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
600 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
601 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
602 X_HIERARCHY_TYPE => X_HIERARCHY_TYPE,
603 X_SERVER_ENABLED_FLAG => X_SERVER_ENABLED_FLAG,
604 X_SERVER_UPDATE_ALLOWED_FLAG => X_SERVER_UPDATE_ALLOWED_FLAG,
605 X_ORG_ENABLED_FLAG => X_ORG_ENABLED_FLAG,
606 X_ORG_UPDATE_ALLOWED_FLAG => X_ORG_UPDATE_ALLOWED_FLAG,
607 X_SERVERRESP_ENABLED_FLAG => 'N',
608 X_SERVERRESP_UPD_ALLOW_FL => 'N');
609
610
611 end UPDATE_ROW;
612
613 -- ### OVERLOADED!
614 procedure UPDATE_ROW (
615 X_PROFILE_OPTION_NAME in VARCHAR2,
616 X_APPLICATION_ID in NUMBER,
617 X_PROFILE_OPTION_ID in NUMBER,
618 X_WRITE_ALLOWED_FLAG in VARCHAR2,
619 X_READ_ALLOWED_FLAG in VARCHAR2,
620 X_USER_CHANGEABLE_FLAG in VARCHAR2,
621 X_USER_VISIBLE_FLAG in VARCHAR2,
622 X_SITE_ENABLED_FLAG in VARCHAR2,
626 X_RESP_ENABLED_FLAG in VARCHAR2,
623 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2,
624 X_APP_ENABLED_FLAG in VARCHAR2,
625 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2,
627 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2,
628 X_USER_ENABLED_FLAG in VARCHAR2,
629 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2,
630 X_START_DATE_ACTIVE in DATE,
631 X_SQL_VALIDATION in VARCHAR2,
632 X_END_DATE_ACTIVE in DATE,
633 X_USER_PROFILE_OPTION_NAME in VARCHAR2,
634 X_DESCRIPTION in VARCHAR2,
635 X_LAST_UPDATE_DATE in DATE,
636 X_LAST_UPDATED_BY in NUMBER,
637 X_LAST_UPDATE_LOGIN in NUMBER,
638 X_HIERARCHY_TYPE in VARCHAR2 default 'SECURITY',
639 X_SERVER_ENABLED_FLAG in VARCHAR2 default 'N',
640 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
641 X_ORG_ENABLED_FLAG in VARCHAR2 default 'N',
642 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default 'N',
643 X_SERVERRESP_ENABLED_FLAG in VARCHAR2,
644 X_SERVERRESP_UPD_ALLOW_FL in VARCHAR2
645 ) is
646 begin
647 fnd_profile_options_pkg.update_row(
648 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME ,
649 X_APPLICATION_ID => X_APPLICATION_ID ,
650 X_PROFILE_OPTION_ID => X_PROFILE_OPTION_ID ,
651 X_WRITE_ALLOWED_FLAG => X_WRITE_ALLOWED_FLAG ,
652 X_READ_ALLOWED_FLAG => X_READ_ALLOWED_FLAG ,
653 X_USER_CHANGEABLE_FLAG => X_USER_CHANGEABLE_FLAG ,
654 X_USER_VISIBLE_FLAG => X_USER_VISIBLE_FLAG ,
655 X_SITE_ENABLED_FLAG => X_SITE_ENABLED_FLAG ,
656 X_SITE_UPDATE_ALLOWED_FLAG => X_SITE_UPDATE_ALLOWED_FLAG ,
657 X_APP_ENABLED_FLAG => X_APP_ENABLED_FLAG ,
658 X_APP_UPDATE_ALLOWED_FLAG => X_APP_UPDATE_ALLOWED_FLAG ,
659 X_RESP_ENABLED_FLAG => X_RESP_ENABLED_FLAG ,
660 X_RESP_UPDATE_ALLOWED_FLAG => X_RESP_UPDATE_ALLOWED_FLAG ,
661 X_USER_ENABLED_FLAG => X_USER_ENABLED_FLAG ,
662 X_USER_UPDATE_ALLOWED_FLAG => X_USER_UPDATE_ALLOWED_FLAG ,
663 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE ,
664 X_SQL_VALIDATION => X_SQL_VALIDATION ,
665 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE ,
666 X_USER_PROFILE_OPTION_NAME => X_USER_PROFILE_OPTION_NAME ,
667 X_DESCRIPTION => X_DESCRIPTION ,
668 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
669 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY ,
670 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN ,
671 X_HIERARCHY_TYPE => X_HIERARCHY_TYPE ,
672 X_SERVER_ENABLED_FLAG => X_SERVER_ENABLED_FLAG ,
673 X_SERVER_UPDATE_ALLOWED_FLAG=> X_SERVER_UPDATE_ALLOWED_FLAG,
674 X_ORG_ENABLED_FLAG => X_ORG_ENABLED_FLAG ,
675 X_ORG_UPDATE_ALLOWED_FLAG => X_ORG_UPDATE_ALLOWED_FLAG ,
676 X_SERVERRESP_ENABLED_FLAG => X_SERVERRESP_ENABLED_FLAG ,
677 X_SERVERRESP_UPD_ALLOW_FL => X_SERVERRESP_UPD_ALLOW_FL ,
678 X_HIERARCHY_SWITCH_MODE => FND_PROFILE_HIERARCHY_PKG.INSERT_UPDATE );
679
680 end UPDATE_ROW;
681
682 -- ### OVERLOADED!
683
684 procedure UPDATE_ROW (
685 X_PROFILE_OPTION_NAME in VARCHAR2,
686 X_HIERARCHY_TYPE in VARCHAR2,
687 X_SITE_ENABLED_FLAG in VARCHAR2 default NULL,
688 X_SITE_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
689 X_APP_ENABLED_FLAG in VARCHAR2 default NULL,
690 X_APP_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
691 X_RESP_ENABLED_FLAG in VARCHAR2 default NULL,
692 X_RESP_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
693 X_USER_ENABLED_FLAG in VARCHAR2 default NULL,
694 X_USER_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
695 X_SERVER_ENABLED_FLAG in VARCHAR2 default NULL,
696 X_SERVER_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
697 X_ORG_ENABLED_FLAG in VARCHAR2 default NULL,
698 X_ORG_UPDATE_ALLOWED_FLAG in VARCHAR2 default NULL,
699 X_SERVERRESP_ENABLED_FLAG in VARCHAR2 default NULL,
700 X_SERVERRESP_UPD_ALLOW_FL in VARCHAR2 default NULL)
701 is
702 prof_name VARCHAR2(80) := upper(X_PROFILE_OPTION_NAME);
703 h_type VARCHAR2(8) := upper(X_HIERARCHY_TYPE);
704 s_v_flag1 VARCHAR2(1) := upper(X_SITE_ENABLED_FLAG);
705 s_u_flag1 VARCHAR2(1) := upper(X_SITE_UPDATE_ALLOWED_FLAG);
706 a_v_flag1 VARCHAR2(1) := upper(X_APP_ENABLED_FLAG);
707 a_u_flag1 VARCHAR2(1) := upper(X_APP_UPDATE_ALLOWED_FLAG);
708 r_v_flag1 VARCHAR2(1) := upper(X_RESP_ENABLED_FLAG);
709 r_u_flag1 VARCHAR2(1) := upper(X_RESP_UPDATE_ALLOWED_FLAG);
710 u_v_flag1 VARCHAR2(1) := upper(X_USER_ENABLED_FLAG);
711 u_u_flag1 VARCHAR2(1) := upper(X_USER_UPDATE_ALLOWED_FLAG);
712 o_v_flag1 VARCHAR2(1) := upper(X_ORG_ENABLED_FLAG);
713 o_u_flag1 VARCHAR2(1) := upper(X_ORG_UPDATE_ALLOWED_FLAG);
714 sv_v_flag1 VARCHAR2(1) := upper(X_SERVER_ENABLED_FLAG);
715 sv_u_flag1 VARCHAR2(1) := upper(X_SERVER_UPDATE_ALLOWED_FLAG);
716 sr_v_flag1 VARCHAR2(1) := upper(X_SERVERRESP_ENABLED_FLAG);
717 sr_u_flag1 VARCHAR2(1) := upper(X_SERVERRESP_UPD_ALLOW_FL);
718 prof_doesnotexist_exception EXCEPTION;
719 invalid_input_exception EXCEPTION;
720
721 cursor profname_cursor is
722 select application_id,
723 profile_option_id,
727 USER_VISIBLE_FLAG,
724 WRITE_ALLOWED_FLAG,
725 READ_ALLOWED_FLAG,
726 USER_CHANGEABLE_FLAG,
728 START_DATE_ACTIVE,
729 SQL_VALIDATION,
730 END_DATE_ACTIVE,
731 HIERARCHY_TYPE,
732 SITE_ENABLED_FLAG,
733 SITE_UPDATE_ALLOWED_FLAG,
734 APP_ENABLED_FLAG,
735 APP_UPDATE_ALLOWED_FLAG,
736 RESP_ENABLED_FLAG,
737 RESP_UPDATE_ALLOWED_FLAG,
738 USER_ENABLED_FLAG,
739 USER_UPDATE_ALLOWED_FLAG,
740 SERVER_ENABLED_FLAG,
741 ORG_ENABLED_FLAG,
742 SERVER_UPDATE_ALLOWED_FLAG,
743 ORG_UPDATE_ALLOWED_FLAG,
744 SERVERRESP_ENABLED_FLAG,
745 SERVERRESP_UPDATE_ALLOWED_FLAG,
746 USER_PROFILE_OPTION_NAME,
747 DESCRIPTION
748 from FND_PROFILE_OPTIONS_VL
749 where PROFILE_OPTION_NAME=prof_name;
750
751 profname_val profname_cursor%ROWTYPE;
752 begin
753
754 -- first check arguments are well formed
755 if (prof_name is NULL or h_type is NULL) then
756 raise invalid_input_exception;
757 end if;
758
759 if (h_type <> 'SECURITY' and h_type <> 'SERVER'
760 and h_type <> 'ORG' and h_type <> 'SERVRESP') then
761 raise invalid_input_exception;
762 end if;
763
764 if ((s_v_flag1 is not NULL and s_v_flag1 <> 'Y' and s_v_flag1 <> 'N') or
765 (s_u_flag1 is not NULL and s_u_flag1 <> 'Y' and s_u_flag1 <> 'N') or
766 (a_v_flag1 is not NULL and a_v_flag1 <> 'Y' and a_v_flag1 <> 'N') or
767 (a_u_flag1 is not NULL and a_u_flag1 <> 'Y' and a_u_flag1 <> 'N') or
768 (r_v_flag1 is not NULL and r_v_flag1 <> 'Y' and r_v_flag1 <> 'N') or
769 (r_u_flag1 is not NULL and r_u_flag1 <> 'Y' and r_u_flag1 <> 'N') or
770 (sr_v_flag1 is not NULL and sr_v_flag1 <> 'Y' and sr_v_flag1 <> 'N') or
771 (sr_u_flag1 is not NULL and sr_u_flag1 <> 'Y' and sr_u_flag1 <> 'N') or
772 (o_v_flag1 is not NULL and o_v_flag1 <> 'Y' and o_v_flag1 <> 'N') or
773 (o_u_flag1 is not NULL and o_u_flag1 <> 'Y' and o_u_flag1 <> 'N') or
774 (sv_v_flag1 is not NULL and sv_v_flag1 <> 'Y' and sv_v_flag1 <> 'N') or
775 (sv_u_flag1 is not NULL and sv_u_flag1 <> 'Y' and sv_u_flag1 <> 'N')) then
776 raise invalid_input_exception;
777 end if;
778 -- done checking arguments are well formed
779
780 -- make sure profile exists
781 open profname_cursor;
782 fetch profname_cursor into profname_val;
783 if (profname_cursor%NOTFOUND) then
784 raise prof_doesnotexist_exception;
785 end if;
786
787 -- figure out the values for the enabled and updatable flags. use
788 -- existing value if NULL
789 if (s_v_flag1 is NULL) then
790 s_v_flag1 := profname_val.SITE_ENABLED_FLAG;
791 end if;
792 if (s_u_flag1 is NULL) then
793 s_u_flag1 := profname_val.SITE_UPDATE_ALLOWED_FLAG;
794 end if;
795 if (u_v_flag1 is NULL) then
796 u_v_flag1 := profname_val.USER_ENABLED_FLAG;
797 end if;
798 if (u_u_flag1 is NULL) then
799 u_u_flag1 := profname_val.USER_UPDATE_ALLOWED_FLAG;
800 end if;
801 if (h_type = 'SECURITY') then
802 if (a_v_flag1 is NULL) then
803 a_v_flag1 := profname_val.APP_ENABLED_FLAG;
804 end if;
805 if (a_u_flag1 is NULL) then
806 a_u_flag1 := profname_val.APP_UPDATE_ALLOWED_FLAG;
807 end if;
808 if (r_v_flag1 is NULL) then
809 r_v_flag1 := profname_val.RESP_ENABLED_FLAG;
810 end if;
811 if (r_u_flag1 is NULL) then
812 r_u_flag1 := profname_val.RESP_UPDATE_ALLOWED_FLAG;
813 end if;
814 o_v_flag1 := 'N';
815 o_u_flag1 := 'N';
816 sv_v_flag1 := 'N';
817 sv_u_flag1 := 'N';
818 sr_v_flag1 := 'N';
819 sr_u_flag1 := 'N';
820 elsif (h_type = 'ORG') then
821 a_v_flag1 := 'N';
822 a_u_flag1 := 'N';
823 r_v_flag1 := 'N';
824 r_u_flag1 := 'N';
825 if (o_v_flag1 is NULL) then
826 o_v_flag1 := profname_val.ORG_ENABLED_FLAG;
827 end if;
828 if (o_u_flag1 is NULL) then
829 o_u_flag1 := profname_val.ORG_UPDATE_ALLOWED_FLAG;
830 end if;
831 sv_v_flag1 := 'N';
832 sv_u_flag1 := 'N';
833 sr_v_flag1 := 'N';
834 sr_u_flag1 := 'N';
835 elsif (h_type = 'SERVER') then
836 a_v_flag1 := 'N';
837 a_u_flag1 := 'N';
838 r_v_flag1 := 'N';
839 r_u_flag1 := 'N';
840 o_v_flag1 := 'N';
841 o_u_flag1 := 'N';
842 if (sv_v_flag1 is NULL) then
843 sv_v_flag1 := profname_val.SERVER_ENABLED_FLAG;
844 end if;
845 if (sv_u_flag1 is NULL) then
846 sv_u_flag1 := profname_val.SERVER_UPDATE_ALLOWED_FLAG;
847 end if;
848 sr_v_flag1 := 'N';
849 sr_u_flag1 := 'N';
850 elsif (h_type = 'SERVRESP') then
851 a_v_flag1 := 'N';
852 a_u_flag1 := 'N';
853 r_v_flag1 := 'N';
854 r_u_flag1 := 'N';
855 o_v_flag1 := 'N';
856 o_u_flag1 := 'N';
857 sv_v_flag1 := 'N';
858 sv_u_flag1 := 'N';
859 if (sr_v_flag1 is NULL) then
860 sr_v_flag1 := profname_val.SERVERRESP_ENABLED_FLAG;
861 end if;
862 if (sr_u_flag1 is NULL) then
863 sr_u_flag1 := profname_val.SERVERRESP_UPDATE_ALLOWED_FLAG;
864 end if;
865 end if;
866
870 X_APPLICATION_ID => profname_val.application_id,
867 -- invoke table handler to update the profile
868 FND_PROFILE_OPTIONS_PKG.UPDATE_ROW (
869 X_PROFILE_OPTION_NAME => prof_name,
871 X_PROFILE_OPTION_ID => profname_val.profile_option_id,
872 X_WRITE_ALLOWED_FLAG => profname_val.WRITE_ALLOWED_FLAG,
873 X_READ_ALLOWED_FLAG => profname_val.READ_ALLOWED_FLAG,
874 X_USER_CHANGEABLE_FLAG => profname_val.USER_CHANGEABLE_FLAG,
875 X_USER_VISIBLE_FLAG => profname_val.USER_VISIBLE_FLAG,
876 X_SITE_ENABLED_FLAG => s_v_flag1,
877 X_SITE_UPDATE_ALLOWED_FLAG => s_u_flag1,
878 X_APP_ENABLED_FLAG => a_v_flag1,
879 X_APP_UPDATE_ALLOWED_FLAG => a_u_flag1,
880 X_RESP_ENABLED_FLAG => r_v_flag1,
881 X_RESP_UPDATE_ALLOWED_FLAG => r_u_flag1,
882 X_USER_ENABLED_FLAG => u_v_flag1,
883 X_USER_UPDATE_ALLOWED_FLAG => u_u_flag1,
884 X_START_DATE_ACTIVE => profname_val.START_DATE_ACTIVE,
885 X_SQL_VALIDATION => profname_val.SQL_VALIDATION,
886 X_END_DATE_ACTIVE => profname_val.END_DATE_ACTIVE,
887 X_USER_PROFILE_OPTION_NAME => profname_val.USER_PROFILE_OPTION_NAME,
888 X_DESCRIPTION => profname_val.DESCRIPTION,
889 X_LAST_UPDATE_DATE => sysdate,
890 X_LAST_UPDATED_BY => -1,
891 X_LAST_UPDATE_LOGIN => -1,
892 X_HIERARCHY_TYPE => h_type,
893 X_SERVER_ENABLED_FLAG => sv_v_flag1,
894 X_SERVER_UPDATE_ALLOWED_FLAG => sv_u_flag1,
895 X_ORG_ENABLED_FLAG => o_v_flag1,
896 X_ORG_UPDATE_ALLOWED_FLAG => o_u_flag1,
897 X_SERVERRESP_ENABLED_FLAG => sr_v_flag1,
898 X_SERVERRESP_UPD_ALLOW_FL => sr_u_flag1);
899
900 end UPDATE_ROW;
901
902 -- ### OVERLOADED!
903 procedure TRANSLATE_ROW (
904 x_profile_name in varchar2,
905 x_owner in varchar2,
906 x_user_profile_option_name in varchar2,
907 x_description in varchar2) is
908 begin
909 fnd_profile_options_pkg.translate_row(
910 x_profile_name => x_profile_name,
911 x_owner => x_owner,
912 x_user_profile_option_name => x_user_profile_option_name,
913 x_description => x_description,
914 x_custom_mode => null,
915 x_last_update_date => null);
916 end TRANSLATE_ROW;
917
918 -- ### OVERLOADED!
919 procedure TRANSLATE_ROW (
920 x_profile_name in varchar2,
921 x_owner in varchar2,
922 x_user_profile_option_name in varchar2,
923 x_description in varchar2,
924 x_custom_mode in varchar2,
925 x_last_update_date in varchar2)
926 is
927 f_luby number; -- entity owner in file
928 f_ludate date; -- entity update date in file
929 db_luby number; -- entity owner in db
930 db_ludate date; -- entity update date in db
931 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(x_profile_name);
932
933 begin
934
935 -- Translate owner to file_last_updated_by
936 f_luby := fnd_load_util.owner_id(x_owner);
937
938 -- Translate char last_update_date to date
939 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
940
941 begin
942 select LAST_UPDATED_BY, LAST_UPDATE_DATE
943 into db_luby, db_ludate
944 from FND_PROFILE_OPTIONS_TL
945 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
946 and LANGUAGE = userenv('LANG');
947
948 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
949 db_ludate, X_CUSTOM_MODE)) then
950 update FND_PROFILE_OPTIONS_TL set
951 USER_PROFILE_OPTION_NAME = nvl(x_user_profile_option_name,
952 USER_PROFILE_OPTION_NAME),
953 DESCRIPTION = nvl(x_description, DESCRIPTION),
954 SOURCE_LANG = userenv('LANG'),
955 LAST_UPDATE_DATE = f_ludate,
956 LAST_UPDATED_BY = f_luby,
957 LAST_UPDATE_LOGIN = 0
958 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME
959 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
960 end if;
961 exception
962 when no_data_found then
963 null;
964 end;
965 end TRANSLATE_ROW;
966
967 -- ### Overloaded!
968 procedure LOAD_ROW (
969 x_profile_name in varchar2,
970 x_owner in varchar2,
971 x_application_short_name in varchar2,
972 x_user_profile_option_name in varchar2,
973 x_description in varchar2,
974 x_user_changeable_flag in varchar2,
975 x_user_visible_flag in varchar2,
976 x_read_allowed_flag in varchar2,
977 x_write_allowed_flag in varchar2,
978 x_site_enabled_flag in varchar2,
979 x_site_update_allowed_flag in varchar2,
980 x_app_enabled_flag in varchar2,
981 x_app_update_allowed_flag in varchar2,
982 x_resp_enabled_flag in varchar2,
983 x_resp_update_allowed_flag in varchar2,
984 x_user_enabled_flag in varchar2,
985 x_user_update_allowed_flag in varchar2,
986 x_start_date_active in varchar2,
987 x_end_date_active in varchar2,
988 x_sql_validation in varchar2)
989 is
990 begin
991 fnd_profile_options_pkg.load_row(
992 x_profile_name => x_profile_name,
993 x_owner => x_owner,
994 x_application_short_name => x_application_short_name,
995 x_user_profile_option_name => x_user_profile_option_name,
996 x_description => x_description,
1000 x_write_allowed_flag => x_write_allowed_flag,
997 x_user_changeable_flag => x_user_changeable_flag,
998 x_user_visible_flag => x_user_visible_flag,
999 x_read_allowed_flag => x_read_allowed_flag,
1001 x_site_enabled_flag => x_site_enabled_flag,
1002 x_site_update_allowed_flag => x_site_update_allowed_flag,
1003 x_app_enabled_flag => x_app_enabled_flag,
1004 x_app_update_allowed_flag => x_app_update_allowed_flag,
1005 x_resp_enabled_flag => x_resp_enabled_flag,
1006 x_resp_update_allowed_flag => x_resp_update_allowed_flag,
1007 x_user_enabled_flag => x_user_enabled_flag,
1008 x_user_update_allowed_flag => x_user_update_allowed_flag,
1009 x_start_date_active => x_start_date_active,
1010 x_end_date_active => x_end_date_active,
1011 x_sql_validation => x_sql_validation,
1012 x_hierarchy_type => 'SECURITY',
1013 x_custom_mode => '',
1014 x_last_update_date => '',
1015 x_server_enabled_flag => 'N',
1016 x_server_update_allowed_flag => 'N',
1017 x_org_enabled_flag => 'N',
1018 x_org_update_allowed_flag => 'N');
1019 end LOAD_ROW;
1020
1021 -- ### Overloaded!
1022 procedure LOAD_ROW (
1023 x_profile_name in varchar2,
1024 x_owner in varchar2,
1025 x_application_short_name in varchar2,
1026 x_user_profile_option_name in varchar2,
1027 x_description in varchar2,
1028 x_user_changeable_flag in varchar2,
1029 x_user_visible_flag in varchar2,
1030 x_read_allowed_flag in varchar2,
1031 x_write_allowed_flag in varchar2,
1032 x_site_enabled_flag in varchar2,
1033 x_site_update_allowed_flag in varchar2,
1034 x_app_enabled_flag in varchar2,
1035 x_app_update_allowed_flag in varchar2,
1036 x_resp_enabled_flag in varchar2,
1037 x_resp_update_allowed_flag in varchar2,
1038 x_user_enabled_flag in varchar2,
1039 x_user_update_allowed_flag in varchar2,
1040 x_start_date_active in varchar2,
1041 x_end_date_active in varchar2,
1042 x_sql_validation in varchar2,
1043 x_hierarchy_type in varchar2,
1044 x_custom_mode in varchar2,
1045 x_last_update_date in varchar2,
1046 x_server_enabled_flag in varchar2,
1047 x_server_update_allowed_flag in varchar2,
1048 x_org_enabled_flag in varchar2,
1049 x_org_update_allowed_flag in varchar2)
1050 is
1051 begin
1052 fnd_profile_options_pkg.load_row(
1053 x_profile_name => x_profile_name,
1054 x_owner => x_owner,
1055 x_application_short_name => x_application_short_name,
1056 x_user_profile_option_name => x_user_profile_option_name,
1057 x_description => x_description,
1058 x_user_changeable_flag => x_user_changeable_flag,
1059 x_user_visible_flag => x_user_visible_flag,
1060 x_read_allowed_flag => x_read_allowed_flag,
1061 x_write_allowed_flag => x_write_allowed_flag,
1062 x_site_enabled_flag => x_site_enabled_flag,
1063 x_site_update_allowed_flag => x_site_update_allowed_flag,
1064 x_app_enabled_flag => x_app_enabled_flag,
1065 x_app_update_allowed_flag => x_app_update_allowed_flag,
1066 x_resp_enabled_flag => x_resp_enabled_flag,
1067 x_resp_update_allowed_flag => x_resp_update_allowed_flag,
1068 x_user_enabled_flag => x_user_enabled_flag,
1069 x_user_update_allowed_flag => x_user_update_allowed_flag,
1070 x_start_date_active => x_start_date_active,
1071 x_end_date_active => x_end_date_active,
1072 x_sql_validation => x_sql_validation,
1073 x_hierarchy_type => x_hierarchy_type,
1074 x_custom_mode => x_custom_mode,
1075 x_last_update_date => x_last_update_date,
1076 x_server_enabled_flag => x_server_enabled_flag,
1077 x_server_update_allowed_flag => x_server_update_allowed_flag,
1078 x_org_enabled_flag => x_org_enabled_flag,
1079 x_org_update_allowed_flag => x_org_update_allowed_flag,
1080 x_serverresp_enabled_flag => 'N',
1081 x_serverresp_upd_allow_fl => 'N');
1082 end LOAD_ROW;
1083
1084 -- ### Overloaded!
1085 procedure LOAD_ROW (
1086 x_profile_name in varchar2,
1087 x_owner in varchar2,
1088 x_application_short_name in varchar2,
1089 x_user_profile_option_name in varchar2,
1090 x_description in varchar2,
1091 x_user_changeable_flag in varchar2,
1092 x_user_visible_flag in varchar2,
1093 x_read_allowed_flag in varchar2,
1094 x_write_allowed_flag in varchar2,
1095 x_site_enabled_flag in varchar2,
1096 x_site_update_allowed_flag in varchar2,
1097 x_app_enabled_flag in varchar2,
1098 x_app_update_allowed_flag in varchar2,
1099 x_resp_enabled_flag in varchar2,
1100 x_resp_update_allowed_flag in varchar2,
1101 x_user_enabled_flag in varchar2,
1102 x_user_update_allowed_flag in varchar2,
1103 x_start_date_active in varchar2,
1104 x_end_date_active in varchar2,
1105 x_sql_validation in varchar2,
1106 x_hierarchy_type in varchar2,
1107 x_custom_mode in varchar2,
1108 x_last_update_date in varchar2,
1109 x_server_enabled_flag in varchar2,
1110 x_server_update_allowed_flag in varchar2,
1111 x_org_enabled_flag in varchar2,
1112 x_org_update_allowed_flag in varchar2,
1113 x_serverresp_enabled_flag IN varchar2,
1114 x_serverresp_upd_allow_fl in varchar2)
1115 is
1116 app_id number := 0;
1117 profo_id number := 0;
1121 f_ludate date; -- entity update date in file
1118 user_id number := 0;
1119 row_id varchar2(64);
1120 f_luby number; -- entity owner in file
1122 db_luby number; -- entity owner in db
1123 db_ludate date; -- entity update date in db
1124 L_HIERARCHY_TYPE VARCHAR2(8);
1125 L_SERVER_ENABLED_FLAG VARCHAR2(1);
1126 L_SERVER_UPDATE_ALLOWED_FLAG VARCHAR2(1);
1127 L_ORG_ENABLED_FLAG VARCHAR(1);
1128 L_ORG_UPDATE_ALLOWED_FLAG VARCHAR2(1);
1129 L_SERVERRESP_ENABLED_FLAG VARCHAR2(1);
1130 L_SERVERRESP_UPD_ALLOW_FL VARCHAR2(1);
1131 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(x_profile_name);
1132
1133 begin
1134 /* For Org and Server Profile options default the hierarchy_type
1135 to maintain compatibility with old loader data files that
1136 don't have GRANT_FLAG */
1137
1138 if (X_HIERARCHY_TYPE is NULL) then
1139 L_HIERARCHY_TYPE := 'SECURITY';
1140 else
1141 L_HIERARCHY_TYPE := X_HIERARCHY_TYPE;
1142 end if;
1143
1144 if (X_SERVER_ENABLED_FLAG is NULL) then
1145 L_SERVER_ENABLED_FLAG := 'N';
1146 else
1147 L_SERVER_ENABLED_FLAG := X_SERVER_ENABLED_FLAG;
1148 end if;
1149
1150 if (X_SERVER_UPDATE_ALLOWED_FLAG is NULL) then
1151 L_SERVER_UPDATE_ALLOWED_FLAG := 'N';
1152 else
1153 L_SERVER_UPDATE_ALLOWED_FLAG := X_SERVER_UPDATE_ALLOWED_FLAG;
1154 end if;
1155
1156 if (X_ORG_ENABLED_FLAG is NULL) then
1157 L_ORG_ENABLED_FLAG := 'N';
1158 else
1159 L_ORG_ENABLED_FLAG := X_ORG_ENABLED_FLAG;
1160 end if;
1161
1162 if (X_ORG_UPDATE_ALLOWED_FLAG is NULL) then
1163 L_ORG_UPDATE_ALLOWED_FLAG := 'N';
1164 else
1165 L_ORG_UPDATE_ALLOWED_FLAG := X_ORG_UPDATE_ALLOWED_FLAG;
1166 end if;
1167
1168 if (X_SERVERRESP_ENABLED_FLAG is NULL) then
1169 L_SERVERRESP_ENABLED_FLAG := 'N';
1170 else
1171 L_SERVERRESP_ENABLED_FLAG := X_SERVERRESP_ENABLED_FLAG;
1172 end if;
1173
1174 if (X_SERVERRESP_UPD_ALLOW_FL is NULL) then
1175 L_SERVERRESP_UPD_ALLOW_FL := 'N';
1176 else
1177 L_SERVERRESP_UPD_ALLOW_FL := X_SERVERRESP_UPD_ALLOW_FL;
1178 end if;
1179
1180 select application_id into app_id
1181 from fnd_application
1182 where application_short_name = X_APPLICATION_SHORT_NAME;
1183
1184 -- Translate owner to file_last_updated_by
1185 f_luby := fnd_load_util.owner_id(x_owner);
1186
1187 -- Translate char last_update_date to date
1188 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1189
1190 begin
1191 select PROFILE_OPTION_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
1192 into profo_id, db_luby, db_ludate
1193 from FND_PROFILE_OPTIONS
1194 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME;
1195
1196 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1197 db_ludate, X_CUSTOM_MODE)) then
1198
1199 fnd_profile_options_pkg.update_row(
1200 x_profile_option_name=> L_PROFILE_OPTION_NAME,
1201 x_application_id => app_id,
1202 x_profile_option_id => profo_id,
1203 x_write_allowed_flag => x_write_allowed_flag,
1204 x_read_allowed_flag => x_read_allowed_flag,
1205 x_user_changeable_flag => x_user_changeable_flag,
1206 x_user_visible_flag => x_user_visible_flag,
1207 x_site_enabled_flag => x_site_enabled_flag,
1208 x_site_update_allowed_flag => x_site_update_allowed_flag,
1209 x_app_enabled_flag => x_app_enabled_flag,
1210 x_app_update_allowed_flag => x_app_update_allowed_flag,
1211 x_resp_enabled_flag => x_resp_enabled_flag,
1212 x_resp_update_allowed_flag => x_resp_update_allowed_flag,
1213 x_user_enabled_flag => x_user_enabled_flag,
1214 x_user_update_allowed_flag => x_user_update_allowed_flag,
1215 x_start_date_active => to_date(x_start_date_active, 'YYYY/MM/DD'),
1216 x_sql_validation => x_sql_validation,
1217 x_end_date_active => to_date(x_end_date_active, 'YYYY/MM/DD'),
1218 x_user_profile_option_name => x_user_profile_option_name,
1219 x_description => x_description,
1220 x_last_update_date => f_ludate,
1221 x_last_updated_by => f_luby,
1222 x_last_update_login => 0,
1223 x_hierarchy_type => l_hierarchy_type,
1224 x_server_enabled_flag => l_server_enabled_flag,
1225 x_server_update_allowed_flag => l_server_update_allowed_flag,
1226 x_org_enabled_flag => l_org_enabled_flag,
1227 x_org_update_allowed_flag => l_org_update_allowed_flag,
1228 x_serverresp_enabled_flag => l_serverresp_enabled_flag,
1229 x_serverresp_upd_allow_fl => l_serverresp_upd_allow_fl);
1230
1231 end if;
1232 exception
1233 when no_data_found then
1234 select fnd_profile_options_s.nextval
1235 into profo_id
1236 from dual;
1237
1238 fnd_profile_options_pkg.insert_row (
1239 x_rowid => row_id,
1240 x_profile_option_name => L_PROFILE_OPTION_NAME,
1241 x_application_id => app_id,
1242 x_profile_option_id => profo_id,
1243 x_write_allowed_flag => x_write_allowed_flag,
1244 x_read_allowed_flag => x_read_allowed_flag,
1245 x_user_changeable_flag => x_user_changeable_flag,
1249 x_app_enabled_flag => x_app_enabled_flag,
1246 x_user_visible_flag => x_user_visible_flag,
1247 x_site_enabled_flag => x_site_enabled_flag,
1248 x_site_update_allowed_flag => x_site_update_allowed_flag,
1250 x_app_update_allowed_flag => x_app_update_allowed_flag,
1251 x_resp_enabled_flag => x_resp_enabled_flag,
1252 x_resp_update_allowed_flag => x_resp_update_allowed_flag,
1253 x_user_enabled_flag => x_user_enabled_flag,
1254 x_user_update_allowed_flag => x_user_update_allowed_flag,
1255 x_start_date_active =>to_date(x_start_date_active, 'YYYY/MM/DD'),
1256 x_sql_validation => x_sql_validation,
1257 x_end_date_active => to_date(x_end_date_active, 'YYYY/MM/DD'),
1258 x_user_profile_option_name => x_user_profile_option_name,
1259 x_description => x_description,
1260 x_creation_date => f_ludate,
1261 x_created_by => f_luby,
1262 x_last_update_date => f_ludate,
1263 x_last_updated_by => f_luby,
1264 x_last_update_login => 0,
1265 x_hierarchy_type => l_hierarchy_type,
1266 x_server_enabled_flag => l_server_enabled_flag,
1267 x_server_update_allowed_flag => l_server_update_allowed_flag,
1268 x_org_enabled_flag => l_org_enabled_flag,
1269 x_org_update_allowed_flag => l_org_update_allowed_flag,
1270 x_serverresp_enabled_flag => l_serverresp_enabled_flag,
1271 x_serverresp_upd_allow_fl => l_serverresp_upd_allow_fl);
1272 end;
1273 end LOAD_ROW;
1274
1275 procedure DELETE_ROW (
1276 X_PROFILE_OPTION_NAME in VARCHAR2
1277 ) is
1278 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(X_PROFILE_OPTION_NAME);
1279 begin
1280
1281 -- Delete profile option values first, so that there are no residual
1282 -- profile option values to a non-existent profile option. This introduces
1283 -- a dependency to FND_PROFILE_OPTION_VALUES_PKG.
1284 FND_PROFILE_OPTION_VALUES_PKG.DELETE_PROFILE_OPTION_VALUES(
1285 L_PROFILE_OPTION_NAME);
1286
1287 delete from FND_PROFILE_OPTIONS
1288 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME;
1289
1290 if (sql%notfound) then
1291 raise no_data_found;
1292 end if;
1293
1294 delete from FND_PROFILE_OPTIONS_TL
1295 where PROFILE_OPTION_NAME = L_PROFILE_OPTION_NAME;
1296
1297 if (sql%notfound) then
1298 raise no_data_found;
1299 end if;
1300 end DELETE_ROW;
1301
1302 procedure ADD_LANGUAGE
1303 is
1304 begin
1305 /* Mar/19/03 requested by Ric Ginsberg */
1306 /* The following delete and update statements are commented out */
1307 /* as a quick workaround to fix the time-consuming table handler issue */
1308 /* Eventually we'll need to turn them into a separate fix_language procedure */
1309 /*
1310
1311 delete from FND_PROFILE_OPTIONS_TL T
1312 where not exists
1313 (select NULL
1314 from FND_PROFILE_OPTIONS B
1315 where B.PROFILE_OPTION_NAME = T.PROFILE_OPTION_NAME
1316 );
1317
1318 update FND_PROFILE_OPTIONS_TL T set (
1319 USER_PROFILE_OPTION_NAME,
1320 DESCRIPTION
1321 ) = (select
1322 B.USER_PROFILE_OPTION_NAME,
1323 B.DESCRIPTION
1324 from FND_PROFILE_OPTIONS_TL B
1325 where B.PROFILE_OPTION_NAME = T.PROFILE_OPTION_NAME
1326 and B.LANGUAGE = T.SOURCE_LANG)
1327 where (
1328 T.PROFILE_OPTION_NAME,
1329 T.LANGUAGE
1330 ) in (select
1331 SUBT.PROFILE_OPTION_NAME,
1332 SUBT.LANGUAGE
1333 from FND_PROFILE_OPTIONS_TL SUBB, FND_PROFILE_OPTIONS_TL SUBT
1334 where SUBB.PROFILE_OPTION_NAME = SUBT.PROFILE_OPTION_NAME
1335 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1336 and (SUBB.USER_PROFILE_OPTION_NAME <> SUBT.USER_PROFILE_OPTION_NAME
1337 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1338 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
1339 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
1340 ));
1341 */
1342
1343 insert into FND_PROFILE_OPTIONS_TL (
1344 PROFILE_OPTION_NAME,
1345 USER_PROFILE_OPTION_NAME,
1346 DESCRIPTION,
1347 CREATED_BY,
1348 CREATION_DATE,
1349 LAST_UPDATED_BY,
1350 LAST_UPDATE_DATE,
1351 LAST_UPDATE_LOGIN,
1352 LANGUAGE,
1353 SOURCE_LANG
1354 ) select
1355 B.PROFILE_OPTION_NAME,
1356 B.USER_PROFILE_OPTION_NAME,
1357 B.DESCRIPTION,
1358 B.CREATED_BY,
1359 B.CREATION_DATE,
1360 B.LAST_UPDATED_BY,
1361 B.LAST_UPDATE_DATE,
1362 B.LAST_UPDATE_LOGIN,
1363 L.LANGUAGE_CODE,
1364 B.SOURCE_LANG
1365 from FND_PROFILE_OPTIONS_TL B, FND_LANGUAGES L
1366 where L.INSTALLED_FLAG in ('I', 'B')
1367 and B.LANGUAGE = userenv('LANG')
1368 and not exists
1369 (select NULL
1370 from FND_PROFILE_OPTIONS_TL T
1371 where T.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME
1372 and T.LANGUAGE = L.LANGUAGE_CODE);
1373 end ADD_LANGUAGE;
1374
1375 /*
1376 ** UPDATE_APPL_ID_PK_COLUMNS - Updates the fk references of
1377 ** APPLICATION_ID in FND_PROFILE_OTPION_VALUES.
1378 **
1379 **
1380 ** AOL_INTERNAL ONLY
1381 **
1382 ** If the profile is moved to a different application, replace old
1383 ** application_id with the new value.
1384 **
1385 ** Check if this update call is for the update of
1386 ** foreign key application_id i.e., this profile option is moved
1387 ** to a different applicatjion. If so, replace
1388 ** all references of application_id of this profile option
1389 ** in FND_PROFILE_OTPION_VALUES to the new value.
1390 */
1391 PROCEDURE UPDATE_APPL_ID_PK_COLUMNS( x_profile_option_name varchar2,
1392 x_profile_id varchar2,
1393 x_appl_id varchar2)
1394 IS
1395 db_appl_id number;
1396 col Fnd_Dictionary_Pkg.NameArrayTyp;
1397 old_val Fnd_Dictionary_Pkg.NameArrayTyp;
1398 new_val Fnd_Dictionary_Pkg.NameArrayTyp;
1399
1400 result boolean;
1401 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(X_PROFILE_OPTION_NAME);
1402
1403 BEGIN
1404 select application_id
1405 into db_appl_id
1406 from fnd_profile_options
1407 where profile_option_name = L_PROFILE_OPTION_NAME;
1408
1409
1410 if (db_appl_id = x_appl_id) then
1411 return;
1412 else
1413 col(0) := upper('APPLICATION_ID');
1414 col(1) := upper('PROFILE_OPTION_ID');
1415 col(2) := null;
1416 old_val(0) := db_appl_id;
1417 old_val(1) := x_profile_id;
1418 old_val(2) := null;
1419 new_val(0) := x_appl_id;
1420 new_val(1) := x_profile_id;
1421 new_val(2) := null;
1422 result:=Fnd_Dictionary_Pkg.UpdatePKColumns('FND','FND_PROFILE_OPTIONS',
1423 col, old_val, new_val
1424 );
1425 end if;
1426 EXCEPTION
1427 when no_data_found then
1428 return;
1429 END UPDATE_APPL_ID_PK_COLUMNS;
1430
1431
1432 /*
1433 ** UPDATE_APPL_ID_CAT_OPTIONS - Updates the fk references of
1434 ** APPLICATION_ID in FND_PROFILE_CAT_OPTIONS.
1435 **
1436 **
1437 ** AOL_INTERNAL ONLY
1438 **
1439 ** Bug 5060938.
1440 ** This api is used to propagate updation of profile from Application1 to
1441 ** Application2 to Fnd_Profile_cat_Options table.
1442 ** DROP/DELETE this api after finding out a way to create Foreign_Key and
1443 ** Primary_Key info in FND_FOREIGN_KEYS and FND_PRIMARY_KEYS AOL meta data
1444 ** through fnd_profile_cat_options.xdf.
1445 **
1446 ** If the profile is moved to a different application, replace old
1447 ** application_id with the new value.
1448 **
1449 ** Check if Fnd_Profile_Cat_Options table and Profile_Option_Application_Id
1450 ** column exists. If so, update the Profile_Option_Application_Id for all the
1451 ** profiles in Fnd_Profile_Cat_Options table with the new value.
1452 */
1453 PROCEDURE UPDATE_CAT_OPTIONS_APPL_ID( x_profile_option_name varchar2,
1454 x_profile_id varchar2,
1455 x_appl_id number)
1456 IS
1457 db_appl_id number;
1458 l_appl_id number;
1459 result boolean;
1460
1461 profOptApplId number;
1462 L_PROFILE_OPTION_NAME VARCHAR2(80):= UPPER(X_PROFILE_OPTION_NAME);
1463
1464 COL_NOT_FOUND EXCEPTION;
1465 TAB_NOT_FOUND EXCEPTION;
1466
1467 PRAGMA EXCEPTION_INIT(COL_NOT_FOUND, -904);
1468 PRAGMA EXCEPTION_INIT(TAB_NOT_FOUND, -942);
1469 BEGIN
1470 select application_id
1471 into db_appl_id
1472 from fnd_profile_options
1473 where profile_option_name = L_PROFILE_OPTION_NAME;
1474
1475 begin
1476 SELECT profile_option_application_id
1477 INTO l_appl_id
1478 FROM fnd_profile_cat_options
1479 WHERE ROWNUM < 2;
1480 exception
1481 when COL_NOT_FOUND then
1482 return;
1483 when TAB_NOT_FOUND then
1484 return;
1485 when others then
1486 null;
1487 end;
1488
1489 if (db_appl_id = x_appl_id) then
1490 return;
1491 else
1492 UPDATE fnd_profile_cat_options
1493 SET profile_option_application_id = x_appl_id
1494 WHERE profile_option_id = x_profile_id
1495 AND profile_option_application_id = db_appl_id;
1496 end if;
1497 EXCEPTION
1498 when no_data_found then
1499 return;
1500 END UPDATE_CAT_OPTIONS_APPL_ID;
1501
1502 end FND_PROFILE_OPTIONS_PKG;