[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_PERSON_RATES_API
Source
1 Package Body BEN_CWB_PERSON_RATES_API as
2 /* $Header: bertsapi.pkb 120.5.12000000.1 2007/01/19 23:09:06 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' BEN_CWB_PERSON_RATES_API.';
7 g_debug boolean := hr_utility.debug_enabled;
8 cursor csr_rates(v_group_per_in_ler_id number
9 ,v_pl_id number
10 ,v_oipl_id number) is
11 select *
12 from ben_cwb_person_rates
13 where group_per_in_ler_id = v_group_per_in_ler_id
14 and pl_id = v_pl_id
15 and oipl_id = v_oipl_id;
16 --
17 --
18 -- ----------------------------------------------------------------------------
19 -- |--------------------------< create_person_rate >--------------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 procedure create_person_rate
23 (p_validate in boolean default false
24 ,p_group_per_in_ler_id in number
25 ,p_pl_id in number
26 ,p_oipl_id in number
27 ,p_group_pl_id in number
28 ,p_group_oipl_id in number
29 ,p_lf_evt_ocrd_dt in date
30 ,p_person_id in number default null
31 ,p_assignment_id in number default null
32 ,p_elig_flag in varchar2 default null
33 ,p_ws_val in number default null
34 ,p_ws_mn_val in number default null
38 ,p_stat_sal_val in number default null
35 ,p_ws_mx_val in number default null
36 ,p_ws_incr_val in number default null
37 ,p_elig_sal_val in number default null
39 ,p_oth_comp_val in number default null
40 ,p_tot_comp_val in number default null
41 ,p_misc1_val in number default null
42 ,p_misc2_val in number default null
43 ,p_misc3_val in number default null
44 ,p_rec_val in number default null
45 ,p_rec_mn_val in number default null
46 ,p_rec_mx_val in number default null
47 ,p_rec_incr_val in number default null
48 ,p_ws_val_last_upd_date in date default null
49 ,p_ws_val_last_upd_by in number default null
50 ,p_pay_proposal_id in number default null
51 ,p_element_entry_value_id in number default null
52 ,p_inelig_rsn_cd in varchar2 default null
53 ,p_elig_ovrid_dt in date default null
54 ,p_elig_ovrid_person_id in number default null
55 ,p_copy_dist_bdgt_val in number default null
56 ,p_copy_ws_bdgt_val in number default null
57 ,p_copy_rsrv_val in number default null
58 ,p_copy_dist_bdgt_mn_val in number default null
59 ,p_copy_dist_bdgt_mx_val in number default null
60 ,p_copy_dist_bdgt_incr_val in number default null
61 ,p_copy_ws_bdgt_mn_val in number default null
62 ,p_copy_ws_bdgt_mx_val in number default null
63 ,p_copy_ws_bdgt_incr_val in number default null
64 ,p_copy_rsrv_mn_val in number default null
65 ,p_copy_rsrv_mx_val in number default null
66 ,p_copy_rsrv_incr_val in number default null
67 ,p_copy_dist_bdgt_iss_val in number default null
68 ,p_copy_ws_bdgt_iss_val in number default null
69 ,p_copy_dist_bdgt_iss_date in date default null
70 ,p_copy_ws_bdgt_iss_date in date default null
71 ,p_comp_posting_date in date default null
72 ,p_ws_rt_start_date in date default null
73 ,p_currency in varchar2 default null
74 ,p_object_version_number out nocopy number
75 ,p_person_rate_id out nocopy number
76 ) is
77 --
78 -- Declare cursors and local variables
79 --
80 l_person_rate_id ben_cwb_person_rates.person_rate_id%type;
81 l_object_version_number number;
82 --
83 l_proc varchar2(72) := g_package||'create_person_rate';
84 begin
85 if g_debug then
86 hr_utility.set_location('Entering:'|| l_proc, 10);
87 end if;
88 --
89 -- Issue a savepoint
90 --
91 savepoint create_person_rate;
92 --
93 -- Call Before Process User Hook
94 --
95 begin
96 ben_cwb_person_rates_bk1.create_person_rate_b
97 (p_group_per_in_ler_id => p_group_per_in_ler_id
98 ,p_pl_id => p_pl_id
99 ,p_oipl_id => p_oipl_id
100 ,p_group_pl_id => p_group_pl_id
101 ,p_group_oipl_id => p_group_oipl_id
102 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
103 ,p_person_id => p_person_id
104 ,p_assignment_id => p_assignment_id
105 ,p_elig_flag => p_elig_flag
106 ,p_ws_val => p_ws_val
107 ,p_ws_mn_val => p_ws_mn_val
108 ,p_ws_mx_val => p_ws_mx_val
109 ,p_ws_incr_val => p_ws_incr_val
110 ,p_elig_sal_val => p_elig_sal_val
111 ,p_stat_sal_val => p_stat_sal_val
112 ,p_oth_comp_val => p_oth_comp_val
113 ,p_tot_comp_val => p_tot_comp_val
114 ,p_misc1_val => p_misc1_val
115 ,p_misc2_val => p_misc2_val
116 ,p_misc3_val => p_misc3_val
117 ,p_rec_val => p_rec_val
118 ,p_rec_mn_val => p_rec_mn_val
119 ,p_rec_mx_val => p_rec_mx_val
120 ,p_rec_incr_val => p_rec_incr_val
121 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
122 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
123 ,p_pay_proposal_id => p_pay_proposal_id
124 ,p_element_entry_value_id => p_element_entry_value_id
125 ,p_inelig_rsn_cd => p_inelig_rsn_cd
126 ,p_elig_ovrid_dt => p_elig_ovrid_dt
127 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
128 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
129 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
130 ,p_copy_rsrv_val => p_copy_rsrv_val
131 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
132 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
136 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
133 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
134 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
135 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
137 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
138 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
139 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
140 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
141 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
142 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
143 ,p_comp_posting_date => p_comp_posting_date
144 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
145 ,p_ws_rt_start_date => p_ws_rt_start_date
146 ,p_currency => p_currency
147 );
148 exception
149 when hr_api.cannot_find_prog_unit then
150 hr_api.cannot_find_prog_unit_error
151 (p_module_name => 'CREATE_PERSON_RATE'
152 ,p_hook_type => 'BP'
153 );
154 end;
155 --
156 -- Validation in addition to Row Handlers
157 --
158
159 --
160 -- Process Logic
161 --
162 ben_rts_ins.ins
163 (p_group_per_in_ler_id => p_group_per_in_ler_id
164 ,p_pl_id => p_pl_id
165 ,p_oipl_id => p_oipl_id
166 ,p_group_pl_id => p_group_pl_id
167 ,p_group_oipl_id => p_group_oipl_id
168 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
169 ,p_person_id => p_person_id
170 ,p_assignment_id => p_assignment_id
171 ,p_elig_flag => p_elig_flag
172 ,p_ws_val => p_ws_val
173 ,p_ws_mn_val => p_ws_mn_val
174 ,p_ws_mx_val => p_ws_mx_val
175 ,p_ws_incr_val => p_ws_incr_val
176 ,p_elig_sal_val => p_elig_sal_val
177 ,p_stat_sal_val => p_stat_sal_val
178 ,p_oth_comp_val => p_oth_comp_val
179 ,p_tot_comp_val => p_tot_comp_val
180 ,p_misc1_val => p_misc1_val
181 ,p_misc2_val => p_misc2_val
182 ,p_misc3_val => p_misc3_val
183 ,p_rec_val => p_rec_val
184 ,p_rec_mn_val => p_rec_mn_val
185 ,p_rec_mx_val => p_rec_mx_val
186 ,p_rec_incr_val => p_rec_incr_val
187 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
188 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
189 ,p_pay_proposal_id => p_pay_proposal_id
190 ,p_element_entry_value_id => p_element_entry_value_id
191 ,p_inelig_rsn_cd => p_inelig_rsn_cd
192 ,p_elig_ovrid_dt => p_elig_ovrid_dt
193 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
194 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
195 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
196 ,p_copy_rsrv_val => p_copy_rsrv_val
197 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
198 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
199 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
200 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
201 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
202 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
203 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
204 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
205 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
206 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
207 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
208 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
209 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
210 ,p_comp_posting_date => p_comp_posting_date
211 ,p_ws_rt_start_date => p_ws_rt_start_date
212 ,p_currency => p_currency
213 ,p_object_version_number => l_object_version_number
214 ,p_person_rate_id => l_person_rate_id
215 );
216 --
217 -- Call After Process User Hook
218 --
219 begin
220 ben_cwb_person_rates_bk1.create_person_rate_a
221 (p_person_rate_id => l_person_rate_id
222 ,p_group_per_in_ler_id => p_group_per_in_ler_id
223 ,p_pl_id => p_pl_id
224 ,p_oipl_id => p_oipl_id
225 ,p_group_pl_id => p_group_pl_id
226 ,p_group_oipl_id => p_group_oipl_id
227 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
228 ,p_person_id => p_person_id
229 ,p_assignment_id => p_assignment_id
230 ,p_elig_flag => p_elig_flag
234 ,p_ws_incr_val => p_ws_incr_val
231 ,p_ws_val => p_ws_val
232 ,p_ws_mn_val => p_ws_mn_val
233 ,p_ws_mx_val => p_ws_mx_val
235 ,p_elig_sal_val => p_elig_sal_val
236 ,p_stat_sal_val => p_stat_sal_val
237 ,p_oth_comp_val => p_oth_comp_val
238 ,p_tot_comp_val => p_tot_comp_val
239 ,p_misc1_val => p_misc1_val
240 ,p_misc2_val => p_misc2_val
241 ,p_misc3_val => p_misc3_val
242 ,p_rec_val => p_rec_val
243 ,p_rec_mn_val => p_rec_mn_val
244 ,p_rec_mx_val => p_rec_mx_val
245 ,p_rec_incr_val => p_rec_incr_val
246 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
247 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
248 ,p_pay_proposal_id => p_pay_proposal_id
249 ,p_element_entry_value_id => p_element_entry_value_id
250 ,p_inelig_rsn_cd => p_inelig_rsn_cd
251 ,p_elig_ovrid_dt => p_elig_ovrid_dt
252 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
253 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
254 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
255 ,p_copy_rsrv_val => p_copy_rsrv_val
256 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
257 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
258 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
259 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
260 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
264 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
261 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
262 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
263 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
265 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
266 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
267 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
268 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
269 ,p_comp_posting_date => p_comp_posting_date
270 ,p_ws_rt_start_date => p_ws_rt_start_date
271 ,p_currency => p_currency
272 ,p_object_version_number => l_object_version_number
273 );
274 exception
275 when hr_api.cannot_find_prog_unit then
276 hr_api.cannot_find_prog_unit_error
277 (p_module_name => 'CREATE_PERSON_RATE'
278 ,p_hook_type => 'AP'
279 );
280 end;
281 --
282 -- When in validation only mode raise the Validate_Enabled exception
283 --
284 if p_validate then
285 raise hr_api.validate_enabled;
286 end if;
287 --
288 -- Set all IN OUT and OUT parameters with out values
289 --
290 p_person_rate_id := l_person_rate_id;
291 p_object_version_number := l_object_version_number;
292 --
293 if g_debug then
294 hr_utility.set_location(' Leaving:'||l_proc, 70);
295 end if;
296 exception
297 when hr_api.validate_enabled then
298 --
299 -- As the Validate_Enabled exception has been raised
300 -- we must rollback to the savepoint
301 --
302 rollback to create_person_rate;
303 --
304 if g_debug then
305 hr_utility.set_location(' Leaving:'||l_proc, 80);
306 end if;
307 when others then
308 --
309 -- A validation or unexpected error has occured
310 --
311 rollback to create_person_rate;
312 --
313 if g_debug then
314 hr_utility.set_location(' Leaving:'||l_proc, 90);
315 end if;
316 raise;
317 end create_person_rate;
318 --
319 -- --------------------------------------------------------------------------
320 -- |----------------------------< check_min_max >----------------------------|
321 -- --------------------------------------------------------------------------
322 procedure check_min_max(p_val in number
323 ,p_min_val in number
324 ,p_max_val in number
325 ,p_incr_val in number
326 ,p_group_per_in_ler_id number)
327 is
328 --
329 l_person_name varchar2(240);
330 --
331 l_proc varchar2(72) := g_package||'check_min_max';
332 --
333 Begin
337 hr_utility.set_location('p_val :'||p_val, 10);
334 --
335 if g_debug then
336 hr_utility.set_location('Entering:'||l_proc, 10);
338 hr_utility.set_location('p_min_val :'||p_min_val, 10);
339 hr_utility.set_location('p_max_val :'||p_max_val, 10);
340 hr_utility.set_location('p_incr_val :'||p_incr_val, 10);
341 end if;
342 --
343 select full_name into l_person_name
344 from ben_cwb_person_info
345 where group_per_in_ler_id = p_group_per_in_ler_id;
346 --
347 --
348 if g_debug then
349 hr_utility.set_location(l_proc, 20);
350 end if;
351 --
352 if (p_val is not null and p_val <> hr_api.g_number and
353 p_min_val is not null) then
354 if (p_val < p_min_val) then
355 fnd_message.set_name('BEN','BEN_92984_CWB_VAL_NOT_IN_RANGE');
356 fnd_message.set_token('VAL',p_val);
357 fnd_message.set_token('MIN',p_min_val);
358 fnd_message.set_token('MAX',p_max_val);
359 fnd_message.set_token('PERSON',l_person_name);
360 fnd_message.raise_error;
361 end if;
362 end if;
363 --
364 --
365 if g_debug then
366 hr_utility.set_location(l_proc, 30);
367 end if;
368 --
369 if (p_val is not null and p_val <> hr_api.g_number and
370 p_max_val is not null) then
371 if (p_val > p_max_val) then
372 fnd_message.set_name('BEN','BEN_92984_CWB_VAL_NOT_IN_RANGE');
373 fnd_message.set_token('VAL',p_val);
374 fnd_message.set_token('MIN',p_min_val);
375 fnd_message.set_token('MAX',p_max_val);
376 fnd_message.set_token('PERSON',l_person_name);
377 fnd_message.raise_error;
378 end if;
379 end if;
380 --
381 if g_debug then
382 hr_utility.set_location(l_proc, 40);
383 end if;
384 --
385 if (p_val is not null and p_val <> hr_api.g_number and
386 p_incr_val is not null) then
387 if (mod(p_val,p_incr_val) <> 0) then
388 fnd_message.set_name('BEN','BEN_92985_CWB_VAL_NOT_INCRMNT');
389 fnd_message.set_token('VAL',p_val);
390 fnd_message.set_token('INCREMENT', p_incr_val);
391 fnd_message.set_token('PERSON',l_person_name);
392 fnd_message.raise_error;
393 end if;
394 end if;
395 if g_debug then
396 hr_utility.set_location(l_proc, 70);
397 end if;
398 --
399 if g_debug then
400 hr_utility.set_location(' Leaving:'||l_proc, 90);
401 end if;
402 end check_min_max;
403 --
404 --
405 -- --------------------------------------------------------------------------
406 -- |---------------------< update_person_rates_summary >---------------------|
407 -- --------------------------------------------------------------------------
408 -- Description
409 -- This is an internal procedure called only by update_person_rates to
410 -- update the summary table after updating the ben_cwb_person_rates.
411 --
412 procedure update_rates_summary(p_rates_old csr_rates%rowtype,
413 p_rates_new csr_rates%rowtype) is
414 --cursor to fetch the managers of the person
415 cursor csr_mgr_pil_ids(p_group_per_in_ler_id number) is
416 select mgr_per_in_ler_id
417 from ben_cwb_group_hrchy
418 where emp_per_in_ler_id = p_group_per_in_ler_id
419 and lvl_num <> 0
420 order by lvl_num;
421 --
422 cursor csr_xchg_rt(p_pl_id number
423 ,p_oipl_id number
424 ,p_lf_evt_ocrd_dt date
425 ,p_currency varchar2) is
426 select decode(ws_nnmntry_uom, null, xchg_rate, 1) ws_xchg_rt
427 ,decode(elig_sal_nnmntry_uom, null, xchg_rate, 1) elig_sal_xchg_rt
428 ,decode(stat_sal_nnmntry_uom, null, xchg_rate, 1) stat_sal_xchg_rt
429 ,decode(oth_comp_nnmntry_uom, null, xchg_rate, 1) oth_comp_xchg_rt
430 ,decode(tot_comp_nnmntry_uom, null, xchg_rate, 1) tot_comp_xchg_rt
431 ,decode(misc1_nnmntry_uom, null, xchg_rate, 1) misc1_xchg_rt
435 ,uses_bdgt_flag uses_bdgt_flag
432 ,decode(misc2_nnmntry_uom, null, xchg_rate, 1) misc2_xchg_rt
433 ,decode(misc3_nnmntry_uom, null, xchg_rate, 1) misc3_xchg_rt
434 ,decode(rec_nnmntry_uom, null, xchg_rate, 1) rec_xchg_rt
436 from ben_cwb_pl_dsgn pl
437 ,ben_cwb_xchg xchg
438 where pl.pl_id = p_pl_id
439 and pl.oipl_id = p_oipl_id
440 and pl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
441 and xchg.group_pl_id = pl.group_pl_id
442 and xchg.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
446 l_old_xchg_rt csr_xchg_rt%rowtype;
443 and xchg.currency = p_currency;
444 --
445 l_new_xchg_rt csr_xchg_rt%rowtype;
447 --
448 l_elig_count_d number:=0;
449 l_emp_recv_count_d number:=0;
450 l_ws_val_d number;
451 l_elig_sal_val_d number;
452 l_stat_sal_val_d number;
453 l_oth_comp_val_d number;
454 l_tot_comp_val_d number;
455 l_misc1_val_d number;
456 l_misc2_val_d number;
457 l_misc3_val_d number;
458 l_rec_val_d number;
459 l_rec_mn_val_d number;
460 l_rec_mx_val_d number;
461 --
462 l_immd_mgr number;
463 --
464 l_proc varchar2(72) :=g_package||'update_person_rates_summary';
465 begin
466 if g_debug then
467 hr_utility.set_location('Entering:'|| l_proc, 10);
468 end if;
469 -- get the pl_xchg_rate and nnmntry_uom from pl_dsgn
470 open csr_xchg_rt(p_rates_new.pl_id
471 ,p_rates_new.oipl_id
472 ,p_rates_new.lf_evt_ocrd_dt
473 ,p_rates_new.currency);
474 fetch csr_xchg_rt into l_new_xchg_rt;
475 close csr_xchg_rt;
476 --
477 if p_rates_new.currency <> p_rates_old.currency then
478 open csr_xchg_rt(p_rates_new.pl_id
479 ,p_rates_new.oipl_id
480 ,p_rates_new.lf_evt_ocrd_dt
481 ,p_rates_old.currency);
482 fetch csr_xchg_rt into l_old_xchg_rt;
483 close csr_xchg_rt;
484 else
485 l_old_xchg_rt := l_new_xchg_rt;
486 end if;
487 --
488 if g_debug then
489 hr_utility.set_location(l_proc, 30);
490 end if;
491 --
492 -- check if the eligibility is changed
493 if nvl(p_rates_new.elig_flag,'N') <> nvl(p_rates_old.elig_flag,'N') then
494 -- there is a change in eligibility.
495 if g_debug then
496 hr_utility.set_location(l_proc, 40);
497 end if;
498 if nvl(p_rates_new.elig_flag,'N') = 'Y' then
499 -- the person has become eligible
500 l_elig_count_d :=1;
501 if(nvl(p_rates_new.ws_val,0)<>0) then
502 l_emp_recv_count_d :=1;
503 end if;
504 --take the new values as diff values
505 l_ws_val_d := p_rates_new.ws_val / l_new_xchg_rt.ws_xchg_rt;
506 l_elig_sal_val_d := p_rates_new.elig_sal_val /
507 l_new_xchg_rt.elig_sal_xchg_rt;
508 l_stat_sal_val_d := p_rates_new.stat_sal_val /
509 l_new_xchg_rt.stat_sal_xchg_rt;
510 l_oth_comp_val_d := p_rates_new.oth_comp_val /
511 l_new_xchg_rt.oth_comp_xchg_rt;
512 l_tot_comp_val_d := p_rates_new.tot_comp_val /
513 l_new_xchg_rt.tot_comp_xchg_rt;
514 l_misc1_val_d := p_rates_new.misc1_val / l_new_xchg_rt.misc1_xchg_rt;
515 l_misc2_val_d := p_rates_new.misc2_val / l_new_xchg_rt.misc2_xchg_rt;
516 l_misc3_val_d := p_rates_new.misc3_val / l_new_xchg_rt.misc3_xchg_rt;
517 l_rec_val_d := p_rates_new.rec_val / l_new_xchg_rt.rec_xchg_rt;
518 l_rec_mn_val_d := p_rates_new.rec_mn_val / l_new_xchg_rt.rec_xchg_rt;
519 l_rec_mx_val_d := p_rates_new.rec_mx_val / l_new_xchg_rt.rec_xchg_rt;
520 --
521 -- update the bdgt summary,
522 if l_new_xchg_rt.uses_bdgt_flag = 'Y' and l_elig_sal_val_d <> 0 then
523 ben_cwb_summary_pkg.upd_summary_on_elig_sal_change(
524 p_group_per_in_ler_id => p_rates_new.group_per_in_ler_id
525 ,p_elig_sal_change => l_elig_sal_val_d);
526 end if;
527 else
528 if g_debug then
529 hr_utility.set_location(l_proc, 50);
530 end if;
531 -- the person has become in-eligible
532 l_elig_count_d := -1;
533 if(nvl(p_rates_old.ws_val,0)<>0) then
534 l_emp_recv_count_d := -1;
535 end if;
536 -- take the old values as diff values
537 l_ws_val_d := -(p_rates_old.ws_val) / l_old_xchg_rt.ws_xchg_rt;
538 l_elig_sal_val_d := -(p_rates_old.elig_sal_val) /
539 l_old_xchg_rt.elig_sal_xchg_rt;
540 l_stat_sal_val_d := -(p_rates_old.stat_sal_val) /
541 l_old_xchg_rt.stat_sal_xchg_rt;
542 l_oth_comp_val_d := -(p_rates_old.oth_comp_val) /
543 l_old_xchg_rt.oth_comp_xchg_rt;
544 l_tot_comp_val_d := -(p_rates_old.tot_comp_val) /
545 l_old_xchg_rt.tot_comp_xchg_rt;
546 l_misc1_val_d := -(p_rates_old.misc1_val) / l_old_xchg_rt.misc1_xchg_rt;
547 l_misc2_val_d := -(p_rates_old.misc2_val) / l_old_xchg_rt.misc2_xchg_rt;
548 l_misc3_val_d := -(p_rates_old.misc3_val) / l_old_xchg_rt.misc3_xchg_rt;
549 l_rec_val_d := -(p_rates_old.rec_val) /l_old_xchg_rt.rec_xchg_rt;
550 l_rec_mn_val_d := -(p_rates_old.rec_mn_val) / l_old_xchg_rt.rec_xchg_rt;
551 l_rec_mx_val_d := -(p_rates_old.rec_mx_val) / l_old_xchg_rt.rec_xchg_rt;
552 --
553 -- update the bdgt summary
554 if l_new_xchg_rt.uses_bdgt_flag = 'Y' and l_elig_sal_val_d <> 0 then
555 ben_cwb_summary_pkg.upd_summary_on_elig_sal_change(
556 p_group_per_in_ler_id => p_rates_new.group_per_in_ler_id
557 ,p_elig_sal_change => l_elig_sal_val_d);
558 end if;
559 end if; -- of elig_flg = 'Y'
560 else
561 if g_debug then
562 hr_utility.set_location(l_proc, 60);
563 end if;
564 -- there is no change in the eligibiltiy.
565 if nvl(p_rates_new.elig_flag,'N') = 'N' then
566 -- The emp remains in-eligible. So no change in summary
567 return;
571 if (nvl(p_rates_new.ws_val,0) <> 0 and nvl(p_rates_old.ws_val,0)=0) then
568 end if;
569 --
570 l_elig_count_d := 0;
572 -- The employee got WS val. So increment the emp_recv_count
573 l_emp_recv_count_d := 1;
574 end if;
575 --
576 if (nvl(p_rates_new.ws_val,0) = 0 and nvl(p_rates_old.ws_val,0)<>0) then
577 -- The employee WS value is cleared. So reduce the emp_recv_count
578 l_emp_recv_count_d := -1;
579 end if;
580 l_ws_val_d := ben_cwb_utils.add_number_with_null_check
581 (p_rates_new.ws_val/l_new_xchg_rt.ws_xchg_rt,
582 -p_rates_old.ws_val/l_old_xchg_rt.ws_xchg_rt);
583 l_elig_sal_val_d := ben_cwb_utils.add_number_with_null_check
584 (p_rates_new.elig_sal_val/l_new_xchg_rt.elig_sal_xchg_rt,
585 -p_rates_old.elig_sal_val/l_old_xchg_rt.elig_sal_xchg_rt);
586 l_stat_sal_val_d := ben_cwb_utils.add_number_with_null_check
587 (p_rates_new.stat_sal_val/l_new_xchg_rt.stat_sal_xchg_rt,
588 -p_rates_old.stat_sal_val/l_old_xchg_rt.stat_sal_xchg_rt);
589 l_oth_comp_val_d := ben_cwb_utils.add_number_with_null_check
590 (p_rates_new.oth_comp_val/l_new_xchg_rt.oth_comp_xchg_rt,
591 -p_rates_old.oth_comp_val/l_old_xchg_rt.oth_comp_xchg_rt);
592 l_tot_comp_val_d := ben_cwb_utils.add_number_with_null_check
593 (p_rates_new.tot_comp_val/l_new_xchg_rt.tot_comp_xchg_rt,
594 -p_rates_old.tot_comp_val/l_old_xchg_rt.tot_comp_xchg_rt);
595 l_misc1_val_d := ben_cwb_utils.add_number_with_null_check
596 (p_rates_new.misc1_val/l_new_xchg_rt.misc1_xchg_rt,
597 -p_rates_old.misc1_val/l_old_xchg_rt.misc1_xchg_rt);
598 l_misc2_val_d := ben_cwb_utils.add_number_with_null_check
599 (p_rates_new.misc2_val/l_new_xchg_rt.misc2_xchg_rt,
600 -p_rates_old.misc2_val/l_old_xchg_rt.misc2_xchg_rt);
601 l_misc3_val_d := ben_cwb_utils.add_number_with_null_check
602 (p_rates_new.misc3_val/l_new_xchg_rt.misc3_xchg_rt,
603 -p_rates_old.misc3_val/l_old_xchg_rt.misc3_xchg_rt);
604 l_rec_val_d := ben_cwb_utils.add_number_with_null_check
605 (p_rates_new.rec_val/l_new_xchg_rt.rec_xchg_rt,
606 -p_rates_old.rec_val/l_old_xchg_rt.rec_xchg_rt);
607 l_rec_mn_val_d := ben_cwb_utils.add_number_with_null_check
608 (p_rates_new.rec_mn_val/l_new_xchg_rt.rec_xchg_rt,
609 -p_rates_old.rec_mn_val/l_old_xchg_rt.rec_xchg_rt);
610 l_rec_mx_val_d := ben_cwb_utils.add_number_with_null_check
611 (p_rates_new.rec_mx_val/l_new_xchg_rt.rec_xchg_rt,
612 -p_rates_old.rec_mx_val/l_old_xchg_rt.rec_xchg_rt);
613 --
614 -- update the bdgt summary,
618 ,p_elig_sal_change => l_elig_sal_val_d);
615 if l_new_xchg_rt.uses_bdgt_flag = 'Y' and l_elig_sal_val_d <> 0 then
616 ben_cwb_summary_pkg.upd_summary_on_elig_sal_change(
617 p_group_per_in_ler_id => p_rates_new.group_per_in_ler_id
619 end if;
620 end if; -- of check comare elig flags.
621 if g_debug then
622 hr_utility.set_location(l_proc, 70);
623 end if;
624 --
625 -- Check the differences
626 if (l_elig_count_d <> 0 or l_emp_recv_count_d <> 0 or l_ws_val_d <> 0 or
627 l_elig_sal_val_d <> 0 or l_stat_sal_val_d <> 0 or l_oth_comp_val_d <> 0 or
628 l_tot_comp_val_d <> 0 or l_misc1_val_d <> 0 or l_misc2_val_d <> 0 or
629 l_misc3_val_d <> 0 or l_rec_val_d <> 0 or l_rec_mn_val_d <> 0 or
630 l_rec_mx_val_d <> 0 ) then
631 --
632 if g_debug then
633 hr_utility.set_location(l_proc, 80);
634 end if;
635 --There is a change in the record
636 l_immd_mgr := 1;
637 for mgr in csr_mgr_pil_ids(p_rates_new.group_per_in_ler_id)
638 loop
639 ben_cwb_summary_pkg.update_or_insert_pl_sql_tab
640 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
641 ,p_group_pl_id => p_rates_old.group_pl_id
642 ,p_group_oipl_id => p_rates_old.group_oipl_id
643 ,p_elig_count_direct => l_elig_count_d * l_immd_mgr
644 ,p_elig_count_all => l_elig_count_d
645 ,p_emp_recv_count_direct => l_emp_recv_count_d * l_immd_mgr
646 ,p_emp_recv_count_all => l_emp_recv_count_d
647 ,p_elig_sal_val_direct => l_elig_sal_val_d * l_immd_mgr
648 ,p_elig_sal_val_all => l_elig_sal_val_d
649 ,p_ws_val_direct => l_ws_val_d * l_immd_mgr
650 ,p_ws_val_all => l_ws_val_d
651 ,p_stat_sal_val_direct => l_stat_sal_val_d * l_immd_mgr
652 ,p_stat_sal_val_all => l_stat_sal_val_d
653 ,p_oth_comp_val_direct => l_oth_comp_val_d * l_immd_mgr
654 ,p_oth_comp_val_all => l_oth_comp_val_d
655 ,p_tot_comp_val_direct => l_tot_comp_val_d * l_immd_mgr
656 ,p_tot_comp_val_all => l_tot_comp_val_d
657 ,p_rec_val_direct => l_rec_val_d * l_immd_mgr
658 ,p_rec_val_all => l_rec_val_d
659 ,p_rec_mn_val_direct => l_rec_mn_val_d * l_immd_mgr
660 ,p_rec_mn_val_all => l_rec_mn_val_d
661 ,p_rec_mx_val_direct => l_rec_mx_val_d * l_immd_mgr
662 ,p_rec_mx_val_all => l_rec_mx_val_d
663 ,p_misc1_val_direct => l_misc1_val_d * l_immd_mgr
664 ,p_misc1_val_all => l_misc1_val_d
665 ,p_misc2_val_direct => l_misc2_val_d * l_immd_mgr
666 ,p_misc2_val_all => l_misc2_val_d
667 ,p_misc3_val_direct => l_misc3_val_d * l_immd_mgr
668 ,p_misc3_val_all => l_misc3_val_d
669 );
670 l_immd_mgr :=0;
671 end loop; -- of csr_mgr_pil_ids;
672 end if; -- of check differences
673 --
674 -- When eligibility changes, we need to check whether the employee's
675 -- manager's manager is stil a HLM (when made ineligible) or is now
676 -- a HLM (when made eligible).
677 --
678 if l_new_xchg_rt.uses_bdgt_flag = 'Y' and l_elig_count_d <> 0 then
679 ben_cwb_summary_pkg.save_pl_sql_tab;
680 ben_cwb_summary_pkg.clean_budget_data(
681 p_per_in_ler_id => p_rates_new.group_per_in_ler_id
682 ,p_lvl_up => 2);
683 ben_cwb_summary_pkg.save_pl_sql_tab;
684 end if;
685 --
686 if g_debug then
687 hr_utility.set_location(' Leaving:'|| l_proc, 99);
688 end if;
689 --
690 end; -- update_rates_summary
691 --
692 --
693 -- ----------------------------------------------------------------------------
694 -- |-------------------------< create_audit_record >--------------------------|
695 -- ----------------------------------------------------------------------------
696 --
697 -- Description
698 -- This is an internal procedure to write into the BEN_CWB_AUDIT table to
699 -- record particular changes in the values of BEN_CWB_PERSON_RATES.
700 -- Changes evaluated:
701 -- Code Desciption
702 -- CA Update Compensation Amount
703 -- ES Update Eligible Salary
704 -- SS Update Started Salary
705 -- RA Update Recommended Amount
706 -- TC Update Total Compensation Amount
707 -- OC Update Other Compensation Amount
708 -- M1 Update Misc Rate 1
709 -- M2 Update Misc Rate 2
710 -- M3 Update Misc Rate 3
711 -- WN Update Worksheet Min
712 -- WX Update Worksheet Max
713 -- RN Update Worksheet Min
714 -- RX Update Worksheet Max
715 -- EL Update Eligibility Status
716 -- ER Update Eligibility Reason
717 --
718 procedure create_audit_record
719 (p_rates_old csr_rates%rowtype
720 ) is
721
722 l_rates_new csr_rates%rowtype;
723 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
724 l_object_version_number ben_cwb_audit.object_version_number%type;
725 l_cd_meaning_old hr_lookups.meaning%type;
726 l_cd_meaning_new hr_lookups.meaning%type;
727 l_person_id fnd_user.employee_id%type;
728
729 begin
730
731 if g_debug then
735 open csr_rates(p_rates_old.group_per_in_ler_id,
732 hr_utility.set_location('entering:'|| g_package, 203);
733 end if;
734
736 p_rates_old.pl_id,
737 p_rates_old.oipl_id);
738 fetch csr_rates into l_rates_new;
739 close csr_rates;
740
741 select employee_id into l_person_id
742 from fnd_user
743 where user_id = l_rates_new.last_updated_by;
744
745 if g_debug then
746 hr_utility.set_location('person_id:'|| l_person_id, 23);
747 end if;
748 if g_debug then
749 hr_utility.set_location('user_id:'|| l_rates_new.last_updated_by, 24);
750 end if;
751
752
753 if( ((p_rates_old.ws_val is null)
754 and (l_rates_new.ws_val is not null))
755 or ((l_rates_new.ws_val is null)
756 and (p_rates_old.ws_val is not null))
757 or (p_rates_old.ws_val <> l_rates_new.ws_val) ) then
758
759 -- if(nvl(p_rates_old.ws_val,-1)<>nvl(l_rates_new.ws_val,-1)) then
760 if(ben_cwb_audit_api.return_lookup_validity('CA')=true) then
761 ben_cwb_audit_api.create_audit_entry
762 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
763 ,p_group_pl_id => l_rates_new.group_pl_id
764 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
765 ,p_pl_id => l_rates_new.pl_id
766 ,p_group_oipl_id => l_rates_new.group_oipl_id
767 ,p_audit_type_cd => 'CA'
768 ,p_old_val_number => p_rates_old.ws_val
769 ,p_new_val_number => l_rates_new.ws_val
770 ,p_date_stamp => sysdate
771 ,p_change_made_by_person_id => l_person_id
772 ,p_cwb_audit_id => l_cwb_audit_id
773 ,p_object_version_number => l_object_version_number
774 );
775 end if;
776 end if;
777
778 if( ((p_rates_old.elig_sal_val is null)
779 and (l_rates_new.elig_sal_val is not null))
780 or ((l_rates_new.elig_sal_val is null)
781 and (p_rates_old.elig_sal_val is not null))
782 or (p_rates_old.elig_sal_val <> l_rates_new.elig_sal_val) ) then
783 if(ben_cwb_audit_api.return_lookup_validity('ES')=true) then
784 ben_cwb_audit_api.create_audit_entry
785 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
786 ,p_group_pl_id => l_rates_new.group_pl_id
787 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
788 ,p_pl_id => l_rates_new.pl_id
789 ,p_group_oipl_id => l_rates_new.group_oipl_id
790 ,p_audit_type_cd => 'ES'
791 ,p_old_val_number => p_rates_old.elig_sal_val
792 ,p_new_val_number => l_rates_new.elig_sal_val
793 ,p_date_stamp => sysdate
794 ,p_change_made_by_person_id => l_person_id
795 ,p_cwb_audit_id => l_cwb_audit_id
796 ,p_object_version_number => l_object_version_number
797 );
798 end if;
799 end if;
800 if( ((p_rates_old.stat_sal_val is null)
801 and (l_rates_new.stat_sal_val is not null))
802 or ((l_rates_new.stat_sal_val is null)
803 and (p_rates_old.stat_sal_val is not null))
804 or (p_rates_old.stat_sal_val <> l_rates_new.stat_sal_val) ) then
805 if(ben_cwb_audit_api.return_lookup_validity('SS')=true) then
806 ben_cwb_audit_api.create_audit_entry
807 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
808 ,p_group_pl_id => l_rates_new.group_pl_id
809 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
810 ,p_pl_id => l_rates_new.pl_id
811 ,p_group_oipl_id => l_rates_new.group_oipl_id
812 ,p_audit_type_cd => 'SS'
813 ,p_old_val_number => p_rates_old.stat_sal_val
814 ,p_new_val_number => l_rates_new.stat_sal_val
815 ,p_date_stamp => sysdate
816 ,p_change_made_by_person_id => l_person_id
817 ,p_cwb_audit_id => l_cwb_audit_id
818 ,p_object_version_number => l_object_version_number
819 );
820 end if;
821 end if;
822 if( ((p_rates_old.rec_val is null)
823 and (l_rates_new.rec_val is not null))
824 or ((l_rates_new.rec_val is null)
825 and (p_rates_old.rec_val is not null))
826 or (p_rates_old.rec_val <> l_rates_new.rec_val) ) then
827 if(ben_cwb_audit_api.return_lookup_validity('RA')=true) then
828 ben_cwb_audit_api.create_audit_entry
829 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
830 ,p_group_pl_id => l_rates_new.group_pl_id
831 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
832 ,p_pl_id => l_rates_new.pl_id
833 ,p_group_oipl_id => l_rates_new.group_oipl_id
834 ,p_audit_type_cd => 'RA'
835 ,p_old_val_number => p_rates_old.rec_val
836 ,p_new_val_number => l_rates_new.rec_val
837 ,p_date_stamp => sysdate
838 ,p_change_made_by_person_id => l_person_id
839 ,p_cwb_audit_id => l_cwb_audit_id
840 ,p_object_version_number => l_object_version_number
841 );
842 end if;
843 end if;
844 if( ((p_rates_old.tot_comp_val is null)
845 and (l_rates_new.tot_comp_val is not null))
846 or ((l_rates_new.tot_comp_val is null)
850 ben_cwb_audit_api.create_audit_entry
847 and (p_rates_old.tot_comp_val is not null))
848 or (p_rates_old.tot_comp_val <> l_rates_new.tot_comp_val) ) then
849 if(ben_cwb_audit_api.return_lookup_validity('TC')=true) then
851 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
852 ,p_group_pl_id => l_rates_new.group_pl_id
853 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
854 ,p_pl_id => l_rates_new.pl_id
855 ,p_group_oipl_id => l_rates_new.group_oipl_id
856 ,p_audit_type_cd => 'TC'
857 ,p_old_val_number => p_rates_old.tot_comp_val
858 ,p_new_val_number => l_rates_new.tot_comp_val
859 ,p_date_stamp => sysdate
860 ,p_change_made_by_person_id => l_person_id
861 ,p_cwb_audit_id => l_cwb_audit_id
862 ,p_object_version_number => l_object_version_number
863 );
864 end if;
865 end if;
866 if( ((p_rates_old.oth_comp_val is null)
867 and (l_rates_new.oth_comp_val is not null))
868 or ((l_rates_new.oth_comp_val is null)
869 and (p_rates_old.oth_comp_val is not null))
870 or (p_rates_old.oth_comp_val <> l_rates_new.oth_comp_val) ) then
871 if(ben_cwb_audit_api.return_lookup_validity('OC')=true) then
872 ben_cwb_audit_api.create_audit_entry
873 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
874 ,p_group_pl_id => l_rates_new.group_pl_id
875 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
876 ,p_pl_id => l_rates_new.pl_id
877 ,p_group_oipl_id => l_rates_new.group_oipl_id
878 ,p_audit_type_cd => 'OC'
879 ,p_old_val_number => p_rates_old.oth_comp_val
880 ,p_new_val_number => l_rates_new.oth_comp_val
881 ,p_date_stamp => sysdate
882 ,p_change_made_by_person_id => l_person_id
883 ,p_cwb_audit_id => l_cwb_audit_id
884 ,p_object_version_number => l_object_version_number
885 );
886 end if;
887 end if;
888 if( ((p_rates_old.misc1_val is null)
889 and (l_rates_new.misc1_val is not null))
890 or ((l_rates_new.misc1_val is null)
891 and (p_rates_old.misc1_val is not null))
892 or (p_rates_old.misc1_val <> l_rates_new.misc1_val) ) then
893 if(ben_cwb_audit_api.return_lookup_validity('M1')=true) then
894 ben_cwb_audit_api.create_audit_entry
895 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
896 ,p_group_pl_id => l_rates_new.group_pl_id
897 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
898 ,p_pl_id => l_rates_new.pl_id
899 ,p_group_oipl_id => l_rates_new.group_oipl_id
900 ,p_audit_type_cd => 'M1'
901 ,p_old_val_number => p_rates_old.misc1_val
902 ,p_new_val_number => l_rates_new.misc1_val
903 ,p_date_stamp => sysdate
904 ,p_change_made_by_person_id => l_person_id
905 ,p_cwb_audit_id => l_cwb_audit_id
906 ,p_object_version_number => l_object_version_number
907 );
908 end if;
909 end if;
910 if( ((p_rates_old.misc2_val is null)
911 and (l_rates_new.misc2_val is not null))
912 or ((l_rates_new.misc2_val is null)
913 and (p_rates_old.misc2_val is not null))
914 or (p_rates_old.misc2_val <> l_rates_new.misc2_val) ) then
915 if(ben_cwb_audit_api.return_lookup_validity('M2')=true) then
916 ben_cwb_audit_api.create_audit_entry
917 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
918 ,p_group_pl_id => l_rates_new.group_pl_id
919 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
920 ,p_pl_id => l_rates_new.pl_id
921 ,p_group_oipl_id => l_rates_new.group_oipl_id
922 ,p_audit_type_cd => 'M2'
923 ,p_old_val_number => p_rates_old.misc2_val
924 ,p_new_val_number => l_rates_new.misc2_val
925 ,p_date_stamp => sysdate
926 ,p_change_made_by_person_id => l_person_id
927 ,p_cwb_audit_id => l_cwb_audit_id
928 ,p_object_version_number => l_object_version_number
929 );
930 end if;
931 end if;
932 if( ((p_rates_old.misc3_val is null)
933 and (l_rates_new.misc3_val is not null))
934 or ((l_rates_new.misc3_val is null)
935 and (p_rates_old.misc3_val is not null))
936 or (p_rates_old.misc3_val <> l_rates_new.misc3_val) ) then
937 if(ben_cwb_audit_api.return_lookup_validity('M3')=true) then
938 ben_cwb_audit_api.create_audit_entry
939 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
940 ,p_group_pl_id => l_rates_new.group_pl_id
941 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
942 ,p_pl_id => l_rates_new.pl_id
943 ,p_group_oipl_id => l_rates_new.group_oipl_id
944 ,p_audit_type_cd => 'M3'
945 ,p_old_val_number => p_rates_old.misc3_val
946 ,p_new_val_number => l_rates_new.misc3_val
947 ,p_date_stamp => sysdate
948 ,p_change_made_by_person_id => l_person_id
949 ,p_cwb_audit_id => l_cwb_audit_id
950 ,p_object_version_number => l_object_version_number
951 );
952 end if;
953 end if;
957 and (p_rates_old.ws_mn_val is not null))
954 if( ((p_rates_old.ws_mn_val is null)
955 and (l_rates_new.ws_mn_val is not null))
956 or ((l_rates_new.ws_mn_val is null)
958 or (p_rates_old.ws_mn_val <> l_rates_new.ws_mn_val) ) then
959 if(ben_cwb_audit_api.return_lookup_validity('WN')=true) then
960 ben_cwb_audit_api.create_audit_entry
961 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
962 ,p_group_pl_id => l_rates_new.group_pl_id
963 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
964 ,p_pl_id => l_rates_new.pl_id
965 ,p_group_oipl_id => l_rates_new.group_oipl_id
966 ,p_audit_type_cd => 'WN'
967 ,p_old_val_number => p_rates_old.ws_mn_val
968 ,p_new_val_number => l_rates_new.ws_mn_val
969 ,p_date_stamp => sysdate
970 ,p_change_made_by_person_id => l_person_id
971 ,p_cwb_audit_id => l_cwb_audit_id
972 ,p_object_version_number => l_object_version_number
973 );
974 end if;
975 end if;
976 if( ((p_rates_old.ws_mx_val is null)
977 and (l_rates_new.ws_mx_val is not null))
978 or ((l_rates_new.ws_mx_val is null)
979 and (p_rates_old.ws_mx_val is not null))
980 or (p_rates_old.ws_mx_val <> l_rates_new.ws_mx_val) ) then
981 if(ben_cwb_audit_api.return_lookup_validity('WX')=true) then
982 ben_cwb_audit_api.create_audit_entry
983 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
984 ,p_group_pl_id => l_rates_new.group_pl_id
985 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
986 ,p_pl_id => l_rates_new.pl_id
987 ,p_group_oipl_id => l_rates_new.group_oipl_id
988 ,p_audit_type_cd => 'WX'
989 ,p_old_val_number => p_rates_old.ws_mx_val
990 ,p_new_val_number => l_rates_new.ws_mx_val
991 ,p_date_stamp => sysdate
992 ,p_change_made_by_person_id => l_person_id
993 ,p_cwb_audit_id => l_cwb_audit_id
994 ,p_object_version_number => l_object_version_number
995 );
996 end if;
997 end if;
998 if( ((p_rates_old.rec_mn_val is null)
999 and (l_rates_new.rec_mn_val is not null))
1000 or ((l_rates_new.rec_mn_val is null)
1001 and (p_rates_old.rec_mn_val is not null))
1002 or (p_rates_old.rec_mn_val <> l_rates_new.rec_mn_val) ) then
1003 if(ben_cwb_audit_api.return_lookup_validity('RN')=true) then
1004 ben_cwb_audit_api.create_audit_entry
1005 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
1006 ,p_group_pl_id => l_rates_new.group_pl_id
1007 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
1008 ,p_pl_id => l_rates_new.pl_id
1009 ,p_group_oipl_id => l_rates_new.group_oipl_id
1010 ,p_audit_type_cd => 'RN'
1011 ,p_old_val_number => p_rates_old.rec_mn_val
1012 ,p_new_val_number => l_rates_new.rec_mn_val
1013 ,p_date_stamp => sysdate
1014 ,p_change_made_by_person_id => l_person_id
1015 ,p_cwb_audit_id => l_cwb_audit_id
1016 ,p_object_version_number => l_object_version_number
1017 );
1018 end if;
1019 end if;
1020 if( ((p_rates_old.rec_mx_val is null)
1021 and (l_rates_new.rec_mx_val is not null))
1022 or ((l_rates_new.rec_mx_val is null)
1023 and (p_rates_old.rec_mx_val is not null))
1024 or (p_rates_old.rec_mx_val <> l_rates_new.rec_mx_val) ) then
1025 if(ben_cwb_audit_api.return_lookup_validity('RX')=true) then
1026 ben_cwb_audit_api.create_audit_entry
1027 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
1028 ,p_group_pl_id => l_rates_new.group_pl_id
1029 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
1030 ,p_pl_id => l_rates_new.pl_id
1031 ,p_group_oipl_id => l_rates_new.group_oipl_id
1032 ,p_audit_type_cd => 'RX'
1033 ,p_old_val_number => p_rates_old.rec_mx_val
1034 ,p_new_val_number => l_rates_new.rec_mx_val
1035 ,p_date_stamp => sysdate
1036 ,p_change_made_by_person_id => l_person_id
1037 ,p_cwb_audit_id => l_cwb_audit_id
1038 ,p_object_version_number => l_object_version_number
1039 );
1040 end if;
1041 end if;
1042 if( ((p_rates_old.elig_flag is null)
1043 and (l_rates_new.elig_flag is not null))
1044 or ((l_rates_new.elig_flag is null)
1045 and (p_rates_old.elig_flag is not null))
1046 or (p_rates_old.elig_flag <> l_rates_new.elig_flag) ) then
1047 if(ben_cwb_audit_api.return_lookup_validity('EL')=true) then
1048
1049 begin
1050 select meaning into l_cd_meaning_old
1051 from hr_lookups
1052 where lookup_type='BEN_CWB_ELIG_CRITERIA'
1053 and lookup_code = p_rates_old.elig_flag;
1054 exception
1055 when no_data_found then
1056 -- record does not exist. return null
1057 l_cd_meaning_old:= p_rates_old.elig_flag;
1058 end;
1059
1060 begin
1061 select meaning into l_cd_meaning_new
1062 from hr_lookups
1063 where lookup_type='BEN_CWB_ELIG_CRITERIA'
1064 and lookup_code = l_rates_new.elig_flag;
1065 exception
1066 when no_data_found then
1067 -- record does not exist. return null
1068 l_cd_meaning_new:= l_rates_new.elig_flag;
1069 end;
1070
1074 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
1071 ben_cwb_audit_api.create_audit_entry
1072 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
1073 ,p_group_pl_id => l_rates_new.group_pl_id
1075 ,p_pl_id => l_rates_new.pl_id
1076 ,p_group_oipl_id => l_rates_new.group_oipl_id
1077 ,p_audit_type_cd => 'EL'
1078 ,p_old_val_varchar => l_cd_meaning_old
1079 ,p_new_val_varchar => l_cd_meaning_new
1080 ,p_date_stamp => sysdate
1081 ,p_change_made_by_person_id => l_person_id
1082 ,p_cwb_audit_id => l_cwb_audit_id
1083 ,p_object_version_number => l_object_version_number
1084 );
1085 end if;
1086 end if;
1087 if( ((p_rates_old.inelig_rsn_cd is null)
1088 and (l_rates_new.inelig_rsn_cd is not null))
1089 or ((l_rates_new.inelig_rsn_cd is null)
1090 and (p_rates_old.inelig_rsn_cd is not null))
1091 or (p_rates_old.inelig_rsn_cd <> l_rates_new.inelig_rsn_cd) ) then
1092 if(ben_cwb_audit_api.return_lookup_validity('ER')=true) then
1093
1094 begin
1095 select meaning into l_cd_meaning_old
1096 from hr_lookups
1097 where lookup_type='BEN_INELG_RSN'
1098 and lookup_code = p_rates_old.inelig_rsn_cd;
1099 exception
1100 when no_data_found then
1101 -- record does not exist. return null
1102 l_cd_meaning_old:= null;
1103 end;
1104
1105 begin
1106 select meaning into l_cd_meaning_new
1107 from hr_lookups
1108 where lookup_type='BEN_INELG_RSN'
1109 and lookup_code = l_rates_new.inelig_rsn_cd;
1110 exception
1111 when no_data_found then
1112 -- record does not exist. return null
1113 l_cd_meaning_new:= null;
1114 end;
1115
1116 ben_cwb_audit_api.create_audit_entry
1117 (p_group_per_in_ler_id => l_rates_new.group_per_in_ler_id
1118 ,p_group_pl_id => l_rates_new.group_pl_id
1119 ,p_lf_evt_ocrd_dt => l_rates_new.lf_evt_ocrd_dt
1120 ,p_pl_id => l_rates_new.pl_id
1121 ,p_group_oipl_id => l_rates_new.group_oipl_id
1122 ,p_audit_type_cd => 'ER'
1123 ,p_old_val_varchar => l_cd_meaning_old
1124 ,p_new_val_varchar => l_cd_meaning_new
1125 ,p_date_stamp => sysdate
1126 ,p_change_made_by_person_id => l_person_id
1127 ,p_cwb_audit_id => l_cwb_audit_id
1128 ,p_object_version_number => l_object_version_number
1129 );
1130 end if;
1131 end if;
1132 end create_audit_record;
1133
1134 --
1135 -- ----------------------------------------------------------------------------
1136 -- |--------------------------< update_person_rate >--------------------------|
1137 -- ----------------------------------------------------------------------------
1138 --
1139 procedure update_person_rate
1140 (p_validate in boolean default false
1141 ,p_group_per_in_ler_id in number
1142 ,p_pl_id in number
1143 ,p_oipl_id in number
1144 ,p_group_pl_id in number default hr_api.g_number
1145 ,p_group_oipl_id in number default hr_api.g_number
1146 ,p_lf_evt_ocrd_dt in date default hr_api.g_date
1147 ,p_person_id in number default hr_api.g_number
1148 ,p_assignment_id in number default hr_api.g_number
1149 ,p_elig_flag in varchar2 default hr_api.g_varchar2
1150 ,p_ws_val in number default hr_api.g_number
1151 ,p_ws_mn_val in number default hr_api.g_number
1152 ,p_ws_mx_val in number default hr_api.g_number
1153 ,p_ws_incr_val in number default hr_api.g_number
1154 ,p_elig_sal_val in number default hr_api.g_number
1155 ,p_stat_sal_val in number default hr_api.g_number
1156 ,p_oth_comp_val in number default hr_api.g_number
1157 ,p_tot_comp_val in number default hr_api.g_number
1158 ,p_misc1_val in number default hr_api.g_number
1159 ,p_misc2_val in number default hr_api.g_number
1160 ,p_misc3_val in number default hr_api.g_number
1161 ,p_rec_val in number default hr_api.g_number
1162 ,p_rec_mn_val in number default hr_api.g_number
1163 ,p_rec_mx_val in number default hr_api.g_number
1164 ,p_rec_incr_val in number default hr_api.g_number
1165 ,p_ws_val_last_upd_date in date default hr_api.g_date
1166 ,p_ws_val_last_upd_by in number default hr_api.g_number
1167 ,p_pay_proposal_id in number default hr_api.g_number
1168 ,p_element_entry_value_id in number default hr_api.g_number
1169 ,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
1170 ,p_elig_ovrid_dt in date default hr_api.g_date
1171 ,p_elig_ovrid_person_id in number default hr_api.g_number
1172 ,p_copy_dist_bdgt_val in number default hr_api.g_number
1173 ,p_copy_ws_bdgt_val in number default hr_api.g_number
1174 ,p_copy_rsrv_val in number default hr_api.g_number
1175 ,p_copy_dist_bdgt_mn_val in number default hr_api.g_number
1179 ,p_copy_ws_bdgt_mx_val in number default hr_api.g_number
1176 ,p_copy_dist_bdgt_mx_val in number default hr_api.g_number
1177 ,p_copy_dist_bdgt_incr_val in number default hr_api.g_number
1178 ,p_copy_ws_bdgt_mn_val in number default hr_api.g_number
1180 ,p_copy_ws_bdgt_incr_val in number default hr_api.g_number
1181 ,p_copy_rsrv_mn_val in number default hr_api.g_number
1182 ,p_copy_rsrv_mx_val in number default hr_api.g_number
1183 ,p_copy_rsrv_incr_val in number default hr_api.g_number
1184 ,p_copy_dist_bdgt_iss_val in number default hr_api.g_number
1185 ,p_copy_ws_bdgt_iss_val in number default hr_api.g_number
1186 ,p_copy_dist_bdgt_iss_date in date default hr_api.g_date
1187 ,p_copy_ws_bdgt_iss_date in date default hr_api.g_date
1188 ,p_comp_posting_date in date default hr_api.g_date
1189 ,p_ws_rt_start_date in date default hr_api.g_date
1190 ,p_currency in varchar2 default hr_api.g_varchar2
1191 ,p_perf_min_max_edit in varchar2 default 'Y'
1192 ,p_object_version_number in out nocopy number
1193 ) is
1194 --
1195 -- Declare cursors and local variables
1196 --
1197 l_object_version_number number;
1198 l_rates_old csr_rates%rowtype;
1199 l_rates_new csr_rates%rowtype;
1200 --
1201 cursor csr_mn_mx_vals is
1202 select ws_mn_val
1203 ,ws_mx_val
1204 ,ws_incr_val
1205 ,rec_mn_val
1206 ,rec_mx_val
1207 ,rec_incr_val
1208 from ben_cwb_person_rates rts
1209 where group_per_in_ler_id = p_group_per_in_ler_id
1210 and pl_id = p_pl_id
1211 and oipl_id = p_oipl_id;
1212 --
1213 l_mn_mx_vals csr_mn_mx_vals%rowtype;
1214 --
1215 l_proc varchar2(72) := g_package||'update_person_rate';
1216 begin
1217 if g_debug then
1218 hr_utility.set_location('Entering:'|| l_proc, 10);
1219 end if;
1220 --
1221 -- Issue a savepoint
1222 --
1223 savepoint update_person_rate;
1224 --
1225 -- select the existing values from table.
1226 open csr_rates(p_group_per_in_ler_id,p_pl_id,p_oipl_id);
1227 fetch csr_rates into l_rates_old;
1228 close csr_rates;
1229 --
1230 -- Remember IN OUT parameter IN values
1231 --
1232 l_object_version_number := p_object_version_number;
1233 --
1234 -- Call Before Process User Hook
1235 --
1236 begin
1237 ben_cwb_person_rates_bk2.update_person_rate_b
1238 (p_group_per_in_ler_id => p_group_per_in_ler_id
1239 ,p_pl_id => p_pl_id
1240 ,p_oipl_id => p_oipl_id
1241 ,p_group_pl_id => p_group_pl_id
1242 ,p_group_oipl_id => p_group_oipl_id
1243 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1244 ,p_person_id => p_person_id
1245 ,p_assignment_id => p_assignment_id
1246 ,p_elig_flag => p_elig_flag
1247 ,p_ws_val => p_ws_val
1248 ,p_ws_mn_val => p_ws_mn_val
1249 ,p_ws_mx_val => p_ws_mx_val
1250 ,p_ws_incr_val => p_ws_incr_val
1251 ,p_elig_sal_val => p_elig_sal_val
1252 ,p_stat_sal_val => p_stat_sal_val
1253 ,p_oth_comp_val => p_oth_comp_val
1254 ,p_tot_comp_val => p_tot_comp_val
1255 ,p_misc1_val => p_misc1_val
1256 ,p_misc2_val => p_misc2_val
1257 ,p_misc3_val => p_misc3_val
1258 ,p_rec_val => p_rec_val
1259 ,p_rec_mn_val => p_rec_mn_val
1260 ,p_rec_mx_val => p_rec_mx_val
1261 ,p_rec_incr_val => p_rec_incr_val
1262 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
1263 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
1264 ,p_pay_proposal_id => p_pay_proposal_id
1265 ,p_element_entry_value_id => p_element_entry_value_id
1266 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1267 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1268 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1269 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
1270 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
1271 ,p_copy_rsrv_val => p_copy_rsrv_val
1272 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
1273 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
1274 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
1275 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
1276 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
1277 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
1278 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
1279 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
1280 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
1281 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
1282 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
1283 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
1284 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
1285 ,p_comp_posting_date => p_comp_posting_date
1286 ,p_ws_rt_start_date => p_ws_rt_start_date
1287 ,p_currency => p_currency
1288 ,p_object_version_number => l_object_version_number
1289 );
1290 exception
1291 when hr_api.cannot_find_prog_unit then
1292 hr_api.cannot_find_prog_unit_error
1293 (p_module_name => 'UPDATE_PERSON_RATE'
1297 --
1294 ,p_hook_type => 'BP'
1295 );
1296 end;
1298 -- Validation in addition to Row Handlers
1299 --
1300 -- Min Max Edits
1301 --
1302 if p_perf_min_max_edit = 'Y' then
1303 --
1304 open csr_mn_mx_vals;
1305 fetch csr_mn_mx_vals into l_mn_mx_vals;
1306 close csr_mn_mx_vals;
1307 --
1308 if (p_ws_mx_val is null) then
1309 l_mn_mx_vals.ws_mx_val := null;
1310 end if;
1311 if (p_ws_mx_val <> hr_api.g_number) then
1312 l_mn_mx_vals.ws_mx_val := p_ws_mx_val;
1313 end if;
1314 --
1315 if (p_ws_mn_val is null) then
1316 l_mn_mx_vals.ws_mn_val := null;
1317 end if;
1318 if (p_ws_mn_val <> hr_api.g_number) then
1319 l_mn_mx_vals.ws_mn_val := p_ws_mn_val;
1320 end if;
1321 --
1322 if (p_ws_incr_val is null) then
1323 l_mn_mx_vals.ws_incr_val := null;
1324 end if;
1325 if (p_ws_incr_val <> hr_api.g_number) then
1326 l_mn_mx_vals.ws_incr_val := p_ws_incr_val;
1327 end if;
1328 --
1329 --
1330 if g_debug then
1331 hr_utility.set_location(l_proc, 30);
1332 end if;
1333 --
1334 if (p_rec_mx_val is null) then
1335 l_mn_mx_vals.rec_mx_val := null;
1336 end if;
1337 if (p_rec_mx_val <> hr_api.g_number) then
1338 l_mn_mx_vals.rec_mx_val := p_rec_mx_val;
1339 end if;
1340 --
1341 if (p_rec_mn_val is null) then
1342 l_mn_mx_vals.rec_mn_val := null;
1343 end if;
1344 if (p_rec_mn_val <> hr_api.g_number) then
1345 l_mn_mx_vals.rec_mn_val := p_rec_mn_val;
1346 end if;
1347 --
1348 if (p_rec_incr_val is null) then
1349 l_mn_mx_vals.rec_incr_val := null;
1350 end if;
1351 if (p_rec_incr_val <> hr_api.g_number) then
1352 l_mn_mx_vals.rec_incr_val := p_rec_incr_val;
1353 end if;
1354 --
1355 if g_debug then
1356 hr_utility.set_location(l_proc, 40);
1357 end if;
1358 --
1359 -- Check Min, Max and Inc for Ws Val
1360 --
1361 check_min_max(p_val => p_ws_val
1362 ,p_min_val => l_mn_mx_vals.ws_mn_val
1363 ,p_max_val => l_mn_mx_vals.ws_mx_val
1364 ,p_incr_val => l_mn_mx_vals.ws_incr_val
1365 ,p_group_per_in_ler_id => p_group_per_in_ler_id);
1366 --
1367 if g_debug then
1368 hr_utility.set_location(l_proc, 50);
1369 end if;
1370 --
1371 --
1372 -- Check Min, Max and Inc for Rec Val
1373 --
1374 check_min_max(p_val => p_rec_val
1375 ,p_min_val => l_mn_mx_vals.rec_mn_val
1376 ,p_max_val => l_mn_mx_vals.rec_mx_val
1377 ,p_incr_val => l_mn_mx_vals.rec_incr_val
1378 ,p_group_per_in_ler_id => p_group_per_in_ler_id);
1379 --
1380 if g_debug then
1381 hr_utility.set_location(l_proc, 60);
1382 end if;
1383 --
1384 end if; -- of p_perf_min_max_edit
1385 --
1386 -- Process Logic
1387 --
1388 ben_rts_upd.upd
1389 (p_group_per_in_ler_id => p_group_per_in_ler_id
1390 ,p_pl_id => p_pl_id
1391 ,p_oipl_id => p_oipl_id
1392 ,p_group_pl_id => p_group_pl_id
1393 ,p_group_oipl_id => p_group_oipl_id
1394 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1395 ,p_person_id => p_person_id
1396 ,p_assignment_id => p_assignment_id
1397 ,p_elig_flag => p_elig_flag
1398 ,p_ws_val => p_ws_val
1399 ,p_ws_mn_val => p_ws_mn_val
1400 ,p_ws_mx_val => p_ws_mx_val
1401 ,p_ws_incr_val => p_ws_incr_val
1402 ,p_elig_sal_val => p_elig_sal_val
1403 ,p_stat_sal_val => p_stat_sal_val
1404 ,p_oth_comp_val => p_oth_comp_val
1405 ,p_tot_comp_val => p_tot_comp_val
1406 ,p_misc1_val => p_misc1_val
1407 ,p_misc2_val => p_misc2_val
1408 ,p_misc3_val => p_misc3_val
1409 ,p_rec_val => p_rec_val
1410 ,p_rec_mn_val => p_rec_mn_val
1411 ,p_rec_mx_val => p_rec_mx_val
1412 ,p_rec_incr_val => p_rec_incr_val
1413 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
1414 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
1415 ,p_pay_proposal_id => p_pay_proposal_id
1416 ,p_element_entry_value_id => p_element_entry_value_id
1417 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1418 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1419 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1420 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
1421 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
1422 ,p_copy_rsrv_val => p_copy_rsrv_val
1423 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
1424 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
1425 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
1426 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
1427 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
1428 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
1429 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
1430 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
1431 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
1432 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
1436 ,p_comp_posting_date => p_comp_posting_date
1433 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
1434 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
1435 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
1437 ,p_ws_rt_start_date => p_ws_rt_start_date
1438 ,p_currency => p_currency
1439 ,p_object_version_number => l_object_version_number
1440 );
1441 --
1442 -- Call After Process User Hook
1443 --
1444 begin
1445 ben_cwb_person_rates_bk2.update_person_rate_a
1446 (p_group_per_in_ler_id => p_group_per_in_ler_id
1447 ,p_pl_id => p_pl_id
1448 ,p_oipl_id => p_oipl_id
1449 ,p_group_pl_id => p_group_pl_id
1450 ,p_group_oipl_id => p_group_oipl_id
1451 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1452 ,p_person_id => p_person_id
1453 ,p_assignment_id => p_assignment_id
1454 ,p_elig_flag => p_elig_flag
1455 ,p_ws_val => p_ws_val
1456 ,p_ws_mn_val => p_ws_mn_val
1457 ,p_ws_mx_val => p_ws_mx_val
1458 ,p_ws_incr_val => p_ws_incr_val
1459 ,p_elig_sal_val => p_elig_sal_val
1460 ,p_stat_sal_val => p_stat_sal_val
1461 ,p_oth_comp_val => p_oth_comp_val
1462 ,p_tot_comp_val => p_tot_comp_val
1463 ,p_misc1_val => p_misc1_val
1464 ,p_misc2_val => p_misc2_val
1465 ,p_misc3_val => p_misc3_val
1466 ,p_rec_val => p_rec_val
1467 ,p_rec_mn_val => p_rec_mn_val
1468 ,p_rec_mx_val => p_rec_mx_val
1469 ,p_rec_incr_val => p_rec_incr_val
1470 ,p_ws_val_last_upd_date => p_ws_val_last_upd_date
1471 ,p_ws_val_last_upd_by => p_ws_val_last_upd_by
1472 ,p_pay_proposal_id => p_pay_proposal_id
1473 ,p_element_entry_value_id => p_element_entry_value_id
1474 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1475 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1476 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1477 ,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
1478 ,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
1479 ,p_copy_rsrv_val => p_copy_rsrv_val
1480 ,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
1481 ,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
1482 ,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
1483 ,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
1484 ,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
1485 ,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
1486 ,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
1487 ,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
1488 ,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
1489 ,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
1490 ,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
1491 ,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
1492 ,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
1493 ,p_comp_posting_date => p_comp_posting_date
1494 ,p_ws_rt_start_date => p_ws_rt_start_date
1495 ,p_currency => p_currency
1496 ,p_object_version_number => l_object_version_number
1497 );
1498 exception
1499 when hr_api.cannot_find_prog_unit then
1500 hr_api.cannot_find_prog_unit_error
1501 (p_module_name => 'UPDATE_PERSON_RATE'
1502 ,p_hook_type => 'AP'
1503 );
1504 end;
1505 --
1506 -- When in validation only mode raise the Validate_Enabled exception
1507 --
1508 if p_validate then
1509 raise hr_api.validate_enabled;
1510 end if;
1511 --
1512 -- Update is successful. So call the update_rates_summary to process
1513 -- the summary information
1514 open csr_rates(p_group_per_in_ler_id,p_pl_id,p_oipl_id);
1515 fetch csr_rates into l_rates_new;
1516 close csr_rates;
1517
1518 update_rates_summary(l_rates_old,l_rates_new);
1519
1520 -- Now call to record changes in audit history table
1521 create_audit_record(l_rates_old);
1522
1523 -- Set all IN OUT and OUT parameters with out values
1524 --
1525 p_object_version_number := l_object_version_number;
1526 --
1527 if g_debug then
1528 hr_utility.set_location(' Leaving:'||l_proc, 70);
1529 end if;
1530 exception
1531 when hr_api.validate_enabled then
1532 --
1533 -- As the Validate_Enabled exception has been raised
1534 -- we must rollback to the savepoint
1535 --
1536 rollback to update_person_rate;
1537 --
1538 if g_debug then
1539 hr_utility.set_location(' Leaving:'||l_proc, 80);
1540 end if;
1541 when others then
1542 --
1543 -- A validation or unexpected error has occured
1544 --
1545 rollback to update_person_rate;
1546 --
1547 if g_debug then
1548 hr_utility.set_location(' Leaving:'||l_proc, 90);
1549 end if;
1550 raise;
1551 end update_person_rate;
1552 --
1553 -- ----------------------------------------------------------------------------
1557 (p_validate in boolean default false
1554 -- |--------------------------< delete_person_rate >--------------------------|
1555 -- ----------------------------------------------------------------------------
1556 procedure delete_person_rate
1558 ,p_group_per_in_ler_id in number
1559 ,p_pl_id in number
1560 ,p_oipl_id in number
1561 ,p_object_version_number in number
1562 ,p_update_summary in boolean default false
1563 ) is
1564 --
1565 -- Declare cursors and local variables
1566 --
1567 l_proc varchar2(72) := g_package||'delete_person_rate';
1568 l_rates_old csr_rates%rowtype;
1569 l_rates_new csr_rates%rowtype;
1570 --
1571 begin
1572 if g_debug then
1573 hr_utility.set_location('Entering:'|| l_proc, 10);
1574 end if;
1575 --
1576 -- Issue a savepoint
1577 --
1578 savepoint delete_person_rate;
1579 --
1580 open csr_rates(p_group_per_in_ler_id,p_pl_id,p_oipl_id);
1581 fetch csr_rates into l_rates_old;
1582 close csr_rates;
1583 --
1584 -- Remember IN OUT parameter IN values
1585 --
1586 --
1587 -- Call Before Process User Hook
1588 --
1589 begin
1590 ben_cwb_person_rates_bk3.delete_person_rate_b
1591 (p_group_per_in_ler_id => p_group_per_in_ler_id
1592 ,p_pl_id => p_pl_id
1593 ,p_oipl_id => p_oipl_id
1594 ,p_object_version_number => p_object_version_number
1595 );
1596 exception
1597 when hr_api.cannot_find_prog_unit then
1598 hr_api.cannot_find_prog_unit_error
1599 (p_module_name => 'DELETE_PERSON_RATE'
1600 ,p_hook_type => 'BP'
1601 );
1602 end;
1603 --
1604 -- Validation in addition to Row Handlers
1605 --
1606
1607 --
1608 -- Process Logic
1609 --
1610 ben_rts_del.del
1611 (p_group_per_in_ler_id => p_group_per_in_ler_id
1612 ,p_pl_id => p_pl_id
1613 ,p_oipl_id => p_oipl_id
1614 ,p_object_version_number => p_object_version_number
1615 );
1616 --
1617 -- Call After Process User Hook
1618 --
1619 begin
1620 ben_cwb_person_rates_bk3.delete_person_rate_a
1621 (p_group_per_in_ler_id => p_group_per_in_ler_id
1622 ,p_pl_id => p_pl_id
1623 ,p_oipl_id => p_oipl_id
1624 ,p_object_version_number => p_object_version_number
1625 );
1626 exception
1627 when hr_api.cannot_find_prog_unit then
1628 hr_api.cannot_find_prog_unit_error
1629 (p_module_name => 'DELETE_PERSON_RATE'
1630 ,p_hook_type => 'AP'
1631 );
1632 end;
1633 --
1634 -- When in validation only mode raise the Validate_Enabled exception
1635 --
1636 if p_validate then
1637 raise hr_api.validate_enabled;
1638 end if;
1639 --
1640 -- Delete is successful. So call the update_rates_summary to process
1641 -- the summary information
1642 --
1643 if p_update_summary then
1644 l_rates_new := l_rates_old;
1645 l_rates_new.elig_flag := 'N'; --Making a rec inelig is like deleting.
1646 update_rates_summary(l_rates_old,l_rates_new);
1647 end if;
1648 --
1649 if g_debug then
1650 hr_utility.set_location(' Leaving:'||l_proc, 70);
1651 end if;
1652 exception
1653 when hr_api.validate_enabled then
1654 --
1655 -- As the Validate_Enabled exception has been raised
1656 -- we must rollback to the savepoint
1657 --
1658 rollback to delete_person_rate;
1659 --
1660 if g_debug then
1661 hr_utility.set_location(' Leaving:'||l_proc, 80);
1662 end if;
1663 when others then
1664 --
1665 -- A validation or unexpected error has occured
1666 --
1667 rollback to delete_person_rate;
1668 --
1669 -- Reset IN OUT parameters and set all
1670 -- OUT parameters, including warnings, to null
1671 --
1672 if g_debug then
1673 hr_utility.set_location(' Leaving:'||l_proc, 90);
1674 end if;
1675 raise;
1676 end delete_person_rate;
1677 --
1678 --
1679 end ben_cwb_person_rates_api;