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