[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.28.12020000.3 2013/01/08 12:45:00 scireddy 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 -----------------------------------------------------------------------------------
207 -- This procedure will be used to transfer the data from intermediate table
208 -- to the EIT table (PER_ASSIGNMENT_EXTRA_INFO)
209 -----------------------------------------------------------------------------------
210 procedure submit_yea_info(p_assignment_id in varchar2,
211 p_target_year in varchar2,
212 p_effective_date in varchar2, -- expects canonical
213 p_return_status out nocopy varchar2, -- S => Success, E => Error
214 p_return_message out nocopy varchar2,
215 p_failed_record out nocopy varchar2
216 )
217 is
218 l_proc_name varchar2(50);
219 l_effective_date date;
220 l_yea_data_exists varchar2(1);
221 l_yea_fwtb_data_exists varchar2(1);
222 l_don_type_data_exists varchar2(1); -- Bug 9393732
223 l_aei_id per_assignment_extra_info.assignment_extra_infO_id%type;
224 l_ovn per_assignment_extra_info.object_version_number%type;
225 l_record_name varchar2(50);
226 l_person_id number ;
227 l_session_id number ;
228 l_data_exists varchar2(1); -- Bug 14321129
229 l_assign_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type;
230 l_aei_information2 per_assignment_extra_info.aei_information2%type;
231 l_obj_ver_number per_assignment_extra_info.object_version_number%type;
232
233 --
234 -- Bug 9326153
235 --
236 cursor csr_delete
240 where assignment_id = p_assignment_id
237 is
238 select assignment_extra_info_id,object_version_number
239 from per_assignment_extra_info
241 and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
242 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
243 ,'KR_YEA_SP_TAX_EXEM_INFO2'
244 ,'KR_YEA_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
245 ,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
246 ,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
247 ,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
248 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
249 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
250 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
251 ,'KR_YEA_PREV_ER_INFO'
252 ,'KR_YEA_TAX_BREAK_INFO'
253 ,'KR_YEA_TAX_EXEM_INFO'
254 ,'KR_YEA_OVS_TAX_BREAK_INFO'
255 ,'KR_YEA_DETAIL_DONATION_INFO'
256 ,'KR_YEA_EMP_EXPENSE_DETAILS'
257 ,'KR_YEA_TAX_GROUP_INFO'
258 ,'KR_YEA_NON_TAXABLE_EARN_DETAIL'
259 ,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
260 ,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
261 ,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
262 ,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
263 ,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
264 ,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
265 --
266 -- End of Bug 9326153
267 --
268 Cursor csr_yea_data_exists
269 Is
270 Select 'Y'
271 From per_assignment_extra_info
272 Where assignment_id = p_assignment_id
273 And to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
274 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
275 ,'KR_YEA_SP_TAX_EXEM_INFO2'
276 ,'KR_YEA_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
277 ,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
278 ,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
279 ,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
280 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
281 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
282 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
283 ,'KR_YEA_PREV_ER_INFO'
284 ,'KR_YEA_TAX_BREAK_INFO'
285 ,'KR_YEA_TAX_EXEM_INFO'
286 ,'KR_YEA_OVS_TAX_BREAK_INFO'
287 ,'KR_YEA_DETAIL_DONATION_INFO' -- Bug 3506170
288 ,'KR_YEA_EMP_EXPENSE_DETAILS' -- Bug 5372366
289 ,'KR_YEA_TAX_GROUP_INFO' -- Bug 7361372
290 ,'KR_YEA_NON_TAXABLE_EARN_DETAIL' -- Bug 8880376
291 ,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
292 ,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
293 ,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
294 ,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
295 ,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
296 ,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
297
298 Cursor csr_yea_fwtb_data_exists
299 Is
300 Select 'Y'
301 From per_assignment_extra_info
302 Where assignment_id = p_assignment_id
303 And information_type ='KR_YEA_FW_TAX_BREAK_INFO';
304
305 --
306 Cursor csr_kr_assignment_yea_info
307 Is
308 select info.assignment_id,
309 info.information_type,
310 info.ayi_information1,
311 info.ayi_information2,
312 info.ayi_information3,
313 info.ayi_information4,
314 info.ayi_information5,
315 info.ayi_information6,
316 info.ayi_information7,
317 info.ayi_information8,
318 info.ayi_information9,
319 info.ayi_information10,
320 info.ayi_information11,
321 info.ayi_information12,
322 info.ayi_information13,
323 info.ayi_information14,
324 info.ayi_information15,
325 info.ayi_information16,
326 info.ayi_information17,
327 info.ayi_information18,
328 info.ayi_information19,
329 info.ayi_information20,
330 info.ayi_information21,
331 info.ayi_information22,
332 info.ayi_information23,
333 info.ayi_information24,
334 info.ayi_information25,
335 info.ayi_information26,
336 info.ayi_information27,
337 info.ayi_information28,
338 info.ayi_information29,
339 info.ayi_information30
340 from per_kr_assignment_yea_info info
341 where assignment_id = p_assignment_id
342 and target_year = p_target_year
343 and information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
344 ,'KR_YEA_SP_TAX_EXEM_INFO2'
345 ,'KR_YEA_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
346 ,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
347 ,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
351 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
348 ,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
349 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
350 ,'KR_YEA_DETAIL_DONATION_INFO'
352 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
353 ,'KR_YEA_TAX_BREAK_INFO'
354 ,'KR_YEA_OVS_TAX_BREAK_INFO'
355 ,'KR_YEA_EMP_EXPENSE_DETAILS' -- Bug 5372366
356 ,'KR_YEA_TAX_GROUP_INFO' -- Bug 7361372
357 ,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
358 ,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
359 ,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
360 ,'KR_YEA_LT_STOCK_SAVING_DETAILS') /* Bug 9737699 */
361 Union All
362 select info.assignment_id,
363 info.information_type,
364 info.ayi_information1,
365 info.ayi_information2,
366 info.ayi_information3,
367 info.ayi_information4,
368 info.ayi_information5,
369 info.ayi_information6,
370 info.ayi_information7,
371 info.ayi_information8,
372 info.ayi_information9,
373 info.ayi_information10,
374 info.ayi_information11,
375 info.ayi_information12,
376 info.ayi_information13,
377 info.ayi_information14,
378 info.ayi_information15,
379 info.ayi_information16,
380 info.ayi_information17,
381 info.ayi_information18,
382 info.ayi_information19,
383 info.ayi_information20,
384 info.ayi_information21,
385 info.ayi_information22,
386 info.ayi_information23,
387 info.ayi_information24,
388 info.ayi_information25,
389 info.ayi_information26,
390 info.ayi_information27,
391 info.ayi_information28,
392 info.ayi_information29,
393 info.ayi_information30
394 from per_kr_assignment_yea_info info
395 where assignment_id = p_assignment_id
396 and information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
397
398 Cursor csr_kr_assignment_yea_info3
399 Is
400 select info.assignment_yea_info_id,
401 info.assignment_id,
402 info.information_type,
403 info.ayi_information1,
404 info.ayi_information2,
405 info.ayi_information3,
406 info.ayi_information4,
407 info.ayi_information5,
408 info.ayi_information6,
409 info.ayi_information7,
410 info.ayi_information8,
411 info.ayi_information9,
412 info.ayi_information10,
413 info.ayi_information11,
414 info.ayi_information12,
415 info.ayi_information13,
416 info.ayi_information14,
417 info.ayi_information15,
418 info.ayi_information16,
419 info.ayi_information17,
420 info.ayi_information18,
421 info.ayi_information19,
422 info.ayi_information20,
423 info.ayi_information21,
424 info.ayi_information22,
425 info.ayi_information23,
426 info.ayi_information24,
427 info.ayi_information25,
428 info.ayi_information26,
429 info.ayi_information27,
430 info.ayi_information28,
431 info.ayi_information29,
432 info.ayi_information30
433 from per_kr_assignment_yea_info info
434 where assignment_id = p_assignment_id
435 and target_year = p_target_year
436 and information_type = 'KR_YEA_NON_TAXABLE_EARN_DETAIL';
437
438 -- Bug 9737699
439 --
440 Cursor csr_kr_assignment_yea_info4
441 Is
442 select info.assignment_yea_info_id,
443 info.assignment_id,
444 info.information_type,
445 info.ayi_information1,
446 info.ayi_information2,
447 info.ayi_information3,
448 info.ayi_information4,
449 info.ayi_information5,
450 info.ayi_information6,
451 info.ayi_information7,
452 info.ayi_information8,
453 info.ayi_information9,
454 info.ayi_information10,
455 info.ayi_information11,
456 info.ayi_information12,
457 info.ayi_information13,
458 info.ayi_information14,
459 info.ayi_information15,
460 info.ayi_information16,
461 info.ayi_information17,
462 info.ayi_information18,
463 info.ayi_information19,
464 info.ayi_information20,
465 info.ayi_information21,
466 info.ayi_information22,
467 info.ayi_information23,
468 info.ayi_information24,
469 info.ayi_information25,
470 info.ayi_information26,
471 info.ayi_information27,
472 info.ayi_information28,
473 info.ayi_information29,
474 info.ayi_information30,
475 info.ayi_information31,
476 info.ayi_information32,
477 info.ayi_information33
478 from per_kr_assignment_yea_info info
479 where assignment_id = p_assignment_id
480 and target_year = p_target_year
481 and information_type = 'KR_YEA_PREV_ER_INFO';
482
483 Cursor csr_kr_assignment_yea_info5
487 info.ayi_information1,
484 Is
485 select info.assignment_id,
486 info.information_type,
488 info.ayi_information2,
489 info.ayi_information3,
490 info.ayi_information4,
491 info.ayi_information5,
492 info.ayi_information6,
493 info.ayi_information7,
494 info.ayi_information8,
495 info.ayi_information9,
496 info.ayi_information10,
497 info.ayi_information11,
498 info.ayi_information12,
499 info.ayi_information13,
500 info.ayi_information14,
501 info.ayi_information15,
502 info.ayi_information16,
503 info.ayi_information17,
504 info.ayi_information18,
505 info.ayi_information19,
506 info.ayi_information20,
507 info.ayi_information21,
508 info.ayi_information22,
509 info.ayi_information23,
510 info.ayi_information24,
511 info.ayi_information25,
512 info.ayi_information26,
513 info.ayi_information27,
514 info.ayi_information28,
515 info.ayi_information29,
516 info.ayi_information30,
517 info.ayi_information31
518 from per_kr_assignment_yea_info info
519 where assignment_id = p_assignment_id
520 and target_year = p_target_year
521 and information_type = 'KR_YEA_TAX_EXEM_INFO';
522
523 -- BUG 14321129 Start
524 Cursor csr_data_exists
525 Is
526 Select assignment_extra_info_id,
527 aei_information2,
528 object_version_number
529 From per_assignment_extra_info
530 Where assignment_id = p_assignment_id
531 And to_char(fnd_date.canonical_to_date(aei_information1), 'yyyy') = p_target_year
532 And information_type = 'KR_YEA_TAX_EXEM_INFO2';
533
534 Cursor csr_kr_assignment_yea_info6
535 Is
536 select info.assignment_id,
537 info.information_type,
538 info.ayi_information1,
539 info.ayi_information2,
540 info.ayi_information3,
541 info.ayi_information4,
542 info.ayi_information5,
543 info.ayi_information6,
544 info.ayi_information7,
545 info.ayi_information8,
546 info.ayi_information9,
547 info.ayi_information10,
548 info.ayi_information11,
549 info.ayi_information12
550 from per_kr_assignment_yea_info info
551 where assignment_id = p_assignment_id
552 and target_year = p_target_year
553 and information_type = 'KR_YEA_TAX_EXEM_INFO2';
554 -- BUG 14321129 End
555
556 l_prev_asgInfoId number;
557 --
558 -- End of bug 9737699
559
560 l_asgInfoId number;
561 --
562 -- Bug 4915940
563 cursor csr_person_id(p_eff_date in date) is
564 select person_id
565 from per_assignments_f
566 where assignment_id = p_assignment_id
567 and p_eff_date between effective_start_date and effective_end_date ;
568 --
569 cursor csr_get_session_id is
570 select session_id
571 from fnd_sessions
572 where session_id = userenv('sessionid');
573
574 -- End of 4915940
575 --
576 begin
577 --
578 l_proc_name := 'pay_kr_yea_sshr_utils_pkg.submit_yea_info';
579 p_return_status := 'S';
580 --
581 --
582 if g_debug then
583 hr_utility.set_location(l_proc_name, 10);
584 end if;
585 --
586 --
587 if p_effective_date is null then
588 l_effective_date := to_date('31/12/'||to_char(p_target_year), 'DD/MM/YYYY');
589 else
590 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
591 end if;
592 --
593 -- Bug 4915940: Setting profiles PER_PERSON_ID and PER_ASSIGNMENT_ID
594
595 -- Insert a row into fnd_sessions if reqd
596 open csr_get_session_id;
597 fetch csr_get_session_id into l_session_id;
598 if csr_get_session_id%notfound then
599 insert into fnd_sessions(session_id,effective_date)
600 values (userenv('sessionid'),l_effective_date);
601 end if;
602 close csr_get_session_id;
603
604 open csr_person_id(l_effective_date) ;
605 fetch csr_person_id into l_person_id ;
606 close csr_person_id ;
607 --
608 fnd_profile.put('PER_ASSIGNMENT_ID', p_assignment_id ) ;
609 fnd_profile.put('PER_PERSON_ID', to_char(l_person_id) ) ;
610 -- End of 4915940
611 --
612 -- check if data has already been entered
613 --
614 open csr_yea_data_exists;
615 fetch csr_yea_data_exists into l_yea_data_exists;
616 if csr_yea_data_exists%notfound then
617 l_yea_data_exists := 'N';
618 end if;
619 close csr_yea_data_exists;
620
621 open csr_yea_fwtb_data_exists;
622 fetch csr_yea_fwtb_data_exists into l_yea_fwtb_data_exists;
623 if csr_yea_fwtb_data_exists%notfound then
624 l_yea_fwtb_data_exists := 'N';
625 end if;
626 close csr_yea_fwtb_data_exists;
627
628 --
629 -- Bug 9326153
630 --
631 for i in csr_delete loop
632 --
633 hr_assignment_extra_info_api.delete_assignment_extra_info(
637 end loop;
634 p_validate => false,
635 p_assignment_extra_info_id => i.assignment_extra_info_id,
636 p_object_version_number => i.object_version_number);
638 -- End of Bug 9326153
639 --
640 delete from per_assignment_extra_info
641 where assignment_id = p_assignment_id
642 And information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
643 --
644 --
645 for rec in csr_kr_assignment_yea_info loop
646 --
647 l_record_name := rec.information_type;
648 -- skip any record which is empty
649 if(NOT (rec.ayi_information1 is null and
650 rec.ayi_information2 is null and
651 rec.ayi_information3 is null and
652 rec.ayi_information4 is null and
653 rec.ayi_information5 is null and
654 rec.ayi_information6 is null and
655 rec.ayi_information7 is null and
656 rec.ayi_information8 is null and
657 rec.ayi_information9 is null and
658 rec.ayi_information10 is null and
659 rec.ayi_information11 is null and
660 rec.ayi_information12 is null and
661 rec.ayi_information13 is null and
662 rec.ayi_information14 is null and
663 rec.ayi_information15 is null and
664 rec.ayi_information16 is null and
665 rec.ayi_information17 is null and
666 rec.ayi_information18 is null and
667 rec.ayi_information19 is null and
668 rec.ayi_information20 is null and
669 rec.ayi_information21 is null and
670 rec.ayi_information22 is null and
671 rec.ayi_information23 is null and
672 rec.ayi_information24 is null and
673 rec.ayi_information25 is null and
674 rec.ayi_information26 is null and
675 rec.ayi_information27 is null and
676 rec.ayi_information28 is null and
677 rec.ayi_information29 is null and
678 rec.ayi_information30 is null )) then
679
680 hr_assignment_extra_info_api.create_assignment_extra_info(
681 p_validate => false,
682 p_assignment_id => rec.assignment_id,
683 p_information_type => rec.information_type,
684 p_aei_information_category => rec.information_type,
685 p_aei_information1 => rec.ayi_information1,
686 p_aei_information2 => rec.ayi_information2,
687 p_aei_information3 => rec.ayi_information3,
688 p_aei_information4 => rec.ayi_information4,
689 p_aei_information5 => rec.ayi_information5,
690 p_aei_information6 => rec.ayi_information6,
691 p_aei_information7 => rec.ayi_information7,
692 p_aei_information8 => rec.ayi_information8,
693 p_aei_information9 => rec.ayi_information9,
694 p_aei_information10 => rec.ayi_information10,
695 p_aei_information11 => rec.ayi_information11,
696 p_aei_information12 => rec.ayi_information12,
697 p_aei_information13 => rec.ayi_information13,
698 p_aei_information14 => rec.ayi_information14,
699 p_aei_information15 => rec.ayi_information15,
700 p_aei_information16 => rec.ayi_information16,
701 p_aei_information17 => rec.ayi_information17,
702 p_aei_information18 => rec.ayi_information18,
703 p_aei_information19 => rec.ayi_information19,
704 p_aei_information20 => rec.ayi_information20,
705 p_aei_information21 => rec.ayi_information21,
706 p_aei_information22 => rec.ayi_information22,
707 p_aei_information23 => rec.ayi_information23,
708 p_aei_information24 => rec.ayi_information24,
709 p_aei_information25 => rec.ayi_information25,
710 p_aei_information26 => rec.ayi_information26,
711 p_aei_information27 => rec.ayi_information27,
712 p_aei_information28 => rec.ayi_information28,
713 p_aei_information29 => rec.ayi_information29,
714 p_aei_information30 => rec.ayi_information30,
715 p_assignment_extra_info_id => l_aei_id,
716 p_object_version_number => l_ovn);
717 end if;
718 end loop;
719
720 -- Bug 9737699
721 for rec4 in csr_kr_assignment_yea_info4 loop
722 --
723 l_prev_asgInfoId := null;
724 l_record_name := rec4.information_type;
725 -- skip any record which is empty
726 if(NOT (rec4.ayi_information1 is null and
727 rec4.ayi_information2 is null and
728 rec4.ayi_information3 is null and
729 rec4.ayi_information4 is null and
730 rec4.ayi_information5 is null and
731 rec4.ayi_information6 is null and
732 rec4.ayi_information7 is null and
733 rec4.ayi_information8 is null and
734 rec4.ayi_information9 is null and
735 rec4.ayi_information10 is null and
736 rec4.ayi_information11 is null and
737 rec4.ayi_information12 is null and
738 rec4.ayi_information13 is null and
739 rec4.ayi_information14 is null and
740 rec4.ayi_information15 is null and
741 rec4.ayi_information16 is null and
742 rec4.ayi_information17 is null and
743 rec4.ayi_information18 is null and
747 rec4.ayi_information22 is null and
744 rec4.ayi_information19 is null and
745 rec4.ayi_information20 is null and
746 rec4.ayi_information21 is null and
748 rec4.ayi_information23 is null and
749 rec4.ayi_information24 is null and
750 rec4.ayi_information25 is null and
751 rec4.ayi_information26 is null and
752 rec4.ayi_information27 is null and
753 rec4.ayi_information28 is null and
754 rec4.ayi_information29 is null and
755 rec4.ayi_information30 is null )) then
756
757 hr_assignment_extra_info_api.create_assignment_extra_info(
758 p_validate => false,
759 p_assignment_id => rec4.assignment_id,
760 p_information_type => rec4.information_type,
761 p_aei_information_category => rec4.information_type,
762 p_aei_information1 => rec4.ayi_information1,
763 p_aei_information2 => rec4.ayi_information2,
764 p_aei_information3 => rec4.ayi_information3,
765 p_aei_information4 => rec4.ayi_information4,
766 p_aei_information5 => rec4.ayi_information5,
767 p_aei_information6 => rec4.ayi_information6,
768 p_aei_information7 => rec4.ayi_information7,
769 p_aei_information8 => rec4.ayi_information8,
770 p_aei_information9 => rec4.ayi_information9,
771 p_aei_information10 => rec4.ayi_information10,
772 p_aei_information11 => rec4.ayi_information11,
773 p_aei_information12 => rec4.ayi_information12,
774 p_aei_information13 => rec4.ayi_information13,
775 p_aei_information14 => rec4.ayi_information14,
776 p_aei_information15 => rec4.ayi_information15,
777 p_aei_information16 => rec4.ayi_information16,
778 p_aei_information17 => rec4.ayi_information17,
779 p_aei_information18 => rec4.ayi_information18,
780 p_aei_information19 => rec4.ayi_information19,
781 p_aei_information20 => rec4.ayi_information20,
782 p_aei_information21 => rec4.ayi_information21,
783 p_aei_information22 => rec4.ayi_information22,
784 p_aei_information23 => rec4.ayi_information23,
785 p_aei_information24 => rec4.ayi_information24,
786 p_aei_information25 => rec4.ayi_information25,
787 p_aei_information26 => rec4.ayi_information26,
788 p_aei_information27 => rec4.ayi_information27,
789 p_aei_information28 => rec4.ayi_information28,
790 p_aei_information29 => rec4.ayi_information29,
791 p_aei_information30 => rec4.ayi_information30,
792 p_assignment_extra_info_id => l_prev_asgInfoId,
793 p_object_version_number => l_ovn);
794 end if;
795
796 --
797 l_record_name := 'KR_YEA_PREV_ER_INFO2';
798 -- skip any record which is empty
799 if(NOT (rec4.ayi_information31 is null and
800 rec4.ayi_information32 is null and
801 rec4.ayi_information33 is null)) then
802
803 hr_assignment_extra_info_api.create_assignment_extra_info(
804 p_validate => false,
805 p_assignment_id => rec4.assignment_id,
806 p_information_type => 'KR_YEA_PREV_ER_INFO2',
807 p_aei_information_category => 'KR_YEA_PREV_ER_INFO2',
808 p_aei_information1 => rec4.ayi_information1,
809 p_aei_information2 => l_prev_asgInfoId,
810 p_aei_information3 => rec4.ayi_information3,
811 p_aei_information4 => rec4.ayi_information31,
812 p_aei_information5 => rec4.ayi_information32,
813 p_aei_information6 => rec4.ayi_information33,
814 p_aei_information7 => null,
815 p_aei_information8 => null,
816 p_aei_information9 => null,
817 p_aei_information10 => null,
818 p_aei_information11 => null,
819 p_aei_information12 => null,
820 p_aei_information13 => null,
821 p_aei_information14 => null,
822 p_aei_information15 => null,
823 p_aei_information16 => null,
824 p_aei_information17 => null,
825 p_aei_information18 => null,
826 p_aei_information19 => null,
827 p_aei_information20 => null,
828 p_aei_information21 => null,
829 p_aei_information22 => null,
830 p_aei_information23 => null,
831 p_aei_information24 => null,
832 p_aei_information25 => null,
833 p_aei_information26 => null,
834 p_aei_information27 => null,
835 p_aei_information28 => null,
836 p_aei_information29 => null,
837 p_aei_information30 => null,
838 p_assignment_extra_info_id => l_aei_id,
839 p_object_version_number => l_ovn);
840 end if;
841
842 end loop;
843 --
844 -- Bug 9737699
845 for rec5 in csr_kr_assignment_yea_info5 loop
849 if(NOT (rec5.ayi_information1 is null and
846 --
847 l_record_name := rec5.information_type;
848 -- skip any record which is empty
850 rec5.ayi_information2 is null and
851 rec5.ayi_information3 is null and
852 rec5.ayi_information4 is null and
853 rec5.ayi_information5 is null and
854 rec5.ayi_information6 is null and
855 rec5.ayi_information7 is null and
856 rec5.ayi_information8 is null and
857 rec5.ayi_information9 is null and
858 rec5.ayi_information10 is null and
859 rec5.ayi_information11 is null and
860 rec5.ayi_information12 is null and
861 rec5.ayi_information13 is null and
862 rec5.ayi_information14 is null and
863 rec5.ayi_information15 is null and
864 rec5.ayi_information16 is null and
865 rec5.ayi_information17 is null and
866 rec5.ayi_information18 is null and
867 rec5.ayi_information19 is null and
868 rec5.ayi_information20 is null and
869 rec5.ayi_information21 is null and
870 rec5.ayi_information22 is null and
871 rec5.ayi_information23 is null and
872 rec5.ayi_information24 is null and
873 rec5.ayi_information25 is null and
874 rec5.ayi_information26 is null and
875 rec5.ayi_information27 is null and
876 rec5.ayi_information28 is null and
877 rec5.ayi_information29 is null and
878 rec5.ayi_information30 is null )) then
879
880 hr_assignment_extra_info_api.create_assignment_extra_info(
881 p_validate => false,
882 p_assignment_id => rec5.assignment_id,
883 p_information_type => rec5.information_type,
884 p_aei_information_category => rec5.information_type,
885 p_aei_information1 => rec5.ayi_information1,
886 p_aei_information2 => rec5.ayi_information2,
887 p_aei_information3 => rec5.ayi_information3,
888 p_aei_information4 => rec5.ayi_information4,
889 p_aei_information5 => rec5.ayi_information5,
890 p_aei_information6 => rec5.ayi_information6,
891 p_aei_information7 => rec5.ayi_information7,
892 p_aei_information8 => rec5.ayi_information8,
893 p_aei_information9 => rec5.ayi_information9,
894 p_aei_information10 => rec5.ayi_information10,
895 p_aei_information11 => rec5.ayi_information11,
896 p_aei_information12 => rec5.ayi_information12,
897 p_aei_information13 => rec5.ayi_information13,
898 p_aei_information14 => rec5.ayi_information14,
899 p_aei_information15 => rec5.ayi_information15,
900 p_aei_information16 => rec5.ayi_information16,
901 p_aei_information17 => rec5.ayi_information17,
902 p_aei_information18 => rec5.ayi_information18,
903 p_aei_information19 => rec5.ayi_information19,
904 p_aei_information20 => rec5.ayi_information20,
905 p_aei_information21 => rec5.ayi_information21,
906 p_aei_information22 => rec5.ayi_information22,
907 p_aei_information23 => rec5.ayi_information23,
908 p_aei_information24 => rec5.ayi_information24,
909 p_aei_information25 => rec5.ayi_information25,
910 p_aei_information26 => rec5.ayi_information26,
911 p_aei_information27 => rec5.ayi_information27,
912 p_aei_information28 => rec5.ayi_information28,
913 p_aei_information29 => rec5.ayi_information29,
914 p_aei_information30 => rec5.ayi_information30,
915 p_assignment_extra_info_id => l_aei_id,
916 p_object_version_number => l_ovn);
917 end if;
918
919 --
920 l_record_name := 'KR_YEA_TAX_EXEM_INFO2';
921 -- skip any record which is empty
922 if(NOT (rec5.ayi_information31 is null)) then
923
924 hr_assignment_extra_info_api.create_assignment_extra_info(
925 p_validate => false,
926 p_assignment_id => rec5.assignment_id,
927 p_information_type => 'KR_YEA_TAX_EXEM_INFO2',
928 p_aei_information_category => 'KR_YEA_TAX_EXEM_INFO2',
929 p_aei_information1 => rec5.ayi_information1,
930 p_aei_information2 => rec5.ayi_information31,
931 p_aei_information3 => null,
932 p_aei_information4 => null,
933 p_aei_information5 => null,
934 p_aei_information6 => null,
935 p_aei_information7 => null,
936 p_aei_information8 => null,
937 p_aei_information9 => null,
938 p_aei_information10 => null,
939 p_aei_information11 => null,
940 p_aei_information12 => null,
941 p_aei_information13 => null,
942 p_aei_information14 => null,
943 p_aei_information15 => null,
944 p_aei_information16 => null,
945 p_aei_information17 => null,
946 p_aei_information18 => null,
947 p_aei_information19 => null,
948 p_aei_information20 => null,
952 p_aei_information24 => null,
949 p_aei_information21 => null,
950 p_aei_information22 => null,
951 p_aei_information23 => null,
953 p_aei_information25 => null,
954 p_aei_information26 => null,
955 p_aei_information27 => null,
956 p_aei_information28 => null,
957 p_aei_information29 => null,
958 p_aei_information30 => null,
959 p_assignment_extra_info_id => l_aei_id,
960 p_object_version_number => l_ovn);
961 end if;
962
963 end loop;
964 --
965 -- End of Bug 9737699
966
967 -- BUg 14321129 Start
968 l_assign_extra_info_id := null;
969 l_aei_information2 := null;
970 open csr_data_exists;
971 fetch csr_data_exists into
972 l_assign_extra_info_id,
973 l_aei_information2,
974 l_obj_ver_number;
975 close csr_data_exists;
976
977 if l_assign_extra_info_id is not null then
978 for rec6 in csr_kr_assignment_yea_info6 loop
979 l_record_name := rec6.information_type;
980 -- skip any record which is empty
981 if(NOT (rec6.ayi_information1 is null and
982 rec6.ayi_information2 is null and
983 rec6.ayi_information3 is null and
984 rec6.ayi_information4 is null and
985 rec6.ayi_information5 is null and
986 rec6.ayi_information6 is null and
987 rec6.ayi_information7 is null and
988 rec6.ayi_information8 is null and
989 rec6.ayi_information9 is null and
990 rec6.ayi_information10 is null and
991 rec6.ayi_information11 is null )) then
992 hr_assignment_extra_info_api.update_assignment_extra_info(
993 p_validate => false,
994 p_assignment_extra_info_id => l_assign_extra_info_id,
995 p_object_version_number => l_obj_ver_number,
996 p_aei_information_category => 'KR_YEA_TAX_EXEM_INFO2',
997 p_aei_information1 => rec6.ayi_information1,
998 p_aei_information2 => l_aei_information2,
999 p_aei_information3 => rec6.ayi_information3,
1000 p_aei_information4 => rec6.ayi_information4,
1001 p_aei_information5 => rec6.ayi_information5,
1002 p_aei_information6 => rec6.ayi_information6,
1003 p_aei_information7 => rec6.ayi_information7,
1004 p_aei_information8 => rec6.ayi_information8,
1005 p_aei_information9 => rec6.ayi_information9,
1006 p_aei_information10 => rec6.ayi_information10,
1007 p_aei_information11 => rec6.ayi_information11,
1008 p_aei_information12 => rec6.ayi_information12,
1009 p_aei_information13 => null,
1010 p_aei_information14 => null,
1011 p_aei_information15 => null,
1012 p_aei_information16 => null,
1013 p_aei_information17 => null,
1014 p_aei_information18 => null,
1015 p_aei_information19 => null,
1016 p_aei_information20 => null,
1017 p_aei_information21 => null,
1018 p_aei_information22 => null,
1019 p_aei_information23 => null,
1020 p_aei_information24 => null,
1021 p_aei_information25 => null,
1022 p_aei_information26 => null,
1023 p_aei_information27 => null,
1024 p_aei_information28 => null,
1025 p_aei_information29 => null,
1026 p_aei_information30 => null);
1027 end if;
1028 end loop;
1029 else
1030 for rec6 in csr_kr_assignment_yea_info6 loop
1031 l_record_name := rec6.information_type;
1032 -- skip any record which is empty
1033 if(NOT (rec6.ayi_information1 is null and
1034 rec6.ayi_information2 is null and
1035 rec6.ayi_information3 is null and
1036 rec6.ayi_information4 is null and
1037 rec6.ayi_information5 is null and
1038 rec6.ayi_information6 is null and
1039 rec6.ayi_information7 is null and
1040 rec6.ayi_information8 is null and
1041 rec6.ayi_information9 is null and
1042 rec6.ayi_information10 is null and
1043 rec6.ayi_information11 is null )) then
1044 hr_assignment_extra_info_api.create_assignment_extra_info(
1045 p_validate => false,
1046 p_assignment_id => rec6.assignment_id,
1047 p_information_type => rec6.information_type,
1048 p_aei_information_category => rec6.information_type,
1049 p_aei_information1 => rec6.ayi_information1,
1050 p_aei_information2 => null,
1051 p_aei_information3 => rec6.ayi_information2,
1052 p_aei_information4 => rec6.ayi_information3,
1053 p_aei_information5 => rec6.ayi_information4,
1054 p_aei_information6 => rec6.ayi_information5,
1055 p_aei_information7 => rec6.ayi_information6,
1056 p_aei_information8 => rec6.ayi_information7,
1057 p_aei_information9 => rec6.ayi_information8,
1061 p_aei_information13 => null,
1058 p_aei_information10 => rec6.ayi_information9,
1059 p_aei_information11 => rec6.ayi_information10,
1060 p_aei_information12 => rec6.ayi_information11,
1062 p_aei_information14 => null,
1063 p_aei_information15 => null,
1064 p_aei_information16 => null,
1065 p_aei_information17 => null,
1066 p_aei_information18 => null,
1067 p_aei_information19 => null,
1068 p_aei_information20 => null,
1069 p_aei_information21 => null,
1070 p_aei_information22 => null,
1071 p_aei_information23 => null,
1072 p_aei_information24 => null,
1073 p_aei_information25 => null,
1074 p_aei_information26 => null,
1075 p_aei_information27 => null,
1076 p_aei_information28 => null,
1077 p_aei_information29 => null,
1078 p_aei_information30 => null,
1079 p_assignment_extra_info_id => l_aei_id,
1080 p_object_version_number => l_ovn);
1081 end if;
1082 end loop;
1083 end if;
1084 -- Bug 14321129 END
1085 --
1086 for rec3 in csr_kr_assignment_yea_info3 loop
1087 -- Bug 8880376
1088 l_record_name := rec3.information_type;
1089 -- skip any record which is empty
1090 if(NOT (rec3.ayi_information1 is null and
1091 rec3.ayi_information2 is null and
1092 rec3.ayi_information3 is null and
1093 rec3.ayi_information4 is null and
1094 rec3.ayi_information5 is null and
1095 rec3.ayi_information6 is null and
1096 rec3.ayi_information7 is null and
1097 rec3.ayi_information8 is null and
1098 rec3.ayi_information9 is null and
1099 rec3.ayi_information10 is null and
1100 rec3.ayi_information11 is null and
1101 rec3.ayi_information12 is null and
1102 rec3.ayi_information13 is null and
1103 rec3.ayi_information14 is null and
1104 rec3.ayi_information15 is null and
1105 rec3.ayi_information16 is null and
1106 rec3.ayi_information17 is null and
1107 rec3.ayi_information18 is null and
1108 rec3.ayi_information19 is null and
1109 rec3.ayi_information20 is null and
1110 rec3.ayi_information21 is null and
1111 rec3.ayi_information22 is null and
1112 rec3.ayi_information23 is null and
1113 rec3.ayi_information24 is null and
1114 rec3.ayi_information25 is null and
1115 rec3.ayi_information26 is null and
1116 rec3.ayi_information27 is null and
1117 rec3.ayi_information28 is null and
1118 rec3.ayi_information29 is null and
1119 rec3.ayi_information30 is null )) then
1120
1121 hr_assignment_extra_info_api.create_assignment_extra_info(
1122 p_validate => false,
1123 p_assignment_id => rec3.assignment_id,
1124 p_information_type => rec3.information_type,
1125 p_aei_information_category => rec3.information_type,
1126 p_aei_information1 => rec3.ayi_information1,
1127 p_aei_information2 => rec3.ayi_information2,
1128 p_aei_information3 => rec3.ayi_information3,
1129 p_aei_information4 => rec3.ayi_information4,
1130 p_aei_information5 => rec3.ayi_information5,
1131 p_aei_information6 => rec3.ayi_information6,
1132 p_aei_information7 => rec3.ayi_information7,
1133 p_aei_information8 => rec3.ayi_information8,
1134 p_aei_information9 => rec3.ayi_information9,
1135 p_aei_information10 => rec3.ayi_information10,
1136 p_aei_information11 => rec3.ayi_information11,
1137 p_aei_information12 => rec3.ayi_information12,
1138 p_aei_information13 => rec3.ayi_information13,
1139 p_aei_information14 => rec3.ayi_information14,
1140 p_aei_information15 => rec3.ayi_information15,
1141 p_aei_information16 => rec3.ayi_information16,
1142 p_aei_information17 => rec3.ayi_information17,
1143 p_aei_information18 => rec3.ayi_information18,
1144 p_aei_information19 => rec3.ayi_information19,
1145 p_aei_information20 => rec3.ayi_information20,
1146 p_aei_information21 => rec3.ayi_information21,
1147 p_aei_information22 => rec3.ayi_information22,
1148 p_aei_information23 => rec3.ayi_information23,
1149 p_aei_information24 => rec3.ayi_information24,
1150 p_aei_information25 => rec3.ayi_information25,
1151 p_aei_information26 => rec3.ayi_information26,
1152 p_aei_information27 => rec3.ayi_information27,
1153 p_aei_information28 => rec3.ayi_information28,
1154 p_aei_information29 => rec3.ayi_information29,
1155 p_aei_information30 => rec3.ayi_information30,
1156 p_assignment_extra_info_id => l_aei_id,
1157 p_object_version_number => l_ovn);
1158 end if;
1159 end loop;
1160 --
1161 -- End of bug 8880376
1162 --
1163 commit;
1164 --
1165 exception
1166 when others then
1167 rollback;
1171 --
1168 p_failed_record := l_record_name;
1169 p_return_status := 'E';
1170 p_return_message := sqlerrm;
1172 end submit_yea_info;
1173 -----------------------------------------------------------------------------------
1174 -- Bug : 4568233
1175 -- This functions determines whether an employee is allowed to update the YEA
1176 -- information provided by him based on the effective start and end dates for the
1177 -- entry of YEA information.
1178 -----------------------------------------------------------------------------------
1179 function update_allowed(p_business_group_id in number,
1180 p_assignment_id in number,
1181 p_target_year in number,
1182 p_effective_date in date)
1183 return varchar2
1184 is
1185
1186 -- cursor to get the Update Allowed Flag of the assignment
1187 cursor csr_update_allowed_flag is
1188 select nvl(aei_information2, 'Y')
1189 from per_assignment_extra_info
1190 where information_type = 'KR_YEA_ENTRY_UPDATE'
1191 and aei_information1 = p_target_year
1192 and assignment_id = p_assignment_id;
1193
1194
1195 -- cursor to check whether YEA for target year has been run for the assignment
1196 cursor csr_yea_exist is
1197 select 'Y'
1198 from pay_payroll_actions ppa,
1199 pay_assignment_actions paa
1200 where paa.assignment_id = p_assignment_id
1201 and paa.source_action_id is null
1202 and ppa.payroll_action_id = paa.payroll_action_id
1203 and ppa.action_type = 'B'
1204 and ppa.action_status = 'C'
1205 and trunc(ppa.effective_date, 'YYYY') = trunc(p_effective_date, 'YYYY')
1206 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null) = 'YEA'
1207 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_QUALIFIER', null) = 'KR'
1208 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) IN ('N','I');
1209
1210 -- Cursor to get Org Structure version id of the Primary Hierarchy of the BG
1211 cursor csr_org_struct_version is
1212 select posv.org_structure_version_id
1213 from per_organization_structures pos,
1214 per_org_structure_versions posv
1215 where pos.primary_structure_flag = 'Y'
1216 and pos.business_group_id = p_business_group_id
1217 and pos.organization_structure_id = posv.organization_structure_id
1218 and p_effective_date between posv.date_from and nvl(posv.date_to,p_effective_date);
1219
1220 -- cursor fetches the entry periods of the organizations defined in the hierarchy
1221 -- in bottom to top order starting from the Employee's Organization.
1222 cursor csr_hierarchy_entry_period(p_version in number,
1223 p_emp_org_id in number) is
1224 select min(fnd_date.canonical_to_date(hoi.org_information2)),
1225 max(fnd_date.canonical_to_date(hoi.org_information3)),
1226 max(fnd_date.canonical_to_date(hoi.org_information4))
1227 from (select pose.organization_id_parent,
1228 level hierarchy_level,
1229 pose.organization_id_child
1230 from per_org_structure_elements pose
1231 start with pose.organization_id_child = p_emp_org_id
1232 and pose.org_structure_version_id = p_version
1233 connect by prior pose.organization_id_parent = organization_id_child
1234 ) org,
1235 hr_organization_information hoi
1236 where hoi.organization_id = org.organization_id_parent
1237 and hoi.org_information1 = p_target_year
1238 and hoi.org_information_context = 'KR_YEA_ENTRY_PERIOD_ORG'
1239 group by organization_id_child, org.hierarchy_level
1240 order by org.hierarchy_level asc;
1241
1242 -- cursor fetches the Entry Period defined in BG level
1243 cursor csr_bg_entry_period is
1244 select min(fnd_date.canonical_to_date(hoi.org_information2)),
1245 max(fnd_date.canonical_to_date(hoi.org_information3)),
1246 max(fnd_date.canonical_to_date(hoi.org_information4))
1247 from hr_organization_information hoi
1248 where hoi.organization_id = p_business_group_id
1249 and hoi.org_information1 = p_target_year
1250 and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_BG';
1251
1252 -- cursor fetches the Entry Period defined in Employee's Org level
1253 cursor csr_emp_org_entry_period (p_emp_org number) is
1254 select min(fnd_date.canonical_to_date(hoi.org_information2)),
1255 max(fnd_date.canonical_to_date(hoi.org_information3)),
1256 max(fnd_date.canonical_to_date(hoi.org_information4))
1257 from hr_organization_information hoi
1258 where hoi.organization_id = p_emp_org
1259 and hoi.org_information1 = p_target_year
1260 and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_ORG';
1261
1262 -- cursor to get organization id of the Employee.
1263 cursor csr_emp_org_id is
1264 select organization_id
1265 from per_assignments_f paf
1266 where assignment_id = p_assignment_id
1267 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
1268
1269 --
1270 l_update_allowed varchar2(1);
1271 l_asg_update_allowed varchar2(1);
1272 l_entry_start_date date;
1273 l_entry_end_date date;
1274 l_update_cut_off_date date;
1275 l_emp_org_id number;
1276 l_struct_version_id number;
1277 l_flag varchar2(1);
1278 --
1279 begin
1280
1284
1281 if g_debug then
1282 hr_utility.set_location('pay_kr_yea_sshr_utils_pkg.update_allowed', 10);
1283 end if;
1285 l_update_allowed := 'N';
1286 l_entry_start_date := null;
1287 l_entry_end_date := null;
1288
1289 -- check if YEA has been run for target year
1290 open csr_yea_exist;
1291 fetch csr_yea_exist into l_flag;
1292
1293 if g_debug then
1294 hr_utility.trace('YEA Run Exists = '||l_flag);
1295 end if;
1296
1297 if csr_yea_exist%NOTFOUND then
1298
1299 -- check if Period defined in Employee's Organization.
1300 open csr_emp_org_id;
1301 fetch csr_emp_org_id into l_emp_org_id;
1302 close csr_emp_org_id;
1303
1304 if g_debug then
1305 hr_utility.trace('fetching emp org entry period');
1306 end if;
1307
1308 open csr_emp_org_entry_period(l_emp_org_id);
1309 fetch csr_emp_org_entry_period into
1310 l_entry_start_date,
1311 l_entry_end_date,
1312 l_update_cut_off_date;
1313 close csr_emp_org_entry_period;
1314
1315 if l_entry_start_date is null then
1316 if g_debug then
1317 hr_utility.trace('Emp org entry period not found.');
1318 hr_utility.trace('Fetching Entry Period from Hierarchy.');
1319 end if;
1320
1321 -- check for periods of parent organizations in hierarchy
1322 open csr_org_struct_version;
1323 fetch csr_org_struct_version into l_struct_version_id;
1324 close csr_org_struct_version;
1325
1326 -- check if primary hierarchy is defined
1327 if l_struct_version_id is not null then
1328 if g_debug then
1329 hr_utility.trace('Struct Version ID : ' || l_struct_version_id);
1330 end if;
1331
1332 open csr_hierarchy_entry_period(l_struct_version_id,l_emp_org_id);
1333
1334 fetch csr_hierarchy_entry_period into
1335 l_entry_start_date,
1336 l_entry_end_date,
1337 l_update_cut_off_date;
1338 close csr_hierarchy_entry_period;
1339
1340 end if; -- primary Hierarchy defined
1341
1342 -- 4657745
1343 -- if entry period not found in Hierarchy, check BG Entry period
1344 if l_entry_start_date is null then
1345 if g_debug then
1346 hr_utility.trace('Entry period of Hierarchy not found.');
1347 hr_utility.trace('Fetching Entry Period from BG.');
1348 end if;
1349
1350 -- check for entry period of the BG
1351 open csr_bg_entry_period;
1352 fetch csr_bg_entry_period into
1353 l_entry_start_date,
1354 l_entry_end_date,
1355 l_update_cut_off_date;
1356 close csr_bg_entry_period;
1357
1358 if l_entry_start_date is null then
1359
1360 l_update_allowed := 'N';
1361
1362 end if;
1363
1364 end if;
1365
1366 end if; -- emp org period exists.
1367
1368 if g_debug then
1369 hr_utility.trace('l_entry_start_date = '|| l_entry_start_date);
1370 hr_utility.trace('l_entry_end_date = '|| l_entry_end_date);
1371 hr_utility.trace('l_update_cut_off_date = '|| l_update_cut_off_date);
1372 end if;
1373
1374
1375 -- compare employees effective entry period with current date
1376 if l_entry_start_date is null then -- no entry period found
1377 l_update_allowed := 'N';
1378 else
1379 -- if update cut-off is not defined assign last date of 4712
1380 if l_update_cut_off_date is null then
1381 l_update_cut_off_date := to_date('31.12.4712','DD.MM.YYYY');
1382 end if;
1383
1384 if p_effective_date between l_entry_start_date and l_entry_end_date then
1385 l_update_allowed := 'Y';
1386 elsif p_effective_date between l_entry_end_date and l_update_cut_off_date then
1387 -- check the assignment level update allowed flag.
1388 l_asg_update_allowed := null;
1389 open csr_update_allowed_flag;
1390 fetch csr_update_allowed_flag into l_asg_update_allowed;
1391 close csr_update_allowed_flag;
1392
1393 if l_asg_update_allowed = 'Y' then
1394 l_update_allowed := 'Y';
1395 else
1396 l_update_allowed := 'N';
1397 end if; -- assignment level update allowed
1398
1399 if g_debug then
1400 hr_utility.trace('Assgn Level Update Allowed = '||l_update_allowed);
1401 end if;
1402
1403 end if; -- compare current date with entry period
1404
1405 end if; -- entry period exists
1406
1407 else
1408 l_update_allowed := 'N';
1409 end if; -- YEA has been run
1410
1411 close csr_yea_exist;
1412
1413 return l_update_allowed;
1414
1415 end update_allowed;
1416 -----------------------------------------------------------------------------------
1420 -- which are captured by this procedure and passed as OUT parameters.
1417 -- Bug : 4568233
1418 -- This procedure fires the fast formula KR_VAILDATE_YEA_DATE and passes few
1419 -- parameters for vaildation of YEA. The formula returns 10 error messages
1421 -----------------------------------------------------------------------------------
1422 procedure run_validation_formula( --4644132
1423 P_BUSINESS_GROUP_ID in varchar2,
1424 P_ASSIGNMENT_ID in varchar2,
1425 P_TARGET_YEAR in varchar2,
1426 P_EFFECTIVE_DATE in varchar2,
1427 P_RETURN_MESSAGE out nocopy varchar2,
1428 P_RETURN_STATUS out nocopy varchar2,
1429 P_FF_MESSAGE0 out nocopy varchar2,
1430 P_FF_MESSAGE1 out nocopy varchar2,
1431 P_FF_MESSAGE2 out nocopy varchar2,
1432 P_FF_MESSAGE3 out nocopy varchar2,
1433 P_FF_MESSAGE4 out nocopy varchar2,
1434 P_FF_MESSAGE5 out nocopy varchar2,
1435 P_FF_MESSAGE6 out nocopy varchar2,
1436 P_FF_MESSAGE7 out nocopy varchar2,
1437 P_FF_MESSAGE8 out nocopy varchar2,
1438 P_FF_MESSAGE9 out nocopy varchar2,
1439 P_FF_RETURN_STATUS out nocopy varchar2,
1440 ---------------- Special tax ---------------------
1441 P_EE_EDUC_EXP in varchar2,
1442 P_HOUSING_SAVING_TYPE in varchar2,
1443 P_HOUSING_SAVING in varchar2,
1444 P_HOUSING_PURCHASE_DATE in varchar2,
1445 P_HOUSING_LOAN_DATE in varchar2,
1446 P_HOUSING_LOAN_REPAY in varchar2,
1447 P_LT_HOUSING_LOAN_DATE in varchar2,
1448 P_LT_HOUSING_LOAN_INTEREST_REP in varchar2,
1449 P_DONATION1 in varchar2,
1450 P_POLITICAL_DONATION1 in varchar2,
1451 P_HI_PREM in varchar2,
1452 P_POLITICAL_DONATION2 in varchar2,
1453 P_POLITICAL_DONATION3 in varchar2,
1454 P_DONATION2 in varchar2,
1455 P_DONATION3 in varchar2,
1456 P_MED_EXP_EMP in varchar2,
1457 P_LT_HOUSING_LOAN_DATE_1 in varchar2,
1458 P_LT_HOUSING_LOAN_INT_REPAY_1 in varchar2,
1459 P_MFR_MARRIAGE_OCCASIONS in varchar2,
1460 P_MFR_FUNERAL_OCCASIONS in varchar2,
1461 P_MFR_RELOCATION_OCCASIONS in varchar2,
1462 P_EI_PREM in varchar2,
1463 P_ESOA_DONATION in varchar2,
1464 P_PERS_INS_NAME in varchar2,
1465 P_PERS_INS_PREM in varchar2,
1466 P_DISABLED_INS_PREM in varchar2,
1467 P_MED_EXP in varchar2,
1468 P_MED_EXP_DISABLED in varchar2,
1469 P_MED_EXP_AGED in varchar2,
1470 P_EE_OCCUPATION_EDUC_EXP in varchar2,
1471 ----------------- FW Tax Break --------------------
1472 P_IMMIGRATION_PURPOSE in varchar2,
1473 P_CONTRACT_DATE in varchar2,
1474 P_EXPIRY_DATE in varchar2,
1475 P_STAX_APPLICABLE_FLAG in varchar2,
1476 P_FW_APPLICATION_DATE in varchar2,
1477 P_FW_SUBMISSION_DATE in varchar2,
1478 ----------------- OVS Tax Break -------------------
1479 P_TAX_PAID_DATE in varchar2,
1480 P_OVS_SUBMISSION_DATE in varchar2,
1481 P_KR_OVS_LOCATION in varchar2,
1482 P_KR_OVS_WORK_PERIOD in varchar2,
1483 P_KR_OVS_RESPONSIBILITY in varchar2,
1484 P_TERRITORY_CODE in varchar2,
1485 P_CURRENCY_CODE in varchar2,
1486 P_TAXABLE in varchar2,
1487 P_TAXABLE_SUBJ_TAX_BREAK in varchar2,
1488 P_TAX_BREAK_RATE in varchar2,
1489 P_TAX_FOREIGN_CURRENCY in varchar2,
1490 P_TAX in varchar2,
1491 P_OVS_APPLICATION_DATE in varchar2,
1492 ----------------- Tax Break Info ------------------
1493 P_HOUSING_LOAN_INTEREST_REPAY in varchar2,
1494 P_STOCK_SAVING in varchar2,
1495 P_LT_STOCK_SAVING1 in varchar2,
1496 P_LT_STOCK_SAVING2 in varchar2,
1500 P_GIRO_TUITION_PAID_EXP in varchar2,
1497 ----------------- Tax Exems ----------------------
1498 P_DIRECT_CARD_EXP in varchar2,
1499 P_DPNT_DIRECT_EXP in varchar2,
1501 P_CASH_RECEIPT_EXP in varchar2,
1502 P_NP_PREM in varchar2,
1503 P_PERS_PENSION_PREM in varchar2,
1504 P_PERS_PENSION_SAVING in varchar2,
1505 P_INVEST_PARTNERSHIP_FIN1 in varchar2,
1506 P_INVEST_PARTNERSHIP_FIN2 in varchar2,
1507 P_CREDIT_CARD_EXP in varchar2,
1508 P_EMP_STOCK_OWN_PLAN_CONTRI in varchar2,
1509 P_CREDIT_CARD_EXP_DPNT in varchar2,
1510 P_PEN_PREM in varchar2, -- Bug 6024342
1511 P_LTCI_PREM in varchar2 -- Bug 7260606
1512 )
1513 is
1514 CURSOR csr_formula_exists(p_formula_name VARCHAR2,
1515 p_effective_date DATE)
1516 is
1517 select formula_id
1518 from ff_formulas_f
1519 where formula_name = upper(p_formula_name)
1520 and business_group_id = p_business_group_id
1521 and p_effective_date between effective_start_date and effective_end_date;
1522 --
1523 CURSOR csr_get_session_id
1524 is
1525 select session_id from fnd_sessions
1526 where session_id = userenv('sessionid');
1527 --
1528 CURSOR csr_get_person_id(p_assignment_id NUMBER)
1529 is
1530 select person_id
1531 from per_assignments_f
1532 where assignment_id = p_assignment_id;
1533 --
1534 l_formula_id NUMBER;
1535 l_inputs ff_exec.inputs_t;
1536 l_outputs ff_exec.outputs_t;
1537 l_counter NUMBER := 0;
1538 l_session_id NUMBER := 0;
1539 l_date_earned DATE;
1540 l_person_id NUMBER ;
1541 l_cntr_loop NUMBER := 0;
1542 l_target_year NUMBER := 0;
1543 l_assignment_id NUMBER;
1544 l_effective_date DATE;
1545 l_year NUMBER;
1546 --
1547 begin
1548 P_RETURN_STATUS := 'E';
1549 P_FF_RETURN_STATUS := 'INVALID';
1550
1551 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1552 l_year := to_char(l_effective_date,'yyyy');
1553 l_assignment_id := p_assignment_id;
1554
1555 OPEN csr_formula_exists('VALIDATE_YEA_DATA',l_effective_date); -- 4644132
1556 FETCH csr_formula_exists into l_formula_id;
1557 IF csr_formula_exists%NOTFOUND then
1558 P_RETURN_STATUS := 'S';
1559 return;
1560 END IF;
1561 CLOSE csr_formula_exists;
1562 --
1563
1564 OPEN csr_get_session_id;
1565 FETCH csr_get_session_id into l_session_id;
1566 IF csr_get_session_id%NOTFOUND then
1567 insert into fnd_sessions(session_id,effective_date)
1568 values (userenv('sessionid'),l_effective_date);
1569 END IF;
1570 CLOSE csr_get_session_id;
1571
1572 OPEN csr_get_person_id(l_assignment_id);
1573 FETCH csr_get_person_id into l_person_id;
1574 CLOSE csr_get_person_id;
1575
1576 ff_exec.init_formula(
1577 p_formula_id => l_formula_id,
1578 p_effective_date => l_effective_date,
1579 p_inputs => l_inputs,
1580 p_outputs => l_outputs
1581 );
1582
1583 l_counter := l_inputs.first;
1584 if l_inputs is NOT NULL then
1585 while l_counter <= l_inputs.last loop
1586
1587 if l_inputs(l_counter).name = 'ASSIGNMENT_ID' then
1588 l_inputs(l_counter).value := l_assignment_id;
1589 elsif l_inputs(l_counter).name = 'DATE_EARNED' then
1590 l_inputs(l_counter).value := p_effective_date;
1591 elsif l_inputs(l_counter).name = 'TARGET_YEAR' then -- 4657745
1592 l_inputs(l_counter).value := p_target_year;
1593 elsif l_inputs(l_counter).name = 'EFFECTIVE_DATE' then
1594 l_inputs(l_counter).value := p_effective_date;
1595 elsif l_inputs(l_counter).name = 'PERSON_ID' then
1596 l_inputs(l_counter).value := l_person_id;
1597 elsif l_inputs(l_counter).name = 'EE_EDUC_EXP' then
1598 l_inputs(l_counter).value := P_EE_EDUC_EXP;
1599 elsif l_inputs(l_counter).name = 'HOUSING_SAVING_TYPE' then
1600 l_inputs(l_counter).value := P_HOUSING_SAVING_TYPE;
1601 elsif l_inputs(l_counter).name = 'HOUSING_SAVING' then
1602 l_inputs(l_counter).value := P_HOUSING_SAVING;
1603 elsif l_inputs(l_counter).name = 'HOUSING_PURCHASE_DATE' then
1604 l_inputs(l_counter).value := P_HOUSING_PURCHASE_DATE;
1605 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_DATE' then
1606 l_inputs(l_counter).value := P_HOUSING_LOAN_DATE;
1607 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_REPAY' then
1608 l_inputs(l_counter).value := P_HOUSING_LOAN_REPAY;
1609 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_DATE' then
1610 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_DATE;
1611 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_INTEREST_REPAY' then
1612 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_INTEREST_REP;
1613 elsif l_inputs(l_counter).name = 'DONATION1' then
1614 l_inputs(l_counter).value := P_DONATION1;
1615 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION1' then
1616 l_inputs(l_counter).value := P_POLITICAL_DONATION1;
1617 elsif l_inputs(l_counter).name = 'HI_PREM' then
1618 l_inputs(l_counter).value := P_HI_PREM;
1622 l_inputs(l_counter).value := P_POLITICAL_DONATION3;
1619 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION2' then
1620 l_inputs(l_counter).value := P_POLITICAL_DONATION2;
1621 elsif l_inputs(l_counter).name = 'POLITICAL_DONATION3' then
1623 elsif l_inputs(l_counter).name = 'DONATION2' then
1624 l_inputs(l_counter).value := P_DONATION2;
1625 elsif l_inputs(l_counter).name = 'DONATION3' then
1626 l_inputs(l_counter).value := P_DONATION3;
1627 elsif l_inputs(l_counter).name = 'MED_EXP_EMP' then
1628 l_inputs(l_counter).value := P_MED_EXP_EMP;
1629 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_DATE_1' then
1630 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_DATE_1;
1631 elsif l_inputs(l_counter).name = 'LT_HOUSING_LOAN_INT_REPAY_1' then
1632 l_inputs(l_counter).value := P_LT_HOUSING_LOAN_INT_REPAY_1;
1633 elsif l_inputs(l_counter).name = 'MFR_MARRIAGE_OCCASIONS' then
1634 l_inputs(l_counter).value := P_MFR_MARRIAGE_OCCASIONS;
1635 elsif l_inputs(l_counter).name = 'MFR_FUNERAL_OCCASIONS' then
1636 l_inputs(l_counter).value := P_MFR_FUNERAL_OCCASIONS;
1637 elsif l_inputs(l_counter).name = 'MFR_RELOCATION_OCCASIONS' then
1638 l_inputs(l_counter).value := P_MFR_RELOCATION_OCCASIONS;
1639 elsif l_inputs(l_counter).name = 'EI_PREM' then
1640 l_inputs(l_counter).value := P_EI_PREM;
1641 elsif l_inputs(l_counter).name = 'ESOA_DONATION' then
1642 l_inputs(l_counter).value := P_ESOA_DONATION;
1643 elsif l_inputs(l_counter).name = 'PERS_INS_NAME' then
1644 l_inputs(l_counter).value := P_PERS_INS_NAME;
1645 elsif l_inputs(l_counter).name = 'PERS_INS_PREM' then
1646 l_inputs(l_counter).value := P_PERS_INS_PREM;
1647 elsif l_inputs(l_counter).name = 'DISABLED_INS_PREM' then
1648 l_inputs(l_counter).value := P_DISABLED_INS_PREM;
1649 elsif l_inputs(l_counter).name = 'MED_EXP' then
1650 l_inputs(l_counter).value := P_MED_EXP;
1651 elsif l_inputs(l_counter).name = 'MED_EXP_DISABLED' then
1652 l_inputs(l_counter).value := P_MED_EXP_DISABLED;
1653 elsif l_inputs(l_counter).name = 'MED_EXP_AGED' then
1654 l_inputs(l_counter).value := P_MED_EXP_AGED;
1655 elsif l_inputs(l_counter).name = 'EE_OCCUPATION_EDUC_EXP' then
1656 l_inputs(l_counter).value := P_EE_OCCUPATION_EDUC_EXP;
1657
1658
1659 elsif l_inputs(l_counter).name = 'IMMIGRATION_PURPOSE' then
1660 l_inputs(l_counter).value := P_IMMIGRATION_PURPOSE;
1661 elsif l_inputs(l_counter).name = 'CONTRACT_DATE' then
1662 l_inputs(l_counter).value := P_CONTRACT_DATE;
1663 elsif l_inputs(l_counter).name = 'EXPIRY_DATE' then
1664 l_inputs(l_counter).value := P_EXPIRY_DATE;
1665 elsif l_inputs(l_counter).name = 'STAX_APPLICABLE_FLAG' then
1666 l_inputs(l_counter).value := P_STAX_APPLICABLE_FLAG;
1667 elsif l_inputs(l_counter).name = 'FWTB_APPLICATION_DATE' then
1668 l_inputs(l_counter).value := P_FW_APPLICATION_DATE;
1669 elsif l_inputs(l_counter).name = 'FWTB_SUBMISSION_DATE' then
1670 l_inputs(l_counter).value := P_FW_SUBMISSION_DATE;
1671
1672
1673 elsif l_inputs(l_counter).name = 'TAX_PAID_DATE' then
1674 l_inputs(l_counter).value := P_TAX_PAID_DATE;
1675 elsif l_inputs(l_counter).name = 'OTB_SUBMISSION_DATE' then
1676 l_inputs(l_counter).value := P_OVS_SUBMISSION_DATE;
1677 elsif l_inputs(l_counter).name = 'KR_OVS_LOCATION' then
1678 l_inputs(l_counter).value := P_KR_OVS_LOCATION;
1679 elsif l_inputs(l_counter).name = 'KR_OVS_WORK_PERIOD' then
1680 l_inputs(l_counter).value := P_KR_OVS_WORK_PERIOD;
1681 elsif l_inputs(l_counter).name = 'KR_OVS_RESPONSIBILITY' then
1682 l_inputs(l_counter).value := P_KR_OVS_RESPONSIBILITY;
1683 elsif l_inputs(l_counter).name = 'TERRITORY_CODE' then
1684 l_inputs(l_counter).value := P_TERRITORY_CODE;
1685 elsif l_inputs(l_counter).name = 'CURRENCY_CODE' then
1686 l_inputs(l_counter).value := P_CURRENCY_CODE;
1687 elsif l_inputs(l_counter).name = 'TAXABLE' then
1688 l_inputs(l_counter).value := P_TAXABLE;
1689 elsif l_inputs(l_counter).name = 'TAXABLE_SUBJ_TAX_BREAK' then
1690 l_inputs(l_counter).value := P_TAXABLE_SUBJ_TAX_BREAK;
1691 elsif l_inputs(l_counter).name = 'TAX_BREAK_RATE' then
1692 l_inputs(l_counter).value := P_TAX_BREAK_RATE;
1693 elsif l_inputs(l_counter).name = 'TAX_FOREIGN_CURRENCY' then
1694 l_inputs(l_counter).value := P_TAX_FOREIGN_CURRENCY;
1695 elsif l_inputs(l_counter).name = 'TAX' then
1696 l_inputs(l_counter).value := P_TAX;
1697 elsif l_inputs(l_counter).name = 'OTB_APPLICATION_DATE' then
1698 l_inputs(l_counter).value := P_OVS_APPLICATION_DATE;
1699
1700
1701 elsif l_inputs(l_counter).name = 'HOUSING_LOAN_INTEREST_REPAY' then
1702 l_inputs(l_counter).value := P_HOUSING_LOAN_INTEREST_REPAY;
1703 elsif l_inputs(l_counter).name = 'STOCK_SAVING' then
1704 l_inputs(l_counter).value := P_STOCK_SAVING;
1705 elsif l_inputs(l_counter).name = 'LT_STOCK_SAVING1' then
1706 l_inputs(l_counter).value := P_LT_STOCK_SAVING1;
1707 elsif l_inputs(l_counter).name = 'LT_STOCK_SAVING2' then
1708 l_inputs(l_counter).value := P_LT_STOCK_SAVING2;
1709
1710
1711 elsif l_inputs(l_counter).name = 'DIRECT_CARD_EXP' then
1712 l_inputs(l_counter).value := P_DIRECT_CARD_EXP;
1713 elsif l_inputs(l_counter).name = 'DPNT_DIRECT_EXP' then
1714 l_inputs(l_counter).value := P_DPNT_DIRECT_EXP;
1715 elsif l_inputs(l_counter).name = 'GIRO_TUITION_PAID_EXP' then
1716 l_inputs(l_counter).value := P_GIRO_TUITION_PAID_EXP;
1717 elsif l_inputs(l_counter).name = 'CASH_RECEIPT_EXP' then
1718 l_inputs(l_counter).value := P_CASH_RECEIPT_EXP;
1719 elsif l_inputs(l_counter).name = 'NP_PREM' then
1720 l_inputs(l_counter).value := P_NP_PREM;
1724 l_inputs(l_counter).value := P_PERS_PENSION_SAVING;
1721 elsif l_inputs(l_counter).name = 'PERS_PENSION_PREM' then
1722 l_inputs(l_counter).value := P_PERS_PENSION_PREM;
1723 elsif l_inputs(l_counter).name = 'PERS_PENSION_SAVING' then
1725 elsif l_inputs(l_counter).name = 'INVEST_PARTNERSHIP_FIN1' then
1726 l_inputs(l_counter).value := P_INVEST_PARTNERSHIP_FIN1;
1727 elsif l_inputs(l_counter).name = 'INVEST_PARTNERSHIP_FIN2' then
1728 l_inputs(l_counter).value := P_INVEST_PARTNERSHIP_FIN2;
1729 elsif l_inputs(l_counter).name = 'CREDIT_CARD_EXP' then
1730 l_inputs(l_counter).value := P_CREDIT_CARD_EXP;
1731 elsif l_inputs(l_counter).name = 'EMP_STOCK_OWN_PLAN_CONTRI' then
1732 l_inputs(l_counter).value := P_EMP_STOCK_OWN_PLAN_CONTRI;
1733 elsif l_inputs(l_counter).name = 'CREDIT_CARD_EXP_DPNT' then
1734 l_inputs(l_counter).value := P_CREDIT_CARD_EXP_DPNT;
1735 elsif l_inputs(l_counter).name = 'PEN_PREM' then -- Bug 6024342
1736 l_inputs(l_counter).value := P_PEN_PREM;
1737 elsif l_inputs(l_counter).name = 'LTCI_PREM' then -- Bug 7260606
1738 l_inputs(l_counter).value := P_LTCI_PREM;
1739 end if;
1740 l_counter := l_inputs.next(l_counter);
1741 end loop;
1742 end if;
1743
1744 ff_exec.run_formula(l_inputs, l_outputs);
1745 --get outputs
1746 if l_outputs is not NULL then
1747 for l_counter in l_outputs.first..l_outputs.last loop
1748 if l_outputs(l_counter).name = 'STATUS' then
1749 P_FF_RETURN_STATUS := trim(l_outputs(l_counter).value);
1750 elsif l_cntr_loop = 0 then
1751 P_FF_MESSAGE0 := trim(l_outputs(l_counter).value);
1752 elsif l_cntr_loop = 1 then
1753 P_FF_MESSAGE1 := trim(l_outputs(l_counter).value);
1754 elsif l_cntr_loop = 2 then
1755 P_FF_MESSAGE2 := trim(l_outputs(l_counter).value);
1756 elsif l_cntr_loop = 3 then
1757 P_FF_MESSAGE3 := trim(l_outputs(l_counter).value);
1758 elsif l_cntr_loop = 4 then
1759 P_FF_MESSAGE4 := trim(l_outputs(l_counter).value);
1760 elsif l_cntr_loop = 5 then
1761 P_FF_MESSAGE5 := trim(l_outputs(l_counter).value);
1762 elsif l_cntr_loop = 6 then
1763 P_FF_MESSAGE6 := trim(l_outputs(l_counter).value);
1764 elsif l_cntr_loop = 7 then
1765 P_FF_MESSAGE7 := trim(l_outputs(l_counter).value);
1766 elsif l_cntr_loop = 8 then
1767 P_FF_MESSAGE8 := trim(l_outputs(l_counter).value);
1768 elsif l_cntr_loop = 9 then
1769 P_FF_MESSAGE9 := trim(l_outputs(l_counter).value);
1770 end if;
1771 l_cntr_loop := l_cntr_loop + 1;
1772 end loop;
1773 end if;
1774 --
1775 if P_FF_RETURN_STATUS = 'VALID' then
1776 P_RETURN_STATUS := 'S';
1777 else
1778 P_RETURN_STATUS := 'E';
1779 P_RETURN_MESSAGE := 'PAY_KR_YEA_DATA_INVALID_MSG';
1780 end if;
1781
1782 --
1783 end run_validation_formula;
1784 -----------------------------------------------------------------------------------
1785 -- This procedure toggles the UPDATE_ALLOWED flag in the Extra Assignment Info
1786 -- Type KR_YEA_ENTRY_UPDATE
1787 -----------------------------------------------------------------------------------
1788 procedure change_access(P_ASSIGNMENT_ID in varchar2,
1789 P_TARGET_YEAR in varchar2,
1790 P_RESULT out nocopy varchar2)
1791 is
1792 cursor csr_access is
1793 select nvl(aei_information2,'Y') update_allowed,
1794 assignment_extra_info_id info_id
1795 from per_assignment_extra_info
1796 where assignment_id = P_ASSIGNMENT_ID
1797 and information_type = 'KR_YEA_ENTRY_UPDATE'
1798 and aei_information1 = P_TARGET_YEAR
1799 for update nowait;
1800
1801 l_current_access varchar2(5);
1802 l_future_access varchar2(5);
1803 l_record_present varchar2(5);
1804 l_info_id number;
1805
1806 begin
1807 open csr_access;
1808 fetch csr_access into l_current_access, l_info_id;
1809 if csr_access%NOTFOUND then
1810 l_record_present := 'N';
1811 l_current_access := 'N';
1812 else
1813 l_record_present := 'Y';
1814 end if;
1815 close csr_access;
1816
1817 if l_record_present = 'N' then -- create a record
1818 insert into per_assignment_extra_info(
1819 assignment_extra_info_id,
1820 assignment_id,
1821 aei_information1,
1822 aei_information2,
1823 information_type,
1824 aei_information_category)
1825 values(
1826 per_assignment_extra_info_s.nextval,
1827 P_ASSIGNMENT_ID,
1828 P_TARGET_YEAR,
1829 'Y',
1830 'KR_YEA_ENTRY_UPDATE',
1831 'KR_YEA_ENTRY_UPDATE');
1832 P_RESULT := 'Y';
1833 else -- update the record
1834 if l_current_access = 'N' then
1835 l_future_access := 'Y';
1836 else
1837 l_future_access := 'N';
1838 end if;
1839
1840 update per_assignment_extra_info
1841 set aei_information2 = l_future_access
1842 where assignment_extra_info_id = l_info_id;
1843
1844 P_RESULT := l_future_access;
1845 end if;
1846 commit;
1847
1848 exception
1849 when others then rollback;
1850 raise;
1851 end change_access;
1852 -----------------------------------------------------------------------------------
1856 procedure get_balances(P_ASSIGNMENT_ID in varchar2,
1853 -- This procedure is used to fetch all the balance values required.
1854 -- Bug 5372366: Updated to fetch balances P_HI_PREM_EE and P_EI_PREM
1855 -----------------------------------------------------------------------------------
1857 P_TARGET_YEAR in varchar2,
1858 P_EFFECTIVE_DATE in varchar2,
1859 P_ITAX out nocopy varchar2,
1860 P_STAX out nocopy varchar2,
1861 P_RTAX out nocopy varchar2,
1862 P_TAXABLE out nocopy varchar2,
1863 P_OVS_PROCESSED out nocopy varchar2,
1864 P_TOTAL_TAXABLE_KRW out nocopy varchar2,
1865 P_HI_PREM_EE out nocopy varchar2, -- Bug 5372366
1866 P_EI_PREM out nocopy varchar2, -- Bug 5372366
1867 P_NP_PREM_EE out nocopy varchar2, -- Bug 5185309
1868 P_PEN_PREM_BAL out nocopy varchar2, -- Bug 6024342
1869 P_LTCI_PREM_EE out nocopy varchar2, -- Bug 7260606
1870 P_MILITARY_PEN_PREM out nocopy varchar2, -- Bug 9737699
1871 P_POST_OFFICE_PEN_PREM out nocopy varchar2, -- Bug 9737699
1872 P_PVT_SCHOOL_PEN_PREM out nocopy varchar2) -- Bug 9737699
1873 is
1874 --
1875 eff_date date ;
1876 l_def_bal_id pay_defined_balances.defined_balance_id%type ;
1877 --
1878 begin
1879 eff_date := fnd_date.canonical_to_date(P_EFFECTIVE_DATE);
1880 --
1881 P_ITAX := get_total_itax(P_ASSIGNMENT_ID, eff_date);
1882 P_STAX := get_total_stax(P_ASSIGNMENT_ID, eff_date);
1883 P_RTAX := get_total_rtax(P_ASSIGNMENT_ID, eff_date);
1884 P_TAXABLE := get_total_taxable(P_ASSIGNMENT_ID, eff_date);
1885 P_OVS_PROCESSED := get_ovs_processed(P_ASSIGNMENT_ID, eff_date);
1886 --
1887 open csr_get_def_bal_id_ytd('HI_PREM_EE') ;
1888 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1889 if csr_get_def_bal_id_ytd%found then
1890 p_hi_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1891 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1892 end if ;
1893 close csr_get_def_bal_id_ytd ;
1894 --
1895 -- Bug 7260606
1896 open csr_get_def_bal_id_ytd('LTCI_PREM_EE') ;
1897 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1898 if csr_get_def_bal_id_ytd%found then
1899 p_ltci_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1900 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1901 end if ;
1902 close csr_get_def_bal_id_ytd ;
1903 -- End of Bug 7260606
1904 --
1905 open csr_get_def_bal_id_ytd('EI_PREM') ;
1906 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1907 if csr_get_def_bal_id_ytd%found then
1908 p_ei_prem := pay_balance_pkg.get_value(l_def_bal_id,
1909 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1910 end if ;
1911 close csr_get_def_bal_id_ytd ;
1912 --
1913 open csr_get_def_bal_id_ytd('NP_PREM_EE') ;
1914 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1915 if csr_get_def_bal_id_ytd%found then
1916 p_np_prem_ee := pay_balance_pkg.get_value(l_def_bal_id,
1917 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1918 end if ;
1919 close csr_get_def_bal_id_ytd ;
1920 --
1921 -- Bug 6024342
1922 open csr_get_def_bal_id_ytd('Pension Premium') ;
1923 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1924 if csr_get_def_bal_id_ytd%found then
1925 p_pen_prem_bal := pay_balance_pkg.get_value(l_def_bal_id,
1926 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1927 end if ;
1928 close csr_get_def_bal_id_ytd ;
1929 --
1930 -- Bug 9737699
1931 open csr_get_def_bal_id_ytd('Post Office Pension') ;
1932 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1933 if csr_get_def_bal_id_ytd%found then
1934 p_post_office_pen_prem := pay_balance_pkg.get_value(l_def_bal_id,
1935 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1936 end if ;
1937 close csr_get_def_bal_id_ytd ;
1938 --
1939 -- Bug 9737699
1940 open csr_get_def_bal_id_ytd('Private School Pension') ;
1941 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1942 if csr_get_def_bal_id_ytd%found then
1943 p_pvt_school_pen_prem := pay_balance_pkg.get_value(l_def_bal_id,
1944 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1945 end if ;
1946 close csr_get_def_bal_id_ytd ;
1947 --
1948 -- Bug 9737699
1949 open csr_get_def_bal_id_ytd('Military Pension Premium') ;
1950 fetch csr_get_def_bal_id_ytd into l_def_bal_id ;
1951 if csr_get_def_bal_id_ytd%found then
1952 p_military_pen_prem := pay_balance_pkg.get_value(l_def_bal_id,
1953 p_assignment_id, fnd_date.canonical_to_date(p_effective_date) ) ;
1954 end if ;
1955 close csr_get_def_bal_id_ytd ;
1959 -----------------------------------------------------------------------------------
1956 --
1957 --
1958 end get_balances;
1960 -- This procedure delete the YEA Data for the target year of an assignment.
1961 -----------------------------------------------------------------------------------
1962 procedure delete_all_records(
1963 P_ASSIGNMENT_ID in varchar2,
1964 P_TARGET_YEAR in varchar2)
1965 is
1966 --
1967 -- Bug 9326153
1968 --
1969 cursor csr_delete
1970 is
1971 select assignment_extra_info_id,object_version_number
1972 from per_assignment_extra_info
1973 where assignment_id = p_assignment_id
1974 and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
1975 And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
1976 ,'KR_YEA_SP_TAX_EXEM_INFO2'
1977 ,'KR_YEA_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
1978 ,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
1979 ,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
1980 ,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
1981 ,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
1982 ,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
1983 ,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
1984 ,'KR_YEA_PREV_ER_INFO'
1985 ,'KR_YEA_TAX_BREAK_INFO'
1986 ,'KR_YEA_TAX_EXEM_INFO'
1987 ,'KR_YEA_OVS_TAX_BREAK_INFO'
1988 ,'KR_YEA_DETAIL_DONATION_INFO'
1989 ,'KR_YEA_EMP_EXPENSE_DETAILS'
1990 ,'KR_YEA_TAX_GROUP_INFO'
1991 ,'KR_YEA_NON_TAXABLE_EARN_DETAIL'
1992 ,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
1993 ,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
1994 ,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
1995 ,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
1996 ,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
1997 ,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
1998 --
1999 begin
2000 --
2001 -- Bug 9326153
2002 for i in csr_delete loop
2003 --
2004 hr_assignment_extra_info_api.delete_assignment_extra_info(
2005 p_validate => false,
2006 p_assignment_extra_info_id => i.assignment_extra_info_id,
2007 p_object_version_number => i.object_version_number);
2008 end loop;
2009 --
2010 commit;
2011 end;
2012 -----------------------------------------------------------------------------------
2013 -- This procedure gets the Dependent Information for an Employee
2014 -----------------------------------------------------------------------------------
2015 procedure get_dependent_information(
2016 P_ASSIGNMENT_ID in varchar2,
2017 P_EFFECTIVE_DATE in varchar2,
2018 P_SPOUSE_EXEM out nocopy varchar2,
2019 P_AGED_DEPENDENTS out nocopy varchar2,
2020 P_ADULT_DEPENDENTS out nocopy varchar2,
2021 P_UNDERAGED_DEPENDENTS out nocopy varchar2,
2022 P_TOTAL_DEPENDENTS out nocopy varchar2,
2023 P_TOTAL_AGEDS out nocopy varchar2,
2024 P_TOTAL_DISABLED out nocopy varchar2,
2025 P_FEMALE_EXEM out nocopy varchar2,
2026 P_TOTAL_CHILDREN out nocopy varchar2,
2027 P_TOTAL_SUPER_AGEDS out nocopy varchar2,
2028 P_NEW_BORN_ADOPTED out nocopy varchar2, -- Bug 6705170
2029 P_HOUSE_HOLDER out nocopy varchar2,
2030 P_HOUSE_HOLDER_CODE out nocopy varchar2)
2031 is
2032 l_return_code NUMBER;
2033 l_spouse_exem VARCHAR2(1);
2034 l_aged_dependents NUMBER;
2035 l_adult_dependents NUMBER;
2036 l_underaged_dpnts NUMBER;
2037 l_total_dependents NUMBER;
2038 l_total_ageds NUMBER;
2039 l_total_disableds NUMBER;
2040 l_female_exem VARCHAR2(1);
2041 l_total_children NUMBER;
2042 l_total_super_ageds NUMBER;
2043 l_new_born_adopted NUMBER; -- Bug 6705170
2044 l_total_addtl_child NUMBER; -- Bug 6784288
2045 l_nonresident_flag VARCHAR2(1);
2046
2047 CURSOR csr_get_non_resident_flag(p_assignment_id in number,
2048 p_effective_date in DATE) is
2049 select peev.screen_entry_value
2050 from pay_element_entries_f pee,
2051 pay_element_entry_values_f peev,
2052 pay_input_values_f piv,
2053 pay_element_types_f pet
2054 where pet.element_name = 'TAX'
2055 and pet.legislation_code = 'KR'
2056 and pee.entry_type = 'E'
2057 and pet.element_type_id = piv.element_type_id
2058 and piv.name = 'NON_RESIDENT_FLAG'
2059 and pee.element_entry_id = peev.element_entry_id
2060 and piv.input_value_id = peev.input_value_id
2061 and pee.assignment_id = p_assignment_id
2062 and p_effective_date between pee.effective_start_date and pee.effective_end_date
2063 and p_effective_date between peev.effective_start_date and peev.effective_end_date
2064 and p_effective_date between piv.effective_start_date and piv.effective_end_date;
2065
2066
2067 cursor csr_house_holder(p_assgn_id in number, p_eff_date in date) is
2068 select nvl(pei.PEI_INFORMATION3,'N')
2069 from PER_PEOPLE_EXTRA_INFO pei,
2070 per_assignments_f paf
2071 where paf.assignment_id = p_assgn_id
2072 and p_eff_date between paf.effective_start_date and paf.effective_end_DATE
2073 and pei.person_id = paf.person_id
2074 and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
2075
2076 --
2077 -- Bug 9246066: Updated to display terminated employee information in SSHR YEA
2078 --
2079 cursor csr_get_eff_date(p_assgn_id in number, p_eff_date in date) is
2080 select nvl(ppos.final_process_date,p_eff_date)
2081 from per_assignments_f asg,
2082 per_periods_of_service ppos
2083 where asg.assignment_id = p_assgn_id
2084 and ppos.period_of_service_id = asg.period_of_service_id
2085 and nvl(ppos.final_process_date,p_eff_date) between asg.effective_start_date and asg.effective_end_date;
2086
2087 l_final_eff_date date;
2088 final_eff_date date;
2089 --
2090 begin
2091
2092 -- Bug 9246066
2093
2094 l_final_eff_date := null;
2095 final_eff_date := null;
2096
2097 open csr_get_eff_date(p_assignment_id, fnd_date.canonical_to_date(p_effective_date));
2098 fetch csr_get_eff_date into l_final_eff_date;
2099 close csr_get_eff_date;
2100
2101 if to_char(l_final_eff_date,'YYYY') = to_char(fnd_date.canonical_to_date(p_effective_date),'YYYY') then
2102 final_eff_date := l_final_eff_date;
2103 else
2104 final_eff_date := fnd_date.canonical_to_date(p_effective_date);
2105 end if;
2106 -- End of bug 9246066
2107
2108 OPEN csr_get_non_resident_flag(to_number(P_ASSIGNMENT_ID),final_eff_date); -- Bug 9246066
2109 FETCH csr_get_non_resident_flag into l_nonresident_flag;
2110 CLOSE csr_get_non_resident_flag;
2111 --
2112
2113 p_house_holder_code := null;
2114 open csr_house_holder(p_assignment_id, final_eff_date); -- Bug 9246066
2115 fetch csr_house_holder into p_house_holder_code;
2116 close csr_house_holder;
2117
2118 if p_house_holder_code is null then
2119 p_house_holder_code := 'N';
2120 end if;
2121
2122 p_house_holder := hr_general.decode_lookup('YES_NO', p_house_holder_code);
2123
2124 l_return_code :=
2125 pay_kr_ff_functions_pkg.get_dependent_info(
2126 p_assignment_id => to_number(P_ASSIGNMENT_ID),
2127 p_date_earned => final_eff_date, -- Bug 9246066
2128 p_non_resident_flag => l_nonresident_flag,
2129 p_dpnt_spouse_flag => l_spouse_exem,
2130 p_num_of_aged_dpnts => l_aged_dependents,
2131 p_num_of_adult_dpnts => l_adult_dependents,
2132 p_num_of_underaged_dpnts => l_underaged_dpnts,
2133 p_num_of_dpnts => l_total_dependents,
2134 p_num_of_ageds => l_total_ageds,
2135 p_num_of_disableds => l_total_disableds,
2136 p_female_ee_flag => l_female_exem,
2137 p_num_of_children => l_total_children,
2138 p_num_of_super_ageds => l_total_super_ageds,
2139 p_num_of_new_born_adopted => l_new_born_adopted, -- Bug 6705170
2140 p_num_of_addtl_child => l_total_addtl_child); -- Bug 6784288
2141 --
2142 if l_return_code = 0 then
2143 P_SPOUSE_EXEM := hr_general.decode_lookup('YES_NO',l_spouse_exem);
2144 P_AGED_DEPENDENTS := to_char(l_aged_dependents);
2145 P_ADULT_DEPENDENTS := to_char(l_adult_dependents);
2146 P_UNDERAGED_DEPENDENTS := to_char(l_underaged_dpnts);
2147 P_TOTAL_DEPENDENTS := to_char(l_total_dependents);
2148 P_TOTAL_AGEDS := to_char(l_total_ageds);
2149 P_TOTAL_DISABLED := to_char(l_total_disableds);
2150 P_FEMALE_EXEM := hr_general.decode_lookup('YES_NO',l_female_exem);
2151 P_TOTAL_CHILDREN := to_char(l_total_children);
2152 P_TOTAL_SUPER_AGEDS := to_char(l_total_super_ageds);
2153 P_NEW_BORN_ADOPTED := to_char(l_new_born_adopted);
2154 end if;
2155 --
2156 end;
2157
2158 ------------------------------------------------------------------------------------
2159 -- This procedure updates House Holder flag. Creates Extra Info Records if necessary
2160 ------------------------------------------------------------------------------------
2161
2165
2162 procedure update_house_holder(p_person_id in varchar2,
2163 p_house_holder_code in varchar2)
2164 is
2166 cursor csr_record_exist(p_per_id in varchar2) is
2167 select person_extra_info_id, object_version_number
2168 from per_people_extra_info
2169 where person_id = p_per_id
2170 and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
2171
2172 l_information_type varchar2(100);
2173 l_pei_id number;
2174 l_ovn number;
2175
2176 begin
2177
2178 l_information_type := 'PER_KR_RELATED_YEA_INFORMATION';
2179 l_ovn := null;
2180 l_pei_id := null;
2181
2182 open csr_record_exist(p_person_id);
2183 fetch csr_record_exist into l_pei_id,l_ovn;
2184 close csr_record_exist;
2185
2186 if l_pei_id is null then -- need to create a record
2187 hr_person_extra_info_api.create_person_extra_info(
2188 p_validate => false,
2189 p_person_id => p_person_id,
2190 p_information_type => l_information_type,
2191 p_pei_information_category => l_information_type,
2192 p_pei_information1 => 'N',
2193 p_pei_information2 => 'R',
2194 p_pei_information3 => p_house_holder_code,
2195 p_person_extra_info_id => l_pei_id,
2196 p_object_version_number => l_ovn);
2197 else
2198 hr_person_extra_info_api.update_person_extra_info(
2199 p_validate => false,
2200 p_person_extra_info_id => l_pei_id,
2201 p_object_version_number => l_ovn,
2202 p_pei_information3 => p_house_holder_code);
2203
2204 end if;
2205 commit;
2206
2207 exception
2208 when others then rollback;
2209 raise;
2210 end;
2211
2212 ------------------------------------------------------------------------------------
2213 -- Bug 6849941: Credit Card Validation Checks
2214 ------------------------------------------------------------------------------------
2215 procedure enable_credit_card(
2216 p_person_id in number,
2217 p_contact_person_id in number,
2218 p_contact_relationship_id in number,
2219 p_date_earned in varchar2,
2220 p_result out nocopy varchar2)
2221 is
2222 begin
2223
2224 p_result := pay_kr_ff_functions_pkg.enable_credit_card(
2225 p_person_id,
2226 p_contact_person_id,
2227 p_contact_relationship_id,
2228 to_date(p_date_earned,'YYYY-MM-DD'));
2229
2230 end;
2231 ------------------------------------------------------------------------------------
2232 -- Bug 7142612
2233 ------------------------------------------------------------------------------------
2234 procedure enable_donation_fields(
2235 p_person_id in number,
2236 p_contact_person_id in number,
2237 p_contact_relationship_id in number,
2238 p_date_earned in varchar2,
2239 p_result out nocopy varchar2)
2240 is
2241 begin
2242
2243 p_result := pay_kr_ff_functions_pkg.enable_donation_fields(
2244 p_person_id,
2245 p_contact_person_id,
2246 p_contact_relationship_id,
2247 to_date(p_date_earned,'YYYY-MM-DD'));
2248 end;
2249 ------------------------------------------------------------------------------------
2250 -- Bug 7142612
2251 ------------------------------------------------------------------------------------
2252 procedure validate_bus_reg_num(
2253 p_national_identifier in varchar2,
2254 p_result out nocopy varchar2)
2255 is
2256 begin
2257 p_result := pay_kr_ff_functions_pkg.validate_bus_reg_num(
2258 p_national_identifier);
2259
2260 end;
2261 ------------------------------------------------------------------------------------
2262 -- Bug 7633302
2263 ------------------------------------------------------------------------------------
2264 procedure detail_exists(
2265 p_ayi_information6 in number,
2266 p_assignment_id in number,
2267 p_target_year in number,
2268 p_result out nocopy varchar2)
2269 is
2270 cursor csr is
2271 select count(*)
2272 from PER_KR_ASSIGNMENT_YEA_INFO
2273 where assignment_id = p_assignment_id
2274 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2275 and target_year = p_target_year
2276 and Ayi_information6 = p_ayi_information6;
2277 --
2278 l_dummy number;
2279 --
2280 begin
2281 l_dummy := 0;
2282 open csr;
2283 fetch csr into l_dummy;
2284 close csr;
2285
2286 if l_dummy > 0 then
2287 p_result := 'Y';
2288 else
2289 p_result := 'N';
2290 end if;
2291
2292 end;
2293 ------------------------------------------------------------------------------------
2294 -- Bug 7633302
2295 ------------------------------------------------------------------------------------
2296 procedure chk_taxation_period_unique(
2297 p_assignment_yea_info_id in number,
2298 p_assignment_id in number,
2299 p_ayi_information2 in varchar2,
2300 p_ayi_information6 in varchar2, -- Bug 9213683
2301 p_target_year in number,
2302 p_result out nocopy varchar2)
2303 is
2304
2305 cursor csr is
2309 and assignment_yea_info_id <> p_assignment_yea_info_id
2306 select count(ayi_information2)
2307 from per_kr_assignment_yea_info
2308 where assignment_id = p_assignment_id
2310 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2311 and ayi_information2 = p_ayi_information2
2312 and ayi_information6 = p_ayi_information6 -- Bug 9213683
2313 and target_year = p_target_year;
2314 --
2315 l_dummy number;
2316 --
2317 begin
2318 --
2319 l_dummy := 0;
2320 open csr;
2321 fetch csr into l_dummy;
2322 close csr;
2323
2324 if l_dummy > 0 then
2325 p_result := 'Y';
2326 else
2327 p_result := 'N';
2328 end if;
2329
2330 end;
2331 --
2332 ------------------------------------------------------------------------------------
2333 -- Bug 9079450
2334 ------------------------------------------------------------------------------------
2335 procedure aged_flag(
2336 p_national_identifier in varchar2,
2337 p_effective_date in varchar2,
2338 p_result out nocopy varchar2)
2339 is
2340
2341 cursor csr is
2342 select pay_kr_ff_functions_pkg.aged_flag(p_national_identifier,fnd_date.canonical_to_date(p_effective_date))
2343 from dual;
2344
2345 begin
2346
2347 open csr;
2348 fetch csr into p_result;
2349 close csr;
2350
2351 end;
2352 ------------------------------------------------------------------------------------
2353 /* 9294813 */
2354 procedure insert_fnd_sessions( p_effective_date in varchar2
2355 )
2356 is
2357 cursor csr_get_session_id is
2358 select session_id
2359 from fnd_sessions
2360 where session_id = userenv('sessionid');
2361 l_session_id number ;
2362 begin
2363
2364 -- Insert a row into fnd_sessions if reqd
2365 open csr_get_session_id;
2366 fetch csr_get_session_id into l_session_id;
2367 if csr_get_session_id%notfound then
2368 insert into fnd_sessions(session_id,effective_date)
2369 values (userenv('sessionid'),fnd_date.canonical_to_date(p_effective_date));
2370 end if;
2371 close csr_get_session_id;
2372
2373 end insert_fnd_sessions;
2374 --------------------------------------------------------------------------------------
2375 --
2376 -- Bug 9737699
2377 --------------------------------------------------------------------------------------
2378 procedure get_lookup_meaning( p_lookup_type in varchar2,
2379 p_lookup_code in varchar2,
2380 p_result out nocopy varchar2)
2381 is
2382 --
2383 cursor csr_get_lookup_meaning is
2384 select MEANING
2385 from HR_LOOKUPS
2386 where lookup_type = p_lookup_type
2387 and lookup_code = p_lookup_code
2388 and enabled_flag = 'Y';
2389 --
2390 begin
2391 --
2392 open csr_get_lookup_meaning;
2393 fetch csr_get_lookup_meaning into p_result;
2394 if csr_get_lookup_meaning%NOTFOUND then
2395 p_result := null;
2396 end if;
2397 close csr_get_lookup_meaning;
2398 --
2399 end;
2400 --------------------------------------------------------------------------------------
2401 --
2402 -- Bug 9737699
2403 --
2404 --------------------------------------------------------------------------------------
2405 procedure get_globalvalue(p_glbvar in varchar2,
2406 p_process_date in varchar2,
2407 p_result out nocopy varchar2)
2408 is
2409 begin
2410 p_result := pay_kr_ff_functions_pkg.get_globalvalue(p_glbvar,fnd_date.canonical_to_date(p_process_date));
2411
2412 end;
2413 --------------------------------------------------------------------------------------
2414 --
2415 -- Bug 9393732
2416 --
2417 --------------------------------------------------------------------------------------
2418 procedure insert_don_type_data(p_assignment_id in number,
2419 p_target_year in varchar2)
2420 --
2421 is
2422 --
2423 l_asg_extra_info_id number;
2424 l_ovn number;
2425 --
2426 cursor csr_get_base_tbl_data is
2427 select aei_information1,
2428 aei_information2,
2429 aei_information3,
2430 aei_information4,
2431 aei_information5,
2432 aei_information6,
2433 aei_information7,
2434 aei_information8,
2435 aei_information9,
2436 aei_information10,
2437 aei_information11
2438 from per_assignment_extra_info
2439 where assignment_id = p_assignment_id
2440 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2441 and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = to_number(p_target_year);
2442 --
2443 cursor csr_get_temp_tbl_data(l_donation_year in varchar2, l_donation_code in varchar2) is
2444 select assignment_yea_info_id,
2445 object_version_number
2446 from per_kr_assignment_yea_info
2447 where assignment_id = p_assignment_id
2448 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2449 and ayi_information2 = l_donation_year
2450 and ayi_information8 = l_donation_code
2451 and target_year = p_target_year;
2452 --
2453 --
2454 begin
2455 --
2456 insert_fnd_sessions( p_target_year||'/12/31 00:00:00');
2457 --
2458 for i in csr_get_base_tbl_data loop
2459 --
2460 l_asg_extra_info_id := null;
2461 l_ovn := null;
2462 --
2463 open csr_get_temp_tbl_data(i.aei_information2,i.aei_information8);
2464 fetch csr_get_temp_tbl_data into l_asg_extra_info_id,l_ovn;
2468 if (i.aei_information2 <> p_target_year) then
2465 close csr_get_temp_tbl_data;
2466 --
2467 if l_asg_extra_info_id is null then
2469 insert into per_kr_assignment_yea_info
2470 (assignment_yea_info_id,
2471 assignment_id,
2472 information_type,
2473 target_year,
2474 ayi_information_category,
2475 ayi_information1,
2476 ayi_information2,
2477 ayi_information3,
2478 ayi_information4,
2479 ayi_information5,
2480 ayi_information6,
2481 ayi_information7,
2482 ayi_information8,
2483 ayi_information9,
2484 ayi_information10,
2485 ayi_information11,
2486 object_version_number)
2487 values
2488 (PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
2489 p_assignment_id,
2490 'KR_YEA_DONATION_TYPE_DETAIL',
2491 p_target_year,
2492 'KR_YEA_DONATION_TYPE_DETAIL',
2493 i.aei_information1,
2494 i.aei_information2,
2495 i.aei_information3,
2496 i.aei_information4,
2497 i.aei_information5,
2498 i.aei_information6,
2499 i.aei_information7,
2500 i.aei_information8,
2501 i.aei_information9,
2502 i.aei_information10,
2503 i.aei_information11,
2504 1);
2505 end if;
2506 else
2507 if((i.aei_information2 = p_target_year)
2508 and (pay_kr_ff_functions_pkg.display_yea_info(p_assignment_id,
2509 fnd_date.canonical_to_date(i.aei_information1)) = 'Y')) then
2510 update per_kr_assignment_yea_info
2511 set ayi_information4 = i.aei_information4,
2512 ayi_information11 = i.aei_information11,
2513 ayi_information5 = i.aei_information5
2514 where assignment_yea_info_id = l_asg_extra_info_id;
2515 elsif(i.aei_information2 <> p_target_year) then
2516 update per_kr_assignment_yea_info
2517 set ayi_information3 = i.aei_information3,
2518 ayi_information4 = i.aei_information4,
2519 ayi_information5 = i.aei_information5,
2520 ayi_information9 = i.aei_information9,
2521 ayi_information11 = i.aei_information11,
2522 ayi_information10 = i.aei_information10
2523 where assignment_yea_info_id = l_asg_extra_info_id;
2524 end if;
2525 --
2526 end if;
2527 --
2528 end loop;
2529 --
2530 commit;
2531 exception
2532 when others then rollback;
2533 raise;
2534 --
2535 end;
2536 --------------------------------------------------------------------------------------
2537 --
2538 -- Bug 9393732
2539 --
2540 --------------------------------------------------------------------------------------
2541 procedure sync_don_type_data(p_assignment_id in number,
2542 p_target_year in number,
2543 p_political in number,
2544 p_statutory in number,
2545 p_esoa in number,
2546 p_pledt in number,
2547 p_religious in number,
2548 p_specified in number,
2549 p_tax_law in number)
2550 is
2551 --
2552 cursor csr_check_data(l_donation_code in varchar2) is
2553 select assignment_yea_info_id
2554 from per_kr_assignment_yea_info
2555 where assignment_id = p_assignment_id
2556 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2557 and target_year = p_target_year
2558 and ayi_information2 = p_target_year
2559 and ayi_information8 = l_donation_code;
2560 --
2561 l_asg_info_id number;
2562 --
2563 procedure sync_temp_tbl_data(l_donation_code in varchar2,
2564 l_asgInfoId in number,
2565 l_amt in number)
2566 is
2567 begin
2568 if ((l_asgInfoId) is not null and (l_amt > 0)) then
2569 --
2570 update per_kr_assignment_yea_info
2571 set ayi_information9 = l_amt,
2572 ayi_information10 = l_amt
2573 where assignment_yea_info_id = l_asgInfoId
2574 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2575 and assignment_id = p_assignment_id;
2576 --
2577 elsif ((l_asgInfoId) is not null and (l_amt = 0)) then
2578 --
2579 delete
2580 from per_kr_assignment_yea_info
2581 where assignment_yea_info_id = l_asgInfoId
2582 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2583 and assignment_id = p_assignment_id;
2584 --
2585 elsif ((l_asgInfoId) is null and (l_amt > 0)) then
2586 --
2587 insert into per_kr_assignment_yea_info
2588 (assignment_yea_info_id,
2589 assignment_id,
2590 information_type,
2591 target_year,
2592 ayi_information_category,
2593 ayi_information1,
2594 ayi_information2,
2595 ayi_information3,
2596 ayi_information4,
2597 ayi_information5,
2598 ayi_information6,
2599 ayi_information7,
2600 ayi_information8,
2601 ayi_information9,
2602 ayi_information10,
2603 ayi_information11,
2604 object_version_number)
2605 values
2606 (PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
2607 p_assignment_id,
2608 'KR_YEA_DONATION_TYPE_DETAIL',
2609 p_target_year,
2610 'KR_YEA_DONATION_TYPE_DETAIL',
2611 fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
2612 p_target_year,
2613 '0',
2614 '0',
2615 '0',
2616 null,
2617 null,
2618 l_donation_code,
2619 l_amt,
2620 l_amt,
2621 '0',
2622 1);
2623 --
2624 end if;
2625 end;
2626 --
2627 begin
2628 --
2629 hr_utility.trace('p_statutory = '||to_char(p_statutory));
2630 hr_utility.trace('p_political = '||to_char(p_political));
2631 hr_utility.trace('p_esoa = '||to_char(p_esoa));
2632 hr_utility.trace('p_pledt = '||to_char(p_pledt));
2633 hr_utility.trace('p_religious = '||to_char(p_religious));
2634 hr_utility.trace('p_specified = '||to_char(p_specified));
2635 hr_utility.trace('p_tax_law = '||to_char(p_tax_law));
2636
2637 -- Statutory Donation
2638 l_asg_info_id := null;
2639 open csr_check_data('10');
2640 fetch csr_check_data into l_asg_info_id;
2641 close csr_check_data;
2642
2643 sync_temp_tbl_data('10',l_asg_info_id,p_statutory);
2644
2645 -- Political Donation
2646 l_asg_info_id := null;
2647 open csr_check_data('20');
2648 fetch csr_check_data into l_asg_info_id;
2649 close csr_check_data;
2650
2651 sync_temp_tbl_data('20',l_asg_info_id,p_political);
2652
2653 -- Tax Reduction Law
2654 l_asg_info_id := null;
2655 open csr_check_data('30');
2656 fetch csr_check_data into l_asg_info_id;
2657 close csr_check_data;
2658
2659 sync_temp_tbl_data('30',l_asg_info_id,p_tax_law);
2660
2661 -- ESOA Donation
2662 l_asg_info_id := null;
2663 open csr_check_data('42');
2664 fetch csr_check_data into l_asg_info_id;
2665 close csr_check_data;
2666
2667 sync_temp_tbl_data('42',l_asg_info_id,p_esoa);
2668
2669 -- Public Legal Entity Donation Trust Details
2670 l_asg_info_id := null;
2671 open csr_check_data('31');
2672 fetch csr_check_data into l_asg_info_id;
2673 close csr_check_data;
2674
2675 sync_temp_tbl_data('31',l_asg_info_id,p_pledt);
2676
2677 -- Religious Donation
2678 l_asg_info_id := null;
2679 open csr_check_data('41');
2680 fetch csr_check_data into l_asg_info_id;
2681 close csr_check_data;
2682
2683 sync_temp_tbl_data('41',l_asg_info_id,p_religious);
2684
2685 -- Specified Donation
2686 l_asg_info_id := null;
2687 open csr_check_data('40');
2688 fetch csr_check_data into l_asg_info_id;
2689 close csr_check_data;
2690
2691 sync_temp_tbl_data('40',l_asg_info_id,p_specified);
2692 --
2693 --
2694 end;
2695 --------------------------------------------------------------------------------------
2696 -- Bug 9393732
2697 --
2698 --------------------------------------------------------------------------------------
2699 procedure display_yea_info(p_assignment_id in varchar2,
2700 p_target_year in varchar2,
2701 p_result out nocopy varchar2)
2702 is
2703 begin
2704 p_result := pay_kr_ff_functions_pkg.display_yea_info(to_number(p_assignment_id),to_date('31-12-'||p_target_year,'DD-MM-YYYY'));
2705 end;
2706 --------------------------------------------------------------------------------------
2707 -- Bug 9393732
2708 --------------------------------------------------------------------------------------
2709 procedure update_don_tot_data(
2710 p_assignment_id in number,
2711 p_target_year in number,
2712 p_stat_self in number,
2713 p_stat_spouse in number,
2714 p_stat_child in number,
2715 p_pol_self in number,
2716 p_tax_self in number,
2717 p_tax_spouse in number,
2718 p_tax_child in number,
2719 p_pledt_self in number,
2720 p_pledt_spouse in number,
2721 p_pledt_child in number,
2722 p_esoa_self in number,
2723 p_spec_self in number,
2724 p_spec_spouse in number,
2725 p_spec_child in number,
2726 p_rel_self in number,
2727 p_rel_spouse in number,
2728 p_rel_child in number,
2729 p_oth_self in number,
2730 p_oth_spouse in number,
2731 p_oth_child in number,
2732 p_stat_parent in number,
2733 p_stat_bro_sis in number,
2734 p_tax_parent in number,
2735 p_tax_bro_sis in number,
2736 p_pledt_parent in number,
2737 p_pledt_bro_sis in number,
2738 p_spec_parent in number,
2739 p_spec_bro_sis in number,
2740 p_rel_parent in number,
2741 p_rel_bro_sis in number,
2742 p_oth_parent in number,
2743 p_oth_bro_sis in number)
2744 --
2745 is
2746 --
2747 cursor csr_total_exists is
2748 select assignment_yea_info_id
2749 from per_kr_assignment_yea_info
2750 where assignment_id = p_assignment_id
2751 and information_type = 'KR_YEA_DONATION_TOTALS'
2752 and target_year = p_target_year;
2753
2754 cursor csr_ex_total_exists is
2755 select assignment_yea_info_id
2756 from per_kr_assignment_yea_info
2757 where assignment_id = p_assignment_id
2758 and information_type = 'KR_YEA_DONATION_TOTALS1'
2759 and target_year = p_target_year;
2760 --
2761 l_asg_info_id number;
2762 l_ex_asg_info_id number;
2763 l_ovn number;
2764 --
2765 begin
2766 --
2767 l_asg_info_id := null;
2768 l_ex_asg_info_id := null;
2769 --
2770 open csr_total_exists;
2771 fetch csr_total_exists into l_asg_info_id;
2772 close csr_total_exists;
2773
2774 open csr_ex_total_exists;
2775 fetch csr_ex_total_exists into l_ex_asg_info_id;
2776 close csr_ex_total_exists;
2777
2781 p_stat_spouse > 0 or
2778 --
2779 if(l_asg_info_id is not null or l_ex_asg_info_id is not null) then
2780 if(p_stat_self > 0 or
2782 p_stat_child > 0 or
2783 p_pol_self > 0 or
2784 p_tax_self > 0 or
2785 p_tax_spouse > 0 or
2786 p_tax_child > 0 or
2787 p_pledt_self > 0 or
2788 p_pledt_spouse > 0 or
2789 p_pledt_child > 0 or
2790 p_esoa_self > 0 or
2791 p_spec_self > 0 or
2792 p_spec_spouse > 0 or
2793 p_spec_child > 0 or
2794 p_rel_self > 0 or
2795 p_rel_spouse > 0 or
2796 p_rel_child > 0 or
2797 p_oth_self > 0 or
2798 p_oth_spouse > 0 or
2799 p_oth_child > 0 or
2800 p_stat_parent > 0 or
2801 p_stat_bro_sis > 0 or
2802 p_tax_parent > 0 or
2803 p_tax_bro_sis > 0 or
2804 p_pledt_parent > 0 or
2805 p_pledt_bro_sis > 0 or
2806 p_spec_parent > 0 or
2807 p_spec_bro_sis > 0 or
2808 p_rel_parent > 0 or
2809 p_rel_bro_sis > 0 or
2810 p_oth_parent > 0 or
2811 p_oth_bro_sis > 0 ) then
2812 --
2813 update per_kr_assignment_yea_info
2814 set ayi_information2 = p_stat_self,
2815 ayi_information4 = p_stat_spouse,
2816 ayi_information3 = p_stat_child,
2817 ayi_information5 = p_pol_self,
2818 ayi_information8 = p_tax_self,
2819 ayi_information10 = p_tax_spouse,
2820 ayi_information9 = p_tax_child,
2821 ayi_information11 = p_pledt_self,
2822 ayi_information13 = p_pledt_spouse,
2823 ayi_information12 = p_pledt_child,
2824 ayi_information14 = p_spec_self,
2825 ayi_information16 = p_spec_spouse,
2826 ayi_information15 = p_spec_child,
2827 ayi_information17 = p_rel_self,
2828 ayi_information19 = p_rel_spouse,
2829 ayi_information18 = p_rel_child,
2830 ayi_information20 = p_esoa_self,
2831 ayi_information23 = p_oth_self,
2832 ayi_information25 = p_oth_spouse,
2833 ayi_information24 = p_oth_child
2834 where assignment_id = p_assignment_id
2835 and information_type = 'KR_YEA_DONATION_TOTALS'
2836 and target_year = p_target_year;
2837
2838 if l_ex_asg_info_id is null then
2839 insert into per_kr_assignment_yea_info
2840 (assignment_yea_info_id,
2841 assignment_id,
2842 information_type,
2843 target_year,
2844 ayi_information_category,
2845 ayi_information1,
2846 ayi_information2,
2847 ayi_information6,
2848 ayi_information8,
2849 ayi_information10,
2850 ayi_information12,
2851 ayi_information16,
2852 ayi_information3,
2853 ayi_information7,
2854 ayi_information9,
2855 ayi_information11,
2856 ayi_information13,
2857 ayi_information17,
2858 object_version_number)
2859 values
2860 (PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
2861 p_assignment_id,
2862 'KR_YEA_DONATION_TOTALS1',
2863 p_target_year,
2864 'KR_YEA_DONATION_TOTALS1',
2865 fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
2866 p_stat_parent,
2867 p_tax_parent,
2868 p_pledt_parent,
2869 p_spec_parent,
2870 p_rel_parent,
2871 p_oth_parent,
2872 p_stat_bro_sis,
2873 p_tax_bro_sis,
2874 p_pledt_bro_sis,
2875 p_spec_bro_sis,
2876 p_rel_bro_sis,
2877 p_oth_bro_sis,
2878 1);
2879 else
2880 update per_kr_assignment_yea_info
2881 set ayi_information2 = p_stat_parent,
2882 ayi_information6 = p_tax_parent,
2883 ayi_information8 = p_pledt_parent,
2884 ayi_information10 = p_spec_parent,
2885 ayi_information12 = p_rel_parent,
2886 ayi_information16 = p_oth_parent,
2887 ayi_information3 = p_stat_bro_sis,
2888 ayi_information7 = p_tax_bro_sis,
2889 ayi_information9 = p_pledt_bro_sis,
2890 ayi_information11 = p_spec_bro_sis,
2891 ayi_information13 = p_rel_bro_sis,
2892 ayi_information17 = p_oth_bro_sis
2893 where assignment_id = p_assignment_id
2894 and information_type = 'KR_YEA_DONATION_TOTALS1'
2895 and target_year = p_target_year;
2896 end if;
2897 else
2898 delete
2899 from per_kr_assignment_yea_info
2900 where assignment_id = p_assignment_id
2901 and information_type in ('KR_YEA_DONATION_TOTALS','KR_YEA_DONATION_TOTALS1')
2902 and target_year = p_target_year;
2903 end if;
2904 else
2905 if(p_stat_self > 0 or
2906 p_stat_spouse > 0 or
2907 p_stat_child > 0 or
2908 p_pol_self > 0 or
2909 p_tax_self > 0 or
2910 p_tax_spouse > 0 or
2911 p_tax_child > 0 or
2912 p_pledt_self > 0 or
2913 p_pledt_spouse > 0 or
2914 p_pledt_child > 0 or
2915 p_esoa_self > 0 or
2916 p_spec_self > 0 or
2917 p_spec_spouse > 0 or
2918 p_spec_child > 0 or
2919 p_rel_self > 0 or
2920 p_rel_spouse > 0 or
2921 p_rel_child > 0 or
2922 p_oth_self > 0 or
2923 p_oth_spouse > 0 or
2924 p_oth_child > 0 or
2925 p_stat_parent > 0 or
2926 p_stat_bro_sis > 0 or
2927 p_tax_parent > 0 or
2928 p_tax_bro_sis > 0 or
2929 p_pledt_parent > 0 or
2930 p_pledt_bro_sis > 0 or
2931 p_spec_parent > 0 or
2932 p_spec_bro_sis > 0 or
2933 p_rel_parent > 0 or
2934 p_rel_bro_sis > 0 or
2935 p_oth_parent > 0 or
2936 p_oth_bro_sis > 0 ) then
2937 --
2938 insert into per_kr_assignment_yea_info
2939 (assignment_yea_info_id,
2940 assignment_id,
2941 information_type,
2942 target_year,
2943 ayi_information_category,
2944 ayi_information1,
2945 ayi_information2,
2946 ayi_information4,
2947 ayi_information3,
2948 ayi_information5,
2949 ayi_information8,
2950 ayi_information10,
2951 ayi_information9,
2952 ayi_information11,
2953 ayi_information13,
2954 ayi_information12,
2955 ayi_information14,
2956 ayi_information16,
2957 ayi_information15,
2958 ayi_information17,
2959 ayi_information19,
2960 ayi_information18,
2961 ayi_information20,
2962 ayi_information23,
2963 ayi_information25,
2964 ayi_information24,
2965 object_version_number)
2966 values
2967 (PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
2968 p_assignment_id,
2969 'KR_YEA_DONATION_TOTALS',
2970 p_target_year,
2971 'KR_YEA_DONATION_TOTALS',
2972 fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
2973 p_stat_self,
2974 p_stat_spouse,
2975 p_stat_child,
2976 p_pol_self ,
2977 p_tax_self ,
2978 p_tax_spouse,
2979 p_tax_child ,
2980 p_pledt_self,
2981 p_pledt_spouse,
2982 p_pledt_child,
2983 p_spec_self ,
2984 p_spec_spouse,
2985 p_spec_child,
2986 p_rel_self,
2987 p_rel_spouse,
2988 p_rel_child ,
2989 p_esoa_self ,
2990 p_oth_self ,
2991 p_oth_spouse,
2992 p_oth_child,
2993 1);
2994 --
2995 insert into per_kr_assignment_yea_info
2996 (assignment_yea_info_id,
2997 assignment_id,
2998 information_type,
2999 target_year,
3000 ayi_information_category,
3001 ayi_information1,
3002 ayi_information2,
3003 ayi_information6,
3004 ayi_information8,
3005 ayi_information10,
3006 ayi_information12,
3007 ayi_information16,
3008 ayi_information3,
3009 ayi_information7,
3010 ayi_information9,
3011 ayi_information11,
3012 ayi_information13,
3013 ayi_information17,
3014 object_version_number)
3015 values
3016 (PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
3017 p_assignment_id,
3018 'KR_YEA_DONATION_TOTALS1',
3019 p_target_year,
3020 'KR_YEA_DONATION_TOTALS1',
3021 fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
3022 p_stat_parent,
3023 p_tax_parent,
3024 p_pledt_parent,
3025 p_spec_parent,
3026 p_rel_parent,
3027 p_oth_parent,
3028 p_stat_bro_sis,
3029 p_tax_bro_sis,
3030 p_pledt_bro_sis,
3031 p_spec_bro_sis,
3032 p_rel_bro_sis,
3033 p_oth_bro_sis,
3034 1);
3035 end if;
3036 --
3037 end if;
3038 --
3039 end;
3040 --
3041 --------------------------------------------------------------------------------------
3042 -- Bug 9393732
3043 --------------------------------------------------------------------------------------
3044 procedure calc_hou_rent_fields( p_contract_st_date in varchar2,
3045 p_contract_end_date in varchar2,
3046 p_total_monthly_rent in number,
3047 p_target_year in number,
3048 p_contract_days out nocopy number,
3049 p_eligible_amt out nocopy number)
3050 --
3051 is
3052 --
3053 l_contract_st_date date;
3054 l_contract_end_date date;
3055 l_start_date date;
3056 l_end_date date;
3057 l_sdate date;
3058 l_edate date;
3059 l_days number;
3060 l_total_days number;
3061 --
3062 begin
3063 --
3064 l_contract_st_date := fnd_date.canonical_to_date(p_contract_st_date);
3065 l_contract_end_date := fnd_date.canonical_to_date(p_contract_end_date);
3066 l_start_date := to_date('01-01-'||p_target_year,'DD-MM-YYYY');
3067 l_end_date := to_date('31-12-'||p_target_year,'DD-MM-YYYY');
3068 l_total_days := (l_contract_end_date - l_contract_st_date) + 1;
3069 --
3070 if (l_contract_st_date < l_start_date) then
3071 l_sdate := l_start_date;
3072 else
3073 l_sdate := l_contract_st_date;
3074 end if;
3075 --
3076 if (l_contract_end_date > l_end_date) then
3077 l_edate := l_end_date;
3078 else
3079 l_edate := l_contract_end_date;
3080 end if;
3081 --
3082 l_days := (l_edate - l_sdate) + 1;
3083 if l_days > 0 then
3084 p_contract_days := trunc(l_days);
3085 else
3086 p_contract_days := 0;
3087 end if;
3088 --
3089 p_eligible_amt := trunc((p_total_monthly_rent * p_contract_days)/l_total_days);
3090 --
3091 end;
3092 --
3093 ---------------------------------------------------------------------------------------
3094 --
3095 end pay_kr_yea_sshr_utils_pkg;