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