[Home] [Help]
PACKAGE BODY: APPS.FA_LOOKUPS_PKG
Source
1 package body FA_LOOKUPS_PKG as
2 /* $Header: faxilob.pls 120.13 2011/03/11 02:37:44 saalampa ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_LOOKUP_CODE in VARCHAR2,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
26 X_MEANING in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
34 cursor C is select ROWID from FA_LOOKUPS_B
35 where LOOKUP_TYPE = X_LOOKUP_TYPE
36 and LOOKUP_CODE = X_LOOKUP_CODE
37 ;
38 begin
39 ad_zd_seed.prepare('FA_LOOKUPS_B');
40 ad_zd_seed.prepare('FA_LOOKUPS_TL');
41 insert into FA_LOOKUPS_B (
42 LOOKUP_TYPE,
43 LOOKUP_CODE,
44 ENABLED_FLAG,
45 START_DATE_ACTIVE,
46 END_DATE_ACTIVE,
47 ATTRIBUTE1,
48 ATTRIBUTE2,
49 ATTRIBUTE3,
50 ATTRIBUTE4,
51 ATTRIBUTE5,
52 ATTRIBUTE6,
53 ATTRIBUTE7,
54 ATTRIBUTE8,
55 ATTRIBUTE9,
56 ATTRIBUTE10,
57 ATTRIBUTE11,
58 ATTRIBUTE12,
59 ATTRIBUTE13,
60 ATTRIBUTE14,
61 ATTRIBUTE15,
62 ATTRIBUTE_CATEGORY_CODE,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_LOOKUP_TYPE,
70 X_LOOKUP_CODE,
71 X_ENABLED_FLAG,
72 X_START_DATE_ACTIVE,
73 X_END_DATE_ACTIVE,
74 X_ATTRIBUTE1,
75 X_ATTRIBUTE2,
76 X_ATTRIBUTE3,
77 X_ATTRIBUTE4,
78 X_ATTRIBUTE5,
79 X_ATTRIBUTE6,
80 X_ATTRIBUTE7,
81 X_ATTRIBUTE8,
82 X_ATTRIBUTE9,
83 X_ATTRIBUTE10,
84 X_ATTRIBUTE11,
85 X_ATTRIBUTE12,
86 X_ATTRIBUTE13,
87 X_ATTRIBUTE14,
88 X_ATTRIBUTE15,
89 X_ATTRIBUTE_CATEGORY_CODE,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN
95 );
96
97 insert into FA_LOOKUPS_TL (
98 LOOKUP_TYPE,
99 LOOKUP_CODE,
100 MEANING,
101 DESCRIPTION,
102 LAST_UPDATE_DATE,
103 LAST_UPDATED_BY,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATE_LOGIN,
107 LANGUAGE,
108 SOURCE_LANG
109 ) select
110 X_LOOKUP_TYPE,
111 X_LOOKUP_CODE,
112 X_MEANING,
113 X_DESCRIPTION,
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 X_CREATED_BY,
117 X_CREATION_DATE,
118 X_LAST_UPDATE_LOGIN,
119 L.LANGUAGE_CODE,
120 userenv('LANG')
121 from FND_LANGUAGES L
122 where L.INSTALLED_FLAG in ('I', 'B')
123 and not exists
124 (select NULL
125 from FA_LOOKUPS_TL T
126 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
127 and T.LOOKUP_CODE = X_LOOKUP_CODE
128 and T.LANGUAGE = L.LANGUAGE_CODE);
129
130 open c;
131 fetch c into X_ROWID;
132 if (c%notfound) then
133 close c;
134 raise no_data_found;
135 end if;
136 close c;
137
138 end INSERT_ROW;
139
140 procedure LOCK_ROW (
141 X_LOOKUP_TYPE in VARCHAR2,
142 X_LOOKUP_CODE in VARCHAR2,
143 X_ENABLED_FLAG in VARCHAR2,
144 X_START_DATE_ACTIVE in DATE,
145 X_END_DATE_ACTIVE in DATE,
146 X_ATTRIBUTE1 in VARCHAR2,
147 X_ATTRIBUTE2 in VARCHAR2,
148 X_ATTRIBUTE3 in VARCHAR2,
149 X_ATTRIBUTE4 in VARCHAR2,
150 X_ATTRIBUTE5 in VARCHAR2,
151 X_ATTRIBUTE6 in VARCHAR2,
152 X_ATTRIBUTE7 in VARCHAR2,
153 X_ATTRIBUTE8 in VARCHAR2,
154 X_ATTRIBUTE9 in VARCHAR2,
155 X_ATTRIBUTE10 in VARCHAR2,
156 X_ATTRIBUTE11 in VARCHAR2,
157 X_ATTRIBUTE12 in VARCHAR2,
158 X_ATTRIBUTE13 in VARCHAR2,
159 X_ATTRIBUTE14 in VARCHAR2,
160 X_ATTRIBUTE15 in VARCHAR2,
161 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
162 X_MEANING in VARCHAR2,
163 X_DESCRIPTION in VARCHAR2
164 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
165 cursor c is select
166 ENABLED_FLAG,
167 START_DATE_ACTIVE,
168 END_DATE_ACTIVE,
169 ATTRIBUTE1,
170 ATTRIBUTE2,
171 ATTRIBUTE3,
172 ATTRIBUTE4,
173 ATTRIBUTE5,
174 ATTRIBUTE6,
175 ATTRIBUTE7,
176 ATTRIBUTE8,
177 ATTRIBUTE9,
178 ATTRIBUTE10,
179 ATTRIBUTE11,
180 ATTRIBUTE12,
181 ATTRIBUTE13,
182 ATTRIBUTE14,
183 ATTRIBUTE15,
184 ATTRIBUTE_CATEGORY_CODE
185 from FA_LOOKUPS_B
186 where LOOKUP_TYPE = X_LOOKUP_TYPE
187 and LOOKUP_CODE = X_LOOKUP_CODE
188 for update of LOOKUP_TYPE nowait;
189 recinfo c%rowtype;
190
191 cursor c1 is select
192 MEANING,
193 DESCRIPTION,
194 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
195 from FA_LOOKUPS_TL
196 where LOOKUP_TYPE = X_LOOKUP_TYPE
197 and LOOKUP_CODE = X_LOOKUP_CODE
198 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
199 for update of LOOKUP_TYPE nowait;
200 begin
201 open c;
202 fetch c into recinfo;
203 if (c%notfound) then
204 close c;
205 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 end if;
208 close c;
209 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
210 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
211 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
212 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
213 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
214 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
215 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
216 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
217 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
218 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
219 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
220 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
221 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
222 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
223 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
224 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
225 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
226 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
227 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
228 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
229 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
230 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
231 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
232 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
233 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
234 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
235 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
236 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
237 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
238 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
239 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
240 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
241 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
242 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
243 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
244 AND ((recinfo.ATTRIBUTE_CATEGORY_CODE = X_ATTRIBUTE_CATEGORY_CODE)
245 OR ((recinfo.ATTRIBUTE_CATEGORY_CODE is null) AND (X_ATTRIBUTE_CATEGORY_CODE is null)))
246 ) then
247 null;
248 else
249 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
250 app_exception.raise_exception;
251 end if;
252
253 for tlinfo in c1 loop
254 if (tlinfo.BASELANG = 'Y') then
255 if ( (tlinfo.MEANING = X_MEANING)
256 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
257 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
258 ) then
259 null;
260 else
261 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
262 app_exception.raise_exception;
263 end if;
264 end if;
265 end loop;
266 return;
267 end LOCK_ROW;
268
269 procedure UPDATE_ROW (
270 X_LOOKUP_TYPE in VARCHAR2,
271 X_LOOKUP_CODE in VARCHAR2,
272 X_ENABLED_FLAG in VARCHAR2,
273 X_START_DATE_ACTIVE in DATE,
274 X_END_DATE_ACTIVE in DATE,
275 X_ATTRIBUTE1 in VARCHAR2,
276 X_ATTRIBUTE2 in VARCHAR2,
277 X_ATTRIBUTE3 in VARCHAR2,
278 X_ATTRIBUTE4 in VARCHAR2,
279 X_ATTRIBUTE5 in VARCHAR2,
280 X_ATTRIBUTE6 in VARCHAR2,
281 X_ATTRIBUTE7 in VARCHAR2,
282 X_ATTRIBUTE8 in VARCHAR2,
283 X_ATTRIBUTE9 in VARCHAR2,
284 X_ATTRIBUTE10 in VARCHAR2,
285 X_ATTRIBUTE11 in VARCHAR2,
286 X_ATTRIBUTE12 in VARCHAR2,
287 X_ATTRIBUTE13 in VARCHAR2,
288 X_ATTRIBUTE14 in VARCHAR2,
289 X_ATTRIBUTE15 in VARCHAR2,
290 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
291 X_MEANING in VARCHAR2,
292 X_DESCRIPTION in VARCHAR2,
293 X_LAST_UPDATE_DATE in DATE,
294 X_LAST_UPDATED_BY in NUMBER,
295 X_LAST_UPDATE_LOGIN in NUMBER
296 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
297 begin
298 ad_zd_seed.prepare('FA_LOOKUPS_B');
299 ad_zd_seed.prepare('FA_LOOKUPS_TL');
300 update FA_LOOKUPS_B set
301 ENABLED_FLAG = X_ENABLED_FLAG,
302 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
303 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
304 ATTRIBUTE1 = X_ATTRIBUTE1,
305 ATTRIBUTE2 = X_ATTRIBUTE2,
306 ATTRIBUTE3 = X_ATTRIBUTE3,
307 ATTRIBUTE4 = X_ATTRIBUTE4,
308 ATTRIBUTE5 = X_ATTRIBUTE5,
309 ATTRIBUTE6 = X_ATTRIBUTE6,
310 ATTRIBUTE7 = X_ATTRIBUTE7,
311 ATTRIBUTE8 = X_ATTRIBUTE8,
312 ATTRIBUTE9 = X_ATTRIBUTE9,
313 ATTRIBUTE10 = X_ATTRIBUTE10,
314 ATTRIBUTE11 = X_ATTRIBUTE11,
315 ATTRIBUTE12 = X_ATTRIBUTE12,
316 ATTRIBUTE13 = X_ATTRIBUTE13,
317 ATTRIBUTE14 = X_ATTRIBUTE14,
318 ATTRIBUTE15 = X_ATTRIBUTE15,
319 ATTRIBUTE_CATEGORY_CODE = X_ATTRIBUTE_CATEGORY_CODE,
320 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
321 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
322 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
323 where LOOKUP_TYPE = X_LOOKUP_TYPE
324 and LOOKUP_CODE = X_LOOKUP_CODE;
325
326 if (sql%notfound) then
327 raise no_data_found;
328 end if;
329
330 update FA_LOOKUPS_TL set
331 MEANING = X_MEANING,
332 DESCRIPTION = X_DESCRIPTION,
333 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
334 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
335 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
336 SOURCE_LANG = userenv('LANG')
337 where LOOKUP_TYPE = X_LOOKUP_TYPE
338 and LOOKUP_CODE = X_LOOKUP_CODE
339 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
340
341 if (sql%notfound) then
342 raise no_data_found;
343 end if;
344 end UPDATE_ROW;
345
346 procedure DELETE_ROW (
347 X_LOOKUP_TYPE in VARCHAR2,
348 X_LOOKUP_CODE in VARCHAR2
349 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
350 begin
351 ad_zd_seed.prepare('FA_LOOKUPS_B');
352 ad_zd_seed.prepare('FA_LOOKUPS_TL');
353 delete from FA_LOOKUPS_TL
354 where LOOKUP_TYPE = X_LOOKUP_TYPE
355 and LOOKUP_CODE = X_LOOKUP_CODE;
356
357 if (sql%notfound) then
358 raise no_data_found;
359 end if;
360
361 delete from FA_LOOKUPS_B
362 where LOOKUP_TYPE = X_LOOKUP_TYPE
363 and LOOKUP_CODE = X_LOOKUP_CODE;
364
365 if (sql%notfound) then
366 raise no_data_found;
367 end if;
368 end DELETE_ROW;
369
370 procedure ADD_LANGUAGE
371 is
372 begin
373 delete from FA_LOOKUPS_TL T
374 where not exists
375 (select NULL
376 from FA_LOOKUPS_B B
377 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
378 and B.LOOKUP_CODE = T.LOOKUP_CODE
379 );
380
381 update FA_LOOKUPS_TL T set (
382 MEANING,
383 DESCRIPTION
384 ) = (select
385 B.MEANING,
386 B.DESCRIPTION
387 from FA_LOOKUPS_TL B
388 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
389 and B.LOOKUP_CODE = T.LOOKUP_CODE
390 and B.LANGUAGE = T.SOURCE_LANG)
391 where (
392 T.LOOKUP_TYPE,
393 T.LOOKUP_CODE,
394 T.LANGUAGE
395 ) in (select
396 SUBT.LOOKUP_TYPE,
397 SUBT.LOOKUP_CODE,
398 SUBT.LANGUAGE
399 from FA_LOOKUPS_TL SUBB, FA_LOOKUPS_TL SUBT
400 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
401 and SUBB.LOOKUP_CODE = SUBT.LOOKUP_CODE
402 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
403 and (SUBB.MEANING <> SUBT.MEANING
404 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
405 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
406 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
407 ));
408
409 insert into FA_LOOKUPS_TL (
410 LOOKUP_TYPE,
411 LOOKUP_CODE,
412 MEANING,
413 DESCRIPTION,
414 LAST_UPDATE_DATE,
415 LAST_UPDATED_BY,
416 CREATED_BY,
417 CREATION_DATE,
418 LAST_UPDATE_LOGIN,
419 LANGUAGE,
420 SOURCE_LANG
421 ) select
422 B.LOOKUP_TYPE,
423 B.LOOKUP_CODE,
424 B.MEANING,
425 B.DESCRIPTION,
426 B.LAST_UPDATE_DATE,
427 B.LAST_UPDATED_BY,
428 B.CREATED_BY,
429 B.CREATION_DATE,
430 B.LAST_UPDATE_LOGIN,
431 L.LANGUAGE_CODE,
432 B.SOURCE_LANG
433 from FA_LOOKUPS_TL B, FND_LANGUAGES L
434 where L.INSTALLED_FLAG in ('I', 'B')
435 and B.LANGUAGE = userenv('LANG')
436 and not exists
437 (select NULL
438 from FA_LOOKUPS_TL T
439 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
440 and T.LOOKUP_CODE = B.LOOKUP_CODE
441 and T.LANGUAGE = L.LANGUAGE_CODE);
442 end ADD_LANGUAGE;
443
444 procedure LOAD_ROW (
445 X_LOOKUP_TYPE in VARCHAR2,
446 X_LOOKUP_CODE in VARCHAR2,
447 X_OWNER in VARCHAR2,
448 X_MEANING in VARCHAR2,
449 X_ENABLED_FLAG in VARCHAR2,
450 X_DESCRIPTION in VARCHAR2,
451 X_START_DATE_ACTIVE in DATE,
452 X_END_DATE_ACTIVE in DATE,
453 X_ATTRIBUTE1 in VARCHAR2,
454 X_ATTRIBUTE2 in VARCHAR2,
455 X_ATTRIBUTE3 in VARCHAR2,
456 X_ATTRIBUTE4 in VARCHAR2,
457 X_ATTRIBUTE5 in VARCHAR2,
458 X_ATTRIBUTE6 in VARCHAR2,
459 X_ATTRIBUTE7 in VARCHAR2,
460 X_ATTRIBUTE8 in VARCHAR2,
461 X_ATTRIBUTE9 in VARCHAR2,
462 X_ATTRIBUTE10 in VARCHAR2,
463 X_ATTRIBUTE11 in VARCHAR2,
464 X_ATTRIBUTE12 in VARCHAR2,
465 X_ATTRIBUTE13 in VARCHAR2,
466 X_ATTRIBUTE14 in VARCHAR2,
467 X_ATTRIBUTE15 in VARCHAR2,
468 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2
469 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
470
471 h_record_exists number(15);
472
473 user_id number;
474 row_id varchar2(64);
475
476 begin
477
478 if (X_Owner = 'SEED') then
479 user_id := 1;
480 else
481 user_id := 0;
482 end if;
483
484 select count(*)
485 into h_record_exists
486 from fa_lookups
487 where lookup_type = X_Lookup_Type
488 and lookup_code = X_Lookup_Code;
489
490 if (h_record_exists > 0) then
491 fa_lookups_pkg.update_row (
492 X_Lookup_Type => X_Lookup_Type,
493 X_Lookup_Code => X_Lookup_Code,
494 X_Enabled_Flag => X_Enabled_Flag,
495 X_Start_Date_Active => X_Start_Date_Active,
496 X_End_Date_Active => X_End_Date_Active,
497 X_Attribute1 => X_Attribute1,
498 X_Attribute2 => X_Attribute2,
499 X_Attribute3 => X_Attribute3,
500 X_Attribute4 => X_Attribute4,
501 X_Attribute5 => X_Attribute5,
502 X_Attribute6 => X_Attribute6,
503 X_Attribute7 => X_Attribute7,
504 X_Attribute8 => X_Attribute8,
505 X_Attribute9 => X_Attribute9,
506 X_Attribute10 => X_Attribute10,
507 X_Attribute11 => X_Attribute11,
508 X_Attribute12 => X_Attribute12,
509 X_Attribute13 => X_Attribute13,
510 X_Attribute14 => X_Attribute14,
511 X_Attribute15 => X_Attribute15,
512 X_Attribute_Category_Code => X_Attribute_Category_Code,
513 X_Meaning => X_Meaning,
514 X_Description => X_Description,
515 X_Last_Update_Date => sysdate,
516 X_Last_Updated_By => user_id,
517 X_Last_Update_Login => 0
518 , p_log_level_rec => p_log_level_rec);
519 else
520 fa_lookups_pkg.insert_row (
521 X_Rowid => row_id,
522 X_Lookup_Type => X_Lookup_Type,
523 X_Lookup_Code => X_Lookup_Code,
524 X_Enabled_Flag => X_Enabled_Flag,
525 X_Start_Date_Active => X_Start_Date_Active,
526 X_End_Date_Active => X_End_Date_Active,
527 X_Attribute1 => X_Attribute1,
528 X_Attribute2 => X_Attribute2,
529 X_Attribute3 => X_Attribute3,
530 X_Attribute4 => X_Attribute4,
531 X_Attribute5 => X_Attribute5,
532 X_Attribute6 => X_Attribute6,
533 X_Attribute7 => X_Attribute7,
534 X_Attribute8 => X_Attribute8,
535 X_Attribute9 => X_Attribute9,
536 X_Attribute10 => X_Attribute10,
537 X_Attribute11 => X_Attribute11,
538 X_Attribute12 => X_Attribute12,
539 X_Attribute13 => X_Attribute13,
540 X_Attribute14 => X_Attribute14,
541 X_Attribute15 => X_Attribute15,
542 X_Attribute_Category_Code => X_Attribute_Category_Code,
543 X_Meaning => X_Meaning,
544 X_Description => X_Description,
545 X_Creation_Date => sysdate,
546 X_Created_By => user_id,
547 X_Last_Update_Date => sysdate,
548 X_Last_Updated_By => user_id,
549 X_Last_Update_Login => 0
550 , p_log_level_rec => p_log_level_rec);
551 end if;
552
553 exception
554 when others then
555 FA_STANDARD_PKG.RAISE_ERROR(
556 CALLED_FN => 'fa_lookups_pkg.load_row',
557 CALLING_FN => 'upload fa_lookups',
558 p_log_level_rec => p_log_level_rec);
559
560 end LOAD_ROW;
561 /*Bug 8355119 overloading function for release specific signatures*/
562 procedure LOAD_ROW (
563 X_CUSTOM_MODE in VARCHAR2,
564 X_LOOKUP_TYPE in VARCHAR2,
565 X_LOOKUP_CODE in VARCHAR2,
566 X_OWNER in VARCHAR2,
567 X_LAST_UPDATE_DATE in DATE,
568 X_MEANING in VARCHAR2,
569 X_ENABLED_FLAG in VARCHAR2,
570 X_DESCRIPTION in VARCHAR2,
571 X_START_DATE_ACTIVE in DATE,
572 X_END_DATE_ACTIVE in DATE,
573 X_ATTRIBUTE1 in VARCHAR2,
574 X_ATTRIBUTE2 in VARCHAR2,
575 X_ATTRIBUTE3 in VARCHAR2,
576 X_ATTRIBUTE4 in VARCHAR2,
577 X_ATTRIBUTE5 in VARCHAR2,
578 X_ATTRIBUTE6 in VARCHAR2,
579 X_ATTRIBUTE7 in VARCHAR2,
580 X_ATTRIBUTE8 in VARCHAR2,
581 X_ATTRIBUTE9 in VARCHAR2,
582 X_ATTRIBUTE10 in VARCHAR2,
583 X_ATTRIBUTE11 in VARCHAR2,
584 X_ATTRIBUTE12 in VARCHAR2,
585 X_ATTRIBUTE13 in VARCHAR2,
586 X_ATTRIBUTE14 in VARCHAR2,
587 X_ATTRIBUTE15 in VARCHAR2,
588 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
589 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
590
591 h_record_exists number(15);
592
593 user_id number;
594 row_id varchar2(64);
595
596 db_last_updated_by number;
597 db_last_update_date date;
598
599 begin
600
601 user_id := fnd_load_util.owner_id (X_Owner);
602
603 select count(*)
604 into h_record_exists
605 from fa_lookups
606 where lookup_type = X_Lookup_Type
607 and lookup_code = X_Lookup_Code;
608
609 if (h_record_exists > 0) then
610
611 select last_updated_by, last_update_date
612 into db_last_updated_by, db_last_update_date
613 from fa_lookups
614 where lookup_type = x_lookup_type
615 and lookup_code = x_lookup_code;
616
617 if (fnd_load_util.upload_test(user_id, x_last_update_date,
618 db_last_updated_by, db_last_update_date,
619 X_CUSTOM_MODE)) then
620
621 fa_lookups_pkg.update_row (
622 X_Lookup_Type => X_Lookup_Type,
623 X_Lookup_Code => X_Lookup_Code,
624 X_Enabled_Flag => X_Enabled_Flag,
625 X_Start_Date_Active => X_Start_Date_Active,
626 X_End_Date_Active => X_End_Date_Active,
627 X_Attribute1 => X_Attribute1,
628 X_Attribute2 => X_Attribute2,
629 X_Attribute3 => X_Attribute3,
630 X_Attribute4 => X_Attribute4,
631 X_Attribute5 => X_Attribute5,
632 X_Attribute6 => X_Attribute6,
633 X_Attribute7 => X_Attribute7,
634 X_Attribute8 => X_Attribute8,
635 X_Attribute9 => X_Attribute9,
636 X_Attribute10 => X_Attribute10,
637 X_Attribute11 => X_Attribute11,
638 X_Attribute12 => X_Attribute12,
639 X_Attribute13 => X_Attribute13,
640 X_Attribute14 => X_Attribute14,
641 X_Attribute15 => X_Attribute15,
642 X_Attribute_Category_Code => X_Attribute_Category_Code,
643 X_Meaning => X_Meaning,
644 X_Description => X_Description,
645 X_Last_Update_Date => X_Last_Update_Date,
646 X_Last_Updated_By => user_id,
647 X_Last_Update_Login => 0
648 ,p_log_level_rec => p_log_level_rec);
649 end if;
650 else
651 fa_lookups_pkg.insert_row (
652 X_Rowid => row_id,
653 X_Lookup_Type => X_Lookup_Type,
654 X_Lookup_Code => X_Lookup_Code,
655 X_Enabled_Flag => X_Enabled_Flag,
656 X_Start_Date_Active => X_Start_Date_Active,
657 X_End_Date_Active => X_End_Date_Active,
658 X_Attribute1 => X_Attribute1,
659 X_Attribute2 => X_Attribute2,
660 X_Attribute3 => X_Attribute3,
661 X_Attribute4 => X_Attribute4,
662 X_Attribute5 => X_Attribute5,
663 X_Attribute6 => X_Attribute6,
664 X_Attribute7 => X_Attribute7,
665 X_Attribute8 => X_Attribute8,
666 X_Attribute9 => X_Attribute9,
667 X_Attribute10 => X_Attribute10,
668 X_Attribute11 => X_Attribute11,
669 X_Attribute12 => X_Attribute12,
670 X_Attribute13 => X_Attribute13,
671 X_Attribute14 => X_Attribute14,
672 X_Attribute15 => X_Attribute15,
673 X_Attribute_Category_Code => X_Attribute_Category_Code,
674 X_Meaning => X_Meaning,
675 X_Description => X_Description,
676 X_Creation_Date => sysdate,
677 X_Created_By => user_id,
678 X_Last_Update_Date => X_Last_Update_Date,
679 X_Last_Updated_By => user_id,
680 X_Last_Update_Login => 0
681 ,p_log_level_rec => p_log_level_rec);
682 end if;
683
684 exception
685 when others then
686 FA_STANDARD_PKG.RAISE_ERROR(
687 CALLED_FN => 'fa_lookups_pkg.load_row',
688 CALLING_FN => 'upload fa_lookups'
689 ,p_log_level_rec => p_log_level_rec);
690
691 end LOAD_ROW;
692
693 procedure TRANSLATE_ROW (
694 X_LOOKUP_TYPE in VARCHAR2,
695 X_LOOKUP_CODE in VARCHAR2,
696 X_OWNER in VARCHAR2,
697 X_MEANING in VARCHAR2,
698 X_DESCRIPTION in VARCHAR2
699 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
700
701 user_id number;
702
703 begin
704
705 if (X_Owner = 'SEED') then
706 user_id := 1;
707 else
708 user_id := 0;
709 end if;
710
711 update FA_LOOKUPS_TL set
712 MEANING = nvl(X_Meaning, MEANING),
713 DESCRIPTION = nvl(X_Description, DESCRIPTION),
714 LAST_UPDATE_DATE = sysdate,
715 LAST_UPDATED_BY = user_id,
716 LAST_UPDATE_LOGIN = 0,
717 SOURCE_LANG = userenv('LANG')
718 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
719 and LOOKUP_TYPE = X_Lookup_Type
720 and LOOKUP_CODE = X_Lookup_Code;
721
722 end TRANSLATE_ROW;
723 /*Bug 8355119 overloading function for release specific signatures*/
724 procedure TRANSLATE_ROW (
725 X_CUSTOM_MODE in VARCHAR2,
726 X_LOOKUP_TYPE in VARCHAR2,
727 X_LOOKUP_CODE in VARCHAR2,
728 X_OWNER in VARCHAR2,
729 X_LAST_UPDATE_DATE in DATE,
730 X_MEANING in VARCHAR2,
731 X_DESCRIPTION in VARCHAR2,
732 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
733
734 user_id number;
735
736 db_last_updated_by number;
737 db_last_update_date date;
738
739 begin
740
741 select last_updated_by, last_update_date
742 into db_last_updated_by, db_last_update_date
743 from fa_lookups_tl
744 where lookup_type = x_lookup_type
745 and lookup_code = x_lookup_code
746 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
747
748 user_id := fnd_load_util.owner_id (X_Owner);
749
750 if (fnd_load_util.upload_test(user_id, sysdate,
751 db_last_updated_by, db_last_update_date,
752 X_CUSTOM_MODE)) then
753
754 update FA_LOOKUPS_TL set
755 MEANING = nvl(X_Meaning, MEANING),
756 DESCRIPTION = nvl(X_Description, DESCRIPTION),
757 LAST_UPDATE_DATE = X_Last_Update_Date,
758 LAST_UPDATED_BY = user_id,
759 LAST_UPDATE_LOGIN = 0,
760 SOURCE_LANG = userenv('LANG')
761 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
762 and LOOKUP_TYPE = X_Lookup_Type
763 and LOOKUP_CODE = X_Lookup_Code;
764
765 end if;
766
767 end TRANSLATE_ROW;
768 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
769 procedure LOAD_SEED_ROW (
770 x_upload_mode IN VARCHAR2,
771 x_custom_mode IN VARCHAR2,
772 x_lookup_type IN VARCHAR2,
773 x_lookup_code IN VARCHAR2,
774 x_owner IN VARCHAR2,
775 x_last_update_date IN DATE,
776 x_meaning IN VARCHAR2,
777 x_enabled_flag IN VARCHAR2,
778 x_description IN VARCHAR2,
779 x_start_date_active IN DATE,
780 x_end_date_active IN DATE,
781 x_attribute1 IN VARCHAR2,
782 x_attribute2 IN VARCHAR2,
783 x_attribute3 IN VARCHAR2,
784 x_attribute4 IN VARCHAR2,
785 x_attribute5 IN VARCHAR2,
786 x_attribute6 IN VARCHAR2,
787 x_attribute7 IN VARCHAR2,
788 x_attribute8 IN VARCHAR2,
789 x_attribute9 IN VARCHAR2,
790 x_attribute10 IN VARCHAR2,
791 x_attribute11 IN VARCHAR2,
792 x_attribute12 IN VARCHAR2,
793 x_attribute13 IN VARCHAR2,
794 x_attribute14 IN VARCHAR2,
795 x_attribute15 IN VARCHAR2,
796 x_attribute_category_code IN VARCHAR2) IS
797
798
799 BEGIN
800
801 if (x_upload_mode = 'NLS') then
802 fa_lookups_pkg.TRANSLATE_ROW (
803 x_custom_mode => x_custom_mode,
804 x_lookup_type => x_lookup_type,
805 x_lookup_code => x_lookup_code,
806 x_owner => x_owner,
807 x_last_update_date => x_last_update_date,
808 x_meaning => x_meaning,
809 x_description => x_description);
810 else
811 fa_lookups_pkg.LOAD_ROW (
812 x_custom_mode => x_custom_mode,
813 x_lookup_type => x_lookup_type,
814 x_lookup_code => x_lookup_code,
815 x_owner => x_owner,
816 x_last_update_date => x_last_update_date,
817 x_meaning => x_meaning,
818 x_enabled_flag => x_enabled_flag,
819 x_description => x_description,
820 x_start_date_active => x_start_date_active,
821 x_end_date_active => x_end_date_active,
822 x_attribute1 => x_attribute1,
823 x_attribute2 => x_attribute2,
824 x_attribute3 => x_attribute3,
825 x_attribute4 => x_attribute4,
826 x_attribute5 => x_attribute5,
827 x_attribute6 => x_attribute6,
828 x_attribute7 => x_attribute7,
829 x_attribute8 => x_attribute8,
830 x_attribute9 => x_attribute9,
831 x_attribute10 => x_attribute10,
832 x_attribute11 => x_attribute11,
833 x_attribute12 => x_attribute12,
834 x_attribute13 => x_attribute13,
835 x_attribute14 => x_attribute14,
836 x_attribute15 => x_attribute15,
837 x_attribute_category_code => x_attribute_category_code);
838
839 end if;
840
841 END LOAD_SEED_ROW;
842
843 end FA_LOOKUPS_PKG;