[Home] [Help]
PACKAGE BODY: APPS.BEN_PLAN_BENEFICIARY_API
Source
1 Package Body ben_PLAN_BENEFICIARY_api as
2 /* $Header: bepbnapi.pkb 120.5 2008/02/22 16:31:50 rtagarra noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_PLAN_BENEFICIARY_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_PLAN_BENEFICIARY >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 --
13 procedure create_PLAN_BENEFICIARY
14 (p_validate in boolean default false
15 ,p_pl_bnf_id out nocopy number
16 ,p_effective_start_date out nocopy date
17 ,p_effective_end_date out nocopy date
18 ,p_business_group_id in number default null
19 ,p_prtt_enrt_rslt_id in number default null
20 ,p_bnf_person_id in number default null
21 ,p_organization_id in number default null
22 ,p_ttee_person_id in number default null
23 ,p_prmry_cntngnt_cd in varchar2 default null
24 ,p_pct_dsgd_num in number default null
25 ,p_amt_dsgd_val in number default null
26 ,p_amt_dsgd_uom in varchar2 default null
27 ,p_dsgn_strt_dt in date default null
28 ,p_dsgn_thru_dt in date default null
29 ,p_addl_instrn_txt in varchar2 default null
30 ,p_pbn_attribute_category in varchar2 default null
31 ,p_pbn_attribute1 in varchar2 default null
32 ,p_pbn_attribute2 in varchar2 default null
33 ,p_pbn_attribute3 in varchar2 default null
34 ,p_pbn_attribute4 in varchar2 default null
35 ,p_pbn_attribute5 in varchar2 default null
36 ,p_pbn_attribute6 in varchar2 default null
37 ,p_pbn_attribute7 in varchar2 default null
38 ,p_pbn_attribute8 in varchar2 default null
39 ,p_pbn_attribute9 in varchar2 default null
40 ,p_pbn_attribute10 in varchar2 default null
41 ,p_pbn_attribute11 in varchar2 default null
42 ,p_pbn_attribute12 in varchar2 default null
43 ,p_pbn_attribute13 in varchar2 default null
44 ,p_pbn_attribute14 in varchar2 default null
45 ,p_pbn_attribute15 in varchar2 default null
46 ,p_pbn_attribute16 in varchar2 default null
47 ,p_pbn_attribute17 in varchar2 default null
48 ,p_pbn_attribute18 in varchar2 default null
49 ,p_pbn_attribute19 in varchar2 default null
50 ,p_pbn_attribute20 in varchar2 default null
51 ,p_pbn_attribute21 in varchar2 default null
52 ,p_pbn_attribute22 in varchar2 default null
53 ,p_pbn_attribute23 in varchar2 default null
54 ,p_pbn_attribute24 in varchar2 default null
55 ,p_pbn_attribute25 in varchar2 default null
56 ,p_pbn_attribute26 in varchar2 default null
57 ,p_pbn_attribute27 in varchar2 default null
58 ,p_pbn_attribute28 in varchar2 default null
59 ,p_pbn_attribute29 in varchar2 default null
60 ,p_pbn_attribute30 in varchar2 default null
61 ,p_request_id in number default null
62 ,p_program_application_id in number default null
63 ,p_program_id in number default null
64 ,p_program_update_date in date default null
65 ,p_object_version_number out nocopy number
66 ,p_per_in_ler_id in number default null
67 ,p_effective_date in date
68 ,p_multi_row_actn in boolean default TRUE
69 ) is
70 --
71 --
72 -- Declare cursors and local variables
73 --
74 l_pl_bnf_id ben_pl_bnf_f.pl_bnf_id%TYPE;
75 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
76 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
77 l_proc varchar2(72) := g_package||'create_PLAN_BENEFICIARY';
78 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
79 l_per_in_ler_id ben_pl_bnf_f.per_in_ler_id%TYPE;
80 --
81 cursor c_pil is
82 select pil.per_in_ler_id
83 from ben_prtt_enrt_rslt_f pen ,
84 ben_per_in_ler pil
85 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
86 and pen.per_in_ler_id = pil.per_in_ler_id
87 and pen.prtt_enrt_rslt_stat_cd is null
88 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
89 order by pil.lf_evt_ocrd_dt asc;
90 --
91 begin
92 --
93 hr_utility.set_location('Entering:'|| l_proc, 10);
94 --
95 if fnd_global.conc_request_id = -1 then
96 --
97 ben_env_object.init(p_business_group_id => p_business_group_id,
98 p_effective_date => p_effective_date,
99 p_thread_id => 1,
100 p_chunk_size => 1,
101 p_threads => 1,
102 p_max_errors => 1,
103 p_benefit_action_id => null);
104 --
105 end if;
106 --
107 -- Issue a savepoint if operating in validation only mode
108 --
109 /***
110 Start 5883382 - Get the earliest possible PIL for BNF
111 ***/
112 l_per_in_ler_id := null ;
113 open c_pil;
114 fetch c_pil into l_per_in_ler_id;
115 close c_pil;
116
117
118
119 l_per_in_ler_id := nvl(l_per_in_ler_id,p_per_in_ler_id);
120
121 /***
122 End 5883382 - Get the earliest possible PIL for BNF
123 ***/
124 savepoint create_PLAN_BENEFICIARY;
125 --
126 hr_utility.set_location(l_proc, 20);
127 --
128 -- Process Logic
129 --
130 --
131 begin
132 --
133 -- Start of API User Hook for the before hook of create_PLAN_BENEFICIARY
134 --
135 ben_PLAN_BENEFICIARY_bk1.create_PLAN_BENEFICIARY_b
136 (
137 p_business_group_id => p_business_group_id
138 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
139 ,p_bnf_person_id => p_bnf_person_id
140 ,p_organization_id => p_organization_id
141 ,p_ttee_person_id => p_ttee_person_id
142 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
143 ,p_pct_dsgd_num => p_pct_dsgd_num
144 ,p_amt_dsgd_val => p_amt_dsgd_val
145 ,p_amt_dsgd_uom => p_amt_dsgd_uom
146 ,p_dsgn_strt_dt => p_dsgn_strt_dt
147 ,p_dsgn_thru_dt => p_dsgn_thru_dt
148 ,p_addl_instrn_txt => p_addl_instrn_txt
149 ,p_pbn_attribute_category => p_pbn_attribute_category
150 ,p_pbn_attribute1 => p_pbn_attribute1
151 ,p_pbn_attribute2 => p_pbn_attribute2
152 ,p_pbn_attribute3 => p_pbn_attribute3
153 ,p_pbn_attribute4 => p_pbn_attribute4
154 ,p_pbn_attribute5 => p_pbn_attribute5
155 ,p_pbn_attribute6 => p_pbn_attribute6
156 ,p_pbn_attribute7 => p_pbn_attribute7
157 ,p_pbn_attribute8 => p_pbn_attribute8
158 ,p_pbn_attribute9 => p_pbn_attribute9
159 ,p_pbn_attribute10 => p_pbn_attribute10
160 ,p_pbn_attribute11 => p_pbn_attribute11
161 ,p_pbn_attribute12 => p_pbn_attribute12
162 ,p_pbn_attribute13 => p_pbn_attribute13
163 ,p_pbn_attribute14 => p_pbn_attribute14
164 ,p_pbn_attribute15 => p_pbn_attribute15
165 ,p_pbn_attribute16 => p_pbn_attribute16
166 ,p_pbn_attribute17 => p_pbn_attribute17
167 ,p_pbn_attribute18 => p_pbn_attribute18
168 ,p_pbn_attribute19 => p_pbn_attribute19
169 ,p_pbn_attribute20 => p_pbn_attribute20
170 ,p_pbn_attribute21 => p_pbn_attribute21
171 ,p_pbn_attribute22 => p_pbn_attribute22
172 ,p_pbn_attribute23 => p_pbn_attribute23
173 ,p_pbn_attribute24 => p_pbn_attribute24
174 ,p_pbn_attribute25 => p_pbn_attribute25
175 ,p_pbn_attribute26 => p_pbn_attribute26
176 ,p_pbn_attribute27 => p_pbn_attribute27
177 ,p_pbn_attribute28 => p_pbn_attribute28
178 ,p_pbn_attribute29 => p_pbn_attribute29
179 ,p_pbn_attribute30 => p_pbn_attribute30
180 ,p_request_id => p_request_id
181 ,p_program_application_id => p_program_application_id
182 ,p_program_id => p_program_id
183 ,p_program_update_date => p_program_update_date
184 ,p_per_in_ler_id => l_per_in_ler_id
185 ,p_effective_date => trunc(p_effective_date)
186 );
187 exception
188 when hr_api.cannot_find_prog_unit then
189 hr_api.cannot_find_prog_unit_error
190 (
191 p_module_name => 'CREATE_PLAN_BENEFICIARY'
192 ,p_hook_type => 'BP'
193 );
194 --
195 -- End of API User Hook for the before hook of create_PLAN_BENEFICIARY
196 --
197 end;
198 --
199 ben_pbn_ins.ins
200 (
201 p_pl_bnf_id => l_pl_bnf_id
202 ,p_effective_start_date => l_effective_start_date
203 ,p_effective_end_date => l_effective_end_date
204 ,p_business_group_id => p_business_group_id
205 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
206 ,p_bnf_person_id => p_bnf_person_id
207 ,p_organization_id => p_organization_id
208 ,p_ttee_person_id => p_ttee_person_id
209 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
210 ,p_pct_dsgd_num => p_pct_dsgd_num
211 ,p_amt_dsgd_val => p_amt_dsgd_val
212 ,p_amt_dsgd_uom => p_amt_dsgd_uom
213 ,p_dsgn_strt_dt => p_dsgn_strt_dt
214 ,p_dsgn_thru_dt => p_dsgn_thru_dt
215 ,p_addl_instrn_txt => p_addl_instrn_txt
216 ,p_pbn_attribute_category => p_pbn_attribute_category
217 ,p_pbn_attribute1 => p_pbn_attribute1
218 ,p_pbn_attribute2 => p_pbn_attribute2
219 ,p_pbn_attribute3 => p_pbn_attribute3
220 ,p_pbn_attribute4 => p_pbn_attribute4
221 ,p_pbn_attribute5 => p_pbn_attribute5
222 ,p_pbn_attribute6 => p_pbn_attribute6
223 ,p_pbn_attribute7 => p_pbn_attribute7
224 ,p_pbn_attribute8 => p_pbn_attribute8
225 ,p_pbn_attribute9 => p_pbn_attribute9
226 ,p_pbn_attribute10 => p_pbn_attribute10
227 ,p_pbn_attribute11 => p_pbn_attribute11
228 ,p_pbn_attribute12 => p_pbn_attribute12
229 ,p_pbn_attribute13 => p_pbn_attribute13
230 ,p_pbn_attribute14 => p_pbn_attribute14
231 ,p_pbn_attribute15 => p_pbn_attribute15
232 ,p_pbn_attribute16 => p_pbn_attribute16
233 ,p_pbn_attribute17 => p_pbn_attribute17
234 ,p_pbn_attribute18 => p_pbn_attribute18
235 ,p_pbn_attribute19 => p_pbn_attribute19
236 ,p_pbn_attribute20 => p_pbn_attribute20
237 ,p_pbn_attribute21 => p_pbn_attribute21
238 ,p_pbn_attribute22 => p_pbn_attribute22
239 ,p_pbn_attribute23 => p_pbn_attribute23
240 ,p_pbn_attribute24 => p_pbn_attribute24
241 ,p_pbn_attribute25 => p_pbn_attribute25
242 ,p_pbn_attribute26 => p_pbn_attribute26
243 ,p_pbn_attribute27 => p_pbn_attribute27
244 ,p_pbn_attribute28 => p_pbn_attribute28
245 ,p_pbn_attribute29 => p_pbn_attribute29
246 ,p_pbn_attribute30 => p_pbn_attribute30
247 ,p_request_id => p_request_id
248 ,p_program_application_id => p_program_application_id
249 ,p_program_id => p_program_id
250 ,p_program_update_date => p_program_update_date
251 ,p_object_version_number => l_object_version_number
252 ,p_per_in_ler_id => l_per_in_ler_id
253 ,p_effective_date => trunc(p_effective_date)
254 );
255 --
256 -- Call Action item RCO if p_multi_row_actn = TRUE
257 --
258 if p_multi_row_actn then
259 --
260 bnf_actn_items(
261 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
262 ,p_pl_bnf_id => l_pl_bnf_id
263 ,p_effective_date => p_effective_date
264 ,p_business_group_id => p_business_group_id
265 ,p_validate => p_validate
266 ,p_datetrack_mode => null
267 );
268 --
269 end if;
270 --
271 -- Create person type usage, if needed
272 --
273 if p_bnf_person_id is not null then
274 --
275 add_usage(
276 p_validate => p_validate
277 ,p_pl_bnf_id => l_pl_bnf_id
278 ,p_bnf_person_id => p_bnf_person_id
279 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
280 ,p_business_group_id => p_business_group_id
281 ,p_effective_date => p_effective_date
282 ,p_datetrack_mode => null
283 );
284
285 --
286 end if;
287 --
288 --
289 begin
290 --
291 -- Start of API User Hook for the after hook of create_PLAN_BENEFICIARY
292 --
293 ben_PLAN_BENEFICIARY_bk1.create_PLAN_BENEFICIARY_a
294 (
295 p_pl_bnf_id => l_pl_bnf_id
296 ,p_effective_start_date => l_effective_start_date
297 ,p_effective_end_date => l_effective_end_date
298 ,p_business_group_id => p_business_group_id
299 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
300 ,p_bnf_person_id => p_bnf_person_id
301 ,p_organization_id => p_organization_id
302 ,p_ttee_person_id => p_ttee_person_id
303 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
304 ,p_pct_dsgd_num => p_pct_dsgd_num
305 ,p_amt_dsgd_val => p_amt_dsgd_val
306 ,p_amt_dsgd_uom => p_amt_dsgd_uom
307 ,p_dsgn_strt_dt => p_dsgn_strt_dt
308 ,p_dsgn_thru_dt => p_dsgn_thru_dt
309 ,p_addl_instrn_txt => p_addl_instrn_txt
310 ,p_pbn_attribute_category => p_pbn_attribute_category
311 ,p_pbn_attribute1 => p_pbn_attribute1
312 ,p_pbn_attribute2 => p_pbn_attribute2
313 ,p_pbn_attribute3 => p_pbn_attribute3
314 ,p_pbn_attribute4 => p_pbn_attribute4
315 ,p_pbn_attribute5 => p_pbn_attribute5
316 ,p_pbn_attribute6 => p_pbn_attribute6
317 ,p_pbn_attribute7 => p_pbn_attribute7
318 ,p_pbn_attribute8 => p_pbn_attribute8
319 ,p_pbn_attribute9 => p_pbn_attribute9
320 ,p_pbn_attribute10 => p_pbn_attribute10
321 ,p_pbn_attribute11 => p_pbn_attribute11
322 ,p_pbn_attribute12 => p_pbn_attribute12
323 ,p_pbn_attribute13 => p_pbn_attribute13
324 ,p_pbn_attribute14 => p_pbn_attribute14
325 ,p_pbn_attribute15 => p_pbn_attribute15
326 ,p_pbn_attribute16 => p_pbn_attribute16
327 ,p_pbn_attribute17 => p_pbn_attribute17
328 ,p_pbn_attribute18 => p_pbn_attribute18
329 ,p_pbn_attribute19 => p_pbn_attribute19
330 ,p_pbn_attribute20 => p_pbn_attribute20
331 ,p_pbn_attribute21 => p_pbn_attribute21
332 ,p_pbn_attribute22 => p_pbn_attribute22
333 ,p_pbn_attribute23 => p_pbn_attribute23
334 ,p_pbn_attribute24 => p_pbn_attribute24
335 ,p_pbn_attribute25 => p_pbn_attribute25
336 ,p_pbn_attribute26 => p_pbn_attribute26
337 ,p_pbn_attribute27 => p_pbn_attribute27
338 ,p_pbn_attribute28 => p_pbn_attribute28
339 ,p_pbn_attribute29 => p_pbn_attribute29
340 ,p_pbn_attribute30 => p_pbn_attribute30
341 ,p_request_id => p_request_id
342 ,p_program_application_id => p_program_application_id
343 ,p_program_id => p_program_id
344 ,p_program_update_date => p_program_update_date
345 ,p_object_version_number => l_object_version_number
346 ,p_per_in_ler_id => l_per_in_ler_id
347 ,p_effective_date => trunc(p_effective_date)
348 );
349 exception
350 when hr_api.cannot_find_prog_unit then
351 hr_api.cannot_find_prog_unit_error
352 (p_module_name => 'CREATE_PLAN_BENEFICIARY'
353 ,p_hook_type => 'AP'
354 );
355 --
356 -- End of API User Hook for the after hook of create_PLAN_BENEFICIARY
357 --
358 end;
359 --
360 hr_utility.set_location(l_proc, 60);
361 --
362 -- When in validation only mode raise the Validate_Enabled exception
363 --
364 if p_validate then
365 raise hr_api.validate_enabled;
366 end if;
367 --
368 -- Set all output arguments
369 --
370 p_pl_bnf_id := l_pl_bnf_id;
371 p_effective_start_date := l_effective_start_date;
372 p_effective_end_date := l_effective_end_date;
373 p_object_version_number := l_object_version_number;
374 --
375 hr_utility.set_location(' Leaving:'||l_proc, 70);
376 --
377 exception
378 --
379 when hr_api.validate_enabled then
380 --
381 -- As the Validate_Enabled exception has been raised
382 -- we must rollback to the savepoint
383 --
384 ROLLBACK TO create_PLAN_BENEFICIARY;
385 --
386 -- Only set output warning arguments
387 -- (Any key or derived arguments must be set to null
388 -- when validation only mode is being used.)
389 --
390 p_pl_bnf_id := null;
391 p_effective_start_date := null;
392 p_effective_end_date := null;
393 p_object_version_number := null;
394 hr_utility.set_location(' Leaving:'||l_proc, 80);
395 --
396 when others then
397 --
398 -- A validation or unexpected error has occured
399 --
400 ROLLBACK TO create_PLAN_BENEFICIARY;
401 p_pl_bnf_id := null;
402 p_effective_start_date := null;
403 p_effective_end_date := null;
404 p_object_version_number := null;
405 raise;
406 --
407 End CREATE_PLAN_BENEFICIARY;
408 --
409 -- ----------------------------------------------------------------------------
410 -- |----------------------< create_PLAN_BENEFICIARY_w >-----------------------|
411 -- ----------------------------------------------------------------------------
412 --
413 procedure create_PLAN_BENEFICIARY_w
414 (
415 p_validate in varchar2
416 ,p_pl_bnf_id out nocopy number
417 ,p_effective_start_date out nocopy date
418 ,p_effective_end_date out nocopy date
419 ,p_business_group_id in number
420 ,p_prtt_enrt_rslt_id in number
421 ,p_bnf_person_id in number default null
422 ,p_organization_id in number default null
423 ,p_prmry_cntngnt_cd in varchar2
424 ,p_pct_dsgd_num in number
425 ,p_dsgn_strt_dt in date
426 ,p_dsgn_thru_dt in date
427 ,p_object_version_number out nocopy number
428 ,p_per_in_ler_id in number
429 ,p_effective_date in date
430 ,p_multi_row_actn in varchar2
431 )
432 is
433
434 l_proc varchar2(72) := g_package||'create_plan_beneficiary wrapper';
435
436 l_pl_bnf_id ben_pl_bnf_f.pl_bnf_id%TYPE;
437 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
438 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
439 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
440
441 l_validate BOOLEAN;
442 l_multi_row_actn BOOLEAN;
443 --
444 --
445 begin
446 --
447 hr_utility.set_location('Entering:'|| l_proc, 10);
448 --
449 if fnd_global.conc_request_id in (0,-1) then
450 --
451 ben_env_object.init(p_business_group_id => p_business_group_id,
452 p_effective_date => p_effective_date,
453 p_thread_id => 1,
454 p_chunk_size => 1,
455 p_threads => 1,
456 p_max_errors => 1,
457 p_benefit_action_id => null);
458 --
459 end if;
460 --
461 hr_utility.set_location(l_proc, 20);
462 --
463 --
464 if upper(p_validate) = 'TRUE'
465 then
466 l_validate := TRUE;
467 else
468 l_validate := FALSE;
469 end if;
470
471 if upper(p_multi_row_actn) = 'TRUE'
472 then
473 l_multi_row_actn := TRUE;
474 else
475 l_multi_row_actn := FALSE;
476 end if;
477
478 create_PLAN_BENEFICIARY
479 (
480 p_validate => l_validate
481 ,p_pl_bnf_id => l_pl_bnf_id
482 ,p_effective_start_date => l_effective_start_date
483 ,p_effective_end_date => l_effective_end_date
484 ,p_business_group_id => p_business_group_id
485 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
486 ,p_bnf_person_id => p_bnf_person_id
487 ,p_organization_id => p_organization_id
488 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
489 ,p_pct_dsgd_num => p_pct_dsgd_num
490 ,p_dsgn_strt_dt => p_dsgn_strt_dt
491 ,p_dsgn_thru_dt => p_dsgn_thru_dt
492 ,p_object_version_number => l_object_version_number
493 ,p_per_in_ler_id => p_per_in_ler_id
494 ,p_effective_date => p_effective_date
495 ,p_multi_row_actn => l_multi_row_actn
496 );
497 --
498 hr_utility.set_location(l_proc, 30);
499 --
500 -- Set all output arguments
501 --
502 p_pl_bnf_id := l_pl_bnf_id;
503 p_effective_start_date := l_effective_start_date;
504 p_effective_end_date := l_effective_end_date;
505 p_object_version_number := l_object_version_number;
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 40);
508 --
509 exception
510 --
511 when others then
512 --
513 -- A validation or unexpected error has occured
514 --
515 p_pl_bnf_id := null;
516 p_effective_start_date := null;
517 p_effective_end_date := null;
518 p_object_version_number := null;
519 fnd_msg_pub.add;
520 --
521 End CREATE_PLAN_BENEFICIARY_w;
522 --
523 -- ----------------------------------------------------------------------------
524 -- |------------------------< update_PLAN_BENEFICIARY >--- ------------------|
525 -- ----------------------------------------------------------------------------
526 --
527 procedure update_PLAN_BENEFICIARY
528 (p_validate in boolean default false
529 ,p_pl_bnf_id in number
530 ,p_effective_start_date out nocopy date
531 ,p_effective_end_date out nocopy date
532 ,p_business_group_id in number default hr_api.g_number
533 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
534 ,p_bnf_person_id in number default hr_api.g_number
535 ,p_organization_id in number default hr_api.g_number
536 ,p_ttee_person_id in number default hr_api.g_number
537 ,p_prmry_cntngnt_cd in varchar2 default hr_api.g_varchar2
538 ,p_pct_dsgd_num in number default hr_api.g_number
539 ,p_amt_dsgd_val in number default hr_api.g_number
540 ,p_amt_dsgd_uom in varchar2 default hr_api.g_varchar2
541 ,p_dsgn_strt_dt in date default hr_api.g_date
542 ,p_dsgn_thru_dt in date default hr_api.g_date
543 ,p_addl_instrn_txt in varchar2 default hr_api.g_varchar2
544 ,p_pbn_attribute_category in varchar2 default hr_api.g_varchar2
545 ,p_pbn_attribute1 in varchar2 default hr_api.g_varchar2
546 ,p_pbn_attribute2 in varchar2 default hr_api.g_varchar2
547 ,p_pbn_attribute3 in varchar2 default hr_api.g_varchar2
548 ,p_pbn_attribute4 in varchar2 default hr_api.g_varchar2
549 ,p_pbn_attribute5 in varchar2 default hr_api.g_varchar2
550 ,p_pbn_attribute6 in varchar2 default hr_api.g_varchar2
551 ,p_pbn_attribute7 in varchar2 default hr_api.g_varchar2
552 ,p_pbn_attribute8 in varchar2 default hr_api.g_varchar2
553 ,p_pbn_attribute9 in varchar2 default hr_api.g_varchar2
554 ,p_pbn_attribute10 in varchar2 default hr_api.g_varchar2
555 ,p_pbn_attribute11 in varchar2 default hr_api.g_varchar2
556 ,p_pbn_attribute12 in varchar2 default hr_api.g_varchar2
557 ,p_pbn_attribute13 in varchar2 default hr_api.g_varchar2
558 ,p_pbn_attribute14 in varchar2 default hr_api.g_varchar2
559 ,p_pbn_attribute15 in varchar2 default hr_api.g_varchar2
560 ,p_pbn_attribute16 in varchar2 default hr_api.g_varchar2
561 ,p_pbn_attribute17 in varchar2 default hr_api.g_varchar2
562 ,p_pbn_attribute18 in varchar2 default hr_api.g_varchar2
563 ,p_pbn_attribute19 in varchar2 default hr_api.g_varchar2
564 ,p_pbn_attribute20 in varchar2 default hr_api.g_varchar2
565 ,p_pbn_attribute21 in varchar2 default hr_api.g_varchar2
566 ,p_pbn_attribute22 in varchar2 default hr_api.g_varchar2
567 ,p_pbn_attribute23 in varchar2 default hr_api.g_varchar2
568 ,p_pbn_attribute24 in varchar2 default hr_api.g_varchar2
569 ,p_pbn_attribute25 in varchar2 default hr_api.g_varchar2
570 ,p_pbn_attribute26 in varchar2 default hr_api.g_varchar2
571 ,p_pbn_attribute27 in varchar2 default hr_api.g_varchar2
572 ,p_pbn_attribute28 in varchar2 default hr_api.g_varchar2
573 ,p_pbn_attribute29 in varchar2 default hr_api.g_varchar2
574 ,p_pbn_attribute30 in varchar2 default hr_api.g_varchar2
575 ,p_request_id in number default hr_api.g_number
576 ,p_program_application_id in number default hr_api.g_number
577 ,p_program_id in number default hr_api.g_number
578 ,p_program_update_date in date default hr_api.g_date
579 ,p_object_version_number in out nocopy number
580 ,p_per_in_ler_id in number default hr_api.g_number
581 ,p_effective_date in date
582 ,p_datetrack_mode in varchar2
583 ,p_multi_row_actn in boolean default TRUE
584 ) is
585 --
586 -- Declare cursors and local variables
587 --
588 --
589 l_proc varchar2(72) := g_package||'update_PLAN_BENEFICIARY';
590 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
591 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
592 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
593 l2_datetrack_mode varchar2(30);
594 l_rslt_object_version_number number;
595 l_actn_typ_id number;
596 l_prtt_enrt_actn_id number;
597 l_cmpltd_dt date;
598 --
599 begin
600 --
601 hr_utility.set_location('Entering:'|| l_proc, 10);
602 --
603 if fnd_global.conc_request_id = -1 then
604 --
605 ben_env_object.init(p_business_group_id => p_business_group_id,
606 p_effective_date => p_effective_date,
607 p_thread_id => 1,
608 p_chunk_size => 1,
609 p_threads => 1,
610 p_max_errors => 1,
611 p_benefit_action_id => null);
612 --
613 end if;
614 --
615 -- Issue a savepoint if operating in validation only mode
616 --
617 savepoint update_PLAN_BENEFICIARY;
618 --
619 hr_utility.set_location(l_proc, 20);
620 --
621 -- Process Logic
622 --
623 l_object_version_number := p_object_version_number;
624 --
625 --
626 begin
627 --
628 -- Start of API User Hook for the before hook of update_PLAN_BENEFICIARY
629 --
630 ben_PLAN_BENEFICIARY_bk2.update_PLAN_BENEFICIARY_b
631 (
632 p_pl_bnf_id => p_pl_bnf_id
633 ,p_business_group_id => p_business_group_id
634 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
635 ,p_bnf_person_id => p_bnf_person_id
636 ,p_organization_id => p_organization_id
637 ,p_ttee_person_id => p_ttee_person_id
638 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
639 ,p_pct_dsgd_num => p_pct_dsgd_num
640 ,p_amt_dsgd_val => p_amt_dsgd_val
641 ,p_amt_dsgd_uom => p_amt_dsgd_uom
642 ,p_dsgn_strt_dt => p_dsgn_strt_dt
643 ,p_dsgn_thru_dt => p_dsgn_thru_dt
644 ,p_addl_instrn_txt => p_addl_instrn_txt
645 ,p_pbn_attribute_category => p_pbn_attribute_category
646 ,p_pbn_attribute1 => p_pbn_attribute1
647 ,p_pbn_attribute2 => p_pbn_attribute2
648 ,p_pbn_attribute3 => p_pbn_attribute3
649 ,p_pbn_attribute4 => p_pbn_attribute4
650 ,p_pbn_attribute5 => p_pbn_attribute5
651 ,p_pbn_attribute6 => p_pbn_attribute6
652 ,p_pbn_attribute7 => p_pbn_attribute7
653 ,p_pbn_attribute8 => p_pbn_attribute8
654 ,p_pbn_attribute9 => p_pbn_attribute9
655 ,p_pbn_attribute10 => p_pbn_attribute10
656 ,p_pbn_attribute11 => p_pbn_attribute11
657 ,p_pbn_attribute12 => p_pbn_attribute12
658 ,p_pbn_attribute13 => p_pbn_attribute13
659 ,p_pbn_attribute14 => p_pbn_attribute14
660 ,p_pbn_attribute15 => p_pbn_attribute15
661 ,p_pbn_attribute16 => p_pbn_attribute16
662 ,p_pbn_attribute17 => p_pbn_attribute17
663 ,p_pbn_attribute18 => p_pbn_attribute18
664 ,p_pbn_attribute19 => p_pbn_attribute19
665 ,p_pbn_attribute20 => p_pbn_attribute20
666 ,p_pbn_attribute21 => p_pbn_attribute21
667 ,p_pbn_attribute22 => p_pbn_attribute22
668 ,p_pbn_attribute23 => p_pbn_attribute23
669 ,p_pbn_attribute24 => p_pbn_attribute24
670 ,p_pbn_attribute25 => p_pbn_attribute25
671 ,p_pbn_attribute26 => p_pbn_attribute26
672 ,p_pbn_attribute27 => p_pbn_attribute27
673 ,p_pbn_attribute28 => p_pbn_attribute28
674 ,p_pbn_attribute29 => p_pbn_attribute29
675 ,p_pbn_attribute30 => p_pbn_attribute30
676 ,p_request_id => p_request_id
677 ,p_program_application_id => p_program_application_id
678 ,p_program_id => p_program_id
679 ,p_program_update_date => p_program_update_date
680 ,p_object_version_number => p_object_version_number
681 ,p_per_in_ler_id => p_per_in_ler_id
682 ,p_effective_date => trunc(p_effective_date)
683 ,p_datetrack_mode => p_datetrack_mode
684 );
685 exception
686 when hr_api.cannot_find_prog_unit then
687 hr_api.cannot_find_prog_unit_error
688 (p_module_name => 'UPDATE_PLAN_BENEFICIARY'
689 ,p_hook_type => 'BP'
690 );
691 --
692 -- End of API User Hook for the before hook of update_PLAN_BENEFICIARY
693 --
694 end;
695 --
696 ben_pbn_upd.upd
697 (
698 p_pl_bnf_id => p_pl_bnf_id
699 ,p_effective_start_date => l_effective_start_date
700 ,p_effective_end_date => l_effective_end_date
701 ,p_business_group_id => p_business_group_id
702 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
703 ,p_bnf_person_id => p_bnf_person_id
704 ,p_organization_id => p_organization_id
705 ,p_ttee_person_id => p_ttee_person_id
706 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
707 ,p_pct_dsgd_num => p_pct_dsgd_num
708 ,p_amt_dsgd_val => p_amt_dsgd_val
709 ,p_amt_dsgd_uom => p_amt_dsgd_uom
710 ,p_dsgn_strt_dt => p_dsgn_strt_dt
711 ,p_dsgn_thru_dt => p_dsgn_thru_dt
712 ,p_addl_instrn_txt => p_addl_instrn_txt
713 ,p_pbn_attribute_category => p_pbn_attribute_category
714 ,p_pbn_attribute1 => p_pbn_attribute1
715 ,p_pbn_attribute2 => p_pbn_attribute2
716 ,p_pbn_attribute3 => p_pbn_attribute3
717 ,p_pbn_attribute4 => p_pbn_attribute4
718 ,p_pbn_attribute5 => p_pbn_attribute5
719 ,p_pbn_attribute6 => p_pbn_attribute6
720 ,p_pbn_attribute7 => p_pbn_attribute7
721 ,p_pbn_attribute8 => p_pbn_attribute8
722 ,p_pbn_attribute9 => p_pbn_attribute9
723 ,p_pbn_attribute10 => p_pbn_attribute10
724 ,p_pbn_attribute11 => p_pbn_attribute11
725 ,p_pbn_attribute12 => p_pbn_attribute12
726 ,p_pbn_attribute13 => p_pbn_attribute13
727 ,p_pbn_attribute14 => p_pbn_attribute14
728 ,p_pbn_attribute15 => p_pbn_attribute15
729 ,p_pbn_attribute16 => p_pbn_attribute16
730 ,p_pbn_attribute17 => p_pbn_attribute17
731 ,p_pbn_attribute18 => p_pbn_attribute18
732 ,p_pbn_attribute19 => p_pbn_attribute19
733 ,p_pbn_attribute20 => p_pbn_attribute20
734 ,p_pbn_attribute21 => p_pbn_attribute21
735 ,p_pbn_attribute22 => p_pbn_attribute22
736 ,p_pbn_attribute23 => p_pbn_attribute23
737 ,p_pbn_attribute24 => p_pbn_attribute24
738 ,p_pbn_attribute25 => p_pbn_attribute25
739 ,p_pbn_attribute26 => p_pbn_attribute26
740 ,p_pbn_attribute27 => p_pbn_attribute27
741 ,p_pbn_attribute28 => p_pbn_attribute28
742 ,p_pbn_attribute29 => p_pbn_attribute29
743 ,p_pbn_attribute30 => p_pbn_attribute30
744 ,p_request_id => p_request_id
745 ,p_program_application_id => p_program_application_id
746 ,p_program_id => p_program_id
747 ,p_program_update_date => p_program_update_date
748 ,p_object_version_number => l_object_version_number
749 ,p_per_in_ler_id => p_per_in_ler_id
750 ,p_effective_date => trunc(p_effective_date)
751 ,p_datetrack_mode => p_datetrack_mode
752 );
753 --
754 --bug#3976575 - remove action item for spousal certification if any when contingent
755 if p_prmry_cntngnt_cd = 'CNTNGNT' then
756 --
757 l_actn_typ_id := ben_enrollment_action_items.get_actn_typ_id
758 (p_type_cd => 'BNFSCCTFN'
759 ,p_business_group_id => p_business_group_id);
760 --
761 ben_enrollment_action_items.get_prtt_enrt_actn_id
762 (p_actn_typ_id => l_actn_typ_id
763 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
764 ,p_pl_bnf_id => p_pl_bnf_id
765 ,p_effective_date => p_effective_date
766 ,p_business_group_id => p_business_group_id
767 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
768 ,p_cmpltd_dt => l_cmpltd_dt
769 ,p_object_version_number => l_object_version_number);
770 --
771 if l_prtt_enrt_actn_id is not null then
772 --
773 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
774 (p_validate => p_validate,
775 p_effective_date => p_effective_date,
776 p_business_group_id => p_business_group_id,
777 p_datetrack_mode => hr_api.g_zap,
778 p_object_version_number => l_object_version_number,
779 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
780 p_rslt_object_version_number => l_rslt_object_version_number,
781 p_unsuspend_enrt_flag => 'N',
782 p_effective_start_date => l_effective_start_date,
783 p_effective_end_date => l_effective_end_date,
784 p_prtt_enrt_actn_id => l_prtt_enrt_actn_id);
785 --
786 end if;
787 --
788 end if;
789
790 -----tilak
791
792 -- If not covered, call delete useage
793 --
794 if not(p_dsgn_strt_dt is not null and p_dsgn_thru_dt = hr_api.g_eot) then
795 --
796 --
797 if p_datetrack_mode = 'UPDATE' then
798 l2_datetrack_mode := 'DELETE';
799 else
800 l2_datetrack_mode := 'ZAP';
801 --
802 end if;
803 --
804 hr_utility.set_location('through ' || p_dsgn_thru_dt ,99);
805 hr_utility.set_location('mode ' || l2_datetrack_mode ,99);
806 remove_usage(
807 p_validate => p_validate
808 ,p_pl_bnf_id => p_pl_bnf_id
809 ,p_effective_date => p_effective_date
810 ,p_datetrack_mode => l2_datetrack_mode
811 ,p_business_group_id => p_business_group_id
812 -- 5668052
813 ,p_dsgn_thru_dt => p_dsgn_strt_dt
814 );
815
816 --
817
818 end if ;
819
820 --
821 -- Call Action item RCO if p_multi_row_actn = TRUE
822 --
823 if p_multi_row_actn then
824 --
825 bnf_actn_items(
826 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
827 ,p_pl_bnf_id => p_pl_bnf_id
828 ,p_effective_date => p_effective_date
829 ,p_business_group_id => p_business_group_id
830 ,p_validate => p_validate
831 ,p_datetrack_mode => p_datetrack_mode
832 );
833 --
834 end if;
835 --
836 --
837 begin
838 --
839 -- Start of API User Hook for the after hook of update_PLAN_BENEFICIARY
840 --
841 ben_PLAN_BENEFICIARY_bk2.update_PLAN_BENEFICIARY_a
842 (
843 p_pl_bnf_id => p_pl_bnf_id
844 ,p_effective_start_date => l_effective_start_date
845 ,p_effective_end_date => l_effective_end_date
846 ,p_business_group_id => p_business_group_id
847 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
848 ,p_bnf_person_id => p_bnf_person_id
849 ,p_organization_id => p_organization_id
850 ,p_ttee_person_id => p_ttee_person_id
851 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
852 ,p_pct_dsgd_num => p_pct_dsgd_num
853 ,p_amt_dsgd_val => p_amt_dsgd_val
854 ,p_amt_dsgd_uom => p_amt_dsgd_uom
855 ,p_dsgn_strt_dt => p_dsgn_strt_dt
856 ,p_dsgn_thru_dt => p_dsgn_thru_dt
857 ,p_addl_instrn_txt => p_addl_instrn_txt
858 ,p_pbn_attribute_category => p_pbn_attribute_category
859 ,p_pbn_attribute1 => p_pbn_attribute1
860 ,p_pbn_attribute2 => p_pbn_attribute2
861 ,p_pbn_attribute3 => p_pbn_attribute3
862 ,p_pbn_attribute4 => p_pbn_attribute4
863 ,p_pbn_attribute5 => p_pbn_attribute5
864 ,p_pbn_attribute6 => p_pbn_attribute6
865 ,p_pbn_attribute7 => p_pbn_attribute7
866 ,p_pbn_attribute8 => p_pbn_attribute8
867 ,p_pbn_attribute9 => p_pbn_attribute9
868 ,p_pbn_attribute10 => p_pbn_attribute10
869 ,p_pbn_attribute11 => p_pbn_attribute11
870 ,p_pbn_attribute12 => p_pbn_attribute12
871 ,p_pbn_attribute13 => p_pbn_attribute13
872 ,p_pbn_attribute14 => p_pbn_attribute14
873 ,p_pbn_attribute15 => p_pbn_attribute15
874 ,p_pbn_attribute16 => p_pbn_attribute16
875 ,p_pbn_attribute17 => p_pbn_attribute17
876 ,p_pbn_attribute18 => p_pbn_attribute18
877 ,p_pbn_attribute19 => p_pbn_attribute19
878 ,p_pbn_attribute20 => p_pbn_attribute20
879 ,p_pbn_attribute21 => p_pbn_attribute21
880 ,p_pbn_attribute22 => p_pbn_attribute22
881 ,p_pbn_attribute23 => p_pbn_attribute23
882 ,p_pbn_attribute24 => p_pbn_attribute24
883 ,p_pbn_attribute25 => p_pbn_attribute25
884 ,p_pbn_attribute26 => p_pbn_attribute26
885 ,p_pbn_attribute27 => p_pbn_attribute27
886 ,p_pbn_attribute28 => p_pbn_attribute28
887 ,p_pbn_attribute29 => p_pbn_attribute29
888 ,p_pbn_attribute30 => p_pbn_attribute30
889 ,p_request_id => p_request_id
890 ,p_program_application_id => p_program_application_id
891 ,p_program_id => p_program_id
892 ,p_program_update_date => p_program_update_date
893 ,p_object_version_number => l_object_version_number
894 ,p_per_in_ler_id => p_per_in_ler_id
895 ,p_effective_date => trunc(p_effective_date)
896 ,p_datetrack_mode => p_datetrack_mode
897 );
898 exception
899 when hr_api.cannot_find_prog_unit then
900 hr_api.cannot_find_prog_unit_error
901 (p_module_name => 'UPDATE_PLAN_BENEFICIARY'
902 ,p_hook_type => 'AP'
903 );
904 --
905 -- End of API User Hook for the after hook of update_PLAN_BENEFICIARY
906 --
907 end;
908 --
909 hr_utility.set_location(l_proc, 60);
910 --
911 -- When in validation only mode raise the Validate_Enabled exception
912 --
913 if p_validate then
914 raise hr_api.validate_enabled;
915 end if;
916 --
917 -- Set all output arguments
918 --
919 p_object_version_number := l_object_version_number;
920 p_effective_start_date := l_effective_start_date;
921 p_effective_end_date := l_effective_end_date;
922 --
923 hr_utility.set_location(' Leaving:'||l_proc, 70);
924 --
925 exception
926 --
927 when hr_api.validate_enabled then
928 --
929 -- As the Validate_Enabled exception has been raised
930 -- we must rollback to the savepoint
931 --
932 ROLLBACK TO update_PLAN_BENEFICIARY;
933 --
934 -- Only set output warning arguments
935 -- (Any key or derived arguments must be set to null
936 -- when validation only mode is being used.)
937 --
938 p_effective_start_date := null;
939 p_effective_end_date := null;
940 hr_utility.set_location(' Leaving:'||l_proc, 80);
941 --
942 when others then
943 --
944 -- A validation or unexpected error has occured
945 --
946 ROLLBACK TO update_PLAN_BENEFICIARY;
947 p_effective_start_date := null;
948 p_effective_end_date := null;
949 raise;
950 --
951 end update_PLAN_BENEFICIARY;
952 --
953 -- ----------------------------------------------------------------------------
954 -- |----------------------< update_plan_beneficiary_w >-----------------------|
955 -- ----------------------------------------------------------------------------
956 --
957 procedure update_plan_beneficiary_w
958 (
959 p_validate in varchar2
960 ,p_pl_bnf_id in number
961 ,p_effective_start_date out nocopy date
962 ,p_effective_end_date out nocopy date
963 ,p_business_group_id in number
964 ,p_prtt_enrt_rslt_id in number
965 ,p_bnf_person_id in number default hr_api.g_number
966 ,p_organization_id in number default hr_api.g_number
967 ,p_prmry_cntngnt_cd in varchar2
968 ,p_pct_dsgd_num in number
969 ,p_dsgn_strt_dt in date
970 ,p_dsgn_thru_dt in date
971 ,p_object_version_number in out nocopy number
972 ,p_per_in_ler_id in number
973 ,p_effective_date in date
974 ,p_datetrack_mode in varchar2
975 ,p_multi_row_actn in varchar2
976 )
977 is
978
979 l_proc varchar2(72) := g_package||'update_plan_beneficiary - wrapper';
980
981 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
982 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
983 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
984
985 l_validate BOOLEAN;
986 l_multi_row_actn BOOLEAN;
987 --
988 --
989 begin
990 --
991 hr_utility.set_location('Entering:'|| l_proc, 10);
992 --
993 if fnd_global.conc_request_id in (0,-1) then
994 --
995 ben_env_object.init(p_business_group_id => p_business_group_id,
996 p_effective_date => p_effective_date,
997 p_thread_id => 1,
998 p_chunk_size => 1,
999 p_threads => 1,
1000 p_max_errors => 1,
1001 p_benefit_action_id => null);
1002 --
1003 end if;
1004 --
1005 hr_utility.set_location(l_proc, 20);
1006 --
1007 --
1008 if upper(p_validate) = 'TRUE'
1009 then
1010 l_validate := TRUE;
1011 else
1012 l_validate := FALSE;
1013 end if;
1014 --
1015 if upper(p_multi_row_actn) = 'TRUE'
1016 then
1017 l_multi_row_actn := TRUE;
1018 else
1019 l_multi_row_actn := FALSE;
1020 end if;
1021 --
1022 l_object_version_number := p_object_version_number;
1023 --
1024 update_plan_beneficiary
1025 (
1026 p_validate => l_validate
1027 ,p_pl_bnf_id => p_pl_bnf_id
1028 ,p_effective_start_date => l_effective_start_date
1029 ,p_effective_end_date => l_effective_end_date
1030 ,p_business_group_id => p_business_group_id
1031 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1032 ,p_bnf_person_id => p_bnf_person_id
1033 ,p_organization_id => p_organization_id
1034 ,p_prmry_cntngnt_cd => p_prmry_cntngnt_cd
1035 ,p_pct_dsgd_num => p_pct_dsgd_num
1036 ,p_amt_dsgd_val => null
1037 ,p_amt_dsgd_uom => null
1038 ,p_dsgn_strt_dt => p_dsgn_strt_dt
1039 ,p_dsgn_thru_dt => p_dsgn_thru_dt
1040 ,p_object_version_number => l_object_version_number
1041 ,p_per_in_ler_id => p_per_in_ler_id
1042 ,p_effective_date => p_effective_date
1043 ,p_datetrack_mode => p_datetrack_mode
1044 ,p_multi_row_actn => l_multi_row_actn
1045 );
1046 --
1047 hr_utility.set_location(l_proc, 30);
1048 --
1049 -- Set all output arguments
1050 --
1051 p_effective_start_date := l_effective_start_date;
1052 p_effective_end_date := l_effective_end_date;
1053 p_object_version_number := l_object_version_number;
1054 --
1055 hr_utility.set_location(' Leaving:'||l_proc, 40);
1056 --
1057 exception
1058 --
1059 when others then
1060 --
1061 -- A validation or unexpected error has occured
1062 --
1063 p_effective_start_date := null;
1064 p_effective_end_date := null;
1065 fnd_msg_pub.add;
1066 --
1067 end update_plan_beneficiary_w;
1068 --
1069 -- ----------------------------------------------------------------------------
1070 -- |------------------------< delete_PLAN_BENEFICIARY >----------------------|
1071 -- ----------------------------------------------------------------------------
1072 --
1073 procedure delete_PLAN_BENEFICIARY
1074 (p_validate in boolean default false
1075 ,p_pl_bnf_id in number
1076 ,p_effective_start_date out nocopy date
1077 ,p_effective_end_date out nocopy date
1078 ,p_object_version_number in out nocopy number
1079 ,p_business_group_id in number
1080 ,p_effective_date in date
1081 ,p_datetrack_mode in varchar2
1082 ,p_multi_row_actn in boolean default TRUE
1083 ) is
1084 --
1085 -- Declare cursors and local variables
1086 --
1087 l_proc varchar2(72) := g_package||'DELETE_PLAN_BENEFICIARY';
1088 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
1089 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
1090 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
1091 --
1092 l_pl_bnf_ctfn_prvdd_id number;
1093 l2_object_version_number ben_pl_bnf_ctfn_prvdd_f.object_version_number%TYPE;
1094 l2_effective_start_date ben_pl_bnf_ctfn_prvdd_f.effective_start_date%TYPE;
1095 l2_effective_end_date ben_pl_bnf_ctfn_prvdd_f.effective_end_date%TYPE;
1096 --mactec
1097 l_effective_date date;
1098 l_datetrack_mode varchar2(30);
1099 --
1100 cursor c_pl_bnf(p_pl_bnf_id number, p_effective_date date) is
1101 select effective_start_date
1102 from ben_pl_bnf_f pnb
1103 where pnb.pl_bnf_id = p_pl_bnf_id
1104 and p_effective_date between pnb.effective_start_date
1105 and pnb.effective_end_date ;
1106 l_pl_bnf c_pl_bnf%rowtype;
1107 --
1108 cursor bnf_ctfn_c is
1109 select pl_bnf_ctfn_prvdd_id,
1110 object_version_number,
1111 effective_end_date
1112 from ben_pl_bnf_ctfn_prvdd_f
1113 where pl_bnf_id = p_pl_bnf_id
1114 and business_group_id = p_business_group_id
1115 and l_effective_date between effective_start_date
1116 and effective_end_date;
1117 --
1118 cursor c_future_row (p_pl_bnf_ctfn_prvdd_id number,
1119 p_effective_date date) is
1120 select null
1121 from ben_pl_bnf_ctfn_prvdd_f
1122 where pl_bnf_ctfn_prvdd_id = p_pl_bnf_ctfn_prvdd_id
1123 and effective_start_date > p_effective_date;
1124 --
1125 cursor bnf_ctfn_id_zap_c is
1126 select distinct pl_bnf_ctfn_prvdd_id
1127 from ben_pl_bnf_ctfn_prvdd_f
1128 where pl_bnf_id = p_pl_bnf_id;
1129 --
1130 cursor bnf_ctfn_ovn_zap_c(l_pl_bnf_ctfn_prvdd_id in number) is
1131 select max(object_version_number)
1132 from ben_pl_bnf_ctfn_prvdd_f
1133 where pl_bnf_ctfn_prvdd_id = l_pl_bnf_ctfn_prvdd_id;
1134 --
1135 l_dummy varchar2(300);
1136 begin
1137 --
1138 hr_utility.set_location('Entering:'|| l_proc, 10);
1139 hr_utility.set_location('p_effective_date'||p_effective_date,10);
1140 --mactec
1141 open c_pl_bnf(p_pl_bnf_id,p_effective_date);
1142 fetch c_pl_bnf into l_pl_bnf ;
1143 close c_pl_bnf ;
1144 --
1145 if l_pl_bnf.effective_start_date = p_effective_date then
1146 l_datetrack_mode := hr_api.g_zap ;
1147 l_effective_date := p_effective_date ;
1148 elsif p_datetrack_mode = hr_api.g_delete then
1149 l_effective_date := p_effective_date - 1 ;
1150 l_datetrack_mode := p_datetrack_mode ;
1151 else
1152 l_effective_date := p_effective_date ;
1153 l_datetrack_mode := p_datetrack_mode ;
1154 end if;
1155 --
1156 hr_utility.set_location('l_effective_date '||l_effective_date,10);
1157 hr_utility.set_location('l_datetrack_mode '||l_datetrack_mode,10);
1158 --
1159 if fnd_global.conc_request_id = -1 then
1160 --
1161 ben_env_object.init(p_business_group_id => p_business_group_id,
1162 p_effective_date => l_effective_date,
1163 p_thread_id => 1,
1164 p_chunk_size => 1,
1165 p_threads => 1,
1166 p_max_errors => 1,
1167 p_benefit_action_id => null);
1168 --
1169 end if;
1170 --
1171 -- Issue a savepoint if operating in validation only mode
1172 --
1173 savepoint delete_PLAN_BENEFICIARY;
1174 --
1175 hr_utility.set_location(l_proc, 20);
1176 --
1177 -- Process Logic
1178 --
1179 l_object_version_number := p_object_version_number;
1180 --
1181 --
1182 -- Delete certifications
1183 --
1184 if p_datetrack_mode = hr_api.g_zap then
1185 --
1186 for ctfn_rec in bnf_ctfn_id_zap_c loop
1187 --
1188 open bnf_ctfn_ovn_zap_c(ctfn_rec.pl_bnf_ctfn_prvdd_id);
1189 fetch bnf_ctfn_ovn_zap_c into l2_object_version_number;
1190 close bnf_ctfn_ovn_zap_c;
1191 --
1192 ben_pl_bnf_ctfn_prvdd_api.delete_pl_bnf_ctfn_prvdd
1193 (p_validate => FALSE
1194 ,p_pl_bnf_ctfn_prvdd_id => ctfn_rec.pl_bnf_ctfn_prvdd_id
1195 ,p_effective_start_date => l2_effective_start_date
1196 ,p_effective_end_date => l2_effective_end_date
1197 ,p_object_version_number => l2_object_version_number
1198 ,p_business_group_id => p_business_group_id
1199 ,p_effective_date => l_effective_date
1200 ,p_datetrack_mode => l_datetrack_mode
1201 );
1202 --
1203 end loop;
1204 --
1205 else
1206 --
1207 for ctfn_rec in bnf_ctfn_c loop
1208 --
1209 l2_object_version_number := ctfn_rec.object_version_number;
1210 --
1211 --bug#2564387 - if the record is not end dated don't call delete in future-change mode
1212 --Bug 4064635 we can't delete the cert which is already deleted
1213 if not (p_datetrack_mode = 'FUTURE_CHANGE' and
1214 ctfn_rec.effective_end_date = hr_api.g_eot) and
1215 ctfn_rec.effective_end_date <> l_effective_date then
1216 --
1217 if p_datetrack_mode = 'FUTURE_CHANGE' then
1218 --if there is no future row change the mode to delete
1219 open c_future_row (ctfn_rec.pl_bnf_ctfn_prvdd_id, ctfn_rec.effective_end_date);
1220 fetch c_future_row into l_dummy;
1221 if c_future_row%notfound then
1222 l_datetrack_mode := hr_api.g_delete;
1223 end if;
1224 close c_future_row;
1225 --
1226 end if;
1227 --
1228 ben_pl_bnf_ctfn_prvdd_api.delete_pl_bnf_ctfn_prvdd
1229 (p_validate => FALSE
1230 ,p_pl_bnf_ctfn_prvdd_id => ctfn_rec.pl_bnf_ctfn_prvdd_id
1231 ,p_effective_start_date => l2_effective_start_date
1232 ,p_effective_end_date => l2_effective_end_date
1233 ,p_object_version_number => l2_object_version_number
1234 ,p_business_group_id => p_business_group_id
1235 ,p_effective_date => l_effective_date
1236 ,p_datetrack_mode => l_datetrack_mode
1237 );
1238 --
1239 end if;
1240 end loop;
1241 --
1242 end if;
1243 --
1244 --
1245 begin
1246 --
1247 -- Start of API User Hook for the before hook of delete_PLAN_BENEFICIARY
1248 --
1249 ben_PLAN_BENEFICIARY_bk3.delete_PLAN_BENEFICIARY_b
1250 (
1251 p_pl_bnf_id => p_pl_bnf_id
1252 ,p_object_version_number => p_object_version_number
1253 ,p_effective_date => trunc(l_effective_date)
1254 ,p_datetrack_mode => l_datetrack_mode
1255 );
1256 exception
1257 when hr_api.cannot_find_prog_unit then
1258 hr_api.cannot_find_prog_unit_error
1259 (p_module_name => 'DELETE_PLAN_BENEFICIARY'
1260 ,p_hook_type => 'BP'
1261 );
1262 --
1263 -- End of API User Hook for the before hook of delete_PLAN_BENEFICIARY
1264 --
1265 end;
1266 --
1267 --
1268 /* BUG 4086994. This call should be made after deleting the plan Benificiary
1269 otherwise process will recreate the action items which got deleted above.
1270 -- Call Action item RCO if p_multi_row_actn = TRUE
1271 --
1272 if p_multi_row_actn then
1273 --
1274 bnf_actn_items(
1275 p_prtt_enrt_rslt_id => null
1276 ,p_pl_bnf_id => p_pl_bnf_id
1277 ,p_effective_date => l_effective_date
1278 ,p_business_group_id => null
1279 ,p_validate => p_validate
1280 ,p_datetrack_mode => l_datetrack_mode
1281 ,p_delete_flag => 'Y'
1282 );
1283 --
1284 end if;
1285 --
1286 */
1287 -- remove usage
1288 --
1289 -- in future-change mode and delete_next_change mode the beneficiary will still be
1290 -- continuing - no need to remove the usage
1291 if p_datetrack_mode not in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE') then
1292 --
1293 remove_usage(
1294 p_validate => p_validate
1295 ,p_pl_bnf_id => p_pl_bnf_id
1296 ,p_effective_date => l_effective_date
1297 ,p_datetrack_mode => l_datetrack_mode
1298 ,p_business_group_id => p_business_group_id
1299 -- 5668052
1300 ,p_dsgn_thru_dt => NULL
1301 );
1302 --
1303 end if;
1304 --
1305 ben_pbn_del.del
1306 (
1307 p_pl_bnf_id => p_pl_bnf_id
1308 ,p_effective_start_date => l_effective_start_date
1309 ,p_effective_end_date => l_effective_end_date
1310 ,p_object_version_number => l_object_version_number
1311 ,p_effective_date => l_effective_date
1312 ,p_datetrack_mode => l_datetrack_mode
1313 );
1314 -- 4879576 : moved the code to delete_PLAN_BENEFICIARY_w
1315 -- BUG 4086994
1316 --
1317 /* if p_multi_row_actn then
1318 --
1319 bnf_actn_items(
1320 p_prtt_enrt_rslt_id => null
1321 ,p_pl_bnf_id => p_pl_bnf_id
1322 ,p_effective_date => l_effective_date
1323 ,p_business_group_id => null
1324 ,p_validate => p_validate
1325 ,p_datetrack_mode => l_datetrack_mode
1326 ,p_delete_flag => 'Y'
1327 );
1328 --
1329 end if; */
1330 --
1331 begin
1332 --
1333 -- Start of API User Hook for the after hook of delete_PLAN_BENEFICIARY
1334 --
1335 ben_PLAN_BENEFICIARY_bk3.delete_PLAN_BENEFICIARY_a
1336 (
1337 p_pl_bnf_id => p_pl_bnf_id
1338 ,p_effective_start_date => l_effective_start_date
1339 ,p_effective_end_date => l_effective_end_date
1340 ,p_object_version_number => l_object_version_number
1341 ,p_effective_date => trunc(l_effective_date)
1342 ,p_datetrack_mode => l_datetrack_mode
1343 );
1344 exception
1345 when hr_api.cannot_find_prog_unit then
1346 hr_api.cannot_find_prog_unit_error
1347 (p_module_name => 'DELETE_PLAN_BENEFICIARY'
1348 ,p_hook_type => 'AP'
1349 );
1350 --
1351 -- End of API User Hook for the after hook of delete_PLAN_BENEFICIARY
1352 --
1353 end;
1354 --
1355 hr_utility.set_location(l_proc, 60);
1356 --
1357 -- When in validation only mode raise the Validate_Enabled exception
1358 --
1359 if p_validate then
1360 raise hr_api.validate_enabled;
1361 end if;
1362 --
1363 hr_utility.set_location(' Leaving:'||l_proc, 70);
1364 --
1365 exception
1366 --
1367 when hr_api.validate_enabled then
1368 --
1369 -- As the Validate_Enabled exception has been raised
1370 -- we must rollback to the savepoint
1371 --
1372 ROLLBACK TO delete_PLAN_BENEFICIARY;
1373 --
1374 -- Only set output warning arguments
1375 -- (Any key or derived arguments must be set to null
1376 -- when validation only mode is being used.)
1377 p_effective_start_date := null;
1378 p_effective_end_date := null;
1379 --
1380 --
1381 when others then
1382 --
1383 -- A validation or unexpected error has occured
1384 --
1385 ROLLBACK TO delete_PLAN_BENEFICIARY;
1386 p_effective_start_date := null;
1387 p_effective_end_date := null;
1388 raise;
1389 --
1390 end delete_PLAN_BENEFICIARY;
1391 --
1392 -- ----------------------------------------------------------------------------
1393 -- |------------------------< delete_plan_beneficiary_w >--------------------|
1394 -- ----------------------------------------------------------------------------
1395 --
1396 procedure delete_plan_beneficiary_w
1397 (
1398 p_validate in varchar2
1399 ,p_pl_bnf_id in number
1400 ,p_effective_start_date out nocopy date
1401 ,p_effective_end_date out nocopy date
1402 ,p_object_version_number in out nocopy number
1403 ,p_business_group_id in number
1404 ,p_effective_date in date
1405 ,p_datetrack_mode in varchar2
1406 ,p_prtt_enrt_rslt_id in number
1407 ,p_multi_row_actn in varchar2
1408 )
1409 is
1410
1411 l_proc varchar2(72) := g_package||'delete_plan_beneficiary - wrapper';
1412
1413 l_effective_start_date ben_pl_bnf_f.effective_start_date%TYPE;
1414 l_effective_end_date ben_pl_bnf_f.effective_end_date%TYPE;
1415 l_object_version_number ben_pl_bnf_f.object_version_number%TYPE;
1416
1417 l_validate BOOLEAN;
1418 l_multi_row_actn BOOLEAN;
1419 --
1420 begin
1421 --
1422 hr_utility.set_location('Entering:'|| l_proc, 10);
1423 --
1424 if fnd_global.conc_request_id in (0,-1) then
1425 --
1426 ben_env_object.init(p_business_group_id => p_business_group_id,
1427 p_effective_date => p_effective_date,
1428 p_thread_id => 1,
1429 p_chunk_size => 1,
1430 p_threads => 1,
1431 p_max_errors => 1,
1432 p_benefit_action_id => null);
1433 --
1434 end if;
1435 --
1436 hr_utility.set_location(l_proc, 20);
1437 --
1438 --
1439 if upper(p_validate) = 'TRUE'
1440 then
1441 l_validate := TRUE;
1442 else
1443 l_validate := FALSE;
1444 end if;
1445 --
1446 --4879576 : uncommented following code
1447 if upper(p_multi_row_actn) = 'TRUE'
1448 then
1449 l_multi_row_actn := TRUE;
1450 else
1451 l_multi_row_actn := FALSE;
1452 end if;
1453
1454
1455 --BUG 4086994
1456 --This is always FALSE from PUI
1457 --We don't need multi row action for DELETE
1458 -- l_multi_row_actn := FALSE; -- 4879576 : commented
1459 --
1460 l_object_version_number := p_object_version_number;
1461 --
1462 delete_plan_beneficiary
1463 (
1464 p_validate => l_validate
1465 ,p_pl_bnf_id => p_pl_bnf_id
1466 ,p_effective_start_date => l_effective_start_date
1467 ,p_effective_end_date => l_effective_end_date
1468 ,p_business_group_id => p_business_group_id
1469 ,p_object_version_number => l_object_version_number
1470 ,p_effective_date => p_effective_date
1471 ,p_datetrack_mode => p_datetrack_mode
1472 ,p_multi_row_actn => l_multi_row_actn
1473 );
1474 --
1475 hr_utility.set_location(l_proc, 30);
1476 --
1477 p_effective_start_date := l_effective_start_date;
1478 p_effective_end_date := l_effective_end_date;
1479 p_object_version_number := l_object_version_number;
1480 -- -- 4879576 : moved the bnf_actn_items from delete_plan_beneficiary
1481 -- and passed correct values to the params.
1482 if l_multi_row_actn then
1483
1484 bnf_actn_items(
1485 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1486 ,p_pl_bnf_id => null
1487 ,p_effective_date => p_effective_date
1488 ,p_business_group_id => p_business_group_id
1489 ,p_validate => l_validate
1490 ,p_datetrack_mode => p_datetrack_mode
1491 ,p_delete_flag => 'N'
1492 );
1493
1494 --
1495 end if;
1496 hr_utility.set_location(' Leaving:'||l_proc, 40);
1497 --
1498 exception
1499 --
1500 when others then
1501 --
1502 -- A validation or unexpected error has occured
1503 --
1504 p_effective_start_date := null;
1505 p_effective_end_date := null;
1506 fnd_msg_pub.add;
1507 --
1508 end delete_plan_beneficiary_w;
1509 --
1510 -- ----------------------------------------------------------------------------
1511 -- |-------------------------------< lck >------------------------------------|
1512 -- ----------------------------------------------------------------------------
1513 --
1514 procedure lck
1515 (
1516 p_pl_bnf_id in number
1517 ,p_object_version_number in number
1518 ,p_effective_date in date
1519 ,p_datetrack_mode in varchar2
1520 ,p_validation_start_date out nocopy date
1521 ,p_validation_end_date out nocopy date
1522 ) is
1523 --
1524 --
1525 -- Declare cursors and local variables
1526 --
1527 l_proc varchar2(72) := g_package||'lck';
1528 l_validation_start_date date;
1529 l_validation_end_date date;
1530 --
1531 begin
1532 --
1533 hr_utility.set_location('Entering:'|| l_proc, 10);
1534 --
1535 ben_pbn_shd.lck
1536 (
1537 p_pl_bnf_id => p_pl_bnf_id
1538 ,p_validation_start_date => l_validation_start_date
1539 ,p_validation_end_date => l_validation_end_date
1540 ,p_object_version_number => p_object_version_number
1541 ,p_effective_date => p_effective_date
1542 ,p_datetrack_mode => p_datetrack_mode
1543 );
1544 --
1545 hr_utility.set_location(' Leaving:'||l_proc, 70);
1546 --
1547 end lck;
1548 --
1549 -- ----------------------------------------------------------------------------
1550 -- |-------------------------------< bnf_actn_items >--------------------------|
1551 -- ----------------------------------------------------------------------------
1552 --
1553 procedure bnf_actn_items
1554 (
1555 p_prtt_enrt_rslt_id in number
1556 ,p_pl_bnf_id in number
1557 ,p_effective_date in date
1558 ,p_business_group_id in number
1559 ,p_validate in boolean default FALSE
1560 ,p_datetrack_mode in varchar2
1561 ,p_delete_flag in varchar2 default 'N'
1562 ) is
1563 --
1564 l_proc varchar2(72) := g_package||'bnf_actn_items';
1565 l_prtt_enrt_rslt_id number(15);
1566 l_business_group_id number(15);
1567 l_suspend_flag varchar2(30);
1568 l_rslt_object_version_number number(9);
1569 l_bnf_actn_warning boolean;
1570 l_bnft_amt number;
1571 l_prmry_dsgd_pct number := 0;
1572 l_prmry_dsgd_amt number := 0;
1573 l_cntngnt_dsgd_pct number := 0;
1574 l_cntngnt_dsgd_amt number := 0;
1575 l_total_prmry_pct number;
1576 l_total_prmry_amt number;
1577 l_total_cntngnt_pct number;
1578 l_total_cntngnt_amt number;
1579 --
1580 cursor get_rslt_id_c is
1581 select prtt_enrt_rslt_id,
1582 business_group_id,
1583 decode(prmry_cntngnt_cd, 'PRIMY',pct_dsgd_num,0) prmry_dsgd_pct,
1584 decode(prmry_cntngnt_cd, 'CNTNGNT',pct_dsgd_num,0) cntngnt_dsgd_pct,
1585 decode(prmry_cntngnt_cd, 'PRIMY',amt_dsgd_val,0) prmry_dsgd_amt,
1586 decode(prmry_cntngnt_cd, 'CNTNGNT',amt_dsgd_val,0) cntngnt_dsgd_amt
1587 from ben_pl_bnf_f
1588 where pl_bnf_id = p_pl_bnf_id
1589 and p_effective_date between effective_start_date
1590 and effective_end_date;
1591 --
1592 cursor get_rslt_ovn_c is
1593 select object_version_number,
1594 sspndd_flag,
1595 bnft_amt
1596 from ben_prtt_enrt_rslt_f
1597 where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1598 and business_group_id = l_business_group_id
1599 and prtt_enrt_rslt_stat_cd is null
1600 and p_effective_date
1601 between effective_start_date and effective_end_date;
1602 --
1603 cursor c_sum_bnf is
1604 select pbn.prmry_cntngnt_cd prmry_cntngnt_cd,
1605 sum(pbn.pct_dsgd_num) prcnt,
1606 sum(pbn.amt_dsgd_val) amount
1607 from ben_pl_bnf_f pbn,
1608 ben_per_in_ler pil
1609 where pbn.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1610 and pbn.business_group_id = l_business_group_id
1611 -- and p_effective_date between pbn.effective_start_date and pbn.effective_end_date
1612 and p_effective_date >= pbn.effective_start_date
1613 and p_effective_date < pbn.effective_end_date
1614 and pil.per_in_ler_id=pbn.per_in_ler_id
1615 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1616 group by pbn.prmry_cntngnt_cd;
1617 --
1618 begin
1619 --
1620 hr_utility.set_location('Entering:'|| l_proc, 10);
1621 --
1622 if p_prtt_enrt_rslt_id is null or
1623 p_business_group_id is null or
1624 p_delete_flag = 'Y' then
1625 --
1626 open get_rslt_id_c;
1627 fetch get_rslt_id_c into l_prtt_enrt_rslt_id,
1628 l_business_group_id,
1629 l_prmry_dsgd_pct,
1630 l_cntngnt_dsgd_pct,
1631 l_prmry_dsgd_amt,
1632 l_cntngnt_dsgd_amt;
1633 close get_rslt_id_c;
1634 --
1635 if p_delete_flag = 'N' then
1636 l_prmry_dsgd_pct := 0;
1637 l_cntngnt_dsgd_pct := 0;
1638 l_prmry_dsgd_amt := 0;
1639 l_cntngnt_dsgd_amt := 0;
1640 end if;
1641 --
1642 else
1643 l_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1644 l_business_group_id := p_business_group_id;
1645 end if;
1646 --
1647 open get_rslt_ovn_c;
1648 fetch get_rslt_ovn_c into l_rslt_object_version_number,
1649 l_suspend_flag,
1650 l_bnft_amt;
1651 close get_rslt_ovn_c;
1652 --
1653 for l_sum_bnf in c_sum_bnf loop
1654 --
1655 if l_sum_bnf.prmry_cntngnt_cd = 'PRIMY' then
1656 --
1657 l_total_prmry_pct := l_sum_bnf.prcnt - l_prmry_dsgd_pct;
1658 l_total_prmry_amt := l_sum_bnf.amount - l_prmry_dsgd_amt;
1659 --
1660 elsif l_sum_bnf.prmry_cntngnt_cd = 'CNTNGNT' then
1661 --
1662 l_total_cntngnt_pct := l_sum_bnf.prcnt - l_cntngnt_dsgd_pct;
1663 l_total_cntngnt_amt := l_sum_bnf.amount - l_cntngnt_dsgd_amt;
1664 --
1665 end if;
1666 --
1667 end loop;
1668 --
1669 if l_total_prmry_pct > 100 or l_total_cntngnt_pct > 100 then
1670 --
1671 fnd_message.set_name('BEN', 'BEN_91644_BNF_TTL_PCT_EXCEEDED');
1672 fnd_message.raise_error;
1673 --
1674 end if;
1675 -- start - bug 2317471
1676 if (l_total_prmry_pct < 100 and nvl(l_total_prmry_pct,0) > 0 ) or
1677 ( l_total_cntngnt_pct < 100 and nvl(l_total_cntngnt_pct,0) > 0)
1678 then
1679 --
1680 fnd_message.set_name('BEN', 'BEN_93122_PCT_LESS_HUND');
1681 fnd_message.raise_error;
1682 --
1683 end if;
1684 -- end - bug 2317471
1685 if l_total_prmry_amt > l_bnft_amt or l_total_cntngnt_amt > l_bnft_amt then
1686 --
1687 fnd_message.set_name('BEN', 'BEN_91645_BNF_TTL_AMT_EXCEEDED');
1688 fnd_message.raise_error;
1689 --
1690 end if;
1691 --
1692 -- Contingent beneficiary cannot be defined without defining a primary
1693 -- beneficiary. (Bug 1368196)
1694 --
1695 if ( (l_total_cntngnt_pct > 0 and nvl(l_total_prmry_pct, 0) = 0)
1696 OR
1697 (l_total_cntngnt_amt > 0 and nvl(l_total_prmry_amt, 0) = 0)) then
1698 --
1699 fnd_message.set_name('BEN', 'BEN_92565_CNTGNT_NO_PRIMY');
1700 fnd_message.raise_error;
1701 --
1702 end if;
1703 --
1704 ben_enrollment_action_items.process_bnf_actn_items(
1705 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1706 ,p_rslt_object_version_number => l_rslt_object_version_number
1707 ,p_effective_date => trunc(p_effective_date)
1708 ,p_business_group_id => l_business_group_id
1709 ,p_validate => FALSE
1710 ,p_datetrack_mode => p_datetrack_mode
1711 ,p_suspend_flag => l_suspend_flag
1712 ,p_bnf_actn_warning => l_bnf_actn_warning
1713 );
1714 --
1715 hr_utility.set_location('Exiting:'|| l_proc, 40);
1716 end bnf_actn_items;
1717 --
1718 --
1719 -- ----------------------------------------------------------------------------
1720 -- |-------------------------------< add_usage >--------------------------|
1721 -- ----------------------------------------------------------------------------
1722 --
1723 procedure add_usage
1724 (
1725 p_validate in boolean default false
1726 ,p_pl_bnf_id in number
1727 ,p_bnf_person_id in number
1728 ,p_prtt_enrt_rslt_id in number
1729 ,p_business_group_id in number
1730 ,p_effective_date in date
1731 ,p_datetrack_mode in varchar2
1732 ) is
1733 --
1734 --
1735 -- Declare cursors and local variables
1736 --
1737 l_proc varchar2(72) := g_package||'add_usage';
1738 --
1739 l_person_type_id number(15);
1740 l_person_type_usage_id number(15);
1741 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1742 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1743 --
1744 l1_person_type_usage_id number(15);
1745 l1_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1746 l1_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1747 --
1748 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1749 l_enrt_cvg_strt_dt date;
1750 l_dsg_strt_dt date;
1751 --
1752 --
1753 cursor get_bnf_type_id_c is
1754 select person_type_id
1755 from per_person_types
1756 where system_person_type = 'BNF'
1757 and business_group_id = p_business_group_id;
1758 --
1759 cursor get_enrt_cvg_strt_dt_c is
1760 select enrt_cvg_strt_dt
1761 from ben_prtt_enrt_rslt_f a
1762 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1763 and a.business_group_id = p_business_group_id
1764 and a.prtt_enrt_rslt_stat_cd is null
1765 and p_effective_date between nvl(a.effective_start_date, p_effective_date)
1766 and nvl(a.effective_end_date, p_effective_date);
1767 --
1768 -- find overlapping ptu segments
1769 --
1770 cursor find_ptu_ovlp_segments_c is
1771 select a.effective_start_date,
1772 a.effective_end_date,
1773 a.person_type_usage_id
1774 from per_person_type_usages_f a
1775 where a.person_id = p_bnf_person_id
1776 and a.person_type_id = l_person_type_id
1777 -- and a.effective_start_date <= hr_api.g_date
1778 and a.effective_end_date >= l_dsg_strt_dt
1779 ;
1780 --
1781 --
1782 begin
1783 --
1784 hr_utility.set_location('Entering:'|| l_proc, 10);
1785 --
1786 l_enrt_cvg_strt_dt := null;
1787 l_dsg_strt_dt := p_effective_date;
1788 --
1789 -- Create person type usage, if needed
1790 --
1791 -- Is this a person?
1792 if p_bnf_person_id is not null then
1793 --
1794 -- get type id
1795 --
1796 hr_utility.set_location('This is a person:'|| l_proc, 15);
1797 -- dbms_output.put_line('This is a person '||to_char(p_bnf_person_id));
1798 --
1799 open get_bnf_type_id_c;
1800 fetch get_bnf_type_id_c into l_person_type_id;
1801 if get_bnf_type_id_c%FOUND then
1802 --
1803 hr_utility.set_location('Got BNF type ID:'|| l_proc, 20);
1804 -- get cvg start dt
1805 open get_enrt_cvg_strt_dt_c;
1806 fetch get_enrt_cvg_strt_dt_c into l_enrt_cvg_strt_dt;
1807 close get_enrt_cvg_strt_dt_c;
1808 --
1809 if l_enrt_cvg_strt_dt is not null then
1810 -- this logic will be changed when
1811 -- dsg_strt_dt column is added to BNF
1812 if p_effective_date < l_enrt_cvg_strt_dt then
1813 l_dsg_strt_dt := l_enrt_cvg_strt_dt;
1814 end if;
1815 --
1816 hr_utility.set_location('Got start date:'|| l_proc, 25);
1817 -- dbms_output.put_line('Strt date is ' || to_char(l_dsg_strt_dt));
1818 --
1819 -- does BNF usage exist as of dsg strt date?
1820 --
1821 open find_ptu_ovlp_segments_c;
1822 fetch find_ptu_ovlp_segments_c into l_effective_start_date,
1823 l_effective_end_date,
1824 l_person_type_usage_id;
1825 if find_ptu_ovlp_segments_c%NOTFOUND then
1826 --
1827 hr_utility.set_location('No overlapping segments:'|| l_proc, 30);
1828 -- dbms_output.put_line('No overlapping segments');
1829 -- call create person_type usage api
1830 --
1831 hr_per_type_usage_internal.create_person_type_usage
1832 (p_validate => FALSE
1833 ,p_person_id => p_bnf_person_id
1834 ,p_person_type_id => l_person_type_id
1835 ,p_effective_date => l_dsg_strt_dt
1836 ,p_person_type_usage_id => l1_person_type_usage_id
1837 ,p_object_version_number => l_object_version_number
1838 ,p_effective_start_date => l1_effective_start_date
1839 ,p_effective_end_date => l1_effective_end_date
1840 );
1841 --
1842 else
1843 if l_effective_start_date <= l_dsg_strt_dt and
1844 l_effective_end_date >= hr_api.g_date
1845 then
1846 null;
1847 elsif l_effective_start_date <= l_dsg_strt_dt and
1848 l_effective_end_date < hr_api.g_date
1849 then
1850 update per_person_type_usages_f
1851 set effective_end_date = hr_api.g_date
1852 where person_type_usage_id = l_person_type_usage_id;
1853 elsif l_effective_start_date > l_dsg_strt_dt and
1854 l_effective_end_date = hr_api.g_date then
1855 --
1856 update per_person_type_usages_f
1857 set effective_start_date = l_dsg_strt_dt
1858 where person_type_usage_id = l_person_type_usage_id;
1859 --
1860 elsif l_effective_start_date > l_dsg_strt_dt and
1861 l_effective_end_date < hr_api.g_date then
1862 --
1863 update per_person_type_usages_f
1864 set effective_start_date = l_dsg_strt_dt,
1865 effective_end_date = hr_api.g_date
1866 where person_type_usage_id = l_person_type_usage_id;
1867 --
1868 end if;
1869 --
1870 close find_ptu_ovlp_segments_c;
1871 --
1872 end if;
1873 --
1874 end if;
1875 --
1876 end if;
1877 --
1878 close get_bnf_type_id_c;
1879 --
1880 end if;
1881 --
1882 hr_utility.set_location(' Leaving:'||l_proc, 70);
1883 --
1884 end add_usage;
1885 --
1886 -- ----------------------------------------------------------------------------
1887 -- |-------------------------------< remove_usage >--------------------------|
1888 -- ----------------------------------------------------------------------------
1889 --
1890 procedure remove_usage
1891 (
1892 p_validate in boolean default false
1893 ,p_pl_bnf_id in number
1894 ,p_effective_date in date
1895 ,p_datetrack_mode in varchar2
1896 ,p_business_group_id in number
1897 -- bug 5668052
1898 ,p_dsgn_thru_dt in date
1899 ) is
1900 --
1901 --
1902 -- Declare cursors and local variables
1903 --
1904 l_proc varchar2(72) := g_package||'remove_usage';
1905 l_effective_start_date date;
1906 l_effective_end_date date;
1907 l_object_version_number number(9);
1908 l_bnf_person_id number(15);
1909 l_business_group_id number(15);
1910 l_exist varchar2(1);
1911 l_person_type_usage_id number(15);
1912 --
1913 cursor bnf_person_c is
1914 select b.bnf_person_id,
1915 b.business_group_id
1916 from ben_pl_bnf_f b
1917 where b.pl_bnf_id = p_pl_bnf_id
1918 and business_group_id = p_business_group_id
1919 and p_effective_date between b.effective_start_date
1920 and b.effective_end_date;
1921 --
1922 cursor other_bnf_c is
1923 select null
1924 from ben_pl_bnf_f a
1925 where a.bnf_person_id = l_bnf_person_id
1926 and a.business_group_id = p_business_group_id
1927 and a.pl_bnf_id <> p_pl_bnf_id
1928 -- and p_effective_date between a.effective_start_date
1929 -- and a.effective_end_date;
1930 -- bug 5668052
1931 and p_effective_date < a.effective_end_date;
1932 --
1933 cursor usage_c is
1934 select a.person_type_usage_id,
1935 a.object_version_number,
1936 a.effective_end_date
1937 -- bug 5668052
1938 ,a.effective_start_date
1939 from per_person_type_usages_f a,
1940 per_person_types b
1941 where a.person_id = l_bnf_person_id
1942 and a.person_type_id = b.person_type_id
1943 and b.system_person_type = 'BNF'
1944 and b.business_group_id = l_business_group_id
1945 and p_effective_date between a.effective_start_date
1946 and a.effective_end_date;
1947 --
1948 -- bug 5668052
1949 CURSOR bnf_rec_exists (v_bnf_person_id IN NUMBER)
1950 IS
1951 SELECT pl_bnf_id
1952 FROM ben_pl_bnf_f pbn
1953 WHERE pbn.bnf_person_id = v_bnf_person_id
1954 AND pbn.pl_bnf_id <> p_pl_bnf_id
1955 AND pbn.business_group_id = p_business_group_id;
1956
1957 l_dummy NUMBER;
1958 l_datetrack_mode varchar(30);
1959 l_usg_effective_start_date date;
1960 --
1961 begin
1962 --
1963 hr_utility.set_location('Entering'||l_proc, 5);
1964 hr_utility.set_location('mode '||p_datetrack_mode , 5);
1965 l_datetrack_mode := p_datetrack_mode;
1966 open bnf_person_c;
1967 fetch bnf_person_c into l_bnf_person_id,
1968 l_business_group_id;
1969 close bnf_person_c;
1970 if l_bnf_person_id is not null then
1971 open other_bnf_c;
1972 fetch other_bnf_c into l_exist;
1973 --
1974 if other_bnf_c%NOTFOUND then
1975 --
1976 open usage_c;
1977 fetch usage_c into l_person_type_usage_id,
1978 l_object_version_number,
1979 l_effective_end_date,
1980 l_usg_effective_start_date;
1981 --
1982 if usage_c%FOUND then
1983 --
1984 -- bug 5668052
1985 if p_dsgn_thru_dt is not null
1986 then
1987 if l_datetrack_mode = hr_api.g_delete
1988 and p_dsgn_thru_dt < l_usg_effective_start_date
1989 then
1990 l_datetrack_mode := 'ZAP';
1991 end if;
1992 end if;
1993 -- if p_datetrack_mode = 'DELETE' AND l_effective_end_date = hr_api.g_eot
1994 if l_datetrack_mode = 'DELETE' AND l_effective_end_date = hr_api.g_eot
1995 then
1996
1997
1998 hr_per_type_usage_internal.delete_person_type_usage
1999 (p_validate => FALSE
2000 ,p_person_type_usage_id => l_person_type_usage_id
2001 ,p_effective_date => p_effective_date
2002 -- ,p_datetrack_mode => p_datetrack_mode
2003 ,p_datetrack_mode => l_datetrack_mode
2004 ,p_object_version_number => l_object_version_number
2005 ,p_effective_start_date => l_effective_start_date
2006 ,p_effective_end_date => l_effective_end_date
2007 );
2008 --
2009 -- start bug 5668052
2010 elsif l_datetrack_mode = hr_api.g_zap then
2011 hr_utility.set_location('Checking deletion in zap mode ',121);
2012 open bnf_rec_exists(l_bnf_person_id);
2013 fetch bnf_rec_exists into l_dummy;
2014 if bnf_rec_exists%notfound then
2015
2016 hr_utility.set_location('Deleteing in zap mode ',121);
2017 hr_per_type_usage_internal.delete_person_type_usage
2018 (p_validate => FALSE
2019 ,p_person_type_usage_id => l_person_type_usage_id
2020 ,p_effective_date => p_effective_date
2021 -- ,p_datetrack_mode => p_datetrack_mode
2022 ,p_datetrack_mode => l_datetrack_mode
2023 ,p_object_version_number => l_object_version_number
2024 ,p_effective_start_date => l_effective_start_date
2025 ,p_effective_end_date => l_effective_end_date
2026 );
2027 else
2028 hr_utility.set_location('Cant zap record exists ' || l_dummy,121);
2029 end if;
2030 close bnf_rec_exists;
2031 end if;
2032 end if;
2033 --
2034 close usage_c;
2035 --
2036 else
2037 hr_utility.set_location(' other benefit found ' , 30);
2038 end if;
2039 --
2040 close other_bnf_c;
2041 --
2042 end if;
2043 --
2044 hr_utility.set_location(' Leaving:'||l_proc, 70);
2045 --
2046 end remove_usage;
2047 --
2048 --
2049 end ben_PLAN_BENEFICIARY_api;