1 PACKAGE OTA_LRNG_PATH_MEMBER_UTIL AUTHID CURRENT_USER AS
2 /* $Header: otlpmwrs.pkh 120.0 2005/05/29 07:23:24 appldev noship $ */
3
4
5 -- ---------------------------------------------------------------------------
6 -- |----------------------< get_enrollment_status >---------------------------|
7 -- ---------------------------------------------------------------------------
8 -- {Start Of Comments}
9 --
10 -- Description:
11 -- Returns the enrollment status type or name in the order A P W R C
12 -- Used to determine the exact status of an learning path member based on
13 -- enrollments falling under it
14 --
15 -- Prerequisites:
16 --
17 -- In Arguments:
18 -- p_person_id
19 -- p_contact_id
20 -- p_activity_version_id
21 -- p_lp_member_enrollment_id
22 -- p_return_code - can be TYPE or NAME
23 --
24 --
25 -- Post Success:
26 -- Enrollment status or meaning is returned to calling unit
27 --
28 -- Post Failure:
29 --
30 --
31 -- Access Status:
32 -- Internal Development Use Only.
33 --
34 -- {End Of Comments}
35 -- ---------------------------------------------------------------------------
36 FUNCTION get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
37 p_contact_id IN ota_learning_paths.contact_id%TYPE,
38 p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
39 p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE DEFAULT NULL,
40 p_return_code IN VARCHAR2)
41 RETURN VARCHAR2;
42
43 -- ---------------------------------------------------------------------------
44 -- |----------------------< get_enrollment_status >---------------------------|
45 -- ---------------------------------------------------------------------------
46 -- {Start Of Comments}
47 --
48 -- Description:
49 -- Sets the enrollment status and date_status changed as the out parameters
50 -- for the class in the order A P W R C
51 --
52 -- Prerequisites:
53 --
54 --
55 -- In Arguments:
56 -- p_person_id
57 -- p_contact_id
58 -- p_lp_member_enrollment_id
59 --
60 -- Post Success:
61 -- Enrollment status, date_status changed is set as out parameters
62 --
63 -- Post Failure:
64 --
65 --
66 -- Access Status:
67 -- Internal Development Use Only.
68 --
69 -- {End Of Comments}
70 -- ----------------------------------------------------------------------------
71 PROCEDURE get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
72 p_contact_id IN ota_learning_paths.contact_id%TYPE,
73 p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
74 p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
75 p_booking_status_type OUT nocopy ota_booking_status_types.type%TYPE,
76 p_date_status_changed OUT nocopy ota_delegate_bookings.date_status_changed%TYPE);
77
78 -- ---------------------------------------------------------------------------
79 -- |----------------------< chk_enrollment_exist >----------------------------|
80 -- ---------------------------------------------------------------------------
81 -- {Start Of Comments}
82 --
83 -- Description:
84 -- Checks if an enrollment exists for the learning path member
85 --
86 -- Prerequisites:
87 --
88 --
89 -- In Arguments:
90 -- p_person_id
91 -- p_contact_id
92 -- p_learning_path_member_id
93 --
94 --
95 -- Post Success:
96 -- True is return to indicate that an enrollment exists
97 --
98 -- Post Failure:
99 --
100 --
101 -- Access Status:
102 -- Internal Development Use Only.
103 --
104 -- {End Of Comments}
105 -- ---------------------------------------------------------------------------
106 FUNCTION chk_enrollment_exist(p_person_id IN ota_learning_paths.person_id%TYPE,
107 p_contact_id IN ota_learning_paths.contact_id%TYPE,
108 p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE)
109 RETURN boolean;
110
111
112 -- ---------------------------------------------------------------------------
113 -- |----------------------< calculate_lme_status >--------------------------|
114 -- ---------------------------------------------------------------------------
115 -- {Start Of Comments}
116 --
117 -- Description:
118 -- Returns the member_status_code
119 -- Called while creating/updating a learning path member enrollment with member
120 -- status not equal to 'PLANNED' to determine the exact status based on
121 -- enrollments falling under it
122 --
123 -- Prerequisites:
124 --
125 -- In Arguments:
126 -- p_activity_version_id
127 -- p_lp_enrollment_id
128 -- p_member_status_code
129 --
130 -- Post Success:
131 -- Member status is returned to calling unit
132 --
133 -- Post Failure:
134 --
135 -- Access Status:
136 -- Internal Development Use Only.
137 --
138 -- {End Of Comments}
139 -- ---------------------------------------------------------------------------
140 PROCEDURE calculate_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
141 p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
142 p_member_status_code OUT nocopy VARCHAR2,
143 p_completion_date OUT nocopy DATE);
144
145
146 -- ---------------------------------------------------------------------------
147 -- |----------------------< get_lme_status >--------------------------|
148 -- ---------------------------------------------------------------------------
149 -- {Start Of Comments}
150 --
151 -- Description:
152 -- Returns the member_status_code
153 -- Called while creating/updating a learning path member enrollment with member
154 -- status not equal to 'PLANNED' to determine the exact status based on
155 -- enrollments falling under it
156 --
157 -- Prerequisites:
158 --
159 -- In Arguments:
160 -- p_activity_version_id
161 -- p_person_id
162 -- p_contact_id
163 -- p_member_status_code
164 --
165 -- Post Success:
166 -- Member status is returned to calling unit
167 --
168 -- Post Failure:
169 --
170 -- Access Status:
171 -- Internal Development Use Only.
172 --
173 -- {End Of Comments}
174 -- ---------------------------------------------------------------------------
175 FUNCTION get_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
176 p_person_id IN ota_learning_paths.person_id%TYPE,
177 p_contact_id IN ota_learning_paths.contact_id%TYPE)
178 RETURN VARCHAR2;
179
180 -- ----------------------------------------------------------------------------
181 -- ---------------------------< get_valid_enroll >-----------------------------
182 -- ----------------------------------------------------------------------------
183 -- {Start Of Comments}
184 --
185 -- Description:
186 -- Returns the member_status_code
187 -- returns E when enrollment status type is not Cancelled and an enrollment exists
188 -- returns S when no enrollment exists for the lpm
189 --
190 -- Prerequisites:
191 --
192 -- In Arguments:
193 -- p_person_id
194 -- p_contact_id
195 -- p_lp_member_enrollment_id
196 -- p_return_status
197 --
198 -- Post Success:
199 -- returns E or S based of whether enrollments exist or not
200 --
201 -- Post Failure:
202 --
203 -- Access Status:
204 -- Internal Development Use Only.
205 --
206 -- {End Of Comments}
207 -- ---------------------------------------------------------------------------
208 PROCEDURE get_valid_enroll(p_person_id IN ota_learning_paths.person_id%TYPE
209 ,p_contact_id IN ota_learning_paths.contact_id%TYPE
210 ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE
211 ,p_return_status OUT nocopy varchar2);
212
213 -- ---------------------------------------------------------------------------
214 -- |----------------------< Update_lpe_lme_change >--------------------------|
215 -- ---------------------------------------------------------------------------
216 -- {Start Of Comments}
217 --
218 -- Description:
219 -- Returns the member_status_code
220 -- Called while creating/updating a learning path member enrollment with member
221 -- status not equal to 'PLANNED' to determine the exact status based on
222 -- enrollments falling under it
223 --
224 -- Prerequisites:
225 --
226 -- In Arguments:
227 -- p_activity_version_id
228 -- p_lp_enrollment_id
229 -- p_member_status_code
230 --
231 -- Post Success:
232 -- Member status is returned to calling unit
233 --
234 -- Post Failure:
235 --
236 -- Access Status:
237 -- Internal Development Use Only.
238 --
239 -- {End Of Comments}
240 -- ---------------------------------------------------------------------------
241 Procedure Update_lpe_lme_change
242 (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%type);
243
244 -- ---------------------------------------------------------------------------
245 -- |----------------------< Update_lpe_lme_change >--------------------------|
246 -- ---------------------------------------------------------------------------
247 -- {Start Of Comments}
248 --
249 -- Description:
250 -- Returns the member_status_code
251 -- Called while creating/updating a learning path member enrollment with member
252 -- status not equal to 'PLANNED' to determine the exact status based on
253 -- enrollments falling under it
254 --
255 -- Prerequisites:
256 --
257 -- In Arguments:
258 -- p_activity_version_id
259 -- p_lp_enrollment_id
260 -- p_member_status_code
261 --
262 -- Post Success:
263 -- Member status is returned to calling unit
264 --
265 -- Post Failure:
266 --
267 -- Access Status:
268 -- Internal Development Use Only.
269 --
270 -- {End Of Comments}
271 -- ---------------------------------------------------------------------------
272 Procedure Update_lpe_lme_change
273 (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%type,
274 p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,
275 p_lp_enrollment_ids OUT NOCOPY varchar2);
276
277 -- ---------------------------------------------------------------------------
278 -- |----------------------< update_lme_enroll_status_chg >--------------------------|
279 -- ---------------------------------------------------------------------------
280 -- {Start Of Comments}
281 --
282 -- Description:
283 -- when Enrollment status to an event changes the TPC's status attached to the
284 -- event also changes.
285 -- Called from ota_tdb_api_upd2.update_enrollment and ota_tdb_api_ins2.create_enrollment
286 -- Prerequisites:
287 --
288 --
289 -- In Arguments:
290 -- p_event_id
291 -- p_person_id
292 --
293 -- Post Success:
294 -- The attached TPC's status is updated
295 --
296 -- Post Failure:
297 --
298 --
299 -- Access Status:
300 -- Internal Development Use Only.
301 --
302 -- {End Of Comments}
303 -- ---------------------------------------------------------------------------
304 PROCEDURE update_lme_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
305 p_person_id IN ota_lp_enrollments.person_id%TYPE,
306 p_contact_id IN ota_lp_enrollments.contact_id%TYPE,
307 p_lp_enrollment_ids OUT NOCOPY varchar2);
308
309 -- ----------------------------------------------------------------------------
310 -- |----------------------<create_talent_mgmt_lpm>-----------------------------|
311 -- ----------------------------------------------------------------------------
312 -- {Start Of Comments}
313 --
314 -- Description:
315 --
316 --
317 -- Prerequisites:
318 --
319 --
320 -- In Parameters:
321 --
322 --
323 --
324 -- Post Success:
325 --
326 --
327 -- Post Failure:
328 -- If an error has occurred, an error message will be raised.
329 --
330 -- Developer Implementation Notes:
331 -- None
332 --
333 -- Access Status:
334 -- Internal Row Handler Use Only.
335 --
336 -- {End Of Comments}
337 -- ----------------------------------------------------------------------------
338 PROCEDURE create_talent_mgmt_lpm
339 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
340 ,p_effective_date IN DATE
341 ,p_business_group_id IN NUMBER
342 ,p_learning_path_id IN NUMBER DEFAULT NULL
343 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
344 ,p_learning_path_section_id IN NUMBER DEFAULT NULL
345 ,p_path_name IN VARCHAR2 DEFAULT NULL
346 ,p_path_purpose IN VARCHAR2 DEFAULT NULL
347 ,p_path_status_code IN VARCHAR2 DEFAULT NULL
348 ,p_path_start_date_active IN DATE DEFAULT NULL
349 ,p_path_end_date_active IN DATE DEFAULT NULL
350 ,p_source_function_code IN VARCHAR2
351 ,p_assignment_id IN NUMBER DEFAULT NULL
352 ,p_source_id IN NUMBER DEFAULT NULL
353 ,p_creator_person_id IN NUMBER
354 ,p_person_id IN NUMBER
355 ,p_display_to_learner_flag IN VARCHAR2
356 ,p_activity_version_id IN NUMBER
357 ,p_course_sequence IN NUMBER
358 ,p_member_status_code IN VARCHAR2 DEFAULT NULL
359 ,p_completion_target_date IN DATE
360 ,p_notify_days_before_target IN NUMBER
361 ,p_object_version_NUMBER OUT NOCOPY NUMBER
362 ,p_return_status OUT NOCOPY VARCHAR2
363 );
364
365
366 -- ----------------------------------------------------------------------------
367 -- |----------------------<update_talent_mgmt_lp>-----------------------------|
368 -- ----------------------------------------------------------------------------
369 -- {Start Of Comments}
370 --
371 -- Description:
372 --
373 --
374 -- Prerequisites:
375 --
376 --
377 -- In Parameters:
378 --
379 --
380 --
381 -- Post Success:
382 --
383 --
384 -- Post Failure:
385 -- If an error has occurred, an error message will be raised.
386 --
387 -- Developer Implementation Notes:
388 -- None
389 --
390 -- Access Status:
391 -- Internal Row Handler Use Only.
392 --
393 -- {End Of Comments}
394 -- ----------------------------------------------------------------------------
395 PROCEDURE update_talent_mgmt_lp
396 (p_validate IN NUMBER DEFAULT hr_api.g_false_num
397 ,p_effective_date IN DATE
398 ,p_mode IN VARCHAR2
399 ,p_learning_path_id IN NUMBER DEFAULT NULL
400 ,p_lp_enrollment_id IN NUMBER DEFAULT NULL
401 ,p_source_function_code IN VARCHAR2
402 ,p_assignment_id IN NUMBER DEFAULT NULL
403 ,p_source_id IN NUMBER DEFAULT NULL
404 ,p_person_id IN NUMBER
405 ,p_display_to_learner_flag IN VARCHAR2
406 ,p_lps_ovn IN OUT NOCOPY NUMBER
407 ,p_lpe_ovn IN OUT NOCOPY NUMBER
408 ,p_return_status OUT NOCOPY VARCHAR2
409 );
410
411 -- ----------------------------------------------------------------------------
412 -- |-------------------< chk_no_of_mandatory_courses >-------------------------|
413 -- ----------------------------------------------------------------------------
414 --
415 PROCEDURE chk_no_of_mandatory_courses
416 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE,
417 p_return_status OUT NOCOPY VARCHAR2);
418
419 END ota_lrng_path_member_util;
420
[Home] [Help]