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