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