[Home] [Help]
12: FUNCTION assg_aggr_possible (p_person_id IN NUMBER,
13: p_effective_date IN DATE,
14: p_message IN VARCHAR2) RETURN boolean
15: IS
16: l_segment_prev hr_soft_coding_keyflex.segment1%TYPE;
17: l_count_assignments NUMBER;
18: l_count_paye_link NUMBER;
19: l_same_tax_district BOOLEAN default FALSE;
20: l_same_paye_element_value BOOLEAN default TRUE;
68: AND p_effective_date BETWEEN effective_start_date AND effective_end_date ;
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
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
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
118: and ele.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)
155: and paaf.effective_start_date <= p_end_date
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
566: and paaf.effective_start_date <= c_end_date-1
567: and paaf.effective_end_date >= c_start_date
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
579: and aaf.assignment_id not in (paaf.assignment_id)
580: and aaf.effective_start_date <= c_end_date-1
986: cursor get_curr_td_info(c_assignment_id in number,
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
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
1032: AND paaf.payroll_id = papf.payroll_id
1033: AND c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
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
1597: and pay.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
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
1612: and p_effective_date between paf.effective_start_date and paf.effective_end_date
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
1765: AND hsck1.SEGMENT1 = (hsck.segment1)
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
1778: AND hsck1.SEGMENT1 = (hsck.segment1)
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
1791: AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
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
2003: and papf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
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
2031: and paaf.payroll_id = papf.payroll_id