[Home] [Help]
PACKAGE BODY: APPS.FND_LOOKUP_VALUES_PKG
Source
1 PACKAGE BODY FND_LOOKUP_VALUES_PKG as
2 /* $Header: AFLVMLUB.pls 120.3 2011/02/21 22:50:03 jvalenti 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 l_null varchar2(20) := fnd_lookup_values_pkg.null_char;
330
331 begin
332 -- Bug 2103124
333 if (X_SECURITY_GROUP_ID is NULL) then
334 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
335 else
336 sgid := X_SECURITY_GROUP_ID;
337 end if;
338
339 -- Update "non-translated" values in all languages
340
341 update FND_LOOKUP_VALUES A
342 set
343 A.TAG = X_TAG,
344 A.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
345 A.ATTRIBUTE1 = decode(x_attribute1,l_null,null, null,A.attribute1, x_attribute1) ,
346 A.ATTRIBUTE2 = decode(x_attribute2,l_null,null, null,A.attribute2, x_attribute2) ,
347 A.ATTRIBUTE3 = decode(x_attribute3,l_null,null, null,A.attribute3, x_attribute3) ,
348 A.ATTRIBUTE4 = decode(x_attribute4,l_null,null, null,A.attribute4, x_attribute4) ,
349 A.ATTRIBUTE5 = decode(x_attribute5,l_null,null, null,A.attribute5, x_attribute5) ,
350 A.ATTRIBUTE6 = decode(x_attribute6,l_null,null, null,A.attribute6, x_attribute6) ,
351 A.ATTRIBUTE7 = decode(x_attribute7,l_null,null, null,A.attribute7, x_attribute7) ,
352 A.ATTRIBUTE8 = decode(x_attribute8,l_null,null, null,A.attribute8, x_attribute8) ,
353 A.ATTRIBUTE9 = decode(x_attribute9,l_null,null, null,A.attribute9, x_attribute9) ,
354 A.ATTRIBUTE10 = decode(x_attribute10,l_null,null, null,A.attribute10, x_attribute10) ,
355 A.ATTRIBUTE11 = decode(x_attribute11,l_null,null, null,A.attribute11, x_attribute11) ,
356 A.ATTRIBUTE12 = decode(x_attribute12,l_null,null, null,A.attribute12, x_attribute12) ,
357 A.ATTRIBUTE13 = decode(x_attribute13,l_null,null, null,A.attribute13, x_attribute13) ,
358 A.ATTRIBUTE14 = decode(x_attribute14,l_null,null, null,A.attribute14, x_attribute14) ,
359 A.ATTRIBUTE15 = decode(x_attribute15,l_null,null, null,A.attribute15, x_attribute15) ,
360 A.ENABLED_FLAG = X_ENABLED_FLAG,
361 A.START_DATE_ACTIVE = X_START_DATE_ACTIVE,
362 A.END_DATE_ACTIVE = X_END_DATE_ACTIVE,
363 A.TERRITORY_CODE = X_TERRITORY_CODE,
364 A.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
365 A.LAST_UPDATED_BY = X_LAST_UPDATED_BY,
366 A.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
367 where A.LOOKUP_TYPE = X_LOOKUP_TYPE
368 and A.SECURITY_GROUP_ID = sgid
369 and A.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
370 and A.LOOKUP_CODE = X_LOOKUP_CODE;
371
372 -- Update "translated" values in current language
373 update FND_LOOKUP_VALUES set
374 MEANING = X_MEANING,
375 DESCRIPTION = X_DESCRIPTION,
376 SOURCE_LANG = userenv('LANG')
377 where LOOKUP_TYPE = X_LOOKUP_TYPE
378 and SECURITY_GROUP_ID = sgid
379 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
380 and LOOKUP_CODE = X_LOOKUP_CODE
381 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
382
383 if (sql%notfound) then
384 raise no_data_found;
385 end if;
386
387 begin
388 -- Calling WF_EVENT.RAISE per bug 3209508
389 -- Business Events need to be raised with any updates to the fnd lookups
390 -- Bug:6113227, added Lang Code parameter to key being used to raise the
391 -- workflow event.
392
393 select userenv('LANG')
394 into X_LANG
395 from dual;
396
397 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.update',
398 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
399 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
400 p_event_data => NULL,
401 p_parameters => NULL,
402 p_send_date => Sysdate);
403 exception
404 when others then
405 null;
406 end;
407 end UPDATE_ROW;
408
409 /* Overloaded */
410 procedure TRANSLATE_ROW (
411 X_LOOKUP_TYPE in VARCHAR2,
412 X_SECURITY_GROUP_ID in NUMBER default NULL,
413 X_VIEW_APPLICATION_ID in NUMBER,
414 X_OWNER in VARCHAR2,
415 X_MEANING in VARCHAR2,
416 X_DESCRIPTION in VARCHAR2,
417 X_LOOKUP_CODE in VARCHAR2
418 ) is
419 begin
420 TRANSLATE_ROW (
421 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
422 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
423 X_VIEW_APPLICATION_ID => X_VIEW_APPLICATION_ID,
424 X_OWNER => X_OWNER,
425 X_MEANING => X_MEANING,
426 X_DESCRIPTION => X_DESCRIPTION,
427 X_LOOKUP_CODE => X_LOOKUP_CODE,
428 X_LAST_UPDATE_DATE => null,
429 X_CUSTOM_MODE => null);
430 end TRANSLATE_ROW;
431
432 /* Overloaded */
433 procedure TRANSLATE_ROW (
434 X_LOOKUP_TYPE in VARCHAR2,
435 X_SECURITY_GROUP_ID in NUMBER default NULL,
436 X_VIEW_APPLICATION_ID in NUMBER,
437 X_OWNER in VARCHAR2,
438 X_MEANING in VARCHAR2,
439 X_DESCRIPTION in VARCHAR2,
440 X_LOOKUP_CODE in VARCHAR2,
441 X_LAST_UPDATE_DATE in VARCHAR2,
442 X_CUSTOM_MODE in VARCHAR2
443 ) is
444 sgid NUMBER; -- security group id, added for Bug 2103124
445 f_luby number; -- entity owner in file
446 f_ludate date; -- entity update date in file
447 db_luby number; -- entity owner in db
448 db_ludate date; -- entity update date in db
449 X_LANG VARCHAR2(2); -- LANG CODE for Cache Key parameter
450
451 begin
452 -- Bug 2103124
453 if (X_SECURITY_GROUP_ID is NULL) then
454 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
455 else
456 sgid := X_SECURITY_GROUP_ID;
457 end if;
458
459 -- Translate owner to file_last_updated_by
460 f_luby := fnd_load_util.owner_id(x_owner);
461
462 -- Translate char last_update_date to date
463 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
464
465 select LAST_UPDATED_BY, LAST_UPDATE_DATE
466 into db_luby, db_ludate
467 from fnd_lookup_values
468 where LOOKUP_TYPE = X_LOOKUP_TYPE
469 and SECURITY_GROUP_ID = sgid
470 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
471 and LOOKUP_CODE = X_LOOKUP_CODE
472 and LANGUAGE = userenv('LANG');
473
474 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
475 db_ludate, X_CUSTOM_MODE)) then
476 update FND_LOOKUP_VALUES set
477 MEANING = nvl(X_MEANING, meaning),
478 DESCRIPTION = nvl(X_DESCRIPTION, description),
479 LAST_UPDATE_DATE = f_ludate,
480 LAST_UPDATED_BY = f_luby,
481 LAST_UPDATE_LOGIN = 0,
482 SOURCE_LANG = userenv('LANG')
483 where LOOKUP_TYPE = X_LOOKUP_TYPE
484 and SECURITY_GROUP_ID = sgid
485 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
486 and LOOKUP_CODE = X_LOOKUP_CODE
487 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
488
489 begin
490 -- Calling WF_EVENT.RAISE per bug 3209508
491 -- Business Events need to be raised with any updates to the fnd lookups
492 -- Bug:6113227, added Lang Code parameter to key being used to raise the
493 -- workflow event.
494
495 select userenv('LANG')
496 into X_LANG
497 from dual;
498
499 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.update',
500 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
501 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
502 p_event_data => NULL,
503 p_parameters => NULL,
504 p_send_date => Sysdate);
505 exception
506 when others then
507 null;
508 end;
509
510 end if;
511 exception
512 when no_data_found then
513 null;
514 end TRANSLATE_ROW;
515
516 procedure DELETE_ROW (
517 X_LOOKUP_TYPE in VARCHAR2,
518 X_SECURITY_GROUP_ID in NUMBER default NULL,
519 X_VIEW_APPLICATION_ID in NUMBER,
520 X_LOOKUP_CODE in VARCHAR2
521 ) is
522 sgid NUMBER;
523 X_LANG VARCHAR2(2);
524 begin
525 -- Bug 2103124
526 if (X_SECURITY_GROUP_ID is NULL) then
527 sgid := FND_GLOBAL.SECURITY_GROUP_ID;
528 else
529 sgid := X_SECURITY_GROUP_ID;
530 end if;
531
532 delete from FND_LOOKUP_VALUES
533 where LOOKUP_TYPE = X_LOOKUP_TYPE
534 and SECURITY_GROUP_ID = sgid
535 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
536 and LOOKUP_CODE = X_LOOKUP_CODE;
537
538 if (sql%notfound) then
539 raise no_data_found;
540 end if;
541
542 begin
543 -- Calling WF_EVENT.RAISE per bug 3209508
544 -- Business Events need to be raised with any updates to the fnd lookups
545 -- Bug:6113227, added Lang Code parameter to key being used to raise the
546 -- workflow event.
547
548 select userenv('LANG')
549 into X_LANG
550 from dual;
551
552 wf_event.raise(p_event_name => 'oracle.apps.fnd.lookup.code.delete',
553 p_event_key => X_LOOKUP_TYPE||':'||X_VIEW_APPLICATION_ID||
554 ':'||X_SECURITY_GROUP_ID||':'||X_LANG||':'||X_LOOKUP_CODE,
555 p_event_data => NULL,
556 p_parameters => NULL,
557 p_send_date => Sysdate);
558 exception
559 when others then
560 null;
561 end;
562 end DELETE_ROW;
563
564 procedure ADD_LANGUAGE
565 is
566 begin
567 /* Mar/19/03 requested by Ric Ginsberg */
568 /* The following update statements are commented out */
569 /* as a quick workaround to fix the time-consuming table handler issue */
570 /* Eventually we'll need to turn them into a separate fix_language procedure */
571 /*
572
573 update FND_LOOKUP_VALUES T set (
574 MEANING,
575 DESCRIPTION
576 ) = (select
577 B.MEANING,
578 B.DESCRIPTION
579 from FND_LOOKUP_VALUES B
580 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
581 and B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
582 and B.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
583 and B.LOOKUP_CODE = T.LOOKUP_CODE
584 and B.LANGUAGE = T.SOURCE_LANG)
585 where (
586 T.LOOKUP_TYPE,
587 T.SECURITY_GROUP_ID,
588 T.VIEW_APPLICATION_ID,
589 T.LOOKUP_CODE,
590 T.LANGUAGE
591 ) in (select
592 SUBT.LOOKUP_TYPE,
593 SUBT.SECURITY_GROUP_ID,
594 SUBT.VIEW_APPLICATION_ID,
595 SUBT.LOOKUP_CODE,
596 SUBT.LANGUAGE
597 from FND_LOOKUP_VALUES SUBB, FND_LOOKUP_VALUES SUBT
598 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
599 and SUBB.SECURITY_GROUP_ID = SUBT.SECURITY_GROUP_ID
600 and SUBB.VIEW_APPLICATION_ID = SUBT.VIEW_APPLICATION_ID
601 and SUBB.LOOKUP_CODE = SUBT.LOOKUP_CODE
602 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
603 and (SUBB.MEANING <> SUBT.MEANING
604 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
605 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
606 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
607 ))
608 -- ***** BEGIN NEW CLAUSE FOR UPDATE *****
609 and not exists
610 (select null
611 from FND_LOOKUP_VALUES DUP
612 where DUP.LOOKUP_TYPE = T.LOOKUP_TYPE
613 and DUP.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
614 and DUP.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
615 and DUP.LANGUAGE = T.LANGUAGE
616 and (DUP.MEANING) =
617 (select
618 B.MEANING
619 from FND_LOOKUP_VALUES B
620 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
621 and B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
622 and B.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
623 and B.LOOKUP_CODE = T.LOOKUP_CODE
624 and B.LANGUAGE = T.SOURCE_LANG));
625 -- ***** END NEW CLAUSE FOR UPDATE *****
626
627 -- ***** NEW CODE FOR INSERT HERE *****
628 loop
629 update FND_LOOKUP_VALUES set
630 MEANING = '@'||MEANING
631 where (
632 LOOKUP_TYPE,
633 VIEW_APPLICATION_ID,
634 MEANING,
635 SECURITY_GROUP_ID,
636 LANGUAGE) in
637 (select
638 B.LOOKUP_TYPE,
639 B.VIEW_APPLICATION_ID,
640 B.MEANING,
641 B.SECURITY_GROUP_ID,
642 L.LANGUAGE_CODE
643 from FND_LOOKUP_VALUES B, FND_LANGUAGES L
644 where L.INSTALLED_FLAG in ('I', 'B')
645 and B.LANGUAGE = userenv('LANG')
646 and not exists
647 (select NULL
648 from FND_LOOKUP_VALUES T
649 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
650 and T.SECURITY_GROUP_ID = B.SECURITY_GROUP_ID
651 and T.VIEW_APPLICATION_ID = B.VIEW_APPLICATION_ID
652 and T.LOOKUP_CODE = B.LOOKUP_CODE
653 and T.LANGUAGE = L.LANGUAGE_CODE));
654
655 exit when SQL%ROWCOUNT = 0;
656 end loop;
657 -- ***** END CODE FOR INSERT HERE *****
658 */
659
660 insert into FND_LOOKUP_VALUES (
661 TAG,
662 ATTRIBUTE_CATEGORY,
663 ATTRIBUTE1,
664 ATTRIBUTE2,
665 ATTRIBUTE3,
666 ATTRIBUTE4,
667 LOOKUP_TYPE,
668 LOOKUP_CODE,
669 MEANING,
670 DESCRIPTION,
671 ENABLED_FLAG,
672 START_DATE_ACTIVE,
673 END_DATE_ACTIVE,
674 CREATED_BY,
675 CREATION_DATE,
676 LAST_UPDATED_BY,
677 LAST_UPDATE_LOGIN,
678 LAST_UPDATE_DATE,
679 SECURITY_GROUP_ID,
680 VIEW_APPLICATION_ID,
681 TERRITORY_CODE,
682 ATTRIBUTE5,
683 ATTRIBUTE6,
684 ATTRIBUTE7,
685 ATTRIBUTE8,
686 ATTRIBUTE9,
687 ATTRIBUTE10,
688 ATTRIBUTE11,
689 ATTRIBUTE12,
690 ATTRIBUTE13,
691 ATTRIBUTE14,
692 ATTRIBUTE15,
693 LANGUAGE,
694 SOURCE_LANG
695 ) select
696 B.TAG,
697 B.ATTRIBUTE_CATEGORY,
698 B.ATTRIBUTE1,
699 B.ATTRIBUTE2,
700 B.ATTRIBUTE3,
701 B.ATTRIBUTE4,
702 B.LOOKUP_TYPE,
703 B.LOOKUP_CODE,
704 B.MEANING,
705 B.DESCRIPTION,
706 B.ENABLED_FLAG,
707 B.START_DATE_ACTIVE,
708 B.END_DATE_ACTIVE,
709 B.CREATED_BY,
710 B.CREATION_DATE,
711 B.LAST_UPDATED_BY,
712 B.LAST_UPDATE_LOGIN,
713 B.LAST_UPDATE_DATE,
714 B.SECURITY_GROUP_ID,
715 B.VIEW_APPLICATION_ID,
716 B.TERRITORY_CODE,
717 B.ATTRIBUTE5,
718 B.ATTRIBUTE6,
719 B.ATTRIBUTE7,
720 B.ATTRIBUTE8,
721 B.ATTRIBUTE9,
722 B.ATTRIBUTE10,
723 B.ATTRIBUTE11,
724 B.ATTRIBUTE12,
725 B.ATTRIBUTE13,
726 B.ATTRIBUTE14,
727 B.ATTRIBUTE15,
728 L.LANGUAGE_CODE,
729 B.SOURCE_LANG
730 from FND_LOOKUP_VALUES B, FND_LANGUAGES L
731 where L.INSTALLED_FLAG in ('I', 'B')
732 and B.LANGUAGE = userenv('LANG')
733 and not exists
734 (select NULL
735 from FND_LOOKUP_VALUES T
736 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
737 and T.SECURITY_GROUP_ID = B.SECURITY_GROUP_ID
738 and T.VIEW_APPLICATION_ID = B.VIEW_APPLICATION_ID
739 and T.LOOKUP_CODE = B.LOOKUP_CODE
740 and T.LANGUAGE = L.LANGUAGE_CODE);
741
742 end ADD_LANGUAGE;
743
744 procedure Load_Row (
745 x_lookup_type in varchar2,
746 x_view_appsname in varchar2,
747 x_lookup_code in varchar2,
748 x_enabled_flag in varchar2,
749 x_start_date_active in varchar2,
750 x_end_date_active in varchar2,
751 x_territory_code in varchar2,
752 x_tag in varchar2,
753 x_attribute_category in varchar2,
754 x_attribute1 in varchar2,
755 x_attribute2 in varchar2,
756 x_attribute3 in varchar2,
757 x_attribute4 in varchar2,
758 x_attribute5 in varchar2,
759 x_attribute6 in varchar2,
760 x_attribute7 in varchar2,
761 x_attribute8 in varchar2,
762 x_attribute9 in varchar2,
763 x_attribute10 in varchar2,
764 x_attribute11 in varchar2,
765 x_attribute12 in varchar2,
766 x_attribute13 in varchar2,
767 x_attribute14 in varchar2,
768 x_attribute15 in varchar2,
769 x_last_update_date in varchar2,
770 x_owner in varchar2,
771 x_meaning in varchar2,
772 x_description in varchar2,
773 x_security_group in varchar2,
774 x_custom_mode in varchar2)
775 is
776 view_appid number;
777 user_id number;
778 row_id varchar2(64);
779 f_luby number; -- entity owner in file
780 f_ludate date; -- entity update date in file
781 db_luby number; -- entity owner in db
782 db_ludate date; -- entity update date in db
783
784 cursor secgrp_curs is
785 select SG.SECURITY_GROUP_ID
786 from FND_LOOKUP_TYPES LT, FND_SECURITY_GROUPS SG
787 where LT.LOOKUP_TYPE = x_lookup_type
788 and LT.VIEW_APPLICATION_ID = view_appid
789 and LT.SECURITY_GROUP_ID = SG.SECURITY_GROUP_ID
790 and SG.SECURITY_GROUP_KEY like nvl(x_security_group, 'STANDARD');
791
792 begin
793
794 select APPLICATION_ID
795 into view_appid
796 from FND_APPLICATION
797 where APPLICATION_SHORT_NAME = x_view_appsname;
798
799 -- Translate owner to file_last_updated_by
800 f_luby := fnd_load_util.owner_id(x_owner);
801
802 -- Translate char last_update_date to date
803 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
804
805 for secgrp in secgrp_curs loop
806 -- check the db last update fields for each record in the cursor
807 begin
808 select LAST_UPDATED_BY, LAST_UPDATE_DATE
809 into db_luby, db_ludate
810 from fnd_lookup_values_vl
811 where LOOKUP_TYPE = X_LOOKUP_TYPE
812 and SECURITY_GROUP_ID = secgrp.security_group_id
813 and VIEW_APPLICATION_ID = view_appid
814 and LOOKUP_CODE = X_LOOKUP_CODE;
815
816 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
817 db_ludate, X_CUSTOM_MODE)) then
818 Fnd_Lookup_Values_Pkg.Update_Row (
819 X_LOOKUP_TYPE => x_lookup_type,
820 X_SECURITY_GROUP_ID => secgrp.security_group_id,
821 X_VIEW_APPLICATION_ID => view_appid,
822 X_LOOKUP_CODE => x_lookup_code,
823 X_TAG => x_tag,
824 X_ATTRIBUTE_CATEGORY => x_attribute_category,
825 X_ATTRIBUTE1 => x_attribute1,
826 X_ATTRIBUTE2 => x_attribute2,
827 X_ATTRIBUTE3 => x_attribute3,
828 X_ATTRIBUTE4 => x_attribute4,
829 X_ENABLED_FLAG => x_enabled_flag,
830 X_START_DATE_ACTIVE => to_date(x_start_date_active,
831 'YYYY/MM/DD'),
832 X_END_DATE_ACTIVE => to_date(x_end_date_active,
833 'YYYY/MM/DD'),
834 X_TERRITORY_CODE => x_territory_code,
835 X_ATTRIBUTE5 => x_attribute5,
836 X_ATTRIBUTE6 => x_attribute6,
837 X_ATTRIBUTE7 => x_attribute7,
838 X_ATTRIBUTE8 => x_attribute8,
839 X_ATTRIBUTE9 => x_attribute9,
840 X_ATTRIBUTE10 => x_attribute10,
841 X_ATTRIBUTE11 => x_attribute11,
842 X_ATTRIBUTE12 => x_attribute12,
843 X_ATTRIBUTE13 => x_attribute13,
844 X_ATTRIBUTE14 => x_attribute14,
845 X_ATTRIBUTE15 => x_attribute15,
846 X_MEANING => x_meaning,
847 X_DESCRIPTION => x_description,
848 X_LAST_UPDATE_DATE => f_ludate,
849 X_LAST_UPDATED_BY => f_luby,
850 X_LAST_UPDATE_LOGIN => 0);
851 end if;
852
853 exception
854 when no_data_found then
855 Fnd_Lookup_Values_Pkg.Insert_Row(
856 X_ROWID => row_id,
857 X_LOOKUP_TYPE => x_lookup_type,
858 X_SECURITY_GROUP_ID => secgrp.security_group_id,
859 X_VIEW_APPLICATION_ID => view_appid,
860 X_LOOKUP_CODE => x_lookup_code,
861 X_TAG => x_tag,
862 X_ATTRIBUTE_CATEGORY => x_attribute_category,
863 X_ATTRIBUTE1 => x_attribute1,
864 X_ATTRIBUTE2 => x_attribute2,
865 X_ATTRIBUTE3 => x_attribute3,
866 X_ATTRIBUTE4 => x_attribute4,
867 X_ENABLED_FLAG => x_enabled_flag,
868 X_START_DATE_ACTIVE => to_date(x_start_date_active,
869 'YYYY/MM/DD'),
870 X_END_DATE_ACTIVE => to_date(x_end_date_active,
871 'YYYY/MM/DD'),
872 X_TERRITORY_CODE => x_territory_code,
873 X_ATTRIBUTE5 => x_attribute5,
874 X_ATTRIBUTE6 => x_attribute6,
875 X_ATTRIBUTE7 => x_attribute7,
876 X_ATTRIBUTE8 => x_attribute8,
877 X_ATTRIBUTE9 => x_attribute9,
878 X_ATTRIBUTE10 => x_attribute10,
879 X_ATTRIBUTE11 => x_attribute11,
880 X_ATTRIBUTE12 => x_attribute12,
881 X_ATTRIBUTE13 => x_attribute13,
882 X_ATTRIBUTE14 => x_attribute14,
883 X_ATTRIBUTE15 => x_attribute15,
884 X_MEANING => x_meaning,
885 X_DESCRIPTION => x_description,
886 X_CREATION_DATE => f_ludate,
887 X_CREATED_BY => f_luby,
888 X_LAST_UPDATE_DATE => f_ludate,
889 X_LAST_UPDATED_BY => f_luby,
890 X_LAST_UPDATE_LOGIN => 0);
891 end;
892 end loop;
893
894 end Load_Row;
895
896 end FND_LOOKUP_VALUES_PKG;