[Home] [Help]
237: -- csr_mode12 = Cursor for select emp for mode 1 and 2 --
238: ------------------------------------------------------------
239: cursor csr_mode12 is
240: select /*+ ORDERED
241: INDEX(ppt, PER_PERSON_TYPES_PK) */
242: -- max(peo.person_id) p_id,
243: peo.person_id p_id,
244: peo.full_name,
245: peo.national_identifier,
255: from per_all_people_f peo,
256: per_all_assignments_f asg,
257: per_periods_of_service pps,
258: per_person_type_usages_f ptu,
259: per_person_types ppt,
260: pay_all_payrolls_f ppf, -- Bug#8497477
261: hr_soft_coding_keyflex scl -- Bug#8497477
262: where peo.person_id = asg.person_id
263: and peo.business_group_id = g_business_group_id
2043: and (pps.actual_termination_date is null or
2044: p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2045: and exists (select 1
2046: from per_person_type_usages_f ptu,
2047: per_person_types ppt
2048: where ptu.person_id = peo.person_id
2049: and ptu.person_type_id = ppt.person_type_id
2050: and ppt.system_person_type in ('EMP','EMP_APL')
2051: and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2083: and (pps.actual_termination_date is null or
2084: p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2085: and exists (select 1
2086: from per_person_type_usages_f ptu,
2087: per_person_types ppt
2088: where ptu.person_id = peo.person_id
2089: and ptu.person_type_id = ppt.person_type_id
2090: and ppt.system_person_type in ('EMP','EMP_APL')
2091: and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2121: and (pps.actual_termination_date is null or
2122: p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2123: and exists (select 1
2124: from per_person_type_usages_f ptu,
2125: per_person_types ppt
2126: where ptu.person_id = peo.person_id
2127: and ptu.person_type_id = ppt.person_type_id
2128: and ppt.system_person_type in ('EMP','EMP_APL')
2129: and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2159: and (pps.actual_termination_date is null or
2160: p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2161: and exists (select 1
2162: from per_person_type_usages_f ptu,
2163: per_person_types ppt
2164: where ptu.person_id = peo.person_id
2165: and ptu.person_type_id = ppt.person_type_id
2166: and ppt.system_person_type in ('EMP','EMP_APL')
2167: and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)