1 PACKAGE BODY per_succ_mgmt_migration_pkg AS
2 /* $Header: pesucmgr.pkb 120.1 2010/05/27 18:28:23 sidsaxen noship $*/
3 PROCEDURE check_migration_required(p_business_group_id IN NUMBER, retcode IN OUT NOCOPY NUMBER) IS
4 CURSOR csr_chk_eit (p_bg_id NUMBER) IS
5 SELECT 'Y'
6 FROM dual
7 WHERE EXISTS (SELECT 'x'
8 FROM per_people_extra_info pei,
9 per_all_people_f ppf
10 WHERE ppf.business_group_id = p_bg_id
11 AND ppf.person_id = pei.person_id
12 AND pei.information_type = 'PER_SUCCESSION_PLANNING');
13 l_data_exist VARCHAR2(10);
14 BEGIN
15 IF NVL(fnd_profile.value('HR_SUCCESSION_MGMT_LICENSED'),'N') = 'N' THEN
16 fnd_file.put_line(fnd_file.log,'Oracle Succession Management is not licensed. No need of running the migration program. Exiting.');
17 retcode := 2;
18 END IF;
19 OPEN csr_chk_eit(p_business_group_id);
20 FETCH csr_chk_eit INTO l_data_exist;
21 IF csr_chk_eit%FOUND THEN
22 fnd_file.put_line(fnd_file.log,'Data Exist in EIT PER_SUCCESSION_PLANNING. Continue with migration');
23 ELSE
24 fnd_file.put_line(fnd_file.log,'No data is entered in the EIT PER_SUCCESSION_PLANNING. Nothing to migrate.Exiting.');
25 retcode := 1;
26 END IF;
27 CLOSE csr_chk_eit;
28 END check_migration_required;
29 --
30 --
31 PROCEDURE check_lookup_mappings(p_business_group_id IN NUMBER,retcode IN OUT NOCOPY NUMBER) IS
32 CURSOR csr_suc_potential(p_bg_id IN NUMBER) IS
33 SELECT rpad(lookup_code,30) LOOKUP_CODE, meaning
34 FROM hr_lookups h
35 WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
36 AND lookup_code IN (SELECT distinct pei_information1
37 FROM per_people_extra_info pei,
38 per_all_people_f ppf
39 WHERE pei.information_type = 'PER_SUCCESSION_PLANNING'
40 AND pei.person_id = ppf.person_id
41 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
42 AND ppf.business_group_id = p_bg_id)
43 AND lookup_code NOT IN (SELECT lookup_code
44 FROM hr_lookups
45 WHERE lookup_type = 'READINESS_LEVEL'
46 AND enabled_flag = 'Y'
47 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND NVL(end_date_active,trunc(sysdate)) );
48 CURSOR csr_risk_of_loss(p_bg_id IN NUMBER) IS
49 SELECT rpad(lookup_code,30) LOOKUP_CODE, meaning
50 FROM hr_lookups h
51 WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
52 AND lookup_code IN (SELECT distinct pei_information2
53 FROM per_people_extra_info pei,
54 per_all_people_f ppf
55 WHERE pei.information_type = 'PER_SUCCESSION_PLANNING'
56 AND pei.person_id = ppf.person_id
57 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
58 AND ppf.business_group_id = p_bg_id)
59 AND lookup_code NOT IN (SELECT lookup_code
60 FROM hr_lookups
61 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
62 AND enabled_flag = 'Y'
63 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND NVL(end_date_active,trunc(sysdate)) );
64 counter NUMBER;
65 BEGIN
66 counter := 0;
67 retcode := 0;
68 FOR i IN csr_suc_potential(p_business_group_id)
69 LOOP
70 retcode := 1;
71 IF counter = 0 THEN
72 fnd_file.put_line(fnd_file.log,'Lookup codes not mapped from PER_SUCC_PLAN_POTENTIAL to READINESS_LEVEL are listed below.');
73 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------------------');
74 END IF;
75 fnd_file.put_line(fnd_file.log, i.lookup_code||'-'||i.meaning);
76 counter := counter +1;
77 END LOOP;
78 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------------------');
79 counter := 0;
80 FOR i IN csr_risk_of_loss(p_business_group_id)
81 LOOP
82 retcode := 1;
83 IF counter = 0 THEN
84 fnd_file.put_line(fnd_file.log,'Lookup codes not mapped from PER_SUCC_PLAN_RISK_LEVEL to PER_RETENTION_POTENTIAL are listed below.');
85 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------------------');
86 END IF;
87 fnd_file.put_line(fnd_file.log, i.lookup_code||'-'||i.meaning);
88 counter := counter +1;
89 END LOOP;
90 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------------------');
91 END check_lookup_mappings;
92 --
93 --
94 PROCEDURE migrate_lookup_data(p_business_group_id IN NUMBER) IS
95 CURSOR csr_old_eit(p_bg_id IN NUMBER) IS
96 SELECT pei.person_id
97 ,pei.person_extra_info_id
98 ,pei_information3 --- key person
99 ,pei_information1 --- potential
100 ,pei_information2 --- risk of loss
101 FROM per_people_extra_info pei
102 ,per_all_people_f ppf
103 WHERE ppf.business_group_id = p_bg_id
104 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
105 AND ppf.person_id = pei.person_id
106 AND pei.information_type = 'PER_SUCCESSION_PLANNING'
107 AND NOT EXISTS (SELECT 'x'
108 FROM per_people_extra_info new
109 WHERE new.information_type = 'PER_SUCCESSION_MGMT_INFO'
110 AND new.person_id = pei.person_id
111 AND new.pei_information7 = (-1*pei.person_extra_info_id));
112 l_ovn NUMBER;
113 l_person_extra_info_id NUMBER;
114 BEGIN
115 FOR i IN csr_old_eit(p_business_group_id)
116 LOOP
117 l_person_extra_info_id := NULL;
118 l_ovn := NULL;
119 hr_person_extra_info_api.create_person_extra_info
120 (p_person_id => i.person_id
121 ,p_information_type => 'PER_SUCCESSION_MGMT_INFO'
122 ,p_pei_information_category => 'PER_SUCCESSION_MGMT_INFO'
123 ,p_pei_information1 => i.pei_information1
124 ,p_pei_information4 => i.pei_information2
125 ,p_pei_information3 => i.pei_information3
126 ,p_pei_information7 => (-1*i.person_extra_info_id)
127 ,p_person_extra_info_id => l_person_extra_info_id
128 ,p_object_version_number => l_ovn);
129 END LOOP;
130 EXCEPTION WHEN OTHERS THEN
131 fnd_file.put_line(fnd_file.log,'Error while migrating the data to new EIT Structure');
132 rollback;
133 RAISE;
134 END migrate_lookup_data;
135 --
136 PROCEDURE migrate_succ_plan_eit(errbuf out nocopy varchar2
137 ,retcode out nocopy number
138 ,p_business_group_id IN NUMBER ) IS
139 BEGIN
140 retcode := 0;
141 check_migration_required(p_business_group_id,retcode);
142 IF retcode = 0 THEN
143 check_lookup_mappings(p_business_group_id,retcode);
144 END IF;
145 IF retcode = 0 THEN
146 migrate_lookup_data(p_business_group_id);
147 END IF;
148 COMMIT;
149 EXCEPTION
150 WHEN OTHERS THEN
151 Rollback;
152 retcode := 2;
153 fnd_file.put_line(fnd_file.log,'Error while completing the migation of EIT Data for Succession Management');
154 fnd_file.put_line(fnd_file.log,'ERROR: '||SQLERRM);
155 END migrate_succ_plan_eit;
156 END per_succ_mgmt_migration_pkg;
[Home] [Help]