[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_PAYKRYRS_PKG
Source
1 package body pay_kr_paykryrs_pkg as
2 /* $Header: paykryrs.pkb 120.39.12020000.3 2012/12/11 18:46:58 mdubasi ship $ */
3
4 -- Bug 9079478
5 g_tax t_element;
6
7 ------------------------- Education Exp Cursor----------------------------
8 cursor per_asg_extra_info_educ_csr(p_assignment_id number, p_year number) is
9 select aei_information3
10 ,to_number(aei_information4)
11 from per_assignment_extra_info
12 where assignment_id = p_assignment_id
13 and information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
14 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
15 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
16
17 ------------------------- Education Exp Cursor from 2009----------------------------
18 -- Bug 9341235
19 cursor per_asg_extra_info_educ_2009(p_assignment_id number, p_year number) is
20 select aei_information3
21 ,sum(to_number(aei_information4))
22 from per_assignment_extra_info
23 where assignment_id = p_assignment_id and
24 information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
25 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
26 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr')
27 group by aei_information5,aei_information3;
28
29 ------------------------- Special Exem Cursor----------------------------
30 cursor per_asg_extra_info_sp_csr(p_assignment_id number, p_year number) is
31 select aei_information5 pers_ins_prem
32 ,aei_information6 dis_ins_prem
33 ,aei_information7 gen_med_exp
34 ,aei_information8 l_med_exp_disabled
35 ,aei_information9 l_med_exp_aged
36 ,aei_information24 l_med_exp_emp
37 ,aei_information15 hou_loan_repay
38 ,aei_information17 lt_hou_int_repay
39 ,nvl(aei_information18,0)+nvl(aei_information19,0)+nvl(aei_information20,0)
40 +nvl(aei_information21,0)+nvl(aei_information22,0)+nvl(aei_information23,0) Donations
41 ,aei_information10 emp_educ
42 ,aei_information2 hi_prem
43 ,aei_information3 ei_prem
44 ,aei_information18 p_100p_donation
45 ,aei_information19 l_political_since_040312
46 ,aei_information20 l_political_before_040312
47 ,aei_information23 p_50p_donation
48 ,aei_information30 p_30p_donation
49 ,aei_information22 p_15p_donation
50 ,aei_information27 l_marriage_count
51 ,aei_information29 l_reloc_count
52 ,aei_information28 l_funeral_count
53 ,aei_information26 l_lt_hou_int_repay_gt_15
54 from per_assignment_extra_info
55 where assignment_id = p_assignment_id
56 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
57 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
58 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
59
60 -- Bug 4336742
61 ------------------------- Special Exem2 Cursor----------------------------
62 cursor per_asg_extra_info_sp2_csr(p_assignment_id number, p_year number) is
63 select aei_information2 emp_occ_trg_exp,
64 aei_information3 med_exp_card,
65 aei_information4 l_promotional_fund_don,
66 aei_information5 l_religious_don,
67 aei_information6 l_other_don,
68 aei_information7 l_public_legal_don, -- Bug 7508706
69 aei_information11 ltci_prem, -- Bug 7644535
70 aei_information13 l_lt_1500_won_limit -- Bug 9079478
71 from per_assignment_extra_info
72 where assignment_id = p_assignment_id
73 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
74 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
75 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
76
77 -- End of 4336742
78 -- Bug 10184055
79 ------------------------- Special Exem3 Cursor----------------------------
80 cursor per_asg_extra_info_sp3_csr(p_assignment_id number, p_year number) is
81 select aei_information4,
82 aei_information6,
83 aei_information7
84 from per_assignment_extra_info
85 where assignment_id = p_assignment_id
86 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO3'
87 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
88 ------------------------- Other Exemptions Cursor -----------------------
89 cursor per_asg_extra_info_othr_csr(p_assignment_id number, p_year number) is
90 select aei_information3 pers_pen_prem
91 ,aei_information4 pers_pen_sav
92 ,aei_information6 inv_part_fin2
93 ,aei_information25 inv_part_fin3 -- Bug 9079478
94 ,aei_information7 emp_crd_exp
95 ,aei_information9 dep_crd_exp
96 ,aei_information8 emp_stk_opt
97 ,aei_information10 emp_crd_drt_exp
98 ,aei_information11 dep_crd_drt_exp -- 4046680
99 ,aei_information12 p_tuition_giro
100 ,aei_information13 cash_receipt -- Bug 4336742
101 ,aei_information2 p_other_exem_np_prem
102 ,aei_information15 p_other_exem_pen_prem -- Bug 6655323
103 ,aei_information14 corporate_pension -- Bug 4764823
104 ,aei_information20 small_bus_install -- Bug 7508706
105 ,aei_information21 company_related_expense -- Bug 7615517
106 ,aei_information22 long_term_stck_fund_1yr -- Bug 7615517
107 ,aei_information23 long_term_stck_fund_2yr -- Bug 7615517
108 ,aei_information24 long_term_stck_fund_3yr -- Bug 7615517
109 ,nvl(to_number(aei_information26),0) -- Bug 9079478
110 ,nvl(to_number(aei_information27),0) -- Bug 9079478
111 from per_assignment_extra_info
112 where assignment_id = p_assignment_id
113 and information_type = 'KR_YEA_TAX_EXEM_INFO'
114 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
115 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
116
117 ------------------------- Tax Breaks Cursor ----------------------------
118 cursor per_asg_extra_info_break_csr(p_assignment_id number, p_year number) is
119 select aei_information2 hou_loan_int_repay
120 ,aei_information4 lt_stk_sav1
121 ,aei_information5 lt_stk_sav2
122 from per_assignment_extra_info
123 where assignment_id = p_assignment_id
124 and information_type = 'KR_YEA_TAX_BREAK_INFO'
125 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
126 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
127
128 ------------------------- FW Info Cursor -------------------------------
129 cursor per_asg_extra_info_fw_csr(p_assignment_id number, p_year number) is
130 select nvl(aei_information2,0) + nvl(aei_information3,0)
131 from per_assignment_extra_info
132 where assignment_id = p_assignment_id
133 and information_type = 'KR_YEA_FW_TAX_EXEM_INFO'
134 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
135 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
136
137 ------------------------- Overseas Tax Cursor --------------------------
138 cursor per_asg_extra_info_ovs_csr(p_assignment_id number, p_year number) is
139 select aei_information7 p_ovs_tax_paid_fc,
140 aei_information8 p_ovs_tax_paid_lc,
141 hr_general.decode_territory(aei_information2) p_ovs_country,
142 to_char(fnd_date.canonical_to_date(aei_information1) , 'YYYY.MM.DD') p_ovs_paid_date,
143 to_char(fnd_date.canonical_to_date(aei_information10), 'YYYY.MM.DD') p_ovs_submit_date,
144 aei_information11 p_ovs_location,
145 aei_information12 p_ovs_period,
146 aei_information13 p_ovs_title
147 from per_assignment_extra_info
148 where assignment_id = p_assignment_id
149 and information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
150 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
151
152 ------------------------- FW Tax Break Cursor --------------------------
153 cursor csr_fw_tax_break(p_assignment_id number, p_year number) is
154 select aei_information1 p_immigration_purpose,
155 to_char(fnd_date.canonical_to_date(aei_information2) , 'YYYY.MM.DD') p_fw_contract_date,
156 to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_fw_expiry_date,
157 to_char(fnd_date.canonical_to_date(aei_information5) , 'YYYY.MM.DD') p_fw_application_date,
158 to_char(fnd_date.canonical_to_date(aei_information6) , 'YYYY.MM.DD') p_fw_submit_date
159 from per_assignment_extra_info
160 where assignment_id = p_assignment_id
161 and information_type = 'KR_YEA_FW_TAX_BREAK_INFO'
162 and p_year between to_char(fnd_date.canonical_to_date(aei_information2), 'YYYY')
163 and to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY');
164
165 ---------------------------SMB Tax Reductions-------------------------------
166 cursor csr_smb_tax_red(p_assignment_id number, p_year number) is
167 select to_char(fnd_date.canonical_to_date(aei_information1) , 'YYYY.MM.DD') p_smb_start_date,
168 to_char(fnd_date.canonical_to_date(aei_information2) , 'YYYY.MM.DD') p_smb_end_date
169 from per_assignment_extra_info
170 where assignment_id = p_assignment_id
171 and information_type = 'KR_YEA_TAX_REDUCTION_SMB'
172 and p_year between to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')
173 and to_char(fnd_date.canonical_to_date(aei_information2), 'YYYY');
174
175 ---------------------------Teachers Treaty Reductions-------------------------------
176 cursor csr_teach_tax_red(p_assignment_id number, p_year number) is
177 select to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_teach_application_date,
178 to_char(fnd_date.canonical_to_date(aei_information4) , 'YYYY.MM.DD') p_teach_submit_date
179 from per_assignment_extra_info
180 where assignment_id = p_assignment_id
181 and information_type = 'KR_YEA_TAX_REDUCTION_TEACHERS'
182 and p_year between to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY')
183 and to_char(fnd_date.canonical_to_date(aei_information4), 'YYYY');
184
185 ---------------------------Marine Tax Reductions-------------------------------
186 cursor csr_marine_tax_red(p_assignment_id number, p_year number) is
187 select to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_marine_confirm_date,
188 to_char(fnd_date.canonical_to_date(aei_information4) , 'YYYY.MM.DD') p_marine_submit_date
189 from per_assignment_extra_info
190 where assignment_id = p_assignment_id
191 and information_type = 'KR_YEA_TAX_REDUCTION_MARINE'
192 and p_year between to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY')
193 and to_char(fnd_date.canonical_to_date(aei_information4), 'YYYY');
194
195 ------------------------Traditional Market-------------------
196 cursor per_asg_extra_info_tm_csr(p_assignment_id number, p_year number) is
197 select aei_information3 tm_emp_crd_exp
198 ,aei_information4 tm_dep_crd_exp
199 ,aei_information5 tm_emp_cash_exp
200 ,aei_information6 tm_dep_cash_exp
201 ,aei_information7 tm_emp_dir_exp
202 ,aei_information8 tm_dep_dir_exp
203 ,aei_information10 inv_fin_2011
204 ,aei_information11 inv_fin_2012_ordinary
205 ,aei_information12 inv_fin_2012_venture
206 from per_assignment_extra_info
207 where ASSIGNMENT_ID = p_assignment_id
208 and information_type = 'KR_YEA_TAX_EXEM_INFO2'
209 and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
210 = to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
211 ------------------------- Prev Employer Cursor --------------------------
212 cursor csr_prev_employer_info(p_assignment_id number, p_year number) is
213 select sum(nvl(aei_information10,0)) p_total_hi_prem,
214 sum(nvl(aei_information11,0)) p_total_ei_prem,
215 sum(nvl(aei_information12,0)) p_total_np_prem,
216 sum(nvl(aei_information16,0)) p_total_pen_prem, /* Bug 6655323 */
217 sum(nvl(aei_information19,0)) p_total_sep_pension, /* Bug 7508706 */
218 sum(nvl(aei_information18,0)) p_total_ltci_prem /* Bug 7644535 */
219 from per_assignment_extra_info
220 where assignment_id = p_assignment_id
221 and information_type = 'KR_YEA_PREV_ER_INFO'
222 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
223
224 ------------------------- Dependent Expense Information ------------------
225 -- Bug 5726158
226 cursor csr_dpnt_expense_info(p_assignment_id number, p_effective_date date) is
227 select sum(nvl(cei_information1,0) + nvl(cei_information2,0)) l_dpnt_pers_ins,
228 sum(nvl(cei_information10,0) + nvl(cei_information11,0)) l_dpnt_dis_ins,
229 sum(nvl(cei_information7,0) + nvl(cei_information8,0)) l_dpnt_cards_exp,
230 sum(nvl(cei_information16,0) + nvl(cei_information17,0)) l_dpnt_direct_exp,
231 sum(nvl(cei_information9,0)) l_dpnt_cash_exp,
232 sum(nvl(cei_information18,0) + nvl(cei_information19,0)) l_dpnt_tm_card_exp,
233 sum(nvl(cei_information20,0)) l_dpnt_tm_cash_exp,
234 sum(nvl(cei_information21,0) + nvl(cei_information22,0)) l_dpnt_tm_direct_exp,
235 sum(nvl(cei_information24,0)) l_dpnt_tuition_exp
236 from pay_kr_cont_details_v pkc,
237 per_contact_extra_info_f cei -- Bug 5879106
238 where assignment_id = p_assignment_id
239 -- Bug 5879106
240 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
241 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
242 and to_char(cei.effective_start_date(+), 'YYYY') = to_char(p_effective_date, 'YYYY')
243 --
244 and p_effective_date between emp_start_date and emp_end_date
245 and p_effective_date between cont_start_date and cont_end_date
246 and p_effective_date between nvl(ADDRESS_START_DATE,p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
247 and p_effective_date between nvl(pkc.date_start, p_effective_date)
248 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) )
249 and pay_kr_ff_functions_pkg.is_exempted_dependent( pkc.contact_type,
250 pkc.cont_information11, -- Bug 7661820
251 pkc.national_identifier,
252 pkc.cont_information2,
253 pkc.cont_information3,
254 pkc.cont_information4,
255 pkc.cont_information7,
256 pkc.cont_information8,
257 p_effective_date,
258 pkc.cont_information10,
259 pkc.cont_information12,
260 pkc.cont_information13,
261 pkc.cont_information14,
262 cei.contact_extra_info_id
263 ) = 'Y';
264
265 -------------------Element Input Values------------------------------
266
267 cursor csr_ee (p_element_type_id number,p_assignment_id number,p_year number) is
268 select
269 peev.input_value_id,
270 peev.screen_entry_value
271 from pay_element_entry_values_f peev,
272 pay_element_entries_f pee,
273 pay_element_links_f pel
274 where pel.element_type_id = p_element_type_id
275 and p_year
276 between to_number(to_char(pel.effective_start_date,'YYYY'))
277 and to_number(to_char(pel.effective_end_date,'YYYY'))
278 and pee.element_link_id = pel.element_link_id
279 and pee.assignment_id = p_assignment_id
280 and nvl(pee.entry_type, 'E') = 'E'
281 and p_year between
282 to_number(to_char(pee.effective_start_date,'YYYY'))
283 and to_number(to_char(pee.effective_end_date,'YYYY'))
284 and peev.element_entry_id = pee.element_entry_id
285 and peev.effective_start_date = pee.effective_start_date
286 and peev.effective_end_date = pee.effective_end_date
287 order by peev.input_value_id;
288 ---------------------------------------------------------------------------------
289 cursor csr_get_join_leave_info (p_assignment_id number, p_effective_date date) is
290 Select decode(sign(to_number(to_char(pds.date_start,'YYYY')) -
291 (to_number(to_char(p_effective_date,'YYYY'))-1)),1,'X',0,'Y','N') Joined_last_year,
292 decode(sign(to_number(to_char(nvl(pds.actual_termination_date,fnd_date.canonical_to_date('4712/12/31')),'YYYY')) -
293 to_number(to_char(p_effective_date,'YYYY'))),1,'N',0,'Y','X') Leaving_cur_year,
294 asg.business_group_id bus_grp_id
295 from per_periods_of_service pds,
296 per_assignments_f asg
297 where asg.assignment_id = p_assignment_id
298 and p_effective_date between asg.effective_start_date and asg.effective_end_date
299 and pds.period_of_service_id = asg.period_of_service_id;
300 -----------------------------------------------------------------------
301 --
302 cursor csr_get_smb_eligibility_flag(p_business_group_id in number) is
303 select
304 nvl(hoi.org_information1,'N')
305 from hr_all_organization_units hou,
306 hr_organization_information hoi
307 where hoi.organization_id = hou.organization_id
308 and hou.business_group_id = p_business_group_id
309 and hoi.org_information_context = 'KR_YEA_ER_SMB_ELIGIBILITY_INFO';
310 -- End of Bug 9079478
311 -- Bug 10184055
312 ------------------------------------------------------------------------------------
313 cursor csr_house_holder(p_assignment_id number,p_effective_date date) is
314 select
315 nvl(pei.pei_information3,'N') house_holder_flag
316 from per_people_f per,
317 per_assignments_f asg,
318 per_people_extra_info pei
319 where asg.assignment_id = p_assignment_id
320 and p_effective_date
321 between asg.effective_start_date and asg.effective_end_date
322 and per.person_id = asg.person_id
323 and p_effective_date
324 between per.effective_start_date and per.effective_end_date
325 and per.person_id = pei.person_id(+)
326 and pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';
327 ------------------------------------------------------------------------------------
328 Cursor csr_fixed_tax_rate(p_assignment_id number)
329 IS
330 Select aei_information1
331 From per_assignment_extra_info
332 Where assignment_id = p_assignment_id
333 And information_type = 'KR_YEA_FOREIGN_WORKER_TAX';
334 ------------------------------------------------------------------------------------
335 cursor csr_hou_rent(p_assignment_id number, p_year number) is
336 select sum(nvl(to_number(aei_information6), 0))
337 from per_assignment_extra_info
338 where assignment_id = p_assignment_id
339 and information_type = 'KR_YEA_HOU_RENT_DETAILS'
340 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
341 ------------------------------------------------------------------------------------
342 cursor csr_housing_rent_details(p_assignment_id number, p_year number) is
343 select
344 aei_information2 type,
345 to_number(aei_information5) cont_amount
346 from per_assignment_extra_info
347 where assignment_id = p_assignment_id
348 and information_type = 'KR_YEA_HOU_SAVING_DETAILS'
349 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
350 ------------------------------------------------------------------------------------
351 cursor csr_sep_pen_details(p_assignment_id number, p_year number) is
352 select rownum,
353 aei_information6 priority,
354 aei_information2 type,
355 aei_information3 fin_institute,
356 aei_information4 acc_number,
357 to_number(aei_information5) cont_amount
358 from per_assignment_extra_info
359 where assignment_id = p_assignment_id
360 and information_type = 'KR_YEA_SEP_PEN_DETAILS'
361 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
362 order by to_number(aei_information6);
363 -----------------------------------------------------------------------------------
364 cursor csr_pen_sav_details(p_assignment_id number, p_year number)is
365 select
366 rownum,
367 aei_information6 priority,
368 aei_information2 type,
369 aei_information3 fin_institute,
370 aei_information4 acc_number,
371 to_number(aei_information5) cont_amount
372 from per_assignment_extra_info
373 where assignment_id = p_assignment_id
374 and information_type = 'KR_YEA_PEN_SAVING_DETAILS'
375 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
376 order by to_number(aei_information6);
377 -----------------------------------------------------------------------------------
378 cursor csr_hou_sav_details(p_assignment_id number, p_year number) is
379 select
380 rownum,
381 aei_information6 priority,
382 aei_information2 type,
383 aei_information3 fin_institute,
384 aei_information4 acc_number,
385 to_number(aei_information5) cont_amount
386 from per_assignment_extra_info
387 where assignment_id = p_assignment_id
388 and information_type = 'KR_YEA_HOU_SAVING_DETAILS'
389 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
390 order by to_number(aei_information6);
391 -----------------------------------------------------------------------------------
392 cursor csr_lt_stck_sav_details(p_assignment_id number, p_year number) is
393 select
394 rownum,
395 aei_information6 priority,
396 aei_information2 type,
397 aei_information3 fin_institute,
398 aei_information4 acc_number,
399 to_number(aei_information5) cont_amount
400 from per_assignment_extra_info
401 where assignment_id = p_assignment_id
402 and information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS'
403 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
404 order by to_number(aei_information6);
405 ------------------------------------------------------------------------------------
406 cursor csr_wkpd (p_assignment_id number,p_year number) is
407 select
408 per.person_id, -- bug 6012258
409 pds.date_start,
410 pds.actual_termination_date
411 from per_people_f per,
412 per_periods_of_service pds,
413 per_assignments_f asg
414 where
415 asg.assignment_id = p_assignment_id
416 and p_year between
417 to_number(to_char(asg.effective_start_date,'YYYY'))
418 and to_number(to_char(asg.effective_end_date,'YYYY'))
419 and pds.period_of_service_id = asg.period_of_service_id
420 and per.person_id = pds.person_id
421 and p_year between
422 to_number(to_char(per.effective_start_date,'YYYY'))
423 and to_number(to_char(per.effective_end_date,'YYYY'));
424 l_rec csr_wkpd%ROWTYPE;
425 ---------------------- Function to get the YTD balance -----------------------
426
427 function get_balance_value( p_assignment_id number,
428 p_year number,
429 p_ytd_balance_name varchar2) return number
430 is
431
432 cursor csr_defined_bal_id is
433 select pdb.defined_balance_id
434 from pay_balance_types pbt,
435 pay_balance_dimensions dim,
436 pay_defined_balances pdb
437 where pbt.legislation_code = 'KR'
438 and pbt.balance_name = p_ytd_balance_name
439 and dim.legislation_code = 'KR'
440 and dim.dimension_name = '_ASG_YTD'
441 and pdb.legislation_code = 'KR'
442 and pdb.BALANCE_DIMENSION_ID = dim.BALANCE_DIMENSION_ID
443 and pdb.BALANCE_TYPE_ID = pbt.balance_type_id;
444
445 l_dim_id number;
446 l_year_end_date date;
447 l_bal_value number;
448
449 begin
450 l_dim_id := null;
451 l_bal_value := null;
452 l_year_end_date := to_date('3112'||to_char(p_year),'ddmmrrrr');
453
454 open csr_defined_bal_id;
455 fetch csr_defined_bal_id into l_dim_id;
456 close csr_defined_bal_id;
457 begin
458 -- bug 9079478
459 l_bal_value := pay_balance_pkg.get_value(l_dim_id,
460 p_assignment_id,
461 l_year_end_date);
462 exception
463 when others then
464 l_bal_value := 0;
465 end;
466
467 return l_bal_value;
468 end get_balance_value;
469
470 -- Bug 9079478
471 ----------------------------------------------------------------------
472 function element(p_element_name in varchar2) return t_element
473
474 is
475 l_element t_element;
476 begin
477 select
478 element_type_id
479 into l_element.element_type_id
480 from pay_element_types_f
481 where element_name = p_element_name
482 and legislation_code = 'KR'
483 and business_group_id is null
484 group by element_type_id;
485 --
486 select
487 input_value_id,
488 min(name)
489 bulk collect into
490 l_element.input_value_id_tbl,
491 l_element.input_value_name_tbl
492 from pay_input_values_f
493 where element_type_id = l_element.element_type_id
494 and legislation_code = 'KR'
495 and business_group_id is null
496 group by input_value_id;
497 --
498 return l_element;
499 end element;
500 ----------------------------------------------------------------------
501
502 --------------------------------------------------------------------------------
503 procedure data(
504 p_assignment_id in number,
505 p_year in number,
506 ----------------Education Exp--------------------
507 p_edu_p_count out nocopy number,
508 p_edu_h_count out nocopy number,
509 p_edu_u_count out nocopy number,
510 p_edu_d_count out nocopy number,
511 p_edu_exp_p out nocopy number,
512 p_edu_exp_h out nocopy number,
513 p_edu_exp_u out nocopy number,
514 p_edu_exp_d out nocopy number,
515 p_edu_exp_total out nocopy number,
516 --------------- Housing Saving Total--------------
517 p_hou_exp out nocopy number,
518 p_resident_type out nocopy varchar2, -- Bug 9079478
519 ---------------Working Period---------------------
520 p_emp_start_date out nocopy date,
521 p_period_end_date out nocopy date,
522 --------------- Special Exems --------------------
523 p_pers_ins_prem out nocopy number,
524 p_dis_ins_prem out nocopy number,
525 p_gen_med_exp out nocopy number,
526 p_emp_aged_dis_med_exp out nocopy number,
527 p_med_exp_card out nocopy number, -- Bug 4336742
528 p_prv_hou_loan_repay out nocopy number, -- Bug 13080235
529 p_hou_loan_repay out nocopy number,
530 p_lt_hou_int_repay out nocopy number,
531 p_lt_1500_won_limit out nocopy number, -- Bug 9079478
532 p_donations out nocopy number,
533 p_emp_educ out nocopy number,
534 p_sp_prem_hi_sub out nocopy number,
535 p_sp_prem_ltci_sub out nocopy number, -- Bug 7644535
536 p_sp_prem_ei_sub out nocopy number,
537 p_100p_donation out nocopy number,
538 p_50p_donation out nocopy number,
539 p_30p_donation out nocopy number,
540 p_15p_donation out nocopy number, -- Bug 7508706
541 p_10p_donation out nocopy number,
542 p_political_donation out nocopy number,
543 p_political_100p out nocopy number, -- Bug 9079478
544 p_marr_reloc_funr_exem out nocopy number,
545 p_lt_hou_int_repay_gt_15 out nocopy number,
546 --------------- Other Exems --------------------
547 p_pers_pen_prem out nocopy number,
548 p_pers_pen_sav out nocopy number,
549 p_inv_part_fin2 out nocopy number,
550 p_inv_part_fin3 out nocopy number, -- Bug 9079478
551 p_emp_crd_exp out nocopy number,
552 p_dep_crd_exp out nocopy number,
553 p_emp_stk_opt out nocopy number,
554 p_tuition_giro out nocopy number,
555 p_cash_receipt out nocopy number, -- Bug 4336742
556 p_other_exem_np_prem out nocopy number,
557 p_other_exem_pen_prem out nocopy number, -- Bug 6655323
558 p_small_bus_install out nocopy number, -- Bug 7508706
559 p_company_related_exp out nocopy number, -- Bug 7615517
560 p_long_term_stck_fund_1yr out nocopy number, -- Bug 7615517
561 p_long_term_stck_fund_2yr out nocopy number, -- Bug 7615517
562 p_long_term_stck_fund_3yr out nocopy number, -- Bug 7615517
563 --------------- Tax Breaks -----------------------
564 p_hou_loan_int_repay_break out nocopy number,
565 p_lt_stk_sav1 out nocopy number,
566 p_lt_stk_sav2 out nocopy number,
567 --------------- FW Info --------------------------
568 p_emp_fw_exp out nocopy number,
569 --------------- Ovs Tax Break --------------------
570 p_ovs_tax_paid_fc out nocopy number,
571 p_ovs_tax_paid_lc out nocopy number,
572 p_ovs_country out nocopy varchar2,
573 p_ovs_paid_date out nocopy varchar2,
574 p_ovs_submit_date out nocopy varchar2,
575 p_ovs_location out nocopy varchar2,
576 p_ovs_period out nocopy varchar2,
577 p_ovs_title out nocopy varchar2,
578 --------------- FW Tax Break ---------------------
579 p_immigration_purpose out nocopy varchar2,
580 p_fw_contract_date out nocopy varchar2,
581 p_fw_expiry_date out nocopy varchar2,
582 p_fw_application_date out nocopy varchar2,
583 p_fw_submit_date out nocopy varchar2,
584 -----------------TAX Reductions-------------------
585 p_smb_start_date out nocopy varchar2,
586 p_smb_end_date out nocopy varchar2,
587 p_teach_application_date out nocopy varchar2,
588 p_teach_submit_date out nocopy varchar2,
589 p_marine_confirm_date out nocopy varchar2,
590 p_marine_submit_date out nocopy varchar2,
591 p_tradition_market out nocopy number,
592 p_lt_fixed_interest out nocopy number,
593 p_lt_others out nocopy number,
594 p_inv_fin_2011 out nocopy number,
595 p_inv_fin_2012_ordinary out nocopy number,
596 p_inv_fin_2012_venture out nocopy number,
597 --------------- Prev Employer Info ---------------
598 p_total_hi_prem out nocopy number,
599 p_total_ei_prem out nocopy number,
600 p_total_np_prem out nocopy number,
601 p_total_pen_prem out nocopy number, /* Bug 6655323 */
602 p_total_sep_pension out nocopy number, /* Bug 7508706 */
603 p_total_ltci_prem out nocopy number, /* Bug 7644535 */
604 --------------- Balance Values -------------------
605 p_np_prem_main out nocopy number,
606 p_pen_prem_main out nocopy number, /* Bug 6655323 */
607 p_ei_prem_main out nocopy number,
608 p_hi_prem_main out nocopy number,
609 p_ltci_prem_main out nocopy number, /* Bug 7644535 */
610 p_corp_pension out nocopy number, --4764823
611 p_emp_ins_included out nocopy varchar2,
612 p_emp_med_included out nocopy varchar2,
613 p_emp_edu_included out nocopy varchar2,
614 p_emp_card_included out nocopy varchar2,
615 p_ovs_earn out nocopy number, /* Bug 9079478 */
616 p_smb_income_exem out nocopy number,
617 -------------- Bug 10184055 ---------------------
618 p_tax_law_don out nocopy number,
619 p_inv_part_fin_tot out nocopy number,
620 p_sep_pen_Details_count out nocopy number,
621 p_pen_sav_Details_count out nocopy number,
622 p_hou_Sav_details_count out nocopy number,
623 p_lt_stck_details_count out nocopy number,
624 p_tot_pen_saving_details_cnt out nocopy number,
625 p_house_holder_flag out nocopy varchar2,
626 p_nationality_type out nocopy varchar2,
627 p_tax_reduction_period out nocopy varchar2,
628 p_housing_rent out nocopy number,
629 p_hou_sub_saving out nocopy number,
630 p_emp_hou_pro_saving out nocopy number,
631 p_lt_hou_pro_saving out nocopy number,
632 p_total_hou_saving out nocopy number,
633 p_emp_crd_drt_exp out nocopy number,
634 p_dep_crd_drt_exp out nocopy number,
635 p_sep_pen_priority1 out nocopy number,
636 p_sep_pen_priority2 out nocopy number,
637 p_pen_sav_priority1 out nocopy number,
638 p_pen_sav_priority2 out nocopy number,
639 p_hou_sav_priority1 out nocopy number,
640 p_hou_sav_priority2 out nocopy number,
641 p_hou_sav_priority3 out nocopy number,
642 p_lt_stck_sav_priority1 out nocopy number,
643 p_lt_stck_sav_priority2 out nocopy number,
644 p_lt_stck_sav_priority3 out nocopy number,
645 p_sep_pen_type1 out nocopy varchar2,
646 p_sep_pen_fin_inst1 out nocopy varchar2,
647 p_sep_pen_acc_number1 out nocopy varchar2,
648 p_sep_pen_cont_amt1 out nocopy number,
649 p_sep_pen_type2 out nocopy varchar2,
650 p_sep_pen_fin_inst2 out nocopy varchar2,
651 p_sep_pen_acc_number2 out nocopy varchar2,
652 p_sep_pen_cont_amt2 out nocopy number,
653 p_pen_sav_type1 out nocopy varchar2,
654 p_pen_sav_fin_inst1 out nocopy varchar2,
655 p_pen_sav_acc_number1 out nocopy varchar2,
656 p_pen_sav_cont_amt1 out nocopy number,
657 p_pen_sav_type2 out nocopy varchar2,
658 p_pen_sav_fin_inst2 out nocopy varchar2,
659 p_pen_sav_acc_number2 out nocopy varchar2,
660 p_pen_sav_cont_amt2 out nocopy number,
661 p_hou_sav_type1 out nocopy varchar2,
662 p_hou_sav_fin_inst1 out nocopy varchar2,
663 p_hou_sav_acc_number1 out nocopy varchar2,
664 p_hou_sav_cont_amt1 out nocopy number,
665 p_hou_sav_type2 out nocopy varchar2,
666 p_hou_sav_fin_inst2 out nocopy varchar2,
667 p_hou_sav_acc_number2 out nocopy varchar2,
668 p_hou_sav_cont_amt2 out nocopy number,
669 p_hou_sav_type3 out nocopy varchar2,
670 p_hou_sav_fin_inst3 out nocopy varchar2,
671 p_hou_sav_acc_number3 out nocopy varchar2,
672 p_hou_sav_cont_amt3 out nocopy number,
673 p_lt_stck_sav_type1 out nocopy varchar2,
674 p_lt_stck_sav_fin_inst1 out nocopy varchar2,
675 p_lt_stck_sav_acc_number1 out nocopy varchar2,
676 p_lt_stck_sav_cont_amt1 out nocopy number,
677 p_lt_stck_sav_type2 out nocopy varchar2,
678 p_lt_stck_sav_fin_inst2 out nocopy varchar2,
679 p_lt_stck_sav_acc_number2 out nocopy varchar2,
680 p_lt_stck_sav_cont_amt2 out nocopy number,
681 p_lt_stck_sav_type3 out nocopy varchar2,
682 p_lt_stck_sav_fin_inst3 out nocopy varchar2,
683 p_lt_stck_sav_acc_number3 out nocopy varchar2,
684 p_lt_stck_sav_cont_amt3 out nocopy number
685 )
686 is
687 l_edu_p_count number;
688 l_edu_h_count number;
689 l_edu_u_count number;
690 l_edu_d_count number;
691 l_edu_type varchar2(5);
692 l_edu_exp number;
693 l_emp_occ_trg_exp number ; -- Bug 4336742
694 l_emp_crd_drt_exp number;
695 l_dep_crd_drt_exp number; -- 4046680
696 l_marriage_count number;
697 l_reloc_count number;
698 l_funeral_count number;
699 l_med_exp_disabled number;
700 l_med_exp_aged number;
701 l_med_exp_emp number;
702 l_political_since_040312 number;
703 l_political_before_040312 number;
704 l_dummy number;
705 l_corp_pension number;
706 l_corp_pension_bal number;
707 l_promotional_fund_don number;
708 l_religious_don number;
709 l_other_don number;
710 l_15p_donation number; -- Bug 7508706
711 -- Bug 5726158
712 l_dpnt_pers_ins number;
713 l_dpnt_dis_ins number;
714 l_dpnt_cards_exp number;
715 l_dpnt_cash_exp number;
716 l_public_legal_don number; -- Bug 7508706
717 l_tax_law_don number; -- Bug 7508706
718 -- Bug 9079478
719 l_non_resident_flag varchar2(10);
720 l_input_value_id_tbl t_number_tbl;
721 l_screen_entry_value_tbl t_varchar2_tbl;
722 l_ovs_earn_bal number;
723 l_period_start_date date;
724 l_er_smb_prev_earn number;
725 l_er_smb_cur_earn number;
726 p_smb_eligibility_flag varchar2(1);
727 p_cur_smb_days_worked number;
728 p_prev_smb_days_worked number;
729 p_emp_join_prev_year varchar2(1);
730 p_emp_leave_cur_year varchar2(1);
731 l_business_group_id number;
732 -- Bug 10184055
733 l_foreign_residency_flag varchar2(1);
734 l_fixed_tax_rate varchar2(1);
735 l_dpnt_direct_exp number;
736 l_prv_hou_loan_repay number;
737 l_tm_emp_crd_exp number;
738 l_tm_dep_crd_exp number;
739 l_tm_emp_cash_exp number;
740 l_tm_dep_cash_exp number;
741 l_tm_emp_dir_exp number;
742 l_tm_dep_dir_exp number;
743 l_dpnt_tm_card_exp number;
744 l_dpnt_tm_cash_exp number;
745 l_dpnt_tm_direct_exp number;
746 l_inv_fin_2011 number; -- Bug 15911822
747 l_inv_fin_2012_ordinary number; -- Bug 15911822
748 l_inv_fin_2012_venture number; -- Bug 15911822
749 l_dpnt_tuition_exp number; -- Bug 15911822
750 begin
751 ----------------------------- Education Expenses ---------------------------
752 l_edu_p_count := 0;
753 l_edu_h_count := 0;
754 l_edu_u_count := 0;
755 l_edu_d_count := 0;
756 p_edu_exp_p := null;
757 p_edu_exp_h := null;
758 p_edu_exp_u := null;
759 p_edu_exp_d := null;
760 p_hou_exp := null;
761
762 if (p_year < 2009) then
763 open per_asg_extra_info_educ_csr(p_assignment_id, p_year);
764
765 loop
766
767 fetch per_asg_extra_info_educ_csr
768 into l_edu_type, l_edu_exp;
769
770 exit when per_asg_extra_info_educ_csr%NOTFOUND;
771
772 if l_edu_type = 'P' then
773 p_edu_exp_p := nvl(p_edu_exp_p,0) + l_edu_exp;
774 l_edu_p_count := l_edu_p_count + 1;
775 elsif l_edu_type = 'H' then
776 p_edu_exp_h := nvl(p_edu_exp_h,0) + l_edu_exp;
777 l_edu_h_count := l_edu_h_count + 1;
778 elsif l_edu_type = 'U' then
779 p_edu_exp_u := nvl(p_edu_exp_u,0) + l_edu_exp;
780 l_edu_u_count := l_edu_u_count + 1;
781 elsif l_edu_type = 'D' then
782 p_edu_exp_d := nvl(p_edu_exp_d,0) + l_edu_exp;
783 l_edu_d_count := l_edu_d_count + 1;
784 end if;
785
786 end loop;
787 if per_asg_extra_info_educ_csr%ISOPEN then
788 close per_asg_extra_info_educ_csr;
789 end if;
790 else
791 -- Bug 9341235
792 open per_asg_extra_info_educ_2009(p_assignment_id, p_year);
793 loop
794 fetch per_asg_extra_info_educ_2009
795 into l_edu_type, l_edu_exp;
796
797 exit when per_asg_extra_info_educ_2009%NOTFOUND;
798
799 if l_edu_type = 'P' then
800 p_edu_exp_p := nvl(p_edu_exp_p,0) + l_edu_exp;
801 l_edu_p_count := l_edu_p_count + 1;
802 elsif l_edu_type = 'H' then
803 p_edu_exp_h := nvl(p_edu_exp_h,0) + l_edu_exp;
804 l_edu_h_count := l_edu_h_count + 1;
805 elsif l_edu_type = 'U' then
806 p_edu_exp_u := nvl(p_edu_exp_u,0) + l_edu_exp;
807 l_edu_u_count := l_edu_u_count + 1;
808 elsif l_edu_type = 'D' then
809 p_edu_exp_d := nvl(p_edu_exp_d,0) + l_edu_exp;
810 l_edu_d_count := l_edu_d_count + 1;
811 end if;
812
813 end loop;
814
815 if per_asg_extra_info_educ_2009%ISOPEN then
816 close per_asg_extra_info_educ_2009;
817 end if;
818 end if;
819 -- End of Bug 9341235
820
821 p_edu_p_count := l_edu_p_count;
822 p_edu_h_count := l_edu_h_count;
823 p_edu_u_count := l_edu_u_count;
824 p_edu_d_count := l_edu_d_count;
825 ----------------------------House holder flag--------------------------------
826 -- Bug 10184055
827 open csr_house_holder(p_assignment_id,to_date('3112'||to_char(p_year), 'DDMMYYYY'));
828 fetch csr_house_holder into p_house_holder_flag;
829 close csr_house_holder;
830 ---------------------------- Special Exems -----------------------------------
831 open per_asg_extra_info_sp_csr(p_assignment_id, p_year);
832 fetch per_asg_extra_info_sp_csr
833 into p_pers_ins_prem
834 ,p_dis_ins_prem
835 ,p_gen_med_exp
836 ,l_med_exp_disabled
837 ,l_med_exp_aged
838 ,l_med_exp_emp
839 ,p_hou_loan_repay
840 ,p_lt_hou_int_repay
841 ,p_donations
842 ,p_emp_educ
843 ,p_sp_prem_hi_sub
844 ,p_sp_prem_ei_sub
845 ,p_100p_donation
846 ,l_political_since_040312
847 ,l_political_before_040312
848 ,l_tax_law_don -- Bug 7508706
849 ,p_30p_donation
850 ,l_15p_donation -- Bug 7508706
851 ,l_marriage_count
852 ,l_reloc_count
853 ,l_funeral_count
854 ,p_lt_hou_int_repay_gt_15;
855
856 close per_asg_extra_info_sp_csr;
857
858 -- Bug 5726158
859 open csr_dpnt_expense_info(p_assignment_id, to_date('3112'||to_char(p_year), 'DDMMYYYY'));
860 fetch csr_dpnt_expense_info into l_dpnt_pers_ins,
861 l_dpnt_dis_ins,
862 l_dpnt_cards_exp,
863 l_dpnt_direct_exp,
864 l_dpnt_cash_exp,
865 l_dpnt_tm_card_exp,
866 l_dpnt_tm_cash_exp,
867 l_dpnt_tm_direct_exp,
868 l_dpnt_tuition_exp;
869 close csr_dpnt_expense_info;
870 p_pers_ins_prem := nvl(p_pers_ins_prem,0) + nvl(l_dpnt_pers_ins,0);
871 p_dis_ins_prem := nvl(p_dis_ins_prem,0) + nvl(l_dpnt_dis_ins,0);
872
873 -- End of Bug 5726158
874
875
876 -- Bug 4336742
877 open per_asg_extra_info_sp2_csr(p_assignment_id, p_year) ;
878 fetch per_asg_extra_info_sp2_csr into l_emp_occ_trg_exp,
879 p_med_exp_card,
880 l_promotional_fund_don,
881 l_religious_don,
882 l_other_don,
883 l_public_legal_don, -- Bug 7508706
884 p_sp_prem_ltci_sub, -- Bug 7644535
885 p_lt_1500_won_limit; -- Bug 9079478
886 close per_asg_extra_info_sp2_csr ;
887
888 -- Bug 10184055
889 open per_asg_extra_info_sp3_csr(p_assignment_id, p_year) ;
890 fetch per_asg_extra_info_sp3_csr into p_prv_hou_loan_repay,p_lt_fixed_interest,p_lt_others; -- BUG 13080235
891 close per_asg_extra_info_sp3_csr ;
892
893 -- BUG 13080235
894 -- p_hou_loan_repay := nvl(p_hou_loan_repay,0) + nvl(l_prv_hou_loan_repay,0);
895
896 --
897 if p_emp_educ is null and l_emp_occ_trg_exp is null then
898 p_emp_educ := null ;
899 else
900 p_emp_educ := nvl(p_emp_educ, 0) + nvl(l_emp_occ_trg_exp, 0) ;
901 end if ;
902
903 -- End of 4336742
904
905 p_edu_exp_total := nvl(p_emp_educ,0) +
906 nvl(p_edu_exp_p,0) +
907 nvl(p_edu_exp_h,0) +
908 nvl(p_edu_exp_u,0) +
909 nvl(p_edu_exp_d,0);
910
911 -- sum should be null if all values are null
912 if (p_emp_educ is null and
913 p_edu_exp_p is null and
914 p_edu_exp_h is null and
915 p_edu_exp_u is null and
916 p_edu_exp_u is null) then
917 p_edu_exp_total := null;
918 end if;
919
920 p_emp_aged_dis_med_exp := nvl(l_med_exp_disabled,0 )+
921 nvl(l_med_exp_aged ,0) +
922 nvl(l_med_exp_emp ,0) ;
923
924
925 -- sum should be null if all values are null
926 if (l_med_exp_disabled is null and
927 l_med_exp_aged is null and
928 l_med_exp_emp is null) then
929 p_emp_aged_dis_med_exp := null;
930 end if;
931
932
933
934 p_marr_reloc_funr_exem := null;
935
936
937 p_marr_reloc_funr_exem := 1000000 * ( nvl(l_marriage_count,0) +
938 nvl(l_reloc_count ,0) +
939 nvl(l_funeral_count ,0) );
940 -- sum should be null if all values are null
941 if (l_marriage_count is null and
942 l_reloc_count is null and
943 l_funeral_count is null) then
944 p_marr_reloc_funr_exem := null;
945 end if;
946
947 -- calculate political donation
948 p_political_donation := Least(100000, nvl(l_political_since_040312,0) );
949 p_political_100p := nvl(l_political_before_040312,0); -- Bug 9079478
950
951 -- calculate 10 percent donations
952 p_10p_donation := l_religious_don;
953 p_15p_donation := l_15p_donation;
954
955 -- Bug 7508706
956 -- Bug 10184055
957 p_tax_law_don := nvl(l_tax_law_don,0);
958 p_50p_donation := l_public_legal_don;
959 --
960 if l_political_since_040312 is null then
961 p_political_donation := null;
962 end if;
963
964 ---------------------------- Other Exems -----------------------------------
965 open per_asg_extra_info_othr_csr(p_assignment_id, p_year);
966 fetch per_asg_extra_info_othr_csr
967 into p_pers_pen_prem
968 ,p_pers_pen_sav
969 ,p_inv_part_fin2
970 ,p_inv_part_fin3 -- Bug 9079478
971 ,p_emp_crd_exp
972 ,p_dep_crd_exp
973 ,p_emp_stk_opt
974 ,p_emp_crd_drt_exp
975 ,l_dep_crd_drt_exp
976 ,p_tuition_giro
977 ,p_cash_receipt -- Bug 4336742
978 ,p_other_exem_np_prem
979 ,p_other_exem_pen_prem -- Bug 6655323
980 ,l_corp_pension -- Bug 4764823
981 ,p_small_bus_install -- Bug 7508706
982 ,p_company_related_exp -- Bug 7615517
983 ,p_long_term_stck_fund_1yr -- Bug 7615517
984 ,p_long_term_stck_fund_2yr -- Bug 7615517
985 ,p_long_term_stck_fund_3yr -- Bug 7615517
986 ,p_cur_smb_days_worked -- Bug 9079478
987 ,p_prev_smb_days_worked; -- Bug 9079478
988
989 close per_asg_extra_info_othr_csr;
990 -- Bug 10184055
991 p_inv_part_fin_tot := nvl(p_inv_part_fin2,0) + nvl(p_inv_part_fin3,0);
992 p_dep_crd_exp := nvl(p_dep_crd_exp,0)+ nvl(l_dpnt_cards_exp,0);
993 p_dep_crd_drt_exp := nvl(l_dpnt_direct_exp,0);
994 p_cash_receipt := nvl(p_cash_receipt,0) + nvl(l_dpnt_cash_exp,0);
995 -- End of Bug 5726158
996
997 ---------------------------- Tax Breaks -----------------------------------
998 open per_asg_extra_info_break_csr(p_assignment_id, p_year);
999 fetch per_asg_extra_info_break_csr
1000 into p_hou_loan_int_repay_break
1001 ,p_lt_stk_sav1
1002 ,p_lt_stk_sav2;
1003
1004 close per_asg_extra_info_break_csr;
1005
1006 ---------------------------- FW Info --------------------------------------
1007 open per_asg_extra_info_fw_csr(p_assignment_id, p_year);
1008 fetch per_asg_extra_info_fw_csr into p_emp_fw_exp;
1009
1010 close per_asg_extra_info_fw_csr; ---#### no more used
1011
1012 ---------------------------- Ovs Tax Break ---------------------------------
1013 open per_asg_extra_info_ovs_csr(p_assignment_id, p_year);
1014 fetch per_asg_extra_info_ovs_csr
1015 into p_ovs_tax_paid_fc,
1016 p_ovs_tax_paid_lc,
1017 p_ovs_country,
1018 p_ovs_paid_date,
1019 p_ovs_submit_date,
1020 p_ovs_location,
1021 p_ovs_period,
1022 p_ovs_title;
1023
1024 close per_asg_extra_info_ovs_csr;
1025
1026 ---------------------------- FW Tax Break ---------------------------------
1027 open csr_fw_tax_break(p_assignment_id, p_year);
1028 fetch csr_fw_tax_break
1029 into p_immigration_purpose,
1030 p_fw_contract_date,
1031 p_fw_expiry_date,
1032 p_fw_application_date,
1033 p_fw_submit_date ;
1034
1035 close csr_fw_tax_break;
1036
1037 ---------------------------- SMB Tax Reductions ---------------------------------
1038 open csr_smb_tax_red(p_assignment_id, p_year);
1039 fetch csr_smb_tax_red
1040 into p_smb_start_date,
1041 p_smb_end_date;
1042 close csr_smb_tax_red;
1043
1044 ---------------------------- Teachers Tax Reductions ---------------------------------
1045 open csr_teach_tax_red(p_assignment_id, p_year);
1046 fetch csr_teach_tax_red
1047 into p_teach_application_date,
1048 p_teach_submit_date;
1049 close csr_teach_tax_red;
1050
1051 ---------------------------- Marine Tax Reductions ---------------------------------
1052 open csr_marine_tax_red(p_assignment_id, p_year);
1053 fetch csr_marine_tax_red
1054 into p_marine_confirm_date,
1055 p_marine_submit_date;
1056 close csr_marine_tax_red;
1057
1058 ---------------------Traditional Market-------------------------------
1059 open per_asg_extra_info_tm_csr(p_assignment_id, p_year);
1060 fetch per_asg_extra_info_tm_csr
1061 into l_tm_emp_crd_exp
1062 ,l_tm_dep_crd_exp
1063 ,l_tm_emp_cash_exp
1064 ,l_tm_dep_cash_exp
1065 ,l_tm_emp_dir_exp
1066 ,l_tm_dep_dir_exp
1067 ,l_inv_fin_2011
1068 ,l_inv_fin_2012_ordinary
1069 ,l_inv_fin_2012_venture ;
1070 close per_asg_extra_info_tm_csr;
1071 l_tm_dep_crd_exp := nvl(l_tm_dep_crd_exp,0) + nvl(l_dpnt_tm_card_exp,0);
1072 l_tm_dep_cash_exp := nvl(l_tm_dep_cash_exp,0) + nvl(l_dpnt_tm_cash_exp,0);
1073 l_tm_dep_dir_exp := nvl(l_tm_dep_dir_exp,0) + nvl(l_dpnt_tm_direct_exp,0);
1074 p_tradition_market := nvl(l_tm_emp_crd_exp,0) + nvl(l_tm_emp_cash_exp,0) + nvl(l_tm_emp_dir_exp,0)
1075 + nvl(l_tm_dep_crd_exp,0) + nvl(l_tm_dep_cash_exp,0) + nvl(l_tm_dep_dir_exp,0);
1076 p_inv_fin_2011 := nvl(l_inv_fin_2011,0);
1077 p_inv_fin_2012_ordinary := nvl(l_inv_fin_2012_ordinary,0);
1078 p_inv_fin_2012_venture := nvl(l_inv_fin_2012_venture,0);
1079 p_tuition_giro := nvl(p_tuition_giro,0) + nvl(l_dpnt_tuition_exp,0);
1080 ---------------------------- Prev Employer Info ---------------------------
1081 open csr_prev_employer_info(p_assignment_id, p_year);
1082 fetch csr_prev_employer_info
1083 into p_total_hi_prem,
1084 p_total_ei_prem,
1085 p_total_np_prem,
1086 p_total_pen_prem, /* Bug 6655323 */
1087 p_total_sep_pension, /* bug 7508706 */
1088 p_total_ltci_prem; /* Bug 7644535 */
1089
1090 close csr_prev_employer_info;
1091
1092 ---------------------------- Get YTD Balances ---------------------------
1093 -- Bug 4446381: Added nvl checks, so that even if no values for premium fields
1094 -- are entered in the YEA Information form, the balance values
1095 -- show up in the reclaim sheet
1096 --
1097 p_np_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'NP_PREM_EE'), 0)
1098 + nvl(p_other_exem_np_prem, 0) ;
1099 -- Sum is null if all components are null
1100 if get_balance_value(p_assignment_id, p_year,'NP_PREM_EE') is null
1101 and p_other_exem_np_prem is null
1102 then
1103 p_np_prem_main := null ;
1104 end if ;
1105 -- Bug 6655323
1106 p_pen_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'Pension Premium'), 0)
1107 + nvl(p_other_exem_pen_prem, 0);
1108 -- Sum is null if all components are null
1109 if get_balance_value(p_assignment_id, p_year,'Pension Premium') is null
1110 and p_other_exem_pen_prem is null
1111 then
1112 p_pen_prem_main := null ;
1113 end if ;
1114 --
1115 p_ei_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'EI_PREM'), 0)
1116 + nvl(p_sp_prem_ei_sub, 0) ;
1117 -- Sum is null if all components are null
1118 if get_balance_value(p_assignment_id, p_year,'EI_PREM') is null
1119 and p_sp_prem_ei_sub is null
1120 then
1121 p_ei_prem_main := null ;
1122 end if ;
1123 -- Bug 7644535
1124 p_ltci_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'LTCI_PREM_EE'), 0)
1125 + nvl(p_sp_prem_ltci_sub, 0) ;
1126 -- Sum is null if all components are null
1127 if get_balance_value(p_assignment_id, p_year,'LTCI_PREM_EE') is null
1128 and p_sp_prem_ltci_sub is null
1129 then
1130 p_ltci_prem_main := null ;
1131 end if ;
1132
1133 p_hi_prem_main := nvl(get_balance_value(p_assignment_id, p_year,'HI_PREM_EE'), 0)
1134 + nvl(p_sp_prem_hi_sub, 0) ;
1135 -- Sum is null if all components are null
1136 if get_balance_value(p_assignment_id, p_year,'HI_PREM_EE') is null
1137 and p_sp_prem_hi_sub is null
1138 then
1139 p_hi_prem_main := null ;
1140 end if ;
1141
1142 -- 4863731
1143 l_corp_pension_bal := get_balance_value(p_assignment_id, p_year,'CORPORATE_PENSION');
1144 p_corp_pension := nvl(l_corp_pension_bal,0) + nvl(l_corp_pension,0);
1145 if l_corp_pension_bal is null and
1146 l_corp_pension is null then
1147
1148 p_corp_pension := null;
1149
1150 end if;
1151 -- End of 4446381
1152 -- Bug 9079478
1153 l_ovs_earn_bal := get_balance_value(p_assignment_id, p_year,'Overseas Earnings');
1154 p_ovs_earn := nvl(l_ovs_earn_bal, 0);
1155 l_er_smb_prev_earn := nvl(get_balance_value(p_assignment_id, p_year-1,'EARN_FOR_ER_SMB_EXEM'),0);
1156 l_er_smb_cur_earn := nvl(get_balance_value(p_assignment_id, p_year,'EARN_FOR_ER_SMB_EXEM'),0);
1157
1158 ---------------------- Pension Saving Details --------------------------------
1159 for i in csr_sep_pen_details(p_assignment_id, p_year) loop
1160 if i.rownum=1 then
1161 p_sep_pen_priority1 := i.priority;
1162 p_sep_pen_type1 := hr_general.decode_lookup('KR_YEA_SEP_PEN_TYPES',i.type);
1163 p_sep_pen_fin_inst1 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',i.fin_institute);
1164 p_sep_pen_acc_number1 := i.acc_number;
1165 p_sep_pen_cont_amt1 := i.cont_amount;
1166 elsif i.rownum=2 then
1167 p_sep_pen_priority2 := i.priority;
1168 p_sep_pen_type2 := hr_general.decode_lookup('KR_YEA_SEP_PEN_TYPES',i.type);
1169 p_sep_pen_fin_inst2 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',i.fin_institute);
1170 p_sep_pen_acc_number2 := i.acc_number;
1171 p_sep_pen_cont_amt2 := i.cont_amount;
1172 elsif i.rownum > 2 then
1173 p_sep_pen_Details_count := nvl(p_sep_pen_Details_count,0) + 1;
1174
1175 end if;
1176 end loop;
1177
1178 for j in csr_pen_sav_details(p_assignment_id, p_year) loop
1179
1180 if j.rownum=1 then
1181
1182 p_pen_sav_priority1 := j.priority;
1183 p_pen_sav_type1 := hr_general.decode_lookup('KR_YEA_PEN_SAVING_TYPES',j.type);
1184 p_pen_sav_fin_inst1 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',j.fin_institute);
1185 p_pen_sav_acc_number1 := j.acc_number;
1186 p_pen_sav_cont_amt1 := j.cont_amount;
1187
1188 elsif j.rownum=2 then
1189
1190 p_pen_sav_priority2 := j.priority;
1191 p_pen_sav_type2 := hr_general.decode_lookup('KR_YEA_PEN_SAVING_TYPES',j.type);
1192 p_pen_sav_fin_inst2 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',j.fin_institute);
1193 p_pen_sav_acc_number2 := j.acc_number;
1194 p_pen_sav_cont_amt2 := j.cont_amount;
1195
1196 elsif j.rownum > 2 then
1197 p_pen_sav_Details_count := nvl(p_pen_sav_Details_count,0) + 1;
1198 end if;
1199 end loop;
1200
1201 for k in csr_hou_sav_details(p_assignment_id, p_year) loop
1202 if k.rownum=1 then
1203 p_hou_sav_priority1 := k.priority;
1204 p_hou_sav_type1 := hr_general.decode_lookup('KR_HOUSING_SAVING_TYPE',k.type);
1205 p_hou_sav_fin_inst1 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',k.fin_institute);
1206 p_hou_sav_acc_number1 := k.acc_number;
1207 p_hou_sav_cont_amt1 := k.cont_amount;
1208 elsif k.rownum=2 then
1209 p_hou_sav_priority2 := k.priority;
1210 p_hou_sav_type2 := hr_general.decode_lookup('KR_HOUSING_SAVING_TYPE',k.type);
1211 p_hou_sav_fin_inst2 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',k.fin_institute);
1212 p_hou_sav_acc_number2 := k.acc_number;
1213 p_hou_sav_cont_amt2 := k.cont_amount;
1214
1215 elsif k.rownum=3 then
1216 p_hou_sav_priority3 := k.priority;
1217 p_hou_sav_type3 := hr_general.decode_lookup('KR_HOUSING_SAVING_TYPE',k.type);
1218 p_hou_sav_fin_inst3 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',k.fin_institute);
1219 p_hou_sav_acc_number3 := k.acc_number;
1220 p_hou_sav_cont_amt3 := k.cont_amount;
1221
1222 elsif k.rownum >3 then
1223 p_hou_Sav_details_count := nvl(p_hou_Sav_details_count,0) + 1;
1224 end if;
1225 end loop;
1226
1227 for m in csr_lt_stck_sav_details(p_assignment_id, p_year) loop
1228 if m.rownum=1 then
1229 p_lt_stck_sav_priority1 := m.priority;
1230 p_lt_stck_sav_type1 := hr_general.decode_lookup('KR_YEA_LT_STOCK_SAVING_YEAR',m.type);
1231 p_lt_stck_sav_fin_inst1 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',m.fin_institute);
1232 p_lt_stck_sav_acc_number1 := m.acc_number;
1233 p_lt_stck_sav_cont_amt1 := m.cont_amount;
1234 elsif m.rownum=2 then
1235 p_lt_stck_sav_priority2 := m.priority;
1236 p_lt_stck_sav_type2 := hr_general.decode_lookup('KR_YEA_LT_STOCK_SAVING_YEAR',m.type);
1237 p_lt_stck_sav_fin_inst2 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',m.fin_institute);
1238 p_lt_stck_sav_acc_number2 := m.acc_number;
1239 p_lt_stck_sav_cont_amt2 := m.cont_amount;
1240
1241 elsif m.rownum=3 then
1242 p_lt_stck_sav_priority3 := m.priority;
1243 p_lt_stck_sav_type3 := hr_general.decode_lookup('KR_YEA_LT_STOCK_SAVING_YEAR',m.type);
1244 p_lt_stck_sav_fin_inst3 := hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',m.fin_institute);
1245 p_lt_stck_sav_acc_number3 := m.acc_number;
1246 p_lt_stck_sav_cont_amt3 := m.cont_amount;
1247
1248 elsif m.rownum >3 then
1249 p_lt_stck_details_count := nvl(p_lt_stck_details_count,0) +1 ;
1250 end if;
1251 end loop;
1252 p_tot_pen_saving_details_cnt := nvl(p_sep_pen_Details_count,0) + nvl(p_pen_sav_Details_count,0) + nvl(p_hou_Sav_details_count,0)
1253 + nvl(p_lt_stck_details_count,0);
1254 ------------------- Set Ins,Med,Edu,Card flags -------------------
1255 p_emp_ins_included := null;
1256 p_emp_med_included := null;
1257 p_emp_edu_included := null;
1258 p_emp_card_included := null;
1259
1260 -- ins included
1261 if p_pers_ins_prem > 0 or p_dis_ins_prem > 0 then
1262 p_emp_ins_included := 'O';
1263 end if;
1264
1265 -- med included
1266 if l_med_exp_emp > 0 then
1267 p_emp_med_included := 'O';
1268 end if;
1269
1270 -- edu included
1271 if p_emp_educ > 0 then
1272 p_emp_edu_included := 'O';
1273 end if;
1274
1275 -- card included
1276 if p_emp_crd_exp > 0 or p_tuition_giro > 0 or p_cash_receipt > 0 then
1277 p_emp_card_included := 'O';
1278 end if;
1279
1280 -- Bug 9079478
1281 g_tax := element('TAX');
1282 open csr_ee(g_tax.element_type_id,p_assignment_id, p_year);
1283 fetch csr_ee bulk collect into l_input_value_id_tbl, l_screen_entry_value_tbl;
1284 close csr_ee;
1285 l_non_resident_flag := nvl(l_screen_entry_value_tbl(1), 'N');
1286
1287 if l_non_resident_flag = 'Y' then
1288 p_resident_type := 'N';
1289 else
1290 p_resident_type := 'R';
1291 end if;
1292 -- Bug 10184055
1293 begin
1294 l_foreign_residency_flag := nvl(l_screen_entry_value_tbl(2), 'N');
1295 exception
1296 when no_data_found then
1297 l_foreign_residency_flag := 'N';
1298 end;
1299 if (pay_kr_ff_functions_pkg.ni_nationality(p_assignment_id, to_date('3112'||to_char(p_year), 'DDMMYYYY')) = 'F' or l_foreign_residency_flag = 'Y') then
1300 p_nationality_type := 'F';
1301 else
1302 p_nationality_type := 'K';
1303 end if;
1304 --
1305 -- Bug 10184055
1306 l_fixed_tax_rate := 'N';
1307 open csr_fixed_tax_rate(p_assignment_id);
1308 fetch csr_fixed_tax_rate into l_fixed_tax_rate;
1309 if csr_fixed_tax_rate%notfound then
1310 l_fixed_tax_rate := 'N';
1311 end if;
1312 close csr_fixed_tax_rate;
1313
1314 if p_nationality_type = 'F' and l_fixed_tax_rate = 'N' and p_immigration_purpose is not null then
1315 p_tax_reduction_period := p_fw_contract_date||'~'||p_fw_expiry_date;
1316 end if;
1317
1318 open csr_hou_rent(p_assignment_id, p_year);
1319 fetch csr_hou_rent into p_housing_rent;
1320 close csr_hou_rent;
1321
1322 for i in csr_housing_rent_details(p_assignment_id, p_year) loop
1323 if i.type = 'HST1' then
1324 p_hou_sub_saving := nvl(p_hou_sub_saving,0) + i.cont_amount;
1325 elsif i.type = 'HST3' then
1326 p_emp_hou_pro_saving := nvl(p_emp_hou_pro_saving,0) + i.cont_amount;
1327 elsif i.type = 'HST4' then
1328 p_lt_hou_pro_saving := nvl(p_lt_hou_pro_saving,0) + i.cont_amount;
1329 elsif i.type = 'HST5' then
1330 p_total_hou_saving := nvl(p_total_hou_saving,0) + i.cont_amount;
1331 end if;
1332 end loop;
1333 p_hou_exp := nvl(p_hou_sub_saving,0) + nvl(p_emp_hou_pro_saving,0) + nvl(p_lt_hou_pro_saving,0) + nvl(p_total_hou_saving,0);
1334 --
1335 open csr_wkpd (p_assignment_id, p_year);
1336 fetch csr_wkpd into l_rec;
1337 close csr_wkpd;
1338
1339 l_period_start_date := to_date('0101'||to_char(p_year),'ddmmrrrr');
1340 p_period_end_date := add_months(l_period_start_date, 12) - 1;
1341 l_period_start_date := greatest(l_period_start_date, l_rec.date_start);
1342 --
1343 p_emp_start_date := l_period_start_date;
1344 if l_rec.actual_termination_date < p_period_end_date then
1345 p_period_end_date := l_rec.actual_termination_date;
1346 end if;
1347
1348 open csr_get_join_leave_info(p_assignment_id, to_date('3112'||to_char(p_year), 'DDMMYYYY'));
1349 fetch csr_get_join_leave_info into p_emp_join_prev_year, p_emp_leave_cur_year,l_business_group_id;
1350 close csr_get_join_leave_info;
1351
1352 open csr_get_smb_eligibility_flag(l_business_group_id);
1353 fetch csr_get_smb_eligibility_flag into p_smb_eligibility_flag;
1354 if csr_get_smb_eligibility_flag%NOTFOUND then
1355 p_smb_eligibility_flag := 'N';
1356 end if;
1357 close csr_get_smb_eligibility_flag;
1358 p_smb_income_exem := 0;
1359 if (p_smb_eligibility_flag = 'Y') then
1360
1361 if (nvl(p_cur_smb_days_worked,0) = 0
1362 or nvl(p_prev_smb_days_worked,0) = 0
1363 or p_emp_join_prev_year = 'X'
1364 or p_emp_leave_cur_year = 'X') then
1365 p_smb_income_exem := 0;
1366 else
1367 if p_emp_join_prev_year = 'Y' then
1368 l_er_smb_cur_earn := l_er_smb_cur_earn
1369 * (p_prev_smb_days_worked / p_cur_smb_days_worked);
1370 end if;
1371 if p_emp_leave_cur_year = 'Y' then
1372 l_er_smb_prev_earn := l_er_smb_prev_earn
1373 * (p_cur_smb_days_worked / p_prev_smb_days_worked);
1374 end if;
1375 --
1376 p_smb_income_exem := greatest(trunc(l_er_smb_prev_earn - l_er_smb_cur_earn),0) ;
1377 end if;
1378 end if;
1379 -- End of Bug 9079478
1380 end data;
1381
1382 ---------------Prodecure to fetch Employee Expense Amounts------------------
1383 -- Bug # 5446051
1384 procedure EMP_EXPENSE_DETAILS (p_emp_assignment_id in number
1385 ,p_year in number
1386 ,p_emp_ins_exp_nts out nocopy number
1387 ,p_emp_ins_exp_oth out nocopy number
1388 ,p_emp_med_exp_nts out nocopy number
1389 ,p_emp_med_exp_oth out nocopy number
1390 ,p_emp_edu_exp_nts out nocopy number
1391 ,p_emp_edu_exp_oth out nocopy number
1392 ,p_emp_card_exp_nts out nocopy number
1393 ,p_emp_card_exp_oth out nocopy number
1394 ,p_emp_cash_exp_nts out nocopy number
1395 ,p_emp_don_exp_nts out nocopy number
1396 ,p_emp_don_exp_oth out nocopy number
1397 ,p_emp_direct_exp_nts out nocopy number
1398 ,p_emp_direct_exp_oth out nocopy number
1399 ,p_emp_tm_exp_nts out nocopy number
1400 ,p_emp_tm_exp_oth out nocopy number
1401 ,p_emp_tution_exp_oth out nocopy number)
1402 is
1403
1404 cursor sp_tax_exem_info is
1405 select (nvl(aei_information2,0) + nvl(aei_information3,0) +
1406 nvl(aei_information5,0) + nvl(aei_information6,0)) --Insurance total -- Bug 5726158
1407 ,nvl(aei_information24,0) --Medical Exem Total
1408 ,nvl(aei_information10,0) --Education Exem Total1
1409 ,(nvl(aei_information18,0)
1410 + nvl(aei_information19,0)
1411 + nvl(aei_information20,0)
1412 + nvl(aei_information22,0)
1413 + nvl(aei_information23,0)
1414 + nvl(aei_information30,0)) --Donation Total1
1415 ,nvl(aei_information19,0) --Political_donation
1416 ,nvl(aei_information1,fnd_date.date_to_canonical(to_date('31-12-'||to_char(p_year),'dd-mm-rrrr'))) --Effective Date
1417 from per_assignment_extra_info
1418 where ASSIGNMENT_ID=p_emp_assignment_id
1419 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
1420 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1421
1422 cursor sp_tax_exem_info2 is
1423 select nvl(aei_information2,0) --Education Exem Total2
1424 ,(nvl(aei_information4,0)
1425 +nvl(aei_information5,0)
1426 +nvl(aei_information6,0)
1427 +nvl(aei_information7,0)) -- Bug 7508706: Donation Total2
1428 ,nvl(aei_information8,0) -- Bug 7508706: Total Dependent Donation Expense
1429 ,nvl(aei_information11, 0) -- Bug 7644535: LTCI premium
1430 ,nvl(aei_information10,'N') -- Bug 9381801: Medical Total and Details Flag
1431 from per_assignment_extra_info
1432 where ASSIGNMENT_ID=p_emp_assignment_id
1433 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
1434 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1435
1436 cursor tax_exem_info is
1437 select nvl(aei_information7,0) --Card Total
1438 ,nvl(aei_information13,0) --Cash Total
1439 ,nvl(aei_information10,0) --Direct Total
1440 , nvl(aei_information12,0) -- Tution Total
1441 from per_assignment_extra_info
1442 where ASSIGNMENT_ID=p_emp_assignment_id
1443 and information_type = 'KR_YEA_TAX_EXEM_INFO'
1444 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1445
1446 cursor tax_exem_info2 is
1447 select nvl(aei_information3, 0), -- TM Card Total Bug 15911822
1448 nvl(aei_information5, 0), -- TM Cash Total Bug 15911822
1449 nvl(aei_information7, 0) -- TM Direct Total Bug 15911822
1450 from per_assignment_extra_info
1451 where ASSIGNMENT_ID=p_emp_assignment_id
1452 and information_type = 'KR_YEA_TAX_EXEM_INFO2'
1453 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1454
1455 cursor csr_get_ins_bal(p_balance_name in varchar2,
1456 p_dimension_name in varchar2) is
1457 select defined_balance_id
1458 from pay_defined_balances pdb
1459 ,pay_balance_types pbt
1460 ,pay_balance_dimensions pbd
1461 where pbt.balance_type_id = pdb.balance_type_id
1462 and pdb.balance_dimension_id = pbd.balance_dimension_id
1463 and pbt.balance_name = p_balance_name
1464 and pbd.dimension_name = p_dimension_name
1465 and pdb.legislation_code = 'KR';
1466
1467 cursor emp_exp is
1468 select aei_information2 --Medical NTS
1469 ,aei_information3 --Education NTS
1470 ,aei_information4 --Cards NTS
1471 ,aei_information5 --Donation NTS
1472 ,aei_information6 --Insurance NTS -- Bug 5726158
1473 ,aei_information7 --Direct Payment -- Bug 10184055
1474 ,aei_information8 -- TM Card NTS Bug 15911822
1475 ,aei_information9 -- TM Direct NTS Bug 15911822
1476 from per_assignment_extra_info
1477 where ASSIGNMENT_ID=p_emp_assignment_id
1478 and information_type = 'KR_YEA_EMP_EXPENSE_DETAILS'
1479 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1480
1481 -- Bug 9381801
1482 cursor medical_emp_totals is
1483 select
1484 nvl(sum(decode(aei_information7, '0', nvl(aei_information3,0) + nvl(aei_information11,0), 0)),0) employee
1485 from per_assignment_extra_info pai
1486 where pai.assignment_id = p_emp_assignment_id
1487 and pai.information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
1488 and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
1489
1490
1491 -- Local Variables
1492
1493 l_hi_prem_bal NUMBER;
1494 l_ei_prem_bal NUMBER;
1495 l_ltci_prem_bal NUMBER; -- Bug 7644535
1496 l_prev_ltci_prem NUMBER; -- Bug 7644535
1497 l_ltci_bal_id NUMBER; -- Bug 7644535
1498 l_prev_hi_prem NUMBER; -- Bug 5735177
1499 l_prev_ei_prem NUMBER;
1500 l_prev_np_prem NUMBER;
1501 l_prev_pen_prem NUMBER; /* Bug 6655323 */
1502 l_hi_bal_id NUMBER;
1503 l_ei_bal_id NUMBER;
1504 l_virtual_date DATE := to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
1505 l_don_tax_break NUMBER;
1506 l_ins_total NUMBER;
1507 l_ins_oth number;
1508 l_med_exem_total number;
1509 l_edu_exem_total1 number;
1510 l_don_total1 number;
1511 l_edu_exem_total2 number;
1512 l_don_total2 number;
1513 l_card_total number;
1514 l_cash_total number;
1515 l_ins_nts number;
1516 l_med_nts number;
1517 l_edu_nts number;
1518 l_card_nts number;
1519 l_don_nts number;
1520 l_effective_date varchar2(20);
1521 l_political_donation number;
1522 l_tot_dpnt_don_exp number; -- Bug 7508706
1523 l_prev_sep_pension number; -- Bug 7508706
1524 l_ltci_prem number; -- Bug 7644535
1525 l_med_flag varchar2(1); -- Bug 9381801
1526 l_med_total number; -- Bug 9381801
1527 l_direct_nts number; -- Bug 10184055
1528 l_direct_total number; -- Bug 10184055
1529 l_tm_card_total number; -- Bug 15911822
1530 l_tm_cash_total number; --Bug 15911822
1531 l_tm_direct_total number; -- Bug 15911822
1532 l_tm_card_nts number; -- Bug 15911822
1533 l_tm_direct_nts number; -- Bug 15911822
1534 l_tutuion_nts number; -- Bug 15911822
1535 l_tution_total number; -- Bug 15911822
1536 begin
1537 open csr_get_ins_bal('HI_PREM_EE','_ASG_YTD');
1538 fetch csr_get_ins_bal into l_hi_bal_id;
1539 close csr_get_ins_bal;
1540
1541 open csr_get_ins_bal('EI_PREM','_ASG_YTD');
1542 fetch csr_get_ins_bal into l_ei_bal_id;
1543 close csr_get_ins_bal;
1544 -- Bug 7644535
1545 open csr_get_ins_bal('LTCI_PREM_EE','_ASG_YTD');
1546 fetch csr_get_ins_bal into l_ltci_bal_id;
1547 close csr_get_ins_bal;
1548
1549 ----------------------------------------------------------------------
1550 -- Get the balance values of HI_PREM_EE_ASG_YTD and EI_PREM_ASG_YTD
1551 -- and LTCI_PREM_EE_ASG_YTD
1552 ----------------------------------------------------------------------
1553 l_hi_prem_bal := pay_balance_pkg.get_value( l_hi_bal_id,
1554 p_emp_assignment_id,
1555 l_virtual_date);
1556
1557 l_ei_prem_bal := pay_balance_pkg.get_value (l_ei_bal_id,
1558 p_emp_assignment_id,
1559 l_virtual_date);
1560 -- Bug 7644535
1561 l_ltci_prem_bal := pay_balance_pkg.get_value (l_ltci_bal_id,
1562 p_emp_assignment_id,
1563 l_virtual_date);
1564 -- Bug 5735177
1565 open csr_prev_employer_info(p_emp_assignment_id,p_year);
1566 fetch csr_prev_employer_info into l_prev_hi_prem, l_prev_ei_prem, l_prev_np_prem,
1567 l_prev_pen_prem, l_prev_sep_pension, l_prev_ltci_prem; -- Bug 7508706,7644535
1568 close csr_prev_employer_info;
1569
1570 open sp_tax_exem_info;
1571 fetch sp_tax_exem_info into l_ins_total,
1572 l_med_exem_total,
1573 l_edu_exem_total1,
1574 l_don_total1,
1575 l_political_donation,
1576 l_effective_date;
1577 close sp_tax_exem_info;
1578 -- bug 5726158
1579
1580 open sp_tax_exem_info2;
1581 fetch sp_tax_exem_info2 into l_edu_exem_total2,
1582 l_don_total2,
1583 l_tot_dpnt_don_exp, -- Bug 7508706
1584 l_ltci_prem, -- Bug 7644535
1585 l_med_flag; -- Bug 9381801
1586 close sp_tax_exem_info2;
1587
1588 l_ins_total := nvl(l_ins_total,0) + greatest(l_hi_prem_bal,0) + greatest(l_ei_prem_bal,0)
1589 + greatest(l_ltci_prem_bal,0) + nvl(l_ltci_prem,0) + nvl(l_prev_hi_prem,0)
1590 + nvl(l_prev_ei_prem,0) + nvl(l_prev_ltci_prem,0); -- Bug 7644535, Bug 8298522
1591
1592 open tax_exem_info;
1593 fetch tax_exem_info into l_card_total,
1594 l_cash_total,
1595 l_direct_total,
1596 l_tution_total; -- Bug 15911822
1597 close tax_exem_info;
1598
1599 -- Bug 15911822 Starts
1600 open tax_exem_info2;
1601 fetch tax_exem_info2 into l_tm_card_total,
1602 l_tm_cash_total,
1603 l_tm_direct_total;
1604 close tax_exem_info2;
1605 -- Bug 15911822 Ends
1606 open emp_exp;
1607 fetch emp_exp into l_med_nts,
1608 l_edu_nts,
1609 l_card_nts,
1610 l_don_nts,
1611 l_ins_nts,
1612 l_direct_nts,
1613 l_tm_card_nts, -- Bug 15911822
1614 l_tm_direct_nts; -- Bug 15911822
1615 close emp_exp;
1616
1617 l_don_tax_break := pay_kr_yea_form_pkg.get_donation_tax_break(l_effective_date,l_political_donation);
1618 -- Bug 9381801
1619 open medical_emp_totals;
1620 fetch medical_emp_totals into l_med_total;
1621 close medical_emp_totals;
1622 -- Bug 10184055
1623 p_emp_direct_exp_nts := nvl(l_direct_nts,0);
1624 p_emp_direct_exp_oth := nvl(l_direct_total,0)- nvl(l_direct_nts,0);
1625
1626 p_emp_ins_exp_nts := nvl(l_ins_nts,0); -- Bug 5726158
1627 p_emp_ins_exp_oth := nvl(l_ins_total,0) - nvl(l_ins_nts,0);
1628 p_emp_med_exp_nts := nvl(l_med_nts,0);
1629
1630 if l_med_flag = 'N' then
1631 p_emp_med_exp_oth := nvl(l_med_exem_total,0)
1632 - nvl(l_med_nts,0);
1633 else
1634 p_emp_med_exp_oth := nvl(l_med_exem_total,0) + nvl(l_med_total,0) - nvl(l_med_nts,0) ;
1635
1636 end if;
1637
1638 p_emp_edu_exp_nts := nvl(l_edu_nts,0);
1639 p_emp_edu_exp_oth := (nvl(l_edu_exem_total1,0)
1640 + nvl(l_edu_exem_total2,0))
1641 - nvl(l_edu_nts,0);
1642
1643 p_emp_card_exp_nts := nvl(l_card_nts,0);
1644 p_emp_card_exp_oth := nvl(l_card_total,0)
1645 - nvl(l_card_nts,0);
1646
1647 p_emp_cash_exp_nts := nvl(l_cash_total,0);
1648
1649 p_emp_don_exp_nts := nvl(l_don_nts,0);
1650 p_emp_don_exp_oth := (nvl(l_don_total1,0)
1651 + nvl(l_don_total2,0))
1652 - (nvl(l_don_tax_break,0) + nvl(l_don_nts,0) + nvl(l_tot_dpnt_don_exp,0)); -- Bug 7508706
1653 p_emp_tm_exp_nts := nvl(l_tm_card_nts,0) + nvl(l_tm_direct_nts,0) + nvl(l_tm_cash_total, 0); -- Bug 15911822
1654 p_emp_tm_exp_oth := ( nvl(l_tm_card_total,0) - nvl(l_tm_card_nts,0) ) + ( nvl(l_tm_direct_total,0) - nvl(l_tm_direct_nts,0) ) ; -- Bug 15911822
1655 p_emp_tution_exp_oth := nvl(l_tution_total,0); -- Bug 15911822
1656
1657 end;
1658 -- End of Bug # 5446051
1659 -- Bug 5654127
1660 -------------------------------------------------------------------------------------
1661 Function EMP_EXPENSE_DETAILS (p_assignment_id in number -- Bug 5856504
1662 ,p_year in number
1663 ,p_emp_ins_exp_nts out nocopy number
1664 ,p_emp_ins_exp_oth out nocopy number
1665 ,p_emp_med_exp_nts out nocopy number
1666 ,p_emp_med_exp_oth out nocopy number
1667 ,p_emp_edu_exp_nts out nocopy number
1668 ,p_emp_edu_exp_oth out nocopy number
1669 ,p_emp_card_exp_nts out nocopy number
1670 ,p_emp_card_exp_oth out nocopy number
1671 ,p_emp_cash_exp_nts out nocopy number
1672 ,p_emp_don_exp_nts out nocopy number
1673 ,p_emp_don_exp_oth out nocopy number
1674 ,p_emp_direct_exp_nts out nocopy number
1675 ,p_emp_direct_exp_oth out nocopy number
1676 ,p_emp_tm_exp_nts out nocopy number
1677 ,p_emp_tm_exp_oth out nocopy number
1678 ,p_emp_tution_exp_oth out nocopy number) return number
1679 -------------------------------------------------------------------------------------
1680 is
1681 p_emp_assignment_id number; -- Bug 5856504
1682
1683 begin
1684 p_emp_assignment_id := p_assignment_id; -- Bug 5856504
1685 EMP_EXPENSE_DETAILS (p_emp_assignment_id
1686 ,p_year
1687 ,p_emp_ins_exp_nts
1688 ,p_emp_ins_exp_oth
1689 ,p_emp_med_exp_nts
1690 ,p_emp_med_exp_oth
1691 ,p_emp_edu_exp_nts
1692 ,p_emp_edu_exp_oth
1693 ,p_emp_card_exp_nts
1694 ,p_emp_card_exp_oth
1695 ,p_emp_cash_exp_nts
1696 ,p_emp_don_exp_nts
1697 ,p_emp_don_exp_oth
1698 ,p_emp_direct_exp_nts
1699 ,p_emp_direct_exp_oth
1700 ,p_emp_tm_exp_nts
1701 ,p_emp_tm_exp_oth
1702 ,p_emp_tution_exp_oth);
1703 return 1;
1704 end;
1705 -- End of 5654127
1706
1707 end pay_kr_paykryrs_pkg;