[Home] [Help]
PACKAGE BODY: APPS.BEN_PRTT_RT_VAL_API
Source
1 Package Body ben_prtt_rt_val_api as
2 /* $Header: beprvapi.pkb 120.7.12020000.2 2012/07/19 07:48:08 stee ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_prtt_rt_val_api.';
7 g_debug boolean := hr_utility.debug_enabled;
8 g_abr_name varchar2(255);
9 --
10 -- ---------------------------------------------------------------------------
11 -- |------------------------< result_is_suspended >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 function result_is_suspended
15 ( p_prtt_enrt_rslt_id number
16 ,p_person_id number
17 ,p_business_group_id number
18 ,p_effective_date date
19 ) return varchar2 is
20 --
21 -- Declare cursors and local variables
22 --
23 l_result varchar2(30);
24 l_proc varchar2(72); -- := g_package||'result_is_suspended';
25 --
26 cursor c_result is
27 select nvl(sspndd_flag,'N')
28 from ben_prtt_enrt_rslt_f pen
29 where prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
30 pen.prtt_enrt_rslt_stat_cd is null and
31 business_group_id=p_business_group_id and
32 p_effective_date <= effective_end_date
33 order by effective_Start_Date;
34 /* Bug 4949280 - Commented - Following clause fails when PEN.EFFECTIVE_START_DATE is later
35 than PRV.EFFECTIVE_START_DATE
36 p_effective_date between
37 effective_start_date and effective_end_date
38 */
39 --
40 begin
41 --
42 if g_debug then
43 l_proc := g_package||'result_is_suspended';
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 hr_utility.set_location('effective_date:'|| p_effective_date, 10);
46 end if;
47 --
48 -- open cursor and fetch result
49 --
50 open c_result;
51 fetch c_result into l_result;
52 if c_result%notfound then
53 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FND');
54 fnd_message.set_token('PROC',l_proc);
55 fnd_message.set_token('ID', to_char(p_prtt_enrt_rslt_id));
56 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
57 fnd_message.set_token('LER_ID', null);
58 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
59 fnd_message.raise_error;
60 end if;
61 close c_result;
62 --
63 if g_debug then
64 hr_utility.set_location('Leaving:'|| l_proc, 1000);
65 end if;
66 --
67 return(l_result);
68 end;
69 --
70 -- ---------------------------------------------------------------------------
71 -- |------------------------< get_non_recurring_end_dt >---------------------|
72 -- ---------------------------------------------------------------------------
73 --
74 procedure get_non_recurring_end_dt
75 ( p_rt_strt_dt date
76 ,p_acty_base_rt_id number
77 ,p_business_group_id number
78 ,p_rt_end_dt in out nocopy date
79 ,p_recurring_rt out nocopy boolean
80 ,p_effective_date date
81 ) is
82
83 l_proc varchar2(72); -- := g_package||'get_non_recurring_end_dt';
84 l_abr_name varchar2(255);
85 l_rcrrg_cd varchar2(30);
86 l_element_type_id number;
87 l_ele_rqd_flag varchar2(1);
88
89 cursor c_ety is
90 select processing_type
91 from pay_element_types_f
92 where element_type_id = l_element_type_id
93 and p_effective_date between effective_start_date
94 and effective_end_date ;
95
96 cursor c_abr is
97 select name,
98 rcrrg_cd,
99 ele_rqd_flag,
100 element_type_id
101 from ben_acty_base_rt_f
102 where business_group_id = p_business_group_id
103 and p_acty_base_rt_id = acty_base_rt_id
104 and p_rt_strt_dt between effective_start_date
105 and effective_end_date ;
106
107
108 Begin
109
110 if g_debug then
111 hr_utility.set_location('Entering:'|| l_proc, 10);
112 end if;
113
114 p_recurring_rt := true;
115
116 open c_abr ;
117 fetch c_abr into
118 l_abr_name,
119 l_rcrrg_cd,
120 l_ele_rqd_flag,
121 l_element_type_id ;
122 close c_abr ;
123
124 if nvl(l_rcrrg_cd,'R') = 'ONCE' then
125
126 p_rt_end_dt := p_rt_strt_dt ;
127 p_recurring_rt := false;
128
129 elsif l_element_type_id is not null and
130 l_ele_rqd_flag = 'Y' then
131 open c_ety;
132 fetch c_ety into l_rcrrg_cd ;
133 if c_ety%found then
134 if nvl(l_rcrrg_cd,'R') = 'N' then
135 p_rt_end_dt := p_rt_strt_dt ;
136 p_recurring_rt := false;
137 end if ;
138 end if;
139 close c_ety ;
140 end if ;
141
142 g_abr_name := l_abr_name;
143 if g_debug then
144 hr_utility.set_location('return date '|| p_rt_end_dt, 20);
145 hr_utility.set_location('Leaving:'|| l_proc, 20);
146 end if;
147
148 end get_non_recurring_end_dt;
149 --
150 -- ---------------------------------------------------------------------------
151 -- |----------------------------< chk_overlapping_dates >---------------------|
152 -- ---------------------------------------------------------------------------
153 --
154 procedure chk_overlapping_dates
155 (p_acty_base_rt_id in number
156 ,p_prtt_rt_val_id in number
157 ,p_prtt_enrt_rslt_id in number
158 ,p_new_rt_strt_dt in date
159 ,p_new_rt_end_dt in date
160 ) is
161
162 cursor c_overlap_rt is
163 select rt_strt_dt,
164 rt_end_dt
165 from ben_prtt_rt_val
166 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
167 and acty_base_rt_id = p_acty_base_rt_id
168 and prtt_rt_val_id <> nvl(p_prtt_rt_val_id,-1)
169 and prtt_rt_val_stat_cd is null
170 and ((p_new_rt_strt_dt between rt_strt_dt and rt_end_dt) or
171 (rt_strt_dt between p_new_rt_strt_dt and p_new_rt_end_dt));
172
173 l_rt_strt_dt date;
174 l_rt_end_dt date;
175 l_proc varchar2(72);
176
177 begin
178
179 g_debug := hr_utility.debug_enabled;
180 if g_debug then
181 l_proc := g_package||'chk_overlapping_dates';
182 hr_utility.set_location('Entering:'|| l_proc, 5);
183 end if;
184
185 open c_overlap_rt;
186 fetch c_overlap_rt into
187 l_rt_strt_dt,
188 l_rt_end_dt;
189 close c_overlap_rt;
190
191 if l_rt_strt_dt is not null then
192 fnd_message.set_name('BEN','BEN_93811_OVERLAPPING_RATES');
193 fnd_message.set_token('PROC',l_proc);
194 fnd_message.set_token('ABR', g_abr_name);
195 fnd_message.set_token('RSLT_ID', to_char(p_prtt_enrt_rslt_id));
196 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_new_rt_strt_dt));
197 fnd_message.raise_error;
198 end if;
199
200 if g_debug then
201 hr_utility.set_location('Leaving:'|| l_proc, 10);
202 end if;
203
204 end chk_overlapping_dates;
205 --
206 -- ---------------------------------------------------------------------------
207 -- |------------------------< create_prtt_rt_val >---------------------------|
208 -- ---------------------------------------------------------------------------
209 --
210 procedure create_prtt_rt_val
211 (p_validate in boolean default false
212 ,p_prtt_rt_val_id out nocopy number
213 ,p_enrt_rt_id in number default null
214 ,p_person_id in number
215 ,p_input_value_id in number
216 ,p_element_type_id in number
217 ,p_rt_strt_dt in date default null
218 ,p_rt_end_dt in date default null
219 ,p_rt_typ_cd in varchar2 default null
220 ,p_tx_typ_cd in varchar2 default null
221 ,p_ordr_num in number default null
222 ,p_acty_typ_cd in varchar2 default null
223 ,p_mlt_cd in varchar2 default null
224 ,p_acty_ref_perd_cd in varchar2 default null
225 ,p_rt_val in number default null
226 ,p_ann_rt_val in number default null
227 ,p_cmcd_rt_val in number default null
228 ,p_cmcd_ref_perd_cd in varchar2 default null
229 ,p_bnft_rt_typ_cd in varchar2 default null
230 ,p_dsply_on_enrt_flag in varchar2 default 'N'
231 ,p_rt_ovridn_flag in varchar2 default 'N'
232 ,p_rt_ovridn_thru_dt in date default null
233 ,p_elctns_made_dt in date default null
234 ,p_prtt_rt_val_stat_cd in varchar2 default null
235 ,p_prtt_enrt_rslt_id in number default null
236 ,p_cvg_amt_calc_mthd_id in number default null
237 ,p_actl_prem_id in number default null
238 ,p_comp_lvl_fctr_id in number default null
239 ,p_element_entry_value_id in number default null
240 ,p_per_in_ler_id in number default null
241 ,p_ended_per_in_ler_id in number default null
242 ,p_acty_base_rt_id in number default null
243 ,p_prtt_reimbmt_rqst_id in number default null
244 ,p_prtt_rmt_aprvd_fr_pymt_id in number default null
245 ,p_pp_in_yr_used_num in number default null
246 ,p_business_group_id in number default null
247 ,p_prv_attribute_category in varchar2 default null
248 ,p_prv_attribute1 in varchar2 default null
249 ,p_prv_attribute2 in varchar2 default null
250 ,p_prv_attribute3 in varchar2 default null
251 ,p_prv_attribute4 in varchar2 default null
252 ,p_prv_attribute5 in varchar2 default null
253 ,p_prv_attribute6 in varchar2 default null
254 ,p_prv_attribute7 in varchar2 default null
255 ,p_prv_attribute8 in varchar2 default null
256 ,p_prv_attribute9 in varchar2 default null
257 ,p_prv_attribute10 in varchar2 default null
258 ,p_prv_attribute11 in varchar2 default null
259 ,p_prv_attribute12 in varchar2 default null
260 ,p_prv_attribute13 in varchar2 default null
261 ,p_prv_attribute14 in varchar2 default null
262 ,p_prv_attribute15 in varchar2 default null
263 ,p_prv_attribute16 in varchar2 default null
264 ,p_prv_attribute17 in varchar2 default null
265 ,p_prv_attribute18 in varchar2 default null
266 ,p_prv_attribute19 in varchar2 default null
267 ,p_prv_attribute20 in varchar2 default null
268 ,p_prv_attribute21 in varchar2 default null
269 ,p_prv_attribute22 in varchar2 default null
270 ,p_prv_attribute23 in varchar2 default null
271 ,p_prv_attribute24 in varchar2 default null
272 ,p_prv_attribute25 in varchar2 default null
273 ,p_prv_attribute26 in varchar2 default null
274 ,p_prv_attribute27 in varchar2 default null
275 ,p_prv_attribute28 in varchar2 default null
276 ,p_prv_attribute29 in varchar2 default null
277 ,p_prv_attribute30 in varchar2 default null
278 ,p_pk_id_table_name in varchar2 default null
279 ,p_pk_id in number default null
280 ,p_object_version_number out nocopy number
281 ,p_effective_date in date
282 ) is
283 --
284 -- Declare cursors and local variables
285 --
286 -- LGE : Rate certification.
287 --
288 cursor c_enrt_ctfn(p_enrt_rt_id in number) is
289 select erc.*
290 from ben_enrt_rt_ctfn erc
291 where enrt_rt_id = p_enrt_rt_id
292 and business_group_id = p_business_group_id;
293 --
294 -- LGE : Check whether the rate is non recurring and attached to
295 -- plan not in program.
296 --
297 cursor c_pl_nip is
298 select null
299 from ben_pl_f pln,
300 ben_prtt_enrt_rslt_f pen
301 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
302 and pen.prtt_enrt_rslt_stat_cd is null
303 and pen.pl_id = pln.pl_id
304 and pln.pl_cd = 'MYNTBPGM'
305 and p_effective_date between pln.effective_start_date and
306 pln.effective_end_date ;
307
308 --Bug 4141719: Retrieve the rate name which will be used to show
309 --in the note 93120(if the note is applicable)
310 cursor c_abr is
311 select name
312 from ben_acty_base_rt_f
313 where business_group_id = p_business_group_id
314 and p_acty_base_rt_id = acty_base_rt_id
315 and p_rt_strt_dt between effective_start_date
316 and effective_end_date ;
317
318 --
319 l_dummy varchar2(30);
320 l_rcrrg_cd varchar2(100);
321 l_recurring_rt boolean;
322 l_abr_name varchar2(300);
323 --
324 l_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%TYPE;
325 l_enrt_rt_ovn number;
326 l_proc varchar2(72) ; -- := g_package||'create_prtt_rt_val';
327 l_object_version_number ben_prtt_rt_val.object_version_number%TYPE;
328 --
329 l_dummy_number number;
330 l_rt_end_dt date ;
331 l_prtt_rt_val_ctfn_prvdd_id number;
332 l_ovn number;
333 l_pl_nip boolean;
334 --
335 begin
336 --
337 g_debug := hr_utility.debug_enabled;
338 if g_debug then
339 l_proc := g_package||'create_prtt_rt_val';
340 hr_utility.set_location('Entering:'|| l_proc, 10);
341 end if;
342 --
343 -- Issue a savepoint if operating in validation only mode
344 --
345 savepoint create_prtt_rt_val;
346 --
347 if g_debug then
348 hr_utility.set_location(l_proc, 20);
349 end if;
350
351 l_rt_end_dt := p_rt_end_dt;
352
353 --Bug 4141719: Retrieve the rate name which will be used to show
354 --in the note 93120(if the note is applicable)
355 open c_abr ;
356 fetch c_abr into g_abr_name;
357 close c_abr ;
358
359 -- get the end date and the rate/element type
360 get_non_recurring_end_dt
361 (p_rt_end_dt => l_rt_end_dt
362 ,p_rt_strt_dt => p_rt_strt_dt
363 ,p_acty_base_rt_id => p_acty_base_rt_id
364 ,p_business_group_id => p_business_group_id
365 ,p_recurring_rt => l_recurring_rt
366 ,p_effective_date => p_effective_date
367 ) ;
368
369 if l_recurring_rt then
370
371 chk_overlapping_dates
372 (p_acty_base_rt_id => p_acty_base_rt_id
373 ,p_prtt_rt_val_id => null
374 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
375 ,p_new_rt_strt_dt => p_rt_strt_dt
376 ,p_new_rt_end_dt => hr_api.g_eot);
377
378 end if;
379 --
380 -- Process Logic
381 --
382 begin
383 --
384 -- Start of API User Hook for the before hook of create_prtt_rt_val
385 --
386 ben_prtt_rt_val_bk1.create_prtt_rt_val_b
387 (
388 p_rt_strt_dt => p_rt_strt_dt
389 ,p_rt_end_dt => l_rt_end_dt
390 ,p_rt_typ_cd => p_rt_typ_cd
391 ,p_tx_typ_cd => p_tx_typ_cd
392 ,p_ordr_num => p_ordr_num
393 ,p_acty_typ_cd => p_acty_typ_cd
394 ,p_mlt_cd => p_mlt_cd
395 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
396 ,p_rt_val => p_rt_val
397 ,p_ann_rt_val => p_ann_rt_val
398 ,p_cmcd_rt_val => p_cmcd_rt_val
399 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
400 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
401 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
402 ,p_rt_ovridn_flag => p_rt_ovridn_flag
403 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
404 ,p_elctns_made_dt => p_elctns_made_dt
405 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
406 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
407 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
408 ,p_actl_prem_id => p_actl_prem_id
409 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
410 ,p_element_entry_value_id => p_element_entry_value_id
411 ,p_per_in_ler_id => p_per_in_ler_id
412 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
413 ,p_acty_base_rt_id => p_acty_base_rt_id
414 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
415 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
416 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
417 ,p_business_group_id => p_business_group_id
418 ,p_prv_attribute_category => p_prv_attribute_category
419 ,p_prv_attribute1 => p_prv_attribute1
420 ,p_prv_attribute2 => p_prv_attribute2
421 ,p_prv_attribute3 => p_prv_attribute3
422 ,p_prv_attribute4 => p_prv_attribute4
423 ,p_prv_attribute5 => p_prv_attribute5
424 ,p_prv_attribute6 => p_prv_attribute6
425 ,p_prv_attribute7 => p_prv_attribute7
426 ,p_prv_attribute8 => p_prv_attribute8
427 ,p_prv_attribute9 => p_prv_attribute9
428 ,p_prv_attribute10 => p_prv_attribute10
429 ,p_prv_attribute11 => p_prv_attribute11
430 ,p_prv_attribute12 => p_prv_attribute12
431 ,p_prv_attribute13 => p_prv_attribute13
432 ,p_prv_attribute14 => p_prv_attribute14
433 ,p_prv_attribute15 => p_prv_attribute15
434 ,p_prv_attribute16 => p_prv_attribute16
435 ,p_prv_attribute17 => p_prv_attribute17
436 ,p_prv_attribute18 => p_prv_attribute18
437 ,p_prv_attribute19 => p_prv_attribute19
438 ,p_prv_attribute20 => p_prv_attribute20
439 ,p_prv_attribute21 => p_prv_attribute21
440 ,p_prv_attribute22 => p_prv_attribute22
441 ,p_prv_attribute23 => p_prv_attribute23
442 ,p_prv_attribute24 => p_prv_attribute24
443 ,p_prv_attribute25 => p_prv_attribute25
444 ,p_prv_attribute26 => p_prv_attribute26
445 ,p_prv_attribute27 => p_prv_attribute27
446 ,p_prv_attribute28 => p_prv_attribute28
447 ,p_prv_attribute29 => p_prv_attribute29
448 ,p_prv_attribute30 => p_prv_attribute30
449 ,p_pk_id_table_name => p_pk_id_table_name
450 ,p_pk_id => p_pk_id
451 ,p_effective_date => trunc(p_effective_date)
452 );
453 exception
454 when hr_api.cannot_find_prog_unit then
455 hr_api.cannot_find_prog_unit_error
456 (
457 p_module_name => 'CREATE_prtt_rt_val'
458 ,p_hook_type => 'BP'
459 );
460 --
461 -- End of API User Hook for the before hook of create_prtt_rt_val
462 --
463 end;
464 --
465 ben_prv_ins.ins
466 (
467 p_prtt_rt_val_id => l_prtt_rt_val_id
468 ,p_enrt_rt_id => p_enrt_rt_id
469 ,p_rt_strt_dt => p_rt_strt_dt
470 ,p_rt_end_dt => l_rt_end_dt
471 ,p_rt_typ_cd => p_rt_typ_cd
472 ,p_tx_typ_cd => p_tx_typ_cd
473 ,p_ordr_num => p_ordr_num
474 ,p_acty_typ_cd => p_acty_typ_cd
475 ,p_mlt_cd => p_mlt_cd
476 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
477 ,p_rt_val => p_rt_val
478 ,p_ann_rt_val => p_ann_rt_val
479 ,p_cmcd_rt_val => p_cmcd_rt_val
480 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
481 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
482 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
483 ,p_rt_ovridn_flag => p_rt_ovridn_flag
484 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
485 ,p_elctns_made_dt => p_elctns_made_dt
486 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
487 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
488 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
489 ,p_actl_prem_id => p_actl_prem_id
490 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
491 ,p_element_entry_value_id => p_element_entry_value_id
492 ,p_per_in_ler_id => p_per_in_ler_id
493 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
494 ,p_acty_base_rt_id => p_acty_base_rt_id
495 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
496 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
497 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
498 ,p_business_group_id => p_business_group_id
499 ,p_prv_attribute_category => p_prv_attribute_category
500 ,p_prv_attribute1 => p_prv_attribute1
501 ,p_prv_attribute2 => p_prv_attribute2
502 ,p_prv_attribute3 => p_prv_attribute3
503 ,p_prv_attribute4 => p_prv_attribute4
504 ,p_prv_attribute5 => p_prv_attribute5
505 ,p_prv_attribute6 => p_prv_attribute6
506 ,p_prv_attribute7 => p_prv_attribute7
507 ,p_prv_attribute8 => p_prv_attribute8
508 ,p_prv_attribute9 => p_prv_attribute9
509 ,p_prv_attribute10 => p_prv_attribute10
510 ,p_prv_attribute11 => p_prv_attribute11
511 ,p_prv_attribute12 => p_prv_attribute12
512 ,p_prv_attribute13 => p_prv_attribute13
513 ,p_prv_attribute14 => p_prv_attribute14
514 ,p_prv_attribute15 => p_prv_attribute15
515 ,p_prv_attribute16 => p_prv_attribute16
516 ,p_prv_attribute17 => p_prv_attribute17
517 ,p_prv_attribute18 => p_prv_attribute18
518 ,p_prv_attribute19 => p_prv_attribute19
519 ,p_prv_attribute20 => p_prv_attribute20
520 ,p_prv_attribute21 => p_prv_attribute21
521 ,p_prv_attribute22 => p_prv_attribute22
522 ,p_prv_attribute23 => p_prv_attribute23
523 ,p_prv_attribute24 => p_prv_attribute24
524 ,p_prv_attribute25 => p_prv_attribute25
525 ,p_prv_attribute26 => p_prv_attribute26
526 ,p_prv_attribute27 => p_prv_attribute27
527 ,p_prv_attribute28 => p_prv_attribute28
528 ,p_prv_attribute29 => p_prv_attribute29
529 ,p_prv_attribute30 => p_prv_attribute30
530 ,p_pk_id_table_name => p_pk_id_table_name
531 ,p_pk_id => p_pk_id
532 ,p_object_version_number => l_object_version_number
533 ,p_effective_date => p_effective_date
534 );
535 --
536 if p_enrt_rt_id is not null then
537 --
538 -- only update the ben_enrt_rt table to point to the inserted prtt_rt_val
539 -- row if the rate val row is not being inserted as a void or backed out
540 -- row. if the rate end date is less than the rate start date, the row
541 -- will be voided in the pre-insert and pre-update in the rhi.
542 --
543 if p_rt_strt_dt > nvl(l_rt_end_dt,p_rt_strt_dt)
544 or p_prtt_rt_val_stat_cd = 'BCKDT' then
545 null;
546 else
547 --
548 -- Get the object version number for the update
549 --
550 l_enrt_rt_ovn:=
551 dt_api.get_object_version_number
552 (p_base_table_name => 'ben_enrt_rt',
553 p_base_key_column => 'enrt_rt_id',
554 p_base_key_value => p_enrt_rt_id)-1;
555
556 ben_enrollment_rate_api.update_enrollment_rate(
557 p_enrt_rt_id => p_enrt_rt_id,
558 p_prtt_rt_val_id => l_prtt_rt_val_id,
559 p_object_version_number => l_enrt_rt_ovn,
560 p_effective_date => p_effective_date
561 );
562 end if;
563 --
564 -- Option Level Rates enhancements
565 --
566 --
567 -- LGE : Create the rate certifications.
568 --
569 if not l_recurring_rt then
570 --
571 -- check if Plan not in Program
572 --
573 open c_pl_nip ;
574 fetch c_pl_nip into l_dummy ;
575 l_pl_nip := c_pl_nip%found;
576 close c_pl_nip ;
577
578 if l_pl_nip then
579 --
580 for l_rt_ctfn_rec in c_enrt_ctfn(p_enrt_rt_id) loop
581 --
582 ben_prv_ctfn_prvdd_api.create_PRV_CTFN_PRVDD
583 (
584 p_prtt_rt_val_ctfn_prvdd_id => l_prtt_rt_val_ctfn_prvdd_id
585 ,p_enrt_ctfn_rqd_flag => l_rt_ctfn_rec.rqd_flag
586 ,p_enrt_ctfn_typ_cd => l_rt_ctfn_rec.enrt_ctfn_typ_cd
587 ,p_enrt_ctfn_recd_dt => null
588 ,p_enrt_ctfn_dnd_dt => null
589 ,p_prtt_rt_val_id => l_prtt_rt_val_id
590 ,p_business_group_id => l_rt_ctfn_rec.business_group_id
591 ,p_object_version_number => l_ovn
592 ,p_effective_date => p_effective_date
593 );
594 --
595 end loop;
596 --Bug 3108779
597 l_abr_name :=g_abr_name;
598 --
599 if l_prtt_rt_val_ctfn_prvdd_id is not null then
600 ben_warnings.load_warning
601 (p_application_short_name => 'BEN',
602 p_message_name => 'BEN_93120_RQD_RT_CTFN_MISSING',
603 p_parma => l_abr_name,
604 p_person_id => p_person_id);
605 end if;
606
607 end if; --pl nip
608 end if; -- rate certification
609 end if; --enrt rt
610 --
611 -- Create the element entry if the result is not suspended
612 --
613 -- Bug 9242703: passing p_rt_strt_dt below. We need to check whether the
614 -- enrollment is suspended on the rate start date, not the effective date.
615 if result_is_suspended(
616 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
617 p_person_id => p_person_id,
618 p_business_group_id => p_business_group_id,
619 p_effective_date => nvl(p_rt_strt_dt,p_effective_date)) ='N' and
620 l_prtt_rt_val_ctfn_prvdd_id is null then
621
622 ben_element_entry.create_enrollment_element
623 (p_business_group_id => p_business_group_id
624 ,p_prtt_rt_val_id => l_prtt_rt_val_id
625 ,p_person_id => p_person_id
626 ,p_acty_ref_perd => p_acty_ref_perd_cd
627 ,p_acty_base_rt_id => p_acty_base_rt_id
628 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
629 ,p_rt_start_date => p_rt_strt_dt
630 ,p_rt => p_rt_val
631 ,p_cmncd_rt => p_cmcd_rt_val
632 ,p_ann_rt => p_ann_rt_val
633 ,p_input_value_id => p_input_value_id
634 ,p_element_type_id => p_element_type_id
635 ,p_prv_object_version_number=> l_object_version_number
636 ,p_effective_date => p_effective_date
637 ,p_eev_screen_entry_value => l_dummy_number
638 ,p_element_entry_value_id => l_dummy_number
639 );
640 --
641 end if;
642 --
643 begin
644 --
645 -- Start of API User Hook for the after hook of create_prtt_rt_val
646 --
647 ben_prtt_rt_val_bk1.create_prtt_rt_val_a
648 (
649 p_prtt_rt_val_id => l_prtt_rt_val_id
650 ,p_rt_strt_dt => p_rt_strt_dt
651 ,p_rt_end_dt => l_rt_end_dt
652 ,p_rt_typ_cd => p_rt_typ_cd
653 ,p_tx_typ_cd => p_tx_typ_cd
654 ,p_ordr_num => p_ordr_num
655 ,p_acty_typ_cd => p_acty_typ_cd
656 ,p_mlt_cd => p_mlt_cd
657 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
658 ,p_rt_val => p_rt_val
659 ,p_ann_rt_val => p_ann_rt_val
660 ,p_cmcd_rt_val => p_cmcd_rt_val
661 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
662 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
663 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
664 ,p_rt_ovridn_flag => p_rt_ovridn_flag
665 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
666 ,p_elctns_made_dt => p_elctns_made_dt
667 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
668 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
669 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
670 ,p_actl_prem_id => p_actl_prem_id
671 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
672 ,p_element_entry_value_id => p_element_entry_value_id
673 ,p_per_in_ler_id => p_per_in_ler_id
674 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
675 ,p_acty_base_rt_id => p_acty_base_rt_id
676 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
677 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
678 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
679 ,p_business_group_id => p_business_group_id
680 ,p_prv_attribute_category => p_prv_attribute_category
681 ,p_prv_attribute1 => p_prv_attribute1
682 ,p_prv_attribute2 => p_prv_attribute2
683 ,p_prv_attribute3 => p_prv_attribute3
684 ,p_prv_attribute4 => p_prv_attribute4
685 ,p_prv_attribute5 => p_prv_attribute5
686 ,p_prv_attribute6 => p_prv_attribute6
687 ,p_prv_attribute7 => p_prv_attribute7
688 ,p_prv_attribute8 => p_prv_attribute8
689 ,p_prv_attribute9 => p_prv_attribute9
690 ,p_prv_attribute10 => p_prv_attribute10
691 ,p_prv_attribute11 => p_prv_attribute11
692 ,p_prv_attribute12 => p_prv_attribute12
693 ,p_prv_attribute13 => p_prv_attribute13
694 ,p_prv_attribute14 => p_prv_attribute14
695 ,p_prv_attribute15 => p_prv_attribute15
696 ,p_prv_attribute16 => p_prv_attribute16
697 ,p_prv_attribute17 => p_prv_attribute17
698 ,p_prv_attribute18 => p_prv_attribute18
699 ,p_prv_attribute19 => p_prv_attribute19
700 ,p_prv_attribute20 => p_prv_attribute20
701 ,p_prv_attribute21 => p_prv_attribute21
702 ,p_prv_attribute22 => p_prv_attribute22
703 ,p_prv_attribute23 => p_prv_attribute23
704 ,p_prv_attribute24 => p_prv_attribute24
705 ,p_prv_attribute25 => p_prv_attribute25
706 ,p_prv_attribute26 => p_prv_attribute26
707 ,p_prv_attribute27 => p_prv_attribute27
708 ,p_prv_attribute28 => p_prv_attribute28
709 ,p_prv_attribute29 => p_prv_attribute29
710 ,p_prv_attribute30 => p_prv_attribute30
711 ,p_pk_id_table_name => p_pk_id_table_name
712 ,p_pk_id => p_pk_id
713 ,p_object_version_number => l_object_version_number
714 ,p_effective_date => trunc(p_effective_date)
715 );
716 exception
717 when hr_api.cannot_find_prog_unit then
718 hr_api.cannot_find_prog_unit_error
719 (p_module_name => 'CREATE_prtt_rt_val'
720 ,p_hook_type => 'AP'
721 );
722 --
723 -- End of API User Hook for the after hook of create_prtt_rt_val
724 --
725 end;
726 --
727 if g_debug then
728 hr_utility.set_location(l_proc, 60);
729 end if;
730 --
731 -- When in validation only mode raise the Validate_Enabled exception
732 --
733 if p_validate then
734 raise hr_api.validate_enabled;
735 end if;
736 --
737 -- Set all output arguments
738 --
739 p_prtt_rt_val_id := l_prtt_rt_val_id;
740 p_object_version_number := l_object_version_number;
741 --
742 if g_debug then
743 hr_utility.set_location(' Leaving:'||l_proc, 70);
744 end if;
745 --
746 exception
747 --
748 when hr_api.validate_enabled then
749 --
750 -- As the Validate_Enabled exception has been raised
751 -- we must rollback to the savepoint
752 --
753 ROLLBACK TO create_prtt_rt_val;
754 --
755 -- Only set output warning arguments
756 -- (Any key or derived arguments must be set to null
757 -- when validation only mode is being used.)
758 --
759 p_prtt_rt_val_id := null;
760 p_object_version_number := null;
761 if g_debug then
762 hr_utility.set_location(' Leaving:'||l_proc, 80);
763 end if;
764 --
765 when others then
766 --
767 -- A validation or unexpected error has occured
768 --
769 ROLLBACK TO create_prtt_rt_val;
770 p_prtt_rt_val_id := null;
771 p_object_version_number := null;
772 raise;
773 --
774 end create_prtt_rt_val;
775 -- ---------------------------------------------------------------------------
776 -- |------------------------< update_prtt_rt_val >--- -----------------------|
777 -- ---------------------------------------------------------------------------
778 --
779 procedure update_prtt_rt_val
780 (p_validate in boolean default false
781 ,p_prtt_rt_val_id in number
782 ,p_person_id in number default hr_api.g_number
783 ,p_input_value_id in number default hr_api.g_number
784 ,p_element_type_id in number default hr_api.g_number
785 ,p_enrt_rt_id in number default hr_api.g_number
786 ,p_rt_strt_dt in date default hr_api.g_date
787 ,p_rt_end_dt in date default hr_api.g_date
788 ,p_rt_typ_cd in varchar2 default hr_api.g_varchar2
789 ,p_tx_typ_cd in varchar2 default hr_api.g_varchar2
790 ,p_ordr_num in number default hr_api.g_number
791 ,p_acty_typ_cd in varchar2 default hr_api.g_varchar2
792 ,p_mlt_cd in varchar2 default hr_api.g_varchar2
793 ,p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2
794 ,p_rt_val in number default hr_api.g_number
795 ,p_ann_rt_val in number default hr_api.g_number
796 ,p_cmcd_rt_val in number default hr_api.g_number
797 ,p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2
798 ,p_bnft_rt_typ_cd in varchar2 default hr_api.g_varchar2
799 ,p_dsply_on_enrt_flag in varchar2 default hr_api.g_varchar2
800 ,p_rt_ovridn_flag in varchar2 default hr_api.g_varchar2
801 ,p_rt_ovridn_thru_dt in date default hr_api.g_date
802 ,p_elctns_made_dt in date default hr_api.g_date
803 ,p_prtt_rt_val_stat_cd in varchar2 default hr_api.g_varchar2
804 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
805 ,p_cvg_amt_calc_mthd_id in number default hr_api.g_number
806 ,p_actl_prem_id in number default hr_api.g_number
807 ,p_comp_lvl_fctr_id in number default hr_api.g_number
808 ,p_element_entry_value_id in number default hr_api.g_number
809 ,p_per_in_ler_id in number default hr_api.g_number
810 ,p_ended_per_in_ler_id in number default hr_api.g_number
811 ,p_acty_base_rt_id in number default hr_api.g_number
812 ,p_prtt_reimbmt_rqst_id in number default hr_api.g_number
813 ,p_prtt_rmt_aprvd_fr_pymt_id in number default hr_api.g_number
814 ,p_pp_in_yr_used_num in number default hr_api.g_number
815 ,p_business_group_id in number default hr_api.g_number
816 ,p_prv_attribute_category in varchar2 default hr_api.g_varchar2
817 ,p_prv_attribute1 in varchar2 default hr_api.g_varchar2
818 ,p_prv_attribute2 in varchar2 default hr_api.g_varchar2
819 ,p_prv_attribute3 in varchar2 default hr_api.g_varchar2
820 ,p_prv_attribute4 in varchar2 default hr_api.g_varchar2
821 ,p_prv_attribute5 in varchar2 default hr_api.g_varchar2
822 ,p_prv_attribute6 in varchar2 default hr_api.g_varchar2
823 ,p_prv_attribute7 in varchar2 default hr_api.g_varchar2
824 ,p_prv_attribute8 in varchar2 default hr_api.g_varchar2
825 ,p_prv_attribute9 in varchar2 default hr_api.g_varchar2
826 ,p_prv_attribute10 in varchar2 default hr_api.g_varchar2
827 ,p_prv_attribute11 in varchar2 default hr_api.g_varchar2
828 ,p_prv_attribute12 in varchar2 default hr_api.g_varchar2
829 ,p_prv_attribute13 in varchar2 default hr_api.g_varchar2
830 ,p_prv_attribute14 in varchar2 default hr_api.g_varchar2
831 ,p_prv_attribute15 in varchar2 default hr_api.g_varchar2
832 ,p_prv_attribute16 in varchar2 default hr_api.g_varchar2
833 ,p_prv_attribute17 in varchar2 default hr_api.g_varchar2
834 ,p_prv_attribute18 in varchar2 default hr_api.g_varchar2
835 ,p_prv_attribute19 in varchar2 default hr_api.g_varchar2
836 ,p_prv_attribute20 in varchar2 default hr_api.g_varchar2
837 ,p_prv_attribute21 in varchar2 default hr_api.g_varchar2
838 ,p_prv_attribute22 in varchar2 default hr_api.g_varchar2
839 ,p_prv_attribute23 in varchar2 default hr_api.g_varchar2
840 ,p_prv_attribute24 in varchar2 default hr_api.g_varchar2
841 ,p_prv_attribute25 in varchar2 default hr_api.g_varchar2
842 ,p_prv_attribute26 in varchar2 default hr_api.g_varchar2
843 ,p_prv_attribute27 in varchar2 default hr_api.g_varchar2
844 ,p_prv_attribute28 in varchar2 default hr_api.g_varchar2
845 ,p_prv_attribute29 in varchar2 default hr_api.g_varchar2
846 ,p_prv_attribute30 in varchar2 default hr_api.g_varchar2
847 ,p_pk_id_table_name in varchar2 default hr_api.g_varchar2
848 ,p_pk_id in number default hr_api.g_number
849 ,p_no_end_element in boolean default false
850 ,p_object_version_number in out nocopy number
851 ,p_effective_date in date
852 ) is
853 --
854 -- Declare cursors and local variables
855 --
856 --
857 cursor c_old_prv is
858 select prv.rt_end_dt,
859 prv.rt_strt_dt,
860 prv.prtt_enrt_rslt_id,
861 prv.element_entry_value_id,
862 prv.acty_base_rt_id,
863 prv.rt_val,
864 prv.cmcd_rt_val,
865 prv.ann_rt_val,
866 prv.acty_ref_perd_cd,
867 prv.per_in_ler_id,
868 prv.prtt_rt_val_stat_cd
869 from ben_prtt_rt_val prv
870 where prv.prtt_rt_val_id=p_prtt_rt_val_id and
871 prv.business_group_id=p_business_group_id;
872 --
873 cursor c_ele_entry (p_element_entry_value_id number,
874 p_effective_date date) is
875 select ele.element_entry_id,
876 ele.entry_type,
877 ele.original_entry_id,
878 elt.processing_type,
879 elk.element_type_id,
880 elk.effective_end_date
881 from pay_element_entry_values_f elv,
882 pay_element_entries_f ele,
883 pay_element_links_f elk,
884 pay_element_types_f elt
885 where elv.element_entry_value_id = p_element_entry_value_id
886 and elv.element_entry_id = ele.element_entry_id
887 and elv.effective_start_date between ele.effective_start_date
888 and ele.effective_end_date
889 and ele.element_link_id = elk.element_link_id
890 and ele.effective_start_date between elk.effective_start_date
891 and elk.effective_end_date
892 and elk.element_type_id = elt.element_type_id
893 and elk.effective_start_date between elt.effective_start_date
894 and elt.effective_end_date ;
895 --
896 l_ele_rec c_ele_entry%rowtype;
897 --
898 cursor c_prv_ovn is
899 select prv.object_version_number
900 from ben_prtt_rt_val prv
901 where prv.prtt_rt_val_id=p_prtt_rt_val_id;
902 --
903 -- 12583091
904 --
905 cursor c_pen_curr(p_prtt_enrt_rslt_id number) is
906 select pen.*
907 from ben_prtt_enrt_rslt_f pen
908 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
909 and pen.effective_end_date = hr_api.g_eot
910 and pen.prtt_enrt_rslt_stat_cd is null
911 and pen.business_group_id = p_business_group_id;
912 --
913 l_pen_curr c_pen_curr%rowtype;
914 --
915 -- end 12583091
916 --
917 l_proc varchar2(72) := g_package||'update_prtt_rt_val';
918 l_object_version_number ben_prtt_rt_val.object_version_number%TYPE;
919 l_enrt_rt_ovn number;
920 l_old_enrt_rslt_id number;
921 l_old_rt_end_dt date;
922 l_rt_end_dt date;
923 l_old_rt_strt_dt date;
924 l_old_element_link_id number;
925 l_ee_effective_end_date date;
926 l_old_element_entry_value_id number;
927 l_old_abr_id number;
928 l_old_rt_val number;
929 l_old_cmcd_rt_val number;
930 l_old_ann_rt_val number;
931 l_old_per_in_ler_id number;
932 l_old_acty_ref_perd_cd varchar2(30);
933 l_old_prtt_rt_val_stat_cd varchar2(30);
934 l_element_type_id number;
935 l_rt_strt_dt date;
936 l_assignment_id number;
937 l_organization_id number;
938 l_payroll_id number;
939 l_max_end_date date;
940 l_rt_dt date;
941 l_abs_ler boolean := false;
942 l_per_in_ler_id number;
943 l_dummy varchar2(30);
944 l_effective_date date;
945 l_processed_flag varchar2(30) := 'N';
946 l_element_end_date date;
947 l_element_start_date date;
948 l_recurring_rt boolean;
949 l_cmcd_rt_val number;
950 l_ann_rt_val number;
951 l_rt_val number;
952 l_recreated boolean := false;
953 l_rslt_suspended varchar2(30);
954 l_dummy_number number;
955 --
956 begin
957 --
958 g_debug := hr_utility.debug_enabled;
959 if g_debug then
960 hr_utility.set_location('Entering:'|| l_proc, 10);
961 end if;
962 --
963 -- Issue a savepoint if operating in validation only mode
964 --
965 savepoint update_prtt_rt_val;
966 --
967 if g_debug then
968 hr_utility.set_location(l_proc, 20);
969 end if;
970 --
971 -- Process Logic
972 --
973 l_object_version_number := p_object_version_number;
974 --
975 -- Get the old values before any changes or calls
976 --
977 open c_old_prv;
978 fetch c_old_prv into
979 l_old_rt_end_dt,
980 l_old_rt_strt_dt,
981 l_old_enrt_rslt_id,
982 l_old_element_entry_value_id,
983 l_old_abr_id,
984 l_old_rt_val,
985 l_old_cmcd_rt_val,
986 l_old_ann_rt_val,
987 l_old_acty_ref_perd_cd,
988 l_old_per_in_ler_id,
989 l_old_prtt_rt_val_stat_cd
990 ;
991 if c_old_prv%notfound then
992 --
993 -- The primary key is invalid therefore we must error
994 --
995 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
996 fnd_message.raise_error;
997 end if;
998 close c_old_prv;
999 --
1000 if nvl(p_rt_strt_dt,hr_api.g_date) = hr_api.g_date then
1001 l_rt_strt_dt := l_old_rt_strt_dt;
1002 else
1003 l_rt_strt_dt := p_rt_strt_dt;
1004 end if;
1005
1006 l_rt_end_dt := p_rt_end_dt;
1007
1008 if nvl(p_prtt_rt_val_stat_cd,'XXX') <> 'BCKDT' then
1009 get_non_recurring_end_dt
1010 (p_rt_end_dt => l_rt_end_dt
1011 ,p_rt_strt_dt => l_rt_strt_dt
1012 ,p_acty_base_rt_id => p_acty_base_rt_id
1013 ,p_business_group_id => p_business_group_id
1014 ,p_recurring_rt => l_recurring_rt
1015 ,p_effective_date => p_effective_date) ;
1016 end if;
1017 --
1018 if l_rt_end_dt = hr_api.g_date then
1019 l_rt_end_dt := l_old_rt_end_dt;
1020 end if;
1021 --
1022 if p_rt_val = hr_api.g_number then
1023 l_rt_val := l_old_rt_val;
1024 else
1025 l_rt_val := p_rt_val;
1026 end if;
1027 --
1028 if p_cmcd_rt_val = hr_api.g_number then
1029 l_cmcd_rt_val := l_old_cmcd_rt_val;
1030 else
1031 l_cmcd_rt_val := p_cmcd_rt_val;
1032 end if;
1033 --
1034 if p_ann_rt_val = hr_api.g_number then
1035 l_ann_rt_val := l_old_ann_rt_val;
1036 else
1037 l_ann_rt_val := p_ann_rt_val;
1038 end if;
1039 --
1040 if not l_recurring_rt and
1041 l_old_element_entry_value_id is not null then
1042 --
1043 open c_ele_entry(l_old_element_entry_value_id,
1044 l_rt_strt_dt);
1045 fetch c_ele_entry into l_ele_rec ;
1046 close c_ele_entry;
1047 --
1048 -- For Non recurring rates if the processed_flag is Y raise an error and
1049 -- dont allow the user to modify the rates
1050 --
1051 --BUG 3476699 fixes. We shoould allow to change the rate from the
1052 --Override forms.
1053 --
1054 --if l_ele_rec.element_entry_id is not null then
1055 if ( (NVL(p_rt_ovridn_flag,'N') = 'N' OR
1056 (NVL(p_rt_ovridn_flag,'N') ='Y' AND
1057 NVL(p_rt_ovridn_thru_dt,hr_api.g_eot ) < l_rt_strt_dt
1058 )
1059 )
1060 AND l_ele_rec.element_entry_id is not null ) then
1061 --
1062 l_processed_flag := substr(pay_paywsmee_pkg.processed(
1063 l_ele_rec.element_entry_id,
1064 l_ele_rec.original_entry_id,
1065 l_ele_rec.processing_type,
1066 l_ele_rec.entry_type,
1067 l_rt_strt_dt), 1,1) ;
1068 --
1069 if l_processed_flag = 'Y' then
1070 --
1071 fnd_message.set_name ('BEN','BEN_93341_PRCCSD_IN_PAYROLL');
1072 fnd_message.raise_error;
1073 --
1074 end if;
1075 --
1076 end if;
1077 --
1078 end if;
1079 --
1080 -- check if the new rate start and end dates result in overlap
1081 --
1082 if (l_recurring_rt and
1083 (p_rt_strt_dt <> l_rt_strt_dt or
1084 p_rt_end_dt <> l_rt_end_dt )) then
1085
1086 chk_overlapping_dates
1087 (p_acty_base_rt_id => p_acty_base_rt_id
1088 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1089 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1090 ,p_new_rt_strt_dt => l_rt_strt_dt
1091 ,p_new_rt_end_dt => l_rt_end_dt);
1092
1093 end if;
1094 --
1095 begin
1096 --
1097 -- Start of API User Hook for the before hook of update_prtt_rt_val
1098 --
1099 ben_prtt_rt_val_bk2.update_prtt_rt_val_b
1100 (
1101 p_prtt_rt_val_id => p_prtt_rt_val_id
1102 ,p_rt_strt_dt => p_rt_strt_dt
1103 ,p_rt_end_dt => l_rt_end_dt
1104 ,p_rt_typ_cd => p_rt_typ_cd
1105 ,p_tx_typ_cd => p_tx_typ_cd
1106 ,p_ordr_num => p_ordr_num
1107 ,p_acty_typ_cd => p_acty_typ_cd
1108 ,p_mlt_cd => p_mlt_cd
1109 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1110 ,p_rt_val => p_rt_val
1111 ,p_ann_rt_val => p_ann_rt_val
1112 ,p_cmcd_rt_val => p_cmcd_rt_val
1113 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
1114 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
1115 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
1116 ,p_rt_ovridn_flag => p_rt_ovridn_flag
1117 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
1118 ,p_elctns_made_dt => p_elctns_made_dt
1119 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
1120 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1121 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
1122 ,p_actl_prem_id => p_actl_prem_id
1123 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
1124 ,p_element_entry_value_id => p_element_entry_value_id
1125 ,p_per_in_ler_id => p_per_in_ler_id
1126 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
1127 ,p_acty_base_rt_id => p_acty_base_rt_id
1128 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1129 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1130 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
1131 ,p_business_group_id => p_business_group_id
1132 ,p_prv_attribute_category => p_prv_attribute_category
1133 ,p_prv_attribute1 => p_prv_attribute1
1134 ,p_prv_attribute2 => p_prv_attribute2
1135 ,p_prv_attribute3 => p_prv_attribute3
1136 ,p_prv_attribute4 => p_prv_attribute4
1137 ,p_prv_attribute5 => p_prv_attribute5
1138 ,p_prv_attribute6 => p_prv_attribute6
1139 ,p_prv_attribute7 => p_prv_attribute7
1140 ,p_prv_attribute8 => p_prv_attribute8
1141 ,p_prv_attribute9 => p_prv_attribute9
1142 ,p_prv_attribute10 => p_prv_attribute10
1143 ,p_prv_attribute11 => p_prv_attribute11
1144 ,p_prv_attribute12 => p_prv_attribute12
1145 ,p_prv_attribute13 => p_prv_attribute13
1146 ,p_prv_attribute14 => p_prv_attribute14
1147 ,p_prv_attribute15 => p_prv_attribute15
1148 ,p_prv_attribute16 => p_prv_attribute16
1149 ,p_prv_attribute17 => p_prv_attribute17
1150 ,p_prv_attribute18 => p_prv_attribute18
1151 ,p_prv_attribute19 => p_prv_attribute19
1152 ,p_prv_attribute20 => p_prv_attribute20
1153 ,p_prv_attribute21 => p_prv_attribute21
1154 ,p_prv_attribute22 => p_prv_attribute22
1155 ,p_prv_attribute23 => p_prv_attribute23
1156 ,p_prv_attribute24 => p_prv_attribute24
1157 ,p_prv_attribute25 => p_prv_attribute25
1158 ,p_prv_attribute26 => p_prv_attribute26
1159 ,p_prv_attribute27 => p_prv_attribute27
1160 ,p_prv_attribute28 => p_prv_attribute28
1161 ,p_prv_attribute29 => p_prv_attribute29
1162 ,p_prv_attribute30 => p_prv_attribute30
1163 ,p_pk_id_table_name => p_pk_id_table_name
1164 ,p_pk_id => p_pk_id
1165 ,p_object_version_number => p_object_version_number
1166 ,p_effective_date => trunc(p_effective_date)
1167 );
1168 exception
1169 when hr_api.cannot_find_prog_unit then
1170 hr_api.cannot_find_prog_unit_error
1171 (p_module_name => 'UPDATE_prtt_rt_val'
1172 ,p_hook_type => 'BP'
1173 );
1174 --
1175 -- End of API User Hook for the before hook of update_prtt_rt_val
1176 --
1177 end;
1178 --
1179 ben_prv_upd.upd
1180 (
1181 p_prtt_rt_val_id => p_prtt_rt_val_id
1182 ,p_enrt_rt_id => p_enrt_rt_id
1183 ,p_rt_strt_dt => p_rt_strt_dt
1184 ,p_rt_end_dt => l_rt_end_dt
1185 ,p_rt_typ_cd => p_rt_typ_cd
1186 ,p_tx_typ_cd => p_tx_typ_cd
1187 ,p_ordr_num => p_ordr_num
1188 ,p_acty_typ_cd => p_acty_typ_cd
1189 ,p_mlt_cd => p_mlt_cd
1190 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1191 ,p_rt_val => p_rt_val
1192 ,p_ann_rt_val => p_ann_rt_val
1193 ,p_cmcd_rt_val => p_cmcd_rt_val
1194 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
1195 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
1196 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
1197 ,p_rt_ovridn_flag => p_rt_ovridn_flag
1198 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
1199 ,p_elctns_made_dt => p_elctns_made_dt
1200 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
1201 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1202 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
1203 ,p_actl_prem_id => p_actl_prem_id
1204 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
1205 ,p_element_entry_value_id => p_element_entry_value_id
1206 ,p_per_in_ler_id => p_per_in_ler_id
1207 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
1208 ,p_acty_base_rt_id => p_acty_base_rt_id
1209 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1210 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1211 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
1212 ,p_business_group_id => p_business_group_id
1213 ,p_prv_attribute_category => p_prv_attribute_category
1214 ,p_prv_attribute1 => p_prv_attribute1
1215 ,p_prv_attribute2 => p_prv_attribute2
1216 ,p_prv_attribute3 => p_prv_attribute3
1217 ,p_prv_attribute4 => p_prv_attribute4
1218 ,p_prv_attribute5 => p_prv_attribute5
1219 ,p_prv_attribute6 => p_prv_attribute6
1220 ,p_prv_attribute7 => p_prv_attribute7
1221 ,p_prv_attribute8 => p_prv_attribute8
1222 ,p_prv_attribute9 => p_prv_attribute9
1223 ,p_prv_attribute10 => p_prv_attribute10
1224 ,p_prv_attribute11 => p_prv_attribute11
1225 ,p_prv_attribute12 => p_prv_attribute12
1226 ,p_prv_attribute13 => p_prv_attribute13
1227 ,p_prv_attribute14 => p_prv_attribute14
1228 ,p_prv_attribute15 => p_prv_attribute15
1229 ,p_prv_attribute16 => p_prv_attribute16
1230 ,p_prv_attribute17 => p_prv_attribute17
1231 ,p_prv_attribute18 => p_prv_attribute18
1232 ,p_prv_attribute19 => p_prv_attribute19
1233 ,p_prv_attribute20 => p_prv_attribute20
1234 ,p_prv_attribute21 => p_prv_attribute21
1235 ,p_prv_attribute22 => p_prv_attribute22
1236 ,p_prv_attribute23 => p_prv_attribute23
1237 ,p_prv_attribute24 => p_prv_attribute24
1238 ,p_prv_attribute25 => p_prv_attribute25
1239 ,p_prv_attribute26 => p_prv_attribute26
1240 ,p_prv_attribute27 => p_prv_attribute27
1241 ,p_prv_attribute28 => p_prv_attribute28
1242 ,p_prv_attribute29 => p_prv_attribute29
1243 ,p_prv_attribute30 => p_prv_attribute30
1244 ,p_pk_id_table_name => p_pk_id_table_name
1245 ,p_pk_id => p_pk_id
1246 ,p_object_version_number => l_object_version_number
1247 ,p_effective_date => p_effective_date
1248 );
1249 --
1250 if g_debug then
1251 hr_utility.set_location('old entry value'||l_old_element_entry_value_id,11);
1252 hr_utility.set_location('prtt rt val id '||p_prtt_rt_val_id,11);
1253 hr_utility.set_location('rate start date '||l_rt_strt_dt,11);
1254 hr_utility.set_location('old rt strt date '||l_old_rt_strt_dt,12);
1255 hr_utility.set_location('rate end date '||l_rt_end_dt,12);
1256 hr_utility.set_location('old rt end date '||l_old_rt_end_dt,12);
1257 end if;
1258 --
1259 --BUG 3559005 We need to compate three values or
1260 --may bedependeding on the value passed to payroll code we need to
1261 --consider checking cmcd_rt_val and ann_rt_val
1262 --
1263 if l_rt_strt_dt <> l_old_rt_strt_dt or
1264 l_rt_end_dt <> l_old_rt_end_dt or
1265 nvl(l_rt_val,0) <> nvl(l_old_rt_val,0) or
1266 nvl(l_cmcd_rt_val,0) <> nvl(l_old_cmcd_rt_val,0) or
1267 nvl(l_ann_rt_val,0) <> nvl(l_old_ann_rt_val,0) then
1268
1269 /* Added if else condition for Bug 12576710. Modified the fix of Bug 10127267. If Rate EndDate is EOT,
1270 there will not exist any enrollment result*/
1271 if l_rt_end_dt <> hr_api.g_eot then
1272 l_rslt_suspended :=
1273 result_is_suspended(p_prtt_enrt_rslt_id => l_old_enrt_rslt_id,
1274 p_person_id => p_person_id,
1275 p_business_group_id => p_business_group_id,
1276 --
1277 -- 10127267 Change the effective date to rate end date. There are
1278 -- scenarios where the enrollment is unsuspended after the rate start
1279 -- date.
1280 --
1281 p_effective_date => l_rt_end_dt) ;
1282 else
1283 l_rslt_suspended :=
1284 result_is_suspended(p_prtt_enrt_rslt_id => l_old_enrt_rslt_id,
1285 p_person_id => p_person_id,
1286 p_business_group_id => p_business_group_id,
1287 p_effective_date => p_effective_date) ;
1288 end if;
1289 else
1290 --
1291 -- 12583091 If result is previously unsuspended, delete element entry.
1292 --
1293 hr_utility.set_location('p_prtt_enrt_rslt_id'||l_old_enrt_rslt_id,15);
1294 hr_utility.set_location('l_old_element_entry_value_id '||l_old_element_entry_value_id,15);
1295 --
1296 if (p_rt_end_dt = hr_api.g_eot
1297 and l_old_element_entry_value_id is not null) then
1298 open c_pen_curr(l_old_enrt_rslt_id);
1299 fetch c_pen_curr into l_pen_curr;
1300 close c_pen_curr;
1301 hr_utility.set_location('l_pen_curr.sspndd_flag '||l_pen_curr.sspndd_flag,150);
1302 if l_pen_curr.sspndd_flag = 'Y' then
1303 l_rslt_suspended := 'Y';
1304 end if;
1305 end if; -- end 12583091
1306 --
1307 end if;
1308 --
1309 -- Rate Start Date updates handled below
1310 --
1311 --BUG 3559005 We need to compate three values or
1312 --may bedependeding on the value passed to payroll code we need to
1313 --consider checking cmcd_rt_val and ann_rt_val
1314 --
1315 if l_rt_strt_dt <> l_old_rt_strt_dt or
1316 nvl(l_rt_val,0) <> nvl(l_old_rt_val,0) or
1317 nvl(l_cmcd_rt_val,0) <> nvl(l_old_cmcd_rt_val,0) or
1318 nvl(l_ann_rt_val,0) <> nvl(l_old_ann_rt_val,0) or
1319 l_pen_curr.sspndd_flag = 'Y' then -- 12583091
1320 --
1321 -- end entry one day before. Will not use rate end code here
1322 -- as the prv is not the one getting end dated
1323 --
1324 if g_debug then
1325 hr_utility.set_location('Inside re-create',13);
1326 end if;
1327
1328 ben_element_entry.end_enrollment_element
1329 (p_business_group_id => p_business_group_id
1330 ,p_person_id => p_person_id
1331 ,p_enrt_rslt_id => l_old_enrt_rslt_id
1332 ,p_acty_ref_perd => l_old_acty_ref_perd_cd
1333 ,p_element_link_id => null
1334 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1335 ,p_rt_end_date => l_old_rt_strt_dt -1
1336 ,p_effective_date => p_effective_date
1337 ,p_dt_delete_mode => null
1338 ,p_acty_base_rt_id => l_old_abr_id
1339 ,p_amt => l_old_rt_val
1340 );
1341 --
1342 if l_rslt_suspended = 'N' then
1343 ben_element_entry.create_enrollment_element
1344 (p_business_group_id => p_business_group_id
1345 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1346 ,p_person_id => p_person_id
1347 ,p_acty_ref_perd => l_old_acty_ref_perd_cd
1348 ,p_acty_base_rt_id => l_old_abr_id
1349 ,p_enrt_rslt_id => l_old_enrt_rslt_id
1350 ,p_rt_start_date => l_rt_strt_dt
1351 ,p_rt => l_rt_val
1352 ,p_cmncd_rt => l_cmcd_rt_val
1353 ,p_ann_rt => l_ann_rt_val
1354 ,p_prv_object_version_number=> l_object_version_number
1355 ,p_effective_date => p_effective_date
1356 ,p_eev_screen_entry_value => l_dummy_number
1357 ,p_element_entry_value_id => l_dummy_number
1358 );
1359 l_recreated := l_recurring_rt;
1360 ben_prv_shd.lck
1361 (
1362 p_prtt_rt_val_id => p_prtt_rt_val_id
1363 ,p_object_version_number => l_object_version_number
1364 );
1365 end if;
1366 end if;
1367 --
1368 -- Rate End Date updates handled below
1369 --
1370 if not l_recreated and
1371 l_rt_end_dt > l_old_rt_end_dt and
1372 l_rslt_suspended = 'N' then
1373 --
1374 -- Bug 5012222. Special case here. Backed out prv re-opened. Do not reopen
1375 -- the previous entry. Rate may have changed.
1376 -- Call create_enrollment_element in this case.
1377 --
1378 if p_prtt_rt_val_stat_cd is null and
1379 l_old_prtt_rt_val_stat_cd = 'BCKDT' then
1380
1381 ben_element_entry.create_enrollment_element
1382 (p_business_group_id => p_business_group_id
1383 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1384 ,p_person_id => p_person_id
1385 ,p_acty_ref_perd => l_old_acty_ref_perd_cd
1386 ,p_acty_base_rt_id => l_old_abr_id
1387 ,p_enrt_rslt_id => l_old_enrt_rslt_id
1388 ,p_rt_start_date => l_rt_strt_dt
1389 ,p_rt => l_rt_val
1390 ,p_cmncd_rt => l_cmcd_rt_val
1391 ,p_ann_rt => l_ann_rt_val
1392 ,p_prv_object_version_number=> l_object_version_number
1393 ,p_effective_date => p_effective_date
1394 ,p_eev_screen_entry_value => l_dummy_number
1395 ,p_element_entry_value_id => l_dummy_number
1396 );
1397 ben_prv_shd.lck
1398 (
1399 p_prtt_rt_val_id => p_prtt_rt_val_id
1400 ,p_object_version_number => l_object_version_number
1401 );
1402 else
1403 --
1404 -- Rate reopened. If rslt is suspended, Element entry will be reopened
1405 -- when it gets unsuspended
1406 --
1407 ben_element_entry.reopen_closed_enrollment
1408 (p_business_group_id => p_business_group_id
1409 ,p_person_id => p_person_id
1410 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1411 ,p_acty_base_rt_id => l_old_abr_id
1412 ,p_element_type_id => null
1413 ,p_input_value_id => null
1414 ,p_rt => null
1415 ,p_rt_start_date => l_old_rt_strt_dt
1416 ,p_effective_date => p_effective_date
1417 );
1418 --
1419 -- This is temporary. Need to add ovn param to
1420 -- reopen_closed_enrollment
1421 --
1422 open c_prv_ovn;
1423 fetch c_prv_ovn into l_object_version_number;
1424 close c_prv_ovn;
1425
1426 end if;
1427 end if;
1428
1429 if not p_no_end_element and
1430 (l_rt_end_dt < l_rt_strt_dt or
1431 (
1432 (l_recurring_rt and l_rt_end_dt <> hr_api.g_eot) and
1433 (l_rt_end_dt <> l_old_rt_end_dt or l_recreated)
1434 )
1435 ) then
1436
1437 ben_element_entry.end_enrollment_element
1438 (p_business_group_id => p_business_group_id
1439 ,p_person_id => p_person_id
1440 ,p_enrt_rslt_id => l_old_enrt_rslt_id
1441 ,p_acty_ref_perd => l_old_acty_ref_perd_cd
1442 ,p_element_link_id => null
1443 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1444 ,p_rt_end_date => l_rt_end_dt
1445 ,p_effective_date => p_effective_date
1446 ,p_dt_delete_mode => null
1447 ,p_acty_base_rt_id => l_old_abr_id
1448 ,p_amt => l_rt_val
1449 );
1450 end if;
1451 --
1452 begin
1453 --
1454 -- Start of API User Hook for the after hook of update_prtt_rt_val
1455 --
1456 ben_prtt_rt_val_bk2.update_prtt_rt_val_a
1457 (
1458 p_prtt_rt_val_id => p_prtt_rt_val_id
1459 ,p_rt_strt_dt => p_rt_strt_dt
1460 ,p_rt_end_dt => l_rt_end_dt
1461 ,p_rt_typ_cd => p_rt_typ_cd
1462 ,p_tx_typ_cd => p_tx_typ_cd
1463 ,p_ordr_num => p_ordr_num
1464 ,p_acty_typ_cd => p_acty_typ_cd
1465 ,p_mlt_cd => p_mlt_cd
1466 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1467 ,p_rt_val => p_rt_val
1468 ,p_ann_rt_val => p_ann_rt_val
1469 ,p_cmcd_rt_val => p_cmcd_rt_val
1470 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
1471 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
1472 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
1473 ,p_rt_ovridn_flag => p_rt_ovridn_flag
1474 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
1475 ,p_elctns_made_dt => p_elctns_made_dt
1476 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
1477 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1478 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
1479 ,p_actl_prem_id => p_actl_prem_id
1480 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
1481 ,p_element_entry_value_id => p_element_entry_value_id
1482 ,p_per_in_ler_id => p_per_in_ler_id
1483 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
1484 ,p_acty_base_rt_id => p_acty_base_rt_id
1485 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1486 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1487 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
1488 ,p_business_group_id => p_business_group_id
1489 ,p_prv_attribute_category => p_prv_attribute_category
1490 ,p_prv_attribute1 => p_prv_attribute1
1491 ,p_prv_attribute2 => p_prv_attribute2
1492 ,p_prv_attribute3 => p_prv_attribute3
1493 ,p_prv_attribute4 => p_prv_attribute4
1494 ,p_prv_attribute5 => p_prv_attribute5
1495 ,p_prv_attribute6 => p_prv_attribute6
1496 ,p_prv_attribute7 => p_prv_attribute7
1497 ,p_prv_attribute8 => p_prv_attribute8
1498 ,p_prv_attribute9 => p_prv_attribute9
1499 ,p_prv_attribute10 => p_prv_attribute10
1500 ,p_prv_attribute11 => p_prv_attribute11
1501 ,p_prv_attribute12 => p_prv_attribute12
1502 ,p_prv_attribute13 => p_prv_attribute13
1503 ,p_prv_attribute14 => p_prv_attribute14
1504 ,p_prv_attribute15 => p_prv_attribute15
1505 ,p_prv_attribute16 => p_prv_attribute16
1506 ,p_prv_attribute17 => p_prv_attribute17
1507 ,p_prv_attribute18 => p_prv_attribute18
1508 ,p_prv_attribute19 => p_prv_attribute19
1509 ,p_prv_attribute20 => p_prv_attribute20
1510 ,p_prv_attribute21 => p_prv_attribute21
1511 ,p_prv_attribute22 => p_prv_attribute22
1512 ,p_prv_attribute23 => p_prv_attribute23
1513 ,p_prv_attribute24 => p_prv_attribute24
1514 ,p_prv_attribute25 => p_prv_attribute25
1515 ,p_prv_attribute26 => p_prv_attribute26
1516 ,p_prv_attribute27 => p_prv_attribute27
1517 ,p_prv_attribute28 => p_prv_attribute28
1518 ,p_prv_attribute29 => p_prv_attribute29
1519 ,p_prv_attribute30 => p_prv_attribute30
1520 ,p_pk_id_table_name => p_pk_id_table_name
1521 ,p_pk_id => p_pk_id
1522 ,p_object_version_number => l_object_version_number
1523 ,p_effective_date => trunc(p_effective_date)
1524 );
1525 exception
1526 when hr_api.cannot_find_prog_unit then
1527 hr_api.cannot_find_prog_unit_error
1528 (p_module_name => 'UPDATE_prtt_rt_val'
1529 ,p_hook_type => 'AP'
1530 );
1531 --
1532 -- End of API User Hook for the after hook of update_prtt_rt_val
1533 --
1534 end;
1535 --
1536 if g_debug then
1537 hr_utility.set_location(l_proc, 60);
1538 end if;
1539 --
1540 -- When in validation only mode raise the Validate_Enabled exception
1541 --
1542 if p_validate then
1543 raise hr_api.validate_enabled;
1544 end if;
1545 --
1546 -- Set all output arguments
1547 --
1548 p_object_version_number := l_object_version_number;
1549 --
1550 if g_debug then
1551 hr_utility.set_location(' Leaving:'||l_proc, 70);
1552 end if;
1553 --
1554 exception
1555 --
1556 when hr_api.validate_enabled then
1557 --
1558 -- As the Validate_Enabled exception has been raised
1559 -- we must rollback to the savepoint
1560 --
1561 ROLLBACK TO update_prtt_rt_val;
1562 --
1563 -- Only set output warning arguments
1564 -- (Any key or derived arguments must be set to null
1565 -- when validation only mode is being used.)
1566 --
1567 if g_debug then
1568 hr_utility.set_location(' Leaving:'||l_proc, 80);
1569 end if;
1570 --
1571 when others then
1572 --
1573 -- A validation or unexpected error has occured
1574 --
1575 ROLLBACK TO update_prtt_rt_val;
1576 raise;
1577 --
1578 end update_prtt_rt_val;
1579 -- ---------------------------------------------------------------------------
1580 -- |------------------------< delete_prtt_rt_val >---------------------------|
1581 -- ---------------------------------------------------------------------------
1582 --
1583 procedure delete_prtt_rt_val
1584 (p_validate in boolean default false
1585 ,p_prtt_rt_val_id in number
1586 ,p_enrt_rt_id in number default null
1587 ,p_person_id in number
1588 ,p_business_group_id in number
1589 ,p_object_version_number in out nocopy number
1590 ,p_effective_date in date
1591 ) is
1592 --
1593 -- Declare cursors and local variables
1594 --
1595 cursor c_old_prv is
1596 select prv.rt_end_dt,
1597 prv.rt_strt_dt,
1598 prv.prtt_enrt_rslt_id,
1599 prv.element_entry_value_id,
1600 prv.acty_base_rt_id,
1601 prv.rt_val,
1602 prv.acty_ref_perd_cd
1603 from ben_prtt_rt_val prv
1604 where prv.prtt_rt_val_id=p_prtt_rt_val_id and
1605 prv.business_group_id=p_business_group_id;
1606 --
1607 cursor c_enrt_rt (p_prtt_rt_val_id number) is
1608 select enrt_rt_id,
1609 object_version_number
1610 from ben_enrt_rt
1611 where prtt_rt_val_id = p_prtt_rt_val_id;
1612 --
1613 l_proc varchar2(72); -- := g_package||'delete_prtt_rt_val';
1614 l_object_version_number ben_prtt_rt_val.object_version_number%TYPE;
1615 l_enrt_rt_ovn number;
1616 l_old_rt_end_dt date;
1617 l_old_rt_strt_dt date;
1618 l_old_enrt_rslt_id number;
1619 l_old_element_link_id number;
1620 l_old_element_entry_value_id number;
1621 l_old_abr_id number;
1622 l_old_rt_val number;
1623 l_old_acty_ref_perd_cd varchar2(30);
1624 l_enrt_rt_id number;
1625
1626 begin
1627 --
1628 g_debug := hr_utility.debug_enabled;
1629
1630 if g_debug then
1631 l_proc := g_package||'delete_prtt_rt_val';
1632 hr_utility.set_location('Entering:'|| l_proc, 10);
1633 end if;
1634 --
1635 -- Issue a savepoint if operating in validation only mode
1636 --
1637 savepoint delete_prtt_rt_val;
1638 --
1639 if g_debug then
1640 hr_utility.set_location(l_proc, 20);
1641 end if;
1642 --
1643 -- Get the old values before any changes or calls
1644 --
1645 open c_old_prv;
1646 fetch c_old_prv into
1647 l_old_rt_end_dt,
1648 l_old_rt_strt_dt,
1649 l_old_enrt_rslt_id,
1650 l_old_element_entry_value_id,
1651 l_old_abr_id,
1652 l_old_rt_val,
1653 l_old_acty_ref_perd_cd ;
1654 if c_old_prv%notfound then
1655 --
1656 -- The primary key is invalid therefore we must error
1657 --
1658 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1659 fnd_message.raise_error;
1660 end if;
1661 close c_old_prv;
1662 if g_debug then
1663 hr_utility.set_location(l_proc, 22);
1664 end if;
1665
1666 l_object_version_number := p_object_version_number;
1667 --
1668 --
1669 begin
1670 --
1671 -- Start of API User Hook for the before hook of delete_prtt_rt_val
1672 --
1673 ben_prtt_rt_val_bk3.delete_prtt_rt_val_b
1674 (
1675 p_prtt_rt_val_id => p_prtt_rt_val_id
1676 ,p_object_version_number => p_object_version_number
1677 ,p_effective_date => trunc(p_effective_date)
1678 );
1679 exception
1680 when hr_api.cannot_find_prog_unit then
1681 hr_api.cannot_find_prog_unit_error
1682 (p_module_name => 'DELETE_prtt_rt_val'
1683 ,p_hook_type => 'BP'
1684 );
1685 --
1686 -- End of API User Hook for the before hook of delete_prtt_rt_val
1687 --
1688 end;
1689 --
1690 -- Get the object version number for the update
1691 -- Do it only if null is not passed.
1692 --
1693 if p_enrt_rt_id is not null then
1694 -- bug#5378504
1695 open c_enrt_rt (p_prtt_rt_val_id);
1696 loop
1697 fetch c_enrt_rt into l_enrt_rt_id, l_enrt_rt_ovn;
1698 if c_enrt_rt%notfound then
1699 exit;
1700 end if;
1701 /*
1702 l_enrt_rt_ovn:=
1703 dt_api.get_object_version_number
1704 (p_base_table_name => 'ben_enrt_rt',
1705 p_base_key_column => 'enrt_rt_id',
1706 p_base_key_value => p_enrt_rt_id)-1;
1707 --
1708 */
1709
1710 ben_enrollment_rate_api.update_enrollment_rate(
1711 p_enrt_rt_id => l_enrt_rt_id,
1712 p_prtt_rt_val_id => null,
1713 p_object_version_number => l_enrt_rt_ovn,
1714 p_effective_date => p_effective_date
1715 );
1716 end loop;
1717 close c_enrt_rt;
1718 end if;
1719 --
1720 if l_old_element_entry_value_id is not null then
1721
1722 l_old_rt_end_dt := l_old_rt_strt_dt - 1;
1723
1724 ben_element_entry.end_enrollment_element
1725 (p_business_group_id => p_business_group_id
1726 ,p_person_id => p_person_id
1727 ,p_enrt_rslt_id => l_old_enrt_rslt_id
1728 ,p_acty_ref_perd => l_old_acty_ref_perd_cd
1729 ,p_element_link_id => null
1730 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1731 ,p_rt_end_date => l_old_rt_end_dt
1732 ,p_effective_date => l_old_rt_strt_dt
1733 ,p_dt_delete_mode => null
1734 ,p_acty_base_rt_id => l_old_abr_id
1735 ,p_amt => l_old_rt_val);
1736
1737 end if;
1738 --
1739
1740 ben_prv_del.del
1741 (
1742 p_prtt_rt_val_id => p_prtt_rt_val_id
1743 ,p_object_version_number => l_object_version_number
1744 );
1745 --
1746 begin
1747 --
1748 -- Start of API User Hook for the after hook of delete_prtt_rt_val
1749 --
1750 ben_prtt_rt_val_bk3.delete_prtt_rt_val_a
1751 (
1752 p_prtt_rt_val_id => p_prtt_rt_val_id
1753 ,p_object_version_number => l_object_version_number
1754 ,p_effective_date => trunc(p_effective_date)
1755 );
1756 exception
1757 when hr_api.cannot_find_prog_unit then
1758 hr_api.cannot_find_prog_unit_error
1759 (p_module_name => 'DELETE_prtt_rt_val'
1760 ,p_hook_type => 'AP'
1761 );
1762 --
1763 -- End of API User Hook for the after hook of delete_prtt_rt_val
1764 --
1765 end;
1766 --
1767 if g_debug then
1768 hr_utility.set_location(l_proc, 60);
1769 end if;
1770 --
1771 -- When in validation only mode raise the Validate_Enabled exception
1772 --
1773 if p_validate then
1774 raise hr_api.validate_enabled;
1775 end if;
1776 --
1777 if g_debug then
1778 hr_utility.set_location(' Leaving:'||l_proc, 70);
1779 end if;
1780 --
1781 exception
1782 --
1783 when hr_api.validate_enabled then
1784 --
1785 -- As the Validate_Enabled exception has been raised
1786 -- we must rollback to the savepoint
1787 --
1788 ROLLBACK TO delete_prtt_rt_val;
1789 --
1790 -- Only set output warning arguments
1791 -- (Any key or derived arguments must be set to null
1792 -- when validation only mode is being used.)
1793 --
1794 --
1795 when others then
1796 --
1797 -- A validation or unexpected error has occured
1798 --
1799 ROLLBACK TO delete_prtt_rt_val;
1800 raise;
1801 --
1802 end delete_prtt_rt_val;
1803 --
1804 -- ---------------------------------------------------------------------------
1805 -- |-------------------------------< lck >-----------------------------------|
1806 -- ---------------------------------------------------------------------------
1807 --
1808 procedure lck
1809 (
1810 p_prtt_rt_val_id in number
1811 ,p_object_version_number in number
1812 ) is
1813 --
1814 --
1815 -- Declare cursors and local variables
1816 --
1817 l_proc varchar2(72) := g_package||'lck';
1818 --
1819 begin
1820 --
1821 if g_debug then
1822 hr_utility.set_location('Entering:'|| l_proc, 10);
1823 end if;
1824 --
1825 ben_prv_shd.lck
1826 (
1827 p_prtt_rt_val_id => p_prtt_rt_val_id
1828 ,p_object_version_number => p_object_version_number
1829 );
1830 --
1831 if g_debug then
1832 hr_utility.set_location(' Leaving:'||l_proc, 70);
1833 end if;
1834 --
1835 end lck;
1836 --
1837 end ben_prtt_rt_val_api;