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