[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_DPNT_API
Source
1 Package Body ben_ELIG_DPNT_api as
2 /* $Header: beegdapi.pkb 120.11 2006/11/02 15:44:27 rgajula noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_ELIG_DPNT_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ELIG_DPNT >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ELIG_DPNT
13 (p_validate in boolean default false
14 ,p_elig_dpnt_id out nocopy number
15 ,p_create_dt in date default null
16 ,p_elig_strt_dt in date default null
17 ,p_elig_thru_dt in date default null
18 ,p_ovrdn_flag in varchar2 default 'N'
19 ,p_ovrdn_thru_dt in date default null
20 ,p_inelg_rsn_cd in varchar2 default null
21 ,p_dpnt_inelig_flag in varchar2 default 'N'
22 ,p_elig_per_elctbl_chc_id in number default null
23 ,p_per_in_ler_id in number default null
24 ,p_elig_per_id in number default null
25 ,p_elig_per_opt_id in number default null
26 ,p_elig_cvrd_dpnt_id in number default null
27 ,p_dpnt_person_id in number default null
28 ,p_business_group_id in number default null
29 ,p_egd_attribute_category in varchar2 default null
30 ,p_egd_attribute1 in varchar2 default null
31 ,p_egd_attribute2 in varchar2 default null
32 ,p_egd_attribute3 in varchar2 default null
33 ,p_egd_attribute4 in varchar2 default null
34 ,p_egd_attribute5 in varchar2 default null
35 ,p_egd_attribute6 in varchar2 default null
36 ,p_egd_attribute7 in varchar2 default null
37 ,p_egd_attribute8 in varchar2 default null
38 ,p_egd_attribute9 in varchar2 default null
39 ,p_egd_attribute10 in varchar2 default null
40 ,p_egd_attribute11 in varchar2 default null
41 ,p_egd_attribute12 in varchar2 default null
42 ,p_egd_attribute13 in varchar2 default null
43 ,p_egd_attribute14 in varchar2 default null
44 ,p_egd_attribute15 in varchar2 default null
45 ,p_egd_attribute16 in varchar2 default null
46 ,p_egd_attribute17 in varchar2 default null
47 ,p_egd_attribute18 in varchar2 default null
48 ,p_egd_attribute19 in varchar2 default null
49 ,p_egd_attribute20 in varchar2 default null
50 ,p_egd_attribute21 in varchar2 default null
51 ,p_egd_attribute22 in varchar2 default null
52 ,p_egd_attribute23 in varchar2 default null
53 ,p_egd_attribute24 in varchar2 default null
54 ,p_egd_attribute25 in varchar2 default null
55 ,p_egd_attribute26 in varchar2 default null
56 ,p_egd_attribute27 in varchar2 default null
57 ,p_egd_attribute28 in varchar2 default null
58 ,p_egd_attribute29 in varchar2 default null
59 ,p_egd_attribute30 in varchar2 default null
60 ,p_request_id in number default null
61 ,p_program_application_id in number default null
62 ,p_program_id in number default null
63 ,p_program_update_date in date default null
64 ,p_object_version_number out nocopy number
65 ,p_effective_date in date
66 ) is
67 --
68 -- Declare cursors and local variables
69 --
70 l_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%TYPE;
71 l_proc varchar2(72) := g_package||'create_ELIG_DPNT';
72 l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
73 --
74 begin
75 --
76 hr_utility.set_location('Entering:'|| l_proc, 10);
77 --
78 -- Issue a savepoint if operating in validation only mode
79 --
80 savepoint create_ELIG_DPNT;
81 --
82 hr_utility.set_location(l_proc, 20);
83 --
84 -- Process Logic
85 --
86 begin
87 --
88 -- Start of API User Hook for the before hook of create_ELIG_DPNT
89 --
90 ben_ELIG_DPNT_bk1.create_ELIG_DPNT_b
91 (
92 p_create_dt => p_create_dt
93 ,p_elig_strt_dt => p_elig_strt_dt
94 ,p_elig_thru_dt => p_elig_thru_dt
95 ,p_ovrdn_flag => p_ovrdn_flag
96 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
97 ,p_inelg_rsn_cd => p_inelg_rsn_cd
98 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
99 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
100 ,p_per_in_ler_id => p_per_in_ler_id
101 ,p_elig_per_id => p_elig_per_id
102 ,p_elig_per_opt_id => p_elig_per_opt_id
103 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
104 ,p_dpnt_person_id => p_dpnt_person_id
105 ,p_business_group_id => p_business_group_id
106 ,p_egd_attribute_category => p_egd_attribute_category
107 ,p_egd_attribute1 => p_egd_attribute1
108 ,p_egd_attribute2 => p_egd_attribute2
109 ,p_egd_attribute3 => p_egd_attribute3
110 ,p_egd_attribute4 => p_egd_attribute4
111 ,p_egd_attribute5 => p_egd_attribute5
112 ,p_egd_attribute6 => p_egd_attribute6
113 ,p_egd_attribute7 => p_egd_attribute7
114 ,p_egd_attribute8 => p_egd_attribute8
115 ,p_egd_attribute9 => p_egd_attribute9
116 ,p_egd_attribute10 => p_egd_attribute10
117 ,p_egd_attribute11 => p_egd_attribute11
118 ,p_egd_attribute12 => p_egd_attribute12
119 ,p_egd_attribute13 => p_egd_attribute13
120 ,p_egd_attribute14 => p_egd_attribute14
121 ,p_egd_attribute15 => p_egd_attribute15
122 ,p_egd_attribute16 => p_egd_attribute16
123 ,p_egd_attribute17 => p_egd_attribute17
124 ,p_egd_attribute18 => p_egd_attribute18
125 ,p_egd_attribute19 => p_egd_attribute19
126 ,p_egd_attribute20 => p_egd_attribute20
127 ,p_egd_attribute21 => p_egd_attribute21
128 ,p_egd_attribute22 => p_egd_attribute22
129 ,p_egd_attribute23 => p_egd_attribute23
130 ,p_egd_attribute24 => p_egd_attribute24
131 ,p_egd_attribute25 => p_egd_attribute25
132 ,p_egd_attribute26 => p_egd_attribute26
133 ,p_egd_attribute27 => p_egd_attribute27
134 ,p_egd_attribute28 => p_egd_attribute28
135 ,p_egd_attribute29 => p_egd_attribute29
136 ,p_egd_attribute30 => p_egd_attribute30
137 ,p_request_id => p_request_id
138 ,p_program_application_id => p_program_application_id
139 ,p_program_id => p_program_id
140 ,p_program_update_date => p_program_update_date
141 ,p_effective_date => trunc(p_effective_date)
142 );
143 exception
144 when hr_api.cannot_find_prog_unit then
145 hr_api.cannot_find_prog_unit_error
146 (
147 p_module_name => 'CREATE_ELIG_DPNT'
148 ,p_hook_type => 'BP'
149 );
150 --
151 -- End of API User Hook for the before hook of create_ELIG_DPNT
152 --
153 end;
154 --
155 ben_egd_ins.ins
156 (
157 p_elig_dpnt_id => l_elig_dpnt_id
158 ,p_create_dt => p_create_dt
159 ,p_elig_strt_dt => p_elig_strt_dt
160 ,p_elig_thru_dt => p_elig_thru_dt
161 ,p_ovrdn_flag => p_ovrdn_flag
162 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
163 ,p_inelg_rsn_cd => p_inelg_rsn_cd
164 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
165 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
166 ,p_per_in_ler_id => p_per_in_ler_id
167 ,p_elig_per_id => p_elig_per_id
168 ,p_elig_per_opt_id => p_elig_per_opt_id
169 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
170 ,p_dpnt_person_id => p_dpnt_person_id
171 ,p_business_group_id => p_business_group_id
172 ,p_egd_attribute_category => p_egd_attribute_category
173 ,p_egd_attribute1 => p_egd_attribute1
174 ,p_egd_attribute2 => p_egd_attribute2
175 ,p_egd_attribute3 => p_egd_attribute3
176 ,p_egd_attribute4 => p_egd_attribute4
177 ,p_egd_attribute5 => p_egd_attribute5
178 ,p_egd_attribute6 => p_egd_attribute6
179 ,p_egd_attribute7 => p_egd_attribute7
180 ,p_egd_attribute8 => p_egd_attribute8
181 ,p_egd_attribute9 => p_egd_attribute9
182 ,p_egd_attribute10 => p_egd_attribute10
183 ,p_egd_attribute11 => p_egd_attribute11
184 ,p_egd_attribute12 => p_egd_attribute12
185 ,p_egd_attribute13 => p_egd_attribute13
186 ,p_egd_attribute14 => p_egd_attribute14
187 ,p_egd_attribute15 => p_egd_attribute15
188 ,p_egd_attribute16 => p_egd_attribute16
189 ,p_egd_attribute17 => p_egd_attribute17
190 ,p_egd_attribute18 => p_egd_attribute18
191 ,p_egd_attribute19 => p_egd_attribute19
192 ,p_egd_attribute20 => p_egd_attribute20
193 ,p_egd_attribute21 => p_egd_attribute21
194 ,p_egd_attribute22 => p_egd_attribute22
195 ,p_egd_attribute23 => p_egd_attribute23
196 ,p_egd_attribute24 => p_egd_attribute24
197 ,p_egd_attribute25 => p_egd_attribute25
198 ,p_egd_attribute26 => p_egd_attribute26
199 ,p_egd_attribute27 => p_egd_attribute27
200 ,p_egd_attribute28 => p_egd_attribute28
201 ,p_egd_attribute29 => p_egd_attribute29
202 ,p_egd_attribute30 => p_egd_attribute30
203 ,p_request_id => p_request_id
204 ,p_program_application_id => p_program_application_id
205 ,p_program_id => p_program_id
206 ,p_program_update_date => p_program_update_date
207 ,p_object_version_number => l_object_version_number
208 ,p_effective_date => trunc(p_effective_date)
209 );
210 --
211 begin
212 --
213 -- Start of API User Hook for the after hook of create_ELIG_DPNT
214 --
215 ben_ELIG_DPNT_bk1.create_ELIG_DPNT_a
216 (
217 p_elig_dpnt_id => l_elig_dpnt_id
218 ,p_create_dt => p_create_dt
219 ,p_elig_strt_dt => p_elig_strt_dt
220 ,p_elig_thru_dt => p_elig_thru_dt
221 ,p_ovrdn_flag => p_ovrdn_flag
222 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
223 ,p_inelg_rsn_cd => p_inelg_rsn_cd
224 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
225 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
226 ,p_per_in_ler_id => p_per_in_ler_id
227 ,p_elig_per_id => p_elig_per_id
228 ,p_elig_per_opt_id => p_elig_per_opt_id
229 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
230 ,p_dpnt_person_id => p_dpnt_person_id
231 ,p_business_group_id => p_business_group_id
232 ,p_egd_attribute_category => p_egd_attribute_category
233 ,p_egd_attribute1 => p_egd_attribute1
234 ,p_egd_attribute2 => p_egd_attribute2
235 ,p_egd_attribute3 => p_egd_attribute3
236 ,p_egd_attribute4 => p_egd_attribute4
237 ,p_egd_attribute5 => p_egd_attribute5
238 ,p_egd_attribute6 => p_egd_attribute6
239 ,p_egd_attribute7 => p_egd_attribute7
240 ,p_egd_attribute8 => p_egd_attribute8
241 ,p_egd_attribute9 => p_egd_attribute9
242 ,p_egd_attribute10 => p_egd_attribute10
243 ,p_egd_attribute11 => p_egd_attribute11
244 ,p_egd_attribute12 => p_egd_attribute12
245 ,p_egd_attribute13 => p_egd_attribute13
246 ,p_egd_attribute14 => p_egd_attribute14
247 ,p_egd_attribute15 => p_egd_attribute15
248 ,p_egd_attribute16 => p_egd_attribute16
249 ,p_egd_attribute17 => p_egd_attribute17
250 ,p_egd_attribute18 => p_egd_attribute18
251 ,p_egd_attribute19 => p_egd_attribute19
252 ,p_egd_attribute20 => p_egd_attribute20
253 ,p_egd_attribute21 => p_egd_attribute21
254 ,p_egd_attribute22 => p_egd_attribute22
255 ,p_egd_attribute23 => p_egd_attribute23
256 ,p_egd_attribute24 => p_egd_attribute24
257 ,p_egd_attribute25 => p_egd_attribute25
258 ,p_egd_attribute26 => p_egd_attribute26
259 ,p_egd_attribute27 => p_egd_attribute27
260 ,p_egd_attribute28 => p_egd_attribute28
261 ,p_egd_attribute29 => p_egd_attribute29
262 ,p_egd_attribute30 => p_egd_attribute30
263 ,p_request_id => p_request_id
264 ,p_program_application_id => p_program_application_id
265 ,p_program_id => p_program_id
266 ,p_program_update_date => p_program_update_date
267 ,p_object_version_number => l_object_version_number
268 ,p_effective_date => trunc(p_effective_date)
269 );
270 exception
271 when hr_api.cannot_find_prog_unit then
272 hr_api.cannot_find_prog_unit_error
273 (p_module_name => 'CREATE_ELIG_DPNT'
274 ,p_hook_type => 'AP'
275 );
276 --
277 -- End of API User Hook for the after hook of create_ELIG_DPNT
278 --
279 end;
280 --
281 hr_utility.set_location(l_proc, 60);
282 --
283 -- When in validation only mode raise the Validate_Enabled exception
284 --
285 if p_validate then
286 raise hr_api.validate_enabled;
287 end if;
288 --
289 -- Set all output arguments
290 --
291 p_elig_dpnt_id := l_elig_dpnt_id;
292 p_object_version_number := l_object_version_number;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 70);
295 --
296 exception
297 --
298 when hr_api.validate_enabled then
299 --
300 -- As the Validate_Enabled exception has been raised
301 -- we must rollback to the savepoint
302 --
303 ROLLBACK TO create_ELIG_DPNT;
304 --
305 -- Only set output warning arguments
306 -- (Any key or derived arguments must be set to null
307 -- when validation only mode is being used.)
308 --
309 p_elig_dpnt_id := null;
310 p_object_version_number := null;
311 hr_utility.set_location(' Leaving:'||l_proc, 80);
312 --
313 when others then
314 --
315 -- A validation or unexpected error has occured
316 --
317 ROLLBACK TO create_ELIG_DPNT;
318 raise;
319 --
320 end create_ELIG_DPNT;
321 --
322 -- ----------------------------------------------------------------------------
323 -- |------------------------< create_perf_ELIG_DPNT >-------------------------|
324 -- ----------------------------------------------------------------------------
325 --
326 procedure create_perf_ELIG_DPNT
327 (p_validate in boolean default false
328 ,p_elig_dpnt_id out nocopy number
329 ,p_create_dt in date default null
330 ,p_elig_strt_dt in date default null
331 ,p_elig_thru_dt in date default null
332 ,p_ovrdn_flag in varchar2 default 'N'
333 ,p_ovrdn_thru_dt in date default null
334 ,p_inelg_rsn_cd in varchar2 default null
335 ,p_dpnt_inelig_flag in varchar2 default 'N'
336 ,p_elig_per_elctbl_chc_id in number default null
337 ,p_per_in_ler_id in number default null
338 ,p_elig_per_id in number default null
339 ,p_elig_per_opt_id in number default null
340 ,p_elig_cvrd_dpnt_id in number default null
341 ,p_dpnt_person_id in number default null
342 ,p_business_group_id in number default null
343 ,p_egd_attribute_category in varchar2 default null
344 ,p_egd_attribute1 in varchar2 default null
345 ,p_egd_attribute2 in varchar2 default null
346 ,p_egd_attribute3 in varchar2 default null
347 ,p_egd_attribute4 in varchar2 default null
348 ,p_egd_attribute5 in varchar2 default null
349 ,p_egd_attribute6 in varchar2 default null
350 ,p_egd_attribute7 in varchar2 default null
351 ,p_egd_attribute8 in varchar2 default null
352 ,p_egd_attribute9 in varchar2 default null
353 ,p_egd_attribute10 in varchar2 default null
354 ,p_egd_attribute11 in varchar2 default null
355 ,p_egd_attribute12 in varchar2 default null
356 ,p_egd_attribute13 in varchar2 default null
357 ,p_egd_attribute14 in varchar2 default null
358 ,p_egd_attribute15 in varchar2 default null
359 ,p_egd_attribute16 in varchar2 default null
360 ,p_egd_attribute17 in varchar2 default null
361 ,p_egd_attribute18 in varchar2 default null
362 ,p_egd_attribute19 in varchar2 default null
363 ,p_egd_attribute20 in varchar2 default null
364 ,p_egd_attribute21 in varchar2 default null
365 ,p_egd_attribute22 in varchar2 default null
366 ,p_egd_attribute23 in varchar2 default null
367 ,p_egd_attribute24 in varchar2 default null
368 ,p_egd_attribute25 in varchar2 default null
369 ,p_egd_attribute26 in varchar2 default null
370 ,p_egd_attribute27 in varchar2 default null
371 ,p_egd_attribute28 in varchar2 default null
372 ,p_egd_attribute29 in varchar2 default null
373 ,p_egd_attribute30 in varchar2 default null
374 ,p_request_id in number default null
375 ,p_program_application_id in number default null
376 ,p_program_id in number default null
377 ,p_program_update_date in date default null
378 ,p_object_version_number out nocopy number
379 ,p_effective_date in date
380 )
381 is
382 --
383 l_proc varchar2(72) := g_package||'create_perf_ELIG_DPNT';
384 --
385 l_old_rec ben_egd_ler.g_egd_ler_rec;
386 l_new_rec ben_egd_ler.g_egd_ler_rec;
387 --
388 -- Declare cursors and local variables
389 --
390 l_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%TYPE;
391 l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
392 --
393 l_created_by ben_elig_dpnt.created_by%TYPE;
394 l_creation_date ben_elig_dpnt.creation_date%TYPE;
395 l_last_update_date ben_elig_dpnt.last_update_date%TYPE;
396 l_last_updated_by ben_elig_dpnt.last_updated_by%TYPE;
397 l_last_update_login ben_elig_dpnt.last_update_login%TYPE;
398 --
399 Cursor C_Sel1 is select ben_elig_dpnt_s.nextval from sys.dual;
400 --
401 begin
402 --
403 hr_utility.set_location('Entering:'|| l_proc, 10);
404 --
405 -- Issue a savepoint if operating in validation only mode
406 --
407 savepoint create_perf_ELIG_DPNT;
408 --
409 -- Insert the row
410 --
411 -- Set the object version number for the insert
412 --
413 l_object_version_number := 1;
414 --
415 ben_egd_shd.g_api_dml := true; -- Set the api dml status
416 --
417 hr_utility.set_location('Insert EGD: '||l_proc, 5);
418 insert into ben_elig_dpnt
419 ( elig_dpnt_id,
420 create_dt,
421 elig_strt_dt,
422 elig_thru_dt,
423 ovrdn_flag,
424 ovrdn_thru_dt,
425 inelg_rsn_cd,
426 dpnt_inelig_flag,
427 elig_per_elctbl_chc_id,
428 per_in_ler_id,
429 elig_per_id,
430 elig_per_opt_id,
431 elig_cvrd_dpnt_id,
432 dpnt_person_id,
433 business_group_id,
434 egd_attribute_category,
435 egd_attribute1,
436 egd_attribute2,
437 egd_attribute3,
438 egd_attribute4,
439 egd_attribute5,
440 egd_attribute6,
441 egd_attribute7,
442 egd_attribute8,
443 egd_attribute9,
444 egd_attribute10,
445 egd_attribute11,
446 egd_attribute12,
447 egd_attribute13,
448 egd_attribute14,
449 egd_attribute15,
450 egd_attribute16,
451 egd_attribute17,
452 egd_attribute18,
453 egd_attribute19,
454 egd_attribute20,
455 egd_attribute21,
456 egd_attribute22,
457 egd_attribute23,
458 egd_attribute24,
459 egd_attribute25,
460 egd_attribute26,
461 egd_attribute27,
462 egd_attribute28,
463 egd_attribute29,
464 egd_attribute30,
465 request_id,
466 program_application_id,
467 program_id,
468 program_update_date,
469 object_version_number
470 )
471 Values
472 ( ben_elig_dpnt_s.nextval,
473 p_create_dt,
474 p_elig_strt_dt,
475 p_elig_thru_dt,
476 p_ovrdn_flag,
477 p_ovrdn_thru_dt,
478 p_inelg_rsn_cd,
479 p_dpnt_inelig_flag,
480 p_elig_per_elctbl_chc_id,
481 p_per_in_ler_id,
482 p_elig_per_id,
483 p_elig_per_opt_id,
484 p_elig_cvrd_dpnt_id,
485 p_dpnt_person_id,
486 p_business_group_id,
487 p_egd_attribute_category,
488 p_egd_attribute1,
489 p_egd_attribute2,
490 p_egd_attribute3,
491 p_egd_attribute4,
492 p_egd_attribute5,
493 p_egd_attribute6,
494 p_egd_attribute7,
495 p_egd_attribute8,
496 p_egd_attribute9,
497 p_egd_attribute10,
498 p_egd_attribute11,
499 p_egd_attribute12,
500 p_egd_attribute13,
501 p_egd_attribute14,
502 p_egd_attribute15,
503 p_egd_attribute16,
504 p_egd_attribute17,
505 p_egd_attribute18,
506 p_egd_attribute19,
507 p_egd_attribute20,
508 p_egd_attribute21,
509 p_egd_attribute22,
510 p_egd_attribute23,
511 p_egd_attribute24,
512 p_egd_attribute25,
513 p_egd_attribute26,
514 p_egd_attribute27,
515 p_egd_attribute28,
516 p_egd_attribute29,
517 p_egd_attribute30,
518 p_request_id,
519 p_program_application_id,
520 p_program_id,
521 p_program_update_date,
522 l_object_version_number
523 ) RETURNING elig_dpnt_id into l_elig_dpnt_id;
524 hr_utility.set_location('Dn Insert: '||l_proc, 5);
525 --
526 -- Call life event trigger
527 --
528 l_old_rec.business_group_id := null;
529 l_old_rec.dpnt_person_id := null;
530 l_old_rec.elig_strt_dt :=null;
531 l_old_rec.elig_thru_dt := null;
532 l_old_rec.dpnt_inelig_flag := null;
533 l_old_rec.ovrdn_thru_dt := null;
534 l_old_rec.ovrdn_flag := null;
535 l_old_rec.create_dt := null;
536 l_new_rec.business_group_id := p_business_group_id;
537 l_new_rec.dpnt_person_id := p_dpnt_person_id;
538 l_new_rec.elig_strt_dt := p_elig_strt_dt;
539 l_new_rec.elig_thru_dt := p_elig_thru_dt;
540 l_new_rec.dpnt_inelig_flag := p_dpnt_inelig_flag;
541 l_new_rec.ovrdn_thru_dt := p_ovrdn_thru_dt;
542 l_new_rec.ovrdn_flag := p_ovrdn_flag;
543 l_new_rec.create_dt := p_create_dt;
544 l_new_rec.per_in_ler_id := p_per_in_ler_id; --Bug 5630251
545 --
546 ben_egd_ler.ler_chk(l_old_rec,l_new_rec,p_effective_date);
547 --
548 ben_egd_shd.g_api_dml := false; -- Unset the api dml status
549 --
550 -- When in validation only mode raise the Validate_Enabled exception
551 --
552 if p_validate then
553 raise hr_api.validate_enabled;
554 end if;
555 --
556 -- Set all output arguments
557 --
558 p_elig_dpnt_id := l_elig_dpnt_id;
559 p_object_version_number := l_object_version_number;
560 --
561 hr_utility.set_location(' Leaving:'||l_proc, 70);
562 --
563 exception
564 --
565 when hr_api.validate_enabled then
566 --
567 -- As the Validate_Enabled exception has been raised
568 -- we must rollback to the savepoint
569 --
570 ROLLBACK TO create_perf_ELIG_DPNT;
571 --
572 -- Only set output warning arguments
573 -- (Any key or derived arguments must be set to null
574 -- when validation only mode is being used.)
575 --
576 p_elig_dpnt_id := null;
577 p_object_version_number := null;
578 hr_utility.set_location(' Leaving:'||l_proc, 80);
579 --
580 when others then
581 --
582 -- A validation or unexpected error has occured
583 --
584 ROLLBACK to create_perf_ELIG_DPNT;
585 raise;
586 --
587 end create_perf_ELIG_DPNT;
588 --
589 -- ----------------------------------------------------------------------------
590 -- |------------------------< update_ELIG_DPNT >--- ------------------|
591 -- ----------------------------------------------------------------------------
592 --
593 procedure update_ELIG_DPNT
594 (p_validate in boolean default false
595 ,p_elig_dpnt_id in number
596 ,p_create_dt in date default hr_api.g_date
597 ,p_elig_strt_dt in date default hr_api.g_date
598 ,p_elig_thru_dt in date default hr_api.g_date
599 ,p_ovrdn_flag in varchar2 default hr_api.g_varchar2
600 ,p_ovrdn_thru_dt in date default hr_api.g_date
601 ,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
602 ,p_dpnt_inelig_flag in varchar2 default hr_api.g_varchar2
603 ,p_elig_per_elctbl_chc_id in number default hr_api.g_number
604 ,p_per_in_ler_id in number default hr_api.g_number
605 ,p_elig_per_id in number default hr_api.g_number
606 ,p_elig_per_opt_id in number default hr_api.g_number
607 ,p_elig_cvrd_dpnt_id in number default hr_api.g_number
608 ,p_dpnt_person_id in number default hr_api.g_number
609 ,p_business_group_id in number default hr_api.g_number
610 ,p_egd_attribute_category in varchar2 default hr_api.g_varchar2
611 ,p_egd_attribute1 in varchar2 default hr_api.g_varchar2
612 ,p_egd_attribute2 in varchar2 default hr_api.g_varchar2
613 ,p_egd_attribute3 in varchar2 default hr_api.g_varchar2
614 ,p_egd_attribute4 in varchar2 default hr_api.g_varchar2
615 ,p_egd_attribute5 in varchar2 default hr_api.g_varchar2
616 ,p_egd_attribute6 in varchar2 default hr_api.g_varchar2
617 ,p_egd_attribute7 in varchar2 default hr_api.g_varchar2
618 ,p_egd_attribute8 in varchar2 default hr_api.g_varchar2
619 ,p_egd_attribute9 in varchar2 default hr_api.g_varchar2
620 ,p_egd_attribute10 in varchar2 default hr_api.g_varchar2
621 ,p_egd_attribute11 in varchar2 default hr_api.g_varchar2
622 ,p_egd_attribute12 in varchar2 default hr_api.g_varchar2
623 ,p_egd_attribute13 in varchar2 default hr_api.g_varchar2
624 ,p_egd_attribute14 in varchar2 default hr_api.g_varchar2
625 ,p_egd_attribute15 in varchar2 default hr_api.g_varchar2
626 ,p_egd_attribute16 in varchar2 default hr_api.g_varchar2
627 ,p_egd_attribute17 in varchar2 default hr_api.g_varchar2
628 ,p_egd_attribute18 in varchar2 default hr_api.g_varchar2
629 ,p_egd_attribute19 in varchar2 default hr_api.g_varchar2
630 ,p_egd_attribute20 in varchar2 default hr_api.g_varchar2
631 ,p_egd_attribute21 in varchar2 default hr_api.g_varchar2
632 ,p_egd_attribute22 in varchar2 default hr_api.g_varchar2
633 ,p_egd_attribute23 in varchar2 default hr_api.g_varchar2
634 ,p_egd_attribute24 in varchar2 default hr_api.g_varchar2
635 ,p_egd_attribute25 in varchar2 default hr_api.g_varchar2
636 ,p_egd_attribute26 in varchar2 default hr_api.g_varchar2
637 ,p_egd_attribute27 in varchar2 default hr_api.g_varchar2
638 ,p_egd_attribute28 in varchar2 default hr_api.g_varchar2
639 ,p_egd_attribute29 in varchar2 default hr_api.g_varchar2
640 ,p_egd_attribute30 in varchar2 default hr_api.g_varchar2
641 ,p_request_id in number default hr_api.g_number
642 ,p_program_application_id in number default hr_api.g_number
643 ,p_program_id in number default hr_api.g_number
644 ,p_program_update_date in date default hr_api.g_date
645 ,p_object_version_number in out nocopy number
646 ,p_effective_date in date
647 ) is
648 --
649 -- Declare cursors and local variables
650 --
651 l_proc varchar2(72) := g_package||'update_ELIG_DPNT';
652 l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
653 --
654 begin
655 --
656 hr_utility.set_location('Entering:'|| l_proc, 10);
657 --
658 -- Issue a savepoint if operating in validation only mode
659 --
660 savepoint update_ELIG_DPNT;
661 --
662 hr_utility.set_location(l_proc, 20);
663 --
664 -- Process Logic
665 --
666 l_object_version_number := p_object_version_number;
667 --
668 begin
669 --
670 -- Start of API User Hook for the before hook of update_ELIG_DPNT
671 --
672 ben_ELIG_DPNT_bk2.update_ELIG_DPNT_b
673 (
674 p_elig_dpnt_id => p_elig_dpnt_id
675 ,p_create_dt => p_create_dt
676 ,p_elig_strt_dt => p_elig_strt_dt
677 ,p_elig_thru_dt => p_elig_thru_dt
678 ,p_ovrdn_flag => p_ovrdn_flag
679 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
680 ,p_inelg_rsn_cd => p_inelg_rsn_cd
681 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
682 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
683 ,p_per_in_ler_id => p_per_in_ler_id
684 ,p_elig_per_id => p_elig_per_id
685 ,p_elig_per_opt_id => p_elig_per_opt_id
686 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
687 ,p_dpnt_person_id => p_dpnt_person_id
688 ,p_business_group_id => p_business_group_id
689 ,p_egd_attribute_category => p_egd_attribute_category
690 ,p_egd_attribute1 => p_egd_attribute1
691 ,p_egd_attribute2 => p_egd_attribute2
692 ,p_egd_attribute3 => p_egd_attribute3
693 ,p_egd_attribute4 => p_egd_attribute4
694 ,p_egd_attribute5 => p_egd_attribute5
695 ,p_egd_attribute6 => p_egd_attribute6
696 ,p_egd_attribute7 => p_egd_attribute7
697 ,p_egd_attribute8 => p_egd_attribute8
698 ,p_egd_attribute9 => p_egd_attribute9
699 ,p_egd_attribute10 => p_egd_attribute10
700 ,p_egd_attribute11 => p_egd_attribute11
701 ,p_egd_attribute12 => p_egd_attribute12
702 ,p_egd_attribute13 => p_egd_attribute13
703 ,p_egd_attribute14 => p_egd_attribute14
704 ,p_egd_attribute15 => p_egd_attribute15
705 ,p_egd_attribute16 => p_egd_attribute16
706 ,p_egd_attribute17 => p_egd_attribute17
707 ,p_egd_attribute18 => p_egd_attribute18
708 ,p_egd_attribute19 => p_egd_attribute19
709 ,p_egd_attribute20 => p_egd_attribute20
710 ,p_egd_attribute21 => p_egd_attribute21
711 ,p_egd_attribute22 => p_egd_attribute22
712 ,p_egd_attribute23 => p_egd_attribute23
713 ,p_egd_attribute24 => p_egd_attribute24
714 ,p_egd_attribute25 => p_egd_attribute25
715 ,p_egd_attribute26 => p_egd_attribute26
716 ,p_egd_attribute27 => p_egd_attribute27
717 ,p_egd_attribute28 => p_egd_attribute28
718 ,p_egd_attribute29 => p_egd_attribute29
719 ,p_egd_attribute30 => p_egd_attribute30
720 ,p_request_id => p_request_id
721 ,p_program_application_id => p_program_application_id
722 ,p_program_id => p_program_id
723 ,p_program_update_date => p_program_update_date
724 ,p_object_version_number => p_object_version_number
725 ,p_effective_date => trunc(p_effective_date)
726 );
727 exception
728 when hr_api.cannot_find_prog_unit then
729 hr_api.cannot_find_prog_unit_error
730 (p_module_name => 'UPDATE_ELIG_DPNT'
731 ,p_hook_type => 'BP'
732 );
733 --
734 -- End of API User Hook for the before hook of update_ELIG_DPNT
735 --
736 end;
737 --
738 ben_egd_upd.upd
739 (
740 p_elig_dpnt_id => p_elig_dpnt_id
741 ,p_create_dt => p_create_dt
742 ,p_elig_strt_dt => p_elig_strt_dt
743 ,p_elig_thru_dt => p_elig_thru_dt
744 ,p_ovrdn_flag => p_ovrdn_flag
745 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
746 ,p_inelg_rsn_cd => p_inelg_rsn_cd
747 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
748 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
749 ,p_per_in_ler_id => p_per_in_ler_id
750 ,p_elig_per_id => p_elig_per_id
751 ,p_elig_per_opt_id => p_elig_per_opt_id
752 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
753 ,p_dpnt_person_id => p_dpnt_person_id
754 ,p_business_group_id => p_business_group_id
755 ,p_egd_attribute_category => p_egd_attribute_category
756 ,p_egd_attribute1 => p_egd_attribute1
757 ,p_egd_attribute2 => p_egd_attribute2
758 ,p_egd_attribute3 => p_egd_attribute3
759 ,p_egd_attribute4 => p_egd_attribute4
760 ,p_egd_attribute5 => p_egd_attribute5
761 ,p_egd_attribute6 => p_egd_attribute6
762 ,p_egd_attribute7 => p_egd_attribute7
763 ,p_egd_attribute8 => p_egd_attribute8
764 ,p_egd_attribute9 => p_egd_attribute9
765 ,p_egd_attribute10 => p_egd_attribute10
766 ,p_egd_attribute11 => p_egd_attribute11
767 ,p_egd_attribute12 => p_egd_attribute12
768 ,p_egd_attribute13 => p_egd_attribute13
769 ,p_egd_attribute14 => p_egd_attribute14
770 ,p_egd_attribute15 => p_egd_attribute15
771 ,p_egd_attribute16 => p_egd_attribute16
772 ,p_egd_attribute17 => p_egd_attribute17
773 ,p_egd_attribute18 => p_egd_attribute18
774 ,p_egd_attribute19 => p_egd_attribute19
775 ,p_egd_attribute20 => p_egd_attribute20
776 ,p_egd_attribute21 => p_egd_attribute21
777 ,p_egd_attribute22 => p_egd_attribute22
778 ,p_egd_attribute23 => p_egd_attribute23
779 ,p_egd_attribute24 => p_egd_attribute24
780 ,p_egd_attribute25 => p_egd_attribute25
781 ,p_egd_attribute26 => p_egd_attribute26
782 ,p_egd_attribute27 => p_egd_attribute27
783 ,p_egd_attribute28 => p_egd_attribute28
784 ,p_egd_attribute29 => p_egd_attribute29
785 ,p_egd_attribute30 => p_egd_attribute30
786 ,p_request_id => p_request_id
787 ,p_program_application_id => p_program_application_id
788 ,p_program_id => p_program_id
789 ,p_program_update_date => p_program_update_date
790 ,p_object_version_number => l_object_version_number
791 ,p_effective_date => trunc(p_effective_date)
792 );
793 --
794 begin
795 --
796 -- Start of API User Hook for the after hook of update_ELIG_DPNT
797 --
798 ben_ELIG_DPNT_bk2.update_ELIG_DPNT_a
799 (
800 p_elig_dpnt_id => p_elig_dpnt_id
801 ,p_create_dt => p_create_dt
802 ,p_elig_strt_dt => p_elig_strt_dt
803 ,p_elig_thru_dt => p_elig_thru_dt
804 ,p_ovrdn_flag => p_ovrdn_flag
805 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
806 ,p_inelg_rsn_cd => p_inelg_rsn_cd
807 ,p_dpnt_inelig_flag => p_dpnt_inelig_flag
808 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
809 ,p_per_in_ler_id => p_per_in_ler_id
810 ,p_elig_per_id => p_elig_per_id
811 ,p_elig_per_opt_id => p_elig_per_opt_id
812 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
813 ,p_dpnt_person_id => p_dpnt_person_id
814 ,p_business_group_id => p_business_group_id
815 ,p_egd_attribute_category => p_egd_attribute_category
816 ,p_egd_attribute1 => p_egd_attribute1
817 ,p_egd_attribute2 => p_egd_attribute2
818 ,p_egd_attribute3 => p_egd_attribute3
819 ,p_egd_attribute4 => p_egd_attribute4
820 ,p_egd_attribute5 => p_egd_attribute5
821 ,p_egd_attribute6 => p_egd_attribute6
822 ,p_egd_attribute7 => p_egd_attribute7
823 ,p_egd_attribute8 => p_egd_attribute8
824 ,p_egd_attribute9 => p_egd_attribute9
825 ,p_egd_attribute10 => p_egd_attribute10
826 ,p_egd_attribute11 => p_egd_attribute11
827 ,p_egd_attribute12 => p_egd_attribute12
828 ,p_egd_attribute13 => p_egd_attribute13
829 ,p_egd_attribute14 => p_egd_attribute14
830 ,p_egd_attribute15 => p_egd_attribute15
831 ,p_egd_attribute16 => p_egd_attribute16
832 ,p_egd_attribute17 => p_egd_attribute17
833 ,p_egd_attribute18 => p_egd_attribute18
834 ,p_egd_attribute19 => p_egd_attribute19
835 ,p_egd_attribute20 => p_egd_attribute20
836 ,p_egd_attribute21 => p_egd_attribute21
837 ,p_egd_attribute22 => p_egd_attribute22
838 ,p_egd_attribute23 => p_egd_attribute23
839 ,p_egd_attribute24 => p_egd_attribute24
840 ,p_egd_attribute25 => p_egd_attribute25
841 ,p_egd_attribute26 => p_egd_attribute26
842 ,p_egd_attribute27 => p_egd_attribute27
843 ,p_egd_attribute28 => p_egd_attribute28
844 ,p_egd_attribute29 => p_egd_attribute29
845 ,p_egd_attribute30 => p_egd_attribute30
846 ,p_request_id => p_request_id
847 ,p_program_application_id => p_program_application_id
848 ,p_program_id => p_program_id
849 ,p_program_update_date => p_program_update_date
850 ,p_object_version_number => l_object_version_number
851 ,p_effective_date => trunc(p_effective_date)
852 );
853 exception
854 when hr_api.cannot_find_prog_unit then
855 hr_api.cannot_find_prog_unit_error
856 (p_module_name => 'UPDATE_ELIG_DPNT'
857 ,p_hook_type => 'AP'
858 );
859 --
860 -- End of API User Hook for the after hook of update_ELIG_DPNT
861 --
862 end;
863 --
864 hr_utility.set_location(l_proc, 60);
865 --
866 -- When in validation only mode raise the Validate_Enabled exception
867 --
868 if p_validate then
869 raise hr_api.validate_enabled;
870 end if;
871 --
872 -- Set all output arguments
873 --
874 p_object_version_number := l_object_version_number;
875 --
876 hr_utility.set_location(' Leaving:'||l_proc, 70);
877 --
878 exception
879 --
880 when hr_api.validate_enabled then
881 --
882 -- As the Validate_Enabled exception has been raised
883 -- we must rollback to the savepoint
884 --
885 ROLLBACK TO update_ELIG_DPNT;
886 --
887 -- Only set output warning arguments
888 -- (Any key or derived arguments must be set to null
889 -- when validation only mode is being used.)
890 --
891 hr_utility.set_location(' Leaving:'||l_proc, 80);
892 --
893 when others then
894 --
895 -- A validation or unexpected error has occured
896 --
897 ROLLBACK TO update_ELIG_DPNT;
898 /* Inserted for nocopy changes */
899 p_object_version_number := l_object_version_number;
900 raise;
901 --
902 end update_ELIG_DPNT;
903 --
904 -- ----------------------------------------------------------------------------
905 -- |------------------------< update_perf_ELIG_DPNT >--- ------------------|
906 -- ----------------------------------------------------------------------------
907 --
908 procedure update_perf_ELIG_DPNT
909 (
910 p_validate in boolean default false
911 ,p_elig_dpnt_id in number
912 ,p_create_dt in date default hr_api.g_date
913 ,p_elig_strt_dt in date default hr_api.g_date
914 ,p_elig_thru_dt in date default hr_api.g_date
915 ,p_ovrdn_flag in varchar2 default hr_api.g_varchar2
916 ,p_ovrdn_thru_dt in date default hr_api.g_date
917 ,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
918 ,p_dpnt_inelig_flag in varchar2 default hr_api.g_varchar2
919 ,p_elig_per_elctbl_chc_id in number default hr_api.g_number
920 ,p_per_in_ler_id in number default hr_api.g_number
921 ,p_elig_per_id in number default hr_api.g_number
922 ,p_elig_per_opt_id in number default hr_api.g_number
923 ,p_elig_cvrd_dpnt_id in number default hr_api.g_number
924 ,p_dpnt_person_id in number default hr_api.g_number
925 ,p_business_group_id in number default hr_api.g_number
926 ,p_egd_attribute_category in varchar2 default hr_api.g_varchar2
927 ,p_egd_attribute1 in varchar2 default hr_api.g_varchar2
928 ,p_egd_attribute2 in varchar2 default hr_api.g_varchar2
929 ,p_egd_attribute3 in varchar2 default hr_api.g_varchar2
930 ,p_egd_attribute4 in varchar2 default hr_api.g_varchar2
931 ,p_egd_attribute5 in varchar2 default hr_api.g_varchar2
932 ,p_egd_attribute6 in varchar2 default hr_api.g_varchar2
933 ,p_egd_attribute7 in varchar2 default hr_api.g_varchar2
934 ,p_egd_attribute8 in varchar2 default hr_api.g_varchar2
935 ,p_egd_attribute9 in varchar2 default hr_api.g_varchar2
936 ,p_egd_attribute10 in varchar2 default hr_api.g_varchar2
937 ,p_egd_attribute11 in varchar2 default hr_api.g_varchar2
938 ,p_egd_attribute12 in varchar2 default hr_api.g_varchar2
939 ,p_egd_attribute13 in varchar2 default hr_api.g_varchar2
940 ,p_egd_attribute14 in varchar2 default hr_api.g_varchar2
941 ,p_egd_attribute15 in varchar2 default hr_api.g_varchar2
942 ,p_egd_attribute16 in varchar2 default hr_api.g_varchar2
943 ,p_egd_attribute17 in varchar2 default hr_api.g_varchar2
944 ,p_egd_attribute18 in varchar2 default hr_api.g_varchar2
945 ,p_egd_attribute19 in varchar2 default hr_api.g_varchar2
946 ,p_egd_attribute20 in varchar2 default hr_api.g_varchar2
947 ,p_egd_attribute21 in varchar2 default hr_api.g_varchar2
948 ,p_egd_attribute22 in varchar2 default hr_api.g_varchar2
949 ,p_egd_attribute23 in varchar2 default hr_api.g_varchar2
950 ,p_egd_attribute24 in varchar2 default hr_api.g_varchar2
951 ,p_egd_attribute25 in varchar2 default hr_api.g_varchar2
952 ,p_egd_attribute26 in varchar2 default hr_api.g_varchar2
953 ,p_egd_attribute27 in varchar2 default hr_api.g_varchar2
954 ,p_egd_attribute28 in varchar2 default hr_api.g_varchar2
955 ,p_egd_attribute29 in varchar2 default hr_api.g_varchar2
956 ,p_egd_attribute30 in varchar2 default hr_api.g_varchar2
957 ,p_request_id in number default hr_api.g_number
958 ,p_program_application_id in number default hr_api.g_number
959 ,p_program_id in number default hr_api.g_number
960 ,p_program_update_date in date default hr_api.g_date
961 ,p_object_version_number in out nocopy number
962 ,p_effective_date in date
963 )
964 is
965 --
966 -- Declare cursors and local variables
967 --
968 l_proc varchar2(72) := g_package||'update_perf_ELIG_DPNT';
969 --
970 l_old_rec ben_egd_ler.g_egd_ler_rec;
971 l_new_rec ben_egd_ler.g_egd_ler_rec;
972 --
973 l_rec ben_egd_shd.g_rec_type;
974 l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
975 --
976 begin
977 --
978 hr_utility.set_location('Entering:'|| l_proc, 10);
979 --
980 -- Issue a savepoint if operating in validation only mode
981 --
982 savepoint update_perf_ELIG_DPNT;
983 --
984 l_rec :=
985 ben_egd_shd.convert_args
986 (
987 p_elig_dpnt_id,
988 p_create_dt,
989 p_elig_strt_dt,
990 p_elig_thru_dt,
991 p_ovrdn_flag,
992 p_ovrdn_thru_dt,
993 p_inelg_rsn_cd,
994 p_dpnt_inelig_flag,
995 p_elig_per_elctbl_chc_id,
996 p_per_in_ler_id,
997 p_elig_per_id,
998 p_elig_per_opt_id,
999 p_elig_cvrd_dpnt_id,
1000 p_dpnt_person_id,
1001 p_business_group_id,
1002 p_egd_attribute_category,
1003 p_egd_attribute1,
1004 p_egd_attribute2,
1005 p_egd_attribute3,
1006 p_egd_attribute4,
1007 p_egd_attribute5,
1008 p_egd_attribute6,
1009 p_egd_attribute7,
1010 p_egd_attribute8,
1011 p_egd_attribute9,
1012 p_egd_attribute10,
1013 p_egd_attribute11,
1014 p_egd_attribute12,
1015 p_egd_attribute13,
1016 p_egd_attribute14,
1017 p_egd_attribute15,
1018 p_egd_attribute16,
1019 p_egd_attribute17,
1020 p_egd_attribute18,
1021 p_egd_attribute19,
1022 p_egd_attribute20,
1023 p_egd_attribute21,
1024 p_egd_attribute22,
1025 p_egd_attribute23,
1026 p_egd_attribute24,
1027 p_egd_attribute25,
1028 p_egd_attribute26,
1029 p_egd_attribute27,
1030 p_egd_attribute28,
1031 p_egd_attribute29,
1032 p_egd_attribute30,
1033 p_request_id,
1034 p_program_application_id,
1035 p_program_id,
1036 p_program_update_date,
1037 p_object_version_number
1038 );
1039 --
1040 -- We must lock the row which we need to update.
1041 --
1042 ben_egd_shd.lck
1043 (p_elig_dpnt_id => p_elig_dpnt_id
1044 ,p_object_version_number => p_object_version_number
1045 );
1046 --
1047 ben_egd_upd.convert_defs(p_rec => l_rec);
1048 --
1049 -- Increment object version number
1050 --
1051 l_object_version_number := p_object_version_number+1;
1052 ben_egd_shd.g_api_dml := true; -- Set the api dml status
1053 --
1054 -- Update the ben_elig_dpnt Row
1055 --
1056 update ben_elig_dpnt
1057 set
1058 elig_dpnt_id = l_rec.elig_dpnt_id,
1059 create_dt = l_rec.create_dt,
1060 elig_strt_dt = l_rec.elig_strt_dt,
1061 elig_thru_dt = l_rec.elig_thru_dt,
1062 ovrdn_flag = l_rec.ovrdn_flag,
1063 ovrdn_thru_dt = l_rec.ovrdn_thru_dt,
1064 inelg_rsn_cd = l_rec.inelg_rsn_cd,
1065 dpnt_inelig_flag = l_rec.dpnt_inelig_flag,
1066 elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id,
1067 per_in_ler_id = l_rec.per_in_ler_id,
1068 elig_per_id = l_rec.elig_per_id,
1069 elig_per_opt_id = l_rec.elig_per_opt_id,
1070 elig_cvrd_dpnt_id = l_rec.elig_cvrd_dpnt_id,
1071 dpnt_person_id = l_rec.dpnt_person_id,
1072 business_group_id = l_rec.business_group_id,
1073 egd_attribute_category = l_rec.egd_attribute_category,
1074 egd_attribute1 = l_rec.egd_attribute1,
1075 egd_attribute2 = l_rec.egd_attribute2,
1076 egd_attribute3 = l_rec.egd_attribute3,
1077 egd_attribute4 = l_rec.egd_attribute4,
1078 egd_attribute5 = l_rec.egd_attribute5,
1079 egd_attribute6 = l_rec.egd_attribute6,
1080 egd_attribute7 = l_rec.egd_attribute7,
1081 egd_attribute8 = l_rec.egd_attribute8,
1082 egd_attribute9 = l_rec.egd_attribute9,
1083 egd_attribute10 = l_rec.egd_attribute10,
1084 egd_attribute11 = l_rec.egd_attribute11,
1085 egd_attribute12 = l_rec.egd_attribute12,
1086 egd_attribute13 = l_rec.egd_attribute13,
1087 egd_attribute14 = l_rec.egd_attribute14,
1088 egd_attribute15 = l_rec.egd_attribute15,
1089 egd_attribute16 = l_rec.egd_attribute16,
1090 egd_attribute17 = l_rec.egd_attribute17,
1091 egd_attribute18 = l_rec.egd_attribute18,
1092 egd_attribute19 = l_rec.egd_attribute19,
1093 egd_attribute20 = l_rec.egd_attribute20,
1094 egd_attribute21 = l_rec.egd_attribute21,
1095 egd_attribute22 = l_rec.egd_attribute22,
1096 egd_attribute23 = l_rec.egd_attribute23,
1097 egd_attribute24 = l_rec.egd_attribute24,
1098 egd_attribute25 = l_rec.egd_attribute25,
1099 egd_attribute26 = l_rec.egd_attribute26,
1100 egd_attribute27 = l_rec.egd_attribute27,
1101 egd_attribute28 = l_rec.egd_attribute28,
1102 egd_attribute29 = l_rec.egd_attribute29,
1103 egd_attribute30 = l_rec.egd_attribute30,
1104 request_id = l_rec.request_id,
1105 program_application_id = l_rec.program_application_id,
1106 program_id = l_rec.program_id,
1107 program_update_date = l_rec.program_update_date,
1108 object_version_number = l_rec.object_version_number
1109 where elig_dpnt_id = l_rec.elig_dpnt_id;
1110 --
1111 -- Call life event trigger
1112 --
1113 l_old_rec.business_group_id := ben_egd_shd.g_old_rec.business_group_id;
1114 l_old_rec.dpnt_person_id := ben_egd_shd.g_old_rec.dpnt_person_id;
1115 l_old_rec.elig_strt_dt := ben_egd_shd.g_old_rec.elig_strt_dt;
1116 l_old_rec.elig_thru_dt := ben_egd_shd.g_old_rec.elig_thru_dt;
1117 l_old_rec.dpnt_inelig_flag := ben_egd_shd.g_old_rec.dpnt_inelig_flag;
1118 l_old_rec.ovrdn_thru_dt := ben_egd_shd.g_old_rec.ovrdn_thru_dt;
1119 l_old_rec.ovrdn_flag := ben_egd_shd.g_old_rec.ovrdn_flag;
1120 l_old_rec.create_dt := ben_egd_shd.g_old_rec.create_dt;
1121 l_old_rec.per_in_ler_id := ben_egd_shd.g_old_rec.per_in_ler_id;
1122
1123
1124 l_new_rec.business_group_id := p_business_group_id;
1125 l_new_rec.dpnt_person_id := p_dpnt_person_id;
1126 l_new_rec.elig_strt_dt := p_elig_strt_dt;
1127 l_new_rec.elig_thru_dt := p_elig_thru_dt;
1128 l_new_rec.dpnt_inelig_flag := p_dpnt_inelig_flag;
1129 l_new_rec.ovrdn_thru_dt := p_ovrdn_thru_dt;
1130 l_new_rec.ovrdn_flag := p_ovrdn_flag;
1131 l_new_rec.create_dt := p_create_dt;
1132 l_new_rec.per_in_ler_id := p_per_in_ler_id; --Bug 5630251
1133 --
1134 hr_utility.set_location(' Old per_in_ler_id from api ' || l_old_rec.per_in_ler_id , 9876 );
1135
1136 ben_egd_ler.ler_chk(l_old_rec,l_new_rec,p_effective_date);
1137 --
1138 ben_egd_shd.g_api_dml := false; -- Unset the api dml status
1139 --
1140 -- When in validation only mode raise the Validate_Enabled exception
1141 --
1142 if p_validate then
1143 raise hr_api.validate_enabled;
1144 end if;
1145 --
1146 -- Set all output arguments
1147 --
1148 p_object_version_number := l_object_version_number;
1149 --
1150 hr_utility.set_location(' Leaving:'||l_proc, 70);
1151 --
1152 exception
1153 --
1154 when hr_api.validate_enabled then
1155 --
1156 -- As the Validate_Enabled exception has been raised
1157 -- we must rollback to the savepoint
1158 --
1159 ROLLBACK TO update_perf_ELIG_DPNT;
1160 --
1161 -- Only set output warning arguments
1162 -- (Any key or derived arguments must be set to null
1163 -- when validation only mode is being used.)
1164 --
1165 hr_utility.set_location(' Leaving:'||l_proc, 80);
1166 --
1167 when others then
1168 --
1169 -- A validation or unexpected error has occured
1170 --
1171 ROLLBACK TO update_perf_ELIG_DPNT;
1172 /* Inserted for nocopy changes */
1173 p_object_version_number := l_object_version_number;
1174 raise;
1175 --
1176 end update_perf_ELIG_DPNT;
1177 -- ----------------------------------------------------------------------------
1178 -- |------------------------< delete_ELIG_DPNT >----------------------|
1179 -- ----------------------------------------------------------------------------
1180 --
1181 procedure delete_ELIG_DPNT
1182 (p_validate in boolean default false
1183 ,p_elig_dpnt_id in number
1184 ,p_object_version_number in out nocopy number
1185 ,p_effective_date in date
1186 ) is
1187 --
1188 -- Declare cursors and local variables
1189 --
1190 l_proc varchar2(72) := g_package||'update_ELIG_DPNT';
1191 l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
1192 --
1193 begin
1194 --
1195 hr_utility.set_location('Entering:'|| l_proc, 10);
1196 --
1197 -- Issue a savepoint if operating in validation only mode
1198 --
1199 savepoint delete_ELIG_DPNT;
1200 --
1201 hr_utility.set_location(l_proc, 20);
1202 --
1203 -- Process Logic
1204 --
1205 l_object_version_number := p_object_version_number;
1206 --
1207 --
1208 begin
1209 --
1210 -- Start of API User Hook for the before hook of delete_ELIG_DPNT
1211 --
1212 ben_ELIG_DPNT_bk3.delete_ELIG_DPNT_b
1213 (
1214 p_elig_dpnt_id => p_elig_dpnt_id
1215 ,p_object_version_number => p_object_version_number
1216 ,p_effective_date => trunc(p_effective_date)
1217 );
1218 exception
1219 when hr_api.cannot_find_prog_unit then
1220 hr_api.cannot_find_prog_unit_error
1221 (p_module_name => 'DELETE_ELIG_DPNT'
1222 ,p_hook_type => 'BP'
1223 );
1224 --
1225 -- End of API User Hook for the before hook of delete_ELIG_DPNT
1226 --
1227 end;
1228 --
1229 ben_egd_del.del
1230 (
1231 p_elig_dpnt_id => p_elig_dpnt_id
1232 ,p_object_version_number => l_object_version_number
1233 ,p_effective_date => p_effective_date
1234 );
1235 --
1236 begin
1237 --
1238 -- Start of API User Hook for the after hook of delete_ELIG_DPNT
1239 --
1240 ben_ELIG_DPNT_bk3.delete_ELIG_DPNT_a
1241 (
1242 p_elig_dpnt_id => p_elig_dpnt_id
1243 ,p_object_version_number => l_object_version_number
1244 ,p_effective_date => trunc(p_effective_date)
1245 );
1246 exception
1247 when hr_api.cannot_find_prog_unit then
1248 hr_api.cannot_find_prog_unit_error
1249 (p_module_name => 'DELETE_ELIG_DPNT'
1250 ,p_hook_type => 'AP'
1251 );
1252 --
1253 -- End of API User Hook for the after hook of delete_ELIG_DPNT
1254 --
1255 end;
1256 --
1257 hr_utility.set_location(l_proc, 60);
1258 --
1259 -- When in validation only mode raise the Validate_Enabled exception
1260 --
1261 if p_validate then
1262 raise hr_api.validate_enabled;
1263 end if;
1264 --
1265 hr_utility.set_location(' Leaving:'||l_proc, 70);
1266 --
1267 exception
1268 --
1269 when hr_api.validate_enabled then
1270 --
1271 -- As the Validate_Enabled exception has been raised
1272 -- we must rollback to the savepoint
1273 --
1274 ROLLBACK TO delete_ELIG_DPNT;
1275 --
1276 -- Only set output warning arguments
1277 -- (Any key or derived arguments must be set to null
1278 -- when validation only mode is being used.)
1279 --
1280 --
1281 when others then
1282 --
1283 -- A validation or unexpected error has occured
1284 --
1285 ROLLBACK TO delete_ELIG_DPNT;
1286 /* Inserted for nocopy changes */
1287 p_object_version_number := l_object_version_number;
1288 raise;
1289 --
1290 end delete_ELIG_DPNT;
1291 --
1292 -- ----------------------------------------------------------------------------
1293 -- |-------------------------------< lck >------------------------------------|
1294 -- ----------------------------------------------------------------------------
1295 --
1296 procedure lck
1297 (
1298 p_elig_dpnt_id in number
1299 ,p_object_version_number in number
1300 ) is
1301 --
1302 --
1303 -- Declare cursors and local variables
1304 --
1305 l_proc varchar2(72) := g_package||'lck';
1306 --
1307 begin
1308 --
1309 hr_utility.set_location('Entering:'|| l_proc, 10);
1310 --
1311 ben_egd_shd.lck
1312 (
1313 p_elig_dpnt_id => p_elig_dpnt_id
1314 ,p_object_version_number => p_object_version_number
1315 );
1316 --
1317 hr_utility.set_location(' Leaving:'||l_proc, 70);
1318 --
1319 end lck;
1320 --
1321 -- ----------------------------------------------------------------------------
1322 -- |---------------------< return_sspnd_if_interim >--------------------------|
1323 -- ----------------------------------------------------------------------------
1324 -- Bug 5486397
1325 function return_sspnd_if_interim (p_interim_pen_id NUMBER,
1326 p_effective_date DATE)
1327 return number
1328 is
1329 --
1330 CURSOR c_is_pen_interim (cv_prtt_enrt_rslt_id NUMBER,
1331 cv_effective_date DATE )
1332 IS
1333 SELECT sspndd.prtt_enrt_rslt_id
1334 FROM ben_prtt_enrt_rslt_f sspndd, ben_prtt_enrt_rslt_f inter
1335 WHERE inter.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
1336 AND sspndd.rplcs_sspndd_rslt_id = inter.prtt_enrt_rslt_id
1337 AND sspndd.pl_id = inter.pl_id
1338 AND NVL (sspndd.pgm_id, -1) = NVL (inter.pgm_id, -1)
1339 AND sspndd.pl_id = inter.pl_id
1340 AND NVL (sspndd.oipl_id, -1) = NVL (inter.oipl_id, -1)
1341 AND sspndd.prtt_enrt_rslt_stat_cd IS NULL
1342 AND inter.prtt_enrt_rslt_stat_cd IS NULL
1343 AND inter.enrt_cvg_thru_dt = hr_api.g_eot
1344 AND sspndd.enrt_cvg_thru_dt = hr_api.g_eot
1345 and cv_effective_date between sspndd.effective_start_date
1346 and sspndd.effective_end_date
1347 and cv_effective_date between inter.effective_start_date
1348 and inter.effective_end_date;
1349
1350 --
1351 l_sspndd_pen_id number(30);
1352 --
1353 begin
1354 --
1355 -- Similar code exists in BENEDSGB.fmb >> EPE.POST-QUERY. Make changes
1356 -- at both the places
1357 hr_utility.set_location('Entering return_sspnd_if_interim', 5);
1358 open c_is_pen_interim (p_interim_pen_id, p_effective_date);
1359 --
1360 fetch c_is_pen_interim into l_sspndd_pen_id;
1361 --
1362 if c_is_pen_interim%found
1363 then
1364 --
1365 null;
1366 --
1367 else
1368 --
1369 l_sspndd_pen_id := p_interim_pen_id;
1370 --
1371 end if;
1372 --
1373 close c_is_pen_interim;
1374 --
1375 hr_utility.set_location('l_sspndd_pen_id = ' || l_sspndd_pen_id, 15);
1376 hr_utility.set_location('Leaving return_sspnd_if_interim', 10);
1377 return l_sspndd_pen_id;
1378 --
1379 end return_sspnd_if_interim;
1380 --
1381 -- Procedure: process_dependent.
1382 -- Purpose : Used to create/update covered dependent (elig_cvrd_dpnt) record
1383 -- based on eligible dependent record (elig_dpnt).
1384 -- Called from: Called from Dependents form and manage dpnts.(8/25/99)
1385 --
1386 procedure process_dependent(p_validate in boolean default false,
1387 p_elig_dpnt_id in number,
1388 p_business_group_id in number,
1389 p_effective_date in date,
1390 p_cvg_strt_dt in date,
1391 p_cvg_thru_dt in date,
1392 p_datetrack_mode in varchar2,
1393 p_pdp_attribute_category in varchar2 default null,
1394 p_pdp_attribute1 in varchar2 default null,
1395 p_pdp_attribute2 in varchar2 default null,
1396 p_pdp_attribute3 in varchar2 default null,
1397 p_pdp_attribute4 in varchar2 default null,
1398 p_pdp_attribute5 in varchar2 default null,
1399 p_pdp_attribute6 in varchar2 default null,
1400 p_pdp_attribute7 in varchar2 default null,
1401 p_pdp_attribute8 in varchar2 default null,
1402 p_pdp_attribute9 in varchar2 default null,
1403 p_pdp_attribute10 in varchar2 default null,
1404 p_pdp_attribute11 in varchar2 default null,
1405 p_pdp_attribute12 in varchar2 default null,
1406 p_pdp_attribute13 in varchar2 default null,
1407 p_pdp_attribute14 in varchar2 default null,
1408 p_pdp_attribute15 in varchar2 default null,
1409 p_pdp_attribute16 in varchar2 default null,
1410 p_pdp_attribute17 in varchar2 default null,
1411 p_pdp_attribute18 in varchar2 default null,
1412 p_pdp_attribute19 in varchar2 default null,
1413 p_pdp_attribute20 in varchar2 default null,
1414 p_pdp_attribute21 in varchar2 default null,
1415 p_pdp_attribute22 in varchar2 default null,
1416 p_pdp_attribute23 in varchar2 default null,
1417 p_pdp_attribute24 in varchar2 default null,
1418 p_pdp_attribute25 in varchar2 default null,
1419 p_pdp_attribute26 in varchar2 default null,
1420 p_pdp_attribute27 in varchar2 default null,
1421 p_pdp_attribute28 in varchar2 default null,
1422 p_pdp_attribute29 in varchar2 default null,
1423 p_pdp_attribute30 in varchar2 default null,
1424 p_elig_cvrd_dpnt_id out nocopy number,
1425 p_effective_start_date out nocopy date,
1426 p_effective_end_date out nocopy date,
1427 p_object_version_number in out nocopy number
1428 ,p_multi_row_actn in BOOLEAN default FALSE) is
1429 --
1430 cursor c_egd is
1431 select egd.elig_dpnt_id,
1432 egd.elig_cvrd_dpnt_id,
1433 egd.elig_per_elctbl_chc_id,
1434 egd.dpnt_person_id,
1435 egd.per_in_ler_id,
1436 epe.prtt_enrt_rslt_id,
1437 egd.object_version_number
1438 from ben_elig_dpnt egd,
1439 ben_elig_per_elctbl_chc epe,
1440 ben_per_in_ler pil
1441 where egd.elig_dpnt_id = p_elig_dpnt_id
1442 and egd.business_group_id = p_business_group_id
1443 and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1444 and egd.per_in_ler_id = pil.per_in_ler_id
1445 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
1446 --
1447 l_egd_rec c_egd%rowtype;
1448 --
1449 cursor c_pdp is
1450 select pdp.cvg_strt_dt,
1451 pdp.cvg_thru_dt,
1452 pdp.object_version_number,
1453 pdp.effective_start_date
1454 from ben_elig_cvrd_dpnt_f pdp
1455 where pdp.elig_cvrd_dpnt_id = l_egd_rec.elig_cvrd_dpnt_id
1456 and pdp.prtt_enrt_rslt_id = l_egd_rec.prtt_enrt_rslt_id
1457 and pdp.business_group_id = p_business_group_id
1458 and p_effective_date between
1459 pdp.effective_start_date and pdp.effective_end_date;
1460 --
1461 l_pdp_rec c_pdp%rowtype;
1462 l_egd_found boolean := false;
1463 l_pdp_found boolean := false;
1464 l_pdp_datetrack_mode varchar2(30);
1465 l_cvg_thru_dt date;
1466 l_effective_start_date date;
1467 l_effective_end_date date;
1468 l_object_version_number number := p_object_version_number;
1469 l_proc varchar2(72) := g_package||'.process_dependent';
1470 --
1471 begin
1472 --
1473 hr_utility.set_location('Entering:'|| l_proc, 10);
1474 --
1475 -- Issue a savepoint if operating in validation only mode
1476 --
1477 savepoint process_dependent;
1478 --
1479 if fnd_global.conc_request_id = -1 then
1480 --
1481 ben_env_object.init(p_business_group_id => p_business_group_id,
1482 p_effective_date => p_effective_date,
1483 p_thread_id => 1,
1484 p_chunk_size => 1,
1485 p_threads => 1,
1486 p_max_errors => 1,
1487 p_benefit_action_id => null);
1488 --
1489 end if;
1490 --
1491 hr_utility.set_location(l_proc, 15);
1492 --
1493 open c_egd;
1494 fetch c_egd into l_egd_rec;
1495 if c_egd%found then
1496 l_egd_found := true;
1497 end if;
1498 close c_egd;
1499 --
1500 -- Bug 5486397
1501 l_egd_rec.prtt_enrt_rslt_id := return_sspnd_if_interim (l_egd_rec.prtt_enrt_rslt_id, p_effective_date);
1502 --
1503 hr_utility.set_location(l_proc, 20);
1504 --
1505 if l_egd_rec.elig_cvrd_dpnt_id is not null then
1506 --
1507 hr_utility.set_location(l_proc, 25);
1508 --
1509 open c_pdp;
1510 fetch c_pdp into l_pdp_rec;
1511 if c_pdp%found then
1512 --
1513 hr_utility.set_location(l_proc, 30);
1514 --
1515 l_pdp_found := true;
1516 --
1517 if l_pdp_rec.effective_start_date = p_effective_date then
1518 --
1519 l_pdp_datetrack_mode := hr_api.g_correction;
1520 --
1521 else
1522 --
1523 l_pdp_datetrack_mode := p_datetrack_mode;
1524 --
1525 end if;
1526 --
1527 end if;
1528 --
1529 close c_pdp;
1530 --
1531 hr_utility.set_location(l_proc, 35);
1532 --
1533 end if;
1534 --
1535 -- Note: We are making coverge through date as EOT, if it is null.
1536 -- Take note of this, when looking for cases.
1537 --
1538 if p_cvg_thru_dt is null then
1539 --
1540 l_cvg_thru_dt := hr_api.g_eot;
1541 --
1542 else
1543 --
1544 l_cvg_thru_dt := p_cvg_thru_dt;
1545 --
1546 end if;
1547 --
1548 hr_utility.set_location(l_proc, 40);
1549 --
1550 -- Cases start. Each case has their own if statement.
1551 --
1552 if not l_egd_found then
1553 --
1554 -- Eligible dependent record not found.
1555 -- ERROR out.
1556 --
1557 hr_utility.set_location(l_proc, 45);
1558 --
1559 fnd_message.set_name('BEN','BEN_92322_EGD_NOT_FOUND');
1560 fnd_message.raise_error;
1561 --
1562 elsif l_cvg_thru_dt <> hr_api.g_eot and not l_pdp_found then
1563 --
1564 -- The Coverage through date is not EOT, it means that the
1565 -- user is trying to end coverage. But here the covered dependent
1566 -- record is not found, so we cannot end coverage.
1567 -- ERROR out.
1568 --
1569 hr_utility.set_location(l_proc, 50);
1570 --
1571 fnd_message.set_name('BEN','BEN_92323_CVG_CANNOT_BE_ENDED');
1572 fnd_message.raise_error;
1573 --
1574 elsif l_pdp_found and (l_cvg_thru_dt <> hr_api.g_eot
1575 or p_cvg_strt_dt is null) then
1576 --
1577 -- The coverage through date is not EOT or the coverage start date
1578 -- is null. In both the case, the user is trying to end the coverage
1579 -- for the dependent. As the covered dependent record is found,
1580 -- we continue with the processing.
1581 --
1582 hr_utility.set_location(l_proc, 55);
1583 --
1584 if p_effective_date > l_pdp_rec.cvg_strt_dt then
1585 --
1586 -- Coverage has started, so we cannot delete it. Just update
1587 -- the record with the coverage through date and the per_in_ler_id.
1588 --
1589 hr_utility.set_location(l_proc, 60);
1590 --
1591 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1592 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1593 p_effective_start_date => p_effective_start_date,
1594 p_effective_end_date => p_effective_end_date,
1595 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1596 p_cvg_thru_dt => l_cvg_thru_dt,
1597 p_object_version_number => l_pdp_rec.object_version_number,
1598 p_datetrack_mode => l_pdp_datetrack_mode,
1599 p_multi_row_actn => p_multi_row_actn,
1600 p_pdp_attribute_category => p_pdp_attribute_category,
1601 p_pdp_attribute1 => p_pdp_attribute1,
1602 p_pdp_attribute2 => p_pdp_attribute2,
1603 p_pdp_attribute3 => p_pdp_attribute3,
1604 p_pdp_attribute4 => p_pdp_attribute4,
1605 p_pdp_attribute5 => p_pdp_attribute5,
1606 p_pdp_attribute6 => p_pdp_attribute6,
1607 p_pdp_attribute7 => p_pdp_attribute7,
1608 p_pdp_attribute8 => p_pdp_attribute8,
1609 p_pdp_attribute9 => p_pdp_attribute9,
1610 p_pdp_attribute10 => p_pdp_attribute10,
1611 p_pdp_attribute11 => p_pdp_attribute11,
1612 p_pdp_attribute12 => p_pdp_attribute12,
1613 p_pdp_attribute13 => p_pdp_attribute13,
1614 p_pdp_attribute14 => p_pdp_attribute14,
1615 p_pdp_attribute15 => p_pdp_attribute15,
1616 p_pdp_attribute16 => p_pdp_attribute16,
1617 p_pdp_attribute17 => p_pdp_attribute17,
1618 p_pdp_attribute18 => p_pdp_attribute18,
1619 p_pdp_attribute19 => p_pdp_attribute19,
1620 p_pdp_attribute20 => p_pdp_attribute20,
1621 p_pdp_attribute21 => p_pdp_attribute21,
1622 p_pdp_attribute22 => p_pdp_attribute22,
1623 p_pdp_attribute23 => p_pdp_attribute23,
1624 p_pdp_attribute24 => p_pdp_attribute24,
1625 p_pdp_attribute25 => p_pdp_attribute25,
1626 p_pdp_attribute26 => p_pdp_attribute26,
1627 p_pdp_attribute27 => p_pdp_attribute27,
1628 p_pdp_attribute28 => p_pdp_attribute28,
1629 p_pdp_attribute29 => p_pdp_attribute29,
1630 p_pdp_attribute30 => p_pdp_attribute30,
1631 p_request_id => fnd_global.conc_request_id,
1632 p_program_application_id => fnd_global.prog_appl_id,
1633 p_program_id => fnd_global.conc_program_id,
1634 p_program_update_date => sysdate,
1635 p_business_group_id => p_business_group_id,
1636 p_effective_date => p_effective_date);
1637 --
1638 p_elig_cvrd_dpnt_id := l_egd_rec.elig_cvrd_dpnt_id;
1639 p_object_version_number := l_pdp_rec.object_version_number;
1640 --
1641 else
1642 --
1643 -- Coverage has not started, so purge the record.
1644 -- Remove the link from eligible dependent record.
1645 --
1646 hr_utility.set_location(l_proc, 65);
1647 --
1648 ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt(
1649 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1650 p_effective_start_date => l_effective_start_date,
1651 p_effective_end_date => l_effective_end_date,
1652 p_object_version_number => l_pdp_rec.object_version_number,
1653 p_business_group_id => p_business_group_id,
1654 p_effective_date => p_effective_date,
1655 p_datetrack_mode => hr_api.g_zap,
1656 p_multi_row_actn => true,
1657 p_called_from => 'beegdapi' );
1658 --
1659 ben_elig_dpnt_api.update_elig_dpnt(
1660 p_elig_dpnt_id => p_elig_dpnt_id,
1661 p_elig_cvrd_dpnt_id => null,
1662 p_effective_date => p_effective_date,
1663 p_business_group_id => p_business_group_id,
1664 p_object_version_number => l_egd_rec.object_version_number,
1665 p_program_application_id => fnd_global.prog_appl_id,
1666 p_program_id => fnd_global.conc_program_id,
1667 p_request_id => fnd_global.conc_request_id,
1668 p_program_update_date => sysdate);
1669 --
1670 p_elig_cvrd_dpnt_id := null;
1671 p_object_version_number := null;
1672 p_effective_start_date := null;
1673 p_effective_end_date := null;
1674 --
1675 end if;
1676 --
1677 elsif /* l_pdp_found and
1678 p_effective_date between
1679 l_pdp_rec.cvg_strt_dt and l_pdp_rec.cvg_thru_dt then
1680 */
1681 --
1682 -- Bug 3138982 if we remove a dependent accidentally and the
1683 -- coverage end date code is in the past like one day before event
1684 -- we dont come into this clause with the above condition and we try
1685 -- to create a new pdp records which will result in 91651 error.
1686 -- We need to look at the new coverage start date also.
1687 --
1688 l_pdp_found and
1689 ((p_effective_date between
1690 l_pdp_rec.cvg_strt_dt and l_pdp_rec.cvg_thru_dt) OR
1691 ( p_effective_date >=l_pdp_rec.effective_start_date and
1692 p_cvg_strt_dt = l_pdp_rec.cvg_strt_dt)) then
1693
1694 --
1695 -- As we have reached here, it means the coverge through date is EOT,
1696 -- and hence we are not trying to de-enroll the dependent.
1697 -- As coverage has started and we are within the coverage period,
1698 -- we can re-open the covered dependent record and use it.
1699 -- Update the record the coverage through date as EOT and the
1700 -- new per in ler id.
1701 --
1702 hr_utility.set_location(l_proc, 70);
1703 --
1704 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1705 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1706 p_effective_start_date => p_effective_start_date,
1707 p_effective_end_date => p_effective_end_date,
1708 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1709 p_cvg_strt_dt => p_cvg_strt_dt, -- 3453213
1710 p_cvg_thru_dt => hr_api.g_eot,
1711 p_object_version_number => l_pdp_rec.object_version_number,
1712 p_datetrack_mode => l_pdp_datetrack_mode,
1713 p_multi_row_actn => p_multi_row_actn,
1714 p_pdp_attribute_category => p_pdp_attribute_category,
1715 p_pdp_attribute1 => p_pdp_attribute1,
1716 p_pdp_attribute2 => p_pdp_attribute2,
1717 p_pdp_attribute3 => p_pdp_attribute3,
1718 p_pdp_attribute4 => p_pdp_attribute4,
1719 p_pdp_attribute5 => p_pdp_attribute5,
1720 p_pdp_attribute6 => p_pdp_attribute6,
1721 p_pdp_attribute7 => p_pdp_attribute7,
1722 p_pdp_attribute8 => p_pdp_attribute8,
1723 p_pdp_attribute9 => p_pdp_attribute9,
1724 p_pdp_attribute10 => p_pdp_attribute10,
1725 p_pdp_attribute11 => p_pdp_attribute11,
1726 p_pdp_attribute12 => p_pdp_attribute12,
1727 p_pdp_attribute13 => p_pdp_attribute13,
1728 p_pdp_attribute14 => p_pdp_attribute14,
1729 p_pdp_attribute15 => p_pdp_attribute15,
1730 p_pdp_attribute16 => p_pdp_attribute16,
1731 p_pdp_attribute17 => p_pdp_attribute17,
1732 p_pdp_attribute18 => p_pdp_attribute18,
1733 p_pdp_attribute19 => p_pdp_attribute19,
1734 p_pdp_attribute20 => p_pdp_attribute20,
1735 p_pdp_attribute21 => p_pdp_attribute21,
1736 p_pdp_attribute22 => p_pdp_attribute22,
1737 p_pdp_attribute23 => p_pdp_attribute23,
1738 p_pdp_attribute24 => p_pdp_attribute24,
1739 p_pdp_attribute25 => p_pdp_attribute25,
1740 p_pdp_attribute26 => p_pdp_attribute26,
1741 p_pdp_attribute27 => p_pdp_attribute27,
1742 p_pdp_attribute28 => p_pdp_attribute28,
1743 p_pdp_attribute29 => p_pdp_attribute29,
1744 p_pdp_attribute30 => p_pdp_attribute30,
1745 p_request_id => fnd_global.conc_request_id,
1746 p_program_application_id => fnd_global.prog_appl_id,
1747 p_program_id => fnd_global.conc_program_id,
1748 p_program_update_date => sysdate,
1749 p_business_group_id => p_business_group_id,
1750 p_effective_date => p_effective_date);
1751 --
1752 p_elig_cvrd_dpnt_id := l_egd_rec.elig_cvrd_dpnt_id;
1753 p_object_version_number := l_pdp_rec.object_version_number;
1754 --
1755 elsif p_cvg_strt_dt is not null then
1756 --
1757 -- Now the only left case, I could think of. But most important.
1758 -- Here we are trying to enroll the dependent for whom no covered
1759 -- dependent records were found and it needs to be created.
1760 -- Create the covered dependent record with the coverage start date
1761 -- and coverge through date.
1762 -- Update the eligible dependent record with the covered dependent id.
1763 --
1764 hr_utility.set_location(l_proc, 75);
1765 --
1766 ben_elig_cvrd_dpnt_api.create_elig_cvrd_dpnt
1767 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1768 p_effective_start_date => p_effective_start_date,
1769 p_effective_end_date => p_effective_end_date,
1770 p_business_group_id => p_business_group_id,
1771 p_dpnt_person_id => l_egd_rec.dpnt_person_id,
1772 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1773 p_cvg_strt_dt => p_cvg_strt_dt,
1774 p_cvg_thru_dt => hr_api.g_eot,
1775 p_prtt_enrt_rslt_id => l_egd_rec.prtt_enrt_rslt_id,
1776 p_object_version_number => p_object_version_number,
1777 p_effective_date => p_effective_date,
1778 p_multi_row_actn => p_multi_row_actn,
1779 p_pdp_attribute_category => p_pdp_attribute_category,
1780 p_pdp_attribute1 => p_pdp_attribute1,
1781 p_pdp_attribute2 => p_pdp_attribute2,
1782 p_pdp_attribute3 => p_pdp_attribute3,
1783 p_pdp_attribute4 => p_pdp_attribute4,
1784 p_pdp_attribute5 => p_pdp_attribute5,
1785 p_pdp_attribute6 => p_pdp_attribute6,
1786 p_pdp_attribute7 => p_pdp_attribute7,
1787 p_pdp_attribute8 => p_pdp_attribute8,
1788 p_pdp_attribute9 => p_pdp_attribute9,
1789 p_pdp_attribute10 => p_pdp_attribute10,
1790 p_pdp_attribute11 => p_pdp_attribute11,
1791 p_pdp_attribute12 => p_pdp_attribute12,
1792 p_pdp_attribute13 => p_pdp_attribute13,
1793 p_pdp_attribute14 => p_pdp_attribute14,
1794 p_pdp_attribute15 => p_pdp_attribute15,
1795 p_pdp_attribute16 => p_pdp_attribute16,
1796 p_pdp_attribute17 => p_pdp_attribute17,
1797 p_pdp_attribute18 => p_pdp_attribute18,
1798 p_pdp_attribute19 => p_pdp_attribute19,
1799 p_pdp_attribute20 => p_pdp_attribute20,
1800 p_pdp_attribute21 => p_pdp_attribute21,
1801 p_pdp_attribute22 => p_pdp_attribute22,
1802 p_pdp_attribute23 => p_pdp_attribute23,
1803 p_pdp_attribute24 => p_pdp_attribute24,
1804 p_pdp_attribute25 => p_pdp_attribute25,
1805 p_pdp_attribute26 => p_pdp_attribute26,
1806 p_pdp_attribute27 => p_pdp_attribute27,
1807 p_pdp_attribute28 => p_pdp_attribute28,
1808 p_pdp_attribute29 => p_pdp_attribute29,
1809 p_pdp_attribute30 => p_pdp_attribute30,
1810 p_program_application_id => fnd_global.prog_appl_id,
1811 p_program_id => fnd_global.conc_program_id,
1812 p_request_id => fnd_global.conc_request_id,
1813 p_program_update_date => sysdate);
1814 --
1815 ben_elig_dpnt_api.update_elig_dpnt(
1816 p_elig_dpnt_id => p_elig_dpnt_id,
1817 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1818 p_effective_date => p_effective_date,
1819 p_business_group_id => p_business_group_id,
1820 p_object_version_number => l_egd_rec.object_version_number,
1821 p_program_application_id => fnd_global.prog_appl_id,
1822 p_program_id => fnd_global.conc_program_id,
1823 p_request_id => fnd_global.conc_request_id,
1824 p_program_update_date => sysdate);
1825 --
1826 end if;
1827 --
1828 -- End of cases.
1829 --
1830 hr_utility.set_location('Leaving '||l_proc, 75);
1831 --
1832 -- When in validation only mode raise the Validate_Enabled exception
1833 --
1834 if p_validate then
1835 raise hr_api.validate_enabled;
1836 end if;
1837 --
1838 exception
1839 --
1840 when hr_api.validate_enabled then
1841 --
1842 -- As the Validate_Enabled exception has been raised
1843 -- we must rollback to the savepoint
1844 --
1845 ROLLBACK TO process_dependent;
1846 --
1847 when others then
1848 --
1849 -- A validation or unexpected error has occured
1850 --
1851 ROLLBACK TO process_dependent;
1852 /* Inserted for nocopy changes */
1853 p_elig_cvrd_dpnt_id := null;
1854 p_object_version_number := l_object_version_number;
1855 p_effective_start_date := null;
1856 p_effective_end_date := null;
1857 raise;
1858 --
1859 end process_dependent;
1860 --
1861 -- Procedure: process_dependent_w.
1862 -- Purpose : Used as a self service wrapper
1863 -- Called from: self service dependents selection screen
1864 --
1865 procedure process_dependent_w(p_validate in varchar2,
1866 p_elig_dpnt_id in number,
1867 p_business_group_id in number,
1868 p_effective_date in date,
1869 p_cvg_strt_dt in date,
1870 p_cvg_thru_dt in date,
1871 p_datetrack_mode in varchar2,
1872 p_elig_cvrd_dpnt_id out nocopy number,
1873 p_effective_start_date out nocopy date,
1874 p_effective_end_date out nocopy date,
1875 p_object_version_number in out nocopy number
1876 ,p_multi_row_actn in varchar2)
1877 IS
1878
1879 l_proc varchar2(72) := g_package||'.process_dependent - wrapper';
1880
1881 l_object_version_number number;
1882 l_elig_cvrd_dpnt_id number;
1883 l_effective_start_date date;
1884 l_effective_end_date date;
1885
1886 l_validate BOOLEAN;
1887 l_multi_row_actn BOOLEAN;
1888 --
1889 begin
1890 --
1891 fnd_msg_pub.initialize;
1892 hr_utility.set_location('Entering:'|| l_proc, 10);
1893 --
1894 -- Issue a savepoint if operating in validation only mode
1895 --
1896 savepoint process_dependent_w;
1897 --
1898 if fnd_global.conc_request_id in (0,-1) then
1899 --
1900 ben_env_object.init(p_business_group_id => p_business_group_id,
1901 p_effective_date => p_effective_date,
1902 p_thread_id => 1,
1903 p_chunk_size => 1,
1904 p_threads => 1,
1905 p_max_errors => 1,
1906 p_benefit_action_id => null);
1907 --
1908 end if;
1909 --
1910 hr_utility.set_location(l_proc, 20);
1911 --
1912 if upper(p_validate) = 'TRUE'
1913 then
1914 l_validate := TRUE;
1915 else
1916 l_validate := FALSE;
1917 end if;
1918
1919 if upper(p_multi_row_actn) = 'TRUE'
1920 then
1921 l_multi_row_actn := TRUE;
1922 else
1923 l_multi_row_actn := FALSE;
1924 end if;
1925 --
1926 hr_utility.set_location(l_proc, 30);
1927 --
1928 process_dependent(
1929 p_validate => l_validate
1930 ,p_elig_dpnt_id => p_elig_dpnt_id
1931 ,p_business_group_id => p_business_group_id
1932 ,p_effective_date => p_effective_date
1933 ,p_cvg_strt_dt => p_cvg_strt_dt
1934 ,p_cvg_thru_dt => p_cvg_thru_dt
1935 ,p_datetrack_mode => p_datetrack_mode
1936 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1937 ,p_effective_start_date => l_effective_start_date
1938 ,p_effective_end_date => l_effective_end_date
1939 ,p_object_version_number => p_object_version_number
1940 ,p_multi_row_actn => l_multi_row_actn);
1941 --
1942 if l_validate then
1943 raise hr_api.validate_enabled;
1944 end if;
1945 --
1946 -- Set all output arguments
1947 --
1948 p_elig_cvrd_dpnt_id := l_elig_cvrd_dpnt_id;
1949 p_effective_start_date := l_effective_start_date;
1950 p_effective_end_date := l_effective_end_date;
1951 p_object_version_number := l_object_version_number;
1952 --
1953 hr_utility.set_location(' Leaving:'||l_proc, 40);
1954
1955 exception
1956 --
1957 when hr_api.validate_enabled then
1958 --
1959 -- As the Validate_Enabled exception has been raised
1960 -- we must rollback to the savepoint
1961 --
1962 ROLLBACK TO process_dependent_w;
1963 when app_exception.application_exception then --Bug 4387247
1964 ROLLBACK TO process_dependent_w;
1965 fnd_msg_pub.add;
1966 --Bug 4436578
1967 /* Inserted for nocopy changes */
1968 p_elig_cvrd_dpnt_id := null;
1969 p_object_version_number := l_object_version_number;
1970 p_effective_start_date := null;
1971 p_effective_end_date := null;
1972 when others then
1973 --
1974 -- A validation or unexpected error has occured
1975 --
1976 hr_utility.set_location(' Exception in :'||l_proc, 100);
1977 --Bug 4387247
1978 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1979 fnd_message.set_token('2',substr(sqlerrm,1,200));
1980 fnd_msg_pub.add; -- bug fix 4111762
1981 ROLLBACK TO process_dependent_w;
1982 /* Inserted for nocopy changes */
1983 p_elig_cvrd_dpnt_id := null;
1984 p_object_version_number := l_object_version_number;
1985 p_effective_start_date := null;
1986 p_effective_end_date := null;
1987 --
1988 end process_dependent_w;
1989 --
1990 -- Bug 4114970
1991 -- Procedure: store_crt_ord_warning_ss.
1992 -- Purpose : Used by self service to store court order warnings
1993 -- Called from: self service dependents selection screen
1994 --
1995 procedure store_crt_ord_warning_ss(p_person_id in number,
1996 p_crt_ord_warning in varchar2) is
1997 l_proc varchar2(72) := g_package||'.store_crt_ord_warning_ss';
1998 begin
1999 hr_utility.set_location('Entering:'|| l_proc, 10);
2000 --
2001 if p_person_id is not null and p_crt_ord_warning is not null then
2002 if p_crt_ord_warning = 'CLEAR' then
2003 DELETE ben_online_warnings
2004 WHERE session_id = p_person_id;
2005 else
2006 INSERT INTO ben_online_warnings(session_id, message_text)
2007 VALUES (p_person_id, p_crt_ord_warning);
2008 end if;
2009 end if;
2010 --
2011 hr_utility.set_location('Leaving'|| l_proc, 20);
2012 exception
2013 when others then
2014 --
2015 -- An unexpected error has occured
2016 --
2017 hr_utility.set_location(' Exception in :'||l_proc, 100);
2018 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2019 fnd_message.set_token('2',substr(sqlerrm,1,200));
2020 fnd_msg_pub.add;
2021 end store_crt_ord_warning_ss;
2022 --
2023 -- Bug 4114970
2024 -- Procedure: store_crt_ord_warng_DDNA.
2025 -- DDNA => Dependent Designation Not Allowed (plan-options)
2026 --
2027 -- Purpose : Used by self service to store court order warnings for those plan-options
2028 -- in which dependent designation is not allowed e.g. emp only option.
2029 -- NOTE : This procedure serves similar purpose in SS as bepenrhi.crt_ordr_warning in PUI
2030 -- So care should be taken to keep them in functional sync.
2031 --
2032 -- Called from: self service dependents selection screen
2033 --
2034 procedure store_crt_ord_warng_DDNA(p_person_id in number,
2035 p_per_in_ler_id in number,
2036 p_pgm_id in number,
2037 p_effective_date in date,
2038 p_business_group_id in number)
2039 is
2040 --
2041 cursor c_crt_ordr_DDNA is
2042 SELECT pen.person_id,
2043 pln.pl_id,
2044 typ.pl_typ_id,
2045 (typ.name || ' - ' || pln.name) comp_obj_name
2046 FROM ben_prtt_enrt_rslt_f pen,
2047 ben_pl_f pln,
2048 ben_pl_typ_f typ,
2049 ben_elig_per_elctbl_chc chc
2050 WHERE chc.per_in_ler_id = p_per_in_ler_id
2051 AND chc.pgm_id = p_pgm_id
2052 AND chc.alws_dpnt_dsgn_flag = 'N'
2053 AND pen.prtt_enrt_rslt_id = chc.prtt_enrt_rslt_id
2054 AND pen.prtt_enrt_rslt_stat_cd IS NULL
2055 AND p_effective_date between pen.effective_start_date
2056 and pen.effective_end_date
2057 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
2058 AND pen.pl_id = pln.pl_id
2059 AND pln.pl_stat_cd = 'A'
2060 AND pln.svgs_pl_flag <> 'Y'
2061 AND pln.alws_qmcso_flag = 'Y'
2062 AND p_effective_date between pln.effective_start_date
2063 and pln.effective_end_date
2064 AND pln.pl_typ_id = typ.pl_typ_id
2065 AND p_effective_date between typ.effective_start_date
2066 and typ.effective_end_date
2067 AND EXISTS(SELECT null
2068 FROM ben_crt_ordr crt,
2069 ben_per_in_ler pil
2070 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
2071 AND crt.person_id = pen.person_id
2072 AND (crt.pl_id = pln.pl_id or crt.pl_typ_id = typ.pl_typ_id)
2073 AND crt.business_group_id = p_business_group_id
2074 AND pil.per_in_ler_id = pen.per_in_ler_id
2075 AND greatest(pen.enrt_cvg_strt_dt, pil.lf_evt_ocrd_dt)
2076 between greatest(nvl(crt.apls_perd_strtg_dt, p_effective_date)
2077 ,nvl(crt.detd_qlfd_ordr_dt, crt.apls_perd_strtg_dt)
2078 )
2079 and nvl(crt.apls_perd_endg_dt, pen.enrt_cvg_thru_dt)
2080 )
2081 AND NOT EXISTS
2082 (SELECT 1
2083 FROM ben_prtt_enrt_rslt_f pen2
2084 where pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
2085 and pen2.sspndd_flag = 'Y'
2086 and pen2.prtt_enrt_rslt_stat_cd is null
2087 and p_effective_date between
2088 pen2.effective_start_date and pen2.effective_end_date
2089 and pen2.enrt_cvg_thru_dt = hr_api.g_eot);
2090 --
2091 l_comp_obj_list varchar2(2000) := null;
2092 l_proc varchar2(72) := g_package||'.store_crt_ord_warng_DDNA';
2093 begin
2094 hr_utility.set_location('Entering:'|| l_proc, 10);
2095 --
2096 for l_results in c_crt_ordr_DDNA loop
2097 if l_comp_obj_list is null then
2098 l_comp_obj_list := l_results.comp_obj_name;
2099 else
2100 l_comp_obj_list := l_comp_obj_list || ' , ' || l_results.comp_obj_name;
2101 end if;
2102 end loop;
2103
2104 if l_comp_obj_list is not null then
2105 fnd_message.set_name('BEN', 'BEN_94486_CRT_ORD_WARNING_DDNA');
2106 fnd_message.set_token('PARAM', l_comp_obj_list);
2107
2108 INSERT INTO ben_online_warnings(session_id, message_text)
2109 VALUES (p_person_id, fnd_message.get);
2110 end if;
2111 --
2112 hr_utility.set_location('Leaving'|| l_proc, 20);
2113 exception
2114 when others then
2115 --
2116 -- An unexpected error has occured
2117 --
2118 hr_utility.set_location(' Exception in :'||l_proc, 100);
2119 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2120 fnd_message.set_token('2',substr(sqlerrm,1,200));
2121 fnd_msg_pub.add;
2122 end store_crt_ord_warng_DDNA;
2123 --
2124 --
2125 -- Function to get the eligible dependent record.
2126 --
2127 function get_elig_dpnt_rec(p_elig_dpnt_id in number,
2128 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2129 return boolean is
2130 --
2131 cursor c_egd is
2132 select egd.*
2133 from ben_elig_dpnt egd
2134 where egd.elig_dpnt_id = p_elig_dpnt_id;
2135 --
2136 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_1';
2137 --
2138 begin
2139 --
2140 hr_utility.set_location('Entering '||l_proc, 10);
2141 --
2142 open c_egd;
2143 fetch c_egd into p_elig_dpnt_rec;
2144 --
2145 if c_egd%notfound then
2146 --
2147 close c_egd;
2148 return false;
2149 --
2150 else
2151 --
2152 close c_egd;
2153 return true;
2154 --
2155 end if;
2156 --
2157 end get_elig_dpnt_rec;
2158 --
2159 --
2160 -- Function to get the eligible dependent record for a covered dependent.
2161 --
2162 function get_elig_dpnt_rec(p_elig_cvrd_dpnt_id in number,
2163 p_effective_date in date,
2164 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2165 return boolean is
2166 --
2167 cursor c_pdp is
2168 select egd.*
2169 from ben_elig_dpnt egd,
2170 ben_elig_cvrd_dpnt_f pdp,
2171 ben_per_in_ler pil
2172 where pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
2173 and pdp.per_in_ler_id = egd.per_in_ler_id
2174 and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
2175 and pdp.dpnt_person_id = egd.dpnt_person_id
2176 and pdp.per_in_ler_id = pil.per_in_ler_id
2177 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2178 and p_effective_date between
2179 pdp.cvg_strt_dt and pdp.cvg_thru_dt
2180 --- Fido fix medged by tilak # 2931919
2181 and pdp.cvg_thru_dt <= pdp.effective_end_date ;
2182 --
2183 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_2';
2184 --
2185 begin
2186 --
2187 hr_utility.set_location('Entering '||l_proc, 10);
2188 --
2189 open c_pdp;
2190 fetch c_pdp into p_elig_dpnt_rec;
2191 --
2192 if c_pdp%notfound then
2193 --
2194 close c_pdp;
2195 return false;
2196 --
2197 else
2198 --
2199 close c_pdp;
2200 return true;
2201 --
2202 end if;
2203 --
2204 end get_elig_dpnt_rec;
2205 --
2206 --
2207 -- Function to get the eligible dependent record for a dependent
2208 -- and enrollment result.
2209 --
2210 function get_elig_dpnt_rec(p_dpnt_person_id in number,
2211 p_prtt_enrt_rslt_id in number,
2212 p_effective_date in date,
2213 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2214 return boolean is
2215 --
2216 cursor c_pen is
2217 select egd.*
2218 from ben_elig_dpnt egd,
2219 ben_elig_cvrd_dpnt_f pdp,
2220 ben_prtt_enrt_rslt_f pen,
2221 ben_per_in_ler pil
2222 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2223 and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2224 and pdp.dpnt_person_id = p_dpnt_person_id
2225 and pdp.per_in_ler_id = egd.per_in_ler_id
2226 and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
2227 and pdp.dpnt_person_id = egd.dpnt_person_id
2228 and pdp.per_in_ler_id = pil.per_in_ler_id
2229 and pen.prtt_enrt_rslt_stat_cd is null
2230 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2231 and p_effective_date between
2232 pen.effective_start_date and pen.effective_end_date
2233 and p_effective_date between
2234 pdp.cvg_strt_dt and pdp.cvg_thru_dt;
2235 --
2236 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_3';
2237 --
2238 begin
2239 --
2240 hr_utility.set_location('Entering '||l_proc, 10);
2241 --
2242 open c_pen;
2243 fetch c_pen into p_elig_dpnt_rec;
2244 --
2245 if c_pen%notfound then
2246 --
2247 close c_pen;
2248 return false;
2249 --
2250 else
2251 --
2252 close c_pen;
2253 return true;
2254 --
2255 end if;
2256 --
2257 end get_elig_dpnt_rec;
2258 --
2259 -- Function to get the eligible dependent record for a dependent person
2260 -- and comp. object (combination of pgm/pl/oipl).
2261 --
2262 function get_elig_dpnt_rec
2263 (p_pgm_id in number default null
2264 ,p_pl_id in number default null
2265 ,p_oipl_id in number default null
2266 ,p_dpnt_person_id in number
2267 ,p_effective_date in date
2268 --
2269 ,p_per_in_ler_id in number default null
2270 ,p_elig_per_id in number default null
2271 ,p_elig_per_opt_id in number default null
2272 ,p_opt_id in number default null
2273 --
2274 ,p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype
2275 )
2276 return boolean
2277 is
2278 --
2279 l_pep_id_va benutils.g_number_table := benutils.g_number_table();
2280 l_epo_id_va benutils.g_number_table := benutils.g_number_table();
2281 --
2282 cursor c_eligdpnt_exists
2283 is
2284 select null
2285 from ben_elig_dpnt egd
2286 where egd.dpnt_person_id = p_dpnt_person_id;
2287 --
2288 cursor c_oipl_max_create_dt
2289 (c_dpnt_person_id in number
2290 ,c_effective_date in date
2291 ,c_oipl_id in number
2292 ,c_pgm_id in number
2293 )
2294 is
2295 select egd.elig_dpnt_id
2296 from ben_elig_dpnt egd,
2297 ben_elig_per_opt_f epo,
2298 ben_elig_per_f pep,
2299 ben_per_in_ler pil,
2300 ben_oipl_f oipl
2301 where egd.dpnt_person_id = c_dpnt_person_id
2302 and egd.elig_per_opt_id = epo.elig_per_opt_id
2303 and egd.elig_per_id = epo.elig_per_id
2304 and c_effective_date between
2305 epo.effective_start_date and epo.effective_end_date
2306 and epo.elig_per_id = pep.elig_per_id
2307 and c_effective_date between
2308 pep.effective_start_date and pep.effective_end_date
2309 and oipl.pl_id = pep.pl_id
2310 and c_effective_date between
2311 oipl.effective_start_date and oipl.effective_end_date
2312 and oipl.oipl_id = c_oipl_id
2313 and oipl.opt_id = epo.opt_id
2314 and nvl(pep.pgm_id, -1) = c_pgm_id
2315 and egd.per_in_ler_id = pil.per_in_ler_id
2316 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
2317 order by egd.create_dt desc;
2318 --
2319 cursor c_pl_max_create_dt
2320 (c_dpnt_person_id number
2321 ,c_pl_id number
2322 ,c_pgm_id number
2323 ,c_effective_date date
2324 )
2325 is
2326 select egd.elig_dpnt_id
2327 from ben_elig_dpnt egd,
2328 ben_per_in_ler pil,
2329 ben_elig_per_f pep
2330 where egd.dpnt_person_id = c_dpnt_person_id
2331 and egd.elig_per_opt_id is null
2332 and egd.elig_per_id = pep.elig_per_id
2333 and pep.pl_id = c_pl_id
2334 and nvl(pep.pgm_id, -1) = nvl(c_pgm_id, -1)
2335 and egd.per_in_ler_id = pil.per_in_ler_id
2336 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
2337 and c_effective_date
2338 between pep.effective_start_date and pep.effective_end_date
2339 order by egd.create_dt desc;
2340 --
2341 cursor c_pilepodets
2342 (c_effective_date in date
2343 ,c_per_in_ler_id in number
2344 ,c_pl_id in number
2345 ,c_opt_id in number
2346 ,c_pgm_id in number
2347 )
2348 is
2349 select epo.elig_per_opt_id,
2350 epo.elig_per_id
2351 from ben_elig_per_opt_f epo,
2352 ben_elig_per_f pep
2353 where epo.per_in_ler_id = c_per_in_ler_id
2354 and c_effective_date between
2355 epo.effective_start_date and epo.effective_end_date
2356 and epo.elig_per_id = pep.elig_per_id
2357 and c_effective_date between
2358 pep.effective_start_date and pep.effective_end_date
2359 and pep.pl_id = c_pl_id
2360 and epo.opt_id = c_opt_id
2361 and nvl(pep.pgm_id, -1) = c_pgm_id;
2362 --
2363 cursor c_epoegddets
2364 (c_elig_per_opt_id in number
2365 ,c_elig_per_id in number
2366 ,c_dpnt_person_id in number
2367 ,c_pil_id in number
2368 )
2369 is
2370 select egd.elig_dpnt_id
2371 from ben_elig_dpnt egd
2372 where egd.elig_per_opt_id = c_elig_per_opt_id
2373 and egd.elig_per_id = c_elig_per_id
2374 and egd.dpnt_person_id = c_dpnt_person_id
2375 and egd.per_in_ler_id = c_pil_id
2376 order by egd.create_dt desc;
2377 --
2378 cursor c_pepegddets
2379 (c_elig_per_id in number
2380 ,c_dpnt_person_id in number
2381 ,c_pil_id in number
2382 )
2383 is
2384 select egd.elig_dpnt_id
2385 from ben_elig_dpnt egd
2386 where egd.elig_per_id = c_elig_per_id
2387 and egd.elig_per_opt_id is null
2388 and egd.dpnt_person_id = c_dpnt_person_id
2389 and egd.per_in_ler_id = c_pil_id;
2390 --
2391 l_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%type := null;
2392 l_return boolean := false;
2393 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_4';
2394 --
2395 begin
2396 --
2397 hr_utility.set_location('Entering '||l_proc, 10);
2398 --
2399 -- Check if the elig dependent exists
2400 --
2401 open c_eligdpnt_exists;
2402 fetch c_eligdpnt_exists into l_elig_dpnt_id;
2403 --
2404 if c_eligdpnt_exists%found then
2405 --
2406 if p_oipl_id is not null then
2407 --
2408 -- Currently only supported from benmngle where PIL ID is passed.
2409 -- This logic was added because of severe performance for customers
2410 -- with enormous amounts of eligible rows.
2411 --
2412 if p_per_in_ler_id is not null
2413 then
2414 --
2415 -- Check if the PEP ID and EPO ID are set from benmngle
2416 --
2417 if p_elig_per_id is not null
2418 and p_elig_per_opt_id is not null
2419 then
2420 --
2421 -- Set the PEPID and EPOID to the value passed from benmngle
2422 --
2423 l_pep_id_va.extend(1);
2424 l_pep_id_va(1) := p_elig_per_id;
2425 --
2426 l_epo_id_va.extend(1);
2427 l_epo_id_va(1) := p_elig_per_opt_id;
2428 --
2429 else
2430 --
2431 -- When eligibility for the current PILID is found then use the
2432 -- performant code. However if for some special cases no current
2433 -- eligibility rows are found for the comp object then revert to the
2434 -- original unperformant cursor.
2435 --
2436 hr_utility.set_location(l_proc, 20);
2437 open c_pilepodets
2438 (c_effective_date => p_effective_date
2439 ,c_per_in_ler_id => p_per_in_ler_id
2440 ,c_pl_id => p_pl_id
2441 ,c_opt_id => p_opt_id
2442 ,c_pgm_id => nvl(p_pgm_id, -1)
2443 );
2444 fetch c_pilepodets BULK COLLECT INTO l_epo_id_va, l_pep_id_va;
2445 close c_pilepodets;
2446 --
2447 end if;
2448 --
2449 if l_epo_id_va.count > 0 then
2450 --
2451 for epoelenum in l_epo_id_va.first..l_epo_id_va.last
2452 loop
2453 --
2454 open c_epoegddets
2455 (c_elig_per_opt_id => l_epo_id_va(epoelenum)
2456 ,c_elig_per_id => l_pep_id_va(epoelenum)
2457 ,c_dpnt_person_id => p_dpnt_person_id
2458 ,c_pil_id => p_per_in_ler_id
2459 );
2460 fetch c_epoegddets into l_elig_dpnt_id;
2461 if c_epoegddets%notfound then
2462 --
2463 l_elig_dpnt_id := null;
2464 --
2465 end if;
2466 close c_epoegddets;
2467 --
2468 end loop;
2469 --
2470 else
2471 --
2472 open c_oipl_max_create_dt
2473 (c_dpnt_person_id => p_dpnt_person_id
2474 ,c_effective_date => p_effective_date
2475 ,c_oipl_id => p_oipl_id
2476 ,c_pgm_id => nvl(p_pgm_id, -1)
2477 );
2478 fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2479 close c_oipl_max_create_dt;
2480 --
2481 end if;
2482 --
2483 hr_utility.set_location(l_proc, 25);
2484 --
2485 else
2486 --
2487 hr_utility.set_location(l_proc, 21);
2488 open c_oipl_max_create_dt
2489 (c_dpnt_person_id => p_dpnt_person_id
2490 ,c_effective_date => p_effective_date
2491 ,c_oipl_id => p_oipl_id
2492 ,c_pgm_id => nvl(p_pgm_id, -1)
2493 );
2494 fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2495 close c_oipl_max_create_dt;
2496 hr_utility.set_location(l_proc, 26);
2497 --
2498 end if;
2499 --
2500 else
2501 --
2502 -- When the PEP ID and PIL ID are set fire the performant SQL
2503 --
2504 if p_elig_per_id is not null
2505 and p_per_in_ler_id is not null
2506 then
2507 --
2508 open c_pepegddets
2509 (c_elig_per_id => p_elig_per_id
2510 ,c_dpnt_person_id => p_dpnt_person_id
2511 ,c_pil_id => p_per_in_ler_id
2512 );
2513 fetch c_pepegddets into l_elig_dpnt_id;
2514 close c_pepegddets;
2515 --
2516 else
2517 --
2518 open c_pl_max_create_dt
2519 (c_dpnt_person_id => p_dpnt_person_id
2520 ,c_pl_id => p_pl_id
2521 ,c_pgm_id => p_pgm_id
2522 ,c_effective_date => p_effective_date
2523 );
2524 fetch c_pl_max_create_dt into l_elig_dpnt_id;
2525 close c_pl_max_create_dt;
2526 --
2527 end if;
2528 hr_utility.set_location(l_proc, 30);
2529 --
2530 end if;
2531 --
2532 end if;
2533 --
2534 close c_eligdpnt_exists;
2535 --
2536 if l_elig_dpnt_id is not null then
2537 --
2538 hr_utility.set_location(l_proc, 40);
2539 --
2540 l_return := get_elig_dpnt_rec(p_elig_dpnt_id => l_elig_dpnt_id,
2541 p_elig_dpnt_rec => p_elig_dpnt_rec);
2542 --
2543 end if;
2544 --
2545 hr_utility.set_location('Leaving '||l_proc, 10);
2546 --
2547 return l_return;
2548 --
2549 end get_elig_dpnt_rec;
2550 --
2551 -- Procedure to obtain the eligible person id's for a participant
2552 -- and the comp. object (combination of pgm/pl/oipl).
2553 --
2554 procedure get_elig_per_id(p_person_id in number,
2555 p_pgm_id in number default null,
2556 p_pl_id in number default null,
2557 p_oipl_id in number default null,
2558 p_business_group_id in number,
2559 p_effective_date in date,
2560 p_elig_per_id out nocopy number,
2561 p_elig_per_opt_id out nocopy number) is
2562 --
2563 cursor c_oipl is
2564 select epo.elig_per_id,
2565 epo.elig_per_opt_id
2566 from ben_elig_per_opt_f epo,
2567 ben_elig_per_f pep,
2568 ben_oipl_f oipl
2569 where oipl.oipl_id = p_oipl_id
2570 and pep.pl_id = oipl.pl_id
2571 and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
2572 and pep.person_id = p_person_id
2573 and pep.business_group_id = p_business_group_id
2574 and oipl.opt_id = epo.opt_id
2575 and epo.elig_per_id = pep.elig_per_id
2576 and pep.elig_flag = 'Y'
2577 and epo.elig_flag = 'Y'
2578 and p_effective_date between
2579 oipl.effective_start_date and oipl.effective_end_date
2580 and p_effective_date between
2581 pep.effective_start_date and pep.effective_end_date
2582 and p_effective_date between
2583 epo.effective_start_date and epo.effective_end_date;
2584 --
2585 cursor c_pl is
2586 select pep.elig_per_id
2587 from ben_elig_per_f pep
2588 where pep.pl_id = p_pl_id
2589 and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
2590 and pep.person_id = p_person_id
2591 and pep.business_group_id = p_business_group_id
2592 and pep.elig_flag = 'Y'
2593 and p_effective_date between
2594 pep.effective_start_date and pep.effective_end_date;
2595 --
2596 l_proc varchar2(72) := g_package||'get_elig_per_id';
2597 --
2598 begin
2599 --
2600 hr_utility.set_location('Entering '||l_proc, 10);
2601 --
2602 p_elig_per_id := null;
2603 p_elig_per_opt_id := null;
2604 --
2605 if p_oipl_id is not null then
2606 --
2607 hr_utility.set_location(l_proc, 20);
2608 --
2609 open c_oipl;
2610 fetch c_oipl into p_elig_per_id, p_elig_per_opt_id;
2611 close c_oipl;
2612 --
2613 else
2614 --
2615 hr_utility.set_location(l_proc, 30);
2616 --
2617 open c_pl;
2618 fetch c_pl into p_elig_per_id;
2619 close c_pl;
2620 --
2621 end if;
2622 --
2623 hr_utility.set_location('Leaving '||l_proc, 10);
2624 --
2625 end get_elig_per_id;
2626 --
2627 -- Bug No 4931912
2628 -- Function to return the court order type defined for a person.
2629 --
2630 procedure get_crt_ordr_typ(p_person_id in number,
2631 p_pl_id in number,
2632 p_pl_typ_id in number,
2633 l_crt_ordr_meaning out nocopy varchar2)
2634 IS
2635 cursor get_crt_ordr_typ
2636 is SELECT crt.CRT_ORDR_TYP_CD
2637 FROM ben_crt_ordr crt
2638 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
2639 AND crt.person_id = p_person_id
2640 AND crt.pl_id = p_pl_id or crt.pl_typ_id = p_pl_typ_id;
2641
2642 l_crt_ordr_typ_cd VARCHAR2(30);
2643 begin
2644 open get_crt_ordr_typ;
2645 fetch get_crt_ordr_typ into l_crt_ordr_typ_cd;
2646 close get_crt_ordr_typ;
2647 l_crt_ordr_meaning := hr_general.decode_lookup
2648 (p_lookup_type => 'BEN_CRT_ORDR_TYP',
2649 p_lookup_code => l_crt_ordr_typ_cd
2650 );
2651 end get_crt_ordr_typ;
2652 --
2653 end ben_ELIG_DPNT_api;