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