[Home] [Help]
PACKAGE BODY: APPS.FND_DESCR_FLEX_COL_USAGE_PKG
Source
1 package body FND_DESCR_FLEX_COL_USAGE_PKG as
2 /* $Header: AFFFDFSB.pls 120.2.12010000.1 2008/07/25 14:13:51 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_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
9 X_APPLICATION_COLUMN_NAME in VARCHAR2,
10 X_END_USER_COLUMN_NAME in VARCHAR2,
11 X_COLUMN_SEQ_NUM in NUMBER,
12 X_ENABLED_FLAG in VARCHAR2,
13 X_REQUIRED_FLAG in VARCHAR2,
14 X_SECURITY_ENABLED_FLAG in VARCHAR2,
15 X_DISPLAY_FLAG in VARCHAR2,
16 X_DISPLAY_SIZE in NUMBER,
17 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
18 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
19 X_FLEX_VALUE_SET_ID in NUMBER,
20 X_RANGE_CODE in VARCHAR2,
21 X_DEFAULT_TYPE in VARCHAR2,
22 X_DEFAULT_VALUE in VARCHAR2,
23 X_RUNTIME_PROPERTY_FUNCTION in VARCHAR2,
24 X_SRW_PARAM in VARCHAR2,
25 X_FORM_LEFT_PROMPT in VARCHAR2,
26 X_FORM_ABOVE_PROMPT in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 l_application_column_name VARCHAR2(30);
35
36 cursor C is select ROWID from FND_DESCR_FLEX_COLUMN_USAGES
37 where APPLICATION_ID = X_APPLICATION_ID
38 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
39 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
40 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
41 ;
42
43 begin
44 /* Bug 1732116 - Remove row level delete of report parameters from here. */
45
46 insert into FND_DESCR_FLEX_COLUMN_USAGES (
47 APPLICATION_ID,
48 DESCRIPTIVE_FLEXFIELD_NAME,
49 DESCRIPTIVE_FLEX_CONTEXT_CODE,
50 APPLICATION_COLUMN_NAME,
51 END_USER_COLUMN_NAME,
52 COLUMN_SEQ_NUM,
53 ENABLED_FLAG,
54 REQUIRED_FLAG,
55 SECURITY_ENABLED_FLAG,
56 DISPLAY_FLAG,
57 DISPLAY_SIZE,
58 MAXIMUM_DESCRIPTION_LEN,
59 CONCATENATION_DESCRIPTION_LEN,
60 FLEX_VALUE_SET_ID,
61 RANGE_CODE,
62 DEFAULT_TYPE,
63 DEFAULT_VALUE,
64 RUNTIME_PROPERTY_FUNCTION,
65 SRW_PARAM,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_LOGIN
71 ) values (
72 X_APPLICATION_ID,
73 X_DESCRIPTIVE_FLEXFIELD_NAME,
74 X_DESCRIPTIVE_FLEX_CONTEXT_COD,
75 X_APPLICATION_COLUMN_NAME,
76 X_END_USER_COLUMN_NAME,
77 X_COLUMN_SEQ_NUM,
78 X_ENABLED_FLAG,
79 X_REQUIRED_FLAG,
80 X_SECURITY_ENABLED_FLAG,
81 X_DISPLAY_FLAG,
82 X_DISPLAY_SIZE,
83 X_MAXIMUM_DESCRIPTION_LEN,
84 X_CONCATENATION_DESCRIPTION_LE,
85 X_FLEX_VALUE_SET_ID,
86 X_RANGE_CODE,
87 X_DEFAULT_TYPE,
88 X_DEFAULT_VALUE,
89 X_RUNTIME_PROPERTY_FUNCTION,
90 X_SRW_PARAM,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN
96 );
97
98 insert into FND_DESCR_FLEX_COL_USAGE_TL (
99 APPLICATION_ID,
100 DESCRIPTIVE_FLEXFIELD_NAME,
101 DESCRIPTIVE_FLEX_CONTEXT_CODE,
102 APPLICATION_COLUMN_NAME,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 CREATION_DATE,
106 CREATED_BY,
107 LAST_UPDATE_LOGIN,
108 FORM_LEFT_PROMPT,
109 FORM_ABOVE_PROMPT,
110 DESCRIPTION,
111 LANGUAGE,
112 SOURCE_LANG
113 ) select
114 X_APPLICATION_ID,
115 X_DESCRIPTIVE_FLEXFIELD_NAME,
116 X_DESCRIPTIVE_FLEX_CONTEXT_COD,
117 X_APPLICATION_COLUMN_NAME,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_CREATION_DATE,
121 X_CREATED_BY,
122 X_LAST_UPDATE_LOGIN,
123 X_FORM_LEFT_PROMPT,
124 X_FORM_ABOVE_PROMPT,
125 X_DESCRIPTION,
126 L.LANGUAGE_CODE,
127 userenv('LANG')
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from FND_DESCR_FLEX_COL_USAGE_TL T
133 where T.APPLICATION_ID = X_APPLICATION_ID
134 and T.DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
135 and T.DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
136 and T.APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
137 and T.LANGUAGE = L.LANGUAGE_CODE);
138
139 open c;
140 fetch c into X_ROWID;
141 if (c%notfound) then
142 close c;
143 raise no_data_found;
144 end if;
145 close c;
146
147 end INSERT_ROW;
148
149 procedure LOCK_ROW (
150 X_APPLICATION_ID in NUMBER,
151 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
152 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
153 X_APPLICATION_COLUMN_NAME in VARCHAR2,
154 X_END_USER_COLUMN_NAME in VARCHAR2,
155 X_COLUMN_SEQ_NUM in NUMBER,
156 X_ENABLED_FLAG in VARCHAR2,
157 X_REQUIRED_FLAG in VARCHAR2,
158 X_SECURITY_ENABLED_FLAG in VARCHAR2,
159 X_DISPLAY_FLAG in VARCHAR2,
160 X_DISPLAY_SIZE in NUMBER,
161 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
162 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
163 X_FLEX_VALUE_SET_ID in NUMBER,
164 X_RANGE_CODE in VARCHAR2,
165 X_DEFAULT_TYPE in VARCHAR2,
166 X_DEFAULT_VALUE in VARCHAR2,
167 X_RUNTIME_PROPERTY_FUNCTION in VARCHAR2,
168 X_SRW_PARAM in VARCHAR2,
169 X_FORM_LEFT_PROMPT in VARCHAR2,
170 X_FORM_ABOVE_PROMPT in VARCHAR2,
171 X_DESCRIPTION in VARCHAR2
172 ) is
173 cursor c is select
174 END_USER_COLUMN_NAME,
175 COLUMN_SEQ_NUM,
176 ENABLED_FLAG,
177 REQUIRED_FLAG,
178 SECURITY_ENABLED_FLAG,
179 DISPLAY_FLAG,
180 DISPLAY_SIZE,
181 MAXIMUM_DESCRIPTION_LEN,
182 CONCATENATION_DESCRIPTION_LEN,
183 FLEX_VALUE_SET_ID,
184 RANGE_CODE,
185 DEFAULT_TYPE,
186 DEFAULT_VALUE,
187 RUNTIME_PROPERTY_FUNCTION,
188 SRW_PARAM
189 from FND_DESCR_FLEX_COLUMN_USAGES
190 where APPLICATION_ID = X_APPLICATION_ID
191 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
192 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
193 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
194 for update of APPLICATION_ID nowait;
195 recinfo c%rowtype;
196
197 cursor c1 is select
198 FORM_LEFT_PROMPT,
199 FORM_ABOVE_PROMPT,
200 DESCRIPTION,
201 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
202 from FND_DESCR_FLEX_COL_USAGE_TL
203 where APPLICATION_ID = X_APPLICATION_ID
204 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
205 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
206 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
207 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
208 for update of APPLICATION_ID nowait;
209 begin
210 open c;
211 fetch c into recinfo;
212 if (c%notfound) then
213 close c;
214 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
215 app_exception.raise_exception;
216 end if;
217 close c;
218 if ( (recinfo.END_USER_COLUMN_NAME = X_END_USER_COLUMN_NAME)
219 AND (recinfo.COLUMN_SEQ_NUM = X_COLUMN_SEQ_NUM)
220 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
221 AND (recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
222 AND (recinfo.SECURITY_ENABLED_FLAG = X_SECURITY_ENABLED_FLAG)
223 AND (recinfo.DISPLAY_FLAG = X_DISPLAY_FLAG)
224 AND (recinfo.DISPLAY_SIZE = X_DISPLAY_SIZE)
225 AND (recinfo.MAXIMUM_DESCRIPTION_LEN = X_MAXIMUM_DESCRIPTION_LEN)
226 AND (recinfo.CONCATENATION_DESCRIPTION_LEN = X_CONCATENATION_DESCRIPTION_LE)
227 AND ((recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
228 OR ((recinfo.FLEX_VALUE_SET_ID is null) AND (X_FLEX_VALUE_SET_ID is null)))
229 AND ((recinfo.RANGE_CODE = X_RANGE_CODE)
230 OR ((recinfo.RANGE_CODE is null) AND (X_RANGE_CODE is null)))
231 AND ((recinfo.DEFAULT_TYPE = X_DEFAULT_TYPE)
232 OR ((recinfo.DEFAULT_TYPE is null) AND (X_DEFAULT_TYPE is null)))
233 AND ((recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
234 OR ((recinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
235 AND ((recinfo.RUNTIME_PROPERTY_FUNCTION = X_RUNTIME_PROPERTY_FUNCTION)
236 OR ((recinfo.RUNTIME_PROPERTY_FUNCTION is null) AND (X_RUNTIME_PROPERTY_FUNCTION is null)))
237 AND ((recinfo.SRW_PARAM = X_SRW_PARAM)
238 OR ((recinfo.SRW_PARAM is null) AND (X_SRW_PARAM is null)))
239 ) then
240 null;
241 else
242 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
243 app_exception.raise_exception;
244 end if;
245
246 for tlinfo in c1 loop
247 if (tlinfo.BASELANG = 'Y') then
248 if ( (tlinfo.FORM_LEFT_PROMPT = X_FORM_LEFT_PROMPT)
249 AND (tlinfo.FORM_ABOVE_PROMPT = X_FORM_ABOVE_PROMPT)
250 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
251 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
252 ) then
253 null;
254 else
255 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
256 app_exception.raise_exception;
257 end if;
258 end if;
259 end loop;
260 return;
261 end LOCK_ROW;
262
263 procedure UPDATE_ROW (
264 X_APPLICATION_ID in NUMBER,
265 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
266 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
267 X_APPLICATION_COLUMN_NAME in VARCHAR2,
268 X_END_USER_COLUMN_NAME in VARCHAR2,
269 X_COLUMN_SEQ_NUM in NUMBER,
270 X_ENABLED_FLAG in VARCHAR2,
271 X_REQUIRED_FLAG in VARCHAR2,
272 X_SECURITY_ENABLED_FLAG in VARCHAR2,
273 X_DISPLAY_FLAG in VARCHAR2,
274 X_DISPLAY_SIZE in NUMBER,
275 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
276 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
277 X_FLEX_VALUE_SET_ID in NUMBER,
278 X_RANGE_CODE in VARCHAR2,
279 X_DEFAULT_TYPE in VARCHAR2,
280 X_DEFAULT_VALUE in VARCHAR2,
281 X_RUNTIME_PROPERTY_FUNCTION in VARCHAR2,
282 X_SRW_PARAM in VARCHAR2,
283 X_FORM_LEFT_PROMPT in VARCHAR2,
284 X_FORM_ABOVE_PROMPT in VARCHAR2,
285 X_DESCRIPTION in VARCHAR2,
286 X_LAST_UPDATE_DATE in DATE,
287 X_LAST_UPDATED_BY in NUMBER,
288 X_LAST_UPDATE_LOGIN in NUMBER
289 ) is
290 begin
291 update FND_DESCR_FLEX_COLUMN_USAGES set
292 END_USER_COLUMN_NAME = X_END_USER_COLUMN_NAME,
293 COLUMN_SEQ_NUM = X_COLUMN_SEQ_NUM,
294 ENABLED_FLAG = X_ENABLED_FLAG,
295 REQUIRED_FLAG = X_REQUIRED_FLAG,
296 SECURITY_ENABLED_FLAG = X_SECURITY_ENABLED_FLAG,
297 DISPLAY_FLAG = X_DISPLAY_FLAG,
298 DISPLAY_SIZE = X_DISPLAY_SIZE,
299 MAXIMUM_DESCRIPTION_LEN = X_MAXIMUM_DESCRIPTION_LEN,
300 CONCATENATION_DESCRIPTION_LEN = X_CONCATENATION_DESCRIPTION_LE,
301 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
302 RANGE_CODE = X_RANGE_CODE,
303 DEFAULT_TYPE = X_DEFAULT_TYPE,
304 DEFAULT_VALUE = X_DEFAULT_VALUE,
305 RUNTIME_PROPERTY_FUNCTION = X_RUNTIME_PROPERTY_FUNCTION,
306 SRW_PARAM = X_SRW_PARAM,
307 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
308 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
310 where APPLICATION_ID = X_APPLICATION_ID
311 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
312 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
313 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
314
315 if (sql%notfound) then
316 raise no_data_found;
317 end if;
318
319 update FND_DESCR_FLEX_COL_USAGE_TL set
320 FORM_LEFT_PROMPT = X_FORM_LEFT_PROMPT,
321 FORM_ABOVE_PROMPT = X_FORM_ABOVE_PROMPT,
322 DESCRIPTION = X_DESCRIPTION,
323 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
324 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
325 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
326 SOURCE_LANG = userenv('LANG')
327 where APPLICATION_ID = X_APPLICATION_ID
328 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
329 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
330 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_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 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
342 X_APPLICATION_COLUMN_NAME in VARCHAR2
343 ) is
344 begin
345 delete from FND_DESCR_FLEX_COL_USAGE_TL
346 where APPLICATION_ID = X_APPLICATION_ID
347 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
348 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
349 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354
355 delete from FND_DESCR_FLEX_COLUMN_USAGES
356 where APPLICATION_ID = X_APPLICATION_ID
357 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
358 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
359 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
360
361 if (sql%notfound) then
362 raise no_data_found;
363 end if;
364 end DELETE_ROW;
365
366 procedure ADD_LANGUAGE
367 is
368 begin
369
370 /* Mar/19/03 requested by Ric Ginsberg */
371 /* The following delete and update statements are commented out */
372 /* as a quick workaround to fix the time-consuming table handler issue */
373 /* Eventually we'll need to turn them into a separate fix_language procedure */
374 /*
375 delete from FND_DESCR_FLEX_COL_USAGE_TL T
376 where not exists
377 (select NULL
378 from FND_DESCR_FLEX_COLUMN_USAGES B
379 where B.APPLICATION_ID = T.APPLICATION_ID
380 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
381 and B.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
382 and B.APPLICATION_COLUMN_NAME = T.APPLICATION_COLUMN_NAME
383 );
384
385 update FND_DESCR_FLEX_COL_USAGE_TL T set (
386 FORM_LEFT_PROMPT,
387 FORM_ABOVE_PROMPT,
388 DESCRIPTION
389 ) = (select
390 B.FORM_LEFT_PROMPT,
391 B.FORM_ABOVE_PROMPT,
392 B.DESCRIPTION
393 from FND_DESCR_FLEX_COL_USAGE_TL B
394 where B.APPLICATION_ID = T.APPLICATION_ID
395 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
396 and B.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
397 and B.APPLICATION_COLUMN_NAME = T.APPLICATION_COLUMN_NAME
398 and B.LANGUAGE = T.SOURCE_LANG)
399 where (
400 T.APPLICATION_ID,
401 T.DESCRIPTIVE_FLEXFIELD_NAME,
405 ) in (select
402 T.DESCRIPTIVE_FLEX_CONTEXT_CODE,
403 T.APPLICATION_COLUMN_NAME,
404 T.LANGUAGE
406 SUBT.APPLICATION_ID,
407 SUBT.DESCRIPTIVE_FLEXFIELD_NAME,
408 SUBT.DESCRIPTIVE_FLEX_CONTEXT_CODE,
409 SUBT.APPLICATION_COLUMN_NAME,
410 SUBT.LANGUAGE
411 from FND_DESCR_FLEX_COL_USAGE_TL SUBB, FND_DESCR_FLEX_COL_USAGE_TL SUBT
412 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
413 and SUBB.DESCRIPTIVE_FLEXFIELD_NAME = SUBT.DESCRIPTIVE_FLEXFIELD_NAME
414 and SUBB.DESCRIPTIVE_FLEX_CONTEXT_CODE = SUBT.DESCRIPTIVE_FLEX_CONTEXT_CODE
415 and SUBB.APPLICATION_COLUMN_NAME = SUBT.APPLICATION_COLUMN_NAME
416 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
417 and (SUBB.FORM_LEFT_PROMPT <> SUBT.FORM_LEFT_PROMPT
418 or SUBB.FORM_ABOVE_PROMPT <> SUBT.FORM_ABOVE_PROMPT
419 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
420 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
421 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
422 ));
423 */
424
425 insert /*+ append parallel(tt) */ into FND_DESCR_FLEX_COL_USAGE_TL tt (
426 APPLICATION_ID,
427 DESCRIPTIVE_FLEXFIELD_NAME,
428 DESCRIPTIVE_FLEX_CONTEXT_CODE,
429 APPLICATION_COLUMN_NAME,
430 LAST_UPDATE_DATE,
431 LAST_UPDATED_BY,
432 CREATION_DATE,
433 CREATED_BY,
434 LAST_UPDATE_LOGIN,
435 FORM_LEFT_PROMPT,
436 FORM_ABOVE_PROMPT,
437 DESCRIPTION,
438 LANGUAGE,
439 SOURCE_LANG
440 )
441 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
442 (select /*+ no_merge ordered parallel(b) */
443 B.APPLICATION_ID,
444 B.DESCRIPTIVE_FLEXFIELD_NAME,
445 B.DESCRIPTIVE_FLEX_CONTEXT_CODE,
446 B.APPLICATION_COLUMN_NAME,
447 B.LAST_UPDATE_DATE,
448 B.LAST_UPDATED_BY,
449 B.CREATION_DATE,
450 B.CREATED_BY,
451 B.LAST_UPDATE_LOGIN,
452 B.FORM_LEFT_PROMPT,
453 B.FORM_ABOVE_PROMPT,
454 B.DESCRIPTION,
455 L.LANGUAGE_CODE,
456 B.SOURCE_LANG
457 from FND_DESCR_FLEX_COL_USAGE_TL B, FND_LANGUAGES L
458 where L.INSTALLED_FLAG in ('I', 'B')
459 and B.LANGUAGE = userenv('LANG')
460 ) v, FND_DESCR_FLEX_COL_USAGE_TL t
461 where t.application_id(+) = v.application_id
462 and t.descriptive_flexfield_name(+) = v.descriptive_flexfield_name
463 and t.descriptive_flex_context_code(+) = v.descriptive_flex_context_code
464 and t.application_column_name(+) = v.application_column_name
465 and t.language(+) = v.language_code
466 and t.application_id is NULL
467 and t.descriptive_flexfield_name is NULL
468 and t.descriptive_flex_context_code is NULL
469 and t.application_column_name is NULL;
470
471 end ADD_LANGUAGE;
472
473 PROCEDURE load_row
474 (x_application_short_name IN VARCHAR2,
475 x_descriptive_flexfield_name IN VARCHAR2,
476 x_descriptive_flex_context_cod IN VARCHAR2,
477 x_application_column_name IN VARCHAR2,
478 x_who IN fnd_flex_loader_apis.who_type,
479 x_end_user_column_name IN VARCHAR2,
480 x_column_seq_num IN NUMBER,
481 x_enabled_flag IN VARCHAR2,
482 x_required_flag IN VARCHAR2,
483 x_security_enabled_flag IN VARCHAR2,
484 x_display_flag IN VARCHAR2,
485 x_display_size IN NUMBER,
486 x_maximum_description_len IN NUMBER,
487 x_concatenation_description_le IN NUMBER,
488 x_flex_value_set_name IN VARCHAR2,
489 x_range_code IN VARCHAR2,
490 x_default_type IN VARCHAR2,
491 x_default_value IN VARCHAR2,
492 x_runtime_property_function IN VARCHAR2,
493 x_srw_param IN VARCHAR2,
494 x_form_left_prompt IN VARCHAR2,
495 x_form_above_prompt IN VARCHAR2,
496 x_description IN VARCHAR2)
497 IS
498 l_application_id NUMBER;
499 l_flex_value_set_id NUMBER := NULL;
500 l_vc2 VARCHAR2(2000);
501 l_rowid VARCHAR2(64);
502 BEGIN
503 SELECT application_id
504 INTO l_application_id
505 FROM fnd_application
506 WHERE application_short_name = x_application_short_name;
507
508 SELECT descriptive_flexfield_name
509 INTO l_vc2
510 FROM fnd_descriptive_flexs
511 WHERE application_id = l_application_id
512 AND descriptive_flexfield_name = x_descriptive_flexfield_name;
513
514 SELECT descriptive_flex_context_code
515 INTO l_vc2
516 FROM fnd_descr_flex_contexts
517 WHERE application_id = l_application_id
518 AND descriptive_flexfield_name = x_descriptive_flexfield_name
519 AND descriptive_flex_context_code = x_descriptive_flex_context_cod;
520
521 IF (x_flex_value_set_name IS NOT NULL) THEN
522 SELECT flex_value_set_id
523 INTO l_flex_value_set_id
524 FROM fnd_flex_value_sets
525 WHERE flex_value_set_name = x_flex_value_set_name;
526 END IF;
527
528 BEGIN
529 fnd_descr_flex_col_usage_pkg.update_row
530 (X_APPLICATION_ID => l_application_id,
534 X_END_USER_COLUMN_NAME => x_end_user_column_name,
531 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
532 X_DESCRIPTIVE_FLEX_CONTEXT_COD => x_descriptive_flex_context_cod,
533 X_APPLICATION_COLUMN_NAME => x_application_column_name,
535 X_COLUMN_SEQ_NUM => x_column_seq_num,
536 X_ENABLED_FLAG => x_enabled_flag,
537 X_REQUIRED_FLAG => x_required_flag,
538 X_SECURITY_ENABLED_FLAG => x_security_enabled_flag,
539 X_DISPLAY_FLAG => x_display_flag,
540 X_DISPLAY_SIZE => x_display_size,
541 X_MAXIMUM_DESCRIPTION_LEN => x_maximum_description_len,
542 X_CONCATENATION_DESCRIPTION_LE => x_concatenation_description_le,
543 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
544 X_RANGE_CODE => x_range_code,
545 X_DEFAULT_TYPE => x_default_type,
546 X_DEFAULT_VALUE => x_default_value,
547 X_RUNTIME_PROPERTY_FUNCTION => x_runtime_property_function,
548 X_SRW_PARAM => x_srw_param,
549 X_FORM_LEFT_PROMPT => x_form_left_prompt,
550 X_FORM_ABOVE_PROMPT => x_form_above_prompt,
551 X_DESCRIPTION => x_description,
552 X_LAST_UPDATE_DATE => x_who.last_update_date,
553 X_LAST_UPDATED_BY => x_who.last_updated_by,
554 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
555 EXCEPTION
556 WHEN no_data_found THEN
557 fnd_descr_flex_col_usage_pkg.insert_row
558 (X_ROWID => l_rowid,
559 X_APPLICATION_ID => l_application_id,
560 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
561 X_DESCRIPTIVE_FLEX_CONTEXT_COD => x_descriptive_flex_context_cod,
562 X_APPLICATION_COLUMN_NAME => x_application_column_name,
563 X_END_USER_COLUMN_NAME => x_end_user_column_name,
564 X_COLUMN_SEQ_NUM => x_column_seq_num,
565 X_ENABLED_FLAG => x_enabled_flag,
566 X_REQUIRED_FLAG => x_required_flag,
567 X_SECURITY_ENABLED_FLAG => x_security_enabled_flag,
568 X_DISPLAY_FLAG => x_display_flag,
569 X_DISPLAY_SIZE => x_display_size,
570 X_MAXIMUM_DESCRIPTION_LEN => x_maximum_description_len,
571 X_CONCATENATION_DESCRIPTION_LE => x_concatenation_description_le,
572 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
573 X_RANGE_CODE => x_range_code,
574 X_DEFAULT_TYPE => x_default_type,
575 X_DEFAULT_VALUE => x_default_value,
576 X_RUNTIME_PROPERTY_FUNCTION => x_runtime_property_function,
577 X_SRW_PARAM => x_srw_param,
578 X_FORM_LEFT_PROMPT => x_form_left_prompt,
579 X_FORM_ABOVE_PROMPT => x_form_above_prompt,
580 X_DESCRIPTION => x_description,
581 X_CREATION_DATE => x_who.creation_date,
582 X_CREATED_BY => x_who.created_by,
583 X_LAST_UPDATE_DATE => x_who.last_update_date,
584 X_LAST_UPDATED_BY => x_who.last_updated_by,
585 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
586 END;
587 END load_row;
588
589 PROCEDURE translate_row
590 (x_application_short_name IN VARCHAR2,
591 x_descriptive_flexfield_name IN VARCHAR2,
592 x_descriptive_flex_context_cod IN VARCHAR2,
593 x_application_column_name IN VARCHAR2,
594 x_who IN fnd_flex_loader_apis.who_type,
595 x_form_left_prompt IN VARCHAR2,
596 x_form_above_prompt IN VARCHAR2,
597 x_description IN VARCHAR2)
598 IS
599 BEGIN
600 UPDATE fnd_descr_flex_col_usage_tl SET
601 form_left_prompt = Nvl(x_form_left_prompt, form_left_prompt),
602 form_above_prompt = Nvl(x_form_above_prompt, form_above_prompt),
603 description = Nvl(x_description, description),
604 last_update_date = x_who.last_update_date,
605 last_updated_by = x_who.last_updated_by,
606 last_update_login = x_who.last_update_login,
607 source_lang = userenv('LANG')
608 WHERE application_id = (SELECT application_id
609 FROM fnd_application
610 WHERE application_short_name = x_application_short_name)
611 AND descriptive_flexfield_name = x_descriptive_flexfield_name
612 AND descriptive_flex_context_code = x_descriptive_flex_context_cod
613 AND application_column_name = x_application_column_name
614 AND userenv('LANG') in (language, source_lang);
615 END translate_row;
616
617 end FND_DESCR_FLEX_COL_USAGE_PKG;