[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_VALUES_PKG
Source
1 package body FND_FLEX_VALUES_PKG as
2 /* $Header: AFFFVLSB.pls 120.1.12010000.1 2008/07/25 14:14:55 appldev 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
413 close c;
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 app_exception.raise_exception;
416 end if;
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)
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)))
510 AND ((recinfo.ATTRIBUTE36 = X_ATTRIBUTE36)
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,
645 STRUCTURED_HIERARCHY_LEVEL = X_STRUCTURED_HIERARCHY_LEVEL,
646 HIERARCHY_LEVEL = X_HIERARCHY_LEVEL,
647 COMPILED_VALUE_ATTRIBUTES = X_COMPILED_VALUE_ATTRIBUTES,
648 VALUE_CATEGORY = X_VALUE_CATEGORY,
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 end if;
707
708 update FND_FLEX_VALUES_TL set
709 FLEX_VALUE_MEANING = X_FLEX_VALUE_MEANING,
710 DESCRIPTION = X_DESCRIPTION,
711 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
712 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
713 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
714 SOURCE_LANG = userenv('LANG')
715 where FLEX_VALUE_ID = X_FLEX_VALUE_ID
716 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
717
718 if (sql%notfound) then
719 raise no_data_found;
720 end if;
721 end UPDATE_ROW;
722
723 procedure DELETE_ROW (
724 X_FLEX_VALUE_ID in NUMBER
725 ) is
726 begin
727 delete from FND_FLEX_VALUES_TL
728 where FLEX_VALUE_ID = X_FLEX_VALUE_ID;
729
730 if (sql%notfound) then
731 raise no_data_found;
732 end if;
733
734 delete from FND_FLEX_VALUES
735 where FLEX_VALUE_ID = X_FLEX_VALUE_ID;
736
737 if (sql%notfound) then
738 raise no_data_found;
739 end if;
740 end DELETE_ROW;
741
742 procedure ADD_LANGUAGE
743 is
744 begin
745 /* Mar/19/03 requested by Ric Ginsberg */
746 /* The following delete and update statements are commented out */
747 /* as a quick workaround to fix the time-consuming table handler issue */
748 /* Eventually we'll need to turn them into a separate fix_language procedure */
749 /*
750
751 delete from FND_FLEX_VALUES_TL T
752 where not exists
753 (select NULL
754 from FND_FLEX_VALUES B
755 where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
756 );
757
758 update FND_FLEX_VALUES_TL T set (
759 FLEX_VALUE_MEANING,
760 DESCRIPTION
761 ) = (select
762 B.FLEX_VALUE_MEANING,
763 B.DESCRIPTION
764 from FND_FLEX_VALUES_TL B
765 where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
766 and B.LANGUAGE = T.SOURCE_LANG)
767 where (
768 T.FLEX_VALUE_ID,
769 T.LANGUAGE
770 ) in (select
771 SUBT.FLEX_VALUE_ID,
772 SUBT.LANGUAGE
773 from FND_FLEX_VALUES_TL SUBB, FND_FLEX_VALUES_TL SUBT
774 where SUBB.FLEX_VALUE_ID = SUBT.FLEX_VALUE_ID
775 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
776 and (SUBB.FLEX_VALUE_MEANING <> SUBT.FLEX_VALUE_MEANING
777 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
778 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
779 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
780 ));
781 */
782
783 insert /*+ append parallel(tt) */ into FND_FLEX_VALUES_TL tt (
784 FLEX_VALUE_ID,
785 LAST_UPDATE_DATE,
786 LAST_UPDATED_BY,
787 CREATION_DATE,
788 CREATED_BY,
789 LAST_UPDATE_LOGIN,
790 DESCRIPTION,
791 FLEX_VALUE_MEANING,
792 LANGUAGE,
793 SOURCE_LANG
794 )
795 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
796 (select /*+ no_merge ordered parallel(b) */
797 B.FLEX_VALUE_ID,
798 B.LAST_UPDATE_DATE,
799 B.LAST_UPDATED_BY,
800 B.CREATION_DATE,
801 B.CREATED_BY,
802 B.LAST_UPDATE_LOGIN,
803 B.DESCRIPTION,
804 B.FLEX_VALUE_MEANING,
805 L.LANGUAGE_CODE,
806 B.SOURCE_LANG
807 from FND_FLEX_VALUES_TL B, FND_LANGUAGES L
808 where L.INSTALLED_FLAG in ('I', 'B')
809 and B.LANGUAGE = userenv('LANG')
810 ) v, FND_FLEX_VALUES_TL t
811 where t.flex_value_id(+) = v.flex_value_id
812 and t.language(+) = v.language_code
813 and t.flex_value_id is NULL;
814
815 end ADD_LANGUAGE;
816
817 PROCEDURE load_row
818 (x_flex_value_set_name IN VARCHAR2,
819 x_parent_flex_value_low IN VARCHAR2,
820 x_flex_value IN VARCHAR2,
821 x_who IN fnd_flex_loader_apis.who_type,
822 x_enabled_flag IN VARCHAR2,
823 x_summary_flag IN VARCHAR2,
824 x_start_date_active IN DATE,
825 x_end_date_active IN DATE,
826 x_parent_flex_value_high IN VARCHAR2,
827 x_structured_hierarchy_level IN NUMBER,
828 x_hierarchy_level IN VARCHAR2,
829 x_compiled_value_attributes IN VARCHAR2,
830 x_value_category IN VARCHAR2,
831 x_attribute1 IN VARCHAR2,
832 x_attribute2 IN VARCHAR2,
833 x_attribute3 IN VARCHAR2,
834 x_attribute4 IN VARCHAR2,
835 x_attribute5 IN VARCHAR2,
836 x_attribute6 IN VARCHAR2,
837 x_attribute7 IN VARCHAR2,
838 x_attribute8 IN VARCHAR2,
839 x_attribute9 IN VARCHAR2,
840 x_attribute10 IN VARCHAR2,
841 x_attribute11 IN VARCHAR2,
842 x_attribute12 IN VARCHAR2,
843 x_attribute13 IN VARCHAR2,
844 x_attribute14 IN VARCHAR2,
845 x_attribute15 IN VARCHAR2,
846 x_attribute16 IN VARCHAR2,
847 x_attribute17 IN VARCHAR2,
848 x_attribute18 IN VARCHAR2,
849 x_attribute19 IN VARCHAR2,
850 x_attribute20 IN VARCHAR2,
851 x_attribute21 IN VARCHAR2,
852 x_attribute22 IN VARCHAR2,
853 x_attribute23 IN VARCHAR2,
854 x_attribute24 IN VARCHAR2,
855 x_attribute25 IN VARCHAR2,
856 x_attribute26 IN VARCHAR2,
857 x_attribute27 IN VARCHAR2,
858 x_attribute28 IN VARCHAR2,
859 x_attribute29 IN VARCHAR2,
860 x_attribute30 IN VARCHAR2,
861 x_attribute31 IN VARCHAR2,
862 x_attribute32 IN VARCHAR2,
863 x_attribute33 IN VARCHAR2,
864 x_attribute34 IN VARCHAR2,
865 x_attribute35 IN VARCHAR2,
866 x_attribute36 IN VARCHAR2,
867 x_attribute37 IN VARCHAR2,
868 x_attribute38 IN VARCHAR2,
869 x_attribute39 IN VARCHAR2,
870 x_attribute40 IN VARCHAR2,
871 x_attribute41 IN VARCHAR2,
872 x_attribute42 IN VARCHAR2,
873 x_attribute43 IN VARCHAR2,
874 x_attribute44 IN VARCHAR2,
875 x_attribute45 IN VARCHAR2,
876 x_attribute46 IN VARCHAR2,
877 x_attribute47 IN VARCHAR2,
878 x_attribute48 IN VARCHAR2,
879 x_attribute49 IN VARCHAR2,
880 x_attribute50 IN VARCHAR2,
881 x_attribute_sort_order IN VARCHAR2,
882 x_flex_value_meaning IN VARCHAR2,
883 x_description IN VARCHAR2)
884 IS
885 l_flex_value_set_id NUMBER := NULL;
886 l_flex_value_id NUMBER;
887 l_validation_type VARCHAR2(1);
888 l_rowid VARCHAR2(64);
889 BEGIN
890 SELECT flex_value_set_id, validation_type
891 INTO l_flex_value_set_id, l_validation_type
892 FROM fnd_flex_value_sets
893 WHERE flex_value_set_name = x_flex_value_set_name;
894
895 BEGIN
896 IF (l_validation_type in ('D', 'Y')) THEN
897 SELECT flex_value_id
898 INTO l_flex_value_id
899 FROM fnd_flex_values
900 WHERE flex_value_set_id = l_flex_value_set_id
901 AND flex_value = x_flex_value
902 AND (parent_flex_value_low = x_parent_flex_value_low OR
903 (parent_flex_value_low IS NULL AND
904 x_parent_flex_value_low IS NULL));
905 ELSE
906 SELECT flex_value_id
907 INTO l_flex_value_id
908 FROM fnd_flex_values
909 WHERE flex_value_set_id = l_flex_value_set_id
910 AND flex_value = x_flex_value;
911 END IF;
912
913 fnd_flex_values_pkg.update_row
914 (X_FLEX_VALUE_ID => l_flex_value_id,
915 X_ATTRIBUTE_SORT_ORDER => x_attribute_sort_order,
916 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
917 X_FLEX_VALUE => x_flex_value,
918 X_ENABLED_FLAG => x_enabled_flag,
919 X_SUMMARY_FLAG => x_summary_flag,
920 X_START_DATE_ACTIVE => x_start_date_active,
921 X_END_DATE_ACTIVE => x_end_date_active,
922 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
923 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
924 X_STRUCTURED_HIERARCHY_LEVEL => x_structured_hierarchy_level,
925 X_HIERARCHY_LEVEL => x_hierarchy_level,
926 X_COMPILED_VALUE_ATTRIBUTES => x_compiled_value_attributes,
927 X_VALUE_CATEGORY => x_value_category,
928 X_ATTRIBUTE1 => x_attribute1,
929 X_ATTRIBUTE2 => x_attribute2,
930 X_ATTRIBUTE3 => x_attribute3,
931 X_ATTRIBUTE4 => x_attribute4,
932 X_ATTRIBUTE5 => x_attribute5,
933 X_ATTRIBUTE6 => x_attribute6,
934 X_ATTRIBUTE7 => x_attribute7,
935 X_ATTRIBUTE8 => x_attribute8,
936 X_ATTRIBUTE9 => x_attribute9,
937 X_ATTRIBUTE10 => x_attribute10,
938 X_ATTRIBUTE11 => x_attribute11,
939 X_ATTRIBUTE12 => x_attribute12,
940 X_ATTRIBUTE13 => x_attribute13,
941 X_ATTRIBUTE14 => x_attribute14,
942 X_ATTRIBUTE15 => x_attribute15,
943 X_ATTRIBUTE16 => x_attribute16,
944 X_ATTRIBUTE17 => x_attribute17,
945 X_ATTRIBUTE18 => x_attribute18,
946 X_ATTRIBUTE19 => x_attribute19,
947 X_ATTRIBUTE20 => x_attribute20,
948 X_ATTRIBUTE21 => x_attribute21,
949 X_ATTRIBUTE22 => x_attribute22,
950 X_ATTRIBUTE23 => x_attribute23,
951 X_ATTRIBUTE24 => x_attribute24,
952 X_ATTRIBUTE25 => x_attribute25,
953 X_ATTRIBUTE26 => x_attribute26,
954 X_ATTRIBUTE27 => x_attribute27,
955 X_ATTRIBUTE28 => x_attribute28,
956 X_ATTRIBUTE29 => x_attribute29,
957 X_ATTRIBUTE30 => x_attribute30,
958 X_ATTRIBUTE31 => x_attribute31,
959 X_ATTRIBUTE32 => x_attribute32,
960 X_ATTRIBUTE33 => x_attribute33,
961 X_ATTRIBUTE34 => x_attribute34,
962 X_ATTRIBUTE35 => x_attribute35,
963 X_ATTRIBUTE36 => x_attribute36,
964 X_ATTRIBUTE37 => x_attribute37,
965 X_ATTRIBUTE38 => x_attribute38,
966 X_ATTRIBUTE39 => x_attribute39,
967 X_ATTRIBUTE40 => x_attribute40,
968 X_ATTRIBUTE41 => x_attribute41,
969 X_ATTRIBUTE42 => x_attribute42,
970 X_ATTRIBUTE43 => x_attribute43,
971 X_ATTRIBUTE44 => x_attribute44,
972 X_ATTRIBUTE45 => x_attribute45,
973 X_ATTRIBUTE46 => x_attribute46,
974 X_ATTRIBUTE47 => x_attribute47,
975 X_ATTRIBUTE48 => x_attribute48,
976 X_ATTRIBUTE49 => x_attribute49,
977 X_ATTRIBUTE50 => x_attribute50,
978 X_FLEX_VALUE_MEANING => x_flex_value_meaning,
979 X_DESCRIPTION => x_description,
980 X_LAST_UPDATE_DATE => x_who.last_update_date,
981 X_LAST_UPDATED_BY => x_who.last_updated_by,
982 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
983 EXCEPTION
984 WHEN no_data_found THEN
985 SELECT fnd_flex_values_s.NEXTVAL
986 INTO l_flex_value_id
987 FROM dual;
988
989 fnd_flex_values_pkg.insert_row
990 (X_ROWID => l_rowid,
991 X_FLEX_VALUE_ID => l_flex_value_id,
992 X_ATTRIBUTE_SORT_ORDER => x_attribute_sort_order,
993 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
994 X_FLEX_VALUE => x_flex_value,
995 X_ENABLED_FLAG => x_enabled_flag,
996 X_SUMMARY_FLAG => x_summary_flag,
997 X_START_DATE_ACTIVE => x_start_date_active,
998 X_END_DATE_ACTIVE => x_end_date_active,
999 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
1000 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
1001 X_STRUCTURED_HIERARCHY_LEVEL => x_structured_hierarchy_level,
1002 X_HIERARCHY_LEVEL => x_hierarchy_level,
1003 X_COMPILED_VALUE_ATTRIBUTES => x_compiled_value_attributes,
1004 X_VALUE_CATEGORY => x_value_category,
1005 X_ATTRIBUTE1 => x_attribute1,
1006 X_ATTRIBUTE2 => x_attribute2,
1007 X_ATTRIBUTE3 => x_attribute3,
1008 X_ATTRIBUTE4 => x_attribute4,
1009 X_ATTRIBUTE5 => x_attribute5,
1010 X_ATTRIBUTE6 => x_attribute6,
1011 X_ATTRIBUTE7 => x_attribute7,
1012 X_ATTRIBUTE8 => x_attribute8,
1013 X_ATTRIBUTE9 => x_attribute9,
1014 X_ATTRIBUTE10 => x_attribute10,
1015 X_ATTRIBUTE11 => x_attribute11,
1016 X_ATTRIBUTE12 => x_attribute12,
1017 X_ATTRIBUTE13 => x_attribute13,
1018 X_ATTRIBUTE14 => x_attribute14,
1019 X_ATTRIBUTE15 => x_attribute15,
1020 X_ATTRIBUTE16 => x_attribute16,
1021 X_ATTRIBUTE17 => x_attribute17,
1022 X_ATTRIBUTE18 => x_attribute18,
1023 X_ATTRIBUTE19 => x_attribute19,
1024 X_ATTRIBUTE20 => x_attribute20,
1025 X_ATTRIBUTE21 => x_attribute21,
1026 X_ATTRIBUTE22 => x_attribute22,
1027 X_ATTRIBUTE23 => x_attribute23,
1028 X_ATTRIBUTE24 => x_attribute24,
1029 X_ATTRIBUTE25 => x_attribute25,
1030 X_ATTRIBUTE26 => x_attribute26,
1031 X_ATTRIBUTE27 => x_attribute27,
1032 X_ATTRIBUTE28 => x_attribute28,
1033 X_ATTRIBUTE29 => x_attribute29,
1034 X_ATTRIBUTE30 => x_attribute30,
1035 X_ATTRIBUTE31 => x_attribute31,
1036 X_ATTRIBUTE32 => x_attribute32,
1037 X_ATTRIBUTE33 => x_attribute33,
1038 X_ATTRIBUTE34 => x_attribute34,
1039 X_ATTRIBUTE35 => x_attribute35,
1040 X_ATTRIBUTE36 => x_attribute36,
1041 X_ATTRIBUTE37 => x_attribute37,
1042 X_ATTRIBUTE38 => x_attribute38,
1043 X_ATTRIBUTE39 => x_attribute39,
1044 X_ATTRIBUTE40 => x_attribute40,
1045 X_ATTRIBUTE41 => x_attribute41,
1046 X_ATTRIBUTE42 => x_attribute42,
1047 X_ATTRIBUTE43 => x_attribute43,
1048 X_ATTRIBUTE44 => x_attribute44,
1049 X_ATTRIBUTE45 => x_attribute45,
1050 X_ATTRIBUTE46 => x_attribute46,
1051 X_ATTRIBUTE47 => x_attribute47,
1052 X_ATTRIBUTE48 => x_attribute48,
1053 X_ATTRIBUTE49 => x_attribute49,
1054 X_ATTRIBUTE50 => x_attribute50,
1055 X_FLEX_VALUE_MEANING => x_flex_value_meaning,
1056 X_DESCRIPTION => x_description,
1057 X_CREATION_DATE => x_who.creation_date,
1058 X_CREATED_BY => x_who.created_by,
1059 X_LAST_UPDATE_DATE => x_who.last_update_date,
1060 X_LAST_UPDATED_BY => x_who.last_updated_by,
1061 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
1062 END;
1063 END load_row;
1064
1065 PROCEDURE translate_row
1066 (x_flex_value_set_name IN VARCHAR2,
1067 x_parent_flex_value_low IN VARCHAR2,
1068 x_flex_value IN VARCHAR2,
1069 x_who IN fnd_flex_loader_apis.who_type,
1070 x_flex_value_meaning IN VARCHAR2,
1071 x_description IN VARCHAR2)
1072 IS
1073 l_flex_value_set_id NUMBER;
1074 l_validation_type VARCHAR2(1);
1075 BEGIN
1076 SELECT flex_value_set_id, validation_type
1077 INTO l_flex_value_set_id, l_validation_type
1078 FROM fnd_flex_value_sets
1079 WHERE flex_value_set_name = x_flex_value_set_name;
1080
1081 UPDATE fnd_flex_values_tl SET
1082 flex_value_meaning = Decode(l_validation_type,
1083 'X', Nvl(x_flex_value_meaning, flex_value_meaning),
1084 'Y', Nvl(x_flex_value_meaning, flex_value_meaning),
1085 flex_value_meaning),
1086 description = Nvl(x_description, description),
1087 last_update_date = x_who.last_update_date,
1088 last_updated_by = x_who.last_updated_by,
1089 last_update_login = x_who.last_update_login,
1090 source_lang = userenv('LANG')
1091 WHERE (flex_value_id =
1092 (SELECT flex_value_id
1093 FROM fnd_flex_values
1094 WHERE flex_value_set_id = l_flex_value_set_id
1095 AND flex_value = x_flex_value
1096 AND ((l_validation_type NOT IN ('D', 'Y')) OR
1097 (l_validation_type IN ('D', 'Y') AND
1098 ((parent_flex_value_low = x_parent_flex_value_low) OR
1099 (parent_flex_value_low IS NULL AND
1100 x_parent_flex_value_low IS NULL))))))
1101 AND userenv('LANG') in (language, source_lang);
1102 END translate_row;
1103
1104 end FND_FLEX_VALUES_PKG;