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