[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA20070101_PKG
Source
1 package body pay_kr_yea20070101_pkg as
2 /* $Header: pykryea6.pkb 120.13 2011/08/29 05:28:52 keyazawa noship $ */
3 -- Constant
4 -- Bug 3966549
5 c_20040101 constant date := fnd_date.canonical_to_date('2004/01/01') ;
6 -- End of 3966549
7 --
8 -- Bug 3901912
9 l_ee_tax_exem number default 0;
10 l_dpnt_spouse_tax_exem number default 0;
11 l_dpnt_tax_exem number default 0;
12 l_aged_tax_exem number default 0;
13 -- Bug 3172960
14 l_aged_tax_exem1 number default 0;
15 l_fw_fixed_tax_rate number default 0;
16 l_fw_income_exem_rate number default 0;
17 --
18 l_disabled_tax_exem number default 0;
19 l_female_ee_tax_exem number default 0;
20 l_addl_child_tax_exem number default 0; -- Bug 5734313
21 l_addl_child_tax_exem1 number default 0; -- Bug 5734313
22 l_raising_child_tax_exem number default 0; -- Bug 5756690
23 l_pers_ins_prem_tax_exem number default 0;
24 l_disabled_ins_prem_tax_exem number default 0;
25 l_med_exp_tax_exem_per number default 0;
26 l_med_exp_tax_exem_lim number default 0;
27 -- Bug 3966549
28 l_total_med_exp number default 0 ;
29 -- End of 3966549
30 l_dpnt_educ_school_type_U number default 0;
31 l_dpnt_educ_school_type_H number default 0;
32 l_dpnt_educ_school_type_P number default 0;
33 l_dpnt_educ_school_type_D number default 0;
34 l_housinexp_tax_exem_per number default 0;
35 l_housinsavintype_HST1 number default 0;
36 l_housinsavintype_HST3 number default 0;
37 l_housinsavintype_HST4 number default 0;
38 l_housinexp_tax_exem_lim number default 0;
39 l_housinexp_tax_exem_lim1 number default 0;
40 l_housinexp_tax_exem_lim2 number default 0;
41 l_political_donation1_lim number default 0;
42 l_political_donation2_lim number default 0;
43 l_political_donation3_lim number default 0;
44 l_political_donation3_per number default 0;
45 l_donation2_tax_exem_per number default 0;
46 l_donation3_tax_exem_per number default 0;
47 -- Bug 3966549
48 l_political_tax_brk_lim2004 number default 0 ;
49 l_esoa_tax_exem_per2004 number default 0 ;
50 -- End of 3966549
51 l_marriage_exem_lim number default 0;
52 l_funeral_exem_lim number default 0;
53 l_relocation_exem_lim number default 0;
54 l_std_sp_tax_exem number default 0;
55 l_pers_pen_prem_tax_exem_per number default 0;
56 l_pers_pen_prem_tax_exem_lim number default 0;
57 l_pers_pen_savintax_exem_lim number default 0;
58 l_corp_pen_prem_tax_exem_lim number default 0; -- Bug : 4776711
59 l_emp_stock_own_plan_exem_lim number default 0;
60 l_cre_card_exp_tax_exem_per1 number default 0;
61 l_cre_card_exp_tax_exem_per2 number default 0;
62 l_dir_card_exp_tax_exem_per number default 0;
63 l_cre_card_exp_tax_exem_lim number default 0;
64 -- Bug 3966549
65 l_cre_card_tax_exem_per2004 number default 0 ;
66 -- End of 3966549
67 l_inv_part_fin1_tax_exem_per number default 0;
68 l_inv_part_fin2_tax_exem_per number default 0;
69 l_inv_part_fin1_tax_exem_lim number default 0;
70 l_inv_part_fin2_tax_exem_lim number default 0;
71 l_small_bus_install_lim number default 0; -- Bug 6895093
72 l_basic_tax_break number default 0;
73 l_stock_savintax_break_per number default 0;
74 l_lt_stk_sav1_tax_break_per number default 0;
75 l_lt_stk_sav2_tax_break_per number default 0;
76 l_housinloan_int_repay_per number default 0;
77 l_annual_itax_per number default 0;
78 l_annual_stax_per number default 0;
79 l_housinexp_tax_break number default 0;
80 l_global_value_flag boolean default False;
81 l_cuml_special_exem number default 0 ; -- Adds up all special exemptions given for tracking purpose, for limit on exemptions.
82 -- Bug 4119539
83 l_fw_inc_exem_res number default 0 ;
84 l_fw_temp number default 0 ;
85 -- End of 4119539
86 -- Bug 5083240
87 l_fw_contr_taxable number default 0 ; -- Foreign Worker taxable during contract; numerator for proration factor
88 l_fw_contr_fw_inc_exem number default 0 ; -- Foreign Worker Income Exemption on income during contract for foreign worker
89 l_fw_contr_basic_exem number default 0 ; -- Basic Exemption on taxable income during contract for foreign worker
90 l_fw_brk_proration_factor number default 0 ; -- Proration Factor (Taxable During Contract / Total Taxable)
91 -- End of 5083240
92 -- Bug 5682450
93 l_pol_don_exem_rate_factor number default 1; -- This factor reflects the change in calculation of tax exemption based on political donation.
94 -- End of 5682450
95 l_card_exp_not_exempted number default 0 ; -- Bug 6630135
96 l_double_exem_amt number default 0 ; -- Bug 6630135
97 --
98 procedure yea(
99 p_assignment_id in number,
100 p_effective_date in date,
101 p_business_group_id in number,
102 p_yea_info in out NOCOPY pay_kr_yea_pkg.t_yea_info,
103 p_tax_adj_warning out NOCOPY boolean)
104 is
105 -- Bug No 3901912
106 cursor csr_ovs_def_bal_id
107 is
108 SELECT pdb.defined_balance_id
109 FROM pay_balance_types pbt,
110 pay_defined_balances pdb,
111 pay_balance_dimensions pbd
112 WHERE pbt.balance_name like 'Overseas Earnings'
113 and pbt.legislation_code = 'KR'
114 and pbd.legislation_code = 'KR'
115 and pdb.legislation_code = 'KR'
116 and pbt.balance_type_id = pdb.balance_type_id
117 and pbd.balance_dimension_id = pdb.balance_dimension_id
118 and pbd.database_item_suffix = '_ASG_YTD';
119 --
120 l_ovs_earnings_bal number:=0;
121 l_ovs_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
122
123 l_addend number;
124 l_multiplier number;
125 l_subtrahend number;
126 l_dummy number;
127 l_net_housing_exp_tax_break number;
128 l_calc_tax_for_stax number;
129 --
130 -- Bug No 3901912 moved variables to package initialisation section
131
132 l_available_tax_break number default 0;
133 l_ameeg_med_exem number default 0;
134
135 function calc_tax(p_taxation_base in number) return number
136 is
137 begin
138 if p_taxation_base > 0 then
139 l_multiplier := to_number(hruserdt.get_table_value(
140 p_bus_group_id => p_business_group_id,
141 p_table_name => 'CALC_TAX',
142 p_col_name => 'MULTIPLIER',
143 p_row_value => to_char(p_taxation_base),
144 p_effective_date => p_effective_date));
145 l_subtrahend := to_number(hruserdt.get_table_value(
146 p_bus_group_id => p_business_group_id,
147 p_table_name => 'CALC_TAX',
148 p_col_name => 'SUBTRAHEND',
149 p_row_value => to_char(p_taxation_base),
150 p_effective_date => p_effective_date));
151 return trunc(p_taxation_base * l_multiplier / 100) - l_subtrahend;
152 else
153 return 0;
154 end if;
155 end calc_tax;
156 --
157 function get_globalvalue(p_glbvar in varchar2,p_process_date in date) return number
158 is
159 --
160 cursor csr_ff_global
161 is
162 select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
163 from ff_globals_f glb
164 where glb.global_name = p_glbvar
165 and p_process_date between glb.effective_start_date and glb.effective_end_date;
166 --
167 l_glbvalue number default 0;
168 begin
169 Open csr_ff_global;
170 fetch csr_ff_global into l_glbvalue;
171 close csr_ff_global;
172 --
173 if l_glbvalue is null then
174 l_glbvalue := 0;
175 end if;
176 --
177 return l_glbvalue;
178 end;
179 --
180 begin
181 ----------------------------------------------------------------------------+
182 -- Populating Local variables with Global values
183 ----------------------------------------------------------------------------+
184 -- Bug no 3901912
185 if l_global_value_flag <> TRUE then
186 l_ee_tax_exem := get_globalvalue('KR_YEA_EE_TAX_EXEM',p_effective_date);
187 l_dpnt_spouse_tax_exem := get_globalvalue('KR_YEA_DPNT_SPOUSE_TAX_EXEM',p_effective_date);
188 l_dpnt_tax_exem := get_globalvalue('KR_YEA_DPNT_TAX_EXEM',p_effective_date);
189 l_aged_tax_exem := get_globalvalue('KR_YEA_AGED_TAX_EXEM',p_effective_date);
190 -- Bug 3172960
191 l_aged_tax_exem1 := get_globalvalue('KR_YEA_AGED_TAX_EXEM1',p_effective_date);
192 --
193 l_disabled_tax_exem := get_globalvalue('KR_YEA_DISABLED_TAX_EXEM',p_effective_date);
194 l_female_ee_tax_exem := get_globalvalue('KR_YEA_FEMALE_EE_TAX_EXEM',p_effective_date);
195 l_addl_child_tax_exem := get_globalvalue('CHILD_TAX_EXEM',p_effective_date); -- Bug 5734313
196 l_addl_child_tax_exem1 := get_globalvalue('CHILD_TAX_EXEM1',p_effective_date); -- Bug 5734313
197 l_raising_child_tax_exem := get_globalvalue('KR_YEA_NUM_OF_CHILDREN',p_effective_date); -- Bug 5756690
198 l_pers_ins_prem_tax_exem := get_globalvalue('KR_YEA_PERS_INS_PREM_TAX_EXEM',p_effective_date);
199 l_disabled_ins_prem_tax_exem := get_globalvalue('KR_YEA_DISABLED_INS_PREM_TAX_EXEM',p_effective_date);
200 l_med_exp_tax_exem_per := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_PER',p_effective_date);
201 l_med_exp_tax_exem_lim := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_LIM',p_effective_date);
202 l_dpnt_educ_school_type_U := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_U',p_effective_date);
203 l_dpnt_educ_school_type_H := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_H',p_effective_date);
204 l_dpnt_educ_school_type_P := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_P',p_effective_date);
205 l_dpnt_educ_school_type_D := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_D',p_effective_date);
206 l_housinexp_tax_exem_per := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_PER',p_effective_date);
207 l_housinsavintype_HST1 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST1',p_effective_date);
208 -- Bug 5731165
209 -- Removed code for HST2
210 l_housinsavintype_HST3 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST3',p_effective_date);
211 l_housinsavintype_HST4 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST4',p_effective_date);
212 l_housinexp_tax_exem_lim := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM',p_effective_date);
213 l_housinexp_tax_exem_lim1 := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM1',p_effective_date);
214 l_housinexp_tax_exem_lim2 := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM2',p_effective_date);
215 l_political_donation1_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION1_LIM',p_effective_date);
216 l_political_donation2_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION2_LIM',p_effective_date);
217 l_political_donation3_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION3_LIM',p_effective_date);
218 l_political_donation3_per := get_globalvalue('KR_YEA_POLITICAL_DONATION3_PER',p_effective_date);
219 l_donation2_tax_exem_per := get_globalvalue('KR_YEA_DONATION2_TAX_EXEM_PER',p_effective_date);
220 l_donation3_tax_exem_per := get_globalvalue('KR_YEA_DONATION3_TAX_EXEM_PER',p_effective_date);
221 -- Bug 3966549
222 l_political_tax_brk_lim2004 := get_globalvalue('KR_YEA_POLITICAL_DONATION_TAX_BREAK_LIM', p_effective_date) ;
223 l_esoa_tax_exem_per2004 := get_globalvalue('KR_YEA_ESOA_DONATION_TAX_EXEM_PER', p_effective_date) ;
224 -- End of 3966549
225
226 l_marriage_exem_lim := get_globalvalue('KR_YEA_MARRIAGE_EXEM_LIM',p_effective_date);
227 l_funeral_exem_lim := get_globalvalue('KR_YEA_FUNERAL_EXEM_LIM',p_effective_date);
228 l_relocation_exem_lim := get_globalvalue('KR_YEA_RELOCATION_EXEM_LIM',p_effective_date);
229 l_std_sp_tax_exem := get_globalvalue('KR_YEA_STD_SP_TAX_EXEM',p_effective_date);
230 l_pers_pen_prem_tax_exem_per := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_PER',p_effective_date);
231 l_pers_pen_prem_tax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_LIM',p_effective_date);
232 -- Bug : 4776711
233 l_corp_pen_prem_tax_exem_lim := get_globalvalue('KR_YEA_CORP_PENSION_PREM_TAX_EXEM_LIM',p_effective_date);
234 -- End of Bug 4776711
235 l_pers_pen_savintax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_SAVINTAX_EXEM_LIM',p_effective_date);
236 l_emp_stock_own_plan_exem_lim := get_globalvalue('KR_YEA_EMP_STOCK_OWN_PLAN_EXEM_LIM',p_effective_date);
237 l_cre_card_exp_tax_exem_per1 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER1',p_effective_date);
238 l_cre_card_exp_tax_exem_per2 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER2',p_effective_date);
239 l_dir_card_exp_tax_exem_per := get_globalvalue('KR_YEA_DIRECT_CARD_EXP_TAX_EXEM_PER',p_effective_date);
240 l_cre_card_exp_tax_exem_lim := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_LIM',p_effective_date);
241 -- Bug 3966549
242 l_cre_card_tax_exem_per2004 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_2004_TAX_EXEM_PER',p_effective_date);
243 -- End of 3966549
244 l_inv_part_fin1_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN1_TAX_EXEM_PER',p_effective_date);
245 l_inv_part_fin2_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_PER',p_effective_date);
246 l_inv_part_fin1_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN1_TAX_EXEM_LIM',p_effective_date);
247 l_inv_part_fin2_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_LIM',p_effective_date);
248 -- Bug 6895093
249 l_small_bus_install_lim := get_globalvalue('KR_YEA_SMALL_BUS_INSTALL_LIM',p_effective_date);
250 -- End of Bug 6895093
251 l_basic_tax_break := get_globalvalue('KR_YEA_BASIC_TAX_BREAK',p_effective_date);
252 l_stock_savintax_break_per := get_globalvalue('KR_YEA_STOCK_SAVINTAX_BREAK_PER',p_effective_date);
253 l_lt_stk_sav1_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING1_TAX_BREAK_PER',p_effective_date);
254 l_lt_stk_sav2_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING2_TAX_BREAK_PER',p_effective_date);
255 l_housinloan_int_repay_per := get_globalvalue('KR_YEA_HOUSINLOAN_INTEREST_REPAY_PER',p_effective_date);
256 l_annual_itax_per := get_globalvalue('KR_YEA_ANNUAL_ITAX_PER',p_effective_date);
257 l_annual_stax_per := get_globalvalue('KR_YEA_ANNUAL_STAX_PER',p_effective_date);
258 l_housinexp_tax_break := get_globalvalue('KR_YEA_HOUSINEXP_TAX_BREAK',p_effective_date);
259 -- Bug 3172960
260 l_fw_income_exem_rate := get_globalvalue('KR_FOREIGN_WORKER_INCOME_EXEM_RATE',p_effective_date);
261 l_fw_fixed_tax_rate := get_globalvalue('KR_FOREIGN_WORKER_FIXED_TAX_RATE',p_effective_date);
262 --
263 -- Bug 5682450
264 l_pol_don_exem_rate_factor := get_globalvalue ('KR_YEA_POLITICAL_DONATION_EXEM_FAC', p_effective_date);
265 -- End of 5682450
266 l_global_value_flag := TRUE;
267 End if;
268
269 l_card_exp_not_exempted := 0 ; -- Bug 6763554
270 --
271 p_tax_adj_warning := false;
272 -- Bug 3172960
273 if (p_yea_info.nationality = 'F' and p_yea_info.fixed_tax_rate = 'Y') then
274 --
275 p_yea_info.taxable := p_yea_info.taxable + p_yea_info.non_taxable;
276 p_yea_info.taxable_income := p_yea_info.taxable;
277 p_yea_info.taxable_income2 := p_yea_info.taxable;
278 p_yea_info.taxation_base := p_yea_info.taxable;
279 --
280 p_yea_info.calc_tax := greatest(trunc(p_yea_info.taxation_base * l_fw_fixed_tax_rate),0);
281 --
282 p_yea_info.num_of_dpnts := 0;
283 p_yea_info.num_of_ageds := 0;
284 p_yea_info.num_of_super_ageds := 0;
285 p_yea_info.num_of_disableds := 0;
286 p_yea_info.num_of_children := 0;
287 --
288 -- Bug 4149947: Add total non taxable into current monthly earnings, and respective totals
289 p_yea_info.cur_taxable_mth := p_yea_info.cur_taxable_mth + p_yea_info.non_taxable ;
290 p_yea_info.cur_taxable := p_yea_info.cur_taxable + p_yea_info.non_taxable ;
291 p_yea_info.taxable_mth := p_yea_info.taxable_mth + p_yea_info.non_taxable ;
292 -- End of 4149947
293 --
294 -- Bug 4156004
295 p_yea_info.non_taxable_ovs := 0 ;
296 p_yea_info.non_taxable_ovt := 0 ;
297 -- End of 4156004
298 --
299 -- Bug 4119539: Make all non-taxables = 0
300 p_yea_info.non_taxable_oth := 0 ;
301 p_yea_info.non_taxable := 0 ;
302 -- End of 4119539
303 --
304 -- Bug 5083240: Removed logic to calculate tax break, as it is
305 -- not applicable to fixed rate cases
306 ----------------------------------------------------------------
307 -- Annual Tax
308 ----------------------------------------------------------------
309 p_yea_info.annual_itax := greatest(p_yea_info.calc_tax, 0);
310 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per,0);
311 else
312 -- Bug 3172960
313 if p_yea_info.nationality = 'F' then
314 -- Bug 6666723
315 p_yea_info.foreign_worker_income_exem := greatest(trunc(p_yea_info.taxable * l_fw_income_exem_rate),0);
316 --
317 if p_yea_info.foreign_worker_income_exem > 0 then
318 p_yea_info.taxable := greatest(p_yea_info.taxable - p_yea_info.foreign_worker_income_exem, 0);
319 --
320 -- Bug 4119539
321 l_fw_inc_exem_res := p_yea_info.foreign_worker_income_exem ;
322 p_yea_info.non_taxable_oth := p_yea_info.non_taxable_oth + p_yea_info.foreign_worker_income_exem ;
323 p_yea_info.non_taxable := p_yea_info.non_taxable + p_yea_info.foreign_worker_income_exem ;
324 l_fw_inc_exem_res := p_yea_info.foreign_worker_income_exem ;
325 --
326 l_fw_temp := greatest(p_yea_info.cur_taxable_mth - l_fw_inc_exem_res, 0) ;
327 l_fw_inc_exem_res := greatest(l_fw_inc_exem_res - p_yea_info.cur_taxable_mth, 0) ;
328 p_yea_info.taxable_mth := greatest(p_yea_info.taxable_mth + l_fw_temp - p_yea_info.cur_taxable_mth, 0) ;
329 p_yea_info.cur_taxable_mth := l_fw_temp ;
330 --
331 l_fw_temp := greatest(p_yea_info.cur_taxable_bon - l_fw_inc_exem_res, 0) ;
332 l_fw_inc_exem_res := greatest(l_fw_inc_exem_res - p_yea_info.cur_taxable_bon, 0) ;
333 p_yea_info.taxable_bon := greatest(p_yea_info.taxable_bon + l_fw_temp - p_yea_info.cur_taxable_bon, 0) ;
334 p_yea_info.cur_taxable_bon := l_fw_temp ;
335 --
336 l_fw_temp := greatest(p_yea_info.cur_sp_irreg_bonus - l_fw_inc_exem_res, 0) ;
337 p_yea_info.sp_irreg_bonus := greatest(p_yea_info.sp_irreg_bonus + l_fw_temp - p_yea_info.cur_sp_irreg_bonus, 0) ;
338 p_yea_info.cur_sp_irreg_bonus := l_fw_temp ;
339 --
340 p_yea_info.cur_taxable := greatest(p_yea_info.cur_taxable - p_yea_info.foreign_worker_income_exem, 0) ;
341 --
342 -- End of 4119539
343 end if;
344 end if;
345 ----------------------------------------------------------------
346 -- Basic Income Exemption
347 -- Taxable Income
348 ----------------------------------------------------------------
349 if p_yea_info.taxable > 0 then
350 l_addend := to_number(hruserdt.get_table_value(
351 p_bus_group_id => p_business_group_id,
352 p_table_name => 'BASIC_INCOME_EXEM',
353 p_col_name => 'ADDEND',
354 p_row_value => to_char(p_yea_info.taxable),
355 p_effective_date => p_effective_date));
356 l_multiplier := to_number(hruserdt.get_table_value(
357 p_bus_group_id => p_business_group_id,
358 p_table_name => 'BASIC_INCOME_EXEM',
359 p_col_name => 'MULTIPLIER',
360 p_row_value => to_char(p_yea_info.taxable),
361 p_effective_date => p_effective_date));
362 l_subtrahend := to_number(hruserdt.get_table_value(
363 p_bus_group_id => p_business_group_id,
364 p_table_name => 'BASIC_INCOME_EXEM',
365 p_col_name => 'SUBTRAHEND',
366 p_row_value => to_char(p_yea_info.taxable),
367 p_effective_date => p_effective_date));
368 p_yea_info.basic_income_exem := l_addend + trunc(l_multiplier / 100 * (p_yea_info.taxable - l_subtrahend));
369 p_yea_info.taxable_income := p_yea_info.taxable - p_yea_info.basic_income_exem;
370 end if;
371 p_yea_info.taxable_income2 := p_yea_info.taxable_income;
372 ----------------------------------------------------------------
373 -- Employee Tax Exemption
374 ----------------------------------------------------------------
375 p_yea_info.ee_tax_exem := l_ee_tax_exem;
376 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.ee_tax_exem;
377 ----------------------------------------------------------------
378 -- Dependent Tax Exemption
379 ----------------------------------------------------------------
380 if p_yea_info.dpnt_spouse_flag = 'Y' then
381 p_yea_info.dpnt_spouse_tax_exem := l_dpnt_spouse_tax_exem;
382 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_spouse_tax_exem;
383 end if;
384 if p_yea_info.num_of_dpnts > 0 then
385 p_yea_info.dpnt_tax_exem := p_yea_info.num_of_dpnts * l_dpnt_tax_exem;
386 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_tax_exem;
387 end if;
388 if p_yea_info.num_of_ageds > 0 then
389 p_yea_info.aged_tax_exem := p_yea_info.num_of_ageds * l_aged_tax_exem;
390 end if;
391 -- Bug 3172960
392 if p_yea_info.num_of_super_ageds > 0 then
393 p_yea_info.aged_tax_exem := p_yea_info.aged_tax_exem + p_yea_info.num_of_super_ageds * l_aged_tax_exem1;
394 end if;
395 p_yea_info.num_of_ageds := nvl(p_yea_info.num_of_ageds, 0) + nvl(p_yea_info.num_of_super_ageds, 0);
396 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.aged_tax_exem;
397 --
398 if p_yea_info.num_of_disableds > 0 then
399 p_yea_info.disabled_tax_exem := p_yea_info.num_of_disableds * l_disabled_tax_exem;
400 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.disabled_tax_exem;
401 end if;
402 if p_yea_info.female_ee_flag = 'Y' then
403 p_yea_info.female_ee_tax_exem := l_female_ee_tax_exem;
404 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.female_ee_tax_exem;
405 end if;
406 -- Bug 5756690
407 if p_yea_info.num_of_children > 0 then
408 p_yea_info.child_tax_exem := p_yea_info.num_of_children * l_raising_child_tax_exem;
409 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.child_tax_exem;
410 end if;
411 -- End of Bug 5756690
412 -- Bug 5734313
413 -- Bug 6784288
414 if p_yea_info.num_of_addtl_child > 2 then
415 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);
416 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.addl_child_tax_exem;
417 elsif p_yea_info.num_of_addtl_child = 2 then
418 p_yea_info.addl_child_tax_exem := l_addl_child_tax_exem;
419 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.addl_child_tax_exem;
420 end if;
421 --
422 ----------------------------------------------------------------
423 -- Supplemental Tax Exemption
424 ----------------------------------------------------------------
425 /* Bug 5734313 : Obsoleted code for Supplemental Tax Exemption */
426 ----------------------------------------------------------------
427 -- Special Tax Exemption
428 ----------------------------------------------------------------
429 l_cuml_special_exem := 0; -- Bug 4709683
430
431 if p_yea_info.non_resident_flag = 'N' then
432 --
433 -- Insurance Premium Tax Exemption
434 --
435 if p_yea_info.hi_prem > 0 then
436 p_yea_info.hi_prem_tax_exem := p_yea_info.hi_prem;
437 end if;
438 if p_yea_info.ei_prem > 0 then
439 p_yea_info.ei_prem_tax_exem := p_yea_info.ei_prem;
440 end if;
441 if p_yea_info.pers_ins_prem > 0 then
442 p_yea_info.pers_ins_prem_tax_exem := least(p_yea_info.pers_ins_prem, l_pers_ins_prem_tax_exem );
443 end if;
444 if p_yea_info.disabled_ins_prem > 0 then
445 p_yea_info.disabled_ins_prem_tax_exem := least(p_yea_info.disabled_ins_prem, l_disabled_ins_prem_tax_exem);
446 end if;
447 p_yea_info.ins_prem_tax_exem := p_yea_info.hi_prem_tax_exem
448 + p_yea_info.ei_prem_tax_exem
449 + p_yea_info.pers_ins_prem_tax_exem
450 + p_yea_info.disabled_ins_prem_tax_exem;
451
452 -- Bug 4119483: Apply upper limit based on cumulative special exemption
453 p_yea_info.ins_prem_tax_exem := least(
454 p_yea_info.ins_prem_tax_exem,
455 greatest(
456 0,
457 p_yea_info.taxable_income2 - l_cuml_special_exem
458 )
459 ) ;
460 -- End of 4119483
461
462 -- Bug 3971542
463 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.ins_prem_tax_exem ;
464 -- End of 3971542
465 --
466
467 ----------------------------------------------------------------
468 -- Medical Expense Tax Exemption
469 -- Bug 3172960
470 -- Bug 3966549: Assigned new fields
471 -- p_yea_info.reg_med_exp_tax_exem2004, and
472 -- p_yea_info.add_med_exp_tax_exem2004.
473 -- Changed calculation for reg. and additional
474 -- medical exp. tax exemption.
475 ----------------------------------------------------------------
476 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;
477 l_ameeg_med_exem := p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged + p_yea_info.med_exp_emp;
478 --
479 if l_total_med_exp > 0 then
480 -- Calculate reg. med. expense exem.
481 l_dummy := l_total_med_exp - trunc(greatest(p_yea_info.taxable, 0) * l_med_exp_tax_exem_per) ;
482 if l_dummy > 0 then
483 -- Calculate reg. med. exp. exem.
484 p_yea_info.reg_med_exp_tax_exem2004 := least(l_dummy, l_med_exp_tax_exem_lim) ;
485 -- To calculate additional med. exp. exem.
486 l_dummy := l_dummy - l_med_exp_tax_exem_lim ;
487 else
488 p_yea_info.reg_med_exp_tax_exem2004 := 0 ;
489 end if;
490 if l_dummy < 0 then
491 l_dummy := 0 ;
492 end if ;
493 -- Calculate additional med. exp. exem.
494 p_yea_info.add_med_exp_tax_exem2004 := trunc( least(l_ameeg_med_exem, l_dummy) ) ;
495 p_yea_info.med_exp_tax_exem := p_yea_info.reg_med_exp_tax_exem2004 + p_yea_info.add_med_exp_tax_exem2004 ;
496 -- Bug 4119483: Apply upper limit based on cumulative special exemption
497 p_yea_info.med_exp_tax_exem := least(
498 p_yea_info.med_exp_tax_exem,
499 greatest(
500 0,
501 p_yea_info.taxable_income2 - l_cuml_special_exem
502 )
503 ) ;
504 -- End of 4119483
505
506 -- Start of Bug 6630135
507 l_card_exp_not_exempted :=0;
508 if p_yea_info.tot_med_exp_cards > 0 then
509 l_card_exp_not_exempted := l_total_med_exp - p_yea_info.med_exp_tax_exem;
510 end if;
511 -- End of Bug 6630135
512
513 -- Bug 3971542
514 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.med_exp_tax_exem ;
515 -- End of 3971542
516 end if;
517 -- End of 3966549
518 --
519 ----------------------------------------------------------------
520 -- Education Expense Tax Exemption
521 ----------------------------------------------------------------
522 p_yea_info.educ_exp_tax_exem := p_yea_info.ee_educ_exp;
523
524 -- Bug 4119483: Removed limit check on occupational education expense as it is not considered/displayed
525 -- individually anywhere.
526 -- Also, p_yea_info.educ_exp_tax_exem may still change. Hence, updated value of educ_exp_tax_exem
527 -- would be added to l_cuml_special_exem later.
528 --
529 p_yea_info.educ_exp_tax_exem := p_yea_info.educ_exp_tax_exem + p_yea_info.ee_occupation_educ_exp2005 ;
530
531 -- End of 3971542
532 for i in 1..p_yea_info.dpnt_educ_contact_type_tbl.count loop
533 if p_yea_info.dpnt_educ_school_type_tbl(i) = 'U' then
534 l_dummy := l_dpnt_educ_school_type_U;
535 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'H' then
536 l_dummy := l_dpnt_educ_school_type_H;
537 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'P' then
538 l_dummy := l_dpnt_educ_school_type_P;
539 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'D' then
540 l_dummy := l_dpnt_educ_school_type_D ;
541 p_yea_info.disabled_educ_exp := p_yea_info.disabled_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
542 end if;
543 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);
544 if p_yea_info.dpnt_educ_contact_type_tbl(i) = 'S' then
545 p_yea_info.spouse_educ_exp := p_yea_info.spouse_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
546 else
547 p_yea_info.dpnt_educ_exp := p_yea_info.dpnt_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
548 end if;
549 end loop;
550 --
551 -- Apply upper limit based on cumulative special exemption
552 p_yea_info.educ_exp_tax_exem := least(
553 p_yea_info.educ_exp_tax_exem,
554 greatest(
555 0,
556 p_yea_info.taxable_income2 - l_cuml_special_exem
557 )
558 ) ;
559 --
560 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.educ_exp_tax_exem ;
561 -- End of 4119483
562 ----------------------------------------------------------------
563 -- Housing Expense Tax Exemption
564 ----------------------------------------------------------------
565 l_dummy := 0;
566 for i in 1..p_yea_info.housing_saving_tbl.count loop
567 if p_yea_info.housing_saving_type_tbl(i) <> 'HST2' then -- 5731165
568 l_dummy := l_dummy + p_yea_info.housing_saving_tbl(i);
569 end if;
570 end loop;
571 if l_dummy > 0
572 or p_yea_info.housing_loan_repay > 0
573 or p_yea_info.lt_housing_loan_interest_repay > 0
574 or p_yea_info.lt_housing_loan_intr_repay_1 > 0 then
575 for i in 1..p_yea_info.housing_saving_tbl.count loop
576 if i = 1 then
577 p_yea_info.housing_saving_type := p_yea_info.housing_saving_type_tbl(i);
578 end if;
579 if p_yea_info.housing_saving_type_tbl(i) = 'HST1' then
580 if p_yea_info.housing_saving_tbl(i) < l_housinsavintype_HST1 then
581 l_dummy := p_yea_info.housing_saving_tbl(i);
582 else
583 l_dummy := l_housinsavintype_HST1;
584 end if;
585 -- Bug 5731165
586 -- Removed code for Housing Saving Type2
587 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST2' then --Bug 5741382
588 l_dummy := 0;
589 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST3' then
590 l_dummy := p_yea_info.housing_saving_tbl(i);
591 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST4' then
592 l_dummy := p_yea_info.housing_saving_tbl(i);
593 end if;
594 --
595 p_yea_info.housing_saving := p_yea_info.housing_saving + l_dummy;
596 --
597 end loop;
598
599 p_yea_info.max_housing_exp_tax_exem := 0;
600 p_yea_info.housing_exp_tax_exem := 0;
601
602 --
603 --
604 if (p_yea_info.housing_saving + p_yea_info.housing_loan_repay) > 0 then
605 p_yea_info.max_housing_exp_tax_exem := trunc(( p_yea_info.housing_saving +
606 p_yea_info.housing_loan_repay )
607 * l_housinexp_tax_exem_per);
608 p_yea_info.housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,
609 l_housinexp_tax_exem_lim);
610 end if;
611
612 -- Long term loan due less then 15 years
613 --
614 -- Bug 3966549 : Added date check
615 if p_yea_info.lt_housing_loan_interest_repay > 0 and p_yea_info.lt_housing_loan_date < c_20040101 then
616 -- End of 3966549
617 p_yea_info.max_housing_exp_tax_exem := p_yea_info.housing_exp_tax_exem +
618 p_yea_info.lt_housing_loan_interest_repay;
619 p_yea_info.housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,
620 l_housinexp_tax_exem_lim1);
621
622 end if;
623
624 -- Long term loan due greater then 15 years
625 --
626 if p_yea_info.lt_housing_loan_intr_repay_1 > 0 then
627 p_yea_info.max_housing_exp_tax_exem := p_yea_info.housing_exp_tax_exem +
628 p_yea_info.lt_housing_loan_intr_repay_1;
629 p_yea_info.housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem,
630 l_housinexp_tax_exem_lim2);
631 end if;
632
633 p_yea_info.max_housing_exp_tax_exem := trunc(p_yea_info.max_housing_exp_tax_exem);
634 p_yea_info.housing_exp_tax_exem := trunc(p_yea_info.housing_exp_tax_exem);
635 --
636 -- Bug 4119483: Apply upper limit based on cumulative special exemption
637 p_yea_info.housing_exp_tax_exem := least(
638 p_yea_info.housing_exp_tax_exem,
639 greatest(
640 0,
641 p_yea_info.taxable_income2 - l_cuml_special_exem
642 )
643 ) ;
644 --
645 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.housing_exp_tax_exem ;
646 -- End of 4119483
647 end if;
648
649 -----------------------------------------------------------------
650 -- Donation Tax Exemption
651 -- Bug 3966549: Modified calculation for statutory donation
652 -- exemption. Added code to calculate tax break
653 -- and ESOA donation exemption details. Included
654 -- ESOA component in the total political exemption
655 -- amount.
656 -----------------------------------------------------------------
657 --
658 -- Calculate for Statutory Donation
659
660 p_yea_info.donation1_tax_exem := p_yea_info.donation1;
661 if p_yea_info.political_donation1 > 0 then
662 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + greatest(p_yea_info.political_donation1 - l_political_tax_brk_lim2004, 0);
663 end if;
664 if p_yea_info.political_donation2 > 0 then
665 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + p_yea_info.political_donation2 ;
666 end if;
667 --
668 -- Calculate Tax Break for Political Donation
669 -- Bug 5682450: Updated Political Donation Tax Break
670 p_yea_info.don_tax_break2004 := (greatest(0, p_yea_info.political_donation1) - greatest(0, p_yea_info.political_donation1 - l_political_tax_brk_lim2004 )) / l_pol_don_exem_rate_factor ;
671 -- End of 5682450
672 --
673 -- Calculate for Special Donation
674 if p_yea_info.donation3 > 0 then
675 if p_yea_info.taxable_income > p_yea_info.donation1_tax_exem then
676 p_yea_info.max_donation3_tax_exem := trunc((p_yea_info.taxable_income - p_yea_info.donation1_tax_exem) * l_donation3_tax_exem_per);
677 p_yea_info.donation3_tax_exem := least(p_yea_info.donation3, p_yea_info.max_donation3_tax_exem);
678 end if;
679 end if;
680 --
681 -- Calculate for ESOA Donation
682 if p_yea_info.esoa_don2004 > 0 then
683 if p_yea_info.taxable_income > (p_yea_info.donation1_tax_exem + p_yea_info.donation3_tax_exem) then
684 p_yea_info.max_esoa_don_tax_exem2004 := trunc( (p_yea_info.taxable_income - p_yea_info.donation1_tax_exem - p_yea_info.donation3_tax_exem) * l_esoa_tax_exem_per2004 ) ;
685 p_yea_info.esoa_don_tax_exem2004 := least(p_yea_info.esoa_don2004, p_yea_info.max_esoa_don_tax_exem2004 ) ;
686 end if ;
687 end if ;
688 --
689 -- Calculate for Specified Donation
690 if p_yea_info.donation2 > 0 then
691 if p_yea_info.taxable_income > ( p_yea_info.donation1_tax_exem + p_yea_info.donation3_tax_exem + p_yea_info.esoa_don_tax_exem2004 ) then
692 p_yea_info.max_donation2_tax_exem := trunc((p_yea_info.taxable_income - p_yea_info.donation1_tax_exem - p_yea_info.donation3_tax_exem - p_yea_info.esoa_don_tax_exem2004 ) * l_donation2_tax_exem_per);
693 p_yea_info.donation2_tax_exem := least(p_yea_info.donation2, p_yea_info.max_donation2_tax_exem);
694 end if;
695 end if;
696 --
697 -- Now calculate total political donation income exemption
698 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 ;
699 --
700 -- Bug 4119483: Apply upper limit based on cumulative special exemption
701 p_yea_info.donation_tax_exem := least(
702 p_yea_info.donation_tax_exem,
703 greatest(
704 0,
705 p_yea_info.taxable_income2 - l_cuml_special_exem
706 )
707 ) ;
708 --
709 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.donation_tax_exem ;
710 -- End of 4119483
711 --
712 -- End of 3966549
713 --
714
715 ----------------------------------------------------------------
716 -- Marriage, Funeral and Relocation Tax Exemption
717 ----------------------------------------------------------------
718 p_yea_info.marr_fun_relo_exemption := 0;
719 if p_yea_info.taxable < 25000000 then
720
721 -- Bug 3901802 Modified for multiple occasions
722 --
723 p_yea_info.marr_fun_relo_exemption :=
724 ( nvl(to_number(p_yea_info.marriage_exemption),0) * l_marriage_exem_lim) +
725 ( nvl(to_number(p_yea_info.funeral_exemption),0) * l_funeral_exem_lim) +
726 ( nvl(to_number(p_yea_info.relocation_exemption),0) * l_relocation_exem_lim);
727
728 -- income exemption available
729 l_dummy := (p_yea_info.taxable_income2) -
730 ( p_yea_info.ins_prem_tax_exem
731 + p_yea_info.med_exp_tax_exem
732 + p_yea_info.educ_exp_tax_exem
733 + p_yea_info.housing_exp_tax_exem
734 + p_yea_info.donation_tax_exem);
735 l_dummy := greatest(l_dummy,0);
736 p_yea_info.marr_fun_relo_exemption := trunc(least(p_yea_info.marr_fun_relo_exemption,
737 l_dummy));
738
739 --
740 -- Bug 4119483: Apply upper limit based on cumulative special exemption
741 p_yea_info.marr_fun_relo_exemption := least(
742 p_yea_info.marr_fun_relo_exemption,
743 greatest(
744 0,
745 p_yea_info.taxable_income2 - l_cuml_special_exem
746 )
747 ) ;
748 --
749 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.marr_fun_relo_exemption ;
750 -- End of 4119483
751 end if;
752
753 ----------------------------------------------------------------
754 -- Special Tax Exemption
755 ----------------------------------------------------------------
756 p_yea_info.sp_tax_exem := p_yea_info.ins_prem_tax_exem
757 + p_yea_info.med_exp_tax_exem
758 + p_yea_info.educ_exp_tax_exem
759 + p_yea_info.housing_exp_tax_exem
760 + p_yea_info.donation_tax_exem
761 + p_yea_info.marr_fun_relo_exemption;
762
763 if p_yea_info.sp_tax_exem < l_std_sp_tax_exem then
764 p_yea_info.sp_tax_exem := 0;
765 p_yea_info.std_sp_tax_exem := l_std_sp_tax_exem;
766 end if;
767 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.sp_tax_exem - p_yea_info.std_sp_tax_exem;
768
769 ----------------------------------------------------------------
770 -- Pension Premium Tax Exemption Bug 6843245
771 ----------------------------------------------------------------
772 if p_yea_info.pen_prem > 0 then
773 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.pen_prem;
774 end if;
775 ----------------------------------------------------------------
776 -- National Pension Premium Tax Exemption
777 ----------------------------------------------------------------
778 if p_yea_info.np_prem > 0 then
779 p_yea_info.np_prem_tax_exem := p_yea_info.np_prem;
780 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.np_prem_tax_exem;
781 end if;
782 ----------------------------------------------------------------
783 -- Bug : 4776711
784 -- Corporate Pension Premium Tax Exemption
785 -- Bug 6852749
786 -- p_yea_info.corp_pension_prem_tax_exem is now deducted from p_yea_info.taxable_income2 and not p_yea_info.taxation_base
787 ----------------------------------------------------------------
788 if p_yea_info.corp_pension_prem > 0 then
789
790 if p_yea_info.pers_pension_saving > 0 then
791 p_yea_info.pers_pension_saving_tax_exem := least(p_yea_info.pers_pension_saving, l_pers_pen_savintax_exem_lim);
792 end if;
793 p_yea_info.corp_pension_prem_tax_exem := least(p_yea_info.corp_pension_prem+p_yea_info.pers_pension_saving,l_corp_pen_prem_tax_exem_lim)- nvl(p_yea_info.pers_pension_saving_tax_exem,0);
794 if p_yea_info.corp_pension_prem_tax_exem < 0 then
795 p_yea_info.corp_pension_prem_tax_exem := 0;
796 end if;
797 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.corp_pension_prem_tax_exem;
798 end if;
799 end if;
800 ----------------------------------------------------------------
801 -- Taxable Income2
802 ----------------------------------------------------------------
803 p_yea_info.taxable_income2 := greatest(p_yea_info.taxable_income2, 0);
804 p_yea_info.taxation_base := p_yea_info.taxable_income2;
805 --
806 if p_yea_info.non_resident_flag = 'N' then
807 ----------------------------------------------------------------
808 -- Personal Pension Premium Tax Exemption
809 ----------------------------------------------------------------
810 if p_yea_info.pers_pension_prem > 0 then
811 p_yea_info.pers_pension_prem_tax_exem := least(trunc(p_yea_info.pers_pension_prem * l_pers_pen_prem_tax_exem_per), l_pers_pen_prem_tax_exem_lim);
812 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_prem_tax_exem;
813 end if;
814 ----------------------------------------------------------------
815 -- Personal Pension Saving Tax Exemption
816 ----------------------------------------------------------------
817 if p_yea_info.pers_pension_saving > 0 then
818 p_yea_info.pers_pension_saving_tax_exem := least(p_yea_info.pers_pension_saving, l_pers_pen_savintax_exem_lim);
819 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_saving_tax_exem;
820 end if;
821 ----------------------------------------------------------------
822 -- Investment Partnership Financing Tax Exemption
823 ----------------------------------------------------------------
824 if p_yea_info.invest_partner_fin1 > 0
825 or p_yea_info.invest_partner_fin2 > 0 then
826 if p_yea_info.taxable_income > 0 then
827 p_yea_info.invest_partner_fin_tax_exem := least(trunc(p_yea_info.invest_partner_fin1 * l_inv_part_fin1_tax_exem_per),
828 trunc(p_yea_info.taxable_income * l_inv_part_fin1_tax_exem_lim));
829 p_yea_info.invest_partner_fin_tax_exem := p_yea_info.invest_partner_fin_tax_exem +
830 least(trunc(p_yea_info.invest_partner_fin2 * l_inv_part_fin2_tax_exem_per),
831 trunc(p_yea_info.taxable_income * l_inv_part_fin2_tax_exem_lim));
832 --/* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
833 --l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base); /* bug 12778151, moved to below for excluding following exemption */
834 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.invest_partner_fin_tax_exem;
835 end if;
836 end if;
837 ----------------------------------------------------------------
838 -- Credit Card Expense Tax Exem
839 -- Bug 3966549: Now differentiate between employee's and
840 -- dependents' direct payments. Also use new
841 -- global value
842 -- (KR_YEA_CREDIT_CARD_EXP_2004_TAX_EXEM) to
843 -- calculate max. exemption amount.
844 -- Bug 3506168: Added cash receipt expenses.
845 ----------------------------------------------------------------
846 ----------------------------------------------------------------
847 -- Bug 6630135 Amount that needs to be excluded from cards exemption
848 -- (Double Exemption amount to be excluded)
849 -- = Total medical expense amount paid in cards (p_yea_info.tot_med_exp_cards)
850 -- - Medical Expense Paid (both cards and cash) but not included in medical expense exemption (l_card_exp_not_exempted -- Calculated in Medical Exemption)
851 -- - Medical Expense Paid in cards for the Dependent who are not eligible for Basic Exemption ((p_yea_info.dpnt_med_exp_cards)
852 ----------------------------------------------------------------
853
854 p_yea_info.med_exp_paid_not_inc_med_exem := nvl(l_card_exp_not_exempted,0);
855 l_double_exem_amt := 0 ;
856 -- Start Bug 6685443 added if condition below
857 if nvl(p_yea_info.tot_med_exp_cards,0) >= trunc(greatest(p_yea_info.taxable, 0) * l_med_exp_tax_exem_per) then
858
859 if nvl(l_card_exp_not_exempted,0) > 0 then
860 l_double_exem_amt := greatest((nvl(p_yea_info.tot_med_exp_cards,0) - nvl(l_card_exp_not_exempted,0) - nvl(p_yea_info.dpnt_med_exp_cards,0)),0);
861 elsif nvl(l_card_exp_not_exempted,0) = 0 then
862 l_double_exem_amt := 0 ;
863 end if;
864 else
865 l_double_exem_amt := 0 ;
866 end if;
867 -- End Bug 6685443
868 -- End of Bug 6630135
869 -- Bug 6716401
870 p_yea_info.double_exem_amt := l_double_exem_amt;
871 -- End of Bug 6716401
872
873 p_yea_info.total_credit_card_exp :=p_yea_info.emp_cre_card_direct_exp2004 + p_yea_info.dpnt_cre_card_direct_exp2004
874 + p_yea_info.giro_tuition_paid_exp2004 + p_yea_info.cash_receipt_exp2005 - l_double_exem_amt ; -- Bug 5682131 l_double_exem_amt added for Bug 6630135
875 if p_yea_info.total_credit_card_exp > 0 then
876 -- After the statement below, l_dummy holds the total credit card expense subject to exemption
877 l_dummy := p_yea_info.total_credit_card_exp - (greatest(p_yea_info.taxable, 0) * l_cre_card_exp_tax_exem_per1);
878 --
879 if l_dummy > 0 then
880 p_yea_info.credit_card_exp_tax_exem := (l_dummy * ( p_yea_info.credit_card_exp / p_yea_info.total_credit_card_exp ));
881 p_yea_info.direct_card_exp_tax_exem := (l_dummy * ( p_yea_info.direct_card_exp / p_yea_info.total_credit_card_exp ));
882 --
883 -- After statement below, l_dummy holds maximum exemption amount
884 l_dummy := l_dummy * l_cre_card_tax_exem_per2004 ;
885 --
886 -- End of 3966549
887 --
888 -- Bug 3089512 Consider the 20% of Taxable Earnings for Credit card exemption.
889 p_yea_info.total_credit_card_exp_tax_exem := trunc(least(l_dummy ,(p_yea_info.taxable * l_cre_card_exp_tax_exem_per2), l_cre_card_exp_tax_exem_lim));
890 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.total_credit_card_exp_tax_exem;
891 --
892 -- Collect total credit card exemption into p_yea_info.credit_card_exp_tax_exem
893 -- for keeping compatibility with package pay_kr_yea20020101_pkg
894 -- These are used to display data in YEA result form and YEA reports
895 --
896 p_yea_info.credit_card_exp_tax_exem := p_yea_info.total_credit_card_exp_tax_exem;
897 end if;
898 -- Collect total credit card expenses into p_yea_info.credit_card_exp
899 p_yea_info.credit_card_exp := p_yea_info.total_credit_card_exp;
900 --
901 end if;
902 ----------------------------------------------------------------
903 -- Employee Stock Ownership Plan Contribution Tax Exemption
904 ----------------------------------------------------------------
905 if p_yea_info.emp_stk_own_contri > 0 then
906 p_yea_info.emp_stk_own_contri_tax_exem := least(p_yea_info.emp_stk_own_contri, l_emp_stock_own_plan_exem_lim);
907 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.emp_stk_own_contri_tax_exem;
908 end if;
909
910 ----------------------------------------------------------------
911 -- Small Business Installment Tax Exemption Bug 6895093
912 ----------------------------------------------------------------
913 if (p_effective_date >= fnd_date.canonical_to_date('2007/09/01') and p_yea_info.small_bus_install > 0) then
914 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));
915 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.small_bus_install_exem;
916 else
917 p_yea_info.small_bus_install_exem := 0;
918 end if;
919 end if;
920 ----------------------------------------------------------------
921 -- Taxation Base
922 ----------------------------------------------------------------
923 p_yea_info.taxation_base := greatest(p_yea_info.taxation_base, 0);
924 ----------------------------------------------------------------
925 -- Calculated Tax
926 ----------------------------------------------------------------
927 p_yea_info.calc_tax := calc_tax(p_yea_info.taxation_base);
928 ----------------------------------------------------------------
929 -- Basic Tax Break
930 -- This tax break is based on "Estimated Calculated Tax
931 -- based on Taxable Earnings without Special Irregular Bonus".
932 ----------------------------------------------------------------
933 if p_yea_info.calc_tax > 0 and p_yea_info.taxable > 0 then
934 l_dummy := trunc( p_yea_info.calc_tax);
935 else
936 l_dummy := 0;
937 end if;
938 --
939 l_available_tax_break := p_yea_info.calc_tax;
940 --
941 if l_dummy > 0 then
942 l_addend := to_number(hruserdt.get_table_value(
943 p_bus_group_id => p_business_group_id,
944 p_table_name => 'BASIC_TAX_BREAK',
945 p_col_name => 'ADDEND',
946 p_row_value => to_char(l_dummy),
947 p_effective_date => p_effective_date));
948 l_subtrahend := to_number(hruserdt.get_table_value(
949 p_bus_group_id => p_business_group_id,
950 p_table_name => 'BASIC_TAX_BREAK',
951 p_col_name => 'SUBTRAHEND',
952 p_row_value => to_char(l_dummy),
953 p_effective_date => p_effective_date));
954 l_multiplier := to_number(hruserdt.get_table_value(
955 p_bus_group_id => p_business_group_id,
956 p_table_name => 'BASIC_TAX_BREAK',
957 p_col_name => 'MULTIPLIER',
958 p_row_value => to_char(l_dummy),
959 p_effective_date => p_effective_date));
960 p_yea_info.basic_tax_break := least(trunc(l_addend + trunc((l_dummy - l_subtrahend) * l_multiplier / 100)), l_basic_tax_break);
961 --
962 p_yea_info.basic_tax_break := least(p_yea_info.basic_tax_break, l_available_tax_break);
963 l_available_tax_break := l_available_tax_break - p_yea_info.basic_tax_break;
964 --
965 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.basic_tax_break;
966 end if;
967 --
968 if p_yea_info.non_resident_flag = 'N' then
969 --
970 ----------------------------------------------------------------
971 -- Housing Expense Tax Break
972 ----------------------------------------------------------------
973 if p_yea_info.housing_loan_interest_repay > 0 then
974 p_yea_info.housing_exp_tax_break := trunc(p_yea_info.housing_loan_interest_repay * l_housinloan_int_repay_per);
975 /* Need the actual housing loan interest tax break for special tax calculation */
976 if p_yea_info.total_tax_break < p_yea_info.calc_tax then
977 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);
978 end if;
979 --
980 p_yea_info.housing_exp_tax_break := least(p_yea_info.housing_exp_tax_break, l_available_tax_break);
981 l_available_tax_break := l_available_tax_break - p_yea_info.housing_exp_tax_break;
982 --
983 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.housing_exp_tax_break;
984 end if;
985 end if;
986 ----------------------------------------------------------------
987 -- Overseas Tax Break
988 ----------------------------------------------------------------
989 if p_yea_info.ovstb_tax_paid_date is not null then
990 if p_yea_info.taxable_income > 0 then
991 --
992 open csr_ovs_def_bal_id;
993 fetch csr_ovs_def_bal_id into l_ovs_def_bal_id;
994 --
995 if csr_ovs_def_bal_id%found then
996 --
997 l_ovs_earnings_bal:= pay_balance_pkg.get_value
998 (p_defined_balance_id => l_ovs_def_bal_id
999 ,p_assignment_id => p_assignment_id
1000 ,p_virtual_date => p_effective_date
1001 ,p_always_get_db_item => FALSE);
1002 end if;
1003 close csr_ovs_def_bal_id;
1004 --
1005 p_yea_info.ovstb_taxable := p_yea_info.ovstb_taxable + l_ovs_earnings_bal;
1006 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);
1007 p_yea_info.ovs_tax_break := least(p_yea_info.ovstb_tax, l_dummy);
1008 --
1009 p_yea_info.ovs_tax_break := least(p_yea_info.ovs_tax_break, l_available_tax_break);
1010 l_available_tax_break := l_available_tax_break - p_yea_info.ovs_tax_break;
1011 --
1012 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.ovs_tax_break;
1013 end if;
1014 end if;
1015 ----------------------------------------------------------------
1016 -- Political Donation Tax Break
1017 -- Bug 3966549
1018 ----------------------------------------------------------------
1019 if p_yea_info.non_resident_flag = 'N' then
1020 p_yea_info.don_tax_break2004 := least(p_yea_info.don_tax_break2004, l_available_tax_break) ;
1021 --
1022 l_available_tax_break := l_available_tax_break - p_yea_info.don_tax_break2004 ;
1023 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.don_tax_break2004 ;
1024 end if ;
1025 -- End of 3966549
1026 ----------------------------------------------------------------
1027 -- Foreign Worker Tax Break and set Annual Tax to "0"
1028 ----------------------------------------------------------------
1029 -- Bug 5083240
1030 if p_yea_info.nationality = 'F'
1031 and p_yea_info.fixed_tax_rate = 'N'
1032 and p_yea_info.fwtb_immigration_purpose is not null then
1033 l_fw_contr_fw_inc_exem := 0 ;
1034 l_fw_contr_taxable := 0 ;
1035 l_fw_contr_basic_exem := 0 ;
1036 l_fw_brk_proration_factor := 0 ;
1037
1038 -- Deduct foreign worker income exemption
1039 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) ;
1040 if l_fw_contr_fw_inc_exem > 0 then
1041 l_fw_contr_taxable := greatest(p_yea_info.fw_contr_taxable_earn - l_fw_contr_fw_inc_exem, 0) ;
1042 p_yea_info.fw_contr_non_taxable_earn := p_yea_info.fw_contr_non_taxable_earn + l_fw_contr_fw_inc_exem ;
1043 end if ;
1044
1045 -- Deduct basic exemption on taxable income calculated till now
1046 if l_fw_contr_taxable > 0 then
1047 l_addend := to_number(hruserdt.get_table_value(
1048 p_bus_group_id => p_business_group_id,
1049 p_table_name => 'BASIC_INCOME_EXEM',
1050 p_col_name => 'ADDEND',
1051 p_row_value => to_char(l_fw_contr_taxable),
1052 p_effective_date => p_effective_date));
1053 l_multiplier := to_number(hruserdt.get_table_value(
1054 p_bus_group_id => p_business_group_id,
1055 p_table_name => 'BASIC_INCOME_EXEM',
1056 p_col_name => 'MULTIPLIER',
1057 p_row_value => to_char(l_fw_contr_taxable),
1058 p_effective_date => p_effective_date));
1059 l_subtrahend := to_number(hruserdt.get_table_value(
1060 p_bus_group_id => p_business_group_id,
1061 p_table_name => 'BASIC_INCOME_EXEM',
1062 p_col_name => 'SUBTRAHEND',
1063 p_row_value => to_char(l_fw_contr_taxable),
1064 p_effective_date => p_effective_date));
1065
1066 l_fw_contr_basic_exem := l_addend + trunc(l_multiplier/100 * (l_fw_contr_taxable - l_subtrahend)) ;
1067 l_fw_contr_taxable := l_fw_contr_taxable - l_fw_contr_basic_exem ;
1068 end if ;
1069
1070 -- Now calculate the proration factor
1071 l_fw_brk_proration_factor := (l_fw_contr_taxable/p_yea_info.taxable_income) ;
1072
1073 -- Prorate tax breaks
1074 p_yea_info.foreign_worker_tax_break := greatest(trunc((p_yea_info.calc_tax * l_fw_brk_proration_factor), 0), 0) ;
1075 p_yea_info.annual_itax := greatest(p_yea_info.calc_tax - p_yea_info.foreign_worker_tax_break, 0) ;
1076 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per, 0) ;
1077 -- End of 5083240
1078
1079 p_yea_info.annual_stax := 0;
1080
1081 p_yea_info.basic_tax_break := 0;
1082 p_yea_info.housing_exp_tax_break := 0;
1083 p_yea_info.ovs_tax_break := 0;
1084 p_yea_info.don_tax_break2004 := 0;
1085 p_yea_info.total_tax_break := 0;
1086
1087 if p_yea_info.fwtb_immigration_purpose = 'G' then
1088 p_yea_info.foreign_worker_tax_break1 := p_yea_info.foreign_worker_tax_break;
1089 else
1090 p_yea_info.foreign_worker_tax_break2 := p_yea_info.foreign_worker_tax_break;
1091 end if;
1092 else
1093 ----------------------------------------------------------------
1094 -- Annual Tax
1095 ----------------------------------------------------------------
1096 if p_yea_info.total_tax_break > p_yea_info.calc_tax then
1097 p_yea_info.total_tax_break := p_yea_info.calc_tax;
1098 end if;
1099 --
1100 p_yea_info.annual_itax := trunc(greatest(p_yea_info.calc_tax - p_yea_info.total_tax_break, 0),0);
1101 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per,0);
1102 --
1103 -- bug 12778151
1104 /* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
1105 l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base + nvl(p_yea_info.invest_partner_fin_tax_exem,0));
1106 --
1107 if l_calc_tax_for_stax > 0 then
1108 p_yea_info.annual_stax := trunc((l_calc_tax_for_stax - p_yea_info.calc_tax) * l_annual_stax_per);
1109 end if;
1110 if l_net_housing_exp_tax_break > 0 then
1111 p_yea_info.annual_stax := p_yea_info.annual_stax + trunc(l_net_housing_exp_tax_break * l_housinexp_tax_break);
1112 end if;
1113 p_yea_info.annual_stax := trunc(p_yea_info.annual_stax,0);
1114 end if;
1115 end if; -- nationality
1116 ----------------------------------------------------------------
1117 -- Calculate Tax Adjustment
1118 -- Truncating the last Won digit after the final calculation of annual_itax , annual_rtax and annual_stax.
1119 ----------------------------------------------------------------
1120 p_yea_info.itax_adj := trunc(p_yea_info.annual_itax - p_yea_info.prev_itax - p_yea_info.cur_itax,-1);
1121 p_yea_info.rtax_adj := trunc(p_yea_info.annual_rtax - p_yea_info.prev_rtax - p_yea_info.cur_rtax,-1);
1122 p_yea_info.stax_adj := trunc(p_yea_info.annual_stax - p_yea_info.prev_stax - p_yea_info.cur_stax,-1);
1123 --
1124 if p_yea_info.itax_adj >= 0 and p_yea_info.itax_adj < 1000 then
1125 p_yea_info.itax_adj := 0;
1126 p_yea_info.rtax_adj := 0;
1127 p_yea_info.stax_adj := 0;
1128 p_tax_adj_warning := TRUE;
1129 end if;
1130 end yea;
1131 --
1132 end pay_kr_yea20070101_pkg;