[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ABSENCE_USER
Source
1 PACKAGE BODY PAY_SE_ABSENCE_USER AS
2 /*$Header: pyseabsence.pkb 120.1 2007/06/28 12:47:02 rravi 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-4,6 - Output variable holds element input value name.
17 -- P_INPUT_VALUE 1-4,6 - Output variable holds element input value.
18 -- USES
19 -- NOTES
20
21 -------------------------------------------------------------------------------------------------------------------------
22 Function Element_populate(p_assignment_id in number,
23 p_person_id in number,
24 p_absence_attendance_id in number,
25 p_element_type_id in number,
26 p_absence_category in varchar2,
27 p_original_entry_id OUT NOCOPY NUMBER,
28 p_input_value_name1 OUT NOCOPY VARCHAR2,
29 p_input_value1 OUT NOCOPY VARCHAR2,
30 p_input_value_name2 OUT NOCOPY VARCHAR2,
31 p_input_value2 OUT NOCOPY VARCHAR2,
32 p_input_value_name3 OUT NOCOPY VARCHAR2,
33 p_input_value3 OUT NOCOPY VARCHAR2,
34 p_input_value_name4 OUT NOCOPY VARCHAR2,
35 p_input_value4 OUT NOCOPY VARCHAR2,
36 p_input_value_name5 OUT NOCOPY VARCHAR2,
37 p_input_value5 OUT NOCOPY VARCHAR2,
38 p_input_value_name6 OUT NOCOPY VARCHAR2,
39 p_input_value6 OUT NOCOPY VARCHAR2,
40 p_input_value_name7 OUT NOCOPY VARCHAR2,
41 p_input_value7 OUT NOCOPY VARCHAR2,
42 p_input_value_name8 OUT NOCOPY VARCHAR2,
43 p_input_value8 OUT NOCOPY VARCHAR2,
44 p_input_value_name9 OUT NOCOPY VARCHAR2,
45 p_input_value9 OUT NOCOPY VARCHAR2,
46 p_input_value_name10 OUT NOCOPY VARCHAR2,
47 p_input_value10 OUT NOCOPY VARCHAR2,
48 p_input_value_name11 OUT NOCOPY VARCHAR2,
49 p_input_value11 OUT NOCOPY VARCHAR2,
50 p_input_value_name12 OUT NOCOPY VARCHAR2,
51 p_input_value12 OUT NOCOPY VARCHAR2,
52 p_input_value_name13 OUT NOCOPY VARCHAR2,
53 p_input_value13 OUT NOCOPY VARCHAR2,
54 p_input_value_name14 OUT NOCOPY VARCHAR2,
55 p_input_value14 OUT NOCOPY VARCHAR2,
56 p_input_value_name15 OUT NOCOPY VARCHAR2,
57 p_input_value15 OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
58
59 l_start_date date;
60 l_end_date date;
61 l_days number;
62 l_hours number;
63 l_absent_reason number;
64 l_start_time varchar2(20);
65 l_end_time varchar2(20);
66 l_abs_cat_meaning varchar2(100);
67 l_start_time_char varchar2(5) := '00:00';
68 l_end_time_char varchar2(5) := '23:59';
69 l_Absence_Percentage Number(10,2);
70 l_wrk_start_date date;
71
72 l_absence_start_date date;
73 l_absence_end_date date;
74
75 l_weekends number;
76 l_public_holidays number;
77 l_Total_absence_days number;
78
79 BEGIN
80
81
82
83
84 -- Fetch absence attendance details
85 BEGIN
86 SELECT abs.date_start
87 ,abs.date_end
88 ,abs.ABS_ATTENDANCE_REASON_ID
89 -- ,abs.TIME_START
90 -- ,abs.TIME_END
91 ,abs.absence_Days
92 ,abs.absence_Hours
93 INTO l_start_date
94 ,l_end_date
95 ,l_absent_reason
96 -- ,l_start_time
97 -- ,l_end_time
98 ,l_days
99 ,l_hours
100 FROM per_absence_attendances abs
101 WHERE abs.absence_attendance_id = p_absence_attendance_id;
102 EXCEPTION
103 WHEN OTHERS THEN
104 NULL;
105 END;
106 -- Check if absence category is S ( Sickness )
107 IF p_absence_category in ('S') THEN
108 p_input_value_name1 := 'Start Date';
109 p_input_value1 := l_start_date;
110 p_input_value_name2 := 'End Date';
111 p_input_value2 := l_end_date;
112 p_input_value_name3 := 'Entitlement Days';
113 p_input_value3 := l_days;
114 p_input_value_name4 := 'Hours';
115 p_input_value4 := l_hours;
116 --p_input_value_name5 := 'Absence Category';
117
118 -- To select absence category meaning by passing code
119 BEGIN
120 SELECT MEANING
121 INTO l_abs_cat_meaning
122 FROM hr_lookups
123 WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
124 AND ENABLED_FLAG = 'Y'
125 AND LOOKUP_CODE = p_absence_category;
126 EXCEPTION
127 WHEN OTHERS THEN
128 l_abs_cat_meaning := null;
129 END;
130 p_input_value_name5 := 'Absence Category';
131 p_input_value5 := l_abs_cat_meaning;
132
133 --p_input_value5 := l_abs_cat_meaning;
134 p_input_value_name6 := 'CREATOR_ID';
135 p_input_value6 := p_absence_attendance_id;
136
137 SELECT fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) INTO
138 l_absence_percentage
139 FROM PER_ABSENCE_ATTENDANCES PAA
140 WHERE PAA.ABSENCE_ATTENDANCE_ID =p_absence_attendance_id;
141
142 p_input_value_name7 := 'Absence Percentage';
143 p_input_value7 := l_absence_percentage;
144
145
146 /*GET_WEEKEND_PUBLIC_HOLIDAYS(p_assignment_id
147 ,fnd_date.date_to_chardate(p_input_value1)
148 ,fnd_date.date_to_chardate(p_input_value2)
149 ,nvl(l_start_time,l_start_time_char)
150 ,nvl(l_end_time,l_end_time_char)
151 ,l_weekends
152 ,l_public_holidays
153 ,l_Total_absence_days);
154
155 p_input_value3 := l_Total_absence_days;
156 p_input_value7 := l_Total_absence_days-(l_weekends+l_public_holidays);*/
157 END IF;
158
159 -- Return Y indicating to process the element for the input assignment id.
160 RETURN 'Y';
161 END Element_populate;
162
163 PROCEDURE GET_WEEKEND_PUBLIC_HOLIDAYS(p_assignment_id in number
164 ,P_START_DATE in varchar2
165 ,P_END_DATE in varchar2
166 ,p_start_time in varchar2
167 ,p_end_time in varchar2
168 ,p_weekends OUT NOCOPY NUMBER
169 ,p_public_holidays OUT NOCOPY NUMBER
170 ,p_Total_holidays OUT NOCOPY NUMBER
171 ) IS
172
173 l_return_frm_wrk_schd number;
174
175 l_DAYS_WTH_PUBLIC NUMBER;
176 l_DAYS_WTHOUT_PUBLIC NUMBER;
177
178 l_TOTAL_DAYS number;
179 l_CURRENT_PUBLIC_HOLIDAYS NUMBER;
180 l_CURRENT_WEEKENDS NUMBER;
181 l_start_date date;
182 l_end_date date;
183
184 CURSOR get_total_days(csr_end_date date,csr_start_date date)
185 is
186 select floor(csr_end_date-csr_start_date) from dual;
187 -- select (TO_DATE(P_END_DATE) ||' '||replace(trim(to_char(p_end_time,'00.00')),'.',':'),'DD-MM-YYYY HH24:MI')) from dual;
188
189 -- select floor(TO_DATE(TO_DATE(P_END_DATE) ||' '||replace(trim(to_char(p_end_time,'00.00')),'.',':'),'DD-MM-YYYY HH24:MI')-
190 --TO_DATE(TO_DATE(P_START_DATE) ||' '||replace(trim(to_char(p_start_time,'00.00')),'.',':'),'DD-MM-YYYY HH24:MI')) from dual;
191
192 CURSOR get_time_format(l_time varchar2) is
193 SELECT replace(trim(l_time),':','.') FROM dual;
194
195 l_start_time VARCHAR2(5);
196 l_end_time VARCHAR2(5);
197
198 BEGIN
199
200 -- Get Total days including Public Holidays including Weekends
201 --l_TOTAL_DAYS := 5.5;
202 --l_TOTAL_DAYS := to_number(floor(TO_DATE(TO_DATE(P_END_DATE) ||' '||to_char(p_end_time),'DD-MM-YYYY HH24:MI')- TO_DATE(TO_DATE(P_START_DATE) ||' '||to_char(p_start_time),'DD-MM-YYYY HH24:MI')));
203 OPEN get_time_format(p_start_time);
204 FETCH get_time_format INTO l_start_time;
205 CLOSE get_time_format;
206
207 OPEN get_time_format(p_end_time);
208 FETCH get_time_format INTO l_end_time;
209 CLOSE get_time_format;
210
211 l_start_date := TO_DATE(TO_CHAR(TO_DATE(P_START_DATE,'DD-MON-YYYY'),'DD-MM-YYYY')||' '||l_start_time,'DD-MM-YYYY HH24:MI');
212 l_end_date := TO_DATE(TO_CHAR(TO_DATE(p_end_date,'DD-MON-YYYY'),'DD-MM-YYYY')||' '||l_end_time,'DD-MM-YYYY HH24:MI');
213
214 OPEN get_total_days(l_end_date,l_start_date);
215 FETCH get_total_days INTO l_TOTAL_DAYS;
216 CLOSE get_total_days;
217
218 -- Get Total days including Public Holidays exculding Weekends
219 l_return_frm_wrk_schd := hr_loc_work_schedule.calc_sch_based_dur
220 ( p_assignment_id,
221 'D',
222 'Y',
223 P_START_DATE,
224 P_END_DATE,
225 l_start_time,
226 l_end_time,
227 l_DAYS_WTH_PUBLIC
228 );
229
230 -- Get Total days Excluding Public Holidays exculding Weekends
231 l_return_frm_wrk_schd := hr_loc_work_schedule.calc_sch_based_dur
232 ( p_assignment_id,
233 'D',
234 'N',
235 P_START_DATE,
236 P_END_DATE,
237 l_start_time,
238 l_end_time,
239 l_DAYS_WTHOUT_PUBLIC
240 );
241
242 l_CURRENT_PUBLIC_HOLIDAYS := l_DAYS_WTH_PUBLIC - l_DAYS_WTHOUT_PUBLIC;
243 l_CURRENT_WEEKENDS:= l_TOTAL_DAYS - l_DAYS_WTH_PUBLIC;
244
245 p_weekends :=l_CURRENT_WEEKENDS;
246 p_public_holidays :=l_CURRENT_PUBLIC_HOLIDAYS;
247 p_Total_holidays :=l_TOTAL_DAYS;
248
249
250
251
252 END GET_WEEKEND_PUBLIC_HOLIDAYS;
253
254 Function holiday_Element_populate(p_assignment_id in number,
255 p_person_id in number,
256 p_absence_attendance_id in number,
257 p_element_type_id in number,
258 p_absence_category in varchar2,
259 p_original_entry_id OUT NOCOPY NUMBER,
260 p_input_value_name1 OUT NOCOPY VARCHAR2,
261 p_input_value1 OUT NOCOPY VARCHAR2,
262 p_input_value_name2 OUT NOCOPY VARCHAR2,
263 p_input_value2 OUT NOCOPY VARCHAR2,
264 p_input_value_name3 OUT NOCOPY VARCHAR2,
265 p_input_value3 OUT NOCOPY VARCHAR2,
266 p_input_value_name4 OUT NOCOPY VARCHAR2,
267 p_input_value4 OUT NOCOPY VARCHAR2,
268 p_input_value_name5 OUT NOCOPY VARCHAR2,
269 p_input_value5 OUT NOCOPY VARCHAR2,
270 p_input_value_name6 OUT NOCOPY VARCHAR2,
271 p_input_value6 OUT NOCOPY VARCHAR2,
272 p_input_value_name7 OUT NOCOPY VARCHAR2,
273 p_input_value7 OUT NOCOPY VARCHAR2,
274 p_input_value_name8 OUT NOCOPY VARCHAR2,
275 p_input_value8 OUT NOCOPY VARCHAR2,
276 p_input_value_name9 OUT NOCOPY VARCHAR2,
277 p_input_value9 OUT NOCOPY VARCHAR2,
278 p_input_value_name10 OUT NOCOPY VARCHAR2,
279 p_input_value10 OUT NOCOPY VARCHAR2,
280 p_input_value_name11 OUT NOCOPY VARCHAR2,
281 p_input_value11 OUT NOCOPY VARCHAR2,
282 p_input_value_name12 OUT NOCOPY VARCHAR2,
283 p_input_value12 OUT NOCOPY VARCHAR2,
284 p_input_value_name13 OUT NOCOPY VARCHAR2,
285 p_input_value13 OUT NOCOPY VARCHAR2,
286 p_input_value_name14 OUT NOCOPY VARCHAR2,
287 p_input_value14 OUT NOCOPY VARCHAR2,
288 p_input_value_name15 OUT NOCOPY VARCHAR2,
289 p_input_value15 OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
290
291 l_start_date date;
292 l_end_date date;
293 l_days number;
294 l_hours number;
295 l_absent_reason number;
296 l_start_time varchar2(5);
297 l_end_time varchar2(5);
298 l_abs_cat_meaning varchar2(100);
299 l_ABS_TYPE varchar2(240);
300 l_ABS_CATEGORY varchar2(240);
301 l_wrk_schd_return number;
302 l_wrk_duration number;
303 l_start_time_char varchar2(5) := '00:00';
304 l_end_time_char varchar2(5) := '23:59';
305 l_Absence_Percentage Number(10);
306 l_wrk_start_date date;
307
308 l_absence_start_date date;
309 l_absence_end_date date;
310
311 l_weekends number;
312 l_public_holidays number;
313 l_Total_absence_days number;
314
315
316 CURSOR CSR_GET_ABSENCE is SELECT PAAT.NAME,-- PAAT.ABSENCE_ATTENDANCE_TYPE_ID,
317 PAAT.ABSENCE_CATEGORY,
318 PAA.date_start,
319 PAA.date_end,
320 PAA.absence_Days,
321 PAA.absence_Hours,
322 PAA.TIME_START,
323 PAA.TIME_END,
324 PAA.ABS_INFORMATION3
325 FROM per_absence_attendances PAA,
326 per_absence_attendance_types PAAT
327 WHERE PAA.absence_attendance_id = p_absence_attendance_id
328 and PAA.ABSENCE_ATTENDANCE_TYPE_ID =PAAT.ABSENCE_ATTENDANCE_TYPE_ID ;
329
330 row_get_absence CSR_GET_ABSENCE%ROWTYPE;
331
332 CURSOR CSR_GET_ABCAT is SELECT MEANING
333 FROM HR_LOOKUPS
334 WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
335 AND ENABLED_FLAG = 'Y'
336 AND LOOKUP_CODE = p_absence_category;
337
338 ROW_GET_ABCAT CSR_GET_ABCAT%ROWTYPE;
339
340
341
342 BEGIN
343
344 -- Fetch absence attendance details
345 OPEN CSR_GET_ABSENCE;
346 FETCH CSR_GET_ABSENCE INTO row_get_absence;
347 CLOSE CSR_GET_ABSENCE;
348
349 l_ABS_TYPE := row_get_absence.NAME;
350 l_ABS_CATEGORY := row_get_absence.ABSENCE_CATEGORY;
351 l_start_date :=row_get_absence.date_start;
352 l_end_date :=row_get_absence.date_end;
353 l_days :=row_get_absence.absence_Days;
354 l_hours :=row_get_absence.absence_Hours;
355 l_Absence_Percentage :=row_get_absence.ABS_INFORMATION3;
356 l_start_time := row_get_absence.TIME_START;
357 l_end_time := row_get_absence.TIME_END ;
358
359
360 -- To select absence category meaning by passing code
361 OPEN CSR_GET_ABCAT;
362 FETCH CSR_GET_ABCAT INTO row_GET_ABCAT;
363 CLOSE CSR_GET_ABCAT;
364
365 l_abs_cat_meaning :=ROW_GET_ABCAT.MEANING;
366
367
368 -- Check if absence category is S ( Sickness )
369 IF p_absence_category in ('V') THEN
370 p_input_value_name1 := 'Absence Category';
371 p_input_value1 := l_abs_cat_meaning;
372
373 -- p_input_value_name2 := 'Absence Type';
374 -- p_input_value2 := l_ABS_TYPE;
375
376 p_input_value_name2 := 'Absence Percentage';
377 p_input_value2 := l_Absence_Percentage;
378
379 p_input_value_name3 := 'Start Date';
380 p_input_value3 := l_start_date;
381
382 p_input_value_name4 := 'End Date';
383 p_input_value4 := l_end_date;
384
385 p_input_value_name5 := 'Days';
386 p_input_value5 := l_days;
387
388 p_input_value_name6 := 'Hours';
389 p_input_value6 := l_hours;
390
391 p_input_value_name7 := 'CREATOR_ID';
392 p_input_value7 := p_absence_attendance_id;
393
394 END IF;
395
396
397 -- IF p_input_value7 IS NOT NULL or p_input_value6 IS NOT NULL
398 -- User entered the value manually
399 -- apply working perccentage and absence percentage and return
400 --THEN
401
402 --hr_utility.set_location(' Null ',10);
403 --ELSE
404 -- equivalent to p_input_value5 IS NULL and p_input_value6 IS NULL
405 /*l_absence_start_date :=fnd_date.date_to_chardate(p_input_value3);
406 l_absence_end_date := fnd_date.date_to_chardate(p_input_value4);
407
408 --send starting date,time and ending date,time to get weekends and public holidays
409 GET_WEEKEND_PUBLIC_HOLIDAYS(p_assignment_id
410 ,fnd_date.date_to_chardate(p_input_value3)
411 ,fnd_date.date_to_chardate(p_input_value4)
412 ,nvl(l_start_time,l_start_time_char)
413 ,nvl(l_end_time,l_end_time_char)
414 ,l_weekends
415 ,l_public_holidays
416 ,l_Total_absence_days); */
417
418 --END IF;
419
420 -- p_input_value5 := l_Total_absence_days;
421
422
423 -- Return Y indicating to process the element for the input assignment id.
424
425 RETURN 'Y';
426
427 END holiday_Element_populate;
428
429 FUNCTION GET_DAYS_WITH_ABS_PERCENTAGE(
430 p_date_earned in date,
431 p_tax_unit_id in Number,
432 p_assignment_action_id IN NUMBER,
433 p_assignment_id IN NUMBER,
434 p_business_group_id in NUMBER,
435 p_days IN NUMBER,
436 p_Absence_percentage IN Number,
437 p_category_code IN VARCHAR2
438 )
439
440 RETURN NUMBER IS
441
442 lr_Get_Defined_Balance_Id number;
443 l_generate char(1);
444 l_max_days number;
445 l_DAYS_IN_BALANCE number;
446 l_check_interrupted char(1);
447
448 l_absence_days NUMBER:=0;
449
450
451
452
453 Cursor csr_Generate_Max_Days IS
454 SELECT hoi2.org_information2,hoi2.org_information3
455 FROM HR_ORGANIZATION_UNITS hou
456 ,HR_ORGANIZATION_INFORMATION hoi1
457 ,HR_ORGANIZATION_INFORMATION hoi2
458 WHERE hou.organization_id =p_tax_unit_id
459 AND hoi1.organization_id = hou.organization_id
460 AND hoi1.org_information_context = 'CLASS'
461 AND hoi1.ORG_INFORMATION1='HR_LEGAL_EMPLOYER'
462 AND hoi2.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
463 AND hoi1.organization_id = hoi2.organization_id
464 AND hoi2.org_information1 IS NOT NULL
465 AND hoi2.org_information1=p_category_code;
466
467 /* SELECT hoi4.ORG_INFORMATION2,hoi4.ORG_INFORMATION3
468 FROM HR_ORGANIZATION_UNITS o1
469 ,HR_ORGANIZATION_INFORMATION hoi1
470 ,HR_ORGANIZATION_INFORMATION hoi2
471 ,HR_ORGANIZATION_INFORMATION hoi3
472 ,HR_ORGANIZATION_INFORMATION hoi4
473 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
474 FROM PER_ALL_ASSIGNMENTS_F ASG
475 ,HR_SOFT_CODING_KEYFLEX SCL
476 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
477 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
478 AND p_date_earned BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
479 WHERE o1.business_group_id = l_business_group_id
480 AND hoi1.organization_id = o1.organization_id
481 AND hoi1.organization_id = X.ORG_ID
482 --AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
483 AND hoi1.org_information_context = 'CLASS'
484 AND o1.organization_id = hoi2.org_information1
485 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
486 AND hoi2.organization_id = hoi3.organization_id
487 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
488 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
489 AND hoi3.organization_id = hoi4.organization_id
490 AND hoi4.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
491 AND hoi4.org_information1 IS NOT NULL
492 AND hoi4.org_information1=p_category_code;
493 */
494 Cursor csr_Earning_Year is
495 SELECT substr(hoi2.ORG_INFORMATION2,4,2)
496 FROM HR_ORGANIZATION_UNITS o1
497 ,HR_ORGANIZATION_INFORMATION hoi1
498 ,HR_ORGANIZATION_INFORMATION hoi2
499 WHERE
500 hoi1.organization_id = o1.organization_id
501 AND hoi1.organization_id = p_tax_unit_id
502 AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
503 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
504 AND hoi1.organization_id = hoi2.organization_id
505 AND hoi2.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
506 AND hoi2.org_information1 IS NOT NULL;
507
508 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE) IS
509 SELECT ue.creator_id
510 FROM ff_user_entities ue,
511 ff_database_items di
512 WHERE di.user_name = csr_v_Balance_Name
513 AND ue.user_entity_id = di.user_entity_id
514 AND ue.legislation_code = 'SE'
515 AND ue.business_group_id is NULL
516 AND ue.creator_type = 'B';
517
518
519 BEGIN
520 /*Check for the interruption of Sick Pay*/
521 l_check_interrupted:=CHECK_SICK_INTERUPTED(p_date_earned,p_assignment_id,p_tax_unit_id,p_business_group_id,p_category_code);
522
523 IF l_check_interrupted='Y' THEN
524 /*If the sick leave is interupted*/
525 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_ABSENCE_DAYS_HOLIDAY_PAY_ASG_LE_ABS_CAT_HY_YEAR');
526 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
527 CLOSE csr_Get_Defined_Balance_Id;
528
529 pay_balance_pkg.set_context('SOURCE_TEXT',p_category_code);
530 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
531 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
532
533 l_DAYS_IN_BALANCE := pay_balance_pkg.get_value(
534 P_DEFINED_BALANCE_ID => lr_Get_Defined_Balance_Id,
535 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
536 P_TAX_UNIT_ID => p_tax_unit_id,
537 P_JURISDICTION_CODE => null,
538 P_SOURCE_ID => null,
539 P_SOURCE_TEXT =>p_category_code,
540 P_TAX_GROUP => null,
541 P_DATE_EARNED =>p_date_earned );
542
543 OPEN csr_Generate_Max_Days;
544 FETCH csr_Generate_Max_Days INTO l_generate,l_max_days;
545 CLOSE csr_Generate_Max_Days;
546
547 l_absence_days := 0;
548 /* If generate is Y
549 then value greater than the max is considered as absence,
550 else whole value */
551 IF l_generate='Y' THEN
552 /* If the Maxmimum days for this category has been already exceeded
553 In that case apply the absence percentage directly and return */
554 IF l_DAYS_IN_BALANCE+p_days <= l_max_days
555 THEN
556 l_absence_days := p_days;
557 ELSIF l_DAYS_IN_BALANCE>=l_max_days THEN
558 /*When the absence has already crossed the limit in previous period*/
559 l_absence_days := round(p_days*p_Absence_percentage/100);
560 ELSE
561 /* Find the no of days with 100 perc tobe added to balance
562 till it exceeds Max days
563 for the rest , abscence percentage to be applied. */
564 l_absence_days := abs(l_max_days - l_DAYS_IN_BALANCE);
565 l_absence_days := l_absence_days + round(abs(p_days - l_absence_days)*p_Absence_percentage/100);
566
567 END IF;
568 ELSE
569 /* If the generate is not set to 'Y' then consider the total absence days without applying absence percentage*/
570 --l_absence_days:=l_absence_days + l_DAYS_IN_BALANCE;
571 l_absence_days := p_days;
572 END IF;
573 ELSE
574
575 /*If the sick leave is not interupted*/
576 l_absence_days := p_days;
577 END if;
578
579 return l_absence_days;
580
581 END GET_DAYS_WITH_ABS_PERCENTAGE;
582
583 FUNCTION CHECK_SICK_INTERUPTED(p_date_earned IN date,
584 p_assignment_id IN NUMBER,
585 p_tax_unit_id IN NUMBER,
586 p_business_group_id in NUMBER,
587 p_category_code IN VARCHAR2
588 )
589 RETURN VARCHAR2
590 IS
591 l_end_month NUMBER;
592 l_earn_year NUMBER;
593 l_earning_end_date DATE;
594 l_earning_start_date DATE;
595 l_prev_end_date DATE;
596 l_return VARCHAR2(1);
597 l_counter NUMBER:=0;
598
599 CURSOR csr_sick_interrupted(csr_v_assignment_id NUMBER,
600 csr_v_effective_date DATE,
601 csr_v_earn_start_date DATE,
602 csr_v_category_code VARCHAR2 ) --,
603 --csr_v_earn_end_date DATE)
604 IS
605 SELECT fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE) start_date
606 ,fnd_date.canonical_to_date(peevf2.SCREEN_ENTRY_VALUE) end_date
607 ,fnd_number.canonical_to_number(peevf4.SCREEN_ENTRY_VALUE) Absence_Percentage
608 FROM per_all_assignments_f paaf
609 ,pay_element_types_f et
610 ,pay_element_entries_f ee
611 ,pay_element_entry_values_f peevf1
612 ,pay_element_entry_values_f peevf2
613 ,pay_element_entry_values_f peevf3
614 ,pay_element_entry_values_f peevf4
615 ,pay_input_values_f pivf1
616 ,pay_input_values_f pivf2
617 ,pay_input_values_f pivf3
618 ,pay_input_values_f pivf4
619 WHERE paaf.assignment_id=csr_v_assignment_id
620 AND csr_v_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
621 AND et.element_name= 'Sickness Details'
622 AND et.legislation_code= 'SE'
623 AND ee.assignment_id= paaf.assignment_id
624 AND ee.ELEMENT_TYPE_ID=et.ELEMENT_TYPE_ID
625 AND ee.ELEMENT_ENTRY_ID=peevf1.ELEMENT_ENTRY_ID
626 AND pivf1.element_type_id= et.element_type_id
627 AND pivf1.name='Start Date'
628 AND peevf1.input_value_id=pivf1.input_value_id
629 and ee.ELEMENT_ENTRY_ID=peevf2.ELEMENT_ENTRY_ID
630 AND pivf2.element_type_id=et.element_type_id
631 AND pivf2.name='End Date'
632 AND peevf2.input_value_id=pivf2.input_value_id
633 AND ee.ELEMENT_ENTRY_ID =peevf3.ELEMENT_ENTRY_ID
634 AND pivf3.element_type_id=et.element_type_id
635 AND pivf3.name='Absence Category'
636 AND peevf3.input_value_id=pivf3.input_value_id
637 AND peevf3.SCREEN_ENTRY_VALUE=csr_v_category_code
638 AND ee.ELEMENT_ENTRY_ID=peevf4.ELEMENT_ENTRY_ID
639 AND pivf4.element_type_id=et.element_type_id
640 AND pivf4.name='Absence Percentage'
641 AND peevf4.input_value_id=pivf4.input_value_id
642 AND ee.effective_start_date<=csr_v_effective_date
643 AND ee.effective_end_date>=csr_v_earn_start_date
644 AND peevf1.effective_start_date<=csr_v_effective_date
645 AND peevf1.effective_end_date>=csr_v_earn_start_date
646 AND peevf2.effective_start_date<=csr_v_effective_date
647 AND peevf2.effective_end_date>=csr_v_earn_start_date
648 AND peevf3.effective_start_date<=csr_v_effective_date
649 AND peevf3.effective_end_date>=csr_v_earn_start_date
650 AND peevf4.effective_start_date<=csr_v_effective_date
651 AND peevf4.effective_end_date>=csr_v_earn_start_date
652 ORDER BY fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE);
653
654 CURSOR csr_end_earn_month(csr_v_assignment_id NUMBER,
655 csr_v_effective_date DATE,
656 csr_v_business_group_id NUMBER)
657 IS
658 SELECT substr(hoi4.ORG_INFORMATION2,4,2)
659 FROM HR_ORGANIZATION_UNITS o1
660 ,HR_ORGANIZATION_INFORMATION hoi1
661 ,HR_ORGANIZATION_INFORMATION hoi2
662 ,HR_ORGANIZATION_INFORMATION hoi3
663 ,HR_ORGANIZATION_INFORMATION hoi4
664 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
665 FROM PER_ALL_ASSIGNMENTS_F ASG
666 ,HR_SOFT_CODING_KEYFLEX SCL
667 WHERE ASG.ASSIGNMENT_ID = csr_v_assignment_id
668 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
669 AND csr_v_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
670 WHERE o1.business_group_id = csr_v_business_group_id
671 AND hoi1.organization_id = o1.organization_id
672 AND hoi1.organization_id = X.ORG_ID
673 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
674 AND hoi1.org_information_context = 'CLASS'
675 AND o1.organization_id = hoi2.org_information1
676 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
677 AND hoi2.organization_id = hoi3.organization_id
678 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
679 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
680 AND hoi3.organization_id = hoi4.organization_id
681 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
682 AND hoi4.org_information1 IS NOT NULL;
683
684 BEGIN
685 /*Get the Earn Year end date*/
686 l_earn_year:=PAY_SE_HOLIDAY_PAY.Get_Earning_Year(p_date_earned,p_tax_unit_id);
687 OPEN csr_end_earn_month(p_assignment_id,p_date_earned,p_business_group_id);
688 FETCH csr_end_earn_month INTO l_end_month;
689 CLOSE csr_end_earn_month;
690 l_earning_end_date:=last_day(to_date('01/'||l_end_month|| '/' ||l_earn_year,'dd/mm/yyyy'));
691 /*find the starting date of the holiday year two years back*/
692 l_earning_start_date:=add_months(l_earning_end_date,-24)+1;
693 /*check for the sick pay whether it is interrupted*/
694 FOR csr_sick IN csr_sick_interrupted(p_assignment_id,l_earning_end_date,l_earning_start_date,p_category_code) LOOP
695 l_counter:=l_counter+1;
696 IF (csr_sick.start_date-(l_prev_end_date+1))>14 OR ((csr_sick.start_date-l_earning_start_date)>14 AND l_counter=1) OR (csr_sick.Absence_Percentage<>100) THEN
697 l_return:='Y';
698 EXIT;
699 END IF;
700 l_prev_end_date:=csr_sick.end_date;
701 END LOOP csr_sick_interrupted;
702
703 IF l_return IS NULL THEN
704 l_return:='N';
705 END IF;
706 RETURN l_return;
707 END CHECK_SICK_INTERUPTED;
708
709 END PAY_SE_ABSENCE_USER;
710