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