[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_PAYKRYLG_PKG
Source
1 package body pay_kr_paykrylg_pkg as
2 /* $Header: paykrylg.pkb 120.5 2007/10/12 12:32:45 vaisriva noship $ */
3 --
4 -- Global Variables
5 --
6 type t_defined_balance_id is record(
7 taxable_mth number,
8 taxable_bon number,
9 sp_irreg_bonus_mth number,
10 sp_irreg_bonus_bon number,
11 stck_pur_opt_exec_earn_mth number, -- Bug 6470526
12 stck_pur_opt_exec_earn_bon number, -- Bug 6470526
13 itax_mth number,
14 itax_bon number,
15 rtax_mth number,
16 rtax_bon number,
17 stax_mth number,
18 stax_bon number,
19 non_taxable_ovs_mth number,
20 non_taxable_ovs_bon number,
21 non_taxable_ovt_mth number,
22 non_taxable_ovt_bon number,
23 research_payment_mth number, -- Bug 6470526
24 research_payment_bon number, -- Bug 6470526
25 non_taxable_mth number,
26 non_taxable_bon number,
27 fw_tax_break_mth number, --3546994
28 fw_tax_break_bon number,
29 -- Bug 4322981
30 np_prem_mth number,
31 hi_prem_mth number,
32 ei_prem_mth number,
33 donation_mth number,
34 np_prem_bon number,
35 hi_prem_bon number,
36 addl_non_tax_mth number, -- Bug 5999074
37 addl_non_tax_bon number,
38 ei_prem_bon number,
39 donation_bon number
40 -- End of 4322981
41 ); --3546994
42 g_defined_balance_id t_defined_balance_id;
43 ------------------------------------------------------------------------
44 procedure data(
45 p_assignment_action_id in number,
46 p_taxable out nocopy number,
47 p_basic_income_exem out nocopy number,
48 p_taxable_income out nocopy number,
49 p_ee_tax_exem out nocopy number,
50 p_dpnt_spouse_flag out nocopy varchar2,
51 p_dpnt_spouse_tax_exem out nocopy number,
52 p_num_of_underaged_dpnts out nocopy number,
53 p_num_of_aged_dpnts out nocopy number,
54 p_dpnt_tax_exem out nocopy number,
55 p_num_of_ageds out nocopy number,
56 p_aged_tax_exem out nocopy number,
57 p_num_of_disableds out nocopy number,
58 p_disabled_tax_exem out nocopy number,
59 p_female_ee_flag out nocopy varchar2,
60 p_female_ee_tax_exem out nocopy number,
61 p_num_of_children out nocopy number,
62 p_child_tax_exem out nocopy number,
63 p_supp_tax_exem out nocopy number,
64 p_hi_prem out nocopy number,
65 p_ei_prem out nocopy number,
66 p_pers_ins_name out nocopy varchar2,
67 p_pers_ins_prem out nocopy number,
68 p_disabled_ins_prem out nocopy number,
69 p_ins_prem_tax_exem out nocopy number,
70 p_med_exp out nocopy number,
71 p_med_exp_aged out nocopy number,
72 p_med_exp_disabled out nocopy number,
73 p_max_med_exp_tax_exem out nocopy number,
74 p_med_exp_tax_exem out nocopy number,
75 p_ee_educ_exp out nocopy number,
76 p_spouse_educ_exp out nocopy number,
77 p_disabled_educ_exp out nocopy number,
78 p_dpnt_educ_exp out nocopy number,
79 p_educ_exp_tax_exem out nocopy number,
80 p_housing_saving_type_meaning out nocopy varchar2,
81 p_housing_saving out nocopy number,
82 p_housing_purchase_date out nocopy date,
83 p_housing_loan_date out nocopy date,
84 p_housing_loan_repay out nocopy number,
85 p_lt_housing_loan_date out nocopy date,
86 p_lt_housing_loan_int_repay out nocopy number,
87 p_max_housing_exp_tax_exem out nocopy number,
88 p_housing_exp_tax_exem out nocopy number,
89 p_donation1_tax_exem out nocopy number,
90 p_donation2 out nocopy number,
91 p_max_donation2_tax_exem out nocopy number,
92 p_donation2_tax_exem out nocopy number,
93 p_donation_tax_exem out nocopy number,
94 p_sp_tax_exem out nocopy number,
95 p_std_sp_tax_exem out nocopy number,
96 p_np_prem out nocopy number,
97 p_np_prem_tax_exem out nocopy number,
98 p_pers_pension_prem_tax out nocopy number,
99 p_pers_pension_saving_tax out nocopy number,
100 p_invest_partner_fin_tax out nocopy number,
101 p_credit_card_exp_tax out nocopy number,
102 p_emp_st_own_plan_cont out nocopy number,
103 p_pers_pension_prem_tax_exem out nocopy number,
104 p_pers_pension_saving_tax_exem out nocopy number,
105 p_invest_partner_fin_tax_exem out nocopy number,
106 p_credit_card_exp_tax_exem out nocopy number,
107 p_emp_st_own_plan_cont_exem out nocopy number,
108 p_taxation_base out nocopy number,
109 p_calc_tax out nocopy number,
110 p_basic_tax_break out nocopy number,
111 p_housing_exp_tax_break out nocopy number,
112 p_stock_saving_tax_break out nocopy number,
113 p_ovstb_territory_short_name out nocopy varchar2,
114 p_ovstb_tax_paid_date out nocopy date,
115 p_ovstb_tax_foreign_currency out nocopy number,
116 p_ovstb_tax out nocopy number,
117 p_ovstb_application_date out nocopy date,
118 p_ovstb_submission_date out nocopy date,
119 p_ovs_tax_break out nocopy number,
120 p_lt_stock_saving_tax_break out nocopy number,
121 p_total_tax_break out nocopy number,
122 p_fwtb_immigration_purpose out nocopy varchar2,
123 p_fwtb_contract_date out nocopy date,
124 p_fwtb_expiry_date out nocopy date,
125 p_fwtb_application_date out nocopy date,
126 p_fwtb_submission_date out nocopy date,
127 p_foreign_worker_tax_break1 out nocopy number,
128 p_foreign_worker_tax_break2 out nocopy number,
129 p_annual_itax out nocopy number,
130 p_annual_rtax out nocopy number,
131 p_annual_stax out nocopy number,
132 p_prev_itax out nocopy number,
133 p_prev_rtax out nocopy number,
134 p_prev_stax out nocopy number,
135 p_cur_annual_itax out nocopy number,
136 p_cur_annual_rtax out nocopy number,
137 p_cur_annual_stax out nocopy number,
138 p_cur_itax out nocopy number,
139 p_cur_rtax out nocopy number,
140 p_cur_stax out nocopy number,
141 p_itax_adj out nocopy number,
142 p_itax_refund out nocopy number,
143 p_rtax_adj out nocopy number,
144 p_rtax_refund out nocopy number,
145 p_stax_adj out nocopy number,
146 p_stax_refund out nocopy number,
147 p_effective_date1 out nocopy date,
148 p_taxable_mth1 out nocopy number,
149 p_taxable_bon1 out nocopy number,
150 p_sp_irreg_bonus1 out nocopy number,
151 p_taxable1 out nocopy number,
152 p_itax1 out nocopy number,
153 p_rtax1 out nocopy number,
154 p_stax1 out nocopy number,
155 -- Bug 4322981
156 p_np_dedc1 out nocopy number,
157 p_hi_dedc1 out nocopy number,
158 p_ei_dedc1 out nocopy number,
159 p_donation_dedc1 out nocopy number,
160 -- End of 4322981
161 p_non_taxable_ovs1 out nocopy number,
162 p_non_taxable_ovt1 out nocopy number,
163 p_non_taxable_oth1 out nocopy number,
164 p_effective_date2 out nocopy date,
165 p_taxable_mth2 out nocopy number,
166 p_taxable_bon2 out nocopy number,
167 p_sp_irreg_bonus2 out nocopy number,
168 p_taxable2 out nocopy number,
169 p_itax2 out nocopy number,
170 p_rtax2 out nocopy number,
171 p_stax2 out nocopy number,
172 -- Bug 4322981
173 p_np_dedc2 out nocopy number,
174 p_hi_dedc2 out nocopy number,
175 p_ei_dedc2 out nocopy number,
176 p_donation_dedc2 out nocopy number,
177 -- End of 4322981
178 p_non_taxable_ovs2 out nocopy number,
179 p_non_taxable_ovt2 out nocopy number,
180 p_non_taxable_oth2 out nocopy number,
181 p_effective_date3 out nocopy date,
182 p_taxable_mth3 out nocopy number,
183 p_taxable_bon3 out nocopy number,
184 p_sp_irreg_bonus3 out nocopy number,
185 p_taxable3 out nocopy number,
186 p_itax3 out nocopy number,
187 p_rtax3 out nocopy number,
188 p_stax3 out nocopy number,
189 -- Bug 4322981
190 p_np_dedc3 out nocopy number,
191 p_hi_dedc3 out nocopy number,
192 p_ei_dedc3 out nocopy number,
193 p_donation_dedc3 out nocopy number,
194 -- End of 4322981
195 p_non_taxable_ovs3 out nocopy number,
196 p_non_taxable_ovt3 out nocopy number,
197 p_non_taxable_oth3 out nocopy number,
198 p_effective_date4 out nocopy date,
199 p_taxable_mth4 out nocopy number,
200 p_taxable_bon4 out nocopy number,
201 p_sp_irreg_bonus4 out nocopy number,
202 p_taxable4 out nocopy number,
203 p_itax4 out nocopy number,
204 p_rtax4 out nocopy number,
205 p_stax4 out nocopy number,
206 -- Bug 4322981
207 p_np_dedc4 out nocopy number,
208 p_hi_dedc4 out nocopy number,
209 p_ei_dedc4 out nocopy number,
210 p_donation_dedc4 out nocopy number,
211 -- End of 4322981
212 p_non_taxable_ovs4 out nocopy number,
213 p_non_taxable_ovt4 out nocopy number,
214 p_non_taxable_oth4 out nocopy number,
215 p_effective_date5 out nocopy date,
216 p_taxable_mth5 out nocopy number,
217 p_taxable_bon5 out nocopy number,
218 p_sp_irreg_bonus5 out nocopy number,
219 p_taxable5 out nocopy number,
220 p_itax5 out nocopy number,
221 p_rtax5 out nocopy number,
222 p_stax5 out nocopy number,
223 -- Bug 4322981
224 p_np_dedc5 out nocopy number,
225 p_hi_dedc5 out nocopy number,
226 p_ei_dedc5 out nocopy number,
227 p_donation_dedc5 out nocopy number,
228 -- End of 4322981
229 p_non_taxable_ovs5 out nocopy number,
230 p_non_taxable_ovt5 out nocopy number,
231 p_non_taxable_oth5 out nocopy number,
232 p_effective_date6 out nocopy date,
233 p_taxable_mth6 out nocopy number,
234 p_taxable_bon6 out nocopy number,
235 p_sp_irreg_bonus6 out nocopy number,
236 p_taxable6 out nocopy number,
237 p_itax6 out nocopy number,
238 p_rtax6 out nocopy number,
239 p_stax6 out nocopy number,
240 -- Bug 4322981
241 p_np_dedc6 out nocopy number,
242 p_hi_dedc6 out nocopy number,
243 p_ei_dedc6 out nocopy number,
244 p_donation_dedc6 out nocopy number,
245 -- End of 4322981
246 p_non_taxable_ovs6 out nocopy number,
247 p_non_taxable_ovt6 out nocopy number,
248 p_non_taxable_oth6 out nocopy number,
249 p_effective_date7 out nocopy date,
250 p_taxable_mth7 out nocopy number,
251 p_taxable_bon7 out nocopy number,
252 p_sp_irreg_bonus7 out nocopy number,
253 p_taxable7 out nocopy number,
254 p_itax7 out nocopy number,
255 p_rtax7 out nocopy number,
256 p_stax7 out nocopy number,
257 -- Bug 4322981
258 p_np_dedc7 out nocopy number,
259 p_hi_dedc7 out nocopy number,
260 p_ei_dedc7 out nocopy number,
261 p_donation_dedc7 out nocopy number,
262 -- End of 4322981
263 p_non_taxable_ovs7 out nocopy number,
264 p_non_taxable_ovt7 out nocopy number,
265 p_non_taxable_oth7 out nocopy number,
266 p_effective_date8 out nocopy date,
267 p_taxable_mth8 out nocopy number,
268 p_taxable_bon8 out nocopy number,
269 p_sp_irreg_bonus8 out nocopy number,
270 p_taxable8 out nocopy number,
271 p_itax8 out nocopy number,
272 p_rtax8 out nocopy number,
273 p_stax8 out nocopy number,
274 -- Bug 4322981
275 p_np_dedc8 out nocopy number,
276 p_hi_dedc8 out nocopy number,
277 p_ei_dedc8 out nocopy number,
278 p_donation_dedc8 out nocopy number,
279 -- End of 4322981
280 p_non_taxable_ovs8 out nocopy number,
281 p_non_taxable_ovt8 out nocopy number,
282 p_non_taxable_oth8 out nocopy number,
283 p_effective_date9 out nocopy date,
284 p_taxable_mth9 out nocopy number,
285 p_taxable_bon9 out nocopy number,
286 p_sp_irreg_bonus9 out nocopy number,
287 p_taxable9 out nocopy number,
288 p_itax9 out nocopy number,
289 p_rtax9 out nocopy number,
290 p_stax9 out nocopy number,
291 -- Bug 4322981
292 p_np_dedc9 out nocopy number,
293 p_hi_dedc9 out nocopy number,
294 p_ei_dedc9 out nocopy number,
295 p_donation_dedc9 out nocopy number,
296 -- End of 4322981
297 p_non_taxable_ovs9 out nocopy number,
298 p_non_taxable_ovt9 out nocopy number,
299 p_non_taxable_oth9 out nocopy number,
300 p_effective_date10 out nocopy date,
301 p_taxable_mth10 out nocopy number,
302 p_taxable_bon10 out nocopy number,
303 p_sp_irreg_bonus10 out nocopy number,
304 p_taxable10 out nocopy number,
305 p_itax10 out nocopy number,
306 p_rtax10 out nocopy number,
307 p_stax10 out nocopy number,
308 -- Bug 4322981
309 p_np_dedc10 out nocopy number,
310 p_hi_dedc10 out nocopy number,
311 p_ei_dedc10 out nocopy number,
312 p_donation_dedc10 out nocopy number,
313 -- End of 4322981
314 p_non_taxable_ovs10 out nocopy number,
315 p_non_taxable_ovt10 out nocopy number,
316 p_non_taxable_oth10 out nocopy number,
317 p_effective_date11 out nocopy date,
318 p_taxable_mth11 out nocopy number,
319 p_taxable_bon11 out nocopy number,
320 p_sp_irreg_bonus11 out nocopy number,
321 p_taxable11 out nocopy number,
322 p_itax11 out nocopy number,
323 p_rtax11 out nocopy number,
324 p_stax11 out nocopy number,
325 -- Bug 4322981
326 p_np_dedc11 out nocopy number,
327 p_hi_dedc11 out nocopy number,
328 p_ei_dedc11 out nocopy number,
329 p_donation_dedc11 out nocopy number,
330 -- End of 4322981
331 p_non_taxable_ovs11 out nocopy number,
332 p_non_taxable_ovt11 out nocopy number,
333 p_non_taxable_oth11 out nocopy number,
334 p_effective_date12 out nocopy date,
335 p_taxable_mth12 out nocopy number,
336 p_taxable_bon12 out nocopy number,
337 p_sp_irreg_bonus12 out nocopy number,
338 p_taxable12 out nocopy number,
339 p_itax12 out nocopy number,
340 p_rtax12 out nocopy number,
341 p_stax12 out nocopy number,
342 -- Bug 4322981
343 p_np_dedc12 out nocopy number,
344 p_hi_dedc12 out nocopy number,
345 p_ei_dedc12 out nocopy number,
346 p_donation_dedc12 out nocopy number,
347 -- End of 4322981
348 p_non_taxable_ovs12 out nocopy number,
349 p_non_taxable_ovt12 out nocopy number,
350 p_non_taxable_oth12 out nocopy number,
351 p_taxable_mth13 out nocopy number,
352 p_taxable_bon13 out nocopy number,
353 p_sp_irreg_bonus13 out nocopy number,
354 p_taxable13 out nocopy number,
355 p_itax13 out nocopy number,
356 p_rtax13 out nocopy number,
357 p_stax13 out nocopy number,
358 -- Bug 4322981
359 p_np_dedc13 out nocopy number,
360 p_hi_dedc13 out nocopy number,
361 p_ei_dedc13 out nocopy number,
362 p_donation_dedc13 out nocopy number,
363 -- End of 4322981
364 p_non_taxable_ovs13 out nocopy number,
365 p_non_taxable_ovt13 out nocopy number,
366 p_non_taxable_oth13 out nocopy number,
367 -- 3546994
368 p_dependent_count out nocopy number,
369 p_fw_tax_break1 out nocopy number,
370 p_fw_tax_break2 out nocopy number,
371 p_fw_tax_break3 out nocopy number,
372 p_fw_tax_break4 out nocopy number,
373 p_fw_tax_break5 out nocopy number,
374 p_fw_tax_break6 out nocopy number,
375 p_fw_tax_break7 out nocopy number,
376 p_fw_tax_break8 out nocopy number,
377 p_fw_tax_break9 out nocopy number,
378 p_fw_tax_break10 out nocopy number,
379 p_fw_tax_break11 out nocopy number,
380 p_fw_tax_break12 out nocopy number,
381 -- Bug 6470526: 2007 YEA Ledger Statutory Updates
382 p_stck_pur_opt_exec_earn1 out nocopy number,
383 p_stck_pur_opt_exec_earn2 out nocopy number,
384 p_stck_pur_opt_exec_earn3 out nocopy number,
385 p_stck_pur_opt_exec_earn4 out nocopy number,
386 p_stck_pur_opt_exec_earn5 out nocopy number,
387 p_stck_pur_opt_exec_earn6 out nocopy number,
388 p_stck_pur_opt_exec_earn7 out nocopy number,
389 p_stck_pur_opt_exec_earn8 out nocopy number,
390 p_stck_pur_opt_exec_earn9 out nocopy number,
391 p_stck_pur_opt_exec_earn10 out nocopy number,
392 p_stck_pur_opt_exec_earn11 out nocopy number,
393 p_stck_pur_opt_exec_earn12 out nocopy number,
394 p_stck_pur_opt_exec_earn13 out nocopy number,
395 p_research_payment1 out nocopy number,
396 p_research_payment2 out nocopy number,
397 p_research_payment3 out nocopy number,
398 p_research_payment4 out nocopy number,
399 p_research_payment5 out nocopy number,
400 p_research_payment6 out nocopy number,
401 p_research_payment7 out nocopy number,
402 p_research_payment8 out nocopy number,
403 p_research_payment9 out nocopy number,
404 p_research_payment10 out nocopy number,
405 p_research_payment11 out nocopy number,
406 p_research_payment12 out nocopy number,
407 p_research_payment13 out nocopy number
408 -- End of Bug 6470526
409 )
410 ------------------------------------------------------------------------
411 is
412 l_yea_info pay_kr_yea_pkg.t_yea_info;
413 l_month number;
414 l_dependent_spouse_count Number; -- 3546994
415 l_bal_adj_assact pay_assignment_actions.assignment_action_id%type ; -- Bug 4261844
416 --
417 -- Bug 4261844: Get the balance adjustment action (sequenced) from the archival (unsequenced) assact
418 -- p_assignment_action_id...
419 cursor csr_bal_adj_assact is
420 select
421 pai.locked_action_id bal_adj_assact
422 from
423 pay_action_interlocks pai,
424 pay_assignment_actions paa,
425 pay_payroll_actions ppa
426 where
427 pai.locking_action_id = p_assignment_action_id
428 and paa.assignment_action_id = pai.locked_action_id
429 and paa.action_status = 'C'
430 and paa.payroll_action_id = ppa.payroll_action_id
431 and ppa.action_type = 'B'
432 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null)
433 = 'YEA' ;
434 --
435 -- Bug 4261844: ... and use it to get the monthly runs.
436 cursor csr_assact(p_bal_adj_assact pay_assignment_actions.assignment_action_id%type)
437 is
438 select
439 paa2.assignment_action_id,
440 ppa2.effective_date
441 from pay_payroll_actions ppa2,
442 pay_assignment_actions paa2,
443 pay_payroll_actions ppa,
444 pay_assignment_actions paa
445 where paa.assignment_action_id = p_bal_adj_assact
446 and ppa.payroll_action_id = paa.payroll_action_id
447 and paa2.assignment_id = paa.assignment_id
448 and paa2.action_sequence < paa.action_sequence
449 and paa2.source_action_id is null
450 and ppa2.payroll_action_id = paa2.payroll_action_id
451 and ( ppa2.action_type in ('I', 'R', 'Q', 'V') or ( ppa2.action_type ='B' and nvl(pay_core_utils.get_parameter('REPORT_TYPE',ppa2.legislative_parameters),'XYZ') <> 'YEA'))
452 and trunc(ppa2.effective_date, 'YYYY') = trunc(ppa.effective_date, 'YYYY')
453 order by paa2.action_sequence desc;
454 -- End of 4261844
455 --
456 procedure payment_details(
457 p_assact in number,
458 p_date_paid in date,
459 p_effective_date in out nocopy date,
460 p_taxable_mth in out nocopy number,
461 p_taxable_bon in out nocopy number,
462 p_sp_irreg_bonus in out nocopy number,
463 p_stck_pur_opt_exec_earn in out nocopy number, -- Bug 6470526
464 p_taxable in out nocopy number,
465 p_itax in out nocopy number,
466 p_rtax in out nocopy number,
467 p_stax in out nocopy number,
468 -- Bug 4322981
469 p_np_dedc in out nocopy number,
470 p_hi_dedc in out nocopy number,
471 p_ei_dedc in out nocopy number,
472 p_donation_dedc in out nocopy number,
473 -- End of 4322981
474 p_non_taxable_ovs in out nocopy number,
475 p_non_taxable_ovt in out nocopy number,
476 p_research_payment in out nocopy number, -- Bug 6470526
477 p_non_taxable_oth in out nocopy number,
478 p_fw_tax_break in out nocopy number) --3546994
479 is
480 -----------------------------------
481 -- Bug 3223825
482 -- Pl/sql table declaration
483 -----------------------------------
484 t_balance_batch pay_balance_pkg.t_balance_value_tab;
485
486 l_taxable_mth number;
487 l_taxable_bon number;
488 l_sp_irreg_bonus_mth number;
489 l_sp_irreg_bonus_bon number;
490 begin
491 if p_effective_date is null then
492 p_effective_date := p_date_paid;
493
494
495
496 ------------------------------------------
497 -- Bug 3223825
498 --populate values into plsql table
499 ------------------------------------------
500 t_balance_batch(1).defined_balance_id := g_defined_balance_id.taxable_mth;
501 t_balance_batch(2).defined_balance_id := g_defined_balance_id.taxable_bon;
502 t_balance_batch(3).defined_balance_id := g_defined_balance_id.sp_irreg_bonus_mth;
503 t_balance_batch(4).defined_balance_id := g_defined_balance_id.sp_irreg_bonus_bon;
504 t_balance_batch(5).defined_balance_id := g_defined_balance_id.itax_mth;
505 t_balance_batch(6).defined_balance_id := g_defined_balance_id.itax_bon;
506 t_balance_batch(7).defined_balance_id := g_defined_balance_id.rtax_mth;
507 t_balance_batch(8).defined_balance_id := g_defined_balance_id.rtax_bon;
508 t_balance_batch(9).defined_balance_id := g_defined_balance_id.stax_mth;
509 t_balance_batch(10).defined_balance_id := g_defined_balance_id.stax_bon;
510 t_balance_batch(11).defined_balance_id := g_defined_balance_id.non_taxable_ovs_mth;
511 t_balance_batch(12).defined_balance_id := g_defined_balance_id.non_taxable_ovs_bon;
512 t_balance_batch(13).defined_balance_id := g_defined_balance_id.non_taxable_ovt_mth;
513 t_balance_batch(14).defined_balance_id := g_defined_balance_id.non_taxable_ovt_bon;
514 t_balance_batch(15).defined_balance_id := g_defined_balance_id.non_taxable_mth;
515 t_balance_batch(16).defined_balance_id := g_defined_balance_id.non_taxable_bon;
516 t_balance_batch(17).defined_balance_id := g_defined_balance_id.fw_tax_break_mth; --3546994
517 t_balance_batch(18).defined_balance_id := g_defined_balance_id.fw_tax_break_bon; --3546994
518 -- Bug 4322981
519 t_balance_batch(19).defined_balance_id := g_defined_balance_id.np_prem_mth ;
520 t_balance_batch(20).defined_balance_id := g_defined_balance_id.np_prem_bon ;
521 t_balance_batch(21).defined_balance_id := g_defined_balance_id.hi_prem_mth ;
522 t_balance_batch(22).defined_balance_id := g_defined_balance_id.hi_prem_bon ;
523 t_balance_batch(23).defined_balance_id := g_defined_balance_id.ei_prem_mth ;
524 t_balance_batch(24).defined_balance_id := g_defined_balance_id.ei_prem_bon ;
525 t_balance_batch(25).defined_balance_id := g_defined_balance_id.donation_mth ;
526 t_balance_batch(26).defined_balance_id := g_defined_balance_id.donation_bon ;
527 -- End of 4322981
528 -- Bug 5684037
529 t_balance_batch(27).defined_balance_id := g_defined_balance_id.addl_non_tax_mth ;
530 t_balance_batch(28).defined_balance_id := g_defined_balance_id.addl_non_tax_bon ;
531 -- Bug 5999074 Rolled back changes for 5684037. Replaced with a single balance
532 -- Additional Other Non-Taxable Earnings
533 -- End of Bug 5684037
534 -- Bug 6470526: 2007 YEA Ledger Statutory Updates
535 t_balance_batch(29).defined_balance_id := g_defined_balance_id.stck_pur_opt_exec_earn_mth;
536 t_balance_batch(30).defined_balance_id := g_defined_balance_id.stck_pur_opt_exec_earn_bon;
537 t_balance_batch(31).defined_balance_id := g_defined_balance_id.research_payment_mth;
538 t_balance_batch(32).defined_balance_id := g_defined_balance_id.research_payment_bon;
539 -- End of Bug 6470526
540 ----------------------------------------------
541 --Bug 3223825
542 --batch get_value call
543 ----------------------------------------------
544 pay_balance_pkg.get_value(p_assact,t_balance_batch,FALSE,FALSE);
545
546
547 l_taxable_mth := t_balance_batch(1).balance_value;
548 l_taxable_bon := t_balance_batch(2).balance_value;
549 l_sp_irreg_bonus_mth := t_balance_batch(3).balance_value;
550 l_sp_irreg_bonus_bon := t_balance_batch(4).balance_value;
551 p_taxable_mth := l_taxable_mth - l_sp_irreg_bonus_mth;
552 p_taxable_bon := l_taxable_bon - l_sp_irreg_bonus_bon;
553 p_sp_irreg_bonus := l_sp_irreg_bonus_mth + l_sp_irreg_bonus_bon;
554 --
555 -- Bug 6470526: 2007 YEA Ledger Statutory Updates
556 p_stck_pur_opt_exec_earn := t_balance_batch(29).balance_value
557 + t_balance_batch(30).balance_value;
558 p_research_payment := t_balance_batch(31).balance_value
559 + t_balance_batch(32).balance_value;
560 p_taxable := p_taxable_mth
561 + p_taxable_bon
562 + p_sp_irreg_bonus
563 + p_stck_pur_opt_exec_earn;
564 -- End of Bug 6470526
565 --
566 p_itax := t_balance_batch(5).balance_value
567 + t_balance_batch(6).balance_value;
568 p_rtax := t_balance_batch(7).balance_value
569 + t_balance_batch(8).balance_value;
570 p_stax := t_balance_batch(9).balance_value
571 + t_balance_batch(10).balance_value;
572 p_non_taxable_ovs := t_balance_batch(11).balance_value
573 + t_balance_batch(12).balance_value;
574 p_non_taxable_ovt := t_balance_batch(13).balance_value
575 + t_balance_batch(14).balance_value;
576 p_non_taxable_oth := t_balance_batch(15).balance_value
577 + t_balance_batch(16).balance_value
578 + t_balance_batch(27).balance_value -- Bug 5684037
579 + t_balance_batch(28).balance_value
580 - p_non_taxable_ovs
581 - p_non_taxable_ovt;
582 p_fw_tax_break := t_balance_batch(17).balance_value --3546994
583 + t_balance_batch(18).balance_value; --3546994
584 -- Bug 4322981
585 p_np_dedc := t_balance_batch(19).balance_value
586 + t_balance_batch(20).balance_value ;
587 p_hi_dedc := t_balance_batch(21).balance_value
588 + t_balance_batch(22).balance_value ;
589 p_ei_dedc := t_balance_batch(23).balance_value
590 + t_balance_batch(24).balance_value ;
591 p_donation_dedc := t_balance_batch(25).balance_value
592 + t_balance_batch(26).balance_value ;
593 -- End of 4322981
594 --
595 -- Index 13th indicates total amount
596 -- If no assacts are detected, total is also "NULL" value.
597 --
598 p_taxable_mth13 := nvl(p_taxable_mth13, 0) + p_taxable_mth;
599 p_taxable_bon13 := nvl(p_taxable_bon13, 0) + p_taxable_bon;
600 p_sp_irreg_bonus13 := nvl(p_sp_irreg_bonus13, 0) + p_sp_irreg_bonus;
601 p_taxable13 := nvl(p_taxable13, 0) + p_taxable;
602 p_itax13 := nvl(p_itax13, 0) + p_itax;
603 p_rtax13 := nvl(p_rtax13, 0) + p_rtax;
604 p_stax13 := nvl(p_stax13, 0) + p_stax;
605 p_non_taxable_ovs13 := nvl(p_non_taxable_ovs13, 0) + p_non_taxable_ovs;
606 p_non_taxable_ovt13 := nvl(p_non_taxable_ovt13, 0) + p_non_taxable_ovt;
607 p_non_taxable_oth13 := nvl(p_non_taxable_oth13, 0) + p_non_taxable_oth;
608 -- Bug 4322981
609 p_np_dedc13 := nvl(p_np_dedc13, 0) + p_np_dedc ;
610 p_hi_dedc13 := nvl(p_hi_dedc13, 0) + p_hi_dedc ;
611 p_ei_dedc13 := nvl(p_ei_dedc13, 0) + p_ei_dedc ;
612 p_donation_dedc13 := nvl(p_donation_dedc13, 0) + p_donation_dedc ;
613 -- End of 4322981
614 --
615 -- Bug 6470526: 2007 YEA Ledger Statutory Updates
616 p_stck_pur_opt_exec_earn13 := nvl(p_stck_pur_opt_exec_earn13, 0) + p_stck_pur_opt_exec_earn ;
617 p_research_payment13 := nvl(p_research_payment13, 0) + p_research_payment ;
618 -- End of Bug 6470526
619 end if;
620 end payment_details;
621 begin
622 --
623 -- Derive YEA result
624 --
625 l_yea_info := pay_kr_yea_pkg.yea_info(p_assignment_action_id);
626 --
627 -- Taxable Earnings
628 --
629 p_taxable := l_yea_info.taxable;
630 --
631 -- Basic Income Exemption
632 --
633 p_basic_income_exem := l_yea_info.basic_income_exem;
634 p_taxable_income := l_yea_info.taxable_income;
635 --
636 -- Basic Tax Exemption
637 --
638 p_ee_tax_exem := l_yea_info.ee_tax_exem;
639 p_dpnt_spouse_flag := l_yea_info.dpnt_spouse_flag;
640 p_dpnt_spouse_tax_exem := l_yea_info.dpnt_spouse_tax_exem;
641 p_num_of_underaged_dpnts := l_yea_info.num_of_underaged_dpnts;
642 p_num_of_aged_dpnts := l_yea_info.num_of_adult_dpnts + l_yea_info.num_of_aged_dpnts;
643 p_dpnt_tax_exem := l_yea_info.dpnt_tax_exem;
644 --
645 -- Additional Tax Exemption
646 --
647 p_num_of_ageds := l_yea_info.num_of_ageds;
648 p_aged_tax_exem := l_yea_info.aged_tax_exem;
649 p_num_of_disableds := l_yea_info.num_of_disableds;
650 p_disabled_tax_exem := l_yea_info.disabled_tax_exem;
651 p_female_ee_flag := l_yea_info.female_ee_flag;
652 p_female_ee_tax_exem := l_yea_info.female_ee_tax_exem;
653 p_num_of_children := l_yea_info.num_of_children;
654 p_child_tax_exem := l_yea_info.child_tax_exem;
655 p_supp_tax_exem := l_yea_info.supp_tax_exem;
656 --
657 -- Special Tax Exemption
658 --
659 p_hi_prem := l_yea_info.hi_prem;
660 p_ei_prem := l_yea_info.ei_prem;
661 p_pers_ins_name := l_yea_info.pers_ins_name;
662 p_pers_ins_prem := l_yea_info.pers_ins_prem;
663 p_disabled_ins_prem := l_yea_info.disabled_ins_prem;
664 p_ins_prem_tax_exem := l_yea_info.ins_prem_tax_exem;
665 --
666 p_med_exp := l_yea_info.med_exp;
667 p_med_exp_aged := l_yea_info.med_exp_aged;
668 p_med_exp_disabled := l_yea_info.med_exp_disabled;
669 p_max_med_exp_tax_exem := l_yea_info.max_med_exp_tax_exem;
670 p_med_exp_tax_exem := l_yea_info.med_exp_tax_exem;
671 --
672 p_ee_educ_exp := l_yea_info.ee_educ_exp;
673 p_spouse_educ_exp := l_yea_info.spouse_educ_exp;
674 p_disabled_educ_exp := l_yea_info.disabled_educ_exp;
675 p_dpnt_educ_exp := l_yea_info.dpnt_educ_exp;
676 p_educ_exp_tax_exem := l_yea_info.educ_exp_tax_exem;
677 --
678 p_housing_saving_type_meaning := hr_general.decode_lookup('KR_HOUSING_SAVING_TYPE', l_yea_info.housing_saving_type);
679 p_housing_saving := l_yea_info.housing_saving;
680 p_housing_purchase_date := l_yea_info.housing_purchase_date;
681 p_housing_loan_date := l_yea_info.housing_loan_date;
682 p_housing_loan_repay := l_yea_info.housing_loan_repay;
683 p_lt_housing_loan_date := l_yea_info.lt_housing_loan_date;
684 p_lt_housing_loan_int_repay := l_yea_info.lt_housing_loan_interest_repay;
685 p_max_housing_exp_tax_exem := l_yea_info.max_housing_exp_tax_exem;
686 p_housing_exp_tax_exem := l_yea_info.housing_exp_tax_exem;
687 --
688 -- source modified for Bug2821759
689 p_donation1_tax_exem := l_yea_info.donation1 + l_yea_info.political_donation1 + l_yea_info.political_donation2 + l_yea_info.political_donation3;
690 p_donation2 := l_yea_info.donation2 + l_yea_info.donation3;
691 p_max_donation2_tax_exem := l_yea_info.max_donation2_tax_exem + l_yea_info.max_donation3_tax_exem;
692 p_donation2_tax_exem := l_yea_info.donation2_tax_exem + l_yea_info.donation3_tax_exem;
693 p_donation_tax_exem := l_yea_info.donation_tax_exem;
694 --
695 p_sp_tax_exem := l_yea_info.sp_tax_exem;
696 p_std_sp_tax_exem := l_yea_info.std_sp_tax_exem;
697 --
698 -- National Pension Premium Tax Exemption
699 --
700 p_np_prem := l_yea_info.np_prem;
701 p_np_prem_tax_exem := l_yea_info.np_prem_tax_exem;
702 --
703 -- Other Tax Exemptions
704 -- source modified for Bug2821759
705 p_pers_pension_prem_tax := l_yea_info.pers_pension_prem;
706 p_pers_pension_saving_tax := l_yea_info.pers_pension_saving;
707 p_invest_partner_fin_tax := l_yea_info.invest_partner_fin1 + l_yea_info.invest_partner_fin2;
708 p_credit_card_exp_tax := l_yea_info.credit_card_exp;
709 p_emp_st_own_plan_cont := l_yea_info.emp_stk_own_contri;
710
711 p_pers_pension_prem_tax_exem := l_yea_info.pers_pension_prem_tax_exem;
712 p_pers_pension_saving_tax_exem := l_yea_info.pers_pension_saving_tax_exem;
713 p_invest_partner_fin_tax_exem := l_yea_info.invest_partner_fin_tax_exem;
714 p_credit_card_exp_tax_exem := l_yea_info.credit_card_exp_tax_exem;
715 p_emp_st_own_plan_cont_exem := l_yea_info.emp_stk_own_contri_tax_exem;
716 --
717 -- Calculated Tax
718 --
719 p_taxation_base := l_yea_info.taxation_base;
720 p_calc_tax := l_yea_info.calc_tax;
721 --
722 -- Tax Breaks
723 --
724 p_basic_tax_break := l_yea_info.basic_tax_break;
725 p_housing_exp_tax_break := l_yea_info.housing_exp_tax_break;
726 p_stock_saving_tax_break := l_yea_info.stock_saving_tax_break;
727 --
728 if l_yea_info.ovstb_tax_paid_date is not null then
729 p_ovstb_territory_short_name := hr_general.decode_territory(l_yea_info.ovstb_territory_code);
730 p_ovstb_tax_paid_date := l_yea_info.ovstb_tax_paid_date;
731 p_ovstb_tax_foreign_currency := l_yea_info.ovstb_tax_foreign_currency;
732 p_ovstb_tax := l_yea_info.ovstb_tax;
733 p_ovstb_application_date := l_yea_info.ovstb_application_date;
734 p_ovstb_submission_date := l_yea_info.ovstb_submission_date;
735 end if;
736 p_ovs_tax_break := l_yea_info.ovs_tax_break;
737 --
738 p_lt_stock_saving_tax_break := l_yea_info.lt_stock_saving_tax_break;
739 p_total_tax_break := l_yea_info.total_tax_break;
740 --
741 -- Foreign Worker Tax Break
742 --
743 if l_yea_info.fwtb_immigration_purpose is not null then
744 p_fwtb_immigration_purpose := l_yea_info.fwtb_immigration_purpose;
745 p_fwtb_contract_date := l_yea_info.fwtb_contract_date;
746 p_fwtb_expiry_date := l_yea_info.fwtb_expiry_date;
747 p_fwtb_application_date := l_yea_info.fwtb_application_date;
748 p_fwtb_submission_date := l_yea_info.fwtb_submission_date;
749 end if;
750 p_foreign_worker_tax_break1 := l_yea_info.foreign_worker_tax_break1;
751 p_foreign_worker_tax_break2 := l_yea_info.foreign_worker_tax_break2;
752 --
753 -- Tax
754 -- Rtax calculation included for bug 2577751
755 --
756 p_annual_itax := l_yea_info.annual_itax;
757 p_annual_rtax := l_yea_info.annual_rtax;
758 p_annual_stax := l_yea_info.annual_stax;
759 p_prev_itax := l_yea_info.prev_itax;
760 p_prev_rtax := l_yea_info.prev_rtax;
761 p_prev_stax := l_yea_info.prev_stax;
762 p_cur_annual_itax := p_annual_itax - p_prev_itax;
763 p_cur_annual_rtax := p_annual_rtax - p_prev_rtax;
764 p_cur_annual_stax := p_annual_stax - p_prev_stax;
765 p_cur_itax := l_yea_info.cur_itax;
766 p_cur_rtax := l_yea_info.cur_rtax;
767 p_cur_stax := l_yea_info.cur_stax;
768 if l_yea_info.itax_adj >= 0 then
769 p_itax_adj := l_yea_info.itax_adj;
770 p_itax_refund := 0;
771 else
772 p_itax_adj := 0;
773 p_itax_refund := abs(l_yea_info.itax_adj);
774 end if;
775 --
776 -- rtax calculation included for bug 2577751
777 --
778 if l_yea_info.rtax_adj >= 0 then
779 p_rtax_adj := l_yea_info.rtax_adj;
780 p_rtax_refund := 0;
781 else
782 p_rtax_adj := 0;
783 p_rtax_refund := abs(l_yea_info.rtax_adj);
784 end if;
785 if l_yea_info.stax_adj >= 0 then
786 p_stax_adj := l_yea_info.stax_adj;
787 p_stax_refund := 0;
788 else
789 p_stax_adj := 0;
790 p_stax_refund := abs(l_yea_info.stax_adj);
791 end if;
792 --
793 -- Derive annual payment details
794 --
795 /* Calculate total no of dependents including spouse
796 and the employee himself - Bug No: 3546994 */
797 if nvl(l_yea_info.dpnt_spouse_flag,'N') = 'Y' then
798 l_dependent_spouse_count := 1;
799 else
800 l_dependent_spouse_count := 0;
801 end if;
802 p_dependent_count := l_yea_info.num_of_underaged_dpnts
803 + l_yea_info.num_of_adult_dpnts
804 + l_yea_info.num_of_aged_dpnts
805 + l_dependent_spouse_count -- include spouse
806 + 1; -- include employee
807 -- 3546994
808 -- Bug 4261844: Get the balance adjustment action
809 open csr_bal_adj_assact ;
810 fetch csr_bal_adj_assact into l_bal_adj_assact ;
811 close csr_bal_adj_assact ;
812
813 if l_bal_adj_assact is null then
814 l_bal_adj_assact := p_assignment_action_id ;
815 end if ;
816
817 for l_rec in csr_assact(l_bal_adj_assact) loop
818 l_month := to_number(to_char(l_rec.effective_date, 'MM'));
819 if l_month = 1 then
820 payment_details(
821 p_assact => l_rec.assignment_action_id,
822 p_date_paid => l_rec.effective_date,
823 p_effective_date => p_effective_date1,
824 p_taxable_mth => p_taxable_mth1,
825 p_taxable_bon => p_taxable_bon1,
826 p_sp_irreg_bonus => p_sp_irreg_bonus1,
827 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn1, -- Bug 6470526
828 p_taxable => p_taxable1,
829 p_itax => p_itax1,
830 p_rtax => p_rtax1,
831 p_stax => p_stax1,
832 -- Bug 4322981
833 p_np_dedc => p_np_dedc1,
834 p_hi_dedc => p_hi_dedc1,
835 p_ei_dedc => p_ei_dedc1,
836 p_donation_dedc => p_donation_dedc1,
837 -- End of 4322981
838 p_non_taxable_ovs => p_non_taxable_ovs1,
839 p_non_taxable_ovt => p_non_taxable_ovt1,
840 p_research_payment => p_research_payment1, -- Bug 6470526
841 p_non_taxable_oth => p_non_taxable_oth1,
842 p_fw_tax_break => p_fw_tax_break1 ); -- 3546994
843 elsif l_month = 2 then
844 payment_details(
845 p_assact => l_rec.assignment_action_id,
846 p_date_paid => l_rec.effective_date,
847 p_effective_date => p_effective_date2,
848 p_taxable_mth => p_taxable_mth2,
849 p_taxable_bon => p_taxable_bon2,
850 p_sp_irreg_bonus => p_sp_irreg_bonus2,
851 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn2, -- Bug 6470526
852 p_taxable => p_taxable2,
853 p_itax => p_itax2,
854 p_rtax => p_rtax2,
855 p_stax => p_stax2,
856 -- Bug 4322981
857 p_np_dedc => p_np_dedc2,
858 p_hi_dedc => p_hi_dedc2,
859 p_ei_dedc => p_ei_dedc2,
860 p_donation_dedc => p_donation_dedc2,
861 -- End of 4322981
862 p_non_taxable_ovs => p_non_taxable_ovs2,
863 p_non_taxable_ovt => p_non_taxable_ovt2,
864 p_research_payment => p_research_payment2, -- Bug 6470526
865 p_non_taxable_oth => p_non_taxable_oth2,
866 p_fw_tax_break => p_fw_tax_break2 ); -- 3546994
867 elsif l_month = 3 then
868 payment_details(
869 p_assact => l_rec.assignment_action_id,
870 p_date_paid => l_rec.effective_date,
871 p_effective_date => p_effective_date3,
872 p_taxable_mth => p_taxable_mth3,
873 p_taxable_bon => p_taxable_bon3,
874 p_sp_irreg_bonus => p_sp_irreg_bonus3,
875 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn3, -- Bug 6470526
876 p_taxable => p_taxable3,
877 p_itax => p_itax3,
878 p_rtax => p_rtax3,
879 p_stax => p_stax3,
880 -- Bug 4322981
881 p_np_dedc => p_np_dedc3,
882 p_hi_dedc => p_hi_dedc3,
883 p_ei_dedc => p_ei_dedc3,
884 p_donation_dedc => p_donation_dedc3,
885 -- End of 4322981
886 p_non_taxable_ovs => p_non_taxable_ovs3,
887 p_non_taxable_ovt => p_non_taxable_ovt3,
888 p_research_payment => p_research_payment3, -- Bug 6470526
889 p_non_taxable_oth => p_non_taxable_oth3,
890 p_fw_tax_break => p_fw_tax_break3 ); -- 3546994
891 elsif l_month = 4 then
892 payment_details(
893 p_assact => l_rec.assignment_action_id,
894 p_date_paid => l_rec.effective_date,
895 p_effective_date => p_effective_date4,
896 p_taxable_mth => p_taxable_mth4,
897 p_taxable_bon => p_taxable_bon4,
898 p_sp_irreg_bonus => p_sp_irreg_bonus4,
899 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn4, -- Bug 6470526
900 p_taxable => p_taxable4,
901 p_itax => p_itax4,
902 p_rtax => p_rtax4,
903 p_stax => p_stax4,
904 -- Bug 4322981
905 p_np_dedc => p_np_dedc4,
906 p_hi_dedc => p_hi_dedc4,
907 p_ei_dedc => p_ei_dedc4,
908 p_donation_dedc => p_donation_dedc4,
909 -- End of 4322981
910 p_non_taxable_ovs => p_non_taxable_ovs4,
911 p_non_taxable_ovt => p_non_taxable_ovt4,
912 p_research_payment => p_research_payment4, -- Bug 6470526
913 p_non_taxable_oth => p_non_taxable_oth4,
914 p_fw_tax_break => p_fw_tax_break4 ); -- 3546994
915 elsif l_month = 5 then
916 payment_details(
917 p_assact => l_rec.assignment_action_id,
918 p_date_paid => l_rec.effective_date,
919 p_effective_date => p_effective_date5,
920 p_taxable_mth => p_taxable_mth5,
921 p_taxable_bon => p_taxable_bon5,
922 p_sp_irreg_bonus => p_sp_irreg_bonus5,
923 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn5, -- Bug 6470526
924 p_taxable => p_taxable5,
925 p_itax => p_itax5,
926 p_rtax => p_rtax5,
927 p_stax => p_stax5,
928 -- Bug 4322981
929 p_np_dedc => p_np_dedc5,
930 p_hi_dedc => p_hi_dedc5,
931 p_ei_dedc => p_ei_dedc5,
932 p_donation_dedc => p_donation_dedc5,
933 -- End of 4322981
934 p_non_taxable_ovs => p_non_taxable_ovs5,
935 p_non_taxable_ovt => p_non_taxable_ovt5,
936 p_research_payment => p_research_payment5, -- Bug 6470526
937 p_non_taxable_oth => p_non_taxable_oth5,
938 p_fw_tax_break => p_fw_tax_break5 ); -- 3546994
939 elsif l_month = 6 then
940 payment_details(
941 p_assact => l_rec.assignment_action_id,
942 p_date_paid => l_rec.effective_date,
943 p_effective_date => p_effective_date6,
944 p_taxable_mth => p_taxable_mth6,
945 p_taxable_bon => p_taxable_bon6,
946 p_sp_irreg_bonus => p_sp_irreg_bonus6,
947 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn6, -- Bug 6470526
948 p_taxable => p_taxable6,
949 p_itax => p_itax6,
950 p_rtax => p_rtax6,
951 p_stax => p_stax6,
952 -- Bug 4322981
953 p_np_dedc => p_np_dedc6,
954 p_hi_dedc => p_hi_dedc6,
955 p_ei_dedc => p_ei_dedc6,
956 p_donation_dedc => p_donation_dedc6,
957 -- End of 4322981
958 p_non_taxable_ovs => p_non_taxable_ovs6,
959 p_non_taxable_ovt => p_non_taxable_ovt6,
960 p_research_payment => p_research_payment6, -- Bug 6470526
961 p_non_taxable_oth => p_non_taxable_oth6,
962 p_fw_tax_break => p_fw_tax_break6 ); -- 3546994
963 elsif l_month = 7 then
964 payment_details(
965 p_assact => l_rec.assignment_action_id,
966 p_date_paid => l_rec.effective_date,
967 p_effective_date => p_effective_date7,
968 p_taxable_mth => p_taxable_mth7,
969 p_taxable_bon => p_taxable_bon7,
970 p_sp_irreg_bonus => p_sp_irreg_bonus7,
971 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn7, -- Bug 6470526
972 p_taxable => p_taxable7,
973 p_itax => p_itax7,
974 p_rtax => p_rtax7,
975 p_stax => p_stax7,
976 -- Bug 4322981
977 p_np_dedc => p_np_dedc7,
978 p_hi_dedc => p_hi_dedc7,
979 p_ei_dedc => p_ei_dedc7,
980 p_donation_dedc => p_donation_dedc7,
981 -- End of 4322981
982 p_non_taxable_ovs => p_non_taxable_ovs7,
983 p_non_taxable_ovt => p_non_taxable_ovt7,
984 p_research_payment => p_research_payment7, -- Bug 6470526
985 p_non_taxable_oth => p_non_taxable_oth7,
986 p_fw_tax_break => p_fw_tax_break7 ); -- 3546994
987 elsif l_month = 8 then
988 payment_details(
989 p_assact => l_rec.assignment_action_id,
990 p_date_paid => l_rec.effective_date,
991 p_effective_date => p_effective_date8,
992 p_taxable_mth => p_taxable_mth8,
993 p_taxable_bon => p_taxable_bon8,
994 p_sp_irreg_bonus => p_sp_irreg_bonus8,
995 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn8, -- Bug 6470526
996 p_taxable => p_taxable8,
997 p_itax => p_itax8,
998 p_rtax => p_rtax8,
999 p_stax => p_stax8,
1000 -- Bug 4322981
1001 p_np_dedc => p_np_dedc8,
1002 p_hi_dedc => p_hi_dedc8,
1003 p_ei_dedc => p_ei_dedc8,
1004 p_donation_dedc => p_donation_dedc8,
1005 -- End of 4322981
1006 p_non_taxable_ovs => p_non_taxable_ovs8,
1007 p_non_taxable_ovt => p_non_taxable_ovt8,
1008 p_research_payment => p_research_payment8, -- Bug 6470526
1009 p_non_taxable_oth => p_non_taxable_oth8,
1010 p_fw_tax_break => p_fw_tax_break8 ); -- 3546994
1011 elsif l_month = 9 then
1012 payment_details(
1013 p_assact => l_rec.assignment_action_id,
1014 p_date_paid => l_rec.effective_date,
1015 p_effective_date => p_effective_date9,
1016 p_taxable_mth => p_taxable_mth9,
1017 p_taxable_bon => p_taxable_bon9,
1018 p_sp_irreg_bonus => p_sp_irreg_bonus9,
1019 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn9, -- Bug 6470526
1020 p_taxable => p_taxable9,
1021 p_itax => p_itax9,
1022 p_rtax => p_rtax9,
1023 p_stax => p_stax9,
1024 -- Bug 4322981
1025 p_np_dedc => p_np_dedc9,
1026 p_hi_dedc => p_hi_dedc9,
1027 p_ei_dedc => p_ei_dedc9,
1028 p_donation_dedc => p_donation_dedc9,
1029 -- End of 4322981
1030 p_non_taxable_ovs => p_non_taxable_ovs9,
1031 p_non_taxable_ovt => p_non_taxable_ovt9,
1032 p_research_payment => p_research_payment9, -- Bug 6470526
1033 p_non_taxable_oth => p_non_taxable_oth9,
1034 p_fw_tax_break => p_fw_tax_break9 ); -- 3546994
1035 elsif l_month = 10 then
1036 payment_details(
1037 p_assact => l_rec.assignment_action_id,
1038 p_date_paid => l_rec.effective_date,
1039 p_effective_date => p_effective_date10,
1040 p_taxable_mth => p_taxable_mth10,
1041 p_taxable_bon => p_taxable_bon10,
1042 p_sp_irreg_bonus => p_sp_irreg_bonus10,
1043 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn10, -- Bug 6470526
1044 p_taxable => p_taxable10,
1045 p_itax => p_itax10,
1046 p_rtax => p_rtax10,
1047 p_stax => p_stax10,
1048 -- Bug 4322981
1049 p_np_dedc => p_np_dedc10,
1050 p_hi_dedc => p_hi_dedc10,
1051 p_ei_dedc => p_ei_dedc10,
1052 p_donation_dedc => p_donation_dedc10,
1053 -- End of 4322981
1054 p_non_taxable_ovs => p_non_taxable_ovs10,
1055 p_non_taxable_ovt => p_non_taxable_ovt10,
1056 p_research_payment => p_research_payment10, -- Bug 6470526
1057 p_non_taxable_oth => p_non_taxable_oth10,
1058 p_fw_tax_break => p_fw_tax_break10 ); -- 3546994
1059 elsif l_month = 11 then
1060 payment_details(
1061 p_assact => l_rec.assignment_action_id,
1062 p_date_paid => l_rec.effective_date,
1063 p_effective_date => p_effective_date11,
1064 p_taxable_mth => p_taxable_mth11,
1065 p_taxable_bon => p_taxable_bon11,
1066 p_sp_irreg_bonus => p_sp_irreg_bonus11,
1067 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn11, -- Bug 6470526
1068 p_taxable => p_taxable11,
1069 p_itax => p_itax11,
1070 p_rtax => p_rtax11,
1071 p_stax => p_stax11,
1072 -- Bug 4322981
1073 p_np_dedc => p_np_dedc11,
1074 p_hi_dedc => p_hi_dedc11,
1075 p_ei_dedc => p_ei_dedc11,
1076 p_donation_dedc => p_donation_dedc11,
1077 -- End of 4322981
1078 p_non_taxable_ovs => p_non_taxable_ovs11,
1079 p_non_taxable_ovt => p_non_taxable_ovt11,
1080 p_research_payment => p_research_payment11, -- Bug 6470526
1081 p_non_taxable_oth => p_non_taxable_oth11,
1082 p_fw_tax_break => p_fw_tax_break11 ); -- 3546994
1083 elsif l_month = 12 then
1084 payment_details(
1085 p_assact => l_rec.assignment_action_id,
1086 p_date_paid => l_rec.effective_date,
1087 p_effective_date => p_effective_date12,
1088 p_taxable_mth => p_taxable_mth12,
1089 p_taxable_bon => p_taxable_bon12,
1090 p_sp_irreg_bonus => p_sp_irreg_bonus12,
1091 p_stck_pur_opt_exec_earn => p_stck_pur_opt_exec_earn12, -- Bug 6470526
1092 p_taxable => p_taxable12,
1093 p_itax => p_itax12,
1094 p_rtax => p_rtax12,
1095 p_stax => p_stax12,
1096 -- Bug 4322981
1097 p_np_dedc => p_np_dedc12,
1098 p_hi_dedc => p_hi_dedc12,
1099 p_ei_dedc => p_ei_dedc12,
1100 p_donation_dedc => p_donation_dedc12,
1101 -- End of 4322981
1102 p_non_taxable_ovs => p_non_taxable_ovs12,
1103 p_non_taxable_ovt => p_non_taxable_ovt12,
1104 p_research_payment => p_research_payment12, -- Bug 6470526
1105 p_non_taxable_oth => p_non_taxable_oth12,
1106 p_fw_tax_break => p_fw_tax_break12 ); -- 3546994
1107 end if;
1108 end loop;
1109 end data;
1110 --------------------------------------------------------
1111 function defined_balance_id(p_user_name in varchar2) return number
1112 --------------------------------------------------------
1113 is
1114 l_defined_balance_id number;
1115 begin
1116 select
1117 u.creator_id
1118 into l_defined_balance_id
1119 from ff_user_entities u,
1120 ff_database_items d
1121 where d.user_name = p_user_name
1122 and u.user_entity_id = d.user_entity_id
1123 and u.legislation_code = 'KR'
1124 and u.business_group_id is null
1125 and u.creator_type = 'B';
1126 --
1127 return l_defined_balance_id;
1128 end defined_balance_id;
1129 ------------------------------------------------------------------------
1130 ------------------------------------------------------------------------
1131 begin
1132 g_defined_balance_id.taxable_mth := defined_balance_id('TOTAL_TAXABLE_EARNINGS_ASG_MTD_MTH');
1133 g_defined_balance_id.taxable_bon := defined_balance_id('TOTAL_TAXABLE_EARNINGS_ASG_MTD_BON');
1134 g_defined_balance_id.sp_irreg_bonus_mth := defined_balance_id('SP_IRREG_BONUS_ASG_MTD_MTH');
1135 g_defined_balance_id.sp_irreg_bonus_bon := defined_balance_id('SP_IRREG_BONUS_ASG_MTD_BON');
1136 g_defined_balance_id.itax_mth := defined_balance_id('ITAX_ASG_MTD_MTH');
1137 g_defined_balance_id.itax_bon := defined_balance_id('ITAX_ASG_MTD_BON');
1138 g_defined_balance_id.rtax_mth := defined_balance_id('RTAX_ASG_MTD_MTH');
1139 g_defined_balance_id.rtax_bon := defined_balance_id('RTAX_ASG_MTD_BON');
1140 g_defined_balance_id.stax_mth := defined_balance_id('STAX_ASG_MTD_MTH');
1141 g_defined_balance_id.stax_bon := defined_balance_id('STAX_ASG_MTD_BON');
1142 g_defined_balance_id.non_taxable_ovs_mth:= defined_balance_id('NON_TAXABLE_OVS_EARNINGS_ASG_MTD_MTH');
1143 g_defined_balance_id.non_taxable_ovs_bon:= defined_balance_id('NON_TAXABLE_OVS_EARNINGS_ASG_MTD_BON');
1144 g_defined_balance_id.non_taxable_ovt_mth:= defined_balance_id('NON_TAXABLE_OVT_EARNINGS_ASG_MTD_MTH');
1145 g_defined_balance_id.non_taxable_ovt_bon:= defined_balance_id('NON_TAXABLE_OVT_EARNINGS_ASG_MTD_BON');
1146 g_defined_balance_id.non_taxable_mth := defined_balance_id('TOTAL_NON_TAXABLE_EARNINGS_ASG_MTD_MTH');
1147 g_defined_balance_id.non_taxable_bon := defined_balance_id('TOTAL_NON_TAXABLE_EARNINGS_ASG_MTD_BON');
1148 -- 3546994
1149 g_defined_balance_id.fw_tax_break_mth := defined_balance_id('FOREIGN_WORKER_TAX_BREAK_ASG_MTD_MTH');
1150 g_defined_balance_id.fw_tax_break_bon := defined_balance_id('FOREIGN_WORKER_TAX_BREAK_ASG_MTD_BON');
1151 -- Bug 4322981
1152 g_defined_balance_id.np_prem_mth := defined_balance_id('NP_PREM_EE_ASG_MTD_MTH') ;
1153 g_defined_balance_id.np_prem_bon := defined_balance_id('NP_PREM_EE_ASG_MTD_BON') ;
1154 g_defined_balance_id.hi_prem_mth := defined_balance_id('HI_PREM_EE_ASG_MTD_MTH') ;
1155 g_defined_balance_id.hi_prem_bon := defined_balance_id('HI_PREM_EE_ASG_MTD_BON') ;
1156 g_defined_balance_id.ei_prem_mth := defined_balance_id('EI_PREM_ASG_MTD_MTH') ;
1157 g_defined_balance_id.ei_prem_bon := defined_balance_id('EI_PREM_ASG_MTD_BON') ;
1158 g_defined_balance_id.donation_mth := defined_balance_id('VOLUNTARY_DONATIONS_ASG_MTD_MTH') ;
1159 g_defined_balance_id.donation_bon := defined_balance_id('VOLUNTARY_DONATIONS_ASG_MTD_BON') ;
1160 -- End of 4322981
1161 -- Bug 5684037
1162 g_defined_balance_id.addl_non_tax_mth := defined_balance_id('ADDITIONAL_OTHER_NON_TAXABLE_EARNINGS_ASG_MTD_MTH') ;
1163 g_defined_balance_id.addl_non_tax_bon := defined_balance_id('ADDITIONAL_OTHER_NON_TAXABLE_EARNINGS_ASG_MTD_BON') ;
1164 -- End of Bug 5684037
1165 -- Bug 6470526: 2007 YEA Ledger Statutory Updates
1166 g_defined_balance_id.stck_pur_opt_exec_earn_mth := defined_balance_id('STOCK_PURCHASE_OPTION_EXECUTION_EARNING_ASG_MTD_MTH');
1167 g_defined_balance_id.stck_pur_opt_exec_earn_bon := defined_balance_id('STOCK_PURCHASE_OPTION_EXECUTION_EARNING_ASG_MTD_BON');
1168 g_defined_balance_id.research_payment_mth := defined_balance_id('RESEARCH_PAYMENT_ASG_MTD_MTH');
1169 g_defined_balance_id.research_payment_bon := defined_balance_id('RESEARCH_PAYMENT_ASG_MTD_BON');
1170 -- End of Bug 6470526
1171
1172 end pay_kr_paykrylg_pkg;