[Home] [Help]
PACKAGE BODY: APPS.FA_LOOKUP_TYPES_PKG
Source
1 package body FA_LOOKUP_TYPES_PKG as
2 /* $Header: faxiltb.pls 120.11 2011/03/11 02:20:14 saalampa ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_USER_MAINTAINABLE in VARCHAR2,
7 X_MEANING in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
15 cursor C is select ROWID from FA_LOOKUP_TYPES_B
16 where LOOKUP_TYPE = X_LOOKUP_TYPE
17 ;
18 begin
19 ad_zd_seed.prepare('FA_LOOKUP_TYPES_B');
20 ad_zd_seed.prepare('FA_LOOKUP_TYPES_TL');
21 insert into FA_LOOKUP_TYPES_B (
22 LOOKUP_TYPE,
23 USER_MAINTAINABLE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_LOOKUP_TYPE,
31 X_USER_MAINTAINABLE,
32 X_CREATION_DATE,
33 X_CREATED_BY,
34 X_LAST_UPDATE_DATE,
35 X_LAST_UPDATED_BY,
36 X_LAST_UPDATE_LOGIN
37 );
38
39 insert into FA_LOOKUP_TYPES_TL (
40 LOOKUP_TYPE,
41 MEANING,
42 DESCRIPTION,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATE_LOGIN,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_LOOKUP_TYPE,
52 X_MEANING,
53 X_DESCRIPTION,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_CREATED_BY,
57 X_CREATION_DATE,
58 X_LAST_UPDATE_LOGIN,
59 L.LANGUAGE_CODE,
60 userenv('LANG')
61 from FND_LANGUAGES L
62 where L.INSTALLED_FLAG in ('I', 'B')
63 and not exists
64 (select NULL
65 from FA_LOOKUP_TYPES_TL T
66 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
67 and T.LANGUAGE = L.LANGUAGE_CODE);
68
69 open c;
70 fetch c into X_ROWID;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 end INSERT_ROW;
78
79 procedure LOCK_ROW (
80 X_LOOKUP_TYPE in VARCHAR2,
81 X_USER_MAINTAINABLE in VARCHAR2,
82 X_MEANING in VARCHAR2,
83 X_DESCRIPTION in VARCHAR2
84 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
85 cursor c is select
86 USER_MAINTAINABLE
87 from FA_LOOKUP_TYPES_B
88 where LOOKUP_TYPE = X_LOOKUP_TYPE
89 for update of LOOKUP_TYPE nowait;
90 recinfo c%rowtype;
91
92 cursor c1 is select
93 MEANING,
94 DESCRIPTION,
95 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
96 from FA_LOOKUP_TYPES_TL
97 where LOOKUP_TYPE = X_LOOKUP_TYPE
98 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
99 for update of LOOKUP_TYPE nowait;
100 begin
101 open c;
102 fetch c into recinfo;
103 if (c%notfound) then
104 close c;
105 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
106 app_exception.raise_exception;
107 end if;
108 close c;
109 if ( (recinfo.USER_MAINTAINABLE = X_USER_MAINTAINABLE)
110 ) then
111 null;
112 else
113 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114 app_exception.raise_exception;
115 end if;
116
117 for tlinfo in c1 loop
118 if (tlinfo.BASELANG = 'Y') then
119 if ( (tlinfo.MEANING = X_MEANING)
120 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
121 ) then
122 null;
123 else
124 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125 app_exception.raise_exception;
126 end if;
127 end if;
128 end loop;
129 return;
130 end LOCK_ROW;
131
132 procedure UPDATE_ROW (
133 X_LOOKUP_TYPE in VARCHAR2,
134 X_USER_MAINTAINABLE in VARCHAR2,
135 X_MEANING in VARCHAR2,
136 X_DESCRIPTION in VARCHAR2,
137 X_LAST_UPDATE_DATE in DATE,
138 X_LAST_UPDATED_BY in NUMBER,
139 X_LAST_UPDATE_LOGIN in NUMBER
140 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
141 begin
142 ad_zd_seed.prepare('FA_LOOKUP_TYPES_B');
143 ad_zd_seed.prepare('FA_LOOKUP_TYPES_TL');
144 update FA_LOOKUP_TYPES_B set
145 USER_MAINTAINABLE = X_USER_MAINTAINABLE,
146 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
147 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
148 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
149 where LOOKUP_TYPE = X_LOOKUP_TYPE;
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154
155 update FA_LOOKUP_TYPES_TL set
156 MEANING = X_MEANING,
157 DESCRIPTION = X_DESCRIPTION,
158 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
159 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
160 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
161 SOURCE_LANG = userenv('LANG')
162 where LOOKUP_TYPE = X_LOOKUP_TYPE
163 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168 end UPDATE_ROW;
169
170 procedure DELETE_ROW (
171 X_LOOKUP_TYPE in VARCHAR2
172 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
173 begin
174 ad_zd_seed.prepare('FA_LOOKUP_TYPES_B');
175 ad_zd_seed.prepare('FA_LOOKUP_TYPES_TL');
176 delete from FA_LOOKUP_TYPES_TL
177 where LOOKUP_TYPE = X_LOOKUP_TYPE;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 delete from FA_LOOKUP_TYPES_B
184 where LOOKUP_TYPE = X_LOOKUP_TYPE;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end DELETE_ROW;
190 /* bug 8355119
191 procedure ADD_LANGUAGE
192 is
193 begin
194 delete from FA_LOOKUP_TYPES_TL T
195 where not exists
196 (select NULL
197 from FA_LOOKUP_TYPES_B B
198 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
199 );*/
200 /* bug 8355119*/
201 procedure ADD_LANGUAGE(p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
202 is
203 begin
204 delete from FA_LOOKUP_TYPES_TL T
205 where not exists
206 (select NULL
207 from FA_LOOKUP_TYPES_B B
208 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
209 );
210
211 update FA_LOOKUP_TYPES_TL T set (
212 MEANING,
213 DESCRIPTION
214 ) = (select
215 B.MEANING,
216 B.DESCRIPTION
217 from FA_LOOKUP_TYPES_TL B
218 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
219 and B.LANGUAGE = T.SOURCE_LANG)
220 where (
221 T.LOOKUP_TYPE,
222 T.LANGUAGE
223 ) in (select
224 SUBT.LOOKUP_TYPE,
225 SUBT.LANGUAGE
226 from FA_LOOKUP_TYPES_TL SUBB, FA_LOOKUP_TYPES_TL SUBT
227 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
228 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
229 and (SUBB.MEANING <> SUBT.MEANING
230 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
231 ));
232
233 insert into FA_LOOKUP_TYPES_TL (
234 LOOKUP_TYPE,
235 MEANING,
236 DESCRIPTION,
237 LAST_UPDATE_DATE,
238 LAST_UPDATED_BY,
239 CREATED_BY,
240 CREATION_DATE,
241 LAST_UPDATE_LOGIN,
242 LANGUAGE,
243 SOURCE_LANG
244 ) select
245 B.LOOKUP_TYPE,
246 B.MEANING,
247 B.DESCRIPTION,
248 B.LAST_UPDATE_DATE,
249 B.LAST_UPDATED_BY,
250 B.CREATED_BY,
251 B.CREATION_DATE,
252 B.LAST_UPDATE_LOGIN,
253 L.LANGUAGE_CODE,
254 B.SOURCE_LANG
255 from FA_LOOKUP_TYPES_TL B, FND_LANGUAGES L
256 where L.INSTALLED_FLAG in ('I', 'B')
257 and B.LANGUAGE = userenv('LANG')
258 and not exists
259 (select NULL
260 from FA_LOOKUP_TYPES_TL T
261 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
262 and T.LANGUAGE = L.LANGUAGE_CODE);
263 end ADD_LANGUAGE;
264
265 procedure LOAD_ROW (
266 X_LOOKUP_TYPE in VARCHAR2,
267 X_OWNER in VARCHAR2,
268 X_MEANING in VARCHAR2,
269 X_DESCRIPTION in VARCHAR2,
270 X_USER_MAINTAINABLE in VARCHAR2
271 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
272
273 h_record_exists number(15);
274
275 user_id number;
276 row_id varchar2(64);
277
278 begin
279
280 if (X_Owner = 'SEED') then
281 user_id := 1;
282 else
283 user_id := 0;
284 end if;
285
286 select count(*)
287 into h_record_exists
288 from fa_lookup_types
289 where lookup_type = X_Lookup_Type;
290
291 if (h_record_exists > 0) then
292 fa_lookup_types_pkg.update_row (
293 X_Lookup_Type => X_Lookup_Type,
294 X_User_Maintainable => X_User_Maintainable,
295 X_Meaning => X_Meaning,
296 X_Description => X_Description,
297 X_Last_Update_Date => sysdate,
298 X_Last_Updated_By => user_id,
299 X_Last_Update_Login => 0
300 , p_log_level_rec => p_log_level_rec);
301 else
302 fa_lookup_types_pkg.insert_row (
303 X_Rowid => row_id,
304 X_Lookup_Type => X_Lookup_Type,
305 X_User_Maintainable => X_User_Maintainable,
306 X_Meaning => X_Meaning,
307 X_Description => X_Description,
308 X_Creation_Date => sysdate,
309 X_Created_By => user_id,
310 X_Last_Update_Date => sysdate,
311 X_Last_Updated_By => user_id,
312 X_Last_Update_Login => 0
313 , p_log_level_rec => p_log_level_rec);
314 end if;
315
316 exception
317 when others then
318 FA_STANDARD_PKG.RAISE_ERROR(
319 CALLED_FN => 'fa_lookup_types_pkg.load_row',
320 CALLING_FN => 'upload fa_lookup_types',
321 p_log_level_rec => p_log_level_rec);
322
323 end LOAD_ROW;
324 /*Bug 8355119 overloading function for release specific signatures*/
325 procedure LOAD_ROW (
326 X_CUSTOM_MODE in VARCHAR2,
327 X_LOOKUP_TYPE in VARCHAR2,
328 X_OWNER in VARCHAR2,
329 X_LAST_UPDATE_DATE in DATE,
330 X_MEANING in VARCHAR2,
331 X_DESCRIPTION in VARCHAR2,
332 X_USER_MAINTAINABLE in VARCHAR2,
333 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
334
335 h_record_exists number(15);
336
337 user_id number;
338 row_id varchar2(64);
339
340 db_last_updated_by number;
341 db_last_update_date date;
342
343 begin
344
345 user_id := fnd_load_util.owner_id (X_Owner);
346
347 select count(*)
348 into h_record_exists
349 from fa_lookup_types
350 where lookup_type = X_Lookup_Type;
351
352 if (h_record_exists > 0) then
353
354 select last_updated_by, last_update_date
355 into db_last_updated_by, db_last_update_date
356 from fa_lookup_types
357 where lookup_type = x_lookup_type;
358
359 if (fnd_load_util.upload_test(user_id, x_last_update_date,
360 db_last_updated_by, db_last_update_date,
361 X_CUSTOM_MODE)) then
362
363 fa_lookup_types_pkg.update_row (
364 X_Lookup_Type => X_Lookup_Type,
365 X_User_Maintainable => X_User_Maintainable,
366 X_Meaning => X_Meaning,
367 X_Description => X_Description,
368 X_Last_Update_Date => x_Last_Update_Date,
369 X_Last_Updated_By => user_id,
370 X_Last_Update_Login => 0
371 ,p_log_level_rec => p_log_level_rec);
372 end if;
373 else
374 fa_lookup_types_pkg.insert_row (
375 X_Rowid => row_id,
376 X_Lookup_Type => X_Lookup_Type,
377 X_User_Maintainable => X_User_Maintainable,
378 X_Meaning => X_Meaning,
379 X_Description => X_Description,
380 X_Creation_Date => sysdate,
381 X_Created_By => user_id,
382 X_Last_Update_Date => X_Last_Update_Date,
383 X_Last_Updated_By => user_id,
384 X_Last_Update_Login => 0
385 ,p_log_level_rec => p_log_level_rec);
386 end if;
387
388 exception
389 when others then
390 FA_STANDARD_PKG.RAISE_ERROR(
391 CALLED_FN => 'fa_lookup_types_pkg.load_row',
392 CALLING_FN => 'upload fa_lookup_types'
393 ,p_log_level_rec => p_log_level_rec);
394
395 end LOAD_ROW;
396
397 procedure TRANSLATE_ROW (
398 X_LOOKUP_TYPE in VARCHAR2,
399 X_OWNER in VARCHAR2,
400 X_MEANING in VARCHAR2,
401 X_DESCRIPTION in VARCHAR2
402 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
403
404 user_id number;
405
406 begin
407
408 if (X_Owner = 'SEED') then
409 user_id := 1;
410 else
411 user_id := 0;
412 end if;
413
414 update FA_LOOKUP_TYPES_TL set
415 MEANING = nvl(X_Meaning, MEANING),
416 DESCRIPTION = nvl(X_Description, DESCRIPTION),
417 LAST_UPDATE_DATE = sysdate,
418 LAST_UPDATED_BY = user_id,
419 LAST_UPDATE_LOGIN = 0,
420 SOURCE_LANG = userenv('LANG')
421 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
422 and LOOKUP_TYPE = X_LOOKUP_TYPE;
423
424
425 exception
426 when others then
427 FA_STANDARD_PKG.RAISE_ERROR(
428 CALLED_FN => 'fa_lookup_types_pkg.translate_row',
429 CALLING_FN => 'upload fa_lookup_types',
430 p_log_level_rec => p_log_level_rec);
431
432 end TRANSLATE_ROW;
433 /*Bug 8355119 overloading function for release specific signatures*/
434 procedure TRANSLATE_ROW (
435 X_CUSTOM_MODE in VARCHAR2,
436 X_LOOKUP_TYPE in VARCHAR2,
437 X_OWNER in VARCHAR2,
438 X_LAST_UPDATE_DATE in DATE,
439 X_MEANING in VARCHAR2,
440 X_DESCRIPTION in VARCHAR2,
441 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
442
443 user_id number;
444
445 db_last_updated_by number;
446 db_last_update_date date;
447
448 begin
449
450 select last_updated_by, last_update_date
451 into db_last_updated_by, db_last_update_date
452 from fa_lookup_types_tl
453 where lookup_type = x_lookup_type
454 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
455
456 user_id := fnd_load_util.owner_id (X_Owner);
457
458 if (fnd_load_util.upload_test(user_id, sysdate,
459 db_last_updated_by, db_last_update_date,
460 X_CUSTOM_MODE)) then
461
462 update FA_LOOKUP_TYPES_TL set
463 MEANING = nvl(X_Meaning, MEANING),
464 DESCRIPTION = nvl(X_Description, DESCRIPTION),
465 LAST_UPDATE_DATE = X_Last_Update_Date,
466 LAST_UPDATED_BY = user_id,
467 LAST_UPDATE_LOGIN = 0,
468 SOURCE_LANG = userenv('LANG')
469 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
470 and LOOKUP_TYPE = X_LOOKUP_TYPE;
471
472 end if;
473
474 exception
475 when others then
476 FA_STANDARD_PKG.RAISE_ERROR(
477 CALLED_FN => 'fa_lookup_types_pkg.translate_row',
478 CALLING_FN => 'upload fa_lookup_types'
479 ,p_log_level_rec => p_log_level_rec);
480
481 end TRANSLATE_ROW;
482 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
483 procedure LOAD_SEED_ROW (
484 x_upload_mode IN VARCHAR2,
485 x_custom_mode IN VARCHAR2,
486 x_lookup_type IN VARCHAR2,
487 x_owner IN VARCHAR2,
488 x_last_update_date IN DATE,
489 x_meaning IN VARCHAR2,
490 x_description IN VARCHAR2,
491 x_user_maintainable IN VARCHAR2) IS
492
493
494 BEGIN
495
496 if (x_upload_mode = 'NLS') then
497 fa_lookup_types_pkg.TRANSLATE_ROW (
498 x_custom_mode => x_custom_mode,
499 x_lookup_type => x_lookup_type,
500 x_owner => x_owner,
501 x_last_update_date => x_last_update_date,
502 x_meaning => x_meaning,
503 x_description => x_description);
504 else
505 fa_lookup_types_pkg.LOAD_ROW (
506 x_custom_mode => x_custom_mode,
507 x_lookup_type => x_lookup_type,
508 x_owner => x_owner,
509 x_last_update_date => x_last_update_date,
510 x_meaning => x_meaning,
511 x_description => x_description,
512 x_user_maintainable => x_user_maintainable);
513 end if;
514
515 END LOAD_SEED_ROW;
516
517 end FA_LOOKUP_TYPES_PKG;