[Home] [Help]
PACKAGE BODY: APPS.AMS_VENUE_PVT
Source
1 PACKAGE BODY AMS_Venue_PVT AS
2 /* $Header: amsvvnub.pls 120.5 2006/04/05 05:39:22 batoleti ship $ */
3
4 ------------------------------------------------------------------------------------------
5 -- PACKAGE
6 -- AMS_Venue_PVT
7 --
8 -- PROCEDURES
9 -- AMS_VENUES_VL:
10 -- Check_Venue_Req_Items
11 -- Check_Venue_UK_Items
12 -- Check_Venue_FK_Items
13 -- Check_Venue_Lookup_Items
14 -- Check_Venue_Flag_Items
15 --
16
17 -- NOTES
18 --
19 --
20 -- HISTORY
21 -- 10-Dec-1999 rvaka Created.
22 -- 05-Feb-2001 mukumar bug 162651 reports while updating a venu getting
23 -- following error
24 -- The Venue already exists -- please specify a different name
25 -- 20-Sep-2001 dcastlem Fixing HZ_locations country_code in update
26 -- 19-APR-2002 dcastlem TCA integration
27 -- 07-JUN-2002 dcastlem bug 2386818 - removed access creation code (a more thorough cleanup
28 -- would also involve fixing the header file, but since this is a bug
29 -- fix I do not want to risk destabilizing the code)
30 -- 05-Aug-2002 gmadana Bug # 2497053.
31 -- Added logic for checking -ve values.
32 -- 04-Sep-2002 musman Bug # 2540837,Commenting out call to create hz_locations and hz_party_Sites in
33 -- the update_venue,since in the screen we have user cannot update these columns
34 -- 09 May 2003 dbiswas Added checks for length of the ceiling height and capacity
35 -- 04-Aug-2004 dhsingh Added org classification code, party site use creation
36 -- 23-Feb-2005 vmodur Fix for Bug 4083293
37 --12-Aug-2005 sikalyan TCA V2API Uptake
38 --13-Sep-2005 sikalyan TCA Mandates for created_by_module 'AMS_EVENT'
39 ------------------------------------------------------------------------------------------
40
41 --
42 -- Global CONSTANTS
43 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Venue_PVT';
44
45 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
46 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
47 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
48
49 PROCEDURE Create_Venue_Base (
50 p_api_version IN NUMBER,
51 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
52 p_commit IN VARCHAR2 := FND_API.g_false,
53 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
54
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58
59 p_venue_rec IN Venue_Rec_Type,
60 p_object_type IN VARCHAR2,
61 x_venue_id OUT NOCOPY NUMBER
62 );
63
64 PROCEDURE Update_Venue_Base (
65 p_api_version IN NUMBER,
66 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
67 p_commit IN VARCHAR2 := FND_API.g_false,
68 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
69
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73
74 p_venue_rec IN Venue_Rec_Type,
75 p_object_type IN VARCHAR2
76 );
77
78 -- Check_Venue_Req_Items
79 PROCEDURE Check_Venue_Req_Items (
80 p_venue_rec IN Venue_Rec_Type,
81 p_object_type IN VARCHAR2,
82 x_return_status OUT NOCOPY VARCHAR2
83 );
84
85 -- Check_Venue_UK_Items
86 PROCEDURE Check_Venue_UK_Items (
87 p_venue_rec IN Venue_Rec_Type,
88 p_object_type IN VARCHAR2,
89 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
90 x_return_status OUT NOCOPY VARCHAR2
91 );
92
93 -- Check_Venue_FK_Items
94 PROCEDURE Check_Venue_FK_Items (
95 p_venue_rec IN Venue_Rec_Type,
96 p_object_type IN VARCHAR2,
97 x_return_status OUT NOCOPY VARCHAR2
98 );
99
100 -- Check_Venue_Lookup_Items
101 PROCEDURE Check_Venue_Lookup_Items (
102 p_venue_rec IN Venue_Rec_Type,
103 x_return_status OUT NOCOPY VARCHAR2
104 );
105
106 -- Check_Venue_Flag_Items
107 PROCEDURE Check_Venue_Flag_Items (
108 p_venue_rec IN Venue_Rec_Type,
109 x_return_status OUT NOCOPY VARCHAR2
110 );
111
112 -------------------------------------
113 ----- VENUE -----
114 -------------------------------------
115
116 --------------------------------------------------------------------
117 -- PROCEDURE
118 -- Create_Venue
119 --
120 --------------------------------------------------------------------
121 PROCEDURE Create_Venue (
122 p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
124 p_commit IN VARCHAR2 := FND_API.g_false,
125 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
126
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130
131 p_venue_rec IN Venue_Rec_Type,
132 x_venue_id OUT NOCOPY NUMBER
133 )
134 IS
135
136 L_API_VERSION CONSTANT NUMBER := 1.0;
137 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Venue';
138 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
139
140 l_venue_rec Venue_Rec_Type := p_venue_rec;
141
142 -- l_location_rec HZ_LOCATION_PUB.Location_Rec_Type;
143 -- l_org_rec HZ_Party_PUB.Organization_Rec_Type;
144 -- l_psite_rec HZ_Party_PUB.Party_Site_Rec_Type;
145
146 l_location_rec HZ_LOCATION_V2PUB.Location_Rec_Type;
147 l_org_rec HZ_Party_V2PUB.Organization_Rec_Type;
148 l_psite_rec HZ_Party_Site_V2PUB.Party_Site_Rec_Type;
149
150 l_sales_team_rec AS_ACCESS_PUB.sales_team_rec_type;
151
152 l_return_status VARCHAR2(1);
153
154 l_location_id NUMBER;
155 l_party_id NUMBER;
156
157 l_party_site_id NUMBER;
158 l_party_number VARCHAR2(30);
159 l_party_site_number VARCHAR2(30);
160 l_profile_id NUMBER;
161 l_access_id NUMBER;
162
163 -- start dhsingh
164 -- l_party_site_use_rec HZ_PARTY_PUB.party_site_use_rec_type;
165 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
166 l_party_site_use_id NUMBER;
167 -- soagrawa
168 -- l_code_assignment_rec_type HZ_CLASSIFICATION_PUB.code_assignment_rec_type;
169 l_code_assignment_rec_type HZ_CLASSIFICATION_V2PUB.code_assignment_rec_type;
170 l_code_assignment_id NUMBER;
171 -- end dhsingh
172
173 BEGIN
174
175 --------------------- initialize -----------------------
176 SAVEPOINT Create_Venue;
177
178 IF (AMS_DEBUG_HIGH_ON) THEN
179 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
180 END IF;
181
182 IF FND_API.to_boolean (p_init_msg_list) THEN
183 FND_MSG_PUB.initialize;
184 END IF;
185
186 IF NOT FND_API.compatible_api_call (
187 L_API_VERSION,
188 p_api_version,
189 L_API_NAME,
190 G_PKG_NAME
191 ) THEN
192 RAISE FND_API.g_exc_unexpected_error;
193 END IF;
194
195 x_return_status := FND_API.g_ret_sts_success;
196
197 ----------------------- validate -----------------------
198 IF (AMS_DEBUG_HIGH_ON) THEN
199
200 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
201 END IF;
202
203 -- Only Validate name here for parties - everything else will be done in the base
204 IF (l_venue_rec.venue_name is null)
205 THEN
206 AMS_Utility_PVT.error_message('AMS_VENUE_NO_VENUE_NAME');
207 RAISE FND_API.g_exc_error;
208 END IF;
209
210 l_location_rec.address1 := l_venue_rec.address1;
211 l_location_rec.address2 := l_venue_rec.address2;
212 l_location_rec.address3 := l_venue_rec.address3;
213 l_location_rec.address4 := l_venue_rec.address4;
214 l_location_rec.city := l_venue_rec.city;
215 l_location_rec.state := l_venue_rec.state;
216 l_location_rec.postal_code := l_venue_rec.postal_code;
217 --l_location_rec.province := l_venue_rec.province;
218 --l_location_rec.county := l_venue_rec.county;
219 l_location_rec.country := l_venue_rec.country_code;
220 l_location_rec.ORIG_SYSTEM_REFERENCE := -1;
221 l_location_rec.CONTENT_SOURCE_TYPE := 'USER_ENTERED';
222 l_location_rec.created_by_module := 'AMS_EVENT';
223
224 HZ_LOCATION_V2PUB.Create_Location(
225 p_init_msg_list => p_init_msg_list
226 , p_location_rec => l_location_rec
227 , x_return_status => l_return_status
228 , x_msg_count => x_msg_count
229 , x_msg_data => x_msg_data
230 , x_location_id => l_location_id
231 );
232 -- GDEODHAR : Sept. 29, 2000 : Changed the p_commit to false in the above call.
233 -- This was giving me the savepoint not established error if something (such as
234 -- resource creation) failed below from here to the exception block.
235
236 IF (AMS_DEBUG_HIGH_ON) THEN
237
238
239
240 AMS_Utility_PVT.Debug_Message('Location ID: ' || to_char(l_location_id));
241
242 END IF;
243
244 IF l_return_status = FND_API.g_ret_sts_error THEN
245 RAISE FND_API.g_exc_error;
246 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
247 RAISE FND_API.g_exc_unexpected_error;
248 END IF;
249
250 l_org_rec.organization_name := l_venue_rec.venue_name;
251 l_org_rec.created_by_module := 'AMS_EVENT';
252 l_venue_rec.venue_name := 'DUMMY'; -- want to be clear that venue_name does not have a real value for VENU
253
254 HZ_Party_V2PUB.Create_Organization(
255 p_init_msg_list => p_init_msg_list
256 , p_organization_rec => l_org_rec
257 , x_return_status => l_return_status
258 , x_msg_count => x_msg_count
259 , x_msg_data => x_msg_data
260 , x_party_id => l_party_id
261 , x_party_number => l_party_number
262 , x_profile_id => l_profile_id
263 );
264
265 IF (AMS_DEBUG_HIGH_ON) THEN
266
267
268
269 AMS_Utility_PVT.Debug_Message('Party ID: ' || to_char(l_party_id));
270
271 END IF;
272
273 IF l_return_status = FND_API.g_ret_sts_error THEN
274 RAISE FND_API.g_exc_error;
275 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
276 RAISE FND_API.g_exc_unexpected_error;
277 END IF;
278
279 -- dhsingh start
280 l_code_assignment_rec_type.owner_table_name := 'HZ_PARTIES';
281 l_code_assignment_rec_type.owner_table_id := l_party_id;
282 l_code_assignment_rec_type.class_category := 'BUSINESS_FUNCTION';
283 l_code_assignment_rec_type.class_code := 'EVENT_VENUE';
284 -- l_code_assignment_rec_type.created_by_module := 'Oracle Marketing';
285 -- l_code_assignment_rec_type.application_id := 530;
286 l_code_assignment_rec_type.primary_flag := 'Y';
287 l_code_assignment_rec_type.start_date_active := sysdate;
288 l_code_assignment_rec_type.created_by_module := 'AMS_EVENT';
289
290 HZ_CLASSIFICATION_V2PUB.create_code_assignment(
291 p_init_msg_list => p_init_msg_list
292 , p_code_assignment_rec => l_code_assignment_rec_type
293 , x_return_status => l_return_status
294 , x_msg_count => x_msg_count
295 , x_msg_data => x_msg_data
296 , x_code_assignment_id => l_code_assignment_id
297 );
298
299 IF l_return_status = FND_API.g_ret_sts_error THEN
300 RAISE FND_API.g_exc_error;
301 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
302 RAISE FND_API.g_exc_unexpected_error;
303 END IF;
304 -- end dhsingh
305 l_psite_rec.party_id := l_party_id;
306 l_psite_rec.location_id := l_location_id;
307 l_psite_rec.identifying_address_flag := 'Y';
308 l_psite_rec.created_by_module := 'AMS_EVENT';
309 -- psite_rec.status := 'A';
310 /*
311 hz_party_pub.create_party_site( p_api_version => 1.0
312 , p_init_msg_list => p_init_msg_list
313 , p_commit => FND_API.g_false
314 , p_party_site_rec => l_psite_rec
315 , x_return_status => l_return_status
316 , x_msg_count => x_msg_count
317 , x_msg_data => x_msg_data
318 , x_party_site_id => l_party_site_id
319 , x_party_site_number => l_party_site_number
320 , p_validation_level => p_validation_level
321 );
322 */
323
324 HZ_Party_Site_V2PUB.create_party_site(
325 p_init_msg_list => p_init_msg_list
326 , p_party_site_rec => l_psite_rec
327 , x_return_status => l_return_status
328 , x_msg_count => x_msg_count
329 , x_msg_data => x_msg_data
330 , x_party_site_id => l_party_site_id
331 , x_party_site_number => l_party_site_number
332 );
333
334
335 IF (AMS_DEBUG_HIGH_ON) THEN
336
337
338
339 AMS_Utility_PVT.Debug_Message('Party Site ID: ' || to_char(l_party_site_id));
340
341 END IF;
342
343 IF l_return_status = FND_API.g_ret_sts_error THEN
344 RAISE FND_API.g_exc_error;
345 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
346 RAISE FND_API.g_exc_unexpected_error;
347 END IF;
348
349 -- start dhsingh
350 l_party_site_use_rec.party_site_id := l_party_site_id ;
351 l_party_site_use_rec.site_use_type := 'VENUE' ;
352 l_party_site_use_rec.created_by_module := 'AMS_EVENT';
353 /*
354 hz_party_pub.create_party_site_use( p_api_version => 1.0
355 , p_init_msg_list => p_init_msg_list
356 , p_commit => FND_API.G_false
357 , p_party_site_use_rec => l_party_site_use_rec
358 , x_return_status => l_return_status
359 , x_msg_count => x_msg_count
360 , x_msg_data => x_msg_data
361 , x_party_site_use_id => l_party_site_use_id
362 , p_validation_level =>p_validation_level
363 );
364 */
368 , p_party_site_use_rec => l_party_site_use_rec
365
366 hz_party_site_v2pub.create_party_site_use(
367 p_init_msg_list => p_init_msg_list
369 , x_return_status => l_return_status
370 , x_msg_count => x_msg_count
371 , x_msg_data => x_msg_data
372 , x_party_site_use_id => l_party_site_use_id
373 );
374 AMS_Utility_PVT.Debug_Message('Party Site ID: ' || to_char(l_party_site_use_id));
375
376 IF l_return_status = FND_API.g_ret_sts_error THEN
377 RAISE FND_API.g_exc_error;
378 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
379 RAISE FND_API.g_exc_unexpected_error;
380 END IF;
381
382 -- end dhsingh
383
384 /*
385 Removing this as unnecessary (see bug 2386818)
386 IF ( (l_venue_rec.salesforce_id is not null)
387 -- AND (FND_PROFILE.value('AS_CUST_ACCESS') <> 'F')
388 )
389 THEN
390 IF (AMS_DEBUG_HIGH_ON) THEN
391
392 AMS_Utility_PVT.Debug_Message('Creating row in AS_ACCESSES_ALL');
393 END IF;
394
395 l_sales_team_rec.salesforce_id := l_venue_rec.salesforce_id;
396 l_sales_team_rec.customer_id := l_party_id;
397 l_sales_team_rec.sales_group_id := l_venue_rec.sales_group_id;
398 l_sales_team_rec.person_id := l_venue_rec.person_id;
399 l_sales_team_rec.address_id := l_party_site_id;
400 l_sales_team_rec.salesforce_role_code := FND_PROFILE.value('AS_DEF_CUST_ST_ROLE');
401
402 as_access_pub.create_salesteam( p_api_version_number => 2.0
403 , p_init_msg_list => p_init_msg_list
404 , p_commit => FND_API.g_false
405 , p_validation_level => p_validation_level
406 , p_access_profile_rec => NULL
407 , p_check_access_flag => 'N'
408 , p_admin_flag => 'N'
409 , p_admin_group_id => NULL
410 , p_identity_salesforce_id => l_venue_rec.salesforce_id
411 , p_sales_team_rec => l_sales_team_rec
412 , x_return_status => l_return_status
413 , x_msg_count => x_msg_count
414 , x_msg_data => x_msg_data
415 , x_access_id => l_access_id
416 );
417
418 IF (AMS_DEBUG_HIGH_ON) THEN
419
420
421
422 AMS_Utility_PVT.Debug_Message('Access ID: ' || to_char(l_access_id));
423
424 END IF;
425
426 IF l_return_status = FND_API.g_ret_sts_error THEN
427 RAISE FND_API.g_exc_error;
428 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
429 RAISE FND_API.g_exc_unexpected_error;
430 END IF;
431
432 END IF; -- Create row in AS_ACCESSES_ALL
433 */
434
435 /*
436 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
437 l_venue_rec.location_id := l_location_id;
438 END IF;
439 */
440 l_venue_rec.party_id := l_party_id;
441
442 Create_Venue_Base( p_api_version => p_api_version
443 , p_init_msg_list => p_init_msg_list
444 , p_commit => p_commit
445 , p_validation_level => p_validation_level
446 , x_return_status => x_return_status
447 , x_msg_count => x_msg_count
448 , x_msg_data => x_msg_data
449 , p_venue_rec => l_venue_rec
450 , p_object_type => 'VENU'
451 , x_venue_id => x_venue_id
452 );
453
454 EXCEPTION
455 WHEN FND_API.g_exc_error THEN
456 ROLLBACK TO Create_Venue;
457 x_return_status := FND_API.g_ret_sts_error;
458 FND_MSG_PUB.count_and_get(
459 p_encoded => FND_API.g_false,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463 WHEN FND_API.g_exc_unexpected_error THEN
464 ROLLBACK TO Create_Venue;
465 x_return_status := FND_API.g_ret_sts_unexp_error ;
466 FND_MSG_PUB.count_and_get (
467 p_encoded => FND_API.g_false,
468 p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471 WHEN OTHERS THEN
472 ROLLBACK TO Create_Venue;
473 x_return_status := FND_API.g_ret_sts_unexp_error ;
474
475 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
476 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
477 END IF;
478
479 FND_MSG_PUB.count_and_get (
480 p_encoded => FND_API.g_false,
481 p_count => x_msg_count,
485 END Create_Venue;
482 p_data => x_msg_data
483 );
484
486
487 --------------------------------------------------------------------
488 -- PROCEDURE
489 -- Create_Room
490 --
491 --------------------------------------------------------------------
492 PROCEDURE Create_Room (
493 p_api_version IN NUMBER,
494 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
495 p_commit IN VARCHAR2 := FND_API.g_false,
496 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
497
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501
502 p_venue_rec IN Venue_Rec_Type,
503 x_venue_id OUT NOCOPY NUMBER
504 )
505 IS
506
507 L_API_VERSION CONSTANT NUMBER := 1.0;
508 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Room';
509 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
510
511 l_return_status VARCHAR2(1);
512
513 l_venue_rec Venue_Rec_Type := p_venue_rec;
514
515 BEGIN
516
517 --------------------- initialize -----------------------
518 SAVEPOINT Create_Venue;
519
520 IF (AMS_DEBUG_HIGH_ON) THEN
521
522
523
524 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
525
526 END IF;
527
528 IF FND_API.to_boolean (p_init_msg_list) THEN
529 FND_MSG_PUB.initialize;
530 END IF;
531
532 IF NOT FND_API.compatible_api_call (
533 L_API_VERSION,
534 p_api_version,
535 L_API_NAME,
536 G_PKG_NAME
537 ) THEN
538 RAISE FND_API.g_exc_unexpected_error;
539 END IF;
540
541 IF l_venue_rec.venue_type_code IS NULL THEN
542 l_venue_rec.venue_type_code := 'ROOM';
543 END IF;
544
545 x_return_status := FND_API.g_ret_sts_success;
546
547 Create_Venue_Base( p_api_version => p_api_version
548 , p_init_msg_list => p_init_msg_list
549 , p_commit => p_commit
550 , p_validation_level => p_validation_level
551 , x_return_status => x_return_status
552 , x_msg_count => x_msg_count
553 , x_msg_data => x_msg_data
554 , p_venue_rec => l_venue_rec
555 , p_object_type => 'ROOM'
556 , x_venue_id => x_venue_id
557 );
558
559 EXCEPTION
560 WHEN FND_API.g_exc_error THEN
561 ROLLBACK TO Create_Venue;
562 x_return_status := FND_API.g_ret_sts_error;
563 FND_MSG_PUB.count_and_get(
564 p_encoded => FND_API.g_false,
565 p_count => x_msg_count,
566 p_data => x_msg_data
567 );
568 WHEN FND_API.g_exc_unexpected_error THEN
569 ROLLBACK TO Create_Venue;
570 x_return_status := FND_API.g_ret_sts_unexp_error ;
571 FND_MSG_PUB.count_and_get (
572 p_encoded => FND_API.g_false,
573 p_count => x_msg_count,
574 p_data => x_msg_data
575 );
576 WHEN OTHERS THEN
577 ROLLBACK TO Create_Venue;
578 x_return_status := FND_API.g_ret_sts_unexp_error ;
579
580 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
581 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
582 END IF;
583
584 FND_MSG_PUB.count_and_get (
585 p_encoded => FND_API.g_false,
586 p_count => x_msg_count,
587 p_data => x_msg_data
588 );
589
590 END Create_Room;
591
592
593 --------------------------------------------------------------------
594 -- PROCEDURE
595 -- Create_Venue_Base
596 --
597 --------------------------------------------------------------------
598 PROCEDURE Create_Venue_Base (
599 p_api_version IN NUMBER,
600 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
601 p_commit IN VARCHAR2 := FND_API.g_false,
602 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
603
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2,
607
608 p_venue_rec IN Venue_Rec_Type,
609 p_object_type IN VARCHAR2,
610 x_venue_id OUT NOCOPY NUMBER
611 )
612 IS
613
614 L_API_VERSION CONSTANT NUMBER := 1.0;
615 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Venue_Base';
616 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
617
618 l_venue_rec Venue_Rec_Type := p_venue_rec;
619 l_dummy NUMBER;
620 l_return_status VARCHAR2(1);
621 l_location_id NUMBER;
622 --l_msg_count NUMBER;
623
624 l_category VARCHAR2(30);
625 l_source_id NUMBER;
626 x_resource_id NUMBER;
627 x_resource_number VARCHAR2(30);
628 l_date DATE;
629
630 CURSOR c_seq IS
631 SELECT ams_venues_b_s.NEXTVAL
632 FROM dual;
633
637 WHERE EXISTS (SELECT 1
634 CURSOR c_id_exists (x_id IN NUMBER) IS
635 SELECT 1
636 FROM dual
638 FROM ams_venues_vl
639 WHERE venue_id = x_id);
640
641 CURSOR c_date IS
642 SELECT sysdate
643 FROM dual;
644
645 BEGIN
646
647 x_return_status := FND_API.g_ret_sts_success;
648
649 Validate_Venue (
650 p_api_version => l_api_version,
651 p_init_msg_list => p_init_msg_list,
652 p_validation_level => p_validation_level,
653 x_return_status => l_return_status,
654 x_msg_count => x_msg_count,
655 x_msg_data => x_msg_data,
656 p_venue_rec => l_venue_rec,
657 p_object_type => p_object_type
658 );
659
660 IF l_return_status = FND_API.g_ret_sts_error THEN
661 RAISE FND_API.g_exc_error;
662 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
663 RAISE FND_API.g_exc_unexpected_error;
664 END IF;
665
666 IF (AMS_DEBUG_HIGH_ON) THEN
667
668
669
670 AMS_Utility_PVT.debug_message (l_full_name || ': Check IDs');
671
672 END IF;
673 --
674 -- Check for the ID.
675 --
676 IF l_venue_rec.venue_id IS NULL THEN
677 LOOP
678 --
679 -- If the ID is not passed into the API, then
680 -- grab a value from the sequence.
681 OPEN c_seq;
682 FETCH c_seq INTO l_venue_rec.venue_id;
683 CLOSE c_seq;
684
685 --
686 -- Check to be sure that the sequence does not exist.
687 OPEN c_id_exists (l_venue_rec.venue_id);
688 FETCH c_id_exists INTO l_dummy;
689 CLOSE c_id_exists;
690
691 --
692 -- If the value for the ID already exists, then
693 -- l_dummy would be populated with '1', otherwise,
694 -- it receives NULL.
695 EXIT WHEN l_dummy IS NULL;
696 END LOOP;
697 END IF;
698
699 -------------------------- insert --------------------------
700 IF (AMS_DEBUG_HIGH_ON) THEN
701
702 AMS_Utility_PVT.debug_message (l_full_name || ': Insert ' || p_object_type || ', ' || l_venue_rec.venue_id);
703 END IF;
704
705 --
706 -- Insert into mutli-language supported table.
707 --
708 INSERT INTO ams_venues_b (
709 VENUE_ID,
710 CUSTOM_SETUP_ID,
711 LAST_UPDATE_DATE,
712 LAST_UPDATED_BY,
713 CREATION_DATE,
714 CREATED_BY,
715 LAST_UPDATE_LOGIN,
716 OBJECT_VERSION_NUMBER,
717 VENUE_TYPE_CODE,
718 DIRECT_PHONE_FLAG,
719 INTERNAL_FLAG,
720 ENABLED_FLAG,
721 RATING_CODE,
722 TELECOM_CODE,
723 CAPACITY,
724 AREA_SIZE,
725 AREA_SIZE_UOM_CODE,
726 CEILING_HEIGHT,
727 CEILING_HEIGHT_UOM_CODE,
728 USAGE_COST,
729 USAGE_COST_UOM_CODE,
730 USAGE_COST_CURRENCY_CODE,
731 PARENT_VENUE_ID,
732 LOCATION_ID,
733 DIRECTIONS,
734 VENUE_CODE,
735 OBJECT_TYPE,
736 PARTY_ID,
737 ATTRIBUTE_CATEGORY,
738 ATTRIBUTE1,
739 ATTRIBUTE2,
740 ATTRIBUTE3,
741 ATTRIBUTE4,
742 ATTRIBUTE5,
743 ATTRIBUTE6,
744 ATTRIBUTE7,
745 ATTRIBUTE8,
746 ATTRIBUTE9,
747 ATTRIBUTE10,
748 ATTRIBUTE11,
749 ATTRIBUTE12,
750 ATTRIBUTE13,
751 ATTRIBUTE14,
752 ATTRIBUTE15
753 )
754 VALUES (
755 l_venue_rec.venue_id,
756 l_venue_rec.custom_setup_id,
757
758 -- standard who columns
759 SYSDATE,
760 FND_GLOBAL.User_Id,
761 SYSDATE,
762 FND_GLOBAL.User_Id,
763 FND_GLOBAL.Conc_Login_Id,
764
765 1, -- object_version_number
766 l_venue_rec.venue_type_code,
767 NVL (l_venue_rec.direct_phone_flag, 'N'), -- Default is 'N'
768 NVL (l_venue_rec.internal_flag, 'Y'), -- Default is 'Y'
769 NVL (l_venue_rec.enabled_flag, 'Y'), -- Default is 'Y'
770
771 l_venue_rec.rating_code,
772 l_venue_rec.telecom_code,
773 l_venue_rec.capacity,
774 l_venue_rec.area_size,
775 l_venue_rec.area_size_uom_code,
776 l_venue_rec.ceiling_height,
777 l_venue_rec.ceiling_height_uom_code,
778 l_venue_rec.usage_cost,
779 l_venue_rec.usage_cost_uom_code,
780 l_venue_rec.usage_cost_currency_code,
781 l_venue_rec.parent_venue_id,
782 l_venue_rec.location_id,
783 l_venue_rec.directions,
784 l_venue_rec.venue_code,
785 p_object_type,
786 l_venue_rec.party_id,
787 l_venue_rec.attribute_category,
788 l_venue_rec.attribute1,
789 l_venue_rec.attribute2,
790 l_venue_rec.attribute3,
791 l_venue_rec.attribute4,
792 l_venue_rec.attribute5,
793 l_venue_rec.attribute6,
797 l_venue_rec.attribute10,
794 l_venue_rec.attribute7,
795 l_venue_rec.attribute8,
796 l_venue_rec.attribute9,
798 l_venue_rec.attribute11,
799 l_venue_rec.attribute12,
800 l_venue_rec.attribute13,
801 l_venue_rec.attribute14,
802 l_venue_rec.attribute15
803 );
804
805 INSERT INTO ams_venues_tl (
806 venue_id,
807 language,
808 last_update_date,
809 last_updated_by,
810 creation_date,
811 created_by,
812 last_update_login,
813 source_lang,
814 venue_name,
815 description
816 )
817 SELECT l_venue_rec.venue_id,
818 l.language_code,
819 -- standard who columns
820 SYSDATE,
821 FND_GLOBAL.User_Id,
822 SYSDATE,
823 FND_GLOBAL.User_Id,
824 FND_GLOBAL.Conc_Login_Id,
825 USERENV('LANG'),
826 l_venue_rec.venue_name,
827 l_venue_rec.description
828 FROM fnd_languages l
829 WHERE l.installed_flag IN ('I', 'B')
830 AND NOT EXISTS (SELECT NULL
831 FROM ams_venues_tl t
832 WHERE t.venue_id = l_venue_rec.venue_id
833 AND t.language = l.language_code);
834
835 ------------------------- finish -------------------------------
836 -- set OUT value
837 x_venue_id := l_venue_rec.venue_id;
838 -- modified sugupta 9/11/2000
839 ----------------- creare resource from recently created venue id--------
840 --- for now since AMS owns Venues, it will create a resource id whenever a new venue id is
841 -- created. However, AMS sjould own responsibility to take care of its data in jtf_rs_resources_extn
842 -- table. RCSINGH has guaranteed they do not delete or mess with data in this table.
843 -- the resource will of the category 'VENUES', a new JTF object has been craeted in jtf objects
844 -- for the purpose.
845 l_category := 'VENUE';
846 l_source_id := l_venue_rec.venue_id;
847
848 OPEN c_date;
849 FETCH c_date INTO l_date;
850 CLOSE c_date;
851
852 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
853 P_API_VERSION => 1.0,
854 P_INIT_MSG_LIST => p_init_msg_list,
855 P_COMMIT => FND_API.g_false,
856 P_CATEGORY => l_category,
857 p_source_id => l_source_id,
858 P_START_DATE_ACTIVE => l_date,
859 P_RESOURCE_NAME => l_venue_rec.venue_name,
860 P_SOURCE_NAME => l_venue_rec.venue_name,
861 X_RETURN_STATUS => l_return_status,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data,
864 x_resource_id => x_resource_id,
865 x_resource_number => x_resource_number
866 );
867 IF l_return_status = FND_API.g_ret_sts_error THEN
868 RAISE FND_API.g_exc_error;
869 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
870 RAISE FND_API.g_exc_unexpected_error;
871 END IF;
872
873
874 -- GDEODHAR : Sept. 29, 2000 : Changed the p_commit to false in the above call.
875 -- It could give the savepoint not established error if something (if we add
876 -- something after this call and before commit) fails from below here to the
877 -- exception block.
878
879
880 --
881 -- END of API body.
882 --
883
884 -- Standard check of p_commit.
885 IF FND_API.To_Boolean ( p_commit ) THEN
886 COMMIT WORK;
887 END IF;
888
889 FND_MSG_PUB.count_and_get(
890 p_encoded => FND_API.g_false,
891 p_count => x_msg_count,
892 p_data => x_msg_data
893 );
894
895 --IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message (l_full_name || ': End');END IF;
896
897 EXCEPTION
898 WHEN FND_API.g_exc_error THEN
899 ROLLBACK TO Create_Venue;
900 x_return_status := FND_API.g_ret_sts_error;
901 FND_MSG_PUB.count_and_get(
902 p_encoded => FND_API.g_false,
903 p_count => x_msg_count,
904 p_data => x_msg_data
905 );
906 WHEN FND_API.g_exc_unexpected_error THEN
907 ROLLBACK TO Create_Venue;
908 x_return_status := FND_API.g_ret_sts_unexp_error ;
909 FND_MSG_PUB.count_and_get (
910 p_encoded => FND_API.g_false,
911 p_count => x_msg_count,
912 p_data => x_msg_data
913 );
914 WHEN OTHERS THEN
915 ROLLBACK TO Create_Venue;
916 x_return_status := FND_API.g_ret_sts_unexp_error ;
917
918 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
919 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
920 END IF;
921
922 FND_MSG_PUB.count_and_get (
923 p_encoded => FND_API.g_false,
924 p_count => x_msg_count,
925 p_data => x_msg_data
926 );
927 END Create_Venue_Base;
928
929
930 --------------------------------------------------------------------
931 -- PROCEDURE
932 -- Update_Venue
933 --
934 --------------------------------------------------------------------
935 PROCEDURE Update_Venue (
939 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
936 p_api_version IN NUMBER,
937 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
938 p_commit IN VARCHAR2 := FND_API.g_false,
940
941 x_return_status OUT NOCOPY VARCHAR2,
942 x_msg_count OUT NOCOPY NUMBER,
943 x_msg_data OUT NOCOPY VARCHAR2,
944
945 p_venue_rec IN Venue_Rec_Type
946 )
947 IS
948
949 CURSOR c_location(ven_id IN NUMBER) IS
950 SELECT loc.address1, loc.address2, loc.city, loc.state, loc.country
951 FROM hz_parties loc, ams_venues_vl ven
952 WHERE loc.party_id = ven.party_id
953 and ven.venue_id = ven_id;
954
955 -- l_location_rec HZ_LOCATION_PUB.Location_Rec_Type;
956 l_location_rec HZ_LOCATION_V2PUB.Location_Rec_Type;
957 l_location_id NUMBER;
958 l_address1 VARCHAR2(240);
959 l_address2 VARCHAR2(240);
960 l_city VARCHAR2(60);
961 l_state VARCHAR2(60);
962 l_country VARCHAR2(60);
963
964 --l_psite_rec HZ_Party_PUB.Party_Site_Rec_Type;
965 l_psite_rec HZ_Party_Site_V2PUB.Party_Site_Rec_Type;
966 l_party_site_id NUMBER;
967 l_party_site_number VARCHAR2(30);
968
969 L_API_VERSION CONSTANT NUMBER := 1.0;
970 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Venue';
971 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
972
973 l_venue_rec Venue_Rec_Type;
974 l_return_status VARCHAR2(1);
975
976 BEGIN
977
978 --------------------- initialize -----------------------
979 SAVEPOINT Update_Venue;
980
981 IF (AMS_DEBUG_HIGH_ON) THEN
982
983
984
985 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
986
987 END IF;
988
989 IF FND_API.to_boolean (p_init_msg_list) THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 IF NOT FND_API.compatible_api_call(
994 l_api_version,
995 p_api_version,
996 l_api_name,
997 g_pkg_name
998 ) THEN
999 RAISE FND_API.g_exc_unexpected_error;
1000 END IF;
1001
1002 x_return_status := FND_API.g_ret_sts_success;
1003
1004 ----------------------- validate ----------------------
1005 IF (AMS_DEBUG_HIGH_ON) THEN
1006
1007 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
1008 END IF;
1009
1010 -- replace g_miss_char/num/date with current column values
1011 -- 07/10/2000 IMPORTANT THING TO NOTE ABOUT UPDATE VENUES
1012 -- THE WAY SCREEN GOT DESIGNED... DONT BLAME ME.... p_venue_rec NOT
1013 -- INITILIZED TO FND_API.G_MISS_CHAR'S... FROM ONE OF THE VENUE SCREENS- AMSVENCR.JSP
1014 -- WE DONT LOSE ANY INFO BECAUSE THAT SCREEN IS SAME FOR CREATE AND UPDATE..
1015 -- ALL INFO FOR VENUE IS ALWAYS PASSED..
1016
1017 -- At this point the p_venue_rec has all the data sent from the outside world
1018 -- such as the JSP page.
1019
1020 Complete_Venue_Rec (p_venue_rec, l_venue_rec);
1021 -- CHECK FOR WHETHER NEW LOCATION NEEDS TO BE CREATED
1022 OPEN c_location(l_venue_rec.venue_id);
1023 FETCH c_location INTO l_address1, l_address2, l_city, l_state, l_country;
1024 IF c_location%NOTFOUND THEN
1025 CLOSE c_location;
1026 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1027 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1028 FND_MSG_PUB.add;
1029 END IF;
1030 RAISE FND_API.g_exc_error;
1031 END IF;
1032 CLOSE c_location;
1033 /*
1034
1035 -- by musman 04-Sep-2002
1036 -- Commenting out the creation of HZ_locations and HZ_party_sites since we
1037 -- are not allowing the user to update the country,address ,city or state through the screen
1038
1039 IF (l_venue_rec.address1 <> l_address1 OR
1040 l_venue_rec.address2 <> l_address2 OR
1041 l_venue_rec.city <> l_city OR
1042 l_venue_rec.state <> l_state OR
1043 l_venue_rec.country <> l_country) THEN
1044
1045 The values on RHS / LHS or both can be NULL, You cannot compare NULL
1046 with NULL. murali 11/13/2000
1047 -- *
1048 IF (nvl(l_venue_rec.address1, ' ') <> nvl(l_address1, ' ') OR
1049 nvl(l_venue_rec.address2, ' ') <> nvl(l_address2, ' ') OR
1050 nvl(l_venue_rec.city, ' ') <> nvl(l_city, ' ') OR
1051 nvl(l_venue_rec.state, ' ') <> nvl(l_state, ' ') OR
1052 nvl(l_venue_rec.country_code, ' ') <> nvl(l_country, ' ')) THEN
1053
1054 l_location_rec.address1 := l_venue_rec.address1;
1055 l_location_rec.address2 := l_venue_rec.address2;
1056 --l_location_rec.address3 := l_venue_rec.address3;
1057 --l_location_rec.address4 := l_venue_rec.address4;
1058 l_location_rec.city := l_venue_rec.city;
1059 l_location_rec.state := l_venue_rec.state;
1060 l_location_rec.postal_code := l_venue_rec.postal_code;
1061 --l_location_rec.province := l_venue_rec.province;
1062 --l_location_rec.county := l_venue_rec.county;
1063 l_location_rec.country := l_venue_rec.country_code;
1064 l_location_rec.ORIG_SYSTEM_REFERENCE := -1;
1065 l_location_rec.CONTENT_SOURCE_TYPE := 'USER_ENTERED';
1069 p_init_msg_list => p_init_msg_list,
1066 l_location_rec.created_by_module := 'AMS_EVENT';
1067
1068 HZ_LOCATION_V2PUB.Create_Location(
1070 p_location_rec => l_location_rec,
1071 x_return_status => l_return_status,
1072 x_msg_count => x_msg_count,
1073 x_msg_data => x_msg_data,
1074 x_location_id => l_location_id
1075 );
1076
1077 -- Changed the commit flag to false for the above call.
1078 -- This could potentially give the savepoint not established error.
1079 -- Refer to the create procedure for more details.
1080
1081 IF l_return_status = FND_API.g_ret_sts_error THEN
1082 RAISE FND_API.g_exc_error;
1083 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1084 RAISE FND_API.g_exc_unexpected_error;
1085 END IF;
1086
1087 l_psite_rec.party_id := l_venue_rec.party_id;
1088 l_psite_rec.location_id := l_location_id;
1089 l_psite_rec.identifying_address_flag := 'Y';
1090 l_psite_rec.created_by_module := 'AMS_EVENT';
1091 -- psite_rec.status := 'A';
1092
1093 hz_party_site_v2pub.create_party_site(
1094 p_init_msg_list => p_init_msg_list
1095 , p_party_site_rec => l_psite_rec
1096 , x_return_status => l_return_status
1097 , x_msg_count => x_msg_count
1098 , x_msg_data => x_msg_data
1099 , x_party_site_id => l_party_site_id
1100 , x_party_site_number => l_party_site_number
1101 );
1102
1103 IF (AMS_DEBUG_HIGH_ON) THEN
1104
1105
1106
1107 AMS_Utility_PVT.Debug_Message('Party Site ID: ' || to_char(l_party_site_id));
1108
1109 END IF;
1110
1111 IF l_return_status = FND_API.g_ret_sts_error THEN
1112 RAISE FND_API.g_exc_error;
1113 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1114 RAISE FND_API.g_exc_unexpected_error;
1115 END IF;
1116
1117 END IF; --check for addr1, city, state, country
1118 */
1119
1120 Update_Venue_Base( p_api_version => p_api_version
1121 , p_init_msg_list => p_init_msg_list
1122 , p_commit => p_commit
1123 , p_validation_level => p_validation_level
1124 , x_return_status => x_return_status
1125 , x_msg_count => x_msg_count
1126 , x_msg_data => x_msg_data
1127 , p_venue_rec => l_venue_rec
1128 , p_object_type => 'VENU'
1129 );
1130
1131 IF x_return_status = FND_API.g_ret_sts_error THEN
1132 RAISE FND_API.g_exc_error;
1133 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1134 RAISE FND_API.g_exc_unexpected_error;
1135 END IF;
1136
1137
1138 EXCEPTION
1139 WHEN FND_API.g_exc_error THEN
1140 ROLLBACK TO Update_Venue;
1141 x_return_status := FND_API.g_ret_sts_error;
1142 FND_MSG_PUB.count_and_get (
1143 p_encoded => FND_API.g_false,
1144 p_count => x_msg_count,
1145 p_data => x_msg_data
1146 );
1147 WHEN FND_API.g_exc_unexpected_error THEN
1148 ROLLBACK TO Update_Venue;
1149 x_return_status := FND_API.g_ret_sts_unexp_error ;
1150 FND_MSG_PUB.count_and_get (
1151 p_encoded => FND_API.g_false,
1152 p_count => x_msg_count,
1153 p_data => x_msg_data
1154 );
1155 WHEN OTHERS THEN
1156 ROLLBACK TO Update_Venue;
1157 x_return_status := FND_API.g_ret_sts_unexp_error ;
1158
1159 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1160 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1161 END IF;
1162
1163 FND_MSG_PUB.count_and_get (
1164 p_encoded => FND_API.g_false,
1165 p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168
1169 END Update_Venue;
1170
1171 --------------------------------------------------------------------
1172 -- PROCEDURE
1173 -- Update_Room
1174 --
1175 --------------------------------------------------------------------
1176 PROCEDURE Update_Room (
1177 p_api_version IN NUMBER,
1178 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1179 p_commit IN VARCHAR2 := FND_API.g_false,
1180 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1181
1182 x_return_status OUT NOCOPY VARCHAR2,
1183 x_msg_count OUT NOCOPY NUMBER,
1184 x_msg_data OUT NOCOPY VARCHAR2,
1185
1186 p_venue_rec IN Venue_Rec_Type
1187 )
1188 IS
1189 L_API_VERSION CONSTANT NUMBER := 1.0;
1190 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Room';
1191 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1192
1193 l_venue_rec Venue_Rec_Type;
1194
1195 BEGIN
1196
1200 IF (AMS_DEBUG_HIGH_ON) THEN
1197 --------------------- initialize -----------------------
1198 SAVEPOINT Update_Venue;
1199
1201
1202
1203
1204 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1205
1206 END IF;
1207
1208 IF FND_API.to_boolean (p_init_msg_list) THEN
1209 FND_MSG_PUB.initialize;
1210 END IF;
1211
1212 IF NOT FND_API.compatible_api_call(
1213 l_api_version,
1214 p_api_version,
1215 l_api_name,
1216 g_pkg_name
1217 ) THEN
1218 RAISE FND_API.g_exc_unexpected_error;
1219 END IF;
1220
1221 x_return_status := FND_API.g_ret_sts_success;
1222
1223 ----------------------- validate ----------------------
1224 IF (AMS_DEBUG_HIGH_ON) THEN
1225
1226 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
1227 END IF;
1228
1229 -- replace g_miss_char/num/date with current column values
1230 -- 07/10/2000 IMPORTANT THING TO NOTE ABOUT UPDATE VENUES
1231 -- THE WAY SCREEN GOT DESIGNED... DONT BLAME ME.... p_venue_rec NOT
1232 -- INITILIZED TO FND_API.G_MISS_CHAR'S... FROM ONE OF THE VENUE SCREENS- AMSVENCR.JSP
1233 -- WE DONT LOSE ANY INFO BECAUSE THAT SCREEN IS SAME FOR CREATE AND UPDATE..
1234 -- ALL INFO FOR VENUE IS ALWAYS PASSED..
1235
1236 -- At this point the p_venue_rec has all the data sent from the outside world
1237 -- such as the JSP page.
1238
1239 Complete_Venue_Rec (p_venue_rec, l_venue_rec);
1240
1241 Update_Venue_Base( p_api_version => p_api_version
1242 , p_init_msg_list => p_init_msg_list
1243 , p_commit => p_commit
1244 , p_validation_level => p_validation_level
1245 , x_return_status => x_return_status
1246 , x_msg_count => x_msg_count
1247 , x_msg_data => x_msg_data
1248 , p_venue_rec => l_venue_rec
1249 , p_object_type => 'ROOM'
1250 );
1251
1252 EXCEPTION
1253 WHEN FND_API.g_exc_error THEN
1254 ROLLBACK TO Update_Venue;
1255 x_return_status := FND_API.g_ret_sts_error;
1256 FND_MSG_PUB.count_and_get (
1257 p_encoded => FND_API.g_false,
1258 p_count => x_msg_count,
1259 p_data => x_msg_data
1260 );
1261 WHEN FND_API.g_exc_unexpected_error THEN
1262 ROLLBACK TO Update_Venue;
1263 x_return_status := FND_API.g_ret_sts_unexp_error ;
1264 FND_MSG_PUB.count_and_get (
1265 p_encoded => FND_API.g_false,
1266 p_count => x_msg_count,
1267 p_data => x_msg_data
1268 );
1269 WHEN OTHERS THEN
1270 ROLLBACK TO Update_Venue;
1271 x_return_status := FND_API.g_ret_sts_unexp_error ;
1272
1273 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1274 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1275 END IF;
1276
1277 FND_MSG_PUB.count_and_get (
1278 p_encoded => FND_API.g_false,
1279 p_count => x_msg_count,
1280 p_data => x_msg_data
1281 );
1282
1283 END Update_Room;
1284
1285
1286 --------------------------------------------------------------------
1287 -- PROCEDURE
1288 -- Update_Venue_Base
1289 --
1290 -- 07/10/2000 modified sugupta added code to create new loc id if
1291 -- address, city, state or country is changed
1292 --------------------------------------------------------------------
1293 PROCEDURE Update_Venue_Base (
1294 p_api_version IN NUMBER,
1295 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1296 p_commit IN VARCHAR2 := FND_API.g_false,
1297 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1298
1299 x_return_status OUT NOCOPY VARCHAR2,
1300 x_msg_count OUT NOCOPY NUMBER,
1301 x_msg_data OUT NOCOPY VARCHAR2,
1302
1303 p_venue_rec IN Venue_Rec_Type,
1304 p_object_type IN VARCHAR2
1305 )
1306 IS
1307 /*
1308 CURSOR c_location(ven_id IN NUMBER) IS
1309 SELECT loc.address1, loc.address2, loc.city, loc.state, loc.country
1310 FROM hz_locations loc, ams_venues_vl ven
1311 WHERE loc.location_id = ven.location_id
1312 and ven.venue_id = ven_id;
1313 -- l_location_rec HZ_LOCATION_PUB.Location_Rec_Type;
1314 l_location_rec HZ_LOCATION_V2PUB.Location_Rec_Type;
1315 l_location_id NUMBER;
1316 l_address1 VARCHAR2(240);
1317 l_address2 VARCHAR2(240);
1318 l_city VARCHAR2(60);
1319 l_state VARCHAR2(60);
1320 l_country VARCHAR2(60);
1321 */
1322 L_API_VERSION CONSTANT NUMBER := 1.0;
1323 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Venue_Base';
1324 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1325
1326 l_venue_rec Venue_Rec_Type;
1327 l_dummy NUMBER;
1328 l_return_status VARCHAR2(1);
1329 l_rec_cnt NUMBER;
1330 BEGIN
1331
1332 x_return_status := FND_API.g_ret_sts_success;
1333
1334 l_venue_rec := p_venue_rec;
1335
1336 -- At this point the l_venue_rec has all the data that came from outside and all
1340
1337 -- the other fields will have the old data. So all the fields will be available
1338 -- for the upcoming update call.
1339
1341 /* -- batoleti
1342 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1343 Check_Venue_Items (
1344 p_venue_rec => p_venue_rec,
1345 p_object_type => p_object_type,
1346 p_validation_mode => JTF_PLSQL_API.g_update,
1347 x_return_status => l_return_status
1348 );
1349
1350 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1351 RAISE FND_API.g_exc_unexpected_error;
1352 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1353 RAISE FND_API.g_exc_error;
1354 END IF;
1355 END IF;
1356
1357 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1358 Check_Venue_Record (
1359 p_venue_rec => p_venue_rec,
1360 p_complete_rec => l_venue_rec,
1361 x_return_status => l_return_status
1362 );
1363
1364 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1365 RAISE FND_API.g_exc_unexpected_error;
1366 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1367 RAISE FND_API.g_exc_error;
1368 END IF;
1369 END IF;
1370 */ -- batoleti
1371 --batoleti
1372 l_rec_cnt := 0;
1373
1374 SELECT count(*)
1375 INTO l_rec_cnt
1376 FROM ams_venues_tl
1377 where venue_id <> l_venue_rec.venue_id
1378 and venue_name = l_venue_rec.venue_name
1379 and SOURCE_LANG = USERENV('LANG');
1380
1381 IF (l_rec_cnt > 0) THEN
1382 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_DUP_NAME');
1383 FND_MSG_PUB.add;
1384 RAISE FND_API.g_exc_error;
1385 END IF;
1386
1387
1388
1389 --batoleti
1390 -------------------------- update --------------------
1391 IF (AMS_DEBUG_HIGH_ON) THEN
1392
1393 AMS_Utility_PVT.debug_message (l_full_name || ': Update');
1394 END IF;
1395
1396 UPDATE ams_venues_b
1397 SET
1398 last_update_date = SYSDATE,
1399 last_updated_by = FND_GLOBAL.User_Id,
1400 last_update_login = FND_GLOBAL.Conc_Login_Id,
1401
1402 object_version_number = object_version_number + 1,
1403 venue_type_code = l_venue_rec.venue_type_code,
1404 custom_setup_id = l_venue_rec.custom_setup_id,
1405 direct_phone_flag = NVL (l_venue_rec.direct_phone_flag, 'N'),
1406 internal_flag = NVL (l_venue_rec.internal_flag, 'Y'),
1407 enabled_flag = NVL (l_venue_rec.enabled_flag, 'Y'),
1408 rating_code = l_venue_rec.rating_code,
1409 capacity = l_venue_rec.capacity,
1410 area_size = l_venue_rec.area_size,
1411 area_size_uom_code = l_venue_rec.area_size_uom_code,
1412 ceiling_height = l_venue_rec.ceiling_height,
1413 ceiling_height_uom_code = l_venue_rec.ceiling_height_uom_code,
1414 usage_cost = l_venue_rec.usage_cost,
1415 usage_cost_uom_code = l_venue_rec.usage_cost_uom_code,
1416 usage_cost_currency_code = l_venue_rec.usage_cost_currency_code,
1417 parent_venue_id = l_venue_rec.parent_venue_id,
1418 location_id = l_venue_rec.location_id,
1419 directions = l_venue_rec.directions,
1420 venue_code = l_venue_rec.venue_code,
1421 telecom_code = l_venue_rec.telecom_code, -- Added Bug 4083293
1422 attribute_category = l_venue_rec.attribute_category,
1423 attribute1 = l_venue_rec.attribute1,
1424 attribute2 = l_venue_rec.attribute2,
1425 attribute3 = l_venue_rec.attribute3,
1426 attribute4 = l_venue_rec.attribute4,
1427 attribute5 = l_venue_rec.attribute5,
1428 attribute6 = l_venue_rec.attribute6,
1429 attribute7 = l_venue_rec.attribute7,
1430 attribute8 = l_venue_rec.attribute8,
1431 attribute9 = l_venue_rec.attribute9,
1432 attribute10 = l_venue_rec.attribute10,
1433 attribute11 = l_venue_rec.attribute11,
1434 attribute12 = l_venue_rec.attribute12,
1435 attribute13 = l_venue_rec.attribute13,
1436 attribute14 = l_venue_rec.attribute14,
1437 attribute15 = l_venue_rec.attribute15
1438 WHERE venue_id = l_venue_rec.venue_id
1439 AND object_version_number = l_venue_rec.object_version_number;
1440 -- GDEODHAR : Sept 29, 2000 : Added the AND condition.
1441
1442 IF (SQL%NOTFOUND) THEN
1443 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1444 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
1445 FND_MSG_PUB.add;
1446 END IF;
1447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1448 END IF;
1449
1450 UPDATE ams_venues_tl
1451 SET
1452 last_update_date = SYSDATE,
1453 last_updated_by = FND_GLOBAL.User_Id,
1454 last_update_login = FND_GLOBAL.Conc_Login_Id,
1455 source_lang = USERENV('LANG'),
1456 venue_name = l_venue_rec.venue_name,
1457 description = l_venue_rec.description
1458 WHERE venue_id = l_venue_rec.venue_id
1459 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
1460
1461 IF (SQL%NOTFOUND) THEN
1462 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1466 RAISE FND_API.g_exc_error;
1463 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
1464 FND_MSG_PUB.add;
1465 END IF;
1467 END IF;
1468
1469 -------------------- finish --------------------------
1470 IF FND_API.to_boolean (p_commit) THEN
1471 COMMIT;
1472 END IF;
1473
1474 FND_MSG_PUB.count_and_get (
1475 p_encoded => FND_API.g_false,
1476 p_count => x_msg_count,
1477 p_data => x_msg_data
1478 );
1479
1480 IF (AMS_DEBUG_HIGH_ON) THEN
1481
1482
1483
1484 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1485
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN FND_API.g_exc_error THEN
1490 ROLLBACK TO Update_Venue;
1491 x_return_status := FND_API.g_ret_sts_error;
1492 FND_MSG_PUB.count_and_get (
1493 p_encoded => FND_API.g_false,
1494 p_count => x_msg_count,
1495 p_data => x_msg_data
1496 );
1497 WHEN FND_API.g_exc_unexpected_error THEN
1498 ROLLBACK TO Update_Venue;
1499 x_return_status := FND_API.g_ret_sts_unexp_error ;
1500 FND_MSG_PUB.count_and_get (
1501 p_encoded => FND_API.g_false,
1502 p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505 WHEN OTHERS THEN
1506 ROLLBACK TO Update_Venue;
1507 x_return_status := FND_API.g_ret_sts_unexp_error ;
1508
1509 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1510 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1511 END IF;
1512
1513 FND_MSG_PUB.count_and_get (
1514 p_encoded => FND_API.g_false,
1515 p_count => x_msg_count,
1516 p_data => x_msg_data
1517 );
1518 END Update_Venue_Base;
1519
1520
1521 --------------------------------------------------------------------
1522 -- PROCEDURE
1523 -- Delete_Venue
1524 --
1525 --------------------------------------------------------------------
1526 PROCEDURE Delete_Venue (
1527 p_api_version IN NUMBER,
1528 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1529 p_commit IN VARCHAR2 := FND_API.g_false,
1530 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1531
1532 x_return_status OUT NOCOPY VARCHAR2,
1533 x_msg_count OUT NOCOPY NUMBER,
1534 x_msg_data OUT NOCOPY VARCHAR2,
1535
1536 p_venue_id IN NUMBER,
1537 p_object_version IN NUMBER
1538 )
1539 IS
1540 l_api_version CONSTANT NUMBER := 1.0;
1541 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Venue';
1542 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1543 BEGIN
1544 --------------------- initialize -----------------------
1545 SAVEPOINT Delete_Venue;
1546
1547 IF (AMS_DEBUG_HIGH_ON) THEN
1548
1549
1550
1551 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1552
1553 END IF;
1554
1555 IF FND_API.to_boolean (p_init_msg_list) THEN
1556 FND_MSG_PUB.initialize;
1557 END IF;
1558
1559 IF NOT FND_API.compatible_api_call (
1560 l_api_version,
1561 p_api_version,
1562 l_api_name,
1563 g_pkg_name
1564 ) THEN
1565 RAISE FND_API.g_exc_unexpected_error;
1566 END IF;
1567
1568 x_return_status := FND_API.g_ret_sts_success;
1569
1570 ------------------------ delete ------------------------
1571 IF (AMS_DEBUG_HIGH_ON) THEN
1572
1573 AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
1574 END IF;
1575
1576 -- Delete TL data
1577 DELETE FROM ams_venues_tl
1578 WHERE venue_id = p_venue_id;
1579
1580 IF (SQL%NOTFOUND) THEN
1581 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1582 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
1583 FND_MSG_PUB.add;
1584 END IF;
1585 RAISE FND_API.g_exc_error;
1586 END IF;
1587
1588 DELETE FROM ams_venues_b
1589 WHERE venue_id = p_venue_id
1590 AND object_version_number = p_object_version;
1591
1592 -------------------- finish --------------------------
1593 IF FND_API.to_boolean (p_commit) THEN
1594 COMMIT;
1595 END IF;
1596
1597 FND_MSG_PUB.count_and_get (
1598 p_encoded => FND_API.g_false,
1599 p_count => x_msg_count,
1600 p_data => x_msg_data
1601 );
1602
1603 IF (AMS_DEBUG_HIGH_ON) THEN
1604
1605
1606
1607 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1608
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN FND_API.g_exc_error THEN
1613 ROLLBACK TO Delete_Venue;
1614 x_return_status := FND_API.g_ret_sts_error;
1615 FND_MSG_PUB.count_and_get (
1616 p_encoded => FND_API.g_false,
1617 p_count => x_msg_count,
1618 p_data => x_msg_data
1619 );
1620 WHEN FND_API.g_exc_unexpected_error THEN
1621 ROLLBACK TO Delete_Venue;
1622 x_return_status := FND_API.g_ret_sts_unexp_error ;
1623 FND_MSG_PUB.count_and_get (
1624 p_encoded => FND_API.g_false,
1628 WHEN OTHERS THEN
1625 p_count => x_msg_count,
1626 p_data => x_msg_data
1627 );
1629 ROLLBACK TO Delete_Venue;
1630 x_return_status := FND_API.g_ret_sts_unexp_error ;
1631
1632 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1633 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1634 END IF;
1635
1636 FND_MSG_PUB.count_and_get (
1637 p_encoded => FND_API.g_false,
1638 p_count => x_msg_count,
1639 p_data => x_msg_data
1640 );
1641 END Delete_Venue;
1642
1643
1644 --------------------------------------------------------------------
1645 -- PROCEDURE
1646 -- Lock_Venue
1647 --
1648 --------------------------------------------------------------------
1649 PROCEDURE Lock_Venue (
1650 p_api_version IN NUMBER,
1651 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1652 p_commit IN VARCHAR2 := FND_API.g_false,
1653 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1654
1655 x_return_status OUT NOCOPY VARCHAR2,
1656 x_msg_count OUT NOCOPY NUMBER,
1657 x_msg_data OUT NOCOPY VARCHAR2,
1658
1659 p_venue_id IN NUMBER,
1660 p_object_version IN NUMBER
1661 )
1662 IS
1663 l_api_version CONSTANT NUMBER := 1.0;
1664 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Venue';
1665 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1666
1667 l_dummy NUMBER; -- Used by the lock cursor.
1668
1669 --
1670 -- NOTE: Not necessary to distinguish between a record
1671 -- which does not exist and one which has been updated
1672 -- by another user. To get that distinction, remove
1673 -- the object_version condition from the SQL statement
1674 -- and perform comparison in the body and raise the
1675 -- exception there.
1676 CURSOR c_lock IS
1677 SELECT object_version_number
1678 FROM ams_venues_vl
1679 WHERE venue_id = p_venue_id
1680 AND object_version_number = p_object_version
1681 FOR UPDATE NOWAIT;
1682 BEGIN
1683 --------------------- initialize -----------------------
1684 IF (AMS_DEBUG_HIGH_ON) THEN
1685
1686 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1687 END IF;
1688
1689 IF FND_API.to_boolean (p_init_msg_list) THEN
1690 FND_MSG_PUB.initialize;
1691 END IF;
1692
1693 IF NOT FND_API.compatible_api_call (
1694 l_api_version,
1695 p_api_version,
1696 l_api_name,
1697 g_pkg_name
1698 ) THEN
1699 RAISE FND_API.g_exc_unexpected_error;
1700 END IF;
1701
1702 x_return_status := FND_API.g_ret_sts_success;
1703
1704 ------------------------ lock -------------------------
1705 IF (AMS_DEBUG_HIGH_ON) THEN
1706
1707 AMS_Utility_PVT.debug_message (l_full_name || ': Lock');
1708 END IF;
1709
1710 OPEN c_lock;
1711 FETCH c_lock INTO l_dummy;
1712 IF (c_lock%NOTFOUND) THEN
1713 CLOSE c_lock;
1714 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1715 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1716 FND_MSG_PUB.add;
1717 END IF;
1718 RAISE FND_API.g_exc_error;
1719 END IF;
1720 CLOSE c_lock;
1721
1722 -------------------- finish --------------------------
1723 FND_MSG_PUB.count_and_get (
1724 p_encoded => FND_API.g_false,
1725 p_count => x_msg_count,
1726 p_data => x_msg_data
1727 );
1728
1729 IF (AMS_DEBUG_HIGH_ON) THEN
1730
1731
1732
1733 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1734
1735 END IF;
1736
1737 EXCEPTION
1738 WHEN AMS_Utility_PVT.resource_locked THEN
1739 x_return_status := FND_API.g_ret_sts_error;
1740 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1741 FND_MESSAGE.set_name ('AMS', 'AMS_API_RESOURCE_LOCKED');
1742 FND_MSG_PUB.add;
1743 END IF;
1744
1745 FND_MSG_PUB.count_and_get (
1746 p_encoded => FND_API.g_false,
1747 p_count => x_msg_count,
1748 p_data => x_msg_data
1749 );
1750 WHEN FND_API.g_exc_error THEN
1751 x_return_status := FND_API.g_ret_sts_error;
1752 FND_MSG_PUB.count_and_get (
1753 p_encoded => FND_API.g_false,
1754 p_count => x_msg_count,
1755 p_data => x_msg_data
1756 );
1757 WHEN FND_API.g_exc_unexpected_error THEN
1758 x_return_status := FND_API.g_ret_sts_unexp_error ;
1759 FND_MSG_PUB.count_and_get (
1760 p_encoded => FND_API.g_false,
1761 p_count => x_msg_count,
1762 p_data => x_msg_data
1763 );
1764 WHEN OTHERS THEN
1765 x_return_status := FND_API.g_ret_sts_unexp_error ;
1766 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1767 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1768 END IF;
1769
1770 FND_MSG_PUB.count_and_get (
1771 p_encoded => FND_API.g_false,
1775 END Lock_Venue;
1772 p_count => x_msg_count,
1773 p_data => x_msg_data
1774 );
1776
1777
1778 --------------------------------------------------------------------
1779 -- PROCEDURE
1780 -- Validate_Venue
1781 --
1782 --------------------------------------------------------------------
1783 PROCEDURE Validate_Venue (
1784 p_api_version IN NUMBER,
1785 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1786 p_commit IN VARCHAR2 := FND_API.g_false,
1787 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1788
1789 x_return_status OUT NOCOPY VARCHAR2,
1790 x_msg_count OUT NOCOPY NUMBER,
1791 x_msg_data OUT NOCOPY VARCHAR2,
1792
1793 p_venue_rec IN Venue_Rec_Type,
1794 p_object_type IN VARCHAR2
1795 )
1796 IS
1797 L_API_VERSION CONSTANT NUMBER := 1.0;
1798 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Venue';
1799 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1800
1801 l_return_status VARCHAR2(1);
1802 BEGIN
1803 --------------------- initialize -----------------------
1804 IF (AMS_DEBUG_HIGH_ON) THEN
1805
1806 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1807 END IF;
1808
1809 IF FND_API.to_boolean (p_init_msg_list) THEN
1810 FND_MSG_PUB.initialize;
1811 END IF;
1812
1813 IF NOT FND_API.compatible_api_call (
1814 l_api_version,
1815 p_api_version,
1816 l_api_name,
1817 g_pkg_name
1818 ) THEN
1819 RAISE FND_API.g_exc_unexpected_error;
1820 END IF;
1821
1822 x_return_status := FND_API.g_ret_sts_success;
1823
1824 ---------------------- validate ------------------------
1825 IF (AMS_DEBUG_HIGH_ON) THEN
1826
1827 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
1828 END IF;
1829
1830 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1831 Check_Venue_Items (
1832 p_venue_rec => p_venue_rec,
1833 p_object_type => p_object_type,
1834 p_validation_mode => JTF_PLSQL_API.g_create,
1835 x_return_status => l_return_status
1836 );
1837
1838 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1839 RAISE FND_API.g_exc_unexpected_error;
1840 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1841 RAISE FND_API.g_exc_error;
1842 END IF;
1843 END IF;
1844
1845 IF (AMS_DEBUG_HIGH_ON) THEN
1846
1847
1848
1849 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
1850
1851 END IF;
1852
1853 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1854 Check_Venue_Record (
1855 p_venue_rec => p_venue_rec,
1856 p_complete_rec => NULL,
1857 x_return_status => l_return_status
1858 );
1859
1860 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1861 RAISE FND_API.g_exc_unexpected_error;
1862 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1863 RAISE FND_API.g_exc_error;
1864 END IF;
1865 END IF;
1866
1867 -------------------- finish --------------------------
1868 FND_MSG_PUB.count_and_get (
1869 p_encoded => FND_API.g_false,
1870 p_count => x_msg_count,
1871 p_data => x_msg_data
1872 );
1873
1874 IF (AMS_DEBUG_HIGH_ON) THEN
1875
1876
1877
1878 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1879
1880 END IF;
1881
1882 EXCEPTION
1883 WHEN FND_API.g_exc_error THEN
1884 x_return_status := FND_API.g_ret_sts_error;
1885 FND_MSG_PUB.count_and_get (
1886 p_encoded => FND_API.g_false,
1887 p_count => x_msg_count,
1888 p_data => x_msg_data
1889 );
1890 WHEN FND_API.g_exc_unexpected_error THEN
1891 x_return_status := FND_API.g_ret_sts_unexp_error ;
1892 FND_MSG_PUB.count_and_get (
1893 p_encoded => FND_API.g_false,
1894 p_count => x_msg_count,
1895 p_data => x_msg_data
1896 );
1897 WHEN OTHERS THEN
1898 x_return_status := FND_API.g_ret_sts_unexp_error;
1899 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1900 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1901 END IF;
1902
1903 FND_MSG_PUB.count_and_get (
1904 p_encoded => FND_API.g_false,
1905 p_count => x_msg_count,
1906 p_data => x_msg_data
1907 );
1908 END Validate_Venue;
1909
1910 ---------------------------------------------------------------------
1911 -- PROCEDURE
1912 -- Check_Venue_Items
1913 -- HISTORY:
1914 -- 09 May 2003 dbiswas Added checks for length of the ceiling height and capacity
1915 ---------------------------------------------------------------------
1916 PROCEDURE Check_Venue_Items (
1917 p_venue_rec IN Venue_Rec_Type,
1918 p_object_type IN VARCHAR2,
1919 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1920 x_return_status OUT NOCOPY VARCHAR2
1921 )
1922 IS
1926 Check_Venue_Req_Items (
1923 BEGIN
1924 --
1925 -- Validate required items.
1927 p_venue_rec => p_venue_rec,
1928 p_object_type => p_object_type,
1929 x_return_status => x_return_status
1930 );
1931
1932 IF x_return_status <> FND_API.g_ret_sts_success THEN
1933 RETURN;
1934 END IF;
1935
1936 Check_Venue_FK_Items(
1937 p_venue_rec => p_venue_rec,
1938 p_object_type => p_object_type,
1939 x_return_status => x_return_status
1940 );
1941
1942 IF x_return_status <> FND_API.g_ret_sts_success THEN
1943 RETURN;
1944 END IF;
1945
1946 --
1947 -- Validate uniqueness.
1948 Check_Venue_UK_Items (
1949 p_venue_rec => p_venue_rec,
1950 p_object_type => p_object_type,
1951 p_validation_mode => p_validation_mode,
1952 x_return_status => x_return_status
1953 );
1954
1955 IF x_return_status <> FND_API.g_ret_sts_success THEN
1956 RETURN;
1957 END IF;
1958
1959 Check_Venue_Lookup_Items (
1960 p_venue_rec => p_venue_rec,
1961 x_return_status => x_return_status
1962 );
1963
1964 IF x_return_status <> FND_API.g_ret_sts_success THEN
1965 RETURN;
1966 END IF;
1967
1968 Check_Venue_Flag_Items(
1969 p_venue_rec => p_venue_rec,
1970 x_return_status => x_return_status
1971 );
1972
1973 IF(p_venue_rec.usage_cost < 0)
1974 THEN
1975 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1976 THEN
1977 Fnd_Message.set_name('AMS', 'AMS_USAGE_COST_NEG');
1978 Fnd_Msg_Pub.ADD;
1979 END IF;
1980 RAISE FND_API.g_exc_error;
1981 END IF;
1982
1983 IF(p_venue_rec.capacity < 0)
1984 THEN
1985 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1986 THEN
1987 Fnd_Message.set_name('AMS', 'AMS_CAPACITY_NEG');
1988 Fnd_Msg_Pub.ADD;
1989 END IF;
1990 RAISE FND_API.g_exc_error;
1991 END IF;
1992
1993 IF(p_venue_rec.ceiling_height < 0)
1994 THEN
1995 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1996 THEN
1997 Fnd_Message.set_name('AMS', 'AMS_CEILING_HEIGHT_NEG');
1998 Fnd_Msg_Pub.ADD;
1999 END IF;
2000 RAISE FND_API.g_exc_error;
2001 END IF;
2002
2003 /* dbiswas added the following two conditions to check that no more than 15 chars are inserted for
2004 ceiling height and capacity on May 9, 2003 for bug 2950429 */
2005
2006 IF(length(p_venue_rec.ceiling_height) > 15)
2007 THEN
2008 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2009 THEN
2010 Fnd_Message.set_name('AMS', 'AMS_OVERSIZE_CEILING_HEIGHT');
2011 Fnd_Msg_Pub.ADD;
2012 END IF;
2013 RAISE FND_API.g_exc_error;
2014 END IF;
2015
2016 IF(length(p_venue_rec.capacity) > 15)
2017 THEN
2018 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2019 THEN
2020 Fnd_Message.set_name('AMS', 'AMS_OVERSIZE_CAPACITY');
2021 Fnd_Msg_Pub.ADD;
2022 END IF;
2023 RAISE FND_API.g_exc_error;
2024 END IF;
2025
2026 /* end update by dbiswas on May 9, 2003 */
2027
2028 IF x_return_status <> FND_API.g_ret_sts_success THEN
2029 RETURN;
2030 END IF;
2031 END Check_Venue_Items;
2032
2033 ---------------------------------------------------------------------
2034 -- PROCEDURE
2035 -- Check_Venue_Record
2036 --
2037 -- PURPOSE
2038 -- Check the record level business rules.
2039 --
2040 -- PARAMETERS
2041 -- p_venue_rec: the record to be validated; may contain attributes
2042 -- as FND_API.g_miss_char/num/date
2043 -- p_complete_rec: the complete record after all "g_miss" items
2044 -- have been replaced by current database values
2045 ---------------------------------------------------------------------
2046 PROCEDURE Check_Venue_Record (
2047 p_venue_rec IN Venue_Rec_Type,
2048 p_complete_rec IN Venue_Rec_Type := NULL,
2049 x_return_status OUT NOCOPY VARCHAR2
2050 )
2051 IS
2052 BEGIN
2053 --
2054 -- Currently, no business rule for record
2055 -- level validation.
2056 x_return_status := FND_API.g_ret_sts_success;
2057 END Check_Venue_Record;
2058
2059 ---------------------------------------------------------------------
2060 -- PROCEDURE
2061 -- Init_Venue_Rec
2062 --
2063 ---------------------------------------------------------------------
2064 PROCEDURE Init_Venue_Rec (
2065 x_venue_rec OUT NOCOPY Venue_Rec_Type
2066 )
2067 IS
2068 BEGIN
2069 x_venue_rec.venue_id := FND_API.g_miss_num;
2070 x_venue_rec.custom_setup_id := FND_API.g_miss_num;
2071 x_venue_rec.last_update_date := FND_API.g_miss_date;
2072 x_venue_rec.last_updated_by := FND_API.g_miss_num;
2073 x_venue_rec.creation_date := FND_API.g_miss_date;
2074 x_venue_rec.created_by := FND_API.g_miss_num;
2075 x_venue_rec.last_update_login := FND_API.g_miss_num;
2079 x_venue_rec.internal_flag := FND_API.g_miss_char;
2076 x_venue_rec.object_version_number := FND_API.g_miss_num;
2077 x_venue_rec.venue_type_code := FND_API.g_miss_char;
2078 x_venue_rec.direct_phone_flag := FND_API.g_miss_char;
2080 x_venue_rec.enabled_flag := FND_API.g_miss_char;
2081 x_venue_rec.rating_code := FND_API.g_miss_char;
2082 x_venue_rec.telecom_code := FND_API.g_miss_char;
2083 x_venue_rec.capacity := FND_API.g_miss_num;
2084 x_venue_rec.area_size := FND_API.g_miss_num;
2085 x_venue_rec.area_size_uom_code := FND_API.g_miss_char;
2086 x_venue_rec.ceiling_height := FND_API.g_miss_num;
2087 x_venue_rec.ceiling_height_uom_code := FND_API.g_miss_char;
2088 x_venue_rec.usage_cost := FND_API.g_miss_num;
2089 x_venue_rec.usage_cost_uom_code := FND_API.g_miss_char;
2090 x_venue_rec.usage_cost_currency_code := FND_API.g_miss_char;
2091 x_venue_rec.parent_venue_id := FND_API.g_miss_num;
2092 x_venue_rec.location_id := FND_API.g_miss_num;
2093 x_venue_rec.directions := FND_API.g_miss_char;
2094 x_venue_rec.venue_code := FND_API.g_miss_char;
2095 x_venue_rec.object_type := FND_API.g_miss_char;
2096 x_venue_rec.party_id := FND_API.g_miss_num;
2097 x_venue_rec.attribute_category := FND_API.g_miss_char;
2098 x_venue_rec.attribute1 := FND_API.g_miss_char;
2099 x_venue_rec.attribute2 := FND_API.g_miss_char;
2100 x_venue_rec.attribute3 := FND_API.g_miss_char;
2101 x_venue_rec.attribute4 := FND_API.g_miss_char;
2102 x_venue_rec.attribute5 := FND_API.g_miss_char;
2103 x_venue_rec.attribute6 := FND_API.g_miss_char;
2104 x_venue_rec.attribute7 := FND_API.g_miss_char;
2105 x_venue_rec.attribute8 := FND_API.g_miss_char;
2106 x_venue_rec.attribute9 := FND_API.g_miss_char;
2107 x_venue_rec.attribute10 := FND_API.g_miss_char;
2108 x_venue_rec.attribute11 := FND_API.g_miss_char;
2109 x_venue_rec.attribute12 := FND_API.g_miss_char;
2110 x_venue_rec.attribute13 := FND_API.g_miss_char;
2111 x_venue_rec.attribute14 := FND_API.g_miss_char;
2112 x_venue_rec.attribute15 := FND_API.g_miss_char;
2113 x_venue_rec.venue_name := FND_API.g_miss_char;
2114 x_venue_rec.description := FND_API.g_miss_char;
2115 END Init_Venue_Rec;
2116
2117
2118
2119 ---------------------------------------------------------------------
2120 -- PROCEDURE
2121 -- Complete_Venue_Rec
2122 --
2123 ---------------------------------------------------------------------
2124 PROCEDURE Complete_Venue_Rec (
2125 p_venue_rec IN Venue_Rec_Type,
2126 x_complete_rec OUT NOCOPY Venue_Rec_Type
2127 )
2128 IS
2129 CURSOR c_venue (id_in IN NUMBER) IS
2130 SELECT *
2131 FROM ams_venues_vl
2132 WHERE venue_id = id_in;
2133
2134 CURSOR c_location(p_party_id IN NUMBER) IS
2135 SELECT address1, address2, city, state, country
2136 FROM hz_parties
2137 WHERE party_id = p_party_id;
2138 --
2139 -- This is the only exception for using %ROWTYPE.
2140 -- We are selecting from the VL view, which may
2141 -- have some denormalized columns as compared to
2142 -- the base tables.
2143 l_venue_rec c_venue%ROWTYPE;
2144 l_address1 VARCHAR2(240);
2145 l_address2 VARCHAR2(240);
2146 l_city VARCHAR2(60);
2147 l_state VARCHAR2(60);
2148 l_country VARCHAR2(60);
2149 BEGIN
2150 x_complete_rec := p_venue_rec;
2151
2152 OPEN c_venue(p_venue_rec.venue_id);
2153 FETCH c_venue INTO l_venue_rec;
2154 IF c_venue%NOTFOUND THEN
2155 CLOSE c_venue;
2156 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2157 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2158 FND_MSG_PUB.add;
2159 END IF;
2160 RAISE FND_API.g_exc_error;
2161 END IF;
2162 CLOSE c_venue;
2163
2164 IF l_venue_rec.party_id IS NOT NULL THEN
2165 OPEN c_location(l_venue_rec.party_id);
2166 FETCH c_location INTO l_address1, l_address2, l_city, l_state, l_country;
2167 IF c_location%NOTFOUND THEN
2168 CLOSE c_location;
2169 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2170 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2171 FND_MSG_PUB.add;
2172 END IF;
2173 RAISE FND_API.g_exc_error;
2174 END IF;
2175 CLOSE c_location;
2176 END IF;
2177
2178 --
2179 -- venue_type_code
2180 IF p_venue_rec.venue_type_code = FND_API.g_miss_char THEN
2181 x_complete_rec.venue_type_code := l_venue_rec.venue_type_code;
2182 END IF;
2183
2184 --
2185 -- custom_setup_id
2186 IF p_venue_rec.custom_setup_id = FND_API.g_miss_num THEN
2187 x_complete_rec.custom_setup_id := l_venue_rec.custom_setup_id;
2188 END IF;
2189
2190 --
2191 -- INTERNAL_FLAG
2192 IF p_venue_rec.internal_flag = FND_API.g_miss_char THEN
2193 x_complete_rec.internal_flag := l_venue_rec.internal_flag;
2194 END IF;
2195
2196 --
2197 -- DIRECT_PHONE_FLAG
2198 IF p_venue_rec.direct_phone_flag = FND_API.g_miss_char THEN
2199 x_complete_rec.direct_phone_flag := l_venue_rec.direct_phone_flag;
2200 END IF;
2201
2202 --
2203 -- ENABLED_FLAG
2204 IF p_venue_rec.enabled_flag = FND_API.g_miss_char THEN
2208 --
2205 x_complete_rec.enabled_flag := l_venue_rec.enabled_flag;
2206 END IF;
2207
2209 --RATING_CODE
2210 IF p_venue_rec.rating_code = FND_API.g_miss_char THEN
2211 x_complete_rec.rating_code := l_venue_rec.rating_code;
2212 END IF;
2213
2214 --
2215 --TELECOM_CODE
2216 IF p_venue_rec.telecom_code = FND_API.g_miss_char THEN
2217 x_complete_rec.telecom_code := l_venue_rec.telecom_code;
2218 END IF;
2219
2220 --
2221 --CAPACITY
2222 IF p_venue_rec.capacity = FND_API.g_miss_num THEN
2223 x_complete_rec.capacity := l_venue_rec.capacity;
2224 END IF;
2225
2226 --
2227 --AREA_SIZE
2228 IF p_venue_rec.area_size = FND_API.g_miss_num THEN
2229 x_complete_rec.area_size := l_venue_rec.area_size;
2230 END IF;
2231
2232 --
2233 --AREA_SIZE_UOM_CODE
2234 IF p_venue_rec.area_size_uom_code = FND_API.g_miss_char THEN
2235 x_complete_rec.area_size_uom_code := l_venue_rec.area_size_uom_code;
2236 END IF;
2237
2238 --
2239 -- CEILING_HEIGHT
2240 IF p_venue_rec.ceiling_height = FND_API.g_miss_num THEN
2241 x_complete_rec.ceiling_height := l_venue_rec.ceiling_height;
2242 END IF;
2243
2244 --
2245 --CEILING_HEIGHT_UOM_CODE
2246 IF p_venue_rec.ceiling_height_uom_code = FND_API.g_miss_char THEN
2247 x_complete_rec.ceiling_height_uom_code := l_venue_rec.ceiling_height_uom_code;
2248 END IF;
2249
2250 --
2251 --USAGE_COST
2252 IF p_venue_rec.usage_cost = FND_API.g_miss_num THEN
2253 x_complete_rec.usage_cost := l_venue_rec.usage_cost;
2254 END IF;
2255
2256 --
2257 --USAGE_COST_UOM_CODE
2258 IF p_venue_rec.usage_cost_uom_code = FND_API.g_miss_char THEN
2259 x_complete_rec.usage_cost_uom_code := l_venue_rec.usage_cost_uom_code;
2260 END IF;
2261 --
2262 --USAGE_COST_CURRENCY_CODE
2263 IF p_venue_rec.usage_cost_currency_code = FND_API.g_miss_char THEN
2264 x_complete_rec.usage_cost_currency_code := l_venue_rec.usage_cost_currency_code;
2265 END IF;
2266
2267 --
2268 --PARENT_VENUE_ID
2269 IF p_venue_rec.parent_venue_id = FND_API.g_miss_num THEN
2270 x_complete_rec.parent_venue_id := l_venue_rec.parent_venue_id;
2271 END IF;
2272
2273 --
2274 --DIRECTIONS
2275 IF p_venue_rec.directions = FND_API.g_miss_char THEN
2276 x_complete_rec.directions := l_venue_rec.directions;
2277 END IF;
2278
2279 --
2280 --VENUE_CODE
2281 IF p_venue_rec.venue_code = FND_API.g_miss_char THEN
2282 x_complete_rec.venue_code := l_venue_rec.venue_code;
2283 END IF;
2284
2285 --
2286 --OBJECT_TYPE
2287 IF p_venue_rec.object_type = FND_API.g_miss_char THEN
2288 x_complete_rec.object_type := l_venue_rec.object_type;
2289 END IF;
2290
2291 --
2292 -- ATTRIBUTE_CATEGORY
2293 IF p_venue_rec.attribute_category = FND_API.g_miss_char THEN
2294 x_complete_rec.attribute_category := l_venue_rec.attribute_category;
2295 END IF;
2296
2297 --
2298 -- ATTRIBUTE1
2299 IF p_venue_rec.attribute1 = FND_API.g_miss_char THEN
2300 x_complete_rec.attribute1 := l_venue_rec.attribute1;
2301 END IF;
2302
2303 --
2304 -- ATTRIBUTE2
2305 IF p_venue_rec.attribute2 = FND_API.g_miss_char THEN
2306 x_complete_rec.attribute2 := l_venue_rec.attribute2;
2307 END IF;
2308
2309 --
2310 -- ATTRIBUTE3
2311 IF p_venue_rec.attribute3 = FND_API.g_miss_char THEN
2312 x_complete_rec.attribute3 := l_venue_rec.attribute3;
2313 END IF;
2314
2315 --
2316 -- ATTRIBUTE4
2317 IF p_venue_rec.attribute4 = FND_API.g_miss_char THEN
2318 x_complete_rec.attribute4 := l_venue_rec.attribute4;
2319 END IF;
2320
2321 --
2322 -- ATTRIBUTE5
2323 IF p_venue_rec.attribute5 = FND_API.g_miss_char THEN
2324 x_complete_rec.attribute5 := l_venue_rec.attribute5;
2325 END IF;
2326
2327 --
2328 -- ATTRIBUTE6
2329 IF p_venue_rec.attribute6 = FND_API.g_miss_char THEN
2330 x_complete_rec.attribute6 := l_venue_rec.attribute6;
2331 END IF;
2332
2333 --
2334 -- ATTRIBUTE7
2335 IF p_venue_rec.attribute7 = FND_API.g_miss_char THEN
2336 x_complete_rec.attribute7 := l_venue_rec.attribute7;
2337 END IF;
2338
2339 --
2340 -- ATTRIBUTE8
2341 IF p_venue_rec.attribute8 = FND_API.g_miss_char THEN
2342 x_complete_rec.attribute8 := l_venue_rec.attribute8;
2343 END IF;
2344
2345 --
2346 -- ATTRIBUTE9
2347 IF p_venue_rec.attribute9 = FND_API.g_miss_char THEN
2348 x_complete_rec.attribute9 := l_venue_rec.attribute9;
2349 END IF;
2350
2351 --
2352 -- ATTRIBUTE10
2353 IF p_venue_rec.attribute10 = FND_API.g_miss_char THEN
2354 x_complete_rec.attribute10 := l_venue_rec.attribute10;
2355 END IF;
2356
2357 --
2358 -- ATTRIBUTE11
2359 IF p_venue_rec.attribute11 = FND_API.g_miss_char THEN
2360 x_complete_rec.attribute11 := l_venue_rec.attribute11;
2361 END IF;
2362
2363 --
2364 -- ATTRIBUTE12
2365 IF p_venue_rec.attribute12 = FND_API.g_miss_char THEN
2366 x_complete_rec.attribute12 := l_venue_rec.attribute12;
2367 END IF;
2368
2372 x_complete_rec.attribute13 := l_venue_rec.attribute13;
2369 --
2370 -- ATTRIBUTE13
2371 IF p_venue_rec.attribute13 = FND_API.g_miss_char THEN
2373 END IF;
2374
2375 --
2376 -- ATTRIBUTE14
2377 IF p_venue_rec.attribute14 = FND_API.g_miss_char THEN
2378 x_complete_rec.attribute14 := l_venue_rec.attribute14;
2379 END IF;
2380
2381 --
2382 -- ATTRIBUTE15
2383 IF p_venue_rec.attribute15 = FND_API.g_miss_char THEN
2384 x_complete_rec.attribute15 := l_venue_rec.attribute15;
2385 END IF;
2386
2387 --
2388 -- VENUE_NAME
2389 IF p_venue_rec.venue_name = FND_API.g_miss_char THEN
2390 x_complete_rec.venue_name := l_venue_rec.venue_name;
2391 END IF;
2392
2393 --
2394 -- DESCRIPTION
2395 IF p_venue_rec.description = FND_API.g_miss_char THEN
2396 x_complete_rec.description := l_venue_rec.description;
2397 END IF;
2398
2399 -- modified sugupta 07/10/2000 added completion for city, state, country and address
2400 IF p_venue_rec.address1 = FND_API.g_miss_char THEN
2401 x_complete_rec.address1 := l_address1;
2402 END IF;
2403 IF p_venue_rec.address2 = FND_API.g_miss_char THEN
2404 x_complete_rec.address2 := l_address2;
2405 END IF;
2406 IF p_venue_rec.city = FND_API.g_miss_char THEN
2407 x_complete_rec.city := l_city;
2408 END IF;
2409 IF p_venue_rec.state = FND_API.g_miss_char THEN
2410 x_complete_rec.state := l_state;
2411 END IF;
2412 IF p_venue_rec.country = FND_API.g_miss_char THEN
2413 x_complete_rec.country := l_country;
2414 END IF;
2415
2416 -- GDEODHAR : Sept. 29, 2000. Noticed that the complete rec does not complete
2417 -- the location id. This causes the update of location id with g_miss_num and
2418 -- gives rise to bugs. Added the following :
2419
2420 --
2421 --LOCATION_ID
2422 IF p_venue_rec.location_id = FND_API.g_miss_num THEN
2423 x_complete_rec.location_id := l_venue_rec.location_id;
2424 END IF;
2425
2426 --
2427 -- PARTY_ID
2428 IF p_venue_rec.party_id = FND_API.g_miss_num THEN
2429 x_complete_rec.party_id := l_venue_rec.party_id;
2430 END IF;
2431
2432 END Complete_Venue_Rec;
2433
2434
2435 -- Check_Venue_Req_Items
2436 PROCEDURE Check_Venue_Req_Items (
2437 p_venue_rec IN Venue_Rec_Type,
2438 p_object_type IN VARCHAR2,
2439 x_return_status OUT NOCOPY VARCHAR2
2440 )
2441 IS
2442 BEGIN
2443
2444 -- venue_type_code
2445 IF ( (p_object_type <> 'ROOM')
2446 AND (p_venue_rec.venue_type_code IS NULL)
2447 )
2448 THEN
2449 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2450 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_NO_VENUE_TYPE');
2451 FND_MSG_PUB.add;
2452 END IF;
2453 x_return_status := FND_API.g_ret_sts_error;
2454 RETURN;
2455 END IF;
2456
2457 -- VENUE_NAME
2458 IF ( (p_object_type = 'ROOM')
2459 AND (p_venue_rec.venue_name IS NULL)
2460 )
2461 THEN
2462 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2463 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_NO_VENUE_NAME');
2464 FND_MSG_PUB.add;
2465 END IF;
2466
2467 x_return_status := FND_API.g_ret_sts_error;
2468 RETURN;
2469 END IF;
2470
2471 -- PARENT_VENUE_ID (For Rooms Only)
2472 IF ( (p_object_type = 'ROOM')
2473 AND (p_venue_rec.parent_venue_id IS NULL)
2474 )
2475 THEN
2476 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2477 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_NO_PARENT_ID');
2478 FND_MSG_PUB.add;
2479 END IF;
2480
2481 x_return_status := FND_API.g_ret_sts_error;
2482 RETURN;
2483 END IF;
2484
2485
2486 END Check_Venue_Req_Items;
2487
2488 -- Check_Venue_UK_Items
2489 PROCEDURE Check_Venue_UK_Items (
2490 p_venue_rec IN Venue_Rec_Type,
2491 p_object_type IN VARCHAR2,
2492 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2493 x_return_status OUT NOCOPY VARCHAR2
2494 )
2495 IS
2496 l_valid_flag VARCHAR2(1);
2497 l_dummy NUMBER;
2498 /*
2499 cursor c_vnu_name(vnu_name_in IN VARCHAR2) IS
2500 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
2501 where VENUE_NAME = vnu_name_in);
2502 */
2503 cursor c_room_name(room_name_in IN VARCHAR2, venue_id_in IN NUMBER) IS
2504 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
2505 where VENUE_NAME = room_name_in and parent_venue_id = venue_id_in);
2506 /*
2507 cursor c_vnu_name_up(vnu_name_in IN VARCHAR2, vnu_id_in in NUMBER) IS
2508 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
2509 where VENUE_NAME = vnu_name_in
2510 and VENUE_ID = vnu_id_in);
2511 */
2512 BEGIN
2513 x_return_status := FND_API.g_ret_sts_success;
2514
2515 -- VENUE_ID
2516 -- For Create_Venue, when ID is passed in, we need to
2517 -- check if this ID is unique.
2518 IF p_validation_mode = JTF_PLSQL_API.g_create
2519 AND p_venue_rec.venue_id IS NOT NULL
2520 THEN
2521 IF AMS_Utility_PVT.check_uniqueness(
2522 'ams_venues_vl',
2523 'venue_id = ' || p_venue_rec.venue_id
2524 ) = FND_API.g_false
2525 THEN
2526 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2527 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_DUP_VENUE_ID');
2528 FND_MSG_PUB.add;
2529
2530 END IF;
2531 x_return_status := FND_API.g_ret_sts_error;
2532
2533 RETURN;
2534 END IF;
2535 END IF;
2536
2537 -- VENUE_NAME
2538 /*
2539 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2540 open c_vnu_name_crt(p_venue_rec.venue_name);
2541 fetch c_vnu_name_crt into l_dummy;
2542 close c_vnu_name_crt;
2543 bug # 1490374
2544 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
2545 'ams_venues_vl',
2546 'venue_name = ''' || p_venue_rec.venue_name || ''''
2547 );
2548
2549 ELSE
2550 open c_vnu_name_up(p_venue_rec.venue_name,p_venue_rec.venue_id);
2551 fetch c_vnu_name_up into l_dummy;
2552 close c_vnu_name_up;
2553 bug # 1490374
2554 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
2555 'ams_venues_vl',
2556 'venue_name = ''' || p_venue_rec.venue_name ||
2557 ''' AND venue_id <> ' || p_venue_rec.venue_id
2558 );
2559
2560 END IF;
2561 */
2562
2563 /* FIX FOR BUG #1625651 DO THE FOLLOWING BLOCK FOR CREATE */
2564 IF ( (p_validation_mode = JTF_PLSQL_API.g_create OR p_validation_mode = JTF_PLSQL_API.g_update )
2565 AND (p_object_type = 'ROOM')
2566 )
2567 THEN
2568
2569 open c_room_name(p_venue_rec.venue_name, p_venue_rec.parent_venue_id);
2570 fetch c_room_name into l_dummy;
2571 close c_room_name;
2572 /*
2573 open c_vnu_name(p_venue_rec.venue_name);
2574 fetch c_vnu_name into l_dummy;
2575 close c_vnu_name;
2576 */
2577 IF l_dummy = 1 THEN
2578 --IF l_valid_flag = FND_API.g_false THEN bug # 1490374
2579
2580 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2581 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_DUP_NAME');
2582 FND_MSG_PUB.add;
2583
2584
2585 END IF;
2586 x_return_status := FND_API.g_ret_sts_error;
2587
2588 RETURN;
2589 END IF;
2590 END IF;
2591
2592 END Check_Venue_UK_Items;
2593
2594 -- Check_Venue_FK_Items
2595 PROCEDURE Check_Venue_FK_Items (
2596 p_venue_rec IN Venue_Rec_Type,
2597 p_object_type IN VARCHAR2,
2598 x_return_status OUT NOCOPY VARCHAR2
2599 )
2600 IS
2601 --
2602 -- Check for the Locations FK
2603 BEGIN
2604 x_return_status := FND_API.g_ret_sts_success;
2605
2606 ----------------------- venue party_id ------------------------
2607 IF nvl(p_object_type, 'VENU') <> 'ROOM' THEN
2608 IF p_venue_rec.location_id <> FND_API.g_miss_num THEN
2609 IF AMS_Utility_PVT.check_fk_exists(
2610 'hz_parties',
2611 'party_id',
2612 p_venue_rec.party_id
2613 ) = FND_API.g_false
2614 THEN
2615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2616 THEN
2617 FND_MESSAGE.set_name('AMS', 'AMS_VENUE_BAD_LOCATION_ID');
2618 FND_MSG_PUB.add;
2619 END IF;
2620
2621 x_return_status := FND_API.g_ret_sts_error;
2622 RETURN;
2623 END IF;
2624 END IF;
2625 ELSE
2626 IF p_venue_rec.parent_venue_id <> FND_API.g_miss_num THEN
2627 IF AMS_Utility_PVT.check_fk_exists(
2628 'ams_venues_b',
2629 'venue_id',
2630 p_venue_rec.parent_venue_id
2631 ) = FND_API.g_false
2632 THEN
2633 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2634 THEN
2635 FND_MESSAGE.set_name('AMS', 'AMS_ROOM_BAD_PARENT_VENUE');
2636 FND_MSG_PUB.add;
2637 END IF;
2638
2639 x_return_status := FND_API.g_ret_sts_error;
2640 RETURN;
2641 END IF;
2642 END IF;
2643
2644 END IF;
2645
2646 /*
2647 --------------------- currency_code_fc ------------------------
2648 IF p_evh_rec.currency_code_fc <> FND_API.g_miss_char
2649 AND p_evh_rec.currency_code_fc IS NOT NULL
2650 THEN
2651 IF AMS_Utility_PVT.check_fk_exists(
2652 'fnd_currencies_vl',
2653 'currency_code',
2654 p_evh_rec.currency_code_fc,
2655 AMS_Utility_PVT.g_varchar2
2656 ) = FND_API.g_false
2657 THEN
2658 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2659 THEN
2660 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_CURRENCY_CODE_FC');
2661 FND_MSG_PUB.add;
2662 END IF;
2663 x_return_status := FND_API.g_ret_sts_error;
2664 RETURN;
2665 END IF;
2666 END IF;
2667
2668 */
2669 END Check_Venue_FK_Items;
2670
2671 -- Check_Venue_Lookup_Items
2672 PROCEDURE Check_Venue_Lookup_Items (
2673 p_venue_rec IN Venue_Rec_Type,
2674 x_return_status OUT NOCOPY VARCHAR2
2675 )
2676 IS
2677 L_VENUE_TYPE CONSTANT VARCHAR(30) := 'AMS_VENUE_TYPE';
2678 L_AREA_SIZE_UOM_CODE CONSTANT VARCHAR(30) := 'AMS_VENUE_AREA_SIZE_UOM_CODE';
2679 L_USAGE_COST_CURRENCY_CODE CONSTANT VARCHAR(30) := 'AMS_USAGE_COST_CURRENCY_CODE';
2680 L_CEILING_HEIGHT_UOM_CODE CONSTANT VARCHAR(30) := 'AMS_CEILING_HEIGHT_UOM_CODE';
2681 BEGIN
2682 x_return_status := FND_API.g_ret_sts_success;
2683
2684 /* --
2685 -- VENUE_TYPE_CODE
2686 IF p_venue_rec.venue_type_code <> FND_API.g_miss_char THEN
2687 IF AMS_Utility_PVT.check_lookup_exists (
2688 p_lookup_type => L_VENUE_TYPE,
2689 p_lookup_code => p_venue_rec.venue_type_code
2690 ) = FND_API.g_false
2691 THEN
2692 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2693 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_VENUE_TYPE');
2694 FND_MSG_PUB.add;
2695 END IF;
2696
2697 x_return_status := FND_API.g_ret_sts_error;
2698 RETURN;
2699 END IF;
2700 END IF;
2701 */
2702
2703 /*
2704 --
2705 -- AREA_SIZE_UOM_CODE
2706 IF p_venue_rec.area_size_uom_code <> FND_API.g_miss_char THEN
2707 IF AMS_Utility_PVT.check_lookup_exists (
2708 p_lookup_type => L_AREA_SIZE_UOM_CODE,
2709 p_lookup_code => p_venue_rec.area_size_uom_code
2710 ) = FND_API.g_false
2711 THEN
2712 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2713 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_AREA_SIZE_UOM_CODE');
2714 FND_MSG_PUB.add;
2715 END IF;
2716
2717 x_return_status := FND_API.g_ret_sts_error;
2718 RETURN;
2719 END IF;
2720 END IF;
2721
2722
2723
2724 --
2725 -- CEILING_HEIGHT_UOM_CODE
2726 IF p_venue_rec.ceiling_height_uom_code <> FND_API.g_miss_char THEN
2727 IF AMS_Utility_PVT.check_lookup_exists (
2728 p_lookup_type => L_CEILING_HEIGHT_UOM_CODE,
2729 p_lookup_code => p_venue_rec.ceiling_height_uom_code
2730 ) = FND_API.g_false
2731 THEN
2732 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2733 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_CEILING_HEIGHT_UOM_CODE');
2734 FND_MSG_PUB.add;
2735 END IF;
2736
2737 x_return_status := FND_API.g_ret_sts_error;
2738 RETURN;
2739 END IF;
2740 END IF;
2741
2742 --
2743 -- USAGE_COST_CURRENCY_CODE
2744 IF p_venue_rec.usage_cost_currency_code <> FND_API.g_miss_char THEN
2745 IF AMS_Utility_PVT.check_lookup_exists (
2746 p_lookup_type => L_USAGE_COST_CURRENCY_CODE,
2747 p_lookup_code => p_venue_rec.usage_cost_currency_code
2748 ) = FND_API.g_false
2749 THEN
2750 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2751 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_USAGE_COST_CURRENCY_CODE');
2752 FND_MSG_PUB.add;
2753 END IF;
2754
2755 x_return_status := FND_API.g_ret_sts_error;
2756 RETURN;
2757 END IF;
2758 END IF;
2759
2760 */
2761
2762 END Check_Venue_Lookup_Items;
2763
2764
2765
2766 -- Check_Venue_Flag_Items
2767 PROCEDURE Check_Venue_Flag_Items (
2768 p_venue_rec IN Venue_Rec_Type,
2769 x_return_status OUT NOCOPY VARCHAR2
2770 )
2771 IS
2772 BEGIN
2773 x_return_status := FND_API.g_ret_sts_success;
2774
2775 -- INTERNAL_FLAG
2776 IF p_venue_rec.internal_flag <> FND_API.g_miss_char AND p_venue_rec.internal_flag IS NOT NULL THEN
2777 IF AMS_Utility_PVT.is_Y_or_N (p_venue_rec.internal_flag) = FND_API.g_false THEN
2778 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2779 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_INTERNAL_FLAG');
2780 FND_MSG_PUB.add;
2781 END IF;
2782
2783 x_return_status := FND_API.g_ret_sts_error;
2784 RETURN;
2785 END IF;
2786 END IF;
2787
2788 -- ENABLED_FLAG
2789 IF p_venue_rec.enabled_flag <> FND_API.g_miss_char AND p_venue_rec.enabled_flag IS NOT NULL THEN
2790 IF AMS_Utility_PVT.is_Y_or_N (p_venue_rec.enabled_flag) = FND_API.g_false THEN
2791 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2792 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_ENABLED_FLAG');
2793 FND_MSG_PUB.add;
2794 END IF;
2795
2796 x_return_status := FND_API.g_ret_sts_error;
2797 RETURN;
2798 END IF;
2799 END IF;
2800
2801 -- DIRECT_PHONE_FLAG
2802 IF p_venue_rec.direct_phone_flag <> FND_API.g_miss_char AND p_venue_rec.direct_phone_flag IS NOT NULL THEN
2803 IF AMS_Utility_PVT.is_Y_or_N (p_venue_rec.direct_phone_flag) = FND_API.g_false THEN
2804 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2805 FND_MESSAGE.set_name ('AMS', 'AMS_VENUE_BAD_DIRECT_PH');
2806 FND_MSG_PUB.add;
2807 END IF;
2808
2809 x_return_status := FND_API.g_ret_sts_error;
2810 RETURN;
2811 END IF;
2812 END IF;
2813
2814 END Check_Venue_Flag_Items;
2815
2816 END AMS_Venue_PVT;