[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA00010101_PKG
Source
1 package body pay_kr_yea00010101_pkg as
2 /* $Header: pykryea0.pkb 120.2 2005/11/24 02:31:57 viagarwa 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 is
10 l_addend number;
11 l_multiplier number;
12 l_subtrahend number;
13 l_dummy number;
14 l_net_housing_exp_tax_break number;
15 l_calc_tax_for_stax number;
16 l_cuml_special_exem number default 0 ; -- Adds up all special exemptions given for tracking purpose, for limit on exemptions.
17 --
18 function calc_tax(p_taxation_base in number) return number
19 is
20 begin
21 if p_taxation_base > 0 then
22 l_multiplier := to_number(hruserdt.get_table_value(
23 p_bus_group_id => p_business_group_id,
24 p_table_name => 'CALC_TAX',
25 p_col_name => 'MULTIPLIER',
26 p_row_value => to_char(p_taxation_base),
27 p_effective_date => p_effective_date));
28 l_subtrahend := to_number(hruserdt.get_table_value(
29 p_bus_group_id => p_business_group_id,
30 p_table_name => 'CALC_TAX',
31 p_col_name => 'SUBTRAHEND',
32 p_row_value => to_char(p_taxation_base),
33 p_effective_date => p_effective_date));
34 return trunc(p_taxation_base * l_multiplier / 100) - l_subtrahend;
35 else
36 return 0;
37 end if;
38 end calc_tax;
39 begin
40 ----------------------------------------------------------------
41 -- Basic Income Exemption
42 -- Taxable Income
43 ----------------------------------------------------------------
44 if p_yea_info.taxable > 0 then
45 l_addend := to_number(hruserdt.get_table_value(
46 p_bus_group_id => p_business_group_id,
47 p_table_name => 'BASIC_INCOME_EXEM',
48 p_col_name => 'ADDEND',
49 p_row_value => to_char(p_yea_info.taxable),
50 p_effective_date => p_effective_date));
51 l_multiplier := to_number(hruserdt.get_table_value(
52 p_bus_group_id => p_business_group_id,
53 p_table_name => 'BASIC_INCOME_EXEM',
54 p_col_name => 'MULTIPLIER',
55 p_row_value => to_char(p_yea_info.taxable),
56 p_effective_date => p_effective_date));
57 l_subtrahend := to_number(hruserdt.get_table_value(
58 p_bus_group_id => p_business_group_id,
59 p_table_name => 'BASIC_INCOME_EXEM',
60 p_col_name => 'SUBTRAHEND',
61 p_row_value => to_char(p_yea_info.taxable),
62 p_effective_date => p_effective_date));
63 p_yea_info.basic_income_exem := l_addend + trunc(l_multiplier / 100 * (p_yea_info.taxable - l_subtrahend));
64 p_yea_info.taxable_income := p_yea_info.taxable - p_yea_info.basic_income_exem;
65 end if;
66 p_yea_info.taxable_income2 := p_yea_info.taxable_income;
67 ----------------------------------------------------------------
68 -- Employee Tax Exemption
69 ----------------------------------------------------------------
70 p_yea_info.ee_tax_exem := 1000000;
71 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.ee_tax_exem;
72 ----------------------------------------------------------------
73 -- Dependent Tax Exemption
74 ----------------------------------------------------------------
75 if p_yea_info.dpnt_spouse_flag = 'Y' then
76 p_yea_info.dpnt_spouse_tax_exem := 1000000;
77 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_spouse_tax_exem;
78 l_dummy := 1;
79 else
80 l_dummy := 0;
81 end if;
82 if p_yea_info.num_of_dpnts > 0 then
83 p_yea_info.dpnt_tax_exem := p_yea_info.num_of_dpnts * 1000000;
84 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.dpnt_tax_exem;
85 l_dummy := l_dummy + p_yea_info.num_of_dpnts;
86 end if;
87 if p_yea_info.num_of_ageds > 0 then
88 p_yea_info.aged_tax_exem := p_yea_info.num_of_ageds * 500000;
89 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.aged_tax_exem;
90 end if;
91 if p_yea_info.num_of_disableds > 0 then
92 p_yea_info.disabled_tax_exem := p_yea_info.num_of_disableds * 500000;
93 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.disabled_tax_exem;
94 end if;
95 if p_yea_info.female_ee_flag = 'Y' then
96 p_yea_info.female_ee_tax_exem := 500000;
97 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.female_ee_tax_exem;
98 end if;
99 if p_yea_info.num_of_children > 0 then
100 p_yea_info.child_tax_exem := p_yea_info.num_of_children * 500000;
101 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.child_tax_exem;
102 end if;
103 ----------------------------------------------------------------
104 -- Supplemental Tax Exemption
105 ----------------------------------------------------------------
106 if l_dummy <= 1 then
107 if l_dummy = 0 then
108 p_yea_info.supp_tax_exem := 1000000;
109 elsif l_dummy = 1 then
110 p_yea_info.supp_tax_exem := 500000;
111 end if;
112 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.supp_tax_exem;
113 end if;
114 ----------------------------------------------------------------
115 -- Special Tax Exemption
116 ----------------------------------------------------------------
117 l_cuml_special_exem := 0; --Bug 4709683
118 if p_yea_info.non_resident_flag = 'N' then
119 --
120 -- Insurance Premium Tax Exemption
121 --
122 if p_yea_info.hi_prem > 0 then
123 p_yea_info.hi_prem_tax_exem := p_yea_info.hi_prem;
124 end if;
125 if p_yea_info.ei_prem > 0 then
126 p_yea_info.ei_prem_tax_exem := p_yea_info.ei_prem;
127 end if;
128 if p_yea_info.pers_ins_prem > 0 then
129 p_yea_info.pers_ins_prem_tax_exem := least(p_yea_info.pers_ins_prem, 700000);
130 end if;
131 if p_yea_info.disabled_ins_prem > 0 then
132 p_yea_info.disabled_ins_prem_tax_exem := least(p_yea_info.disabled_ins_prem, 1000000);
133 end if;
134 p_yea_info.ins_prem_tax_exem := p_yea_info.hi_prem_tax_exem
135 + p_yea_info.ei_prem_tax_exem
136 + p_yea_info.pers_ins_prem_tax_exem
137 + p_yea_info.disabled_ins_prem_tax_exem;
138
139 -- Bug 4119483: Apply upper limit based on cumulative special exemption
140 p_yea_info.ins_prem_tax_exem := least(
141 p_yea_info.ins_prem_tax_exem,
142 greatest(
143 0,
144 p_yea_info.taxable_income2 - l_cuml_special_exem
145 )
146 ) ;
147
148 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.ins_prem_tax_exem ;
149 -- End of 4119483
150
151 --
152 -- Medical Expense Tax Exemption
153 --
154 l_dummy := p_yea_info.med_exp + p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged;
155 if l_dummy > 0 then
156 l_dummy := l_dummy - trunc(greatest(p_yea_info.taxable, 0) * 0.03);
157 if l_dummy > 0 then
158 p_yea_info.max_med_exp_tax_exem := least(p_yea_info.med_exp_disabled + p_yea_info.med_exp_aged, l_dummy - 3000000);
159 p_yea_info.med_exp_tax_exem := 3000000 + p_yea_info.max_med_exp_tax_exem;
160 end if;
161 end if;
162 -- Bug 4119483: Apply upper limit based on cumulative special exemption
163 p_yea_info.med_exp_tax_exem := least(
164 p_yea_info.med_exp_tax_exem,
165 greatest(
166 0,
167 p_yea_info.taxable_income2 - l_cuml_special_exem
168 )
169 ) ;
170
171 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.med_exp_tax_exem ;
172 -- End of 4119483
173 --
174 -- Education Expense Tax Exemption
175 --
176 p_yea_info.educ_exp_tax_exem := p_yea_info.ee_educ_exp;
177 for i in 1..p_yea_info.dpnt_educ_contact_type_tbl.count loop
178 if p_yea_info.dpnt_educ_school_type_tbl(i) = 'U' then
179 l_dummy := 3000000;
180 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'H' then
181 l_dummy := 1500000;
182 elsif p_yea_info.dpnt_educ_school_type_tbl(i) = 'P' then
183 l_dummy := 1000000;
184 end if;
185 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);
186 if p_yea_info.dpnt_educ_contact_type_tbl(i) = 'S' then
187 p_yea_info.spouse_educ_exp := p_yea_info.spouse_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
188 else
189 p_yea_info.dpnt_educ_exp := p_yea_info.dpnt_educ_exp + p_yea_info.dpnt_educ_exp_tbl(i);
190 end if;
191 end loop;
192 --
193 -- Bug 4119483: Apply upper limit based on cumulative special exemption
194 p_yea_info.educ_exp_tax_exem := least(
195 p_yea_info.educ_exp_tax_exem,
196 greatest(
197 0,
198 p_yea_info.taxable_income2 - l_cuml_special_exem
199 )
200 ) ;
201 --
202 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.educ_exp_tax_exem ;
203 -- End of 4119483
204 --
205 -- Housing Expense Tax Exemption
206 --
207 if p_yea_info.housing_saving > 0
208 or p_yea_info.housing_loan_repay > 0
209 or p_yea_info.lt_housing_loan_interest_repay > 0 then
210 p_yea_info.max_housing_exp_tax_exem := trunc((p_yea_info.housing_saving + p_yea_info.housing_loan_repay) * 0.4)
211 + p_yea_info.lt_housing_loan_interest_repay;
212 p_yea_info.housing_exp_tax_exem := least(p_yea_info.max_housing_exp_tax_exem, 3000000);
213 --
214 -- Bug 4119483: Apply upper limit based on cumulative special exemption
215 p_yea_info.housing_exp_tax_exem := least(
216 p_yea_info.housing_exp_tax_exem,
217 greatest(
218 0,
219 p_yea_info.taxable_income2 - l_cuml_special_exem
220 )
221 ) ;
222 --
223 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.housing_exp_tax_exem ;
224 -- End of 4119483
225 end if;
226 --
227 -- Donation Tax Exemption
228 --
229 p_yea_info.donation1_tax_exem := p_yea_info.donation1;
230 if p_yea_info.political_donation1 > 0 then
231 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation1, 100000000);
232 end if;
233 if p_yea_info.political_donation2 > 0 then
234 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation2, 20000000);
235 end if;
236 if p_yea_info.political_donation3 > 0 then
237 p_yea_info.donation1_tax_exem := p_yea_info.donation1_tax_exem + least(p_yea_info.political_donation3,
238 greatest(trunc(greatest(p_yea_info.taxable, 0) * 0.03), 100000000));
239 end if;
240 if p_yea_info.donation2 > 0 then
241 if p_yea_info.taxable > p_yea_info.donation1_tax_exem then
242 p_yea_info.max_donation2_tax_exem := trunc((p_yea_info.taxable - p_yea_info.donation1_tax_exem) * 0.1);
243 p_yea_info.donation2_tax_exem := least(p_yea_info.donation2, p_yea_info.max_donation2_tax_exem);
244 end if;
245 end if;
246 p_yea_info.donation_tax_exem := p_yea_info.donation1_tax_exem + p_yea_info.donation2_tax_exem;
247 --
248 -- Bug 4119483: Apply upper limit based on cumulative special exemption
249 p_yea_info.donation_tax_exem := least(
250 p_yea_info.donation_tax_exem,
251 greatest(
252 0,
253 p_yea_info.taxable_income2 - l_cuml_special_exem
254 )
255 ) ;
256 --
257 l_cuml_special_exem := l_cuml_special_exem + p_yea_info.donation_tax_exem ;
258 -- End of 4119483
259 --
260 --
261 -- Special Tax Exemption
262 --
263 p_yea_info.sp_tax_exem := p_yea_info.ins_prem_tax_exem
264 + p_yea_info.med_exp_tax_exem
265 + p_yea_info.educ_exp_tax_exem
266 + p_yea_info.housing_exp_tax_exem
267 + p_yea_info.donation_tax_exem;
268 if p_yea_info.sp_tax_exem < 600000 then
269 p_yea_info.sp_tax_exem := 0;
270 p_yea_info.std_sp_tax_exem := 600000;
271 end if;
272 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.sp_tax_exem - p_yea_info.std_sp_tax_exem;
273 end if;
274 ----------------------------------------------------------------
275 -- National Pension Premium Tax Exemption
276 ----------------------------------------------------------------
277 if p_yea_info.np_prem > 0 then
278 p_yea_info.np_prem_tax_exem := p_yea_info.np_prem;
279 p_yea_info.taxable_income2 := p_yea_info.taxable_income2 - p_yea_info.np_prem_tax_exem;
280 end if;
281 ----------------------------------------------------------------
282 -- Taxable Income2
283 ----------------------------------------------------------------
284 p_yea_info.taxable_income2 := greatest(p_yea_info.taxable_income2, 0);
285 p_yea_info.taxation_base := p_yea_info.taxable_income2;
286 --
287 if p_yea_info.non_resident_flag = 'N' then
288 ----------------------------------------------------------------
289 -- Personal Pension Premium Tax Exemption
290 ----------------------------------------------------------------
291 if p_yea_info.pers_pension_prem > 0 then
292 p_yea_info.pers_pension_prem_tax_exem := least(trunc(p_yea_info.pers_pension_prem * 0.4), 720000);
293 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_prem_tax_exem;
294 end if;
295 ----------------------------------------------------------------
296 -- Personal Pension Saving Tax Exemption
297 ----------------------------------------------------------------
298 if p_yea_info.pers_pension_saving > 0 then
299 p_yea_info.pers_pension_saving_tax_exem := least(p_yea_info.pers_pension_saving, 2400000);
300 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.pers_pension_saving_tax_exem;
301 end if;
302 ----------------------------------------------------------------
303 -- Credit Card Expense Tax Exem
304 ----------------------------------------------------------------
305 if p_yea_info.credit_card_exp > 0 then
306 l_dummy := p_yea_info.credit_card_exp - trunc(greatest(p_yea_info.taxable, 0) * 0.1);
307 if l_dummy > 0 then
308 p_yea_info.credit_card_exp_tax_exem := least(trunc(l_dummy * 0.2), 5000000);
309 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.credit_card_exp_tax_exem;
310 end if;
311 end if;
312 ----------------------------------------------------------------
316 or p_yea_info.invest_partner_fin2 > 0 then
313 -- Investment Partnership Financing Tax Exemption
314 ----------------------------------------------------------------
315 if p_yea_info.invest_partner_fin1 > 0
317 if p_yea_info.taxable > 0 then
318 p_yea_info.invest_partner_fin_tax_exem := least(trunc(p_yea_info.invest_partner_fin1 * 0.2) + trunc(p_yea_info.invest_partner_fin2 * 0.3),
319 trunc((p_yea_info.taxable) * 0.7));
320 /* Calculated Tax using Taxation Base without Investment Partnership Financing Tax Exemption */
321 l_calc_tax_for_stax := calc_tax(p_yea_info.taxation_base);
322 p_yea_info.taxation_base := p_yea_info.taxation_base - p_yea_info.invest_partner_fin_tax_exem;
323 end if;
324 end if;
325 end if;
326 ----------------------------------------------------------------
327 -- Taxation Base
328 ----------------------------------------------------------------
329 p_yea_info.taxation_base := greatest(p_yea_info.taxation_base, 0);
330 ----------------------------------------------------------------
331 -- Calculated Tax
332 ----------------------------------------------------------------
333 p_yea_info.calc_tax := calc_tax(p_yea_info.taxation_base);
334 ----------------------------------------------------------------
335 -- Basic Tax Break
336 -- This tax break is based on "Estimated Calculated Tax
337 -- based on Taxable Earnings without Special Irregular Bonus".
338 ----------------------------------------------------------------
339 if p_yea_info.calc_tax > 0 and p_yea_info.taxable > 0 then
340 l_dummy := trunc( p_yea_info.calc_tax
341 * (p_yea_info.taxable - p_yea_info.sp_irreg_bonus)
342 / p_yea_info.taxable);
343 else
344 l_dummy := 0;
345 end if;
346 --
347 if l_dummy > 0 then
348 l_addend := to_number(hruserdt.get_table_value(
349 p_bus_group_id => p_business_group_id,
350 p_table_name => 'BASIC_TAX_BREAK',
351 p_col_name => 'ADDEND',
352 p_row_value => to_char(l_dummy),
353 p_effective_date => p_effective_date));
354 l_subtrahend := to_number(hruserdt.get_table_value(
355 p_bus_group_id => p_business_group_id,
356 p_table_name => 'BASIC_TAX_BREAK',
357 p_col_name => 'SUBTRAHEND',
358 p_row_value => to_char(l_dummy),
359 p_effective_date => p_effective_date));
360 l_multiplier := to_number(hruserdt.get_table_value(
361 p_bus_group_id => p_business_group_id,
362 p_table_name => 'BASIC_TAX_BREAK',
363 p_col_name => 'MULTIPLIER',
364 p_row_value => to_char(l_dummy),
365 p_effective_date => p_effective_date));
366 p_yea_info.basic_tax_break := least(trunc(l_addend + trunc((l_dummy - l_subtrahend) * l_multiplier / 100)), 400000);
367 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.basic_tax_break;
368 end if;
369 --
370 if p_yea_info.non_resident_flag = 'N' then
371 ----------------------------------------------------------------
372 -- Stock Saving Tax Break
373 ----------------------------------------------------------------
374 if p_yea_info.stock_saving > 0 then
375 p_yea_info.stock_saving_tax_break := trunc(p_yea_info.stock_saving * 0.05);
376 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.stock_saving_tax_break;
377 end if;
378 ----------------------------------------------------------------
379 -- Longterm Stock Saving1 Tax Break
380 ----------------------------------------------------------------
381 if p_yea_info.lt_stock_saving1 > 0 then
382 p_yea_info.lt_stock_saving_tax_break := trunc(p_yea_info.lt_stock_saving1 * 0.05);
383 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.lt_stock_saving_tax_break;
384 end if;
385 ----------------------------------------------------------------
386 -- Longterm Stock Saving2 Tax Break
387 ----------------------------------------------------------------
388 if p_yea_info.lt_stock_saving2 > 0 then
389 p_yea_info.lt_stock_saving_tax_break := p_yea_info.lt_stock_saving_tax_break + trunc(p_yea_info.lt_stock_saving2 * 0.07);
390 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.lt_stock_saving_tax_break;
391 end if;
392 ----------------------------------------------------------------
393 -- Housing Expense Tax Break
394 ----------------------------------------------------------------
395 if p_yea_info.housing_loan_interest_repay > 0 then
396 p_yea_info.housing_exp_tax_break := trunc(p_yea_info.housing_loan_interest_repay * 0.3);
397 /* Need the actual housing loan interest tax break for special tax calculation */
398 if p_yea_info.total_tax_break < p_yea_info.calc_tax then
399 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);
400 end if;
401 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.housing_exp_tax_break;
402 end if;
403 end if;
404 ----------------------------------------------------------------
405 -- Overseas Tax Break
406 ----------------------------------------------------------------
407 if p_yea_info.ovstb_tax_paid_date is not null then
408 if p_yea_info.taxable_income > 0 then
409 --
410 -- Calculate Maximum Tax Break allowed in this calendar year.
411 --
412 l_dummy := trunc(p_yea_info.calc_tax
413 * greatest((p_yea_info.ovstb_taxable - trunc(p_yea_info.ovstb_taxable_subj_tax_break * p_yea_info.ovstb_tax_break_rate / 100)), 0)
414 / p_yea_info.taxable
415 );
416 p_yea_info.ovs_tax_break := least(p_yea_info.ovstb_tax, l_dummy);
417 p_yea_info.total_tax_break := p_yea_info.total_tax_break + p_yea_info.ovs_tax_break;
418 end if;
419 end if;
420 ----------------------------------------------------------------
421 -- Foreign Worker Tax Break and set Annual Tax to "0"
422 ----------------------------------------------------------------
423 if p_yea_info.fwtb_immigration_purpose is not null then
427 p_yea_info.annual_stax := 0;
424 p_yea_info.foreign_worker_tax_break := p_yea_info.calc_tax;
425 p_yea_info.annual_itax := 0;
426 p_yea_info.annual_rtax := 0;
428 if p_yea_info.fwtb_immigration_purpose = 'G' then
429 p_yea_info.foreign_worker_tax_break1 := p_yea_info.foreign_worker_tax_break;
430 else
431 p_yea_info.foreign_worker_tax_break2 := p_yea_info.foreign_worker_tax_break;
432 end if;
433 else
434 ----------------------------------------------------------------
435 -- Annual Tax
436 ----------------------------------------------------------------
437 p_yea_info.annual_itax := trunc(greatest(p_yea_info.calc_tax - p_yea_info.total_tax_break, 0), -1);
438 p_yea_info.annual_rtax := trunc(p_yea_info.annual_itax * 0.1, -1);
439 if l_calc_tax_for_stax > 0 then
440 p_yea_info.annual_stax := trunc((l_calc_tax_for_stax - p_yea_info.calc_tax) * 0.2);
441 end if;
442 if l_net_housing_exp_tax_break > 0 then
443 p_yea_info.annual_stax := p_yea_info.annual_stax + trunc(l_net_housing_exp_tax_break * 0.2);
444 end if;
445 p_yea_info.annual_stax := trunc(p_yea_info.annual_stax, -1);
446 end if;
447 ----------------------------------------------------------------
448 -- Calculate Tax Adjustment
449 ----------------------------------------------------------------
450 p_yea_info.itax_adj := p_yea_info.annual_itax - p_yea_info.prev_itax - p_yea_info.cur_itax;
451 p_yea_info.rtax_adj := p_yea_info.annual_rtax - p_yea_info.prev_rtax - p_yea_info.cur_rtax;
452 p_yea_info.stax_adj := p_yea_info.annual_stax - p_yea_info.prev_stax - p_yea_info.cur_stax;
453
454 end yea;
455 --
456 end pay_kr_yea00010101_pkg;