[Home] [Help]
125: from
126: per_all_people_f pap,
127: per_all_assignments_f paf,
128: pay_all_payrolls_f pay,
129: hr_soft_coding_keyflex hsc
130: where paf.person_id = pap.person_id
131: and paf.assignment_id = p_assignment_id
132: and paf.payroll_id = pay.payroll_id
133: and pay.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
142: cursor csr_oldest_asg is
143: select min(paf.assignment_id) from
144: per_all_assignments_f paf,
145: pay_all_payrolls_f pay,
146: hr_soft_coding_keyflex hsc
147: where paf.payroll_id= pay.payroll_id
148: and pay.soft_coding_keyflex_id= hsc.soft_coding_keyflex_id
149: and p_effective_date between pay.effective_start_date and pay.effective_end_date
150: and p_effective_date between paf.effective_start_date and paf.effective_end_date
500: ,REGEXP_REPLACE( nvl(
501: (SELECT MIN(paaf2.assignment_number)
502: FROM per_all_assignments_f paaf2,
503: pay_all_payrolls_f papf,
504: hr_soft_coding_keyflex hsck
505: WHERE paaf2.person_id = asg.person_id
506: AND papf.payroll_id = paaf2.payroll_id
507: AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
508: AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
515: ,
516: (SELECT MIN(paaf2.assignment_number)
517: FROM per_all_assignments_f paaf2,
518: pay_all_payrolls_f papf,
519: hr_soft_coding_keyflex hsck
520: WHERE paaf2.person_id = asg.person_id
521: AND papf.payroll_id = paaf2.payroll_id
522: AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
523: AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
532: ,per_all_assignments_f asg
533: ,per_assignment_status_types past
534: --,per_periods_of_service serv
535: ,pay_all_payrolls_f pay
536: ,hr_soft_coding_keyflex sck
537: WHERE --pap.current_employee_flag = 'Y'
538: pap.person_id = asg.person_id
539: AND asg.business_group_id = l_business_group_id
540: AND asg.assignment_status_type_id = past.assignment_status_type_id