1 PACKAGE OTA_CME_UTIL AUTHID CURRENT_USER AS
2 /* $Header: otcmewrs.pkh 120.8.12020000.2 2012/12/03 10:47:15 jaysridh ship $ */
3
4 -- ---------------------------------------------------------------------------
5 -- |----------------------< get_enrl_status_on_update >-----------------------|
6 -- ---------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- During cme update set's the enrollment status and date_status changed as
11 -- the out parameters for the class in the order A P W R C
12 --
13 -- Prerequisites:
14 --
15 --
16 -- In Arguments:
17 -- p_activity_version_id
18 -- p_cert_prd_enrollment_id
19 --
20 -- Post Success:
21 -- Enrollment status, date_status changed is set as out parameters
22 --
23 -- Post Failure:
24 --
25 --
26 -- Access Status:
27 -- Internal Development Use Only.
28 --
29 -- {End Of Comments}
30 -- ----------------------------------------------------------------------------
31 PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
32 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
33 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
34 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE);
35
36 PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
37 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
38 p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
39 p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE,
40 p_successful_attendance_flag OUT NOCOPY ota_delegate_bookings.successful_attendance_flag%TYPE);
41
42 -- ---------------------------------------------------------------------------
43 -- |----------------------< calculate_cme_status >--------------------------|
44 -- ---------------------------------------------------------------------------
45 -- {Start Of Comments}
46 --
47 -- Description:
48 -- Returns the member_status_code
49 -- Called while creating/updating a cert member enrollment with member
50 -- status not equal to 'PLANNED' to determine the exact status based on
51 -- enrollments falling under it
52 --
53 -- Prerequisites:
54 --
55 -- In Arguments:
56 -- p_activity_version_id
57 -- p_cert_prd_enrollment_id
58 -- p_mode, either 'C' as create or 'U' as update
59 -- p_is_recert - Need to be passed only when the p_mode is 'C'
60 --
61 -- Post Success:
62 -- Member status is returned to calling unit
63 --
64 -- Post Failure:
65 --
66 -- Access Status:
67 -- Internal Development Use Only.
68 --
69 -- {End Of Comments}
70 -- ---------------------------------------------------------------------------
71 PROCEDURE calculate_cme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
72 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
73 p_mode IN VARCHAR2,
74 p_member_status_code OUT nocopy VARCHAR2,
75 p_completion_date OUT nocopy DATE,
76 p_is_recert IN VARCHAR2 default 'N');
77
78 -- ---------------------------------------------------------------------------
79 -- |----------------------< update_cme_status >-------------------|
80 -- ---------------------------------------------------------------------------
81 -- {Start Of Comments}
82 --
83 -- Description:
84 -- when Enrollment status to an event changes the CME's status attached to the
85 -- event also changes.
86 -- Called from ota_tdb_api_upd2.update_enrollment and ota_tdb_api_ins2.create_enrollment
87 -- Prerequisites:
88 --
89 --
90 -- In Arguments:
91 -- p_event_id
92 -- p_person_id
93 --
94 -- Post Success:
95 -- The attached CME's status is updated
96 --
97 -- Post Failure:
98 --
99 --
100 -- Access Status:
101 -- Internal Development Use Only.
102 --
103 -- {End Of Comments}
104 -- ---------------------------------------------------------------------------
105 PROCEDURE update_cme_status (p_event_id IN ota_events.event_id%TYPE,
106 p_person_id IN ota_cert_enrollments.person_id%TYPE,
107 p_contact_id IN ota_cert_enrollments.contact_id%TYPE,
108 p_cert_prd_enrollment_ids OUT NOCOPY varchar2);
109
110
111 -- ---------------------------------------------------------------------------
112 -- |----------------------< Update_cpe_status >------------------------------|
113 -- ---------------------------------------------------------------------------
114 -- {Start Of Comments}
115 --
116 -- Description:
117 -- Returns the member_status_code
118 -- Called while creating/updating a cert member enrollment with member
119 -- status not equal to 'PLANNED' to determine the exact status based on
120 -- enrollments falling under it
121 --
122 -- Prerequisites:
123 --
124 -- In Arguments:
125 -- p_cert_mbr_enrollment_id
126 -- p_completion_date
127 --
128 -- Post Success:
129 -- Member status is returned to calling unit
130 --
131 -- Post Failure:
132 --
133 -- Access Status:
134 -- Internal Development Use Only.
135 --
136 -- {End Of Comments}
137 -- ---------------------------------------------------------------------------
138 Procedure Update_cpe_status
139 (p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
140 ,p_cert_prd_enrollment_id OUT NOCOPY varchar2
141 ,p_completion_date in date default sysdate);
142
143 -- ---------------------------------------------------------------------------
144 -- |----------------------< update_cme_status >-------------------|
145 -- ---------------------------------------------------------------------------
146 -- {Start Of Comments}
147 --
148 -- Description:
149 -- when Enrollment status to an event changes the CME's status attached to the
150 -- event also changes.
151 -- Called from ota_tdb_api_upd2.update_enrollment and ota_tdb_api_ins2.create_enrollment
152 -- Prerequisites:
153 --
154 --
155 -- In Arguments:
156 -- p_cert_mbr_enrollment_id
157 --
158 -- Post Success:
159 -- The attached CME's status is updated
160 --
161 -- Post Failure:
162 --
163 --
164 -- Access Status:
165 -- Internal Development Use Only.
166 --
167 -- {End Of Comments}
168 -- ---------------------------------------------------------------------------
169 PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type);
170
171 -- ---------------------------------------------------------------------------
172 -- |----------------------< chk_if_cme_exists >-------------------|
173 -- ---------------------------------------------------------------------------
174 -- {Start Of Comments}
175 --
176 -- Description:
177 -- Checks whether there are learners who have enrolled in the certification member.
178 -- If there are no enrollments, then the component may be removed from the certification.
179 -- Prerequisites:
180 --
181 --
182 -- In Arguments:
183 -- p_cmb_id
184 --
185 --
186 -- Post Failure:
187 --
188 --
189 -- Access Status:
190 --
191 -- {End Of Comments}
192 -- ---------------------------------------------------------------------------
193 PROCEDURE chk_if_cme_exists (p_cmb_id IN ota_certification_members.certification_member_id%TYPE
194 , p_return_status OUT NOCOPY VARCHAR2);
195
196
197 -- ---------------------------------------------------------------------------
198 -- |----------------------< refresh_cme >-------------------|
199 -- ---------------------------------------------------------------------------
200 -- {Start Of Comments}
201 --
202 -- Description:
203 -- This procedure will check for any newly added and end dated courses since
204 -- last cert unsubscribe, creates cme record for new courses and update cme
205 -- member_status_code for end dated courses.
206 --
207 -- Prerequisites:
208 --
209 --
210 -- In Arguments:
211 -- p_cert_prd_enrollment_id
212 --
213 --
214 -- Post Failure:
215 --
216 --
217 -- Access Status:
218 --
219 -- {End Of Comments}
220 -- ---------------------------------------------------------------------------
221 procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type);
222
223 Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
224 return varchar2;
225
226 function get_cert_mbr_enroll_id(p_event_id IN ota_events.event_id%type,
227 p_person_id IN ota_cert_enrollments.person_id%type,
228 p_contact_id ota_cert_enrollments.contact_id%type)
229 return ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
230
231 function get_cert_prd_enroll_id(p_event_id IN ota_events.event_id%type,
232 p_person_id IN ota_cert_enrollments.person_id%type,
233 p_contact_id ota_cert_enrollments.contact_id%type)
234 return ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
235 END OTA_CME_UTIL;
[Home] [Help]