[Home] [Help]
PACKAGE BODY: APPS.OTA_LP_ENROLLMENT_API
Source
1 Package Body OTA_LP_ENROLLMENT_API as
2 /* $Header: otlpeapi.pkb 120.7.12010000.2 2009/05/21 09:36:15 pekasi ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_LP_ENROLLMENT_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_LP_ENROLLMENT >------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_lp_enrollment
13 (
14 p_effective_date in date,
15 p_validate in boolean default false ,
16 p_learning_path_id in number,
17 p_person_id in number default null,
18 p_contact_id in number default null,
19 p_path_status_code in varchar2,
20 p_enrollment_source_code in varchar2,
21 p_no_of_mandatory_courses in number default null,
22 p_no_of_completed_courses in number default null,
23 p_completion_target_date in date default null,
24 p_completion_date in date default null,
25 p_creator_person_id in number default null,
26 p_business_group_id in number,
27 p_attribute_category in varchar2 default null,
28 p_attribute1 in varchar2 default null,
29 p_attribute2 in varchar2 default null,
30 p_attribute3 in varchar2 default null,
31 p_attribute4 in varchar2 default null,
32 p_attribute5 in varchar2 default null,
33 p_attribute6 in varchar2 default null,
34 p_attribute7 in varchar2 default null,
35 p_attribute8 in varchar2 default null,
36 p_attribute9 in varchar2 default null,
37 p_attribute10 in varchar2 default null,
38 p_attribute11 in varchar2 default null,
39 p_attribute12 in varchar2 default null,
40 p_attribute13 in varchar2 default null,
41 p_attribute14 in varchar2 default null,
42 p_attribute15 in varchar2 default null,
43 p_attribute16 in varchar2 default null,
44 p_attribute17 in varchar2 default null,
45 p_attribute18 in varchar2 default null,
46 p_attribute19 in varchar2 default null,
47 p_attribute20 in varchar2 default null,
48 p_attribute21 in varchar2 default null,
49 p_attribute22 in varchar2 default null,
50 p_attribute23 in varchar2 default null,
51 p_attribute24 in varchar2 default null,
52 p_attribute25 in varchar2 default null,
53 p_attribute26 in varchar2 default null,
54 p_attribute27 in varchar2 default null,
55 p_attribute28 in varchar2 default null,
56 p_attribute29 in varchar2 default null,
57 p_attribute30 in varchar2 default null,
58 p_IS_HISTORY_FLAG in varchar2 default 'N',
59 p_lp_enrollment_id out nocopy number,
60 p_object_version_number out nocopy number
61 ) is
62 --
63 -- Declare cursors and local variables
64 --
65 l_proc varchar2(72) := g_package||' create_lp_enrollment';
66 l_lp_enrollment_id number;
67 l_object_version_number number;
68 l_effective_date date;
69
70 -- competency update associated with lps
71 l_item_key wf_items.item_key%TYPE;
72 g_dummy varchar2(1);
73
74 CURSOR get_info_for_comp IS
75 SELECT null
76 FROM ota_learning_paths lps
77 WHERE lps.learning_path_id = p_learning_path_id
78 AND lps.path_source_code = 'CATALOG';
79
80 cursor get_lp_type is
81 select path_source_code
82 from ota_learning_paths
83 where learning_path_id = p_learning_path_id;
84
85 l_type ota_learning_paths.path_source_code%type;
86
87 begin
88 hr_utility.set_location('Entering:'|| l_proc, 10);
89 --
90 -- Issue a savepoint
91 --
92 savepoint CREATE_LP_ENROLLMENT;
93 l_effective_date := trunc(p_effective_date);
94
95
96 begin
97 ota_lp_enrollment_bk1.create_lp_enrollment_b
98 ( p_effective_date => p_effective_date
99 ,p_validate => p_validate
100 ,p_learning_path_id => p_learning_path_id
101 ,p_business_group_id => p_business_group_id
102 ,p_person_id => p_person_id
103 ,p_contact_id => p_contact_id
104 ,p_path_status_code => p_path_status_code
105 ,p_enrollment_source_code => p_enrollment_source_code
106 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
107 ,p_no_of_completed_courses => p_no_of_completed_courses
108 ,p_completion_target_date => p_completion_target_date
109 ,p_completion_date => p_completion_date
110 ,p_creator_person_id => p_creator_person_id
111 ,p_attribute_category => p_attribute_category
112 ,p_attribute1 => p_attribute1
113 ,p_attribute2 => p_attribute2
114 ,p_attribute3 => p_attribute3
115 ,p_attribute4 => p_attribute4
116 ,p_attribute5 => p_attribute5
117 ,p_attribute6 => p_attribute6
118 ,p_attribute7 => p_attribute7
119 ,p_attribute8 => p_attribute8
120 ,p_attribute9 => p_attribute9
121 ,p_attribute10 => p_attribute10
122 ,p_attribute11 => p_attribute11
123 ,p_attribute12 => p_attribute12
124 ,p_attribute13 => p_attribute13
125 ,p_attribute14 => p_attribute14
126 ,p_attribute15 => p_attribute15
127 ,p_attribute16 => p_attribute16
128 ,p_attribute17 => p_attribute17
129 ,p_attribute18 => p_attribute18
130 ,p_attribute19 => p_attribute19
131 ,p_attribute20 => p_attribute20
132 ,p_attribute21 => p_attribute21
133 ,p_attribute22 => p_attribute22
134 ,p_attribute23 => p_attribute23
135 ,p_attribute24 => p_attribute24
136 ,p_attribute25 => p_attribute25
137 ,p_attribute26 => p_attribute26
138 ,p_attribute27 => p_attribute27
139 ,p_attribute28 => p_attribute28
140 ,p_attribute29 => p_attribute29
141 ,p_attribute30 => p_attribute30
142 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
143 );
144 exception
145 when hr_api.cannot_find_prog_unit then
146 hr_api.cannot_find_prog_unit_error
147 (p_module_name => 'CREATE_LP_ENROLLMENT'
148 ,p_hook_type => 'BP'
149 );
150 end;
151
152 --
153 -- Process Logic
154 --
155 ota_lpe_ins.ins
156 (
157 p_effective_date => p_effective_date
158 ,p_learning_path_id => p_learning_path_id
159 ,p_path_status_code => p_path_status_code
160 ,p_enrollment_source_code => p_enrollment_source_code
161 ,p_business_group_id => p_business_group_id
162 ,p_person_id => p_person_id
163 ,p_contact_id => p_contact_id
164 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
165 ,p_no_of_completed_courses => p_no_of_completed_courses
166 ,p_completion_target_date => p_completion_target_date
167 ,p_completion_date => p_completion_date
168 ,p_creator_person_id => p_creator_person_id
169 ,p_attribute_category => p_attribute_category
170 ,p_attribute1 => p_attribute1
171 ,p_attribute2 => p_attribute2
172 ,p_attribute3 => p_attribute3
173 ,p_attribute4 => p_attribute4
174 ,p_attribute5 => p_attribute5
175 ,p_attribute6 => p_attribute6
176 ,p_attribute7 => p_attribute7
177 ,p_attribute8 => p_attribute8
178 ,p_attribute9 => p_attribute9
179 ,p_attribute10 => p_attribute10
180 ,p_attribute11 => p_attribute11
181 ,p_attribute12 => p_attribute12
182 ,p_attribute13 => p_attribute13
183 ,p_attribute14 => p_attribute14
184 ,p_attribute15 => p_attribute15
185 ,p_attribute16 => p_attribute16
186 ,p_attribute17 => p_attribute17
187 ,p_attribute18 => p_attribute18
188 ,p_attribute19 => p_attribute19
189 ,p_attribute20 => p_attribute20
190 ,p_attribute21 => p_attribute21
191 ,p_attribute22 => p_attribute22
192 ,p_attribute23 => p_attribute23
193 ,p_attribute24 => p_attribute24
194 ,p_attribute25 => p_attribute25
195 ,p_attribute26 => p_attribute26
196 ,p_attribute27 => p_attribute27
197 ,p_attribute28 => p_attribute28
198 ,p_attribute29 => p_attribute29
199 ,p_attribute30 => p_attribute30
200 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
201 ,p_lp_enrollment_id => l_lp_enrollment_id
202 ,p_object_version_number => l_object_version_number
203 );
204 --
205 -- Set all output arguments
206 --
207 p_lp_enrollment_id := l_lp_enrollment_id;
208 p_object_version_number := l_object_version_number;
209
210
211
212 begin
213 ota_lp_enrollment_bk1.create_lp_enrollment_a
214 ( p_effective_date => p_effective_date
215 ,p_validate => p_validate
216 ,p_learning_path_id => p_learning_path_id
217 ,p_business_group_id => p_business_group_id
218 ,p_person_id => p_person_id
219 ,p_contact_id => p_contact_id
220 ,p_path_status_code => p_path_status_code
221 ,p_enrollment_source_code => p_enrollment_source_code
222 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
223 ,p_no_of_completed_courses => p_no_of_completed_courses
224 ,p_completion_target_date => p_completion_target_date
225 ,p_completion_date => p_completion_date
226 ,p_creator_person_id => p_creator_person_id
227 ,p_attribute_category => p_attribute_category
228 ,p_attribute1 => p_attribute1
229 ,p_attribute2 => p_attribute2
230 ,p_attribute3 => p_attribute3
231 ,p_attribute4 => p_attribute4
232 ,p_attribute5 => p_attribute5
233 ,p_attribute6 => p_attribute6
234 ,p_attribute7 => p_attribute7
235 ,p_attribute8 => p_attribute8
236 ,p_attribute9 => p_attribute9
237 ,p_attribute10 => p_attribute10
238 ,p_attribute11 => p_attribute11
239 ,p_attribute12 => p_attribute12
240 ,p_attribute13 => p_attribute13
241 ,p_attribute14 => p_attribute14
242 ,p_attribute15 => p_attribute15
243 ,p_attribute16 => p_attribute16
244 ,p_attribute17 => p_attribute17
245 ,p_attribute18 => p_attribute18
246 ,p_attribute19 => p_attribute19
247 ,p_attribute20 => p_attribute20
248 ,p_attribute21 => p_attribute21
249 ,p_attribute22 => p_attribute22
250 ,p_attribute23 => p_attribute23
251 ,p_attribute24 => p_attribute24
252 ,p_attribute25 => p_attribute25
253 ,p_attribute26 => p_attribute26
254 ,p_attribute27 => p_attribute27
255 ,p_attribute28 => p_attribute28
256 ,p_attribute29 => p_attribute29
257 ,p_attribute30 => p_attribute30
258 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
259 );
260
261 exception
262 when hr_api.cannot_find_prog_unit then
263 hr_api.cannot_find_prog_unit_error
264 (p_module_name => 'CREATE_LP_ENROLLMENT'
265 ,p_hook_type => 'AP'
266 );
267 end;
268
269 --
270 -- When in validation only mode raise the Validate_Enabled exception
271 --
272 if p_validate then
273 raise hr_api.validate_enabled;
274 end if;
275
276 -- Added to check and test LP completion notifications
277 IF p_path_status_code = 'COMPLETED' THEN
278 OTA_LP_NOTIFY_SS.create_wf_process(
279 p_lp_notification_type => 'LP_COMPLETE',
280 p_lp_enrollment_id => p_lp_enrollment_id);
281 END IF;
282 -- End of added code to test LP Completion Notifications
283 --competency update associated with LP
284 OPEN get_info_for_comp;
285 FETCH get_info_for_comp INTO g_dummy;
286 IF get_info_for_comp%FOUND THEN
287 --Modified for Bug#3864084
288 IF p_path_status_code = 'COMPLETED' AND p_person_id IS NOT NULL
289 and ('Y' = ota_lrng_path_util.is_path_successful(p_lp_enrollment_id => p_lp_enrollment_id))
290 THEN
291 ota_competence_ss.create_wf_process(p_process => 'OTA_COMPETENCE_UPDATE_JSP_PRC',
292 p_itemtype => 'HRSSA',
293 p_person_id => p_person_id,
294 p_eventid => null,
295 p_learningpath_ids => to_char(p_learning_path_id),
296 p_itemkey => l_item_key);
297 END IF;
298 END IF;
299 CLOSE get_info_for_comp;
300
301 -- fire notifications to Learner
302 open get_lp_type;
303 fetch get_lp_type into l_type;
304 close get_lp_type;
305
306 if l_type = 'CATALOG' or l_type = 'MANAGER' then
307
308 OTA_INITIALIZATION_WF.init_lp_wf(p_item_type => 'HRSSA',
309 p_lp_enrollment_id => p_lp_enrollment_id,
310 p_event_fired => 'LP_SUBSCRIBE');
311
312
313 end if;
314
315
316 hr_utility.set_location(' Leaving:'||l_proc, 70);
317 exception
318 when hr_api.validate_enabled then
319 --
320 -- As the Validate_Enabled exception has been raised
321 -- we must rollback to the savepoint
322 --
323 rollback to CREATE_LP_ENROLLMENT;
324 --
325 -- Only set output warning arguments
326 -- (Any key or derived arguments must be set to null
327 -- when validation only mode is being used.)
328 --
329 p_lp_enrollment_id := null;
330 p_object_version_number := null;
331 hr_utility.set_location(' Leaving:'||l_proc, 80);
332 when others then
333 --
334 -- A validation or unexpected error has occured
335 --
336 rollback to CREATE_LP_ENROLLMENT;
337 p_lp_enrollment_id := null;
338 p_object_version_number := null;
339 hr_utility.set_location(' Leaving:'||l_proc, 90);
340 raise;
341 end create_lp_enrollment;
342 -- ----------------------------------------------------------------------------
343 -- |-------------------------< UPDATE_LP_ENROLLMENT >-------------------------|
344 -- ----------------------------------------------------------------------------
345 --
346 procedure update_lp_enrollment
347 (
348 p_effective_date in date,
349 p_lp_enrollment_id in number,
350 p_object_version_number in out nocopy number,
351 p_person_id in number default hr_api.g_number,
352 p_contact_id in number default hr_api.g_number,
353 p_path_status_code in varchar2 default hr_api.g_varchar2,
354 p_enrollment_source_code in varchar2 default hr_api.g_varchar2,
355 p_no_of_mandatory_courses in number default hr_api.g_number,
356 p_no_of_completed_courses in number default hr_api.g_number,
357 p_completion_target_date in date default hr_api.g_date,
358 p_completion_date in date default hr_api.g_date,
359 p_creator_person_id in number default hr_api.g_number,
360 p_attribute_category in varchar2 default hr_api.g_varchar2,
361 p_attribute1 in varchar2 default hr_api.g_varchar2,
362 p_attribute2 in varchar2 default hr_api.g_varchar2,
363 p_attribute3 in varchar2 default hr_api.g_varchar2,
364 p_attribute4 in varchar2 default hr_api.g_varchar2,
365 p_attribute5 in varchar2 default hr_api.g_varchar2,
366 p_attribute6 in varchar2 default hr_api.g_varchar2,
367 p_attribute7 in varchar2 default hr_api.g_varchar2,
368 p_attribute8 in varchar2 default hr_api.g_varchar2,
369 p_attribute9 in varchar2 default hr_api.g_varchar2,
370 p_attribute10 in varchar2 default hr_api.g_varchar2,
371 p_attribute11 in varchar2 default hr_api.g_varchar2,
372 p_attribute12 in varchar2 default hr_api.g_varchar2,
373 p_attribute13 in varchar2 default hr_api.g_varchar2,
374 p_attribute14 in varchar2 default hr_api.g_varchar2,
375 p_attribute15 in varchar2 default hr_api.g_varchar2,
376 p_attribute16 in varchar2 default hr_api.g_varchar2,
377 p_attribute17 in varchar2 default hr_api.g_varchar2,
378 p_attribute18 in varchar2 default hr_api.g_varchar2,
379 p_attribute19 in varchar2 default hr_api.g_varchar2,
380 p_attribute20 in varchar2 default hr_api.g_varchar2,
381 p_attribute21 in varchar2 default hr_api.g_varchar2,
382 p_attribute22 in varchar2 default hr_api.g_varchar2,
383 p_attribute23 in varchar2 default hr_api.g_varchar2,
384 p_attribute24 in varchar2 default hr_api.g_varchar2,
385 p_attribute25 in varchar2 default hr_api.g_varchar2,
386 p_attribute26 in varchar2 default hr_api.g_varchar2,
387 p_attribute27 in varchar2 default hr_api.g_varchar2,
388 p_attribute28 in varchar2 default hr_api.g_varchar2,
389 p_attribute29 in varchar2 default hr_api.g_varchar2,
390 p_attribute30 in varchar2 default hr_api.g_varchar2,
391 p_is_history_flag in varchar2 default hr_api.g_varchar2,
392 p_business_group_id in number default hr_api.g_number,
393 p_validate in boolean default false
394 ) is
395 --
396 -- Declare cursors and local variables
397 --
398 l_proc varchar2(72) := g_package||' Update Learning Path';
399 l_object_version_number number := p_object_version_number;
400 l_effective_date date;
401
402 -- competency update associated with lps
403 l_item_key wf_items.item_key%TYPE;
404 l_learning_path_id ota_lp_enrollments.learning_path_id%TYPE;
405 l_person_id ota_lp_enrollments.person_id%TYPE;
406 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
407 l_proceed varchar2(1) := 'Y';
408
409
410 CURSOR get_info_for_comp IS
411 --Modified for Bug#3864084
412 SELECT lps.learning_path_id, lpe.person_id
413 FROM ota_learning_paths lps,
414 ota_lp_enrollments lpe
415 WHERE lps.learning_path_id = lpe.learning_path_id
416 AND lpe.lp_enrollment_id = p_lp_enrollment_id
417 AND lps.path_source_code = 'CATALOG';
418
419 --- modified by dbatra for source_type='SUITABILITY'
420 Cursor is_suitability_LP is
421 SELECT lps.learning_path_id
422 FROM ota_learning_paths lps,
423 ota_lp_enrollments lpe
424 WHERE lps.learning_path_id = lpe.learning_path_id
425 AND lpe.lp_enrollment_id = p_lp_enrollment_id
426 AND lps.source_function_code = 'SUITABILITY';
427
428 cursor get_lp_type is
429 select lps.path_source_code , lpe.path_status_code
430 from ota_learning_paths lps, ota_lp_enrollments lpe
431 where lps.learning_path_id = lpe.learning_path_id
432 AND lpe.lp_enrollment_id = p_lp_enrollment_id;
433
434 l_type ota_learning_paths.path_source_code%type;
435
436
437 begin
438 hr_utility.set_location('Entering:'|| l_proc, 10);
439 --
440 -- Issue a savepoint
441 --
442 savepoint UPDATE_LP_ENROLLMENT;
443 l_effective_date := trunc(p_effective_date);
444
445 begin
446 ota_lp_enrollment_bk2.update_lp_enrollment_b
447 (p_effective_date => p_effective_date
448 ,p_lp_enrollment_id => p_lp_enrollment_id
449 ,p_object_version_number => l_object_version_number
450 ,p_person_id => p_person_id
451 ,p_contact_id => p_contact_id
452 ,p_path_status_code => p_path_status_code
453 ,p_enrollment_source_code => p_enrollment_source_code
454 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
455 ,p_no_of_completed_courses => p_no_of_completed_courses
456 ,p_completion_target_date => p_completion_target_date
457 ,p_completion_date => p_completion_date
458 ,p_creator_person_id => p_creator_person_id
459 ,p_attribute_category => p_attribute_category
460 ,p_attribute1 => p_attribute1
461 ,p_attribute2 => p_attribute2
462 ,p_attribute3 => p_attribute3
463 ,p_attribute4 => p_attribute4
464 ,p_attribute5 => p_attribute5
465 ,p_attribute6 => p_attribute6
466 ,p_attribute7 => p_attribute7
467 ,p_attribute8 => p_attribute8
468 ,p_attribute9 => p_attribute9
469 ,p_attribute10 => p_attribute10
470 ,p_attribute11 => p_attribute11
471 ,p_attribute12 => p_attribute12
472 ,p_attribute13 => p_attribute13
473 ,p_attribute14 => p_attribute14
474 ,p_attribute15 => p_attribute15
475 ,p_attribute16 => p_attribute16
476 ,p_attribute17 => p_attribute17
477 ,p_attribute18 => p_attribute18
478 ,p_attribute19 => p_attribute19
479 ,p_attribute20 => p_attribute20
480 ,p_attribute21 => p_attribute21
481 ,p_attribute22 => p_attribute22
482 ,p_attribute23 => p_attribute23
483 ,p_attribute24 => p_attribute24
484 ,p_attribute25 => p_attribute25
485 ,p_attribute26 => p_attribute26
486 ,p_attribute27 => p_attribute27
487 ,p_attribute28 => p_attribute28
488 ,p_attribute29 => p_attribute29
489 ,p_attribute30 => p_attribute30
490 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
491 ,p_business_group_id => p_business_group_id
492 ,p_validate => p_validate
493 );
494
495
496 exception
497 when hr_api.cannot_find_prog_unit then
498 hr_api.cannot_find_prog_unit_error
499 (p_module_name => 'UPDATE_LP_ENROLLMENT'
500 ,p_hook_type => 'BP'
501 );
502 end;
503
504 -- fire notifications to Learner
505 open get_lp_type;
506 fetch get_lp_type into l_type, l_path_status_code;
507 close get_lp_type;
508
509 -- check for suitability LP
510 OPEN is_suitability_LP;
511 FETCH is_suitability_LP INTO l_learning_path_id;
512 close is_suitability_LP;
513
514 if l_learning_path_id is not null and p_path_status_code='COMPLETED' then
515 -- don't update lp enrollment status to completed
516 l_proceed :='N';
517 end if;
518
519 --
520 -- Process Logic
521 --
522 if l_proceed = 'Y' then
523 ota_lpe_upd.upd
524 (
525 p_effective_date => p_effective_date
526 ,p_lp_enrollment_id => p_lp_enrollment_id
527 ,p_object_version_number => l_object_version_number
528 ,p_path_status_code => p_path_status_code
529 ,p_enrollment_source_code => p_enrollment_source_code
530 ,p_business_group_id => p_business_group_id
531 ,p_person_id => p_person_id
532 ,p_contact_id => p_contact_id
533 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
534 ,p_no_of_completed_courses => p_no_of_completed_courses
535 ,p_completion_target_date => p_completion_target_date
536 ,p_completion_date => p_completion_date
537 ,p_creator_person_id => p_creator_person_id
538 ,p_attribute_category => p_attribute_category
539 ,p_attribute1 => p_attribute1
540 ,p_attribute2 => p_attribute2
541 ,p_attribute3 => p_attribute3
542 ,p_attribute4 => p_attribute4
543 ,p_attribute5 => p_attribute5
544 ,p_attribute6 => p_attribute6
545 ,p_attribute7 => p_attribute7
546 ,p_attribute8 => p_attribute8
547 ,p_attribute9 => p_attribute9
548 ,p_attribute10 => p_attribute10
549 ,p_attribute11 => p_attribute11
550 ,p_attribute12 => p_attribute12
551 ,p_attribute13 => p_attribute13
552 ,p_attribute14 => p_attribute14
553 ,p_attribute15 => p_attribute15
554 ,p_attribute16 => p_attribute16
555 ,p_attribute17 => p_attribute17
556 ,p_attribute18 => p_attribute18
557 ,p_attribute19 => p_attribute19
558 ,p_attribute20 => p_attribute20
559 ,p_attribute21 => p_attribute21
560 ,p_attribute22 => p_attribute22
561 ,p_attribute23 => p_attribute23
562 ,p_attribute24 => p_attribute24
563 ,p_attribute25 => p_attribute25
564 ,p_attribute26 => p_attribute26
565 ,p_attribute27 => p_attribute27
566 ,p_attribute28 => p_attribute28
567 ,p_attribute29 => p_attribute29
568 ,p_attribute30 => p_attribute30
569 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
570 );
571 end if;
572
573 begin
574 ota_lp_enrollment_bk2.update_lp_enrollment_a
575 (p_effective_date => p_effective_date
576 ,p_lp_enrollment_id => p_lp_enrollment_id
577 ,p_object_version_number => l_object_version_number
578 ,p_person_id => p_person_id
579 ,p_contact_id => p_contact_id
580 ,p_path_status_code => p_path_status_code
581 ,p_enrollment_source_code => p_enrollment_source_code
582 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
583 ,p_no_of_completed_courses => p_no_of_completed_courses
584 ,p_completion_target_date => p_completion_target_date
585 ,p_completion_date => p_completion_date
586 ,p_creator_person_id => p_creator_person_id
587 ,p_attribute_category => p_attribute_category
588 ,p_attribute1 => p_attribute1
589 ,p_attribute2 => p_attribute2
590 ,p_attribute3 => p_attribute3
591 ,p_attribute4 => p_attribute4
592 ,p_attribute5 => p_attribute5
593 ,p_attribute6 => p_attribute6
594 ,p_attribute7 => p_attribute7
595 ,p_attribute8 => p_attribute8
596 ,p_attribute9 => p_attribute9
597 ,p_attribute10 => p_attribute10
598 ,p_attribute11 => p_attribute11
599 ,p_attribute12 => p_attribute12
600 ,p_attribute13 => p_attribute13
601 ,p_attribute14 => p_attribute14
602 ,p_attribute15 => p_attribute15
603 ,p_attribute16 => p_attribute16
604 ,p_attribute17 => p_attribute17
605 ,p_attribute18 => p_attribute18
606 ,p_attribute19 => p_attribute19
607 ,p_attribute20 => p_attribute20
608 ,p_attribute21 => p_attribute21
609 ,p_attribute22 => p_attribute22
610 ,p_attribute23 => p_attribute23
611 ,p_attribute24 => p_attribute24
612 ,p_attribute25 => p_attribute25
613 ,p_attribute26 => p_attribute26
614 ,p_attribute27 => p_attribute27
615 ,p_attribute28 => p_attribute28
616 ,p_attribute29 => p_attribute29
617 ,p_attribute30 => p_attribute30
618 ,p_IS_HISTORY_FLAG => p_IS_HISTORY_FLAG
619 ,p_business_group_id => p_business_group_id
620 ,p_validate => p_validate
621 );
622
623
624 exception
625 when hr_api.cannot_find_prog_unit then
626 hr_api.cannot_find_prog_unit_error
627 (p_module_name => 'UPDATE_LP_ENROLLMENT'
628 ,p_hook_type => 'AP'
629 );
630 end;
631
632
633 --
634 -- When in validation only mode raise the Validate_Enabled exception
635 --
636 if p_validate then
637 raise hr_api.validate_enabled;
638 end if;
639 --
640 -- Set all output arguments
641 --
642 p_object_version_number := l_object_version_number;
643
644 --competency update associated with LP
645 OPEN get_info_for_comp;
646 -- Modified for Bug#3864084
647 FETCH get_info_for_comp INTO l_learning_path_id, l_person_id;
648 IF get_info_for_comp%FOUND THEN
649 IF p_path_status_code = 'COMPLETED' AND l_person_id IS NOT NULL
650 and ('Y' = ota_lrng_path_util.is_path_successful(p_lp_enrollment_id => p_lp_enrollment_id))
651 --Added for bug#5054787
652 AND l_path_status_code <> 'COMPLETED'
653 THEN
654 ota_competence_ss.create_wf_process(p_process => 'OTA_COMPETENCE_UPDATE_JSP_PRC',
655 p_itemtype => 'HRSSA',
656 p_person_id => l_person_id,
657 p_eventid => null,
658 p_learningpath_ids => to_char(l_learning_path_id),
659 p_itemkey => l_item_key);
660 END IF;
661 END IF;
662 CLOSE get_info_for_comp;
663 --
664 -- Added to check and test LP completion notifications
665 -- IF p_path_status_code = 'COMPLETED' THEN
666 IF p_path_status_code = 'COMPLETED'
667 AND l_proceed ='Y'
668 --Added for bug#5054787
669 AND l_path_status_code <> 'COMPLETED' THEN
670 OTA_LP_NOTIFY_SS.create_wf_process(
671 p_lp_notification_type => 'LP_COMPLETE',
672 p_lp_enrollment_id => p_lp_enrollment_id);
673 END IF;
674 -- End of added code to test LP Completion Notifications
675
676 if (l_type = 'CATALOG' or l_type = 'MANAGER') and
677 p_path_status_code = 'CANCELLED' and l_proceed ='Y' then
678
679 OTA_INITIALIZATION_WF.init_lp_wf(p_item_type => 'HRSSA',
680 p_lp_enrollment_id => p_lp_enrollment_id,
681 p_event_fired => 'LP_UNSUBSCRIBE');
682 end if;
683
684 hr_utility.set_location(' Leaving:'||l_proc, 70);
685 exception
686 when hr_api.validate_enabled then
687 --
688 -- As the Validate_Enabled exception has been raised
689 -- we must rollback to the savepoint
690 --
691 rollback to UPDATE_LP_ENROLLMENT;
692 --
693 -- Only set output warning arguments
694 -- (Any key or derived arguments must be set to null
695 -- when validation only mode is being used.)
696 --
697 p_object_version_number := null;
698 hr_utility.set_location(' Leaving:'||l_proc, 80);
699 when others then
700 --
701 -- A validation or unexpected error has occured
702 --
703 rollback to UPDATE_LP_ENROLLMENT;
704 p_object_version_number := l_object_version_number;
705 hr_utility.set_location(' Leaving:'||l_proc, 90);
706 raise;
707 end update_lp_enrollment;
708 --
709 -- ----------------------------------------------------------------------------
710 -- |-------------------------< DELETE_LP_ENROLLMENT >-------------------|
711 -- ----------------------------------------------------------------------------
712 --
713 procedure delete_lp_enrollment
714 (p_lp_enrollment_id in number
715 ,p_object_version_number in number
716 ,p_validate in boolean default false
717
718 ) is
719 --
720 -- Declare cursors and local variables
721 --
722 l_proc varchar2(72) := g_package||'DELETE_LP_ENROLLMENT';
723 --
724 --
725 begin
726 hr_utility.set_location('Entering:'|| l_proc, 10);
727 --
728 -- Issue a savepoint
729 --
730 savepoint DELETE_LP_ENROLLMENT;
731 --
732 -- Truncate the time portion from all IN date parameters
733 --
734 --
735
736 begin
737 ota_lp_enrollment_bk3.delete_lp_enrollment_b
738 (p_lp_enrollment_id => p_lp_enrollment_id
739 ,p_object_version_number => p_object_version_number
740 );
741 exception
742 when hr_api.cannot_find_prog_unit then
743 hr_api.cannot_find_prog_unit_error
744 (p_module_name => 'DELETE_LP_ENROLLMENT'
745 ,p_hook_type => 'BP'
746 );
747 end;
748
749 --
750 -- Process Logic
751 --
752
753 ota_lpe_del.del
754 (
755 p_lp_enrollment_id => p_lp_enrollment_id ,
756 p_object_version_number => p_object_version_number
757 );
758
759
760 begin
761 ota_lp_enrollment_bk3.delete_lp_enrollment_a
762 (p_lp_enrollment_id => p_lp_enrollment_id
763 ,p_object_version_number => p_object_version_number
764 );
765 exception
766 when hr_api.cannot_find_prog_unit then
767 hr_api.cannot_find_prog_unit_error
768 (p_module_name => 'DELETE_LP_ENROLLMENT'
769 ,p_hook_type => 'AP'
770 );
771 end;
772
773 --
774 -- When in validation only mode raise the Validate_Enabled exception
775 --
776 if p_validate then
777 raise hr_api.validate_enabled;
778 end if;
779 --
780 -- Set all output arguments
781 --
782 --
783 hr_utility.set_location(' Leaving:'||l_proc, 170);
784 exception
785 when hr_api.validate_enabled then
786 --
787 -- As the Validate_Enabled exception has been raised
788 -- we must rollback to the savepoint
789 --
790 rollback to DELETE_LP_ENROLLMENT;
791 --
792 -- Only set output warning arguments
793 -- (Any key or derived arguments must be set to null
794 -- when validation only mode is being used.)
795 --
796 hr_utility.set_location(' Leaving:'||l_proc, 180);
797 when others then
798 --
799 -- A validation or unexpected error has occured
800 --
801 rollback to DELETE_LP_ENROLLMENT;
802 hr_utility.set_location(' Leaving:'||l_proc, 190);
803 raise;
804 end delete_lp_enrollment;
805 --
806 -- ----------------------------------------------------------------------------
807 -- |--------------------------< SUBSCRIBE_TO_LEARNING_PATH>-------------------------|
808 -- ----------------------------------------------------------------------------
809 procedure subscribe_to_learning_path
810 (p_validate in boolean default false
811 ,p_learning_path_id IN NUMBER
812 ,p_person_id IN NUMBER default null
813 ,p_contact_id IN NUMBER default null
814 ,p_enrollment_source_code IN VARCHAR2
815 ,p_business_group_id IN NUMBER
816 ,p_creator_person_id IN NUMBER
817 ,p_attribute_category IN VARCHAR2 default NULL
818 ,p_attribute1 IN VARCHAR2 default NULL
819 ,p_attribute2 IN VARCHAR2 default NULL
820 ,p_attribute3 IN VARCHAR2 default NULL
821 ,p_attribute4 IN VARCHAR2 default NULL
822 ,p_attribute5 IN VARCHAR2 default NULL
823 ,p_attribute6 IN VARCHAR2 default NULL
824 ,p_attribute7 IN VARCHAR2 default NULL
825 ,p_attribute8 IN VARCHAR2 default NULL
826 ,p_attribute9 IN VARCHAR2 default NULL
827 ,p_attribute10 IN VARCHAR2 default NULL
828 ,p_attribute11 IN VARCHAR2 default NULL
829 ,p_attribute12 IN VARCHAR2 default NULL
830 ,p_attribute13 IN VARCHAR2 default NULL
831 ,p_attribute14 IN VARCHAR2 default NULL
832 ,p_attribute15 IN VARCHAR2 default NULL
833 ,p_attribute16 IN VARCHAR2 default NULL
834 ,p_attribute17 IN VARCHAR2 default NULL
835 ,p_attribute18 IN VARCHAR2 default NULL
836 ,p_attribute19 IN VARCHAR2 default NULL
837 ,p_attribute20 IN VARCHAR2 default NULL
838 ,p_attribute21 IN VARCHAR2 default NULL
839 ,p_attribute22 IN VARCHAR2 default NULL
840 ,p_attribute23 IN VARCHAR2 default NULL
841 ,p_attribute24 IN VARCHAR2 default NULL
842 ,p_attribute25 IN VARCHAR2 default NULL
843 ,p_attribute26 IN VARCHAR2 default NULL
844 ,p_attribute27 IN VARCHAR2 default NULL
845 ,p_attribute28 IN VARCHAR2 default NULL
846 ,p_attribute29 IN VARCHAR2 default NULL
847 ,p_attribute30 IN VARCHAR2 default NULL
848 ,p_lp_enrollment_id OUT NOCOPY number
849 ,p_path_status_code OUT NOCOPY VARCHAR2
850 ) IS
851 l_total_number_of_courses number;
852
853 CURSOR csr_get_lp_details IS
854 select duration
855 from ota_learning_paths
856 where learning_path_id = p_learning_path_id;
857
858 CURSOR csr_get_lpms IS
859 select learning_path_member_id, activity_version_id, duration, learning_path_section_id
860 from ota_learning_path_members
861 where learning_path_id = p_learning_path_id;
862
863 CURSOR csr_get_lpe_details IS
864 select path_status_code, no_of_completed_courses, object_version_number, completion_date
865 FROM ota_lp_enrollments
866 where lp_enrollment_id = p_lp_enrollment_id;
867
868 l_proc varchar2(72) := g_package || ' subscribe_to_learning_path';
869 l_lp_rec csr_get_lp_details%ROWTYPE;
870 l_lpm_rec csr_get_lpms%ROWTYPE;
871 l_lpe_rec csr_get_lpe_details%ROWTYPE;
872 l_no_of_completed_courses NUMBER;
873 l_completion_status VARCHAR2(30);
874 l_object_version_number1 number;
875 l_object_version_number2 number;
876 l_completion_date DATE;
877 l_lp_member_enrollment_id NUMBER;
878
879 l_attribute_category VARCHAR2(30) := p_attribute_category;
880 l_attribute1 VARCHAR2(150) := p_attribute1 ;
881 l_attribute2 VARCHAR2(150) := p_attribute2 ;
882 l_attribute3 VARCHAR2(150) := p_attribute3 ;
883 l_attribute4 VARCHAR2(150) := p_attribute4 ;
884 l_attribute5 VARCHAR2(150) := p_attribute5 ;
885 l_attribute6 VARCHAR2(150) := p_attribute6 ;
886 l_attribute7 VARCHAR2(150) := p_attribute7 ;
887 l_attribute8 VARCHAR2(150) := p_attribute8 ;
888 l_attribute9 VARCHAR2(150) := p_attribute9 ;
889 l_attribute10 VARCHAR2(150) := p_attribute10 ;
890 l_attribute11 VARCHAR2(150) := p_attribute11 ;
891 l_attribute12 VARCHAR2(150) := p_attribute12 ;
892 l_attribute13 VARCHAR2(150) := p_attribute13 ;
893 l_attribute14 VARCHAR2(150) := p_attribute14 ;
894 l_attribute15 VARCHAR2(150) := p_attribute15 ;
895 l_attribute16 VARCHAR2(150) := p_attribute16 ;
896 l_attribute17 VARCHAR2(150) := p_attribute17 ;
897 l_attribute18 VARCHAR2(150) := p_attribute18 ;
898 l_attribute19 VARCHAR2(150) := p_attribute19 ;
899 l_attribute20 VARCHAR2(150) := p_attribute20 ;
900 l_attribute21 VARCHAR2(150) := p_attribute21 ;
901 l_attribute22 VARCHAR2(150) := p_attribute22 ;
902 l_attribute23 VARCHAR2(150) := p_attribute23 ;
903 l_attribute24 VARCHAR2(150) := p_attribute24 ;
904 l_attribute25 VARCHAR2(150) := p_attribute25 ;
905 l_attribute26 VARCHAR2(150) := p_attribute26 ;
906 l_attribute27 VARCHAR2(150) := p_attribute27 ;
907 l_attribute28 VARCHAR2(150) := p_attribute28 ;
908 l_attribute29 VARCHAR2(150) := p_attribute29 ;
909 l_attribute30 VARCHAR2(150) := p_attribute30 ;
910
911 BEGIN
912
913 l_total_number_of_courses := ota_lrng_path_util.get_no_of_mandatory_courses(p_learning_path_id => p_learning_path_id
914 ,p_path_source_code => 'CATALOG');
915 OPEN csr_get_lp_details;
916 FETCH csr_get_lp_details INTO l_lp_rec;
917 CLOSE csr_get_lp_details;
918
919 savepoint create_lp_subscription;
920 ota_utility.Get_Default_Value_Dff(
921 appl_short_name => 'OTA'
922 ,flex_field_name => 'OTA_LP_ENROLLMENTS'
923 ,p_attribute_category => l_attribute_category
924 ,p_attribute1 => l_attribute1
925 ,p_attribute2 => l_attribute2
926 ,p_attribute3 => l_attribute3
927 ,p_attribute4 => l_attribute4
928 ,p_attribute5 => l_attribute5
929 ,p_attribute6 => l_attribute6
930 ,p_attribute7 => l_attribute7
931 ,p_attribute8 => l_attribute8
932 ,p_attribute9 => l_attribute9
933 ,p_attribute10 => l_attribute10
934 ,p_attribute11 => l_attribute11
935 ,p_attribute12 => l_attribute12
936 ,p_attribute13 => l_attribute13
937 ,p_attribute14 => l_attribute14
938 ,p_attribute15 => l_attribute15
939 ,p_attribute16 => l_attribute16
940 ,p_attribute17 => l_attribute17
941 ,p_attribute18 => l_attribute18
942 ,p_attribute19 => l_attribute19
943 ,p_attribute20 => l_attribute20
944 ,p_attribute21 => l_attribute21
945 ,p_attribute22 => l_attribute22
946 ,p_attribute23 => l_attribute23
947 ,p_attribute24 => l_attribute24
948 ,p_attribute25 => l_attribute25
949 ,p_attribute26 => l_attribute26
950 ,p_attribute27 => l_attribute27
951 ,p_attribute28 => l_attribute28
952 ,p_attribute29 => l_attribute29
953 ,p_attribute30 => l_attribute30);
954
955 ota_lp_enrollment_api.create_lp_enrollment(
956 p_effective_date => trunc(sysdate)
957 ,p_validate => p_validate
958 ,p_learning_path_id => p_learning_path_id
959 ,p_person_id => p_person_id
960 ,p_contact_id => p_contact_id
961 ,p_path_status_code => 'ACTIVE'
962 ,p_enrollment_source_code => p_enrollment_source_code
963 ,p_no_of_mandatory_courses =>l_total_number_of_courses
964 ,p_completion_target_date => sysdate + l_lp_rec.duration -1
965 ,p_business_group_id => p_business_group_id
966 ,p_lp_enrollment_id => p_lp_enrollment_id
967 ,p_object_version_number => l_object_version_number1
968 ,p_attribute_category => l_attribute_category
969 ,p_attribute1 => l_attribute1
970 ,p_attribute2 => l_attribute2
971 ,p_attribute3 => l_attribute3
972 ,p_attribute4 => l_attribute4
973 ,p_attribute5 => l_attribute5
974 ,p_attribute6 => l_attribute6
975 ,p_attribute7 => l_attribute7
976 ,p_attribute8 => l_attribute8
977 ,p_attribute9 => l_attribute9
978 ,p_attribute10 => l_attribute10
979 ,p_attribute11 => l_attribute11
980 ,p_attribute12 => l_attribute12
981 ,p_attribute13 => l_attribute13
982 ,p_attribute14 => l_attribute14
983 ,p_attribute15 => l_attribute15
984 ,p_attribute16 => l_attribute16
985 ,p_attribute17 => l_attribute17
986 ,p_attribute18 => l_attribute18
987 ,p_attribute19 => l_attribute19
988 ,p_attribute20 => l_attribute20
989 ,p_attribute21 => l_attribute21
990 ,p_attribute22 => l_attribute22
991 ,p_attribute23 => l_attribute23
992 ,p_attribute24 => l_attribute24
993 ,p_attribute25 => l_attribute25
994 ,p_attribute26 => l_attribute26
995 ,p_attribute27 => l_attribute27
996 ,p_attribute28 => l_attribute28
997 ,p_attribute29 => l_attribute29
998 ,p_attribute30 => l_attribute30
999 -- Added for bug#5161382
1000 ,p_creator_person_id => p_creator_person_id
1001 );
1002 l_attribute_category := NULL;
1003 l_attribute1 := NULL ;
1004 l_attribute2 := NULL ;
1005 l_attribute3 := NULL ;
1006 l_attribute4 := NULL ;
1007 l_attribute5 := NULL ;
1008 l_attribute6 := NULL ;
1009 l_attribute7 := NULL ;
1010 l_attribute8 := NULL ;
1011 l_attribute9 := NULL ;
1012 l_attribute10 := NULL ;
1013 l_attribute11 := NULL ;
1014 l_attribute12 := NULL ;
1015 l_attribute13 := NULL ;
1016 l_attribute14 := NULL ;
1017 l_attribute15 := NULL ;
1018 l_attribute16 := NULL ;
1019 l_attribute17 := NULL ;
1020 l_attribute18 := NULL ;
1021 l_attribute19 := NULL ;
1022 l_attribute20 := NULL ;
1023 l_attribute21 := NULL ;
1024 l_attribute22 := NULL ;
1025 l_attribute23 := NULL ;
1026 l_attribute24 := NULL ;
1027 l_attribute25 := NULL ;
1028 l_attribute26 := NULL ;
1029 l_attribute27 := NULL ;
1030 l_attribute28 := NULL ;
1031 l_attribute29 := NULL ;
1032 l_attribute30 := NULL ;
1033
1034 ota_utility.Get_Default_Value_Dff(
1035 appl_short_name => 'OTA'
1036 ,flex_field_name => 'OTA_LP_MEMBER_ENROLLMENTS'
1037 ,p_attribute_category => l_attribute_category
1038 ,p_attribute1 => l_attribute1
1039 ,p_attribute2 => l_attribute2
1040 ,p_attribute3 => l_attribute3
1041 ,p_attribute4 => l_attribute4
1042 ,p_attribute5 => l_attribute5
1043 ,p_attribute6 => l_attribute6
1044 ,p_attribute7 => l_attribute7
1045 ,p_attribute8 => l_attribute8
1046 ,p_attribute9 => l_attribute9
1047 ,p_attribute10 => l_attribute10
1048 ,p_attribute11 => l_attribute11
1049 ,p_attribute12 => l_attribute12
1050 ,p_attribute13 => l_attribute13
1051 ,p_attribute14 => l_attribute14
1052 ,p_attribute15 => l_attribute15
1053 ,p_attribute16 => l_attribute16
1054 ,p_attribute17 => l_attribute17
1055 ,p_attribute18 => l_attribute18
1056 ,p_attribute19 => l_attribute19
1057 ,p_attribute20 => l_attribute20
1058 ,p_attribute21 => l_attribute21
1059 ,p_attribute22 => l_attribute22
1060 ,p_attribute23 => l_attribute23
1061 ,p_attribute24 => l_attribute24
1062 ,p_attribute25 => l_attribute25
1063 ,p_attribute26 => l_attribute26
1064 ,p_attribute27 => l_attribute27
1065 ,p_attribute28 => l_attribute28
1066 ,p_attribute29 => l_attribute29
1067 ,p_attribute30 => l_attribute30);
1068 FOR l_lpm_rec IN csr_get_lpms LOOP
1069 ota_lp_member_enrollment_api.create_lp_member_enrollment(
1070 p_effective_date => trunc(sysdate)
1071 ,p_validate => p_validate
1072 ,p_lp_enrollment_id => p_lp_enrollment_id
1073 ,p_learning_path_section_id => l_lpm_rec.learning_path_section_id
1074 ,p_learning_path_member_id => l_lpm_rec.learning_path_member_id
1075 ,p_member_status_code => 'PLANNED'
1076 ,p_completion_target_date => l_lpm_rec.duration + sysdate -1
1077 ,p_business_group_id => p_business_group_id
1078 ,p_lp_member_enrollment_id => l_lp_member_enrollment_id
1079 ,p_object_version_number => l_object_version_number2
1080 ,p_attribute_category => l_attribute_category
1081 ,p_attribute1 => l_attribute1
1082 ,p_attribute2 => l_attribute2
1083 ,p_attribute3 => l_attribute3
1084 ,p_attribute4 => l_attribute4
1085 ,p_attribute5 => l_attribute5
1086 ,p_attribute6 => l_attribute6
1087 ,p_attribute7 => l_attribute7
1088 ,p_attribute8 => l_attribute8
1089 ,p_attribute9 => l_attribute9
1090 ,p_attribute10 => l_attribute10
1091 ,p_attribute11 => l_attribute11
1092 ,p_attribute12 => l_attribute12
1093 ,p_attribute13 => l_attribute13
1094 ,p_attribute14 => l_attribute14
1095 ,p_attribute15 => l_attribute15
1096 ,p_attribute16 => l_attribute16
1097 ,p_attribute17 => l_attribute17
1098 ,p_attribute18 => l_attribute18
1099 ,p_attribute19 => l_attribute19
1100 ,p_attribute20 => l_attribute20
1101 ,p_attribute21 => l_attribute21
1102 ,p_attribute22 => l_attribute22
1103 ,p_attribute23 => l_attribute23
1104 ,p_attribute24 => l_attribute24
1105 ,p_attribute25 => l_attribute25
1106 ,p_attribute26 => l_attribute26
1107 ,p_attribute27 => l_attribute27
1108 ,p_attribute28 => l_attribute28
1109 ,p_attribute29 => l_attribute29
1110 ,p_attribute30 => l_attribute30 );
1111 END LOOP;
1112
1113 OPEN csr_get_lpe_details;
1114 FETCH csr_get_lpe_details INTO l_lpe_rec;
1115 CLOSE csr_get_lpe_details;
1116
1117 l_no_of_completed_courses := ota_lrng_path_util.get_no_of_completed_courses(
1118 p_lp_enrollment_id => p_lp_enrollment_id,
1119 p_path_source_code => 'CATALOG');
1120 p_path_status_code := l_lpe_rec.path_status_code;
1121 l_completion_date := l_lpe_rec.completion_date;
1122
1123 --Bug 4615753
1124 --this update is reqd for no_of_completed_courses values at lpe
1125 --which wouldn't be populated otherwise and call to chk_complete_path_ok
1126 --would never return 'S' even if all its components are in Completed status
1127 ota_lp_enrollment_api.update_lp_enrollment
1128 (p_effective_date => sysdate
1129 ,p_validate => p_validate
1130 ,p_lp_enrollment_id => p_lp_enrollment_id
1131 ,p_object_version_number => l_lpe_rec.object_version_number
1132 ,p_no_of_completed_courses => l_no_of_completed_courses
1133 ,p_path_status_code => p_path_status_code
1134 ,p_completion_date => l_completion_date);
1135
1136 l_completion_status := ota_lrng_path_util.chk_complete_path_ok(p_lp_enrollment_id => p_lp_enrollment_id);
1137
1138 IF l_completion_status = 'S' THEN
1139 p_path_status_code := 'COMPLETED';
1140 --l_completion_date := trunc(sysdate);
1141 l_completion_date := ota_lrng_path_member_util.get_lp_completion_date(p_lp_enrollment_id);
1142 --mark lpe as Completed
1143 OPEN csr_get_lpe_details;
1144 FETCH csr_get_lpe_details INTO l_lpe_rec;
1145 CLOSE csr_get_lpe_details;
1146
1147 ota_lp_enrollment_api.update_lp_enrollment
1148 (p_effective_date => sysdate
1149 ,p_validate => p_validate
1150 ,p_lp_enrollment_id => p_lp_enrollment_id
1151 ,p_object_version_number => l_lpe_rec.object_version_number
1152 ,p_path_status_code => p_path_status_code
1153 ,p_completion_date => l_completion_date);
1154 END IF;
1155
1156 if p_validate then
1157 raise hr_api.validate_enabled;
1158 end if;
1159
1160 exception
1161 when hr_api.validate_enabled then
1162 --
1163 --
1164 -- Catch the Multiple Message List exception which
1165 -- indicates API processing has been aborted because
1166 -- at least one message exists in the list.
1167 --
1168 rollback to create_lp_subscription;
1169 --
1170 -- Reset IN OUT parameters and set OUT parameters
1171 --
1172 p_lp_enrollment_id := null;
1173 p_path_status_code := null;
1174 hr_utility.set_location(' Leaving:' || l_proc, 30);
1175
1176 when others then
1177 --
1178 -- A validation or unexpected error has occured
1179 --
1180 rollback to create_lp_subscription;
1181 --
1182 -- Reset IN OUT and set OUT parameters
1183 --
1184 p_lp_enrollment_id := null;
1185 p_path_status_code := null;
1186
1187 hr_utility.set_location(' Leaving:' || l_proc,50);
1188 raise;
1189 END subscribe_to_learning_path;
1190 end ota_lp_enrollment_api;
1191