870: -- Bug 4650086
871: CURSOR c_get_adj_svc_date
872: IS
873: SELECT pps.adjusted_svc_date
874: FROM per_periods_of_service pps,
875: per_all_people_f pap
876: WHERE pap.person_id = p_person_id
877: AND pps.person_id = pap.person_id
878: AND p_effective_date BETWEEN pap.effective_start_date
877: AND pps.person_id = pap.person_id
878: AND p_effective_date BETWEEN pap.effective_start_date
879: AND pap.effective_end_date
880: AND pps.date_start = (SELECT MAX (pps1.date_start)
881: FROM per_periods_of_service pps1
882: WHERE pps1.person_id = pps.person_id
883: AND pps1.date_start <= p_effective_date);
884: -- ORDER BY pps.adjusted_svc_date DESC;
885: