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