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