[Home] [Help]
9: FUNCTION assg_aggr_possible (p_person_id IN NUMBER,
10: p_effective_date IN DATE,
11: p_message IN VARCHAR2) RETURN boolean
12: IS
13: l_segment_prev hr_soft_coding_keyflex.segment1%TYPE;
14: l_count_assignments NUMBER;
15: l_count_paye_link NUMBER;
16: l_same_tax_district BOOLEAN default FALSE;
17: l_same_paye_element_value BOOLEAN default TRUE;
61: AND p_effective_date BETWEEN effective_start_date AND effective_end_date ;
62:
63: CURSOR cur_tax_reference IS
64: SELECT COUNT(hsck.segment1) Num, hsck.segment1 tax_district
65: FROM hr_soft_coding_keyflex hsck,
66: pay_all_payrolls_f papf,
67: per_all_assignments_f paaf,
68: per_assignment_status_types past
69: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
76: AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
77: GROUP BY hsck.segment1;
78:
79: /*BUG 2879391 Added the cursor to compare PAYE info for multiple assignments*/
80: /*BUG 4520393 added joins with pay_all_payrolls_f and hr_soft_coding_keyflex to validate
81: PAYE info only for assignments within the same PAYE reference*/
82:
83: -- Start of BUG 5671777-9
84: -- Added code to fetch PAYE info of the multiple assignments with same CPE
103: pay_input_values_f inv,
104: pay_element_links_f lnk, pay_element_types_f elt,
105: per_all_assignments_f paa,
106: pay_all_payrolls_f ppf,
107: hr_soft_coding_keyflex scl
108: where ele.element_entry_id = eev.element_entry_id
109: -- and p_effective_date between ele.effective_start_date and ele.effective_end_date
110: and ele.effective_start_date <= p_end_date
111: and ele.effective_end_date >= p_start_date
140:
141: and exists ( SELECT 1
142: FROM per_all_assignments_f paaf,
143: pay_all_payrolls_f papf,
144: hr_soft_coding_keyflex hsck,
145: per_assignment_status_types past
146: WHERE paaf.person_id = p_person_id
147: and paaf.assignment_id not in (paa.assignment_id)
148: and paaf.effective_start_date <= p_end_date