[Home] [Help]
PACKAGE BODY: APPS.OTA_TRNG_PLAN_UTIL_SS
Source
1 PACKAGE BODY OTA_TRNG_PLAN_UTIL_SS as
2 /* $Header: ottpswrs.pkb 115.15 2004/08/29 23:33:55 rdola noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := 'ota_trng_plan_util_ss.'; -- Global package name
9
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< chk_cancel_plan_ok >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 Function chk_cancel_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
15 return varchar2
16 is
17
18 Cursor any_child is
19 Select tpm.training_plan_member_id from
20 ota_Training_plan_members tpm
21 where member_status_type_id<>'CANCELLED'
22 and training_plan_id=p_training_plan_id and rownum=1;
23
24 l_proc VARCHAR2(72) := g_package ||'chk_cancel_plan_ok';
25 l_exists Number(9);
26 l_result varchar2(3) :='F';
27
28 Begin
29
30 hr_utility.set_location(' Entering:' || l_proc,10);
31
32 open any_child;
33 fetch any_child into l_exists;
34 if any_child%NOTFOUND then
35 l_result :='S';
36 end if;
37 close any_child;
38
39 return l_result;
40
41 end chk_cancel_plan_ok;
42
43 -- ---------------------------------------------------------------------------
44 -- |----------------------< chk_complete_plan_ok >--------------------------|
45 -- ---------------------------------------------------------------------------
46 --
47
48 Function chk_complete_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
49 return varchar2
50 IS
51
52 Cursor any_child is
53 Select tpm.training_plan_member_id from
54 ota_Training_plan_members tpm
55 where member_status_type_id<>'CANCELLED'
56 and member_status_type_id<>'OTA_COMPLETED'
57 and training_plan_id=p_training_plan_id and rownum=1;
58
59 Cursor one_child_completed is
60 Select tpm.training_plan_member_id from
61 ota_training_plan_members tpm
62 where member_status_type_id='OTA_COMPLETED'
63 and training_plan_id=p_training_plan_id and rownum=1;
64
65 l_proc VARCHAR2(72) := g_package ||'chk_complete_plan_ok';
66 l_exists Number(9);
67 l_complete Number(9);
68 l_result varchar2(3) :='F';
69
70 Begin
71
72 hr_utility.set_location(' Entering:' || l_proc,10);
73
74 open any_child;
75 fetch any_child into l_exists;
76 if any_child%NOTFOUND then
77 open one_child_completed;
78 fetch one_child_completed into l_complete;
79 if one_child_completed%found then
80 l_result :='S';
81 end if;
82 close one_child_completed;
83 end if;
84 close any_child;
85
86 return l_result;
87
88 end chk_complete_plan_ok;
89
90
91 -- ---------------------------------------------------------------------------
92 -- |----------------------< get_enroll_status >--------------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
96 p_contact_id IN ota_training_plans.contact_id%TYPE,
97 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
98 RETURN VARCHAR2 IS
99
100
101 CURSOR enroll_status IS
102 SELECT DECODE(bst.type,'C','Z',bst.type) status
103 FROM ota_training_plan_members tpm,
104 ota_events evt,
105 ota_delegate_bookings tdb,
106 ota_booking_status_types bst,
107 ota_training_plans tps
108 WHERE tpm.activity_version_id=evt.activity_version_id
109 AND evt.event_id = tdb.event_id
110 AND bst.booking_status_type_id = tdb.booking_status_type_id
111 -- and bst.active_flag='Y'
112 AND tps.training_plan_id = tpm.training_plan_id
113 -- Modified for Bug#3855721
114 AND (tps.learning_path_id IS NOT NULL OR (
115 evt.course_start_date >= tpm.earliest_start_date
116 AND
117 (
118 evt.course_end_date IS NOT NULL
119 AND evt.course_end_date <= tpm.target_completion_date
120 )
121 OR
122 (
123 evt.event_type = 'SELFPACED'
124 AND tpm.target_completion_date >= evt.course_start_date
125 )
126 ))
127 AND tpm.training_plan_member_id = p_training_plan_member_id
128 -- Modified for Bug#3855721
129 --AND tdb.delegate_person_id = p_person_id
130 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
131 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id))
132 ORDER BY status;
133
134 l_proc VARCHAR2(72) := g_package|| 'get_enroll_status';
135
136 v_enroll_status VARCHAR2(30);
137
138 BEGIN
139
140 hr_utility.set_location(' Step:'|| l_proc, 10);
141 FOR rec IN enroll_status
142 LOOP
143 v_enroll_status :=rec.status ;
144 EXIT;
145 END LOOP;
146
147 RETURN v_enroll_status;
148
149 hr_utility.set_location(' Step:'|| l_proc, 20);
150
151 END get_enroll_status;
152
153 -- ---------------------------------------------------------------------------
154 -- |----------------------< chk_login_person >--------------------------|
155 -- ---------------------------------------------------------------------------
156 --
157 FUNCTION chk_login_person(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
158 RETURN VARCHAR2
159 IS
160
161 l_person_id ota_training_plans.person_id%TYPE;
162 l_login_person ota_training_plans.person_id%TYPE;
163 l_login_customer NUMBER;
164 l_contact_id ota_training_plans.contact_id%TYPE;
165 l_proc VARCHAR2(72) := g_package|| 'chk_login_person';
166
167 BEGIN
168
169 hr_utility.set_location(' Step:'|| l_proc, 10);
170
171 -- Modified for Bug#3479186
172 SELECT tps.person_id,tps.contact_id
173 INTO l_person_id, l_contact_id
174 FROM ota_training_plans tps
175 WHERE tps.training_plan_id = p_training_plan_id;
176
177 SELECT employee_id, customer_id
178 INTO l_login_person, l_login_customer
179 FROM fnd_user
180 WHERE user_id = fnd_profile.value('USER_ID');
181
182 hr_utility.set_location(' Step:'|| l_proc, 20);
183 IF l_login_person IS NOT NULL THEN
184 IF l_login_person = l_person_id THEN
185 RETURN 'E';
186 ELSE
187 RETURN 'M';
188 END IF;
189 ELSIF l_login_customer IS NOT NULL THEN
190 RETURN 'E';
191 END IF;
192
193
194
195 hr_utility.set_location(' Step:'|| l_proc, 30);
196 END chk_login_person;
197
198
199 -- ---------------------------------------------------------------------------
200 -- |----------------------< chk_src_func_TLNTMGT >--------------------------|
201 -- ---------------------------------------------------------------------------
202 --
203
204 FUNCTION chk_src_func_tlntmgt(p_person_id IN ota_training_plans.person_id%TYPE
205 -- ,p_source_function in ota_training_plan_members.source_function%type
206 ,p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE
207 ,p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE
208 -- Added for Bug#3108246
209 ,p_business_group_id IN number)
210 RETURN number
211 IS
212
213 CURSOR csr_get_TP IS
214 SELECT tps.Training_plan_id
215 FROM
216 ota_training_plans tps
217 WHERE
218 p_earliest_start_date >= tps.start_date
219 AND (tps.end_date IS NOT NULL AND p_target_completion_date <= tps.end_date)
220 AND tps.plan_source = 'TALENT_MGMT'
221 AND tps.person_id = p_person_id
222 -- Added for Bug#3493925
223 AND tps.plan_status_type_id = 'ACTIVE'
224 -- Added for Bug#3108246
225 AND tps.business_group_id = p_business_group_id
226 AND (tps.additional_member_flag is null or tps.additional_member_flag<>'N');
227
228 l_proc VARCHAR2(72) := g_package|| 'chk_src_func_tlntmgt';
229
230 l_training_plan_id number(9) :=0;
231
232 BEGIN
233
234 hr_utility.set_location(' Step:'|| l_proc, 10);
235
236 OPEN csr_get_TP ;
237 FETCH csr_get_TP INTO l_training_plan_id;
238
239 CLOSE csr_get_TP;
240 RETURN l_training_plan_id;
241
242 hr_utility.set_location(' Step:'|| l_proc, 20);
243
244 END chk_src_func_tlntmgt;
245
246 -- ---------------------------------------------------------------------------
247 -- |----------------------< chk_valid_act_version_dates >--------------------------|
248 -- ---------------------------------------------------------------------------
249 --
250
251 PROCEDURE chk_valid_act_version_dates
252 (p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE
253 ,p_start_date IN ota_activity_versions.start_date%TYPE
254 ,p_end_date IN ota_activity_versions.end_date%TYPE)
255
256 IS
257
258
259 CURSOR csr_attached_TPC
260 IS
261 SELECT tpm.training_plan_member_id
262 FROM ota_training_plan_members tpm
263 WHERE tpm.activity_version_id = p_activity_version_id
264 AND (tpm.earliest_start_date < p_start_date
265 or (p_end_date IS NOT NULL AND tpm.target_completion_date > p_end_date))
266 and tpm.member_status_type_id <> 'CANCELLED'
267 AND ROWNUM=1;
268
269 l_proc VARCHAR2(72) := g_package|| 'chk_valid_act_version_dates';
270
271 l_act_version_id number(9);
272
273 BEGIN
274
275 hr_utility.set_location(' Step:'|| l_proc, 10);
276
277 OPEN csr_attached_TPC;
278 FETCH csr_attached_TPC INTO l_act_version_id;
279 IF csr_attached_TPC%FOUND THEN
280 CLOSE csr_attached_TPC;
281 fnd_message.set_name('OTA', 'OTA_13186_TPM_ACT_DATES');
282 fnd_message.raise_error;
283 ELSE
284 CLOSE csr_attached_TPC;
285 END IF;
286
287 hr_utility.set_location(' Step:'|| l_proc, 20);
288 END chk_valid_act_version_dates;
289
290 -- ---------------------------------------------------------------------------
291 -- |----------------------< chk_enrollment_exist >--------------------------|
292 -- ---------------------------------------------------------------------------
293
294 FUNCTION chk_enrollment_exist ( p_person_id IN ota_training_plans.person_id%TYPE,
295 p_contact_id IN ota_training_plans.contact_id%TYPE,
296 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
297 RETURN boolean
298 IS
299 CURSOR chk_enr IS
300 SELECT NULL
301 FROM ota_events e,
302 ota_activity_versions a,
303 ota_delegate_bookings b,
304 ota_booking_status_types s,
305 ota_training_plan_members tpm
306 WHERE e.event_id = b.event_id
307 AND tpm.activity_version_id = a.activity_version_id
308 AND ((e.course_start_date >= tpm.earliest_start_date
309 AND e.course_end_date <= tpm.target_completion_date )
310 OR
311 (e.event_type ='SELFPACED'
312 AND e.course_start_date< tpm.target_completion_date
313 AND e.course_end_date >= tpm.target_completion_date
314 ))
315 AND e.activity_version_id = a.activity_version_id
316 AND b.booking_status_type_id = s.booking_status_type_id
317 -- Modified for Bug#3479186
318 -- AND b.delegate_person_id = p_person_id
319 AND ((p_person_id IS NOT NULL AND b.delegate_person_id = p_person_id)
320 OR (p_contact_id IS NOT NULL AND b.delegate_contact_id = p_contact_id)
321 )
322 AND tpm.training_plan_member_id = p_training_plan_member_id;
323
324 l_proc VARCHAR2(72) := g_package|| 'chk_enrollment_exist';
325 l_return_val VARCHAR2(1);
326 l_found BOOLEAN := FALSE;
327
328 BEGIN
329
330 hr_utility.set_location(' Step:'|| l_proc, 10);
331
332 OPEN chk_enr;
333 FETCH chk_enr INTO l_return_val;
334 IF chk_enr%FOUND THEN
335 --
336 l_found := TRUE;
337 --
338 END IF;
339 CLOSE chk_enr;
340 --
341 hr_utility.set_location('Leaving '||l_proc,10);
342
343 RETURN l_found;
344
345 END chk_enrollment_exist;
346
347 -- ---------------------------------------------------------------------------
348 -- |----------------------< get_enroll_status >--------------------------|
349 -- ---------------------------------------------------------------------------
350 -- DECODE(bst.type,'C','Z',bst.type): Decode is to get the Cancelled enrollments
351 -- in the end because the attended overrules all others and 'P', 'R', 'W'
352 -- overrules 'C'.
353
354 FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
355 p_contact_id IN ota_training_plans.contact_id%TYPE,
356 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
357 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
358 p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE,
359 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
360 p_action IN VARCHAR2)
361 RETURN VARCHAR2 IS
362
363
364 CURSOR enroll_status_dates IS
365 SELECT DECODE(bst.type,'C','Z',bst.type) status
366 FROM ota_events evt,
367 ota_delegate_bookings tdb,
368 ota_booking_status_types bst
369 WHERE evt.event_id = tdb.event_id
370 AND bst.booking_status_type_id = tdb.booking_status_type_id
371 AND (
372 evt.course_start_date >= p_earliest_start_date
373 AND
374 (
375 evt.course_end_date IS NOT NULL
376 AND evt.course_end_date <= p_target_completion_date
377 )
378 OR
379 (
380 evt.event_type = 'SELFPACED'
381 AND p_target_completion_date >= evt.course_start_date
382 )
383 )
384 AND evt.activity_version_id = p_activity_version_id
385 --AND tdb.delegate_person_id = p_person_id
386 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
387 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
388 )
389 ORDER BY status;
390
391 CURSOR enroll_status_without_dates IS
392 SELECT DECODE(bst.type,'C','Z',bst.type) status
393 FROM ota_events evt,
394 ota_delegate_bookings tdb,
395 ota_booking_status_types bst
396 WHERE evt.event_id = tdb.event_id
397 AND bst.booking_status_type_id = tdb.booking_status_type_id
398 AND evt.activity_version_id = p_activity_version_id
399 -- Modified for Bug#3479186
400 -- AND tdb.delegate_person_id = p_person_id
401 AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
402 OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
403 )
404 ORDER BY status;
405
406 CURSOR csr_plan_source IS
407 SELECT tps.plan_source
408 FROM ota_training_plans tps
409 where tps.training_plan_id = p_training_plan_id;
410
411 l_proc VARCHAR2(72) := g_package|| 'get_enroll_status';
412
413 v_enroll_status VARCHAR2(30);
414
415 l_plan_source varchar2(30);
416
417 BEGIN
418
419 hr_utility.set_location(' Step:'|| l_proc, 10);
420
421 -- Remove the original date checks for creating members of non tlnt mgmt sources.
422 -- This is requied to determine past completed course before enrolling or creating a LP
423 open csr_plan_source;
424 fetch csr_plan_source into l_plan_source;
425 close csr_plan_source;
426
427 if (l_plan_source <> 'CATALOG') then
428 FOR rec_dates IN enroll_status_dates
429 LOOP
430 v_enroll_status := rec_dates.status ;
431 EXIT;
432 END LOOP;
433 else
434 FOR rec_without_dates IN enroll_status_without_dates
435 LOOP
436 v_enroll_status := rec_without_dates.status ;
437 EXIT;
438 END LOOP;
439 end if;
440
441 RETURN v_enroll_status;
442
443 hr_utility.set_location(' Step:'|| l_proc, 20);
444
445 END get_enroll_status;
446
447 -- ---------------------------------------------------------------------------
448 -- |----------------------< modify_tpc_status_on_create >--------------------------|
449 -- ---------------------------------------------------------------------------
450 --
451
452 PROCEDURE modify_tpc_status_on_create(--p_person_id IN ota_training_plans.person_id%TYPE,
453 -- p_contact_id IN ota_training_plans.contact_id%TYPE,
454 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
455 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
456 p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
457 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
458 p_member_status_id OUT nocopy VARCHAR2)
459 IS
460
461 l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_create';
462 l_enroll_status VARCHAR2(30);
463 l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
464 l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
465
466 BEGIN
467
468 hr_utility.set_location('Entering:'|| l_proc, 10);
469
470 -- Modified for Bug#3479186
471 SELECT tp.person_id, tp.contact_id
472 INTO l_person_id , l_contact_id
473 FROM ota_training_plans tp
474 where tp.training_plan_id = p_training_plan_id;
475
476
477 l_enroll_status := get_enroll_status(p_person_id => l_person_id,
478 p_contact_id => l_contact_id,
479 p_earliest_start_date => p_earliest_start_date,
480 p_target_completion_date => p_target_completion_date,
481 p_activity_version_id => p_activity_version_id,
482 p_training_plan_id => p_training_plan_id,
483 p_action => 'CREATE' );
484
485 IF ( l_enroll_status='A' ) THEN
486
487 p_member_status_id := 'OTA_COMPLETED';
488
489 ELSIF ( l_enroll_status='P'
490 OR l_enroll_status='W'
491 OR l_enroll_status ='R') THEN
492
493 p_member_status_id := 'ACTIVE';
494
495 ELSE
496 p_member_status_id := 'OTA_PLANNED';
497
498 END IF;
499
500 hr_utility.set_location('LEAVING:'|| l_proc, 20);
501
502 EXCEPTION
503 WHEN others THEN
504
505 p_member_status_id := NULL;
506
507 RAISE;
508
509 END modify_tpc_status_on_create;
510
511 -- ---------------------------------------------------------------------------
512 -- |----------------------< modify_tpc_status_on_update >--------------------------|
513 -- ---------------------------------------------------------------------------
514 --
515
516 PROCEDURE modify_tpc_status_on_update(--p_person_id IN ota_training_plans.person_id%TYPE,
517 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
518 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
519 p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
520 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
521 p_member_status_id OUT nocopy VARCHAR2)
522 IS
523
524 l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_update';
525 l_enroll_status VARCHAR2(30);
526 l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
527 l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
528
529 BEGIN
530
531 hr_utility.set_location('Entering:'|| l_proc, 10);
532
533 -- Modified for Bug#3479186
534 SELECT tp.person_id, tp.contact_id
535 INTO l_person_id , l_contact_id
536 FROM ota_training_plans tp
537 where tp.training_plan_id = p_training_plan_id;
538
539
540 l_enroll_status := get_enroll_status(p_person_id => l_person_id,
541 p_contact_id => l_contact_id,
542 p_earliest_start_date => p_earliest_start_date,
543 p_target_completion_date => p_target_completion_date,
544 p_activity_version_id => p_activity_version_id,
545 p_training_plan_id => p_training_plan_id,
546 p_action => 'UPDATE' );
547
548 IF ( l_enroll_status='A' ) THEN
549
550 p_member_status_id := 'OTA_COMPLETED';
551
552 ELSIF ( l_enroll_status='P'
553 OR l_enroll_status='W'
554 OR l_enroll_status ='R') THEN
555
556 p_member_status_id := 'ACTIVE';
557
558 ELSE
559 p_member_status_id := 'OTA_PLANNED';
560
561 END IF;
562
563 hr_utility.set_location('LEAVING:'|| l_proc, 20);
564
565 EXCEPTION
566 WHEN others THEN
567
568 p_member_status_id := NULL;
569
570 RAISE;
571
572 END modify_tpc_status_on_update;
573
574 -- ----------------------------------------------------------------------------
575 -- |---------------------------< get_person_id >----------------------------|
576 -- ----------------------------------------------------------------------------
577
578 FUNCTION get_person_id(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
579 RETURN number
580 IS
581
582 CURSOR csr_person_id IS
583 SELECT person_id
584 FROM ota_training_plans
585 WHERE training_plan_id = p_training_plan_id;
586
587 l_person_id number(9) := 0;
588
589 BEGIN
590
591 OPEN csr_person_id;
592 FETCH csr_person_id INTO l_person_id;
593 CLOSE csr_person_id;
594
595 IF l_person_id is null then
596 l_person_id := 0;
597 END IF;
598
599 RETURN l_person_id;
600
601 END get_person_id;
602
603 -- ----------------------------------------------------------------------------
604 -- |---------------------------< get_valid_enroll >-------------------------|
605 -- ----------------------------------------------------------------------------
606 PROCEDURE get_valid_enroll (p_person_id IN ota_training_plans.person_id%TYPE
607 , p_contact_id IN ota_training_plans.contact_id%TYPE
608 ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
609 ,p_return_status OUT nocopy VARCHAR2)
610 IS
611 l_evt_type VARCHAR2(30);
612 l_proc VARCHAR2(72) := g_package|| 'get_valid_enroll';
613 BEGIN
614
615 l_evt_type:= get_enroll_status(p_person_id => p_person_id,
616 p_contact_id => p_contact_id,
617 p_training_plan_member_id => p_training_plan_member_id);
618 p_return_status := 'S';
619
620 IF ( l_evt_Type IS NOT NULL AND l_evt_type <> 'Z' ) THEN
621 p_return_status := 'E';
622 END IF;
623
624 END get_valid_enroll;
625
626 -- ---------------------------------------------------------------------------
627 -- |----------------------< is_personal_trng_plan >--------------------------|
628 -- ---------------------------------------------------------------------------
629 --
630 FUNCTION is_personal_trng_plan
631 RETURN BOOLEAN
632 IS
633
634 l_proc VARCHAR2(72) := g_package|| 'is_personal_trng_plan';
635
636 BEGIN
637
638 hr_utility.set_location(' Step:'|| l_proc, 10);
639
640 IF g_is_per_trng_plan IS NOT NULL
641 THEN
642 RETURN g_is_per_trng_plan;
643 END IF;
644
645 END is_personal_trng_plan;
646
647 -- ---------------------------------------------------------------------------
648 -- |----------------------< is_personal_trng_plan >--------------------------|
649 -- ---------------------------------------------------------------------------
650 --
651 FUNCTION is_personal_trng_plan(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
652 RETURN BOOLEAN
653 IS
654
655 l_person_id ota_training_plans.person_id%TYPE;
656 l_contact_id ota_training_plans.contact_id%TYPE;
657 l_proc VARCHAR2(72) := g_package|| 'is_personal_trng_plan';
658
659 BEGIN
660
661 hr_utility.set_location(' Step:'|| l_proc, 10);
662
663 SELECT tps.person_id, tps.contact_id
664 INTO l_person_id, l_contact_id
665 FROM ota_training_plans tps
666 WHERE tps.training_plan_id = p_training_plan_id;
667
668 hr_utility.set_location(' Step:'|| l_proc, 20);
669
670 IF l_person_id IS NOT NULL OR l_contact_id IS NOT NULL
671 THEN
672 g_is_per_trng_plan := true;
673 END IF;
674
675 hr_utility.set_location(' Step:'|| l_proc, 30);
676
677 RETURN g_is_per_trng_plan;
678 END is_personal_trng_plan;
679
680 Procedure complete_plan
681 (p_training_plan_id ota_training_plans.training_plan_id%type)
682 is
683
684 CURSOR csr_tp_update(csr_training_plan_id number)
685 IS
686 SELECT otp.name,
687 otp.object_version_number,
688 otp.time_period_id,
689 otp.budget_currency
690 FROM ota_training_plans otp
691 WHERE otp.training_plan_id = csr_training_plan_id;
692
693
694 l_name ota_training_plans.name%type;
695 l_object_version_number ota_training_plans.object_version_number%type;
696 l_time_period_id ota_training_plans.time_period_id%type;
697 l_budget_currency ota_training_plans.budget_currency%type;
698 l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
699
700 BEGIN
701 l_plan_status_type_id := 'OTA_COMPLETED';
702
703 OPEN csr_tp_update(p_training_plan_id);
704 FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
705 IF csr_tp_update%FOUND then
706 CLOSE csr_tp_update;
707 ota_tps_api.update_training_plan
708 (p_effective_date => sysdate
709 ,p_training_plan_id => p_training_plan_id
710 ,p_object_version_number => l_object_version_number
711 ,p_plan_status_type_id => l_plan_status_type_id
712 ,p_name => l_name
713 ,p_time_period_id => l_time_period_id
714 ,p_budget_currency => l_budget_currency);
715
716 ELSE
717 CLOSE csr_tp_update;
718 END IF;
719 END complete_plan;
720
721 --
722 END ota_trng_plan_util_ss;
723