[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PER_IN_CON_LEG_HOOK
Source
1 PACKAGE BODY per_in_con_leg_hook AS
2 /* $Header: peinlhco.pkb 120.8 2011/10/05 10:44:44 pnethaga ship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'per_in_con_leg_hook.';
5 g_debug BOOLEAN;
6 p_token_name pay_in_utils.char_tab_type;
7 p_token_value pay_in_utils.char_tab_type;
8 p_message_name VARCHAR2(50);
9
10
11
12 -- -----------------------------------------------------------------------+
13 -- Name : nominee_age_check --+
14 -- Type : Procedure --+
15 -- Access : Public --+
16 -- Description : This procedure does the age validation i.e --+
17 -- checks if the guardian details are entered if the --+
18 -- nominee's age is below 18 --+
19 -- Parameters : --+
20 -- IN : p_contact_relationship_id NUMBER --+
21 -- --+
22 -- Change History : --+
23 --------------------------------------------------------------------------+
24 -- Rev# Date Userid Description --+
25 --------------------------------------------------------------------------+
26 -- 1.1 16-May-2005 sukukuma Created this procedure --+
27 --------------------------------------------------------------------------+
28 -- vbanner, commenting out for bug 4674384.
29 -- sukukuma,uncommented out for bug 4674384
30 PROCEDURE nominee_age_check
31 ( p_contact_relationship_id IN
32 PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
33 BEGIN
34 NULL ;
35 END nominee_age_check;
36
37 -- -----------------------------------------------------------------------+
38 -- Name : nomination_share_insert_check --+
39 -- Type : Procedure --+
40 -- Access : Public --+
41 -- Description : This procedure checks if the sum of nomination share--+
42 -- for a particular benifit of employee is under 100 --+
43 -- or not. --+
44 -- Parameters : --+
45 -- IN : p_CEI_INFORMATION2 NUMBER --+
46 -- p_CEI_INFORMATION3 NUMBER --+
47 -- p_effective_date DATE --+
48 -- p_contact_relationship_id NUMBER --+
49 -- OUT : N/A --+
50 -- RETURN : N/A --+
51 -- --+
52 -- --+
53 -- --+
57 --------------------------------------------------------------------------+
54 -- Change History : --+
55 --------------------------------------------------------------------------+
56 -- Rev# Date Userid Description --+
58 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
59 -- 1.1 16-May-2005 sukukuma Updated this procedure --+
60 --------------------------------------------------------------------------+
61
62 PROCEDURE nomination_share_insert_check
63 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
64 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
65 ,p_effective_date IN DATE
66 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
67 )IS
68
69 BEGIN
70 NULL ;
71 END nomination_share_insert_check ;
72
73
74 -- -----------------------------------------------------------------------+
75 -- Name : nomination_share_update_check --+
76 -- Type : Procedure --+
77 -- Access : Public --+
78 -- Description : This procedure checks if the sum of nomination share--+
79 -- for a particular benifit of employee is under 100 --+
80 -- or not. --+
81 -- Parameters : --+
82 -- IN : p_CEI_INFORMATION2 NUMBER --+
83 -- p_CEI_INFORMATION3 NUMBER --+
84 -- p_effective_date DATE --+
85 -- p_contact_relationship_id NUMBER --+
86 -- p_contact_extra_info_id NUMBER --+
87 -- OUT : N/A --+
88 -- RETURN : N/A --+
89 -- --+
90 -- --+
91 -- --+
92 -- Change History : --+
93 --------------------------------------------------------------------------+
94 -- Rev# Date Userid Description --+
95 --------------------------------------------------------------------------+
96 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
97 -- 1.1 16-May-2005 sukukuma Updated this procedure --+
98 --------------------------------------------------------------------------+
99
100 PROCEDURE nomination_share_update_check
101 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
102 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
103 ,p_effective_date IN DATE
104 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
105 ,p_contact_extra_info_id IN PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE)IS
106 BEGIN
107 NULL ;
108 END nomination_share_update_check;
109
110
111
112 -- -----------------------------------------------------------------------+
113 -- Name : check_nominee_age --+
114 -- Type : Procedure --+
115 -- Access : Public --+
116 -- Description : This procedure does the age validation i.e --+
117 -- checks if the guardian details are entered if the --+
118 -- nominee's age is below 18 --+
119 -- Parameters : --+
120 -- IN : p_contact_relationship_id NUMBER --+
121 -- p_message_name VARCHAR2 --+
122 -- p_token_name VARCHAR2 --+
123 -- p_toen_value VARCHAR2 --+
124 -- OUT : 3 --+
125 -- RETURN : N/A --+
126 -- Change History : --+
127 --------------------------------------------------------------------------+
128 -- Rev# Date Userid Description --+
129 --------------------------------------------------------------------------+
130 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
131 -- 1.1 31-Mar-2004 gaugupta Bug 3590036 fixed. --+
132 -- 1.2 16-May-2005 sukukuma updated this procedure --+
133 -- 1.3 05-APR-2008 mdubasi Bug 6871352 fixed. --+
134 --------------------------------------------------------------------------+
135 --sukukuma, changed the name of this procedure from nominee_age_check
136 -- to check_nominee_age
137 --mdubasi, added Exception block to handle NO_DATA_FOUND Exception
138
139 PROCEDURE check_nominee_age
140 ( p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
141 ,p_information_type IN PER_CONTACT_EXTRA_INFO_F.information_type%TYPE
142 ,p_message_name OUT NOCOPY VARCHAR2
143 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
144 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
145
146 IS
147 l_birth_date DATE;
148 l_current_date DATE;
149 l_year NUMBER;
150 l_month NUMBER;
151 l_day NUMBER;
152 l_contact_person_id NUMBER;
153 l_legislation_code VARCHAR2(10);
154 l_guardian_detail_flag NUMBER;
155 l_procedure VARCHAR2(100);
156 l_message VARCHAR2(255);
157
158
159 CURSOR c_business_group_check
160 (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
161
162 SELECT pbg.legislation_code
163 FROM per_business_groups pbg,
164 per_contact_relationships pcr
165 WHERE pcr.contact_relationship_id = p_contact_relationship_id
166 AND pcr.business_group_id = pbg.business_group_id;
167
168 CURSOR c_get_birth_date(l_contact_person_id IN NUMBER) IS
169
170 SELECT DATE_OF_BIRTH from per_all_people_f
171 WHERE PERSON_ID = l_contact_person_id ;
172
173 CURSOR c_get_contact_person_id IS
174
175 SELECT contact_person_id from PER_CONTACT_RELATIONSHIPS
176 WHERE contact_relationship_id = p_contact_relationship_id;
177
178 CURSOR c_check_guardian_details IS
179 SELECT 1 from per_contact_relationships
180 WHERE contact_relationship_id = p_contact_relationship_id
181 AND cont_information13 is not null
182 AND cont_information14 is not null
183 AND cont_information15 is not null
184 AND cont_information17 is not null;
185 BEGIN
186
187 g_debug := hr_utility.debug_enabled ;
188 l_procedure := g_package ||'check_nominee_age';
189 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
190
191 IF g_debug THEN
192 pay_in_utils.trace('**************************************************','********************');
193 pay_in_utils.trace('p_contact_relationship_id',p_contact_relationship_id);
194 pay_in_utils.trace('**************************************************','********************');
195 END IF;
196
197 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
198 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
199 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
200 RETURN;
201 END IF;
202
203 OPEN c_business_group_check(p_contact_relationship_id);
204 FETCH c_business_group_check into l_legislation_code;
205 CLOSE c_business_group_check;
206
207 IF l_legislation_code = 'IN' THEN
208
209 pay_in_utils.set_location(g_debug,l_procedure,30);
210
211 OPEN c_get_contact_person_id;
212 FETCH c_get_contact_person_id into l_contact_person_id;
213 CLOSE c_get_contact_person_id;
214
215 IF l_contact_person_id is not NULL THEN
216 --Get contact person Id--
217 pay_in_utils.set_location(g_debug,l_procedure,40);
218
219 OPEN c_get_birth_date(l_contact_person_id);
220 FETCH c_get_birth_date into l_birth_date;
221 CLOSE c_get_birth_date;
222
223 IF l_birth_date is not null THEN
224 ---Get the age of nominee--
225 pay_in_utils.set_location(g_debug,l_procedure,50);
226 If p_information_type = 'IN_NOMINATION_DETAILS' then
227 BEGIN
228 SELECT effective_date into l_current_date from fnd_sessions
229 WHERE session_id = userenv('SESSIONID');
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 l_current_date := trunc(sysdate);
233 END;
234
235 l_year := to_number(to_char(l_current_date , 'yyyy')) - to_number(to_char(l_birth_date , 'yyyy'));
236 l_month := to_number(to_char(l_current_date , 'mm')) - to_number(to_char(l_birth_date , 'mm'));
237 l_day := to_number(to_char(l_current_date , 'dd')) - to_number(to_char(l_birth_date , 'dd'));
238
239 IF (l_year < 18 or ((l_year = 18) and (l_month < 0)) or ((l_year = 18) and (l_month = 0) and (l_day < 0))) THEN
240 l_guardian_detail_flag := 0;
241 --check the age of nominee--
242 pay_in_utils.set_location(g_debug,l_procedure,60);
243
244 OPEN c_check_guardian_details;
245 FETCH c_check_guardian_details into l_guardian_detail_flag;
246 CLOSE c_check_guardian_details;
247
248 IF l_guardian_detail_flag = 0 THEN
249 ---Gardian Details---
250 pay_in_utils.set_location(g_debug,l_procedure,70);
251
252 hr_utility.set_message(800,'PER_IN_GUARDIAN_DETAILS');
253 hr_utility.raise_error;
254 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
255 fnd_log.message(LOG_LEVEL =>FND_LOG.LEVEL_EXCEPTION
256 ,MODULE =>'per.plsql.hr_in_contact_extra_info_api.create_in_contact_extra_info'
257 ,POP_MESSAGE => FALSE);
258 END IF;
259 END IF;
260 END IF;
261 END IF;
262 ELSE
263
264 pay_in_utils.set_location(g_debug,l_procedure,80);
265
266 hr_utility.set_message(800,'PER_IN_GUARDIAN_DETAILS');
267
268 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
269 fnd_log.message(LOG_LEVEL =>FND_LOG.LEVEL_EXCEPTION
270 ,MODULE =>'per.plsql.hr_in_contact_extra_info_api.create_in_contact_extra_info'
271 ,POP_MESSAGE => FALSE);
272 END IF;
273 END IF;
274 ELSE
275
276 pay_in_utils.set_location(g_debug,l_procedure,90);
277 p_message_name := 'PER_IN_NO_RELATIONSHIP';
278 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,100);
279 RETURN;
280 END IF;
281 END IF;
282 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,110);
283
284 END check_nominee_age;
285
286
287
288
289 -- -----------------------------------------------------------------------+
290 -- Name : get_essential_insert_value --+
291 -- Type : Procedure --+
292 -- Access : Private --+
293 -- Description : This procedure checks if the sum of nomination share--+
294 -- for a particular benifit of employee is under 100 --+
295 -- or not. --+
296 -- Parameters : --+
297 -- IN : p_CEI_INFORMATION2 NUMBER --+
298 -- : p_CEI_INFORMATION3 NUMBER --+
299 -- : p_effective_date DATE --+
300 -- : p_contact_relationship_id NUMBER --+
301 -- p_message_name VARCHAR2 --+
302 -- p_token_name VARCHAR2 --+
303 -- p_toen_value VARCHAR2 --+
304 -- OUT : 3 --+
305 -- RETURN : N/A --+
306 -- --+
307 -- Change History : --+
308 --------------------------------------------------------------------------+
309 -- Rev# Date Userid Description --+
310 --------------------------------------------------------------------------+
311 -- 1.0 16-May-2005 sukukuma Created this procedure --+
312 -- 1.1 12-Jan-2006 rpalli Bug:4895307 - Added the check --+
313 -- for nomination share less than --+
314 -- 100 --+
315 --------------------------------------------------------------------------+0
316
317 PROCEDURE get_essential_insert_value
318 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
319 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
320 ,p_effective_date IN DATE
321 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
322 ,p_message_name OUT NOCOPY VARCHAR2
323 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
324 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
325 IS
326 l_nomination_share NUMBER;
327 l_person_id NUMBER;
328 l_contact_relationship_id NUMBER;
329 l_temp NUMBER;
330 l_legislation_code VARCHAR2(10);
331 l_procedure VARCHAR2(200);
332 l_message VARCHAR2(255);
333
334
335
336 CURSOR c_business_group_check
337 (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
338 SELECT pbg.legislation_code
339 FROM per_business_groups pbg,
340 per_contact_relationships pcr
341 WHERE pcr.contact_relationship_id = p_contact_relationship_id
342 AND pcr.business_group_id = pbg.business_group_id;
343
344
345 CURSOR c_get_relationship_id(l_person_id IN NUMBER) IS
346 SELECT contact_relationship_id from PER_CONTACT_RELATIONSHIPS
347 WHERE person_id = l_person_id
348 AND contact_relationship_id <> p_contact_relationship_id;
349
350 CURSOR c_get_person_id IS
351 SELECT person_id from PER_CONTACT_RELATIONSHIPS
352 WHERE contact_relationship_id = p_contact_relationship_id;
353
354 CURSOR c_check_benefit IS
355 SELECT 1 from PER_CONTACT_EXTRA_INFO_F
356 WHERE contact_relationship_id = p_contact_relationship_id
357 AND CEI_INFORMATION3 = p_CEI_INFORMATION3
358 AND p_effective_date between effective_start_date and effective_end_date;
359
360 BEGIN
361
362 g_debug := hr_utility.debug_enabled ;
363 l_procedure := g_package ||'get_essential_insert_value';
364 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
365
366 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
367 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
368 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
369 RETURN;
370 END IF;
371
372 OPEN c_business_group_check(p_contact_relationship_id);
373 FETCH c_business_group_check into l_legislation_code;
374 CLOSE c_business_group_check;
375
376 pay_in_utils.set_location(g_debug,l_procedure,20);
377
378 IF l_legislation_code = 'IN' THEN
379 l_nomination_share := 0;
380 l_temp := 0;
381
382 BEGIN
383 OPEN c_check_benefit;
384 FETCH c_check_benefit into l_temp;
385 CLOSE c_check_benefit;
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 NULL ;
389 END;
390
391 IF l_temp = 0 THEN
392 --get person id--
393 pay_in_utils.set_location(g_debug,l_procedure,30);
394
395 OPEN c_get_person_id;
396 FETCH c_get_person_id into l_person_id;
397 CLOSE c_get_person_id;
398
399 OPEN c_get_relationship_id(l_person_id);
400 LOOP
401 FETCH c_get_relationship_id into l_contact_relationship_id;
402 EXIT WHEN c_get_relationship_id%NOTFOUND;
403 l_nomination_share := l_nomination_share + get_nomination_share(l_contact_relationship_id ,
404 p_CEI_INFORMATION3,
405 p_effective_date);
406 END LOOP;
407 CLOSE c_get_relationship_id;
408
409 IF g_debug then
410 pay_in_utils.trace('nomination share after',l_nomination_share + to_number(p_CEI_INFORMATION2));
411 END IF;
412
413 IF l_nomination_share + to_number(p_CEI_INFORMATION2) > 100 THEN
414 ---check for percentage--
415 pay_in_utils.trace('Check the value of nomination share','40');
416 p_message_name := 'PER_IN_NOM_SHARE_MORE';
417 RETURN ;
418 ELSIF l_nomination_share + to_number(p_CEI_INFORMATION2) < 100 THEN
419 --check for percentage--
420 pay_in_utils.trace('Check the value of nomination share less','50');
421 p_message_name := 'PER_IN_NOM_SHARE_LESS';
422 RETURN ;
423 END IF;
424 ELSE
425
426 pay_in_utils.set_location(g_debug,l_procedure,60);
427 p_message_name := 'PER_IN_CONT_ALREADY_NOMINATED';
428 RETURN;
429 END IF;
430
431 END IF;
432
433 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
434
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 NULL;
438
439
440 END get_essential_insert_value;
441
442
443
444
445 -- -----------------------------------------------------------------------+
446 -- Name : get_essential_update_value --+
447 -- Type : Procedure --+
448 -- Access : Private --+
449 -- Description : This procedure checks if the sum of nomination share--+
450 -- for a particular benifit of employee is under 100 --+
451 -- or not. --+
452 -- Parameters : --+
453 -- IN : p_CEI_INFORMATION2 NUMBER --+
454 -- p_CEI_INFORMATION3 NUMBER --+
455 -- p_effective_date DATE --+
456 -- p_contact_relationship_id NUMBER --+
457 -- p_contact_extra_info_id NUMBER --+
458 -- p_message_name VARCHAR2 --+
459 -- p_token_name VARCHAR2 --+
460 -- p_toen_value VARCHAR2 --+
461 -- OUT : 3 --+
462 -- RETURN : N/A --+
463 -- --+
464 -- --+
465 -- Change History : --+
466 --------------------------------------------------------------------------+
467 -- Rev# Date Userid Description --+
468 --------------------------------------------------------------------------+
469 -- 1.0 16-May-2004 sukukuma Created this procedure --+
470 -- 1.1 12-Jan-2006 rpalli Bug:4895307 - Added the check --+
471 -- for nomination share less than --+
472 -- 100 --+
473 --------------------------------------------------------------------------+0
474
475 PROCEDURE get_essential_update_value
476 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
477 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
478 ,p_effective_date IN DATE
479 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
480 ,p_contact_extra_info_id IN PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE
481 ,p_message_name OUT NOCOPY VARCHAR2
482 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
483 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
484 IS
485 l_nomination_share NUMBER;
486 l_person_id NUMBER;
487 l_contact_relationship_id NUMBER;
488 l_temp NUMBER;
489 l_legislation_code VARCHAR2(10);
490 l_procedure VARCHAR2(200);
491 l_message VARCHAR2(255);
492
493 CURSOR c_check_benefit IS
494 SELECT 1 from PER_CONTACT_EXTRA_INFO_F
498 AND p_effective_date between effective_start_date and effective_end_date;
495 WHERE contact_relationship_id = p_contact_relationship_id
496 AND contact_extra_info_id <> p_contact_extra_info_id
497 AND CEI_INFORMATION3 = p_CEI_INFORMATION3
499
500 CURSOR c_business_group_check
501 (p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE)IS
502 SELECT pbg.legislation_code
503 FROM per_business_groups pbg,
504 per_contact_relationships pcr
505 WHERE pcr.contact_relationship_id = p_contact_relationship_id
506 AND pcr.business_group_id = pbg.business_group_id;
507
508
509 CURSOR c_get_relationship_id(l_person_id IN NUMBER) IS
510 SELECT contact_relationship_id from PER_CONTACT_RELATIONSHIPS
511 WHERE person_id = l_person_id
512 AND contact_relationship_id <> p_contact_relationship_id;
513
514 CURSOR c_get_person_id IS
515 SELECT person_id from PER_CONTACT_RELATIONSHIPS
516 WHERE contact_relationship_id = p_contact_relationship_id;
517
518
519 BEGIN
520
521 g_debug := hr_utility.debug_enabled ;
522 l_procedure := g_package ||'get_essential_update_value';
523 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
524
525 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
526 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
527 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
528 RETURN;
529 END IF;
530
531 OPEN c_business_group_check(p_contact_relationship_id);
532 FETCH c_business_group_check into l_legislation_code;
533 CLOSE c_business_group_check;
534
535 IF l_legislation_code = 'IN' THEN
536
537 pay_in_utils.set_location(g_debug,l_procedure,30);
538
539 l_nomination_share := 0;
540 l_temp := 0;
541 BEGIN
542 OPEN c_check_benefit;
543 FETCH c_check_benefit into l_temp;
544 CLOSE c_check_benefit;
545
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 NULL ;
549 END;
550
551 IF l_temp = 0 THEN
552 --Get the person Id--
553 pay_in_utils.set_location(g_debug,l_procedure,40);
554
555
556 OPEN c_get_person_id;
557 FETCH c_get_person_id into l_person_id;
558 CLOSE c_get_person_id;
559
560 OPEN c_get_relationship_id(l_person_id);
561 LOOP
562 FETCH c_get_relationship_id into l_contact_relationship_id;
563 EXIT WHEN c_get_relationship_id%NOTFOUND;
564 l_nomination_share := l_nomination_share + get_nomination_share(l_contact_relationship_id ,
565 p_CEI_INFORMATION3,
566 p_effective_date);
567 END LOOP;
568 CLOSE c_get_relationship_id;
569
570 IF g_debug then
571 pay_in_utils.trace('nomination share after',l_nomination_share + to_number(p_CEI_INFORMATION2));
572 END IF;
573 IF l_nomination_share + to_number(p_CEI_INFORMATION2) > 100 THEN
574 --check for percentage--
575 pay_in_utils.trace('Check the value of nomination share','50');
576 p_message_name := 'PER_IN_NOM_SHARE_MORE';
577 RETURN ;
578 ELSIF l_nomination_share + to_number(p_CEI_INFORMATION2) < 100 THEN
579 --check for percentage--
580 pay_in_utils.trace('Check the value of nomination share less','60');
581 p_message_name := 'PER_IN_NOM_SHARE_LESS';
582 RETURN ;
583 END IF;
584
585 ELSE
586 ----check for status of the nominee------
587 pay_in_utils.set_location(g_debug,l_procedure,70);
588 p_message_name := 'PER_IN_CONT_ALREADY_NOMINATED';
589 RETURN ;
590
591 END IF;
592
593 END IF;
594 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
595
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 NULL ;
599
600 END get_essential_update_value;
601
602
603
604
605 -- -----------------------------------------------------------------------+
606 -- Name : check_in_con_int --+
607 -- Type : Procedure --+
608 -- Access : Public --+
609 -- Description : Internal procedure which calls the appropriate --+
610 -- : procedures for Insert and Upadte --+
611 -- Parameters : --+
612 -- IN : p_CEI_INFORMATION2 NUMBER --+
613 -- p_CEI_INFORMATION3 NUMBER --+
614 -- p_effective_date DATE --+
615 -- p_contact_relationship_id NUMBER --+
616 -- p_contact_extra_info_id NUMBER --+
617 -- p_message_name VARCHAR2 --+
618 -- p_token_name VARCHAR2 --+
619 -- p_toen_value VARCHAR2 --+
620 -- OUT : 3 --+
621 -- RETURN : N/A --+
622 -- --+
623 -- --+
624 -- --+
625 -- Change History : --+
626 --------------------------------------------------------------------------+
627 -- Rev# Date Userid Description --+
628 --------------------------------------------------------------------------+
629 -- 1.0 16-May-2005 sukukuma Created this procedure --+
630 --------------------------------------------------------------------------+0
631 PROCEDURE check_in_con_int
632 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
633 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
634 ,p_effective_date IN DATE
635 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
636 ,p_contact_extra_info_id IN PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE
637 ,p_information_type IN PER_CONTACT_EXTRA_INFO_F.information_type%TYPE
638 ,p_message_name OUT NOCOPY VARCHAR2
639 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
640 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
641
642 IS
643 l_procedure VARCHAR2(100);
644 l_message VARCHAR2(255);
645 BEGIN
646 g_debug := hr_utility.debug_enabled;
647 l_procedure := g_package ||'check_in_con_int';
648 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
649
650 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
651 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
652 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
653 RETURN;
654 END IF;
655
656 IF p_contact_extra_info_id IS NULL THEN
657 --Insert---
658 pay_in_utils.set_location(g_debug,l_procedure,30);
659
660 -------check age and guardian status-----------------
661
662 check_nominee_age(p_contact_relationship_id =>p_contact_relationship_id
663 ,p_information_type =>p_information_type
664 ,p_message_name =>p_message_name
665 ,p_token_name => p_token_name
666 ,p_token_value =>p_token_value);
667
668 IF p_message_name <>'SUCCESS' THEN
669 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
670 RETURN ;
671 END IF ;
672
673
674 get_essential_insert_value
675 (p_CEI_INFORMATION2 => p_CEI_INFORMATION2
676 ,p_CEI_INFORMATION3 => p_CEI_INFORMATION3
677 ,p_effective_date => p_effective_date
678 ,p_contact_relationship_id => p_contact_relationship_id
679 ,p_message_name =>p_message_name
680 ,p_token_name => p_token_name
681 ,p_token_value =>p_token_value);
682
683 IF p_message_name <>'SUCCESS' THEN
684 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
685 RETURN ;
686 END IF;
687
688
689 ELSE
690 --Update--
691 pay_in_utils.set_location(g_debug,l_procedure,60);
692
693 get_essential_update_value
694 (p_CEI_INFORMATION2 => p_CEI_INFORMATION2
695 ,p_CEI_INFORMATION3 => p_CEI_INFORMATION3
696 ,p_effective_date => p_effective_date
697 ,p_contact_relationship_id => p_contact_relationship_id
698 ,p_contact_extra_info_id => p_contact_extra_info_id
699 ,p_message_name => p_message_name
700 ,p_token_name => p_token_name
701 ,p_token_value => p_token_value);
702
703 IF p_message_name <>'SUCCESS' THEN
704 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
705 RETURN ;
706 END IF;
707
708 END IF ;
709
710 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
711
712
713
714 END check_in_con_int;
715
716
717
718
719
720 -- -----------------------------------------------------------------------+
721 -- Name : check_in_con_insert --+
722 -- Type : Procedure --+
723 -- Access : Public --+
724 -- Description : This procedure checks if the sum of nomination share--+
725 -- for a particular benifit of employee is under 100 --+
726 -- or not. --+
727 -- Parameters : --+
728 -- IN : p_CEI_INFORMATION2 NUMBER --+
729 -- p_CEI_INFORMATION3 NUMBER --+
730 -- p_effective_date DATE --+
731 -- p_contact_relationship_id NUMBER --+
732 -- OUT : N/A --+
733 -- RETURN : N/A --+
734 -- --+
735 -- --+
736 -- --+
737 -- Change History : --+
738 --------------------------------------------------------------------------+
739 -- Rev# Date Userid Description --+
740 --------------------------------------------------------------------------+
741 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
742 -- 1.1 16-May-2005 sukukuma Updated this procedure --+
743 -- 1.2 12-Jan-2006 rpalli Updated the procedure to raise --+
744 -- no error when nomination share --+
745 -- less than 100. The message is --+
746 -- handled in the library --+
747 --------------------------------------------------------------------------+
748
749 PROCEDURE check_in_con_insert
750 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
751 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
752 ,p_effective_date IN DATE
756 IS
753 ,p_information_type IN PER_CONTACT_EXTRA_INFO_F.information_type%TYPE
754 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
755 )
757 l_procedure VARCHAR2(100);
758 l_message VARCHAR2(255);
759
760 BEGIN
761
762 g_debug := hr_utility.debug_enabled;
763 l_procedure := g_package ||'check_in_con_insert';
764 p_message_name := 'SUCCESS';
765 pay_in_utils.null_message(p_token_name, p_token_value);
766
767 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
768
769 check_in_con_int
770 (p_CEI_INFORMATION2 => p_CEI_INFORMATION2
771 ,p_CEI_INFORMATION3 => p_CEI_INFORMATION3
772 ,p_effective_date => p_effective_date
773 ,p_contact_relationship_id => p_contact_relationship_id
774 ,p_contact_extra_info_id => NULL
775 ,p_information_type => p_information_type
776 ,p_message_name => p_message_name
777 ,p_token_name => p_token_name
778 ,p_token_value => p_token_value);
779
780
781 IF p_message_name in ('PER_IN_NOM_SHARE_LESS') THEN
782 pay_in_utils.trace('PER_IN_NOM_SHARE_LESS','20');
783 ELSIF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
784 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
785 ELSE
786 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
787 END IF;
788
789 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
790
791 END check_in_con_insert ;
792
793 -- -----------------------------------------------------------------------+
794 -- Name : check_in_con_update --+
795 -- Type : Procedure --+
796 -- Access : Public --+
797 -- Description : This procedure checks if the sum of nomination share--+
798 -- for a particular benifit of employee is under 100 --+
799 -- or not. --+
800 -- Parameters : --+
801 -- IN : p_CEI_INFORMATION2 NUMBER --+
802 -- p_CEI_INFORMATION3 NUMBER --+
803 -- p_effective_date DATE --+
804 -- p_contact_relationship_id NUMBER --+
805 -- p_contact_extra_info_id NUMBER --+
806 -- OUT : N/A --+
807 -- RETURN : N/A --+
808 -- --+
809 -- --+
810 -- --+
811 -- Change History : --+
812 --------------------------------------------------------------------------+
813 -- Rev# Date Userid Description --+
814 --------------------------------------------------------------------------+
815 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
816 -- 1.1 16-May-2005 sukukuma Updated this procedure --+
817 -- 1.2 12-Jan-2006 rpalli Updated the procedure to raise --+
818 -- no error when nomination share --+
819 -- less than 100. The message is --+
820 -- handled in the library --+
821 --------------------------------------------------------------------------+
822
823 PROCEDURE check_in_con_update
824 (p_CEI_INFORMATION2 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE
825 ,p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE
826 ,p_effective_date IN DATE
827 ,p_information_type IN PER_CONTACT_EXTRA_INFO_F.information_type%TYPE
828 ,p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE
829 ,p_contact_extra_info_id IN PER_CONTACT_EXTRA_INFO_F.contact_extra_info_id%TYPE)
830 IS
831
832 CURSOR c_nom_id IS
833 SELECT
834 CEI_INFORMATION2
835 ,CEI_INFORMATION3
836 ,contact_relationship_id
837 FROM PER_CONTACT_EXTRA_INFO_F
838 WHERE contact_extra_info_id =p_contact_extra_info_id;
839
840
841 l_CEI_INFORMATION2 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION2%TYPE;
842 l_CEI_INFORMATION3 PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE;
843 l_contact_relationship_id PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE;
844 l_procedure VARCHAR2(100);
845 l_message VARCHAR2(255);
846
847 BEGIN
848
849
850 g_debug := hr_utility.debug_enabled;
851 l_procedure := g_package ||'check_in_con_update';
852 p_message_name := 'SUCCESS';
853 pay_in_utils.null_message(p_token_name, p_token_value);
854
855 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
856
857 OPEN c_nom_id;
858 FETCH c_nom_id
859 INTO l_CEI_INFORMATION2
860 ,l_CEI_INFORMATION3
861 ,l_contact_relationship_id;
862 CLOSE c_nom_id;
863
864 pay_in_utils.set_location(g_debug,l_procedure,20);
865
866 IF p_CEI_INFORMATION2 <> hr_api.g_varchar2 THEN
867 l_CEI_INFORMATION2 := p_CEI_INFORMATION2;
868 END IF;
869
870 IF p_CEI_INFORMATION3 <> hr_api.g_varchar2 THEN
874
871 l_CEI_INFORMATION3 := p_CEI_INFORMATION3;
872 END IF;
873
875 check_in_con_int
876 (p_CEI_INFORMATION2 => l_CEI_INFORMATION2
877 ,p_CEI_INFORMATION3 => l_CEI_INFORMATION3
878 ,p_effective_date => p_effective_date
879 ,p_contact_relationship_id => l_contact_relationship_id
880 ,p_contact_extra_info_id => p_contact_extra_info_id
881 ,p_information_type => p_information_type
882 ,p_message_name => p_message_name
883 ,p_token_name => p_token_name
884 ,p_token_value => p_token_value
885 );
886
887 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
888
889 IF p_message_name in ('PER_IN_NOM_SHARE_LESS') THEN
890 hr_utility.trace('PER_IN_NOM_SHARE_LESS');
891 pay_in_utils.trace('PER_IN_NOM_SHARE_LESS','40');
892 ELSIF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
893 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
894 ELSE
895 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
896 END IF;
897
898 END check_in_con_update ;
899
900 -- -----------------------------------------------------------------------+
901 -- Name : get_nomination_share --+
902 -- Type : Function --+
903 -- Access : Public --+
904 -- Description : This function returns the nomination share for a --+
905 -- particular combination of contact relationship id --+
906 -- effecttive date and benefit type. --+
907 -- Parameters : --+
908 -- IN : p_contact_relationship_id NUMBER --+
909 -- p_CEI_INFORMATION3 NUMBER --+
910 -- p_effective_date DATE --+
911 -- OUT : 3 --+
912 -- RETURN : N/A --+
913 -- --+
914 -- --+
915 -- --+
916 -- Change History : --+
917 --------------------------------------------------------------------------+
918 -- Rev# Date Userid Description --+
919 --------------------------------------------------------------------------+
920 -- 1.0 31-Mar-2004 gaugupta Created this procedure --+
921 -- 1.1 24-Jun-2004 vgsriniv Modified the logic.(Bug:3683622)--+
922 --------------------------------------------------------------------------+
923 FUNCTION get_nomination_share(p_contact_relationship_id IN PER_CONTACT_EXTRA_INFO_F.contact_relationship_id%TYPE,
924 p_CEI_INFORMATION3 IN PER_CONTACT_EXTRA_INFO_F.CEI_INFORMATION3%TYPE,
925 p_effective_date IN DATE)
926 RETURN NUMBER IS
927 l_nomination_share NUMBER;
928 l_nom NUMBER;
929 l_procedure VARCHAR2(100);
930 l_message VARCHAR2(255);
931
932 CURSOR c_nomination_share IS
933 SELECT CEI_INFORMATION2
934 FROM PER_CONTACT_EXTRA_INFO_F
935 WHERE contact_relationship_id = p_contact_relationship_id
936 AND CEI_INFORMATION3 = p_CEI_INFORMATION3
937 AND effective_end_date >= p_effective_date;
938
939
940 BEGIN
941 g_debug := hr_utility.debug_enabled;
942 l_procedure := 'get_nomination_share';
943 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
944 l_nomination_share := 0;
945 l_nom := 0;
946
947 OPEN c_nomination_share;
948 LOOP
949 FETCH c_nomination_share INTO l_nom;
950 pay_in_utils.trace('l_nom',l_nom);
951 EXIT WHEN c_nomination_share%NOTFOUND;
952 l_nomination_share := l_nomination_share + l_nom;
953 END LOOP;
954 CLOSE c_nomination_share;
955
956 pay_in_utils.trace('l_nomination_share',l_nomination_share);
957 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
958
959 RETURN l_nomination_share;
960
961 EXCEPTION
962 WHEN NO_DATA_FOUND THEN
963 RETURN 0;
964
965
966 END get_nomination_share;
967
968 END per_in_con_leg_hook;