1 package body AMS_WEB_PLACEMENTS_PKG as
2 /* $Header: amstwplb.pls 120.1 2005/06/27 05:40:38 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_PLACEMENT_ID in NUMBER,
6 X_PLACEMENT_CATEGORY in VARCHAR2,
7 X_SITE_ID in NUMBER,
8 X_SITE_REF_CODE in VARCHAR2,
9 X_PAGE_ID in NUMBER,
10 X_PAGE_REF_CODE in VARCHAR2,
11 X_LOCATION_CODE in VARCHAR2,
12 X_SITE_PARAM1 in NUMBER,
13 X_SITE_PARAM2 in NUMBER,
14 X_SITE_PARAM3 in NUMBER,
15 X_SITE_PARAM4 in NUMBER,
16 X_SITE_PARAM5 in NUMBER,
17 X_DEFAULT_CONTENT_ITEM_ID in NUMBER,
18 X_DEFAULT_CITEM_VER_ID in NUMBER,
19 X_DEFAULT_STYLESHEET_ID in NUMBER,
20 X_DEFAULT_STYLESHEET_VER_ID in NUMBER,
21 X_STATUS_CODE in VARCHAR2,
22 X_AUTO_PUBLISH_FLAG in VARCHAR2,
23 X_DISPLAY_SELECTION_CODE in VARCHAR2,
24 X_SECURITY_GROUP_ID in NUMBER,
25 X_OBJECT_VERSION_NUMBER in NUMBER,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2,
27 X_ATTRIBUTE1 in VARCHAR2,
28 X_ATTRIBUTE2 in VARCHAR2,
29 X_ATTRIBUTE3 in VARCHAR2,
30 X_ATTRIBUTE4 in VARCHAR2,
31 X_ATTRIBUTE5 in VARCHAR2,
32 X_ATTRIBUTE6 in VARCHAR2,
33 X_ATTRIBUTE7 in VARCHAR2,
34 X_ATTRIBUTE8 in VARCHAR2,
35 X_ATTRIBUTE9 in VARCHAR2,
36 X_ATTRIBUTE10 in VARCHAR2,
37 X_ATTRIBUTE11 in VARCHAR2,
38 X_ATTRIBUTE12 in VARCHAR2,
39 X_ATTRIBUTE13 in VARCHAR2,
40 X_ATTRIBUTE14 in VARCHAR2,
41 X_ATTRIBUTE15 in VARCHAR2,
42 X_PLACEMENT_NAME in VARCHAR2,
43 X_DESCRIPTION in VARCHAR2,
44 X_CREATION_DATE in DATE,
45 X_CREATED_BY in NUMBER,
46 X_LAST_UPDATE_DATE in DATE,
47 X_LAST_UPDATED_BY in NUMBER,
48 X_LAST_UPDATE_LOGIN in NUMBER
49 ) is
50 cursor C is select ROWID from AMS_WEB_PLACEMENTS_B
51 where PLACEMENT_ID = X_PLACEMENT_ID
52 ;
53 begin
54 insert into AMS_WEB_PLACEMENTS_B (
55 PLACEMENT_ID,
56 PLACEMENT_CATEGORY,
57 SITE_ID,
58 SITE_REF_CODE,
59 PAGE_ID,
60 PAGE_REF_CODE,
61 LOCATION_CODE,
62 SITE_PARAM1,
63 SITE_PARAM2,
64 SITE_PARAM3,
65 SITE_PARAM4,
66 SITE_PARAM5,
67 DEFAULT_CONTENT_ITEM_ID,
68 DEFAULT_CITEM_VER_ID,
69 DEFAULT_STYLESHEET_ID,
70 DEFAULT_STYLESHEET_VER_ID,
71 STATUS_CODE,
72 AUTO_PUBLISH_FLAG,
73 DISPLAY_SELECTION_CODE,
74 SECURITY_GROUP_ID,
75 OBJECT_VERSION_NUMBER,
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 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_LOGIN
97 ) values (
98 X_PLACEMENT_ID,
99 X_PLACEMENT_CATEGORY,
100 X_SITE_ID,
101 X_SITE_REF_CODE,
102 X_PAGE_ID,
103 X_PAGE_REF_CODE,
104 X_LOCATION_CODE,
105 X_SITE_PARAM1,
106 X_SITE_PARAM2,
107 X_SITE_PARAM3,
108 X_SITE_PARAM4,
109 X_SITE_PARAM5,
110 X_DEFAULT_CONTENT_ITEM_ID,
111 X_DEFAULT_CITEM_VER_ID,
112 X_DEFAULT_STYLESHEET_ID,
113 X_DEFAULT_STYLESHEET_VER_ID,
114 X_STATUS_CODE,
115 X_AUTO_PUBLISH_FLAG,
116 X_DISPLAY_SELECTION_CODE,
117 X_SECURITY_GROUP_ID,
118 X_OBJECT_VERSION_NUMBER,
119 X_ATTRIBUTE_CATEGORY,
120 X_ATTRIBUTE1,
121 X_ATTRIBUTE2,
122 X_ATTRIBUTE3,
123 X_ATTRIBUTE4,
124 X_ATTRIBUTE5,
125 X_ATTRIBUTE6,
126 X_ATTRIBUTE7,
127 X_ATTRIBUTE8,
128 X_ATTRIBUTE9,
129 X_ATTRIBUTE10,
130 X_ATTRIBUTE11,
131 X_ATTRIBUTE12,
132 X_ATTRIBUTE13,
133 X_ATTRIBUTE14,
134 X_ATTRIBUTE15,
135 X_CREATION_DATE,
136 X_CREATED_BY,
137 X_LAST_UPDATE_DATE,
138 X_LAST_UPDATED_BY,
139 X_LAST_UPDATE_LOGIN
140 );
141
142 insert into AMS_WEB_PLACEMENTS_TL (
143 PLACEMENT_ID,
144 PLACEMENT_NAME,
145 DESCRIPTION,
146 CREATED_BY,
147 CREATION_DATE,
148 LAST_UPDATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATE_LOGIN,
151 SECURITY_GROUP_ID,
152 OBJECT_VERSION_NUMBER,
153 LANGUAGE,
154 SOURCE_LANG
155 ) select
156 X_PLACEMENT_ID,
157 X_PLACEMENT_NAME,
158 X_DESCRIPTION,
159 X_CREATED_BY,
160 X_CREATION_DATE,
161 X_LAST_UPDATED_BY,
162 X_LAST_UPDATE_DATE,
163 X_LAST_UPDATE_LOGIN,
164 X_SECURITY_GROUP_ID,
165 X_OBJECT_VERSION_NUMBER,
166 L.LANGUAGE_CODE,
167 userenv('LANG')
168 from FND_LANGUAGES L
169 where L.INSTALLED_FLAG in ('I', 'B')
170 and not exists
171 (select NULL
172 from AMS_WEB_PLACEMENTS_TL T
173 where T.PLACEMENT_ID = X_PLACEMENT_ID
174 and T.LANGUAGE = L.LANGUAGE_CODE);
175
176 open c;
177 fetch c into X_ROWID;
178 if (c%notfound) then
179 close c;
180 raise no_data_found;
181 end if;
182 close c;
183
184 end INSERT_ROW;
185
186 procedure LOCK_ROW (
187 X_PLACEMENT_ID in NUMBER,
188 X_PLACEMENT_CATEGORY in VARCHAR2,
189 X_SITE_ID in NUMBER,
190 X_SITE_REF_CODE in VARCHAR2,
191 X_PAGE_ID in NUMBER,
192 X_PAGE_REF_CODE in VARCHAR2,
193 X_LOCATION_CODE in VARCHAR2,
194 X_SITE_PARAM1 in NUMBER,
195 X_SITE_PARAM2 in NUMBER,
196 X_SITE_PARAM3 in NUMBER,
197 X_SITE_PARAM4 in NUMBER,
198 X_SITE_PARAM5 in NUMBER,
199 X_DEFAULT_CONTENT_ITEM_ID in NUMBER,
200 X_DEFAULT_CITEM_VER_ID in NUMBER,
201 X_DEFAULT_STYLESHEET_ID in NUMBER,
202 X_DEFAULT_STYLESHEET_VER_ID in NUMBER,
203 X_STATUS_CODE in VARCHAR2,
204 X_AUTO_PUBLISH_FLAG in VARCHAR2,
205 X_DISPLAY_SELECTION_CODE in VARCHAR2,
206 X_SECURITY_GROUP_ID in NUMBER,
207 X_OBJECT_VERSION_NUMBER in NUMBER,
208 X_ATTRIBUTE_CATEGORY in VARCHAR2,
209 X_ATTRIBUTE1 in VARCHAR2,
210 X_ATTRIBUTE2 in VARCHAR2,
211 X_ATTRIBUTE3 in VARCHAR2,
212 X_ATTRIBUTE4 in VARCHAR2,
213 X_ATTRIBUTE5 in VARCHAR2,
214 X_ATTRIBUTE6 in VARCHAR2,
215 X_ATTRIBUTE7 in VARCHAR2,
216 X_ATTRIBUTE8 in VARCHAR2,
217 X_ATTRIBUTE9 in VARCHAR2,
218 X_ATTRIBUTE10 in VARCHAR2,
219 X_ATTRIBUTE11 in VARCHAR2,
220 X_ATTRIBUTE12 in VARCHAR2,
221 X_ATTRIBUTE13 in VARCHAR2,
222 X_ATTRIBUTE14 in VARCHAR2,
223 X_ATTRIBUTE15 in VARCHAR2,
224 X_PLACEMENT_NAME in VARCHAR2,
225 X_DESCRIPTION in VARCHAR2
226 ) is
227 cursor c is select
228 PLACEMENT_CATEGORY,
229 SITE_ID,
230 SITE_REF_CODE,
231 PAGE_ID,
232 PAGE_REF_CODE,
233 LOCATION_CODE,
234 SITE_PARAM1,
235 SITE_PARAM2,
236 SITE_PARAM3,
237 SITE_PARAM4,
238 SITE_PARAM5,
239 DEFAULT_CONTENT_ITEM_ID,
240 DEFAULT_CITEM_VER_ID,
244 AUTO_PUBLISH_FLAG,
241 DEFAULT_STYLESHEET_ID,
242 DEFAULT_STYLESHEET_VER_ID,
243 STATUS_CODE,
245 DISPLAY_SELECTION_CODE,
246 SECURITY_GROUP_ID,
247 OBJECT_VERSION_NUMBER,
248 ATTRIBUTE_CATEGORY,
249 ATTRIBUTE1,
250 ATTRIBUTE2,
251 ATTRIBUTE3,
252 ATTRIBUTE4,
253 ATTRIBUTE5,
254 ATTRIBUTE6,
255 ATTRIBUTE7,
256 ATTRIBUTE8,
257 ATTRIBUTE9,
258 ATTRIBUTE10,
259 ATTRIBUTE11,
260 ATTRIBUTE12,
261 ATTRIBUTE13,
262 ATTRIBUTE14,
263 ATTRIBUTE15
264 from AMS_WEB_PLACEMENTS_B
265 where PLACEMENT_ID = X_PLACEMENT_ID
266 for update of PLACEMENT_ID nowait;
267 recinfo c%rowtype;
268
269 cursor c1 is select
270 PLACEMENT_NAME,
271 DESCRIPTION,
272 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
273 from AMS_WEB_PLACEMENTS_TL
274 where PLACEMENT_ID = X_PLACEMENT_ID
275 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
276 for update of PLACEMENT_ID nowait;
277 begin
278 open c;
279 fetch c into recinfo;
280 if (c%notfound) then
281 close c;
282 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
283 app_exception.raise_exception;
284 end if;
285 close c;
286 if ( ((recinfo.PLACEMENT_CATEGORY = X_PLACEMENT_CATEGORY)
287 OR ((recinfo.PLACEMENT_CATEGORY is null) AND (X_PLACEMENT_CATEGORY is null)))
288 AND (recinfo.SITE_ID = X_SITE_ID)
289 AND ((recinfo.SITE_REF_CODE = X_SITE_REF_CODE)
290 OR ((recinfo.SITE_REF_CODE is null) AND (X_SITE_REF_CODE is null)))
291 AND (recinfo.PAGE_ID = X_PAGE_ID)
292 AND ((recinfo.PAGE_REF_CODE = X_PAGE_REF_CODE)
293 OR ((recinfo.PAGE_REF_CODE is null) AND (X_PAGE_REF_CODE is null)))
294 AND (recinfo.LOCATION_CODE = X_LOCATION_CODE)
295 AND ((recinfo.SITE_PARAM1 = X_SITE_PARAM1)
296 OR ((recinfo.SITE_PARAM1 is null) AND (X_SITE_PARAM1 is null)))
297 AND ((recinfo.SITE_PARAM2 = X_SITE_PARAM2)
298 OR ((recinfo.SITE_PARAM2 is null) AND (X_SITE_PARAM2 is null)))
299 AND ((recinfo.SITE_PARAM3 = X_SITE_PARAM3)
300 OR ((recinfo.SITE_PARAM3 is null) AND (X_SITE_PARAM3 is null)))
301 AND ((recinfo.SITE_PARAM4 = X_SITE_PARAM4)
302 OR ((recinfo.SITE_PARAM4 is null) AND (X_SITE_PARAM4 is null)))
303 AND ((recinfo.SITE_PARAM5 = X_SITE_PARAM5)
304 OR ((recinfo.SITE_PARAM5 is null) AND (X_SITE_PARAM5 is null)))
305 AND ((recinfo.DEFAULT_CONTENT_ITEM_ID = X_DEFAULT_CONTENT_ITEM_ID)
306 OR ((recinfo.DEFAULT_CONTENT_ITEM_ID is null) AND (X_DEFAULT_CONTENT_ITEM_ID is null)))
307 AND ((recinfo.DEFAULT_CITEM_VER_ID = X_DEFAULT_CITEM_VER_ID)
308 OR ((recinfo.DEFAULT_CITEM_VER_ID is null) AND (X_DEFAULT_CITEM_VER_ID is null)))
309 AND ((recinfo.DEFAULT_STYLESHEET_ID = X_DEFAULT_STYLESHEET_ID)
310 OR ((recinfo.DEFAULT_STYLESHEET_ID is null) AND (X_DEFAULT_STYLESHEET_ID is null)))
311 AND ((recinfo.DEFAULT_STYLESHEET_VER_ID = X_DEFAULT_STYLESHEET_VER_ID)
312 OR ((recinfo.DEFAULT_STYLESHEET_VER_ID is null) AND (X_DEFAULT_STYLESHEET_VER_ID is null)))
313 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
314 AND ((recinfo.AUTO_PUBLISH_FLAG = X_AUTO_PUBLISH_FLAG)
315 OR ((recinfo.AUTO_PUBLISH_FLAG is null) AND (X_AUTO_PUBLISH_FLAG is null)))
316 AND ((recinfo.DISPLAY_SELECTION_CODE = X_DISPLAY_SELECTION_CODE)
317 OR ((recinfo.DISPLAY_SELECTION_CODE is null) AND (X_DISPLAY_SELECTION_CODE is null)))
318 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
319 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
320 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
321 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
322 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
323 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
324 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
325 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
326 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
327 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
328 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
329 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
330 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
331 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
332 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
333 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
334 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
335 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
336 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
337 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
338 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
339 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
340 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
341 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
342 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
343 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
344 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
345 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
346 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
347 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
348 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
349 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
350 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
351 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
352 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
353 ) then
354 null;
355 else
359
356 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
357 app_exception.raise_exception;
358 end if;
360 for tlinfo in c1 loop
361 if (tlinfo.BASELANG = 'Y') then
362 if ( (tlinfo.PLACEMENT_NAME = X_PLACEMENT_NAME)
363 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
364 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
368 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
365 ) then
366 null;
367 else
369 app_exception.raise_exception;
370 end if;
371 end if;
372 end loop;
373 return;
374 end LOCK_ROW;
375
376 procedure UPDATE_ROW (
377 X_PLACEMENT_ID in NUMBER,
378 X_PLACEMENT_CATEGORY in VARCHAR2,
379 X_SITE_ID in NUMBER,
380 X_SITE_REF_CODE in VARCHAR2,
381 X_PAGE_ID in NUMBER,
382 X_PAGE_REF_CODE in VARCHAR2,
383 X_LOCATION_CODE in VARCHAR2,
384 X_SITE_PARAM1 in NUMBER,
385 X_SITE_PARAM2 in NUMBER,
386 X_SITE_PARAM3 in NUMBER,
387 X_SITE_PARAM4 in NUMBER,
388 X_SITE_PARAM5 in NUMBER,
389 X_DEFAULT_CONTENT_ITEM_ID in NUMBER,
390 X_DEFAULT_CITEM_VER_ID in NUMBER,
391 X_DEFAULT_STYLESHEET_ID in NUMBER,
392 X_DEFAULT_STYLESHEET_VER_ID in NUMBER,
393 X_STATUS_CODE in VARCHAR2,
394 X_AUTO_PUBLISH_FLAG in VARCHAR2,
395 X_DISPLAY_SELECTION_CODE in VARCHAR2,
396 X_SECURITY_GROUP_ID in NUMBER,
397 X_OBJECT_VERSION_NUMBER in NUMBER,
398 X_ATTRIBUTE_CATEGORY in VARCHAR2,
399 X_ATTRIBUTE1 in VARCHAR2,
400 X_ATTRIBUTE2 in VARCHAR2,
401 X_ATTRIBUTE3 in VARCHAR2,
402 X_ATTRIBUTE4 in VARCHAR2,
403 X_ATTRIBUTE5 in VARCHAR2,
404 X_ATTRIBUTE6 in VARCHAR2,
405 X_ATTRIBUTE7 in VARCHAR2,
406 X_ATTRIBUTE8 in VARCHAR2,
407 X_ATTRIBUTE9 in VARCHAR2,
408 X_ATTRIBUTE10 in VARCHAR2,
409 X_ATTRIBUTE11 in VARCHAR2,
410 X_ATTRIBUTE12 in VARCHAR2,
411 X_ATTRIBUTE13 in VARCHAR2,
412 X_ATTRIBUTE14 in VARCHAR2,
413 X_ATTRIBUTE15 in VARCHAR2,
414 X_PLACEMENT_NAME in VARCHAR2,
415 X_DESCRIPTION in VARCHAR2,
416 X_LAST_UPDATE_DATE in DATE,
417 X_LAST_UPDATED_BY in NUMBER,
418 X_LAST_UPDATE_LOGIN in NUMBER
419 ) is
420 begin
421 update AMS_WEB_PLACEMENTS_B set
422 PLACEMENT_CATEGORY = X_PLACEMENT_CATEGORY,
423 SITE_ID = X_SITE_ID,
424 SITE_REF_CODE = X_SITE_REF_CODE,
425 PAGE_ID = X_PAGE_ID,
426 PAGE_REF_CODE = X_PAGE_REF_CODE,
427 LOCATION_CODE = X_LOCATION_CODE,
428 SITE_PARAM1 = X_SITE_PARAM1,
429 SITE_PARAM2 = X_SITE_PARAM2,
430 SITE_PARAM3 = X_SITE_PARAM3,
431 SITE_PARAM4 = X_SITE_PARAM4,
432 SITE_PARAM5 = X_SITE_PARAM5,
433 DEFAULT_CONTENT_ITEM_ID = X_DEFAULT_CONTENT_ITEM_ID,
434 DEFAULT_CITEM_VER_ID = X_DEFAULT_CITEM_VER_ID,
435 DEFAULT_STYLESHEET_ID = X_DEFAULT_STYLESHEET_ID,
436 DEFAULT_STYLESHEET_VER_ID = X_DEFAULT_STYLESHEET_VER_ID,
437 STATUS_CODE = X_STATUS_CODE,
438 AUTO_PUBLISH_FLAG = X_AUTO_PUBLISH_FLAG,
439 DISPLAY_SELECTION_CODE = X_DISPLAY_SELECTION_CODE,
440 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
441 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
442 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
443 ATTRIBUTE1 = X_ATTRIBUTE1,
444 ATTRIBUTE2 = X_ATTRIBUTE2,
445 ATTRIBUTE3 = X_ATTRIBUTE3,
446 ATTRIBUTE4 = X_ATTRIBUTE4,
447 ATTRIBUTE5 = X_ATTRIBUTE5,
448 ATTRIBUTE6 = X_ATTRIBUTE6,
449 ATTRIBUTE7 = X_ATTRIBUTE7,
450 ATTRIBUTE8 = X_ATTRIBUTE8,
451 ATTRIBUTE9 = X_ATTRIBUTE9,
452 ATTRIBUTE10 = X_ATTRIBUTE10,
453 ATTRIBUTE11 = X_ATTRIBUTE11,
454 ATTRIBUTE12 = X_ATTRIBUTE12,
455 ATTRIBUTE13 = X_ATTRIBUTE13,
456 ATTRIBUTE14 = X_ATTRIBUTE14,
457 ATTRIBUTE15 = X_ATTRIBUTE15,
458 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
459 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
460 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
461 where PLACEMENT_ID = X_PLACEMENT_ID;
462
463 if (sql%notfound) then
464 raise no_data_found;
465 end if;
466
467 update AMS_WEB_PLACEMENTS_TL set
468 PLACEMENT_NAME = X_PLACEMENT_NAME,
469 DESCRIPTION = X_DESCRIPTION,
470 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
471 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
472 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
473 SOURCE_LANG = userenv('LANG')
474 where PLACEMENT_ID = X_PLACEMENT_ID
475 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480 end UPDATE_ROW;
481
482 procedure DELETE_ROW (
483 X_PLACEMENT_ID in NUMBER
484 ) is
485 begin
486 delete from AMS_WEB_PLACEMENTS_TL
487 where PLACEMENT_ID = X_PLACEMENT_ID;
488
489 if (sql%notfound) then
490 raise no_data_found;
491 end if;
492
493 delete from AMS_WEB_PLACEMENTS_B
494 where PLACEMENT_ID = X_PLACEMENT_ID;
495
496 if (sql%notfound) then
497 raise no_data_found;
498 end if;
499 end DELETE_ROW;
500
501 procedure ADD_LANGUAGE
502 is
503 begin
504 delete from AMS_WEB_PLACEMENTS_TL T
505 where not exists
506 (select NULL
507 from AMS_WEB_PLACEMENTS_B B
508 where B.PLACEMENT_ID = T.PLACEMENT_ID
509 );
510
511 update AMS_WEB_PLACEMENTS_TL T set (
515 B.PLACEMENT_NAME,
512 PLACEMENT_NAME,
513 DESCRIPTION
514 ) = (select
516 B.DESCRIPTION
517 from AMS_WEB_PLACEMENTS_TL B
518 where B.PLACEMENT_ID = T.PLACEMENT_ID
519 and B.LANGUAGE = T.SOURCE_LANG)
520 where (
521 T.PLACEMENT_ID,
522 T.LANGUAGE
523 ) in (select
524 SUBT.PLACEMENT_ID,
525 SUBT.LANGUAGE
526 from AMS_WEB_PLACEMENTS_TL SUBB, AMS_WEB_PLACEMENTS_TL SUBT
527 where SUBB.PLACEMENT_ID = SUBT.PLACEMENT_ID
528 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
529 and (SUBB.PLACEMENT_NAME <> SUBT.PLACEMENT_NAME
530 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
531 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
532 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
533 ));
534
535 insert into AMS_WEB_PLACEMENTS_TL (
536 PLACEMENT_ID,
537 PLACEMENT_NAME,
538 DESCRIPTION,
539 CREATED_BY,
540 CREATION_DATE,
541 LAST_UPDATED_BY,
542 LAST_UPDATE_DATE,
543 LAST_UPDATE_LOGIN,
544 SECURITY_GROUP_ID,
545 OBJECT_VERSION_NUMBER,
546 LANGUAGE,
547 SOURCE_LANG
548 ) select /*+ ORDERED */
549 B.PLACEMENT_ID,
550 B.PLACEMENT_NAME,
551 B.DESCRIPTION,
552 B.CREATED_BY,
553 B.CREATION_DATE,
554 B.LAST_UPDATED_BY,
555 B.LAST_UPDATE_DATE,
556 B.LAST_UPDATE_LOGIN,
557 B.SECURITY_GROUP_ID,
558 B.OBJECT_VERSION_NUMBER,
559 L.LANGUAGE_CODE,
560 B.SOURCE_LANG
561 from AMS_WEB_PLACEMENTS_TL B, FND_LANGUAGES L
562 where L.INSTALLED_FLAG in ('I', 'B')
563 and B.LANGUAGE = userenv('LANG')
564 and not exists
565 (select NULL
566 from AMS_WEB_PLACEMENTS_TL T
567 where T.PLACEMENT_ID = B.PLACEMENT_ID
568 and T.LANGUAGE = L.LANGUAGE_CODE);
569 end ADD_LANGUAGE;
570
571 end AMS_WEB_PLACEMENTS_PKG;