[Home] [Help]
272: service.date_start,
273: service.prior_employment_SSP_weeks,
274: service.prior_employment_SSP_paid_to,
275: nvl (service.projected_termination_date,
276: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
277: nvl (service.actual_termination_date,
278: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
279: nvl (service.last_standard_process_date,
280: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
274: service.prior_employment_SSP_paid_to,
275: nvl (service.projected_termination_date,
276: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
277: nvl (service.actual_termination_date,
278: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
279: nvl (service.last_standard_process_date,
280: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
281: service.leaving_reason
282: from per_all_people_f PERSON,
276: hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
277: nvl (service.actual_termination_date,
278: hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
279: nvl (service.last_standard_process_date,
280: hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
281: service.leaving_reason
282: from per_all_people_f PERSON,
283: per_periods_of_service SERVICE
284: where person.person_id = p_person_id
284: where person.person_id = p_person_id
285: and service.person_id = person.person_id
286: and p_date_start between service.date_start
287: and nvl (service.actual_termination_date,
288: hr_general.end_of_time);
289: --
290: absentee csr_person%rowtype;
291: --
292: cursor csr_absence_details (p_absence_attendance_id in number) is
305: --
306: -- 563202 nvl removed from the where clause as was implicitly causing the
307: -- index to be removed.
308: select sickness_start_date,
309: nvl(sickness_end_date,hr_general.end_of_time) sickness_end_date,
310: business_group_id,
311: absence_attendance_id,
312: pregnancy_related_illness
313: from per_absence_attendances
409: --
410: hr_utility.trace (l_proc||' p_qualifying_days = ' ||
411: to_char (p_qualifying_days));
412: --
413: hr_general.assert_condition (all_parameters_valid);
414: --
415: end check_parameters;
416: --
417: begin
713: begin
714: hr_utility.trace(l_proc||' p_person_id = '||to_char (p_person_id));
715: hr_utility.trace(l_proc||' p_sickness_start_date = '||to_char (p_sickness_start_date));
716: hr_utility.trace(l_proc||' p_sickness_end_date = '||to_char (p_sickness_end_date));
717: hr_general.assert_condition (all_parameters_valid);
718: end check_parameters;
719: begin
720: --
721: hr_utility.set_location('Entering:'||l_proc,1);
787: greatest(sickness_start_date,p_start) + 1),0)
788: from per_absence_attendances
789: where person_id = p_person_id
790: and sickness_start_date <= p_end
791: and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
792: --
793: cursor csr_contig_abs(p_start date, p_end date,
794: p_start2 date, p_end2 date) is
795: -- select the dates for the continuous absence formed by the parameter
794: p_start2 date, p_end2 date) is
795: -- select the dates for the continuous absence formed by the parameter
796: -- absence and any absences adjacent to it.
797: select least(min(sickness_start_date),p_start),
798: greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
799: from per_absence_attendances
800: where person_id = p_person_id
801: and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
802: --
803: l_days_absent number := 0;
804: l_prev_sickness_start date := null;
805: l_prev_sickness_end date := null;
806: l_sickness_start_date date := p_sickness_start_date;
807: l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
808: l_absence_is_a_PIW boolean := FALSE;
809: --
810: begin
811: get_SSP_element (p_sickness_start_date);
884: greatest(sickness_start_date,p_start) + 1),0)
885: from per_absence_attendances
886: where person_id = p_person_id
887: and sickness_start_date <= p_end
888: and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
889: --
890: cursor csr_contig_abs(p_start date, p_end date,
891: p_start2 date, p_end2 date) is
892: -- select the dates for the continuous absence formed by the parameter
891: p_start2 date, p_end2 date) is
892: -- select the dates for the continuous absence formed by the parameter
893: -- absence and any absences adjacent to it.
894: select least(min(sickness_start_date),p_start),
895: greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
896: from per_absence_attendances
897: where person_id = p_person_id
898: and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
899: --
906: l_linked number := null;
907: l_prev_sickness_start date := null;
908: l_prev_sickness_end date := null;
909: l_sickness_start_date date := p_sickness_start_date;
910: l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
911: l_absence_is_a_PIW boolean := FALSE;
912: --
913: begin
914: get_SSP_element (p_sickness_start_date);
1033: ||to_char (p_new_sickness_end_date));
1034: hr_utility.trace (' p_old_sickness_end_date = '
1035: ||to_char (p_old_sickness_end_date));
1036: --
1037: hr_general.assert_condition (all_parameters_valid);
1038: --
1039: end check_parameters;
1040: --
1041: begin
1155: ||to_char (p_linked_absence_id));
1156: hr_utility.trace (l_proc||' p_absence_attendance_id = '
1157: ||to_char (p_absence_attendance_id));
1158: --
1159: hr_general.assert_condition (all_parameters_valid);
1160: --
1161: end check_parameters;
1162: --
1163: begin
1331: --
1332: PIW_start date := null;
1333: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1334: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1335: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1336: l_age number;
1337: l_s_date date;
1338: l_e_date date;
1339: --
1425: --
1426: PIW_start date := null;
1427: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1428: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1429: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1430: l_age number;
1431: l_linked_id number;
1432: l_s_date date;
1433: l_e_date date;
1523: --
1524: PIW_start date := null;
1525: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1526: l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1527: l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1528: l_age number;
1529: l_s_date date;
1530: l_e_date date;
1531: --
1883: hr_utility.trace(' p_period_to: '||to_char(p_period_to));
1884: hr_utility.trace(' p_person_id: '||to_char(p_person_id));
1885: hr_utility.trace(' p_business_group_id: '||to_char(p_business_group_id));
1886: --
1887: hr_general.assert_condition (all_parameters_valid);
1888: --
1889: end check_parameters;
1890: --
1891: begin
2136: --
2137: return boolean is
2138: --
2139: l_proc varchar2(72) := g_package||'entitled_to_ssp';
2140: last_entitled_day date := hr_general.end_of_time;
2141: max_PIW_end_date date := null;
2142: l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
2143: --
2144: no_prima_facia_entitlement exception;
2177: begin
2178: hr_utility.trace(' p_reason: ' || p_reason);
2179: hr_utility.trace(' withhold_from/to: ' ||
2180: to_char(p_withhold_from)||', '||to_char(p_withhold_to));
2181: hr_general.assert_condition (all_parameters_valid);
2182: end check_parameters;
2183: --
2184: begin
2185: hr_utility.set_location('Entering:'||l_proc,1);
2302: -- PIW. Return the day before the stoppage is
2303: -- effective because that is the last day of payment.
2304: --
2305: cursor csr_last_entitled_day is
2306: select nvl (min (withhold_from),hr_general.end_of_time) -1
2307: from ssp_stoppages
2308: where absence_attendance_id = g_PIW_id
2309: and override_stoppage <> 'Y'
2310: and withhold_to is null;
2353: l_sickness_end_date date;
2354: l_min_withheld_from date;
2355: l_max_withheld_to date;
2356: l_PIW_end_date date := null;
2357: stoppage_date date := hr_general.end_of_time;
2358: min_cal_start_date date := hr_general.end_of_time;
2359: max_cal_end_date date := hr_general.start_of_time;
2360: l_nos_absences number;
2361: l_business_group_id number;
2354: l_min_withheld_from date;
2355: l_max_withheld_to date;
2356: l_PIW_end_date date := null;
2357: stoppage_date date := hr_general.end_of_time;
2358: min_cal_start_date date := hr_general.end_of_time;
2359: max_cal_end_date date := hr_general.start_of_time;
2360: l_nos_absences number;
2361: l_business_group_id number;
2362: l_absence_attendance_id number;
2355: l_max_withheld_to date;
2356: l_PIW_end_date date := null;
2357: stoppage_date date := hr_general.end_of_time;
2358: min_cal_start_date date := hr_general.end_of_time;
2359: max_cal_end_date date := hr_general.start_of_time;
2360: l_nos_absences number;
2361: l_business_group_id number;
2362: l_absence_attendance_id number;
2363: l_nos_stoppages number;
2399: where absence_attendance_id = g_PIW_id
2400: and override_stoppage = 'N'
2401: and withhold_to is not null
2402: and ((p_effective_start_date <= withhold_from and
2403: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2404: )
2405: or
2406: (p_effective_end_date >= withhold_from and
2407: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2403: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2404: )
2405: or
2406: (p_effective_end_date >= withhold_from and
2407: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2408: ))
2409: union all
2410: select withhold_from, withhold_to
2411: from ssp_stoppages ss,
2414: and paa.absence_attendance_id = ss.absence_attendance_id
2415: and override_stoppage = 'N'
2416: and withhold_to is not null
2417: and ((p_effective_start_date <= withhold_from and
2418: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2419: )
2420: or
2421: (p_effective_end_date >= withhold_from and
2422: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2418: p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2419: )
2420: or
2421: (p_effective_end_date >= withhold_from and
2422: p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2423: ));
2424: --
2425: function SSP_rate (p_effective_date date) return number is
2426: --
2448: p_effective_date = trunc (p_effective_date));
2449: begin
2450: hr_utility.trace (l_proc||' p_effective_date = '||to_char (p_effective_date));
2451: --
2452: hr_general.assert_condition (all_parameters_valid);
2453: --
2454: end check_parameters;
2455: --
2456: begin
2509: begin
2510: hr_utility.trace (' p_start_date = '||to_char (p_start_date));
2511: hr_utility.trace (' p_end_date = '||to_char (p_end_date));
2512: hr_utility.trace (' p_PIW_id = '||to_char (p_PIW_id));
2513: hr_general.assert_condition (all_parameters_valid);
2514: end check_parameters;
2515: begin
2516: hr_utility.set_location('Entering:'||l_proc,1);
2517: --
2610: hr_utility.trace(l_proc||' p_sickness_end_date = '
2611: ||to_char (p_sickness_end_date));
2612: hr_utility.trace(l_proc||' p_pregnancy_related_illness = '
2613: ||p_pregnancy_related_illness);
2614: hr_general.assert_condition (all_parameters_valid);
2615: end check_parameters;
2616: begin
2617: hr_utility.set_location('Entering:'||l_proc,1);
2618: --
2785: --
2786: -- Handle the case where the end date would exceed the
2787: -- maximum date that Oracle can deal with.
2788: --
2789: end_disqualifying_period := hr_general.end_of_time;
2790: --
2791: end;
2792: --
2793: -- the previously calculated last entitlement day is
2882: SSP_rate_figure := SSP_rate (PIW_start_date);
2883: --
2884: l_nos_absences := 0;
2885: absence_table := empty_absence_table;
2886: l_max_absence_end_date := hr_general.start_of_time;
2887: --
2888: for each_abs_tab in PIW loop
2889: l_max_absence_start_date := each_abs_tab.sickness_start_date;
2890: l_max_absence_end_date := each_abs_tab.sickness_end_date;
2963: -- than the Saturday after the sickness_start_date, which
2964: -- causes an error if the sickness_start_date is itself a
2965: -- Saturday.
2966: end_date := least(next_day(next_day(l_sickness_start_date,l_sunday) -7, l_saturday),
2967: nvl (rate_end_date, hr_general.end_of_time),last_entitled_day);
2968: --
2969: if start_date > last_entitled_day then
2970: -- There is a stoppage which prevents the entry creation
2971: --
2994: -- this is not legislation specific.
2995: --
2996: cal_end_date := l_max_absence_end_date;
2997: --
2998: if cal_end_date is null or cal_end_date = hr_general.end_of_time then
2999: cal_end_date := l_max_absence_start_date +
3000: ((nvl(g_SSP_legislation.MAXIMUM_SSP_PERIOD,28) + 1) * 7);
3001: else
3002: cal_end_date := ssp_smp_support_pkg.end_of_week(l_max_absence_end_date);
3025: -- qualifying_days_in_period so many times.
3026: --
3027: stoppage_table := empty_stoppage_table;
3028: l_nos_stoppages := 0;
3029: l_min_withheld_from := hr_general.end_of_time;
3030: l_max_withheld_to := hr_general.start_of_time;
3031: --
3032: for each_stoppage in csr_stoppages(PIW_start_date, cal_end_date)
3033: loop
3026: --
3027: stoppage_table := empty_stoppage_table;
3028: l_nos_stoppages := 0;
3029: l_min_withheld_from := hr_general.end_of_time;
3030: l_max_withheld_to := hr_general.start_of_time;
3031: --
3032: for each_stoppage in csr_stoppages(PIW_start_date, cal_end_date)
3033: loop
3034: l_nos_stoppages := l_nos_stoppages + 1;
3289: -- calendar week end date and the withholding date.
3290: --
3291: start_date := next_day (start_date, l_sunday);
3292: end_date := least (next_day (start_date,l_saturday),
3293: nvl (rate_end_date,hr_general.end_of_time),
3294: nvl (last_entitled_day,hr_general.end_of_time));
3295: end if;
3296: --
3297: end loop;
3290: --
3291: start_date := next_day (start_date, l_sunday);
3292: end_date := least (next_day (start_date,l_saturday),
3293: nvl (rate_end_date,hr_general.end_of_time),
3294: nvl (last_entitled_day,hr_general.end_of_time));
3295: end if;
3296: --
3297: end loop;
3298: end;
3466: --
3467: -- no medical evidence was found for this absence
3468: if csr_evidence%notfound then
3469: /*BUG 2984577 Added exception */
3470: if each_absence.sickness_end_date = hr_general.end_of_time then
3471: create_stoppage (p_withhold_from => each_absence.sickness_start_date,
3472: p_reason => 'No acceptable evidence in time',
3473: p_overridden => l_stoppage_overridden);
3474: last_entitled_day := least(last_entitled_day,each_absence.sickness_start_date - 1);
3500: hr_utility.set_location('Entering:'||l_proc,1);
3501: -- the employment is scheduled to end
3502: -- bug 2984458 to avoid displaying 'Contract ends' when
3503: -- employee is deceased
3504: if (absentee.actual_termination_date < hr_general.end_of_time) and
3505: absentee.leaving_reason <>'D' then
3506: create_stoppage(p_withhold_from => absentee.actual_termination_date +1,
3507: p_reason => 'Contract ends',
3508: p_overridden => l_stoppage_overridden);
3598: --
3599: g_PIW_id := p_absence.absence_attendance_id;
3600: new_entry := empty_record;
3601: PIW_start_date := p_absence.sickness_start_date;
3602: PIW_end_date := hr_general.end_of_time;
3603: --
3604: -- Delete stoppages
3605: --
3606: for obsolete_stoppage in csr_old_stoppages loop
3614: PIW_end_date := nvl (linked_PIW_end_date (p_absence.person_id,
3615: p_absence.sickness_start_date,
3616: p_absence.sickness_end_date,
3617: p_absence.absence_attendance_id),
3618: hr_general.end_of_time);
3619: --
3620: hr_utility.trace('Piw_end_date ' || to_char(PIW_end_date,'DD-MON-YYYY'));
3621: --
3622: g_PIW_id := nvl(p_absence.linked_absence_id,p_absence.absence_attendance_id);
4069: --
4070: hr_utility.trace (l_proc||' p_person_id: '||to_char(p_person_id));
4071: hr_utility.trace (l_proc||' p_date_start: ' ||to_char(p_date_start));
4072: --
4073: hr_general.assert_condition (all_parameters_valid);
4074: --
4075: end check_parameters;
4076: --
4077: begin
4132: hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4133: hr_utility.trace (l_proc||' p_date_of_death = '||to_char (p_date_of_death));
4134: hr_utility.trace (l_proc||' p_date_of_birth = '||to_char (p_date_of_birth));
4135: --
4136: hr_general.assert_condition (all_parameters_valid);
4137: --
4138: end check_parameters;
4139: --
4140: begin
4206: else
4207: hr_utility.trace (l_proc||' p_deleting is FALSE');
4208: end if;
4209: --
4210: hr_general.assert_condition (all_parameters_valid);
4211: --
4212: end check_parameters;
4213: begin
4214: hr_utility.set_location('Entering:'||l_proc,1);
4279: hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4280: hr_utility.trace (l_proc||' p_effective_date = '
4281: ||to_char (p_effective_date));
4282: --
4283: hr_general.assert_condition (all_parameters_valid);
4284: --
4285: end check_parameters;
4286: --
4287: begin
4365: all_parameters_valid constant boolean := (p_absence_attendance_id is not null);
4366: begin
4367: hr_utility.trace (l_proc||' p_absence_attendance_id = '||to_char (p_absence_attendance_id));
4368: --
4369: hr_general.assert_condition (all_parameters_valid);
4370: --
4371: end check_parameters;
4372: begin
4373: hr_utility.set_location('Entering:'||l_proc,1);