[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_CVRD_DPNT_API
Source
1 Package Body ben_ELIG_CVRD_DPNT_api as
2 /* $Header: bepdpapi.pkb 120.15 2011/03/07 06:40:52 pvelvano noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_ELIG_CVRD_DPNT_api.';
7 --
8 ---- ----------------------------------------------------------------------------
9 -- |------------------------< create_ELIG_CVRD_DPNT >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ELIG_CVRD_DPNT
13 (p_validate in boolean default false
14 ,p_elig_cvrd_dpnt_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_business_group_id in number default null
18 ,p_prtt_enrt_rslt_id in number default null
19 ,p_dpnt_person_id in number default null
20 ,p_cvg_strt_dt in date default null
21 ,p_cvg_thru_dt in date default null
22 ,p_cvg_pndg_flag in varchar2 default 'N'
23 ,p_pdp_attribute_category in varchar2 default null
24 ,p_pdp_attribute1 in varchar2 default null
25 ,p_pdp_attribute2 in varchar2 default null
26 ,p_pdp_attribute3 in varchar2 default null
27 ,p_pdp_attribute4 in varchar2 default null
28 ,p_pdp_attribute5 in varchar2 default null
29 ,p_pdp_attribute6 in varchar2 default null
30 ,p_pdp_attribute7 in varchar2 default null
31 ,p_pdp_attribute8 in varchar2 default null
32 ,p_pdp_attribute9 in varchar2 default null
33 ,p_pdp_attribute10 in varchar2 default null
34 ,p_pdp_attribute11 in varchar2 default null
35 ,p_pdp_attribute12 in varchar2 default null
36 ,p_pdp_attribute13 in varchar2 default null
37 ,p_pdp_attribute14 in varchar2 default null
38 ,p_pdp_attribute15 in varchar2 default null
39 ,p_pdp_attribute16 in varchar2 default null
40 ,p_pdp_attribute17 in varchar2 default null
41 ,p_pdp_attribute18 in varchar2 default null
42 ,p_pdp_attribute19 in varchar2 default null
43 ,p_pdp_attribute20 in varchar2 default null
44 ,p_pdp_attribute21 in varchar2 default null
45 ,p_pdp_attribute22 in varchar2 default null
46 ,p_pdp_attribute23 in varchar2 default null
47 ,p_pdp_attribute24 in varchar2 default null
48 ,p_pdp_attribute25 in varchar2 default null
49 ,p_pdp_attribute26 in varchar2 default null
50 ,p_pdp_attribute27 in varchar2 default null
51 ,p_pdp_attribute28 in varchar2 default null
52 ,p_pdp_attribute29 in varchar2 default null
53 ,p_pdp_attribute30 in varchar2 default null
54 ,p_request_id in number default null
55 ,p_program_application_id in number default null
56 ,p_program_id in number default null
57 ,p_program_update_date in date default null
58 ,p_object_version_number out nocopy number
59 ,p_ovrdn_flag in varchar2 default 'N'
60 ,p_per_in_ler_id in number default null
61 ,p_ovrdn_thru_dt in date default null
62 ,p_effective_date in date
63 ,p_multi_row_actn in boolean default TRUE
64 ) is
65 --
66 -- Declare cursors and local variables
67 --
68 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
69 l_effective_start_date ben_elig_cvrd_dpnt_f.effective_start_date%TYPE;
70 l_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
71 l_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%TYPE;
72 l_proc varchar2(72) := g_package||'create_ELIG_CVRD_DPNT';
73 l_object_version_number ben_elig_cvrd_dpnt_f.object_version_number%TYPE;
74 --
75 cursor c_chg_info (p_prtt_enrt_rslt_id number) is
76 SELECT pen.pl_id,
77 pen.oipl_id,
78 pen.person_id
79 FROM ben_prtt_enrt_rslt_f pen
80 WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
81 AND pen.prtt_enrt_rslt_stat_cd is null
82 AND p_effective_date between pen.effective_start_date and pen.effective_end_date;
83 --
84 l_chg_info c_chg_info%rowtype;
85 --
86 l_env_rec ben_env_object.g_global_env_rec_type;
87 --
88 begin
89 --
90 hr_utility.set_location('Entering:'|| l_proc, 10);
91 --
92 if fnd_global.conc_request_id = -1 then
93 --
94 ben_env_object.get(p_rec => l_env_rec);
95 if l_env_rec.effective_date is null then
96 --
97 ben_env_object.init(p_business_group_id => p_business_group_id,
98 p_effective_date => p_effective_date,
99 p_thread_id => 1,
100 p_chunk_size => 1,
101 p_threads => 1,
102 p_max_errors => 1,
103 p_benefit_action_id => null);
104 --
105 end if;
106 --
107 end if;
108 --
109 -- Issue a savepoint if operating in validation only mode
110 --
111 savepoint create_ELIG_CVRD_DPNT;
112 --
113 l_cvg_thru_dt := p_cvg_thru_dt;
114 --
115 if l_cvg_thru_dt is null then
116 --
117 l_cvg_thru_dt := hr_api.g_eot;
118 --
119 end if;
120 --
121 --
122 hr_utility.set_location(l_proc, 20);
123 --
124 -- Process Logic
125 --
126 begin
127 --
128 -- Start of API User Hook for the before hook of create_ELIG_CVRD_DPNT
129 --
130 ben_ELIG_CVRD_DPNT_bk1.create_ELIG_CVRD_DPNT_b
131 (
132 p_business_group_id => p_business_group_id
133 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
134 ,p_dpnt_person_id => p_dpnt_person_id
135 ,p_cvg_strt_dt => p_cvg_strt_dt
136 ,p_cvg_thru_dt => l_cvg_thru_dt
137 ,p_cvg_pndg_flag => p_cvg_pndg_flag
138 ,p_pdp_attribute_category => p_pdp_attribute_category
139 ,p_pdp_attribute1 => p_pdp_attribute1
140 ,p_pdp_attribute2 => p_pdp_attribute2
141 ,p_pdp_attribute3 => p_pdp_attribute3
142 ,p_pdp_attribute4 => p_pdp_attribute4
143 ,p_pdp_attribute5 => p_pdp_attribute5
144 ,p_pdp_attribute6 => p_pdp_attribute6
145 ,p_pdp_attribute7 => p_pdp_attribute7
146 ,p_pdp_attribute8 => p_pdp_attribute8
147 ,p_pdp_attribute9 => p_pdp_attribute9
148 ,p_pdp_attribute10 => p_pdp_attribute10
149 ,p_pdp_attribute11 => p_pdp_attribute11
150 ,p_pdp_attribute12 => p_pdp_attribute12
151 ,p_pdp_attribute13 => p_pdp_attribute13
152 ,p_pdp_attribute14 => p_pdp_attribute14
153 ,p_pdp_attribute15 => p_pdp_attribute15
154 ,p_pdp_attribute16 => p_pdp_attribute16
155 ,p_pdp_attribute17 => p_pdp_attribute17
156 ,p_pdp_attribute18 => p_pdp_attribute18
157 ,p_pdp_attribute19 => p_pdp_attribute19
158 ,p_pdp_attribute20 => p_pdp_attribute20
159 ,p_pdp_attribute21 => p_pdp_attribute21
160 ,p_pdp_attribute22 => p_pdp_attribute22
161 ,p_pdp_attribute23 => p_pdp_attribute23
162 ,p_pdp_attribute24 => p_pdp_attribute24
163 ,p_pdp_attribute25 => p_pdp_attribute25
164 ,p_pdp_attribute26 => p_pdp_attribute26
165 ,p_pdp_attribute27 => p_pdp_attribute27
166 ,p_pdp_attribute28 => p_pdp_attribute28
167 ,p_pdp_attribute29 => p_pdp_attribute29
168 ,p_pdp_attribute30 => p_pdp_attribute30
169 ,p_request_id => p_request_id
170 ,p_program_application_id => p_program_application_id
171 ,p_program_id => p_program_id
172 ,p_program_update_date => p_program_update_date
173 ,p_ovrdn_flag => p_ovrdn_flag
174 ,p_per_in_ler_id => p_per_in_ler_id
175 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
176 ,p_effective_date => trunc(p_effective_date)
177 );
178 exception
179 when hr_api.cannot_find_prog_unit then
180 hr_api.cannot_find_prog_unit_error
181 (
182 p_module_name => 'CREATE_ELIG_CVRD_DPNT'
183 ,p_hook_type => 'BP'
184 );
185 --
186 -- End of API User Hook for the before hook of create_ELIG_CVRD_DPNT
187 --
188 end;
189 --
190 -- dbms_output.put_line('before rhi');
191 ben_pdp_ins.ins
192 (
193 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
194 ,p_effective_start_date => l_effective_start_date
195 ,p_effective_end_date => l_effective_end_date
196 ,p_business_group_id => p_business_group_id
197 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
198 ,p_dpnt_person_id => p_dpnt_person_id
199 ,p_cvg_strt_dt => p_cvg_strt_dt
200 ,p_cvg_thru_dt => l_cvg_thru_dt
201 ,p_cvg_pndg_flag => p_cvg_pndg_flag
202 ,p_pdp_attribute_category => p_pdp_attribute_category
203 ,p_pdp_attribute1 => p_pdp_attribute1
204 ,p_pdp_attribute2 => p_pdp_attribute2
205 ,p_pdp_attribute3 => p_pdp_attribute3
206 ,p_pdp_attribute4 => p_pdp_attribute4
207 ,p_pdp_attribute5 => p_pdp_attribute5
208 ,p_pdp_attribute6 => p_pdp_attribute6
209 ,p_pdp_attribute7 => p_pdp_attribute7
210 ,p_pdp_attribute8 => p_pdp_attribute8
211 ,p_pdp_attribute9 => p_pdp_attribute9
212 ,p_pdp_attribute10 => p_pdp_attribute10
213 ,p_pdp_attribute11 => p_pdp_attribute11
214 ,p_pdp_attribute12 => p_pdp_attribute12
215 ,p_pdp_attribute13 => p_pdp_attribute13
216 ,p_pdp_attribute14 => p_pdp_attribute14
217 ,p_pdp_attribute15 => p_pdp_attribute15
218 ,p_pdp_attribute16 => p_pdp_attribute16
219 ,p_pdp_attribute17 => p_pdp_attribute17
220 ,p_pdp_attribute18 => p_pdp_attribute18
221 ,p_pdp_attribute19 => p_pdp_attribute19
222 ,p_pdp_attribute20 => p_pdp_attribute20
223 ,p_pdp_attribute21 => p_pdp_attribute21
224 ,p_pdp_attribute22 => p_pdp_attribute22
225 ,p_pdp_attribute23 => p_pdp_attribute23
226 ,p_pdp_attribute24 => p_pdp_attribute24
227 ,p_pdp_attribute25 => p_pdp_attribute25
228 ,p_pdp_attribute26 => p_pdp_attribute26
229 ,p_pdp_attribute27 => p_pdp_attribute27
230 ,p_pdp_attribute28 => p_pdp_attribute28
231 ,p_pdp_attribute29 => p_pdp_attribute29
232 ,p_pdp_attribute30 => p_pdp_attribute30
233 ,p_request_id => p_request_id
234 ,p_program_application_id => p_program_application_id
235 ,p_program_id => p_program_id
236 ,p_program_update_date => p_program_update_date
237 ,p_object_version_number => l_object_version_number
238 ,p_ovrdn_flag => p_ovrdn_flag
239 ,p_per_in_ler_id => p_per_in_ler_id
240 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
241 ,p_effective_date => trunc(p_effective_date)
242 );
243 -- dbms_output.put_line('after rhi');
244 --
245 -- create person type usage, if needed
246 --
247 if p_cvg_strt_dt is not null and l_cvg_thru_dt = hr_api.g_eot then
248 --
249 add_usage( p_validate => p_validate
250 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
251 ,p_effective_date => p_effective_date
252 ,p_datetrack_mode => null
253 );
254 --
255 end if;
256 --
257 -- Call Action item RCO if p_multi_row_actn = TRUE
258 --
259 if (p_multi_row_actn and
260 p_cvg_strt_dt is not null and l_cvg_thru_dt = hr_api.g_eot) then
261 --
262 dpnt_actn_items(
263 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
264 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
265 ,p_effective_date => p_effective_date
266 ,p_business_group_id => p_business_group_id
267 ,p_validate => p_validate
268 ,p_datetrack_mode => null
269 );
270 --
271 end if;
272 --
273 -- If processing dependents in the COBRA program.
274 --
275 ben_cobra_requirements.update_dpnt_cobra_info
276 (p_per_in_ler_id => p_per_in_ler_id
277 ,p_person_id => p_dpnt_person_id
278 ,p_business_group_id => p_business_group_id
279 ,p_effective_date => p_effective_date
280 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
281 ,p_validate => p_validate
282 );
283 --
284 -- Added by pxdas for logging change event needed for extract.
285 -- p_erson control added to fox 5399
286
287 hr_utility.set_location('pl id '||l_chg_info.pl_id , 5399);
288 hr_utility.set_location('oipl id '||l_chg_info.oipl_id , 5399);
289 hr_utility.set_location('prt_enrt_rslt id '||p_prtt_enrt_rslt_id , 5399);
290 hr_utility.set_location('person id '||l_chg_info.person_id , 5399);
291 hr_utility.set_location('dpndnt id '||p_dpnt_person_id , 5399);
292 hr_utility.set_location('per_in_ler id '||p_per_in_ler_id , 5399);
293 hr_utility.set_location(' idg_cvrd_dpnt_id '||p_elig_cvrd_dpnt_id , 5399);
294
295 if p_dpnt_person_id is not null then
296 --
297 open c_chg_info (p_prtt_enrt_rslt_id);
298 fetch c_chg_info into l_chg_info;
299 close c_chg_info;
300 --
301 if l_chg_info.person_id is not null then
302 --
303 --
304 -- Call the change event logging process.
305 --
306 ben_ext_chlg.log_dependent_chg
307 (p_action => 'CREATE',
308 p_pl_id => l_chg_info.pl_id,
309 p_oipl_id => l_chg_info.oipl_id,
310 p_cvg_strt_dt => p_cvg_strt_dt,
311 p_cvg_end_dt => l_cvg_thru_dt,
312 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
313 p_per_in_ler_id => p_per_in_ler_id,
314 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
315 p_person_id => l_chg_info.person_id,
316 p_dpnt_person_id => p_dpnt_person_id,
317 p_business_group_id => p_business_group_id,
318 p_effective_date => p_effective_date);
319 --
320 end if;
321 --
322 end if;
323 --
324 -- End logging change event
325 --
326 begin
327 --
328 -- Start of API User Hook for the after hook of create_ELIG_CVRD_DPNT
329 --
330 ben_ELIG_CVRD_DPNT_bk1.create_ELIG_CVRD_DPNT_a
331 (
332 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
333 ,p_effective_start_date => l_effective_start_date
334 ,p_effective_end_date => l_effective_end_date
335 ,p_business_group_id => p_business_group_id
336 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
337 ,p_dpnt_person_id => p_dpnt_person_id
338 ,p_cvg_strt_dt => p_cvg_strt_dt
339 ,p_cvg_thru_dt => l_cvg_thru_dt
340 ,p_cvg_pndg_flag => p_cvg_pndg_flag
341 ,p_pdp_attribute_category => p_pdp_attribute_category
342 ,p_pdp_attribute1 => p_pdp_attribute1
343 ,p_pdp_attribute2 => p_pdp_attribute2
344 ,p_pdp_attribute3 => p_pdp_attribute3
345 ,p_pdp_attribute4 => p_pdp_attribute4
346 ,p_pdp_attribute5 => p_pdp_attribute5
347 ,p_pdp_attribute6 => p_pdp_attribute6
348 ,p_pdp_attribute7 => p_pdp_attribute7
349 ,p_pdp_attribute8 => p_pdp_attribute8
350 ,p_pdp_attribute9 => p_pdp_attribute9
351 ,p_pdp_attribute10 => p_pdp_attribute10
352 ,p_pdp_attribute11 => p_pdp_attribute11
353 ,p_pdp_attribute12 => p_pdp_attribute12
354 ,p_pdp_attribute13 => p_pdp_attribute13
355 ,p_pdp_attribute14 => p_pdp_attribute14
356 ,p_pdp_attribute15 => p_pdp_attribute15
357 ,p_pdp_attribute16 => p_pdp_attribute16
358 ,p_pdp_attribute17 => p_pdp_attribute17
359 ,p_pdp_attribute18 => p_pdp_attribute18
360 ,p_pdp_attribute19 => p_pdp_attribute19
361 ,p_pdp_attribute20 => p_pdp_attribute20
362 ,p_pdp_attribute21 => p_pdp_attribute21
363 ,p_pdp_attribute22 => p_pdp_attribute22
364 ,p_pdp_attribute23 => p_pdp_attribute23
365 ,p_pdp_attribute24 => p_pdp_attribute24
366 ,p_pdp_attribute25 => p_pdp_attribute25
367 ,p_pdp_attribute26 => p_pdp_attribute26
368 ,p_pdp_attribute27 => p_pdp_attribute27
369 ,p_pdp_attribute28 => p_pdp_attribute28
370 ,p_pdp_attribute29 => p_pdp_attribute29
371 ,p_pdp_attribute30 => p_pdp_attribute30
372 ,p_request_id => p_request_id
373 ,p_program_application_id => p_program_application_id
374 ,p_program_id => p_program_id
375 ,p_program_update_date => p_program_update_date
376 ,p_object_version_number => l_object_version_number
377 ,p_ovrdn_flag => p_ovrdn_flag
378 ,p_per_in_ler_id => p_per_in_ler_id
379 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
380 ,p_effective_date => trunc(p_effective_date)
381 );
382 exception
383 when hr_api.cannot_find_prog_unit then
384 hr_api.cannot_find_prog_unit_error
385 (p_module_name => 'CREATE_ELIG_CVRD_DPNT'
386 ,p_hook_type => 'AP'
387 );
388 --
389 -- End of API User Hook for the after hook of create_ELIG_CVRD_DPNT
390 --
391 end;
392 --
393 hr_utility.set_location(l_proc, 60);
394 --
395 -- When in validation only mode raise the Validate_Enabled exception
396 --
397 if p_validate then
398 raise hr_api.validate_enabled;
399 end if;
400 --
401 -- Set all output arguments
402 --
403 p_elig_cvrd_dpnt_id := l_elig_cvrd_dpnt_id;
404 p_effective_start_date := l_effective_start_date;
405 p_effective_end_date := l_effective_end_date;
406 p_object_version_number := l_object_version_number;
407 --
408 hr_utility.set_location(' Leaving:'||l_proc, 70);
409 --
410 exception
411 --
412 when hr_api.validate_enabled then
413 --
414 -- As the Validate_Enabled exception has been raised
415 -- we must rollback to the savepoint
416 --
417 ROLLBACK TO create_ELIG_CVRD_DPNT;
418 --
419 -- Only set output warning arguments
420 -- (Any key or derived arguments must be set to null
421 -- when validation only mode is being used.)
422 --
423 p_elig_cvrd_dpnt_id := null;
424 p_effective_start_date := null;
425 p_effective_end_date := null;
426 p_object_version_number := null;
427 hr_utility.set_location(' Leaving:'||l_proc, 80);
428 --
429 when others then
430 --
431 -- A validation or unexpected error has occured
432 --
433 ROLLBACK TO create_ELIG_CVRD_DPNT;
434 raise;
435 --
436 end create_ELIG_CVRD_DPNT;
437 -- ----------------------------------------------------------------------------
438 -- |------------------------< update_ELIG_CVRD_DPNT >--- ------------------|
439 -- ----------------------------------------------------------------------------
440 --
441 procedure update_ELIG_CVRD_DPNT
442 (p_validate in boolean default false
443 ,p_elig_cvrd_dpnt_id in number
444 ,p_effective_start_date out nocopy date
445 ,p_effective_end_date out nocopy date
446 ,p_business_group_id in number default hr_api.g_number
447 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
448 ,p_dpnt_person_id in number default hr_api.g_number
449 ,p_cvg_strt_dt in date default hr_api.g_date
450 ,p_cvg_thru_dt in date default hr_api.g_eot
451 ,p_cvg_pndg_flag in varchar2 default hr_api.g_varchar2
452 ,p_pdp_attribute_category in varchar2 default hr_api.g_varchar2
453 ,p_pdp_attribute1 in varchar2 default hr_api.g_varchar2
454 ,p_pdp_attribute2 in varchar2 default hr_api.g_varchar2
455 ,p_pdp_attribute3 in varchar2 default hr_api.g_varchar2
456 ,p_pdp_attribute4 in varchar2 default hr_api.g_varchar2
457 ,p_pdp_attribute5 in varchar2 default hr_api.g_varchar2
458 ,p_pdp_attribute6 in varchar2 default hr_api.g_varchar2
459 ,p_pdp_attribute7 in varchar2 default hr_api.g_varchar2
460 ,p_pdp_attribute8 in varchar2 default hr_api.g_varchar2
461 ,p_pdp_attribute9 in varchar2 default hr_api.g_varchar2
462 ,p_pdp_attribute10 in varchar2 default hr_api.g_varchar2
463 ,p_pdp_attribute11 in varchar2 default hr_api.g_varchar2
464 ,p_pdp_attribute12 in varchar2 default hr_api.g_varchar2
465 ,p_pdp_attribute13 in varchar2 default hr_api.g_varchar2
466 ,p_pdp_attribute14 in varchar2 default hr_api.g_varchar2
467 ,p_pdp_attribute15 in varchar2 default hr_api.g_varchar2
468 ,p_pdp_attribute16 in varchar2 default hr_api.g_varchar2
469 ,p_pdp_attribute17 in varchar2 default hr_api.g_varchar2
470 ,p_pdp_attribute18 in varchar2 default hr_api.g_varchar2
471 ,p_pdp_attribute19 in varchar2 default hr_api.g_varchar2
472 ,p_pdp_attribute20 in varchar2 default hr_api.g_varchar2
473 ,p_pdp_attribute21 in varchar2 default hr_api.g_varchar2
474 ,p_pdp_attribute22 in varchar2 default hr_api.g_varchar2
475 ,p_pdp_attribute23 in varchar2 default hr_api.g_varchar2
476 ,p_pdp_attribute24 in varchar2 default hr_api.g_varchar2
477 ,p_pdp_attribute25 in varchar2 default hr_api.g_varchar2
478 ,p_pdp_attribute26 in varchar2 default hr_api.g_varchar2
479 ,p_pdp_attribute27 in varchar2 default hr_api.g_varchar2
480 ,p_pdp_attribute28 in varchar2 default hr_api.g_varchar2
481 ,p_pdp_attribute29 in varchar2 default hr_api.g_varchar2
482 ,p_pdp_attribute30 in varchar2 default hr_api.g_varchar2
483 ,p_request_id in number default hr_api.g_number
484 ,p_program_application_id in number default hr_api.g_number
485 ,p_program_id in number default hr_api.g_number
486 ,p_program_update_date in date default hr_api.g_date
487 ,p_object_version_number in out nocopy number
488 ,p_ovrdn_flag in varchar2 default hr_api.g_varchar2
489 ,p_per_in_ler_id in number default hr_api.g_number
490 ,p_ovrdn_thru_dt in date default hr_api.g_date
491 ,p_effective_date in date
492 ,p_datetrack_mode in varchar2
493 ,p_multi_row_actn in boolean default TRUE
494 ) is
495 --
496 -- Declare cursors and local variables
497 --
498 l_proc varchar2(72) := g_package||'update_ELIG_CVRD_DPNT';
499 l_object_version_number ben_elig_cvrd_dpnt_f.object_version_number%TYPE;
500 l_effective_start_date ben_elig_cvrd_dpnt_f.effective_start_date%TYPE;
501 l_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
502 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
503 --
504 l2_object_version_number ben_cvrd_dpnt_ctfn_prvdd_f.object_version_number%TYPE;
505 l2_effective_start_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_start_date%TYPE;
506 l2_effective_end_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_end_date%TYPE;
507 l2_datetrack_mode varchar2(30);
508 --
509 cursor dpnt_ctfn_c(x_datetrack_mode varchar2) is
510 select bcc.cvrd_dpnt_ctfn_prvdd_id,
511 bcc.object_version_number
512 from ben_cvrd_dpnt_ctfn_prvdd_f bcc ,
513 ben_prtt_enrt_actn_f bpe
514 where bcc.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
515 and bcc.business_group_id = p_business_group_id
516 and bcc.prtt_enrt_actn_id = bpe.prtt_enrt_actn_id
517 and bpe.per_in_ler_id = p_per_in_ler_id
518 and bpe.business_group_id = p_business_group_id
519 and p_effective_date between bcc.effective_start_date
520 and bcc.effective_end_date
521 and p_effective_date between bpe.effective_start_date
522 and bpe.effective_end_date
523 and (x_datetrack_mode=hr_api.g_delete
524 and p_effective_date<>bcc.effective_end_date
525 or x_datetrack_mode=hr_api.g_zap)
526 ;
527 --
528 cursor c_get_previous_values is
529 SELECT pdp.cvg_strt_dt,
530 pdp.cvg_thru_dt,
531 pdp.prtt_enrt_rslt_id,
532 pdp.per_in_ler_id,
533 pdp.dpnt_person_id,
534 pen.pl_id,
535 pen.oipl_id,
536 pen.person_id
537 FROM ben_prtt_enrt_rslt_f pen,
538 ben_elig_cvrd_dpnt_f pdp
539 WHERE pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
540 AND pen.prtt_enrt_rslt_stat_cd is null
541 and p_effective_date between pdp.effective_start_date and pdp.effective_end_date
542 and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
543 AND p_effective_date between pen.effective_start_date and pen.effective_end_date;
544 --
545 -- 3574168
546 -- Fetch all PCP records on effective date
547 Cursor c_pcp (c_elig_cvrd_dpnt_id NUMBER, c_pcp_effective_date DATE)
548 is
549 select pcp.PRMRY_CARE_PRVDR_ID
550 ,pcp.EFFECTIVE_START_DATE
551 ,pcp.EFFECTIVE_END_DATE
552 ,pcp.PRTT_ENRT_RSLT_ID
553 ,pcp.BUSINESS_GROUP_ID
554 ,pcp.OBJECT_VERSION_NUMBER
555 from ben_prmry_care_prvdr_f pcp
556 where business_group_id = p_business_group_id
557 and elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
558 and c_pcp_effective_date between effective_start_date --3631067: Changed p_effective_date to c_pcp_effective_date
559 and effective_end_date
560 ;
561 --
562 -- Fetch all PCP records in future
563 Cursor c_pcp_future (c_elig_cvrd_dpnt_id NUMBER, c_pcp_effective_date DATE)
564 is
565 select pcp.PRMRY_CARE_PRVDR_ID
566 ,pcp.EFFECTIVE_START_DATE
567 ,pcp.EFFECTIVE_END_DATE
568 ,pcp.PRTT_ENRT_RSLT_ID
569 ,pcp.BUSINESS_GROUP_ID
570 ,pcp.OBJECT_VERSION_NUMBER
571 from ben_prmry_care_prvdr_f pcp
572 where pcp.business_group_id = p_business_group_id
573 and pcp.elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
574 and c_pcp_effective_date < pcp.effective_start_date ----3631067: Changed p_effective_date to c_pcp_effective_date
575 and NVL(pcp.effective_end_date, hr_api.g_eot) = hr_api.g_eot
576 ;
577 -- 3574168
578
579 --
580 l_previous_values c_get_previous_values%rowtype;
581 l_pcp_effective_date DATE;
582 l_pcp_effective_start_date DATE;
583 --
584 l_env_rec ben_env_object.g_global_env_rec_type;
585 --
586 --
587 begin
588 --
589 hr_utility.set_location('Entering:'|| l_proc, 10);
590 hr_utility.set_location('per in ler id '|| p_per_in_ler_id , 9745);
591 hr_utility.set_location('elig_dpnt ' || p_elig_cvrd_dpnt_id, 9745);
592 hr_utility.set_location(' dt' || p_effective_start_date, 9745);
593 --
594 if fnd_global.conc_request_id = -1 then
595 --
596 ben_env_object.get(p_rec => l_env_rec);
597 if l_env_rec.effective_date is null then
598 --
599 ben_env_object.init(p_business_group_id => p_business_group_id,
600 p_effective_date => p_effective_date,
601 p_thread_id => 1,
602 p_chunk_size => 1,
603 p_threads => 1,
604 p_max_errors => 1,
605 p_benefit_action_id => null);
606 --
607 end if;
608 --
609 end if;
610 --
611 -- Issue a savepoint if operating in validation only mode
612 --
613 savepoint update_ELIG_CVRD_DPNT;
614 --
615 hr_utility.set_location(l_proc, 20);
616 --
617 -- Process Logic
618 --
619 l_object_version_number := p_object_version_number;
620 --
621 -- Added by pxdas for logging change event needed for extract.
622 --
623 open c_get_previous_values;
624 fetch c_get_previous_values into l_previous_values;
625 close c_get_previous_values;
626 --
627 -- Ending Benefit Coverage for Dependent.
628 --
629 if l_previous_values.cvg_thru_dt = hr_api.g_eot
630 and p_cvg_thru_dt <> hr_api.g_date
631 and p_cvg_thru_dt <> hr_api.g_eot then
632 --
633 -- Call the extract change event logging process.
634 --
635 ben_ext_chlg.log_dependent_chg
636 (p_action => 'DELETE',
637 p_pl_id => l_previous_values.pl_id,
638 p_oipl_id => l_previous_values.oipl_id,
639 p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
640 p_cvg_end_dt => p_cvg_thru_dt,
641 p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
642 p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
643 p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
644 -- bug 1540458. line below was just prev per_in_ler_id
645 p_per_in_ler_id => nvl(p_per_in_ler_id,
646 l_previous_values.per_in_ler_id),
647 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
648 p_person_id => l_previous_values.person_id,
649 p_dpnt_person_id => l_previous_values.dpnt_person_id,
650 p_business_group_id => p_business_group_id,
651 p_effective_date => p_effective_date);
652 --
653 --
654 -- Benefit reinstatement for Dependent.
655 --
656 elsif l_previous_values.cvg_thru_dt <> hr_api.g_eot
657 and p_cvg_thru_dt = hr_api.g_eot then
658 --
659 --
660 ben_ext_chlg.log_dependent_chg
661 (p_action => 'REINSTATE',
662 p_pl_id => l_previous_values.pl_id,
663 p_oipl_id => l_previous_values.oipl_id,
664 p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
665 p_cvg_end_dt => p_cvg_thru_dt,
666 p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
667 p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
668 p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
669 -- bug 1540458. line below was just prev per_in_ler_id
670 p_per_in_ler_id => nvl(p_per_in_ler_id,
671 l_previous_values.per_in_ler_id),
672 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
673 p_person_id => l_previous_values.person_id,
674 p_dpnt_person_id => l_previous_values.dpnt_person_id,
675 p_business_group_id => p_business_group_id,
676 p_effective_date => p_effective_date);
677
678 end if;
679 --
680 -- End logging change event
681 --
682 begin
683 --
684 -- Start of API User Hook for the before hook of update_ELIG_CVRD_DPNT
685 --
686 ben_ELIG_CVRD_DPNT_bk2.update_ELIG_CVRD_DPNT_b
687 (
688 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
689 ,p_business_group_id => p_business_group_id
690 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
691 ,p_dpnt_person_id => p_dpnt_person_id
692 ,p_cvg_strt_dt => p_cvg_strt_dt
693 ,p_cvg_thru_dt => p_cvg_thru_dt
694 ,p_cvg_pndg_flag => p_cvg_pndg_flag
695 ,p_pdp_attribute_category => p_pdp_attribute_category
696 ,p_pdp_attribute1 => p_pdp_attribute1
697 ,p_pdp_attribute2 => p_pdp_attribute2
698 ,p_pdp_attribute3 => p_pdp_attribute3
699 ,p_pdp_attribute4 => p_pdp_attribute4
700 ,p_pdp_attribute5 => p_pdp_attribute5
701 ,p_pdp_attribute6 => p_pdp_attribute6
702 ,p_pdp_attribute7 => p_pdp_attribute7
703 ,p_pdp_attribute8 => p_pdp_attribute8
704 ,p_pdp_attribute9 => p_pdp_attribute9
705 ,p_pdp_attribute10 => p_pdp_attribute10
706 ,p_pdp_attribute11 => p_pdp_attribute11
707 ,p_pdp_attribute12 => p_pdp_attribute12
708 ,p_pdp_attribute13 => p_pdp_attribute13
709 ,p_pdp_attribute14 => p_pdp_attribute14
710 ,p_pdp_attribute15 => p_pdp_attribute15
711 ,p_pdp_attribute16 => p_pdp_attribute16
712 ,p_pdp_attribute17 => p_pdp_attribute17
713 ,p_pdp_attribute18 => p_pdp_attribute18
714 ,p_pdp_attribute19 => p_pdp_attribute19
715 ,p_pdp_attribute20 => p_pdp_attribute20
716 ,p_pdp_attribute21 => p_pdp_attribute21
717 ,p_pdp_attribute22 => p_pdp_attribute22
718 ,p_pdp_attribute23 => p_pdp_attribute23
719 ,p_pdp_attribute24 => p_pdp_attribute24
720 ,p_pdp_attribute25 => p_pdp_attribute25
721 ,p_pdp_attribute26 => p_pdp_attribute26
722 ,p_pdp_attribute27 => p_pdp_attribute27
723 ,p_pdp_attribute28 => p_pdp_attribute28
724 ,p_pdp_attribute29 => p_pdp_attribute29
725 ,p_pdp_attribute30 => p_pdp_attribute30
726 ,p_request_id => p_request_id
727 ,p_program_application_id => p_program_application_id
728 ,p_program_id => p_program_id
729 ,p_program_update_date => p_program_update_date
730 ,p_object_version_number => p_object_version_number
731 ,p_ovrdn_flag => p_ovrdn_flag
732 ,p_per_in_ler_id => p_per_in_ler_id
733 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
734 ,p_effective_date => trunc(p_effective_date)
735 ,p_datetrack_mode => p_datetrack_mode
736 );
737 exception
738 when hr_api.cannot_find_prog_unit then
739 hr_api.cannot_find_prog_unit_error
740 (p_module_name => 'UPDATE_ELIG_CVRD_DPNT'
741 ,p_hook_type => 'BP'
742 );
743 --
744 -- End of API User Hook for the before hook of update_ELIG_CVRD_DPNT
745 --
746 end;
747 --
748 -- If not covered, delete certifications provided.
749 --
750 if not(p_cvg_strt_dt is not null and p_cvg_thru_dt = hr_api.g_eot) then
751 --
752 --
753 -- delete certifications provided
754 if p_datetrack_mode = 'UPDATE' then
755 l2_datetrack_mode := 'DELETE';
756 else
757 l2_datetrack_mode := 'ZAP';
758 --
759 end if;
760 --
761 remove_usage( p_validate => p_validate
762 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
763 ,p_cvg_thru_dt => p_cvg_thru_dt
764 ,p_effective_date => p_effective_date
765 ,p_datetrack_mode => l2_datetrack_mode
766 );
767 --
768 for ctfn_rec in dpnt_ctfn_c(l2_datetrack_mode) loop
769 --
770 l2_object_version_number := ctfn_rec.object_version_number;
771 --
772 ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
773 (p_validate => FALSE
774 ,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
775 ,p_effective_start_date => l2_effective_start_date
776 ,p_effective_end_date => l2_effective_end_date
777 ,p_object_version_number => l2_object_version_number
778 ,p_business_group_id => p_business_group_id
779 ,p_effective_date => p_effective_date
780 ,p_datetrack_mode => l2_datetrack_mode
781 );
782 --
783 end loop;
784 --
785 --
786 -- 3574168: Remove PCP records
787 -- Set End-date to coverage-end-date.
788 --
789 l_pcp_effective_date := NVL(p_cvg_thru_dt+1,p_effective_date);
790 --
791 for l_pcp in c_pcp(p_elig_cvrd_dpnt_id, l_pcp_effective_date) loop
792 --
793 hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
794 hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
795 hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
796 hr_utility.set_location('DATETRACK_MODE '|| l2_datetrack_mode, 15);
797 --
798 -- Since, deletion automatically sets end-date to 1 day less than effective-date,
799 -- call the delete-api with effective_date = cvg_thru_date+1.
800 --
801 ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
802 (P_VALIDATE => FALSE
803 ,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
804 ,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
805 ,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
806 ,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
807 ,P_EFFECTIVE_DATE => l_pcp_effective_date --3631067: Changed p_effective_date to l_pcp_effective_date
808 ,P_DATETRACK_MODE => l2_datetrack_mode
809 ,p_called_from => 'delete_enrollment'
810 );
811 --
812 End loop;
813 --
814 -- Get future PCP records if any and zap - delete all of them.
815 --
816 for l_pcp_future in c_pcp_future(p_elig_cvrd_dpnt_id, l_pcp_effective_date) loop
817 --
818 hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
819 hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
820 hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
821 --
822 l_pcp_effective_start_date := l_pcp_future.effective_start_date;
823 --
824 ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
825 (P_VALIDATE => FALSE
826 ,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
827 ,P_EFFECTIVE_START_DATE => l_pcp_future.effective_start_date
828 ,P_EFFECTIVE_END_DATE => l_pcp_future.effective_end_date
829 ,P_OBJECT_VERSION_NUMBER => l_pcp_future.object_version_number
830 ,P_EFFECTIVE_DATE => l_pcp_effective_start_date
831 ,P_DATETRACK_MODE => hr_api.g_zap
832 ,p_called_from => 'delete_enrollment'
833 );
834 End loop;
835 -- 3574168
836 end if;
837 --
838 --
839 ben_pdp_upd.upd
840 (
841 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
842 ,p_effective_start_date => l_effective_start_date
843 ,p_effective_end_date => l_effective_end_date
844 ,p_business_group_id => p_business_group_id
845 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
846 ,p_dpnt_person_id => p_dpnt_person_id
847 ,p_cvg_strt_dt => p_cvg_strt_dt
848 ,p_cvg_thru_dt => p_cvg_thru_dt
849 ,p_cvg_pndg_flag => p_cvg_pndg_flag
850 ,p_pdp_attribute_category => p_pdp_attribute_category
851 ,p_pdp_attribute1 => p_pdp_attribute1
852 ,p_pdp_attribute2 => p_pdp_attribute2
853 ,p_pdp_attribute3 => p_pdp_attribute3
854 ,p_pdp_attribute4 => p_pdp_attribute4
855 ,p_pdp_attribute5 => p_pdp_attribute5
856 ,p_pdp_attribute6 => p_pdp_attribute6
857 ,p_pdp_attribute7 => p_pdp_attribute7
858 ,p_pdp_attribute8 => p_pdp_attribute8
859 ,p_pdp_attribute9 => p_pdp_attribute9
860 ,p_pdp_attribute10 => p_pdp_attribute10
861 ,p_pdp_attribute11 => p_pdp_attribute11
862 ,p_pdp_attribute12 => p_pdp_attribute12
863 ,p_pdp_attribute13 => p_pdp_attribute13
864 ,p_pdp_attribute14 => p_pdp_attribute14
865 ,p_pdp_attribute15 => p_pdp_attribute15
866 ,p_pdp_attribute16 => p_pdp_attribute16
867 ,p_pdp_attribute17 => p_pdp_attribute17
868 ,p_pdp_attribute18 => p_pdp_attribute18
869 ,p_pdp_attribute19 => p_pdp_attribute19
870 ,p_pdp_attribute20 => p_pdp_attribute20
871 ,p_pdp_attribute21 => p_pdp_attribute21
872 ,p_pdp_attribute22 => p_pdp_attribute22
873 ,p_pdp_attribute23 => p_pdp_attribute23
874 ,p_pdp_attribute24 => p_pdp_attribute24
875 ,p_pdp_attribute25 => p_pdp_attribute25
876 ,p_pdp_attribute26 => p_pdp_attribute26
877 ,p_pdp_attribute27 => p_pdp_attribute27
878 ,p_pdp_attribute28 => p_pdp_attribute28
879 ,p_pdp_attribute29 => p_pdp_attribute29
880 ,p_pdp_attribute30 => p_pdp_attribute30
881 ,p_request_id => p_request_id
882 ,p_program_application_id => p_program_application_id
883 ,p_program_id => p_program_id
884 ,p_program_update_date => p_program_update_date
885 ,p_object_version_number => l_object_version_number
886 ,p_ovrdn_flag => p_ovrdn_flag
887 ,p_per_in_ler_id => p_per_in_ler_id
888 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
889 ,p_effective_date => trunc(p_effective_date)
890 ,p_datetrack_mode => p_datetrack_mode
891 );
892 --
893 if p_prtt_enrt_rslt_id = hr_api.g_number then
894 l_prtt_enrt_rslt_id := l_previous_values.prtt_enrt_rslt_id;
895 else
896 l_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
897 end if;
898 --
899 if p_cvg_strt_dt is not null and p_cvg_thru_dt = hr_api.g_eot then
900 --
901 add_usage( p_validate => p_validate
902 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
903 ,p_effective_date => p_effective_date
904 ,p_datetrack_mode => p_datetrack_mode
905 );
906 --
907 ben_cobra_requirements.update_dpnt_cobra_info
908 (p_per_in_ler_id => p_per_in_ler_id
909 ,p_person_id => l_previous_values.dpnt_person_id
910 ,p_business_group_id => p_business_group_id
911 ,p_effective_date => p_effective_date
912 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
913 ,p_validate => p_validate
914 );
915
916 end if;
917 --
918 -- Call Action item RCO if p_multi_row_actn = TRUE
919 --
920 if p_multi_row_actn then
921 --
922 dpnt_actn_items(
923 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
924 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
925 ,p_effective_date => p_effective_date
926 ,p_business_group_id => p_business_group_id
927 ,p_validate => p_validate
928 ,p_datetrack_mode => p_datetrack_mode
929 );
930 --
931 end if;
932 --
933 --
934 begin
935 --
936 -- Start of API User Hook for the after hook of update_ELIG_CVRD_DPNT
937 --
938 ben_ELIG_CVRD_DPNT_bk2.update_ELIG_CVRD_DPNT_a
939 (
940 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
941 ,p_effective_start_date => l_effective_start_date
942 ,p_effective_end_date => l_effective_end_date
943 ,p_business_group_id => p_business_group_id
944 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
945 ,p_dpnt_person_id => p_dpnt_person_id
946 ,p_cvg_strt_dt => p_cvg_strt_dt
947 ,p_cvg_thru_dt => p_cvg_thru_dt
948 ,p_cvg_pndg_flag => p_cvg_pndg_flag
949 ,p_pdp_attribute_category => p_pdp_attribute_category
950 ,p_pdp_attribute1 => p_pdp_attribute1
951 ,p_pdp_attribute2 => p_pdp_attribute2
952 ,p_pdp_attribute3 => p_pdp_attribute3
953 ,p_pdp_attribute4 => p_pdp_attribute4
954 ,p_pdp_attribute5 => p_pdp_attribute5
955 ,p_pdp_attribute6 => p_pdp_attribute6
956 ,p_pdp_attribute7 => p_pdp_attribute7
957 ,p_pdp_attribute8 => p_pdp_attribute8
958 ,p_pdp_attribute9 => p_pdp_attribute9
959 ,p_pdp_attribute10 => p_pdp_attribute10
960 ,p_pdp_attribute11 => p_pdp_attribute11
961 ,p_pdp_attribute12 => p_pdp_attribute12
962 ,p_pdp_attribute13 => p_pdp_attribute13
963 ,p_pdp_attribute14 => p_pdp_attribute14
964 ,p_pdp_attribute15 => p_pdp_attribute15
965 ,p_pdp_attribute16 => p_pdp_attribute16
966 ,p_pdp_attribute17 => p_pdp_attribute17
967 ,p_pdp_attribute18 => p_pdp_attribute18
968 ,p_pdp_attribute19 => p_pdp_attribute19
969 ,p_pdp_attribute20 => p_pdp_attribute20
970 ,p_pdp_attribute21 => p_pdp_attribute21
971 ,p_pdp_attribute22 => p_pdp_attribute22
972 ,p_pdp_attribute23 => p_pdp_attribute23
973 ,p_pdp_attribute24 => p_pdp_attribute24
974 ,p_pdp_attribute25 => p_pdp_attribute25
975 ,p_pdp_attribute26 => p_pdp_attribute26
976 ,p_pdp_attribute27 => p_pdp_attribute27
977 ,p_pdp_attribute28 => p_pdp_attribute28
978 ,p_pdp_attribute29 => p_pdp_attribute29
979 ,p_pdp_attribute30 => p_pdp_attribute30
980 ,p_request_id => p_request_id
981 ,p_program_application_id => p_program_application_id
982 ,p_program_id => p_program_id
983 ,p_program_update_date => p_program_update_date
984 ,p_object_version_number => l_object_version_number
985 ,p_ovrdn_flag => p_ovrdn_flag
986 ,p_per_in_ler_id => p_per_in_ler_id
987 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
988 ,p_effective_date => trunc(p_effective_date)
989 ,p_datetrack_mode => p_datetrack_mode
990 );
991 exception
992 when hr_api.cannot_find_prog_unit then
993 hr_api.cannot_find_prog_unit_error
994 (p_module_name => 'UPDATE_ELIG_CVRD_DPNT'
995 ,p_hook_type => 'AP'
996 );
997 --
998 -- End of API User Hook for the after hook of update_ELIG_CVRD_DPNT
999 --
1000 end;
1001 --
1002 hr_utility.set_location(l_proc, 60);
1003 --
1004 -- When in validation only mode raise the Validate_Enabled exception
1005 --
1006 if p_validate then
1007 raise hr_api.validate_enabled;
1008 end if;
1009 --
1010 -- Set all output arguments
1011 --
1012 p_object_version_number := l_object_version_number;
1013 p_effective_start_date := l_effective_start_date;
1014 p_effective_end_date := l_effective_end_date;
1015 --
1016 hr_utility.set_location(' Leaving:'||l_proc, 70);
1017 --
1018 exception
1019 --
1020 when hr_api.validate_enabled then
1021 --
1022 -- As the Validate_Enabled exception has been raised
1023 -- we must rollback to the savepoint
1024 --
1025 ROLLBACK TO update_ELIG_CVRD_DPNT;
1026 --
1027 -- Only set output warning arguments
1028 -- (Any key or derived arguments must be set to null
1029 -- when validation only mode is being used.)
1030 --
1031 p_effective_start_date := null;
1032 p_effective_end_date := null;
1033 hr_utility.set_location(' Leaving:'||l_proc, 80);
1034 --
1035 when others then
1036 --
1037 -- A validation or unexpected error has occured
1038 --
1039 ROLLBACK TO update_ELIG_CVRD_DPNT;
1040 p_object_version_number := l_object_version_number;
1041 p_effective_start_date := null;
1042 p_effective_end_date := null;
1043 raise;
1044 --
1045 end update_ELIG_CVRD_DPNT;
1046
1047 -- ----------------------------------------------------------------------------
1048 -- |------------------------< un_end_date_dpnt_ptu >----------------------|
1049 -- ----------------------------------------------------------------------------
1050 --
1051 -- {Start Of Comments}
1052 --
1053 -- Description:
1054 -- Bug: 1485862. Added new method un_end_date_dpnt_ptu to un_end_date 'DPNT' person_type_usage for
1055 -- the dependents if the employee's termination event is backed out.
1056 -- Prerequisites:
1057 --
1058 --
1059 -- In Parameters:
1060 -- Name Reqd Type
1061 -- p_validate No boolean
1062 -- p_elig_cvrd_dpnt_id Yes number
1063 -- p_cvg_thru_dt Yes date
1064 -- p_effective_date Yes date
1065 -- p_datetrack_mode Yes varchar2
1066 --
1067 -- Post Success:
1068 -- The Dependent's PTU is un-end-dated.
1069 --
1070 -- Post Failure:
1071 -- The procedure passes the failure to the calling procedure. The calling procedure
1072 -- should handle the failure
1073 --
1074 -- Access Status:
1075 -- Internal.
1076 --
1077 -- {End Of Comments}
1078 --
1079 procedure un_end_date_dpnt_ptu
1080 (p_validate in boolean default false
1081 ,p_elig_cvrd_dpnt_id in number
1082 ,p_cvg_thru_dt in date
1083 ,p_effective_date in date
1084 ,p_datetrack_mode in varchar2
1085 ) is
1086 --
1087 --
1088 -- Declare cursors and local variables
1089 --
1090 l_proc varchar2(72) := g_package||'un_end_date_dpnt_ptu';
1091 l_exist varchar2(1);
1092 l_dpnt_person_id number(15);
1093 l_cvg_strt_dt date;
1094 l_end_dt date;
1095 l_object_version_number number(9);
1096 l_business_group_id number(15);
1097 l_datetrack_mode varchar2(30);
1098 l_effective_end_date date;
1099 l_effective_date date;
1100 --
1101 --
1102 cursor get_dpnt_info_c is
1103 select dpnt_person_id,
1104 cvg_strt_dt,
1105 business_group_id
1106 from ben_elig_cvrd_dpnt_f
1107 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1108 and p_effective_date between effective_start_date
1109 and effective_end_date;
1110 --
1111 cursor other_dpnt_c is
1112 select null
1113 from ben_elig_cvrd_dpnt_f a,
1114 ben_per_in_ler pil
1115 where a.dpnt_person_id = l_dpnt_person_id
1116 and a.elig_cvrd_dpnt_id <> p_elig_cvrd_dpnt_id
1117 and a.cvg_strt_dt is not null
1118 and a.cvg_thru_dt = hr_api.g_eot
1119 and l_end_dt between a.cvg_strt_dt
1120 and nvl(a.cvg_thru_dt, hr_api.g_date)
1121 and p_effective_date between a.effective_start_date
1122 and a.effective_end_date
1123 and a.per_in_ler_id = pil.per_in_ler_id
1124 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1125 --
1126 cursor usage_c is
1127 select a.person_id
1128 ,a.person_type_usage_id
1129 ,a.object_version_number
1130 ,a.person_type_id
1131 ,a.effective_start_date
1132 ,a.effective_end_date
1133 from per_person_type_usages_f a,
1134 per_person_types b
1135 where a.person_id = l_dpnt_person_id
1136 and a.person_type_id = b.person_type_id
1137 and b.system_person_type = 'DPNT'
1138 and b.business_group_id = l_business_group_id
1139 and l_end_dt between a.effective_start_date
1140 and a.effective_end_date;
1141 --
1142 cursor delete_c is
1143 select a.person_id
1144 ,a.person_type_usage_id
1145 ,a.object_version_number
1146 ,a.person_type_id
1147 ,a.effective_start_date
1148 ,a.effective_end_date
1149 from per_person_type_usages_f a,
1150 per_person_types b
1151 where a.person_id = l_dpnt_person_id
1152 and a.person_type_id = b.person_type_id
1153 and b.system_person_type = 'DPNT'
1154 and b.business_group_id = l_business_group_id
1155 and a.effective_start_date > l_end_dt;
1156
1157 begin
1158 --
1159
1160 hr_utility.set_location(' Entering:'||l_proc, 10);
1161
1162 open get_dpnt_info_c;
1163 fetch get_dpnt_info_c into l_dpnt_person_id,
1164 l_cvg_strt_dt,
1165 l_business_group_id;
1166 --
1167 if get_dpnt_info_c%NOTFOUND then
1168 -- error
1169 null;
1170 --
1171 end if;
1172 --
1173 close get_dpnt_info_c;
1174
1175 hr_utility.set_location('Dependent Person Id : '||l_dpnt_person_id , 20);
1176
1177 --
1178 if p_cvg_thru_dt is not null then
1179 l_end_dt := p_cvg_thru_dt;
1180 else
1181 l_end_dt := l_cvg_strt_dt;
1182 end if;
1183 --
1184 --
1185 open other_dpnt_c;
1186 fetch other_dpnt_c into l_exist;
1187 if other_dpnt_c%NOTFOUND then
1188 --
1189
1190 -- delete all future ptu's of type 'DPNT', as this one extends to end-of-time
1191
1192 l_datetrack_mode := hr_api.g_zap ;
1193
1194 for del_rec in delete_c loop
1195 --- due to nocopy the p_effective date is nulifile because
1196 --- the same variable sent to p_effective_start_date
1197 --- this is fixed by sending different variabale as p_effective_date
1198
1199 l_effective_date := del_rec.effective_start_date ;
1200 --
1201 hr_per_type_usage_internal.delete_person_type_usage
1202 (p_validate => FALSE
1203 ,p_person_type_usage_id => del_rec.person_type_usage_id
1204 ,p_effective_date => l_effective_date -- p_effective_date # 2744060
1205 ,p_datetrack_mode => l_datetrack_mode
1206 ,p_object_version_number => del_rec.object_version_number
1207 ,p_effective_start_date => del_rec.effective_start_date
1208 ,p_effective_end_date => del_rec.effective_end_date
1209 );
1210 hr_utility.set_location('Delete Person Type Usage Id : '||del_rec.person_type_usage_id , 30);
1211 end loop;
1212
1213
1214 -- set the effective_end_date to end-of-time
1215 -- update table directly as this is not supported by row handler.
1216 for cur_rec in usage_c loop
1217 l_datetrack_mode := hr_api.g_correction;
1218 l_effective_end_date := hr_api.g_eot;
1219
1220 update per_person_type_usages_f ptu
1221 set effective_end_date = l_effective_end_date
1222 where ptu.person_type_usage_id = cur_rec.person_type_usage_id
1223 and ptu.effective_start_date = cur_rec.effective_start_date;
1224
1225 hr_utility.set_location('Un-end-date Person Type Usage Id : '||cur_rec.person_type_usage_id , 30);
1226 end loop;
1227 --
1228 end if;
1229 --
1230 close other_dpnt_c;
1231 --
1232 hr_utility.set_location(' Leaving:'||l_proc, 40);
1233 --
1234 end un_end_date_dpnt_ptu;
1235
1236 ----------------------------------------------------------------------------
1237 -- |------------------------< un_end_date_dpnt_pea >----------------------|
1238 -- ----------------------------------------------------------------------------
1239 --
1240 -- {Start Of Comments}
1241 --
1242 -- Description:
1243 -- Bug: 5572910. Added new method un_end_date_dpnt_pea to un_end_date PEA records which in
1244 -- turn will un end CCP records if the life event is backed out.
1245 -- Prerequisites:
1246 --
1247 --
1248 -- In Parameters:
1249 -- Name Reqd Type
1250 -- p_validate No boolean
1251 -- p_elig_cvrd_dpnt_id Yes number
1252 -- p_effective_date Yes date
1253 --
1254 -- Post Success:
1255 -- THE PEA and Dependent's CERT is un-end-dated.
1256 --
1257 -- Post Failure:
1258 -- The procedure passes the failure to the calling procedure. The calling procedure
1259 -- should handle the failure
1260 --
1261 -- Access Status:
1262 -- Internal.
1263 --
1264 -- {End Of Comments}
1265 --
1266 procedure un_end_date_dpnt_pea
1267 (p_validate in boolean default false
1268 ,p_elig_cvrd_dpnt_id in number
1269 ,p_effective_date in date
1270 ) is
1271 --
1272 --
1273 -- Declare cursors and local variables
1274 --
1275 l_proc varchar2(72) := g_package||'un_end_date_dpnt_pea';
1276 l_cvg_strt_dt date;
1277 --
1278 --
1279 cursor c_get_dpnt_info is
1280 select dpnt_person_id,
1281 cvg_strt_dt,
1282 business_group_id,
1283 prtt_enrt_rslt_id
1284 from ben_elig_cvrd_dpnt_f
1285 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1286 and p_effective_date between effective_start_date
1287 and effective_end_date;
1288 --
1289 cursor c_pea(p_prtt_enrt_rslt_id number) is
1290 --
1291 select pea.prtt_enrt_actn_id,
1292 pea.effective_start_date,
1293 pea.effective_end_date,
1294 pea.object_version_number,
1295 pen.object_version_number rslt_object_version_number
1296 from ben_prtt_enrt_actn_f pea,
1297 ben_prtt_enrt_rslt_f pen
1298 where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1299 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1300 and pen.prtt_enrt_rslt_stat_cd is null
1301 and p_effective_date between pea.effective_start_date
1302 and pea.effective_end_date
1303 and p_effective_date between pen.effective_start_date
1304 and pen.effective_end_date;
1305 --
1306 l_dpnt_person_id number(15);
1307 l_business_group_id number(15);
1308 l_prtt_enrt_rslt_id number;
1309 l_effective_date date;
1310 l_effective_start_date date;
1311 l_effective_end_date date;
1312 l_rslt_object_version_number number;
1313 l_datetrack_mode varchar2(30);
1314 --
1315 begin
1316 --
1317 hr_utility.set_location(' Entering:'||l_proc, 10);
1318 --
1319 open c_get_dpnt_info;
1320 fetch c_get_dpnt_info into l_dpnt_person_id,
1321 l_cvg_strt_dt,
1322 l_business_group_id,
1323 l_prtt_enrt_rslt_id;
1324 --
1325 if c_get_dpnt_info%NOTFOUND then
1326 null;
1327 --
1328 end if;
1329 --
1330 close c_get_dpnt_info;
1331
1332 hr_utility.set_location('Dependent Person Id : '||l_dpnt_person_id , 20);
1333 --
1334 for l_usage_pea in c_pea(l_prtt_enrt_rslt_id)
1335 loop
1336 --
1337 l_effective_date := l_usage_pea.effective_start_date ;
1338 if l_usage_pea.effective_end_date <> hr_api.g_eot then
1339 --
1340 ben_PRTT_ENRT_ACTN_api.delete_PRTT_ENRT_ACTN
1341 (p_validate => FALSE,
1342 p_effective_date => l_effective_date,
1343 p_business_group_id => l_business_group_id,
1344 p_datetrack_mode => hr_api.g_future_change,
1345 p_object_version_number => l_usage_pea.object_version_number,
1346 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
1347 p_rslt_object_version_number => l_usage_pea.rslt_object_version_number,
1348 p_effective_start_date => l_effective_start_date,
1349 p_effective_end_date => l_effective_end_date,
1350 p_prtt_enrt_actn_id => l_usage_pea.prtt_enrt_actn_id );
1351
1352 end if;
1353 --
1354 end loop;
1355 --
1356 hr_utility.set_location(' Leaving:'||l_proc, 40);
1357 --
1358 end un_end_date_dpnt_pea;
1359
1360 -- ----------------------------------------------------------------------------
1361 -- |------------------------< delete_ELIG_CVRD_DPNT >----------------------|
1362 -- ----------------------------------------------------------------------------
1363 -- !!! THIS IS OVERLOADED. CHANGE THE OTHE PROCEDURE ALSO !!!
1364 procedure delete_ELIG_CVRD_DPNT
1365 (p_validate in boolean default false
1366 ,p_elig_cvrd_dpnt_id in number
1367 ,p_effective_start_date out nocopy date
1368 ,p_effective_end_date out nocopy date
1369 ,p_object_version_number in out nocopy number
1370 ,p_business_group_id in number
1371 ,p_effective_date in date
1372 ,p_datetrack_mode in varchar2
1373 ,p_multi_row_actn in boolean default TRUE
1374 ) is
1375 --
1376 -- Declare cursors and local variables
1377 --
1378 l_proc varchar2(72) := g_package||'delete_ELIG_CVRD_DPNT';
1379 l_object_version_number ben_elig_cvrd_dpnt_f.object_version_number%TYPE;
1380 l_effective_start_date ben_elig_cvrd_dpnt_f.effective_start_date%TYPE;
1381 l_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
1382 l_parent_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
1383 l2_object_version_number ben_cvrd_dpnt_ctfn_prvdd_f.object_version_number%TYPE;
1384 l2_effective_start_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_start_date%TYPE;
1385 l2_effective_end_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_end_date%TYPE;
1386 l3_object_version_number ben_prmry_care_prvdr_f.object_version_number%TYPE;
1387 l3_effective_start_date ben_prmry_care_prvdr_f.effective_start_date%TYPE;
1388 l3_effective_end_date ben_prmry_care_prvdr_f.effective_end_date%TYPE;
1389 l2_datetrack_mode varchar2(30);
1390 l_child_effective_date date;
1391 --
1392 cursor dpnt_info_c is
1393 select cvg_strt_dt,
1394 cvg_thru_dt,
1395 dpnt_person_id,
1396 prtt_enrt_rslt_id,
1397 per_in_ler_id
1398 from ben_elig_cvrd_dpnt_f
1399 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1400 and business_group_id = p_business_group_id
1401 and p_effective_date between effective_start_date
1402 and effective_end_date;
1403 --
1404 l_previous_values dpnt_info_c%rowtype;
1405 --
1406 cursor parent_c(cp_prtt_enrt_actn_id number,cp_effective_date date) is
1407 select effective_end_date
1408 from ben_prtt_enrt_actn_f
1409 where prtt_enrt_actn_id = cp_prtt_enrt_actn_id
1410 and business_group_id + 0 = p_business_group_id
1411 and cp_effective_date between effective_start_date and effective_end_date
1412
1413 ;
1414
1415 cursor dpnt_ctfn_c is
1416 select cvrd_dpnt_ctfn_prvdd_id,
1417 prtt_enrt_actn_id,
1418 object_version_number,
1419 effective_start_date,
1420 effective_end_date
1421 from ben_cvrd_dpnt_ctfn_prvdd_f
1422 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1423 and business_group_id = p_business_group_id
1424 and l_child_effective_date between effective_start_date
1425 and effective_end_date
1426 order by cvrd_dpnt_ctfn_prvdd_id asc;
1427 --
1428 cursor dpnt_pcp_c is
1429 select prmry_care_prvdr_id,
1430 object_version_number
1431 from ben_prmry_care_prvdr_f
1432 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1433 and business_group_id = p_business_group_id
1434 and p_effective_date between effective_start_date
1435 and effective_end_date;
1436 --
1437 cursor c_chg_info (p_prtt_enrt_rslt_id number) is
1438 SELECT pen.pl_id,
1439 pen.oipl_id,
1440 pen.person_id
1441 FROM ben_prtt_enrt_rslt_f pen
1442 WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1443 and pen.prtt_enrt_rslt_stat_cd is null
1444 and p_effective_date between pen.effective_start_date and pen.effective_end_date;
1445 --
1446 -- Bug 4879122
1447 --
1448 l_dpnt_pcp_actn_typ_id NUMBER (30);
1449 --
1450 CURSOR c_dpnt_pcp_actn_item
1451 IS
1452 SELECT pea.object_version_number, pea.prtt_enrt_actn_id,
1453 pea.prtt_enrt_rslt_id, pea.effective_end_date -- 5096675
1454 FROM ben_prtt_enrt_actn_f pea
1455 WHERE pea.business_group_id = p_business_group_id
1456 AND p_effective_date BETWEEN pea.effective_start_date
1457 AND pea.effective_end_date
1458 AND pea.actn_typ_id = l_dpnt_pcp_actn_typ_id
1459 AND pea.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;
1460 --
1461 l_chg_info c_chg_info%rowtype;
1462 l_env_rec ben_env_object.g_global_env_rec_type;
1463 --
1464 begin
1465 --
1466 hr_utility.set_location('Entering:'|| l_proc, 10);
1467 if ben_back_out_life_event.g_bolfe_effective_date is not null then
1468 l_child_effective_date:=ben_back_out_life_event.g_bolfe_effective_date;
1469 else
1470 l_child_effective_date:=p_effective_date;
1471 end if;
1472 --
1473 if fnd_global.conc_request_id = -1 then
1474 --
1475 ben_env_object.get(p_rec => l_env_rec);
1476 if l_env_rec.effective_date is null then
1477 --
1478 ben_env_object.init(p_business_group_id => p_business_group_id,
1479 p_effective_date => p_effective_date,
1480 p_thread_id => 1,
1481 p_chunk_size => 1,
1482 p_threads => 1,
1483 p_max_errors => 1,
1484 p_benefit_action_id => null);
1485 --
1486 end if;
1487 --
1488 end if;
1489 --
1490 -- Issue a savepoint if operating in validation only mode
1491 --
1492 savepoint delete_ELIG_CVRD_DPNT;
1493 --
1494 hr_utility.set_location(l_proc, 20);
1495 --
1496 -- Process Logic
1497 --
1498 l_object_version_number := p_object_version_number;
1499 --
1500 --
1501 -- Delete certifications
1502 --
1503 for ctfn_rec in dpnt_ctfn_c loop
1504 --
1505 l2_object_version_number := ctfn_rec.object_version_number;
1506
1507 -- open parent_c(ctfn_rec.prtt_enrt_actn_id,l_child_effective_date);
1508 -- fetch parent_c into l_parent_effective_end_date;
1509 -- close parent_c;
1510
1511 if p_datetrack_mode <> hr_api.g_future_change then
1512 --and
1513 -- (ctfn_rec.effective_end_date = hr_api.g_eot or
1514 -- l_parent_effective_end_date = hr_api.g_eot)) then
1515 ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
1516 (p_validate => FALSE
1517 ,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
1518 ,p_effective_start_date => l2_effective_start_date
1519 ,p_effective_end_date => l2_effective_end_date
1520 ,p_object_version_number => l2_object_version_number
1521 ,p_business_group_id => p_business_group_id
1522 ,p_effective_date => l_child_effective_date
1523 ,p_datetrack_mode => p_datetrack_mode
1524 );
1525 end if;
1526
1527 --
1528 end loop;
1529 --
1530 --
1531 -- Delete Primary Care Providers.
1532 --
1533 for pcp_rec in dpnt_pcp_c loop
1534 --
1535 l3_object_version_number := pcp_rec.object_version_number;
1536 --
1537 ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
1538 (p_validate => FALSE
1539 ,p_prmry_care_prvdr_id => pcp_rec.prmry_care_prvdr_id
1540 ,p_effective_start_date => l3_effective_start_date
1541 ,p_effective_end_date => l3_effective_end_date
1542 ,p_object_version_number => l3_object_version_number
1543 ,p_effective_date => p_effective_date
1544 ,p_datetrack_mode => p_datetrack_mode
1545 ,p_called_from => 'delete_enrollment'
1546 );
1547 --
1548 end loop;
1549 --
1550 -- Bug 4879122
1551 -- Delete Primary Care Providers Action Item.
1552 --
1553 l_dpnt_pcp_actn_typ_id := ben_enrollment_action_items.get_actn_typ_id
1554 (p_type_cd => 'PCPDPNT',
1555 p_business_group_id => p_business_group_id
1556 );
1557 --
1558 hr_utility.set_location('ACE l_dpnt_pcp_actn_typ_id = ' ||l_dpnt_pcp_actn_typ_id, 9999);
1559 --
1560 FOR l_dpnt_pcp_actn_item_rec IN c_dpnt_pcp_actn_item
1561 LOOP
1562 --
1563 hr_utility.set_location ('ACE prtt_enrt_actn_id = ' || l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id, 9999);
1564 --
1565 l3_object_version_number := l_dpnt_pcp_actn_item_rec.object_version_number;
1566 --
1567 -- 5096675, If Only one record exist in PEA for future change mode, dont call delete api
1568 if not ( p_datetrack_mode = hr_api.g_future_change and
1569 l_dpnt_pcp_actn_item_rec.effective_end_date = hr_api.g_eot ) then --5096675 rbingi
1570 --
1571 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1572 (p_validate => FALSE,
1573 p_effective_date => p_effective_date,
1574 p_business_group_id => p_business_group_id,
1575 p_datetrack_mode => p_datetrack_mode,
1576 p_object_version_number => l3_object_version_number,
1577 p_prtt_enrt_rslt_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_rslt_id,
1578 p_rslt_object_version_number => l3_object_version_number,
1579 p_post_rslt_flag => 'N',
1580 p_unsuspend_enrt_flag => 'Y',
1581 p_effective_start_date => l3_effective_start_date,
1582 p_effective_end_date => l3_effective_end_date,
1583 p_prtt_enrt_actn_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id
1584 );
1585 --
1586 end if;
1587 --
1588 END LOOP;
1589 --
1590 begin
1591 --
1592 -- Start of API User Hook for the before hook of delete_ELIG_CVRD_DPNT
1593 --
1594 ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_b
1595 (
1596 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1597 ,p_object_version_number => p_object_version_number
1598 ,p_effective_date => trunc(p_effective_date)
1599 ,p_datetrack_mode => p_datetrack_mode
1600 );
1601 exception
1602 when hr_api.cannot_find_prog_unit then
1603 hr_api.cannot_find_prog_unit_error
1604 (p_module_name => 'DELETE_ELIG_CVRD_DPNT'
1605 ,p_hook_type => 'BP'
1606 );
1607 --
1608 -- End of API User Hook for the before hook of delete_ELIG_CVRD_DPNT
1609 --
1610 end;
1611 --
1612 open dpnt_info_c;
1613 fetch dpnt_info_c into l_previous_values;
1614 close dpnt_info_c;
1615 --
1616 if l_previous_values.cvg_strt_dt is not null and l_previous_values.cvg_thru_dt = hr_api.g_eot then
1617 --
1618 -- Added by pxdas for logging change event needed for extract.
1619 --
1620 open c_chg_info(l_previous_values.prtt_enrt_rslt_id);
1621 fetch c_chg_info into l_chg_info;
1622 close c_chg_info;
1623 --
1624 -- Call the extract change event logging process.
1625 --
1626 ben_ext_chlg.log_dependent_chg
1627 (p_action => 'DELETE',
1628 p_pl_id => l_chg_info.pl_id,
1629 p_oipl_id => l_chg_info.oipl_id,
1630 p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
1631 p_cvg_end_dt => (l_previous_values.cvg_strt_dt-1),
1632 p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
1633 p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
1634 p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
1635 p_per_in_ler_id => l_previous_values.per_in_ler_id,
1636 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1637 p_person_id => l_chg_info.person_id,
1638 p_dpnt_person_id => l_previous_values.dpnt_person_id,
1639 p_business_group_id => p_business_group_id,
1640 p_effective_date => p_effective_date);
1641 --
1642 if p_datetrack_mode = 'DELETE' then
1643 l2_datetrack_mode := 'DELETE';
1644 else
1645 l2_datetrack_mode := 'ZAP';
1646 --
1647 end if;
1648 --
1649 remove_usage (
1650 p_validate => p_validate
1651 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1652 ,p_cvg_thru_dt => null
1653 ,p_effective_date => p_effective_date
1654 ,p_datetrack_mode => l2_datetrack_mode
1655 );
1656 --
1657 end if;
1658 --
1659 ben_pdp_del.del
1660 (
1661 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1662 ,p_effective_start_date => l_effective_start_date
1663 ,p_effective_end_date => l_effective_end_date
1664 ,p_object_version_number => l_object_version_number
1665 ,p_effective_date => p_effective_date
1666 ,p_datetrack_mode => p_datetrack_mode
1667 );
1668 --
1669 -- Bug No 4214527 Moved out the call to dpnt_actn_items after del
1670 -- Call Action item RCO if p_multi_row_actn = TRUE
1671 --
1672 if p_multi_row_actn then
1673 --
1674 dpnt_actn_items(
1675 p_prtt_enrt_rslt_id => null
1676 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1677 ,p_effective_date => p_effective_date
1678 ,p_business_group_id => null
1679 ,p_validate => p_validate
1680 ,p_datetrack_mode => p_datetrack_mode
1681 );
1682 --
1683 end if;
1684 --
1685 --
1686 --
1687 --
1688 -- Delete certifications
1689 --
1690 if p_datetrack_mode=hr_api.g_future_change then
1691 for ctfn_rec in dpnt_ctfn_c loop
1692 --
1693 l2_object_version_number := ctfn_rec.object_version_number;
1694
1695 open parent_c(ctfn_rec.prtt_enrt_actn_id,l_child_effective_date);
1696 fetch parent_c into l_parent_effective_end_date;
1697 close parent_c;
1698
1699 if p_datetrack_mode = hr_api.g_future_change and
1700 ctfn_rec.effective_end_date<>hr_api.g_eot and
1701 l_parent_effective_end_date = hr_api.g_eot then
1702 ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
1703 (p_validate => FALSE
1704 ,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
1705 ,p_effective_start_date => l2_effective_start_date
1706 ,p_effective_end_date => l2_effective_end_date
1707 ,p_object_version_number => l2_object_version_number
1708 ,p_business_group_id => p_business_group_id
1709 ,p_effective_date => l_child_effective_date
1710 ,p_datetrack_mode => p_datetrack_mode
1711 );
1712 end if;
1713
1714 --
1715 end loop;
1716 end if;
1717 --
1718 begin
1719 --
1720 -- Start of API User Hook for the after hook of delete_ELIG_CVRD_DPNT
1721 --
1722 ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_a
1723 (
1724 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1725 ,p_effective_start_date => l_effective_start_date
1726 ,p_effective_end_date => l_effective_end_date
1727 ,p_object_version_number => l_object_version_number
1728 ,p_effective_date => trunc(p_effective_date)
1729 ,p_datetrack_mode => p_datetrack_mode
1730 );
1731 exception
1732 when hr_api.cannot_find_prog_unit then
1733 hr_api.cannot_find_prog_unit_error
1734 (p_module_name => 'DELETE_ELIG_CVRD_DPNT'
1735 ,p_hook_type => 'AP'
1736 );
1737 --
1738 -- End of API User Hook for the after hook of delete_ELIG_CVRD_DPNT
1739 --
1740 end;
1741 --
1742 hr_utility.set_location(l_proc, 60);
1743 --
1744 -- When in validation only mode raise the Validate_Enabled exception
1745 --
1746 if p_validate then
1747 raise hr_api.validate_enabled;
1748 end if;
1749 --
1750 hr_utility.set_location(' Leaving:'||l_proc, 70);
1751 --
1752 exception
1753 --
1754 when hr_api.validate_enabled then
1755 --
1756 -- As the Validate_Enabled exception has been raised
1757 -- we must rollback to the savepoint
1758 --
1759 ROLLBACK TO delete_ELIG_CVRD_DPNT;
1760 --
1761 -- Only set output warning arguments
1762 -- (Any key or derived arguments must be set to null
1763 -- when validation only mode is being used.)
1764 --
1765 --
1766 when others then
1767 --
1768 -- A validation or unexpected error has occured
1769 --
1770 ROLLBACK TO delete_ELIG_CVRD_DPNT;
1771 p_object_version_number := l_object_version_number;
1772 p_effective_start_date := null;
1773 p_effective_end_date := null;
1774 raise;
1775 --
1776 end delete_ELIG_CVRD_DPNT;
1777 --
1778 -- Overloaded Procedure. 2386000
1779 -- ----------------------------------------------------------------------------
1780 -- |------------------------< delete_ELIG_CVRD_DPNT >----------------------|
1781 -- ----------------------------------------------------------------------------
1782 procedure delete_ELIG_CVRD_DPNT
1783 (p_validate in boolean default false
1784 ,p_elig_cvrd_dpnt_id in number
1785 ,p_effective_start_date out nocopy date
1786 ,p_effective_end_date out nocopy date
1787 ,p_object_version_number in out nocopy number
1788 ,p_business_group_id in number
1789 ,p_effective_date in date
1790 ,p_datetrack_mode in varchar2
1791 ,p_multi_row_actn in boolean default TRUE
1792 ,p_called_from in varchar2
1793 ) is
1794 --
1795 -- Declare cursors and local variables
1796 --
1797 l_proc varchar2(72) := g_package||'delete_ELIG_CVRD_DPNT';
1798 l_object_version_number ben_elig_cvrd_dpnt_f.object_version_number%TYPE;
1799 l_effective_start_date ben_elig_cvrd_dpnt_f.effective_start_date%TYPE;
1800 l_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
1801 l_parent_effective_end_date ben_elig_cvrd_dpnt_f.effective_end_date%TYPE;
1802 l2_object_version_number ben_cvrd_dpnt_ctfn_prvdd_f.object_version_number%TYPE;
1803 l2_effective_start_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_start_date%TYPE;
1804 l2_effective_end_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_end_date%TYPE;
1805 l3_object_version_number ben_prmry_care_prvdr_f.object_version_number%TYPE;
1806 l3_effective_start_date ben_prmry_care_prvdr_f.effective_start_date%TYPE;
1807 l3_effective_end_date ben_prmry_care_prvdr_f.effective_end_date%TYPE;
1808 l2_datetrack_mode varchar2(30);
1809 l_child_effective_date date;
1810 --
1811 cursor dpnt_info_c is
1812 select cvg_strt_dt,
1813 cvg_thru_dt,
1814 dpnt_person_id,
1815 prtt_enrt_rslt_id,
1816 per_in_ler_id
1817 from ben_elig_cvrd_dpnt_f
1818 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1819 and business_group_id = p_business_group_id
1820 and p_effective_date between effective_start_date
1821 and effective_end_date;
1822 --
1823 l_previous_values dpnt_info_c%rowtype;
1824 --
1825 cursor parent_c(cp_prtt_enrt_actn_id number,cp_effective_date date) is
1826 select effective_end_date
1827 from ben_prtt_enrt_actn_f
1828 where prtt_enrt_actn_id = cp_prtt_enrt_actn_id
1829 and business_group_id + 0 = p_business_group_id
1830 and cp_effective_date between effective_start_date and effective_end_date
1831
1832 ;
1833
1834 cursor dpnt_ctfn_c is
1835 select cvrd_dpnt_ctfn_prvdd_id,
1836 prtt_enrt_actn_id,
1837 object_version_number,
1838 effective_start_date,
1839 effective_end_date
1840 from ben_cvrd_dpnt_ctfn_prvdd_f
1841 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1842 and business_group_id = p_business_group_id
1843 and l_child_effective_date between effective_start_date
1844 and effective_end_date
1845 order by cvrd_dpnt_ctfn_prvdd_id asc;
1846 --
1847 cursor dpnt_pcp_c is
1848 select prmry_care_prvdr_id,
1849 object_version_number
1850 from ben_prmry_care_prvdr_f
1851 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1852 and business_group_id = p_business_group_id
1853 and p_effective_date between effective_start_date
1854 and effective_end_date;
1855 --
1856 cursor c_chg_info (p_prtt_enrt_rslt_id number) is
1857 SELECT pen.pl_id,
1858 pen.oipl_id,
1859 pen.person_id
1860 FROM ben_prtt_enrt_rslt_f pen
1861 WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1862 and pen.prtt_enrt_rslt_stat_cd is null
1863 and p_effective_date between pen.effective_start_date and pen.effective_end_date;
1864 --
1865 -- Bug 4879122
1866 --
1867 l_dpnt_pcp_actn_typ_id NUMBER (30);
1868 --
1869 CURSOR c_dpnt_pcp_actn_item
1870 IS
1871 SELECT pea.object_version_number, pea.prtt_enrt_actn_id,
1872 pea.prtt_enrt_rslt_id, pea.effective_end_date -- 5096675
1873 FROM ben_prtt_enrt_actn_f pea
1874 WHERE pea.business_group_id = p_business_group_id
1875 AND p_effective_date BETWEEN pea.effective_start_date
1876 AND pea.effective_end_date
1877 AND pea.actn_typ_id = l_dpnt_pcp_actn_typ_id
1878 AND pea.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;
1879 --
1880 l_chg_info c_chg_info%rowtype;
1881 l_env_rec ben_env_object.g_global_env_rec_type;
1882 --
1883 begin
1884 --
1885 hr_utility.set_location('Entering:'|| l_proc, 10);
1886 if ben_back_out_life_event.g_bolfe_effective_date is not null then
1887 l_child_effective_date:=ben_back_out_life_event.g_bolfe_effective_date;
1888 else
1889 l_child_effective_date:=p_effective_date;
1890 end if;
1891 --
1892 if fnd_global.conc_request_id = -1 then
1893 --
1894 ben_env_object.get(p_rec => l_env_rec);
1895 if l_env_rec.effective_date is null then
1896 --
1897 ben_env_object.init(p_business_group_id => p_business_group_id,
1898 p_effective_date => p_effective_date,
1899 p_thread_id => 1,
1900 p_chunk_size => 1,
1901 p_threads => 1,
1902 p_max_errors => 1,
1903 p_benefit_action_id => null);
1904 --
1905 end if;
1906 --
1907 end if;
1908 --
1909 -- Issue a savepoint if operating in validation only mode
1910 --
1911 savepoint delete_ELIG_CVRD_DPNT;
1912 --
1913 hr_utility.set_location(l_proc, 20);
1914 --
1915 -- Process Logic
1916 --
1917 l_object_version_number := p_object_version_number;
1918 --
1919 --
1920 -- Delete certifications
1921 --
1922 for ctfn_rec in dpnt_ctfn_c loop
1923 --
1924 l2_object_version_number := ctfn_rec.object_version_number;
1925
1926 -- open parent_c(ctfn_rec.prtt_enrt_actn_id,l_child_effective_date);
1927 -- fetch parent_c into l_parent_effective_end_date;
1928 -- close parent_c;
1929
1930 if p_datetrack_mode <> hr_api.g_future_change then
1931 --and
1932 -- (ctfn_rec.effective_end_date = hr_api.g_eot or
1933 -- l_parent_effective_end_date = hr_api.g_eot)) then
1934 ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
1935 (p_validate => FALSE
1936 ,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
1937 ,p_effective_start_date => l2_effective_start_date
1938 ,p_effective_end_date => l2_effective_end_date
1939 ,p_object_version_number => l2_object_version_number
1940 ,p_business_group_id => p_business_group_id
1941 ,p_effective_date => l_child_effective_date
1942 ,p_datetrack_mode => p_datetrack_mode
1943 ,p_called_from => p_called_from
1944 );
1945 end if;
1946
1947 --
1948 end loop;
1949 --
1950 --
1951 -- Delete Primary Care Providers.
1952 --
1953 for pcp_rec in dpnt_pcp_c loop
1954 --
1955 l3_object_version_number := pcp_rec.object_version_number;
1956 --
1957 ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
1958 (p_validate => FALSE
1959 ,p_prmry_care_prvdr_id => pcp_rec.prmry_care_prvdr_id
1960 ,p_effective_start_date => l3_effective_start_date
1961 ,p_effective_end_date => l3_effective_end_date
1962 ,p_object_version_number => l3_object_version_number
1963 ,p_effective_date => p_effective_date
1964 ,p_datetrack_mode => p_datetrack_mode
1965 ,p_called_from => 'delete_enrollment'
1966 );
1967 --
1968 end loop;
1969 --
1970 -- Bug 4879122
1971 -- Delete Primary Care Providers Action Item.
1972 --
1973 l_dpnt_pcp_actn_typ_id := ben_enrollment_action_items.get_actn_typ_id
1974 (p_type_cd => 'PCPDPNT',
1975 p_business_group_id => p_business_group_id
1976 );
1977 --
1978 hr_utility.set_location('ACE l_dpnt_pcp_actn_typ_id = ' ||l_dpnt_pcp_actn_typ_id, 9999);
1979 --
1980 FOR l_dpnt_pcp_actn_item_rec IN c_dpnt_pcp_actn_item
1981 LOOP
1982 --
1983 hr_utility.set_location ('ACE prtt_enrt_actn_id = ' || l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id, 9999);
1984 --
1985 l3_object_version_number := l_dpnt_pcp_actn_item_rec.object_version_number;
1986 --
1987 -- 5096675, If Only one record exist in PEA for future change mode, dont call delete api
1988 if not ( p_datetrack_mode = hr_api.g_future_change and
1989 l_dpnt_pcp_actn_item_rec.effective_end_date = hr_api.g_eot ) then --5096675 rbingi
1990 --
1991 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1992 (p_validate => FALSE,
1993 p_effective_date => p_effective_date,
1994 p_business_group_id => p_business_group_id,
1995 p_datetrack_mode => p_datetrack_mode,
1996 p_object_version_number => l3_object_version_number,
1997 p_prtt_enrt_rslt_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_rslt_id,
1998 p_rslt_object_version_number => l3_object_version_number,
1999 p_post_rslt_flag => 'N',
2000 p_unsuspend_enrt_flag => 'Y',
2001 p_effective_start_date => l3_effective_start_date,
2002 p_effective_end_date => l3_effective_end_date,
2003 p_prtt_enrt_actn_id => l_dpnt_pcp_actn_item_rec.prtt_enrt_actn_id
2004 );
2005 --
2006 end if;
2007 --
2008 END LOOP;
2009 --
2010 begin
2011 --
2012 -- Start of API User Hook for the before hook of delete_ELIG_CVRD_DPNT
2013 --
2014 ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_b
2015 (
2016 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2017 ,p_object_version_number => p_object_version_number
2018 ,p_effective_date => trunc(p_effective_date)
2019 ,p_datetrack_mode => p_datetrack_mode
2020 );
2021 exception
2022 when hr_api.cannot_find_prog_unit then
2023 hr_api.cannot_find_prog_unit_error
2024 (p_module_name => 'DELETE_ELIG_CVRD_DPNT'
2025 ,p_hook_type => 'BP'
2026 );
2027 --
2028 -- End of API User Hook for the before hook of delete_ELIG_CVRD_DPNT
2029 --
2030 end;
2031 --
2032 open dpnt_info_c;
2033 fetch dpnt_info_c into l_previous_values;
2034 close dpnt_info_c;
2035 --
2036 if l_previous_values.cvg_strt_dt is not null and l_previous_values.cvg_thru_dt = hr_api.g_eot then
2037 --
2038 -- Added by pxdas for logging change event needed for extract.
2039 --
2040 open c_chg_info(l_previous_values.prtt_enrt_rslt_id);
2041 fetch c_chg_info into l_chg_info;
2042 close c_chg_info;
2043 --
2044 -- Call the extract change event logging process.
2045 --
2046 ben_ext_chlg.log_dependent_chg
2047 (p_action => 'DELETE',
2048 p_pl_id => l_chg_info.pl_id,
2049 p_oipl_id => l_chg_info.oipl_id,
2050 p_cvg_strt_dt => l_previous_values.cvg_strt_dt,
2051 p_cvg_end_dt => (l_previous_values.cvg_strt_dt-1),
2052 p_old_cvg_strt_dt => l_previous_values.cvg_strt_dt,
2053 p_old_cvg_end_dt => l_previous_values.cvg_thru_dt,
2054 p_prtt_enrt_rslt_id => l_previous_values.prtt_enrt_rslt_id,
2055 p_per_in_ler_id => l_previous_values.per_in_ler_id,
2056 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
2057 p_person_id => l_chg_info.person_id,
2058 p_dpnt_person_id => l_previous_values.dpnt_person_id,
2059 p_business_group_id => p_business_group_id,
2060 p_effective_date => p_effective_date);
2061 --
2062 if p_datetrack_mode = 'DELETE' then
2063 l2_datetrack_mode := 'DELETE';
2064 else
2065 l2_datetrack_mode := 'ZAP';
2066 --
2067 end if;
2068 --
2069 /*
2070 Bug: 1485862. To un_end_date 'DPNT' person_type_usage for the dependents if
2071 the employee's termination event is backed out.
2072 */
2073 if (p_called_from = 'benbolfe') then
2074 un_end_date_dpnt_ptu (
2075 p_validate => p_validate
2076 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2077 ,p_cvg_thru_dt => null
2078 ,p_effective_date => p_effective_date
2079 ,p_datetrack_mode => l2_datetrack_mode
2080 );
2081 un_end_date_dpnt_pea
2082 (
2083 p_validate => p_validate
2084 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2085 ,p_effective_date => p_effective_date);
2086 else
2087 remove_usage (
2088 p_validate => p_validate
2089 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2090 ,p_cvg_thru_dt => null
2091 ,p_effective_date => p_effective_date
2092 ,p_datetrack_mode => l2_datetrack_mode
2093 );
2094 end if;
2095 /*
2096 Bug: 1485862. To un_end_date 'DPNT' person_type_usage for the dependents if
2097 the employee's termination event is backed out.
2098 */
2099 end if;
2100 --
2101 ben_pdp_del.del
2102 (
2103 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2104 ,p_effective_start_date => l_effective_start_date
2105 ,p_effective_end_date => l_effective_end_date
2106 ,p_object_version_number => l_object_version_number
2107 ,p_effective_date => p_effective_date
2108 ,p_datetrack_mode => p_datetrack_mode
2109 );
2110 --
2111 -- Bug No 4214527 Moved out call to dpnt_actn_items after del
2112 -- Call Action item RCO if p_multi_row_actn = TRUE
2113 --
2114 if p_multi_row_actn then
2115 --
2116 dpnt_actn_items(
2117 p_prtt_enrt_rslt_id => null
2118 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2119 ,p_effective_date => p_effective_date
2120 ,p_business_group_id => null
2121 ,p_validate => p_validate
2122 ,p_datetrack_mode => p_datetrack_mode
2123 );
2124 --
2125 end if;
2126 --
2127 --
2128 --
2129 --
2130 -- Delete certifications
2131 --
2132 if p_datetrack_mode=hr_api.g_future_change then
2133 for ctfn_rec in dpnt_ctfn_c loop
2134 --
2135 l2_object_version_number := ctfn_rec.object_version_number;
2136
2137 open parent_c(ctfn_rec.prtt_enrt_actn_id,l_child_effective_date);
2138 fetch parent_c into l_parent_effective_end_date;
2139 close parent_c;
2140
2141 if p_datetrack_mode = hr_api.g_future_change and
2142 ctfn_rec.effective_end_date<>hr_api.g_eot and
2143 l_parent_effective_end_date = hr_api.g_eot then
2144 ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
2145 (p_validate => FALSE
2146 ,p_cvrd_dpnt_ctfn_prvdd_id => ctfn_rec.cvrd_dpnt_ctfn_prvdd_id
2147 ,p_effective_start_date => l2_effective_start_date
2148 ,p_effective_end_date => l2_effective_end_date
2149 ,p_object_version_number => l2_object_version_number
2150 ,p_business_group_id => p_business_group_id
2151 ,p_effective_date => l_child_effective_date
2152 ,p_datetrack_mode => p_datetrack_mode
2153 ,p_called_from => p_called_from
2154 );
2155 end if;
2156
2157 --
2158 end loop;
2159 end if;
2160 --
2161 begin
2162 --
2163 -- Start of API User Hook for the after hook of delete_ELIG_CVRD_DPNT
2164 --
2165 ben_ELIG_CVRD_DPNT_bk3.delete_ELIG_CVRD_DPNT_a
2166 (
2167 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2168 ,p_effective_start_date => l_effective_start_date
2169 ,p_effective_end_date => l_effective_end_date
2170 ,p_object_version_number => l_object_version_number
2171 ,p_effective_date => trunc(p_effective_date)
2172 ,p_datetrack_mode => p_datetrack_mode
2173 );
2174 exception
2175 when hr_api.cannot_find_prog_unit then
2176 hr_api.cannot_find_prog_unit_error
2177 (p_module_name => 'DELETE_ELIG_CVRD_DPNT'
2178 ,p_hook_type => 'AP'
2179 );
2180 --
2181 -- End of API User Hook for the after hook of delete_ELIG_CVRD_DPNT
2182 --
2183 end;
2184 --
2185 hr_utility.set_location(l_proc, 60);
2186 --
2187 -- When in validation only mode raise the Validate_Enabled exception
2188 --
2189 if p_validate then
2190 raise hr_api.validate_enabled;
2191 end if;
2192 --
2193 hr_utility.set_location(' Leaving:'||l_proc, 70);
2194 --
2195 exception
2196 --
2197 when hr_api.validate_enabled then
2198 --
2199 -- As the Validate_Enabled exception has been raised
2200 -- we must rollback to the savepoint
2201 --
2202 ROLLBACK TO delete_ELIG_CVRD_DPNT;
2203 --
2204 -- Only set output warning arguments
2205 -- (Any key or derived arguments must be set to null
2206 -- when validation only mode is being used.)
2207 --
2208 --
2209 when others then
2210 --
2211 -- A validation or unexpected error has occured
2212 --
2213 ROLLBACK TO delete_ELIG_CVRD_DPNT;
2214 raise;
2215 --
2216 end delete_ELIG_CVRD_DPNT;
2217 --
2218 -- ----------------------------------------------------------------------------
2219 -- |-------------------------------< lck >------------------------------------|
2220 -- ----------------------------------------------------------------------------
2221 --
2222 procedure lck
2223 (
2224 p_elig_cvrd_dpnt_id in number
2225 ,p_object_version_number in number
2226 ,p_effective_date in date
2227 ,p_datetrack_mode in varchar2
2228 ,p_validation_start_date out nocopy date
2229 ,p_validation_end_date out nocopy date
2230 ) is
2231 --
2232 --
2233 -- Declare cursors and local variables
2234 --
2235 l_proc varchar2(72) := g_package||'lck';
2236 l_validation_start_date date;
2237 l_validation_end_date date;
2238 --
2239 begin
2240 --
2241 hr_utility.set_location('Entering:'|| l_proc, 10);
2242 --
2243 ben_pdp_shd.lck
2244 (
2245 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
2246 ,p_validation_start_date => l_validation_start_date
2247 ,p_validation_end_date => l_validation_end_date
2248 ,p_object_version_number => p_object_version_number
2249 ,p_effective_date => p_effective_date
2250 ,p_datetrack_mode => p_datetrack_mode
2251 );
2252 --
2253 hr_utility.set_location(' Leaving:'||l_proc, 70);
2254 --
2255 end lck;
2256 --
2257 -- ----------------------------------------------------------------------------
2258 -- |-------------------------------< dpnt_actn_items >--------------------------|
2259 -- ----------------------------------------------------------------------------
2260 --
2261 procedure dpnt_actn_items
2262 (
2263 p_prtt_enrt_rslt_id in number
2264 ,p_elig_cvrd_dpnt_id in number
2265 ,p_effective_date in date
2266 ,p_business_group_id in number
2267 ,p_validate in boolean default false
2268 ,p_datetrack_mode in varchar2
2269 ) is
2270 --
2271 l_proc varchar2(72) := g_package||'dpnt_actn_items';
2272 l_prtt_enrt_rslt_id number(15);
2273 l_business_group_id number(15);
2274 l_rslt_object_version_number number(9);
2275 l_suspend_flag varchar2(30);
2276 l_dpnt_actn_warning boolean;
2277 --Bug No 4525608 new dummy variable to pass to process_dpnt_actn_items
2278 l_ctfn_actn_warning boolean;
2279 --End Bug 4525608
2280 l_pcp_dpnt_actn_warning boolean;
2281 --
2282 cursor get_rslt_id_c is
2283 select prtt_enrt_rslt_id,
2284 business_group_id
2285 from ben_elig_cvrd_dpnt_f
2286 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
2287 and p_effective_date between effective_start_date
2288 and effective_end_date;
2289 --
2290 cursor get_rslt_ovn_c is
2291 select object_version_number,
2292 sspndd_flag
2293 from ben_prtt_enrt_rslt_f
2294 where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
2295 and business_group_id = l_business_group_id
2296 and prtt_enrt_rslt_stat_cd is null
2297 and p_effective_date between effective_start_date and effective_end_date
2298 and p_effective_date < enrt_cvg_thru_dt ; -- 5173425: Need not determine dpnts for End-dated enrollments.
2299 --
2300 begin
2301 --
2302 hr_utility.set_location('Entering:'|| l_proc, 10);
2303 --
2304 if p_prtt_enrt_rslt_id is null or
2305 p_business_group_id is null then
2306 open get_rslt_id_c;
2307 fetch get_rslt_id_c into l_prtt_enrt_rslt_id,
2308 l_business_group_id;
2309 close get_rslt_id_c;
2310 else
2311 l_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
2312 l_business_group_id := p_business_group_id;
2313 end if;
2314 --
2315 if l_prtt_enrt_rslt_id is not null then
2316 --
2317 open get_rslt_ovn_c;
2318 fetch get_rslt_ovn_c into l_rslt_object_version_number,
2319 l_suspend_flag;
2320 close get_rslt_ovn_c;
2321 --
2322 if l_rslt_object_version_number IS NOT NULL then -- 5173425: Added this condition
2323 ben_enrollment_action_items.process_dpnt_actn_items(
2324 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2325 ,p_rslt_object_version_number => l_rslt_object_version_number
2326 ,p_effective_date => trunc(p_effective_date)
2327 ,p_business_group_id => l_business_group_id
2328 ,p_validate => FALSE
2329 ,p_datetrack_mode => p_datetrack_mode
2330 ,p_suspend_flag => l_suspend_flag
2331 ,p_dpnt_actn_warning => l_dpnt_actn_warning
2332 --Bug No 4525608 new dummy variable to pass to process_dpnt_actn_items
2333 ,p_ctfn_actn_warning => l_ctfn_actn_warning
2334 );
2335 --End Bug 4525608
2336 --
2337 ben_enrollment_action_items.process_pcp_dpnt_actn_items(
2338 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2339 ,p_rslt_object_version_number => l_rslt_object_version_number
2340 ,p_effective_date => trunc(p_effective_date)
2341 ,p_business_group_id => l_business_group_id
2342 ,p_validate => FALSE
2343 ,p_datetrack_mode => p_datetrack_mode
2344 ,p_suspend_flag => l_suspend_flag
2345 ,p_pcp_dpnt_actn_warning => l_pcp_dpnt_actn_warning
2346 );
2347 end if; -- 5173425
2348 --
2349 end if;
2350 --
2351 hr_utility.set_location('Exiting:'|| l_proc, 40);
2352 --
2353 end dpnt_actn_items;
2354 --
2355 --
2356 --
2357 -- ----------------------------------------------------------------------------
2358 -- |-------------------------------< add_usage >--------------------------|
2359 -- ----------------------------------------------------------------------------
2360 --
2361 procedure add_usage (
2362 p_validate in boolean default false
2363 ,p_elig_cvrd_dpnt_id in number
2364 ,p_effective_date in date
2365 ,p_datetrack_mode in varchar2
2366 ) is
2367 --
2368 --
2369 -- Declare cursors and local variables
2370 --
2371 l_proc varchar2(72) := g_package||'add_usage';
2372 --
2373 l_person_type_id number(15);
2374 l_person_type_usage_id number(15);
2375 l_per_effective_start_date date;
2376 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
2377 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
2378 l1_person_type_usage_id number(15);
2379 l1_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
2380 l1_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
2381 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
2382 --
2383 l_dpnt_person_id number(15);
2384 l_cvg_strt_dt date;
2385 l_business_group_id number(15);
2386 --
2387 cursor get_dpnt_info_c is
2388 select dpnt_person_id,
2389 cvg_strt_dt,
2390 business_group_id
2391 from ben_elig_cvrd_dpnt_f
2392 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
2393 and p_effective_date between effective_start_date
2394 and effective_end_date;
2395 --
2396 --
2397 cursor get_person_info_c is
2398 select min(per.effective_start_date)
2399 from per_all_people_f per
2400 where per.person_id = l_dpnt_person_id;
2401 --
2402 --
2403 cursor get_dpnt_type_id_c is
2404 select person_type_id
2405 from per_person_types
2406 where system_person_type = 'DPNT'
2407 and business_group_id = l_business_group_id;
2408 --
2409 -- find overlapping ptu segments
2410 --
2411 cursor find_ptu_ovlp_segments_c is
2412 select a.effective_start_date,
2413 a.effective_end_date,
2414 a.person_type_usage_id
2415 from per_person_type_usages_f a
2416 where a.person_id = l_dpnt_person_id
2417 and a.person_type_id = l_person_type_id
2418 -- and a.effective_start_date <= hr_api.g_date
2419 and a.effective_end_date >= l_cvg_strt_dt
2420 order by a.effective_start_date -- 5604361
2421 ;
2422 --
2423 begin
2424 --
2425 hr_utility.set_location('Entering:'|| l_proc, 10);
2426 --
2427 open get_dpnt_info_c;
2428 fetch get_dpnt_info_c into l_dpnt_person_id,
2429 l_cvg_strt_dt,
2430 l_business_group_id;
2431 --
2432 if get_dpnt_info_c%NOTFOUND then
2433 -- error
2434 close get_dpnt_info_c;
2435 return;
2436 --
2437 end if;
2438 --
2439 close get_dpnt_info_c;
2440 --
2441 -- get the minimum effective start date of the dependents record.
2442 --
2443 open get_person_info_c;
2444 fetch get_person_info_c into l_per_effective_start_date;
2445 close get_person_info_c;
2446 --
2447 -- Person type usage cannot start before the person exists.
2448 --
2449 if l_per_effective_start_date > l_cvg_strt_dt then
2450 l_cvg_strt_dt := l_per_effective_start_date;
2451 end if;
2452 --
2453 --
2454 -- get type id
2455 --
2456 open get_dpnt_type_id_c;
2457 fetch get_dpnt_type_id_c into l_person_type_id;
2458 if get_dpnt_type_id_c%FOUND then
2459 --
2460 open find_ptu_ovlp_segments_c;
2461 fetch find_ptu_ovlp_segments_c into l_effective_start_date,
2462 l_effective_end_date,
2463 l_person_type_usage_id;
2464 if find_ptu_ovlp_segments_c%NOTFOUND then
2465 --
2466 -- call create person_type usage api
2467 --
2468 hr_per_type_usage_internal.create_person_type_usage
2469 (p_validate => FALSE
2470 ,p_person_id => l_dpnt_person_id
2471 ,p_person_type_id => l_person_type_id
2472 ,p_effective_date => l_cvg_strt_dt
2473 ,p_person_type_usage_id => l1_person_type_usage_id
2474 ,p_object_version_number => l_object_version_number
2475 ,p_effective_start_date => l1_effective_start_date
2476 ,p_effective_end_date => l1_effective_end_date
2477 );
2478 --
2479 else
2480 --
2481 -- changed all g_date to g_eot
2482 --
2483 if l_effective_start_date <= l_cvg_strt_dt and
2484 l_effective_end_date >= hr_api.g_eot
2485 then
2486 null;
2487 elsif l_effective_start_date <= l_cvg_strt_dt and
2488 l_effective_end_date < hr_api.g_eot
2489 then
2490 update per_person_type_usages_f
2491 set effective_end_date = hr_api.g_eot
2492 where person_type_usage_id = l_person_type_usage_id;
2493 elsif l_effective_start_date > l_cvg_strt_dt and
2494 l_effective_end_date = hr_api.g_eot then
2495 --
2496 update per_person_type_usages_f
2497 set effective_start_date = l_cvg_strt_dt
2498 where person_type_usage_id = l_person_type_usage_id;
2499 --
2500 elsif l_effective_start_date > l_cvg_strt_dt and
2501 l_effective_end_date < hr_api.g_eot then
2502 --
2503 update per_person_type_usages_f
2504 set effective_start_date = l_cvg_strt_dt,
2505 effective_end_date = hr_api.g_eot
2506 where person_type_usage_id = l_person_type_usage_id;
2507 --
2508 end if;
2509 --
2510 close find_ptu_ovlp_segments_c;
2511 --
2512 end if;
2513 --
2514 end if;
2515 --
2516 close get_dpnt_type_id_c;
2517 --
2518 --
2519 hr_utility.set_location(' Leaving:'||l_proc, 70);
2520 --
2521 end add_usage;
2522 --
2523 --
2524 -- ----------------------------------------------------------------------------
2525 -- |-------------------------------< remove_usage >--------------------------|
2526 -- ----------------------------------------------------------------------------
2527 --
2528 procedure remove_usage (
2529 p_validate in boolean default false
2530 ,p_elig_cvrd_dpnt_id in number
2531 ,p_cvg_thru_dt in date
2532 ,p_effective_date in date
2533 ,p_datetrack_mode in varchar2
2534 ) is
2535 --
2536 --
2537 -- Declare cursors and local variables
2538 --
2539 l_proc varchar2(72) := g_package||'remove_usage';
2540 l_exist varchar2(1);
2541 l_dpnt_person_id number(15);
2542 l_cvg_strt_dt date;
2543 l_end_dt date;
2544 l_person_type_usage_id number(15);
2545 l_object_version_number number(9);
2546 l_effective_start_date date;
2547 l_eff_strt_date date;
2548 l_effective_end_date date;
2549 l_business_group_id number(15);
2550 l_datetrack_mode varchar2(30);
2551 l_eff_end_date date;
2552 --
2553 --
2554 cursor get_dpnt_info_c is
2555 select dpnt_person_id,
2556 cvg_strt_dt,
2557 business_group_id
2558 from ben_elig_cvrd_dpnt_f
2559 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
2560 and p_effective_date between effective_start_date
2561 and effective_end_date;
2562 --
2563 cursor other_dpnt_c is
2564 select null
2565 from ben_elig_cvrd_dpnt_f a,
2566 ben_per_in_ler pil
2567 where a.dpnt_person_id = l_dpnt_person_id
2568 and a.elig_cvrd_dpnt_id <> p_elig_cvrd_dpnt_id
2569 and a.cvg_strt_dt is not null
2570 and a.cvg_thru_dt = hr_api.g_eot
2571 -- and a.cvrd_flag = 'Y'
2572 and l_end_dt + 1 between a.cvg_strt_dt -- Bug 5451726
2573 and nvl(a.cvg_thru_dt, hr_api.g_date)
2574 and p_effective_date between a.effective_start_date
2575 and a.effective_end_date
2576 and a.per_in_ler_id = pil.per_in_ler_id
2577 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2578 --
2579 cursor usage_c is
2580 select a.person_type_usage_id,
2581 a.object_version_number,
2582 a.effective_start_date,
2583 a.effective_end_date
2584 from per_person_type_usages_f a,
2585 per_person_types b
2586 where a.person_id = l_dpnt_person_id
2587 and a.person_type_id = b.person_type_id
2588 and b.system_person_type = 'DPNT'
2589 and b.business_group_id = l_business_group_id
2590 and l_end_dt between a.effective_start_date
2591 and a.effective_end_date;
2592 --
2593 begin
2594 --
2595 open get_dpnt_info_c;
2596 fetch get_dpnt_info_c into l_dpnt_person_id,
2597 l_cvg_strt_dt,
2598 l_business_group_id;
2599 --
2600 if get_dpnt_info_c%NOTFOUND then
2601 -- error
2602 null;
2603 --
2604 end if;
2605 --
2606 close get_dpnt_info_c;
2607 --
2608 if (p_cvg_thru_dt is not null
2609 and p_cvg_thru_dt > l_cvg_strt_dt) then -- 5655342
2610 l_end_dt := p_cvg_thru_dt;
2611 else
2612 l_end_dt := l_cvg_strt_dt;
2613 end if;
2614 --
2615 open other_dpnt_c;
2616 fetch other_dpnt_c into l_exist;
2617 if other_dpnt_c%NOTFOUND then
2618 --
2619 open usage_c;
2620 fetch usage_c into l_person_type_usage_id,
2621 l_object_version_number,
2622 l_eff_strt_date,
2623 l_eff_end_date;
2624 --
2625 if usage_c%FOUND then
2626 if p_datetrack_mode = 'ZAP' then
2627 if l_eff_strt_date < l_end_dt then
2628 l_datetrack_mode := 'DELETE';
2629 if l_eff_end_date=l_end_dt then
2630 close usage_c;
2631 close other_dpnt_c;
2632 hr_utility.set_location(' Leaving:'||l_proc, 64);
2633 return;
2634 end if;
2635 else
2636 l_datetrack_mode := 'ZAP';
2637 end if;
2638 --
2639 -- Check to see if the row is already end dated
2640 --
2641 elsif p_datetrack_mode=hr_api.g_delete and
2642 l_eff_end_date=l_end_dt then
2643 close usage_c;
2644 close other_dpnt_c;
2645 hr_utility.set_location(' Leaving:'||l_proc, 65);
2646 return;
2647 elsif p_cvg_thru_dt < l_eff_strt_date then -- 5655342
2648 l_datetrack_mode := 'ZAP';
2649 else
2650 l_datetrack_mode := p_datetrack_mode;
2651 end if;
2652 hr_per_type_usage_internal.delete_person_type_usage
2653 (p_validate => FALSE
2654 ,p_person_type_usage_id => l_person_type_usage_id
2655 ,p_effective_date => l_end_dt
2656 ,p_datetrack_mode => l_datetrack_mode
2657 ,p_object_version_number => l_object_version_number
2658 ,p_effective_start_date => l_effective_start_date
2659 ,p_effective_end_date => l_effective_end_date
2660 );
2661 --
2662 end if;
2663 --
2664 close usage_c;
2665 --
2666 end if;
2667 --
2668 close other_dpnt_c;
2669 --
2670 hr_utility.set_location(' Leaving:'||l_proc, 70);
2671 --
2672 end remove_usage;
2673 --
2674 --
2675 -- bug : 1418754 : Max number of dependents for a comp object enrollment
2676 -- have to be checked as part of post-forms commit.
2677 -- If user uncovers one dependent and covers other dependent then,
2678 -- this check have to be done after making changes to the rows.
2679 --
2680 Procedure chk_max_num_dpnt_for_pen (p_prtt_enrt_rslt_id in number,
2681 p_effective_date in date,
2682 p_business_group_id in number) as
2683 --
2684 g_package varchar2(72) := null;
2685 l_proc varchar2(72) := g_package||'chk_max_num_dpnt_for_pen';
2686 l_api_updating boolean;
2687 --
2688 l_temp varchar2(1);
2689 l_total_num_dpnt number(15);
2690 l_rlshp_num_dpnt number(15);
2691 -- l_person_id number(15);
2692 -- l_pl_id number(15);
2693 -- l_oipl_id number(15);
2694 -- l_opt_id number(15);
2695 l_contact_type per_contact_relationships.contact_type%type; -- varchar2(30);
2696 l_t_mx_dpnts_alwd_num number(15);
2697 l_t_no_mx_num_dfnd_flag varchar2(1);
2698 l_r_mx_dpnts_alwd_num number(15);
2699 l_r_no_mx_num_dfnd_flag varchar2(1);
2700 l_dsgn_rqmt_id number(15);
2701 l_heir number(15);
2702 l_grp_rlshp_cd varchar2(30);
2703 l_grp_rlshp_meaning varchar2(30);
2704 ---- Added
2705 l_pl_name ben_pl_f.name%type; -- UTF8 Change Bug 2254683
2706 l_opt_name ben_opt_f.name%type; -- UTF8 Change Bug 2254683
2707 l_rel_name hr_lookups.meaning%type; -- UTF8 Change Bug 2254683
2708 --
2709 -- get required info
2710 --
2711 cursor info1_c is
2712 select r.person_id
2713 ,egd.dpnt_person_id
2714 ,r.pl_id
2715 ,r.oipl_id
2716 ,o.opt_id
2717 ,egd.cvg_strt_dt
2718 ,egd.cvg_thru_dt
2719 from ben_prtt_enrt_rslt_f r,
2720 ben_elig_cvrd_dpnt_f egd,
2721 ben_oipl_f o ,
2722 ben_per_in_ler pil
2723 where r.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2724 and r.prtt_enrt_rslt_id = egd.prtt_enrt_rslt_id
2725 and r.business_group_id + 0 = p_business_group_id
2726 and r.per_in_ler_id = egd.per_in_ler_id
2727 and r.per_in_ler_id = pil.per_in_ler_id
2728 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2729 and r.prtt_enrt_rslt_stat_cd is null
2730 and p_effective_date between r.effective_start_date
2731 and r.effective_end_date
2732 and o.oipl_id(+) = r.oipl_id
2733 and o.business_group_id(+)= p_business_group_id
2734 and p_effective_date between o.effective_start_date(+)
2735 and o.effective_end_date(+)
2736 and egd.cvg_strt_dt is not null
2737 and egd.cvg_thru_dt = hr_api.g_eot
2738 -- bug#2151619
2739 and egd.effective_end_date = hr_api.g_eot;
2740 /*
2741 and p_effective_date between egd.effective_start_date
2742 and egd.effective_end_date
2743 ;
2744 */
2745 --
2746
2747 cursor info2_c(cv_person_id number,
2748 cv_dpnt_person_id number) is
2749 select c.contact_type
2750 from per_contact_relationships c
2751 where c.person_id = cv_person_id
2752 and c.contact_person_id = cv_dpnt_person_id
2753 -- Bug 1762932 added the personal_flag condition
2754 and nvl(c.personal_flag, 'N') = 'Y'
2755 and c.business_group_id = p_business_group_id
2756 and p_effective_date between nvl(c.date_start, p_effective_date)
2757 and nvl(c.date_end, p_effective_date)
2758 ;
2759 --
2760 -- total designation requirements
2761 --
2762 cursor total_rqmt_c(cv_pl_id number,
2763 cv_oipl_id number,
2764 cv_opt_id number) is
2765 select mx_dpnts_alwd_num
2766 ,no_mx_num_dfnd_flag
2767 ,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
2768 from ben_dsgn_rqmt_f
2769 where
2770 ((nvl(pl_id, hr_api.g_number) = cv_pl_id)
2771 or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
2772 or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
2773 and dsgn_typ_cd = 'DPNT'
2774 and grp_rlshp_cd is null
2775 and business_group_id + 0 = p_business_group_id
2776 and p_effective_date between effective_start_date
2777 and effective_end_date
2778 order by heir
2779 ;
2780
2781 --
2782 -- any designation requirements for this comp object?
2783 --
2784 cursor any_rqmt_c(cv_pl_id number,
2785 cv_oipl_id number,
2786 cv_opt_id number)is
2787 select 's'
2788 from ben_dsgn_rqmt_f r
2789 where ((nvl(pl_id, hr_api.g_number) = cv_pl_id)
2790 or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
2791 or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
2792 and r.dsgn_typ_cd = 'DPNT'
2793 and r.business_group_id = p_business_group_id
2794 and p_effective_date between nvl(r.effective_start_date, p_effective_date)
2795 and nvl(r.effective_end_date, p_effective_date)
2796 ;
2797
2798 --
2799 -- designation requirement for relationship type of this dpnt
2800 --
2801 cursor rlshp_rqmt_c(cv_pl_id number,
2802 cv_oipl_id number,
2803 cv_opt_id number,
2804 cv_person_id number,
2805 cv_dpnt_person_id number) is
2806 select r.mx_dpnts_alwd_num
2807 ,r.no_mx_num_dfnd_flag
2808 ,r.dsgn_rqmt_id
2809 ,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
2810 ,r.grp_rlshp_cd
2811 from ben_dsgn_rqmt_f r,
2812 ben_dsgn_rqmt_rlshp_typ dr
2813 where ((nvl(pl_id, hr_api.g_number) = cv_pl_id)
2814 or (nvl(oipl_id, hr_api.g_number) = cv_oipl_id)
2815 or (nvl(opt_id, hr_api.g_number) = cv_opt_id))
2816 and r.dsgn_typ_cd = 'DPNT'
2817 and r.business_group_id = p_business_group_id
2818 and p_effective_date between nvl(r.effective_start_date, p_effective_date)
2819 and nvl(r.effective_end_date, p_effective_date)
2820 and dr.dsgn_rqmt_id = r.dsgn_rqmt_id
2821 and dr.rlshp_typ_cd in (select c.contact_type
2822 from per_contact_relationships c
2823 where c.person_id = cv_person_id
2824 and c.contact_person_id = cv_dpnt_person_id
2825 and nvl(c.personal_flag, 'N') = 'Y'
2826 and c.business_group_id = p_business_group_id
2827 and p_effective_date between nvl(c.date_start, p_effective_date)
2828 and nvl(c.date_end, p_effective_date) )
2829 order by heir ;
2830 --
2831 -- total number of covered dependents for the result
2832 --
2833 cursor total_num_dpnt_c(cv_cvg_strt_dt date,
2834 cv_cvg_thru_dt date) is
2835 select count(elig_cvrd_dpnt_id)
2836 from ben_elig_cvrd_dpnt_f ecd ,
2837 ben_per_in_ler pil
2838 where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2839 and ecd.cvg_strt_dt is not null
2840 and ecd.cvg_thru_dt = hr_api.g_eot
2841 -- and cvrd_flag = 'Y'
2842 and ecd.business_group_id + 0 = p_business_group_id
2843 and p_effective_date between ecd.effective_start_date
2844 and ecd.effective_end_date
2845 and cv_cvg_strt_dt <= nvl(ecd.cvg_thru_dt, hr_api.g_date)
2846 and nvl(cv_cvg_thru_dt, hr_api.g_date) >= ecd.cvg_strt_dt
2847 and ecd.per_in_ler_id = pil.per_in_ler_id
2848 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2849 ;
2850 --
2851 --
2852 -- number of covered dependents of any of the rel types covered
2853 -- by the appropriate dsgn rqmt.
2854
2855 cursor rlshp_num_dpnt_c(cv_person_id number,
2856 cv_cvg_strt_dt date,
2857 cv_cvg_thru_dt date) is
2858 select count(*)
2859 from per_contact_relationships c
2860 , ben_elig_cvrd_dpnt_f d
2861 , ben_per_in_ler pil
2862 where
2863 c.person_id = cv_person_id
2864 and c.contact_person_id = d.dpnt_person_id
2865 and d.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2866 and d.cvg_strt_dt is not null
2867 and d.cvg_thru_dt = hr_api.g_eot
2868 and cv_cvg_strt_dt <= nvl(d.cvg_thru_dt, hr_api.g_date)
2869 and nvl(cv_cvg_thru_dt, hr_api.g_date) >= d.cvg_strt_dt
2870 and c.business_group_id + 0 = p_business_group_id
2871 and p_effective_date between nvl(c.date_start, p_effective_date)
2872 and nvl(c.date_end, p_effective_date)
2873 and d.effective_end_date = hr_api.g_eot -- bug 1237204
2874 and d.business_group_id + 0 = p_business_group_id
2875 and c.contact_type in
2876 (select rlshp_typ_cd
2877 from ben_dsgn_rqmt_rlshp_typ
2878 where dsgn_rqmt_id = l_dsgn_rqmt_id)
2879 and d.per_in_ler_id = pil.per_in_ler_id
2880 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2881 ;
2882
2883
2884
2885 -- Added for bug no. 1845251
2886
2887 cursor get_pln_opt_c is
2888
2889 select p.name pl_name, o.name opt_name
2890 from ben_pl_f p,
2891 ben_opt_f o,
2892 ben_oipl_f op,
2893 ben_prtt_enrt_rslt_f en
2894 where en.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2895 and p.pl_id=en.pl_id
2896 and en.oipl_id = op.oipl_id(+)
2897 and op.opt_id=o.opt_id(+)
2898 and en.prtt_enrt_rslt_stat_cd is null
2899 and p_effective_date between en.effective_start_date and en.effective_end_date
2900 and p_effective_date between p.effective_start_date and p.effective_end_date
2901 and p_effective_date between o.effective_start_date(+) and o.effective_end_date(+)
2902 and p_effective_date between op.effective_start_date(+) and op.effective_end_date(+)
2903
2904 ;
2905
2906
2907 cursor get_rel_name_c(cv_contact_type varchar2) is
2908
2909 select meaning
2910 from hr_lookups
2911 where lookup_code=cv_contact_type
2912 and lookup_type='CONTACT'
2913 ;
2914
2915 --
2916
2917 Begin
2918 --
2919 hr_utility.set_location('Entering:'||l_proc,5);
2920 --
2921 if p_prtt_enrt_rslt_id is not null then
2922 --
2923 hr_utility.set_location('open info1_c :'||l_proc,10);
2924 --
2925 for l_pen_pdp_rec in info1_c loop
2926 --
2927 open info2_c(l_pen_pdp_rec.person_id,
2928 l_pen_pdp_rec.dpnt_person_id);
2929 fetch info2_c into l_contact_type;
2930 if info2_c%notfound then
2931 --
2932 close info2_c;
2933 --
2934 -- raise error as there are no contact relationship
2935 --
2936 fnd_message.set_name('BEN', 'BEN_91652_NO_CNTCT_RLSHP');
2937 fnd_message.raise_error;
2938 --
2939 else
2940 -- Check if there are any requirements at all
2941 hr_utility.set_location(l_proc, 12);
2942 close info2_c;
2943 open any_rqmt_c(l_pen_pdp_rec.pl_id,
2944 l_pen_pdp_rec.oipl_id,
2945 l_pen_pdp_rec.opt_id);
2946 fetch any_rqmt_c into
2947 l_temp;
2948 --
2949 if any_rqmt_c%found then
2950 --
2951 close any_rqmt_c;
2952 -- Check total max requirement
2953 hr_utility.set_location(l_proc, 15);
2954 open total_rqmt_c(l_pen_pdp_rec.pl_id,
2955 l_pen_pdp_rec.oipl_id,
2956 l_pen_pdp_rec.opt_id);
2957 fetch total_rqmt_c into
2958 l_t_mx_dpnts_alwd_num
2959 ,l_t_no_mx_num_dfnd_flag
2960 ,l_heir;
2961
2962 if total_rqmt_c%notfound then
2963 -- there is no total max # limitation
2964 close total_rqmt_c;
2965
2966 else
2967 close total_rqmt_c;
2968 if l_t_no_mx_num_dfnd_flag = 'N' then
2969 open total_num_dpnt_c(l_pen_pdp_rec.cvg_strt_dt,
2970 l_pen_pdp_rec.cvg_thru_dt);
2971 fetch total_num_dpnt_c into l_total_num_dpnt;
2972 close total_num_dpnt_c;
2973
2974 hr_utility.set_location('total_mx '||to_char(l_t_mx_dpnts_alwd_num)||
2975 'total_dpnt '||l_total_num_dpnt, 18);
2976
2977
2978 if l_total_num_dpnt > l_t_mx_dpnts_alwd_num then
2979 -- error as total # of cov dependents will exceed total max
2980
2981 fnd_message.set_name('BEN', 'BEN_91653_DPNT_MAX_NUM_EXCDD');
2982 fnd_message.raise_error;
2983
2984 end if; -- l_total_num_dpnt > l_t_mx_dpnts_alwd_num
2985 end if; -- l_t_no_mx_num_dfnd_flag = 'N'
2986 end if; -- total_rqmt_c
2987
2988 -- Check max requirement for relationship type
2989 hr_utility.set_location('LAMC: l_contact_type '||l_contact_type,30);
2990 hr_utility.set_location('l_opt_id '||to_char(l_pen_pdp_rec.opt_id),30);
2991 hr_utility.set_location('l_oipl_id '||to_char(l_pen_pdp_rec.oipl_id), 30);
2992
2993 open rlshp_rqmt_c(l_pen_pdp_rec.pl_id,
2994 l_pen_pdp_rec.oipl_id,
2995 l_pen_pdp_rec.opt_id,
2996 l_pen_pdp_rec.person_id,
2997 l_pen_pdp_rec.dpnt_person_id);
2998 fetch rlshp_rqmt_c into
2999 l_r_mx_dpnts_alwd_num
3000 ,l_r_no_mx_num_dfnd_flag
3001 ,l_dsgn_rqmt_id
3002 ,l_heir
3003 ,l_grp_rlshp_cd;
3004
3005 if rlshp_rqmt_c%notfound then
3006
3007 -- No rqmts for this relationship type, do not allow dsgn.
3008 hr_utility.set_location('No rlshp rqmts', 20);
3009 close rlshp_rqmt_c;
3010 fnd_message.set_name('BEN', 'BEN_91971_NO_DPNTS_ALWD');
3011 fnd_message.raise_error;
3012
3013 else
3014 hr_utility.set_location('total_rlshp_mx '||to_char(l_r_mx_dpnts_alwd_num), 20);
3015 close rlshp_rqmt_c;
3016 if l_r_no_mx_num_dfnd_flag = 'N' then
3017
3018 hr_utility.set_location('l_dsgn_rqmt_id'||to_char(l_dsgn_rqmt_id),30);
3019 hr_utility.set_location('l_person_id'||to_char(l_pen_pdp_rec.person_id),30);
3020 open rlshp_num_dpnt_c(l_pen_pdp_rec.person_id,
3021 l_pen_pdp_rec.cvg_strt_dt,
3022 l_pen_pdp_rec.cvg_thru_dt);
3023 fetch rlshp_num_dpnt_c into l_rlshp_num_dpnt;
3024 close rlshp_num_dpnt_c;
3025 hr_utility.set_location('rlshp_dpnt_mx '||to_char(l_rlshp_num_dpnt), 30);
3026
3027 if l_rlshp_num_dpnt > l_r_mx_dpnts_alwd_num then
3028
3029 -- error as # of cov dependents of this rel type will exceed max
3030 --
3031 --Bug 3015999
3032 -- Message without relationship type created
3033 --fnd_message.set_name('BEN', 'BEN_91654_DPNT_RL_MAX_NUM_EXCD');
3034 --Bug 4080815 Message with relationship group created
3035 fnd_message.set_name('BEN', 'BEN_94125_REL_GRP_MAX_EXCD');
3036
3037 -- Added for bug no. 1845251
3038 --fnd_message.set_token('MAX',l_r_mx_dpnts_alwd_num);
3039
3040 open get_pln_opt_c;
3041 fetch get_pln_opt_c into l_pl_name,l_opt_name;
3042 close get_pln_opt_c;
3043 fnd_message.set_token('PLANOPT',l_pl_name||' '||l_opt_name);
3044
3045 l_grp_rlshp_meaning := hr_general.decode_lookup('BEN_GRP_RLSHP', l_grp_rlshp_cd);
3046 fnd_message.set_token('GROUP',l_grp_rlshp_meaning);
3047 --open get_rel_name_c(l_contact_type);
3048 --fetch get_rel_name_c into l_rel_name;
3049 --close get_rel_name_c;
3050 --fnd_message.set_token('REL',l_rel_name);
3051 --End Bug 3015999
3052
3053 fnd_message.raise_error;
3054
3055 end if;
3056 end if;
3057 end if;
3058 else
3059 --
3060 -- there are no rqmts at all, allow dsgn.
3061 --
3062 close any_rqmt_c;
3063 --
3064 end if; -- any_rqmt_c%FOUND
3065 end if; -- info2_c%notfound
3066 end loop; -- info1_c
3067 end if; -- p_prtt_enrt_rslt_id is not null
3068 --
3069 hr_utility.set_location('Leaving:'||l_proc,99);
3070 --
3071 end chk_max_num_dpnt_for_pen;
3072 --
3073 --
3074 -- Self-service wrapper for dpnt_actn_items.
3075 --
3076 procedure dpnt_actn_items_w
3077 (p_prtt_enrt_rslt_id in number
3078 ,p_effective_date in date
3079 ,p_business_group_id in number
3080 ,p_datetrack_mode in varchar2)
3081 is
3082 begin
3083
3084 dpnt_actn_items(
3085 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
3086 p_elig_cvrd_dpnt_id => null,
3087 p_effective_date => p_effective_date,
3088 p_business_group_id => p_business_group_id,
3089 p_datetrack_mode => p_datetrack_mode);
3090
3091 chk_max_num_dpnt_for_pen(
3092 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
3093 p_effective_date => p_effective_date,
3094 p_business_group_id => p_business_group_id);
3095
3096 exception
3097 when others then
3098 fnd_msg_pub.add;
3099
3100 end;
3101 --
3102 end ben_ELIG_CVRD_DPNT_api;