[Home] [Help]
PACKAGE BODY: APPS.BEN_PL_BNF_CTFN_PRVDD_API
Source
1 Package Body ben_PL_BNF_CTFN_PRVDD_api as
2 /* $Header: bepbcapi.pkb 120.1.12010000.2 2008/08/05 15:03:15 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_PL_BNF_CTFN_PRVDD_api.';
7 --
8 procedure check_bnf_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 bnf_c is
17 select pen.prtt_enrt_rslt_id,
18 plb.bnf_person_id,
19 plb.organization_id, -- Bug 5156111
20 plb.pl_bnf_id,
21 pea.prtt_enrt_actn_id,
22 pea.actn_typ_id,
23 pea.cmpltd_dt,
24 pen.object_version_number rslt_ovn,
25 pea.object_version_number
26 from ben_prtt_enrt_rslt_f pen,
27 ben_prtt_enrt_actn_f pea,
28 ben_pl_bnf_f plb
29 where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
30 and pea.pl_bnf_id = plb.pl_bnf_id
31 and plb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
32 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
33 and pen.prtt_enrt_rslt_stat_cd is null
34 and pea.business_group_id = p_business_group_id
35 and p_effective_date between pea.effective_start_date
36 and pea.effective_end_date
37 and plb.business_group_id = p_business_group_id
38 and p_effective_date between plb.effective_start_date
39 and plb.effective_end_date
40 and pen.business_group_id = p_business_group_id
41 and p_effective_date between pen.effective_start_date
42 and pen.effective_end_date;
43 --
44 l_bnf bnf_c%rowtype;
45 --
46 l_proc varchar2(80) := g_package||'check_bnf_ctfn';
47 --
48 begin
49 --
50 hr_utility.set_location('Entering '||l_proc, 10);
51 --
52 open bnf_c;
53 fetch bnf_c into l_bnf;
54 close bnf_c;
55 --
56 if l_bnf.prtt_enrt_rslt_id is not null and
57 (l_bnf.bnf_person_id is not null or l_bnf.organization_id is not null) then -- Bug 5156111
58 --
59 --
60 l_all_prvdd := ben_enrollment_action_items.check_bnf_ctfn
61 (p_prtt_enrt_actn_id => l_bnf.prtt_enrt_actn_id
62 ,p_pl_bnf_id => l_bnf.pl_bnf_id
63 ,p_effective_date => p_effective_date);
64 --
65 ben_enrollment_action_items.process_action_item
66 (p_prtt_enrt_actn_id => l_bnf.prtt_enrt_actn_id
67 ,p_actn_typ_id => l_bnf.actn_typ_id
68 ,p_cmpltd_dt => l_bnf.cmpltd_dt
69 ,p_object_version_number => l_bnf.object_version_number
70 ,p_effective_date => p_effective_date
71 ,p_rqd_data_found => l_all_prvdd
72 ,p_prtt_enrt_rslt_id => l_bnf.prtt_enrt_rslt_id
73 ,p_pl_bnf_id => l_bnf.pl_bnf_id
74 ,p_rqd_flag => 'Y'
75 ,p_post_rslt_flag => 'N'
76 ,p_business_group_id => p_business_group_id
77 ,p_datetrack_mode => p_datetrack_mode
78 ,p_rslt_object_version_number=> l_bnf.rslt_ovn);
79 --
80 end if;
81 --
82 hr_utility.set_location('Leaving '||l_proc, 10);
83 --
84 end check_bnf_ctfn;
85 --
86 -- ----------------------------------------------------------------------------
87 -- |------------------------< create_PL_BNF_CTFN_PRVDD >----------------------|
88 -- ----------------------------------------------------------------------------
89 --
90 procedure create_PL_BNF_CTFN_PRVDD
91 (p_validate in boolean default false
92 ,p_pl_bnf_ctfn_prvdd_id out nocopy number
93 ,p_effective_start_date out nocopy date
94 ,p_effective_end_date out nocopy date
95 ,p_bnf_ctfn_typ_cd in varchar2 default null
96 ,p_bnf_ctfn_recd_dt in date default null
97 ,p_bnf_ctfn_rqd_flag in varchar2 default 'N'
98 ,p_pl_bnf_id in number default null
99 ,p_prtt_enrt_actn_id in number default null
100 ,p_business_group_id in number default null
101 ,p_pbc_attribute_category in varchar2 default null
102 ,p_pbc_attribute1 in varchar2 default null
103 ,p_pbc_attribute2 in varchar2 default null
104 ,p_pbc_attribute3 in varchar2 default null
105 ,p_pbc_attribute4 in varchar2 default null
106 ,p_pbc_attribute5 in varchar2 default null
107 ,p_pbc_attribute6 in varchar2 default null
108 ,p_pbc_attribute7 in varchar2 default null
109 ,p_pbc_attribute8 in varchar2 default null
110 ,p_pbc_attribute9 in varchar2 default null
111 ,p_pbc_attribute10 in varchar2 default null
112 ,p_pbc_attribute11 in varchar2 default null
113 ,p_pbc_attribute12 in varchar2 default null
114 ,p_pbc_attribute13 in varchar2 default null
115 ,p_pbc_attribute14 in varchar2 default null
116 ,p_pbc_attribute15 in varchar2 default null
117 ,p_pbc_attribute16 in varchar2 default null
118 ,p_pbc_attribute17 in varchar2 default null
119 ,p_pbc_attribute18 in varchar2 default null
120 ,p_pbc_attribute19 in varchar2 default null
121 ,p_pbc_attribute20 in varchar2 default null
122 ,p_pbc_attribute21 in varchar2 default null
123 ,p_pbc_attribute22 in varchar2 default null
124 ,p_pbc_attribute23 in varchar2 default null
125 ,p_pbc_attribute24 in varchar2 default null
126 ,p_pbc_attribute25 in varchar2 default null
127 ,p_pbc_attribute26 in varchar2 default null
128 ,p_pbc_attribute27 in varchar2 default null
129 ,p_pbc_attribute28 in varchar2 default null
130 ,p_pbc_attribute29 in varchar2 default null
131 ,p_pbc_attribute30 in varchar2 default null
132 ,p_request_id in number default null
133 ,p_program_application_id in number default null
134 ,p_program_id in number default null
135 ,p_program_update_date in date default null
136 ,p_object_version_number out nocopy number
137 ,p_effective_date in date
138 ) is
139 --
140 -- Declare cursors and local variables
141 --
142 l_pl_bnf_ctfn_prvdd_id ben_pl_bnf_ctfn_prvdd_f.pl_bnf_ctfn_prvdd_id%TYPE;
143 l_effective_start_date ben_pl_bnf_ctfn_prvdd_f.effective_start_date%TYPE;
144 l_effective_end_date ben_pl_bnf_ctfn_prvdd_f.effective_end_date%TYPE;
145 l_proc varchar2(72) := g_package||'create_PL_BNF_CTFN_PRVDD';
146 l_object_version_number ben_pl_bnf_ctfn_prvdd_f.object_version_number%TYPE;
147 l_prtt_enrt_actn_id ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type;
148 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
149 l_actn_typ_cd varchar2(30);
150 --
151 cursor c_pbn is
152 select pbn.prtt_enrt_rslt_id
153 from ben_pl_bnf_f pbn
154 where pbn.pl_bnf_id = p_pl_bnf_id
155 and pbn.business_group_id = p_business_group_id
156 and p_effective_date between
157 pbn.effective_start_date and pbn.effective_end_date;
158 --
159 begin
160 --
161 hr_utility.set_location('Entering:'|| l_proc, 10);
162 --
163 -- Initialize environment
164 --
165 if fnd_global.conc_request_id = -1 then
166 --
167 ben_env_object.init
168 (p_business_group_id => p_business_group_id,
169 p_effective_date => p_effective_date,
170 p_thread_id => null,
171 p_chunk_size => null,
172 p_threads => null,
173 p_max_errors => null,
174 p_benefit_action_id => null);
175 --
176 end if;
177 --
178 -- Issue a savepoint if operating in validation only mode
179 --
180 savepoint create_PL_BNF_CTFN_PRVDD;
181 --
182 hr_utility.set_location(l_proc, 20);
183 --
184 if p_prtt_enrt_actn_id is null then
185 --
186 open c_pbn;
187 fetch c_pbn into l_prtt_enrt_rslt_id;
188 close c_pbn;
189 --
190 if p_bnf_ctfn_typ_cd = 'NSC' then
191 --
192 l_actn_typ_cd := 'BNFSCCTFN';
193 --
194 else
195 --
196 l_actn_typ_cd := 'BNFCTFN';
197 --
198 end if;
199 --
200 ben_enrollment_action_items.process_new_ctfn_action
201 (p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
202 ,p_pl_bnf_id => p_pl_bnf_id
203 ,p_actn_typ_cd => l_actn_typ_cd
204 ,p_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
205 ,p_ctfn_recd_dt => p_bnf_ctfn_recd_dt
206 ,p_business_group_id => p_business_group_id
207 ,p_effective_date => p_effective_date
208 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id);
209 --
210 else
211 --
212 l_prtt_enrt_actn_id := p_prtt_enrt_actn_id;
213 --
214 end if;
215 --
216 -- Process Logic
217 --
218 begin
219 --
220 -- Start of API User Hook for the before hook of create_PL_BNF_CTFN_PRVDD
221 --
222 ben_PL_BNF_CTFN_PRVDD_bk1.create_PL_BNF_CTFN_PRVDD_b
223 (
224 p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
225 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
226 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
227 ,p_pl_bnf_id => p_pl_bnf_id
228 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
229 ,p_business_group_id => p_business_group_id
230 ,p_pbc_attribute_category => p_pbc_attribute_category
231 ,p_pbc_attribute1 => p_pbc_attribute1
232 ,p_pbc_attribute2 => p_pbc_attribute2
233 ,p_pbc_attribute3 => p_pbc_attribute3
234 ,p_pbc_attribute4 => p_pbc_attribute4
235 ,p_pbc_attribute5 => p_pbc_attribute5
236 ,p_pbc_attribute6 => p_pbc_attribute6
237 ,p_pbc_attribute7 => p_pbc_attribute7
238 ,p_pbc_attribute8 => p_pbc_attribute8
239 ,p_pbc_attribute9 => p_pbc_attribute9
240 ,p_pbc_attribute10 => p_pbc_attribute10
241 ,p_pbc_attribute11 => p_pbc_attribute11
242 ,p_pbc_attribute12 => p_pbc_attribute12
243 ,p_pbc_attribute13 => p_pbc_attribute13
244 ,p_pbc_attribute14 => p_pbc_attribute14
245 ,p_pbc_attribute15 => p_pbc_attribute15
246 ,p_pbc_attribute16 => p_pbc_attribute16
247 ,p_pbc_attribute17 => p_pbc_attribute17
248 ,p_pbc_attribute18 => p_pbc_attribute18
249 ,p_pbc_attribute19 => p_pbc_attribute19
250 ,p_pbc_attribute20 => p_pbc_attribute20
251 ,p_pbc_attribute21 => p_pbc_attribute21
252 ,p_pbc_attribute22 => p_pbc_attribute22
253 ,p_pbc_attribute23 => p_pbc_attribute23
254 ,p_pbc_attribute24 => p_pbc_attribute24
255 ,p_pbc_attribute25 => p_pbc_attribute25
256 ,p_pbc_attribute26 => p_pbc_attribute26
257 ,p_pbc_attribute27 => p_pbc_attribute27
258 ,p_pbc_attribute28 => p_pbc_attribute28
259 ,p_pbc_attribute29 => p_pbc_attribute29
260 ,p_pbc_attribute30 => p_pbc_attribute30
261 ,p_request_id => p_request_id
262 ,p_program_application_id => p_program_application_id
263 ,p_program_id => p_program_id
264 ,p_program_update_date => p_program_update_date
265 ,p_effective_date => trunc(p_effective_date)
266 );
267 exception
268 when hr_api.cannot_find_prog_unit then
269 hr_api.cannot_find_prog_unit_error
270 (
271 p_module_name => 'CREATE_PL_BNF_CTFN_PRVDD'
272 ,p_hook_type => 'BP'
273 );
274 --
275 -- End of API User Hook for the before hook of create_PL_BNF_CTFN_PRVDD
276 --
277 end;
278 --
279 ben_pbc_ins.ins
280 (
281 p_pl_bnf_ctfn_prvdd_id => l_pl_bnf_ctfn_prvdd_id
282 ,p_effective_start_date => l_effective_start_date
283 ,p_effective_end_date => l_effective_end_date
284 ,p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
285 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
286 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
287 ,p_pl_bnf_id => p_pl_bnf_id
288 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
289 ,p_business_group_id => p_business_group_id
290 ,p_pbc_attribute_category => p_pbc_attribute_category
291 ,p_pbc_attribute1 => p_pbc_attribute1
292 ,p_pbc_attribute2 => p_pbc_attribute2
293 ,p_pbc_attribute3 => p_pbc_attribute3
294 ,p_pbc_attribute4 => p_pbc_attribute4
295 ,p_pbc_attribute5 => p_pbc_attribute5
296 ,p_pbc_attribute6 => p_pbc_attribute6
297 ,p_pbc_attribute7 => p_pbc_attribute7
298 ,p_pbc_attribute8 => p_pbc_attribute8
299 ,p_pbc_attribute9 => p_pbc_attribute9
300 ,p_pbc_attribute10 => p_pbc_attribute10
301 ,p_pbc_attribute11 => p_pbc_attribute11
302 ,p_pbc_attribute12 => p_pbc_attribute12
303 ,p_pbc_attribute13 => p_pbc_attribute13
304 ,p_pbc_attribute14 => p_pbc_attribute14
305 ,p_pbc_attribute15 => p_pbc_attribute15
306 ,p_pbc_attribute16 => p_pbc_attribute16
307 ,p_pbc_attribute17 => p_pbc_attribute17
308 ,p_pbc_attribute18 => p_pbc_attribute18
309 ,p_pbc_attribute19 => p_pbc_attribute19
310 ,p_pbc_attribute20 => p_pbc_attribute20
311 ,p_pbc_attribute21 => p_pbc_attribute21
312 ,p_pbc_attribute22 => p_pbc_attribute22
313 ,p_pbc_attribute23 => p_pbc_attribute23
314 ,p_pbc_attribute24 => p_pbc_attribute24
315 ,p_pbc_attribute25 => p_pbc_attribute25
316 ,p_pbc_attribute26 => p_pbc_attribute26
317 ,p_pbc_attribute27 => p_pbc_attribute27
318 ,p_pbc_attribute28 => p_pbc_attribute28
319 ,p_pbc_attribute29 => p_pbc_attribute29
320 ,p_pbc_attribute30 => p_pbc_attribute30
321 ,p_request_id => p_request_id
322 ,p_program_application_id => p_program_application_id
323 ,p_program_id => p_program_id
324 ,p_program_update_date => p_program_update_date
325 ,p_object_version_number => l_object_version_number
326 ,p_effective_date => trunc(p_effective_date)
327 );
328 --
329 begin
330 --
331 -- Start of API User Hook for the after hook of create_PL_BNF_CTFN_PRVDD
332 --
333 ben_PL_BNF_CTFN_PRVDD_bk1.create_PL_BNF_CTFN_PRVDD_a
334 (
335 p_pl_bnf_ctfn_prvdd_id => l_pl_bnf_ctfn_prvdd_id
336 ,p_effective_start_date => l_effective_start_date
337 ,p_effective_end_date => l_effective_end_date
338 ,p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
339 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
340 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
341 ,p_pl_bnf_id => p_pl_bnf_id
342 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
343 ,p_business_group_id => p_business_group_id
344 ,p_pbc_attribute_category => p_pbc_attribute_category
345 ,p_pbc_attribute1 => p_pbc_attribute1
346 ,p_pbc_attribute2 => p_pbc_attribute2
347 ,p_pbc_attribute3 => p_pbc_attribute3
348 ,p_pbc_attribute4 => p_pbc_attribute4
349 ,p_pbc_attribute5 => p_pbc_attribute5
350 ,p_pbc_attribute6 => p_pbc_attribute6
351 ,p_pbc_attribute7 => p_pbc_attribute7
352 ,p_pbc_attribute8 => p_pbc_attribute8
353 ,p_pbc_attribute9 => p_pbc_attribute9
354 ,p_pbc_attribute10 => p_pbc_attribute10
355 ,p_pbc_attribute11 => p_pbc_attribute11
356 ,p_pbc_attribute12 => p_pbc_attribute12
357 ,p_pbc_attribute13 => p_pbc_attribute13
358 ,p_pbc_attribute14 => p_pbc_attribute14
359 ,p_pbc_attribute15 => p_pbc_attribute15
360 ,p_pbc_attribute16 => p_pbc_attribute16
361 ,p_pbc_attribute17 => p_pbc_attribute17
362 ,p_pbc_attribute18 => p_pbc_attribute18
363 ,p_pbc_attribute19 => p_pbc_attribute19
364 ,p_pbc_attribute20 => p_pbc_attribute20
365 ,p_pbc_attribute21 => p_pbc_attribute21
366 ,p_pbc_attribute22 => p_pbc_attribute22
367 ,p_pbc_attribute23 => p_pbc_attribute23
368 ,p_pbc_attribute24 => p_pbc_attribute24
369 ,p_pbc_attribute25 => p_pbc_attribute25
370 ,p_pbc_attribute26 => p_pbc_attribute26
371 ,p_pbc_attribute27 => p_pbc_attribute27
372 ,p_pbc_attribute28 => p_pbc_attribute28
373 ,p_pbc_attribute29 => p_pbc_attribute29
374 ,p_pbc_attribute30 => p_pbc_attribute30
375 ,p_request_id => p_request_id
376 ,p_program_application_id => p_program_application_id
377 ,p_program_id => p_program_id
378 ,p_program_update_date => p_program_update_date
379 ,p_object_version_number => l_object_version_number
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_PL_BNF_CTFN_PRVDD'
386 ,p_hook_type => 'AP'
387 );
388 --
389 -- End of API User Hook for the after hook of create_PL_BNF_CTFN_PRVDD
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_pl_bnf_ctfn_prvdd_id := l_pl_bnf_ctfn_prvdd_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_PL_BNF_CTFN_PRVDD;
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_pl_bnf_ctfn_prvdd_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_PL_BNF_CTFN_PRVDD;
434 p_pl_bnf_ctfn_prvdd_id := null;
435 p_effective_start_date := null;
436 p_effective_end_date := null;
437 p_object_version_number := null;
438 raise;
439 --
440 end create_PL_BNF_CTFN_PRVDD;
441 -- ----------------------------------------------------------------------------
442 -- |------------------------< update_PL_BNF_CTFN_PRVDD >--- ------------------|
443 -- ----------------------------------------------------------------------------
444 --
445 procedure update_PL_BNF_CTFN_PRVDD
446 (p_validate in boolean default false
447 ,p_pl_bnf_ctfn_prvdd_id in number
448 ,p_effective_start_date out nocopy date
449 ,p_effective_end_date out nocopy date
450 ,p_bnf_ctfn_typ_cd in varchar2 default hr_api.g_varchar2
451 ,p_bnf_ctfn_recd_dt in date default hr_api.g_date
452 ,p_bnf_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
453 ,p_pl_bnf_id in number default hr_api.g_number
454 ,p_prtt_enrt_actn_id in number default hr_api.g_number
455 ,p_business_group_id in number default hr_api.g_number
456 ,p_pbc_attribute_category in varchar2 default hr_api.g_varchar2
457 ,p_pbc_attribute1 in varchar2 default hr_api.g_varchar2
458 ,p_pbc_attribute2 in varchar2 default hr_api.g_varchar2
459 ,p_pbc_attribute3 in varchar2 default hr_api.g_varchar2
460 ,p_pbc_attribute4 in varchar2 default hr_api.g_varchar2
461 ,p_pbc_attribute5 in varchar2 default hr_api.g_varchar2
462 ,p_pbc_attribute6 in varchar2 default hr_api.g_varchar2
463 ,p_pbc_attribute7 in varchar2 default hr_api.g_varchar2
464 ,p_pbc_attribute8 in varchar2 default hr_api.g_varchar2
465 ,p_pbc_attribute9 in varchar2 default hr_api.g_varchar2
466 ,p_pbc_attribute10 in varchar2 default hr_api.g_varchar2
467 ,p_pbc_attribute11 in varchar2 default hr_api.g_varchar2
468 ,p_pbc_attribute12 in varchar2 default hr_api.g_varchar2
469 ,p_pbc_attribute13 in varchar2 default hr_api.g_varchar2
470 ,p_pbc_attribute14 in varchar2 default hr_api.g_varchar2
471 ,p_pbc_attribute15 in varchar2 default hr_api.g_varchar2
472 ,p_pbc_attribute16 in varchar2 default hr_api.g_varchar2
473 ,p_pbc_attribute17 in varchar2 default hr_api.g_varchar2
474 ,p_pbc_attribute18 in varchar2 default hr_api.g_varchar2
475 ,p_pbc_attribute19 in varchar2 default hr_api.g_varchar2
476 ,p_pbc_attribute20 in varchar2 default hr_api.g_varchar2
477 ,p_pbc_attribute21 in varchar2 default hr_api.g_varchar2
478 ,p_pbc_attribute22 in varchar2 default hr_api.g_varchar2
479 ,p_pbc_attribute23 in varchar2 default hr_api.g_varchar2
480 ,p_pbc_attribute24 in varchar2 default hr_api.g_varchar2
481 ,p_pbc_attribute25 in varchar2 default hr_api.g_varchar2
482 ,p_pbc_attribute26 in varchar2 default hr_api.g_varchar2
483 ,p_pbc_attribute27 in varchar2 default hr_api.g_varchar2
484 ,p_pbc_attribute28 in varchar2 default hr_api.g_varchar2
485 ,p_pbc_attribute29 in varchar2 default hr_api.g_varchar2
486 ,p_pbc_attribute30 in varchar2 default hr_api.g_varchar2
487 ,p_request_id in number default hr_api.g_number
488 ,p_program_application_id in number default hr_api.g_number
489 ,p_program_id in number default hr_api.g_number
490 ,p_program_update_date in date default hr_api.g_date
491 ,p_object_version_number in out nocopy number
492 ,p_effective_date in date
493 ,p_datetrack_mode in varchar2
494 ) is
495 --
496 -- Declare cursors and local variables
497 --
498 l_proc varchar2(72) := g_package||'update_PL_BNF_CTFN_PRVDD';
499 l_object_version_number ben_pl_bnf_ctfn_prvdd_f.object_version_number%TYPE;
500 l_effective_start_date ben_pl_bnf_ctfn_prvdd_f.effective_start_date%TYPE;
501 l_effective_end_date ben_pl_bnf_ctfn_prvdd_f.effective_end_date%TYPE;
502 --
503 begin
504 --
505 hr_utility.set_location('Entering:'|| l_proc, 10);
506 --
507 -- Initialize environment
508 --
509 if fnd_global.conc_request_id = -1 then
510 --
511 ben_env_object.init
512 (p_business_group_id => p_business_group_id,
513 p_effective_date => p_effective_date,
514 p_thread_id => null,
515 p_chunk_size => null,
516 p_threads => null,
517 p_max_errors => null,
518 p_benefit_action_id => null);
519 --
520 end if;
521 --
522 -- Issue a savepoint if operating in validation only mode
523 --
524 savepoint update_PL_BNF_CTFN_PRVDD;
525 --
526 hr_utility.set_location(l_proc, 20);
527 --
528 -- Process Logic
529 --
530 l_object_version_number := p_object_version_number;
531 --
532 begin
533 --
534 -- Start of API User Hook for the before hook of update_PL_BNF_CTFN_PRVDD
535 --
536 ben_PL_BNF_CTFN_PRVDD_bk2.update_PL_BNF_CTFN_PRVDD_b
537 (
538 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
539 ,p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
540 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
541 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
542 ,p_pl_bnf_id => p_pl_bnf_id
543 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
544 ,p_business_group_id => p_business_group_id
545 ,p_pbc_attribute_category => p_pbc_attribute_category
546 ,p_pbc_attribute1 => p_pbc_attribute1
547 ,p_pbc_attribute2 => p_pbc_attribute2
548 ,p_pbc_attribute3 => p_pbc_attribute3
549 ,p_pbc_attribute4 => p_pbc_attribute4
550 ,p_pbc_attribute5 => p_pbc_attribute5
551 ,p_pbc_attribute6 => p_pbc_attribute6
552 ,p_pbc_attribute7 => p_pbc_attribute7
553 ,p_pbc_attribute8 => p_pbc_attribute8
554 ,p_pbc_attribute9 => p_pbc_attribute9
555 ,p_pbc_attribute10 => p_pbc_attribute10
556 ,p_pbc_attribute11 => p_pbc_attribute11
557 ,p_pbc_attribute12 => p_pbc_attribute12
558 ,p_pbc_attribute13 => p_pbc_attribute13
559 ,p_pbc_attribute14 => p_pbc_attribute14
560 ,p_pbc_attribute15 => p_pbc_attribute15
561 ,p_pbc_attribute16 => p_pbc_attribute16
562 ,p_pbc_attribute17 => p_pbc_attribute17
563 ,p_pbc_attribute18 => p_pbc_attribute18
564 ,p_pbc_attribute19 => p_pbc_attribute19
565 ,p_pbc_attribute20 => p_pbc_attribute20
566 ,p_pbc_attribute21 => p_pbc_attribute21
567 ,p_pbc_attribute22 => p_pbc_attribute22
568 ,p_pbc_attribute23 => p_pbc_attribute23
569 ,p_pbc_attribute24 => p_pbc_attribute24
570 ,p_pbc_attribute25 => p_pbc_attribute25
571 ,p_pbc_attribute26 => p_pbc_attribute26
572 ,p_pbc_attribute27 => p_pbc_attribute27
573 ,p_pbc_attribute28 => p_pbc_attribute28
574 ,p_pbc_attribute29 => p_pbc_attribute29
575 ,p_pbc_attribute30 => p_pbc_attribute30
576 ,p_request_id => p_request_id
577 ,p_program_application_id => p_program_application_id
578 ,p_program_id => p_program_id
579 ,p_program_update_date => p_program_update_date
580 ,p_object_version_number => p_object_version_number
581 ,p_effective_date => trunc(p_effective_date)
582 ,p_datetrack_mode => p_datetrack_mode
583 );
584 exception
585 when hr_api.cannot_find_prog_unit then
586 hr_api.cannot_find_prog_unit_error
587 (p_module_name => 'UPDATE_PL_BNF_CTFN_PRVDD'
588 ,p_hook_type => 'BP'
589 );
590 --
591 -- End of API User Hook for the before hook of update_PL_BNF_CTFN_PRVDD
592 --
593 end;
594 --
595 ben_pbc_upd.upd
596 (
597 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
598 ,p_effective_start_date => l_effective_start_date
599 ,p_effective_end_date => l_effective_end_date
600 ,p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
601 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
602 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
603 ,p_pl_bnf_id => p_pl_bnf_id
604 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
605 ,p_business_group_id => p_business_group_id
606 ,p_pbc_attribute_category => p_pbc_attribute_category
607 ,p_pbc_attribute1 => p_pbc_attribute1
608 ,p_pbc_attribute2 => p_pbc_attribute2
609 ,p_pbc_attribute3 => p_pbc_attribute3
610 ,p_pbc_attribute4 => p_pbc_attribute4
611 ,p_pbc_attribute5 => p_pbc_attribute5
612 ,p_pbc_attribute6 => p_pbc_attribute6
613 ,p_pbc_attribute7 => p_pbc_attribute7
614 ,p_pbc_attribute8 => p_pbc_attribute8
615 ,p_pbc_attribute9 => p_pbc_attribute9
616 ,p_pbc_attribute10 => p_pbc_attribute10
617 ,p_pbc_attribute11 => p_pbc_attribute11
618 ,p_pbc_attribute12 => p_pbc_attribute12
619 ,p_pbc_attribute13 => p_pbc_attribute13
620 ,p_pbc_attribute14 => p_pbc_attribute14
621 ,p_pbc_attribute15 => p_pbc_attribute15
622 ,p_pbc_attribute16 => p_pbc_attribute16
623 ,p_pbc_attribute17 => p_pbc_attribute17
624 ,p_pbc_attribute18 => p_pbc_attribute18
625 ,p_pbc_attribute19 => p_pbc_attribute19
626 ,p_pbc_attribute20 => p_pbc_attribute20
627 ,p_pbc_attribute21 => p_pbc_attribute21
628 ,p_pbc_attribute22 => p_pbc_attribute22
629 ,p_pbc_attribute23 => p_pbc_attribute23
630 ,p_pbc_attribute24 => p_pbc_attribute24
631 ,p_pbc_attribute25 => p_pbc_attribute25
632 ,p_pbc_attribute26 => p_pbc_attribute26
633 ,p_pbc_attribute27 => p_pbc_attribute27
634 ,p_pbc_attribute28 => p_pbc_attribute28
635 ,p_pbc_attribute29 => p_pbc_attribute29
636 ,p_pbc_attribute30 => p_pbc_attribute30
637 ,p_request_id => p_request_id
638 ,p_program_application_id => p_program_application_id
639 ,p_program_id => p_program_id
640 ,p_program_update_date => p_program_update_date
641 ,p_object_version_number => l_object_version_number
642 ,p_effective_date => trunc(p_effective_date)
643 ,p_datetrack_mode => p_datetrack_mode
644 );
645 --
646 -- call procedure to close actn items here
647 --
648 --
649 check_bnf_ctfn
650 (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
651 p_datetrack_mode => p_datetrack_mode,
652 p_business_group_id => p_business_group_id,
653 p_effective_date => p_effective_date);
654 --
655 begin
656 --
657 -- Start of API User Hook for the after hook of update_PL_BNF_CTFN_PRVDD
658 --
659 ben_PL_BNF_CTFN_PRVDD_bk2.update_PL_BNF_CTFN_PRVDD_a
660 (
661 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
662 ,p_effective_start_date => l_effective_start_date
663 ,p_effective_end_date => l_effective_end_date
664 ,p_bnf_ctfn_typ_cd => p_bnf_ctfn_typ_cd
665 ,p_bnf_ctfn_recd_dt => p_bnf_ctfn_recd_dt
666 ,p_bnf_ctfn_rqd_flag => p_bnf_ctfn_rqd_flag
667 ,p_pl_bnf_id => p_pl_bnf_id
668 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
669 ,p_business_group_id => p_business_group_id
670 ,p_pbc_attribute_category => p_pbc_attribute_category
671 ,p_pbc_attribute1 => p_pbc_attribute1
672 ,p_pbc_attribute2 => p_pbc_attribute2
673 ,p_pbc_attribute3 => p_pbc_attribute3
674 ,p_pbc_attribute4 => p_pbc_attribute4
675 ,p_pbc_attribute5 => p_pbc_attribute5
676 ,p_pbc_attribute6 => p_pbc_attribute6
677 ,p_pbc_attribute7 => p_pbc_attribute7
678 ,p_pbc_attribute8 => p_pbc_attribute8
679 ,p_pbc_attribute9 => p_pbc_attribute9
680 ,p_pbc_attribute10 => p_pbc_attribute10
681 ,p_pbc_attribute11 => p_pbc_attribute11
682 ,p_pbc_attribute12 => p_pbc_attribute12
683 ,p_pbc_attribute13 => p_pbc_attribute13
684 ,p_pbc_attribute14 => p_pbc_attribute14
685 ,p_pbc_attribute15 => p_pbc_attribute15
686 ,p_pbc_attribute16 => p_pbc_attribute16
687 ,p_pbc_attribute17 => p_pbc_attribute17
688 ,p_pbc_attribute18 => p_pbc_attribute18
689 ,p_pbc_attribute19 => p_pbc_attribute19
690 ,p_pbc_attribute20 => p_pbc_attribute20
691 ,p_pbc_attribute21 => p_pbc_attribute21
692 ,p_pbc_attribute22 => p_pbc_attribute22
693 ,p_pbc_attribute23 => p_pbc_attribute23
694 ,p_pbc_attribute24 => p_pbc_attribute24
695 ,p_pbc_attribute25 => p_pbc_attribute25
696 ,p_pbc_attribute26 => p_pbc_attribute26
697 ,p_pbc_attribute27 => p_pbc_attribute27
698 ,p_pbc_attribute28 => p_pbc_attribute28
699 ,p_pbc_attribute29 => p_pbc_attribute29
700 ,p_pbc_attribute30 => p_pbc_attribute30
701 ,p_request_id => p_request_id
702 ,p_program_application_id => p_program_application_id
703 ,p_program_id => p_program_id
704 ,p_program_update_date => p_program_update_date
705 ,p_object_version_number => l_object_version_number
706 ,p_effective_date => trunc(p_effective_date)
707 ,p_datetrack_mode => p_datetrack_mode
708 );
709 exception
710 when hr_api.cannot_find_prog_unit then
711 hr_api.cannot_find_prog_unit_error
712 (p_module_name => 'UPDATE_PL_BNF_CTFN_PRVDD'
713 ,p_hook_type => 'AP'
714 );
715 --
716 -- End of API User Hook for the after hook of update_PL_BNF_CTFN_PRVDD
717 --
718 end;
719 --
720 hr_utility.set_location(l_proc, 60);
721 --
722 -- When in validation only mode raise the Validate_Enabled exception
723 --
724 if p_validate then
725 raise hr_api.validate_enabled;
726 end if;
727 --
728 -- Set all output arguments
729 --
730 p_object_version_number := l_object_version_number;
731 p_effective_start_date := l_effective_start_date;
732 p_effective_end_date := l_effective_end_date;
733 --
734 hr_utility.set_location(' Leaving:'||l_proc, 70);
735 --
736 exception
737 --
738 when hr_api.validate_enabled then
739 --
740 -- As the Validate_Enabled exception has been raised
741 -- we must rollback to the savepoint
742 --
743 ROLLBACK TO update_PL_BNF_CTFN_PRVDD;
744 --
745 -- Only set output warning arguments
746 -- (Any key or derived arguments must be set to null
747 -- when validation only mode is being used.)
748 --
749 p_effective_start_date := null;
750 p_effective_end_date := null;
751 hr_utility.set_location(' Leaving:'||l_proc, 80);
752 --
753 when others then
754 --
755 -- A validation or unexpected error has occured
756 --
757 ROLLBACK TO update_PL_BNF_CTFN_PRVDD;
758 p_effective_start_date := null;
759 p_effective_end_date := null;
760 raise;
761 --
762 end update_PL_BNF_CTFN_PRVDD;
763 -- ----------------------------------------------------------------------------
764 -- |------------------------< delete_PL_BNF_CTFN_PRVDD >----------------------|
765 -- ----------------------------------------------------------------------------
766 --
767 procedure delete_PL_BNF_CTFN_PRVDD
768 (p_validate in boolean default false
769 ,p_pl_bnf_ctfn_prvdd_id in number
770 ,p_effective_start_date out nocopy date
771 ,p_effective_end_date out nocopy date
772 ,p_object_version_number in out nocopy number
773 ,p_business_group_id in number
774 ,p_effective_date in date
775 ,p_datetrack_mode in varchar2
776 ,p_check_actions in varchar2 default 'Y'
777 ) is
778 --
779 -- Declare cursors and local variables
780 --
781 l_proc varchar2(72) := g_package||'delete_PL_BNF_CTFN_PRVDD';
782 l_object_version_number ben_pl_bnf_ctfn_prvdd_f.object_version_number%TYPE;
783 l_effective_start_date ben_pl_bnf_ctfn_prvdd_f.effective_start_date%TYPE;
784 l_effective_end_date ben_pl_bnf_ctfn_prvdd_f.effective_end_date%TYPE;
785 --
786 l_prtt_enrt_actn_id number(15) := null;
787 l_prtt_enrt_rslt_id number(15);
788 l_rslt_object_version_number number(15);
789 l_exist varchar2(1) := 'N';
790 l1_object_version_number ben_prtt_enrt_actn_f.object_version_number%TYPE;
791 l1_effective_start_date ben_prtt_enrt_actn_f.effective_start_date%TYPE;
792 l1_effective_end_date ben_prtt_enrt_actn_f.effective_end_date%TYPE;
793
794 --
795 cursor get_actn_c is
796 select prtt_enrt_actn_id
797 from ben_pl_bnf_ctfn_prvdd_f
798 where pl_bnf_ctfn_prvdd_id = p_pl_bnf_ctfn_prvdd_id
799 and business_group_id = p_business_group_id
800 and p_effective_date between effective_start_date
801 and effective_end_date;
802 --
803 cursor more_ctfn_c is
804 select 'Y'
805 from ben_pl_bnf_ctfn_prvdd_f
806 where prtt_enrt_actn_id = l_prtt_enrt_actn_id
807 and business_group_id = p_business_group_id
808 and p_effective_date between effective_start_date
809 and effective_end_date;
810 --
811 cursor actn_info_c is
812 select pea.object_version_number,
813 pea.prtt_enrt_rslt_id,
814 pen.object_version_number
815 from ben_prtt_enrt_actn_f pea,
816 ben_prtt_enrt_rslt_f pen
817 where pea.prtt_enrt_actn_id = l_prtt_enrt_actn_id
818 and pea.business_group_id = p_business_group_id
819 and pen.prtt_enrt_rslt_stat_cd is null
820 and p_effective_date between pea.effective_start_date
821 and pea.effective_end_date
822 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
823 and p_effective_date between
824 pen.effective_start_date and pen.effective_end_date;
825 --
826 begin
827 --
828 hr_utility.set_location('Entering:'|| l_proc, 10);
829 --
830 -- Initialize environment
831 --
832 if fnd_global.conc_request_id = -1 then
833 --
834 ben_env_object.init
835 (p_business_group_id => p_business_group_id,
836 p_effective_date => p_effective_date,
837 p_thread_id => null,
838 p_chunk_size => null,
839 p_threads => null,
840 p_max_errors => null,
841 p_benefit_action_id => null);
842 --
843 end if;
844 --
845 -- Issue a savepoint if operating in validation only mode
846 --
847 savepoint delete_PL_BNF_CTFN_PRVDD;
848 --
849 hr_utility.set_location(l_proc, 20);
850 --
851 -- Process Logic
852 --
853 l_object_version_number := p_object_version_number;
854 --
855 --
856 begin
857 --
858 -- Start of API User Hook for the before hook of delete_PL_BNF_CTFN_PRVDD
859 --
860 ben_PL_BNF_CTFN_PRVDD_bk3.delete_PL_BNF_CTFN_PRVDD_b
861 (
862 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
863 ,p_object_version_number => p_object_version_number
864 ,p_effective_date => trunc(p_effective_date)
865 ,p_datetrack_mode => p_datetrack_mode
866 );
867 exception
868 when hr_api.cannot_find_prog_unit then
869 hr_api.cannot_find_prog_unit_error
870 (p_module_name => 'DELETE_PL_BNF_CTFN_PRVDD'
871 ,p_hook_type => 'BP'
872 );
873 --
874 -- End of API User Hook for the before hook of delete_PL_BNF_CTFN_PRVDD
875 --
876 end;
877 --
878 -- Get action item id
879 --
880 open get_actn_c;
881 fetch get_actn_c into l_prtt_enrt_actn_id;
882 close get_actn_c;
883 --
884 ben_pbc_del.del
885 (
886 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
887 ,p_effective_start_date => l_effective_start_date
888 ,p_effective_end_date => l_effective_end_date
889 ,p_object_version_number => l_object_version_number
890 ,p_effective_date => p_effective_date
891 ,p_datetrack_mode => p_datetrack_mode
892 );
893 --
894 -- If there are no remaining certifications,
895 -- delete corresponding action item.
896 --
897 if p_check_actions = 'Y' then
898 --
899 open more_ctfn_c;
900 fetch more_ctfn_c into l_exist;
901 close more_ctfn_c;
902 --
903 if l_exist = 'N' then
904 --
905 open actn_info_c;
906 fetch actn_info_c into l1_object_version_number,
907 l_prtt_enrt_rslt_id,
908 l_rslt_object_version_number;
909 if actn_info_c%FOUND then
910 --
911 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
912 (p_validate => p_validate,
913 p_effective_date => p_effective_date,
914 p_business_group_id => p_business_group_id,
915 p_datetrack_mode => p_datetrack_mode,
916 p_object_version_number => l1_object_version_number,
917 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
918 p_rslt_object_version_number => l_rslt_object_version_number,
919 p_post_rslt_flag => 'N',
920 p_unsuspend_enrt_flag => 'Y',
921 p_effective_start_date => l1_effective_start_date,
922 p_effective_end_date => l1_effective_end_date,
923 p_prtt_enrt_actn_id => l_prtt_enrt_actn_id);
924 --
925 end if;
926 --
927 close actn_info_c;
928 --
929 else
930 --
931 -- Other Ctfn exist. Check whether action item can be closed.
932 --
933 check_bnf_ctfn
934 (p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
935 p_datetrack_mode => p_datetrack_mode,
936 p_business_group_id => p_business_group_id,
937 p_effective_date => p_effective_date);
938 --
939 end if; -- l_exist
940 --
941 end if; -- check_actions
942 --
943 begin
944 --
945 -- Start of API User Hook for the after hook of delete_PL_BNF_CTFN_PRVDD
946 --
947 ben_PL_BNF_CTFN_PRVDD_bk3.delete_PL_BNF_CTFN_PRVDD_a
948 (
949 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
950 ,p_effective_start_date => l_effective_start_date
951 ,p_effective_end_date => l_effective_end_date
952 ,p_object_version_number => l_object_version_number
953 ,p_effective_date => trunc(p_effective_date)
954 ,p_datetrack_mode => p_datetrack_mode
955 );
956 exception
957 when hr_api.cannot_find_prog_unit then
958 hr_api.cannot_find_prog_unit_error
959 (p_module_name => 'DELETE_PL_BNF_CTFN_PRVDD'
960 ,p_hook_type => 'AP'
961 );
962 --
963 -- End of API User Hook for the after hook of delete_PL_BNF_CTFN_PRVDD
964 --
965 end;
966 --
967 hr_utility.set_location(l_proc, 60);
968 --
969 -- When in validation only mode raise the Validate_Enabled exception
970 --
971 if p_validate then
972 raise hr_api.validate_enabled;
973 end if;
974 --
975 hr_utility.set_location(' Leaving:'||l_proc, 70);
976 --
977 exception
978 --
979 when hr_api.validate_enabled then
980 --
981 -- As the Validate_Enabled exception has been raised
982 -- we must rollback to the savepoint
983 --
984 ROLLBACK TO delete_PL_BNF_CTFN_PRVDD;
985 --
986 -- Only set output warning arguments
987 -- (Any key or derived arguments must be set to null
988 -- when validation only mode is being used.)
989 --
990 p_effective_start_date := null;
991 p_effective_end_date := null;
992 --
993 when others then
994 --
995 -- A validation or unexpected error has occured
996 --
997 ROLLBACK TO delete_PL_BNF_CTFN_PRVDD;
998 p_effective_start_date := null;
999 p_effective_end_date := null;
1000 raise;
1001 --
1002 end delete_PL_BNF_CTFN_PRVDD;
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |-------------------------------< lck >------------------------------------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 procedure lck
1009 (
1010 p_pl_bnf_ctfn_prvdd_id in number
1011 ,p_object_version_number in number
1012 ,p_effective_date in date
1013 ,p_datetrack_mode in varchar2
1014 ,p_validation_start_date out nocopy date
1015 ,p_validation_end_date out nocopy date
1016 ) is
1017 --
1018 --
1019 -- Declare cursors and local variables
1020 --
1021 l_proc varchar2(72) := g_package||'lck';
1022 l_validation_start_date date;
1023 l_validation_end_date date;
1024 --
1025 begin
1026 --
1027 hr_utility.set_location('Entering:'|| l_proc, 10);
1028 --
1029 ben_pbc_shd.lck
1030 (
1031 p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id
1032 ,p_validation_start_date => l_validation_start_date
1033 ,p_validation_end_date => l_validation_end_date
1034 ,p_object_version_number => p_object_version_number
1035 ,p_effective_date => p_effective_date
1036 ,p_datetrack_mode => p_datetrack_mode
1037 );
1038 --
1039 hr_utility.set_location(' Leaving:'||l_proc, 70);
1040 --
1041 end lck;
1042 --
1043 end ben_PL_BNF_CTFN_PRVDD_api;