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