[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_DPNT_ELIG_SS
Source
1 PACKAGE BODY ben_determine_dpnt_elig_ss as
2 /* $Header: bendpels.pkb 120.1.12000000.2 2007/09/12 10:42:50 vborkar noship $ */
3 /*
4 +==============================================================================+
5 | Copyright (c) 1998 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==============================================================================+
9 --
10 Name
11 Dependent Eligibility for Self Service
12 Purpose
13 This package loops through all electable choices for a passed in per_in_ler
14 and determines if the dependent (person_contact_id is passed in) is eligible. This
15 is called from self service enrollment. This logic is similar to the Dependent
16 Designation Form logic so changes made here should also be made in the forms library
17 for the Dependent Designation Form.
18
19 History
20 Date Who Version What?
21 ---- --- ------- -----
22 03 Aug 00 Thayden 115.0 Created.
23 12 Feb 02 Shdas 115.1 Added fmly_mmbr_cd_exist proc.
24 01 Apr 02 Shdas 115.2 Changed procedure Main(bug fix2049316)
25 06 May 02 Shdas 115.3 Added exception.
26 29 May 02 Shdas 115.5 Added code to update elig dpnt record
27 if found eligble.
28 31-Oct-02 ashrivas 115.6 bug - 2419139. passing in lf_evt_ocrd_dt for
29 effective date for creating dpnt and removed the
30 date clause from the c_elig_dpnt cursor
31 31-Oct-02 ashrivas 115.7 Added Whenever OSError..
32 04-Dec-02 kmullapu 115.8 Added create_contact_w,update_person_w
33 NOCOPY changes
34 21-Jun-03 hnarayan 115.10 bug 3013964 - passed lf_evt_ocrd_dt as null
35 to dpnt elig process and create_elig_dpnt
36 if it is less than effective date.
37 Also added fnd_msg_pub.initialize
38 31-jul-03 hnarayan 115.11 bug 3042223 - changed create_contact_w
39 to set contact effective_start_date
40 based on max(PESD, min(RSD, EFF Dt))
41 09 Mar 05 vborkar 115.12 Bug 4218944 - Added wrapper procedure
42 update_contact_w which in turn calls
43 HR_CONTACT_REL_API.update_contact_relationship
44 and treats exceptions in more user friendly
45 manner.
46 26-May-06 bmanyam 115.13 5100008 - EGD elig_thru_dt is the
47 date eligibility is lost. Previously the elig_thru_dt
48 was updated with PDP cvg_thru_dt.
49 12-Sep-07 vborkar 115.14 6279654 - Called ben_env_object.init to initialize
50 business_group_id and effective_date in the environment record.
51 */
52 --------------------------------------------------------------------------------+
53 --
54 g_package varchar2(80) := 'ben_determine_dpnt_elig_ss';
55
56
57 procedure main
58 (p_pgm_id in number
59 ,p_per_in_ler_id in number
60 ,p_person_id in number
61 ,p_contact_person_id in number
62 ,p_contact_relationship_id in number
63 ,p_effective_date in date
64 --,p_business_group_id in number
65 )
66 is
67 --
68 l_proc varchar2(80):= g_package||'.main';
69 --
70 l_dependent_eligible_flag varchar2(30);
71 l_cvrd_flag varchar2(30);
72 l_inelig_rsn_cd varchar2(30);
73 --
74 l_elig_per_id number;
75 l_elig_per_opt_id number;
76 l_elig_dpnt_id number;
77 l_elig_cvrd_dpnt_id number;
78 l_object_version_number number;
79 l_pdp_effective_start_date date;
80 l_pdp_effective_end_date date;
81 l_datetrack_mode varchar2(30);
82 l_correction boolean;
83 l_update boolean;
84 l_update_override boolean;
85 l_update_change_insert boolean;
86 l_business_group_id number;
87 --
88
89 cursor c_elig_per_elctbl_chc is
90 select pl_id,
91 oipl_id,
92 ptip_id,
93 business_group_id,
94 dpnt_cvg_strt_dt_cd,
95 enrt_cvg_strt_dt_cd,
96 dpnt_cvg_strt_dt_rl,
97 elig_per_elctbl_chc_id
98 from ben_elig_per_elctbl_chc epe
99 where epe.per_in_ler_id = p_per_in_ler_id
100 and epe.pgm_id = p_pgm_id
101 and epe.elctbl_flag = 'Y'
102 and epe.alws_dpnt_dsgn_flag = 'Y';
103
104 cursor c_elig_dpnt (p_dpnt_person_id number,
105 p_elig_per_elctbl_chc_id number,
106 p_effective_date date) is
107 select epe.elig_dpnt_id,
108 epe.object_version_number,
109 epe.dpnt_inelig_flag
110 from ben_elig_dpnt epe
111 where epe.dpnt_person_id = p_dpnt_person_id
112 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
113 --bug 2419139
114 --and p_effective_date between
115 -- nvl(epe.elig_strt_dt,p_effective_date)
116 --and nvl(epe.elig_thru_dt,p_effective_date);
117 --
118 l_elig_dpnt c_elig_dpnt%rowtype;
119
120 cursor c_per_in_ler is
121 select pil.lf_evt_ocrd_dt,
122 pil.ler_id
123 from ben_per_in_ler pil,
124 ben_ler_f ler
125 where pil.person_id = p_person_id
126 and pil.business_group_id = l_business_group_id
127 and pil.per_in_ler_stat_cd = 'STRTD'
128 and pil.ler_id = ler.ler_id
129 and ler.typ_cd <> 'COMP'
130 and p_effective_date
131 between nvl(ler.effective_start_date,p_effective_date)
132 and nvl(ler.effective_end_date,p_effective_date)
133 and pil.per_in_ler_id = p_per_in_ler_id;
134 --
135 l_per_in_ler c_per_in_ler%rowtype;
136 --
137 -- bug 3013964
138 l_lf_evt_ocrd_dt date ;
139 --
140 cursor c_ler_chg_dep(l_level varchar2,l_pl_id number,l_ptip_id number) is
141 select chg.cvg_eff_strt_cd,
142 chg.cvg_eff_end_cd,
143 chg.cvg_eff_strt_rl,
144 chg.cvg_eff_end_rl,
145 chg.ler_chg_dpnt_cvg_cd,
146 chg.ler_chg_dpnt_cvg_rl
147 from ben_ler_chg_dpnt_cvg_f chg
148 where chg.ler_id = l_per_in_ler.ler_id
149 and chg.business_group_id = l_business_group_id
150 and decode(l_level,
151 'PL',l_pl_id,
152 'PTIP',l_ptip_id,
153 'PGM', p_pgm_id) =
154 decode(l_level,
155 'PL',chg.pl_id,
156 'PTIP',chg.ptip_id,
157 'PGM', chg.pgm_id)
158 and nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)
159 between nvl(chg.effective_start_date,p_effective_date)
160 and nvl(chg.effective_end_date,p_effective_date);
161 --
162 l_ler_chg_dep c_ler_chg_dep%rowtype;
163
164 cursor c_plan(l_pl_id number) is
165 select pl.dpnt_dsgn_cd,
166 pl.dpnt_cvg_strt_dt_cd,
167 pl.dpnt_cvg_strt_dt_rl,
168 pl.dpnt_cvg_end_dt_cd,
169 pl.dpnt_cvg_end_dt_rl
170 from ben_pl_f pl
171 where pl.pl_id = l_pl_id
172 and pl.business_group_id = l_business_group_id
173 and nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)
174 between nvl(pl.effective_start_date,p_effective_date)
175 and nvl(pl.effective_end_date,p_effective_date);
176 --
177 l_plan c_plan%rowtype;
178 --
179 cursor c_pgm is
180 select pgm.dpnt_dsgn_lvl_cd,
181 pgm.dpnt_dsgn_cd,
182 pgm.dpnt_cvg_strt_dt_cd,
183 pgm.dpnt_cvg_strt_dt_rl,
184 pgm.dpnt_cvg_end_dt_cd,
185 pgm.dpnt_cvg_end_dt_rl
186 from ben_pgm_f pgm
187 where pgm.pgm_id = p_pgm_id
188 and pgm.business_group_id = l_business_group_id
189 and nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)
190 between nvl(pgm.effective_start_date,p_effective_date)
191 and nvl(pgm.effective_end_date,p_effective_date);
192 --
193 l_pgm c_pgm%rowtype;
194 l_level ben_pgm_f.dpnt_dsgn_lvl_cd%type;
195
196 cursor c_ptip(l_ptip_id number) is
197 select ptip.dpnt_dsgn_cd,
198 ptip.dpnt_cvg_strt_dt_cd,
199 ptip.dpnt_cvg_strt_dt_rl,
200 ptip.dpnt_cvg_end_dt_cd,
201 ptip.dpnt_cvg_end_dt_rl
202 from ben_ptip_f ptip
203 where ptip.ptip_id = l_ptip_id
204 and ptip.business_group_id = l_business_group_id
205 and nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)
206 between nvl(ptip.effective_start_date,p_effective_date)
207 and nvl(ptip.effective_end_date,p_effective_date);
208 --
209 -- Gets the enrolment information for this plan
210 --
211 cursor c_plan_enrolment_info(l_pl_id number) is
212 select pen.prtt_enrt_rslt_id
213 from ben_prtt_enrt_rslt_f pen
214 where pen.person_id=p_person_id and
215 pen.sspndd_flag='N' and
216 pen.prtt_enrt_rslt_stat_cd is null and
217 pen.effective_end_date = hr_api.g_eot and
218 nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)-1 <=
219 pen.enrt_cvg_thru_dt and
220 pen.enrt_cvg_strt_dt < pen.effective_end_date
221 and pen.pl_id = l_pl_id
222 and pen.pgm_id = p_pgm_id;
223 --
224 -- Gets the enrolment information for this oipl
225 --
226 cursor c_oipl_enrolment_info(l_oipl_id number) is
227 select pen.prtt_enrt_rslt_id
228 from ben_prtt_enrt_rslt_f pen
229 where pen.person_id=p_person_id and
230 pen.sspndd_flag='N' and
231 pen.prtt_enrt_rslt_stat_cd is null and
232 pen.effective_end_date = hr_api.g_eot and
233 nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)-1 <=
234 pen.enrt_cvg_thru_dt and
235 pen.enrt_cvg_strt_dt < pen.effective_end_date and
236 pen.oipl_id=l_oipl_id
237 and pen.pgm_id = p_pgm_id;
238 --
239 l_prtt_enrt_rslt_id number;
240
241 cursor c_pdp(l_prtt_enrt_rslt_id number) is
242 select pdp.object_version_number,
243 pdp.elig_cvrd_dpnt_id,
244 pdp.effective_start_date,
245 pdp.cvg_strt_dt,
246 pdp.effective_end_date
247 from ben_elig_cvrd_dpnt_f pdp,
248 ben_per_in_ler pil
249 where pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
250 and pdp.business_group_id = l_business_group_id
251 and pdp.dpnt_person_id = p_contact_person_id
252 and nvl(l_per_in_ler.lf_evt_ocrd_dt,p_effective_date)
253 between pdp.effective_start_date and pdp.effective_end_date
254 and nvl(l_per_in_ler.lf_evt_ocrd_dt, p_effective_date)
255 between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
256 and pil.per_in_ler_id=pdp.per_in_ler_id
257 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
258 --
259
260 cursor c_bg_id is
261 select business_group_id
262 from per_all_people_f
263 where person_id = p_person_id
264 and p_effective_date between effective_start_date and effective_end_date;
265
266 l_pdp c_pdp%rowtype;
267 l_ptip c_ptip%rowtype;
268 l_cvg_end_cd varchar2(30);
269 l_cvg_end_rl number(15);
270 l_cvg_end_dt date;
271 l_pdp_rec_found boolean := false;
272 l_look_again boolean := false;
273 l_dpnt_cvg_strt_dt date;
274 l_egd_elig_thru_dt date; -- 5100008
275 --
276 l_dummy varchar2(1);
277
278 begin
279 hr_utility.set_location ('Entering '|| l_proc,10);
280 --6279654
281 if fnd_global.conc_request_id = -1 then
282 --
283 if ben_env_object.g_global_env_rec.business_group_id is null then
284 --
285 open c_bg_id;
286 fetch c_bg_id into l_business_group_id;
287 close c_bg_id;
288
289 hr_utility.set_location ('business_group_id '|| l_business_group_id, 8085);
290 hr_utility.set_location ('effective_date '|| p_effective_date, 8085);
291
292 ben_env_object.init(p_business_group_id => l_business_group_id,
293 p_effective_date => p_effective_date,
294 p_thread_id => 1,
295 p_chunk_size => 1,
296 p_threads => 1,
297 p_max_errors => 1,
298 p_benefit_action_id => null);
299 --
300 end if;
301 end if;
302 --end 6279654
303
304 --
305 for epe in c_elig_per_elctbl_chc loop
306
307 l_business_group_id := epe.business_group_id;
308 --Need to get the lf_evt_ocrd_dt --bug 2419139
309 open c_per_in_ler;
310 fetch c_per_in_ler into l_per_in_ler;
311 close c_per_in_ler;
312 --
313 -- bug 3013964
314 -- if life event occurred date is less than effective date then we have to
315 -- pass null for lf_evt_ocrd_dt instead of l_per_in_ler.lf_evt_ocrd_dt.
316 -- Otherwise, dependent elig prcess will check for the contact eligibility
317 -- as of the life event date, even when the contact is added as of effective
318 -- date. This needs to be avoided.
319 -- Also, the same applies for the elig_strt_dt when calling create_elig_dpnt
320 -- So set the elig_strt_dt accordingly.
321 -- This is now in sync with forms library for Dpnt Designation form.
322 --
323 l_lf_evt_ocrd_dt := l_per_in_ler.lf_evt_ocrd_dt ;
324 --
325 if l_lf_evt_ocrd_dt < p_effective_date then
326 l_lf_evt_ocrd_dt := null ;
327 end if;
328 --Always call ben_evaluate_dpnt_elg_profiles.main
329 --
330 l_dependent_eligible_flag := 'N';
331 ben_evaluate_dpnt_elg_profiles.main(
332 p_contact_relationship_id => p_contact_relationship_id
333 , p_contact_person_id => p_contact_person_id
334 , p_pgm_id => p_pgm_id
335 , p_pl_id => epe.pl_id
336 , p_ptip_id => epe.ptip_id
337 , p_oipl_id => epe.oipl_id
338 , p_business_group_id => epe.business_group_id
339 , p_per_in_ler_id => p_per_in_ler_id
340 , p_effective_date => p_effective_date
341 , p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
342 , p_level => null
343 , p_dependent_eligible_flag => l_dependent_eligible_flag --out
344 , p_dpnt_inelig_rsn_cd => l_inelig_rsn_cd --out
345 );
346 --
347 -- 5100008 : EGD ELIG_THRU_DT
348 l_egd_elig_thru_dt := ben_evaluate_dpnt_elg_profiles.get_elig_change_dt;
349 --
350 open c_elig_dpnt(p_contact_person_id,
351 epe.elig_per_elctbl_chc_id,
352 p_effective_date);
353 fetch c_elig_dpnt into l_elig_dpnt;
354 if c_elig_dpnt%notfound then
355 --
356 /* if no elig dpnt record but is now found eligible then create elig dpnt record.
357 if no elig dpnt record and is still found inelig then do nothing
358 */
359 if l_dependent_eligible_flag = 'N' then
360 --
361 null;
362 --
363 else
364 --
365
366 ben_ELIG_DPNT_api.get_elig_per_id(
367 p_person_id => p_person_id
368 ,p_pgm_id => p_pgm_id
369 ,p_pl_id => epe.pl_id
370 ,p_oipl_id => epe.oipl_id
371 ,p_business_group_id => epe.business_group_id
372 ,p_effective_date => p_effective_date
373 ,p_elig_per_id => l_elig_per_id
374 ,p_elig_per_opt_id => l_elig_per_opt_id);
375 --
376 ben_elig_dpnt_api.create_elig_dpnt
377 (p_validate => FALSE
378 ,p_ELIG_DPNT_ID => l_ELIG_DPNT_ID --out
379 ,p_create_dt => p_effective_date
380 ,p_ELIG_CVRD_DPNT_ID => null
381 ,p_BUSINESS_GROUP_ID => epe.business_group_id
382 ,p_elig_per_elctbl_chc_id => epe.elig_per_elctbl_chc_id
383 ,p_DPNT_PERSON_ID => p_contact_person_id
384 ,p_ELIG_STRT_DT => nvl(l_lf_evt_ocrd_dt,p_effective_date)
385 ,p_ELIG_THRU_DT => hr_api.g_eot
386 ,p_elig_per_id => l_elig_per_id
387 ,p_elig_per_opt_id => l_elig_per_opt_id
388 ,p_OBJECT_VERSION_NUMBER => l_object_version_number
389 ,p_ovrdn_flag => 'N'
390 ,p_per_in_ler_id => p_per_in_ler_id
391 ,p_ovrdn_thru_dt => null
392 ,p_effective_date => p_effective_date);
393 --
394 end if; --flag
395 --
396 elsif c_elig_dpnt%found then
397 --
398 /* If elig dpnt record exists and is now found eligible after evaluation of elig
399 profiles then check dpnt_inelig_flag on the elig dpnt record.If it's Y, that means
400 he was ineligible.Since he is now eligible again,update elig dpnt record to make it N.
401 Else if the flag is N, it means he was eligible.Since he is still eligible,do nothing.
402
403 If elig dpnt record exists and is now found ineligible after evaluation of elig
404 profiles then check if the dependent person is covered--determine coverage end date and
405 update elig covered dependent record.If dpnt_inelig_flag on the elig dpnt record is N ,
406 which means he was eligible,update the elig dpnt record to set it to Y with the elig rsn cd.
407
408 */
409 if l_dependent_eligible_flag = 'Y' then
410 --
411 if l_elig_dpnt.dpnt_inelig_flag = 'Y' then
412 --
413 ben_elig_dpnt_api.update_elig_dpnt(
414 p_elig_dpnt_id => l_elig_dpnt.elig_dpnt_id
415 ,p_object_version_number => l_elig_dpnt.object_version_number
416 ,p_effective_date => p_effective_date
417 ,p_elig_thru_dt => hr_api.g_eot
418 ,p_dpnt_inelig_flag => 'N'
419 ,p_inelg_rsn_cd => null
420 );
421 --
422 else
423 null;
424 end if;
425 --
426 else
427 --
428 -- Determine designation level
429 --
430 if p_pgm_id is not null then
431 --
432 -- find the level from the program
433 --
434 open c_pgm;
435 fetch c_pgm into l_pgm;
436 close c_pgm;
437 l_level := l_pgm.dpnt_dsgn_lvl_cd;
438 else
439 l_level := 'PL';
440
441 end if;
442 --
443 -- added following 3 lines for core bug 2189561
444 if l_level is null then
445 l_level := 'PL';
446 end if;
447
448 -- if p_per_in_ler_id is not null then
449 -- open c_per_in_ler;
450 -- fetch c_per_in_ler into l_per_in_ler;
451 -- close c_per_in_ler;
452 -- end if;
453
454 -- Determine coverage end date
455
456 open c_ler_chg_dep(l_level,epe.pl_id,epe.ptip_id);
457 fetch c_ler_chg_dep into l_ler_chg_dep;
458 if c_ler_chg_dep%found then
459 l_cvg_end_cd := l_ler_chg_dep.cvg_eff_end_cd;
460 l_cvg_end_rl := l_ler_chg_dep.cvg_eff_end_rl;
461 if l_cvg_end_cd is null and l_cvg_end_rl is null then
462 l_look_again := true;
463 end if;
464 close c_ler_chg_dep;
465 else
466 close c_ler_chg_dep;
467 l_look_again := true;
468 end if;
469 if (l_look_again) then
470 if l_level ='PL' then
471 open c_plan(epe.pl_id);
472 fetch c_plan into l_plan;
473 if c_plan%found then
474 l_cvg_end_cd := l_plan.dpnt_cvg_end_dt_cd;
475 l_cvg_end_rl := l_plan.dpnt_cvg_end_dt_rl;
476 close c_plan;
477 else
478 close c_plan;
479 if l_level ='PTIP' then
480 open c_ptip(epe.ptip_id);
481 fetch c_ptip into l_ptip;
482 if c_ptip%found then
483 l_cvg_end_cd := l_ptip.dpnt_cvg_end_dt_cd;
484 l_cvg_end_rl := l_ptip.dpnt_cvg_end_dt_rl;
485 close c_ptip;
486 else
487 close c_ptip;
488 if l_level ='PGM' then
489 l_cvg_end_cd := l_pgm.dpnt_cvg_end_dt_cd;
490 l_cvg_end_rl := l_pgm.dpnt_cvg_end_dt_rl;
491 end if;
492 end if;
493 end if; -- ptip
494 end if;
495 end if; -- plan
496 end if; --dependent change of life event
497 -- End determine coverage end date
498
499 if l_cvg_end_cd is not null then
500 ben_determine_date.main
501 (P_DATE_CD => l_cvg_end_cd,
502 P_BUSINESS_GROUP_ID => l_business_group_id,
503 P_PERSON_ID => p_person_id,
504 P_PGM_ID => p_pgm_id,
505 P_PL_ID => epe.pl_id,
506 P_OIPL_ID => epe.oipl_id,
507 P_PER_IN_LER_ID => p_per_in_ler_id,
508 P_ELIG_PER_ELCTBL_CHC_ID => null,
509 P_FORMULA_ID => l_cvg_end_rl,
510 P_EFFECTIVE_DATE => p_effective_date,
511 P_LF_EVT_OCRD_DT => l_per_in_ler.lf_evt_ocrd_dt,
512 P_RETURNED_DATE => l_cvg_end_dt,
513 P_PARAM1 => 'CON_PERSON_ID',
514 P_PARAM1_VALUE => to_char(p_contact_person_id));
515 end if;
516
517 -- Get the enrollment result row.
518 --
519 if (epe.oipl_id is null and epe.pl_id is not null) then
520 --
521 open c_plan_enrolment_info(epe.pl_id);
522 fetch c_plan_enrolment_info into l_prtt_enrt_rslt_id;
523 if c_plan_enrolment_info%notfound then
524 null;
525 end if;
526 close c_plan_enrolment_info;
527 elsif (epe.oipl_id is not null) then
528 --
529 open c_oipl_enrolment_info(epe.oipl_id);
530 fetch c_oipl_enrolment_info into l_prtt_enrt_rslt_id;
531 if c_oipl_enrolment_info%notfound then
532 null;
533 end if;
534 close c_oipl_enrolment_info;
535 --
536 end if;
537
538 -- Get Dependent Coverage Start Date
539 --
540 if l_prtt_enrt_rslt_id is not null then
541 open c_pdp(l_prtt_enrt_rslt_id);
542 fetch c_pdp into l_pdp;
543 if c_pdp%found then
544 l_pdp_rec_found := true;
545 l_elig_cvrd_dpnt_id := l_pdp.elig_cvrd_dpnt_id;
546 l_dpnt_cvg_strt_dt := l_pdp.cvg_strt_dt;
547 else
548 l_pdp_rec_found := false;
549 l_elig_cvrd_dpnt_id := null;
550 l_dpnt_cvg_strt_dt := null;
551 end if;
552 close c_pdp;
553
554 /*if l_cvg_end_dt < l_pdp.cvg_strt_dt then
555 l_cvg_end_dt := p_effective_date;
556 end if;*/
557 --
558 -- Update the elig cvrd dependent record
559 --
560 if l_pdp_rec_found then
561 --
562 -- Check datetrack mode.
563 --
564 dt_api.find_dt_upd_modes
565 (p_effective_date => nvl(l_per_in_ler.lf_evt_ocrd_dt,
566 p_effective_date),
567 p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
568 p_base_key_column => 'elig_cvrd_dpnt_id',
569 p_base_key_value => l_elig_cvrd_dpnt_id,
570 p_correction => l_correction,
571 p_update => l_update,
572 p_update_override => l_update_override,
573 p_update_change_insert => l_update_change_insert);
574 --
575 if l_update_override then
576 --
577 l_datetrack_mode := hr_api.g_update_override;
578 --
579 elsif l_update then
580 --
581 l_datetrack_mode := hr_api.g_update;
582 --
583 else
584 --
585 l_datetrack_mode := hr_api.g_correction;
586 --
587 end if;
588
589 if l_cvg_end_dt is null then
590 --
591 l_cvg_end_dt := hr_api.g_eot;
592 --
593 end if;
594 --
595 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
596 (p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
597 ,p_effective_start_date => l_pdp_effective_start_date
598 ,p_effective_end_date => l_pdp_effective_end_date
599 ,p_per_in_ler_id => p_per_in_ler_id
600 ,p_cvg_thru_dt => l_cvg_end_dt
601 ,p_object_version_number => l_pdp.object_version_number
602 ,p_effective_date => nvl(l_per_in_ler.lf_evt_ocrd_dt,
603 p_effective_date)
604 ,p_datetrack_mode => l_datetrack_mode
605 ,p_program_application_id => fnd_global.prog_appl_id
606 ,p_program_id => fnd_global.conc_program_id
607 ,p_request_id => fnd_global.conc_request_id
608 ,p_program_update_date => sysdate
609 ,p_business_group_id => l_business_group_id
610 ,p_multi_row_actn => FALSE
611 );
612 end if;
613 end if; -- l_prtt_enrt_rslt_id not null
614
615 if l_elig_dpnt.dpnt_inelig_flag = 'N' then
616 --
617 ben_elig_dpnt_api.update_elig_dpnt(
618 p_elig_dpnt_id => l_elig_dpnt.elig_dpnt_id
619 ,p_object_version_number => l_elig_dpnt.object_version_number
620 ,p_effective_date => p_effective_date
621 ,p_elig_thru_dt => l_egd_elig_thru_dt --l_cvg_end_dt -- 5100008
622 ,p_dpnt_inelig_flag => 'Y'
623 ,p_inelg_rsn_cd => l_inelig_rsn_cd
624 );
625 --
626 end if;
627
628 end if;
629 end if; --notfound
630 close c_elig_dpnt;
631 --
632 end loop;
633 --
634 hr_utility.set_location ('Leaving '|| l_proc,10);
635
636 exception
637 when others then
638 fnd_msg_pub.initialize;
639 fnd_msg_pub.add;
640 --
641 END main;
642 --
643 -- -----------------------------------------------------------------------------
644 -- |-----------------------< fmly_mmbr_cd_exist >----------------------------|
645 -- -----------------------------------------------------------------------------
646 --
647 -- This procedure is called from selfservice to determine if family mmbr cd exists
648 -- at any level i.e pgm,plip,ptip,pl or oipl level.Also it checks if per_cvrd_cd
649 -- exists at pl or oipl level.If fmly_mmbr_cd or fmly_mmbr_rl or per_cvrd_cd exists
650 -- this procedure returns Y.
651 --
652 procedure fmly_mmbr_cd_exist
653 (p_business_group_id in number
654 ,p_effective_date in date
655 ,p_fmly_mmbr_exist out NOCOPY varchar2
656 )
657 is
658 --
659 l_proc varchar2(100):= g_package||'fmly_mmbr_cd_exist';
660 --
661 cursor c_pgm is
662 select pgm.pgm_id,
663 pgm.vrfy_fmly_mmbr_cd,
664 pgm.vrfy_fmly_mmbr_rl
665 from ben_pgm_f pgm
666 where pgm.business_group_id = p_business_group_id
667 and pgm.pgm_stat_cd = 'A'
668 and p_effective_date
669 between pgm.effective_start_date and pgm.effective_end_date;
670 --
671 cursor c_plip(l_pgm_id number) is
672 select plip.plip_id,
673 plip.pl_id,
674 plip.vrfy_fmly_mmbr_cd,
675 plip.vrfy_fmly_mmbr_rl
676 from ben_plip_f plip
677 where plip.business_group_id = p_business_group_id
678 and plip.plip_stat_cd = 'A'
679 and plip.pgm_id = l_pgm_id
680 and p_effective_date
681 between plip.effective_start_date and plip.effective_end_date;
682 --
683 cursor c_ptip(l_pgm_id number) is
684 select ptip.ptip_id,
685 ptip.vrfy_fmly_mmbr_cd,
686 ptip.vrfy_fmly_mmbr_rl
687 from ben_ptip_f ptip
688 where ptip.business_group_id = p_business_group_id
689 and ptip.ptip_stat_cd = 'A'
690 and ptip.pgm_id = l_pgm_id
691 and p_effective_date
692 between ptip.effective_start_date and ptip.effective_end_date;
693 --
694 cursor c_pl(l_pl_id number) is
695 select pl.pl_id,
696 pl.vrfy_fmly_mmbr_cd,
697 pl.vrfy_fmly_mmbr_rl,
698 pl.per_cvrd_cd
699 from ben_pl_f pl
700 where pl.business_group_id = p_business_group_id
701 and pl.pl_stat_cd = 'A'
702 and pl.pl_id = l_pl_id
703 and p_effective_date
704 between pl.effective_start_date and pl.effective_end_date;
705 --
706 cursor c_oipl(l_pl_id number) is
707 select oipl.oipl_id,
708 oipl.vrfy_fmly_mmbr_cd,
709 oipl.vrfy_fmly_mmbr_rl,
710 oipl.per_cvrd_cd
711 from ben_oipl_f oipl
712 where oipl.business_group_id = p_business_group_id
713 and oipl.oipl_stat_cd = 'A'
714 and oipl.pl_id = l_pl_id
715 and p_effective_date
716 between oipl.effective_start_date and oipl.effective_end_date;
717 --
718 cursor c_elig_to_prte_pgm(l_pgm_id number) is
719 select etpr.elig_to_prte_rsn_id,
720 etpr.vrfy_fmly_mmbr_cd,
721 etpr.vrfy_fmly_mmbr_rl
722 from ben_elig_to_prte_rsn_f etpr
723 where etpr.business_group_id = p_business_group_id
724 and etpr.pgm_id = l_pgm_id
725 and p_effective_date
726 between etpr.effective_start_date and etpr.effective_end_date;
727 --
728 cursor c_elig_to_prte_plip(l_plip_id number) is
729 select etpr.elig_to_prte_rsn_id,
730 etpr.vrfy_fmly_mmbr_cd,
731 etpr.vrfy_fmly_mmbr_rl
732 from ben_elig_to_prte_rsn_f etpr
733 where etpr.business_group_id = p_business_group_id
734 and etpr.plip_id = l_plip_id
735 and p_effective_date
736 between etpr.effective_start_date and etpr.effective_end_date;
737 --
738 cursor c_elig_to_prte_pl(l_pl_id number) is
739 select etpr.elig_to_prte_rsn_id,
740 etpr.vrfy_fmly_mmbr_cd,
741 etpr.vrfy_fmly_mmbr_rl
742 from ben_elig_to_prte_rsn_f etpr
743 where etpr.business_group_id = p_business_group_id
744 and etpr.pl_id = l_pl_id
745 and p_effective_date
746 between etpr.effective_start_date and etpr.effective_end_date;
747 --
748 cursor c_elig_to_prte_ptip(l_ptip_id number) is
749 select etpr.elig_to_prte_rsn_id,
750 etpr.vrfy_fmly_mmbr_cd,
751 etpr.vrfy_fmly_mmbr_rl
752 from ben_elig_to_prte_rsn_f etpr
753 where etpr.business_group_id = p_business_group_id
754 and etpr.ptip_id = l_ptip_id
755 and p_effective_date
756 between etpr.effective_start_date and etpr.effective_end_date;
757 --
758 cursor c_elig_to_prte_oipl(l_oipl_id number) is
759 select etpr.elig_to_prte_rsn_id,
760 etpr.vrfy_fmly_mmbr_cd,
761 etpr.vrfy_fmly_mmbr_rl
762 from ben_elig_to_prte_rsn_f etpr
763 where etpr.business_group_id = p_business_group_id
764 and etpr.oipl_id = l_oipl_id
765 and p_effective_date
766 between etpr.effective_start_date and etpr.effective_end_date;
767 --
768 l_elig_to_prte_rsn_id ben_elig_to_prte_rsn_f.elig_to_prte_rsn_id%TYPE;
769 l_vrfy_fmly_mmbr_cd ben_pgm_f.vrfy_fmly_mmbr_cd%TYPE;
770 l_vrfy_fmly_mmbr_rl ben_pgm_f.vrfy_fmly_mmbr_rl%TYPE;
771 l_per_cvrd_cd ben_pl_f.per_cvrd_cd%TYPE;
772 l_found boolean := false;
773 --
774 begin
775 --
776 hr_utility.set_location('Entering: '||l_proc,10);
777 --
778 for l_pgm_rec in c_pgm loop
779 --
780 if l_pgm_rec.vrfy_fmly_mmbr_cd is not null or --level 1
781 l_pgm_rec.vrfy_fmly_mmbr_rl is not null then
782 l_found := true;
783 exit;
784 --
785 else --level 1
786 open c_elig_to_prte_pgm(l_pgm_rec.pgm_id);
787 fetch c_elig_to_prte_pgm into l_elig_to_prte_rsn_id,l_vrfy_fmly_mmbr_cd,l_vrfy_fmly_mmbr_rl;
788 --
789 if (c_elig_to_prte_pgm%found) and --level 2
790 (l_vrfy_fmly_mmbr_cd is not null or
791 l_vrfy_fmly_mmbr_rl is not null) then
792 l_found := true;
793 close c_elig_to_prte_pgm;
794 exit;
795 else --level 2
796 close c_elig_to_prte_pgm;
797 for l_plip_rec in c_plip(l_pgm_rec.pgm_id) loop
798 --
799 if l_plip_rec.vrfy_fmly_mmbr_cd is not null or --level 3
800 l_plip_rec.vrfy_fmly_mmbr_rl is not null then
801 l_found := true;
802 exit;
803 else --level 3
804 open c_elig_to_prte_plip(l_plip_rec.plip_id);
805 fetch c_elig_to_prte_plip into l_elig_to_prte_rsn_id,l_vrfy_fmly_mmbr_cd,l_vrfy_fmly_mmbr_rl;
806 --
807 if (c_elig_to_prte_plip%found) and --level 4
808 (l_vrfy_fmly_mmbr_cd is not null or
809 l_vrfy_fmly_mmbr_rl is not null) then
810 l_found := true;
811 close c_elig_to_prte_plip;
812 exit;
813 else --level 4
814 close c_elig_to_prte_plip;
815 for l_pl_rec in c_pl(l_plip_rec.pl_id) loop
816 --
817 if l_pl_rec.vrfy_fmly_mmbr_cd is not null or --level 5
818 l_pl_rec.vrfy_fmly_mmbr_rl is not null or
819 l_pl_rec.per_cvrd_cd is not null then
820 l_found := true;
821 exit;
822 else --level 5
823 open c_elig_to_prte_pl(l_plip_rec.pl_id);
824 fetch c_elig_to_prte_pl into l_elig_to_prte_rsn_id,l_vrfy_fmly_mmbr_cd,l_vrfy_fmly_mmbr_rl;
825 if (c_elig_to_prte_pl%found) and --level 6
826 (l_vrfy_fmly_mmbr_cd is not null or
827 l_vrfy_fmly_mmbr_rl is not null) then
828 l_found := true;
829 close c_elig_to_prte_pl;
830 exit;
831 else --level 6
832 close c_elig_to_prte_pl;
833 for l_oipl_rec in c_oipl(l_plip_rec.pl_id) loop
834 --
835 if l_oipl_rec.vrfy_fmly_mmbr_cd is not null or --level 7
836 l_oipl_rec.vrfy_fmly_mmbr_rl is not null or
837 l_oipl_rec.per_cvrd_cd is not null then
838 l_found := true;
839 exit;
840 else --level 7
841 open c_elig_to_prte_oipl(l_oipl_rec.oipl_id);
842 fetch c_elig_to_prte_oipl into l_elig_to_prte_rsn_id,l_vrfy_fmly_mmbr_cd,l_vrfy_fmly_mmbr_rl;
843 --
844 if (c_elig_to_prte_oipl%found) and --level 8
845 (l_vrfy_fmly_mmbr_cd is not null or
846 l_vrfy_fmly_mmbr_rl is not null) then
847 l_found := true;
848 close c_elig_to_prte_oipl;
849 exit;
850 else --level 8
851 close c_elig_to_prte_oipl;
852 end if; --level 8
853 end if; --level 7
854 end loop; --oipl end loop
855 if (l_found) then
856 exit;
857 end if; --level 6
858 end if; --level 6
859 end if; --level 5
860 end loop; --pl end loop
861 if (l_found) then
862 exit;
863 end if; --level 6
864 end if; --level 4
865 end if; --level 3
866 end loop; --plip end loop
867 if (l_found) then
868 exit;
869 end if;
870
871 for l_ptip_rec in c_ptip(l_pgm_rec.pgm_id) loop
872 --
873 if l_ptip_rec.vrfy_fmly_mmbr_cd is not null or --level 9
874 l_ptip_rec.vrfy_fmly_mmbr_rl is not null then
875 l_found := true;
876 exit;
877 else --level 9
878 open c_elig_to_prte_ptip(l_ptip_rec.ptip_id);
879 fetch c_elig_to_prte_ptip into l_elig_to_prte_rsn_id,l_vrfy_fmly_mmbr_cd,l_vrfy_fmly_mmbr_rl;
880 --
881 if (c_elig_to_prte_ptip%found) and --level 10
882 (l_vrfy_fmly_mmbr_cd is not null or
883 l_vrfy_fmly_mmbr_rl is not null) then
884 l_found := true;
885 close c_elig_to_prte_ptip;
886 exit;
887 else --level 10
888 close c_elig_to_prte_ptip;
889 end if; --level 10
890 end if; --level 9
891 end loop; --ptip end loop
892 if (l_found) then
893 exit;
894 end if;
895 end if; --level 2
896 end if; --level 1
897 end loop; --pgm end loop
898 hr_utility.set_location('Leaving: '||l_proc,20);
899 --
900 if (l_found) then
901 p_fmly_mmbr_exist := 'Y';
902 else
903 p_fmly_mmbr_exist := 'N';
904 end if;
905 --
906 exception
907 when others then
908 p_fmly_mmbr_exist :=NULL;
909 fnd_msg_pub.initialize;
910 fnd_msg_pub.add;
911 --
912 end fmly_mmbr_cd_exist;
913 --
914 -- -----------------------------------------------------------------------------
915 -- |-------------------------<create_contact_w>--------------------------------|
916 -- -----------------------------------------------------------------------------
917 --
918 -- This is a SS wraper to HR_CONTACT_REL_API.create_contact.
919 -- Returns 'S' on sucess and 'E' on error
920 --
921 procedure create_contact_w
922 (p_validate in varchar2 default 'N'
923 ,p_start_date in date
924 ,p_business_group_id in number
925 ,p_person_id in number
926 ,p_contact_person_id in number default null
927 ,p_contact_type in varchar2
928 ,p_ctr_comments in varchar2 default null
929 ,p_primary_contact_flag in varchar2 default 'N'
930 ,p_date_start in date default null
931 ,p_start_life_reason_id in number default null
932 ,p_date_end in date default null
933 ,p_end_life_reason_id in number default null
934 ,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default 'N'
935 ,p_personal_flag in varchar2 default 'N'
936 ,p_sequence_number in number default null
937 ,p_cont_attribute_category in varchar2 default null
938 ,p_cont_attribute1 in varchar2 default null
939 ,p_cont_attribute2 in varchar2 default null
940 ,p_cont_attribute3 in varchar2 default null
941 ,p_cont_attribute4 in varchar2 default null
942 ,p_cont_attribute5 in varchar2 default null
943 ,p_cont_attribute6 in varchar2 default null
944 ,p_cont_attribute7 in varchar2 default null
945 ,p_cont_attribute8 in varchar2 default null
946 ,p_cont_attribute9 in varchar2 default null
947 ,p_cont_attribute10 in varchar2 default null
948 ,p_cont_attribute11 in varchar2 default null
949 ,p_cont_attribute12 in varchar2 default null
950 ,p_cont_attribute13 in varchar2 default null
951 ,p_cont_attribute14 in varchar2 default null
952 ,p_cont_attribute15 in varchar2 default null
953 ,p_cont_attribute16 in varchar2 default null
954 ,p_cont_attribute17 in varchar2 default null
955 ,p_cont_attribute18 in varchar2 default null
956 ,p_cont_attribute19 in varchar2 default null
957 ,p_cont_attribute20 in varchar2 default null
958 ,p_cont_information_category in varchar2 default null
959 ,p_cont_information1 in varchar2 default null
960 ,p_cont_information2 in varchar2 default null
961 ,p_cont_information3 in varchar2 default null
962 ,p_cont_information4 in varchar2 default null
963 ,p_cont_information5 in varchar2 default null
964 ,p_cont_information6 in varchar2 default null
965 ,p_cont_information7 in varchar2 default null
966 ,p_cont_information8 in varchar2 default null
967 ,p_cont_information9 in varchar2 default null
968 ,p_cont_information10 in varchar2 default null
969 ,p_cont_information11 in varchar2 default null
970 ,p_cont_information12 in varchar2 default null
971 ,p_cont_information13 in varchar2 default null
972 ,p_cont_information14 in varchar2 default null
973 ,p_cont_information15 in varchar2 default null
974 ,p_cont_information16 in varchar2 default null
975 ,p_cont_information17 in varchar2 default null
976 ,p_cont_information18 in varchar2 default null
977 ,p_cont_information19 in varchar2 default null
978 ,p_cont_information20 in varchar2 default null
979 ,p_third_party_pay_flag in varchar2 default 'N'
980 ,p_bondholder_flag in varchar2 default 'N'
981 ,p_dependent_flag in varchar2 default 'N'
982 ,p_beneficiary_flag in varchar2 default 'N'
983 ,p_last_name in varchar2 default null
984 ,p_sex in varchar2 default null
985 ,p_person_type_id in number default null
986 ,p_per_comments in varchar2 default null
987 ,p_date_of_birth in date default null
988 ,p_email_address in varchar2 default null
989 ,p_first_name in varchar2 default null
990 ,p_known_as in varchar2 default null
991 ,p_marital_status in varchar2 default null
992 ,p_middle_names in varchar2 default null
993 ,p_nationality in varchar2 default null
994 ,p_national_identifier in varchar2 default null
995 ,p_previous_last_name in varchar2 default null
996 ,p_registered_disabled_flag in varchar2 default null
997 ,p_title in varchar2 default null
998 ,p_work_telephone in varchar2 default null
999 ,p_attribute_category in varchar2 default null
1000 ,p_attribute1 in varchar2 default null
1001 ,p_attribute2 in varchar2 default null
1002 ,p_attribute3 in varchar2 default null
1003 ,p_attribute4 in varchar2 default null
1004 ,p_attribute5 in varchar2 default null
1005 ,p_attribute6 in varchar2 default null
1006 ,p_attribute7 in varchar2 default null
1007 ,p_attribute8 in varchar2 default null
1008 ,p_attribute9 in varchar2 default null
1009 ,p_attribute10 in varchar2 default null
1010 ,p_attribute11 in varchar2 default null
1011 ,p_attribute12 in varchar2 default null
1012 ,p_attribute13 in varchar2 default null
1013 ,p_attribute14 in varchar2 default null
1014 ,p_attribute15 in varchar2 default null
1015 ,p_attribute16 in varchar2 default null
1016 ,p_attribute17 in varchar2 default null
1017 ,p_attribute18 in varchar2 default null
1018 ,p_attribute19 in varchar2 default null
1019 ,p_attribute20 in varchar2 default null
1020 ,p_attribute21 in varchar2 default null
1021 ,p_attribute22 in varchar2 default null
1022 ,p_attribute23 in varchar2 default null
1023 ,p_attribute24 in varchar2 default null
1024 ,p_attribute25 in varchar2 default null
1025 ,p_attribute26 in varchar2 default null
1026 ,p_attribute27 in varchar2 default null
1027 ,p_attribute28 in varchar2 default null
1028 ,p_attribute29 in varchar2 default null
1029 ,p_attribute30 in varchar2 default null
1030 ,p_per_information_category in varchar2 default null
1031 ,p_per_information1 in varchar2 default null
1032 ,p_per_information2 in varchar2 default null
1033 ,p_per_information3 in varchar2 default null
1034 ,p_per_information4 in varchar2 default null
1035 ,p_per_information5 in varchar2 default null
1036 ,p_per_information6 in varchar2 default null
1037 ,p_per_information7 in varchar2 default null
1038 ,p_per_information8 in varchar2 default null
1039 ,p_per_information9 in varchar2 default null
1040 ,p_per_information10 in varchar2 default null
1041 ,p_per_information11 in varchar2 default null
1042 ,p_per_information12 in varchar2 default null
1043 ,p_per_information13 in varchar2 default null
1044 ,p_per_information14 in varchar2 default null
1045 ,p_per_information15 in varchar2 default null
1046 ,p_per_information16 in varchar2 default null
1047 ,p_per_information17 in varchar2 default null
1048 ,p_per_information18 in varchar2 default null
1049 ,p_per_information19 in varchar2 default null
1050 ,p_per_information20 in varchar2 default null
1051 ,p_per_information21 in varchar2 default null
1052 ,p_per_information22 in varchar2 default null
1053 ,p_per_information23 in varchar2 default null
1054 ,p_per_information24 in varchar2 default null
1055 ,p_per_information25 in varchar2 default null
1056 ,p_per_information26 in varchar2 default null
1057 ,p_per_information27 in varchar2 default null
1058 ,p_per_information28 in varchar2 default null
1059 ,p_per_information29 in varchar2 default null
1060 ,p_per_information30 in varchar2 default null
1061 ,p_correspondence_language in varchar2 default null
1062 ,p_honors in varchar2 default null
1063 ,p_pre_name_adjunct in varchar2 default null
1064 ,p_suffix in varchar2 default null
1065 ,p_create_mirror_flag in varchar2 default 'N'
1066 ,p_mirror_type in varchar2 default null
1067
1068 ,p_contact_relationship_id out NOCOPY number
1069 ,p_ctr_object_version_number out NOCOPY number
1070 ,p_per_person_id out NOCOPY number
1071 ,p_per_object_version_number out NOCOPY number
1072 ,p_per_effective_start_date out NOCOPY date
1073 ,p_per_effective_end_date out NOCOPY date
1074 ,p_full_name out NOCOPY varchar2
1075 ,p_per_comment_id out NOCOPY number
1076 ,p_name_combination_warning out NOCOPY varchar2
1077 ,p_orig_hire_warning out NOCOPY varchar2
1078 ,p_return_status out NOCOPY varchar2
1079 ) IS
1080
1081 l_validate boolean :=false;
1082 l_name_combination_warning boolean :=false;
1083 l_orig_hire_warning boolean :=false;
1084
1085 -- bug 3042223
1086 -- CESD - Contact Person Effective Start Date
1087 -- PESD - Participant's Effective Start Date
1088 -- RSD - Relationship Start Date
1089 -- EFD - Effective Date (Session Date)
1090 --
1091 -- CESD = Max (PESD, Min(RSD, EFD))
1092 --
1093 cursor c_per_efsd is
1094 select per.effective_start_date
1095 from per_all_people_f per
1096 where per.person_id = p_person_id
1097 order by effective_start_date asc ;
1098 --
1099 -- By default, effective date is initally passed on from SS into
1100 -- p_start_date param, and it is overridden here.
1101 -- RSD is passed on as p_date_start param. it is either user-entered or
1102 -- defaulted to effective date.
1103 --
1104 l_start_date date := p_start_date ;
1105 l_per_effective_start_date date := null ;
1106 BEGIN
1107 IF(p_validate = 'Y')
1108 THEN
1109 l_validate :=true;
1110 END IF;
1111
1112 -- bug fix 3042223
1113 open c_per_efsd;
1114 fetch c_per_efsd into l_per_effective_start_date ;
1115 close c_per_efsd;
1116 --
1117 if p_date_start < l_start_date then -- RSD < Eff Date
1118 l_start_date := p_date_start ;
1119 end if;
1120 --
1121 if l_per_effective_start_date > l_start_date then -- PESD > Min (RSD, Eff Dt)
1122 l_start_date := l_per_effective_start_date ;
1123 end if;
1124 --
1125 -- now pass l_start_date instead of p_start_date to HR's api - HR_CONTACT_REL_API.
1126 -- end fix 3042223
1127
1128 HR_CONTACT_REL_API.create_contact
1129 ( p_validate => l_validate
1130 , p_start_date => l_start_date -- p_start_date
1131 , p_business_group_id => p_business_group_id
1132 , p_person_id => p_person_id
1133 , p_contact_person_id => p_contact_person_id
1134 , p_contact_type => p_contact_type
1135 , p_ctr_comments => p_ctr_comments
1136 , p_primary_contact_flag => p_primary_contact_flag
1137 , p_date_start => p_date_start
1138 , p_start_life_reason_id => p_start_life_reason_id
1139 , p_date_end => p_date_end
1140 , p_end_life_reason_id => p_end_life_reason_id
1141 , p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
1142 , p_personal_flag => p_personal_flag
1143 , p_sequence_number => p_sequence_number
1144 , p_cont_attribute_category => p_cont_attribute_category
1145 , p_cont_attribute1 => p_cont_attribute1
1146 , p_cont_attribute2 => p_cont_attribute2
1147 , p_cont_attribute3 => p_cont_attribute3
1148 , p_cont_attribute4 => p_cont_attribute4
1149 , p_cont_attribute5 => p_cont_attribute5
1150 , p_cont_attribute6 => p_cont_attribute6
1151 , p_cont_attribute7 => p_cont_attribute7
1152 , p_cont_attribute8 => p_cont_attribute8
1153 , p_cont_attribute9 => p_cont_attribute9
1154 , p_cont_attribute10 => p_cont_attribute10
1155 , p_cont_attribute11 => p_cont_attribute11
1156 , p_cont_attribute12 => p_cont_attribute12
1157 , p_cont_attribute13 => p_cont_attribute13
1158 , p_cont_attribute14 => p_cont_attribute14
1159 , p_cont_attribute15 => p_cont_attribute15
1160 , p_cont_attribute16 => p_cont_attribute16
1161 , p_cont_attribute17 => p_cont_attribute17
1162 , p_cont_attribute18 => p_cont_attribute18
1163 , p_cont_attribute19 => p_cont_attribute19
1164 , p_cont_attribute20 => p_cont_attribute20
1165 , p_cont_information_category => p_cont_information_category
1166 , p_cont_information1 => p_cont_information1
1167 , p_cont_information2 => p_cont_information2
1168 , p_cont_information3 => p_cont_information3
1169 , p_cont_information4 => p_cont_information4
1170 , p_cont_information5 => p_cont_information5
1171 , p_cont_information6 => p_cont_information6
1172 , p_cont_information7 => p_cont_information7
1173 , p_cont_information8 => p_cont_information8
1174 , p_cont_information9 => p_cont_information9
1175 , p_cont_information10 => p_cont_information10
1176 , p_cont_information11 => p_cont_information11
1177 , p_cont_information12 => p_cont_information12
1178 , p_cont_information13 => p_cont_information13
1179 , p_cont_information14 => p_cont_information14
1180 , p_cont_information15 => p_cont_information15
1181 , p_cont_information16 => p_cont_information16
1182 , p_cont_information17 => p_cont_information17
1183 , p_cont_information18 => p_cont_information18
1184 , p_cont_information19 => p_cont_information19
1185 , p_cont_information20 => p_cont_information20
1186 , p_third_party_pay_flag => p_third_party_pay_flag
1187 , p_bondholder_flag => p_bondholder_flag
1188 , p_dependent_flag => p_dependent_flag
1189 , p_beneficiary_flag => p_beneficiary_flag
1190 , p_last_name => p_last_name
1191 , p_sex => p_sex
1192 , p_person_type_id => p_person_type_id
1193 , p_per_comments => p_per_comments
1194 , p_date_of_birth => p_date_of_birth
1195 , p_email_address => p_email_address
1196 , p_first_name => p_first_name
1197 , p_known_as => p_known_as
1198 , p_marital_status => p_marital_status
1199 , p_middle_names => p_middle_names
1200 , p_nationality => p_nationality
1201 , p_national_identifier => p_national_identifier
1202 , p_previous_last_name => p_previous_last_name
1203 , p_registered_disabled_flag => p_registered_disabled_flag
1204 , p_title => p_title
1205 , p_work_telephone => p_work_telephone
1206 , p_attribute_category => p_attribute_category
1207 , p_attribute1 => p_attribute1
1208 , p_attribute2 => p_attribute2
1209 , p_attribute3 => p_attribute3
1210 , p_attribute4 => p_attribute4
1211 , p_attribute5 => p_attribute5
1212 , p_attribute6 => p_attribute6
1213 , p_attribute7 => p_attribute7
1214 , p_attribute8 => p_attribute8
1215 , p_attribute9 => p_attribute9
1216 , p_attribute10 => p_attribute10
1217 , p_attribute11 => p_attribute11
1218 , p_attribute12 => p_attribute12
1219 , p_attribute13 => p_attribute13
1220 , p_attribute14 => p_attribute14
1221 , p_attribute15 => p_attribute15
1222 , p_attribute16 => p_attribute16
1223 , p_attribute17 => p_attribute17
1224 , p_attribute18 => p_attribute18
1225 , p_attribute19 => p_attribute19
1226 , p_attribute20 => p_attribute20
1227 , p_attribute21 => p_attribute21
1228 , p_attribute22 => p_attribute22
1229 , p_attribute23 => p_attribute23
1230 , p_attribute24 => p_attribute24
1231 , p_attribute25 => p_attribute25
1232 , p_attribute26 => p_attribute26
1233 , p_attribute27 => p_attribute27
1234 , p_attribute28 => p_attribute28
1235 , p_attribute29 => p_attribute29
1236 , p_attribute30 => p_attribute30
1237 , p_per_information_category => p_per_information_category
1238 , p_per_information1 => p_per_information1
1239 , p_per_information2 => p_per_information2
1240 , p_per_information3 => p_per_information3
1241 , p_per_information4 => p_per_information4
1242 , p_per_information5 => p_per_information5
1243 , p_per_information6 => p_per_information6
1244 , p_per_information7 => p_per_information7
1245 , p_per_information8 => p_per_information8
1246 , p_per_information9 => p_per_information9
1247 , p_per_information10 => p_per_information10
1248 , p_per_information11 => p_per_information11
1249 , p_per_information12 => p_per_information12
1250 , p_per_information13 => p_per_information13
1251 , p_per_information14 => p_per_information14
1252 , p_per_information15 => p_per_information15
1253 , p_per_information16 => p_per_information16
1254 , p_per_information17 => p_per_information17
1255 , p_per_information18 => p_per_information18
1256 , p_per_information19 => p_per_information19
1257 , p_per_information20 => p_per_information20
1258 , p_per_information21 => p_per_information21
1259 , p_per_information22 => p_per_information22
1260 , p_per_information23 => p_per_information23
1261 , p_per_information24 => p_per_information24
1262 , p_per_information25 => p_per_information25
1263 , p_per_information26 => p_per_information26
1264 , p_per_information27 => p_per_information27
1265 , p_per_information28 => p_per_information28
1266 , p_per_information29 => p_per_information29
1267 , p_per_information30 => p_per_information30
1268 , p_correspondence_language => p_correspondence_language
1269 , p_honors => p_honors
1270 , p_pre_name_adjunct => p_pre_name_adjunct
1271 , p_suffix => p_suffix
1272 , p_create_mirror_flag => p_create_mirror_flag
1273 , p_mirror_type => p_mirror_type
1274
1275 , p_contact_relationship_id => p_contact_relationship_id
1276 , p_ctr_object_version_number => p_ctr_object_version_number
1277 , p_per_person_id => p_per_person_id
1278 , p_per_object_version_number => p_per_object_version_number
1279 , p_per_effective_start_date => p_per_effective_start_date
1280 , p_per_effective_end_date => p_per_effective_end_date
1281 , p_full_name => p_full_name
1282 , p_per_comment_id => p_per_comment_id
1283 , p_name_combination_warning => l_name_combination_warning
1284 , p_orig_hire_warning => l_orig_hire_warning
1285 ) ;
1286
1287 p_return_status :='S';
1288 EXCEPTION
1289 --
1290 when others then
1291 p_return_status :='E';
1292 p_contact_relationship_id := NULL;
1293 --p_ctr_object_version_number := NULL;
1294 p_per_person_id := NULL;
1295 --p_per_object_version_number := NULL;
1296 p_per_effective_start_date := NULL;
1297 p_per_effective_end_date := NULL;
1298 p_full_name := NULL;
1299 p_per_comment_id := NULL;
1300 p_name_combination_warning := NULL;
1301 p_orig_hire_warning := NULL;
1302
1303 fnd_msg_pub.initialize;
1304 fnd_msg_pub.add;
1305 --
1306 END create_contact_w;
1307 --
1308 --
1309 -- -----------------------------------------------------------------------------
1310 -- |-------------------------<update_contact_w>--------------------------------|
1311 -- -----------------------------------------------------------------------------
1312 --
1313 -- This is a SS wraper to HR_CONTACT_REL_API.update_contact_relationship.
1314 -- Returns 'S' on sucess and 'E' on error
1315 --
1316 procedure update_contact_w
1317 (p_validate in varchar2 default 'N'
1318 ,p_effective_date in date
1319 ,p_contact_relationship_id in number
1320 ,p_contact_type in varchar2 default hr_api.g_varchar2
1321 ,p_comments in long default hr_api.g_varchar2
1322 ,p_primary_contact_flag in varchar2 default hr_api.g_varchar2
1323 ,p_third_party_pay_flag in varchar2 default hr_api.g_varchar2
1324 ,p_bondholder_flag in varchar2 default hr_api.g_varchar2
1325 ,p_date_start in date default hr_api.g_date
1326 ,p_start_life_reason_id in number default hr_api.g_number
1327 ,p_date_end in date default hr_api.g_date
1328 ,p_end_life_reason_id in number default hr_api.g_number
1329 ,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default hr_api.g_varchar2
1330 ,p_personal_flag in varchar2 default hr_api.g_varchar2
1331 ,p_sequence_number in number default hr_api.g_number
1332 ,p_dependent_flag in varchar2 default hr_api.g_varchar2
1333 ,p_beneficiary_flag in varchar2 default hr_api.g_varchar2
1334 ,p_cont_attribute_category in varchar2 default hr_api.g_varchar2
1335 ,p_cont_attribute1 in varchar2 default hr_api.g_varchar2
1336 ,p_cont_attribute2 in varchar2 default hr_api.g_varchar2
1337 ,p_cont_attribute3 in varchar2 default hr_api.g_varchar2
1338 ,p_cont_attribute4 in varchar2 default hr_api.g_varchar2
1339 ,p_cont_attribute5 in varchar2 default hr_api.g_varchar2
1340 ,p_cont_attribute6 in varchar2 default hr_api.g_varchar2
1341 ,p_cont_attribute7 in varchar2 default hr_api.g_varchar2
1342 ,p_cont_attribute8 in varchar2 default hr_api.g_varchar2
1343 ,p_cont_attribute9 in varchar2 default hr_api.g_varchar2
1344 ,p_cont_attribute10 in varchar2 default hr_api.g_varchar2
1345 ,p_cont_attribute11 in varchar2 default hr_api.g_varchar2
1346 ,p_cont_attribute12 in varchar2 default hr_api.g_varchar2
1347 ,p_cont_attribute13 in varchar2 default hr_api.g_varchar2
1348 ,p_cont_attribute14 in varchar2 default hr_api.g_varchar2
1349 ,p_cont_attribute15 in varchar2 default hr_api.g_varchar2
1350 ,p_cont_attribute16 in varchar2 default hr_api.g_varchar2
1351 ,p_cont_attribute17 in varchar2 default hr_api.g_varchar2
1352 ,p_cont_attribute18 in varchar2 default hr_api.g_varchar2
1353 ,p_cont_attribute19 in varchar2 default hr_api.g_varchar2
1354 ,p_cont_attribute20 in varchar2 default hr_api.g_varchar2
1355 ,p_cont_information_category in varchar2 default hr_api.g_varchar2
1356 ,p_cont_information1 in varchar2 default hr_api.g_varchar2
1357 ,p_cont_information2 in varchar2 default hr_api.g_varchar2
1358 ,p_cont_information3 in varchar2 default hr_api.g_varchar2
1359 ,p_cont_information4 in varchar2 default hr_api.g_varchar2
1360 ,p_cont_information5 in varchar2 default hr_api.g_varchar2
1361 ,p_cont_information6 in varchar2 default hr_api.g_varchar2
1362 ,p_cont_information7 in varchar2 default hr_api.g_varchar2
1363 ,p_cont_information8 in varchar2 default hr_api.g_varchar2
1364 ,p_cont_information9 in varchar2 default hr_api.g_varchar2
1365 ,p_cont_information10 in varchar2 default hr_api.g_varchar2
1366 ,p_cont_information11 in varchar2 default hr_api.g_varchar2
1367 ,p_cont_information12 in varchar2 default hr_api.g_varchar2
1368 ,p_cont_information13 in varchar2 default hr_api.g_varchar2
1369 ,p_cont_information14 in varchar2 default hr_api.g_varchar2
1370 ,p_cont_information15 in varchar2 default hr_api.g_varchar2
1371 ,p_cont_information16 in varchar2 default hr_api.g_varchar2
1372 ,p_cont_information17 in varchar2 default hr_api.g_varchar2
1373 ,p_cont_information18 in varchar2 default hr_api.g_varchar2
1374 ,p_cont_information19 in varchar2 default hr_api.g_varchar2
1375 ,p_cont_information20 in varchar2 default hr_api.g_varchar2
1376 ,p_object_version_number in out nocopy number
1377 ,p_return_status out nocopy varchar2
1378 ) IS
1379
1380 l_validate boolean :=false;
1381 BEGIN
1382 IF (p_validate = 'Y') THEN
1383 l_validate :=true;
1384 END IF;
1385
1386 HR_CONTACT_REL_API.update_contact_relationship
1387 ( p_validate => l_validate
1388 ,p_effective_date => p_effective_date
1389 ,p_contact_relationship_id => p_contact_relationship_id
1390 ,p_contact_type => p_contact_type
1391 ,p_comments => p_comments
1392 ,p_primary_contact_flag => p_primary_contact_flag
1393 ,p_third_party_pay_flag => p_third_party_pay_flag
1394 ,p_bondholder_flag => p_bondholder_flag
1395 ,p_date_start => p_date_start
1396 ,p_start_life_reason_id => p_start_life_reason_id
1397 ,p_date_end => p_date_end
1398 ,p_end_life_reason_id => p_end_life_reason_id
1399 ,p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
1400 ,p_personal_flag => p_personal_flag
1401 ,p_sequence_number => p_sequence_number
1402 ,p_dependent_flag => p_dependent_flag
1403 ,p_beneficiary_flag => p_beneficiary_flag
1404 ,p_cont_attribute_category => p_cont_attribute_category
1405 ,p_cont_attribute1 => p_cont_attribute1
1406 ,p_cont_attribute2 => p_cont_attribute2
1407 ,p_cont_attribute3 => p_cont_attribute3
1408 ,p_cont_attribute4 => p_cont_attribute4
1409 ,p_cont_attribute5 => p_cont_attribute5
1410 ,p_cont_attribute6 => p_cont_attribute6
1411 ,p_cont_attribute7 => p_cont_attribute7
1412 ,p_cont_attribute8 => p_cont_attribute8
1413 ,p_cont_attribute9 => p_cont_attribute9
1414 ,p_cont_attribute10 => p_cont_attribute10
1415 ,p_cont_attribute11 => p_cont_attribute11
1416 ,p_cont_attribute12 => p_cont_attribute12
1417 ,p_cont_attribute13 => p_cont_attribute13
1418 ,p_cont_attribute14 => p_cont_attribute14
1419 ,p_cont_attribute15 => p_cont_attribute15
1420 ,p_cont_attribute16 => p_cont_attribute16
1421 ,p_cont_attribute17 => p_cont_attribute17
1422 ,p_cont_attribute18 => p_cont_attribute18
1423 ,p_cont_attribute19 => p_cont_attribute19
1424 ,p_cont_attribute20 => p_cont_attribute20
1425 ,p_cont_information_category => p_cont_information_category
1426 ,p_cont_information1 => p_cont_information1
1427 ,p_cont_information2 => p_cont_information2
1428 ,p_cont_information3 => p_cont_information3
1429 ,p_cont_information4 => p_cont_information4
1430 ,p_cont_information5 => p_cont_information5
1431 ,p_cont_information6 => p_cont_information6
1432 ,p_cont_information7 => p_cont_information7
1433 ,p_cont_information8 => p_cont_information8
1434 ,p_cont_information9 => p_cont_information9
1435 ,p_cont_information10 => p_cont_information10
1436 ,p_cont_information11 => p_cont_information11
1437 ,p_cont_information12 => p_cont_information12
1438 ,p_cont_information13 => p_cont_information13
1439 ,p_cont_information14 => p_cont_information14
1440 ,p_cont_information15 => p_cont_information15
1441 ,p_cont_information16 => p_cont_information16
1442 ,p_cont_information17 => p_cont_information17
1443 ,p_cont_information18 => p_cont_information18
1444 ,p_cont_information19 => p_cont_information19
1445 ,p_cont_information20 => p_cont_information20
1446 ,p_object_version_number => p_object_version_number
1447 ) ;
1448
1449 p_return_status :='S';
1450 EXCEPTION
1451 --
1452 when others then
1453 p_return_status :='E';
1454
1455 fnd_msg_pub.initialize;
1456 fnd_msg_pub.add;
1457 --
1458 END update_contact_w;
1459 --
1460 -- -----------------------------------------------------------------------------
1461 -- |--------------------------<update_person_w>--------------------------------|
1462 -- -----------------------------------------------------------------------------
1463 --
1464 -- This is a SS wraper to HR_PERSON_API.update_person.
1465 -- Returns 'S' on sucess and 'E' on error
1466 --
1467 procedure update_person_w
1468 (p_validate in varchar2 default 'N'
1469 ,p_effective_date in date
1470 ,p_datetrack_update_mode in varchar2
1471 ,p_person_id in number
1472 ,p_object_version_number in out NOCOPY number
1473 ,p_person_type_id in number default hr_api.g_number
1474 ,p_last_name in varchar2 default hr_api.g_varchar2
1475 ,p_applicant_number in varchar2 default hr_api.g_varchar2
1476 ,p_comments in varchar2 default hr_api.g_varchar2
1477 ,p_date_employee_data_verified in date default hr_api.g_date
1478 ,p_date_of_birth in date default hr_api.g_date
1479 ,p_email_address in varchar2 default hr_api.g_varchar2
1480 ,p_employee_number in out NOCOPY varchar2
1481 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
1482 ,p_first_name in varchar2 default hr_api.g_varchar2
1483 ,p_known_as in varchar2 default hr_api.g_varchar2
1484 ,p_marital_status in varchar2 default hr_api.g_varchar2
1485 ,p_middle_names in varchar2 default hr_api.g_varchar2
1486 ,p_nationality in varchar2 default hr_api.g_varchar2
1487 ,p_national_identifier in varchar2 default hr_api.g_varchar2
1488 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
1489 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
1490 ,p_sex in varchar2 default hr_api.g_varchar2
1491 ,p_title in varchar2 default hr_api.g_varchar2
1492 ,p_vendor_id in number default hr_api.g_number
1493 ,p_work_telephone in varchar2 default hr_api.g_varchar2
1494 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1495 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1496 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1497 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1498 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1499 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1500 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1501 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1502 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1503 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1504 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1505 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1506 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1507 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1508 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1509 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1510 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1511 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1512 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1513 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1514 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1515 ,p_attribute21 in varchar2 default hr_api.g_varchar2
1516 ,p_attribute22 in varchar2 default hr_api.g_varchar2
1517 ,p_attribute23 in varchar2 default hr_api.g_varchar2
1518 ,p_attribute24 in varchar2 default hr_api.g_varchar2
1519 ,p_attribute25 in varchar2 default hr_api.g_varchar2
1520 ,p_attribute26 in varchar2 default hr_api.g_varchar2
1521 ,p_attribute27 in varchar2 default hr_api.g_varchar2
1522 ,p_attribute28 in varchar2 default hr_api.g_varchar2
1523 ,p_attribute29 in varchar2 default hr_api.g_varchar2
1524 ,p_attribute30 in varchar2 default hr_api.g_varchar2
1525 ,p_per_information_category in varchar2 default hr_api.g_varchar2
1526 ,p_per_information1 in varchar2 default hr_api.g_varchar2
1527 ,p_per_information2 in varchar2 default hr_api.g_varchar2
1528 ,p_per_information3 in varchar2 default hr_api.g_varchar2
1529 ,p_per_information4 in varchar2 default hr_api.g_varchar2
1530 ,p_per_information5 in varchar2 default hr_api.g_varchar2
1531 ,p_per_information6 in varchar2 default hr_api.g_varchar2
1532 ,p_per_information7 in varchar2 default hr_api.g_varchar2
1533 ,p_per_information8 in varchar2 default hr_api.g_varchar2
1534 ,p_per_information9 in varchar2 default hr_api.g_varchar2
1535 ,p_per_information10 in varchar2 default hr_api.g_varchar2
1536 ,p_per_information11 in varchar2 default hr_api.g_varchar2
1537 ,p_per_information12 in varchar2 default hr_api.g_varchar2
1538 ,p_per_information13 in varchar2 default hr_api.g_varchar2
1539 ,p_per_information14 in varchar2 default hr_api.g_varchar2
1540 ,p_per_information15 in varchar2 default hr_api.g_varchar2
1541 ,p_per_information16 in varchar2 default hr_api.g_varchar2
1542 ,p_per_information17 in varchar2 default hr_api.g_varchar2
1543 ,p_per_information18 in varchar2 default hr_api.g_varchar2
1544 ,p_per_information19 in varchar2 default hr_api.g_varchar2
1545 ,p_per_information20 in varchar2 default hr_api.g_varchar2
1546 ,p_per_information21 in varchar2 default hr_api.g_varchar2
1547 ,p_per_information22 in varchar2 default hr_api.g_varchar2
1548 ,p_per_information23 in varchar2 default hr_api.g_varchar2
1549 ,p_per_information24 in varchar2 default hr_api.g_varchar2
1550 ,p_per_information25 in varchar2 default hr_api.g_varchar2
1551 ,p_per_information26 in varchar2 default hr_api.g_varchar2
1552 ,p_per_information27 in varchar2 default hr_api.g_varchar2
1553 ,p_per_information28 in varchar2 default hr_api.g_varchar2
1554 ,p_per_information29 in varchar2 default hr_api.g_varchar2
1555 ,p_per_information30 in varchar2 default hr_api.g_varchar2
1556 ,p_date_of_death in date default hr_api.g_date
1557 ,p_background_check_status in varchar2 default hr_api.g_varchar2
1558 ,p_background_date_check in date default hr_api.g_date
1559 ,p_blood_type in varchar2 default hr_api.g_varchar2
1560 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
1561 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
1562 ,p_fte_capacity in number default hr_api.g_number
1563 ,p_hold_applicant_date_until in date default hr_api.g_date
1564 ,p_honors in varchar2 default hr_api.g_varchar2
1565 ,p_internal_location in varchar2 default hr_api.g_varchar2
1566 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
1567 ,p_last_medical_test_date in date default hr_api.g_date
1568 ,p_mailstop in varchar2 default hr_api.g_varchar2
1569 ,p_office_number in varchar2 default hr_api.g_varchar2
1570 ,p_on_military_service in varchar2 default hr_api.g_varchar2
1571 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
1572 ,p_projected_start_date in date default hr_api.g_date
1573 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
1574 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
1575 ,p_resume_exists in varchar2 default hr_api.g_varchar2
1576 ,p_resume_last_updated in date default hr_api.g_date
1577 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
1578 ,p_student_status in varchar2 default hr_api.g_varchar2
1579 ,p_work_schedule in varchar2 default hr_api.g_varchar2
1580 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
1581 ,p_suffix in varchar2 default hr_api.g_varchar2
1582 ,p_benefit_group_id in number default hr_api.g_number
1583 ,p_receipt_of_death_cert_date in date default hr_api.g_date
1584 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
1585 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
1586 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
1587 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
1588 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
1589 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
1590 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
1591 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
1592 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
1593 ,p_dpdnt_adoption_date in date default hr_api.g_date
1594 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
1595 ,p_original_date_of_hire in date default hr_api.g_date
1596 ,p_adjusted_svc_date in date default hr_api.g_date
1597 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
1598 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
1599 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
1600 ,p_global_person_id in varchar2 default hr_api.g_varchar2
1601 ,p_party_id in number default hr_api.g_number
1602 ,p_npw_number in varchar2 default hr_api.g_varchar2
1603 ,p_effective_start_date out NOCOPY date
1604 ,p_effective_end_date out NOCOPY date
1605 ,p_full_name out NOCOPY varchar2
1606 ,p_comment_id out NOCOPY number
1607 ,p_name_combination_warning out NOCOPY varchar2
1608 ,p_assign_payroll_warning out NOCOPY varchar2
1609 ,p_orig_hire_warning out NOCOPY varchar2
1610 ,p_return_status out NOCOPY varchar2
1611 ) is
1612
1613 l_validate boolean :=false;
1614 l_name_combination_warning boolean :=false;
1615 l_orig_hire_warning boolean :=false;
1616 l_assign_payroll_warning boolean :=false;
1617
1618 BEGIN
1619 IF(p_validate = 'Y')
1620 THEN
1621 l_validate :=true;
1622 END IF;
1623
1624 hr_person_api.update_person
1625 (p_validate =>l_validate
1626 ,p_effective_date =>p_effective_date
1627 ,p_datetrack_update_mode =>p_datetrack_update_mode
1628 ,p_person_id =>p_person_id
1629 ,p_object_version_number =>p_object_version_number
1630 ,p_person_type_id =>p_person_type_id
1631 ,p_last_name =>p_last_name
1632 ,p_applicant_number =>p_applicant_number
1633 ,p_comments =>p_comments
1634 ,p_date_employee_data_verified =>p_date_employee_data_verified
1635 ,p_date_of_birth =>p_date_of_birth
1636 ,p_email_address =>p_email_address
1637 ,p_employee_number =>p_employee_number
1638 ,p_expense_check_send_to_addres =>p_expense_check_send_to_addres
1639 ,p_first_name =>p_first_name
1640 ,p_known_as =>p_known_as
1641 ,p_marital_status =>p_marital_status
1642 ,p_middle_names =>p_middle_names
1643 ,p_nationality =>p_nationality
1644 ,p_national_identifier =>p_national_identifier
1645 ,p_previous_last_name =>p_previous_last_name
1646 ,p_registered_disabled_flag =>p_registered_disabled_flag
1647 ,p_sex =>p_sex
1648 ,p_title =>p_title
1649 ,p_vendor_id =>p_vendor_id
1650 ,p_work_telephone =>p_work_telephone
1651 ,p_attribute_category =>p_attribute_category
1652 ,p_attribute1 =>p_attribute1
1653 ,p_attribute2 =>p_attribute2
1654 ,p_attribute3 =>p_attribute3
1655 ,p_attribute4 =>p_attribute4
1656 ,p_attribute5 =>p_attribute5
1657 ,p_attribute6 =>p_attribute6
1658 ,p_attribute7 =>p_attribute7
1659 ,p_attribute8 =>p_attribute8
1660 ,p_attribute9 =>p_attribute9
1661 ,p_attribute10 =>p_attribute10
1662 ,p_attribute11 =>p_attribute11
1663 ,p_attribute12 =>p_attribute12
1664 ,p_attribute13 =>p_attribute13
1665 ,p_attribute14 =>p_attribute14
1666 ,p_attribute15 =>p_attribute15
1667 ,p_attribute16 =>p_attribute16
1668 ,p_attribute17 =>p_attribute17
1669 ,p_attribute18 =>p_attribute18
1670 ,p_attribute19 =>p_attribute19
1671 ,p_attribute20 =>p_attribute20
1672 ,p_attribute21 =>p_attribute21
1673 ,p_attribute22 =>p_attribute22
1674 ,p_attribute23 =>p_attribute23
1675 ,p_attribute24 =>p_attribute24
1676 ,p_attribute25 =>p_attribute25
1677 ,p_attribute26 =>p_attribute26
1678 ,p_attribute27 =>p_attribute27
1679 ,p_attribute28 =>p_attribute28
1680 ,p_attribute29 =>p_attribute29
1681 ,p_attribute30 =>p_attribute30
1682 ,p_per_information_category =>p_per_information_category
1683 ,p_per_information1 =>p_per_information1
1684 ,p_per_information2 =>p_per_information2
1685 ,p_per_information3 =>p_per_information3
1686 ,p_per_information4 =>p_per_information4
1687 ,p_per_information5 =>p_per_information5
1688 ,p_per_information6 =>p_per_information6
1689 ,p_per_information7 =>p_per_information7
1690 ,p_per_information8 =>p_per_information8
1691 ,p_per_information9 =>p_per_information9
1692 ,p_per_information10 =>p_per_information10
1693 ,p_per_information11 =>p_per_information11
1694 ,p_per_information12 =>p_per_information12
1695 ,p_per_information13 =>p_per_information13
1696 ,p_per_information14 =>p_per_information14
1697 ,p_per_information15 =>p_per_information15
1698 ,p_per_information16 =>p_per_information16
1699 ,p_per_information17 =>p_per_information17
1700 ,p_per_information18 =>p_per_information18
1701 ,p_per_information19 =>p_per_information19
1702 ,p_per_information20 =>p_per_information20
1703 ,p_per_information21 =>p_per_information21
1704 ,p_per_information22 =>p_per_information22
1705 ,p_per_information23 =>p_per_information23
1706 ,p_per_information24 =>p_per_information24
1707 ,p_per_information25 =>p_per_information25
1708 ,p_per_information26 =>p_per_information26
1709 ,p_per_information27 =>p_per_information27
1710 ,p_per_information28 =>p_per_information28
1711 ,p_per_information29 =>p_per_information29
1712 ,p_per_information30 =>p_per_information30
1713 ,p_date_of_death =>p_date_of_death
1714 ,p_background_check_status =>p_background_check_status
1715 ,p_background_date_check =>p_background_date_check
1716 ,p_blood_type =>p_blood_type
1717 ,p_correspondence_language =>p_correspondence_language
1718 ,p_fast_path_employee =>p_fast_path_employee
1719 ,p_fte_capacity =>p_fte_capacity
1720 ,p_hold_applicant_date_until =>p_hold_applicant_date_until
1721 ,p_honors =>p_honors
1722 ,p_internal_location =>p_internal_location
1723 ,p_last_medical_test_by =>p_last_medical_test_by
1724 ,p_last_medical_test_date =>p_last_medical_test_date
1725 ,p_mailstop =>p_mailstop
1726 ,p_office_number =>p_office_number
1727 ,p_on_military_service =>p_on_military_service
1728 ,p_pre_name_adjunct =>p_pre_name_adjunct
1729 ,p_projected_start_date =>p_projected_start_date
1730 ,p_rehire_authorizor =>p_rehire_authorizor
1731 ,p_rehire_recommendation =>p_rehire_recommendation
1732 ,p_resume_exists =>p_resume_exists
1733 ,p_resume_last_updated =>p_resume_last_updated
1734 ,p_second_passport_exists =>p_second_passport_exists
1735 ,p_student_status =>p_student_status
1736 ,p_work_schedule =>p_work_schedule
1737 ,p_rehire_reason =>p_rehire_reason
1738 ,p_suffix =>p_suffix
1739 ,p_benefit_group_id =>p_benefit_group_id
1740 ,p_receipt_of_death_cert_date =>p_receipt_of_death_cert_date
1741 ,p_coord_ben_med_pln_no =>p_coord_ben_med_pln_no
1742 ,p_coord_ben_no_cvg_flag =>p_coord_ben_no_cvg_flag
1743 ,p_coord_ben_med_ext_er =>p_coord_ben_med_ext_er
1744 ,p_coord_ben_med_pl_name =>p_coord_ben_med_pl_name
1745 ,p_coord_ben_med_insr_crr_name =>p_coord_ben_med_insr_crr_name
1746 ,p_coord_ben_med_insr_crr_ident =>p_coord_ben_med_insr_crr_ident
1747 ,p_coord_ben_med_cvg_strt_dt =>p_coord_ben_med_cvg_strt_dt
1748 ,p_coord_ben_med_cvg_end_dt =>p_coord_ben_med_cvg_end_dt
1749 ,p_uses_tobacco_flag =>p_uses_tobacco_flag
1750 ,p_dpdnt_adoption_date =>p_dpdnt_adoption_date
1751 ,p_dpdnt_vlntry_svce_flag =>p_dpdnt_vlntry_svce_flag
1752 ,p_original_date_of_hire =>p_original_date_of_hire
1753 ,p_adjusted_svc_date =>p_adjusted_svc_date
1754 ,p_town_of_birth =>p_town_of_birth
1755 ,p_region_of_birth =>p_region_of_birth
1756 ,p_country_of_birth =>p_country_of_birth
1757 ,p_global_person_id =>p_global_person_id
1758 ,p_party_id =>p_party_id
1759 ,p_npw_number =>p_npw_number
1760 ,p_effective_start_date =>p_effective_start_date
1761 ,p_effective_end_date =>p_effective_end_date
1762 ,p_full_name =>p_full_name
1763 ,p_comment_id =>p_comment_id
1764 ,p_name_combination_warning =>l_name_combination_warning
1765 ,p_assign_payroll_warning =>l_assign_payroll_warning
1766 ,p_orig_hire_warning =>l_orig_hire_warning
1767 );
1768
1769 p_return_status :='S';
1770 EXCEPTION
1771 --
1772 when others then
1773 p_return_status := 'E';
1774 p_employee_number := NULL;
1775 p_effective_start_date := NULL;
1776 p_effective_end_date := NULL;
1777 p_full_name := NULL;
1778 p_comment_id := NULL;
1779 p_name_combination_warning := NULL;
1780 p_assign_payroll_warning := NULL;
1781 p_orig_hire_warning := NULL;
1782
1783 fnd_msg_pub.initialize;
1784 fnd_msg_pub.add;
1785 --
1786 END update_person_w;
1787 end ben_determine_dpnt_elig_ss;