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