[Home] [Help]
PACKAGE BODY: APPS.FND_DESCRIPTIVE_FLEXS_PKG
Source
1 package body FND_DESCRIPTIVE_FLEXS_PKG as
2 /* $Header: AFFFDFFB.pls 120.11.12010000.1 2008/07/25 14:13:48 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
8 X_CONCATENATED_SEGS_VIEW_NAME in VARCHAR2,
9 X_APPLICATION_TABLE_NAME in VARCHAR2,
10 X_TABLE_APPLICATION_ID in NUMBER,
11 X_CONTEXT_REQUIRED_FLAG in VARCHAR2,
12 X_CONTEXT_COLUMN_NAME in VARCHAR2,
13 X_CONTEXT_USER_OVERRIDE_FLAG in VARCHAR2,
14 X_CONCATENATED_SEGMENT_DELIMIT in VARCHAR2,
15 X_FREEZE_FLEX_DEFINITION_FLAG in VARCHAR2,
16 X_PROTECTED_FLAG in VARCHAR2,
17 X_DEFAULT_CONTEXT_FIELD_NAME in VARCHAR2,
18 X_DEFAULT_CONTEXT_VALUE in VARCHAR2,
19 X_CONTEXT_DEFAULT_TYPE in VARCHAR2,
20 X_CONTEXT_DEFAULT_VALUE in VARCHAR2,
21 X_CONTEXT_OVERRIDE_VALUE_SET_I in NUMBER,
22 X_CONTEXT_RUNTIME_PROPERTY_FUN in VARCHAR2,
23 X_CONTEXT_SYNCHRONIZATION_FLAG in VARCHAR2 DEFAULT NULL,
24 X_TITLE in VARCHAR2,
25 X_FORM_CONTEXT_PROMPT 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 ) is
33 cursor C is select ROWID from FND_DESCRIPTIVE_FLEXS
34 where APPLICATION_ID = X_APPLICATION_ID
35 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
36 ;
37
38 l_context_synchronization_flag VARCHAR2(1);
39 begin
40 IF X_CONTEXT_SYNCHRONIZATION_FLAG IS NULL THEN
41 IF ( SUBSTR(X_DESCRIPTIVE_FLEXFIELD_NAME, 1, 6) = '$SRS$.' ) THEN
42 l_context_synchronization_flag := 'N';
43 ELSE
44 l_context_synchronization_flag := 'X';
45 END IF;
46 ELSE
47 l_context_synchronization_flag := X_CONTEXT_SYNCHRONIZATION_FLAG;
48 END IF;
49 insert into FND_DESCRIPTIVE_FLEXS (
50 CONCATENATED_SEGS_VIEW_NAME,
51 APPLICATION_ID,
52 APPLICATION_TABLE_NAME,
53 DESCRIPTIVE_FLEXFIELD_NAME,
54 TABLE_APPLICATION_ID,
55 CONTEXT_REQUIRED_FLAG,
56 CONTEXT_COLUMN_NAME,
57 CONTEXT_USER_OVERRIDE_FLAG,
58 CONCATENATED_SEGMENT_DELIMITER,
59 FREEZE_FLEX_DEFINITION_FLAG,
60 PROTECTED_FLAG,
61 DEFAULT_CONTEXT_FIELD_NAME,
62 DEFAULT_CONTEXT_VALUE,
63 CONTEXT_DEFAULT_TYPE,
64 CONTEXT_DEFAULT_VALUE,
65 CONTEXT_OVERRIDE_VALUE_SET_ID,
66 CONTEXT_RUNTIME_PROPERTY_FUNCT,
67 CONTEXT_SYNCHRONIZATION_FLAG,
68 CREATION_DATE,
69 CREATED_BY,
70 LAST_UPDATE_DATE,
71 LAST_UPDATED_BY,
72 LAST_UPDATE_LOGIN
73 ) values (
74 X_CONCATENATED_SEGS_VIEW_NAME,
75 X_APPLICATION_ID,
76 X_APPLICATION_TABLE_NAME,
77 X_DESCRIPTIVE_FLEXFIELD_NAME,
78 X_TABLE_APPLICATION_ID,
79 X_CONTEXT_REQUIRED_FLAG,
80 X_CONTEXT_COLUMN_NAME,
81 X_CONTEXT_USER_OVERRIDE_FLAG,
82 X_CONCATENATED_SEGMENT_DELIMIT,
83 X_FREEZE_FLEX_DEFINITION_FLAG,
84 X_PROTECTED_FLAG,
85 X_DEFAULT_CONTEXT_FIELD_NAME,
86 X_DEFAULT_CONTEXT_VALUE,
87 X_CONTEXT_DEFAULT_TYPE,
88 X_CONTEXT_DEFAULT_VALUE,
89 X_CONTEXT_OVERRIDE_VALUE_SET_I,
90 X_CONTEXT_RUNTIME_PROPERTY_FUN,
91 l_context_synchronization_flag,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_LOGIN
97 );
98
99 insert into FND_DESCRIPTIVE_FLEXS_TL (
100 APPLICATION_ID,
101 DESCRIPTIVE_FLEXFIELD_NAME,
102 TITLE,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 CREATION_DATE,
106 CREATED_BY,
107 LAST_UPDATE_LOGIN,
108 FORM_CONTEXT_PROMPT,
109 DESCRIPTION,
110 LANGUAGE,
111 SOURCE_LANG
112 ) select
113 X_APPLICATION_ID,
114 X_DESCRIPTIVE_FLEXFIELD_NAME,
115 X_TITLE,
116 X_LAST_UPDATE_DATE,
117 X_LAST_UPDATED_BY,
118 X_CREATION_DATE,
119 X_CREATED_BY,
120 X_LAST_UPDATE_LOGIN,
121 X_FORM_CONTEXT_PROMPT,
122 X_DESCRIPTION,
123 L.LANGUAGE_CODE,
124 userenv('LANG')
125 from FND_LANGUAGES L
126 where L.INSTALLED_FLAG in ('I', 'B')
127 and not exists
128 (select NULL
129 from FND_DESCRIPTIVE_FLEXS_TL T
130 where T.APPLICATION_ID = X_APPLICATION_ID
131 and T.DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
132 and T.LANGUAGE = L.LANGUAGE_CODE);
133
134 open c;
135 fetch c into X_ROWID;
136 if (c%notfound) then
137 close c;
138 raise no_data_found;
139 end if;
140 close c;
141
142 end INSERT_ROW;
143
144 procedure LOCK_ROW (
145 X_APPLICATION_ID in NUMBER,
146 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
147 X_CONCATENATED_SEGS_VIEW_NAME in VARCHAR2,
148 X_APPLICATION_TABLE_NAME in VARCHAR2,
149 X_TABLE_APPLICATION_ID in NUMBER,
150 X_CONTEXT_REQUIRED_FLAG in VARCHAR2,
151 X_CONTEXT_COLUMN_NAME in VARCHAR2,
152 X_CONTEXT_USER_OVERRIDE_FLAG in VARCHAR2,
153 X_CONCATENATED_SEGMENT_DELIMIT in VARCHAR2,
154 X_FREEZE_FLEX_DEFINITION_FLAG in VARCHAR2,
155 X_PROTECTED_FLAG in VARCHAR2,
156 X_DEFAULT_CONTEXT_FIELD_NAME in VARCHAR2,
157 X_DEFAULT_CONTEXT_VALUE in VARCHAR2,
158 X_CONTEXT_DEFAULT_TYPE in VARCHAR2,
159 X_CONTEXT_DEFAULT_VALUE in VARCHAR2,
160 X_CONTEXT_OVERRIDE_VALUE_SET_I in NUMBER,
161 X_CONTEXT_RUNTIME_PROPERTY_FUN in VARCHAR2,
162 X_CONTEXT_SYNCHRONIZATION_FLAG in VARCHAR2,
163 X_TITLE in VARCHAR2,
164 X_FORM_CONTEXT_PROMPT in VARCHAR2,
165 X_DESCRIPTION in VARCHAR2
166 ) is
167 cursor c is select
168 CONCATENATED_SEGS_VIEW_NAME,
169 APPLICATION_TABLE_NAME,
170 TABLE_APPLICATION_ID,
171 CONTEXT_REQUIRED_FLAG,
172 CONTEXT_COLUMN_NAME,
173 CONTEXT_USER_OVERRIDE_FLAG,
174 CONCATENATED_SEGMENT_DELIMITER,
175 FREEZE_FLEX_DEFINITION_FLAG,
176 PROTECTED_FLAG,
177 DEFAULT_CONTEXT_FIELD_NAME,
178 DEFAULT_CONTEXT_VALUE,
179 CONTEXT_DEFAULT_TYPE,
180 CONTEXT_DEFAULT_VALUE,
181 CONTEXT_OVERRIDE_VALUE_SET_ID,
182 CONTEXT_RUNTIME_PROPERTY_FUNCT,
183 CONTEXT_SYNCHRONIZATION_FLAG
184 from FND_DESCRIPTIVE_FLEXS
185 where APPLICATION_ID = X_APPLICATION_ID
186 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
187 for update of APPLICATION_ID nowait;
188 recinfo c%rowtype;
189
190 cursor c1 is select
191 TITLE,
192 FORM_CONTEXT_PROMPT,
193 DESCRIPTION,
194 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
195 from FND_DESCRIPTIVE_FLEXS_TL
196 where APPLICATION_ID = X_APPLICATION_ID
197 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
198 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
199 for update of APPLICATION_ID nowait;
200 begin
201 open c;
202 fetch c into recinfo;
203 if (c%notfound) then
204 close c;
205 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 end if;
208 close c;
209 if ( ((recinfo.CONCATENATED_SEGS_VIEW_NAME = X_CONCATENATED_SEGS_VIEW_NAME)
210 OR ((recinfo.CONCATENATED_SEGS_VIEW_NAME is null) AND (X_CONCATENATED_SEGS_VIEW_NAME is null)))
211 AND (recinfo.APPLICATION_TABLE_NAME = X_APPLICATION_TABLE_NAME)
212 AND (recinfo.TABLE_APPLICATION_ID = X_TABLE_APPLICATION_ID)
213 AND (recinfo.CONTEXT_REQUIRED_FLAG = X_CONTEXT_REQUIRED_FLAG)
214 AND (recinfo.CONTEXT_COLUMN_NAME = X_CONTEXT_COLUMN_NAME)
215 AND (recinfo.CONTEXT_USER_OVERRIDE_FLAG = X_CONTEXT_USER_OVERRIDE_FLAG)
216 AND (recinfo.CONCATENATED_SEGMENT_DELIMITER = X_CONCATENATED_SEGMENT_DELIMIT)
217 AND (recinfo.FREEZE_FLEX_DEFINITION_FLAG = X_FREEZE_FLEX_DEFINITION_FLAG)
218 AND (recinfo.PROTECTED_FLAG = X_PROTECTED_FLAG)
219 AND ((recinfo.DEFAULT_CONTEXT_FIELD_NAME = X_DEFAULT_CONTEXT_FIELD_NAME)
220 OR ((recinfo.DEFAULT_CONTEXT_FIELD_NAME is null) AND (X_DEFAULT_CONTEXT_FIELD_NAME is null)))
221 AND ((recinfo.DEFAULT_CONTEXT_VALUE = X_DEFAULT_CONTEXT_VALUE)
222 OR ((recinfo.DEFAULT_CONTEXT_VALUE is null) AND (X_DEFAULT_CONTEXT_VALUE is null)))
223 AND ((recinfo.CONTEXT_DEFAULT_TYPE = X_CONTEXT_DEFAULT_TYPE)
224 OR ((recinfo.CONTEXT_DEFAULT_TYPE is null) AND (X_CONTEXT_DEFAULT_TYPE is null)))
225 AND ((recinfo.CONTEXT_DEFAULT_VALUE = X_CONTEXT_DEFAULT_VALUE)
226 OR ((recinfo.CONTEXT_DEFAULT_VALUE is null) AND (X_CONTEXT_DEFAULT_VALUE is null)))
227 AND ((recinfo.CONTEXT_OVERRIDE_VALUE_SET_ID = X_CONTEXT_OVERRIDE_VALUE_SET_I)
228 OR ((recinfo.CONTEXT_OVERRIDE_VALUE_SET_ID is null) AND (X_CONTEXT_OVERRIDE_VALUE_SET_I is null)))
229 AND ((recinfo.CONTEXT_RUNTIME_PROPERTY_FUNCT = X_CONTEXT_RUNTIME_PROPERTY_FUN)
230 OR ((recinfo.CONTEXT_RUNTIME_PROPERTY_FUNCT is null) AND (X_CONTEXT_RUNTIME_PROPERTY_FUN is null)))
231 AND ((recinfo.CONTEXT_SYNCHRONIZATION_FLAG = X_CONTEXT_SYNCHRONIZATION_FLAG)
232 OR ((recinfo.CONTEXT_SYNCHRONIZATION_FLAG is null) AND (X_CONTEXT_SYNCHRONIZATION_FLAG is null)))
233 ) then
234 null;
235 else
236 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
237 app_exception.raise_exception;
238 end if;
239
240 for tlinfo in c1 loop
241 if (tlinfo.BASELANG = 'Y') then
242 if ( (tlinfo.TITLE = X_TITLE)
243 AND (tlinfo.FORM_CONTEXT_PROMPT = X_FORM_CONTEXT_PROMPT)
244 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
245 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
246 ) then
247 null;
248 else
249 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
250 app_exception.raise_exception;
251 end if;
252 end if;
253 end loop;
254 return;
255 end LOCK_ROW;
256
257 procedure UPDATE_ROW (
258 X_APPLICATION_ID in NUMBER,
259 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
260 X_CONCATENATED_SEGS_VIEW_NAME in VARCHAR2,
261 X_APPLICATION_TABLE_NAME in VARCHAR2,
262 X_TABLE_APPLICATION_ID in NUMBER,
263 X_CONTEXT_REQUIRED_FLAG in VARCHAR2,
264 X_CONTEXT_COLUMN_NAME in VARCHAR2,
265 X_CONTEXT_USER_OVERRIDE_FLAG in VARCHAR2,
266 X_CONCATENATED_SEGMENT_DELIMIT in VARCHAR2,
267 X_FREEZE_FLEX_DEFINITION_FLAG in VARCHAR2,
268 X_PROTECTED_FLAG in VARCHAR2,
269 X_DEFAULT_CONTEXT_FIELD_NAME in VARCHAR2,
270 X_DEFAULT_CONTEXT_VALUE in VARCHAR2,
271 X_CONTEXT_DEFAULT_TYPE in VARCHAR2,
272 X_CONTEXT_DEFAULT_VALUE in VARCHAR2,
273 X_CONTEXT_OVERRIDE_VALUE_SET_I in NUMBER,
274 X_CONTEXT_RUNTIME_PROPERTY_FUN in VARCHAR2,
275 X_CONTEXT_SYNCHRONIZATION_FLAG in VARCHAR2 DEFAULT NULL,
276 X_TITLE in VARCHAR2,
277 X_FORM_CONTEXT_PROMPT in VARCHAR2,
278 X_DESCRIPTION in VARCHAR2,
279 X_LAST_UPDATE_DATE in DATE,
280 X_LAST_UPDATED_BY in NUMBER,
281 X_LAST_UPDATE_LOGIN in NUMBER
282 ) is
283 l_context_synchronization_flag VARCHAR2(1);
284 begin
285 IF X_CONTEXT_SYNCHRONIZATION_FLAG IS NULL THEN
286 IF ( SUBSTR(X_DESCRIPTIVE_FLEXFIELD_NAME, 1, 6) = '$SRS$.' ) THEN
287 l_context_synchronization_flag := 'N';
288 ELSE
289 l_context_synchronization_flag := 'X';
290 END IF;
291 ELSE
292 l_context_synchronization_flag := X_CONTEXT_SYNCHRONIZATION_FLAG;
293 END IF;
294 update FND_DESCRIPTIVE_FLEXS set
295 CONCATENATED_SEGS_VIEW_NAME = X_CONCATENATED_SEGS_VIEW_NAME,
296 APPLICATION_TABLE_NAME = X_APPLICATION_TABLE_NAME,
297 TABLE_APPLICATION_ID = X_TABLE_APPLICATION_ID,
298 CONTEXT_REQUIRED_FLAG = X_CONTEXT_REQUIRED_FLAG,
299 CONTEXT_COLUMN_NAME = X_CONTEXT_COLUMN_NAME,
300 CONTEXT_USER_OVERRIDE_FLAG = X_CONTEXT_USER_OVERRIDE_FLAG,
301 CONCATENATED_SEGMENT_DELIMITER = X_CONCATENATED_SEGMENT_DELIMIT,
302 FREEZE_FLEX_DEFINITION_FLAG = X_FREEZE_FLEX_DEFINITION_FLAG,
303 PROTECTED_FLAG = X_PROTECTED_FLAG,
304 DEFAULT_CONTEXT_FIELD_NAME = X_DEFAULT_CONTEXT_FIELD_NAME,
305 DEFAULT_CONTEXT_VALUE = X_DEFAULT_CONTEXT_VALUE,
306 CONTEXT_DEFAULT_TYPE = X_CONTEXT_DEFAULT_TYPE,
307 CONTEXT_DEFAULT_VALUE = X_CONTEXT_DEFAULT_VALUE,
308 CONTEXT_OVERRIDE_VALUE_SET_ID = X_CONTEXT_OVERRIDE_VALUE_SET_I,
309 CONTEXT_RUNTIME_PROPERTY_FUNCT = X_CONTEXT_RUNTIME_PROPERTY_FUN,
310 CONTEXT_SYNCHRONIZATION_FLAG = l_context_synchronization_flag,
311 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
312 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
314 where APPLICATION_ID = X_APPLICATION_ID
315 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME;
316
317 if (sql%notfound) then
318 raise no_data_found;
319 end if;
320
321 update FND_DESCRIPTIVE_FLEXS_TL set
322 TITLE = X_TITLE,
323 FORM_CONTEXT_PROMPT = X_FORM_CONTEXT_PROMPT,
324 DESCRIPTION = X_DESCRIPTION,
325 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
326 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
327 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
328 SOURCE_LANG = userenv('LANG')
329 where APPLICATION_ID = X_APPLICATION_ID
330 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
331 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
332
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336 end UPDATE_ROW;
337
338 procedure DELETE_ROW (
339 X_APPLICATION_ID in NUMBER,
340 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2
341 ) is
342 begin
343 delete from FND_DESCRIPTIVE_FLEXS_TL
344 where APPLICATION_ID = X_APPLICATION_ID
345 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME;
346
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350
351 delete from FND_DESCRIPTIVE_FLEXS
352 where APPLICATION_ID = X_APPLICATION_ID
353 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME;
354
355 if (sql%notfound) then
356 raise no_data_found;
357 end if;
358 end DELETE_ROW;
359
360 procedure ADD_LANGUAGE
361 is
362 begin
363
364 /* Mar/19/03 requested by Ric Ginsberg */
365 /* The following delete and update statements are commented out */
366 /* as a quick workaround to fix the time-consuming table handler issue */
367 /* Eventually we'll need to turn them into a separate fix_language procedure */
368 /*
369 delete from FND_DESCRIPTIVE_FLEXS_TL T
370 where not exists
371 (select NULL
372 from FND_DESCRIPTIVE_FLEXS B
373 where B.APPLICATION_ID = T.APPLICATION_ID
374 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
375 );
376
377 update FND_DESCRIPTIVE_FLEXS_TL T set (
378 TITLE,
379 FORM_CONTEXT_PROMPT,
380 DESCRIPTION
381 ) = (select
382 B.TITLE,
383 B.FORM_CONTEXT_PROMPT,
384 B.DESCRIPTION
385 from FND_DESCRIPTIVE_FLEXS_TL B
386 where B.APPLICATION_ID = T.APPLICATION_ID
387 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
388 and B.LANGUAGE = T.SOURCE_LANG)
389 where (
390 T.APPLICATION_ID,
391 T.DESCRIPTIVE_FLEXFIELD_NAME,
392 T.LANGUAGE
393 ) in (select
394 SUBT.APPLICATION_ID,
395 SUBT.DESCRIPTIVE_FLEXFIELD_NAME,
396 SUBT.LANGUAGE
397 from FND_DESCRIPTIVE_FLEXS_TL SUBB, FND_DESCRIPTIVE_FLEXS_TL SUBT
398 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
399 and SUBB.DESCRIPTIVE_FLEXFIELD_NAME = SUBT.DESCRIPTIVE_FLEXFIELD_NAME
400 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
401 and (SUBB.TITLE <> SUBT.TITLE
402 or SUBB.FORM_CONTEXT_PROMPT <> SUBT.FORM_CONTEXT_PROMPT
403 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
404 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
405 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
406 ));
407 */
408
409 insert into FND_DESCRIPTIVE_FLEXS_TL (
410 APPLICATION_ID,
411 DESCRIPTIVE_FLEXFIELD_NAME,
412 TITLE,
416 CREATED_BY,
413 LAST_UPDATE_DATE,
414 LAST_UPDATED_BY,
415 CREATION_DATE,
417 LAST_UPDATE_LOGIN,
418 FORM_CONTEXT_PROMPT,
419 DESCRIPTION,
420 LANGUAGE,
421 SOURCE_LANG
422 ) select /*+ ORDERED */
423 B.APPLICATION_ID,
424 B.DESCRIPTIVE_FLEXFIELD_NAME,
425 B.TITLE,
426 B.LAST_UPDATE_DATE,
427 B.LAST_UPDATED_BY,
428 B.CREATION_DATE,
429 B.CREATED_BY,
430 B.LAST_UPDATE_LOGIN,
431 B.FORM_CONTEXT_PROMPT,
432 B.DESCRIPTION,
433 L.LANGUAGE_CODE,
434 B.SOURCE_LANG
435 from FND_DESCRIPTIVE_FLEXS_TL B, FND_LANGUAGES L
436 where L.INSTALLED_FLAG in ('I', 'B')
437 and B.LANGUAGE = userenv('LANG')
438 and not exists
439 (select NULL
440 from FND_DESCRIPTIVE_FLEXS_TL T
441 where T.APPLICATION_ID = B.APPLICATION_ID
442 and T.DESCRIPTIVE_FLEXFIELD_NAME = B.DESCRIPTIVE_FLEXFIELD_NAME
443 and T.LANGUAGE = L.LANGUAGE_CODE);
444 end ADD_LANGUAGE;
445
446 PROCEDURE load_row
447 (x_application_short_name IN VARCHAR2,
448 x_descriptive_flexfield_name IN VARCHAR2,
449 x_who IN fnd_flex_loader_apis.who_type,
450 x_table_application_short_name IN VARCHAR2,
451 x_application_table_name IN VARCHAR2,
452 x_concatenated_segs_view_name IN VARCHAR2,
453 x_context_required_flag IN VARCHAR2,
454 x_context_column_name IN VARCHAR2,
455 x_context_user_override_flag IN VARCHAR2,
456 x_concatenated_segment_delimit IN VARCHAR2,
457 x_freeze_flex_definition_flag IN VARCHAR2,
458 x_protected_flag IN VARCHAR2,
459 x_default_context_field_name IN VARCHAR2,
460 x_default_context_value IN VARCHAR2,
461 x_context_default_type IN VARCHAR2,
462 x_context_default_value IN VARCHAR2,
463 x_context_override_value_set_n IN VARCHAR2,
464 x_context_runtime_property_fun IN VARCHAR2,
465 x_context_synchronization_flag IN VARCHAR2 DEFAULT NULL,
466 x_title IN VARCHAR2,
467 x_form_context_prompt IN VARCHAR2,
468 x_description IN VARCHAR2)
469 IS
470 l_application_id NUMBER;
471 l_table_application_id NUMBER;
472 l_rowid VARCHAR2(64);
473 l_context_override_value_set_i NUMBER := NULL;
474 BEGIN
475 SELECT application_id
476 INTO l_application_id
477 FROM fnd_application
478 WHERE application_short_name = x_application_short_name;
479
480 SELECT application_id
481 INTO l_table_application_id
482 FROM fnd_application
483 WHERE application_short_name = x_table_application_short_name;
484
485 IF (x_context_override_value_set_n IS NOT NULL) THEN
486 SELECT flex_value_set_id
487 INTO l_context_override_value_set_i
488 FROM fnd_flex_value_sets
489 WHERE flex_value_set_name = x_context_override_value_set_n;
490 END IF;
491
492 BEGIN
493 fnd_descriptive_flexs_pkg.update_row
494 (X_APPLICATION_ID => l_application_id,
495 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
496 X_APPLICATION_TABLE_NAME => x_application_table_name,
497 X_TABLE_APPLICATION_ID => l_table_application_id,
498 X_CONCATENATED_SEGS_VIEW_NAME => x_concatenated_segs_view_name,
499 X_CONTEXT_REQUIRED_FLAG => x_context_required_flag,
500 X_CONTEXT_COLUMN_NAME => x_context_column_name,
501 X_CONTEXT_USER_OVERRIDE_FLAG => x_context_user_override_flag,
502 X_CONCATENATED_SEGMENT_DELIMIT => x_concatenated_segment_delimit,
503 X_FREEZE_FLEX_DEFINITION_FLAG => x_freeze_flex_definition_flag,
504 X_PROTECTED_FLAG => x_protected_flag,
505 X_DEFAULT_CONTEXT_FIELD_NAME => x_default_context_field_name,
506 X_DEFAULT_CONTEXT_VALUE => x_default_context_value,
507 X_CONTEXT_DEFAULT_TYPE => x_context_default_type,
508 X_CONTEXT_DEFAULT_VALUE => x_context_default_value,
509 X_CONTEXT_OVERRIDE_VALUE_SET_I => l_context_override_value_set_i,
510 X_CONTEXT_RUNTIME_PROPERTY_FUN => x_context_runtime_property_fun,
511 X_CONTEXT_SYNCHRONIZATION_FLAG => x_context_synchronization_flag,
512 X_TITLE => x_title,
513 X_FORM_CONTEXT_PROMPT => x_form_context_prompt,
514 X_DESCRIPTION => x_description,
515 X_LAST_UPDATE_DATE => x_who.last_update_date,
516 X_LAST_UPDATED_BY => x_who.last_updated_by,
517 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
518 EXCEPTION
519 WHEN no_data_found THEN
520 fnd_descriptive_flexs_pkg.insert_row
521 (X_ROWID => l_rowid,
522 X_APPLICATION_ID => l_application_id,
523 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
524 X_APPLICATION_TABLE_NAME => x_application_table_name,
525 X_TABLE_APPLICATION_ID => l_table_application_id,
526 X_CONCATENATED_SEGS_VIEW_NAME => x_concatenated_segs_view_name,
527 X_CONTEXT_REQUIRED_FLAG => x_context_required_flag,
528 X_CONTEXT_COLUMN_NAME => x_context_column_name,
529 X_CONTEXT_USER_OVERRIDE_FLAG => x_context_user_override_flag,
530 X_CONCATENATED_SEGMENT_DELIMIT => x_concatenated_segment_delimit,
531 X_FREEZE_FLEX_DEFINITION_FLAG => x_freeze_flex_definition_flag,
532 X_PROTECTED_FLAG => x_protected_flag,
533 X_DEFAULT_CONTEXT_FIELD_NAME => x_default_context_field_name,
534 X_DEFAULT_CONTEXT_VALUE => x_default_context_value,
535 X_CONTEXT_DEFAULT_TYPE => x_context_default_type,
536 X_CONTEXT_DEFAULT_VALUE => x_context_default_value,
537 X_CONTEXT_OVERRIDE_VALUE_SET_I => l_context_override_value_set_i,
538 X_CONTEXT_RUNTIME_PROPERTY_FUN => x_context_runtime_property_fun,
539 X_CONTEXT_SYNCHRONIZATION_FLAG => x_context_synchronization_flag,
540 X_TITLE => x_title,
541 X_FORM_CONTEXT_PROMPT => x_form_context_prompt,
542 X_DESCRIPTION => x_description,
543 X_CREATION_DATE => x_who.creation_date,
544 X_CREATED_BY => x_who.created_by,
545 X_LAST_UPDATE_DATE => x_who.last_update_date,
546 X_LAST_UPDATED_BY => x_who.last_updated_by,
547 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
548 END;
549 END load_row;
550
551 PROCEDURE translate_row
552 (x_application_short_name IN VARCHAR2,
553 x_descriptive_flexfield_name IN VARCHAR2,
554 x_who IN fnd_flex_loader_apis.who_type,
555 x_title IN VARCHAR2,
556 x_form_context_prompt IN VARCHAR2,
557 x_description IN VARCHAR2)
558 IS
559 BEGIN
560 UPDATE fnd_descriptive_flexs_tl SET
561 title = Nvl(x_title, title),
562 form_context_prompt = Nvl(x_form_context_prompt, form_context_prompt),
563 description = Nvl(x_description, description),
564 last_update_date = x_who.last_update_date,
565 last_updated_by = x_who.last_updated_by,
566 last_update_login = x_who.last_update_login,
567 source_lang = userenv('LANG')
568 WHERE application_id = (SELECT application_id
569 FROM fnd_application
570 WHERE application_short_name = x_application_short_name)
571 AND descriptive_flexfield_name = x_descriptive_flexfield_name
572 AND userenv('LANG') in (language, source_lang);
573 END translate_row;
574
575 end FND_DESCRIPTIVE_FLEXS_PKG;