[Home] [Help]
247: effective_start_date and effective_end_date;
248:
249: l_effective_date date;
250: l_aggregated_paye_flag per_all_people_f.per_information10%type;
251: l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
252: l_object_version_number number;
253: l_assignment_extra_info_id number;
254: --
255: -- fetch the tax PAYE reference for the given asg. on the effective date
258: c_effective_date in date) IS
259: SELECT hsck.segment1, period_of_service_id
260: FROM hr_soft_coding_keyflex hsck,
261: pay_all_payrolls_f papf,
262: per_all_assignments_f paaf
263: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
264: AND papf.payroll_id = paaf.payroll_id
265: AND paaf.assignment_id = c_assignment_id
266: AND c_effective_date between
287: extra.aei_information1,
288: extra.aei_information2,
289: extra.aei_information3,
290: extra.aei_information4
291: from per_all_assignments_f a,
292: pay_all_payrolls_f pay,
293: hr_soft_coding_keyflex flex,
294: per_assignment_status_types past,
295: per_assignment_extra_info extra
313: -- to fetch the last active/susp status date for the given assignment
314: --
315: cursor csr_asg_last_active_date(c_assignment_id number) is
316: select max(effective_end_date)
317: from per_all_assignments_f a,
318: per_assignment_status_types past
319: where a.assignment_id = c_assignment_id
320: and a.assignment_status_type_id = past.assignment_status_type_id
321: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
335: -- the earliest aggregation start date and manual issue date/override date.
336: --
337: cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
338: select 1
339: from per_all_assignments_f a
340: where a.assignment_id = c_assignment_id
341: and a.effective_end_date >= c_start_date
342: and a.effective_start_date <= c_end_date;
343:
726: c_effective_date in date) IS
727: SELECT hsck.segment1, period_of_service_id
728: FROM hr_soft_coding_keyflex hsck,
729: pay_all_payrolls_f papf,
730: per_all_assignments_f paaf
731: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
732: AND papf.payroll_id = paaf.payroll_id
733: AND paaf.assignment_id = c_assignment_id
734: AND c_effective_date between
758: extra.aei_information1,
759: extra.aei_information2,
760: extra.aei_information3,
761: extra.aei_information4
762: from per_all_assignments_f a,
763: pay_all_payrolls_f pay,
764: hr_soft_coding_keyflex flex,
765: per_assignment_status_types past,
766: per_assignment_extra_info extra
784: -- to fetch the last active/susp status date for the given assignment
785: --
786: cursor csr_asg_last_active_date(c_assignment_id number) is
787: select max(effective_end_date)
788: from per_all_assignments_f a,
789: per_assignment_status_types past
790: where a.assignment_id = c_assignment_id
791: and a.assignment_status_type_id = past.assignment_status_type_id
792: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
806: -- the earliest aggregation start date and manual issue date/override date.
807: --
808: cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
809: select 1
810: from per_all_assignments_f a
811: where a.assignment_id = c_assignment_id
812: and a.effective_end_date >= c_start_date
813: and a.effective_start_date <= c_end_date;
814:
1408: --
1409:
1410: l_effective_date date;
1411: l_aggregated_paye_flag per_all_people_f.per_information10%type;
1412: l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
1413: l_assignment_id per_all_assignments_f.assignment_id%type;
1414: l_object_version_number number;
1415:
1416: l_aei_information3 per_assignment_extra_info.aei_information3%type;
1409:
1410: l_effective_date date;
1411: l_aggregated_paye_flag per_all_people_f.per_information10%type;
1412: l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
1413: l_assignment_id per_all_assignments_f.assignment_id%type;
1414: l_object_version_number number;
1415:
1416: l_aei_information3 per_assignment_extra_info.aei_information3%type;
1417: l_aei_information4 per_assignment_extra_info.aei_information4%type;
1429: c_effective_date in date) IS
1430: SELECT hsck.segment1, period_of_service_id, person_id
1431: FROM hr_soft_coding_keyflex hsck,
1432: pay_all_payrolls_f papf,
1433: per_all_assignments_f paaf
1434: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
1435: AND papf.payroll_id = paaf.payroll_id
1436: AND paaf.assignment_id = c_assignment_id
1437: AND c_effective_date between
1461: extra.aei_information1,
1462: extra.aei_information2,
1463: extra.aei_information3,
1464: extra.aei_information4
1465: from per_all_assignments_f a,
1466: pay_all_payrolls_f pay,
1467: hr_soft_coding_keyflex flex,
1468: per_assignment_status_types past,
1469: per_assignment_extra_info extra
1486: -- to fetch the last active/susp status date for the given assignment
1487: --
1488: cursor csr_asg_last_active_date(c_assignment_id number) is
1489: select max(effective_end_date)
1490: from per_all_assignments_f a,
1491: per_assignment_status_types past
1492: where a.assignment_id = c_assignment_id
1493: and a.assignment_status_type_id = past.assignment_status_type_id
1494: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
1508: -- the earliest aggregation start date and manual issue date/override date.
1509: --
1510: cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
1511: select 1
1512: from per_all_assignments_f a
1513: where a.assignment_id = c_assignment_id
1514: and a.effective_end_date >= c_start_date
1515: and a.effective_start_date <= c_end_date;
1516: