1 PACKAGE BODY HR_NL_DAILY_SICK_AND_RECOVERY AS
2 /* $Header: pernldsr.pkb 120.6 2010/10/02 14:57:08 vijranga ship $ */
3 --
4 -- Description
5 -- Calls to different procedures, will Insert, Update or Delete
6 -- rows from table PER_NL_ABSENCE_CHANGES
7 --
8 -- Call to the local function will return absence category.
9 --
10 -- columns legislation_code, absence_attandnce_id, date_changed,
11 -- update_type, reported_indicator and person_id in table
12 -- PER_NL_ABSENCE_CHANGES are mandatory.
13 --
14 -- Declare local function
15 --
16 FUNCTION absence_category(p_absence_attendance_id IN number) return varchar2;
17 --
18 --
19 PROCEDURE insert_person_absence_changes
20 (p_absence_attendance_id IN number
21 ,p_effective_date IN date
22 ,p_person_id IN number
23 ,p_date_projected_start IN date
24 ,p_date_start IN date
25 ,p_abs_information1 IN varchar2
26 ,p_date_projected_end IN date
27 ,p_date_end IN date) IS
28 --
29 v_update_type varchar2(30) := 'START';
30 v_reported_indicator varchar2(30) := 'N';
31 --
32 --
33 BEGIN
34 --
35 -- Added for GSI Bug 5472781
36 --
37 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
38 --
39 --
40 -- Row is inserted into PER_NL_ABSENCE_CHANGES when the
41 -- absence category is "'S'ickness"
42 --
43 If absence_category(p_absence_attendance_id) = 'S' then
44 insert into per_nl_absence_changes
45 (absence_attendance_id
46 ,date_changed
47 ,update_type
48 ,reported_indicator
49 ,person_id
50 ,sickness_start_date
51 ,percentage_sick
52 ,recovery_date
53 )
54 values
55 (p_absence_attendance_id
56 ,p_effective_date
57 ,v_update_type
58 ,v_reported_indicator
59 ,p_person_id
60 ,nvl(p_date_start,p_date_projected_start)
61 ,TO_NUMBER(p_abs_information1,'990.90') /* Bug 4375570 */
62 ,nvl(p_date_end,p_date_projected_end)
63 );
64 end if;
65 END IF;
66 END insert_person_absence_changes;
67 --
68 PROCEDURE update_person_absence_changes
69 (p_absence_attendance_id IN number
70 ,p_effective_date IN date
71 ,p_date_end IN date
72 ,p_date_projected_end IN date
73 ,p_date_start IN date
74 ,p_date_projected_start IN date
75 ,p_abs_information1 IN varchar2) IS
76 --
77 v_update_type varchar2(30) := NULL;
78 v_reported_indicator varchar2(30) := 'N';
79 --
80 -- This cursor will fetch old data to check
81 -- changes to the data.
82 --
83 cursor cur_per_abs_chags is
84 select person_id
85 ,date_end
86 ,date_projected_end
87 ,date_start
88 ,date_projected_start
89 ,abs_information1
90 from per_absence_attendances
91 where absence_attendance_id = p_absence_attendance_id;
92 --
93 l_rec cur_per_abs_chags%ROWTYPE;
94 --
95 --
96 BEGIN
97 --
98 -- Added for GSI Bug 5472781
99 --
100 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
101 --
102 --
103 -- Row is inserted into PER_NL_ABSENCE_CHANGES when the
104 -- absence category is "'S'ickness"
105 --
106 If absence_category(p_absence_attendance_id) = 'S' then
107 open cur_per_abs_chags;
108 fetch cur_per_abs_chags into l_rec;
109 --
110 if (nvl(p_date_end,hr_general.start_of_time) <>
111 nvl(l_rec.date_end,hr_general.start_of_time)
112 )
113 or
114 (nvl(p_date_projected_end,hr_general.start_of_time) <>
115 nvl(l_rec.date_projected_end,hr_general.start_of_time)
116 )
117 then
118 --
119 -- when there are any changes to DATE_END or
120 -- DATE_PROJECTED_END, update type in
121 -- PER_NL_ABSENCE_CHANGES is 'END'
122 --
123 v_update_type := 'END';
124 --
125 elsif (nvl(p_date_start,hr_general.start_of_time) <>
126 nvl(l_rec.date_start,hr_general.start_of_time)
127 )
128 or
129 (nvl(p_date_projected_start,hr_general.start_of_time) <>
130 nvl(l_rec.date_projected_start,hr_general.start_of_time)
131 )
132 or
133 (nvl(p_abs_information1,'-1') <>
134 nvl(l_rec.abs_information1,'-1')
135 ) then
136 --
137 -- when there are any changes to DATE_START,
138 -- DATE_PROJECTED_START or ABS_INFORMATION1 update type in
139 -- PER_NL_ABSENCE_CHANGES is 'UPDATE'
140 --
141 v_update_type := 'UPDATE';
142 --
143 end if;
144 --
145 close cur_per_abs_chags;
146 --
147 -- when there is change to v_update_type
148 -- then insert row into PER_NL_ABSENCE_CHANGES
149 --
150 if v_update_type is not null then
151 insert into per_nl_absence_changes
152 (absence_attendance_id
153 ,date_changed
154 ,update_type
155 ,reported_indicator
156 ,person_id
157 ,sickness_start_date
158 ,percentage_sick
159 ,recovery_date
160 )
161 values
162 (p_absence_attendance_id
163 ,p_effective_date
164 ,v_update_type
165 ,v_reported_indicator
166 ,l_rec.person_id
167 ,nvl(p_date_start,p_date_projected_start)
168 ,decode(p_abs_information1,NULL,NULL,TO_NUMBER(l_rec.abs_information1,'990.90')) /* Bug 4375570 */ -- 8342503
169 ,nvl(p_date_end,p_date_projected_end)
170 );
171 end if;
172 end if;
173 --
174 END IF;
175 --
176 END update_person_absence_changes;
177 --
178 PROCEDURE delete_person_absence_changes
179 (p_absence_attendance_id IN number) IS
180 --
181 -- This cursor fetchs old data from PER_ABSENCE_ATTENDANCES
182 -- required to maitain the details of the deleted absence.
183 --
184 cursor cur_per_abs_chags is
185 select person_id
186 ,date_end
187 ,date_projected_end
188 ,date_start
189 ,date_projected_start
190 ,abs_information1
191 from per_absence_attendances
192 where absence_attendance_id = p_absence_attendance_id;
193 --
194 l_rec cur_per_abs_chags%ROWTYPE;
195 --
196 v_update_type varchar2(30) := 'DELETE';
197 v_reported_indicator varchar2(30) := 'N';
198 v_effective_date date;
199 --
200 --
201 BEGIN
202 --
203 -- Added for GSI Bug 5472781
204 --
205 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
206 --
207 begin
208 --
209 -- This query return current session date
210 --
211 select nvl(effective_date,trunc(sysdate))
212 into v_effective_date
213 from fnd_sessions
214 where userenv('sessionid') = session_id;
215 exception
216 when no_data_found then
217 null;
218 end;
219 --
220 open cur_per_abs_chags;
221 fetch cur_per_abs_chags into l_rec;
222 close cur_per_abs_chags;
223 --
224 -- Insert data into table PER_NL_ABSENCE_CHANGES only
225 -- only when absence category is "'S'ickness".
226 --
227 If absence_category(p_absence_attendance_id) = 'S' then
228 insert into per_nl_absence_changes
229 (absence_attendance_id
230 ,date_changed
231 ,update_type
232 ,reported_indicator
233 ,person_id
234 ,sickness_start_date
235 ,percentage_sick
236 ,recovery_date
237 )
238 values
239 (p_absence_attendance_id
240 ,v_effective_date
241 ,v_update_type
242 ,v_reported_indicator
243 ,l_rec.person_id
244 ,nvl(l_rec.date_start,l_rec.date_projected_start)
245 ,TO_NUMBER(l_rec.abs_information1,'990.90') /* BUG 4375570 */
246 ,nvl(l_rec.date_end,l_rec.date_projected_end)
247 );
248 end if;
249 --
250 END IF;
251 END delete_person_absence_changes;
252 --
253 procedure purge_per_nl_absence_changes
254 (p_errbuf OUT nocopy varchar2
255 ,p_retcode OUT nocopy varchar2
256 ,p_effective_date IN varchar2
257 ,p_business_group_id IN number) is
258 --
259 v_effective_date date := fnd_date.string_to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
260 --
261 begin
262 --
263 DELETE from per_nl_absence_changes pnac
264 WHERE months_between(trunc(v_effective_date), pnac.date_changed) > 12
265 AND person_id in ( SELECT person_id
266 FROM per_all_people_f
267 WHERE business_group_id = p_business_group_id);
268 --
269 end purge_per_nl_absence_changes;
270 --
271 procedure update_reported_absence_chgs
272 (p_effective_date IN date
273 ,p_prev_rep_chg IN varchar2
274 ,p_structure_version_id IN number
275 ,p_top_org_id IN number) is
276 --
277 -- This cursor will fetch same rows as that of the rows
278 -- fetched by the report 'Daily Sick and Recovery Report'
279 --
280
281 cursor cur_upd is
282 select
283 paaf.person_id person_id
284 ,paaf.effective_start_date effective_start_date
285 ,paaf.effective_end_date effective_end_date
286 from per_all_assignments_f paaf
287 where paaf.primary_flag = 'Y'
288 and paaf.organization_id in
289 (SELECT pose1.organization_id_child
290 FROM (SELECT pose.organization_id_child ,
291 pose.organization_id_parent
292 FROM per_org_structure_elements pose
293 WHERE pose.org_structure_version_id = p_structure_version_id
294 ) pose1
295 CONNECT BY PRIOR pose1.organization_id_child =
296 pose1.organization_id_parent
297 START WITH pose1.organization_id_parent = p_top_org_id
298 UNION
299 SELECT p_top_org_id
300 from dual
301 );
302
303
304 --
305 begin
306 --
307 for rec_upd in cur_upd
308 loop
309 update per_nl_absence_changes pnac
310 set pnac.reported_indicator = 'Y'
311 WHERE pnac.person_id = rec_upd.person_id
312 AND nvl(p_effective_date,pnac.sickness_start_date )
313 BETWEEN rec_upd.effective_start_date AND rec_upd.effective_end_date
314 AND pnac.reported_indicator = decode(p_prev_rep_chg, 'Y',pnac.reported_indicator, 'N')
315 AND pnac.date_changed = nvl(p_effective_date, pnac.date_changed);
316 end loop;
317 --
318 end update_reported_absence_chgs;
319 --
320 -- This local function will return sickness category for a supplied
321 -- absence_attedance_id.
322 --
323 FUNCTION absence_category
324 (p_absence_attendance_id IN number) return varchar2 is
325 --
326 -- cursor will fetch absence_category
327 --
328 cursor cur_per_abs_s_chags is
329 select paat.absence_category
330 from per_absence_attendances paa,
331 per_absence_attendance_types paat
332 where paa.absence_attendance_type_id
333 = paat.absence_attendance_type_id
334 and paa.absence_attendance_id = p_absence_attendance_id
335 and paa.business_group_id = paat.business_group_id;
336 --
337 v_per_abs_chgs per_absence_attendance_types.absence_category%TYPE;
338 --
339 BEGIN
340 --
341 open cur_per_abs_s_chags;
342 fetch cur_per_abs_s_chags into v_per_abs_chgs;
343 close cur_per_abs_s_chags;
344 --
345 return(v_per_abs_chgs);
346 --
347 END absence_category;
348
349 /* Bug#10163492 fix Added this function to populate the absence reasons automatically
350 based on the values set at EITs NL_TML and NL_DS_SICK_INFO */
351 FUNCTION get_eit_sick_reason
352 (p_person_id IN number
353 , p_assignment_id IN number
354 , p_effective_date IN date) return varchar2 is
355
356 cursor cur_eit_sick_reasons (l_person_id IN number) is
357 select PEI_INFORMATION1, PEI_INFORMATION2, PEI_INFORMATION3
358 from per_people_extra_info pei
359 where pei.person_id = l_person_id
360 and pei.information_type = 'NL_DS_SICK_INFO';
361
362 cursor cur_get_tml_info( l_assignment_id number, l_effective_date date) is
363 select aei.aei_INFORMATION3
364 from per_assignment_extra_info aei
365 where aei.assignment_id = l_assignment_id
366 AND aei_information_category = 'NL_TML'
367 and l_effective_date between fnd_date.canonical_to_date(aei_information1) and
368 NVL(fnd_date.canonical_to_date(aei_information2), to_date('31-12-4712','DD-MM-YYYY'));
369 --
370 l_standby_emp varchar2(10);
371 l_art_4_5 varchar2(10);
372 l_art_29 varchar2(10);
373 l_temp_worker varchar2(10);
374
375 BEGIN
376 OPEN cur_get_tml_info(p_assignment_id, p_effective_date);
377 FETCH cur_get_tml_info INTO l_temp_worker;
378 CLOSE cur_get_tml_info;
379
380 if l_temp_worker is not null and l_temp_worker = '17' then
381 return '01';
382 else
383 OPEN cur_eit_sick_reasons(p_person_id);
384 FETCH cur_eit_sick_reasons INTO l_standby_emp, l_art_4_5, l_art_29;
385 CLOSE cur_eit_sick_reasons;
386
387 if(l_art_4_5 is not null and l_art_4_5 = 'Y') then
388 return '05';
389 elsif (l_art_29 is not null and l_art_29 in ('01', '02', '03', '04', '05', '06', '07', '08', '09','10', '11', '99') ) then
390 return '04';
391 elsif (l_standby_emp is not null and l_standby_emp = 'Y') then
392 return '02';
393 else
394 return '00';
395 end if;
396 end if;
397
398 END get_eit_sick_reason;
399
400 END HR_NL_DAILY_SICK_AND_RECOVERY;
[Home] [Help]