[Home] [Help]
PACKAGE BODY: APPS.OTA_TRNG_PLAN_COMP_SS
Source
1 PACKAGE BODY OTA_TRNG_PLAN_COMP_SS as
2 /* $Header: ottpmwrs.pkb 120.1 2006/05/11 05:34:11 rdola noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' ota_trng_plan_comp_ss.'; -- Global package name
9
10 --
11 -- ---------------------------------------------------------------------------
12 -- |----------------------< update_tpc_enroll_status_chg >--------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15
16 PROCEDURE update_tpc_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
17 p_person_id IN ota_training_plans.person_id%TYPE,
18 -- Modified for Bug#3479186
19 p_contact_id IN ota_training_plans.contact_id%TYPE,
20 p_learning_path_ids OUT NOCOPY varchar2)
21 --p_status_id in ota_booking_status_types.booking_status_type_id%type)
22 IS
23
24 l_proc VARCHAR2(72) := g_package|| 'update_tpc_enroll_status_chg';
25
26
27 --GET ALL THE TPC'S WHICH HAVE PASSED EVENT AS MEMBER
28 CURSOR csr_tpm_info(csr_activity_version_id number, csr_evt_start_date date,csr_evt_end_date date,csr_evt_type varchar2) IS
29 SELECT otpm.training_plan_member_id,
30 otpm.object_version_number,otpm.earliest_start_date, otpm.target_completion_date
31 -- otpm.member_status_type_id
32 FROM ota_training_plan_members otpm,
33 ota_training_plans otp
34 WHERE otp.training_plan_id = otpm.training_plan_id
35 -- AND otp.person_id = p_person_id
36 AND (( p_person_id IS NOT NULL AND otp.person_id = p_person_id)
37 OR (p_contact_id IS NOT NULL AND otp.contact_id = p_contact_id))
38 AND otpm.activity_version_id = csr_activity_version_id
39 and otpm.member_status_type_id <> 'CANCELLED'
40 -- AND otpm.target_completion_date IS NOT NULL
41 --Modified for Bug#3855721
42 AND(otp.learning_path_id IS NOT NULL OR ( otpm.earliest_start_date <= csr_evt_start_date
43 AND
44 (csr_evt_end_date IS NOT NULL
45 AND otpm.target_completion_date >= csr_evt_end_date)
46 or (csr_evt_type = 'SELFPACED'
47 AND otpm.target_completion_date >= csr_evt_start_date)));
48
49 CURSOR evt_det IS
50 SELECT activity_version_id,
51 course_start_date,
52 course_end_date,
53 event_type
54 FROM ota_events
55 WHERE event_id = p_event_id;
56
57
58
59 l_evt_start_date DATE;
60 l_evt_end_date DATE;
61 l_activity_version_id NUMBER(9);
62 l_evt_type VARCHAR2(30);
63 l_enroll_type varchar2(30);
64 l_member_status_type ota_training_plan_members.member_status_type_id%TYPE;
65 /* l_exists ota_training_plan_members.training_plan_member_id%TYPE;
66 l_name ota_training_plans.name%type;
67 l_object_version_number ota_training_plans.object_version_number%type;
68 l_time_period_id ota_training_plans.time_period_id%type;
69 l_budget_currency ota_training_plans.budget_currency%type;
70 */
71
72 BEGIN
73
74
75 OPEN evt_det;
76 FETCH evt_det
77 INTO l_activity_version_id,
78 l_evt_start_date,
79 l_evt_end_date ,
80 l_evt_type;
81
82 IF evt_det%FOUND THEN
83
84 CLOSE evt_det;
85
86 hr_utility.set_location(' Step:'|| l_proc, 20);
87
88 /* open evt_type;
89 fetch evt_type into l_evt_Type;
90 close evt_type; */
91
92 FOR rec IN csr_tpm_info(l_activity_version_id,l_evt_start_date,l_evt_end_date,l_evt_type)
93
94 LOOP
95
96 -- Modified for Bug#3479186
97 l_enroll_type := ota_trng_plan_util_ss.get_enroll_status(p_person_id,p_contact_id,rec.training_plan_member_id);
98
99 IF l_enroll_type = 'A' THEN
100 l_member_status_type := 'OTA_COMPLETED';
101 ELSIF ( l_enroll_type = 'P'
102 OR l_enroll_type = 'W'
103 OR l_enroll_type = 'R') THEN
104 l_member_status_type := 'ACTIVE';
105 ELSE l_member_status_type := 'OTA_PLANNED';
106 END IF;
107 --call upd tpm api after lck
108 ota_tpm_api.update_training_plan_member
109 (p_effective_date => sysdate
110 ,p_object_version_number => rec.object_version_number
111 ,p_training_plan_member_id => rec.training_plan_member_id
112 ,p_member_status_type_id => l_member_status_type
113 ,p_earliest_start_date => rec.earliest_start_date
114 ,p_target_completion_date => rec.target_completion_date
115 ,p_activity_version_id => l_activity_version_id);
116
117 --Thes checks are required only if member status has been updated to Completed
118
119
120 -- IF l_enroll_type='A' then
121
122 Update_tp_tpc_change(rec.training_plan_member_id, p_learning_path_ids);
123
124 /* FOR rec1 in csr_tp_with_tpc(rec.training_plan_member_id)
125 LOOP
126 ---check if all the components under this tP are completed or cancelled
127 open csr_tp_with_valid_tpc(rec1.training_plan_id);
128 fetch csr_tp_with_valid_tpc into l_exists;
129 IF csr_tp_with_valid_tpc%NOTFOUND then
130
131 CLOSE csr_tp_with_valid_tpc;
132
133 --check if this TP has flag set to Y
134 OPEN csr_tp_update(rec1.training_plan_id);
135 FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
136 IF csr_tp_update%FOUND then
137 CLOSE csr_tp_update;
138 --update TP
139 ota_tps_api.update_training_plan
140 (p_effective_date => sysdate
141 ,p_training_plan_id => rec1.training_plan_id
142 ,p_object_version_NUMBER => l_object_version_number
143 ,p_plan_status_type_id => 'COMPLETED'
144 ,p_name => l_name
145 ,p_time_period_id => l_time_period_id
146 ,p_budget_currency => l_budget_currency);
147
148
149 END IF;
150 CLOSE csr_tp_update;
151
152 END IF;
153
154 CLOSE csr_tp_with_valid_tpc;
155
156 END LOOP;*/
157
158 -- END IF;
159
160
161
162 --update TP status
163 END LOOP;
164
165 ELSE
166 CLOSE evt_Det;
167 END IF;
168
169 hr_utility.set_location(' Step:'|| l_proc, 30);
170
171 --MULTI MESSAGE SUPPORT
172
173
174 END update_tpc_enroll_status_chg;
175 -- ---------------------------------------------------------------------------
176 -- |----------------------< update_tpc_evt_change >--------------------------|
177 -- ---------------------------------------------------------------------------
178 --
179 --checkes component status on change of enrollment status
180 PROCEDURE update_tpc_evt_change (p_event_id IN ota_Events.event_id%TYPE,
181 p_course_start_date IN ota_events.course_start_date%TYPE,
182 p_course_end_date IN ota_events.course_end_date%TYPE)
183 IS
184 /* Commented out for bug#5086156
185 CURSOR csr_tpm IS
186 SELECT tpm.training_plan_member_id,
187 tp.person_id,
188 -- Modified for Bug#3479186
189 tp.contact_id,
190 tpm.object_version_number
191 FROM ota_training_plans tp,
192 ota_training_plan_members tpm,
193 ota_events oe,
194 ota_delegate_bookings odb,
195 ota_booking_status_types bst
196 WHERE oe.event_id = odb.event_id
197 AND odb.booking_status_type_id=bst.booking_status_type_id
198 AND bst.type <>'C'
199 -- and bst.active_flag='Y'
200 AND oe.activity_version_id = tpm.activity_version_id
201 AND tpm.training_plan_id = tp.training_plan_id
202 --Modified for Bug#3855721
203 -- AND odb.delegate_person_id = tp.person_id
204 AND (odb.delegate_person_id = tp.person_id OR odb.delegate_contact_id = tp.contact_id)
205 AND oe.event_id = p_event_id
206 AND tpm.member_status_type_id NOT IN ('CANCELLED', 'OTA_AWAITING_APPROVAL','OTA_COMPLETED')
207 AND (tpm.target_completion_date <nvl(p_course_start_date, hr_api.g_eot)
208 or tpm.earliest_start_date > nvl(p_course_end_date, hr_api.g_eot)) ;
209 */
210
211 l_proc VARCHAR2(72) := g_package|| 'update_tpc_evt_date_change';
212 l_enroll_status VARCHAR2(30);
213 l_member_status_type VARCHAR2(30);
214
215 BEGIN
216 hr_utility.set_location(' Step:'|| l_proc, 10);
217 /*
218 FOR rec IN csr_tpm
219 LOOP
220
221 hr_utility.set_location(' Step:'|| l_proc, 20);
222 l_enroll_status := ota_trng_plan_util_ss.get_enroll_status (p_person_id => rec.person_id,
223 -- Modified for Bug#3479186
224 p_contact_id => rec.contact_id,
225 p_training_plan_member_id => rec.training_plan_member_id);
226 IF ( l_enroll_status = 'P'
227 OR l_enroll_status = 'W'
228 OR l_enroll_status = 'R') THEN
229
230 fnd_message.set_name('OTA', 'OTA_13187_TPM_EVT_DATES');
231 fnd_message.raise_error;
232 EXIT;
233 -- l_member_status_type := 'ACTIVE';
234 -- ELSE l_member_status_type := 'OTA_PLANNED';
235 END IF;
236
237
238
239 END LOOP;
240 */
241 hr_utility.set_location(' Step:'|| l_proc, 30);
242
243 END update_tpc_evt_change;
244
245 -- ----------------------------------------------------------------------------
246 -- |---------------------------< validate_TPC >-------------------------|
247 -- ----------------------------------------------------------------------------
248 Procedure validate_tpc
249 ( p_mode in varchar2
250 ,p_validate IN NUMBER DEFAULT hr_api.g_false_num
251 ,p_effective_date IN date
252 ,p_business_group_id IN number
253 ,p_training_plan_id IN number
254 ,p_activity_version_id IN NUMBER DEFAULT NULL
255 ,p_activity_definition_id IN NUMBER DEFAULT NULL
256 ,p_member_status_type_id IN VARCHAR2
257 ,p_target_completion_date IN date DEFAULT NULL
258 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
259 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
260 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
261 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
262 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
263 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
264 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
265 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
266 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
267 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
268 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
269 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
270 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
271 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
272 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
273 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
274 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
275 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
276 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
277 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
278 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
279 ,p_attribute21 IN VARCHAR2 DEFAULT NULL
280 ,p_attribute22 IN VARCHAR2 DEFAULT NULL
281 ,p_attribute23 IN VARCHAR2 DEFAULT NULL
282 ,p_attribute24 IN VARCHAR2 DEFAULT NULL
283 ,p_attribute25 IN VARCHAR2 DEFAULT NULL
284 ,p_attribute26 IN VARCHAR2 DEFAULT NULL
285 ,p_attribute27 IN VARCHAR2 DEFAULT NULL
286 ,p_attribute28 IN VARCHAR2 DEFAULT NULL
287 ,p_attribute29 IN VARCHAR2 DEFAULT NULL
288 ,p_attribute30 IN VARCHAR2 DEFAULT NULL
289 ,p_assignment_id IN NUMBER DEFAULT NULL
290 ,p_source_id IN NUMBER DEFAULT NULL
291 ,p_source_function IN VARCHAR2 DEFAULT NULL
292 ,p_cancellation_reason IN VARCHAR2 DEFAULT NULL
293 ,p_earliest_start_date IN date DEFAULT NULL
294 ,p_training_plan_member_id IN number
295 ,p_creator_person_id IN number
296 ,p_object_version_NUMBER IN OUT NOCOPY number
297 ,p_return_status OUT NOCOPY VARCHAR2)
298
299 is
300 l_proc VARCHAR2(72) := g_package ||'validate_TPC';
301 l_object_version_number number;
302 l_training_plan_member_id number;
303
304 begin
305 hr_utility.set_location(' Entering:' || l_proc,10);
306
307 -- SAVEPOINT validate_TPC_proc;
308
309 if p_mode= 'INSERT' then
310 ota_tpm_swi.create_training_plan_member
311 (p_validate => p_validate
312 ,p_effective_date => p_effective_date
313 ,p_business_group_id => p_business_group_id
314 ,p_training_plan_id => p_training_plan_id
315 ,p_activity_version_id => p_activity_version_id
316 ,p_activity_definition_id => p_activity_definition_id
317 ,p_member_status_type_id => p_member_status_type_id
318 ,p_target_completion_date => p_target_completion_date
319 ,p_attribute_category => p_attribute_category
320 ,p_attribute1 => p_attribute1
321 ,p_attribute2 => p_attribute2
322 ,p_attribute3 => p_attribute3
323 ,p_attribute4 => p_attribute4
324 ,p_attribute5 => p_attribute5
325 ,p_attribute6 => p_attribute6
326 ,p_attribute7 => p_attribute7
327 ,p_attribute8 => p_attribute8
328 ,p_attribute9 => p_attribute9
329 ,p_attribute10 => p_attribute10
330 ,p_attribute11 => p_attribute11
331 ,p_attribute12 => p_attribute12
332 ,p_attribute13 => p_attribute13
333 ,p_attribute14 => p_attribute14
334 ,p_attribute15 => p_attribute15
335 ,p_attribute16 => p_attribute16
336 ,p_attribute17 => p_attribute17
337 ,p_attribute18 => p_attribute18
338 ,p_attribute19 => p_attribute19
339 ,p_attribute20 => p_attribute20
340 ,p_attribute21 => p_attribute21
341 ,p_attribute22 => p_attribute22
342 ,p_attribute23 => p_attribute23
343 ,p_attribute24 => p_attribute24
344 ,p_attribute25 => p_attribute25
345 ,p_attribute26 => p_attribute26
346 ,p_attribute27 => p_attribute27
347 ,p_attribute28 => p_attribute28
348 ,p_attribute29 => p_attribute29
349 ,p_attribute30 => p_attribute30
350 ,p_assignment_id => p_assignment_id
351 ,p_source_id => p_source_id
352 ,p_source_function => p_source_function
353 ,p_cancellation_reason => p_cancellation_reason
354 ,p_earliest_start_date => p_earliest_start_date
355 ,p_training_plan_member_id => l_training_plan_member_id
356 ,p_object_version_NUMBER => l_object_version_number
357 ,p_creator_person_id => p_creator_person_id
358 ,p_return_status => p_return_status
359 );
360
361 elsif p_mode = 'UPDATE' then
362
363 ota_tpm_swi.update_training_plan_member
364 (p_validate => p_validate
365 ,p_effective_date => p_effective_date
366 ,p_training_plan_member_id => p_training_plan_member_id
367 ,p_object_version_NUMBER => p_object_version_number
368 ,p_activity_version_id => p_activity_version_id
369 ,p_activity_definition_id => p_activity_definition_id
370 ,p_member_status_type_id => p_member_status_type_id
371 ,p_target_completion_date => p_target_completion_date
372 ,p_attribute_category => p_attribute_category
373 ,p_attribute1 => p_attribute1
374 ,p_attribute2 => p_attribute2
375 ,p_attribute3 => p_attribute3
376 ,p_attribute4 => p_attribute4
377 ,p_attribute5 => p_attribute5
378 ,p_attribute6 => p_attribute6
379 ,p_attribute7 => p_attribute7
380 ,p_attribute8 => p_attribute8
381 ,p_attribute9 => p_attribute9
382 ,p_attribute10 => p_attribute10
383 ,p_attribute11 => p_attribute11
384 ,p_attribute12 => p_attribute12
385 ,p_attribute13 => p_attribute13
386 ,p_attribute14 => p_attribute14
387 ,p_attribute15 => p_attribute15
388 ,p_attribute16 => p_attribute16
389 ,p_attribute17 => p_attribute17
390 ,p_attribute18 => p_attribute18
391 ,p_attribute19 => p_attribute19
392 ,p_attribute20 => p_attribute20
393 ,p_attribute21 => p_attribute21
394 ,p_attribute22 => p_attribute22
395 ,p_attribute23 => p_attribute23
396 ,p_attribute24 => p_attribute24
397 ,p_attribute25 => p_attribute25
398 ,p_attribute26 => p_attribute26
399 ,p_attribute27 => p_attribute27
400 ,p_attribute28 => p_attribute28
401 ,p_attribute29 => p_attribute29
402 ,p_attribute30 => p_attribute30
403 ,p_assignment_id => p_assignment_id
404 ,p_source_id => p_source_id
405 ,p_source_function => p_source_function
406 ,p_cancellation_reason => p_cancellation_reason
407 ,p_earliest_start_date => p_earliest_start_date
408 ,p_creator_person_id => p_creator_person_id
409 ,p_return_status => p_return_status
410 );
411 end if;
412
413 -- ROLLBACK to validate_TPC_proc;
414
415 p_object_version_number:=null;
416 p_return_status:= hr_multi_message.get_return_status_disable;
417
418 hr_utility.set_location(' LEAVING:' || l_proc,20);
419
420 end validate_TPC;
421
422 -- ---------------------------------------------------------------------------
423 -- |----------------------< Update_tpc_sshr_change >--------------------------|
424 -- ---------------------------------------------------------------------------
425 --
426
427 Procedure Update_tpc_sshr_change(
428 p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type,
429 p_person_id ota_training_plans.person_id%type,
430 p_mode varchar2)
431 is
432
433 Cursor get_tpc_detail
434 is
435 Select object_version_number from
436 ota_training_plan_members
437 where training_plan_member_id=p_training_plan_member_id;
438
439 l_enroll_type varchar2(30);
440 l_object_version_number Number(9);
441 l_member_status_type varchar2(30);
442
443 begin
444
445
446 open get_tpc_detail;
447 fetch get_tpc_detail into l_object_version_number;
448 close get_tpc_detail;
449
450 if p_mode='APPROVE' then
451
452 -- Modified for Bug#3479186
453 l_enroll_type := ota_trng_plan_util_ss.get_enroll_status(p_person_id,NULL,p_training_plan_member_id);
454
455 IF l_enroll_type = 'A' THEN
456 l_member_status_type := 'OTA_COMPLETED';
457 ELSIF ( l_enroll_type = 'P'
458 OR l_enroll_type = 'W'
459 OR l_enroll_type = 'R') THEN
460 l_member_status_type := 'ACTIVE';
461 ELSE l_member_status_type := 'OTA_PLANNED';
462 END IF;
463
464
465 --call upd tpm api after lck
466 ota_tpm_api.update_training_plan_member
467 (p_effective_date => sysdate
468 ,p_object_version_number => l_object_version_number
469 ,p_training_plan_member_id => p_training_plan_member_id
470 ,p_member_status_type_id => l_member_status_type);
471
472
473
474 elsif p_mode='REJECT' then
475 l_member_status_type :='CANCELLED';
476
477 ota_tpm_api.update_training_plan_member
478 (p_effective_date => sysdate
479 ,p_object_version_number => l_object_version_number
480 ,p_training_plan_member_id => p_training_plan_member_id
481 ,p_member_status_type_id => l_member_status_type);
482
483 end if;
484
485
486 end Update_tpc_sshr_change;
487
488 -- ---------------------------------------------------------------------------
489 -- |----------------------< Update_tp_tpc_change >--------------------------|
490 -- ---------------------------------------------------------------------------
491 --
492 -- This procedure will get called only when a tpc is Cancelled
493 /*
494 Procedure Update_tp_tpc_change
495 (p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type)
496 is
497
498 --get all the TP corresponding to a particular TP member
499 CURSOR csr_tp_with_tpc
500 IS
501 SELECT otp.training_plan_id
502 , otp.plan_status_type_id
503 FROM ota_training_plans otp,
504 ota_training_plan_members otpm
505 WHERE otp.training_plan_id=otpm.training_plan_id
506 and otp.plan_status_type_id <> 'CANCELLED'
507 --and otp.plan_status_type_id <> 'COMPLETED'
508 -- Modified from COMPLETED to OTA_COMPLETED
509 and otp.plan_status_type_id <> 'OTA_COMPLETED'
510 and otpm.training_plan_member_id=p_training_plan_member_id;
511
512 --check if selected TP has any component not in Cancelled or completed status
513 CURSOR csr_tp_with_valid_tpc(csr_training_plan_id number)
514 IS
515 SELECT otpm.training_plan_member_id
516 FROM ota_training_plan_members otpm
517 WHERE otpm.member_status_type_id <>'CANCELLED'
518 and otpm.member_status_type_id <>'OTA_COMPLETED'
519 and otpm.training_plan_id=csr_training_plan_id
520 and rownum=1;
521
522 --check if flag has been set to y
523 CURSOR csr_tp_update(csr_training_plan_id number)
524 IS
525 SELECT otp.name,
526 otp.object_version_number,
527 otp.time_period_id,
528 otp.budget_currency
529 FROM ota_training_plans otp,
530 ota_training_plan_members otpm
531 WHERE otp.training_plan_id = csr_training_plan_id
532 AND otp.training_plan_id = otpm.training_plan_id
533 AND otpm.member_status_type_id = 'OTA_COMPLETED'
534 AND additional_member_flag = 'N';
535
536 l_exists ota_training_plan_members.training_plan_member_id%TYPE;
537 l_name ota_training_plans.name%type;
538 l_object_version_number ota_training_plans.object_version_number%type;
539 l_time_period_id ota_training_plans.time_period_id%type;
540 l_budget_currency ota_training_plans.budget_currency%type;
541 l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
542
543 begin
544
545 FOR rec1 in csr_tp_with_tpc
546 LOOP
547 IF rec1.plan_status_type_id <> 'OTA_COMPLETED' THEN
548 ---check if all the components under this tP are completed or cancelled
549 open csr_tp_with_valid_tpc(rec1.training_plan_id);
550 fetch csr_tp_with_valid_tpc into l_exists;
551 IF csr_tp_with_valid_tpc%NOTFOUND then
552
553 CLOSE csr_tp_with_valid_tpc;
554
555
556 --check if this TP has flag set to Y
557 OPEN csr_tp_update(rec1.training_plan_id);
558 FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
559 IF csr_tp_update%FOUND then
560 CLOSE csr_tp_update;
561 --update TP
562 ota_tps_api.update_training_plan
563 (p_effective_date => sysdate
564 ,p_training_plan_id => rec1.training_plan_id
565 ,p_object_version_NUMBER => l_object_version_number
566 -- Modified to use OTA_COMPLETED
567 ,p_plan_status_type_id => 'OTA_COMPLETED'
568 ,p_name => l_name
569 ,p_time_period_id => l_time_period_id
570 ,p_budget_currency => l_budget_currency);
571
572 ELSE
573
574 CLOSE csr_tp_update;
575
576 END IF;
577
578
579 CLOSE csr_tp_with_valid_tpc;
580
581 END IF;
582
583 ELSE
584
585 END IF;
586
587 END LOOP;
588
589
590
591
592 end Update_tp_tpc_change;
593 */
594 Procedure Update_tp_tpc_change
595 (p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type)
596 is
597
598 CURSOR csr_tp_with_tpc
599 IS
600 SELECT otp.training_plan_id
601 , otp.plan_status_type_id
602 , otp.additional_member_flag
603 FROM ota_training_plans otp,
604 ota_training_plan_members otpm
605 WHERE otp.training_plan_id=otpm.training_plan_id
606 and otp.plan_status_type_id <> 'CANCELLED'
607 and otpm.training_plan_member_id=p_training_plan_member_id;
608
609
610 CURSOR csr_tp_update(csr_training_plan_id number)
611 IS
612 SELECT otp.name,
613 otp.object_version_number,
614 otp.time_period_id,
615 otp.budget_currency
616 FROM ota_training_plans otp
617 WHERE otp.training_plan_id = csr_training_plan_id;
618
619
620 l_exists ota_training_plan_members.training_plan_member_id%TYPE;
621 l_name ota_training_plans.name%type;
622 l_object_version_number ota_training_plans.object_version_number%type;
623 l_time_period_id ota_training_plans.time_period_id%type;
624 l_budget_currency ota_training_plans.budget_currency%type;
625 l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
626 l_complete_ok varchar2(1);
627
628 BEGIN
629 FOR rec1 in csr_tp_with_tpc LOOP
630 l_plan_status_type_id :=rec1.plan_status_type_id;
631 l_complete_ok := ota_trng_plan_util_ss.chk_complete_plan_ok(rec1.training_plan_id);
632 IF l_complete_ok = 'S'
633 AND rec1.plan_status_type_id = 'ACTIVE'
634 -- Bug3499850 AND rec1.additional_member_flag = 'N' THEN
635 THEN
636 -- The Plan can be completed
637 l_plan_status_type_id := 'OTA_COMPLETED';
638 ELSIF l_complete_ok = 'F' AND rec1.plan_status_type_id = 'OTA_COMPLETED' THEN
639 l_plan_status_type_id := 'ACTIVE';
640 END IF;
641
642 IF l_plan_status_type_id <> rec1.plan_status_type_id THEN
643 OPEN csr_tp_update(rec1.training_plan_id);
644 FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
645 IF csr_tp_update%FOUND then
646 CLOSE csr_tp_update;
647 ota_tps_api.update_training_plan
648 (p_effective_date => sysdate
649 ,p_training_plan_id => rec1.training_plan_id
650 ,p_object_version_number => l_object_version_number
651 ,p_plan_status_type_id => l_plan_status_type_id
652 ,p_name => l_name
653 ,p_time_period_id => l_time_period_id
654 ,p_budget_currency => l_budget_currency);
655
656 ELSE
657 CLOSE csr_tp_update;
658 END IF;
659 END IF;
660 END LOOP;
661 END Update_tp_tpc_change;
662
663
664 Procedure Update_tp_tpc_change
665 (p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type
666 ,p_learning_path_ids OUT NOCOPY varchar2)
667 is
668
669 CURSOR csr_tp_with_tpc
670 IS
671 SELECT otp.training_plan_id
672 , otp.plan_status_type_id
673 , otp.additional_member_flag
674 , otp.learning_path_id
675 FROM ota_training_plans otp,
676 ota_training_plan_members otpm
677 WHERE otp.training_plan_id=otpm.training_plan_id
678 and otp.plan_status_type_id <> 'CANCELLED'
679 and otpm.training_plan_member_id=p_training_plan_member_id;
680
681
682 CURSOR csr_tp_update(csr_training_plan_id number)
683 IS
684 SELECT otp.name,
685 otp.object_version_number,
686 otp.time_period_id,
687 otp.budget_currency
688 FROM ota_training_plans otp
689 WHERE otp.training_plan_id = csr_training_plan_id;
690
691
692 l_exists ota_training_plan_members.training_plan_member_id%TYPE;
693 l_name ota_training_plans.name%type;
694 l_object_version_number ota_training_plans.object_version_number%type;
695 l_time_period_id ota_training_plans.time_period_id%type;
696 l_budget_currency ota_training_plans.budget_currency%type;
697 l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
698 l_complete_ok varchar2(1);
699 l_learning_path_ids varchar2(4000) := '';
700
701 BEGIN
702 FOR rec1 in csr_tp_with_tpc LOOP
703 l_plan_status_type_id :=rec1.plan_status_type_id;
704 l_complete_ok := ota_trng_plan_util_ss.chk_complete_plan_ok(rec1.training_plan_id);
705 IF l_complete_ok = 'S'
706 AND rec1.plan_status_type_id = 'ACTIVE'
707 -- Bug3499850 AND rec1.additional_member_flag = 'N' THEN
708 THEN
709 -- The Plan can be completed
710 l_plan_status_type_id := 'OTA_COMPLETED';
711 IF rec1.learning_path_id IS NOT NULL THEN
712 if l_learning_path_ids = '' or l_learning_path_ids is null then
713 l_learning_path_ids := rec1.learning_path_id;
714 else
715 l_learning_path_ids := l_learning_path_ids || '^' || rec1.learning_path_id;
716
717 end if;
718 -- l_learning_path_ids := l_learning_path_ids || '^' || rec1.learning_path_id;
719 END IF;
720 ELSIF l_complete_ok = 'F' AND rec1.plan_status_type_id = 'OTA_COMPLETED' THEN
721 l_plan_status_type_id := 'ACTIVE';
722 END IF;
723
724 IF l_plan_status_type_id <> rec1.plan_status_type_id THEN
725 OPEN csr_tp_update(rec1.training_plan_id);
726 FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
727 IF csr_tp_update%FOUND then
728 CLOSE csr_tp_update;
729 ota_tps_api.update_training_plan
730 (p_effective_date => sysdate
731 ,p_training_plan_id => rec1.training_plan_id
732 ,p_object_version_number => l_object_version_number
733 ,p_plan_status_type_id => l_plan_status_type_id
734 ,p_name => l_name
735 ,p_time_period_id => l_time_period_id
736 ,p_budget_currency => l_budget_currency);
737
738 ELSE
739 CLOSE csr_tp_update;
740 END IF;
741 END IF;
742 END LOOP;
743 p_learning_path_ids := l_learning_path_ids;
744 END Update_tp_tpc_change;
745
746 END ota_trng_plan_comp_ss;
747