[Home] [Help]
PACKAGE BODY: APPS.AMS_REGISTRANTS_PUB
Source
1 PACKAGE BODY AMS_Registrants_PUB AS
2 /* $Header: amspevrb.pls 115.15 2004/02/18 10:45:53 anchaudh ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30):='AMS_Registrants_PUB';
4 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvevrb.pls';
5 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6
7 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
8 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
9 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10
11 PROCEDURE Get_Party_Detail_Reg_Rec( p_reg_det_rec IN RegistrationDet
12 , x_reg_party_rec OUT NOCOPY AMS_Registrants_PVT.party_detail_rec_type
13 );
14
15 PROCEDURE Get_Party_Detail_Att_Rec( p_reg_det_rec IN RegistrationDet
16 , x_att_party_rec OUT NOCOPY AMS_Registrants_PVT.party_detail_rec_type
17 );
18
19 --=============================================================================
20 -- Start of Comment
21 --=============================================================================
22 --API Name
23 -- Write_log
24 --Type
25 -- Public
26 --Purpose
27 -- Used to write logs for this API
28 --Author
29 -- Dhirendra Singh
30 --=============================================================================
31 --
32 PROCEDURE Write_log (p_api_name IN VARCHAR2,
33 p_log_message IN VARCHAR2)
34 IS
35 l_api_name VARCHAR(30);
36 l_log_msg VARCHAR(2000);
37 BEGIN
38 l_api_name := p_api_name;
39 l_log_msg := p_log_message;
40
41 IF (AMS_DEBUG_HIGH_ON)
42 THEN AMS_Utility_PVT.debug_message(p_log_message);
43 END IF;
44 AMS_Utility_PVT.debug_message(
45 p_log_level => g_log_level,
46 p_module_name => G_FILE_NAME ||'.'||g_pkg_name||'.'||l_api_name||'.',
47 p_text => p_log_message
48 );
49
50 --EXCEPTION
51 -- currently no exception handled
52
53 END Write_log;
54
55
56
57 --==============================================================================
58 -- Start of Comments
59 --==============================================================================
60 --API Name
61 -- Register
62 --Type
63 -- Public
64 --Pre-Req
65 --
66 --Parameters
67 --
68 --IN
69 -- p_api_version_number IN NUMBER Required
70 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
71 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
72 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
73 -- p_reg_det_rec IN RegistrationDet Required
74 --
75 --OUT
76 -- x_return_status OUT VARCHAR2
77 -- x_msg_count OUT NUMBER
78 -- x_msg_data OUT VARCHAR2
79 --Version : Current version 1.0
80 --
81 --End of Comments
82 --==============================================================================
83 --
84
85 PROCEDURE Register(
86 p_api_version_number IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94
95 p_reg_det_rec IN RegistrationDet,
96 p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE,
97 p_owner_user_id IN NUMBER,
98 p_application_id IN NUMBER,
99 x_confirm_code OUT NOCOPY VARCHAR2
100 )
101
102 IS
103
104 L_API_NAME CONSTANT VARCHAR2(30) := 'Register';
105 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
106 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
107
108 l_reg_party_id NUMBER := p_reg_det_rec.reg_party_id;
109 l_reg_contact_id NUMBER := p_reg_det_rec.reg_contact_id;
110 l_att_party_id NUMBER := p_reg_det_rec.att_party_id;
111 l_att_contact_id NUMBER := p_reg_det_rec.att_contact_id;
112
113 l_reg_rec AMS_Registrants_PVT.party_detail_rec_type;
114 l_att_rec AMS_Registrants_PVT.party_detail_rec_type;
115
116 l_evt_regs_rec AMS_EvtRegs_PVT.evt_regs_Rec_Type;
117 l_event_registration_id NUMBER;
118 l_event_id NUMBER;
119 l_status_id NUMBER;
120 l_object_version_number NUMBER;
121 l_system_status_code VARCHAR2(30) := 'REGISTERED';
122 l_confirmation_code VARCHAR2(30) := p_reg_det_rec.confirmation_code;
123 l_cancellation_code VARCHAR2(30);
124 l_waitlisted_flag VARCHAR2(1) := p_reg_det_rec.waitlisted_flag;
125 l_attended_flag VARCHAR2(1) := p_reg_det_rec.attendance_flag;
126
127 l_return_status VARCHAR2(1);
128
129 -- soagrawa 03-feb-2003
130 l_update_reg_rec VARCHAR2(1);
131
132 --dbiswas 13-Mar-2003
133 l_dummy NUMBER;
134
135 -- soagrawa added the following on 24-apr-2003 for bug 2863012
136 l_reg_status VARCHAR2(30);
137
138 Cursor c_event_registration_id_exists( p_reg_party_id NUMBER
139 , p_reg_contact_id NUMBER
140 , p_att_party_id NUMBER
141 , p_att_contact_id NUMBER
142 , p_event_offer_id NUMBER
143 )
144 IS
145 select event_registration_id,
146 object_version_number,
147 confirmation_code,
148 system_status_code
149 from ams_event_registrations
150 where REGISTRANT_PARTY_ID = p_reg_party_id
151 and REGISTRANT_CONTACT_ID = p_reg_contact_id
152 and ATTENDANT_PARTY_ID = p_att_party_id
153 and ATTENDANT_CONTACT_ID = p_att_contact_id
154 and EVENT_OFFER_ID = p_event_offer_id
155 order by event_registration_id desc;
156
157 Cursor c_event_reg_id_conf_code(p_confirmation_code VARCHAR2) Is
158 select event_registration_id,
159 object_version_number,
160 REGISTRANT_PARTY_ID,
161 REGISTRANT_CONTACT_ID,
162 ATTENDANT_PARTY_ID,
163 ATTENDANT_CONTACT_ID,
164 system_status_code
165 from ams_event_registrations
166 where confirmation_code = p_confirmation_code;
167
168 Cursor c_find_status_id(l_system_status_code VARCHAR2) is
169 select user_status_id
170 from ams_user_statuses_vl
171 where system_status_type = 'AMS_EVENT_REG_STATUS'
172 and system_status_code = l_system_status_code
173 and default_flag = 'Y';
174
175 -- dbiswas added the following cursor for bug 2839226
176 Cursor c_contact_id_exists(p_reg_contact_id NUMBER ) IS
177 SELECT 1
178 FROM hz_parties
179 WHERE party_id = p_reg_contact_id;
180
181 -- soagrawa added the following 24-apr-2003 cursor for bug 2863012
182 Cursor c_get_reg_status(p_reg_id NUMBER ) IS
183 select system_status_code
184 from ams_event_registrations
185 where event_registration_id = p_reg_id;
186
187 -- soagrawa 09-jun-2003 added to fix bug# 2997372
188 CURSOR c_party_Type (p_party_id NUMBER) IS
189 select party_type
190 from hz_parties
191 where party_id = p_party_id;
192
193 CURSOR c_rel_det (p_party_id NUMBER) IS
194 select object_id
195 from hz_relationships
196 where party_id = p_party_id
197 and object_type = 'ORGANIZATION';
198
199 l_party_type VARCHAR2(30);
200 -- end soagrawa 09-jun-2003
201
202 -- soagrawa 09-jun-2003 added to fix bug# 2997411
203 CURSOR c_rel_validate (p_party_id NUMBER, p_org_id NUMBER) IS
204 SELECT 1
205 From hz_relationships
206 where party_id = p_party_id
207 and object_type = 'ORGANIZATION'
208 and object_id = p_org_id;
209
210 l_dummy2 NUMBER;
211 -- end soagrawa 09-jun-2003
212
213
214
215 BEGIN
216 -- Standard Start of API savepoint
217 SAVEPOINT Register_Registrants_PUB;
218
219
220 -- Standard call to check for call compatibility.
221 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
222 p_api_version_number,
223 l_api_name,
224 G_PKG_NAME)
225 THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 -- Initialize message list if p_init_msg_list is set to TRUE.
230 IF FND_API.to_Boolean( p_init_msg_list )
231 THEN
232 FND_MSG_PUB.initialize;
233 END IF;
234
235 /*
236 IF (AMS_DEBUG_HIGH_ON) THEN
237 AMS_Utility_PVT.debug_message(l_full_name || ': start');
238 END IF ;*/
239 Write_log(L_API_NAME, l_full_name || ': start');
240
241 l_return_status := FND_API.g_ret_sts_success;
242
243 IF (p_reg_det_rec.event_source_code IS NULL)
244 THEN
245 AMS_Utility_PVT.Error_Message('AMS_EVEO_NO_SOURCE_CODE');
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 ELSE
248 l_event_id := AMS_Registrants_PVT.Get_Event_Det(p_reg_det_rec.event_source_code);
249 END IF;
250
251 IF (l_confirmation_code is not null)
252 THEN
253 Open c_event_reg_id_conf_code(l_confirmation_code);
254 Fetch c_event_reg_id_conf_code
255 Into l_event_registration_id,
256 l_object_version_number,
257 l_reg_party_id,
258 l_reg_contact_id,
259 l_att_party_id,
260 l_att_contact_id,
261 l_system_status_code;
262 Close c_event_reg_id_conf_code;
263 END IF;
264
265
266 IF (l_event_registration_id IS NULL)
267 THEN
268 -- Registrant Party Id
269 -- soagrawa 09-jun-2003 bug# 2997411
270 IF l_reg_contact_id IS NOT NULL
271 AND l_reg_party_id IS NOT null
272 AND l_reg_contact_id <> l_reg_party_id
273 THEN
274 -- B2B
275 l_dummy2 := 0;
276
277 OPEN c_rel_validate(l_reg_contact_id, l_reg_party_id);
278 FETCH c_rel_validate INTO l_dummy2;
279 CLOSE c_rel_validate;
280
281 IF l_dummy2 IS NULL OR l_dummy2 = 0
282 THEN
283 -- Throw an Error
284 AMS_Utility_PVT.error_message('AMS_REG_B2B_IMP_TIP');
285 RAISE FND_API.g_exc_error;
286 END IF;
287 END IF;
288 -- soagrawa end 09-jun-2003 bug# 2997411
289
290
291 -- bug 2839226
292 IF (l_reg_contact_id IS NOT NULL)
293 THEN
294 OPEN c_contact_id_exists(l_reg_contact_id);
295 FETCH c_contact_id_exists
296 INTO l_dummy;
297
298 CLOSE c_contact_id_exists;
299 IF l_dummy is null THEN
300 AMS_Utility_PVT.Error_Message('AMS_INVALID_REG_CONTACT_ID');
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303
304 ELSE
305 Get_Party_Detail_Reg_Rec( p_reg_det_rec => p_reg_det_rec
306 , x_reg_party_rec => l_reg_rec
307 );
308 AMS_Registrants_PVT.get_party_id( p_api_version => 1.0
309 , p_commit => FND_API.g_false
310 , p_validation_level => FND_API.g_valid_level_full
311 , p_rec => l_reg_rec
312 , x_return_status => l_return_status
313 , x_msg_count => x_msg_count
314 , x_msg_data => x_msg_data
315 , x_new_party_id => l_reg_contact_id
316 , x_new_org_party_id => l_reg_party_id
317 );
318 IF ( (l_reg_contact_id is null)
319 OR (l_return_status <> FND_API.g_ret_sts_success)
320 )
321 THEN
322 -- Throw an Error
323 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_PARTY');
324 RAISE FND_API.g_exc_error;
325 END IF; -- l_reg_contact_id error
326 END IF; -- registrant ids
327 -- What do we do here? How do we get the ORG id if it exists?
328
329 -- soagrawa 09-jun-2003 modified to fix bug# 2997372
330 --l_reg_party_id := nvl(l_reg_party_id, l_reg_contact_id);
331 IF l_reg_party_id IS NULL
332 THEN
333 OPEN c_party_Type(l_reg_contact_id);
334 FETCH c_party_Type INTO l_party_type;
335 CLOSE c_party_Type;
336
337 IF l_party_type = 'PARTY_RELATIONSHIP'
338 THEN
339 -- B2B
340 OPEN c_rel_det(l_reg_contact_id);
341 FETCH c_rel_det INTO l_reg_party_id;
342 CLOSE c_rel_det;
343 ELSE
344 -- B2C
345 l_reg_party_id := l_reg_contact_id;
346 END IF;
347 END IF;
348 -- end soagrawa 09-jun-2003
349
350
351 -- Attendant Party Id
352 -- bug 2839226
353
354 -- soagrawa 09-jun-2003 bug# 2997411
355 IF l_att_contact_id IS NOT NULL
356 AND l_att_party_id IS NOT null
357 AND l_att_contact_id <> l_att_party_id
358 THEN
359 -- B2B
360
361 l_dummy2 := 0;
362
363 OPEN c_rel_validate(l_att_contact_id, l_att_party_id);
364 FETCH c_rel_validate INTO l_dummy2;
365 CLOSE c_rel_validate;
366
367 IF l_dummy2 IS NULL OR l_dummy2 = 0
368 THEN
369 -- Throw an Error
370 AMS_Utility_PVT.error_message('AMS_ATT_B2B_IMP_TIP');
371 RAISE FND_API.g_exc_error;
372 END IF;
373 END IF;
374 -- soagrawa end 09-jun-2003 bug# 2997411
375
376 IF (l_att_contact_id is not null)
377 THEN
378 -- reset l_dummy
379 l_dummy := NULL;
380 OPEN c_contact_id_exists(l_att_contact_id);
381 FETCH c_contact_id_exists
382 INTO l_dummy;
383
384 CLOSE c_contact_id_exists;
385 IF l_dummy is null THEN
386 AMS_Utility_PVT.Error_Message('AMS_INVALID_ATT_CONTACT_ID');
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389
390 -- end bug 2839226
391
392 ELSE
393 -- Following code is modified by ptendulk for better error handling.
394 -- check here if the first name, last name and email address of the attendant is sent,
395 -- if not sent , then use the registrant id as attendant. This way in import when
396 -- user maps only registrant, the attendant will be picked up from attendant.
397
398 IF (p_reg_det_rec.att_first_name IS NOT NULL AND
399 p_reg_det_rec.att_last_name IS NOT NULL AND
400 p_reg_det_rec.att_email_address IS NOT NULL )
401 THEN
402
403 Get_Party_Detail_Att_Rec( p_reg_det_rec => p_reg_det_rec
404 , x_att_party_rec => l_att_rec
405 );
406 AMS_Registrants_PVT.get_party_id( p_api_version => 1.0
407 , p_commit => FND_API.g_false
408 , p_validation_level => FND_API.g_valid_level_full
409 , p_rec => l_att_rec
410 , x_return_status => l_return_status
411 , x_msg_count => x_msg_count
412 , x_msg_data => x_msg_data
413 , x_new_party_id => l_att_contact_id
414 , x_new_org_party_id => l_att_party_id
415 );
416 IF ( (l_att_contact_id is null)
417 OR (l_return_status <> FND_API.g_ret_sts_success)
418 )
419 THEN
420 -- Throw an Error
421 AMS_Utility_PVT.error_message('AMS_EVT_ATT_NO_PARTY');
422 RAISE FND_API.g_exc_error;
423 END IF; -- l_reg_contact_id error
424 --IF ( (l_att_contact_id is null)
425 -- OR (l_return_status <> FND_API.g_ret_sts_success)
426 -- )
427 --THEN
428 -- l_att_contact_id := l_reg_contact_id;
429 -- l_att_party_id := l_reg_party_id;
430 --END IF; -- second attendant contact id
431 ELSE
432 l_att_contact_id := l_reg_contact_id;
433 l_att_party_id := l_reg_party_id;
434 END IF;
435 END IF; -- attendant ids
436 -- What do we do here? How do we get the ORG id if it exists?
437
438 -- soagrawa 09-jun-2003 modified to fix bug# 2997372
439 --l_att_party_id := nvl(l_att_party_id, l_att_contact_id);
440 IF l_att_party_id IS NULL
441 THEN
442 OPEN c_party_Type(l_att_contact_id);
443 FETCH c_party_Type INTO l_party_type;
444 CLOSE c_party_Type;
445
446 IF l_party_type = 'PARTY_RELATIONSHIP'
447 THEN
448 -- B2B
449 OPEN c_rel_det(l_att_contact_id);
450 FETCH c_rel_det INTO l_att_party_id;
451 CLOSE c_rel_det;
452 ELSE
453 -- B2C
454 l_att_party_id := l_att_contact_id;
455 END IF;
456 END IF;
457 -- end soagrawa 09-jun-2003
458
459
460 -- Try to see if event registration id exists
461 Open c_event_registration_id_exists( l_reg_party_id
462 , l_reg_contact_id
463 , l_att_party_id
464 , l_att_contact_id
465 , l_event_id
466 );
467 Fetch c_event_registration_id_exists
468 Into l_event_registration_id,
469 l_object_version_number,
470 l_confirmation_code,
471 l_system_status_code;
472 Close c_event_registration_id_exists;
473 /**
474 IF (AMS_DEBUG_HIGH_ON) THEN
475 AMS_Utility_PVT.debug_message('Event Registration ID: ' || l_event_registration_id);
476 END IF;*/
477 Write_log(L_API_NAME, 'Event Registration ID: ' || l_event_registration_id);
478
479 END IF; -- l_event_registration_id
480
481 x_confirm_code := l_confirmation_code;
482
483 IF (p_reg_det_rec.cancellation_flag = 'Y')
484 THEN
485 -- Call cancel
486 IF (l_event_registration_id is null)
487 THEN
488 -- throw error - cannot cancel a registration that doesn't exist
489 AMS_Utility_PVT.error_message('AMS_EVT_REG_CANC_NO_CODE');
490 RAISE FND_API.g_exc_error;
491 END IF; -- l_event_registration_id
492 AMS_EvtRegs_PUB.Cancel_Registration( P_Api_Version_Number => 1.0
493 , P_Init_Msg_List => FND_API.G_FALSE
494 , P_Commit => FND_API.G_FALSE
495 , p_object_version => l_object_version_number
496 , P_event_offer_id => l_event_id
497 , p_registrant_party_id => l_reg_party_id
498 , p_confirmation_code => l_confirmation_code
499 , p_registration_group_id => null
500 , p_cancellation_reason_code => p_reg_det_rec.cancellation_reason_code
501 , p_block_fulfillment => p_block_fulfillment
502 , x_cancellation_code => l_cancellation_code
503 , X_Return_Status => l_return_status
504 , X_Msg_Count => x_msg_count
505 , X_Msg_Data => x_msg_data
506 );
507
508 ELSE
509
510 -- Cancellation Flag is not 'Y' - call create/update
511
512 IF (l_event_registration_id is not null)
513 THEN
514 -- Prepare Record for Update
515 AMS_EvtRegs_PUB.init_reg_rec(x_evt_regs_rec => l_evt_regs_rec);
516
517 IF ( (l_waitlisted_flag = 'N')
518 OR (l_attended_flag = 'Y')
519 )
520 THEN
521 l_system_status_code := 'REGISTERED';
522 ELSIF (l_waitlisted_flag = 'Y')
523 THEN
524 l_system_status_code := 'WAITLISTED';
525 END IF;
526
527 Open c_find_status_id(l_system_status_code);
528 Fetch c_find_status_id
529 Into l_status_id;
530 Close c_find_status_id;
531
532 l_evt_regs_rec.USER_STATUS_ID := l_status_id;
533 l_evt_regs_rec.waitlisted_priority := null;
534 l_evt_regs_rec.system_status_code := l_system_status_code;
535 l_evt_regs_rec.object_version_number := l_object_version_number;
536 l_evt_regs_rec.confirmation_code := l_confirmation_code;
537
538 END IF; -- l_event_registration_id
539
540 l_evt_regs_rec.event_offer_id := l_event_id;
541 l_evt_regs_rec.event_registration_id := l_event_registration_id;
542 l_evt_regs_rec.owner_user_id := p_owner_user_id;
543 l_evt_regs_rec.application_id := p_application_id;
544 l_evt_regs_rec.waitlisted_flag := l_waitlisted_flag;
545
546 l_evt_regs_rec.ATTENDED_FLAG := nvl(l_attended_flag, l_evt_regs_rec.ATTENDED_FLAG);
547 IF (l_evt_regs_Rec.ATTENDED_FLAG = 'Y')
548 THEN
549 l_evt_regs_Rec.max_attendee_override_flag := 'Y';
550 END IF;
551
552 l_evt_regs_rec.REG_SOURCE_TYPE_CODE := p_reg_det_rec.registration_source_type;
553
554 l_evt_regs_rec.REGISTRANT_PARTY_ID := l_reg_party_id;
555 l_evt_regs_rec.REGISTRANT_CONTACT_ID := l_reg_contact_id;
556 l_evt_regs_rec.ATTENDANT_PARTY_ID := l_att_party_id;
557 l_evt_regs_rec.ATTENDANT_CONTACT_ID := l_att_contact_id;
558
559 IF (l_event_registration_id is not null)
560 THEN
561
562 -- soagrawa 03-feb-2003
563 -- such a registration already exists
564
565 IF p_reg_det_rec.update_reg_rec IS NULL
566 THEN
567 l_update_reg_rec := 'Y';
568 ELSE
569 l_update_reg_rec := p_reg_det_rec.update_reg_rec;
570 END IF;
571
572 /*
573 AMS_Utility_PVT.debug_message('Value of l_update_reg_rec is '||l_update_reg_rec);
574 */
575 Write_log(L_API_NAME, 'Value of l_update_reg_rec is '||l_update_reg_rec);
576
577 IF l_update_reg_rec = 'C'
578 THEN
579
580 /*
581 IF (AMS_DEBUG_HIGH_ON) THEN
582 AMS_Utility_PVT.debug_message('Calling AMS_EvtRegs_PUB.Register');
583 END IF;
584 */
585 Write_log(L_API_NAME, 'Calling AMS_EvtRegs_PUB.Register');
586
587 l_event_registration_id := null;
588 l_evt_regs_rec.event_registration_id := null;
589 l_evt_regs_rec.confirmation_code := p_reg_det_rec.confirmation_code;
590
591 l_evt_regs_rec.event_offer_id := l_event_id;
592 l_evt_regs_rec.event_registration_id := l_event_registration_id;
593 l_evt_regs_rec.owner_user_id := p_owner_user_id;
594 l_evt_regs_rec.application_id := p_application_id;
595 l_evt_regs_rec.waitlisted_flag := l_waitlisted_flag;
596
597 l_evt_regs_rec.ATTENDED_FLAG := nvl(l_attended_flag, l_evt_regs_rec.ATTENDED_FLAG);
598 IF (l_evt_regs_Rec.ATTENDED_FLAG = 'Y')
599 THEN
600 l_evt_regs_Rec.max_attendee_override_flag := 'Y';
601 END IF;
602
603 l_evt_regs_rec.REG_SOURCE_TYPE_CODE := p_reg_det_rec.registration_source_type;
604
605 l_evt_regs_rec.REGISTRANT_PARTY_ID := l_reg_party_id;
606 l_evt_regs_rec.REGISTRANT_CONTACT_ID := l_reg_contact_id;
607 l_evt_regs_rec.ATTENDANT_PARTY_ID := l_att_party_id;
608 l_evt_regs_rec.ATTENDANT_CONTACT_ID := l_att_contact_id;
609 l_evt_regs_rec.system_status_code := null;
610 l_evt_regs_rec.user_status_id := null;
611 l_evt_regs_rec.registration_group_id := null;
612 l_evt_regs_rec.registration_source_id := null;
613
614 AMS_EvtRegs_PUB.Register( P_Api_Version_Number => 1.0
615 , P_Init_Msg_List => FND_API.G_FALSE
616 , P_Commit => FND_API.G_FALSE
617 , P_evt_regs_Rec => l_evt_regs_rec
618 , p_block_fulfillment => p_block_fulfillment
619 , x_event_registration_id => l_event_registration_id
620 , x_confirmation_code => x_confirm_code
621 , x_system_status_code => l_system_status_code
622 , X_Return_Status => l_return_status
623 , X_Msg_Count => x_msg_count
624 , X_Msg_Data => x_msg_data
625 );
626
627 ELSIF l_update_reg_rec = 'N'
628 THEN
629
630 -- soagrawa added the following on 24-apr-2003 for bug 2863012
631 -- see if existing registration's status is cancelled
632 -- if yes, then dont throw error
633 OPEN c_get_reg_status(l_event_registration_id);
634 FETCH c_get_reg_status INTO l_reg_status;
635 CLOSE c_get_reg_status;
636
637 IF l_reg_status = 'CANCELLED'
638 THEN
639 /*
640 IF (AMS_DEBUG_HIGH_ON) THEN
641 AMS_Utility_PVT.debug_message('Calling AMS_EvtRegs_PUB.Register');
642 END IF;
643 */
644 Write_log(L_API_NAME, 'Calling AMS_EvtRegs_PUB.Register');
645
646 l_event_registration_id := null;
647 l_evt_regs_rec.event_registration_id := null;
648 l_evt_regs_rec.confirmation_code := p_reg_det_rec.confirmation_code;
649
650 l_evt_regs_rec.event_offer_id := l_event_id;
651 l_evt_regs_rec.event_registration_id := l_event_registration_id;
652 l_evt_regs_rec.owner_user_id := p_owner_user_id;
653 l_evt_regs_rec.application_id := p_application_id;
654 l_evt_regs_rec.waitlisted_flag := l_waitlisted_flag;
655
656 l_evt_regs_rec.ATTENDED_FLAG := nvl(l_attended_flag, l_evt_regs_rec.ATTENDED_FLAG);
657 IF (l_evt_regs_Rec.ATTENDED_FLAG = 'Y')
658 THEN
659 l_evt_regs_Rec.max_attendee_override_flag := 'Y';
660 END IF;
661
662 l_evt_regs_rec.REG_SOURCE_TYPE_CODE := p_reg_det_rec.registration_source_type;
663
664 l_evt_regs_rec.REGISTRANT_PARTY_ID := l_reg_party_id;
665 l_evt_regs_rec.REGISTRANT_CONTACT_ID := l_reg_contact_id;
666 l_evt_regs_rec.ATTENDANT_PARTY_ID := l_att_party_id;
667 l_evt_regs_rec.ATTENDANT_CONTACT_ID := l_att_contact_id;
668 l_evt_regs_rec.system_status_code := null;
669 l_evt_regs_rec.user_status_id := null;
670 l_evt_regs_rec.registration_group_id := null;
671 l_evt_regs_rec.registration_source_id := null;
672
673 AMS_EvtRegs_PUB.Register( P_Api_Version_Number => 1.0
674 , P_Init_Msg_List => FND_API.G_FALSE
675 , P_Commit => FND_API.G_FALSE
676 , P_evt_regs_Rec => l_evt_regs_rec
677 , p_block_fulfillment => p_block_fulfillment
678 , x_event_registration_id => l_event_registration_id
679 , x_confirmation_code => x_confirm_code
680 , x_system_status_code => l_system_status_code
681 , X_Return_Status => l_return_status
682 , X_Msg_Count => x_msg_count
683 , X_Msg_Data => x_msg_data
684 );
685
686 ELSE
687 /*
688 IF (AMS_DEBUG_HIGH_ON) THEN
689 AMS_Utility_PVT.debug_message('Throwing error ');
690 END IF;
691 */
692 Write_log(L_API_NAME, 'Throwing error ');
693
694 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
695 FND_MESSAGE.set_name('AMS', 'AMS_PARTY_ALREADY_REGISTERED');
696 FND_MSG_PUB.add;
697 END IF;
698
699 l_return_status := FND_API.g_ret_sts_error;
700
701 END IF;
702
703
704 ELSE -- default is update 'U'
705
706 /*
707 IF (AMS_DEBUG_HIGH_ON) THEN
708 AMS_Utility_PVT.debug_message('Calling AMS_EvtRegs_PUB.Update_Registration');
709 AMS_Utility_PVT.debug_message('object version number: ' || l_evt_regs_rec.object_version_number);
710 END IF ;
711 */
712 Write_log(L_API_NAME, 'Calling AMS_EvtRegs_PUB.Update_Registration');
713 Write_log(L_API_NAME, 'object version number: ' || l_evt_regs_rec.object_version_number);
714
715 AMS_EvtRegs_PUB.Update_Registration( P_Api_Version_Number => 1.0
716 , P_Init_Msg_List => FND_API.G_FALSE
717 , P_Commit => FND_API.G_FALSE
718 , P_evt_regs_Rec => l_evt_regs_rec
719 , p_block_fulfillment => p_block_fulfillment
720 , X_Return_Status => l_return_status
721 , X_Msg_Count => x_msg_count
722 , X_Msg_Data => x_msg_data
723 );
724
725 END IF;
726 ELSE
727 /*
728 IF (AMS_DEBUG_HIGH_ON) THEN
729 AMS_Utility_PVT.debug_message('Calling AMS_EvtRegs_PUB.Register');
730 END IF;
731 */
732 Write_log(L_API_NAME,'Calling AMS_EvtRegs_PUB.Register');
733 AMS_EvtRegs_PUB.Register( P_Api_Version_Number => 1.0
734 , P_Init_Msg_List => FND_API.G_FALSE
735 , P_Commit => FND_API.G_FALSE
736 , P_evt_regs_Rec => l_evt_regs_rec
737 , p_block_fulfillment => p_block_fulfillment
738 , x_event_registration_id => l_event_registration_id
739 , x_confirmation_code => x_confirm_code
740 , x_system_status_code => l_system_status_code
741 , X_Return_Status => l_return_status
742 , X_Msg_Count => x_msg_count
743 , X_Msg_Data => x_msg_data
744 );
745 END IF; -- l_event_registration_id
746
747 END IF; -- call cancel
748
749 -- Check return status of create/update/cancel
750 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
751 THEN
752 RAISE FND_API.g_exc_unexpected_error;
753 ELSIF (l_return_status = FND_API.g_ret_sts_error)
754 THEN
755 RAISE FND_API.g_exc_error;
756 END IF; -- l_return_status
757
758 IF FND_API.to_boolean(p_commit)
759 THEN
760 COMMIT;
761 END IF; -- p_commit
762
763 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
764 , p_count => x_msg_count
765 , p_data => x_msg_data
766 );
767
768 x_return_status := l_return_status;
769
770 /*
771 IF (AMS_DEBUG_HIGH_ON) THEN
772 AMS_Utility_PVT.debug_message(l_full_name || ': end');
773 END IF;
774 */
775 Write_log(L_API_NAME, l_full_name || ': end');
776
777 EXCEPTION
778
779 WHEN FND_API.g_exc_error
780 THEN
781 ROLLBACK TO Register_Registrants_PUB;
782 x_return_status := FND_API.g_ret_sts_error;
783 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
784 , p_count => x_msg_count
785 , p_data => x_msg_data
786 );
787
788 WHEN FND_API.g_exc_unexpected_error
789 THEN
790 ROLLBACK TO Register_Registrants_PUB;
791 x_return_status := FND_API.g_ret_sts_unexp_error ;
792 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
793 , p_count => x_msg_count
794 , p_data => x_msg_data
795 );
796
797 WHEN OTHERS
798 THEN
799 ROLLBACK TO Register_Registrants_PUB;
800 x_return_status := FND_API.g_ret_sts_unexp_error ;
801 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
802 THEN
803 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
804 END IF; -- check_msg_level
805 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
806 , p_count => x_msg_count
807 , p_data => x_msg_data
808 );
809
810 END Register;
811
812 PROCEDURE Get_Party_Detail_Reg_Rec( p_reg_det_rec IN RegistrationDet
813 , x_reg_party_rec OUT NOCOPY AMS_Registrants_PVT.party_detail_rec_type
814 )
815
816 IS
817
818 BEGIN
819
820 x_reg_party_rec.party_id := p_reg_det_rec.reg_party_id;
821 x_reg_party_rec.party_type := p_reg_det_rec.reg_party_type;
822 x_reg_party_rec.contact_id := p_reg_det_rec.reg_contact_id;
823 x_reg_party_rec.party_name := p_reg_det_rec.reg_party_name;
824 x_reg_party_rec.title := p_reg_det_rec.reg_title;
825 x_reg_party_rec.first_name := p_reg_det_rec.reg_first_name;
826 x_reg_party_rec.middle_name := p_reg_det_rec.reg_middle_name;
827 x_reg_party_rec.last_name := p_reg_det_rec.reg_last_name;
828 x_reg_party_rec.address1 := p_reg_det_rec.reg_address1;
829 x_reg_party_rec.address2 := p_reg_det_rec.reg_address2;
830 x_reg_party_rec.address3 := p_reg_det_rec.reg_address3;
831 x_reg_party_rec.address4 := p_reg_det_rec.reg_address4;
832 x_reg_party_rec.gender := p_reg_det_rec.reg_gender;
833 x_reg_party_rec.address_line_phonetic := p_reg_det_rec.reg_address_line_phonetic;
834 x_reg_party_rec.analysis_fy := p_reg_det_rec.reg_analysis_fy;
835 x_reg_party_rec.apt_flag := p_reg_det_rec.reg_apt_flag;
836 x_reg_party_rec.best_time_contact_begin := p_reg_det_rec.reg_best_time_contact_begin;
837 x_reg_party_rec.best_time_contact_end := p_reg_det_rec.reg_best_time_contact_end;
838 x_reg_party_rec.category_code := p_reg_det_rec.reg_category_code;
839 x_reg_party_rec.ceo_name := p_reg_det_rec.reg_ceo_name;
840 x_reg_party_rec.city := p_reg_det_rec.reg_city;
841 x_reg_party_rec.country := p_reg_det_rec.reg_country;
842 x_reg_party_rec.county := p_reg_det_rec.reg_county;
843 x_reg_party_rec.current_fy_potential_rev := p_reg_det_rec.reg_current_fy_potential_rev;
844 x_reg_party_rec.next_fy_potential_rev := p_reg_det_rec.reg_next_fy_potential_rev;
845 x_reg_party_rec.household_income := p_reg_det_rec.reg_household_income;
846 x_reg_party_rec.decision_maker_flag := p_reg_det_rec.reg_decision_maker_flag;
847 x_reg_party_rec.department := p_reg_det_rec.reg_department;
848 x_reg_party_rec.dun_no_c := p_reg_det_rec.reg_dun_no_c;
849 x_reg_party_rec.email_address := p_reg_det_rec.reg_email_address;
850 x_reg_party_rec.employee_total := p_reg_det_rec.reg_employee_total;
851 x_reg_party_rec.fy_end_month := p_reg_det_rec.reg_fy_end_month;
852 x_reg_party_rec.floor := p_reg_det_rec.reg_floor;
853 x_reg_party_rec.gsa_indicator_flag := p_reg_det_rec.reg_gsa_indicator_flag;
854 x_reg_party_rec.house_number := p_reg_det_rec.reg_house_number;
855 x_reg_party_rec.identifying_address_flag := p_reg_det_rec.reg_identifying_address_flag;
856 x_reg_party_rec.jgzz_fiscal_code := p_reg_det_rec.reg_jgzz_fiscal_code;
857 x_reg_party_rec.job_title := p_reg_det_rec.reg_job_title;
858 x_reg_party_rec.last_order_date := p_reg_det_rec.reg_last_order_date;
859 x_reg_party_rec.org_legal_status := p_reg_det_rec.reg_org_legal_status;
860 x_reg_party_rec.line_of_business := p_reg_det_rec.reg_line_of_business;
861 x_reg_party_rec.mission_statement := p_reg_det_rec.reg_mission_statement;
862 x_reg_party_rec.org_name_phonetic := p_reg_det_rec.reg_org_name_phonetic;
863 x_reg_party_rec.overseas_address_flag := p_reg_det_rec.reg_overseas_address_flag;
864 x_reg_party_rec.name_suffix := p_reg_det_rec.reg_name_suffix;
865 x_reg_party_rec.phone_area_code := p_reg_det_rec.reg_phone_area_code;
866 x_reg_party_rec.phone_country_code := p_reg_det_rec.reg_phone_country_code;
867 x_reg_party_rec.phone_extension := p_reg_det_rec.reg_phone_extension;
868 x_reg_party_rec.phone_number := p_reg_det_rec.reg_phone_number;
869 x_reg_party_rec.postal_code := p_reg_det_rec.reg_postal_code;
870 x_reg_party_rec.postal_plus4_code := p_reg_det_rec.reg_postal_plus4_code;
871 x_reg_party_rec.po_box_no := p_reg_det_rec.reg_po_box_no;
872 x_reg_party_rec.province := p_reg_det_rec.reg_province;
873 x_reg_party_rec.rural_route_no := p_reg_det_rec.reg_rural_route_no;
874 x_reg_party_rec.rural_route_type := p_reg_det_rec.reg_rural_route_type;
875 x_reg_party_rec.secondary_suffix_element := p_reg_det_rec.reg_secondary_suffix_element;
876 x_reg_party_rec.sic_code := p_reg_det_rec.reg_sic_code;
877 x_reg_party_rec.sic_code_type := p_reg_det_rec.reg_sic_code_type;
878 x_reg_party_rec.site_use_code := p_reg_det_rec.reg_site_use_code;
879 x_reg_party_rec.state := p_reg_det_rec.reg_state;
880 x_reg_party_rec.street := p_reg_det_rec.reg_street;
881 x_reg_party_rec.street_number := p_reg_det_rec.reg_street_number;
882 x_reg_party_rec.street_suffix := p_reg_det_rec.reg_street_suffix;
883 x_reg_party_rec.suite := p_reg_det_rec.reg_suite;
884 x_reg_party_rec.tax_name := p_reg_det_rec.reg_tax_name;
885 x_reg_party_rec.tax_reference := p_reg_det_rec.reg_tax_reference;
886 x_reg_party_rec.timezone := p_reg_det_rec.reg_timezone;
887 x_reg_party_rec.total_no_of_orders := p_reg_det_rec.reg_total_no_of_orders;
888 x_reg_party_rec.total_order_amount := p_reg_det_rec.reg_total_order_amount;
889 x_reg_party_rec.year_established := p_reg_det_rec.reg_year_established;
890 x_reg_party_rec.url := p_reg_det_rec.reg_url;
891 x_reg_party_rec.survey_notes := p_reg_det_rec.reg_survey_notes;
892 x_reg_party_rec.contact_me_flag := p_reg_det_rec.reg_contact_me_flag;
893 x_reg_party_rec.email_ok_flag := p_reg_det_rec.reg_email_ok_flag;
894
895 END Get_Party_Detail_Reg_Rec;
896
897 PROCEDURE Get_Party_Detail_Att_Rec( p_reg_det_rec IN RegistrationDet
898 , x_att_party_rec OUT NOCOPY AMS_Registrants_PVT.party_detail_rec_type
899 )
900
901 IS
902
903 BEGIN
904
905 x_att_party_rec.party_id := p_reg_det_rec.att_party_id;
906 x_att_party_rec.party_type := p_reg_det_rec.att_party_type;
907 x_att_party_rec.contact_id := p_reg_det_rec.att_contact_id;
908 x_att_party_rec.party_name := p_reg_det_rec.att_party_name;
909 x_att_party_rec.title := p_reg_det_rec.att_title;
910 x_att_party_rec.first_name := p_reg_det_rec.att_first_name;
911 x_att_party_rec.middle_name := p_reg_det_rec.att_middle_name;
912 x_att_party_rec.last_name := p_reg_det_rec.att_last_name;
913 x_att_party_rec.address1 := p_reg_det_rec.att_address1;
914 x_att_party_rec.address2 := p_reg_det_rec.att_address2;
915 x_att_party_rec.address3 := p_reg_det_rec.att_address3;
916 x_att_party_rec.address4 := p_reg_det_rec.att_address4;
917 x_att_party_rec.gender := p_reg_det_rec.att_gender;
918 x_att_party_rec.address_line_phonetic := p_reg_det_rec.att_address_line_phonetic;
919 x_att_party_rec.analysis_fy := p_reg_det_rec.att_analysis_fy;
920 x_att_party_rec.apt_flag := p_reg_det_rec.att_apt_flag;
921 x_att_party_rec.best_time_contact_begin := p_reg_det_rec.att_best_time_contact_begin;
922 x_att_party_rec.best_time_contact_end := p_reg_det_rec.att_best_time_contact_end;
923 x_att_party_rec.category_code := p_reg_det_rec.att_category_code;
924 x_att_party_rec.ceo_name := p_reg_det_rec.att_ceo_name;
925 x_att_party_rec.city := p_reg_det_rec.att_city;
926 x_att_party_rec.country := p_reg_det_rec.att_country;
927 x_att_party_rec.county := p_reg_det_rec.att_county;
928 x_att_party_rec.current_fy_potential_rev := p_reg_det_rec.att_current_fy_potential_rev;
929 x_att_party_rec.next_fy_potential_rev := p_reg_det_rec.att_next_fy_potential_rev;
930 x_att_party_rec.household_income := p_reg_det_rec.att_household_income;
931 x_att_party_rec.decision_maker_flag := p_reg_det_rec.att_decision_maker_flag;
932 x_att_party_rec.department := p_reg_det_rec.att_department;
933 x_att_party_rec.dun_no_c := p_reg_det_rec.att_dun_no_c;
934 x_att_party_rec.email_address := p_reg_det_rec.att_email_address;
935 x_att_party_rec.employee_total := p_reg_det_rec.att_employee_total;
936 x_att_party_rec.fy_end_month := p_reg_det_rec.att_fy_end_month;
937 x_att_party_rec.floor := p_reg_det_rec.att_floor;
938 x_att_party_rec.gsa_indicator_flag := p_reg_det_rec.att_gsa_indicator_flag;
939 x_att_party_rec.house_number := p_reg_det_rec.att_house_number;
940 x_att_party_rec.identifying_address_flag := p_reg_det_rec.att_identifying_address_flag;
941 x_att_party_rec.jgzz_fiscal_code := p_reg_det_rec.att_jgzz_fiscal_code;
942 x_att_party_rec.job_title := p_reg_det_rec.att_job_title;
943 x_att_party_rec.last_order_date := p_reg_det_rec.att_last_order_date;
944 x_att_party_rec.org_legal_status := p_reg_det_rec.att_org_legal_status;
945 x_att_party_rec.line_of_business := p_reg_det_rec.att_line_of_business;
946 x_att_party_rec.mission_statement := p_reg_det_rec.att_mission_statement;
947 x_att_party_rec.org_name_phonetic := p_reg_det_rec.att_org_name_phonetic;
948 x_att_party_rec.overseas_address_flag := p_reg_det_rec.att_overseas_address_flag;
949 x_att_party_rec.name_suffix := p_reg_det_rec.att_name_suffix;
950 x_att_party_rec.phone_area_code := p_reg_det_rec.att_phone_area_code;
951 x_att_party_rec.phone_country_code := p_reg_det_rec.att_phone_country_code;
952 x_att_party_rec.phone_extension := p_reg_det_rec.att_phone_extension;
953 x_att_party_rec.phone_number := p_reg_det_rec.att_phone_number;
954 x_att_party_rec.postal_code := p_reg_det_rec.att_postal_code;
955 x_att_party_rec.postal_plus4_code := p_reg_det_rec.att_postal_plus4_code;
956 x_att_party_rec.po_box_no := p_reg_det_rec.att_po_box_no;
957 x_att_party_rec.province := p_reg_det_rec.att_province;
958 x_att_party_rec.rural_route_no := p_reg_det_rec.att_rural_route_no;
959 x_att_party_rec.rural_route_type := p_reg_det_rec.att_rural_route_type;
960 x_att_party_rec.secondary_suffix_element := p_reg_det_rec.att_secondary_suffix_element;
961 x_att_party_rec.sic_code := p_reg_det_rec.att_sic_code;
962 x_att_party_rec.sic_code_type := p_reg_det_rec.att_sic_code_type;
963 x_att_party_rec.site_use_code := p_reg_det_rec.att_site_use_code;
964 x_att_party_rec.state := p_reg_det_rec.att_state;
965 x_att_party_rec.street := p_reg_det_rec.att_street;
966 x_att_party_rec.street_number := p_reg_det_rec.att_street_number;
967 x_att_party_rec.street_suffix := p_reg_det_rec.att_street_suffix;
968 x_att_party_rec.suite := p_reg_det_rec.att_suite;
969 x_att_party_rec.tax_name := p_reg_det_rec.att_tax_name;
970 x_att_party_rec.tax_reference := p_reg_det_rec.att_tax_reference;
971 x_att_party_rec.timezone := p_reg_det_rec.att_timezone;
972 x_att_party_rec.total_no_of_orders := p_reg_det_rec.att_total_no_of_orders;
973 x_att_party_rec.total_order_amount := p_reg_det_rec.att_total_order_amount;
974 x_att_party_rec.year_established := p_reg_det_rec.att_year_established;
975 x_att_party_rec.url := p_reg_det_rec.att_url;
976 x_att_party_rec.survey_notes := p_reg_det_rec.att_survey_notes;
977 x_att_party_rec.contact_me_flag := p_reg_det_rec.att_contact_me_flag;
978 x_att_party_rec.email_ok_flag := p_reg_det_rec.att_email_ok_flag;
979
980 END Get_Party_Detail_Att_Rec;
981
982 END AMS_Registrants_PUB;