[Home] [Help]
PACKAGE BODY: APPS.FND_ID_FLEX_SEGMENTS_PKG
Source
1 package body FND_ID_FLEX_SEGMENTS_PKG as
2 /* $Header: AFFFSEGB.pls 120.1.12010000.1 2008/07/25 14:14:17 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ID_FLEX_CODE in VARCHAR2,
7 X_ID_FLEX_NUM in NUMBER,
8 X_APPLICATION_COLUMN_NAME in VARCHAR2,
9 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
10 X_SEGMENT_NAME in VARCHAR2,
11 X_SEGMENT_NUM in NUMBER,
12 X_APPLICATION_COLUMN_INDEX_FLA in VARCHAR2,
13 X_ENABLED_FLAG in VARCHAR2,
14 X_REQUIRED_FLAG in VARCHAR2,
15 X_DISPLAY_FLAG in VARCHAR2,
16 X_DISPLAY_SIZE in NUMBER,
17 X_SECURITY_ENABLED_FLAG in VARCHAR2,
18 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
19 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
20 X_FLEX_VALUE_SET_ID in NUMBER,
21 X_RANGE_CODE in VARCHAR2,
22 X_DEFAULT_TYPE in VARCHAR2,
23 X_DEFAULT_VALUE in VARCHAR2,
24 X_RUNTIME_PROPERTY_FUNCTION 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 cursor C is select ROWID from FND_ID_FLEX_SEGMENTS
35 where APPLICATION_ID = X_APPLICATION_ID
36 and ID_FLEX_CODE = X_ID_FLEX_CODE
37 and ID_FLEX_NUM = X_ID_FLEX_NUM
38 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
39 ;
40 begin
41 insert into FND_ID_FLEX_SEGMENTS (
42 ADDITIONAL_WHERE_CLAUSE,
43 APPLICATION_ID,
44 ID_FLEX_CODE,
45 ID_FLEX_NUM,
46 APPLICATION_COLUMN_NAME,
47 SEGMENT_NAME,
48 SEGMENT_NUM,
49 APPLICATION_COLUMN_INDEX_FLAG,
50 ENABLED_FLAG,
51 REQUIRED_FLAG,
52 DISPLAY_FLAG,
53 DISPLAY_SIZE,
54 SECURITY_ENABLED_FLAG,
55 MAXIMUM_DESCRIPTION_LEN,
56 CONCATENATION_DESCRIPTION_LEN,
57 FLEX_VALUE_SET_ID,
58 RANGE_CODE,
59 DEFAULT_TYPE,
60 DEFAULT_VALUE,
61 RUNTIME_PROPERTY_FUNCTION,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_ADDITIONAL_WHERE_CLAUSE,
69 X_APPLICATION_ID,
70 X_ID_FLEX_CODE,
71 X_ID_FLEX_NUM,
72 X_APPLICATION_COLUMN_NAME,
73 X_SEGMENT_NAME,
74 X_SEGMENT_NUM,
75 X_APPLICATION_COLUMN_INDEX_FLA,
76 X_ENABLED_FLAG,
77 X_REQUIRED_FLAG,
78 X_DISPLAY_FLAG,
79 X_DISPLAY_SIZE,
80 X_SECURITY_ENABLED_FLAG,
81 X_MAXIMUM_DESCRIPTION_LEN,
82 X_CONCATENATION_DESCRIPTION_LE,
83 X_FLEX_VALUE_SET_ID,
84 X_RANGE_CODE,
85 X_DEFAULT_TYPE,
86 X_DEFAULT_VALUE,
87 X_RUNTIME_PROPERTY_FUNCTION,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN
93 );
94
95 insert into FND_ID_FLEX_SEGMENTS_TL (
96 APPLICATION_ID,
97 ID_FLEX_CODE,
98 ID_FLEX_NUM,
99 APPLICATION_COLUMN_NAME,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_LOGIN,
105 FORM_LEFT_PROMPT,
106 FORM_ABOVE_PROMPT,
107 DESCRIPTION,
108 LANGUAGE,
109 SOURCE_LANG
110 ) select
111 X_APPLICATION_ID,
112 X_ID_FLEX_CODE,
113 X_ID_FLEX_NUM,
114 X_APPLICATION_COLUMN_NAME,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATED_BY,
117 X_CREATION_DATE,
118 X_CREATED_BY,
119 X_LAST_UPDATE_LOGIN,
120 X_FORM_LEFT_PROMPT,
121 X_FORM_ABOVE_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_ID_FLEX_SEGMENTS_TL T
130 where T.APPLICATION_ID = X_APPLICATION_ID
131 and T.ID_FLEX_CODE = X_ID_FLEX_CODE
132 and T.ID_FLEX_NUM = X_ID_FLEX_NUM
133 and T.APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135
136 open c;
137 fetch c into X_ROWID;
138 if (c%notfound) then
139 close c;
140 raise no_data_found;
141 end if;
142 close c;
143
144 end INSERT_ROW;
145
146 procedure LOCK_ROW (
147 X_APPLICATION_ID in NUMBER,
148 X_ID_FLEX_CODE in VARCHAR2,
149 X_ID_FLEX_NUM in NUMBER,
150 X_APPLICATION_COLUMN_NAME in VARCHAR2,
151 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
152 X_SEGMENT_NAME in VARCHAR2,
153 X_SEGMENT_NUM in NUMBER,
154 X_APPLICATION_COLUMN_INDEX_FLA in VARCHAR2,
155 X_ENABLED_FLAG in VARCHAR2,
156 X_REQUIRED_FLAG in VARCHAR2,
157 X_DISPLAY_FLAG in VARCHAR2,
158 X_DISPLAY_SIZE in NUMBER,
159 X_SECURITY_ENABLED_FLAG in VARCHAR2,
160 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
161 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
162 X_FLEX_VALUE_SET_ID in NUMBER,
163 X_RANGE_CODE in VARCHAR2,
164 X_DEFAULT_TYPE in VARCHAR2,
165 X_DEFAULT_VALUE in VARCHAR2,
166 X_RUNTIME_PROPERTY_FUNCTION in VARCHAR2,
167 X_FORM_LEFT_PROMPT in VARCHAR2,
168 X_FORM_ABOVE_PROMPT in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2
170 ) is
171 cursor c is select
172 ADDITIONAL_WHERE_CLAUSE,
173 SEGMENT_NAME,
174 SEGMENT_NUM,
175 APPLICATION_COLUMN_INDEX_FLAG,
176 ENABLED_FLAG,
177 REQUIRED_FLAG,
178 DISPLAY_FLAG,
179 DISPLAY_SIZE,
180 SECURITY_ENABLED_FLAG,
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 from FND_ID_FLEX_SEGMENTS
189 where APPLICATION_ID = X_APPLICATION_ID
190 and ID_FLEX_CODE = X_ID_FLEX_CODE
191 and ID_FLEX_NUM = X_ID_FLEX_NUM
192 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
193 for update of APPLICATION_ID nowait;
194 recinfo c%rowtype;
195
196 cursor c1 is select
197 FORM_LEFT_PROMPT,
198 FORM_ABOVE_PROMPT,
199 DESCRIPTION,
200 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
201 from FND_ID_FLEX_SEGMENTS_TL
202 where APPLICATION_ID = X_APPLICATION_ID
203 and ID_FLEX_CODE = X_ID_FLEX_CODE
204 and ID_FLEX_NUM = X_ID_FLEX_NUM
205 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
206 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
207 for update of APPLICATION_ID nowait;
208 begin
209 open c;
210 fetch c into recinfo;
211 if (c%notfound) then
212 close c;
213 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
214 app_exception.raise_exception;
215 end if;
216 close c;
217 if ( ((recinfo.ADDITIONAL_WHERE_CLAUSE = X_ADDITIONAL_WHERE_CLAUSE)
218 OR ((recinfo.ADDITIONAL_WHERE_CLAUSE is null) AND (X_ADDITIONAL_WHERE_CLAUSE is null)))
219 AND (recinfo.SEGMENT_NAME = X_SEGMENT_NAME)
220 AND (recinfo.SEGMENT_NUM = X_SEGMENT_NUM)
221 AND (recinfo.APPLICATION_COLUMN_INDEX_FLAG = X_APPLICATION_COLUMN_INDEX_FLA)
222 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
223 AND (recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
224 AND (recinfo.DISPLAY_FLAG = X_DISPLAY_FLAG)
225 AND (recinfo.DISPLAY_SIZE = X_DISPLAY_SIZE)
226 AND (recinfo.SECURITY_ENABLED_FLAG = X_SECURITY_ENABLED_FLAG)
227 AND (recinfo.MAXIMUM_DESCRIPTION_LEN = X_MAXIMUM_DESCRIPTION_LEN)
228 AND (recinfo.CONCATENATION_DESCRIPTION_LEN = X_CONCATENATION_DESCRIPTION_LE)
229 AND ((recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
230 OR ((recinfo.FLEX_VALUE_SET_ID is null) AND (X_FLEX_VALUE_SET_ID is null)))
231 AND ((recinfo.RANGE_CODE = X_RANGE_CODE)
232 OR ((recinfo.RANGE_CODE is null) AND (X_RANGE_CODE is null)))
233 AND ((recinfo.DEFAULT_TYPE = X_DEFAULT_TYPE)
234 OR ((recinfo.DEFAULT_TYPE is null) AND (X_DEFAULT_TYPE is null)))
235 AND ((recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
236 OR ((recinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
237 AND ((recinfo.RUNTIME_PROPERTY_FUNCTION = X_RUNTIME_PROPERTY_FUNCTION)
238 OR ((recinfo.RUNTIME_PROPERTY_FUNCTION is null) AND (X_RUNTIME_PROPERTY_FUNCTION 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_ID_FLEX_CODE in VARCHAR2,
266 X_ID_FLEX_NUM in NUMBER,
267 X_APPLICATION_COLUMN_NAME in VARCHAR2,
268 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
269 X_SEGMENT_NAME in VARCHAR2,
270 X_SEGMENT_NUM in NUMBER,
271 X_APPLICATION_COLUMN_INDEX_FLA in VARCHAR2,
272 X_ENABLED_FLAG in VARCHAR2,
273 X_REQUIRED_FLAG in VARCHAR2,
274 X_DISPLAY_FLAG in VARCHAR2,
275 X_DISPLAY_SIZE in NUMBER,
276 X_SECURITY_ENABLED_FLAG in VARCHAR2,
277 X_MAXIMUM_DESCRIPTION_LEN in NUMBER,
278 X_CONCATENATION_DESCRIPTION_LE in NUMBER,
279 X_FLEX_VALUE_SET_ID in NUMBER,
280 X_RANGE_CODE in VARCHAR2,
281 X_DEFAULT_TYPE in VARCHAR2,
282 X_DEFAULT_VALUE in VARCHAR2,
283 X_RUNTIME_PROPERTY_FUNCTION in VARCHAR2,
284 X_FORM_LEFT_PROMPT in VARCHAR2,
285 X_FORM_ABOVE_PROMPT in VARCHAR2,
286 X_DESCRIPTION in VARCHAR2,
287 X_LAST_UPDATE_DATE in DATE,
288 X_LAST_UPDATED_BY in NUMBER,
289 X_LAST_UPDATE_LOGIN in NUMBER
290 ) is
291 begin
292 update FND_ID_FLEX_SEGMENTS set
293 ADDITIONAL_WHERE_CLAUSE = X_ADDITIONAL_WHERE_CLAUSE,
294 SEGMENT_NAME = X_SEGMENT_NAME,
295 SEGMENT_NUM = X_SEGMENT_NUM,
296 APPLICATION_COLUMN_INDEX_FLAG = X_APPLICATION_COLUMN_INDEX_FLA,
297 ENABLED_FLAG = X_ENABLED_FLAG,
298 REQUIRED_FLAG = X_REQUIRED_FLAG,
299 DISPLAY_FLAG = X_DISPLAY_FLAG,
300 DISPLAY_SIZE = X_DISPLAY_SIZE,
301 SECURITY_ENABLED_FLAG = X_SECURITY_ENABLED_FLAG,
302 MAXIMUM_DESCRIPTION_LEN = X_MAXIMUM_DESCRIPTION_LEN,
303 CONCATENATION_DESCRIPTION_LEN = X_CONCATENATION_DESCRIPTION_LE,
304 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
305 RANGE_CODE = X_RANGE_CODE,
306 DEFAULT_TYPE = X_DEFAULT_TYPE,
307 DEFAULT_VALUE = X_DEFAULT_VALUE,
308 RUNTIME_PROPERTY_FUNCTION = X_RUNTIME_PROPERTY_FUNCTION,
309 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
310 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
312 where APPLICATION_ID = X_APPLICATION_ID
313 and ID_FLEX_CODE = X_ID_FLEX_CODE
314 and ID_FLEX_NUM = X_ID_FLEX_NUM
315 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
316
317 if (sql%notfound) then
318 raise no_data_found;
319 end if;
320
321 update FND_ID_FLEX_SEGMENTS_TL set
322 FORM_LEFT_PROMPT = X_FORM_LEFT_PROMPT,
323 FORM_ABOVE_PROMPT = X_FORM_ABOVE_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 ID_FLEX_CODE = X_ID_FLEX_CODE
331 and ID_FLEX_NUM = X_ID_FLEX_NUM
332 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME
333 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338 end UPDATE_ROW;
339
340 procedure DELETE_ROW (
341 X_APPLICATION_ID in NUMBER,
342 X_ID_FLEX_CODE in VARCHAR2,
343 X_ID_FLEX_NUM in NUMBER,
344 X_APPLICATION_COLUMN_NAME in VARCHAR2
345 ) is
346 begin
347 delete from FND_ID_FLEX_SEGMENTS_TL
348 where APPLICATION_ID = X_APPLICATION_ID
349 and ID_FLEX_CODE = X_ID_FLEX_CODE
350 and ID_FLEX_NUM = X_ID_FLEX_NUM
351 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
352
353 if (sql%notfound) then
354 raise no_data_found;
355 end if;
356
357 delete from FND_ID_FLEX_SEGMENTS
358 where APPLICATION_ID = X_APPLICATION_ID
359 and ID_FLEX_CODE = X_ID_FLEX_CODE
360 and ID_FLEX_NUM = X_ID_FLEX_NUM
361 and APPLICATION_COLUMN_NAME = X_APPLICATION_COLUMN_NAME;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 -- Since we are deleting this segment, we must update
368 -- any CVR rule that uses this segment. We will update
369 -- segment reference to null since it is now deleted.
370 update fnd_flex_validation_rules
371 set ERROR_SEGMENT_COLUMN_NAME=NULL
372 where APPLICATION_ID = X_APPLICATION_ID
373 and ID_FLEX_CODE = X_ID_FLEX_CODE
374 and ID_FLEX_NUM = X_ID_FLEX_NUM
375 and ERROR_SEGMENT_COLUMN_NAME=X_APPLICATION_COLUMN_NAME;
376 if (sql%notfound) then
377 null;
378 end if;
379
380 end DELETE_ROW;
381
382 procedure ADD_LANGUAGE
383 is
384 begin
385 /* Mar/19/03 requested by Ric Ginsberg */
386 /* The following delete and update statements are commented out */
387 /* as a quick workaround to fix the time-consuming table handler issue */
388 /* Eventually we'll need to turn them into a separate fix_language procedure */
389 /*
390
391 delete from FND_ID_FLEX_SEGMENTS_TL T
392 where not exists
393 (select NULL
394 from FND_ID_FLEX_SEGMENTS B
395 where B.APPLICATION_ID = T.APPLICATION_ID
396 and B.ID_FLEX_CODE = T.ID_FLEX_CODE
397 and B.ID_FLEX_NUM = T.ID_FLEX_NUM
398 and B.APPLICATION_COLUMN_NAME = T.APPLICATION_COLUMN_NAME
399 );
400
401 update FND_ID_FLEX_SEGMENTS_TL T set (
402 FORM_LEFT_PROMPT,
403 FORM_ABOVE_PROMPT,
404 DESCRIPTION
405 ) = (select
406 B.FORM_LEFT_PROMPT,
407 B.FORM_ABOVE_PROMPT,
408 B.DESCRIPTION
409 from FND_ID_FLEX_SEGMENTS_TL B
410 where B.APPLICATION_ID = T.APPLICATION_ID
411 and B.ID_FLEX_CODE = T.ID_FLEX_CODE
412 and B.ID_FLEX_NUM = T.ID_FLEX_NUM
413 and B.APPLICATION_COLUMN_NAME = T.APPLICATION_COLUMN_NAME
414 and B.LANGUAGE = T.SOURCE_LANG)
415 where (
416 T.APPLICATION_ID,
417 T.ID_FLEX_CODE,
418 T.ID_FLEX_NUM,
419 T.APPLICATION_COLUMN_NAME,
420 T.LANGUAGE
421 ) in (select
422 SUBT.APPLICATION_ID,
423 SUBT.ID_FLEX_CODE,
424 SUBT.ID_FLEX_NUM,
425 SUBT.APPLICATION_COLUMN_NAME,
426 SUBT.LANGUAGE
427 from FND_ID_FLEX_SEGMENTS_TL SUBB, FND_ID_FLEX_SEGMENTS_TL SUBT
428 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
429 and SUBB.ID_FLEX_CODE = SUBT.ID_FLEX_CODE
430 and SUBB.ID_FLEX_NUM = SUBT.ID_FLEX_NUM
431 and SUBB.APPLICATION_COLUMN_NAME = SUBT.APPLICATION_COLUMN_NAME
432 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
433 and (SUBB.FORM_LEFT_PROMPT <> SUBT.FORM_LEFT_PROMPT
434 or SUBB.FORM_ABOVE_PROMPT <> SUBT.FORM_ABOVE_PROMPT
435 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
436 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
437 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
438 ));
439 */
440
441 insert into FND_ID_FLEX_SEGMENTS_TL (
442 APPLICATION_ID,
443 ID_FLEX_CODE,
444 ID_FLEX_NUM,
445 APPLICATION_COLUMN_NAME,
446 LAST_UPDATE_DATE,
447 LAST_UPDATED_BY,
448 CREATION_DATE,
449 CREATED_BY,
450 LAST_UPDATE_LOGIN,
451 FORM_LEFT_PROMPT,
452 FORM_ABOVE_PROMPT,
453 DESCRIPTION,
454 LANGUAGE,
455 SOURCE_LANG
456 ) select /*+ ORDERED */
457 B.APPLICATION_ID,
458 B.ID_FLEX_CODE,
459 B.ID_FLEX_NUM,
460 B.APPLICATION_COLUMN_NAME,
461 B.LAST_UPDATE_DATE,
462 B.LAST_UPDATED_BY,
463 B.CREATION_DATE,
464 B.CREATED_BY,
465 B.LAST_UPDATE_LOGIN,
466 B.FORM_LEFT_PROMPT,
467 B.FORM_ABOVE_PROMPT,
468 B.DESCRIPTION,
469 L.LANGUAGE_CODE,
470 B.SOURCE_LANG
471 from FND_ID_FLEX_SEGMENTS_TL B, FND_LANGUAGES L
472 where L.INSTALLED_FLAG in ('I', 'B')
473 and B.LANGUAGE = userenv('LANG')
474 and not exists
475 (select NULL
476 from FND_ID_FLEX_SEGMENTS_TL T
477 where T.APPLICATION_ID = B.APPLICATION_ID
478 and T.ID_FLEX_CODE = B.ID_FLEX_CODE
479 and T.ID_FLEX_NUM = B.ID_FLEX_NUM
480 and T.APPLICATION_COLUMN_NAME = B.APPLICATION_COLUMN_NAME
481 and T.LANGUAGE = L.LANGUAGE_CODE);
482 end ADD_LANGUAGE;
483
484 PROCEDURE load_row
485 (x_application_short_name IN VARCHAR2,
486 x_id_flex_code IN VARCHAR2,
487 x_id_flex_structure_code IN VARCHAR2,
488 x_application_column_name IN VARCHAR2,
489 x_who IN fnd_flex_loader_apis.who_type,
490 x_segment_name IN VARCHAR2,
491 x_segment_num IN NUMBER,
492 x_application_column_index_fla IN VARCHAR2,
493 x_enabled_flag IN VARCHAR2,
494 x_required_flag IN VARCHAR2,
495 x_display_flag IN VARCHAR2,
496 x_display_size IN NUMBER,
497 x_security_enabled_flag IN VARCHAR2,
498 x_maximum_description_len IN NUMBER,
499 x_concatenation_description_le IN NUMBER,
500 x_flex_value_set_name IN VARCHAR2,
501 x_range_code IN VARCHAR2,
502 x_default_type IN VARCHAR2,
503 x_default_value IN VARCHAR2,
504 x_runtime_property_function IN VARCHAR2,
505 x_additional_where_clause IN VARCHAR2,
506 x_form_left_prompt IN VARCHAR2,
507 x_form_above_prompt IN VARCHAR2,
508 x_description IN VARCHAR2)
509 IS
510 l_application_id NUMBER;
511 l_id_flex_code fnd_id_flexs.id_flex_code%TYPE;
512 l_id_flex_num NUMBER;
513 l_flex_value_set_id NUMBER := NULL;
514 l_rowid VARCHAR2(64);
515 BEGIN
516 SELECT application_id
517 INTO l_application_id
518 FROM fnd_application
519 WHERE application_short_name = x_application_short_name;
520
521 SELECT id_flex_code
522 INTO l_id_flex_code
523 FROM fnd_id_flexs
524 WHERE application_id = l_application_id
525 AND id_flex_code = x_id_flex_code;
526
527 SELECT id_flex_num
528 INTO l_id_flex_num
529 FROM fnd_id_flex_structures
530 WHERE application_id = l_application_id
531 AND id_flex_code = l_id_flex_code
532 AND id_flex_structure_code = x_id_flex_structure_code;
533
534 IF (x_flex_value_set_name IS NOT NULL) THEN
535 SELECT flex_value_set_id
536 INTO l_flex_value_set_id
537 FROM fnd_flex_value_sets
538 WHERE flex_value_set_name = x_flex_value_set_name;
539 END IF;
540
541 BEGIN
542 fnd_id_flex_segments_pkg.update_row
543 (X_APPLICATION_ID => l_application_id,
544 X_ID_FLEX_CODE => l_id_flex_code,
545 X_ID_FLEX_NUM => l_id_flex_num,
546 X_APPLICATION_COLUMN_NAME => x_application_column_name,
547 X_SEGMENT_NAME => x_segment_name,
548 X_SEGMENT_NUM => x_segment_num,
549 X_APPLICATION_COLUMN_INDEX_FLA => x_application_column_index_fla,
550 X_ENABLED_FLAG => x_enabled_flag,
551 X_REQUIRED_FLAG => x_required_flag,
552 X_DISPLAY_FLAG => x_display_flag,
553 X_DISPLAY_SIZE => x_display_size,
554 X_SECURITY_ENABLED_FLAG => x_security_enabled_flag,
555 X_MAXIMUM_DESCRIPTION_LEN => x_maximum_description_len,
556 X_CONCATENATION_DESCRIPTION_LE => x_concatenation_description_le,
557 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
558 X_RANGE_CODE => x_range_code,
559 X_DEFAULT_TYPE => x_default_type,
560 X_DEFAULT_VALUE => x_default_value,
561 X_RUNTIME_PROPERTY_FUNCTION => x_runtime_property_function,
562 X_ADDITIONAL_WHERE_CLAUSE => x_additional_where_clause,
563 X_FORM_LEFT_PROMPT => x_form_left_prompt,
564 X_FORM_ABOVE_PROMPT => x_form_above_prompt,
565 X_DESCRIPTION => x_description,
566 X_LAST_UPDATE_DATE => x_who.last_update_date,
567 X_LAST_UPDATED_BY => x_who.last_updated_by,
568 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
569 EXCEPTION
570 WHEN no_data_found THEN
571 fnd_id_flex_segments_pkg.insert_row
572 (X_ROWID => l_rowid,
573 X_APPLICATION_ID => l_application_id,
574 X_ID_FLEX_CODE => l_id_flex_code,
575 X_ID_FLEX_NUM => l_id_flex_num,
576 X_APPLICATION_COLUMN_NAME => x_application_column_name,
577 X_SEGMENT_NAME => x_segment_name,
578 X_SEGMENT_NUM => x_segment_num,
579 X_APPLICATION_COLUMN_INDEX_FLA => x_application_column_index_fla,
580 X_ENABLED_FLAG => x_enabled_flag,
581 X_REQUIRED_FLAG => x_required_flag,
582 X_DISPLAY_FLAG => x_display_flag,
583 X_DISPLAY_SIZE => x_display_size,
584 X_SECURITY_ENABLED_FLAG => x_security_enabled_flag,
585 X_MAXIMUM_DESCRIPTION_LEN => x_maximum_description_len,
586 X_CONCATENATION_DESCRIPTION_LE => x_concatenation_description_le,
587 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
588 X_RANGE_CODE => x_range_code,
589 X_DEFAULT_TYPE => x_default_type,
590 X_DEFAULT_VALUE => x_default_value,
591 X_RUNTIME_PROPERTY_FUNCTION => x_runtime_property_function,
592 X_ADDITIONAL_WHERE_CLAUSE => x_additional_where_clause,
593 X_FORM_LEFT_PROMPT => x_form_left_prompt,
594 X_FORM_ABOVE_PROMPT => x_form_above_prompt,
595 X_DESCRIPTION => x_description,
596 X_CREATION_DATE => x_who.creation_date,
597 X_CREATED_BY => x_who.created_by,
598 X_LAST_UPDATE_DATE => x_who.last_update_date,
599 X_LAST_UPDATED_BY => x_who.last_updated_by,
600 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
601 END;
602 END load_row;
603
604 PROCEDURE translate_row
605 (x_application_short_name IN VARCHAR2,
606 x_id_flex_code IN VARCHAR2,
607 x_id_flex_structure_code IN VARCHAR2,
608 x_application_column_name IN VARCHAR2,
609 x_who IN fnd_flex_loader_apis.who_type,
610 x_form_left_prompt IN VARCHAR2,
611 x_form_above_prompt IN VARCHAR2,
612 x_description IN VARCHAR2)
613 IS
614 BEGIN
615 UPDATE fnd_id_flex_segments_tl SET
616 form_left_prompt = Nvl(x_form_left_prompt, form_left_prompt),
617 form_above_prompt = Nvl(x_form_above_prompt, form_above_prompt),
618 description = Nvl(x_description, description),
619 last_update_date = x_who.last_update_date,
620 last_updated_by = x_who.last_updated_by,
621 last_update_login = x_who.last_update_login,
622 source_lang = userenv('LANG')
623 WHERE ((application_id, id_flex_code, id_flex_num) =
624 (SELECT application_id, id_flex_code, id_flex_num
625 FROM fnd_id_flex_structures
626 WHERE (application_id =
627 (SELECT application_id
628 FROM fnd_application
629 WHERE application_short_name = x_application_short_name))
630 AND id_flex_code = x_id_flex_code
631 AND id_flex_structure_code = x_id_flex_structure_code))
632 AND application_column_name = x_application_column_name
633 AND userenv('LANG') in (language, source_lang);
634 END translate_row;
635
636 end FND_ID_FLEX_SEGMENTS_PKG;