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