[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_VALUES_PKG
Source
1 package body FND_FLEX_VALUES_PKG as
2 /* $Header: AFFFVLSB.pls 120.5 2010/08/20 09:30:04 tebarnes ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_FLEX_VALUE_ID in NUMBER,
7 X_ATTRIBUTE_SORT_ORDER in NUMBER,
8 X_FLEX_VALUE_SET_ID in NUMBER,
9 X_FLEX_VALUE in VARCHAR2,
10 X_ENABLED_FLAG in VARCHAR2,
11 X_SUMMARY_FLAG in VARCHAR2,
12 X_START_DATE_ACTIVE in DATE,
13 X_END_DATE_ACTIVE in DATE,
14 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
15 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
16 X_STRUCTURED_HIERARCHY_LEVEL in NUMBER,
17 X_HIERARCHY_LEVEL in VARCHAR2,
18 X_COMPILED_VALUE_ATTRIBUTES in VARCHAR2,
19 X_VALUE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 X_ATTRIBUTE16 in VARCHAR2,
36 X_ATTRIBUTE17 in VARCHAR2,
37 X_ATTRIBUTE18 in VARCHAR2,
38 X_ATTRIBUTE19 in VARCHAR2,
39 X_ATTRIBUTE20 in VARCHAR2,
40 X_ATTRIBUTE21 in VARCHAR2,
41 X_ATTRIBUTE22 in VARCHAR2,
42 X_ATTRIBUTE23 in VARCHAR2,
43 X_ATTRIBUTE24 in VARCHAR2,
44 X_ATTRIBUTE25 in VARCHAR2,
45 X_ATTRIBUTE26 in VARCHAR2,
46 X_ATTRIBUTE27 in VARCHAR2,
47 X_ATTRIBUTE28 in VARCHAR2,
48 X_ATTRIBUTE29 in VARCHAR2,
49 X_ATTRIBUTE30 in VARCHAR2,
50 X_ATTRIBUTE31 in VARCHAR2,
51 X_ATTRIBUTE32 in VARCHAR2,
52 X_ATTRIBUTE33 in VARCHAR2,
53 X_ATTRIBUTE34 in VARCHAR2,
54 X_ATTRIBUTE35 in VARCHAR2,
55 X_ATTRIBUTE36 in VARCHAR2,
56 X_ATTRIBUTE37 in VARCHAR2,
57 X_ATTRIBUTE38 in VARCHAR2,
58 X_ATTRIBUTE39 in VARCHAR2,
59 X_ATTRIBUTE40 in VARCHAR2,
60 X_ATTRIBUTE41 in VARCHAR2,
61 X_ATTRIBUTE42 in VARCHAR2,
62 X_ATTRIBUTE43 in VARCHAR2,
63 X_ATTRIBUTE44 in VARCHAR2,
64 X_ATTRIBUTE45 in VARCHAR2,
65 X_ATTRIBUTE46 in VARCHAR2,
66 X_ATTRIBUTE47 in VARCHAR2,
67 X_ATTRIBUTE48 in VARCHAR2,
68 X_ATTRIBUTE49 in VARCHAR2,
69 X_ATTRIBUTE50 in VARCHAR2,
70 X_FLEX_VALUE_MEANING in VARCHAR2,
71 X_DESCRIPTION in VARCHAR2,
72 X_CREATION_DATE in DATE,
73 X_CREATED_BY in NUMBER,
74 X_LAST_UPDATE_DATE in DATE,
75 X_LAST_UPDATED_BY in NUMBER,
76 X_LAST_UPDATE_LOGIN in NUMBER
77 ) is
78 cursor C is select ROWID from FND_FLEX_VALUES
79 where FLEX_VALUE_ID = X_FLEX_VALUE_ID
80 ;
81 begin
82 insert into FND_FLEX_VALUES (
83 ATTRIBUTE_SORT_ORDER,
84 FLEX_VALUE_SET_ID,
85 FLEX_VALUE_ID,
86 FLEX_VALUE,
87 ENABLED_FLAG,
88 SUMMARY_FLAG,
89 START_DATE_ACTIVE,
90 END_DATE_ACTIVE,
91 PARENT_FLEX_VALUE_LOW,
92 PARENT_FLEX_VALUE_HIGH,
93 STRUCTURED_HIERARCHY_LEVEL,
94 HIERARCHY_LEVEL,
95 COMPILED_VALUE_ATTRIBUTES,
96 VALUE_CATEGORY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15,
112 ATTRIBUTE16,
113 ATTRIBUTE17,
114 ATTRIBUTE18,
115 ATTRIBUTE19,
116 ATTRIBUTE20,
117 ATTRIBUTE21,
118 ATTRIBUTE22,
119 ATTRIBUTE23,
120 ATTRIBUTE24,
121 ATTRIBUTE25,
122 ATTRIBUTE26,
123 ATTRIBUTE27,
124 ATTRIBUTE28,
125 ATTRIBUTE29,
126 ATTRIBUTE30,
127 ATTRIBUTE31,
128 ATTRIBUTE32,
129 ATTRIBUTE33,
130 ATTRIBUTE34,
131 ATTRIBUTE35,
132 ATTRIBUTE36,
133 ATTRIBUTE37,
134 ATTRIBUTE38,
135 ATTRIBUTE39,
136 ATTRIBUTE40,
137 ATTRIBUTE41,
138 ATTRIBUTE42,
139 ATTRIBUTE43,
140 ATTRIBUTE44,
141 ATTRIBUTE45,
142 ATTRIBUTE46,
143 ATTRIBUTE47,
144 ATTRIBUTE48,
145 ATTRIBUTE49,
146 ATTRIBUTE50,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN
152 ) values (
153 X_ATTRIBUTE_SORT_ORDER,
154 X_FLEX_VALUE_SET_ID,
155 X_FLEX_VALUE_ID,
156 X_FLEX_VALUE,
157 X_ENABLED_FLAG,
158 X_SUMMARY_FLAG,
159 X_START_DATE_ACTIVE,
160 X_END_DATE_ACTIVE,
161 X_PARENT_FLEX_VALUE_LOW,
162 X_PARENT_FLEX_VALUE_HIGH,
163 X_STRUCTURED_HIERARCHY_LEVEL,
164 X_HIERARCHY_LEVEL,
165 X_COMPILED_VALUE_ATTRIBUTES,
166 X_VALUE_CATEGORY,
167 X_ATTRIBUTE1,
168 X_ATTRIBUTE2,
169 X_ATTRIBUTE3,
170 X_ATTRIBUTE4,
171 X_ATTRIBUTE5,
172 X_ATTRIBUTE6,
173 X_ATTRIBUTE7,
174 X_ATTRIBUTE8,
175 X_ATTRIBUTE9,
176 X_ATTRIBUTE10,
177 X_ATTRIBUTE11,
178 X_ATTRIBUTE12,
179 X_ATTRIBUTE13,
180 X_ATTRIBUTE14,
181 X_ATTRIBUTE15,
182 X_ATTRIBUTE16,
183 X_ATTRIBUTE17,
184 X_ATTRIBUTE18,
185 X_ATTRIBUTE19,
186 X_ATTRIBUTE20,
187 X_ATTRIBUTE21,
188 X_ATTRIBUTE22,
189 X_ATTRIBUTE23,
190 X_ATTRIBUTE24,
191 X_ATTRIBUTE25,
192 X_ATTRIBUTE26,
193 X_ATTRIBUTE27,
194 X_ATTRIBUTE28,
195 X_ATTRIBUTE29,
196 X_ATTRIBUTE30,
197 X_ATTRIBUTE31,
198 X_ATTRIBUTE32,
199 X_ATTRIBUTE33,
200 X_ATTRIBUTE34,
201 X_ATTRIBUTE35,
202 X_ATTRIBUTE36,
203 X_ATTRIBUTE37,
204 X_ATTRIBUTE38,
205 X_ATTRIBUTE39,
206 X_ATTRIBUTE40,
207 X_ATTRIBUTE41,
208 X_ATTRIBUTE42,
209 X_ATTRIBUTE43,
210 X_ATTRIBUTE44,
211 X_ATTRIBUTE45,
212 X_ATTRIBUTE46,
213 X_ATTRIBUTE47,
214 X_ATTRIBUTE48,
215 X_ATTRIBUTE49,
216 X_ATTRIBUTE50,
217 X_CREATION_DATE,
218 X_CREATED_BY,
219 X_LAST_UPDATE_DATE,
220 X_LAST_UPDATED_BY,
221 X_LAST_UPDATE_LOGIN
222 );
223
224 insert into FND_FLEX_VALUES_TL (
225 FLEX_VALUE_ID,
226 LAST_UPDATE_DATE,
227 LAST_UPDATED_BY,
228 CREATION_DATE,
229 CREATED_BY,
230 LAST_UPDATE_LOGIN,
231 DESCRIPTION,
232 FLEX_VALUE_MEANING,
233 LANGUAGE,
234 SOURCE_LANG
235 ) select
236 X_FLEX_VALUE_ID,
237 X_LAST_UPDATE_DATE,
238 X_LAST_UPDATED_BY,
239 X_CREATION_DATE,
240 X_CREATED_BY,
241 X_LAST_UPDATE_LOGIN,
242 X_DESCRIPTION,
243 X_FLEX_VALUE_MEANING,
244 L.LANGUAGE_CODE,
245 userenv('LANG')
246 from FND_LANGUAGES L
247 where L.INSTALLED_FLAG in ('I', 'B')
248 and not exists
249 (select NULL
250 from FND_FLEX_VALUES_TL T
251 where T.FLEX_VALUE_ID = X_FLEX_VALUE_ID
252 and T.LANGUAGE = L.LANGUAGE_CODE);
253
254 open c;
255 fetch c into X_ROWID;
256 if (c%notfound) then
257 close c;
258 raise no_data_found;
259 end if;
260 close c;
261
262 end INSERT_ROW;
263
264 procedure LOCK_ROW (
265 X_FLEX_VALUE_ID in NUMBER,
266 X_ATTRIBUTE_SORT_ORDER in NUMBER,
267 X_FLEX_VALUE_SET_ID in NUMBER,
268 X_FLEX_VALUE in VARCHAR2,
269 X_ENABLED_FLAG in VARCHAR2,
270 X_SUMMARY_FLAG in VARCHAR2,
271 X_START_DATE_ACTIVE in DATE,
272 X_END_DATE_ACTIVE in DATE,
273 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
274 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
275 X_STRUCTURED_HIERARCHY_LEVEL in NUMBER,
276 X_HIERARCHY_LEVEL in VARCHAR2,
277 X_COMPILED_VALUE_ATTRIBUTES in VARCHAR2,
278 X_VALUE_CATEGORY in VARCHAR2,
279 X_ATTRIBUTE1 in VARCHAR2,
280 X_ATTRIBUTE2 in VARCHAR2,
281 X_ATTRIBUTE3 in VARCHAR2,
282 X_ATTRIBUTE4 in VARCHAR2,
283 X_ATTRIBUTE5 in VARCHAR2,
284 X_ATTRIBUTE6 in VARCHAR2,
285 X_ATTRIBUTE7 in VARCHAR2,
286 X_ATTRIBUTE8 in VARCHAR2,
287 X_ATTRIBUTE9 in VARCHAR2,
288 X_ATTRIBUTE10 in VARCHAR2,
289 X_ATTRIBUTE11 in VARCHAR2,
290 X_ATTRIBUTE12 in VARCHAR2,
291 X_ATTRIBUTE13 in VARCHAR2,
292 X_ATTRIBUTE14 in VARCHAR2,
293 X_ATTRIBUTE15 in VARCHAR2,
294 X_ATTRIBUTE16 in VARCHAR2,
295 X_ATTRIBUTE17 in VARCHAR2,
296 X_ATTRIBUTE18 in VARCHAR2,
297 X_ATTRIBUTE19 in VARCHAR2,
298 X_ATTRIBUTE20 in VARCHAR2,
299 X_ATTRIBUTE21 in VARCHAR2,
300 X_ATTRIBUTE22 in VARCHAR2,
301 X_ATTRIBUTE23 in VARCHAR2,
302 X_ATTRIBUTE24 in VARCHAR2,
303 X_ATTRIBUTE25 in VARCHAR2,
304 X_ATTRIBUTE26 in VARCHAR2,
305 X_ATTRIBUTE27 in VARCHAR2,
306 X_ATTRIBUTE28 in VARCHAR2,
307 X_ATTRIBUTE29 in VARCHAR2,
308 X_ATTRIBUTE30 in VARCHAR2,
309 X_ATTRIBUTE31 in VARCHAR2,
310 X_ATTRIBUTE32 in VARCHAR2,
311 X_ATTRIBUTE33 in VARCHAR2,
312 X_ATTRIBUTE34 in VARCHAR2,
313 X_ATTRIBUTE35 in VARCHAR2,
314 X_ATTRIBUTE36 in VARCHAR2,
315 X_ATTRIBUTE37 in VARCHAR2,
316 X_ATTRIBUTE38 in VARCHAR2,
317 X_ATTRIBUTE39 in VARCHAR2,
318 X_ATTRIBUTE40 in VARCHAR2,
319 X_ATTRIBUTE41 in VARCHAR2,
320 X_ATTRIBUTE42 in VARCHAR2,
321 X_ATTRIBUTE43 in VARCHAR2,
322 X_ATTRIBUTE44 in VARCHAR2,
323 X_ATTRIBUTE45 in VARCHAR2,
324 X_ATTRIBUTE46 in VARCHAR2,
325 X_ATTRIBUTE47 in VARCHAR2,
326 X_ATTRIBUTE48 in VARCHAR2,
327 X_ATTRIBUTE49 in VARCHAR2,
328 X_ATTRIBUTE50 in VARCHAR2,
329 X_FLEX_VALUE_MEANING in VARCHAR2,
330 X_DESCRIPTION in VARCHAR2
331 ) is
332 cursor c is select
333 ATTRIBUTE_SORT_ORDER,
334 FLEX_VALUE_SET_ID,
335 FLEX_VALUE,
336 ENABLED_FLAG,
337 SUMMARY_FLAG,
338 START_DATE_ACTIVE,
339 END_DATE_ACTIVE,
340 PARENT_FLEX_VALUE_LOW,
341 PARENT_FLEX_VALUE_HIGH,
342 STRUCTURED_HIERARCHY_LEVEL,
343 HIERARCHY_LEVEL,
344 COMPILED_VALUE_ATTRIBUTES,
345 VALUE_CATEGORY,
346 ATTRIBUTE1,
347 ATTRIBUTE2,
348 ATTRIBUTE3,
349 ATTRIBUTE4,
350 ATTRIBUTE5,
351 ATTRIBUTE6,
352 ATTRIBUTE7,
353 ATTRIBUTE8,
354 ATTRIBUTE9,
355 ATTRIBUTE10,
356 ATTRIBUTE11,
357 ATTRIBUTE12,
358 ATTRIBUTE13,
359 ATTRIBUTE14,
360 ATTRIBUTE15,
361 ATTRIBUTE16,
362 ATTRIBUTE17,
363 ATTRIBUTE18,
364 ATTRIBUTE19,
365 ATTRIBUTE20,
366 ATTRIBUTE21,
367 ATTRIBUTE22,
368 ATTRIBUTE23,
369 ATTRIBUTE24,
370 ATTRIBUTE25,
371 ATTRIBUTE26,
372 ATTRIBUTE27,
373 ATTRIBUTE28,
374 ATTRIBUTE29,
375 ATTRIBUTE30,
376 ATTRIBUTE31,
377 ATTRIBUTE32,
378 ATTRIBUTE33,
379 ATTRIBUTE34,
380 ATTRIBUTE35,
381 ATTRIBUTE36,
382 ATTRIBUTE37,
383 ATTRIBUTE38,
384 ATTRIBUTE39,
385 ATTRIBUTE40,
386 ATTRIBUTE41,
387 ATTRIBUTE42,
388 ATTRIBUTE43,
389 ATTRIBUTE44,
390 ATTRIBUTE45,
391 ATTRIBUTE46,
392 ATTRIBUTE47,
393 ATTRIBUTE48,
394 ATTRIBUTE49,
395 ATTRIBUTE50
396 from FND_FLEX_VALUES
397 where FLEX_VALUE_ID = X_FLEX_VALUE_ID
398 for update of FLEX_VALUE_ID nowait;
399 recinfo c%rowtype;
400
401 cursor c1 is select
402 FLEX_VALUE_MEANING,
403 DESCRIPTION,
404 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
405 from FND_FLEX_VALUES_TL
406 where FLEX_VALUE_ID = X_FLEX_VALUE_ID
407 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
408 for update of FLEX_VALUE_ID nowait;
409 begin
410 open c;
411 fetch c into recinfo;
412 if (c%notfound) then
416 end if;
413 close c;
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 app_exception.raise_exception;
417 close c;
418 if ( ((recinfo.ATTRIBUTE_SORT_ORDER = X_ATTRIBUTE_SORT_ORDER)
419 OR ((recinfo.ATTRIBUTE_SORT_ORDER is null) AND (X_ATTRIBUTE_SORT_ORDER is null)))
420 AND (recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
421 AND (recinfo.FLEX_VALUE = X_FLEX_VALUE)
422 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
423 AND (recinfo.SUMMARY_FLAG = X_SUMMARY_FLAG)
424 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
425 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
426 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
427 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
428 AND ((recinfo.PARENT_FLEX_VALUE_LOW = X_PARENT_FLEX_VALUE_LOW)
429 OR ((recinfo.PARENT_FLEX_VALUE_LOW is null) AND (X_PARENT_FLEX_VALUE_LOW is null)))
430 AND ((recinfo.PARENT_FLEX_VALUE_HIGH = X_PARENT_FLEX_VALUE_HIGH)
431 OR ((recinfo.PARENT_FLEX_VALUE_HIGH is null) AND (X_PARENT_FLEX_VALUE_HIGH is null)))
432 AND ((recinfo.STRUCTURED_HIERARCHY_LEVEL = X_STRUCTURED_HIERARCHY_LEVEL)
433 OR ((recinfo.STRUCTURED_HIERARCHY_LEVEL is null) AND (X_STRUCTURED_HIERARCHY_LEVEL is null)))
434 AND ((recinfo.HIERARCHY_LEVEL = X_HIERARCHY_LEVEL)
435 OR ((recinfo.HIERARCHY_LEVEL is null) AND (X_HIERARCHY_LEVEL is null)))
436 AND ((recinfo.COMPILED_VALUE_ATTRIBUTES = X_COMPILED_VALUE_ATTRIBUTES)
437 OR ((recinfo.COMPILED_VALUE_ATTRIBUTES is null) AND (X_COMPILED_VALUE_ATTRIBUTES is null)))
438 AND ((recinfo.VALUE_CATEGORY = X_VALUE_CATEGORY)
439 OR ((recinfo.VALUE_CATEGORY is null) AND (X_VALUE_CATEGORY is null)))
440 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
441 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
442 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
443 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
444 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
445 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
446 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
447 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
448 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
449 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
450 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
451 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
452 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
453 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
454 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
455 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
456 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
457 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
458 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
459 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
460 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
461 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
462 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
463 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
464 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
465 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
466 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
467 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
468 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
469 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
470 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
471 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
472 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
473 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
474 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
475 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
476 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
477 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
478 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
479 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
480 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
481 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
482 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
483 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
484 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
485 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
486 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
487 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
488 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
489 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
490 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
491 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
492 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
493 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
494 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
495 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
496 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
497 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
498 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
499 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
500 AND ((recinfo.ATTRIBUTE31 = X_ATTRIBUTE31)
501 OR ((recinfo.ATTRIBUTE31 is null) AND (X_ATTRIBUTE31 is null)))
502 AND ((recinfo.ATTRIBUTE32 = X_ATTRIBUTE32)
503 OR ((recinfo.ATTRIBUTE32 is null) AND (X_ATTRIBUTE32 is null)))
504 AND ((recinfo.ATTRIBUTE33 = X_ATTRIBUTE33)
505 OR ((recinfo.ATTRIBUTE33 is null) AND (X_ATTRIBUTE33 is null)))
506 AND ((recinfo.ATTRIBUTE34 = X_ATTRIBUTE34)
510 AND ((recinfo.ATTRIBUTE36 = X_ATTRIBUTE36)
507 OR ((recinfo.ATTRIBUTE34 is null) AND (X_ATTRIBUTE34 is null)))
508 AND ((recinfo.ATTRIBUTE35 = X_ATTRIBUTE35)
509 OR ((recinfo.ATTRIBUTE35 is null) AND (X_ATTRIBUTE35 is null)))
511 OR ((recinfo.ATTRIBUTE36 is null) AND (X_ATTRIBUTE36 is null)))
512 AND ((recinfo.ATTRIBUTE37 = X_ATTRIBUTE37)
513 OR ((recinfo.ATTRIBUTE37 is null) AND (X_ATTRIBUTE37 is null)))
514 AND ((recinfo.ATTRIBUTE38 = X_ATTRIBUTE38)
515 OR ((recinfo.ATTRIBUTE38 is null) AND (X_ATTRIBUTE38 is null)))
516 AND ((recinfo.ATTRIBUTE39 = X_ATTRIBUTE39)
517 OR ((recinfo.ATTRIBUTE39 is null) AND (X_ATTRIBUTE39 is null)))
518 AND ((recinfo.ATTRIBUTE40 = X_ATTRIBUTE40)
519 OR ((recinfo.ATTRIBUTE40 is null) AND (X_ATTRIBUTE40 is null)))
520 AND ((recinfo.ATTRIBUTE41 = X_ATTRIBUTE41)
521 OR ((recinfo.ATTRIBUTE41 is null) AND (X_ATTRIBUTE41 is null)))
522 AND ((recinfo.ATTRIBUTE42 = X_ATTRIBUTE42)
523 OR ((recinfo.ATTRIBUTE42 is null) AND (X_ATTRIBUTE42 is null)))
524 AND ((recinfo.ATTRIBUTE43 = X_ATTRIBUTE43)
525 OR ((recinfo.ATTRIBUTE43 is null) AND (X_ATTRIBUTE43 is null)))
526 AND ((recinfo.ATTRIBUTE44 = X_ATTRIBUTE44)
527 OR ((recinfo.ATTRIBUTE44 is null) AND (X_ATTRIBUTE44 is null)))
528 AND ((recinfo.ATTRIBUTE45 = X_ATTRIBUTE45)
529 OR ((recinfo.ATTRIBUTE45 is null) AND (X_ATTRIBUTE45 is null)))
530 AND ((recinfo.ATTRIBUTE46 = X_ATTRIBUTE46)
531 OR ((recinfo.ATTRIBUTE46 is null) AND (X_ATTRIBUTE46 is null)))
532 AND ((recinfo.ATTRIBUTE47 = X_ATTRIBUTE47)
533 OR ((recinfo.ATTRIBUTE47 is null) AND (X_ATTRIBUTE47 is null)))
534 AND ((recinfo.ATTRIBUTE48 = X_ATTRIBUTE48)
535 OR ((recinfo.ATTRIBUTE48 is null) AND (X_ATTRIBUTE48 is null)))
536 AND ((recinfo.ATTRIBUTE49 = X_ATTRIBUTE49)
537 OR ((recinfo.ATTRIBUTE49 is null) AND (X_ATTRIBUTE49 is null)))
538 AND ((recinfo.ATTRIBUTE50 = X_ATTRIBUTE50)
539 OR ((recinfo.ATTRIBUTE50 is null) AND (X_ATTRIBUTE50 is null)))
540 ) then
541 null;
542 else
543 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
544 app_exception.raise_exception;
545 end if;
546
547 for tlinfo in c1 loop
548 if (tlinfo.BASELANG = 'Y') then
549 if ( (tlinfo.FLEX_VALUE_MEANING = X_FLEX_VALUE_MEANING)
550 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
551 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
552 ) then
553 null;
554 else
555 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
556 app_exception.raise_exception;
557 end if;
558 end if;
559 end loop;
560 return;
561 end LOCK_ROW;
562
563 procedure UPDATE_ROW (
564 X_FLEX_VALUE_ID in NUMBER,
565 X_ATTRIBUTE_SORT_ORDER in NUMBER,
566 X_FLEX_VALUE_SET_ID in NUMBER,
567 X_FLEX_VALUE in VARCHAR2,
568 X_ENABLED_FLAG in VARCHAR2,
569 X_SUMMARY_FLAG in VARCHAR2,
570 X_START_DATE_ACTIVE in DATE,
571 X_END_DATE_ACTIVE in DATE,
572 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
573 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
574 X_STRUCTURED_HIERARCHY_LEVEL in NUMBER,
575 X_HIERARCHY_LEVEL in VARCHAR2,
576 X_COMPILED_VALUE_ATTRIBUTES in VARCHAR2,
577 X_VALUE_CATEGORY in VARCHAR2,
578 X_ATTRIBUTE1 in VARCHAR2,
579 X_ATTRIBUTE2 in VARCHAR2,
580 X_ATTRIBUTE3 in VARCHAR2,
581 X_ATTRIBUTE4 in VARCHAR2,
582 X_ATTRIBUTE5 in VARCHAR2,
583 X_ATTRIBUTE6 in VARCHAR2,
584 X_ATTRIBUTE7 in VARCHAR2,
585 X_ATTRIBUTE8 in VARCHAR2,
586 X_ATTRIBUTE9 in VARCHAR2,
587 X_ATTRIBUTE10 in VARCHAR2,
588 X_ATTRIBUTE11 in VARCHAR2,
589 X_ATTRIBUTE12 in VARCHAR2,
590 X_ATTRIBUTE13 in VARCHAR2,
591 X_ATTRIBUTE14 in VARCHAR2,
592 X_ATTRIBUTE15 in VARCHAR2,
593 X_ATTRIBUTE16 in VARCHAR2,
594 X_ATTRIBUTE17 in VARCHAR2,
595 X_ATTRIBUTE18 in VARCHAR2,
596 X_ATTRIBUTE19 in VARCHAR2,
597 X_ATTRIBUTE20 in VARCHAR2,
598 X_ATTRIBUTE21 in VARCHAR2,
599 X_ATTRIBUTE22 in VARCHAR2,
600 X_ATTRIBUTE23 in VARCHAR2,
601 X_ATTRIBUTE24 in VARCHAR2,
602 X_ATTRIBUTE25 in VARCHAR2,
603 X_ATTRIBUTE26 in VARCHAR2,
604 X_ATTRIBUTE27 in VARCHAR2,
605 X_ATTRIBUTE28 in VARCHAR2,
606 X_ATTRIBUTE29 in VARCHAR2,
607 X_ATTRIBUTE30 in VARCHAR2,
608 X_ATTRIBUTE31 in VARCHAR2,
609 X_ATTRIBUTE32 in VARCHAR2,
610 X_ATTRIBUTE33 in VARCHAR2,
611 X_ATTRIBUTE34 in VARCHAR2,
612 X_ATTRIBUTE35 in VARCHAR2,
613 X_ATTRIBUTE36 in VARCHAR2,
614 X_ATTRIBUTE37 in VARCHAR2,
615 X_ATTRIBUTE38 in VARCHAR2,
616 X_ATTRIBUTE39 in VARCHAR2,
617 X_ATTRIBUTE40 in VARCHAR2,
618 X_ATTRIBUTE41 in VARCHAR2,
619 X_ATTRIBUTE42 in VARCHAR2,
620 X_ATTRIBUTE43 in VARCHAR2,
621 X_ATTRIBUTE44 in VARCHAR2,
622 X_ATTRIBUTE45 in VARCHAR2,
623 X_ATTRIBUTE46 in VARCHAR2,
624 X_ATTRIBUTE47 in VARCHAR2,
625 X_ATTRIBUTE48 in VARCHAR2,
626 X_ATTRIBUTE49 in VARCHAR2,
627 X_ATTRIBUTE50 in VARCHAR2,
628 X_FLEX_VALUE_MEANING in VARCHAR2,
629 X_DESCRIPTION in VARCHAR2,
630 X_LAST_UPDATE_DATE in DATE,
631 X_LAST_UPDATED_BY in NUMBER,
632 X_LAST_UPDATE_LOGIN in NUMBER
633 ) is
634 begin
635 update FND_FLEX_VALUES set
636 ATTRIBUTE_SORT_ORDER = X_ATTRIBUTE_SORT_ORDER,
637 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
638 FLEX_VALUE = X_FLEX_VALUE,
639 ENABLED_FLAG = X_ENABLED_FLAG,
640 SUMMARY_FLAG = X_SUMMARY_FLAG,
641 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
642 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
643 PARENT_FLEX_VALUE_LOW = X_PARENT_FLEX_VALUE_LOW,
644 PARENT_FLEX_VALUE_HIGH = X_PARENT_FLEX_VALUE_HIGH,
648 VALUE_CATEGORY = X_VALUE_CATEGORY,
645 STRUCTURED_HIERARCHY_LEVEL = X_STRUCTURED_HIERARCHY_LEVEL,
646 HIERARCHY_LEVEL = X_HIERARCHY_LEVEL,
647 COMPILED_VALUE_ATTRIBUTES = X_COMPILED_VALUE_ATTRIBUTES,
649 ATTRIBUTE1 = X_ATTRIBUTE1,
650 ATTRIBUTE2 = X_ATTRIBUTE2,
651 ATTRIBUTE3 = X_ATTRIBUTE3,
652 ATTRIBUTE4 = X_ATTRIBUTE4,
653 ATTRIBUTE5 = X_ATTRIBUTE5,
654 ATTRIBUTE6 = X_ATTRIBUTE6,
655 ATTRIBUTE7 = X_ATTRIBUTE7,
656 ATTRIBUTE8 = X_ATTRIBUTE8,
657 ATTRIBUTE9 = X_ATTRIBUTE9,
658 ATTRIBUTE10 = X_ATTRIBUTE10,
659 ATTRIBUTE11 = X_ATTRIBUTE11,
660 ATTRIBUTE12 = X_ATTRIBUTE12,
661 ATTRIBUTE13 = X_ATTRIBUTE13,
662 ATTRIBUTE14 = X_ATTRIBUTE14,
663 ATTRIBUTE15 = X_ATTRIBUTE15,
664 ATTRIBUTE16 = X_ATTRIBUTE16,
665 ATTRIBUTE17 = X_ATTRIBUTE17,
666 ATTRIBUTE18 = X_ATTRIBUTE18,
667 ATTRIBUTE19 = X_ATTRIBUTE19,
668 ATTRIBUTE20 = X_ATTRIBUTE20,
669 ATTRIBUTE21 = X_ATTRIBUTE21,
670 ATTRIBUTE22 = X_ATTRIBUTE22,
671 ATTRIBUTE23 = X_ATTRIBUTE23,
672 ATTRIBUTE24 = X_ATTRIBUTE24,
673 ATTRIBUTE25 = X_ATTRIBUTE25,
674 ATTRIBUTE26 = X_ATTRIBUTE26,
675 ATTRIBUTE27 = X_ATTRIBUTE27,
676 ATTRIBUTE28 = X_ATTRIBUTE28,
677 ATTRIBUTE29 = X_ATTRIBUTE29,
678 ATTRIBUTE30 = X_ATTRIBUTE30,
679 ATTRIBUTE31 = X_ATTRIBUTE31,
680 ATTRIBUTE32 = X_ATTRIBUTE32,
681 ATTRIBUTE33 = X_ATTRIBUTE33,
682 ATTRIBUTE34 = X_ATTRIBUTE34,
683 ATTRIBUTE35 = X_ATTRIBUTE35,
684 ATTRIBUTE36 = X_ATTRIBUTE36,
685 ATTRIBUTE37 = X_ATTRIBUTE37,
686 ATTRIBUTE38 = X_ATTRIBUTE38,
687 ATTRIBUTE39 = X_ATTRIBUTE39,
688 ATTRIBUTE40 = X_ATTRIBUTE40,
689 ATTRIBUTE41 = X_ATTRIBUTE41,
690 ATTRIBUTE42 = X_ATTRIBUTE42,
691 ATTRIBUTE43 = X_ATTRIBUTE43,
692 ATTRIBUTE44 = X_ATTRIBUTE44,
693 ATTRIBUTE45 = X_ATTRIBUTE45,
694 ATTRIBUTE46 = X_ATTRIBUTE46,
695 ATTRIBUTE47 = X_ATTRIBUTE47,
696 ATTRIBUTE48 = X_ATTRIBUTE48,
697 ATTRIBUTE49 = X_ATTRIBUTE49,
698 ATTRIBUTE50 = X_ATTRIBUTE50,
699 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
700 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
701 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
702 where FLEX_VALUE_ID = X_FLEX_VALUE_ID;
703
704 if (sql%notfound) then
705 raise no_data_found;
706 else
707 update FND_FLEX_VALUES_TL set
708 FLEX_VALUE_MEANING = X_FLEX_VALUE_MEANING,
709 DESCRIPTION = X_DESCRIPTION,
710 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
711 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
712 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
713 SOURCE_LANG = userenv('LANG')
714 where FLEX_VALUE_ID = X_FLEX_VALUE_ID
715 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
716
717 if (sql%notfound) then
718 insert into FND_FLEX_VALUES_TL (
719 FLEX_VALUE_ID,
720 LAST_UPDATE_DATE,
721 LAST_UPDATED_BY,
722 CREATION_DATE,
723 CREATED_BY,
724 LAST_UPDATE_LOGIN,
725 DESCRIPTION,
726 FLEX_VALUE_MEANING,
727 LANGUAGE,
728 SOURCE_LANG
729 ) select
730 X_FLEX_VALUE_ID,
731 X_LAST_UPDATE_DATE,
732 X_LAST_UPDATED_BY,
733 SYSDATE,
734 X_LAST_UPDATE_LOGIN,
735 X_LAST_UPDATE_LOGIN,
736 X_DESCRIPTION,
737 X_FLEX_VALUE_MEANING,
738 L.LANGUAGE_CODE,
739 userenv('LANG')
740 from FND_LANGUAGES L
741 where L.INSTALLED_FLAG in ('I', 'B')
742 and not exists
743 (select NULL
744 from FND_FLEX_VALUES_TL T
745 where T.FLEX_VALUE_ID = X_FLEX_VALUE_ID
746 and T.LANGUAGE = L.LANGUAGE_CODE);
747 -- raise no_data_found;
748 end if;
749 end if;
750 end UPDATE_ROW;
751
752 procedure DELETE_ROW (
753 X_FLEX_VALUE_ID in NUMBER
754 ) is
755 begin
756 delete from FND_FLEX_VALUES_TL
757 where FLEX_VALUE_ID = X_FLEX_VALUE_ID;
758
759 if (sql%notfound) then
760 raise no_data_found;
761 end if;
762
763 delete from FND_FLEX_VALUES
764 where FLEX_VALUE_ID = X_FLEX_VALUE_ID;
765
766 if (sql%notfound) then
767 raise no_data_found;
768 end if;
769 end DELETE_ROW;
770
771 procedure ADD_BASE_LANGUAGE
772 is
773 BEGIN
774 /*For fnd_flex_values records with no rows in the fnd_flex_values_tl table,
775 add the base language row so the ADD_LANGUAGE code will add the additional
776 installed language rows */
777
778
779 INSERT INTO FND_FLEX_VALUES_TL
780 (FLEX_VALUE_ID,
781 LAST_UPDATE_DATE,
782 LAST_UPDATED_BY,
783 CREATION_DATE,
784 CREATED_BY,
785 LAST_UPDATE_LOGIN,
786 FLEX_VALUE_MEANING,
787 LANGUAGE,
788 SOURCE_LANG
789 )
790 SELECT FLEX_VALUE_ID,
791 LAST_UPDATE_DATE,
792 LAST_UPDATED_BY,
793 CREATION_DATE,
794 CREATED_BY,
795 LAST_UPDATE_LOGIN,
796 FLEX_VALUE,
797 (select language_code
798 from fnd_languages
799 where installed_flag='B'),
800 (select language_code
801 from fnd_languages
802 where installed_flag='B')
803 FROM FND_FLEX_VALUES
804 WHERE FLEX_VALUE_ID NOT IN
805 (SELECT FLEX_VALUE_ID
806 FROM FND_FLEX_VALUES_TL
807 WHERE FLEX_VALUE_ID IN
808 (SELECT FLEX_VALUE_ID
809 FROM FND_FLEX_VALUES));
810
811 end ADD_BASE_LANGUAGE;
812
813 procedure ADD_LANGUAGE
814 is
815 begin
816 /* Mar/19/03 requested by Ric Ginsberg */
817 /* The following delete and update statements are commented out */
821
818 /* as a quick workaround to fix the time-consuming table handler issue */
819 /* Eventually we'll need to turn them into a separate fix_language procedure */
820 /*
822 delete from FND_FLEX_VALUES_TL T
823 where not exists
824 (select NULL
825 from FND_FLEX_VALUES B
826 where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
827 );
828
829 update FND_FLEX_VALUES_TL T set (
830 FLEX_VALUE_MEANING,
831 DESCRIPTION
832 ) = (select
833 B.FLEX_VALUE_MEANING,
834 B.DESCRIPTION
835 from FND_FLEX_VALUES_TL B
836 where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
837 and B.LANGUAGE = T.SOURCE_LANG)
838 where (
839 T.FLEX_VALUE_ID,
840 T.LANGUAGE
841 ) in (select
842 SUBT.FLEX_VALUE_ID,
843 SUBT.LANGUAGE
844 from FND_FLEX_VALUES_TL SUBB, FND_FLEX_VALUES_TL SUBT
845 where SUBB.FLEX_VALUE_ID = SUBT.FLEX_VALUE_ID
846 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
847 and (SUBB.FLEX_VALUE_MEANING <> SUBT.FLEX_VALUE_MEANING
848 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
849 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
850 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
851 ));
852 */
853
854 /* Call ADD_BASE_LANGUAGE to insure all values in fnd_flex_values table
855 have an associated row in the fnd_flex_values_tl table for the base language
856 before doing the add_language code to insert missing installed language
857 rows in the fnd_flex_values_tl table */
858
859 add_base_language;
860
861 insert /*+ append */ into FND_FLEX_VALUES_TL (
862 FLEX_VALUE_ID,
863 LAST_UPDATE_DATE,
864 LAST_UPDATED_BY,
865 CREATION_DATE,
866 CREATED_BY,
867 LAST_UPDATE_LOGIN,
868 DESCRIPTION,
869 FLEX_VALUE_MEANING,
870 LANGUAGE,
871 SOURCE_LANG
872 )
873 select /*+ ordered swap_join_inputs(t) parallel(v) parallel(t) */ v.* from
874 (select /*+ no_merge parallel(b) */
875 B.FLEX_VALUE_ID,
876 B.LAST_UPDATE_DATE,
877 B.LAST_UPDATED_BY,
878 B.CREATION_DATE,
879 B.CREATED_BY,
880 B.LAST_UPDATE_LOGIN,
881 B.DESCRIPTION,
882 B.FLEX_VALUE_MEANING,
883 L.LANGUAGE_CODE,
884 B.SOURCE_LANG
885 from FND_LANGUAGES L, FND_FLEX_VALUES_TL B
886 where DECODE(L.INSTALLED_FLAG, 'I', 1, 'B', 1, 0) = 1
887 and B.LANGUAGE = userenv('LANG')
888 ) v, FND_FLEX_VALUES_TL t
889 where t.flex_value_id(+) = v.flex_value_id
890 and t.language(+) = v.language_code
891 and t.flex_value_id is NULL;
892
893 end ADD_LANGUAGE;
894
895 PROCEDURE load_row
896 (x_flex_value_set_name IN VARCHAR2,
897 x_parent_flex_value_low IN VARCHAR2,
898 x_flex_value IN VARCHAR2,
899 x_who IN fnd_flex_loader_apis.who_type,
900 x_enabled_flag IN VARCHAR2,
901 x_summary_flag IN VARCHAR2,
902 x_start_date_active IN DATE,
903 x_end_date_active IN DATE,
904 x_parent_flex_value_high IN VARCHAR2,
905 x_structured_hierarchy_level IN NUMBER,
906 x_hierarchy_level IN VARCHAR2,
907 x_compiled_value_attributes IN VARCHAR2,
908 x_value_category IN VARCHAR2,
909 x_attribute1 IN VARCHAR2,
910 x_attribute2 IN VARCHAR2,
911 x_attribute3 IN VARCHAR2,
912 x_attribute4 IN VARCHAR2,
913 x_attribute5 IN VARCHAR2,
914 x_attribute6 IN VARCHAR2,
915 x_attribute7 IN VARCHAR2,
916 x_attribute8 IN VARCHAR2,
917 x_attribute9 IN VARCHAR2,
918 x_attribute10 IN VARCHAR2,
919 x_attribute11 IN VARCHAR2,
920 x_attribute12 IN VARCHAR2,
921 x_attribute13 IN VARCHAR2,
922 x_attribute14 IN VARCHAR2,
923 x_attribute15 IN VARCHAR2,
924 x_attribute16 IN VARCHAR2,
925 x_attribute17 IN VARCHAR2,
926 x_attribute18 IN VARCHAR2,
927 x_attribute19 IN VARCHAR2,
928 x_attribute20 IN VARCHAR2,
929 x_attribute21 IN VARCHAR2,
930 x_attribute22 IN VARCHAR2,
931 x_attribute23 IN VARCHAR2,
932 x_attribute24 IN VARCHAR2,
933 x_attribute25 IN VARCHAR2,
934 x_attribute26 IN VARCHAR2,
935 x_attribute27 IN VARCHAR2,
936 x_attribute28 IN VARCHAR2,
937 x_attribute29 IN VARCHAR2,
938 x_attribute30 IN VARCHAR2,
939 x_attribute31 IN VARCHAR2,
940 x_attribute32 IN VARCHAR2,
941 x_attribute33 IN VARCHAR2,
942 x_attribute34 IN VARCHAR2,
943 x_attribute35 IN VARCHAR2,
944 x_attribute36 IN VARCHAR2,
945 x_attribute37 IN VARCHAR2,
946 x_attribute38 IN VARCHAR2,
947 x_attribute39 IN VARCHAR2,
948 x_attribute40 IN VARCHAR2,
949 x_attribute41 IN VARCHAR2,
950 x_attribute42 IN VARCHAR2,
951 x_attribute43 IN VARCHAR2,
952 x_attribute44 IN VARCHAR2,
953 x_attribute45 IN VARCHAR2,
954 x_attribute46 IN VARCHAR2,
955 x_attribute47 IN VARCHAR2,
956 x_attribute48 IN VARCHAR2,
960 x_flex_value_meaning IN VARCHAR2,
957 x_attribute49 IN VARCHAR2,
958 x_attribute50 IN VARCHAR2,
959 x_attribute_sort_order IN VARCHAR2,
961 x_description IN VARCHAR2)
962 IS
963 l_flex_value_set_id NUMBER := NULL;
964 l_flex_value_id NUMBER;
965 l_validation_type VARCHAR2(1);
966 l_rowid VARCHAR2(64);
967 BEGIN
968 SELECT flex_value_set_id, validation_type
969 INTO l_flex_value_set_id, l_validation_type
970 FROM fnd_flex_value_sets
971 WHERE flex_value_set_name = x_flex_value_set_name;
972
973 BEGIN
974 IF (l_validation_type in ('D', 'Y')) THEN
975 SELECT flex_value_id
976 INTO l_flex_value_id
977 FROM fnd_flex_values
978 WHERE flex_value_set_id = l_flex_value_set_id
979 AND flex_value = x_flex_value
980 AND (parent_flex_value_low = x_parent_flex_value_low OR
981 (parent_flex_value_low IS NULL AND
982 x_parent_flex_value_low IS NULL));
983 ELSE
984 SELECT flex_value_id
985 INTO l_flex_value_id
986 FROM fnd_flex_values
987 WHERE flex_value_set_id = l_flex_value_set_id
988 AND flex_value = x_flex_value;
989 END IF;
990
991 fnd_flex_values_pkg.update_row
992 (X_FLEX_VALUE_ID => l_flex_value_id,
993 X_ATTRIBUTE_SORT_ORDER => x_attribute_sort_order,
994 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
995 X_FLEX_VALUE => x_flex_value,
996 X_ENABLED_FLAG => x_enabled_flag,
997 X_SUMMARY_FLAG => x_summary_flag,
998 X_START_DATE_ACTIVE => x_start_date_active,
999 X_END_DATE_ACTIVE => x_end_date_active,
1000 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
1001 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
1002 X_STRUCTURED_HIERARCHY_LEVEL => x_structured_hierarchy_level,
1003 X_HIERARCHY_LEVEL => x_hierarchy_level,
1004 X_COMPILED_VALUE_ATTRIBUTES => x_compiled_value_attributes,
1005 X_VALUE_CATEGORY => x_value_category,
1006 X_ATTRIBUTE1 => x_attribute1,
1007 X_ATTRIBUTE2 => x_attribute2,
1008 X_ATTRIBUTE3 => x_attribute3,
1009 X_ATTRIBUTE4 => x_attribute4,
1010 X_ATTRIBUTE5 => x_attribute5,
1011 X_ATTRIBUTE6 => x_attribute6,
1012 X_ATTRIBUTE7 => x_attribute7,
1013 X_ATTRIBUTE8 => x_attribute8,
1014 X_ATTRIBUTE9 => x_attribute9,
1015 X_ATTRIBUTE10 => x_attribute10,
1016 X_ATTRIBUTE11 => x_attribute11,
1017 X_ATTRIBUTE12 => x_attribute12,
1018 X_ATTRIBUTE13 => x_attribute13,
1019 X_ATTRIBUTE14 => x_attribute14,
1020 X_ATTRIBUTE15 => x_attribute15,
1021 X_ATTRIBUTE16 => x_attribute16,
1022 X_ATTRIBUTE17 => x_attribute17,
1023 X_ATTRIBUTE18 => x_attribute18,
1024 X_ATTRIBUTE19 => x_attribute19,
1025 X_ATTRIBUTE20 => x_attribute20,
1026 X_ATTRIBUTE21 => x_attribute21,
1027 X_ATTRIBUTE22 => x_attribute22,
1028 X_ATTRIBUTE23 => x_attribute23,
1029 X_ATTRIBUTE24 => x_attribute24,
1030 X_ATTRIBUTE25 => x_attribute25,
1031 X_ATTRIBUTE26 => x_attribute26,
1032 X_ATTRIBUTE27 => x_attribute27,
1033 X_ATTRIBUTE28 => x_attribute28,
1034 X_ATTRIBUTE29 => x_attribute29,
1035 X_ATTRIBUTE30 => x_attribute30,
1036 X_ATTRIBUTE31 => x_attribute31,
1037 X_ATTRIBUTE32 => x_attribute32,
1038 X_ATTRIBUTE33 => x_attribute33,
1039 X_ATTRIBUTE34 => x_attribute34,
1040 X_ATTRIBUTE35 => x_attribute35,
1041 X_ATTRIBUTE36 => x_attribute36,
1042 X_ATTRIBUTE37 => x_attribute37,
1043 X_ATTRIBUTE38 => x_attribute38,
1044 X_ATTRIBUTE39 => x_attribute39,
1045 X_ATTRIBUTE40 => x_attribute40,
1046 X_ATTRIBUTE41 => x_attribute41,
1047 X_ATTRIBUTE42 => x_attribute42,
1048 X_ATTRIBUTE43 => x_attribute43,
1049 X_ATTRIBUTE44 => x_attribute44,
1050 X_ATTRIBUTE45 => x_attribute45,
1051 X_ATTRIBUTE46 => x_attribute46,
1052 X_ATTRIBUTE47 => x_attribute47,
1053 X_ATTRIBUTE48 => x_attribute48,
1054 X_ATTRIBUTE49 => x_attribute49,
1055 X_ATTRIBUTE50 => x_attribute50,
1056 X_FLEX_VALUE_MEANING => x_flex_value_meaning,
1057 X_DESCRIPTION => x_description,
1058 X_LAST_UPDATE_DATE => x_who.last_update_date,
1059 X_LAST_UPDATED_BY => x_who.last_updated_by,
1060 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
1061 EXCEPTION
1062 WHEN no_data_found THEN
1063 SELECT fnd_flex_values_s.NEXTVAL
1064 INTO l_flex_value_id
1065 FROM dual;
1066
1067 fnd_flex_values_pkg.insert_row
1068 (X_ROWID => l_rowid,
1069 X_FLEX_VALUE_ID => l_flex_value_id,
1070 X_ATTRIBUTE_SORT_ORDER => x_attribute_sort_order,
1071 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
1072 X_FLEX_VALUE => x_flex_value,
1073 X_ENABLED_FLAG => x_enabled_flag,
1074 X_SUMMARY_FLAG => x_summary_flag,
1075 X_START_DATE_ACTIVE => x_start_date_active,
1076 X_END_DATE_ACTIVE => x_end_date_active,
1080 X_HIERARCHY_LEVEL => x_hierarchy_level,
1077 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
1078 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
1079 X_STRUCTURED_HIERARCHY_LEVEL => x_structured_hierarchy_level,
1081 X_COMPILED_VALUE_ATTRIBUTES => x_compiled_value_attributes,
1082 X_VALUE_CATEGORY => x_value_category,
1083 X_ATTRIBUTE1 => x_attribute1,
1084 X_ATTRIBUTE2 => x_attribute2,
1085 X_ATTRIBUTE3 => x_attribute3,
1086 X_ATTRIBUTE4 => x_attribute4,
1087 X_ATTRIBUTE5 => x_attribute5,
1088 X_ATTRIBUTE6 => x_attribute6,
1089 X_ATTRIBUTE7 => x_attribute7,
1090 X_ATTRIBUTE8 => x_attribute8,
1091 X_ATTRIBUTE9 => x_attribute9,
1092 X_ATTRIBUTE10 => x_attribute10,
1093 X_ATTRIBUTE11 => x_attribute11,
1094 X_ATTRIBUTE12 => x_attribute12,
1095 X_ATTRIBUTE13 => x_attribute13,
1096 X_ATTRIBUTE14 => x_attribute14,
1097 X_ATTRIBUTE15 => x_attribute15,
1098 X_ATTRIBUTE16 => x_attribute16,
1099 X_ATTRIBUTE17 => x_attribute17,
1100 X_ATTRIBUTE18 => x_attribute18,
1101 X_ATTRIBUTE19 => x_attribute19,
1102 X_ATTRIBUTE20 => x_attribute20,
1103 X_ATTRIBUTE21 => x_attribute21,
1104 X_ATTRIBUTE22 => x_attribute22,
1105 X_ATTRIBUTE23 => x_attribute23,
1106 X_ATTRIBUTE24 => x_attribute24,
1107 X_ATTRIBUTE25 => x_attribute25,
1108 X_ATTRIBUTE26 => x_attribute26,
1109 X_ATTRIBUTE27 => x_attribute27,
1110 X_ATTRIBUTE28 => x_attribute28,
1111 X_ATTRIBUTE29 => x_attribute29,
1112 X_ATTRIBUTE30 => x_attribute30,
1113 X_ATTRIBUTE31 => x_attribute31,
1114 X_ATTRIBUTE32 => x_attribute32,
1115 X_ATTRIBUTE33 => x_attribute33,
1116 X_ATTRIBUTE34 => x_attribute34,
1117 X_ATTRIBUTE35 => x_attribute35,
1118 X_ATTRIBUTE36 => x_attribute36,
1119 X_ATTRIBUTE37 => x_attribute37,
1120 X_ATTRIBUTE38 => x_attribute38,
1121 X_ATTRIBUTE39 => x_attribute39,
1122 X_ATTRIBUTE40 => x_attribute40,
1123 X_ATTRIBUTE41 => x_attribute41,
1124 X_ATTRIBUTE42 => x_attribute42,
1125 X_ATTRIBUTE43 => x_attribute43,
1126 X_ATTRIBUTE44 => x_attribute44,
1127 X_ATTRIBUTE45 => x_attribute45,
1128 X_ATTRIBUTE46 => x_attribute46,
1129 X_ATTRIBUTE47 => x_attribute47,
1130 X_ATTRIBUTE48 => x_attribute48,
1131 X_ATTRIBUTE49 => x_attribute49,
1132 X_ATTRIBUTE50 => x_attribute50,
1133 X_FLEX_VALUE_MEANING => x_flex_value_meaning,
1134 X_DESCRIPTION => x_description,
1135 X_CREATION_DATE => x_who.creation_date,
1136 X_CREATED_BY => x_who.created_by,
1137 X_LAST_UPDATE_DATE => x_who.last_update_date,
1138 X_LAST_UPDATED_BY => x_who.last_updated_by,
1139 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
1140 END;
1141 END load_row;
1142
1143 PROCEDURE translate_row
1144 (x_flex_value_set_name IN VARCHAR2,
1145 x_parent_flex_value_low IN VARCHAR2,
1146 x_flex_value IN VARCHAR2,
1147 x_who IN fnd_flex_loader_apis.who_type,
1148 x_flex_value_meaning IN VARCHAR2,
1149 x_description IN VARCHAR2)
1150 IS
1151 l_flex_value_set_id NUMBER;
1152 l_validation_type VARCHAR2(1);
1153 BEGIN
1154 SELECT flex_value_set_id, validation_type
1155 INTO l_flex_value_set_id, l_validation_type
1156 FROM fnd_flex_value_sets
1157 WHERE flex_value_set_name = x_flex_value_set_name;
1158
1159 UPDATE fnd_flex_values_tl SET
1160 flex_value_meaning = Decode(l_validation_type,
1161 'X', Nvl(x_flex_value_meaning, flex_value_meaning),
1162 'Y', Nvl(x_flex_value_meaning, flex_value_meaning),
1163 flex_value_meaning),
1164 description = Nvl(x_description, description),
1165 last_update_date = x_who.last_update_date,
1166 last_updated_by = x_who.last_updated_by,
1167 last_update_login = x_who.last_update_login,
1168 source_lang = userenv('LANG')
1169 WHERE (flex_value_id =
1170 (SELECT flex_value_id
1171 FROM fnd_flex_values
1172 WHERE flex_value_set_id = l_flex_value_set_id
1173 AND flex_value = x_flex_value
1174 AND ((l_validation_type NOT IN ('D', 'Y')) OR
1175 (l_validation_type IN ('D', 'Y') AND
1176 ((parent_flex_value_low = x_parent_flex_value_low) OR
1177 (parent_flex_value_low IS NULL AND
1178 x_parent_flex_value_low IS NULL))))))
1179 AND userenv('LANG') in (language, source_lang);
1180 END translate_row;
1181
1182 end FND_FLEX_VALUES_PKG;