1 package ota_cpr_utility AUTHID CURRENT_USER as
2 /* $Header: otcprutl.pkh 120.6 2011/11/16 11:05:45 jaysridh ship $ */
3
4 -- Author: sbhullar
5 -- ----------------------------------------------------------------
6 -- ------------------< is_competency_acheived >--------------------
7 -- ----------------------------------------------------------------
8 -- {Start of Comments}
9 --
10 -- Description:
11 -- This function will be used to find whether a particular person
12 -- has acheived the specfied competency at specified level or not
13 --
14 -- IN
15 -- p_person_id
16 -- p_comp_id
17 -- p_level_id
18 --
19 -- Post Failure:
20 -- None.
21 -- Access Status
22 -- Public
23 -- {End of Comments}
24 ------------------------------------------------------------------
25 FUNCTION is_competency_acheived
26 (p_person_id in per_all_people_f.person_id%type,
27 p_comp_id in per_competence_elements.competence_id%type,
28 p_level_id in per_competence_elements.proficiency_level_id%type
29 ) RETURN varchar2;
30
31 FUNCTION check_learner_comp_step_values
32 (p_act_ver_id in ota_activity_versions.activity_version_id%type,
33 p_comp_id in per_competence_elements.competence_id%type,
34 p_level_id in per_competence_elements.proficiency_level_id%type
35 ) RETURN varchar2;
36
37 FUNCTION is_course_completed
38 (p_person_id in per_all_people_f.person_id%type,
39 p_delegate_contact_id in NUMBER,
40 p_user_id in NUMBER,
41 p_user_type in ota_attempts.user_type%type,
42 p_act_ver_id in ota_activity_versions.activity_version_id%type
43 ) RETURN varchar2;
44
45 FUNCTION is_mandatory_prereqs_completed
46 (p_person_id in per_all_people_f.person_id%type,
47 p_delegate_contact_id in NUMBER,
48 p_user_id in NUMBER,
49 p_user_type in ota_attempts.user_type%type,
50 p_act_ver_id in ota_activity_versions.activity_version_id%type
51 ) RETURN varchar2;
52
53 FUNCTION is_advisory_prereqs_completed
54 (p_person_id in per_all_people_f.person_id%type,
55 p_delegate_contact_id in NUMBER,
56 p_user_id in NUMBER,
57 p_user_type in ota_attempts.user_type%type,
58 p_act_ver_id in ota_activity_versions.activity_version_id%type
59 ) RETURN varchar2;
60
61 FUNCTION get_enroll_image
62 (p_person_id in per_all_people_f.person_id%type,
63 p_delegate_contact_id in NUMBER,
64 p_user_id in NUMBER,
65 p_user_type in ota_attempts.user_type%type,
66 p_event_id in ota_events.event_id%type
67 ) RETURN varchar2;
68
69 Procedure chk_mandatory_prereqs
70 (p_person_id ota_delegate_bookings.delegate_person_id%TYPE,
71 p_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE,
72 p_event_id ota_events.event_id%TYPE
73 );
74
75 FUNCTION is_mand_crs_prereqs_completed
76 (p_person_id in per_all_people_f.person_id%type,
77 p_delegate_contact_id in NUMBER,
78 p_user_id in NUMBER,
79 p_user_type in ota_attempts.user_type%type,
80 p_act_ver_id in ota_activity_versions.activity_version_id%type
81 ) RETURN varchar2;
82
83 FUNCTION is_mand_comp_prereqs_completed
84 (p_person_id in per_all_people_f.person_id%type,
85 p_act_ver_id in ota_activity_versions.activity_version_id%type
86 ) RETURN varchar2;
87
88
89 FUNCTION get_prereq_met_count
90 (p_event_id in ota_events.event_id%type,
91 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
92 p_comp_id in per_competence_elements.competence_id%type,
93 p_level_id in per_competence_elements.proficiency_level_id%type
94 ) RETURN varchar2;
95
96 FUNCTION get_prereq_not_met_count
97 (p_event_id in ota_events.event_id%type,
98 p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
99 p_comp_id in per_competence_elements.competence_id%type,
100 p_level_id in per_competence_elements.proficiency_level_id%type
101 ) RETURN varchar2;
102
103 FUNCTION is_mand_comp_prereqs_comp_evt
104 (p_person_id in per_all_people_f.person_id%type,
105 p_event_id in ota_events.event_id%type
106 ) RETURN varchar2;
107
108 FUNCTION is_mand_crs_prereqs_comp_evt
109 (p_person_id in per_all_people_f.person_id%type
110 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE
111 ,p_user_id in number
112 ,p_user_type in varchar2
113 ,p_event_id in ota_events.event_id%type
114 ) RETURN varchar2;
115
116 FUNCTION is_mandatory_prereqs_comp_evt
117 (p_person_id in per_all_people_f.person_id%type
118 ,p_delegate_contact_id in ota_delegate_bookings.delegate_contact_id%TYPE default NULL
119 ,p_user_id in number default NULL
120 ,p_user_type in varchar2 default 'E'
121 , p_event_id in ota_events.event_id%type
122 ) RETURN varchar2;
123
124 -- ----------------------------------------------------------------------------
125 -- |-------------------------< is_valid_classes_available >--------------------|
126 -- ----------------------------------------------------------------------------
127 -- PUBLIC
128 -- Description:
129 -- Validates whether prerequisite course contains valid classes or not.
130 -- Course should have associated offering and valid classes. Valid classes
131 -- include classes whose class type is SCHEDULED or SELFPACED and whose
132 -- class status is not Cancelled and which are not expired
133 --
134 FUNCTION is_valid_classes_available
135 (p_prerequisite_course_id in number
136 ) RETURN varchar2;
137 --
138 -- ----------------------------------------------------------------------------
139 -- |-------------------------< get_all_dependent_courses >--------------------|
140 -- ----------------------------------------------------------------------------
141 -- PUBLIC
142 -- Description:
143 -- Fetch the list of courses that are dependant on the specified activity id
144 -- whose end date are greater than the given pre-req course.
145 --
146 FUNCTION get_all_dependent_courses
147 (p_act_ver_id in ota_activity_versions.activity_version_id%type,
148 p_pre_req_crs_end_date varchar2) RETURN varchar2;
149
150 end ota_cpr_utility;