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