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