[Home] [Help]
306: --
307: -- Get the details of an absence, given its ID.
308: --
309: select *
310: from per_absence_attendances
311: where absence_attendance_id = p_absence_attendance_id;
312: --
313: g_absence per_absence_attendances%rowtype;
314: --
309: select *
310: from per_absence_attendances
311: where absence_attendance_id = p_absence_attendance_id;
312: --
313: g_absence per_absence_attendances%rowtype;
314: --
315: cursor PIW is
316: --
317: -- Get all the PIWs which link to this one
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
327: where (g_PIW_id = absence_attendance_id and linked_absence_id is null)
328: or g_PIW_ID = linked_absence_id
329: order by sickness_start_date;
330: --
645: --
646: -- Get all the absences affected by the current transaction.
647: --
648: select absence.*
649: from per_absence_attendances ABSENCE,
650: ssp_temp_affected_rows TRANSACTION
651: where
652: ( (transaction.PIW_ID = absence.absence_attendance_id and
653: absence.linked_absence_id IS NULL)
681: then
682: --
683: -- update the absence's linked absence ID to the calculated one
684: --
685: update per_absence_attendances
686: set linked_absence_id = l_new_linked_absence_id
687: where absence_attendance_id = new_link.absence_attendance_id;
688: --
689: if l_new_linked_absence_id is not null
720: cursor csr_contiguous_absences is
721: select absence_attendance_id,
722: sickness_start_date,
723: sickness_end_date
724: from per_absence_attendances
725: where person_id = p_person_id
726: and sickness_start_date is not null
727: and sickness_start_date < p_sickness_start_date
728: and linked_absence_id is null
730: for update;
731: --
732: cursor csr_linked_absence (x_sickness_start_date date) is
733: select absence_attendance_id
734: from per_absence_attendances
735: where sickness_start_date = x_sickness_start_date
736: and person_id = p_person_id;
737: --
738: l_PIW_id number := null;
788: exit when(previous.sickness_end_date <> l_current_sickness_start - 1 or
789: previous.absence_attendance_id = l_PIW_ID);
790: --
791: -- Set the PIW ID of the previous absence the same as the current one
792: update per_absence_attendances
793: set linked_absence_id = l_PIW_ID
794: where current of csr_contiguous_absences;
795: --
796: l_current_sickness_start := previous.sickness_start_date;
819: --for which the person has absences
820: cursor csr_abs_days(p_start date, p_end date) is
821: select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
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: --
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;
916: --for which the person has absences
917: cursor csr_abs_days(p_start date, p_end date) is
918: select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
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: --
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: --
935: cursor csr_linked_id is
933: and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
934: --
935: cursor csr_linked_id is
936: select linked_absence_id
937: from per_absence_attendances
938: where absence_attendance_id = p_absence_attendance_id;
939: --
940: l_days_absent number := 0;
941: l_linked number := null;
1035: --
1036: select abs.linked_absence_id,
1037: abs.sickness_start_date,
1038: abs.sickness_end_date
1039: from per_absence_attendances ABS
1040: where abs.sickness_start_date is not null
1041: and person_id = p_person_id
1042: and abs.sickness_start_date = (
1043: --
1041: and person_id = p_person_id
1042: and abs.sickness_start_date = (
1043: --
1044: select min (abs2.sickness_start_date)
1045: from per_absence_attendances ABS2
1046: where abs2.sickness_start_date >= p_old_sickness_end_date
1047: and person_id = p_person_id);
1048: --
1049: l_proc varchar2 (72) := g_package||'check_sickness_date_change';
1147: -- Return a row if there are any sickness absences in a series of
1148: -- which this absence is first.
1149: --
1150: select 1
1151: from per_absence_attendances
1152: where linked_absence_id = p_absence_attendance_id;
1153: --
1154: cursor csr_next_absence is
1155: --
1156: -- Get the absence which is next in the linked series of which this
1157: -- absence is a part.
1158: --
1159: select max (sickness_end_date)
1160: from per_absence_attendances
1161: where p_linked_absence_id = linked_absence_id
1162: and sickness_end_date <= p_sickness_start_date;
1163: --
1164: cursor csr_previous_absence is
1166: -- Get the absence which is the previous one in the linked series of
1167: -- which this absence is a part.
1168: --
1169: select min (sickness_start_date)
1170: from per_absence_attendances
1171: where (l_PIW_id = linked_absence_id and absence_attendance_id is null)
1172: or (l_PIW_id = absence_attendance_id and linked_absence_id is null)
1173: and sickness_start_date >= p_sickness_end_date;
1174: --
1317: --
1318: -- Get the linked ID from the absence
1319: cursor csr_linked_id(p_absence_id number) is
1320: select linked_absence_id
1321: from per_absence_attendances
1322: where absence_attendance_id = p_absence_id;
1323: --
1324: cursor csr_start_date(p_absence_id number) is
1325: select sickness_start_date
1322: where absence_attendance_id = p_absence_id;
1323: --
1324: cursor csr_start_date(p_absence_id number) is
1325: select sickness_start_date
1326: from per_absence_attendances
1327: where absence_attendance_id = p_absence_id;
1328: --
1329: cursor csr_end_date(p_absence_id number) is
1330: select sickness_end_date
1327: where absence_attendance_id = p_absence_id;
1328: --
1329: cursor csr_end_date(p_absence_id number) is
1330: select sickness_end_date
1331: from per_absence_attendances
1332: where (absence_attendance_id = p_absence_id
1333: or
1334: linked_absence_id = p_absence_id)
1335: order by sickness_end_date desc;
1376: cursor PIW_series(p_start_date date) is
1377: select sickness_start_date,
1378: sickness_end_date,
1379: absence_attendance_id
1380: from per_absence_attendances
1381: where person_id = p_person_id
1382: and sickness_start_date is not null
1383: and sickness_start_date < p_sickness_start_date
1384: and (p_start_date is null or
1473: cursor PIW_series(p_start_date date) is
1474: select sickness_start_date,
1475: sickness_end_date,
1476: absence_attendance_id
1477: from per_absence_attendances
1478: where person_id = p_person_id
1479: and sickness_start_date is not null
1480: and sickness_start_date < p_sickness_start_date
1481: and (p_start_date is null or
1488: cursor PIW_series(p_start_date date) is
1489: select sickness_start_date,
1490: sickness_end_date,
1491: absence_attendance_id
1492: from per_absence_attendances per,
1493: per_periods_of_service service
1494: where per.person_id = p_person_id
1495: and per.sickness_start_date is not null
1496: and per.sickness_start_date < p_sickness_start_date
1604: cursor PIW_series(p_start_date date) is
1605: select sickness_start_date,
1606: sickness_end_date,
1607: absence_attendance_id
1608: from per_absence_attendances
1609: where person_id = p_person_id
1610: and sickness_start_date is not null
1611: and sickness_start_date < p_sickness_start_date
1612: and (p_start_date is null or
1619: cursor PIW_series(p_start_date date) is
1620: select sickness_start_date,
1621: sickness_end_date,
1622: absence_attendance_id
1623: from per_absence_attendances per,
1624: per_periods_of_service service
1625: where per.person_id = p_person_id
1626: and per.sickness_start_date is not null
1627: and per.sickness_start_date < p_sickness_start_date
1735: cursor PIW_series(p_start_date date) is
1736: select sickness_start_date,
1737: sickness_end_date,
1738: absence_attendance_id
1739: from per_absence_attendances
1740: where person_id = p_person_id
1741: and sickness_start_date is not null
1742: and sickness_start_date > p_sickness_start_date
1743: and (p_start_date is null or
1800: cursor PIW_series(p_start_date date) is
1801: select sickness_start_date,
1802: sickness_end_date,
1803: absence_attendance_id
1804: from per_absence_attendances
1805: where person_id = p_person_id
1806: and sickness_start_date is not null
1807: and sickness_start_date > p_sickness_start_date
1808: and (p_start_date is null or
1869: cursor PIW_series(p_start_date date) is
1870: select sickness_start_date,
1871: sickness_end_date,
1872: absence_attendance_id
1873: from per_absence_attendances
1874: where person_id = p_person_id
1875: and sickness_start_date is not null
1876: and sickness_start_date > p_sickness_start_date
1877: and (p_start_date is null or
2235: --
2236: end SSP_is_installed;
2237: --------------------------------------------------------------------------------
2238: --------------------------------------------------------------------------------
2239: function ENTITLED_TO_SSP(p_absence per_absence_attendances%rowtype)
2240: --
2241: -- See header for description of this function.
2242: --
2243: return boolean is
2341: l_end_date date;
2342:
2343: cursor get_end_date is
2344: select nvl(sickness_end_date, l_01_October_06 - 1)
2345: from per_absence_attendances
2346: where ((sickness_end_date is null and sickness_start_date < l_01_October_06)
2347: or
2348: sickness_end_date < l_01_October_06)
2349: and person_id = p_absence.person_id
2522: ))
2523: union all
2524: select withhold_from, withhold_to
2525: from ssp_stoppages ss,
2526: per_absence_attendances paa
2527: where paa.linked_absence_id = g_PIW_id
2528: and paa.absence_attendance_id = ss.absence_attendance_id
2529: and override_stoppage = 'N'
2530: and withhold_to is not null
4182: --
4183: -- Get any PIW which may have been linked to the prior employment SSP.
4184: --
4185: select nvl (linked_absence_id, absence_attendance_id) PIW_ID
4186: from per_absence_attendances
4187: where sickness_start_date
4188: <= (p_date_start + g_SSP_legislation.linking_period_days)
4189: and person_id = p_person_id;
4190: --
4245: select linked_absence_id,
4246: absence_attendance_id,
4247: sickness_start_date,
4248: sickness_end_date
4249: from per_absence_attendances
4250: where person_id = p_person_id
4251: and sickness_start_date is not null;
4252: --
4253: l_proc varchar2(72) := g_package||'person_control';
4393: -- Find the PIW whose start date is the same as the date of the
4394: -- average earnings calculation.
4395: --
4396: select *
4397: from per_absence_attendances
4398: where sickness_start_date = p_effective_date
4399: and person_id = p_person_id;
4400: --
4401: l_proc varchar2 (72) := g_package||'earnings_control';