[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA20100101_PKG
Source
1 package body pay_kr_yea20100101_pkg as
2 /* $Header: pykryea9.pkb 120.18 2011/08/29 05:53:49 keyazawa noship $ */
3 -- Constant
4 -- Bug 3966549
5 c_20040101 constant date := fnd_date.canonical_to_date('2004/01/01') ;
6 -- End of 3966549
7 -- Bug 9231094
8 c_20100101 constant date := fnd_date.canonical_to_date('2010/01/01') ;
9 c_20130101 constant date := fnd_date.canonical_to_date('2013/01/01') ;
10 c_20120101 constant date := fnd_date.canonical_to_date('2012/01/01') ;
11
12 -- Bug 9393732
13 l_pledt_tax_exem number default 0;
14 l_tlaw_tax_exem number default 0;
15 l_stat_tax_exem number default 0;
16 l_spec_tax_exem number default 0;
17 l_rel_tax_exem number default 0;
18 l_max_donation3_dummy number default 0;
19 l_max_donation2_dummy number default 0;
20 l_max_donation1_dummy number default 0;
21 l_political2_exem number default 0;
22 l_spec_amt number default 0;
23 l_spec_year number default 0;
24 l_rel_year number default 0;
25 l_actual_max_donation_dummy number default 0;
26 l_dummy_taxable_income number default 0;
27 label number default 0;
28 l_row_count number default 0;
29 g_debug constant boolean := hr_utility.debug_enabled;
30 -- End of Bug 9393732
31 --
32 l_credit_card_exp number default 0; -- Bug 11665554
33 -- Bug 3901912
34 l_ee_tax_exem number default 0;
35 l_dpnt_spouse_tax_exem number default 0;
36 l_dpnt_tax_exem number default 0;
37 l_aged_tax_exem number default 0;
38 -- Bug 3172960
39 l_aged_tax_exem1 number default 0;
40 l_fw_fixed_tax_rate number default 0;
41 l_fw_income_exem_rate number default 0;
42 --
43 l_disabled_tax_exem number default 0;
44 l_female_ee_tax_exem number default 0;
45 l_addl_child_tax_exem number default 0; -- Bug 5734313
46 l_addl_child_tax_exem1 number default 0; -- Bug 5734313
47 l_raising_child_tax_exem number default 0; -- Bug 5756690
48 l_new_born_adopted_tax_exem number default 0; -- Bug 6705170
49 l_pers_ins_prem_tax_exem number default 0;
50 l_disabled_ins_prem_tax_exem number default 0;
51 l_med_exp_tax_exem_per number default 0;
52 l_med_exp_tax_exem_lim number default 0;
53 -- Bug 3966549
54 l_total_med_exp number default 0 ;
55 -- End of 3966549
56 l_dpnt_educ_school_type_U number default 0;
57 l_dpnt_educ_school_type_H number default 0;
58 l_dpnt_educ_school_type_P number default 0;
59 l_dpnt_educ_school_type_D number default 0;
60 l_housinexp_tax_exem_per number default 0;
61 l_housinsavintype_HST1 number default 0;
62 l_housinsavintype_HST3 number default 0;
63 l_housinsavintype_HST4 number default 0;
64 l_housinexp_tax_exem_lim number default 0;
65 l_housinexp_tax_exem_lim1 number default 0;
66 l_housinexp_tax_exem_lim2 number default 0;
67 l_housinexp_tax_exem_lim3 number default 0; -- Bug 8237227
68 l_political_donation1_lim number default 0;
69 l_political_donation2_lim number default 0;
70 l_political_donation3_lim number default 0;
71 l_political_donation3_per number default 0;
72 l_donation2_tax_exem_per number default 0;
73 l_donation3_tax_exem_per number default 0;
74 l_rel_don_tax_exem_per1 number default 0; -- Bug 7142612
75 l_rel_don_tax_exem_per2 number default 0; -- Bug 7142612
76 -- Bug 3966549
77 l_political_tax_brk_lim2004 number default 0 ;
78 l_esoa_tax_exem_per2004 number default 0 ;
79 -- End of 3966549
80 l_std_sp_tax_exem number default 0;
81 l_pers_pen_prem_tax_exem_per number default 0;
82 l_pers_pen_prem_tax_exem_lim number default 0;
83 l_pers_pen_savintax_exem_lim number default 0;
84 l_corp_pen_prem_tax_exem_lim number default 0; -- Bug : 4776711
85 l_emp_stock_own_plan_exem_lim number default 0;
86 l_cre_card_exp_tax_exem_per1 number default 0;
87 l_cre_card_exp_tax_exem_per2 number default 0;
88 l_dir_card_exp_tax_exem_per number default 0;
89 l_cre_card_exp_tax_exem_lim number default 0;
90 -- Bug 3966549
91 l_cre_card_tax_exem_per2004 number default 0 ;
92 -- End of 3966549
93 l_dir_pay_tax_exem_per2010 number default 0 ; -- Bug 9231094
94 l_inv_part_fin1_tax_exem_per number default 0;
95 l_inv_part_fin2_tax_exem_per number default 0;
96 l_inv_part_fin3_tax_exem_per number default 0; -- Bug 8237227
97 l_inv_part_fin1_tax_exem_lim number default 0;
98 l_inv_part_fin2_tax_exem_lim number default 0;
99 l_inv_part_fin3_tax_exem_lim number default 0; -- Bug 8237227
100 l_small_bus_install_lim number default 0; -- Bug 6895093
101 l_basic_tax_break number default 0;
102 l_stock_savintax_break_per number default 0;
103 l_lt_stk_sav1_tax_break_per number default 0;
104 l_lt_stk_sav2_tax_break_per number default 0;
105 l_housinloan_int_repay_per number default 0;
106 l_annual_itax_per number default 0;
107 l_annual_stax_per number default 0;
108 l_housinexp_tax_break number default 0;
109 l_global_value_flag boolean default False;
110 l_cuml_special_exem number default 0 ; -- Adds up all special exemptions given for tracking purpose, for limit on exemptions.
111 -- Bug 4119539
112 l_fw_inc_exem_res number default 0 ;
113 l_fw_temp number default 0 ;
114 -- End of 4119539
115 -- Bug 5083240
116 l_fw_contr_taxable number default 0 ; -- Foreign Worker taxable during contract; numerator for proration factor
117 l_fw_contr_fw_inc_exem number default 0 ; -- Foreign Worker Income Exemption on income during contract for foreign worker
118 l_fw_contr_basic_exem number default 0 ; -- Basic Exemption on taxable income during contract for foreign worker
119 l_fw_brk_proration_factor number default 0 ; -- Proration Factor (Taxable During Contract / Total Taxable)
120 -- End of 5083240
121 -- Bug 5682450
122 l_pol_don_exem_rate_factor number default 1; -- This factor reflects the change in calculation of tax exemption based on political donation.
123 -- End of 5682450
124 l_card_exp_not_exempted number default 0 ; -- Bug 6630135
125 l_double_exem_amt number default 0 ; -- Bug 6630135
126 l_type_B_tax_group_pct number default 0 ; -- Bug 7361372
127 l_long_term_stck_fund_1yr_lim number default 0 ; -- Bug 7615517
128 l_long_term_stck_fund_2yr_lim number default 0 ; -- Bug 7615517
129 l_long_term_stck_fund_3yr_lim number default 0 ; -- Bug 7615517
130 l_long_term_stck_fund_tot_lim number default 0 ; -- Bug 9737699
131 l_long_term_stck_fund_1yr_pct number default 0 ; -- Bug 7615517
132 l_long_term_stck_fund_2yr_pct number default 0 ; -- Bug 7615517
133 l_long_term_stck_fund_3yr_pct number default 0 ; -- Bug 7615517
134 --
135 l_er_smb_exem_pct number default 0 ; -- Bug 9079450
136 l_er_smb_exem_limit number default 0 ; -- Bug 9079450
137 l_er_smb_prev_earn number default 0 ; -- Bug 9079450
138 l_er_smb_cur_earn number default 0 ; -- Bug 9079450
139 l_long_term_hou_pro_saving_lim number default 0 ; -- Bug 9231094
140 l_fw_tax_reduction_rate number default 0 ; -- Bug 9231094
141 l_sep_pen_details number default 0 ; -- Bug 9737699
142 l_housing_saving_exem number default 0 ; -- Bug 9737699
143 l_pen_prem_details number default 0 ; -- Bug 9737699
144 l_pen_saving_details number default 0 ; -- Bug 9737699
145 lt_stock_saving_year number default 0 ; -- Bug 9737699
146 l_housing_rent_exp_limit number default 0 ; -- Bug 9737699
147 l_housing_rent_exp_per number default 0 ; -- Bug 9737699
148 l_housing_rent_taxable_limit number default 0 ; -- Bug 9737699
149 lt_housing_pro_saving_limit number default 0 ; -- Bug 10184055
150 --
151 procedure yea(
152 p_assignment_id in number,
153 p_effective_date in date,
154 p_business_group_id in number,
155 p_yea_info in out NOCOPY pay_kr_yea_pkg.t_yea_info,
156 p_tax_adj_warning out NOCOPY boolean)
157 is
158 -- Bug No 3901912
159 cursor csr_ovs_def_bal_id
160 is
161 SELECT pdb.defined_balance_id
162 FROM pay_balance_types pbt,
163 pay_defined_balances pdb,
164 pay_balance_dimensions pbd
165 WHERE pbt.balance_name like 'Overseas Earnings'
166 and pbt.legislation_code = 'KR'
167 and pbd.legislation_code = 'KR'
168 and pdb.legislation_code = 'KR'
169 and pbt.balance_type_id = pdb.balance_type_id
170 and pbd.balance_dimension_id = pdb.balance_dimension_id
171 and pbd.database_item_suffix = '_ASG_YTD';
172 --
173 l_ovs_earnings_bal number:=0;
174 l_ovs_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
175
176 l_addend number;
177 l_multiplier number;
178 l_subtrahend number;
179 l_dummy number;
180 l_net_housing_exp_tax_break number;
181 l_calc_tax_for_stax number;
182 --
183 -- Bug No 3901912 moved variables to package initialisation section
184
185 l_available_tax_break number default 0;
186 l_ameeg_med_exem number default 0;
187
188 function calc_tax(p_taxation_base in number) return number
189 is
190 begin
191 if p_taxation_base > 0 then
192 l_multiplier := to_number(hruserdt.get_table_value(
193 p_bus_group_id => p_business_group_id,
194 p_table_name => 'CALC_TAX',
195 p_col_name => 'MULTIPLIER',
196 p_row_value => to_char(p_taxation_base),
197 p_effective_date => p_effective_date));
198 l_subtrahend := to_number(hruserdt.get_table_value(
199 p_bus_group_id => p_business_group_id,
200 p_table_name => 'CALC_TAX',
201 p_col_name => 'SUBTRAHEND',
202 p_row_value => to_char(p_taxation_base),
203 p_effective_date => p_effective_date));
204 return trunc(p_taxation_base * l_multiplier / 100) - l_subtrahend;
205 else
206 return 0;
207 end if;
208 end calc_tax;
209 --
210 function get_globalvalue(p_glbvar in varchar2,p_process_date in date) return number
211 is
212 --
213 cursor csr_ff_global
214 is
215 select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
216 from ff_globals_f glb
217 where glb.global_name = p_glbvar
218 and p_process_date between glb.effective_start_date and glb.effective_end_date;
219 --
220 l_glbvalue number default 0;
221 begin
222 Open csr_ff_global;
223 fetch csr_ff_global into l_glbvalue;
224 close csr_ff_global;
225 --
226 if l_glbvalue is null then
227 l_glbvalue := 0;
228 end if;
229 --
230 return l_glbvalue;
231 end;
232 --
233 ----------------------------------------------------------------------------
234 -- Bug 9393732
235 ----------------------------------------------------------------------------
236 procedure insert_donation_details(p_assignment_id in number,
237 p_effective_date in date,
238 p_asg_info_id in number,
239 p_exem_for_target_year in number,
240 p_carryover_amount in number
241 )
242 ----------------------------------------------------------------------------
243 is
244 --
245 l_ovn number := null;
246 l_ovn1 number := null;
247 l_asgInfoId number := null;
248 l_eff_date date := null;
249 l_donation_code varchar2(2) := null;
250 l_donation_year number := null;
251 l_donated_amt number := 0;
252 l_exem_balance number := 0;
253 l_amt_sub_exem number := 0;
254 l_obsolete_amt number := 0;
255 l_carryoveramt number := 0;
256 --
257 cursor get_donation_details is
258 select fnd_date.canonical_to_date(aei_information1) Effective_date,
259 aei_information8 Donation_Code,
260 to_number(aei_information2) Donated_Year,
261 to_number(aei_information9) Donated_Amount,
262 to_number(aei_information3) Exempted_Balance,
263 to_number(aei_information10) Amt_sub_to_exem,
264 object_version_number ovn
265 from per_assignment_extra_info
266 where assignment_id = p_assignment_id
267 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
268 and assignment_extra_info_id = p_asg_info_id
269 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
270 --
271 cursor check_records_exists(p_donated_year in varchar2,p_donation_code in varchar2) is
272 select assignment_extra_info_id, object_version_number
273 from per_assignment_extra_info
274 where assignment_id = p_assignment_id
275 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
276 and aei_information2 = p_donated_year
277 and aei_information8 = p_donation_code
278 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(add_months(p_effective_date,12), 'YYYY');
279 --
280 begin
281 --
282 if g_debug then
283 hr_utility.trace('*** Insert Donation Details ***');
284 end if;
285 --
286 open get_donation_details;
287 fetch get_donation_details into
288 l_eff_date,
289 l_donation_code,
290 l_donation_year,
291 l_donated_amt,
292 l_exem_balance,
293 l_amt_sub_exem,
294 l_ovn;
295 close get_donation_details;
296 --
297 if g_debug then
298 hr_utility.trace('l_eff_date = '||to_char(l_eff_date));
299 hr_utility.trace('l_donation_code = '||l_donation_code);
300 hr_utility.trace('l_donation_year = '||to_char(l_donation_year));
301 hr_utility.trace('l_donated_amt = '||to_char(l_donated_amt));
302 hr_utility.trace('l_exem_balance = '||to_char(l_exem_balance));
303 hr_utility.trace('l_amt_sub_exem = '||to_char(l_amt_sub_exem));
304 hr_utility.trace('l_ovn = '||to_char(l_ovn));
305 end if;
306 --
307 if l_donation_code = '31' then
308 if (l_donation_year + 3) > to_number(to_char(p_effective_date, 'YYYY')) then
309 l_carryoveramt := p_carryover_amount;
310 l_obsolete_amt := 0;
311 else
312 l_carryoveramt := 0;
313 l_obsolete_amt := p_carryover_amount;
314 end if;
315 elsif l_donation_code = '10' then
316 if (l_donation_year + 1) > to_number(to_char(p_effective_date, 'YYYY')) then
317 l_carryoveramt := p_carryover_amount;
318 l_obsolete_amt := 0;
319 else
320 l_carryoveramt := 0;
321 l_obsolete_amt := p_carryover_amount;
322 end if;
323 elsif l_donation_code = '30' then
324 if (l_donation_year + 2) > to_number(to_char(p_effective_date, 'YYYY')) then
325 l_carryoveramt := p_carryover_amount;
326 l_obsolete_amt := 0;
327 else
328 l_carryoveramt := 0;
329 l_obsolete_amt := p_carryover_amount;
330 end if;
331 elsif l_donation_code = '40' then
332 if (l_donation_year + 5) > to_number(to_char(p_effective_date, 'YYYY')) then
333 l_carryoveramt := p_carryover_amount;
334 l_obsolete_amt := 0;
335 else
336 l_carryoveramt := 0;
337 l_obsolete_amt := p_carryover_amount;
338 end if;
339 elsif l_donation_code = '41' then
340 if (l_donation_year + 5) > to_number(to_char(p_effective_date, 'YYYY')) then
341 l_carryoveramt := p_carryover_amount;
342 l_obsolete_amt := 0;
343 else
344 l_carryoveramt := 0;
345 l_obsolete_amt := p_carryover_amount;
346 end if;
347 end if;
348
349 -- Updating the records for the target year
350 if p_asg_info_id is not null then
351 --
352 if g_debug then
353 hr_utility.trace('Updating the records for the asg_info_id : '||to_char(p_asg_info_id));
354 end if;
355 --
356 hr_assignment_extra_info_api.update_assignment_extra_info(
357 p_validate => false,
358 p_assignment_extra_info_id => p_asg_info_id,
359 p_object_version_number => l_ovn,
360 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
361 p_aei_information1 => fnd_date.date_to_canonical(l_eff_date),
362 p_aei_information2 => l_donation_year,
363 p_aei_information3 => l_exem_balance,
364 p_aei_information4 => p_exem_for_target_year,
365 p_aei_information5 => l_carryoveramt,
366 p_aei_information6 => null,
367 p_aei_information7 => null,
368 p_aei_information8 => l_donation_code,
369 p_aei_information9 => l_donated_amt,
370 p_aei_information10 => l_amt_sub_exem,
371 p_aei_information11 => l_obsolete_amt,
372 p_aei_information12 => null,
373 p_aei_information13 => null,
374 p_aei_information14 => null,
375 p_aei_information15 => null,
376 p_aei_information16 => null,
377 p_aei_information17 => null,
378 p_aei_information18 => null,
379 p_aei_information19 => null,
380 p_aei_information20 => null,
381 p_aei_information21 => null,
382 p_aei_information22 => null,
383 p_aei_information23 => null,
384 p_aei_information24 => null,
385 p_aei_information25 => null,
386 p_aei_information26 => null,
387 p_aei_information27 => null,
388 p_aei_information28 => null,
389 p_aei_information29 => null,
390 p_aei_information30 => null
391 );
392 --
393 end if;
394 --
395 -- Creating a new row for the next year.
396 if l_carryoveramt > 0 then
397 --
398 if g_debug then
399 hr_utility.trace('Check if the records already exist for this donation type');
400 end if;
401 --
402 open check_records_exists(l_donation_year,l_donation_code);
403 fetch check_records_exists into l_asgInfoId,l_ovn1;
404 if check_records_exists%NOTFOUND then
405 --
406 if g_debug then
407 hr_utility.trace('Create record if already not present for this donation type');
408 end if;
409 --
410 if p_yea_info.create_don_rec_flag = 'Y' then
411 hr_assignment_extra_info_api.create_assignment_extra_info(
412 p_validate => false,
413 p_assignment_id => p_assignment_id,
414 p_information_type => 'KR_YEA_DONATION_TYPE_DETAIL',
415 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
416 p_aei_information1 => fnd_date.date_to_canonical(add_months(l_eff_date,12)),
417 p_aei_information2 => l_donation_year,
418 p_aei_information3 => (l_exem_balance + p_exem_for_target_year),
419 p_aei_information4 => '0',
420 p_aei_information5 => '0',
421 p_aei_information6 => null,
422 p_aei_information7 => null,
423 p_aei_information8 => l_donation_code,
424 p_aei_information9 => l_donated_amt,
425 p_aei_information10 => (l_donated_amt - (l_exem_balance + p_exem_for_target_year)),
426 p_aei_information11 => '0',
427 p_aei_information12 => null,
428 p_aei_information13 => null,
429 p_aei_information14 => null,
430 p_aei_information15 => null,
431 p_aei_information16 => null,
432 p_aei_information17 => null,
433 p_aei_information18 => null,
434 p_aei_information19 => null,
435 p_aei_information20 => null,
436 p_aei_information21 => null,
437 p_aei_information22 => null,
438 p_aei_information23 => null,
439 p_aei_information24 => null,
440 p_aei_information25 => null,
441 p_aei_information26 => null,
442 p_aei_information27 => null,
443 p_aei_information28 => null,
444 p_aei_information29 => null,
445 p_aei_information30 => null,
446 p_assignment_extra_info_id => l_asgInfoId,
447 p_object_version_number => l_ovn1
448 );
449 end if;
450 --
451 else
452 --
453 if g_debug then
454 hr_utility.trace('Update record if already present for this donation type');
455 end if;
456 --
457 hr_assignment_extra_info_api.update_assignment_extra_info(
458 p_validate => false,
459 p_assignment_extra_info_id => l_asgInfoId,
460 p_object_version_number => l_ovn1,
461 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
462 p_aei_information1 => fnd_date.date_to_canonical(add_months(l_eff_date,12)),
463 p_aei_information2 => l_donation_year,
464 p_aei_information3 => (l_exem_balance + p_exem_for_target_year),
465 p_aei_information4 => '0',
466 p_aei_information5 => '0',
467 p_aei_information6 => null,
468 p_aei_information7 => null,
469 p_aei_information8 => l_donation_code,
470 p_aei_information9 => l_donated_amt,
471 p_aei_information10 => (l_donated_amt - (l_exem_balance + p_exem_for_target_year)),
472 p_aei_information11 => '0',
473 p_aei_information12 => null,
474 p_aei_information13 => null,
475 p_aei_information14 => null,
476 p_aei_information15 => null,
477 p_aei_information16 => null,
478 p_aei_information17 => null,
479 p_aei_information18 => null,
480 p_aei_information19 => null,
481 p_aei_information20 => null,
482 p_aei_information21 => null,
483 p_aei_information22 => null,
484 p_aei_information23 => null,
485 p_aei_information24 => null,
486 p_aei_information25 => null,
487 p_aei_information26 => null,
488 p_aei_information27 => null,
489 p_aei_information28 => null,
490 p_aei_information29 => null,
491 p_aei_information30 => null
492 );
493 --
494 end if;
495 --
496 close check_records_exists;
497 --
498 end if;
499 --
500 end;
501 --
502 ----------------------------------------------------------------------------
503 -- Bug 9393732
504 ----------------------------------------------------------------------------
505 procedure update_donation_details(p_assignment_id in number,
506 p_effective_date in date,
507 p_donation_code in varchar2,
508 p_exem_for_target_year in number,
509 p_obsolete_amount in number
510 )
511 ----------------------------------------------------------------------------
512 is
513 --
514 l_ovn number := null;
515 l_asg_info_id number := null;
516 l_eff_date date := null;
517 l_donation_year number := null;
518 l_donated_amt number := 0;
519 l_exem_balance number := 0;
520 l_amt_sub_exem number := 0;
521 --
522 cursor get_donation_details is
523 select assignment_extra_info_id asg_info_id,
524 fnd_date.canonical_to_date(aei_information1) effective_date,
525 to_number(aei_information2) donated_year,
526 to_number(aei_information9) donated_amount,
527 to_number(aei_information3) exempted_balance,
528 to_number(aei_information10) amt_sub_to_exem,
529 object_version_number ovn
530 from per_assignment_extra_info
531 where assignment_id = p_assignment_id
532 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
533 and aei_information8 = p_donation_code
534 and aei_information2 = to_char(p_effective_date, 'YYYY')
535 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
536
537 begin
538 --
539 if g_debug then
540 hr_utility.trace('*** Update Donation Details ***');
541 end if;
542 --
543 open get_donation_details;
544 fetch get_donation_details into
545 l_asg_info_id,
546 l_eff_date,
547 l_donation_year,
548 l_donated_amt,
549 l_exem_balance,
550 l_amt_sub_exem,
551 l_ovn;
552 close get_donation_details;
553 --
554 if g_debug then
555 hr_utility.trace('l_eff_date = '||to_char(l_eff_date));
556 hr_utility.trace('l_asg_info_id = '||l_asg_info_id);
557 hr_utility.trace('l_donation_year = '||to_char(l_donation_year));
558 hr_utility.trace('l_donated_amt = '||to_char(l_donated_amt));
559 hr_utility.trace('l_exem_balance = '||to_char(l_exem_balance));
560 hr_utility.trace('l_amt_sub_exem = '||to_char(l_amt_sub_exem));
561 hr_utility.trace('l_ovn = '||to_char(l_ovn));
562 end if;
563 --
564 -- Updating the records for the target year
565 if l_asg_info_id is not null then
566 --
567 if g_debug then
568 hr_utility.trace('Updating the records for the asg_info_id : '||to_char(l_asg_info_id));
569 end if;
570 --
571 hr_assignment_extra_info_api.update_assignment_extra_info(
572 p_validate => false,
573 p_assignment_extra_info_id => l_asg_info_id,
574 p_object_version_number => l_ovn,
575 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
576 p_aei_information1 => fnd_date.date_to_canonical(l_eff_date),
577 p_aei_information2 => l_donation_year,
578 p_aei_information3 => l_exem_balance,
579 p_aei_information4 => p_exem_for_target_year,
580 p_aei_information5 => '0',
581 p_aei_information6 => null,
582 p_aei_information7 => null,
583 p_aei_information8 => p_donation_code,
584 p_aei_information9 => l_donated_amt,
585 p_aei_information10 => l_amt_sub_exem,
586 p_aei_information11 => p_obsolete_amount,
587 p_aei_information12 => null,
588 p_aei_information13 => null,
589 p_aei_information14 => null,
590 p_aei_information15 => null,
591 p_aei_information16 => null,
592 p_aei_information17 => null,
593 p_aei_information18 => null,
594 p_aei_information19 => null,
595 p_aei_information20 => null,
596 p_aei_information21 => null,
597 p_aei_information22 => null,
598 p_aei_information23 => null,
599 p_aei_information24 => null,
600 p_aei_information25 => null,
601 p_aei_information26 => null,
602 p_aei_information27 => null,
603 p_aei_information28 => null,
604 p_aei_information29 => null,
605 p_aei_information30 => null
606 );
607 --
608 end if;
609 --
610 end;
611 --
612 ----------------------------------------------------------------------------
613 procedure delete_future_details(p_assignment_id in number,
614 p_effective_date in date)
615 ----------------------------------------------------------------------------
616 is
617 --
618 cursor del_future_details is
619 select assignment_extra_info_id
620 from per_assignment_extra_info
621 where assignment_id = p_assignment_id
622 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
623 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(add_months(p_effective_date,12), 'YYYY')
624 and to_number(aei_information2) < to_number(to_char(add_months(p_effective_date,12),'YYYY'));
625 --
626 begin
627 for i in del_future_details loop
628 delete
629 from per_assignment_extra_info
630 where assignment_extra_info_id = i.assignment_extra_info_id;
631 end loop;
632 --
633 end;
634 ----------------------------------------------------------------------------
635 -- Bug 9393732
636 ----------------------------------------------------------------------------
637 procedure check_donation_details(p_assignment_id in number,
638 p_effective_date in date)
639 ----------------------------------------------------------------------------
640 is
641 --
642 cursor chk_donation_details is
643 select assignment_extra_info_id asg_info_id,
644 to_number(aei_information2) donated_year,
645 aei_information8 donation_code,
646 to_number(aei_information9) donated_amount,
647 to_number(aei_information4) exempted_amt,
648 to_number(aei_information5) carry_over_amt,
649 to_number(aei_information10) amt_sub_to_exem,
650 to_number(aei_information11) obsolete_amt
651 from per_assignment_extra_info
652 where assignment_id = p_assignment_id
653 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
654 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
655 --
656 begin
657 --
658 for i in chk_donation_details loop
659 if (nvl(i.exempted_amt,0) = 0 and
660 nvl(i.carry_over_amt,0) = 0 and
661 nvl(i.obsolete_amt,0) = 0 and
662 nvl(i.amt_sub_to_exem,0) > 0) then
663 if (i.donation_code = '20' or i.donation_code = '42') then
664 update_donation_details(p_assignment_id,
665 p_effective_date,
666 i.donation_code,
667 i.exempted_amt,
668 i.amt_sub_to_exem);
669 else
670 insert_donation_details(p_assignment_id,
671 p_effective_date,
672 i.asg_info_id,
673 i.exempted_amt,
674 i.amt_sub_to_exem);
675 end if;
676 end if;
677
678 end loop;
679 --
680 end;
681 --
682 -- Bug 9393732
683 --
684 function chk_valid_donation(p_donation_year in varchar2,
685 p_donation_code in varchar2,
686 p_effective_date in date) return varchar2
687 is
688 --
689
690 begin
691 --
692 if p_donation_code = '31' then
693 if (p_donation_year + 3) >= to_number(to_char(p_effective_date, 'YYYY')) then
694 return 'Y';
695 else
696 return 'N';
697 end if;
698 elsif p_donation_code = '10' then
699 if (p_donation_year + 1) >= to_number(to_char(p_effective_date, 'YYYY')) then
700 return 'Y';
701 else
702 return 'N';
703 end if;
704 elsif p_donation_code = '30' then
705 if (p_donation_year + 2) >= to_number(to_char(p_effective_date, 'YYYY')) then
706 return 'Y';
707 else
708 return 'N';
709 end if;
710 elsif p_donation_code = '40' then
711 if (p_donation_year + 5) >= to_number(to_char(p_effective_date, 'YYYY')) then
712 return 'Y';
713 else
714 return 'N';
715 end if;
716 elsif p_donation_code = '41' then
717 if (p_donation_year + 5) >= to_number(to_char(p_effective_date, 'YYYY')) then
718 return 'Y';
719 else
720 return 'N';
721 end if;
722 end if;
723 end;
724 begin
725 --
726 if g_debug then
727 hr_utility.trace('*** Inside Yea Package ***');
728 end if;
729 --
730 ----------------------------------------------------------------------------+
731 -- Populating Local variables with Global values
732 ----------------------------------------------------------------------------+
733 -- Bug no 3901912
734 if l_global_value_flag <> TRUE then
735 l_ee_tax_exem := get_globalvalue('KR_YEA_EE_TAX_EXEM',p_effective_date);
736 l_dpnt_spouse_tax_exem := get_globalvalue('KR_YEA_DPNT_SPOUSE_TAX_EXEM',p_effective_date);
737 l_dpnt_tax_exem := get_globalvalue('KR_YEA_DPNT_TAX_EXEM',p_effective_date);
738 l_aged_tax_exem := get_globalvalue('KR_YEA_AGED_TAX_EXEM',p_effective_date);
739 -- Bug 3172960
740 l_aged_tax_exem1 := get_globalvalue('KR_YEA_AGED_TAX_EXEM1',p_effective_date);
741 --
742 l_disabled_tax_exem := get_globalvalue('KR_YEA_DISABLED_TAX_EXEM',p_effective_date);
743 l_female_ee_tax_exem := get_globalvalue('KR_YEA_FEMALE_EE_TAX_EXEM',p_effective_date);
744 l_addl_child_tax_exem := get_globalvalue('CHILD_TAX_EXEM',p_effective_date); -- Bug 5734313
745 l_addl_child_tax_exem1 := get_globalvalue('CHILD_TAX_EXEM1',p_effective_date); -- Bug 5734313
746 l_raising_child_tax_exem := get_globalvalue('KR_YEA_NUM_OF_CHILDREN',p_effective_date); -- Bug 5756690
747 -- Bug 6705170
748 l_new_born_adopted_tax_exem := get_globalvalue('NEW_BORN_ADOPTED_CHILD_TAX_EXEM',p_effective_date);
749 --
750 l_pers_ins_prem_tax_exem := get_globalvalue('KR_YEA_PERS_INS_PREM_TAX_EXEM',p_effective_date);
751 l_disabled_ins_prem_tax_exem := get_globalvalue('KR_YEA_DISABLED_INS_PREM_TAX_EXEM',p_effective_date);
752 l_med_exp_tax_exem_per := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_PER',p_effective_date);
753 l_med_exp_tax_exem_lim := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_LIM',p_effective_date);
754 l_dpnt_educ_school_type_U := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_U',p_effective_date);
755 l_dpnt_educ_school_type_H := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_H',p_effective_date);
756 l_dpnt_educ_school_type_P := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_P',p_effective_date);
757 l_dpnt_educ_school_type_D := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_D',p_effective_date);
758 l_housinexp_tax_exem_per := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_PER',p_effective_date);
759 l_housinsavintype_HST1 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST1',p_effective_date);
760 -- Bug 5731165
761 -- Removed code for HST2
762 l_housinsavintype_HST3 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST3',p_effective_date);
763 l_housinsavintype_HST4 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST4',p_effective_date);
764 l_housinexp_tax_exem_lim := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM',p_effective_date);
765 l_housinexp_tax_exem_lim1 := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM1',p_effective_date);
766 l_housinexp_tax_exem_lim2 := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM2',p_effective_date);
767 l_housinexp_tax_exem_lim3 := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM3',p_effective_date); -- Bug 8237227
768 l_political_donation1_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION1_LIM',p_effective_date);
769 l_political_donation2_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION2_LIM',p_effective_date);
770 l_political_donation3_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION3_LIM',p_effective_date);
771 l_political_donation3_per := get_globalvalue('KR_YEA_POLITICAL_DONATION3_PER',p_effective_date);
772 l_donation2_tax_exem_per := get_globalvalue('KR_YEA_DONATION2_TAX_EXEM_PER',p_effective_date);
773 -- Bug 7142612
774 l_rel_don_tax_exem_per1 := get_globalvalue('KR_YEA_RELIGIOUS_DONATION_TAX_EXEM_PER1',p_effective_date);
775 l_rel_don_tax_exem_per2 := get_globalvalue('KR_YEA_RELIGIOUS_DONATION_TAX_EXEM_PER2',p_effective_date);
776 -- End of Bug 7142612
777 l_donation3_tax_exem_per := get_globalvalue('KR_YEA_DONATION3_TAX_EXEM_PER',p_effective_date);
778 -- Bug 3966549
779 l_political_tax_brk_lim2004 := get_globalvalue('KR_YEA_POLITICAL_DONATION_TAX_BREAK_LIM', p_effective_date) ;
780 l_esoa_tax_exem_per2004 := get_globalvalue('KR_YEA_ESOA_DONATION_TAX_EXEM_PER', p_effective_date) ;
781 -- End of 3966549
782 l_std_sp_tax_exem := get_globalvalue('KR_YEA_STD_SP_TAX_EXEM',p_effective_date);
783 l_pers_pen_prem_tax_exem_per := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_PER',p_effective_date);
784 l_pers_pen_prem_tax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_LIM',p_effective_date);
785 -- Bug : 4776711
786 l_corp_pen_prem_tax_exem_lim := get_globalvalue('KR_YEA_CORP_PENSION_PREM_TAX_EXEM_LIM',p_effective_date);
787 -- End of Bug 4776711
788 l_pers_pen_savintax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_SAVINTAX_EXEM_LIM',p_effective_date);
789 l_emp_stock_own_plan_exem_lim := get_globalvalue('KR_YEA_EMP_STOCK_OWN_PLAN_EXEM_LIM',p_effective_date);
790 l_cre_card_exp_tax_exem_per1 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER1',p_effective_date);
791 l_cre_card_exp_tax_exem_per2 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER2',p_effective_date);
792 l_dir_card_exp_tax_exem_per := get_globalvalue('KR_YEA_DIRECT_CARD_EXP_TAX_EXEM_PER',p_effective_date);
793 l_cre_card_exp_tax_exem_lim := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_LIM',p_effective_date);
794 -- Bug 3966549
795 l_cre_card_tax_exem_per2004 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_2004_TAX_EXEM_PER',p_effective_date);
796 -- End of 3966549
797 l_dir_pay_tax_exem_per2010 := get_globalvalue('KR_YEA_DIRECT_PAY_EXP_2010_TAX_EXEM_PER',p_effective_date); -- Bug 9231094
798 -- Bug 9079450: Removed code for investment partnership financing1
799 l_inv_part_fin2_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_PER',p_effective_date);
800 l_inv_part_fin3_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN3_TAX_EXEM_PER',p_effective_date); -- Bug 8237227
801 -- Bug 9079450: Removed code for investment partnership financing1
802 l_inv_part_fin2_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_LIM',p_effective_date);
803 l_inv_part_fin3_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN3_TAX_EXEM_LIM',p_effective_date); -- Bug 8237227
804 -- Bug 6895093
805 l_small_bus_install_lim := get_globalvalue('KR_YEA_SMALL_BUS_INSTALL_LIM',p_effective_date);
806 -- End of Bug 6895093
807 l_basic_tax_break := get_globalvalue('KR_YEA_BASIC_TAX_BREAK',p_effective_date);
808 l_stock_savintax_break_per := get_globalvalue('KR_YEA_STOCK_SAVINTAX_BREAK_PER',p_effective_date);
809 l_lt_stk_sav1_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING1_TAX_BREAK_PER',p_effective_date);
810 l_lt_stk_sav2_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING2_TAX_BREAK_PER',p_effective_date);
811 l_housinloan_int_repay_per := get_globalvalue('KR_YEA_HOUSINLOAN_INTEREST_REPAY_PER',p_effective_date);
812 l_annual_itax_per := get_globalvalue('KR_YEA_ANNUAL_ITAX_PER',p_effective_date);
813 l_annual_stax_per := get_globalvalue('KR_YEA_ANNUAL_STAX_PER',p_effective_date);
814 l_housinexp_tax_break := get_globalvalue('KR_YEA_HOUSINEXP_TAX_BREAK',p_effective_date);
815 -- Bug 3172960
816 l_fw_income_exem_rate := get_globalvalue('KR_FOREIGN_WORKER_INCOME_EXEM_RATE',p_effective_date);
817 l_fw_fixed_tax_rate := get_globalvalue('KR_FOREIGN_WORKER_FIXED_TAX_RATE',p_effective_date);
818 --
819 -- Bug 7361372
820 l_type_B_tax_group_pct := get_globalvalue('KR_YEA_TYPE_B_TAX_GROUP_PCT',p_effective_date);
821 --
822 l_housing_rent_exp_limit := get_globalvalue('KR_YEA_HOUSING_RENT_EXP_LIMIT',p_effective_date); -- Bug 9737699
823 l_housing_rent_exp_per := get_globalvalue('KR_YEA_HOUSING_RENT_EXP_PER',p_effective_date); -- Bug 9737699
824 l_housing_rent_taxable_limit := get_globalvalue('KR_YEA_HOU_RENT_TAXABLE_LIMIT',p_effective_date); -- Bug 9737699
825 -- Bug 7615517
826 l_long_term_stck_fund_1yr_lim := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_1YEAR_LIM',p_effective_date);
827 l_long_term_stck_fund_2yr_lim := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_2YEAR_LIM',p_effective_date);
828 l_long_term_stck_fund_3yr_lim := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_3YEAR_LIM',p_effective_date);
829 l_long_term_stck_fund_1yr_pct := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_1YEAR_PCT',p_effective_date);
830 l_long_term_stck_fund_2yr_pct := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_2YEAR_PCT',p_effective_date);
831 l_long_term_stck_fund_3yr_pct := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_3YEAR_PCT',p_effective_date);
832 l_long_term_stck_fund_tot_lim := get_globalvalue('KR_YEA_LONG_TERM_STCK_FUND_TOT_LIM',p_effective_date);
833 -- End of Bug 7615517
834 -- Bug 5682450
835 l_pol_don_exem_rate_factor := get_globalvalue ('KR_YEA_POLITICAL_DONATION_EXEM_FAC', p_effective_date);
836 -- End of 5682450
837 --
838 l_er_smb_exem_pct := get_globalvalue ('KR_YEA_ER_SMB_EXEM_PCT', p_effective_date); -- Bug 9079450
839 l_er_smb_exem_limit := get_globalvalue ('KR_YEA_ER_SMB_EXEM_LIMIT', p_effective_date); -- Bug 9079450
840 --
841 -- Bug 9231094
842 l_long_term_hou_pro_saving_lim := get_globalvalue ('KR_YEA_LONG_TERM_HOU_PRO_SAVING_LIM', p_effective_date);
843 lt_housing_pro_saving_limit := get_globalvalue ('KR_YEA_LT_HOU_PRO_SAV_LIMIT', p_effective_date);
844 --
845
846 l_global_value_flag := TRUE;
847 End if;
848 l_card_exp_not_exempted := 0 ; -- Bug 6763554
849 --
850 p_tax_adj_warning := false;
851 -- Bug 3172960
852 if (p_yea_info.nationality = 'F' and p_yea_info.fixed_tax_rate = 'Y' and p_effective_date < c_20130101) then
853 --
854 p_yea_info.taxable := p_yea_info.taxable + p_yea_info.non_taxable;
855 p_yea_info.taxable_income := p_yea_info.taxable;
856 p_yea_info.taxable_income2 := p_yea_info.taxable;
857 p_yea_info.taxation_base := p_yea_info.taxable;
858 --
859 p_yea_info.calc_tax := greatest(trunc((p_yea_info.taxation_base + p_yea_info.non_rep_non_taxable) * l_fw_fixed_tax_rate),0); -- Bug 9079450
860 --
861 p_yea_info.num_of_dpnts := 0;
862 p_yea_info.num_of_ageds := 0;
863 p_yea_info.num_of_super_ageds := 0;
864 p_yea_info.num_of_disableds := 0;
865 p_yea_info.num_of_children := 0;
866 --
867 /*
868 Bug 7615517
869 -- Bug 4149947: Add total non taxable into current monthly earnings, and respective totals
870 p_yea_info.cur_taxable_mth := p_yea_info.cur_taxable_mth + p_yea_info.non_taxable ;
871 p_yea_info.cur_taxable := p_yea_info.cur_taxable + p_yea_info.non_taxable ;
872 p_yea_info.taxable_mth := p_yea_info.taxable_mth + p_yea_info.non_taxable ;
873 End of Bug 7615517
874 */
875 -- End of 4149947
876 --
877 -- Bug 4156004
878 /* Bug 7142612: Displaying all Non Taxable Earnings components
879 p_yea_info.non_taxable_ovs := 0 ;
880 p_yea_info.non_taxable_ovt := 0 ;
881 p_yea_info.non_taxable_ovs_frgn := 0 ; -- Bug 7439803
882 p_yea_info.research_payment := 0 ; -- Bug 7439803
883 p_yea_info.birth_raising_allowance := 0 ; -- Bug 7439803
884 p_yea_info.foreign_worker_income_exem := 0 ; -- Bug 7439803
885 -- End of 4156004
886 --
887 -- Bug 4119539: Make all non-taxables = 0
888 p_yea_info.non_taxable_oth := 0 ;
889 p_yea_info.non_taxable := 0 ;
890 -- End of 4119539
891 */
892 --
893 -- Bug 5083240: Removed logic to calculate tax break, as it is
894 -- not applicable to fixed rate cases
895 ----------------------------------------------------------------
896 -- Annual Tax
897 ----------------------------------------------------------------
898 p_yea_info.annual_itax := greatest(p_yea_info.calc_tax, 0);
899 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per,0);
900 else
901 /*
902 -- Bug 9539550
903 -- Bug 3172960
904 if p_yea_info.nationality = 'F' then
905 -- Bug 6666723
906 -- Bug 9231094
907 p_yea_info.foreign_worker_income_exem := greatest(trunc(p_yea_info.taxable * l_fw_income_exem_rate),0);
908 p_yea_info.cur_fw_income_exem := p_yea_info.foreign_worker_income_exem; -- Bug 8644512
909 --
910 if p_yea_info.foreign_worker_income_exem > 0 then
911 p_yea_info.taxable := greatest(p_yea_info.taxable - p_yea_info.foreign_worker_income_exem, 0);
912 --
913 -- Bug 4119539
914 l_fw_inc_exem_res := p_yea_info.foreign_worker_income_exem ;
915 p_yea_info.non_taxable := p_yea_info.non_taxable + p_yea_info.foreign_worker_income_exem ;
916 p_yea_info.cur_total_ntax_earn := nvl(p_yea_info.cur_total_ntax_earn,0)
917 + p_yea_info.foreign_worker_income_exem ;
918 l_fw_inc_exem_res := p_yea_info.foreign_worker_income_exem ;
919 --
920
921 -- Bug 7615517
922 l_fw_temp := greatest(p_yea_info.cur_taxable_mth - l_fw_inc_exem_res, 0) ;
923 l_fw_inc_exem_res := greatest(l_fw_inc_exem_res - p_yea_info.cur_taxable_mth, 0) ;
924 p_yea_info.taxable_mth := greatest(p_yea_info.taxable_mth + l_fw_temp - p_yea_info.cur_taxable_mth, 0) ;
925 p_yea_info.cur_taxable_mth := l_fw_temp ;
926 --
927 l_fw_temp := greatest(p_yea_info.cur_taxable_bon - l_fw_inc_exem_res, 0) ;
928 l_fw_inc_exem_res := greatest(l_fw_inc_exem_res - p_yea_info.cur_taxable_bon, 0) ;
929 p_yea_info.taxable_bon := greatest(p_yea_info.taxable_bon + l_fw_temp - p_yea_info.cur_taxable_bon, 0) ;
930 p_yea_info.cur_taxable_bon := l_fw_temp ;
931 --
932 l_fw_temp := greatest(p_yea_info.cur_sp_irreg_bonus - l_fw_inc_exem_res, 0) ;
933 p_yea_info.sp_irreg_bonus := greatest(p_yea_info.sp_irreg_bonus + l_fw_temp - p_yea_info.cur_sp_irreg_bonus, 0) ;
934 p_yea_info.cur_sp_irreg_bonus := l_fw_temp ;
935
936 --
937 p_yea_info.cur_taxable := greatest(p_yea_info.cur_taxable - p_yea_info.foreign_worker_income_exem, 0) ;
938 --
939 -- End of 4119539
940 -- End of Bug 7615517
941
942 end if;
943 -- Bug 8341054 Added Previous employer foreign worker Exemption to the current employer
944 p_yea_info.foreign_worker_income_exem := nvl(p_yea_info.foreign_worker_income_exem,0)
945 + nvl(p_yea_info.prev_foreign_wrkr_inc_exem,0)
946 + nvl(p_yea_info.tax_grp_fw_income_exem,0);
947
948
949 end if;
950 */
951 ----------------------------------------------------------------
952 -- Basic Income Exemption
953 -- Taxable Income
954 ----------------------------------------------------------------
955 if p_yea_info.taxable > 0 then
956 l_addend := to_number(hruserdt.get_table_value(
957 p_bus_group_id => p_business_group_id,
958 p_table_name => 'BASIC_INCOME_EXEM',
959 p_col_name => 'ADDEND',
960 p_row_value => to_char(p_yea_info.taxable),
961 p_effective_date => p_effective_date));
962 l_multiplier := to_number(hruserdt.get_table_value(
963 p_bus_group_id => p_business_group_id,
964 p_table_name => 'BASIC_INCOME_EXEM',
965 p_col_name => 'MULTIPLIER',
966 p_row_value => to_char(p_yea_info.taxable),
967 p_effective_date => p_effective_date));
968 l_subtrahend := to_number(hruserdt.get_table_value(
969 p_bus_group_id => p_business_group_id,
970 p_table_name => 'BASIC_INCOME_EXEM',
971 p_col_name => 'SUBTRAHEND',
972 p_row_value => to_char(p_yea_info.taxable),
973 p_effective_date => p_effective_date));
974 p_yea_info.basic_income_exem := l_addend + trunc(l_multiplier / 100 * (p_yea_info.taxable - l_subtrahend));
975 p_yea_info.taxable_income := p_yea_info.taxable - p_yea_info.basic_income_exem;
976 end if;
977 p_yea_info.taxable_income2 := p_yea_info.taxable_income;
978 ----------------------------------------------------------------
979 -- Employee Tax Exemption
980 ----------------------------------------------------------------
981 p_yea_info.ee_tax_exem := l_ee_tax_exem;
982 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.ee_tax_exem;
983 ----------------------------------------------------------------
984 -- Dependent Tax Exemption
985 ----------------------------------------------------------------
986 if p_yea_info.dpnt_spouse_flag = 'Y' then
987 p_yea_info.dpnt_spouse_tax_exem := l_dpnt_spouse_tax_exem;
988 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_spouse_tax_exem;
989 end if;
990 if p_yea_info.num_of_dpnts > 0 then
991 p_yea_info.dpnt_tax_exem := p_yea_info.num_of_dpnts * l_dpnt_tax_exem;
992 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_tax_exem;
993 end if;
994 -- Bug 3172960
995 -- Bug 7676136
996 if p_yea_info.num_of_super_ageds > 0 then
997 p_yea_info.aged_tax_exem := p_yea_info.num_of_super_ageds * l_aged_tax_exem1;
998 end if;
999 p_yea_info.num_of_ageds := nvl(p_yea_info.num_of_super_ageds, 0);
1000 --
1001 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.aged_tax_exem;
1002 --
1003 if p_yea_info.num_of_disableds > 0 then
1004 p_yea_info.disabled_tax_exem := p_yea_info.num_of_disableds * l_disabled_tax_exem;
1005 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.disabled_tax_exem;
1006 end if;
1007 if p_yea_info.female_ee_flag = 'Y' then
1008 p_yea_info.female_ee_tax_exem := l_female_ee_tax_exem;
1009 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.female_ee_tax_exem;
1010 end if;
1011 -- Bug 5756690
1012 if p_yea_info.num_of_children > 0 then
1013 p_yea_info.child_tax_exem := p_yea_info.num_of_children * l_raising_child_tax_exem;
1014 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.child_tax_exem;
1015 end if;
1016 -- End of Bug 5756690
1017 --
1018 -- Bug 6705170: New Exemption For New Born Baby Or Adopted Child
1019 if p_yea_info.num_of_new_born_adopted > 0 then
1020 p_yea_info.new_born_adopted_tax_exem := p_yea_info.num_of_new_born_adopted * l_new_born_adopted_tax_exem;
1021 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.new_born_adopted_tax_exem;
1022 end if;
1023 --End of Bug 6705170
1024 --
1025 -- Bug 5734313
1026 -- Bug 6784288
1027 if p_yea_info.num_of_addtl_child > 2 then
1028 p_yea_info.addl_child_tax_exem := l_addl_child_tax_exem + ((p_yea_info.num_of_addtl_child - 2) * l_addl_child_tax_exem1);
1029 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.addl_child_tax_exem;
1030 elsif p_yea_info.num_of_addtl_child = 2 then
1031 p_yea_info.addl_child_tax_exem := l_addl_child_tax_exem;
1032 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.addl_child_tax_exem;
1033 end if;
1034 --
1035 ----------------------------------------------------------------
1036 -- Supplemental Tax Exemption
1037 ----------------------------------------------------------------
1038 /* Bug 5734313 : Obsoleted code for Supplemental Tax Exemption */
1039 ----------------------------------------------------------------
1040 -- Special Tax Exemption
1041 ----------------------------------------------------------------
1042 l_cuml_special_exem := 0; -- Bug 4709683
1043
1044 if p_yea_info.non_resident_flag = 'N' then
1045 --
1046 -- Insurance Premium Tax Exemption
1047 --
1048 if p_yea_info.hi_prem > 0 then
1049
1050 p_yea_info.hi_prem_tax_exem := least(p_yea_info.hi_prem,
1051 greatest(0,p_yea_info.taxable_income2 - l_cuml_special_exem)) ;
1052 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.hi_prem_tax_exem;
1053
1054 end if;
1055 -- Bug 7164589
1056 if p_yea_info.long_term_ins_prem > 0 then
1057 p_yea_info.long_term_ins_prem_tax_exem := least(p_yea_info.long_term_ins_prem,
1058 greatest(0,p_yea_info.taxable_income2 - l_cuml_special_exem)) ;
1059
1060 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.long_term_ins_prem_tax_exem;
1061 end if;
1062 -- End of Bug 7164589
1063 if p_yea_info.ei_prem > 0 then
1064 p_yea_info.ei_prem_tax_exem := least(p_yea_info.ei_prem,
1065 greatest(0,p_yea_info.taxable_income2 - l_cuml_special_exem)) ;
1066 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.ei_prem_tax_exem;
1067
1068 end if;
1069
1070 if p_yea_info.pers_ins_prem > 0 then
1071 p_yea_info.pers_ins_prem_tax_exem := least(p_yea_info.pers_ins_prem,
1072 greatest(0,p_yea_info.taxable_income2 - l_cuml_special_exem)) ;
1073 p_yea_info.pers_ins_prem_tax_exem := least(p_yea_info.pers_ins_prem_tax_exem, l_pers_ins_prem_tax_exem );
1074 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.pers_ins_prem_tax_exem;
1075 end if;
1076 if p_yea_info.disabled_ins_prem > 0 then
1077 p_yea_info.disabled_ins_prem_tax_exem := least(p_yea_info.disabled_ins_prem,
1078 greatest(0,p_yea_info.taxable_income2 - l_cuml_special_exem)) ;
1079 p_yea_info.disabled_ins_prem_tax_exem := least(p_yea_info.disabled_ins_prem_tax_exem, l_disabled_ins_prem_tax_exem);
1080 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.disabled_ins_prem_tax_exem;
1081 end if;
1082 p_yea_info.ins_prem_tax_exem := p_yea_info.hi_prem_tax_exem
1083 + p_yea_info.long_term_ins_prem_tax_exem -- Bug 7164589
1084 + p_yea_info.ei_prem_tax_exem
1085 + p_yea_info.pers_ins_prem_tax_exem
1086 + p_yea_info.disabled_ins_prem_tax_exem;
1087
1088 -- Bug 3971542
1089 -- l_cuml_special_exem := l_cuml_special_exem + p_yea_info.ins_prem_tax_exem ;
1090 -- End of 3971542
1091 --
1092
1093 ----------------------------------------------------------------
1094 -- Medical Expense Tax Exemption
1095 -- Bug 3172960
1096 -- Bug 3966549: Assigned new fields
1097 -- p_yea_info.reg_med_exp_tax_exem2004, and
1098 -- p_yea_info.add_med_exp_tax_exem2004.
1099 -- Changed calculation for reg. and additional
1100 -- medical exp. tax exemption.
1101 ----------------------------------------------------------------
1102 l_total_med_exp := p_yea_info.med_exp + p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged + p_yea_info.med_exp_emp;
1103 l_ameeg_med_exem := p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged + p_yea_info.med_exp_emp;
1104 --
1105 if l_total_med_exp > 0 then
1106 -- Calculate reg. med. expense exem.
1107 l_dummy := l_total_med_exp - trunc(greatest(p_yea_info.taxable, 0) * l_med_exp_tax_exem_per) ;
1108 if l_dummy > 0 then
1109 -- Calculate reg. med. exp. exem.
1110 p_yea_info.reg_med_exp_tax_exem2004 := least(l_dummy, l_med_exp_tax_exem_lim) ;
1111 -- To calculate additional med. exp. exem.
1112 l_dummy := l_dummy - l_med_exp_tax_exem_lim ;
1113 else
1114 p_yea_info.reg_med_exp_tax_exem2004 := 0 ;
1115 end if;
1116 if l_dummy < 0 then
1117 l_dummy := 0 ;
1118 end if ;
1119 -- Calculate additional med. exp. exem.
1120 p_yea_info.add_med_exp_tax_exem2004 := trunc( least(l_ameeg_med_exem, l_dummy) ) ;
1121 p_yea_info.med_exp_tax_exem := p_yea_info.reg_med_exp_tax_exem2004 + p_yea_info.add_med_exp_tax_exem2004 ;
1122 -- Bug 4119483: Apply upper limit based on cumulative special exemption
1123 p_yea_info.med_exp_tax_exem := least(
1124 p_yea_info.med_exp_tax_exem,
1125 greatest(
1126 0,
1127 p_yea_info.taxable_income2 - l_cuml_special_exem
1128 )
1129 ) ;
1130 -- End of 4119483
1131 -- Start of Bug 6630135
1132 l_card_exp_not_exempted :=0;
1133 if p_yea_info.tot_med_exp_cards > 0 then
1134 l_card_exp_not_exempted := l_total_med_exp - p_yea_info.med_exp_tax_exem;
1135 end if;
1136 -- End of Bug 6630135
1137
1138 -- Bug 3971542
1139 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.med_exp_tax_exem ;
1140 -- End of 3971542
1141 end if;
1142 -- End of 3966549
1143 --
1144 ----------------------------------------------------------------
1145 -- Education Expense Tax Exemption
1146 ----------------------------------------------------------------
1147 p_yea_info.educ_exp_tax_exem := p_yea_info.ee_educ_exp;
1148
1149 -- Bug 4119483: Removed limit check on occupational education expense as it is not considered/displayed
1150 -- individually anywhere.
1151 -- Also, p_yea_info.educ_exp_tax_exem may still change. Hence, updated value of educ_exp_tax_exem
1152 -- would be added to l_cuml_special_exem later.
1153 --
1154 p_yea_info.educ_exp_tax_exem := p_yea_info.educ_exp_tax_exem + p_yea_info.ee_occupation_educ_exp2005 ;
1155
1156 -- End of 3971542
1157 for i in 1..p_yea_info.dpnt_educ_contact_type_tbl.count loop
1158 if p_yea_info.dpnt_educ_school_type_tbl(i) = 'U' then
1159 l_dummy := l_dpnt_educ_school_type_U;
1160 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'H' then
1161 l_dummy := l_dpnt_educ_school_type_H;
1162 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'P' then
1163 l_dummy := l_dpnt_educ_school_type_P;
1164 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'D' then
1165 l_dummy := l_dpnt_educ_school_type_D ;
1166 p_yea_info.disabled_educ_exp := p_yea_info.disabled_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
1167 end if;
1168 p_yea_info.educ_exp_tax_exem := p_yea_info.educ_exp_tax_exem + least(p_yea_info.dpnt_educ_exp_tbl(i), l_dummy);
1169 if (p_yea_info.dpnt_educ_contact_type_tbl(i) = '3') then -- Bug 9079450
1170 p_yea_info.spouse_educ_exp := p_yea_info.spouse_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
1171 else
1172 p_yea_info.dpnt_educ_exp := p_yea_info.dpnt_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
1173 end if;
1174 end loop;
1175 -- Apply upper limit based on cumulative special exemption
1176 p_yea_info.educ_exp_tax_exem := least(
1177 p_yea_info.educ_exp_tax_exem,
1178 greatest(
1179 0,
1180 p_yea_info.taxable_income2 - l_cuml_special_exem
1181 )
1182 ) ;
1183 --
1184 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.educ_exp_tax_exem ;
1185 -- End of 4119483
1186 ----------------------------------------------------------------
1187 -- Housing Expense Tax Exemption
1188 ----------------------------------------------------------------
1189 --
1190 if g_debug then
1191 hr_utility.trace('*** Housing Expense Tax Exemption ***');
1192 end if;
1193 --
1194 l_dummy := 0;
1195 for i in 1..p_yea_info.hou_saving_type_tbl.count loop
1196 if p_yea_info.hou_saving_type_tbl(i) <> 'HST2' then
1197 l_dummy := l_dummy + nvl(p_yea_info.hou_saving_cont_amount_tbl(i),0);
1198 end if;
1199 end loop;
1200
1201 if p_yea_info.house_holder_flag = 'Y' then -- Bug 9737699
1202 if l_dummy > 0
1203 or p_yea_info.housing_loan_repay > 0
1204 or p_yea_info.housing_prv_loan_repay > 0
1205 or p_yea_info.lt_housing_loan_interest_repay > 0
1206 or p_yea_info.lt_housing_loan_intr_repay_1 > 0
1207 or p_yea_info.lt_housing_loan_intr_repay_2 > 0
1208 or p_yea_info.housing_rent_exp > 0 then
1209 for i in 1..p_yea_info.hou_saving_type_tbl.count loop
1210 if i = 1 then
1211 p_yea_info.housing_saving_type := p_yea_info.hou_saving_type_tbl(i);
1212 end if;
1213 /* Bug 9079478 */
1214 if p_yea_info.hou_saving_type_tbl(i) in ('HST1','HST5') then
1215 if p_yea_info.hou_saving_cont_amount_tbl(i) < l_housinsavintype_HST1 then
1216 l_dummy := p_yea_info.hou_saving_cont_amount_tbl(i);
1217 p_yea_info.housing_saving_tbl(i) := p_yea_info.hou_saving_cont_amount_tbl(i);
1218 else
1219 l_dummy := l_housinsavintype_HST1;
1220 p_yea_info.housing_saving_tbl(i) := l_housinsavintype_HST1;
1221 end if;
1222 /* Bug 9231094 */
1223 elsif p_yea_info.hou_saving_type_tbl(i) in ('HST4')
1224 and p_yea_info.taxable1 <= l_long_term_hou_pro_saving_lim
1225 and p_effective_date < c_20130101
1226 then
1227 l_dummy := p_yea_info.hou_saving_cont_amount_tbl(i);
1228 p_yea_info.housing_saving_tbl(i) := p_yea_info.hou_saving_cont_amount_tbl(i);
1229 -- Bug 5731165
1230 -- Removed code for Housing Saving Type2
1231 elsif p_yea_info.hou_saving_type_tbl(i) = 'HST2' then --Bug 5741382
1232 l_dummy := 0;
1233 p_yea_info.housing_saving_tbl(i) := 0;
1234 elsif p_yea_info.hou_saving_type_tbl(i) = 'HST3' then
1235 l_dummy := p_yea_info.hou_saving_cont_amount_tbl(i);
1236 p_yea_info.housing_saving_tbl(i) := p_yea_info.hou_saving_cont_amount_tbl(i);
1237 else
1238 p_yea_info.housing_saving_tbl(i) := 0;
1239 end if;
1240 --
1241 p_yea_info.housing_saving := p_yea_info.housing_saving + l_dummy;
1242 --
1243 end loop;
1244
1245 p_yea_info.max_housing_exp_tax_exem := 0;
1246 p_yea_info.housing_exp_tax_exem := 0;
1247
1248 --
1249 -- Start of Bug -- 7142620
1250 p_yea_info.housing_loan_repay_exem := 0;
1251 p_yea_info.lt_housing_loan_intr_exem := 0;
1252 -----------------------------------------------------------------------------------------
1253 --Removed code of housing Expense exemption calculation
1254 -----------------------------------------------------------------------------------------
1255 -----------------------------------------------------------------------------------------
1256 --Housing Loan Repay Exemption --Bug 7142620
1257 -----------------------------------------------------------------------------------------
1258 if p_yea_info.housing_loan_date >= add_months(p_yea_info.housing_purchase_date,-3)
1259 and p_yea_info.housing_loan_date <= add_months(p_yea_info.housing_purchase_date,3)
1260 then
1261 null;
1262 else
1263 p_yea_info.housing_loan_repay := 0;
1264 end if;
1265 if p_yea_info.housing_prv_loan_repay > 0
1266 and p_yea_info.taxable1 <= l_housing_rent_taxable_limit
1267 and p_yea_info.tot_num_dpnts > 0
1268 and p_yea_info.house_holder_flag = 'Y'
1269 and p_yea_info.housing_prv_loan_date >= c_20100101
1270 and p_yea_info.housing_prv_loan_date >= add_months(p_yea_info.housing_prv_date_moved,-1)
1271 and p_yea_info.housing_prv_loan_date <= add_months(p_yea_info.housing_prv_date_moved,1)
1272 then
1273 p_yea_info.housing_loan_repay := nvl(p_yea_info.housing_loan_repay,0) + p_yea_info.housing_prv_loan_repay;
1274 end if;
1275
1276 if p_yea_info.housing_loan_repay >0 then
1277
1278 p_yea_info.housing_loan_repay_exem := least(greatest(p_yea_info.taxable_income2 - l_cuml_special_exem,0), least(l_housinexp_tax_exem_lim,
1279 p_yea_info.housing_loan_repay * l_housinexp_tax_exem_per));
1280 p_yea_info.housing_exp_tax_exem := p_yea_info.housing_exp_tax_exem + p_yea_info.housing_loan_repay_exem;
1281 p_yea_info.max_housing_exp_tax_exem := nvl(p_yea_info.housing_loan_repay_exem,0) ;
1282
1283 end if;
1284 -- End of Bug -- 7142620
1285 -----------------------------------------------------------------------------------------
1286 -- Housing Rent Expense -- Bug 9737699
1287 -----------------------------------------------------------------------------------------
1288 if p_yea_info.housing_rent_exp > 0
1289 and p_yea_info.taxable1 <= l_housing_rent_taxable_limit
1290 and p_yea_info.tot_num_dpnts > 0
1291 and p_yea_info.house_holder_flag = 'Y' then
1292
1293 p_yea_info.housing_rent_exp_tax_exem := least(greatest(p_yea_info.taxable_income2 - l_cuml_special_exem ,0),
1294 least(l_housing_rent_exp_limit, (p_yea_info.housing_rent_exp * l_housing_rent_exp_per) + p_yea_info.max_housing_exp_tax_exem));
1295
1296 p_yea_info.housing_rent_exp_tax_exem := p_yea_info.housing_rent_exp_tax_exem - p_yea_info.housing_loan_repay_exem;
1297 p_yea_info.max_housing_exp_tax_exem := p_yea_info.max_housing_exp_tax_exem + p_yea_info.housing_rent_exp_tax_exem ;
1298 p_yea_info.housing_exp_tax_exem := p_yea_info.housing_exp_tax_exem + p_yea_info.housing_rent_exp_tax_exem;
1299 end if;
1300 -----------------------------------------------------------------------------------------
1301 --Long Term Housing Interest Repay Exemption
1302 -----------------------------------------------------------------------------------------
1303 -- Long term loan due less then 15 years
1304 --
1305 -- Bug 3966549 : Added date check
1306 -- Bug 9079478 : removed the date check for long term interest repay
1307 if p_yea_info.lt_housing_loan_interest_repay > 0 then
1308 -- End of 3966549
1309 -- Start of Bug --7142620
1310 -- Bug 7692554
1311 p_yea_info.max_housing_exp_tax_exem := p_yea_info.max_housing_exp_tax_exem + p_yea_info.lt_housing_loan_interest_repay;
1312 p_yea_info.max_housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,l_housinexp_tax_exem_lim1);
1313
1314 -- End of Bug -- 7142620
1315
1316 end if;
1317
1318 -- Long term loan due 15 - 29 years
1319 --
1320 if p_yea_info.lt_housing_loan_intr_repay_1 > 0 then
1321 --Start of Bug -- 7142620
1322 -- Bug 7692554
1323 p_yea_info.max_housing_exp_tax_exem := p_yea_info.max_housing_exp_tax_exem + p_yea_info.lt_housing_loan_intr_repay_1 ;
1324 p_yea_info.max_housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,l_housinexp_tax_exem_lim2);
1325 --End of Bug --7142620
1326 end if;
1327 -- Start of Bug 8237227
1328 -- Long term loan due greater then 30 years
1329 --
1330 if p_yea_info.lt_housing_loan_intr_repay_2 > 0 then
1331
1332 p_yea_info.max_housing_exp_tax_exem := p_yea_info.max_housing_exp_tax_exem + p_yea_info.lt_housing_loan_intr_repay_2 ;
1333 p_yea_info.max_housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,l_housinexp_tax_exem_lim3);
1334 end if;
1335 p_yea_info.lt_housing_loan_intr_exem := greatest(p_yea_info.max_housing_exp_tax_exem - p_yea_info.housing_loan_repay_exem - p_yea_info.housing_rent_exp_tax_exem,0);
1336 --
1337 -- End of Bug 8237227
1338 -- Bug 4119483: Apply upper limit based on cumulative special exemption
1339 --Start of Bug -- 7142620
1340 -- Bug 7692554
1341 p_yea_info.lt_housing_loan_intr_exem := least(p_yea_info.lt_housing_loan_intr_exem ,greatest(0,p_yea_info.taxable_income2 -
1342 (l_cuml_special_exem + p_yea_info.housing_loan_repay_exem + p_yea_info.housing_rent_exp_tax_exem)));
1343 p_yea_info.housing_exp_tax_exem := p_yea_info.housing_exp_tax_exem + p_yea_info.lt_housing_loan_intr_exem ;
1344 p_yea_info.housing_exp_tax_exem := trunc(p_yea_info.housing_exp_tax_exem);
1345 p_yea_info.lt_housing_loan_intr_exem := trunc(p_yea_info.lt_housing_loan_intr_exem);
1346 p_yea_info.housing_loan_repay_exem := trunc(p_yea_info.housing_loan_repay_exem );
1347 --End of Bug --7142620
1348 --
1349 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.housing_exp_tax_exem ;
1350 -- End of 4119483
1351 end if;
1352 end if; -- Bug 9737699
1353
1354 ------------------------------------------------------------------------
1355 -- Bug 7676136: Obsoleted Marriage, Funeral and Relocation Tax Exemption
1356 ------------------------------------------------------------------------
1357 p_yea_info.marr_fun_relo_exemption := 0;
1358 ----------------------------------------------------------------
1359 -- Special Tax Exemption
1360 ----------------------------------------------------------------
1361 end if;
1362 --
1363 -- Bug 9393732
1364 l_dummy_taxable_income := p_yea_info.taxable_income2;
1365 label := 0;
1366 while(label < 2) loop
1367
1368 --
1369 if g_debug then
1370 hr_utility.trace('Entering while loop : '||to_char(label));
1371 hr_utility.trace('Taxable Income2 = '||to_char(l_dummy_taxable_income));
1372 end if;
1373 --
1374
1375 p_yea_info.taxable_income2 := l_dummy_taxable_income;
1376
1377 if p_yea_info.non_resident_flag = 'N' then
1378 p_yea_info.std_sp_tax_exem := 0;
1379 p_yea_info.sp_tax_exem := p_yea_info.ins_prem_tax_exem
1380 + p_yea_info.med_exp_tax_exem
1381 + p_yea_info.educ_exp_tax_exem
1382 + p_yea_info.housing_exp_tax_exem
1383 + p_yea_info.donation_tax_exem
1384 + p_yea_info.marr_fun_relo_exemption;
1385
1386 if (p_yea_info.sp_tax_exem < l_std_sp_tax_exem and label > 0 ) then -- Bug 11768555
1387 p_yea_info.sp_tax_exem := 0;
1388 p_yea_info.std_sp_tax_exem := l_std_sp_tax_exem;
1389 end if;
1390 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.sp_tax_exem - p_yea_info.std_sp_tax_exem;
1391 --
1392 if g_debug then
1393 hr_utility.trace('Special Tax Exemption = '||to_char(p_yea_info.sp_tax_exem));
1394 hr_utility.trace('Std Special Tax Exemption = '||to_char(p_yea_info.std_sp_tax_exem));
1395 hr_utility.trace('Taxable Income2 = '||to_char(p_yea_info.taxable_income2));
1396 end if;
1397 --
1398 end if;
1399
1400 -- Bug 19737699
1401 ----------------------------------------------------------------
1402 -- Pension Premium Tax Exemption Bug 6843245
1403 ----------------------------------------------------------------
1404 if p_yea_info.pen_prem
1405 + p_yea_info.military_pen_prem
1406 + p_yea_info.private_school_pen_prem
1407 + p_yea_info.post_office_pen_prem > 0 then
1408 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.pen_prem - p_yea_info.military_pen_prem
1409 - p_yea_info.private_school_pen_prem - p_yea_info.post_office_pen_prem ;
1410
1411 end if;
1412 if p_yea_info.non_resident_flag = 'N' then
1413 ----------------------------------------------------------------
1414 -- National Pension Premium Tax Exemption
1415 ----------------------------------------------------------------
1416 if p_yea_info.np_prem > 0 then
1417 p_yea_info.np_prem_tax_exem := p_yea_info.np_prem;
1418 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.np_prem_tax_exem;
1419 end if;
1420 end if;
1421 ----------------------------------------------------------------
1422 -- Bug : 4776711
1423 -- Corporate Pension Premium Tax Exemption
1424 -- Bug 6852749
1425 -- p_yea_info.corp_pension_prem_tax_exem is now deducted from p_yea_info.taxable_income2 and not p_yea_info.taxation_base
1426 ----------------------------------------------------------------
1427 -- Bug 9737699
1428 l_sep_pen_details := 0;
1429 p_yea_info.scientific_technician_tax_exem := 0;
1430 p_yea_info.corp_pension_prem_tax_exem := 0;
1431 if p_yea_info.pers_pension_saving > 0 then
1432 p_yea_info.pers_pension_saving_tax_exem := least(p_yea_info.pers_pension_saving, l_pers_pen_savintax_exem_lim);
1433 end if;
1434 for i in 1..p_yea_info.sep_pen_type_tbl.count loop
1435 if p_yea_info.sep_pen_cont_amount_tbl(i) > 0 then
1436 p_yea_info.sep_pen_exem_amount_tbl(i) := greatest(least(p_yea_info.sep_pen_cont_amount_tbl(i)+p_yea_info.pers_pension_saving_tax_exem+l_sep_pen_details
1437 ,l_corp_pen_prem_tax_exem_lim)- nvl(p_yea_info.pers_pension_saving_tax_exem,0) - l_sep_pen_details,0);
1438 p_yea_info.sep_pen_exem_amount_tbl(i) := least (p_yea_info.taxable_income2, p_yea_info.sep_pen_exem_amount_tbl(i));
1439 l_sep_pen_details := l_sep_pen_details + p_yea_info.sep_pen_exem_amount_tbl(i);
1440 if p_yea_info.sep_pen_type_tbl(i) = '1' then
1441 p_yea_info.corp_pension_prem_tax_exem := nvl(p_yea_info.corp_pension_prem_tax_exem,0) + p_yea_info.sep_pen_exem_amount_tbl(i);
1442 elsif p_yea_info.sep_pen_type_tbl(i) = '2' then
1443 p_yea_info.scientific_technician_tax_exem := nvl(p_yea_info.scientific_technician_tax_exem,0) + p_yea_info.sep_pen_exem_amount_tbl(i);
1444 end if;
1445 end if;
1446 end loop;
1447 p_yea_info.pers_pension_saving_tax_exem := 0;
1448 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - nvl(p_yea_info.corp_pension_prem_tax_exem,0) - nvl(p_yea_info.scientific_technician_tax_exem,0);
1449 ----------------------------------------------------------------
1450 -- Taxable Income2
1451 ----------------------------------------------------------------
1452 p_yea_info.taxable_income2 := greatest(p_yea_info.taxable_income2, 0);
1453 p_yea_info.taxation_base := p_yea_info.taxable_income2;
1454 --
1455 if p_yea_info.non_resident_flag = 'N' then
1456 ------------------------------------------------------------------------------------
1457 -- Personal Pension Premium Tax Exemption and Personal Pension Saving Tax Exemption
1458 ------------------------------------------------------------------------------------
1459 l_pen_prem_details := 0;
1460 l_pen_saving_details := 0;
1461 p_yea_info.pers_pension_saving_tax_exem := 0;
1462 p_yea_info.pers_pension_prem_tax_exem := 0;
1463 for i in 1..p_yea_info.pen_saving_type_tbl.count loop
1464 if p_yea_info.pen_saving_cont_amount_tbl(i) > 0 then
1465 if p_yea_info.pen_saving_type_tbl(i) = '1' then
1466 p_yea_info.pen_saving_exem_amount_tbl(i) := trunc(greatest(least(trunc(p_yea_info.pen_saving_cont_amount_tbl(i) * l_pers_pen_prem_tax_exem_per) + nvl(l_pen_prem_details,0)
1467 , l_pers_pen_prem_tax_exem_lim) - nvl(l_pen_prem_details,0),0));
1468 p_yea_info.pen_saving_exem_amount_tbl(i) := least(p_yea_info.pen_saving_exem_amount_tbl(i), p_yea_info.taxation_base);
1469 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pen_saving_exem_amount_tbl(i);
1470 l_pen_prem_details := l_pen_prem_details + p_yea_info.pen_saving_exem_amount_tbl(i);
1471 p_yea_info.pers_pension_prem_tax_exem := nvl(p_yea_info.pers_pension_prem_tax_exem,0) + p_yea_info.pen_saving_exem_amount_tbl(i);
1472 elsif p_yea_info.pen_saving_type_tbl(i) = '2' then
1473 p_yea_info.pen_saving_exem_amount_tbl(i) := greatest(least(p_yea_info.pen_saving_cont_amount_tbl(i) + l_pen_saving_details,l_pers_pen_savintax_exem_lim) - l_pen_saving_details,0);
1474 p_yea_info.pen_saving_exem_amount_tbl(i) := least(p_yea_info.taxation_base,p_yea_info.pen_saving_exem_amount_tbl(i));
1475 l_pen_saving_details := l_pen_saving_details + p_yea_info.pen_saving_exem_amount_tbl(i);
1476 p_yea_info.pers_pension_saving_tax_exem := nvl(p_yea_info.pers_pension_saving_tax_exem,0) + p_yea_info.pen_saving_exem_amount_tbl(i);
1477 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pen_saving_exem_amount_tbl(i);
1478 end if;
1479 end if;
1480 end loop;
1481 ----------------------------------------------------------------
1482 -- Small Business Installment Tax Exemption Bug 6895093
1483 ----------------------------------------------------------------
1484 if p_yea_info.small_bus_install > 0 then
1485 p_yea_info.small_bus_install_exem := least(p_yea_info.small_bus_install, least(greatest(p_yea_info.taxation_base,0),l_small_bus_install_lim));
1486 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.small_bus_install_exem;
1487 else
1488 p_yea_info.small_bus_install_exem := 0;
1489 end if;
1490 -----------------------------------------------------------------------------------------
1491 -- Housing Saving Exemption -- Bug 7142620
1492 -----------------------------------------------------------------------------------------
1493
1494 l_dummy := 0;
1495 l_housing_saving_exem := 0;
1496 p_yea_info.housing_sub_saving_exem := 0;
1497 p_yea_info.emp_hou_pro_saving_exem := 0;
1498 p_yea_info.lt_hou_pro_saving_exem := 0;
1499 p_yea_info.total_housing_saving_exem := 0;
1500
1501 if p_yea_info.housing_saving >0 then
1502 -- Bug 8240892
1503
1504 p_yea_info.housing_saving_exem := least(greatest(p_yea_info.taxation_base,0) ,
1505 least(l_housinexp_tax_exem_lim - nvl(p_yea_info.housing_loan_repay_exem,0), p_yea_info.housing_saving * l_housinexp_tax_exem_per ));
1506
1507 if p_yea_info.lt_housing_loan_interest_repay > 0 and p_yea_info.lt_housing_loan_date < c_20040101 then
1508 p_yea_info.housing_saving_exem := least(greatest(p_yea_info.taxation_base,0) ,
1509 least(l_housinexp_tax_exem_lim1 - nvl(p_yea_info.housing_loan_repay_exem,0) - nvl(p_yea_info.lt_housing_loan_intr_exem,0),
1510 l_housinexp_tax_exem_lim - nvl(p_yea_info.housing_loan_repay_exem,0), p_yea_info.housing_saving * l_housinexp_tax_exem_per ));
1511 end if;
1512
1513 if p_yea_info.lt_housing_loan_intr_repay_1 > 0 then
1514 p_yea_info.housing_saving_exem := least(greatest(p_yea_info.taxation_base,0) ,
1515 least(l_housinexp_tax_exem_lim2 - nvl(p_yea_info.housing_loan_repay_exem,0) - nvl(p_yea_info.lt_housing_loan_intr_exem,0),
1516 l_housinexp_tax_exem_lim - nvl(p_yea_info.housing_loan_repay_exem,0), p_yea_info.housing_saving * l_housinexp_tax_exem_per ));
1517 end if;
1518
1519 -- Start of Bug 8237227
1520 if p_yea_info.lt_housing_loan_intr_repay_2 > 0 then
1521 p_yea_info.housing_saving_exem := least(greatest(p_yea_info.taxation_base,0) ,
1522 least(l_housinexp_tax_exem_lim3 - nvl(p_yea_info.housing_loan_repay_exem,0) - nvl(p_yea_info.lt_housing_loan_intr_exem,0),
1523 l_housinexp_tax_exem_lim - nvl(p_yea_info.housing_loan_repay_exem,0), p_yea_info.housing_saving * l_housinexp_tax_exem_per ));
1524 end if;
1525 -- Bug 9737699
1526 l_housing_saving_exem := nvl(p_yea_info.housing_saving_exem,0);
1527 for i in 1..p_yea_info.hou_saving_type_tbl.count loop
1528 p_yea_info.hou_saving_exem_amount_tbl(i) := least(l_housing_saving_exem,trunc((p_yea_info.housing_saving_tbl(i)* l_housinexp_tax_exem_per)));
1529 if p_yea_info.hou_saving_type_tbl(i) = 'HST1' then
1530 p_yea_info.housing_sub_saving_exem := nvl(p_yea_info.housing_sub_saving_exem,0)+ p_yea_info.hou_saving_exem_amount_tbl(i);
1531 elsif p_yea_info.hou_saving_type_tbl(i) = 'HST3' then
1532 p_yea_info.emp_hou_pro_saving_exem := nvl(p_yea_info.emp_hou_pro_saving_exem,0)+ p_yea_info.hou_saving_exem_amount_tbl(i);
1533 elsif p_yea_info.hou_saving_type_tbl(i) = 'HST4' then
1534 p_yea_info.hou_saving_exem_amount_tbl(i) := least(lt_housing_pro_saving_limit,p_yea_info.hou_saving_exem_amount_tbl(i));
1535 p_yea_info.lt_hou_pro_saving_exem := nvl(p_yea_info.lt_hou_pro_saving_exem,0) + p_yea_info.hou_saving_exem_amount_tbl(i);
1536 elsif p_yea_info.hou_saving_type_tbl(i) = 'HST5' then
1537 p_yea_info.total_housing_saving_exem := nvl(p_yea_info.total_housing_saving_exem,0) + p_yea_info.hou_saving_exem_amount_tbl(i);
1538 end if;
1539 l_housing_saving_exem := greatest(l_housing_saving_exem- p_yea_info.hou_saving_exem_amount_tbl(i),0);
1540 end loop;
1541
1542
1543 -- End of bug 8237227
1544 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.housing_saving_exem;
1545
1546 -- End of Bug 8240892
1547 else
1548 p_yea_info.housing_saving_exem := 0;
1549 end if;
1550 ----------------------------------------------------------------
1551 -- Investment Partnership Financing Tax Exemption
1552 ----------------------------------------------------------------
1553 -- Bug 9079450: Removed code for investment partnership financing1
1554 if p_yea_info.invest_partner_fin2 > 0
1555 or p_yea_info.invest_partner_fin3 > 0 then
1556 if p_yea_info.taxable_income > 0 then
1557 p_yea_info.invest_partner_fin_tax_exem := least(trunc(p_yea_info.invest_partner_fin2 * l_inv_part_fin2_tax_exem_per),
1558 trunc(p_yea_info.taxable_income * l_inv_part_fin2_tax_exem_lim));
1559 -- Bug 8237227
1560 p_yea_info.invest_partner_fin_tax_exem := p_yea_info.invest_partner_fin_tax_exem +
1561 least(trunc(p_yea_info.invest_partner_fin3 * l_inv_part_fin3_tax_exem_per),
1562 trunc(p_yea_info.taxable_income * l_inv_part_fin3_tax_exem_lim));
1563 --/* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
1564 --l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base); /* bug 12778151, moved to below for excluding following exemption */
1565 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.invest_partner_fin_tax_exem;
1566 end if;
1567 end if;
1568 ----------------------------------------------------------------
1569 -- Credit Card Expense Tax Exem
1570 -- Bug 3966549: Now differentiate between employee's and
1571 -- dependents' direct payments. Also use new
1572 -- global value
1573 -- (KR_YEA_CREDIT_CARD_EXP_2004_TAX_EXEM) to
1574 -- calculate max. exemption amount.
1575 -- Bug 3506168: Added cash receipt expenses.
1576 ----------------------------------------------------------------
1577 ----------------------------------------------------------------
1578 -- Bug 6630135 Amount that needs to be excluded from cards exemption
1579 -- (Double Exemption amount to be excluded)
1580 -- = Total medical expense amount paid in cards (p_yea_info.tot_med_exp_cards)
1581 -- - Medical Expense Paid (both cards and cash) but not included in medical expense exemption (l_card_exp_not_exempted -- Calculated in Medical Exemption)
1582 -- - Medical Expense Paid in cards for the Dependent who are not eligible for Basic Exemption ((p_yea_info.dpnt_med_exp_cards)
1583 ----------------------------------------------------------------
1584
1585 p_yea_info.med_exp_paid_not_inc_med_exem := nvl(l_card_exp_not_exempted,0);
1586 l_double_exem_amt := 0 ;
1587 l_dummy := 0;
1588 -- Bug 7668422 Double Exemption is allowed for medical expense paid in cards
1589 /*
1590 -- Start Bug 6685443 added if condition below
1591 if nvl(p_yea_info.tot_med_exp_cards,0) >= trunc(greatest(p_yea_info.taxable, 0) * l_med_exp_tax_exem_per) then
1592
1593 if nvl(l_card_exp_not_exempted,0) > 0 then
1594 l_double_exem_amt := greatest((nvl(p_yea_info.tot_med_exp_cards,0) - nvl(l_card_exp_not_exempted,0) - nvl(p_yea_info.dpnt_med_exp_cards,0)),0);
1595 elsif nvl(l_card_exp_not_exempted,0) = 0 then
1596 l_double_exem_amt := 0 ;
1597 end if;
1598 else
1599 l_double_exem_amt := 0 ;
1600 end if;
1601 -- End Bug 6685443
1602 -- End of Bug 6630135
1603 -- Bug 6716401
1604 */
1605 p_yea_info.double_exem_amt := l_double_exem_amt;
1606 -- End of Bug 6716401
1607 -- Bug 7615517: Updated Total Credit Card amount for Company Related Expenses
1608 p_yea_info.total_credit_card_exp :=p_yea_info.emp_cre_card_direct_exp2004
1609 + p_yea_info.dpnt_cre_card_direct_exp2004
1610 + p_yea_info.giro_tuition_paid_exp2004
1611 + p_yea_info.cash_receipt_exp2005
1612 - p_yea_info.company_related_exp ;
1613 -- Bug 7668422
1614 -- - l_double_exem_amt ; -- Bug 5682131 l_double_exem_amt added for Bug 6630135
1615 if p_yea_info.total_credit_card_exp > 0 and p_effective_date < c_20120101 then
1616 -- After the statement below, l_dummy holds the total credit card expense subject to exemption
1617 l_dummy := p_yea_info.total_credit_card_exp - (greatest(p_yea_info.taxable, 0) * l_cre_card_exp_tax_exem_per1);
1618 --
1619
1620 if l_dummy > 0 then
1621 -- Bug 9231094
1622 -- Bug 11665554
1623 if label = 0 then
1624 l_credit_card_exp := p_yea_info.credit_card_exp;
1625 end if;
1626 -- End of Bug 11665554
1627 p_yea_info.credit_card_exp_tax_exem := (l_dummy * ( (l_credit_card_exp + p_yea_info.cash_receipt_exp2005 + p_yea_info.giro_tuition_paid_exp2004)/ p_yea_info.total_credit_card_exp) * l_cre_card_tax_exem_per2004 );
1628 p_yea_info.direct_card_exp_tax_exem := (l_dummy * ( p_yea_info.direct_card_exp / p_yea_info.total_credit_card_exp ) * l_dir_pay_tax_exem_per2010 );
1629 --
1630 -- After statement below, l_dummy holds maximum exemption amount
1631 l_dummy := p_yea_info.credit_card_exp_tax_exem + p_yea_info.direct_card_exp_tax_exem;
1632 -- End of bug 9231094
1633 --
1634 -- End of 3966549
1635 --
1636 -- Bug 3089512 Consider the 20% of Taxable Earnings for Credit card exemption.
1637 p_yea_info.total_credit_card_exp_tax_exem := trunc(least(l_dummy ,(p_yea_info.taxable * l_cre_card_exp_tax_exem_per2), l_cre_card_exp_tax_exem_lim));
1638 p_yea_info.total_credit_card_exp_tax_exem := least(p_yea_info.total_credit_card_exp_tax_exem,greatest(p_yea_info.taxation_base,0));
1639 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.total_credit_card_exp_tax_exem;
1640 --
1641 -- Collect total credit card exemption into p_yea_info.credit_card_exp_tax_exem
1642 -- for keeping compatibility with package pay_kr_yea20020101_pkg
1643 -- These are used to display data in YEA result form and YEA reports
1644 --
1645 p_yea_info.credit_card_exp_tax_exem := p_yea_info.total_credit_card_exp_tax_exem;
1646 end if;
1647 -- Collect total credit card expenses into p_yea_info.credit_card_exp
1648 p_yea_info.credit_card_exp := p_yea_info.total_credit_card_exp;
1649 --
1650 end if;
1651 end if;
1652 ----------------------------------------------------------------
1653 -- Employee Stock Ownership Plan Contribution Tax Exemption
1654 ----------------------------------------------------------------
1655 if p_yea_info.emp_stk_own_contri > 0 then
1656 p_yea_info.emp_stk_own_contri_tax_exem :=
1657 least(p_yea_info.emp_stk_own_contri, l_emp_stock_own_plan_exem_lim);
1658 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.emp_stk_own_contri_tax_exem;
1659 end if;
1660 if p_yea_info.non_resident_flag = 'N' then
1661 ----------------------------------------------------------------
1662 -- Bug 7615517: Long Term Stock Fund Tax Exemption
1663 ----------------------------------------------------------------
1664 if ((p_yea_info.long_term_stck_fund_1year > 0)
1665 or (p_yea_info.long_term_stck_fund_2year > 0)
1666 or (p_yea_info.long_term_stck_fund_3year > 0)) then
1667
1668 p_yea_info.long_term_stck_fund_tax_exem
1669 := (greatest(p_yea_info.long_term_stck_fund_1year,0)*l_long_term_stck_fund_1yr_pct)
1670 + (greatest(p_yea_info.long_term_stck_fund_2year,0)*l_long_term_stck_fund_2yr_pct)
1671 + (greatest(p_yea_info.long_term_stck_fund_3year,0)*l_long_term_stck_fund_3yr_pct); -- Bug 9737699
1672
1673 /* (least(greatest(p_yea_info.long_term_stck_fund_1year,0), l_long_term_stck_fund_1yr_lim)*l_long_term_stck_fund_1yr_pct)
1674 + (least(greatest(p_yea_info.long_term_stck_fund_2year,0), l_long_term_stck_fund_2yr_lim)*l_long_term_stck_fund_2yr_pct)
1675 + (least(greatest(p_yea_info.long_term_stck_fund_3year,0), l_long_term_stck_fund_3yr_lim)*l_long_term_stck_fund_3yr_pct); */
1676
1677 p_yea_info.long_term_stck_fund_tax_exem := least(trunc(p_yea_info.long_term_stck_fund_tax_exem), greatest(p_yea_info.taxation_base,0),l_long_term_stck_fund_tot_lim);
1678 lt_stock_saving_year := p_yea_info.long_term_stck_fund_tax_exem;
1679
1680 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.long_term_stck_fund_tax_exem;
1681 for i in 1..p_yea_info.lt_stck_saving_type_tbl.count loop
1682 if p_yea_info.lt_stck_saving_type_tbl(i) = '1' then
1683 p_yea_info.lt_stck_saving_exem_amt_tbl(i) := trunc(least((greatest(p_yea_info.lt_stck_saving_cont_amt_tbl(i),0)
1684 *l_long_term_stck_fund_1yr_pct),greatest(p_yea_info.taxation_base,0),lt_stock_saving_year));
1685 lt_stock_saving_year := lt_stock_saving_year - p_yea_info.lt_stck_saving_exem_amt_tbl(i);
1686
1687 elsif p_yea_info.lt_stck_saving_type_tbl(i) = '2' then
1688 p_yea_info.lt_stck_saving_exem_amt_tbl(i) := trunc(least((greatest(p_yea_info.lt_stck_saving_cont_amt_tbl(i),0)
1689 *l_long_term_stck_fund_2yr_pct),greatest(p_yea_info.taxation_base,0),lt_stock_saving_year));
1690 lt_stock_saving_year := lt_stock_saving_year - p_yea_info.lt_stck_saving_exem_amt_tbl(i);
1691
1692 elsif p_yea_info.lt_stck_saving_type_tbl(i) = '3' then
1693 p_yea_info.lt_stck_saving_exem_amt_tbl(i) := trunc(least((greatest(p_yea_info.lt_stck_saving_cont_amt_tbl(i),0)
1694 *l_long_term_stck_fund_3yr_pct),greatest(p_yea_info.taxation_base,0),lt_stock_saving_year));
1695 lt_stock_saving_year := lt_stock_saving_year - p_yea_info.lt_stck_saving_exem_amt_tbl(i);
1696 end if;
1697 end loop;
1698 else
1699 p_yea_info.long_term_stck_fund_tax_exem := 0;
1700 end if;
1701 --
1702 end if;
1703 ---------------------------------------------------------------------------
1704 -- Bug 9079450: Employment Retention Small-Medium Business Income Exemption
1705 ---------------------------------------------------------------------------
1706 if (p_yea_info.smb_eligibility_flag = 'Y') then
1707
1708 l_er_smb_prev_earn := nvl(p_yea_info.prev_smb_eligible_income,0);
1709 l_er_smb_cur_earn := nvl(p_yea_info.cur_smb_eligible_income,0);
1710
1711 if ( nvl(p_yea_info.cur_smb_days_worked,0) = 0
1712 or nvl(p_yea_info.prev_smb_days_worked,0) = 0
1713 or p_yea_info.emp_join_prev_year = 'X'
1714 or p_yea_info.emp_leave_cur_year = 'X') then
1715 p_yea_info.smb_income_exem := 0;
1716 else
1717 if p_yea_info.emp_join_prev_year = 'Y' then
1718 l_er_smb_cur_earn := l_er_smb_cur_earn
1719 * (p_yea_info.prev_smb_days_worked / p_yea_info.cur_smb_days_worked);
1720 end if;
1721 if p_yea_info.emp_leave_cur_year = 'Y' then
1722 l_er_smb_prev_earn := l_er_smb_prev_earn
1723 * (p_yea_info.cur_smb_days_worked / p_yea_info.prev_smb_days_worked);
1724 end if;
1725 --
1726 p_yea_info.smb_income_exem := greatest(least(round((l_er_smb_prev_earn - l_er_smb_cur_earn)
1727 * l_er_smb_exem_pct,0), l_er_smb_exem_limit,
1728 greatest(p_yea_info.taxation_base,0)),0);
1729 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.smb_income_exem;
1730 end if;
1731 else
1732 p_yea_info.smb_income_exem := 0;
1733 end if;
1734
1735 if label = 0 then
1736
1737 --
1738 if g_debug then
1739 hr_utility.trace('Deleting future carry over details for donation (if any)');
1740 end if;
1741 --
1742 delete_future_details(p_assignment_id,
1743 p_effective_date);
1744 --
1745 if p_yea_info.non_resident_flag = 'N' then
1746 -----------------------------------------------------------------
1747 -- Donation Tax Exemption
1748 -- Bug 3966549: Modified calculation for statutory donation
1749 -- exemption. Added code to calculate tax break
1750 -- and ESOA donation exemption details. Included
1751 -- ESOA component in the total political exemption
1752 -- amount.
1753 -- Bug 9393732: Donation calculation logic has been revised
1754 -----------------------------------------------------------------
1755 --
1756 -- Calculate for Statutory Donation
1757 --
1758 if g_debug then
1759 hr_utility.trace('Taxable Income just before Donation: '||to_char(p_yea_info.taxation_base));
1760 hr_utility.trace('*** Calculate Statutory Donation ***');
1761 end if;
1762 --
1763 l_political2_exem := 0;
1764 --
1765 l_actual_max_donation_dummy := greatest(p_yea_info.taxation_base,0);
1766 --
1767 p_yea_info.donation1_tax_exem := 0;
1768 --
1769 if g_debug then
1770 hr_utility.trace('*** Calculate Political Donation ***');
1771 end if;
1772 --
1773 if p_yea_info.political_donation1 > 0 then
1774 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(l_actual_max_donation_dummy,p_yea_info.taxable_income,greatest(p_yea_info.political_donation1 - l_political_tax_brk_lim2004, 0));
1775 l_actual_max_donation_dummy := greatest(l_actual_max_donation_dummy - p_yea_info.donation1_tax_exem,0);
1776 end if;
1777 --
1778 if p_yea_info.political_donation2 > 0 then
1779 l_political2_exem := least(greatest(p_yea_info.taxable_income - p_yea_info.donation1_tax_exem,0),p_yea_info.political_donation2,l_actual_max_donation_dummy);
1780 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + l_political2_exem ;
1781 l_actual_max_donation_dummy := greatest(l_actual_max_donation_dummy - l_political2_exem ,0);
1782 update_donation_details(p_assignment_id,
1783 p_effective_date,
1784 '20',
1785 l_political2_exem,
1786 p_yea_info.political_donation2 - l_political2_exem);
1787 end if;
1788 --
1789 l_max_donation1_dummy := greatest(p_yea_info.taxable_income - p_yea_info.donation1_tax_exem,0);
1790 --
1791 l_stat_tax_exem := 0;
1792 --
1793 for i in 1..p_yea_info.stat_amt_sub_to_exem_tbl.count loop
1794 if (chk_valid_donation(p_yea_info.stat_donated_year_tbl(i),'10',p_effective_date) = 'Y') then
1795 l_stat_tax_exem := least(p_yea_info.stat_amt_sub_to_exem_tbl(i),l_max_donation1_dummy,l_actual_max_donation_dummy);
1796 else
1797 l_stat_tax_exem := 0;
1798 end if;
1799 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + l_stat_tax_exem;
1800 l_max_donation1_dummy := greatest(0,l_max_donation1_dummy - l_stat_tax_exem);
1801 l_actual_max_donation_dummy := greatest(0,l_actual_max_donation_dummy - l_stat_tax_exem);
1802 insert_donation_details(p_assignment_id,
1803 p_effective_date,
1804 p_yea_info.stat_asg_info_id_tbl(i),
1805 l_stat_tax_exem,
1806 p_yea_info.stat_amt_sub_to_exem_tbl(i) - l_stat_tax_exem);
1807 end loop;
1808 --
1809 -- Calculate Tax Break for Political Donation
1810 -- Bug 5682450: Updated Political Donation Tax Break
1811 p_yea_info.don_tax_break2004 := (greatest(0, p_yea_info.political_donation1) - greatest(0, p_yea_info.political_donation1 - l_political_tax_brk_lim2004 )) / l_pol_don_exem_rate_factor ;
1812 -- End of 5682450
1813 --
1814 -- Calculate for Special Donation
1815 -- Bug 7142612: Added Public Legal Entity Donation Trust to Tax Law Amount
1816 --
1817 --
1818 if g_debug then
1819 hr_utility.trace('*** Calculate Special Donation ***');
1820 end if;
1821 --
1822 p_yea_info.donation3_tax_exem := 0;
1823 l_max_donation3_dummy := 0;
1824
1825 if (p_yea_info.pledt_amt_sub_to_exem_tbl.count > 0 or p_yea_info.tlaw_amt_sub_to_exem_tbl.count > 0) then
1826
1827 if p_yea_info.taxable_income > p_yea_info.donation1_tax_exem then
1828 p_yea_info.max_donation3_tax_exem := trunc((p_yea_info.taxable_income - p_yea_info.donation1_tax_exem) * l_donation3_tax_exem_per);
1829 l_max_donation3_dummy := p_yea_info.max_donation3_tax_exem;
1830
1831 -- Loop for Tax Reduction Law
1832 l_tlaw_tax_exem := 0;
1833 for i in 1..p_yea_info.tlaw_amt_sub_to_exem_tbl.count loop
1834 if (chk_valid_donation(p_yea_info.tlaw_donated_year_tbl(i),'30',p_effective_date) = 'Y') then
1835 l_tlaw_tax_exem := least(p_yea_info.tlaw_amt_sub_to_exem_tbl(i), l_max_donation3_dummy,l_actual_max_donation_dummy);
1836 else
1837 l_tlaw_tax_exem := 0;
1838 end if;
1839 p_yea_info.donation3_tax_exem := p_yea_info.donation3_tax_exem + l_tlaw_tax_exem;
1840 l_max_donation3_dummy := greatest(0,l_max_donation3_dummy - l_tlaw_tax_exem);
1841 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_tlaw_tax_exem);
1842 insert_donation_details(p_assignment_id,
1843 p_effective_date,
1844 p_yea_info.tlaw_asg_info_id_tbl(i),
1845 l_tlaw_tax_exem,
1846 p_yea_info.tlaw_amt_sub_to_exem_tbl(i) - l_tlaw_tax_exem);
1847 end loop;
1848
1849 -- Loop for PLEDT Donation Amount
1850 l_pledt_tax_exem := 0;
1851 for i in 1..p_yea_info.pledt_amt_sub_to_exem_tbl.count loop
1852 if (chk_valid_donation(p_yea_info.pledt_donated_year_tbl(i),'31',p_effective_date) = 'Y') then
1853 l_pledt_tax_exem := least(p_yea_info.pledt_amt_sub_to_exem_tbl(i), l_max_donation3_dummy, l_actual_max_donation_dummy);
1854 else
1855 l_pledt_tax_exem := 0;
1856 end if;
1857 p_yea_info.donation3_tax_exem := p_yea_info.donation3_tax_exem + l_pledt_tax_exem;
1858 l_max_donation3_dummy := greatest(0,l_max_donation3_dummy - l_pledt_tax_exem);
1859 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_pledt_tax_exem);
1860 insert_donation_details(p_assignment_id,
1861 p_effective_date,
1862 p_yea_info.pledt_asg_info_id_tbl(i),
1863 l_pledt_tax_exem,
1864 p_yea_info.pledt_amt_sub_to_exem_tbl(i) - l_pledt_tax_exem);
1865
1866 end loop;
1867 end if;
1868 end if;
1869
1870 --
1871 -- Calculate for ESOA Donation
1872 --
1873 if g_debug then
1874 hr_utility.trace('*** Calculate ESOA Donation ***');
1875 end if;
1876 --
1877 if p_yea_info.esoa_don2004 > 0 then
1878 if p_yea_info.taxable_income > (p_yea_info.donation1_tax_exem + p_yea_info.donation3_tax_exem) then
1879 p_yea_info.max_esoa_don_tax_exem2004 := trunc( (p_yea_info.taxable_income - p_yea_info.donation1_tax_exem - p_yea_info.donation3_tax_exem) * l_esoa_tax_exem_per2004 ) ;
1880 p_yea_info.esoa_don_tax_exem2004 := least(p_yea_info.esoa_don2004, p_yea_info.max_esoa_don_tax_exem2004, l_actual_max_donation_dummy ) ;
1881 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - p_yea_info.esoa_don_tax_exem2004);
1882 end if ;
1883 end if ;
1884 update_donation_details(p_assignment_id,
1885 p_effective_date,
1886 '42',
1887 p_yea_info.esoa_don_tax_exem2004,
1888 p_yea_info.esoa_don2004 - p_yea_info.esoa_don_tax_exem2004);
1889 --
1890 -- Calculate for Specified and Religious Donation
1891 --
1892 --
1893 if g_debug then
1894 hr_utility.trace('*** Calculate Specified and Religious Donation ***');
1895 end if;
1896 --
1897 l_max_donation2_dummy := 0;
1898 p_yea_info.donation2_tax_exem := 0;
1899 --
1900 if p_yea_info.rel_amt_sub_to_exem_tbl.count > 0 then
1901 --
1902 l_spec_amt := 0;
1903 l_spec_year := 0;
1904 l_rel_year := 0;
1905 l_row_count := 1;
1906 --
1907 for i in 1..p_yea_info.spec_amt_sub_to_exem_tbl.count loop
1908 --
1909 if (chk_valid_donation(p_yea_info.spec_donated_year_tbl(i),'40',p_effective_date) = 'Y') then
1910 if l_row_count = 1 then
1911 l_spec_year := p_yea_info.spec_donated_year_tbl(i);
1912 l_row_count := l_row_count + 1;
1913 end if;
1914 --
1915 l_spec_amt := l_spec_amt + p_yea_info.spec_amt_sub_to_exem_tbl(i);
1916 end if;
1917 end loop;
1918 --
1919 l_row_count := 1;
1920 --
1921 for i in 1..p_yea_info.rel_amt_sub_to_exem_tbl.count loop
1922 --
1923 if (chk_valid_donation(p_yea_info.rel_donated_year_tbl(i),'41',p_effective_date) = 'Y') then
1924 --
1925 if l_row_count = 1 then
1926 l_rel_year := p_yea_info.rel_donated_year_tbl(i);
1927 l_row_count := l_row_count + 1;
1928 end if;
1929 end if;
1930 end loop;
1931 --
1932 if p_yea_info.taxable_income > ( p_yea_info.donation1_tax_exem
1933 + p_yea_info.donation3_tax_exem
1934 + p_yea_info.esoa_don_tax_exem2004 ) then
1935 p_yea_info.max_donation2_tax_exem := trunc(
1936 (p_yea_info.taxable_income
1937 - p_yea_info.donation1_tax_exem
1938 - p_yea_info.donation3_tax_exem
1939 - p_yea_info.esoa_don_tax_exem2004 ) * l_rel_don_tax_exem_per1)
1940 + least(trunc(
1941 (p_yea_info.taxable_income
1942 - p_yea_info.donation1_tax_exem
1943 - p_yea_info.donation3_tax_exem
1944 - p_yea_info.esoa_don_tax_exem2004 ) * l_rel_don_tax_exem_per2), l_spec_amt);
1945 l_max_donation2_dummy := p_yea_info.max_donation2_tax_exem;
1946
1947 if l_rel_year < l_spec_year then
1948 --
1949 -- Loop for Religious Donation
1950 --
1951 l_rel_tax_exem := 0;
1952 --
1953 for i in 1..p_yea_info.rel_amt_sub_to_exem_tbl.count loop
1954 if (chk_valid_donation(p_yea_info.rel_donated_year_tbl(i),'41',p_effective_date) = 'Y') then
1955 l_rel_tax_exem := least(p_yea_info.rel_amt_sub_to_exem_tbl(i), l_max_donation2_dummy, l_actual_max_donation_dummy);
1956 else
1957 l_rel_tax_exem := 0;
1958 end if;
1959 p_yea_info.donation2_tax_exem := p_yea_info.donation2_tax_exem + l_rel_tax_exem;
1960 l_max_donation2_dummy := greatest(0,l_max_donation2_dummy - l_rel_tax_exem);
1961 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_rel_tax_exem);
1962 insert_donation_details(p_assignment_id,
1963 p_effective_date,
1964 p_yea_info.rel_asg_info_id_tbl(i),
1965 l_rel_tax_exem,
1966 p_yea_info.rel_amt_sub_to_exem_tbl(i) - l_rel_tax_exem);
1967 end loop;
1968 --
1969 -- Loop for Specified Donation
1970 --
1971 l_spec_tax_exem := 0;
1972 --
1973 for i in 1..p_yea_info.spec_amt_sub_to_exem_tbl.count loop
1974 if (chk_valid_donation(p_yea_info.spec_donated_year_tbl(i),'40',p_effective_date) = 'Y') then
1975 l_spec_tax_exem := least(p_yea_info.spec_amt_sub_to_exem_tbl(i), l_max_donation2_dummy, l_actual_max_donation_dummy);
1976 else
1977 l_spec_tax_exem := 0;
1978 end if;
1979 p_yea_info.donation2_tax_exem := p_yea_info.donation2_tax_exem + l_spec_tax_exem;
1980 l_max_donation2_dummy := greatest(0,l_max_donation2_dummy - l_spec_tax_exem);
1981 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_spec_tax_exem);
1982 insert_donation_details(p_assignment_id,
1983 p_effective_date,
1984 p_yea_info.spec_asg_info_id_tbl(i),
1985 l_spec_tax_exem,
1986 p_yea_info.spec_amt_sub_to_exem_tbl(i) - l_spec_tax_exem);
1987 end loop;
1988 --
1989 else
1990 --
1991 -- Loop for Specified Donation
1992 --
1993 l_spec_tax_exem := 0;
1994 --
1995 for i in 1..p_yea_info.spec_amt_sub_to_exem_tbl.count loop
1996 if (chk_valid_donation(p_yea_info.spec_donated_year_tbl(i),'40',p_effective_date) = 'Y') then
1997 l_spec_tax_exem := least(p_yea_info.spec_amt_sub_to_exem_tbl(i), l_max_donation2_dummy, l_actual_max_donation_dummy);
1998 else
1999 l_spec_tax_exem := 0;
2000 end if;
2001 p_yea_info.donation2_tax_exem := p_yea_info.donation2_tax_exem + l_spec_tax_exem;
2002 l_max_donation2_dummy := greatest(0,l_max_donation2_dummy - l_spec_tax_exem);
2003 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_spec_tax_exem);
2004 insert_donation_details(p_assignment_id,
2005 p_effective_date,
2006 p_yea_info.spec_asg_info_id_tbl(i),
2007 l_spec_tax_exem,
2008 p_yea_info.spec_amt_sub_to_exem_tbl(i) - l_spec_tax_exem);
2009 end loop;
2010 --
2011 -- Loop for Religious Donation
2012 --
2013 l_rel_tax_exem := 0;
2014 --
2015 for i in 1..p_yea_info.rel_amt_sub_to_exem_tbl.count loop
2016 if (chk_valid_donation(p_yea_info.rel_donated_year_tbl(i),'41',p_effective_date) = 'Y') then
2017 l_rel_tax_exem := least(p_yea_info.rel_amt_sub_to_exem_tbl(i), l_max_donation2_dummy, l_actual_max_donation_dummy);
2018 else
2019 l_rel_tax_exem := 0;
2020 end if;
2021 p_yea_info.donation2_tax_exem := p_yea_info.donation2_tax_exem + l_rel_tax_exem;
2022 l_max_donation2_dummy := greatest(0,l_max_donation2_dummy - l_rel_tax_exem);
2023 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_rel_tax_exem);
2024 insert_donation_details(p_assignment_id,
2025 p_effective_date,
2026 p_yea_info.rel_asg_info_id_tbl(i),
2027 l_rel_tax_exem,
2028 p_yea_info.rel_amt_sub_to_exem_tbl(i) - l_rel_tax_exem);
2029 end loop;
2030 --
2031 end if;
2032
2033 end if;
2034 --
2035 else
2036 --
2037 if p_yea_info.spec_amt_sub_to_exem_tbl.count > 0 then
2038 if p_yea_info.taxable_income > ( p_yea_info.donation1_tax_exem
2039 + p_yea_info.donation3_tax_exem
2040 + p_yea_info.esoa_don_tax_exem2004 ) then
2041 p_yea_info.max_donation2_tax_exem := trunc(
2042 (p_yea_info.taxable_income - p_yea_info.donation1_tax_exem
2043 - p_yea_info.donation3_tax_exem
2044 - p_yea_info.esoa_don_tax_exem2004 ) * l_donation2_tax_exem_per);
2045 l_max_donation2_dummy := p_yea_info.max_donation2_tax_exem;
2046 --
2047 -- Loop for Specified Donation
2048 --
2049 l_spec_tax_exem := 0;
2050 --
2051 for i in 1..p_yea_info.spec_amt_sub_to_exem_tbl.count loop
2052 if (chk_valid_donation(p_yea_info.spec_donated_year_tbl(i),'40',p_effective_date) = 'Y') then
2053 l_spec_tax_exem := least(p_yea_info.spec_amt_sub_to_exem_tbl(i), l_max_donation2_dummy, l_actual_max_donation_dummy);
2054 else
2055 l_spec_tax_exem := 0;
2056 end if;
2057 p_yea_info.donation2_tax_exem := p_yea_info.donation2_tax_exem + l_spec_tax_exem;
2058 l_max_donation2_dummy := greatest(0,l_max_donation2_dummy - l_spec_tax_exem);
2059 l_actual_max_donation_dummy := greatest(0, l_actual_max_donation_dummy - l_spec_tax_exem);
2060 insert_donation_details(p_assignment_id,
2061 p_effective_date,
2062 p_yea_info.spec_asg_info_id_tbl(i),
2063 l_spec_tax_exem,
2064 p_yea_info.spec_amt_sub_to_exem_tbl(i) - l_spec_tax_exem);
2065 end loop;
2066 --
2067 end if;
2068 --
2069 end if;
2070 --
2071 end if;
2072 --
2073 -- Now calculate total political donation income exemption
2074 p_yea_info.donation_tax_exem := p_yea_info.donation1_tax_exem + p_yea_info.donation2_tax_exem + p_yea_info.donation3_tax_exem + p_yea_info.esoa_don_tax_exem2004 ;
2075 --
2076 if g_debug then
2077 hr_utility.trace('Total Statutory + Political Donation = '||to_char(p_yea_info.donation1_tax_exem));
2078 hr_utility.trace('Total PLEDT + Tax Reduction Law = '||to_char(p_yea_info.donation3_tax_exem));
2079 hr_utility.trace('Total Specified + Religious Donation = '||to_char(p_yea_info.donation2_tax_exem));
2080 hr_utility.trace('Total ESOA Donation = '||to_char(p_yea_info.esoa_don_tax_exem2004));
2081 end if;
2082 --
2083 --
2084 -- Bug 4119483: Apply upper limit based on cumulative special exemption
2085 p_yea_info.donation_tax_exem := least(
2086 p_yea_info.donation_tax_exem,
2087 greatest(
2088 0,
2089 p_yea_info.taxation_base)
2090 ) ;
2091 --
2092 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.donation_tax_exem ;
2093 --
2094 --
2095 check_donation_details(p_assignment_id,
2096 p_effective_date);
2097 end if;
2098 --
2099 end if;
2100 --
2101 label := label + 1;
2102 end loop;
2103 -- End of Bug 9393732
2104 ----------------------------------------------------------------
2105 -- Taxation Base
2106 ----------------------------------------------------------------
2107 p_yea_info.taxation_base := greatest(p_yea_info.taxation_base, 0);
2108 ----------------------------------------------------------------
2109 -- Calculated Tax
2110 ----------------------------------------------------------------
2111 p_yea_info.calc_tax := calc_tax(p_yea_info.taxation_base);
2112 ----------------------------------------------------------------
2113 -- Basic Tax Break
2114 -- This tax break is based on "Estimated Calculated Tax
2115 -- based on Taxable Earnings without Special Irregular Bonus".
2116 ----------------------------------------------------------------
2117 if p_yea_info.calc_tax > 0 and p_yea_info.taxable > 0 then
2118 l_dummy := trunc( p_yea_info.calc_tax);
2119 else
2120 l_dummy := 0;
2121 end if;
2122 --
2123 l_available_tax_break := p_yea_info.calc_tax;
2124 --
2125
2126 if l_dummy > 0 then
2127 -- Bug 9231094
2128 if p_effective_date > c_20120101 then
2129 l_basic_tax_break := to_number(hruserdt.get_table_value(
2130 p_bus_group_id => p_business_group_id,
2131 p_table_name => 'MAX_BASIC_TAX_BREAK',
2132 p_col_name => 'VALUE',
2133 p_row_value => to_char(p_yea_info.taxable),
2134 p_effective_date => p_effective_date));
2135 end if;
2136
2137 l_addend := to_number(hruserdt.get_table_value(
2138 p_bus_group_id => p_business_group_id,
2139 p_table_name => 'BASIC_TAX_BREAK',
2140 p_col_name => 'ADDEND',
2141 p_row_value => to_char(l_dummy),
2142 p_effective_date => p_effective_date));
2143 l_subtrahend := to_number(hruserdt.get_table_value(
2144 p_bus_group_id => p_business_group_id,
2145 p_table_name => 'BASIC_TAX_BREAK',
2146 p_col_name => 'SUBTRAHEND',
2147 p_row_value => to_char(l_dummy),
2148 p_effective_date => p_effective_date));
2149 l_multiplier := to_number(hruserdt.get_table_value(
2150 p_bus_group_id => p_business_group_id,
2151 p_table_name => 'BASIC_TAX_BREAK',
2152 p_col_name => 'MULTIPLIER',
2153 p_row_value => to_char(l_dummy),
2154 p_effective_date => p_effective_date));
2155 p_yea_info.basic_tax_break := least(trunc(l_addend + trunc((l_dummy - l_subtrahend) * l_multiplier / 100)), l_basic_tax_break);
2156
2157 --
2158 p_yea_info.basic_tax_break := least(p_yea_info.basic_tax_break, l_available_tax_break);
2159 l_available_tax_break := l_available_tax_break - p_yea_info.basic_tax_break;
2160 --
2161 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.basic_tax_break;
2162 end if;
2163 --
2164 ----------------------------------------------------------------
2165 -- Bug 7361372: Type B Tax Group Tax Break
2166 ----------------------------------------------------------------
2167 if p_yea_info.calc_tax > 0 and p_yea_info.taxable > 0 then
2168 if p_yea_info.tax_grp_taxable > 0 then
2169 p_yea_info.tax_grp_post_tax_deduc := (p_yea_info.calc_tax * (p_yea_info.tax_grp_taxable / p_yea_info.taxable)) * l_type_B_tax_group_pct;
2170 --
2171 p_yea_info.tax_grp_post_tax_deduc := least(p_yea_info.tax_grp_post_tax_deduc,l_available_tax_break);
2172 l_available_tax_break := l_available_tax_break - p_yea_info.tax_grp_post_tax_deduc;
2173 --
2174 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.tax_grp_post_tax_deduc;
2175 else
2176 p_yea_info.tax_grp_post_tax_deduc := 0;
2177 end if;
2178 end if;
2179 --
2180 ----------------------------------------------------------------
2181 if p_yea_info.non_resident_flag = 'N' then
2182 --
2183 ----------------------------------------------------------------
2184 -- Housing Expense Tax Break
2185 ----------------------------------------------------------------
2186 if p_yea_info.housing_loan_interest_repay > 0 then
2187 p_yea_info.housing_exp_tax_break := trunc(p_yea_info.housing_loan_interest_repay * l_housinloan_int_repay_per);
2188 /* Need the actual housing loan interest tax break for special tax calculation */
2189 if p_yea_info.total_tax_break < p_yea_info.calc_tax then
2190 l_net_housing_exp_tax_break := least(p_yea_info.housing_exp_tax_break, p_yea_info.calc_tax - p_yea_info.total_tax_break);
2191 end if;
2192 --
2193 p_yea_info.housing_exp_tax_break := least(p_yea_info.housing_exp_tax_break, l_available_tax_break);
2194 l_available_tax_break := l_available_tax_break - p_yea_info.housing_exp_tax_break;
2195 --
2196 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.housing_exp_tax_break;
2197 end if;
2198 end if;
2199 ----------------------------------------------------------------
2200 -- Overseas Tax Break
2201 ----------------------------------------------------------------
2202 if p_yea_info.ovstb_tax_paid_date is not null then
2203 if p_yea_info.taxable_income > 0 then
2204 --
2205 open csr_ovs_def_bal_id;
2206 fetch csr_ovs_def_bal_id into l_ovs_def_bal_id;
2207 --
2208 if csr_ovs_def_bal_id%found then
2209 --
2210 l_ovs_earnings_bal:= pay_balance_pkg.get_value
2211 (p_defined_balance_id => l_ovs_def_bal_id
2212 ,p_assignment_id => p_assignment_id
2213 ,p_virtual_date => p_effective_date
2214 ,p_always_get_db_item => FALSE);
2215 end if;
2216 close csr_ovs_def_bal_id;
2217 --
2218 p_yea_info.ovstb_taxable := p_yea_info.ovstb_taxable + l_ovs_earnings_bal;
2219 l_dummy := trunc(p_yea_info.calc_tax * greatest((p_yea_info.ovstb_taxable - trunc(p_yea_info.ovstb_taxable_subj_tax_break * p_yea_info.ovstb_tax_break_rate / 100)), 0) / p_yea_info.taxable);
2220 p_yea_info.ovs_tax_break := least(p_yea_info.ovstb_tax, l_dummy);
2221 --
2222 p_yea_info.ovs_tax_break := least(p_yea_info.ovs_tax_break, l_available_tax_break);
2223 l_available_tax_break := l_available_tax_break - p_yea_info.ovs_tax_break;
2224 --
2225 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.ovs_tax_break;
2226 end if;
2227 end if;
2228 ----------------------------------------------------------------
2229 -- Political Donation Tax Break
2230 -- Bug 3966549
2231 ----------------------------------------------------------------
2232 if p_yea_info.non_resident_flag = 'N' then
2233 p_yea_info.don_tax_break2004 := least(p_yea_info.don_tax_break2004, l_available_tax_break) ;
2234 --
2235 l_available_tax_break := l_available_tax_break - p_yea_info.don_tax_break2004 ;
2236 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.don_tax_break2004 ;
2237 end if ;
2238 -- End of 3966549
2239 ----------------------------------------------------------------
2240 -- Foreign Worker Tax Break and set Annual Tax to "0"
2241 ----------------------------------------------------------------
2242 -- Bug 5083240
2243 p_yea_info.foreign_worker_tax_break := 0;
2244 if p_yea_info.nationality = 'F'
2245 and p_yea_info.fixed_tax_rate = 'N'
2246 and p_yea_info.fwtb_immigration_purpose is not null then
2247 l_fw_contr_fw_inc_exem := 0 ;
2248 l_fw_contr_taxable := 0 ;
2249 l_fw_contr_basic_exem := 0 ;
2250 l_fw_brk_proration_factor := 0 ;
2251
2252 l_fw_contr_taxable := trunc(p_yea_info.fw_contr_taxable_earn);
2253 -- Bug 9231094
2254 l_fw_tax_reduction_rate := get_globalvalue ('KR_FW_TAX_REDUCTION_RATE',p_yea_info.fwtb_contract_date);
2255 /* Bug 9231094 -- Deduct foreign worker income exemption
2256 l_fw_contr_fw_inc_exem := greatest(trunc(((p_yea_info.fw_contr_taxable_earn + p_yea_info.fw_contr_non_taxable_earn) * l_fw_income_exem_rate), 0), 0) ;
2257 if l_fw_contr_fw_inc_exem > 0 then
2258 l_fw_contr_taxable := greatest(p_yea_info.fw_contr_taxable_earn - l_fw_contr_fw_inc_exem, 0) ;
2259 p_yea_info.fw_contr_non_taxable_earn := p_yea_info.fw_contr_non_taxable_earn + l_fw_contr_fw_inc_exem ;
2260 end if ; */
2261 -- Deduct basic exemption on taxable income calculated till now
2262 if l_fw_contr_taxable > 0 then
2263 l_addend := to_number(hruserdt.get_table_value(
2264 p_bus_group_id => p_business_group_id,
2265 p_table_name => 'BASIC_INCOME_EXEM',
2266 p_col_name => 'ADDEND',
2267 p_row_value => to_char(l_fw_contr_taxable),
2268 p_effective_date => p_effective_date));
2269 l_multiplier := to_number(hruserdt.get_table_value(
2270 p_bus_group_id => p_business_group_id,
2271 p_table_name => 'BASIC_INCOME_EXEM',
2272 p_col_name => 'MULTIPLIER',
2273 p_row_value => to_char(l_fw_contr_taxable),
2274 p_effective_date => p_effective_date));
2275 l_subtrahend := to_number(hruserdt.get_table_value(
2276 p_bus_group_id => p_business_group_id,
2277 p_table_name => 'BASIC_INCOME_EXEM',
2278 p_col_name => 'SUBTRAHEND',
2279 p_row_value => to_char(l_fw_contr_taxable),
2280 p_effective_date => p_effective_date));
2281
2282 l_fw_contr_basic_exem := l_addend + trunc(l_multiplier/100 * (l_fw_contr_taxable - l_subtrahend)) ;
2283 l_fw_contr_taxable := l_fw_contr_taxable - l_fw_contr_basic_exem ;
2284 end if ;
2285 -- Now calculate the proration factor
2286 l_fw_brk_proration_factor := (l_fw_contr_taxable/p_yea_info.taxable_income) ;
2287
2288 -- Prorate tax breaks
2289 -- Bug 9231094
2290 if p_yea_info.fwtb_contract_date < c_20100101 then
2291 p_yea_info.foreign_worker_tax_break := greatest(trunc((p_yea_info.calc_tax * l_fw_brk_proration_factor), 0), 0);
2292 if p_yea_info.fwtb_immigration_purpose = 'G' then
2293 p_yea_info.foreign_worker_tax_break1 := p_yea_info.foreign_worker_tax_break;
2294 else
2295 p_yea_info.foreign_worker_tax_break2 := p_yea_info.foreign_worker_tax_break;
2296 end if;
2297
2298 else
2299 if p_yea_info.fwtb_immigration_purpose = 'G' then
2300 p_yea_info.foreign_worker_tax_break := 0;
2301 else
2302 p_yea_info.foreign_worker_tax_break := greatest(trunc((p_yea_info.calc_tax * l_fw_brk_proration_factor * l_fw_tax_reduction_rate), 0), 0);
2303 p_yea_info.foreign_worker_tax_break2 := p_yea_info.foreign_worker_tax_break;
2304 end if;
2305 end if;
2306 p_yea_info.cur_ntax_T01 := nvl(p_yea_info.foreign_worker_tax_break1,0) + nvl(p_yea_info.foreign_worker_tax_break2,0); -- Bug 9737699
2307 end if;
2308
2309 if p_yea_info.foreign_worker_tax_break < p_yea_info.calc_tax then
2310
2311 ----------------------------------------------------------------
2312 -- Annual Tax
2313 ----------------------------------------------------------------
2314 if p_yea_info.total_tax_break > (p_yea_info.calc_tax - p_yea_info.foreign_worker_tax_break) then
2315 p_yea_info.total_tax_break := p_yea_info.calc_tax - p_yea_info.foreign_worker_tax_break;
2316 end if;
2317 --
2318 p_yea_info.annual_itax := trunc(greatest(p_yea_info.calc_tax - p_yea_info.total_tax_break - p_yea_info.foreign_worker_tax_break, 0),0);
2319 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per,0);
2320 --
2321 -- bug 12778151
2322 /* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
2323 l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base + nvl(p_yea_info.invest_partner_fin_tax_exem,0));
2324 --
2325 if l_calc_tax_for_stax > 0 then
2326 p_yea_info.annual_stax := trunc((l_calc_tax_for_stax - p_yea_info.calc_tax) * l_annual_stax_per);
2327 end if;
2328 if l_net_housing_exp_tax_break > 0 then
2329 p_yea_info.annual_stax := p_yea_info.annual_stax + trunc(l_net_housing_exp_tax_break * l_housinexp_tax_break);
2330 end if;
2331 p_yea_info.annual_stax := trunc(p_yea_info.annual_stax,0);
2332 else
2333 p_yea_info.annual_itax := greatest(p_yea_info.calc_tax - p_yea_info.foreign_worker_tax_break, 0) ;
2334 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per, 0) ;
2335 p_yea_info.annual_stax := 0;
2336 p_yea_info.basic_tax_break := 0;
2337 p_yea_info.housing_exp_tax_break := 0;
2338 p_yea_info.ovs_tax_break := 0;
2339 p_yea_info.don_tax_break2004 := 0;
2340 p_yea_info.total_tax_break := 0;
2341 end if;
2342 end if; -- nationality
2343 ----------------------------------------------------------------
2344 -- Calculate Tax Adjustment
2345 -- Truncating the last Won digit after the final calculation of annual_itax , annual_rtax and annual_stax.
2346 ----------------------------------------------------------------
2347 -- Bug 7676136: Added NVL clause for the Type B Tax Group fields
2348 p_yea_info.itax_adj := trunc(p_yea_info.annual_itax - p_yea_info.prev_itax - nvl(p_yea_info.tax_grp_itax,0) - p_yea_info.cur_itax,-1);
2349 p_yea_info.rtax_adj := trunc(p_yea_info.annual_rtax - p_yea_info.prev_rtax - nvl(p_yea_info.tax_grp_rtax,0) - p_yea_info.cur_rtax,-1);
2350 p_yea_info.stax_adj := trunc(p_yea_info.annual_stax - p_yea_info.prev_stax - nvl(p_yea_info.tax_grp_stax,0) - p_yea_info.cur_stax,-1);
2351 --
2352 if p_yea_info.itax_adj >= 0 and p_yea_info.itax_adj < 1000 then
2353 p_yea_info.itax_adj := 0;
2354 p_yea_info.rtax_adj := 0;
2355 p_yea_info.stax_adj := 0;
2356 p_tax_adj_warning := TRUE;
2357 end if;
2358 end yea;
2359 --
2360 end pay_kr_yea20100101_pkg;