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