[Home] [Help]
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
264: and (g_payroll_id is null or
265: asg.payroll_id = g_payroll_id)
266: and peo.person_id = ptu.person_id
267: and ptu.person_type_id = ppt.person_type_id
268: and pps.period_of_service_id = asg.period_of_service_id
269: and asg.payroll_id is not null
270: -- Start bug#8497477 : added pay_all_payrolls_f and hr_soft_coding_keyflex for tax reference check
271: and ppf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
272: and ppf.payroll_id = asg.payroll_id
273: and g_effective_date between ppf.effective_start_date and ppf.effective_end_date
274: -- End bug#8497477
338: sck.segment1 tax_ref
339: from per_all_assignments_f asg,
340: pay_all_payrolls_f pay,
341: per_assignment_status_types pat,
342: hr_soft_coding_keyflex sck
343: where asg.person_id = p_person_id
344: and ( -- no need to fetch payroll, but do check the tax ref
345: (p_aggr_flag = 'Y' and
346: ((g_payroll_id is not null and sck.segment1 = g_tax_ref)
387: -- This cursor returns tax ref id based on the payroll --
388: ---------------------------------------------------------
389: cursor get_payroll_details IS
390: select sck.segment1, payroll_name
391: from hr_soft_coding_keyflex sck,
392: pay_all_payrolls_f pay
393: where sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
394: and pay.payroll_id = g_payroll_id
395: and g_effective_date between pay.effective_start_date and pay.effective_end_date;
401: select assignment_id
402: from per_all_assignments_f asg,
403: pay_all_payrolls_f pay,
404: per_assignment_status_types pat,
405: hr_soft_coding_keyflex sck
406: where asg.person_id = p_person_id
407: and sck.segment1 = p_paye_ref
408: and asg.payroll_id = pay.payroll_id
409: and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
2175: p_asg_no varchar2 default null) is
2176: select count(*)
2177: from per_all_assignments_f asg,
2178: pay_all_payrolls_f pay,
2179: hr_soft_coding_keyflex sck
2180: where asg.person_id = p_person_id
2181: and asg.payroll_id = pay.payroll_id
2182: and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2183: and sck.segment1 = p_tax_district
2193: select count(*)
2194: from per_all_people_f peo,
2195: per_all_assignments_f asg,
2196: pay_all_payrolls_f pay,
2197: hr_soft_coding_keyflex sck
2198: where peo.person_id = p_person_id
2199: and asg.person_id = peo.person_id
2200: and asg.payroll_id = pay.payroll_id
2201: and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2206: and asg.effective_start_date between peo.effective_start_date and peo.effective_end_date
2207: and asg.assignment_id not in (select assignment_id
2208: from per_all_assignments_f asg,
2209: pay_all_payrolls_f pay,
2210: hr_soft_coding_keyflex sck
2211: where asg.person_id = p_person_id
2212: and asg.payroll_id = pay.payroll_id
2213: and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2214: and sck.segment1 = p_tax_district
2529: cursor csr_asg is
2530: select paaf.assignment_id
2531: from per_all_assignments_f paaf,
2532: pay_payrolls_f pay,
2533: hr_soft_coding_keyflex sck
2534: where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =l_asg_id)
2535: and pay.payroll_id = paaf.payroll_id
2536: and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
2537: and sck.SEGMENT1 = p_m34_rec.paye_ref