[Home] [Help]
PACKAGE BODY: APPS.OTA_CANCEL_API
Source
1 Package body ota_cancel_api as
2 /* $Header: ottomint.pkb 120.58.12020000.3 2012/06/29 11:19:18 rpahune ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7
8 g_package varchar2(33) := ' ota_cancel_api.'; -- Global package name
9
10 l_conc_request_id fnd_concurrent_requests.request_id%TYPE := -1;
11 l_debug_msg VARCHAR2(2000);
12 l_status VARCHAR2(80);
13 l_err_num VARCHAR2(30) := '';
14 l_err_msg VARCHAR2(1000) := '';
15 l_return_boolean BOOLEAN := FALSE;
16 l_exception_message VARCHAR2(240) := '';
17 l_user_id number := fnd_profile.value('USER_ID');
18 l_login_id number := fnd_profile.value('LOGIN_ID');
19 l_sob_id NUMBER;
20 -- ----------------------------------------------------------------------------
21 -- |---------------------------< delete_cancel_line>--------------------------|
22 -- ----------------------------------------------------------------------------
23 -- {Start Of Comments}
24 --
25 -- Description:
26 -- This procedure will be used to update delegate booking and event table.
27 --
28 -- This procedure will only be used for OTA and OM integration. The prurpose
29 -- of this procedure is only be called by OM Process Order API when the order
30 -- line got canceled or deleted. This procedure being created because Order
31 -- Management doesnot support workflow for Cancel or delete Order Line.
32 --
33 -- Pre Conditions:
34 -- None.
35 --
36 -- In Arguments:
37 -- p_line_id,
38 -- p_org_id
39 -- p_uom
40 -- p_daemon_type
41 --
42 -- Post Success:
43 -- Processing continues.
44 --
45 --
46 -- Post Failure:
47 -- None.
48 --
49 -- Access Status:
50 -- Public.
51 --
52 -- {End Of Comments}
53 ----------------------------------------------------------------------------
54 Procedure delete_cancel_line
55 (
56 p_line_id IN Number,
57 p_org_id IN Number,
58 p_UOM IN Varchar2,
59 P_daemon_type IN varchar2,
60 x_return_status OUT NOCOPY varchar2)
61
62 is
63
64 l_proc varchar2(72) := g_package||'cnc_evt_enr';
65
66 l_event_id ota_events.event_id%type;
67 l_event_business_group_id ota_events.business_group_id%type;
68 l_event_status varchar2(100);
69 l_title ota_events_tl.title%type; -- MLS change _tl added
70 l_owner_id ota_events.owner_id%type := null;
71 l_owner_email varchar2(100);
72 l_type varchar2(4);
73 l_event_ovn ota_events.object_version_number%type;
74 l_full_name per_people_f.full_name%type;
75
76 l_booking_id ota_delegate_bookings.booking_id%type;
77 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%type;
78 l_enr_business_group_id ota_delegate_bookings.business_group_id%type;
79 l_enr_ovn ota_delegate_bookings.object_version_number%type;
80 l_booking_status_type ota_booking_status_types.type%type;
81 l_tfl_ovn ota_finance_lines.object_version_number%type;
82 l_new_fl ota_finance_lines.finance_line_id%type;
83 l_user_name fnd_user.user_name%type;
84
85 l_cancel_hours number := FND_PROFILE.VALUE('OTA_AUTO_WAITLIST_DAYS');
86
87 l_different_hours number(11,3);
88 l_event_date date;
89 l_current_date date;
90 l_sysdate date;
91 l_wf_date varchar2(30);
92 -- l_event_owner_id ota_events.owner_id%type;
93
94 l2_event_status OTA_EVENTS.EVENT_STATUS%TYPE;
95 --
96 CURSOR C_event IS
97 SELECT
98 Event_ID,
99 Business_Group_ID ,
100 Event_status,
101 Title,
102 Owner_Id,
103 Object_Version_number
104 FROM OTA_Events_vl --MLS change _vl added
105 WHERE Line_Id = p_line_Id;
106
107 CURSOR C_PEOPLE IS
108 SELECT
109 email_address, full_name
110 FROM
111 per_all_people_f
112 WHERE
113 person_id = l_owner_id and
114 trunc(sysdate) between
115 effective_start_date and
116 effective_end_date;
117
118 CURSOR C_USER IS
119 SELECT
120 USER_NAME
121 FROM
122 FND_USER
123 WHERE
124 Employee_id = l_owner_id
125 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
126
127 --
128
129 CURSOR C_ENROLLMENT IS
130 SELECT
131 tdb.Booking_id,
132 tdb.Booking_status_type_id,
133 tdb.Business_group_id,
134 tdb.Object_version_number,
135 tdb.event_id,
136 evt.title
137 FROM
138 OTA_DELEGATE_BOOKINGS tdb,
139 OTA_EVENTS_tl evt -- MLS change _tl added
140 WHERE
141 tdb.Line_id = p_line_id and
142 evt.event_id = tdb.event_id;
143
144 --
145
146 CURSOR C_BOOKING_STATUS IS
147 SELECT
148 Type
149 FROM
150 OTA_BOOKING_STATUS_TYPES
151 WHERE
152 booking_status_type_id = l_booking_status_type_id;
153 --bug # 5231470 first Date format changed to DD/MM/YYYY from DD-MON-YYYY
154 CURSOR C_EVENT_DATE (p_event_id ota_events.event_ID%type) IS
155 SELECT to_date(to_char(evt.Course_start_date,'DD/MM/YYYY')||EVT.Course_start_time,'DD/MM/YYYYHH24:MI'),
156 OWNER_ID
157 FROM OTA_EVENTS EVT
158 WHERE evt.event_id = p_event_id;
159
160 CURSOR c_sysdate IS
161 SELECT
162 sysdate
163 FROM
164 dual;
165
166 Begin
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 --
169 OPEN c_sysdate;
170 FETCH c_sysdate INTO l_sysdate;
171 CLOSE c_sysdate;
172
173 x_return_status := 'S';
174 IF p_uom = 'EVT' THEN
175 OPEN C_EVENT;
176 FETCH C_EVENT INTO l_event_id,
177 l_event_business_group_id,
178 l_Event_status,
179 l_Title,
180 l_Owner_Id,
181 l_event_ovn;
182 IF c_event%found then
183 /* OPEN c_people;
184 FETCH c_people into l_owner_email,l_full_name ;
185 IF c_people%found then */
186 OPEN C_USER;
187 FETCH C_USER INTO l_user_name;
188 IF p_daemon_type = 'C' THEN
189 hr_utility.set_location('Entering:'||l_proc, 10);
190
191 -- Bug # 2707198
192
193 OTA_INITIALIZATION_WF.INITIALIZE_CANCEL_ORDER (
194 p_itemtype => 'OTWF',
195 p_process => 'OTA_CANCEL_ORDER_LINE_2',
196 p_Event_title => l_title ,
197 p_event_id => l_event_id,
198 p_user_name => l_user_name,
199 p_line_id => p_line_id,
200 p_status => 'C',
201 p_full_name => l_full_name);
202
203 hr_approval_wf.create_item_attrib_if_notexist (p_item_type => 'OTWF'
204 ,p_item_key => to_char(p_line_id)
205 ,p_name => 'CALLER_SOURCE');
206
207 WF_ENGINE.setitemattrtext('OTWF',
208 to_char(p_line_id),
209 'CALLER_SOURCE',
210 'ONT');
211 -- Bug # 2707198
212
213
214 --Enh#1753511 HDSHAH
215 l_booking_status_type_id := Fnd_profile.value('OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS');
216 l2_event_status := 'A';
217 OTA_EVT_API_UPD2.UPDATE_EVENT (
218 P_EVENT => 'STATUS',
219 P_EVENT_ID => l_event_id,
220 P_OBJECT_VERSION_NUMBER => l_event_ovn,
221 P_EVENT_STATUS => l2_event_status,
222 P_VALIDATE => false,
223 P_BOOKING_STATUS_TYPE_ID => l_booking_status_type_id,
224 P_UPDATE_FINANCE_LINE => 'C',
225 P_DATE_STATUS_CHANGED => l_sysdate);
226
227
228 /* OTA_INITIALIZATION_WF.INITIALIZE_CANCEL_ORDER (
229 p_itemtype => 'OTWF',
230 p_process => 'OTA_CANCEL_ORDER_LINE_2',
231 p_Event_title => l_title ,
232 p_event_id => l_event_id,
233 p_user_name => l_user_name,
234 p_line_id => p_line_id,
235 p_status => 'C',
236 p_full_name => l_full_name); */
237
238 --Enh#1753511 HDSHAH
239
240 /* Hitesh Shah
241 OTA_INITIALIZATION_WF.INITIALIZE_CANCEL_ORDER (
242 p_itemtype => 'OTWF',
243 p_process => 'OTA_CANCEL_ORDER_LINE',
244 p_Event_title => l_title ,
245 p_event_id => l_event_id,
246 p_user_name => l_user_name,
247 p_line_id => p_line_id,
248 p_status => 'C',
249 p_full_name => l_full_name);
250 */
251
252 ELSIF p_daemon_type = 'D' THEN
253 hr_utility.set_location('Entering:'||l_proc, 15);
254 ota_evt_upd.upd(
255 p_Event_id => l_Event_Id
256 ,P_Business_Group_id => l_event_Business_group_id
257 ,P_Object_version_number => l_event_ovn
258 ,p_comments => 'The Order Line for this event has been deleted.'
259 ,p_Line_id => Null
260 ,p_Org_id => Null
261 ,P_validate => False);
262
263 hr_utility.set_location('Entering:'||l_proc, 20);
264
265
266 OTA_INITIALIZATION_WF.INITIALIZE_CANCEL_ORDER (
267 p_itemtype => 'OTWF',
268 p_process => 'OTA_CANCEL_ORDER_LINE',
269 p_Event_title => l_title ,
270 p_event_id => l_event_id,
271 p_user_name => l_user_name,
272 p_line_id => p_line_id,
273 p_status => 'D',
274 p_full_name => l_full_name);
275
276 ELSIF p_daemon_type = 'P' THEN
277 hr_utility.set_location('Entering:'||l_proc, 15);
278 ota_evt_upd.upd(
279 p_Event_id => l_Event_Id
280 ,P_Business_Group_id => l_event_Business_group_id
281 ,P_Object_version_number => l_event_ovn
282 ,p_comments => 'The Order Line for this event has been closed.'
283 ,p_Line_id => Null
284 ,p_Org_id => Null
285 ,P_validate => False);
286
287 hr_utility.set_location('Entering:'||l_proc, 20);
288
289 END IF;
290 CLOSE C_USER;
291 /* END IF;
292 CLOSE c_people; */
293 END IF;
294 CLOSE C_EVENT;
295
296 ELSIF p_uom = 'ENR' THEN
297
298 hr_utility.set_location('Entering:'||l_proc, 25);
299 BEGIN
300 OPEN C_ENROLLMENT;
301 FETCH c_enrollment into
302 l_booking_id,
303 l_Booking_status_type_id,
304 l_enr_Business_group_id,
305 l_enr_ovn,
306 l_event_id,
307 l_title;
308 IF c_enrollment%found THEN
309
310 OPEN c_event_date(l_event_id);
311 FETCH c_event_date into l_event_date,l_owner_id;
312 CLOSE c_event_date;
313
314 OPEN c_sysdate;
315 FETCH c_sysdate INTO l_sysdate;
316 CLOSE c_sysdate;
317
318 l_different_hours := l_event_date - l_sysdate ;
319 l_different_hours := l_different_hours * 24 ;
320 IF l_different_hours <= nvl(l_cancel_hours,0) THEN
321 OPEN C_USER;
322 FETCH C_USER INTO l_user_name;
323 select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') into l_wf_date from dual;
324
325 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(
326 p_itemtype => 'OTWF',
327 p_process => 'OTA_MANUAL_WAITLIST',
328 p_Event_title => l_title ,
329 p_event_id => l_event_id,
330 p_item_key => l_booking_id||':'||l_wf_date,
331 p_user_name => l_user_name);
332 END IF;
333 OPEN C_BOOKING_STATUS;
334 FETCH c_booking_status into
335 l_booking_status_type;
336 IF c_booking_status%found then
337 IF l_booking_status_type not in('A','C') then
338 hr_utility.set_location('Entering:'||l_proc, 30);
339 ota_tdb_api_upd2.update_enrollment(
340 p_booking_id => l_booking_id
341 ,p_object_version_number => l_enr_ovn
342 ,p_event_id => l_event_id
343 ,p_daemon_flag => 'Y'
344 ,p_tfl_object_version_number => l_tfl_ovn
345 ,p_booking_status_type_id => l_Booking_status_type_id
346 -- ,p_update_finance_line => 'N'
347 ,p_finance_line_id => l_new_fl
348 ,p_daemon_type => p_daemon_type
349 ,p_status_change_comments => null); /* Bug# 3469326 */
350
351 END IF;
352 END IF;
353 CLOSE C_BOOKING_status;
354 END IF;
355 CLOSE C_ENROLLMENT;
356
357 EXCEPTION WHEN
358 OTHERS THEN
359 x_return_status := 'E';
360 RAISE;
361 END;
362 END IF;
363 --
364 hr_utility.set_location(' Leaving:'||l_proc, 40);
365 EXCEPTION WHEN
366 OTHERS THEN
367 x_return_status := 'E';
368 RAISE;
369 END;
370
371
375 -- {Start Of Comments}
372 -- ----------------------------------------------------------------------------
373 -- |---------------------------------< cancel_enrollment >--------------------|
374 -- ----------------------------------------------------------------------------
376 --
377 -- Description:
378 -- This procedure will be a concurrent process which run in the background.
379 --
380 -- This procedure will only be used for OTA and OM integration. Basically this
381 -- procedure will select all delegate booking data that has daemon_flag='Y' and
382 -- Daemon_type is not nul. If the enrollment got canceled and there is a
383 -- waitlisted student then the automatic waitlist processing will be called.
384 --
385 -- Pre Conditions:
386 -- None.
387 --
388 -- In Arguments:
389 -- p_user_id,
390 -- p_login_id
391 --
392 -- Post Success:
393 -- Processing continues.
394 --
395 --
396 -- Post Failure:
397 -- None.
398 --
399 -- Access Status:
400 -- Public.
401 --
402 -- {End Of Comments}
403 ----------------------------------------------------------------------------
404
405 Procedure cancel_enrollment
406 (p_user_id in number,
407 p_login_id in number)IS
408
409
410 l_status_name varchar2(80);
411 l_status_id ota_delegate_bookings.booking_status_type_id%type;
412 -- Define Local variable for booking
413
414 l_event_id ota_delegate_bookings.event_id%type;
415 l_old_event_id ota_delegate_bookings.event_id%type; -- added for bug #1632104
416 l_booking_id ota_delegate_bookings.booking_id%type;
417 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%type;
418 l_enr_business_group_id ota_delegate_bookings.business_group_id%type;
419 l_enr_ovn ota_delegate_bookings.object_version_number%type;
420 l_booking_status_type ota_booking_status_types.type%type;
421 l_daemon_flag ota_delegate_bookings.daemon_flag%type;
422 l_daemon_type ota_delegate_bookings.daemon_type%type;
423 l_line_id ota_delegate_bookings.line_id%type;
424 l_event_title ota_events_tl.title%type; -- MLS change _tl added
425 l_business_group_id ota_delegate_bookings.business_group_id%type;
426 l_single_business_group_id ota_delegate_bookings.business_group_id%type:=
427 fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
428 -- Define local variable for status
429 l_status_type_id ota_delegate_bookings.booking_status_type_id%type;
430
431 -- Define local variable for lines
432 l_line_ovn ota_finance_lines.object_version_number%type;
433 l_Finance_Line_id ota_finance_lines.finance_line_id%type;
434 l_Finance_header_id ota_finance_lines.finance_header_id%type;
435 l_line_transfer_Status ota_finance_lines.transfer_status%type;
436 l_Sequence_Number ota_finance_lines.sequence_number%type;
437 l_Date_raised ota_finance_lines.date_raised%type;
438
439 -- Define local variable for header;
440 l_header_ovn ota_finance_headers.object_version_number%type;
441 l_header_Transfer_status ota_finance_headers.transfer_status%type;
442 -- Define local variable for other
443 l_auto_waitlist varchar2(1);
444 l_sysdate date;
445 l_count number(6);
446 l_tfl_ovn ota_finance_lines.object_version_number%type;
447 l_new_fl ota_finance_lines.finance_line_id%type;
448 l_return_status varchar2(1):= 'T';
449 e_validation_error exception;
450
451 l_cancel_hours number := FND_PROFILE.VALUE('OTA_AUTO_WAITLIST_DAYS');
452
453 l_different_hours number(11,3);
454 l_event_date date;
455 l_current_date date;
456 l_owner_id ota_events.owner_id%type;
457
458 l_date_booking_placed date;
459
460 -- Define Enrollment cursor
461
462 CURSOR C_ENROLLMENT IS
463 SELECT
464 Event_id,
465 Booking_id,
466 Booking_status_type_id,
467 Business_group_id,
468 Object_version_number,
469 daemon_flag,
470 daemon_type,
471 line_id,
472 old_event_id, -- added for bug #1632104
473 date_booking_placed -- Added for bug 1708632
474 FROM
475 OTA_DELEGATE_BOOKINGS
476 WHERE
477 (daemon_flag = 'Y' OR daemon_flag IS NULL) and
478 daemon_type is not null and
479 business_group_id = l_business_group_id;
480 --fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
481 -- FOR UPDATE nowait;
482
483 -- Define status cursor
484
485 CURSOR C_STATUS IS
486 SELECT
487 BOOKING_STATUS_TYPE_ID
488 FROM
489 OTA_BOOKING_STATUS_TYPES
490 WHERE
491 Name = l_status_name;
492
493 -- Define booking status cursor
494
495 CURSOR C_BOOKING_STATUS IS
496 SELECT
497 name
498 FROM
499 OTA_BOOKING_STATUS_TYPES_TL -- MLS change _TL added
500 WHERE
501 booking_status_type_id = l_booking_status_type_id;
502
503 -- Define finance line cursor
504
505 CURSOR C_LINES IS
506 SELECT
507 Finance_Line_id,
508 Finance_header_id,
509 Transfer_Status,
510 Object_version_number,
511 Sequence_Number,
512 Date_raised
513 FROM
514 OTA_FINANCE_LINES
515 WHERE Booking_id = l_Booking_id;
516
517 -- Define finance header cursor
518
519
520 CURSOR C_HEADER IS
521 SELECT
522 Object_version_number,
523 Transfer_status
524 FROM
525 ota_finance_headers
526 WHERE
527 finance_header_id = l_finance_header_id;
528
529 -- define caount finance line cursor
530
531 CURSOR c_count IS
532 SELECT
533 count(finance_line_id)
534 FROM
535 ota_finance_lines
536 WHERE
537 finance_header_id = l_finance_header_id;
538
539 CURSOR c_sysdate IS
540 SELECT
541 sysdate
542 FROM
543 dual;
544 --bug # 5231470 first date format changed to DD/MM/YYYY from DD-MON-YYYY
545 CURSOR C_EVENT_DATE (p_event_id ota_events.event_ID%type) IS
546 SELECT to_date(to_char(evt.Course_start_date,'DD/MM/YYYY')||EVT.Course_start_time,'DD/MM/YYYYHH24:MI'),
547 OWNER_ID
548 FROM OTA_EVENTS EVT
549 WHERE evt.event_id = p_event_id;
550
551
552 l_proc varchar2(72) := g_package||'cancel_enrollment';
553
554
555 BEGIN
556
557 hr_utility.set_location('Entering:'|| l_proc, 5);
558 -- l_status_name := Fnd_profile.value('OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS');
559 l_status_id := Fnd_profile.value('OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS');
560 l_auto_waitlist := Fnd_profile.value('OTA_AUTO_WAITLIST_ACTIVE');
561
562 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Start test of concurrent program');
563 IF l_single_business_group_id is not null then
564 l_business_group_id := l_single_business_group_id;
565 ELSE
566 l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
567 END IF;
568
569 OPEN C_ENROLLMENT;
570 LOOP
571 hr_utility.set_location(l_proc, 10);
572
573 FETCH c_enrollment into
574 l_event_id,
575 l_booking_id,
576 l_Booking_status_type_id,
577 l_enr_Business_group_id,
578 l_enr_ovn,
579 l_daemon_flag,
580 l_daemon_type,
581 l_line_id,
582 l_old_event_id, -- added for bug #1632104
583 l_date_booking_placed; -- Added for bug# 1708632
584 EXIT when c_enrollment%notfound;
585 BEGIN
586 savepoint Cancel_enrollment;
587 l_return_status := 'T';
588 IF l_daemon_type = 'C' then
589 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Daemon type :' || l_daemon_type );
590 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing booking id : ' || l_booking_id);
591 OPEN c_lines;
592 FETCH c_lines INTO l_Finance_Line_id,
593 l_Finance_header_id,
594 l_line_transfer_Status,
595 l_line_ovn,
596 l_Sequence_Number,
597 l_Date_raised ;
598 IF c_lines%found THEN
599
600 IF l_line_transfer_status <> 'ST' THEN
601 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cancelling Finance Line for booking id : ' || l_booking_id);
602 hr_utility.set_location('Entering:'|| l_proc, 10);
603 BEGIN
604 ota_tfl_api_upd.upd(
605 p_finance_line_id => l_finance_line_id,
606 p_date_raised => l_date_raised,
607 p_cancelled_flag => 'Y',
608 p_object_version_number => l_line_ovn,
609 p_transfer_status => l_line_transfer_Status,
610 p_sequence_number => l_sequence_number,
611 p_validate => false,
612 p_transaction_type => 'UPDATE');
613 exception when others then
614 l_err_num := SQLCODE;
615 l_err_msg := SUBSTR(SQLERRM, 1, 100);
616 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
617 'Booking Id :'||to_char(l_booking_id)||','||
618 'Finance_line_id :'||to_char(l_finance_line_id)||','||l_err_msg);
619 l_return_status := 'F';
620 END;
621 OPEN c_count;
622 FETCH c_count INTO l_count;
623 IF l_count = 1 THEN
624
625 OPEN c_header;
626 FETCH c_header INTO l_header_ovn,
627 l_header_Transfer_status;
628 IF c_header%found THEN
629
630 IF l_header_transfer_status <> 'ST' THEN
631 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cancelling Finance Header for booking id : ' || l_booking_id);
632 BEGIN
633 hr_utility.set_location('Entering:'|| l_proc, 30);
634 ota_tfh_api_upd.upd( p_finance_header_id => l_finance_header_id
635 ,p_object_version_number => l_header_ovn
636 ,p_cancelled_flag => 'Y'
637 ,p_validate => False
638 ,p_Transaction_type => 'UPDATE');
639 exception when others then
640 l_err_num := SQLCODE;
641 l_err_msg := SUBSTR(SQLERRM, 1, 100);
642 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
643 'Booking Id :'||to_char(l_booking_id)||','||
644 'Finance_header_id :'||to_char(l_finance_header_id)||','||l_err_msg);
648 END IF;
645 l_return_status := 'F';
646 END;
647
649 END IF;
650 CLOSE C_header;
651 END IF;
652 CLOSE c_count;
653 END IF;
654 END IF;
655 CLOSE C_lines;
656
657 IF l_status_id is not null THEN
658 BEGIN
659 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating booking id : ' || l_booking_id);
660 hr_utility.set_location('Entering:'|| l_proc, 40);
661 ota_tdb_shd.lck(l_booking_id,l_enr_ovn);
662 ota_tdb_api_upd2.Update_Enrollment(
663 p_booking_id => l_booking_id
664 ,p_booking_status_type_id => l_status_id
665 ,p_event_id => l_event_id
666 ,p_business_group_id => l_enr_business_group_id
667 ,p_object_version_number => l_enr_ovn
668 ,p_update_finance_line => 'N'
669 ,p_finance_line_id => l_new_fl
670 ,p_tfl_object_version_number => l_tfl_ovn
671 ,p_validate => False
672 ,p_daemon_flag => null
673 ,p_daemon_type => null
674 ,p_date_status_changed => sysdate -- Added for bug# 1708632
675 ,p_date_booking_placed => l_date_booking_placed -- Added for bug# 1708632
676 ,p_status_change_comments => null); /* Bug# 3469326 */
677 exception when others then
678 l_err_num := SQLCODE;
679 l_err_msg := SUBSTR(SQLERRM, 1, 500);
680 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
681 to_char(l_booking_id)||','||l_err_msg);
682 l_return_status := 'F';
683
684 END;
685 ELSE
686 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
687 to_char(l_booking_id)||','||'OTA:OM Default Enrollment Cancalled Status profile '||
688 'value has not been defined yet' );
689 l_return_status := 'F';
690
691 END IF;
692
693 ELSIF l_daemon_type = 'P' THEN
694 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Daemon type :' || l_daemon_type );
695 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing booking id : ' || l_booking_id);
696 hr_utility.set_location('Entering:'|| l_proc, 45);
697 BEGIN
698 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating booking id : ' || l_booking_id);
699 ota_tdb_shd.lck(l_booking_id,l_enr_ovn);
700 ota_tdb_api_upd2.Update_Enrollment(
701 p_booking_id => l_booking_id
702 ,p_booking_status_type_id => l_booking_status_type_id
703 ,p_event_id => l_event_id
704 ,p_business_group_id => l_enr_business_group_id
705 ,p_object_version_number => l_enr_ovn
706 ,p_update_finance_line => 'N'
707 ,p_finance_line_id => l_new_fl
708 ,p_validate => False
709 ,p_tfl_object_version_number => l_tfl_ovn
710 ,P_Line_id => null
711 ,p_Org_id => null
712 ,p_daemon_flag => null
713 ,p_daemon_type => null
714 ,p_status_change_comments => null); /* Bug# 3469326 */
715 EXCEPTION WHEN OTHERS THEN
716 l_err_num := SQLCODE;
717 l_err_msg := SUBSTR(SQLERRM, 1, 500);
718 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
719 to_char(l_booking_id)||','||l_err_msg);
720 l_return_status := 'F';
721 END;
722 /** Created for Bug 1576558 **/
723
724 ELSIF l_daemon_type = 'D' THEN
725 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Daemon type :' || l_daemon_type );
726 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing booking id : ' || l_booking_id);
727 hr_utility.set_location('Entering:'|| l_proc, 47);
728 IF l_status_id is not null THEN
729
730 BEGIN
731 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating booking id : ' || l_booking_id);
732 ota_tdb_shd.lck(l_booking_id,l_enr_ovn);
733 ota_tdb_api_upd2.Update_Enrollment(
734 p_booking_id => l_booking_id
735 ,p_booking_status_type_id => l_status_id
736 ,p_event_id => l_event_id
737 ,p_business_group_id => l_enr_business_group_id
738 ,p_object_version_number => l_enr_ovn
739 ,p_update_finance_line => 'N'
740 ,p_finance_line_id => l_new_fl
741 ,p_validate => False
742 ,p_tfl_object_version_number => l_tfl_ovn
743 ,P_Line_id => null
744 ,p_Org_id => null
745 ,p_daemon_flag => null
746 ,p_daemon_type => null
747 ,p_date_status_changed => sysdate -- Added for bug# 1708632
748 ,p_date_booking_placed => l_date_booking_placed -- Added for bug# 1708632
749 ,p_status_change_comments => null); /* Bug# 3469326 */
750
751 EXCEPTION WHEN OTHERS THEN
752 l_err_num := SQLCODE;
753 l_err_msg := SUBSTR(SQLERRM, 1, 500);
754 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
755 to_char(l_booking_id)||','||l_err_msg);
756 l_return_status := 'F';
757 END;
758 ELSE
759 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
760 to_char(l_booking_id)||','||'OTA:OM Default Enrollment Cancalled Status profile '||
761 'value has not been defined yet' );
762 l_return_status := 'F';
763
764 END IF;
765 /** End Created for Bug 1576558 **/
766
767 ELSIF l_daemon_type = 'W' THEN
768 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Daemon type :' || l_daemon_type );
769 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing booking id : ' || l_booking_id);
770
771 hr_utility.set_location('Entering:'|| l_proc, 50);
772 BEGIN
773 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating booking id : ' || l_booking_id);
774 ota_tdb_shd.lck(l_booking_id,l_enr_ovn);
775 ota_tdb_api_upd2.Update_Enrollment(
776 p_booking_id => l_booking_id
777 ,p_booking_status_type_id => l_booking_status_type_id
778 ,p_event_id => l_event_id
779 ,p_business_group_id => l_enr_business_group_id
780 ,p_object_version_number => l_enr_ovn
781 ,p_update_finance_line => 'N'
782 ,p_finance_line_id => l_new_fl
783 ,p_validate => False
784 ,p_tfl_object_version_number => l_tfl_ovn
785 ,p_daemon_flag => null
786 ,p_daemon_type => null
787 ,p_status_change_comments => null); /* Bug# 3469326 */
788 EXCEPTION WHEN OTHERS THEN
789 l_err_num := SQLCODE;
790 l_err_msg := SUBSTR(SQLERRM, 1, 100);
791 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
792 to_char(l_booking_id)||','||l_err_msg);
793 l_return_status := 'F';
794 END;
795
796 ELSIF l_daemon_type = 'E' THEN -- added for bug #1632104
797 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Daemon type :' || l_daemon_type );
798 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing booking id : ' || l_booking_id);
799 hr_utility.set_location('Entering:'|| l_proc, 55); -- err location added for bug#1632104
800 BEGIN
801 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating booking id : ' || l_booking_id);
802 ota_tdb_shd.lck(l_booking_id,l_enr_ovn);
803 ota_tdb_api_upd2.Update_Enrollment(
804 p_booking_id => l_booking_id
805 ,p_booking_status_type_id => l_booking_status_type_id
806 ,p_event_id => l_event_id
807 ,p_business_group_id => l_enr_business_group_id
808 ,p_object_version_number => l_enr_ovn
809 ,p_update_finance_line => 'N'
810 ,p_finance_line_id => l_new_fl
811 ,p_validate => False
812 ,p_tfl_object_version_number => l_tfl_ovn
813 ,p_daemon_flag => null
814 ,p_daemon_type => null
815 ,p_old_event_id => null
816 ,p_status_change_comments => null); /* Bug# 3469326 */
817 EXCEPTION WHEN OTHERS THEN
818 l_err_num := SQLCODE;
819 l_err_msg := SUBSTR(SQLERRM, 1, 100);
820 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
821 to_char(l_booking_id)||','||l_err_msg);
822 l_return_status := 'F';
823 END;
824
825 END IF;
826 IF l_return_status = 'T' then
827 IF l_auto_waitlist = 'Y' THEN
828 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto Waitlist profile value is :' || l_auto_waitlist );
829 IF l_daemon_type in ('D','C','W','E') THEN -- modified for bug #1632104
830
831 IF l_daemon_type in ('D','C','W') THEN
832 FND_FILE.PUT_LINE(FND_FILE.LOG,'Daemon type :' ||l_daemon_type );
833 OPEN c_event_date(l_event_id);
834 FETCH c_event_date into l_event_date,l_owner_id;
835 CLOSE c_event_date;
836
837 OPEN c_sysdate;
838 FETCH c_sysdate INTO l_sysdate;
839 CLOSE c_sysdate;
840
841 ELSIF l_daemon_type = 'E' THEN -- added for bug #1632104
842 FND_FILE.PUT_LINE(FND_FILE.LOG,'Daemon type :' ||l_daemon_type );
843 OPEN c_event_date(l_old_event_id);
844 FETCH c_event_date into l_event_date,l_owner_id;
845 CLOSE c_event_date;
846
847 OPEN c_sysdate;
848 FETCH c_sysdate INTO l_sysdate;
849 CLOSE c_sysdate;
850
851 END IF;
852
853 l_different_hours := l_event_date - l_sysdate ;
854 l_different_hours := l_different_hours * 24 ;
855
856 IF l_different_hours > nvl(l_cancel_hours,0) THEN
857
858 IF l_daemon_type in ('D','C','W') THEN
859 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing for Daemon Type:' ||l_daemon_type );
860 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing for Event:' ||l_event_id);
861 hr_utility.set_location('Entering:'|| l_proc, 60);
865 ,p_event_id => l_event_id
862 OTA_OM_TDB_WAITLIST_API.AUTO_ENROLL_FROM_WAITLIST
863 (p_validate => false
864 ,p_business_group_id => l_enr_business_group_id
866 ,p_return_status => l_return_status);
867
868 ELSIF l_daemon_type = 'E' THEN -- added for bug #1632104
869 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing for Daemon Type:' ||l_daemon_type );
870 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing for old Event:' ||l_old_event_id);
871 hr_utility.set_location('Entering:'|| l_proc, 60);
872 OTA_OM_TDB_WAITLIST_API.AUTO_ENROLL_FROM_WAITLIST
873 (p_validate => false
874 ,p_business_group_id => l_enr_business_group_id
875 ,p_event_id => l_old_event_id
876 ,p_return_status => l_return_status);
877
878 END IF;
879 /* ELSE
880 IF l_daemon_type = 'E'
881 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please do a manual waitlist for Event :'|| l_old_event_id );
882 ELSE
883 IF l_daemon_type in ('D','C','W') then
884 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please do a manual waitlist for Event :'|| l_event_id );
885 END IF;
886 END IF; */
887 END IF;
888
889 END IF;
890 END IF;
891 END IF;
892 IF l_return_status = 'T' THEN
893 FND_FILE.PUT_LINE(FND_FILE.LOG,'Committing for Booking_id :' ||l_booking_id);
894 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------');
895 COMMIT;
896 ELSE
897 rollback to Cancel_enrollment;
898 END IF;
899 exception
900 when others then
901 --
902 l_err_num := SQLCODE;
903 l_err_msg := SUBSTR(SQLERRM, 1, 500);
904 --
905 --
906 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
907 to_char(l_booking_id)||','||l_err_msg);
908
909 -- fnd_message.raise_error;
910 rollback to Cancel_enrollment;
911 -- A validation or unexpected error has occured
912 --
913 hr_utility.set_location(' Leaving:'||l_proc, 80);
914 END;
915 END LOOP;
916 CLOSE C_enrollment;
917 hr_utility.set_location('Leaving:'|| l_proc, 70);
918 exception
919 when e_validation_error then
920 --
921 l_err_num := SQLCODE;
922 l_err_msg := SUBSTR(SQLERRM, 1, 100);
923 --
924 --
925 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
926 to_char(l_booking_id)||','||l_err_msg);
927
928 fnd_message.raise_error;
929
930 -- A validation or unexpected error has occured
931 --
932
933 -- rollback to Cancel_enrollment;
934 hr_utility.set_location(' Leaving:'||l_proc, 80);
935
936 when others then
937 --
938 l_err_num := SQLCODE;
939 l_err_msg := SUBSTR(SQLERRM, 1, 500);
940 --
941 --
942 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
943 to_char(l_booking_id)||','||l_err_msg);
944
945 fnd_message.raise_error;
946
947 -- A validation or unexpected error has occured
948 --
949
950 --rollback to Cancel_enrollment;
951 hr_utility.set_location(' Leaving:'||l_proc, 80);
952
953 END cancel_enrollment;
954
955
956 --
957 -- ----------------------------------------------------------------------------
958 -- |------------------------------------< upd_max_attendee >------------------|
959 -- ----------------------------------------------------------------------------
960 -- {Start Of Comments}
961 --
962 -- Description:
963 -- This procedure will be used to update event maximum atenddee and will be
964 -- called by Pricing API if Pricing Attribute in OM got changed.
965 --
966 --
967 -- Pre Conditions:
968 -- None.
969 --
970 -- In Arguments:
971 -- p_line_id,
972 -- p_org_id,
973 -- p_max_attendee
974 -- p_uom
975 -- p_operation
976 --
977 -- Out Arguments:
978 -- x_return_status
979 -- x_msg_data
980 --
981 -- Post Success:
982 -- Processing continues.
983 --
984 --
985 -- Post Failure:
986 -- None.
987 --
988 -- Access Status:
989 -- Public.
990 --
991 -- {End Of Comments}
992 ----------------------------------------------------------------------------
993
994 Procedure upd_max_attendee
995 (p_line_id in number,
996 p_org_id in number,
997 p_max_attendee in number,
998 p_uom in varchar2,
999 p_operation in varchar2,
1000 x_return_status out nocopy varchar2,
1001 x_msg_data out nocopy varchar2
1002 )
1003 IS
1004
1005 l_event_id ota_events.event_id%type;
1006 l_event_business_group_id ota_events.business_group_id%type;
1010 l_booking_ovn ota_delegate_bookings.object_version_number%type;
1007 l_event_ovn ota_events.object_version_number%type;
1008 l_max_attendee ota_events.maximum_attendees%type;
1009 l_return_status varchar2(1) := 'S';
1011 l_finance_line_id ota_finance_lines.finance_line_id%type;
1012 l_tfl_ovn ota_finance_lines.object_version_number%type;
1013 l_update_finance varchar2(1);
1014 l_booking_id ota_delegate_bookings.booking_id%type;
1015 l_count number;
1016 l_proc varchar2(72) := g_package||'upd_max_attendee';
1017
1018 CURSOR C_event IS
1019 SELECT
1020 Event_ID,
1021 Business_Group_ID ,
1022 Object_Version_number,
1023 maximum_attendees
1024 FROM OTA_Events
1025 WHERE Line_Id = p_line_Id;
1026
1027
1028 CURSOR c_booking is
1029 SELECT
1030 booking_id,
1031 Object_Version_number,
1032 business_group_id,
1033 booking_status_type_id
1034 FROM OTA_Delegate_bookings
1035 WHERE event_Id = l_event_id and
1036 number_of_places = l_max_attendee;
1037
1038 CURSOR c_tfl IS
1039 Select finance_line_id,
1040 object_version_number
1041 FROM OTA_FINANCE_LINES
1042 WHERE booking_id = l_booking_id;
1043
1044
1045 BEGIN
1046 hr_utility.set_location('Entering:'|| l_proc, 5);
1047 x_return_status := 'S';
1048 IF p_uom = 'EVT' AND p_operation = 'UPDATE' THEN
1049
1050 OPEN C_event;
1051 FETCH c_event INTO l_event_id,
1052 l_event_business_group_id,
1053 l_event_ovn,
1054 l_max_attendee;
1055 IF c_event%found THEN
1056 IF l_max_attendee < p_max_attendee THEN
1057 hr_utility.set_location('Entering:'||l_proc, 10);
1058 ota_evt_upd.upd(
1059 p_Event_id => l_Event_Id
1060 ,P_Business_Group_id => l_event_Business_group_id
1061 ,P_Object_version_number => l_event_ovn
1062 ,p_maximum_attendees => p_max_attendee
1063 ,P_validate => False);
1064
1065 /* For bug 1819473 */
1066 For db in c_booking
1067 LOOP
1068 select count(*) into l_count
1069 from ota_delegate_bookings
1070 where event_id = l_event_id;
1071 if l_count = 1 then
1072 l_booking_id := db.booking_id;
1073 FOR r_tfl in c_tfl
1074 LOOP
1075 l_finance_line_id := r_tfl.finance_line_id;
1076 l_tfl_ovn := r_tfl.object_version_number;
1077
1078 END LOOP;
1079 if l_finance_line_id is not null then
1080 l_update_finance := 'Y';
1081 else
1082 l_update_finance := 'N';
1083 end if;
1084
1085 l_booking_ovn := db.object_version_number;
1086 ota_tdb_shd.lck(db.booking_id,l_booking_ovn);
1087
1088 ota_tdb_api_upd2.Update_Enrollment(
1089 p_booking_id => db.booking_id
1090 ,p_event_id => l_event_id
1091 ,p_business_group_id => db.business_group_id
1092 ,p_booking_status_type_id => db.booking_status_type_id
1093 ,p_object_version_number => l_booking_ovn
1094 ,p_update_finance_line => l_update_finance
1095 ,p_finance_line_id => l_finance_line_id
1096 ,p_number_of_places => p_max_attendee
1097 ,p_validate => False
1098 ,p_tfl_object_version_number => l_tfl_ovn
1099 ,p_status_change_comments => null); /* Bug# 3469326 */
1100
1101 end if;
1102 END LOOP;
1103 /* END bug 1819473 */
1104
1105 END IF;
1106 END IF;
1107 CLOSE c_event;
1108 hr_utility.set_location(' Leaving:'||l_proc, 20);
1109 END IF;
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 x_return_status := 'E';
1113 x_msg_data := 'OTA_13894_UPD_ERR';
1114 -- x_msg_data := SUBSTR(SQLERRM, 1, 200);
1115
1116 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 END;
1118
1119 --
1120 -- ----------------------------------------------------------------------------
1121 -- |----------------------< initial_cancel_enrollment>-------------------------|
1122 -- ----------------------------------------------------------------------------
1123 -- {Start Of Comments}
1124 --
1125 -- Description:
1126 -- This procedure will be a concurrent process which run in the background.
1127 --
1128 -- This procedure will only be used for OTA and OM integration. Basically this
1129 -- procedure will select all delegate booking data that has daemon_flag='Y' and
1130 -- Daemon_type is not nul. If the enrollment got canceled and there is a
1131 -- waitlisted student then the automatic waitlist processing will be called.
1132 --
1133 -- Pre Conditions:
1134 -- None.
1135 --
1136 -- Out Arguments:
1137 -- errbuf
1138 -- retcode
1139 --
1140 -- Post Success:
1141 -- Processing continues.
1142 --
1143 --
1144 -- Post Failure:
1145 -- None.
1146 --
1147 -- Access Status:
1151 ----------------------------------------------------------------------------
1148 -- Public.
1149 --
1150 -- {End Of Comments}
1152
1153 Procedure initial_cancel_enrollment
1154 (ERRBUF OUT NOCOPY VARCHAR2,
1155 RETCODE OUT NOCOPY VARCHAR2) as
1156
1157 p_user_id number;
1158 p_login_id number;
1159 l_completed boolean;
1160 failure exception;
1161 l_proc varchar2(72) := g_package||'initial_cancel_enrollment';
1162
1163 BEGIN
1164 p_user_id := fnd_profile.value('USER_ID');
1165 p_login_id := fnd_profile.value('LOGIN_ID');
1166
1167 ota_cancel_api.cancel_enrollment(p_user_id,
1168 p_login_id);
1169 EXCEPTION
1170 when others then
1171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1172 ||','||SUBSTR(SQLERRM, 1, 500));
1173
1174 END;
1175
1176
1177 end ota_cancel_api ;