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