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