[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_SSHR_UTILS_PKG
Source
1 package body pay_kr_yea_sshr_utils_pkg
2 /* $Header: pykryutl.pkb 120.8.12010000.5 2008/08/12 10:32:56 vaisriva ship $ */
3 as
4 --------------------------------------------------------------
5 g_debug boolean := hr_utility.debug_enabled;
6 --------------------------------------------------------------
7
8 CURSOR csr_get_def_bal_id(p_bal_name IN varchar2) is
9 select pdb.defined_balance_id
10 from pay_balance_types pbt,
11 pay_defined_balances pdb,
12 pay_balance_dimensions pbd
13 where pbt.balance_type_id = pdb.balance_type_id
14 and pbt.balance_name = p_bal_name
15 and pdb.balance_dimension_id = pbd.balance_dimension_id
16 and pbd.dimension_name in ('_ASG_YTD_MTH','_ASG_YTD_BON')
17 and pbt.legislation_code = 'KR'
18 and pbd.legislation_code = 'KR';
19
20 CURSOR csr_get_def_bal_id_ytd(p_bal_name IN varchar2) is
21 select pdb.defined_balance_id
22 from pay_balance_types pbt,
23 pay_defined_balances pdb,
24 pay_balance_dimensions pbd
25 where pbt.balance_type_id = pdb.balance_type_id
26 and pbt.balance_name = p_bal_name
27 and pdb.balance_dimension_id = pbd.balance_dimension_id
28 and pbd.dimension_name = '_ASG_YTD'
29 and pbt.legislation_code = 'KR'
30 and pbd.legislation_code = 'KR';
31
32
33 function yea_entry_status(p_assignment_id number, p_target_year varchar2) return varchar2
34 is
35 l_entry_status VARCHAR2(2);
36
37 cursor csr_get_entry_status is
38 select ayi_information3
39 from per_kr_assignment_yea_info
40 where information_type = 'KR_YEA_ENTRY_STATUS'
41 and assignment_id = p_assignment_id
42 and target_year = p_target_year;
43 begin
44
45 OPEN csr_get_entry_status;
46 FETCH csr_get_entry_status into l_entry_status;
47 IF csr_get_entry_status%NOTFOUND then
48 return 'N';
49 ELSE
50 return l_entry_status;
51 END IF;
52 end yea_entry_status;
53
54 -----------------------------------------------------------------------------------
55 -- This function takes Concurrent request ID, and output type as input parameters
56 -- and it returns and URL for the Concurrent requests output / log file.
57 -----------------------------------------------------------------------------------
58
59 function get_URL(p_file_type varchar2,
60 p_request_id number,
61 p_gwy_uid varchar2,
62 p_two_task varchar2) return varchar2
63 is
64 l_return_url varchar2(256);
65 begin
66 --
67 if p_file_type = 'OUTPUT' then
68 l_return_url := fnd_webfile.get_url(fnd_webfile.request_out, p_request_id, p_gwy_uid, p_two_task, 15);
69 elsif p_file_type = 'LOG' then
70 l_return_url := fnd_webfile.get_url(fnd_webfile.request_log, p_request_id, p_gwy_uid, p_two_task, 15);
71 else
72 l_return_url := 'ERROR';
73 end if;
74 --
75 return l_return_url;
76 end get_URL;
77 -----------------------------------------------------------------------------------
78 -- Bug : 4568233
79 -- Function get_total_taxable returns the total taxable earnings of an employee's
80 -- assignment as on an effective date.
81 -----------------------------------------------------------------------------------
82 function get_total_taxable(p_assignment_id number, p_effective_date date) return number
83 is
84 l_def_balance_id1 NUMBER;
85 l_def_balance_id2 NUMBER;
86 l_total_taxable NUMBER;
87 --
88 begin
89
90 OPEN csr_get_def_bal_id('TOTAL_TAXABLE_EARNINGS');
91 FETCH csr_get_def_bal_id into l_def_balance_id1;
92 FETCH csr_get_def_bal_id into l_def_balance_id2;
93 if csr_get_def_bal_id%NOTFOUND then
94 CLOSE csr_get_def_bal_id;
95 raise no_data_found;
96 end if;
97 CLOSE csr_get_def_bal_id;
98
99 l_total_taxable := nvl(pay_balance_pkg.get_value(l_def_balance_id1,p_assignment_id,p_effective_date),0)+
100 nvl(pay_balance_pkg.get_value(l_def_balance_id2,p_assignment_id,p_effective_date),0);
101
102 return(l_total_taxable);
103 end;
104 -----------------------------------------------------------------------------------
105 -- Bug : 4568233
106 -- Function get_total_itax returns the total income tax incurred for an employee's
107 -- assignment as on an effective date.
108 -----------------------------------------------------------------------------------
109 function get_total_itax(p_assignment_id number, p_effective_date date) return number
110 is
111 l_def_balance_id1 NUMBER;
112 l_def_balance_id2 NUMBER;
113 l_total_itax NUMBER;
114 --
115 begin
116
117 OPEN csr_get_def_bal_id('ITAX');
118 FETCH csr_get_def_bal_id into l_def_balance_id1;
119 FETCH csr_get_def_bal_id into l_def_balance_id2;
120 if csr_get_def_bal_id%NOTFOUND then
121 CLOSE csr_get_def_bal_id;
122 raise no_data_found;
123 end if;
124 CLOSE csr_get_def_bal_id;
125
126 l_total_itax := nvl(pay_balance_pkg.get_value(l_def_balance_id1,p_assignment_id,p_effective_date),0)
127 +nvl(pay_balance_pkg.get_value(l_def_balance_id2,p_assignment_id,p_effective_date),0);
128
129 return(l_total_itax);
130 end;
131 -----------------------------------------------------------------------------------
132 -- Bug : 4568233
133 -- Function get_total_rtax returns the total resident tax incurred for an employee's
134 -- assignment as on an effective date.
135 -----------------------------------------------------------------------------------
136 function get_total_rtax(p_assignment_id number, p_effective_date date) return number
137 is
138 l_def_balance_id1 NUMBER;
139 l_def_balance_id2 NUMBER;
140 l_total_rtax NUMBER;
141
142 --
143 begin
144
145 OPEN csr_get_def_bal_id('RTAX');
146 FETCH csr_get_def_bal_id into l_def_balance_id1;
147 FETCH csr_get_def_bal_id into l_def_balance_id2;
148 if csr_get_def_bal_id%NOTFOUND then
149 CLOSE csr_get_def_bal_id;
150 raise no_data_found;
151 end if;
152 CLOSE csr_get_def_bal_id;
153 l_total_rtax := nvl(pay_balance_pkg.get_value(l_def_balance_id1,p_assignment_id,p_effective_date),0)
154 +nvl(pay_balance_pkg.get_value(l_def_balance_id2,p_assignment_id,p_effective_date),0);
155 return(l_total_rtax);
156 end;
157 -----------------------------------------------------------------------------------
158 -- Bug : 4568233
159 -- Function get_total_stax returns the total special tax incurred for an employee's
160 -- assignment as on an effective date.
161 -----------------------------------------------------------------------------------
162 function get_total_stax(p_assignment_id number, p_effective_date date) return number
163 is
164 l_def_balance_id1 NUMBER;
165 l_def_balance_id2 NUMBER;
166 l_total_stax NUMBER;
167
168 begin
169
170 OPEN csr_get_def_bal_id('STAX');
171 FETCH csr_get_def_bal_id into l_def_balance_id1;
172 FETCH csr_get_def_bal_id into l_def_balance_id2;
173 if csr_get_def_bal_id%NOTFOUND then
174 CLOSE csr_get_def_bal_id;
175 raise no_data_found;
176 end if;
177 CLOSE csr_get_def_bal_id;
178
179 l_total_stax := nvl(pay_balance_pkg.get_value(l_def_balance_id1,p_assignment_id,p_effective_date),0)
180 +nvl(pay_balance_pkg.get_value(l_def_balance_id2,p_assignment_id,p_effective_date),0);
181 return(l_total_stax);
182 end;
183 -----------------------------------------------------------------------------------
184 -- Gets the YTD balance for Overseas Earnings
185 -----------------------------------------------------------------------------------
186 function get_ovs_processed(p_assignment_id number, p_effective_date date) return number
187 is
188 l_def_balance_id NUMBER;
189 l_ovs_bal NUMBER;
190
191 begin
192
193 OPEN csr_get_def_bal_id_ytd('Overseas Earnings');
194 FETCH csr_get_def_bal_id_ytd into l_def_balance_id;
195 if csr_get_def_bal_id_ytd%NOTFOUND then
196 CLOSE csr_get_def_bal_id_ytd;
197 raise no_data_found;
198 end if;
199 CLOSE csr_get_def_bal_id_ytd;
200
201 l_ovs_bal := nvl(pay_balance_pkg.get_value(l_def_balance_id,p_assignment_id,p_effective_date),0);
202
203 return(l_ovs_bal);
204 end;
205 -----------------------------------------------------------------------------------
206 -- This procedure will be used to transfer the data from intermediate table
207 -- to the EIT table (PER_ASSIGNMENT_EXTRA_INFO)
208 -----------------------------------------------------------------------------------
209 procedure submit_yea_info(p_assignment_id in varchar2,
210 p_target_year in varchar2,
211 p_effective_date in varchar2, -- expects canonical
212 p_return_status out nocopy varchar2, -- S => Success, E => Error
213 p_return_message out nocopy varchar2,
214 p_failed_record out nocopy varchar2
215 )
216 is
217 l_proc_name varchar2(50);
218 l_effective_date date;
219 l_yea_data_exists varchar2(1);
220 l_yea_fwtb_data_exists varchar2(1);
221 l_aei_id per_assignment_extra_info.assignment_extra_infO_id%type;
222 l_ovn per_assignment_extra_info.object_version_number%type;
223 l_record_name varchar2(50);
224 l_person_id number ;
225 l_session_id number ;
226 --
227 Cursor csr_yea_data_exists
228 Is
229 Select 'Y'
230 From per_assignment_extra_info
231 Where assignment_id = p_assignment_id
232 And to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
233 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
234 ,'KR_YEA_SP_TAX_EXEM_INFO2'
235 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
236 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
237 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
238 ,'KR_YEA_PREV_ER_INFO'
239 ,'KR_YEA_TAX_BREAK_INFO'
240 ,'KR_YEA_TAX_EXEM_INFO'
241 ,'KR_YEA_OVS_TAX_BREAK_INFO'
242 ,'KR_YEA_DETAIL_DONATION_INFO' --3506170
243 ,'KR_YEA_EMP_EXPENSE_DETAILS') ; -- Bug 5372366
244
245 Cursor csr_yea_fwtb_data_exists
246 Is
247 Select 'Y'
248 From per_assignment_extra_info
249 Where assignment_id = p_assignment_id
250 And information_type ='KR_YEA_FW_TAX_BREAK_INFO';
251
252 --
253 Cursor csr_kr_assignment_yea_info
254 Is
255 select info.assignment_id,
256 info.information_type,
257 info.ayi_information1,
258 info.ayi_information2,
259 info.ayi_information3,
260 info.ayi_information4,
261 info.ayi_information5,
262 info.ayi_information6,
263 info.ayi_information7,
264 info.ayi_information8,
265 info.ayi_information9,
266 info.ayi_information10,
267 info.ayi_information11,
268 info.ayi_information12,
269 info.ayi_information13,
270 info.ayi_information14,
271 info.ayi_information15,
272 info.ayi_information16,
273 info.ayi_information17,
274 info.ayi_information18,
275 info.ayi_information19,
276 info.ayi_information20,
277 info.ayi_information21,
278 info.ayi_information22,
279 info.ayi_information23,
280 info.ayi_information24,
281 info.ayi_information25,
282 info.ayi_information26,
283 info.ayi_information27,
284 info.ayi_information28,
285 info.ayi_information29,
286 info.ayi_information30
287 from per_kr_assignment_yea_info info
288 where assignment_id = p_assignment_id
289 and target_year = p_target_year
290 and information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
291 ,'KR_YEA_SP_TAX_EXEM_INFO2'
292 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
293 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
294 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
295 ,'KR_YEA_PREV_ER_INFO'
296 ,'KR_YEA_TAX_BREAK_INFO'
297 ,'KR_YEA_TAX_EXEM_INFO'
298 ,'KR_YEA_OVS_TAX_BREAK_INFO'
299 ,'KR_YEA_DETAIL_DONATION_INFO' --3506170
300 ,'KR_YEA_EMP_EXPENSE_DETAILS') -- Bug 5372366
301 Union All
302 select info.assignment_id,
303 info.information_type,
304 info.ayi_information1,
305 info.ayi_information2,
306 info.ayi_information3,
307 info.ayi_information4,
308 info.ayi_information5,
309 info.ayi_information6,
310 info.ayi_information7,
311 info.ayi_information8,
312 info.ayi_information9,
313 info.ayi_information10,
314 info.ayi_information11,
315 info.ayi_information12,
316 info.ayi_information13,
317 info.ayi_information14,
318 info.ayi_information15,
319 info.ayi_information16,
320 info.ayi_information17,
321 info.ayi_information18,
322 info.ayi_information19,
323 info.ayi_information20,
324 info.ayi_information21,
325 info.ayi_information22,
326 info.ayi_information23,
327 info.ayi_information24,
328 info.ayi_information25,
329 info.ayi_information26,
330 info.ayi_information27,
331 info.ayi_information28,
332 info.ayi_information29,
333 info.ayi_information30
334 from per_kr_assignment_yea_info info
335 where assignment_id = p_assignment_id
336 and information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
337 --
338 -- Bug 4915940
339 cursor csr_person_id(p_eff_date in date) is
340 select person_id
341 from per_assignments_f
342 where assignment_id = p_assignment_id
343 and p_eff_date between effective_start_date and effective_end_date ;
344 --
345 cursor csr_get_session_id is
346 select session_id
347 from fnd_sessions
348 where session_id = userenv('sessionid');
349
350 -- End of 4915940
351 --
352 begin
353 --
354 l_proc_name := 'pay_kr_yea_sshr_utils_pkg.submit_yea_info';
355 p_return_status := 'S';
356 --
357 --
358 if g_debug then
359 hr_utility.set_location(l_proc_name, 10);
360 end if;
361 --
362 --
363 if p_effective_date is null then
364 l_effective_date := to_date('31/12/'||to_char(p_target_year), 'DD/MM/YYYY');
365 else
366 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
367 end if;
368 --
369 -- Bug 4915940: Setting profiles PER_PERSON_ID and PER_ASSIGNMENT_ID
370
371 -- Insert a row into fnd_sessions if reqd
372 open csr_get_session_id;
373 fetch csr_get_session_id into l_session_id;
374 if csr_get_session_id%notfound then
375 insert into fnd_sessions(session_id,effective_date)
376 values (userenv('sessionid'),l_effective_date);
377 end if;
378 close csr_get_session_id;
379
380 open csr_person_id(l_effective_date) ;
381 fetch csr_person_id into l_person_id ;
382 close csr_person_id ;
383 --
384 fnd_profile.put('PER_ASSIGNMENT_ID', p_assignment_id ) ;
385 fnd_profile.put('PER_PERSON_ID', to_char(l_person_id) ) ;
386 -- End of 4915940
387 --
388 -- check if data has already been entered
389 --
390 open csr_yea_data_exists;
391 fetch csr_yea_data_exists into l_yea_data_exists;
392 if csr_yea_data_exists%notfound then
393 l_yea_data_exists := 'N';
394 end if;
395 close csr_yea_data_exists;
396
397 open csr_yea_fwtb_data_exists;
398 fetch csr_yea_fwtb_data_exists into l_yea_fwtb_data_exists;
399 if csr_yea_fwtb_data_exists%notfound then
400 l_yea_fwtb_data_exists := 'N';
401 end if;
402 close csr_yea_fwtb_data_exists;
403
404
405 delete from per_assignment_extra_info
406 where assignment_id = p_assignment_id
407 and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
408 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
409 ,'KR_YEA_SP_TAX_EXEM_INFO2'
410 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
411 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
412 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
413 ,'KR_YEA_PREV_ER_INFO'
414 ,'KR_YEA_TAX_BREAK_INFO'
415 ,'KR_YEA_TAX_EXEM_INFO'
416 ,'KR_YEA_OVS_TAX_BREAK_INFO'
417 ,'KR_YEA_DETAIL_DONATION_INFO' --3506170
418 ,'KR_YEA_EMP_EXPENSE_DETAILS') ; -- Bug 5372366
419
420 delete from per_assignment_extra_info
421 where assignment_id = p_assignment_id
422 And information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
423
424 --
425 --
426 for rec in csr_kr_assignment_yea_info loop
427 --
428 l_record_name := rec.information_type;
429 -- skip any record which is empty
430 if(NOT (rec.ayi_information1 is null and
431 rec.ayi_information2 is null and
432 rec.ayi_information3 is null and
433 rec.ayi_information4 is null and
434 rec.ayi_information5 is null and
435 rec.ayi_information6 is null and
436 rec.ayi_information7 is null and
437 rec.ayi_information8 is null and
438 rec.ayi_information9 is null and
439 rec.ayi_information10 is null and
440 rec.ayi_information11 is null and
441 rec.ayi_information12 is null and
442 rec.ayi_information13 is null and
443 rec.ayi_information14 is null and
444 rec.ayi_information15 is null and
445 rec.ayi_information16 is null and
446 rec.ayi_information17 is null and
447 rec.ayi_information18 is null and
448 rec.ayi_information19 is null and
449 rec.ayi_information20 is null and
450 rec.ayi_information21 is null and
451 rec.ayi_information22 is null and
452 rec.ayi_information23 is null and
453 rec.ayi_information24 is null and
454 rec.ayi_information25 is null and
455 rec.ayi_information26 is null and
456 rec.ayi_information27 is null and
457 rec.ayi_information28 is null and
458 rec.ayi_information29 is null and
459 rec.ayi_information30 is null )) then
460
461 hr_assignment_extra_info_api.create_assignment_extra_info(
462 p_validate => false,
463 p_assignment_id => rec.assignment_id,
464 p_information_type => rec.information_type,
465 p_aei_information_category => rec.information_type,
466 p_aei_information1 => rec.ayi_information1,
467 p_aei_information2 => rec.ayi_information2,
468 p_aei_information3 => rec.ayi_information3,
469 p_aei_information4 => rec.ayi_information4,
470 p_aei_information5 => rec.ayi_information5,
471 p_aei_information6 => rec.ayi_information6,
472 p_aei_information7 => rec.ayi_information7,
473 p_aei_information8 => rec.ayi_information8,
474 p_aei_information9 => rec.ayi_information9,
475 p_aei_information10 => rec.ayi_information10,
476 p_aei_information11 => rec.ayi_information11,
477 p_aei_information12 => rec.ayi_information12,
478 p_aei_information13 => rec.ayi_information13,
479 p_aei_information14 => rec.ayi_information14,
480 p_aei_information15 => rec.ayi_information15,
481 p_aei_information16 => rec.ayi_information16,
482 p_aei_information17 => rec.ayi_information17,
483 p_aei_information18 => rec.ayi_information18,
484 p_aei_information19 => rec.ayi_information19,
485 p_aei_information20 => rec.ayi_information20,
486 p_aei_information21 => rec.ayi_information21,
487 p_aei_information22 => rec.ayi_information22,
488 p_aei_information23 => rec.ayi_information23,
489 p_aei_information24 => rec.ayi_information24,
490 p_aei_information25 => rec.ayi_information25,
491 p_aei_information26 => rec.ayi_information26,
492 p_aei_information27 => rec.ayi_information27,
493 p_aei_information28 => rec.ayi_information28,
494 p_aei_information29 => rec.ayi_information29,
495 p_aei_information30 => rec.ayi_information30,
496 p_assignment_extra_info_id => l_aei_id,
497 p_object_version_number => l_ovn);
498 end if;
499 end loop;
500 --
501 commit;
502 --
503 exception
504 when others then
505 rollback;
506 p_failed_record := l_record_name;
507 p_return_status := 'E';
508 p_return_message := sqlerrm;
509 --
510 end submit_yea_info;
511 -----------------------------------------------------------------------------------
512 -- Bug : 4568233
513 -- This functions determines whether an employee is allowed to update the YEA
514 -- information provided by him based on the effective start and end dates for the
515 -- entry of YEA information.
516 -----------------------------------------------------------------------------------
517 function update_allowed(p_business_group_id in number,
518 p_assignment_id in number,
519 p_target_year in number,
520 p_effective_date in date)
521 return varchar2
522 is
523
524 -- cursor to get the Update Allowed Flag of the assignment
525 cursor csr_update_allowed_flag is
526 select nvl(aei_information2, 'Y')
527 from per_assignment_extra_info
528 where information_type = 'KR_YEA_ENTRY_UPDATE'
529 and aei_information1 = p_target_year
530 and assignment_id = p_assignment_id;
531
532
533 -- cursor to check whether YEA for target year has been run for the assignment
534 cursor csr_yea_exist is
535 select 'Y'
536 from pay_payroll_actions ppa,
537 pay_assignment_actions paa
538 where paa.assignment_id = p_assignment_id
539 and paa.source_action_id is null
540 and ppa.payroll_action_id = paa.payroll_action_id
541 and ppa.action_type = 'B'
542 and ppa.action_status = 'C'
543 and trunc(ppa.effective_date, 'YYYY') = trunc(p_effective_date, 'YYYY')
544 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null) = 'YEA'
545 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_QUALIFIER', null) = 'KR'
546 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) IN ('N','I');
547
548 -- Cursor to get Org Structure version id of the Primary Hierarchy of the BG
549 cursor csr_org_struct_version is
550 select posv.org_structure_version_id
551 from per_organization_structures pos,
552 per_org_structure_versions posv
553 where pos.primary_structure_flag = 'Y'
554 and pos.business_group_id = p_business_group_id
555 and pos.organization_structure_id = posv.organization_structure_id
556 and p_effective_date between posv.date_from and nvl(posv.date_to,p_effective_date);
557
558 -- cursor fetches the entry periods of the organizations defined in the hierarchy
559 -- in bottom to top order starting from the Employee's Organization.
560 cursor csr_hierarchy_entry_period(p_version in number,
561 p_emp_org_id in number) is
562 select min(fnd_date.canonical_to_date(hoi.org_information2)),
563 max(fnd_date.canonical_to_date(hoi.org_information3)),
564 max(fnd_date.canonical_to_date(hoi.org_information4))
565 from (select pose.organization_id_parent,
566 level hierarchy_level,
567 pose.organization_id_child
568 from per_org_structure_elements pose
569 start with pose.organization_id_child = p_emp_org_id
570 and pose.org_structure_version_id = p_version
571 connect by prior pose.organization_id_parent = organization_id_child
572 ) org,
573 hr_organization_information hoi
574 where hoi.organization_id = org.organization_id_parent
575 and hoi.org_information1 = p_target_year
576 and hoi.org_information_context = 'KR_YEA_ENTRY_PERIOD_ORG'
577 group by organization_id_child, org.hierarchy_level
578 order by org.hierarchy_level asc;
579
580 -- cursor fetches the Entry Period defined in BG level
581 cursor csr_bg_entry_period is
582 select min(fnd_date.canonical_to_date(hoi.org_information2)),
583 max(fnd_date.canonical_to_date(hoi.org_information3)),
584 max(fnd_date.canonical_to_date(hoi.org_information4))
585 from hr_organization_information hoi
586 where hoi.organization_id = p_business_group_id
587 and hoi.org_information1 = p_target_year
588 and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_BG';
589
590 -- cursor fetches the Entry Period defined in Employee's Org level
591 cursor csr_emp_org_entry_period (p_emp_org number) is
592 select min(fnd_date.canonical_to_date(hoi.org_information2)),
593 max(fnd_date.canonical_to_date(hoi.org_information3)),
594 max(fnd_date.canonical_to_date(hoi.org_information4))
595 from hr_organization_information hoi
596 where hoi.organization_id = p_emp_org
597 and hoi.org_information1 = p_target_year
598 and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_ORG';
599
600 -- cursor to get organization id of the Employee.
601 cursor csr_emp_org_id is
602 select organization_id
603 from per_assignments_f paf
604 where assignment_id = p_assignment_id
605 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
606
607 --
608 l_update_allowed varchar2(1);
609 l_asg_update_allowed varchar2(1);
610 l_entry_start_date date;
611 l_entry_end_date date;
612 l_update_cut_off_date date;
613 l_emp_org_id number;
614 l_struct_version_id number;
615 l_flag varchar2(1);
616 --
617 begin
618
619 if g_debug then
620 hr_utility.set_location('pay_kr_yea_sshr_utils_pkg.update_allowed', 10);
621 end if;
622
623 l_update_allowed := 'N';
624 l_entry_start_date := null;
625 l_entry_end_date := null;
626
627 -- check if YEA has been run for target year
628 open csr_yea_exist;
629 fetch csr_yea_exist into l_flag;
630
631 if g_debug then
632 hr_utility.trace('YEA Run Exists = '||l_flag);
633 end if;
634
635 if csr_yea_exist%NOTFOUND then
636
637 -- check if Period defined in Employee's Organization.
638 open csr_emp_org_id;
639 fetch csr_emp_org_id into l_emp_org_id;
640 close csr_emp_org_id;
641
642 if g_debug then
643 hr_utility.trace('fetching emp org entry period');
644 end if;
645
646 open csr_emp_org_entry_period(l_emp_org_id);
647 fetch csr_emp_org_entry_period into
648 l_entry_start_date,
649 l_entry_end_date,
650 l_update_cut_off_date;
651 close csr_emp_org_entry_period;
652
653 if l_entry_start_date is null then
654 if g_debug then
655 hr_utility.trace('Emp org entry period not found.');
656 hr_utility.trace('Fetching Entry Period from Hierarchy.');
657 end if;
658
659 -- check for periods of parent organizations in hierarchy
660 open csr_org_struct_version;
661 fetch csr_org_struct_version into l_struct_version_id;
662 close csr_org_struct_version;
663
664 -- check if primary hierarchy is defined
665 if l_struct_version_id is not null then
666 if g_debug then
667 hr_utility.trace('Struct Version ID : ' || l_struct_version_id);
668 end if;
669
670 open csr_hierarchy_entry_period(l_struct_version_id,l_emp_org_id);
671
672 fetch csr_hierarchy_entry_period into
673 l_entry_start_date,
674 l_entry_end_date,
675 l_update_cut_off_date;
676 close csr_hierarchy_entry_period;
677
678 end if; -- primary Hierarchy defined
679
680 -- 4657745
681 -- if entry period not found in Hierarchy, check BG Entry period
682 if l_entry_start_date is null then
683 if g_debug then
684 hr_utility.trace('Entry period of Hierarchy not found.');
685 hr_utility.trace('Fetching Entry Period from BG.');
686 end if;
687
688 -- check for entry period of the BG
689 open csr_bg_entry_period;
690 fetch csr_bg_entry_period into
691 l_entry_start_date,
692 l_entry_end_date,
693 l_update_cut_off_date;
694 close csr_bg_entry_period;
695
696 if l_entry_start_date is null then
697
698 l_update_allowed := 'N';
699
700 end if;
701
702 end if;
703
704 end if; -- emp org period exists.
705
706 if g_debug then
707 hr_utility.trace('l_entry_start_date = '|| l_entry_start_date);
708 hr_utility.trace('l_entry_end_date = '|| l_entry_end_date);
709 hr_utility.trace('l_update_cut_off_date = '|| l_update_cut_off_date);
710 end if;
711
712
713 -- compare employees effective entry period with current date
714 if l_entry_start_date is null then -- no entry period found
715 l_update_allowed := 'N';
716 else
717 -- if update cut-off is not defined assign last date of 4712
718 if l_update_cut_off_date is null then
719 l_update_cut_off_date := to_date('31.12.4712','DD.MM.YYYY');
720 end if;
721
722 if p_effective_date between l_entry_start_date and l_entry_end_date then
723 l_update_allowed := 'Y';
724 elsif p_effective_date between l_entry_end_date and l_update_cut_off_date then
725 -- check the assignment level update allowed flag.
726 l_asg_update_allowed := null;
727 open csr_update_allowed_flag;
728 fetch csr_update_allowed_flag into l_asg_update_allowed;
729 close csr_update_allowed_flag;
730
731 if l_asg_update_allowed = 'Y' then
732 l_update_allowed := 'Y';
733 else
734 l_update_allowed := 'N';
735 end if; -- assignment level update allowed
736
737 if g_debug then
738 hr_utility.trace('Assgn Level Update Allowed = '||l_update_allowed);
739 end if;
740
741 end if; -- compare current date with entry period
742
743 end if; -- entry period exists
744
745 else
746 l_update_allowed := 'N';
747 end if; -- YEA has been run
748
749 close csr_yea_exist;
750
751 return l_update_allowed;
752
753 end update_allowed;
754 -----------------------------------------------------------------------------------
755 -- Bug : 4568233
756 -- This procedure fires the fast formula KR_VAILDATE_YEA_DATE and passes few
757 -- parameters for vaildation of YEA. The formula returns 10 error messages
758 -- which are captured by this procedure and passed as OUT parameters.
759 -----------------------------------------------------------------------------------
760 procedure run_validation_formula( --4644132
761 P_BUSINESS_GROUP_ID in varchar2,
762 P_ASSIGNMENT_ID in varchar2,
763 P_TARGET_YEAR in varchar2,
764 P_EFFECTIVE_DATE in varchar2,
765 P_RETURN_MESSAGE out nocopy varchar2,
766 P_RETURN_STATUS out nocopy varchar2,
767 P_FF_MESSAGE0 out nocopy varchar2,
768 P_FF_MESSAGE1 out nocopy varchar2,
769 P_FF_MESSAGE2 out nocopy varchar2,
770 P_FF_MESSAGE3 out nocopy varchar2,
771 P_FF_MESSAGE4 out nocopy varchar2,
772 P_FF_MESSAGE5 out nocopy varchar2,
773 P_FF_MESSAGE6 out nocopy varchar2,
774 P_FF_MESSAGE7 out nocopy varchar2,
775 P_FF_MESSAGE8 out nocopy varchar2,
776 P_FF_MESSAGE9 out nocopy varchar2,
777 P_FF_RETURN_STATUS out nocopy varchar2,
778 ---------------- Special tax ---------------------
779 P_EE_EDUC_EXP in varchar2,
780 P_HOUSING_SAVING_TYPE in varchar2,
781 P_HOUSING_SAVING in varchar2,
782 P_HOUSING_PURCHASE_DATE in varchar2,
783 P_HOUSING_LOAN_DATE in varchar2,
784 P_HOUSING_LOAN_REPAY in varchar2,
785 P_LT_HOUSING_LOAN_DATE in varchar2,
786 P_LT_HOUSING_LOAN_INTEREST_REP in varchar2,
787 P_DONATION1 in varchar2,
788 P_POLITICAL_DONATION1 in varchar2,
789 P_HI_PREM in varchar2,
790 P_POLITICAL_DONATION2 in varchar2,
791 P_POLITICAL_DONATION3 in varchar2,
792 P_DONATION2 in varchar2,
793 P_DONATION3 in varchar2,
794 P_MED_EXP_EMP in varchar2,
795 P_LT_HOUSING_LOAN_DATE_1 in varchar2,
796 P_LT_HOUSING_LOAN_INT_REPAY_1 in varchar2,
797 P_MFR_MARRIAGE_OCCASIONS in varchar2,
798 P_MFR_FUNERAL_OCCASIONS in varchar2,
799 P_MFR_RELOCATION_OCCASIONS in varchar2,
800 P_EI_PREM in varchar2,
801 P_ESOA_DONATION in varchar2,
802 P_PERS_INS_NAME in varchar2,
803 P_PERS_INS_PREM in varchar2,
804 P_DISABLED_INS_PREM in varchar2,
805 P_MED_EXP in varchar2,
806 P_MED_EXP_DISABLED in varchar2,
807 P_MED_EXP_AGED in varchar2,
808 P_EE_OCCUPATION_EDUC_EXP in varchar2,
809 ----------------- FW Tax Break --------------------
810 P_IMMIGRATION_PURPOSE in varchar2,
811 P_CONTRACT_DATE in varchar2,
812 P_EXPIRY_DATE in varchar2,
813 P_STAX_APPLICABLE_FLAG in varchar2,
814 P_FW_APPLICATION_DATE in varchar2,
815 P_FW_SUBMISSION_DATE in varchar2,
816 ----------------- OVS Tax Break -------------------
817 P_TAX_PAID_DATE in varchar2,
818 P_OVS_SUBMISSION_DATE in varchar2,
819 P_KR_OVS_LOCATION in varchar2,
820 P_KR_OVS_WORK_PERIOD in varchar2,
821 P_KR_OVS_RESPONSIBILITY in varchar2,
822 P_TERRITORY_CODE in varchar2,
823 P_CURRENCY_CODE in varchar2,
824 P_TAXABLE in varchar2,
825 P_TAXABLE_SUBJ_TAX_BREAK in varchar2,
826 P_TAX_BREAK_RATE in varchar2,
827 P_TAX_FOREIGN_CURRENCY in varchar2,
828 P_TAX in varchar2,
829 P_OVS_APPLICATION_DATE in varchar2,
830 ----------------- Tax Break Info ------------------
831 P_HOUSING_LOAN_INTEREST_REPAY in varchar2,
832 P_STOCK_SAVING in varchar2,
833 P_LT_STOCK_SAVING1 in varchar2,
834 P_LT_STOCK_SAVING2 in varchar2,
835 ----------------- Tax Exems ----------------------
836 P_DIRECT_CARD_EXP in varchar2,
837 P_DPNT_DIRECT_EXP in varchar2,
838 P_GIRO_TUITION_PAID_EXP in varchar2,
839 P_CASH_RECEIPT_EXP in varchar2,
840 P_NP_PREM in varchar2,
841 P_PERS_PENSION_PREM in varchar2,
842 P_PERS_PENSION_SAVING in varchar2,
843 P_INVEST_PARTNERSHIP_FIN1 in varchar2,
844 P_INVEST_PARTNERSHIP_FIN2 in varchar2,
845 P_CREDIT_CARD_EXP in varchar2,
846 P_EMP_STOCK_OWN_PLAN_CONTRI in varchar2,
847 P_CREDIT_CARD_EXP_DPNT in varchar2,
848 P_PEN_PREM in varchar2, -- Bug 6024342
849 P_LTCI_PREM in varchar2 -- Bug 7260606
850 )
851 is
852 CURSOR csr_formula_exists(p_formula_name VARCHAR2,
853 p_effective_date DATE)
854 is
855 select formula_id
856 from ff_formulas_f
857 where formula_name = upper(p_formula_name)
858 and business_group_id = p_business_group_id
859 and p_effective_date between effective_start_date and effective_end_date;
860 --
861 CURSOR csr_get_session_id
862 is
863 select session_id from fnd_sessions
864 where session_id = userenv('sessionid');
865 --
866 CURSOR csr_get_person_id(p_assignment_id NUMBER)
867 is
868 select person_id
869 from per_assignments_f
870 where assignment_id = p_assignment_id;
871 --
872 l_formula_id NUMBER;
873 l_inputs ff_exec.inputs_t;
874 l_outputs ff_exec.outputs_t;
875 l_counter NUMBER := 0;
876 l_session_id NUMBER := 0;
877 l_date_earned DATE;
878 l_person_id NUMBER ;
879 l_cntr_loop NUMBER := 0;
880 l_target_year NUMBER := 0;
881 l_assignment_id NUMBER;
882 l_effective_date DATE;
883 l_year NUMBER;
884 --
885 begin
886 P_RETURN_STATUS := 'E';
887 P_FF_RETURN_STATUS := 'INVALID';
888
889 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
890 l_year := to_char(l_effective_date,'yyyy');
891 l_assignment_id := p_assignment_id;
892
893 OPEN csr_formula_exists('VALIDATE_YEA_DATA',l_effective_date); -- 4644132
894 FETCH csr_formula_exists into l_formula_id;
895 IF csr_formula_exists%NOTFOUND then
896 P_RETURN_STATUS := 'S';
897 return;
898 END IF;
899 CLOSE csr_formula_exists;
900 --
901
902 OPEN csr_get_session_id;
903 FETCH csr_get_session_id into l_session_id;
904 IF csr_get_session_id%NOTFOUND then
905 insert into fnd_sessions(session_id,effective_date)
906 values (userenv('sessionid'),l_effective_date);
907 END IF;
908 CLOSE csr_get_session_id;
909
910 OPEN csr_get_person_id(l_assignment_id);
911 FETCH csr_get_person_id into l_person_id;
912 CLOSE csr_get_person_id;
913
914 ff_exec.init_formula(
915 p_formula_id => l_formula_id,
916 p_effective_date => l_effective_date,
917 p_inputs => l_inputs,
918 p_outputs => l_outputs
919 );
920
921 l_counter := l_inputs.first;
922 if l_inputs is NOT NULL then
923 while l_counter <= l_inputs.last loop
924
925 if l_inputs(l_counter).name = 'ASSIGNMENT_ID' then
926 l_inputs(l_counter).value := l_assignment_id;
927 elsif l_inputs(l_counter).name = 'DATE_EARNED' then
928 l_inputs(l_counter).value := p_effective_date;
929 elsif l_inputs(l_counter).name = 'TARGET_YEAR' then -- 4657745
930 l_inputs(l_counter).value := p_target_year;
931 elsif l_inputs(l_counter).name = 'EFFECTIVE_DATE' then
932 l_inputs(l_counter).value := p_effective_date;
933 elsif l_inputs(l_counter).name = 'PERSON_ID' then
934 l_inputs(l_counter).value := l_person_id;
935 elsif l_inputs(l_counter).name = 'EE_EDUC_EXP' then
936 l_inputs(l_counter).value := P_EE_EDUC_EXP;
937 elsif l_inputs(l_counter).name = 'HOUSING_SAVING_TYPE' then
938 l_inputs(l_counter).value := P_HOUSING_SAVING_TYPE;
939 elsif l_inputs(l_counter).name = 'HOUSING_SAVING' then
940 l_inputs(l_counter).value := P_HOUSING_SAVING;
941 elsif l_inputs(l_counter).name = 'HOUSING_PURCHASE_DATE' then
942 l_inputs(l_counter).value := P_HOUSING_PURCHASE_DATE;
943 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_DATE' then
944 l_inputs(l_counter).value := P_HOUSING_LOAN_DATE;
945 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_REPAY' then
946 l_inputs(l_counter).value := P_HOUSING_LOAN_REPAY;
947 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_DATE' then
948 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_DATE;
949 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_INTEREST_REPAY' then
950 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_INTEREST_REP;
951 elsif l_inputs(l_counter).name = 'DONATION1' then
952 l_inputs(l_counter).value := P_DONATION1;
953 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION1' then
954 l_inputs(l_counter).value := P_POLITICAL_DONATION1;
955 elsif l_inputs(l_counter).name = 'HI_PREM' then
956 l_inputs(l_counter).value := P_HI_PREM;
957 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION2' then
958 l_inputs(l_counter).value := P_POLITICAL_DONATION2;
959 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION3' then
960 l_inputs(l_counter).value := P_POLITICAL_DONATION3;
961 elsif l_inputs(l_counter).name = 'DONATION2' then
962 l_inputs(l_counter).value := P_DONATION2;
963 elsif l_inputs(l_counter).name = 'DONATION3' then
964 l_inputs(l_counter).value := P_DONATION3;
965 elsif l_inputs(l_counter).name = 'MED_EXP_EMP' then
966 l_inputs(l_counter).value := P_MED_EXP_EMP;
967 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_DATE_1' then
968 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_DATE_1;
969 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_INT_REPAY_1' then
970 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_INT_REPAY_1;
971 elsif l_inputs(l_counter).name = 'MFR_MARRIAGE_OCCASIONS' then
972 l_inputs(l_counter).value := P_MFR_MARRIAGE_OCCASIONS;
973 elsif l_inputs(l_counter).name = 'MFR_FUNERAL_OCCASIONS' then
974 l_inputs(l_counter).value := P_MFR_FUNERAL_OCCASIONS;
975 elsif l_inputs(l_counter).name = 'MFR_RELOCATION_OCCASIONS' then
976 l_inputs(l_counter).value := P_MFR_RELOCATION_OCCASIONS;
977 elsif l_inputs(l_counter).name = 'EI_PREM' then
978 l_inputs(l_counter).value := P_EI_PREM;
979 elsif l_inputs(l_counter).name = 'ESOA_DONATION' then
980 l_inputs(l_counter).value := P_ESOA_DONATION;
981 elsif l_inputs(l_counter).name = 'PERS_INS_NAME' then
982 l_inputs(l_counter).value := P_PERS_INS_NAME;
983 elsif l_inputs(l_counter).name = 'PERS_INS_PREM' then
984 l_inputs(l_counter).value := P_PERS_INS_PREM;
985 elsif l_inputs(l_counter).name = 'DISABLED_INS_PREM' then
986 l_inputs(l_counter).value := P_DISABLED_INS_PREM;
987 elsif l_inputs(l_counter).name = 'MED_EXP' then
988 l_inputs(l_counter).value := P_MED_EXP;
989 elsif l_inputs(l_counter).name = 'MED_EXP_DISABLED' then
990 l_inputs(l_counter).value := P_MED_EXP_DISABLED;
991 elsif l_inputs(l_counter).name = 'MED_EXP_AGED' then
992 l_inputs(l_counter).value := P_MED_EXP_AGED;
993 elsif l_inputs(l_counter).name = 'EE_OCCUPATION_EDUC_EXP' then
994 l_inputs(l_counter).value := P_EE_OCCUPATION_EDUC_EXP;
995
996
997 elsif l_inputs(l_counter).name = 'IMMIGRATION_PURPOSE' then
998 l_inputs(l_counter).value := P_IMMIGRATION_PURPOSE;
999 elsif l_inputs(l_counter).name = 'CONTRACT_DATE' then
1000 l_inputs(l_counter).value := P_CONTRACT_DATE;
1001 elsif l_inputs(l_counter).name = 'EXPIRY_DATE' then
1002 l_inputs(l_counter).value := P_EXPIRY_DATE;
1003 elsif l_inputs(l_counter).name = 'STAX_APPLICABLE_FLAG' then
1004 l_inputs(l_counter).value := P_STAX_APPLICABLE_FLAG;
1005 elsif l_inputs(l_counter).name = 'FWTB_APPLICATION_DATE' then
1006 l_inputs(l_counter).value := P_FW_APPLICATION_DATE;
1007 elsif l_inputs(l_counter).name = 'FWTB_SUBMISSION_DATE' then
1008 l_inputs(l_counter).value := P_FW_SUBMISSION_DATE;
1009
1010
1011 elsif l_inputs(l_counter).name = 'TAX_PAID_DATE' then
1012 l_inputs(l_counter).value := P_TAX_PAID_DATE;
1013 elsif l_inputs(l_counter).name = 'OTB_SUBMISSION_DATE' then
1014 l_inputs(l_counter).value := P_OVS_SUBMISSION_DATE;
1015 elsif l_inputs(l_counter).name = 'KR_OVS_LOCATION' then
1016 l_inputs(l_counter).value := P_KR_OVS_LOCATION;
1017 elsif l_inputs(l_counter).name = 'KR_OVS_WORK_PERIOD' then
1018 l_inputs(l_counter).value := P_KR_OVS_WORK_PERIOD;
1019 elsif l_inputs(l_counter).name = 'KR_OVS_RESPONSIBILITY' then
1020 l_inputs(l_counter).value := P_KR_OVS_RESPONSIBILITY;
1021 elsif l_inputs(l_counter).name = 'TERRITORY_CODE' then
1022 l_inputs(l_counter).value := P_TERRITORY_CODE;
1023 elsif l_inputs(l_counter).name = 'CURRENCY_CODE' then
1024 l_inputs(l_counter).value := P_CURRENCY_CODE;
1025 elsif l_inputs(l_counter).name = 'TAXABLE' then
1026 l_inputs(l_counter).value := P_TAXABLE;
1027 elsif l_inputs(l_counter).name = 'TAXABLE_SUBJ_TAX_BREAK' then
1028 l_inputs(l_counter).value := P_TAXABLE_SUBJ_TAX_BREAK;
1029 elsif l_inputs(l_counter).name = 'TAX_BREAK_RATE' then
1030 l_inputs(l_counter).value := P_TAX_BREAK_RATE;
1031 elsif l_inputs(l_counter).name = 'TAX_FOREIGN_CURRENCY' then
1032 l_inputs(l_counter).value := P_TAX_FOREIGN_CURRENCY;
1033 elsif l_inputs(l_counter).name = 'TAX' then
1034 l_inputs(l_counter).value := P_TAX;
1035 elsif l_inputs(l_counter).name = 'OTB_APPLICATION_DATE' then
1036 l_inputs(l_counter).value := P_OVS_APPLICATION_DATE;
1037
1038
1039 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_INTEREST_REPAY' then
1040 l_inputs(l_counter).value := P_HOUSING_LOAN_INTEREST_REPAY;
1041 elsif l_inputs(l_counter).name = 'STOCK_SAVING' then
1042 l_inputs(l_counter).value := P_STOCK_SAVING;
1043 elsif l_inputs(l_counter).name = 'LT_STOCK_SAVING1' then
1044 l_inputs(l_counter).value := P_LT_STOCK_SAVING1;
1045 elsif l_inputs(l_counter).name = 'LT_STOCK_SAVING2' then
1046 l_inputs(l_counter).value := P_LT_STOCK_SAVING2;
1047
1048
1049 elsif l_inputs(l_counter).name = 'DIRECT_CARD_EXP' then
1050 l_inputs(l_counter).value := P_DIRECT_CARD_EXP;
1051 elsif l_inputs(l_counter).name = 'DPNT_DIRECT_EXP' then
1052 l_inputs(l_counter).value := P_DPNT_DIRECT_EXP;
1053 elsif l_inputs(l_counter).name = 'GIRO_TUITION_PAID_EXP' then
1054 l_inputs(l_counter).value := P_GIRO_TUITION_PAID_EXP;
1055 elsif l_inputs(l_counter).name = 'CASH_RECEIPT_EXP' then
1056 l_inputs(l_counter).value := P_CASH_RECEIPT_EXP;
1057 elsif l_inputs(l_counter).name = 'NP_PREM' then
1058 l_inputs(l_counter).value := P_NP_PREM;
1059 elsif l_inputs(l_counter).name = 'PERS_PENSION_PREM' then
1060 l_inputs(l_counter).value := P_PERS_PENSION_PREM;
1061 elsif l_inputs(l_counter).name = 'PERS_PENSION_SAVING' then
1062 l_inputs(l_counter).value := P_PERS_PENSION_SAVING;
1063 elsif l_inputs(l_counter).name = 'INVEST_PARTNERSHIP_FIN1' then
1064 l_inputs(l_counter).value := P_INVEST_PARTNERSHIP_FIN1;
1065 elsif l_inputs(l_counter).name = 'INVEST_PARTNERSHIP_FIN2' then
1066 l_inputs(l_counter).value := P_INVEST_PARTNERSHIP_FIN2;
1067 elsif l_inputs(l_counter).name = 'CREDIT_CARD_EXP' then
1068 l_inputs(l_counter).value := P_CREDIT_CARD_EXP;
1069 elsif l_inputs(l_counter).name = 'EMP_STOCK_OWN_PLAN_CONTRI' then
1070 l_inputs(l_counter).value := P_EMP_STOCK_OWN_PLAN_CONTRI;
1071 elsif l_inputs(l_counter).name = 'CREDIT_CARD_EXP_DPNT' then
1072 l_inputs(l_counter).value := P_CREDIT_CARD_EXP_DPNT;
1073 elsif l_inputs(l_counter).name = 'PEN_PREM' then -- Bug 6024342
1074 l_inputs(l_counter).value := P_PEN_PREM;
1075 elsif l_inputs(l_counter).name = 'LTCI_PREM' then -- Bug 7260606
1076 l_inputs(l_counter).value := P_LTCI_PREM;
1077 end if;
1078 l_counter := l_inputs.next(l_counter);
1079 end loop;
1080 end if;
1081
1082 ff_exec.run_formula(l_inputs, l_outputs);
1083 --get outputs
1084 if l_outputs is not NULL then
1085 for l_counter in l_outputs.first..l_outputs.last loop
1086 if l_outputs(l_counter).name = 'STATUS' then
1087 P_FF_RETURN_STATUS := trim(l_outputs(l_counter).value);
1088 elsif l_cntr_loop = 0 then
1089 P_FF_MESSAGE0 := trim(l_outputs(l_counter).value);
1090 elsif l_cntr_loop = 1 then
1091 P_FF_MESSAGE1 := trim(l_outputs(l_counter).value);
1092 elsif l_cntr_loop = 2 then
1093 P_FF_MESSAGE2 := trim(l_outputs(l_counter).value);
1094 elsif l_cntr_loop = 3 then
1095 P_FF_MESSAGE3 := trim(l_outputs(l_counter).value);
1096 elsif l_cntr_loop = 4 then
1097 P_FF_MESSAGE4 := trim(l_outputs(l_counter).value);
1098 elsif l_cntr_loop = 5 then
1099 P_FF_MESSAGE5 := trim(l_outputs(l_counter).value);
1100 elsif l_cntr_loop = 6 then
1101 P_FF_MESSAGE6 := trim(l_outputs(l_counter).value);
1102 elsif l_cntr_loop = 7 then
1103 P_FF_MESSAGE7 := trim(l_outputs(l_counter).value);
1104 elsif l_cntr_loop = 8 then
1105 P_FF_MESSAGE8 := trim(l_outputs(l_counter).value);
1106 elsif l_cntr_loop = 9 then
1107 P_FF_MESSAGE9 := trim(l_outputs(l_counter).value);
1108 end if;
1109 l_cntr_loop := l_cntr_loop + 1;
1110 end loop;
1111 end if;
1112 --
1113 if P_FF_RETURN_STATUS = 'VALID' then
1114 P_RETURN_STATUS := 'S';
1115 else
1116 P_RETURN_STATUS := 'E';
1117 P_RETURN_MESSAGE := 'PAY_KR_YEA_DATA_INVALID_MSG';
1118 end if;
1119
1120 --
1121 end run_validation_formula;
1122 -----------------------------------------------------------------------------------
1123 -- This procedure toggles the UPDATE_ALLOWED flag in the Extra Assignment Info
1124 -- Type KR_YEA_ENTRY_UPDATE
1125 -----------------------------------------------------------------------------------
1126 procedure change_access(P_ASSIGNMENT_ID in varchar2,
1127 P_TARGET_YEAR in varchar2,
1128 P_RESULT out nocopy varchar2)
1129 is
1130 cursor csr_access is
1131 select nvl(aei_information2,'Y') update_allowed,
1132 assignment_extra_info_id info_id
1133 from per_assignment_extra_info
1134 where assignment_id = P_ASSIGNMENT_ID
1135 and information_type = 'KR_YEA_ENTRY_UPDATE'
1136 and aei_information1 = P_TARGET_YEAR
1137 for update nowait;
1138
1139 l_current_access varchar2(5);
1140 l_future_access varchar2(5);
1141 l_record_present varchar2(5);
1142 l_info_id number;
1143
1144 begin
1145 open csr_access;
1146 fetch csr_access into l_current_access, l_info_id;
1147 if csr_access%NOTFOUND then
1148 l_record_present := 'N';
1149 l_current_access := 'N';
1150 else
1151 l_record_present := 'Y';
1152 end if;
1153 close csr_access;
1154
1155 if l_record_present = 'N' then -- create a record
1156 insert into per_assignment_extra_info(
1157 assignment_extra_info_id,
1158 assignment_id,
1159 aei_information1,
1160 aei_information2,
1161 information_type,
1162 aei_information_category)
1163 values(
1164 per_assignment_extra_info_s.nextval,
1165 P_ASSIGNMENT_ID,
1166 P_TARGET_YEAR,
1167 'Y',
1168 'KR_YEA_ENTRY_UPDATE',
1169 'KR_YEA_ENTRY_UPDATE');
1170 P_RESULT := 'Y';
1171 else -- update the record
1172 if l_current_access = 'N' then
1173 l_future_access := 'Y';
1174 else
1175 l_future_access := 'N';
1176 end if;
1177
1178 update per_assignment_extra_info
1179 set aei_information2 = l_future_access
1180 where assignment_extra_info_id = l_info_id;
1181
1182 P_RESULT := l_future_access;
1183 end if;
1184 commit;
1185
1186 exception
1187 when others then rollback;
1188 raise;
1189 end change_access;
1190 -----------------------------------------------------------------------------------
1191 -- This procedure is used to fetch all the balance values required.
1192 -- Bug 5372366: Updated to fetch balances P_HI_PREM_EE and P_EI_PREM
1193 -----------------------------------------------------------------------------------
1194 procedure get_balances(P_ASSIGNMENT_ID in varchar2,
1195 P_TARGET_YEAR in varchar2,
1196 P_EFFECTIVE_DATE in varchar2,
1197 P_ITAX out nocopy varchar2,
1198 P_STAX out nocopy varchar2,
1199 P_RTAX out nocopy varchar2,
1200 P_TAXABLE out nocopy varchar2,
1201 P_OVS_PROCESSED out nocopy varchar2,
1202 P_TOTAL_TAXABLE_KRW out nocopy varchar2,
1203 P_HI_PREM_EE out nocopy varchar2, -- Bug 5372366
1204 P_EI_PREM out nocopy varchar2, -- Bug 5372366
1205 P_NP_PREM_EE out nocopy varchar2, -- Bug 5185309
1206 P_PEN_PREM_BAL out nocopy varchar2, -- Bug 6024342
1207 P_LTCI_PREM_EE out nocopy varchar2) -- Bug 7260606
1208 is
1209 --
1210 eff_date date ;
1211 l_def_bal_id pay_defined_balances.defined_balance_id%type ;
1212 --
1213 begin
1214 eff_date := fnd_date.canonical_to_date(P_EFFECTIVE_DATE);
1215 --
1216 P_ITAX := get_total_itax(P_ASSIGNMENT_ID, eff_date);
1217 P_STAX := get_total_stax(P_ASSIGNMENT_ID, eff_date);
1218 P_RTAX := get_total_rtax(P_ASSIGNMENT_ID, eff_date);
1219 P_TAXABLE := get_total_taxable(P_ASSIGNMENT_ID, eff_date);
1220 P_OVS_PROCESSED := get_ovs_processed(P_ASSIGNMENT_ID, eff_date);
1221 --
1222 open csr_get_def_bal_id_ytd('HI_PREM_EE') ;
1223 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1224 if csr_get_def_bal_id_ytd%found then
1225 p_hi_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1226 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1227 end if ;
1228 close csr_get_def_bal_id_ytd ;
1229 --
1230 -- Bug 7260606
1231 open csr_get_def_bal_id_ytd('LTCI_PREM_EE') ;
1232 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1233 if csr_get_def_bal_id_ytd%found then
1234 p_ltci_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1235 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1236 end if ;
1237 close csr_get_def_bal_id_ytd ;
1238 -- End of Bug 7260606
1239 --
1240 open csr_get_def_bal_id_ytd('EI_PREM') ;
1241 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1242 if csr_get_def_bal_id_ytd%found then
1243 p_ei_prem := pay_balance_pkg.get_value(l_def_bal_id,
1244 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1245 end if ;
1246 close csr_get_def_bal_id_ytd ;
1247 --
1248 open csr_get_def_bal_id_ytd('NP_PREM_EE') ;
1249 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1250 if csr_get_def_bal_id_ytd%found then
1251 p_np_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1252 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1253 end if ;
1254 close csr_get_def_bal_id_ytd ;
1255 --
1256 -- Bug 6024342
1257 open csr_get_def_bal_id_ytd('Pension Premium') ;
1258 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1259 if csr_get_def_bal_id_ytd%found then
1260 p_pen_prem_bal := pay_balance_pkg.get_value(l_def_bal_id,
1261 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1262 end if ;
1263 close csr_get_def_bal_id_ytd ;
1264 --
1265 end get_balances;
1266 -----------------------------------------------------------------------------------
1267 -- This procedure delete the YEA Data for the target year of an assignment.
1268 -----------------------------------------------------------------------------------
1269 procedure delete_all_records(
1270 P_ASSIGNMENT_ID in varchar2,
1271 P_TARGET_YEAR in varchar2)
1272 is
1273 begin
1274 delete from per_assignment_extra_info
1275 where assignment_id = p_assignment_id
1276 and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
1277 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
1278 ,'KR_YEA_SP_TAX_EXEM_INFO2'
1279 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
1280 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
1281 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
1282 ,'KR_YEA_PREV_ER_INFO'
1283 ,'KR_YEA_TAX_BREAK_INFO'
1284 ,'KR_YEA_TAX_EXEM_INFO'
1285 ,'KR_YEA_OVS_TAX_BREAK_INFO'
1286 ,'KR_YEA_DETAIL_DONATION_INFO' --3506170
1287 ,'KR_YEA_EMP_EXPENSE_DETAILS'); -- Bug 5372366
1288 commit;
1289 end;
1290 -----------------------------------------------------------------------------------
1291 -- This procedure gets the Dependent Information for an Employee
1292 -----------------------------------------------------------------------------------
1293 procedure get_dependent_information(
1294 P_ASSIGNMENT_ID in varchar2,
1295 P_EFFECTIVE_DATE in varchar2,
1296 P_SPOUSE_EXEM out nocopy varchar2,
1297 P_AGED_DEPENDENTS out nocopy varchar2,
1298 P_ADULT_DEPENDENTS out nocopy varchar2,
1299 P_UNDERAGED_DEPENDENTS out nocopy varchar2,
1300 P_TOTAL_DEPENDENTS out nocopy varchar2,
1301 P_TOTAL_AGEDS out nocopy varchar2,
1302 P_TOTAL_DISABLED out nocopy varchar2,
1303 P_FEMALE_EXEM out nocopy varchar2,
1304 P_TOTAL_CHILDREN out nocopy varchar2,
1305 P_TOTAL_SUPER_AGEDS out nocopy varchar2,
1306 P_NEW_BORN_ADOPTED out nocopy varchar2, -- Bug 6705170
1307 P_HOUSE_HOLDER out nocopy varchar2,
1308 P_HOUSE_HOLDER_CODE out nocopy varchar2)
1309 is
1310 l_return_code NUMBER;
1311 l_spouse_exem VARCHAR2(1);
1312 l_aged_dependents NUMBER;
1313 l_adult_dependents NUMBER;
1314 l_underaged_dpnts NUMBER;
1315 l_total_dependents NUMBER;
1316 l_total_ageds NUMBER;
1317 l_total_disableds NUMBER;
1318 l_female_exem VARCHAR2(1);
1319 l_total_children NUMBER;
1320 l_total_super_ageds NUMBER;
1321 l_new_born_adopted NUMBER; -- Bug 6705170
1322 l_total_addtl_child NUMBER; -- Bug 6784288
1323 l_nonresident_flag VARCHAR2(1);
1324
1325 CURSOR csr_get_non_resident_flag(p_assignment_id in number,
1326 p_effective_date in DATE) is
1327 select peev.screen_entry_value
1328 from pay_element_entries_f pee,
1329 pay_element_entry_values_f peev,
1330 pay_input_values_f piv,
1331 pay_element_types_f pet
1332 where pet.element_name = 'TAX'
1333 and pet.legislation_code = 'KR'
1334 and pee.entry_type = 'E'
1335 and pet.element_type_id = piv.element_type_id
1336 and piv.name = 'NON_RESIDENT_FLAG'
1337 and pee.element_entry_id = peev.element_entry_id
1338 and piv.input_value_id = peev.input_value_id
1339 and pee.assignment_id = p_assignment_id
1340 and p_effective_date between pee.effective_start_date and pee.effective_end_date
1341 and p_effective_date between peev.effective_start_date and peev.effective_end_date
1342 and p_effective_date between piv.effective_start_date and piv.effective_end_date;
1343
1344
1345 cursor csr_house_holder(p_assgn_id in number, p_eff_date in date) is
1346 select nvl(pei.PEI_INFORMATION3,'N')
1347 from PER_PEOPLE_EXTRA_INFO pei,
1348 per_assignments_f paf
1349 where paf.assignment_id = p_assgn_id
1350 and p_eff_date between paf.effective_start_date and paf.effective_end_DATE
1351 and pei.person_id = paf.person_id
1352 and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
1353
1354 begin
1355 OPEN csr_get_non_resident_flag(to_number(P_ASSIGNMENT_ID),fnd_date.canonical_to_date(P_EFFECTIVE_DATE));
1356 FETCH csr_get_non_resident_flag into l_nonresident_flag;
1357 CLOSE csr_get_non_resident_flag;
1358 --
1359
1360 p_house_holder_code := null;
1361 open csr_house_holder(p_assignment_id, fnd_date.canonical_to_date(p_effective_date));
1362 fetch csr_house_holder into p_house_holder_code;
1363 close csr_house_holder;
1364
1365 if p_house_holder_code is null then
1366 p_house_holder_code := 'N';
1367 end if;
1368
1369 p_house_holder := hr_general.decode_lookup('YES_NO', p_house_holder_code);
1370
1371 l_return_code :=
1372 pay_kr_ff_functions_pkg.get_dependent_info(
1373 p_assignment_id => to_number(P_ASSIGNMENT_ID),
1374 p_date_earned => fnd_date.canonical_to_date(P_EFFECTIVE_DATE),
1375 p_non_resident_flag => l_nonresident_flag,
1376 p_dpnt_spouse_flag => l_spouse_exem,
1377 p_num_of_aged_dpnts => l_aged_dependents,
1378 p_num_of_adult_dpnts => l_adult_dependents,
1379 p_num_of_underaged_dpnts => l_underaged_dpnts,
1380 p_num_of_dpnts => l_total_dependents,
1381 p_num_of_ageds => l_total_ageds,
1382 p_num_of_disableds => l_total_disableds,
1383 p_female_ee_flag => l_female_exem,
1384 p_num_of_children => l_total_children,
1385 p_num_of_super_ageds => l_total_super_ageds,
1386 p_num_of_new_born_adopted => l_new_born_adopted, -- Bug 6705170
1387 p_num_of_addtl_child => l_total_addtl_child); -- Bug 6784288
1388 --
1389 if l_return_code = 0 then
1390 P_SPOUSE_EXEM := hr_general.decode_lookup('YES_NO',l_spouse_exem);
1391 P_AGED_DEPENDENTS := to_char(l_aged_dependents);
1392 P_ADULT_DEPENDENTS := to_char(l_adult_dependents);
1393 P_UNDERAGED_DEPENDENTS := to_char(l_underaged_dpnts);
1394 P_TOTAL_DEPENDENTS := to_char(l_total_dependents);
1395 P_TOTAL_AGEDS := to_char(l_total_ageds);
1396 P_TOTAL_DISABLED := to_char(l_total_disableds);
1397 P_FEMALE_EXEM := hr_general.decode_lookup('YES_NO',l_female_exem);
1398 P_TOTAL_CHILDREN := to_char(l_total_children);
1399 P_TOTAL_SUPER_AGEDS := to_char(l_total_super_ageds);
1400 P_NEW_BORN_ADOPTED := to_char(l_new_born_adopted);
1401 end if;
1402 --
1403 end;
1404
1405 ------------------------------------------------------------------------------------
1406 -- This procedure updates House Holder flag. Creates Extra Info Records if necessary
1407 ------------------------------------------------------------------------------------
1408
1409 procedure update_house_holder(p_person_id in varchar2,
1410 p_house_holder_code in varchar2)
1411 is
1412
1413 cursor csr_record_exist(p_per_id in varchar2) is
1414 select person_extra_info_id, object_version_number
1415 from per_people_extra_info
1416 where person_id = p_per_id
1417 and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
1418
1419 l_information_type varchar2(100);
1420 l_pei_id number;
1421 l_ovn number;
1422
1423 begin
1424
1425 l_information_type := 'PER_KR_RELATED_YEA_INFORMATION';
1426 l_ovn := null;
1427 l_pei_id := null;
1428
1429 open csr_record_exist(p_person_id);
1430 fetch csr_record_exist into l_pei_id,l_ovn;
1431 close csr_record_exist;
1432
1433 if l_pei_id is null then -- need to create a record
1434 hr_person_extra_info_api.create_person_extra_info(
1435 p_validate => false,
1436 p_person_id => p_person_id,
1437 p_information_type => l_information_type,
1438 p_pei_information_category => l_information_type,
1439 p_pei_information1 => 'N',
1440 p_pei_information2 => 'R',
1441 p_pei_information3 => p_house_holder_code,
1442 p_person_extra_info_id => l_pei_id,
1443 p_object_version_number => l_ovn);
1444 else
1445 hr_person_extra_info_api.update_person_extra_info(
1446 p_validate => false,
1447 p_person_extra_info_id => l_pei_id,
1448 p_object_version_number => l_ovn,
1449 p_pei_information3 => p_house_holder_code);
1450
1451 end if;
1452 commit;
1453
1454 exception
1455 when others then rollback;
1456 raise;
1457 end;
1458
1459 ------------------------------------------------------------------------------------
1460 -- Bug 6849941: Credit Card Validation Checks
1461 ------------------------------------------------------------------------------------
1462 procedure enable_credit_card(
1463 p_person_id in number,
1464 p_contact_person_id in number,
1465 p_contact_relationship_id in number,
1466 p_date_earned in varchar2,
1467 p_result out nocopy varchar2)
1468 is
1469 begin
1470
1471 p_result := pay_kr_ff_functions_pkg.enable_credit_card(
1472 p_person_id,
1473 p_contact_person_id,
1474 p_contact_relationship_id,
1475 to_date(p_date_earned,'YYYY-MM-DD'));
1476
1477 end;
1478 ------------------------------------------------------------------------------------
1479
1480 end pay_kr_yea_sshr_utils_pkg;