[Home] [Help]
PACKAGE BODY: APPS.FND_LOOKUP_VALUES_PKG
Source
1 PACKAGE BODY FND_LOOKUP_VALUES_PKG as
2 /* $Header: AFLVMLUB.pls 120.2 2007/10/09 15:14:49 dggriffi ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_LOOKUP_TYPE in VARCHAR2,
7 X_SECURITY_GROUP_ID in NUMBER default NULL,
8 X_VIEW_APPLICATION_ID in NUMBER,
9 X_LOOKUP_CODE in VARCHAR2,
10 X_TAG in VARCHAR2,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ENABLED_FLAG in VARCHAR2,
17 X_START_DATE_ACTIVE in DATE,
18 X_END_DATE_ACTIVE in DATE,
19 X_TERRITORY_CODE in VARCHAR2,
20 X_ATTRIBUTE5 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ATTRIBUTE7 in VARCHAR2,
23 X_ATTRIBUTE8 in VARCHAR2,
24 X_ATTRIBUTE9 in VARCHAR2,
25 X_ATTRIBUTE10 in VARCHAR2,
26 X_ATTRIBUTE11 in VARCHAR2,
27 X_ATTRIBUTE12 in VARCHAR2,
28 X_ATTRIBUTE13 in VARCHAR2,
29 X_ATTRIBUTE14 in VARCHAR2,
30 X_ATTRIBUTE15 in VARCHAR2,
31 X_MEANING in VARCHAR2,
32 X_DESCRIPTION in VARCHAR2,
33 X_CREATION_DATE in DATE,
34 X_CREATED_BY in NUMBER,
35 X_LAST_UPDATE_DATE in DATE,
36 X_LAST_UPDATED_BY in NUMBER,
37 X_LAST_UPDATE_LOGIN in NUMBER
38 ) is
39 sgid NUMBER;
40 X_LANG VARCHAR2(2);
41
42 cursor C is select ROWID from FND_LOOKUP_VALUES
43 where LOOKUP_TYPE = X_LOOKUP_TYPE
44 and SECURITY_GROUP_ID = sgid
45 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
46 and LOOKUP_CODE = X_LOOKUP_CODE
47 and LANGUAGE = userenv('LANG');
48 begin
49 -- Bug 2103124
50 if (X_SECURITY_GROUP_ID is NULL) then
51 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
52 else
53 sgid := X_SECURITY_GROUP_ID;
54 end if;
55
56 insert into FND_LOOKUP_VALUES (
57 TAG,
58 ATTRIBUTE_CATEGORY,
59 ATTRIBUTE1,
60 ATTRIBUTE2,
61 ATTRIBUTE3,
62 ATTRIBUTE4,
63 LOOKUP_TYPE,
64 LOOKUP_CODE,
65 MEANING,
66 DESCRIPTION,
67 ENABLED_FLAG,
68 START_DATE_ACTIVE,
69 END_DATE_ACTIVE,
70 CREATED_BY,
71 CREATION_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN,
74 LAST_UPDATE_DATE,
75 SECURITY_GROUP_ID,
76 VIEW_APPLICATION_ID,
77 TERRITORY_CODE,
78 ATTRIBUTE5,
79 ATTRIBUTE6,
80 ATTRIBUTE7,
81 ATTRIBUTE8,
82 ATTRIBUTE9,
83 ATTRIBUTE10,
84 ATTRIBUTE11,
85 ATTRIBUTE12,
86 ATTRIBUTE13,
87 ATTRIBUTE14,
88 ATTRIBUTE15,
89 LANGUAGE,
90 SOURCE_LANG
91 ) select
92 X_TAG,
93 X_ATTRIBUTE_CATEGORY,
94 X_ATTRIBUTE1,
95 X_ATTRIBUTE2,
96 X_ATTRIBUTE3,
97 X_ATTRIBUTE4,
98 X_LOOKUP_TYPE,
99 X_LOOKUP_CODE,
100 X_MEANING,
101 X_DESCRIPTION,
102 X_ENABLED_FLAG,
103 X_START_DATE_ACTIVE,
104 X_END_DATE_ACTIVE,
105 X_CREATED_BY,
106 X_CREATION_DATE,
107 X_LAST_UPDATED_BY,
108 X_LAST_UPDATE_LOGIN,
109 X_LAST_UPDATE_DATE,
110 sgid,
111 X_VIEW_APPLICATION_ID,
112 X_TERRITORY_CODE,
113 X_ATTRIBUTE5,
114 X_ATTRIBUTE6,
115 X_ATTRIBUTE7,
116 X_ATTRIBUTE8,
117 X_ATTRIBUTE9,
118 X_ATTRIBUTE10,
119 X_ATTRIBUTE11,
120 X_ATTRIBUTE12,
121 X_ATTRIBUTE13,
122 X_ATTRIBUTE14,
123 X_ATTRIBUTE15,
124 L.LANGUAGE_CODE,
125 userenv('LANG')
126 from FND_LANGUAGES L
127 where L.INSTALLED_FLAG in ('I', 'B')
128 and not exists
129 (select NULL
130 from FND_LOOKUP_VALUES T
131 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
132 and T.SECURITY_GROUP_ID = sgid
133 and T.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
134 and T.LOOKUP_CODE = X_LOOKUP_CODE
135 and T.LANGUAGE = L.LANGUAGE_CODE);
136
137 open c;
138 fetch c into X_ROWID;
139 if (c%notfound) then
140 close c;
141 raise no_data_found;
142 end if;
143 close c;
144
145 begin
146 -- Calling WF_EVENT.RAISE per bug 3209508
147 -- Business Events need to be raised with any updates to the fnd lookups
148 -- Bug:6113227, added Lang Code parameter to key being used to raise the
149 -- workflow event.
150
151 select userenv('LANG')
152 into X_LANG
153 from dual;
154
155 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.insert',
156 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
157 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
158 p_event_data => NULL,
159 p_parameters => NULL,
160 p_send_date => Sysdate);
161 exception
162 when others then
163 null;
164 end;
165 end INSERT_ROW;
166
167 procedure LOCK_ROW (
168 X_LOOKUP_TYPE in VARCHAR2,
169 X_SECURITY_GROUP_ID in NUMBER default NULL,
170 X_VIEW_APPLICATION_ID in NUMBER,
171 X_LOOKUP_CODE in VARCHAR2,
172 X_TAG in VARCHAR2,
173 X_ATTRIBUTE_CATEGORY in VARCHAR2,
174 X_ATTRIBUTE1 in VARCHAR2,
175 X_ATTRIBUTE2 in VARCHAR2,
176 X_ATTRIBUTE3 in VARCHAR2,
177 X_ATTRIBUTE4 in VARCHAR2,
178 X_ENABLED_FLAG in VARCHAR2,
179 X_START_DATE_ACTIVE in DATE,
180 X_END_DATE_ACTIVE in DATE,
181 X_TERRITORY_CODE in VARCHAR2,
182 X_ATTRIBUTE5 in VARCHAR2,
183 X_ATTRIBUTE6 in VARCHAR2,
184 X_ATTRIBUTE7 in VARCHAR2,
185 X_ATTRIBUTE8 in VARCHAR2,
186 X_ATTRIBUTE9 in VARCHAR2,
187 X_ATTRIBUTE10 in VARCHAR2,
188 X_ATTRIBUTE11 in VARCHAR2,
189 X_ATTRIBUTE12 in VARCHAR2,
190 X_ATTRIBUTE13 in VARCHAR2,
191 X_ATTRIBUTE14 in VARCHAR2,
192 X_ATTRIBUTE15 in VARCHAR2,
193 X_MEANING in VARCHAR2,
194 X_DESCRIPTION in VARCHAR2
195 ) is
196 sgid NUMBER;
197 cursor c1 is select
198 TAG,
199 ATTRIBUTE_CATEGORY,
200 ATTRIBUTE1,
201 ATTRIBUTE2,
202 ATTRIBUTE3,
203 ATTRIBUTE4,
204 ENABLED_FLAG,
205 START_DATE_ACTIVE,
206 END_DATE_ACTIVE,
207 TERRITORY_CODE,
208 ATTRIBUTE5,
209 ATTRIBUTE6,
210 ATTRIBUTE7,
211 ATTRIBUTE8,
212 ATTRIBUTE9,
213 ATTRIBUTE10,
214 ATTRIBUTE11,
215 ATTRIBUTE12,
216 ATTRIBUTE13,
217 ATTRIBUTE14,
218 ATTRIBUTE15,
219 MEANING,
220 DESCRIPTION,
221 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
222 from FND_LOOKUP_VALUES
223 where LOOKUP_TYPE = X_LOOKUP_TYPE
224 and SECURITY_GROUP_ID = sgid
225 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
226 and LOOKUP_CODE = X_LOOKUP_CODE
227 for update of LOOKUP_TYPE nowait;
228
229 begin
230 -- Bug 2103124
231 if (X_SECURITY_GROUP_ID is NULL) then
232 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
233 else
234 sgid := X_SECURITY_GROUP_ID;
235 end if;
236
237 for tlinfo in c1 loop
238 if (tlinfo.BASELANG = 'Y') then
239 if ( (tlinfo.MEANING = X_MEANING)
240 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
241 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
242 AND ((tlinfo.TAG = X_TAG)
243 OR ((tlinfo.TAG is null) AND (X_TAG is null)))
244 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
245 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
246 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
247 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
248 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
249 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
250 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
251 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
252 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
253 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
254 AND (tlinfo.ENABLED_FLAG = X_ENABLED_FLAG)
255 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
256 OR ((tlinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
257 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
258 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
259 AND ((tlinfo.TERRITORY_CODE = X_TERRITORY_CODE)
260 OR ((tlinfo.TERRITORY_CODE is null) AND (X_TERRITORY_CODE is null)))
261 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
262 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
263 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
264 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
265 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
266 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
267 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
268 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
269 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
270 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
271 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
272 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
273 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
274 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
275 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
276 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
277 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
278 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
279 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
280 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
281 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
282 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
283 ) then
284 null;
285 else
286 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
287 app_exception.raise_exception;
288 end if;
289 end if;
290 end loop;
291 return;
292 end LOCK_ROW;
293
294 procedure UPDATE_ROW (
295 X_LOOKUP_TYPE in VARCHAR2,
296 X_SECURITY_GROUP_ID in NUMBER default NULL,
297 X_VIEW_APPLICATION_ID in NUMBER,
298 X_LOOKUP_CODE in VARCHAR2,
299 X_TAG in VARCHAR2,
300 X_ATTRIBUTE_CATEGORY in VARCHAR2,
301 X_ATTRIBUTE1 in VARCHAR2,
302 X_ATTRIBUTE2 in VARCHAR2,
303 X_ATTRIBUTE3 in VARCHAR2,
304 X_ATTRIBUTE4 in VARCHAR2,
305 X_ENABLED_FLAG in VARCHAR2,
306 X_START_DATE_ACTIVE in DATE,
307 X_END_DATE_ACTIVE in DATE,
308 X_TERRITORY_CODE in VARCHAR2,
309 X_ATTRIBUTE5 in VARCHAR2,
310 X_ATTRIBUTE6 in VARCHAR2,
311 X_ATTRIBUTE7 in VARCHAR2,
312 X_ATTRIBUTE8 in VARCHAR2,
313 X_ATTRIBUTE9 in VARCHAR2,
314 X_ATTRIBUTE10 in VARCHAR2,
315 X_ATTRIBUTE11 in VARCHAR2,
316 X_ATTRIBUTE12 in VARCHAR2,
317 X_ATTRIBUTE13 in VARCHAR2,
318 X_ATTRIBUTE14 in VARCHAR2,
319 X_ATTRIBUTE15 in VARCHAR2,
320 X_MEANING in VARCHAR2,
321 X_DESCRIPTION in VARCHAR2,
322 X_LAST_UPDATE_DATE in DATE,
323 X_LAST_UPDATED_BY in NUMBER,
324 X_LAST_UPDATE_LOGIN in NUMBER
325 ) is
326 sgid NUMBER;
327 X_LANG VARCHAR2(2);
328
329 begin
330 -- Bug 2103124
331 if (X_SECURITY_GROUP_ID is NULL) then
332 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
333 else
334 sgid := X_SECURITY_GROUP_ID;
335 end if;
336
337 -- Update "non-translated" values in all languages
338 update FND_LOOKUP_VALUES set
339 TAG = X_TAG,
340 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
341 ATTRIBUTE1 = X_ATTRIBUTE1,
342 ATTRIBUTE2 = X_ATTRIBUTE2,
343 ATTRIBUTE3 = X_ATTRIBUTE3,
344 ATTRIBUTE4 = X_ATTRIBUTE4,
345 ENABLED_FLAG = X_ENABLED_FLAG,
346 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
347 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
348 TERRITORY_CODE = X_TERRITORY_CODE,
349 ATTRIBUTE5 = X_ATTRIBUTE5,
350 ATTRIBUTE6 = X_ATTRIBUTE6,
351 ATTRIBUTE7 = X_ATTRIBUTE7,
352 ATTRIBUTE8 = X_ATTRIBUTE8,
353 ATTRIBUTE9 = X_ATTRIBUTE9,
354 ATTRIBUTE10 = X_ATTRIBUTE10,
355 ATTRIBUTE11 = X_ATTRIBUTE11,
356 ATTRIBUTE12 = X_ATTRIBUTE12,
357 ATTRIBUTE13 = X_ATTRIBUTE13,
358 ATTRIBUTE14 = X_ATTRIBUTE14,
359 ATTRIBUTE15 = X_ATTRIBUTE15,
360 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
361 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
362 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
363 where LOOKUP_TYPE = X_LOOKUP_TYPE
364 and SECURITY_GROUP_ID = sgid
365 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
366 and LOOKUP_CODE = X_LOOKUP_CODE;
367
368 -- Update "translated" values in current language
369 update FND_LOOKUP_VALUES set
370 MEANING = X_MEANING,
371 DESCRIPTION = X_DESCRIPTION,
372 SOURCE_LANG = userenv('LANG')
373 where LOOKUP_TYPE = X_LOOKUP_TYPE
374 and SECURITY_GROUP_ID = sgid
375 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
376 and LOOKUP_CODE = X_LOOKUP_CODE
377 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
378
379 if (sql%notfound) then
380 raise no_data_found;
381 end if;
382
383 begin
384 -- Calling WF_EVENT.RAISE per bug 3209508
385 -- Business Events need to be raised with any updates to the fnd lookups
386 -- Bug:6113227, added Lang Code parameter to key being used to raise the
387 -- workflow event.
388
389 select userenv('LANG')
390 into X_LANG
391 from dual;
392
393 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.update',
394 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
395 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
396 p_event_data => NULL,
397 p_parameters => NULL,
398 p_send_date => Sysdate);
399 exception
400 when others then
401 null;
402 end;
403 end UPDATE_ROW;
404
405 /* Overloaded */
406 procedure TRANSLATE_ROW (
407 X_LOOKUP_TYPE in VARCHAR2,
408 X_SECURITY_GROUP_ID in NUMBER default NULL,
409 X_VIEW_APPLICATION_ID in NUMBER,
410 X_OWNER in VARCHAR2,
411 X_MEANING in VARCHAR2,
412 X_DESCRIPTION in VARCHAR2,
413 X_LOOKUP_CODE in VARCHAR2
414 ) is
415 begin
416 TRANSLATE_ROW (
417 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
418 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
419 X_VIEW_APPLICATION_ID => X_VIEW_APPLICATION_ID,
420 X_OWNER => X_OWNER,
421 X_MEANING => X_MEANING,
422 X_DESCRIPTION => X_DESCRIPTION,
423 X_LOOKUP_CODE => X_LOOKUP_CODE,
424 X_LAST_UPDATE_DATE => null,
425 X_CUSTOM_MODE => null);
426 end TRANSLATE_ROW;
427
428 /* Overloaded */
429 procedure TRANSLATE_ROW (
430 X_LOOKUP_TYPE in VARCHAR2,
431 X_SECURITY_GROUP_ID in NUMBER default NULL,
432 X_VIEW_APPLICATION_ID in NUMBER,
433 X_OWNER in VARCHAR2,
434 X_MEANING in VARCHAR2,
435 X_DESCRIPTION in VARCHAR2,
436 X_LOOKUP_CODE in VARCHAR2,
437 X_LAST_UPDATE_DATE in VARCHAR2,
438 X_CUSTOM_MODE in VARCHAR2
439 ) is
440 sgid NUMBER; -- security group id, added for Bug 2103124
441 f_luby number; -- entity owner in file
442 f_ludate date; -- entity update date in file
443 db_luby number; -- entity owner in db
444 db_ludate date; -- entity update date in db
445 X_LANG VARCHAR2(2); -- LANG CODE for Cache Key parameter
446
447 begin
448 -- Bug 2103124
449 if (X_SECURITY_GROUP_ID is NULL) then
450 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
451 else
452 sgid := X_SECURITY_GROUP_ID;
453 end if;
454
455 -- Translate owner to file_last_updated_by
456 f_luby := fnd_load_util.owner_id(x_owner);
457
458 -- Translate char last_update_date to date
459 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
460
461 select LAST_UPDATED_BY, LAST_UPDATE_DATE
462 into db_luby, db_ludate
463 from fnd_lookup_values
464 where LOOKUP_TYPE = X_LOOKUP_TYPE
465 and SECURITY_GROUP_ID = sgid
466 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
467 and LOOKUP_CODE = X_LOOKUP_CODE
468 and LANGUAGE = userenv('LANG');
469
470 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
471 db_ludate, X_CUSTOM_MODE)) then
472 update FND_LOOKUP_VALUES set
473 MEANING = nvl(X_MEANING, meaning),
474 DESCRIPTION = nvl(X_DESCRIPTION, description),
475 LAST_UPDATE_DATE = f_ludate,
476 LAST_UPDATED_BY = f_luby,
477 LAST_UPDATE_LOGIN = 0,
478 SOURCE_LANG = userenv('LANG')
479 where LOOKUP_TYPE = X_LOOKUP_TYPE
480 and SECURITY_GROUP_ID = sgid
481 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
482 and LOOKUP_CODE = X_LOOKUP_CODE
483 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
484
485 begin
486 -- Calling WF_EVENT.RAISE per bug 3209508
487 -- Business Events need to be raised with any updates to the fnd lookups
488 -- Bug:6113227, added Lang Code parameter to key being used to raise the
489 -- workflow event.
490
491 select userenv('LANG')
492 into X_LANG
493 from dual;
494
495 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.update',
496 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
497 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
498 p_event_data => NULL,
499 p_parameters => NULL,
500 p_send_date => Sysdate);
501 exception
502 when others then
503 null;
504 end;
505
506 end if;
507 exception
508 when no_data_found then
509 null;
510 end TRANSLATE_ROW;
511
512 procedure DELETE_ROW (
513 X_LOOKUP_TYPE in VARCHAR2,
514 X_SECURITY_GROUP_ID in NUMBER default NULL,
515 X_VIEW_APPLICATION_ID in NUMBER,
516 X_LOOKUP_CODE in VARCHAR2
517 ) is
518 sgid NUMBER;
519 X_LANG VARCHAR2(2);
520 begin
521 -- Bug 2103124
522 if (X_SECURITY_GROUP_ID is NULL) then
523 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
524 else
525 sgid := X_SECURITY_GROUP_ID;
526 end if;
527
528 delete from FND_LOOKUP_VALUES
529 where LOOKUP_TYPE = X_LOOKUP_TYPE
530 and SECURITY_GROUP_ID = sgid
531 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
532 and LOOKUP_CODE = X_LOOKUP_CODE;
533
534 if (sql%notfound) then
535 raise no_data_found;
536 end if;
537
538 begin
539 -- Calling WF_EVENT.RAISE per bug 3209508
540 -- Business Events need to be raised with any updates to the fnd lookups
541 -- Bug:6113227, added Lang Code parameter to key being used to raise the
542 -- workflow event.
543
544 select userenv('LANG')
545 into X_LANG
546 from dual;
547
548 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.delete',
549 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
550 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
551 p_event_data => NULL,
552 p_parameters => NULL,
553 p_send_date => Sysdate);
554 exception
555 when others then
556 null;
557 end;
558 end DELETE_ROW;
559
560 procedure ADD_LANGUAGE
561 is
562 begin
563 /* Mar/19/03 requested by Ric Ginsberg */
564 /* The following update statements are commented out */
565 /* as a quick workaround to fix the time-consuming table handler issue */
566 /* Eventually we'll need to turn them into a separate fix_language procedure */
567 /*
568
569 update FND_LOOKUP_VALUES T set (
570 MEANING,
571 DESCRIPTION
572 ) = (select
573 B.MEANING,
574 B.DESCRIPTION
575 from FND_LOOKUP_VALUES B
576 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
577 and B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
578 and B.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
579 and B.LOOKUP_CODE = T.LOOKUP_CODE
580 and B.LANGUAGE = T.SOURCE_LANG)
581 where (
582 T.LOOKUP_TYPE,
583 T.SECURITY_GROUP_ID,
584 T.VIEW_APPLICATION_ID,
585 T.LOOKUP_CODE,
586 T.LANGUAGE
587 ) in (select
588 SUBT.LOOKUP_TYPE,
589 SUBT.SECURITY_GROUP_ID,
590 SUBT.VIEW_APPLICATION_ID,
591 SUBT.LOOKUP_CODE,
592 SUBT.LANGUAGE
593 from FND_LOOKUP_VALUES SUBB, FND_LOOKUP_VALUES SUBT
594 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
595 and SUBB.SECURITY_GROUP_ID = SUBT.SECURITY_GROUP_ID
596 and SUBB.VIEW_APPLICATION_ID = SUBT.VIEW_APPLICATION_ID
597 and SUBB.LOOKUP_CODE = SUBT.LOOKUP_CODE
598 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
599 and (SUBB.MEANING <> SUBT.MEANING
600 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
601 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
602 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
603 ))
604 -- ***** BEGIN NEW CLAUSE FOR UPDATE *****
605 and not exists
606 (select null
607 from FND_LOOKUP_VALUES DUP
608 where DUP.LOOKUP_TYPE = T.LOOKUP_TYPE
609 and DUP.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
610 and DUP.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
611 and DUP.LANGUAGE = T.LANGUAGE
612 and (DUP.MEANING) =
613 (select
614 B.MEANING
615 from FND_LOOKUP_VALUES B
616 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
617 and B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
618 and B.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
619 and B.LOOKUP_CODE = T.LOOKUP_CODE
620 and B.LANGUAGE = T.SOURCE_LANG));
621 -- ***** END NEW CLAUSE FOR UPDATE *****
622
623 -- ***** NEW CODE FOR INSERT HERE *****
624 loop
625 update FND_LOOKUP_VALUES set
626 MEANING = '@'||MEANING
627 where (
628 LOOKUP_TYPE,
629 VIEW_APPLICATION_ID,
630 MEANING,
631 SECURITY_GROUP_ID,
632 LANGUAGE) in
633 (select
634 B.LOOKUP_TYPE,
635 B.VIEW_APPLICATION_ID,
636 B.MEANING,
637 B.SECURITY_GROUP_ID,
638 L.LANGUAGE_CODE
639 from FND_LOOKUP_VALUES B, FND_LANGUAGES L
640 where L.INSTALLED_FLAG in ('I', 'B')
641 and B.LANGUAGE = userenv('LANG')
642 and not exists
643 (select NULL
644 from FND_LOOKUP_VALUES T
645 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
646 and T.SECURITY_GROUP_ID = B.SECURITY_GROUP_ID
647 and T.VIEW_APPLICATION_ID = B.VIEW_APPLICATION_ID
648 and T.LOOKUP_CODE = B.LOOKUP_CODE
649 and T.LANGUAGE = L.LANGUAGE_CODE));
650
651 exit when SQL%ROWCOUNT = 0;
652 end loop;
653 -- ***** END CODE FOR INSERT HERE *****
654 */
655
656 insert into FND_LOOKUP_VALUES (
657 TAG,
658 ATTRIBUTE_CATEGORY,
659 ATTRIBUTE1,
660 ATTRIBUTE2,
661 ATTRIBUTE3,
662 ATTRIBUTE4,
663 LOOKUP_TYPE,
664 LOOKUP_CODE,
665 MEANING,
666 DESCRIPTION,
667 ENABLED_FLAG,
668 START_DATE_ACTIVE,
669 END_DATE_ACTIVE,
670 CREATED_BY,
671 CREATION_DATE,
672 LAST_UPDATED_BY,
673 LAST_UPDATE_LOGIN,
674 LAST_UPDATE_DATE,
675 SECURITY_GROUP_ID,
676 VIEW_APPLICATION_ID,
677 TERRITORY_CODE,
678 ATTRIBUTE5,
679 ATTRIBUTE6,
680 ATTRIBUTE7,
681 ATTRIBUTE8,
682 ATTRIBUTE9,
683 ATTRIBUTE10,
684 ATTRIBUTE11,
685 ATTRIBUTE12,
686 ATTRIBUTE13,
687 ATTRIBUTE14,
688 ATTRIBUTE15,
689 LANGUAGE,
690 SOURCE_LANG
691 ) select
692 B.TAG,
693 B.ATTRIBUTE_CATEGORY,
694 B.ATTRIBUTE1,
695 B.ATTRIBUTE2,
696 B.ATTRIBUTE3,
697 B.ATTRIBUTE4,
698 B.LOOKUP_TYPE,
699 B.LOOKUP_CODE,
700 B.MEANING,
701 B.DESCRIPTION,
702 B.ENABLED_FLAG,
703 B.START_DATE_ACTIVE,
704 B.END_DATE_ACTIVE,
705 B.CREATED_BY,
706 B.CREATION_DATE,
707 B.LAST_UPDATED_BY,
708 B.LAST_UPDATE_LOGIN,
709 B.LAST_UPDATE_DATE,
710 B.SECURITY_GROUP_ID,
711 B.VIEW_APPLICATION_ID,
712 B.TERRITORY_CODE,
713 B.ATTRIBUTE5,
714 B.ATTRIBUTE6,
715 B.ATTRIBUTE7,
716 B.ATTRIBUTE8,
717 B.ATTRIBUTE9,
718 B.ATTRIBUTE10,
719 B.ATTRIBUTE11,
720 B.ATTRIBUTE12,
721 B.ATTRIBUTE13,
722 B.ATTRIBUTE14,
723 B.ATTRIBUTE15,
724 L.LANGUAGE_CODE,
725 B.SOURCE_LANG
726 from FND_LOOKUP_VALUES B, FND_LANGUAGES L
727 where L.INSTALLED_FLAG in ('I', 'B')
728 and B.LANGUAGE = userenv('LANG')
729 and not exists
730 (select NULL
731 from FND_LOOKUP_VALUES T
732 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
733 and T.SECURITY_GROUP_ID = B.SECURITY_GROUP_ID
734 and T.VIEW_APPLICATION_ID = B.VIEW_APPLICATION_ID
735 and T.LOOKUP_CODE = B.LOOKUP_CODE
736 and T.LANGUAGE = L.LANGUAGE_CODE);
737
738 end ADD_LANGUAGE;
739
740 procedure Load_Row (
741 x_lookup_type in varchar2,
742 x_view_appsname in varchar2,
743 x_lookup_code in varchar2,
744 x_enabled_flag in varchar2,
745 x_start_date_active in varchar2,
746 x_end_date_active in varchar2,
747 x_territory_code in varchar2,
748 x_tag in varchar2,
749 x_attribute_category in varchar2,
750 x_attribute1 in varchar2,
751 x_attribute2 in varchar2,
752 x_attribute3 in varchar2,
753 x_attribute4 in varchar2,
754 x_attribute5 in varchar2,
755 x_attribute6 in varchar2,
756 x_attribute7 in varchar2,
757 x_attribute8 in varchar2,
758 x_attribute9 in varchar2,
759 x_attribute10 in varchar2,
760 x_attribute11 in varchar2,
761 x_attribute12 in varchar2,
762 x_attribute13 in varchar2,
763 x_attribute14 in varchar2,
764 x_attribute15 in varchar2,
765 x_last_update_date in varchar2,
766 x_owner in varchar2,
767 x_meaning in varchar2,
768 x_description in varchar2,
769 x_security_group in varchar2,
770 x_custom_mode in varchar2)
771 is
772 view_appid number;
773 user_id number;
774 row_id varchar2(64);
775 f_luby number; -- entity owner in file
776 f_ludate date; -- entity update date in file
777 db_luby number; -- entity owner in db
778 db_ludate date; -- entity update date in db
779
780 cursor secgrp_curs is
781 select SG.SECURITY_GROUP_ID
782 from FND_LOOKUP_TYPES LT, FND_SECURITY_GROUPS SG
783 where LT.LOOKUP_TYPE = x_lookup_type
784 and LT.VIEW_APPLICATION_ID = view_appid
785 and LT.SECURITY_GROUP_ID = SG.SECURITY_GROUP_ID
786 and SG.SECURITY_GROUP_KEY like nvl(x_security_group, 'STANDARD');
787
788 begin
789
790 select APPLICATION_ID
791 into view_appid
792 from FND_APPLICATION
793 where APPLICATION_SHORT_NAME = x_view_appsname;
794
795 -- Translate owner to file_last_updated_by
796 f_luby := fnd_load_util.owner_id(x_owner);
797
798 -- Translate char last_update_date to date
799 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
800
801 for secgrp in secgrp_curs loop
802 -- check the db last update fields for each record in the cursor
803 begin
804 select LAST_UPDATED_BY, LAST_UPDATE_DATE
805 into db_luby, db_ludate
806 from fnd_lookup_values_vl
807 where LOOKUP_TYPE = X_LOOKUP_TYPE
808 and SECURITY_GROUP_ID = secgrp.security_group_id
809 and VIEW_APPLICATION_ID = view_appid
810 and LOOKUP_CODE = X_LOOKUP_CODE;
811
812 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
813 db_ludate, X_CUSTOM_MODE)) then
814 Fnd_Lookup_Values_Pkg.Update_Row (
815 X_LOOKUP_TYPE => x_lookup_type,
816 X_SECURITY_GROUP_ID => secgrp.security_group_id,
817 X_VIEW_APPLICATION_ID => view_appid,
818 X_LOOKUP_CODE => x_lookup_code,
819 X_TAG => x_tag,
820 X_ATTRIBUTE_CATEGORY => x_attribute_category,
821 X_ATTRIBUTE1 => x_attribute1,
822 X_ATTRIBUTE2 => x_attribute2,
823 X_ATTRIBUTE3 => x_attribute3,
824 X_ATTRIBUTE4 => x_attribute4,
825 X_ENABLED_FLAG => x_enabled_flag,
826 X_START_DATE_ACTIVE => to_date(x_start_date_active,
827 'YYYY/MM/DD'),
828 X_END_DATE_ACTIVE => to_date(x_end_date_active,
829 'YYYY/MM/DD'),
830 X_TERRITORY_CODE => x_territory_code,
831 X_ATTRIBUTE5 => x_attribute5,
832 X_ATTRIBUTE6 => x_attribute6,
833 X_ATTRIBUTE7 => x_attribute7,
834 X_ATTRIBUTE8 => x_attribute8,
835 X_ATTRIBUTE9 => x_attribute9,
836 X_ATTRIBUTE10 => x_attribute10,
837 X_ATTRIBUTE11 => x_attribute11,
838 X_ATTRIBUTE12 => x_attribute12,
839 X_ATTRIBUTE13 => x_attribute13,
840 X_ATTRIBUTE14 => x_attribute14,
841 X_ATTRIBUTE15 => x_attribute15,
842 X_MEANING => x_meaning,
843 X_DESCRIPTION => x_description,
844 X_LAST_UPDATE_DATE => f_ludate,
845 X_LAST_UPDATED_BY => f_luby,
846 X_LAST_UPDATE_LOGIN => 0);
847 end if;
848
849 exception
850 when no_data_found then
851 Fnd_Lookup_Values_Pkg.Insert_Row(
852 X_ROWID => row_id,
853 X_LOOKUP_TYPE => x_lookup_type,
854 X_SECURITY_GROUP_ID => secgrp.security_group_id,
855 X_VIEW_APPLICATION_ID => view_appid,
856 X_LOOKUP_CODE => x_lookup_code,
857 X_TAG => x_tag,
858 X_ATTRIBUTE_CATEGORY => x_attribute_category,
859 X_ATTRIBUTE1 => x_attribute1,
860 X_ATTRIBUTE2 => x_attribute2,
861 X_ATTRIBUTE3 => x_attribute3,
862 X_ATTRIBUTE4 => x_attribute4,
863 X_ENABLED_FLAG => x_enabled_flag,
864 X_START_DATE_ACTIVE => to_date(x_start_date_active,
865 'YYYY/MM/DD'),
866 X_END_DATE_ACTIVE => to_date(x_end_date_active,
867 'YYYY/MM/DD'),
868 X_TERRITORY_CODE => x_territory_code,
869 X_ATTRIBUTE5 => x_attribute5,
870 X_ATTRIBUTE6 => x_attribute6,
871 X_ATTRIBUTE7 => x_attribute7,
872 X_ATTRIBUTE8 => x_attribute8,
873 X_ATTRIBUTE9 => x_attribute9,
874 X_ATTRIBUTE10 => x_attribute10,
875 X_ATTRIBUTE11 => x_attribute11,
876 X_ATTRIBUTE12 => x_attribute12,
877 X_ATTRIBUTE13 => x_attribute13,
878 X_ATTRIBUTE14 => x_attribute14,
879 X_ATTRIBUTE15 => x_attribute15,
880 X_MEANING => x_meaning,
881 X_DESCRIPTION => x_description,
882 X_CREATION_DATE => f_ludate,
883 X_CREATED_BY => f_luby,
884 X_LAST_UPDATE_DATE => f_ludate,
885 X_LAST_UPDATED_BY => f_luby,
886 X_LAST_UPDATE_LOGIN => 0);
887 end;
888 end loop;
889
890 end Load_Row;
891
892 end FND_LOOKUP_VALUES_PKG;