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