1 PACKAGE BODY PER_FR_DISABILITY_UPG_PKG AS
2 /* $Header: pefrdiup.pkb 115.1 2002/07/03 06:47:11 pvaish noship $ */
3
4 CURSOR csr_disabled_entries(p_business_group_id number)
5 IS
6 SELECT pei.PERSON_ID,
7 per1.full_name,
8 per1.employee_number,
9 pei.PEI_INFORMATION1,
10 pei.PEI_INFORMATION2,
11 pei.PEI_INFORMATION3,
12 pei.PEI_INFORMATION4,
13 pei.PEI_INFORMATION5,
14 pei.PEI_INFORMATION6,
15 pei.PEI_INFORMATION7,
16 pei.PEI_INFORMATION8,
17 pei.PEI_INFORMATION9,
18 pei.PERSON_EXTRA_INFO_ID
19 FROM per_people_extra_info pei
20 , per_all_people_f per1
21 WHERE pei.PEI_INFORMATION_CATEGORY = 'FR_DISABILITY'
22 AND pei.person_id = per1.person_id
23 AND per1.business_group_id = p_business_group_id
24 AND per1.effective_start_date = (select max(per2.effective_start_date)
25 from per_all_people_f per2
26 where per2.person_id = per1.person_id
27 and per2.business_group_id = p_business_group_id)
28 AND pei.PEI_INFORMATION30 IS NULL
29 order by per1.full_name;
30
31 g_package varchar2(30) := 'per_fr_disability_upg_pkg';
32
33 /********************************************************************************
34 * Procedure that writes out the whole disability information to the log *
35 * this allows users to mannually enter this information where it could not be *
36 * created by the process *
37 ********************************************************************************/
38 procedure write_disability_to_log(p_disability in csr_disabled_entries%ROWTYPE)
39 IS
40 BEGIN
41 /*Added script to print all Disability related information into Log*/
42 per_fr_upgrade_data_pkg.write_log(p_disability.employee_number);
43 per_fr_upgrade_data_pkg.write_log(p_disability.full_name);
44 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION1);
45 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION2);
46 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION3);
47 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION4);
48 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION5);
49 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION6);
50 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION7);
51 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION8);
52 per_fr_upgrade_data_pkg.write_log(p_disability.PEI_INFORMATION9);
53
54 END write_disability_to_log;
55
56 /***********************************************************************
57 * function TRANSFER_DATA *
58 * This fucntion must be called from run_upgrade *
59 * Return = 0 means upgrade completed OK. *
60 * Return = 1 means warnings *
61 * Return = 2 means upgrade failed *
62 ***********************************************************************/
63 function transfer_data(p_business_group_id IN NUMBER) return number
64 IS
65 l_disabled csr_disabled_entries%ROWTYPE;
66 l_person_start_date date;
67 l_person_end_date date;
68 l_disability_id number;
69 l_object_version_number number;
70 l_reason varchar2(30);
71 l_effective_start_date date;
72 l_effective_end_date date;
73 l_disabilities_start_date date;
74 l_disabilities_end_date date;
75 l_category varchar2(30);
76 l_run_status number :=0; /* Status of the whole run */
77 l_record_status number;
78 l_proc varchar2(72) := g_package||'.transfer_data';
79 --
80 begin
81 --
82 hr_utility.set_location('Entered '||l_proc,5);
83
84 OPEN csr_disabled_entries(p_business_group_id);
85 FETCH csr_disabled_entries INTO l_disabled;
86
87 WHILE csr_disabled_entries%FOUND LOOP
88
89 l_record_status :=0;
90
91 SELECT MIN(EFFECTIVE_START_DATE), MAX(effective_end_date)
92 INTO l_person_start_date, l_person_end_date
93 FROM per_all_people_f
94 WHERE person_id = l_disabled.person_id;
95
96 /* Determine Start date for disability record */
97 IF l_disabled.PEI_INFORMATION6 IS NULL THEN
98 -- Disability Start Date is NULL. Use person start date
99 l_disabilities_start_date := l_person_start_date;
100 ELSIF FND_date.canonical_to_date(l_disabled.PEI_INFORMATION6) < l_person_start_date THEN
101 -- Disability started prior to employment. Therefore use person start date.
102 l_disabilities_start_date := l_person_start_date;
103 ELSIF FND_date.canonical_to_date(l_disabled.PEI_INFORMATION6) BETWEEN l_person_start_date AND l_person_end_date THEN
104 -- Disability started within employment term. Use disability start date.
105 l_disabilities_start_date := FND_date.canonical_to_date(l_disabled.PEI_INFORMATION6);
106 ELSE
107 -- Disability Started after end date of employee
108 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74992_DIS_BAD_STR_DATE');
109 l_record_status := 1;
110 END IF;
111
112 /* Determine End date for disability record */
113 IF l_disabled.PEI_INFORMATION7 IS NULL THEN
114 -- Disability end Date is NULL. Use person end date
115 l_disabilities_end_date := l_person_end_date;
116 ELSIF FND_date.canonical_to_date(l_disabled.PEI_INFORMATION7) > l_person_end_date THEN
117 -- Disability end date exceeds employment term, use end of employment
118 l_disabilities_end_date := l_person_end_date;
119 ELSIF (FND_date.canonical_to_date(l_disabled.PEI_INFORMATION7) between l_person_start_date AND l_person_end_date) THEN
120 -- Disability ended within employment term. Use Disability end date.
121 l_disabilities_end_date := FND_date.canonical_to_date(l_disabled.PEI_INFORMATION7);
122 ELSE
123 -- Disability end before start date of employeee
124 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74993_DIS_BAD_END_DATE');
125 l_record_status := 1;
126 END IF;
127
128
129 If l_record_status = 1 THEN
130 -- We could not create the record because the dates were invalid.
131 -- Write out disability information to log for mannual user entry.
132 write_disability_to_log(l_disabled);
133 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
134 l_run_status := 1; -- Set status of run to warning.
135 end if;
136 -- Set the record status so that it is not processed in the future.
137 update per_people_extra_info
138 set PEI_INFORMATION30 = 'INVALID_DATES'
139 where person_id = l_disabled.person_id
140 and PEI_INFORMATION_CATEGORY = 'FR_DISABILITY'
141 and PERSON_EXTRA_INFO_ID = l_disabled.PERSON_EXTRA_INFO_ID;
142
143 ELSE
144 /* Map fields to new values and default mandatory fields that are NULL */
145 SELECT decode(l_disabled.PEI_INFORMATION9, 'Y', 'OCC_INC', NULL)
146 INTO l_reason
147 from DUAL;
148
149 if l_disabled.PEI_INFORMATION4 IS NULL THEN
150 l_category := 'UNKNOWN';
151 /* Field is defaulted Change status to warning. And write message to log */
152 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74994_DIS_CAT_DFLT',
153 p_token1 => 'EMPLOYEE:'|| l_disabled.employee_number);
154 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
155 l_run_status := 1; -- Set status of run to warning.
156 end if;
157 else
158 l_category := l_disabled.PEI_INFORMATION4;
159 end if;
160
161
162
163 BEGIN -- Insert new disability section
164
165 SAVEPOINT start_insert;
166
167 PER_DISABILITY_API.create_disability(p_disability_id => l_disability_id,
168 p_person_id => l_disabled.PERSON_ID,
169 p_quota_fte => 1.00,
170 p_category => l_category,
171 p_status => 'A',
172 p_description => l_disabled.PEI_INFORMATION2,
173 p_reason => l_reason,
174 p_degree => TO_NUMBER(l_disabled.PEI_INFORMATION3),
175 p_work_restriction => l_disabled.PEI_INFORMATION8,
176 p_effective_date => l_disabilities_start_date,
177 p_effective_start_date => l_effective_start_date,
178 p_effective_end_date => l_effective_end_date,
179 p_dis_information_category => 'FR',
180 p_dis_information1 => l_disabled.PEI_INFORMATION1,
181 p_dis_information2 => l_disabled.PEI_INFORMATION5,
182 p_object_version_number => l_object_version_number);
183
184 /* End Date the Disability if different to end date of person */
185 IF l_disabilities_end_date<>l_person_end_date then
186 hr_utility.set_location('about to update',5);
187 --
188 PER_DISABILITY_API.delete_disability(p_disability_id => l_disability_id,
189 p_effective_date => l_disabilities_end_date,
190 p_datetrack_mode => 'DELETE',
191 p_object_version_number => l_object_version_number,
192 p_effective_start_date => l_effective_start_date,
193 p_effective_end_date => l_effective_end_date);
194 END IF; -- End of Disability End Date.
195
196 update per_people_extra_info
197 set PEI_INFORMATION30 = to_char(l_disability_id)
198 where person_id = l_disabled.person_id
199 and PEI_INFORMATION_CATEGORY = 'FR_DISABILITY'
200 and PERSON_EXTRA_INFO_ID = l_disabled.PERSON_EXTRA_INFO_ID;
201
202 exception when others then
203 rollback to start_insert;
204 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74995_DIS_UPG_FATAL'
205 ,p_token1 => 'STEP:10');
206 write_disability_to_log(l_disabled);
207 per_fr_upgrade_data_pkg.write_log(sqlcode);
208 per_fr_upgrade_data_pkg.write_log(sqlerrm);
209 l_run_status := 2; /* Fatal Error */
210 END; -- end of section inserting new disability
211
212 /* Commit every record to ensure conc log corresponds to records in DB */
213 commit;
214
215 END IF;
216
217 FETCH csr_disabled_entries INTO l_disabled;
218 END LOOP;
219 CLOSE csr_disabled_entries;
220
221 return l_run_status;
222
223 exception when others then
224 rollback;
225 CLOSE csr_disabled_entries;
226 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74995_DIS_UPG_FATAL'
227 ,p_token1 => 'STEP:50');
228 per_fr_upgrade_data_pkg.write_log(sqlcode);
229 per_fr_upgrade_data_pkg.write_log(sqlerrm);
230 return 2; /* Fatal Error */
231 END transfer_data;
232
233
234 /***********************************************************************
235 * function RUN_UPGRADE *
236 * This fucntion must be called from *
237 * per_fr_upgrade_data_pkg.run_upgrade *
238 * return = 0 for Status Normal *
239 * return = 1 for Status Warning *
240 * return = 2 for Status Error *
241 ***********************************************************************/
242 function run_upgrade(p_business_group_id number) return number
243 IS
244 l_status number :=0;
245 l_error_status number :=0;
246 l_proc varchar2(72) := g_package||'.run_upgrade';
247 --
248 begin
249 --
250 hr_utility.set_location('Entered '||l_proc,5);
251 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_COTOREP_CODE'
252 ,p_core_lookup_type => 'DISABILITY_CATEGORY');
253
254 l_error_status := l_status;
255
256 /* Lookups checked, Check for DF*/
257 l_status := per_fr_upgrade_data_pkg.check_dfs(p_df => 'PER_DISABILITIES');
258 if l_status > 0 then
259 l_error_status := l_status;
260 end if;
261
262 if l_error_status = 0 then
263 /* Lookups and DFs checked OK, upgrade data */
264 l_status := transfer_data(p_business_group_id => p_business_group_id);
265 RETURN l_status;
266 else
267 /* If check fails then fatal error */
268 RETURN 2;
269 end if;
270
271 /* Allow exceptions to be handled by calling unit do not trap here. */
272
273 end run_upgrade;
274
275
276 END PER_FR_DISABILITY_UPG_PKG;
[Home] [Help]