[Home] [Help]
PACKAGE BODY: APPS.BEN_ASSIGNMENT_INTERNAL
Source
1 Package Body ben_assignment_internal as
2 /* $Header: beasgbsi.pkb 120.10 2010/07/01 08:12:04 pvelvano ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_assignment_internal.';
7
8 /*Bug 9744453: Moved the procedure copy_emppradd_ctrpradd from copy_empasg_to_benasg*/
9 g_death boolean;
10 g_divorce boolean;
11
12 procedure copy_emppradd_ctrpradd
13 (p_empper_id in number
14 ,p_ctrper_id in number
15 ,p_benasg_effdate in date
16 ,p_death in boolean
17 ,p_divorce in boolean
18 ,p_date in date default null
19 )
20 is
21 --
22 l_add_dets per_addresses%ROWTYPE;
23 --
24 l_ctr_shdresflg varchar2(100);
25 l_ctr_id number;
26 l_ctr_ovn number;
27 l_add_id number;
28 l_add_ovn number;
29 --
30 cursor c_getctrdets
31 (c_per_id in number
32 ,c_conper_id in number
33 )
34 Is
35 select ctr.rltd_per_rsds_w_dsgntr_flag,
36 ctr.contact_relationship_id,
37 ctr.object_version_number
38 from per_contact_relationships ctr
39 where ctr.person_id = c_per_id
40 and ctr.contact_person_id = c_conper_id
41 and p_benasg_effdate between ctr.date_start and nvl(ctr.date_end,p_benasg_effdate);
42 --
43 cursor c_getpradddets
44 (c_per_id in number
45 )
46 Is
47 select *
48 from per_addresses adr
49 where adr.person_id = c_per_id
50 and adr.primary_flag = 'Y'
51 and p_benasg_effdate between adr.date_from and nvl(adr.date_to,p_benasg_effdate)
52 order by adr.date_from desc ;
53 --
54
55 /*Added Code for Bug: 7506378*/
56 cursor c_chk_future_rec
57 (c_per_id in number
58 )
59 Is
60 select min(date_from)
61 from per_addresses adr
62 where adr.person_id = c_per_id
63 and adr.primary_flag = 'Y'
64 and adr.date_from > p_benasg_effdate;
65
66 l_date_to date default null;
67
68 /*Ended Code for Bug: 7506378*/
69
70 begin
71 --
72 -- Copy the primary address from the employee when the
73 -- if an address does not exist for the contact.
74 --
75 open c_getctrdets
76 (c_per_id => p_empper_id
77 ,c_conper_id => p_ctrper_id
78 );
79 fetch c_getctrdets into l_ctr_shdresflg, l_ctr_id, l_ctr_ovn;
80 close c_getctrdets;
81 hr_utility.set_location('in address', 210);
82 hr_utility.set_location('in p_benasg_effdate'||p_benasg_effdate, 210);
83 hr_utility.set_location('in p_empper_id'||p_empper_id, 210);
84 hr_utility.set_location('in p_ctrper_id'||p_ctrper_id, 210);
85 --
86 -- Check if a primary address already exists for the contact
87 --
88 open c_getpradddets
89 (c_per_id => p_ctrper_id
90 );
91 --
92 fetch c_getpradddets into l_add_dets;
93 if c_getpradddets%notfound then
94 close c_getpradddets;
95 hr_utility.set_location('Primary add not found', 210);
96 --
97 -- Get the primary address details for the employee
98 --
99 open c_getpradddets
100 (c_per_id => p_empper_id
101 );
102 --
103 fetch c_getpradddets into l_add_dets;
104 --
105 -- Check for the employee primary address
106 --
107 if c_getpradddets%found then
108 --
109 close c_getpradddets;
110 --hr_utility.set_location(l_proc, 220);
111 --
112 -- Create the primary address for the contact
113 --
114 if (l_ctr_shdresflg = 'Y' and not p_death and not p_divorce) then -- 5750408
115 null; -- do not create address
116 else
117
118 /*Added Code for Bug: 7506378*/
119 open c_chk_future_rec
120 (c_per_id => p_ctrper_id
121 );
122 fetch c_chk_future_rec into l_date_to;
123 close c_chk_future_rec;
124 if(l_date_to is not null) then
125 l_date_to := l_date_to -1;
126 end if;
127 /*Ended Code for Bug: 7506378*/
128
129 hr_person_address_api.create_person_address
130 (p_effective_date => nvl(p_date,p_benasg_effdate)
131 ,p_person_id => p_ctrper_id
132 ,p_primary_flag => 'Y'
133 ,p_style => l_add_dets.style
134 ,p_date_from => nvl(p_date,p_benasg_effdate)
135 ,p_date_to => l_date_to -- Added parameter for Bug 7506378
136 ,p_address_type => l_add_dets.address_type
137 ,p_address_line1 => l_add_dets.address_line1
138 ,p_address_line2 => l_add_dets.address_line2
139 ,p_address_line3 => l_add_dets.address_line3
140 ,p_town_or_city => l_add_dets.town_or_city
141 ,p_region_1 => l_add_dets.region_1
142 ,p_region_2 => l_add_dets.region_2
143 ,p_region_3 => l_add_dets.region_3
144 ,p_postal_code => l_add_dets.postal_code
145 ,p_country => l_add_dets.country
146 ,p_telephone_number_1 => l_add_dets.telephone_number_1
147 ,p_telephone_number_2 => l_add_dets.telephone_number_2
148 ,p_telephone_number_3 => l_add_dets.telephone_number_3
149 ,p_addr_attribute_category => l_add_dets.addr_attribute_category
150 ,p_addr_attribute1 => l_add_dets.addr_attribute1
151 ,p_addr_attribute2 => l_add_dets.addr_attribute2
152 ,p_addr_attribute3 => l_add_dets.addr_attribute3
153 ,p_addr_attribute4 => l_add_dets.addr_attribute4
154 ,p_addr_attribute5 => l_add_dets.addr_attribute5
155 ,p_addr_attribute6 => l_add_dets.addr_attribute6
156 ,p_addr_attribute7 => l_add_dets.addr_attribute7
157 ,p_addr_attribute8 => l_add_dets.addr_attribute8
158 ,p_addr_attribute9 => l_add_dets.addr_attribute9
159 ,p_addr_attribute10 => l_add_dets.addr_attribute10
160 ,p_addr_attribute11 => l_add_dets.addr_attribute11
161 ,p_addr_attribute12 => l_add_dets.addr_attribute12
162 ,p_addr_attribute13 => l_add_dets.addr_attribute13
163 ,p_addr_attribute14 => l_add_dets.addr_attribute14
164 ,p_addr_attribute15 => l_add_dets.addr_attribute15
165 ,p_addr_attribute16 => l_add_dets.addr_attribute16
166 ,p_addr_attribute17 => l_add_dets.addr_attribute17
167 ,p_addr_attribute18 => l_add_dets.addr_attribute18
168 ,p_addr_attribute19 => l_add_dets.addr_attribute19
169 ,p_addr_attribute20 => l_add_dets.addr_attribute20
170 --
171 ,p_address_id => l_add_id
172 ,p_object_version_number => l_add_ovn
173 --Bug 4310174
174 ,p_add_information13 => l_add_dets.add_information13
175 ,p_add_information14 => l_add_dets.add_information14
176 ,p_add_information15 => l_add_dets.add_information15
177 ,p_add_information16 => l_add_dets.add_information16
178 ,p_add_information17 => l_add_dets.add_information17
179 ,p_add_information18 => l_add_dets.add_information18
180 ,p_add_information19 => l_add_dets.add_information19
181 ,p_add_information20 => l_add_dets.add_information20
182 --End Bug 4310174
183 );
184 end if;
185 --
186 if l_ctr_shdresflg = 'Y' and
187 (p_death or p_divorce)
188 then
189 --
190 -- Set shared residency flag to N.
191 --
192 hr_contact_rel_api.update_contact_relationship(p_effective_date => p_benasg_effdate
193 ,p_contact_relationship_id => l_ctr_id
194 ,p_rltd_per_rsds_w_dsgntr_flag => 'N'
195 ,p_object_version_number => l_ctr_ovn
196 );
197 --
198 end if;
199 --
200 else
201 --
202 close c_getpradddets;
203 --
204 end if;
205 --
206 else
207 --
208 close c_getpradddets;
209 --
210 end if;
211 --
212 end;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-------------------------< check_second_event >---------------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 procedure check_second_event
219 (p_person_id in number
220 ,p_business_group_id in number
221 ,p_effective_date in date
222 ,p_contact_type in varchar2
223 ,p_death in boolean
224 )
225 is
226 --
227 l_proc varchar2(72):=g_package||'check_second_event';
228 --
229 -- Declare cursors and local variables
230 --
231 cursor c1 is
232 select null
233 from per_person_type_usages_f ptu,
234 per_person_types ppt
235 where ptu.person_id = p_person_id
236 and p_effective_date
237 between ptu.effective_start_date
238 and ptu.effective_end_date
239 and ptu.person_type_id = ppt.person_type_id
240 and ppt.system_person_type = decode(p_contact_type,
241 'S','SRVNG_SPS',
242 'D','SRVNG_DP',
243 'R','SRVNG_DPFM',
244 'SRVNG_FMLY_MMBR');
245 --
246 l_dummy varchar2(1);
247 l_type varchar2(30);
248 l_ptu_id number;
249 l_pet_id number;
250 l_ptu_ovn number;
251 l_esd date;
252 l_eed date;
253 --
254 begin
255 --
256 hr_utility.set_location('Entering: '||l_proc,10);
257 --
258 -- Test if a death occurred then we have to create frmr types, etc
259 --
260 if not p_death then
261 --
262 return;
263 --
264 end if;
265 --
266 -- First check if person type usage already exists for person
267 -- If it does do not create it.
268 --
269 open c1;
270 --
271 fetch c1 into l_dummy;
272 --
273 if c1%notfound then
274 --
275 -- Create the appropriate usages
276 --
277 if p_contact_type = 'S' then
278 --
279 l_type := 'SRVNG_SPS';
280 --
281 elsif p_contact_type = 'D' then
282 --
283 l_type := 'SRVNG_DP';
284 --
285 elsif p_contact_type = 'R' then
286 --
287 l_type := 'SRVNG_DPFM';
288 --
289 else
290 --
291 l_type := 'SRVNG_FMLY_MMBR';
292 --
293 end if;
294 --
295 select person_type_id
296 into l_pet_id
297 from per_person_types
298 where business_group_id = p_business_group_id
299 and system_person_type = l_type
300 and default_flag = 'Y' ; -- Bug 3878962
301 --
302 hr_per_type_usage_internal.create_person_type_usage
303 (p_person_id => p_person_id
304 ,p_person_type_id => l_pet_id
305 ,p_effective_date => p_effective_date
306 --
307 ,p_person_type_usage_id => l_ptu_id
308 ,p_object_version_number => l_ptu_ovn
309 ,p_effective_start_date => l_esd
310 ,p_effective_end_date => l_eed);
311 --
312 end if;
313 --
314 close c1;
315 --
316 hr_utility.set_location('Leaving: '||l_proc,10);
317 --
318 end check_second_event;
319
320 -- ----------------------------------------------------------------------------
321 -- |-------------------------< check_person_type>------------------------------|
322 -- this validate whether the person_type_usage already exist
323 -- this was added due to the new flag added in organisation level to stop
324 -- the creation of benefit assignment
325 -- so far the duplicate validation is done against benefit asg level
326 -- which wont work when the ben_Asg creations is set to 'N'
327 --
328 -- ----------------------------------------------------------------------------
329
330 procedure check_person_type(
331 p_person_type_id in number
332 ,p_person_id in number
333 ,p_effective_date in date default null
334 ,p_person_type_usage_id in number default null
335 ,p_type_exist out nocopy varchar2 )
336 is
337
338 l_proc varchar2(72) := g_package||' chk_person_type';
339 l_person_type per_person_types.system_person_type%TYPE;
340 l_business_group_id per_person_types.business_group_id%TYPE;
341 l_person_type_id number;
342
343 cursor csr_valid_person_type ( lc_person_type_id number)
344 is
345 select system_person_type ,business_group_id
346 from per_person_types
347 where person_type_id = lc_person_type_id;
348
349 -- We are doing this check regardless of the enabled flag
350 -- as even old records must be used for this validation
351 cursor csr_check_uniqueness is
352 select person_type_usage_id
353 from per_person_type_usages_f
354 where person_type_id in ( select person_type_id
355 from per_person_types
356 where system_person_type = l_person_type
357 and business_group_id = l_business_group_id )
358 and person_id = p_person_id
359 and ((effective_start_date <= p_effective_date and
360 effective_end_date >= p_effective_date) or
361 (effective_start_date >= p_effective_date));
362
363
364
365 begin
366 hr_utility.set_location('Entering: '||l_proc,10);
367 p_type_exist := 'N' ;
368 open csr_valid_person_type(p_person_type_id);
369 fetch csr_valid_person_type into l_person_type , l_business_group_id;
370 if csr_valid_person_type%notfound then
371 close csr_valid_person_type;
372 --- the forefigh key validated in per peckages
373 return ;
374 end if;
375 close csr_valid_person_type;
376
377 open csr_check_uniqueness;
378 fetch csr_check_uniqueness into l_person_type_id;
379 if csr_check_uniqueness%found then
380 p_type_exist := 'Y' ;
381 end if;
382 close csr_check_uniqueness;
383 hr_utility.set_location('Leaving: '||l_proc,10);
384 end check_person_type ;
385 -- ----------------------------------------------------------------------------
386 -- |-------------------------< copy_empasg_to_benasg >------------------------|
387 -- ----------------------------------------------------------------------------
388 --
389 procedure copy_empasg_to_benasg
390 (p_person_id in number
391 --
392 ,p_pds_atd in date default null
393 ,p_pds_leaving_reason in varchar2 default null
394 ,p_pds_fpd in date default null
395 ,p_per_date_of_death in date default null
396 ,p_per_marital_status in varchar2 default null
397 ,p_per_esd in date default null
398 ,p_dpnt_person_id in number default null
399 ,p_redu_hrs_flag in varchar2 default 'N'
400 ,p_effective_date in date default null
401 --
402 ,p_assignment_id out nocopy number
403 ,p_object_version_number out nocopy number
404 ,p_perhasmultptus out nocopy boolean
405 )
406 is
407 --
408 l_proc varchar2(72):=g_package||'copy_empasg_to_benasg';
409 --
410 -- Declare cursors and local variables
411 --
412 Type PerDetailsType is record
413 (per_id per_contact_relationships.contact_person_id%TYPE
414 ,contype varchar2(100)
415 );
416 Type PerDetails is table of PerDetailsType index by binary_integer;
417 --
418 Type Var2Datatype is table of varchar2(100) index by binary_integer;
419 --
420 -- Declare cursors and local variables
421 --
422 l_perdetails_set PerDetails;
423 l_ptupetspt_set Var2Datatype;
424 l_asg_dets per_all_assignments_f%ROWTYPE;
425 l_plstab_count binary_integer;
426 --
427 l_v2dummy varchar2(1);
428 --
429 l_copybenasg boolean;
430 l_divorce boolean;
431 l_death boolean;
432 l_terminate boolean;
433 l_leg_code varchar2(100);
434 l_bgp_id number;
435 l_emp_perid number;
436 l_empasg_effdate date;
437 l_benasg_effdate date;
438 l_asg_id number;
439 l_asg_ovn number;
440 l_asg_esd date;
441 l_aei_id number;
442 l_aei_ovn number;
443 l_assignment_id number;
444 l_pet_id number;
445 l_per_id number;
446 l_ctr_contype varchar2(100);
447 --
448 l_date_dummy1 date;
449 l_date_dummy2 date;
450 l_date_dummy3 date;
451 l_date_dummy4 date;
452 l_number_dummy1 number;
453 l_number_dummy2 number;
454 l_number_dummy3 number;
455 l_number_dummy4 number;
456 l_boolean_dummy1 boolean;
457 l_boolean_dummy2 boolean;
458 l_boolean_dummy3 boolean;
459 l_boolean_dummy4 boolean;
460 l_varchar2_dummy1 varchar2(1000);
461 l_varchar2_dummy2 varchar2(1000);
462 --
463 l_business_group_id number;
464 l_period_of_service_id number;
465 l_assignment_status_type_id number;
466 l_person_id number;
467 l_organization_id number;
468 l_assignment_number varchar2(200);
469 l_atd_dtfmstr varchar2(200);
470 l_dtupdate_mode varchar2(200);
471 l_empasg_ovn number;
472 l_prevempasg_ovn number;
473 l_perpet_spt varchar2(200);
474 l_ptu_id number;
475 l_ptu_ovn number;
476 l_esd date;
477 l_eed date;
478 l_benasg_id number;
479 l_benasg_esd date;
480 l_empasg_esd date;
481 l_asg_payroll_id number;
482 l_origpayroll_id number;
483 l_emp_pyp_id number;
484 l_emp_salary varchar2(100);
485 l_benasg_pyp_id number;
486 l_benasg_ovn number;
487 l_tmpeffdate_str varchar2(200);
488 l_dummy varchar2(200);
489 l_dummy1_id number;
490 l_booldummy_1 boolean;
491 l_booldummy_2 boolean;
492 l_booldummy_3 boolean;
493 l_booldummy_4 boolean;
494 l_age varchar2(100);
495 l_adj_serv_date date;
496 l_orig_hire_date date;
497 l_payroll_changed varchar2(100);
498 l_orig_payroll_id varchar2(100);
499 l_salary varchar2(100);
500 l_termn_date date;
501 l_termn_reason varchar2(100);
502 l_abs_date date;
503 l_date_of_hire date;
504 l_abs_type varchar2(100);
505 l_abs_reason varchar2(100);
506 l_fammem boolean;
507 l_prptupet_spt varchar2(100);
508 l_count number;
509 l_pet_spt varchar2(1000);
510 l_contacts boolean default FALSE;
511 l_perele_num pls_integer;
512 l_act_pos varchar2(1);
513 l_exists boolean default false;
514 lc_effective_date date;
515 l_type_exist varchar2(1) ;
516 l_prior_marital_status per_all_people_f.marital_status%type;
517 --
518 cursor c_getbgpdets
519 (c_person_id in number
520 ,c_effective_date in date
521 )
522 Is
523 select bgp.business_group_id,
524 bgp.legislation_code,
525 pet.system_person_type
526 from per_business_groups bgp,
527 per_all_people_f per,
528 per_person_types pet
529 where per.business_group_id = bgp.business_group_id
530 and per.person_type_id = pet.person_type_id
531 and per.person_id = c_person_id
532 and c_effective_date between per.effective_start_date -- Bug No 4451864 Removed -1 from the c_effective_date
533 and per.effective_end_date;
534 --
535 cursor c_get_ss_fm_dets
536 (c_person_id in number
537 ,c_eff_date in date
538 ,c_contact_type in varchar2
539 )
540 is
541 select ctr.contact_person_id, ctr.contact_type
542 from per_contact_relationships ctr
543 where ctr.person_id = c_person_id
544 and c_eff_date
545 between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
546 and ctr.contact_type = c_contact_type
547 order by ctr.sequence_number;
548 --
549 cursor c_getdpntperdets
550 (c_person_id in number
551 ,c_eff_date in date
552 )
553 is
554 select distinct ctr.contact_person_id
555 from per_contact_relationships ctr
556 where exists
557 (select null
558 from per_person_type_usages_f ptu,
559 per_person_types pet
560 where ctr.contact_person_id = ptu.person_id
561 and ptu.person_type_id = pet.person_type_id
562 and pet.system_person_type = 'DPNT'
563 and c_eff_date between ptu.effective_start_date and ptu.effective_end_date)
564 and ctr.person_id = c_person_id
565 and ctr.personal_flag = 'Y'
566 and c_eff_date between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
567 order by ctr.contact_person_id;
568 --
569 cursor c_get_all_contacts
570 (c_person_id in number
571 ,c_contact_person_id in number default null
572 ,c_eff_date in date
573 )
574 is
575 select ctr.contact_person_id, ctr.contact_type
576 from per_contact_relationships ctr
577 where ctr.person_id = c_person_id
578 and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
579 and ctr.personal_flag = 'Y'
580 and c_eff_date
581 between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
582 order by ctr.sequence_number;
583 --
584 cursor c_get_contacts
585 (c_person_id in number
586 ,c_contact_person_id in number default null
587 ,c_eff_date in date
588 )
589 is
590 select ctr.contact_person_id, ctr.contact_type
591 from per_contact_relationships ctr
592 where ctr.person_id = c_person_id
593 and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
594 and ctr.personal_flag = 'Y'
595 order by ctr.sequence_number;
596 --
597 cursor c_getbenasgid
598 (c_person_id in number
599 )
600 Is
601 select asg.assignment_id,
602 asg.object_version_number,
603 asg.effective_start_date
604 from per_all_assignments_f asg
605 where asg.person_id = c_person_id
606 and asg.assignment_type = 'B';
607 --
608 cursor c_getbenasgdets
609 (c_person_id in number
610 ,c_eff_date in date
611 )
612 Is
613 select null
614 from per_all_assignments_f asg
615 where asg.person_id = c_person_id
616 and asg.assignment_type = 'B'
617 and c_eff_date
618 between asg.effective_start_date and asg.effective_end_date;
619 --
620 -- WWBUG 1202148. handles issue with dt position being ended for federal
621 -- at same tine assignment is ended. this causes an error condition in
622 -- the create assignment api.
623 --
624 cursor c_getdt_pos
625 (c_position_id in NUMBER
626 ,c_eff_date in DATE)
627 is
628 select 'Y'
629 from hr_positions_f hp
630 , per_shared_types ps
631 where hp.position_id = c_position_id
632 and c_eff_date
633 between hp.date_effective
634 and nvl(hp.date_end, hr_api.g_eot)
635 and ps.shared_type_id = hp.availability_status_id
636 and ps.system_type_cd = 'ACTIVE' ;
637 --
638 cursor c_getempprasgdets
639 (c_person_id in number
640 ,c_eff_date in date
641 )
642 Is
643 select *
644 from per_all_assignments_f asg
645 where asg.person_id = c_person_id
646 and asg.assignment_type = 'E'
647 and asg.primary_flag = 'Y'
648 and c_eff_date
649 between asg.effective_start_date and asg.effective_end_date;
650 -- 2852514
651 cursor c_petprimasgdets
652 (c_person_id in number
653 ,c_eff_date in date
654 )
655 Is
656 select *
657 from per_all_assignments_f asg
658 where asg.person_id = c_person_id
659 and asg.primary_flag = 'Y'
660 and c_eff_date
661 between asg.effective_start_date and asg.effective_end_date;
662
663
664 cursor c_getasgdtinsdets
665 (c_assignment_id in number
666 ,c_eff_date in date
667 )
668 Is
669 select asg.object_version_number,
670 asg.effective_start_date
671 from per_all_assignments_f asg
672 where asg.assignment_id = c_assignment_id
673 and c_eff_date
674 between asg.effective_start_date and asg.effective_end_date;
675 --
676 cursor c_getasgovndtinsdets
677 (c_assignment_id in number
678 ,c_ovn in number
679 )
680 Is
681 select null
682 from per_all_assignments_f asg
683 where asg.assignment_id = c_assignment_id
684 and asg.object_version_number = c_ovn;
685 --
686 cursor c_getaeidets
687 (c_assignment_id in number
688 )
689 Is
690 select aei.aei_information4,
691 aei.aei_information5
692 from per_assignment_extra_info aei
693 where aei.assignment_id = c_assignment_id
694 and aei.information_type = 'BEN_DERIVED';
695 --
696 cursor c_getpypdets
697 (c_pyp_id in number
698 )
699 Is
700 select *
701 from per_pay_proposals
702 where pay_proposal_id = c_pyp_id;
703 --
704 cursor c_getbgpdefpet
705 (c_bgp_id in number
706 ,c_pet_spt in varchar2
707 )
708 Is
709 select pet.person_type_id
710 from per_person_types pet
711 where pet.business_group_id = c_bgp_id
712 and pet.system_person_type = c_pet_spt
713 and pet.default_flag = 'Y';
714 --
715 cursor c_getperptupetdets
716 (c_per_id in number
717 ,c_eff_date in date
718 ,c_type_cd in varchar2 default null
719 )
720 Is
721 select pet.system_person_type
722 from per_person_type_usages_f ptu, per_person_types pet
723 where ptu.person_type_id = pet.person_type_id
724 and ptu.person_id = c_per_id
725 ---- added to test a particular type avaialble # 2852514
726 and pet.system_person_type = nvl(c_type_cd,pet.system_person_type )
727 and c_eff_date
728 between ptu.effective_start_date and ptu.effective_end_date;
729 --
730 cursor c1 is
731 select business_group_id
732 from per_all_people_f
733 where person_id = p_person_id;
734 --
735 cursor c_get_prior_marital_status is
736 select per.marital_status
737 from per_all_people_f per
738 where per.person_id = p_person_id
739 and p_per_esd - 1
740 between per.effective_start_date and per.effective_end_date;
741 ---
742 cursor c_dptn_cvg
743 ( c_contact_person_id number ,
744 c_eff_date date ) is
745 select 'x'
746 from ben_elig_cvrd_dpnt_f pdp,
747 ben_prtt_enrt_rslt_f pen
748 where pen.person_id = p_person_id
749 and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
750 and pdp.dpnt_person_id = c_contact_person_id
751 and c_eff_date between
752 pen.effective_start_date and pen.effective_end_date
753 and c_eff_date between
754 pdp.effective_start_date and pdp.effective_end_date ;
755
756 --
757 cursor c_get_ben_asgdets(p_business_group_id IN NUMBER)
758 Is
759 select substr(hoi.ORG_INFORMATION3,1)
760 from hr_organization_information hoi
761 where hoi.org_information_context = 'Benefits Defaults'
762 and hoi.organization_id = p_business_group_id
763 ;
764
765 l_ben_asg_status_flag hr_organization_information.ORG_INFORMATION3%type ;
766 l_prev_per_id number;
767
768 l_chk_flag varchar2(1) default 'N';
769 --
770 --
771 begin
772 hr_utility.set_location('Entering:'|| l_proc, 10);
773 --
774 -- Check mandatory arguments
775 --
776 hr_api.mandatory_arg_error
777 (p_api_name => l_proc,
778 p_argument => 'person_id',
779 p_argument_value => p_person_id
780 );
781 --
782 -- Check if either of the following are occuring or has occured
783 --
784 -- - Employee termination - PDS ATD is set
785 -- - Employee death - PER DOD is set
786 -- - Employee divorce - PER marital status is D or L
787 --
788 open c1;
789 --
790 fetch c1 into l_business_group_id;
791 --
792 close c1;
793 --
794
795 -- when the default assgnement is not Y or N then
796 -- do nothing # 3899506
797 open c_get_ben_asgdets(l_business_group_id);
798 fetch c_get_ben_asgdets into l_ben_asg_status_flag;
799 close c_get_ben_asgdets;
800
801
802 hr_utility.set_location ( ' ben_asg_status_flag ' || l_ben_asg_status_flag , 99 ) ;
803
804 if NVL(l_ben_asg_status_flag,'N') not in ('Y','N')
805 then
806 return;
807 end if;
808 --
809 if p_per_date_of_death is not null and p_per_esd is not null
810 and p_per_date_of_death < p_per_esd
811 then
812 -- this is possible when entering history for person.
813 -- father expires before childs birth and enter contact details of father.
814 return ;
815 end if;
816
817 if p_pds_atd is not null
818 or p_per_date_of_death is not null
819 or p_per_marital_status in ('D','L') and p_per_esd is not null
820 or p_dpnt_person_id is not null
821 or p_redu_hrs_flag = 'Y'
822 then
823 hr_utility.set_location('Entering:'|| l_proc, 20);
824 --
825 -- Get the business group details
826 --
827 /*
828 open c_getbgpdets(p_person_id
829 ,nvl(p_pds_atd,
830 nvl(p_per_date_of_death,
831 nvl(p_per_esd, p_effective_date)))
832 );
833 fetch c_getbgpdets Into l_bgp_id, l_leg_code, l_perpet_spt;
834 If c_getbgpdets%notfound then
835 close c_getbgpdets;
836 --
837 -- A benefits assignment cannot be created for the employee.
838 -- The employee does not exist.
839 --
840 hr_utility.set_message(801,'BEN_92112_NOBENASGEMP');
841 hr_utility.raise_error;
842 --
843 End If;
844 close c_getbgpdets;
845 */
846 lc_effective_date := nvl(p_pds_atd,
847 nvl(p_per_date_of_death,
848 nvl(p_per_esd, p_effective_date)));
849 --
850 open c_getbgpdets(p_person_id
851 ,lc_effective_date
852 );
853 fetch c_getbgpdets Into l_bgp_id, l_leg_code, l_perpet_spt;
854 If c_getbgpdets%notfound then
855 close c_getbgpdets;
856 --
857 -- Bug 1857193
858 -- In case of termination and death above lc_effective_date
859 -- holds good. In the following case above cursor does not work.
860 -- Create employee with divorce status or correct status to divorce
861 -- 1. Create person on 01/01/01, with divorce status OR
862 -- 2. Create person on 01/01/01 and do date correction of
863 -- marital status to DIVORCE.
864 -- the date cursor c_getbgpdets uses 31-dec-00
865 -- It errors out with BEN_92112_NOBENASGEMP.
866 --
867 lc_effective_date := lc_effective_date + 1;
868 open c_getbgpdets(p_person_id
869 ,lc_effective_date
870 );
871 fetch c_getbgpdets Into l_bgp_id, l_leg_code, l_perpet_spt;
872 If c_getbgpdets%notfound then
873 close c_getbgpdets;
874 -- A benefits assignment cannot be created for the employee.
875 -- The employee does not exist.
876 --
877 hr_utility.set_message(801,'BEN_92112_NOBENASGEMP');
878 hr_utility.raise_error;
879 --
880 end if;
881 End If;
882 close c_getbgpdets;
883 hr_utility.set_location('Entering:'|| l_proc, 30);
884 --
885 -- Check that the system person type is an employee related
886 -- person type
887 --
888 if l_perpet_spt not in ('OTHER') then
889 --
890 l_copybenasg := TRUE;
891 --
892 else
893
894 --
895 l_copybenasg := FALSE;
896 --
897 --- If the contact is particpant then allow to create the ben asg for the dpnt # 2852514
898 --- the date -1 used that he might have losed the participation today the asg may be creadted
899 --- because of that
900 hr_utility.set_location (' OTHER lc_effective_date ' || lc_effective_date , 991 );
901 open c_getperptupetdets
902 (p_person_id
903 ,lc_effective_date -- Bug No 4451864 Removed -1 from lc_effective_date
904 ,'PRTN'
905 ) ;
906 fetch c_getperptupetdets into l_dummy ;
907 if c_getperptupetdets%found then
908 hr_utility.set_location (' l_copybenasg TRUE ' , 991 );
909 l_copybenasg := TRUE;
910 end if ;
911 close c_getperptupetdets ;
912
913 end if;
914 --
915 else
916 --
917 l_copybenasg := FALSE;
918 --
919 end if;
920 --
921 -- Check that OAB is installed in a US legislation
922 --
923 if l_copybenasg then
924 hr_utility.set_location('Entering:'|| l_proc, 40);
925 --
926 -- Deduce effective dates and determine if to create a benefits assignment for
927 -- the employee or the surviving spouse based on the event
928 --
929 l_divorce := FALSE;
930 l_death := FALSE;
931 l_copybenasg := FALSE;
932 --
933 -- Event checks
934 --
935 -- - Date of death set on the person
936 --
937 if p_per_date_of_death is not null
938 then
939 hr_utility.set_location(l_proc, 50);
940 --
941 -- Death of an employee - date of death set
942 --
943 l_empasg_effdate := p_per_date_of_death;
944 l_benasg_effdate := p_per_date_of_death+1;
945 --
946 l_copybenasg := TRUE;
947 l_death := TRUE;
948 g_death := true;
949 --
950 -- - Divorce or legal separation of a person
951 --
952 elsif p_per_marital_status in ('D','L')
953 and p_per_esd is not null
954 then
955 hr_utility.set_location('divorce '|| l_proc, 60);
956 hr_utility.set_location('p_per_esd '|| p_per_esd, 60);
957 --
958 -- If the marital status has not changed, do not update or
959 -- create a benefits assignment.
960 --
961 open c_get_prior_marital_status;
962 fetch c_get_prior_marital_status into l_prior_marital_status;
963 if c_get_prior_marital_status%found then
964 hr_utility.set_location('l_prior_marital_status '|| l_prior_marital_status, 60);
965 hr_utility.set_location('p_per_marital_status '|| p_per_marital_status, 60);
966 if nvl(l_prior_marital_status,'NULL') <> p_per_marital_status then
967 hr_utility.set_location('create asg ', 60);
968 --
969 -- The benefit assignment and person type usage information
970 -- are created on the day of the divorce or legal separation
971 --
972 l_empasg_effdate := p_per_esd;
973 l_benasg_effdate := p_per_esd;
974 --
975 l_copybenasg := TRUE;
976 l_divorce := TRUE;
977 g_divorce := true;
978 --
979 else
980 l_copybenasg := false;
981 end if;
982 --
983 else -- If prior marital status is not found
984 hr_utility.set_location('not found ', 60);
985 l_copybenasg := false;
986 end if;
987 --
988 close c_get_prior_marital_status;
989 --
990 --
991 -- No leaving reason set and termination leaving reasons
992 --
993 elsif p_pds_atd is not null
994 and nvl(p_pds_leaving_reason,hr_api.g_varchar2) not in('D')
995 then
996 hr_utility.set_location(l_proc, 90);
997 --
998 -- Terminating an employee with leaving reason not deceased
999 --
1000 l_empasg_effdate := p_pds_atd;
1001 l_benasg_effdate := p_pds_atd+1;
1002 --
1003 l_copybenasg := TRUE;
1004 l_terminate := TRUE;
1005 --
1006 -- Deceased leaving reason
1007 --
1008 elsif p_pds_atd is not null
1009 and p_pds_leaving_reason = 'D'
1010 then
1011 hr_utility.set_location(l_proc, 100);
1012 --
1013 -- Terminating an employee with leaving reason deceased
1014 --
1015 l_empasg_effdate := p_pds_atd;
1016 l_benasg_effdate := p_pds_atd+1;
1017 --
1018 l_copybenasg := TRUE;
1019 l_death := TRUE;
1020 g_death := true;
1021 --
1022 elsif (p_dpnt_person_id is not null or
1023 p_redu_hrs_flag = 'Y') then
1024 --
1025 l_empasg_effdate := p_effective_date;
1026 l_benasg_effdate := p_effective_date;
1027 l_copybenasg := TRUE;
1028 l_contacts := TRUE;
1029 --
1030 end if;
1031 --
1032 -- Check if to copy the emp assignment to the benefits assignment
1033 --
1034 if l_copybenasg then
1035 hr_utility.set_location(l_proc, 120);
1036 --
1037 -- Get primary employee assignment details
1038 --
1039 open c_getempprasgdets(p_person_id,l_empasg_effdate);
1040 fetch c_getempprasgdets Into l_asg_dets;
1041 ---- when dpnt's dpnt get the benefit asg there wont be any
1042 --- employee assignmnet so pickup from primary asg available # 2852514
1043 --- asked siok to answer the primary can be used for all who is not having Emp asg.
1044 if c_getempprasgdets%notfound then
1045 open c_petprimasgdets(p_person_id,l_empasg_effdate);
1046 fetch c_petprimasgdets Into l_asg_dets;
1047 if c_petprimasgdets%notfound then
1048 l_copybenasg := false ;
1049 end if;
1050 close c_petprimasgdets ;
1051 hr_utility.set_location('Geting asg detail from prim asg', 120);
1052 end if ;
1053
1054 close c_getempprasgdets;
1055 hr_utility.set_location(l_proc, 130);
1056 --
1057 -- IS position still active?
1058 --
1059 --
1060 if l_asg_dets.position_id is not null
1061 then
1062
1063 -- WWBug 2178374 - Added code to pass l_benasg_effdate to the cursor if p_effective_date is null
1064
1065 open c_getdt_pos(l_asg_dets.position_id,nvl(p_effective_date,l_benasg_effdate));
1066 fetch c_getdt_pos into l_act_pos;
1067 if c_getdt_pos%NOTFOUND then
1068 --
1069 -- At this point we have a position that
1070 -- does not exists as of the time of the benefit assignments
1071 -- creation, but did when the assignment existed, we need to
1072 -- null the assignment as it will cause benefits assignment
1073 -- insert to fail.
1074 l_asg_dets.position_id := NULL;
1075 end if;
1076 end if;
1077 --
1078 -- Is probation end after assignment start
1079 --
1080 if l_asg_dets.date_probation_end < l_benasg_effdate then
1081 --
1082 l_asg_dets.date_probation_end := null;
1083 l_asg_dets.probation_period := null;
1084 l_asg_dets.probation_unit := null;
1085 --
1086 end if;
1087 --
1088 -- Check for death and divorce events
1089 --
1090 if l_death
1091 or l_divorce
1092 then
1093 --
1094 -- Get spouse details without benefit assignments
1095 --
1096 hr_utility.set_location(l_proc, 140);
1097 hr_utility.set_location(l_proc||' l_ba_edate: '||l_benasg_effdate, 140);
1098 --
1099 l_per_id := null;
1100 l_ctr_contype := null;
1101 --
1102 open c_get_ss_fm_dets
1103 (c_person_id => p_person_id
1104 ,c_eff_date => l_benasg_effdate
1105 ,c_contact_type => 'S'
1106 );
1107 fetch c_get_ss_fm_dets Into l_per_id, l_ctr_contype;
1108 close c_get_ss_fm_dets;
1109 --
1110 hr_utility.set_location(l_proc||' SPS l_per_id: '||l_per_id, 141);
1111 --
1112 l_plstab_count := 0;
1113 --
1114 If l_per_id is not null
1115 then
1116 check_bnft_asgn
1117 (p_person_id => l_per_id
1118 --RCHASE BENASG bug fix Start
1119 ,p_emp_person_id => p_person_id
1120 --RCHASE End
1121 ,p_effective_date => l_benasg_effdate
1122 ,p_asg_dets => l_asg_dets
1123 ,p_exists => l_exists
1124 );
1125 --
1126 if l_exists = true then
1127 --
1128 check_second_event
1129 (p_person_id => l_per_id
1130 ,p_business_group_id => l_business_group_id
1131 ,p_effective_date => l_benasg_effdate
1132 ,p_contact_type => 'S'
1133 ,p_death => l_death);
1134 --
1135 end if;
1136 --
1137 if l_exists = false then
1138 l_perdetails_set(l_plstab_count).per_id := l_per_id;
1139 l_perdetails_set(l_plstab_count).contype := l_ctr_contype;
1140 hr_utility.set_location('count and type '||l_plstab_count||' '||l_ctr_contype,10);
1141 l_plstab_count := l_plstab_count+1;
1142
1143
1144 end if;
1145 --
1146 hr_utility.set_location('PL_STA AFTER SPS'||l_plstab_count,10);
1147 end if;
1148 --
1149 -- Domestic Partner.
1150 --
1151 l_per_id := null;
1152 l_ctr_contype := null;
1153 --
1154 open c_get_ss_fm_dets
1155 (c_person_id => p_person_id
1156 ,c_eff_date => l_benasg_effdate
1157 ,c_contact_type => 'D'
1158 );
1159 fetch c_get_ss_fm_dets Into l_per_id, l_ctr_contype;
1160 close c_get_ss_fm_dets;
1161 --
1162 hr_utility.set_location(l_proc||' DP l_per_id: '||l_per_id, 141);
1163 --
1164 -- tilak l_plstab_count := 0;
1165 --
1166 If l_per_id is not null
1167 then
1168 check_bnft_asgn
1169 (p_person_id => l_per_id
1170 --RCHASE BENASG bug fix Start
1171 ,p_emp_person_id => p_person_id
1172 --RCHASE End
1173 ,p_effective_date => l_benasg_effdate
1174 ,p_asg_dets => l_asg_dets
1175 ,p_exists => l_exists
1176 );
1177 --
1178 if l_exists = true then
1179 --
1180 check_second_event
1181 (p_person_id => l_per_id
1182 ,p_business_group_id => l_business_group_id
1183 ,p_effective_date => l_benasg_effdate
1184 ,p_contact_type => 'D'
1185 ,p_death => l_death);
1186 --
1187 end if;
1188 --
1189 if l_exists = false then
1190 l_perdetails_set(l_plstab_count).per_id := l_per_id;
1191 l_perdetails_set(l_plstab_count).contype := l_ctr_contype;
1192 l_plstab_count := l_plstab_count+1;
1193 end if;
1194 --
1195 hr_utility.set_location('PL_STA AFTER SPS'||l_plstab_count,10);
1196 end if;
1197 --
1198 -- Populate the family member person id set for Child, Foster Child
1199 -- Step Child and Adopted Child
1200 --
1201 hr_utility.set_location(l_proc||' Family Member: ', 140);
1202 --
1203 -- Child
1204 --
1205 for per_id in c_get_ss_fm_dets
1206 (c_person_id => p_person_id
1207 ,c_eff_date => l_benasg_effdate
1208 ,c_contact_type => 'C'
1209 )
1210 loop
1211 --
1212 -- If benefit assignment exist, update it.
1213 --
1214 check_bnft_asgn
1215 (p_person_id => per_id.contact_person_id
1216 --RCHASE BENASG bug fix Start
1217 ,p_emp_person_id => p_person_id
1218 --RCHASE End
1219 ,p_effective_date => l_benasg_effdate
1220 ,p_asg_dets => l_asg_dets
1221 ,p_exists => l_exists
1222 );
1223 --
1224 if l_exists = true then
1225 --
1226 check_second_event
1227 (p_person_id => per_id.contact_person_id
1228 ,p_business_group_id => l_business_group_id
1229 ,p_effective_date => l_benasg_effdate
1230 ,p_contact_type => 'C'
1231 ,p_death => l_death);
1232 --
1233 end if;
1234 if l_exists = false then
1235 l_fammem := TRUE;
1236 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1237 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1238 l_plstab_count := l_plstab_count+1;
1239 end if;
1240 --
1241 end loop;
1242 --
1243 -- Foster Child
1244 --
1245 for per_id in c_get_ss_fm_dets
1246 (c_person_id => p_person_id
1247 ,c_eff_date => l_benasg_effdate
1248 ,c_contact_type => 'O'
1249 )
1250 loop
1251 --
1252 -- If benefit assignment exist, update it.
1253 --
1254 check_bnft_asgn
1255 (p_person_id => per_id.contact_person_id
1256 --RCHASE BENASG bug fix Start
1257 ,p_emp_person_id => p_person_id
1258 --RCHASE End
1259 ,p_effective_date => l_benasg_effdate
1260 ,p_asg_dets => l_asg_dets
1261 ,p_exists => l_exists
1262 );
1263 --
1264 if l_exists = true then
1265 --
1266 check_second_event
1267 (p_person_id => per_id.contact_person_id
1268 ,p_business_group_id => l_business_group_id
1269 ,p_effective_date => l_benasg_effdate
1270 ,p_contact_type => 'O'
1271 ,p_death => l_death);
1272 --
1273 end if;
1274 if l_exists = false then
1275 l_fammem := TRUE;
1276 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1277 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1278 l_plstab_count := l_plstab_count+1;
1279 end if;
1280 --
1281 end loop;
1282 --
1283 -- Step Child
1284 --
1285 for per_id in c_get_ss_fm_dets
1286 (c_person_id => p_person_id
1287 ,c_eff_date => l_benasg_effdate
1288 ,c_contact_type => 'T'
1289 )
1290 loop
1291 --
1292 -- If benefit assignment exist, update it.
1293 --
1294 check_bnft_asgn
1295 (p_person_id => per_id.contact_person_id
1296 --RCHASE BENASG bug fix Start
1297 ,p_emp_person_id => p_person_id
1298 --RCHASE End
1299 ,p_effective_date => l_benasg_effdate
1300 ,p_asg_dets => l_asg_dets
1301 ,p_exists => l_exists
1302 );
1303 --
1304 if l_exists = true then
1305 --
1306 check_second_event
1307 (p_person_id => per_id.contact_person_id
1308 ,p_business_group_id => l_business_group_id
1309 ,p_effective_date => l_benasg_effdate
1310 ,p_contact_type => 'T'
1311 ,p_death => l_death);
1312 --
1313 end if;
1314 if l_exists = false then
1315 l_fammem := TRUE;
1316 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1317 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1318 l_plstab_count := l_plstab_count+1;
1319 end if;
1320 --
1321 end loop;
1322 --
1323 -- Adopted Child
1324 --
1325 for per_id in c_get_ss_fm_dets
1326 (c_person_id => p_person_id
1327 ,c_eff_date => l_benasg_effdate
1328 ,c_contact_type => 'A'
1329 )
1330 loop
1331 --
1332 -- If benefit assignment exist, update it.
1333 --
1334 check_bnft_asgn
1335 (p_person_id => per_id.contact_person_id
1336 --RCHASE BENASG bug fix Start
1337 ,p_emp_person_id => p_person_id
1338 --RCHASE End
1339 ,p_effective_date => l_benasg_effdate
1340 ,p_asg_dets => l_asg_dets
1341 ,p_exists => l_exists
1342 );
1343 --
1344 if l_exists = true then
1345 --
1346 check_second_event
1347 (p_person_id => per_id.contact_person_id
1348 ,p_business_group_id => l_business_group_id
1349 ,p_effective_date => l_benasg_effdate
1350 ,p_contact_type => 'A'
1351 ,p_death => l_death);
1352 --
1353 end if;
1354 if l_exists = false then
1355 l_fammem := TRUE;
1356 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1357 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1358 l_plstab_count := l_plstab_count+1;
1359 end if;
1360 --
1361 end loop;
1362 --
1363 -- Domestic Partner Child
1364 --
1365 for per_id in c_get_ss_fm_dets
1366 (c_person_id => p_person_id
1367 ,c_eff_date => l_benasg_effdate
1368 ,c_contact_type => 'R'
1369 )
1370 loop
1371 --
1372 -- If benefit assignment exist, update it.
1373 --
1374 check_bnft_asgn
1375 (p_person_id => per_id.contact_person_id
1376 --RCHASE BENASG bug fix Start
1377 ,p_emp_person_id => p_person_id
1378 --RCHASE End
1379 ,p_effective_date => l_benasg_effdate
1380 ,p_asg_dets => l_asg_dets
1381 ,p_exists => l_exists
1382 );
1383 --
1384 if l_exists = true then
1385 --
1386 check_second_event
1387 (p_person_id => per_id.contact_person_id
1388 ,p_business_group_id => l_business_group_id
1389 ,p_effective_date => l_benasg_effdate
1390 ,p_contact_type => 'R'
1391 ,p_death => l_death);
1392 --
1393 end if;
1394 if l_exists = false then
1395 l_fammem := TRUE;
1396 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1397 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1398 l_plstab_count := l_plstab_count+1;
1399 end if;
1400 --
1401 end loop;
1402 --
1403 hr_utility.set_location(l_proc||' CON count: '||l_perdetails_set.count, 147);
1404 if l_perdetails_set.count = 0 then
1405 --
1406 l_copybenasg := FALSE;
1407 --
1408 else
1409 --
1410 l_copybenasg := TRUE;
1411 --
1412 end if;
1413 --
1414 elsif l_contacts then
1415 hr_utility.set_location('l_contacts',147);
1416 --
1417 l_per_id := null;
1418 l_ctr_contype := null;
1419 --
1420 l_plstab_count := 0;
1421 --
1422 -- Check if benefit assignment exist for dependent.
1423 -- in a loss of dependent status event.
1424 --
1425 if p_dpnt_person_id is not null then
1426 hr_utility.set_location('p_dpnt_person_id: '||p_dpnt_person_id,147);
1427 hr_utility.set_location('l_benasg_effdate: '||l_benasg_effdate,147);
1428 open c_get_contacts
1429 (c_person_id => p_person_id
1430 ,c_contact_person_id => p_dpnt_person_id
1431 ,c_eff_date => l_benasg_effdate
1432 );
1433 fetch c_get_contacts Into l_per_id, l_ctr_contype;
1434 if c_get_contacts%found then
1435 close c_get_contacts;
1436 --
1437 -- If benefit assignment exist, update it.
1438 --
1439 check_bnft_asgn
1440 (p_person_id => l_per_id
1441 --RCHASE BENASG bug fix Start
1442 ,p_emp_person_id => p_person_id
1443 --RCHASE End
1444 ,p_effective_date => l_benasg_effdate
1445 ,p_asg_dets => l_asg_dets
1446 ,p_exists => l_exists
1447 );
1448 --
1449 if l_exists = false then
1450 l_perdetails_set(l_plstab_count).per_id := l_per_id;
1451 l_perdetails_set(l_plstab_count).contype := l_ctr_contype;
1452 l_plstab_count := l_plstab_count+1;
1453 else
1454 --
1455 -- Display an informational message in the benmngle log
1456 -- indicating that a benefit assignment exist has been updated
1457 -- with the current employee assignment information.
1458 --
1459 fnd_message.set_name('BEN','BEN_92552_BNFT_ASSIGN_EXISTS');
1460 if fnd_global.conc_request_id <> -1 then
1461 benutils.write(fnd_message.get);
1462 end if;
1463 --
1464 end if;
1465 else
1466 close c_get_contacts;
1467 --
1468 end if;
1469 --
1470 -- Check is benefit assignment exists for all personal contacts
1471 --
1472 elsif p_redu_hrs_flag = 'Y' then
1473 --
1474 -- Write benefits assignment for participant and all contacts.
1475 --
1476 -- If benefit assignment exist, update it.
1477 --
1478 check_bnft_asgn
1479 (p_person_id => p_person_id
1480 --RCHASE BENASG bug fix Start
1481 ,p_emp_person_id => p_person_id
1482 --RCHASE End
1483 ,p_effective_date => l_benasg_effdate
1484 ,p_asg_dets => l_asg_dets
1485 ,p_exists => l_exists
1486 );
1487 --
1488 if l_exists = false then
1489 l_perdetails_set(l_plstab_count).per_id := p_person_id;
1490 l_perdetails_set(l_plstab_count).contype := null;
1491 l_plstab_count := l_plstab_count+1;
1492 else
1493 --
1494 -- Display an informational message in the benmngle log
1495 -- indicating that a benefit assignment exist has been updated
1496 -- with the current employee assignment information.
1497 --
1498 fnd_message.set_name('BEN','BEN_92552_BNFT_ASSIGN_EXISTS');
1499 if fnd_global.conc_request_id <> -1 then
1500 benutils.write(fnd_message.get);
1501 end if;
1502 end if;
1503 --
1504 l_prev_per_id := -999 ;
1505 for per_id in c_get_all_contacts
1506 (c_person_id => p_person_id
1507 ,c_eff_date => l_benasg_effdate
1508 )
1509 loop
1510 --
1511 -- If benefit assignment exist, update it.
1512 --
1513 /* Bug 9028676 : When reduction in hours life type of Life event is processed on the employee,
1514 do not update the benefits assignment record of the dependents. Commented the proc call to update
1515 the benefits record of the dependent*/
1516 open c_getbenasgdets(per_id.contact_person_id,p_effective_date);
1517 fetch c_getbenasgdets into l_chk_flag;
1518 if(c_getbenasgdets%found) then
1519 l_exists := true;
1520 else
1521 l_exists := false;
1522 end if;
1523 close c_getbenasgdets;
1524
1525
1526 /*check_bnft_asgn
1527 (p_person_id => per_id.contact_person_id
1528 --RCHASE BENASG bug fix Start
1529 ,p_emp_person_id => p_person_id
1530 --RCHASE End
1531 ,p_effective_date => l_benasg_effdate
1532 ,p_asg_dets => l_asg_dets
1533 ,p_exists => l_exists
1534 );*/
1535 --
1536 /* End of Bug 9028676 */
1537 if l_exists = false then
1538 if l_prev_per_id <> per_id.contact_person_id
1539 then
1540 l_prev_per_id := per_id.contact_person_id;
1541 l_perdetails_set(l_plstab_count).per_id := per_id.contact_person_id;
1542 l_perdetails_set(l_plstab_count).contype := per_id.contact_type;
1543 l_plstab_count := l_plstab_count+1;
1544 end if;
1545 else
1546 fnd_message.set_name('BEN','BEN_92554_CON_BNFT_ASS_EXISTS');
1547 if fnd_global.conc_request_id <> -1 then
1548 benutils.write(fnd_message.get);
1549 end if;
1550 end if;
1551 --
1552 end loop;
1553 end if;
1554 elsif l_terminate
1555 then
1556 --
1557 -- If benefit assignment exist, update it.
1558 --
1559 check_bnft_asgn
1560 (p_person_id => p_person_id
1561 --RCHASE BENASG bug fix Start
1562 ,p_emp_person_id => p_person_id
1563 --RCHASE End
1564 ,p_effective_date => l_benasg_effdate
1565 ,p_asg_dets => l_asg_dets
1566 ,p_exists => l_exists
1567 );
1568 --
1569 if l_exists = true then
1570 hr_utility.set_location('benefit assignment exists', 150);
1571 --
1572 l_copybenasg := FALSE;
1573 else
1574 l_perele_num := 0;
1575 l_perdetails_set(l_perele_num).per_id := p_person_id;
1576 l_perdetails_set(l_perele_num).contype := null;
1577 l_copybenasg := TRUE;
1578 --
1579 -- Get all dependents of the employee
1580 --
1581 l_perele_num := l_perele_num+1;
1582 for c_inst in c_getdpntperdets(p_person_id,l_benasg_effdate) loop
1583 --
1584 -- If benefit assignment exist, update it.
1585 --
1586 check_bnft_asgn
1587 (p_person_id => c_inst.contact_person_id
1588 --RCHASE BENASG bug fix Start
1589 ,p_emp_person_id => p_person_id
1590 --RCHASE End
1591 ,p_effective_date => l_benasg_effdate
1592 ,p_asg_dets => l_asg_dets
1593 ,p_exists => l_exists
1594 );
1595 --
1596 if l_exists = false then
1597 --
1598 l_perdetails_set(l_perele_num).per_id := c_inst.contact_person_id;
1599 l_perdetails_set(l_perele_num).contype := 'DPNT';
1600 l_perele_num := l_perele_num+1;
1601 end if;
1602 --
1603 end loop;
1604 --
1605 end if;
1606 hr_utility.set_location(l_proc, 150);
1607 --
1608 end if;
1609 --
1610 -- Create or refresh the benefits assignment
1611 --
1612 if l_benasg_id is null
1613 and l_copybenasg
1614 then
1615 --
1616 -- Loop through the benefits assignment person id set
1617 --
1618 hr_utility.set_location(l_proc||'l_perid_set.count: '||l_perdetails_set.count, 155);
1619 if l_perdetails_set.count > 0 then
1620 --
1621 for l_plstab_count in l_perdetails_set.first .. l_perdetails_set.last
1622 loop
1623 --
1624
1625 -- when the person system type is other make sure the dependent are coverd by the person
1626 -- this happen when the dpnd lose the coverage the dpnt's dpnt get the ben asg
1627 -- in thos situation validate the dpnt has the coverage 2852514
1628 l_dummy := null ;
1629
1630 hr_utility.set_location(' person ' || p_person_id , 156 );
1631 hr_utility.set_location(' dpnt ' || l_perdetails_set(l_plstab_count).per_id , 156 );
1632 if l_perdetails_set(l_plstab_count).per_id <> p_person_id and l_perpet_spt = 'OTHER' then
1633 open c_dptn_cvg (l_perdetails_set(l_plstab_count).per_id , l_benasg_effdate-1 ) ;
1634 fetch c_dptn_cvg into l_dummy ;
1635 close c_dptn_cvg ;
1636
1637 hr_utility.set_location(' dpnt covered ' || l_dummy , 156 );
1638 hr_utility.set_location(' dpnt covered date ' || l_benasg_effdate,156 );
1639
1640 end if ;
1641 --- allow to create be asg when the asg for the same person or
1642 --- person system type is not other or dpnt has the current covrage
1643 --- # 2852514
1644 hr_utility.set_location(' person sys type ' || l_perpet_spt , 156 );
1645
1646 if l_perdetails_set(l_plstab_count).per_id = p_person_id
1647 or l_perpet_spt not in ('OTHER') or l_dummy is not null then
1648
1649 -- Check for dependents
1650 --
1651 if l_perdetails_set(l_plstab_count).contype = 'DPNT' then
1652 --
1653 -- Copy employee primary address to the spouse/family member
1654 -- contact primary address
1655 --
1656 copy_emppradd_ctrpradd
1657 (p_empper_id => p_person_id
1658 ,p_ctrper_id => l_perdetails_set(l_plstab_count).per_id
1659 ,p_benasg_effdate => l_benasg_effdate
1660 ,p_death => l_death
1661 ,p_divorce => l_divorce
1662 );
1663 --
1664 -- Check for a spouses or family members
1665 --
1666 elsif l_perdetails_set(l_plstab_count).contype is not null then
1667 --
1668 -- By pass creating a person type usage for a loss
1669 -- of dependent status and Reduction of Hours event.
1670 --
1671 if l_contacts = FALSE then
1672 hr_utility.set_location(' type and count ' || l_plstab_count || l_perdetails_set(l_plstab_count).contype, 151 );
1673 --
1674 hr_utility.set_location(l_proc||' Spouse Fammem: ', 155);
1675 --
1676 -- Create the person type usage
1677 --
1678 if l_death
1679 and l_perdetails_set(l_plstab_count).contype in ('S','D')
1680 then
1681 --
1682 hr_utility.set_location(l_proc, 160);
1683 if l_perdetails_set(l_plstab_count).contype = 'S' then
1684 l_pet_spt := 'SRVNG_SPS';
1685 else
1686 l_pet_spt := 'SRVNG_DP';
1687 end if;
1688 --
1689 elsif l_divorce
1690 and l_perdetails_set(l_plstab_count).contype = 'S'
1691 then
1692 --
1693 hr_utility.set_location(l_proc, 170);
1694 l_pet_spt := 'FRMR_SPS';
1695 --
1696 hr_utility.set_location('FRMR_SPS :' || l_proc, 172);
1697 elsif l_divorce
1698 and l_perdetails_set(l_plstab_count).contype = 'D'
1699 then
1700 --
1701 hr_utility.set_location('FRMR_DP :' || l_proc, 175);
1702 l_pet_spt := 'FRMR_DP';
1703
1704 elsif l_death
1705 and l_perdetails_set(l_plstab_count).contype in ('C','O','T','A','R')
1706 then
1707 --
1708 hr_utility.set_location(l_proc, 180);
1709 if l_perdetails_set(l_plstab_count).contype = 'R' then
1710 l_pet_spt := 'SRVNG_DPFM';
1711 else
1712 l_pet_spt := 'SRVNG_FMLY_MMBR';
1713 end if;
1714 --
1715 elsif l_divorce
1716 and l_perdetails_set(l_plstab_count).contype in ('C','O','T','A','R') -- Bug 7594105: Added 'R' to the list
1717 then
1718 --
1719 hr_utility.set_location(l_proc, 180);
1720 l_pet_spt := 'FRMR_FMLY_MMBR';
1721 --
1722 end if;
1723 --
1724 -- Get the default person type id for the PET system person type
1725 --
1726 hr_utility.set_location('l_pet_spt: '||l_pet_spt||' '||l_proc, 185);
1727 hr_utility.set_location('l_bgp_id: '||l_bgp_id||' '||l_proc, 185);
1728 open c_getbgpdefpet
1729 (c_bgp_id => l_bgp_id
1730 ,c_pet_spt => l_pet_spt
1731 );
1732 --
1733 fetch c_getbgpdefpet into l_pet_id;
1734 if c_getbgpdefpet%notfound then
1735 close c_getbgpdefpet;
1736 --
1737 -- A person type usage cannot be created for the person when
1738 -- creating the benefits assignment. The person type of the usage
1739 -- does not exist for the business group.
1740 --
1741 hr_utility.set_message(801,'BEN_92113_NOPTUBGPPET');
1742 hr_utility.raise_error;
1743 --
1744 end if;
1745 close c_getbgpdefpet;
1746 hr_utility.set_location(l_proc, 190);
1747
1748
1749 -- added by tilak bug : 2188986
1750 -- if the person_type exist dont create that again
1751 -- the check_Against ben_Asg is not work when the
1752 -- ben_asg creation set to N
1753 check_person_type(
1754 p_person_type_id => l_pet_id
1755 ,p_person_id => l_perdetails_set(l_plstab_count).per_id
1756 ,p_effective_date => l_benasg_effdate
1757 ,p_person_type_usage_id => l_ptu_id
1758 ,p_type_exist => l_type_exist ) ;
1759 hr_utility.set_location(' type exist '|| l_type_exist , 199);
1760 --
1761 if l_type_exist = 'N' then
1762 hr_per_type_usage_internal.create_person_type_usage
1763 (p_person_id => l_perdetails_set(l_plstab_count).per_id
1764 ,p_person_type_id => l_pet_id
1765 ,p_effective_date => l_benasg_effdate
1766 --
1767 ,p_person_type_usage_id => l_ptu_id
1768 ,p_object_version_number => l_ptu_ovn
1769 ,p_effective_start_date => l_esd
1770 ,p_effective_end_date => l_eed
1771 );
1772 end if ;
1773 end if;
1774 hr_utility.set_location(l_proc, 200);
1775 --
1776 -- Copy employee primary address to the spouse/family member
1777 -- contact primary address
1778 --
1779 copy_emppradd_ctrpradd
1780 (p_empper_id => p_person_id
1781 ,p_ctrper_id => l_perdetails_set(l_plstab_count).per_id
1782 ,p_benasg_effdate => l_benasg_effdate
1783 ,p_death => l_death
1784 ,p_divorce => l_divorce
1785 );
1786 --
1787 end if;
1788 --
1789 -- Derive the AEI information
1790 --
1791 ben_assignment_internal.derive_aei_information
1792 (p_person_id => p_person_id
1793 ,p_effective_date => l_benasg_effdate
1794 --
1795 ,p_age => l_age
1796 ,p_adj_serv_date => l_adj_serv_date
1797 ,p_orig_hire_date => l_orig_hire_date
1798 ,p_salary => l_salary
1799 ,p_termn_date => l_termn_date
1800 ,p_termn_reason => l_termn_reason
1801 ,p_absence_date => l_abs_date
1802 ,p_absence_type => l_abs_type
1803 ,p_absence_reason => l_abs_reason
1804 ,p_date_of_hire => l_date_of_hire
1805 );
1806 hr_utility.set_location(l_proc, 210);
1807 --
1808 -- Derive the PTU SPT for the employee as of the event
1809 --
1810 l_count := 0;
1811 for c_inst in c_getperptupetdets(p_person_id,l_asg_dets.effective_start_date) loop
1812 --
1813 l_ptupetspt_set(l_count) := c_inst.system_person_type;
1814 l_count := l_count+1;
1815 hr_utility.set_location('system_person_type ' || c_inst.system_person_type, 220);
1816 --
1817 end loop;
1818 hr_utility.set_location(l_proc, 220);
1819 --
1820 -- Get the base system person type from the PTU system person type set
1821 -- for the person
1822 --
1823 if l_ptupetspt_set.count > 0 then
1824 --
1825 -- Loop until a primary person type is found
1826 --
1827 -- - primary - APL, APL_EX_APL, EMP, EMP_APL, EX_APL, EX_EMP, EX_EMP_APL, OTHER, RETIREE
1828 -- - secondary - BNF, FRMR_SPS, SRVNG_FMLY_MMBR, SRVNG_SPS, FRMR_FMLY_MMBR
1829 -- DPNT, PRTN
1830 --
1831 for l_torrw_num in l_ptupetspt_set.first .. l_ptupetspt_set.last loop
1832 --
1833 if l_ptupetspt_set(l_torrw_num)
1834 in ('APL', 'APL_EX_APL', 'EMP', 'EMP_APL', 'EX_APL', 'EX_EMP', 'EX_EMP_APL', 'OTHER', 'RETIREE')
1835 then
1836 --
1837 l_prptupet_spt := l_ptupetspt_set(l_torrw_num);
1838 exit;
1839 --
1840 end if;
1841 --
1842 end loop;
1843 --
1844 -- Check for multiple PTUs
1845 --
1846 if l_ptupetspt_set.count > 1 then
1847 --
1848 p_perhasmultptus := TRUE;
1849 --
1850 else
1851 --
1852 p_perhasmultptus := FALSE;
1853 --
1854 end if;
1855 --
1856 end if;
1857 --
1858 -- Create the benefits assignment
1859 --
1860 if l_asg_dets.assignment_id is not null then ----Bug 9170856
1861 ben_assignment_api.create_ben_asg
1862 (p_event_mode => TRUE
1863 ,p_effective_date => l_benasg_effdate
1864 ,p_person_id => l_perdetails_set(l_plstab_count).per_id
1865 ,p_assignment_status_type_id => l_asg_dets.assignment_status_type_id
1866 ,p_organization_id => l_asg_dets.organization_id
1867 --
1868 ,p_grade_id => l_asg_dets.grade_id
1869 ,p_position_id => l_asg_dets.position_id
1870 ,p_job_id => l_asg_dets.job_id
1871 ,p_payroll_id => l_asg_dets.payroll_id
1872 ,p_location_id => l_asg_dets.location_id
1873 ,p_supervisor_id => l_asg_dets.supervisor_id
1874 ,p_special_ceiling_step_id => l_asg_dets.special_ceiling_step_id
1875 ,p_people_group_id => l_asg_dets.people_group_id
1876 ,p_soft_coding_keyflex_id => l_asg_dets.soft_coding_keyflex_id
1877 ,p_pay_basis_id => l_asg_dets.pay_basis_id
1878 ,p_change_reason => l_asg_dets.change_reason
1879 ,p_date_probation_end => l_asg_dets.date_probation_end
1880 ,p_default_code_comb_id => l_asg_dets.default_code_comb_id
1881 ,p_employment_category => l_asg_dets.employment_category
1882 ,p_frequency => l_asg_dets.frequency
1883 ,p_internal_address_line => null
1884 ,p_manager_flag => l_asg_dets.manager_flag
1885 ,p_normal_hours => l_asg_dets.normal_hours
1886 ,p_perf_review_period => l_asg_dets.perf_review_period
1887 ,p_perf_review_period_frequency => l_asg_dets.perf_review_period_frequency
1888 ,p_probation_period => l_asg_dets.probation_period
1889 ,p_probation_unit => l_asg_dets.probation_unit
1890 ,p_sal_review_period => l_asg_dets.sal_review_period
1891 ,p_sal_review_period_frequency => l_asg_dets.sal_review_period_frequency
1892 ,p_set_of_books_id => null
1893 ,p_source_type => l_asg_dets.source_type
1894 ,p_time_normal_finish => l_asg_dets.time_normal_finish
1895 ,p_time_normal_start => l_asg_dets.time_normal_start
1896 ,p_bargaining_unit_code => l_asg_dets.bargaining_unit_code
1897 ,p_labour_union_member_flag => l_asg_dets.labour_union_member_flag
1898 ,p_hourly_salaried_code => l_asg_dets.hourly_salaried_code
1899 ,p_ass_attribute_category => l_asg_dets.ass_attribute_category
1900 ,p_ass_attribute1 => l_asg_dets.ass_attribute1
1901 ,p_ass_attribute2 => l_asg_dets.ass_attribute2
1902 ,p_ass_attribute3 => l_asg_dets.ass_attribute3
1903 ,p_ass_attribute4 => l_asg_dets.ass_attribute4
1904 ,p_ass_attribute5 => l_asg_dets.ass_attribute5
1905 ,p_ass_attribute6 => l_asg_dets.ass_attribute6
1906 ,p_ass_attribute7 => l_asg_dets.ass_attribute7
1907 ,p_ass_attribute8 => l_asg_dets.ass_attribute8
1908 ,p_ass_attribute9 => l_asg_dets.ass_attribute9
1909 ,p_ass_attribute10 => l_asg_dets.ass_attribute10
1910 ,p_ass_attribute11 => l_asg_dets.ass_attribute11
1911 ,p_ass_attribute12 => l_asg_dets.ass_attribute12
1912 ,p_ass_attribute13 => l_asg_dets.ass_attribute13
1913 ,p_ass_attribute14 => l_asg_dets.ass_attribute14
1914 ,p_ass_attribute15 => l_asg_dets.ass_attribute15
1915 ,p_ass_attribute16 => l_asg_dets.ass_attribute16
1916 ,p_ass_attribute17 => l_asg_dets.ass_attribute17
1917 ,p_ass_attribute18 => l_asg_dets.ass_attribute18
1918 ,p_ass_attribute19 => l_asg_dets.ass_attribute19
1919 ,p_ass_attribute20 => l_asg_dets.ass_attribute20
1920 ,p_ass_attribute21 => l_asg_dets.ass_attribute21
1921 ,p_ass_attribute22 => l_asg_dets.ass_attribute22
1922 ,p_ass_attribute23 => l_asg_dets.ass_attribute23
1923 ,p_ass_attribute24 => l_asg_dets.ass_attribute24
1924 ,p_ass_attribute25 => l_asg_dets.ass_attribute25
1925 ,p_ass_attribute26 => l_asg_dets.ass_attribute26
1926 ,p_ass_attribute27 => l_asg_dets.ass_attribute27
1927 ,p_ass_attribute28 => l_asg_dets.ass_attribute28
1928 ,p_ass_attribute29 => l_asg_dets.ass_attribute29
1929 ,p_ass_attribute30 => l_asg_dets.ass_attribute30
1930 ,p_title => l_asg_dets.title
1931 ,p_age => l_age
1932 ,p_adjusted_service_date => l_adj_serv_date
1933 ,p_original_hire_date => l_orig_hire_date
1934 ,p_salary => l_salary
1935 ,p_original_person_type => l_prptupet_spt
1936 ,p_termination_date => l_termn_date
1937 ,p_termination_reason => l_termn_reason
1938 ,p_leave_of_absence_date => l_abs_date
1939 ,p_absence_type => l_abs_type
1940 ,p_absence_reason => l_abs_reason
1941 ,p_date_of_hire => l_date_of_hire
1942 ,p_validate => FALSE
1943 --
1944 ,p_assignment_id => l_assignment_id
1945 ,p_object_version_number => p_object_version_number
1946 ,p_effective_start_date => l_date_dummy1
1947 ,p_effective_end_date => l_date_dummy2
1948 ,p_assignment_extra_info_id => l_number_dummy1
1949 ,p_aei_object_version_number => l_number_dummy2
1950 );
1951 end if ; ----Bug 9170856
1952 hr_utility.set_location(l_proc, 220);
1953 --
1954 end if ;
1955 end loop;
1956 --
1957 end if;
1958 --
1959 end if;
1960 --
1961 end if;
1962 --
1963 end if;
1964 --
1965 hr_utility.set_location(' Leaving:'||l_proc, 300);
1966 end copy_empasg_to_benasg;
1967 --
1968 -- ----------------------------------------------------------------------------
1969 -- |-------------------------< check_bnft_asgn >------------------------------|
1970 -- ----------------------------------------------------------------------------
1971 --
1972 procedure check_bnft_asgn
1973 (p_person_id in number
1974 ,p_effective_date in date
1975 ,p_asg_dets in per_all_assignments_f%rowtype
1976 ,p_exists out nocopy boolean
1977 --RCHASE BENASG bug fix Start
1978 ,p_emp_person_id in number default null
1979 --RCHASE End
1980 )
1981 is
1982 --
1983 l_proc varchar2(72):=g_package||'check_bnft_asgn';
1984 --
1985 -- Declare cursors and local variables
1986 --
1987 cursor c_getbenasgdets
1988 Is
1989 select asg.*
1990 from per_all_assignments_f asg
1991 where asg.person_id = p_person_id
1992 and asg.assignment_type = 'B'
1993 and p_effective_date
1994 between asg.effective_start_date and asg.effective_end_date;
1995
1996 l_asg_rec c_getbenasgdets%rowtype;
1997 l_object_version_number per_all_assignments_f.object_version_number%type;
1998 l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1999 l_age varchar2(100);
2000 l_adj_serv_date date;
2001 l_orig_hire_date date;
2002 l_salary varchar2(100);
2003 l_termn_date date;
2004 l_termn_reason varchar2(100);
2005 l_abs_date date;
2006 l_abs_type varchar2(100);
2007 l_abs_reason varchar2(100);
2008 l_date_of_hire date;
2009 l_datetrack_mode varchar2(30);
2010 l_effective_start_date date;
2011 l_effective_end_date date;
2012 l_correction boolean;
2013 l_update boolean;
2014 l_update_override boolean;
2015 l_update_change_insert boolean;
2016
2017 cursor c_getpradddets
2018 (c_per_id in number
2019 )
2020 Is
2021 select 'Y'
2022 from per_addresses adr
2023 where adr.person_id = c_per_id
2024 and adr.primary_flag = 'Y'
2025 and p_effective_date between adr.date_from and nvl(adr.date_to,p_effective_date)
2026 order by adr.date_from desc ;
2027
2028 cursor c_getadddets
2029 (c_per_id in number
2030 )
2031 Is
2032 select *
2033 from per_addresses adr
2034 where adr.person_id = c_per_id
2035 and adr.primary_flag = 'Y'
2036 order by adr.date_from desc ;
2037
2038 l_addr c_getadddets%rowtype;
2039
2040 l_flag varchar2(1);
2041 --
2042 BEGIN
2043 hr_utility.set_location(' Entering:'||l_proc, 100);
2044 hr_utility.set_location(' p_person_id:'||p_person_id, 100);
2045 hr_utility.set_location(' p_emp_person_id:'||p_emp_person_id, 100);
2046 hr_utility.set_location(' p_effective_date:'||p_effective_date, 100);
2047 --
2048 -- Check if benefit assignment exist.
2049 --
2050 open c_getbenasgdets;
2051 --
2052 fetch c_getbenasgdets Into l_asg_rec;
2053 if c_getbenasgdets%found then
2054 close c_getbenasgdets;
2055 p_exists := true;
2056 --
2057 -- Bug : 2793136 : If benefits assignment already exists and
2058 -- employee assignment is not found then simply return.
2059 --
2060 if p_asg_dets.assignment_id is null
2061 then
2062 hr_utility.set_location('Leaving :' || l_proc, 100);
2063 return;
2064 end if;
2065 hr_utility.set_location(' exist ben asg :'||l_asg_rec.effective_start_date ||'-'||p_effective_date, 100);
2066
2067
2068 /*Bug 9744453:While updating benefits assignment, check whether address record exits for the dependent as
2069 of update date. If there exists no address record, then create one.*/
2070 open c_getpradddets(p_person_id);
2071 fetch c_getpradddets into l_flag;
2072 if(c_getpradddets%notfound) then
2073 close c_getpradddets;
2074
2075 open c_getadddets(p_person_id);
2076 fetch c_getadddets into l_addr;
2077 close c_getadddets;
2078
2079 copy_emppradd_ctrpradd
2080 (p_empper_id => p_emp_person_id
2081 ,p_ctrper_id => p_person_id
2082 ,p_benasg_effdate => p_effective_date
2083 ,p_death => g_death
2084 ,p_divorce => g_divorce
2085 ,p_date => l_addr.date_to+1
2086 );
2087 else
2088 close c_getpradddets;
2089 end if;
2090 g_death := false;
2091 g_divorce := false;
2092 /*End of Bug 9744453*/
2093 --
2094 if l_asg_rec.effective_start_date <> p_effective_date then
2095 --
2096 -- Check for valid date track mode.
2097 --
2098 dt_api.find_dt_upd_modes
2099 (p_effective_date => p_effective_date,
2100 p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
2101 p_base_key_column => 'assignment_id',
2102 p_base_key_value => l_asg_rec.assignment_id,
2103 p_correction => l_correction,
2104 p_update => l_update,
2105 p_update_override => l_update_override,
2106 p_update_change_insert => l_update_change_insert);
2107 --
2108 if l_update_override then
2109 --
2110 l_datetrack_mode := hr_api.g_update_override;
2111 --
2112 elsif l_update then
2113 --
2114 l_datetrack_mode := hr_api.g_update;
2115 --
2116 else
2117 --
2118 l_datetrack_mode := hr_api.g_correction;
2119 --
2120 end if;
2121 --
2122 -- Update the current benefit assignment.
2123 --
2124 --
2125 -- Derive the AEI information
2126 --
2127 ben_assignment_internal.derive_aei_information
2128 --RCHASE BENASG bug fix Start
2129 (p_person_id => nvl(p_emp_person_id, p_person_id)
2130 --RCHASE End
2131 ,p_effective_date => p_effective_date
2132 --
2133 ,p_age => l_age
2134 ,p_adj_serv_date => l_adj_serv_date
2135 ,p_orig_hire_date => l_orig_hire_date
2136 ,p_salary => l_salary
2137 ,p_termn_date => l_termn_date
2138 ,p_termn_reason => l_termn_reason
2139 ,p_absence_date => l_abs_date
2140 ,p_absence_type => l_abs_type
2141 ,p_absence_reason => l_abs_reason
2142 ,p_date_of_hire => l_date_of_hire
2143 );
2144 --
2145 l_object_version_number := l_asg_rec.object_version_number;
2146 l_special_ceiling_step_id := p_asg_dets.special_ceiling_step_id;
2147 --
2148 -- Update the benefits assignment
2149 --
2150 hr_utility.set_location(' updating assignment:', 100);
2151 ben_assignment_api.update_ben_asg
2152 (p_validate => FALSE
2153 ,p_effective_date => p_effective_date
2154 ,p_datetrack_update_mode => l_datetrack_mode
2155 ,p_assignment_id => l_asg_rec.assignment_id
2156 ,p_object_version_number => l_object_version_number
2157 --
2158 ,p_grade_id => p_asg_dets.grade_id
2159 ,p_position_id => p_asg_dets.position_id
2160 ,p_job_id => p_asg_dets.job_id
2161 ,p_payroll_id => p_asg_dets.payroll_id
2162 ,p_location_id => p_asg_dets.location_id
2163 ,p_special_ceiling_step_id => l_special_ceiling_step_id
2164 ,p_organization_id => p_asg_dets.organization_id
2165 ,p_people_group_id => p_asg_dets.people_group_id
2166 ,p_pay_basis_id => p_asg_dets.pay_basis_id
2167 ,p_employment_category => p_asg_dets.employment_category
2168 --
2169 ,p_supervisor_id => p_asg_dets.supervisor_id
2170 ,p_change_reason => p_asg_dets.change_reason
2171 ,p_date_probation_end => p_asg_dets.date_probation_end
2172 ,p_default_code_comb_id => p_asg_dets.default_code_comb_id
2173 ,p_frequency => p_asg_dets.frequency
2174 ,p_internal_address_line => p_asg_dets.internal_address_line
2175 ,p_manager_flag => p_asg_dets.manager_flag
2176 ,p_normal_hours => p_asg_dets.normal_hours
2177 ,p_perf_review_period => p_asg_dets.perf_review_period
2178 ,p_perf_review_period_frequency => p_asg_dets.perf_review_period_frequency
2179 ,p_probation_period => p_asg_dets.probation_period
2180 ,p_probation_unit => p_asg_dets.probation_unit
2181 ,p_sal_review_period => p_asg_dets.sal_review_period
2182 ,p_sal_review_period_frequency => p_asg_dets.sal_review_period_frequency
2183 ,p_set_of_books_id => null
2184 ,p_source_type => p_asg_dets.source_type
2185 ,p_time_normal_finish => p_asg_dets.time_normal_finish
2186 ,p_time_normal_start => p_asg_dets.time_normal_start
2187 ,p_bargaining_unit_code => p_asg_dets.bargaining_unit_code
2188 ,p_labour_union_member_flag => p_asg_dets.labour_union_member_flag
2189 ,p_hourly_salaried_code => p_asg_dets.hourly_salaried_code
2190 ,p_ass_attribute_category => p_asg_dets.ass_attribute_category
2191 ,p_ass_attribute1 => p_asg_dets.ass_attribute1
2192 ,p_ass_attribute2 => p_asg_dets.ass_attribute2
2193 ,p_ass_attribute3 => p_asg_dets.ass_attribute3
2194 ,p_ass_attribute4 => p_asg_dets.ass_attribute4
2195 ,p_ass_attribute5 => p_asg_dets.ass_attribute5
2196 ,p_ass_attribute6 => p_asg_dets.ass_attribute6
2197 ,p_ass_attribute7 => p_asg_dets.ass_attribute7
2198 ,p_ass_attribute8 => p_asg_dets.ass_attribute8
2199 ,p_ass_attribute9 => p_asg_dets.ass_attribute9
2200 ,p_ass_attribute10 => p_asg_dets.ass_attribute10
2201 ,p_ass_attribute11 => p_asg_dets.ass_attribute11
2202 ,p_ass_attribute12 => p_asg_dets.ass_attribute12
2203 ,p_ass_attribute13 => p_asg_dets.ass_attribute13
2204 ,p_ass_attribute14 => p_asg_dets.ass_attribute14
2205 ,p_ass_attribute15 => p_asg_dets.ass_attribute15
2206 ,p_ass_attribute16 => p_asg_dets.ass_attribute16
2207 ,p_ass_attribute17 => p_asg_dets.ass_attribute17
2208 ,p_ass_attribute18 => p_asg_dets.ass_attribute18
2209 ,p_ass_attribute19 => p_asg_dets.ass_attribute19
2210 ,p_ass_attribute20 => p_asg_dets.ass_attribute20
2211 ,p_ass_attribute21 => p_asg_dets.ass_attribute21
2212 ,p_ass_attribute22 => p_asg_dets.ass_attribute22
2213 ,p_ass_attribute23 => p_asg_dets.ass_attribute23
2214 ,p_ass_attribute24 => p_asg_dets.ass_attribute24
2215 ,p_ass_attribute25 => p_asg_dets.ass_attribute25
2216 ,p_ass_attribute26 => p_asg_dets.ass_attribute26
2217 ,p_ass_attribute27 => p_asg_dets.ass_attribute27
2218 ,p_ass_attribute28 => p_asg_dets.ass_attribute28
2219 ,p_ass_attribute29 => p_asg_dets.ass_attribute29
2220 ,p_ass_attribute30 => p_asg_dets.ass_attribute30
2221 ,p_title => p_asg_dets.title
2222 ,p_age => l_age
2223 ,p_adjusted_service_date => l_adj_serv_date
2224 ,p_original_hire_date => l_orig_hire_date
2225 ,p_salary => l_salary
2226 ,p_termination_date => l_termn_date
2227 ,p_termination_reason => l_termn_reason
2228 ,p_leave_of_absence_date => l_abs_date
2229 ,p_absence_type => l_abs_type
2230 ,p_absence_reason => l_abs_reason
2231 ,p_date_of_hire => l_date_of_hire
2232 --
2233 ,p_effective_start_date => l_effective_start_date
2234 ,p_effective_end_date => l_effective_end_date
2235 );
2236 end if;
2237 else
2238 close c_getbenasgdets;
2239 p_exists := false;
2240 end if;
2241 hr_utility.set_location(' Leaving:'||l_proc, 100);
2242 end check_bnft_asgn;
2243 --
2244 -- ----------------------------------------------------------------------------
2245 -- |-------------------------< derive_aei_information >-----------------------|
2246 -- ----------------------------------------------------------------------------
2247 --
2248 procedure derive_aei_information
2249 (p_effective_date in date
2250 ,p_person_id in number
2251 --
2252 ,p_age out nocopy number
2253 ,p_adj_serv_date out nocopy date
2254 ,p_orig_hire_date out nocopy date
2255 ,p_salary out nocopy varchar2
2256 ,p_termn_date out nocopy date
2257 ,p_termn_reason out nocopy varchar2
2258 ,p_absence_date out nocopy date
2259 ,p_absence_type out nocopy varchar2
2260 ,p_absence_reason out nocopy varchar2
2261 ,p_date_of_hire out nocopy date
2262 )
2263 is
2264 --
2265 l_proc varchar2(72):=g_package||'derive_aei_information';
2266 --
2267 -- Declare cursors and local variables
2268 --
2269 l_emp_dob date;
2270 l_emp_doh date;
2271 l_emp_asd date;
2272 l_emp_ohd date;
2273 l_emp_tmd date;
2274 l_abs_date date;
2275 l_age number;
2276 l_emp_salary varchar2(100);
2277 l_emp_tmr varchar2(100);
2278 l_abs_type varchar2(100);
2279 l_abs_reason varchar2(100);
2280 l_emp_perid number;
2281 --
2282 cursor c_getempdtperdets
2283 (c_person_id in number
2284 ,c_eff_date in date
2285 )
2286 Is
2287 select per.date_of_birth,
2288 pds.adjusted_svc_date,
2289 per.original_date_of_hire,
2290 pds.actual_termination_date,
2291 pds.leaving_reason,
2292 pds.date_start
2293 from per_all_people_f per,
2294 per_periods_of_service pds
2295 where per.person_id = c_person_id
2296 and per.person_id = pds.person_id
2297 and c_eff_date
2298 between per.effective_start_date and per.effective_end_date
2299 and pds.date_start = (select max(date_start) from per_periods_of_service
2300 pps where pps.person_id = c_person_id) ;
2301 --
2302 cursor c_getempabsence
2303 (c_person_id in number
2304 ,c_eff_date in date
2305 )
2306 Is
2307 select paa.date_start,
2308 paa.absence_attendance_type_id,
2309 paa.abs_attendance_reason_id
2310 from per_absence_attendances paa
2311 where paa.person_id = c_person_id
2312 and c_eff_date
2313 between nvl(paa.date_start,c_eff_date) and nvl(paa.date_end,c_eff_date);
2314 --
2315 cursor c_getsalary
2316 (c_person_id in number
2317 ,c_eff_date in date
2318 )
2319 Is
2320 select pyp.proposed_salary_n
2321 from per_all_assignments_f asg,
2322 per_pay_proposals pyp
2323 where asg.assignment_id = pyp.assignment_id
2324 and c_eff_date
2325 between asg.effective_start_date and asg.effective_end_date
2326 and asg.person_id = c_person_id
2327 and asg.primary_flag = 'Y'
2328 and asg.assignment_type = 'E'
2329 and pyp.approved = 'Y'
2330 and nvl(pyp.change_date,hr_api.g_sot) <= c_eff_date
2331 order by pyp.change_date desc;
2332 --
2333 begin
2334 hr_utility.set_location('Entering:'|| l_proc, 10);
2335 hr_utility.set_location('p_person_id '||p_person_id, 300);
2336 hr_utility.set_location('p_effective_date '||p_effective_date, 300);
2337 --
2338 -- Check mandatory arguments
2339 --
2340 hr_api.mandatory_arg_error
2341 (p_api_name => l_proc,
2342 p_argument => 'p_person_id',
2343 p_argument_value => p_person_id
2344 );
2345 --
2346 hr_api.mandatory_arg_error
2347 (p_api_name => l_proc,
2348 p_argument => 'p_effective_date',
2349 p_argument_value => p_effective_date
2350 );
2351 --
2352 -- Get the date of birth, adjusted service date,trmination date,reason and
2353 -- original hire date of the employee
2354 --
2355 hr_utility.set_location('p_per_id: '||p_person_id||' '||l_proc, 20);
2356 hr_utility.set_location('p_eff_date: '||p_effective_date||' '||l_proc, 20);
2357 open c_getempdtperdets
2358 (c_person_id => p_person_id
2359 ,c_eff_date => p_effective_date
2360 );
2361 fetch c_getempdtperdets into l_emp_dob, l_emp_asd, l_emp_ohd,l_emp_tmd,l_emp_tmr,l_emp_doh;
2362 close c_getempdtperdets;
2363 --
2364 if l_emp_dob is not null then
2365 --
2366 -- Bug : 1782261
2367 -- Changed the ROUND of months to FLOOR to get the correct age .
2368 --
2369 l_age := floor(months_between(p_effective_date,l_emp_dob)/12);
2370 --
2371 -- Bug : 1782261
2372 end if;
2373 --
2374 -- Get the most recent approved salary for the employee
2375 --
2376 open c_getsalary
2377 (c_person_id => p_person_id
2378 ,c_eff_date => p_effective_date
2379 );
2380 --
2381 fetch c_getsalary into l_emp_salary;
2382 close c_getsalary;
2383 --
2384 open c_getempabsence
2385 (c_person_id => p_person_id
2386 ,c_eff_date => p_effective_date
2387 );
2388 --
2389 fetch c_getempabsence into l_abs_date,l_abs_type,l_abs_reason;
2390 if c_getempabsence%notfound then
2391 null;
2392 end if;
2393 close c_getempabsence;
2394 --
2395 -- Set OUT parameters
2396 --
2397 p_age := l_age;
2398 p_adj_serv_date := l_emp_asd;
2399 p_orig_hire_date := l_emp_ohd;
2400 p_salary := l_emp_salary;
2401 p_termn_date := l_emp_tmd;
2402 p_termn_reason := l_emp_tmr;
2403 p_absence_date := l_abs_date;
2404 p_absence_type := l_abs_type;
2405 p_absence_reason := l_abs_reason;
2406 p_date_of_hire := l_emp_doh;
2407 --
2408 hr_utility.set_location(' Leaving:'||l_proc, 100);
2409 end derive_aei_information;
2410 --
2411 end ben_assignment_internal;