[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PS_POSTINGS_B_PKG
Source
1 PACKAGE BODY AMS_IBA_PS_POSTINGS_B_PKG as
2 /* $Header: amstpstb.pls 115.9 2002/12/19 04:16:57 ryedator ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IBA_PS_POSTINGS_B_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstpstb.pls';
6
7 ----------------------------------------------------------
8 ---- MEDIA ----
9 ----------------------------------------------------------
10
11 -- ========================================================
12 --
13 -- NAME
14 -- createInsertBody
15 --
16 -- PURPOSE
17 --
18 -- NOTES
19 --
20 -- HISTORY
21 --
22 -- ========================================================
23 PROCEDURE Insert_Row(
24 p_created_by NUMBER,
25 p_creation_date DATE,
26 p_last_updated_by NUMBER,
27 p_last_update_date DATE,
28 p_last_update_login NUMBER,
29 px_object_version_number IN OUT NOCOPY NUMBER,
30 px_posting_id IN OUT NOCOPY NUMBER,
31 p_max_no_contents NUMBER,
32 p_posting_type VARCHAR2,
33 p_content_type VARCHAR2,
34 p_default_content_id NUMBER,
35 p_status_code VARCHAR2,
36 p_posting_name IN VARCHAR2,
37 p_display_name IN VARCHAR2,
38 p_posting_description IN VARCHAR2,
39 p_attribute_category VARCHAR2,
40 p_attribute1 VARCHAR2,
41 p_attribute2 VARCHAR2,
42 p_attribute3 VARCHAR2,
43 p_attribute4 VARCHAR2,
44 p_attribute5 VARCHAR2,
45 p_attribute6 VARCHAR2,
46 p_attribute7 VARCHAR2,
47 p_attribute8 VARCHAR2,
48 p_attribute9 VARCHAR2,
49 p_attribute10 VARCHAR2,
50 p_attribute11 VARCHAR2,
51 p_attribute12 VARCHAR2,
52 p_attribute13 VARCHAR2,
53 p_attribute14 VARCHAR2,
54 p_attribute15 VARCHAR2)
55
56 IS
57 x_rowid VARCHAR2(30);
58
59 BEGIN
60
61 px_object_version_number := 1;
62
63 INSERT INTO AMS_IBA_PS_POSTINGS_B(
64 created_by,
65 creation_date,
66 last_updated_by,
67 last_update_date,
68 last_update_login,
69 object_version_number,
70 posting_id,
71 max_no_contents,
72 posting_type,
73 content_type,
74 default_content_id,
75 status_code,
76 attribute_category,
77 attribute1,
78 attribute2,
79 attribute3,
80 attribute4,
81 attribute5,
82 attribute6,
83 attribute7,
84 attribute8,
85 attribute9,
86 attribute10,
87 attribute11,
88 attribute12,
89 attribute13,
90 attribute14,
91 attribute15
92
93 ) VALUES (
94 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
95 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
96 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
97 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
98 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
99 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
100 DECODE( px_posting_id, FND_API.g_miss_num, NULL, px_posting_id),
101 DECODE( p_max_no_contents, FND_API.g_miss_num, NULL, p_max_no_contents),
102 DECODE( p_posting_type, FND_API.g_miss_char, NULL, p_posting_type),
103 DECODE( p_content_type, FND_API.g_miss_char, NULL, p_content_type),
104 DECODE( p_default_content_id, FND_API.g_miss_num, NULL, p_default_content_id),
105 DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
106 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
107 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
108 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
109 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
110 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
111 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
112 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
113 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
114 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
115 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
116 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
117 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
118 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
119 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
120 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
121 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15));
122
123
124 INSERT INTO ams_iba_ps_postings_tl (
125 created_by,
126 creation_date,
127 last_update_date,
128 last_update_login,
129 last_updated_by,
130 object_version_number,
131 posting_id,
132 posting_name,
133 display_name,
134 posting_description,
135 language,
136 source_lang
137 ) SELECT
138 FND_GLOBAL.user_id,
139 SYSDATE,
140 SYSDATE,
141 FND_GLOBAL.conc_login_id,
142 FND_GLOBAL.conc_login_id,
143 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
144 DECODE( px_posting_id, FND_API.g_miss_num, NULL, px_posting_id),
145 decode( p_posting_name, FND_API.G_MISS_CHAR, NULL, p_posting_name),
146 decode( p_display_name, FND_API.G_MISS_CHAR, NULL, p_display_name),
147 decode( p_posting_description, FND_API.G_MISS_CHAR, NULL, p_posting_description),
148 l.language_code,
149 USERENV('LANG')
150 FROM fnd_languages l
151 WHERE l.installed_flag IN ('I', 'B')
152 AND NOT EXISTS
153 (SELECT null
154 FROM ams_iba_ps_postings_tl t
155 WHERE t.posting_id = DECODE( px_posting_id, FND_API.g_miss_num, NULL, px_posting_id)
156 AND t.language = l.language_code);
157
158 END Insert_Row;
159
160
161 ----------------------------------------------------------
162 ---- MEDIA ----
163 ----------------------------------------------------------
164
165 -- ========================================================
166 --
167 -- NAME
168 -- createUpdateBody
169 --
170 -- PURPOSE
171 --
172 -- NOTES
173 --
174 -- HISTORY
175 --
176 -- ========================================================
177 PROCEDURE Update_Row(
178 p_created_by NUMBER,
179 p_creation_date DATE,
180 p_last_updated_by NUMBER,
181 p_last_update_date DATE,
182 p_last_update_login NUMBER,
183 p_object_version_number NUMBER,
184 p_posting_id NUMBER,
185 p_max_no_contents NUMBER,
186 p_posting_type VARCHAR2,
187 p_content_type VARCHAR2,
188 p_default_content_id NUMBER,
189 p_status_code VARCHAR2,
190 p_posting_name IN VARCHAR2,
191 p_display_name IN VARCHAR2,
192 p_posting_description IN VARCHAR2,
193 p_attribute_category VARCHAR2,
194 p_attribute1 VARCHAR2,
195 p_attribute2 VARCHAR2,
196 p_attribute3 VARCHAR2,
197 p_attribute4 VARCHAR2,
198 p_attribute5 VARCHAR2,
199 p_attribute6 VARCHAR2,
200 p_attribute7 VARCHAR2,
201 p_attribute8 VARCHAR2,
202 p_attribute9 VARCHAR2,
203 p_attribute10 VARCHAR2,
204 p_attribute11 VARCHAR2,
205 p_attribute12 VARCHAR2,
206 p_attribute13 VARCHAR2,
207 p_attribute14 VARCHAR2,
208 p_attribute15 VARCHAR2)
209
210 IS
211 BEGIN
212 Update AMS_IBA_PS_POSTINGS_B
213 SET
214 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
215 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
216 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
217 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
218 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
219 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
220 posting_id = DECODE( p_posting_id, FND_API.g_miss_num, posting_id, p_posting_id),
221 max_no_contents = DECODE( p_max_no_contents, FND_API.g_miss_num, max_no_contents, p_max_no_contents),
222 posting_type = DECODE( p_posting_type, FND_API.g_miss_char, posting_type, p_posting_type),
223 content_type = DECODE( p_content_type, FND_API.g_miss_char, content_type, p_content_type),
224 default_content_id = DECODE( p_default_content_id, FND_API.g_miss_num, default_content_id, p_default_content_id),
225 status_code = DECODE( p_status_code, FND_API.g_miss_char, status_code, p_status_code),
226 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
227 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
228 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
229 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
230 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
231 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
232 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
233 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
234 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
235 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
236 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
237 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
238 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
239 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
240 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
241 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15)
242
243 WHERE posting_id = p_posting_id
244 AND object_version_number = p_object_version_number;
245
246 IF (SQL%NOTFOUND) THEN
247 RAISE FND_API.G_EXC_ERROR;
248 END IF;
249
250
251 UPDATE ams_iba_ps_postings_tl SET
252 posting_name = decode( p_posting_name, FND_API.G_MISS_CHAR, posting_name, p_posting_name),
253 display_name = decode( p_display_name, FND_API.G_MISS_CHAR, display_name, p_posting_name),
254 posting_description = decode( p_posting_description, FND_API.G_MISS_CHAR, posting_description, p_posting_description),
255 last_update_date = SYSDATE,
256 last_updated_by = FND_GLOBAL.user_id,
260 AND USERENV('LANG') IN (language, source_lang);
257 last_update_login = FND_GLOBAL.conc_login_id,
258 source_lang = USERENV('LANG')
259 WHERE posting_id = p_posting_id
261
262 IF (SQL%NOTFOUND) THEN
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 END Update_Row;
267
268 ----------------------------------------------------------
269 ---- MEDIA ----
270 ----------------------------------------------------------
271
272 -- ======================================================
273 --
274 -- NAME
275 -- createDeleteBody
276 --
277 -- PURPOSE
278 --
279 -- NOTES
280 --
281 -- HISTORY
282 --
283 -- ========================================================
284 PROCEDURE Delete_Row(
285 p_POSTING_ID NUMBER)
286 IS
287 BEGIN
288 DELETE FROM AMS_IBA_PS_POSTINGS_B
289 WHERE POSTING_ID = p_POSTING_ID;
290 If (SQL%NOTFOUND) then
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 End If;
293 END Delete_Row ;
294
295
296 ----------------------------------------------------------
297 ---- MEDIA ----
298 ----------------------------------------------------------
299
300 -- ======================================================
301 --
302 -- NAME
303 -- createLockBody
304 --
305 -- PURPOSE
306 --
307 -- NOTES
308 --
309 -- HISTORY
310 --
311 -- ========================================================
312 PROCEDURE Lock_Row(
313 p_created_by NUMBER,
314 p_creation_date DATE,
315 p_last_updated_by NUMBER,
316 p_last_update_date DATE,
317 p_last_update_login NUMBER,
318 p_object_version_number NUMBER,
319 p_posting_id NUMBER,
320 p_max_no_contents NUMBER,
321 p_posting_type VARCHAR2,
322 p_content_type VARCHAR2,
323 p_default_content_id NUMBER,
324 p_status_code VARCHAR2,
325 p_attribute_category VARCHAR2,
326 p_attribute1 VARCHAR2,
327 p_attribute2 VARCHAR2,
328 p_attribute3 VARCHAR2,
329 p_attribute4 VARCHAR2,
330 p_attribute5 VARCHAR2,
331 p_attribute6 VARCHAR2,
332 p_attribute7 VARCHAR2,
333 p_attribute8 VARCHAR2,
334 p_attribute9 VARCHAR2,
335 p_attribute10 VARCHAR2,
336 p_attribute11 VARCHAR2,
337 p_attribute12 VARCHAR2,
338 p_attribute13 VARCHAR2,
339 p_attribute14 VARCHAR2,
340 p_attribute15 VARCHAR2)
341
342 IS
343 CURSOR C IS
344 SELECT *
345 FROM AMS_IBA_PS_POSTINGS_B
346 WHERE POSTING_ID = p_POSTING_ID
347 FOR UPDATE of POSTING_ID NOWAIT;
348 Recinfo C%ROWTYPE;
349 BEGIN
350 OPEN c;
351 FETCH c INTO Recinfo;
352 If (c%NOTFOUND) then
353 CLOSE c;
354 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
355 APP_EXCEPTION.RAISE_EXCEPTION;
356 END IF;
357 CLOSE C;
358 IF (
359 ( Recinfo.created_by = p_created_by)
360 AND ( ( Recinfo.creation_date = p_creation_date)
361 OR ( ( Recinfo.creation_date IS NULL )
362 AND ( p_creation_date IS NULL )))
363 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
364 OR ( ( Recinfo.last_updated_by IS NULL )
365 AND ( p_last_updated_by IS NULL )))
366 AND ( ( Recinfo.last_update_date = p_last_update_date)
367 OR ( ( Recinfo.last_update_date IS NULL )
368 AND ( p_last_update_date IS NULL )))
369 AND ( ( Recinfo.last_update_login = p_last_update_login)
370 OR ( ( Recinfo.last_update_login IS NULL )
371 AND ( p_last_update_login IS NULL )))
372 AND ( ( Recinfo.object_version_number = p_object_version_number)
373 OR ( ( Recinfo.object_version_number IS NULL )
374 AND ( p_object_version_number IS NULL )))
375 AND ( ( Recinfo.posting_id = p_posting_id)
376 OR ( ( Recinfo.posting_id IS NULL )
377 AND ( p_posting_id IS NULL )))
378 AND ( ( Recinfo.max_no_contents = p_max_no_contents)
379 OR ( ( Recinfo.max_no_contents IS NULL )
380 AND ( p_max_no_contents IS NULL )))
381 AND ( ( Recinfo.posting_type = p_posting_type)
382 OR ( ( Recinfo.posting_type IS NULL )
383 AND ( p_posting_type IS NULL )))
384 AND ( ( Recinfo.content_type = p_content_type)
385 OR ( ( Recinfo.content_type IS NULL )
386 AND ( p_content_type IS NULL )))
387 AND ( ( Recinfo.default_content_id = p_default_content_id)
388 OR ( ( Recinfo.default_content_id IS NULL )
389 AND ( p_default_content_id IS NULL )))
390 AND ( ( Recinfo.status_code = p_status_code)
391 OR ( ( Recinfo.status_code IS NULL )
392 AND ( p_status_code IS NULL )))
393 ) THEN
394 RETURN;
395 ELSE
396 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
397 APP_EXCEPTION.RAISE_EXCEPTION;
398 END IF;
399 END Lock_Row;
400
401 procedure ADD_LANGUAGE
402 is
403 begin
404 delete from AMS_IBA_PS_POSTINGS_TL T
405 where not exists
406 (select NULL
407 from AMS_IBA_PS_POSTINGS_B B
408 where B.POSTING_ID = T.POSTING_ID
409 );
410
411 update AMS_IBA_PS_POSTINGS_TL T set (
415 ) = (select
412 POSTING_NAME,
413 DISPLAY_NAME,
414 POSTING_DESCRIPTION
416 B.POSTING_NAME,
417 B.DISPLAY_NAME,
418 B.POSTING_DESCRIPTION
419 from AMS_IBA_PS_POSTINGS_TL B
420 where B.POSTING_ID = T.POSTING_ID
421 and B.LANGUAGE = T.SOURCE_LANG)
422 where (
423 T.POSTING_ID,
424 T.LANGUAGE
425 ) in (select
426 SUBT.POSTING_ID,
427 SUBT.LANGUAGE
428 from AMS_IBA_PS_POSTINGS_TL SUBB, AMS_IBA_PS_POSTINGS_TL SUBT
429 where SUBB.POSTING_ID = SUBT.POSTING_ID
430 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
431 and (SUBB.POSTING_NAME <> SUBT.POSTING_NAME
432 or SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
433 or SUBB.POSTING_DESCRIPTION <> SUBT.POSTING_DESCRIPTION
434 or (SUBB.POSTING_DESCRIPTION is null and SUBT.POSTING_DESCRIPTION is not null)
435 or (SUBB.POSTING_DESCRIPTION is not null and SUBT.POSTING_DESCRIPTION is null)
436 ));
437
438 insert into AMS_IBA_PS_POSTINGS_TL (
439 CREATED_BY,
440 CREATION_DATE,
441 LAST_UPDATE_DATE,
442 LAST_UPDATE_LOGIN,
443 LAST_UPDATED_BY,
444 OBJECT_VERSION_NUMBER,
445 POSTING_ID,
446 POSTING_NAME,
447 DISPLAY_NAME,
448 POSTING_DESCRIPTION,
449 LANGUAGE,
450 SOURCE_LANG
451 ) select
452 B.CREATED_BY,
453 B.CREATION_DATE,
454 B.LAST_UPDATE_DATE,
455 B.LAST_UPDATE_LOGIN,
456 B.LAST_UPDATED_BY,
457 B.OBJECT_VERSION_NUMBER,
458 B.POSTING_ID,
459 B.POSTING_NAME,
460 B.DISPLAY_NAME,
461 B.POSTING_DESCRIPTION,
462 L.LANGUAGE_CODE,
463 B.SOURCE_LANG
464 from AMS_IBA_PS_POSTINGS_TL B, FND_LANGUAGES L
465 where L.INSTALLED_FLAG in ('I', 'B')
466 and B.LANGUAGE = userenv('LANG')
467 and not exists
468 (select NULL
469 from AMS_IBA_PS_POSTINGS_TL T
470 where T.POSTING_ID = B.POSTING_ID
471 and T.LANGUAGE = L.LANGUAGE_CODE);
472 end ADD_LANGUAGE;
473
474 END AMS_IBA_PS_POSTINGS_B_PKG;