[Home] [Help]
PACKAGE BODY: APPS.BEN_PRTT_ENRT_CTFN_PRVDD_API
Source
1 Package Body ben_PRTT_ENRT_CTFN_PRVDD_api as
2 /* $Header: bepcsapi.pkb 120.1.12010000.2 2008/08/05 15:06:09 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_PRTT_ENRT_CTFN_PRVDD_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< check_prtt_ctfn> ----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure check_prtt_ctfn
13 (p_prtt_enrt_actn_id in number,
14 p_datetrack_mode in varchar2,
15 p_business_group_id in number,
16 p_effective_date in date) is
17 --
18 l_all_prvdd boolean := FALSE;
19 --
20 cursor prtt_c is
21 select pen.prtt_enrt_rslt_id,
22 pen.person_id,
23 pea.prtt_enrt_actn_id,
24 pea.actn_typ_id,
25 pea.cmpltd_dt,
26 pea.rqd_flag,
27 pen.object_version_number rslt_ovn,
28 pea.object_version_number
29 from ben_prtt_enrt_rslt_f pen,
30 ben_prtt_enrt_actn_f pea
31 where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
32 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
33 and pea.business_group_id = p_business_group_id
34 and pen.prtt_enrt_rslt_stat_cd is null
35 and p_effective_date between pea.effective_start_date
36 and pea.effective_end_date
37 and pen.business_group_id = p_business_group_id
38 and p_effective_date between pen.effective_start_date
39 and pen.effective_end_date;
40 --
41 l_prtt prtt_c%rowtype;
42 --
43 l_proc varchar2(80) := g_package||'check_prtt_ctfn';
44 --
45 begin
46 --
47 hr_utility.set_location('Entering '||l_proc, 10);
48 --
49 open prtt_c;
50 fetch prtt_c into l_prtt;
51 close prtt_c;
52 --
53 if l_prtt.prtt_enrt_rslt_id is not null and
54 l_prtt.person_id is not null then
55 --
56 --
57 l_all_prvdd := ben_enrollment_action_items.check_enrt_ctfn
58 (p_prtt_enrt_actn_id => l_prtt.prtt_enrt_actn_id
59 ,p_prtt_enrt_rslt_id => l_prtt.prtt_enrt_rslt_id
60 ,p_effective_date => p_effective_date);
61 --
62 ben_enrollment_action_items.process_action_item
63 (p_prtt_enrt_actn_id => l_prtt.prtt_enrt_actn_id
64 ,p_actn_typ_id => l_prtt.actn_typ_id
65 ,p_cmpltd_dt => l_prtt.cmpltd_dt
66 ,p_object_version_number => l_prtt.object_version_number
67 ,p_effective_date => p_effective_date
68 ,p_rqd_data_found => l_all_prvdd
69 ,p_prtt_enrt_rslt_id => l_prtt.prtt_enrt_rslt_id
70 ,p_rqd_flag => l_prtt.rqd_flag
71 ,p_post_rslt_flag => 'Y' -- 3626176
72 ,p_business_group_id => p_business_group_id
73 ,p_datetrack_mode => p_datetrack_mode
74 ,p_rslt_object_version_number => l_prtt.rslt_ovn);
75 --
76 end if;
77 --
78 hr_utility.set_location('Leaving '||l_proc, 10);
79 --
80 end check_prtt_ctfn;
81 --
82 -- ----------------------------------------------------------------------------
83 -- |------------------------< check_dnd_ctfns> ----------------------|
84 -- ----------------------------------------------------------------------------
85 --
86 procedure check_dnd_ctfns
87 (p_prtt_enrt_rslt_id in number,
88 p_effective_date in date,
89 p_business_group_id in number,
90 p_validate in boolean,
91 p_datetrack_mode in varchar2
92 )
93 is
94 -- this procedure will determine if suspended enrollment should be deleted
95 -- due to a certification being denied.
96 -- The person will still have the interm coverage that was assigned to them.
97 --
98 l_proc varchar2(72) := g_package||'.check_dnd_ctfns';
99 l_effective_start_date date;
100 l_effective_end_date date;
101 l_object_version_number number;
102 l_datetrack_mode varchar2(30) := hr_api.g_delete;
103 l_del_enrt varchar2(1) := 'N';
104 l_per_in_ler_id number;
105 --
106 -- Check if any required is denied.
107 --
108 cursor c_rqd_ctfn_prvdd is
109 select 'Y'
110 from ben_prtt_enrt_ctfn_prvdd_f pcs
111 where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
112 and pcs.business_group_id = p_business_group_id
113 and pcs.enrt_ctfn_rqd_flag = 'Y'
114 and p_effective_date
115 between pcs.effective_start_date and pcs.effective_end_date
116 and pcs.enrt_ctfn_dnd_dt is not null;
117 --
118 -- Check if all optionals are denied.
119 -- If there are any optional ctfns available and yet to be denied,
120 -- it will be the first record because of "order by" clause.
121 --
122 cursor c_opt_ctfn_prvdd is
123 select decode(pcs.enrt_ctfn_dnd_dt,null,'N','Y')
124 from ben_prtt_enrt_ctfn_prvdd_f pcs
125 where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
126 and pcs.business_group_id = p_business_group_id
127 and pcs.enrt_ctfn_rqd_flag = 'N'
128 and p_effective_date
129 between pcs.effective_start_date and pcs.effective_end_date
130 order by pcs.enrt_ctfn_dnd_dt desc;
131 --
132 -- mmogel - fixed bug 1146777 (this cursor's where clause used to say
133 -- where p_prtt_enrt_rslt_id = p_prtt_enrt_rslt_id)
134 --
135 cursor c_sspndd_rslt is
136 select pen.object_version_number,
137 pen.effective_start_date,pen.per_in_ler_id -- 2386000
138 from ben_prtt_enrt_rslt_f pen
139 where p_prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
140 and pen.business_group_id = p_business_group_id
141 and pen.prtt_enrt_rslt_stat_cd is null
142 and p_effective_date
143 between pen.effective_start_date and pen.effective_end_date
144 and pen.sspndd_flag = 'Y';
145 --
146 l_sspndd_rslt varchar2(1) := null;
147 --
148 begin
149 --
150 hr_utility.set_location('Entering:'|| l_proc, 10);
151 --
152 -- Check whether any required certification denied. If it is, then
153 -- the l_del_enrt flag is turned to 'Y'
154 --
155 open c_rqd_ctfn_prvdd;
156 fetch c_rqd_ctfn_prvdd into l_del_enrt;
157 close c_rqd_ctfn_prvdd;
158 --
159 -- If no required cert. has been denied, look whether all the optionals
160 -- have been denied. Here we need to look for all, as even if one optional
161 -- is completed then the optional conditions are satisfied.
162 --
163 if l_del_enrt = 'N' then
164 --
165 -- If no optional certs. available, then the flag remains intact.
166 -- If optional certs are availbe, then the 'order by' clause helps us
167 -- determine whether any optional cert. is still available.
168 --
169 open c_opt_ctfn_prvdd;
170 fetch c_opt_ctfn_prvdd into l_del_enrt;
171 close c_opt_ctfn_prvdd;
172 --
173 end if;
174 --
175 -- If the result is suspended, and the either one required cert. is denied
176 -- or all the optionals are denied, there is no way, the action item can
177 -- be completed, so delete the result.
178 --
179 if l_del_enrt = 'Y' then
180 --
181 open c_sspndd_rslt;
182 fetch c_sspndd_rslt into l_object_version_number,
183 l_effective_start_date,
184 l_per_in_ler_id ;
185 --
186 if c_sspndd_rslt%notfound then
187 null; -- result is not suspended, do nothing
188 else
189 --
190 if l_effective_start_date = p_effective_date then
191 l_datetrack_mode := hr_api.g_zap;
192 end if;
193 -- this will end date the enrollment, it's action items and it's
194 -- certifications (all the children).
195 hr_utility.set_location('Leaving:'|| l_proc, 40);
196 ben_prtt_enrt_result_api.delete_enrollment
197 (P_VALIDATE => p_validate
198 ,P_PRTT_ENRT_RSLT_ID => p_prtt_enrt_rslt_id
199 ,p_per_in_ler_id => l_per_in_ler_id -- 2386000
200 ,P_BUSINESS_GROUP_ID => p_business_group_id
201 ,P_EFFECTIVE_START_DATE => l_effective_start_date
202 ,P_EFFECTIVE_END_DATE => l_effective_end_date
203 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
204 ,P_EFFECTIVE_DATE => p_effective_date
205 ,P_DATETRACK_MODE => l_datetrack_mode
206 ,P_MULTI_ROW_VALIDATE => TRUE
207 ,p_source => 'bepcsapi'
208 );
209 hr_utility.set_location('Leaving:'|| l_proc, 45);
210 end if;
211 close c_sspndd_rslt;
212 end if;
213 --
214 hr_utility.set_location('Leaving:'|| l_proc, 50);
215 --
216 end check_dnd_ctfns;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |------------------------< check_prtt_futur_lf_evnt> ----------------------|
220 -- ----------------------------------------------------------------------------
221 --
222 procedure check_prtt_futur_lf_evnt
223 (p_prtt_enrt_actn_id in number,
224 p_effective_date in date) is
225 --
226 --
227 cursor c_future_pils( p_person_id number,
228 p_business_group_id number,
229 p_lf_evt_ocrd_dt date ) is
230 select 1
231 from ben_per_in_ler pil,
232 ben_ler_f ler
233 where pil.business_group_id = p_business_group_id
234 and pil.person_id = p_person_id
235 and pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt
236 and pil.ler_id = ler.ler_id
237 and p_effective_date
238 between ler.effective_start_date
239 and ler.effective_end_date
240 and ler.typ_cd not in ('IREC', 'SCHDU','ABS', 'COMP', 'GSP')
241 and pil.per_in_ler_stat_cd in ('STRTD','PROCD');
242
243 l_future_pils c_future_pils%rowtype;
244 --
245 cursor c_pil(p_PRTT_ENRT_ACTN_ID number) is
246 select pil1.lf_evt_ocrd_dt lf_evt_ocrd_dt,
247 pil1.person_id person_id,
248 pil1.business_group_id business_group_id
249 from ben_per_in_ler pil1, ben_prtt_enrt_actn_f actn
250 where pil1.per_in_ler_id = actn.per_in_ler_id
251 and actn.PRTT_ENRT_ACTN_ID = p_PRTT_ENRT_ACTN_ID
252 and p_effective_date
253 between actn.effective_start_date
254 and actn.effective_end_date;
255 l_pil c_pil%rowtype;
256 --
257 l_proc varchar2(72) := g_package||'.check_prtt_futur_lf_evnt';
258 begin
259 --
260 hr_utility.set_location('Entering:'|| l_proc, 50);
261 --
262 open c_pil(p_PRTT_ENRT_ACTN_ID);
263 fetch c_pil into l_pil;
264 close c_pil;
265 --
266 open c_future_pils( l_pil.person_id,l_pil.business_group_id,l_pil.lf_evt_ocrd_dt);
267 fetch c_future_pils into l_future_pils;
268 --
269 if(c_future_pils%found) then
270
271 close c_future_pils;
272 fnd_message.set_name('BEN', 'BEN_94037_FUTUR_EVT_EXISTS');
273 fnd_message.raise_error;
274
275 end if;
276 --
277 close c_future_pils;
278 --
279 hr_utility.set_location('Leaving:'|| l_proc, 50);
280 --
281 end check_prtt_futur_lf_evnt;
282
283 -- ----------------------------------------------------------------------------
284 -- |---------------------< create_PRTT_ENRT_CTFN_PRVDD >----------------------|
285 -- ----------------------------------------------------------------------------
286 --
287 procedure create_PRTT_ENRT_CTFN_PRVDD
288 (p_validate in boolean default false
289 ,p_prtt_enrt_ctfn_prvdd_id out nocopy number
290 ,p_effective_start_date out nocopy date
291 ,p_effective_end_date out nocopy date
292 ,p_enrt_ctfn_rqd_flag in varchar2 default 'N'
293 ,p_enrt_ctfn_typ_cd in varchar2 default null
294 ,p_enrt_ctfn_recd_dt in date default null
295 ,p_enrt_ctfn_dnd_dt in date default null
296 ,p_enrt_r_bnft_ctfn_cd in varchar2 default null
297 ,p_prtt_enrt_rslt_id in number default null
298 ,p_prtt_enrt_actn_id in number default null
299 ,p_business_group_id in number default null
300 ,p_pcs_attribute_category in varchar2 default null
301 ,p_pcs_attribute1 in varchar2 default null
302 ,p_pcs_attribute2 in varchar2 default null
303 ,p_pcs_attribute3 in varchar2 default null
304 ,p_pcs_attribute4 in varchar2 default null
305 ,p_pcs_attribute5 in varchar2 default null
306 ,p_pcs_attribute6 in varchar2 default null
307 ,p_pcs_attribute7 in varchar2 default null
308 ,p_pcs_attribute8 in varchar2 default null
309 ,p_pcs_attribute9 in varchar2 default null
310 ,p_pcs_attribute10 in varchar2 default null
311 ,p_pcs_attribute11 in varchar2 default null
312 ,p_pcs_attribute12 in varchar2 default null
313 ,p_pcs_attribute13 in varchar2 default null
314 ,p_pcs_attribute14 in varchar2 default null
315 ,p_pcs_attribute15 in varchar2 default null
316 ,p_pcs_attribute16 in varchar2 default null
317 ,p_pcs_attribute17 in varchar2 default null
318 ,p_pcs_attribute18 in varchar2 default null
319 ,p_pcs_attribute19 in varchar2 default null
320 ,p_pcs_attribute20 in varchar2 default null
321 ,p_pcs_attribute21 in varchar2 default null
322 ,p_pcs_attribute22 in varchar2 default null
323 ,p_pcs_attribute23 in varchar2 default null
324 ,p_pcs_attribute24 in varchar2 default null
325 ,p_pcs_attribute25 in varchar2 default null
326 ,p_pcs_attribute26 in varchar2 default null
327 ,p_pcs_attribute27 in varchar2 default null
328 ,p_pcs_attribute28 in varchar2 default null
329 ,p_pcs_attribute29 in varchar2 default null
330 ,p_pcs_attribute30 in varchar2 default null
331 ,p_object_version_number out nocopy number
332 ,p_effective_date in date
333 ) is
334 --
335 -- Declare cursors and local variables
336 --
337 l_prtt_enrt_ctfn_prvdd_id ben_prtt_enrt_ctfn_prvdd_f.prtt_enrt_ctfn_prvdd_id%TYPE;
338 l_effective_start_date ben_prtt_enrt_ctfn_prvdd_f.effective_start_date%TYPE;
339 l_effective_end_date ben_prtt_enrt_ctfn_prvdd_f.effective_end_date%TYPE;
340 l_proc varchar2(72) := g_package||'create_PRTT_ENRT_CTFN_PRVDD';
341 l_object_version_number ben_prtt_enrt_ctfn_prvdd_f.object_version_number%TYPE;
342 l_prtt_enrt_actn_id ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type;
343 --
344 begin
345 --
346 hr_utility.set_location('Entering:'|| l_proc, 10);
347 --
348 -- Initialize environment
349 --
350 if fnd_global.conc_request_id = -1 then
351 --
352 ben_env_object.init
353 (p_business_group_id => p_business_group_id,
354 p_effective_date => p_effective_date,
355 p_thread_id => null,
356 p_chunk_size => null,
357 p_threads => null,
358 p_max_errors => null,
359 p_benefit_action_id => null);
360 --
361 end if;
362 --
363 -- Issue a savepoint if operating in validation only mode
364 --
365 savepoint create_PRTT_ENRT_CTFN_PRVDD;
366 --
367 hr_utility.set_location(l_proc, 20);
368 --
369 if p_prtt_enrt_actn_id is null then
370 --
371 ben_enrollment_action_items.process_new_ctfn_action
372 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
373 ,p_actn_typ_cd => 'ENRTCTFN'
374 ,p_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
375 ,p_ctfn_recd_dt => p_enrt_ctfn_recd_dt
376 ,p_business_group_id => p_business_group_id
377 ,p_effective_date => p_effective_date
378 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id);
379 --
380 else
381 --
382 l_prtt_enrt_actn_id := p_prtt_enrt_actn_id;
383 --
384 end if;
385 --
386 -- Process Logic
387 --
388 begin
389 --
390 -- Start of API User Hook for the before hook of create_PRTT_ENRT_CTFN_PRVDD
391 --
392 ben_PRTT_ENRT_CTFN_PRVDD_bk1.create_PRTT_ENRT_CTFN_PRVDD_b
393 (
394 p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
395 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
396 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
397 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
398 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
399 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
400 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
401 ,p_business_group_id => p_business_group_id
402 ,p_pcs_attribute_category => p_pcs_attribute_category
403 ,p_pcs_attribute1 => p_pcs_attribute1
404 ,p_pcs_attribute2 => p_pcs_attribute2
405 ,p_pcs_attribute3 => p_pcs_attribute3
406 ,p_pcs_attribute4 => p_pcs_attribute4
407 ,p_pcs_attribute5 => p_pcs_attribute5
408 ,p_pcs_attribute6 => p_pcs_attribute6
409 ,p_pcs_attribute7 => p_pcs_attribute7
410 ,p_pcs_attribute8 => p_pcs_attribute8
411 ,p_pcs_attribute9 => p_pcs_attribute9
412 ,p_pcs_attribute10 => p_pcs_attribute10
413 ,p_pcs_attribute11 => p_pcs_attribute11
414 ,p_pcs_attribute12 => p_pcs_attribute12
415 ,p_pcs_attribute13 => p_pcs_attribute13
416 ,p_pcs_attribute14 => p_pcs_attribute14
417 ,p_pcs_attribute15 => p_pcs_attribute15
418 ,p_pcs_attribute16 => p_pcs_attribute16
419 ,p_pcs_attribute17 => p_pcs_attribute17
420 ,p_pcs_attribute18 => p_pcs_attribute18
421 ,p_pcs_attribute19 => p_pcs_attribute19
422 ,p_pcs_attribute20 => p_pcs_attribute20
423 ,p_pcs_attribute21 => p_pcs_attribute21
424 ,p_pcs_attribute22 => p_pcs_attribute22
425 ,p_pcs_attribute23 => p_pcs_attribute23
426 ,p_pcs_attribute24 => p_pcs_attribute24
427 ,p_pcs_attribute25 => p_pcs_attribute25
428 ,p_pcs_attribute26 => p_pcs_attribute26
429 ,p_pcs_attribute27 => p_pcs_attribute27
430 ,p_pcs_attribute28 => p_pcs_attribute28
431 ,p_pcs_attribute29 => p_pcs_attribute29
432 ,p_pcs_attribute30 => p_pcs_attribute30
433 ,p_effective_date => trunc(p_effective_date)
434 );
435 exception
436 when hr_api.cannot_find_prog_unit then
437 hr_api.cannot_find_prog_unit_error
438 (
439 p_module_name => 'CREATE_PRTT_ENRT_CTFN_PRVDD'
440 ,p_hook_type => 'BP'
441 );
442 --
443 -- End of API User Hook for the before hook of create_PRTT_ENRT_CTFN_PRVDD
444 --
445 end;
446 --
447 ben_pcs_ins.ins
448 (
449 p_prtt_enrt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id
450 ,p_effective_start_date => l_effective_start_date
451 ,p_effective_end_date => l_effective_end_date
452 ,p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
453 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
454 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
455 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
456 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
457 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
458 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
459 ,p_business_group_id => p_business_group_id
460 ,p_pcs_attribute_category => p_pcs_attribute_category
461 ,p_pcs_attribute1 => p_pcs_attribute1
462 ,p_pcs_attribute2 => p_pcs_attribute2
463 ,p_pcs_attribute3 => p_pcs_attribute3
464 ,p_pcs_attribute4 => p_pcs_attribute4
465 ,p_pcs_attribute5 => p_pcs_attribute5
466 ,p_pcs_attribute6 => p_pcs_attribute6
467 ,p_pcs_attribute7 => p_pcs_attribute7
468 ,p_pcs_attribute8 => p_pcs_attribute8
469 ,p_pcs_attribute9 => p_pcs_attribute9
470 ,p_pcs_attribute10 => p_pcs_attribute10
471 ,p_pcs_attribute11 => p_pcs_attribute11
472 ,p_pcs_attribute12 => p_pcs_attribute12
473 ,p_pcs_attribute13 => p_pcs_attribute13
474 ,p_pcs_attribute14 => p_pcs_attribute14
475 ,p_pcs_attribute15 => p_pcs_attribute15
476 ,p_pcs_attribute16 => p_pcs_attribute16
477 ,p_pcs_attribute17 => p_pcs_attribute17
478 ,p_pcs_attribute18 => p_pcs_attribute18
479 ,p_pcs_attribute19 => p_pcs_attribute19
480 ,p_pcs_attribute20 => p_pcs_attribute20
481 ,p_pcs_attribute21 => p_pcs_attribute21
482 ,p_pcs_attribute22 => p_pcs_attribute22
483 ,p_pcs_attribute23 => p_pcs_attribute23
484 ,p_pcs_attribute24 => p_pcs_attribute24
485 ,p_pcs_attribute25 => p_pcs_attribute25
486 ,p_pcs_attribute26 => p_pcs_attribute26
487 ,p_pcs_attribute27 => p_pcs_attribute27
488 ,p_pcs_attribute28 => p_pcs_attribute28
489 ,p_pcs_attribute29 => p_pcs_attribute29
490 ,p_pcs_attribute30 => p_pcs_attribute30
491 ,p_object_version_number => l_object_version_number
492 ,p_effective_date => trunc(p_effective_date)
493 );
494 --
495 begin
496 --
497 -- Start of API User Hook for the after hook of create_PRTT_ENRT_CTFN_PRVDD
498 --
499 ben_PRTT_ENRT_CTFN_PRVDD_bk1.create_PRTT_ENRT_CTFN_PRVDD_a
500 (
501 p_prtt_enrt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id
502 ,p_effective_start_date => l_effective_start_date
503 ,p_effective_end_date => l_effective_end_date
504 ,p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
505 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
506 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
507 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
508 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
509 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
510 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
511 ,p_business_group_id => p_business_group_id
512 ,p_pcs_attribute_category => p_pcs_attribute_category
513 ,p_pcs_attribute1 => p_pcs_attribute1
514 ,p_pcs_attribute2 => p_pcs_attribute2
515 ,p_pcs_attribute3 => p_pcs_attribute3
516 ,p_pcs_attribute4 => p_pcs_attribute4
517 ,p_pcs_attribute5 => p_pcs_attribute5
518 ,p_pcs_attribute6 => p_pcs_attribute6
519 ,p_pcs_attribute7 => p_pcs_attribute7
520 ,p_pcs_attribute8 => p_pcs_attribute8
521 ,p_pcs_attribute9 => p_pcs_attribute9
522 ,p_pcs_attribute10 => p_pcs_attribute10
523 ,p_pcs_attribute11 => p_pcs_attribute11
524 ,p_pcs_attribute12 => p_pcs_attribute12
525 ,p_pcs_attribute13 => p_pcs_attribute13
526 ,p_pcs_attribute14 => p_pcs_attribute14
527 ,p_pcs_attribute15 => p_pcs_attribute15
528 ,p_pcs_attribute16 => p_pcs_attribute16
529 ,p_pcs_attribute17 => p_pcs_attribute17
530 ,p_pcs_attribute18 => p_pcs_attribute18
531 ,p_pcs_attribute19 => p_pcs_attribute19
532 ,p_pcs_attribute20 => p_pcs_attribute20
533 ,p_pcs_attribute21 => p_pcs_attribute21
534 ,p_pcs_attribute22 => p_pcs_attribute22
535 ,p_pcs_attribute23 => p_pcs_attribute23
536 ,p_pcs_attribute24 => p_pcs_attribute24
537 ,p_pcs_attribute25 => p_pcs_attribute25
538 ,p_pcs_attribute26 => p_pcs_attribute26
539 ,p_pcs_attribute27 => p_pcs_attribute27
540 ,p_pcs_attribute28 => p_pcs_attribute28
541 ,p_pcs_attribute29 => p_pcs_attribute29
542 ,p_pcs_attribute30 => p_pcs_attribute30
543 ,p_object_version_number => l_object_version_number
544 ,p_effective_date => trunc(p_effective_date)
545 );
546 exception
547 when hr_api.cannot_find_prog_unit then
548 hr_api.cannot_find_prog_unit_error
549 (p_module_name => 'CREATE_PRTT_ENRT_CTFN_PRVDD'
550 ,p_hook_type => 'AP'
551 );
552 --
553 -- End of API User Hook for the after hook of create_PRTT_ENRT_CTFN_PRVDD
554 --
555 end;
556 --
557 hr_utility.set_location(l_proc, 60);
558 --
559 -- When in validation only mode raise the Validate_Enabled exception
560 --
561 if p_validate then
562 raise hr_api.validate_enabled;
563 end if;
564 --
565 -- Set all output arguments
566 --
567 p_prtt_enrt_ctfn_prvdd_id := l_prtt_enrt_ctfn_prvdd_id;
568 p_effective_start_date := l_effective_start_date;
569 p_effective_end_date := l_effective_end_date;
570 p_object_version_number := l_object_version_number;
571 --
572 hr_utility.set_location(' Leaving:'||l_proc, 70);
573 --
574 exception
575 --
576 when hr_api.validate_enabled then
577 --
578 -- As the Validate_Enabled exception has been raised
579 -- we must rollback to the savepoint
580 --
581 ROLLBACK TO create_PRTT_ENRT_CTFN_PRVDD;
582 --
583 -- Only set output warning arguments
584 -- (Any key or derived arguments must be set to null
585 -- when validation only mode is being used.)
586 --
587 p_prtt_enrt_ctfn_prvdd_id := null;
588 p_effective_start_date := null;
589 p_effective_end_date := null;
590 p_object_version_number := null;
591 hr_utility.set_location(' Leaving:'||l_proc, 80);
592 --
593 when others then
594 --
595 -- A validation or unexpected error has occured
596 --
597 ROLLBACK TO create_PRTT_ENRT_CTFN_PRVDD;
598 --
599 p_prtt_enrt_ctfn_prvdd_id := null;
600 p_effective_start_date := null;
601 p_effective_end_date := null;
602 p_object_version_number := null;
603 hr_utility.set_location(' Leaving:'||l_proc, 80);
604 --
605 raise;
606 --
607 end create_PRTT_ENRT_CTFN_PRVDD;
608 -- ----------------------------------------------------------------------------
609 -- |---------------------< update_PRTT_ENRT_CTFN_PRVDD >--- ------------------|
610 -- ----------------------------------------------------------------------------
611 --
612 procedure update_PRTT_ENRT_CTFN_PRVDD
613 (p_validate in boolean default false
614 ,p_prtt_enrt_ctfn_prvdd_id in number
615 ,p_effective_start_date out nocopy date
616 ,p_effective_end_date out nocopy date
617 ,p_enrt_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
618 ,p_enrt_ctfn_typ_cd in varchar2 default hr_api.g_varchar2
619 ,p_enrt_ctfn_recd_dt in date default hr_api.g_date
620 ,p_enrt_ctfn_dnd_dt in date default hr_api.g_date
621 ,p_enrt_r_bnft_ctfn_cd in varchar2 default hr_api.g_varchar2
622 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
623 ,p_prtt_enrt_actn_id in number default hr_api.g_number
624 ,p_business_group_id in number default hr_api.g_number
625 ,p_pcs_attribute_category in varchar2 default hr_api.g_varchar2
626 ,p_pcs_attribute1 in varchar2 default hr_api.g_varchar2
627 ,p_pcs_attribute2 in varchar2 default hr_api.g_varchar2
628 ,p_pcs_attribute3 in varchar2 default hr_api.g_varchar2
629 ,p_pcs_attribute4 in varchar2 default hr_api.g_varchar2
630 ,p_pcs_attribute5 in varchar2 default hr_api.g_varchar2
631 ,p_pcs_attribute6 in varchar2 default hr_api.g_varchar2
632 ,p_pcs_attribute7 in varchar2 default hr_api.g_varchar2
633 ,p_pcs_attribute8 in varchar2 default hr_api.g_varchar2
634 ,p_pcs_attribute9 in varchar2 default hr_api.g_varchar2
635 ,p_pcs_attribute10 in varchar2 default hr_api.g_varchar2
636 ,p_pcs_attribute11 in varchar2 default hr_api.g_varchar2
637 ,p_pcs_attribute12 in varchar2 default hr_api.g_varchar2
638 ,p_pcs_attribute13 in varchar2 default hr_api.g_varchar2
639 ,p_pcs_attribute14 in varchar2 default hr_api.g_varchar2
640 ,p_pcs_attribute15 in varchar2 default hr_api.g_varchar2
641 ,p_pcs_attribute16 in varchar2 default hr_api.g_varchar2
642 ,p_pcs_attribute17 in varchar2 default hr_api.g_varchar2
643 ,p_pcs_attribute18 in varchar2 default hr_api.g_varchar2
644 ,p_pcs_attribute19 in varchar2 default hr_api.g_varchar2
645 ,p_pcs_attribute20 in varchar2 default hr_api.g_varchar2
646 ,p_pcs_attribute21 in varchar2 default hr_api.g_varchar2
647 ,p_pcs_attribute22 in varchar2 default hr_api.g_varchar2
648 ,p_pcs_attribute23 in varchar2 default hr_api.g_varchar2
649 ,p_pcs_attribute24 in varchar2 default hr_api.g_varchar2
650 ,p_pcs_attribute25 in varchar2 default hr_api.g_varchar2
651 ,p_pcs_attribute26 in varchar2 default hr_api.g_varchar2
652 ,p_pcs_attribute27 in varchar2 default hr_api.g_varchar2
653 ,p_pcs_attribute28 in varchar2 default hr_api.g_varchar2
654 ,p_pcs_attribute29 in varchar2 default hr_api.g_varchar2
655 ,p_pcs_attribute30 in varchar2 default hr_api.g_varchar2
656 ,p_object_version_number in out nocopy number
657 ,p_effective_date in date
658 ,p_datetrack_mode in varchar2
659 ) is
660 --
661 -- Declare cursors and local variables
662 --
663 l_proc varchar2(72) := g_package||'update_PRTT_ENRT_CTFN_PRVDD';
664 l_object_version_number ben_prtt_enrt_ctfn_prvdd_f.object_version_number%TYPE;
665 l_effective_start_date ben_prtt_enrt_ctfn_prvdd_f.effective_start_date%TYPE;
666 l_effective_end_date ben_prtt_enrt_ctfn_prvdd_f.effective_end_date%TYPE;
667 --
668 begin
669 --
670 hr_utility.set_location('Entering:'|| l_proc, 10);
671 --
672 -- Initialize environment
673 --
674 if fnd_global.conc_request_id = -1 then
675 --
676 ben_env_object.init
677 (p_business_group_id => p_business_group_id,
678 p_effective_date => p_effective_date,
679 p_thread_id => null,
680 p_chunk_size => null,
681 p_threads => null,
682 p_max_errors => null,
683 p_benefit_action_id => null);
684 --
685 end if;
686 --
687 -- Issue a savepoint if operating in validation only mode
688 --
689 savepoint update_PRTT_ENRT_CTFN_PRVDD;
690
691 /* code moved to PLD
692 --
693 -- Before completing Enrollment certification should check for the future pil records
694 -- if they exist raise error else contine
695 --
696 check_prtt_futur_lf_evnt
697 (p_prtt_enrt_actn_id,
698 p_effective_date);
699 */
700 --
701 hr_utility.set_location(l_proc, 20);
702 --
703 -- Process Logic
704 --
705 l_object_version_number := p_object_version_number;
706 --
707 begin
708 --
709 -- Start of API User Hook for the before hook of update_PRTT_ENRT_CTFN_PRVDD
710 --
711 ben_PRTT_ENRT_CTFN_PRVDD_bk2.update_PRTT_ENRT_CTFN_PRVDD_b
712 (
713 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
714 ,p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
715 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
716 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
717 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
718 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
719 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
720 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
721 ,p_business_group_id => p_business_group_id
722 ,p_pcs_attribute_category => p_pcs_attribute_category
723 ,p_pcs_attribute1 => p_pcs_attribute1
724 ,p_pcs_attribute2 => p_pcs_attribute2
725 ,p_pcs_attribute3 => p_pcs_attribute3
726 ,p_pcs_attribute4 => p_pcs_attribute4
727 ,p_pcs_attribute5 => p_pcs_attribute5
728 ,p_pcs_attribute6 => p_pcs_attribute6
729 ,p_pcs_attribute7 => p_pcs_attribute7
730 ,p_pcs_attribute8 => p_pcs_attribute8
731 ,p_pcs_attribute9 => p_pcs_attribute9
732 ,p_pcs_attribute10 => p_pcs_attribute10
733 ,p_pcs_attribute11 => p_pcs_attribute11
734 ,p_pcs_attribute12 => p_pcs_attribute12
735 ,p_pcs_attribute13 => p_pcs_attribute13
736 ,p_pcs_attribute14 => p_pcs_attribute14
737 ,p_pcs_attribute15 => p_pcs_attribute15
738 ,p_pcs_attribute16 => p_pcs_attribute16
739 ,p_pcs_attribute17 => p_pcs_attribute17
740 ,p_pcs_attribute18 => p_pcs_attribute18
741 ,p_pcs_attribute19 => p_pcs_attribute19
742 ,p_pcs_attribute20 => p_pcs_attribute20
743 ,p_pcs_attribute21 => p_pcs_attribute21
744 ,p_pcs_attribute22 => p_pcs_attribute22
745 ,p_pcs_attribute23 => p_pcs_attribute23
746 ,p_pcs_attribute24 => p_pcs_attribute24
747 ,p_pcs_attribute25 => p_pcs_attribute25
748 ,p_pcs_attribute26 => p_pcs_attribute26
749 ,p_pcs_attribute27 => p_pcs_attribute27
750 ,p_pcs_attribute28 => p_pcs_attribute28
751 ,p_pcs_attribute29 => p_pcs_attribute29
752 ,p_pcs_attribute30 => p_pcs_attribute30
753 ,p_object_version_number => p_object_version_number
754 ,p_effective_date => trunc(p_effective_date)
755 ,p_datetrack_mode => p_datetrack_mode
756 );
757 exception
758 when hr_api.cannot_find_prog_unit then
759 hr_api.cannot_find_prog_unit_error
760 (p_module_name => 'UPDATE_PRTT_ENRT_CTFN_PRVDD'
761 ,p_hook_type => 'BP'
762 );
763 --
764 -- End of API User Hook for the before hook of update_PRTT_ENRT_CTFN_PRVDD
765 --
766 end;
767 --
768 ben_pcs_upd.upd
769 (
770 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
771 ,p_effective_start_date => l_effective_start_date
772 ,p_effective_end_date => l_effective_end_date
773 ,p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
774 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
775 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
776 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
777 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
778 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
779 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
780 ,p_business_group_id => p_business_group_id
781 ,p_pcs_attribute_category => p_pcs_attribute_category
782 ,p_pcs_attribute1 => p_pcs_attribute1
783 ,p_pcs_attribute2 => p_pcs_attribute2
784 ,p_pcs_attribute3 => p_pcs_attribute3
785 ,p_pcs_attribute4 => p_pcs_attribute4
786 ,p_pcs_attribute5 => p_pcs_attribute5
787 ,p_pcs_attribute6 => p_pcs_attribute6
788 ,p_pcs_attribute7 => p_pcs_attribute7
789 ,p_pcs_attribute8 => p_pcs_attribute8
790 ,p_pcs_attribute9 => p_pcs_attribute9
791 ,p_pcs_attribute10 => p_pcs_attribute10
792 ,p_pcs_attribute11 => p_pcs_attribute11
793 ,p_pcs_attribute12 => p_pcs_attribute12
794 ,p_pcs_attribute13 => p_pcs_attribute13
795 ,p_pcs_attribute14 => p_pcs_attribute14
796 ,p_pcs_attribute15 => p_pcs_attribute15
797 ,p_pcs_attribute16 => p_pcs_attribute16
798 ,p_pcs_attribute17 => p_pcs_attribute17
799 ,p_pcs_attribute18 => p_pcs_attribute18
800 ,p_pcs_attribute19 => p_pcs_attribute19
801 ,p_pcs_attribute20 => p_pcs_attribute20
802 ,p_pcs_attribute21 => p_pcs_attribute21
803 ,p_pcs_attribute22 => p_pcs_attribute22
804 ,p_pcs_attribute23 => p_pcs_attribute23
805 ,p_pcs_attribute24 => p_pcs_attribute24
806 ,p_pcs_attribute25 => p_pcs_attribute25
807 ,p_pcs_attribute26 => p_pcs_attribute26
808 ,p_pcs_attribute27 => p_pcs_attribute27
809 ,p_pcs_attribute28 => p_pcs_attribute28
810 ,p_pcs_attribute29 => p_pcs_attribute29
811 ,p_pcs_attribute30 => p_pcs_attribute30
812 ,p_object_version_number => l_object_version_number
813 ,p_effective_date => trunc(p_effective_date)
814 ,p_datetrack_mode => p_datetrack_mode
815 );
816 --
817 begin
818 --
819 -- Start of API User Hook for the after hook of update_PRTT_ENRT_CTFN_PRVDD
820 --
821 ben_PRTT_ENRT_CTFN_PRVDD_bk2.update_PRTT_ENRT_CTFN_PRVDD_a
822 (
823 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
824 ,p_effective_start_date => l_effective_start_date
825 ,p_effective_end_date => l_effective_end_date
826 ,p_enrt_ctfn_rqd_flag => p_enrt_ctfn_rqd_flag
827 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
828 ,p_enrt_ctfn_recd_dt => p_enrt_ctfn_recd_dt
829 ,p_enrt_ctfn_dnd_dt => p_enrt_ctfn_dnd_dt
830 ,p_enrt_r_bnft_ctfn_cd => p_enrt_r_bnft_ctfn_cd
831 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
832 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
833 ,p_business_group_id => p_business_group_id
834 ,p_pcs_attribute_category => p_pcs_attribute_category
835 ,p_pcs_attribute1 => p_pcs_attribute1
836 ,p_pcs_attribute2 => p_pcs_attribute2
837 ,p_pcs_attribute3 => p_pcs_attribute3
838 ,p_pcs_attribute4 => p_pcs_attribute4
839 ,p_pcs_attribute5 => p_pcs_attribute5
840 ,p_pcs_attribute6 => p_pcs_attribute6
841 ,p_pcs_attribute7 => p_pcs_attribute7
842 ,p_pcs_attribute8 => p_pcs_attribute8
843 ,p_pcs_attribute9 => p_pcs_attribute9
844 ,p_pcs_attribute10 => p_pcs_attribute10
845 ,p_pcs_attribute11 => p_pcs_attribute11
846 ,p_pcs_attribute12 => p_pcs_attribute12
847 ,p_pcs_attribute13 => p_pcs_attribute13
848 ,p_pcs_attribute14 => p_pcs_attribute14
849 ,p_pcs_attribute15 => p_pcs_attribute15
850 ,p_pcs_attribute16 => p_pcs_attribute16
851 ,p_pcs_attribute17 => p_pcs_attribute17
852 ,p_pcs_attribute18 => p_pcs_attribute18
853 ,p_pcs_attribute19 => p_pcs_attribute19
854 ,p_pcs_attribute20 => p_pcs_attribute20
855 ,p_pcs_attribute21 => p_pcs_attribute21
856 ,p_pcs_attribute22 => p_pcs_attribute22
857 ,p_pcs_attribute23 => p_pcs_attribute23
858 ,p_pcs_attribute24 => p_pcs_attribute24
859 ,p_pcs_attribute25 => p_pcs_attribute25
860 ,p_pcs_attribute26 => p_pcs_attribute26
861 ,p_pcs_attribute27 => p_pcs_attribute27
862 ,p_pcs_attribute28 => p_pcs_attribute28
863 ,p_pcs_attribute29 => p_pcs_attribute29
864 ,p_pcs_attribute30 => p_pcs_attribute30
865 ,p_object_version_number => l_object_version_number
866 ,p_effective_date => trunc(p_effective_date)
867 ,p_datetrack_mode => p_datetrack_mode
868 );
869 exception
870 when hr_api.cannot_find_prog_unit then
871 hr_api.cannot_find_prog_unit_error
872 (p_module_name => 'UPDATE_PRTT_ENRT_CTFN_PRVDD'
873 ,p_hook_type => 'AP'
874 );
875 --
876 -- End of API User Hook for the after hook of update_PRTT_ENRT_CTFN_PRVDD
877 --
878 end;
879 --
880 hr_utility.set_location(l_proc, 60);
881 --
882 check_prtt_ctfn(p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
883 p_effective_date => p_effective_date,
884 p_business_group_id => p_business_group_id,
885 p_datetrack_mode => p_datetrack_mode);
886 --
887 hr_utility.set_location(' Leaving:'||l_proc, 62);
888 --
889 if p_enrt_ctfn_dnd_dt IS NOT NULL then
890 check_dnd_ctfns(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
891 p_effective_date => p_effective_date,
892 p_business_group_id => p_business_group_id,
893 p_validate => p_validate,
894 p_datetrack_mode => p_datetrack_mode);
895 end if;
896 --
897 hr_utility.set_location(' Leaving:'||l_proc, 64);
898 --
899 -- When in validation only mode raise the Validate_Enabled exception
900 --
901 if p_validate then
902 raise hr_api.validate_enabled;
903 end if;
904 --
905 -- Set all output arguments
906 --
907 p_object_version_number := l_object_version_number;
908 p_effective_start_date := l_effective_start_date;
909 p_effective_end_date := l_effective_end_date;
910
911 --
912 hr_utility.set_location(' Leaving:'||l_proc, 70);
913 --
914 exception
915 --
916 when hr_api.validate_enabled then
917 --
918 -- As the Validate_Enabled exception has been raised
919 -- we must rollback to the savepoint
920 --
921 ROLLBACK TO update_PRTT_ENRT_CTFN_PRVDD;
922 --
923 -- Only set output warning arguments
924 -- (Any key or derived arguments must be set to null
925 -- when validation only mode is being used.)
926 --
927 p_effective_start_date := null;
928 p_effective_end_date := null;
929 hr_utility.set_location(' Leaving:'||l_proc, 80);
930 --
931 when others then
932 --
933 -- A validation or unexpected error has occured
934 --
935 ROLLBACK TO update_PRTT_ENRT_CTFN_PRVDD;
936 --
937 p_effective_start_date := null;
938 p_effective_end_date := null;
939 p_object_version_number := l_object_version_number;
940 hr_utility.set_location(' Leaving:'||l_proc, 80);
941 --
942 raise;
943 --
944 end update_PRTT_ENRT_CTFN_PRVDD;
945 --
946 -- ----------------------------------------------------------------------------
947 -- |---------------------< delete_PRTT_ENRT_CTFN_PRVDD >----------------------|
948 -- ----------------------------------------------------------------------------
949 --
950 procedure delete_PRTT_ENRT_CTFN_PRVDD
951 (p_validate in boolean default false
952 ,p_prtt_enrt_ctfn_prvdd_id in number
953 ,p_effective_start_date out nocopy date
954 ,p_effective_end_date out nocopy date
955 ,p_object_version_number in out nocopy number
956 ,p_effective_date in date
957 ,p_datetrack_mode in varchar2
958 ,p_check_actions in varchar2 default 'Y'
959 ) is
960 --
961 -- Declare cursors and local variables
962 --
963 l_proc varchar2(72) := g_package||'delete_PRTT_ENRT_CTFN_PRVDD';
964 l_object_version_number ben_prtt_enrt_ctfn_prvdd_f.object_version_number%TYPE;
965 l_effective_start_date ben_prtt_enrt_ctfn_prvdd_f.effective_start_date%TYPE;
966 l_effective_end_date ben_prtt_enrt_ctfn_prvdd_f.effective_end_date%TYPE;
967 --
968 l_prtt_enrt_actn_id ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type := null;
969 l_prtt_enrt_rslt_id number(15);
970 l_rslt_object_version_number number(15);
971 l_business_group_id number(15);
972 l_exist varchar2(1) := 'N';
973 l1_object_version_number ben_prtt_enrt_actn_f.object_version_number%TYPE;
974 l1_effective_start_date ben_prtt_enrt_actn_f.effective_start_date%TYPE;
975 l1_effective_end_date ben_prtt_enrt_actn_f.effective_end_date%TYPE;
976 l_env_rec ben_env_object.g_global_env_rec_type;
977 --
978 cursor get_actn_c is
979 select prtt_enrt_actn_id,
980 business_group_id
981 from ben_prtt_enrt_ctfn_prvdd_f
982 where prtt_enrt_ctfn_prvdd_id = p_prtt_enrt_ctfn_prvdd_id
983 and p_effective_date between effective_start_date
984 and effective_end_date;
985 --
986 cursor more_ctfn_c is
987 select 'Y'
988 from ben_prtt_enrt_ctfn_prvdd_f
989 where prtt_enrt_actn_id = l_prtt_enrt_actn_id
990 and p_effective_date between effective_start_date
991 and effective_end_date;
992 --
993 cursor actn_info_c is
994 select pea.object_version_number,
995 pea.prtt_enrt_rslt_id,
996 pen.object_version_number
997 from ben_prtt_enrt_actn_f pea,
998 ben_prtt_enrt_rslt_f pen
999 where pea.prtt_enrt_actn_id = l_prtt_enrt_actn_id
1000 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1001 and pen.prtt_enrt_rslt_stat_cd is null
1002 and p_effective_date between pea.effective_start_date
1003 and pea.effective_end_date
1004 and p_effective_date between pen.effective_start_date
1005 and pen.effective_end_date;
1006 --
1007 begin
1008 --
1009 hr_utility.set_location('Entering:'|| l_proc, 10);
1010 --
1011 -- Get action item id and the business group id.
1012 --
1013 open get_actn_c;
1014 fetch get_actn_c into l_prtt_enrt_actn_id,
1015 l_business_group_id;
1016 close get_actn_c;
1017 --
1018 -- Initialize environment
1019 --
1020 if fnd_global.conc_request_id = -1 then
1021 --5460912
1022 ben_env_object.get(p_rec => l_env_rec);
1023 if l_env_rec.business_group_id is null then
1024 --
1025 ben_env_object.init
1026 (p_business_group_id => l_business_group_id,
1027 p_effective_date => p_effective_date,
1028 p_thread_id => null,
1029 p_chunk_size => null,
1030 p_threads => null,
1031 p_max_errors => null,
1032 p_benefit_action_id => null);
1033 --
1034 end if;
1035 end if;
1036 --
1037 -- Issue a savepoint if operating in validation only mode
1038 --
1039 savepoint delete_PRTT_ENRT_CTFN_PRVDD;
1040 /* code moved to PLD
1041 --
1042 -- Before completing Enrollment certification should check for the future pil records
1043 -- if they exist raise error else contine
1044 --
1045 check_prtt_futur_lf_evnt
1046 (l_prtt_enrt_actn_id,
1047 p_effective_date);
1048 */
1049 --
1050 hr_utility.set_location(l_proc, 20);
1051 --
1052 -- Process Logic
1053 --
1054 l_object_version_number := p_object_version_number;
1055 --
1056 begin
1057 --
1058 -- Start of API User Hook for the before hook of delete_PRTT_ENRT_CTFN_PRVDD
1059 --
1060 ben_PRTT_ENRT_CTFN_PRVDD_bk3.delete_PRTT_ENRT_CTFN_PRVDD_b
1061 (p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1062 ,p_object_version_number => p_object_version_number
1063 ,p_effective_date => trunc(p_effective_date)
1064 ,p_datetrack_mode => p_datetrack_mode );
1065 exception
1066 when hr_api.cannot_find_prog_unit then
1067 hr_api.cannot_find_prog_unit_error
1068 (p_module_name => 'DELETE_PRTT_ENRT_CTFN_PRVDD'
1069 ,p_hook_type => 'BP'
1070 );
1071 --
1072 -- End of API User Hook for the before hook of delete_PRTT_ENRT_CTFN_PRVDD
1073 --
1074 end;
1075 --
1076 ben_pcs_del.del
1077 (
1078 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1079 ,p_effective_start_date => l_effective_start_date
1080 ,p_effective_end_date => l_effective_end_date
1081 ,p_object_version_number => l_object_version_number
1082 ,p_effective_date => p_effective_date
1083 ,p_datetrack_mode => p_datetrack_mode
1084 );
1085 --
1086 --
1087 --
1088 -- If there are no remaining certifications,
1089 -- delete corresponding action item.
1090 --
1091 if p_check_actions = 'Y' then
1092 --
1093 open more_ctfn_c;
1094 fetch more_ctfn_c into l_exist;
1095 close more_ctfn_c;
1096 --
1097 if l_exist = 'N' then
1098 --
1099 open actn_info_c;
1100 fetch actn_info_c into l1_object_version_number,
1101 l_prtt_enrt_rslt_id,
1102 l_rslt_object_version_number;
1103 --
1104 if actn_info_c%FOUND then
1105 --
1106 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1107 (p_validate => p_validate
1108 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
1109 ,p_business_group_id => l_business_group_id
1110 ,p_effective_date => p_effective_date
1111 ,p_datetrack_mode => p_datetrack_mode
1112 ,p_object_version_number => l1_object_version_number
1113 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1114 ,p_rslt_object_version_number => l_rslt_object_version_number
1115 ,p_effective_start_date => l1_effective_start_date
1116 ,p_effective_end_date => l1_effective_end_date
1117 );
1118 --
1119 end if;
1120 --
1121 close actn_info_c;
1122 --
1123 else
1124 --
1125 -- Since a certification was deleted, we may be able to complete
1126 -- action item.
1127 --
1128 check_prtt_ctfn(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
1129 p_effective_date => p_effective_date,
1130 p_business_group_id => l_business_group_id,
1131 p_datetrack_mode => p_datetrack_mode);
1132 --
1133 end if; -- l_exist
1134 --
1135 end if; -- check_actions
1136
1137 begin
1138 --
1139 -- Start of API User Hook for the after hook of delete_PRTT_ENRT_CTFN_PRVDD
1140 --
1141 ben_PRTT_ENRT_CTFN_PRVDD_bk3.delete_PRTT_ENRT_CTFN_PRVDD_a
1142 (
1143 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1144 ,p_effective_start_date => l_effective_start_date
1145 ,p_effective_end_date => l_effective_end_date
1146 ,p_object_version_number => l_object_version_number
1147 ,p_effective_date => trunc(p_effective_date)
1148 ,p_datetrack_mode => p_datetrack_mode
1149 );
1150 exception
1151 when hr_api.cannot_find_prog_unit then
1152 hr_api.cannot_find_prog_unit_error
1153 (p_module_name => 'DELETE_PRTT_ENRT_CTFN_PRVDD'
1154 ,p_hook_type => 'AP'
1155 );
1156 --
1157 -- End of API User Hook for the after hook of delete_PRTT_ENRT_CTFN_PRVDD
1158 --
1159 end;
1160 --
1161 hr_utility.set_location(l_proc, 60);
1162 --
1163 -- When in validation only mode raise the Validate_Enabled exception
1164 --
1165 if p_validate then
1166 raise hr_api.validate_enabled;
1167 end if;
1168 --
1169 hr_utility.set_location(' Leaving:'||l_proc, 70);
1170 --
1171 exception
1172 --
1173 when hr_api.validate_enabled then
1174 --
1175 -- As the Validate_Enabled exception has been raised
1176 -- we must rollback to the savepoint
1177 --
1178 ROLLBACK TO delete_PRTT_ENRT_CTFN_PRVDD;
1179 --
1180 -- Only set output warning arguments
1181 -- (Any key or derived arguments must be set to null
1182 -- when validation only mode is being used.)
1183 --
1184 --
1185 when others then
1186 --
1187 -- A validation or unexpected error has occured
1188 --
1189 ROLLBACK TO delete_PRTT_ENRT_CTFN_PRVDD;
1190 --
1191 p_effective_start_date := null;
1192 p_effective_end_date := null;
1193 p_object_version_number := l_object_version_number;
1194 hr_utility.set_location(' Leaving:'||l_proc, 80);
1195 raise;
1196 --
1197 end delete_PRTT_ENRT_CTFN_PRVDD;
1198 --
1199 -- ----------------------------------------------------------------------------
1200 -- |-------------------------------< lck >------------------------------------|
1201 -- ----------------------------------------------------------------------------
1202 --
1203 procedure lck
1204 (
1205 p_prtt_enrt_ctfn_prvdd_id in number
1206 ,p_object_version_number in number
1207 ,p_effective_date in date
1208 ,p_datetrack_mode in varchar2
1209 ,p_validation_start_date out nocopy date
1210 ,p_validation_end_date out nocopy date
1211 ) is
1212 --
1213 --
1214 -- Declare cursors and local variables
1215 --
1216 l_proc varchar2(72) := g_package||'lck';
1217 l_validation_start_date date;
1218 l_validation_end_date date;
1219 --
1220 begin
1221 --
1222 hr_utility.set_location('Entering:'|| l_proc, 10);
1223 --
1224 ben_pcs_shd.lck
1225 (
1226 p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1227 ,p_validation_start_date => l_validation_start_date
1228 ,p_validation_end_date => l_validation_end_date
1229 ,p_object_version_number => p_object_version_number
1230 ,p_effective_date => p_effective_date
1231 ,p_datetrack_mode => p_datetrack_mode
1232 );
1233 --
1234 hr_utility.set_location(' Leaving:'||l_proc, 70);
1235 --
1236 end lck;
1237 --
1238 end ben_PRTT_ENRT_CTFN_PRVDD_api;