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