[Home] [Help]
PACKAGE BODY: APPS.AMS_VENUES_PKG
Source
1 package body AMS_VENUES_PKG as
2 /* $Header: amslvnub.pls 115.3 2002/11/16 00:41:58 dbiswas noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_VENUE_ID in NUMBER,
6 --X_SECURITY_GROUP_ID in NUMBER,
7 X_ATTRIBUTE10 in VARCHAR2,
8 X_ATTRIBUTE11 in VARCHAR2,
9 X_ATTRIBUTE12 in VARCHAR2,
10 X_ATTRIBUTE13 in VARCHAR2,
11 X_ATTRIBUTE14 in VARCHAR2,
12 X_ATTRIBUTE15 in VARCHAR2,
13 X_OBJECT_VERSION_NUMBER in NUMBER,
14 X_VENUE_TYPE_CODE in VARCHAR2,
15 X_DIRECT_PHONE_FLAG in VARCHAR2,
16 X_INTERNAL_FLAG in VARCHAR2,
17 X_ENABLED_FLAG in VARCHAR2,
18 X_RATING_CODE in VARCHAR2,
19 X_CAPACITY in NUMBER,
20 X_AREA_SIZE in NUMBER,
21 X_AREA_SIZE_UOM_CODE in VARCHAR2,
22 X_CEILING_HEIGHT in NUMBER,
23 X_CEILING_HEIGHT_UOM_CODE in VARCHAR2,
24 X_USAGE_COST in NUMBER,
25 X_USAGE_COST_CURRENCY_CODE in VARCHAR2,
26 X_USAGE_COST_UOM_CODE in VARCHAR2,
27 X_PARENT_VENUE_ID in NUMBER,
28 X_LOCATION_ID in NUMBER,
29 X_DIRECTIONS in VARCHAR2,
30 X_VENUE_CODE in VARCHAR2,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2,
32 X_ATTRIBUTE1 in VARCHAR2,
33 X_ATTRIBUTE2 in VARCHAR2,
34 X_ATTRIBUTE3 in VARCHAR2,
35 X_ATTRIBUTE4 in VARCHAR2,
36 X_ATTRIBUTE5 in VARCHAR2,
37 X_ATTRIBUTE6 in VARCHAR2,
38 X_ATTRIBUTE7 in VARCHAR2,
39 X_ATTRIBUTE8 in VARCHAR2,
40 X_ATTRIBUTE9 in VARCHAR2,
41 X_VENUE_NAME in VARCHAR2,
42 X_DESCRIPTION in VARCHAR2,
43 X_CREATION_DATE in DATE,
44 X_CREATED_BY in NUMBER,
45 X_LAST_UPDATE_DATE in DATE,
46 X_LAST_UPDATED_BY in NUMBER,
47 X_LAST_UPDATE_LOGIN in NUMBER
48 ) is
49 cursor C is select ROWID from AMS_VENUES_B
50 where VENUE_ID = X_VENUE_ID
51 ;
52 begin
53 insert into AMS_VENUES_B (
54 --SECURITY_GROUP_ID,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15,
61 VENUE_ID,
62 OBJECT_VERSION_NUMBER,
63 VENUE_TYPE_CODE,
64 DIRECT_PHONE_FLAG,
65 INTERNAL_FLAG,
66 ENABLED_FLAG,
67 RATING_CODE,
68 CAPACITY,
69 AREA_SIZE,
70 AREA_SIZE_UOM_CODE,
71 CEILING_HEIGHT,
72 CEILING_HEIGHT_UOM_CODE,
73 USAGE_COST,
74 USAGE_COST_CURRENCY_CODE,
75 USAGE_COST_UOM_CODE,
76 PARENT_VENUE_ID,
77 LOCATION_ID,
78 DIRECTIONS,
79 VENUE_CODE,
80 ATTRIBUTE_CATEGORY,
81 ATTRIBUTE1,
82 ATTRIBUTE2,
83 ATTRIBUTE3,
84 ATTRIBUTE4,
85 ATTRIBUTE5,
86 ATTRIBUTE6,
87 ATTRIBUTE7,
88 ATTRIBUTE8,
89 ATTRIBUTE9,
90 CREATION_DATE,
91 CREATED_BY,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 LAST_UPDATE_LOGIN
95 ) values (
96 --X_SECURITY_GROUP_ID,
97 X_ATTRIBUTE10,
98 X_ATTRIBUTE11,
99 X_ATTRIBUTE12,
100 X_ATTRIBUTE13,
101 X_ATTRIBUTE14,
102 X_ATTRIBUTE15,
103 X_VENUE_ID,
104 X_OBJECT_VERSION_NUMBER,
105 X_VENUE_TYPE_CODE,
106 X_DIRECT_PHONE_FLAG,
107 X_INTERNAL_FLAG,
108 X_ENABLED_FLAG,
109 X_RATING_CODE,
110 X_CAPACITY,
111 X_AREA_SIZE,
112 X_AREA_SIZE_UOM_CODE,
113 X_CEILING_HEIGHT,
114 X_CEILING_HEIGHT_UOM_CODE,
115 X_USAGE_COST,
116 X_USAGE_COST_CURRENCY_CODE,
117 X_USAGE_COST_UOM_CODE,
118 X_PARENT_VENUE_ID,
119 X_LOCATION_ID,
120 X_DIRECTIONS,
121 X_VENUE_CODE,
122 X_ATTRIBUTE_CATEGORY,
123 X_ATTRIBUTE1,
124 X_ATTRIBUTE2,
125 X_ATTRIBUTE3,
126 X_ATTRIBUTE4,
127 X_ATTRIBUTE5,
128 X_ATTRIBUTE6,
129 X_ATTRIBUTE7,
130 X_ATTRIBUTE8,
131 X_ATTRIBUTE9,
132 X_CREATION_DATE,
133 X_CREATED_BY,
134 X_LAST_UPDATE_DATE,
135 X_LAST_UPDATED_BY,
136 X_LAST_UPDATE_LOGIN
137 );
138
139 insert into AMS_VENUES_TL (
140 --SECURITY_GROUP_ID,
141 VENUE_ID,
142 LAST_UPDATE_DATE,
143 LAST_UPDATED_BY,
144 CREATION_DATE,
145 CREATED_BY,
146 LAST_UPDATE_LOGIN,
147 VENUE_NAME,
148 DESCRIPTION,
149 LANGUAGE,
150 SOURCE_LANG
151 ) select
152 --X_SECURITY_GROUP_ID,
153 X_VENUE_ID,
154 X_LAST_UPDATE_DATE,
155 X_LAST_UPDATED_BY,
156 X_CREATION_DATE,
157 X_CREATED_BY,
158 X_LAST_UPDATE_LOGIN,
159 X_VENUE_NAME,
160 X_DESCRIPTION,
161 L.LANGUAGE_CODE,
162 userenv('LANG')
163 from FND_LANGUAGES L
164 where L.INSTALLED_FLAG in ('I', 'B')
165 and not exists
166 (select NULL
167 from AMS_VENUES_TL T
168 where T.VENUE_ID = X_VENUE_ID
169 and T.LANGUAGE = L.LANGUAGE_CODE);
170
171 open c;
172 fetch c into X_ROWID;
173 if (c%notfound) then
174 close c;
175 raise no_data_found;
176 end if;
177 close c;
178
179 end INSERT_ROW;
180
181 procedure LOCK_ROW (
182 X_VENUE_ID in NUMBER,
183 --X_SECURITY_GROUP_ID in NUMBER,
184 X_ATTRIBUTE10 in VARCHAR2,
185 X_ATTRIBUTE11 in VARCHAR2,
186 X_ATTRIBUTE12 in VARCHAR2,
187 X_ATTRIBUTE13 in VARCHAR2,
188 X_ATTRIBUTE14 in VARCHAR2,
189 X_ATTRIBUTE15 in VARCHAR2,
190 X_OBJECT_VERSION_NUMBER in NUMBER,
191 X_VENUE_TYPE_CODE in VARCHAR2,
192 X_DIRECT_PHONE_FLAG in VARCHAR2,
193 X_INTERNAL_FLAG in VARCHAR2,
194 X_ENABLED_FLAG in VARCHAR2,
195 X_RATING_CODE in VARCHAR2,
196 X_CAPACITY in NUMBER,
197 X_AREA_SIZE in NUMBER,
198 X_AREA_SIZE_UOM_CODE in VARCHAR2,
199 X_CEILING_HEIGHT in NUMBER,
200 X_CEILING_HEIGHT_UOM_CODE in VARCHAR2,
201 X_USAGE_COST in NUMBER,
202 X_USAGE_COST_CURRENCY_CODE in VARCHAR2,
203 X_USAGE_COST_UOM_CODE in VARCHAR2,
204 X_PARENT_VENUE_ID in NUMBER,
205 X_LOCATION_ID in NUMBER,
206 X_DIRECTIONS in VARCHAR2,
207 X_VENUE_CODE in VARCHAR2,
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_VENUE_NAME in VARCHAR2,
219 X_DESCRIPTION in VARCHAR2
220 ) is
221 cursor c is select
222 --SECURITY_GROUP_ID,
223 ATTRIBUTE10,
224 ATTRIBUTE11,
225 ATTRIBUTE12,
226 ATTRIBUTE13,
227 ATTRIBUTE14,
228 ATTRIBUTE15,
229 OBJECT_VERSION_NUMBER,
230 VENUE_TYPE_CODE,
231 DIRECT_PHONE_FLAG,
232 INTERNAL_FLAG,
233 ENABLED_FLAG,
234 RATING_CODE,
235 CAPACITY,
236 AREA_SIZE,
237 AREA_SIZE_UOM_CODE,
238 CEILING_HEIGHT,
239 CEILING_HEIGHT_UOM_CODE,
240 USAGE_COST,
241 USAGE_COST_CURRENCY_CODE,
242 USAGE_COST_UOM_CODE,
243 PARENT_VENUE_ID,
244 LOCATION_ID,
245 DIRECTIONS,
246 VENUE_CODE,
247 ATTRIBUTE_CATEGORY,
248 ATTRIBUTE1,
249 ATTRIBUTE2,
250 ATTRIBUTE3,
251 ATTRIBUTE4,
252 ATTRIBUTE5,
253 ATTRIBUTE6,
254 ATTRIBUTE7,
255 ATTRIBUTE8,
256 ATTRIBUTE9
257 from AMS_VENUES_B
258 where VENUE_ID = X_VENUE_ID
259 for update of VENUE_ID nowait;
260 recinfo c%rowtype;
261
262 cursor c1 is select
263 VENUE_NAME,
264 DESCRIPTION,
265 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
266 from AMS_VENUES_TL
267 where VENUE_ID = X_VENUE_ID
268 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
269 for update of VENUE_ID nowait;
270 begin
271 open c;
272 fetch c into recinfo;
273 if (c%notfound) then
274 close c;
275 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
276 app_exception.raise_exception;
277 end if;
278 close c;
279 if ( ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
280 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
281 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
282 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
283 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
284 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
285 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
286 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
287 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
288 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
289 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
290 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
291 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
292 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
293 AND (recinfo.VENUE_TYPE_CODE = X_VENUE_TYPE_CODE)
294 AND (recinfo.DIRECT_PHONE_FLAG = X_DIRECT_PHONE_FLAG)
295 AND (recinfo.INTERNAL_FLAG = X_INTERNAL_FLAG)
296 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
297 AND ((recinfo.RATING_CODE = X_RATING_CODE)
298 OR ((recinfo.RATING_CODE is null) AND (X_RATING_CODE is null)))
299 AND ((recinfo.CAPACITY = X_CAPACITY)
300 OR ((recinfo.CAPACITY is null) AND (X_CAPACITY is null)))
301 AND ((recinfo.AREA_SIZE = X_AREA_SIZE)
302 OR ((recinfo.AREA_SIZE is null) AND (X_AREA_SIZE is null)))
303 AND ((recinfo.AREA_SIZE_UOM_CODE = X_AREA_SIZE_UOM_CODE)
304 OR ((recinfo.AREA_SIZE_UOM_CODE is null) AND (X_AREA_SIZE_UOM_CODE is null)))
305 AND ((recinfo.CEILING_HEIGHT = X_CEILING_HEIGHT)
306 OR ((recinfo.CEILING_HEIGHT is null) AND (X_CEILING_HEIGHT is null)))
307 AND ((recinfo.CEILING_HEIGHT_UOM_CODE = X_CEILING_HEIGHT_UOM_CODE)
308 OR ((recinfo.CEILING_HEIGHT_UOM_CODE is null) AND (X_CEILING_HEIGHT_UOM_CODE is null)))
309 AND ((recinfo.USAGE_COST = X_USAGE_COST)
310 OR ((recinfo.USAGE_COST is null) AND (X_USAGE_COST is null)))
311 AND ((recinfo.USAGE_COST_CURRENCY_CODE = X_USAGE_COST_CURRENCY_CODE)
312 OR ((recinfo.USAGE_COST_CURRENCY_CODE is null) AND (X_USAGE_COST_CURRENCY_CODE is null)))
313 AND ((recinfo.USAGE_COST_UOM_CODE = X_USAGE_COST_UOM_CODE)
314 OR ((recinfo.USAGE_COST_UOM_CODE is null) AND (X_USAGE_COST_UOM_CODE is null)))
315 AND ((recinfo.PARENT_VENUE_ID = X_PARENT_VENUE_ID)
316 OR ((recinfo.PARENT_VENUE_ID is null) AND (X_PARENT_VENUE_ID is null)))
317 AND ((recinfo.LOCATION_ID = X_LOCATION_ID)
318 OR ((recinfo.LOCATION_ID is null) AND (X_LOCATION_ID is null)))
319 AND ((recinfo.DIRECTIONS = X_DIRECTIONS)
320 OR ((recinfo.DIRECTIONS is null) AND (X_DIRECTIONS is null)))
321 AND ((recinfo.VENUE_CODE = X_VENUE_CODE)
322 OR ((recinfo.VENUE_CODE is null) AND (X_VENUE_CODE is null)))
323 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
324 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
325 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
326 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
327 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
328 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
329 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
330 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
331 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
332 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
333 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
334 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
335 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
336 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
337 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
338 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
339 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
340 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
341 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
342 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
343 ) then
344 null;
345 else
346 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
347 app_exception.raise_exception;
348 end if;
349
350 for tlinfo in c1 loop
351 if (tlinfo.BASELANG = 'Y') then
352 if ( (tlinfo.VENUE_NAME = X_VENUE_NAME)
353 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
354 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
355 ) then
356 null;
357 else
358 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
359 app_exception.raise_exception;
360 end if;
361 end if;
362 end loop;
363 return;
364 end LOCK_ROW;
365
366 procedure UPDATE_ROW (
367 X_VENUE_ID in NUMBER,
368 --X_SECURITY_GROUP_ID in NUMBER,
369 X_ATTRIBUTE10 in VARCHAR2,
370 X_ATTRIBUTE11 in VARCHAR2,
371 X_ATTRIBUTE12 in VARCHAR2,
372 X_ATTRIBUTE13 in VARCHAR2,
373 X_ATTRIBUTE14 in VARCHAR2,
374 X_ATTRIBUTE15 in VARCHAR2,
375 X_OBJECT_VERSION_NUMBER in NUMBER,
376 X_VENUE_TYPE_CODE in VARCHAR2,
377 X_DIRECT_PHONE_FLAG in VARCHAR2,
378 X_INTERNAL_FLAG in VARCHAR2,
379 X_ENABLED_FLAG in VARCHAR2,
380 X_RATING_CODE in VARCHAR2,
381 X_CAPACITY in NUMBER,
382 X_AREA_SIZE in NUMBER,
383 X_AREA_SIZE_UOM_CODE in VARCHAR2,
384 X_CEILING_HEIGHT in NUMBER,
385 X_CEILING_HEIGHT_UOM_CODE in VARCHAR2,
386 X_USAGE_COST in NUMBER,
387 X_USAGE_COST_CURRENCY_CODE in VARCHAR2,
388 X_USAGE_COST_UOM_CODE in VARCHAR2,
389 X_PARENT_VENUE_ID in NUMBER,
390 X_LOCATION_ID in NUMBER,
391 X_DIRECTIONS in VARCHAR2,
392 X_VENUE_CODE in VARCHAR2,
393 X_ATTRIBUTE_CATEGORY in VARCHAR2,
394 X_ATTRIBUTE1 in VARCHAR2,
395 X_ATTRIBUTE2 in VARCHAR2,
396 X_ATTRIBUTE3 in VARCHAR2,
397 X_ATTRIBUTE4 in VARCHAR2,
398 X_ATTRIBUTE5 in VARCHAR2,
399 X_ATTRIBUTE6 in VARCHAR2,
400 X_ATTRIBUTE7 in VARCHAR2,
401 X_ATTRIBUTE8 in VARCHAR2,
402 X_ATTRIBUTE9 in VARCHAR2,
403 X_VENUE_NAME in VARCHAR2,
404 X_DESCRIPTION in VARCHAR2,
405 X_LAST_UPDATE_DATE in DATE,
406 X_LAST_UPDATED_BY in NUMBER,
407 X_LAST_UPDATE_LOGIN in NUMBER
408 ) is
409 begin
410 update AMS_VENUES_B set
411 --SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
412 ATTRIBUTE10 = X_ATTRIBUTE10,
413 ATTRIBUTE11 = X_ATTRIBUTE11,
414 ATTRIBUTE12 = X_ATTRIBUTE12,
415 ATTRIBUTE13 = X_ATTRIBUTE13,
416 ATTRIBUTE14 = X_ATTRIBUTE14,
417 ATTRIBUTE15 = X_ATTRIBUTE15,
418 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
419 VENUE_TYPE_CODE = X_VENUE_TYPE_CODE,
420 DIRECT_PHONE_FLAG = X_DIRECT_PHONE_FLAG,
421 INTERNAL_FLAG = X_INTERNAL_FLAG,
422 ENABLED_FLAG = X_ENABLED_FLAG,
423 RATING_CODE = X_RATING_CODE,
424 CAPACITY = X_CAPACITY,
425 AREA_SIZE = X_AREA_SIZE,
426 AREA_SIZE_UOM_CODE = X_AREA_SIZE_UOM_CODE,
427 CEILING_HEIGHT = X_CEILING_HEIGHT,
428 CEILING_HEIGHT_UOM_CODE = X_CEILING_HEIGHT_UOM_CODE,
429 USAGE_COST = X_USAGE_COST,
430 USAGE_COST_CURRENCY_CODE = X_USAGE_COST_CURRENCY_CODE,
431 USAGE_COST_UOM_CODE = X_USAGE_COST_UOM_CODE,
432 PARENT_VENUE_ID = X_PARENT_VENUE_ID,
433 LOCATION_ID = X_LOCATION_ID,
434 DIRECTIONS = X_DIRECTIONS,
435 VENUE_CODE = X_VENUE_CODE,
436 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
437 ATTRIBUTE1 = X_ATTRIBUTE1,
438 ATTRIBUTE2 = X_ATTRIBUTE2,
439 ATTRIBUTE3 = X_ATTRIBUTE3,
440 ATTRIBUTE4 = X_ATTRIBUTE4,
441 ATTRIBUTE5 = X_ATTRIBUTE5,
442 ATTRIBUTE6 = X_ATTRIBUTE6,
443 ATTRIBUTE7 = X_ATTRIBUTE7,
444 ATTRIBUTE8 = X_ATTRIBUTE8,
445 ATTRIBUTE9 = X_ATTRIBUTE9,
446 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
447 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
448 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
449 where VENUE_ID = X_VENUE_ID;
450
451 if (sql%notfound) then
452 raise no_data_found;
453 end if;
454
455 update AMS_VENUES_TL set
459 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
456 VENUE_NAME = X_VENUE_NAME,
457 DESCRIPTION = X_DESCRIPTION,
458 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
460 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
461 SOURCE_LANG = userenv('LANG')
462 where VENUE_ID = X_VENUE_ID
463 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
464
465 if (sql%notfound) then
466 raise no_data_found;
467 end if;
468 end UPDATE_ROW;
469
470 procedure DELETE_ROW (
471 X_VENUE_ID in NUMBER
472 ) is
473 begin
474 delete from AMS_VENUES_TL
475 where VENUE_ID = X_VENUE_ID;
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480
481 delete from AMS_VENUES_B
482 where VENUE_ID = X_VENUE_ID;
483
484 if (sql%notfound) then
485 raise no_data_found;
486 end if;
487 end DELETE_ROW;
488
489 procedure ADD_LANGUAGE
490 is
491 begin
492 delete from AMS_VENUES_TL T
493 where not exists
494 (select NULL
495 from AMS_VENUES_B B
496 where B.VENUE_ID = T.VENUE_ID
497 );
498
499 update AMS_VENUES_TL T set (
500 VENUE_NAME,
501 DESCRIPTION
502 ) = (select
503 B.VENUE_NAME,
504 B.DESCRIPTION
505 from AMS_VENUES_TL B
506 where B.VENUE_ID = T.VENUE_ID
507 and B.LANGUAGE = T.SOURCE_LANG)
508 where (
509 T.VENUE_ID,
510 T.LANGUAGE
511 ) in (select
512 SUBT.VENUE_ID,
513 SUBT.LANGUAGE
514 from AMS_VENUES_TL SUBB, AMS_VENUES_TL SUBT
515 where SUBB.VENUE_ID = SUBT.VENUE_ID
516 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
517 and (SUBB.VENUE_NAME <> SUBT.VENUE_NAME
518 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
519 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
520 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
521 ));
522
523 insert into AMS_VENUES_TL (
524 --SECURITY_GROUP_ID,
525 VENUE_ID,
526 LAST_UPDATE_DATE,
527 LAST_UPDATED_BY,
528 CREATION_DATE,
529 CREATED_BY,
530 LAST_UPDATE_LOGIN,
531 VENUE_NAME,
532 DESCRIPTION,
533 LANGUAGE,
534 SOURCE_LANG
535 ) select
536 --B.SECURITY_GROUP_ID,
537 B.VENUE_ID,
538 B.LAST_UPDATE_DATE,
539 B.LAST_UPDATED_BY,
540 B.CREATION_DATE,
541 B.CREATED_BY,
542 B.LAST_UPDATE_LOGIN,
543 B.VENUE_NAME,
544 B.DESCRIPTION,
545 L.LANGUAGE_CODE,
546 B.SOURCE_LANG
547 from AMS_VENUES_TL B, FND_LANGUAGES L
548 where L.INSTALLED_FLAG in ('I', 'B')
549 and B.LANGUAGE = userenv('LANG')
550 and not exists
551 (select NULL
552 from AMS_VENUES_TL T
553 where T.VENUE_ID = B.VENUE_ID
554 and T.LANGUAGE = L.LANGUAGE_CODE);
555 end ADD_LANGUAGE;
556
557 procedure TRANSLATE_ROW(
558 X_VENUE_ID in NUMBER,
559 X_NAME in VARCHAR2,
560 X_DESCRIPTION in VARCHAR2,
561 X_OWNER in VARCHAR2
562 ) IS
563 begin
564 update AMS_VENUES_TL set
565 venue_name = nvl(x_name, venue_name),
566 description = nvl(x_description, description),
567 source_lang = userenv('LANG'),
568 last_update_date = sysdate,
569 last_updated_by = decode(x_owner, 'SEED', 1, 0),
570 last_update_login = 0
571 where VENUE_ID = X_VENUE_ID
572 and userenv('LANG') in (language, source_lang);
573 end TRANSLATE_ROW;
574
575 procedure LOAD_ROW (
576 X_VENUE_ID in NUMBER,
577 --X_SECURITY_GROUP_ID in NUMBER,
578 X_ATTRIBUTE10 in VARCHAR2,
579 X_ATTRIBUTE11 in VARCHAR2,
580 X_ATTRIBUTE12 in VARCHAR2,
581 X_ATTRIBUTE13 in VARCHAR2,
582 X_ATTRIBUTE14 in VARCHAR2,
583 X_ATTRIBUTE15 in VARCHAR2,
584 X_OBJECT_VERSION_NUMBER in NUMBER,
585 X_VENUE_TYPE_CODE in VARCHAR2,
586 X_DIRECT_PHONE_FLAG in VARCHAR2,
587 X_INTERNAL_FLAG in VARCHAR2,
588 X_ENABLED_FLAG in VARCHAR2,
589 X_RATING_CODE in VARCHAR2,
590 X_CAPACITY in NUMBER,
591 X_AREA_SIZE in NUMBER,
592 X_AREA_SIZE_UOM_CODE in VARCHAR2,
593 X_CEILING_HEIGHT in NUMBER,
594 X_CEILING_HEIGHT_UOM_CODE in VARCHAR2,
595 X_USAGE_COST in NUMBER,
596 X_USAGE_COST_CURRENCY_CODE in VARCHAR2,
597 X_USAGE_COST_UOM_CODE in VARCHAR2,
598 X_PARENT_VENUE_ID in NUMBER,
599 X_LOCATION_ID in NUMBER,
600 X_DIRECTIONS in VARCHAR2,
601 X_VENUE_CODE in VARCHAR2,
602 X_ATTRIBUTE_CATEGORY in VARCHAR2,
603 X_ATTRIBUTE1 in VARCHAR2,
604 X_ATTRIBUTE2 in VARCHAR2,
605 X_ATTRIBUTE3 in VARCHAR2,
606 X_ATTRIBUTE4 in VARCHAR2,
607 X_ATTRIBUTE5 in VARCHAR2,
608 X_ATTRIBUTE6 in VARCHAR2,
609 X_ATTRIBUTE7 in VARCHAR2,
610 X_ATTRIBUTE8 in VARCHAR2,
611 X_ATTRIBUTE9 in VARCHAR2,
612 X_VENUE_NAME in VARCHAR2,
613 X_DESCRIPTION in VARCHAR2,
614 X_OWNER in VARCHAR2
615 ) IS
616 l_user_id number := 0;
617 l_obj_verno number;
618 l_venue_id number;
619 l_dummy_char varchar2(1);
620 l_row_id varchar2(100);
621
622 cursor c_obj_verno (id_in in NUMBER) is
623 select object_version_number
624 from AMS_VENUES_B
625 where VENUE_ID = id_in;
626
627 cursor c_chk_vnu_exists (id_in in NUMBER) is
628 select 'x'
629 from AMS_VENUES_B
630 where VENUE_ID = id_in;
631
632 cursor c_get_vnu_id is
633 select AMS_VENUES_B_S.nextval
634 from dual;
635 BEGIN
636 if X_OWNER = 'SEED' then
637 l_user_id := 1;
638 end if;
639 open c_chk_vnu_exists(X_VENUE_ID);
640 fetch c_chk_vnu_exists into l_dummy_char;
641 if c_chk_vnu_exists%notfound
642 then
643 close c_chk_vnu_exists;
644 if X_VENUE_ID is null
645 then
646 open c_get_vnu_id;
647 fetch c_get_vnu_id into l_venue_id;
648 close c_get_vnu_id;
649 else
650 l_venue_id := X_VENUE_ID;
651 end if;
652 l_obj_verno := 1;
653 AMS_VENUES_PKG.INSERT_ROW (
654 X_ROWID => l_row_id,
655 X_VENUE_ID => l_venue_id,
656 --X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
657 X_ATTRIBUTE10 => X_ATTRIBUTE10,
658 X_ATTRIBUTE11 => X_ATTRIBUTE11,
659 X_ATTRIBUTE12 => X_ATTRIBUTE12,
660 X_ATTRIBUTE13 => X_ATTRIBUTE13,
661 X_ATTRIBUTE14 => X_ATTRIBUTE14,
662 X_ATTRIBUTE15 => X_ATTRIBUTE15,
663 X_OBJECT_VERSION_NUMBER => l_obj_verno,
664 X_VENUE_TYPE_CODE => X_VENUE_TYPE_CODE,
665 X_DIRECT_PHONE_FLAG => X_DIRECT_PHONE_FLAG,
666 X_INTERNAL_FLAG => X_INTERNAL_FLAG,
667 X_ENABLED_FLAG => X_ENABLED_FLAG,
668 X_RATING_CODE => X_RATING_CODE,
669 X_CAPACITY => X_CAPACITY,
670 X_AREA_SIZE => X_AREA_SIZE,
671 X_AREA_SIZE_UOM_CODE => X_AREA_SIZE_UOM_CODE,
672 X_CEILING_HEIGHT => X_CEILING_HEIGHT,
673 X_CEILING_HEIGHT_UOM_CODE => X_CEILING_HEIGHT_UOM_CODE,
674 X_USAGE_COST => X_USAGE_COST,
675 X_USAGE_COST_CURRENCY_CODE => X_USAGE_COST_CURRENCY_CODE,
676 X_USAGE_COST_UOM_CODE => X_USAGE_COST_UOM_CODE,
677 X_PARENT_VENUE_ID => X_PARENT_VENUE_ID,
678 X_LOCATION_ID => X_LOCATION_ID,
679 X_DIRECTIONS => X_DIRECTIONS,
680 X_VENUE_CODE => X_VENUE_CODE,
681 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
682 X_ATTRIBUTE1 => X_ATTRIBUTE1,
683 X_ATTRIBUTE2 => X_ATTRIBUTE2,
684 X_ATTRIBUTE3 => X_ATTRIBUTE3,
685 X_ATTRIBUTE4 => X_ATTRIBUTE4,
686 X_ATTRIBUTE5 => X_ATTRIBUTE5,
687 X_ATTRIBUTE6 => X_ATTRIBUTE6,
688 X_ATTRIBUTE7 => X_ATTRIBUTE7,
689 X_ATTRIBUTE8 => X_ATTRIBUTE8,
690 X_ATTRIBUTE9 => X_ATTRIBUTE9,
691 X_VENUE_NAME => X_VENUE_NAME,
692 X_DESCRIPTION => X_DESCRIPTION,
693 X_CREATION_DATE => SYSDATE,
694 X_CREATED_BY => l_user_id,
695 X_LAST_UPDATE_DATE => SYSDATE,
696 X_LAST_UPDATED_BY => l_user_id,
697 X_LAST_UPDATE_LOGIN => 0
698 );
699 else
700 close c_chk_vnu_exists;
701 open c_obj_verno(X_VENUE_ID);
702 fetch c_obj_verno into l_obj_verno;
703 close c_obj_verno;
704 -- assigning value for l_user_status_id
705 l_venue_id := X_VENUE_ID;
706 AMS_VENUES_PKG.update_row (
707 X_VENUE_ID => l_venue_id,
708 --X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
709 X_ATTRIBUTE10 => X_ATTRIBUTE10,
710 X_ATTRIBUTE11 => X_ATTRIBUTE11,
711 X_ATTRIBUTE12 => X_ATTRIBUTE12,
712 X_ATTRIBUTE13 => X_ATTRIBUTE13,
713 X_ATTRIBUTE14 => X_ATTRIBUTE14,
714 X_ATTRIBUTE15 => X_ATTRIBUTE15,
715 X_OBJECT_VERSION_NUMBER => l_obj_verno+1,
716 X_VENUE_TYPE_CODE => X_VENUE_TYPE_CODE,
717 X_DIRECT_PHONE_FLAG => X_DIRECT_PHONE_FLAG,
718 X_INTERNAL_FLAG => X_INTERNAL_FLAG,
719 X_ENABLED_FLAG => X_ENABLED_FLAG,
720 X_RATING_CODE => X_RATING_CODE,
721 X_CAPACITY => X_CAPACITY,
722 X_AREA_SIZE => X_AREA_SIZE,
723 X_AREA_SIZE_UOM_CODE => X_AREA_SIZE_UOM_CODE,
724 X_CEILING_HEIGHT => X_CEILING_HEIGHT,
725 X_CEILING_HEIGHT_UOM_CODE => X_CEILING_HEIGHT_UOM_CODE,
726 X_USAGE_COST => X_USAGE_COST,
727 X_USAGE_COST_CURRENCY_CODE => X_USAGE_COST_CURRENCY_CODE,
728 X_USAGE_COST_UOM_CODE => X_USAGE_COST_UOM_CODE,
729 X_PARENT_VENUE_ID => X_PARENT_VENUE_ID,
730 X_LOCATION_ID => X_LOCATION_ID,
731 X_DIRECTIONS => X_DIRECTIONS,
732 X_VENUE_CODE => X_VENUE_CODE,
733 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
734 X_ATTRIBUTE1 => X_ATTRIBUTE1,
735 X_ATTRIBUTE2 => X_ATTRIBUTE2,
736 X_ATTRIBUTE3 => X_ATTRIBUTE3,
737 X_ATTRIBUTE4 => X_ATTRIBUTE4,
738 X_ATTRIBUTE5 => X_ATTRIBUTE5,
739 X_ATTRIBUTE6 => X_ATTRIBUTE6,
740 X_ATTRIBUTE7 => X_ATTRIBUTE7,
741 X_ATTRIBUTE8 => X_ATTRIBUTE8,
742 X_ATTRIBUTE9 => X_ATTRIBUTE9,
743 X_VENUE_NAME => X_VENUE_NAME,
744 X_DESCRIPTION => X_DESCRIPTION,
745 X_LAST_UPDATE_DATE => SYSDATE,
746 X_LAST_UPDATED_BY => l_user_id,
747 X_LAST_UPDATE_LOGIN => 0
748 );
749 END IF;
750 END LOAD_ROW;
751 end AMS_VENUES_PKG;