1 PACKAGE BODY per_mls_migration AS
2 /* $Header: pemlsmig.pkb 115.10 2004/04/29 04:34:34 adudekul noship $ */
3 --
4 -- Fix for bug 3481355 starts here. Commented out the JOB procedure.
5 --
6 -- ----------------------------------------------------------------------------
7 -- |--------------------------< migrateJobData >----------------------------|
8 -- ----------------------------------------------------------------------------
9 --
10 -- Fix for the bug 3481355 ends here.
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< migratePositionData >-------------------------|
14 -- ----------------------------------------------------------------------------
15 procedure migratePositionData(
16 p_process_ctrl IN varchar2,
17 p_start_pkid IN number,
18 p_end_pkid IN number,
19 p_rows_processed OUT nocopy number)
20 is
21
22 cursor csr_installed_languages is
23 select language_code,
24 nls_Language
25 from fnd_languages
26 where installed_flag in ('I', 'B');
27
28 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
29 l_current_nls_language VARCHAR2(30);
30 l_current_language VARCHAR2(4);
31 l_rows_processed number := 0;
32 l_end_of_time date := hr_general.end_of_time;
33
34 begin
35
36 /*
37 ** Clear out any existing data for this range of records
38 **
39 ** Don't delete just yet.
40 **
41 delete from hr_all_positions_f_tl
42 where position_id between p_start_pkid
43 and p_end_pkid;
44 */
45
46 /*
47 **
48 ** Note the issue found by Jon and Phil whereby a validation/generation
49 ** failure in the derivation of the string due to unresolvable references to
50 ** profile options is not an error but a signaled by a null string. If this
51 ** happens use the name from the base table.
52 **
53 ** For each installed language insert a new record into the TL table for
54 ** each record in the range provided that is present in the base table.
55 */
56 for c_language in csr_installed_languages loop
57
58 /*
59 ** Set language for iteration....
60 */
61 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
62 l_current_language := c_language.language_code;
63
64 /*
65 ** Insert the TL rows.
66 */
67 insert into hr_all_positions_f_tl (
68 position_id,
69 language,
70 source_lang,
71 name,
72 created_by,
73 creation_date,
74 last_updated_by,
75 last_update_date,
76 last_update_login )
77 select j.position_id,
78 l_current_language,
79 l_userenv_language_code,
80 nvl(fnd_flex_ext.get_segs('PER', 'POS',
81 jd.id_flex_num,
82 jd.position_definition_id),
83 j.name),
84 j.created_by,
85 j.creation_date,
86 j.last_updated_by,
87 j.last_update_date,
88 j.last_update_login
89 from hr_all_positions_f j,
90 per_position_definitions jd
91 where j.position_definition_id = jd.position_definition_id
92 and j.position_id between p_start_pkid
93 and p_end_pkid
94 -- Fix for bug 3359423 starts here. check for the max EED and not the EOT.
95 -- and j.effective_end_date = l_end_of_time
96 and j.effective_end_date = (select max(effective_end_date)
97 from hr_all_positions_f pos
98 where pos.position_id = j.position_id)
99 -- Fix for bug 3359423 ends here.
100 and not exists (select '1'
101 from hr_all_positions_f_tl jtl
102 where jtl.position_id = j.position_id
103 and jtl.language = l_current_language);
104
105 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
106
107 end loop;
108
109 p_rows_processed := l_rows_processed;
110
111 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
112
113 Exception
114 --
115 When Others Then
116 --
117 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
118 --
119 raise;
120
121 end;
122
123 -- ----------------------------------------------------------------------------
124 -- |---------------------------< migrateGradeData >---------------------------|
125 -- ----------------------------------------------------------------------------
126 procedure migrateGradeData(
127 p_process_ctrl IN varchar2,
128 p_start_pkid IN number,
129 p_end_pkid IN number,
130 p_rows_processed OUT nocopy number)
131 is
132
133 cursor csr_installed_languages is
134 select language_code,
135 nls_Language
136 from fnd_languages
137 where installed_flag in ('I', 'B');
138
139 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
140 l_current_nls_language VARCHAR2(30);
141 l_current_language VARCHAR2(4);
142 l_rows_processed number := 0;
143 l_end_of_time date := hr_general.end_of_time;
144
145 begin
146
147 /*
148 ** Clear out any existing data for this range of records
149 **
150 ** Don't delete just yet.
151 **
152 delete from per_grades_tl
153 where grade_id between p_start_pkid
154 and p_end_pkid;
155 */
156
157 /*
158 **
159 ** Note the issue found by Jon and Phil whereby a validation/generation
160 ** failure in the derivation of the string due to unresolvable references to
161 ** profile options is not an error but a signaled by a null string. If this
162 ** happens use the name from the base table.
163 **
164 ** For each installed language insert a new record into the TL table for
165 ** each record in the range provided that is present in the base table.
166 */
167 for c_language in csr_installed_languages loop
168
169 /*
170 ** Set language for iteration....
171 */
172 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
173 l_current_language := c_language.language_code;
174
175 /*
176 ** Insert the TL rows.
177 */
178 insert into per_grades_tl(
179 grade_id,
180 language,
181 source_lang,
182 name,
183 created_by,
184 creation_date,
185 last_updated_by,
186 last_update_date,
187 last_update_login)
188 select g.grade_id,
189 l_current_language,
190 l_userenv_language_code,
191 nvl(fnd_flex_ext.get_segs('PER', 'GRD',
192 gd.id_flex_num,
193 gd.grade_definition_id),
194 g.name),
195 g.created_by,
196 g.creation_date,
197 g.last_updated_by,
198 g.last_update_date,
199 g.last_update_login
200 from per_grades g,
201 per_grade_definitions gd
202 where g.grade_definition_id = gd.grade_definition_id
203 and g.grade_id between p_start_pkid
204 and p_end_pkid
205 and not exists (select '1'
206 from per_grades_tl gtl
207 where gtl.grade_id = g.grade_id
208 and gtl.language = l_current_language);
209
210 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
211
212 end loop;
213
214 p_rows_processed := l_rows_processed;
215
216 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
217
218 Exception
219 --
220 When Others Then
221 --
222 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
223 --
224 raise;
225
226 end;
227
228 -- ----------------------------------------------------------------------------
229 -- |---------------------------< migrateRatingScaleData >---------------------------|
230 -- ----------------------------------------------------------------------------
231 procedure migrateRatingScaleData(
232 p_process_ctrl IN varchar2,
233 p_start_pkid IN number,
234 p_end_pkid IN number,
235 p_rows_processed OUT nocopy number)
236 is
237
238 cursor csr_installed_languages is
239 select language_code,
240 nls_Language
241 from fnd_languages
242 where installed_flag in ('I', 'B');
243
244 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
245 l_current_nls_language VARCHAR2(30);
246 l_current_language VARCHAR2(4);
247 l_rows_processed number := 0;
248 l_end_of_time date := hr_general.end_of_time;
249
250 begin
251
252 /*
253 ** Clear out any existing data for this range of records
254 **
255 ** Don't delete just yet.
256 **
257 delete from per_rating_scales_tl
258 where rating_scale_id between p_start_pkid
259 and p_end_pkid;
260 */
261
262 /*
263 **
264 ** For each installed language insert a new record into the TL table for
265 ** each record in the range provided that is present in the base table.
266 */
267 for c_language in csr_installed_languages loop
268
269 /*
270 ** Set language for iteration....
271 */
272 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
273 l_current_language := c_language.language_code;
274
275 /*
276 ** Insert the TL rows.
277 */
278 insert into per_rating_scales_tl(
279 rating_scale_id,
280 language,
281 source_lang,
282 name,
283 description,
284 created_by,
285 creation_date,
286 last_updated_by,
287 last_update_date,
288 last_update_login)
289 select r.rating_scale_id,
290 l_current_language,
291 l_userenv_language_code,
292 r.name,
293 r.description,
294 r.created_by,
295 r.creation_date,
296 r.last_updated_by,
297 r.last_update_date,
298 r.last_update_login
299 from per_rating_scales r
300 where r.rating_scale_id between p_start_pkid
301 and p_end_pkid
302 and not exists (select '1'
303 from per_rating_scales_tl rtl
304 where rtl.rating_scale_id = r.rating_scale_id
305 and rtl.language = l_current_language);
306
307 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
308
309 end loop;
310
311 p_rows_processed := l_rows_processed;
312
313 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
314
315 Exception
316 --
317 When Others Then
318 --
319 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
320 --
321 raise;
322
323 end;
324
325 -- ----------------------------------------------------------------------------
326 -- |---------------------------< migrateRatingLevelData >---------------------|
327 -- ----------------------------------------------------------------------------
328 procedure migrateRatingLevelData(
329 p_process_ctrl IN varchar2,
330 p_start_pkid IN number,
331 p_end_pkid IN number,
332 p_rows_processed OUT nocopy number)
333 is
334
335 cursor csr_installed_languages is
336 select language_code,
337 nls_Language
338 from fnd_languages
339 where installed_flag in ('I', 'B');
340
341 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
342 l_current_nls_language VARCHAR2(30);
343 l_current_language VARCHAR2(4);
344 l_rows_processed number := 0;
345 l_end_of_time date := hr_general.end_of_time;
346
347 begin
348
349 /*
350 ** Clear out any existing data for this range of records
351 **
352 ** Don't delete just yet.
353 **
354 delete from per_rating_levels_tl
355 where rating_level_id between p_start_pkid
356 and p_end_pkid;
357 */
358
359 /*
360 **
361 ** For each installed language insert a new record into the TL table for
362 ** each record in the range provided that is present in the base table.
363 */
364 for c_language in csr_installed_languages loop
365
366 /*
367 ** Set language for iteration....
368 */
369 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
370 l_current_language := c_language.language_code;
371
372 /*
373 ** Insert the TL rows.
374 */
375 insert into per_rating_levels_tl(
376 rating_level_id,
377 language,
378 source_lang,
379 name,
380 behavioural_indicator,
381 created_by,
382 creation_date,
383 last_updated_by,
384 last_update_date,
385 last_update_login)
386 select r.rating_level_id,
387 l_current_language,
388 l_userenv_language_code,
389 r.name,
390 r.behavioural_indicator,
391 r.created_by,
392 r.creation_date,
393 r.last_updated_by,
394 r.last_update_date,
395 r.last_update_login
396 from per_rating_levels r
397 where r.rating_level_id between p_start_pkid
398 and p_end_pkid
399 and not exists (select '1'
400 from per_rating_levels_tl rtl
401 where rtl.rating_level_id = r.rating_level_id
402 and rtl.language = l_current_language);
403
404 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
405
406 end loop;
407
408 p_rows_processed := l_rows_processed;
409
410 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
411
412 Exception
413 --
414 When Others Then
415 --
416 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
417 --
418 raise;
419
420 end;
421
422 -- ----------------------------------------------------------------------------
423 -- |-------------------------< migrateCompetenceData >------------------------|
424 -- ----------------------------------------------------------------------------
425 procedure migrateCompetenceData(
426 p_process_ctrl IN varchar2,
427 p_start_pkid IN number,
428 p_end_pkid IN number,
429 p_rows_processed OUT nocopy number)
430 is
431
432 cursor csr_installed_languages is
433 select language_code,
434 nls_Language
435 from fnd_languages
436 where installed_flag in ('I', 'B');
437
438 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
439 l_current_nls_language VARCHAR2(30);
440 l_current_language VARCHAR2(4);
441 l_rows_processed number := 0;
442 l_end_of_time date := hr_general.end_of_time;
443
444 begin
445
446 /*
447 ** Clear out any existing data for this range of records
448 **
449 ** Don't delete just yet.
450 **
451 delete from per_competences_tl
452 where competence_id between p_start_pkid
453 and p_end_pkid;
454 */
455
456 /*
457 **
458 ** Note the issue found by Jon and Phil whereby a validation/generation
459 ** failure in the derivation of the string due to unresolvable references to
460 ** profile options is not an error but a signaled by a null string. If this
461 ** happens use the name from the base table.
462 **
463 ** For each installed language insert a new record into the TL table for
464 ** each record in the range provided that is present in the base table.
465 */
466 for c_language in csr_installed_languages loop
467
468 /*
469 ** Set language for iteration....
470 */
471 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
472 l_current_language := c_language.language_code;
473
474 /*
475 ** Insert the TL rows.
476 */
477 insert into per_competences_tl(
478 competence_id,
479 language,
480 source_lang,
481 name,
482 competence_alias,
483 behavioural_indicator,
484 description,
485 created_by,
486 creation_date,
487 last_updated_by,
488 last_update_date,
489 last_update_login)
490 select c.competence_id,
491 l_current_language,
492 l_userenv_language_code,
493 nvl(fnd_flex_ext.get_segs('PER', 'CMP',
494 cd.id_flex_num,
495 cd.competence_definition_id),
496 c.name),
497 c.competence_alias,
498 c.behavioural_indicator,
499 c.description,
500 c.created_by,
501 c.creation_date,
502 c.last_updated_by,
503 c.last_update_date,
504 c.last_update_login
505 from per_competences c,
506 per_competence_definitions cd
507 where c.competence_definition_id = cd.competence_definition_id
508 and c.competence_id between p_start_pkid
509 and p_end_pkid
510 and not exists (select '1'
511 from per_competences_tl ctl
512 where ctl.competence_id = c.competence_id
513 and ctl.language = l_current_language);
514
515 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
516
517 end loop;
518
519 p_rows_processed := l_rows_processed;
520
521 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
522
523 Exception
524 --
525 When Others Then
526 --
527 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
528 --
529 raise;
530
531 end;
532
533 -- ----------------------------------------------------------------------------
534 -- |-----------------------< migrateQualificationData >-----------------------|
535 -- ----------------------------------------------------------------------------
536 procedure migrateQualificationData(
537 p_process_ctrl IN varchar2,
538 p_start_pkid IN number,
539 p_end_pkid IN number,
540 p_rows_processed OUT nocopy number)
541 is
542
543 cursor csr_installed_languages is
544 select language_code,
545 nls_Language
546 from fnd_languages
547 where installed_flag in ('I', 'B');
548
549 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
550 l_current_nls_language VARCHAR2(30);
551 l_current_language VARCHAR2(4);
552 l_rows_processed number := 0;
553 l_end_of_time date := hr_general.end_of_time;
554
555 begin
556
557 /*
558 ** Clear out any existing data for this range of records
559 **
560 ** Don't delete just yet.
561 **
562 delete from per_qualifications_tl
563 where qualification_id between p_start_pkid
564 and p_end_pkid;
565 */
566
567 /*
568 **
569 ** For each installed language insert a new record into the TL table for
570 ** each record in the range provided that is present in the base table.
571 */
572 for c_language in csr_installed_languages loop
573
574 /*
575 ** Set language for iteration....
576 */
577 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
578 l_current_language := c_language.language_code;
579
580 /*
581 ** Insert the TL rows.
582 */
583 insert into per_qualifications_tl(
584 qualification_id,
585 language,
586 source_lang,
587 title,
588 group_ranking,
589 license_restrictions,
590 awarding_body,
591 grade_attained,
592 reimbursement_arrangements,
593 training_completed_units,
594 membership_category,
595 created_by,
596 creation_date,
597 last_updated_by,
598 last_update_date,
599 last_update_login)
600 select q.qualification_id,
601 l_current_language,
602 l_userenv_language_code,
603 q.title,
604 q.group_ranking,
605 q.license_restrictions,
606 q.awarding_body,
607 q.grade_attained,
608 q.reimbursement_arrangements,
609 q.training_completed_units,
610 q.membership_category,
611 q.created_by,
612 q.creation_date,
613 q.last_updated_by,
614 q.last_update_date,
615 q.last_update_login
616 from per_qualifications q
617 where q.qualification_id between p_start_pkid
618 and p_end_pkid
619 and not exists (select '1'
620 from per_qualifications_tl qtl
621 where qtl.qualification_id = q.qualification_id
622 and qtl.language = l_current_language);
623
624 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
625
626 end loop;
627
628 p_rows_processed := l_rows_processed;
629
630 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
631
632 Exception
633 --
634 When Others Then
635 --
636 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
637 --
638 raise;
639
640 end;
641
642 -- ----------------------------------------------------------------------------
643 -- |----------------------< migrateSubjectsTakenData >------------------------|
644 -- ----------------------------------------------------------------------------
645 procedure migrateSubjectsTakenData(
646 p_process_ctrl IN varchar2,
647 p_start_pkid IN number,
648 p_end_pkid IN number,
649 p_rows_processed OUT nocopy number)
650 is
651
652 cursor csr_installed_languages is
653 select language_code,
654 nls_Language
655 from fnd_languages
656 where installed_flag in ('I', 'B');
657
658 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
659 l_current_nls_language VARCHAR2(30);
660 l_current_language VARCHAR2(4);
661 l_rows_processed number := 0;
662 l_end_of_time date := hr_general.end_of_time;
663
664 begin
665
666 /*
667 ** Clear out any existing data for this range of records
668 **
669 ** Don't delete just yet.
670 **
671 delete from per_subjects_taken_tl
672 where subjects_taken_id between p_start_pkid
673 and p_end_pkid;
674 */
675
676 /*
677 **
678 ** For each installed language insert a new record into the TL table for
679 ** each record in the range provided that is present in the base table.
680 */
681 for c_language in csr_installed_languages loop
682
683 /*
684 ** Set language for iteration....
685 */
686 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
687 l_current_language := c_language.language_code;
688
689 /*
690 ** Insert the TL rows.
691 */
692 insert into per_subjects_taken_tl(
693 subjects_taken_id,
694 language,
695 source_lang,
696 grade_attained,
697 created_by,
698 creation_date,
699 last_updated_by,
700 last_update_date,
701 last_update_login)
702 select s.subjects_taken_id,
703 l_current_language,
704 l_userenv_language_code,
705 s.grade_attained,
706 s.created_by,
707 s.creation_date,
708 s.last_updated_by,
709 s.last_update_date,
710 s.last_update_login
711 from per_subjects_taken s
712 where s.subjects_taken_id between p_start_pkid
713 and p_end_pkid
714 and not exists (select '1'
715 from per_subjects_taken_tl stl
716 where stl.subjects_taken_id = s.subjects_taken_id
717 and stl.language = l_current_language);
718
719 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
720
721 end loop;
722
723 p_rows_processed := l_rows_processed;
724
725 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
726
727 Exception
728 --
729 When Others Then
730 --
731 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
732 --
733 raise;
734
735 end;
736
740 procedure migrateQualificationTypeData(
737 -- ----------------------------------------------------------------------------
738 -- |---------------------< migrateQualificationTypeData >---------------------|
739 -- ----------------------------------------------------------------------------
741 p_process_ctrl IN varchar2,
742 p_start_pkid IN number,
743 p_end_pkid IN number,
744 p_rows_processed OUT nocopy number)
745 is
746
747 cursor csr_installed_languages is
748 select language_code,
749 nls_Language
750 from fnd_languages
751 where installed_flag in ('I', 'B');
752
753 l_userenv_language_code VARCHAR2(4) := userenv('LANG');
754 l_current_nls_language VARCHAR2(30);
755 l_current_language VARCHAR2(4);
756 l_rows_processed number := 0;
757 l_end_of_time date := hr_general.end_of_time;
758
759 begin
760
761 /*
762 ** Clear out any existing data for this range of records
763 **
764 ** Don't delete just yet.
765 **
766 delete from per_qualification_types_tl
767 where qualification_type_id between p_start_pkid
768 and p_end_pkid;
769 */
770
771 /*
772 **
773 ** For each installed language insert a new record into the TL table for
774 ** each record in the range provided that is present in the base table.
775 */
776 for c_language in csr_installed_languages loop
777
778 /*
779 ** Set language for iteration....
780 */
781 hr_kflex_utility.set_session_nls_language(c_language.nls_language);
782 l_current_language := c_language.language_code;
783
784 /*
785 ** Insert the TL rows.
786 */
787 insert into per_qualification_types_tl(
788 qualification_type_id,
789 language,
790 source_lang,
791 name,
792 created_by,
793 creation_date,
794 last_updated_by,
795 last_update_date,
796 last_update_login)
797 select q.qualification_type_id,
798 l_current_language,
799 l_userenv_language_code,
800 q.name,
801 q.created_by,
802 q.creation_date,
803 q.last_updated_by,
804 q.last_update_date,
805 q.last_update_login
806 from per_qualification_types q
807 where q.qualification_type_id between p_start_pkid
808 and p_end_pkid
809 and not exists (select '1'
810 from per_qualification_types_tl qtl
811 where qtl.qualification_type_id = q.qualification_type_id
812 and qtl.language = l_current_language);
813
814 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
815
816 end loop;
817
818 p_rows_processed := l_rows_processed;
819
820 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
821
822 Exception
823 --
824 When Others Then
825 --
826 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
827 --
828 raise;
829
830 end;
831
832 end per_mls_migration;
[Home] [Help]