[Home] [Help]
69:
70: CURSOR cur_tax_reference IS
71: SELECT COUNT(hsck.segment1) Num, hsck.segment1 tax_district
72: FROM hr_soft_coding_keyflex hsck,
73: pay_all_payrolls_f papf,
74: per_all_assignments_f paaf,
75: per_assignment_status_types past
76: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
77: AND papf.payroll_id =paaf.payroll_id
83: AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
84: GROUP BY hsck.segment1;
85:
86: /*BUG 2879391 Added the cursor to compare PAYE info for multiple assignments*/
87: /*BUG 4520393 added joins with pay_all_payrolls_f and hr_soft_coding_keyflex to validate
88: PAYE info only for assignments within the same PAYE reference*/
89:
90: -- Start of BUG 5671777-9
91: -- Added code to fetch PAYE info of the multiple assignments with same CPE
109: pay_element_entry_values_f eev,
110: pay_input_values_f inv,
111: pay_element_links_f lnk, pay_element_types_f elt,
112: per_all_assignments_f paa,
113: pay_all_payrolls_f ppf,
114: hr_soft_coding_keyflex scl
115: where ele.element_entry_id = eev.element_entry_id
116: -- and p_effective_date between ele.effective_start_date and ele.effective_end_date
117: and ele.effective_start_date <= p_end_date
146: and ppf.effective_end_date >= p_start_date
147:
148: and exists ( SELECT 1
149: FROM per_all_assignments_f paaf,
150: pay_all_payrolls_f papf,
151: hr_soft_coding_keyflex hsck,
152: per_assignment_status_types past
153: WHERE paaf.person_id = p_person_id
154: and paaf.assignment_id not in (paa.assignment_id)
557: -- which shares same CPE and PAYE reference
558: --
559: cursor cur_chk_multiple_asg(c_person_id in number, c_start_date date, c_end_date date) IS
560: select 1
561: from pay_all_payrolls_f papf,
562: per_all_assignments_f paaf,
563: hr_soft_coding_keyflex hsck,
564: per_assignment_status_types past
565: where paaf.person_id = c_person_id
570: and papf.payroll_id =paaf.payroll_id
571: and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
572: and c_end_date between papf.effective_start_date and papf.effective_end_date
573: and exists ( select 1
574: from pay_all_payrolls_f apf,
575: per_all_assignments_f aaf,
576: hr_soft_coding_keyflex sck,
577: per_assignment_status_types ast
578: where aaf.person_id = c_person_id
987: c_effective_date in date) is
988: SELECT --COUNT(hsck.segment1) Num,
989: hsck.segment1 tax_district
990: FROM hr_soft_coding_keyflex hsck,
991: pay_all_payrolls_f papf,
992: per_all_assignments_f paaf
993: WHERE paaf.assignment_id= c_assignment_id
994: AND papf.payroll_id =paaf.payroll_id
995: AND hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
1023: pay_element_entry_values_f eev,
1024: pay_input_values_f inv,
1025: pay_element_links_f lnk,
1026: pay_element_types_f elt,
1027: pay_all_payrolls_f papf,
1028: per_all_assignments_f paaf,
1029: hr_soft_coding_keyflex hsck
1030: WHERE paaf.person_id = c_person_id
1031: AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1588: select paf.assignment_id
1589: from
1590: per_all_people_f pap,
1591: per_all_assignments_f paf,
1592: pay_all_payrolls_f pay,
1593: hr_soft_coding_keyflex hsc
1594: where paf.person_id = pap.person_id
1595: and paf.assignment_id = p_assignment_id
1596: and paf.payroll_id = pay.payroll_id
1603: -- find minimum assignment
1604: cursor csr_oldest_asg is
1605: select min(paf.assignment_id) from
1606: per_all_assignments_f paf,
1607: pay_all_payrolls_f pay,
1608: hr_soft_coding_keyflex hsc
1609: where paf.payroll_id= pay.payroll_id
1610: and pay.soft_coding_keyflex_id= hsc.soft_coding_keyflex_id
1611: and p_effective_date between pay.effective_start_date and pay.effective_end_date
1756: ,pap.business_group_id business_group_id
1757: ,nvl(
1758: (SELECT MIN(paaf2.assignment_number)
1759: FROM per_all_assignments_f paaf2,
1760: pay_all_payrolls_f papf1,
1761: hr_soft_coding_keyflex hsck1
1762: WHERE paaf2.person_id = paf.person_id
1763: AND papf1.payroll_id = paaf2.payroll_id
1764: AND papf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
1769: AND paaf2.effective_end_date)
1770: ,
1771: (SELECT MIN(paaf2.assignment_number)
1772: FROM per_all_assignments_f paaf2,
1773: pay_all_payrolls_f papf1,
1774: hr_soft_coding_keyflex hsck1
1775: WHERE paaf2.person_id = paf.person_id
1776: AND papf1.payroll_id = paaf2.payroll_id
1777: AND papf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
1782: ) primary_assignment_number
1783: ,hsck.segment1 paye_reference
1784: FROM per_all_people_f pap
1785: ,per_all_assignments_f paf
1786: ,pay_all_payrolls_f papf
1787: ,hr_soft_coding_keyflex hsck
1788: WHERE paf.person_id = pap.person_id
1789: AND pap.person_id = p_person_id
1790: AND papf.payroll_id=paf.payroll_id
1994: is
1995: select paaf.assignment_id assignment_id
1996: ,sck.segment1 paye_ref
1997: from per_all_assignments_f paaf,
1998: pay_all_payrolls_f papf,
1999: hr_soft_coding_keyflex sck
2000: where paaf.person_id = p_person_id
2001: and paaf.payroll_id = papf.payroll_id
2002: and paaf.business_group_id = p_business_group_id
2022: CURSOR csr_get_agg_asg(p_assignment_id NUMBER, p_paye_ref varchar2,p_business_group_id NUMBER)
2023: is
2024: select paaf.assignment_id assignment_id
2025: from per_all_assignments_f paaf,
2026: pay_all_payrolls_f papf,
2027: hr_soft_coding_keyflex sck
2028: where paaf.person_id = p_person_id
2029: and paaf.assignment_id <> p_assignment_id
2030: and paaf.business_group_id = p_business_group_id