4 -------------------------------------------------------------------------------------------------------------------------
1 PACKAGE BODY PAY_DK_ABSENCE_USER AS
2 /*$Header: pydkabsence.pkb 120.26.12020000.2 2012/07/06 09:48:07 vmaripal ship $*/
3
5 /* Element populate function to return Input values of absence element */
6 -- NAME
7 -- Element_populate
8 -- PURPOSE
9 -- To populate element input values for absence recording.
10 -- ARGUMENTS
11 -- P_ASSIGNMENT_ID - Assignment id
12 -- P_PERSON_ID - Person id,
13 -- P_ABSENCE_ATTENDANCE_ID - Absence attendance id,
14 -- P_ELEMENT_TYPE_ID - Element type id,
18 -- USES
15 -- P_ABSENCE_CATEGORY - Absence category ( Sickness ),
16 -- P_INPUT_VALUE_NAME 1-15 - Output variable holds element input value name.
17 -- P_INPUT_VALUE 1-15 - Output variable holds element input value.
19 -- NOTES
20 -- The procedure fetches absence information from absence table with input absence_attendance_id and absence
21 -- category 'Sickness' and 'Vacation'. Then it assigns the values to output variables.
22 -------------------------------------------------------------------------------------------------------------------------
23 Function Element_populate(p_assignment_id in number,
24 p_person_id in number,
25 p_absence_attendance_id in number,
26 p_element_type_id in number,
27 p_absence_category in varchar2,
28 p_original_entry_id OUT nocopy NUMBER,
29 p_input_value_name1 OUT NOCOPY VARCHAR2,
30 p_input_value1 OUT NOCOPY VARCHAR2,
31 p_input_value_name2 OUT NOCOPY VARCHAR2,
32 p_input_value2 OUT NOCOPY VARCHAR2,
33 p_input_value_name3 OUT NOCOPY VARCHAR2,
34 p_input_value3 OUT NOCOPY VARCHAR2,
35 p_input_value_name4 OUT NOCOPY VARCHAR2,
36 p_input_value4 OUT NOCOPY VARCHAR2,
37 p_input_value_name5 OUT NOCOPY VARCHAR2,
38 p_input_value5 OUT NOCOPY VARCHAR2,
39 p_input_value_name6 OUT NOCOPY VARCHAR2,
40 p_input_value6 OUT NOCOPY VARCHAR2,
41 p_input_value_name7 OUT NOCOPY VARCHAR2,
42 p_input_value7 OUT NOCOPY VARCHAR2,
43 p_input_value_name8 OUT NOCOPY VARCHAR2,
44 p_input_value8 OUT NOCOPY VARCHAR2,
45 p_input_value_name9 OUT NOCOPY VARCHAR2,
46 p_input_value9 OUT NOCOPY VARCHAR2,
47 p_input_value_name10 OUT NOCOPY VARCHAR2,
48 p_input_value10 OUT NOCOPY VARCHAR2,
49 p_input_value_name11 OUT NOCOPY VARCHAR2,
50 p_input_value11 OUT NOCOPY VARCHAR2,
51 p_input_value_name12 OUT NOCOPY VARCHAR2,
52 p_input_value12 OUT NOCOPY VARCHAR2,
53 p_input_value_name13 OUT NOCOPY VARCHAR2,
54 p_input_value13 OUT NOCOPY VARCHAR2,
55 p_input_value_name14 OUT NOCOPY VARCHAR2,
56 p_input_value14 OUT NOCOPY VARCHAR2,
57 p_input_value_name15 OUT NOCOPY VARCHAR2,
58 p_input_value15 OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
59
60
61 /*Cursor to get the intial entry id*/
62 CURSOR csr_get_intial_entry_id(p_creator_id IN VARCHAR2 ,p_assignment_id IN NUMBER ) IS
63 SELECT
64 peef.element_entry_id
65 FROM
66 pay_element_entry_values_f peevf
67 ,pay_input_values_f pivf
68 ,pay_element_entries_f peef
69 WHERE
70 peevf.screen_entry_value = p_creator_id
71 AND pivf.input_value_id = peevf.input_value_id
72 AND pivf.NAME = 'CREATOR_ID'
73 AND pivf.legislation_code = 'DK'
74 AND peef.element_entry_id = peevf.element_entry_id
75 AND peef.assignment_id = p_assignment_id;
76
77 l_start_date date;
78 l_end_date date;
79 l_absent_reason number;
80 l_start_time varchar2(20);
81 l_end_time varchar2(20);
82 l_intial_absence varchar2(3);
83 l_initial_abs_creator_id number;
84
85 BEGIN
86
87
88 hr_utility.set_location('Entering: Element_populate ', 10);
89 hr_utility.set_location('p_absence_attendance_id'|| p_absence_attendance_id, 10);
90
91 -- Fetch absence attendance details
92 BEGIN
93 SELECT abs.date_start
94 ,abs.date_end
95 ,abs.ABS_ATTENDANCE_REASON_ID
96 ,abs.TIME_START
97 ,abs.TIME_END
98 ,abs.abs_information1
99 ,abs.abs_information2
100 INTO l_start_date
101 ,l_end_date
102 ,l_absent_reason
103 ,l_start_time
104 ,l_end_time
105 ,l_intial_absence
106 ,l_initial_abs_creator_id
107 FROM per_absence_attendances abs
108 WHERE abs.absence_attendance_id = p_absence_attendance_id;
109 EXCEPTION
110 WHEN OTHERS THEN
111 NULL;
112 END;
113 -- Check if absence category is S ( Sickness )
114 IF p_absence_category = 'S' THEN
115 p_input_value_name1 := 'Start Date';
116 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion added for 7037491
117 p_input_value_name2 := 'End Date';
118 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion added for 7037491
119 p_input_value_name3 := 'Start Time';
120 p_input_value3 := l_start_time;
121 p_input_value_name4 := 'End Time';
122 p_input_value4 := l_end_time;
123 p_input_value_name5 := 'Absent Reason';
124 -- Fetch absence reason from the lookup
125 BEGIN
126 SELECT Meaning
127 INTO p_input_value5
128 FROM FND_LOOKUP_VALUES_VL LKP
129 ,PER_ABSENCE_ATTENDANCES PAA
130 ,PER_ABS_ATTENDANCE_REASONS PAR
131 WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
132 AND PAA.ABSENCE_ATTENDANCE_ID = p_absence_attendance_id
133 AND PAR.ABS_ATTENDANCE_REASON_ID = l_absent_reason
134 AND LKP.lookup_type = 'ABSENCE_REASON'
135 AND LOOKUP_CODE = PAR.NAME
139 WHEN OTHERS THEN
136 -- Bug No 4994835. Group by clause introduced to avoid duplicate records
137 GROUP BY LKP.Meaning;
138 EXCEPTION
140 NULL;
141 END;
142 p_input_value_name6 := 'CREATOR_ID';
143 p_input_value6 := p_absence_attendance_id;
144
145 /* bug 13843742 start */
146 -- Check if absence category is HCR ( E- Holiday )
147 ELSIF p_absence_category = 'HCR' THEN
148 p_input_value_name1 := 'Start Date';
149 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion added for 7037491
150 p_input_value_name2 := 'End Date';
151 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion added for 7037491
152 p_input_value_name5 := 'Absent Reason';
153 -- Fetch absence reason from the lookup
154 BEGIN
155 SELECT Meaning
156 INTO p_input_value5
157 FROM FND_LOOKUP_VALUES_VL LKP
158 ,PER_ABSENCE_ATTENDANCES PAA
159 ,PER_ABS_ATTENDANCE_REASONS PAR
160 WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
161 AND PAA.ABSENCE_ATTENDANCE_ID = p_absence_attendance_id
162 AND PAR.ABS_ATTENDANCE_REASON_ID = l_absent_reason
163 AND LKP.lookup_type = 'ABSENCE_REASON'
164 AND LOOKUP_CODE = PAR.NAME
165 -- Bug No 4994835. Group by clause introduced to avoid duplicate records
166 GROUP BY LKP.Meaning;
167 EXCEPTION
168 WHEN OTHERS THEN
169 NULL;
170 END;
171 p_input_value_name8 := 'CREATOR_ID';
172 p_input_value8 := p_absence_attendance_id;
173
174 /* bug 13843742 end */
175 -- Check if absence category is V ( Vacation or Holiday )
176 ELSIF p_absence_category = 'V' THEN
177 p_input_value_name1 := 'Start Date';
178 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion added for 7037491
179 p_input_value_name2 := 'End Date';
180 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion added for 7037491
181 p_input_value_name3 := 'Start Time';
182 p_input_value3 := l_start_time;
183 p_input_value_name4 := 'End Time';
184 p_input_value4 := l_end_time;
185 p_input_value_name5 := 'Absent Reason';
186 -- Fetch absence reason from the lookup
187 BEGIN
188 SELECT Meaning
189 INTO p_input_value5
190 FROM FND_LOOKUP_VALUES_VL LKP
191 ,PER_ABSENCE_ATTENDANCES PAA
192 ,PER_ABS_ATTENDANCE_REASONS PAR
193 WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
194 AND PAA.ABSENCE_ATTENDANCE_ID = p_absence_attendance_id
195 AND PAR.ABS_ATTENDANCE_REASON_ID = l_absent_reason
196 AND LKP.lookup_type = 'ABSENCE_REASON'
197 AND LOOKUP_CODE = PAR.NAME
198 -- Bug No 4994835. Group by clause introduced to avoid duplicate records
199 GROUP BY LKP.Meaning;
200 EXCEPTION
201 WHEN OTHERS THEN
202 NULL;
203 END;
204 p_input_value_name8 := 'CREATOR_ID';
205 p_input_value8 := p_absence_attendance_id;
206
207 -- Check if absence category is M ( Maternity )
208 ELSIF p_absence_category in ('M','PA','IE_AL','IE_PL','PTM') THEN
209 p_input_value_name1 := 'Start Date';
210 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion added for 7037491
211 p_input_value_name2 := 'End Date';
212 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion added for 7037491
213 p_input_value_name3 := 'Start Time';
214 p_input_value3 := l_start_time;
215 p_input_value_name4 := 'End Time';
216 p_input_value4 := l_end_time;
217 p_input_value_name10 := 'CREATOR_ID';
218 p_input_value10 := p_absence_attendance_id;
219 /* Included for Maternity Linking*/
220 If p_absence_category in ('M','PTM') then
221 p_input_value_name13 := 'Full or Part Time';
222 p_input_value13 := hr_general.decode_lookup('DK_MAT_DURATION',p_absence_category);
223 End if;
224
225 IF l_intial_absence = 'N' THEN
226 OPEN csr_get_intial_entry_id(l_initial_abs_creator_id,p_assignment_id);
227 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
228 CLOSE csr_get_intial_entry_id;
229 End if;
230 END IF;
231
232
233 -- Return Y indicating to process the element for the input assignment id.
234 RETURN 'Y';
235 END Element_populate;
236
237 FUNCTION get_override_details
238 (p_assignment_id IN NUMBER
239 ,p_effective_date IN DATE
240 ,p_abs_start_date IN DATE
241 ,p_abs_end_date IN DATE
242 ,p_pre_birth_duration IN OUT NOCOPY NUMBER
243 ,p_post_birth_duration IN OUT NOCOPY NUMBER
244 ,p_maternity_allowance_used OUT NOCOPY NUMBER
245 ,p_shared_allowance_used OUT NOCOPY NUMBER
246 ,p_holiday_override OUT NOCOPY NUMBER
247 ,p_part_time_hours IN OUT NOCOPY NUMBER
248 ,p_part_time_hrs_freq IN OUT NOCOPY VARCHAR2
252 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
249 ,p_payment_during_leave IN OUT NOCOPY VARCHAR2 /* 11694807 */
250 ) RETURN NUMBER IS
251 --
253 SELECT ee.element_entry_id element_entry_id
254 , eev1.screen_entry_value screen_entry_value
255 , iv1.name
256 FROM per_all_assignments_f asg1
257 ,per_all_assignments_f asg2
258 ,per_all_people_f per
259 ,pay_element_links_f el
260 ,pay_element_types_f et
261 ,pay_input_values_f iv1
262 ,pay_element_entries_f ee
263 ,pay_element_entry_values_f eev1
264 WHERE asg1.assignment_id = p_assignment_id
265 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
266 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
267 AND per.person_id = asg1.person_id
268 AND asg2.person_id = per.person_id
269 AND et.element_name = 'Maternity Override'
270 AND et.legislation_code = 'DK'
271 AND iv1.element_type_id = et.element_type_id
272 AND iv1.name in ('Start Date', 'End Date','Payment During Leave'
273 ,'Pre Birth Duration Override','Post Birth Duration Override','Part Time Hours','Part Time Hours Frequency',
274 'Maternity Weeks Used','Shared Maternity Weeks Used','Holiday Accrual to Supress')
275 AND el.business_group_id = per.business_group_id
276 AND el.element_type_id = et.element_type_id
277 AND ee.assignment_id = asg2.assignment_id
278 AND ee.element_link_id = el.element_link_id
279 AND eev1.element_entry_id = ee.element_entry_id
280 AND eev1.input_value_id = iv1.input_value_id
281 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
282 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
283 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
284 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
285 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
286 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
287 ORDER BY ee.element_entry_id;
288 --
289 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
290 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
291 eename pay_input_values_f.name%TYPE );
292 l_rec l_record;
293 TYPE l_table is table of l_record index by BINARY_INTEGER;
294 l_tab l_table;
295 l_start_date date;
296 l_end_date date;
297 l_counter number ;
298 l_bool_match boolean;
299 l_num_match number;
300 l_check_nomatch number;
301 l_start_time NUMBER;
302 l_end_time NUMBER;
303 l_pay_during_leave VARCHAR2(40);
304 l_pre_birth_duration NUMBER;
305 l_post_birth_duration NUMBER;
306 l_over_ride_frequency NUMBER;
307 l_part_time_hours NUMBER;
308 l_part_time_hrs_frequency VARCHAR2(40);
309 l_maternity_allowance_used NUMBER;
310 l_shared_allowance_used NUMBER;
311 l_holiday_override NUMBER;
312 --
313 BEGIN
314 --
315 l_counter := 1;
316 l_bool_match := FALSE;
317 l_check_nomatch := 0;
318 p_maternity_allowance_used := 0;
319 p_shared_allowance_used := 0;
320 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
321 OPEN get_details(p_assignment_id , p_effective_date );
322 -- Assign the values to a table type
323 FETCH get_details BULK COLLECT INTO l_tab;
324 CLOSE get_details;
325 -- Loop through each values for processing.
326 FOR l_cur in 1..l_tab.count LOOP
327 -- Assign values to local variables.
328 case l_tab(l_cur).eename
329 when 'Start Date' then l_start_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
330 when 'End Date' then l_end_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
331 -- when 'Start Time' then l_start_time :=l_tab(l_cur).eevalue;
332 -- when 'End Time' then l_end_time :=l_tab(l_cur).eevalue;
333 when 'Payment During Leave' then l_pay_during_leave :=l_tab(l_cur).eevalue;
334 when 'Pre Birth Duration Override' then l_pre_birth_duration :=l_tab(l_cur).eevalue;
335 when 'Post Birth Duration Override' then l_post_birth_duration :=l_tab(l_cur).eevalue;
336 -- when 'Override Frequency' then l_over_ride_frequency :=l_tab(l_cur).eevalue;
337 when 'Part Time Hours' then l_part_time_hours :=l_tab(l_cur).eevalue;
338 when 'Part Time Hours Frequency' then l_part_time_hrs_frequency :=l_tab(l_cur).eevalue;
339 when 'Maternity Weeks Used' then l_maternity_allowance_used:=l_tab(l_cur).eevalue;
340 when 'Shared Maternity Weeks Used' then l_shared_allowance_used :=l_tab(l_cur).eevalue;
341 when 'Holiday Accrual to Supress' then l_holiday_override :=l_tab(l_cur).eevalue;
342 end case;
343 -- Check no. of input values of override element is 12
344
345 IF l_counter < 10 then
346 l_counter := l_counter + 1;
347 else
348 -- Check override element's start and end date matches with Absent element.
349 if l_start_date = p_abs_start_date then
350 -- Multiple entry exists with same start and end date
354 p_maternity_allowance_used := 0;
351 IF l_bool_match THEN
352 --p_pre_birth_duration := null;
353 --p_post_birth_duration := null;
355 p_shared_allowance_used := 0;
356 return -1;
357 -- Exact match found
358 ELSE
359 l_bool_match := True;
360 END IF;
361 -- Assign input values to output variables.
362 p_pre_birth_duration := nvl(l_pre_birth_duration,p_pre_birth_duration) ;
363 p_post_birth_duration := nvl(l_post_birth_duration,p_post_birth_duration);
364 p_maternity_allowance_used := nvl(l_maternity_allowance_used,0);
365 p_shared_allowance_used := nvl(l_shared_allowance_used,0);
366 p_holiday_override := l_holiday_override;
367 p_part_time_hours := nvl(l_part_time_hours,p_part_time_hours) ;
368 p_part_time_hrs_freq := nvl(l_part_time_hrs_frequency,p_part_time_hrs_freq);
369 p_payment_during_leave := nvl(l_pay_during_leave,p_payment_during_leave); /* 11694807 */
370 end if;
371 l_counter := 1;
372 END if;
373 END LOOP;
374 RETURN 0;
375 END get_override_details;
376 /* Function to get Maternity Absence */
377 FUNCTION get_absence_details
378 (p_assignment_id IN NUMBER
379 ,p_date_earned IN DATE
380 ,p_abs_attendance_id IN NUMBER
381 ,p_expected_dob OUT NOCOPY DATE
382 ,p_actual_dob OUT NOCOPY DATE
383 ,p_pre_birth_duration OUT NOCOPY NUMBER
384 ,p_post_birth_duration OUT NOCOPY NUMBER
385 ,p_frequency OUT NOCOPY VARCHAR2
386 ,p_normal_hours OUT NOCOPY NUMBER
387 ,p_maternity_weeks_transfer OUT NOCOPY NUMBER
388 ,p_holiday_accrual OUT NOCOPY VARCHAR2
389 ,p_payment_during_leave OUT NOCOPY VARCHAR2 /* 11694807 */
390 ) Return varchar2 is
391 CURSOR csr_absence IS SELECT
392 fnd_date.canonical_to_date(abs_information4) expected_dob,
393 fnd_date.canonical_to_date(abs_information5) actual_dob,
394 abs_information8 pre_birth_duration,
395 abs_information9 post_birth_duration,
396 abs_information10 maternity_weeks_transfer,
397 abs_information6 holiday_accrual,
398 abs_information7 payment_during_leave -- 11694807
399 FROM PER_ABSENCE_ATTENDANCES PAA
400 WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
401
402 CURSOR csr_asg_le IS SELECT
403 nvl(asg.normal_hours,hoi.org_information3) hours
404 ,nvl(asg.frequency,hoi.org_information4) frequency
405 FROM
406 hr_organization_information hoi
407 ,HR_SOFT_CODING_KEYFLEX SCL
408 ,PER_ALL_ASSIGNMENTS_F ASG
409 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
410 AND hoi.organization_id = scl.segment1
411 AND org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
412 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
413 AND fnd_date.canonical_to_date(p_date_earned) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE;
414 l_count VARCHAR2(2):='N';
415 l_duration_freq VARCHAR2(80);
416 Begin
417 hr_utility.trace('****************Inside***************');
418 FOR csr_abs_details in csr_absence
419 Loop
420 hr_utility.trace('****csr_abs_details.expected_dob**'||csr_abs_details.expected_dob);
421 p_expected_dob := csr_abs_details.expected_dob;
422 hr_utility.trace('****p_expected_dob**'||p_expected_dob);
423 p_actual_dob := csr_abs_details.actual_dob;
424 p_pre_birth_duration := csr_abs_details.pre_birth_duration;
425 p_post_birth_duration := csr_abs_details.post_birth_duration;
426 p_maternity_weeks_transfer := csr_abs_details.maternity_weeks_transfer;
427 p_holiday_accrual := csr_abs_details.holiday_accrual;
428 p_payment_during_leave := csr_abs_details.payment_during_leave; -- 11694807
429 l_count := 'Y';
430 End Loop;
431 FOR csr_asg_le_details in csr_asg_le
432 Loop
433 p_normal_hours:= csr_asg_le_details.hours;
434 p_frequency:= csr_asg_le_details.frequency;
435 l_count := 'Y';
436 End Loop;
437 return l_count;
438 End get_absence_details;
439
440
441 FUNCTION get_assg_term_date(p_business_group_id IN NUMBER, p_assignment_id IN NUMBER)
442 RETURN DATE IS
443
444 CURSOR csr_asg IS
445 SELECT MAX(paaf.effective_end_date) effective_end_date
446 FROM per_all_assignments_f paaf
447 WHERE paaf.business_group_id = p_business_group_id
448 AND paaf.assignment_id = p_assignment_id
449 AND paaf.assignment_status_type_id = 1;
450
451 l_asg_trem_date DATE;
452 l_asg_status csr_asg % rowtype;
453
454 BEGIN
455
456 OPEN csr_asg;
457 FETCH csr_asg
458 INTO l_asg_status;
459 CLOSE csr_asg;
460 l_asg_trem_date := l_asg_status.effective_end_date;
461 RETURN l_asg_trem_date;
462
463 END get_assg_term_date;
464
465 /*Function to get paternity absence details*/
466 FUNCTION get_pat_abs_details
467 (
468 p_abs_attendance_id IN NUMBER,
469 p_override_weeks OUT NOCOPY NUMBER,
470 p_holiday_accrual OUT NOCOPY VARCHAR2
471 ) Return NUMBER IS
472
473 CURSOR get_abs_details IS
474 SELECT
475 ABS.abs_information7
476 ,ABS.abs_information5
477 FROM per_absence_attendances ABS
478 WHERE ABS.absence_attendance_id = p_abs_attendance_id;
482
479 l_abs_details get_abs_details%ROWTYPE ;
480
481 BEGIN
483 OPEN get_abs_details;
484 FETCH get_abs_details INTO l_abs_details;
485 CLOSE get_abs_details;
486
487 p_override_weeks := nvl(l_abs_details.abs_information7,-999) ;
488 p_holiday_accrual := l_abs_details.abs_information5;
489
490 RETURN 1 ;
491 EXCEPTION WHEN OTHERS THEN
492 raise_application_error(-20001,SQLERRM);
493 END get_pat_abs_details;
494
495 FUNCTION get_paternity_override
496 (p_assignment_id IN NUMBER
497 ,p_effective_date IN DATE
498 ,p_abs_start_date IN DATE
499 ,p_abs_end_date IN DATE
500 ,p_duration_override IN OUT NOCOPY NUMBER
501 ,p_holiday_override OUT NOCOPY NUMBER
502 ) RETURN NUMBER IS
503 --
504 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
505 SELECT ee.element_entry_id element_entry_id
506 , eev1.screen_entry_value screen_entry_value
507 , iv1.name
508 FROM per_all_assignments_f asg1
509 ,per_all_assignments_f asg2
510 ,per_all_people_f per
511 ,pay_element_links_f el
512 ,pay_element_types_f et
513 ,pay_input_values_f iv1
514 ,pay_element_entries_f ee
515 ,pay_element_entry_values_f eev1
516 WHERE asg1.assignment_id = p_assignment_id
517 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
518 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
519 AND per.person_id = asg1.person_id
520 AND asg2.person_id = per.person_id
521 AND et.element_name = 'Paternity Override'
522 AND et.legislation_code = 'DK'
523 AND iv1.element_type_id = et.element_type_id
524 AND iv1.name in ('Start Date', 'End Date','Duration Override Weeks'
525 ,'Holiday Accrual to Supress')
526 AND el.business_group_id = per.business_group_id
527 AND el.element_type_id = et.element_type_id
528 AND ee.assignment_id = asg2.assignment_id
529 AND ee.element_link_id = el.element_link_id
530 AND eev1.element_entry_id = ee.element_entry_id
531 AND eev1.input_value_id = iv1.input_value_id
532 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
533 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
534 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
535 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
536 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
537 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
538 ORDER BY ee.element_entry_id;
539 --
540 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
541 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
542 eename pay_input_values_f.name%TYPE );
543 l_rec l_record;
544 TYPE l_table is table of l_record index by BINARY_INTEGER;
545 l_tab l_table;
546 l_start_date date;
547 l_end_date date;
548 l_counter number ;
549 l_bool_match boolean;
550 l_num_match number;
551 l_check_nomatch number;
552 l_start_time NUMBER;
553 l_end_time NUMBER;
554 l_duration_override NUMBER;
555 l_shared_mat_allowance_used NUMBER;
556 l_shared_adopt_allowance_used NUMBER;
557 l_holiday_override NUMBER;
558 --
559 BEGIN
560 --
561 l_counter := 1;
562 l_bool_match := FALSE;
563 l_check_nomatch := 0;
564 --p_shared_duration := 0;
565
566 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
567 OPEN get_details(p_assignment_id , p_effective_date );
568 -- Assign the values to a table type
569 FETCH get_details BULK COLLECT INTO l_tab;
570 CLOSE get_details;
571 -- Loop through each values for processing.
572 FOR l_cur in 1..l_tab.count LOOP
573 -- Assign values to local variables.
574 case l_tab(l_cur).eename
575 when 'Start Date' then l_start_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
576 when 'End Date' then l_end_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
577 when 'Duration Override Weeks' then l_duration_override :=l_tab(l_cur).eevalue;
578 when 'Holiday Accrual to Supress' then l_holiday_override :=l_tab(l_cur).eevalue;
579 end case;
580 -- Check no. of input values of override element is 12
581 IF l_counter < 4 then
582 l_counter := l_counter + 1;
583 else
584 -- Check override element's start and end date matches with Absent element.
585 if l_start_date = p_abs_start_date then
586 -- Multiple entry exists with same start and end date
587 IF l_bool_match THEN
588 --p_shared_duration := 0;
589
590 return -1;
591 -- Exact match found
592 ELSE
593 l_bool_match := True;
594 END IF;
595 -- Assign input values to output variables.
596 p_duration_override := nvl(l_duration_override,p_duration_override);
597 p_holiday_override := l_holiday_override;
598
599 end if;
600 l_counter := 1;
601 END if;
605
602 END LOOP;
603
604
606 RETURN 0;
607 END get_paternity_override;
608
609 FUNCTION get_adopt_abs_details
610 (p_assignment_id IN NUMBER
611 ,p_date_earned IN DATE
612 ,p_abs_attendance_id IN NUMBER
613 ,p_expected_dob OUT NOCOPY DATE
614 ,p_actual_dob OUT NOCOPY DATE
615 ,p_pre_adopt_duration OUT NOCOPY NUMBER
616 ,p_post_adopt_duration OUT NOCOPY NUMBER
617 ,p_adopt_weeks_transfer OUT NOCOPY NUMBER
618 ,p_weeks_from_mother OUT NOCOPY NUMBER
619 ,p_sex OUT NOCOPY VARCHAR2
620 ,p_holiday_accrual OUT NOCOPY VARCHAR2
621 ) Return NUMBER is
622 CURSOR csr_absence IS SELECT
623 fnd_date.canonical_to_date(abs_information4) expected_dob,
624 fnd_date.canonical_to_date(abs_information5) actual_dob,
625 abs_information8 pre_adopt_duration,
626 abs_information9 post_adopt_duration,
627 abs_information10 adoption_weeks_transfer,
628 abs_information11 weeks_from_mother,
629 abs_information6 hol_accrual_elig
630 FROM PER_ABSENCE_ATTENDANCES PAA
631 WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
632
633 CURSOR csr_person_details IS
634 SELECT papf.sex FROM per_all_people_f papf,per_all_assignments_f paaf
635 WHERE
636 paaf.assignment_id = p_assignment_id
637 AND p_date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date
638 AND papf.person_id = paaf.person_id
639 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date;
640 Begin
641 FOR csr_abs_details in csr_absence
642 Loop
643 p_expected_dob := csr_abs_details.expected_dob;
644 p_actual_dob := csr_abs_details.actual_dob;
645 p_pre_adopt_duration := csr_abs_details.pre_adopt_duration;
646 p_post_adopt_duration := csr_abs_details.post_adopt_duration;
647 p_adopt_weeks_transfer := nvl(csr_abs_details.adoption_weeks_transfer,0);
648 p_weeks_from_mother := nvl(csr_abs_details.weeks_from_mother,0);
649 p_holiday_accrual := csr_abs_details.hol_accrual_elig;
650 End Loop;
651
652 OPEN csr_person_details;
653 FETCH csr_person_details INTO p_sex;
654 CLOSE csr_person_details;
655
656 RETURN 1 ;
657 EXCEPTION WHEN OTHERS THEN
658 raise_application_error(-20001,SQLERRM);
659 End get_adopt_abs_details;
660
661
662 FUNCTION get_adopt_override_details
663 (p_assignment_id IN NUMBER
664 ,p_effective_date IN DATE
665 ,p_abs_start_date IN DATE
666 ,p_abs_end_date IN DATE
667 ,p_pre_adopt_duration IN OUT NOCOPY NUMBER
668 ,p_post_adopt_duration IN OUT NOCOPY NUMBER
669 ,p_adoption_allowance_used OUT NOCOPY NUMBER
670 ,p_shared_allowance_used OUT NOCOPY NUMBER
671 ,p_holiday_override OUT NOCOPY NUMBER
672 ) RETURN NUMBER IS
673 --
674 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
675 SELECT ee.element_entry_id element_entry_id
676 , eev1.screen_entry_value screen_entry_value
677 , iv1.name
678 FROM per_all_assignments_f asg1
679 ,per_all_assignments_f asg2
680 ,per_all_people_f per
681 ,pay_element_links_f el
682 ,pay_element_types_f et
683 ,pay_input_values_f iv1
684 ,pay_element_entries_f ee
685 ,pay_element_entry_values_f eev1
686 WHERE asg1.assignment_id = p_assignment_id
687 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
688 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
689 AND per.person_id = asg1.person_id
690 AND asg2.person_id = per.person_id
691 AND et.element_name = 'Adoption Override'
692 AND et.legislation_code = 'DK'
693 AND iv1.element_type_id = et.element_type_id
694 AND iv1.name in ('Start Date', 'End Date','Payment During Leave'
695 ,'Pre Adopt Duration Override','Post Adopt Duration Override','Adoption Weeks Used','Shared Adoption Weeks Used','Holiday Accrual to Supress')
696 AND el.business_group_id = per.business_group_id
697 AND el.element_type_id = et.element_type_id
698 AND ee.assignment_id = asg2.assignment_id
699 AND ee.element_link_id = el.element_link_id
700 AND eev1.element_entry_id = ee.element_entry_id
701 AND eev1.input_value_id = iv1.input_value_id
702 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
703 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
704 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
705 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
706 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
707 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
708 ORDER BY ee.element_entry_id;
709 --
710 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
711 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
712 eename pay_input_values_f.name%TYPE );
713 l_rec l_record;
714 TYPE l_table is table of l_record index by BINARY_INTEGER;
715 l_tab l_table;
719 l_bool_match boolean;
716 l_start_date date;
717 l_end_date date;
718 l_counter number ;
720 l_num_match number;
721 l_check_nomatch number;
722 l_start_time NUMBER;
723 l_end_time NUMBER;
724 l_pay_during_leave VARCHAR2(40);
725 l_pre_adopt_duration NUMBER;
726 l_post_adopt_duration NUMBER;
727 l_over_ride_frequency NUMBER;
728 l_adoption_allowance_used NUMBER;
729 l_shared_allowance_used NUMBER;
730 l_holiday_override NUMBER;
731 --
732 BEGIN
733 --
734 l_counter := 1;
735 l_bool_match := FALSE;
736 l_check_nomatch := 0;
737 p_adoption_allowance_used := 0;
738 p_shared_allowance_used := 0;
739 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
740 OPEN get_details(p_assignment_id , p_effective_date );
741 -- Assign the values to a table type
742 FETCH get_details BULK COLLECT INTO l_tab;
743 CLOSE get_details;
744 -- Loop through each values for processing.
745 FOR l_cur in 1..l_tab.count LOOP
746 -- Assign values to local variables.
747 case l_tab(l_cur).eename
748 when 'Start Date' then l_start_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
749 when 'End Date' then l_end_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
750 when 'Payment During Leave' then l_pay_during_leave :=l_tab(l_cur).eevalue;
751 when 'Pre Adopt Duration Override' then l_pre_adopt_duration :=l_tab(l_cur).eevalue;
752 when 'Post Adopt Duration Override' then l_post_adopt_duration :=l_tab(l_cur).eevalue;
753 when 'Adoption Weeks Used' then l_adoption_allowance_used :=l_tab(l_cur).eevalue;
754 when 'Shared Adoption Weeks Used' then l_shared_allowance_used :=l_tab(l_cur).eevalue;
755 when 'Holiday Accrual to Supress' then l_holiday_override :=l_tab(l_cur).eevalue;
756 end case;
757 -- Check no. of input values of override element is 12
758 IF l_counter < 8 then
759 l_counter := l_counter + 1;
760 else
761 -- Check override element's start and end date matches with Absent element.
762 if l_start_date = p_abs_start_date then
763 -- Multiple entry exists with same start and end date
764 IF l_bool_match THEN
765 --p_pre_adopt_duration := null;
766 --p_post_adopt_duration := null;
767 p_adoption_allowance_used := 0;
768 p_shared_allowance_used := 0;
769 return -1;
770 -- Exact match found
771 ELSE
772 l_bool_match := True;
773 END IF;
774 -- Assign input values to output variables.
775 p_pre_adopt_duration := nvl(l_pre_adopt_duration,p_pre_adopt_duration) ;
776 p_post_adopt_duration := nvl(l_post_adopt_duration,p_post_adopt_duration);
777 p_adoption_allowance_used := nvl(l_adoption_allowance_used,0);
778 p_shared_allowance_used := nvl(l_shared_allowance_used,0);
779 p_holiday_override := l_holiday_override;
780 end if;
781 l_counter := 1;
782 END if;
783 END LOOP;
784 RETURN 0;
785 EXCEPTION WHEN OTHERS THEN
786 raise_application_error(-20001,SQLERRM);
787 END get_adopt_override_details;
788
789 FUNCTION get_parental_details
790 (p_abs_attendance_id IN NUMBER
791 ,p_actual_dob OUT NOCOPY DATE
792 ,p_duration_override OUT NOCOPY NUMBER
793 ,p_parental_type OUT NOCOPY VARCHAR2
794 ,p_holiday_accrual OUT NOCOPY VARCHAR2
795 ) Return varchar2 is
796 CURSOR csr_absence IS SELECT
797 fnd_date.canonical_to_date(abs_information4) actual_dob,
798 abs_information7 duration_override,
799 abs_information10 parental_type,
800 abs_information5 holiday_accrual
801 FROM PER_ABSENCE_ATTENDANCES PAA
802 WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
803 l_count VARCHAR2(2):='N';
804 Begin
805 FOR csr_abs_details in csr_absence
806 Loop
807 p_actual_dob := csr_abs_details.actual_dob;
808 p_duration_override := csr_abs_details.duration_override;
809 p_parental_type := csr_abs_details.parental_type;
810 p_holiday_accrual := csr_abs_details.holiday_accrual;
811 l_count := 'Y';
812 End Loop;
813 return l_count;
814 End get_parental_details;
815
816 FUNCTION get_parental_override
817 (p_assignment_id IN NUMBER
818 ,p_effective_date IN DATE
819 ,p_abs_start_date IN DATE
820 ,p_abs_end_date IN DATE
821 ,p_shared_duration IN OUT NOCOPY NUMBER
822 ,p_shared_mat_allowance_used OUT NOCOPY NUMBER
823 ,p_shared_adopt_allowance_used OUT NOCOPY NUMBER
824 ,p_holiday_override OUT NOCOPY NUMBER
825 ) RETURN NUMBER IS
826 --
827 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
828 SELECT ee.element_entry_id element_entry_id
829 , eev1.screen_entry_value screen_entry_value
830 , iv1.name
831 FROM per_all_assignments_f asg1
832 ,per_all_assignments_f asg2
833 ,per_all_people_f per
834 ,pay_element_links_f el
835 ,pay_element_types_f et
836 ,pay_input_values_f iv1
840 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
837 ,pay_element_entries_f ee
838 ,pay_element_entry_values_f eev1
839 WHERE asg1.assignment_id = p_assignment_id
841 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
842 AND per.person_id = asg1.person_id
843 AND asg2.person_id = per.person_id
844 AND et.element_name = 'Parental Leave Override'
845 AND et.legislation_code = 'DK'
846 AND iv1.element_type_id = et.element_type_id
847 AND iv1.name in ('Start Date', 'End Date','Duration Override'
848 ,'Shared Maternity Weeks Used','Shared Adoption Weeks Used','Holiday Accrual to Supress')
849 AND el.business_group_id = per.business_group_id
850 AND el.element_type_id = et.element_type_id
851 AND ee.assignment_id = asg2.assignment_id
852 AND ee.element_link_id = el.element_link_id
853 AND eev1.element_entry_id = ee.element_entry_id
854 AND eev1.input_value_id = iv1.input_value_id
855 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
856 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
857 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
858 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
859 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
860 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
861 ORDER BY ee.element_entry_id;
862 --
863 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
864 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
865 eename pay_input_values_f.name%TYPE );
866 l_rec l_record;
867 TYPE l_table is table of l_record index by BINARY_INTEGER;
868 l_tab l_table;
869 l_start_date date;
870 l_end_date date;
871 l_counter number ;
872 l_bool_match boolean;
873 l_num_match number;
874 l_check_nomatch number;
875 l_start_time NUMBER;
876 l_end_time NUMBER;
877 l_duration_override NUMBER;
878 l_shared_mat_allowance_used NUMBER;
879 l_shared_adopt_allowance_used NUMBER;
880 l_holiday_override NUMBER;
881 --
882 BEGIN
883 --
884 l_counter := 1;
885 l_bool_match := FALSE;
886 l_check_nomatch := 0;
887 --p_shared_duration := 0;
888 p_shared_mat_allowance_used := 0;
889 p_shared_adopt_allowance_used :=0;
890 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
891 OPEN get_details(p_assignment_id , p_effective_date );
892 -- Assign the values to a table type
893 FETCH get_details BULK COLLECT INTO l_tab;
894 CLOSE get_details;
895 -- Loop through each values for processing.
896 FOR l_cur in 1..l_tab.count LOOP
897 -- Assign values to local variables.
898 case l_tab(l_cur).eename
899 when 'Start Date' then l_start_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
900 when 'End Date' then l_end_date :=to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
901 when 'Duration Override' then l_duration_override :=l_tab(l_cur).eevalue;
902 when 'Shared Maternity Weeks Used' then l_shared_mat_allowance_used :=l_tab(l_cur).eevalue;
903 when 'Shared Adoption Weeks Used' then l_shared_adopt_allowance_used :=l_tab(l_cur).eevalue;
904 when 'Holiday Accrual to Supress' then l_holiday_override :=l_tab(l_cur).eevalue;
905 end case;
906 -- Check no. of input values of override element is 12
907 IF l_counter < 6 then
908 l_counter := l_counter + 1;
909 else
910 -- Check override element's start and end date matches with Absent element.
911 if l_start_date = p_abs_start_date then
912 -- Multiple entry exists with same start and end date
913 IF l_bool_match THEN
914 --p_shared_duration := 0;
915 p_shared_mat_allowance_used := 0;
916 p_shared_adopt_allowance_used := 0;
917
918 return -1;
919 -- Exact match found
920 ELSE
921 l_bool_match := True;
922 END IF;
923 -- Assign input values to output variables.
924 p_shared_duration := nvl(l_duration_override,p_shared_duration);
925 p_holiday_override := l_holiday_override;
926 p_shared_mat_allowance_used := nvl(l_shared_mat_allowance_used,0);
927 p_shared_adopt_allowance_used := nvl(l_shared_adopt_allowance_used,0);
928 end if;
929 l_counter := 1;
930 END if;
931 END LOOP;
932
933
934
935 RETURN 0;
936 END get_parental_override;
937
938 /* Added for Holiday Accrual impact of Maternity and related absences */
939
940 /* Function to get the effective working days based on work pattern between two given dates*/
941 FUNCTION get_wrk_days_hol_accr
942 (p_wrk_pattern IN VARCHAR2
943 ,p_hrs_in_day IN NUMBER
944 ,p_abs_start_date IN DATE
945 ,p_abs_end_date IN DATE
946 ,p_abs_start_time IN VARCHAR2
950 l_time_diff NUMBER;
947 ,p_abs_end_time IN VARCHAR2
948 ) RETURN NUMBER IS
949
951 l_days_diff NUMBER;
952 l_rem_num_diff NUMBER;
953 l_start_day VARCHAR2(10);
954 l_end_day VARCHAR2(10);
955 l_start_day_num NUMBER;
956 l_end_day_num NUMBER;
957 l_no_weeks NUMBER;
958 l_count_weekend NUMBER;
959
960
961 BEGIN
962
963 l_time_diff := 0;
964 l_days_diff := 0;
965 l_rem_num_diff := 0;
966 l_start_day := ' ';
967 l_end_day := ' ';
968 l_start_day_num := 0;
969 l_end_day_num := 0;
970 l_no_weeks := 0;
971 l_count_weekend := 0;
972
973 l_days_diff := p_abs_end_date - p_abs_start_date + 1;
974
975 /* Calculate the number of whole weeks involved */
976 l_no_weeks := TRUNC(l_days_diff/7);
977
978 /* 5 Day pattern means Saturday and Sunday off */
979 /* 5 Day pattern means Sunday off */
980
981 IF(p_wrk_pattern ='5DAY') THEN
982 l_count_weekend := l_no_weeks*2;
983 ELSIF(p_wrk_pattern ='6DAY') THEN
984 l_count_weekend := l_no_weeks;
985 END IF;
986
987 /* Count the number of weekends between the remaining days */
988 l_start_day := to_char(p_abs_start_date,'DY','NLS_DATE_LANGUAGE = English');
989 l_end_day := to_char(p_abs_end_date,'DY','NLS_DATE_LANGUAGE = English');
990
991 l_start_day_num := conv_day_to_num(l_start_day);
992 l_end_day_num := conv_day_to_num(l_end_day);
993
994 l_rem_num_diff := l_end_day_num - l_start_day_num;
995
996 /* If diff is -1 means one week covered*/
997 IF (l_rem_num_diff <-1 ) THEN
998 IF(p_wrk_pattern ='5DAY') THEN
999 IF(l_start_day_num = 7) THEN
1000 l_count_weekend := l_count_weekend + 1;
1001 ELSE
1002 l_count_weekend := l_count_weekend + 2;
1003 END IF;
1004 ELSIF(p_wrk_pattern ='6DAY') THEN
1005 l_count_weekend := l_count_weekend + 1;
1006 END IF;
1007 ELSE IF( l_rem_num_diff > 0 AND l_end_day_num = 6 ) THEN
1008 IF(p_wrk_pattern ='5DAY') THEN
1009 l_count_weekend := l_count_weekend + 1;
1010 ELSIF(p_wrk_pattern ='6DAY') THEN
1011 l_count_weekend := l_count_weekend + 0;
1012 END IF;
1013 ELSE IF ( l_rem_num_diff > 0 AND l_end_day_num = 7) THEN
1014 IF(p_wrk_pattern ='5DAY') THEN
1015 IF(l_start_day_num = 7) THEN
1016 l_count_weekend := l_count_weekend + 1 ;
1017 ELSE
1018 l_count_weekend := l_count_weekend + 2 ;
1019 END IF;
1020 ELSIF(p_wrk_pattern ='6DAY') THEN
1021 IF(l_start_day_num = 7) THEN
1022 l_count_weekend := l_count_weekend + 1 ;
1023 ELSE
1024 l_count_weekend := l_count_weekend + 1 ;
1025 END IF;
1026 END IF;
1027 ELSIF (l_rem_num_diff = 0 ) THEN
1028 IF(l_end_day_num IN(6,7)) THEN
1029 l_count_weekend := l_count_weekend + 1 ;
1030 END IF;
1031 END IF;
1032 END IF;
1033 END IF;
1034
1035
1036 RETURN (l_days_diff - l_count_weekend);
1037
1038 END get_wrk_days_hol_accr;
1039
1040 /* Function to convert the day of the week to a number to be used in logic processing */
1041 /* Cannot rely on to_char with format as 'D' to return the same value as dependent on NLS_TERRITORY */
1042 FUNCTION conv_day_to_num( p_day VARCHAR2) RETURN NUMBER IS
1043 l_return NUMBER;
1044 BEGIN
1045
1046 IF (p_day ='MON') THEN
1047 l_return := 1;
1048 ELSIF (p_day ='TUE') THEN
1049 l_return := 2;
1050 ELSIF (p_day ='WED') THEN
1051 l_return := 3;
1052 ELSIF (p_day ='THU') THEN
1053 l_return := 4;
1054 ELSIF (p_day ='FRI') THEN
1055 l_return := 5;
1056 ELSIF (p_day ='SAT') THEN
1057 l_return := 6;
1058 ELSIF (p_day ='SUN') THEN
1059 l_return := 7;
1060 END IF;
1061
1062 RETURN l_return;
1063
1064 END conv_day_to_num;
1065
1066 /* Function to get Part Time Maternity Details */
1067 FUNCTION get_ptm_abs_details
1068 (p_abs_attendance_id IN NUMBER
1069 ,p_actual_dob OUT NOCOPY DATE
1070 ,p_part_time_hours OUT NOCOPY NUMBER
1071 ,p_part_time_hrs_freq OUT NOCOPY VARCHAR2
1072 ,p_holiday_accrual OUT NOCOPY VARCHAR2
1073 ,p_payment_during_leave OUT NOCOPY VARCHAR2 /* 11694807 */
1074 ) Return varchar2 is
1075 CURSOR csr_absence IS SELECT
1076 fnd_date.canonical_to_date(abs_information4) actual_dob,
1077 fnd_number.canonical_to_number(abs_information7) part_time_hours,
1078 abs_information8 part_time_hrs_freq,
1079 abs_information5 holiday_accrual
1080 ,abs_information6 payment_during_leave -- 11694807
1081 FROM PER_ABSENCE_ATTENDANCES PAA
1082 WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
1083 l_count VARCHAR2(2):='N';
1084 Begin
1085 FOR csr_abs_details in csr_absence
1086 Loop
1087 p_actual_dob := csr_abs_details.actual_dob;
1088 p_part_time_hours := csr_abs_details.part_time_hours;
1089 p_part_time_hrs_freq := csr_abs_details.part_time_hrs_freq;
1090 p_holiday_accrual := csr_abs_details.holiday_accrual;
1091 p_payment_during_leave:= csr_abs_details.payment_during_leave; -- 11694807
1092 l_count := 'Y';
1093 End Loop;
1094 return l_count;
1095 End get_ptm_abs_details;
1096
1097 /* End of Function to get Part Time Maternity Details */
1098 /* Function to Get worked Hrs for PTM*/
1099 Function get_part_time_worked_hrs
1100 (p_assignment_id IN NUMBER
1101 ,p_date_earned IN DATE
1102 ,p_abs_start_date IN DATE
1103 ,p_abs_end_date IN DATE
1104 ,p_start_time IN VARCHAR2
1105 ,p_end_time IN VARCHAR2
1106 ,p_worked_hours OUT NOCOPY NUMBER
1107 ,p_weekly_worked_days OUT NOCOPY NUMBER
1108 ) return Varchar2 is
1109
1110 CURSOR csr_asg_le IS SELECT
1111 fnd_number.canonical_to_number(nvl(asg.normal_hours,hoi.org_information3)) hours
1112 ,nvl(asg.frequency,hoi.org_information4) frequency
1113 ,segment10 work_pattern
1114 FROM
1115 hr_organization_information hoi
1116 ,HR_SOFT_CODING_KEYFLEX SCL
1117 ,PER_ALL_ASSIGNMENTS_F ASG
1118 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
1119 AND hoi.organization_id = scl.segment1
1120 AND org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
1121 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
1122 AND p_date_earned BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE;
1123
1124 l_count NUMBER;
1125 l_abs_start date;
1126 l_abs_end date;
1127 l_abs_hours_returned NUMBER;
1128 l_hours NUMBER;
1129 l_hours_rate NUMBER;
1130 l_mul_factor NUMBER;
1131 l_work_pattern Varchar2(40);
1132 l_freq VARCHAR2(3);
1133 Begin
1134
1135 l_abs_start:= p_abs_start_date - to_number(to_char(p_abs_start_date,'D'))+1;
1136 l_abs_end := l_abs_start+6;
1137
1138 hr_utility.trace(' l_abs_start : '||l_abs_start||' l_abs_end :'||l_abs_end);
1139
1140 l_count := hr_loc_work_schedule.calc_sch_based_dur(p_assignment_id,'H','N',l_abs_start,l_abs_end,'00','00',l_abs_hours_returned);
1141 l_count := hr_loc_work_schedule.calc_sch_based_dur(p_assignment_id,'D','N',l_abs_start,l_abs_end,'00','00',p_weekly_worked_days);
1142
1143 FOR csr_asg_le_details in csr_asg_le
1144 Loop
1145 l_hours_rate:= csr_asg_le_details.hours;
1146 l_freq:= csr_asg_le_details.frequency;
1147 l_work_pattern := csr_asg_le_details.work_pattern;
1148 End Loop;
1149
1150 If l_abs_hours_returned = 0 then
1151 If l_hours_rate is not null and l_freq is not null then
1152
1153 IF(l_freq = 'D') THEN
1154 l_mul_factor:= 5;
1155 ELSIF (l_freq = 'W') THEN
1156 l_mul_factor := 1;
1157 ELSIF (l_freq = 'M') THEN
1158 l_mul_factor := 5/22;
1159 ELSIF (l_freq = 'Y') THEN
1160 l_mul_factor :=5/260;
1161 End if;
1162
1163 /* Prorate Employee Hours for new starters and leavers in the period */
1164 l_hours := ROUND(l_hours_rate * l_mul_factor,2);
1165 Else
1166 p_worked_hours := 0;
1167 return 'N';
1168 End if;
1169
1170 Else
1171 l_hours := l_abs_hours_returned;
1172 End if;
1173 p_worked_hours := l_hours;
1174 return 'Y';
1175 End get_part_time_worked_hrs;
1176 /* End of Function to Get worked Hrs for PTM*/
1177
1178 END PAY_DK_ABSENCE_USER;
[Home] [Help]