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