eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_GB_PAYE_CALC

Source


1 package body pay_gb_paye_calc as
2 /* $Header: pygbpaye.pkb 120.6.12010000.2 2008/10/03 09:08:21 rlingama 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 */
43 
44 
45 /* Calculate free pay for given amount */
46 function free_pay
47 ( p_amount IN NUMBER,
48   p_tax_code IN VARCHAR2,
49   p_tax_basis IN VARCHAR2,
50   p_stat_annual_periods IN NUMBER,
51   p_current_period IN NUMBER)
52 return NUMBER
53 as
54 
55 l_tax_code 		VARCHAR2(8);
56 l_scots_code		BOOLEAN := FALSE;
57 l_k_code 		BOOLEAN := FALSE;
58 l_num_code		NUMBER;
59 l_remainder		NUMBER;
60 l_part1			NUMBER;
61 l_ann_value		NUMBER;
62 l_period1_pay		NUMBER;
63 l_free_or_add_pay	NUMBER;
64 l_taxable_pay		NUMBER;
65 l_amount 		NUMBER;
66 
67 
68 
69 begin
70 
71 --hr_utility.trace_on(null,'FOT');
72 
73 hr_utility.trace('ENTERING: pay_gb_paye_calc.free_pay');
74 
75 hr_utility.trace('***********************');
76 hr_utility.trace('INPUT values:	     ');
77 hr_utility.trace('Amount: '||p_amount);
78 hr_utility.trace('Tax code: '||p_tax_code);
79 hr_utility.trace('Tax basis: '||p_tax_basis);
80 hr_utility.trace('Stat annual periods: '||p_stat_annual_periods);
81 hr_utility.trace('Current pay period: '||p_current_period);
82 hr_utility.trace('***********************');
83 
84 l_amount := p_amount;
85 
86 /* Check for Scottish tax code, S prefix, strip off S prefix if present */
87 if upper(substrb(p_tax_code,1,1)) = 'S'
88 then
89 
90    l_scots_code := TRUE;
91    l_tax_code := ltrim(p_tax_code,'S');
92 
93 else
94 
95    l_tax_code := p_tax_code;
96 
97 end if;
98 
99 
100 /* Check for K codes */
101 if substrb(l_tax_code,1,1) = 'K'
102 then
103 
104    l_k_code := TRUE;
105    l_num_code := to_number(substrb(l_tax_code,2,length(l_tax_code) -1 ));
106 
107 
108 else
109 
110 
111    l_num_code := to_number(substrb(l_tax_code,1,length(l_tax_code) -1 ));
112 
113 end if;
114 
115 hr_utility.trace('Numeric component: '||l_num_code);
116 
117 
118 /* Check if numeric component > 500 */
119 if l_num_code > 500
120 then
121 
122     l_remainder := floor((l_num_code - 1)/500);
123     l_part1 := l_num_code - ( l_remainder * 500) + 1;
124     l_ann_value := ((l_part1 * 10) + 9) + (5000 * l_remainder);
125 
126 
127 else
128 
129     l_ann_value := (l_num_code * 10) + 9;
130 
131 end if;
132 
133 hr_utility.trace('Annual free/additional pay: '||l_ann_value);
134 
135 
136 
137 /* Calculate free/additional pay for period 1, apply rounding rules*/
138 
139 l_period1_pay := (l_ann_value/p_stat_annual_periods);
140 
141 if mod((l_period1_pay * 100),1) <> 0
142 then
143 
144    l_period1_pay := round(l_period1_pay,4);
145 
146    l_period1_pay := trunc(l_period1_pay + 0.01,2);
147 
148 end if;
149 
150 
151 hr_utility.trace('Free/additional pay for period 1: '||l_period1_pay);
152 
153 
154 /* If tax basis is cumulative, find free/additional pay for year to date */
155 /* else just use free/additional pay for period 1			 */
156 
157 if p_tax_basis = 'C'
158 then
159 
160 	/* Calculate free/additional pay for year to date, based on current pay period (cumulative basis only) */
161 
162 
163 	l_free_or_add_pay := p_current_period * l_period1_pay;
164 
165 else
166 
167 	l_free_or_add_pay := l_period1_pay;
168 
169 
170 end if;
171 
172 
173 hr_utility.trace('Free/additional pay : '||l_free_or_add_pay);
174 
175 
176 
177 /* Now calculate the taxable pay to date by subtracting/adding free/additional pay */
178 /* to the cumulative value entered in as an input value */
179 /* 1) If taxable pay is less then cumulative/period 1 free pay and tax code is not K, then set free pay to 0 */
180 /* as subtracting free pay from taxable pay would result in negative amount (cumulative tax basis only)*/
181 /* 2) If tax code was a K code then add the free/additional pay, else subtract it */
182 
183 
184 if (l_k_code)
185 then
186 
187    hr_utility.trace('K code is TRUE');
188    l_taxable_pay := l_amount + l_free_or_add_pay;
189 
190 else
191 
192    hr_utility.trace('K code is FALSE');
193 
194    if (l_free_or_add_pay >= l_amount)
195    then
196 
197      hr_utility.trace('Free pay exceeds taxable pay for this period - setting taxable pay to 0');
198      l_taxable_pay := 0;
199 
200    else
201 
202      l_taxable_pay := l_amount - l_free_or_add_pay;
203 
204    end if;
205 
206 end if;
207 
208 hr_utility.trace('********************');
209 hr_utility.trace('OUTPUT values:      ');
210 hr_utility.trace('Taxable pay: '||l_taxable_pay);
211 hr_utility.trace('********************');
212 
213 hr_utility.trace('LEAVING: pay_gb_paye_calc.free_pay');
214 
215 --hr_utility.trace_off;
216 
217 return l_taxable_pay;
218 
219 exception
220 
221 
222 when others
223 then raise;
224 
225 end free_pay;
226 
227 /* Calculate tax due to date on taxable pay */
228 
229 function tax_to_date
230 (p_session_date IN DATE,
231  p_taxable_pay IN NUMBER,
232  p_tax_code IN VARCHAR2,
233  p_tax_basis IN VARCHAR2,
234  p_stat_annual_periods IN NUMBER,
235  p_current_period IN NUMBER)
236 return NUMBER
237 as
238 
239 
240 l_row_num            NUMBER := 0;
241 l_current_deduct     NUMBER;
242 l_band_tax_deduct    NUMBER;
243 l_temp_val 	     NUMBER;
244 l_tax_liable	     NUMBER;
245 l_scots_code	     BOOLEAN := FALSE;
246 l_taxable_pay	     NUMBER;
247 
248 
249 l_td_net_low         NUMBER;
250 l_td_net_high	     NUMBER;
251 l_td_tax_col	     NUMBER;
252 
253 
254 
255 cursor csr_paye is
256    select pur.row_low_range_or_name,
257    	  pur.row_high_range,
258    	  puci.value
259    from pay_user_tables put,
260    	pay_user_rows_f pur,
261    	pay_user_columns puc,
262    	pay_user_column_instances_f puci
263    where put.user_table_id = puc.user_table_id
264    and pur.user_table_id = puc.user_table_id
265    and puci.user_row_id = pur.user_row_id
266    and puci.user_column_id = puc.user_column_id
267    and upper(puc.user_column_name) = upper('paye_percentage')
268    and put.legislation_code = 'GB'
269    and upper(put.user_table_name) = upper('PAYE')
270    and p_session_date between puci.effective_start_date and puci.effective_end_date
271    and p_session_date between pur.effective_start_date and pur.effective_end_date
272    order by pur.row_low_range_or_name;
273 
274 
275 cursor csr_scot_paye is
276    select pur.row_low_range_or_name,
277    	  pur.row_high_range,
278    	  puci.value
279    from pay_user_tables put,
280    	pay_user_rows_f pur,
281    	pay_user_columns puc,
282    	pay_user_column_instances_f puci
283    where put.user_table_id = puc.user_table_id
284    and pur.user_table_id = puc.user_table_id
285    and puci.user_row_id = pur.user_row_id
286    and puci.user_column_id = puc.user_column_id
287    and upper(puc.user_column_name) = upper('paye_percentage_svr')
288    and put.legislation_code = 'GB'
289    and upper(put.user_table_name) = upper('PAYE')
290    and p_session_date between puci.effective_start_date and puci.effective_end_date
291    and p_session_date between pur.effective_start_date and pur.effective_end_date
292    order by pur.row_low_range_or_name;
293 
294 
295 begin
296 
297 --hr_utility.trace_on(null,'FOT');
298 
299 hr_utility.trace('ENTERING: pay_gb_paye_calc.tax_to_date');
300 
301 hr_utility.trace('*********************');
302 hr_utility.trace('INPUT values: 	   ');
303 hr_utility.trace('Session date: '||to_char(p_session_date,'DD/MM/YYYY'));
304 hr_utility.trace('Taxable Pay: '||p_taxable_pay);
305 hr_utility.trace('Tax code: '||p_tax_code);
306 hr_utility.trace('Tax basis: '||p_tax_basis);
307 hr_utility.trace('Stat annual periods: '||p_stat_annual_periods);
308 hr_utility.trace('Current pay period: '||p_current_period);
309 hr_utility.trace('*********************');
310 
311 l_taxable_pay := p_taxable_pay;
312 
313 
314 /* Convert down to nearest pound */
315 
316 l_taxable_pay := floor(l_taxable_pay);
317 
318 hr_utility.trace('Rounded taxable pay: '||l_taxable_pay);
319 
320 
321 /* Check for Scottish tax code, S prefix */
322 
323 if upper(substrb(p_tax_code,1,1)) <> 'S'
324 then
325 
326 
327 -- Populate PL/SQL table if not already populated
328 -- else bypass this phase
329 
330 if not g_table_inited
331 then
332 
333    hr_utility.trace('Initing PAYE table...');
334 
335 	for r_paye in csr_paye loop
336 
337 	  l_row_num := l_row_num + 1;
338 
339 	  tbl_paye_table(l_row_num).g_gross_low_value := r_paye.row_low_range_or_name;
340 	  tbl_paye_table(l_row_num).g_gross_high_value := r_paye.row_high_range;
341 	  tbl_paye_table(l_row_num).g_rate := r_paye.value;
342 	  tbl_paye_table(l_row_num).g_gross_denom := 100 - r_paye.value;
343 
344 	  /* First iteration, set net low value to 0 */
345 	  /* Set tax to be rated percentage of gross high value for this row */
346 	  /* Set tax column for this row to 0 */
347 	  if l_row_num = 1
348 	  then
349 
350 	     tbl_paye_table(l_row_num).g_net_low_value := 0;
351 
352 	     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);
353 	     tbl_paye_table(l_row_num).g_tax_column := 0;
354 
355 	  /* Subsequent iterations, set net low value to be net high value of previous row + 0.01*/
356 	  /* get gross high value of this row first, then subtract from that the gross high values of previous rows*/
357 	  else
358 
359 	     tbl_paye_table(l_row_num).g_net_low_value := tbl_paye_table(l_row_num-1).g_net_high_value + 0.01;
360 
361 	     l_temp_val := tbl_paye_table(l_row_num).g_gross_high_value;
362 
363 	     for i in reverse 1..l_row_num-1 loop
364 
365 	      l_temp_val := l_temp_val - tbl_paye_table(i).g_gross_high_value;
366 
367 	     end loop;
368 
369 	  /* Get the percentage of the values found above */
370 	     l_current_deduct := l_temp_val * (tbl_paye_table(l_row_num).g_rate/100);
371 
372 	  /* add percentage for this row to that of the row before to get */
373 	  /* max deductible amount for this row */
374 	  /* set tax column to tax deductible value of previous row */
375 	     tbl_paye_table(l_row_num).g_tax_deduct := l_current_deduct + tbl_paye_table(l_row_num-1).g_tax_deduct;
376 
377 	     tbl_paye_table(l_row_num).g_tax_column := tbl_paye_table(l_row_num-1).g_tax_deduct;
378 
379 
380 
381 	  end if;
382 
383 	 /* Find net high value for this row by subtracting the max deductible amount */
384 	 /* from the gross high value for this row */
385 	 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;
386 
387 	end loop;
388 
389 g_table_inited := TRUE;
390 
391 end if;
392 
393 -- Show what's in the table
394 
395 for loop_count in 1..tbl_paye_table.count loop
396 
397 
398    hr_utility.trace('*************************');
399    hr_utility.trace('Tax band '||loop_count);
400    hr_utility.trace('*************************');
404    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);
401    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);
402    hr_utility.trace('Rate: '||tbl_paye_table(loop_count).g_rate);
403    hr_utility.trace('Gross denominator: '||tbl_paye_table(loop_count).g_gross_denom);
405    hr_utility.trace('Tax deduct: '||tbl_paye_table(loop_count).g_tax_deduct);
406    hr_utility.trace('Tax column: '||tbl_paye_table(loop_count).g_tax_column);
407    hr_utility.trace('************************');
408 
409 
410    if p_tax_basis = 'C'
411    then
412 
413       l_td_net_low := (tbl_paye_table(loop_count).g_net_low_value * p_current_period) / p_stat_annual_periods;
414       l_td_net_high := (tbl_paye_table(loop_count).g_net_high_value * p_current_period) / p_stat_annual_periods;
415       l_td_tax_col := (tbl_paye_table(loop_count).g_tax_column * p_current_period) / p_stat_annual_periods;
416 
417 
418    else
419 
420       l_td_net_low := tbl_paye_table(loop_count).g_net_low_value / p_stat_annual_periods;
421       l_td_net_high := tbl_paye_table(loop_count).g_net_high_value / p_stat_annual_periods;
422       l_td_tax_col := tbl_paye_table(loop_count).g_tax_column / p_stat_annual_periods;
423 
424    end if;
425 
426 
427 
428    if (l_taxable_pay <= ceil(l_td_net_high)
429       AND l_taxable_pay >= ceil(l_td_net_low))
430    then
431 
432         hr_utility.trace('Band/loop count: '||loop_count);
433         hr_utility.trace('TD NL: '||l_td_net_low);
434         hr_utility.trace('TD NH: '||l_td_net_high);
435         hr_utility.trace('TD TC: '||l_td_tax_col);
436 
437         l_tax_liable := l_td_tax_col +
438 		    (((l_taxable_pay - l_td_net_low) * (tbl_paye_table(loop_count).g_rate/100))
439                      * (100/tbl_paye_table(loop_count).g_gross_denom));
440 
441    end if;
442 
443 
444 end loop;
445 
446 
447 
448 
449 /* Code is Scots tax code, treat accordingly */
450 
451 else
452 
453 l_scots_code := TRUE;
454 
455 -- Populate PL/SQL table if not already populated
456 -- else bypass this phase
457 
458 if not g_table_inited
459 then
460 
461   hr_utility.trace('Initing PAYE table...');
462 
463 	for r_scot_paye in csr_scot_paye loop
464 
465 	  l_row_num := l_row_num + 1;
466 
467 	  tbl_paye_table(l_row_num).g_gross_low_value := r_scot_paye.row_low_range_or_name;
468 	  tbl_paye_table(l_row_num).g_gross_high_value := r_scot_paye.row_high_range;
469 	  tbl_paye_table(l_row_num).g_rate := r_scot_paye.value;
470 	  tbl_paye_table(l_row_num).g_gross_denom := 100 - r_scot_paye.value;
471 
472 	  /* First iteration, set net low value to 0 */
473 	  /* Set tax to be rated percentage of gross high value for this row */
474 	  /* Set tax column for this row to 0 */
475 	  if l_row_num = 1
476 	  then
477 
478 	     tbl_paye_table(l_row_num).g_net_low_value := 0;
479 
480 	     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);
481 	     tbl_paye_table(l_row_num).g_tax_column := 0;
482 
483 	  /* Subsequent iterations, set net low value to be net high value of previous row + 0.01*/
484 	  /* get gross high value of this row first, then subtract from that the gross high values of previous rows*/
485 	  else
486 
487 	     tbl_paye_table(l_row_num).g_net_low_value := tbl_paye_table(l_row_num-1).g_net_high_value + 0.01;
488 
489 	     l_temp_val := tbl_paye_table(l_row_num).g_gross_high_value;
490 
491 	     for i in reverse 1..l_row_num-1 loop
492 
493 	      l_temp_val := l_temp_val - tbl_paye_table(i).g_gross_high_value;
494 
495 	     end loop;
496 
497 	  /* Get the percentage of the values found above */
498 	     l_current_deduct := l_temp_val * (tbl_paye_table(l_row_num).g_rate/100);
499 
500 	  /* add percentage for this row to that of the row before to get */
501 	  /* max deductible amount for this row */
502 	  /* set tax column to tax deductible value of previous row */
503 	     tbl_paye_table(l_row_num).g_tax_deduct := l_current_deduct + tbl_paye_table(l_row_num-1).g_tax_deduct;
504 
505 	     tbl_paye_table(l_row_num).g_tax_column := tbl_paye_table(l_row_num-1).g_tax_deduct;
506 
507 
508 
509 	  end if;
510 
511 	 /* Find net high value for this row by subtracting the max deductible amount */
512 	 /* from the gross high value for this row */
513 	 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;
514 
515 	end loop;
516 
517 g_table_inited := TRUE;
518 
519 end if;
520 
521 
522 -- Show what's in the table
523 
524 for loop_count in 1..tbl_paye_table.count loop
525 
526 
527    hr_utility.trace('*************************');
528    hr_utility.trace('Tax band '||loop_count);
529    hr_utility.trace('*************************');
530    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);
531    hr_utility.trace('Rate: '||tbl_paye_table(loop_count).g_rate);
532    hr_utility.trace('Gross denominator: '||tbl_paye_table(loop_count).g_gross_denom);
536    hr_utility.trace('************************');
533    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);
534    hr_utility.trace('Tax deduct: '||tbl_paye_table(loop_count).g_tax_deduct);
535    hr_utility.trace('Tax column: '||tbl_paye_table(loop_count).g_tax_column);
537 
538 
539 
540    if p_tax_basis = 'C'
541    then
542 
543       l_td_net_low := (tbl_paye_table(loop_count).g_net_low_value * p_current_period) / p_stat_annual_periods;
544       l_td_net_high := (tbl_paye_table(loop_count).g_net_high_value * p_current_period) / p_stat_annual_periods;
545       l_td_tax_col := (tbl_paye_table(loop_count).g_tax_column * p_current_period) / p_stat_annual_periods;
546 
547    else
548 
549       l_td_net_low := tbl_paye_table(loop_count).g_net_low_value / p_stat_annual_periods;
550       l_td_net_high := tbl_paye_table(loop_count).g_net_high_value / p_stat_annual_periods;
551       l_td_tax_col := tbl_paye_table(loop_count).g_tax_column / p_stat_annual_periods;
552 
553    end if;
554 
555 
556 
557    if (l_taxable_pay <= ceil(l_td_net_high)
558       AND l_taxable_pay >= ceil(l_td_net_low))
559    then
560 
561 	hr_utility.trace('Band/loop count: '||loop_count);
562 	hr_utility.trace('TD NL: '||l_td_net_low);
563         hr_utility.trace('TD NH: '||l_td_net_high);
564         hr_utility.trace('TD TC: '||l_td_tax_col);
565 
566         l_tax_liable := l_td_tax_col +
567 		    (((l_taxable_pay - l_td_net_low) * (tbl_paye_table(loop_count).g_rate/100))
568                      * (100/tbl_paye_table(loop_count).g_gross_denom));
569 
570 
571    end if;
572 
573 
574 
575 end loop;
576 
577 
578 
579 end if;
580 
581 
582 /* Round down */
583 
584 l_tax_liable := round(l_tax_liable,4);
585 
586 l_tax_liable := trunc(l_tax_liable,2);
587 
588 hr_utility.trace('Tax liability: '||l_tax_liable);
589 
590 
591 if (l_scots_code)
592 then
593 
594   hr_utility.trace('Scots code : TRUE');
595 
596 else
597 
598   hr_utility.trace('Scots code : FALSE');
599 
600 end if;
601 
602 
603 hr_utility.trace('********************');
604 hr_utility.trace('OUTPUT values:      ');
605 hr_utility.trace('Tax liability to date: '||l_tax_liable);
606 hr_utility.trace('********************');
607 
608 hr_utility.trace('LEAVING: pay_gb_paye_calc.tax_to_date');
609 
610 --hr_utility.trace_off;
611 
612 return l_tax_liable;
613 
614 exception
615 
616    when others
617    then raise;
618 
619 end tax_to_date;
620 
621 
622 
623 --- Called from GB_TAX_REF_CHK formula.
624 --- Effective from 06-APR-2006.
625 
626 function check_tax_ref(p_assignment_id number, p_payroll_id number, p_pay_run_date date,p_payroll_action_id number) return number
627 is
628 
629 l_date_soy           date            ;
630 l_date_eoy           date            ;
631 l_effective_date     date            ;
632 l_assgt_creation_date  date ;
633 l_return number  ;
634 
635 CURSOR cur_assgt_first_eff_start_date(p_assignment_id number )
636 	IS
637 		select min(effective_start_date) effective_start_date
638 		from   per_all_assignments_f
639 		where  assignment_id = p_assignment_id
640 	;
641 
642 CURSOR cur_chk_pay_actions( p_payroll_action_id number, p_assignment_id number )
643 	IS
644 	    select   ppa.payroll_id old_payroll_id
645 		from   pay_payroll_actions ppa,
646 	      	       pay_assignment_actions paa
647 		where  ppa.payroll_action_id = paa.payroll_action_id
648 	        and    paa.assignment_id     = p_assignment_id
649 	        and    ppa.payroll_action_id <> p_payroll_action_id
650 		and    ppa.action_type       in  ('Q', 'R', 'B', 'I' , 'V')
651 		and    ppa.effective_date    >= l_date_soy
652 		and    ppa.effective_date    <= l_date_eoy ;
653 
654 
655 
656 CURSOR cur_check_payroll_tax_ref(p_old_payroll_id number, p_new_payroll_id number)
657 	IS
658 		select count(*)     l_exist           -- if this cursor fetches '1', that means new payroll is valid.
659 		from   pay_all_payrolls_f           pap
660 	      	      ,hr_soft_coding_keyflex       scl
661 		where  pap.payroll_id               = p_new_payroll_id
662 		and    pap.soft_coding_keyflex_id   = scl.soft_coding_keyflex_id
663 		and    scl.segment1                 in
664 	        (
665 	        	select distinct scl.segment1
666 			from   pay_all_payrolls_f           pap
667 		      	  ,hr_soft_coding_keyflex       scl
668 
669 			where
670 			      pap.payroll_id             = p_old_payroll_id
671 			and   pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
672             and   pap.effective_start_date   <= l_effective_date
673 			and   pap.effective_end_date     >= l_effective_date
674 		)
675 	;
676 
677 CURSOR cur_check_aggregated_asg(p_assignment_id number) is
678   select count (distinct nvl(per_information10,'N') ) l_count
679   from per_all_people_f papf , per_all_assignments_f paaf
680     where paaf.assignment_id=p_assignment_id
681     and papf.person_id=paaf.person_id
682     and papf.effective_start_date > l_date_soy
683     and papf.effective_start_date < l_date_eoy  ;
684 
688 CURSOR cur_get_tax_reference(c_payroll_id number) is
685 
686 
687     /* PAYE Details check for bug 6018309*/
689    select hsck.segment1
690   from pay_all_payrolls_f papf,
691        hr_soft_coding_keyflex hsck
692   where
693        papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
694        and papf.payroll_id=c_payroll_id;
695 
696 
697 CURSOR cur_chk_tax_code(c_tax_reference varchar2,
698                         c_assignment_id number,c_pay_run_date date) IS
699 select count(*)
700 from
701 (
702  select
703      distinct
704      ppev.INPUT_VALUE_ID1,
705      ppev.TAX_CODE,
706      ppev.INPUT_VALUE_ID2,
707      ppev.D_TAX_BASIS,
708      ppev.INPUT_VALUE_ID4,
709      ppev.D_PAY_PREVIOUS,
710      ppev.INPUT_VALUE_ID5,
711      ppev.D_TAX_PREVIOUS,
712      ppev.INPUT_VALUE_ID3,
713      ppev.D_REFUNDABLE,
714      ppev.INPUT_VALUE_ID6,
715      ppev.D_AUTHORITY,
716      ppev.entry_information1,
717      ppev.entry_information2
718 
719      from
720 (
721 SELECT ele.rowid ROW_ID, ele.element_entry_id, min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) INPUT_VALUE_ID1,
722 min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
723 min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) INPUT_VALUE_ID2,
724 min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)) D_Tax_Basis,
725 min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis, min(decode(inv.name, 'Refundable',
726 eev.input_value_id, null)) INPUT_VALUE_ID3,
727 min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)) D_Refundable,
728 min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
729 min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) INPUT_VALUE_ID4,
730 hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Pay_Previous,
731 min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) Pay_Previous,
732 min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) INPUT_VALUE_ID5,
733 hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Tax_Previous,
734 min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) Tax_Previous,
735 min(decode(inv.name, 'Authority', eev.input_value_id, null)) INPUT_VALUE_ID6,
736 min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)) D_AUTHORITY,
737 min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
738 ele.assignment_id,
739 ele.effective_start_date,
740 ele.effective_end_date,
741 ele.entry_information_category,
742 ele.entry_information1,
743 ele.entry_information2
744 from
745 pay_element_entries_f ele,
746 pay_element_entry_values_f eev,
747 pay_input_values_f inv,
748 pay_element_links_f lnk,
749 pay_element_types_f elt,
750 
751 pay_all_payrolls_f papf,
752 per_all_assignments_f paaf,
753 hr_soft_coding_keyflex hsck
754 
755 where  ele.element_entry_id = eev.element_entry_id
756 AND c_pay_run_date between ele.effective_start_date and ele.effective_end_date
757 AND eev.input_value_id + 0 = inv.input_value_id
758 AND c_pay_run_date between eev.effective_start_date and eev.effective_end_date
759 AND inv.element_type_id = elt.element_type_id
760 AND c_pay_run_date between inv.effective_start_date and inv.effective_end_date
761 AND ele.element_link_id = lnk.element_link_id
765 AND c_pay_run_date between elt.effective_start_date and elt.effective_end_date
762 AND elt.element_type_id = lnk.element_type_id
763 AND c_pay_run_date between lnk.effective_start_date and lnk.effective_end_date
764 AND elt.element_name = 'PAYE Details'
766 
767 AND c_pay_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
768 AND c_pay_run_date BETWEEN papf.effective_start_date AND papf.effective_end_date
769 
770 AND ele.assignment_id=paaf.assignment_id
771 
772 AND papf.payroll_id=paaf.payroll_id
773 AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
774 AND hsck.segment1=c_tax_reference
775 AND paaf.person_id = (select distinct pap.person_id
776                       from per_all_people_f pap,
777                            per_all_assignments_f paaf1
778                       where paaf1.person_id=pap.person_id
779                       and   paaf1.assignment_id=c_assignment_id)
780 AND pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_tax_reference,c_pay_run_date)
781   = pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_reference,c_pay_run_date)
782 AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_tax_reference,c_pay_run_date)
783   = pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_reference,c_pay_run_date)
784 /*Bug 7389532 - Added the distinct and null condition */
785 AND paaf.period_of_service_id = (select distinct period_of_service_id
786                                         from per_all_assignments_f paaf2
787 					where paaf2.assignment_id=c_assignment_id
788 					and paaf.person_id =paaf2.person_id
789 					and period_of_service_id is not null)
790 
791 group by ele.rowid, ele.element_entry_id, ele.assignment_id, ele.entry_information_category,
792 ele.entry_information1, ele.entry_information2, ele.effective_start_date, ele.effective_end_date
793 ) ppev
794 );
795 
796 
797 l_cur_chk_pay_actions	          cur_chk_pay_actions%ROWTYPE;
798 l_cur_check_payroll_tax_ref	  cur_check_payroll_tax_ref%ROWTYPE;
799 l_cur_check_aggregated_asg	  cur_check_aggregated_asg%ROWTYPE;
800 
801 l_tax_reference_code  varchar2(30);
802 l_count_c number default 0;
803 l_aggregated_asg   varchar2(10);
804 l_pay_run_date    date;
805 
806 begin
807 l_return:=0;
808 
809 
810 hr_utility.set_location('Enter CHECK_TAX_REF',10);
811 
812 
813 /*PAYE Details check for bug 6018309*/
814 
815 select regular_payment_date into l_pay_run_date
816  from per_time_periods ptp ,
817       pay_payroll_actions ppa
818  where ptp.time_period_id=ppa.time_period_id
819  and ppa.payroll_action_id=p_payroll_action_id;
820 
821 
822 open cur_get_tax_reference(p_payroll_id);
823 fetch cur_get_tax_reference into l_tax_reference_code;
824 close cur_get_tax_reference;
825 
826    select nvl(PER_INFORMATION10,'N') into l_aggregated_asg
827        from per_all_people_f
828        where person_id = (select distinct papf.person_id
829                           from per_all_people_f papf, per_all_assignments_f paaf1
830                           where  papf.person_id=paaf1.person_id
831 			   AND paaf1.assignment_id= p_assignment_id
832 			)
833        and l_pay_run_date  between effective_start_date and effective_end_date;
834 
835 if (l_aggregated_asg='Y') then
836 open cur_chk_tax_code(l_tax_reference_code,p_assignment_id, l_pay_run_date);
837 fetch cur_chk_tax_code into l_count_c;
838 close cur_chk_tax_code;
839 
840 if (l_count_c > 1) then
841  l_return:= 3;
842  return l_return;   /* Exit the function */
843 end if;
844 end if;
845 /* end of check for bug 6018309*/
846 
847 select effective_date into l_effective_date
848  from pay_payroll_actions ppa
849  where ppa.payroll_action_id=p_payroll_action_id;
850 
851 
852 hr_utility.set_location('effetive date:'||to_char(l_effective_date),12);
853 
854         If l_effective_date >=to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
855 		l_date_soy := to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
856 		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')  ;
857 	Else
858 		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')  ;
859 		l_date_eoy := to_date('05-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
860 	End If;
861 
862 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);
863 
864 open cur_assgt_first_eff_start_date(p_assignment_id);
865 fetch cur_assgt_first_eff_start_date into l_assgt_creation_date;
866 
867 hr_utility.set_location('Payroll_id'||to_char(p_payroll_id)||'Assignment_id:'||to_char(p_assignment_id),13);
868 
869 open cur_check_aggregated_asg(p_assignment_id);
870 fetch cur_check_aggregated_asg into l_cur_check_aggregated_asg;
871 
872  if(l_cur_check_aggregated_asg.l_count>1) then
873       l_return :=2;
874  end if;
875 close cur_check_aggregated_asg;
876 
877 for l_cur_chk_pay_actions in cur_chk_pay_actions(p_payroll_action_id,p_assignment_id)
878 loop
879   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);
880   exit when l_return<>0;
881 
882   for l_cur_check_payroll_tax_ref in cur_check_payroll_tax_ref(l_cur_chk_pay_actions.old_payroll_id , p_payroll_id)
883    loop
884 
885     if ( l_cur_check_payroll_tax_ref.l_exist =0) then
886      l_return :=1;
887      exit when l_return<>0;
888     end if ;
889 
890    end loop;
891 
892 end loop;
893 
894 
895 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);
896 
897 hr_utility.set_location('Exit CHECK_TAX_REF',15);
898 
899 close cur_assgt_first_eff_start_date;
900 
901 return l_return;
902 end check_tax_ref;
903 
904 
905 
906 -- Function tax_year_of_pensioners_death : Bug 4528372
907 -- To find out if the date of death of the pensioner is in the same tax year as
908 -- the date of the payment
909 -- called from PAYE formula for persioners VALIDATE_TAX_YEAR_OF_DEATH
910 
911 
912 function tax_year_of_pensioners_death(p_assignmnet_id IN number ,p_pay_run_date IN date)
913 return varchar2
914 is
915 
916  l_return number  ;
917  l_tax_year_start date ;
918  l_tax_year_end date ;
919  l_date_of_death date := to_date('31-12-4712','DD-MM-YYYY') ;
920 
921  l_pay_date  number;
922  l_pay_month number;
923  l_start_factor NUMBER;
924  l_end_factor NUMBER;
925 
926 
927 
928  cursor csr_date_of_death is
929   select PEOPLE.DATE_OF_DEATH
930   from   per_all_assignments_f           ASSIGN
931         ,per_all_people_f               PEOPLE
932         ,fnd_sessions                   SES
933   where   SES.effective_date BETWEEN ASSIGN.effective_start_date
934                             AND ASSIGN.effective_end_date
935   and 	  SES.effective_date BETWEEN PEOPLE.effective_start_date
936                             AND PEOPLE.effective_end_date
937   and     ASSIGN.assignment_id           = p_assignmnet_id
938   and     PEOPLE.person_id               = ASSIGN.person_id
939   and     PEOPLE.per_information4        ='Y'
940   and     PEOPLE.DATE_OF_DEATH is not null
941   and     SES.session_id                 = USERENV('sessionid') ;
942 
943 
944 BEGIN
945 
946   hr_utility.set_location('tax_year_of_pensioners_death',0);
947 
948   l_pay_date  :=  to_number( to_char( p_pay_run_date ,'DD' ) ) ;
949   l_pay_month :=  to_number( to_char( p_pay_run_date ,'MM' ) ) ;
950 
951   If l_pay_month >=4 and l_pay_date >=6 then
952    l_start_factor := 0;
953    l_end_factor   := 1;
954   end if;
955 
956   If ( l_pay_month >=4 and l_pay_date < 6 ) OR l_pay_month < 4 then
957    l_start_factor := 1;
958    l_end_factor   := 0;
959   end if;
960 
961   l_tax_year_start := to_date('06-04-' ||
962             to_char(to_number(to_char(p_pay_run_date,'YYYY' ) ) -l_start_factor ),'DD-MM-YYYY' );
963 
964   l_tax_year_end := to_date('05-04-' ||
965             to_char(to_number(to_char(p_pay_run_date,'YYYY' ) ) + l_end_factor ),'DD-MM-YYYY' );
966 
967   open csr_date_of_death;
968   fetch csr_date_of_death into l_date_of_death;
969   close csr_date_of_death;
970 
971   if l_date_of_death >= l_tax_year_start and l_date_of_death <= l_tax_year_end then
972 	l_return :=1;
973   else
974 	l_return :=0;
975   end if;
976 
977   hr_utility.set_location('tax_year_of_pensioners_death',99);
978   return(l_return);
979 
980 END tax_year_of_pensioners_death;
981 
982 
983 /* End of package body */
984 end pay_gb_paye_calc;