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