1 PACKAGE BODY per_asg_aggr AS
2 /* $Header: peaggasg.pkb 120.4 2007/10/31 15:07:33 rlingama noship $ */
3
4 /*---------------------------------------------------
5 --FUNCTION: assg_aggr_possible
6 Function to check if multiple assignments with
7 same tax district exist for this person.
8 ---------------------------------------------------*/
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;
18 l_new_paye_element_value VARCHAR(100) default NULL;
19 l_old_paye_element_value VARCHAR(100) default NULL;
20 l_sys_per_type varchar2(30);
21 l_ni_flag varchar2(10);
22 l_paye_flag varchar2(10);
23
24 -- Start of Bug 5671777-9
25 l_effective_end_date DATE;
26 l_new_cpe_strat_date DATE;
27 l_old_cpe_strat_date DATE;
28 l_old_assignment_id NUMBER;
29 l_new_assignment_id NUMBER;
30 l_old_effective_end_date DATE;
31 l_new_effective_end_date DATE;
32 l_old_effective_start_date DATE;
33 l_new_effective_start_date DATE;
34 -- End of Bug 5671777-9
35
36 --
37 -- Start of Bug 5671777-9
38 -- Changed the cursor to fecth PAYE agg flag effective end date
39 cursor cur_get_aggr_flag(c_person_id in number,
40 c_effective_date in date) is
41 select per_information10,effective_end_date
42 from per_all_people_f
43 where person_id = c_person_id
44 and c_effective_date between effective_start_date and effective_end_date;
45 -- End of Bug 5671777-9
46
47 cursor cur_person_type (c_person_id in number,
48 c_effective_date in date) is
49 select typ.system_person_type
50 from per_person_types typ,
51 per_all_people_f ppf
52 where ppf.person_id = c_person_id
53 and ppf.person_type_id = typ.person_type_id
54 and c_effective_date between
55 ppf.effective_start_date and ppf.effective_end_date;
56 --
57 CURSOR cur_rows_assg IS
58 SELECT count(*)
59 FROM per_all_assignments_f
60 WHERE person_id = p_person_id
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
70 AND papf.payroll_id =paaf.payroll_id
71 AND past.assignment_status_type_id = paaf.assignment_status_type_id
72 AND paaf.person_id = p_person_id
73 /*Commented for bug fix 3949536*/
74 --AND past.per_system_status='ACTIVE_ASSIGN'
75 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
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
85 --
86
87 CURSOR cur_paye_element_values(p_tax_district varchar2,p_start_date date,p_end_date date) IS
88 SELECT nvl(min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)),0)||
89 nvl(min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)),0)||
90 nvl(min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)),0)||
91 nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)),0)||
92 nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)),0)||
93 nvl(min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)),0)||
94 nvl(ele.entry_information1,0)||
95 nvl(ele.entry_information2,0) VALUE,
96 pay_gb_eoy_archive.get_agg_active_start(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date)) cpe_start_date,
97 paa.assignment_id assignment_id,
98 eev.effective_start_date effective_start_date,
99 eev.effective_end_date effective_end_date
100 from
101 pay_element_entries_f ele,
102 pay_element_entry_values_f eev,
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
112 and eev.input_value_id + 0 = inv.input_value_id
113 -- and p_effective_date between eev.effective_start_date and eev.effective_end_date
114 and eev.effective_start_date <= p_end_date
115 and eev.effective_end_date >= p_start_date
116 and inv.element_type_id = elt.element_type_id
117 -- and p_effective_date between inv.effective_start_date and inv.effective_end_date
118 and inv.effective_start_date <= p_end_date
119 and inv.effective_end_date >= p_start_date
120 and ele.element_link_id = lnk.element_link_id
121 and elt.element_type_id = lnk.element_type_id
122 -- and p_effective_date between lnk.effective_start_date and lnk.effective_end_date
123 and lnk.effective_start_date <= p_end_date
124 and lnk.effective_end_date >= p_start_date
125 and elt.element_name = 'PAYE Details'
126 and paa.person_id= p_person_id
127 and ele.assignment_id=paa.assignment_id
128 -- and p_effective_date between elt.effective_start_date and elt.effective_end_date
129 and elt.effective_start_date <= p_end_date
130 and elt.effective_end_date >= p_start_date
131 -- and p_effective_date between paa.effective_start_date and paa.effective_end_date
132 and paa.effective_start_date <= p_end_date
133 and paa.effective_end_date >= p_start_date
134 and scl.segment1=p_tax_district
135 and ppf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
136 and ppf.payroll_id = paa.payroll_id
137 -- and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
138 and ppf.effective_start_date <= p_end_date
139 and ppf.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
149 and paaf.effective_end_date >= p_start_date
150 and papf.effective_start_date <= p_end_date
151 and papf.effective_end_date >= p_start_date
152 and papf.payroll_id = paaf.payroll_id
153 and papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
154 and hsck.segment1 = scl.segment1
155 and pay_gb_eoy_archive.get_agg_active_end(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date))
156 = pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, p_tax_district, greatest(paaf.effective_start_date,papf.effective_start_date))
157 and pay_gb_eoy_archive.get_agg_active_start(paa.assignment_id, p_tax_district, greatest(paa.effective_start_date,ppf.effective_start_date))
158 = pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, p_tax_district, greatest(paaf.effective_start_date,papf.effective_start_date))
159 and paaf.assignment_status_type_id = past.assignment_status_type_id
160 and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
161 )
162 group by ele.rowid, scl.segment1,
163 ele.assignment_id,ele.element_entry_id,
164 ele.entry_information_category, ele.entry_information1, ele.entry_information2,
165 ele.effective_start_date, ele.effective_end_date,
166 eev.effective_start_date,eev.effective_end_date,
167 paa.assignment_id,paa.effective_start_date,ppf.effective_start_date
168 order by cpe_start_date,eev.effective_start_date,paa.assignment_id;
169
170 -- End of Bug 5671777-9
171
172 CURSOR cur_paye_element_link IS
173 select count(*)
174 from pay_element_entries_f ele,
175 pay_element_links_f lnk,
176 pay_element_types_f elt,
177 per_all_assignments_f paa
178 where elt.element_name = 'PAYE Details'
179 and p_effective_date between elt.effective_start_date and elt.effective_end_date
180 and elt.element_type_id = lnk.element_type_id
181 and p_effective_date between lnk.effective_start_date and lnk.effective_end_date
182 and lnk.element_link_id = ele.element_link_id
183 and p_effective_date between ele.effective_start_date and ele.effective_end_date
184 and ele.assignment_id = paa.assignment_id
185 and paa.person_id = p_person_id
186 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
187
188 BEGIN
189 --
190 open cur_get_aggr_flag(p_person_id, p_effective_date);
191 fetch cur_get_aggr_flag into l_paye_flag,l_effective_end_date;
192 close cur_get_aggr_flag;
193
194 -- if the current values is already 'Y' then no need for validation
195 if (l_paye_flag = 'Y') then
196 return true;
197 end if;
198 --
199 open cur_person_type(p_person_id, p_effective_date);
200 fetch cur_person_type into l_sys_per_type;
201 close cur_person_type;
202 --
203 if l_sys_per_type <> 'EX_EMP' then
204 -- If the Person is an Ex Employee, then no checks below
205 -- necessary as they are given a new assignment on rehire
206 OPEN cur_rows_assg;
207 FETCH cur_rows_assg INTO l_count_assignments;
208 CLOSE cur_rows_assg;
209 --
210 -- check number of asgs for live person.
211 IF l_count_assignments <=1 THEN
212 IF p_message = 'Y' THEN
213 hr_utility.set_message(800,'HR_78101_CHK_MULTI_ASSG');
214 hr_utility.raise_error;
215 END IF;
216 RETURN FALSE;
217 ELSE
218 --
219 FOR l_segment_1 IN cur_tax_reference LOOP
220 --
221 IF l_segment_1.Num > 1 THEN
222 l_same_tax_district:= TRUE;
223 END IF;
224 --
225 END LOOP;
226 --
227 IF l_same_tax_district <> TRUE THEN
228 IF p_message = 'Y' THEN
229 hr_utility.set_message(800,'HR_78102_DIFF_TAX_DIST');
230 hr_utility.raise_error;
231 END IF;
232 RETURN FALSE;
233 END IF;
234 --
235
236 /*BUG 3516114 Added code to check for PAYE Details element link */
237 OPEN cur_paye_element_link;
238 FETCH cur_paye_element_link INTO l_count_paye_link;
239 CLOSE cur_paye_element_link;
240
241 IF l_count_paye_link < 1 THEN
242 IF p_message = 'Y' THEN
243 hr_utility.set_message(801,'HR_78110_DIFF_PAYE_VALUES');
244 hr_utility.raise_error;
245 END IF;
246 RETURN FALSE;
247 END IF;
248 --
249
250 /*BUG 2879391 Added Code to check that multiple assignments have same PAYE info*/
251 /* BUG 4520393 Added futher code to check that multiple assignments within SAME PAYE reference
252 have same PAYE info*/
253 -- Start of Bug 5671777-9
254 -- Added code to check that multiple assignments with same CPE have sme PAYE info
255
256 FOR l_tax_ref IN cur_tax_reference LOOP
257 FOR l_paye_values IN cur_paye_element_values(L_TAX_REF.tax_district,p_effective_date,l_effective_end_date) LOOP
258
259 IF l_new_paye_element_value is null AND l_old_paye_element_value is null
260 AND l_new_cpe_strat_date is null AND l_old_cpe_strat_date is null THEN
261 l_old_paye_element_value := l_paye_values.VALUE ;
262 l_old_cpe_strat_date := l_paye_values.cpe_start_date;
263 l_old_assignment_id := l_paye_values.assignment_id;
264 l_old_effective_start_date := l_paye_values.effective_start_date;
265 l_old_effective_end_date := l_paye_values.effective_end_date;
266 ELSE
267 l_new_paye_element_value := l_paye_values.VALUE;
268 l_new_cpe_strat_date := l_paye_values.cpe_start_date;
269 l_new_assignment_id := l_paye_values.assignment_id;
270 l_new_effective_start_date := l_paye_values.effective_start_date;
271 l_new_effective_end_date := l_paye_values.effective_end_date;
272
273 IF l_old_cpe_strat_date = l_new_cpe_strat_date THEN
274 IF l_old_assignment_id = l_new_assignment_id AND l_old_paye_element_value = l_new_paye_element_value THEN
275 l_old_effective_end_date := l_new_effective_end_date;
276 ELSIF l_old_assignment_id = l_new_assignment_id AND l_old_paye_element_value <> l_new_paye_element_value THEN
277 IF l_old_effective_end_date + 1 = l_new_effective_start_date THEN
278 l_old_paye_element_value := l_new_paye_element_value;
279 l_old_effective_start_date := l_new_effective_start_date;
280 l_old_effective_end_date := l_new_effective_end_date;
281 ELSE
282 l_same_paye_element_value := FALSE;
283 EXIT;
284 END IF;
285 ELSIF l_old_assignment_id <> l_new_assignment_id AND l_old_paye_element_value = l_new_paye_element_value THEN
286 l_old_effective_end_date := greatest(l_new_effective_end_date,l_old_effective_end_date);
287 ELSIF l_old_assignment_id <> l_new_assignment_id AND l_old_paye_element_value <>l_new_paye_element_value THEN
288 IF l_old_effective_end_date + 1 = l_new_effective_start_date THEN
289 l_old_paye_element_value := l_new_paye_element_value;
290 l_old_effective_start_date := l_new_effective_start_date;
291 l_old_effective_end_date := l_new_effective_end_date;
292 ELSE
293 l_same_paye_element_value := FALSE;
294 EXIT;
295 END IF;
296 END IF;
297 ELSE
298 l_old_paye_element_value := l_new_paye_element_value;
299 l_old_cpe_strat_date := l_new_cpe_strat_date;
300 l_old_assignment_id := l_new_assignment_id;
301 l_old_effective_start_date := l_new_effective_start_date;
302 l_old_effective_end_date := l_new_effective_end_date;
303 END IF;
304 END IF;
305 END LOOP;
306 l_new_paye_element_value := NULL;
307 l_old_paye_element_value := NULL;
308 l_new_cpe_strat_date := NULL;
309 l_old_cpe_strat_date := NULL;
310 l_old_assignment_id := NULL;
311 l_new_assignment_id := NULL;
312 l_old_effective_start_date := NULL;
313 l_new_effective_start_date := NULL;
314 l_old_effective_end_date := NULL;
315 l_new_effective_end_date := NULL;
316 END LOOP;
317 -- End of Bug 5671777-9
318
319 IF l_same_paye_element_value <> TRUE THEN
320 IF p_message = 'Y' THEN
321 -- Input values of the Paye Details for multiple assignments is not same
322 hr_utility.set_message(801,'HR_78110_DIFF_PAYE_VALUES');
323 hr_utility.raise_error;
324 END IF;
325 --
326 RETURN FALSE;
327 END IF;
328 RETURN TRUE;
329 --
330 END IF; -- Count of assignments
331 --
332 else -- The person is an ex employee so this is a rehire,
333 -- return TRUE
334 RETURN TRUE;
335 --
336 end if; -- Ex employee check
337 --
338 END assg_aggr_possible;
339
340 /* -----------------------------------------------------------
341 --PROCEDURE:check_aggr_assg
342 Procedure to be called through User hook of update_person_api
343 for calling function assg_aggr_possible and checking if 'NI
344 Multiple assignments' flag is 'Y' if aggregate assignment flag
345 is 'Y'
346 -------------------------------------------------------------*/
347
348 PROCEDURE check_aggr_assg(p_person_id IN NUMBER,
349 p_effective_date IN DATE,
350 p_per_information9 IN VARCHAR2,
351 p_per_information10 IN VARCHAR2)
352 IS
353 BEGIN
354
355 --
356 -- Added for GSI Bug 5472781
357 --
358 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
359 --
360 --If aggregate assignment flag is 'Y'
361 IF p_per_information10 = 'Y' THEN
362 -- Check if 'NI Multiple assignments' flag is 'Y'
363 IF p_per_information9 = 'Y' THEN
364 -- Check for multiple assignments and same tax district
365 IF NOT assg_aggr_possible (p_person_id , p_effective_date,'Y') THEN
366 hr_utility.raise_error;
367 END IF;
368 ELSE
369 -- if 'NI MUltiple assignment flag is not 'Y'
370 -- aggregate assignment flag cannot be 'Y'
371 hr_utility.set_message(800,'HR_78103_CHK_NI_MULTI_ASSG_FLG');
372 hr_utility.raise_error;
373 END IF;
374 END IF;
375 END IF;
376 END check_aggr_assg;
377 --
378 END per_asg_aggr;
379 --
[Home] [Help]