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