1 PACKAGE BODY ben_det_wait_perd_cmpltn AS
2 /*$Header: benwtprc.pkb 120.1 2006/06/30 13:37:50 swjain ship $*/
3 /*
4 --------------------------------------------------------------------------------
5 Name
6 Determine Waiting Period Completed Date
7 Purpose
8 This package is used compute when the waiting period end date.
9 --------------------------------------------------------------------------------
10 History
11 -------
12 Version Date Author Comment
13 -------+----------+----------+------------------------------------------------
14 115.0 14-May-99 bbulusu Created.
15 115.1 14-Jun-99 Tmathers Fixed P1 nls to_date errors.
16 115.2 23-JUN-98 G PERRY Performance fixes.
17 115.3 20-JUL-99 Gperry genutils -> benutils package rename.
18 115.4 10-AUG-99 Gperry Removed reference to ben_manage_life_events
19 old cache routine. Added fnd_date.canonical
20 _to_date for formula calls.
21 115.5 26-AUG-99 Gperry Added call to benefits assignment cache when
22 employee assignment can not be found.
23 115.6 18-JAN-00 pbodla Fixed bug 4146(WWBUG 1120687)
24 p_business_group_id added to benutils.formula
25 call.
26 115.7 26-JAN-00 pbodla Fixed bug 4401; plan is fetched only if p_pl_id
27 is not null
28 115.8 17-Feb-00 lmcdonal Bugs 1178692, 1178674. Benefit's assignment
29 data is in aei_information fields, not
30 aei_attribute fields.
31 115.9 18-Feb-00 lmcdonal Bug 1179550: get ptip waiting period data.
32 115.10 22-Feb-00 lmcdonal Bug 1187476: Inherit wtg perd codes.
33 115.11 03-Mar-00 mhoyes - Added parameter p_comp_obj_tree_row.
34 - Assigned env values to locals.
35 - Phased out nocopy ben_env_object comp object references.
36 115.12 04-Apr-00 mmogel - Added tokens to message calls so that the
37 messages are more meaningful to the user
38 115.13 06-Apr-00 jcarpent - Changed get_ben_asg_dt to look at benefits ass
39 first then emp assignment. 1178674/4299
40 115.14 20-Jun-99 mhoyes - Added current ben_prtn_elig_f,ben_elig_to_prte_rsn_f
41 and ben_pl_f row parameters.
42 - Derived row variables from parameters rather than
43 cache calls.
44 115.15 13-Jul-00 mhoyes - Removed context parameters.
45 115.16 16-Aug-01 pbodla - Bug 1838055 : Code AED added in procedure
46 get_wtg_perd_end_date.
47 115.17 27-Aug-01 pbodla - bug:1949361 jurisdiction code is
48 derived inside benutils.formula.
49 115.18 26-sep-01 tjesumic wait_perd_Strt_dt added
50 115.19 23-Dec-02 rpgupta - Nocopy changes
51 115.20 21-JUL-03 glingapp - Bug 3047147 While checking for max waiting period
52 current code had a check, which restricted the max
53 waiting period check to plan level. Now even at
54 option in plan level max waiting period is checked for
55 and used to determine the actual waiting period.
56 115.21 22-SEP-04 ikasire Bug 3895120 fixes
57 Several Changes in get_per_svc_dt,get_ben_asg_dt and
58 get_wait_end_date functions. else clause for the
59 waiting period codes was never working right.
60 115.22 20-OCT-04 swjain Bug No 3954620 Added code in get_wait_end_date to handle
61 the null case in p_wait_perd_dt_to_use_cd 'EASDNASD'
62 115.23 25-OCT-04 swjain Bug No 3954620 Few modifications in the added code
63 115.24 29-Jun-06 swjain Bug 5331889 Added person_id param in call to
64 benutils.formula in procedure get_wait_end_date
65 --------------------------------------------------------------------------------
66 */
67 --
68 g_package varchar2(80) := 'ben_det_wait_perd_cmpltn';
69 --
70 -- -----------------------------------------------------------------------------
71 -- |-------------------------< get_per_svc_dt >--------------------------------|
72 -- -----------------------------------------------------------------------------
73 --
74 function get_per_svc_dt(p_person_id in number,
75 p_date_code in varchar2) return date is
76 --
77 l_ovrid_svc_dt date;
78 l_adj_svc_dt date;
79 l_orig_hire_date date;
80 l_hire_date date;
81 l_return_date date;
82 l_look_further boolean := TRUE;
83 l_proc varchar2(80) := g_package||'.get_per_svc_dt';
84 l_pps_rec per_periods_of_service%rowtype;
85 l_per_rec per_all_people_f%rowtype;
86 --
87 begin
88 --
89 hr_utility.set_location('Entering : ' || l_proc, 10);
90 --
91 ben_person_object.get_object(p_person_id => p_person_id,
92 p_rec => l_per_rec);
93 --
94 ben_person_object.get_object(p_person_id => p_person_id,
95 p_rec => l_pps_rec);
96 --
97 l_ovrid_svc_dt := ben_derive_part_and_rate_facts.
98 g_cache_details.
99 ovrid_svc_dt;
100 --
101 l_adj_svc_dt := l_pps_rec.adjusted_svc_date;
102 --
103 l_orig_hire_date := l_per_rec.original_date_of_hire;
104 --
105 l_hire_date := l_pps_rec.date_start;
106 --
107 if p_date_code <> 'OHD' then
108 --
109 -- If the date being searched for is not the original hire date, look in
110 -- the following order: OSD -> ASD -> DOH
111 --
112 if p_date_code = 'OSD' then
113 --
114 -- Overriden Service Date.
115 --
116 if l_ovrid_svc_dt is not null then
117 --
118 hr_utility.set_location('Using OSD', 10);
119 --
120 l_return_date := l_ovrid_svc_dt;
121 --
122 -- Set flag so that the search stops.
123 --
124 -- l_look_further := FALSE;
125 --
126 end if;
127 --
128 end if;
129 --
130 if p_date_code = 'ASD' then -- or
131 -- l_look_further = TRUE then
132 --
133 -- Adjusted service date.
134 --
135 if l_adj_svc_dt is not null then
136 --
137 hr_utility.set_location('Using ASD', 10);
138 --
139 l_return_date := l_adj_svc_dt;
140 --
141 -- Set flag so that the search stops.
142 --
143 l_look_further := FALSE;
144 --
145 end if;
146 --
147 end if;
148 --
149 if p_date_code = 'DOH' then -- or
150 -- l_look_further = TRUE then
151 --
152 -- Date of Hire.
153 --
154 if l_hire_date is not null then
155 --
156 hr_utility.set_location('Using DOH', 10);
157 --
158 l_return_date := l_hire_date;
159 --
160 end if;
161 --
162 end if;
163 --
164 else
165 --
166 -- Date code is Original Hire Date.
167 --
168 hr_utility.set_location('Using OHD', 10);
169 --
170 l_return_date := l_orig_hire_date;
171 --
172 end if;
173 --
174 /*
175 if l_return_date is null then
176 --
177 hr_utility.set_location('ERROR. Unable to calculate service date : ' ||
178 p_date_code, 10);
179 --
180 fnd_message.set_name('BEN', 'BEN_92197_CANT_CALC_SVC_DATE');
181 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
182 fnd_message.set_token('DATE_CODE',p_date_code);
183 fnd_message.set_token('PROC',l_proc);
184 raise ben_manage_life_events.g_record_error;
185 --
186 end if;
187 */
188 --
189 hr_utility.set_location('Leaving : ' || l_proc, 10);
190 --
191 return l_return_date;
192 --
193 end get_per_svc_dt;
194 --
195 -- -----------------------------------------------------------------------------
196 -- |--------------------------< get_ben_asg_dt >-------------------------------|
197 -- -----------------------------------------------------------------------------
198 --
199 function get_ben_asg_dt
200 (p_person_id in number,
201 p_effective_date in date,
202 p_business_group_id in number,
203 p_date_code in varchar2) return date is
204 --
205 l_ovrid_svc_dt date;
206 l_adj_svc_dt date;
207 l_orig_hire_dt date;
208 l_return_date date;
209 l_look_further boolean := TRUE;
210 l_ass_rec per_all_assignments_f%rowtype;
211 L_aei_rec per_assignment_extra_info%rowtype;
212 l_proc varchar2(80) := g_package||'.get_ben_asg_dt';
213
214 begin
215 hr_utility.set_location('Entering : ' || l_proc, 10);
216 --
217 -- Get the ovrid_svc_dt from the cache.
218 --
219 l_ovrid_svc_dt := ben_derive_part_and_rate_facts.
220 g_cache_details.
221 ovrid_svc_dt;
222 --
223 -- Get the other service dates from the per_assignment_extra_info table.
224 --
225 ben_person_object.get_benass_object(p_person_id => p_person_id,
226 p_rec => l_ass_rec);
227 --
228 if l_ass_rec.assignment_id is null then
229 --
230 ben_person_object.get_object(p_person_id => p_person_id,
231 p_rec => l_ass_rec);
232 --
233 end if;
234 --
235 ben_person_object.get_object(p_assignment_id => l_ass_rec.assignment_id,
236 p_rec => l_aei_rec);
237 --
238 l_adj_svc_dt := fnd_date.canonical_to_date(l_aei_rec.aei_information2);
239 l_orig_hire_dt := fnd_date.canonical_to_date(l_aei_rec.aei_information3);
240 --
241 -- Get the appropriate date based osn the date code passed in.
242 --
243 if p_date_code = 'OSD' then
244 --
245 if l_ovrid_svc_dt is not null then
246 --
247 l_return_date := l_ovrid_svc_dt;
248 --
249 -- Set the flag so that the process does not search any more.
250 --
251 -- l_look_further := FALSE;
252 --
253 end if;
254 --
255 end if;
256 --
257 if p_date_code = 'ASD' then -- or l_look_further = TRUE then
258 --
259 if l_adj_svc_dt is not null then
260 --
261 l_return_date := l_adj_svc_dt;
262 --
263 -- Set the flag so that the process does not search any more.
264 --
265 -- l_look_further := FALSE;
266 --
267 end if;
268 --
269 end if;
270 --
271 if p_date_code = 'OHD' or
272 p_date_code = 'DOH' then -- or
273 -- l_look_further = TRUE then
274 --
275 if l_orig_hire_dt is not null then
276 --
277 l_return_date := l_orig_hire_dt;
278 --
279 end if;
280 --
281 end if;
282 --
283 /*
284 if l_return_date is null then
285 --
286 hr_utility.set_location('ERROR in ' || l_proc, 10);
287 --
288 fnd_message.set_name('BEN', 'BEN_92197_CANT_CALC_SVC_DATE');
289 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
290 fnd_message.set_token('DATE_CODE',p_date_code);
291 fnd_message.set_token('PROC',l_proc);
292 raise ben_manage_life_events.g_record_error;
293 --
294 end if;
295 */
296 --
297 hr_utility.set_location('Leaving : ' || l_proc, 10);
298 --
299 return l_return_date;
300 --
301 end get_ben_asg_dt;
302 --
303 -- -----------------------------------------------------------------------------
304 -- |-------------------------< get_wait_end_date >-----------------------------|
305 -- -----------------------------------------------------------------------------
306 --
307 function get_wait_end_date
308 (p_wait_perd_dt_to_use_cd in varchar2,
309 p_wait_perd_dt_to_use_rl in number,
310 p_wait_perd_rl in number,
311 p_wait_perd_uom in varchar2,
312 p_wait_perd_val in number,
313 p_lf_evt_ocrd_dt in date,
314 p_ntfn_dt in date,
315 p_person_id in number,
316 p_pgm_id in number,
317 p_pl_id in number,
318 p_oipl_id in number,
319 p_ler_id in number,
320 p_effective_date in date,
321 p_business_group_id in number,
322 p_wait_perd_strt_dt out nocopy date ) return date is
323 --
324 l_employee_flag boolean := FALSE;
325 l_wait_st_date date;
326 l_wait_end_date date;
327 l_outputs ff_exec.outputs_t;
328 l_wait_perd_uom varchar2(30);
329 l_wait_perd_val number(15);
330 l_oipl_rec ben_oipl_f%rowtype;
331 l_loc_rec hr_locations_all%rowtype;
332 l_pln_rec ben_pl_f%rowtype;
333 l_ass_rec per_all_assignments_f%rowtype;
334 --
335 l_proc varchar2(80) := g_package || '.get_wtg_perd_end_date';
336 l_typ_rec ben_person_object.g_cache_typ_table;
337 l_jurisdiction_code varchar2(30);
338 --
339 begin
340 --
341 hr_utility.set_location('Entering : ' || l_proc, 10);
342 --
343 -- First determine if the person is an employee or not.
344 --
345 ben_person_object.get_object(p_person_id => p_person_id,
346 p_rec => l_typ_rec);
347 --
348 if l_typ_rec(1).system_person_type = 'EMP' then
349 l_employee_flag := TRUE;
350 hr_utility.set_location('l_employee_flag ', 11);
351 else
352 l_employee_flag := FALSE;
353 hr_utility.set_location('NOT l_employee_flag', 11);
354 --for a in l_typ_rec.first..l_typ_rec.last loop
355 --hr_utility.set_location(to_char(a)||' '||l_typ_rec(a).system_person_type, 11);
356 --end loop;
357 end if;
358 --
359 -- Determine the date from which the waiting period should calculated from.
360 --
361 if p_wait_perd_dt_to_use_cd = 'RL' and
362 p_wait_perd_dt_to_use_rl is not null then
363 --
364 -- Use the rule to determine the waiting period date from.
365 --
366 if p_oipl_id is not null then
367 ben_comp_object.get_object(p_oipl_id => p_oipl_id,
368 p_rec => l_oipl_rec);
369 end if;
370 --
371 -- Initialize the fast formula to return l_date_from
372 --
373 if p_pl_id is not null then
374 ben_comp_object.get_object(p_pl_id => p_pl_id,
375 p_rec => l_pln_rec);
376 end if;
377
378 ben_person_object.get_object(p_person_id => p_person_id,
379 p_rec => l_ass_rec);
380 if l_ass_rec.assignment_id is null then
381 ben_person_object.get_benass_object(p_person_id => p_person_id,
382 p_rec => l_ass_rec);
383 end if;
384
385 if l_ass_rec.location_id is not null then
386 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
387 p_rec => l_loc_rec);
388 /*
389 if l_loc_rec.region_2 is not null then
390 l_jurisdiction_code :=
391 pay_mag_utils.lookup_jurisdiction_code
392 (p_state => l_loc_rec.region_2);
393 end if;
394 */
395 end if;
396 l_outputs := benutils.formula
397 (p_formula_id => p_wait_perd_dt_to_use_rl,
398 p_effective_date => p_effective_date,
399 p_assignment_id => l_ass_rec.assignment_id,
400 p_organization_id => l_ass_rec.organization_id,
401 p_pgm_id => p_pgm_id,
402 p_pl_id => p_pl_id,
403 p_pl_typ_id => l_pln_rec.pl_typ_id,
404 p_opt_id => l_oipl_rec.opt_id,
405 p_ler_id => p_ler_id,
406 p_business_group_id => p_business_group_id,
407 p_jurisdiction_code => l_jurisdiction_code,
408 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
409 p_param1_value => to_char(p_person_id));
410 --
411 -- Now we have executed the formula. We want to assign the output to
412 -- l_date_from
413 --
414 begin
415 l_wait_st_date := fnd_date.canonical_to_date
416 (l_outputs(l_outputs.first).value);
417 exception
418 when others then
419 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
420 fnd_message.set_token('RL',
421 'wait_perd_dt_to_use_rl :'||p_wait_perd_dt_to_use_rl);
422 fnd_message.set_token('PROC',l_proc);
423 fnd_message.raise_error;
424 end;
425
426 elsif p_wait_perd_dt_to_use_cd = 'EOSDNOSD' then
427 --
428 -- IF EMPLOYEE USE THE OVERRIDE SERVICE DATE IF NOT OVERRIDE SERVICE DATE
429 --
430 if l_employee_flag = TRUE then
431 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
432 p_date_code => 'OSD');
433 else
434 --
435 -- Person is not an employee. Find service dates from bnf asg.
436 --
437 l_wait_st_date := get_ben_asg_dt
438 (p_person_id => p_person_id,
439 p_effective_date => p_effective_date,
440 p_business_group_id => p_business_group_id,
441 p_date_code => 'OSD');
442 end if;
443
444 elsif p_wait_perd_dt_to_use_cd = 'EOSDNLED' then
445 --
446 -- IF EMPLOYEE USE THE OVERRIDE SERVICE DATE IF NOT LIFE EVENT DATE
447 --
448 if l_employee_flag = TRUE then
449 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
450 p_date_code => 'OSD');
451 else
452 -- l_wait_st_date := p_lf_evt_ocrd_dt;
453 l_wait_st_date := get_ben_asg_dt
454 (p_person_id => p_person_id,
455 p_effective_date => p_effective_date,
456 p_business_group_id => p_business_group_id,
457 p_date_code => 'OSD');
458 end if;
459 --
460 if l_wait_st_date IS NULL THEN
461 l_wait_st_date := p_lf_evt_ocrd_dt;
462 end if;
463 --
464 elsif p_wait_perd_dt_to_use_cd = 'EOSDNLLRD' then
465 --
466 -- IF EMPLOYEE USE THE OVERRIDE SERVICE DATE IF NOT LATER OF LIFE EVENT
467 -- DATE OR NOTIFIED DATE
468 --
469 if l_employee_flag = TRUE then
470 --
471 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
472 p_date_code => 'OSD');
473 else
474 --
475 -- Not employee use later or life event or notification date.
476 --
477 l_wait_st_date := get_ben_asg_dt
478 (p_person_id => p_person_id,
479 p_effective_date => p_effective_date,
480 p_business_group_id => p_business_group_id,
481 p_date_code => 'OSD');
482 --
483 end if;
484 if l_wait_st_date is null then
485 l_wait_st_date := greatest(p_lf_evt_ocrd_dt,nvl(p_ntfn_dt,hr_api.g_sot));
486 end if;
487 --
488 elsif p_wait_perd_dt_to_use_cd = 'EASDNASD' then
489 --
490 -- IF EMPLOYEE USE THE ADJUSTED SERVICE DATE IF NOT ADJUSTED SERVICE DATE
491 --
492 if l_employee_flag = TRUE then
493 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
494 p_date_code => 'ASD');
495 else
496 --
497 -- Person is not an employee. Find service dates from bnf asg.
498 --
499 l_wait_st_date := get_ben_asg_dt
500 (p_person_id => p_person_id,
501 p_effective_date => p_effective_date,
502 p_business_group_id => p_business_group_id,
503 p_date_code => 'ASD');
504 end if;
505 --
506 --Bug No: 3954620
507 --
508 if l_wait_st_date is null then
509 --
510 -- Use person's hire date
511 --
512 if l_employee_flag = TRUE then
513 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
514 p_date_code => 'DOH');
515 else
516 --
517 -- Person is not an employee. Find service dates from bnf asg.
518 --
519 l_wait_st_date := get_ben_asg_dt
520 (p_person_id => p_person_id,
521 p_effective_date => p_effective_date,
522 p_business_group_id => p_business_group_id,
523 p_date_code => 'DOH');
524 end if;
525 --
526 end if;
527 --
528 elsif p_wait_perd_dt_to_use_cd = 'EASDNLED' then
529 --
530 -- IF EMPLOYEE USE THE ADJUSTED SERVICE DATE IF NOT LIFE EVENT DATE
531 --
532 if l_employee_flag = TRUE then
533 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
534 p_date_code => 'ASD');
535 else
536 -- Person is not an employee. Find service dates from bnf asg.
537 --
538 l_wait_st_date := get_ben_asg_dt
539 (p_person_id => p_person_id,
540 p_effective_date => p_effective_date,
541 p_business_group_id => p_business_group_id,
542 p_date_code => 'ASD');
543 end if;
544 --
545 if l_wait_st_date is null then
546 l_wait_st_date := p_lf_evt_ocrd_dt;
547 end if;
548
549 elsif p_wait_perd_dt_to_use_cd = 'EASDNLRD' then
550 --
551 -- IF EMPLOYEE USE THE ADJUSTED SERVICE DATE IF NOT LATER OF LIFE EVENT
552 -- DATE OR NOTIFIED DATE
553 --
554 if l_employee_flag = TRUE then
555 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
556 p_date_code => 'ASD');
557 else
558 --
559 -- Not employee use later or life event or notification date.
560 --
561 l_wait_st_date := get_ben_asg_dt
562 (p_person_id => p_person_id,
563 p_effective_date => p_effective_date,
564 p_business_group_id => p_business_group_id,
565 p_date_code => 'ASD');
566 end if;
567 --
568 if l_wait_st_date is null then
569 --
570 l_wait_st_date := greatest(p_lf_evt_ocrd_dt,nvl(p_ntfn_dt,hr_api.g_sot));
571 end if;
572 --
573 elsif p_wait_perd_dt_to_use_cd = 'EDOHNDOH' then
574 --
575 -- IF EMPLOYEE USE THE DATE OF HIRE IF NOT DATE OF HIRE
576 --
577 if l_employee_flag = TRUE then
578 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
579 p_date_code => 'DOH');
580 else
581 --
582 -- Person is not an employee. Find service dates from bnf asg.
583 l_wait_st_date := get_ben_asg_dt
584 (p_person_id => p_person_id,
585 p_effective_date => p_effective_date,
586 p_business_group_id => p_business_group_id,
587 p_date_code => 'DOH');
588 end if;
589
590 elsif p_wait_perd_dt_to_use_cd = 'EDOHNLED' then
591 --
592 -- If employee use the Date Of Hire if not Life Event Date
593 --
594 if l_employee_flag = TRUE then
595 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
596 p_date_code => 'DOH');
597 else
598 -- Person is not an employee. Find service dates from bnf asg.
599 l_wait_st_date := get_ben_asg_dt
600 (p_person_id => p_person_id,
601 p_effective_date => p_effective_date,
602 p_business_group_id => p_business_group_id,
603 p_date_code => 'DOH');
604 end if;
605 --
606 if l_wait_st_date is null then
607 l_wait_st_date := p_lf_evt_ocrd_dt;
608 end if;
609
610 elsif p_wait_perd_dt_to_use_cd = 'EDOHNLRD' then
611 --
612 -- IF EMPLOYEE USE THE DATE OF HIRE IF NOT LATER OF LIFE EVENT DATE
613 -- OR NOTIFIED DATE
614 --
615 if l_employee_flag = TRUE then
616 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
617 p_date_code => 'DOH');
618 else
619 l_wait_st_date := get_ben_asg_dt
620 (p_person_id => p_person_id,
621 p_effective_date => p_effective_date,
622 p_business_group_id => p_business_group_id,
623 p_date_code => 'DOH');
624 end if;
625 --
626 if l_wait_st_date is null then
627 l_wait_st_date := greatest(p_lf_evt_ocrd_dt,nvl(p_ntfn_dt,hr_api.g_sot));
628 end if;
629
630 elsif p_wait_perd_dt_to_use_cd = 'EOHDNOHD' then
631 --
632 -- IF EMPLOYEE USE THE ORIGINAL HIRE DATE IF NOT ORIGINAL HIRE DATE
633 --
634 if l_employee_flag = TRUE then
635 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
636 p_date_code => 'OHD');
637 else
638 --
639 -- Person is not an employee. Find service dates from bnf asg.
640 --
641 l_wait_st_date := get_ben_asg_dt
642 (p_person_id => p_person_id,
643 p_effective_date => p_effective_date,
644 p_business_group_id => p_business_group_id,
645 p_date_code => 'DOH');
646 end if;
647
648 elsif p_wait_perd_dt_to_use_cd = 'EOHDNLED' then
649 --
650 -- IF EMPLOYEE USE THE ORIGINAL HIRE DATE IF NOT LIFE EVENT DATE
651 --
652 if l_employee_flag = TRUE then
653 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
654 p_date_code => 'OHD');
655 else
656 -- Person is not an employee. Find service dates from bnf asg.
657 --
658 l_wait_st_date := get_ben_asg_dt
659 (p_person_id => p_person_id,
660 p_effective_date => p_effective_date,
661 p_business_group_id => p_business_group_id,
662 p_date_code => 'DOH');
663 end if;
664 --
665 if l_wait_st_date is null then
666 l_wait_st_date := p_lf_evt_ocrd_dt;
667 end if;
668
669 elsif p_wait_perd_dt_to_use_cd = 'EOHDNLRD' then
670 --
671 -- IF EMPLOYEE USE THE ORIGINAL HIRE DATE IF NOT LATER OF LIFE EVENT DATE
672 -- OR NOTIFIED DATE
673
674 if l_employee_flag = TRUE then
675 l_wait_st_date := get_per_svc_dt(p_person_id => p_person_id,
676 p_date_code => 'OHD');
677 else
678 l_wait_st_date := get_ben_asg_dt
679 (p_person_id => p_person_id,
680 p_effective_date => p_effective_date,
681 p_business_group_id => p_business_group_id,
682 p_date_code => 'DOH');
683 end if;
684 --
685 if l_wait_st_date is null then
686 l_wait_st_date := greatest(p_lf_evt_ocrd_dt,nvl(p_ntfn_dt,hr_api.g_sot));
687 end if;
688
689 -- Bug 1838055 : Added code AED.
690 elsif p_wait_perd_dt_to_use_cd in ('LED', 'AED') then
691 --
692 -- Use the Life Event Date
693 --
694 l_wait_st_date := p_lf_evt_ocrd_dt;
695 elsif p_wait_perd_dt_to_use_cd = 'LRD' then
696 --
697 -- Use the Later or Life Event Date or Notified Date
698 --
699 l_wait_st_date := greatest(p_lf_evt_ocrd_dt,nvl(p_ntfn_dt,hr_api.g_sot));
700 else
701 --
702 -- Defensive coding in case code is not known.
703 --
704 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
705 fnd_message.set_token('PROC',l_proc);
706 fnd_message.set_token('CODE1',p_wait_perd_dt_to_use_cd);
707 raise ben_manage_life_events.g_record_error ;
708 end if;
709 --
710 if p_wait_perd_dt_to_use_cd IS NOT NULL and l_wait_st_date IS NULL then
711 --
712 hr_utility.set_location('ERROR in ' || l_proc, 10);
713 --
714 fnd_message.set_name('BEN', 'BEN_92197_CANT_CALC_SVC_DATE');
715 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
716 fnd_message.set_token('DATE_CODE',p_wait_perd_dt_to_use_cd);
717 fnd_message.set_token('PROC',l_proc);
718 raise ben_manage_life_events.g_record_error;
719 --
720 end if;
721 --
722 hr_utility.set_location('Wait start date ' || l_wait_st_date, 10);
723 --
724 -- Now calculate the waiting period end date.
725 --
726 if p_wait_perd_rl is not null and
727 p_wait_perd_val is null and
728 p_wait_perd_uom is null then
729 --
730 -- Execute the waiting period rule to determine the uom and value.
731 --
732 --
733 -- Use the rule to determine the waiting period date from.
734 --
735 if p_oipl_id is not null then
736 ben_comp_object.get_object(p_oipl_id => p_oipl_id,
737 p_rec => l_oipl_rec);
738 end if;
739 if p_pl_id is not null then
740 ben_comp_object.get_object(p_pl_id => p_pl_id,
741 p_rec => l_pln_rec);
742 end if;
743 ben_person_object.get_object(p_person_id => p_person_id,
744 p_rec => l_ass_rec);
745 if l_ass_rec.assignment_id is null then
746 ben_person_object.get_benass_object(p_person_id => p_person_id,
747 p_rec => l_ass_rec);
748 end if;
749 if l_ass_rec.location_id is not null then
750 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
751 p_rec => l_loc_rec);
752 /*
753 if l_loc_rec.region_2 is not null then
754 l_jurisdiction_code :=
755 pay_mag_utils.lookup_jurisdiction_code
756 (p_state => l_loc_rec.region_2);
757 end if;
758 */
759 end if;
760 --
761 -- Initialize the fast formula to return l_date_from
762 --
763 l_outputs := benutils.formula
764 (p_formula_id => p_wait_perd_rl,
765 p_effective_date => p_effective_date,
766 p_assignment_id => l_ass_rec.assignment_id,
767 p_organization_id => l_ass_rec.organization_id,
768 p_pgm_id => p_pgm_id,
769 p_pl_id => p_pl_id,
770 p_pl_typ_id => l_pln_rec.pl_typ_id,
771 p_opt_id => l_oipl_rec.opt_id,
772 p_ler_id => p_ler_id,
773 p_business_group_id => p_business_group_id,
774 p_jurisdiction_code => l_jurisdiction_code,
775 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
776 p_param1_value => to_char(p_person_id));
777 --
778 -- Loop through the returned table and make sure that the returned values
779 -- have been found
780 --
781 for l_count in l_outputs.first..l_outputs.last loop
782 if l_outputs(l_count).name = 'WAIT_PERD_VAL' then
783 l_wait_perd_val := l_outputs(l_count).value;
784 elsif l_outputs(l_count).name = 'WAIT_PERD_UOM' then
785 l_wait_perd_uom := l_outputs(l_count).value;
786 else
787 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
788 fnd_message.set_token('RL',
789 'wait_perd_rl :'||p_wait_perd_rl);
790 fnd_message.set_token('PROC',l_proc);
791 fnd_message.raise_error;
792 end if;
793 end loop;
794 else
795 --
796 -- There is no rule defined. Use the val and uom defined.
797 --
798 l_wait_perd_uom := p_wait_perd_uom;
799 l_wait_perd_val := p_wait_perd_val;
800 end if;
801 --
802 -- Use the wait_perd_uom and wait_perd_val to compute the wait end date.
803 --
804 if l_wait_perd_uom = 'DY' then
805 l_wait_end_date := l_wait_st_date + l_wait_perd_val;
806 elsif l_wait_perd_uom = 'WK' then
807 l_wait_end_date := l_wait_st_date + (l_wait_perd_val * 7);
808 elsif l_wait_perd_uom = 'MO' then
809 l_wait_end_date := add_months(l_wait_st_date,l_wait_perd_val);
810 elsif l_wait_perd_uom = 'QTR' then
811 l_wait_end_date := add_months(l_wait_st_date,(3 * l_wait_perd_val));
812 elsif l_wait_perd_uom = 'YR' then
813 l_wait_end_date := add_months(l_wait_st_date,(12 * l_wait_perd_val));
814 end if;
815 p_wait_perd_strt_dt := l_wait_st_date ;
816 hr_utility.set_location('Leaving : ' || l_proc, 10);
817
818 return l_wait_end_date;
819
820 end get_wait_end_date;
821 --
822 -- -----------------------------------------------------------------------------
823 -- |------------------------------< main >-------------------------------------|
824 -- -----------------------------------------------------------------------------
825 --
826 -- This is the main procedure that is called to compute the waiting period end
827 -- date for a plan.
828 --
829 procedure main
830 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
831 ,p_person_id in number
832 ,p_effective_date in date
833 ,p_business_group_id in number
834 ,p_ler_id in number
835 ,p_oipl_id in number
836 ,p_pl_id in number
837 ,p_pgm_id in number
838 ,p_plip_id in number
839 ,p_ptip_id in number
840 ,p_lf_evt_ocrd_dt in date
841 ,p_ntfn_dt in date
842 ,p_return_date out nocopy date
843 ,p_wait_perd_strt_dt out nocopy date
844 )
845 is
846 --
847 l_dummy_num number;
848 l_wait_perd_dt_to_use_cd varchar2(80);
849 l_wait_perd_dt_to_use_rl number(15);
850 l_wait_perd_rl number(15);
851 l_wait_perd_uom varchar2(80);
852 l_wait_perd_val number(15);
853 l_wait_end_date date;
854 l_max_wait_end_date date;
855 l_pln_rec ben_pl_f%rowtype;
856 l_plip_id number(15);
857 l_wait_data_found boolean := FALSE;
858 l_env_rec ben_env_object.g_global_env_rec_type;
859 l_prtn_rec ben_prtn_elig_f%rowtype;
860 l_elig_rec ben_elig_to_prte_rsn_f%rowtype;
861 --
862 l_proc varchar2(80) := g_package || '.main';
863 --
864 l_envpgm_id number;
865 l_envptip_id number;
866 l_envplip_id number;
867 l_envpl_id number;
868 --
869 begin
870 hr_utility.set_location('Entering : ' || l_proc, 10);
871 hr_utility.set_location('p_pgm_id -> ' ||p_pgm_id ,10);
872 hr_utility.set_location('p_ptip_id -> ' ||p_ptip_id,10);
873 hr_utility.set_location('p_plip_id -> ' ||p_plip_id,10);
874 hr_utility.set_location('p_pl_id -> ' ||p_pl_id ,10);
875 hr_utility.set_location('p_oipl_id -> ' ||p_oipl_id,10);
876
877 ben_env_object.get(p_rec => l_env_rec);
878 --
879 -- Assign comp object locals
880 --
881 l_envpgm_id := p_comp_obj_tree_row.par_pgm_id;
882 l_envptip_id := p_comp_obj_tree_row.par_ptip_id;
883 l_envplip_id := p_comp_obj_tree_row.par_plip_id;
884 l_envpl_id := p_comp_obj_tree_row.par_pl_id;
885 --
886 -- Look for waiting period attributes at the appropriate level based on the
887 -- comp object being processed.
888 if p_pgm_id is not null then
889 -- The comp object being passed in is a pgm. Get wtg data at pgm level.
890 if ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_cd is null then
891 if ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_cd is null then
892 l_wait_data_found := false;
893 else
894 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_cd;
895 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_rl;
896 l_wait_perd_rl := ben_cobj_cache.g_pgmetpr_currow.wait_perd_rl;
897 l_wait_perd_uom := ben_cobj_cache.g_pgmetpr_currow.wait_perd_uom;
898 l_wait_perd_val := ben_cobj_cache.g_pgmetpr_currow.wait_perd_val;
899 l_wait_data_found := true;
900 end if;
901 else
902 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_cd;
903 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_rl;
904 l_wait_perd_rl := ben_cobj_cache.g_pgmprel_currow.wait_perd_rl;
905 l_wait_perd_uom := ben_cobj_cache.g_pgmprel_currow.wait_perd_uom;
906 l_wait_perd_val := ben_cobj_cache.g_pgmprel_currow.wait_perd_val;
907 l_wait_data_found := true;
908 end if;
909 end if; -- if pgm
910
911 if p_oipl_id is not null then
912 -- The comp object being evaluated is a OIPL. Get details at OIPL level.
913 if ben_cobj_cache.g_oiplprel_currow.wait_perd_dt_to_use_cd is null then
914 if ben_cobj_cache.g_oipletpr_currow.wait_perd_dt_to_use_cd is null then
915 l_wait_data_found := false;
916 else
917 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_oipletpr_currow.wait_perd_dt_to_use_cd;
918 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_oipletpr_currow.wait_perd_dt_to_use_rl;
919 l_wait_perd_rl := ben_cobj_cache.g_oipletpr_currow.wait_perd_rl;
920 l_wait_perd_uom := ben_cobj_cache.g_oipletpr_currow.wait_perd_uom;
921 l_wait_perd_val := ben_cobj_cache.g_oipletpr_currow.wait_perd_val;
922 l_wait_data_found := true;
923 end if;
924 else
925 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_oiplprel_currow.wait_perd_dt_to_use_cd;
926 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_oiplprel_currow.wait_perd_dt_to_use_rl;
927 l_wait_perd_rl := ben_cobj_cache.g_oiplprel_currow.wait_perd_rl;
928 l_wait_perd_uom := ben_cobj_cache.g_oiplprel_currow.wait_perd_uom;
929 l_wait_perd_val := ben_cobj_cache.g_oiplprel_currow.wait_perd_val;
930 l_wait_data_found := true;
931 end if;
932 end if; -- if oipl
933
934 if p_pl_id is not null or (p_oipl_id is not null and l_wait_data_found = FALSE)
935 or p_plip_id is not null then
936 -- The comp object being evaluated is a plan.
937 if l_envplip_id is not null then
938 -- The plan belongs to a program. Try to get anything defined at the
939 -- plip level first.
940 if ben_cobj_cache.g_plipprel_currow.wait_perd_dt_to_use_cd is null then
941 if ben_cobj_cache.g_plipetpr_currow.wait_perd_dt_to_use_cd is null then
942 l_wait_data_found := false;
943 else
944 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_plipetpr_currow.wait_perd_dt_to_use_cd;
945 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_plipetpr_currow.wait_perd_dt_to_use_rl;
946 l_wait_perd_rl := ben_cobj_cache.g_plipetpr_currow.wait_perd_rl;
947 l_wait_perd_uom := ben_cobj_cache.g_plipetpr_currow.wait_perd_uom;
948 l_wait_perd_val := ben_cobj_cache.g_plipetpr_currow.wait_perd_val;
949 l_wait_data_found := true;
950 end if;
951 else
952 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_plipprel_currow.wait_perd_dt_to_use_cd;
953 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_plipprel_currow.wait_perd_dt_to_use_rl;
954 l_wait_perd_rl := ben_cobj_cache.g_plipprel_currow.wait_perd_rl;
955 l_wait_perd_uom := ben_cobj_cache.g_plipprel_currow.wait_perd_uom;
956 l_wait_perd_val := ben_cobj_cache.g_plipprel_currow.wait_perd_val;
957 l_wait_data_found := true;
958 end if;
959 end if; -- if plip_id is not null
960 --
961 -- If nothing was found at the PLIP level above, then search at the PL level
962 --
963 if l_wait_data_found = FALSE and (p_pl_id is not null or p_oipl_id is not null) then
964 if ben_cobj_cache.g_plprel_currow.wait_perd_dt_to_use_cd is null then
965 if ben_cobj_cache.g_pletpr_currow.wait_perd_dt_to_use_cd is null then
966 l_wait_data_found := false;
967 else
968 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pletpr_currow.wait_perd_dt_to_use_cd;
969 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pletpr_currow.wait_perd_dt_to_use_rl;
970 l_wait_perd_rl := ben_cobj_cache.g_pletpr_currow.wait_perd_rl;
971 l_wait_perd_uom := ben_cobj_cache.g_pletpr_currow.wait_perd_uom;
972 l_wait_perd_val := ben_cobj_cache.g_pletpr_currow.wait_perd_val;
973 l_wait_data_found := true;
974 end if;
975 else
976 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_plprel_currow.wait_perd_dt_to_use_cd;
977 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_plprel_currow.wait_perd_dt_to_use_rl;
978 l_wait_perd_rl := ben_cobj_cache.g_plprel_currow.wait_perd_rl;
979 l_wait_perd_uom := ben_cobj_cache.g_plprel_currow.wait_perd_uom;
980 l_wait_perd_val := ben_cobj_cache.g_plprel_currow.wait_perd_val;
981 l_wait_data_found := true;
982 end if;
983 end if; -- wait_data_found=false
984 end if; -- if plan or oipl(and didn't find oipl level)
985
986 -- If no wait period data was found for plip, pl or oipl records, or we
987 -- are working with a ptip, look for ptip
988 if l_wait_data_found =FALSE and l_envptip_id is not null and
989 (p_pl_id is not null or p_oipl_id is not null or p_ptip_id is not null
990 or p_plip_id is not null) then
991 if ben_cobj_cache.g_ptipprel_currow.wait_perd_dt_to_use_cd is null then
992 if ben_cobj_cache.g_ptipetpr_currow.wait_perd_dt_to_use_cd is null then
993 -- Look at pgm level
994 if ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_cd is null then
995 if ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_cd is null then
996 l_wait_data_found := false;
997 else
998 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_cd;
999 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pgmprel_currow.wait_perd_dt_to_use_rl;
1000 l_wait_perd_rl := ben_cobj_cache.g_pgmprel_currow.wait_perd_rl;
1001 l_wait_perd_uom := ben_cobj_cache.g_pgmprel_currow.wait_perd_uom;
1002 l_wait_perd_val := ben_cobj_cache.g_pgmprel_currow.wait_perd_val;
1003 l_wait_data_found := true;
1004 end if;
1005 else
1006 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_cd;
1007 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pgmetpr_currow.wait_perd_dt_to_use_rl;
1008 l_wait_perd_rl := ben_cobj_cache.g_pgmetpr_currow.wait_perd_rl;
1009 l_wait_perd_uom := ben_cobj_cache.g_pgmetpr_currow.wait_perd_uom;
1010 l_wait_perd_val := ben_cobj_cache.g_pgmetpr_currow.wait_perd_val;
1011 l_wait_data_found := true;
1012 end if;
1013 else
1014 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_ptipetpr_currow.wait_perd_dt_to_use_cd;
1015 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_ptipetpr_currow.wait_perd_dt_to_use_rl;
1016 l_wait_perd_rl := ben_cobj_cache.g_ptipetpr_currow.wait_perd_rl;
1017 l_wait_perd_uom := ben_cobj_cache.g_ptipetpr_currow.wait_perd_uom;
1018 l_wait_perd_val := ben_cobj_cache.g_ptipetpr_currow.wait_perd_val;
1019 l_wait_data_found := true;
1020 end if;
1021 else
1022 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_ptipprel_currow.wait_perd_dt_to_use_cd;
1023 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_ptipprel_currow.wait_perd_dt_to_use_rl;
1024 l_wait_perd_rl := ben_cobj_cache.g_ptipprel_currow.wait_perd_rl;
1025 l_wait_perd_uom := ben_cobj_cache.g_ptipprel_currow.wait_perd_uom;
1026 l_wait_perd_val := ben_cobj_cache.g_ptipprel_currow.wait_perd_val;
1027 l_wait_data_found := true;
1028 end if;
1029
1030 end if;
1031 if l_wait_data_found = TRUE then
1032 --
1033 -- Waiting period data was found. Continue with processing.
1034 --
1035 hr_utility.set_location('Waiting period data found. Continuing', 60);
1036 -- Calculate the date when the waiting period will end.
1037 l_wait_end_date := get_wait_end_date
1038 (p_wait_perd_dt_to_use_cd => l_wait_perd_dt_to_use_cd,
1039 p_wait_perd_dt_to_use_rl => l_wait_perd_dt_to_use_rl,
1040 p_wait_perd_rl => l_wait_perd_rl,
1041 p_wait_perd_uom => l_wait_perd_uom,
1042 p_wait_perd_val => l_wait_perd_val,
1043 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1044 p_ntfn_dt => p_ntfn_dt,
1045 p_person_id => p_person_id,
1046 p_pgm_id => p_pgm_id,
1047 p_pl_id => p_pl_id,
1048 p_oipl_id => p_oipl_id,
1049 p_ler_id => p_ler_id,
1050 p_effective_date => p_effective_date,
1051 p_business_group_id => p_business_group_id,
1052 p_wait_perd_strt_dt => p_wait_perd_strt_dt );
1053 hr_utility.set_location('Wait end date : ' ||
1054 nvl(to_char(l_wait_end_date, 'DD-MON-YYYY')
1055 ,'NULL'), 62);
1056 --
1057 -- Get the plan's max waiting perd attibutes form the cache.
1058 --
1059 if p_pl_id is not null or p_oipl_id is not null then /*Bug 3047147 chk for p_oipl_id also*/
1060 --
1061 -- Get the plan related data from the cache.
1062 --
1063 l_wait_perd_dt_to_use_cd := ben_cobj_cache.g_pl_currow.mx_wtg_dt_to_use_cd;
1064 l_wait_perd_dt_to_use_rl := ben_cobj_cache.g_pl_currow.mx_wtg_dt_to_use_rl;
1065 l_wait_perd_rl := ben_cobj_cache.g_pl_currow.mx_wtg_perd_rl;
1066 l_wait_perd_uom := ben_cobj_cache.g_pl_currow.mx_wtg_perd_prte_uom;
1067 l_wait_perd_val := ben_cobj_cache.g_pl_currow.mx_wtg_perd_prte_val;
1068 --
1069 -- Now calculate the maximum waiting period end date if one is defined.
1070 --
1071 if l_wait_perd_dt_to_use_cd is not null then
1072 --
1073 l_max_wait_end_date :=
1074 get_wait_end_date
1075 (p_wait_perd_dt_to_use_cd => l_wait_perd_dt_to_use_cd,
1076 p_wait_perd_dt_to_use_rl => l_wait_perd_dt_to_use_rl,
1077 p_wait_perd_rl => l_wait_perd_rl,
1078 p_wait_perd_uom => l_wait_perd_uom,
1079 p_wait_perd_val => l_wait_perd_val,
1080 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1081 p_ntfn_dt => p_ntfn_dt,
1082 p_person_id => p_person_id,
1083 p_pgm_id => p_pgm_id,
1084 p_pl_id => p_pl_id,
1085 p_oipl_id => p_oipl_id,
1086 p_ler_id => p_ler_id,
1087 p_effective_date => p_effective_date,
1088 p_business_group_id => p_business_group_id,
1089 p_wait_perd_strt_dt => p_wait_perd_strt_dt );
1090 hr_utility.set_location('Max wait end date : ' ||
1091 to_char(l_max_wait_end_date, 'DD-MON-YYYY'), 70);
1092 end if;
1093 end if;
1094
1095 if l_max_wait_end_date is not null then
1096 --
1097 -- Compare the waiting period end date to the max_wait_end_date. If it is
1098 -- greater than the max wait end date then return the max wait end date.
1099 -- If not then return the wait_end_date.
1100 --
1101 if l_wait_end_date > l_max_wait_end_date then
1102 p_return_date := l_max_wait_end_date;
1103 else
1104 p_return_date := l_wait_end_date;
1105 end if;
1106 else
1107 -- There is no maximum wait for the plan. Just return the wait_end_date.
1108 p_return_date := l_wait_end_date;
1109 end if;
1110 else
1111 -- l_wait_data_found is FALSE. Exit the procedure
1112 hr_utility.set_location('Waiting period data not found.', 90);
1113 p_return_date := NULL;
1114 end if;
1115
1116 hr_utility.set_location('Leaving : ' || l_proc, 99);
1117 end main;
1118 --
1119 end ben_det_wait_perd_cmpltn;
[Home] [Help]