[Home] [Help]
PACKAGE BODY: APPS.FA_LOOKUP_TYPES_PKG
Source
1 package body FA_LOOKUP_TYPES_PKG as
2 /* $Header: faxiltb.pls 120.7 2006/06/29 20:50:35 glchen 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 default null) is
15 cursor C is select ROWID from FA_LOOKUP_TYPES_B
16 where LOOKUP_TYPE = X_LOOKUP_TYPE
17 ;
18 begin
19 insert into FA_LOOKUP_TYPES_B (
20 LOOKUP_TYPE,
21 USER_MAINTAINABLE,
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN
27 ) values (
28 X_LOOKUP_TYPE,
29 X_USER_MAINTAINABLE,
30 X_CREATION_DATE,
31 X_CREATED_BY,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATED_BY,
34 X_LAST_UPDATE_LOGIN
35 );
36
37 insert into FA_LOOKUP_TYPES_TL (
38 LOOKUP_TYPE,
39 MEANING,
40 DESCRIPTION,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATE_LOGIN,
46 LANGUAGE,
47 SOURCE_LANG
48 ) select
49 X_LOOKUP_TYPE,
50 X_MEANING,
51 X_DESCRIPTION,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_CREATED_BY,
55 X_CREATION_DATE,
56 X_LAST_UPDATE_LOGIN,
57 L.LANGUAGE_CODE,
58 userenv('LANG')
59 from FND_LANGUAGES L
60 where L.INSTALLED_FLAG in ('I', 'B')
61 and not exists
62 (select NULL
63 from FA_LOOKUP_TYPES_TL T
64 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
65 and T.LANGUAGE = L.LANGUAGE_CODE);
66
67 open c;
68 fetch c into X_ROWID;
69 if (c%notfound) then
70 close c;
71 raise no_data_found;
72 end if;
73 close c;
74
75 end INSERT_ROW;
76
77 procedure LOCK_ROW (
78 X_LOOKUP_TYPE in VARCHAR2,
79 X_USER_MAINTAINABLE in VARCHAR2,
80 X_MEANING in VARCHAR2,
81 X_DESCRIPTION in VARCHAR2,
82 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
83 cursor c is select
84 USER_MAINTAINABLE
85 from FA_LOOKUP_TYPES_B
86 where LOOKUP_TYPE = X_LOOKUP_TYPE
87 for update of LOOKUP_TYPE nowait;
88 recinfo c%rowtype;
89
90 cursor c1 is select
91 MEANING,
92 DESCRIPTION,
93 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
94 from FA_LOOKUP_TYPES_TL
95 where LOOKUP_TYPE = X_LOOKUP_TYPE
96 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97 for update of LOOKUP_TYPE nowait;
98 begin
99 open c;
100 fetch c into recinfo;
101 if (c%notfound) then
102 close c;
103 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
104 app_exception.raise_exception;
105 end if;
106 close c;
107 if ( (recinfo.USER_MAINTAINABLE = X_USER_MAINTAINABLE)) then
108 null;
109 else
110 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
111 app_exception.raise_exception;
112 end if;
113
114 for tlinfo in c1 loop
115 if (tlinfo.BASELANG = 'Y') then
116 if ( (tlinfo.MEANING = X_MEANING)
117 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)) then
118 null;
119 else
120 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
121 app_exception.raise_exception;
122 end if;
123 end if;
124 end loop;
125 return;
126 end LOCK_ROW;
127
128 procedure UPDATE_ROW (
129 X_LOOKUP_TYPE in VARCHAR2,
130 X_USER_MAINTAINABLE in VARCHAR2,
131 X_MEANING in VARCHAR2,
132 X_DESCRIPTION in VARCHAR2,
133 X_LAST_UPDATE_DATE in DATE,
134 X_LAST_UPDATED_BY in NUMBER,
135 X_LAST_UPDATE_LOGIN in NUMBER,
136 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
137 begin
138 update FA_LOOKUP_TYPES_B set
139 USER_MAINTAINABLE = X_USER_MAINTAINABLE,
140 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
141 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
142 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
143 where LOOKUP_TYPE = X_LOOKUP_TYPE;
144
145 if (sql%notfound) then
146 raise no_data_found;
147 end if;
148
149 update FA_LOOKUP_TYPES_TL set
150 MEANING = X_MEANING,
151 DESCRIPTION = X_DESCRIPTION,
152 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
153 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
154 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
155 SOURCE_LANG = userenv('LANG')
156 where LOOKUP_TYPE = X_LOOKUP_TYPE
157 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162 end UPDATE_ROW;
163
164 procedure DELETE_ROW (
165 X_LOOKUP_TYPE in VARCHAR2
166 ,p_log_level_rec in fa_api_types.log_level_rec_type default null) is
167 begin
168 delete from FA_LOOKUP_TYPES_TL
169 where LOOKUP_TYPE = X_LOOKUP_TYPE;
170
171 if (sql%notfound) then
172 raise no_data_found;
173 end if;
174
175 delete from FA_LOOKUP_TYPES_B
176 where LOOKUP_TYPE = X_LOOKUP_TYPE;
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181 end DELETE_ROW;
182
183 procedure ADD_LANGUAGE(p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
184 is
185 begin
186 delete from FA_LOOKUP_TYPES_TL T
187 where not exists
188 (select NULL
189 from FA_LOOKUP_TYPES_B B
190 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
191 );
192
193 update FA_LOOKUP_TYPES_TL T set (
194 MEANING,
195 DESCRIPTION
196 ) = (select
197 B.MEANING,
198 B.DESCRIPTION
199 from FA_LOOKUP_TYPES_TL B
200 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
201 and B.LANGUAGE = T.SOURCE_LANG)
202 where (
203 T.LOOKUP_TYPE,
204 T.LANGUAGE
205 ) in (select
206 SUBT.LOOKUP_TYPE,
207 SUBT.LANGUAGE
208 from FA_LOOKUP_TYPES_TL SUBB, FA_LOOKUP_TYPES_TL SUBT
209 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
210 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
211 and (SUBB.MEANING <> SUBT.MEANING
212 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
213 ));
214
215 insert into FA_LOOKUP_TYPES_TL (
216 LOOKUP_TYPE,
217 MEANING,
218 DESCRIPTION,
219 LAST_UPDATE_DATE,
220 LAST_UPDATED_BY,
221 CREATED_BY,
222 CREATION_DATE,
223 LAST_UPDATE_LOGIN,
224 LANGUAGE,
225 SOURCE_LANG
226 ) select
227 B.LOOKUP_TYPE,
228 B.MEANING,
229 B.DESCRIPTION,
230 B.LAST_UPDATE_DATE,
231 B.LAST_UPDATED_BY,
232 B.CREATED_BY,
233 B.CREATION_DATE,
234 B.LAST_UPDATE_LOGIN,
235 L.LANGUAGE_CODE,
236 B.SOURCE_LANG
237 from FA_LOOKUP_TYPES_TL B, FND_LANGUAGES L
238 where L.INSTALLED_FLAG in ('I', 'B')
239 and B.LANGUAGE = userenv('LANG')
240 and not exists
241 (select NULL
242 from FA_LOOKUP_TYPES_TL T
243 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
244 and T.LANGUAGE = L.LANGUAGE_CODE);
245 end ADD_LANGUAGE;
246
247 procedure LOAD_ROW (
248 X_CUSTOM_MODE in VARCHAR2,
249 X_LOOKUP_TYPE in VARCHAR2,
250 X_OWNER in VARCHAR2,
251 X_LAST_UPDATE_DATE in DATE,
252 X_MEANING in VARCHAR2,
253 X_DESCRIPTION in VARCHAR2,
254 X_USER_MAINTAINABLE in VARCHAR2,
255 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
256
257 h_record_exists number(15);
258
259 user_id number;
260 row_id varchar2(64);
261
262 db_last_updated_by number;
263 db_last_update_date date;
264
265 begin
266
267 user_id := fnd_load_util.owner_id (X_Owner);
268
269 select count(*)
270 into h_record_exists
271 from fa_lookup_types
272 where lookup_type = X_Lookup_Type;
273
274 if (h_record_exists > 0) then
275
276 select last_updated_by, last_update_date
277 into db_last_updated_by, db_last_update_date
278 from fa_lookup_types
279 where lookup_type = x_lookup_type;
280
281 if (fnd_load_util.upload_test(user_id, x_last_update_date,
282 db_last_updated_by, db_last_update_date,
283 X_CUSTOM_MODE)) then
284
285 fa_lookup_types_pkg.update_row (
286 X_Lookup_Type => X_Lookup_Type,
287 X_User_Maintainable => X_User_Maintainable,
288 X_Meaning => X_Meaning,
289 X_Description => X_Description,
290 X_Last_Update_Date => x_Last_Update_Date,
291 X_Last_Updated_By => user_id,
292 X_Last_Update_Login => 0
293 ,p_log_level_rec => p_log_level_rec);
294 end if;
295 else
296 fa_lookup_types_pkg.insert_row (
297 X_Rowid => row_id,
298 X_Lookup_Type => X_Lookup_Type,
299 X_User_Maintainable => X_User_Maintainable,
300 X_Meaning => X_Meaning,
301 X_Description => X_Description,
302 X_Creation_Date => sysdate,
303 X_Created_By => user_id,
304 X_Last_Update_Date => X_Last_Update_Date,
305 X_Last_Updated_By => user_id,
306 X_Last_Update_Login => 0
307 ,p_log_level_rec => p_log_level_rec);
308 end if;
309
310 exception
311 when others then
312 FA_STANDARD_PKG.RAISE_ERROR(
313 CALLED_FN => 'fa_lookup_types_pkg.load_row',
314 CALLING_FN => 'upload fa_lookup_types'
315 ,p_log_level_rec => p_log_level_rec);
316
317 end LOAD_ROW;
318
319 procedure TRANSLATE_ROW (
320 X_CUSTOM_MODE in VARCHAR2,
321 X_LOOKUP_TYPE in VARCHAR2,
322 X_OWNER in VARCHAR2,
323 X_LAST_UPDATE_DATE in DATE,
324 X_MEANING in VARCHAR2,
325 X_DESCRIPTION in VARCHAR2,
326 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
327
328 user_id number;
329
330 db_last_updated_by number;
331 db_last_update_date date;
332
333 begin
334
335 select last_updated_by, last_update_date
336 into db_last_updated_by, db_last_update_date
337 from fa_lookup_types_tl
338 where lookup_type = x_lookup_type
339 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
340
341 user_id := fnd_load_util.owner_id (X_Owner);
342
343 if (fnd_load_util.upload_test(user_id, sysdate,
344 db_last_updated_by, db_last_update_date,
345 X_CUSTOM_MODE)) then
346
347 update FA_LOOKUP_TYPES_TL set
348 MEANING = nvl(X_Meaning, MEANING),
349 DESCRIPTION = nvl(X_Description, DESCRIPTION),
350 LAST_UPDATE_DATE = X_Last_Update_Date,
351 LAST_UPDATED_BY = user_id,
352 LAST_UPDATE_LOGIN = 0,
353 SOURCE_LANG = userenv('LANG')
354 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
355 and LOOKUP_TYPE = X_LOOKUP_TYPE;
356
357 end if;
358
359 exception
360 when others then
361 FA_STANDARD_PKG.RAISE_ERROR(
362 CALLED_FN => 'fa_lookup_types_pkg.translate_row',
363 CALLING_FN => 'upload fa_lookup_types'
364 ,p_log_level_rec => p_log_level_rec);
365
366 end TRANSLATE_ROW;
367
368 procedure LOAD_SEED_ROW (
369 x_upload_mode IN VARCHAR2,
370 x_custom_mode IN VARCHAR2,
371 x_lookup_type IN VARCHAR2,
372 x_owner IN VARCHAR2,
373 x_last_update_date IN DATE,
374 x_meaning IN VARCHAR2,
375 x_description IN VARCHAR2,
376 x_user_maintainable IN VARCHAR2) IS
377
378
379 BEGIN
380
381 if (x_upload_mode = 'NLS') then
382 fa_lookup_types_pkg.TRANSLATE_ROW (
383 x_custom_mode => x_custom_mode,
384 x_lookup_type => x_lookup_type,
385 x_owner => x_owner,
386 x_last_update_date => x_last_update_date,
387 x_meaning => x_meaning,
388 x_description => x_description);
389 else
390 fa_lookup_types_pkg.LOAD_ROW (
391 x_custom_mode => x_custom_mode,
392 x_lookup_type => x_lookup_type,
393 x_owner => x_owner,
394 x_last_update_date => x_last_update_date,
395 x_meaning => x_meaning,
396 x_description => x_description,
397 x_user_maintainable => x_user_maintainable);
398 end if;
399
400 END LOAD_SEED_ROW;
401
402 end FA_LOOKUP_TYPES_PKG;