[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_PAYKRYRS_PKG
Source
1 package body pay_kr_paykryrs_pkg as
2 /* $Header: paykryrs.pkb 120.20 2007/11/30 14:42:57 vaisriva noship $ */
3
4 ------------------------- Education Exp Cursor----------------------------
5 cursor per_asg_extra_info_educ_csr(p_assignment_id number, p_year number) is
6 select aei_information3
7 ,to_number(aei_information4)
8 from per_assignment_extra_info
9 where assignment_id = p_assignment_id
10 and information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
11 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
12 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
13
14 ------------------------- Housing Exp Cursor----------------------------
15 cursor per_asg_extra_info_hou_csr(p_assignment_id number, p_year number) is
16 select sum(to_number(aei_information3)) aei_information3
17 from per_assignment_extra_info
18 where assignment_id = p_assignment_id
19 and aei_information2 <> 'HST2'
20 and information_type = 'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
21 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
22 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
23
24 ------------------------- Special Exem Cursor----------------------------
25 cursor per_asg_extra_info_sp_csr(p_assignment_id number, p_year number) is
26 select aei_information5 pers_ins_prem
27 ,aei_information6 dis_ins_prem
28 ,aei_information7 gen_med_exp
29 ,aei_information8 l_med_exp_disabled
30 ,aei_information9 l_med_exp_aged
31 ,aei_information24 l_med_exp_emp
32 ,aei_information15 hou_loan_repay
33 ,aei_information17 lt_hou_int_repay
34 ,nvl(aei_information18,0)+nvl(aei_information19,0)+nvl(aei_information20,0)
35 +nvl(aei_information21,0)+nvl(aei_information22,0)+nvl(aei_information23,0) Donations
36 ,aei_information10 emp_educ
37 ,aei_information2 hi_prem
38 ,aei_information3 ei_prem
39 ,aei_information18 p_100p_donation
40 ,aei_information19 l_political_since_040312
41 ,aei_information20 l_political_before_040312
42 ,aei_information23 p_50p_donation
43 ,aei_information30 p_30p_donation
44 ,aei_information22 p_10p_donation
45 ,aei_information27 l_marriage_count
46 ,aei_information29 l_reloc_count
47 ,aei_information28 l_funeral_count
48 ,aei_information26 l_lt_hou_int_repay_gt_15
49 from per_assignment_extra_info
50 where assignment_id = p_assignment_id
51 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
52 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
53 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
54
55 -- Bug 4336742
56 ------------------------- Special Exem2 Cursor----------------------------
57 cursor per_asg_extra_info_sp2_csr(p_assignment_id number, p_year number) is
58 select aei_information2 emp_occ_trg_exp,
59 aei_information3 med_exp_card,
60 aei_information4 l_promotional_fund_don,
61 aei_information5 l_religious_don,
62 aei_information6 l_other_don
63 from per_assignment_extra_info
64 where assignment_id = p_assignment_id
65 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
66 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
67 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
68
69 -- End of 4336742
70
71 ------------------------- Other Exemptions Cursor -----------------------
72 cursor per_asg_extra_info_othr_csr(p_assignment_id number, p_year number) is
73 select aei_information3 pers_pen_prem
74 ,aei_information4 pers_pen_sav
75 ,aei_information6 inv_part_fin2
76 ,aei_information7 emp_crd_exp
77 ,aei_information9 dep_crd_exp
78 ,aei_information8 emp_stk_opt
79 ,aei_information10 emp_crd_drt_exp
80 ,aei_information11 dep_crd_drt_exp -- 4046680
81 ,aei_information12 p_tuition_giro
82 ,aei_information13 cash_receipt -- Bug 4336742
83 ,aei_information2 p_other_exem_np_prem
84 ,aei_information15 p_other_exem_pen_prem -- Bug 6655323
85 ,aei_information14 corporate_pension -- Bug 4764823
86 from per_assignment_extra_info
87 where assignment_id = p_assignment_id
88 and information_type = 'KR_YEA_TAX_EXEM_INFO'
89 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
90 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
91
92 ------------------------- Tax Breaks Cursor ----------------------------
93 cursor per_asg_extra_info_break_csr(p_assignment_id number, p_year number) is
94 select aei_information2 hou_loan_int_repay
95 ,aei_information4 lt_stk_sav1
96 ,aei_information5 lt_stk_sav2
97 from per_assignment_extra_info
98 where assignment_id = p_assignment_id
99 and information_type = 'KR_YEA_TAX_BREAK_INFO'
100 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
101 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
102
103 ------------------------- FW Info Cursor -------------------------------
104 cursor per_asg_extra_info_fw_csr(p_assignment_id number, p_year number) is
105 select nvl(aei_information2,0) + nvl(aei_information3,0)
106 from per_assignment_extra_info
107 where assignment_id = p_assignment_id
108 and information_type = 'KR_YEA_FW_TAX_EXEM_INFO'
109 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
110 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
111
112 ------------------------- Overseas Tax Cursor --------------------------
113 cursor per_asg_extra_info_ovs_csr(p_assignment_id number, p_year number) is
114 select aei_information7 p_ovs_tax_paid_fc,
115 aei_information8 p_ovs_tax_paid_lc,
116 hr_general.decode_territory(aei_information2) p_ovs_country,
117 to_char(fnd_date.canonical_to_date(aei_information1) , 'YYYY.MM.DD') p_ovs_paid_date,
118 to_char(fnd_date.canonical_to_date(aei_information10), 'YYYY.MM.DD') p_ovs_submit_date,
119 aei_information11 p_ovs_location,
120 aei_information12 p_ovs_period,
121 aei_information13 p_ovs_title
122 from per_assignment_extra_info
123 where assignment_id = p_assignment_id
124 and information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
125 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
126
127 ------------------------- FW Tax Break Cursor --------------------------
128 cursor csr_fw_tax_break(p_assignment_id number, p_year number) is
129 select aei_information1 p_immigration_purpose,
130 to_char(fnd_date.canonical_to_date(aei_information2) , 'YYYY.MM.DD') p_fw_contract_date,
131 to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_fw_expiry_date,
132 to_char(fnd_date.canonical_to_date(aei_information5) , 'YYYY.MM.DD') p_fw_application_date,
133 to_char(fnd_date.canonical_to_date(aei_information6) , 'YYYY.MM.DD') p_fw_submit_date
134 from per_assignment_extra_info
135 where assignment_id = p_assignment_id
136 and information_type = 'KR_YEA_FW_TAX_BREAK_INFO'
137 and p_year between to_char(fnd_date.canonical_to_date(aei_information2), 'YYYY')
138 and to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY');
139
140 ------------------------- Prev Employer Cursor --------------------------
141 cursor csr_prev_employer_info(p_assignment_id number, p_year number) is
142 select sum(nvl(aei_information10,0)) p_total_hi_prem,
143 sum(nvl(aei_information11,0)) p_total_ei_prem,
144 sum(nvl(aei_information12,0)) p_total_np_prem,
145 sum(nvl(aei_information16,0)) p_total_pen_prem /* Bug 6655323 */
146 from per_assignment_extra_info
147 where assignment_id = p_assignment_id
148 and information_type = 'KR_YEA_PREV_ER_INFO'
149 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
150
151 ------------------------- Dependent Expense Information ------------------
152 -- Bug 5726158
153 cursor csr_dpnt_expense_info(p_assignment_id number, p_effective_date date) is
154 select sum(nvl(cei_information1,0) + nvl(cei_information2,0)) l_dpnt_pers_ins,
155 sum(nvl(cei_information10,0) + nvl(cei_information11,0)) l_dpnt_dis_ins,
156 sum(nvl(cei_information7,0) + nvl(cei_information8,0)) l_dpnt_cards_exp,
157 sum(nvl(cei_information9,0)) l_dpnt_cash_exp
158 from pay_kr_cont_details_v pkc,
159 per_contact_extra_info_f cei -- Bug 5879106
160 where assignment_id = p_assignment_id
161 -- Bug 5879106
162 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
163 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
164 and to_char(cei.effective_start_date(+), 'YYYY') = to_char(p_effective_date, 'YYYY')
165 --
166 and p_effective_date between emp_start_date and emp_end_date
167 and p_effective_date between cont_start_date and cont_end_date
168 and p_effective_date between nvl(ADDRESS_START_DATE,p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
169 and p_effective_date between nvl(pkc.date_start, p_effective_date)
170 and decode(pkc.cont_information9, 'D', trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1, nvl(pkc.date_end, p_effective_date) )
171 and pay_kr_ff_functions_pkg.is_exempted_dependent( pkc.contact_type,
172 pkc.national_identifier,
173 pkc.cont_information2,
174 pkc.cont_information3,
175 pkc.cont_information4,
176 pkc.cont_information7,
177 pkc.cont_information8,
178 p_effective_date,
179 pkc.cont_information10,
180 pkc.cont_information12,
181 pkc.cont_information13,
182 pkc.cont_information14,
183 cei.contact_extra_info_id
184 ) = 'Y';
185
186 ---------------------- Function to get the YTD balance -----------------------
187
188 function get_balance_value( p_assignment_id number,
189 p_year number,
190 p_ytd_balance_name varchar2) return number
191 is
192
193 cursor csr_defined_bal_id is
194 select pdb.defined_balance_id
195 from pay_balance_types pbt,
196 pay_balance_dimensions dim,
197 pay_defined_balances pdb
198 where pbt.legislation_code = 'KR'
199 and pbt.balance_name = p_ytd_balance_name
200 and dim.legislation_code = 'KR'
201 and dim.dimension_name = '_ASG_YTD'
202 and pdb.legislation_code = 'KR'
203 and pdb.BALANCE_DIMENSION_ID = dim.BALANCE_DIMENSION_ID
204 and pdb.BALANCE_TYPE_ID = pbt.balance_type_id;
205
206 l_dim_id number;
207 l_year_end_date date;
208 l_bal_value number;
209
210 begin
211 l_dim_id := null;
212 l_bal_value := null;
213 l_year_end_date := to_date('3112'||to_char(p_year),'ddmmrrrr');
214
215 open csr_defined_bal_id;
216 fetch csr_defined_bal_id into l_dim_id;
217 close csr_defined_bal_id;
218
219 l_bal_value := pay_balance_pkg.get_value(l_dim_id,
220 p_assignment_id,
221 l_year_end_date);
222 return l_bal_value;
223 end get_balance_value;
224
225
226
227
228
229
230
231 --------------------------------------------------------------------------------
232 procedure data(
233 p_assignment_id in number,
234 p_year in number,
235 ----------------Education Exp--------------------
236 p_edu_p_count out nocopy number,
237 p_edu_h_count out nocopy number,
238 p_edu_u_count out nocopy number,
239 p_edu_d_count out nocopy number,
240 p_edu_exp_p out nocopy number,
241 p_edu_exp_h out nocopy number,
242 p_edu_exp_u out nocopy number,
243 p_edu_exp_d out nocopy number,
244 p_edu_exp_total out nocopy number,
245 --------------- Housing Saving Total--------------
246 p_hou_exp out nocopy number,
247 --------------- Special Exems --------------------
248 p_pers_ins_prem out nocopy number,
249 p_dis_ins_prem out nocopy number,
250 p_gen_med_exp out nocopy number,
251 p_emp_aged_dis_med_exp out nocopy number,
252 p_med_exp_card out nocopy number, -- Bug 4336742
253 p_hou_loan_repay out nocopy number,
254 p_lt_hou_int_repay out nocopy number,
255 p_donations out nocopy number,
256 p_emp_educ out nocopy number,
257 p_sp_prem_hi_sub out nocopy number,
258 p_sp_prem_ei_sub out nocopy number,
259 p_100p_donation out nocopy number,
260 p_50p_donation out nocopy number,
261 p_30p_donation out nocopy number,
262 p_10p_donation out nocopy number,
263 p_political_donation out nocopy number,
264 p_marr_reloc_funr_exem out nocopy number,
265 p_lt_hou_int_repay_gt_15 out nocopy number,
266 --------------- Other Exems --------------------
267 p_pers_pen_prem out nocopy number,
268 p_pers_pen_sav out nocopy number,
269 p_inv_part_fin2 out nocopy number,
270 p_emp_crd_exp out nocopy number,
271 p_dep_crd_exp out nocopy number,
272 p_emp_stk_opt out nocopy number,
273 p_tuition_giro out nocopy number,
274 p_cash_receipt out nocopy number, -- Bug 4336742
275 p_other_exem_np_prem out nocopy number,
276 p_other_exem_pen_prem out nocopy number, -- Bug 6655323
277 --------------- Tax Breaks -----------------------
278 p_hou_loan_int_repay_break out nocopy number,
279 p_lt_stk_sav1 out nocopy number,
280 p_lt_stk_sav2 out nocopy number,
281 --------------- FW Info --------------------------
282 p_emp_fw_exp out nocopy number,
283 --------------- Ovs Tax Break --------------------
284 p_ovs_tax_paid_fc out nocopy number,
285 p_ovs_tax_paid_lc out nocopy number,
286 p_ovs_country out nocopy varchar2,
287 p_ovs_paid_date out nocopy varchar2,
288 p_ovs_submit_date out nocopy varchar2,
289 p_ovs_location out nocopy varchar2,
290 p_ovs_period out nocopy varchar2,
291 p_ovs_title out nocopy varchar2,
292 --------------- FW Tax Break ---------------------
293 p_immigration_purpose out nocopy varchar2,
294 p_fw_contract_date out nocopy varchar2,
295 p_fw_expiry_date out nocopy varchar2,
296 p_fw_application_date out nocopy varchar2,
297 p_fw_submit_date out nocopy varchar2,
298 --------------- Prev Employer Info ---------------
299 p_total_hi_prem out nocopy number,
300 p_total_ei_prem out nocopy number,
301 p_total_np_prem out nocopy number,
302 p_total_pen_prem out nocopy number, /* Bug 6655323 */
303 --------------- Balance Values -------------------
304 p_np_prem_main out nocopy number,
305 p_pen_prem_main out nocopy number, /* Bug 6655323 */
306 p_ei_prem_main out nocopy number,
307 p_hi_prem_main out nocopy number,
308 p_corp_pension out nocopy number, --4764823
309 p_emp_ins_included out nocopy varchar2,
310 p_emp_med_included out nocopy varchar2,
311 p_emp_edu_included out nocopy varchar2,
312 p_emp_card_included out nocopy varchar2
313 )
314 is
315 l_edu_p_count number;
316 l_edu_h_count number;
317 l_edu_u_count number;
318 l_edu_d_count number;
319 l_edu_type varchar2(5);
320 l_edu_exp number;
321 l_emp_occ_trg_exp number ; -- Bug 4336742
322 l_emp_crd_drt_exp number;
323 l_dep_crd_drt_exp number; -- 4046680
324 l_marriage_count number;
325 l_reloc_count number;
326 l_funeral_count number;
327 l_med_exp_disabled number;
328 l_med_exp_aged number;
329 l_med_exp_emp number;
330 l_political_since_040312 number;
331 l_political_before_040312 number;
332 l_dummy number;
333 l_corp_pension number;
334 l_corp_pension_bal number;
335 l_promotional_fund_don number;
336 l_religious_don number;
337 l_other_don number;
338 l_10p_donation number;
339 -- Bug 5726158
340 l_dpnt_pers_ins number;
341 l_dpnt_dis_ins number;
342 l_dpnt_cards_exp number;
343 l_dpnt_cash_exp number;
344
345
346
347 begin
348 ----------------------------- Education Expenses ---------------------------
349 l_edu_p_count := 0;
350 l_edu_h_count := 0;
351 l_edu_u_count := 0;
352 l_edu_d_count := 0;
353 p_edu_exp_p := null;
354 p_edu_exp_h := null;
355 p_edu_exp_u := null;
356 p_edu_exp_d := null;
357 p_hou_exp := null;
358
359 open per_asg_extra_info_educ_csr(p_assignment_id, p_year);
360
361 loop
362
363 fetch per_asg_extra_info_educ_csr
364 into l_edu_type, l_edu_exp;
365
366 exit when per_asg_extra_info_educ_csr%NOTFOUND;
367
368 if l_edu_type = 'P' then
369 p_edu_exp_p := nvl(p_edu_exp_p,0) + l_edu_exp;
370 l_edu_p_count := l_edu_p_count + 1;
371 elsif l_edu_type = 'H' then
372 p_edu_exp_h := nvl(p_edu_exp_h,0) + l_edu_exp;
373 l_edu_h_count := l_edu_h_count + 1;
374 elsif l_edu_type = 'U' then
375 p_edu_exp_u := nvl(p_edu_exp_u,0) + l_edu_exp;
376 l_edu_u_count := l_edu_u_count + 1;
377 elsif l_edu_type = 'D' then
378 p_edu_exp_d := nvl(p_edu_exp_d,0) + l_edu_exp;
379 l_edu_d_count := l_edu_d_count + 1;
380 end if;
381
382 end loop;
383
384 if per_asg_extra_info_educ_csr%ISOPEN then
385 close per_asg_extra_info_educ_csr;
386 end if;
387
388 p_edu_p_count := l_edu_p_count;
389 p_edu_h_count := l_edu_h_count;
390 p_edu_u_count := l_edu_u_count;
391 p_edu_d_count := l_edu_d_count;
392
393
394
395 ---------------------------- Housing Saving Total ----------------------------
396 open per_asg_extra_info_hou_csr(p_assignment_id, p_year);
397 fetch per_asg_extra_info_hou_csr into p_hou_exp;
398 close per_asg_extra_info_hou_csr;
399
400 ---------------------------- Special Exems -----------------------------------
401 open per_asg_extra_info_sp_csr(p_assignment_id, p_year);
402 fetch per_asg_extra_info_sp_csr
403 into p_pers_ins_prem
404 ,p_dis_ins_prem
405 ,p_gen_med_exp
406 ,l_med_exp_disabled
407 ,l_med_exp_aged
408 ,l_med_exp_emp
409 ,p_hou_loan_repay
410 ,p_lt_hou_int_repay
411 ,p_donations
412 ,p_emp_educ
413 ,p_sp_prem_hi_sub
414 ,p_sp_prem_ei_sub
415 ,p_100p_donation
416 ,l_political_since_040312
417 ,l_political_before_040312
418 ,p_50p_donation
419 ,p_30p_donation
420 ,l_10p_donation
421 ,l_marriage_count
422 ,l_reloc_count
423 ,l_funeral_count
424 ,p_lt_hou_int_repay_gt_15;
425
426 close per_asg_extra_info_sp_csr;
427
428 -- Bug 5726158
429 open csr_dpnt_expense_info(p_assignment_id, to_date('3112'||to_char(p_year), 'DDMMYYYY'));
430 fetch csr_dpnt_expense_info into l_dpnt_pers_ins,
431 l_dpnt_dis_ins,
432 l_dpnt_cards_exp,
433 l_dpnt_cash_exp;
434 close csr_dpnt_expense_info;
435 p_pers_ins_prem := nvl(p_pers_ins_prem,0) + nvl(l_dpnt_pers_ins,0);
436 p_dis_ins_prem := nvl(p_dis_ins_prem,0) + nvl(l_dpnt_dis_ins,0);
437
438 -- End of Bug 5726158
439
440
441 -- Bug 4336742
442 open per_asg_extra_info_sp2_csr(p_assignment_id, p_year) ;
443 fetch per_asg_extra_info_sp2_csr into l_emp_occ_trg_exp,
444 p_med_exp_card,
445 l_promotional_fund_don,
446 l_religious_don,
447 l_other_don;
448 close per_asg_extra_info_sp2_csr ;
449
450 if p_emp_educ is null and l_emp_occ_trg_exp is null then
451 p_emp_educ := null ;
452 else
453 p_emp_educ := nvl(p_emp_educ, 0) + nvl(l_emp_occ_trg_exp, 0) ;
454 end if ;
455
456 -- End of 4336742
457
458 p_edu_exp_total := nvl(p_emp_educ,0) +
459 nvl(p_edu_exp_p,0) +
460 nvl(p_edu_exp_h,0) +
461 nvl(p_edu_exp_u,0) +
462 nvl(p_edu_exp_d,0);
463
464 -- sum should be null if all values are null
465 if (p_emp_educ is null and
466 p_edu_exp_p is null and
467 p_edu_exp_h is null and
468 p_edu_exp_u is null and
469 p_edu_exp_u is null) then
470 p_edu_exp_total := null;
471 end if;
472
473 p_emp_aged_dis_med_exp := nvl(l_med_exp_disabled,0 )+
474 nvl(l_med_exp_aged ,0) +
475 nvl(l_med_exp_emp ,0) ;
476
477
478 -- sum should be null if all values are null
479 if (l_med_exp_disabled is null and
480 l_med_exp_aged is null and
481 l_med_exp_emp is null) then
482 p_emp_aged_dis_med_exp := null;
483 end if;
484
485
486
487 p_marr_reloc_funr_exem := null;
488
489
490 p_marr_reloc_funr_exem := 1000000 * ( nvl(l_marriage_count,0) +
491 nvl(l_reloc_count ,0) +
492 nvl(l_funeral_count ,0) );
493 -- sum should be null if all values are null
494 if (l_marriage_count is null and
495 l_reloc_count is null and
496 l_funeral_count is null) then
497 p_marr_reloc_funr_exem := null;
498 end if;
499
500 -- calculate political donation
501 p_political_donation := Least(100000, nvl(l_political_since_040312,0) );
502 l_dummy := nvl(l_promotional_fund_don,0) +
503 nvl(p_100p_donation,0) +
504 nvl(l_political_since_040312,0 ) +
505 nvl(l_political_before_040312,0) - p_political_donation;
506
507 -- sum should be null if all values are null
508 if( l_promotional_fund_don is null and
509 p_100p_donation is null and
510 l_political_since_040312 is null and
511 l_political_before_040312 is null) then
512 p_100p_donation := null;
513 else
514 p_100p_donation := l_dummy;
515 end if;
516
517 -- calculate 10 percent donations
518 p_10p_donation := nvl(l_10p_donation,0) + nvl(l_religious_don,0);
519 if l_10p_donation is null and
520 l_religious_don is null then
521 p_10p_donation := null;
522 end if;
523
524 --
525 if l_political_since_040312 is null then
526 p_political_donation := null;
527 end if;
528
529 ---------------------------- Other Exems -----------------------------------
530 open per_asg_extra_info_othr_csr(p_assignment_id, p_year);
531 fetch per_asg_extra_info_othr_csr
532 into p_pers_pen_prem
533 ,p_pers_pen_sav
534 ,p_inv_part_fin2
535 ,p_emp_crd_exp
536 ,p_dep_crd_exp
537 ,p_emp_stk_opt
538 ,l_emp_crd_drt_exp
539 ,l_dep_crd_drt_exp
540 ,p_tuition_giro
541 ,p_cash_receipt -- Bug 4336742
542 ,p_other_exem_np_prem
543 ,p_other_exem_pen_prem -- Bug 6655323
544 ,l_corp_pension; -- Bug 4764823
545
546 close per_asg_extra_info_othr_csr;
547
548 -- 4046680: emp credit/direct exp
549 l_dummy := nvl(p_emp_crd_exp,0) + nvl(l_emp_crd_drt_exp,0);
550 if ( p_emp_crd_exp is null and
551 l_emp_crd_drt_exp is null ) then
552 p_emp_crd_exp := null;
553 else
554 p_emp_crd_exp := l_dummy;
555 end if;
556
557 -- 4046680: dep credit/direct exp
558 l_dummy := nvl(p_dep_crd_exp,0) + nvl(l_dep_crd_drt_exp,0);
559 if ( p_dep_crd_exp is null and
560 l_dep_crd_drt_exp is null ) then
561 p_dep_crd_exp := null;
562 else
563 p_dep_crd_exp := l_dummy;
564 end if;
565
566 -- Bug 5726158
567 p_dep_crd_exp := nvl(p_dep_crd_exp,0)+ nvl(l_dpnt_cards_exp,0);
568 p_cash_receipt := nvl(p_cash_receipt,0) + nvl(l_dpnt_cash_exp,0);
569 -- End of Bug 5726158
570
571 ---------------------------- Tax Breaks -----------------------------------
572 open per_asg_extra_info_break_csr(p_assignment_id, p_year);
573 fetch per_asg_extra_info_break_csr
574 into p_hou_loan_int_repay_break
575 ,p_lt_stk_sav1
576 ,p_lt_stk_sav2;
577
578 close per_asg_extra_info_break_csr;
579
580 ---------------------------- FW Info --------------------------------------
581 open per_asg_extra_info_fw_csr(p_assignment_id, p_year);
582 fetch per_asg_extra_info_fw_csr into p_emp_fw_exp;
583
584 close per_asg_extra_info_fw_csr; ---#### no more used
585
586 ---------------------------- Ovs Tax Break ---------------------------------
587 open per_asg_extra_info_ovs_csr(p_assignment_id, p_year);
588 fetch per_asg_extra_info_ovs_csr
589 into p_ovs_tax_paid_fc,
590 p_ovs_tax_paid_lc,
591 p_ovs_country,
592 p_ovs_paid_date,
593 p_ovs_submit_date,
594 p_ovs_location,
595 p_ovs_period,
596 p_ovs_title;
597
598 close per_asg_extra_info_ovs_csr;
599
600 ---------------------------- FW Tax Break ---------------------------------
601 open csr_fw_tax_break(p_assignment_id, p_year);
602 fetch csr_fw_tax_break
603 into p_immigration_purpose,
604 p_fw_contract_date,
605 p_fw_expiry_date,
606 p_fw_application_date,
607 p_fw_submit_date ;
608
609 close csr_fw_tax_break;
610
611 ---------------------------- Prev Employer Info ---------------------------
612 open csr_prev_employer_info(p_assignment_id, p_year);
613 fetch csr_prev_employer_info
614 into p_total_hi_prem,
615 p_total_ei_prem,
616 p_total_np_prem,
617 p_total_pen_prem; /* Bug 6655323 */
618
619 close csr_prev_employer_info;
620
621 ---------------------------- Get YTD Balances ---------------------------
622 -- Bug 4446381: Added nvl checks, so that even if no values for premium fields
623 -- are entered in the YEA Information form, the balance values
624 -- show up in the reclaim sheet
625 --
626 p_np_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'NP_PREM_EE'), 0)
627 + nvl(p_other_exem_np_prem, 0) ;
628 -- Sum is null if all components are null
629 if get_balance_value(p_assignment_id, p_year,'NP_PREM_EE') is null
630 and p_other_exem_np_prem is null
631 then
632 p_np_prem_main := null ;
633 end if ;
634 -- Bug 6655323
635 p_pen_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'Pension Premium'), 0)
636 + nvl(p_other_exem_pen_prem, 0);
637 -- Sum is null if all components are null
638 if get_balance_value(p_assignment_id, p_year,'Pension Premium') is null
639 and p_other_exem_pen_prem is null
640 then
641 p_pen_prem_main := null ;
642 end if ;
643 --
644 p_ei_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'EI_PREM'), 0)
645 + nvl(p_sp_prem_ei_sub, 0) ;
646 -- Sum is null if all components are null
647 if get_balance_value(p_assignment_id, p_year,'EI_PREM') is null
648 and p_sp_prem_ei_sub is null
649 then
650 p_ei_prem_main := null ;
651 end if ;
652
653 p_hi_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'HI_PREM_EE'), 0)
654 + nvl(p_sp_prem_hi_sub, 0) ;
655 -- Sum is null if all components are null
656 if get_balance_value(p_assignment_id, p_year,'HI_PREM_EE') is null
657 and p_sp_prem_hi_sub is null
658 then
659 p_hi_prem_main := null ;
660 end if ;
661
662 -- 4863731
663 l_corp_pension_bal := get_balance_value(p_assignment_id, p_year,'CORPORATE_PENSION');
664 p_corp_pension := nvl(l_corp_pension_bal,0) + nvl(l_corp_pension,0);
665 if l_corp_pension_bal is null and
666 l_corp_pension is null then
667
668 p_corp_pension := null;
669
670 end if;
671
672 -- End of 4446381
673
674 ------------------- Set Ins,Med,Edu,Card flags -------------------
675 p_emp_ins_included := null;
676 p_emp_med_included := null;
677 p_emp_edu_included := null;
678 p_emp_card_included := null;
679
680 -- ins included
681 if p_pers_ins_prem > 0 or p_dis_ins_prem > 0 then
682 p_emp_ins_included := 'O';
683 end if;
684
685 -- med included
686 if l_med_exp_emp > 0 then
687 p_emp_med_included := 'O';
688 end if;
689
690 -- edu included
691 if p_emp_educ > 0 then
692 p_emp_edu_included := 'O';
693 end if;
694
695 -- card included
696 if p_emp_crd_exp > 0 or p_tuition_giro > 0 or p_cash_receipt > 0 then
697 p_emp_card_included := 'O';
698 end if;
699
700 end data;
701
702 ---------------Prodecure to fetch Employee Expense Amounts------------------
703 -- Bug # 5446051
704 procedure EMP_EXPENSE_DETAILS (p_emp_assignment_id in number
705 ,p_year in number
706 ,p_emp_ins_exp_nts out nocopy number
707 ,p_emp_ins_exp_oth out nocopy number
708 ,p_emp_med_exp_nts out nocopy number
709 ,p_emp_med_exp_oth out nocopy number
710 ,p_emp_edu_exp_nts out nocopy number
711 ,p_emp_edu_exp_oth out nocopy number
712 ,p_emp_card_exp_nts out nocopy number
713 ,p_emp_card_exp_oth out nocopy number
714 ,p_emp_cash_exp_nts out nocopy number
715 ,p_emp_don_exp_nts out nocopy number
716 ,p_emp_don_exp_oth out nocopy number)
717 is
718
719 cursor sp_tax_exem_info is
720 select (nvl(aei_information2,0) + nvl(aei_information3,0) +
721 nvl(aei_information5,0) + nvl(aei_information6,0)) --Insurance total -- Bug 5726158
722 ,nvl(aei_information24,0) --Medical Exem Total
723 ,nvl(aei_information10,0) --Education Exem Total1
724 ,(nvl(aei_information18,0)
725 + nvl(aei_information19,0)
726 + nvl(aei_information20,0)
727 + nvl(aei_information22,0)
728 + nvl(aei_information23,0)
729 + nvl(aei_information30,0)) --Donation Total1
730 ,nvl(aei_information19,0) --Political_donation
731 ,nvl(aei_information1,fnd_date.date_to_canonical(to_date('31-12-'||to_char(p_year),'dd-mm-rrrr'))) --Effective Date
732 from per_assignment_extra_info
733 where ASSIGNMENT_ID=p_emp_assignment_id
734 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
735 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
736
737 cursor sp_tax_exem_info2 is
738 select nvl(aei_information2,0) --Education Exem Total2
739 ,(nvl(aei_information4,0)
740 +nvl(aei_information5,0)
741 +nvl(aei_information6,0)) --Donation Total2
742 from per_assignment_extra_info
743 where ASSIGNMENT_ID=p_emp_assignment_id
744 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
745 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
746
747 cursor tax_exem_info is
748 select (nvl(aei_information10,0)
749 +nvl(aei_information7,0)
750 +nvl(aei_information12,0)) --Card Total
751 ,nvl(aei_information13,0) --Cash Total
752 from per_assignment_extra_info
753 where ASSIGNMENT_ID=p_emp_assignment_id
754 and information_type = 'KR_YEA_TAX_EXEM_INFO'
755 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
756
757 cursor csr_get_ins_bal(p_balance_name in varchar2,
758 p_dimension_name in varchar2) is
759 select defined_balance_id
760 from pay_defined_balances pdb
761 ,pay_balance_types pbt
762 ,pay_balance_dimensions pbd
763 where pbt.balance_type_id = pdb.balance_type_id
764 and pdb.balance_dimension_id = pbd.balance_dimension_id
765 and pbt.balance_name = p_balance_name
766 and pbd.dimension_name = p_dimension_name
767 and pdb.legislation_code = 'KR';
768
769 cursor emp_exp is
770 select aei_information2 --Medical NTS
771 ,aei_information3 --Education NTS
772 ,aei_information4 --Cards NTS
773 ,aei_information5 --Donation NTS
774 ,aei_information6 --Insurance NTS -- Bug 5726158
775 from per_assignment_extra_info
776 where ASSIGNMENT_ID=p_emp_assignment_id
777 and information_type = 'KR_YEA_EMP_EXPENSE_DETAILS'
778 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
779
780 -- Local Variables
781
782 l_hi_prem_bal NUMBER;
783 l_ei_prem_bal NUMBER;
784 l_prev_hi_prem NUMBER; -- Bug 5735177
785 l_prev_ei_prem NUMBER;
786 l_prev_np_prem NUMBER;
787 l_prev_pen_prem NUMBER; /* Bug 6655323 */
788 l_hi_bal_id NUMBER;
789 l_ei_bal_id NUMBER;
790 l_virtual_date DATE := to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
791 l_don_tax_break NUMBER;
792 l_ins_total NUMBER;
793 l_ins_oth number;
794 l_med_exem_total number;
795 l_edu_exem_total1 number;
796 l_don_total1 number;
797 l_edu_exem_total2 number;
798 l_don_total2 number;
799 l_card_total number;
800 l_cash_total number;
801 l_ins_nts number;
802 l_med_nts number;
803 l_edu_nts number;
804 l_card_nts number;
805 l_don_nts number;
806 l_effective_date varchar2(20);
807 l_political_donation number;
808
809 begin
810 open csr_get_ins_bal('HI_PREM_EE','_ASG_YTD');
811 fetch csr_get_ins_bal into l_hi_bal_id;
812 close csr_get_ins_bal;
813
814 open csr_get_ins_bal('EI_PREM','_ASG_YTD');
815 fetch csr_get_ins_bal into l_ei_bal_id;
816 close csr_get_ins_bal;
817 ----------------------------------------------------------------------
818 -- Get the balance values of HI_PREM_EE_ASG_YTD and EI_PREM_ASG_YTD
819 ----------------------------------------------------------------------
820 l_hi_prem_bal := pay_balance_pkg.get_value( l_hi_bal_id,
821 p_emp_assignment_id,
822 l_virtual_date);
823
824 l_ei_prem_bal := pay_balance_pkg.get_value (l_ei_bal_id,
825 p_emp_assignment_id,
826 l_virtual_date);
827
828 -- Bug 5735177
829 open csr_prev_employer_info(p_emp_assignment_id,p_year);
830 fetch csr_prev_employer_info into l_prev_hi_prem, l_prev_ei_prem, l_prev_np_prem, l_prev_pen_prem;
831 close csr_prev_employer_info;
832
833 open sp_tax_exem_info;
834 fetch sp_tax_exem_info into l_ins_total,
835 l_med_exem_total,
836 l_edu_exem_total1,
837 l_don_total1,
838 l_political_donation,
839 l_effective_date;
840 close sp_tax_exem_info;
841 -- bug 5726158
842 l_ins_total := nvl(l_ins_total,0) + l_hi_prem_bal + l_ei_prem_bal
843 + nvl(l_prev_hi_prem,0) + nvl(l_prev_ei_prem,0);
844
845 open sp_tax_exem_info2;
846 fetch sp_tax_exem_info2 into l_edu_exem_total2,
847 l_don_total2;
848 close sp_tax_exem_info2;
849
850 open tax_exem_info;
851 fetch tax_exem_info into l_card_total,
852 l_cash_total;
853 close tax_exem_info;
854
855 open emp_exp;
856 fetch emp_exp into l_med_nts,
857 l_edu_nts,
858 l_card_nts,
859 l_don_nts,
860 l_ins_nts;
861 close emp_exp;
862
863 l_don_tax_break := pay_kr_yea_form_pkg.get_donation_tax_break(l_effective_date,l_political_donation);
864
865 p_emp_ins_exp_nts := nvl(l_ins_nts,0); -- Bug 5726158
866 p_emp_ins_exp_oth := nvl(l_ins_total,0) - nvl(l_ins_nts,0);
867
868 p_emp_med_exp_nts := nvl(l_med_nts,0);
869 p_emp_med_exp_oth := nvl(l_med_exem_total,0)
870 - nvl(l_med_nts,0);
871
872 p_emp_edu_exp_nts := nvl(l_edu_nts,0);
873 p_emp_edu_exp_oth := (nvl(l_edu_exem_total1,0)
874 + nvl(l_edu_exem_total2,0))
875 - nvl(l_edu_nts,0);
876
877 p_emp_card_exp_nts := nvl(l_card_nts,0);
878 p_emp_card_exp_oth := nvl(l_card_total,0)
879 - nvl(l_card_nts,0);
880
881 p_emp_cash_exp_nts := nvl(l_cash_total,0);
882
883 p_emp_don_exp_nts := nvl(l_don_nts,0);
884 p_emp_don_exp_oth := (nvl(l_don_total1,0)
885 + nvl(l_don_total2,0))
886 - (nvl(l_don_tax_break,0)
887 + nvl(l_don_nts,0));
888
889 end;
890 -- End of Bug # 5446051
891 -- Bug 5654127
892 -------------------------------------------------------------------------------------
893 Function EMP_EXPENSE_DETAILS (p_assignment_id in number -- Bug 5856504
894 ,p_year in number
895 ,p_emp_ins_exp_nts out nocopy number
896 ,p_emp_ins_exp_oth out nocopy number
897 ,p_emp_med_exp_nts out nocopy number
898 ,p_emp_med_exp_oth out nocopy number
899 ,p_emp_edu_exp_nts out nocopy number
900 ,p_emp_edu_exp_oth out nocopy number
901 ,p_emp_card_exp_nts out nocopy number
902 ,p_emp_card_exp_oth out nocopy number
903 ,p_emp_cash_exp_nts out nocopy number
904 ,p_emp_don_exp_nts out nocopy number
905 ,p_emp_don_exp_oth out nocopy number) return number
906 -------------------------------------------------------------------------------------
907 is
908 p_emp_assignment_id number; -- Bug 5856504
909 begin
910 p_emp_assignment_id := p_assignment_id; -- Bug 5856504
911 EMP_EXPENSE_DETAILS (p_emp_assignment_id
912 ,p_year
913 ,p_emp_ins_exp_nts
914 ,p_emp_ins_exp_oth
915 ,p_emp_med_exp_nts
916 ,p_emp_med_exp_oth
917 ,p_emp_edu_exp_nts
918 ,p_emp_edu_exp_oth
919 ,p_emp_card_exp_nts
920 ,p_emp_card_exp_oth
921 ,p_emp_cash_exp_nts
922 ,p_emp_don_exp_nts
923 ,p_emp_don_exp_oth);
924 return 1;
925 end;
926 -- End of 5654127
927
928 end pay_kr_paykryrs_pkg;