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