[Home] [Help]
PACKAGE BODY: APPS.AMS_ATTB_LOV_PKG
Source
1 package body AMS_ATTB_LOV_PKG as
2 /* $Header: amstatbb.pls 120.1 2005/06/27 05:39:27 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_ATTB_LOV_ID in NUMBER,
6 X_DATA_TYPE in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_REQUEST_ID in NUMBER,
9 X_VIEW_APPLICATION_ID in NUMBER,
10 X_CREATION_TYPE in VARCHAR2,
11 X_STATUS_CODE in VARCHAR2,
12 X_LOV_SEEDED_FOR in VARCHAR2,
13 X_SEEDED_FLAG in VARCHAR2,
14 X_SECURITY_GROUP_ID in NUMBER,
15 X_ATTB_LOV_NAME in VARCHAR2,
16 X_DESCRIPTION in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER,
22 X_PROGRAM_ID in NUMBER,
23 X_PROGRAM_APPLICATION_ID in NUMBER,
24 X_PROGRAM_UPDATE_DATE in DATE
25 ) is
26 cursor C is select ROWID from AMS_ATTB_LOV_B
27 where ATTB_LOV_ID = X_ATTB_LOV_ID
28 ;
29 begin
30 insert into AMS_ATTB_LOV_B (
31 DATA_TYPE,
32 ATTB_LOV_ID,
33 OBJECT_VERSION_NUMBER,
34 REQUEST_ID,
35 VIEW_APPLICATION_ID,
36 CREATION_TYPE,
37 STATUS_CODE,
38 LOV_SEEDED_FOR,
39 SEEDED_FLAG,
40 SECURITY_GROUP_ID,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN,
46 PROGRAM_ID,
47 PROGRAM_APPLICATION_ID,
48 PROGRAM_UPDATE_DATE
49 ) values (
50 X_DATA_TYPE,
51 X_ATTB_LOV_ID,
52 X_OBJECT_VERSION_NUMBER,
53 X_REQUEST_ID,
54 X_VIEW_APPLICATION_ID,
55 X_CREATION_TYPE,
56 X_STATUS_CODE,
57 X_LOV_SEEDED_FOR,
58 X_SEEDED_FLAG,
59 X_SECURITY_GROUP_ID,
60 X_CREATION_DATE,
61 X_CREATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_LOGIN,
65 X_PROGRAM_ID,
66 X_PROGRAM_APPLICATION_ID,
67 X_PROGRAM_UPDATE_DATE
68 );
69
70 insert into AMS_ATTB_LOV_TL (
71 CREATION_DATE,
72 CREATED_BY,
73 LAST_UPDATE_LOGIN,
74 ATTB_LOV_NAME,
75 DESCRIPTION,
76 ATTB_LOV_ID,
77 LAST_UPDATE_DATE,
78 LAST_UPDATED_BY,
79 LANGUAGE,
80 SOURCE_LANG
81 ) select
82 X_CREATION_DATE,
83 X_CREATED_BY,
84 X_LAST_UPDATE_LOGIN,
85 X_ATTB_LOV_NAME,
86 X_DESCRIPTION,
87 X_ATTB_LOV_ID,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATED_BY,
90 L.LANGUAGE_CODE,
91 userenv('LANG')
92 from FND_LANGUAGES L
93 where L.INSTALLED_FLAG in ('I', 'B')
94 and not exists
95 (select NULL
96 from AMS_ATTB_LOV_TL T
97 where T.ATTB_LOV_ID = X_ATTB_LOV_ID
98 and T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 end INSERT_ROW;
109
110 procedure LOCK_ROW (
111 X_ATTB_LOV_ID in NUMBER,
112 X_DATA_TYPE in VARCHAR2,
113 X_OBJECT_VERSION_NUMBER in NUMBER,
114 X_REQUEST_ID in NUMBER,
115 X_VIEW_APPLICATION_ID in NUMBER,
116 X_CREATION_TYPE in VARCHAR2,
117 X_STATUS_CODE in VARCHAR2,
118 X_LOV_SEEDED_FOR in VARCHAR2,
119 X_SEEDED_FLAG in VARCHAR2,
120 X_SECURITY_GROUP_ID in NUMBER,
121 X_ATTB_LOV_NAME in VARCHAR2,
122 X_DESCRIPTION in VARCHAR2,
123 X_PROGRAM_ID in NUMBER,
124 X_PROGRAM_APPLICATION_ID in NUMBER,
125 X_PROGRAM_UPDATE_DATE in DATE
126 ) is
127 cursor c is select
128 DATA_TYPE,
129 OBJECT_VERSION_NUMBER,
130 REQUEST_ID,
131 VIEW_APPLICATION_ID,
132 CREATION_TYPE,
133 STATUS_CODE,
134 LOV_SEEDED_FOR,
135 SEEDED_FLAG,
136 SECURITY_GROUP_ID
137 from AMS_ATTB_LOV_B
138 where ATTB_LOV_ID = X_ATTB_LOV_ID
139 for update of ATTB_LOV_ID nowait;
140 recinfo c%rowtype;
141
142 cursor c1 is select
143 ATTB_LOV_NAME,
144 DESCRIPTION,
145 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
146 from AMS_ATTB_LOV_TL
147 where ATTB_LOV_ID = X_ATTB_LOV_ID
148 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
149 for update of ATTB_LOV_ID nowait;
150 begin
151 open c;
152 fetch c into recinfo;
153 if (c%notfound) then
154 close c;
155 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
156 app_exception.raise_exception;
157 end if;
158 close c;
159 if ( ((recinfo.DATA_TYPE = X_DATA_TYPE)
160 OR ((recinfo.DATA_TYPE is null) AND (X_DATA_TYPE is null)))
161 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
162 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
163 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
164 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
165 AND ((recinfo.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID)
166 OR ((recinfo.VIEW_APPLICATION_ID is null) AND (X_VIEW_APPLICATION_ID is null)))
167 AND ((recinfo.CREATION_TYPE = X_CREATION_TYPE)
168 OR ((recinfo.CREATION_TYPE is null) AND (X_CREATION_TYPE is null)))
169 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
170 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
171 AND ((recinfo.LOV_SEEDED_FOR = X_LOV_SEEDED_FOR)
172 OR ((recinfo.LOV_SEEDED_FOR is null) AND (X_LOV_SEEDED_FOR is null)))
173 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
174 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
175 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
176 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
177 ) then
178 null;
179 else
180 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
181 app_exception.raise_exception;
182 end if;
183
184 for tlinfo in c1 loop
185 if (tlinfo.BASELANG = 'Y') then
186 if ( (tlinfo.ATTB_LOV_NAME = X_ATTB_LOV_NAME)
187 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
188 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
189 ) then
190 null;
191 else
192 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
193 app_exception.raise_exception;
194 end if;
195 end if;
196 end loop;
197 return;
198 end LOCK_ROW;
199
200 procedure UPDATE_ROW (
201 X_ATTB_LOV_ID in NUMBER,
202 X_DATA_TYPE in VARCHAR2,
203 X_OBJECT_VERSION_NUMBER in NUMBER,
204 X_REQUEST_ID in NUMBER,
205 X_VIEW_APPLICATION_ID in NUMBER,
206 X_CREATION_TYPE in VARCHAR2,
207 X_STATUS_CODE in VARCHAR2,
208 X_LOV_SEEDED_FOR in VARCHAR2,
209 X_SEEDED_FLAG in VARCHAR2,
210 X_SECURITY_GROUP_ID in NUMBER,
211 X_ATTB_LOV_NAME in VARCHAR2,
212 X_DESCRIPTION in VARCHAR2,
213 X_LAST_UPDATE_DATE in DATE,
214 X_LAST_UPDATED_BY in NUMBER,
215 X_LAST_UPDATE_LOGIN in NUMBER,
216 X_PROGRAM_ID in NUMBER,
217 X_PROGRAM_APPLICATION_ID in NUMBER,
218 X_PROGRAM_UPDATE_DATE in DATE
219 ) is
220 begin
221 update AMS_ATTB_LOV_B set
222 DATA_TYPE = X_DATA_TYPE,
223 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
224 REQUEST_ID = X_REQUEST_ID,
225 VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID,
226 CREATION_TYPE = X_CREATION_TYPE,
227 STATUS_CODE = X_STATUS_CODE,
228 LOV_SEEDED_FOR = X_LOV_SEEDED_FOR,
229 SEEDED_FLAG = X_SEEDED_FLAG,
230 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
231 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
232 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
233 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
234 where ATTB_LOV_ID = X_ATTB_LOV_ID;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239
240 update AMS_ATTB_LOV_TL set
241 ATTB_LOV_NAME = X_ATTB_LOV_NAME,
242 DESCRIPTION = X_DESCRIPTION,
243 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
244 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
245 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
246 SOURCE_LANG = userenv('LANG')
247 where ATTB_LOV_ID = X_ATTB_LOV_ID
248 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253 end UPDATE_ROW;
254
255 procedure DELETE_ROW (
256 X_ATTB_LOV_ID in NUMBER
257 ) is
258 begin
259 delete from AMS_ATTB_LOV_TL
260 where ATTB_LOV_ID = X_ATTB_LOV_ID;
261
262 if (sql%notfound) then
263 raise no_data_found;
264 end if;
265
266 delete from AMS_ATTB_LOV_B
267 where ATTB_LOV_ID = X_ATTB_LOV_ID;
268
269 if (sql%notfound) then
270 raise no_data_found;
271 end if;
272 end DELETE_ROW;
273
274 PROCEDURE LOAD_ROW (
275 X_ATTB_LOV_ID in NUMBER,
276 X_DATA_TYPE in VARCHAR2,
277 X_VIEW_APPLICATION_ID in NUMBER,
278 X_CREATION_TYPE in VARCHAR2,
279 X_STATUS_CODE in VARCHAR2,
280 X_LOV_SEEDED_FOR in VARCHAR2,
281 X_SEEDED_FLAG in VARCHAR2,
282 X_ATTB_LOV_NAME in VARCHAR2,
283 X_DESCRIPTION in VARCHAR2,
284 x_owner IN VARCHAR2,
285 x_custom_mode IN VARCHAR2
286
287 )
288 IS
289 l_user_id number := 0;
290 l_obj_verno number;
291 l_dummy_char varchar2(1);
292 l_row_id varchar2(100);
293 l_attb_lov_id number;
294 l_db_luby_id number;
295
296 CURSOR c_obj_verno IS
297 SELECT object_version_number, last_updated_by
298 FROM AMS_ATTB_LOV_B
299 WHERE ATTB_LOV_ID = X_ATTB_LOV_ID;
300
301 CURSOR c_chk_exists is
302 SELECT 'x'
303 FROM AMS_ATTB_LOV_B
304 WHERE ATTB_LOV_ID = X_ATTB_LOV_ID;
305
306 CURSOR c_get_id is
307 SELECT AMS_ATTB_LOV_B_S.NEXTVAL
308 FROM DUAL;
309 BEGIN
310 if X_OWNER = 'SEED' then
311 l_user_id := 1;
312 elsif X_OWNER = 'ORACLE' then
313 l_user_id := 2;
314 elsif X_OWNER = 'SYSADMIN' then
315 l_user_id := 0;
316
317 end if;
318
319 OPEN c_chk_exists;
320 FETCH c_chk_exists INTO l_dummy_char;
321 IF c_chk_exists%notfound THEN
322 CLOSE c_chk_exists;
323
324 IF X_ATTB_LOV_ID IS NULL THEN
325 OPEN c_get_id;
326 FETCH c_get_id INTO l_ATTB_LOV_ID;
327 CLOSE c_get_id;
328 ELSE
329 l_ATTB_LOV_ID := X_ATTB_LOV_ID;
330 END IF;
331
332 l_obj_verno := 1;
333
334 AMS_ATTB_LOV_PKG.Insert_Row (
335 X_ROWID => l_row_id,
336 X_ATTB_LOV_ID => l_ATTB_LOV_ID,
337 X_DATA_TYPE => X_DATA_TYPE,
338 X_OBJECT_VERSION_NUMBER => l_obj_verno,
339 X_REQUEST_ID => 0,
340 X_VIEW_APPLICATION_ID => X_VIEW_APPLICATION_ID,
341 X_CREATION_TYPE => X_CREATION_TYPE,
342 X_STATUS_CODE => X_STATUS_CODE,
343 X_LOV_SEEDED_FOR => X_LOV_SEEDED_FOR,
344 X_SEEDED_FLAG => X_SEEDED_FLAG,
345 X_SECURITY_GROUP_ID => 0,
346 X_ATTB_LOV_NAME => X_ATTB_LOV_NAME,
347 X_DESCRIPTION => X_DESCRIPTION,
348 X_creation_date => SYSDATE,
349 X_created_by => l_user_id,
350 X_last_update_date => SYSDATE,
351 X_last_updated_by => l_user_id,
352 X_last_update_login => 0,
353 X_PROGRAM_ID => 0,
354 X_PROGRAM_APPLICATION_ID => 0,
355 X_PROGRAM_UPDATE_DATE => SYSDATE
356 );
357 ELSE
358 CLOSE c_chk_exists;
359 OPEN c_obj_verno;
360 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
361 CLOSE c_obj_verno;
362
363
364 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
365 then
366 AMS_ATTB_LOV_PKG.Update_Row (
367 X_ATTB_LOV_ID => x_ATTB_LOV_ID,
368 X_DATA_TYPE => X_DATA_TYPE,
369 X_OBJECT_VERSION_NUMBER => l_obj_verno,
370 X_REQUEST_ID => 0,
371 X_VIEW_APPLICATION_ID => X_VIEW_APPLICATION_ID,
372 X_CREATION_TYPE => X_CREATION_TYPE,
373 X_STATUS_CODE => X_STATUS_CODE,
374 X_LOV_SEEDED_FOR => X_LOV_SEEDED_FOR,
375 X_SEEDED_FLAG => X_SEEDED_FLAG,
376 X_SECURITY_GROUP_ID => 0,
377 X_ATTB_LOV_NAME => X_ATTB_LOV_NAME,
378 X_DESCRIPTION => X_DESCRIPTION,
379 X_last_update_date => SYSDATE,
380 X_last_updated_by => l_user_id,
381 X_last_update_login => 0,
382 X_PROGRAM_ID => 0,
383 X_PROGRAM_APPLICATION_ID => 0,
384 X_PROGRAM_UPDATE_DATE => SYSDATE
385 );
386 end if;
387
388 END IF;
389 END LOAD_ROW;
390
391
392
393 procedure ADD_LANGUAGE
394 is
395 begin
396 delete from AMS_ATTB_LOV_TL T
397 where not exists
398 (select NULL
399 from AMS_ATTB_LOV_B B
400 where B.ATTB_LOV_ID = T.ATTB_LOV_ID
401 );
402
403 update AMS_ATTB_LOV_TL T set (
404 ATTB_LOV_NAME,
405 DESCRIPTION
406 ) = (select
407 B.ATTB_LOV_NAME,
408 B.DESCRIPTION
409 from AMS_ATTB_LOV_TL B
410 where B.ATTB_LOV_ID = T.ATTB_LOV_ID
411 and B.LANGUAGE = T.SOURCE_LANG)
412 where (
413 T.ATTB_LOV_ID,
414 T.LANGUAGE
415 ) in (select
416 SUBT.ATTB_LOV_ID,
417 SUBT.LANGUAGE
418 from AMS_ATTB_LOV_TL SUBB, AMS_ATTB_LOV_TL SUBT
419 where SUBB.ATTB_LOV_ID = SUBT.ATTB_LOV_ID
420 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
421 and (SUBB.ATTB_LOV_NAME <> SUBT.ATTB_LOV_NAME
422 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
423 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
424 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
425 ));
426
427 insert into AMS_ATTB_LOV_TL (
428 CREATION_DATE,
429 CREATED_BY,
430 LAST_UPDATE_LOGIN,
431 ATTB_LOV_NAME,
432 DESCRIPTION,
433 ATTB_LOV_ID,
434 LAST_UPDATE_DATE,
435 LAST_UPDATED_BY,
436 LANGUAGE,
437 SOURCE_LANG
438 ) select /*+ ORDERED */
439 B.CREATION_DATE,
440 B.CREATED_BY,
441 B.LAST_UPDATE_LOGIN,
442 B.ATTB_LOV_NAME,
443 B.DESCRIPTION,
444 B.ATTB_LOV_ID,
445 B.LAST_UPDATE_DATE,
446 B.LAST_UPDATED_BY,
447 L.LANGUAGE_CODE,
448 B.SOURCE_LANG
449 from AMS_ATTB_LOV_TL B, FND_LANGUAGES L
450 where L.INSTALLED_FLAG in ('I', 'B')
451 and B.LANGUAGE = userenv('LANG')
452 and not exists
453 (select NULL
454 from AMS_ATTB_LOV_TL T
455 where T.ATTB_LOV_ID = B.ATTB_LOV_ID
456 and T.LANGUAGE = L.LANGUAGE_CODE);
457 end ADD_LANGUAGE;
458 procedure TRANSLATE_ROW(
459 X_ATTB_LOV_ID in NUMBER,
460 X_ATTB_LOV_NAME in VARCHAR2,
461 X_DESCRIPTION in VARCHAR2,
462 x_owner in VARCHAR2,
463 x_custom_mode IN VARCHAR2
464
465 ) is
466
467 cursor c_last_updated_by is
468 select last_updated_by
469 from AMS_ATTB_LOV_TL
470 where ATTB_LOV_ID = X_ATTB_LOV_ID
471 and USERENV('LANG') = LANGUAGE;
472
473 l_luby number; --last updated by
474
475
476 begin
477
478 open c_last_updated_by;
479 fetch c_last_updated_by into l_luby;
480 close c_last_updated_by;
481
482 if (l_luby IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
483 then
484
485 update AMS_ATTB_LOV_TL set
486 ATTB_LOV_NAME= nvl(X_ATTB_LOV_NAME, ATTB_LOV_NAME),
487 description = nvl(x_description, description),
488 source_lang = userenv('LANG'),
489 last_update_date = sysdate,
490 last_updated_by = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
491 last_update_login = 0
492 where ATTB_LOV_ID = X_ATTB_LOV_ID
493 and userenv('LANG') in (language, source_lang);
494
495 end if;
496 end TRANSLATE_ROW;
497
498
499 end AMS_ATTB_LOV_PKG;