1 package OTA_EL_ENROLL_SS AUTHID CURRENT_USER as
2 /* $Header: otelenss.pkh 120.1 2005/09/16 16:44:11 hdshah 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 -- CSR_BOOKING_STATUS_ID retrieves a single booking_status_type_id for
9 -- a particular BG of the reqired type.
10 -- It selects using the following priority :-
11 -- 1) The name is like 'W:%' -- This indicates it is seeded for the web
12 -- 2) The default flag is set
13 -- 3) The first row retrieved that is of the type required.
14 --
15 CURSOR csr_booking_status_id (p_business_group_id IN NUMBER
16 ,p_web_booking_status_type IN VARCHAR2) IS
17 SELECT bst.BOOKING_STATUS_TYPE_ID,bst.BUSINESS_GROUP_ID,bst.ACTIVE_FLAG,bst.DEFAULT_FLAG,bst.PLACE_USED_FLAG,
18 bstt.NAME,bst.OBJECT_VERSION_NUMBER,bst.TYPE,bst.COMMENTS,bstt.DESCRIPTION,bst.LAST_UPDATE_DATE,
19 bst.LAST_UPDATED_BY,bst.LAST_UPDATE_LOGIN,bst.CREATED_BY,bst.CREATION_DATE,
20 bst.BST_INFORMATION_CATEGORY,bst.BST_INFORMATION1,bst.BST_INFORMATION2,
21 bst.BST_INFORMATION3,bst.BST_INFORMATION4,bst.BST_INFORMATION5,
22 bst.BST_INFORMATION6,bst.BST_INFORMATION7,bst.BST_INFORMATION8,
23 bst.BST_INFORMATION9,bst.BST_INFORMATION10,bst.BST_INFORMATION11,
24 bst.BST_INFORMATION12,bst.BST_INFORMATION13,bst.BST_INFORMATION14,
25 bst.BST_INFORMATION15,bst.BST_INFORMATION16,bst.BST_INFORMATION17,
26 bst.BST_INFORMATION18,bst.BST_INFORMATION19,bst.BST_INFORMATION20
27 FROM ota_booking_status_types bst, ota_booking_status_types_tl bstt
28 WHERE bst.business_group_id = p_business_group_id
29 AND bst.booking_status_type_id = bstt.booking_status_type_id
30 AND bstt.language=userenv('LANG')
31 AND rownum=1
32 AND bst.type = DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
33 , 'WAITLISTED','W'
34 , 'CANCELLED', 'C'
35 , 'ATTENDED' , 'A'
36 , 'PLACED' , 'P')
37 -- The name is like W: ( highest priority choice)
38 AND ( (bstt.name like 'W:%')
39 -- There are no names like W:, so a defaulted required status is
40 -- the second choice.
41 OR ( NOT EXISTS (SELECT 1
42 FROM ota_booking_status_types bst1, ota_booking_status_types_tl bstt1
43 WHERE bst1.business_group_id = p_business_group_id
44 AND bst1.booking_status_type_id = bstt1.booking_status_type_id
45 AND bstt1.language=userenv('LANG')
46 AND bst1.type =
47 DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
48 , 'WAITLISTED','W'
49 , 'CANCELLED', 'C'
50 , 'ATTENDED' , 'A'
51 , 'PLACED' , 'P')
52 AND bstt1.name like 'W:%')
53 AND ( (bst.default_flag = 'Y')
54 -- If there are no names like 'W:%' and no defaulted status of type
55 -- required, then select one that is of type required
56 OR NOT EXISTS (SELECT 1
57 FROM ota_booking_status_types
58 WHERE business_group_id = p_business_group_id
59 AND type =
60 DECODE(p_web_booking_status_type, 'REQUESTED', 'R'
61 , 'WAITLISTED','W'
62 , 'CANCELLED', 'C'
63 , 'ATTENDED' , 'A'
64 , 'PLACED' , 'P')
65 AND default_flag ='Y')
66 )
67 )
68 );
69
70 --
71 -- Cursor to retrieve necessary information in order to save the enrollment
72 -- NOTE : Removed the address section as it appears that a person doesn't
73 -- need to have a primary address (however, I've only commented it out
74 -- just incase)
75
76 CURSOR csr_person_to_enroll_details (p_person_id number) IS
77 SELECT pp.last_name
78 ,pp.first_name
79 ,pp.full_name
80 ,pp.business_group_id
81 ,pp.email_address
82 ,pp.work_telephone
83 -- ,p2.email_address super_email
84 -- ,p2.work_telephone super_phone
85 ,p2.person_id super_id
86 ,asg2.assignment_id super_asg_id
87 -- ,ad.address_id
88 -- ,ad.address_line1
89 ,pp.object_version_number per_object_version_number
90 ,asg.assignment_id
91 ,asg.internal_address_line work_line
92 ,asg.organization_id
93 ,loc.address_line_1 work_line1
94 ,loc.town_or_city work_city
95 FROM per_all_people_f pp
96 ,per_all_assignments_f asg
97 ,per_all_people_f p2
98 ,per_all_assignments_f asg2
99 -- ,per_addresses ad
100 ,hr_locations loc
101 ,hr_all_organization_units org
102 WHERE pp.person_id = p_person_id
103 AND trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date
104 and asg.person_id = pp.person_id
105 AND asg.primary_flag = 'Y'
106 AND trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date
107 and p2.person_id(+) = asg.supervisor_id
108 AND trunc(sysdate) BETWEEN p2.effective_start_date(+) AND p2.effective_end_date(+)
109 and asg2.person_id(+) = asg.supervisor_id
110 AND asg2.primary_flag(+) = 'Y'
111 AND trunc(sysdate) BETWEEN asg2.effective_start_date(+) AND asg2.effective_end_date(+)
112 -- AND ad.person_id = pp.person_id
113 -- AND ad.primary_flag = 'Y'
114 -- AND trunc(sysdate)BETWEEN ad.date_from AND nvl(ad.date_to, trunc(sysdate))
115 and asg.organization_id = org.organization_id
116 and org.location_id = loc.location_id (+);
117
118
119 g_mesg_on_stack_exception EXCEPTION;
120 PRAGMA EXCEPTION_INIT(g_mesg_on_stack_exception, -20002);
121
122 --
123 -- ----------------------------------------------------------------------------
124 -- |-----------------------------<ProcessSaveEnrollment>----------------------|
125 -- ----------------------------------------------------------------------------
126 -- {Start Of Comments}
127 --
128 -- Description:
129 -- This package is used for self service application to create enrollment
130 -- data when user enroll in the class.
131 --
132 -- Prerequisites:
133 --
134 -- In Parameters:
135 --
136 -- Post Success:
137 -- Enrollment data will be created.
138 --
139 -- Post Failure:
140 -- Status will be passed to the caller and the caller will raise a notification.
141 --
142 -- Developer Implementation Notes:
143 -- The attrbute in parameters should be modified as to the business process
144 -- requirements.
145 --
146 -- Access Status:
147 -- Internal Development Use Only.
148 --
149 -- {End Of Comments}
150 -- ----------------------------------------------------------------------------
151 PROCEDURE ProcessSaveEnrollment( p_event_id IN VARCHAR2
152 ,p_extra_information IN VARCHAR2 DEFAULT NULL
153 ,p_mode IN VARCHAR2
154 ,p_cost_centers IN VARCHAR2 DEFAULT NULL
155 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
156 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
157 ,p_business_group_name IN PER_BUSINESS_GROUPS.name%TYPE
158 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
159 ,p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
160 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
161 ,p_booking_id out nocopy OTA_DELEGATE_BOOKINGS.Booking_id%type
162 , p_message_name out nocopy varchar2
163 ,p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
164 ,p_tdb_information_category in varchar2 default null
165 ,p_tdb_information1 in varchar2 default null
166 ,p_tdb_information2 in varchar2 default null
167 ,p_tdb_information3 in varchar2 default null
168 ,p_tdb_information4 in varchar2 default null
169 ,p_tdb_information5 in varchar2 default null
170 ,p_tdb_information6 in varchar2 default null
171 ,p_tdb_information7 in varchar2 default null
172 ,p_tdb_information8 in varchar2 default null
173 ,p_tdb_information9 in varchar2 default null
174 ,p_tdb_information10 in varchar2 default null
175 ,p_tdb_information11 in varchar2 default null
176 ,p_tdb_information12 in varchar2 default null
177 ,p_tdb_information13 in varchar2 default null
178 ,p_tdb_information14 in varchar2 default null
179 ,p_tdb_information15 in varchar2 default null
180 ,p_tdb_information16 in varchar2 default null
181 ,p_tdb_information17 in varchar2 default null
182 ,p_tdb_information18 in varchar2 default null
183 ,p_tdb_information19 in varchar2 default null
184 ,p_tdb_information20 in varchar2 default null);
185
186 --
187 -- ----------------------------------------------------------------------------
188 -- |-----------------------------<Cancel_finance>-----------------------------|
189 -- ----------------------------------------------------------------------------
190 -- {Start Of Comments}
191 --
192 -- Description:
193 -- This package is used for self service application to cancel finance if
194 -- user re-enroll in the class.
195 --
196 -- Prerequisites:
197 --
198 -- In Parameters:
199 --
200 -- Post Success:
201 -- Finance will be canceled.
202 --
203 -- Post Failure:
204 -- Status will be passed to the caller and the caller will raise a notification.
205 --
206 -- Developer Implementation Notes:
207 -- The attrbute in parameters should be modified as to the business process
208 -- requirements.
209 --
210 -- Access Status:
211 -- Internal Development Use Only.
212 --
213 -- {End Of Comments}
214 -- ----------------------------------------------------------------------------
215
216 PROCEDURE cancel_finance(p_booking_id in number);
217
218
219 --
220 --
221 -- ----------------------------------------------------------------------------
222 -- |----------------------<Get_Booking_Status_For_Web >-----------------------|
223 -- ----------------------------------------------------------------------------
224 -- {Start Of Comments}
225 --
226 -- Description:
227 -- This package is used for self service application to create enrollment
228 -- data when user enroll in the class.
229 --
230 -- Prerequisites:
231 --
232 -- In Parameters:
233 --
234 -- Post Success:
235 -- Enrollment data will be created.
236 --
237 -- Post Failure:
238 -- Status will be passed to the caller and the caller will raise a notification.
239 --
240 -- Developer Implementation Notes:
241 -- The attrbute in parameters should be modified as to the business process
242 -- requirements.
243 --
244 -- Access Status:
245 -- Internal Development Use Only.
246 --
247 -- {End Of Comments}
248 -- ----------------------------------------------------------------------------
249
250
251 FUNCTION Get_Booking_Status_For_Web (p_web_booking_status_type VARCHAR2
252 ,p_business_group_id NUMBER)
253 RETURN OTA_BOOKING_STATUS_TYPES%ROWTYPE;
254
255 --
256 -- ----------------------------------------------------------------------------
257 -- |-----------------------------<Check_Cost_Center>--------------------------|
258 -- ----------------------------------------------------------------------------
259 -- {Start Of Comments}
260 --
261 -- Description:
262 -- This package is used for self service application to create enrollment
263 -- data when user enroll in the class.
264 --
265 -- Prerequisites:
266 --
267 -- In Parameters:
268 --
269 -- Post Success:
270 -- Enrollment data will be created.
271 --
272 -- Post Failure:
273 -- Status will be passed to the caller and the caller will raise a notification.
274 --
275 -- Developer Implementation Notes:
276 -- The attrbute in parameters should be modified as to the business process
277 -- requirements.
278 --
279 -- Access Status:
280 -- Internal Development Use Only.
281 --
282 -- {End Of Comments}
283 -- ----------------------------------------------------------------------------
284 Procedure check_cost_center
285 (p_person_id in number,
286 p_no_cost_center out nocopy number,
287 p_cost_alloc_keyflex_id out nocopy number,
288 p_business_group_id out nocopy number,
289 p_assignment_id out nocopy number,
290 p_organization_id out nocopy number,
291 p_cost_center out nocopy varchar2 );
292 --
293 -- ----------------------------------------------------------------------------
294 -- |----------------------<Create_enroll_wf_process>--------------------------|
295 -- ----------------------------------------------------------------------------
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- This package is used for self service application to create enrollment
300 -- data when user enroll in the class.
301 --
302 -- Prerequisites:
303 --
304 -- In Parameters:
305 --
306 -- Post Success:
307 -- Enrollment data will be created.
308 --
309 -- Post Failure:
310 -- Status will be passed to the caller and the caller will raise a notification.
311 --
312 -- Developer Implementation Notes:
313 -- The attrbute in parameters should be modified as to the business process
314 -- requirements.
315 --
316 -- Access Status:
317 -- Internal Development Use Only.
318 --
319 -- {End Of Comments}
320 -- ----------------------------------------------------------------------------
321 Procedure create_enroll_wf_process
322 (p_process in wf_process_activities.process_name%type,
323 p_itemtype in wf_items.item_type%type,
324 p_person_id in number default null,
325 p_called_from in varchar2 default null,
326 p_itemkey out nocopy wf_items.item_key%type
327 );
328
329
330 --
331 -- ----------------------------------------------------------------------------
332 -- |----------------------<Get_Person_To_Enroll_Details >----------------------|
333 -- ----------------------------------------------------------------------------
334 -- {Start Of Comments}
335 --
336 -- Description:
337 -- This package is used for self service application to create enrollment
338 -- data when user enroll in the class.
339 --
340 -- Prerequisites:
341 --
342 -- In Parameters:
343 --
344 -- Post Success:
345 -- Enrollment data will be created.
346 --
347 -- Post Failure:
348 -- Status will be passed to the caller and the caller will raise a notification.
349 --
350 -- Developer Implementation Notes:
351 -- The attrbute in parameters should be modified as to the business process
352 -- requirements.
353 --
354 -- Access Status:
355 -- Internal Development Use Only.
356 --
357 -- {End Of Comments}
358 -- ----------------------------------------------------------------------------
359
360 FUNCTION Get_Person_To_Enroll_Details (p_person_id per_all_people_f.PERSON_ID%TYPE)
361 RETURN csr_person_to_enroll_details%ROWTYPE;
362
363
364 --
365 -- ----------------------------------------------------------------------------
366 -- |-------------------------------<Validate_Enrollment>----------------------|
367 -- ----------------------------------------------------------------------------
368 -- {Start Of Comments}
369 --
370 -- Description:
371 -- This package is used for self service application to create enrollment
372 -- data when user enroll in the class.
373 --
374 -- Prerequisites:
375 --
376 -- In Parameters:
377 --
378 -- Post Success:
379 -- Enrollment data will be created.
380 --
381 -- Post Failure:
382 -- Status will be passed to the caller and the caller will raise a notification.
383 --
384 -- Developer Implementation Notes:
385 -- The attrbute in parameters should be modified as to the business process
386 -- requirements.
387 --
388 -- Access Status:
389 -- Internal Development Use Only.
390 --
391 -- {End Of Comments}
392 -- ----------------------------------------------------------------------------
393
394 PROCEDURE Validate_enrollment(p_person_id IN per_all_people_f.PERSON_ID%TYPE,
395 p_event_id IN VARCHAR2,
396 p_double_book OUT NOCOPY VARCHAR2);
397
398 -- |--------------------------------------------------------------------------|
399 -- |--< Chk_Event_Cancelled_for_Person >--------------------------------------|
400 -- |--------------------------------------------------------------------------|
401 --
402 -- {Start Of Comments}
403 --
404 -- Description:
405 -- This package is used for self service application to create enrollment
406 -- data when user enroll in the class.
407 --
408 -- Prerequisites:
409 --
410 -- In Parameters:
411 --
412 -- Post Success:
413 -- Enrollment data will be created.
414 --
415 -- Post Failure:
416 -- Status will be passed to the caller and the caller will raise a notification.
417 --
418 -- Developer Implementation Notes:
419 -- The attrbute in parameters should be modified as to the business process
420 -- requirements.
421 --
422 -- Access Status:
423 -- Internal Development Use Only.
424 --
425 -- {End Of Comments}
426 -- ----------------------------------------------------------------------------
427 FUNCTION Chk_Event_Cancelled_for_Person (p_event_id IN NUMBER
428 ,p_delegate_person_id IN NUMBER
429 ,p_booking_id OUT NOCOPY NUMBER)
430 RETURN BOOLEAN;
431
432 procedure Chk_booking_clash (p_event_id IN NUMBER
433 ,p_person_id IN NUMBER
434 ,p_booking_Clash OUT NOCOPY varchar2)
435 ;
436 --|--------------------------------------------------------------------------|
437 --|--< CHK_FOR_SECURE_EVT >-------------------------------------------|
438 --|--------------------------------------------------------------------------|
439
440 -- {Start Of Comments}
441 --
442 -- Description:
443 -- Called by dynamic sql to determine whether event is secure and it is OK to show the delegate
444 -- the event in question. Only the events that they can enroll onto are
445 -- shown.
446 -- Prerequisites:
447 -- none
448 --
449 -- Post Success:
450 --
451 -- Post Failure:
452 -- an exception is raised
453 --
454 -- Access Status:
455 -- Public
456 --
457 -- {End Of Comments}
458 --
459 ----------------------------------------------------------------------------
460
461 FUNCTION CHK_FOR_SECURE_EVT (
462 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
463 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
464 RETURN VARCHAR2;
465 PRAGMA RESTRICT_REFERENCES(CHK_FOR_SECURE_EVT, WNDS, WNPS);
466 --|--------------------------------------------------------------------------|
467 --|--< CHK_DELEGATE_OK_FOR_EVENT>-----------------------------------------|
468 --|--------------------------------------------------------------------------|
469
470 -- {Start Of Comments}
471 --
472 -- Description:
473 -- Called by dynamic sql to determine whether it is OK to show the delegate
474 -- the event in question. Only the events that they can enroll onto are
475 -- shown.
476 -- Prerequisites:
477 -- none
478 --
479 -- Post Success:
480 --
481 -- Post Failure:
482 -- an exception is raised
483 --
484 -- Access Status:
485 -- Public
486 --
487 -- {End Of Comments}
488 --
489 ----------------------------------------------------------------------------
490
491 FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
492 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
493 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
494 , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
495 default null)
496 RETURN VARCHAR2;
497 PRAGMA RESTRICT_REFERENCES(CHK_DELEGATE_OK_FOR_EVENT, WNDS, WNPS);
498
499 --|--------------------------------------------------------------------------|
500 --|--< GET_CURRENT_PERSON_ID>-----------------------------------------|
501 --|--------------------------------------------------------------------------|
502
503 -- {Start Of Comments}
504 --
505 -- Description:
506 -- Called by dynamic sql to determine whether it is OK to show the delegate
507 -- the event in question. Only the events that they can enroll onto are
508 -- shown.
509 -- Prerequisites:
510 -- none
511 --
512 -- Post Success:
513 --
514 -- Post Failure:
515 -- an exception is raised
516 --
517 -- Access Status:
518 -- Public
519 --
520 -- {End Of Comments}
521 --
522 ----------------------------------------------------------------------------
523
524 FUNCTION Get_Current_Person_ID
525 (p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
526 ,p_item_key IN WF_ITEMS.ITEM_KEY%TYPE)
527 RETURN NUMBER;
528
529 -- ----------------------------------------------------------------------------
530 -- |---------------------< CHECK_ENROLLMENT_CREATION>----------------------|
531 -- ----------------------------------------------------------------------------
532 -- {Start Of Comments}
533 --
534 -- Description:
535 -- This procedure will be a concurrent process which run in the background.
536 --
537 -- This procedure will only be used for OTA and OM integration. Basically this
538 -- procedure will select all delegate booking data that has daemon_flag='Y' and
539 -- Daemon_type is not nul. If the enrollment got canceled and there is a
540 -- waitlisted student then the automatic waitlist processing will be called.
541 --
542 -- Pre Conditions:
543 -- None.
544 --
545 -- IN
546 -- itemtype - type of the current item
547 -- itemkey - key of the current item
548 -- actid - process activity instance id
549 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
550 -- OUT
551 -- result
552 -- - COMPLETE[:<result>]
553 -- activity has completed with the indicated result
554 -- - WAITING
555 -- activity is waiting for additional transitions
556 -- - DEFERED
557 -- execution should be defered to background
558 -- - NOTIFIED[:<notification_id>:<assigned_user>]
559 -- activity has notified an external entity that this
560 -- step must be performed. A call to wf_engine.CompleteActivty
561 -- will signal when this step is complete. Optional
562 -- return of notification ID and assigned user.
563 -- - ERROR[:<error_code>]
564 -- function encountered an err--
565 --
566 -- Post Success:
567 -- Processing continues.
568 --
569 --
570 -- Post Failure:
571 -- None.
572 --
573 -- Access Status:
574 -- Public.
575 --
576 -- {End Of Comments}
577 ----------------------------------------------------------------------------
578 PROCEDURE CHECK_ENROLLMENT_CREATION(
579 Itemtype IN VARCHAR2
580 ,Itemkey IN VARCHAR2
581 ,actid IN NUMBER
582 ,funcmode IN VARCHAR2
583 ,resultout OUT NOCOPY VARCHAR2
584 );
585
586 --
587 -- ----------------------------------------------------------------------------
588 -- |--------------------------------< CHECK_WF_STATUS>-------------------------|
589 -- ----------------------------------------------------------------------------
590 -- {Start Of Comments}
591 --
592 -- Description:
593 -- This function will be a used to check the workflow status of Order Line.
594 --
595 -- IN
596 -- p_line_id
597 --
598 -- OUT
599 -- p_exist
600 --
601 -- Post Failure:
602 -- None.
603 --
604 -- Access Status:
605 -- Public.
606 --
607 -- {End Of Comments}
608 ----------------------------------------------------------------------------
609
610 FUNCTION Check_wf_Status (
611 p_item_key NUMBER,
612 p_activity varchar2,
613 p_item_type VARCHAR2
614 ) RETURN Boolean;
615
616 --
617 -- ----------------------------------------
618 -- Workflow Cross Charges Notifications
619 -- ----------------------------------------
620 --
621 PROCEDURE Cross_Charges_Notifications ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
622 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
623 actid IN NUMBER,
624 funcmode IN VARCHAR2,
625 resultout OUT NOCOPY VARCHAR2 );
626
627
628 PROCEDURE APPROVED ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
629 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
630 actid IN NUMBER,
631 funcmode IN VARCHAR2,
632 resultout OUT NOCOPY VARCHAR2 );
633
634 --|--------------------------------------------------------------------------|
635 --|--< CHK_FOR_RESTRICTED_EVT >-------------------------------------------|
636 --|--------------------------------------------------------------------------|
637
638 -- {Start Of Comments}
639 --
640 -- Description:
641 -- Called by dynamic sql to determine whether event is restricted and it is OK to show the delegate
642 -- the event in question. Only the events that they can enroll onto are
643 -- shown.
644 -- Prerequisites:
645 -- none
646 --
647 -- Post Success:
648 --
649 -- Post Failure:
650 -- an exception is raised
651 --
652 -- Access Status:
653 -- Public
654 --
655 -- {End Of Comments}
656 --
657 ----------------------------------------------------------------------------
658
659 FUNCTION CHK_FOR_RESTRICTED_EVT (
660 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
661 RETURN VARCHAR2;
662 PRAGMA RESTRICT_REFERENCES(CHK_FOR_RESTRICTED_EVT, WNDS, WNPS);
663
664 --|--------------------------------------------------------------------------|
665 --|--< CHK_VALID_ACTIVITY >-------------------------------------------|
666 --|--------------------------------------------------------------------------|
667
668 -- {Start Of Comments}
669 --
670 -- Description:
671 -- Called before displaying the Activity details page to find out whether
672 -- a) Activity is valid else return 'I' (Invalid)
673 -- b) Activity is open else return 'E' (Expired)
674 -- c) Activity has events attached to it else return 'NE' (No Events)
675 -- Prerequisites:
676 -- none
677 --
678 -- Post Success:
679 --
680 -- Post Failure:
681 -- an exception is raised
682 --
683 -- Access Status:
684 -- Public
685 --
686 -- {End Of Comments}
687 --
688 ----------------------------------------------------------------------------
689
690
691
692 FUNCTION CHK_VALID_ACTIVITY(
693 p_activity_id IN OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type
694 )
695 RETURN VARCHAR2;
696
697
698 --|--------------------------------------------------------------------------|
699 --|--< CHK_VALID_EVENT >-------------------------------------------|
700 --|--------------------------------------------------------------------------|
701
702 -- {Start Of Comments}
703 --
704 -- Description:
705 -- Called before displaying the Event details page to find out whether
706 -- a) Event is valid else return 'I' (Invalid)
707 -- b) Event is open else return 'E' (Expired) or 'C' (closed)
708 -- c) Enrollment for the event is still open else return 'EC' (enrollment Closed) or
709 -- 'ENS' (enrolmetn not yet started)
710 -- Prerequisites:
711 -- none
712 --
713 -- Post Success:
714 --
715 -- Post Failure:
716 -- an exception is raised
717 --
718 -- Access Status:
719 -- Public
720 --
721 -- {End Of Comments}
722 --
723 ----------------------------------------------------------------------------
724
725
726 FUNCTION CHK_VALID_EVENT(
727 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
728 )
729 RETURN VARCHAR2;
730
731
732
733 Procedure CHK_UNIQUE_FUNC(p_function_name in varchar2,
734 p_user_id in number,
735 result out nocopy varchar2);
736
737 -- |--------------------------------------------------------------------------|
738 -- |--< Get_Booking_OVN >---------------------------------------------------|
739 -- |--------------------------------------------------------------------------|
740 -- {Start Of Comments}
741 --
742 FUNCTION Get_Booking_OVN (p_booking_id IN NUMBER)
743 RETURN NUMBER;
744 --
745
746 end OTA_EL_ENROLL_SS;
747