1 PACKAGE OTA_LEARNER_ENROLL_SS AUTHID CURRENT_USER as
2 /* $Header: otlnrenr.pkh 120.12 2010/11/12 13:47:12 shwnayak noship $ */
3
4 Type resp_id_tab is table of fnd_user_resp_groups.responsibility_id%type INDEX BY BINARY_INTEGER;
5 Type sec_id_tab is table of fnd_user_resp_groups.security_group_id%type INDEX BY BINARY_INTEGER;
6
7
8 --
9 -- CSR_BOOKING_STATUS_ID retrieves a single booking_status_type_id for
10 -- a particular BG of the reqired type.
11 -- It selects using the following priority :-
12 -- 1) The name is like 'W:%' -- This indicates it is seeded for the web
13 -- 2) The default flag is set
14 -- 3) The first row retrieved that is of the type required.
15 --
16 CURSOR csr_booking_status_id (p_business_group_id IN NUMBER
17 ,p_web_booking_status_type IN VARCHAR2) IS
18 SELECT bst.BOOKING_STATUS_TYPE_ID,bst.BUSINESS_GROUP_ID,bst.ACTIVE_FLAG,bst.DEFAULT_FLAG,bst.PLACE_USED_FLAG,
19 bstt.NAME,bst.OBJECT_VERSION_NUMBER,bst.TYPE,bst.COMMENTS,bstt.DESCRIPTION,bst.LAST_UPDATE_DATE,
20 bst.LAST_UPDATED_BY,bst.LAST_UPDATE_LOGIN,bst.CREATED_BY,bst.CREATION_DATE,
21 bst.BST_INFORMATION_CATEGORY,bst.BST_INFORMATION1,bst.BST_INFORMATION2,
22 bst.BST_INFORMATION3,bst.BST_INFORMATION4,bst.BST_INFORMATION5,
23 bst.BST_INFORMATION6,bst.BST_INFORMATION7,bst.BST_INFORMATION8,
24 bst.BST_INFORMATION9,bst.BST_INFORMATION10,bst.BST_INFORMATION11,
25 bst.BST_INFORMATION12,bst.BST_INFORMATION13,bst.BST_INFORMATION14,
26 bst.BST_INFORMATION15,bst.BST_INFORMATION16,bst.BST_INFORMATION17,
27 bst.BST_INFORMATION18,bst.BST_INFORMATION19,bst.BST_INFORMATION20
28 FROM ota_booking_status_types bst, ota_booking_status_types_tl bstt
29 WHERE bst.business_group_id = p_business_group_id
30 AND bst.booking_status_type_id = bstt.booking_status_type_id
31 AND bstt.language=userenv('LANG')
32 AND bst.ACTIVE_FLAG = 'Y'
33 AND rownum=1
34 AND bst.type = DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
35 , 'WAITLISTED','W'
36 , 'CANCELLED', 'C'
37 , 'ATTENDED' , 'A'
38 , 'PLACED' , 'P'
39 , 'PENDING EVALUATION', 'E')
40 -- The name is like W: ( highest priority choice)
41 AND ( (bstt.name like 'W:%' and bst.ACTIVE_FLAG = 'Y')
42 -- There are no names like W:, so a defaulted required status is
43 -- the second choice.
44 OR ( NOT EXISTS (SELECT 1
45 FROM ota_booking_status_types bst1, ota_booking_status_types_tl bstt1
46 WHERE bst1.business_group_id = p_business_group_id
47 AND bst1.booking_status_type_id = bstt1.booking_status_type_id
48 AND bstt1.language=userenv('LANG')
49 AND bst1.type =
50 DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
51 , 'WAITLISTED','W'
52 , 'CANCELLED', 'C'
53 , 'ATTENDED' , 'A'
54 , 'PLACED' , 'P'
55 , 'PENDING EVALUATION', 'E')
56 AND bstt1.name like 'W:%' AND bst1.active_flag = 'Y')
57 AND ( (bst.default_flag = 'Y')
58 -- If there are no names like 'W:%' and no defaulted status of type
59 -- required, then select one that is of type required
60 OR NOT EXISTS (SELECT 1
61 FROM ota_booking_status_types
62 WHERE business_group_id = p_business_group_id
63 AND type =
64 DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
65 , 'WAITLISTED','W'
66 , 'CANCELLED', 'C'
67 , 'ATTENDED' , 'A'
68 , 'PLACED' , 'P'
69 , 'PENDING EVALUATION', 'E')
70 AND default_flag ='Y')
71 )
72 )
73 );
74
75 --
76 -- Cursor to retrieve necessary information in order to save the enrollment
77 -- NOTE : Removed the address section as it appears that a person doesn't
78 -- need to have a primary address (however, I've only commented it out
79 -- just incase)
80
81 CURSOR csr_person_to_enroll_details (p_person_id number) IS
82 SELECT pp.last_name
83 ,pp.first_name
84 ,pp.full_name
85 ,pp.business_group_id
86 ,pp.email_address
87 -- ,pp.work_telephone
88 -- ,p2.email_address super_email
89 -- ,p2.work_telephone super_phone
90 ,p2.person_id super_id
91 ,asg2.assignment_id super_asg_id
92 -- ,ad.address_id
93 -- ,ad.address_line1
94 ,pp.object_version_number per_object_version_number
95 ,asg.assignment_id
96 ,asg.internal_address_line work_line
97 ,asg.organization_id
98 ,loc.address_line_1 work_line1
99 ,loc.town_or_city work_city
100 ,pph.PHONE_NUMBER work_telephone
101 ,pfax.PHONE_NUMBER work_fax
102 FROM per_all_people_f pp
103 ,per_all_assignments_f asg
104 ,per_all_people_f p2
105 ,per_all_assignments_f asg2
106 ,per_person_types ppt
107 ,per_person_type_usages_f ptu
108 -- ,per_addresses ad
109 ,hr_locations_all loc
110 ,hr_all_organization_units org
111 , per_phones pph
112 , per_phones pfax
113 WHERE pp.person_id = p_person_id
114 AND trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date
115 and asg.person_id = pp.person_id
116 --Modified for bug#5579345
117 --AND (asg.primary_flag = 'Y' Or ppt.system_person_type = 'APL') -- Added OR condition for 3885568
118 AND ( (asg.primary_flag = 'Y' AND ppt.system_person_type in ('EMP', 'CWK'))
119 Or (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
120 AND trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date
121 AND asg.assignment_type IN ('A','E','C')--Added for bug 9833327
122 AND ptu.person_id = pp.person_id
123 AND trunc(sysdate) between ptu.effective_start_Date and ptu.effective_end_date
124 and ppt.business_group_id = pp.business_group_id
125 and ptu.person_type_id = ppt.person_type_id
126 and p2.person_id(+) = asg.supervisor_id
127 AND trunc(sysdate) BETWEEN p2.effective_start_date(+) AND p2.effective_end_date(+)
128 and asg2.person_id(+) = asg.supervisor_id
129 AND asg2.primary_flag(+) = 'Y'
130 AND trunc(sysdate) BETWEEN asg2.effective_start_date(+) AND asg2.effective_end_date(+)
131 -- AND ad.person_id = pp.person_id
132 -- AND ad.primary_flag = 'Y'
133 -- AND trunc(sysdate)BETWEEN ad.date_from AND nvl(ad.date_to, trunc(sysdate))
134 and asg.organization_id = org.organization_id
135 and org.location_id = loc.location_id (+)
136 AND pph.PARENT_ID(+) = pp.PERSON_ID
137 AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
138 AND pph.PHONE_TYPE(+) = 'W1'
139 AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE)
140 AND pfax.PARENT_ID(+) = pp.PERSON_ID
141 AND pfax.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
142 AND pfax.PHONE_TYPE(+) = 'WF'
143 AND trunc(sysdate) BETWEEN NVL(pfax.DATE_FROM(+), SYSDATE) AND NVL(pfax.DATE_TO(+), SYSDATE)
144 order by asg.primary_flag desc; --Bug#6872547
145 --
146 -- Cursor to retrieve necessary information in order to save the enrollment
147
148 CURSOR csr_ext_lrnr_details (p_delegate_contact_id number) IS
149 select substrb( PARTY.person_last_name,1,50) LAST_NAME,
150 substrb( PARTY.person_first_name,1,40) FIRST_NAME,
151 party.person_pre_name_adjunct title,
152 ACCT_ROLE.cust_account_role_id CONTACT_ID,
153 ACCT_ROLE.cust_account_id CUSTOMER_ID
154
155 from HZ_CUST_ACCOUNT_ROLES acct_role,
156 HZ_PARTIES party,
157 HZ_RELATIONSHIPS rel,
158 HZ_ORG_CONTACTS org_cont,
159 HZ_PARTIES rel_party,
160 HZ_CUST_ACCOUNTS role_acct
161
162 where acct_role.party_id = rel.party_id
163 and acct_role.role_type = 'CONTACT'
164 and org_cont.party_relationship_id = rel.relationship_id
165 and rel.subject_id = party.party_id
166 and rel.party_id = rel_party.party_id
167 and rel.subject_table_name = 'HZ_PARTIES'
168 and rel.object_table_name = 'HZ_PARTIES'
169 and acct_role.cust_account_id = role_acct.cust_account_id
170 and role_acct.party_id = rel.object_id
171 and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
172
173 g_mesg_on_stack_exception EXCEPTION;
174 PRAGMA EXCEPTION_INIT(g_mesg_on_stack_exception, -20002);
175
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------------<ProcessSaveEnrollment>----------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This package is used for self service application to create enrollment
184 -- data when user enroll in the class.
185 --
186 -- Prerequisites:
187 --
188 -- In Parameters:
189 --
190 -- Post Success:
191 -- Enrollment data will be created.
192 --
193 -- Post Failure:
194 -- Status will be passed to the caller and the caller will raise a notification.
195 --
196 -- Developer Implementation Notes:
197 -- The attrbute in parameters should be modified as to the business process
198 -- requirements.
199 --
200 -- Access Status:
201 -- Internal Development Use Only.
202 --
203 -- {End Of Comments}
204 -- ----------------------------------------------------------------------------
205 PROCEDURE ProcessSaveEnrollment( p_event_id IN VARCHAR2
206 ,p_extra_information IN VARCHAR2 DEFAULT NULL
207 ,p_mode IN VARCHAR2
208 ,p_cost_centers IN VARCHAR2 DEFAULT NULL
209 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
210 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
211 ,p_business_group_name IN PER_BUSINESS_GROUPS.name%TYPE
212 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
213 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
214 ,p_delegate_contact_id IN NUMBER
215 ,p_booking_id out nocopy OTA_DELEGATE_BOOKINGS.Booking_id%type
216 , p_message_name out nocopy varchar2
217 ,p_tdb_information_category in varchar2 default null
218 ,p_tdb_information1 in varchar2 default null
219 ,p_tdb_information2 in varchar2 default null
220 ,p_tdb_information3 in varchar2 default null
221 ,p_tdb_information4 in varchar2 default null
222 ,p_tdb_information5 in varchar2 default null
223 ,p_tdb_information6 in varchar2 default null
224 ,p_tdb_information7 in varchar2 default null
225 ,p_tdb_information8 in varchar2 default null
226 ,p_tdb_information9 in varchar2 default null
227 ,p_tdb_information10 in varchar2 default null
228 ,p_tdb_information11 in varchar2 default null
229 ,p_tdb_information12 in varchar2 default null
230 ,p_tdb_information13 in varchar2 default null
231 ,p_tdb_information14 in varchar2 default null
232 ,p_tdb_information15 in varchar2 default null
233 ,p_tdb_information16 in varchar2 default null
234 ,p_tdb_information17 in varchar2 default null
235 ,p_tdb_information18 in varchar2 default null
236 ,p_tdb_information19 in varchar2 default null
237 ,p_tdb_information20 in varchar2 default null
238 ,p_booking_justification_id in varchar2 default null);
239
240 --
241 -- ----------------------------------------------------------------------------
242 -- |-----------------------------<Cancel_finance>-----------------------------|
243 -- ----------------------------------------------------------------------------
244 -- {Start Of Comments}
245 --
246 -- Description:
247 -- This package is used for self service application to cancel finance if
248 -- user re-enroll in the class.
249 --
250 -- Prerequisites:
251 --
252 -- In Parameters:
253 --
254 -- Post Success:
255 -- Finance will be canceled.
256 --
257 -- Post Failure:
258 -- Status will be passed to the caller and the caller will raise a notification.
259 --
260 -- Developer Implementation Notes:
261 -- The attrbute in parameters should be modified as to the business process
262 -- requirements.
263 --
264 -- Access Status:
265 -- Internal Development Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269
270 PROCEDURE cancel_finance(p_booking_id in number);
271
272
273 --
274 --
275 -- ----------------------------------------------------------------------------
276 -- |----------------------<Get_Booking_Status_For_Web >-----------------------|
277 -- ----------------------------------------------------------------------------
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- This package is used for self service application to create enrollment
282 -- data when user enroll in the class.
283 --
284 -- Prerequisites:
285 --
286 -- In Parameters:
287 --
288 -- Post Success:
289 -- Enrollment data will be created.
290 --
291 -- Post Failure:
292 -- Status will be passed to the caller and the caller will raise a notification.
293 --
294 -- Developer Implementation Notes:
295 -- The attrbute in parameters should be modified as to the business process
296 -- requirements.
297 --
298 -- Access Status:
299 -- Internal Development Use Only.
300 --
301 -- {End Of Comments}
302 -- ----------------------------------------------------------------------------
303
304
305 FUNCTION Get_Booking_Status_For_Web (p_web_booking_status_type VARCHAR2
306 ,p_business_group_id NUMBER)
307 RETURN OTA_BOOKING_STATUS_TYPES%ROWTYPE;
308
309 Procedure supervisor_exists ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
310 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
311 actid IN NUMBER,
312 funcmode IN VARCHAR2,
313 resultout OUT nocopy VARCHAR2 );
314
315 --
316 -- ----------------------------------------------------------------------------
317 -- |-----------------------------<Check_Cost_Center>--------------------------|
318 -- ----------------------------------------------------------------------------
319 -- {Start Of Comments}
320 --
321 -- Description:
322 -- This package is used for self service application to create enrollment
323 -- data when user enroll in the class.
324 --
325 -- Prerequisites:
326 --
327 -- In Parameters:
328 --
329 -- Post Success:
330 -- Enrollment data will be created.
331 --
332 -- Post Failure:
333 -- Status will be passed to the caller and the caller will raise a notification.
334 --
335 -- Developer Implementation Notes:
336 -- The attrbute in parameters should be modified as to the business process
337 -- requirements.
338 --
339 -- Access Status:
340 -- Internal Development Use Only.
341 --
342 -- {End Of Comments}
343 -- ----------------------------------------------------------------------------
344 Procedure check_cost_center
345 (p_person_id in number,
346 p_no_cost_center out nocopy number,
347 p_cost_alloc_keyflex_id out nocopy number,
348 p_business_group_id out nocopy number,
349 p_assignment_id out nocopy number,
350 p_organization_id out nocopy number,
351 p_cost_center out nocopy varchar2 );
352
353
354 --
355 -- ----------------------------------------------------------------------------
356 -- |----------------------<Get_Person_To_Enroll_Details >----------------------|
357 -- ----------------------------------------------------------------------------
358 -- {Start Of Comments}
359 --
360 -- Description:
361 -- This package is used for self service application to create enrollment
362 -- data when user enroll in the class.
363 --
364 -- Prerequisites:
365 --
366 -- In Parameters:
367 --
368 -- Post Success:
369 -- Enrollment data will be created.
370 --
371 -- Post Failure:
372 -- Status will be passed to the caller and the caller will raise a notification.
373 --
374 -- Developer Implementation Notes:
375 -- The attrbute in parameters should be modified as to the business process
376 -- requirements.
377 --
378 -- Access Status:
379 -- Internal Development Use Only.
380 --
381 -- {End Of Comments}
382 -- ----------------------------------------------------------------------------
383
384 FUNCTION Get_Person_To_Enroll_Details (p_person_id per_all_people_f.PERSON_ID%TYPE)
385 RETURN csr_person_to_enroll_details%ROWTYPE;
386
387 --
388 -- ----------------------------------------------------------------------------
389 -- |----------------------<Get_ext_lrnr_Details >----------------------|
390 -- ----------------------------------------------------------------------------
391 -- {Start Of Comments}
392 --
393 -- Description:
394 -- This package is used for self service application to create enrollment
395 -- data when user enroll in the class.
396 --
397 -- Prerequisites:
398 --
399 -- In Parameters:
400 --
401 -- Post Success:
402 -- Enrollment data will be created.
403 --
404 -- Post Failure:
405 -- Status will be passed to the caller and the caller will raise a notification.
406 --
407 -- Developer Implementation Notes:
408 -- The attrbute in parameters should be modified as to the business process
409 -- requirements.
410 --
411 -- Access Status:
412 -- Internal Development Use Only.
413 --
414 -- {End Of Comments}
415 -- ----------------------------------------------------------------------------
416
417 FUNCTION Get_ext_lrnr_Details (p_delegate_contact_id IN NUMBER)
418 RETURN csr_ext_lrnr_Details%ROWTYPE;
419 --
420 -- ----------------------------------------------------------------------------
421 -- |-------------------------------<Validate_Enrollment>----------------------|
422 -- ----------------------------------------------------------------------------
423 -- {Start Of Comments}
424 --
425 -- Description:
426 -- This package is used for self service application to create enrollment
427 -- data when user enroll in the class.
428 --
429 -- Prerequisites:
430 --
431 -- In Parameters:
432 --
433 -- Post Success:
434 -- Enrollment data will be created.
435 --
436 -- Post Failure:
437 -- Status will be passed to the caller and the caller will raise a notification.
438 --
439 -- Developer Implementation Notes:
440 -- The attrbute in parameters should be modified as to the business process
441 -- requirements.
442 --
443 -- Access Status:
444 -- Internal Development Use Only.
445 --
446 -- {End Of Comments}
447 -- ----------------------------------------------------------------------------
448
449 PROCEDURE Validate_enrollment(p_person_id IN per_all_people_f.PERSON_ID%TYPE,
450 p_delegate_contact_id IN NUMBER,
451 p_event_id IN VARCHAR2,
452 p_double_book OUT nocopy VARCHAR2);
453
454 -- |--------------------------------------------------------------------------|
455 -- |--< Chk_Event_Cancelled_for_Person >--------------------------------------|
456 -- |--------------------------------------------------------------------------|
457 --
458 -- {Start Of Comments}
459 --
460 -- Description:
461 -- This package is used for self service application to create enrollment
462 -- data when user enroll in the class.
463 --
464 -- Prerequisites:
465 --
466 -- In Parameters:
467 --
468 -- Post Success:
469 -- Enrollment data will be created.
470 --
471 -- Post Failure:
472 -- Status will be passed to the caller and the caller will raise a notification.
473 --
474 -- Developer Implementation Notes:
475 -- The attrbute in parameters should be modified as to the business process
476 -- requirements.
477 --
478 -- Access Status:
479 -- Internal Development Use Only.
480 --
481 -- {End Of Comments}
482 -- ----------------------------------------------------------------------------
483 FUNCTION Chk_Event_Cancelled_for_Person (p_event_id IN NUMBER
484 ,p_delegate_person_id IN NUMBER
485 ,p_delegate_contact_id IN NUMBER
486 ,p_booking_id OUT nocopy NUMBER)
487 RETURN BOOLEAN;
488
489 procedure Chk_booking_clash (p_event_id IN NUMBER
490 ,p_person_id IN NUMBER
491 ,p_delegate_contact_id IN NUMBER
492 ,p_booking_Clash OUT nocopy varchar2)
493 ;
494 --|--------------------------------------------------------------------------|
495 --|--< CHK_FOR_SECURE_EVT >-------------------------------------------|
496 --|--------------------------------------------------------------------------|
497
498 -- {Start Of Comments}
499 --
500 -- Description:
501 -- Called by dynamic sql to determine whether event is secure and it is OK to show the delegate
502 -- the event in question. Only the events that they can enroll onto are
503 -- shown.
504 -- Prerequisites:
505 -- none
506 --
507 -- Post Success:
508 --
509 -- Post Failure:
510 -- an exception is raised
511 --
512 -- Access Status:
513 -- Public
514 --
515 -- {End Of Comments}
516 --
517 ----------------------------------------------------------------------------
518
519 FUNCTION CHK_FOR_SECURE_EVT (
520 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
521 ,p_delegate_contact_id IN NUMBER
522 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
523 RETURN VARCHAR2;
524 --PRAGMA RESTRICT_REFERENCES(CHK_FOR_SECURE_EVT, WNDS, WNPS);
525
526
527 --|--------------------------------------------------------------------------|
528 --|--< CHK_DELEGATE_OK_FOR_EVENT>-----------------------------------------|
529 --|--------------------------------------------------------------------------|
530
531 -- {Start Of Comments}
532 --
533 -- Description:
534 -- Called by dynamic sql to determine whether it is OK to show the delegate
535 -- the event in question. Only the events that they can enroll onto are
536 -- shown.
537 -- Prerequisites:
538 -- none
539 --
540 -- Post Success:
541 --
542 -- Post Failure:
543 -- an exception is raised
544 --
545 -- Access Status:
546 -- Public
547 --
548 -- {End Of Comments}
549 --
550 ----------------------------------------------------------------------------
551
552 FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
553 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
554 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
555 , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
556 default null)
557 RETURN VARCHAR2;
558 --Modified for bug#5032859
559 --PRAGMA RESTRICT_REFERENCES(CHK_DELEGATE_OK_FOR_EVENT, WNDS, WNPS);
560
561
562 --
563 -- ----------------------------------------------------------------------------
564 -- |--------------------------------< Get_Booking_OVN >-----------------------|
565 -- ----------------------------------------------------------------------------
566 -- {Start Of Comments}
567 --
568 -- Description:
569 -- This function will be used to return object version number of an enrollment.
570 --
571 -- IN
572 -- p_booking_id
573 --
574 -- OUT
575 -- p_ovn
576 --
577 -- Post Failure:
578 -- None.
579 --
580 -- Access Status:
581 -- Public.
582 --
583 -- {End Of Comments}
584 ----------------------------------------------------------------------------
585
586 FUNCTION Get_Booking_OVN (p_booking_id IN NUMBER)
587 RETURN NUMBER;
588
589
590 --
591 --|--------------------------------------------------------------------------|
592 --|--< CHK_FOR_RESTRICTED_EVT >-------------------------------------------|
593 --|--------------------------------------------------------------------------|
594
595 -- {Start Of Comments}
596 --
597 -- Description:
598 -- Called by dynamic sql to determine whether event is restricted and it is OK to show the delegate
599 -- the event in question. Only the events that they can enroll onto are
600 -- shown.
601 -- Prerequisites:
602 -- none
603 --
604 -- Post Success:
605 --
606 -- Post Failure:
607 -- an exception is raised
608 --
609 -- Access Status:
610 -- Public
611 --
612 -- {End Of Comments}
613 --
614 ----------------------------------------------------------------------------
615
616 FUNCTION CHK_FOR_RESTRICTED_EVT (
617 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE,
618 p_delegate_contact_id IN NUMBER)
619 RETURN VARCHAR2;
620 --PRAGMA RESTRICT_REFERENCES(CHK_FOR_RESTRICTED_EVT, WNDS, WNPS);
621
622
623
624 -- ----------------------------------------------------------------------------
625 -- |-----------------------------< cancel_enrollment>-------------------------|
626 -- ----------------------------------------------------------------------------
627 -- {Start Of Comments}
628 --
629 -- Description:
630 -- This procedure will be called from the View Enrollment Details Screen on pressing 'Submit'.
631 --
632 -- This procedure will be used to call the cancel the enrollment Id passed in and
633 -- update the Enrollment with the Cancellation details.
634 --
635 -- Pre Conditions:
636 -- None.
637 --
638 -- In Arguments:
639 -- p_booking_id
640 -- p_event_id
641 -- p_booking_status_type_id
642 -- p_cancel_reason
643 -- p_waitlist_size
644 --
645 -- Out Arguments:
646 -- x_return_status
647 --
648 -- Post Success:
649 -- Processing continues.
650 --
651 --
652 -- Post Failure:
653 -- None.
654 --
655 -- Access Status:
656 -- Public.
657 --
658 -- {End Of Comments}
659 ----------------------------------------------------------------------------
660 PROCEDURE cancel_enrollment
661 (x_return_status OUT NOCOPY VARCHAR2,
662 p_booking_id IN NUMBER,
663 p_event_id IN NUMBER,
664 p_booking_status_type_id IN NUMBER,
665 p_cancel_reason IN VARCHAR2,
666 p_waitlist_size IN NUMBER,
667 p_tdb_information_category IN VARCHAR2 DEFAULT NULL,
668 p_tdb_information1 IN VARCHAR2 DEFAULT NULL,
669 p_tdb_information2 IN VARCHAR2 DEFAULT NULL,
670 p_tdb_information3 IN VARCHAR2 DEFAULT NULL,
671 p_tdb_information4 IN VARCHAR2 DEFAULT NULL,
672 p_tdb_information5 IN VARCHAR2 DEFAULT NULL,
673 p_tdb_information6 IN VARCHAR2 DEFAULT NULL,
674 p_tdb_information7 IN VARCHAR2 DEFAULT NULL,
675 p_tdb_information8 IN VARCHAR2 DEFAULT NULL,
676 p_tdb_information9 IN VARCHAR2 DEFAULT NULL,
677 p_tdb_information10 IN VARCHAR2 DEFAULT NULL,
678 p_tdb_information11 IN VARCHAR2 DEFAULT NULL,
679 p_tdb_information12 IN VARCHAR2 DEFAULT NULL,
680 p_tdb_information13 IN VARCHAR2 DEFAULT NULL,
681 p_tdb_information14 IN VARCHAR2 DEFAULT NULL,
682 p_tdb_information15 IN VARCHAR2 DEFAULT NULL,
683 p_tdb_information16 IN VARCHAR2 DEFAULT NULL,
684 p_tdb_information17 IN VARCHAR2 DEFAULT NULL,
685 p_tdb_information18 IN VARCHAR2 DEFAULT NULL,
686 p_tdb_information19 IN VARCHAR2 DEFAULT NULL,
687 p_tdb_information20 IN VARCHAR2 DEFAULT NULL,
688 p_failure_reason IN varchar2 DEFAULT NULL,
689 p_attendance_result IN varchar2 DEFAULT NULL,
690 p_successful_attendance_flag IN varchar2 DEFAULT NULL,
691 p_comments in varchar2 DEFAULT NULL );
692
693 Procedure CHK_UNIQUE_FUNC(p_function_name in varchar2,
694 p_user_id in number,
695 result out nocopy varchar2);
696
697 --|--------------------------------------------------------------------------|
698 --|--< CHK_VALID_ACTIVITY >-------------------------------------------|
699 --|--------------------------------------------------------------------------|
700
701 -- {Start Of Comments}
702 --
703 -- Description:
704 -- Called before displaying the Course details page to find out whether
705 -- a) Activity is valid else return 'I' (Invalid)
706 -- b) Activity is open else return 'E' (Expired)
707 -- c) Activity has offerings attached to it else return 'NO'(No Offerings)
708 -- Prerequisites:
709 -- none
710 --
711 -- Post Success:
712 --
713 -- Post Failure:
714 -- an exception is raised
715 --
716 -- Access Status:
717 -- Public
718 --
719 -- {End Of Comments}
720 --
721 ----------------------------------------------------------------------------
722
723
724
725 FUNCTION CHK_VALID_ACTIVITY(
726 p_activity_id IN OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type
727 )
728 RETURN VARCHAR2;
729
730
731 --|--------------------------------------------------------------------------|
732 --|--< CHK_VALID_OFFERING >-------------------------------------------|
733 --|--------------------------------------------------------------------------|
734
735 -- {Start Of Comments}
736 --
737 -- Description:
738 -- Called before displaying the Offering details page to find out whether
739 -- a) Offering is valid else return 'I' (Invalid)
740 -- b) Offering is open else return 'E' (Expired)
741 -- c) Offering has events attached to it else return 'NE'(No Events)
742 -- Prerequisites:
743 -- none
744 --
745 -- Post Success:
746 --
747 -- Post Failure:
748 -- an exception is raised
749 --
750 -- Access Status:
751 -- Public
752 --
753 -- {End Of Comments}
754 --
755 ----------------------------------------------------------------------------
756
757
758
759 FUNCTION CHK_VALID_OFFERING(
760 p_offering_id IN OTA_OFFERINGS.offering_id%type
761 )
762 RETURN VARCHAR2;
763
764
765 --|--------------------------------------------------------------------------|
766 --|--< CHK_VALID_EVENT >-------------------------------------------|
767 --|--------------------------------------------------------------------------|
768
769 -- {Start Of Comments}
770 --
771 -- Description:
772 -- Called before displaying the Class details page to find out whether
773 -- a) Event is valid else return 'I' (Invalid)
774 -- b) Event is open else return 'E' (Expired) or 'C' (closed)
775 -- c) Enrollment for the event is still open else return 'EC' (enrollment Closed) or
776 -- 'ENS' (enrollment not yet started)
777 -- Prerequisites:
778 -- none
779 --
780 -- Post Success:
781 --
782 -- Post Failure:
783 -- an exception is raised
784 --
785 -- Access Status:
786 -- Public
787 --
788 -- {End Of Comments}
789 --
790 ----------------------------------------------------------------------------
791
792
793 FUNCTION CHK_VALID_EVENT(
794 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
795 )
796 RETURN VARCHAR2;
797
798 PROCEDURE get_wf_attr_for_cancel_ntf
799 (p_event_id IN ota_events.event_id%TYPE,
800 p_person_id IN number,
801 p_supervisor_username OUT NOCOPY fnd_user.user_name%TYPE,
802 p_supervisor_full_name OUT NOCOPY per_all_people_f.full_name%TYPE,
803 p_supervisor_id OUT NOCOPY per_all_people_f.person_id%Type,
804 p_current_person_name OUT NOCOPY VARCHAR2,
805 p_current_username OUT NOCOPY VARCHAR2,
806 p_person_displayname OUT NOCOPY per_all_people_f.full_name%TYPE,
807 p_creator_displayname OUT NOCOPY per_all_people_f.full_name%TYPE,
808 x_return_status OUT NOCOPY VARCHAR2);
809
810 --
811 -- ----------------------------------------------------------------------------
812 -- |-------------------------< get_booking_status_comments >----------------------------|
813 -- ----------------------------------------------------------------------------
814 --
815 -- Description: get the comments from the booking history table for the
816 -- booking_id and booking_status_type_id passed in as parameters.
817 --
818 --
819 FUNCTION get_booking_status_comments(p_booking_id IN NUMBER,
820 p_booking_status_type_id IN NUMBER) RETURN VARCHAR2;
821 --
822 -- ---------------------------------------------------------------------------------|
823 -- |-------------------------< getCancellationStatusId >----------------------------|
824 -- ---------------------------------------------------------------------------------|
825 --
826 -- Description: Retrieves the default cancellation enrollment status id
827 --
828 --
829 Function getCancellationStatusId
830 RETURN ota_booking_status_types.booking_status_type_id%type;
831
832 end ota_learner_enroll_ss ;
[Home] [Help]