301: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
302: and a.period_of_service_id = c_period_of_service_id
303: and c_effective_date between
304: pay.effective_start_date and pay.effective_end_date
305: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
306: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
307: and extra.assignment_id(+) = a.assignment_id
308: and extra.information_type(+) = p_information_type
309: and a.assignment_id <> c_assignment_id
302: and a.period_of_service_id = c_period_of_service_id
303: and c_effective_date between
304: pay.effective_start_date and pay.effective_end_date
305: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
306: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
307: and extra.assignment_id(+) = a.assignment_id
308: and extra.information_type(+) = p_information_type
309: and a.assignment_id <> c_assignment_id
310: ;
774: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
775: and a.period_of_service_id = c_period_of_service_id
776: and c_effective_date between
777: pay.effective_start_date and pay.effective_end_date
778: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
779: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
780: and a.assignment_id <> c_assignment_id
781: ;
782:
775: and a.period_of_service_id = c_period_of_service_id
776: and c_effective_date between
777: pay.effective_start_date and pay.effective_end_date
778: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
779: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
780: and a.assignment_id <> c_assignment_id
781: ;
782:
783: --
1477: and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1478: and a.period_of_service_id = c_period_of_service_id
1479: and c_effective_date between
1480: pay.effective_start_date and pay.effective_end_date
1481: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
1482: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
1483: and a.assignment_id <> c_assignment_id
1484: ;
1485: --
1478: and a.period_of_service_id = c_period_of_service_id
1479: and c_effective_date between
1480: pay.effective_start_date and pay.effective_end_date
1481: and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
1482: and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
1483: and a.assignment_id <> c_assignment_id
1484: ;
1485: --
1486: -- to fetch the last active/susp status date for the given assignment