[Home] [Help]
PACKAGE BODY: APPS.OTA_TRAINING_PLAN_UPGRADE
Source
1 PACKAGE BODY OTA_TRAINING_PLAN_UPGRADE AS
2 /* $Header: ottplpupg.pkb 120.0.12000000.2 2007/12/26 16:57:18 pekasi noship $ */
3
4 -- LP_MAP_UPGRADE_ID constant number(2) := -2;
5 OTA_MIG_FAIL_ID constant number(2) := -1;
6 LP_MAP_TABLE_NAME_E constant varchar2(30) := 'OTA_LP_ENROLLMENTS';
7 LP_MAP_TABLE_NAME_M constant varchar2(30) := 'OTA_LEARNING_PATH_MEMBERS';
8 LP_MAP_TABLE_NAME_M_E constant varchar2(30) := 'OTA_LP_MEMBER_ENROLLMENTS';
9 LP_UPGRADE_NAME constant varchar2(30) := 'OTLPUPG';
10 LP_LOG_TYPE_I constant varchar2(30) := 'I'; -- log type is Infornation
11 LP_LOG_TYPE_N constant varchar2(30) := 'N'; -- log type is Internal
12 LP_LOG_TYPE_E constant varchar2(30) := 'E';-- log type is Error
13
14
15
16 Cursor csr_get_date_completed(p_activity_version_id IN NUMBER, p_person_id IN NUMBER
17 ,p_contact_id in number) IS
18 SELECT min(date_status_changed)
19 FROM ota_events evt,
20 ota_delegate_bookings tdb,
21 ota_booking_status_types bst
22 WHERE evt.activity_version_id=p_activity_version_id
23 AND evt.event_id = tdb.event_id
24 AND bst.booking_status_type_id = tdb.booking_status_type_id
25 AND (tdb.delegate_person_id = p_person_id or tdb.delegate_person_id = p_contact_id)
26 AND bst.type = 'A';
27
28
29 -- ----------------------------------------------------------------------------
30 -- |--------------------------< migrate_dff_contexts >-----------------------|
31 -- ----------------------------------------------------------------------------
32
33 procedure migrate_dff_contexts (
34 p_source_table in varchar2, p_destination_table in varchar2,p_source_field
35 in varchar2, p_destination_field in varchar2,p_update_id in number) is
36
37 l_date date;
38 l_segrec FND_DESCR_FLEX_COLUMN_USAGES%RowType ;
39 l_err_code varchar2(72);
40 l_err_msg varchar2(2000);
41 l_context_code FND_DESCR_FLEX_CONTEXTS.descriptive_flex_context_code%Type;
42 l_segment_exists Varchar2(1) := 'N' ;
43 l_context_exists Varchar2(1) := 'N' ;
44
45 Cursor C1 (p_context_code in varchar2 ,p_source_table in varchar2
46 , p_destination_table in varchar2) IS
47 Select fcu.*,fvs.flex_value_set_name
48 From Fnd_Descr_Flex_Col_Usage_Vl fcu, fnd_flex_value_sets fvs
49 Where fcu.Application_id = 810
50 and fcu.Descriptive_FlexField_Name = p_source_table
51 and fcu.Descriptive_Flex_Context_code = p_context_code
52 and fcu.flex_value_set_id = fvs.flex_value_set_id(+)
53 and Not exists (SELECT 'Y'
54 From Fnd_Descr_Flex_Col_Usage_Vl cat_fcu
55 Where cat_fcu.Application_id = fcu.application_id
56 and cat_fcu.Descriptive_FlexField_Name = p_destination_table
57 and cat_fcu.Descriptive_Flex_Context_code = fcu.Descriptive_Flex_Context_code
58 and cat_fcu.end_user_column_name = fcu.end_user_column_name );
59
60 Cursor Csr_DFF_contexts (p_source_table in varchar2)is
61 Select *
62 from FND_DESCR_FLEX_CONTEXTS_vl
63 Where Application_id = 810
64 and Descriptive_FLexfield_Name = p_source_table
65 and Enabled_Flag = 'Y';
66
67 Cursor Csr_Segment_exists(p_context_name in varchar2) is
68 SELECT 'Y'
69 FROM fnd_descriptive_flexs
70 WHERE application_id = 810
71 AND descriptive_flexfield_name = p_context_name ;
72
73 Begin
74 fnd_flex_dsc_api.set_session_mode('seed_data');
75 --p_upgrade_id := g_update_id;
76 Select Trunc(sysdate) into l_date from dual ;
77
78 For dff_context in Csr_Dff_contexts(p_source_table)
79 Loop
80 begin
81 l_context_exists := NULL ;
82 Select Max('Y') into l_context_exists
83 From FND_DESCR_FLEX_CONTEXTS_vl
84 Where Descriptive_Flexfield_name = p_destination_table
85 and Descriptive_Flex_COntext_Code = dff_context.Descriptive_Flex_Context_code ;
86 -- If there is a definition already existis and it is a Global context
87 -- then NO context will be created but the strucre will be added to the existing one.
88
89 If (NOT (dff_context.Global_Flag = 'Y' OR l_context_exists is NOT NULL)) then
90
91 fnd_flex_dsc_api.create_context(
92 appl_short_name => 'OTA' ,
93 flexfield_name => p_destination_table,
94 context_code => Dff_context.Descriptive_flex_context_code,
95 context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
96 description => dff_context.DESCRIPTION,
97 enabled => dff_context.ENABLED_FLAG );
98
99 End If;
100 For I in C1 (Dff_context.Descriptive_flex_context_code,p_source_table
101 , p_destination_table)
102 Loop
103 begin
104 fnd_flex_dsc_api.create_segment(
105 appl_short_name => 'OTA' ,
106 flexfield_name => p_destination_table, --'Add''l Category Information',
107 context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
108 name => I.END_USER_COLUMN_NAME,
109 column => Replace(I.APPLICATION_COLUMN_NAME,p_source_field,p_destination_field),
110 description => I.DESCRIPTION,
111 sequence_number => I.COLUMN_SEQ_NUM,
112 enabled => I.ENABLED_FLAG,
113 displayed => I.DISPLAY_FLAG,
114 value_set => I.flex_value_set_name, --'7 Characters',
115 default_type => I.DEFAULT_TYPE,
116 default_value => I.DEFAULT_VALUE,
117 required => I.REQUIRED_FLAG,
118 security_enabled => I.SECURITY_ENABLED_FLAG,
119 display_size => I.DISPLAY_SIZE,
120 description_size => I.MAXIMUM_DESCRIPTION_LEN,
121 concatenated_description_size => I.CONCATENATION_DESCRIPTION_LEN,
122 list_of_values_prompt => I.FORM_ABOVE_PROMPT,
123 window_prompt => I.FORM_LEFT_PROMPT,
124 range => NULL);
125 Exception
126 when others then
127 l_err_code := SQLCODE;
128 l_err_msg := nvl(substr(SQLERRM,1,2000),'migrate_dff_contexts - segments');
129 ota_classic_upgrade.add_log_entry( p_table_name => 'MIGRATE_'||p_source_table
130 ,p_source_primary_key => substr(dff_context.application_id||'|'||
131 dff_context.descriptive_flexfield_name||'|'||
132 I.application_column_name,1,80)
133 ,p_object_value => 'migrate_dff_contexts'
134 ,p_message_text => l_err_msg
135 ,p_upgrade_id => p_update_id
136 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
137 ,p_log_type => LP_LOG_TYPE_E
138 ,p_upgrade_name => LP_UPGRADE_NAME );
139 End;
140
141
142 End Loop;
143 Exception
144 when others then
145 l_err_code := SQLCODE;
146 l_err_msg := nvl(substr(SQLERRM,1,2000),'migrate_dff_contexts');
147 ota_classic_upgrade.add_log_entry( p_table_name => 'MIGRATE_'||p_source_table
148 ,p_source_primary_key => substr( dff_context.application_id||
149 dff_context.descriptive_flexfield_name,
150 1,80)
151 ,p_object_value => 'migrate_dff_contexts'
152 ,p_message_text => l_err_msg
153 ,p_upgrade_id => p_update_id
154 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
155 ,p_log_type => LP_LOG_TYPE_E
156 ,p_upgrade_name => LP_UPGRADE_NAME );
157 End;
158 End Loop;
159
160 End migrate_dff_contexts;
161
162 -- ----------------------------------------------------------------------------
163 -- |---------------------------< get_lp_enr_map >-----------------------------|
164 -- ----------------------------------------------------------------------------
165 function get_status(p_status in varchar2) return
166 varchar2 is
167 l_new_status varchar2(30);
168
169 begin
170 if p_status = 'OTA_PLANNED' then
171 l_new_status := 'PLANNED';
172 elsif p_status = 'OTA_AWAITING_APPROVAL' then
173 l_new_status := 'AWAITING_APPROVAL';
174 elsif p_status = 'OTA_COMPLETED' then
175 l_new_status := 'COMPLETED';
176 else
177 l_new_status := p_status;
178 end if;
179
180 return l_new_status;
181 end get_status;
182
183
184 -- ----------------------------------------------------------------------------
185 -- |---------------------------< get_lp_enr_map >-----------------------------|
186 -- ----------------------------------------------------------------------------
187 function get_lp_enr_map(p_training_plan_id in number
188 ,p_source_id in number,p_source_function in varchar2
189 ,p_assignment_id in number) return number
190 is
191 l_concat varchar2(255);
192 l_ret number(10);
193 cursor csr_get_pk(p_concat varchar2) is
194 select target_primary_key
195
196 from ota_upgrade_log
197 where table_name = LP_MAP_TABLE_NAME_E
198 -- and upgrade_id = LP_MAP_UPGRADE_ID
199 and source_primary_key = p_concat;
200
201 begin
202 l_concat :=p_training_plan_id|| p_source_id||p_source_function|| p_assignment_id;
203 open csr_get_pk(l_concat);
204 fetch csr_get_pk into l_ret;
205 if csr_get_pk%notfound then
206 l_ret := OTA_MIG_FAIL_ID;
207 end if;
208 /*
209 begin
210 select nvl(target_primary_key,OTA_MIG_FAIL_ID)
211 into l_ret
212 from ota_upgrade_log
213 where table_name = LP_MAP_TABLE_NAME_E
214 -- and upgrade_id = LP_MAP_UPGRADE_ID
215 and source_primary_key = l_concat;
216
217 exception
218 when no_data_found then
219 l_ret := OTA_MIG_FAIL_ID;
220 end;
221 */
222
223 return l_ret;
224 end get_lp_enr_map;
225
226 -- ----------------------------------------------------------------------------
227 -- |---------------------------< get_lp_mem_map >-----------------------------|
228 -- ----------------------------------------------------------------------------
229 function get_lp_mem_map (p_training_plan_id in number,p_activity_version_id in number
230 ,p_completion_target_date date)
231 return number is
232 l_concat varchar2(80);
233 l_ret number;
234
235 cursor csr_get_pk(p_concat varchar2) is
236 select target_primary_key
237 from ota_upgrade_log
238 where table_name = LP_MAP_TABLE_NAME_M
239 -- and upgrade_id = LP_MAP_UPGRADE_ID
240 and source_primary_key = p_concat;
241
242 begin
243 l_concat :=p_training_plan_id || p_activity_version_id || to_char(p_completion_target_date,'DDMMRRRR');
244
245 open csr_get_pk(l_concat);
246 fetch csr_get_pk into l_ret;
247 if csr_get_pk%notfound then
248 l_ret := OTA_MIG_FAIL_ID;
249 end if;
250 /* begin
251 select nvl(target_primary_key,OTA_MIG_FAIL_ID)
252 into l_ret
253 from ota_upgrade_log
254 where table_name = LP_MAP_TABLE_NAME_M
255 -- and upgrade_id = LP_MAP_UPGRADE_ID
256 and source_primary_key = l_concat;
257
258 exception
259 when no_data_found then
260 l_ret := OTA_MIG_FAIL_ID;
261 end;
262 */
263
264 return l_ret;
265
266 end get_lp_mem_map;
267
268 -- ----------------------------------------------------------------------------
269 -- |-----------------------< get_lp_mem_enr_map >-----------------------------|
270 -- ----------------------------------------------------------------------------
271 function get_lp_mem_enr_map(p_training_plan_member_id in number) return number
272 is
273
274 l_ret number(10);
275 cursor csr_get_pk(p_training_plan_member_id varchar2)is
276 select target_primary_key
277 from ota_upgrade_log
278 where table_name = LP_MAP_TABLE_NAME_M_E
279 and source_primary_key = p_training_plan_member_id;
280 begin
281 open csr_get_pk(p_training_plan_member_id);
282 fetch csr_get_pk into l_ret;
283 if csr_get_pk%notfound then
284 l_ret := OTA_MIG_FAIL_ID;
285 end if;
286 /*
287 begin
288 select nvl(target_primary_key,OTA_MIG_FAIL_ID)
289 into l_ret
290 from ota_upgrade_log
291 where table_name = LP_MAP_TABLE_NAME_M_E
292 -- and upgrade_id = LP_MAP_UPGRADE_ID
293 and source_primary_key = p_training_plan_member_id;
294
295 exception
296 when no_data_found then
297 l_ret := OTA_MIG_FAIL_ID;
298 end;*/
299
300 return l_ret;
301 end get_lp_mem_enr_map;
302
303
304 -- ----------------------------------------------------------------------------
305 -- |---------------------------< set_lp_enr_map >-----------------------------|
306 -- ----------------------------------------------------------------------------
307
308 procedure set_lp_enr_map (p_upgrade_id in number
309 ,p_training_plan_id in number
310 ,p_new_lp_enr_id in number
311 ,p_source_id in number
312 ,p_source_function in varchar2
313 ,p_assignment_id in number) is
314
315 cursor c_exists (p_concat in varchar2) is
316 select 1
317 from ota_upgrade_log
318 where table_name = LP_MAP_TABLE_NAME_E
319 -- and upgrade_id = LP_MAP_UPGRADE_ID
320 and source_primary_key = p_concat;
321
322
323 l_dummy number;
324 l_concat varchar2(255);
325
326 begin
327 l_concat := p_training_plan_id||p_source_id || p_source_function || p_assignment_id;
328
329 open c_exists(l_concat);
330 fetch c_exists into l_dummy;
331
332 if c_exists%found then
333 update ota_upgrade_log
334 set target_primary_key = p_new_lp_enr_id
335 where table_name = LP_MAP_TABLE_NAME_E
336 -- and upgrade_id = LP_MAP_UPGRADE_ID
337 and source_primary_key = l_concat;
338 else
339 insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
340 ,log_type,upgrade_name)
341 values (p_upgrade_id,LP_MAP_TABLE_NAME_E,l_concat,p_new_lp_enr_id
342 ,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
343 end if;
344 close c_exists;
345 end set_lp_enr_map;
346
347 -- ----------------------------------------------------------------------------
348 -- |---------------------------< set_lp_mem_map >-----------------------------|
349 -- ----------------------------------------------------------------------------
350
351 procedure set_lp_mem_map (p_upgrade_id in number
352 ,p_training_plan_id in number
353 ,p_activity_versions_id number
354 ,p_completion_target_date date
355 ,p_new_lp_mem_id in number) is
356 cursor c_exists (l_concat in varchar2) is
357 select 1
358 from ota_upgrade_log
359 where table_name = LP_MAP_TABLE_NAME_M
360 -- and upgrade_id = LP_MAP_UPGRADE_ID
361 and source_primary_key = l_concat;
362
363 l_concat varchar2(80);
364 l_dummy number;
365
366 begin
367 l_concat := p_training_plan_id || p_activity_versions_id ||to_char(p_completion_target_date,'DDMMRRRR') ;
368 open c_exists(l_concat);
369 fetch c_exists into l_dummy;
370
371 if c_exists%found then
372 update ota_upgrade_log
373 set target_primary_key = p_new_lp_mem_id
374 where table_name = LP_MAP_TABLE_NAME_M
375 -- and upgrade_id = LP_MAP_UPGRADE_ID
376 and source_primary_key = l_concat;
377 else
378 insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
379 ,log_type,upgrade_name)
380 values (p_upgrade_id,LP_MAP_TABLE_NAME_M,l_concat,p_new_lp_mem_id,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
381 end if;
382 close c_exists;
383 end set_lp_mem_map;
384
385 -- ----------------------------------------------------------------------------
386 -- |--------------------------< set_lp_mem_enr_map >--------------------------|
387 -- ----------------------------------------------------------------------------
388
389 procedure set_lp_mem_enr_map (p_upgrade_id in number,p_training_plan_member_id in number
390 ,p_new_lp_mem_enr_id in number) is
391 cursor c_exists (p_training_plan_member_id in varchar2) is
392 select 1
393 from ota_upgrade_log
394 where table_name = LP_MAP_TABLE_NAME_M_E
395 -- and upgrade_id = LP_MAP_UPGRADE_ID
396 and source_primary_key = p_training_plan_member_id;
397
398
399 l_dummy number;
400
401 begin
402 open c_exists(p_training_plan_member_id);
403 fetch c_exists into l_dummy;
404
405 if c_exists%found then
406 update ota_upgrade_log
407 set target_primary_key = p_new_lp_mem_enr_id
408 where table_name = LP_MAP_TABLE_NAME_M_E
409 -- and upgrade_id = LP_MAP_UPGRADE_ID
410 and source_primary_key = p_training_plan_member_id;
411 else
412 insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
413 ,log_type,upgrade_name)
414 values (p_upgrade_id,LP_MAP_TABLE_NAME_M_E,p_training_plan_member_id,p_new_lp_mem_enr_id
415 ,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
416 end if;
417 close c_exists;
418 end set_lp_mem_enr_map;
419
420
421 -- ----------------------------------------------------------------------------
422 -- |--------------------------< is_lp_enr_migrated >--------------------------|
423 -- ----------------------------------------------------------------------------
424 function is_lp_enr_migrated (p_training_plan_id in number
425 ,p_source_id in number,p_source_function in varchar2
426 ,p_assignment_id in number) return boolean is
427 l_ret boolean ;
428 l_new_id number;
429 begin
430 l_ret := false;
431 l_new_id := get_lp_enr_map(p_training_plan_id, p_source_id, p_source_function
432 ,p_assignment_id);
433 if l_new_id <> OTA_MIG_FAIL_ID then
434 l_ret := true;
435 end if;
436 return l_ret;
437 end is_lp_enr_migrated;
438 -- ----------------------------------------------------------------------------
439 -- |------------------------< is_lp_mem_migrated >----------------------------|
440 -- ----------------------------------------------------------------------------
441 function is_lp_mem_migrated (p_training_plan_id in number, p_activity_version_id number
442 , p_completion_target_date date)
443 return boolean is
444
445 l_ret boolean;
446 l_new_id number;
447 begin
448 l_ret := false;
449 l_new_id := get_lp_mem_map(p_training_plan_id,p_activity_version_id
450 ,p_completion_target_date);
451 if l_new_id <> OTA_MIG_FAIL_ID then
452 l_ret := true;
453 end if;
454 return l_ret;
455 end is_lp_mem_migrated;
456 -- ----------------------------------------------------------------------------
457 -- |------------------------< is_lp_mem_enr_migrated >----------------------------|
458 -- ----------------------------------------------------------------------------
459 function is_lp_mem_enr_migrated (p_training_plan_member_id in number)
460 return boolean is
461
462 l_ret boolean;
463 l_new_id number;
464 begin
465 l_ret := false;
466 l_new_id := get_lp_mem_enr_map(p_training_plan_member_id);
467 if l_new_id <> OTA_MIG_FAIL_ID then
468 l_ret := true;
469 end if;
470 return l_ret;
471 end is_lp_mem_enr_migrated;
472 -- ----------------------------------------------------------------------------
473 -- |---------------------< populate_path_source_code >------------------------|
474 -- ----------------------------------------------------------------------------
475 PROCEDURE populate_path_source_code is
476
477 begin
478
479
480 UPDATE ota_learning_paths
481 SET path_source_code ='CATALOG'
482 ,display_to_learner_flag = 'Y'
483 WHERE path_source_code IS null;
484
485 UPDATE ota_learning_paths lps
486 SET public_flag = (SELECT decode(count(tea.learning_path_id),0,'Y','N')
487 FROM ota_event_associations tea
488 WHERE lps.learning_path_id = tea.learning_path_id)
489 WHERE lps.public_flag IS NULL
490 AND lps.path_source_code = 'CATALOG';
491
492 end populate_path_source_code;
493
494 -- ----------------------------------------------------------------------------
495 -- |-------------------------< create_learning_path >-------------------------|
496 -- ----------------------------------------------------------------------------
497 Procedure create_learning_path
498 (p_name in varchar
499 ,p_business_group_id in number
500 ,p_start_date_active in date
501 ,p_end_date_active in date
502 ,p_description in varchar
503 ,p_path_source_code in varchar
504 ,p_person_id in number
505 ,p_contact_id in number default null
506 ,p_table_name in varchar
507 ,p_upgrade_id in number
508 ,p_learning_path_id IN OUT NOCOPY Number
509 ,p_object_version_number IN OUT NOCOPY Number
510 ,p_source_function_code in varchar
511 ,p_assignment_id in number
512 ,p_source_id in number
513 ,p_display_to_learner_flag in varchar
514 ,p_training_plan_id in number
515 ) is
516
517 l_learning_path_id number;
518 l_object_version_number number;
519 l_err_code varchar2(72);
520 l_err_msg varchar2(2000);
521
522 cursor csr_get_dup_lps_rec(p_name in varchar2, p_business_group_id in number
523 , p_person_id in number, p_contact_id in number) IS
524 SELECT lps.learning_path_id
525 FROM ota_learning_paths lps, ota_learning_paths_tl lpst
526 where lps.learning_path_id = lpst.learning_path_id
527 and lpst.language = userenv('LANG')
528 and lpst.name = p_name
529 and lps.business_group_id = p_business_group_id
530 and (lps.person_id = p_person_id OR lps.contact_id = p_contact_id);
531
532 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
533 hr_dflex_utility.l_ignore_dfcode_varray();
534
535
536 BEGIN
537 l_add_struct_d.extend(1);
538 l_add_struct_d(l_add_struct_d.count) := 'OTA_LEARNING_PATHS';
539 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
540
541 open csr_get_dup_lps_rec(p_name,p_business_group_id, p_person_id, p_contact_id);
542 fetch csr_get_dup_lps_rec into l_learning_path_id;
543 if csr_get_dup_lps_rec%notfound then
544 Begin
545 ota_learning_path_api.create_learning_path(
546 p_effective_date => trunc(sysdate)
547 ,p_path_name => p_name
548 ,p_duration => null
549 ,p_duration_units => null
550 ,p_business_group_id => p_business_group_id
551 ,p_start_date_active => p_start_date_active
552 ,p_end_date_active => p_end_date_active
553 ,p_description => p_description
554 ,p_path_source_code => p_path_source_code
555 ,p_person_id => p_person_id
556 ,p_contact_id => p_contact_id
557 ,p_learning_path_id => l_learning_path_id
558 ,p_object_version_number => l_object_version_number
559 ,p_source_function_code => p_source_function_code
560 ,p_assignment_id => p_assignment_id
561 ,p_source_id => p_source_id
562 ,p_display_to_learner_flag => p_display_to_learner_flag
563 ,p_public_flag => 'N'
564 );
565
566
567 Exception
568 when others then
569 l_err_code := SQLCODE;
570 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path for Training Plan ');
571
572 ota_classic_upgrade.add_log_entry( p_table_name=>p_table_name
573 ,p_source_primary_key => p_training_plan_id || p_business_group_id
574 ,p_business_group_id => p_business_group_id
575 ,p_object_value => p_name
576 ,p_message_text => l_err_msg
577 ,p_upgrade_id => p_upgrade_id
578 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPGRADE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
579 ,p_log_type => LP_LOG_TYPE_E
580 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
581 end;
582 end if;
583 close csr_get_dup_lps_rec;
584 p_learning_path_id := l_learning_path_id;
585 p_object_version_number := l_object_version_number;
586 hr_dflex_utility.remove_ignore_df_validation;
587 End create_learning_path;
588
589 -- ----------------------------------------------------------------------------
590 -- |---------------------------< create_lp_sections >----------------------|
591 -- ----------------------------------------------------------------------------
592 Procedure create_lp_sections
593 (p_section_name in varchar
594 ,p_description in varchar default null
595 ,p_section_sequence in number
596 ,p_completion_type_code in varchar
597 ,p_business_group_id in varchar
598 ,p_table_name in varchar
599 ,p_upgrade_id in number
600 ,p_learning_path_id in number
601 ,p_learning_path_section_id IN OUT NOCOPY Number
602 ,p_object_version_number IN OUT NOCOPY Number
603
604
605 ) is
606
607 l_learning_path_section_id number;
608 l_object_version_number number;
609 l_err_code varchar2(72);
610 l_err_msg varchar2(2000);
611 l_number number;
612
613 cursor csr_get_dup_lpc_rec(p_learning_path_id in number,p_section_name in varchar) is
614 select lpc.learning_path_section_id
615 from ota_lp_sections_tl lpct,ota_lp_sections lpc
616 where lpc.learning_path_section_id = lpct.learning_path_section_id
617 and lpct.language=userenv('LANG')
618 and lpc.learning_path_id = p_learning_path_id
619 and lpct.name = p_section_name ;
620
621 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
622 hr_dflex_utility.l_ignore_dfcode_varray();
623
624
625 BEGIN
626 l_add_struct_d.extend(1);
627 l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_SECTIONS';
628 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
629
630 open csr_get_dup_lpc_rec(p_learning_path_id, p_section_name);
631 fetch csr_get_dup_lpc_rec into l_learning_path_section_id;
632 if csr_get_dup_lpc_rec%notfound then
633 begin
634 ota_lp_section_api.create_lp_section(
635 p_effective_date => trunc(sysdate)
636 ,p_learning_path_section_id => l_learning_path_section_id
637 ,p_learning_path_id => p_learning_path_id
638 ,p_section_name => p_section_name --ota_lp_sections_tl
639 ,p_description => p_description -- ota_lp_sections_tl
640 ,p_section_sequence => p_section_sequence
641 ,p_completion_type_code => p_completion_type_code
642 ,p_business_group_id => p_business_group_id
643 ,p_object_version_number => l_object_version_number
644 );
645 Exception
646 when others then
647 l_err_code := SQLCODE;
648 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path Sections ');
649
650 ota_classic_upgrade.add_log_entry( p_table_name=>p_table_name
651 ,p_source_primary_key => p_business_group_id
652 ,p_business_group_id => p_business_group_id
653 ,p_object_value => p_section_name
654 ,p_message_text => l_err_msg
655 ,p_upgrade_id => p_upgrade_id
656 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPGRADE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
657 ,p_log_type => LP_LOG_TYPE_E
658 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
659
660 End;
661 end if;
662 close csr_get_dup_lpc_rec;
663 p_learning_path_section_id := l_learning_path_section_id;
664 hr_dflex_utility.remove_ignore_df_validation;
665 End create_lp_sections;
666
667 -- ----------------------------------------------------------------------------
668 -- |---------------------< create_learning_path_members >---------------------|
669 -- ----------------------------------------------------------------------------
670
671 Procedure create_learning_path_members(
672 p_business_group_id IN number
673 ,p_learning_path_id in number
674 ,p_activity_version_id in number
675 ,p_course_sequence in number
676 ,p_completion_target_date in date
677 ,p_learning_path_section_id in number
678 ,p_table_name in varchar
679 ,p_upgrade_id in number
680 ,p_learning_path_member_id IN OUT NOCOPY Number
681 ,p_object_version_number IN OUT NOCOPY Number
682 ,p_training_plan_member_id in number
683 ,p_training_plan_id in number
684 ) is
685
686
687 l_learning_path_member_id number;
688 l_object_version_number number;
689 l_err_code varchar2(72);
690 l_err_msg varchar2(2000);
691 l_number number;
692
693
694 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
695 hr_dflex_utility.l_ignore_dfcode_varray();
696
697
698 BEGIN
699 l_add_struct_d.extend(1);
700 l_add_struct_d(l_add_struct_d.count) := 'OTA_LEARNING_PATH_MEMBERS';
701 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
702
703
704
705 if not is_lp_mem_migrated(p_learning_path_id, p_activity_version_id
706 ,p_completion_target_date) then
707 begin
708 -- p_course_sequence := p_course_sequence + 1;
709 ota_lp_member_api.create_learning_path_member(
710 p_effective_date => trunc(sysdate)
711 ,p_business_group_id => p_business_group_id
712 ,p_learning_path_id => p_learning_path_id
713 ,p_activity_version_id => p_activity_version_id
714 ,p_course_sequence => p_course_sequence
715 ,p_learning_path_section_id => p_learning_path_section_id
716 ,p_learning_path_member_id => l_learning_path_member_id
717 ,p_object_version_number => l_object_version_number
718 );
719 set_lp_mem_map(p_upgrade_id,p_learning_path_id,p_activity_version_id
720 ,p_completion_target_date,l_learning_path_member_id);
721 Exception
722 when others then
723 l_err_code := SQLCODE;
724 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path Member for Training Plan Member');
725 --p_course_sequence := p_course_sequence-1;
726 ota_classic_upgrade.add_log_entry( p_table_name=>p_table_name
727 ,p_source_primary_key => p_training_plan_member_id
728 ,p_business_group_id => p_business_group_id
729 ,p_object_value => p_activity_version_id
730 ,p_message_text => l_err_msg
731 ,p_upgrade_id => p_upgrade_id
732 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPGRADE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
733 ,p_log_type => LP_LOG_TYPE_E
734 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
735
736 End;
737 else
738 l_learning_path_member_id := get_lp_mem_map(p_learning_path_id
739 , p_activity_version_id,p_completion_target_date);
740 end if;
741
742 p_learning_path_member_id := l_learning_path_member_id;
743 p_object_version_number := l_object_version_number;
744
745 hr_dflex_utility.remove_ignore_df_validation;
746 END create_learning_path_members;
747
748 -- ----------------------------------------------------------------------------
749 -- |---------------------< create_lp_member_enrollment >---------------------|
750 -- ----------------------------------------------------------------------------
751
752 Procedure create_lp_member_enrollment(
753 p_lp_member_enrollment_id IN OUT NOCOPY Number
754 ,p_lp_enrollment_id in number
755 ,p_learning_path_section_id in number
756 ,p_learning_path_member_id in number
757 ,p_member_status_code in varchar
758 ,p_completion_target_date in date
759 ,p_creator_person_id in number --bug 3984648
760 -- ,p_completion_date in date
761 ,p_business_group_id in number
762 -- ,p_person_id in number
763 ,p_activity_version_id in number
764 -- ,p_contact_id in number
765 ,p_table_name in varchar
766 ,p_upgrade_id in number
767 ,p_object_version_number IN OUT NOCOPY Number
768 ,p_training_plan_member_id in number
769 ,p_attribute_category IN VARCHAR2
770 ,p_attribute1 IN VARCHAR2
771 ,p_attribute2 IN VARCHAR2
772 ,p_attribute3 IN VARCHAR2
773 ,p_attribute4 IN VARCHAR2
774 ,p_attribute5 IN VARCHAR2
775 ,p_attribute6 IN VARCHAR2
776 ,p_attribute7 IN VARCHAR2
777 ,p_attribute8 IN VARCHAR2
778 ,p_attribute9 IN VARCHAR2
779 ,p_attribute10 IN VARCHAR2
780 ,p_attribute11 IN VARCHAR2
781 ,p_attribute12 IN VARCHAR2
782 ,p_attribute13 IN VARCHAR2
783 ,p_attribute14 IN VARCHAR2
784 ,p_attribute15 IN VARCHAR2
785 ,p_attribute16 IN VARCHAR2
786 ,p_attribute17 IN VARCHAR2
787 ,p_attribute18 IN VARCHAR2
788 ,p_attribute19 IN VARCHAR2
789 ,p_attribute20 IN VARCHAR2
790 ,p_attribute21 IN VARCHAR2
791 ,p_attribute22 IN VARCHAR2
792 ,p_attribute23 IN VARCHAR2
793 ,p_attribute24 IN VARCHAR2
794 ,p_attribute25 IN VARCHAR2
795 ,p_attribute26 IN VARCHAR2
796 ,p_attribute27 IN VARCHAR2
797 ,p_attribute28 IN VARCHAR2
798 ,p_attribute29 IN VARCHAR2
799 ,p_attribute30 IN VARCHAR2
800 ) is
801
802 l_date_status_changed date;
803 l_lp_member_enrollment_id number;
804 l_object_version_number number;
805 l_err_code varchar2(72);
806 l_err_msg varchar2(2000);
807 l_number number;
808
809 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
810 hr_dflex_utility.l_ignore_dfcode_varray();
811
812
813 BEGIN
814 l_add_struct_d.extend(1);
815 l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_MEMBER_ENROLLMENTS';
816 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
817
818
819 if not is_lp_mem_enr_migrated(p_training_plan_member_id) then
820 l_date_status_changed := null;
821
822
823 begin
824 ota_lp_member_enrollment_api.create_lp_member_enrollment(
825 p_effective_date => trunc(sysdate)
826 ,p_lp_member_enrollment_id => l_lp_member_enrollment_id
827 ,p_lp_enrollment_id => p_lp_enrollment_id
828 ,p_learning_path_section_id => p_learning_path_section_id
829 ,p_learning_path_member_id => p_learning_path_member_id
830 ,p_member_status_code => get_status(p_member_status_code)
831 ,p_completion_target_date => p_completion_target_date
832 -- ,p_completion_date => l_date_status_changed
833 ,p_business_group_id => p_business_group_id
834 ,p_object_version_number => p_object_version_number
835 ,p_creator_person_id => p_creator_person_id -- bug no 3984648
836 ,p_attribute_category => p_attribute_category
837 ,p_attribute1 => p_attribute1
838 ,p_attribute2 => p_attribute2
839 ,p_attribute3 => p_attribute3
840 ,p_attribute4 => p_attribute4
841 ,p_attribute5 => p_attribute5
842 ,p_attribute6 => p_attribute6
843 ,p_attribute7 => p_attribute7
844 ,p_attribute8 => p_attribute8
845 ,p_attribute9 => p_attribute9
846 ,p_attribute10 => p_attribute10
847 ,p_attribute11 => p_attribute11
848 ,p_attribute12 => p_attribute12
849 ,p_attribute13 => p_attribute13
850 ,p_attribute14 => p_attribute14
851 ,p_attribute15 => p_attribute15
852 ,p_attribute16 => p_attribute16
853 ,p_attribute17 => p_attribute17
854 ,p_attribute18 => p_attribute18
855 ,p_attribute19 => p_attribute19
856 ,p_attribute20 => p_attribute20
857 ,p_attribute21 => p_attribute21
858 ,p_attribute22 => p_attribute22
859 ,p_attribute23 => p_attribute23
860 ,p_attribute24 => p_attribute24
861 ,p_attribute25 => p_attribute25
862 ,p_attribute26 => p_attribute26
863 ,p_attribute27 => p_attribute27
864 ,p_attribute28 => p_attribute28
865 ,p_attribute29 => p_attribute29
866 ,p_attribute30 => p_attribute30
867 );
868
869 set_lp_mem_enr_map(p_upgrade_id
870 ,p_training_plan_member_id,l_lp_member_enrollment_id);
871
872
873
874
875 Exception
876 when others then
877 l_err_code := SQLCODE;
878 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path Member Enrollment for Training Plan Member');
879
880 ota_classic_upgrade.add_log_entry( p_table_name=>p_table_name
881 ,p_source_primary_key => p_training_plan_member_id
882 ,p_business_group_id => p_business_group_id
883 ,p_object_value => p_learning_path_member_id
884 ,p_message_text => l_err_msg
885 ,p_upgrade_id => p_upgrade_id
886 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPGRADE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
887 ,p_log_type => LP_LOG_TYPE_E
888 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
889
890 l_lp_member_enrollment_id := -1;
891
892 End;
893 else
894 l_lp_member_enrollment_id := get_lp_mem_enr_map(p_training_plan_member_id);
895 end if;
896 --close csr_get_dup_lpme_rec;
897 p_lp_member_enrollment_id := l_lp_member_enrollment_id;
898 hr_dflex_utility.remove_ignore_df_validation;
899 end create_lp_member_enrollment;
900
901
902 -- ----------------------------------------------------------------------------
903 -- |-------------------------< create_lp_enrollment >-------------------------|
904 -- ----------------------------------------------------------------------------
905 PROCEDURE create_lp_enrollment(p_name in varchar2
906 ,p_learning_path_id in number
907 ,p_Completion_target_date in date
908 ,p_Person_id in number
909 ,p_Contact_id in number default null
910 ,p_path_status_code in varchar2
911 ,p_creator_person_id in number
912 ,p_business_group_id in number
913 ,p_enrollment_source_code in varchar2
914 ,p_lp_enrollment_id IN OUT NOCOPY Number
915 ,p_object_version_number IN OUT NOCOPY Number
916 ,p_table_name in varchar
917 ,p_upgrade_id in number
918 ,p_training_plan_id IN number
919 ,p_source_id IN number DEFAULT NULL
920 ,p_source_function IN varchar2 DEFAULT NULL
921 ,p_assignment_id IN number DEFAULT NULL
922 ,p_attribute_category IN VARCHAR2
923 ,p_attribute1 IN VARCHAR2
924 ,p_attribute2 IN VARCHAR2
925 ,p_attribute3 IN VARCHAR2
926 ,p_attribute4 IN VARCHAR2
927 ,p_attribute5 IN VARCHAR2
928 ,p_attribute6 IN VARCHAR2
929 ,p_attribute7 IN VARCHAR2
930 ,p_attribute8 IN VARCHAR2
931 ,p_attribute9 IN VARCHAR2
932 ,p_attribute10 IN VARCHAR2
933 ,p_attribute11 IN VARCHAR2
934 ,p_attribute12 IN VARCHAR2
935 ,p_attribute13 IN VARCHAR2
936 ,p_attribute14 IN VARCHAR2
937 ,p_attribute15 IN VARCHAR2
938 ,p_attribute16 IN VARCHAR2
939 ,p_attribute17 IN VARCHAR2
940 ,p_attribute18 IN VARCHAR2
941 ,p_attribute19 IN VARCHAR2
942 ,p_attribute20 IN VARCHAR2
943 ,p_attribute21 IN VARCHAR2
944 ,p_attribute22 IN VARCHAR2
945 ,p_attribute23 IN VARCHAR2
946 ,p_attribute24 IN VARCHAR2
947 ,p_attribute25 IN VARCHAR2
948 ,p_attribute26 IN VARCHAR2
949 ,p_attribute27 IN VARCHAR2
950 ,p_attribute28 IN VARCHAR2
951 ,p_attribute29 IN VARCHAR2
952 ,p_attribute30 IN VARCHAR2
953 ) is
954
955 l_lp_enrollment_id number;
956 l_object_version_number number;
957 l_err_code varchar2(72);
958 l_err_msg varchar2(2000);
959 l_number number;
960
961 --
962 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
963 hr_dflex_utility.l_ignore_dfcode_varray();
964
965
966 BEGIN
967 l_add_struct_d.extend(1);
968 l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_ENROLLMENTS';
969 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
970
971
972 if not is_lp_enr_migrated(p_training_plan_id
973 ,p_source_id, p_source_function, p_assignment_id) then
974 begin
975 -- ota_lp_enrollment_api.create_lp_enrollment(
976 ota_lpe_ins.ins(
977 p_effective_date => trunc(sysdate)
978 ,p_learning_path_id => p_learning_path_id
979 ,p_Completion_target_date => p_Completion_target_date
980 ,p_Person_id => p_Person_id
981 ,p_Contact_id => p_Contact_id
982 ,p_path_status_code => get_status(p_path_status_code)
983 ,p_Creator_person_id => p_creator_person_id
984 ,p_business_group_id => p_business_group_id
985 ,p_Enrollment_source_code => p_Enrollment_source_code
986 ,p_lp_enrollment_id => l_lp_enrollment_id
987 ,p_object_version_number => l_object_version_number
988 ,p_attribute_category => p_attribute_category
989 ,p_attribute1 => p_attribute1
990 ,p_attribute2 => p_attribute2
991 ,p_attribute3 => p_attribute3
992 ,p_attribute4 => p_attribute4
993 ,p_attribute5 => p_attribute5
994 ,p_attribute6 => p_attribute6
995 ,p_attribute7 => p_attribute7
996 ,p_attribute8 => p_attribute8
997 ,p_attribute9 => p_attribute9
998 ,p_attribute10 => p_attribute10
999 ,p_attribute11 => p_attribute11
1000 ,p_attribute12 => p_attribute12
1001 ,p_attribute13 => p_attribute13
1002 ,p_attribute14 => p_attribute14
1003 ,p_attribute15 => p_attribute15
1004 ,p_attribute16 => p_attribute16
1005 ,p_attribute17 => p_attribute17
1006 ,p_attribute18 => p_attribute18
1007 ,p_attribute19 => p_attribute19
1008 ,p_attribute20 => p_attribute20
1009 ,p_attribute21 => p_attribute21
1010 ,p_attribute22 => p_attribute22
1011 ,p_attribute23 => p_attribute23
1012 ,p_attribute24 => p_attribute24
1013 ,p_attribute25 => p_attribute25
1014 ,p_attribute26 => p_attribute26
1015 ,p_attribute27 => p_attribute27
1016 ,p_attribute28 => p_attribute28
1017 ,p_attribute29 => p_attribute29
1018 ,p_attribute30 => p_attribute30
1019
1020 );
1021
1022 set_lp_enr_map(p_upgrade_id,p_training_plan_id,l_lp_enrollment_id, p_source_id, p_source_function
1023 ,p_assignment_id);
1024 Exception
1025 when others then
1026 l_err_code := SQLCODE;
1027 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path Enrollments for Training Plan ');
1028
1029 ota_classic_upgrade.add_log_entry( p_table_name=>p_table_name
1030 ,p_source_primary_key => p_training_plan_id
1031 ,p_business_group_id => p_business_group_id
1032 ,p_object_value => p_name
1033 ,p_message_text => l_err_msg
1034 ,p_upgrade_id => p_upgrade_id
1035 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPGRADE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
1036 ,p_log_type => LP_LOG_TYPE_E
1037 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
1038
1039 End;
1040
1041 else
1042 l_lp_enrollment_id := get_lp_enr_map(p_training_plan_id,p_source_id, p_source_function
1043 ,p_assignment_id);
1044 end if;
1045 -- close csr_get_dup_lpe_rec;
1046 p_lp_enrollment_id := l_lp_enrollment_id;
1047 hr_dflex_utility.remove_ignore_df_validation;
1048 end create_lp_enrollment;
1049 -- ----------------------------------------------------------------------------
1050 -- |-------------------------< upg_cat_lp_to_section >------------------------|
1051 -- ----------------------------------------------------------------------------
1052 PROCEDURE upg_cat_lp_to_section (
1053 p_process_control IN varchar2,
1054 p_start_pkid IN number,
1055 p_end_pkid IN number,
1056 p_rows_processed OUT nocopy number,
1057 p_update_id IN number default 1 --CONC_UPGRADE_ID
1058 ) IS
1059 /*
1060 Upgrade existing catalog learning paths to have sections
1061 */
1062 l_learning_path_id number;
1063 l_object_version_number number;
1064 l_learning_path_section_id number;
1065 l_number number;
1066 l_err_code varchar2(72);
1067 l_err_msg varchar2(2000);
1068 l_rows_processed number;
1069 l_upgrade_id number;
1070
1071
1072 CURSOR csr_get_lp IS
1073 SELECT lps.learning_path_id, lpst.name , lps.business_group_id
1074 FROM ota_learning_paths lps, ota_learning_paths_tl lpst
1075 WHERE lpst.learning_path_id = lps.learning_path_id
1076 AND lpst.language = USERENV('LANG')
1077 AND lps.path_source_code = 'CATALOG'
1078 AND lps.learning_path_id between p_start_pkid and p_end_pkid;
1079
1080
1081
1082 cursor csr_get_dup_lpc_rec(p_learning_path_id in number,p_section_name in varchar) is
1083 select lpc.learning_path_section_id
1084 from ota_lp_sections_tl lpct,ota_lp_sections lpc
1085 where lpc.learning_path_section_id = lpct.learning_path_section_id
1086 and lpct.language=userenv('LANG')
1087 and lpc.learning_path_id = p_learning_path_id
1088 and lpct.name = p_section_name ;
1089
1090 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
1091 hr_dflex_utility.l_ignore_dfcode_varray();
1092
1093
1094
1095 BEGIN
1096
1097
1098 -- Update path_source_code_code_code to CATALOG and display_to_learner_flag to 'Y'
1099 -- for existing data in ota_learning_paths
1100 l_upgrade_id := null;
1101
1102 select max(upgrade_id) INTO l_upgrade_id
1103 from ota_upgrade_log
1104 where upgrade_name = LP_UPGRADE_NAME;
1105
1106 if l_upgrade_id is null then
1107 ota_classic_upgrade.add_log_entry( p_table_name=> 'DUMMY'
1108 ,p_source_primary_key => 1
1109 ,p_business_group_id => null
1110 ,p_object_value => null
1111 ,p_message_text => 'Starting LP Upgrade'
1112 ,p_upgrade_id => 1
1113 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
1114 ,p_log_type => LP_LOG_TYPE_N
1115 ,p_upgrade_name => LP_UPGRADE_NAME );
1116
1117 commit;
1118 end if;
1119 FOR lp_rec IN csr_get_lp LOOP
1120 l_learning_path_id := lp_rec.learning_path_id;
1121 l_add_struct_d.extend(1);
1122 l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_SECTIONS';
1123 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
1124
1125 open csr_get_dup_lpc_rec(l_learning_path_id, lp_rec.name);
1126 fetch csr_get_dup_lpc_rec into l_learning_path_section_id;
1127 if csr_get_dup_lpc_rec%notfound then
1128
1129 --Create a New Learning Path Section
1130 l_learning_path_section_id := null;
1131 begin
1132 ota_lpc_ins.ins(
1133 p_effective_date => trunc(sysdate)
1134 ,p_section_sequence => 1
1135 ,p_completion_type_code => 'M'
1136 ,p_business_group_id => lp_rec.business_group_id
1137 ,p_learning_path_id => l_learning_path_id
1138 ,p_learning_path_section_id => l_learning_path_section_id
1139 ,p_object_version_number => l_object_version_number);
1140
1141
1142 Insert into ota_lp_sections_tl
1143 (learning_path_section_id,
1144 Language,
1145 name,
1146 Description,
1147 Source_Lang,
1148 Created_By,
1149 Creation_Date,
1150 Last_Updated_By,
1151 Last_Update_Date,
1152 Last_Update_Login )
1153 Select l_learning_path_section_id,
1154 M.language,
1155 M.name,
1156 M.description,
1157 M.source_lang,
1158 M.Created_By,
1159 M.Creation_date,
1160 M.Last_Updated_By,
1161 M.Last_Update_Date,
1162 M.Last_Update_Login
1163 From Ota_learning_paths_tl M
1164 Where M.learning_path_id = l_learning_path_id;
1165
1166 Exception
1167 when others then
1168 l_err_code := SQLCODE;
1169 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Learning Path Sections for Training Plan ');
1170
1171 ota_classic_upgrade.add_log_entry( p_table_name=> 'CREATE_LPC_FOR_TPS_FOR_CAT'
1172 ,p_source_primary_key => lp_rec.business_group_id
1173 ,p_business_group_id => lp_rec.business_group_id
1174 ,p_object_value => lp_rec.name
1175 ,p_message_text => l_err_msg
1176 ,p_upgrade_id => p_update_id
1177 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
1178 ,p_log_type => LP_LOG_TYPE_E
1179 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
1180
1181 End;
1182 end if;
1183 close csr_get_dup_lpc_rec;
1184 hr_dflex_utility.remove_ignore_df_validation;
1185
1186
1187
1188
1189 if l_learning_path_section_id is not null then
1190 UPDATE ota_learning_path_members
1191 SET learning_path_section_id = l_learning_path_section_id --//Section_id returned from create call
1192 where learning_path_id = l_learning_path_id;
1193 end if;
1194
1195
1196
1197 END LOOP;
1198
1199
1200 Select nvl(count(1),0)
1201 into l_rows_processed
1202 from ota_learning_paths
1203 where learning_path_id between p_start_pkid and p_end_pkid;
1204
1205 p_rows_processed := l_rows_processed;
1206 END upg_cat_lp_to_section;
1207
1208 -- ----------------------------------------------------------------------------
1209 -- |--------------------< upg_tp_for_lrnr_and_mgr_to_lp >---------------------|
1210 -- ----------------------------------------------------------------------------
1211 procedure upg_tp_for_lrnr_and_mgr_to_lp (p_process_control IN varchar2,
1212 p_start_pkid IN number,
1213 p_end_pkid IN number,
1214 p_rows_processed OUT nocopy number,
1215 p_update_id in number default 1
1216 ) is
1217
1218 l_training_plan_id number;
1219 l_object_version_number number;
1220 l_learning_path_id number;
1221 l_lp_enrollment_id number;
1222 l_learning_path_section_id number;
1223 l_learning_path_member_id number;
1224 l_lp_member_enrollment_id number;
1225 l_total_comp number;
1226 l_completed_comp number;
1227 l_seq number;
1228 l_Completion_date date;
1229 l_number number;
1230 l_err_code varchar2(72);
1231 l_err_msg varchar2(2000);
1232 l_rows_processed number;
1233 l_end_date date;
1234
1235 l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1236 l_old_status ota_lp_member_enrollments.member_status_code%type;
1237 l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1238 l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1239 --
1240 CURSOR csr_get_tp IS
1241 SELECT tps.training_plan_id, tps.business_group_id,
1242 tps.name, tps.description,
1243 tps.START_DATE, tps.end_date,
1244 tps.plan_source, tps.plan_status_type_id
1245 -- Source_Function_Code,
1246 , tps.person_id, tps.contact_id
1247 ,tps.ATTRIBUTE_CATEGORY,tps.ATTRIBUTE1
1248 ,tps.ATTRIBUTE2 ,tps.ATTRIBUTE3
1249 ,tps.ATTRIBUTE4 ,tps.ATTRIBUTE5
1250 ,tps.ATTRIBUTE6 ,tps.ATTRIBUTE7
1251 ,tps.ATTRIBUTE8 ,tps.ATTRIBUTE9
1252 ,tps.ATTRIBUTE10 ,tps.ATTRIBUTE11
1253 ,tps.ATTRIBUTE12 ,tps.ATTRIBUTE13
1254 ,tps.ATTRIBUTE14 ,tps.ATTRIBUTE15
1255 ,tps.ATTRIBUTE16 ,tps.ATTRIBUTE17
1256 ,tps.ATTRIBUTE18 ,tps.ATTRIBUTE19
1257 ,tps.ATTRIBUTE20 ,tps.ATTRIBUTE21
1258 ,tps.ATTRIBUTE22 ,tps.ATTRIBUTE23
1259 ,tps.ATTRIBUTE24 ,tps.ATTRIBUTE25
1260 ,tps.ATTRIBUTE26 ,tps.ATTRIBUTE27
1261 ,tps.ATTRIBUTE28 ,tps.ATTRIBUTE29
1262 ,tps.ATTRIBUTE30 ,tps.creator_person_id
1263 FROM ota_training_plans tps
1264 WHERE tps.plan_source NOT IN ('TALENT_MGMT','CATALOG')
1265 AND (tps.PERSON_ID is not NULL OR contact_id is not null)
1266 AND tps.learning_path_id is null
1267 AND tps.training_plan_id between p_start_pkid AND p_end_pkid;
1268 --
1269 CURSOR csr_get_tpm(p_training_plan_id NUMBER) IS
1270 SELECT tpm.training_plan_member_id, tpm.business_group_id
1271 ,tpm.activity_version_id
1272 ,tpm.member_status_type_id, tpm.target_completion_date
1273 ,tpm.ATTRIBUTE_CATEGORY ,tpm.ATTRIBUTE1
1274 ,tpm.ATTRIBUTE2 ,tpm.ATTRIBUTE3
1275 ,tpm.ATTRIBUTE4 ,tpm.ATTRIBUTE5
1276 ,tpm.ATTRIBUTE6 ,tpm.ATTRIBUTE7
1277 ,tpm.ATTRIBUTE8 ,tpm.ATTRIBUTE9
1278 ,tpm.ATTRIBUTE10 ,tpm.ATTRIBUTE11
1279 ,tpm.ATTRIBUTE12 ,tpm.ATTRIBUTE13
1280 ,tpm.ATTRIBUTE14 ,tpm.ATTRIBUTE15
1281 ,tpm.ATTRIBUTE16 ,tpm.ATTRIBUTE17
1282 ,tpm.ATTRIBUTE18 ,tpm.ATTRIBUTE19
1283 ,tpm.ATTRIBUTE20 ,tpm.ATTRIBUTE21
1284 ,tpm.ATTRIBUTE22 ,tpm.ATTRIBUTE23
1285 ,tpm.ATTRIBUTE24 ,tpm.ATTRIBUTE25
1286 ,tpm.ATTRIBUTE26 ,tpm.ATTRIBUTE27
1287 ,tpm.ATTRIBUTE28 ,tpm.ATTRIBUTE29
1288 ,tpm.ATTRIBUTE30
1289 FROM ota_training_plan_members tpm
1290 WHERE tpm.training_plan_id = p_training_plan_id;
1291 --
1292
1293 BEGIN
1294
1295 FOR tp_rec IN csr_get_tp LOOP
1296
1297 l_learning_path_id := null;
1298 l_lp_enrollment_id := null;
1299 l_learning_path_section_id := null;
1300
1301 if to_char(tp_rec.end_date,'dd/mm/rrrr') = '31/12/4712' then
1302 l_end_date := null;
1303 else
1304 l_end_date := tp_rec.end_date;
1305 end if;
1306 -- Create learning paths from training plans
1307 create_learning_path(p_name => tp_rec.name
1308 ,p_business_group_id => tp_rec.business_group_id
1309 ,p_start_date_active => tp_rec.start_date
1310 ,p_end_date_active => l_end_date
1311 ,p_description => tp_rec.description
1312 ,p_path_source_code => tp_rec.PLAN_SOURCE
1313
1314 ,p_person_id => tp_rec.person_id
1315 ,p_contact_id => tp_rec.contact_id
1316 ,p_table_name => 'CREATE_LPS_FOR_TPS_LRNR_AND_MGR'
1317 ,p_upgrade_id => p_update_id
1318 ,p_learning_path_id => l_learning_path_id
1319 ,p_object_version_number => l_object_version_number
1320 ,p_source_function_code => null
1321 ,p_assignment_id => null
1322 ,p_source_id => null
1323 ,p_display_to_learner_flag => 'Y'
1324 ,p_training_plan_id => tp_rec.training_plan_id);
1325
1326 --Create a learning path section
1327 if l_learning_path_id is not null then
1328 create_lp_sections(
1329 p_section_name => tp_rec.name
1330 ,p_description => tp_rec.description
1331 ,p_section_sequence => 1
1332 ,p_completion_type_code => 'M'
1333 ,p_business_group_id => tp_rec.business_group_id
1334 ,p_table_name => 'CREATE_LPC_FOR_TPS_LRNR_AND_MGR'
1335 ,p_upgrade_id => p_update_id
1336 ,p_learning_path_id => l_learning_path_id
1337 ,p_learning_path_section_id => l_learning_path_section_id
1338 ,p_object_version_number => l_object_version_number);
1339
1340
1341
1342 --Create a learning path enrollments
1343 if l_learning_path_section_id is not null then
1344 create_lp_enrollment(p_name => tp_rec.name
1345 ,p_learning_path_id => l_learning_path_id
1346 ,p_Completion_target_date => l_end_date
1347 ,p_Person_id => tp_rec.person_id
1348 ,p_Contact_id => tp_rec.contact_id
1349 ,p_path_status_code => tp_rec.plan_status_type_id
1350 ,p_Creator_person_id => tp_rec.creator_person_id
1351 ,p_business_group_id => tp_rec.business_group_id
1352 ,p_Enrollment_source_code => tp_rec.plan_source
1353 ,p_lp_enrollment_id => l_lp_enrollment_id
1354 ,p_object_version_number => l_object_version_number
1355 ,p_table_name => 'CREATE_LPE_FOR_TPS_LRNR_AND_MGR'
1356 ,p_upgrade_id => p_update_id
1357 ,p_training_plan_id => tp_rec.training_plan_id
1358 ,p_attribute_category => tp_rec.attribute_category
1359 ,p_attribute1 => tp_rec.attribute1
1360 ,p_attribute2 => tp_rec.attribute2
1361 ,p_attribute3 => tp_rec.attribute3
1362 ,p_attribute4 => tp_rec.attribute4
1363 ,p_attribute5 => tp_rec.attribute5
1364 ,p_attribute6 => tp_rec.attribute6
1365 ,p_attribute7 => tp_rec.attribute7
1366 ,p_attribute8 => tp_rec.attribute8
1367 ,p_attribute9 => tp_rec.attribute9
1368 ,p_attribute10 => tp_rec.attribute10
1369 ,p_attribute11 => tp_rec.attribute11
1370 ,p_attribute12 => tp_rec.attribute12
1371 ,p_attribute13 => tp_rec.attribute13
1372 ,p_attribute14 => tp_rec.attribute14
1373 ,p_attribute15 => tp_rec.attribute15
1374 ,p_attribute16 => tp_rec.attribute16
1375 ,p_attribute17 => tp_rec.attribute17
1376 ,p_attribute18 => tp_rec.attribute18
1377 ,p_attribute19 => tp_rec.attribute19
1378 ,p_attribute20 => tp_rec.attribute20
1379 ,p_attribute21 => tp_rec.attribute21
1380 ,p_attribute22 => tp_rec.attribute22
1381 ,p_attribute23 => tp_rec.attribute23
1382 ,p_attribute24 => tp_rec.attribute24
1383 ,p_attribute25 => tp_rec.attribute25
1384 ,p_attribute26 => tp_rec.attribute26
1385 ,p_attribute27 => tp_rec.attribute27
1386 ,p_attribute28 => tp_rec.attribute28
1387 ,p_attribute29 => tp_rec.attribute29
1388 ,p_attribute30 => tp_rec.attribute30
1389 );
1390
1391
1392 l_total_comp := 0;
1393 l_completed_comp := 0;
1394 l_seq :=0;
1395 --Create learning path components for the learning path created above
1396 FOR tpm_rec IN csr_get_tpm(tp_rec.training_plan_id)
1397 LOOP
1398 l_learning_path_member_id := null;
1399 l_lp_member_enrollment_id := null;
1400 l_seq := l_seq + 1;
1401 l_member_status_code := tpm_rec.member_status_type_id;
1402 l_date_status_changed := null;
1403
1404 if l_lp_enrollment_id is not null then
1405
1406 create_learning_path_members(
1407 p_business_group_id => tpm_rec.business_group_id
1408 ,p_learning_path_id => l_learning_path_id
1409 ,p_activity_version_id => tpm_rec.activity_version_id
1410 ,p_course_sequence => l_seq
1411 ,p_completion_target_date => tpm_rec.target_completion_date
1412 ,p_learning_path_section_id => l_learning_path_section_id
1413 ,p_learning_path_member_id => l_learning_path_member_id
1414 ,p_object_version_number => l_object_version_number
1415 ,p_table_name => 'CREATE_LPM_FOR_TPM_LRNR_AND_MGR'
1416 ,p_upgrade_id => p_update_id
1417 ,p_training_plan_member_id => tpm_rec.training_plan_member_id
1418 ,p_training_plan_id => tp_rec.training_plan_id);
1419
1420 --Create a record in table ota_lp_member_enrollments
1421 if l_learning_path_member_id is not null then
1422 create_lp_member_enrollment(
1423 p_lp_member_enrollment_id => l_lp_member_enrollment_id
1424 ,p_lp_enrollment_id => l_lp_enrollment_id
1425 ,p_learning_path_section_id=> l_learning_path_section_id
1426 ,p_learning_path_member_id => l_learning_path_member_id
1427 ,p_member_status_code => tpm_rec.member_status_type_id
1428 ,p_completion_target_date => tpm_rec.target_completion_date
1429 -- ,p_completion_date => l_date_status_changed
1430 ,p_business_group_id => tpm_rec.business_group_id
1431 ,p_creator_person_id => tp_rec.creator_person_id -- bug no 3984648
1432 -- ,p_person_id => tp_rec.person_id
1433 ,p_activity_version_id => tpm_rec.activity_version_id
1434 -- ,p_contact_id => tp_rec.contact_id
1435 ,p_table_name => 'CREATE_LPME_FOR_TPM_LRNR_AND_MGR'
1436 ,p_upgrade_id => p_update_id
1437 ,p_object_version_number => l_object_version_number
1438 ,p_training_plan_member_id => tpm_rec.training_plan_member_id
1439 ,p_attribute_category => tpm_rec.attribute_category
1440 ,p_attribute1 => tp_rec.attribute1
1441 ,p_attribute2 => tpm_rec.attribute2
1442 ,p_attribute3 => tpm_rec.attribute3
1443 ,p_attribute4 => tpm_rec.attribute4
1444 ,p_attribute5 => tpm_rec.attribute5
1445 ,p_attribute6 => tpm_rec.attribute6
1446 ,p_attribute7 => tpm_rec.attribute7
1447 ,p_attribute8 => tpm_rec.attribute8
1448 ,p_attribute9 => tpm_rec.attribute9
1449 ,p_attribute10 => tpm_rec.attribute10
1450 ,p_attribute11 => tpm_rec.attribute11
1451 ,p_attribute12 => tpm_rec.attribute12
1452 ,p_attribute13 => tpm_rec.attribute13
1453 ,p_attribute14 => tpm_rec.attribute14
1454 ,p_attribute15 => tpm_rec.attribute15
1455 ,p_attribute16 => tpm_rec.attribute16
1456 ,p_attribute17 => tpm_rec.attribute17
1457 ,p_attribute18 => tpm_rec.attribute18
1458 ,p_attribute19 => tpm_rec.attribute19
1459 ,p_attribute20 => tpm_rec.attribute20
1460 ,p_attribute21 => tpm_rec.attribute21
1461 ,p_attribute22 => tpm_rec.attribute22
1462 ,p_attribute23 => tpm_rec.attribute23
1463 ,p_attribute24 => tpm_rec.attribute24
1464 ,p_attribute25 => tpm_rec.attribute25
1465 ,p_attribute26 => tpm_rec.attribute26
1466 ,p_attribute27 => tpm_rec.attribute27
1467 ,p_attribute28 => tpm_rec.attribute28
1468 ,p_attribute29 => tpm_rec.attribute29
1469 ,p_attribute30 => tpm_rec.attribute30);
1470 end if;
1471 end if;
1472 END LOOP;
1473
1474
1475
1476 end if;
1477 end if;
1478
1479 END LOOP;
1480 Select nvl(count(1),0)
1481 into l_rows_processed
1482 from ota_training_plans
1483 where training_plan_id between p_start_pkid and p_end_pkid;
1484
1485 p_rows_processed := l_rows_processed;
1486 end upg_tp_for_lrnr_and_mgr_to_lp;
1487
1488
1489
1490 -- ----------------------------------------------------------------------------
1491 -- |---------------------< upg_tp_to_lp_for_talent_mgmt >---------------------|
1492 -- ----------------------------------------------------------------------------
1493 procedure upg_tp_to_lp_for_talent_mgmt (
1494 p_process_control IN varchar2,
1495 p_start_pkid IN number,
1496 p_end_pkid IN number,
1497 p_rows_processed OUT nocopy number,
1498 p_update_id in number default 1 --CONC_UPGRADE_ID
1499 ) is
1500
1501
1502 l_training_plan_id number;
1503 l_object_version_number number;
1504 l_learning_path_id number;
1505 l_lp_enrollment_id number;
1506 l_learning_path_section_id number;
1507 l_learning_path_member_id number;
1508 l_lp_member_enrollment_id number;
1509 l_total_comp number;
1510 l_completed_comp number;
1511 l_flag boolean;
1512 l_seq number;
1513 l_Completion_date date;
1514 l_err_code varchar2(72);
1515 l_err_msg varchar2(2000);
1516 l_number number;
1517 l_count number;
1518 l_rows_processed number;
1519 l_end_date date;
1520 l_member_status_type_flag boolean;
1521 l_status_code ota_lp_enrollments.path_status_code%type;
1522 l_status_type_id varchar2(30);
1523 l_old_status ota_lp_member_enrollments.member_status_code%type;
1524 l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1525 l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1526 l_name varchar2(80);
1527 l_lang_len number;
1528 MAX_NAME_LEN constant number := 80;
1529 MAX_DATA_TRUNC_LEN constant number := 10;
1530 l_display_to_learner_flag ota_learning_paths.display_to_learner_flag%type;
1531 l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1532 --
1533
1534
1535
1536 cursor csr_get_talent_mgmt_comb is
1537 SELECT distinct Tpm.source_id,Tpm.source_function, Tpm.assignment_id, tps.person_id
1538 FROM ota_training_plans tps,Ota_training_plan_members tpm
1539 WHERE PLAN_SOURCE = 'TALENT_MGMT'
1540 AND tpm.training_plan_id = tps.training_plan_id
1541 AND tps.PERSON_ID is not NULL;
1542
1543
1544 cursor csr_get_talent_mgmt_rec (p_source_id in number
1545 ,p_source_function in varchar2, p_assignment_id in number, p_person_id IN NUMBER) is
1546 SELECT tps.training_plan_id,
1547 Tps.name,
1548 Tps.description,
1549 Tps.business_group_id,
1550 Tps.start_date,
1551 Tps.end_date,
1552 Tps.Plan_status_type_id,
1553 Tps.creator_person_id,
1554 Tps.plan_source,
1555 Tpm.training_plan_member_id,
1556 Tpm.activity_version_id,
1557 Tpm.target_completion_date,
1558 Tpm.cancellation_reason,
1559 Tpm.member_status_type_id,
1560 Tps.person_id,
1561 Tps.contact_id,
1562 Tpm.assignment_id,
1563 Tpm.source_id,
1564 Tpm.source_function,
1565 Tpm.creator_person_id tpm_creator
1566 ,tpm.ATTRIBUTE_CATEGORY tpm_ATTRIBUTE_CATEGORY
1567 ,tpm.ATTRIBUTE1 tpm_ATTRIBUTE1
1568 ,tpm.ATTRIBUTE2 tpm_ATTRIBUTE2
1569 ,tpm.ATTRIBUTE3 tpm_ATTRIBUTE3
1570 ,tpm.ATTRIBUTE4 tpm_ATTRIBUTE4
1571 ,tpm.ATTRIBUTE5 tpm_ATTRIBUTE5
1572 ,tpm.ATTRIBUTE6 tpm_ATTRIBUTE6
1573 ,tpm.ATTRIBUTE7 tpm_ATTRIBUTE7
1574 ,tpm.ATTRIBUTE8 tpm_ATTRIBUTE8
1575 ,tpm.ATTRIBUTE9 tpm_ATTRIBUTE9
1576 ,tpm.ATTRIBUTE10 tpm_ATTRIBUTE10
1577 ,tpm.ATTRIBUTE11 tpm_ATTRIBUTE11
1578 ,tpm.ATTRIBUTE12 tpm_ATTRIBUTE12
1579 ,tpm.ATTRIBUTE13 tpm_ATTRIBUTE13
1580 ,tpm.ATTRIBUTE14 tpm_ATTRIBUTE14
1581 ,tpm.ATTRIBUTE15 tpm_ATTRIBUTE15
1582 ,tpm.ATTRIBUTE16 tpm_ATTRIBUTE16
1583 ,tpm.ATTRIBUTE17 tpm_ATTRIBUTE17
1584 ,tpm.ATTRIBUTE18 tpm_ATTRIBUTE18
1585 ,tpm.ATTRIBUTE19 tpm_ATTRIBUTE19
1586 ,tpm.ATTRIBUTE20 tpm_ATTRIBUTE20
1587 ,tpm.ATTRIBUTE21 tpm_ATTRIBUTE21
1588 ,tpm.ATTRIBUTE22 tpm_ATTRIBUTE22
1589 ,tpm.ATTRIBUTE23 tpm_ATTRIBUTE23
1590 ,tpm.ATTRIBUTE24 tpm_ATTRIBUTE24
1591 ,tpm.ATTRIBUTE25 tpm_ATTRIBUTE25
1592 ,tpm.ATTRIBUTE26 tpm_ATTRIBUTE26
1593 ,tpm.ATTRIBUTE27 tpm_ATTRIBUTE27
1594 ,tpm.ATTRIBUTE28 tpm_ATTRIBUTE28
1595 ,tpm.ATTRIBUTE29 tpm_ATTRIBUTE29
1596 ,tpm.ATTRIBUTE30 tpm_ATTRIBUTE30
1597 ,tps.ATTRIBUTE_CATEGORY tps_ATTRIBUTE_CATEGORY
1598 ,tps.ATTRIBUTE1 tps_attribute1
1599 ,tps.ATTRIBUTE2 tps_attribute2
1600 ,tps.ATTRIBUTE3 tps_attribute3
1601 ,tps.ATTRIBUTE4 tps_attribute4
1602 ,tps.ATTRIBUTE5 tps_attribute5
1603 ,tps.ATTRIBUTE6 tps_attribute6
1604 ,tps.ATTRIBUTE7 tps_attribute7
1605 ,tps.ATTRIBUTE8 tps_attribute8
1606 ,tps.ATTRIBUTE9 tps_attribute9
1607 ,tps.attribute10 tps_attribute10
1608 ,tps.attribute11 tps_attribute11
1609 ,tps.attribute12 tps_attribute12
1610 ,tps.attribute13 tps_attribute13
1611 ,tps.ATTRIBUTE14 tps_attribute14
1612 ,tps.ATTRIBUTE15 tps_attribute15
1613 ,tps.ATTRIBUTE16 tps_attribute16
1614 ,tps.ATTRIBUTE17 tps_attribute17
1615 ,tps.ATTRIBUTE18 tps_attribute18
1616 ,tps.ATTRIBUTE19 tps_attribute19
1617 ,tps.ATTRIBUTE20 tps_attribute20
1618 ,tps.ATTRIBUTE21 tps_attribute21
1619 ,tps.ATTRIBUTE22 tps_attribute22
1620 ,tps.ATTRIBUTE23 tps_attribute23
1621 ,tps.ATTRIBUTE24 tps_attribute24
1622 ,tps.ATTRIBUTE25 tps_attribute25
1623 ,tps.ATTRIBUTE26 tps_attribute26
1624 ,tps.ATTRIBUTE27 tps_attribute27
1625 ,tps.ATTRIBUTE28 tps_attribute28
1626 ,tps.ATTRIBUTE29 tps_attribute29
1627 ,tps.ATTRIBUTE30 tps_ATTRIBUTE30
1628 FROM ota_training_plans tps, Ota_training_plan_members tpm
1629 WHERE tps.PLAN_SOURCE = 'TALENT_MGMT'
1630 AND tps.PERSON_ID = p_person_id
1631 --AND PERSON_ID is not NULL
1632 AND tpm.training_plan_id = tps.training_plan_id
1633 AND tpm.source_function = p_source_function
1634 AND (tpm.source_id is null or tpm.source_id = p_source_id)
1635 AND (tpm.assignment_id is null or tpm.assignment_id = p_assignment_id)
1636 order by tpm.training_plan_member_id ;
1637
1638 cursor csr_member_status (p_source_id in number
1639 ,p_source_function in varchar2, p_assignment_id in number, p_person_id IN NUMBER) is
1640 SELECT decode(member_status_type_id, 'OTA_PLANNED', 0,'ACTIVE', 0, 'OTA_AWAITING_APPROVAL',0, 'OTA_COMPLETED',1,
1641 'CANCELLED',2) top_status
1642 FROM Ota_training_plan_members tpm, ota_training_plans tps
1643 WHERE tps.training_plan_id = tpm.training_plan_id
1644 AND tpm.source_function = p_source_function
1645 AND (tpm.source_id is null or tpm.source_id = p_source_id)
1646 AND (tpm.assignment_id is null or tpm.assignment_id = p_assignment_id)
1647 AND tps.person_id = p_person_id
1648 order by top_status;
1649
1650 --
1651 --
1652
1653 BEGIN
1654
1655 FOR talent_mgmt_comb IN csr_get_talent_mgmt_comb LOOP
1656 l_flag := true;
1657 l_count := 0;
1658 l_total_comp := 0;
1659 l_completed_comp := 0;
1660
1661 /*
1662 open csr_member_status(talent_mgmt_comb.source_id
1663 ,talent_mgmt_comb.source_function, talent_mgmt_comb.assignment_id);
1664 fetch csr_member_status into l_status_type_id;
1665 if csr_member_status%notfound then
1666 l_status_code := 'CANCELLED';
1667 end if;
1668 close csr_member_status;
1669 */
1670 FOR talent_mgmt_rec IN csr_get_talent_mgmt_rec(talent_mgmt_comb.source_id
1671 ,talent_mgmt_comb.source_function, talent_mgmt_comb.assignment_id, talent_mgmt_comb.person_id) LOOP
1672 l_count := l_count + 1;
1673
1674
1675
1676 --Create learning paths from training plans
1677 --For the first record in this loop, create a learning path and a record in ota_lp_enrollments
1678 if l_flag then
1679 l_status_code := talent_mgmt_rec.plan_status_type_id;
1680 FOR lpm_rec IN csr_member_status(talent_mgmt_comb.source_id
1681 ,talent_mgmt_comb.source_function, talent_mgmt_comb.assignment_id,talent_mgmt_comb.person_id) LOOP
1682 IF lpm_rec.top_status = 1 THEN
1683 l_status_code := 'OTA_COMPLETED';
1684 ELSIF lpm_rec.top_status = 2 THEN
1685 l_status_code := 'CANCELLED';
1686 END IF;
1687 EXIT;
1688 END LOOP;
1689 l_learning_path_id := null;
1690 l_learning_path_section_id := null;
1691 l_lp_enrollment_id := null;
1692
1693 if talent_mgmt_rec.end_date = hr_api.g_eot then
1694 l_end_date := null;
1695 else
1696 l_end_date := talent_mgmt_rec.end_date;
1697 end if;
1698 /* For Suitability Matching records, Name of LP is picked from Lookup */
1699 If talent_mgmt_comb.source_function = 'SUITABILITY' then
1700 /* l_name := ota_utility.Get_lookup_meaning
1701 (p_lookup_type => 'OTA_PLAN_COMPONENT_SOURCE',
1702 p_lookup_code => talent_mgmt_comb.source_function,
1703 p_application_id => 810);
1704 */
1705 l_name := 'Suitability Matching Recommended Courses';
1706 Else
1707 l_name := talent_mgmt_rec.name;
1708 l_lang_len := length(talent_mgmt_rec.name || '-' || talent_mgmt_rec.training_plan_member_id) ;
1709 If l_lang_len > MAX_NAME_LEN then
1710 l_lang_len := l_lang_len - MAX_NAME_LEN ;
1711 If l_lang_len > MAX_DATA_TRUNC_LEN then
1712 l_lang_len := MAX_DATA_TRUNC_LEN;
1713 End if;
1714 End if;
1715 if( length(talent_mgmt_rec.name) = 80 OR length(talent_mgmt_rec.name||'-'||talent_mgmt_rec.training_plan_member_id) > 80 ) then
1716 l_name := substrb(talent_mgmt_rec.name,1,80-l_lang_len);
1717 end if;
1718
1719 l_name := substrb(l_name||'-'||talent_mgmt_rec.training_plan_member_id,1,80);
1720 End if;
1721
1722
1723
1724 if talent_mgmt_comb.source_function = 'APPRAISAL' and
1725 talent_mgmt_rec.member_status_type_id = 'OTA_AWAITING_APPROVAL' then
1726 l_status_code := 'AWAITING_APPROVAL';
1727 --l_member_status_code := 'AWAITING_APPROVAL';
1728 l_display_to_learner_flag := 'N';
1729 else
1730 -- l_member_status_code := talent_mgmt_rec.member_status_type_id;
1731 l_display_to_learner_flag := 'Y';
1732 end if;
1733 create_learning_path(p_name => l_name
1734 ,p_business_group_id => talent_mgmt_rec.business_group_id
1735 ,p_start_date_active => talent_mgmt_rec.start_date
1736 ,p_end_date_active => l_end_date
1737 ,p_description => talent_mgmt_rec.description
1738 ,p_path_source_code => 'TALENT_MGMT'
1739 ,p_person_id => talent_mgmt_rec.person_id
1740 ,p_table_name => 'CREATE_LPS_FOR_TPS_TALENT_MGMT'
1741 ,p_upgrade_id => p_update_id
1742 ,p_learning_path_id => l_learning_path_id
1743 ,p_object_version_number => l_object_version_number
1744 ,p_source_function_code => talent_mgmt_comb.source_function
1745 ,p_assignment_id => talent_mgmt_comb.assignment_id
1746 ,p_source_id => talent_mgmt_comb.source_id
1747 ,p_display_to_learner_flag =>l_display_to_learner_flag
1748 ,p_training_plan_id => talent_mgmt_rec.training_plan_id
1749 );
1750
1751 if l_learning_path_id is not null then
1752
1753 create_lp_sections(
1754 p_section_name => l_name
1755 ,p_description => talent_mgmt_rec.description
1756 ,p_section_sequence => 1
1757 ,p_completion_type_code => 'M'
1758 ,p_business_group_id => talent_mgmt_rec.business_group_id
1759 ,p_table_name => 'CREATE_LPC_FOR_TPS_TALENT_MGMT'
1760 ,p_upgrade_id => p_update_id
1761 ,p_learning_path_id => l_learning_path_id
1762 ,p_learning_path_section_id => l_learning_path_section_id
1763 ,p_object_version_number => l_object_version_number);
1764
1765 if l_learning_path_section_id is not null then
1766 create_lp_enrollment(p_name => talent_mgmt_rec.name
1767 ,p_learning_path_id => l_learning_path_id
1768 ,p_Completion_target_date => l_end_date
1769 ,p_Person_id => talent_mgmt_rec.person_id
1770 ,p_path_status_code => l_status_code
1771 ,p_Creator_person_id => talent_mgmt_rec.creator_person_id
1772 ,p_business_group_id => talent_mgmt_rec.business_group_id
1773 ,p_Enrollment_source_code => talent_mgmt_rec.plan_source
1774 ,p_lp_enrollment_id => l_lp_enrollment_id
1775 ,p_object_version_number => l_object_version_number
1776 ,p_table_name => 'CREATE_LPE_FOR_TPS_TALENT_MGMT'
1777 ,p_upgrade_id => p_update_id
1778 ,p_training_plan_id => talent_mgmt_rec.training_plan_id
1779 ,p_source_id => talent_mgmt_comb.source_id
1780 ,p_source_function => talent_mgmt_comb.source_function
1781 ,p_assignment_id => talent_mgmt_comb.assignment_id
1782 ,p_attribute_category => talent_mgmt_rec.tps_attribute_category
1783 ,p_attribute1 => talent_mgmt_rec.tps_attribute1
1784 ,p_attribute2 => talent_mgmt_rec.tps_attribute2
1785 ,p_attribute3 => talent_mgmt_rec.tps_attribute3
1786 ,p_attribute4 => talent_mgmt_rec.tps_attribute4
1787 ,p_attribute5 => talent_mgmt_rec.tps_attribute5
1788 ,p_attribute6 => talent_mgmt_rec.tps_attribute6
1789 ,p_attribute7 => talent_mgmt_rec.tps_attribute7
1790 ,p_attribute8 => talent_mgmt_rec.tps_attribute8
1791 ,p_attribute9 => talent_mgmt_rec.tps_attribute9
1792 ,p_attribute10 => talent_mgmt_rec.tps_attribute10
1793 ,p_attribute11 => talent_mgmt_rec.tps_attribute11
1794 ,p_attribute12 => talent_mgmt_rec.tps_attribute12
1795 ,p_attribute13 => talent_mgmt_rec.tps_attribute13
1796 ,p_attribute14 => talent_mgmt_rec.tps_attribute14
1797 ,p_attribute15 => talent_mgmt_rec.tps_attribute15
1798 ,p_attribute16 => talent_mgmt_rec.tps_attribute16
1799 ,p_attribute17 => talent_mgmt_rec.tps_attribute17
1800 ,p_attribute18 => talent_mgmt_rec.tps_attribute18
1801 ,p_attribute19 => talent_mgmt_rec.tps_attribute19
1802 ,p_attribute20 => talent_mgmt_rec.tps_attribute20
1803 ,p_attribute21 => talent_mgmt_rec.tps_attribute21
1804 ,p_attribute22 => talent_mgmt_rec.tps_attribute22
1805 ,p_attribute23 => talent_mgmt_rec.tps_attribute23
1806 ,p_attribute24 => talent_mgmt_rec.tps_attribute24
1807 ,p_attribute25 => talent_mgmt_rec.tps_attribute25
1808 ,p_attribute26 => talent_mgmt_rec.tps_attribute26
1809 ,p_attribute27 => talent_mgmt_rec.tps_attribute27
1810 ,p_attribute28 => talent_mgmt_rec.tps_attribute28
1811 ,p_attribute29 => talent_mgmt_rec.tps_attribute29
1812 ,p_attribute30 => talent_mgmt_rec.tps_attribute30
1813 );
1814 end if;
1815 end if;
1816
1817 end if;
1818 l_flag := false;
1819 l_learning_path_member_id := null;
1820 l_lp_member_enrollment_id := null;
1821
1822 if l_learning_path_id is not null and l_learning_path_section_id is not null
1823 and l_lp_enrollment_id is not null then
1824
1825 create_learning_path_members(
1826 p_business_group_id => talent_mgmt_rec.business_group_id
1827 ,p_learning_path_id => l_learning_path_id
1828 ,p_activity_version_id => talent_mgmt_rec.activity_version_id
1829 ,p_course_sequence => l_count
1830 ,p_completion_target_date => talent_mgmt_rec.target_completion_date
1831 ,p_learning_path_section_id => l_learning_path_section_id
1832 ,p_learning_path_member_id => l_learning_path_member_id
1833 ,p_object_version_number => l_object_version_number
1834 ,p_table_name => 'CREATE_LPM_FOR_TPM_TALENT_MGMT'
1835 ,p_upgrade_id => p_update_id
1836 ,p_training_plan_member_id => talent_mgmt_rec.training_plan_member_id
1837 ,p_training_plan_id => talent_mgmt_rec.training_plan_id );
1838
1839 if l_learning_path_member_id is not null then
1840 if talent_mgmt_rec.member_status_type_id = 'OTA_AWAITING_APPROVAL'
1841 /* AND talent_mgmt_rec.source_function = 'SUITABILITY' */ then
1842 l_member_status_code := 'OTA_PLANNED';
1843 else
1844 l_member_status_code := talent_mgmt_rec.member_status_type_id;
1845 end if;
1846
1847 create_lp_member_enrollment(
1848 p_lp_member_enrollment_id => l_lp_member_enrollment_id
1849 ,p_lp_enrollment_id => l_lp_enrollment_id
1850 ,p_learning_path_section_id=> l_learning_path_section_id
1851 ,p_learning_path_member_id => l_learning_path_member_id
1852 ,p_member_status_code => l_member_status_code ,p_completion_target_date => talent_mgmt_rec.target_completion_date
1853 -- ,p_completion_date => l_date_status_changed
1854 ,p_creator_person_id => talent_mgmt_rec.creator_person_id -- bug no 3984648
1855 ,p_business_group_id => talent_mgmt_rec.business_group_id
1856 -- ,p_person_id => talent_mgmt_rec.person_id
1857 ,p_activity_version_id => talent_mgmt_rec.activity_version_id
1858 -- ,p_contact_id => talent_mgmt_rec.contact_id
1859 ,p_table_name => 'CREATE_LPME_FOR_TPM_TALENT_MGMT'
1860 ,p_upgrade_id => p_update_id
1861 ,p_object_version_number => l_object_version_number
1862 ,p_training_plan_member_id => talent_mgmt_rec.training_plan_member_id
1863 ,p_attribute_category => talent_mgmt_rec.tpm_attribute_category
1864 ,p_attribute1 => talent_mgmt_rec.tpm_attribute1
1865 ,p_attribute2 => talent_mgmt_rec.tpm_attribute2
1866 ,p_attribute3 => talent_mgmt_rec.tpm_attribute3
1867 ,p_attribute4 => talent_mgmt_rec.tpm_attribute4
1868 ,p_attribute5 => talent_mgmt_rec.tpm_attribute5
1869 ,p_attribute6 => talent_mgmt_rec.tpm_attribute6
1870 ,p_attribute7 => talent_mgmt_rec.tpm_attribute7
1871 ,p_attribute8 => talent_mgmt_rec.tpm_attribute8
1872 ,p_attribute9 => talent_mgmt_rec.tpm_attribute9
1873 ,p_attribute10 => talent_mgmt_rec.tpm_attribute10
1874 ,p_attribute11 => talent_mgmt_rec.tpm_attribute11
1875 ,p_attribute12 => talent_mgmt_rec.tpm_attribute12
1876 ,p_attribute13 => talent_mgmt_rec.tpm_attribute13
1877 ,p_attribute14 => talent_mgmt_rec.tpm_attribute14
1878 ,p_attribute15 => talent_mgmt_rec.tpm_attribute15
1879 ,p_attribute16 => talent_mgmt_rec.tpm_attribute16
1880 ,p_attribute17 => talent_mgmt_rec.tpm_attribute17
1881 ,p_attribute18 => talent_mgmt_rec.tpm_attribute18
1882 ,p_attribute19 => talent_mgmt_rec.tpm_attribute19
1883 ,p_attribute20 => talent_mgmt_rec.tpm_attribute20
1884 ,p_attribute21 => talent_mgmt_rec.tpm_attribute21
1885 ,p_attribute22 => talent_mgmt_rec.tpm_attribute22
1886 ,p_attribute23 => talent_mgmt_rec.tpm_attribute23
1887 ,p_attribute24 => talent_mgmt_rec.tpm_attribute24
1888 ,p_attribute25 => talent_mgmt_rec.tpm_attribute25
1889 ,p_attribute26 => talent_mgmt_rec.tpm_attribute26
1890 ,p_attribute27 => talent_mgmt_rec.tpm_attribute27
1891 ,p_attribute28 => talent_mgmt_rec.tpm_attribute28
1892 ,p_attribute29 => talent_mgmt_rec.tpm_attribute29
1893 ,p_attribute30 => talent_mgmt_rec.tpm_attribute30 );
1894
1895 end if;
1896 end if;
1897
1898
1899 END LOOP;
1900
1901
1902 END LOOP;
1903
1904 Select nvl(count(1),0)
1905 into l_rows_processed
1906 from ota_training_plans
1907 where training_plan_id between p_start_pkid and p_end_pkid;
1908
1909 p_rows_processed := l_rows_processed;
1910 end upg_tp_to_lp_for_talent_mgmt;
1911 -- ----------------------------------------------------------------------------
1912 -- |---------------------------< upg_enrol_to_cat_lp >-----------------------|
1913 -- ----------------------------------------------------------------------------
1914 Procedure upg_enrol_to_cat_lp(
1915 p_process_control IN varchar2,
1916 p_start_pkid IN number,
1917 p_end_pkid IN number,
1918 p_rows_processed OUT nocopy number,
1919 p_update_id in number default 1 --CONC_UPGRADE_ID
1920 ) IS
1921
1922 l_learning_path_id number;
1923 l_target_completion_date date;
1924 l_lp_enrollment_id number;
1925 l_lp_member_enrollment_id number;
1926 l_object_version_number number;
1927 l_completed_comp number;
1928 L_total_comp number;
1929 l_completion_date date;
1930 l_rows_processed number;
1931 l_end_date date;
1932
1933 l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1934 l_old_status ota_lp_member_enrollments.member_status_code%type;
1935 l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1936 l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1937 l_err_code varchar2(72);
1938 l_err_msg varchar2(2000);
1939
1940 CURSOR csr_get_tp IS
1941 SELECT tp.training_plan_id, tp.end_date, tp.person_id,tp.name
1942 , tp.learning_path_id,tp.plan_status_type_id, tp.business_group_id
1943 , tp.plan_source, tp.contact_id
1944 ,tp.ATTRIBUTE_CATEGORY,tp.ATTRIBUTE1
1945 ,tp.ATTRIBUTE2 ,tp.ATTRIBUTE3
1946 ,tp.ATTRIBUTE4 ,tp.ATTRIBUTE5
1947 ,tp.ATTRIBUTE6 ,tp.ATTRIBUTE7
1948 ,tp.ATTRIBUTE8 ,tp.ATTRIBUTE9
1949 ,tp.ATTRIBUTE10 ,tp.ATTRIBUTE11
1950 ,tp.ATTRIBUTE12 ,tp.ATTRIBUTE13
1951 ,tp.ATTRIBUTE14 ,tp.ATTRIBUTE15
1952 ,tp.ATTRIBUTE16 ,tp.ATTRIBUTE17
1953 ,tp.ATTRIBUTE18 ,tp.ATTRIBUTE19
1954 ,tp.ATTRIBUTE20 ,tp.ATTRIBUTE21
1955 ,tp.ATTRIBUTE22 ,tp.ATTRIBUTE23
1956 ,tp.ATTRIBUTE24 ,tp.ATTRIBUTE25
1957 ,tp.ATTRIBUTE26 ,tp.ATTRIBUTE27
1958 ,tp.ATTRIBUTE28 ,tp.ATTRIBUTE29
1959 ,tp.ATTRIBUTE30 ,tp.creator_person_id
1960 FROM ota_training_plans tp
1961 WHERE tp.PLAN_SOURCE in ('CATALOG','MANAGER')
1962 AND tp.learning_path_id is not null
1963 AND (tp.PERSON_ID is not NULL OR tp.contact_id is not null)
1964 AND tp.training_plan_id between p_start_pkid and p_end_pkid;
1965
1966
1967
1968 CURSOR csr_get_tpm(p_training_plan_id NUMBER) IS
1969 SELECT tpm.training_plan_member_id
1970 ,tpm.member_status_type_id
1971 ,tpm.activity_version_id
1972 ,tpm.target_completion_date
1973 ,tpm.business_group_id
1974 ,Lpm.learning_path_member_id
1975 ,Lpm.learning_path_section_id
1976 ,tpm.ATTRIBUTE_CATEGORY ,tpm.ATTRIBUTE1
1977 ,tpm.ATTRIBUTE2 ,tpm.ATTRIBUTE3
1978 ,tpm.ATTRIBUTE4 ,tpm.ATTRIBUTE5
1979 ,tpm.ATTRIBUTE6 ,tpm.ATTRIBUTE7
1980 ,tpm.ATTRIBUTE8 ,tpm.ATTRIBUTE9
1981 ,tpm.ATTRIBUTE10 ,tpm.ATTRIBUTE11
1982 ,tpm.ATTRIBUTE12 ,tpm.ATTRIBUTE13
1983 ,tpm.ATTRIBUTE14 ,tpm.ATTRIBUTE15
1984 ,tpm.ATTRIBUTE16 ,tpm.ATTRIBUTE17
1985 ,tpm.ATTRIBUTE18 ,tpm.ATTRIBUTE19
1986 ,tpm.ATTRIBUTE20 ,tpm.ATTRIBUTE21
1987 ,tpm.ATTRIBUTE22 ,tpm.ATTRIBUTE23
1988 ,tpm.ATTRIBUTE24 ,tpm.ATTRIBUTE25
1989 ,tpm.ATTRIBUTE26 ,tpm.ATTRIBUTE27
1990 ,tpm.ATTRIBUTE28 ,tpm.ATTRIBUTE29
1991 ,tpm.ATTRIBUTE30
1992 FROM ota_training_plan_members tpm,
1993 ota_training_plans tp,
1994 ota_learning_path_members lpm,
1995 ota_learning_paths lps
1996 WHERE tp.training_plan_id = p_training_plan_id
1997 AND lpm.activity_version_id = tpm.activity_version_id
1998 AND lps.path_source_code in ('CATALOG','MANAGER')
1999 AND lps.learning_path_id = lpm.learning_path_id
2000 AND tp.training_plan_id = tpm.training_plan_id
2001 AND tp.learning_path_id = lps.learning_path_id;
2002
2003 BEGIN
2004
2005 FOR tp_rec IN csr_get_tp LOOP
2006 l_lp_enrollment_id := null;
2007 l_max_date_status_changed := null;
2008 --completion_target_date := tp_rec.end_date; (if tp_rec.end_date is 31-Dec-4712 then completion_target_date is set to null)
2009 if tp_rec.end_date = hr_api.g_eot then
2010 l_end_date := null;
2011 else
2012 l_end_date := tp_rec.end_date;
2013 end if;
2014 --Create a record in ota_lp_enrollments
2015
2016 create_lp_enrollment(p_name => tp_rec.name
2017 ,p_learning_path_id => tp_rec.learning_path_id
2018 ,p_Completion_target_date => l_end_date
2019 ,p_Person_id => tp_rec.person_id
2020 ,p_Contact_id => tp_rec.contact_id
2021 ,p_path_status_code => tp_rec.plan_status_type_id
2022 ,p_Creator_person_id => tp_rec.creator_person_id
2023 ,p_business_group_id => tp_rec.business_group_id
2024 ,p_Enrollment_source_code => tp_rec.plan_source
2025 ,p_lp_enrollment_id => l_lp_enrollment_id
2026 ,p_object_version_number => l_object_version_number
2027 ,p_table_name => 'CREATE_LPE_FOR_TPS_ENROL_TO_CAT'
2028 ,p_upgrade_id => p_update_id
2029 ,p_training_plan_id => tp_rec.training_plan_id
2030 ,p_attribute_category => tp_rec.attribute_category
2031 ,p_attribute1 => tp_rec.attribute1
2032 ,p_attribute2 => tp_rec.attribute2
2033 ,p_attribute3 => tp_rec.attribute3
2034 ,p_attribute4 => tp_rec.attribute4
2035 ,p_attribute5 => tp_rec.attribute5
2036 ,p_attribute6 => tp_rec.attribute6
2037 ,p_attribute7 => tp_rec.attribute7
2038 ,p_attribute8 => tp_rec.attribute8
2039 ,p_attribute9 => tp_rec.attribute9
2040 ,p_attribute10 => tp_rec.attribute10
2041 ,p_attribute11 => tp_rec.attribute11
2042 ,p_attribute12 => tp_rec.attribute12
2043 ,p_attribute13 => tp_rec.attribute13
2044 ,p_attribute14 => tp_rec.attribute14
2045 ,p_attribute15 => tp_rec.attribute15
2046 ,p_attribute16 => tp_rec.attribute16
2047 ,p_attribute17 => tp_rec.attribute17
2048 ,p_attribute18 => tp_rec.attribute18
2049 ,p_attribute19 => tp_rec.attribute19
2050 ,p_attribute20 => tp_rec.attribute20
2051 ,p_attribute21 => tp_rec.attribute21
2052 ,p_attribute22 => tp_rec.attribute22
2053 ,p_attribute23 => tp_rec.attribute23
2054 ,p_attribute24 => tp_rec.attribute24
2055 ,p_attribute25 => tp_rec.attribute25
2056 ,p_attribute26 => tp_rec.attribute26
2057 ,p_attribute27 => tp_rec.attribute27
2058 ,p_attribute28 => tp_rec.attribute28
2059 ,p_attribute29 => tp_rec.attribute29
2060 ,p_attribute30 => tp_rec.attribute30);
2061
2062 FOR tpm_rec IN csr_get_tpm(tp_rec.training_plan_id) LOOP
2063 l_lp_member_enrollment_id := null;
2064 if tpm_rec.target_completion_date = hr_api.g_eot then
2065 l_target_completion_date := null;
2066 else
2067 l_target_completion_date := tpm_rec.target_completion_date;
2068 end if;
2069 l_member_status_code := tpm_rec.member_status_type_id;
2070 l_date_status_changed := null;
2071
2072 create_lp_member_enrollment(
2073 p_lp_member_enrollment_id => l_lp_member_enrollment_id
2074 ,p_lp_enrollment_id => l_lp_enrollment_id
2075 ,p_learning_path_section_id => tpm_rec.learning_path_section_id
2076 ,p_learning_path_member_id => tpm_rec.learning_path_member_id
2077 ,p_member_status_code => tpm_rec.member_status_type_id
2078 ,p_completion_target_date => l_target_completion_date
2079 -- ,p_completion_date => l_date_status_changed
2080 ,p_business_group_id => tpm_rec.business_group_id
2081 -- ,p_person_id => tp_rec.person_id
2082 ,p_creator_person_id => tp_rec.creator_person_id -- bug no 3984648
2083 ,p_activity_version_id => tpm_rec.activity_version_id
2084 -- ,p_contact_id => tp_rec.contact_id
2085 ,p_table_name => 'CREATE_LPME_FOR_TPM_ENROL_TO_CAT'
2086 ,p_upgrade_id => p_update_id
2087 ,p_object_version_number => l_object_version_number
2088 ,p_training_plan_member_id => tpm_rec.training_plan_member_id
2089 ,p_attribute_category => tpm_rec.attribute_category
2090 ,p_attribute1 => tp_rec.attribute1
2091 ,p_attribute2 => tpm_rec.attribute2
2092 ,p_attribute3 => tpm_rec.attribute3
2093 ,p_attribute4 => tpm_rec.attribute4
2094 ,p_attribute5 => tpm_rec.attribute5
2095 ,p_attribute6 => tpm_rec.attribute6
2096 ,p_attribute7 => tpm_rec.attribute7
2097 ,p_attribute8 => tpm_rec.attribute8
2098 ,p_attribute9 => tpm_rec.attribute9
2099 ,p_attribute10 => tpm_rec.attribute10
2100 ,p_attribute11 => tpm_rec.attribute11
2101 ,p_attribute12 => tpm_rec.attribute12
2102 ,p_attribute13 => tpm_rec.attribute13
2103 ,p_attribute14 => tpm_rec.attribute14
2104 ,p_attribute15 => tpm_rec.attribute15
2105 ,p_attribute16 => tpm_rec.attribute16
2106 ,p_attribute17 => tpm_rec.attribute17
2107 ,p_attribute18 => tpm_rec.attribute18
2108 ,p_attribute19 => tpm_rec.attribute19
2109 ,p_attribute20 => tpm_rec.attribute20
2110 ,p_attribute21 => tpm_rec.attribute21
2111 ,p_attribute22 => tpm_rec.attribute22
2112 ,p_attribute23 => tpm_rec.attribute23
2113 ,p_attribute24 => tpm_rec.attribute24
2114 ,p_attribute25 => tpm_rec.attribute25
2115 ,p_attribute26 => tpm_rec.attribute26
2116 ,p_attribute27 => tpm_rec.attribute27
2117 ,p_attribute28 => tpm_rec.attribute28
2118 ,p_attribute29 => tpm_rec.attribute29
2119 ,p_attribute30 => tpm_rec.attribute30);
2120
2121
2122
2123 END LOOP;
2124
2125 END LOOP;
2126 Select nvl(count(1),0)
2127 into l_rows_processed
2128 from ota_training_plans
2129 where training_plan_id between p_start_pkid and p_end_pkid;
2130
2131 p_rows_processed := l_rows_processed;
2132 end upg_enrol_to_cat_lp;
2133
2134 -- ----------------------------------------------------------------------------
2135 -- |-----------------------< update_enrollment_status >-----------------------|
2136 -- ----------------------------------------------------------------------------
2137 -- Updating the status of ota_lp_enrollments and ota_lp_member_enrollments table.
2138 /*
2139 PROCEDURE update_enrollment_status is
2140 begin
2141 UPDATE ota_lp_enrollments
2142 SET path_status_code = 'COMPLETED'
2143 where path_status_code = 'OTA_COMPLETED';
2144
2145
2146 UPDATE ota_lp_member_enrollments
2147 SET member_status_code = decode(member_status_code,'OTA_COMPLETED','COMPLETED'
2148 ,'OTA_PLANNED','PLANNED','OTA_AWAITING_APPROVAL','AWAITING_APPROVAL'
2149 ,member_status_code);
2150
2151
2152 end update_enrollment_status;
2153 */
2154 -- ----------------------------------------------------------------------------
2155 -- |----------------------------< is_path_complete >--------------------------|
2156 -- ----------------------------------------------------------------------------
2157
2158 FUNCTION is_path_complete(p_lp_enrollment_id in number) return varchar2
2159 IS
2160 CURSOR csr_member_status is
2161 SELECT decode(member_status_code, 'PLANNED', 0,'ACTIVE', 0, 'AWAITING_APPROVAL',0, 'COMPLETED',1,
2162 'CANCELLED',2) top_status
2163 FROM ota_lp_member_enrollments lpme
2164 WHERE lpme.lp_enrollment_id = p_lp_enrollment_id
2165 ORDER BY top_status;
2166 l_is_complete varchar2(1) := 'F';
2167 begin
2168 FOR lpme_rec IN csr_member_status LOOP
2169 IF lpme_rec.top_status = 1 THEN
2170 l_is_complete := 'S';
2171 END IF;
2172 EXIT;
2173 END LOOP;
2174 return l_is_complete;
2175 end is_path_complete;
2176 -- ----------------------------------------------------------------------------
2177 -- |----------------------------< remove_date_rest >--------------------------|
2178 -- ----------------------------------------------------------------------------
2179 PROCEDURE remove_date_rest (
2180 p_process_control IN varchar2,
2181 p_start_pkid IN number,
2182 p_end_pkid IN number,
2183 p_rows_processed OUT nocopy number,
2184 p_update_id in number default 1
2185 ) IS
2186
2187 l_lp_enrollment_id number;
2188 l_learning_path_id number;
2189 l_tpm_changed boolean;
2190 l_lpm_status_changed boolean;
2191 l_member_status_code ota_lp_member_enrollments.member_status_code%type;
2192 l_old_status ota_lp_member_enrollments.member_status_code%type;
2193 l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
2194 l_date_status_changed ota_delegate_bookings.date_status_changed%type;
2195 l_dummy number;
2196 l_status ota_booking_status_types.type%type;
2197 l_rows_processed number;
2198 l_completion_date date;
2199 l_lpm_enrollment_id number;
2200 l_completed_courses number;
2201 l_flex_val varchar2(2000);
2202 l_lp_object_version_number number;
2203 l_lp_completion_date_old date;
2204 l_err_code varchar2(72);
2205 l_err_msg varchar2(2000);
2206 l_source_function_code ota_learning_paths.source_function_code%type;
2207
2208
2209
2210 CURSOR csr_get_lpe(p_path_status_code IN VARCHAR2) is
2211 SELECT lpe.learning_path_id, lpe.lp_enrollment_id, lpe.person_id, lpe.contact_id
2212 ,lpe.completion_date
2213 ,lpe.object_version_number
2214 FROM ota_lp_enrollments lpe
2215 WHERE lpe.path_status_code = p_path_status_code
2216 and lpe.lp_enrollment_id between p_start_pkid and p_end_pkid;
2217 /*
2218 -- Get all the Learning Path Components that are in Active or Planned Status.
2219 CURSOR csr_get_lpme(p_lp_enrollment_id in number) IS
2220 SELECT lpm.activity_version_id, lpme.lp_member_enrollment_id, lpme.member_status_code,
2221 lpme.object_version_number
2222 FROM ota_lp_member_enrollments lpme,ota_learning_path_members lpm
2223 WHERE lpme.learning_path_member_id = lpm.learning_path_member_id
2224 AND lpme.lp_enrollment_id = p_lp_enrollment_id
2225 -- AND lpm.learning_path_id = p_learning_path_id
2226 AND lpme.member_status_code IN ('PLANNED','ACTIVE');
2227
2228 CURSOR csr_get_status(p_activity_version_id IN NUMBER, p_person_id IN NUMBER
2229 ,p_contact_id in number) IS
2230 SELECT DECODE(bst.type,'C','Z',bst.type) status
2231 FROM ota_events evt,
2232 ota_delegate_bookings tdb,
2233 ota_booking_status_types bst
2234 WHERE evt.activity_version_id=p_activity_version_id
2235 AND evt.event_id = tdb.event_id
2236 AND bst.booking_status_type_id = tdb.booking_status_type_id
2237 AND ((p_person_id is not null and tdb.delegate_person_id = p_person_id )
2238 or (p_contact_id is not null and tdb.delegate_contact_id = p_contact_id))
2239 and rownum=1
2240 ORDER BY status;
2241
2242 */
2243
2244 cursor csr_get_member_status(P_LP_ENROLLMENT_ID IN NUMBER) is
2245 SELECT 1 from ota_lp_member_enrollments lpme
2246 WHERE lpme.member_status_code in ('ACTIVE','PLANNED')
2247 AND lpme.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID;
2248
2249 cursor csr_get_completed_lpms is
2250 SELECT lpme.lp_member_enrollment_id
2251 FROM ota_lp_member_enrollments lpme
2252 WHERE lpme.member_status_code = 'COMPLETED'
2253 AND lpme.LP_ENROLLMENT_ID between p_start_pkid and p_end_pkid;
2254 --
2255 cursor csr_get_lpm_completion_date(p_lpm_enrollment_id IN NUMBER) IS
2256 SELECT trunc(min(nvl(tdb.date_status_changed,tdb.date_booking_placed))) completion_date
2257 FROM ota_delegate_bookings tdb,
2258 ota_lp_member_enrollments lpme,
2259 ota_learning_path_members lpm,
2260 ota_lp_enrollments lpe,
2261 ota_events evt,
2262 ota_booking_status_types bst
2263 WHERE tdb.event_id = evt.event_id
2264 AND evt.activity_version_id = lpm.activity_version_id
2265 AND tdb.booking_status_type_id = bst.booking_status_type_id
2266 AND bst.type = 'A'
2267 AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
2268 AND lpme.learning_path_member_id = lpm.learning_path_member_id
2269 AND ((lpe.person_id IS NOT NULL AND lpe.person_id = tdb.delegate_person_id)
2270 OR (lpe.contact_id IS NOT NULL AND lpe.contact_id = tdb.delegate_contact_id))
2271 AND lpme.lp_member_enrollment_id = p_lpm_enrollment_id;
2272
2273 cursor csr_get_lp_completion_info(p_lp_enrollment_id IN NUMBER) IS
2274 SELECT trunc(max(lpme.completion_date)) completion_date,
2275 count(lpme.lp_member_enrollment_id) completed_courses
2276 FROM ota_lp_member_enrollments lpme
2277 WHERE lpme.member_status_code = 'COMPLETED'
2278 AND lpme.lp_enrollment_id = p_lp_enrollment_id;
2279
2280 Cursor csr_get_attrib_personal_tps is
2281 SELECT 1
2282 FROM ota_training_plans tps
2283 WHERE (tps.PERSON_ID is not NULL OR tps.contact_id is not null)
2284 and (tps.ATTRIBUTE_CATEGORY||tps.ATTRIBUTE1||tps.ATTRIBUTE2||tps.ATTRIBUTE3||tps.ATTRIBUTE4||
2285 tps.ATTRIBUTE5||tps.ATTRIBUTE6||tps.ATTRIBUTE7 ||tps.ATTRIBUTE9||tps.ATTRIBUTE10||tps.ATTRIBUTE11||
2286 tps.ATTRIBUTE12||tps.ATTRIBUTE13||tps.ATTRIBUTE14||tps.ATTRIBUTE15||tps.ATTRIBUTE16 ||
2287 tps.ATTRIBUTE17||tps.ATTRIBUTE18||tps.ATTRIBUTE19||tps.ATTRIBUTE20||tps.ATTRIBUTE21 ||
2288 tps.ATTRIBUTE22||tps.ATTRIBUTE23||tps.ATTRIBUTE24 ||tps.ATTRIBUTE25||tps.ATTRIBUTE26||
2289 tps.ATTRIBUTE27||tps.ATTRIBUTE28||tps.ATTRIBUTE29||tps.ATTRIBUTE30) is not null
2290 AND ROWNUM=1;
2291
2292 Cursor csr_get_attrib_personal_tpm is
2293 SELECT 1
2294 FROM ota_training_plan_members tpm, ota_training_plans tps
2295 WHERE (tps.PERSON_ID is not NULL OR contact_id is not null)
2296 AND (TPM.ATTRIBUTE_CATEGORY||TPM.ATTRIBUTE1||TPM.ATTRIBUTE2||TPM.ATTRIBUTE3||TPM.ATTRIBUTE4||
2297 TPM.ATTRIBUTE5||TPM.ATTRIBUTE6||TPM.ATTRIBUTE7 ||TPM.ATTRIBUTE9||TPM.ATTRIBUTE10||TPM.ATTRIBUTE11||
2298 TPM.ATTRIBUTE12||TPM.ATTRIBUTE13||TPM.ATTRIBUTE14||TPM.ATTRIBUTE15||TPM.ATTRIBUTE16 ||
2299 TPM.ATTRIBUTE17||TPM.ATTRIBUTE18||TPM.ATTRIBUTE19||TPM.ATTRIBUTE20||TPM.ATTRIBUTE21 ||
2300 TPM.ATTRIBUTE22||TPM.ATTRIBUTE23||TPM.ATTRIBUTE24 ||TPM.ATTRIBUTE25||TPM.ATTRIBUTE26||
2301 TPM.ATTRIBUTE27||TPM.ATTRIBUTE28||TPM.ATTRIBUTE29|| TPM.ATTRIBUTE30) IS NOT NULL
2302 AND tps.training_plan_id = tpm.training_plan_id
2303 AND ROWNUM=1;
2304
2305 CURSOR GET_SOURCE_FUNCTION_CODE (P_LP_ENROLLMENT_ID IN VARCHAR2)
2306 IS
2307 SELECT LPS.SOURCE_FUNCTION_CODE
2308 FROM OTA_LEARNING_PATHS LPS, OTA_LP_ENROLLMENTS LPE
2309 WHERE LPE.LEARNING_PATH_ID = LPS.LEARNING_PATH_ID
2310 AND LPE.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID
2311 AND LPS.SOURCE_FUNCTION_CODE = 'SUITABILITY';
2312
2313
2314 BEGIN
2315
2316 /* update the enrollment status of ota_lp_enrollments and ota_lp_member_enrollments */
2317 --update_enrollment_status;
2318
2319 update ota_lp_enrollments lpe
2320 set lpe.no_of_mandatory_courses = (select count(lpme.learning_path_member_id)
2321 from ota_lp_member_enrollments lpme
2322 where lpme.lp_enrollment_id = lpe.lp_enrollment_id);
2323
2324
2325
2326
2327
2328 FOR lpe_rec IN csr_get_lpe('ACTIVE') LOOP
2329 l_lp_enrollment_id:= lpe_rec.lp_enrollment_id;
2330 OPEN GET_SOURCE_FUNCTION_CODE(l_lp_enrollment_id);
2331 FETCH GET_SOURCE_FUNCTION_CODE INTO L_SOURCE_FUNCTION_CODE;
2332 IF GET_SOURCE_FUNCTION_CODE%NOTFOUND THEN
2333 if is_path_complete(p_lp_enrollment_id => l_lp_enrollment_id) = 'S' THEN
2334 UPDATE ota_lp_enrollments
2335 SET path_status_code = 'COMPLETED'
2336 , completion_date = to_date('31/12/4712','DD/MM/RRRR')
2337 WHERE lp_enrollment_id = l_lp_enrollment_id;
2338 end if;
2339 END IF;
2340 CLOSE GET_SOURCE_FUNCTION_CODE;
2341
2342 end loop;
2343
2344 FOR lpm_rec IN csr_get_completed_lpms LOOP
2345 l_lpm_enrollment_id := lpm_rec.lp_member_enrollment_id;
2346 OPEN csr_get_lpm_completion_date(l_lpm_enrollment_id);
2347 FETCH csr_get_lpm_completion_date INTO l_completion_date;
2348 IF csr_get_lpm_completion_date%FOUND THEN
2349 UPDATE ota_lp_member_enrollments
2350 SET completion_date = l_completion_date
2351 WHERE lp_member_enrollment_id = l_lpm_enrollment_id;
2352 END IF;
2353 CLOSE csr_get_lpm_completion_date;
2354 END LOOP;
2355
2356
2357 FOR lpe_rec IN csr_get_lpe('COMPLETED') LOOP
2358 l_lp_enrollment_id := lpe_rec.lp_enrollment_id;
2359 l_lp_completion_date_old := lpe_rec.completion_date;
2360 l_lp_object_version_number := lpe_rec.object_version_number;
2361 OPEN csr_get_lp_completion_info(l_lp_enrollment_id);
2362 FETCH csr_get_lp_completion_info INTO l_completion_date, l_completed_courses;
2363 IF csr_get_lp_completion_info%FOUND THEN
2364 IF to_char(l_lp_completion_date_old,'DD/MM/RRRR') = '31/12/4712' then
2365 begin
2366 ota_lp_enrollment_api.update_lp_enrollment
2367 (p_effective_date => trunc(sysdate)
2368 ,p_lp_enrollment_id => l_lp_enrollment_id
2369 ,p_path_status_code => 'COMPLETED'
2370 ,p_no_of_completed_courses => nvl(l_completed_courses,0)
2371 ,p_completion_date => l_completion_date
2372 ,p_object_version_number => l_lp_object_version_number);
2373
2374 exception
2375 when others then
2376 l_err_code := SQLCODE;
2377 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When updating Learning Path Enrollments ');
2378 --p_course_sequence := p_course_sequence-1;
2379 ota_classic_upgrade.add_log_entry( p_table_name=>'UPDATE_LP_ENROLLMENTS'
2380 ,p_source_primary_key => l_lp_enrollment_id
2381 ,p_business_group_id => l_lp_enrollment_id
2382 ,p_object_value => l_lp_object_version_number
2383 ,p_message_text => l_err_msg
2384 ,p_upgrade_id => p_update_id
2385 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
2386 ,p_log_type => LP_LOG_TYPE_E
2387 ,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
2388
2389
2390
2391 end;
2392
2393 else
2394 UPDATE ota_lp_enrollments
2395 SET completion_date = l_completion_date
2396 ,no_of_completed_courses = nvl(l_completed_courses,0)
2397 WHERE lp_enrollment_id = l_lp_enrollment_id;
2398 end if;
2399 END IF;
2400 CLOSE csr_get_lp_completion_info;
2401 END LOOP;
2402
2403 FOR lpe_rec IN csr_get_lpe('ACTIVE') LOOP
2404 OPEN csr_get_lp_completion_info(lpe_rec.lp_enrollment_id);
2405 FETCH csr_get_lp_completion_info INTO l_completion_date, l_completed_courses;
2406 IF csr_get_lp_completion_info%FOUND THEN
2407 UPDATE ota_lp_enrollments
2408 SET no_of_completed_courses = nvl(l_completed_courses,0)
2409 WHERE lp_enrollment_id = lpe_rec.lp_enrollment_id;
2410 END IF;
2411 CLOSE csr_get_lp_completion_info;
2412 END LOOP;
2413
2414 FOR lpe_rec IN csr_get_lpe('CANCELLED') LOOP
2415 OPEN csr_get_lp_completion_info(lpe_rec.lp_enrollment_id);
2416 FETCH csr_get_lp_completion_info INTO l_completion_date, l_completed_courses;
2417 IF csr_get_lp_completion_info%FOUND THEN
2418 UPDATE ota_lp_enrollments
2419 SET no_of_completed_courses = nvl(l_completed_courses,0)
2420 WHERE lp_enrollment_id = lpe_rec.lp_enrollment_id;
2421 END IF;
2422 CLOSE csr_get_lp_completion_info;
2423 END LOOP;
2424 /* Path status code for Sutibality matching records should be 'ACTIVE' */
2425 UPDATE OTA_LP_ENROLLMENTS
2426 SET PATH_STATUS_CODE = 'ACTIVE',
2427 COMPLETION_DATE = NULL
2428 WHERE LP_ENROLLMENT_ID IN (SELECT LPE.LP_ENROLLMENT_ID
2429 FROM OTA_LP_ENROLLMENTS LPE, OTA_LEARNING_PATHS LPS
2430 WHERE LPE.LEARNING_PATH_ID = LPS.LEARNING_PATH_ID
2431 AND LPS.SOURCE_FUNCTION_CODE = 'SUITABILITY' );
2432
2433
2434
2435 /* Create DFF context for LPE from personal TPS */
2436 Open csr_get_attrib_personal_tps;
2437 Fetch csr_get_attrib_personal_tps into l_flex_val;
2438 if l_flex_val is not null then
2439 migrate_dff_contexts('OTA_TRAINING_PLANS','OTA_LP_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);
2440 end if;
2441 Close csr_get_attrib_personal_tps;
2442 /* Create DFF context for LPME from personal TPM */
2443 Open csr_get_attrib_personal_tpm;
2444 Fetch csr_get_attrib_personal_tpm into l_flex_val;
2445 if l_flex_val is not null then
2446 migrate_dff_contexts('OTA_TRAINING_PLAN_MEMBERS','OTA_LP_MEMBER_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);
2447 end if;
2448 Close csr_get_attrib_personal_tpm;
2449 SELECT nvl(count(1),0)
2450 INTO l_rows_processed
2451 FROM ota_lp_enrollments lpe
2452 WHERE lpe.lp_enrollment_id between p_start_pkid and p_end_pkid;
2453
2454 p_rows_processed := l_rows_processed;
2455
2456 ota_classic_upgrade.add_log_entry( p_table_name=> 'DUMMY'
2457 ,p_source_primary_key => p_update_id
2458 ,p_business_group_id => null
2459 ,p_object_value => null
2460 ,p_message_text => 'Done with LP upgrade'
2461 ,p_upgrade_id => p_update_id
2462 ,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
2463 ,p_log_type => LP_LOG_TYPE_N
2464 ,p_upgrade_name => LP_UPGRADE_NAME );
2465
2466 END REMOVE_DATE_REST;
2467
2468 END OTA_TRAINING_PLAN_UPGRADE;
2469
2470
2471
2472
2473