[Home] [Help]
281: service.date_start,
282: service.prior_employment_SSP_weeks,
283: service.prior_employment_SSP_paid_to,
284: nvl (service.projected_termination_date,
285: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
286: nvl (service.actual_termination_date,
287: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
288: nvl (service.last_standard_process_date,
289: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
283: service.prior_employment_SSP_paid_to,
284: nvl (service.projected_termination_date,
285: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
286: nvl (service.actual_termination_date,
287: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
288: nvl (service.last_standard_process_date,
289: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
290: /* 7688727 begin */
291: nvl (service.final_process_date,
285: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
286: nvl (service.actual_termination_date,
287: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
288: nvl (service.last_standard_process_date,
289: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
290: /* 7688727 begin */
291: nvl (service.final_process_date,
292: hr_general.end_of_time) FINAL_PROCESS_DATE,
293: /* 7688727 End */
288: nvl (service.last_standard_process_date,
289: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
290: /* 7688727 begin */
291: nvl (service.final_process_date,
292: hr_general.end_of_time) FINAL_PROCESS_DATE,
293: /* 7688727 End */
294: service.leaving_reason
295: from per_all_people_f PERSON,
296: per_periods_of_service SERVICE
297: where person.person_id = p_person_id
298: and service.person_id = person.person_id
299: and p_date_start between service.date_start
300: and nvl (service.actual_termination_date,
301: hr_general.end_of_time);
302: --
303: absentee csr_person%rowtype;
304: --
305: cursor csr_absence_details (p_absence_attendance_id in number) is
318: --
319: -- 563202 nvl removed from the where clause as was implicitly causing the
320: -- index to be removed.
321: select sickness_start_date,
322: nvl(sickness_end_date,hr_general.end_of_time) sickness_end_date,
323: business_group_id,
324: absence_attendance_id,
325: pregnancy_related_illness
326: from per_absence_attendances
422: --
423: hr_utility.trace (l_proc||' p_qualifying_days = ' ||
424: to_char (p_qualifying_days));
425: --
426: hr_general.assert_condition (all_parameters_valid);
427: --
428: end check_parameters;
429: --
430: begin
748: begin
749: hr_utility.trace(l_proc||' p_person_id = '||to_char (p_person_id));
750: hr_utility.trace(l_proc||' p_sickness_start_date = '||to_char (p_sickness_start_date));
751: hr_utility.trace(l_proc||' p_sickness_end_date = '||to_char (p_sickness_end_date));
752: hr_general.assert_condition (all_parameters_valid);
753: end check_parameters;
754: begin
755: --
756: hr_utility.set_location('Entering:'||l_proc,1);
822: greatest(sickness_start_date,p_start) + 1),0)
823: from per_absence_attendances
824: where person_id = p_person_id
825: and sickness_start_date <= p_end
826: and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
827: --
828: cursor csr_contig_abs(p_start date, p_end date,
829: p_start2 date, p_end2 date) is
830: -- select the dates for the continuous absence formed by the parameter
829: p_start2 date, p_end2 date) is
830: -- select the dates for the continuous absence formed by the parameter
831: -- absence and any absences adjacent to it.
832: select least(min(sickness_start_date),p_start),
833: greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
834: from per_absence_attendances
835: where person_id = p_person_id
836: and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
837: --
838: l_days_absent number := 0;
839: l_prev_sickness_start date := null;
840: l_prev_sickness_end date := null;
841: l_sickness_start_date date := p_sickness_start_date;
842: l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
843: l_absence_is_a_PIW boolean := FALSE;
844: --
845: begin
846: get_SSP_element (p_sickness_start_date);
919: greatest(sickness_start_date,p_start) + 1),0)
920: from per_absence_attendances
921: where person_id = p_person_id
922: and sickness_start_date <= p_end
923: and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
924: --
925: cursor csr_contig_abs(p_start date, p_end date,
926: p_start2 date, p_end2 date) is
927: -- select the dates for the continuous absence formed by the parameter
926: p_start2 date, p_end2 date) is
927: -- select the dates for the continuous absence formed by the parameter
928: -- absence and any absences adjacent to it.
929: select least(min(sickness_start_date),p_start),
930: greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
931: from per_absence_attendances
932: where person_id = p_person_id
933: and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
934: --
941: l_linked number := null;
942: l_prev_sickness_start date := null;
943: l_prev_sickness_end date := null;
944: l_sickness_start_date date := p_sickness_start_date;
945: l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
946: l_absence_is_a_PIW boolean := FALSE;
947: --
948: begin
949: get_SSP_element (p_sickness_start_date);
1068: ||to_char (p_new_sickness_end_date));
1069: hr_utility.trace (' p_old_sickness_end_date = '
1070: ||to_char (p_old_sickness_end_date));
1071: --
1072: hr_general.assert_condition (all_parameters_valid);
1073: --
1074: end check_parameters;
1075: --
1076: begin
1190: ||to_char (p_linked_absence_id));
1191: hr_utility.trace (l_proc||' p_absence_attendance_id = '
1192: ||to_char (p_absence_attendance_id));
1193: --
1194: hr_general.assert_condition (all_parameters_valid);
1195: --
1196: end check_parameters;
1197: --
1198: begin
1366: --
1367: PIW_start date := null;
1368: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1369: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1370: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1371: l_age number;
1372: l_s_date date;
1373: l_e_date date;
1374: --
1460: --
1461: PIW_start date := null;
1462: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1463: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1464: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1465: l_age number;
1466: l_linked_id number;
1467: l_s_date date;
1468: l_e_date date;
1495: and per.sickness_start_date is not null
1496: and per.sickness_start_date < p_sickness_start_date
1497: and (p_start_date is null or
1498: per.sickness_start_date >= p_start_date)
1499: and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
1500: and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
1501: order by sickness_start_date desc;
1502:
1503: Cursor POS_series(p_start_date date) is
1502:
1503: Cursor POS_series(p_start_date date) is
1504: select PERIOD_OF_SERVICE_ID from per_periods_of_service service
1505: where PERSON_ID = p_person_id
1506: and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
1507:
1508: --12552547 End
1509:
1510: --
1592: --
1593: PIW_start date := null;
1594: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1595: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1596: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1597: l_age number;
1598: l_s_date date;
1599: l_e_date date;
1600: --
1627: and per.sickness_start_date < p_sickness_start_date
1628: and (p_start_date is null or
1629: per.sickness_start_date >= p_start_date)
1630: and per.person_id = service.person_id
1631: and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
1632: and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
1633: order by sickness_start_date desc;
1634:
1635: --
1637: Cursor POS_series(p_start_date date) is
1638: select PERIOD_OF_SERVICE_ID
1639: from per_periods_of_service service
1640: where PERSON_ID = p_person_id
1641: and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
1642:
1643: --12552547 End
1644:
1645: --
1989: hr_utility.trace(' p_period_to: '||to_char(p_period_to));
1990: hr_utility.trace(' p_person_id: '||to_char(p_person_id));
1991: hr_utility.trace(' p_business_group_id: '||to_char(p_business_group_id));
1992: --
1993: hr_general.assert_condition (all_parameters_valid);
1994: --
1995: end check_parameters;
1996: --
1997: begin
2242: --
2243: return boolean is
2244: --
2245: l_proc varchar2(72) := g_package||'entitled_to_ssp';
2246: last_entitled_day date := hr_general.end_of_time;
2247: max_PIW_end_date date := null;
2248: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
2249: --
2250: no_prima_facia_entitlement exception;
2288: begin
2289: hr_utility.trace(' p_reason: ' || p_reason);
2290: hr_utility.trace(' withhold_from/to: ' ||
2291: to_char(p_withhold_from)||', '||to_char(p_withhold_to));
2292: hr_general.assert_condition (all_parameters_valid);
2293: end check_parameters;
2294: --
2295: begin
2296: hr_utility.set_location('Entering:'||l_proc,1);
2416: -- PIW. Return the day before the stoppage is
2417: -- effective because that is the last day of payment.
2418: --
2419: cursor csr_last_entitled_day is
2420: select nvl (min (withhold_from),hr_general.end_of_time) -1
2421: from ssp_stoppages
2422: where absence_attendance_id = g_PIW_id
2423: and override_stoppage <> 'Y'
2424: and withhold_to is null;
2467: l_sickness_end_date date;
2468: l_min_withheld_from date;
2469: l_max_withheld_to date;
2470: l_PIW_end_date date := null;
2471: stoppage_date date := hr_general.end_of_time;
2472: min_cal_start_date date := hr_general.end_of_time;
2473: max_cal_end_date date := hr_general.start_of_time;
2474: l_nos_absences number;
2475: l_business_group_id number;
2468: l_min_withheld_from date;
2469: l_max_withheld_to date;
2470: l_PIW_end_date date := null;
2471: stoppage_date date := hr_general.end_of_time;
2472: min_cal_start_date date := hr_general.end_of_time;
2473: max_cal_end_date date := hr_general.start_of_time;
2474: l_nos_absences number;
2475: l_business_group_id number;
2476: l_absence_attendance_id number;
2469: l_max_withheld_to date;
2470: l_PIW_end_date date := null;
2471: stoppage_date date := hr_general.end_of_time;
2472: min_cal_start_date date := hr_general.end_of_time;
2473: max_cal_end_date date := hr_general.start_of_time;
2474: l_nos_absences number;
2475: l_business_group_id number;
2476: l_absence_attendance_id number;
2477: l_nos_stoppages number;
2513: where absence_attendance_id = g_PIW_id
2514: and override_stoppage = 'N'
2515: and withhold_to is not null
2516: and ((p_effective_start_date <= withhold_from and
2517: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2518: )
2519: or
2520: (p_effective_end_date >= withhold_from and
2521: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2517: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2518: )
2519: or
2520: (p_effective_end_date >= withhold_from and
2521: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2522: ))
2523: union all
2524: select withhold_from, withhold_to
2525: from ssp_stoppages ss,
2528: and paa.absence_attendance_id = ss.absence_attendance_id
2529: and override_stoppage = 'N'
2530: and withhold_to is not null
2531: and ((p_effective_start_date <= withhold_from and
2532: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2533: )
2534: or
2535: (p_effective_end_date >= withhold_from and
2536: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2532: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2533: )
2534: or
2535: (p_effective_end_date >= withhold_from and
2536: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2537: ));
2538: --
2539: function SSP_rate (p_effective_date date) return number is
2540: --
2562: p_effective_date = trunc (p_effective_date));
2563: begin
2564: hr_utility.trace (l_proc||' p_effective_date = '||to_char (p_effective_date));
2565: --
2566: hr_general.assert_condition (all_parameters_valid);
2567: --
2568: end check_parameters;
2569: --
2570: begin
2623: begin
2624: hr_utility.trace (' p_start_date = '||to_char (p_start_date));
2625: hr_utility.trace (' p_end_date = '||to_char (p_end_date));
2626: hr_utility.trace (' p_PIW_id = '||to_char (p_PIW_id));
2627: hr_general.assert_condition (all_parameters_valid);
2628: end check_parameters;
2629: begin
2630: hr_utility.set_location('Entering:'||l_proc,1);
2631: --
2724: hr_utility.trace(l_proc||' p_sickness_end_date = '
2725: ||to_char (p_sickness_end_date));
2726: hr_utility.trace(l_proc||' p_pregnancy_related_illness = '
2727: ||p_pregnancy_related_illness);
2728: hr_general.assert_condition (all_parameters_valid);
2729: end check_parameters;
2730: begin
2731: hr_utility.set_location('Entering:'||l_proc,1);
2732: --
2899: --
2900: -- Handle the case where the end date would exceed the
2901: -- maximum date that Oracle can deal with.
2902: --
2903: end_disqualifying_period := hr_general.end_of_time;
2904: --
2905: end;
2906: --
2907: -- the previously calculated last entitlement day is
2996: SSP_rate_figure := SSP_rate (PIW_start_date);
2997: --
2998: l_nos_absences := 0;
2999: absence_table := empty_absence_table;
3000: l_max_absence_end_date := hr_general.start_of_time;
3001: --
3002: for each_abs_tab in PIW loop
3003: l_max_absence_start_date := each_abs_tab.sickness_start_date;
3004: l_max_absence_end_date := each_abs_tab.sickness_end_date;
3077: -- than the Saturday after the sickness_start_date, which
3078: -- causes an error if the sickness_start_date is itself a
3079: -- Saturday.
3080: end_date := least(next_day(next_day(l_sickness_start_date,l_sunday) -7, l_saturday),
3081: nvl (rate_end_date, hr_general.end_of_time),last_entitled_day);
3082: --
3083: if start_date > last_entitled_day then
3084: -- There is a stoppage which prevents the entry creation
3085: --
3108: -- this is not legislation specific.
3109: --
3110: cal_end_date := l_max_absence_end_date;
3111: --
3112: if cal_end_date is null or cal_end_date = hr_general.end_of_time then
3113: cal_end_date := l_max_absence_start_date +
3114: ((nvl(g_SSP_legislation.MAXIMUM_SSP_PERIOD,28) + 1) * 7);
3115: else
3116: cal_end_date := ssp_smp_support_pkg.end_of_week(l_max_absence_end_date);
3139: -- qualifying_days_in_period so many times.
3140: --
3141: stoppage_table := empty_stoppage_table;
3142: l_nos_stoppages := 0;
3143: l_min_withheld_from := hr_general.end_of_time;
3144: l_max_withheld_to := hr_general.start_of_time;
3145: --
3146: for each_stoppage in csr_stoppages(PIW_start_date, cal_end_date)
3147: loop
3140: --
3141: stoppage_table := empty_stoppage_table;
3142: l_nos_stoppages := 0;
3143: l_min_withheld_from := hr_general.end_of_time;
3144: l_max_withheld_to := hr_general.start_of_time;
3145: --
3146: for each_stoppage in csr_stoppages(PIW_start_date, cal_end_date)
3147: loop
3148: l_nos_stoppages := l_nos_stoppages + 1;
3403: -- calendar week end date and the withholding date.
3404: --
3405: start_date := next_day (start_date, l_sunday);
3406: end_date := least (next_day (start_date,l_saturday),
3407: nvl (rate_end_date,hr_general.end_of_time),
3408: nvl (last_entitled_day,hr_general.end_of_time));
3409: end if;
3410: --
3411: end loop;
3404: --
3405: start_date := next_day (start_date, l_sunday);
3406: end_date := least (next_day (start_date,l_saturday),
3407: nvl (rate_end_date,hr_general.end_of_time),
3408: nvl (last_entitled_day,hr_general.end_of_time));
3409: end if;
3410: --
3411: end loop;
3412: end;
3580: --
3581: -- no medical evidence was found for this absence
3582: if csr_evidence%notfound then
3583: /*BUG 2984577 Added exception */
3584: if each_absence.sickness_end_date = hr_general.end_of_time then
3585: create_stoppage (p_withhold_from => each_absence.sickness_start_date,
3586: p_reason => 'No acceptable evidence in time',
3587: p_overridden => l_stoppage_overridden);
3588: last_entitled_day := least(last_entitled_day,each_absence.sickness_start_date - 1);
3614: hr_utility.set_location('Entering:'||l_proc,1);
3615: -- the employment is scheduled to end
3616: -- bug 2984458 to avoid displaying 'Contract ends' when
3617: -- employee is deceased
3618: if (absentee.actual_termination_date < hr_general.end_of_time) and
3619: nvl(absentee.leaving_reason,'No Reason') <>'D' then -- Only If termination reason is 'Death' then this stoppage should be not generated.
3620: create_stoppage(p_withhold_from => absentee.actual_termination_date +1,
3621: p_reason => 'Contract ends',
3622: p_overridden => l_stoppage_overridden);
3712: --
3713: g_PIW_id := p_absence.absence_attendance_id;
3714: new_entry := empty_record;
3715: PIW_start_date := p_absence.sickness_start_date;
3716: PIW_end_date := hr_general.end_of_time;
3717: --
3718: -- Delete stoppages
3719: --
3720: for obsolete_stoppage in csr_old_stoppages loop
3728: PIW_end_date := nvl (linked_PIW_end_date (p_absence.person_id,
3729: p_absence.sickness_start_date,
3730: p_absence.sickness_end_date,
3731: p_absence.absence_attendance_id),
3732: hr_general.end_of_time);
3733: --
3734: hr_utility.trace('Piw_end_date ' || to_char(PIW_end_date,'DD-MON-YYYY'));
3735: --
3736: g_PIW_id := nvl(p_absence.linked_absence_id,p_absence.absence_attendance_id);
4201: --
4202: hr_utility.trace (l_proc||' p_person_id: '||to_char(p_person_id));
4203: hr_utility.trace (l_proc||' p_date_start: ' ||to_char(p_date_start));
4204: --
4205: hr_general.assert_condition (all_parameters_valid);
4206: --
4207: end check_parameters;
4208: --
4209: begin
4264: hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4265: hr_utility.trace (l_proc||' p_date_of_death = '||to_char (p_date_of_death));
4266: hr_utility.trace (l_proc||' p_date_of_birth = '||to_char (p_date_of_birth));
4267: --
4268: hr_general.assert_condition (all_parameters_valid);
4269: --
4270: end check_parameters;
4271: --
4272: begin
4338: else
4339: hr_utility.trace (l_proc||' p_deleting is FALSE');
4340: end if;
4341: --
4342: hr_general.assert_condition (all_parameters_valid);
4343: --
4344: end check_parameters;
4345: begin
4346: hr_utility.set_location('Entering:'||l_proc,1);
4411: hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4412: hr_utility.trace (l_proc||' p_effective_date = '
4413: ||to_char (p_effective_date));
4414: --
4415: hr_general.assert_condition (all_parameters_valid);
4416: --
4417: end check_parameters;
4418: --
4419: begin
4497: all_parameters_valid constant boolean := (p_absence_attendance_id is not null);
4498: begin
4499: hr_utility.trace (l_proc||' p_absence_attendance_id = '||to_char (p_absence_attendance_id));
4500: --
4501: hr_general.assert_condition (all_parameters_valid);
4502: --
4503: end check_parameters;
4504: begin
4505: hr_utility.set_location('Entering:'||l_proc,1);