[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA20020101_PKG
Source
1 package body pay_kr_yea20020101_pkg as
2 /* $Header: pykryea1.pkb 120.3 2006/12/21 06:54:08 pdesu noship $ */
3 --
4 procedure yea(
5 p_assignment_id in number,
6 p_effective_date in date,
7 p_business_group_id in number,
8 p_yea_info in out NOCOPY pay_kr_yea_pkg.t_yea_info,
9 -- Bug 2878937
10 p_tax_adj_warning out NOCOPY boolean)
11 is
12 -- Bug # : 2706312
13 cursor csr_ovs_def_bal_id
14 is
15 SELECT pdb.defined_balance_id
16 FROM pay_balance_types pbt,
17 pay_defined_balances pdb,
18 pay_balance_dimensions pbd
19 WHERE pbt.balance_name like 'Overseas Earnings'
20 and pbt.legislation_code = 'KR'
21 and pbt.balance_type_id = pdb.balance_type_id
22 and pbd.balance_dimension_id = pdb.balance_dimension_id
23 and pbd.database_item_suffix = '_ASG_YTD';
24
25 l_ovs_earnings_bal number:=0;
26 l_ovs_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
27
28 l_addend number;
29 l_multiplier number;
30 l_subtrahend number;
31 l_dummy number;
32 l_net_housing_exp_tax_break number;
33 l_calc_tax_for_stax number;
34 --
35 l_ee_tax_exem number default 0;
36 l_dpnt_spouse_tax_exem number default 0;
37 l_dpnt_tax_exem number default 0;
38 l_aged_tax_exem number default 0;
39 l_disabled_tax_exem number default 0;
40 l_female_ee_tax_exem number default 0;
41 l_num_of_children number default 0;
42 l_supp_tax_exem_single number default 0;
43 l_supp_tax_exem_1_dpnt number default 0;
44 l_pers_ins_prem_tax_exem number default 0;
45 l_disabled_ins_prem_tax_exem number default 0;
46 l_med_exp_tax_exem_per number default 0;
47 l_med_exp_tax_exem_lim number default 0;
48 l_dpnt_educ_school_type_U number default 0;
49 l_dpnt_educ_school_type_H number default 0;
50 l_dpnt_educ_school_type_P number default 0;
51 l_dpnt_educ_school_type_D number default 0;
52 l_housinexp_tax_exem_per number default 0;
53 l_housinsavintype_HST1 number default 0;
54 l_housinsavintype_HST2 number default 0;
55 l_housinsavintype_HST3 number default 0;
56 l_housinsavintype_HST4 number default 0;
57 l_housinexp_tax_exem_lim number default 0;
58 l_political_donation1_lim number default 0;
59 l_political_donation2_lim number default 0;
60 l_political_donation3_lim number default 0;
61 l_political_donation3_per number default 0;
62 l_donation2_tax_exem_per number default 0;
63 l_donation3_tax_exem_per number default 0;
64 l_std_sp_tax_exem number default 0;
65 l_pers_pen_prem_tax_exem_per number default 0;
66 l_pers_pen_prem_tax_exem_lim number default 0;
67 l_pers_pen_savintax_exem_lim number default 0;
68 l_emp_stock_own_plan_exem_lim number default 0;
69 l_cre_card_exp_tax_exem_per1 number default 0;
70 l_cre_card_exp_tax_exem_per2 number default 0;
71 l_cre_card_exp_tax_exem_lim number default 0;
72 l_inv_part_fin1_tax_exem_per number default 0;
73 l_inv_part_fin2_tax_exem_per number default 0;
74 l_inv_part_fin1_tax_exem_lim number default 0;
75 l_inv_part_fin2_tax_exem_lim number default 0;
76 l_basic_tax_break number default 0;
77 l_stock_savintax_break_per number default 0;
78 l_lt_stk_sav1_tax_break_per number default 0;
79 l_lt_stk_sav2_tax_break_per number default 0;
80 l_housinloan_int_repay_per number default 0;
81 l_annual_itax_per number default 0;
82 l_annual_stax_per number default 0;
83 l_housinexp_tax_break number default 0;
84 l_cuml_special_exem number default 0 ; -- Adds up all special exemptions given for tracking purpose, for limit on exemptions.
85
86 function calc_tax(p_taxation_base in number) return number
87 is
88 begin
89 if p_taxation_base > 0 then
90 l_multiplier := to_number(hruserdt.get_table_value(
91 p_bus_group_id => p_business_group_id,
92 p_table_name => 'CALC_TAX',
93 p_col_name => 'MULTIPLIER',
94 p_row_value => to_char(p_taxation_base),
95 p_effective_date => p_effective_date));
96 l_subtrahend := to_number(hruserdt.get_table_value(
97 p_bus_group_id => p_business_group_id,
98 p_table_name => 'CALC_TAX',
99 p_col_name => 'SUBTRAHEND',
100 p_row_value => to_char(p_taxation_base),
101 p_effective_date => p_effective_date));
102 return trunc(p_taxation_base * l_multiplier / 100) - l_subtrahend;
103 else
104 return 0;
105 end if;
106 end calc_tax;
107 --
108 function get_globalvalue(p_glbvar in varchar2,p_process_date in date) return number
109 is
110 --
111 cursor csr_ff_global
112 is
113 select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
114 from ff_globals_f glb
115 where glb.global_name = p_glbvar
116 and p_process_date between glb.effective_start_date and glb.effective_end_date;
117 --
118 l_glbvalue number default 0;
119 begin
120 Open csr_ff_global;
121 fetch csr_ff_global into l_glbvalue;
122 close csr_ff_global;
123 --
124 if l_glbvalue is null then
125 l_glbvalue := 0;
126 end if;
127 --
128 return l_glbvalue;
129 end;
130 --
131
132 begin
133
134 ----------------------------------------------------------------------------+
135 -- Populating Local variables with Global values
136 --
137 ----------------------------------------------------------------------------+
138 l_ee_tax_exem := get_globalvalue('KR_YEA_EE_TAX_EXEM',p_effective_date);
139 l_dpnt_spouse_tax_exem := get_globalvalue('KR_YEA_DPNT_SPOUSE_TAX_EXEM',p_effective_date);
140 l_dpnt_tax_exem := get_globalvalue('KR_YEA_DPNT_TAX_EXEM',p_effective_date);
141 l_aged_tax_exem := get_globalvalue('KR_YEA_AGED_TAX_EXEM',p_effective_date);
142 l_disabled_tax_exem := get_globalvalue('KR_YEA_DISABLED_TAX_EXEM',p_effective_date);
143 l_female_ee_tax_exem := get_globalvalue('KR_YEA_FEMALE_EE_TAX_EXEM',p_effective_date);
144 l_num_of_children := get_globalvalue('KR_YEA_NUM_OF_CHILDREN',p_effective_date);
145 l_supp_tax_exem_single := get_globalvalue('KR_YEA_SUPP_TAX_EXEM_SINGLE',p_effective_date);
146 l_supp_tax_exem_1_dpnt := get_globalvalue('KR_YEA_SUPP_TAX_EXEM_1_DPNT',p_effective_date);
147 l_pers_ins_prem_tax_exem := get_globalvalue('KR_YEA_PERS_INS_PREM_TAX_EXEM',p_effective_date);
148 l_disabled_ins_prem_tax_exem := get_globalvalue('KR_YEA_DISABLED_INS_PREM_TAX_EXEM',p_effective_date);
149 l_med_exp_tax_exem_per := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_PER',p_effective_date);
150 l_med_exp_tax_exem_lim := get_globalvalue('KR_YEA_MED_EXP_TAX_EXEM_LIM',p_effective_date);
151 l_dpnt_educ_school_type_U := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_U',p_effective_date);
152 l_dpnt_educ_school_type_H := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_H',p_effective_date);
153 l_dpnt_educ_school_type_P := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_P',p_effective_date);
154 l_dpnt_educ_school_type_D := get_globalvalue('KR_YEA_DPNT_EDUC_SCHOOL_TYPE_D',p_effective_date);
155 l_housinexp_tax_exem_per := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_PER',p_effective_date);
156 l_housinsavintype_HST1 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST1',p_effective_date);
157 l_housinsavintype_HST2 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST2',p_effective_date);
158 l_housinsavintype_HST3 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST3',p_effective_date);
159 l_housinsavintype_HST4 := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST4',p_effective_date);
160 l_housinexp_tax_exem_lim := get_globalvalue('KR_YEA_HOUSINEXP_TAX_EXEM_LIM',p_effective_date);
161 l_political_donation1_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION1_LIM',p_effective_date);
162 l_political_donation2_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION2_LIM',p_effective_date);
163 l_political_donation3_lim := get_globalvalue('KR_YEA_POLITICAL_DONATION3_LIM',p_effective_date);
164 l_political_donation3_per := get_globalvalue('KR_YEA_POLITICAL_DONATION3_PER',p_effective_date);
165 l_donation2_tax_exem_per := get_globalvalue('KR_YEA_DONATION2_TAX_EXEM_PER',p_effective_date);
166 l_donation3_tax_exem_per := get_globalvalue('KR_YEA_DONATION3_TAX_EXEM_PER',p_effective_date);
167 l_std_sp_tax_exem := get_globalvalue('KR_YEA_STD_SP_TAX_EXEM',p_effective_date);
168 l_pers_pen_prem_tax_exem_per := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_PER',p_effective_date);
169 l_pers_pen_prem_tax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_PREM_TAX_EXEM_LIM',p_effective_date);
170 l_pers_pen_savintax_exem_lim := get_globalvalue('KR_YEA_PERS_PENSION_SAVINTAX_EXEM_LIM',p_effective_date);
171 l_emp_stock_own_plan_exem_lim := get_globalvalue('KR_YEA_EMP_STOCK_OWN_PLAN_EXEM_LIM',p_effective_date);
172 l_cre_card_exp_tax_exem_per1 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER1',p_effective_date);
173 l_cre_card_exp_tax_exem_per2 := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_PER2',p_effective_date);
174 l_cre_card_exp_tax_exem_lim := get_globalvalue('KR_YEA_CREDIT_CARD_EXP_TAX_EXEM_LIM',p_effective_date);
175 l_inv_part_fin1_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN1_TAX_EXEM_PER',p_effective_date);
176 l_inv_part_fin2_tax_exem_per := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_PER',p_effective_date);
177 l_inv_part_fin1_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN1_TAX_EXEM_LIM',p_effective_date);
178 l_inv_part_fin2_tax_exem_lim := get_globalvalue('KR_YEA_INVEST_PARTNER_FIN2_TAX_EXEM_LIM',p_effective_date);
179 l_basic_tax_break := get_globalvalue('KR_YEA_BASIC_TAX_BREAK',p_effective_date);
180 l_stock_savintax_break_per := get_globalvalue('KR_YEA_STOCK_SAVINTAX_BREAK_PER',p_effective_date);
181 l_lt_stk_sav1_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING1_TAX_BREAK_PER',p_effective_date);
182 l_lt_stk_sav2_tax_break_per := get_globalvalue('KR_YEA_LT_STOCK_SAVING2_TAX_BREAK_PER',p_effective_date);
183 l_housinloan_int_repay_per := get_globalvalue('KR_YEA_HOUSINLOAN_INTEREST_REPAY_PER',p_effective_date);
184 l_annual_itax_per := get_globalvalue('KR_YEA_ANNUAL_ITAX_PER',p_effective_date);
185 l_annual_stax_per := get_globalvalue('KR_YEA_ANNUAL_STAX_PER',p_effective_date);
186 l_housinexp_tax_break := get_globalvalue('KR_YEA_HOUSINEXP_TAX_BREAK',p_effective_date);
187 -- Bug 2878937
188 p_tax_adj_warning := false;
189 ----------------------------------------------------------------
190 -- Basic Income Exemption
191 -- Taxable Income
192 ----------------------------------------------------------------
193 if p_yea_info.taxable > 0 then
194 l_addend := to_number(hruserdt.get_table_value(
195 p_bus_group_id => p_business_group_id,
196 p_table_name => 'BASIC_INCOME_EXEM',
197 p_col_name => 'ADDEND',
198 p_row_value => to_char(p_yea_info.taxable),
199 p_effective_date => p_effective_date));
200 l_multiplier := to_number(hruserdt.get_table_value(
201 p_bus_group_id => p_business_group_id,
202 p_table_name => 'BASIC_INCOME_EXEM',
206 l_subtrahend := to_number(hruserdt.get_table_value(
203 p_col_name => 'MULTIPLIER',
204 p_row_value => to_char(p_yea_info.taxable),
205 p_effective_date => p_effective_date));
207 p_bus_group_id => p_business_group_id,
208 p_table_name => 'BASIC_INCOME_EXEM',
209 p_col_name => 'SUBTRAHEND',
210 p_row_value => to_char(p_yea_info.taxable),
211 p_effective_date => p_effective_date));
212 p_yea_info.basic_income_exem := l_addend + trunc(l_multiplier / 100 * (p_yea_info.taxable - l_subtrahend));
213 p_yea_info.taxable_income := p_yea_info.taxable - p_yea_info.basic_income_exem;
214 end if;
215 p_yea_info.taxable_income2 := p_yea_info.taxable_income;
216 ----------------------------------------------------------------
217 -- Employee Tax Exemption
218 ----------------------------------------------------------------
219 p_yea_info.ee_tax_exem := l_ee_tax_exem;
220 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.ee_tax_exem;
221 ----------------------------------------------------------------
222 -- Dependent Tax Exemption
223 ----------------------------------------------------------------
224 if p_yea_info.dpnt_spouse_flag = 'Y' then
225 p_yea_info.dpnt_spouse_tax_exem := l_dpnt_spouse_tax_exem;
226 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_spouse_tax_exem;
227 l_dummy := 1;
228 else
229 l_dummy := 0;
230 end if;
231 if p_yea_info.num_of_dpnts > 0 then
232 p_yea_info.dpnt_tax_exem := p_yea_info.num_of_dpnts * l_dpnt_tax_exem;
233 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_tax_exem;
234 l_dummy := l_dummy + p_yea_info.num_of_dpnts;
235 end if;
236 if p_yea_info.num_of_ageds > 0 then
237 p_yea_info.aged_tax_exem := p_yea_info.num_of_ageds * l_aged_tax_exem;
238 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.aged_tax_exem;
239 end if;
240 if p_yea_info.num_of_disableds > 0 then
241 p_yea_info.disabled_tax_exem := p_yea_info.num_of_disableds * l_disabled_tax_exem;
242 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.disabled_tax_exem;
243 end if;
244 if p_yea_info.female_ee_flag = 'Y' then
245 p_yea_info.female_ee_tax_exem := l_female_ee_tax_exem;
246 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.female_ee_tax_exem;
247 end if;
248 if p_yea_info.num_of_children > 0 then
249 p_yea_info.child_tax_exem := p_yea_info.num_of_children * l_num_of_children;
250 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.child_tax_exem;
251 end if;
252 ----------------------------------------------------------------
253 -- Supplemental Tax Exemption
254 ----------------------------------------------------------------
255 if l_dummy <= 1 then
256 if l_dummy = 0 then
257 p_yea_info.supp_tax_exem := l_supp_tax_exem_single;
258 elsif l_dummy = 1 then
259 p_yea_info.supp_tax_exem := l_supp_tax_exem_1_dpnt;
260 end if;
261 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.supp_tax_exem;
262 end if;
263 ----------------------------------------------------------------
264 -- Special Tax Exemption
265 ----------------------------------------------------------------
266 l_cuml_special_exem := 0; -- Bug 4709683
267 if p_yea_info.non_resident_flag = 'N' then
268 --
269 -- Insurance Premium Tax Exemption
270 --
271 if p_yea_info.hi_prem > 0 then
272 p_yea_info.hi_prem_tax_exem := p_yea_info.hi_prem;
273 end if;
274 if p_yea_info.ei_prem > 0 then
275 p_yea_info.ei_prem_tax_exem := p_yea_info.ei_prem;
276 end if;
277 if p_yea_info.pers_ins_prem > 0 then
278 p_yea_info.pers_ins_prem_tax_exem := least(p_yea_info.pers_ins_prem, l_pers_ins_prem_tax_exem );
279 end if;
280 if p_yea_info.disabled_ins_prem > 0 then
281 p_yea_info.disabled_ins_prem_tax_exem := least(p_yea_info.disabled_ins_prem, l_disabled_ins_prem_tax_exem);
282 end if;
283 p_yea_info.ins_prem_tax_exem := p_yea_info.hi_prem_tax_exem
284 + p_yea_info.ei_prem_tax_exem
285 + p_yea_info.pers_ins_prem_tax_exem
286 + p_yea_info.disabled_ins_prem_tax_exem;
287
288 -- Bug 4119483: Apply upper limit based on cumulative special exemption
289 p_yea_info.ins_prem_tax_exem := least(
290 p_yea_info.ins_prem_tax_exem,
291 greatest(
292 0,
293 p_yea_info.taxable_income2 - l_cuml_special_exem
294 )
295 ) ;
296
297 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.ins_prem_tax_exem ;
298 -- End of 4119483
299
300 --
301 -- Medical Expense Tax Exemption
302 -- Tax Exemption Calculation Modified for Bug No 2508354
303 --
304 l_dummy := p_yea_info.med_exp + p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged;
305 if l_dummy > 0 then
306 l_dummy := l_dummy - trunc(greatest(p_yea_info.taxable, 0) * l_med_exp_tax_exem_per);
307 if l_dummy > 0 then
308 if l_dummy < l_med_exp_tax_exem_lim then
309 p_yea_info.med_exp_tax_exem := l_dummy;
310 elsif (l_dummy >= l_med_exp_tax_exem_lim ) and ((p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged)=0) then
311 p_yea_info.med_exp_tax_exem := l_med_exp_tax_exem_lim ;
312 else
313 p_yea_info.max_med_exp_tax_exem := least(p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged, l_dummy - l_med_exp_tax_exem_lim);
314 p_yea_info.med_exp_tax_exem := l_med_exp_tax_exem_lim + p_yea_info.max_med_exp_tax_exem;
315 end if;
316 end if;
317 end if;
318 -- Bug 4119483: Apply upper limit based on cumulative special exemption
319 p_yea_info.med_exp_tax_exem := least(
320 p_yea_info.med_exp_tax_exem,
321 greatest(
322 0,
326
323 p_yea_info.taxable_income2 - l_cuml_special_exem
324 )
325 ) ;
327 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.med_exp_tax_exem ;
328 -- End of 4119483
329 --
330 -- Education Expense Tax Exemption
331 -- Tax Exemption Calculation Modified for Bug No 2508354
332 --
333 p_yea_info.educ_exp_tax_exem := p_yea_info.ee_educ_exp;
334 for i in 1..p_yea_info.dpnt_educ_contact_type_tbl.count loop
335 if p_yea_info.dpnt_educ_school_type_tbl(i) = 'U' then
336 l_dummy := l_dpnt_educ_school_type_U;
337 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'H' then
338 l_dummy := l_dpnt_educ_school_type_H;
339 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'P' then
340 l_dummy := l_dpnt_educ_school_type_P;
341 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'D' then
342 l_dummy := l_dpnt_educ_school_type_D ;
343 --
344 -- Tax Exemption Calculation Modified for Bug No 2577751 (Calculated Education Expenses for disableds)
345 --
346 p_yea_info.disabled_educ_exp := p_yea_info.disabled_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
347 end if;
348 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);
349 if p_yea_info.dpnt_educ_contact_type_tbl(i) = 'S' then
350 p_yea_info.spouse_educ_exp := p_yea_info.spouse_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
351 else
352 p_yea_info.dpnt_educ_exp := p_yea_info.dpnt_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
353 end if;
354 end loop;
355 --
356 -- Bug 4119483: Apply upper limit based on cumulative special exemption
357 p_yea_info.educ_exp_tax_exem := least(
358 p_yea_info.educ_exp_tax_exem,
359 greatest(
360 0,
361 p_yea_info.taxable_income2 - l_cuml_special_exem
362 )
363 ) ;
364 --
365 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.educ_exp_tax_exem ;
366 -- End of 4119483
367 --
368 -- Housing Expense Tax Exemption
369 -- Tax Exemption calculation modified for Bug No 2523481
370 --
371 for i in 1..p_yea_info.housing_saving_tbl.count loop
372 l_dummy := l_dummy + p_yea_info.housing_saving_tbl(i);
373 end loop;
374 if l_dummy > 0
375 or p_yea_info.housing_loan_repay > 0
376 or p_yea_info.lt_housing_loan_interest_repay > 0 then
377 for i in 1..p_yea_info.housing_saving_tbl.count loop
378 if i = 1 then
379 p_yea_info.housing_saving_type := p_yea_info.housing_saving_type_tbl(i);
380 end if;
381 if p_yea_info.housing_saving_type_tbl(i) = 'HST1' then
382 if p_yea_info.housing_saving_tbl(i) < l_housinsavintype_HST1 then
383 l_dummy := p_yea_info.housing_saving_tbl(i);
384 else
385 l_dummy := l_housinsavintype_HST1;
386 end if;
387 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST2' then
388 if p_yea_info.housing_saving_tbl(i) < l_housinsavintype_HST2 then
389 l_dummy := p_yea_info.housing_saving_tbl(i);
390 else
391 l_dummy := l_housinsavintype_HST2;
392 end if;
393 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST3' then
394 l_dummy := p_yea_info.housing_saving_tbl(i);
395 elsif p_yea_info.housing_saving_type_tbl(i) = 'HST4' then
396 l_dummy := p_yea_info.housing_saving_tbl(i);
397 end if;
398 --
399 p_yea_info.housing_saving := p_yea_info.housing_saving + l_dummy;
400 --
401 end loop;
402 --
403 p_yea_info.max_housing_exp_tax_exem := trunc((p_yea_info.housing_saving + p_yea_info.housing_loan_repay) * l_housinexp_tax_exem_per )
404 + p_yea_info.lt_housing_loan_interest_repay;
405 p_yea_info.housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem, l_housinexp_tax_exem_lim);
406 --
407 -- Bug 4119483: Apply upper limit based on cumulative special exemption
408 p_yea_info.housing_exp_tax_exem := least(
409 p_yea_info.housing_exp_tax_exem,
410 greatest(
411 0,
412 p_yea_info.taxable_income2 - l_cuml_special_exem
413 )
414 ) ;
415 --
416 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.housing_exp_tax_exem ;
417 -- End of 4119483
418 end if;
419 --
420 -- Donation Tax Exemption
421 -- MOdified for Bug 2581461
422 -- Replaced every instance of p_yea_info.taxable with p_yea_info.taxable_income
423 --
424 p_yea_info.donation1_tax_exem := p_yea_info.donation1;
425 if p_yea_info.political_donation1 > 0 then
426 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation1, l_political_donation1_lim);
427 end if;
428 if p_yea_info.political_donation2 > 0 then
429 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation2, l_political_donation2_lim);
430 end if;
431 if p_yea_info.political_donation3 > 0 then
432 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation3,
436 if p_yea_info.taxable_income > p_yea_info.donation1_tax_exem then
433 greatest(trunc(greatest(p_yea_info.taxable_income, 0) * l_political_donation3_per), l_political_donation3_lim));
434 end if;
435 if p_yea_info.donation3 > 0 then
437 p_yea_info.max_donation3_tax_exem := trunc((p_yea_info.taxable_income - p_yea_info.donation1_tax_exem) * l_donation3_tax_exem_per);
438 p_yea_info.donation3_tax_exem := least(p_yea_info.donation3, p_yea_info.max_donation3_tax_exem);
439 end if;
440 end if;
441 if p_yea_info.donation2 > 0 then
442 if p_yea_info.taxable_income > ( p_yea_info.donation1_tax_exem + p_yea_info.donation3_tax_exem) then
443 p_yea_info.max_donation2_tax_exem := trunc((p_yea_info.taxable_income - p_yea_info.donation1_tax_exem - p_yea_info.donation3_tax_exem ) * l_donation2_tax_exem_per);
444 p_yea_info.donation2_tax_exem := least(p_yea_info.donation2, p_yea_info.max_donation2_tax_exem);
445 end if;
446 end if;
447 p_yea_info.donation_tax_exem := p_yea_info.donation1_tax_exem + p_yea_info.donation2_tax_exem + p_yea_info.donation3_tax_exem;
448 --
449 -- Bug 4119483: Apply upper limit based on cumulative special exemption
450 p_yea_info.donation_tax_exem := least(
451 p_yea_info.donation_tax_exem,
452 greatest(
453 0,
454 p_yea_info.taxable_income2 - l_cuml_special_exem
455 )
456 ) ;
457 --
458 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.donation_tax_exem ;
459 -- End of 4119483
460 --
461 --
462 -- Special Tax Exemption
463 --
464 p_yea_info.sp_tax_exem := p_yea_info.ins_prem_tax_exem
465 + p_yea_info.med_exp_tax_exem
466 + p_yea_info.educ_exp_tax_exem
467 + p_yea_info.housing_exp_tax_exem
468 + p_yea_info.donation_tax_exem;
469 if p_yea_info.sp_tax_exem < l_std_sp_tax_exem then
470 p_yea_info.sp_tax_exem := 0;
471 p_yea_info.std_sp_tax_exem := l_std_sp_tax_exem;
472 end if;
473 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.sp_tax_exem - p_yea_info.std_sp_tax_exem;
474 ----------------------------------------------------------------
475 -- National Pension Premium Tax Exemption
476 -- Bug 2878936 : National Pension Exemption is given only to residents
477 ----------------------------------------------------------------
478 if p_yea_info.np_prem > 0 then
479 p_yea_info.np_prem_tax_exem := p_yea_info.np_prem;
480 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.np_prem_tax_exem;
481 end if;
482 end if;
483 ----------------------------------------------------------------
484 -- Taxable Income2
485 ----------------------------------------------------------------
486 p_yea_info.taxable_income2 := greatest(p_yea_info.taxable_income2, 0);
487 p_yea_info.taxation_base := p_yea_info.taxable_income2;
488 --
489 if p_yea_info.non_resident_flag = 'N' then
490 ----------------------------------------------------------------
491 -- Personal Pension Premium Tax Exemption
492 ----------------------------------------------------------------
493 if p_yea_info.pers_pension_prem > 0 then
494 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);
495 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_prem_tax_exem;
496 end if;
497 ----------------------------------------------------------------
498 -- Personal Pension Saving Tax Exemption
499 ----------------------------------------------------------------
500 if p_yea_info.pers_pension_saving > 0 then
501 p_yea_info.pers_pension_saving_tax_exem := least(p_yea_info.pers_pension_saving, l_pers_pen_savintax_exem_lim);
502 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_saving_tax_exem;
503 end if;
504 ----------------------------------------------------------------
505 -- Employee Stock Ownership Plan Contribution Tax Exemption
506 ----------------------------------------------------------------
507 if p_yea_info.emp_stk_own_contri > 0 then
508 p_yea_info.emp_stk_own_contri_tax_exem := least(p_yea_info.emp_stk_own_contri, l_emp_stock_own_plan_exem_lim);
509 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.emp_stk_own_contri_tax_exem;
510 end if;
511
512 ----------------------------------------------------------------
513 -- Credit Card Expense Tax Exem
514 ----------------------------------------------------------------
515 if p_yea_info.credit_card_exp > 0 then
516 l_dummy := p_yea_info.credit_card_exp - trunc(greatest(p_yea_info.taxable, 0) * l_cre_card_exp_tax_exem_per1);
517 if l_dummy > 0 then
518 p_yea_info.credit_card_exp_tax_exem := least(trunc(l_dummy * l_cre_card_exp_tax_exem_per2), l_cre_card_exp_tax_exem_lim);
519 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.credit_card_exp_tax_exem;
520 end if;
521 end if;
522 ----------------------------------------------------------------
523 -- Investment Partnership Financing Tax Exemption
524 -- MOdified for Bug 2581461
525 -- Replaced every instance of p_yea_info.taxable with p_yea_info.taxable_income
526 -- Modified for 2617486
527 --
528 ----------------------------------------------------------------
529 if p_yea_info.invest_partner_fin1 > 0
530 or p_yea_info.invest_partner_fin2 > 0 then
531 if p_yea_info.taxable_income > 0 then
532 p_yea_info.invest_partner_fin_tax_exem := least(trunc(p_yea_info.invest_partner_fin1 * l_inv_part_fin1_tax_exem_per),
533 trunc(p_yea_info.taxable_income * l_inv_part_fin1_tax_exem_lim));
534 p_yea_info.invest_partner_fin_tax_exem := p_yea_info.invest_partner_fin_tax_exem +
538 l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base);
535 least(trunc(p_yea_info.invest_partner_fin2 * l_inv_part_fin2_tax_exem_per),
536 trunc(p_yea_info.taxable_income * l_inv_part_fin2_tax_exem_lim));
537 /* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
539 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.invest_partner_fin_tax_exem;
540 end if;
541 end if;
542 end if;
543 ----------------------------------------------------------------
544 -- Taxation Base
545 ----------------------------------------------------------------
546 p_yea_info.taxation_base := greatest(p_yea_info.taxation_base, 0);
547 ----------------------------------------------------------------
548 -- Calculated Tax
549 ----------------------------------------------------------------
550 p_yea_info.calc_tax := calc_tax(p_yea_info.taxation_base);
551 ----------------------------------------------------------------
552 -- Basic Tax Break
553 -- This tax break is based on "Estimated Calculated Tax
554 -- based on Taxable Earnings without Special Irregular Bonus".
555 ----------------------------------------------------------------
556 if p_yea_info.calc_tax > 0 and p_yea_info.taxable > 0 then
557 l_dummy := trunc( p_yea_info.calc_tax
558 * (p_yea_info.taxable - p_yea_info.sp_irreg_bonus)
559 / p_yea_info.taxable);
560 else
561 l_dummy := 0;
562 end if;
563 --
564 if l_dummy > 0 then
565 l_addend := to_number(hruserdt.get_table_value(
566 p_bus_group_id => p_business_group_id,
567 p_table_name => 'BASIC_TAX_BREAK',
568 p_col_name => 'ADDEND',
569 p_row_value => to_char(l_dummy),
570 p_effective_date => p_effective_date));
571 l_subtrahend := to_number(hruserdt.get_table_value(
572 p_bus_group_id => p_business_group_id,
573 p_table_name => 'BASIC_TAX_BREAK',
574 p_col_name => 'SUBTRAHEND',
575 p_row_value => to_char(l_dummy),
576 p_effective_date => p_effective_date));
577 l_multiplier := to_number(hruserdt.get_table_value(
578 p_bus_group_id => p_business_group_id,
579 p_table_name => 'BASIC_TAX_BREAK',
580 p_col_name => 'MULTIPLIER',
581 p_row_value => to_char(l_dummy),
582 p_effective_date => p_effective_date));
583 p_yea_info.basic_tax_break := least(trunc(l_addend + trunc((l_dummy - l_subtrahend) * l_multiplier / 100)), l_basic_tax_break);
584 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.basic_tax_break;
585 end if;
586 --
587 if p_yea_info.non_resident_flag = 'N' then
588 ----------------------------------------------------------------
589 -- Stock Saving Tax Break
590 -- Commented out this calculation for YEA Stats Update 2002
591 -- Stock Saving Tax Break is obsolete now (Bug 2577751)
592 ----------------------------------------------------------------
593 -- if p_yea_info.stock_saving > 0 then
594 -- p_yea_info.stock_saving_tax_break := trunc(p_yea_info.stock_saving * l_stock_savintax_break_per);
595 -- p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.stock_saving_tax_break;
596 -- end if;
597 ----------------------------------------------------------------
598 -- Longterm Stock Saving1 and saving2 Tax Break
599 -- Modified for Bug 2581461
600 --
601 ----------------------------------------------------------------
602 if p_yea_info.lt_stock_saving1 > 0
603 or p_yea_info.lt_stock_saving2 > 0 then
604 p_yea_info.lt_stock_saving_tax_break := trunc(p_yea_info.lt_stock_saving1 * l_lt_stk_sav1_tax_break_per);
605 p_yea_info.lt_stock_saving_tax_break := p_yea_info.lt_stock_saving_tax_break + trunc(p_yea_info.lt_stock_saving2 * l_lt_stk_sav2_tax_break_per);
606 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.lt_stock_saving_tax_break;
607 end if;
608 ----------------------------------------------------------------
609 -- Housing Expense Tax Break
610 ----------------------------------------------------------------
611 if p_yea_info.housing_loan_interest_repay > 0 then
612 p_yea_info.housing_exp_tax_break := trunc(p_yea_info.housing_loan_interest_repay * l_housinloan_int_repay_per);
613 /* Need the actual housing loan interest tax break for special tax calculation */
614 if p_yea_info.total_tax_break < p_yea_info.calc_tax then
615 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);
616 end if;
617 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.housing_exp_tax_break;
618 end if;
619 end if;
620 ----------------------------------------------------------------
621 -- Overseas Tax Break
622 ----------------------------------------------------------------
623 if p_yea_info.ovstb_tax_paid_date is not null then
624 if p_yea_info.taxable_income > 0 then
625
626 -- Bug # 2706312
627
628 open csr_ovs_def_bal_id;
629 fetch csr_ovs_def_bal_id into l_ovs_def_bal_id;
630
631 if csr_ovs_def_bal_id%found then
632
633 l_ovs_earnings_bal:= pay_balance_pkg.get_value
634 (p_defined_balance_id => l_ovs_def_bal_id
635 ,p_assignment_id => p_assignment_id
636 ,p_virtual_date => p_effective_date
637 ,p_always_get_db_item => FALSE);
638 end if;
639 close csr_ovs_def_bal_id;
640
641 p_yea_info.ovstb_taxable := p_yea_info.ovstb_taxable + l_ovs_earnings_bal;
642 --
646 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);
643 -- Calculate Maximum Tax Break allowed in this calendar year.
644 --
645
647
648 p_yea_info.ovs_tax_break := least(p_yea_info.ovstb_tax, l_dummy);
649 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.ovs_tax_break;
650 end if;
651 end if;
652 ----------------------------------------------------------------
653 -- Foreign Worker Tax Break and set Annual Tax to "0"
654 ----------------------------------------------------------------
655 if p_yea_info.fwtb_immigration_purpose is not null then
656 p_yea_info.foreign_worker_tax_break := p_yea_info.calc_tax;
657 p_yea_info.annual_itax := 0;
658 p_yea_info.annual_rtax := 0;
659 p_yea_info.annual_stax := 0;
660 if p_yea_info.fwtb_immigration_purpose = 'G' then
661 p_yea_info.foreign_worker_tax_break1 := p_yea_info.foreign_worker_tax_break;
662 else
663 p_yea_info.foreign_worker_tax_break2 := p_yea_info.foreign_worker_tax_break;
664 end if;
665 else
666 ----------------------------------------------------------------
667 -- Annual Tax
668 ----------------------------------------------------------------
669 -- Bug # 2767493
670 if p_yea_info.total_tax_break > p_yea_info.calc_tax then
671 p_yea_info.total_tax_break := p_yea_info.calc_tax;
672 end if;
673 --
674 p_yea_info.annual_itax := trunc(greatest(p_yea_info.calc_tax - p_yea_info.total_tax_break, 0),0);
675 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * l_annual_itax_per,0);
676 if l_calc_tax_for_stax > 0 then
677 p_yea_info.annual_stax := trunc((l_calc_tax_for_stax - p_yea_info.calc_tax) * l_annual_stax_per);
678 end if;
679 if l_net_housing_exp_tax_break > 0 then
680 p_yea_info.annual_stax := p_yea_info.annual_stax + trunc(l_net_housing_exp_tax_break * l_housinexp_tax_break);
681 end if;
682 p_yea_info.annual_stax := trunc(p_yea_info.annual_stax,0);
683 end if;
684 ----------------------------------------------------------------
685 -- Calculate Tax Adjustment
686 ----------------------------------------------------------------
687 --Bug 2605158 : truncating the last Won digit after the final calculation of
688 -- annual_itax , annual_rtax and annual_stax.
689
690 p_yea_info.itax_adj := trunc(p_yea_info.annual_itax - p_yea_info.prev_itax - p_yea_info.cur_itax,-1);
691 p_yea_info.rtax_adj := trunc(p_yea_info.annual_rtax - p_yea_info.prev_rtax - p_yea_info.cur_rtax,-1);
692 p_yea_info.stax_adj := trunc(p_yea_info.annual_stax - p_yea_info.prev_stax - p_yea_info.cur_stax,-1);
693 -- Bug 2878937
694 if p_yea_info.itax_adj >= 0 and p_yea_info.itax_adj < 1000 then
695 p_yea_info.itax_adj := 0;
696 p_yea_info.rtax_adj := 0;
697 p_yea_info.stax_adj := 0;
698 p_tax_adj_warning := TRUE;
699 end if;
700
701 end yea;
702 --
703 end pay_kr_yea20020101_pkg;