[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_TX
Source
1 PACKAGE BODY py_za_tx AS
2 /* $Header: pyzatax.pkb 120.2 2005/06/28 00:11:26 kapalani noship $ */
3
4 /* Function: tax_as_per_table calculates tax on a given amount according to the ZA Tax table */
5 FUNCTION calc_tax_on_table(
6 payroll_action_id NUMBER,
7 p_taxable_amount NUMBER,
8 p_tax_rebate NUMBER
9 ) RETURN NUMBER
10
11 AS
12
13 l_effective_date DATE;
14 l_user_table_id pay_user_tables.user_table_id%TYPE;
15 l_fixed_column_id pay_user_columns.user_column_id%TYPE;
16 l_limit_column_id pay_user_columns.user_column_id%TYPE;
17 l_percentage_column_id pay_user_columns.user_column_id%TYPE;
18 l_bracket_row pay_user_rows_f.user_row_id%TYPE;
19 l_fixed pay_user_column_instances_f.value%TYPE;
20 l_limit pay_user_column_instances_f.value%TYPE;
21 l_percentage pay_user_column_instances_f.value%TYPE;
22 tax_liability NUMBER;
23 l_id NUMBER;
24
25
26 BEGIN
27
28 /* Done for jion */
29 l_id := payroll_action_id;
30
31 /* this selects the effective date for the payroll_run*/
32 select ppa.effective_date
33 into l_effective_date
34 from pay_payroll_actions ppa
35 where ppa.payroll_action_id = l_id;
36
37 /* Selects to get the relevant id's */
38 select user_table_id
39 into l_user_table_id
40 from pay_user_tables
41 where user_table_name = 'ZA_TAX_TABLE';
42
43 select user_column_id
44 into l_fixed_column_id
45 from pay_user_columns
46 where user_table_id = l_user_table_id
47 and user_column_name = 'Fixed';
48
49 select user_column_id
50 into l_limit_column_id
51 from pay_user_columns
52 where user_table_id = l_user_table_id
53 and user_column_name = 'Limit';
54
55 select user_column_id
56 into l_percentage_column_id
57 from pay_user_columns
58 where user_table_id = l_user_table_id
59 and user_column_name = 'Percentage';
60
61 select purf.user_row_id
62 into l_bracket_row
63 from pay_user_rows_f purf
64 where purf.user_table_id = l_user_table_id
65 and (l_effective_date >= purf.effective_start_date
66 and l_effective_date <= purf.effective_end_date)
67 and (p_taxable_amount >= purf.row_low_range_or_name
68 and p_taxable_amount <= purf.row_high_range);
69
70 /* Selects to get the actual values */
71 select pucif.value
72 into l_fixed
73 from pay_user_column_instances_f pucif
74 where pucif.user_row_id = l_bracket_row
75 and (l_effective_date >= pucif.effective_start_date
76 and l_effective_date <= pucif.effective_end_date)
77 and pucif.user_column_id = l_fixed_column_id;
78
79 select pucif.value
80 into l_limit
81 from pay_user_column_instances_f pucif
82 where pucif.user_row_id = l_bracket_row
83 and (l_effective_date >= pucif.effective_start_date
84 and l_effective_date <= pucif.effective_end_date)
85 and pucif.user_column_id = l_limit_column_id;
86
87 select pucif.value
88 into l_percentage
89 from pay_user_column_instances_f pucif
90 where pucif.user_row_id = l_bracket_row
91 and (l_effective_date >= pucif.effective_start_date
92 and l_effective_date <= pucif.effective_end_date)
93 and pucif.user_column_id = l_percentage_column_id;
94
95
96 tax_liability := (l_fixed + ((p_taxable_amount - l_limit) * (l_percentage / 100))) - p_tax_rebate;
97
98 RETURN tax_liability;
99
100 END calc_tax_on_table;
101
102
103
104 /* Function: arr_pen_mon_check calculates the monthly arrear pension fund abatement
105 and, once a year, the arrear excess that should be taken over to the
106 next year.
107 */
108
109 FUNCTION arr_pen_mon_check(
110 p_tax_status IN VARCHAR2,
111 p_start_site IN VARCHAR2,
112 p_site_factor IN NUMBER,
113 p_pen_ind IN VARCHAR2,
114 p_apf_ptd_bal IN NUMBER,
115 p_apf_ytd_bal IN NUMBER,
116 p_apf_exces_bal IN NUMBER,
117 p_periods_left IN NUMBER,
118 p_period_factor IN NUMBER,
119 p_possible_periods_left IN NUMBER,
120 p_max_abate IN NUMBER,
121 p_exces_itd_upd OUT NOCOPY NUMBER
122 ) RETURN NUMBER
123
124 AS
125 l_contribution NUMBER;
126 l_abatement NUMBER;
127 l_contr_ptd_fact NUMBER;
128 l_apf_yearly_fig NUMBER;
129 l_exces_should_be NUMBER;
130
131 BEGIN
132 -- Assign default to p_exces_itd_upd
133 --
134 p_exces_itd_upd := 0;
135
136 IF p_tax_status = 'G'
137 THEN
138 l_contribution := (p_apf_ptd_bal * p_site_factor);
139
140 IF l_contribution > p_max_abate
141 THEN
142 l_abatement := p_max_abate;
143 ELSE
144 l_abatement := l_contribution;
145 END IF;
146
147 ELSE -- Tax status 'A' or 'B'
148
149 l_apf_yearly_fig := p_apf_ytd_bal + p_apf_exces_bal; -- YTD plus EXCESS ITD
150
151 IF p_start_site = 'N'
152 THEN
153 IF p_pen_ind = 'M' --Monthly Contribution
154 THEN
155 l_contr_ptd_fact := p_apf_ptd_bal / p_period_factor;
156
157 l_contribution := (((p_apf_ptd_bal / p_period_factor) * p_periods_left)
158 + (l_apf_yearly_fig - l_contr_ptd_fact)) * p_possible_periods_left;
159
160 IF l_contribution > p_max_abate
161 THEN
162 l_abatement := p_max_abate;
163 ELSE
164 l_abatement := l_contribution;
165 END IF;
166
167 ELSE -- i.e. Yearly Contribution
168 IF l_apf_yearly_fig > p_max_abate
169 THEN
170 l_abatement := p_max_abate;
171 ELSE
172 l_abatement := l_apf_yearly_fig;
173 END IF;
174
175 END IF;
176 ELSE -- End of year SITE calculation
177 IF p_pen_ind = 'M' --Monthly Contribution
178 THEN
179 l_contribution := (l_apf_yearly_fig * p_site_factor);
180
181 IF l_contribution > p_max_abate
182 THEN
183 l_abatement := p_max_abate;
184 ELSE
185 l_abatement := l_contribution;
186 END IF;
187
188 ELSE -- i.e. Yearly Contribution
189 IF l_apf_yearly_fig > p_max_abate
190 THEN
191 l_abatement := p_max_abate;
192 ELSE
193 l_abatement := l_apf_yearly_fig;
194 END IF;
195
196 END IF; -- end monthly or yearly
197
198 -- Excess calculation
199 --
200 l_exces_should_be := l_apf_yearly_fig - l_abatement;
201 p_exces_itd_upd := l_exces_should_be - p_apf_exces_bal; -- Return the Excess update figure.
202
203 END IF; -- end SITE or No SITE
204 END IF; -- end Tax status
205 RETURN l_abatement;
206 --
207 exception
208 when others then
209 p_exces_itd_upd := null;
210 END arr_pen_mon_check;
211
212 /* Function: arr_ra_mon_check calculates the monthly arrear retirement annuity abatement */
213
214 FUNCTION arr_ra_mon_check(
215 p_tax_status In VARCHAR2,
216 p_start_site VARCHAR2,
217 p_site_factor NUMBER,
218 p_ra_ind VARCHAR2,
219 p_ara_ptd_bal NUMBER,
220 p_ara_ytd_bal NUMBER,
221 p_ara_exces_bal NUMBER,
222 p_periods_left NUMBER,
223 p_period_factor NUMBER,
224 p_possible_periods_left NUMBER,
225 p_max_abate NUMBER,
226 p_exces_itd_upd OUT NOCOPY NUMBER
227 ) RETURN NUMBER
228
229 AS
230 l_contribution NUMBER;
231 l_abatement NUMBER;
232 l_contr_ptd_fact NUMBER;
233 l_ara_yearly_fig NUMBER;
234 l_exces_should_be NUMBER;
235
236 BEGIN
237
238 -- Assign default to p_exces_itd_upd
239 --
240 p_exces_itd_upd := 0;
241 --
242 IF p_tax_status = 'G'
243 THEN
244 l_contribution := (p_ara_ptd_bal * p_site_factor);
245
246 IF l_contribution > p_max_abate
247 THEN
248 l_abatement := p_max_abate;
249 ELSE
250 l_abatement := l_contribution;
251 END IF;
252
253 ELSE -- tax status A/B
254
255 l_ara_yearly_fig := p_ara_ytd_bal + p_ara_exces_bal; -- YTD plus EXCESS ITD
256
257 IF p_start_site = 'N'
258 THEN
259 IF p_ra_ind = 'M' --Monthly Contribution
260 THEN
261 l_contr_ptd_fact := p_ara_ptd_bal / p_period_factor;
262
263 l_contribution := (((p_ara_ptd_bal / p_period_factor) * p_periods_left)
264 + (l_ara_yearly_fig - l_contr_ptd_fact)) * p_possible_periods_left;
265
266 IF l_contribution > p_max_abate
267 THEN
268 l_abatement := p_max_abate;
269 ELSE
270 l_abatement := l_contribution;
271 END IF;
272
273 ELSE -- i.e. Yearly Contribution
274 IF l_ara_yearly_fig > p_max_abate
275 THEN
276 l_abatement := p_max_abate;
277 ELSE
278 l_abatement := l_ara_yearly_fig;
279 END IF;
280
281 END IF; -- end monthly or yearly
282
283 ELSE -- End of year SITE calculation
284 IF p_ra_ind = 'M' --Monthly Contribution
285 THEN
286 l_contribution := (l_ara_yearly_fig * p_site_factor);
287
288 IF l_contribution > p_max_abate
289 THEN
290 l_abatement := p_max_abate;
291 ELSE
292 l_abatement := l_contribution;
293 END IF;
294
295 ELSE -- i.e. Yearly Contribution
296 IF l_ara_yearly_fig > p_max_abate
297 THEN
298 l_abatement := p_max_abate;
299 ELSE
300 l_abatement := l_ara_yearly_fig;
301 END IF;
302
303 END IF; -- end monthly or yearly
304
305 -- Excess calculation
306 --
307 l_exces_should_be := l_ara_yearly_fig - l_abatement;
308 p_exces_itd_upd := l_exces_should_be - p_ara_exces_bal; -- Return the Excess update figure.
309
310 END IF; -- end SITE or No SITE
311 END IF; -- Tax Status
312 RETURN l_abatement;
313 --
314 exception
315 when others then
316 p_exces_itd_upd := null;
317 --
318 END arr_ra_mon_check;
319
320
321 /* Function: za_site_paye_split to calculate the split between site and paye */
322 FUNCTION site_paye_split(
323 p_total_tax IN NUMBER,
324 p_tax_on_travel IN NUMBER,
325 p_tax_on_pub_off IN NUMBER,
326 p_site_lim IN NUMBER,
327 p_qual IN VARCHAR2
328 ) RETURN NUMBER
329 AS
330 l_temp_site NUMBER;
331 l_site NUMBER;
332 l_paye NUMBER;
333 l_value NUMBER;
334
335 BEGIN
336 l_temp_site := p_total_tax - (p_tax_on_travel + p_tax_on_pub_off);
337
338 IF l_temp_site <= p_site_lim
339 THEN
340 l_site := l_temp_site;
341 ELSE
342 l_site := p_site_lim;
343 END IF;
344
345 l_paye := p_total_tax - l_site;
346
347 IF p_qual = 'S'
348 THEN
349 l_value := l_site;
350 ELSE
351 l_value := l_paye;
352 END IF;
353
354 RETURN l_value;
355
356 END site_paye_split;
357
358 /* Function: calc_tax_on_perc to calculate tax on a percentage according to tax status */
359 FUNCTION calc_tax_on_perc(
360 p_amount IN NUMBER,
361 p_tax_status IN VARCHAR2,
362 p_tax_directive_value IN NUMBER,
363 p_cc_tax_perc IN NUMBER,
364 p_temp_tax_perc IN NUMBER
365 ) RETURN NUMBER
366
367 AS
368 l_tax NUMBER;
369
370 BEGIN
371 IF p_tax_status = 'D'
372 THEN
373 l_tax := (p_amount * p_tax_directive_value) / 100;
374 ELSE
375 IF p_tax_status = 'E'
376 THEN
377 l_tax := (p_amount * p_cc_tax_perc) / 100;
378 ELSE
379 IF p_tax_status = 'F'
380 THEN
381 l_tax := (p_amount * p_temp_tax_perc) / 100;
382 ELSE
383 NULL;
384 END IF;
385 END IF;
386 END IF;
387
388 RETURN l_tax;
389 END calc_tax_on_perc;
390
391
392 /* Function: tax_period_factor calculates the period factor for the person,
393 i.e. did the person work a full period or a half or even one and a half.
394 */
395 FUNCTION tax_period_factor(
396 p_tax_year_start_date IN DATE,
397 p_asg_start_date IN DATE,
398 p_cur_period_start_date IN DATE,
399 p_cur_period_end_date IN DATE,
400 p_total_inc_ytd IN NUMBER,
401 p_total_inc_ptd IN NUMBER
402 ) RETURN NUMBER
403 AS
404 l_period_factor NUMBER;
405
406 BEGIN
407 IF p_tax_year_start_date < p_asg_start_date
408 THEN
409
410 IF p_total_inc_ytd = p_total_inc_ptd /* i.e. first pay for the person */
411 THEN
412 l_period_factor := (p_cur_period_end_date - p_asg_start_date + 1) /
413 (p_cur_period_end_date - p_cur_period_start_date + 1);
414 ELSE
415 l_period_factor := 1;
416 END IF;
417
418 ELSE
419 l_period_factor := 1;
420 END IF;
421 RETURN l_period_factor;
422 END tax_period_factor;
423
424
425
426 /* Function: pp_factor calculates the possible days the person could work in the year as a factor*/
427 FUNCTION tax_pp_factor(
428 p_tax_year_start_date IN DATE,
429 p_tax_year_end_date IN DATE,
430 p_asg_start_date IN DATE,
431 p_days_in_year IN NUMBER
432 ) RETURN NUMBER
433 AS
434 l_pp_factor NUMBER;
435
436 BEGIN
437 IF p_tax_year_start_date >= p_asg_start_date
438 THEN
439 l_pp_factor := 1;
440 ELSE
441 l_pp_factor := p_days_in_year / (p_tax_year_end_date - p_asg_start_date + 1);
442 END IF;
443 RETURN l_pp_factor;
444 END tax_pp_factor;
445
446
447 /* Function: annualise is used to annualise an amount */
448 FUNCTION annualise(
449 p_ytd_income IN NUMBER,
450 p_ptd_income IN NUMBER,
451 p_period_left IN NUMBER,
452 p_pp_factor IN NUMBER,
453 p_period_factor IN NUMBER
454 ) RETURN NUMBER
455 AS
456 l_annual_fig NUMBER;
457 l_ptd_fact NUMBER;
458
459 BEGIN
460 l_ptd_fact := p_ptd_income / p_period_factor;
461
462 IF p_period_factor < 1
463 THEN
464 l_annual_fig := ((l_ptd_fact * p_period_left) + (p_ytd_income - p_ptd_income)) * p_pp_factor;
465 ELSE
466 l_annual_fig := ((l_ptd_fact * p_period_left) + (p_ytd_income - l_ptd_fact)) * p_pp_factor;
467 END IF;
468 RETURN l_annual_fig;
469 END annualise;
470
471
472 /* Function to determine if employee has been terminated */
473 FUNCTION za_emp_terminated(
474 p_ee_date_to IN DATE,
475 p_cps_date IN DATE,
476 p_cpe_date IN DATE
477 ) RETURN VARCHAR2
478 AS
479 l_terminated VARCHAR2(5);
480
481 BEGIN
482 IF p_ee_date_to BETWEEN p_cps_date AND p_cpe_date
483 THEN
484 l_terminated := 'TRUE';
485 ELSE
486 l_terminated := 'FALSE';
487 END IF;
488
489 RETURN l_terminated;
490
491 END za_emp_terminated;
492
493
494 /* Function to determine if pay period is a site period or not */
495 FUNCTION za_site_period(
496 p_pay_periods_left IN NUMBER,
497 p_asg_date_to IN DATE,
498 p_current_period_start_date IN DATE,
499 p_current_period_end_date IN DATE
500 ) RETURN VARCHAR2
501 AS
502 l_do_SITE VARCHAR2(5);
503 l_ee_status VARCHAR2(5);
504
505 BEGIN
506 l_ee_status :=
507 py_za_tx.za_emp_terminated(
508 p_asg_date_to,
509 p_current_period_start_date,
510 p_current_period_end_date
511 );
512
513 IF p_pay_periods_left > 1 AND
514 l_ee_status = 'FALSE'
515 THEN
516 l_do_SITE := 'FALSE';
517 ELSE
518 l_do_SITE := 'TRUE';
519 END IF;
520
524
521 RETURN l_do_SITE;
522
523 END za_site_period;
525
526 /* Function to determine number of days worked in a year, including weekends and holidays*/
527 FUNCTION za_days_worked(
528 p_asg_date_from IN DATE,
529 p_asg_date_to IN DATE,
530 p_za_tax_year_from IN DATE,
531 p_za_tax_year_to IN DATE
532 ) RETURN NUMBER
533 AS
534 l_year_start_date date;
535 l_year_end_date date;
536 l_ee_days_worked number;
537
538 BEGIN
539 IF p_asg_date_from > p_za_tax_year_from
540 THEN
541 l_year_start_date := p_asg_date_from;
542 ELSE
543 l_year_start_date := p_za_tax_year_from;
544 END IF;
545
546 IF p_asg_date_to < p_za_tax_year_to
547 THEN
548 l_year_end_date := p_asg_date_to;
549 ELSE
550 l_year_end_date := p_za_tax_year_to;
551 END IF;
552
553 l_ee_days_worked := fnd_number.canonical_to_number((l_year_end_date + 1) - l_year_start_date);
554
555 RETURN l_ee_days_worked;
556
557 END za_days_worked;
558
559
560 /* Function ytd_days_worked calculates the number of days worked up to the present date */
561 FUNCTION ytd_days_worked(
562 p_tax_year_start IN DATE,
563 p_asg_date_from IN DATE,
564 p_cur_period_start IN DATE
565 ) RETURN NUMBER
566
567 AS
568 l_days_wk NUMBER;
569
570 BEGIN
571 IF p_asg_date_from > p_tax_year_start
572 THEN
573 l_days_wk := p_cur_period_start - p_asg_date_from;
574 ELSE
575 l_days_wk := p_cur_period_start - p_tax_year_start;
576 END IF;
577 RETURN l_days_wk;
578 END ytd_days_worked;
579
580
581 /* Function cal_days_worked calculates the number of days worked from 01 JAN to tax year start*/
582 FUNCTION cal_days_worked(
583 p_tax_year_start IN DATE,
584 p_asg_date_from IN DATE
585 ) RETURN NUMBER
586
587 AS
588 l_cal_y_start_date DATE;
589 l_days_wk NUMBER;
590
591 BEGIN
592 l_cal_y_start_date := fnd_date.canonical_to_date('01-JAN-'||to_char(sysdate, 'YYYY'));
593
594 IF p_asg_date_from > l_cal_y_start_date
595 THEN
596 l_days_wk := p_tax_year_start - p_asg_date_from;
597 ELSE
598 l_days_wk := p_tax_year_start - l_cal_y_start_date;
599 END IF;
600 RETURN l_days_wk;
601 END cal_days_worked;
602
603
604 /* Function get_ytd_car_allow_val calculates the travelling allowance ytd balance */
605 FUNCTION get_ytd_car_allow_val (
606 assignment_id NUMBER, -- context passed from application
607 p_tax_year_start_date DATE,
608 p_tax_year_end_date DATE,
609 p_current_period_end_date DATE, --end date for this payroll run
610 p_global_value VARCHAR2 -- current effective value
611
612
613 ) RETURN NUMBER
614 AS
615
616
617 -- Declare cursor statement
618 --
619 CURSOR c_get_eff_date (
620 p_ty_sd DATE, -- tax year start date
621 p_ty_ed DATE, -- tax year end date
622 p_cur_per_ed DATE -- current period end date
623 )
624 IS
625 SELECT effective_end_date,
626 global_value
627 FROM ff_globals_f
628 WHERE effective_end_date < p_ty_ed
629 AND effective_end_date > p_ty_sd
630 AND effective_end_date < p_cur_per_ed
631 AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
632
633 -- Declare variables statements
634 --
635 ytd_bal_val NUMBER := 0;
636 l_dim_id NUMBER := 0;
637 differ_bal_val NUMBER := 0;
638 bal_tot_val NUMBER := 0;
639 taxable_bal_val NUMBER := 0;
640 taxable_bal_val_tot NUMBER := 0;
641
642 l_effective_end_date DATE;
643 l_global_value NUMBER;
644
645 l_asg_start_date DATE;
646 l_asg_end_date DATE;
647
648 l_min_start_date DATE;
649 l_max_end_date DATE;
650
651 cursor c1 (c_assignment_id NUMBER) is
652 SELECT per.effective_start_date, per.effective_end_date
653 FROM per_assignments_f per,
654 fnd_sessions fnd
655 WHERE per.assignment_id = c_assignment_id
656 AND fnd.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
657 AND fnd.session_id = USERENV('sessionid');
658
659 BEGIN
660
661 OPEN c1(assignment_id);
662 LOOP
663 FETCH c1 INTO l_asg_start_date, l_asg_end_date;
664 EXIT WHEN c1%NOTFOUND;
665 -- -- dbms_output.put_line('l_asg_end_date = '||to_char(l_asg_end_date)); -- GSCC Error: File.Sql.18
666 -- -- dbms_output.put_line('l_asg_start_date = '||to_char(l_asg_start_date)); -- GSCC Error: File.Sql.18
667 END LOOP;
668
669 l_min_start_date := GREATEST(l_asg_start_date, p_tax_year_start_date);
670 l_max_end_date := LEAST(l_asg_end_date, p_tax_year_end_date);
671
672 -- -- dbms_output.put_line('l_asg_end_date = '||to_char(l_asg_end_date)); -- GSCC Error: File.Sql.18
673 -- -- dbms_output.put_line('l_asg_end_date = '||to_char(l_asg_end_date)); -- GSCC Error: File.Sql.18
674
678
675 pay_balance_pkg.set_context ('ASSIGNMENT_ID', to_char(assignment_id));
676
677 l_dim_id := pay_za_payroll_action_pkg.defined_balance_id('Travelling Allowance','_ASG_TAX_YTD');
679
680
681 FOR v_date IN c_get_eff_date(
682 l_min_start_date,
683 l_max_end_date,
684 p_current_period_end_date
685 )
686
687 LOOP
688
689 ytd_bal_val := pay_balance_pkg.get_value(l_dim_id,assignment_id,v_date.effective_end_date);
690 differ_bal_val := ytd_bal_val - bal_tot_val;
691
692 bal_tot_val := ytd_bal_val;
693
694 taxable_bal_val := differ_bal_val * (fnd_number.canonical_to_number(v_date.global_value) / 100);
695
696 taxable_bal_val_tot := taxable_bal_val_tot + taxable_bal_val;
697
698 END LOOP;
699
700 ytd_bal_val := pay_balance_pkg.get_value(l_dim_id,assignment_id,l_max_end_date);
701 -- ytd_bal_val := pay_balance_pkg.get_value(l_dim_id,assignment_id,p_current_period_end_date);
702
703 differ_bal_val := ytd_bal_val - bal_tot_val;
704
705 taxable_bal_val := differ_bal_val * (fnd_number.canonical_to_number(p_global_value) / 100);
706
707 taxable_bal_val_tot := taxable_bal_val_tot + taxable_bal_val;
708
709
710 RETURN taxable_bal_val_tot;
711
712 END get_ytd_car_allow_val;
713
714
715 /* Function get_cal_car_allow_val calculates the asg_cal_ytd value for the
716 TRAVELLING_ALLOWANCE balance.*/
717 FUNCTION get_cal_car_allow_val (
718 assignment_id NUMBER, -- context passed from application
719 p_tax_year_start_date DATE
720 ) RETURN NUMBER
721 AS
722
723
724 -- Declare cursor statement
725 --
726 CURSOR c_get_eff_date (
727 p_ty_sd DATE, -- tax year start date
728 p_asg_cal_max DATE -- maximum of assignment start date and Calendar year start
729 )
730 IS
731 SELECT effective_end_date,
732 global_value
733 FROM ff_globals_f
734 WHERE effective_end_date > p_asg_cal_max
735 AND effective_end_date < (p_ty_sd - 1)
736 AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
737
738
739 ytd_bal_val NUMBER;
740 l_dim_id NUMBER;
741 differ_bal_val NUMBER;
742 bal_tot_val NUMBER;
743 taxable_bal_val NUMBER;
744 taxable_bal_val_tot NUMBER;
745 l_effec_date DATE;
746 l_value_at_year_st NUMBER;
747
748 l_asg_start_date DATE;
749
750 l_min_start_date DATE;
751
752 cursor c1 (c_assignment_id NUMBER) is
753 SELECT per.effective_start_date
754 FROM per_assignments_f per,
755 fnd_sessions fnd
756 WHERE per.assignment_id = c_assignment_id
757 AND fnd.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
758 AND fnd.session_id = USERENV('sessionid');
759
760 l_assignment_id number;
761 l_asg_cal_max DATE;
762
763 BEGIN
764
765 l_assignment_id := assignment_id;
766
767 OPEN c1(assignment_id);
768 LOOP
769 FETCH c1 INTO l_asg_start_date;
770 EXIT WHEN c1%NOTFOUND;
771 END LOOP;
772
773
774 l_min_start_date := GREATEST(l_asg_start_date, p_tax_year_start_date);
775
776 pay_balance_pkg.set_context ('ASSIGNMENT_ID', to_char(assignment_id));
777
778 l_dim_id := pay_za_payroll_action_pkg.defined_balance_id('Travelling Allowance','_ASG_CAL_YTD');
779
780 -- Check that the newest of the Calendar Year Start Date and the Assignment Start Date
781 -- is used to get Effective End Dates for the ZA_CAR_ALLOW_TAX_PERC global in the
782 -- following loop
783 --
784 IF to_date('01-01-'||to_char(p_tax_year_start_date,'YYYY')||''||'','DD-MM-YYYY') > l_asg_start_date THEN
785 l_asg_cal_max := to_date('01-01-'||to_char(p_tax_year_start_date,'YYYY')||''||'','DD-MM-YYYY');
786 ELSE
787 l_asg_cal_max := l_asg_start_date;
788 END IF;
789
790 FOR v_date IN c_get_eff_date(
791 l_min_start_date,
792 l_asg_cal_max
793 )
794
795 LOOP -- for every record that is returned. Total the actual taxable value
796
797 ytd_bal_val := pay_balance_pkg.get_value(l_dim_id,assignment_id,v_date.effective_end_date);
798
799 differ_bal_val := ytd_bal_val - bal_tot_val;
800
801 bal_tot_val := ytd_bal_val;
802
803 taxable_bal_val := differ_bal_val * (v_date.global_value / 100);
804
805 taxable_bal_val_tot := taxable_bal_val_tot + taxable_bal_val;
806
807 END LOOP;
808
809 -- Do one last run for the exact date you want the value on
810 --
811
812 IF l_min_start_date >= p_tax_year_start_date THEN
813 l_effec_date := l_min_start_date;
814 ELSE
815 l_effec_date := (p_tax_year_start_date - 1);
816 END IF;
817
818
819 SELECT global_value
820 INTO l_value_at_year_st
821 FROM ff_globals_f glb
822 WHERE l_effec_date > glb.effective_start_date
823 AND l_effec_date < glb.effective_end_date
824 AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
825
826
827 ytd_bal_val := pay_balance_pkg.get_value(l_dim_id,assignment_id,l_effec_date);
828
829 differ_bal_val := ytd_bal_val - bal_tot_val;
830
831 taxable_bal_val := differ_bal_val * (l_value_at_year_st / 100);
832
833 taxable_bal_val_tot := taxable_bal_val_tot + taxable_bal_val;
834
835
836 RETURN taxable_bal_val_tot;
837
838
839
840 END get_cal_car_allow_val;
841
842 END py_za_tx;
843
844