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