[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_PAYE_CALC
Source
1 package body pay_gb_paye_calc as
2 /* $Header: pygbpaye.pkb 120.15.12020000.2 2013/03/27 06:19:54 rajganga ship $
3 ******************************************************************
4 * *
5 * Copyright (C) 1989 Oracle Corporation UK Ltd., *
6 * Richmond, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21
22 Name : pay_gb_paye_calc
23
24 Description : This package contains calculations for use in PAYE processing
25
26 Uses :
27
28 Used By : FOT fast formula
29
30
31 Change List :
32
33 Version Date Author Description
34 ------- ----- -------- ----------------
35 115.0 6/6/01 GBUTLER Created
36 115.3 21/12/06 SBAIRAGI Added check_tax_ref function
37 115.5 28/12/05 TUKUMAR Bug 4528372 : added function tax_year_of_pensioners_death
38 115.6 04/01/06 SBAIRAGI 4724096 - PAYE Aggregated Assignments validation.
39 115.7 12/07/07 SBAIRAGI PAYE Details check for bug 6018309
40 115.8 25/09/08 rlingama Bug 7389532 - Added the distinct and null condition on period of service id
41 column for cur_chk_tax_code cursor.
42 115.9 03/11/08 rlingama Bug 7492621 the PAYE mismatch error should come after
43 6th April 2008 check is added.
44 115.11 12/11/08 rlingama Bug 7492621 - Reverted the root cause for the issue introduced in HRMS FPK
45 Rollup 3 for the PAYE mismatch error.
46 115.12 03/04/08 rlingama Bug 7601088 - Re-introduced the PAYE validation.
47 115.13 19/11/09 jvaradra Bug 9127942 - Changed the PAYE validation from 06-Apr-2009 to 06-Apr-2010.
48 115.14 02/02/10 rlingama Bug 9278271 - Replaced the select query with cursor cur_get_paye_agg to avoid
49 technical errors.
50 115.15 05/07/11 pbalu Bug 12662579 - PAYE reference change - Validations made more stringent.
51 115.16 21/07/11 pbalu Bug 12662579 - PAYE reference change - Validations made more stringent.
52 115.17 27/03/13 sampmand Bug 16489664 - Multiple PAYE Ref change validation switched off for DWP Retro-process.
53 */
54
55
56 /* Calculate free pay for given amount */
57 function free_pay
58 ( p_amount IN NUMBER,
59 p_tax_code IN VARCHAR2,
60 p_tax_basis IN VARCHAR2,
61 p_stat_annual_periods IN NUMBER,
62 p_current_period IN NUMBER)
63 return NUMBER
64 as
65
66 l_tax_code VARCHAR2(8);
67 l_scots_code BOOLEAN := FALSE;
68 l_k_code BOOLEAN := FALSE;
69 l_num_code NUMBER;
70 l_remainder NUMBER;
71 l_part1 NUMBER;
72 l_ann_value NUMBER;
73 l_period1_pay NUMBER;
74 l_free_or_add_pay NUMBER;
75 l_taxable_pay NUMBER;
76 l_amount NUMBER;
77
78
79
80 begin
81
82 --hr_utility.trace_on(null,'FOT');
83
84 hr_utility.trace('ENTERING: pay_gb_paye_calc.free_pay');
85
86 hr_utility.trace('***********************');
87 hr_utility.trace('INPUT values: ');
88 hr_utility.trace('Amount: '||p_amount);
89 hr_utility.trace('Tax code: '||p_tax_code);
90 hr_utility.trace('Tax basis: '||p_tax_basis);
91 hr_utility.trace('Stat annual periods: '||p_stat_annual_periods);
92 hr_utility.trace('Current pay period: '||p_current_period);
93 hr_utility.trace('***********************');
94
95 l_amount := p_amount;
96
97 /* Check for Scottish tax code, S prefix, strip off S prefix if present */
98 if upper(substrb(p_tax_code,1,1)) = 'S'
99 then
100
101 l_scots_code := TRUE;
102 l_tax_code := ltrim(p_tax_code,'S');
103
104 else
105
106 l_tax_code := p_tax_code;
107
108 end if;
109
110
111 /* Check for K codes */
112 if substrb(l_tax_code,1,1) = 'K'
113 then
114
115 l_k_code := TRUE;
116 l_num_code := to_number(substrb(l_tax_code,2,length(l_tax_code) -1 ));
117
118
119 else
120
121
122 l_num_code := to_number(substrb(l_tax_code,1,length(l_tax_code) -1 ));
123
124 end if;
125
126 hr_utility.trace('Numeric component: '||l_num_code);
127
128
129 /* Check if numeric component > 500 */
130 if l_num_code > 500
131 then
132
133 l_remainder := floor((l_num_code - 1)/500);
134 l_part1 := l_num_code - ( l_remainder * 500) + 1;
135 l_ann_value := ((l_part1 * 10) + 9) + (5000 * l_remainder);
136
137
138 else
139
140 l_ann_value := (l_num_code * 10) + 9;
141
142 end if;
143
144 hr_utility.trace('Annual free/additional pay: '||l_ann_value);
145
146
147
148 /* Calculate free/additional pay for period 1, apply rounding rules*/
149
150 l_period1_pay := (l_ann_value/p_stat_annual_periods);
151
152 if mod((l_period1_pay * 100),1) <> 0
153 then
154
155 l_period1_pay := round(l_period1_pay,4);
156
157 l_period1_pay := trunc(l_period1_pay + 0.01,2);
158
159 end if;
160
161
162 hr_utility.trace('Free/additional pay for period 1: '||l_period1_pay);
163
164
165 /* If tax basis is cumulative, find free/additional pay for year to date */
166 /* else just use free/additional pay for period 1 */
167
168 if p_tax_basis = 'C'
169 then
170
171 /* Calculate free/additional pay for year to date, based on current pay period (cumulative basis only) */
172
173
174 l_free_or_add_pay := p_current_period * l_period1_pay;
175
176 else
177
178 l_free_or_add_pay := l_period1_pay;
179
180
181 end if;
182
183
184 hr_utility.trace('Free/additional pay : '||l_free_or_add_pay);
185
186
187
188 /* Now calculate the taxable pay to date by subtracting/adding free/additional pay */
189 /* to the cumulative value entered in as an input value */
190 /* 1) If taxable pay is less then cumulative/period 1 free pay and tax code is not K, then set free pay to 0 */
191 /* as subtracting free pay from taxable pay would result in negative amount (cumulative tax basis only)*/
192 /* 2) If tax code was a K code then add the free/additional pay, else subtract it */
193
194
195 if (l_k_code)
196 then
197
198 hr_utility.trace('K code is TRUE');
199 l_taxable_pay := l_amount + l_free_or_add_pay;
200
201 else
202
203 hr_utility.trace('K code is FALSE');
204
205 if (l_free_or_add_pay >= l_amount)
206 then
207
208 hr_utility.trace('Free pay exceeds taxable pay for this period - setting taxable pay to 0');
209 l_taxable_pay := 0;
210
211 else
212
213 l_taxable_pay := l_amount - l_free_or_add_pay;
214
215 end if;
216
217 end if;
218
219 hr_utility.trace('********************');
220 hr_utility.trace('OUTPUT values: ');
221 hr_utility.trace('Taxable pay: '||l_taxable_pay);
222 hr_utility.trace('********************');
223
224 hr_utility.trace('LEAVING: pay_gb_paye_calc.free_pay');
225
226 --hr_utility.trace_off;
227
228 return l_taxable_pay;
229
230 exception
231
232
233 when others
234 then raise;
235
236 end free_pay;
237
238 /* Calculate tax due to date on taxable pay */
239
240 function tax_to_date
241 (p_session_date IN DATE,
242 p_taxable_pay IN NUMBER,
243 p_tax_code IN VARCHAR2,
244 p_tax_basis IN VARCHAR2,
245 p_stat_annual_periods IN NUMBER,
246 p_current_period IN NUMBER)
247 return NUMBER
248 as
249
250
251 l_row_num NUMBER := 0;
252 l_current_deduct NUMBER;
253 l_band_tax_deduct NUMBER;
254 l_temp_val NUMBER;
255 l_tax_liable NUMBER;
256 l_scots_code BOOLEAN := FALSE;
257 l_taxable_pay NUMBER;
258
259
260 l_td_net_low NUMBER;
261 l_td_net_high NUMBER;
262 l_td_tax_col NUMBER;
263
264
265
266 cursor csr_paye is
267 select pur.row_low_range_or_name,
268 pur.row_high_range,
269 puci.value
270 from pay_user_tables put,
271 pay_user_rows_f pur,
272 pay_user_columns puc,
273 pay_user_column_instances_f puci
274 where put.user_table_id = puc.user_table_id
275 and pur.user_table_id = puc.user_table_id
276 and puci.user_row_id = pur.user_row_id
277 and puci.user_column_id = puc.user_column_id
278 and upper(puc.user_column_name) = upper('paye_percentage')
279 and put.legislation_code = 'GB'
280 and upper(put.user_table_name) = upper('PAYE')
281 and p_session_date between puci.effective_start_date and puci.effective_end_date
282 and p_session_date between pur.effective_start_date and pur.effective_end_date
283 order by pur.row_low_range_or_name;
284
285
286 cursor csr_scot_paye is
287 select pur.row_low_range_or_name,
288 pur.row_high_range,
289 puci.value
290 from pay_user_tables put,
291 pay_user_rows_f pur,
292 pay_user_columns puc,
293 pay_user_column_instances_f puci
294 where put.user_table_id = puc.user_table_id
295 and pur.user_table_id = puc.user_table_id
296 and puci.user_row_id = pur.user_row_id
297 and puci.user_column_id = puc.user_column_id
298 and upper(puc.user_column_name) = upper('paye_percentage_svr')
299 and put.legislation_code = 'GB'
300 and upper(put.user_table_name) = upper('PAYE')
301 and p_session_date between puci.effective_start_date and puci.effective_end_date
302 and p_session_date between pur.effective_start_date and pur.effective_end_date
303 order by pur.row_low_range_or_name;
304
305
306 begin
307
308 --hr_utility.trace_on(null,'FOT');
309
310 hr_utility.trace('ENTERING: pay_gb_paye_calc.tax_to_date');
311
312 hr_utility.trace('*********************');
313 hr_utility.trace('INPUT values: ');
314 hr_utility.trace('Session date: '||to_char(p_session_date,'DD/MM/YYYY'));
315 hr_utility.trace('Taxable Pay: '||p_taxable_pay);
316 hr_utility.trace('Tax code: '||p_tax_code);
317 hr_utility.trace('Tax basis: '||p_tax_basis);
318 hr_utility.trace('Stat annual periods: '||p_stat_annual_periods);
319 hr_utility.trace('Current pay period: '||p_current_period);
320 hr_utility.trace('*********************');
321
322 l_taxable_pay := p_taxable_pay;
323
324
325 /* Convert down to nearest pound */
326
327 l_taxable_pay := floor(l_taxable_pay);
328
329 hr_utility.trace('Rounded taxable pay: '||l_taxable_pay);
330
331
332 /* Check for Scottish tax code, S prefix */
333
334 if upper(substrb(p_tax_code,1,1)) <> 'S'
335 then
336
337
338 -- Populate PL/SQL table if not already populated
339 -- else bypass this phase
340
341 if not g_table_inited
342 then
343
344 hr_utility.trace('Initing PAYE table...');
345
346 for r_paye in csr_paye loop
347
348 l_row_num := l_row_num + 1;
349
350 tbl_paye_table(l_row_num).g_gross_low_value := r_paye.row_low_range_or_name;
351 tbl_paye_table(l_row_num).g_gross_high_value := r_paye.row_high_range;
352 tbl_paye_table(l_row_num).g_rate := r_paye.value;
353 tbl_paye_table(l_row_num).g_gross_denom := 100 - r_paye.value;
354
355 /* First iteration, set net low value to 0 */
356 /* Set tax to be rated percentage of gross high value for this row */
357 /* Set tax column for this row to 0 */
358 if l_row_num = 1
359 then
360
361 tbl_paye_table(l_row_num).g_net_low_value := 0;
362
363 tbl_paye_table(l_row_num).g_tax_deduct := tbl_paye_table(l_row_num).g_gross_high_value * (tbl_paye_table(l_row_num).g_rate/100);
364 tbl_paye_table(l_row_num).g_tax_column := 0;
365
366 /* Subsequent iterations, set net low value to be net high value of previous row + 0.01*/
367 /* get gross high value of this row first, then subtract from that the gross high values of previous rows*/
368 else
369
370 tbl_paye_table(l_row_num).g_net_low_value := tbl_paye_table(l_row_num-1).g_net_high_value + 0.01;
371
372 l_temp_val := tbl_paye_table(l_row_num).g_gross_high_value;
373
374 for i in reverse 1..l_row_num-1 loop
375
376 l_temp_val := l_temp_val - tbl_paye_table(i).g_gross_high_value;
377
378 end loop;
379
380 /* Get the percentage of the values found above */
381 l_current_deduct := l_temp_val * (tbl_paye_table(l_row_num).g_rate/100);
382
383 /* add percentage for this row to that of the row before to get */
384 /* max deductible amount for this row */
385 /* set tax column to tax deductible value of previous row */
386 tbl_paye_table(l_row_num).g_tax_deduct := l_current_deduct + tbl_paye_table(l_row_num-1).g_tax_deduct;
387
388 tbl_paye_table(l_row_num).g_tax_column := tbl_paye_table(l_row_num-1).g_tax_deduct;
389
390
391
392 end if;
393
394 /* Find net high value for this row by subtracting the max deductible amount */
395 /* from the gross high value for this row */
396 tbl_paye_table(l_row_num).g_net_high_value := tbl_paye_table(l_row_num).g_gross_high_value - tbl_paye_table(l_row_num).g_tax_deduct;
397
398 end loop;
399
400 g_table_inited := TRUE;
401
402 end if;
403
404 -- Show what's in the table
405
406 for loop_count in 1..tbl_paye_table.count loop
407
408
409 hr_utility.trace('*************************');
410 hr_utility.trace('Tax band '||loop_count);
411 hr_utility.trace('*************************');
412 hr_utility.trace('Gross low: '||tbl_paye_table(loop_count).g_gross_low_value||' Gross high: '||tbl_paye_table(loop_count).g_gross_high_value);
413 hr_utility.trace('Rate: '||tbl_paye_table(loop_count).g_rate);
414 hr_utility.trace('Gross denominator: '||tbl_paye_table(loop_count).g_gross_denom);
415 hr_utility.trace('Net low: '||tbl_paye_table(loop_count).g_net_low_value||' Net high: '||tbl_paye_table(loop_count).g_net_high_value);
416 hr_utility.trace('Tax deduct: '||tbl_paye_table(loop_count).g_tax_deduct);
417 hr_utility.trace('Tax column: '||tbl_paye_table(loop_count).g_tax_column);
418 hr_utility.trace('************************');
419
420
421 if p_tax_basis = 'C'
422 then
423
424 l_td_net_low := (tbl_paye_table(loop_count).g_net_low_value * p_current_period) / p_stat_annual_periods;
425 l_td_net_high := (tbl_paye_table(loop_count).g_net_high_value * p_current_period) / p_stat_annual_periods;
426 l_td_tax_col := (tbl_paye_table(loop_count).g_tax_column * p_current_period) / p_stat_annual_periods;
427
428
429 else
430
431 l_td_net_low := tbl_paye_table(loop_count).g_net_low_value / p_stat_annual_periods;
432 l_td_net_high := tbl_paye_table(loop_count).g_net_high_value / p_stat_annual_periods;
433 l_td_tax_col := tbl_paye_table(loop_count).g_tax_column / p_stat_annual_periods;
434
435 end if;
436
437
438
439 if (l_taxable_pay <= ceil(l_td_net_high)
440 AND l_taxable_pay >= ceil(l_td_net_low))
441 then
442
443 hr_utility.trace('Band/loop count: '||loop_count);
444 hr_utility.trace('TD NL: '||l_td_net_low);
445 hr_utility.trace('TD NH: '||l_td_net_high);
446 hr_utility.trace('TD TC: '||l_td_tax_col);
447
448 l_tax_liable := l_td_tax_col +
449 (((l_taxable_pay - l_td_net_low) * (tbl_paye_table(loop_count).g_rate/100))
450 * (100/tbl_paye_table(loop_count).g_gross_denom));
451
452 end if;
453
454
455 end loop;
456
457
458
459
460 /* Code is Scots tax code, treat accordingly */
461
462 else
463
464 l_scots_code := TRUE;
465
466 -- Populate PL/SQL table if not already populated
467 -- else bypass this phase
468
469 if not g_table_inited
470 then
471
472 hr_utility.trace('Initing PAYE table...');
473
474 for r_scot_paye in csr_scot_paye loop
475
476 l_row_num := l_row_num + 1;
477
478 tbl_paye_table(l_row_num).g_gross_low_value := r_scot_paye.row_low_range_or_name;
479 tbl_paye_table(l_row_num).g_gross_high_value := r_scot_paye.row_high_range;
480 tbl_paye_table(l_row_num).g_rate := r_scot_paye.value;
481 tbl_paye_table(l_row_num).g_gross_denom := 100 - r_scot_paye.value;
482
483 /* First iteration, set net low value to 0 */
484 /* Set tax to be rated percentage of gross high value for this row */
485 /* Set tax column for this row to 0 */
486 if l_row_num = 1
487 then
488
489 tbl_paye_table(l_row_num).g_net_low_value := 0;
490
491 tbl_paye_table(l_row_num).g_tax_deduct := tbl_paye_table(l_row_num).g_gross_high_value * (tbl_paye_table(l_row_num).g_rate/100);
492 tbl_paye_table(l_row_num).g_tax_column := 0;
493
494 /* Subsequent iterations, set net low value to be net high value of previous row + 0.01*/
495 /* get gross high value of this row first, then subtract from that the gross high values of previous rows*/
496 else
497
498 tbl_paye_table(l_row_num).g_net_low_value := tbl_paye_table(l_row_num-1).g_net_high_value + 0.01;
499
500 l_temp_val := tbl_paye_table(l_row_num).g_gross_high_value;
501
502 for i in reverse 1..l_row_num-1 loop
503
504 l_temp_val := l_temp_val - tbl_paye_table(i).g_gross_high_value;
505
506 end loop;
507
508 /* Get the percentage of the values found above */
509 l_current_deduct := l_temp_val * (tbl_paye_table(l_row_num).g_rate/100);
510
511 /* add percentage for this row to that of the row before to get */
512 /* max deductible amount for this row */
513 /* set tax column to tax deductible value of previous row */
514 tbl_paye_table(l_row_num).g_tax_deduct := l_current_deduct + tbl_paye_table(l_row_num-1).g_tax_deduct;
515
516 tbl_paye_table(l_row_num).g_tax_column := tbl_paye_table(l_row_num-1).g_tax_deduct;
517
518
519
520 end if;
521
522 /* Find net high value for this row by subtracting the max deductible amount */
523 /* from the gross high value for this row */
524 tbl_paye_table(l_row_num).g_net_high_value := tbl_paye_table(l_row_num).g_gross_high_value - tbl_paye_table(l_row_num).g_tax_deduct;
525
526 end loop;
527
528 g_table_inited := TRUE;
529
530 end if;
531
532
533 -- Show what's in the table
534
535 for loop_count in 1..tbl_paye_table.count loop
536
537
538 hr_utility.trace('*************************');
539 hr_utility.trace('Tax band '||loop_count);
540 hr_utility.trace('*************************');
541 hr_utility.trace('Gross low: '||tbl_paye_table(loop_count).g_gross_low_value||' Gross high: '||tbl_paye_table(loop_count).g_gross_high_value);
542 hr_utility.trace('Rate: '||tbl_paye_table(loop_count).g_rate);
543 hr_utility.trace('Gross denominator: '||tbl_paye_table(loop_count).g_gross_denom);
544 hr_utility.trace('Net low: '||tbl_paye_table(loop_count).g_net_low_value||' Net high: '||tbl_paye_table(loop_count).g_net_high_value);
545 hr_utility.trace('Tax deduct: '||tbl_paye_table(loop_count).g_tax_deduct);
546 hr_utility.trace('Tax column: '||tbl_paye_table(loop_count).g_tax_column);
547 hr_utility.trace('************************');
548
549
550
551 if p_tax_basis = 'C'
552 then
553
554 l_td_net_low := (tbl_paye_table(loop_count).g_net_low_value * p_current_period) / p_stat_annual_periods;
555 l_td_net_high := (tbl_paye_table(loop_count).g_net_high_value * p_current_period) / p_stat_annual_periods;
556 l_td_tax_col := (tbl_paye_table(loop_count).g_tax_column * p_current_period) / p_stat_annual_periods;
557
558 else
559
560 l_td_net_low := tbl_paye_table(loop_count).g_net_low_value / p_stat_annual_periods;
561 l_td_net_high := tbl_paye_table(loop_count).g_net_high_value / p_stat_annual_periods;
562 l_td_tax_col := tbl_paye_table(loop_count).g_tax_column / p_stat_annual_periods;
563
564 end if;
565
566
567
568 if (l_taxable_pay <= ceil(l_td_net_high)
569 AND l_taxable_pay >= ceil(l_td_net_low))
570 then
571
572 hr_utility.trace('Band/loop count: '||loop_count);
573 hr_utility.trace('TD NL: '||l_td_net_low);
574 hr_utility.trace('TD NH: '||l_td_net_high);
575 hr_utility.trace('TD TC: '||l_td_tax_col);
576
577 l_tax_liable := l_td_tax_col +
578 (((l_taxable_pay - l_td_net_low) * (tbl_paye_table(loop_count).g_rate/100))
579 * (100/tbl_paye_table(loop_count).g_gross_denom));
580
581
582 end if;
583
584
585
586 end loop;
587
588
589
590 end if;
591
592
593 /* Round down */
594
595 l_tax_liable := round(l_tax_liable,4);
596
597 l_tax_liable := trunc(l_tax_liable,2);
598
599 hr_utility.trace('Tax liability: '||l_tax_liable);
600
601
602 if (l_scots_code)
603 then
604
605 hr_utility.trace('Scots code : TRUE');
606
607 else
608
609 hr_utility.trace('Scots code : FALSE');
610
611 end if;
612
613
614 hr_utility.trace('********************');
615 hr_utility.trace('OUTPUT values: ');
616 hr_utility.trace('Tax liability to date: '||l_tax_liable);
617 hr_utility.trace('********************');
618
619 hr_utility.trace('LEAVING: pay_gb_paye_calc.tax_to_date');
620
621 --hr_utility.trace_off;
622
623 return l_tax_liable;
624
625 exception
626
627 when others
628 then raise;
629
630 end tax_to_date;
631 --
632 /*DWP fix start - Bug 16489664 */
633 FUNCTION is_this_retro
634 RETURN NUMBER
635 IS
636 l_retro_status NUMBER;
637 l_request_id number;
638 cursor csr_get_request_details is
639 select request_id from fnd_concurrent_requests
640 where request_id = FND_GLOBAL.CONC_REQUEST_ID;
641 BEGIN
642 hr_utility.set_location('Entering: is_this_retro ', 10);
643 open csr_get_request_details;
644 fetch csr_get_request_details into l_request_id;
645 close csr_get_request_details;
646
647 hr_utility.set_location(' l_request_id '||l_request_id , 20);
648 BEGIN
649 SELECT 1
650 INTO l_retro_status
651 FROM PAY_PAYROLL_ACTIONS
652 WHERE request_id = l_request_id
653 AND ACTION_TYPE IN ('L','G','O');
654 hr_utility.set_location('**** This is retro ' , 30);
655 hr_utility.set_location(' Value of l_retro_status is : '||l_retro_status , 40);
656 EXCEPTION
657 WHEN NO_DATA_FOUND THEN
658 hr_utility.set_location('**** This is Normal Payroll ' , 50);
659 l_retro_status := 0;
660 hr_utility.set_location(' Value of l_retro_status is : '||l_retro_status , 60);
661 END;
662 RETURN l_retro_status;
663 END IS_THIS_RETRO;
664 --
665 /*DWP fix start - Bug 16489664 */
666
667
668 --- Called from GB_TAX_REF_CHK formula.
669 --- Effective from 06-APR-2006.
670
671 function check_tax_ref(p_assignment_id number, p_payroll_id number, p_pay_run_date date,p_payroll_action_id number) return number
672 is
673
674 l_date_soy date ;
675 l_date_eoy date ;
676 l_effective_date date ;
677 l_assgt_creation_date date ;
678 l_return number ;
679 l_record number :=0;
680
681 CURSOR cur_assgt_first_eff_start_date(p_assignment_id number )
682 IS
683 select min(effective_start_date) effective_start_date
684 from per_all_assignments_f
685 where assignment_id = p_assignment_id
686 ;
687
688 /*12662579 Begin
689 CURSOR cur_chk_pay_actions( p_payroll_action_id number, p_assignment_id number )
690 IS
691 select ppa.payroll_id old_payroll_id
692 from pay_payroll_actions ppa,
693 pay_assignment_actions paa
694 where ppa.payroll_action_id = paa.payroll_action_id
695 and paa.assignment_id = p_assignment_id
696 and ppa.payroll_action_id <> p_payroll_action_id
697 and ppa.action_type in ('Q', 'R', 'B', 'I' , 'V')
698 and ppa.effective_date >= l_date_soy
699 and ppa.effective_date <= l_date_eoy ;
700
701
702
703 CURSOR cur_check_payroll_tax_ref(p_old_payroll_id number, p_new_payroll_id number)
704 IS
705 select count(*) l_exist -- if this cursor fetches '1', that means new payroll is valid.
706 from pay_all_payrolls_f pap
707 ,hr_soft_coding_keyflex scl
708 where pap.payroll_id = p_new_payroll_id
709 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
710 and scl.segment1 in
711 (
712 select distinct scl.segment1
713 from pay_all_payrolls_f pap
714 ,hr_soft_coding_keyflex scl
715
716 where
717 pap.payroll_id = p_old_payroll_id
718 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
719 and pap.effective_start_date <= l_effective_date
720 and pap.effective_end_date >= l_effective_date
721 )
722 ;
723 */
724
725 CURSOR cur_check_payroll_tax_ref(p_old_tax_reference varchar2, p_new_payroll_id number)
726 IS
727 select count(*) l_exist -- if this cursor fetches '1', that means new payroll is valid.
728 from pay_all_payrolls_f pap
729 ,hr_soft_coding_keyflex scl
730 where pap.payroll_id = p_new_payroll_id
731 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
732 and scl.segment1 = p_old_tax_reference
733 and l_effective_date between pap.effective_start_date and pap.effective_end_date;
734
735
736 --12662579 end
737
738 CURSOR cur_check_aggregated_asg(p_assignment_id number) is
739 select count (distinct nvl(per_information10,'N') ) l_count
740 from per_all_people_f papf , per_all_assignments_f paaf
741 where paaf.assignment_id=p_assignment_id
742 and papf.person_id=paaf.person_id
743 and papf.effective_start_date > l_date_soy
744 and papf.effective_start_date < l_date_eoy ;
745
746
747
748 /* PAYE Details check for bug 6018309*/
749 CURSOR cur_get_tax_reference(c_payroll_id number) is
750 select hsck.segment1
751 from pay_all_payrolls_f papf,
752 hr_soft_coding_keyflex hsck
753 where
754 papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
755 and papf.payroll_id=c_payroll_id;
756
757 -- Start of bug 7601088 - commented out few columns which are not related to PAYE details.
758 CURSOR cur_chk_tax_code(c_tax_reference varchar2,
759 c_assignment_id number,c_pay_run_date date) IS
760 select count(*)
761 from
762 (
763 select
764 distinct
765 ppev.INPUT_VALUE_ID1,
766 ppev.TAX_CODE,
767 ppev.INPUT_VALUE_ID2,
768 ppev.D_TAX_BASIS,
769 ppev.INPUT_VALUE_ID4,
770 ppev.D_PAY_PREVIOUS,
771 ppev.INPUT_VALUE_ID5,
772 ppev.D_TAX_PREVIOUS,
773 ppev.INPUT_VALUE_ID3,
774 ppev.D_REFUNDABLE,
775 ppev.INPUT_VALUE_ID6,
776 ppev.D_AUTHORITY
777 --ppev.entry_information1,
778 --ppev.entry_information2
779
780 from
781 (
782 SELECT ele.rowid ROW_ID, ele.element_entry_id, min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) INPUT_VALUE_ID1,
783 min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
784 min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) INPUT_VALUE_ID2,
785 min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)) D_Tax_Basis,
786 min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis, min(decode(inv.name, 'Refundable',
787 eev.input_value_id, null)) INPUT_VALUE_ID3,
788 min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)) D_Refundable,
789 --min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
790 min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) INPUT_VALUE_ID4,
791 hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Pay_Previous,
792 --min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) Pay_Previous,
793 min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) INPUT_VALUE_ID5,
794 hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Tax_Previous,
795 --min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) Tax_Previous,
796 min(decode(inv.name, 'Authority', eev.input_value_id, null)) INPUT_VALUE_ID6,
797 min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)) D_AUTHORITY,
798 --min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
799 ele.assignment_id,
800 ele.effective_start_date,
801 ele.effective_end_date,
802 ele.entry_information_category,
803 ele.entry_information1,
804 ele.entry_information2
805 from
806 pay_element_entries_f ele,
807 pay_element_entry_values_f eev,
808 pay_input_values_f inv,
809 pay_element_links_f lnk,
810 pay_element_types_f elt,
811
812 pay_all_payrolls_f papf,
813 per_all_assignments_f paaf,
814 hr_soft_coding_keyflex hsck
815
816 where ele.element_entry_id = eev.element_entry_id
817 AND c_pay_run_date between ele.effective_start_date and ele.effective_end_date
818 AND eev.input_value_id + 0 = inv.input_value_id
819 AND c_pay_run_date between eev.effective_start_date and eev.effective_end_date
820 AND inv.element_type_id = elt.element_type_id
821 AND c_pay_run_date between inv.effective_start_date and inv.effective_end_date
822 AND ele.element_link_id = lnk.element_link_id
823 AND elt.element_type_id = lnk.element_type_id
824 AND c_pay_run_date between lnk.effective_start_date and lnk.effective_end_date
825 AND elt.element_name = 'PAYE Details'
826 AND c_pay_run_date between elt.effective_start_date and elt.effective_end_date
827
828 AND c_pay_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
829 AND c_pay_run_date BETWEEN papf.effective_start_date AND papf.effective_end_date
830
831 AND ele.assignment_id=paaf.assignment_id
832
833 AND papf.payroll_id=paaf.payroll_id
834 AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
835 AND hsck.segment1=c_tax_reference
836 AND paaf.person_id = (select distinct pap.person_id
837 from per_all_people_f pap,
838 per_all_assignments_f paaf1
839 where paaf1.person_id=pap.person_id
840 and paaf1.assignment_id=c_assignment_id)
841 AND pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_tax_reference,c_pay_run_date)
842 = pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_reference,c_pay_run_date)
843 AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_tax_reference,c_pay_run_date)
844 = pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_reference,c_pay_run_date)
845 -- Bug 7601088 Ignore the P45 issued assignments while checking the aggregated assignment PAYE details with in CPE.
846 AND pay_p45_pkg.paye_return_p45_issued_flag(c_assignment_id,p_payroll_action_id) = 'N'
847 AND pay_p45_pkg.paye_return_p45_issued_flag(paaf.assignment_id,p_payroll_action_id) = 'N'
848 /*Bug 7389532 - Added the distinct and null condition */
849 AND paaf.period_of_service_id = (select distinct period_of_service_id
850 from per_all_assignments_f paaf2
851 where paaf2.assignment_id=c_assignment_id
852 and paaf.person_id =paaf2.person_id
853 and period_of_service_id is not null)
854
855 group by ele.rowid, ele.element_entry_id, ele.assignment_id, ele.entry_information_category,
856 ele.entry_information1, ele.entry_information2, ele.effective_start_date, ele.effective_end_date
857 ) ppev
858 );
859
860 -- Bug#9278271
861 CURSOR cur_get_paye_agg(p_assignment_id number,
862 p_pay_run_date date) IS
863 select nvl(PER_INFORMATION10,'N')
864 from per_all_people_f
865 where person_id = (select distinct papf.person_id
866 from per_all_people_f papf, per_all_assignments_f paaf1
867 where papf.person_id=paaf1.person_id
868 and paaf1.assignment_id= p_assignment_id)
869 and p_pay_run_date between effective_start_date and effective_end_date;
870
871 --l_cur_chk_pay_actions cur_chk_pay_actions%ROWTYPE; 12662579
872 l_cur_check_payroll_tax_ref cur_check_payroll_tax_ref%ROWTYPE;
873 l_cur_check_aggregated_asg cur_check_aggregated_asg%ROWTYPE;
874
875 l_tax_reference_code varchar2(30);
876 l_count_c number default 0;
877 l_aggregated_asg varchar2(10);
878 l_pay_run_date date;
879
880 l_old_tax_reference hr_soft_coding_keyflex.segment1%type; --12662579 Begin
881 begin
882 l_return:=0;
883
884
885 hr_utility.set_location('Enter CHECK_TAX_REF',10);
886 hr_utility.set_location(' payroll action id '||p_payroll_action_id , 20);
887
888
889 /*PAYE Details check for bug 6018309*/
890
891 select regular_payment_date into l_pay_run_date
892 from per_time_periods ptp ,
893 pay_payroll_actions ppa
894 where ptp.time_period_id=ppa.time_period_id
895 and ppa.payroll_action_id=p_payroll_action_id;
896
897
898 open cur_get_tax_reference(p_payroll_id);
899 fetch cur_get_tax_reference into l_tax_reference_code;
900 close cur_get_tax_reference;
901
902 -- Start of bug#9278271
903 /* select nvl(PER_INFORMATION10,'N') into l_aggregated_asg
904 from per_all_people_f
905 where person_id = (select distinct papf.person_id
906 from per_all_people_f papf, per_all_assignments_f paaf1
907 where papf.person_id=paaf1.person_id
908 AND paaf1.assignment_id= p_assignment_id
909 )
910 and l_pay_run_date between effective_start_date and effective_end_date;*/
911
912 open cur_get_paye_agg(p_assignment_id,l_pay_run_date);
913 fetch cur_get_paye_agg into l_aggregated_asg;
914 close cur_get_paye_agg;
915 -- End of bug#9278271
916
917 if (l_aggregated_asg='Y') then
918 open cur_chk_tax_code(l_tax_reference_code,p_assignment_id, l_pay_run_date);
919 fetch cur_chk_tax_code into l_count_c;
920 close cur_chk_tax_code;
921
922 /* Bug 7492621 the PAYE mismatch error should come after 6th April 2008 check is added*/
923 --if (l_count_c > 1) and (p_pay_run_date >= to_date('06/04/2008','dd/mm/yyyy')) then
924 /*Bug 7492621 - changed the return value to 0 so the error won't come.*/
925 --l_return:= 3;
926 -- BUg 7601088 the PAYE mismatch error added after 6th April 2009.
927 if (l_count_c > 1) and (p_pay_run_date >= to_date('06/04/2010','dd/mm/yyyy')) then -- for bug 9127942
928 l_return:= 3;
929 return l_return; /* Exit the function */
930 end if;
931 end if;
932 /* end of check for bug 6018309*/
933
934 select effective_date into l_effective_date
935 from pay_payroll_actions ppa
936 where ppa.payroll_action_id=p_payroll_action_id;
937
938
939 hr_utility.set_location('effetive date:'||to_char(l_effective_date),12);
940
941 If l_effective_date >=to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
942 l_date_soy := to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
943 l_date_eoy := to_date('05-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
944 Else
945 l_date_soy := to_date('06-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
946 l_date_eoy := to_date('05-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
947 End If;
948
949 hr_utility.set_location('effetive date:'||to_char(l_effective_date)||'l_date_soy'||to_char(l_date_soy)||'l_date_eoy'||to_char(l_date_eoy),12);
950
951 open cur_assgt_first_eff_start_date(p_assignment_id);
952 fetch cur_assgt_first_eff_start_date into l_assgt_creation_date;
953
954 hr_utility.set_location('Payroll_id'||to_char(p_payroll_id)||'Assignment_id:'||to_char(p_assignment_id),13);
955
956 open cur_check_aggregated_asg(p_assignment_id);
957 fetch cur_check_aggregated_asg into l_cur_check_aggregated_asg;
958
959 if(l_cur_check_aggregated_asg.l_count>1) then
960 l_return :=2;
961 end if;
962 close cur_check_aggregated_asg;
963
964 -- 12662579 begin
965 begin
966 hr_utility.set_location('Before fetching tax references for this Assignment ',11);
967 --This will fetch the PAYE reference for the Assignment in the current Tax year
968 select distinct scl.segment1 into l_old_tax_reference
969 from pay_payroll_actions ppa,
970 pay_assignment_actions paa,
971 pay_all_payrolls_f pap,
972 hr_soft_coding_keyflex scl
973 where ppa.payroll_action_id = paa.payroll_action_id
974 and paa.assignment_id = p_assignment_id
975 and ppa.payroll_action_id <> p_payroll_action_id
976 and ppa.action_type in ('Q', 'R', 'B', 'I' , 'V')
977 and ppa.effective_date >= l_date_soy
978 and ppa.effective_date <= l_date_eoy
979 and pap.payroll_id = ppa.payroll_id
980 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
981 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date;
982 hr_utility.set_location('After fetching tax references for this Assignment '||l_old_tax_reference,12);
983 exception
984 when too_many_rows then
985 hr_utility.set_location('Error - Multiple PAYE references for this Assignment ',13);
986 l_return :=1;
987 when no_data_found then
988 --first payroll run in the tax year . No issues
989 null;
990 end;
991
992 if l_return = 0 and l_old_tax_reference is not null then
993 for l_cur_check_payroll_tax_ref in cur_check_payroll_tax_ref(l_old_tax_reference , p_payroll_id)
994 loop
995 --This is to check the current payroll action's PAYE reference is same as the earlier PAYE reference
996 if ( l_cur_check_payroll_tax_ref.l_exist =0) then
997 hr_utility.set_location('Error - PAYE reference for this Assignment is changed ',13);
998 l_return :=1;
999 exit when l_return<>0;
1000 end if ;
1001 end loop;
1002 end if;
1003
1004 --
1005 /*
1006 for l_cur_chk_pay_actions in cur_chk_pay_actions(p_payroll_action_id,p_assignment_id)
1007 loop
1008 hr_utility.set_location('Old payroll Id:'||to_char(l_cur_chk_pay_actions.old_payroll_id)||' New payroll:'||to_char(p_payroll_id)||'l_return:'||to_char(l_return)||'Rows'||to_char(cur_chk_pay_actions%ROWCOUNT),13);
1009 exit when l_return<>0;
1010
1011 for l_cur_check_payroll_tax_ref in cur_check_payroll_tax_ref(l_cur_chk_pay_actions.old_payroll_id , p_payroll_id)
1012 loop
1013
1014 if ( l_cur_check_payroll_tax_ref.l_exist =0) then
1015 l_return :=1;
1016 exit when l_return<>0;
1017 end if ;
1018
1019 end loop;
1020
1021 end loop;
1022 12662579 end */
1023
1024
1025 hr_utility.set_location('L_return '||to_char(l_return)||'l_date_soy:'||to_char(l_date_soy)||'l_date_eoy:'||to_char(l_date_eoy),13);
1026
1027 hr_utility.set_location('Exit CHECK_TAX_REF',15);
1028
1029 close cur_assgt_first_eff_start_date;
1030
1031 /*DWP fix start- Bug 16489664*/
1032 begin
1033 select 1 into l_record
1034 from PAY_PATCH_STATUS
1035 where PATCH_NAME = 'DWP_RETRO_VAL_SKIP';
1036
1037 hr_utility.set_location(' l_record if dwp record exist: '||l_record , 20);
1038 exception
1039 when NO_DATA_FOUND then
1040 hr_utility.set_location(' l_record if no dwp record : '||l_record , 25);
1041 end;
1042
1043
1044 if l_return = 1 then
1045 if (is_this_retro() = 1 and l_record = 1) then
1046 hr_utility.set_location(' skipping paye ref change validation for dwp retro-process ', 30);
1047 l_return :=0;
1048 end if;
1049 end if ;
1050 /*DWP fix end - Bug 16489664*/
1051
1052 return l_return;
1053 end check_tax_ref;
1054
1055
1056
1057 -- Function tax_year_of_pensioners_death : Bug 4528372
1058 -- To find out if the date of death of the pensioner is in the same tax year as
1059 -- the date of the payment
1060 -- called from PAYE formula for persioners VALIDATE_TAX_YEAR_OF_DEATH
1061
1062
1063 function tax_year_of_pensioners_death(p_assignmnet_id IN number ,p_pay_run_date IN date)
1064 return varchar2
1065 is
1066
1067 l_return number ;
1068 l_tax_year_start date ;
1069 l_tax_year_end date ;
1070 l_date_of_death date := to_date('31-12-4712','DD-MM-YYYY') ;
1071
1075 l_end_factor NUMBER;
1072 l_pay_date number;
1073 l_pay_month number;
1074 l_start_factor NUMBER;
1076
1077
1078
1079 cursor csr_date_of_death is
1080 select PEOPLE.DATE_OF_DEATH
1081 from per_all_assignments_f ASSIGN
1082 ,per_all_people_f PEOPLE
1083 ,fnd_sessions SES
1084 where SES.effective_date BETWEEN ASSIGN.effective_start_date
1085 AND ASSIGN.effective_end_date
1086 and SES.effective_date BETWEEN PEOPLE.effective_start_date
1087 AND PEOPLE.effective_end_date
1088 and ASSIGN.assignment_id = p_assignmnet_id
1089 and PEOPLE.person_id = ASSIGN.person_id
1090 and PEOPLE.per_information4 ='Y'
1091 and PEOPLE.DATE_OF_DEATH is not null
1092 and SES.session_id = USERENV('sessionid') ;
1093
1094
1095 BEGIN
1096
1097 hr_utility.set_location('tax_year_of_pensioners_death',0);
1098
1099 l_pay_date := to_number( to_char( p_pay_run_date ,'DD' ) ) ;
1100 l_pay_month := to_number( to_char( p_pay_run_date ,'MM' ) ) ;
1101
1102 If l_pay_month >=4 and l_pay_date >=6 then
1103 l_start_factor := 0;
1104 l_end_factor := 1;
1105 end if;
1106
1107 If ( l_pay_month >=4 and l_pay_date < 6 ) OR l_pay_month < 4 then
1108 l_start_factor := 1;
1109 l_end_factor := 0;
1110 end if;
1111
1112 l_tax_year_start := to_date('06-04-' ||
1113 to_char(to_number(to_char(p_pay_run_date,'YYYY' ) ) -l_start_factor ),'DD-MM-YYYY' );
1114
1115 l_tax_year_end := to_date('05-04-' ||
1116 to_char(to_number(to_char(p_pay_run_date,'YYYY' ) ) + l_end_factor ),'DD-MM-YYYY' );
1117
1118 open csr_date_of_death;
1119 fetch csr_date_of_death into l_date_of_death;
1120 close csr_date_of_death;
1121
1122 if l_date_of_death >= l_tax_year_start and l_date_of_death <= l_tax_year_end then
1123 l_return :=1;
1124 else
1125 l_return :=0;
1126 end if;
1127
1128 hr_utility.set_location('tax_year_of_pensioners_death',99);
1129 return(l_return);
1130
1131 END tax_year_of_pensioners_death;
1132
1133
1134 /* End of package body */
1135 end pay_gb_paye_calc;