[Home] [Help]
PACKAGE BODY: APPS.AMS_EVTREGS_PVT
Source
1 PACKAGE BODY AMS_EvtRegs_PVT as
2 /*$Header: amsvregb.pls 120.1 2011/04/14 08:59:04 dkailash ship $*/
3 --------------------------------------------------------------------------------------
4 -- Package name
5 -- AMS_EvtRegs_PVT
6 -- Purpose
7 -- This package is a Private API for managing event registrations
8 -- History
9 -- 16-OCT-1999 sugupta Created
10 -- 27-NOV-2001 mukumar added code to support if the effective capacity is zero.
11 -- 01-MAR-2002 dcastlem Implemented invite list validation and
12 -- automatic registration for capacity changes
13 -- 12-MAR-2002 dcastlem Added support for general Public API
14 -- (AMS_Registrants_PUB)
15 -- 05-APR-2002 dcastlem Refined waitlist code
16 -- Fixed bug 2284681 - cancel end date
17 -- 08-APR-2002 dcastlem Copied write_interaction from AMS_ScheduleRules_PVT
18 -- 28-MAY-2002 dcastlem removed task creation from prioritize_waitlist
19 -- 28-MAY-2002 dcastlem disallowed cancelled registrants to transfer
20 -- 24-OCT-2002 soagrawa Checking for registration id sequence value's uniqueness
21 -- 12-NOV-2002 musman Restricted the cancelled registrant to update the attended flag
22 -- 18-nov-2002 soagrawa Fixed bug# 2672928 regding fulfilling for CSCH of type events
23 -- 20-dec-2002 soagrawa Fixed bug# 2600986 ini query of contact_in_invite_list
24 -- 24-dec-2002 soagrawa added get_user_id for calls to check_content_rule to pass a valid user id
25 -- 29-jan-2003 soagrawa Modified update_evtregs_wrapper to fix bug# 2775357
26 -- 03-feb-2003 soagrawa Fixed bug# 2777302
27 -- 13-Feb-2003 soagrawa Modified cursor c_id_exists for soagrawa's above fix.
28 -- 08-Mar-2003 ptendulk modified write interactions procedure. Bug # 2838162
29 -- 13-Mar-2003 dbiswas Check for date_registration_placed. bug 2845867
30 -- 20-Mar-2003 dbiswas Modified update statements in cancel_evtregs for NI issue
31 -- 16-Apr-2003 dbiswas Modified cursor for contact_in_invite_list to carry fix for NI bug# 2610067
32 -- to the next releases
33 -- 23-May-2003 SOAGRAWA fixed bug# 2949603
34 -- 23-May-2003 SOAGRAWA fixed bug# 2525529
35 -- 27-may-2003 soagrawa Fixed NI issue about result of interaction in write_interaction bug# 2978948
36 -- 24-jun-2003 anchaudh fixed bug#3020564
37 -- 19-aug-2003 anchaudh Fixed bug#3101955
38 -- 29-aug-2003 anchaudh Fixed bug#3119915
39 -- 08-jun-2004 soagrawa Modified cursor in contact_in_invite_list for performance bug# 3667627
40 -- 15-Feb-2005 sikalyan Fixed bug#4185688
41 --------------------------------------------------------------------------------------
42
43 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_EvtRegs_PVT';
44 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvregb.pls';
45
46 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
47 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
48 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
49
50 FUNCTION get_user_id (
51 p_resource_id IN NUMBER
52 )
53 RETURN NUMBER
54 IS
55 l_user_id NUMBER;
56
57 CURSOR c_user IS
58 SELECT user_id
59 FROM ams_jtf_rs_emp_v
60 WHERE resource_id = p_resource_id;
61 BEGIN
62 OPEN c_user;
63 FETCH c_user INTO l_user_id;
64 IF c_user%NOTFOUND THEN
65 l_user_id := -1;
66 -- Adding an error message will cause the function
67 -- to violate the WNDS pragma, preventing it from
68 -- being able to be called from a SQL statement.
69 END IF;
70 CLOSE c_user;
71
72 RETURN l_user_id;
73 END get_user_id;
74
75 PROCEDURE Insert_evtregs( p_evt_regs_Rec IN evt_regs_Rec_Type
76 , p_system_status_code IN VARCHAR2
77 , p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
78 , x_confirmation_code OUT NOCOPY VARCHAR2
79 , x_event_registration_id OUT NOCOPY NUMBER
80 )
81 IS
82
83 l_evt_regs_Rec evt_regs_Rec_Type := p_evt_regs_Rec;
84 l_system_status_code VARCHAR2(30) := p_system_status_code;
85 l_event_registration_id NUMBER := l_evt_regs_Rec.EVENT_REGISTRATION_ID;
86 l_confirmation_code VARCHAR2(30) := l_evt_regs_rec.confirmation_code;
87 l_waitlisted_priority NUMBER := NULL;
88 l_confirmation_id NUMBER;
89 l_code_prefix VARCHAR2(50) := FND_PROFILE.Value('AMS_CONF_CODE_PREFIX');
90 /*dbiswas Mar 13, 2003 */
91 l_date_reg_placed DATE := l_evt_regs_rec.DATE_REGISTRATION_PLACED;
92
93 /* Following code is modified by ptendulk to move to 1:1 ffm
94 l_email AMF_REQUEST.string_tbl_type;
95 l_fax AMF_REQUEST.string_tbl_type;
96 l_bind_values AMF_REQUEST.string_tbl_type;
97 l_party_id AMF_REQUEST.number_tbl_type;
98 */
99
100 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
101 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
102 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
103 l_bind_names JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
104 l_bind_values JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
105
106
107
108 l_return_status VARCHAR2(1);
109 l_msg_count NUMBER;
110 l_msg_data VARCHAR2(2000);
111 l_request_history_id NUMBER;
112
113 l_object_type VARCHAR2(30);
114 l_end_date DATE;
115 l_dummy NUMBER;
116
117 /*
118 CURSOR c_get_email_address(p_party_id IN NUMBER) is
119 select email_address
120 from hz_parties
121 where party_id = p_party_id;
122
123 CURSOR c_get_email_address_b2b( p_contact_id IN NUMBER
124 , p_party_id IN NUMBER
125 )
126 IS
127 select email_address
128 from hz_parties hzp,
129 hz_relationships hzpr
130 where hzp.party_id = hzpr.party_id
131 and hzpr.object_id = p_party_id
132 and hzpr.subject_id = p_contact_id;
133 */
134
135 CURSOR c_get_object_type(p_event_offer_id IN NUMBER) IS
136 SELECT event_object_type,
137 trunc(event_end_date) + 1,
138 parent_type
139 from ams_event_offers_all_b
140 where event_offer_id = p_event_offer_id;
141
142 CURSOR c_evt_regs_seq IS
143 SELECT ams_event_registrations_s.NEXTVAL
144 FROM DUAL;
145
146 -- added by soagrawa on 24-oct-2002 for uniqueness sake
147 -- needed so as to not conflict with ids generated in migration
148 CURSOR c_id_exists (l_id IN NUMBER) IS
149 SELECT 1
150 FROM dual
151 WHERE EXISTS (SELECT 1
152 -- FROM AMS_event_offers_all_B Modified by ptendulk on 12-Feb-2002
153 -- WHERE event_offer_id = l_id);
154 FROM ams_event_registrations
155 WHERE event_registration_id = l_id);
156
157 CURSOR c_evt_reg_conf_seq IS
158 SELECT ams_event_reg_confirmation_s.nextval
159 FROM dual;
160
161 CURSOR c_evt_reg_waitlist_seq IS
162 SELECT ams_reg_waitlist_priority_s.nextval
163 FROM dual;
164
165 CURSOR c_evt_reg_status IS
166 SELECT user_status_id
167 FROM ams_user_statuses_b
168 WHERE SYSTEM_STATUS_CODE = p_system_status_code
169 and SYSTEM_STATUS_TYPE = 'AMS_EVENT_REG_STATUS'
170 and default_flag = 'Y'; --anchaudh:fixed bug#3020564 on 24-jun-2003.
171
172 -- soagrawa 18-nov-2002 for bug# 2672928
173 l_csch_id NUMBER;
174 l_object_id NUMBER;
175 l_parent_type VARCHAR2(10);
176
177 CURSOR c_csch_id (obj_id NUMBER) IS
178 SELECT schedule_id
179 FROM ams_campaign_schedules_b
180 WHERE related_event_id = obj_id;
181
182
183 BEGIN
184 IF (l_event_registration_id IS NULL)
185 THEN
186 -- added by soagrawa on 24-oct-2002 for uniqueness sake
187 -- encapsulated sequence nextval retrieval in a loop
188 -- for uniqueness sake
189 LOOP
190 l_dummy := NULL;
191 OPEN c_evt_regs_seq;
192 FETCH c_evt_regs_seq INTO l_event_registration_id;
193 CLOSE c_evt_regs_seq;
194
195 OPEN c_id_exists(l_event_registration_id);
196 FETCH c_id_exists INTO l_dummy;
197 CLOSE c_id_exists;
198 EXIT WHEN l_dummy IS NULL;
199 END LOOP;
200 -- end soagrawa 24-oct-2002
201 END IF; -- l_event_registration_id
202
203 IF (l_confirmation_code IS NULL)
204 THEN
205 OPEN c_evt_reg_conf_seq;
206 FETCH c_evt_reg_conf_seq
207 INTO l_confirmation_id;
208 CLOSE c_evt_reg_conf_seq;
209 END IF; -- l_confirmation_code
210
211 l_confirmation_code := l_code_prefix || to_char(l_confirmation_id);
212
213 IF (l_system_status_code = 'WAITLISTED')
214 THEN
215 open c_evt_reg_waitlist_seq;
216 fetch c_evt_reg_waitlist_seq
217 into l_waitlisted_priority;
218 close c_evt_reg_waitlist_seq;
219 END IF; -- l_system_status_code
220
221 OPEN c_evt_reg_status;
222 FETCH c_evt_reg_status
223 INTO l_evt_regs_Rec.user_status_id;
224 -- error out if user status not found...
225 CLOSE c_evt_reg_status;
226
227 -- added by dbiswas on Mar 12, 2003 for bug 2845867
228 IF l_date_reg_placed = Fnd_Api.g_miss_date THEN
229 l_date_reg_placed := sysdate;
230 END IF;
231 -- end update on Mar 12, 2003
232
233 INSERT INTO AMS_EVENT_REGISTRATIONS(
234 EVENT_REGISTRATION_ID,
235 LAST_UPDATE_DATE,
236 LAST_UPDATED_BY,
237 CREATION_DATE,
238 CREATED_BY,
239 LAST_UPDATE_LOGIN,
240 OBJECT_VERSION_NUMBER,
241 EVENT_OFFER_ID,
242 APPLICATION_ID,
243 ACTIVE_FLAG,
244 OWNER_USER_ID,
245 DATE_REGISTRATION_PLACED,
246 USER_STATUS_ID,
247 SYSTEM_STATUS_CODE,
248 LAST_REG_STATUS_DATE,
249 REG_SOURCE_TYPE_CODE,
250 REGISTRATION_SOURCE_ID,
251 CONFIRMATION_CODE,
252 SOURCE_CODE,
253 REGISTRATION_GROUP_ID,
254 REGISTRANT_PARTY_ID,
255 REGISTRANT_CONTACT_ID,
256 REGISTRANT_ACCOUNT_ID,
257 ATTENDANT_PARTY_ID,
258 ATTENDANT_CONTACT_ID,
259 ATTENDANT_ACCOUNT_ID,
260 ORIGINAL_REGISTRANT_CONTACT_ID,
261 PROSPECT_FLAG,
262 ATTENDED_FLAG,
263 CONFIRMED_FLAG,
264 EVALUATED_FLAG,
265 ATTENDANCE_RESULT_CODE,
266 WAITLISTED_PRIORITY,
267 TARGET_LIST_ID,
268 INBOUND_MEDIA_ID,
269 INBOUND_CHANNEL_ID,
270 CANCELLATION_CODE,
271 CANCELLATION_REASON_CODE,
272 ATTENDANCE_FAILURE_REASON,
273 ATTENDANT_LANGUAGE,
274 SALESREP_ID,
275 ORDER_HEADER_ID,
276 ORDER_LINE_ID,
277 DESCRIPTION,
278 MAX_ATTENDEE_OVERRIDE_FLAG,
279 INVITE_ONLY_OVERRIDE_FLAG,
280 PAYMENT_STATUS_CODE,
281 AUTO_REGISTER_FLAG,
282 ATTRIBUTE_CATEGORY,
283 ATTRIBUTE1,
284 ATTRIBUTE2,
285 ATTRIBUTE3,
286 ATTRIBUTE4,
287 ATTRIBUTE5,
288 ATTRIBUTE6,
289 ATTRIBUTE7,
290 ATTRIBUTE8,
291 ATTRIBUTE9,
292 ATTRIBUTE10,
293 ATTRIBUTE11,
294 ATTRIBUTE12,
295 ATTRIBUTE13,
296 ATTRIBUTE14,
297 ATTRIBUTE15,
298 attendee_role_type, /* Hornet : added for imeeting integration*/
299 notification_type, /* Hornet : added for imeeting integration*/
300 last_notified_time, /* Hornet : added for imeeting integration*/
301 EVENT_JOIN_TIME,/* Hornet : added for imeeting integration*/
302 EVENT_EXIT_TIME, /* Hornet : added for imeeting integration*/
303 MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
304 ) VALUES (
305 l_event_registration_id,
306 SYSDATE,
307 FND_GLOBAL.user_id,
308 SYSDATE,
309 FND_GLOBAL.user_id,
310 FND_GLOBAL.conc_login_id,
311 1, -- object_version_number
312 l_evt_regs_rec.EVENT_OFFER_ID,
313 l_evt_regs_rec.APPLICATION_ID,
314 nvl(l_evt_regs_rec.ACTIVE_FLAG, 'Y'),
315 l_evt_regs_rec.OWNER_USER_ID,
316 nvl(l_date_reg_placed,sysdate),
317 l_evt_regs_rec.USER_STATUS_ID,
318 l_system_status_code,
319 nvl(l_evt_regs_rec.LAST_REG_STATUS_DATE, sysdate),
320 l_evt_regs_rec.REG_SOURCE_TYPE_CODE,
321 l_evt_regs_rec.REGISTRATION_SOURCE_ID,
322 l_confirmation_code,
323 l_evt_regs_rec.SOURCE_CODE,
324 l_evt_regs_rec.REGISTRATION_GROUP_ID,
325 l_evt_regs_rec.REGISTRANT_PARTY_ID,
326 l_evt_regs_rec.REGISTRANT_CONTACT_ID,
327 l_evt_regs_rec.REGISTRANT_ACCOUNT_ID,
328 l_evt_regs_rec.ATTENDANT_PARTY_ID,
329 l_evt_regs_rec.ATTENDANT_CONTACT_ID,
330 l_evt_regs_rec.ATTENDANT_ACCOUNT_ID,
331 nvl(l_evt_regs_rec.ORIGINAL_REGISTRANT_CONTACT_ID,l_evt_regs_rec.REGISTRANT_CONTACT_ID),
332 nvl(l_evt_regs_rec.PROSPECT_FLAG, 'N'),
333 nvl(l_evt_regs_rec.ATTENDED_FLAG, 'N'),
334 nvl(l_evt_regs_rec.CONFIRMED_FLAG, 'N'),
335 nvl(l_evt_regs_rec.EVALUATED_FLAG, 'N'),
336 l_evt_regs_rec.ATTENDANCE_RESULT_CODE,
337 l_waitlisted_priority,
338 l_evt_regs_rec.TARGET_LIST_ID,
339 l_evt_regs_rec.INBOUND_MEDIA_ID,
340 l_evt_regs_rec.INBOUND_CHANNEL_ID,
341 l_evt_regs_rec.CANCELLATION_CODE,
342 l_evt_regs_rec.CANCELLATION_REASON_CODE,
343 l_evt_regs_rec.ATTENDANCE_FAILURE_REASON,
344 l_evt_regs_rec.ATTENDANT_LANGUAGE,
345 l_evt_regs_rec.SALESREP_ID,
346 l_evt_regs_rec.ORDER_HEADER_ID,
347 l_evt_regs_rec.ORDER_LINE_ID,
348 l_evt_regs_rec.DESCRIPTION,
349 nvl(l_evt_regs_rec.MAX_ATTENDEE_OVERRIDE_FLAG, 'N'),
350 nvl(l_evt_regs_rec.INVITE_ONLY_OVERRIDE_FLAG, 'N'),
351 l_evt_regs_rec.PAYMENT_STATUS_CODE,
352 nvl(l_evt_regs_rec.AUTO_REGISTER_FLAG, 'Y'),
353 l_evt_regs_rec.ATTRIBUTE_CATEGORY,
354 l_evt_regs_rec.ATTRIBUTE1,
355 l_evt_regs_rec.ATTRIBUTE2,
356 l_evt_regs_rec.ATTRIBUTE3,
357 l_evt_regs_rec.ATTRIBUTE4,
358 l_evt_regs_rec.ATTRIBUTE5,
359 l_evt_regs_rec.ATTRIBUTE6,
360 l_evt_regs_rec.ATTRIBUTE7,
361 l_evt_regs_rec.ATTRIBUTE8,
362 l_evt_regs_rec.ATTRIBUTE9,
363 l_evt_regs_rec.ATTRIBUTE10,
364 l_evt_regs_rec.ATTRIBUTE11,
365 l_evt_regs_rec.ATTRIBUTE12,
366 l_evt_regs_rec.ATTRIBUTE13,
367 l_evt_regs_rec.ATTRIBUTE14,
368 l_evt_regs_rec.ATTRIBUTE15,
369 l_evt_regs_rec.attendee_role_type, /* Hornet : added for imeeting integration*/
370 l_evt_regs_rec.notification_type, /* Hornet : added for imeeting integration*/
371 l_evt_regs_rec.last_notified_time, /* Hornet : added for imeeting integration*/
372 l_evt_regs_rec.EVENT_JOIN_TIME, /* Hornet : added for imeeting integration*/
373 l_evt_regs_rec.EVENT_EXIT_TIME, /* Hornet : added for imeeting integration*/
374 l_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
375 );
376 x_confirmation_code := l_confirmation_code;
377 x_event_registration_id := l_event_registration_id;
378
379 /*
380 IF (l_evt_regs_rec.attendant_contact_id = l_evt_regs_rec.attendant_party_id)
381 THEN
382 -- B2C
383 open c_get_email_address(l_evt_regs_rec.attendant_contact_id);
384 fetch c_get_email_address
385 into l_email(0);
386 close c_get_email_address;
387 ELSE
388 open c_get_email_address_b2b( l_evt_regs_rec.attendant_contact_id
389 , l_evt_regs_rec.attendant_party_id
390 );
391 fetch c_get_email_address_b2b
392 into l_email(0);
393 close c_get_email_address_b2b;
394 END IF;
395 --l_party_id(0) := l_evt_regs_rec.attendant_contact_id;
396 */
400 l_end_date,
397 open c_get_object_type(l_evt_regs_rec.event_offer_id);
398 fetch c_get_object_type
399 into l_object_type,
401 l_parent_type;
402 close c_get_object_type;
403
404 -- soagrawa 18-nov-2002 for bug# 2672928
405 l_object_id := l_evt_regs_rec.event_offer_id;
406
407 IF l_object_type = 'EONE'
408 THEN
409 IF l_parent_type = 'CAMP'
410 THEN
411
412 OPEN c_csch_id(l_object_id);
413 FETCH c_csch_id INTO l_csch_id;
414 CLOSE c_csch_id;
415
416 l_object_type := 'CSCH';
417 l_object_id := l_csch_id;
418 END IF;
419 END IF;
420 -- end soagrawa 18-nov-2002
421
422 IF ( (nvl(l_evt_regs_rec.ATTENDED_FLAG, 'N') = 'N')
423 AND (sysdate < l_end_date)
424 )
425 THEN
426
427
428 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
429 l_bind_values(0) := to_char(l_event_registration_id);
430 l_bind_values(1) := to_char(l_event_registration_id); */
431 l_bind_names(1) := 'REGISTRATION_ID' ;
432 l_bind_values(1) := TO_CHAR(l_event_registration_id);
433
434 IF (l_system_status_code = 'REGISTERED')
435 THEN
436 -- Interaction
437 write_interaction( p_event_offer_id => l_evt_regs_rec.EVENT_OFFER_ID
438 -- dbiswas 16-apr-2003 for NI issue with interactions (part of 2610067)
439 --, p_party_id => l_evt_regs_rec.ATTENDANT_PARTY_ID
440 , p_party_id => l_evt_regs_rec.ATTENDANT_CONTACT_ID
441 );
442
443 -- Fulfillment
444 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
445 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
446 )
447 THEN
448 IF (AMS_DEBUG_HIGH_ON) THEN
449
450 AMS_Utility_PVT.Debug_Message('Calling check_content_rule for fulfillment (registered)');
451 END IF;
452 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
453 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
454 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
455 , p_object_type => l_object_type -- IN VARCHAR2
456 , p_object_id => l_object_id --l_evt_regs_rec.event_offer_id -- IN NUMBER
457 , p_trigger_type => 'REG_CONFIRM' -- IN VARCHAR2
458 -- , p_requestor_type => -- IN VARCHAR2 := NULL
459 -- Following line is modified by ptendulk on 12-Dec-2002
460 , p_requestor_id => get_user_id(l_evt_regs_rec.OWNER_USER_ID)
461 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_evt_regs_rec.OWNER_USER_ID) -- IN NUMBER
462 -- , p_server_group => -- IN NUMBER := NULL
463 -- , p_scheduled_date => -- IN DATE := SYSDATE
464 -- , p_media_types => -- IN VARCHAR2 := 'E'
465 -- , p_archive => -- IN VARCHAR2 := 'N'
466 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
467 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
468 -- , p_language_code => -- IN VARCHAR2 := NULL
469 -- , p_profile_id => -- IN NUMBER := NULL
470 -- , p_order_id => -- IN NUMBER := NULL
471 -- , p_collateral_id => -- IN NUMBER := NULL
472 , p_party_id => l_party_id -- IN JTF_REQUEST_GRP.G_NUMBER_TBL_TYPE
473 , p_email => l_email -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
474 , p_fax => l_fax -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
475 -- Following line is added by ptendulk on 12-Dec-2002
476 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
477 , p_bind_values => l_bind_values -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
478 , x_return_status => l_return_status -- OUT VARCHAR2
479 , x_msg_count => l_msg_count -- OUT NUMBER
480 , x_msg_data => l_msg_data -- OUT VARCHAR2
481 , x_request_history_id => l_request_history_id -- OUT NUMBER
482 );
483 IF (AMS_DEBUG_HIGH_ON) THEN
484 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
485 END IF;
486
487 IF l_return_status = FND_API.g_ret_sts_error THEN
488 RAISE FND_API.g_exc_error;
489 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
490 RAISE FND_API.g_exc_unexpected_error;
491 END IF;
492
493
494 END IF;
495 ELSIF (l_system_status_code = 'WAITLISTED')
496 THEN
500 )
497 -- Fulfillment
498 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
499 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
501 THEN
502 IF (AMS_DEBUG_HIGH_ON) THEN
503
504 AMS_Utility_PVT.Debug_Message('Calling check_content_rule for fulfillment (waitlisted)');
505 END IF;
506 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
507 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
508 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
509 , p_object_type => l_object_type -- IN VARCHAR2
510 , p_object_id => l_object_id --l_evt_regs_rec.event_offer_id -- IN NUMBER
511 , p_trigger_type => 'REG_WAITLIST' -- IN VARCHAR2
512 -- , p_requestor_type => -- IN VARCHAR2 := NULL
513 -- Following line is modified by ptendulk on 12-Dec-2002
514 , p_requestor_id => get_user_id(l_evt_regs_rec.OWNER_USER_ID)
515 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_evt_regs_rec.OWNER_USER_ID) -- IN NUMBER
516 -- , p_server_group => -- IN NUMBER := NULL
517 -- , p_scheduled_date => -- IN DATE := SYSDATE
518 -- , p_media_types => -- IN VARCHAR2 := 'E'
519 -- , p_archive => -- IN VARCHAR2 := 'N'
520 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
521 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
522 -- , p_language_code => -- IN VARCHAR2 := NULL
523 -- , p_profile_id => -- IN NUMBER := NULL
524 -- , p_order_id => -- IN NUMBER := NULL
525 -- , p_collateral_id => -- IN NUMBER := NULL
526 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
527 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
528 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
529 -- Following line is added by ptendulk on 12-Dec-2002
530 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
531 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
532 , x_return_status => l_return_status -- OUT VARCHAR2
533 , x_msg_count => l_msg_count -- OUT NUMBER
534 , x_msg_data => l_msg_data -- OUT VARCHAR2
535 , x_request_history_id => l_request_history_id -- OUT NUMBER
536 );
537 IF (AMS_DEBUG_HIGH_ON) THEN
538 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
539 END IF;
540
541 IF l_return_status = FND_API.g_ret_sts_error THEN
542 RAISE FND_API.g_exc_error;
543 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
544 RAISE FND_API.g_exc_unexpected_error;
545 END IF;
546
547 END IF;
548 END IF;
549 END IF; -- attendance flag and sysdate
550
551 END Insert_evtregs;
552
553 FUNCTION check_number_registered(p_event_offer_id IN NUMBER)
554 RETURN NUMBER
555 IS
556 cursor c_num_registered is
557 select count(*)
558 from AMS_EVENT_REGISTRATIONS
559 where event_offer_id = p_event_offer_id
560 and system_status_code = 'REGISTERED';
564 BEGIN
561
562 l_num_registered NUMBER := 0;
563
565
566 open c_num_registered;
567 fetch c_num_registered
568 into l_num_registered;
569 close c_num_registered;
570
571 return l_num_registered;
572
573 END;
574
575 FUNCTION check_number_waitlisted(p_event_offer_id IN NUMBER)
576 RETURN NUMBER
577 IS
578 cursor c_num_waitlisted is
579 select count(*)
580 from AMS_EVENT_REGISTRATIONS
581 where event_offer_id = p_event_offer_id
582 and system_status_code = 'WAITLISTED';
583
584 l_num_waitlisted NUMBER := 0;
585
586 BEGIN
587
588 open c_num_waitlisted;
589 fetch c_num_waitlisted
590 into l_num_waitlisted;
591 close c_num_waitlisted;
592
593 return l_num_waitlisted;
594
595 END;
596
597 FUNCTION check_reg_availability( p_effective_capacity IN NUMBER
598 , p_event_offer_id IN NUMBER
599 )
600 RETURN NUMBER -- return no. of seat availability
601 IS
602
603 l_availability NUMBER := 0;
604 l_max_capacity NUMBER;
605 BEGIN
606
607 if (p_effective_capacity = 0)
608 then
609 select reg_maximum_capacity into l_max_capacity --anchaudh added for bug#3119915.
610 from AMS_event_offers_all_B
611 where event_offer_id = p_event_offer_id;
612
613 if(l_max_capacity is null) then --anchaudh added for bug#3119915.
614 l_availability := 1;
615 else
616 l_availability := 0; --anchaudh :fixed bug#3101955,changed l_availability := 0 instead of l_availability := 1
617 end if;
618 else
619 l_availability := round(p_effective_capacity - check_number_registered(p_event_offer_id => p_event_offer_id));
620 end if; -- p_effective_capacity
621
622 return l_availability;
623
624 END check_reg_availability;
625
626 FUNCTION check_waitlist_availability( l_reg_waitlist_pct IN NUMBER
627 , l_effective_capacity IN NUMBER
628 , l_event_offer_id IN NUMBER
629 )
630 RETURN VARCHAR2 -- FND_API.g_true or false
631 IS
632
633 cursor c_num_waitlist_done is
634 select count(*)
635 from AMS_EVENT_REGISTRATIONS
636 where event_offer_id = l_event_offer_id
637 and system_status_code = 'WAITLISTED';
638
639 l_num_of_waitlist_done NUMBER := 0;
640 l_availability NUMBER := 0;
641
642 BEGIN
643
644 IF (l_reg_waitlist_pct is NULL)
645 THEN
646 return FND_API.g_true;
647 ELSE
648 open c_num_waitlist_done;
649 fetch c_num_waitlist_done
650 into l_num_of_waitlist_done;
651 close c_num_waitlist_done;
652
653 l_availability := (round(l_reg_waitlist_pct*l_effective_capacity/100) - l_num_of_waitlist_done);
654
655 IF (l_availability > 0)
656 THEN
657 return FND_API.g_true;
658 ELSE
659 return FND_API.g_false;
660 END IF; -- l_availabilty > 0
661 END IF; -- l_reg_waitlist_pct is NULL
662
663 END check_waitlist_availability;
664
665 FUNCTION contact_in_invite_list( p_event_offer_id IN NUMBER
666 , p_attendant_contact_id IN NUMBER
667 , p_attendant_party_id IN NUMBER
668 )
669 RETURN VARCHAR2 -- FND_API.g_true or false
670 IS
671 l_count NUMBER := 0;
672
673
674 -- soagrawa modified query for bug# 2600986
675 -- soagrawa modified the cursor for bug# 2525529
676 -- soagrawa modified the cursor on 08-jun-2004 for performance bug# 3667627
677
678 CURSOR c_exists_in_invite_list
679 IS
680 SELECT 1
681 FROM ams_list_entries le
682 , ams_act_lists al
683 WHERE al.list_used_by_id = p_event_offer_id
684 AND al.list_used_by in ('EVEO', 'EONE')
685 AND al.list_act_type = 'TARGET'
686 AND le.list_header_id = al.list_header_id
687 AND le.party_id = p_attendant_contact_id;
688
689 /*
690 CURSOR c_exists_in_invite_list
691 IS
692 SELECT COUNT(1)
693 FROM ams_list_entries le
694 WHERE EXISTS (SELECT 1
695 FROM ams_act_lists al
696 WHERE list_used_by_id = p_event_offer_id
697 AND list_used_by in ('EVEO', 'EONE')
698 AND list_act_type = 'TARGET'
699 AND le.list_header_id = al.list_header_id
700 AND le.party_id = p_attendant_contact_id );
701 */
702 /*
703 CURSOR c_exists_in_invite_list
704 IS
705 SELECT COUNT(1)
706 FROM ams_list_entries le
707 WHERE EXISTS (SELECT 1
708 FROM ams_act_lists al
709 WHERE list_used_by_id = p_event_offer_id
710 AND list_used_by in ('EVEO', 'EONE')
711 AND list_act_type = 'TARGET'
712 AND le.list_header_id = al.list_header_id
713 AND ((le.party_id = p_attendant_contact_id
714 AND le.list_entry_source_system_type = 'PERSON_LIST')
715 -- dbiswas modified the following to carry fix for NI bug# 2610067 on 16-apr-2003
716 or(le.party_id IN (SELECT subject_id -- person id
717 FROM ar.hz_relationships
721 AND subject_Type = 'PERSON'
718 WHERE party_id = p_attendant_contact_id
719 -- soagrawa 23-may-2003 fixed bug# 2949603
720 -- AND directional_flag = 'F'
722 AND object_Type = 'ORGANIZATION'
723 )
724 and le.list_entry_source_system_type ='ORGANIZATION_CONTACT_LIST')));
725 */
726
727
728 /*
729 (SELECT party_id
730 FROM ar.hz_relationships
731 WHERE subject_id = p_attendant_contact_id
732 AND subject_type = 'PERSON'
733 AND object_type = 'ORGANIZATION'
734 AND object_id = le.col147
735 AND object_id = p_attendant_party_id)
736 and le.list_entry_source_system_type ='ORGANIZATION_CONTACT_LIST')));
737 */
738 /* select count(1)
739 from ams_list_entries le
740 where exists (select 1
741 from ams_act_lists al
742 where list_used_by_id = p_event_offer_id
743 and list_used_by in ('EVEO', 'EONE')
744 and list_act_type = 'TARGET'
745 and le.list_header_id = al.list_header_id
746 and le.party_id = p_attendant_contact_id
747 );
748 /*
749 and exists (select 1
750 from hz_relationships re
751 where subject_id = p_attendant_contact_id
752 and subject_type = 'PERSON'
753 and le.party_id = re.party_id
754 );
755 */
756
757 BEGIN
758
759 -- get invite list for event offer id and verify l_attendant_contact_id is in the invite list *
760 IF (AMS_DEBUG_HIGH_ON) THEN
761
762 AMS_Utility_PVT.Debug_Message('Checking if contact is in invite list');
763 END IF;
764 IF (AMS_DEBUG_HIGH_ON) THEN
765
766 AMS_Utility_PVT.Debug_Message('(event_offer_id, attendant_contact_id) = (' || p_event_offer_id || ',' || p_attendant_contact_id || ')');
767 END IF;
768
769 open c_exists_in_invite_list;
770 fetch c_exists_in_invite_list
771 into l_count;
772 close c_exists_in_invite_list;
773
774 IF (AMS_DEBUG_HIGH_ON) THEN
775
776
777
778 AMS_Utility_PVT.Debug_Message('l_count: ' || l_count);
779
780 END IF;
781
782 IF (l_count > 0)
783 THEN
784 return FND_API.g_true;
785 END IF; -- l_count > 0
786
787 return FND_API.g_false;
788
789 END contact_in_invite_list;
790
791 -- SUB PART OF FK VALIDATION BELOW
792 PROCEDURE check_registrant_fk_info( p_registrant_party_id IN NUMBER
793 , p_registrant_contact_id IN NUMBER
794 , p_registrant_account_id IN NUMBER
795 , x_return_status OUT NOCOPY VARCHAR2
796 )
797 IS
798
799 BEGIN
800
801 x_return_status := FND_API.g_ret_sts_success;
802
803 ------------------------- registrant party id -------------------
804 IF ( (p_registrant_party_id is NOT NULL)
805 OR (p_registrant_party_id <> FND_API.g_miss_num)
806 )
807 THEN
808 IF (AMS_Utility_PVT.check_fk_exists( 'hz_parties'
809 , 'party_id'
810 , p_registrant_party_id
811 ) = FND_API.g_false
812 )
813 THEN
814 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_EVT_REG_BAD_PARTY_ID');
815 x_return_status := FND_API.g_ret_sts_error;
816 RETURN;
817 END IF; -- check_fk_exists
818 END IF; -- p_registrant_party_id
819
820 /*
821 -------------------------registration CONTACT id-------------------
822 IF p_registrant_contact_id IS NOT NULL OR p_registrant_contact_id <> FND_API.g_miss_num THEN
823 IF AMS_Utility_PVT.check_fk_exists(
824 'hz_org_contacts',
825 'org_contact_id',
826 p_registrant_contact_id,
827 AMS_Utility_PVT.g_number,
828 'party_relationship_id ='|| p_registrant_party_id
829 ) = FND_API.g_false
830 THEN
831 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
832 THEN
833 FND_MESSAGE.set_name('AMS', 'AMS_EVT_REG_BAD_CONTACT_ID');
834 FND_MSG_PUB.add;
835 END IF;
836
837 x_return_status := FND_API.g_ret_sts_error;
838 --RETURN;
839 END IF;
840 END IF;
841 */
842
843 -------------------------registration account id-------------------
844 IF ( (p_registrant_account_id IS NOT NULL)
845 AND (p_registrant_account_id <> FND_API.g_miss_num)
846 )
847 THEN
848 IF (AMS_Utility_PVT.check_fk_exists( 'hz_cust_accounts'
849 , 'cust_account_id'
850 , p_registrant_account_id
851 , AMS_Utility_PVT.g_number
852 , 'party_id ='|| p_registrant_party_id
853 ) = FND_API.g_false
854 )
855 THEN
856 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_EVT_REG_BAD_ACCOUNT_ID');
860 END IF; -- p_registrant_account_id
857 x_return_status := FND_API.g_ret_sts_error;
858 RETURN;
859 END IF; -- check_fk_exists
861
862 END check_registrant_fk_info;
863
864 PROCEDURE transfer_insert( p_Api_Version_Number IN NUMBER
865 , p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
866 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
867 , p_old_offer_id IN NUMBER
868 , p_new_offer_id IN NUMBER
869 , p_system_status_code IN VARCHAR2
870 , p_reg_status_date IN DATE
871 , p_old_confirmation_code IN VARCHAR2
872 , p_registrant_account_id IN NUMBER
873 , p_registrant_party_id IN NUMBER
874 , p_registrant_contact_id IN NUMBER
875 , p_attendant_party_id IN NUMBER
876 , p_attendant_contact_id IN NUMBER
877 , x_new_confirmation_code OUT NOCOPY VARCHAR2
878 , x_new_system_status_code OUT NOCOPY VARCHAR2
879 , x_new_registration_id OUT NOCOPY NUMBER
880 , x_Return_Status OUT NOCOPY VARCHAR2
881 , x_Msg_Count OUT NOCOPY NUMBER
882 , x_Msg_Data OUT NOCOPY VARCHAR2
883 )
884 IS
885
886 l_return_status VARCHAR2(1);
887 l_api_name CONSTANT VARCHAR2(30) := 'transfer_insert';
888 l_api_version_number CONSTANT NUMBER := 1.0;
889 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
890
891 CURSOR c_reg IS
892 SELECT
893 EVENT_REGISTRATION_ID,
894 LAST_UPDATE_DATE,
895 LAST_UPDATED_BY,
896 CREATION_DATE,
897 CREATED_BY,
898 LAST_UPDATE_LOGIN,
899 OBJECT_VERSION_NUMBER,
900 EVENT_OFFER_ID,
901 APPLICATION_ID,
902 ACTIVE_FLAG,
903 OWNER_USER_ID ,
904 SYSTEM_STATUS_CODE,
905 DATE_REGISTRATION_PLACED,
906 USER_STATUS_ID,
907 LAST_REG_STATUS_DATE,
908 REG_SOURCE_TYPE_CODE,
909 REGISTRATION_SOURCE_ID,
910 CONFIRMATION_CODE,
911 SOURCE_CODE,
912 REGISTRATION_GROUP_ID,
913 REGISTRANT_PARTY_ID,
914 REGISTRANT_CONTACT_ID,
915 REGISTRANT_ACCOUNT_ID,
916 ATTENDANT_PARTY_ID,
917 ATTENDANT_CONTACT_ID,
918 ATTENDANT_ACCOUNT_ID,
919 ORIGINAL_REGISTRANT_CONTACT_ID,
920 PROSPECT_FLAG,
921 ATTENDED_FLAG,
922 CONFIRMED_FLAG,
923 EVALUATED_FLAG,
924 null,
925 ATTENDANCE_RESULT_CODE,
926 WAITLISTED_PRIORITY,
927 TARGET_LIST_ID,
928 INBOUND_MEDIA_ID,
929 INBOUND_CHANNEL_ID,
930 CANCELLATION_CODE,
931 CANCELLATION_REASON_CODE,
932 ATTENDANCE_FAILURE_REASON,
933 ATTENDANT_LANGUAGE,
934 SALESREP_ID,
935 ORDER_HEADER_ID,
936 ORDER_LINE_ID,
937 DESCRIPTION,
938 MAX_ATTENDEE_OVERRIDE_FLAG,
939 INVITE_ONLY_OVERRIDE_FLAG,
940 PAYMENT_STATUS_CODE,
941 AUTO_REGISTER_FLAG,
942 ATTRIBUTE_CATEGORY,
943 ATTRIBUTE1 ,
944 ATTRIBUTE2 ,
945 ATTRIBUTE3 ,
946 ATTRIBUTE4 ,
947 ATTRIBUTE5 ,
948 ATTRIBUTE6 ,
949 ATTRIBUTE7 ,
950 ATTRIBUTE8 ,
951 ATTRIBUTE9 ,
952 ATTRIBUTE10,
953 ATTRIBUTE11,
954 ATTRIBUTE12,
955 ATTRIBUTE13,
956 ATTRIBUTE14,
957 ATTRIBUTE15,
958 attendee_role_type, -- Hornet : added for imeeting integration
959 notification_type, -- Hornet : added for imeeting integration
960 last_notified_time, -- Hornet : added for imeeting integration
961 EVENT_JOIN_TIME, -- Hornet : added for imeeting integration
962 EVENT_EXIT_TIME, -- Hornet : added for imeeting integration
963 MEETING_ENCRYPTION_KEY_CODE -- Hornet : added for imeeting integration
964 FROM ams_event_registrations
965 WHERE confirmation_code = p_old_confirmation_code
966 and event_offer_id = p_old_offer_id;
967
968 l_evt_regs_rec evt_regs_rec_type;
969
970 BEGIN
971
972 -- Standard Start of API savepoint
973 SAVEPOINT transfer_insert_PVT;
974 IF (AMS_DEBUG_HIGH_ON) THEN
975
976 AMS_Utility_PVT.debug_message(l_full_name||': start');
977 END IF;
978
979 IF (FND_API.to_boolean(p_init_msg_list))
980 THEN
981 FND_MSG_PUB.initialize;
982 END IF; -- p_init_msg_list
983
984 -- Standard call to check for call compatibility.
985 IF (NOT FND_API.Compatible_API_Call( l_api_version_number
986 , p_api_version_number
987 , l_api_name
988 , G_PKG_NAME
989 )
990 )
991 THEN
992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
993 END IF; -- compatible API call
994
995 -- Initialize API return status to SUCCESS
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997
998 OPEN c_reg;
999 FETCH c_reg
1000 INTO l_evt_regs_rec;
1001 IF (c_reg%NOTFOUND)
1002 THEN
1003 CLOSE c_reg;
1004 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RECORD_NOT_FOUND');
1008
1005 RAISE FND_API.g_exc_error;
1006 END IF; -- c_reg%NOTFOUND
1007 CLOSE c_reg;
1009
1010 -- validate the registrant fk info....
1011 check_registrant_fk_info( p_registrant_party_id
1012 , p_registrant_contact_id
1013 , p_registrant_account_id
1014 , l_return_status
1015 );
1016
1017 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
1018 THEN
1019 RAISE FND_API.g_exc_unexpected_error;
1020 ELSIF (l_return_status = FND_API.g_ret_sts_error)
1021 THEN
1022 RAISE FND_API.g_exc_error;
1023 END IF; -- l_return_status
1024
1025 -- make changes to the record...
1026 l_evt_regs_rec.event_registration_id := NULL;
1027 l_evt_regs_rec.confirmation_code := NULL;
1028 l_evt_regs_rec.object_version_number := NULL;
1029 l_evt_regs_rec.system_status_code := NULL;
1030 l_evt_regs_rec.event_offer_id := p_new_offer_id;
1031 l_evt_regs_rec.date_registration_placed := NULL;
1032 l_evt_regs_rec.last_reg_status_date := p_reg_status_date;
1033
1034 if (p_registrant_contact_id IS NOT NULL)
1035 then
1036 l_evt_regs_rec.ORIGINAL_REGISTRANT_CONTACT_ID := l_evt_regs_rec.REGISTRANT_CONTACT_ID;
1037 end if; -- p_registrant_contact_id
1038
1039 l_evt_regs_rec.REGISTRANT_ACCOUNT_ID := nvl(p_registrant_account_id,l_evt_regs_rec.REGISTRANT_ACCOUNT_ID);
1040 l_evt_regs_rec.REGISTRANT_PARTY_ID := nvl(p_registrant_party_id,l_evt_regs_rec.REGISTRANT_PARTY_ID);
1041 l_evt_regs_rec.REGISTRANT_CONTACT_ID := nvl(p_registrant_contact_id,l_evt_regs_rec.REGISTRANT_CONTACT_ID);
1042 l_evt_regs_rec.ATTENDANT_PARTY_ID := nvl(p_attendant_party_id,l_evt_regs_rec.ATTENDANT_PARTY_ID);
1043 l_evt_regs_rec.ATTENDANT_CONTACT_ID := nvl(p_attendant_contact_id,l_evt_regs_rec.ATTENDANT_CONTACT_ID);
1044
1045 Insert_evtRegs( l_evt_regs_rec
1046 , p_system_status_code
1047 , FND_API.G_FALSE
1048 , x_new_confirmation_code
1049 , x_new_registration_id
1050 );
1051 x_new_system_status_code := p_system_status_code;
1052 -- should i be committing before cancel_evtregs call....or just use cancel_evtregs commit call
1053 -- something wrong happens...rollback...called in transfer and validate....return l_return status
1054
1055 IF FND_API.to_boolean(p_commit)
1056 THEN
1057 COMMIT;
1058 END IF; -- p_commit
1059
1060 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1061 , p_count => x_msg_count
1062 , p_data => x_msg_data
1063 );
1064
1065 IF (AMS_DEBUG_HIGH_ON) THEN
1066
1067
1068
1069 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1070
1071 END IF;
1072
1073 EXCEPTION
1074
1075 WHEN FND_API.g_exc_error
1076 THEN
1077 ROLLBACK TO transfer_insert_PVT;
1078 x_return_status := FND_API.g_ret_sts_error;
1079 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1080 , p_count => x_msg_count
1081 , p_data => x_msg_data
1082 );
1083
1084 WHEN FND_API.g_exc_unexpected_error
1085 THEN
1086 ROLLBACK TO transfer_insert_PVT;
1087 x_return_status := FND_API.g_ret_sts_unexp_error;
1088 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1089 , p_count => x_msg_count
1090 , p_data => x_msg_data
1091 );
1092
1093 WHEN OTHERS
1094 THEN
1095 ROLLBACK TO transfer_insert_PVT;
1096 x_return_status := FND_API.g_ret_sts_unexp_error;
1097
1098 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1099 THEN
1100 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1101 END IF; -- check_msg_level
1102
1103 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1104 , p_count => x_msg_count
1105 , p_data => x_msg_data
1106 );
1107
1108 END transfer_insert;
1109
1110 -- 2/16/00 sugupta- added function Get_Reg_Rec to get rec type for telesales
1111 FUNCTION Get_Reg_Rec
1112 RETURN AMS_EvtRegs_PVT.evt_regs_Rec_Type
1113 IS
1114 TMP_REC AMS_EvtRegs_PVT.evt_regs_Rec_Type;
1115 BEGIN
1116 RETURN TMP_REC;
1117 END Get_Reg_Rec;
1118
1119
1120 PROCEDURE Create_evtregs( P_Api_Version_Number IN NUMBER
1121 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1122 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
1123 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1124 , p_evt_regs_rec IN evt_regs_Rec_Type
1125 , p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
1126 , x_event_registration_id OUT NOCOPY NUMBER
1127 , x_confirmation_code OUT NOCOPY VARCHAR2
1128 , x_system_status_code OUT NOCOPY VARCHAR2
1129 , x_return_status OUT NOCOPY VARCHAR2
1130 , x_msg_count OUT NOCOPY NUMBER
1131 , x_msg_data OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134
1135 l_api_name CONSTANT VARCHAR2(30) := 'Create_evtregs';
1136 l_api_version_number CONSTANT NUMBER := 1.0;
1137 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1141 l_invite_only_override_flag VARCHAR2(1) := NVL(l_evt_regs_Rec.invite_only_override_flag, 'N');
1138 l_return_status VARCHAR2(1);
1139 l_evt_regs_rec evt_regs_rec_type := P_evt_regs_Rec;
1140 l_event_offer_id NUMBER := l_evt_regs_Rec.event_offer_id;
1142 l_attendant_party_id NUMBER := l_evt_regs_Rec.attendant_party_id;
1143 l_attendant_contact_id NUMBER := l_evt_regs_Rec.attendant_contact_id;
1144 l_max_attendee_override_flag VARCHAR2(1) := l_evt_regs_Rec.max_attendee_override_flag;
1145 l_waitlist_flag VARCHAR2(1) := l_evt_regs_Rec.waitlisted_flag;
1146 l_invited_only_flag VARCHAR2(1) := 'N';
1147 l_waitlist_allowed_flag VARCHAR2(1) := 'N';
1148 l_reg_required_flag VARCHAR2(1);
1149 l_reg_frozen_flag VARCHAR2(1);
1150 l_effective_capacity NUMBER;
1151 l_reg_waitlist_pct NUMBER;
1152 l_system_status_code VARCHAR2(30);
1153 l_reg_end_date DATE;
1154 l_reg_start_date DATE;
1155 Cursor get_offer_details(l_event_offer_id NUMBER) is
1156 select
1157 REG_INVITED_ONLY_FLAG,
1158 REG_WAITLIST_ALLOWED_FLAG,
1159 REG_REQUIRED_FLAG,
1160 REG_FROZEN_FLAG,
1161 REG_EFFECTIVE_CAPACITY,
1162 REG_WAITLIST_PCT,
1163 reg_start_date,
1164 reg_end_date
1165 from ams_event_offers_all_b
1166 where EVENT_OFFER_ID = l_event_offer_id;
1167
1168
1169 BEGIN
1170
1171 -- Standard Start of API savepoint
1172 SAVEPOINT CREATE_EvtRegs_PVT;
1173 IF (AMS_DEBUG_HIGH_ON) THEN
1174
1175 AMS_Utility_PVT.debug_message(l_full_name||': start');
1176 END IF;
1177
1178 IF FND_API.to_boolean(p_init_msg_list)
1179 THEN
1180 FND_MSG_PUB.initialize;
1181 END IF; -- p_init_msg_list
1182
1183 -- Standard call to check for call compatibility.
1184 IF (NOT FND_API.Compatible_API_Call( l_api_version_number
1185 , p_api_version_number
1186 , l_api_name
1187 , G_PKG_NAME
1188 )
1189 )
1190 THEN
1191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1192 END IF; -- compatible API call
1193
1194 -- Initialize API return status to SUCCESS
1195 x_return_status := FND_API.G_RET_STS_SUCCESS;
1196
1197 -- Validate Environment
1198 IF (AMS_DEBUG_HIGH_ON) THEN
1199
1200 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1201 END IF;
1202
1203 Validate_evtregs( p_api_version_number => l_api_version_number
1204 , p_init_msg_list => p_init_msg_list
1205 , p_validation_level => p_validation_level
1206 , P_evt_regs_Rec => l_evt_regs_Rec
1207 , p_validation_mode => JTF_PLSQL_API.g_create
1208 , x_return_status => l_return_status
1209 , x_msg_count => x_msg_count
1210 , x_msg_data => x_msg_data
1211 );
1212
1213 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1214 THEN
1215 RAISE FND_API.G_EXC_ERROR;
1216 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error
1217 THEN
1218 RAISE FND_API.g_exc_unexpected_error;
1219 END IF; -- l_return_status
1220
1221 -----------------------insert------------------
1222 IF (AMS_DEBUG_HIGH_ON) THEN
1223
1224 AMS_Utility_PVT.debug_message(l_full_name || ': insert');
1225 END IF;
1226
1227 IF (l_event_offer_id is NULL)
1228 then
1229 -- already validated
1230 IF (AMS_DEBUG_HIGH_ON) THEN
1231
1232 AMS_UTILITY_PVT.debug_message('Corresponding event offering information is not provided');
1233 END IF;
1234 ELSE
1235
1236 open get_offer_details(l_event_offer_id);
1237 fetch get_offer_details
1238 into
1239 l_invited_only_flag,
1240 l_waitlist_allowed_flag,
1241 l_reg_required_flag,
1242 l_reg_frozen_flag,
1243 l_effective_capacity,
1244 l_reg_waitlist_pct,
1245 l_reg_start_date,
1246 l_reg_end_date;
1247 close get_offer_details;
1248
1249 IF (AMS_DEBUG_HIGH_ON) THEN
1250
1251
1252
1253 AMS_UTILITY_PVT.debug_message('l_invited_only_flag: ' || l_invited_only_flag);
1254
1255 END IF;
1256 IF (AMS_DEBUG_HIGH_ON) THEN
1257
1258 AMS_UTILITY_PVT.debug_message('l_invite_only_override_flag: ' || l_invite_only_override_flag);
1259 END IF;
1260
1261 -- soagrawa modified call out to contact_in_invite_list for bug# 2600986
1262 IF (l_invited_only_flag = 'Y')
1263 THEN
1264 IF ( (l_invite_only_override_flag = 'N')
1265 AND (contact_in_invite_list( l_event_offer_id
1266 , l_attendant_contact_id
1267 , l_attendant_party_id
1268 ) = FND_API.g_false
1269 )
1270 )
1271 THEN
1272 IF (AMS_DEBUG_HIGH_ON) THEN
1273
1274 AMS_UTILITY_PVT.debug_message('The attendant is not on the invite list');
1275 END IF;
1276 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NOT_INVITED');
1277 x_return_status := FND_API.g_ret_sts_error;
1278 RAISE FND_API.G_EXC_ERROR;
1279 ELSE
1283 , p_system_status_code => l_system_status_code
1280 l_system_status_code := 'REGISTERED';
1281
1282 Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
1284 , p_block_fulfillment => p_block_fulfillment
1285 , x_confirmation_code => x_confirmation_code
1286 , x_event_registration_id => x_event_registration_id
1287 );
1288
1289 x_system_status_code := 'REGISTERED';
1290 END IF; -- invite only override flag
1291 ELSE -- INVITE ONLY FLAG IS NO
1292 IF (l_reg_required_flag = 'N')
1293 THEN
1294 IF (AMS_DEBUG_HIGH_ON) THEN
1295
1296 AMS_UTILITY_PVT.DEBUG_MESSAGE ('Registration for this event offering is not required');
1297 END IF;
1298 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NOT_REQ');
1299 x_return_status := FND_API.g_ret_sts_error;
1300 RAISE FND_API.G_EXC_ERROR;
1301 ELSE --reg required flag is Y
1302 IF (sysdate < l_reg_start_date OR sysdate > l_reg_end_date OR (l_reg_start_date is null AND l_reg_end_date is null))
1303 THEN
1304 IF (AMS_DEBUG_HIGH_ON) THEN
1305 AMS_UTILITY_PVT.debug_message('Registrations for this event is not open');
1306 END IF;
1307 AMS_UTILITY_PVT.error_message('AMS_EVENT_UNAVAIL_FOR_REG');
1308 x_return_status := FND_API.g_ret_sts_error;
1309 RAISE FND_API.G_EXC_ERROR;
1310 END IF;
1311
1312 IF (l_reg_frozen_flag = 'Y')
1313 THEN
1314 IF (AMS_DEBUG_HIGH_ON) THEN
1315
1316 AMS_UTILITY_PVT.debug_message('Registrations for this event offering are no longer accepted');
1317 END IF;
1318 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_FROZEN');
1319 x_return_status := FND_API.g_ret_sts_error;
1320 RAISE FND_API.G_EXC_ERROR;
1321 END IF;
1322
1323 IF ( (l_max_attendee_override_flag = 'Y')
1324 OR (l_waitlist_flag = 'N')
1325 OR ( (nvl(l_waitlist_flag, 'X') = 'X')
1326 AND (check_reg_availability(l_effective_capacity, l_event_offer_id) > 0)
1327 )
1328 )
1329 THEN
1330 l_system_status_code := 'REGISTERED';
1331 Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
1332 , p_system_status_code => l_system_status_code
1333 , p_block_fulfillment => p_block_fulfillment
1334 , x_confirmation_code => x_confirmation_code
1335 , x_event_registration_id => x_event_registration_id
1336 );
1337 x_system_status_code := 'REGISTERED';
1338
1339 ELSE -- check for waitlist
1340 IF ( (l_waitlist_allowed_flag = 'N')
1341 AND (nvl(l_waitlist_flag, 'X') <> 'Y')
1342 )
1343 THEN
1344 IF (AMS_DEBUG_HIGH_ON) THEN
1345
1346 AMS_UTILITY_PVT.debug_message('Registrations sold out. Waitlist not allowed for this event offering');
1347 END IF;
1348 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_WAIT_ALLOWED');
1349 x_return_status := FND_API.g_ret_sts_error;
1350 RAISE FND_API.G_EXC_ERROR;
1351 ELSE -- wailist allowed
1352 -- if (l_reg_waitlist_pct is NOT NULL)
1353 -- then
1354 if ( (nvl(l_waitlist_flag, 'X') <> 'Y')
1355 AND (check_waitlist_availability( l_reg_waitlist_pct
1356 , l_effective_capacity
1357 , l_event_offer_id
1358 ) = FND_API.g_false
1359 )
1360 )
1361 THEN
1362 IF (AMS_DEBUG_HIGH_ON) THEN
1363
1364 AMS_UTILITY_PVT.debug_message('Eff Capacity:' || l_effective_capacity || 'and wait:' || l_reg_waitlist_pct);
1365 END IF;
1366 IF (AMS_DEBUG_HIGH_ON) THEN
1367
1368 AMS_UTILITY_PVT.debug_message('Waiting list for this event offer ing is full');
1369 END IF;
1370 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_WAIT_AVAILABLE');
1371 x_return_status := FND_API.g_ret_sts_error;
1372 RAISE FND_API.G_EXC_ERROR;
1373 end if; -- check_waitlist_availability
1374 -- end if; -- l_reg_waitlist_pct
1375 l_system_status_code := 'WAITLISTED';
1376 Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
1377 , p_system_status_code => l_system_status_code
1378 , p_block_fulfillment => p_block_fulfillment
1379 , x_confirmation_code => x_confirmation_code
1380 , x_event_registration_id => x_event_registration_id
1381 );
1382 x_system_status_code := 'WAITLISTED';
1383
1384 END IF; -- wailist allowed
1385 END IF; -- check reg else waitlist availability
1386 END IF; -- Reg required flag is Y else N
1387 END IF; -- invite only flag is Y else N
1388 END IF; -- event offer id not null
1389
1390 IF FND_API.to_boolean(p_commit)
1391 THEN
1392 COMMIT;
1393 END IF; -- p_commit
1394
1398 );
1395 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1396 , p_count => x_msg_count
1397 , p_data => x_msg_data
1399
1400 IF (AMS_DEBUG_HIGH_ON) THEN
1401
1402
1403
1404 AMS_Utility_PVT.debug_message(l_full_name || ': end');
1405
1406 END IF;
1407
1408 EXCEPTION
1409
1410 WHEN FND_API.g_exc_error
1411 THEN
1412 ROLLBACK TO CREATE_EvtRegs_PVT;
1413 x_return_status := FND_API.g_ret_sts_error;
1414 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1415 , p_count => x_msg_count
1416 , p_data => x_msg_data
1417 );
1418
1419 WHEN FND_API.g_exc_unexpected_error
1420 THEN
1421 ROLLBACK TO CREATE_EvtRegs_PVT;
1422 x_return_status := FND_API.g_ret_sts_unexp_error ;
1423 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1424 , p_count => x_msg_count
1425 , p_data => x_msg_data
1426 );
1427
1428 WHEN OTHERS
1429 THEN
1430 ROLLBACK TO CREATE_EvtRegs_PVT;
1431 x_return_status := FND_API.g_ret_sts_unexp_error ;
1432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1433 THEN
1434 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1435 END IF;
1436 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1437 , p_count => x_msg_count
1438 , p_data => x_msg_data
1439 );
1440
1441 End Create_evtregs;
1442
1443 PROCEDURE UPDATE_evtregs_wrapper( P_Api_Version_Number IN NUMBER
1444 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1445 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
1446 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1447 , P_evt_regs_Rec IN evt_regs_Rec_Type
1448 , p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
1449 , p_cancellation_reason_code IN VARCHAR2 := NULL
1450 , x_cancellation_code OUT NOCOPY VARCHAR2
1451 , X_Return_Status OUT NOCOPY VARCHAR2
1452 , X_Msg_Count OUT NOCOPY NUMBER
1453 , X_Msg_Data OUT NOCOPY VARCHAR2
1454 )
1455
1456 IS
1457
1458 l_api_name CONSTANT VARCHAR2(30) := 'Update_evtregs_wrapper';
1459 l_api_version_number CONSTANT NUMBER := 1.0;
1460 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1461 l_return_status VARCHAR2(1);
1462 l_cancel_id NUMBER;
1463 l_cancellation_reason_code VARCHAR2(30) := nvl( p_cancellation_reason_code
1464 , P_evt_regs_Rec.cancellation_reason_code
1465 );
1466
1467 CURSOR c_reg IS
1468 SELECT *
1469 FROM ams_event_registrations
1470 WHERE event_registration_id = p_evt_regs_rec.event_registration_id;
1471
1472 Cursor c_cancel_status_id is
1473 select user_status_id
1474 from ams_user_statuses_vl
1475 where system_status_type = 'AMS_EVENT_REG_STATUS'
1476 and system_status_code = 'CANCELLED'
1477 and default_flag = 'Y';
1478
1479 l_evt_regs_Rec c_reg%ROWTYPE;
1480
1481 BEGIN
1482
1483 -- Standard Start of API savepoint
1484 SAVEPOINT UPDATE_evtregs_wrapper;
1485
1486 -- Standard call to check for call compatibility.
1487 IF (NOT FND_API.Compatible_API_Call( l_api_version_number
1488 , p_api_version_number
1489 , l_api_name
1490 , G_PKG_NAME
1491 )
1492 )
1493 THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 END IF; -- compatible API call
1496
1497
1498 -- Initialize message list if p_init_msg_list is set to TRUE.
1499 IF (FND_API.to_Boolean(p_init_msg_list))
1500 THEN
1501 FND_MSG_PUB.initialize;
1502 END IF; -- p_init_msg_list
1503
1504 -- Initialize API return status to SUCCESS
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506
1507 OPEN c_reg;
1508 FETCH c_reg
1509 INTO l_evt_regs_rec;
1510 IF c_reg%NOTFOUND THEN
1511 CLOSE c_reg;
1512 AMS_UTILITY_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
1513 RAISE FND_API.g_exc_error;
1514 END IF; -- c_reg%NOTFOUND
1515 CLOSE c_reg;
1516
1517 open c_cancel_status_id;
1518 fetch c_cancel_status_id
1519 into l_cancel_id;
1520 close c_cancel_status_id;
1521
1522 -- soagrawa added on 29-jan-2003
1523 /* IF ((l_evt_regs_rec.user_status_id = p_evt_regs_rec.user_status_id
1524 OR p_evt_regs_rec.user_status_id = FND_API.g_miss_num)
1525 AND (l_evt_regs_rec.attended_flag = p_evt_regs_rec.attended_flag
1526 OR p_evt_regs_rec.attended_flag = FND_API.g_miss_char)
1527 AND (l_evt_regs_rec.reg_source_type_code = p_evt_regs_rec.reg_source_type_code
1531 AMS_UTILITY_PVT.debug_message('Nothing changed');
1528 OR p_evt_regs_rec.reg_source_type_code = FND_API.g_miss_char)
1529 )
1530 THEN
1532 RETURN;
1533 ELSE
1534 AMS_UTILITY_PVT.debug_message('Something changed');
1535 END IF;
1536 */
1537
1538 -- soagrawa 29-jan-2003 modified the following code for bug# 2775357
1539 IF ( ( l_evt_regs_rec.user_status_id = l_cancel_id
1540 OR p_evt_regs_Rec.user_status_id = l_cancel_id)
1541 AND p_evt_regs_Rec.attended_flag = 'Y')
1542 THEN
1543 IF p_evt_regs_Rec.attended_flag <> l_evt_regs_Rec.attended_flag
1544 AND l_evt_regs_rec.user_status_id <> p_evt_regs_rec.user_status_id
1545 THEN
1546 -- trying to update both attended flag to Y and status to cancel
1547 -- A registrant who has already attended cannot have his registration cancelled.
1548 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_ATTENDED_1');
1549 RAISE FND_API.g_exc_error;
1550
1551 ELSIF l_evt_regs_rec.user_status_id <> p_evt_regs_rec.user_status_id
1552 THEN
1553 -- trying to update status to cancel when attended flag is already Y
1554 -- A registrant who has already attended cannot have his registration cancelled.
1555 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_ATTENDED_1');
1556 RAISE FND_API.g_exc_error;
1557
1558 ELSIF p_evt_regs_Rec.attended_flag <> l_evt_regs_Rec.attended_flag
1559 THEN
1560 -- trying to update attended flag to Y when status is already cancelled
1561 -- Cannot update the Attended flag for the Registrant whose Registration Status is Cancelled.
1562 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_ATTENDED');
1563 RAISE FND_API.g_exc_error;
1564 END IF;
1565 END IF;
1566
1567
1568
1569 IF ( (l_evt_regs_rec.user_status_id <> l_cancel_id)
1570 AND (p_evt_regs_rec.user_status_id = l_cancel_id)
1571 )
1572 THEN
1573 cancel_evtregs( p_api_version_number => p_api_version_number
1574 , p_init_msg_list => p_init_msg_list
1575 , p_commit => p_commit
1576 , p_object_version => p_evt_regs_rec.object_version_number
1577 , p_event_offer_id => l_evt_regs_rec.event_offer_id
1578 , p_registrant_party_id => l_evt_regs_rec.registrant_party_id
1579 , p_confirmation_code => l_evt_regs_rec.confirmation_code
1580 , p_registration_group_id => l_evt_regs_rec.registration_group_id
1581 , p_cancellation_reason_code => l_cancellation_reason_code
1582 , p_block_fulfillment => p_block_fulfillment
1583 , x_cancellation_code => x_cancellation_code
1584 , x_return_status => x_return_status
1585 , x_msg_count => x_msg_count
1586 , x_msg_data => x_msg_data
1587 );
1588 ELSE
1589 update_evtregs( p_api_version_number => p_api_version_number
1590 , p_init_msg_list => p_init_msg_list
1591 , p_commit => p_commit
1592 , p_validation_level => p_validation_level
1593 , p_evt_regs_rec => p_evt_regs_rec
1594 , p_block_fulfillment => p_block_fulfillment
1595 , x_return_status => x_return_status
1596 , x_msg_count => x_msg_count
1597 , x_msg_data => x_msg_data
1598 );
1599 END IF; -- l_cancel_id
1600
1601 EXCEPTION
1602
1603 WHEN FND_API.g_exc_error
1604 THEN
1605 ROLLBACK TO UPDATE_evtregs_wrapper;
1606 x_return_status := FND_API.g_ret_sts_error;
1607 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1608 , p_count => x_msg_count
1609 , p_data => x_msg_data
1610 );
1611
1612 WHEN FND_API.g_exc_unexpected_error
1613 THEN
1614 ROLLBACK TO UPDATE_evtregs_wrapper;
1615 x_return_status := FND_API.g_ret_sts_unexp_error ;
1616 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1617 , p_count => x_msg_count
1618 , p_data => x_msg_data
1619 );
1620
1621 WHEN OTHERS
1622 THEN
1623 ROLLBACK TO UPDATE_evtregs_wrapper;
1624 x_return_status := FND_API.g_ret_sts_unexp_error ;
1625 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1626 THEN
1627 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1628 END IF;
1629 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
1630 , p_count => x_msg_count
1631 , p_data => x_msg_data
1632 );
1633 END Update_evtregs_wrapper;
1634
1635 PROCEDURE Update_evtregs( P_Api_Version_Number IN NUMBER
1636 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1637 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
1638 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1639 , P_evt_regs_Rec IN evt_regs_Rec_Type
1640 , p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
1641 , X_Return_Status OUT NOCOPY VARCHAR2
1642 , X_Msg_Count OUT NOCOPY NUMBER
1643 , X_Msg_Data OUT NOCOPY VARCHAR2
1644 )
1645
1646 IS
1647
1651 l_evt_regs_Rec evt_regs_Rec_Type;
1648 l_api_name CONSTANT VARCHAR2(30) := 'Update_evtregs';
1649 l_api_version_number CONSTANT NUMBER := 1.0;
1650 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1652 l_return_status VARCHAR2(1);
1653 l_msg_count NUMBER;
1654 l_msg_data VARCHAR2(2000);
1655 l_old_reg_status VARCHAR2(30);
1656 l_event_capacity NUMBER;
1657 l_event_status VARCHAR2(30);
1658 l_event_status_name VARCHAR2(120);
1659
1660 /* Following code is modified by ptendulk to move to 1:1 ffm
1661 l_email AMF_REQUEST.string_tbl_type;
1662 l_fax AMF_REQUEST.string_tbl_type;
1663 l_bind_values AMF_REQUEST.string_tbl_type;
1664 l_party_id AMF_REQUEST.number_tbl_type;
1665 */
1666
1667 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1668 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1669 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
1670 l_bind_names JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1671 l_bind_values JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1672
1673
1674
1675 l_object_type VARCHAR2(30);
1676 l_end_date DATE;
1677 l_request_history_id NUMBER;
1678
1679 CURSOR c_get_object_type(p_event_offer_id IN NUMBER) IS
1680 SELECT event_object_type,
1681 trunc(event_end_date) + 1,
1682 parent_type
1683 from ams_event_offers_all_b
1684 where event_offer_id = p_event_offer_id;
1685
1686 cursor c_old_reg_status(p_reg_id NUMBER) is
1687 select system_status_code
1688 from ams_event_registrations
1689 where event_registration_id = p_reg_id;
1690
1691 cursor c_event_details(p_event_id NUMBER) is
1692 select e.reg_effective_capacity,
1693 e.system_status_code,
1694 u.name
1695 from ams_event_offers_all_b e,
1696 ams_user_statuses_vl u
1697 where e.event_offer_id = p_event_id
1698 and e.user_status_id = u.user_status_id;
1699
1700 cursor c_get_status_code(p_status_id IN NUMBER) IS
1701 select system_status_code
1702 from ams_user_statuses_b
1703 where user_status_id = p_status_id
1704 and system_status_type = 'AMS_EVENT_REG_STATUS';
1705
1706 -- soagrawa 18-nov-2002 for bug# 2672928
1707 l_csch_id NUMBER;
1708 l_object_id NUMBER;
1709 l_parent_type VARCHAR2(10);
1710
1711 CURSOR c_csch_id (obj_id NUMBER) IS
1712 SELECT schedule_id
1713 FROM ams_campaign_schedules_b
1714 WHERE related_event_id = obj_id;
1715
1716
1717 BEGIN
1718
1719 -- Standard Start of API savepoint
1720 SAVEPOINT UPDATE_EvtRegs_PVT;
1721
1722 -- Standard call to check for call compatibility.
1723 IF (NOT FND_API.Compatible_API_Call( l_api_version_number
1724 , p_api_version_number
1725 , l_api_name
1726 , G_PKG_NAME
1727 )
1728 )
1729 THEN
1730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1731 END IF; -- compatible API call
1732
1733
1734 -- Initialize message list if p_init_msg_list is set to TRUE.
1735 IF (FND_API.to_Boolean(p_init_msg_list))
1736 THEN
1737 FND_MSG_PUB.initialize;
1738 END IF; -- p_init_msg_list
1739
1740 -- Initialize API return status to SUCCESS
1741 x_return_status := FND_API.G_RET_STS_SUCCESS;
1742
1743 -- add complete_evtreg_rec to complete rec with existing values instead of empty FND_API.g_miss_char
1744 -- replace g_miss_char/num/date with current column values
1745 IF (AMS_DEBUG_HIGH_ON) THEN
1746
1747 AMS_Utility_PVT.debug_message(l_full_name ||': complete');
1748 END IF;
1749 complete_evtreg_rec(P_evt_regs_Rec, l_evt_regs_Rec);
1750
1751 -- not now...unique key val provided...sugupta:todo- check for unique key before calling
1752 ----------------------- validate ----------------------
1753 IF (AMS_DEBUG_HIGH_ON) THEN
1754
1755 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1756 END IF;
1757
1758 -- Invoke validation procedures
1759 Validate_evtregs( p_api_version_number => l_api_version_number
1760 , p_init_msg_list => FND_API.G_FALSE
1761 , p_validation_level => p_validation_level
1762 , P_evt_regs_Rec => l_evt_regs_Rec
1763 , p_validation_mode => JTF_PLSQL_API.g_update
1764 , x_return_status => l_return_status
1765 , x_msg_count => x_msg_count
1766 , x_msg_data => x_msg_data
1767 );
1768
1769 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
1770 THEN
1771 RAISE FND_API.g_exc_unexpected_error;
1772 ELSIF (l_return_status = FND_API.g_ret_sts_error)
1773 THEN
1774 RAISE FND_API.g_exc_error;
1775 END IF; -- l_return_status
1776
1777 OPEN c_old_reg_status(l_evt_regs_Rec.event_registration_id);
1778 FETCH c_old_reg_status
1779 INTO l_old_reg_status;
1780 CLOSE c_old_reg_status;
1781
1782 OPEN c_event_details(l_evt_regs_Rec.event_offer_id);
1783 FETCH c_event_details
1784 INTO l_event_capacity,
1785 l_event_status,
1786 l_event_status_name;
1787 CLOSE c_event_details;
1788
1789 IF (nvl(l_event_status, 'X') in ('ARCHIVED', 'CLOSED'))
1790 THEN
1791 AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
1792 RAISE FND_API.g_exc_error;
1793 END IF;
1794
1798 INTO l_evt_regs_rec.system_status_code;
1795 -- make sure the status code matches the user status id
1796 OPEN c_get_status_code(l_evt_regs_Rec.user_status_id);
1797 FETCH c_get_status_code
1799 CLOSE c_get_status_code;
1800
1801 IF ( (l_old_reg_status = 'WAITLISTED')
1802 AND (l_evt_regs_Rec.system_status_code = 'REGISTERED')
1803 AND (l_evt_regs_Rec.max_attendee_override_flag <> 'Y')
1804 AND (check_reg_availability( l_event_capacity
1805 , l_evt_regs_Rec.event_offer_id
1806 ) <= 0
1807 )
1808 )
1809 THEN
1810 AMS_UTILITY_PVT.error_message('AMS_EVT_REG_NO_WAIT_AVAILABLE');
1811 RAISE FND_API.g_exc_error;
1812 END IF;
1813
1814 IF ( (l_old_reg_status = 'WAITLISTED')
1815 AND (l_evt_regs_Rec.system_status_code = 'REGISTERED')
1816 )
1817 THEN
1818 l_evt_regs_Rec.WAITLISTED_PRIORITY := null;
1819 END IF;
1820 -------------------------- update --------------------
1821 IF (AMS_DEBUG_HIGH_ON) THEN
1822
1823 AMS_Utility_PVT.debug_message(l_full_name || ': update');
1824 END IF;
1825
1826 update AMS_EVENT_REGISTRATIONS set
1827 LAST_UPDATE_DATE = sysdate,
1828 LAST_UPDATED_BY = FND_GLOBAL.user_id,
1829 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
1830 OBJECT_VERSION_NUMBER = l_evt_regs_Rec.OBJECT_VERSION_NUMBER + 1,
1831 EVENT_OFFER_ID = l_evt_regs_Rec.EVENT_OFFER_ID,
1832 APPLICATION_ID = l_evt_regs_Rec.APPLICATION_ID,
1833 ACTIVE_FLAG = nvl(l_evt_regs_Rec.ACTIVE_FLAG,'Y'),
1834 OWNER_USER_ID = l_evt_regs_Rec.OWNER_USER_ID,
1835 DATE_REGISTRATION_PLACED = nvl(l_evt_regs_Rec.DATE_REGISTRATION_PLACED,sysdate),
1836 USER_STATUS_ID = l_evt_regs_Rec.USER_STATUS_ID,
1837 SYSTEM_STATUS_CODE = l_evt_regs_Rec.SYSTEM_STATUS_CODE,
1838 LAST_REG_STATUS_DATE = nvl(l_evt_regs_Rec.LAST_REG_STATUS_DATE, sysdate),
1839 REG_SOURCE_TYPE_CODE = l_evt_regs_Rec.REG_SOURCE_TYPE_CODE,
1840 REGISTRATION_SOURCE_ID = l_evt_regs_Rec.REGISTRATION_SOURCE_ID,
1841 CONFIRMATION_CODE = l_evt_regs_Rec.CONFIRMATION_CODE,
1842 SOURCE_CODE = l_evt_regs_Rec.SOURCE_CODE,
1843 REGISTRATION_GROUP_ID = l_evt_regs_Rec.REGISTRATION_GROUP_ID,
1844 REGISTRANT_PARTY_ID = l_evt_regs_Rec.REGISTRANT_PARTY_ID,
1845 REGISTRANT_CONTACT_ID = l_evt_regs_Rec.REGISTRANT_CONTACT_ID,
1846 ATTENDANT_PARTY_ID = l_evt_regs_Rec.ATTENDANT_PARTY_ID,
1847 ATTENDANT_CONTACT_ID = l_evt_regs_Rec.ATTENDANT_CONTACT_ID,
1848 ORIGINAL_REGISTRANT_CONTACT_ID = l_evt_regs_Rec.ORIGINAL_REGISTRANT_CONTACT_ID,
1849 PROSPECT_FLAG = l_evt_regs_Rec.PROSPECT_FLAG,
1850 ATTENDED_FLAG = l_evt_regs_Rec.ATTENDED_FLAG,
1851 CONFIRMED_FLAG = l_evt_regs_Rec.CONFIRMED_FLAG,
1852 EVALUATED_FLAG = l_evt_regs_Rec.EVALUATED_FLAG,
1853 ATTENDANCE_RESULT_CODE = l_evt_regs_Rec.ATTENDANCE_RESULT_CODE,
1854 WAITLISTED_PRIORITY = l_evt_regs_Rec.WAITLISTED_PRIORITY,
1855 TARGET_LIST_ID = l_evt_regs_Rec.TARGET_LIST_ID,
1856 INBOUND_MEDIA_ID = l_evt_regs_Rec.INBOUND_MEDIA_ID,
1857 INBOUND_CHANNEL_ID = l_evt_regs_Rec.INBOUND_CHANNEL_ID,
1858 CANCELLATION_CODE = l_evt_regs_Rec.CANCELLATION_CODE,
1859 CANCELLATION_REASON_CODE = l_evt_regs_Rec.CANCELLATION_REASON_CODE,
1860 ATTENDANCE_FAILURE_REASON = l_evt_regs_Rec.ATTENDANCE_FAILURE_REASON,
1861 ATTENDANT_LANGUAGE = l_evt_regs_Rec.ATTENDANT_LANGUAGE,
1862 SALESREP_ID = l_evt_regs_Rec.SALESREP_ID,
1863 ORDER_HEADER_ID = l_evt_regs_Rec.ORDER_HEADER_ID,
1864 ORDER_LINE_ID = l_evt_regs_Rec.ORDER_LINE_ID,
1865 DESCRIPTION = l_evt_regs_Rec.DESCRIPTION,
1866 MAX_ATTENDEE_OVERRIDE_FLAG = l_evt_regs_Rec.MAX_ATTENDEE_OVERRIDE_FLAG,
1867 INVITE_ONLY_OVERRIDE_FLAG = l_evt_regs_Rec.INVITE_ONLY_OVERRIDE_FLAG,
1868 PAYMENT_STATUS_CODE = l_evt_regs_Rec.PAYMENT_STATUS_CODE,
1869 AUTO_REGISTER_FLAG = l_evt_regs_Rec.AUTO_REGISTER_FLAG,
1870 ATTRIBUTE_CATEGORY = l_evt_regs_Rec.ATTRIBUTE_CATEGORY,
1871 ATTRIBUTE1 = l_evt_regs_Rec.ATTRIBUTE1,
1872 ATTRIBUTE2 = l_evt_regs_Rec.ATTRIBUTE2,
1873 ATTRIBUTE3 = l_evt_regs_Rec.ATTRIBUTE3,
1874 ATTRIBUTE4 = l_evt_regs_Rec.ATTRIBUTE4,
1875 ATTRIBUTE5 = l_evt_regs_Rec.ATTRIBUTE5,
1876 ATTRIBUTE6 = l_evt_regs_Rec.ATTRIBUTE6,
1877 ATTRIBUTE7 = l_evt_regs_Rec.ATTRIBUTE7,
1878 ATTRIBUTE8 = l_evt_regs_Rec.ATTRIBUTE8,
1879 ATTRIBUTE9 = l_evt_regs_Rec.ATTRIBUTE9,
1880 ATTRIBUTE10 = l_evt_regs_Rec.ATTRIBUTE10,
1881 ATTRIBUTE11 = l_evt_regs_Rec.ATTRIBUTE11,
1882 ATTRIBUTE12 = l_evt_regs_Rec.ATTRIBUTE12,
1883 ATTRIBUTE13 = l_evt_regs_Rec.ATTRIBUTE13,
1884 ATTRIBUTE14 = l_evt_regs_Rec.ATTRIBUTE14,
1885 ATTRIBUTE15 = l_evt_regs_Rec.ATTRIBUTE15,
1886 attendee_role_type = l_evt_regs_rec.attendee_role_type, -- Hornet : added for imeeting integration
1887 notification_type = l_evt_regs_rec.notification_type, -- Hornet : added for imeeting integration
1888 last_notified_time = l_evt_regs_rec.last_notified_time, -- Hornet : added for imeeting integration
1889 EVENT_JOIN_TIME = l_evt_regs_rec.EVENT_JOIN_TIME, -- Hornet : added for imeeting integration
1890 EVENT_EXIT_TIME = l_evt_regs_rec.EVENT_EXIT_TIME, -- Hornet : added for imeeting integration
1891 MEETING_ENCRYPTION_KEY_CODE = l_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE -- Hornet : added for imeeting integration
1892 where EVENT_REGISTRATION_ID = l_evt_regs_Rec.EVENT_REGISTRATION_ID
1893 and object_version_number = l_evt_regs_Rec.object_version_number;
1894
1895 IF (SQL%NOTFOUND) THEN
1896 AMS_UTILITY_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
1897 RAISE FND_API.g_exc_error;
1898 END IF; -- SQL%NOTFOUND
1899
1900 IF ( (l_old_reg_status = 'WAITLISTED')
1901 AND (l_evt_regs_Rec.system_status_code = 'REGISTERED')
1902 )
1903 THEN
1904
1905 open c_get_object_type(l_evt_regs_rec.event_offer_id);
1906 fetch c_get_object_type
1907 into l_object_type,
1908 l_end_date,
1909 l_parent_type;
1910 close c_get_object_type;
1911
1912 -- soagrawa 18-nov-2002 for bug# 2672928
1913 l_object_id := l_evt_regs_rec.event_offer_id;
1914
1915 IF l_object_type = 'EONE'
1916 THEN
1917 IF l_parent_type = 'CAMP'
1918 THEN
1919
1920 OPEN c_csch_id(l_object_id);
1921 FETCH c_csch_id INTO l_csch_id;
1922 CLOSE c_csch_id;
1923
1924 l_object_type := 'CSCH';
1925 l_object_id := l_csch_id;
1926 END IF;
1927 END IF;
1928 -- end soagrawa 18-nov-2002
1929
1930
1931 IF ( (nvl(l_evt_regs_rec.ATTENDED_FLAG, 'N') = 'N')
1932 AND (sysdate < l_end_date)
1933 )
1934 THEN
1935
1936 write_interaction( p_event_offer_id => l_evt_regs_rec.EVENT_OFFER_ID
1937 -- dbiswas 16-apr-2003 for NI issue with interactions (part of 2610067)
1938 -- , p_party_id => l_evt_regs_rec.ATTENDANT_PARTY_ID
1939 , p_party_id => l_evt_regs_rec.ATTENDANT_CONTACT_ID
1940 );
1941
1942 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
1943 l_bind_values(0) := to_char(l_evt_regs_Rec.EVENT_REGISTRATION_ID);
1944 l_bind_values(1) := to_char(l_evt_regs_Rec.EVENT_REGISTRATION_ID); */
1945 l_bind_names(1) := 'REGISTRATION_ID' ;
1946 l_bind_values(1) := TO_CHAR(l_evt_regs_Rec.event_registration_id);
1947
1948
1949 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
1950 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
1951 )
1952 THEN
1953 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
1954 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
1955 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
1956 , p_object_type => l_object_type -- IN VARCHAR2
1957 , p_object_id => l_object_id -- l_evt_regs_rec.event_offer_id -- IN NUMBER
1958 , p_trigger_type => 'REG_CONFIRM' -- IN VARCHAR2
1959 -- , p_requestor_type => -- IN VARCHAR2 := NULL
1960 -- Following line is modified by ptendulk on 12-Dec-2002
1961 , p_requestor_id => get_user_id(l_evt_regs_rec.OWNER_USER_ID)
1962 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_evt_regs_rec.OWNER_USER_ID) -- IN NUMBER
1963 -- , p_server_group => -- IN NUMBER := NULL
1964 -- , p_scheduled_date => -- IN DATE := SYSDATE
1965 -- , p_media_types => -- IN VARCHAR2 := 'E'
1966 -- , p_archive => -- IN VARCHAR2 := 'N'
1967 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
1968 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
1969 -- , p_language_code => -- IN VARCHAR2 := NULL
1970 -- , p_profile_id => -- IN NUMBER := NULL
1971 -- , p_order_id => -- IN NUMBER := NULL
1972 -- , p_collateral_id => -- IN NUMBER := NULL
1973 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
1974 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
1975 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
1976 -- Following line is added by ptendulk on 12-Dec-2002
1977 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
1978 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
1982 , x_request_history_id => l_request_history_id -- OUT NUMBER
1979 , x_return_status => l_return_status -- OUT VARCHAR2
1980 , x_msg_count => l_msg_count -- OUT NUMBER
1981 , x_msg_data => l_msg_data -- OUT VARCHAR2
1983 );
1984 IF (AMS_DEBUG_HIGH_ON) THEN
1985 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
1986 END IF;
1987
1988 IF l_return_status = FND_API.g_ret_sts_error THEN
1989 RAISE FND_API.g_exc_error;
1990 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1991 RAISE FND_API.g_exc_unexpected_error;
1992 END IF;
1993
1994 END IF;
1995 END IF; -- ATTENDANCE FLAG
1996 END IF;
1997
1998
1999 -- Standard check for p_commit
2000 IF FND_API.to_Boolean( p_commit )
2001 THEN
2002 COMMIT WORK;
2003 END IF; -- p_commit
2004
2005 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2006 , p_count => x_msg_count
2007 , p_data => x_msg_data
2008 );
2009
2010 IF (AMS_DEBUG_HIGH_ON) THEN
2011
2012
2013
2014 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2015
2016 END IF;
2017
2018
2019 EXCEPTION
2020
2021 WHEN FND_API.g_exc_error
2022 THEN
2023 ROLLBACK TO UPDATE_EvtRegs_PVT;
2024 x_return_status := FND_API.g_ret_sts_error;
2025 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2026 , p_count => x_msg_count
2027 , p_data => x_msg_data
2028 );
2029
2030 WHEN FND_API.g_exc_unexpected_error
2031 THEN
2032 ROLLBACK TO UPDATE_EvtRegs_PVT;
2033 x_return_status := FND_API.g_ret_sts_unexp_error ;
2034 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2035 , p_count => x_msg_count
2036 , p_data => x_msg_data
2037 );
2038
2039 WHEN OTHERS
2040 THEN
2041 ROLLBACK TO UPDATE_EvtRegs_PVT;
2042 x_return_status := FND_API.g_ret_sts_unexp_error ;
2043 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2044 THEN
2045 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2046 END IF; -- check_msg_level
2047 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2048 , p_count => x_msg_count
2049 , p_data => x_msg_data
2050 );
2051
2055 -- return cancellation code as Varchar2
2052 End Update_evtregs;
2053
2054 -- modified sugupta 06/21/2000
2056
2057 PROCEDURE Cancel_evtregs( P_Api_Version_Number IN NUMBER
2058 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2059 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
2060 , p_object_version IN NUMBER
2061 , p_event_offer_id IN NUMBER
2062 , p_registrant_party_id IN NUMBER
2063 , p_confirmation_code IN VARCHAR2
2064 , p_registration_group_id IN NUMBER
2065 , p_cancellation_reason_code IN VARCHAR2
2066 , p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
2067 , x_cancellation_code OUT NOCOPY VARCHAR2
2068 , X_Return_Status OUT NOCOPY VARCHAR2
2069 , X_Msg_Count OUT NOCOPY NUMBER
2070 , X_Msg_Data OUT NOCOPY VARCHAR2
2071 )
2072
2073 IS
2074
2075 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_evtregs';
2076 l_api_version_number CONSTANT NUMBER := 1.0;
2077 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2078 l_return_status VARCHAR2(1);
2079 l_msg_count NUMBER;
2080 l_msg_data VARCHAR2(2000);
2081 l_event_registration_id NUMBER;
2082 l_event_offer_id NUMBER := p_event_offer_id;
2083 l_registrant_party_id NUMBER := p_registrant_party_id;
2084 l_confirmation_code VARCHAR2(30) := p_confirmation_code;
2085 l_registration_group_id NUMBER := p_registration_group_id;
2086 l_cancellation_id NUMBER;
2087 l_cancellation_code VARCHAR2(30);
2088 l_cancellation_reason_code VARCHAR2(30) := p_cancellation_reason_code;
2089 l_reg_charge_flag VARCHAR2(1);
2090 l_attended_flag VARCHAR2(1);
2091 l_event_end_date DATE;
2092 l_event_end_date_time DATE;
2093 l_reg_end_date DATE;
2094 l_reg_end_time DATE;
2095 l_user_stat_id NUMBER;
2096 l_event_status VARCHAR2(30);
2097 l_event_status_name VARCHAR2(120);
2098
2099 /* Following code is modified by ptendulk to move to 1:1 ffm
2100 l_email AMF_REQUEST.string_tbl_type;
2101 l_fax AMF_REQUEST.string_tbl_type;
2102 l_bind_values AMF_REQUEST.string_tbl_type;
2103 l_party_id AMF_REQUEST.number_tbl_type;
2104 */
2105
2106 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2107 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2108 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
2109 l_bind_names JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2110 l_bind_values JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2111
2112 l_object_type VARCHAR2(30);
2113 l_request_history_id NUMBER;
2114
2115 l_owner_user_id NUMBER;
2116
2117 CURSOR c_get_object_type(p_event_offer_id IN NUMBER) IS
2118 SELECT event_object_type, parent_type
2119 from ams_event_offers_all_b
2120 where event_offer_id = p_event_offer_id;
2121
2122
2123 /* what to do with registrant_party_id...not validating coz transfer calls
2124 this procedure and they might not give the registrant party id..*/
2125 /* alternate cursor cancel_get_offer_details wud be not to use
2126 event_registration_id , and write update cancel reg where clause
2127 as in where clause of this cursor, instead of using event_registration_id
2128 */
2129 -- indeed i need not get offer_id as confirmation code should be enuf to get all information..
2130
2131 l_csch_id NUMBER;
2132 l_object_id NUMBER;
2133 l_parent_type VARCHAR2(10);
2134
2135 CURSOR c_csch_id (obj_id NUMBER) IS
2136 SELECT schedule_id
2137 FROM ams_campaign_schedules_b
2138 WHERE related_event_id = obj_id;
2139
2140
2141 Cursor cancel_get_offer_details( l_event_offer_id NUMBER
2142 , l_confirmation_code VARCHAR2
2143 , l_registrant_party_id NUMBER
2144 )
2145 is
2146 select
2147 reg.event_registration_id,
2148 -- soagrawa 03-feb-2003 bug# 2777302, now getting owner user id from event
2149 -- reg.owner_user_id,
2150 offers.owner_user_id,
2151 offers.system_status_code,
2152 usrsts.name,
2153 reg.attended_flag,
2154 offers.REG_CHARGE_FLAG,
2155 offers.EVENT_END_DATE,
2156 offers.EVENT_END_DATE_TIME,
2157 offers.REG_END_DATE,
2158 offers.REG_END_TIME
2159 from ams_event_offers_all_b offers, ams_event_registrations reg, ams_user_statuses_vl usrsts
2160 where offers.EVENT_OFFER_ID = l_event_offer_id
2161 and reg.EVENT_OFFER_ID = l_event_offer_id
2162 and reg.CONFIRMATION_CODE = l_confirmation_code
2163 and nvl(l_registrant_party_id, reg.REGISTRANT_PARTY_ID) = reg.REGISTRANT_PARTY_ID
2164 and offers.user_status_id = usrsts.user_status_id;
2165
2166 Cursor cancel_reg_conf_details( l_registration_group_id NUMBER
2167 , l_event_offer_id NUMBER
2168 )
2169 is
2170 select
2171 REGISTRANT_PARTY_ID,
2172 CONFIRMATION_CODE
2173 from ams_event_registrations
2174 where EVENT_OFFER_ID = l_event_offer_id
2175 and REGISTRATION_GROUP_ID = l_registration_group_id;
2176
2177 l_cancel_reg_conf_data cancel_reg_conf_details%ROWTYPE;
2178
2182
2179 CURSOR c_evtregs_cancel_seq IS
2180 select ams_event_reg_cancellation_s.nextval
2181 from dual;
2183 Cursor cur_user_stat_id is
2184 select USER_STATUS_ID
2185 from AMS_USER_STATUSES_VL
2186 where SYSTEM_STATUS_CODE = 'CANCELLED'
2187 and DEFAULT_FLAG = 'Y'
2188 and SYSTEM_STATUS_TYPE = 'AMS_EVENT_REG_STATUS';
2189
2190 BEGIN
2191
2192 -- Standard Start of API savepoint
2193 SAVEPOINT Cancel_EvtRegs_PVT;
2194 IF (AMS_DEBUG_HIGH_ON) THEN
2195
2196 AMS_Utility_PVT.debug_message(l_full_name || ': start');
2197 END IF;
2198
2199 -- Standard call to check for call compatibility.
2200 IF (NOT FND_API.Compatible_API_Call( l_api_version_number
2201 , p_api_version_number
2202 , l_api_name
2203 , G_PKG_NAME
2204 )
2205 )
2206 THEN
2207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2208 END IF; -- compatible API call
2209
2210 -- Initialize message list if p_init_msg_list is set to TRUE.
2211 IF (FND_API.to_Boolean(p_init_msg_list))
2212 THEN
2213 FND_MSG_PUB.initialize;
2214 END IF; -- p_init_msg_list
2215
2216 -- Initialize API return status to SUCCESS
2217 x_return_status := FND_API.G_RET_STS_SUCCESS;
2218
2219 IF (AMS_DEBUG_HIGH_ON) THEN
2220 AMS_Utility_PVT.debug_message(l_full_name || ': cancel');
2221 END IF;
2222
2223 open cur_user_stat_id;
2224 FETCH cur_user_stat_id
2225 INTO l_user_stat_id;
2226 close cur_user_stat_id;
2227
2228 open c_get_object_type(p_event_offer_id);
2229 fetch c_get_object_type
2230 into l_object_type, l_parent_type;
2231 close c_get_object_type;
2232
2233
2234 -- soagrawa 18-nov-2002 for bug# 2672928
2235 l_object_id := p_event_offer_id;
2236
2237 IF l_object_type = 'EONE'
2238 THEN
2239 IF l_parent_type = 'CAMP'
2240 THEN
2241
2242 OPEN c_csch_id(l_object_id);
2243 FETCH c_csch_id INTO l_csch_id;
2244 CLOSE c_csch_id;
2245
2246 l_object_type := 'CSCH';
2247 l_object_id := l_csch_id;
2248 END IF;
2249 END IF;
2250 -- end soagrawa 18-nov-2002
2251
2252
2253 IF (l_event_offer_id is NULL)
2254 then
2255 IF (AMS_DEBUG_HIGH_ON) THEN
2256
2257 AMS_UTILITY_PVT.debug_message(' Corresponding event offering information is not provided');
2258 END IF;
2259 AMS_Utility_PVT.error_message('AMS_EVT_REG_CANC_NO_EVOID');
2260 x_return_status := FND_API.g_ret_sts_error;
2261 RAISE FND_API.G_EXC_ERROR;
2262 ELSE
2263 IF l_registration_group_id is NULL
2264 THEN
2265 IF l_confirmation_code is NULL
2266 THEN
2267 IF (AMS_DEBUG_HIGH_ON) THEN
2268
2269 AMS_UTILITY_PVT.debug_message(' Corresponding confirmation code for the event offering is not provided');
2270 END IF;
2271 AMS_Utility_PVT.error_message('AMS_EVT_REG_CANC_NO_CODE');
2272 x_return_status := FND_API.g_ret_sts_error;
2273 RAISE FND_API.G_EXC_ERROR;
2274 ELSE
2275 IF (AMS_DEBUG_HIGH_ON) THEN
2276
2277 AMS_Utility_PVT.debug_message(l_full_name ||': before off det cursor');
2278 END IF;
2279 open cancel_get_offer_details( l_event_offer_id
2280 , l_confirmation_code
2281 , l_registrant_party_id
2282 );
2283 fetch cancel_get_offer_details
2284 into
2285 l_event_registration_id,
2286 l_owner_user_id,
2287 l_event_status,
2288 l_event_status_name,
2289 l_attended_flag,
2290 l_reg_charge_flag,
2291 l_event_end_date,
2292 l_event_end_date_time,
2293 l_reg_end_date,
2294 l_reg_end_time;
2295 close cancel_get_offer_details;
2296 IF (AMS_DEBUG_HIGH_ON) THEN
2297
2298 AMS_Utility_PVT.debug_message(l_full_name ||': after off det cursor');
2299 END IF;
2300 IF (nvl(l_event_status, 'X') in ('ARCHIVED', 'CLOSED'))
2301 THEN
2302 AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
2303 RAISE FND_API.g_exc_error;
2304 END IF;
2305
2306 IF (l_reg_charge_flag = 'N')
2307 THEN
2308 IF l_cancellation_code IS NULL
2309 THEN
2310 OPEN c_evtregs_cancel_seq;
2311 FETCH c_evtregs_cancel_seq
2312 INTO l_cancellation_id;
2313 CLOSE c_evtregs_cancel_seq;
2314 END IF; -- l_cancellation_code
2315 l_cancellation_code := to_char(l_cancellation_id);
2316
2317 UPDATE ams_event_registrations SET
2318 system_status_code = 'CANCELLED',
2319 USER_STATUS_ID = l_user_stat_id,
2320 cancellation_code = l_cancellation_code,
2321 cancellation_reason_code = l_cancellation_reason_code,
2322 waitlisted_priority = null
2323 -- added by dbiswas for NI issue on 19-mar-2003
2324 , last_update_date = sysdate
2325 , last_updated_by = FND_GLOBAL.USER_ID
2326 , LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
2327 -- added by dbiswas to carry fix for NI issue on 16-apr-2003
2331
2328 , last_reg_status_date = sysdate
2329 WHERE event_registration_id = l_event_registration_id
2330 and object_version_number = p_object_version;
2332 IF ( (l_attended_flag = 'N')
2333 AND (sysdate < (trunc(l_event_end_date) + 1))
2334 )
2335 THEN
2336 /* Following changes are made by ptendulk on 13-Dec-2002 to implement 1:1
2337 l_bind_values(0) := to_char(l_event_registration_id);
2338 l_bind_values(1) := to_char(l_event_registration_id); */
2339
2340 l_bind_names(1) := 'REGISTRATION_ID' ;
2341 l_bind_values(1):= TO_CHAR(l_event_registration_id) ;
2342
2343 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
2344 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
2345 )
2346 THEN
2347
2348 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
2349 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
2350 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
2351 , p_object_type => l_object_type -- IN VARCHAR2
2352 , p_object_id => l_object_id --p_event_offer_id -- IN NUMBER
2353 , p_trigger_type => 'REG_CANCEL' -- IN VARCHAR2
2354 -- , p_requestor_type => -- IN VARCHAR2 := NULL
2355 -- , p_requestor_id => l_owner_user_id -- Change made by ptendulk on 12-Dec-2002
2356 , p_requestor_id => get_user_id(l_owner_user_id)
2357 -- , p_requestor_id => AMS_Utility_PVT.get_resource_id(l_OWNER_USER_ID) -- IN NUMBER
2358 -- , p_server_group => -- IN NUMBER := NULL
2359 -- , p_scheduled_date => -- IN DATE := SYSDATE
2360 -- , p_media_types => -- IN VARCHAR2 := 'E'
2361 -- , p_archive => -- IN VARCHAR2 := 'N'
2362 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
2363 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
2364 -- , p_language_code => -- IN VARCHAR2 := NULL
2365 -- , p_profile_id => -- IN NUMBER := NULL
2366 -- , p_order_id => -- IN NUMBER := NULL
2367 -- , p_collateral_id => -- IN NUMBER := NULL
2368 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
2369 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
2370 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
2371 , p_bind_names => l_bind_names -- IN Added by ptendulk on 13-Dec-2002
2372 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
2373 , x_return_status => l_return_status -- OUT VARCHAR2
2374 , x_msg_count => l_msg_count -- OUT NUMBER
2375 , x_msg_data => l_msg_data -- OUT VARCHAR2
2376 , x_request_history_id => l_request_history_id -- OUT NUMBER
2377 );
2378 IF (AMS_DEBUG_HIGH_ON) THEN
2379 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
2380 END IF;
2381
2382 IF l_return_status = FND_API.g_ret_sts_error THEN
2383 RAISE FND_API.g_exc_error;
2384 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2385 RAISE FND_API.g_exc_unexpected_error;
2386 END IF;
2387
2388 END IF;
2389 END IF; -- attended flag and sysdate
2390 x_cancellation_code := l_cancellation_code;
2391 -- call to prioritize_waillist upon cancellation
2392 prioritize_waitlist( p_api_version_number => p_api_version_number
2393 , p_Init_Msg_List => p_Init_Msg_List
2394 , p_commit => p_commit
2395 , p_event_offer_id => l_event_offer_id
2396 , X_Return_Status => l_return_status
2397 , X_Msg_Count => x_msg_count
2398 , X_Msg_Data => x_msg_data
2399 );
2400 IF l_return_status = FND_API.g_ret_sts_unexp_error
2401 THEN
2402 RAISE FND_API.g_exc_unexpected_error;
2403 ELSIF l_return_status = FND_API.g_ret_sts_error
2404 THEN
2405 RAISE FND_API.g_exc_error;
2406 END IF; -- l_return_status
2407
2408 ELSE
2409 IF (nvl(l_event_end_date, sysdate+1) < sysdate)
2410 then
2411 IF (AMS_DEBUG_HIGH_ON) THEN
2412
2413 AMS_UTILITY_PVT.debug_message(' Cannot cancel a PAID event if cancellation date is later than Event end date.');
2414 END IF;
2415 ELSE
2416 IF (l_cancellation_code IS NULL)
2417 THEN
2418 OPEN c_evtregs_cancel_seq;
2419 FETCH c_evtregs_cancel_seq
2420 INTO l_cancellation_id;
2421 CLOSE c_evtregs_cancel_seq;
2422 END IF; -- l_cancellation_code
2423 l_cancellation_code := to_char(l_cancellation_id);
2424 UPDATE ams_event_registrations SET
2425 system_status_code = 'CANCELLED',
2426 USER_STATUS_ID = l_user_stat_id,
2427 cancellation_code = l_cancellation_code,
2428 cancellation_reason_code = l_cancellation_reason_code,
2429 waitlisted_priority = null
2430 -- added by dbiswas for NI issue on 19-mar-2003
2431 , last_update_date = sysdate
2432 , last_updated_by = FND_GLOBAL.USER_ID
2433 , LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
2434 -- added by dbiswas to carry fix for NI issue on 16-apr-2003
2435 , last_reg_status_date = sysdate
2436 WHERE event_registration_id = l_event_registration_id
2437 and object_version_number = p_object_version;
2438
2439 IF ( (l_attended_flag = 'N')
2440 AND (sysdate < (trunc(l_event_end_date) + 1))
2441 )
2442 THEN
2443
2444 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
2445 l_bind_values(0) := to_char(l_event_registration_id);
2446 l_bind_values(1) := to_char(l_event_registration_id); */
2447 l_bind_names(1) := 'REGISTRATION_ID' ;
2448 l_bind_values(1) := TO_CHAR(l_event_registration_id);
2449
2450 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
2451 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
2452 )
2453 THEN
2454 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
2455 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
2456 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
2457 , p_object_type => l_object_type -- IN VARCHAR2
2458 , p_object_id => l_object_id --p_event_offer_id -- IN NUMBER
2459 , p_trigger_type => 'REG_CANCEL' -- IN VARCHAR2
2460 -- , p_requestor_type => -- IN VARCHAR2 := NULL
2461 -- Following line is modified by ptendulk on 12-Dec-2002
2462 -- , p_requestor_id => l_owner_user_id
2463 , p_requestor_id => get_user_id(l_owner_user_id)
2464 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_owner_user_id) -- IN NUMBER
2465 -- , p_server_group => -- IN NUMBER := NULL
2466 -- , p_scheduled_date => -- IN DATE := SYSDATE
2467 -- , p_media_types => -- IN VARCHAR2 := 'E'
2468 -- , p_archive => -- IN VARCHAR2 := 'N'
2469 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
2470 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
2471 -- , p_language_code => -- IN VARCHAR2 := NULL
2472 -- , p_profile_id => -- IN NUMBER := NULL
2473 -- , p_order_id => -- IN NUMBER := NULL
2474 -- , p_collateral_id => -- IN NUMBER := NULL
2475 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
2476 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
2477 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
2478 -- Following line is added by ptendulk on 12-Dec-2002
2479 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
2480 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
2481 , x_return_status => l_return_status -- OUT VARCHAR2
2482 , x_msg_count => l_msg_count -- OUT NUMBER
2483 , x_msg_data => l_msg_data -- OUT VARCHAR2
2484 , x_request_history_id => l_request_history_id -- OUT NUMBER
2485 );
2486 IF (AMS_DEBUG_HIGH_ON) THEN
2487 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
2488 END IF;
2489
2490 IF l_return_status = FND_API.g_ret_sts_error THEN
2491 RAISE FND_API.g_exc_error;
2492 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2493 RAISE FND_API.g_exc_unexpected_error;
2494 END IF;
2495 END IF;
2496 END IF;
2497 x_cancellation_code := l_cancellation_code;
2498 -- call to prioritize_waillist upon cancellation
2499 prioritize_waitlist( p_api_version_number => p_api_version_number
2500 , p_Init_Msg_List => p_Init_Msg_List
2501 , p_commit => p_commit
2502 , p_event_offer_id => l_event_offer_id
2503 , X_Return_Status => l_return_status
2504 , X_Msg_Count => x_msg_count
2505 , X_Msg_Data => x_msg_data
2506 );
2507 IF l_return_status = FND_API.g_ret_sts_unexp_error
2508 THEN
2509 RAISE FND_API.g_exc_unexpected_error;
2510 ELSIF l_return_status = FND_API.g_ret_sts_error
2511 THEN
2512 RAISE FND_API.g_exc_error;
2513 END IF; -- l_return_status
2514
2515 END IF; -- event end date < sysdate
2516 END IF; -- reg charge flag
2517
2518 END IF; -- confirmation code null
2519 ELSE /* registration group id is NOT NULL */
2520 FOR cancel_reg_conf_data in cancel_reg_conf_details( l_registration_group_id
2521 , l_event_offer_id
2522 )
2523 LOOP
2524 -- might want to add another if loop to check if the reg system_status_code is already cancelled ....
2525 IF (AMS_DEBUG_HIGH_ON) THEN
2526
2527 AMS_Utility_PVT.debug_message(l_full_name ||': gp id not null-before off det cursor');
2528 END IF;
2529 -- sikalyan Fixed Bug 4185688 Updated the Conditional Check for a Registrant Group
2530 IF ( (cancel_reg_conf_data.confirmation_code IS NOT NULL)
2531 AND (cancel_reg_conf_data.registrant_party_id IS NOT NULL)
2532 AND (cancel_reg_conf_data.confirmation_code = l_confirmation_code)
2533 AND (cancel_reg_conf_data.registrant_party_id = l_registrant_party_id)
2534 )
2535 THEN
2536 open cancel_get_offer_details( l_event_offer_id
2537 , cancel_reg_conf_data.confirmation_code
2538 , cancel_reg_conf_data.registrant_party_id
2539 );
2540 fetch cancel_get_offer_details
2541 into
2542 l_event_registration_id,
2543 l_owner_user_id,
2544 l_event_status,
2545 l_event_status_name,
2546 l_attended_flag,
2547 l_reg_charge_flag,
2548 l_event_end_date,
2549 l_event_end_date_time,
2550 l_reg_end_date,
2551 l_reg_end_time;
2552 close cancel_get_offer_details;
2553 IF (AMS_DEBUG_HIGH_ON) THEN
2554
2555 AMS_Utility_PVT.debug_message(l_full_name ||': gp id not null-after off det cursor');
2556 END IF;
2557 IF (nvl(l_event_status, 'X') in ('ARCHIVED', 'CLOSED'))
2558 THEN
2559 AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
2560 RAISE FND_API.g_exc_error;
2561 END IF;
2562
2563 IF (l_reg_charge_flag = 'N')
2564 THEN
2565 IF (l_cancellation_code IS NULL)
2566 THEN
2567 OPEN c_evtregs_cancel_seq;
2568 FETCH c_evtregs_cancel_seq
2569 INTO l_cancellation_id;
2570 CLOSE c_evtregs_cancel_seq;
2571 END IF; -- l_cancellation_code
2572 l_cancellation_code := to_char(l_cancellation_id);
2573
2574 UPDATE ams_event_registrations SET
2575 system_status_code = 'CANCELLED',
2576 USER_STATUS_ID = l_user_stat_id,
2577 cancellation_code = l_cancellation_code,
2578 cancellation_reason_code = l_cancellation_reason_code,
2579 waitlisted_priority = null
2580 -- added by dbiswas for NI issue on 19-mar-2003
2581 , last_update_date = sysdate
2582 , last_updated_by = FND_GLOBAL.USER_ID
2583 , LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
2584 -- added by dbiswas to carry fix for NI issue on 16-apr-2003
2585 , last_reg_status_date = sysdate
2586 WHERE event_registration_id = l_event_registration_id
2587 and object_version_number = p_object_version;
2588
2589 IF ( (l_attended_flag = 'N')
2590 AND (sysdate < (trunc(l_event_end_date) + 1))
2591 )
2592 THEN
2593
2594 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
2595 l_bind_values(0) := to_char(l_event_registration_id);
2596 l_bind_values(1) := to_char(l_event_registration_id); */
2597 l_bind_names(1) := 'REGISTRATION_ID' ;
2598 l_bind_values(1) := TO_CHAR(l_event_registration_id);
2599
2600 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
2601 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
2602 )
2603 THEN
2604 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
2605 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
2606 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
2607 , p_object_type => l_object_type -- IN VARCHAR2
2608 , p_object_id => l_object_id --p_event_offer_id -- IN NUMBER
2609 , p_trigger_type => 'REG_CANCEL' -- IN VARCHAR2
2610 -- , p_requestor_type => -- IN VARCHAR2 := NULL
2611 -- Following line is modified by ptendulk on 12-Dec-2002
2612 -- , p_requestor_id => l_owner_user_id
2613 , p_requestor_id => get_user_id(l_owner_user_id)
2614 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_owner_user_id) -- IN NUMBER
2615 -- , p_server_group => -- IN NUMBER := NULL
2616 -- , p_scheduled_date => -- IN DATE := SYSDATE
2617 -- , p_media_types => -- IN VARCHAR2 := 'E'
2618 -- , p_archive => -- IN VARCHAR2 := 'N'
2619 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
2620 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
2621 -- , p_language_code => -- IN VARCHAR2 := NULL
2622 -- , p_profile_id => -- IN NUMBER := NULL
2623 -- , p_order_id => -- IN NUMBER := NULL
2627 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
2624 -- , p_collateral_id => -- IN NUMBER := NULL
2625 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
2626 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
2628 -- Following line is added by ptendulk on 12-Dec-2002
2629 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
2630 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
2631 , x_return_status => l_return_status -- OUT VARCHAR2
2632 , x_msg_count => l_msg_count -- OUT NUMBER
2633 , x_msg_data => l_msg_data -- OUT VARCHAR2
2634 , x_request_history_id => l_request_history_id -- OUT NUMBER
2635 );
2636 IF (AMS_DEBUG_HIGH_ON) THEN
2637 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
2638 END IF;
2639
2640 IF l_return_status = FND_API.g_ret_sts_error THEN
2641 RAISE FND_API.g_exc_error;
2642 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2643 RAISE FND_API.g_exc_unexpected_error;
2644 END IF;
2645 END IF;
2646 END IF;
2647 x_cancellation_code := l_cancellation_code;
2648 -- call to prioritize_waitlist upon cancellation
2649 prioritize_waitlist( p_api_version_number => p_api_version_number
2650 , p_Init_Msg_List => p_Init_Msg_List
2651 , p_commit => p_commit
2652 , p_event_offer_id => l_event_offer_id
2653 , X_Return_Status => l_return_status
2654 , X_Msg_Count => x_msg_count
2655 , X_Msg_Data => x_msg_data
2656 );
2657 IF l_return_status = FND_API.g_ret_sts_unexp_error
2658 THEN
2659 RAISE FND_API.g_exc_unexpected_error;
2660 ELSIF l_return_status = FND_API.g_ret_sts_error
2661 THEN
2662 RAISE FND_API.g_exc_error;
2663 END IF; -- l_return_status
2664
2665 ELSE
2666 IF (l_event_end_date < sysdate)
2667 then
2668 IF (AMS_DEBUG_HIGH_ON) THEN
2669
2670 AMS_UTILITY_PVT.debug_message('Cannot cancel a PAID event if cancellation date is later than Event end date.');
2671 END IF;
2672 IF (AMS_DEBUG_HIGH_ON) THEN
2673
2674 AMS_UTILITY_PVT.debug_message('AMS_EVT_REG_CANC_DATE');
2675 END IF;
2676 x_return_status := FND_API.g_ret_sts_error;
2677 RAISE FND_API.G_EXC_ERROR;
2678 ELSE
2679 IF (l_cancellation_code IS NULL)
2680 THEN
2681 OPEN c_evtregs_cancel_seq;
2682 FETCH c_evtregs_cancel_seq
2683 INTO l_cancellation_id;
2684 CLOSE c_evtregs_cancel_seq;
2685 END IF; -- l_cancellation_code
2686 l_cancellation_code := to_char(l_cancellation_id);
2687
2688 UPDATE ams_event_registrations SET
2689 system_status_code = 'CANCELLED',
2690 USER_STATUS_ID = l_user_stat_id,
2691 cancellation_code = l_cancellation_code,
2692 cancellation_reason_code = l_cancellation_reason_code,
2693 waitlisted_priority = null
2694 -- added by dbiswas for NI issue on 19-mar-2003
2695 , last_update_date = sysdate
2696 , last_updated_by = FND_GLOBAL.USER_ID
2697 , LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
2698 -- added by dbiswas to carry fix for NI issue on 16-apr-2003
2699 , last_reg_status_date = sysdate
2700 WHERE event_registration_id = l_event_registration_id
2701 and object_version_number = p_object_version;
2702
2703 IF ( (l_attended_flag = 'N')
2704 AND (sysdate < (trunc(l_event_end_date) + 1))
2705 )
2706 THEN
2707
2708 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
2709 l_bind_values(0) := to_char(l_event_registration_id);
2710 l_bind_values(1) := to_char(l_event_registration_id); */
2711 l_bind_names(1) := 'REGISTRATION_ID' ;
2712 l_bind_values(1) := TO_CHAR(l_event_registration_id);
2713
2714 IF ( (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
2715 AND (nvl(p_block_fulfillment, FND_API.G_FALSE) <> FND_API.G_TRUE)
2716 )
2717 THEN
2718 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
2722 , p_object_id => l_object_id --p_event_offer_id -- IN NUMBER
2719 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
2720 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
2721 , p_object_type => l_object_type -- IN VARCHAR2
2723 , p_trigger_type => 'REG_CANCEL' -- IN VARCHAR2
2724 -- , p_requestor_type => -- IN VARCHAR2 := NULL
2725 -- Following line is modified by ptendulk on 12-Dec-2002
2726 -- , p_requestor_id => l_owner_user_id
2727 , p_requestor_id => get_user_id(l_owner_user_id)
2728 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_owner_user_id) -- IN NUMBER
2729 -- , p_server_group => -- IN NUMBER := NULL
2730 -- , p_scheduled_date => -- IN DATE := SYSDATE
2731 -- , p_media_types => -- IN VARCHAR2 := 'E'
2732 -- , p_archive => -- IN VARCHAR2 := 'N'
2733 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
2734 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
2735 -- , p_language_code => -- IN VARCHAR2 := NULL
2736 -- , p_profile_id => -- IN NUMBER := NULL
2737 -- , p_order_id => -- IN NUMBER := NULL
2738 -- , p_collateral_id => -- IN NUMBER := NULL
2739 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
2740 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
2741 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
2742 -- Following line is added by ptendulk on 12-Dec-2002
2743 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
2744 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
2745 , x_return_status => l_return_status -- OUT VARCHAR2
2746 , x_msg_count => l_msg_count -- OUT NUMBER
2747 , x_msg_data => l_msg_data -- OUT VARCHAR2
2748 , x_request_history_id => l_request_history_id -- OUT NUMBER
2749 );
2750 IF (AMS_DEBUG_HIGH_ON) THEN
2751 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
2752 END IF;
2753
2754 IF l_return_status = FND_API.g_ret_sts_error THEN
2755 RAISE FND_API.g_exc_error;
2756 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2757 RAISE FND_API.g_exc_unexpected_error;
2758 END IF;
2759 END IF;
2760 END IF;
2761 x_cancellation_code := l_cancellation_code;
2762 -- call to prioritize_waitlist upon cancellation
2763 prioritize_waitlist( p_api_version_number => p_api_version_number
2764 , p_Init_Msg_List => p_Init_Msg_List
2765 , p_commit => p_commit
2766 , p_event_offer_id => l_event_offer_id
2767 , X_Return_Status => l_return_status
2768 , X_Msg_Count => x_msg_count
2769 , X_Msg_Data => x_msg_data
2770 );
2771 IF l_return_status = FND_API.g_ret_sts_unexp_error
2772 THEN
2773 RAISE FND_API.g_exc_unexpected_error;
2774 ELSIF l_return_status = FND_API.g_ret_sts_error
2775 THEN
2776 RAISE FND_API.g_exc_error;
2777 END IF; -- l_return_status
2778 END IF; -- event end date
2779 END IF; -- reg charge flag
2780 END IF; -- CONF CODE NOT NULL
2781 END LOOP; -- for cancel_reg_conf_data
2782 END IF; -- registration group id null
2783 END IF; -- event offer id
2784
2785 -- Standard check for p_commit
2786 IF (FND_API.to_Boolean(p_commit))
2787 THEN
2788 COMMIT WORK;
2789 END IF; -- p_commit
2790
2791 -- Standard call to get message count and if count is 1, get message info.
2792 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2793 , p_count => x_msg_count
2794 , p_data => x_msg_data
2795 );
2799
2796
2797 IF (AMS_DEBUG_HIGH_ON) THEN
2798
2800
2801 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2802
2803 END IF;
2804
2805 EXCEPTION
2806
2807 WHEN FND_API.g_exc_error
2808 THEN
2809 ROLLBACK TO Cancel_EvtRegs_PVT;
2810 x_return_status := FND_API.g_ret_sts_error;
2811 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2812 , p_count => x_msg_count
2813 , p_data => x_msg_data
2814 );
2815
2816 WHEN FND_API.g_exc_unexpected_error
2817 THEN
2818 ROLLBACK TO Cancel_EvtRegs_PVT;
2819 x_return_status := FND_API.g_ret_sts_unexp_error;
2820 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2821 , p_count => x_msg_count
2822 , p_data => x_msg_data
2823 );
2824
2825 WHEN OTHERS
2826 THEN
2827 ROLLBACK TO Cancel_EvtRegs_PVT;
2828 x_return_status := FND_API.g_ret_sts_unexp_error;
2829 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2830 THEN
2831 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2832 END IF; -- check_msg_level
2833 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2834 , p_count => x_msg_count
2835 , p_data => x_msg_data
2836 );
2837
2838 End Cancel_evtregs;
2839
2840 PROCEDURE lock_evtregs( p_api_version_Number IN NUMBER
2841 , p_init_msg_list IN VARCHAR2 := FND_API.g_false
2842 , p_validation_level IN NUMBER := FND_API.g_valid_level_full
2843 , x_return_status OUT NOCOPY VARCHAR2
2844 , x_msg_count OUT NOCOPY NUMBER
2845 , x_msg_data OUT NOCOPY VARCHAR2
2846 , p_event_registration_id IN NUMBER
2847 , p_object_version IN NUMBER
2848 )
2849
2850 IS
2851
2852 l_api_name CONSTANT VARCHAR2(30) := 'lock_evtregs';
2853 l_api_version_number CONSTANT NUMBER := 1.0;
2854 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2855 l_reg_id NUMBER := p_event_registration_id;
2856
2857 CURSOR c_reg_b IS
2858 SELECT event_registration_id
2859 FROM ams_event_registrations
2860 WHERE event_registration_id = l_reg_id
2861 AND object_version_number = p_object_version
2862 FOR UPDATE OF event_registration_id NOWAIT;
2863
2864 BEGIN
2865
2866 -------------------- initialize ------------------------
2867 IF (AMS_DEBUG_HIGH_ON) THEN
2868
2869 AMS_Utility_PVT.debug_message(l_full_name||': start');
2870 END IF;
2871
2872 IF FND_API.to_boolean(p_init_msg_list)
2873 THEN
2874 FND_MSG_PUB.initialize;
2875 END IF; -- p_init_msg_list
2876
2877 IF (NOT FND_API.compatible_api_call( l_api_version_number
2878 , p_api_version_number
2879 , l_api_name
2880 , g_pkg_name
2881 )
2882 )
2883 THEN
2884 RAISE FND_API.g_exc_unexpected_error;
2885 END IF; -- compatible API call
2886
2887 x_return_status := FND_API.G_RET_STS_SUCCESS;
2888
2889 ------------------------ lock -------------------------
2890 IF (AMS_DEBUG_HIGH_ON) THEN
2891
2892 AMS_Utility_PVT.debug_message(l_full_name||': lock');
2893 END IF;
2894
2895 OPEN c_reg_b;
2896 FETCH c_reg_b
2897 INTO l_reg_id;
2898 IF (c_reg_b%NOTFOUND)
2899 THEN
2900 CLOSE c_reg_b;
2901 AMS_Utility_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
2902 RAISE FND_API.g_exc_error;
2903 END IF;
2904 CLOSE c_reg_b;
2905
2906 -------------------- finish --------------------------
2907 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2908 , p_count => x_msg_count
2909 , p_data => x_msg_data
2910 );
2911
2912 IF (AMS_DEBUG_HIGH_ON) THEN
2913
2914
2915
2916 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2917
2918 END IF;
2919
2920 EXCEPTION
2921
2922 WHEN AMS_Utility_PVT.resource_locked
2923 THEN
2924 x_return_status := FND_API.g_ret_sts_error;
2925 AMS_Utility_PVT.error_message('AMS_API_RESOURCE_LOCKED');
2926 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2927 , p_count => x_msg_count
2928 , p_data => x_msg_data
2929 );
2930
2931 WHEN FND_API.g_exc_error
2932 THEN
2933 x_return_status := FND_API.g_ret_sts_error;
2934 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2935 , p_count => x_msg_count
2936 , p_data => x_msg_data
2937 );
2938
2939 WHEN FND_API.g_exc_unexpected_error
2940 THEN
2941 x_return_status := FND_API.g_ret_sts_unexp_error;
2942 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2943 , p_count => x_msg_count
2944 , p_data => x_msg_data
2945 );
2946
2947 WHEN OTHERS
2948 THEN
2952 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2949 x_return_status := FND_API.g_ret_sts_unexp_error;
2950 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
2951 THEN
2953 END IF; -- check_msg_Level
2954 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
2955 , p_count => x_msg_count
2956 , p_data => x_msg_data
2957 );
2958
2959 END lock_evtregs;
2960
2961 PROCEDURE delete_evtRegs( p_api_version_number IN NUMBER
2962 , p_init_msg_list IN VARCHAR2 := FND_API.g_false
2963 , p_commit IN VARCHAR2 := FND_API.g_false
2964 , p_object_version IN NUMBER
2965 , p_event_registration_id IN NUMBER
2966 , x_return_status OUT NOCOPY VARCHAR2
2967 , x_msg_count OUT NOCOPY NUMBER
2968 , x_msg_data OUT NOCOPY VARCHAR2
2969 )
2970
2971 IS
2972
2973 l_api_version_number CONSTANT NUMBER := 1.0;
2974 l_api_name CONSTANT VARCHAR2(30) := 'delete_evtRegs';
2975 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2976
2977 BEGIN
2978
2979 --------------------- initialize -----------------------
2980 SAVEPOINT delete_evtRegs;
2981 IF (AMS_DEBUG_HIGH_ON) THEN
2982
2983 AMS_Utility_PVT.debug_message(l_full_name||': start');
2984 END IF;
2985
2986 IF FND_API.to_boolean(p_init_msg_list)
2987 THEN
2988 FND_MSG_PUB.initialize;
2989 END IF; -- p_init_msg_list
2990
2991 IF (NOT FND_API.compatible_api_call( l_api_version_number
2992 , p_api_version_number
2993 , l_api_name
2994 , g_pkg_name
2995 )
2996 )
2997 THEN
2998 RAISE FND_API.g_exc_unexpected_error;
2999 END IF; -- compatible API call
3000
3001 x_return_status := FND_API.G_RET_STS_SUCCESS;
3002
3003 ------------------------ delete ------------------------
3004 IF (AMS_DEBUG_HIGH_ON) THEN
3005
3006 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
3007 END IF;
3008
3009 UPDATE ams_event_registrations
3010 SET active_flag = 'N'
3011 WHERE event_registration_id = p_event_registration_id
3012 AND object_version_number = p_object_version;
3013
3014 IF (SQL%NOTFOUND)
3015 THEN
3016 AMS_Utility_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
3017 RAISE FND_API.g_exc_error;
3018 END IF; -- SQL%NOTFOUND
3019
3020 -------------------- finish --------------------------
3021 IF (FND_API.to_boolean(p_commit))
3022 THEN
3023 COMMIT;
3024 END IF; -- p_commit
3025
3026 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3027 , p_count => x_msg_count
3028 , p_data => x_msg_data
3029 );
3030
3031 IF (AMS_DEBUG_HIGH_ON) THEN
3032
3033
3034
3035 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3036
3037 END IF;
3038
3039 EXCEPTION
3040
3041 WHEN FND_API.g_exc_error
3042 THEN
3043 ROLLBACK TO delete_evtRegs;
3044 x_return_status := FND_API.g_ret_sts_error;
3045 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3046 , p_count => x_msg_count
3047 , p_data => x_msg_data
3048 );
3049
3050 WHEN FND_API.g_exc_unexpected_error
3051 THEN
3052 ROLLBACK TO delete_evtRegs;
3053 x_return_status := FND_API.g_ret_sts_unexp_error ;
3054 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3055 , p_count => x_msg_count
3056 , p_data => x_msg_data
3057 );
3058
3059 WHEN OTHERS
3060 THEN
3061 ROLLBACK TO delete_evtRegs;
3062 x_return_status := FND_API.g_ret_sts_unexp_error ;
3063 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
3064 THEN
3065 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3066 END IF; -- check_msg_level
3067 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3068 , p_count => x_msg_count
3069 , p_data => x_msg_data
3070 );
3071
3072 END delete_evtRegs;
3073
3074 PROCEDURE prioritize_waitlist( p_api_version_number IN NUMBER
3075 , p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
3076 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
3077 , p_override_availability IN VARCHAR2 := FND_API.G_FALSE
3078 , p_event_offer_id IN NUMBER
3079 , x_return_status OUT NOCOPY VARCHAR2
3080 , x_msg_count OUT NOCOPY NUMBER
3081 , x_msg_data OUT NOCOPY VARCHAR2
3082 )
3083
3084 IS
3085
3086 l_api_name CONSTANT VARCHAR2(30) := 'prioritize_waitlist';
3087 l_api_version_number CONSTANT NUMBER := 1.0;
3088 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3089 l_return_status VARCHAR2(1);
3093 --l_r_auto_register_flag VARCHAR2(1);
3090 l_msg_count NUMBER;
3091 l_msg_data VARCHAR2(2000);
3092 l_e_auto_register_flag VARCHAR2(1);
3094 l_min_wait_reg_id NUMBER;
3095 l_reg_frozen_flag VARCHAR2(1);
3096 l_effective_capacity NUMBER;
3097 l_event_offer_name VARCHAR2(240);
3098 l_owner_user_id NUMBER;
3099 l_task_id NUMBER;
3100 l_task_assignment_id NUMBER;
3101 l_sales_rep_id NUMBER;
3102 l_order_hdr_id NUMBER;
3103 l_order_line_id NUMBER;
3104 l_waitlist_action_type_code VARCHAR2(30);
3105
3106 /* Following code is modified by ptendulk to move to 1:1 ffm
3107 l_email AMF_REQUEST.string_tbl_type;
3108 l_fax AMF_REQUEST.string_tbl_type;
3109 l_bind_values AMF_REQUEST.string_tbl_type;
3110 l_party_id AMF_REQUEST.number_tbl_type;
3111 */
3112
3113 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
3114 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
3115 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
3116 l_bind_names JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
3117 l_bind_values JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
3118
3119 l_object_type VARCHAR2(30);
3120 l_request_history_id NUMBER;
3121
3122 Cursor prioritize_get_offer_details(p_event_offer_id NUMBER) is
3123 select
3124 b.AUTO_REGISTER_FLAG,
3125 b.REG_FROZEN_FLAG,
3126 b.REG_EFFECTIVE_CAPACITY,
3127 b.OWNER_USER_ID,
3128 b.event_object_type,
3129 t.EVENT_OFFER_NAME,
3130 b.waitlist_action_type_code,
3131 b.parent_type
3132 from
3133 ams_event_offers_all_b b,
3134 AMS_EVENT_OFFERS_ALL_TL t
3135 where b.event_offer_id = p_event_offer_id
3136 and t.event_offer_id = p_event_offer_id;
3137
3138 Cursor c_prioritize_waiting_minnum(p_event_offer_id NUMBER) is
3139 select
3140 event_registration_id,
3141 --auto_register_flag,
3142 SALESREP_ID,
3143 ORDER_HEADER_ID,
3144 ORDER_LINE_ID
3145 from ams_event_registrations
3146 where event_offer_id = p_event_offer_id
3147 and system_status_code = 'WAITLISTED'
3148 and waitlisted_priority = (select min(waitlisted_priority)
3149 from ams_event_registrations
3150 where event_offer_id = p_event_offer_id
3151 and system_status_code = 'WAITLISTED'
3152 );
3153
3154 -- soagrawa 18-nov-2002 for bug# 2672928
3155 l_csch_id NUMBER;
3156 l_object_id NUMBER;
3157 l_parent_type VARCHAR2(10);
3158
3159 CURSOR c_csch_id (obj_id NUMBER) IS
3160 SELECT schedule_id
3161 FROM ams_campaign_schedules_b
3162 WHERE related_event_id = obj_id;
3163
3164
3165 BEGIN
3166
3167 ----------------------- initialize --------------------
3168 SAVEPOINT prioritize_waitlist;
3169 IF (AMS_DEBUG_HIGH_ON) THEN
3170
3171 AMS_Utility_PVT.debug_message(l_full_name||': start');
3172 END IF;
3173
3174 IF FND_API.to_boolean(p_init_msg_list)
3175 THEN
3176 FND_MSG_PUB.initialize;
3177 END IF; -- p_init_msg_list
3178
3179 IF (NOT FND_API.compatible_api_call( l_api_version_number
3180 , p_api_version_number
3181 , l_api_name
3182 , g_pkg_name
3183 )
3184 )
3185 THEN
3186 RAISE FND_API.g_exc_unexpected_error;
3187 END IF; -- compatible API call
3188
3189 -- Initialize API return status to SUCCESS
3190 l_return_status := FND_API.G_RET_STS_SUCCESS;
3191
3192 -- validate not null values passed for required parameters...
3193 IF (p_event_offer_id IS NULL)
3194 THEN
3195 AMS_Utility_PVT.error_message('AMS_EVT_REG_PR_NULL_PARAM');
3196 l_return_status := FND_API.g_ret_sts_error;
3197 END IF; -- p_event_offer_id
3198
3199 /* check Offer id's fk .....*/
3200 IF (AMS_Utility_PVT.check_fk_exists( 'ams_event_offers_all_b'
3201 , 'event_offer_id'
3202 , p_event_offer_id
3203 ) = FND_API.g_false
3204 )
3205 THEN
3206 AMS_Utility_PVT.error_message('AMS_EVT_REG_PR_BAD_EVOID');
3207 l_return_status := FND_API.g_ret_sts_error;
3208 -- RAISE FND_API.g_exc_error;
3209 END IF; -- check_fk_exists
3210
3211 ------------------api logic-----------------------
3212 -- Get the offering details of offer id
3213 open prioritize_get_offer_details(p_event_offer_id);
3214 fetch prioritize_get_offer_details
3215 into
3216 l_e_auto_register_flag,
3217 l_reg_frozen_flag,
3218 l_effective_capacity,
3219 l_owner_user_id,
3220 l_object_type,
3221 l_event_offer_name,
3222 l_waitlist_action_type_code,
3223 l_parent_type;
3224 close prioritize_get_offer_details;
3225
3226 -- soagrawa 18-nov-2002 for bug# 2672928
3227 l_object_id := p_event_offer_id;
3228
3229 IF l_object_type = 'EONE'
3230 THEN
3231 IF l_parent_type = 'CAMP'
3232 THEN
3233
3234 OPEN c_csch_id(l_object_id);
3235 FETCH c_csch_id INTO l_csch_id;
3236 CLOSE c_csch_id;
3237
3238 l_object_type := 'CSCH';
3239 l_object_id := l_csch_id;
3240 END IF;
3241 END IF;
3245 IF (l_reg_frozen_flag = 'Y')
3242 -- end soagrawa 18-nov-2002
3243
3244
3246 THEN
3247 IF (AMS_DEBUG_HIGH_ON) THEN
3248
3249 AMS_UTILITY_PVT.debug_message('Registrations for the event offering are frozen- Cannot prioritize your wait');
3250 END IF;
3251 -- AMS_Utility_PVT.error_message('AMS_EVT_REG_PRI_FROZEN');
3252 -- RAISE FND_API.G_EXC_ERROR;
3253 RETURN;
3254 END IF; -- l_reg_frozen_flag
3255
3256 if ( (p_override_availability = FND_API.G_FALSE)
3257 AND
3258 (check_reg_availability( l_effective_capacity
3259 , p_event_offer_id
3260 ) <= 0
3261 )
3262 )
3263 THEN
3264 IF (AMS_DEBUG_HIGH_ON) THEN
3265
3266 AMS_UTILITY_PVT.debug_message('No Waitlist Available');
3267 END IF;
3268 -- AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_WAIT_ALLOWED');
3269 -- RAISE FND_API.G_EXC_ERROR;
3270 RETURN;
3271 END IF;
3272
3273 OPEN c_prioritize_waiting_minnum(p_event_offer_id);
3274 FETCH c_prioritize_waiting_minnum
3275 into
3276 l_min_wait_reg_id,
3277 --l_r_auto_register_flag,
3278 l_sales_rep_id,
3279 l_order_hdr_id,
3280 l_order_line_id;
3281 CLOSE c_prioritize_waiting_minnum;
3282 IF (l_min_wait_reg_id IS NOT NULL)
3283 THEN
3284 IF ( (nvl(l_e_auto_register_flag,'N') = 'Y')
3285 --AND (l_r_auto_register_flag = 'N')
3286 )
3287 THEN
3288 /*
3289 -- create task for notification
3290 AMS_TASK_PVT.Create_task( p_api_version => l_api_version_number
3291 , p_init_msg_list => FND_API.g_false
3292 , p_commit => FND_API.g_false
3293 , p_task_id => NULL
3294 , p_task_name => 'wait list registration task for - '||l_event_offer_name
3295 , p_task_type_id => 15 -- from jtf_task_types_vl
3296 , p_task_status_id => 13 -- in jtf_task_statuses_vl, 13
3297 , p_task_priority_id => NULL
3298 , p_owner_id => l_owner_user_id
3299 , p_owner_type_code => 'RS_EMPLOYEE'
3300 , p_private_flag => 'N'
3301 , p_planned_start_date => NULL
3302 , p_planned_end_date => NULL
3303 , p_actual_start_date => NULL
3304 , p_actual_end_date => NULL
3305 , p_source_object_type_code => 'AMS_EVEO'
3306 , p_source_object_id => p_event_offer_id
3307 , x_return_status => l_return_status
3308 , x_msg_count => x_msg_count
3309 , x_msg_data => x_msg_data
3310 , x_task_id => l_task_id
3311 );
3312 IF (l_return_status = FND_API.g_ret_sts_error)
3313 THEN
3314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
3315 THEN
3316 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3317 END IF; -- check_msg_level
3318 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3319 , p_count => x_msg_count
3320 , p_data => x_msg_data
3321 );
3322 RAISE FND_API.g_exc_error;
3323 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error)
3324 THEN
3325 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
3326 THEN
3327 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3328 END IF; -- check_msg_level
3329 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3330 , p_count => x_msg_count
3331 , p_data => x_msg_data
3332 );
3333 RAISE FND_API.g_exc_unexpected_error;
3334 END IF; -- l_return_status
3335 -- create task assignment
3336 IF (AMS_DEBUG_HIGH_ON) THEN
3337
3338 AMS_Utility_PVT.debug_message('calling AMS_TASK_PVT.Create_Task_Assignment');
3339 END IF;
3340 AMS_TASK_PVT.Create_Task_Assignment( p_api_version => l_api_version_number
3341 , p_init_msg_list => FND_API.g_false
3342 , p_commit => FND_API.g_false
3343 , p_task_id => l_task_id
3344 , p_resource_type_code => 'RS_EMPLOYEE'
3345 , p_resource_id => l_sales_rep_id
3346 , p_assignment_status_id => 1
3347 , x_return_status => l_return_status
3348 , x_msg_count => x_msg_count
3349 , x_msg_data => x_msg_data
3350 , x_task_assignment_id => l_task_assignment_id
3351 );
3352 IF (l_return_status = FND_API.g_ret_sts_error)
3353 THEN
3357 END IF; -- check_msg_level
3354 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
3355 THEN
3356 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3358 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3359 , p_count => x_msg_count
3360 , p_data => x_msg_data
3361 );
3362 RAISE FND_API.g_exc_error;
3363 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error)
3364 THEN
3365 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
3366 THEN
3367 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3368 END IF; -- check_msg_level
3369 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3370 , p_count => x_msg_count
3371 , p_data => x_msg_data
3372 );
3373 RAISE FND_API.g_exc_unexpected_error;
3374 END IF; -- l_return_status
3375 ELSE -- l_e_auto_register_flag
3376 */
3377 update ams_event_registrations set
3378 system_status_code = 'REGISTERED',
3379 waitlisted_priority = null,
3380 user_status_id = (select user_status_id
3381 from ams_user_statuses_vl
3382 where system_status_type = 'AMS_EVENT_REG_STATUS'
3383 and system_status_code = 'REGISTERED'
3384 and default_flag = 'Y'
3385 )
3386 where event_registration_id = l_min_wait_reg_id;
3387
3388 /* Following code is modified by ptendulk on 12-Dec-2002 to move to 1:1
3389 l_bind_values(0) := to_char(l_min_wait_reg_id);
3390 l_bind_values(1) := to_char(l_min_wait_reg_id); */
3391 l_bind_names(1) := 'REGISTRATION_ID' ;
3392 l_bind_values(1) := TO_CHAR(l_min_wait_reg_id);
3393
3394
3395 IF (nvl(FND_PROFILE.value('AMS_FULFILL_ENABLE_FLAG'), 'N') = 'Y')
3396 THEN
3397 AMS_CT_RULE_PVT.check_content_rule( p_api_version => 1.0 -- IN NUMBER
3398 , p_init_msg_list => FND_API.g_false -- IN VARCHAR2 := FND_API.g_false
3399 , p_commit => FND_API.g_false-- IN VARCHAR2 := FND_API.g_false
3400 , p_object_type => l_object_type -- IN VARCHAR2
3401 , p_object_id => l_object_id --p_event_offer_id -- IN NUMBER
3402 , p_trigger_type => 'REG_CONFIRM' -- IN VARCHAR2
3403 -- , p_requestor_type => -- IN VARCHAR2 := NULL
3404 -- Following line is modified by ptendulk on 12-Dec-2002
3405 -- , p_requestor_id => l_owner_user_id
3406 , p_requestor_id => get_user_id(l_owner_user_id)
3407 --, p_requestor_id => AMS_Utility_PVT.get_resource_id(l_owner_user_id) -- IN NUMBER
3408 -- , p_server_group => -- IN NUMBER := NULL
3409 -- , p_scheduled_date => -- IN DATE := SYSDATE
3410 -- , p_media_types => -- IN VARCHAR2 := 'E'
3411 -- , p_archive => -- IN VARCHAR2 := 'N'
3412 -- , p_log_user_ih => -- IN VARCHAR2 := 'N'
3413 -- , p_request_type => 'MASS_CUSTOM' -- IN VARCHAR2 := 'TEST_EMAIL'
3414 -- , p_language_code => -- IN VARCHAR2 := NULL
3415 -- , p_profile_id => -- IN NUMBER := NULL
3416 -- , p_order_id => -- IN NUMBER := NULL
3417 -- , p_collateral_id => -- IN NUMBER := NULL
3418 , p_party_id => l_party_id -- IN AMF_REQUEST.number_tbl_type
3419 , p_email => l_email -- IN AMF_REQUEST.string_tbl_type
3420 , p_fax => l_fax -- IN AMF_REQUEST.string_tbl_type
3421 -- Following line is added by ptendulk on 12-Dec-2002
3422 , p_bind_names => l_bind_names -- IN JTF_REQUEST_GRP.G_VARCHAR_TBL_TYPE
3423 , p_bind_values => l_bind_values -- IN AMF_REQUEST.string_tbl_type
3424 , x_return_status => l_return_status -- OUT VARCHAR2
3425 , x_msg_count => l_msg_count -- OUT NUMBER
3426 , x_msg_data => l_msg_data -- OUT VARCHAR2
3427 , x_request_history_id => l_request_history_id -- OUT NUMBER
3428 );
3429 IF (AMS_DEBUG_HIGH_ON) THEN
3430 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
3431 END IF;
3432
3433 IF l_return_status = FND_API.g_ret_sts_error THEN
3434 RAISE FND_API.g_exc_error;
3435 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3436 RAISE FND_API.g_exc_unexpected_error;
3437 END IF;
3438
3439 END IF;
3440
3441 IF (AMS_DEBUG_HIGH_ON) THEN
3442
3443
3444
3445 AMS_Utility_Pvt.Debug_Message('Registered id ' || l_min_wait_reg_id);
3446
3447 END IF;
3448 END IF; -- l_e_auto_register_flag
3449 END IF; -- l_min_wait_reg_id
3450
3451 -------------FINISH-----------------------------
3452
3453 x_return_status := l_return_status;
3454
3455 -- Standard check for p_commit
3456 IF (FND_API.to_Boolean(p_commit))
3457 THEN
3458 COMMIT WORK;
3459 END IF; -- p_commit
3460
3461 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3462 , p_count => x_msg_count
3463 , p_data => x_msg_data
3464 );
3465
3466 IF (AMS_DEBUG_HIGH_ON) THEN
3467
3468
3469
3470 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3471
3472 END IF;
3473
3474 EXCEPTION
3475
3476 WHEN FND_API.g_exc_error
3477 THEN
3478 ROLLBACK TO prioritize_waitlist;
3479 x_return_status := FND_API.g_ret_sts_error;
3480 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3481 , p_count => x_msg_count
3482 , p_data => x_msg_data
3483 );
3484
3485 WHEN FND_API.g_exc_unexpected_error
3486 THEN
3487 ROLLBACK TO prioritize_waitlist;
3488 x_return_status := FND_API.g_ret_sts_unexp_error;
3489 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3490 , p_count => x_msg_count
3491 , p_data => x_msg_data
3492 );
3493
3494 WHEN OTHERS
3495 THEN
3496 ROLLBACK TO prioritize_waitlist;
3497 x_return_status := FND_API.g_ret_sts_unexp_error;
3498 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
3499 THEN
3500 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3501 END IF; -- check_msg_level
3502 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3503 , p_count => x_msg_count
3504 , p_data => x_msg_data
3505 );
3506
3507
3508 END prioritize_waitlist;
3509
3510 -------------------------------------------------------
3511 -- Start of Comments
3512 --
3513 -- PROCEDURE
3514 -- substitute_and_validate
3515 --
3516 -- HISTORY
3517 -- 11/16/99 sugupta Added.
3518 -- PURPOSE
3519 -- called by substitute_enrollee in PUB API..
3520 -- Substitute an enrollee(attendant) for an existing event registration..
3521 -- Who can substitute is NOT verified in this API call...
3522 -- If registrant information is also provided, then the existing
3523 -- 'registrant information' is replaced...
3524 -- 'Attendant information' is mandatory, but for account information...
3525 -- if registrant info is changed, reg_contact id is stored in original_reg_contact_id column..
3526 -------------------------------------------------------------
3527 PROCEDURE substitute_and_validate( P_Api_Version_Number IN NUMBER
3528 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
3529 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
3530 , p_confirmation_code IN VARCHAR2
3531 , p_attendant_party_id IN NUMBER
3532 , p_attendant_contact_id IN NUMBER
3533 , p_attendant_account_id IN NUMBER
3534 , p_registrant_party_id IN NUMBER
3535 , p_registrant_contact_id IN NUMBER
3536 , p_registrant_account_id IN NUMBER
3537 , X_Return_Status OUT NOCOPY VARCHAR2
3538 , X_Msg_Count OUT NOCOPY NUMBER
3539 , X_Msg_Data OUT NOCOPY VARCHAR2
3540 )
3541
3542 IS
3543
3544 l_api_name CONSTANT VARCHAR2(30) := 'substitute_and_validate';
3545 l_api_version_number CONSTANT NUMBER := 1.0;
3546 l_confirmation_code VARCHAR2(30) := p_confirmation_code;
3547 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3548 l_return_status VARCHAR2(1);
3549
3550 BEGIN
3551
3552 -- Standard Start of API savepoint
3553 SAVEPOINT substitute_validate_PVT;
3554 IF (AMS_DEBUG_HIGH_ON) THEN
3555
3556 AMS_Utility_PVT.debug_message(l_full_name || ': start');
3557 END IF;
3558
3559 IF FND_API.to_boolean(p_init_msg_list)
3560 THEN
3561 FND_MSG_PUB.initialize;
3562 END IF; -- p_init_msg_list
3563
3564 IF (NOT FND_API.compatible_api_call( l_api_version_number
3565 , p_api_version_number
3566 , l_api_name
3567 , g_pkg_name
3568 )
3569 )
3570 THEN
3571 RAISE FND_API.g_exc_unexpected_error;
3572 END IF; -- compatible API call
3573
3574 -- Initialize API return status to SUCCESS
3575 l_return_status := FND_API.G_RET_STS_SUCCESS;
3576
3577 -- validate not null values passed for required parameters...
3578 IF ( (p_confirmation_code IS NULL)
3579 OR (p_attendant_party_id IS NULL)
3580 OR (p_attendant_contact_id IS NULL)
3581 OR (p_registrant_contact_id IS NULL)
3582 )
3583 THEN
3584 AMS_Utility_PVT.error_message('AMS_EVT_REG_SUBST_NULL_PARAM');
3585 x_return_status := FND_API.g_ret_sts_error;
3586 RAISE FND_API.g_exc_error;
3587 END IF; -- null ids
3588
3589 -- validate the registrant fk info....
3590 check_registrant_fk_info( p_registrant_party_id
3591 , p_registrant_contact_id
3592 , p_registrant_account_id
3593 , x_return_status => l_return_status
3594 );
3595
3596 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
3597 THEN
3598 RAISE FND_API.g_exc_unexpected_error;
3599 ELSIF (l_return_status = FND_API.g_ret_sts_error)
3600 THEN
3601 RAISE FND_API.g_exc_error;
3602 END IF; -- l_return_status
3603
3604 -- update sql stmt
3605 -------------------------- update --------------------
3606 IF (AMS_DEBUG_HIGH_ON) THEN
3607
3608 AMS_Utility_PVT.debug_message(l_full_name || ': update');
3609 END IF;
3610
3611 update AMS_EVENT_REGISTRATIONS set
3612 ATTENDANT_PARTY_ID = p_attendant_party_id,
3613 ATTENDANT_CONTACT_ID = p_attendant_contact_id,
3614 ATTENDANT_ACCOUNT_ID = nvl(p_attendant_account_id,attendant_account_id),
3615 REGISTRANT_PARTY_ID = nvl(p_registrant_party_id, registrant_party_id),
3616 REGISTRANT_CONTACT_ID = p_registrant_contact_id,
3617 REGISTRANT_ACCOUNT_ID = nvl(p_registrant_account_id, registrant_account_id),
3618 ORIGINAL_REGISTRANT_CONTACT_ID = registrant_contact_id
3619 where CONFIRMATION_CODE LIKE p_confirmation_code;
3620
3621 IF (SQL%NOTFOUND)
3622 THEN
3623 AMS_Utility_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
3624 RAISE FND_API.g_exc_error;
3625 END IF; -- SQL%NOTFOUND
3626
3627 -- Standard check for p_commit
3628 IF (FND_API.to_Boolean(p_commit))
3629 THEN
3630 COMMIT WORK;
3631 END IF; -- p_commit
3632
3633 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3634 , p_count => x_msg_count
3635 , p_data => x_msg_data
3636 );
3637
3638 IF (AMS_DEBUG_HIGH_ON) THEN
3639
3640
3641
3642 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3643
3644 END IF;
3645
3646 EXCEPTION
3647 WHEN FND_API.g_exc_error
3648 THEN
3649 ROLLBACK TO substitute_validate_PVT;
3650 x_return_status := FND_API.g_ret_sts_error;
3651 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3652 , p_count => x_msg_count
3653 , p_data => x_msg_data
3654 );
3655
3656 WHEN FND_API.g_exc_unexpected_error
3657 THEN
3658 ROLLBACK TO substitute_validate_PVT;
3659 x_return_status := FND_API.g_ret_sts_unexp_error;
3660 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3661 , p_count => x_msg_count
3662 , p_data => x_msg_data
3663 );
3664
3665 WHEN OTHERS
3666 THEN
3667 ROLLBACK TO substitute_validate_PVT;
3668 x_return_status := FND_API.g_ret_sts_unexp_error;
3669 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
3670 THEN
3671 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3672 END IF; -- check_msg_level
3673 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
3674 , p_count => x_msg_count
3675 , p_data => x_msg_data
3676 );
3677
3678 END substitute_and_validate;
3679
3680 -------------------------------------------------------
3681 -- Start of Comments
3682 --
3683 -- PROCEDURE
3684 -- transfer_and_validate
3685 --
3686 -- HISTORY
3690 -- TRansfer an enrollee(attendant) for an existing event registration..
3687 -- 11/16/99 sugupta Added.
3688 -- PURPOSE
3689 -- called by transfer_enrollee in PUB API..
3691 -- from one event offering to another offering..id's are mandatory..
3692 -- Who can transfer is NOT verified in this API call...
3693 -- Waitlist flag input is mandatory which means that if the other offering is full, is
3694 -- the attendant willing to get waitlisted....
3695 -- if the offering is full, and waitlisting is not wanted or even wailist is full, then
3696 -- the transfer will fail...
3697 -- PAYMENT details are not taken care of in this API call....
3698 -------------------------------------------------------------
3699 PROCEDURE transfer_and_validate( P_Api_Version_Number IN NUMBER
3700 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
3701 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
3702 , p_object_version IN NUMBER
3703 , p_old_confirmation_code IN VARCHAR2
3704 , p_old_offer_id IN NUMBER
3705 , p_new_offer_id IN NUMBER
3706 , p_waitlist_flag IN VARCHAR2
3707 , p_registrant_account_id IN NUMBER -- can be null
3708 , p_registrant_party_id IN NUMBER -- can be null
3709 , p_registrant_contact_id IN NUMBER -- can be null
3710 , p_attendant_party_id IN NUMBER -- can be null
3711 , p_attendant_contact_id IN NUMBER -- can be null
3712 , x_new_confirmation_code OUT NOCOPY VARCHAR2
3713 , x_old_cancellation_code OUT NOCOPY VARCHAR2
3714 , x_new_registration_id OUT NOCOPY NUMBER
3715 , x_old_system_status_code OUT NOCOPY VARCHAR2
3716 , x_new_system_status_code OUT NOCOPY VARCHAR2
3717 , X_Return_Status OUT NOCOPY VARCHAR2
3718 , X_Msg_Count OUT NOCOPY NUMBER
3719 , X_Msg_Data OUT NOCOPY VARCHAR2
3720 )
3721
3722 IS
3723
3724 l_api_name CONSTANT VARCHAR2(30) := 'transfer_and_validate';
3725 l_api_version_number CONSTANT NUMBER := 1.0;
3726 l_old_confirmation_code VARCHAR2(30) := p_old_confirmation_code;
3727 l_full_name VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3728 l_return_status VARCHAR2(1);
3729 l_waitlist_allowed_flag VARCHAR2(1);
3730 l_reg_required_flag VARCHAR2(1);
3731 l_reg_frozen_flag VARCHAR2(1);
3732 l_effective_capacity NUMBER;
3733 l_reg_waitlist_pct NUMBER;
3734 l_cancellation_reason_code VARCHAR2(30);
3735 l_system_status_code VARCHAR2(30);
3736 l_reg_status_date DATE;
3737 l_evt_regs_rec evt_regs_Rec_type;
3738
3739 Cursor transfer_get_offer_details(l_event_offer_id NUMBER) is
3740 select
3741 REG_WAITLIST_ALLOWED_FLAG,
3742 REG_REQUIRED_FLAG,
3743 REG_FROZEN_FLAG,
3744 REG_EFFECTIVE_CAPACITY,
3745 REG_WAITLIST_PCT
3746 from ams_event_offers_all_b
3747 where event_offer_id = l_event_offer_id;
3748
3749 Cursor get_registrant_status(p_confirmation_code VARCHAR2) IS
3750 select system_status_code
3751 from ams_event_registrations
3752 where confirmation_code = p_confirmation_code;
3753
3754 BEGIN
3755
3756 -- Standard Start of API savepoint
3757 SAVEPOINT transfer_validate_PVT;
3758 IF (AMS_DEBUG_HIGH_ON) THEN
3759
3760 AMS_Utility_PVT.debug_message(l_full_name || ': start');
3761 END IF;
3762
3763 IF FND_API.to_boolean(p_init_msg_list)
3764 THEN
3765 FND_MSG_PUB.initialize;
3766 END IF; -- p_init_msg_list
3767
3768 IF (NOT FND_API.compatible_api_call( l_api_version_number
3769 , p_api_version_number
3770 , l_api_name
3771 , g_pkg_name
3772 )
3773 )
3774 THEN
3775 RAISE FND_API.g_exc_unexpected_error;
3776 END IF; -- compatible API call
3777
3778 -- Initialize API return status to SUCCESS
3779 l_return_status := FND_API.G_RET_STS_SUCCESS;
3780
3781 -- validate not null values passed for required parameters...
3782 IF ( (p_old_confirmation_code IS NULL)
3783 OR (p_old_offer_id IS NULL)
3784 OR (p_new_offer_id IS NULL)
3785 OR (p_waitlist_flag IS NULL)
3786 )
3787 THEN
3788 AMS_Utility_PVT.error_message('AMS_EVT_REG_TR_NULL_PARAM');
3789 x_return_status := FND_API.g_ret_sts_error;
3790 RAISE FND_API.g_exc_error;
3791 END IF; -- null ids
3792
3793 -- check Offer id's fk .....
3794 IF (AMS_Utility_PVT.check_fk_exists( 'ams_event_offers_all_b'
3795 , 'event_offer_id'
3796 , p_old_offer_id
3797 ) = FND_API.g_false
3798 )
3799 THEN
3800 AMS_Utility_PVT.error_message('AMS_EVT_REG_TR_BAD_EVOID');
3801 x_return_status := FND_API.g_ret_sts_error;
3802 RAISE FND_API.g_exc_error;
3803 END IF; -- check_fk_exists
3804
3805 IF (AMS_Utility_PVT.check_fk_exists( 'ams_event_offers_all_b'
3806 , 'event_offer_id'
3807 , p_new_offer_id
3808 ) = FND_API.g_false
3809 )
3810 THEN
3811 AMS_Utility_PVT.error_message('AMS_EVT_REG_TR_BAD_EVOID');
3812 x_return_status := FND_API.g_ret_sts_error;
3813 RAISE FND_API.g_exc_error;
3814 END IF; -- check_fk_exists
3815
3816 -- Prevent cancelled registrants from cancelled (probably should not be at this level in the tech stack)
3817 open get_registrant_status(p_old_confirmation_code);
3818 fetch get_registrant_status
3819 into l_system_status_code;
3820 close get_registrant_status;
3821
3822 IF (nvl(l_system_status_code,'X') = 'CANCELLED')
3823 THEN
3824 AMS_Utility_PVT.error_message('AMS_EVT_REG_TR_CANCEL');
3825 RAISE FND_API.g_exc_error;
3826 END IF; -- l_system_status_code
3827
3828 ------------------api logic-----------------------
3829 -- Get the offering details of new offer id
3830 open transfer_get_offer_details(p_new_offer_id);
3831 fetch transfer_get_offer_details
3832 into
3833 l_waitlist_allowed_flag,
3834 l_reg_required_flag,
3835 l_reg_frozen_flag,
3836 l_effective_capacity,
3837 l_reg_waitlist_pct;
3838 close transfer_get_offer_details;
3839
3840 IF (AMS_DEBUG_HIGH_ON) THEN
3841
3842
3843
3844 AMS_UTILITY_PVT.DEBUG_MESSAGE ('after offer details');
3845
3846 END IF;
3847 IF (l_reg_required_flag = 'N')
3848 THEN
3849 -- call cancel registration...and give the message that reg not required for the other event
3850 l_cancellation_reason_code := 'TRANSFERRED_TO_OTHER_EVENT';
3851 Cancel_evtRegs( P_Api_Version_Number => p_api_version_number
3852 , P_Init_Msg_List => p_init_msg_list
3853 , P_Commit => p_commit
3854 , p_object_version => p_object_version
3855 , p_event_offer_id => p_old_offer_id
3856 , p_registrant_party_id => NULL
3857 , p_confirmation_code => p_old_confirmation_code
3858 , p_registration_group_id => NULL
3859 , p_cancellation_reason_code => l_cancellation_reason_code
3860 , x_cancellation_code => x_old_cancellation_code
3861 , X_Return_Status => l_return_status
3862 , X_Msg_Count => x_msg_count
3863 , X_Msg_Data => x_msg_data
3864 );
3865 IF l_return_status = FND_API.g_ret_sts_unexp_error
3866 THEN
3867 RAISE FND_API.g_exc_unexpected_error;
3868 ELSIF l_return_status = FND_API.g_ret_sts_error
3869 THEN
3870 RAISE FND_API.g_exc_error;
3871 END IF; -- l_return_status
3872
3873 x_old_system_status_code := 'CANCELLED';
3874 IF (AMS_DEBUG_HIGH_ON) THEN
3875
3876 AMS_UTILITY_PVT.DEBUG_MESSAGE ('REgistration for the new event offering is not required, so just cancelling');
3877 END IF;
3878 RETURN;
3879 ELSE -- reg required flag is Y
3880 IF (l_reg_frozen_flag = 'Y')
3881 THEN
3882 IF (AMS_DEBUG_HIGH_ON) THEN
3883
3884 AMS_UTILITY_PVT.debug_message('Registrations for the new event offering are no longer accepted, Old Registration is not cancelled');
3885 END IF;
3886 RETURN;
3887 END IF; -- l_reg_frozen_flag
3888
3889 IF (check_reg_availability(l_effective_capacity, p_new_offer_id) > 0)
3890 THEN
3891 l_system_status_code := 'REGISTERED';
3892
3893 -- call insert, then call cancel reg
3894 IF (AMS_DEBUG_HIGH_ON) THEN
3895
3896 AMS_UTILITY_PVT.debug_message('Calling transfer_insert');
3897 END IF;
3898 transfer_insert( p_Api_Version_Number => p_api_version_number
3899 , p_Init_Msg_List => p_init_msg_list
3900 , p_Commit => p_commit
3901 , p_old_offer_id => p_old_offer_id
3902 , p_new_offer_id => p_new_offer_id
3903 , p_system_status_code => l_system_status_code
3904 , p_reg_status_date => sysdate
3905 , p_old_confirmation_code => p_old_confirmation_code
3906 , p_registrant_account_id => p_registrant_account_id
3907 , p_registrant_party_id => p_registrant_party_id
3908 , p_registrant_contact_id => p_registrant_contact_id
3909 , p_attendant_party_id => p_attendant_party_id
3910 , p_attendant_contact_id => p_attendant_contact_id
3911 , x_new_confirmation_code => x_new_confirmation_code
3912 , x_new_system_status_code => x_new_system_status_code
3913 , x_new_registration_id => x_new_registration_id
3914 , x_return_status => l_return_status
3915 , x_Msg_Count => x_msg_count
3916 , x_Msg_Data => x_msg_data
3917 );
3918
3919 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
3920 THEN
3921 RAISE FND_API.g_exc_unexpected_error;
3922 ELSIF (l_return_status = FND_API.g_ret_sts_error)
3923 THEN
3924 RAISE FND_API.g_exc_error;
3925 END IF; -- l_return_status
3926 x_new_system_status_code := l_system_status_code;
3927
3928 -- if reg successfull....call cancel_reg for old event offer id and pass out cancellation code for old
3929 l_cancellation_reason_code := 'TRANSFERRED_TO_OTHER_EVENT';
3930 Cancel_evtRegs( P_Api_Version_Number => p_api_version_number
3931 , P_Init_Msg_List => p_init_msg_list
3932 , P_Commit => p_commit
3933 , p_object_version => p_object_version
3934 , p_event_offer_id => p_old_offer_id
3935 , p_registrant_party_id => NULL
3936 , p_confirmation_code => p_old_confirmation_code
3940 , X_Return_Status => l_return_status
3937 , p_registration_group_id => NULL
3938 , p_cancellation_reason_code => l_cancellation_reason_code
3939 , x_cancellation_code => x_old_cancellation_code
3941 , X_Msg_Count => x_msg_count
3942 , X_Msg_Data => x_msg_data
3943 );
3944
3945 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
3946 THEN
3947 RAISE FND_API.g_exc_unexpected_error;
3948 ELSIF (l_return_status = FND_API.g_ret_sts_error)
3949 THEN
3950 RAISE FND_API.g_exc_error;
3951 END IF; -- l_return_status
3952 x_old_system_status_code := 'CANCELLED';
3953
3954 ELSE -- check_reg_availability
3955 IF (p_waitlist_flag = 'Y')
3956 THEN
3957 -- checked reg, now check for waitlist
3958 IF (l_waitlist_allowed_flag = 'N')
3959 THEN
3960 IF (AMS_DEBUG_HIGH_ON) THEN
3961
3962 AMS_UTILITY_PVT.debug_message('Registrations are sold out. Waitlist not allowed for this event offering');
3963 END IF;
3964 RETURN;
3965 ELSE -- wailist allowed
3966 if (check_waitlist_availability( l_reg_waitlist_pct
3967 , l_effective_capacity
3968 , p_new_offer_id
3969 ) = FND_API.g_false
3970 )
3971 then
3972 IF (AMS_DEBUG_HIGH_ON) THEN
3973
3974 AMS_UTILITY_PVT.debug_message(' Could not wailist..Waiting list for this event offering is full, the old event is not cancelled');
3975 END IF;
3976 RETURN;
3977 else -- waitlist available
3978 l_system_status_code := 'WAITLISTED';
3979 -- same logic as above....
3980 -- YES IT'S THE SAME GODDAMN LOGIC - THAT MEANS THERE'S A BETTER WAY TO DO IT!!!!!!
3981 transfer_insert( p_Api_Version_Number => p_api_version_number
3982 , p_Init_Msg_List => p_init_msg_list
3983 , p_Commit => p_commit
3984 , p_old_offer_id => p_old_offer_id
3985 , p_new_offer_id => p_new_offer_id
3986 , p_system_status_code => l_system_status_code
3987 , p_reg_status_date => sysdate
3988 , p_old_confirmation_code => p_old_confirmation_code
3989 , p_registrant_account_id => p_registrant_account_id
3990 , p_registrant_party_id => p_registrant_party_id
3991 , p_registrant_contact_id => p_registrant_contact_id
3992 , p_attendant_party_id => p_attendant_party_id
3993 , p_attendant_contact_id => p_attendant_contact_id
3994 , x_new_confirmation_code => x_new_confirmation_code
3995 , x_new_system_status_code => x_new_system_status_code
3996 , x_new_registration_id => x_new_registration_id
3997 , x_return_status => l_return_status
3998 , x_Msg_Count => x_msg_count
3999 , x_Msg_Data => x_msg_data
4000 );
4001
4002 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4003 RAISE FND_API.g_exc_unexpected_error;
4004 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
4005 RAISE FND_API.g_exc_error;
4006 END IF;
4007 x_new_system_status_code := l_system_status_code;
4008
4012 Cancel_evtRegs( P_Api_Version_Number => p_api_version_number
4009 -- if waitlisting is successfull....call cancel_reg for old event offer id....
4010 --and pass OUT NOCOPY cancellation code for old...
4011 l_cancellation_reason_code := 'TRANSFERRED_TO_OTHER_EVENT';
4013 , P_Init_Msg_List => p_init_msg_list
4014 , P_Commit => p_commit
4015 , p_object_version => p_object_version
4016 , p_event_offer_id => p_old_offer_id
4017 , p_registrant_party_id => NULL
4018 , p_confirmation_code => p_old_confirmation_code
4019 , p_registration_group_id => NULL
4020 , p_cancellation_reason_code => l_cancellation_reason_code
4021 , x_cancellation_code => x_old_cancellation_code
4022 , X_Return_Status => l_return_status
4023 , X_Msg_Count => x_msg_count
4024 , X_Msg_Data => x_msg_data
4025 );
4026
4027 IF l_return_status = FND_API.g_ret_sts_unexp_error
4028 THEN
4029 RAISE FND_API.g_exc_unexpected_error;
4030 ELSIF l_return_status = FND_API.g_ret_sts_error
4031 THEN
4032 RAISE FND_API.g_exc_error;
4033 END IF;
4034 x_old_system_status_code := 'CANCELLED';
4035 end if; -- waitlist available
4036 END IF; -- wailist allowed
4037 END IF; -- waitlist is OK
4038 END IF; -- check reg availability
4039 END IF; --Reg required flag
4040
4041
4042 -- Standard check for p_commit
4043 IF (FND_API.to_Boolean(p_commit))
4044 THEN
4045 COMMIT WORK;
4046 END IF; -- p_commit
4047
4048 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4049 , p_count => x_msg_count
4050 , p_data => x_msg_data
4051 );
4052
4053 IF (AMS_DEBUG_HIGH_ON) THEN
4054
4055
4056
4057 AMS_Utility_PVT.debug_message(l_full_name ||': end');
4058
4059 END IF;
4060
4061 EXCEPTION
4062 WHEN FND_API.g_exc_error
4063 THEN
4064 ROLLBACK TO transfer_validate_PVT;
4065 x_return_status := FND_API.g_ret_sts_error;
4066 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4067 , p_count => x_msg_count
4068 , p_data => x_msg_data
4069 );
4070
4071 WHEN FND_API.g_exc_unexpected_error
4072 THEN
4073 ROLLBACK TO transfer_validate_PVT;
4074 x_return_status := FND_API.g_ret_sts_unexp_error ;
4075 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4076 , p_count => x_msg_count
4077 , p_data => x_msg_data
4078 );
4079
4080 WHEN OTHERS
4081 THEN
4082 ROLLBACK TO transfer_validate_PVT;
4083 x_return_status := FND_API.g_ret_sts_unexp_error ;
4084 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
4085 THEN
4086 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4087 END IF; -- check_msg_level
4088 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4089 , p_count => x_msg_count
4090 , p_data => x_msg_data
4091 );
4092
4093 END transfer_and_validate;
4094
4095 ---------------------------------------------------------------------
4096 -- PROCEDURE
4097 -- check_evtRegs_items
4098 -- check_evtRegs_req_items
4099 -- check_evtRegs_uk_items
4100 -- check_evtREgs_fk_items
4101 -- check_evtRegs_lookup_items
4102 -- check_evtRegs_flag_items
4103 --
4104 -- HISTORY
4105 -- 11/01/99 sugupta Created.
4106 ---------------------------------------------------------------------
4107 -------------------Required items-----------------------
4108 PROCEDURE check_evtRegs_req_items( p_evt_Regs_rec IN evt_regs_Rec_Type
4109 , x_return_status OUT NOCOPY VARCHAR2
4110 )
4111
4112 IS
4113
4114 BEGIN
4115
4116 x_return_status := FND_API.g_ret_sts_success;
4117 ------------------------ owner_user_id --------------------------
4118 IF (p_evt_Regs_rec.owner_user_id IS NULL)
4119 THEN
4120 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_OWNER_ID');
4121 x_return_status := FND_API.g_ret_sts_error;
4122 RETURN;
4123 END IF;
4124
4125 /* Should user status be mandatory .. it wont be passed from screen, but derived from system status
4126 ------------------------ user_status_id --------------------------
4127 IF p_evt_Regs_rec.user_status_id IS NULL THEN
4128 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4129 THEN
4130 FND_MESSAGE.set_name('AMS', 'AMS_EVT_REG_NO_USER_STATUS');
4131 FND_MSG_PUB.add;
4132 END IF;
4133
4134 x_return_status := FND_API.g_ret_sts_error;
4135 RETURN;
4136 END IF;
4137 */
4138
4139 ----------------------REG PARTY ID---------------------------------
4140 IF (p_evt_Regs_rec.registrant_party_id IS NULL)
4141 THEN
4142 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_PARTY');
4143 x_return_status := FND_API.g_ret_sts_error;
4147 ------------------------ payment status-Order header/line id --------------------------
4144 RETURN;
4145 END IF;
4146
4148 IF (p_evt_Regs_rec.payment_status_code is NOT NULL)
4149 THEN
4150 IF (p_evt_Regs_rec.payment_status_code = 'PAID')
4151 THEN
4152 IF (p_evt_Regs_rec.order_header_id IS NULL)
4153 THEN
4154 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_ORDER_HEADER');
4155 x_return_status := FND_API.g_ret_sts_error;
4156 RETURN;
4157 END IF; -- order_header_id
4158 ELSIF (p_evt_Regs_rec.payment_status_code = 'FREE')
4159 THEN
4160 IF (p_evt_Regs_rec.order_header_id IS NOT NULL)
4161 THEN
4162 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_ORDER_HEADER');
4163 x_return_status := FND_API.g_ret_sts_error;
4164 RETURN;
4165 END IF; -- order_header_id
4166 END IF; -- payment_status_code
4167 END IF; -- payment_status_code is not null
4168
4169 ------------------------ Order header/line id --------------------------
4170 IF ( (p_evt_Regs_rec.order_line_id IS NOT NULL)
4171 AND (p_evt_Regs_rec.order_header_id IS NULL)
4172 )
4173 THEN
4174 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_ORDER_HEADER');
4175 x_return_status := FND_API.g_ret_sts_error;
4176 RETURN;
4177 END IF; -- order_line_id/order_header_id
4178
4179 ------------------------ Order header/line id-Account needed------------------
4180 IF ( (p_evt_Regs_rec.order_line_id IS NOT NULL)
4181 OR (p_evt_Regs_rec.order_header_id IS NOT NULL)
4182 )
4183 THEN
4184 IF ( (p_evt_regs_rec.registrant_account_id is NULL)
4185 AND (p_evt_regs_rec.attendant_account_id IS NULL)
4186 )
4187 THEN
4188 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_ACCT_FOR_ORDER');
4189 x_return_status := FND_API.g_ret_sts_error;
4190 RETURN;
4191 END IF; -- registrant_account_id/attendant_account_id
4192 END IF; -- order_line_id/order_header_id
4193
4194 -----------------------REG CONTACT ID-----------------------------------
4195 IF (p_evt_Regs_rec.registrant_contact_id IS NULL)
4196 THEN
4197 AMS_Utility_PVT.error_message('AMS_EVT_REG_NO_CONTACT_ID');
4198 x_return_status := FND_API.g_ret_sts_error;
4199 RETURN;
4200 END IF; -- registrant_contact_id
4201
4202 END check_evtRegs_req_items;
4203
4204 ----------------- unique key validation --------------------
4205 -- only needed at update_evt..check for valdation mode
4206 -- ask ravi if cross products need validate
4207 PROCEDURE check_evtRegs_uk_items( p_confirmation_code IN VARCHAR2
4208 , p_event_registration_id IN NUMBER
4209 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
4210 , x_return_status OUT NOCOPY VARCHAR2
4211 )
4212
4213 IS
4214
4215 l_dummy NUMBER;
4216 cursor c_conf_code(conf_code_in IN VARCHAR2) IS
4217 SELECT 1 FROM DUAL
4218 WHERE EXISTS (select 1
4219 from ams_event_registrations
4220 where confirmation_code = conf_code_in
4221 );
4222
4223 BEGIN
4224
4225 -- check reg id, conf code only for create mode
4226 x_return_status := FND_API.g_ret_sts_success;
4227
4228 IF ( (p_validation_mode = JTF_PLSQL_API.g_create)
4229 AND (p_event_registration_id IS NOT NULL)
4230 )
4231 THEN
4232 IF (AMS_Utility_PVT.check_uniqueness( 'ams_event_registrations'
4233 , 'event_registration_id = ' || p_event_registration_id
4234 ) = FND_API.g_false
4235 )
4236 THEN
4237 AMS_Utility_PVT.error_message('AMS_EVT_REG_DUPLICATE_ID');
4238 x_return_status := FND_API.g_ret_sts_error;
4239 RETURN;
4240 END IF; -- check_uniqueness
4241 END IF; -- p_event_registration_id
4242
4243 IF ( (p_validation_mode = JTF_PLSQL_API.g_create)
4244 AND (p_confirmation_code IS NOT NULL)
4245 )
4246 THEN
4247 /* bug#1490374 commented OUT NOCOPY this piece
4248 IF (AMS_Utility_PVT.check_uniqueness( 'ams_event_registrations'
4249 , 'confirmation_code = ''' || p_confirmation_code || ''''
4250 ) = FND_API.g_false
4251 )
4252 */
4253 open c_conf_code(p_confirmation_code);
4254 fetch c_conf_code
4255 into l_dummy;
4256 close c_conf_code;
4257 IF (l_dummy <> 1)
4258 THEN
4259 AMS_Utility_PVT.error_message('AMS_EVT_REG_DUPLICATE_CODE');
4260 x_return_status := FND_API.g_ret_sts_error;
4261 RETURN;
4262 END IF; -- l_dummy
4263 END IF; -- p_confirmation_code
4264
4265 END check_evtRegs_uk_items;
4266
4267
4268 ------------------------FOREIGN KEY------------------------
4269 PROCEDURE check_evtRegs_fk_items( p_evt_Regs_rec IN evt_regs_Rec_Type
4270 , p_validation_mode IN VARCHAR2
4271 , x_return_status OUT NOCOPY VARCHAR2
4272 )
4273
4274 IS
4275
4276 BEGIN
4277
4278 ------------------- EVENT OFFER ID ----------------
4279 IF (p_evt_regs_rec.event_offer_id <> FND_API.g_miss_num)
4280 THEN
4281 IF (AMS_Utility_PVT.check_fk_exists( 'ams_event_offers_all_b'
4285 )
4282 , 'event_offer_id'
4283 , p_evt_regs_rec.event_offer_id
4284 ) = FND_API.g_false
4286 THEN
4287 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_EVENT_OFFER_ID');
4288 x_return_status := FND_API.g_ret_sts_error;
4289 RETURN;
4290 END IF; -- check_fk_exists
4291 END IF; -- event_offer_id
4292
4293 ------------APPLICATION ID---------------------
4294 IF (p_evt_regs_rec.application_id <> FND_API.g_miss_num)
4295 THEN
4296 IF (AMS_Utility_PVT.check_fk_exists( 'fnd_application'
4297 , 'application_id'
4298 , p_evt_regs_rec.application_id
4299 ) = FND_API.g_false
4300 )
4301 THEN
4302 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_APPLICATION_ID');
4303 x_return_status := FND_API.g_ret_sts_error;
4304 RETURN;
4305 END IF; -- check_fk_exists
4306 END IF; -- application_id
4307
4308 --------------user status id------------------------
4309 IF (p_evt_regs_rec.user_status_id <> FND_API.g_miss_num)
4310 THEN
4311 IF (AMS_Utility_PVT.check_fk_exists( 'ams_user_statuses_b'
4312 , 'user_status_id'
4313 , p_evt_regs_rec.user_status_id
4314 ) = FND_API.g_false
4315 )
4316 THEN
4317 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_USER_STATUS_ID');
4318 x_return_status := FND_API.g_ret_sts_error;
4319 RETURN;
4320 END IF; -- check_fk_exists
4321 END IF; -- user_status_id
4322
4323 --------------------------SOURCE CODE-------------------
4324 IF p_evt_regs_rec.source_code <> FND_API.g_miss_char THEN
4325 IF (AMS_Utility_PVT.check_fk_exists( 'ams_source_codes'
4326 , 'source_code'
4327 , p_evt_regs_rec.source_code
4328 , AMS_Utility_PVT.g_varchar2
4329 ) = FND_API.g_false
4330 )
4331 THEN
4332 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_SOURCE_CODE');
4333 x_return_status := FND_API.g_ret_sts_error;
4334 RETURN;
4335 END IF; -- check_fk_exists
4336 END IF; -- source_code
4337
4338 --------------- attendant language-----------------------
4339 IF (p_evt_regs_rec.attendant_language <> FND_API.g_miss_char)
4340 THEN
4341 IF (AMS_Utility_PVT.check_fk_exists( 'fnd_languages'
4342 , 'language_code'
4343 , p_evt_regs_rec.attendant_language
4344 , AMS_Utility_PVT.g_varchar2
4345 ) = FND_API.g_false
4346 )
4347 THEN
4348 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_LANGUAGE');
4349 x_return_status := FND_API.g_ret_sts_error;
4350 RETURN;
4351 END IF; -- check_fk_exists
4352 END IF; -- attendant_language
4353
4354 ---------------------TARGET LIST ID------------------
4355 IF (p_evt_regs_rec.target_list_id <> FND_API.g_miss_num)
4356 THEN
4357 IF (AMS_Utility_PVT.check_fk_exists( 'ams_list_headers_all'
4358 , 'list_header_id'
4359 , p_evt_regs_rec.target_list_id
4360 ) = FND_API.g_false
4361 )
4362 THEN
4363 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_TARGET_LIST_ID');
4364 x_return_status := FND_API.g_ret_sts_error;
4365 RETURN;
4366 END IF; -- check_fk_exists
4367 END IF; -- target_list_id
4368
4369 ---------------------SYSTEM STATUS CODE--------------------
4370 IF (p_evt_regs_rec.system_status_code <> FND_API.g_miss_char)
4371 THEN
4372 IF (p_validation_mode = JTF_PLSQL_API.g_create)
4373 THEN
4374 IF (AMS_Utility_PVT.check_fk_exists( 'ams_user_statuses_b'
4375 , 'system_status_code'
4376 , p_evt_regs_rec.system_status_code
4377 , AMS_Utility_PVT.g_varchar2
4378 ) = FND_API.g_false
4379 )
4380 THEN
4381 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_SYS_STATUS');
4382 x_return_status := FND_API.g_ret_sts_error;
4383 RETURN;
4384 END IF; -- check_fk_exists
4385 ELSE
4386 -- will have to validate system status rules against ams_status_order_rules
4387 -- hongju to provide api..if not I will write it...
4388 null;
4389 END IF; -- p_validation_mode
4390 END IF; -- system_status_code
4391
4392 -----------Check REGISTRANT fk INFORMATION----------------------
4393 check_registrant_fk_info( p_evt_regs_rec.registrant_party_id
4394 , p_evt_regs_rec.registrant_contact_id
4395 , p_evt_regs_rec.registrant_account_id
4396 , x_return_status
4397 );
4398
4399 END check_evtRegs_fk_items;
4400
4401
4402 ------------------------LOOK UP------------------------
4403 PROCEDURE check_evtRegs_lookup_items( p_evt_Regs_rec IN evt_regs_Rec_Type
4404 , x_return_status OUT NOCOPY VARCHAR2
4405 )
4406
4407 IS
4408
4409 BEGIN
4410
4411 x_return_status := FND_API.g_ret_sts_success;
4415 IF (AMS_Utility_PVT.check_lookup_exists( p_lookup_type => 'AMS_EVENT_REG_SOURCE'
4412 ----------------------- registration_source_type--------------
4413 IF (p_evt_regs_rec.reg_source_type_code <> FND_API.g_miss_char)
4414 THEN
4416 , p_lookup_code => p_evt_regs_rec.reg_source_type_code
4417 ) = FND_API.g_false
4418 )
4419 THEN
4420 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_SOURCE_TYPE');
4421 x_return_status := FND_API.g_ret_sts_error;
4422 RETURN;
4423 END IF; -- check_lookup_exists
4424 END IF; -- reg_source_type_code
4425
4426 -----------------------PAYMENT STATUS_CODE--------------
4427 IF (p_evt_regs_rec.payment_status_code <> FND_API.g_miss_char)
4428 THEN
4429 IF (AMS_Utility_PVT.check_lookup_exists( p_lookup_type => 'AMS_EVENT_PAYMENT_STATUS'
4430 , p_lookup_code => p_evt_regs_rec.payment_status_code
4431 ) = FND_API.g_false
4432 )
4433 THEN
4434 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_PAY_STAT');
4435 x_return_status := FND_API.g_ret_sts_error;
4436 RETURN;
4437 END IF; -- check_lookup_exists
4438 END IF; -- payment_status_code
4439
4440 ------------------------CANCELLATION REASON CODE-------------------
4441 IF (p_evt_regs_rec.cancellation_reason_code <> FND_API.g_miss_char)
4442 THEN
4443 IF (AMS_Utility_PVT.check_lookup_exists( p_lookup_type => 'AMS_EVENT_CANCEL_REASON_CODE'
4444 , p_lookup_code => p_evt_regs_rec.cancellation_reason_code
4445 ) = FND_API.g_false
4446 )
4447 THEN
4448 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_CANCEL_REASON');
4449 x_return_status := FND_API.g_ret_sts_error;
4450 RETURN;
4451 END IF; -- check_lookup_exists
4452 END IF; -- cancellation_reason_code
4453
4454 ------------------------ATTENDANCE FAILURE REASON-------------------
4455 IF (p_evt_regs_rec.attendance_failure_reason <> FND_API.g_miss_char)
4456 THEN
4457 IF (AMS_Utility_PVT.check_lookup_exists( p_lookup_type => 'AMS_EVENT_ATTENDANCE_FAILURE'
4458 , p_lookup_code => p_evt_regs_rec.attendance_failure_reason
4459 ) = FND_API.g_false
4460 )
4461 THEN
4462 AMS_Utility_PVT.error_message('AMS_EVT_REG_BAD_ATTEN_FAILURE');
4463 x_return_status := FND_API.g_ret_sts_error;
4464 RETURN;
4465 END IF; -- check_lookup_exists
4466 END IF; -- attendance_failure_reason
4467
4468 END check_evtRegs_lookup_items;
4469
4470 -----------------------FLAG-----------------------
4471 PROCEDURE check_evtRegs_flag_items( p_evt_Regs_rec IN evt_regs_Rec_Type
4472 , x_return_status OUT NOCOPY VARCHAR2
4473 )
4474
4475 IS
4476
4477 BEGIN
4478
4479 null;
4480 -- prospect, attended, confirmed, evaluated...
4481
4482 END check_evtRegs_flag_items;
4483
4484 PROCEDURE check_evtRegs_items( p_evt_Regs_rec IN evt_regs_Rec_Type
4485 , p_validation_mode IN VARCHAR2
4486 , x_return_status OUT NOCOPY VARCHAR2
4487 )
4488
4489 IS
4490
4491 BEGIN
4492
4493 IF (AMS_DEBUG_HIGH_ON) THEN
4494
4495
4496
4497 AMS_Utility_PVT.debug_message(' check req items');
4498
4499 END IF;
4500 check_evtRegs_req_items( p_evt_Regs_rec => p_evt_Regs_rec
4504 THEN
4501 , x_return_status => x_return_status
4502 );
4503 IF (x_return_status <> FND_API.g_ret_sts_success)
4505 RETURN;
4506 END IF;
4507
4508 IF (AMS_DEBUG_HIGH_ON) THEN
4509
4510
4511
4512 AMS_Utility_PVT.debug_message('check uk items');
4513
4514 END IF;
4515 check_evtRegs_uk_items( p_confirmation_code => p_evt_Regs_rec.confirmation_code
4516 , p_event_registration_id => p_evt_Regs_rec.event_registration_id
4517 , x_return_status => x_return_status
4518 , p_validation_mode => p_validation_mode
4519 );
4520 IF (x_return_status <> FND_API.g_ret_sts_success)
4521 THEN
4522 RETURN;
4523 END IF;
4524
4525 IF (AMS_DEBUG_HIGH_ON) THEN
4526
4527
4528
4529 AMS_Utility_PVT.debug_message('check fk items');
4530
4531 END IF;
4532 check_evtRegs_fk_items( p_evt_Regs_rec => p_evt_Regs_rec
4533 , x_return_status => x_return_status
4534 , p_validation_mode => p_validation_mode
4535 );
4536 IF (x_return_status <> FND_API.g_ret_sts_success)
4537 THEN
4538 RETURN;
4539 END IF;
4540
4541 IF (AMS_DEBUG_HIGH_ON) THEN
4542
4543
4544
4545 AMS_Utility_PVT.debug_message('check lookup items');
4546
4547 END IF;
4548 check_evtRegs_lookup_items( p_evt_Regs_rec => p_evt_Regs_rec
4549 , x_return_status => x_return_status
4550 );
4551 IF (x_return_status <> FND_API.g_ret_sts_success)
4552 THEN
4553 RETURN;
4554 END IF;
4555
4556 IF (AMS_DEBUG_HIGH_ON) THEN
4557
4558
4559
4560 AMS_Utility_PVT.debug_message('check flag items');
4561
4562 END IF;
4563 check_evtRegs_flag_items( p_evt_Regs_rec => p_evt_Regs_rec
4564 , x_return_status => x_return_status
4565 );
4566 IF (x_return_status <> FND_API.g_ret_sts_success)
4567 THEN
4568 RETURN;
4569 END IF;
4570
4571 END check_evtRegs_items;
4572
4573 /* record validation...*/
4574
4575 PROCEDURE CHECK_EVTREGS_RECORD( p_evt_Regs_rec IN evt_regs_Rec_Type
4576 , x_return_status OUT NOCOPY VARCHAR2
4577 )
4578
4579 IS
4580
4581 l_event_offer_id NUMBER := p_evt_regs_rec.event_offer_id;
4582 l_registrant_party_id NUMBER := p_evt_regs_rec.registrant_party_id;
4583 l_registrant_contact_id NUMBER := p_evt_regs_rec.registrant_contact_id;
4584 l_attendant_party_id NUMBER := p_evt_regs_rec.attendant_party_id;
4585 l_attendant_contact_id NUMBER := p_evt_regs_rec.attendant_contact_id;
4586 temp_registrant_party_id NUMBER;
4587 temp_registrant_contact_id NUMBER;
4588 temp_attendant_party_id NUMBER;
4589 temp_attendant_contact_id NUMBER;
4590 temp_event_offer_id NUMBER;
4591 l_evt_Regs_rec evt_regs_Rec_Type;
4592
4593 CURSOR chkexists( p_reg_party_id IN NUMBER
4594 , p_reg_contact_id IN NUMBER
4595 , p_attendant_party_id IN NUMBER
4596 , p_attendant_contact_id IN NUMBER
4597 , p_event_offer_id IN NUMBER
4598 )
4599 IS
4600 select
4601 registrant_party_id,
4602 registrant_contact_id,
4603 attendant_party_id,
4604 attendant_contact_id,
4605 event_offer_id
4606 from ams_event_registrations
4607 where registrant_party_id = p_reg_party_id
4608 and registrant_contact_id = p_reg_contact_id
4609 and attendant_party_id = p_attendant_party_id
4610 and attendant_contact_id = p_attendant_contact_id
4611 and event_offer_id = p_event_offer_id;
4612
4613 BEGIN
4614
4615 OPEN chkexists( l_registrant_party_id
4616 , l_registrant_contact_id
4617 , l_attendant_party_id
4618 , l_attendant_contact_id
4619 , l_event_offer_id
4620 );
4621 FETCH chkexists
4622 INTO
4623 temp_registrant_party_id,
4624 temp_registrant_contact_id,
4625 temp_attendant_party_id,
4626 temp_attendant_contact_id,
4627 temp_event_offer_id;
4628 IF (chkexists%NOTFOUND)
4629 THEN
4630 x_return_status := FND_API.g_ret_sts_success;
4631 ELSE
4632 IF (AMS_DEBUG_HIGH_ON) THEN
4633
4634 AMS_Utility_PVT.debug_message('This record already exists');
4635 END IF;
4636 AMS_Utility_PVT.error_message('AMS_EVENT_REGISTRANT_EXISTS');
4637 x_return_status := FND_API.g_ret_sts_error;
4638 END IF;
4639 CLOSE chkexists;
4640
4641 END CHECK_EVTREGS_RECORD;
4642
4643 PROCEDURE Validate_evtregs( p_api_version_number IN NUMBER
4644 , p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
4645 , p_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4646 , p_evt_regs_Rec IN evt_regs_Rec_Type
4647 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
4648 , X_Return_Status OUT NOCOPY VARCHAR2
4649 , X_Msg_Count OUT NOCOPY NUMBER
4650 , X_Msg_Data OUT NOCOPY VARCHAR2
4654
4651 )
4652
4653 IS
4655 l_api_name CONSTANT VARCHAR2(30) := 'Validate_evtregs';
4656 l_api_version_number CONSTANT NUMBER := 1.0;
4657 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4658 l_return_status VARCHAR2(1);
4659 l_evt_regs_rec evt_regs_Rec_Type := p_evt_regs_Rec;
4660
4661 BEGIN
4662
4663 ----------------------- initialize --------------------
4664 IF (AMS_DEBUG_HIGH_ON) THEN
4665
4666 AMS_Utility_PVT.debug_message(l_full_name||': start');
4667 END IF;
4668
4669 IF (FND_API.to_boolean(p_init_msg_list))
4670 THEN
4671 FND_MSG_PUB.initialize;
4672 END IF; -- p_init_msg_list
4673
4674 IF (NOT FND_API.compatible_api_call( l_api_version_number
4675 , p_api_version_number
4676 , l_api_name
4677 , g_pkg_name
4678 )
4679 )
4680 THEN
4681 RAISE FND_API.g_exc_unexpected_error;
4682 END IF; -- compatible API call
4683
4684 x_return_status := FND_API.g_ret_sts_success;
4685
4686 ---------------------- validate ------------------------
4687
4688 IF (AMS_DEBUG_HIGH_ON) THEN
4689
4690
4691
4692 AMS_Utility_PVT.debug_message(l_full_name||': check items');
4693
4694 END IF;
4695 IF (p_validation_level >= JTF_PLSQL_API.g_valid_level_item)
4696 THEN
4697 check_evtRegs_items( p_evt_regs_Rec => l_evt_regs_Rec
4698 , p_validation_mode => p_validation_mode
4699 , x_return_status => l_return_status
4700 );
4701
4702 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
4703 THEN
4704 RAISE FND_API.g_exc_unexpected_error;
4705 ELSIF (l_return_status = FND_API.g_ret_sts_error)
4706 THEN
4707 RAISE FND_API.g_exc_error;
4708 END IF; -- l_return_status
4709 END IF; -- p_validation_level
4710
4711 /*
4712 IF (AMS_DEBUG_HIGH_ON) THEN
4713
4714 AMS_Utility_PVT.debug_message(l_full_name||': check record');
4715 END IF;
4716
4717 IF (p_validation_level >= JTF_PLSQL_API.g_valid_level_record)
4718 THEN
4719 check_evtRegs_record( P_evt_regs_Rec => l_evt_regs_Rec
4720 , x_return_status => l_return_status
4721 );
4722 IF (l_return_status = FND_API.g_ret_sts_unexp_error)
4723 THEN
4724 RAISE FND_API.g_exc_unexpected_error;
4725 ELSIF (l_return_status = FND_API.g_ret_sts_error)
4726 THEN
4727 RAISE FND_API.g_exc_error;
4728 END IF; -- l_return_status
4729 END IF; -- p_validation_level
4730
4731 -------------------- finish --------------------------
4732 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
4733 , p_count => x_msg_count,
4734 , p_data => x_msg_data
4735 );
4736 */
4737 IF (AMS_DEBUG_HIGH_ON) THEN
4738
4739 AMS_Utility_PVT.debug_message(l_full_name ||': end');
4740 END IF;
4741
4742 EXCEPTION
4743
4744 WHEN FND_API.g_exc_error
4745 THEN
4746 x_return_status := FND_API.g_ret_sts_error;
4747 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4748 , p_count => x_msg_count
4749 , p_data => x_msg_data
4750 );
4751
4752 WHEN FND_API.g_exc_unexpected_error
4753 THEN
4754 x_return_status := FND_API.g_ret_sts_unexp_error;
4755 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4756 , p_count => x_msg_count
4757 , p_data => x_msg_data
4758 );
4759
4760 WHEN OTHERS
4761 THEN
4762 x_return_status := FND_API.g_ret_sts_unexp_error;
4763 IF (FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error))
4764 THEN
4765 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4766 END IF; -- check_msg_level
4767 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4768 , p_count => x_msg_count
4769 , p_data => x_msg_data
4770 );
4771
4772 END Validate_evtregs;
4773
4774 ---------------------------------------------------------------------
4775 -- PROCEDURE
4776 -- init_evtregs_rec
4777 --
4778 -- HISTORY
4779 -- 06/29/2000 sugupta Create.
4780 ---------------------------------------------------------------------
4781 PROCEDURE init_evtregs_rec(x_evt_regs_rec OUT NOCOPY evt_regs_Rec_Type)
4782
4783 IS
4784
4785 BEGIN
4786
4787 x_evt_regs_rec.EVENT_REGISTRATION_ID := FND_API.g_miss_num;
4788 x_evt_regs_rec.last_update_date := FND_API.g_miss_date;
4789 x_evt_regs_rec.last_updated_by := FND_API.g_miss_num;
4790 x_evt_regs_rec.creation_date := FND_API.g_miss_date;
4791 x_evt_regs_rec.created_by := FND_API.g_miss_num;
4792 x_evt_regs_rec.last_update_login := FND_API.g_miss_num;
4793 x_evt_regs_rec.object_version_number := FND_API.g_miss_num;
4794 x_evt_regs_rec.EVENT_OFFER_ID := FND_API.g_miss_num;
4795 x_evt_regs_rec.application_id := FND_API.g_miss_num;
4796 x_evt_regs_rec.ACTIVE_FLAG := FND_API.g_miss_char;
4797 x_evt_regs_rec.OWNER_USER_ID := FND_API.g_miss_num;
4798 x_evt_regs_rec.SYSTEM_STATUS_CODE := FND_API.g_miss_char;
4799 x_evt_regs_rec.DATE_REGISTRATION_PLACED := FND_API.g_miss_date;
4800 x_evt_regs_rec.USER_STATUS_ID := FND_API.g_miss_num;
4801 x_evt_regs_rec.LAST_REG_STATUS_DATE := FND_API.g_miss_date;
4802 x_evt_regs_rec.REG_SOURCE_TYPE_CODE := FND_API.g_miss_char;
4803 x_evt_regs_rec.REGISTRATION_SOURCE_ID := FND_API.g_miss_num;
4804 x_evt_regs_rec.CONFIRMATION_CODE := FND_API.g_miss_char;
4805 x_evt_regs_rec.SOURCE_CODE := FND_API.g_miss_char;
4806 x_evt_regs_rec.REGISTRATION_GROUP_ID := FND_API.g_miss_num;
4807 x_evt_regs_rec.REGISTRANT_PARTY_ID := FND_API.g_miss_num;
4808 x_evt_regs_rec.REGISTRANT_CONTACT_ID := FND_API.g_miss_num;
4809 x_evt_regs_rec.REGISTRANT_ACCOUNT_ID := FND_API.g_miss_num;
4810 x_evt_regs_rec.ATTENDANT_PARTY_ID := FND_API.g_miss_num;
4811 x_evt_regs_rec.ATTENDANT_CONTACT_ID := FND_API.g_miss_num;
4812 x_evt_regs_rec.ATTENDANT_ACCOUNT_ID := FND_API.g_miss_num;
4813 x_evt_regs_rec.ORIGINAL_REGISTRANT_CONTACT_ID := FND_API.g_miss_num;
4814 x_evt_regs_rec.PROSPECT_FLAG := FND_API.g_miss_char;
4815 x_evt_regs_rec.ATTENDED_FLAG := FND_API.g_miss_char;
4816 x_evt_regs_rec.CONFIRMED_FLAG := FND_API.g_miss_char;
4817 x_evt_regs_rec.EVALUATED_FLAG := FND_API.g_miss_char;
4818 x_evt_regs_rec.ATTENDANCE_RESULT_CODE := FND_API.g_miss_char;
4819 x_evt_regs_rec.WAITLISTED_PRIORITY := FND_API.g_miss_num;
4820 x_evt_regs_rec.TARGET_LIST_ID := FND_API.g_miss_num;
4821 x_evt_regs_rec.INBOUND_MEDIA_ID := fnd_api.g_miss_num;
4822 x_evt_regs_rec.INBOUND_CHANNEL_ID := fnd_api.g_miss_num;
4823 x_evt_regs_rec.CANCELLATION_CODE := fnd_api.g_miss_char;
4824 x_evt_regs_rec.CANCELLATION_REASON_CODE := fnd_api.g_miss_char;
4825 x_evt_regs_rec.ATTENDANCE_FAILURE_REASON := fnd_api.g_miss_char;
4826 x_evt_regs_rec.ATTENDANT_LANGUAGE := fnd_api.g_miss_char;
4827 x_evt_regs_rec.SALESREP_ID := fnd_api.g_miss_num;
4828 x_evt_regs_rec.ORDER_HEADER_ID := fnd_api.g_miss_num;
4829 x_evt_regs_rec.ORDER_LINE_ID := fnd_api.g_miss_num;
4830 x_evt_regs_rec.DESCRIPTION := fnd_api.g_miss_char;
4831 x_evt_regs_rec.MAX_ATTENDEE_OVERRIDE_FLAG := fnd_api.g_miss_char;
4832 x_evt_regs_rec.INVITE_ONLY_OVERRIDE_FLAG := fnd_api.g_miss_char;
4833 x_evt_regs_rec.PAYMENT_STATUS_CODE := fnd_api.g_miss_char;
4834 x_evt_regs_rec.AUTO_REGISTER_FLAG := fnd_api.g_miss_char;
4835 x_evt_regs_rec.attribute_category := FND_API.g_miss_char;
4836 x_evt_regs_rec.attribute1 := FND_API.g_miss_char;
4837 x_evt_regs_rec.attribute2 := FND_API.g_miss_char;
4838 x_evt_regs_rec.attribute3 := FND_API.g_miss_char;
4839 x_evt_regs_rec.attribute4 := FND_API.g_miss_char;
4840 x_evt_regs_rec.attribute5 := FND_API.g_miss_char;
4841 x_evt_regs_rec.attribute6 := FND_API.g_miss_char;
4842 x_evt_regs_rec.attribute7 := FND_API.g_miss_char;
4843 x_evt_regs_rec.attribute8 := FND_API.g_miss_char;
4844 x_evt_regs_rec.attribute9 := FND_API.g_miss_char;
4845 x_evt_regs_rec.attribute10 := FND_API.g_miss_char;
4846 x_evt_regs_rec.attribute11 := FND_API.g_miss_char;
4847 x_evt_regs_rec.attribute12 := FND_API.g_miss_char;
4848 x_evt_regs_rec.attribute13 := FND_API.g_miss_char;
4849 x_evt_regs_rec.attribute14 := FND_API.g_miss_char;
4850 x_evt_regs_rec.attribute15 := FND_API.g_miss_char;
4851 x_evt_regs_rec.attendee_role_type := FND_API.g_miss_char; -- Hornet : added for imeeting integration
4852 x_evt_regs_rec.notification_type := FND_API.g_miss_char; -- Hornet : added for imeeting integration
4853 x_evt_regs_rec.last_notified_time := FND_API.g_miss_date; -- Hornet : added for imeeting integration
4854 x_evt_regs_rec.EVENT_JOIN_TIME := FND_API.g_miss_date; -- Hornet : added for imeeting integration
4855 x_evt_regs_rec.EVENT_EXIT_TIME := FND_API.g_miss_date; -- Hornet : added for imeeting integration
4856 x_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE := FND_API.g_miss_char; --Hornet : added for imeeting integration
4857
4858 END init_evtregs_rec;
4859
4860 --------------complete evtregs rec for update-------------------------
4861 PROCEDURE COMPLETE_EVTREG_REC( P_evt_regs_Rec IN evt_regs_Rec_Type
4862 , x_complete_Rec OUT NOCOPY evt_regs_Rec_Type
4863 )
4864
4865 IS
4866
4867 CURSOR c_reg IS
4868 SELECT *
4869 FROM ams_event_registrations
4870 WHERE event_registration_id = p_evt_regs_rec.event_registration_id;
4871
4872 l_reg_rec c_reg%ROWTYPE;
4873
4874 BEGIN
4875
4876 x_complete_rec := p_evt_regs_rec;
4877
4878 IF (AMS_DEBUG_HIGH_ON) THEN
4879
4880
4881
4882 AMS_UTILITY_PVT.debug_message('Ev Reg Id:'|| p_evt_regs_rec.event_registration_id);
4883
4884 END IF;
4885
4886 OPEN c_reg;
4887 FETCH c_reg
4888 INTO l_reg_rec;
4889 IF (c_reg%NOTFOUND)
4890 THEN
4891 CLOSE c_reg;
4892 AMS_UTILITY_PVT.error_message('AMS_API_RECORD_NOT_FOUND');
4893 -- RAISE FND_API.g_exc_error;
4894 END IF; -- c_reg%NOTFOUND
4895 CLOSE c_reg;
4896
4897 -- This procedure should complete the record by going through all the items in the incoming record.
4898 IF (p_evt_regs_rec.EVENT_OFFER_ID = FND_API.g_miss_num)
4899 THEN
4900 x_complete_rec.EVENT_OFFER_ID := l_reg_rec.EVENT_OFFER_ID;
4901 END IF; -- EVENT_OFFER_ID
4902
4903 IF (p_evt_regs_rec.APPLICATION_ID = FND_API.g_miss_num)
4904 THEN
4905 x_complete_rec.APPLICATION_ID := l_reg_rec.APPLICATION_ID;
4906 END IF; -- APPLICATION_ID
4907
4908 IF (p_evt_regs_rec.ACTIVE_FLAG = FND_API.g_miss_char)
4909 THEN
4910 x_complete_rec.ACTIVE_FLAG := l_reg_rec.ACTIVE_FLAG;
4911 END IF; -- ACTIVE_FLAG
4912
4913 IF (p_evt_regs_rec.OWNER_USER_ID = FND_API.g_miss_num)
4914 THEN
4915 x_complete_rec.OWNER_USER_ID := l_reg_rec.OWNER_USER_ID;
4916 END IF; -- OWNER_USER_ID
4917
4918 IF (p_evt_regs_rec.SYSTEM_STATUS_CODE = FND_API.g_miss_char)
4919 THEN
4920 x_complete_rec.SYSTEM_STATUS_CODE := l_reg_rec.SYSTEM_STATUS_CODE;
4921 END IF; -- SYSTEM_STATUS_CODE
4922
4923 IF (p_evt_regs_rec.DATE_REGISTRATION_PLACED = FND_API.g_miss_date)
4924 THEN
4925 x_complete_rec.DATE_REGISTRATION_PLACED := l_reg_rec.DATE_REGISTRATION_PLACED;
4926 END IF; -- DATE_REGISTRATION_PLACED
4927
4928 IF (p_evt_regs_rec.USER_STATUS_ID = FND_API.g_miss_num)
4929 THEN
4930 x_complete_rec.USER_STATUS_ID := l_reg_rec.USER_STATUS_ID;
4931 END IF; -- USER_STATUS_ID
4932
4933 IF (p_evt_regs_rec.LAST_REG_STATUS_DATE = FND_API.g_miss_date)
4934 THEN
4935 x_complete_rec.LAST_REG_STATUS_DATE := l_reg_rec.LAST_REG_STATUS_DATE;
4936 END IF; -- LAST_REG_STATUS_DATE
4937
4938 IF (p_evt_regs_rec.REG_SOURCE_TYPE_CODE = FND_API.g_miss_char)
4939 THEN
4940 x_complete_rec.REG_SOURCE_TYPE_CODE := l_reg_rec.REG_SOURCE_TYPE_CODE;
4941 END IF; -- REG_SOURCE_TYPE_CODE
4942
4943 IF (p_evt_regs_rec.REGISTRATION_SOURCE_ID = FND_API.g_miss_num)
4944 THEN
4945 x_complete_rec.REGISTRATION_SOURCE_ID := l_reg_rec.REGISTRATION_SOURCE_ID;
4946 END IF; -- REGISTRATION_SOURCE_ID
4947
4948 IF (p_evt_regs_rec.CONFIRMATION_CODE = FND_API.g_miss_char)
4949 THEN
4950 x_complete_rec.CONFIRMATION_CODE := l_reg_rec.CONFIRMATION_CODE;
4951 END IF; -- CONFIRMATION_CODE
4952
4953 IF (p_evt_regs_rec.SOURCE_CODE = FND_API.g_miss_char)
4954 THEN
4955 x_complete_rec.SOURCE_CODE := l_reg_rec.SOURCE_CODE;
4956 END IF; -- SOURCE_CODE
4957
4958 IF (p_evt_regs_rec.REGISTRATION_GROUP_ID = FND_API.g_miss_num)
4959 THEN
4960 x_complete_rec.REGISTRATION_GROUP_ID := l_reg_rec.REGISTRATION_GROUP_ID;
4961 END IF; -- REGISTRATION_GROUP_ID
4962
4963 IF (p_evt_regs_rec.REGISTRANT_PARTY_ID = FND_API.g_miss_num)
4964 THEN
4965 x_complete_rec.REGISTRANT_PARTY_ID := l_reg_rec.REGISTRANT_PARTY_ID;
4966 END IF; -- REGISTRANT_PARTY_ID
4967
4968 IF (p_evt_regs_rec.REGISTRANT_CONTACT_ID = FND_API.g_miss_num)
4969 THEN
4970 x_complete_rec.REGISTRANT_CONTACT_ID := l_reg_rec.REGISTRANT_CONTACT_ID;
4971 END IF; -- REGISTRANT_CONTACT_ID
4972
4973 IF (p_evt_regs_rec.REGISTRANT_ACCOUNT_ID = FND_API.g_miss_num)
4974 THEN
4975 x_complete_rec.REGISTRANT_ACCOUNT_ID := l_reg_rec.REGISTRANT_ACCOUNT_ID;
4976 END IF; -- REGISTRANT_ACCOUNT_ID
4977
4978 IF (p_evt_regs_rec.ATTENDANT_PARTY_ID = FND_API.g_miss_num)
4979 THEN
4980 x_complete_rec.ATTENDANT_PARTY_ID := l_reg_rec.ATTENDANT_PARTY_ID;
4981 END IF; -- ATTENDANT_PARTY_ID
4982
4983 IF (p_evt_regs_rec.ATTENDANT_CONTACT_ID = FND_API.g_miss_num)
4984 THEN
4985 x_complete_rec.ATTENDANT_CONTACT_ID := l_reg_rec.ATTENDANT_CONTACT_ID;
4986 END IF; -- ATTENDANT_CONTACT_ID
4987
4988 IF (p_evt_regs_rec.ATTENDANT_ACCOUNT_ID = FND_API.g_miss_num)
4989 THEN
4990 x_complete_rec.ATTENDANT_ACCOUNT_ID := l_reg_rec.ATTENDANT_ACCOUNT_ID;
4991 END IF; -- ATTENDANT_ACCOUNT_ID
4992
4993 IF (p_evt_regs_rec.ORIGINAL_REGISTRANT_CONTACT_ID = FND_API.g_miss_num)
4994 THEN
4995 x_complete_rec.ORIGINAL_REGISTRANT_CONTACT_ID := l_reg_rec.ORIGINAL_REGISTRANT_CONTACT_ID;
4996 END IF; -- ORIGINAL_REGISTRANT_CONTACT_ID
4997
4998 IF (p_evt_regs_rec.PROSPECT_FLAG = FND_API.g_miss_char)
4999 THEN
5000 x_complete_rec.PROSPECT_FLAG := l_reg_rec.PROSPECT_FLAG;
5001 END IF; -- PROSPECT_FLAG
5002
5003 IF (p_evt_regs_rec.ATTENDED_FLAG = FND_API.g_miss_char)
5004 THEN
5005 x_complete_rec.ATTENDED_FLAG := l_reg_rec.ATTENDED_FLAG;
5006 END IF; -- ATTENDED_FLAG
5007
5008 IF (p_evt_regs_rec.EVALUATED_FLAG = FND_API.g_miss_char)
5009 THEN
5010 x_complete_rec.EVALUATED_FLAG := l_reg_rec.EVALUATED_FLAG;
5011 END IF; -- EVALUATED_FLAG
5012
5013 IF (p_evt_regs_rec.CONFIRMED_FLAG = FND_API.g_miss_char)
5014 THEN
5015 x_complete_rec.CONFIRMED_FLAG := l_reg_rec.CONFIRMED_FLAG;
5016 END IF; -- CONFIRMED_FLAG
5017
5018 IF (p_evt_regs_rec.ATTENDANCE_RESULT_CODE = FND_API.g_miss_char)
5019 THEN
5020 x_complete_rec.ATTENDANCE_RESULT_CODE := l_reg_rec.ATTENDANCE_RESULT_CODE;
5021 END IF; -- ATTENDANCE_RESULT_CODE
5022
5023 IF (p_evt_regs_rec.WAITLISTED_PRIORITY = FND_API.g_miss_num)
5024 THEN
5025 x_complete_rec.WAITLISTED_PRIORITY := l_reg_rec.WAITLISTED_PRIORITY;
5026 END IF; -- WAITLISTED_PRIORITY
5027
5028 IF (p_evt_regs_rec.TARGET_LIST_ID = FND_API.g_miss_num)
5029 THEN
5030 x_complete_rec.TARGET_LIST_ID := l_reg_rec.TARGET_LIST_ID;
5031 END IF; -- TARGET_LIST_ID
5032
5033 IF (p_evt_regs_rec.INBOUND_MEDIA_ID = FND_API.g_miss_num)
5034 THEN
5035 x_complete_rec.INBOUND_MEDIA_ID := l_reg_rec.INBOUND_MEDIA_ID;
5036 END IF; -- INBOUND_MEDIA_ID
5037
5038 IF (p_evt_regs_rec.INBOUND_CHANNEL_ID = FND_API.g_miss_num)
5039 THEN
5040 x_complete_rec.INBOUND_CHANNEL_ID := l_reg_rec.INBOUND_CHANNEL_ID;
5041 END IF; -- INBOUND_CHANNEL_ID
5042
5043 IF (p_evt_regs_rec.CANCELLATION_CODE = FND_API.g_miss_char)
5044 THEN
5045 x_complete_rec.CANCELLATION_CODE := l_reg_rec.CANCELLATION_CODE;
5046 END IF; -- CANCELLATION_CODE
5047
5048 IF (p_evt_regs_rec.CANCELLATION_REASON_CODE = FND_API.g_miss_char)
5049 THEN
5050 x_complete_rec.CANCELLATION_REASON_CODE := l_reg_rec.CANCELLATION_REASON_CODE;
5051 END IF; -- CANCELLATION_REASON_CODE
5052
5053 IF (p_evt_regs_rec.ATTENDANCE_FAILURE_REASON = FND_API.g_miss_char)
5054 THEN
5055 x_complete_rec.ATTENDANCE_FAILURE_REASON := l_reg_rec.ATTENDANCE_FAILURE_REASON;
5056 END IF; -- ATTENDANCE_FAILURE_REASON
5057
5058 IF (p_evt_regs_rec.ATTENDANT_LANGUAGE = FND_API.g_miss_char)
5059 THEN
5060 x_complete_rec.ATTENDANT_LANGUAGE := l_reg_rec.ATTENDANT_LANGUAGE;
5061 END IF; -- ATTENDANT_LANGUAGE
5062
5063 IF (p_evt_regs_rec.SALESREP_ID = FND_API.g_miss_num)
5064 THEN
5065 x_complete_rec.SALESREP_ID := l_reg_rec.SALESREP_ID;
5066 END IF; -- SALESREP_ID
5067
5068 IF (p_evt_regs_rec.ORDER_HEADER_ID = FND_API.g_miss_num)
5069 THEN
5070 x_complete_rec.ORDER_HEADER_ID := l_reg_rec.ORDER_HEADER_ID;
5071 END IF; -- ORDER_HEADER_ID
5072
5073 IF (p_evt_regs_rec.ORDER_LINE_ID = FND_API.g_miss_num)
5074 THEN
5075 x_complete_rec.ORDER_LINE_ID := l_reg_rec.ORDER_LINE_ID;
5076 END IF; -- ORDER_LINE_ID
5077
5078 IF (p_evt_regs_rec.DESCRIPTION = FND_API.g_miss_char)
5079 THEN
5080 x_complete_rec.DESCRIPTION := l_reg_rec.DESCRIPTION;
5081 END IF; -- DESCRIPTION
5082
5083 IF (p_evt_regs_rec.MAX_ATTENDEE_OVERRIDE_FLAG = FND_API.g_miss_char)
5084 THEN
5085 x_complete_rec.MAX_ATTENDEE_OVERRIDE_FLAG := l_reg_rec.MAX_ATTENDEE_OVERRIDE_FLAG;
5086 END IF; -- MAX_ATTENDEE_OVERRIDE_FLAG
5087
5088 IF (p_evt_regs_rec.INVITE_ONLY_OVERRIDE_FLAG = FND_API.g_miss_char)
5089 THEN
5090 x_complete_rec.INVITE_ONLY_OVERRIDE_FLAG := l_reg_rec.INVITE_ONLY_OVERRIDE_FLAG;
5091 END IF; -- INVITE_ONLY_OVERRIDE_FLAG
5092
5093 IF (p_evt_regs_rec.PAYMENT_STATUS_CODE = FND_API.g_miss_char)
5094 THEN
5095 x_complete_rec.PAYMENT_STATUS_CODE := l_reg_rec.PAYMENT_STATUS_CODE;
5096 END IF; -- PAYMENT_STATUS_CODE
5097
5098 IF (p_evt_regs_rec.AUTO_REGISTER_FLAG = FND_API.g_miss_char)
5099 THEN
5100 x_complete_rec.AUTO_REGISTER_FLAG := l_reg_rec.AUTO_REGISTER_FLAG;
5101 END IF; -- AUTO_REGISTER_FLAG
5102
5103 IF (p_evt_regs_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char)
5104 THEN
5105 x_complete_rec.ATTRIBUTE_CATEGORY := l_reg_rec.ATTRIBUTE_CATEGORY;
5106 END IF; -- ATTRIBUTE_CATEGORY
5107
5108 IF (p_evt_regs_rec.ATTRIBUTE1 = FND_API.g_miss_char)
5109 THEN
5110 x_complete_rec.ATTRIBUTE1 := l_reg_rec.ATTRIBUTE1;
5111 END IF; -- ATTRIBUTE1
5112
5113 IF (p_evt_regs_rec.ATTRIBUTE2 = FND_API.g_miss_char)
5114 THEN
5115 x_complete_rec.ATTRIBUTE2 := l_reg_rec.ATTRIBUTE2;
5116 END IF; -- ATTRIBUTE2
5117
5118 IF (p_evt_regs_rec.ATTRIBUTE3 = FND_API.g_miss_char)
5119 THEN
5120 x_complete_rec.ATTRIBUTE3 := l_reg_rec.ATTRIBUTE3;
5121 END IF; -- ATTRIBUTE3
5122
5123 IF (p_evt_regs_rec.ATTRIBUTE4 = FND_API.g_miss_char)
5124 THEN
5125 x_complete_rec.ATTRIBUTE4 := l_reg_rec.ATTRIBUTE4;
5126 END IF; -- ATTRIBUTE4
5127
5128 IF (p_evt_regs_rec.ATTRIBUTE5 = FND_API.g_miss_char)
5129 THEN
5130 x_complete_rec.ATTRIBUTE5 := l_reg_rec.ATTRIBUTE5;
5131 END IF; -- ATTRIBUTE5
5132
5133 IF (p_evt_regs_rec.ATTRIBUTE6 = FND_API.g_miss_char)
5134 THEN
5138 IF (p_evt_regs_rec.ATTRIBUTE7 = FND_API.g_miss_char)
5135 x_complete_rec.ATTRIBUTE6 := l_reg_rec.ATTRIBUTE6;
5136 END IF; -- ATTRIBUTE6
5137
5139 THEN
5140 x_complete_rec.ATTRIBUTE7 := l_reg_rec.ATTRIBUTE7;
5141 END IF; -- ATTRIBUTE7
5142
5143 IF (p_evt_regs_rec.ATTRIBUTE8 = FND_API.g_miss_char)
5144 THEN
5145 x_complete_rec.ATTRIBUTE8 := l_reg_rec.ATTRIBUTE8;
5146 END IF; -- ATTRIBUTE8
5147
5148 IF (p_evt_regs_rec.ATTRIBUTE9 = FND_API.g_miss_char)
5149 THEN
5150 x_complete_rec.ATTRIBUTE9 := l_reg_rec.ATTRIBUTE9;
5151 END IF; -- ATTRIBUTE9
5152
5153 IF (p_evt_regs_rec.ATTRIBUTE10 = FND_API.g_miss_char)
5154 THEN
5155 x_complete_rec.ATTRIBUTE10 := l_reg_rec.ATTRIBUTE10;
5156 END IF; -- ATTRIBUTE10
5157
5158 IF (p_evt_regs_rec.ATTRIBUTE11 = FND_API.g_miss_char)
5159 THEN
5160 x_complete_rec.ATTRIBUTE11 := l_reg_rec.ATTRIBUTE11;
5161 END IF; -- ATTRIBUTE11
5162
5163 IF (p_evt_regs_rec.ATTRIBUTE12 = FND_API.g_miss_char)
5164 THEN
5165 x_complete_rec.ATTRIBUTE12 := l_reg_rec.ATTRIBUTE12;
5166 END IF; -- ATTRIBUTE12
5167
5168 IF (p_evt_regs_rec.ATTRIBUTE13 = FND_API.g_miss_char)
5169 THEN
5170 x_complete_rec.ATTRIBUTE13 := l_reg_rec.ATTRIBUTE13;
5171 END IF; -- ATTRIBUTE13
5172
5173 IF (p_evt_regs_rec.ATTRIBUTE14 = FND_API.g_miss_char)
5174 THEN
5175 x_complete_rec.ATTRIBUTE14 := l_reg_rec.ATTRIBUTE14;
5176 END IF; -- ATTRIBUTE14
5177
5178 IF (p_evt_regs_rec.ATTRIBUTE15 = FND_API.g_miss_char)
5179 THEN
5180 x_complete_rec.ATTRIBUTE15 := l_reg_rec.ATTRIBUTE15;
5181 END IF; -- ATTRIBUTE15
5182
5183 -- Hornet: following six fields added for imeeting integration
5184 IF (p_evt_regs_rec.attendee_role_type = FND_API.g_miss_char)
5185 THEN
5186 x_complete_rec.attendee_role_type := l_reg_rec.attendee_role_type;
5187 END IF; -- attendee_role_type
5188
5189 IF (p_evt_regs_rec.notification_type = FND_API.g_miss_char)
5190 THEN
5191 x_complete_rec.notification_type := l_reg_rec.notification_type;
5192 END IF; -- notification_type
5193
5194 IF (p_evt_regs_rec.last_notified_time = FND_API.g_miss_date)
5195 THEN
5196 x_complete_rec.last_notified_time := l_reg_rec.last_notified_time;
5197 END IF; -- last_notified_time
5198
5199 IF (p_evt_regs_rec.EVENT_JOIN_TIME = FND_API.g_miss_date)
5200 THEN
5201 x_complete_rec.EVENT_JOIN_TIME := l_reg_rec.EVENT_JOIN_TIME;
5202 END IF; -- EVENT_JOIN_TIME
5203
5204 IF (p_evt_regs_rec.EVENT_EXIT_TIME = FND_API.g_miss_date)
5205 THEN
5206 x_complete_rec.EVENT_EXIT_TIME := l_reg_rec.EVENT_EXIT_TIME;
5207 END IF; -- EVENT_EXIT_TIME
5208
5209 IF (p_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE = FND_API.g_miss_char)
5210 THEN
5211 x_complete_rec.MEETING_ENCRYPTION_KEY_CODE := l_reg_rec.MEETING_ENCRYPTION_KEY_CODE;
5212 END IF; -- MEETING_ENCRYPTION_KEY_CODE
5213
5214 END COMPLETE_EVTREG_REC;
5215
5216 --========================================================================
5217 -- PROCEDURE
5218 -- write_interaction
5219 --
5220 -- PURPOSE
5221 -- This api is called in update_Status to write to interaction history
5222 -- if it was DIRECT_MARKETING Direct Mail
5223 --
5224 -- NOTE
5225 --
5226 -- HISTORY
5227 -- 19-mar-2002 soagrawa Created to log interactions for
5228 -- DIRECT_MARKETING MAIL
5229 -- 08-APR-2002 dcastlem Copied from AMS_ScheduleRules_PVT
5230 -- 08-Mar-2003 ptendulk Modified start date, end date as system for NI
5231 -- 27-may-2003 soagrawa Fixed NI issue about result of interaction bug# 2978948
5232 --========================================================================
5233
5234 PROCEDURE write_interaction( p_event_offer_id IN NUMBER
5235 , p_party_id IN NUMBER
5236 )
5237
5238 IS
5239
5240 -- CURSOR:
5241 -- get the target grp for this CSCH
5242 -- get the list entries from that target group
5243 -- get the party_id for those list entries
5244
5245 CURSOR c_event_details IS
5246 SELECT event_object_type,
5247 source_code,
5248 reg_start_date,
5249 event_end_date,
5250 owner_user_id
5251 FROM ams_event_offers_all_b
5252 WHERE event_offer_id = p_event_offer_id;
5253
5254 CURSOR c_media_item_id IS
5255 SELECT JTF_IH_MEDIA_ITEMS_S1.NEXTVAL
5256 FROM dual;
5257
5258 CURSOR c_interactions_id IS
5259 SELECT jtf_ih_interactions_s1.NEXTVAL
5260 FROM dual;
5261
5262 CURSOR c_activities_id IS
5263 SELECT JTF_IH_ACTIVITIES_S1.NEXTVAL
5264 FROM dual;
5265
5266 CURSOR c_user(p_resource_id IN NUMBER) IS
5267 SELECT user_id
5268 FROM ams_jtf_rs_emp_v
5269 WHERE resource_id = p_resource_id;
5270
5271 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
5272 l_activities JTF_IH_PUB.activity_tbl_type;
5273 l_activity_rec JTF_IH_PUB.activity_rec_type;
5274 l_media_rec JTF_IH_PUB.media_rec_type;
5275 l_interaction_id NUMBER;
5276 l_media_id NUMBER;
5277
5278 l_start_time DATE;
5279 l_end_time DATE;
5280 l_owner_id NUMBER;
5281 l_source_code VARCHAR2(30);
5282 l_object_type VARCHAR2(30);
5283
5284 l_return_status VARCHAR2(1);
5285 l_msg_count NUMBER;
5286 l_msg_data VARCHAR2(2000);
5287 l_user_id NUMBER;
5288
5289 BEGIN
5290
5291 OPEN c_event_details;
5292 FETCH c_event_details
5293 INTO l_object_type,
5294 l_source_code,
5295 l_start_time,
5296 l_end_time,
5297 l_owner_id;
5298 CLOSE c_event_details;
5299
5300 OPEN c_user(l_owner_id);
5301 FETCH c_user
5302 INTO l_user_id;
5303 CLOSE c_user;
5304 --l_user_id := get_user_id(p_resource_id => l_owner_id);
5305
5306 -- populate media_rec
5307 OPEN c_media_item_id;
5308 FETCH c_media_item_id INTO l_media_rec.media_id ;
5309 CLOSE c_media_item_id;
5310 -- l_media_rec.media_id := JTF_IH_MEDIA_ITEMS_S1.nextval;
5311 l_media_rec.end_date_time := SYSDATE ; -- l_end_time ; Modified by ptendulk
5312 l_media_rec.start_date_time := SYSDATE ; -- l_start_time ; Modified by ptendulk
5313 l_media_rec.media_item_type := 'MAIL' ;
5314
5315 -- create media_rec
5316 JTF_IH_PUB.Create_MediaItem
5317 (
5318 p_api_version => 1.0,
5319 p_init_msg_list => FND_API.g_false,
5320 p_commit => FND_API.g_false,
5321 -- p_resp_appl_id => l_resp_appl_id,
5322 -- p_resp_id => l_resp_id,
5323 p_user_id => l_user_id,
5324 -- p_login_id => l_login_id,
5325 x_return_status => l_return_status,
5326 x_msg_count => l_msg_count,
5327 x_msg_data => l_msg_data,
5328 p_media_rec => l_media_rec,
5329 x_media_id => l_media_id
5330 );
5331 IF l_return_status <> FND_API.g_ret_sts_success THEN
5332 RAISE FND_API.g_exc_error;
5333 RETURN;
5334 END IF;
5335
5336 IF (AMS_DEBUG_HIGH_ON) THEN
5337
5338
5339
5340 AMS_Utility_PVT.debug_message('Write interaction: created media item ');
5341
5342 END IF;
5343
5344
5345 IF (AMS_DEBUG_HIGH_ON) THEN
5346
5347
5348
5349
5350
5351 AMS_Utility_PVT.debug_message('Write interaction: looping for party id ');
5352
5353
5354 END IF;
5355
5356 -- populate interaction record
5357 /*OPEN c_interactions_id;
5358 FETCH c_interactions_id INTO l_interaction_id ;
5359 CLOSE c_interactions_id;*/
5360 -- l_interaction_id := jtf_ih_interactions_s1.nextval ;
5361
5362 --l_interaction_rec.interaction_id := l_interaction_id ;
5363 l_interaction_rec.end_date_time := l_end_time ;
5364 l_interaction_rec.start_date_time := l_start_time ;
5365 l_interaction_rec.handler_id := 530 ;
5366 l_interaction_rec.outcome_id := 10 ; -- request processed
5367 -- soagrawa added on 27-may-2003 for NI interaction issue bug# 2978948
5368 l_interaction_rec.result_id := 8 ; -- sent
5369 l_interaction_rec.resource_id := l_owner_id ;
5370 l_interaction_rec.party_id := p_party_id ; -- looping for all party ids in the list
5371 l_interaction_rec.object_id := p_event_offer_id ;
5372 l_interaction_rec.object_type := l_object_type;
5373 l_interaction_rec.source_code := l_source_code;
5374
5375 -- populate activity record
5376 /*OPEN c_activities_id;
5377 FETCH c_activities_id INTO l_activity_rec.activity_id ;
5378 CLOSE c_activities_id;*/
5379 -- l_activity_rec.activity_id := JTF_IH_ACTIVITIES_S1.nextval ;
5380 l_activity_rec.end_date_time := SYSDATE ; -- l_end_time ; Modified by ptendulk
5381 l_activity_rec.start_date_time := SYSDATE ; -- l_start_time ; Modified by ptendulk
5382 l_activity_rec.media_id := l_media_id ;
5383 l_activity_rec.action_item_id := 42 ; -- Event Enrollment
5384 --l_activity_rec.interaction_id := l_interaction_id ;
5385 l_activity_rec.outcome_id := 10 ; -- request processed
5386 l_activity_rec.result_id := 8 ; -- sent
5387 l_activity_rec.action_id := 5 ; -- sent
5388
5389 -- populate activity table with the activity record
5390 l_activities(1) := l_activity_rec;
5391
5392 -- create interaction
5393 JTF_IH_PUB.Create_Interaction
5394 (
5395 p_api_version => 1.0,
5396 p_init_msg_list => FND_API.g_false,
5397 p_commit => FND_API.g_false,
5398 -- p_resp_appl_id => l_resp_appl_id, -- 530
5399 -- p_resp_id => l_resp_id, -- fnd global
5400 p_user_id => l_user_id,
5401 -- p_login_id => l_login_id,
5402 x_return_status => l_return_status,
5403 x_msg_count => l_msg_count,
5404 x_msg_data => l_msg_data,
5405 p_interaction_rec => l_interaction_rec,
5406 p_activities => l_activities
5407 );
5408 IF l_return_status <> FND_API.g_ret_sts_success THEN
5409 RAISE FND_API.g_exc_error;
5410 RETURN;
5411 END IF;
5412
5413 END write_interaction;
5414
5415
5416
5417
5418
5419 End AMS_EvtRegs_PVT;