[Home] [Help]
PACKAGE BODY: APPS.PER_KR_EXTRA_AEI_RULES
Source
1 package body per_kr_extra_aei_rules as
2 /* $Header: pekrexae.pkb 120.24.12020000.7 2013/01/15 09:12:35 scireddy ship $ */
3 --
4
5 procedure chk_information_type_unique(
6 p_assignment_extra_info_id in number,
7 p_assignment_id in number,
8 p_information_type in varchar2,
9 p_aei_information1 in varchar2)
10 is
11 l_effective_date date;
12 l_dummy varchar2(1);
13 cursor csr_lck is
14 select null
15 from per_periods_of_service pds,
16 per_assignments_f asg
17 where asg.assignment_id = p_assignment_id
18 and l_effective_date
19 between effective_start_date and effective_end_date
20 and pds.period_of_service_id = asg.period_of_service_id
21 for update of pds.period_of_service_id nowait;
22 cursor csr_unique is
23 select 'Y'
24 from dual
25 where not exists(
26 select null
27 from per_assignment_extra_info
28 where assignment_id = p_assignment_id
29 and assignment_extra_info_id <> p_assignment_extra_info_id
30 and information_type = p_information_type
31 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY'));
32 begin
33 if p_information_type in ('KR_YEA_SP_TAX_EXEM_INFO',
34 'KR_YEA_TAX_EXEM_INFO',
35 'KR_YEA_TAX_BREAK_INFO',
36 'KR_YEA_OVS_TAX_BREAK_INFO') then
37 l_effective_date := fnd_date.canonical_to_date(p_aei_information1);
38 --
39 -- Lock first assignment record in this calendar year
40 -- to guarantee uniqueness.
41 --
42 open csr_lck;
43 close csr_lck;
44 --
45 -- Check whether the information_type is unique.
46 --
47 open csr_unique;
48 fetch csr_unique into l_dummy;
49 if csr_unique%NOTFOUND then
50 close csr_unique;
51 fnd_message.set_name('PAY', 'PAY_KR_AEI_TYPE_UNIQUE_ERROR');
52 fnd_message.raise_error;
53 end if;
54 close csr_unique;
55 end if;
56 end chk_information_type_unique;
57 -----------------------------------------------------------------------------
58 -- Bug 7142612
59 -----------------------------------------------------------------------------
60 procedure chk_insert_update(
61 p_assignment_id in number,
62 p_information_type in varchar2,
63 p_aei_information1 in varchar2,
64 p_aei_information3 in varchar2,
65 p_aei_information12 in varchar2,
66 p_aei_information13 in varchar2)
67 -----------------------------------------------------------------------------
68 is
69 l_nts_amt number;
70 l_oth_amt number;
71 l_cont_rel_id number;
72 l_cei_ovn NUMBER;
73 l_contact_extra_info_id NUMBER;
74 l_effective_date DATE;
75
76 l_cei_information1 varchar2(30);
77 l_cei_information2 varchar2(30);
78 l_cei_information3 varchar2(30);
79 l_cei_information4 varchar2(30);
80 l_cei_information5 varchar2(30);
81 l_cei_information6 varchar2(30);
82 l_cei_information7 varchar2(30);
83 l_cei_information8 varchar2(30);
84 l_cei_information9 varchar2(30);
85 l_cei_information10 varchar2(30);
86 l_cei_information11 varchar2(30);
87 l_cei_information12 varchar2(30);
88 l_cei_information13 varchar2(30);
89 l_cei_information14 varchar2(30);
90 l_cei_information15 varchar2(30);
91 l_cei_information16 varchar2(30); -- Bug 9737699
92 l_cei_information17 varchar2(30); -- Bug 9737699
93 l_cei_information18 varchar2(30); -- Bug 14219478
94 l_cei_information19 varchar2(30); -- Bug 14219478
95 l_cei_information20 varchar2(30); -- Bug 14219478
96 l_cei_information21 varchar2(30); -- Bug 14219478
97 l_cei_information22 varchar2(30); -- Bug 14219478
98 l_cei_information23 varchar2(30); -- Bug 14219478
99 l_cei_information24 varchar2(30); -- Bug 14219478
100 l_object_version_number number;
101 l_effective_start_date date;
102 l_effective_end_date date;
103 --
104 cursor csr_nts(l_aei_information12 in varchar2,l_aei_information13 in varchar2, p_effective_date in date) is
105 select sum(nvl(aei_information3,0))
106 from per_assignment_extra_info
107 where assignment_id = p_assignment_id
108 and information_type = p_information_type
109 and aei_information12 = l_aei_information12
110 and aei_information13 = l_aei_information13
111 and aei_information15 = '1'
112 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
113 --
114 cursor csr_oth(l_aei_information12 in varchar2,l_aei_information13 in varchar2, p_effective_date in date) is
115 select sum(nvl(aei_information3,0))
116 from per_assignment_extra_info
117 where assignment_id = p_assignment_id
118 and information_type = p_information_type
119 and aei_information12 = l_aei_information12
120 and aei_information13 = l_aei_information13
121 and aei_information15 = '2'
122 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
123 --
124 cursor csr_cont_rel_id is
125 select contact_relationship_id, national_identifier,
126 decode(nvl(cont_information11,decode(contact_type, 'S','3','P','1','B','5','SISTER','5',
127 'A','4','C','4','R','4','O','4','T','4','6')),'1','4','2','4','5','5','4','3','3','2','7','3','6') code -- Bug 12714266
128 from pay_kr_contact_v
129 where assignment_id = p_assignment_id;
130 --
131 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
132 select cei.contact_extra_info_id,
133 cei.cei_information1,
134 cei.cei_information2,
135 cei.cei_information3,
136 cei.cei_information4,
137 cei.cei_information5,
138 cei.cei_information6,
139 cei.cei_information7,
140 cei.cei_information8,
141 cei.cei_information9,
142 cei.cei_information10,
143 cei.cei_information11,
144 cei.cei_information12,
145 cei.cei_information13,
146 cei.cei_information14,
147 cei.cei_information15,
148 cei.cei_information16, -- Bug 9737699
149 cei.cei_information17, -- Bug 9737699
150 cei.cei_information18, -- Bug 14219478
151 cei.cei_information19, -- Bug 14219478
152 cei.cei_information20, -- Bug 14219478
153 cei.cei_information21, -- Bug 14219478
154 cei.cei_information22, -- Bug 14219478
155 cei.cei_information23, -- Bug 14219478
156 cei.cei_information24, -- Bug 14219478
157 cei.object_version_number,
158 cei.effective_start_date,
159 cei.effective_end_date
160 from per_contact_extra_info_f cei
161 where cei.contact_relationship_id = p_cont_rel_id
162 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
163 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
164 --
165 l_rel_code varchar2(10);
166 l_ni_number varchar2(20);
167 ----------------------------------------------------------------------------------------------
168 begin
169 --
170 if p_information_type = 'KR_YEA_DETAIL_DONATION_INFO' and p_aei_information12 in ('5','4','3','2','1') then
171 --
172 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1;
173 --
174 for i in csr_cont_rel_id loop
175 l_cont_rel_id := i.contact_relationship_id;
176 l_ni_number := i.national_identifier;
177 l_rel_code := i.code;
178 -- Initialized all local variables to null
179 l_contact_extra_info_id := null;
180 l_cei_information1 := null;
181 l_cei_information2 := null;
182 l_cei_information3 := null;
183 l_cei_information4 := null;
184 l_cei_information5 := null;
185 l_cei_information6 := null;
186 l_cei_information7 := null;
187 l_cei_information8 := null;
188 l_cei_information9 := null;
189 l_cei_information10 := null;
190 l_cei_information11 := null;
191 l_cei_information12 := null;
192 l_cei_information13 := null;
193 l_cei_information14 := null;
194 l_cei_information15 := null;
195 l_cei_information16 := null; -- Bug 9737699
196 l_cei_information17 := null; -- Bug 9737699
197 l_cei_information18 := null; -- Bug 14219478
198 l_cei_information19 := null; -- Bug 14219478
199 l_cei_information20 := null; -- Bug 14219478
200 l_cei_information21 := null; -- Bug 14219478
201 l_cei_information22 := null; -- Bug 14219478
202 l_cei_information23 := null; -- Bug 14219478
203 l_cei_information24 := null; -- Bug 14219478
204 l_object_version_number := null;
205 l_effective_start_date := null;
206 l_effective_end_date := null;
207 l_cei_ovn := null;
208 l_nts_amt := null;
209 l_oth_amt := null;
210 --
211 open csr_cei_exists(l_cont_rel_id,l_effective_date);
212 fetch csr_cei_exists into l_contact_extra_info_id,
213 l_cei_information1,
214 l_cei_information2,
215 l_cei_information3,
216 l_cei_information4,
217 l_cei_information5,
218 l_cei_information6,
219 l_cei_information7,
220 l_cei_information8,
221 l_cei_information9,
222 l_cei_information10,
223 l_cei_information11,
224 l_cei_information12,
225 l_cei_information13,
226 l_cei_information14,
227 l_cei_information15,
228 l_cei_information16, -- Bug 9737699
229 l_cei_information17, -- Bug 9737699
230 l_cei_information18, -- Bug 14219478
231 l_cei_information19, -- Bug 14219478
232 l_cei_information20, -- Bug 14219478
233 l_cei_information21, -- Bug 14219478
234 l_cei_information22, -- Bug 14219478
235 l_cei_information23, -- Bug 14219478
236 l_cei_information24, -- Bug 14219478
237 l_object_version_number,
238 l_effective_start_date,
239 l_effective_end_date;
240 close csr_cei_exists;
241 --
242 if l_contact_extra_info_id is not null then
243
244 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
245 fetch csr_nts into l_nts_amt;
246 close csr_nts;
247 --
248 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
249 fetch csr_oth into l_oth_amt;
250 close csr_oth;
251
252 pay_kr_entries_pkg.upd_contact_extra_info
253 ( p_effective_date => l_effective_date,
254 p_contact_extra_info_id => l_contact_extra_info_id,
255 p_contact_relationship_id => l_cont_rel_id,
256 p_contact_ovn => l_object_version_number,
257 p_cei_information1 => l_cei_information1,
258 p_cei_information2 => l_cei_information2,
259 p_cei_information3 => l_cei_information3,
260 p_cei_information4 => l_cei_information4,
261 p_cei_information5 => l_cei_information5,
262 p_cei_information6 => l_cei_information6,
263 p_cei_information7 => l_cei_information7,
264 p_cei_information8 => l_cei_information8,
265 p_cei_information9 => l_cei_information9,
266 p_cei_information10 => l_cei_information10,
267 p_cei_information11 => l_cei_information11,
268 p_cei_information12 => l_cei_information12,
269 p_cei_information13 => l_cei_information13,
270 p_cei_information14 => l_nts_amt,
271 p_cei_information15 => l_oth_amt,
272 p_cei_information16 => l_cei_information16, -- Bug 9737699
273 p_cei_information17 => l_cei_information17, -- Bug 9737699
274 p_cei_information18 => l_cei_information18, -- Bug 14219478
275 p_cei_information19 => l_cei_information19, -- Bug 14219478
276 p_cei_information20 => l_cei_information20, -- Bug 14219478
277 p_cei_information21 => l_cei_information21, -- Bug 14219478
278 p_cei_information22 => l_cei_information22, -- Bug 14219478
279 p_cei_information23 => l_cei_information23, -- Bug 14219478
280 p_cei_information24 => l_cei_information24, -- Bug 14219478
281 p_cei_effective_start_date => l_effective_start_date,
282 p_cei_effective_end_date => l_effective_end_date
283 );
284 --
285 else
286 --
287 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
288 fetch csr_nts into l_nts_amt;
289 close csr_nts;
290 --
291 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
292 fetch csr_oth into l_oth_amt;
293 close csr_oth;
294
295 pay_kr_entries_pkg.create_contact_extra_info
296 ( p_effective_date => l_effective_date,
297 p_contact_extra_info_id => l_contact_extra_info_id,
298 p_contact_relationship_id => l_cont_rel_id,
299 p_contact_ovn => l_cei_ovn,
300 p_cei_information1 => l_cei_information1,
301 p_cei_information2 => l_cei_information2,
302 p_cei_information3 => l_cei_information3,
303 p_cei_information4 => l_cei_information4,
304 p_cei_information5 => l_cei_information5,
305 p_cei_information6 => l_cei_information6,
306 p_cei_information7 => l_cei_information7,
307 p_cei_information8 => l_cei_information8,
308 p_cei_information9 => l_cei_information9,
309 p_cei_information10 => l_cei_information10,
310 p_cei_information11 => l_cei_information11,
311 p_cei_information12 => l_cei_information12,
312 p_cei_information13 => l_cei_information13,
313 p_cei_information14 => l_nts_amt,
314 p_cei_information15 => l_oth_amt,
315 p_cei_information16 => l_cei_information16, -- Bug 9737699
316 p_cei_information17 => l_cei_information17, -- Bug 9737699
317 p_cei_information18 => l_cei_information18, -- Bug 14219478
318 p_cei_information19 => l_cei_information19, -- Bug 14219478
319 p_cei_information20 => l_cei_information20, -- Bug 14219478
320 p_cei_information21 => l_cei_information21, -- Bug 14219478
321 p_cei_information22 => l_cei_information22, -- Bug 14219478
322 p_cei_information23 => l_cei_information23, -- Bug 14219478
323 p_cei_information24 => l_cei_information24, -- Bug 14219478
324 p_cei_effective_start_date => l_effective_start_date,
325 p_cei_effective_end_date => l_effective_end_date
326 );
327 --
328 end if;
329 --
330 end loop;
331 --
332 end if;
333 --
334 end chk_insert_update;
335 --
336 -----------------------------------------------------------------------------
337 -- Bug 7142612
338 -----------------------------------------------------------------------------
339 procedure chk_delete(
340 p_assignment_id_o in number,
341 p_information_type_o in varchar2,
342 p_aei_information1_o in varchar2,
343 p_aei_information3_o in varchar2,
344 p_aei_information12_o in varchar2,
345 p_aei_information13_o in varchar2)
346 -----------------------------------------------------------------------------
347 is
348 l_nts_amt number;
349 l_oth_amt number;
350 l_cont_rel_id number;
351 l_cei_ovn NUMBER;
352 l_contact_extra_info_id NUMBER;
353 l_effective_date DATE;
354
355 l_cei_information1 varchar2(30);
356 l_cei_information2 varchar2(30);
357 l_cei_information3 varchar2(30);
358 l_cei_information4 varchar2(30);
359 l_cei_information5 varchar2(30);
360 l_cei_information6 varchar2(30);
361 l_cei_information7 varchar2(30);
362 l_cei_information8 varchar2(30);
363 l_cei_information9 varchar2(30);
364 l_cei_information10 varchar2(30);
365 l_cei_information11 varchar2(30);
366 l_cei_information12 varchar2(30);
367 l_cei_information13 varchar2(30);
368 l_cei_information14 varchar2(30);
369 l_cei_information15 varchar2(30);
370 l_cei_information16 varchar2(30); -- Bug 9737699
371 l_cei_information17 varchar2(30); -- Bug 9737699
372 l_cei_information18 varchar2(30); -- Bug 14219478
373 l_cei_information19 varchar2(30); -- Bug 14219478
374 l_cei_information20 varchar2(30); -- Bug 14219478
375 l_cei_information21 varchar2(30); -- Bug 14219478
376 l_cei_information22 varchar2(30); -- Bug 14219478
377 l_cei_information23 varchar2(30); -- Bug 14219478
378 l_cei_information24 varchar2(30); -- Bug 14219478
379 l_object_version_number number;
380 l_effective_start_date date;
381 l_effective_end_date date;
382 --
383 cursor csr_nts(l_aei_information12 in varchar2,l_aei_information13 in varchar2, p_effective_date in date) is
384 select sum(nvl(aei_information3,0))
385 from per_assignment_extra_info
386 where assignment_id = p_assignment_id_o
387 and information_type = p_information_type_o
388 and aei_information12 = l_aei_information12
389 and aei_information13 = l_aei_information13
390 and aei_information15 = '1'
391 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
392 --
393 cursor csr_oth(l_aei_information12 in varchar2,l_aei_information13 in varchar2, p_effective_date in date) is
394 select sum(nvl(aei_information3,0))
395 from per_assignment_extra_info
396 where assignment_id = p_assignment_id_o
397 and information_type = p_information_type_o
398 and aei_information12 = l_aei_information12
399 and aei_information13 = l_aei_information13
400 and aei_information15 = '2'
401 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
402 --
403 cursor csr_cont_rel_id is
404 select contact_relationship_id, national_identifier,
405 decode(nvl(cont_information11,decode(contact_type, 'S','3','P','1','B','5','SISTER','5',
406 'A','4','C','4','R','4','O','4','T','4','6')),'1','4','2','4','5','5','4','3','3','2','7','3','6') code -- Bug 12714266
407 from pay_kr_contact_v
408 where assignment_id = p_assignment_id_o;
409 --
410 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
411 select cei.contact_extra_info_id,
412 cei.cei_information1,
413 cei.cei_information2,
414 cei.cei_information3,
415 cei.cei_information4,
416 cei.cei_information5,
417 cei.cei_information6,
418 cei.cei_information7,
419 cei.cei_information8,
420 cei.cei_information9,
421 cei.cei_information10,
422 cei.cei_information11,
423 cei.cei_information12,
424 cei.cei_information13,
425 cei.cei_information14,
426 cei.cei_information15,
427 cei.cei_information16, -- Bug 9737699
428 cei.cei_information17, -- Bug 9737699
429 cei.cei_information18, -- Bug 14219478
430 cei.cei_information19, -- Bug 14219478
431 cei.cei_information20, -- Bug 14219478
432 cei.cei_information21, -- Bug 14219478
433 cei.cei_information22, -- Bug 14219478
434 cei.cei_information23, -- Bug 14219478
435 cei.cei_information24, -- Bug 14219478
436 cei.object_version_number,
437 cei.effective_start_date,
438 cei.effective_end_date
439 from per_contact_extra_info_f cei
440 where cei.contact_relationship_id = p_cont_rel_id
441 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
442 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
443 --
444 l_rel_code varchar2(10);
445 l_ni_number varchar2(20);
446 ----------------------------------------------------------------------------------------------
447 begin
448 --
449 if p_information_type_o = 'KR_YEA_DETAIL_DONATION_INFO' and p_aei_information12_o in ('5','4','3','2','1') then
450 --
451 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1_o),'YYYY'),12) - 1;
452 --
453 for i in csr_cont_rel_id loop
454 l_cont_rel_id := i.contact_relationship_id;
455 l_ni_number := i.national_identifier;
456 l_rel_code := i.code;
457 --
458 -- Initialized all local variables to null
459 l_contact_extra_info_id := null;
460 l_cei_information1 := null;
461 l_cei_information2 := null;
462 l_cei_information3 := null;
463 l_cei_information4 := null;
464 l_cei_information5 := null;
465 l_cei_information6 := null;
466 l_cei_information7 := null;
467 l_cei_information8 := null;
468 l_cei_information9 := null;
469 l_cei_information10 := null;
470 l_cei_information11 := null;
471 l_cei_information12 := null;
472 l_cei_information13 := null;
473 l_cei_information14 := null;
474 l_cei_information15 := null;
475 l_cei_information16 := null; -- Bug 9737699
476 l_cei_information17 := null; -- Bug 9737699
477 l_cei_information18 := null; -- Bug 14219478
478 l_cei_information19 := null; -- Bug 14219478
479 l_cei_information20 := null; -- Bug 14219478
480 l_cei_information21 := null; -- Bug 14219478
481 l_cei_information22 := null; -- Bug 14219478
482 l_cei_information23 := null; -- Bug 14219478
483 l_cei_information24 := null; -- Bug 14219478
484 l_object_version_number := null;
485 l_effective_start_date := null;
486 l_effective_end_date := null;
487 l_cei_ovn := null;
488 l_nts_amt := null;
489 l_oth_amt := null;
490 --
491 open csr_cei_exists(l_cont_rel_id,l_effective_date);
492 fetch csr_cei_exists into l_contact_extra_info_id,
493 l_cei_information1,
494 l_cei_information2,
495 l_cei_information3,
496 l_cei_information4,
497 l_cei_information5,
498 l_cei_information6,
499 l_cei_information7,
500 l_cei_information8,
501 l_cei_information9,
502 l_cei_information10,
503 l_cei_information11,
504 l_cei_information12,
505 l_cei_information13,
506 l_cei_information14,
507 l_cei_information15,
508 l_cei_information16, -- Bug 9737699
509 l_cei_information17, -- Bug 9737699
510 l_cei_information18, -- Bug 14219478
511 l_cei_information19, -- Bug 14219478
512 l_cei_information20, -- Bug 14219478
513 l_cei_information21, -- Bug 14219478
514 l_cei_information22, -- Bug 14219478
515 l_cei_information23, -- Bug 14219478
516 l_cei_information24, -- Bug 14219478
517 l_object_version_number,
518 l_effective_start_date,
519 l_effective_end_date;
520 close csr_cei_exists;
521 --
522 if l_contact_extra_info_id is not null then
523
524 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
525 fetch csr_nts into l_nts_amt;
526 close csr_nts;
527 --
528 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
529 fetch csr_oth into l_oth_amt;
530 close csr_oth;
531
532 pay_kr_entries_pkg.upd_contact_extra_info
533 ( p_effective_date => l_effective_date,
534 p_contact_extra_info_id => l_contact_extra_info_id,
535 p_contact_relationship_id => l_cont_rel_id,
536 p_contact_ovn => l_object_version_number,
537 p_cei_information1 => l_cei_information1,
538 p_cei_information2 => l_cei_information2,
539 p_cei_information3 => l_cei_information3,
540 p_cei_information4 => l_cei_information4,
541 p_cei_information5 => l_cei_information5,
542 p_cei_information6 => l_cei_information6,
543 p_cei_information7 => l_cei_information7,
544 p_cei_information8 => l_cei_information8,
545 p_cei_information9 => l_cei_information9,
546 p_cei_information10 => l_cei_information10,
547 p_cei_information11 => l_cei_information11,
548 p_cei_information12 => l_cei_information12,
549 p_cei_information13 => l_cei_information13,
550 p_cei_information14 => l_nts_amt,
551 p_cei_information15 => l_oth_amt,
552 p_cei_information16 => l_cei_information16, -- Bug 9737699
553 p_cei_information17 => l_cei_information17, -- Bug 9737699
554 p_cei_information18 => l_cei_information18, -- Bug 14219478
555 p_cei_information19 => l_cei_information19, -- Bug 14219478
556 p_cei_information20 => l_cei_information20, -- Bug 14219478
557 p_cei_information21 => l_cei_information21, -- Bug 14219478
558 p_cei_information22 => l_cei_information22, -- Bug 14219478
559 p_cei_information23 => l_cei_information23, -- Bug 14219478
560 p_cei_information24 => l_cei_information24, -- Bug 14219478
561 p_cei_effective_start_date => l_effective_start_date,
562 p_cei_effective_end_date => l_effective_end_date
563 );
564 --
565 else
566 --
567 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
568 fetch csr_nts into l_nts_amt;
569 close csr_nts;
570 --
571 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
572 fetch csr_oth into l_oth_amt;
573 close csr_oth;
574
575 pay_kr_entries_pkg.create_contact_extra_info
576 ( p_effective_date => l_effective_date,
577 p_contact_extra_info_id => l_contact_extra_info_id,
578 p_contact_relationship_id => l_cont_rel_id,
579 p_contact_ovn => l_cei_ovn,
580 p_cei_information1 => l_cei_information1,
581 p_cei_information2 => l_cei_information2,
582 p_cei_information3 => l_cei_information3,
583 p_cei_information4 => l_cei_information4,
584 p_cei_information5 => l_cei_information5,
585 p_cei_information6 => l_cei_information6,
586 p_cei_information7 => l_cei_information7,
587 p_cei_information8 => l_cei_information8,
588 p_cei_information9 => l_cei_information9,
589 p_cei_information10 => l_cei_information10,
590 p_cei_information11 => l_cei_information11,
591 p_cei_information12 => l_cei_information12,
592 p_cei_information13 => l_cei_information13,
593 p_cei_information14 => l_nts_amt,
594 p_cei_information15 => l_oth_amt,
595 p_cei_information16 => l_cei_information16, -- Bug 9737699
596 p_cei_information17 => l_cei_information17, -- Bug 9737699
597 p_cei_information18 => l_cei_information18, -- Bug 14219478
598 p_cei_information19 => l_cei_information19, -- Bug 14219478
599 p_cei_information20 => l_cei_information20, -- Bug 14219478
600 p_cei_information21 => l_cei_information21, -- Bug 14219478
601 p_cei_information22 => l_cei_information22, -- Bug 14219478
602 p_cei_information23 => l_cei_information23, -- Bug 14219478
603 p_cei_information24 => l_cei_information24, -- Bug 14219478
604 p_cei_effective_start_date => l_effective_start_date,
605 p_cei_effective_end_date => l_effective_end_date
606 );
607 --
608 end if;
609 --
610 end loop;
611 --
612 end if;
613 --
614 end chk_delete;
615 --
616 -----------------------------------------------------------------------------
617 -- Bug 7633302
618 -----------------------------------------------------------------------------
619 procedure chk_taxation_period_unique(
620 p_assignment_extra_info_id in number,
621 p_assignment_id in number,
622 p_information_type in varchar2,
623 p_aei_information1 in varchar2,
624 p_aei_information2 in varchar2,
625 p_aei_information3 in varchar2,
626 p_aei_information4 in varchar2,
627 p_aei_information5 in varchar2,
628 p_aei_information7 in varchar2,
629 p_aei_information8 in varchar2)
630 is
631 l_effective_date date;
632 l_dummy number;
633 l_year varchar2(10);
634
635 cursor csr_unique is
636 select count(aei_information2)
637 from per_assignment_extra_info
638 where assignment_id = p_assignment_id
639 and assignment_extra_info_id <> p_assignment_extra_info_id
640 and information_type = p_information_type
641 and aei_information2 = p_aei_information2
642 and nvl(aei_information7,'0') = nvl(p_aei_information7,'0') -- Bug 9213683
643 and nvl(aei_information8,'0') = nvl(p_aei_information8,'0')
644 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
645 --
646 begin
647 if (p_information_type = 'KR_YEA_DONATION_TYPE_DETAIL') then
648 l_effective_date := fnd_date.canonical_to_date(p_aei_information1);
649 l_year := to_char(fnd_date.canonical_to_date(p_aei_information1), 'YYYY');
650 if (fnd_number.canonical_to_number(p_aei_information2) < 2008 or
651 fnd_number.canonical_to_number(p_aei_information2) > fnd_number.canonical_to_number(l_year)) then
652 fnd_message.set_name('PAY', 'PAY_KR_TAX_PD_OUT_OF_RANGE');
653 fnd_message.raise_error;
654 end if;
655 --
656 -- Check whether the taxation_period is unique.
657 --
658 open csr_unique;
659 fetch csr_unique into l_dummy;
660 close csr_unique;
661
662 if l_dummy > 0 then
663 fnd_message.set_name('PAY', 'PAY_KR_TAX_PERIOD_UNIQUE_ERROR');
664 fnd_message.set_token('YEAR',p_aei_information2);
665 fnd_message.raise_error;
666 end if;
667
668 if (fnd_number.canonical_to_number(p_aei_information3) < 0 or
669 fnd_number.canonical_to_number(p_aei_information4) < 0 or
670 fnd_number.canonical_to_number(p_aei_information5) < 0 ) then
671 fnd_message.set_name('PAY' , 'PAY_KR_YEA_NEG_VAL_ERR');
672 fnd_message.raise_error;
673 end if;
674
675 if (fnd_number.canonical_to_number(p_aei_information2) = l_year) then -- Bug 9213683
676 if fnd_number.canonical_to_number(p_aei_information3) > 0 then
677 fnd_message.set_name('PAY', 'PAY_KR_CARRY_OVER_BAL_VAL_ERR');
678 fnd_message.raise_error;
679 end if;
680 end if;
681
682 end if;
683
684 end chk_taxation_period_unique;
685 --
686 -----------------------------------------------------------------------------
687 -- Bug 8200240
688 -----------------------------------------------------------------------------
689 procedure chk_med_insert_update(
690 p_assignment_id in number,
691 p_information_type in varchar2,
692 p_aei_information1 in varchar2)
693 -----------------------------------------------------------------------------
694 is
695 l_nts_amt number;
696 l_oth_amt number;
697 l_cont_rel_id number;
698 l_cei_ovn NUMBER;
699 l_contact_extra_info_id NUMBER;
700 l_effective_date DATE;
701
702 l_cei_information1 varchar2(30);
703 l_cei_information2 varchar2(30);
704 l_cei_information3 varchar2(30);
705 l_cei_information4 varchar2(30);
706 l_cei_information5 varchar2(30);
707 l_cei_information6 varchar2(30);
708 l_cei_information7 varchar2(30);
709 l_cei_information8 varchar2(30);
710 l_cei_information9 varchar2(30);
711 l_cei_information10 varchar2(30);
712 l_cei_information11 varchar2(30);
713 l_cei_information12 varchar2(30);
714 l_cei_information13 varchar2(30);
715 l_cei_information14 varchar2(30);
716 l_cei_information15 varchar2(30);
717 l_cei_information16 varchar2(30); -- Bug 9737699
718 l_cei_information17 varchar2(30); -- Bug 9737699
719 l_cei_information18 varchar2(30); -- Bug 14219478
720 l_cei_information19 varchar2(30); -- Bug 14219478
721 l_cei_information20 varchar2(30); -- Bug 14219478
722 l_cei_information21 varchar2(30); -- Bug 14219478
723 l_cei_information22 varchar2(30); -- Bug 14219478
724 l_cei_information23 varchar2(30); -- Bug 14219478
725 l_cei_information24 varchar2(30); -- Bug 14219478
726 l_object_version_number number;
727 l_effective_start_date date;
728 l_effective_end_date date;
729 --
730 cursor csr_nts(p_aei_information8 in varchar2, p_effective_date in date) is
731 select sum(nvl(aei_information3,0) + nvl(aei_information11,0))
732 from per_assignment_extra_info
733 where assignment_id = p_assignment_id
734 and information_type = p_information_type
735 and aei_information8 = p_aei_information8
736 and aei_information13 = '1'
737 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
738 --
739 cursor csr_oth(p_aei_information8 in varchar2, p_effective_date in date) is
740 select sum(nvl(aei_information3,0) + nvl(aei_information11,0))
741 from per_assignment_extra_info
742 where assignment_id = p_assignment_id
743 and information_type = p_information_type
744 and aei_information8 = p_aei_information8
745 and aei_information13 <> '1'
746 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
747 --
748 cursor csr_cont_rel_id is
749 select distinct pkc.contact_relationship_id, pkc.national_identifier
750 from pay_kr_contact_v pkc
751 where pkc.assignment_id = p_assignment_id;
752 --
753 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
754 select cei.contact_extra_info_id,
755 cei.cei_information1,
756 cei.cei_information2,
757 cei.cei_information3,
758 cei.cei_information4,
759 cei.cei_information5,
760 cei.cei_information6,
761 cei.cei_information7,
762 cei.cei_information8,
763 cei.cei_information9,
764 cei.cei_information10,
765 cei.cei_information11,
766 cei.cei_information12,
767 cei.cei_information13,
768 cei.cei_information14,
769 cei.cei_information15,
770 cei.cei_information16, -- Bug 9737699
771 cei.cei_information17, -- Bug 9737699
772 cei.cei_information18, -- Bug 14219478
773 cei.cei_information19, -- Bug 14219478
774 cei.cei_information20, -- Bug 14219478
775 cei.cei_information21, -- Bug 14219478
776 cei.cei_information22, -- Bug 14219478
777 cei.cei_information23, -- Bug 14219478
778 cei.cei_information24, -- Bug 14219478
779 cei.object_version_number,
780 cei.effective_start_date,
781 cei.effective_end_date
782 from per_contact_extra_info_f cei
783 where cei.contact_relationship_id = p_cont_rel_id
784 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
785 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
786 --
787 l_ni_number varchar2(20);
788 ----------------------------------------------------------------------------------------------
789 begin
790 --
791 if p_information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' then
792 --
793 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1;
794 --
795 for i in csr_cont_rel_id loop
796 l_cont_rel_id := i.contact_relationship_id;
797 l_ni_number := i.national_identifier;
798 -- Initialized all local variables to null
799 l_contact_extra_info_id := null;
800 l_cei_information1 := null;
801 l_cei_information2 := null;
802 l_cei_information3 := null;
803 l_cei_information4 := null;
804 l_cei_information5 := null;
805 l_cei_information6 := null;
806 l_cei_information7 := null;
807 l_cei_information8 := null;
808 l_cei_information9 := null;
809 l_cei_information10 := null;
810 l_cei_information11 := null;
811 l_cei_information12 := null;
812 l_cei_information13 := null;
813 l_cei_information14 := null;
814 l_cei_information15 := null;
815 l_cei_information16 := null; -- Bug 9737699
816 l_cei_information17 := null; -- Bug 9737699
817 l_cei_information18 := null; -- Bug 14219478
818 l_cei_information19 := null; -- Bug 14219478
819 l_cei_information20 := null; -- Bug 14219478
820 l_cei_information21 := null; -- Bug 14219478
821 l_cei_information22 := null; -- Bug 14219478
822 l_cei_information23 := null; -- Bug 14219478
823 l_cei_information24 := null; -- Bug 14219478
824 l_object_version_number := null;
825 l_effective_start_date := null;
826 l_effective_end_date := null;
827 l_cei_ovn := null;
828 l_nts_amt := null;
829 l_oth_amt := null;
830 --
831 open csr_cei_exists(l_cont_rel_id,l_effective_date);
832 fetch csr_cei_exists into l_contact_extra_info_id,
833 l_cei_information1,
834 l_cei_information2,
835 l_cei_information3,
836 l_cei_information4,
837 l_cei_information5,
838 l_cei_information6,
839 l_cei_information7,
840 l_cei_information8,
841 l_cei_information9,
842 l_cei_information10,
843 l_cei_information11,
844 l_cei_information12,
845 l_cei_information13,
846 l_cei_information14,
847 l_cei_information15,
848 l_cei_information16, -- Bug 9737699
849 l_cei_information17, -- Bug 9737699
850 l_cei_information18, -- Bug 14219478
851 l_cei_information19, -- Bug 14219478
852 l_cei_information20, -- Bug 14219478
853 l_cei_information21, -- Bug 14219478
854 l_cei_information22, -- Bug 14219478
855 l_cei_information23, -- Bug 14219478
856 l_cei_information24, -- Bug 14219478
857 l_object_version_number,
858 l_effective_start_date,
859 l_effective_end_date;
860 close csr_cei_exists;
861 --
862 if l_contact_extra_info_id is not null then
863
864 open csr_nts(l_ni_number,l_effective_date);
865 fetch csr_nts into l_nts_amt;
866 close csr_nts;
867 --
868 open csr_oth(l_ni_number,l_effective_date);
869 fetch csr_oth into l_oth_amt;
870 close csr_oth;
871
872 pay_kr_entries_pkg.upd_contact_extra_info
873 ( p_effective_date => l_effective_date,
874 p_contact_extra_info_id => l_contact_extra_info_id,
875 p_contact_relationship_id => l_cont_rel_id,
876 p_contact_ovn => l_object_version_number,
877 p_cei_information1 => l_cei_information1,
878 p_cei_information2 => l_cei_information2,
879 p_cei_information3 => l_nts_amt,
880 p_cei_information4 => l_oth_amt,
881 p_cei_information5 => l_cei_information5,
882 p_cei_information6 => l_cei_information6,
883 p_cei_information7 => l_cei_information7,
884 p_cei_information8 => l_cei_information8,
885 p_cei_information9 => l_cei_information9,
886 p_cei_information10 => l_cei_information10,
887 p_cei_information11 => l_cei_information11,
888 p_cei_information12 => l_cei_information12,
889 p_cei_information13 => l_cei_information13,
890 p_cei_information14 => l_cei_information14,
891 p_cei_information15 => l_cei_information15,
892 p_cei_information16 => l_cei_information16, -- Bug 9737699
893 p_cei_information17 => l_cei_information17, -- Bug 9737699
894 p_cei_information18 => l_cei_information18, -- Bug 14219478
895 p_cei_information19 => l_cei_information19, -- Bug 14219478
896 p_cei_information20 => l_cei_information20, -- Bug 14219478
897 p_cei_information21 => l_cei_information21, -- Bug 14219478
898 p_cei_information22 => l_cei_information22, -- Bug 14219478
899 p_cei_information23 => l_cei_information23, -- Bug 14219478
900 p_cei_information24 => l_cei_information24, -- Bug 14219478
901 p_cei_effective_start_date => l_effective_start_date,
902 p_cei_effective_end_date => l_effective_end_date
903 );
904 --
905 else
906 --
907 open csr_nts(l_ni_number,l_effective_date);
908 fetch csr_nts into l_nts_amt;
909 close csr_nts;
910 --
911 open csr_oth(l_ni_number,l_effective_date);
912 fetch csr_oth into l_oth_amt;
913 close csr_oth;
914
915 pay_kr_entries_pkg.create_contact_extra_info
916 ( p_effective_date => l_effective_date,
917 p_contact_extra_info_id => l_contact_extra_info_id,
918 p_contact_relationship_id => l_cont_rel_id,
919 p_contact_ovn => l_cei_ovn,
920 p_cei_information1 => l_cei_information1,
921 p_cei_information2 => l_cei_information2,
922 p_cei_information3 => l_nts_amt,
923 p_cei_information4 => l_oth_amt,
924 p_cei_information5 => l_cei_information5,
925 p_cei_information6 => l_cei_information6,
926 p_cei_information7 => l_cei_information7,
927 p_cei_information8 => l_cei_information8,
928 p_cei_information9 => l_cei_information9,
929 p_cei_information10 => l_cei_information10,
930 p_cei_information11 => l_cei_information11,
931 p_cei_information12 => l_cei_information12,
932 p_cei_information13 => l_cei_information13,
933 p_cei_information14 => l_cei_information14,
934 p_cei_information15 => l_cei_information15,
935 p_cei_information16 => l_cei_information16, -- Bug 9737699
936 p_cei_information17 => l_cei_information17, -- Bug 9737699
937 p_cei_information18 => l_cei_information18, -- Bug 14219478
938 p_cei_information19 => l_cei_information19, -- Bug 14219478
939 p_cei_information20 => l_cei_information20, -- Bug 14219478
940 p_cei_information21 => l_cei_information21, -- Bug 14219478
941 p_cei_information22 => l_cei_information22, -- Bug 14219478
942 p_cei_information23 => l_cei_information23, -- Bug 14219478
943 p_cei_information24 => l_cei_information24, -- Bug 14219478
944 p_cei_effective_start_date => l_effective_start_date,
945 p_cei_effective_end_date => l_effective_end_date
946 );
947 --
948 end if;
949 --
950 end loop;
951 --
952 end if;
953 --
954 end chk_med_insert_update;
955 --
956 -----------------------------------------------------------------------------
957 -- Bug 8200240
958 -----------------------------------------------------------------------------
959 procedure chk_med_delete(
960 p_assignment_id_o in number,
961 p_information_type_o in varchar2,
962 p_aei_information1_o in varchar2)
963 -----------------------------------------------------------------------------
964 is
965 l_nts_amt number;
966 l_oth_amt number;
967 l_cont_rel_id number;
968 l_cei_ovn NUMBER;
969 l_contact_extra_info_id NUMBER;
970 l_effective_date DATE;
971
972 l_cei_information1 varchar2(30);
973 l_cei_information2 varchar2(30);
974 l_cei_information3 varchar2(30);
975 l_cei_information4 varchar2(30);
976 l_cei_information5 varchar2(30);
977 l_cei_information6 varchar2(30);
978 l_cei_information7 varchar2(30);
979 l_cei_information8 varchar2(30);
980 l_cei_information9 varchar2(30);
981 l_cei_information10 varchar2(30);
982 l_cei_information11 varchar2(30);
983 l_cei_information12 varchar2(30);
984 l_cei_information13 varchar2(30);
985 l_cei_information14 varchar2(30);
986 l_cei_information15 varchar2(30);
987 l_cei_information16 varchar2(30); -- Bug 9737699
988 l_cei_information17 varchar2(30); -- Bug 9737699
989 l_cei_information18 varchar2(30); -- Bug 14219478
990 l_cei_information19 varchar2(30); -- Bug 14219478
991 l_cei_information20 varchar2(30); -- Bug 14219478
992 l_cei_information21 varchar2(30); -- Bug 14219478
993 l_cei_information22 varchar2(30); -- Bug 14219478
994 l_cei_information23 varchar2(30); -- Bug 14219478
995 l_cei_information24 varchar2(30); -- Bug 14219478
996 l_object_version_number number;
997 l_effective_start_date date;
998 l_effective_end_date date;
999 --
1000 cursor csr_nts(p_aei_information8 in varchar2, p_effective_date in date) is
1001 select sum(nvl(aei_information3,0))
1002 from per_assignment_extra_info
1003 where assignment_id = p_assignment_id_o
1004 and information_type = p_information_type_o
1005 and aei_information8 = p_aei_information8
1006 and aei_information13 = '1'
1007 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1008 --
1009 cursor csr_oth(p_aei_information8 in varchar2, p_effective_date in date) is
1010 select sum(nvl(aei_information3,0))
1011 from per_assignment_extra_info
1012 where assignment_id = p_assignment_id_o
1013 and information_type = p_information_type_o
1014 and aei_information8 = p_aei_information8
1015 and aei_information13 <> '1'
1016 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1017 --
1018 cursor csr_cont_rel_id is
1019 select distinct contact_relationship_id, national_identifier
1020 from pay_kr_contact_v pkc
1021 where pkc.assignment_id = p_assignment_id_o;
1022 --
1023 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
1024 select cei.contact_extra_info_id,
1025 cei.cei_information1,
1026 cei.cei_information2,
1027 cei.cei_information3,
1028 cei.cei_information4,
1029 cei.cei_information5,
1030 cei.cei_information6,
1031 cei.cei_information7,
1032 cei.cei_information8,
1033 cei.cei_information9,
1034 cei.cei_information10,
1035 cei.cei_information11,
1036 cei.cei_information12,
1037 cei.cei_information13,
1038 cei.cei_information14,
1039 cei.cei_information15,
1040 cei.cei_information16, -- Bug 9737699
1041 cei.cei_information17, -- Bug 9737699
1042 cei.cei_information18, -- Bug 14219478
1043 cei.cei_information19, -- Bug 14219478
1044 cei.cei_information20, -- Bug 14219478
1045 cei.cei_information21, -- Bug 14219478
1046 cei.cei_information22, -- Bug 14219478
1047 cei.cei_information23, -- Bug 14219478
1048 cei.cei_information24, -- Bug 14219478
1049 cei.object_version_number,
1050 cei.effective_start_date,
1051 cei.effective_end_date
1052 from per_contact_extra_info_f cei
1053 where cei.contact_relationship_id = p_cont_rel_id
1054 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
1055 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
1056 --
1057 l_ni_number varchar2(20);
1058 ----------------------------------------------------------------------------------------------
1059 begin
1060 --
1061 if p_information_type_o = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' then
1062 --
1063 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1_o),'YYYY'),12) - 1;
1064 --
1065 for i in csr_cont_rel_id loop
1066 l_cont_rel_id := i.contact_relationship_id;
1067 l_ni_number := i.national_identifier;
1068 --
1069 -- Initialized all local variables to null
1070 l_contact_extra_info_id := null;
1071 l_cei_information1 := null;
1072 l_cei_information2 := null;
1073 l_cei_information3 := null;
1074 l_cei_information4 := null;
1075 l_cei_information5 := null;
1076 l_cei_information6 := null;
1077 l_cei_information7 := null;
1078 l_cei_information8 := null;
1079 l_cei_information9 := null;
1080 l_cei_information10 := null;
1081 l_cei_information11 := null;
1082 l_cei_information12 := null;
1083 l_cei_information13 := null;
1084 l_cei_information14 := null;
1085 l_cei_information15 := null;
1086 l_cei_information16 := null; -- Bug 9737699
1087 l_cei_information17 := null; -- Bug 9737699
1088 l_cei_information18 := null; -- Bug 14219478
1089 l_cei_information19 := null; -- Bug 14219478
1090 l_cei_information20 := null; -- Bug 14219478
1091 l_cei_information21 := null; -- Bug 14219478
1092 l_cei_information22 := null; -- Bug 14219478
1093 l_cei_information23 := null; -- Bug 14219478
1094 l_cei_information24 := null; -- Bug 14219478
1095 l_object_version_number := null;
1096 l_effective_start_date := null;
1097 l_effective_end_date := null;
1098 l_cei_ovn := null;
1099 l_nts_amt := null;
1100 l_oth_amt := null;
1101 --
1102 open csr_cei_exists(l_cont_rel_id,l_effective_date);
1103 fetch csr_cei_exists into l_contact_extra_info_id,
1104 l_cei_information1,
1105 l_cei_information2,
1106 l_cei_information3,
1107 l_cei_information4,
1108 l_cei_information5,
1109 l_cei_information6,
1110 l_cei_information7,
1111 l_cei_information8,
1112 l_cei_information9,
1113 l_cei_information10,
1114 l_cei_information11,
1115 l_cei_information12,
1116 l_cei_information13,
1117 l_cei_information14,
1118 l_cei_information15,
1119 l_cei_information16, -- Bug 9737699
1120 l_cei_information17, -- Bug 9737699
1121 l_cei_information18, -- Bug 14219478
1122 l_cei_information19, -- Bug 14219478
1123 l_cei_information20, -- Bug 14219478
1124 l_cei_information21, -- Bug 14219478
1125 l_cei_information22, -- Bug 14219478
1126 l_cei_information23, -- Bug 14219478
1127 l_cei_information24, -- Bug 14219478
1128 l_object_version_number,
1129 l_effective_start_date,
1130 l_effective_end_date;
1131 close csr_cei_exists;
1132 --
1133 if l_contact_extra_info_id is not null then
1134
1135 open csr_nts(l_ni_number,l_effective_date);
1136 fetch csr_nts into l_nts_amt;
1137 close csr_nts;
1138 --
1139 open csr_oth(l_ni_number,l_effective_date);
1140 fetch csr_oth into l_oth_amt;
1141 close csr_oth;
1142
1143 pay_kr_entries_pkg.upd_contact_extra_info
1144 ( p_effective_date => l_effective_date,
1145 p_contact_extra_info_id => l_contact_extra_info_id,
1146 p_contact_relationship_id => l_cont_rel_id,
1147 p_contact_ovn => l_object_version_number,
1148 p_cei_information1 => l_cei_information1,
1149 p_cei_information2 => l_cei_information2,
1150 p_cei_information3 => l_nts_amt,
1151 p_cei_information4 => l_oth_amt,
1152 p_cei_information5 => l_cei_information5,
1153 p_cei_information6 => l_cei_information6,
1154 p_cei_information7 => l_cei_information7,
1155 p_cei_information8 => l_cei_information8,
1156 p_cei_information9 => l_cei_information9,
1157 p_cei_information10 => l_cei_information10,
1158 p_cei_information11 => l_cei_information11,
1159 p_cei_information12 => l_cei_information12,
1160 p_cei_information13 => l_cei_information13,
1161 p_cei_information14 => l_cei_information14,
1162 p_cei_information15 => l_cei_information15,
1163 p_cei_information16 => l_cei_information16, -- Bug 9737699
1164 p_cei_information17 => l_cei_information17, -- Bug 9737699
1165 p_cei_information18 => l_cei_information18, -- Bug 14219478
1166 p_cei_information19 => l_cei_information19, -- Bug 14219478
1167 p_cei_information20 => l_cei_information20, -- Bug 14219478
1168 p_cei_information21 => l_cei_information21, -- Bug 14219478
1169 p_cei_information22 => l_cei_information22, -- Bug 14219478
1170 p_cei_information23 => l_cei_information23, -- Bug 14219478
1171 p_cei_information24 => l_cei_information24, -- Bug 14219478
1172 p_cei_effective_start_date => l_effective_start_date,
1173 p_cei_effective_end_date => l_effective_end_date
1174 );
1175 --
1176 else
1177 --
1178 open csr_nts(l_ni_number,l_effective_date);
1179 fetch csr_nts into l_nts_amt;
1180 close csr_nts;
1181 --
1182 open csr_oth(l_ni_number,l_effective_date);
1183 fetch csr_oth into l_oth_amt;
1184 close csr_oth;
1185
1186 pay_kr_entries_pkg.create_contact_extra_info
1187 ( p_effective_date => l_effective_date,
1188 p_contact_extra_info_id => l_contact_extra_info_id,
1189 p_contact_relationship_id => l_cont_rel_id,
1190 p_contact_ovn => l_cei_ovn,
1191 p_cei_information1 => l_cei_information1,
1192 p_cei_information2 => l_cei_information2,
1193 p_cei_information3 => l_nts_amt,
1194 p_cei_information4 => l_oth_amt,
1195 p_cei_information5 => l_cei_information5,
1196 p_cei_information6 => l_cei_information6,
1197 p_cei_information7 => l_cei_information7,
1198 p_cei_information8 => l_cei_information8,
1199 p_cei_information9 => l_cei_information9,
1200 p_cei_information10 => l_cei_information10,
1201 p_cei_information11 => l_cei_information11,
1202 p_cei_information12 => l_cei_information12,
1203 p_cei_information13 => l_cei_information13,
1204 p_cei_information14 => l_cei_information14,
1205 p_cei_information15 => l_cei_information15,
1206 p_cei_information16 => l_cei_information16, -- Bug 9737699
1207 p_cei_information17 => l_cei_information17, -- Bug 9737699
1208 p_cei_information18 => l_cei_information18, -- Bug 14219478
1209 p_cei_information19 => l_cei_information19, -- Bug 14219478
1210 p_cei_information20 => l_cei_information20, -- Bug 14219478
1211 p_cei_information21 => l_cei_information21, -- Bug 14219478
1212 p_cei_information22 => l_cei_information22, -- Bug 14219478
1213 p_cei_information23 => l_cei_information23, -- Bug 14219478
1214 p_cei_information24 => l_cei_information24, -- Bug 14219478
1215 p_cei_effective_start_date => l_effective_start_date,
1216 p_cei_effective_end_date => l_effective_end_date
1217 );
1218 --
1219 end if;
1220 --
1221 end loop;
1222 --
1223 end if;
1224 --
1225 end chk_med_delete;
1226 --
1227 -------------------------------------------------------------------------------
1228 -- Bug 8644512: Added the procedure chk_ntax_earn_unique() to validate the
1229 -- uniqueness of the 'non-taxable code' and 'business registration
1230 -- number' pair.
1231 -------------------------------------------------------------------------------
1232 procedure chk_ntax_earn_unique(
1233 p_assignment_extra_info_id in number,
1234 p_assignment_id in number,
1235 p_information_type in varchar2,
1236 p_aei_information1 in varchar2,
1237 p_aei_information2 in varchar2,
1238 p_aei_information4 in varchar2)
1239 is
1240 l_effective_date date;
1241 l_dummy number;
1242 l_year varchar2(10);
1243
1244 cursor csr_unique is
1245 select count(aei_information2)
1246 from per_assignment_extra_info
1247 where assignment_id = p_assignment_id
1248 and assignment_extra_info_id <> p_assignment_extra_info_id
1249 and information_type = p_information_type
1250 and aei_information2 = p_aei_information2
1251 and aei_information4 = p_aei_information4
1252 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
1253 --
1254 begin
1255 if (p_information_type = 'KR_YEA_NON_TAXABLE_EARN_DETAIL') then
1256 l_effective_date := fnd_date.canonical_to_date(p_aei_information1);
1257 l_year := to_char(fnd_date.canonical_to_date(p_aei_information1), 'YYYY');
1258 --
1259 -- Check whether the 'Non-Taxable Earnings' and 'Business Registration No.' pair is unique.
1260 --
1261 open csr_unique;
1262 fetch csr_unique into l_dummy;
1263 close csr_unique;
1264
1265 if l_dummy > 0 then
1266 fnd_message.set_name('PAY', 'PAY_KR_NTAX_EARN_UNIQUE_ERROR');
1267 fnd_message.set_token('NTAX_CODE',p_aei_information2);
1268 fnd_message.set_token('BUS_REG_NUM',p_aei_information4);
1269 fnd_message.raise_error;
1270 end if;
1271
1272 end if;
1273
1274 end chk_ntax_earn_unique;
1275 --
1276 -----------------------------------------------------------------------------
1277 -- Bug 9079450
1278 -----------------------------------------------------------------------------
1279 procedure chk_dpnt_educ_insert_update(
1280 p_assignment_id in number,
1281 p_information_type in varchar2,
1282 p_aei_information1 in varchar2,
1283 p_aei_information6 in varchar2,
1284 p_aei_information7 in varchar2)
1285 -----------------------------------------------------------------------------
1286 is
1287 l_nts_amt number;
1288 l_oth_amt number;
1289 l_cont_rel_id number;
1290 l_cei_ovn NUMBER;
1291 l_contact_extra_info_id NUMBER;
1292 l_effective_date DATE;
1293
1294 l_cei_information1 varchar2(30);
1295 l_cei_information2 varchar2(30);
1296 l_cei_information3 varchar2(30);
1297 l_cei_information4 varchar2(30);
1298 l_cei_information5 varchar2(30);
1299 l_cei_information6 varchar2(30);
1300 l_cei_information7 varchar2(30);
1301 l_cei_information8 varchar2(30);
1302 l_cei_information9 varchar2(30);
1303 l_cei_information10 varchar2(30);
1304 l_cei_information11 varchar2(30);
1305 l_cei_information12 varchar2(30);
1306 l_cei_information13 varchar2(30);
1307 l_cei_information14 varchar2(30);
1308 l_cei_information15 varchar2(30);
1309 l_cei_information16 varchar2(30); -- Bug 9737699
1310 l_cei_information17 varchar2(30); -- Bug 9737699
1311 l_cei_information18 varchar2(30); -- Bug 14219478
1312 l_cei_information19 varchar2(30); -- Bug 14219478
1313 l_cei_information20 varchar2(30); -- Bug 14219478
1314 l_cei_information21 varchar2(30); -- Bug 14219478
1315 l_cei_information22 varchar2(30); -- Bug 14219478
1316 l_cei_information23 varchar2(30); -- Bug 14219478
1317 l_cei_information24 varchar2(30); -- Bug 14219478
1318 l_object_version_number number;
1319 l_effective_start_date date;
1320 l_effective_end_date date;
1321 --
1322 cursor csr_nts(l_aei_information2 in varchar2,l_aei_information5 in varchar2, p_effective_date in date) is
1323 select sum(nvl(aei_information4,0))
1324 from per_assignment_extra_info
1325 where assignment_id = p_assignment_id
1326 and information_type = p_information_type
1327 and aei_information2 = l_aei_information2
1328 and aei_information5 = l_aei_information5
1329 and aei_information6 = '1'
1330 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1331 --
1332 cursor csr_oth(l_aei_information2 in varchar2,l_aei_information5 in varchar2, p_effective_date in date) is
1333 select sum(nvl(aei_information4,0))
1334 from per_assignment_extra_info
1335 where assignment_id = p_assignment_id
1336 and information_type = p_information_type
1337 and aei_information2 = l_aei_information2
1338 and aei_information5 = l_aei_information5
1339 and aei_information6 = '2'
1340 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1341 --
1342 cursor csr_cont_rel_id is
1343 select contact_relationship_id, national_identifier,
1344 nvl(cont_information11,decode(contact_type,'P','1','S','3',
1345 'A','4','C','4','R','4','O','4','T','4','BROTHER','5','SISTER','5','6') ) code
1346 from pay_kr_contact_v
1347 where assignment_id = p_assignment_id;
1348 --
1349 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
1350 select cei.contact_extra_info_id,
1351 cei.cei_information1,
1352 cei.cei_information2,
1353 cei.cei_information3,
1354 cei.cei_information4,
1355 cei.cei_information5,
1356 cei.cei_information6,
1357 cei.cei_information7,
1358 cei.cei_information8,
1359 cei.cei_information9,
1360 cei.cei_information10,
1361 cei.cei_information11,
1362 cei.cei_information12,
1363 cei.cei_information13,
1364 cei.cei_information14,
1365 cei.cei_information15,
1366 cei.cei_information16, -- Bug 9737699
1367 cei.cei_information17, -- Bug 9737699
1368 cei.cei_information18, -- Bug 14219478
1369 cei.cei_information19, -- Bug 14219478
1370 cei.cei_information20, -- Bug 14219478
1371 cei.cei_information21, -- Bug 14219478
1372 cei.cei_information22, -- Bug 14219478
1373 cei.cei_information23, -- Bug 14219478
1374 cei.cei_information24, -- Bug 14219478
1375 cei.object_version_number,
1376 cei.effective_start_date,
1377 cei.effective_end_date
1378 from per_contact_extra_info_f cei
1379 where cei.contact_relationship_id = p_cont_rel_id
1380 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
1381 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
1382 --
1383 l_rel_code varchar2(10);
1384 l_ni_number varchar2(20);
1385 l_year number(4);
1386 ----------------------------------------------------------------------------------------------
1387 begin
1388 --
1389 if p_information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO' then
1390 --
1391 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1;
1392 l_year := to_number(to_char(l_effective_date,'YYYY'));
1393 if l_year > 2008 then
1394 --
1395 if (p_aei_information6 is null) or (p_aei_information7 is null) then
1396 fnd_message.set_name('PAY', 'PAY_KR_YEA_EDUCATION_REQ_ERR');
1397 fnd_message.raise_error;
1398 end if;
1399 --
1400 for i in csr_cont_rel_id loop
1401 l_cont_rel_id := i.contact_relationship_id;
1402 l_ni_number := i.national_identifier;
1403 l_rel_code := i.code;
1404 -- Initialized all local variables to null
1405 l_contact_extra_info_id := null;
1406 l_cei_information1 := null;
1407 l_cei_information2 := null;
1408 l_cei_information3 := null;
1409 l_cei_information4 := null;
1410 l_cei_information5 := null;
1411 l_cei_information6 := null;
1412 l_cei_information7 := null;
1413 l_cei_information8 := null;
1414 l_cei_information9 := null;
1415 l_cei_information10 := null;
1416 l_cei_information11 := null;
1417 l_cei_information12 := null;
1418 l_cei_information13 := null;
1419 l_cei_information14 := null;
1420 l_cei_information15 := null;
1421 l_cei_information16 := null; -- Bug 9737699
1422 l_cei_information17 := null; -- Bug 9737699
1423 l_cei_information18 := null; -- Bug 14219478
1424 l_cei_information19 := null; -- Bug 14219478
1425 l_cei_information20 := null; -- Bug 14219478
1426 l_cei_information21 := null; -- Bug 14219478
1427 l_cei_information22 := null; -- Bug 14219478
1428 l_cei_information23 := null; -- Bug 14219478
1429 l_cei_information24 := null; -- Bug 14219478
1430 l_object_version_number := null;
1431 l_effective_start_date := null;
1432 l_effective_end_date := null;
1433 l_cei_ovn := null;
1434 l_nts_amt := null;
1435 l_oth_amt := null;
1436 --
1437 open csr_cei_exists(l_cont_rel_id,l_effective_date);
1438 fetch csr_cei_exists into l_contact_extra_info_id,
1439 l_cei_information1,
1440 l_cei_information2,
1441 l_cei_information3,
1442 l_cei_information4,
1443 l_cei_information5,
1444 l_cei_information6,
1445 l_cei_information7,
1446 l_cei_information8,
1447 l_cei_information9,
1448 l_cei_information10,
1449 l_cei_information11,
1450 l_cei_information12,
1451 l_cei_information13,
1452 l_cei_information14,
1453 l_cei_information15,
1454 l_cei_information16, -- Bug 9737699
1455 l_cei_information17, -- Bug 9737699
1456 l_cei_information18, -- Bug 14219478
1457 l_cei_information19, -- Bug 14219478
1458 l_cei_information20, -- Bug 14219478
1459 l_cei_information21, -- Bug 14219478
1460 l_cei_information22, -- Bug 14219478
1461 l_cei_information23, -- Bug 14219478
1462 l_cei_information24, -- Bug 14219478
1463 l_object_version_number,
1464 l_effective_start_date,
1465 l_effective_end_date;
1466 close csr_cei_exists;
1467 --
1468 if l_contact_extra_info_id is not null then
1469
1470 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
1471 fetch csr_nts into l_nts_amt;
1472 close csr_nts;
1473 --
1474 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
1475 fetch csr_oth into l_oth_amt;
1476 close csr_oth;
1477
1478 pay_kr_entries_pkg.upd_contact_extra_info
1479 ( p_effective_date => l_effective_date,
1480 p_contact_extra_info_id => l_contact_extra_info_id,
1481 p_contact_relationship_id => l_cont_rel_id,
1482 p_contact_ovn => l_object_version_number,
1483 p_cei_information1 => l_cei_information1,
1484 p_cei_information2 => l_cei_information2,
1485 p_cei_information3 => l_cei_information3,
1486 p_cei_information4 => l_cei_information4,
1487 p_cei_information5 => l_nts_amt,
1488 p_cei_information6 => l_oth_amt,
1489 p_cei_information7 => l_cei_information7,
1490 p_cei_information8 => l_cei_information8,
1491 p_cei_information9 => l_cei_information9,
1492 p_cei_information10 => l_cei_information10,
1493 p_cei_information11 => l_cei_information11,
1494 p_cei_information12 => l_cei_information12,
1495 p_cei_information13 => l_cei_information13,
1496 p_cei_information14 => l_cei_information14,
1497 p_cei_information15 => l_cei_information15,
1498 p_cei_information16 => l_cei_information16, -- Bug 9737699
1499 p_cei_information17 => l_cei_information17, -- Bug 9737699
1500 p_cei_information18 => l_cei_information18, -- Bug 14219478
1501 p_cei_information19 => l_cei_information19, -- Bug 14219478
1502 p_cei_information20 => l_cei_information20, -- Bug 14219478
1503 p_cei_information21 => l_cei_information21, -- Bug 14219478
1504 p_cei_information22 => l_cei_information22, -- Bug 14219478
1505 p_cei_information23 => l_cei_information23, -- Bug 14219478
1506 p_cei_information24 => l_cei_information24, -- Bug 14219478
1507 p_cei_effective_start_date => l_effective_start_date,
1508 p_cei_effective_end_date => l_effective_end_date
1509 );
1510 --
1511 else
1512 --
1513 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
1514 fetch csr_nts into l_nts_amt;
1515 close csr_nts;
1516 --
1517 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
1518 fetch csr_oth into l_oth_amt;
1519 close csr_oth;
1520
1521 pay_kr_entries_pkg.create_contact_extra_info
1522 ( p_effective_date => l_effective_date,
1523 p_contact_extra_info_id => l_contact_extra_info_id,
1524 p_contact_relationship_id => l_cont_rel_id,
1525 p_contact_ovn => l_cei_ovn,
1526 p_cei_information1 => l_cei_information1,
1527 p_cei_information2 => l_cei_information2,
1528 p_cei_information3 => l_cei_information3,
1529 p_cei_information4 => l_cei_information4,
1530 p_cei_information5 => l_nts_amt,
1531 p_cei_information6 => l_oth_amt,
1532 p_cei_information7 => l_cei_information7,
1533 p_cei_information8 => l_cei_information8,
1534 p_cei_information9 => l_cei_information9,
1535 p_cei_information10 => l_cei_information10,
1536 p_cei_information11 => l_cei_information11,
1537 p_cei_information12 => l_cei_information12,
1538 p_cei_information13 => l_cei_information13,
1539 p_cei_information14 => l_cei_information14,
1540 p_cei_information15 => l_cei_information15,
1541 p_cei_information16 => l_cei_information16, -- Bug 9737699
1542 p_cei_information17 => l_cei_information17, -- Bug 9737699
1543 p_cei_information18 => l_cei_information18, -- Bug 14219478
1544 p_cei_information19 => l_cei_information19, -- Bug 14219478
1545 p_cei_information20 => l_cei_information20, -- Bug 14219478
1546 p_cei_information21 => l_cei_information21, -- Bug 14219478
1547 p_cei_information22 => l_cei_information22, -- Bug 14219478
1548 p_cei_information23 => l_cei_information23, -- Bug 14219478
1549 p_cei_information24 => l_cei_information24, -- Bug 14219478
1550 p_cei_effective_start_date => l_effective_start_date,
1551 p_cei_effective_end_date => l_effective_end_date
1552 );
1553 --
1554 end if;
1555 --
1556 end loop;
1557 --
1558 end if;
1559 --
1560 end if;
1561 --
1562 end chk_dpnt_educ_insert_update;
1563 --
1564 -----------------------------------------------------------------------------
1565 -- Bug 9079450
1566 -----------------------------------------------------------------------------
1567 procedure chk_dpnt_educ_delete(
1568 p_assignment_id_o in number,
1569 p_information_type_o in varchar2,
1570 p_aei_information1_o in varchar2)
1571 -----------------------------------------------------------------------------
1572 is
1573 l_nts_amt number;
1574 l_oth_amt number;
1575 l_cont_rel_id number;
1576 l_cei_ovn NUMBER;
1577 l_contact_extra_info_id NUMBER;
1578 l_effective_date DATE;
1579
1580 l_cei_information1 varchar2(30);
1581 l_cei_information2 varchar2(30);
1582 l_cei_information3 varchar2(30);
1583 l_cei_information4 varchar2(30);
1584 l_cei_information5 varchar2(30);
1585 l_cei_information6 varchar2(30);
1586 l_cei_information7 varchar2(30);
1587 l_cei_information8 varchar2(30);
1588 l_cei_information9 varchar2(30);
1589 l_cei_information10 varchar2(30);
1590 l_cei_information11 varchar2(30);
1591 l_cei_information12 varchar2(30);
1592 l_cei_information13 varchar2(30);
1593 l_cei_information14 varchar2(30);
1594 l_cei_information15 varchar2(30);
1595 l_cei_information16 varchar2(30); -- Bug 9737699
1596 l_cei_information17 varchar2(30); -- Bug 9737699
1597 l_cei_information18 varchar2(30); -- Bug 14219478
1598 l_cei_information19 varchar2(30); -- Bug 14219478
1599 l_cei_information20 varchar2(30); -- Bug 14219478
1600 l_cei_information21 varchar2(30); -- Bug 14219478
1601 l_cei_information22 varchar2(30); -- Bug 14219478
1602 l_cei_information23 varchar2(30); -- Bug 14219478
1603 l_cei_information24 varchar2(30); -- Bug 14219478
1604 l_object_version_number number;
1605 l_effective_start_date date;
1606 l_effective_end_date date;
1607 --
1608 cursor csr_nts(l_aei_information2 in varchar2,l_aei_information5 in varchar2, p_effective_date in date) is
1609 select sum(nvl(aei_information4,0))
1610 from per_assignment_extra_info
1611 where assignment_id = p_assignment_id_o
1612 and information_type = p_information_type_o
1613 and aei_information2 = l_aei_information2
1614 and aei_information5 = l_aei_information5
1615 and aei_information6 = '1'
1616 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1617 --
1618 cursor csr_oth(l_aei_information2 in varchar2,l_aei_information5 in varchar2, p_effective_date in date) is
1619 select sum(nvl(aei_information4,0))
1620 from per_assignment_extra_info
1621 where assignment_id = p_assignment_id_o
1622 and information_type = p_information_type_o
1623 and aei_information2 = l_aei_information2
1624 and aei_information5 = l_aei_information5
1625 and aei_information6 = '2'
1626 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1627 --
1628 cursor csr_cont_rel_id is
1629 select contact_relationship_id, national_identifier,
1630 nvl(cont_information11,decode(contact_type,'P','1','S','3',
1631 'A','4','C','4','R','4','O','4','T','4','BROTHER','5','SISTER','5','6')) code
1632 from pay_kr_contact_v
1633 where assignment_id = p_assignment_id_o;
1634 --
1635 cursor csr_cei_exists(p_cont_rel_id in number,p_effective_date in date) is
1636 select cei.contact_extra_info_id,
1637 cei.cei_information1,
1638 cei.cei_information2,
1639 cei.cei_information3,
1640 cei.cei_information4,
1641 cei.cei_information5,
1642 cei.cei_information6,
1643 cei.cei_information7,
1644 cei.cei_information8,
1645 cei.cei_information9,
1646 cei.cei_information10,
1647 cei.cei_information11,
1648 cei.cei_information12,
1649 cei.cei_information13,
1650 cei.cei_information14,
1651 cei.cei_information15,
1652 cei.cei_information16, -- Bug 9737699
1653 cei.cei_information17, -- Bug 9737699
1654 cei.cei_information18, -- Bug 14219478
1655 cei.cei_information19, -- Bug 14219478
1656 cei.cei_information20, -- Bug 14219478
1657 cei.cei_information21, -- Bug 14219478
1658 cei.cei_information22, -- Bug 14219478
1659 cei.cei_information23, -- Bug 14219478
1660 cei.cei_information24, -- Bug 14219478
1661 cei.object_version_number,
1662 cei.effective_start_date,
1663 cei.effective_end_date
1664 from per_contact_extra_info_f cei
1665 where cei.contact_relationship_id = p_cont_rel_id
1666 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
1667 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
1668 --
1669 l_rel_code varchar2(10);
1670 l_ni_number varchar2(20);
1671 l_year number(4);
1672 ----------------------------------------------------------------------------------------------
1673 begin
1674 --
1675 if p_information_type_o = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO' then
1676 --
1677 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1_o),'YYYY'),12) - 1;
1678 l_year := to_number(to_char(l_effective_date,'YYYY'));
1679 if l_year > 2008 then
1680 --
1681 for i in csr_cont_rel_id loop
1682 l_cont_rel_id := i.contact_relationship_id;
1683 l_ni_number := i.national_identifier;
1684 l_rel_code := i.code;
1685 --
1686 -- Initialized all local variables to null
1687 l_contact_extra_info_id := null;
1688 l_cei_information1 := null;
1689 l_cei_information2 := null;
1690 l_cei_information3 := null;
1691 l_cei_information4 := null;
1692 l_cei_information5 := null;
1693 l_cei_information6 := null;
1694 l_cei_information7 := null;
1695 l_cei_information8 := null;
1696 l_cei_information9 := null;
1697 l_cei_information10 := null;
1698 l_cei_information11 := null;
1699 l_cei_information12 := null;
1700 l_cei_information13 := null;
1701 l_cei_information14 := null;
1702 l_cei_information15 := null;
1703 l_cei_information16 := null; -- Bug 9737699
1704 l_cei_information17 := null; -- Bug 9737699
1705 l_cei_information18 := null; -- Bug 14219478
1706 l_cei_information19 := null; -- Bug 14219478
1707 l_cei_information20 := null; -- Bug 14219478
1708 l_cei_information21 := null; -- Bug 14219478
1709 l_cei_information22 := null; -- Bug 14219478
1710 l_cei_information23 := null; -- Bug 14219478
1711 l_cei_information24 := null; -- Bug 14219478
1712 l_object_version_number := null;
1713 l_effective_start_date := null;
1714 l_effective_end_date := null;
1715 l_cei_ovn := null;
1716 l_nts_amt := null;
1717 l_oth_amt := null;
1718 --
1719 open csr_cei_exists(l_cont_rel_id,l_effective_date);
1720 fetch csr_cei_exists into l_contact_extra_info_id,
1721 l_cei_information1,
1722 l_cei_information2,
1723 l_cei_information3,
1724 l_cei_information4,
1725 l_cei_information5,
1726 l_cei_information6,
1727 l_cei_information7,
1728 l_cei_information8,
1729 l_cei_information9,
1730 l_cei_information10,
1731 l_cei_information11,
1732 l_cei_information12,
1733 l_cei_information13,
1734 l_cei_information14,
1735 l_cei_information15,
1736 l_cei_information16, -- Bug 9737699
1737 l_cei_information17, -- Bug 9737699
1738 l_cei_information18, -- Bug 14219478
1739 l_cei_information19, -- Bug 14219478
1740 l_cei_information20, -- Bug 14219478
1741 l_cei_information21, -- Bug 14219478
1742 l_cei_information22, -- Bug 14219478
1743 l_cei_information23, -- Bug 14219478
1744 l_cei_information24, -- Bug 14219478
1745 l_object_version_number,
1746 l_effective_start_date,
1747 l_effective_end_date;
1748 close csr_cei_exists;
1749 --
1750 if l_contact_extra_info_id is not null then
1751
1752 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
1753 fetch csr_nts into l_nts_amt;
1754 close csr_nts;
1755 --
1756 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
1757 fetch csr_oth into l_oth_amt;
1758 close csr_oth;
1759
1760 pay_kr_entries_pkg.upd_contact_extra_info
1761 ( p_effective_date => l_effective_date,
1762 p_contact_extra_info_id => l_contact_extra_info_id,
1763 p_contact_relationship_id => l_cont_rel_id,
1764 p_contact_ovn => l_object_version_number,
1765 p_cei_information1 => l_cei_information1,
1766 p_cei_information2 => l_cei_information2,
1767 p_cei_information3 => l_cei_information3,
1768 p_cei_information4 => l_cei_information4,
1769 p_cei_information5 => l_nts_amt,
1770 p_cei_information6 => l_oth_amt,
1771 p_cei_information7 => l_cei_information7,
1772 p_cei_information8 => l_cei_information8,
1773 p_cei_information9 => l_cei_information9,
1774 p_cei_information10 => l_cei_information10,
1775 p_cei_information11 => l_cei_information11,
1776 p_cei_information12 => l_cei_information12,
1777 p_cei_information13 => l_cei_information13,
1778 p_cei_information14 => l_cei_information14,
1779 p_cei_information15 => l_cei_information15,
1780 p_cei_information16 => l_cei_information16, -- Bug 9737699
1781 p_cei_information17 => l_cei_information17, -- Bug 9737699
1782 p_cei_information18 => l_cei_information18, -- Bug 14219478
1783 p_cei_information19 => l_cei_information19, -- Bug 14219478
1784 p_cei_information20 => l_cei_information20, -- Bug 14219478
1785 p_cei_information21 => l_cei_information21, -- Bug 14219478
1786 p_cei_information22 => l_cei_information22, -- Bug 14219478
1787 p_cei_information23 => l_cei_information23, -- Bug 14219478
1788 p_cei_information24 => l_cei_information24, -- Bug 14219478
1789 p_cei_effective_start_date => l_effective_start_date,
1790 p_cei_effective_end_date => l_effective_end_date
1791 );
1792 --
1793 else
1794 --
1795 open csr_nts(l_rel_code,l_ni_number,l_effective_date);
1796 fetch csr_nts into l_nts_amt;
1797 close csr_nts;
1798 --
1799 open csr_oth(l_rel_code,l_ni_number,l_effective_date);
1800 fetch csr_oth into l_oth_amt;
1801 close csr_oth;
1802
1803 pay_kr_entries_pkg.create_contact_extra_info
1804 ( p_effective_date => l_effective_date,
1805 p_contact_extra_info_id => l_contact_extra_info_id,
1806 p_contact_relationship_id => l_cont_rel_id,
1807 p_contact_ovn => l_cei_ovn,
1808 p_cei_information1 => l_cei_information1,
1809 p_cei_information2 => l_cei_information2,
1810 p_cei_information3 => l_cei_information3,
1811 p_cei_information4 => l_cei_information4,
1812 p_cei_information5 => l_nts_amt,
1813 p_cei_information6 => l_oth_amt,
1814 p_cei_information7 => l_cei_information7,
1815 p_cei_information8 => l_cei_information8,
1816 p_cei_information9 => l_cei_information9,
1817 p_cei_information10 => l_cei_information10,
1818 p_cei_information11 => l_cei_information11,
1819 p_cei_information12 => l_cei_information12,
1820 p_cei_information13 => l_cei_information13,
1821 p_cei_information14 => l_cei_information14,
1822 p_cei_information15 => l_cei_information15,
1823 p_cei_information16 => l_cei_information16, -- Bug 9737699
1824 p_cei_information17 => l_cei_information17, -- Bug 9737699
1825 p_cei_information18 => l_cei_information18, -- Bug 14219478
1826 p_cei_information19 => l_cei_information19, -- Bug 14219478
1827 p_cei_information20 => l_cei_information20, -- Bug 14219478
1828 p_cei_information21 => l_cei_information21, -- Bug 14219478
1829 p_cei_information22 => l_cei_information22, -- Bug 14219478
1830 p_cei_information23 => l_cei_information23, -- Bug 14219478
1831 p_cei_information24 => l_cei_information24, -- Bug 14219478
1832 p_cei_effective_start_date => l_effective_start_date,
1833 p_cei_effective_end_date => l_effective_end_date
1834 );
1835 --
1836 end if;
1837 --
1838 end loop;
1839 --
1840 end if;
1841 end if;
1842 --
1843 end chk_dpnt_educ_delete;
1844 --
1845 -----------------------------------------------------------------------------
1846 -- Bug 9079450: The medical expense exemption for aged employee/dependents
1847 -- will only be if they are of age 65 or older
1848 -----------------------------------------------------------------------------
1849 procedure eligible_for_med_exem_aged(
1850 p_aei_information7 in varchar2,
1851 p_aei_information8 in varchar2,
1852 p_aei_information9 in varchar2,
1853 p_information_type in varchar2,
1854 p_aei_information1 in varchar2)
1855 is
1856 l_effective_date date;
1857 l_dummy varchar2(2);
1858 l_year number(4);
1859
1860 cursor csr is
1861 select pay_kr_ff_functions_pkg.aged_flag(p_aei_information8,l_effective_date)
1862 from dual;
1863 --
1864 begin
1865 --
1866 if (p_information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO') then
1867
1868 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1;
1869 l_year := to_number(to_char(l_effective_date,'YYYY'));
1870
1871 if (p_aei_information7 <> '0') and (l_year > 2008) then
1872
1873 if p_aei_information9 = 'B' then
1874 --
1875 open csr;
1876 fetch csr into l_dummy;
1877 close csr;
1878 --
1879 if l_dummy = 'N' then
1880 fnd_message.set_name('PAY', 'PAY_KR_ELIGIBLE_MED_AGED_ERROR');
1881 fnd_message.set_token('DPNT_NUM',p_aei_information8);
1882 fnd_message.raise_error;
1883 end if;
1884
1885 end if;
1886
1887 end if;
1888
1889 end if;
1890
1891 end eligible_for_med_exem_aged;
1892 -----------------------------------------------------------------------------
1893 procedure dup_records( p_aei_information1 in varchar2,
1894 p_assignment_id in number,
1895 p_assignment_extra_info_id in number,
1896 p_aei_information6 in varchar2,
1897 p_aei_information2 in varchar2,
1898 p_aei_information4 in varchar2,
1899 p_information_type in varchar2)
1900 is
1901 l_dup_priority varchar2(2);
1902 l_dup_acc_number varchar2(2);
1903 l_effective_date date;
1904
1905 cursor csr_priority is
1906 select 'Y'
1907 from per_assignment_extra_info
1908 where
1909 assignment_id = p_assignment_id
1910 and information_type = p_information_type
1911 and aei_information6 = p_aei_information6
1912 and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
1913 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
1914
1915 cursor csr_dup_acc_number is
1916 select 'Y'
1917 from per_assignment_extra_info
1918 where
1919 assignment_id = p_assignment_id
1920 and information_type = p_information_type
1921 and aei_information2 = p_aei_information2
1922 and aei_information4 = p_aei_information4
1923 and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
1924 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
1925
1926 begin
1927 if (p_information_type in ('KR_YEA_SEP_PEN_DETAILS','KR_YEA_PEN_SAVING_DETAILS',
1928 'KR_YEA_HOU_SAVING_DETAILS','KR_YEA_LT_STOCK_SAVING_DETAILS')) then
1929
1930 l_effective_date := fnd_date.canonical_to_date(p_aei_information1);
1931 l_dup_priority := 'N';
1932 l_dup_acc_number := 'N';
1933 open csr_priority;
1934 fetch csr_priority into l_dup_priority;
1935 if csr_priority%notfound then
1936 l_dup_priority := 'N' ;
1937 end if ;
1938 close csr_priority;
1939 if (l_dup_priority = 'Y') then
1940 fnd_message.set_name('PAY' , 'PAY_KR_INV_PRIORITY');
1941 fnd_message.set_token('PRIORITY',p_aei_information6);
1942 fnd_message.raise_error;
1943 end if;
1944
1945 open csr_dup_acc_number;
1946 fetch csr_dup_acc_number into l_dup_acc_number;
1947 if csr_dup_acc_number%notfound then
1948 l_dup_acc_number := 'N';
1949 end if;
1950 close csr_dup_acc_number;
1951
1952 if (l_dup_acc_number = 'Y') then
1953 fnd_message.set_name('PAY' , 'PAY_KR_INV_ACCOUNT_NUMBER');
1954 fnd_message.set_token('ACC_NUMBER',p_aei_information4);
1955 if p_information_type = 'KR_YEA_SEP_PEN_DETAILS' then
1956 fnd_message.set_token('TYPE',hr_general.decode_lookup('KR_YEA_SEP_PEN_TYPES',p_aei_information2));
1957 elsif p_information_type = 'KR_YEA_PEN_SAVING_DETAILS' then
1958 fnd_message.set_token('TYPE',hr_general.decode_lookup('KR_YEA_PEN_SAVING_TYPES',p_aei_information2));
1959 elsif p_information_type = 'KR_YEA_HOU_SAVING_DETAILS' then
1960 fnd_message.set_token('TYPE',hr_general.decode_lookup('KR_HOUSING_SAVING_TYPE',p_aei_information2));
1961 elsif p_information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS' then
1962 fnd_message.set_token('TYPE',hr_general.decode_lookup('KR_YEA_LT_STOCK_SAVING_YEAR',p_aei_information2));
1963 end if;
1964 fnd_message.raise_error;
1965 end if;
1966 end if;
1967 end dup_records;
1968 -------------------------------------------------------------------------------
1969 procedure housing_sub_saving_limit( p_aei_information1 in varchar2,
1970 p_aei_information2 in varchar2,
1971 p_aei_information5 in varchar2,
1972 p_information_type in varchar2) is
1973
1974 l_hou_sub_saving_limit number;
1975
1976 function get_globalvalue(p_glbvar in varchar2,p_process_date in date) return number
1977 is
1978 --
1979 cursor csr_ff_global
1980 is
1981 select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
1982 from ff_globals_f glb
1983 where glb.global_name = p_glbvar
1984 and p_process_date between glb.effective_start_date and glb.effective_end_date;
1985 --
1986 l_glbvalue number default 0;
1987 begin
1988 Open csr_ff_global;
1989 fetch csr_ff_global into l_glbvalue;
1990 close csr_ff_global;
1991 --
1992 if l_glbvalue is null then
1993 l_glbvalue := 0;
1994 end if;
1995 --
1996 return l_glbvalue;
1997 end get_globalvalue;
1998
1999 begin
2000 if p_information_type = 'KR_YEA_HOU_SAVING_DETAILS' and p_aei_information2 = 'HST1' then
2001
2002 l_hou_sub_saving_limit := get_globalvalue('KR_YEA_HOUSINSAVINTYPE_HST1',fnd_date.canonical_to_date(p_aei_information1));
2003
2004 if p_aei_information5 > l_hou_sub_saving_limit then
2005 fnd_message.set_name('PAY' , 'PAY_KR_MAX_HOU_SUB_SAVING');
2006 fnd_message.set_token('LIMIT',l_hou_sub_saving_limit);
2007 fnd_message.raise_error;
2008 end if;
2009 end if;
2010 end housing_sub_saving_limit;
2011 -------------------------------------------------------------------------------
2012 procedure hou_rent_contract_dates( p_aei_information1 in varchar2,
2013 p_assignment_extra_info_id in number,
2014 p_assignment_id in number,
2015 p_aei_information2 in varchar2,
2016 p_aei_information3 in varchar2,
2017 p_information_type in varchar2) is
2018
2019 cursor csr_hou_cont_dates is
2020 select fnd_date.canonical_to_date(aei_information2) cont_start_date,
2021 fnd_date.canonical_to_date(aei_information3) cont_end_date
2022 from per_assignment_extra_info
2023 where
2024 assignment_id = p_assignment_id
2025 and information_type = p_information_type
2026 and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
2027 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(fnd_date.canonical_to_date(p_aei_information1), 'YYYY');
2028 l_flag varchar2(1);
2029 l_curr_start_date date;
2030 l_curr_end_date date;
2031 begin
2032 l_flag := 'N';
2033 if p_information_type = 'KR_YEA_HOU_RENT_DETAILS' then
2034
2035 l_curr_start_date := fnd_date.canonical_to_date(p_aei_information2);
2036 l_curr_end_date := fnd_date.canonical_to_date(p_aei_information3);
2037
2038 for i in csr_hou_cont_dates loop
2039 if l_curr_start_date between i.cont_start_date and i.cont_end_date then
2040 l_flag := 'Y';
2041 elsif l_curr_end_date between i.cont_start_date and i.cont_end_date then
2042 l_flag := 'Y';
2043 elsif l_curr_start_date < i.cont_start_date and l_curr_end_date > i.cont_end_date then
2044 l_flag := 'Y';
2045 end if;
2046 if l_flag = 'Y' then
2047 fnd_message.set_name('PAY' , 'PAY_KR_CONT_PD_UNIQUE_ERROR');
2048 fnd_message.set_token('START_DATE',l_curr_start_date);
2049 fnd_message.set_token('END_DATE',l_curr_end_date);
2050 fnd_message.raise_error;
2051 end if;
2052 end loop;
2053 end if;
2054 end hou_rent_contract_dates;
2055 -------------------------------------------------------------------------------
2056 procedure chk_don_insert_update(
2057 p_assignment_id in number,
2058 p_information_type in varchar2,
2059 p_aei_information1 in varchar2,
2060 p_aei_information12 in varchar2)
2061 -----------------------------------------------------------------------------
2062 is
2063 l_donated_amt number;
2064 l_donated_year varchar2(4);
2065 l_effective_date date;
2066 l_don_type_asg_info_id number;
2067 l_don_asg_info_id number;
2068 l_ovn number;
2069 --
2070 cursor csr_get_donation_details(p_aei_information5 in varchar2, p_effective_date in date) is
2071 select sum(nvl(aei_information3,0))
2072 from per_assignment_extra_info
2073 where assignment_id = p_assignment_id
2074 and information_type = p_information_type
2075 and aei_information5 = p_aei_information5
2076 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
2077 --
2078 cursor csr_get_donation_type_details(p_donated_year in varchar2, p_aei_information5 in varchar2, p_effective_date in date) is
2079 select assignment_extra_info_id
2080 from per_assignment_extra_info
2081 where assignment_id = p_assignment_id
2082 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2083 and aei_information2 = p_donated_year
2084 and aei_information8 = p_aei_information5
2085 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
2086 --
2087 cursor validate_detail_rec(p_donated_year in varchar2, p_effective_date in date) is
2088 select assignment_extra_info_id
2089 from per_assignment_extra_info
2090 where assignment_id = p_assignment_id
2091 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2092 and aei_information2 = p_donated_year
2093 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
2094 and aei_information8 not in (select distinct aei_information5
2095 from per_assignment_extra_info
2096 where assignment_id = p_assignment_id
2097 and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
2098 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY'));
2099 --
2100 cursor csr_donations_avail(p_effective_date in date) is
2101 select distinct aei_information5
2102 from per_assignment_extra_info
2103 where assignment_id = p_assignment_id
2104 and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
2105 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
2106 ----------------------------------------------------------------------------------------------
2107 begin
2108 --
2109 l_donated_amt := null;
2110 l_donated_year := null;
2111 l_effective_date := null;
2112 l_don_type_asg_info_id := null;
2113 l_don_asg_info_id := null;
2114 l_ovn := null;
2115 --
2116 if p_information_type = 'KR_YEA_DETAIL_DONATION_INFO' and p_aei_information12 in ('5','4','3','2','1') then
2117 --
2118 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1;
2119 l_donated_year := to_char(l_effective_date,'YYYY');
2120 --
2121 for i in csr_donations_avail(l_effective_date) loop
2122 --
2123 l_donated_amt := null;
2124 l_don_type_asg_info_id := null;
2125 l_ovn := null; /* Bug 12714266 */
2126 open csr_get_donation_details(i.aei_information5, l_effective_date);
2127 fetch csr_get_donation_details into l_donated_amt;
2128 close csr_get_donation_details;
2129 --
2130 open csr_get_donation_type_details(l_donated_year, i.aei_information5, l_effective_date);
2131 fetch csr_get_donation_type_details into l_don_type_asg_info_id;
2132 close csr_get_donation_type_details;
2133
2134 if l_don_type_asg_info_id is not null then
2135 hr_assignment_extra_info_api.update_assignment_extra_info(
2136 p_validate => false,
2137 p_assignment_extra_info_id => l_don_type_asg_info_id,
2138 p_object_version_number => l_ovn,
2139 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
2140 p_aei_information1 => fnd_date.date_to_canonical(l_effective_date),
2141 p_aei_information2 => l_donated_year,
2142 p_aei_information3 => '0',
2143 p_aei_information4 => '0',
2144 p_aei_information5 => '0',
2145 p_aei_information6 => null,
2146 p_aei_information7 => null,
2147 p_aei_information8 => i.aei_information5,
2148 p_aei_information9 => l_donated_amt,
2149 p_aei_information10 => l_donated_amt,
2150 p_aei_information11 => '0',
2151 p_aei_information12 => null,
2152 p_aei_information13 => null,
2153 p_aei_information14 => null,
2154 p_aei_information15 => null,
2155 p_aei_information16 => null,
2156 p_aei_information17 => null,
2157 p_aei_information18 => null,
2158 p_aei_information19 => null,
2159 p_aei_information20 => null,
2160 p_aei_information21 => null,
2161 p_aei_information22 => null,
2162 p_aei_information23 => null,
2163 p_aei_information24 => null,
2164 p_aei_information25 => null,
2165 p_aei_information26 => null,
2166 p_aei_information27 => null,
2167 p_aei_information28 => null,
2168 p_aei_information29 => null,
2169 p_aei_information30 => null
2170 );
2171 else
2172 hr_assignment_extra_info_api.create_assignment_extra_info(
2173 p_validate => false,
2174 p_assignment_id => p_assignment_id,
2175 p_information_type => 'KR_YEA_DONATION_TYPE_DETAIL',
2176 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
2177 p_aei_information1 => fnd_date.date_to_canonical(l_effective_date),
2178 p_aei_information2 => l_donated_year,
2179 p_aei_information3 => '0',
2180 p_aei_information4 => '0',
2181 p_aei_information5 => '0',
2182 p_aei_information6 => null,
2183 p_aei_information7 => null,
2184 p_aei_information8 => i.aei_information5,
2185 p_aei_information9 => l_donated_amt,
2186 p_aei_information10 => l_donated_amt,
2187 p_aei_information11 => '0',
2188 p_aei_information12 => null,
2189 p_aei_information13 => null,
2190 p_aei_information14 => null,
2191 p_aei_information15 => null,
2192 p_aei_information16 => null,
2193 p_aei_information17 => null,
2194 p_aei_information18 => null,
2195 p_aei_information19 => null,
2196 p_aei_information20 => null,
2197 p_aei_information21 => null,
2198 p_aei_information22 => null,
2199 p_aei_information23 => null,
2200 p_aei_information24 => null,
2201 p_aei_information25 => null,
2202 p_aei_information26 => null,
2203 p_aei_information27 => null,
2204 p_aei_information28 => null,
2205 p_aei_information29 => null,
2206 p_aei_information30 => null,
2207 p_assignment_extra_info_id => l_don_type_asg_info_id,
2208 p_object_version_number => l_ovn
2209 );
2210 end if;
2211 --
2212 end loop;
2213 --
2214 for j in validate_detail_rec(l_donated_year,l_effective_date) loop
2215 delete
2216 from per_assignment_extra_info
2217 where assignment_extra_info_id = j.assignment_extra_info_id
2218 and aei_information2 = l_donated_year;
2219 end loop;
2220 end if;
2221
2222 end chk_don_insert_update;
2223 --
2224 -----------------------------------------------------------------------------
2225 -- Bug 9393732
2226 -----------------------------------------------------------------------------
2227 procedure chk_don_delete(
2228 p_assignment_id_o in number,
2229 p_information_type_o in varchar2,
2230 p_aei_information1_o in varchar2,
2231 p_aei_information5_o in varchar2,
2232 p_aei_information12_o in varchar2)
2233 -----------------------------------------------------------------------------
2234 is
2235 l_donated_amt number;
2236 l_donated_year varchar2(4);
2237 l_effective_date date;
2238 l_don_type_asg_info_id number;
2239 l_ovn number;
2240 --
2241 cursor csr_get_donation_details(p_effective_date in date) is
2242 select sum(nvl(aei_information3,0))
2243 from per_assignment_extra_info
2244 where assignment_id = p_assignment_id_o
2245 and information_type = p_information_type_o
2246 and aei_information5 = p_aei_information5_o
2247 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
2248 --
2249 cursor csr_get_donation_type_details(p_donated_year in varchar2, p_effective_date in date) is
2250 select assignment_extra_info_id,
2251 object_version_number
2252 from per_assignment_extra_info
2253 where assignment_id = p_assignment_id_o
2254 and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
2255 and aei_information2 = p_donated_year
2256 and aei_information8 = p_aei_information5_o
2257 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
2258 --
2259
2260
2261 ----------------------------------------------------------------------------------------------
2262 begin
2263 --
2264 l_donated_amt := null;
2265 l_donated_year := null;
2266 l_effective_date := null;
2267 l_don_type_asg_info_id := null;
2268 l_ovn := null;
2269 --
2270 if p_information_type_o = 'KR_YEA_DETAIL_DONATION_INFO' and p_aei_information12_o in ('5','4','3','2','1') then
2271 --
2272 l_effective_date := add_months(trunc(fnd_date.canonical_to_date(p_aei_information1_o),'YYYY'),12) - 1;
2273 l_donated_year := to_char(l_effective_date,'YYYY');
2274 --
2275 open csr_get_donation_details(l_effective_date);
2276 fetch csr_get_donation_details into l_donated_amt;
2277 close csr_get_donation_details;
2278 --
2279 open csr_get_donation_type_details(l_donated_year,l_effective_date);
2280 fetch csr_get_donation_type_details into l_don_type_asg_info_id,l_ovn;
2281 close csr_get_donation_type_details;
2282
2283 if l_don_type_asg_info_id is not null then
2284 if nvl(l_donated_amt,0) > 0 then
2285 hr_assignment_extra_info_api.update_assignment_extra_info(
2286 p_validate => false,
2287 p_assignment_extra_info_id => l_don_type_asg_info_id,
2288 p_object_version_number => l_ovn,
2289 p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
2290 p_aei_information1 => fnd_date.date_to_canonical(l_effective_date),
2291 p_aei_information2 => l_donated_year,
2292 p_aei_information3 => '0',
2293 p_aei_information4 => '0',
2294 p_aei_information5 => '0',
2295 p_aei_information6 => null,
2296 p_aei_information7 => null,
2297 p_aei_information8 => p_aei_information5_o,
2298 p_aei_information9 => l_donated_amt,
2299 p_aei_information10 => l_donated_amt,
2300 p_aei_information11 => '0',
2301 p_aei_information12 => null,
2302 p_aei_information13 => null,
2303 p_aei_information14 => null,
2304 p_aei_information15 => null,
2305 p_aei_information16 => null,
2306 p_aei_information17 => null,
2307 p_aei_information18 => null,
2308 p_aei_information19 => null,
2309 p_aei_information20 => null,
2310 p_aei_information21 => null,
2311 p_aei_information22 => null,
2312 p_aei_information23 => null,
2313 p_aei_information24 => null,
2314 p_aei_information25 => null,
2315 p_aei_information26 => null,
2316 p_aei_information27 => null,
2317 p_aei_information28 => null,
2318 p_aei_information29 => null,
2319 p_aei_information30 => null
2320 );
2321 else
2322
2323 delete from per_assignment_extra_info
2324 where assignment_extra_info_id = l_don_type_asg_info_id
2325 and aei_information2 = l_donated_year;
2326 end if;
2327 end if;
2328 --
2329 end if;
2330
2331 end chk_don_delete;
2332 --
2333 -------------------------------------------------------------------------------------------------
2334 -- Bug 9393732
2335 -------------------------------------------------------------------------------------------------
2336 procedure chk_invalid_donation(
2337 p_aei_information1 in varchar2,
2338 p_information_type in varchar2,
2339 p_aei_information5 in varchar2,
2340 p_aei_information12 in varchar2,
2341 p_aei_information13 in varchar2,
2342 p_aei_information2 in varchar2) -- Bug 13247926
2343 --
2344 is
2345 --
2346 l_donation_type varchar2(40);
2347 l_year varchar2(4);
2348 --
2349 begin
2350 --
2351 if p_information_type = 'KR_YEA_DETAIL_DONATION_INFO' then
2352 --
2353 l_year := to_char((add_months(trunc(fnd_date.canonical_to_date(p_aei_information1),'YYYY'),12) - 1),'YYYY');
2354 --
2355 if ((p_aei_information12 <> '1') and
2356 (p_aei_information5 = '20' or p_aei_information5 = '42') and
2357 (to_number(l_year) >= 2008)) then
2358 --
2359 l_donation_type := hr_general.decode_lookup('KR_YEA_DONATION_TYPE', p_aei_information5);
2360 fnd_message.set_name('PAY', 'PAY_KR_ELIGIBLE_DON_ERROR');
2361 fnd_message.set_token('DPNT_NUM',p_aei_information13);
2362 fnd_message.set_token('DON_TYPE',l_donation_type);
2363 fnd_message.raise_error;
2364 --
2365 end if;
2366 --
2367 -- Bug 13247926
2368 if ( (p_aei_information5 = '30')
2369 and (fnd_date.canonical_to_date(p_aei_information2) > to_date('2011/06/30', 'YYYY/MM/DD'))
2370 and (to_number(l_year) >= 2011) )
2371 then
2372 l_donation_type := hr_general.decode_lookup('KR_YEA_DONATION_TYPE', p_aei_information5);
2373 fnd_message.set_name('PAY', 'PAY_KR_TAX_LAW_DON_ERROR');
2374 fnd_message.set_token('DON_TYPE',l_donation_type);
2375 fnd_message.raise_error;
2376 end if;
2377 end if;
2378 --
2379 end;
2380 --
2381 procedure chk_tax_reduction_date( p_aei_information1 in varchar2,
2382 p_aei_information2 in varchar2,
2383 p_assignment_id in number,
2384 p_information_type in varchar2)
2385 is
2386 l_curr_start_date date ;
2387 l_curr_end_date date ;
2388 l_flag varchar2(1);
2389 c_20120101 date;
2390 c_20140101 date;
2391 begin
2392 l_flag := 'N';
2393 c_20120101 := to_date('01-01-2012','DD-MM-YYYY');
2394 c_20140101 := to_date('01-01-2014','DD-MM-YYYY');
2395
2396 if p_information_type in('KR_YEA_TAX_REDUCTION_SMB','KR_YEA_TAX_REDUCTION_TEACHERS','KR_YEA_TAX_REDUCTION_MARINE') then
2397
2398 l_curr_start_date := fnd_date.canonical_to_date(p_aei_information1);
2399 l_curr_end_date := fnd_date.canonical_to_date(p_aei_information2);
2400
2401 if l_curr_start_date < c_20120101 or l_curr_start_date >= c_20140101 then
2402 l_flag := 'Y';
2403 elsif l_curr_start_date > l_curr_end_date then
2404 l_flag := 'Y';
2405 end if;
2406
2407 if l_flag = 'Y' then
2408 fnd_message.set_name('PAY' , 'PAY_KR_TAX_REDUCTION_DATE');
2409 fnd_message.raise_error;
2410 end if;
2411 end if;
2412 end chk_tax_reduction_date;
2413
2414 procedure chk_fw_tax_reduction_date( p_assignment_id in number,
2415 p_information_type in varchar2,
2416 p_aei_information2 in varchar2,
2417 p_aei_information3 in varchar2)
2418 is
2419 l_contract_date date ;
2420 l_expiry_date date ;
2421 c_20150101 date;
2422 begin
2423 c_20150101 := to_date('01-01-2015','DD-MM-YYYY');
2424
2425 if p_information_type in('KR_YEA_FW_TAX_BREAK_INFO') then
2426 l_contract_date := fnd_date.canonical_to_date(p_aei_information2);
2427 l_expiry_date := fnd_date.canonical_to_date(p_aei_information3);
2428
2429 if l_expiry_date < l_contract_date or l_expiry_date >= c_20150101 then
2430 fnd_message.set_name('PAY' , 'PAY_KR_FW_TAX_BREAK_END_DATE');
2431 fnd_message.raise_error;
2432 end if;
2433 end if;
2434 end chk_fw_tax_reduction_date;
2435 -------------------------------------------------------------------------------------------------
2436 end per_kr_extra_aei_rules;