[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PL_PARAMETERS_B_PKG
Source
1 PACKAGE BODY AMS_IBA_PL_PARAMETERS_B_PKG as
2 /* $Header: amstparb.pls 120.0 2005/05/31 15:23:03 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IBA_PL_PARAMETERS_B_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IBA_PL_PARAMETERS_B_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstparb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 px_parameter_id IN OUT NOCOPY NUMBER,
39 p_site_id NUMBER,
40 p_site_ref_code VARCHAR2,
41 p_parameter_ref_code VARCHAR2,
42 p_execution_order NUMBER,
43 p_created_by NUMBER,
44 p_creation_date DATE,
45 p_last_updated_by NUMBER,
46 p_last_update_date DATE,
47 p_last_update_login NUMBER,
48 px_object_version_number IN OUT NOCOPY NUMBER,
49 p_name VARCHAR2,
50 p_description VARCHAR2
51 )
52
53 IS
54 x_rowid VARCHAR2(30);
55 BEGIN
56
57 px_object_version_number := 1;
58
59 INSERT INTO AMS_IBA_PL_PARAMETERS_B(
60 parameter_id,
61 site_id,
62 site_ref_code,
63 parameter_ref_code,
64 execution_order,
65 created_by,
66 creation_date,
67 last_updated_by,
68 last_update_date,
69 last_update_login,
70 object_version_number
71 ) VALUES (
72 DECODE( px_parameter_id, FND_API.g_miss_num, NULL, px_parameter_id),
73 DECODE( p_site_id, FND_API.g_miss_num, NULL, p_site_id),
74 DECODE( p_site_ref_code, FND_API.g_miss_char, NULL, p_site_ref_code),
75 DECODE( p_parameter_ref_code, FND_API.g_miss_char, NULL, p_parameter_ref_code),
76 DECODE( p_execution_order, FND_API.g_miss_num, NULL, p_execution_order),
77 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
78 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
79 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
80 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
81 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
82 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number));
83
84 insert into AMS_IBA_PL_PARAMETERS_TL (
85 PARAMETER_ID,
86 NAME,
87 DESCRIPTION,
88 CREATED_BY,
89 CREATION_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATE_LOGIN,
93 OBJECT_VERSION_NUMBER,
94 LANGUAGE,
95 SOURCE_LANG
96 ) select
97 DECODE( px_parameter_id, FND_API.g_miss_num, NULL, px_parameter_id),
98 DECODE( p_name, FND_API.g_miss_char, NULL, p_name),
99 DECODE( p_description, FND_API.g_miss_char, NULL, p_description),
100 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
101 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
102 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
103 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
104 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
105 l.language_code,
106 USERENV('LANG')
107 from FND_LANGUAGES L
108 where L.INSTALLED_FLAG in ('I', 'B')
109 and not exists
110 (select NULL
111 from AMS_IBA_PL_PARAMETERS_TL T
112 where T.PARAMETER_ID = DECODE( px_parameter_id, FND_API.g_miss_num, NULL, px_parameter_id)
113 and T.LANGUAGE = L.LANGUAGE_CODE);
114
115 END Insert_Row;
116
117
118 ----------------------------------------------------------
119 ---- MEDIA ----
120 ----------------------------------------------------------
121
122 -- ========================================================
123 --
124 -- NAME
125 -- createUpdateBody
126 --
127 -- PURPOSE
128 --
129 -- NOTES
130 --
131 -- HISTORY
132 --
133 -- ========================================================
134 PROCEDURE Update_Row(
135 p_parameter_id NUMBER,
136 p_site_id NUMBER,
137 p_site_ref_code VARCHAR2,
138 p_parameter_ref_code VARCHAR2,
139 p_execution_order NUMBER,
140 p_created_by NUMBER,
141 p_creation_date DATE,
142 p_last_updated_by NUMBER,
143 p_last_update_date DATE,
144 p_last_update_login NUMBER,
145 p_object_version_number NUMBER,
146 p_name VARCHAR2,
147 p_description VARCHAR2
148 )
149
150 IS
151 BEGIN
152 Update AMS_IBA_PL_PARAMETERS_B
153 SET
154 site_id = DECODE( p_site_id, FND_API.g_miss_num, site_id, p_site_id),
155 site_ref_code = DECODE( p_site_ref_code, FND_API.g_miss_char, site_ref_code, p_site_ref_code),
156 parameter_ref_code = DECODE( p_parameter_ref_code, FND_API.g_miss_char, parameter_ref_code, p_parameter_ref_code),
157 execution_order = DECODE( p_execution_order, FND_API.g_miss_num, execution_order, p_execution_order),
158 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
159 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
160 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
161 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number)
162 WHERE PARAMETER_ID = p_PARAMETER_ID
163 AND object_version_number = p_object_version_number;
164
165 IF (SQL%NOTFOUND) THEN
166 RAISE no_data_found;
167 END IF;
168
169 UPDATE ams_iba_pl_parameters_tl
170 SET
171 name = DECODE(p_name,FND_API.g_miss_char,name,p_name),
172 description = DECODE(p_description,FND_API.g_miss_char,description,p_description),
173 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
174 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
175 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
176 source_lang = userenv('LANG')
177 WHERE parameter_id = DECODE( p_parameter_id, FND_API.g_miss_num, parameter_id, p_parameter_id)
178 AND USERENV('lang') IN (language, source_lang);
179
180 IF (SQL%NOTFOUND) THEN
181 RAISE no_data_found;
182 END IF;
183
184
185 END Update_Row;
186
187
188 ----------------------------------------------------------
189 ---- MEDIA ----
190 ----------------------------------------------------------
191
192 -- ========================================================
193 --
194 -- NAME
195 -- createDeleteBody
196 --
197 -- PURPOSE
198 --
199 -- NOTES
200 --
201 -- HISTORY
202 --
203 -- ========================================================
204 PROCEDURE Delete_Row(
205 p_PARAMETER_ID NUMBER)
206 IS
207 BEGIN
208 DELETE FROM AMS_IBA_PL_PARAMETERS_B
209 WHERE PARAMETER_ID = p_PARAMETER_ID;
210 If (SQL%NOTFOUND) then
211 RAISE no_data_found;
212 End If;
213 END Delete_Row ;
214
215 procedure ADD_LANGUAGE
216 is
217 begin
218 delete from AMS_IBA_PL_PARAMETERS_TL T
219 where not exists
220 (select NULL
221 from AMS_IBA_PL_PARAMETERS_B B
222 where B.PARAMETER_ID = T.PARAMETER_ID
223 );
224
225 update AMS_IBA_PL_PARAMETERS_TL T set (
226 NAME,
227 description
228 ) = (select
229 B.NAME,
230 B.description
231 from AMS_IBA_PL_PARAMETERS_TL B
232 where B.PARAMETER_ID = T.PARAMETER_ID
233 and B.LANGUAGE = T.SOURCE_LANG)
234 where (
235 T.PARAMETER_ID,
236 T.LANGUAGE
237 ) in (select
238 SUBT.PARAMETER_ID,
239 SUBT.LANGUAGE
240 from AMS_IBA_PL_PARAMETERS_TL SUBB, AMS_IBA_PL_PARAMETERS_TL SUBT
241 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
242 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
243 and (SUBB.NAME <> SUBT.NAME
244 or SUBB.LANGUAGE <> SUBT.LANGUAGE
245 ));
246
247 insert into AMS_IBA_PL_PARAMETERS_TL (
248 CREATION_DATE,
249 LAST_UPDATED_BY,
250 LAST_UPDATE_DATE,
251 LAST_UPDATE_LOGIN,
252 OBJECT_VERSION_NUMBER,
253 PARAMETER_ID,
254 NAME,
255 DESCRIPTION,
256 CREATED_BY,
257 LANGUAGE,
258 SOURCE_LANG
259 ) select
260 B.CREATION_DATE,
261 B.LAST_UPDATED_BY,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATE_LOGIN,
264 B.OBJECT_VERSION_NUMBER,
265 B.PARAMETER_ID,
266 B.NAME,
267 B.DESCRIPTION,
268 B.CREATED_BY,
269 L.LANGUAGE_CODE,
270 B.SOURCE_LANG
271 from AMS_IBA_PL_PARAMETERS_TL B, FND_LANGUAGES L
272 where L.INSTALLED_FLAG in ('I', 'B')
273 and B.LANGUAGE = userenv('LANG')
274 and not exists
275 (select NULL
276 from AMS_IBA_PL_PARAMETERS_TL T
277 where T.PARAMETER_ID = B.PARAMETER_ID
278 and T.LANGUAGE = L.LANGUAGE_CODE);
279 end ADD_LANGUAGE;
280
281 ----------------------------------------------------------
282 ---- MEDIA ----
283 ----------------------------------------------------------
284
285 -- ========================================================
286 --
287 -- NAME
288 -- createLockBody
289 --
290 -- PURPOSE
291 --
292 -- NOTES
293 --
294 -- HISTORY
295 --
296 -- ========================================================
297 PROCEDURE Lock_Row(
298 p_parameter_id NUMBER,
299 p_site_id NUMBER,
300 p_site_ref_code VARCHAR2,
301 p_parameter_ref_code VARCHAR2,
302 p_execution_order NUMBER,
303 p_created_by NUMBER,
304 p_creation_date DATE,
305 p_last_updated_by NUMBER,
306 p_last_update_date DATE,
307 p_last_update_login NUMBER,
308 p_object_version_number NUMBER,
309 p_name VARCHAR2,
310 p_description VARCHAR2
311 )
312 IS
313 CURSOR C IS
314 SELECT *
315 FROM AMS_IBA_PL_PARAMETERS_B
316 WHERE PARAMETER_ID = p_PARAMETER_ID
317 FOR UPDATE of PARAMETER_ID NOWAIT;
318 Recinfo C%ROWTYPE;
319 BEGIN
320 OPEN c;
321 FETCH c INTO Recinfo;
322 If (c%NOTFOUND) then
323 CLOSE c;
324 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
325 APP_EXCEPTION.RAISE_EXCEPTION;
326 END IF;
327 CLOSE C;
328 IF (
329 ( Recinfo.parameter_id = p_parameter_id)
330 AND ( ( Recinfo.site_id = p_site_id)
331 OR ( ( Recinfo.site_id IS NULL )
332 AND ( p_site_id IS NULL )))
333 AND ( ( Recinfo.site_ref_code = p_site_ref_code)
334 OR ( ( Recinfo.site_ref_code IS NULL )
335 AND ( p_site_ref_code IS NULL )))
336 AND ( ( Recinfo.parameter_ref_code = p_parameter_ref_code)
337 OR ( ( Recinfo.parameter_ref_code IS NULL )
338 AND ( p_parameter_ref_code IS NULL )))
339 AND ( ( Recinfo.execution_order = p_execution_order)
340 OR ( ( Recinfo.execution_order IS NULL )
341 AND ( p_execution_order IS NULL )))
342 AND ( ( Recinfo.created_by = p_created_by)
343 OR ( ( Recinfo.created_by IS NULL )
344 AND ( p_created_by IS NULL )))
345 AND ( ( Recinfo.creation_date = p_creation_date)
346 OR ( ( Recinfo.creation_date IS NULL )
347 AND ( p_creation_date IS NULL )))
348 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
349 OR ( ( Recinfo.last_updated_by IS NULL )
350 AND ( p_last_updated_by IS NULL )))
351 AND ( ( Recinfo.last_update_date = p_last_update_date)
352 OR ( ( Recinfo.last_update_date IS NULL )
353 AND ( p_last_update_date IS NULL )))
354 AND ( ( Recinfo.last_update_login = p_last_update_login)
355 OR ( ( Recinfo.last_update_login IS NULL )
356 AND ( p_last_update_login IS NULL )))
357 AND ( ( Recinfo.object_version_number = p_object_version_number)
358 OR ( ( Recinfo.object_version_number IS NULL )
359 AND ( p_object_version_number IS NULL )))
360 ) THEN
361 RETURN;
362 ELSE
363 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
364 APP_EXCEPTION.RAISE_EXCEPTION;
365 END IF;
366 END Lock_Row;
367
368 PROCEDURE translate_row (
369 x_parameter_id IN NUMBER,
370 x_name IN VARCHAR2,
371 x_description IN VARCHAR2,
372 x_owner IN VARCHAR2,
373 x_custom_mode IN VARCHAR2
374 )
375 IS
376 cursor c_last_updated_by is
377 select last_updated_by
378 from ams_iba_pl_parameters_tl
379 where parameter_id = x_parameter_id
380 and USERENV('LANG') = LANGUAGE;
381
382 l_luby number; --last updated by
383
384 BEGIN
385 open c_last_updated_by;
386 fetch c_last_updated_by into l_luby;
387 close c_last_updated_by;
388
389 if (l_luby IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
390 then
391
392 update ams_iba_pl_parameters_tl set
393 name = nvl(x_name, name),
394 description = nvl(x_description, description),
395 source_lang = userenv('LANG'),
396 last_update_date = sysdate,
397 last_updated_by = decode(x_owner, 'SEED', 1,
398 'ORACLE', 2,
399 'SYSADMIN', 0, -1),
400 last_update_login = 0
401 where parameter_id = x_parameter_id
402 and userenv('LANG') in (language, source_lang);
403 end if;
404 END translate_row;
405
406 PROCEDURE load_row (
407 x_parameter_id IN NUMBER,
408 x_site_id IN NUMBER,
409 x_site_ref_code IN VARCHAR2,
410 x_parameter_ref_code IN VARCHAR2,
411 x_execution_order IN NUMBER,
412 x_name IN VARCHAR2,
413 x_description IN VARCHAR2,
414 x_owner IN VARCHAR2,
415 X_CUSTOM_MODE IN VARCHAR2
416 )
417 IS
418 l_user_id number := 1;
419 l_obj_verno number;
420 l_dummy_char varchar2(1);
421 l_row_id varchar2(100);
422 l_parameter_id number;
423 l_db_luby_id number;
424
425 /* cursor c_obj_verno is
426 select object_version_number
427 from ams_iba_pl_parameters_b
428 where parameter_id = x_parameter_id;*/
429
430 cursor c_db_data_details is
431 select last_updated_by, nvl(object_version_number,1)
432 from ams_iba_pl_parameters_b
436 select 'x'
433 where parameter_id = x_parameter_id;
434
435 cursor c_chk_parameter_exists is
437 from ams_iba_pl_parameters_b
438 where parameter_id = x_parameter_id;
439
440 cursor c_get_parameter_id is
441 select ams_iba_pl_params_b_s.nextval
442 from dual;
443 BEGIN
444 if X_OWNER = 'SEED' then
445 l_user_id := 1;
446 elsif X_OWNER = 'ORACLE' then
447 l_user_id := 2;
448 elsif X_OWNER = 'SYSADMIN' then
449 l_user_id := 0;
450 end if;
451
452 open c_chk_parameter_exists;
453 fetch c_chk_parameter_exists into l_dummy_char;
454 if c_chk_parameter_exists%notfound THEN
455 if x_parameter_id is null then
456 open c_get_parameter_id;
457 fetch c_get_parameter_id into l_parameter_id;
458 close c_get_parameter_id;
459 else
460 l_parameter_id := x_parameter_id;
461 end if;
462 l_obj_verno := 1;
463
464 AMS_IBA_PL_PARAMETERS_B_PKG.Insert_Row (
465 px_parameter_id => l_parameter_id,
466 p_site_id => x_site_id,
467 p_site_ref_code => x_site_ref_code,
468 p_parameter_ref_code => x_parameter_ref_code,
469 p_execution_order => x_execution_order,
470 p_created_by => l_user_id,
471 p_creation_date => SYSDATE,
472 p_last_updated_by => l_user_id,
473 p_last_update_date => SYSDATE,
474 p_last_update_login => 1,
475 px_object_version_number => l_obj_verno,
476 p_name => x_name,
477 p_description => x_description
478 );
479 else
480 open c_db_data_details;
481 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
482 close c_db_data_details;
483
484 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
485 then
486 AMS_IBA_PL_PARAMETERS_B_PKG.UPDATE_ROW (
487 p_parameter_id => x_parameter_id,
488 p_site_id => x_site_id,
489 p_site_ref_code => x_site_ref_code,
490 p_parameter_ref_code => x_parameter_ref_code,
491 p_execution_order => x_execution_order,
492 p_created_by => l_user_id,
493 p_creation_date => SYSDATE,
494 p_last_updated_by => l_user_id,
495 p_last_update_date => SYSDATE,
496 p_last_update_login => 1,
497 p_object_version_number => l_obj_verno,
498 p_name => x_name,
499 p_description => x_description
500 );
501 end if;
502 end if;
503 close c_chk_parameter_exists;
504 END load_row;
505
506 END AMS_IBA_PL_PARAMETERS_B_PKG;