4 ----------------------------------------------------------------------
1 PACKAGE BODY pay_gb_payroll_rules AS
2 /* $Header: pygbprlr.pkb 120.7.12010000.2 2009/01/13 09:31:08 npannamp ship $ */
3
5 -- Procedure: validate_update
6 -- Description: This is "Before Process" GB legislative hook call on
7 -- UPDATE_PAYROLL API to ensure a date track update or correction
8 -- would not lead to inconsistent PAYE Ref in a tax year
9 ---------------------------------------------------------------------
10 PROCEDURE validate_update(p_effective_date IN DATE
11 ,p_datetrack_mode IN VARCHAR2
12 ,p_payroll_id IN NUMBER
13 ,p_payroll_name IN VARCHAR2
14 ,p_soft_coding_keyflex_id_in in NUMBER) IS
15 --
16 l_cur_scl_id NUMBER;
17 l_next_scl_id NUMBER;
18 l_prev_scl_id NUMBER;
19 l_cur_eff_start DATE;
20 l_cur_eff_end DATE;
21 l_first_eff_start DATE;
22 l_last_eff_end DATE;
23 l_cur_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
24 l_new_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
25 l_next_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
26 l_prev_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
27 l_span_start DATE;
28 l_span_end DATE;
29 --
30 CURSOR get_current_details IS
31 SELECT soft_coding_keyflex_id, effective_start_date, effective_end_date
32 FROM pay_all_payrolls_f
33 WHERE payroll_id = p_payroll_id
34 AND p_effective_date BETWEEN effective_start_date and effective_end_date;
35 --
36 CURSOR get_next_details IS
37 SELECT soft_coding_keyflex_id
38 FROM pay_all_payrolls_f
39 WHERE payroll_id = p_payroll_id
40 AND l_cur_eff_end+1 BETWEEN effective_start_date and effective_end_date;
41 --
42 CURSOR get_prev_details IS
43 SELECT soft_coding_keyflex_id
44 FROM pay_all_payrolls_f
45 WHERE payroll_id = p_payroll_id
46 AND l_cur_eff_start-1 BETWEEN effective_start_date and effective_end_date;
47 --
48 CURSOR get_paye_ref(p_scl_id NUMBER) IS
49 SELECT segment1
50 FROM hr_soft_coding_keyflex
51 WHERE soft_coding_keyflex_id = p_scl_id;
52 --
53 CURSOR get_min_max_dates IS
54 SELECT min(effective_start_date) first_eff_start, max(effective_end_date)
55 FROM pay_all_payrolls_f
56 WHERE payroll_id = p_payroll_id;
57 --
58 l_found NUMBER;
59 /* Start Bug Fix 7343780 */
60 l_assg_no per_all_assignments_f.assignment_number%type;
61 /* End Bug Fix 7343780 */
62 --
63
64 --
65 -- to check whether any terminated asg found on the cur. payroll at effective(start) date
66 --
67 CURSOR csr_term_asg_exists(c_payroll_id number, c_effective_date date) is
68 /* Start Bug Fix 7343780 */
69 -- SELECT 1
70 SELECT a.assignment_number
71 /* End Bug Fix 7343780 */
72 FROM per_all_assignments_f a,
73 per_assignment_status_types past
74 where a.payroll_id = c_payroll_id
75 and a.assignment_status_type_id = past.assignment_status_type_id
76 and past.per_system_status IN ('TERM_ASSIGN')
77 and c_effective_date between a.effective_start_date and a.effective_end_date;
78 --
79
80 --
81 -- to check whether future payroll actions exists for the terminated assignments
82 -- if found then no error, otherwise raise an error.
83 --
84 CURSOR csr_term_asg_future_act_exists(c_payroll_id number, c_effective_date date) is
85 SELECT 1
86 FROM per_all_assignments_f a,
87 per_assignment_status_types past,
88 pay_assignment_actions act,
89 pay_payroll_actions pact,
90 per_time_periods ptp
91 where a.payroll_id = c_payroll_id
92 and a.assignment_status_type_id = past.assignment_status_type_id
93 and past.per_system_status IN ('TERM_ASSIGN')
97 and act.assignment_id = a.assignment_id
94 and c_effective_date between a.effective_start_date and a.effective_end_date
95 and pact.payroll_action_id = act.payroll_action_id
96 and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
98 and pact.time_period_id = ptp.time_period_id
99 and regular_payment_date >= c_effective_date;
100 --
101
102 l_proc VARCHAR2(100) := 'pay_gb_payroll_rules.validate_update';
103 BEGIN
104 hr_utility.trace('Entering '||l_proc);
105 hr_utility.trace('p_effective_date='||fnd_date.date_to_displaydate(p_effective_date));
106 hr_utility.trace('p_datetrack_mode='||p_datetrack_mode);
107 hr_utility.trace('p_payroll_id='||p_payroll_id);
108 hr_utility.trace('p_payroll_name='||p_payroll_name);
109 hr_utility.trace('p_soft_coding_keyflex_id_in='||p_soft_coding_keyflex_id_in);
110 hr_utility.trace('Fetching PAYE Ref for new scl id='|| p_soft_coding_keyflex_id_in);
111 OPEN get_paye_ref(p_soft_coding_keyflex_id_in);
112 FETCH get_paye_ref INTO l_new_paye_ref;
113 CLOSE get_paye_ref;
114 --
115 hr_utility.trace('New Paye Ref is '||l_new_paye_ref);
116 --
117 hr_utility.trace('Fetching current payroll details');
118 OPEN get_current_details;
119 FETCH get_current_details INTO l_cur_scl_id, l_cur_eff_start, l_cur_eff_end;
120 CLOSE get_current_details;
121 --
122 hr_utility.trace('Currrent l_cur_scl_id='||l_cur_scl_id);
123 hr_utility.trace('Currrent l_cur_eff_start='||fnd_date.date_to_displaydate(l_cur_eff_start));
124 hr_utility.trace('Currrent l_cur_eff_end='||fnd_date.date_to_displaydate(l_cur_eff_end));
125 --
126 hr_utility.trace('Fetching Current PAYE Ref.');
127 OPEN get_paye_ref(l_cur_scl_id);
128 FETCH get_paye_ref INTO l_cur_paye_ref;
129 CLOSE get_paye_ref;
130 hr_utility.trace('Current PAYE Ref is '||l_cur_paye_ref);
131 --
132 hr_utility.trace('Fetching fiest start date and last end date of the payroll');
133 OPEN get_min_max_dates;
134 FETCH get_min_max_dates INTO l_first_eff_start, l_last_eff_end;
135 CLOSE get_min_max_dates;
136 hr_utility.trace('l_first_eff_start='||fnd_date.date_to_displaydate(l_first_eff_start));
137 hr_utility.trace('l_last_eff_end='||fnd_date.date_to_displaydate(l_last_eff_end));
138 --
139 IF p_datetrack_mode = hr_api.g_correction THEN
140 hr_utility.trace('Datetrack Action is Correction.');
141 --
142 IF l_new_paye_ref <> l_cur_paye_ref THEN
143 hr_utility.trace('PAYE Refs is changing, validating the change');
144 --
145 hr_utility.trace('Fetching Previous PAYE Ref');
146 OPEN get_prev_details;
147 FETCH get_prev_details INTO l_prev_scl_id;
151 OPEN get_paye_ref(l_prev_scl_id);
148 CLOSE get_prev_details;
149 --
150 hr_utility.trace('l_prev_scl_id = '||l_prev_scl_id);
152 FETCH get_paye_ref INTO l_prev_paye_ref;
153 CLOSE get_paye_ref;
154 --
155 hr_utility.trace('l_prev_paye_ref = '||l_prev_paye_ref);
156 --
157 IF l_prev_paye_ref <> l_new_paye_ref AND
158 l_cur_eff_start <> l_first_eff_start AND
159 to_char(l_cur_eff_start, 'DD-MM') <> '06-04' THEN
160 hr_utility.trace('New PAYE Ref does not match the previous ');
161 hr_utility.trace('PAYE Ref and current effective start date ');
162 hr_utility.trace('is not the first effective start date and ');
163 hr_utility.trace('current effective date is not start of a ');
164 hr_utility.trace('tax year therefore raise an error message.');
165 --
166 l_span_start := hr_gbbal.span_start(l_cur_eff_start);
167 l_span_end := hr_gbbal.span_end(l_cur_eff_start);
168 --
169 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
170 fnd_message.set_token('TAX_YEAR',
171 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
172 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
173 fnd_message.raise_error;
174 END IF;
175 --
176 hr_utility.trace('Change as at the start date is ok, Checking end date');
177 --
178 hr_utility.trace('Fetching Next PAYE Ref');
179 OPEN get_next_details;
180 FETCH get_next_details INTO l_next_scl_id;
181 CLOSE get_next_details;
182 --
183 hr_utility.trace('l_next_scl_id = '||l_next_scl_id);
184 OPEN get_paye_ref(l_next_scl_id);
185 FETCH get_paye_ref INTO l_next_paye_ref;
186 CLOSE get_paye_ref;
187 --
188 hr_utility.trace('l_next_paye_ref = '||l_next_paye_ref);
192 to_char(l_cur_eff_end, 'DD-MM') <> '05-04' THEN
189 --
190 IF l_next_paye_ref <> l_new_paye_ref AND
191 l_cur_eff_end <> l_last_eff_end AND
193 hr_utility.trace('New PAYE Ref does not match the next ');
194 hr_utility.trace('PAYE Ref and current effective end date ');
195 hr_utility.trace('is not the last effective end date and ');
196 hr_utility.trace('current effective date is not end of a ');
197 hr_utility.trace('tax year therefore raise an error message.');
198 --
199 l_span_start := hr_gbbal.span_start(l_cur_eff_end);
200 l_span_end := hr_gbbal.span_end(l_cur_eff_end);
201 --
202 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
203 fnd_message.set_token('TAX_YEAR',
204 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
205 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
206 fnd_message.raise_error;
207 END IF;
208 --
209 hr_utility.trace('Change as at the end date is ok too.');
210 --
211
212 -- START CHECK - Termination Assignment exists at the Effective start date
213 IF (l_prev_paye_ref <> l_new_paye_ref AND
214 l_cur_eff_start <> l_first_eff_start) THEN
215
216 open csr_term_asg_exists(p_payroll_id, l_cur_eff_start);
217 /* Start Bug Fix 7343780 */
218 -- fetch csr_term_asg_exists into l_found;
219 fetch csr_term_asg_exists into l_assg_no;
220 /* End Bug Fix 7343780 */
221 if csr_term_asg_exists%found then
222 open csr_term_asg_future_act_exists(p_payroll_id, l_cur_eff_start);
223 fetch csr_term_asg_future_act_exists into l_found;
224 if csr_term_asg_future_act_exists%notfound then
225 close csr_term_asg_exists;
226 close csr_term_asg_future_act_exists;
227 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
228 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(l_cur_eff_start));
229 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
230 fnd_message.raise_error;
231 end if;
232 close csr_term_asg_future_act_exists;
233 end if;
234 close csr_term_asg_exists;
235 END IF;
236
237 IF (l_next_paye_ref <> l_new_paye_ref AND
238 l_cur_eff_end <> l_last_eff_end) THEN
239
240 open csr_term_asg_exists(p_payroll_id, l_cur_eff_end+1);
241 /* Start Bug Fix 7343780 */
242 -- fetch csr_term_asg_exists into l_found;
243 fetch csr_term_asg_exists into l_assg_no;
244 /* End Bug Fix 7343780 */
245 if csr_term_asg_exists%found then
246 open csr_term_asg_future_act_exists(p_payroll_id, l_cur_eff_end+1);
247 fetch csr_term_asg_future_act_exists into l_found;
248 if csr_term_asg_future_act_exists%notfound then
249 close csr_term_asg_exists;
250 close csr_term_asg_future_act_exists;
251 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
252 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(l_cur_eff_end+1));
253 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
254 fnd_message.raise_error;
255 end if;
256 close csr_term_asg_future_act_exists;
257 end if;
258 close csr_term_asg_exists;
259 END IF;
260
261 -- END CHECK - Termination Assignment exists at the Effective start date
262
263 END IF; -- PAYE Ref Changing
264 END IF; -- Date track mode is correction
265 --
266 IF p_datetrack_mode = hr_api.g_update
267 OR p_datetrack_mode = hr_api.g_update_override THEN
268 hr_utility.trace('Datetrack Action is Update or Update Override.');
269 --
270 IF l_new_paye_ref <> l_cur_paye_ref THEN
271 hr_utility.trace('PAYE Refs is changing, validating the change');
272 --
273 IF l_cur_paye_ref <> l_new_paye_ref AND
274 p_effective_date <> l_first_eff_start AND
275 to_char(p_effective_date, 'DD-MM') <> '06-04' THEN
276 hr_utility.trace('New PAYE Ref does not match the current ');
277 hr_utility.trace('PAYE Ref and new effective start date ');
278 hr_utility.trace('is not the first effective start date and ');
279 hr_utility.trace('new effective date is not start of a ');
285 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
280 hr_utility.trace('tax year therefore raise an error message.');
281 --
282 l_span_start := hr_gbbal.span_start(p_effective_date);
283 l_span_end := hr_gbbal.span_end(p_effective_date);
284 --
286 fnd_message.set_token('TAX_YEAR',
287 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
288 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
289 fnd_message.raise_error;
290 END IF;
291 --
292 hr_utility.trace('Change as at the new effective date ok, no need to check as at the effective end date');
293 --
294
295 -- START CHECK - Termination Assignment exists at the Effective start date
296 IF l_cur_paye_ref <> l_new_paye_ref AND
297 p_effective_date <> l_first_eff_start THEN
298
299 open csr_term_asg_exists(p_payroll_id, p_effective_date);
300 /* Start Bug Fix 7343780 */
301 -- fetch csr_term_asg_exists into l_found;
302 fetch csr_term_asg_exists into l_assg_no;
303 /* End Bug Fix 7343780 */
304 if csr_term_asg_exists%found then
305 open csr_term_asg_future_act_exists(p_payroll_id, p_effective_date);
306 fetch csr_term_asg_future_act_exists into l_found;
307 if csr_term_asg_future_act_exists%notfound then
308 close csr_term_asg_exists;
309 close csr_term_asg_future_act_exists;
310 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
311 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(p_effective_date));
312 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
313 fnd_message.raise_error;
314 end if;
315 close csr_term_asg_future_act_exists;
316 end if;
317 close csr_term_asg_exists;
318
319 END IF;
320 -- END CHECK - Termination Assignment exists at the Effective start date
321
322 END IF; -- PAYE Ref Changing
323 END IF; -- Date track update or update override
324 --
325 IF p_datetrack_mode = hr_api.g_update_change_insert THEN
326 hr_utility.trace('Datetrack Action is Update Change Insert.');
327 --
328 IF l_new_paye_ref <> l_cur_paye_ref THEN
329 hr_utility.trace('PAYE Refs is changing, validating the change');
330 --
331 IF l_cur_paye_ref <> l_new_paye_ref AND
332 p_effective_date <> l_first_eff_start AND
333 to_char(p_effective_date, 'DD-MM') <> '06-04' THEN
334 hr_utility.trace('New PAYE Ref does not match the current ');
335 hr_utility.trace('PAYE Ref and new effective start date ');
336 hr_utility.trace('is not the first effective start date and ');
337 hr_utility.trace('new effective date is not start of a ');
338 hr_utility.trace('tax year therefore raise an error message.');
339 --
340 l_span_start := hr_gbbal.span_start(p_effective_date);
341 l_span_end := hr_gbbal.span_end(p_effective_date);
342 --
343 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
344 fnd_message.set_token('TAX_YEAR',
345 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
346 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
347 fnd_message.raise_error;
348 END IF;
349 --
350 hr_utility.trace('Change as at the new effective date ok, Checking as at the effective end date');
351 --
352 --
353 hr_utility.trace('Fetching Next PAYE Ref');
354 OPEN get_next_details;
355 FETCH get_next_details INTO l_next_scl_id;
356 CLOSE get_next_details;
357 --
358 hr_utility.trace('l_next_scl_id = '||l_next_scl_id);
359 OPEN get_paye_ref(l_next_scl_id);
360 FETCH get_paye_ref INTO l_next_paye_ref;
361 CLOSE get_paye_ref;
362 --
363 hr_utility.trace('l_next_paye_ref = '||l_next_paye_ref);
364 --
365 IF l_next_paye_ref <> l_new_paye_ref AND
366 l_cur_eff_end <> l_last_eff_end AND
367 to_char(l_cur_eff_end, 'DD-MM') <> '05-04' THEN
368 hr_utility.trace('New PAYE Ref does not match the next ');
369 hr_utility.trace('PAYE Ref and current effective end date ');
370 hr_utility.trace('is not the last effective end date and ');
371 hr_utility.trace('current effective date is not end of a ');
372 hr_utility.trace('tax year therefore raise an error message.');
373 --
374 l_span_start := hr_gbbal.span_start(l_cur_eff_end);
375 l_span_end := hr_gbbal.span_end(l_cur_eff_end);
376 --
377 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
378 fnd_message.set_token('TAX_YEAR',
379 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
380 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
381 fnd_message.raise_error;
382 END IF;
383 --
384 hr_utility.trace('Change as at the end date is ok too.');
385 --
386
387 -- START CHECK - Termination Assignment exists at the Effective start date
388 IF (l_cur_paye_ref <> l_new_paye_ref AND
389 p_effective_date <> l_first_eff_start) THEN
390
391 open csr_term_asg_exists(p_payroll_id, p_effective_date);
392 /* Start Bug Fix 7343780 */
393 -- fetch csr_term_asg_exists into l_found;
394 fetch csr_term_asg_exists into l_assg_no;
395 /* End Bug Fix 7343780 */
399 if csr_term_asg_future_act_exists%notfound then
396 if csr_term_asg_exists%found then
397 open csr_term_asg_future_act_exists(p_payroll_id, p_effective_date);
398 fetch csr_term_asg_future_act_exists into l_found;
400 close csr_term_asg_exists;
401 close csr_term_asg_future_act_exists;
402 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
403 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(p_effective_date));
404 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
405 fnd_message.raise_error;
406 end if;
407 close csr_term_asg_future_act_exists;
408 end if;
409 close csr_term_asg_exists;
410 END IF;
411
415 open csr_term_asg_exists(p_payroll_id, l_cur_eff_end+1);
412 IF (l_next_paye_ref <> l_new_paye_ref AND
413 l_cur_eff_end <> l_last_eff_end) THEN
414
416 /* Start Bug Fix 7343780 */
417 -- fetch csr_term_asg_exists into l_found;
418 fetch csr_term_asg_exists into l_assg_no;
419 /* End Bug Fix 7343780 */
420 if csr_term_asg_exists%found then
421 open csr_term_asg_future_act_exists(p_payroll_id, l_cur_eff_end+1);
422 fetch csr_term_asg_future_act_exists into l_found;
423 if csr_term_asg_future_act_exists%notfound then
424 close csr_term_asg_exists;
425 close csr_term_asg_future_act_exists;
426 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
427 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(l_cur_eff_end+1));
428 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
429 fnd_message.raise_error;
430 end if;
431 close csr_term_asg_future_act_exists;
432 end if;
433 close csr_term_asg_exists;
434 END IF;
435 -- END CHECK - Termination Assignment exists at the Effective start date
436
437 END IF; -- PAYE Ref Changing
438 END IF; -- Date track update change insert
439 --
440
441 hr_utility.trace('No problem with this update/correction.');
442 hr_utility.trace('Leaving pay_gb_payroll_rules.validate_update');
443 END validate_update;
444
445 PROCEDURE validate_delete(p_effective_date IN DATE
446 ,p_datetrack_mode IN VARCHAR2
447 ,p_payroll_id IN NUMBER) IS
448
449 --
450 l_cur_scl_id NUMBER;
451 l_next_to_next_scl_id NUMBER;
452 l_cur_eff_end DATE;
453 l_next_eff_end DATE;
454 l_first_eff_start DATE;
455 l_last_eff_end DATE;
456 l_cur_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
457 l_next_to_next_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
458 l_span_start DATE;
459 l_span_end DATE;
460 --
461 CURSOR get_current_details IS
462 SELECT soft_coding_keyflex_id, effective_end_date
463 FROM pay_all_payrolls_f
464 WHERE payroll_id = p_payroll_id
465 AND p_effective_date BETWEEN effective_start_date and effective_end_date;
466 --
467 CURSOR get_next_details IS
468 SELECT effective_end_date
469 FROM pay_all_payrolls_f
470 WHERE payroll_id = p_payroll_id
471 AND l_cur_eff_end+1 BETWEEN effective_start_date and effective_end_date;
472 --
473 CURSOR get_next_to_next_details IS
474 SELECT soft_coding_keyflex_id
475 FROM pay_all_payrolls_f
476 WHERE payroll_id = p_payroll_id
477 AND l_next_eff_end+1 BETWEEN effective_start_date and effective_end_date;
478 --
479 CURSOR get_paye_ref(p_scl_id NUMBER) IS
480 SELECT segment1
481 FROM hr_soft_coding_keyflex
482 WHERE soft_coding_keyflex_id = p_scl_id;
483 --
484 CURSOR get_min_max_dates IS
485 SELECT min(effective_start_date) first_eff_start, max(effective_end_date)
486 FROM pay_all_payrolls_f
487 WHERE payroll_id = p_payroll_id;
488 --
489 l_found NUMBER;
490 /* Start Bug Fix 7343780 */
491 l_assg_no per_all_assignments_f.assignment_number%type;
492 /* End Bug Fix 7343780 */
493 --
494
495 --
496 -- to check whether any terminated asg found on the cur. payroll at effective(start) date
497 --
498 CURSOR csr_term_asg_exists(c_payroll_id number, c_effective_date date) is
499 /* Start Bug Fix 7343780 */
500 -- SELECT 1
501 SELECT a.assignment_number
502 /* End Bug Fix 7343780 */
506 and a.assignment_status_type_id = past.assignment_status_type_id
503 FROM per_all_assignments_f a,
504 per_assignment_status_types past
505 where a.payroll_id = c_payroll_id
507 and past.per_system_status IN ('TERM_ASSIGN')
508 and c_effective_date between a.effective_start_date and a.effective_end_date;
509 --
510
511 --
512 -- to check whether future payroll actions exists for the terminated assignments
513 -- if found then no error, otherwise raise an error.
514 --
515 CURSOR csr_term_asg_future_act_exists(c_payroll_id number, c_effective_date date) is
516 SELECT 1
517 FROM per_all_assignments_f a,
518 per_assignment_status_types past,
519 pay_assignment_actions act,
520 pay_payroll_actions pact,
521 per_time_periods ptp
522 where a.payroll_id = c_payroll_id
523 and a.assignment_status_type_id = past.assignment_status_type_id
524 and past.per_system_status IN ('TERM_ASSIGN')
525 and c_effective_date between a.effective_start_date and a.effective_end_date
526 and pact.payroll_action_id = act.payroll_action_id
527 and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
528 and act.assignment_id = a.assignment_id
529 and pact.time_period_id = ptp.time_period_id
530 and regular_payment_date >= c_effective_date;
531 --
532
533 l_proc VARCHAR2(100) := 'pay_gb_payroll_rules.validate_delete';
534 BEGIN
535 hr_utility.trace('Entering '||l_proc);
536 hr_utility.trace('p_effective_date='||fnd_date.date_to_displaydate(p_effective_date));
537 hr_utility.trace('p_datetrack_mode='||p_datetrack_mode);
538 hr_utility.trace('p_payroll_id='||p_payroll_id);
539 --
540 hr_utility.trace('Fetching current payroll details');
541 OPEN get_current_details;
542 FETCH get_current_details INTO l_cur_scl_id, l_cur_eff_end;
543 CLOSE get_current_details;
544 --
545 hr_utility.trace('Currrent l_cur_scl_id='||l_cur_scl_id);
546 hr_utility.trace('Currrent l_cur_eff_end='||fnd_date.date_to_displaydate(l_cur_eff_end));
547 --
548 hr_utility.trace('Fetching Current PAYE Ref.');
549 OPEN get_paye_ref(l_cur_scl_id);
550 FETCH get_paye_ref INTO l_cur_paye_ref;
551 CLOSE get_paye_ref;
552 hr_utility.trace('Current PAYE Ref is '||l_cur_paye_ref);
553 --
554 hr_utility.trace('Fetching fiest start date and last end date of the payroll');
555 OPEN get_min_max_dates;
556 FETCH get_min_max_dates INTO l_first_eff_start, l_last_eff_end;
557 CLOSE get_min_max_dates;
558 hr_utility.trace('l_first_eff_start='||fnd_date.date_to_displaydate(l_first_eff_start));
559 hr_utility.trace('l_last_eff_end='||fnd_date.date_to_displaydate(l_last_eff_end));
560 --
561 IF p_datetrack_mode = hr_api.g_delete_next_change THEN
562 hr_utility.trace('Datetrack Mode is Delete next change.');
563 hr_utility.trace('Fetching end date of next instance.');
564 OPEN get_next_details;
565 FETCH get_next_details INTO l_next_eff_end;
566 CLOSE get_next_details;
567 --
568 hr_utility.trace('l_next_eff_date='||fnd_date.date_to_displaydate(l_next_eff_end));
569 hr_utility.trace('Fetching PAYE Ref on next to next instance.');
570 OPEN get_next_to_next_details;
571 FETCH get_next_to_next_details INTO l_next_to_next_scl_id;
572 CLOSE get_next_to_next_details;
573 hr_utility.trace('l_next_to_next_scl_id='||l_next_to_next_scl_id);
574 --
575 OPEN get_paye_ref(l_next_to_next_scl_id);
576 FETCH get_paye_ref INTO l_next_to_next_paye_ref;
577 CLOSE get_paye_ref;
578 hr_utility.trace('l_next_to_next_paye_ref='||l_next_to_next_paye_ref);
579 --
580 IF l_next_to_next_paye_ref <> l_cur_paye_ref AND
581 l_next_eff_end <> l_last_eff_end AND
582 to_char(l_next_eff_end, 'DD-MM') <> '05-04' THEN
583 --
584 hr_utility.trace('Current PAYE Ref does not match the PAYE ');
585 hr_utility.trace('Ref on next to next instance and next effective ');
586 hr_utility.trace('end date is not the last effective end date and ');
587 hr_utility.trace('next effective end date is not end of a ');
588 hr_utility.trace('tax year therefore raise an error message.');
589 --
590 l_span_start := hr_gbbal.span_start(l_next_eff_end);
591 l_span_end := hr_gbbal.span_end(l_next_eff_end);
592 --
593 fnd_message.set_name('PAY', 'HR_78126_INCONSISTENT_PAYE_REF');
594 fnd_message.set_token('TAX_YEAR',
595 substr(fnd_date.date_to_canonical(l_span_start), 1, 4)||'-'||
596 substr(fnd_date.date_to_canonical(l_span_end), 1, 4));
597 fnd_message.raise_error;
598 END IF;
599 --
600
601 -- START CHECK - Termination Assignment exists at the Effective start date
602 IF l_next_to_next_paye_ref <> l_cur_paye_ref AND
603 l_next_eff_end <> l_last_eff_end THEN
604
605 open csr_term_asg_exists(p_payroll_id, l_next_eff_end+1);
606 /* Start Bug Fix 7343780 */
607 -- fetch csr_term_asg_exists into l_found;
608 fetch csr_term_asg_exists into l_assg_no;
609 /* End Bug Fix 7343780 */
610 if csr_term_asg_exists%found then
611 open csr_term_asg_future_act_exists(p_payroll_id, l_next_eff_end+1);
612 fetch csr_term_asg_future_act_exists into l_found;
613 if csr_term_asg_future_act_exists%notfound then
614 close csr_term_asg_exists;
615 close csr_term_asg_future_act_exists;
616 fnd_message.set_name('PAY', 'HR_GB_78131_TERM_ASSIGN_EXIST');
617 fnd_message.set_token('EFF_DATE', fnd_date.date_to_displaydate(l_next_eff_end+1));
621 close csr_term_asg_future_act_exists;
618 fnd_message.set_token('ASSG_NO', l_assg_no); --Bug Fix 7343780
619 fnd_message.raise_error;
620 end if;
622 end if;
623 close csr_term_asg_exists;
624
625 END IF;
626 -- END CHECK - Termination Assignment exists at the Effective start date
627
628 END IF; -- Datetrack mode is Remove next change
629 --
630 hr_utility.trace('No problem with this delete.');
631 hr_utility.trace('Leaving pay_gb_payroll_rules.validate_delete');
632 END validate_delete;
633
634 END;
[Home] [Help]