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