[Home] [Help]
PACKAGE BODY: APPS.OTA_TRNG_ENROLL_SS
Source
1 PACKAGE BODY OTA_TRNG_ENROLL_SS as
2 /* $Header: ottrnenr.pkb 120.2 2008/01/08 14:06:43 aabalakr noship $ */
3
4 g_package varchar2(33) := ' ota_trng_enroll_ss.'; -- Global package name
5
6 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------<ProcessSaveEnrollment>-----------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This package is used for self service application to create enrollment
15 -- data when user enroll in the class.
16 --
17 -- Prerequisites:
18 --
19 -- In Parameters:
20 --
21 -- Post Success:
22 -- Enrollment data will be created.
23 --
24 -- Post Failure:
25 -- Status will be passed to the caller and the caller will raise a notification.
26 --
27 -- Developer Implementation Notes:
28 -- The attrbute in parameters should be modified as to the business process
29 -- requirements.
30 --
31 -- Access Status:
32 -- Internal Development Use Only.
33 --
34 -- {End Of Comments}
35 -- ----------------------------------------------------------------------------
36 PROCEDURE ProcessSaveEnrollment( p_event_id IN VARCHAR2
37 ,p_extra_information IN VARCHAR2
38 ,p_mode IN VARCHAR2
39 ,p_cost_centers IN VARCHAR2
40 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
41 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
42 ,p_business_group_name IN PER_BUSINESS_GROUPS.name%TYPE
43 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
44 -- ,p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
45 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
46 ,p_booking_id out nocopy OTA_DELEGATE_BOOKINGS.Booking_id%type
47 ,p_message_name out nocopy varchar2
48 -- ,p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
49 ,p_tdb_information_category in varchar2
50 ,p_tdb_information1 in varchar2
51 ,p_tdb_information2 in varchar2
52 ,p_tdb_information3 in varchar2
53 ,p_tdb_information4 in varchar2
54 ,p_tdb_information5 in varchar2
55 ,p_tdb_information6 in varchar2
56 ,p_tdb_information7 in varchar2
57 ,p_tdb_information8 in varchar2
58 ,p_tdb_information9 in varchar2
59 ,p_tdb_information10 in varchar2
60 ,p_tdb_information11 in varchar2
61 ,p_tdb_information12 in varchar2
62 ,p_tdb_information13 in varchar2
63 ,p_tdb_information14 in varchar2
64 ,p_tdb_information15 in varchar2
65 ,p_tdb_information16 in varchar2
66 ,p_tdb_information17 in varchar2
67 ,p_tdb_information18 in varchar2
68 ,p_tdb_information19 in varchar2
69 ,p_tdb_information20 in varchar2 )
70 IS
71
72 CURSOR bg_to (pp_event_id ota_events.event_id%TYPE) IS
73 SELECT hao.business_group_id,
74 evt.organization_id,
75 evt.currency_code,
76 evt.course_start_date,
77 evt.course_end_date,
78 evt.Title,
79 evt.owner_id,
80 evt.activity_version_id,
81 evt.offering_id
82 FROM OTA_EVENTS_VL evt,
83 HR_ALL_ORGANIZATION_UNITS hao
84 WHERE evt.event_id = pp_event_id
85 AND evt.organization_id = hao.organization_id (+); -- Bug 2213009
86
87
88 Cursor Get_Event_status is
89 Select event_status, maximum_internal_attendees
90 from OTA_EVENTS
91 WHERE EVENT_ID = TO_NUMBER(p_event_id);
92
93 CURSOR get_existing_internal IS
94 SELECT count(*)
95 FROM OTA_DELEGATE_BOOKINGS dbt,
96 OTA_BOOKING_STATUS_TYPES bst
97 WHERE dbt.event_id = TO_NUMBER(p_event_id)
98 AND dbt.internal_booking_flag = 'Y'
99 AND dbt.booking_status_type_id = bst.booking_status_type_id
100 AND bst.type in ('P','A','E');
101
102
103 CURSOR c_get_price_basis is
104 SELECT nvl(price_basis,NULL)
105 FROM ota_events
106 where event_id = p_event_id;
107
108 CURSOR csr_user(p_owner_id in number) IS
109 SELECT
110 USER_NAME
111 FROM
112 FND_USER
113 WHERE
114 Employee_id = p_owner_id ;
115
116 CURSOR csr_activity(p_activity_version_id number )
117 IS
118 SELECT version_name
119 FROM OTA_ACTIVITY_VERSIONS_TL
120 WHERE activity_version_id = p_activity_version_id
121 AND language=userenv('LANG');
122
123 l_price_basis OTA_EVENTS.price_basis%TYPE;
124
125 l_person_details OTA_TRNG_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
126 --
127 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
128 l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
129 l_object_version_number BINARY_INTEGER;
130 l_tfl_ovn BINARY_INTEGER;
131 l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
132 l_booking_type VARCHAR2(4000);
133 l_error_crypt VARCHAR2(4000);
134 --
135 l_mode VARCHAR2(200);
136 l_delegate_id PER_PEOPLE_F.person_id%TYPE;
137 l_restricted_assignment_id PER_ASSIGNMENTS_F.assignment_id%type;
138 l_cancel_boolean BOOLEAN;
139 -- -------------------
140 -- Finance API Vars
141 -- -------------------
142 l_auto_create_finance VARCHAR2(40);
143 fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
144 fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
145 fapi_result VARCHAR2(40);
146 fapi_from VARCHAR2(5);
147 fapi_to VARCHAR2(5);
148
149 result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
150 result_create_finance_line VARCHAR2(5) := 'Y';
151 result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
152
153 l_logged_in_user NUMBER;
154 l_user NUMBER;
155 l_automatic_transfer_gl VARCHAR2(40);
156 l_notification_text VARCHAR2(1000);
157 l_cost_allocation_keyflex_id VARCHAR2(1000);
158
159 l_event_status varchar2(30);
160
161 l_maximum_internal_attendees NUMBER;
162 l_existing_internal NUMBER;
163 l_maximum_internal_allowed NUMBER;
164
165 -- l_item_key wf_items.item_key%type;
166 l_called_from varchar2(80);
167 l_business_group_id_to hr_all_organization_units.organization_id%type;
168 l_sponsor_organization_id hr_all_organization_units.organization_id%type;
169 l_event_currency_code ota_events.currency_code%type;
170 l_event_title ota_events.title%type;
171 l_course_start_date ota_events.course_start_date%type;
172 l_course_end_date ota_events.course_end_date%type;
173 l_owner_id ota_events.owner_id%type;
174 l_activity_version_id ota_activity_versions.activity_version_id%type;
175 l_version_name ota_activity_versions.version_name%type;
176 l_owner_username fnd_user.user_name%type;
177 --Bug#2197997 commenting out completeactivity call.
178 -- l_return boolean;
179 l_offering_id ota_events.offering_id%type;
180 l_booking_status_used varchar2(20);
181
182 -- l_approval_req_flag Varchar2(20);
183
184 BEGIN
185 /*
186 fnd_global.APPS_INITIALIZE(
187 user_id =>1549,
188 resp_id =>50677,
189 resp_appl_id => 810);
190 */
191
192 HR_UTIL_MISC_WEB.VALIDATE_SESSION(p_person_id => l_logged_in_user);
193
194 -- ----------------------------------------------------------------------
195 -- RETRIEVE THE DATA REQUIRED
196 -- ----------------------------------------------------------------------
197
198 BEGIN
199
200
201 -- l_item_key := p_item_key;
202
203 l_delegate_id := p_person_id;
204
205 l_restricted_assignment_id := CHK_DELEGATE_OK_FOR_EVENT(p_delegate_id => l_delegate_id
206 ,p_event_id => p_event_id);
207
208
209 l_person_details := Get_Person_To_Enroll_Details(p_person_id => l_delegate_id);
210
211
212 /* Set Workflow Attribute */
213
214 /* IF l_person_details.full_name is not null then
215 WF_ENGINE.setitemattrtext(p_item_type,
216 l_item_key,
217 'CURRENT_PERSON_DISPLAY_NAME',
218 l_person_details.full_name);
219 END IF;*/
220
221 IF l_restricted_assignment_id IS NULL OR
222 l_restricted_assignment_id = '-1' THEN
223 NULL;
224 ELSE
225 l_person_details.assignment_id := l_restricted_assignment_id;
226 END IF;
227
228 -- -----------------------------------------------
229 -- Open BG Cursor to get the Business Group TO
230 -- -----------------------------------------------
231 OPEN bg_to(p_event_id);
232 FETCH bg_to INTO l_business_group_id_to,
233 l_sponsor_organization_id,
234 l_event_currency_code,
235 l_course_start_date,
236 l_course_end_date,
237 l_event_title,
238 l_owner_id,
239 l_activity_version_id,
240 l_offering_id;
241 CLOSE bg_to;
242
243
244 For act in csr_activity(l_activity_version_id)
245 Loop
246 l_version_name := act.version_name;
247 End Loop;
248
249 if l_owner_id is not null then
250 For owner in csr_user(l_owner_id)
251 Loop
252 l_owner_username := owner.user_name;
253 End Loop;
254 end if;
255
256 /*
257 WF_ENGINE.setitemattrtext(p_item_type,
258 l_item_key,
259 'OTA_ACTIVITY_VERSION_NAME',
260 l_version_name);
261
262
263 WF_ENGINE.setitemattrtext(p_item_type,
264 l_item_key,
265 'EVENT_OWNER_EMAIL',
266 l_owner_username);
267
268
269 WF_ENGINE.setitemattrtext(p_item_type,
270 l_item_key,
271 'OTA_EVENT_TITLE',
272 l_event_title);
273
274 WF_ENGINE.setitemattrtext(p_item_type,
275 l_item_key,
276 'OTA_COURSE_START_DATE',
277 l_course_start_date);
278
279 WF_ENGINE.setitemattrtext(p_item_type,
280 l_item_key,
281 'OTA_COURSE_END_DATE',
282 l_course_end_date);
283
284 WF_ENGINE.setitemattrnumber(p_item_type,
285 l_item_key,
286 'TRANSACTION_ID',
287 hr_transaction_web.get_transaction_id
288 (p_item_type => p_item_type
289 ,p_item_key => l_item_key));
290
291 WF_ENGINE.setitemattrnumber(p_item_type,
292 l_item_key,
293 'FORWARD_FROM_PERSON_ID',
294 p_person_id);
295
296
297 wf_engine.setItemAttrNumber(itemtype => p_item_type
298 ,itemkey => l_item_key
299 ,aname => 'EVENT_ID'
300 ,avalue => p_event_id);
301
302 */
303
304 BEGIN /* Check Booking Type */
305
306 -- ---------------------------------
307 -- Find Which booking status to use
308 -- ---------------------------------
309 -- Find out whether the person should be enrolled on the event directly
310 -- or if they can just request to be enrolled.
311 --
312 -- The booking_type returned will be one of four possible values :
313 -- Name Enrollment Status
314 -- MGR_APR_NO_ADMIN = Requested
315 -- MGR_APR_WITH_ADMIN = Requested
316 -- SELF_BOOKING = Attempt to enroll them
317 -- TRAINING_ADMIN = Requested
318 --
319 /* l_booking_type := wf_engine.GetItemAttrText(itemtype => p_item_type
320 ,itemkey => l_item_key
321 ,aname => 'ENROLL_IN_CLASS_APPROVAL_MODE'); */
322
323 /* l_approval_req_flag := wf_engine.GetItemAttrText(itemtype => p_item_type
324 ,itemkey => l_item_key
325 ,aname => 'HR_APPROVAL_REQ_FLAG'); */
326
327 null;
328 EXCEPTION
329 WHEN OTHERS THEN
330
331 fnd_message.set_name ('OTA','OTA_13658_WF_ERR_GETTING_TYPE');
332 RAISE OTA_TRNG_ENROLL_SS.g_mesg_on_stack_exception ;
333 p_message_name := SUBSTR(SQLERRM, 1,300);
334 END; /* End Check booking Type */
335
336
337 -- IF (l_booking_type <> 'SELF_BOOKING') THEN
338 -- IF (l_approval_req_flag <> 'NO') THEN
339 --
340 -- The enrollment will have to be saved with a status of Requested,
341 -- so get the ID for the seeded status.
342 --
343 -- l_booking_status_row := Get_Booking_Status_for_web(
344 -- p_web_booking_status_type => 'REQUESTED'
345 -- ,p_business_group_id => ota_general.get_business_group_id);
346
347 -- ELSE
348
349 -- The enrollment doesn't need mangerial approval so check the mode
350 -- to find out whether they can only be waitlisted and then get the
351 -- default booking status for either waitlisted or placed.
352
353 OPEN get_event_status;
354 FETCH get_event_status into l_event_status, l_maximum_internal_attendees;
355 CLOSE get_event_status;
356
357 OPEN get_existing_internal;
358 FETCH get_existing_internal into l_existing_internal;
359 CLOSE get_existing_internal;
360
361 l_maximum_internal_allowed := nvl(l_maximum_internal_attendees,0) - l_existing_internal;
362
363 IF l_event_status in ('F') THEN
364
365 l_booking_status_row := Get_Booking_Status_for_web
366 (p_web_booking_status_type => 'WAITLISTED'
367 ,p_business_group_id => ota_general.get_business_group_id);
368
369 l_booking_status_used := 'WAITLISTED';
370
371 ELSIF l_event_status in ('P') THEN
372
373 l_booking_status_row := Get_Booking_Status_for_web
374 (p_web_booking_status_type => 'REQUESTED'
375 ,p_business_group_id => ota_general.get_business_group_id);
376
377 l_booking_status_used := 'REQUESTED';
378
379 ELSIF l_event_status = 'N' THEN
380
381 IF l_maximum_internal_attendees is null then
382 l_booking_status_row := Get_Booking_Status_for_web
383 (p_web_booking_status_type => 'PLACED'
384 ,p_business_group_id => ota_general.get_business_group_id);
385
386 l_booking_status_used := 'PLACED';
387
388 ELSE
389
390 IF l_maximum_internal_allowed > 0 THEN
391 l_booking_status_row := Get_Booking_Status_for_web
392 (p_web_booking_status_type => 'PLACED'
393 ,p_business_group_id => ota_general.get_business_group_id);
394
395 l_booking_status_used := 'PLACED';
396
397 ELSIF l_maximum_internal_allowed <= 0 THEN
398 l_booking_status_row := Get_Booking_Status_for_web
399 (p_web_booking_status_type => 'WAITLISTED'
400 ,p_business_group_id => ota_general.get_business_group_id);
401
402 l_booking_status_used := 'WAITLISTED';
403
404 END IF;
405 END IF;
406 -- END IF;
407 IF l_booking_status_row.booking_Status_type_id is null then
408 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
409 --Bug#4617150
410 -- RAISE OTA_ENROLL_CLASS_UTILITY_WEB.g_mesg_on_stack_exception ;
411 RAISE g_mesg_on_stack_exception ;
412 ELSE
413 /* WF_ENGINE.setitemattrtext(p_item_type,
414 l_item_key,
415 'ENROLL_IN_A_CLASS_STATUS',
416 l_booking_status_row.name);
417
418 */
419 Null;
420 END IF ;
421 END IF;
422 /* WF_ENGINE.setitemattrtext(p_item_type,
423 l_item_key,
424 'ENROLL_IN_A_CLASS_STATUS',
425 l_booking_status_row.name);
426 */
427 -- END IF;
428
429 EXCEPTION
430 --Bug#4617150
431 -- WHEN OTA_ENROLL_CLASS_UTILITY_WEB.g_mesg_on_stack_exception THEN
432 WHEN g_mesg_on_stack_exception THEN
433 --
434 -- Store the technical message which will have been seeded
435 -- if this exception has been raised. This will be used to provide
436 -- the code.
437 --
438 hr_message.provide_error;
439 --
440 -- Now distinguish which error was raised.
441 --
442 IF (hr_message.last_message_name = 'OTA_13667_WEB_STATUS_NOT_SEEDE') THEN
443 --
444 -- Seed the user friendly message
445 --
446 fnd_message.set_name ('OTA','OTA_WEB_INCORRECT_CONF');
447 --
448 -- Raise the error for the main procedure exception handler
449 -- to handle
450 --
451 p_message_name := hr_message.last_message_name;
452 p_message_name := SUBSTR(SQLERRM, 1,300);
453 --
454 ELSIF (hr_message.last_message_name = 'HR_51396_WEB_PERSON_NOT_FND')
455 THEN
456 --
457 -- Seed the user friendly message
458 --
459 fnd_message.set_name ('OTA','OTA_NO_DELEGATE_INFORMATION');
460 --
461 -- Raise the error for the main procedure exception handler
462 -- to handle
463 p_message_name := 'OTA_NO_DELEGATE_INFORMATION';
464 p_message_name := SUBSTR(SQLERRM, 1,300);
465 --
466 /* ELSIF (hr_message.last_message_name = 'OTA_13658_WF_ERR_GETTING_TYPE')
467 THEN
468 --
469 -- Seed the user friendly message
470 --
471 fnd_message.set_name ('OTA','OTA_WEB_WF_PROBLEM');
472 -- Raise the error for the main procedure exception handler
473 -- to handle
474 --
475 p_message_name := fnd_message.get;
476 p_message_name := SUBSTR(SQLERRM, 1,300);*/
477 --
478 ELSE
479 -- Raise the error for the main procedure exception handler
480 -- to handle
481 p_message_name := hr_message.get_message_text;
482
483 --
484 END IF;
485 --
486 WHEN OTHERS THEN
487 --
488 -- Can't store a technical message, as we don't know what it is
489 -- and a message may not have been put on the stack
490 --
491 hr_message.provide_error;
492 --
493 -- Seed the user friendly message
494 --
495 fnd_message.set_name ('OTA','OTA_WEB_ERR_GETTING_INFO');
496 --
497 --
498 -- Raise the error for the main procedure exception handler
499 -- to handle
500 -- p_message_name := 'OTA_WEB_ERR_GETTING_INFO';
501
502 p_message_name := SUBSTR(SQLERRM, 1,300);
503
504 END ;
505 --
506 -- ----------------------------------------------------------------------
507 -- Save
508 -- ----------------------------------------------------------------------
509 -- If there are no errors, save to the database
510 -- (there shouldn't be as the main exception handler will be used
511 --
512 -- IF NOT hr_errors_api.errorExists THEN
513 IF p_message_name is null then
514
515
516 BEGIN
517 --
518 -- Check to see if delegate has a booking status of CANCELLED for
519 -- this event, if cancelled l_cancel_boolean is set to true
520 -- FIX for bug 900679
521 --
522 l_cancel_boolean := Chk_Event_Cancelled_for_Person(p_event_id => p_event_id
523 ,p_delegate_person_id => l_delegate_id
524 ,p_booking_id => l_booking_id);
525
526 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
527 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
528 l_user := FND_PROFILE.value('USER_ID');
529
530 -- --------------------------------------------
531 -- Dynamic Notification Text for Workflow
532 -- --------------------------------------------
533 -- l_notification_text := 'The Cross Charge details have been successfully obtained for the Enrollment record. ';
534
535 -- l_notification_text := ' The appropriate cost center has been charged.';
536 l_notification_text := ' The cost center will be charged if appropriate.';
537
538 IF (l_cancel_boolean) THEN
539 --
540 -- Delegate has a Cancelled status for this event, hence
541 -- we must update the existing record by changing Cancelled
542 -- to Requested status
543 --
544 /* wf_engine.setItemAttrText (itemtype => p_item_type
545 ,itemkey => l_item_key
546 ,aname => 'BOOKING_STATUS_TYPE_ID'
547 ,avalue => l_booking_status_row.booking_status_type_id); */
548
549 l_object_version_number := OTA_TRNG_ENROLL_SS.Get_Booking_OVN (p_booking_id => l_booking_id);
550
551 /* Call Cancel procedure to cancel the Finance if person Re-enroll */
552 cancel_finance(l_booking_id);
553
554
555 -- ----------------------------------------------------------------
556 -- Delegate has no record for this event, hence create a record
557 -- with requested status
558 -- ----------------------------------------------------------------
559 -- Check if the Profile AutoCreate Finance is ON or OFF
560 -- ----------------------------------------------------------------
561 END IF;
562 -- ELSE
563 open c_get_price_basis;
564 fetch c_get_price_basis into l_price_basis;
565 close c_get_price_basis;
566
567
568 -- l_approval_req_flag = 'NO' and included (Create finance only if self-approval is yes)
569
570 IF l_auto_create_finance = 'Y' and
571 l_price_basis <> 'N' and
572 -- l_approval_req_flag = 'NO' and
573 l_event_currency_code is not null THEN
574
575 l_cost_allocation_keyflex_id := TO_NUMBER(p_cost_centers);
576 result_finance_header_id := fapi_finance_header_id;
577 result_object_version_number := l_object_version_number;
578
579 ota_crt_finance_segment.Create_Segment(
580 p_assignment_id => p_assignment_id,
581 p_business_group_id_from => p_business_group_id_from,
582 p_business_group_id_to => l_business_group_id_to,
583 p_organization_id => p_organization_id,
584 p_sponsor_organization_id => l_sponsor_organization_id,
585 p_event_id => p_event_id,
586 p_person_id => l_delegate_id,
587 p_currency_code => l_event_currency_code,
588 p_cost_allocation_keyflex_id=> l_cost_allocation_keyflex_id,
589 p_user_id => l_user,
590 p_finance_header_id => fapi_finance_header_id,
591 p_object_version_number => fapi_object_version_number,
592 p_result => fapi_result,
593 p_from_result => fapi_from,
594 p_to_result => fapi_to );
595
596 IF fapi_result = 'S' THEN
597
598 /* wf_engine.setItemAttrText (itemtype => p_item_type
599 ,itemkey => l_item_key
600 ,aname => 'API_RESULT'
601 ,avalue => fapi_result);
602
603 wf_engine.setItemAttrText (itemtype => p_item_type
604 ,itemkey => l_item_key
605 ,aname => 'API_FROM'
606 ,avalue => fapi_from);
607
608 wf_engine.setItemAttrText (itemtype => p_item_type
609 ,itemkey => l_item_key
610 ,aname => 'API_TO'
611 ,avalue => fapi_to);
612
613 wf_engine.setItemAttrNumber(itemtype => p_item_type
614 ,itemkey => l_item_key
615 ,aname => 'EVENT_ID'
616 ,avalue => p_event_id);
617
618 wf_engine.setItemAttrText (itemtype => p_item_type
619 ,itemkey => l_item_key
620 ,aname => 'BUSINESS_GROUP_NAME'
621 ,avalue => p_business_group_name);
622
623 wf_engine.setItemAttrText (itemtype => p_item_type
624 ,itemkey => l_item_key
625 ,aname => 'NOTIFICATION_TEXT'
626 ,avalue => l_notification_text);*/
627
628 result_object_version_number := fapi_object_version_number;
629 result_finance_header_id := fapi_finance_header_id;
630
631 ELSIF fapi_result = 'E' THEN
632
633 l_notification_text := NULL;
634
635 /* wf_engine.setItemAttrText (itemtype => p_item_type
636 ,itemkey => l_item_key
637 ,aname => 'API_RESULT'
638 ,avalue => fapi_result);
639
640 wf_engine.setItemAttrText (itemtype => p_item_type
641 ,itemkey => l_item_key
642 ,aname => 'API_FROM'
643 ,avalue => fapi_from);
644
645 wf_engine.setItemAttrText (itemtype => p_item_type
646 ,itemkey => l_item_key
647 ,aname => 'API_TO'
648 ,avalue => fapi_to);
649
650 wf_engine.setItemAttrNumber(itemtype => p_item_type
651 ,itemkey => l_item_key
652 ,aname => 'EVENT_ID'
653 ,avalue => p_event_id);
654
655 wf_engine.setItemAttrText (itemtype => p_item_type
656 ,itemkey => l_item_key
657 ,aname => 'BUSINESS_GROUP_NAME'
658 ,avalue => p_business_group_name);
659
660 wf_engine.setItemAttrText (itemtype => p_item_type
661 ,itemkey => l_item_key
662 ,aname => 'NOTIFICATION_TEXT'
663 ,avalue => l_notification_text);*/
664
665 result_object_version_number := l_object_version_number;
666 result_finance_header_id := NULL;
667 result_create_finance_line := NULL;
668 END IF;
669
670 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
671 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
672 ,p_delegate_person_id => l_delegate_id
673 ,p_contact_id => null
674 ,p_business_group_id => ota_general.get_business_group_id
675 ,p_event_id => p_event_id
676 ,p_date_booking_placed => trunc(sysdate)
677 ,p_corespondent => 'S' --l_corespondent
678 ,p_internal_booking_flag => 'Y'
679 ,p_person_address_type => 'I'
680 ,p_number_of_places => 1
681 ,p_object_version_number => result_object_version_number
682 ,p_delegate_contact_phone => l_person_details.work_telephone
683 ,p_source_of_booking => 'E'
684 ,p_special_booking_instructions => p_extra_information
685 ,p_successful_attendance_flag => 'N'
686 ,p_finance_header_id => result_finance_header_id
687 ,p_create_finance_line => result_create_finance_line
688 ,p_finance_line_id => l_finance_line_id
689 ,p_enrollment_type => 'S'
690 ,p_validate => FALSE
691 ,p_currency_code => l_event_currency_code
692 ,p_organization_id => l_person_details.organization_id
693 ,p_delegate_assignment_id => l_person_details.assignment_id
694 ,p_delegate_contact_email => l_person_details.email_address
695 ,p_tdb_information_category => p_tdb_information_category
696 ,p_tdb_information1 => p_tdb_information1
697 ,p_tdb_information2 => p_tdb_information2
698 ,p_tdb_information3 => p_tdb_information3
699 ,p_tdb_information4 => p_tdb_information4
700 ,p_tdb_information5 => p_tdb_information5
701 ,p_tdb_information6 => p_tdb_information6
702 ,p_tdb_information7 => p_tdb_information7
703 ,p_tdb_information8 => p_tdb_information8
704 ,p_tdb_information9 => p_tdb_information9
705 ,p_tdb_information10 => p_tdb_information10
706 ,p_tdb_information11 => p_tdb_information11
707 ,p_tdb_information12 => p_tdb_information12
708 ,p_tdb_information13 => p_tdb_information13
709 ,p_tdb_information14 => p_tdb_information14
710 ,p_tdb_information15 => p_tdb_information15
711 ,p_tdb_information16 => p_tdb_information16
712 ,p_tdb_information17 => p_tdb_information17
713 ,p_tdb_information18 => p_tdb_information18
714 ,p_tdb_information19 => p_tdb_information19
715 ,p_tdb_information20 => p_tdb_information20);
716
717
718 IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
719
720 UPDATE ota_finance_lines SET transfer_status = 'AT'
721 WHERE finance_line_id = l_finance_line_id;
722
723
724 END IF;
725
726 /* wf_engine.setItemAttrText (itemtype => p_item_type
727 ,itemkey => l_item_key
728 ,aname => 'BOOKING_STATUS_TYPE_ID'
729 ,avalue => l_booking_status_row.booking_status_type_id);
730 */
731 ELSE
732
733 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
734 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
735 ,p_delegate_person_id => l_delegate_id
736 ,p_contact_id => null
737 ,p_business_group_id => ota_general.get_business_group_id
738 ,p_event_id => p_event_id
739 ,p_date_booking_placed => trunc(sysdate)
740 ,p_corespondent => 'S' --l_corespondent
741 ,p_internal_booking_flag => 'Y'
742 ,p_person_address_type => 'I'
743 ,p_number_of_places => 1
744 ,p_object_version_number => l_object_version_number
745 ,p_delegate_contact_phone => l_person_details.work_telephone
746 ,p_source_of_booking => 'E'
747 ,p_special_booking_instructions => p_extra_information
748 ,p_successful_attendance_flag => 'N'
749 ,p_finance_line_id => l_finance_line_id
750 ,p_enrollment_type => 'S'
751 ,p_validate => FALSE
752 ,p_organization_id => l_person_details.organization_id
753 ,p_delegate_assignment_id => l_person_details.assignment_id
754 ,p_delegate_contact_email => l_person_details.email_address
755 ,p_tdb_information_category => p_tdb_information_category
756 ,p_tdb_information1 => p_tdb_information1
757 ,p_tdb_information2 => p_tdb_information2
758 ,p_tdb_information3 => p_tdb_information3
759 ,p_tdb_information4 => p_tdb_information4
760 ,p_tdb_information5 => p_tdb_information5
761 ,p_tdb_information6 => p_tdb_information6
762 ,p_tdb_information7 => p_tdb_information7
763 ,p_tdb_information8 => p_tdb_information8
764 ,p_tdb_information9 => p_tdb_information9
765 ,p_tdb_information10 => p_tdb_information10
766 ,p_tdb_information11 => p_tdb_information11
767 ,p_tdb_information12 => p_tdb_information12
768 ,p_tdb_information13 => p_tdb_information13
769 ,p_tdb_information14 => p_tdb_information14
770 ,p_tdb_information15 => p_tdb_information15
771 ,p_tdb_information16 => p_tdb_information16
772 ,p_tdb_information17 => p_tdb_information17
773 ,p_tdb_information18 => p_tdb_information18
774 ,p_tdb_information19 => p_tdb_information19
775 ,p_tdb_information20 => p_tdb_information20);
776
777
778 /*wf_engine.setItemAttrText (itemtype => p_item_type
779 ,itemkey => l_item_key
780 ,aname => 'BOOKING_STATUS_TYPE_ID'
781 ,avalue => l_booking_status_row.booking_status_type_id);
782 */
783 END IF;
784
785
786 p_booking_id := l_booking_id;
787
788 /* WF_ENGINE.setitemattrtext(p_item_type,
789 l_item_key,
790 'BOOKING_ID',
791 l_booking_id);
792 */
793 IF l_booking_id is not null then
794
795 /* Bug#2197997 Commenting out Completeactivity call.
796 l_return := check_wf_status(l_item_key,'BLOCK',p_item_type);
797 IF l_return = TRUE THEN
798 wf_engine.Completeactivity(p_item_type,l_item_key,'BLOCK',null);
799 END IF;
800 */
801
802 IF l_booking_status_used = 'PLACED' then
803 p_message_name := 'OTA_SS_CONFIRMED_PLACED';
804 ELSIF l_booking_status_used = 'WAITLISTED' then
805 p_message_name := 'OTA_SS_CONFIRMED_WAITLISTED';
806 ELSIF l_booking_status_used = 'REQUESTED' then
807 p_message_name := 'OTA_SS_CONFIRMED_REQUESTED';
808 END IF;
809
810
811
812 END IF;
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 -- Both the Confirm Procedure and the API return APP-20002 or -20001
817 -- so provide error can be used, as if the confirm procedure errors
818 -- a different tool bar will be used.
819 -- If the API has errored, the WF won't have been activated
820 -- whereas if the confirm procedure errored, then it probably will have
821 -- been.
822 -- p_mode will be changed to indicate an error and,if it's a WF error
823 -- the mode will also indicate this.
824 -- Then the "Confirmation" page will be called from the main handler.
825 --
826 -- It is OK to use hr_message.provide_error as an application
827 -- error will have been raised which will have put an error onto
828 -- the stack
829 --
830 /* IF (hr_message.last_message_name = 'OTA_13668_WEB_NO_TRANMISSION')
831 THEN
832 --
833 -- The WF may have already be transissioned, so change the mode.
834 l_mode := l_mode || 'BADWF';
835 --
836
837 END IF; */
838 p_message_name := fnd_message.get;
839 -- p_message_name := SUBSTR(SQLERRM, 1,300);
840 --
841 END; -- End of if p_message is not null
842
843 END IF;
844 EXCEPTION
845 WHEN OTHERS THEN
846 p_message_name := SUBSTR(SQLERRM, 1,300);
847 END ProcessSaveEnrollment;
848
849 -- -----------------------------------------------------
850 -- Procedure cancel_finance
851 -- -----------------------------------------------------
852 PROCEDURE cancel_finance(p_booking_id in number)
853 IS
854
855 -- ------------------------
856 -- Finance_cur Variables
857 -- ------------------------
858 l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
859 l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
860 l_transfer_status ota_finance_lines.transfer_status%TYPE;
861 lf_booking_id ota_finance_lines.booking_id%TYPE;
862 lf_object_version_number ota_finance_lines.object_version_number%TYPE;
863 l_sequence_number ota_finance_lines.sequence_number%TYPE;
864 l_raised_date date;
865 l_finance_count number(10);
866 l_cancelled_flag ota_finance_lines.cancelled_flag%type;
867 l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
868 -- ------------------------
869 -- header_cur Variables
870 -- ------------------------
871 lh_finance_header_id ota_finance_headers.finance_header_id%TYPE;
872 lh_cancelled_flag ota_finance_headers.cancelled_flag%TYPE;
873 lh_transfer_status ota_finance_headers.transfer_status%TYPE;
874 lh_object_version_number ota_finance_headers.object_version_number%TYPE;
875
876 CURSOR finance (p_booking_id ota_finance_lines.booking_id%TYPE) IS
877 SELECT FLN.finance_line_id finance_line_id,
878 FLN.finance_header_id finance_header_id,
879 FLN.transfer_status transfer_status,
880 FLN.booking_id booking_id,
881 FLN.object_version_number object_version_number,
882 FLN.sequence_number sequence_number,
883 FLN.Cancelled_flag cancelled_flag
884 FROM OTA_FINANCE_LINES FLN
885 WHERE FLN.booking_id = p_booking_id;
886
887 CURSOR finance_count (p_finance_header_id ota_finance_lines.finance_header_id%TYPE) IS
888 SELECT count(*)
889 FROM OTA_FINANCE_LINES FLN
890 WHERE FLN.finance_header_id = p_finance_header_id;
891
892 CURSOR header (p_booking_id ota_finance_lines.booking_id%TYPE) IS
893 SELECT FLH.finance_header_id finance_header_id,
894 FLH.cancelled_flag cancelled_flag,
895 FLH.transfer_status transfer_status,
896 FLH.object_version_number object_version_number
897 FROM OTA_FINANCE_HEADERS FLH,
898 OTA_FINANCE_LINES FLN
899 WHERE FLH.finance_header_id = FLN.finance_header_id
900 AND FLN.booking_id = p_booking_id;
901
902 BEGIN
903
904 OPEN finance (p_booking_id);
905 FETCH finance INTO l_finance_line_id,
906 l_finance_header_id,
907 l_transfer_status,
908 lf_booking_id,
909 lf_object_version_number,
910 l_sequence_number,
911 l_cancelled_flag ;
912
913 IF finance%found THEN
914 -- IF l_finance_line_id is not null THEN
915
916
917 IF l_transfer_status = 'ST' or l_cancelled_flag = 'Y' THEN
918 NULL;
919 ELSE
920
921 -- ----------------------------------------------
922 -- Call Finance Lines API (Cancel Finance Line)
923 -- ----------------------------------------------
924
925 -- select sysdate into l_raised_date from dual; -- Select Date from the System for p_date_raised
926 -- END IF;
927
928 OPEN finance_count (l_finance_header_id);
929 FETCH finance_count INTO l_finance_count;
930 CLOSE finance_count;
931
932 IF l_finance_count = 1 THEN
933
934 -- ---------------------------
935 -- If only one Finance Line
936 -- ---------------------------
937
938 OPEN header (p_booking_id);
939 FETCH header INTO lh_finance_header_id,
940 lh_cancelled_flag,
941 lh_transfer_status,
942 lh_object_version_number;
943
944 IF lh_transfer_status <> 'ST' or lh_cancelled_flag <>'Y' THEN
945 -- -------------------------------------------------
946 -- Call Finance Header API (Cancel Finance Header)
947 -- -------------------------------------------------
948 l_raised_date := sysdate;
949 ota_tfh_api_business_rules.cancel_header(p_finance_header_id => lh_finance_header_id
950 ,p_cancel_header_id => l_cancel_header_id
951 ,p_date_raised => l_raised_date
952 ,p_validate => false
953 ,p_commit => false);
954 END IF;
955
956 ELSE
957 l_raised_date := sysdate;
958 ota_tfl_api_upd.upd(p_finance_line_id => l_finance_line_id
959 ,p_date_raised => l_raised_date
960 ,p_cancelled_flag => 'Y'
961 ,p_object_version_number => lf_object_version_number
962 ,p_sequence_number => l_sequence_number
963 ,p_validate => false
964 ,p_transaction_type => 'CANCEL_HEADER_LINE');
965
966 END IF;
967 CLOSE header;
968 END IF; -- For Lines;
969
970 -- else
971 -- ---------------------------------------
972 -- Call the API to Cancel the Enrollment
973 -- ---------------------------------------
974
975 END IF;
976 CLOSE finance;
977
978 END cancel_finance;
979
980 --
981 --
982 -- ----------------------------------------------------------------------------
983 -- |----------------------<Get_Booking_Status_For_Web >-----------------------|
984 -- ----------------------------------------------------------------------------
985 -- {Start Of Comments}
986 --
987 -- Description:
988 -- This package is used for self service application to create enrollment
989 -- data when user enroll in the class.
990 --
991 -- Prerequisites:
992 --
993 -- In Parameters:
994 --
995 -- Post Success:
996 -- Enrollment data will be created.
997 --
998 -- Post Failure:
999 -- Status will be passed to the caller and the caller will raise a notification.
1000 --
1001 -- Developer Implementation Notes:
1002 -- The attrbute in parameters should be modified as to the business process
1003 -- requirements.
1004 --
1005 -- Access Status:
1006 -- Internal Development Use Only.
1007 --
1008 -- {End Of Comments}
1009 -- ----------------------------------------------------------------------------
1010
1011
1012 FUNCTION Get_Booking_Status_For_Web (p_web_booking_status_type VARCHAR2
1013 ,p_business_group_id NUMBER)
1014 RETURN OTA_BOOKING_STATUS_TYPES%ROWTYPE
1015
1016 IS
1017
1018 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE DEFAULT NULL;
1019
1020
1021 BEGIN
1022
1023 OPEN csr_booking_status_id (p_business_group_id => p_business_group_id
1024 ,p_web_booking_status_type => p_web_booking_status_type);
1025 FETCH csr_booking_status_id INTO l_booking_status_row;
1026
1027 IF ( csr_booking_status_id%NOTFOUND ) THEN
1028 --
1029 CLOSE csr_booking_status_id;
1030 --
1031 -- Seed a technical message so that if the calling procedure decides
1032 -- that it is an error having nothing returned, it can use it.
1033 --
1034 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
1035 Fnd_message.raise_error;
1036 --
1037 RETURN l_booking_status_row;
1038 --
1039 ELSE
1040 --
1041 CLOSE csr_booking_status_id;
1042 --
1043 RETURN l_booking_status_row;
1044 --
1045 END IF;
1046 RETURN l_booking_status_row;
1047 END Get_Booking_Status_For_Web;
1048 --
1049
1050 --
1051 -- ----------------------------------------------------------------------------
1052 -- |-----------------------------<Check_Cost_Center>--------------------------|
1053 -- ----------------------------------------------------------------------------
1054 -- {Start Of Comments}
1055 --
1056 -- Description:
1057 -- This package is used for self service application to create enrollment
1058 -- data when user enroll in the class.
1059 --
1060 -- Prerequisites:
1061 --
1062 -- In Parameters:
1063 --
1064 -- Post Success:
1065 -- Enrollment data will be created.
1066 --
1067 -- Post Failure:
1068 -- Status will be passed to the caller and the caller will raise a notification.
1069 --
1070 -- Developer Implementation Notes:
1071 -- The attrbute in parameters should be modified as to the business process
1072 -- requirements.
1073 --
1074 -- Access Status:
1075 -- Internal Development Use Only.
1076 --
1077 -- {End Of Comments}
1078 -- ----------------------------------------------------------------------------
1079 Procedure check_cost_center
1080 (p_person_id in number,
1081 p_no_cost_center out nocopy number,
1082 p_cost_alloc_keyflex_id out nocopy number,
1083 p_business_group_id out nocopy number,
1084 p_assignment_id out nocopy number,
1085 p_organization_id out nocopy number,
1086 p_cost_center out nocopy varchar2
1087 )
1088 IS
1089
1090 Cursor csr_cost IS
1091 SELECT assg.assignment_id,
1092 assg.business_group_id,
1093 assg.organization_id,
1094 pcak.cost_allocation_keyflex_id,
1095 pcak.concatenated_segments,
1096 pcaf.proportion
1097 FROM per_all_people_f per,
1098 per_all_assignments_f assg,
1099 pay_cost_allocations_f pcaf,
1100 pay_cost_allocation_keyflex pcak
1101 WHERE per.person_id = p_person_id
1102 AND per.person_id = assg.person_id
1103 AND assg.assignment_id = pcaf.assignment_id
1104 AND assg.Primary_flag = 'Y'
1105 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1106 AND pcak.enabled_flag = 'Y'
1107 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
1108 and nvl(pcaf.effective_end_date,sysdate+1)
1109 AND sysdate between nvl(assg.effective_start_date,sysdate)
1110 and nvl(assg.effective_end_date,sysdate+1)
1111 AND sysdate between nvl(per.effective_start_date,sysdate)
1112 and nvl(per.effective_end_date,sysdate+1);
1113
1114
1115 CURSOR get_assignment(p_delegate_id per_all_people_f.person_id%TYPE) IS
1116 SELECT assg.assignment_id,
1117 assg.business_group_id,
1118 assg.organization_id
1119 FROM per_all_people_f per,
1120 per_all_assignments_f assg
1121 WHERE per.person_id = p_delegate_id
1122 AND per.person_id = assg.person_id
1123 AND sysdate between nvl(assg.effective_start_date,sysdate)
1124 and nvl(assg.effective_end_date,sysdate+1)
1125 AND assg.primary_flag = 'Y'
1126 AND sysdate between nvl(per.effective_start_date,sysdate)
1127 and nvl(per.effective_end_date,sysdate+1);
1128
1129
1130 l_proc varchar2(72) := g_package||'return_api_dml_status';
1131 l_no number := 0;
1132 BEGIN
1133
1134 --
1135 hr_utility.set_location('Entering:'||l_proc, 5);
1136 --
1137 For a in csr_cost loop
1138 l_no := l_no+1;
1139 p_cost_alloc_keyflex_id :=a.cost_allocation_keyflex_id;
1140 p_business_group_id :=a.business_group_id ;
1141 p_assignment_id :=a.assignment_id ;
1142 p_organization_id :=a.organization_id ;
1143 --Bug#2228669 hdshah proportion and Percentage sign included.
1144 p_cost_center := a.concatenated_segments ||' ----- '|| a.proportion*100 ||' %';
1145 end loop;
1146 p_no_cost_center := l_no;
1147
1148 if l_no = 0 then
1149 for a in get_assignment(p_person_id) loop
1150 p_business_group_id :=a.business_group_id ;
1151 p_assignment_id :=a.assignment_id ;
1152 p_organization_id :=a.organization_id ;
1153 end loop;
1154 end if;
1155 --
1156 hr_utility.set_location(' Leaving:'||l_proc, 10);
1157
1158 END check_cost_center;
1159
1160 --
1161 -- ----------------------------------------------------------------------------
1162 -- |----------------------<Create_enroll_wf_process>--------------------------|
1163 -- ----------------------------------------------------------------------------
1164 -- {Start Of Comments}
1165 --
1166 -- Description:
1167 -- This package is used for self service application to create enrollment
1168 -- data when user enroll in the class.
1169 --
1170 -- Prerequisites:
1171 --
1172 -- In Parameters:
1173 --
1174 -- Post Success:
1175 -- Enrollment data will be created.
1176 --
1177 -- Post Failure:
1178 -- Status will be passed to the caller and the caller will raise a notification.
1179 --
1180 -- Developer Implementation Notes:
1181 -- The attrbute in parameters should be modified as to the business process
1182 -- requirements.
1183 --
1184 -- Access Status:
1185 -- Internal Development Use Only.
1186 --
1187 -- {End Of Comments}
1188 -- ----------------------------------------------------------------------------
1189 /*Procedure create_enroll_wf_process
1190 (p_process in wf_process_activities.process_name%type,
1191 p_itemtype in wf_items.item_type%type,
1192 p_person_id in number ,
1193 p_called_from in varchar2 ,
1194 p_itemkey out nocopy wf_items.item_key%type
1195 )
1196 IS
1197
1198 l_proc varchar2(72) := g_package||'create_enroll_wf_process';
1199 l_process wf_activities.name%type := upper(p_process);
1200 l_item_type wf_items.item_type%type := upper(p_itemtype);
1201 l_item_key wf_items.item_key%type;
1202
1203
1204 l_user_name varchar2(80);
1205 l_current_username varchar2(80):= fnd_profile.value('USERNAME');
1206 l_current_user_Id number := fnd_profile.value('USER_ID');
1207 l_creator_person_id per_all_people_f.person_id%type;
1208
1209 CURSOR C_USER IS
1210 SELECT
1211 EMPLOYEE_ID
1212 FROM
1213 FND_USER
1214 WHERE
1215 user_id = l_current_user_id ;
1216
1217 BEGIN
1218 hr_utility.set_location('Entering:'||l_proc, 5);
1219
1220 OPEN C_USER;
1221 FETCH C_USER INTO l_creator_person_id;
1222 CLOSE C_USER;
1223
1224 hr_utility.set_location('Entering:'||l_proc, 10);
1225 -- Get the next item key from the sequence
1226 select hr_workflow_item_key_s.nextval
1227 into l_item_key
1228 from sys.dual;
1229
1230
1231 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1232 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CURRENT_PERSON_ID', p_person_id);
1233 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_USERNAME', l_current_username);
1234 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_ID', l_creator_person_id);
1235 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'PROCESS_DISPLAY_NAME', 'Enroll in a Training Event');
1236
1237
1238 --WF_ENGINE.SetItemattrtext(p_itemtype,p_item_key, 'EVENT_OWNER',l_user_name);
1239 WF_ENGINE.STARTPROCESS(p_itemtype,l_item_key);
1240
1241 p_itemkey:=l_item_key;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1246 hr_utility.set_location('leaving:'||l_proc, 20);
1247
1248
1249 End create_enroll_wf_process;
1250
1251 */
1252 --
1253 -- ----------------------------------------------------------------------------
1254 -- |----------------------<Get_Person_To_Enroll_Details >----------------------|
1255 -- ----------------------------------------------------------------------------
1256 -- {Start Of Comments}
1257 --
1258 -- Description:
1259 -- This package is used for self service application to create enrollment
1260 -- data when user enroll in the class.
1261 --
1262 -- Prerequisites:
1263 --
1264 -- In Parameters:
1265 --
1266 -- Post Success:
1267 -- Enrollment data will be created.
1268 --
1269 -- Post Failure:
1270 -- Status will be passed to the caller and the caller will raise a notification.
1271 --
1272 -- Developer Implementation Notes:
1273 -- The attrbute in parameters should be modified as to the business process
1274 -- requirements.
1275 --
1276 -- Access Status:
1277 -- Internal Development Use Only.
1278 --
1279 -- {End Of Comments}
1280 -- ----------------------------------------------------------------------------
1281
1282 FUNCTION Get_Person_To_Enroll_Details (p_person_id per_all_people_f.PERSON_ID%TYPE)
1283 RETURN csr_person_to_enroll_details%ROWTYPE
1284
1285 IS
1286 --
1287 l_csr_person_to_enroll_details csr_person_to_enroll_details%ROWTYPE;
1288 --
1289 l_person_id BINARY_INTEGER;
1290
1291 BEGIN
1292 --
1293 OPEN csr_person_to_enroll_details (p_person_id);
1294 --
1295 FETCH csr_person_to_enroll_details INTO l_csr_person_to_enroll_details;
1296 --
1297 IF csr_person_to_enroll_details%NOTFOUND THEN
1298 CLOSE csr_person_to_enroll_details;
1299 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1300 RAISE OTA_TRNG_ENROLL_SS.g_mesg_on_stack_exception ;
1301 ELSE
1302 CLOSE csr_person_to_enroll_details;
1303 RETURN l_csr_person_to_enroll_details;
1304 END IF;
1305
1306 EXCEPTION
1307 WHEN OTA_TRNG_ENROLL_SS.g_mesg_on_stack_exception THEN
1308 --
1309 -- Handle the exception in the calling code.
1310 RAISE;
1311
1312 WHEN OTHERS THEN
1313 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1314 RAISE;
1315
1316 END Get_Person_To_Enroll_Details;
1317
1318 --
1319 -- ----------------------------------------------------------------------------
1320 -- |-------------------------------<Validate_Enrollment>----------------------|
1321 -- ----------------------------------------------------------------------------
1322 -- {Start Of Comments}
1323 --
1324 -- Description:
1325 -- This package is used for self service application to create enrollment
1326 -- data when user enroll in the class.
1327 --
1328 -- Prerequisites:
1329 --
1330 -- In Parameters:
1331 --
1332 -- Post Success:
1333 -- Enrollment data will be created.
1334 --
1335 -- Post Failure:
1336 -- Status will be passed to the caller and the caller will raise a notification.
1337 --
1338 -- Developer Implementation Notes:
1339 -- The attrbute in parameters should be modified as to the business process
1340 -- requirements.
1341 --
1342 -- Access Status:
1343 -- Internal Development Use Only.
1344 --
1345 -- {End Of Comments}
1346 -- ----------------------------------------------------------------------------
1347
1348 PROCEDURE Validate_enrollment(p_person_id per_all_people_f.PERSON_ID%TYPE,
1349 p_event_id IN VARCHAR2,
1350 p_double_book out nocopy VARCHAR2 )
1351
1352 IS
1353
1354 l_person_details
1355 OTA_TRNG_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
1356 l_cancel_boolean boolean;
1357 l_dummy number;
1358 Begin
1359 p_double_book := 'N';
1360 l_person_details := OTA_TRNG_ENROLL_SS.Get_Person_To_Enroll_Details(p_person_id => p_person_id);
1361
1362 l_cancel_boolean :=
1363 Chk_Event_Cancelled_for_Person
1364 (p_event_id => p_event_id
1365 ,p_delegate_person_id => p_person_id
1366 ,p_booking_id => l_dummy);
1367 IF (l_cancel_boolean) THEN
1368 -- Delegate has Cancelled status, so dont check for unique_booking
1369 -- as a row exists for delegate, for this event
1370 null;
1371 ELSE
1372 ota_tdb_bus.check_unique_booking
1373 (p_customer_id => ''
1374 ,p_organization_id => l_person_details.organization_id
1375 ,p_event_id => p_event_id
1376 ,p_delegate_person_id => p_person_id
1377 ,p_delegate_contact_id => ''
1378 ,p_booking_id => '');
1379 END IF;
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 p_double_book := 'Y';
1383
1384
1385 end Validate_Enrollment;
1386
1387
1388 -- |--------------------------------------------------------------------------|
1389 -- |--< Chk_Event_Cancelled_for_Person >--------------------------------------|
1390 -- |--------------------------------------------------------------------------|
1391 --
1392 -- {Start Of Comments}
1393 --
1394 -- Description:
1395 -- This package is used for self service application to create enrollment
1396 -- data when user enroll in the class.
1397 --
1398 -- Prerequisites:
1399 --
1400 -- In Parameters:
1401 --
1402 -- Post Success:
1403 -- Enrollment data will be created.
1404 --
1405 -- Post Failure:
1406 -- Status will be passed to the caller and the caller will raise a notification.
1407 --
1408 -- Developer Implementation Notes:
1409 -- The attrbute in parameters should be modified as to the business process
1410 -- requirements.
1411 --
1412 -- Access Status:
1413 -- Internal Development Use Only.
1414 --
1415 -- {End Of Comments}
1416 -- ----------------------------------------------------------------------------
1417 FUNCTION Chk_Event_Cancelled_for_Person (p_event_id IN NUMBER
1418 ,p_delegate_person_id IN NUMBER
1419 ,p_booking_id OUT nocopy NUMBER)
1420 RETURN BOOLEAN
1421
1422 IS
1423
1424 CURSOR csr_chk_event
1425 (p_event_id IN NUMBER
1426 ,p_person_id IN NUMBER) IS
1427 SELECT ov.booking_id
1428 FROM ota_booking_status_types os,
1429 ota_delegate_bookings ov
1430 WHERE ov.event_id = p_event_id
1431 AND ov.delegate_person_id = p_person_id
1432 AND os.booking_status_type_id = ov.booking_status_type_id
1433 AND os.type = 'C';
1434
1435 CURSOR csr_chk_event_placed
1436 (p_event_id IN NUMBER
1437 ,p_person_id IN NUMBER) IS
1438 SELECT ov.booking_id
1439 FROM ota_booking_status_types os,
1440 ota_delegate_bookings ov
1441 WHERE ov.event_id = p_event_id
1442 AND ov.delegate_person_id = p_person_id
1443 AND os.booking_status_type_id = ov.booking_status_type_id
1444 AND os.type <> 'C';
1445
1446 l_temp csr_chk_event%rowtype;
1447 l_enroll_exist boolean := False;
1448
1449 BEGIN
1450
1451 OPEN csr_chk_event(p_event_id, p_delegate_person_id);
1452 FETCH csr_chk_event INTO l_temp;
1453
1454 p_booking_id := l_temp.booking_id;
1455
1456 IF csr_chk_event%FOUND THEN
1457
1458
1459 For r_enroll in csr_chk_event_placed(p_event_id, p_delegate_person_id)
1460 LOOP
1461 if r_enroll.booking_id is not null then
1462 l_enroll_exist := True;
1463 end if;
1464 END LOOP;
1465 if l_enroll_exist then
1466 RETURN FALSE;
1467 else
1468 RETURN TRUE;
1469 end if;
1470
1471
1472 ELSE
1473 -- PERSON DOES NOT HAVE A BOOKING STATUS OF CANCELLED FOR THIS EVENT
1474 --
1475 RETURN FALSE;
1476 --
1477 END IF;
1478
1479 -- p_booking_id := l_temp.booking_id;
1480 CLOSE csr_chk_event;
1481 END Chk_Event_Cancelled_for_Person;
1482
1483 -- |--------------------------------------------------------------------------|
1484 -- |---------------< Chk_booking_clash >--------------------------------------|
1485 -- |--------------------------------------------------------------------------|
1486 --
1487 -- {Start Of Comments}
1488 --
1489 -- Description:
1490 -- This package is used for self service application to create enrollment
1491 -- data when user enroll in the class.
1492 --
1493 -- Prerequisites:
1494 --
1495 -- In Parameters:
1496 --
1497 -- Post Success:
1498 -- Enrollment data will be created.
1499 --
1500 -- Post Failure:
1501 -- Status will be passed to the caller and the caller will raise a notification.
1502 --
1503 -- Developer Implementation Notes:
1504 -- The attrbute in parameters should be modified as to the business process
1505 -- requirements.
1506 --
1507 -- Access Status:
1508 -- Internal Development Use Only.
1509 --
1510 -- {End Of Comments}
1511 -- ----------------------------------------------------------------------------
1512 procedure Chk_booking_clash (p_event_id IN NUMBER
1513 ,p_person_id IN NUMBER
1514 ,p_booking_Clash OUT nocopy varchar2)
1515 IS
1516 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
1517 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1518
1519
1520 Begin
1521
1522 l_booking_status_row := Get_Booking_Status_for_web
1523 (p_web_booking_status_type => 'PLACED'
1524 ,p_business_group_id => ota_general.get_business_group_id);
1525
1526 l_booking_status_type_id := l_booking_status_row.booking_status_type_id;
1527
1528 IF ota_tdb_bus2.other_bookings_clash(
1529 p_delegate_person_id => p_person_id
1530 ,p_delegate_contact_id=> ''
1531 ,p_event_id => p_event_id
1532 ,p_booking_status_type_id => l_booking_status_type_id ) THEN
1533
1534 p_booking_clash := 'Y';
1535 ELSE
1536 p_booking_clash := 'N';
1537
1538 END IF;
1539
1540 end Chk_booking_clash;
1541
1542 --|--------------------------------------------------------------------------|
1543 --|--< CHK_FOR_SECURE_EVT >-------------------------------------------|
1544 --|--------------------------------------------------------------------------|
1545
1546 FUNCTION CHK_FOR_SECURE_EVT (
1547 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1548 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
1549 RETURN VARCHAR2
1550 IS
1551
1552 CURSOR C_GET_SECURE_FLAG is
1553 SELECT secure_event_flag,organization_id
1554 from ota_events
1555 where event_id = p_event_id;
1556
1557 CURSOR C_GET_ORG_ID is
1558 SELECT organization_id
1559 from per_all_assignments_f
1560 where person_id = p_delegate_id and
1561 trunc(sysdate) between effective_start_date and
1562 effective_end_date;
1563
1564
1565 l_secure_flag varchar2(1);
1566 l_evt_organization_id OTA_EVENTS.ORGANIZATION_ID%TYPE;
1567 l_per_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1568 l_return_value varchar2(2000);
1569
1570 BEGIN
1571
1572 OPEN C_GET_SECURE_FLAG;
1573 FETCH C_GET_SECURE_FLAG into l_secure_flag,l_evt_organization_id;
1574 IF C_GET_SECURE_FLAG%NOTFOUND then
1575 CLOSE C_GET_SECURE_FLAG;
1576 l_return_value := NULL;
1577 return l_return_value;
1578 ELSE
1579 CLOSE C_GET_SECURE_FLAG;
1580 END IF;
1581 IF l_secure_flag = 'Y' then
1582
1583 OPEN C_GET_ORG_ID;
1584 FETCH C_GET_ORG_ID into l_per_organization_id;
1585 IF C_GET_ORG_ID%NOTFOUND then
1586 CLOSE C_GET_ORG_ID;
1587 l_return_value := NULL;
1588 return l_return_value;
1589 ELSE
1590 CLOSE C_GET_ORG_ID;
1591 END IF;
1592 if l_per_organization_id = l_evt_organization_id then
1593 l_return_value := '-1';
1594 else
1595 l_return_value := NULL;
1596 end if;
1597 ELSE
1598 l_return_value := '-1';
1599
1600 END IF;
1601
1602 RETURN l_return_value;
1603
1604 END CHK_FOR_SECURE_EVT;
1605
1606
1607 --|--------------------------------------------------------------------------|
1608 --|--< CHK_DELEGATE_OK_FOR_EVENT>-------------------------------------------|
1609 --|--------------------------------------------------------------------------|
1610
1611 FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
1612 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1613 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
1614 , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
1615 )
1616 RETURN VARCHAR2
1617
1618 IS
1619 CURSOR csr_event_associations IS
1620 SELECT ea.organization_id, ea.job_id, ea.position_id
1621 FROM ota_event_associations ea
1622 WHERE ea.event_id = p_event_id;
1623 --
1624
1625 CURSOR csr_event_start_date IS
1626 SELECT course_start_date
1627 -- FROM otv_scheduled_events
1628 FROM ota_events
1629 WHERE event_id = p_event_id and
1630 --Bug#2201434 SELFPACED event_type included.
1631 -- event_type='SCHEDULED' and
1632 event_type in ('SCHEDULED','SELFPACED') and
1633 event_status in('P','N','F')and
1634 TRUNC(SYSDATE) BETWEEN NVL( ENROLMENT_START_DATE, TRUNC(
1635 SYSDATE)) AND
1636 NVL( ENROLMENT_END_DATE, TRUNC( SYSDATE)) AND
1637 TRUNC(SYSDATE) <= NVL( COURSE_END_DATE, TRUNC(SYSDATE));
1638 --
1639 CURSOR csr_asg_details
1640 (p_organization_id OTA_EVENT_ASSOCIATIONS.organization_id%TYPE
1641 ,p_job_id OTA_EVENT_ASSOCIATIONS.job_id%TYPE
1642 ,p_position_id OTA_EVENT_ASSOCIATIONS.position_id%TYPE
1643 ,p_course_start_date
1644 otv_scheduled_events.course_start_date%type) IS
1645 SELECT a.assignment_id
1646 FROM per_all_assignments_f a
1647 WHERE a.person_id = p_delegate_id
1648 AND NVL(p_course_start_date,trunc(sysdate)) BETWEEN
1649 a.effective_start_date AND a.effective_end_date
1650 AND NVL(p_organization_id, -1) = DECODE(p_organization_id, null, -1,
1651 NVL(a.organization_id,-1))
1652 AND NVL(p_job_id, -1) = DECODE(p_job_id, null, -1,
1653 NVL(a.job_id, -1))
1654 AND NVL(p_position_id,-1) = DECODE(p_position_id, null, -1,
1655 NVL(a.position_id, -1))
1656 AND a.assignment_type = 'E';
1657 --
1658 l_return_value varchar2(4000);
1659 l_event_start_date otv_scheduled_events.course_start_date%type;
1660
1661 BEGIN
1662 --Bug#2201434 default value of l_return_value modified to '-1' from null.
1663 -- l_return_value := null;
1664 l_return_value := '-1';
1665 IF p_event_start_date IS null THEN
1666 OPEN csr_event_start_date;
1667 FETCH csr_event_start_date INTO l_event_start_date;
1668 CLOSE csr_event_start_date;
1669 ELSE
1670 l_event_start_date := p_event_start_date;
1671 END IF;
1672
1673 FOR assoc IN csr_event_associations LOOP
1674 -- For each of the event restrictions, loop
1675 OPEN csr_asg_details
1676 (p_organization_id => assoc.organization_id
1677 ,p_job_id => assoc.job_id
1678 ,p_position_id => assoc.position_id
1679 ,p_course_start_date => l_event_start_date);
1680 FETCH csr_asg_details INTO l_return_value;
1681 IF (csr_asg_details%FOUND) THEN
1682 l_return_value := '-1';
1683 CLOSE csr_asg_details;
1684 EXIT;
1685 ELSE
1686 -- The delegate hasn't got an assignment which satified the needs
1687 -- of all the event associations, so set the return value to 'N'
1688 CLOSE csr_asg_details;
1689 l_return_value := NULL;
1690 END IF;
1691 END LOOP;
1692
1693 RETURN l_return_value;
1694 END CHK_DELEGATE_OK_FOR_EVENT;
1695
1696 /*
1697 FUNCTION Get_Current_Person_ID
1698 (p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
1699 ,p_item_key IN WF_ITEMS.ITEM_KEY%TYPE)
1700 RETURN NUMBER
1701
1702 IS
1703 l_current_person_id NUMBER;
1704
1705 BEGIN
1706 --
1707
1708 l_current_person_id := wf_engine.getItemAttrNumber
1709 (itemtype => p_item_type
1710 ,itemkey => p_item_key
1711 ,aname => 'CURRENT_PERSON_ID');
1712 --
1713
1714
1715 RETURN l_current_person_id;
1716 END Get_Current_Person_ID;
1717
1718 -- ----------------------------------------------------------------------------
1719 -- |------------------------< CHECK_ENROLLMENT_CREATION>----------------------|
1720 -- ----------------------------------------------------------------------------
1721 -- {Start Of Comments}
1722 --
1723 -- Description:
1724 -- This procedure will be a concurrent process which run in the background.
1725 --
1726 -- This procedure will only be used for OTA and OM integration. Basically this
1727 -- procedure will select all delegate booking data that has daemon_flag='Y' and
1728 -- Daemon_type is not nul. If the enrollment got canceled and there is a
1729 -- waitlisted student then the automatic waitlist processing will be called.
1730 --
1731 -- Pre Conditions:
1732 -- None.
1733 --
1734 -- IN
1735 -- itemtype - type of the current item
1736 -- itemkey - key of the current item
1737 -- actid - process activity instance id
1738 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1739 -- OUT
1740 -- result
1741 -- - COMPLETE[:<result>]
1742 -- activity has completed with the indicated result
1743 -- - WAITING
1744 -- activity is waiting for additional transitions
1745 -- - DEFERED
1746 -- execution should be defered to background
1747 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1748 -- activity has notified an external entity that this
1749 -- step must be performed. A call to wf_engine.CompleteActivty
1750 -- will signal when this step is complete. Optional
1751 -- return of notification ID and assigned user.
1752 -- - ERROR[:<error_code>]
1753 -- function encountered an err--
1754 --
1755 -- Post Success:
1756 -- Processing continues.
1757 --
1758 --
1759 -- Post Failure:
1760 -- None.
1761 --
1762 -- Access Status:
1763 -- Public.
1764 --
1765 -- {End Of Comments}
1766 ----------------------------------------------------------------------------
1767 PROCEDURE CHECK_ENROLLMENT_CREATION(
1768 Itemtype IN VARCHAR2
1769 ,Itemkey IN VARCHAR2
1770 ,actid IN NUMBER
1771 ,funcmode IN VARCHAR2
1772 ,resultout OUT nocopy VARCHAR2
1773 )
1774
1775 IS
1776
1777 l_booking_id ota_delegate_bookings.booking_id%type;
1778 l_proc varchar2(72) := g_package||'check_enrollment_creation';
1779
1780
1781 BEGIN
1782 hr_utility.set_location('Entering:'||l_proc, 5);
1783 IF (funcmode = 'RUN') THEN
1784 l_booking_id := wf_engine.GetItemAttrNUMBER(itemtype => itemtype
1785 ,itemkey => itemkey
1786 ,aname => 'BOOKING_ID');
1787
1788 IF l_booking_id is not null then
1789 resultout := wf_engine.eng_completed || ':' || 'Y';
1790 ELSE
1791 resultout := wf_engine.eng_completed || ':' || 'N';
1792 END IF;
1793
1794
1795
1796 END IF;
1797 -- CANCEL mode - activity 'compensation'
1798 --
1799 -- This is an event point is called with the effect of the activity must
1800 -- be undone, for example when a process is reset to an earlier point
1801 -- due to a loop back.
1802 --
1803 IF (funcmode = 'CANCEL') THEN
1804
1805 -- no result needed
1806 resultout := 'COMPLETE';
1807 return;
1808 END IF;
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 -- The line below records this function call in the error system
1813 -- in the case of an exception.
1814 wf_core.context('OTA_WF', 'Check_Creation',
1815 itemtype, itemkey, to_char(actid), funcmode);
1816 RAISE;
1817
1818 hr_utility.set_location(' Leaving:'||l_proc, 10);
1819 END;
1820
1821
1822 --
1823 -- ----------------------------------------------------------------------------
1824 -- |--------------------------------< CHECK_WF_STATUS>-------------------------|
1825 -- ----------------------------------------------------------------------------
1826 -- {Start Of Comments}
1827 --
1828 -- Description:
1829 -- This function will be a used to check the workflow status of Order Line.
1830 --
1831 -- IN
1832 -- p_line_id
1833 --
1834 -- OUT
1835 -- p_exist
1836 --
1837 -- Post Failure:
1838 -- None.
1839 --
1840 -- Access Status:
1841 -- Public.
1842 --
1843 -- {End Of Comments}
1844 ----------------------------------------------------------------------------
1845
1846 FUNCTION Check_wf_Status (
1847 p_item_key NUMBER,
1848 p_activity varchar2,
1849 p_item_type VARCHAR2)
1850
1851 return boolean
1852
1853 IS
1854
1855 l_proc varchar2(72) := g_package||'Check_wf_Status' ;
1856 l_exist varchar2(1);
1857 l_return boolean :=False;
1858
1859 CURSOR line_wf IS
1860 SELECT null
1861 FROM wf_item_activity_statuses_v wf
1862 WHERE activity_name = p_activity
1863 AND activity_status_code = 'NOTIFIED'
1864 AND item_type = p_item_type
1865 AND item_key = p_item_key;
1866
1867 BEGIN
1868 hr_utility.set_location('Entering:'||l_proc, 5);
1869 OPEN line_wf;
1870 fetch line_wf into l_exist;
1871 if line_wf%found then
1872 l_return := True;
1873 end if;
1874 CLOSE line_wf;
1875 Return(l_return);
1876
1877 hr_utility.set_location('Leaving:'||l_proc, 10);
1878 END check_wf_status;
1879
1880 --
1881 -- -----------------------------------------------------------
1882 -- Cross Charges Notifications (Workflow Notifications)
1883 -- -----------------------------------------------------------
1884 --
1885 PROCEDURE Cross_Charges_Notifications ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1886 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1887 actid IN NUMBER,
1888 funcmode IN VARCHAR2,
1889 resultout OUT nocopy VARCHAR2 )
1890 IS
1891
1892 CURSOR user_name(p_event_id OTA_EVENTS.event_id%TYPE) IS
1893 SELECT usr.user_name,
1894 evt.offering_id
1895 FROM OTA_EVENTS evt,
1896 FND_USER USR
1897 WHERE evt.event_id = p_event_id and
1898 usr.employee_id = evt.owner_id;
1899
1900
1901 CURSOR csr_booking_status(p_booking_id ota_delegate_bookings.booking_id%type) IS
1902 SELECT bst.Type
1903 FROM OTA_DELEGATE_BOOKINGS tdb,
1904 OTA_BOOKING_STATUS_TYPES bst
1905 WHERE tdb.booking_id = p_booking_id
1906 AND bst.booking_status_type_id = tdb.booking_status_type_id;
1907
1908
1909 l_api_result VARCHAR2(4000);
1910 l_api_from VARCHAR2(4000);
1911 l_api_to VARCHAR2(4000);
1912 l_event_id NUMBER;
1913 l_user_name FND_USER.USER_NAME%TYPE;
1914 l_offering_id ota_events.offering_id%type;
1915 l_booking_id ota_delegate_bookings.booking_id%type;
1916 l_version_name ota_activity_versions.version_name%type;
1917 l_notification_text varchar2(2000);
1918 l_status_type ota_booking_status_types.type%type;
1919 BEGIN
1920
1921 l_event_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1922 ,itemkey => itemkey
1923 ,aname => 'EVENT_ID');
1924
1925 OPEN user_name(l_event_id);
1926 FETCH user_name INTO l_user_name, l_offering_id;
1927 CLOSE user_name;
1928
1929
1930 l_booking_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1931 ,itemkey => itemkey
1932 ,aname => 'BOOKING_ID');
1933
1934 l_notification_text := wf_engine.GetItemAttrText(itemtype => itemtype
1935 ,itemkey => itemkey
1936 ,aname => 'NOTIFICATION_TEXT');
1937
1938 l_version_name := wf_engine.GetItemAttrText(itemtype => itemtype
1939 ,itemkey => itemkey
1940 ,aname => 'OTA_ACTIVITY_VERSION_NAME');
1941
1942 IF l_booking_id is not null then
1943
1944 For sts in csr_booking_status(l_booking_id)
1945 LOOP
1946 l_status_type := sts.type;
1947 END LOOP;
1948
1949 if l_status_type = 'P' then
1950 if l_offering_id is not null then
1951 l_notification_text := l_notification_text
1952 || ' The student can now play the content '
1953 || l_version_name ||'.';
1954 wf_engine.setItemAttrText (itemtype => itemtype
1955 ,itemkey => itemkey
1956 ,aname => 'NOTIFICATION_TEXT'
1957 ,avalue => l_notification_text);
1958
1959 end if;
1960 elsif l_status_type = 'W' then
1961
1962 l_notification_text := l_notification_text || ' The student has been placed on a waiting list. ';
1963
1964 wf_engine.setItemAttrText (itemtype => itemtype
1965 ,itemkey => itemkey
1966 ,aname => 'NOTIFICATION_TEXT'
1967 ,avalue => l_notification_text);
1968 end if;
1969
1970 END IF;
1971
1972 wf_engine.setItemAttrText (itemtype => itemtype
1973 ,itemkey => itemkey
1974 ,aname => 'EVENT_OWNER_EMAIL'
1975 ,avalue => l_user_name);
1976
1977 l_api_result := wf_engine.GetItemAttrText(itemtype => itemtype
1978 ,itemkey => itemkey
1979 ,aname => 'API_RESULT');
1980
1981 l_api_from := wf_engine.GetItemAttrText(itemtype => itemtype
1982 ,itemkey => itemkey
1983 ,aname => 'API_FROM');
1984
1985 l_api_to := wf_engine.GetItemAttrText(itemtype => itemtype
1986 ,itemkey => itemkey
1987 ,aname => 'API_TO');
1988
1989 IF (funcmode='RUN') THEN
1990
1991 IF l_api_result = 'S' THEN
1992 resultout:='COMPLETE:SUCCESS';
1993 RETURN;
1994 ELSE
1995 IF l_api_from IS NOT NULL THEN
1996 resultout:='COMPLETE:FROM_ERROR';
1997
1998 ELSIF l_api_to IS NOT NULL THEN
1999 resultout:='COMPLETE:ERROR_TO';
2000 ELSE
2001 resultout:='COMPLETE:SUCCESS';
2002 RETURN;
2003 END IF;
2004 END IF;
2005
2006 END IF;
2007
2008 IF (funcmode='CANCEL') THEN
2009 resultout:='COMPLETE';
2010 RETURN;
2011 END IF;
2012
2013 END Cross_Charges_Notifications;
2014 */
2015
2016 /* Added By Dharma */
2017 --
2018 -- ------------------------------------------------------------------
2019 -- PROCEDURE Approved
2020 -- ------------------------------------------------------------------
2021 --
2022
2023 /*
2024 PROCEDURE Approved ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2025 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2026 actid IN NUMBER,
2027 funcmode IN VARCHAR2,
2028 resultout OUT nocopy VARCHAR2 ) IS
2029
2030 BEGIN
2031
2032 IF (funcmode='RUN') THEN
2033 wf_engine.setItemAttrText (itemtype => itemtype
2034 ,itemkey => itemkey
2035 ,aname => 'APPROVAL_RESULT'
2036 ,avalue => 'ACCEPTED');
2037 resultout:='COMPLETE';
2038 RETURN;
2039 END IF;
2040
2041 IF (funcmode='CANCEL') THEN
2042 resultout:='COMPLETE';
2043 RETURN;
2044 END IF;
2045
2046 END Approved;
2047 */
2048 --|--------------------------------------------------------------------------|
2049 --|--< CHK_FOR_RESTRICTED_EVT >-------------------------------------------|
2050 --|--------------------------------------------------------------------------|
2051
2052 FUNCTION CHK_FOR_RESTRICTED_EVT (
2053 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
2054 RETURN VARCHAR2
2055 IS
2056
2057 CURSOR C_GET_EVT_DETAILS is
2058 SELECT EVT.public_event_flag,EVT.maximum_internal_attendees
2059 from ota_events EVT, ota_event_associations EVA
2060 where EVT.event_id = p_event_id and
2061 EVT.event_id = EVA.event_id and
2062 EVA.customer_id is not null;
2063
2064 l_public_event_flag OTA_EVENTS.PUBLIC_EVENT_FLAG%TYPE;
2065 l_maximum_internal_attendees OTA_EVENTS.MAXIMUM_INTERNAL_ATTENDEES%TYPE;
2066 l_return_value varchar2(10);
2067
2068 BEGIN
2069
2070 OPEN C_GET_EVT_DETAILS;
2071 FETCH C_GET_EVT_DETAILS into l_public_event_flag,l_maximum_internal_attendees;
2072 IF C_GET_EVT_DETAILS%NOTFOUND then
2073 CLOSE C_GET_EVT_DETAILS;
2074 l_return_value := '-1';
2075 return l_return_value;
2076 ELSE
2077 CLOSE C_GET_EVT_DETAILS;
2078 END IF;
2079
2080 IF l_public_event_flag = 'N' then
2081
2082 IF l_maximum_internal_attendees = 0 then
2083 l_return_value := NULL;
2084 ELSE
2085 l_return_value := '-1';
2086 END IF;
2087
2088 ELSE
2089 l_return_value := '-1';
2090
2091 END IF;
2092
2093 RETURN l_return_value;
2094
2095 END CHK_FOR_RESTRICTED_EVT;
2096
2097 --
2098 -- ----------------------------------------------------------------------------
2099 -- |--------------------------------< Get_Booking_OVN >-----------------------|
2100 -- ----------------------------------------------------------------------------
2101 -- {Start Of Comments}
2102 --
2103 -- Description:
2104 -- This function will be used to return object version number of an enrollment.
2105 --
2106 -- IN
2107 -- p_booking_id
2108 --
2109 -- OUT
2110 -- p_ovn
2111 --
2112 -- Post Failure:
2113 -- None.
2114 --
2115 -- Access Status:
2116 -- Public.
2117 --
2118 -- {End Of Comments}
2119 ----------------------------------------------------------------------------
2120 FUNCTION Get_Booking_OVN (p_booking_id IN NUMBER)
2121 RETURN NUMBER
2122
2123 IS
2124
2125 CURSOR csr_get_ovn IS
2126 SELECT object_version_number
2127 FROM ota_delegate_bookings
2128 WHERE booking_id = p_booking_id;
2129 --
2130 l_ovn BINARY_INTEGER DEFAULT '';
2131
2132 BEGIN
2133 -- Get the Object Version No. of the Enrollment.
2134 --
2135 OPEN csr_get_ovn;
2136 FETCH csr_get_ovn INTO l_ovn;
2137
2138 IF csr_get_ovn%NOTFOUND THEN
2139 --
2140 -- Major Problem as the record can't be located.
2141 --
2142 CLOSE csr_get_ovn;
2143 --
2144 -- Set a technical message, then the calling proc can decide
2145 -- what to do.
2146 --
2147 fnd_message.set_name('OTA','OTA_13661_PROB_GETTING_DATA');
2148 --
2149 --
2150 ELSE
2151 --
2152 CLOSE csr_get_ovn;
2153 --
2154 END IF;
2155 --
2156 RETURN l_ovn;
2157 --
2158 END Get_Booking_OVN;
2159
2160 end ota_trng_enroll_ss;