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