[Home] [Help]
PACKAGE BODY: APPS.PER_NO_SICKLEAVEMONITORING
Source
1 PACKAGE BODY per_no_sickleavemonitoring AS
2 /* $Header: pernoslmr.pkb 120.2.12020000.1 2012/07/18 07:28:38 asudhaka noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4 EOL VARCHAR2(5) := fnd_global.newline();
5 -------------------------------------------------------------------------------+
6 /* Function used to default User table value in Org DFF */
7 FUNCTION get_default_followup_days(p_date IN DATE
8 ,p_followup IN NUMBER)
9 RETURN NUMBER
10 AS
11
12 CURSOR csr_default_followup_days(c_date IN DATE DEFAULT SYSDATE
13 ,c_followup IN NUMBER) IS
14 SELECT NVL(to_number(ucf.value),0)
15 FROM pay_user_column_instances_f ucf
16 ,pay_user_columns uc
17 ,pay_user_rows_f ur
18 ,pay_user_tables ut
19 WHERE ut.legislation_code = 'NO'
20 AND ut.user_table_name = 'Sick Leave Monitoring'
21 AND ur.legislation_code = 'NO'
22 AND ur.user_table_id = ut.user_table_id
23 AND c_date BETWEEN ur.effective_start_date
24 AND ur.effective_end_date
25 AND ur.row_low_range_or_name = to_char(c_followup)
26 AND uc.legislation_code = 'NO'
27 AND uc.user_table_id = ut.user_table_id
28 AND uc.user_column_name = 'Max Days'
29 AND ucf.legislation_code = 'NO'
30 AND ucf.user_row_id = ur.user_row_id
31 AND ucf.user_column_id = uc.user_column_id
32 AND c_date BETWEEN ucf.effective_start_date
33 AND ucf.effective_end_date;
34
35 DAYS NUMBER :=0;
36 BEGIN
37
38 OPEN csr_default_followup_days( c_date => p_date
39 ,c_followup => p_followup);
40 FETCH csr_default_followup_days INTO DAYS;
41 CLOSE csr_default_followup_days;
42
43 RETURN DAYS;
44
45 EXCEPTION
46 WHEN TOO_MANY_ROWS THEN
47 RETURN NULL;
48 WHEN NO_DATA_FOUND THEN
49 RETURN NULL;
50
51 END get_default_followup_days;
52 ------------------------------------------------------------------------------+
53 PROCEDURE generate( p_employer NUMBER
54 ,p_from VARCHAR2
55 ,p_to VARCHAR2
56 ,p_template_name VARCHAR2
57 ,p_xml OUT NOCOPY CLOB) IS
58
59 -- -- -- -- -- -- -- -- -- -- Private Cursors -- ---- -- -- -- -- -- -- -- --+
60 --#CSR1
61 CURSOR csr_employer_details(c_org_id NUMBER) IS
62 SELECT hou.name Name
63 ,hou.business_group_id bg_id
64 FROM hr_organization_units hou
65 WHERE hou.organization_id = c_org_id;
66
67 --#CSR2
68 CURSOR csr_user_table_details(c_date DATE) IS
69 SELECT ur.row_low_range_or_name followup
70 ,max (decode (uc.user_column_name
71 ,'Max Days'
72 ,to_number(ucf.value)
73 ,0)) days
74 ,max (decode (uc.user_column_name
75 ,'Action Message'
76 ,ucf.value
77 ,0)) message
78 FROM pay_user_column_instances_f ucf
79 ,pay_user_columns uc
80 ,pay_user_rows_f ur
81 ,pay_user_tables ut
82 WHERE ut.legislation_code = 'NO'
83 AND ut.user_table_name = 'Sick Leave Monitoring'
84 AND ur.legislation_code = 'NO'
85 AND ur.user_table_id = ut.user_table_id
86 AND c_date BETWEEN ur.effective_start_date
87 AND ur.effective_end_date
88 AND uc.legislation_code = 'NO'
89 AND uc.user_table_id = ut.user_table_id
90 AND ucf.legislation_code = 'NO'
91 AND ucf.user_row_id = ur.user_row_id
92 AND ucf.user_column_id = uc.user_column_id
93 AND c_date BETWEEN ucf.effective_start_date
94 AND ucf.effective_end_date
95 GROUP BY ur.row_low_range_or_name
96 ORDER BY ur.row_low_range_or_name;
97
98 --#CSR3
99 CURSOR csr_employer_days(c_date IN DATE) IS
100 SELECT to_number (global_value)
101 FROM ff_globals_f
102 WHERE global_name = 'NO_ABS_LINK_PERIOD'
103 AND legislation_code = 'NO'
104 AND c_date BETWEEN effective_start_date
105 AND effective_end_date;
106
107 --#CSR4
108 CURSOR csr_all_absences(c_bg_id IN NUMBER
109 ,c_org_id IN NUMBER
110 ,c_from_date IN DATE
111 ,c_to_date IN DATE) IS
112 SELECT paa.absence_attendance_id abs_id
113 ,paa.date_start start_date
114 ,NVL(paa.date_end,c_to_date) end_date
115 ,papf.employee_number employee_number
116 ,papf.full_name employee_name
117 FROM per_absence_attendances paa
118 ,per_absence_attendance_types paat
119 ,per_all_assignments_f paaf
120 ,per_assignment_status_types past
121 ,per_all_people_f papf
122 ,hr_soft_coding_keyflex hsc
123 ,hr_organization_information hoi
124 WHERE paa.person_id = papf.person_id
125 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
126 AND paaf.person_id = papf.person_id
127 AND paaf.assignment_status_type_id = past.assignment_status_type_id
128 AND papf.business_group_id = c_bg_id
129 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
130 AND hoi.org_information1 = hsc.segment2
131 AND hoi.organization_id = c_org_id
132 AND past.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN')
133 AND hoi.org_information_context = 'NO_LOCAL_UNITS'
134 AND paaf.primary_flag = 'Y'
135 AND paat.absence_category IN ('S','PTS')
136 AND paa.date_start BETWEEN add_months(c_from_date,-24) --Last 2 years
137 AND c_to_date
138 AND paa.date_start BETWEEN papf.effective_start_date
139 AND papf.effective_end_date
140 AND paa.date_start BETWEEN paaf.effective_start_date
141 AND paaf.effective_end_date
142 ORDER BY paa.date_start DESC;
143
144 -- -- -- -- -- -- -- -- -- Private Variables , Types -- -- -- -- -- -- -- -- -+
145
146 TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
147 TYPE AbsenceDetails IS RECORD (
148 start_date per_absence_attendances.date_start%TYPE
149 ,end_date per_absence_attendances.date_end%TYPE
150 ,enabled SMALLINT := NULL
151 ,employee_number per_all_people_f.employee_number%TYPE
152 ,employee_name per_all_people_f.full_name%TYPE
153 ,sickness_days date_table
154 ,reimb_days date_table
155 );
156 TYPE user_table IS TABLE OF csr_user_table_details%ROWTYPE INDEX BY BINARY_INTEGER;
157 TYPE strings IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
158 TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
159 TYPE absence_table IS TABLE OF AbsenceDetails INDEX BY BINARY_INTEGER;
160
161 r_employer_details csr_employer_details%ROWTYPE;
162 dummy_date_table date_table;
163 absences absence_table;
164 data user_table;
165 actions strings;
166 override number_table;
167 absence_count number_table;
168 followup_days number_table;
169
170 max_days NUMBER :=0;
171 absence_link_period NUMBER;
172 l_string VARCHAR2(32767) := NULL;
173 l_xml CLOB;
174 l_action_from DATE;
175 l_action_to DATE;
176 i BINARY_INTEGER;
177 k BINARY_INTEGER;
178
179 -- -- -- -- -- -- -- -- Private Functions and Procedures -- -- -- -- -- -- -- +
180 --#PROC1
181 PROCEDURE process_absence_details(p_abs_id IN NUMBER
182 ,p_start_date IN DATE
183 ,p_employee_number IN per_all_people_f.employee_number%TYPE
184 ,p_employee_name IN per_all_people_f.full_name%TYPE
185 ,p_sickness_days IN date_table
186 ) IS
187
188
189 --#CSR5
190 CURSOR csr_connected_absence(c_abs_id IN NUMBER) IS
191 SELECT prev_abs.absence_attendance_id abs_id
192 ,curr_abs.date_start - prev_abs.date_end duration
193 ,prev_abs.date_start start_date
194 ,curr_abs.date_start curr_start_date
195 ,curr_abs.date_end curr_end_date
196 FROM per_absence_attendances prev_abs
197 ,per_absence_attendances curr_abs
198 ,per_absence_attendance_types pat
199 WHERE prev_abs.absence_attendance_type_id = pat.absence_attendance_type_id
200 AND prev_abs.date_end < curr_abs.date_start
201 AND pat.absence_category IN ('S','PTS')
202 AND (((curr_abs.date_start - prev_abs.date_end) <= absence_link_period)
203 OR (decode (trim (to_char (prev_abs.date_end,'DAY'))
204 ,'FRIDAY',next_day (prev_abs.date_end,'SUNDAY')
205 ,'SATURDAY',next_day (prev_abs.date_end,'SUNDAY')
206 ,prev_abs.date_end) BETWEEN decode (trim (to_char (curr_abs.date_start,'DAY'))
207 ,'MONDAY',curr_abs.date_start - 2
208 ,'SUNDAY',curr_abs.date_start - 1
209 ,curr_abs.date_start) - (absence_link_period+1)
210 AND decode (trim (to_char (curr_abs.date_start,'DAY'))
211 ,'MONDAY',curr_abs.date_start - 2
212 ,'SUNDAY',curr_abs.date_start - 1
213 ,curr_abs.date_start)-1))
214 AND prev_abs.date_start IS NOT NULL
215 AND prev_abs.date_end IS NOT NULL
216 AND prev_abs.person_id = curr_abs.person_id
217 AND curr_abs.absence_attendance_id = c_abs_id
218 ORDER BY duration;
219 --#CSR6
220 CURSOR csr_unconnected_absence(c_abs_id IN NUMBER) IS
221 SELECT abs.date_start start_date
222 ,NVL(abs.date_end,l_action_to) end_date
223 FROM per_absence_attendances abs
224 WHERE abs.absence_attendance_id = c_abs_id;
225
226 r_connected_absence csr_connected_absence%ROWTYPE;
227 r_unconnected_absence csr_unconnected_absence%ROWTYPE;
228 sick_day DATE;
229 l_sickness_days date_table;
230 l_reimb_days date_table;
231 l_days NUMBER;
232 l_reimb_day NUMBER;
233 BEGIN
234 OPEN csr_connected_absence(c_abs_id => p_abs_id);
235 FETCH csr_connected_absence INTO r_connected_absence;
236
237 IF csr_connected_absence%FOUND THEN
238 -- Conntected Absence
239 CLOSE csr_connected_absence;
240 absences(p_abs_id).enabled := 0;
241
242 --Store current sickness_days
243 l_sickness_days := p_sickness_days;
244 sick_day := r_connected_absence.curr_end_date;
245 WHILE sick_day >= r_connected_absence.curr_start_date LOOP
246 l_sickness_days(NVL(l_sickness_days.LAST,0)+1) := sick_day;
247 sick_day := sick_day-1;
248 END LOOP;
249 --Process the connected absence
250 process_absence_details(p_abs_id => r_connected_absence.abs_id
251 ,p_start_date => r_connected_absence.start_date
252 ,p_employee_number => p_employee_number
253 ,p_employee_name => p_employee_name
254 ,p_sickness_days => l_sickness_days);
255
256 ELSIF csr_connected_absence%NOTFOUND THEN
257 --Unconnected Absence
258 CLOSE csr_connected_absence;
259
260 OPEN csr_unconnected_absence(c_abs_id => p_abs_id);
261 FETCH csr_unconnected_absence INTO r_unconnected_absence;
262 CLOSE csr_unconnected_absence;
263
264 --Store current sickness_days
265 l_sickness_days := p_sickness_days;
266 sick_day := r_unconnected_absence.end_date;
267 WHILE sick_day >=r_unconnected_absence.start_date LOOP
268 l_sickness_days(NVL(l_sickness_days.LAST,0)+1) := sick_day;
269 sick_day := sick_day-1;
270 END LOOP;
271
272 --Store all reimbursement days before the Action To Date
273 l_days:=0;
274 l_reimb_day:=0;
275 FOR I IN REVERSE l_sickness_days.FIRST..l_sickness_days.LAST LOOP
276 l_days:=l_days+1;
277 IF l_days > absence_link_period
278 AND (trim(to_char (l_sickness_days(I),'DAY')) NOT IN ('SATURDAY','SUNDAY'))
279 AND l_sickness_days(I)<=l_action_to THEN
280 l_reimb_day:=l_reimb_day+1;
281 l_reimb_days(l_reimb_day) := l_sickness_days(I);
282 END IF;
283 EXIT WHEN l_reimb_day>max_days;
284 EXIT WHEN l_sickness_days(I)>l_action_to;
285 END LOOP;
286
287 IF l_reimb_day >0 THEN
288 absences(p_abs_id).enabled := 1;
289 absences(p_abs_id).reimb_days := l_reimb_days;
290 absences(p_abs_id).start_date := p_start_date;
291 absences(p_abs_id).employee_number := p_employee_number;
292 absences(p_abs_id).employee_name := p_employee_name;
293 ELSE
294 absences(p_abs_id).enabled := 0;
295 END IF;
296 END IF;
297
298 EXCEPTION
299 WHEN others THEN
300 IF g_debug THEN
301 hr_utility.trace('Error in process_absence_details: '||sqlerrm);
302 END IF;
303 END process_absence_details;
304
305 --#FUNC2
306 FUNCTION get_override_followup_days(p_followup IN NUMBER
307 ,p_org_id IN NUMBER)
308 RETURN NUMBER
309 AS
310 --#CSR7
311 CURSOR csr_override_followup_days(c_followup IN NUMBER
312 ,c_org_id IN NUMBER) IS
313 SELECT decode (c_followup
314 ,1,org_information1
315 ,2,org_information2
316 ,3,org_information3
317 ,4,org_information4
318 ,5,org_information5
319 ,9999) days
320 FROM hr_organization_information
321 WHERE organization_id = c_org_id
322 AND org_information_context = 'NO_SLM_OVERRIDE';
323
324 DAYS NUMBER :=9999;
325
326 BEGIN
327
328 OPEN csr_override_followup_days(c_followup => p_followup
329 ,c_org_id => p_org_id);
330 FETCH csr_override_followup_days INTO DAYS;
331 CLOSE csr_override_followup_days;
332 RETURN DAYS;
333
334 EXCEPTION
335 WHEN TOO_MANY_ROWS THEN
336 RETURN 9999;
337 WHEN NO_DATA_FOUND THEN
338 RETURN 9999;
339 END get_override_followup_days;
340
341 ------------------------------------------------------------------------------+
342 BEGIN
343 IF g_debug THEN
344 hr_utility.set_location(' Entering Procedure GENERATE',1);
345 hr_utility.trace('p_employer : '||p_employer);
346 hr_utility.trace('p_from : '||p_from);
347 hr_utility.trace('p_to : '||p_to);
348 hr_utility.trace('p_template_name: '||p_template_name);
349 END IF;
350
351
352 l_action_from := fnd_date.canonical_to_date(p_from);
353 l_action_to := fnd_date.canonical_to_date(p_to);
354 --Fetch Employer Details
355 OPEN csr_employer_details(p_employer);
356 FETCH csr_employer_details INTO r_employer_details;
357 CLOSE csr_employer_details;
358 --Fetch Linking days
359 OPEN csr_employer_days(l_action_from);
360 FETCH csr_employer_days INTO absence_link_period;
361 CLOSE csr_employer_days;
362
363 --Fetch Legislative default and Org Override information
364 data.DELETE;
365 override.DELETE;
366 followup_days.DELETE;
367 FOR r_user_table_details IN csr_user_table_details(l_action_to)
368 LOOP
369 data(to_number(r_user_table_details.followup)) := r_user_table_details;
370
371 max_days := GREATEST(max_days,r_user_table_details.days);
372
373 override(to_number(r_user_table_details.followup)) :=
374 get_override_followup_days
375 (p_followup => to_number(r_user_table_details.followup)
376 ,p_org_id => p_employer);
377
378 followup_days(to_number(r_user_table_details.followup)):=
379 LEAST(data(to_number(r_user_table_details.followup)).days,
380 override(to_number(r_user_table_details.followup)));
381 END LOOP;
382
383 -- Build XML Structure --
384
385 l_string := l_string || '<PERNOSLMR>'||EOL;
386 l_string := l_string || '<LEGAL_EMPLOYER>'||r_employer_details.name||'</LEGAL_EMPLOYER>'||EOL;
387 l_string := l_string || '<ACTION_FROM>'||fnd_date.date_to_displaydate(l_action_from)||'</ACTION_FROM>'||EOL;
388 l_string := l_string || '<ACTION_TO>'||fnd_date.date_to_displaydate(l_action_to)||'</ACTION_TO>'||EOL;
389
390 actions.delete;
391 FOR i IN data.FIRST..data.LAST LOOP
392 actions(i) := '<G_ACTIONS>'||EOL;
393 actions(i) := actions(i) || '<ACTION_MESSAGE>'||data(i).message||'</ACTION_MESSAGE>'||EOL;
394 actions(i) := actions(i) || '<FOLLOW_UP_DAYS>'||followup_days(i)||'</FOLLOW_UP_DAYS>'||EOL;
395 actions(i) := actions(i) || '<FOLLOW_UP>'||data(i).followup||'</FOLLOW_UP>'||EOL;
396 absence_count(i) :=0;
397 END LOOP;
398
399 --Fetch all absences and for connected absences, enable only the first absence.
400 FOR r_all_absences IN csr_all_absences
401 (c_bg_id => r_employer_details.bg_id
402 ,c_org_id => p_employer
403 ,c_from_date => l_action_from
404 ,c_to_date => l_action_to)
405 LOOP
406 IF (r_all_absences.abs_id IS NOT NULL) AND
407 (NOT absences.EXISTS(r_all_absences.abs_id) OR absences(r_all_absences.abs_id).enabled IS NULL) THEN
408 -- Process only unprocessed absences
409 process_absence_details(p_abs_id => r_all_absences.abs_id
410 ,p_start_date => r_all_absences.start_date
411 ,p_employee_number => r_all_absences.employee_number
412 ,p_employee_name => r_all_absences.employee_name
413 ,p_sickness_days => dummy_date_table);
414 END IF;
415 END LOOP;
416 -- For all enabled absences find the followp dates and report them if in range.
417 i := absences.FIRST;
418 <<Absences_Loop>>
419 WHILE i IS NOT NULL LOOP
420 IF absences(i).enabled=1 THEN
421 <<Followup_Loop>>
422 FOR k IN followup_days.FIRST..followup_days.LAST LOOP
423 IF absences(i).reimb_days.EXISTS(followup_days(k)) THEN
424 IF absences(i).reimb_days(followup_days(k)) BETWEEN l_action_from AND l_action_to THEN
425 actions(k) := actions(k) || '<G_ABSENCES>'||EOL;
426 actions(k) := actions(k) || '<ACTION_DATE>'||
427 fnd_date.date_to_displaydate(absences(i).reimb_days(followup_days(k)))
428 ||'</ACTION_DATE>'||EOL;
429 actions(k) := actions(k) || '<START_DATE>'||fnd_date.date_to_displaydate(absences(i).start_date)||'</START_DATE>'||EOL;
430 actions(k) := actions(k) || '<EMPLOYEE_NUMBER>'||absences(i).employee_number||'</EMPLOYEE_NUMBER>'||EOL;
431 actions(k) := actions(k) || '<EMPLOYEE_NAME><![CDATA['||absences(i).employee_name||']]></EMPLOYEE_NAME>'||EOL;
432 actions(k) := actions(k) || '</G_ABSENCES>'||EOL;
433 absence_count(k) := absence_count(k)+1;
434 END IF; --If Followup day in Date range
435 END IF; --If its a followup Day exists
436 END LOOP; --For each follow-up
437 END IF; --If Absence enabled
438 i:= absences.NEXT(i);
439 END LOOP; --For each Absence
440
441 FOR i IN data.FIRST..data.LAST
442 LOOP
443 actions(i) := actions(i) || '</G_ACTIONS>'||EOL;
444 IF absence_count(i)>0 THEN
445 l_string := l_string||actions(i);
446 END IF;
447 END LOOP;
448
449 l_string := l_string || '</PERNOSLMR>'||EOL;
450
451 -- Writing XML File
452 dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
453 dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
454 dbms_lob.writeAppend( l_xml, length(l_string), l_string);
455 p_xml := l_xml;
456 dbms_lob.freeTemporary(l_xml);
457
458 IF g_debug THEN
459 hr_utility.set_location(' Leaving Procedure GENERATE',2);
460 END IF;
461 EXCEPTION
462 WHEN others THEN
463 IF g_debug THEN
464 hr_utility.set_location('Error raised in GENERATE ',9);
465 hr_utility.trace('Error: '||sqlerrm);
466 END IF;
467 RAISE;
468 END generate;
469
470 ------------------------------------------------------------------------------+
471
472 END per_no_sickleavemonitoring;