[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_DPNT_API
Source
1 Package Body ben_ELIG_DPNT_api as
2 /* $Header: beegdapi.pkb 120.11.12010000.5 2009/11/03 09:51:23 sallumwa ship $ */
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_end_date, -------Bug 9051021
1454 pdp.effective_start_date
1455 from ben_elig_cvrd_dpnt_f pdp
1456 where pdp.elig_cvrd_dpnt_id = l_egd_rec.elig_cvrd_dpnt_id
1457 and pdp.prtt_enrt_rslt_id = l_egd_rec.prtt_enrt_rslt_id
1458 and pdp.business_group_id = p_business_group_id
1459 and p_effective_date between
1460 pdp.effective_start_date and pdp.effective_end_date;
1461 --
1462 l_pdp_rec c_pdp%rowtype;
1463 l_egd_found boolean := false;
1464 l_pdp_found boolean := false;
1465 l_pdp_datetrack_mode varchar2(30);
1466 l_cvg_thru_dt date;
1467 l_effective_start_date date;
1468 l_effective_end_date date;
1469 l_object_version_number number := p_object_version_number;
1470 l_proc varchar2(72) := g_package||'.process_dependent';
1471 --
1472 begin
1473 --
1474 hr_utility.set_location('Entering:'|| l_proc, 10);
1475 --
1476 -- Issue a savepoint if operating in validation only mode
1477 --
1478 savepoint process_dependent;
1479 --
1480 if fnd_global.conc_request_id = -1 then
1481 --
1482 ben_env_object.init(p_business_group_id => p_business_group_id,
1483 p_effective_date => p_effective_date,
1484 p_thread_id => 1,
1485 p_chunk_size => 1,
1486 p_threads => 1,
1487 p_max_errors => 1,
1488 p_benefit_action_id => null);
1489 --
1490 end if;
1491 --
1492 hr_utility.set_location(l_proc, 15);
1493 --
1494 open c_egd;
1495 fetch c_egd into l_egd_rec;
1496 if c_egd%found then
1497 l_egd_found := true;
1498 end if;
1499 close c_egd;
1500 --
1501 -- Bug 5486397
1502 l_egd_rec.prtt_enrt_rslt_id := return_sspnd_if_interim (l_egd_rec.prtt_enrt_rslt_id, p_effective_date);
1503 --
1504 hr_utility.set_location(l_proc, 20);
1505 --
1506 if l_egd_rec.elig_cvrd_dpnt_id is not null then
1507 --
1508 hr_utility.set_location(l_proc, 25);
1509 --
1510 open c_pdp;
1511 fetch c_pdp into l_pdp_rec;
1512 if c_pdp%found then
1513 --
1514 hr_utility.set_location(l_proc, 30);
1515 --
1516 l_pdp_found := true;
1517 --
1518 if l_pdp_rec.effective_start_date = p_effective_date then
1519 --
1520 l_pdp_datetrack_mode := hr_api.g_correction;
1521 --
1522 else
1523 --
1524 l_pdp_datetrack_mode := p_datetrack_mode;
1525 --
1526 end if;
1527 --
1528 end if;
1529 --
1530 close c_pdp;
1531 --
1532 hr_utility.set_location(l_proc, 35);
1533 --
1534 end if;
1535 --
1536 -- Note: We are making coverge through date as EOT, if it is null.
1537 -- Take note of this, when looking for cases.
1538 --
1539 if p_cvg_thru_dt is null then
1540 --
1541 l_cvg_thru_dt := hr_api.g_eot;
1542 --
1543 else
1544 --
1545 l_cvg_thru_dt := p_cvg_thru_dt;
1546 --
1547 end if;
1548 --
1549 hr_utility.set_location(l_proc, 40);
1550 --
1551 -- Cases start. Each case has their own if statement.
1552 --
1553 if not l_egd_found then
1554 --
1555 -- Eligible dependent record not found.
1556 -- ERROR out.
1557 --
1558 hr_utility.set_location(l_proc, 45);
1559 --
1560 fnd_message.set_name('BEN','BEN_92322_EGD_NOT_FOUND');
1561 fnd_message.raise_error;
1562 --
1563 elsif l_cvg_thru_dt <> hr_api.g_eot and not l_pdp_found then
1564 --
1565 -- The Coverage through date is not EOT, it means that the
1566 -- user is trying to end coverage. But here the covered dependent
1567 -- record is not found, so we cannot end coverage.
1568 -- ERROR out.
1569 --
1570 hr_utility.set_location(l_proc, 50);
1571 --
1572 fnd_message.set_name('BEN','BEN_92323_CVG_CANNOT_BE_ENDED');
1573 fnd_message.raise_error;
1574 --
1575 elsif l_pdp_found and (l_cvg_thru_dt <> hr_api.g_eot
1576 or p_cvg_strt_dt is null) then
1577 --
1578 -- The coverage through date is not EOT or the coverage start date
1579 -- is null. In both the case, the user is trying to end the coverage
1580 -- for the dependent. As the covered dependent record is found,
1581 -- we continue with the processing.
1582 --
1583 hr_utility.set_location(l_proc, 55);
1584 --
1585 if p_effective_date > l_pdp_rec.cvg_strt_dt then
1586 --
1587 -- Coverage has started, so we cannot delete it. Just update
1588 -- the record with the coverage through date and the per_in_ler_id.
1589 --
1590 hr_utility.set_location(l_proc, 60);
1591 --
1592 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1593 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1594 p_effective_start_date => p_effective_start_date,
1595 p_effective_end_date => p_effective_end_date,
1596 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1597 p_cvg_thru_dt => l_cvg_thru_dt,
1598 p_object_version_number => l_pdp_rec.object_version_number,
1599 p_datetrack_mode => l_pdp_datetrack_mode,
1600 p_multi_row_actn => p_multi_row_actn,
1601 p_pdp_attribute_category => p_pdp_attribute_category,
1602 p_pdp_attribute1 => p_pdp_attribute1,
1603 p_pdp_attribute2 => p_pdp_attribute2,
1604 p_pdp_attribute3 => p_pdp_attribute3,
1605 p_pdp_attribute4 => p_pdp_attribute4,
1606 p_pdp_attribute5 => p_pdp_attribute5,
1607 p_pdp_attribute6 => p_pdp_attribute6,
1608 p_pdp_attribute7 => p_pdp_attribute7,
1609 p_pdp_attribute8 => p_pdp_attribute8,
1610 p_pdp_attribute9 => p_pdp_attribute9,
1611 p_pdp_attribute10 => p_pdp_attribute10,
1612 p_pdp_attribute11 => p_pdp_attribute11,
1613 p_pdp_attribute12 => p_pdp_attribute12,
1614 p_pdp_attribute13 => p_pdp_attribute13,
1615 p_pdp_attribute14 => p_pdp_attribute14,
1616 p_pdp_attribute15 => p_pdp_attribute15,
1617 p_pdp_attribute16 => p_pdp_attribute16,
1618 p_pdp_attribute17 => p_pdp_attribute17,
1619 p_pdp_attribute18 => p_pdp_attribute18,
1620 p_pdp_attribute19 => p_pdp_attribute19,
1621 p_pdp_attribute20 => p_pdp_attribute20,
1622 p_pdp_attribute21 => p_pdp_attribute21,
1623 p_pdp_attribute22 => p_pdp_attribute22,
1624 p_pdp_attribute23 => p_pdp_attribute23,
1625 p_pdp_attribute24 => p_pdp_attribute24,
1626 p_pdp_attribute25 => p_pdp_attribute25,
1627 p_pdp_attribute26 => p_pdp_attribute26,
1628 p_pdp_attribute27 => p_pdp_attribute27,
1629 p_pdp_attribute28 => p_pdp_attribute28,
1630 p_pdp_attribute29 => p_pdp_attribute29,
1631 p_pdp_attribute30 => p_pdp_attribute30,
1632 p_request_id => fnd_global.conc_request_id,
1633 p_program_application_id => fnd_global.prog_appl_id,
1634 p_program_id => fnd_global.conc_program_id,
1635 p_program_update_date => sysdate,
1636 p_business_group_id => p_business_group_id,
1637 p_effective_date => p_effective_date);
1638 --
1639 p_elig_cvrd_dpnt_id := l_egd_rec.elig_cvrd_dpnt_id;
1640 p_object_version_number := l_pdp_rec.object_version_number;
1641 --
1642 else
1643 --
1644 -- Coverage has not started, so purge the record.
1645 -- Remove the link from eligible dependent record.
1646 --
1647 hr_utility.set_location(l_proc, 65);
1648 --
1649 ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt(
1650 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1651 p_effective_start_date => l_effective_start_date,
1652 p_effective_end_date => l_effective_end_date,
1653 p_object_version_number => l_pdp_rec.object_version_number,
1654 p_business_group_id => p_business_group_id,
1655 p_effective_date => p_effective_date,
1656 p_datetrack_mode => hr_api.g_zap,
1657 p_multi_row_actn => true,
1658 p_called_from => 'beegdapi' );
1659 --
1660 ben_elig_dpnt_api.update_elig_dpnt(
1661 p_elig_dpnt_id => p_elig_dpnt_id,
1662 p_elig_cvrd_dpnt_id => null,
1663 p_effective_date => p_effective_date,
1664 p_business_group_id => p_business_group_id,
1665 p_object_version_number => l_egd_rec.object_version_number,
1666 p_program_application_id => fnd_global.prog_appl_id,
1667 p_program_id => fnd_global.conc_program_id,
1668 p_request_id => fnd_global.conc_request_id,
1669 p_program_update_date => sysdate);
1670 --
1671 p_elig_cvrd_dpnt_id := null;
1672 p_object_version_number := null;
1673 p_effective_start_date := null;
1674 p_effective_end_date := null;
1675 --
1676 end if;
1677 --
1678 elsif /* l_pdp_found and
1679 p_effective_date between
1680 l_pdp_rec.cvg_strt_dt and l_pdp_rec.cvg_thru_dt then
1681 */
1682 --
1683 -- Bug 3138982 if we remove a dependent accidentally and the
1684 -- coverage end date code is in the past like one day before event
1685 -- we dont come into this clause with the above condition and we try
1686 -- to create a new pdp records which will result in 91651 error.
1687 -- We need to look at the new coverage start date also.
1688 --
1689 l_pdp_found and
1690 ((p_effective_date between
1691 l_pdp_rec.cvg_strt_dt and l_pdp_rec.cvg_thru_dt) OR
1692 ( p_effective_date >=l_pdp_rec.effective_start_date and
1693 p_cvg_strt_dt = l_pdp_rec.cvg_strt_dt) or
1694 (p_effective_date between
1695 l_pdp_rec.effective_start_date and l_pdp_rec.effective_end_date and -----Bug 9051021
1696 l_pdp_rec.effective_end_date = hr_api.g_eot and
1697 l_pdp_rec.cvg_strt_dt between
1698 l_pdp_rec.effective_start_date and l_pdp_rec.effective_end_date and
1699 l_pdp_rec.cvg_thru_dt = hr_api.g_eot)) then
1700
1701 --
1702 -- As we have reached here, it means the coverge through date is EOT,
1703 -- and hence we are not trying to de-enroll the dependent.
1704 -- As coverage has started and we are within the coverage period,
1705 -- we can re-open the covered dependent record and use it.
1706 -- Update the record the coverage through date as EOT and the
1707 -- new per in ler id.
1708 --
1709 hr_utility.set_location(l_proc, 70);
1710 --
1711 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1712 p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1713 p_effective_start_date => p_effective_start_date,
1714 p_effective_end_date => p_effective_end_date,
1715 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1716 p_cvg_strt_dt => p_cvg_strt_dt, -- 3453213
1717 p_cvg_thru_dt => hr_api.g_eot,
1718 p_object_version_number => l_pdp_rec.object_version_number,
1719 p_datetrack_mode => l_pdp_datetrack_mode,
1720 p_multi_row_actn => p_multi_row_actn,
1721 p_pdp_attribute_category => p_pdp_attribute_category,
1722 p_pdp_attribute1 => p_pdp_attribute1,
1723 p_pdp_attribute2 => p_pdp_attribute2,
1724 p_pdp_attribute3 => p_pdp_attribute3,
1725 p_pdp_attribute4 => p_pdp_attribute4,
1726 p_pdp_attribute5 => p_pdp_attribute5,
1727 p_pdp_attribute6 => p_pdp_attribute6,
1728 p_pdp_attribute7 => p_pdp_attribute7,
1729 p_pdp_attribute8 => p_pdp_attribute8,
1730 p_pdp_attribute9 => p_pdp_attribute9,
1731 p_pdp_attribute10 => p_pdp_attribute10,
1732 p_pdp_attribute11 => p_pdp_attribute11,
1733 p_pdp_attribute12 => p_pdp_attribute12,
1734 p_pdp_attribute13 => p_pdp_attribute13,
1735 p_pdp_attribute14 => p_pdp_attribute14,
1736 p_pdp_attribute15 => p_pdp_attribute15,
1737 p_pdp_attribute16 => p_pdp_attribute16,
1738 p_pdp_attribute17 => p_pdp_attribute17,
1739 p_pdp_attribute18 => p_pdp_attribute18,
1740 p_pdp_attribute19 => p_pdp_attribute19,
1741 p_pdp_attribute20 => p_pdp_attribute20,
1742 p_pdp_attribute21 => p_pdp_attribute21,
1743 p_pdp_attribute22 => p_pdp_attribute22,
1744 p_pdp_attribute23 => p_pdp_attribute23,
1745 p_pdp_attribute24 => p_pdp_attribute24,
1746 p_pdp_attribute25 => p_pdp_attribute25,
1747 p_pdp_attribute26 => p_pdp_attribute26,
1748 p_pdp_attribute27 => p_pdp_attribute27,
1749 p_pdp_attribute28 => p_pdp_attribute28,
1750 p_pdp_attribute29 => p_pdp_attribute29,
1751 p_pdp_attribute30 => p_pdp_attribute30,
1752 p_request_id => fnd_global.conc_request_id,
1753 p_program_application_id => fnd_global.prog_appl_id,
1754 p_program_id => fnd_global.conc_program_id,
1755 p_program_update_date => sysdate,
1756 p_business_group_id => p_business_group_id,
1757 p_effective_date => p_effective_date);
1758 --
1759 p_elig_cvrd_dpnt_id := l_egd_rec.elig_cvrd_dpnt_id;
1760 p_object_version_number := l_pdp_rec.object_version_number;
1761 --
1762 elsif p_cvg_strt_dt is not null then
1763 --
1764 -- Now the only left case, I could think of. But most important.
1765 -- Here we are trying to enroll the dependent for whom no covered
1766 -- dependent records were found and it needs to be created.
1767 -- Create the covered dependent record with the coverage start date
1768 -- and coverge through date.
1769 -- Update the eligible dependent record with the covered dependent id.
1770 --
1771 hr_utility.set_location(l_proc, 75);
1772 --
1773 ben_elig_cvrd_dpnt_api.create_elig_cvrd_dpnt
1774 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1775 p_effective_start_date => p_effective_start_date,
1776 p_effective_end_date => p_effective_end_date,
1777 p_business_group_id => p_business_group_id,
1778 p_dpnt_person_id => l_egd_rec.dpnt_person_id,
1779 p_per_in_ler_id => l_egd_rec.per_in_ler_id,
1780 p_cvg_strt_dt => p_cvg_strt_dt,
1781 p_cvg_thru_dt => hr_api.g_eot,
1782 p_prtt_enrt_rslt_id => l_egd_rec.prtt_enrt_rslt_id,
1783 p_object_version_number => p_object_version_number,
1784 p_effective_date => p_effective_date,
1785 p_multi_row_actn => p_multi_row_actn,
1786 p_pdp_attribute_category => p_pdp_attribute_category,
1787 p_pdp_attribute1 => p_pdp_attribute1,
1788 p_pdp_attribute2 => p_pdp_attribute2,
1789 p_pdp_attribute3 => p_pdp_attribute3,
1790 p_pdp_attribute4 => p_pdp_attribute4,
1791 p_pdp_attribute5 => p_pdp_attribute5,
1792 p_pdp_attribute6 => p_pdp_attribute6,
1793 p_pdp_attribute7 => p_pdp_attribute7,
1794 p_pdp_attribute8 => p_pdp_attribute8,
1795 p_pdp_attribute9 => p_pdp_attribute9,
1796 p_pdp_attribute10 => p_pdp_attribute10,
1797 p_pdp_attribute11 => p_pdp_attribute11,
1798 p_pdp_attribute12 => p_pdp_attribute12,
1799 p_pdp_attribute13 => p_pdp_attribute13,
1800 p_pdp_attribute14 => p_pdp_attribute14,
1801 p_pdp_attribute15 => p_pdp_attribute15,
1802 p_pdp_attribute16 => p_pdp_attribute16,
1803 p_pdp_attribute17 => p_pdp_attribute17,
1804 p_pdp_attribute18 => p_pdp_attribute18,
1805 p_pdp_attribute19 => p_pdp_attribute19,
1806 p_pdp_attribute20 => p_pdp_attribute20,
1807 p_pdp_attribute21 => p_pdp_attribute21,
1808 p_pdp_attribute22 => p_pdp_attribute22,
1809 p_pdp_attribute23 => p_pdp_attribute23,
1810 p_pdp_attribute24 => p_pdp_attribute24,
1811 p_pdp_attribute25 => p_pdp_attribute25,
1812 p_pdp_attribute26 => p_pdp_attribute26,
1813 p_pdp_attribute27 => p_pdp_attribute27,
1814 p_pdp_attribute28 => p_pdp_attribute28,
1815 p_pdp_attribute29 => p_pdp_attribute29,
1816 p_pdp_attribute30 => p_pdp_attribute30,
1817 p_program_application_id => fnd_global.prog_appl_id,
1818 p_program_id => fnd_global.conc_program_id,
1819 p_request_id => fnd_global.conc_request_id,
1820 p_program_update_date => sysdate);
1821 --
1822 ben_elig_dpnt_api.update_elig_dpnt(
1823 p_elig_dpnt_id => p_elig_dpnt_id,
1824 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1825 p_effective_date => p_effective_date,
1826 p_business_group_id => p_business_group_id,
1827 p_object_version_number => l_egd_rec.object_version_number,
1828 p_program_application_id => fnd_global.prog_appl_id,
1829 p_program_id => fnd_global.conc_program_id,
1830 p_request_id => fnd_global.conc_request_id,
1831 p_program_update_date => sysdate);
1832 --
1833 end if;
1834 --
1835 -- End of cases.
1836 --
1837 hr_utility.set_location('Leaving '||l_proc, 75);
1838 --
1839 -- When in validation only mode raise the Validate_Enabled exception
1840 --
1841 if p_validate then
1842 raise hr_api.validate_enabled;
1843 end if;
1844 --
1845 exception
1846 --
1847 when hr_api.validate_enabled then
1848 --
1849 -- As the Validate_Enabled exception has been raised
1850 -- we must rollback to the savepoint
1851 --
1852 ROLLBACK TO process_dependent;
1853 --
1854 when others then
1855 --
1856 -- A validation or unexpected error has occured
1857 --
1858 ROLLBACK TO process_dependent;
1859 /* Inserted for nocopy changes */
1860 p_elig_cvrd_dpnt_id := null;
1861 p_object_version_number := l_object_version_number;
1862 p_effective_start_date := null;
1863 p_effective_end_date := null;
1864 raise;
1865 --
1866 end process_dependent;
1867 --
1868 -- Procedure: process_dependent_w.
1869 -- Purpose : Used as a self service wrapper
1870 -- Called from: self service dependents selection screen
1871 --
1872 procedure process_dependent_w(p_validate in varchar2,
1873 p_elig_dpnt_id in number,
1874 p_business_group_id in number,
1875 p_effective_date in date,
1876 p_cvg_strt_dt in date,
1877 p_cvg_thru_dt in date,
1878 p_datetrack_mode in varchar2,
1879 p_elig_cvrd_dpnt_id out nocopy number,
1880 p_effective_start_date out nocopy date,
1881 p_effective_end_date out nocopy date,
1882 p_object_version_number in out nocopy number
1883 ,p_multi_row_actn in varchar2)
1884 IS
1885
1886 l_proc varchar2(72) := g_package||'.process_dependent - wrapper';
1887
1888 l_object_version_number number;
1889 l_elig_cvrd_dpnt_id number;
1890 l_effective_start_date date;
1891 l_effective_end_date date;
1892
1893 l_validate BOOLEAN;
1894 l_multi_row_actn BOOLEAN;
1895 --
1896 begin
1897 --
1898 fnd_msg_pub.initialize;
1899 hr_utility.set_location('Entering:'|| l_proc, 10);
1900 --
1901 -- Issue a savepoint if operating in validation only mode
1902 --
1903 savepoint process_dependent_w;
1904 --
1905 if fnd_global.conc_request_id in (0,-1) then
1906 --
1907 ben_env_object.init(p_business_group_id => p_business_group_id,
1908 p_effective_date => p_effective_date,
1909 p_thread_id => 1,
1910 p_chunk_size => 1,
1911 p_threads => 1,
1912 p_max_errors => 1,
1913 p_benefit_action_id => null);
1914 --
1915 end if;
1916 --
1917 hr_utility.set_location(l_proc, 20);
1918 --
1919 if upper(p_validate) = 'TRUE'
1920 then
1921 l_validate := TRUE;
1922 else
1923 l_validate := FALSE;
1924 end if;
1925
1926 if upper(p_multi_row_actn) = 'TRUE'
1927 then
1928 l_multi_row_actn := TRUE;
1929 else
1930 l_multi_row_actn := FALSE;
1931 end if;
1932 --
1933 hr_utility.set_location(l_proc, 30);
1934 --
1935 process_dependent(
1936 p_validate => l_validate
1937 ,p_elig_dpnt_id => p_elig_dpnt_id
1938 ,p_business_group_id => p_business_group_id
1939 ,p_effective_date => p_effective_date
1940 ,p_cvg_strt_dt => p_cvg_strt_dt
1941 ,p_cvg_thru_dt => p_cvg_thru_dt
1942 ,p_datetrack_mode => p_datetrack_mode
1943 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1944 ,p_effective_start_date => l_effective_start_date
1945 ,p_effective_end_date => l_effective_end_date
1946 ,p_object_version_number => p_object_version_number
1947 ,p_multi_row_actn => l_multi_row_actn);
1948 --
1949 if l_validate then
1950 raise hr_api.validate_enabled;
1951 end if;
1952 --
1953 -- Set all output arguments
1954 --
1955 p_elig_cvrd_dpnt_id := l_elig_cvrd_dpnt_id;
1956 p_effective_start_date := l_effective_start_date;
1957 p_effective_end_date := l_effective_end_date;
1958 p_object_version_number := l_object_version_number;
1959 --
1960 hr_utility.set_location(' Leaving:'||l_proc, 40);
1961
1962 exception
1963 --
1964 when hr_api.validate_enabled then
1965 --
1966 -- As the Validate_Enabled exception has been raised
1967 -- we must rollback to the savepoint
1968 --
1969 ROLLBACK TO process_dependent_w;
1970 when app_exception.application_exception then --Bug 4387247
1971 ROLLBACK TO process_dependent_w;
1972 fnd_msg_pub.add;
1973 --Bug 4436578
1974 /* Inserted for nocopy changes */
1975 p_elig_cvrd_dpnt_id := null;
1976 p_object_version_number := l_object_version_number;
1977 p_effective_start_date := null;
1978 p_effective_end_date := null;
1979 when others then
1980 --
1981 -- A validation or unexpected error has occured
1982 --
1983 hr_utility.set_location(' Exception in :'||l_proc, 100);
1984 --Bug 4387247
1985 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1986 fnd_message.set_token('2',substr(sqlerrm,1,200));
1987 fnd_msg_pub.add; -- bug fix 4111762
1988 ROLLBACK TO process_dependent_w;
1989 /* Inserted for nocopy changes */
1990 p_elig_cvrd_dpnt_id := null;
1991 p_object_version_number := l_object_version_number;
1992 p_effective_start_date := null;
1993 p_effective_end_date := null;
1994 --
1995 end process_dependent_w;
1996 --
1997 -- Bug 4114970
1998 -- Procedure: store_crt_ord_warning_ss.
1999 -- Purpose : Used by self service to store court order warnings
2000 -- Called from: self service dependents selection screen
2001 --
2002 procedure store_crt_ord_warning_ss(p_person_id in number,
2003 p_crt_ord_warning in varchar2) is
2004 l_proc varchar2(72) := g_package||'.store_crt_ord_warning_ss';
2005 begin
2006 hr_utility.set_location('Entering:'|| l_proc, 10);
2007 --
2008 if p_person_id is not null and p_crt_ord_warning is not null then
2009 if p_crt_ord_warning = 'CLEAR' then
2010 DELETE ben_online_warnings
2011 WHERE session_id = p_person_id;
2012 else
2013 INSERT INTO ben_online_warnings(session_id, message_text)
2014 VALUES (p_person_id, p_crt_ord_warning);
2015 end if;
2016 end if;
2017 --
2018 hr_utility.set_location('Leaving'|| l_proc, 20);
2019 exception
2020 when others then
2021 --
2022 -- An unexpected error has occured
2023 --
2024 hr_utility.set_location(' Exception in :'||l_proc, 100);
2025 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2026 fnd_message.set_token('2',substr(sqlerrm,1,200));
2027 fnd_msg_pub.add;
2028 end store_crt_ord_warning_ss;
2029 --
2030 -- Bug 4114970
2031 -- Procedure: store_crt_ord_warng_DDNA.
2032 -- DDNA => Dependent Designation Not Allowed (plan-options)
2033 --
2034 -- Purpose : Used by self service to store court order warnings for those plan-options
2035 -- in which dependent designation is not allowed e.g. emp only option.
2036 -- NOTE : This procedure serves similar purpose in SS as bepenrhi.crt_ordr_warning in PUI
2037 -- So care should be taken to keep them in functional sync.
2038 --
2039 -- Called from: self service dependents selection screen
2040 --
2041 procedure store_crt_ord_warng_DDNA(p_person_id in number,
2042 p_per_in_ler_id in number,
2043 p_pgm_id in number,
2044 p_effective_date in date,
2045 p_business_group_id in number)
2046 is
2047 --
2048 cursor c_crt_ordr_DDNA is
2049 SELECT pen.person_id,
2050 pln.pl_id,
2051 typ.pl_typ_id,
2052 (typ.name || ' - ' || pln.name) comp_obj_name
2053 FROM ben_prtt_enrt_rslt_f pen,
2054 ben_pl_f pln,
2055 ben_pl_typ_f typ,
2056 ben_elig_per_elctbl_chc chc
2057 WHERE chc.per_in_ler_id = p_per_in_ler_id
2058 AND chc.pgm_id = p_pgm_id
2059 AND chc.alws_dpnt_dsgn_flag = 'N'
2060 AND pen.prtt_enrt_rslt_id = chc.prtt_enrt_rslt_id
2061 AND pen.prtt_enrt_rslt_stat_cd IS NULL
2062 AND p_effective_date between pen.effective_start_date
2063 and pen.effective_end_date
2064 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
2065 AND pen.pl_id = pln.pl_id
2066 AND pln.pl_stat_cd = 'A'
2067 AND pln.svgs_pl_flag <> 'Y'
2068 AND pln.alws_qmcso_flag = 'Y'
2069 AND p_effective_date between pln.effective_start_date
2070 and pln.effective_end_date
2071 AND pln.pl_typ_id = typ.pl_typ_id
2072 AND p_effective_date between typ.effective_start_date
2073 and typ.effective_end_date
2074 AND EXISTS(SELECT null
2075 FROM ben_crt_ordr crt,
2076 ben_per_in_ler pil
2077 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
2078 AND crt.person_id = pen.person_id
2079 AND (crt.pl_id = pln.pl_id or crt.pl_typ_id = typ.pl_typ_id)
2080 AND crt.business_group_id = p_business_group_id
2081 AND pil.per_in_ler_id = pen.per_in_ler_id
2082 AND greatest(pen.enrt_cvg_strt_dt, pil.lf_evt_ocrd_dt)
2083 between greatest(nvl(crt.apls_perd_strtg_dt, p_effective_date)
2084 ,nvl(crt.detd_qlfd_ordr_dt, crt.apls_perd_strtg_dt)
2085 )
2086 and nvl(crt.apls_perd_endg_dt, pen.enrt_cvg_thru_dt)
2087 )
2088 AND NOT EXISTS
2089 (SELECT 1
2090 FROM ben_prtt_enrt_rslt_f pen2
2091 where pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
2092 and pen2.sspndd_flag = 'Y'
2093 and pen2.prtt_enrt_rslt_stat_cd is null
2094 and p_effective_date between
2095 pen2.effective_start_date and pen2.effective_end_date
2096 and pen2.enrt_cvg_thru_dt = hr_api.g_eot);
2097 --
2098 l_comp_obj_list varchar2(2000) := null;
2099 l_proc varchar2(72) := g_package||'.store_crt_ord_warng_DDNA';
2100 begin
2101 hr_utility.set_location('Entering:'|| l_proc, 10);
2102 --
2103 for l_results in c_crt_ordr_DDNA loop
2104 if l_comp_obj_list is null then
2105 l_comp_obj_list := l_results.comp_obj_name;
2106 else
2107 l_comp_obj_list := l_comp_obj_list || ' , ' || l_results.comp_obj_name;
2108 end if;
2109 end loop;
2110
2111 if l_comp_obj_list is not null then
2112 fnd_message.set_name('BEN', 'BEN_94486_CRT_ORD_WARNING_DDNA');
2113 fnd_message.set_token('PARAM', l_comp_obj_list);
2114
2115 INSERT INTO ben_online_warnings(session_id, message_text)
2116 VALUES (p_person_id, fnd_message.get);
2117 end if;
2118 --
2119 hr_utility.set_location('Leaving'|| l_proc, 20);
2120 exception
2121 when others then
2122 --
2123 -- An unexpected error has occured
2124 --
2125 hr_utility.set_location(' Exception in :'||l_proc, 100);
2126 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2127 fnd_message.set_token('2',substr(sqlerrm,1,200));
2128 fnd_msg_pub.add;
2129 end store_crt_ord_warng_DDNA;
2130 --
2131 --
2132 -- Function to get the eligible dependent record.
2133 --
2134 function get_elig_dpnt_rec(p_elig_dpnt_id in number,
2135 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2136 return boolean is
2137 --
2138 cursor c_egd is
2139 select egd.*
2140 from ben_elig_dpnt egd
2141 where egd.elig_dpnt_id = p_elig_dpnt_id;
2142 --
2143 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_1';
2144 --
2145 begin
2146 --
2147 hr_utility.set_location('Entering '||l_proc, 10);
2148 --
2149 open c_egd;
2150 fetch c_egd into p_elig_dpnt_rec;
2151 --
2152 if c_egd%notfound then
2153 --
2154 close c_egd;
2155 return false;
2156 --
2157 else
2158 --
2159 close c_egd;
2160 return true;
2161 --
2162 end if;
2163 --
2164 end get_elig_dpnt_rec;
2165 --
2166 --
2167 -- Function to get the eligible dependent record for a covered dependent.
2168 --
2169 function get_elig_dpnt_rec(p_elig_cvrd_dpnt_id in number,
2170 p_effective_date in date,
2171 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2172 return boolean is
2173 --
2174 cursor c_pdp is
2175 select egd.*
2176 from ben_elig_dpnt egd,
2177 ben_elig_cvrd_dpnt_f pdp,
2178 ben_per_in_ler pil
2179 where pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
2180 and pdp.per_in_ler_id = egd.per_in_ler_id
2181 and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
2182 and pdp.dpnt_person_id = egd.dpnt_person_id
2183 and pdp.per_in_ler_id = pil.per_in_ler_id
2184 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2185 and p_effective_date between
2186 pdp.cvg_strt_dt and pdp.cvg_thru_dt
2187 --- Fido fix medged by tilak # 2931919
2188 and pdp.cvg_thru_dt <= pdp.effective_end_date ;
2189 --
2190 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_2';
2191 --
2192 begin
2193 --
2194 hr_utility.set_location('Entering '||l_proc, 10);
2195 --
2196 open c_pdp;
2197 fetch c_pdp into p_elig_dpnt_rec;
2198 --
2199 if c_pdp%notfound then
2200 --
2201 close c_pdp;
2202 return false;
2203 --
2204 else
2205 --
2206 close c_pdp;
2207 return true;
2208 --
2209 end if;
2210 --
2211 end get_elig_dpnt_rec;
2212 --
2213 --
2214 -- Function to get the eligible dependent record for a dependent
2215 -- and enrollment result.
2216 --
2217 function get_elig_dpnt_rec(p_dpnt_person_id in number,
2218 p_prtt_enrt_rslt_id in number,
2219 p_effective_date in date,
2220 p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype)
2221 return boolean is
2222 --
2223 cursor c_pen is
2224 select egd.*
2225 from ben_elig_dpnt egd,
2226 ben_elig_cvrd_dpnt_f pdp,
2227 ben_prtt_enrt_rslt_f pen,
2228 ben_per_in_ler pil
2229 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2230 and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2231 and pdp.dpnt_person_id = p_dpnt_person_id
2232 and pdp.per_in_ler_id = egd.per_in_ler_id
2233 and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
2234 and pdp.dpnt_person_id = egd.dpnt_person_id
2235 and pdp.per_in_ler_id = pil.per_in_ler_id
2236 and pen.prtt_enrt_rslt_stat_cd is null
2237 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2238 and p_effective_date between
2239 pen.effective_start_date and pen.effective_end_date
2240 and p_effective_date between
2241 pdp.cvg_strt_dt and pdp.cvg_thru_dt;
2242 --
2243 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_3';
2244 --
2245 begin
2246 --
2247 hr_utility.set_location('Entering '||l_proc, 10);
2248 --
2249 open c_pen;
2250 fetch c_pen into p_elig_dpnt_rec;
2251 --
2252 if c_pen%notfound then
2253 --
2254 close c_pen;
2255 return false;
2256 --
2257 else
2258 --
2259 close c_pen;
2260 return true;
2261 --
2262 end if;
2263 --
2264 end get_elig_dpnt_rec;
2265 --
2266 -- Function to get the eligible dependent record for a dependent person
2267 -- and comp. object (combination of pgm/pl/oipl).
2268 --
2269 function get_elig_dpnt_rec
2270 (p_pgm_id in number default null
2271 ,p_pl_id in number default null
2272 ,p_oipl_id in number default null
2273 ,p_dpnt_person_id in number
2274 ,p_effective_date in date
2275 --
2276 ,p_per_in_ler_id in number default null
2277 ,p_elig_per_id in number default null
2278 ,p_elig_per_opt_id in number default null
2279 ,p_opt_id in number default null
2280 --
2281 ,p_elig_dpnt_rec out nocopy ben_elig_dpnt%rowtype
2282 )
2283 return boolean
2284 is
2285 --
2286 l_pep_id_va benutils.g_number_table := benutils.g_number_table();
2287 l_epo_id_va benutils.g_number_table := benutils.g_number_table();
2288 --
2289 cursor c_eligdpnt_exists
2290 is
2291 select null
2292 from ben_elig_dpnt egd
2293 where egd.dpnt_person_id = p_dpnt_person_id;
2294 --
2295 cursor c_oipl_max_create_dt
2296 (c_dpnt_person_id in number
2297 ,c_effective_date in date
2298 ,c_oipl_id in number
2299 ,c_pgm_id in number
2300 )
2301 is
2302 select egd.elig_dpnt_id
2303 from ben_elig_dpnt egd,
2304 ben_elig_per_opt_f epo,
2305 ben_elig_per_f pep,
2306 ben_per_in_ler pil,
2307 ben_oipl_f oipl
2308 where egd.dpnt_person_id = c_dpnt_person_id
2309 and egd.elig_per_opt_id = epo.elig_per_opt_id
2310 and egd.elig_per_id = epo.elig_per_id
2311 and c_effective_date between
2312 epo.effective_start_date and epo.effective_end_date
2313 and epo.elig_per_id = pep.elig_per_id
2314 and c_effective_date between
2315 pep.effective_start_date and pep.effective_end_date
2316 and oipl.pl_id = pep.pl_id
2317 and c_effective_date between
2318 oipl.effective_start_date and oipl.effective_end_date
2319 and oipl.oipl_id = c_oipl_id
2320 and oipl.opt_id = epo.opt_id
2321 and nvl(pep.pgm_id, -1) = c_pgm_id
2322 and egd.per_in_ler_id = pil.per_in_ler_id
2323 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
2324 order by egd.create_dt desc;
2325 --
2326 cursor c_pl_max_create_dt
2327 (c_dpnt_person_id number
2328 ,c_pl_id number
2329 ,c_pgm_id number
2330 ,c_effective_date date
2331 )
2332 is
2333 select egd.elig_dpnt_id
2334 from ben_elig_dpnt egd,
2335 ben_per_in_ler pil,
2336 ben_elig_per_f pep
2337 where egd.dpnt_person_id = c_dpnt_person_id
2338 and egd.elig_per_opt_id is null
2339 and egd.elig_per_id = pep.elig_per_id
2340 and pep.pl_id = c_pl_id
2341 and nvl(pep.pgm_id, -1) = nvl(c_pgm_id, -1)
2342 and egd.per_in_ler_id = pil.per_in_ler_id
2343 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
2344 and c_effective_date
2345 between pep.effective_start_date and pep.effective_end_date
2346 order by egd.create_dt desc;
2347 --
2348 cursor c_pilepodets
2349 (c_effective_date in date
2350 ,c_per_in_ler_id in number
2351 ,c_pl_id in number
2352 ,c_opt_id in number
2353 ,c_pgm_id in number
2354 )
2355 is
2356 select epo.elig_per_opt_id,
2357 epo.elig_per_id
2358 from ben_elig_per_opt_f epo,
2359 ben_elig_per_f pep
2360 where epo.per_in_ler_id = c_per_in_ler_id
2361 and c_effective_date between
2362 epo.effective_start_date and epo.effective_end_date
2363 and epo.elig_per_id = pep.elig_per_id
2364 and c_effective_date between
2365 pep.effective_start_date and pep.effective_end_date
2366 and pep.pl_id = c_pl_id
2367 and epo.opt_id = c_opt_id
2368 and nvl(pep.pgm_id, -1) = c_pgm_id;
2369 --
2370 cursor c_epoegddets
2371 (c_elig_per_opt_id in number
2372 ,c_elig_per_id in number
2373 ,c_dpnt_person_id in number
2374 ,c_pil_id in number
2375 )
2376 is
2377 select egd.elig_dpnt_id
2378 from ben_elig_dpnt egd
2379 where egd.elig_per_opt_id = c_elig_per_opt_id
2380 and egd.elig_per_id = c_elig_per_id
2381 and egd.dpnt_person_id = c_dpnt_person_id
2382 and egd.per_in_ler_id = c_pil_id
2383 order by egd.create_dt desc;
2384 --
2385 cursor c_pepegddets
2386 (c_elig_per_id in number
2387 ,c_dpnt_person_id in number
2388 ,c_pil_id in number
2389 )
2390 is
2391 select egd.elig_dpnt_id
2392 from ben_elig_dpnt egd
2393 where egd.elig_per_id = c_elig_per_id
2394 and egd.elig_per_opt_id is null
2395 and egd.dpnt_person_id = c_dpnt_person_id
2396 and egd.per_in_ler_id = c_pil_id;
2397 --
2398 l_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%type := null;
2399 l_return boolean := false;
2400 l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_4';
2401 --
2402 begin
2403 --
2404 hr_utility.set_location('Entering '||l_proc, 10);
2405 --
2406 -- Check if the elig dependent exists
2407 --
2408 open c_eligdpnt_exists;
2409 fetch c_eligdpnt_exists into l_elig_dpnt_id;
2410 --
2411 if c_eligdpnt_exists%found then
2412 --
2413 if p_oipl_id is not null then
2414 --
2415 -- Currently only supported from benmngle where PIL ID is passed.
2416 -- This logic was added because of severe performance for customers
2417 -- with enormous amounts of eligible rows.
2418 --
2419 if p_per_in_ler_id is not null
2420 then
2421 --
2422 -- Check if the PEP ID and EPO ID are set from benmngle
2423 --
2424 if p_elig_per_id is not null
2425 and p_elig_per_opt_id is not null
2426 then
2427 --
2428 -- Set the PEPID and EPOID to the value passed from benmngle
2429 --
2430 l_pep_id_va.extend(1);
2431 l_pep_id_va(1) := p_elig_per_id;
2432 --
2433 l_epo_id_va.extend(1);
2434 l_epo_id_va(1) := p_elig_per_opt_id;
2435 --
2436 else
2437 --
2438 -- When eligibility for the current PILID is found then use the
2439 -- performant code. However if for some special cases no current
2440 -- eligibility rows are found for the comp object then revert to the
2441 -- original unperformant cursor.
2442 --
2443 hr_utility.set_location(l_proc, 20);
2444 open c_pilepodets
2445 (c_effective_date => p_effective_date
2446 ,c_per_in_ler_id => p_per_in_ler_id
2447 ,c_pl_id => p_pl_id
2448 ,c_opt_id => p_opt_id
2449 ,c_pgm_id => nvl(p_pgm_id, -1)
2450 );
2451 fetch c_pilepodets BULK COLLECT INTO l_epo_id_va, l_pep_id_va;
2452 close c_pilepodets;
2453 --
2454 end if;
2455 --
2456 if l_epo_id_va.count > 0 then
2457 --
2458 for epoelenum in l_epo_id_va.first..l_epo_id_va.last
2459 loop
2460 --
2461 open c_epoegddets
2462 (c_elig_per_opt_id => l_epo_id_va(epoelenum)
2463 ,c_elig_per_id => l_pep_id_va(epoelenum)
2464 ,c_dpnt_person_id => p_dpnt_person_id
2465 ,c_pil_id => p_per_in_ler_id
2466 );
2467 fetch c_epoegddets into l_elig_dpnt_id;
2468 if c_epoegddets%notfound then
2469 --
2470 l_elig_dpnt_id := null;
2471 --
2472 end if;
2473 close c_epoegddets;
2474 --
2475 end loop;
2476 --
2477 else
2478 --
2479 open c_oipl_max_create_dt
2480 (c_dpnt_person_id => p_dpnt_person_id
2481 ,c_effective_date => p_effective_date
2482 ,c_oipl_id => p_oipl_id
2483 ,c_pgm_id => nvl(p_pgm_id, -1)
2484 );
2485 fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2486 close c_oipl_max_create_dt;
2487 --
2488 end if;
2489 --
2490 hr_utility.set_location(l_proc, 25);
2491 --
2492 else
2493 --
2494 hr_utility.set_location(l_proc, 21);
2495 open c_oipl_max_create_dt
2496 (c_dpnt_person_id => p_dpnt_person_id
2497 ,c_effective_date => p_effective_date
2498 ,c_oipl_id => p_oipl_id
2499 ,c_pgm_id => nvl(p_pgm_id, -1)
2500 );
2501 fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2502 close c_oipl_max_create_dt;
2503 hr_utility.set_location(l_proc, 26);
2504 --
2505 end if;
2506 --
2507 else
2508 --
2509 -- When the PEP ID and PIL ID are set fire the performant SQL
2510 --
2511 if p_elig_per_id is not null
2512 and p_per_in_ler_id is not null
2513 then
2514 --
2515 open c_pepegddets
2516 (c_elig_per_id => p_elig_per_id
2517 ,c_dpnt_person_id => p_dpnt_person_id
2518 ,c_pil_id => p_per_in_ler_id
2519 );
2520 fetch c_pepegddets into l_elig_dpnt_id;
2521 close c_pepegddets;
2522 --
2523 else
2524 --
2525 open c_pl_max_create_dt
2526 (c_dpnt_person_id => p_dpnt_person_id
2527 ,c_pl_id => p_pl_id
2528 ,c_pgm_id => p_pgm_id
2529 ,c_effective_date => p_effective_date
2530 );
2531 fetch c_pl_max_create_dt into l_elig_dpnt_id;
2532 close c_pl_max_create_dt;
2533 --
2534 end if;
2535 hr_utility.set_location(l_proc, 30);
2536 --
2537 end if;
2538 --
2539 end if;
2540 --
2541 close c_eligdpnt_exists;
2542 --
2543 if l_elig_dpnt_id is not null then
2544 --
2545 hr_utility.set_location(l_proc, 40);
2546 --
2547 l_return := get_elig_dpnt_rec(p_elig_dpnt_id => l_elig_dpnt_id,
2548 p_elig_dpnt_rec => p_elig_dpnt_rec);
2549 --
2550 end if;
2551 --
2552 hr_utility.set_location('Leaving '||l_proc, 10);
2553 --
2554 return l_return;
2555 --
2556 end get_elig_dpnt_rec;
2557 --
2558 -- Procedure to obtain the eligible person id's for a participant
2559 -- and the comp. object (combination of pgm/pl/oipl).
2560 --
2561 procedure get_elig_per_id(p_person_id in number,
2562 p_pgm_id in number default null,
2563 p_pl_id in number default null,
2564 p_oipl_id in number default null,
2565 p_business_group_id in number,
2566 p_effective_date in date,
2567 p_elig_per_id out nocopy number,
2568 p_elig_per_opt_id out nocopy number) is
2569 --
2570 cursor c_oipl is
2571 select epo.elig_per_id,
2572 epo.elig_per_opt_id
2573 from ben_elig_per_opt_f epo,
2574 ben_elig_per_f pep,
2575 ben_oipl_f oipl
2576 where oipl.oipl_id = p_oipl_id
2577 and pep.pl_id = oipl.pl_id
2578 and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
2579 and pep.person_id = p_person_id
2580 and pep.business_group_id = p_business_group_id
2581 and oipl.opt_id = epo.opt_id
2582 and epo.elig_per_id = pep.elig_per_id
2583 and pep.elig_flag = 'Y'
2584 and epo.elig_flag = 'Y'
2585 and p_effective_date between
2586 oipl.effective_start_date and oipl.effective_end_date
2587 and p_effective_date between
2588 pep.effective_start_date and pep.effective_end_date
2589 and p_effective_date between
2590 epo.effective_start_date and epo.effective_end_date;
2591 --
2592 cursor c_pl is
2593 select pep.elig_per_id
2594 from ben_elig_per_f pep
2595 where pep.pl_id = p_pl_id
2596 and pep.pgm_id = nvl(p_pgm_id, pep.pgm_id)
2597 and pep.person_id = p_person_id
2598 and pep.business_group_id = p_business_group_id
2599 and pep.elig_flag = 'Y'
2600 and p_effective_date between
2601 pep.effective_start_date and pep.effective_end_date;
2602 --
2603 l_proc varchar2(72) := g_package||'get_elig_per_id';
2604 --
2605 begin
2606 --
2607 hr_utility.set_location('Entering '||l_proc, 10);
2608 --
2609 p_elig_per_id := null;
2610 p_elig_per_opt_id := null;
2611 --
2612 if p_oipl_id is not null then
2613 --
2614 hr_utility.set_location(l_proc, 20);
2615 --
2616 open c_oipl;
2617 fetch c_oipl into p_elig_per_id, p_elig_per_opt_id;
2618 close c_oipl;
2619 --
2620 else
2621 --
2622 hr_utility.set_location(l_proc, 30);
2623 --
2624 open c_pl;
2625 fetch c_pl into p_elig_per_id;
2626 close c_pl;
2627 --
2628 end if;
2629 --
2630 hr_utility.set_location('Leaving '||l_proc, 10);
2631 --
2632 end get_elig_per_id;
2633 --
2634 -- Bug No 4931912
2635 -- Function to return the court order type defined for a person.
2636 --
2637 procedure get_crt_ordr_typ(p_person_id in number,
2638 p_pl_id in number,
2639 p_pl_typ_id in number,
2640 l_crt_ordr_meaning out nocopy varchar2)
2641 IS
2642 cursor get_crt_ordr_typ
2643 is SELECT crt.CRT_ORDR_TYP_CD
2644 FROM ben_crt_ordr crt
2645 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
2646 AND crt.person_id = p_person_id
2647 AND crt.pl_id = p_pl_id or crt.pl_typ_id = p_pl_typ_id;
2648
2649 l_crt_ordr_typ_cd VARCHAR2(30);
2650 begin
2651 open get_crt_ordr_typ;
2652 fetch get_crt_ordr_typ into l_crt_ordr_typ_cd;
2653 close get_crt_ordr_typ;
2654 l_crt_ordr_meaning := hr_general.decode_lookup
2655 (p_lookup_type => 'BEN_CRT_ORDR_TYP',
2656 p_lookup_code => l_crt_ordr_typ_cd
2657 );
2658 end get_crt_ordr_typ;
2659 --
2660
2661
2662 /* Added procedure for Bug 8414373 */
2663 procedure chk_enrt_for_dpnt
2664 (
2665 p_dpnt_person_id in number
2666 ,p_dpnt_rltp_id in number
2667 ,p_rltp_type in varchar2
2668 ,p_business_group_id in number
2669 ) is
2670
2671 cursor c_chk_enrt (c_rltp_start_date date,c_rltp_end_date date) is
2672 select 'Y' from ben_elig_cvrd_dpnt_f ecd,
2673 ben_prtt_enrt_rslt_f pen,
2674 ben_per_in_ler pil
2675 where ecd.dpnt_person_id = p_dpnt_person_id
2676 and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2677 and pen.prtt_enrt_rslt_stat_cd is null
2678 and ecd.per_in_ler_id = pil.per_in_ler_id
2679 and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
2680 and ecd.cvg_thru_dt >= ecd.cvg_strt_dt
2681 and ecd.cvg_strt_dt between c_rltp_start_date and c_rltp_end_date
2682 and pen.business_group_id = p_business_group_id
2683 and pil.business_group_id = p_business_group_id
2684 and ecd.business_group_id = p_business_group_id;
2685
2686 cursor c_rltp is
2687 select * from per_contact_relationships
2688 where contact_person_id = p_dpnt_person_id
2689 and contact_relationship_id = p_dpnt_rltp_id;
2690
2691 l_dpnt_rltp_row c_rltp%rowtype;
2692
2693 l_proc varchar2(72) := g_package||'chk_enrt_for_dpnt';
2694 l_flag varchar2(2);
2695 l_rltp_start_date date;
2696 l_rltp_end_date date;
2697 begin
2698 hr_utility.set_location('Entering '||l_proc, 10);
2699 open c_rltp;
2700 fetch c_rltp into l_dpnt_rltp_row;
2701 close c_rltp;
2702
2703 if(l_dpnt_rltp_row.date_start is not null and l_dpnt_rltp_row.personal_flag = 'Y' and (p_rltp_type <> l_dpnt_rltp_row.contact_type) ) then
2704 hr_utility.set_location('Date not null ', 10);
2705 open c_chk_enrt(l_dpnt_rltp_row.date_start,nvl(l_dpnt_rltp_row.date_end,hr_api.g_eot));
2706 fetch c_chk_enrt into l_flag;
2707 if(c_chk_enrt%found) then
2708 hr_utility.set_location('Enrollment Rec exists for Dep ', 10);
2709 fnd_message.set_name('BEN','BEN_94714_RELATION_TYP_UPD');
2710 fnd_message.raise_error;
2711 --raise error message;
2712 end if;
2713 close c_chk_enrt;
2714 end if;
2715 hr_utility.set_location('Leaving '||l_proc, 10);
2716 end chk_enrt_for_dpnt;
2717
2718 end ben_ELIG_DPNT_api;