[Home] [Help]
PACKAGE BODY: APPS.BEN_ACTY_VRBL_RATE_API
Source
1 Package Body ben_acty_vrbl_rate_api as
2 /* $Header: beavrapi.pkb 115.8 2003/06/02 21:14:14 ikasire ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_acty_vrbl_rate_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_acty_vrbl_rate >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_acty_vrbl_rate
13 (p_validate in boolean default false
14 ,p_acty_vrbl_rt_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_acty_base_rt_id in number default null
18 ,p_vrbl_rt_prfl_id in number default null
19 ,p_ordr_num in number default null
20 ,p_business_group_id in number default null
21 ,p_avr_attribute_category in varchar2 default null
22 ,p_avr_attribute1 in varchar2 default null
23 ,p_avr_attribute2 in varchar2 default null
24 ,p_avr_attribute3 in varchar2 default null
25 ,p_avr_attribute4 in varchar2 default null
26 ,p_avr_attribute5 in varchar2 default null
27 ,p_avr_attribute6 in varchar2 default null
28 ,p_avr_attribute7 in varchar2 default null
29 ,p_avr_attribute8 in varchar2 default null
30 ,p_avr_attribute9 in varchar2 default null
31 ,p_avr_attribute10 in varchar2 default null
32 ,p_avr_attribute11 in varchar2 default null
33 ,p_avr_attribute12 in varchar2 default null
34 ,p_avr_attribute13 in varchar2 default null
35 ,p_avr_attribute14 in varchar2 default null
36 ,p_avr_attribute15 in varchar2 default null
37 ,p_avr_attribute16 in varchar2 default null
38 ,p_avr_attribute17 in varchar2 default null
39 ,p_avr_attribute18 in varchar2 default null
40 ,p_avr_attribute19 in varchar2 default null
41 ,p_avr_attribute20 in varchar2 default null
42 ,p_avr_attribute21 in varchar2 default null
43 ,p_avr_attribute22 in varchar2 default null
44 ,p_avr_attribute23 in varchar2 default null
45 ,p_avr_attribute24 in varchar2 default null
46 ,p_avr_attribute25 in varchar2 default null
47 ,p_avr_attribute26 in varchar2 default null
48 ,p_avr_attribute27 in varchar2 default null
49 ,p_avr_attribute28 in varchar2 default null
50 ,p_avr_attribute29 in varchar2 default null
51 ,p_avr_attribute30 in varchar2 default null
52 ,p_object_version_number out nocopy number
53 ,p_effective_date in date
54 ) is
55 --
56 -- Declare cursors and local variables
57 --
58 cursor c1 is select pgm_id,pl_id,ptip_id,plip_id,oipl_id,opt_id
59 from ben_acty_base_rt_f
60 where acty_base_rt_id = p_acty_base_rt_id;
61 l_c1 c1%ROWTYPE;
62 --
63 -- Start Option Level Rates
64 --
65 cursor c_oipl(p_opt_id number) is
66 select distinct oipl_id
67 from ben_oipl_f oipl
68 where oipl.opt_id = p_opt_id ;
69 --
70 -- End Option Level Rates
71 --
72 l_acty_vrbl_rt_id ben_acty_vrbl_rt_f.acty_vrbl_rt_id%TYPE;
73 l_effective_start_date ben_acty_vrbl_rt_f.effective_start_date%TYPE;
74 l_effective_end_date ben_acty_vrbl_rt_f.effective_end_date%TYPE;
75 l_proc varchar2(72) := g_package||'create_acty_vrbl_rate';
76 l_object_version_number ben_acty_vrbl_rt_f.object_version_number%TYPE;
77 --
78 begin
79 --
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81 --
82 -- Issue a savepoint if operating in validation only mode
83 --
84 savepoint create_acty_vrbl_rate;
85 --
86 hr_utility.set_location(l_proc, 20);
87 --
88 -- Process Logic
89 --
90 begin
91 --
92 -- Start of API User Hook for the before hook of create_acty_vrbl_rate
93 --
94 ben_acty_vrbl_rate_bk1.create_acty_vrbl_rate_b
95 (
96 p_acty_base_rt_id => p_acty_base_rt_id
97 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
98 ,p_ordr_num => p_ordr_num
99 ,p_business_group_id => p_business_group_id
100 ,p_avr_attribute_category => p_avr_attribute_category
101 ,p_avr_attribute1 => p_avr_attribute1
102 ,p_avr_attribute2 => p_avr_attribute2
103 ,p_avr_attribute3 => p_avr_attribute3
104 ,p_avr_attribute4 => p_avr_attribute4
105 ,p_avr_attribute5 => p_avr_attribute5
106 ,p_avr_attribute6 => p_avr_attribute6
107 ,p_avr_attribute7 => p_avr_attribute7
108 ,p_avr_attribute8 => p_avr_attribute8
109 ,p_avr_attribute9 => p_avr_attribute9
110 ,p_avr_attribute10 => p_avr_attribute10
111 ,p_avr_attribute11 => p_avr_attribute11
112 ,p_avr_attribute12 => p_avr_attribute12
113 ,p_avr_attribute13 => p_avr_attribute13
114 ,p_avr_attribute14 => p_avr_attribute14
115 ,p_avr_attribute15 => p_avr_attribute15
116 ,p_avr_attribute16 => p_avr_attribute16
117 ,p_avr_attribute17 => p_avr_attribute17
118 ,p_avr_attribute18 => p_avr_attribute18
119 ,p_avr_attribute19 => p_avr_attribute19
120 ,p_avr_attribute20 => p_avr_attribute20
121 ,p_avr_attribute21 => p_avr_attribute21
122 ,p_avr_attribute22 => p_avr_attribute22
123 ,p_avr_attribute23 => p_avr_attribute23
124 ,p_avr_attribute24 => p_avr_attribute24
125 ,p_avr_attribute25 => p_avr_attribute25
126 ,p_avr_attribute26 => p_avr_attribute26
127 ,p_avr_attribute27 => p_avr_attribute27
128 ,p_avr_attribute28 => p_avr_attribute28
129 ,p_avr_attribute29 => p_avr_attribute29
130 ,p_avr_attribute30 => p_avr_attribute30
131 ,p_effective_date => trunc(p_effective_date)
132 );
133 exception
134 when hr_api.cannot_find_prog_unit then
135 hr_api.cannot_find_prog_unit_error
136 (
137 p_module_name => 'CREATE_acty_vrbl_rate'
138 ,p_hook_type => 'BP'
139 );
140 --
141 -- End of API User Hook for the before hook of create_acty_vrbl_rate
142 --
143 end;
144 --
145 ben_avr_ins.ins
146 (
147 p_acty_vrbl_rt_id => l_acty_vrbl_rt_id
148 ,p_effective_start_date => l_effective_start_date
149 ,p_effective_end_date => l_effective_end_date
150 ,p_acty_base_rt_id => p_acty_base_rt_id
151 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
152 ,p_ordr_num => p_ordr_num
153 ,p_business_group_id => p_business_group_id
154 ,p_avr_attribute_category => p_avr_attribute_category
155 ,p_avr_attribute1 => p_avr_attribute1
156 ,p_avr_attribute2 => p_avr_attribute2
157 ,p_avr_attribute3 => p_avr_attribute3
158 ,p_avr_attribute4 => p_avr_attribute4
159 ,p_avr_attribute5 => p_avr_attribute5
160 ,p_avr_attribute6 => p_avr_attribute6
161 ,p_avr_attribute7 => p_avr_attribute7
162 ,p_avr_attribute8 => p_avr_attribute8
163 ,p_avr_attribute9 => p_avr_attribute9
164 ,p_avr_attribute10 => p_avr_attribute10
165 ,p_avr_attribute11 => p_avr_attribute11
166 ,p_avr_attribute12 => p_avr_attribute12
167 ,p_avr_attribute13 => p_avr_attribute13
168 ,p_avr_attribute14 => p_avr_attribute14
169 ,p_avr_attribute15 => p_avr_attribute15
170 ,p_avr_attribute16 => p_avr_attribute16
171 ,p_avr_attribute17 => p_avr_attribute17
172 ,p_avr_attribute18 => p_avr_attribute18
173 ,p_avr_attribute19 => p_avr_attribute19
174 ,p_avr_attribute20 => p_avr_attribute20
175 ,p_avr_attribute21 => p_avr_attribute21
176 ,p_avr_attribute22 => p_avr_attribute22
177 ,p_avr_attribute23 => p_avr_attribute23
178 ,p_avr_attribute24 => p_avr_attribute24
179 ,p_avr_attribute25 => p_avr_attribute25
180 ,p_avr_attribute26 => p_avr_attribute26
181 ,p_avr_attribute27 => p_avr_attribute27
182 ,p_avr_attribute28 => p_avr_attribute28
183 ,p_avr_attribute29 => p_avr_attribute29
184 ,p_avr_attribute30 => p_avr_attribute30
185 ,p_object_version_number => l_object_version_number
186 ,p_effective_date => trunc(p_effective_date)
187 );
188 --
189 begin
190 --
191 -- Start of API User Hook for the after hook of create_acty_vrbl_rate
192 --
193 ben_acty_vrbl_rate_bk1.create_acty_vrbl_rate_a
194 (
195 p_acty_vrbl_rt_id => l_acty_vrbl_rt_id
196 ,p_effective_start_date => l_effective_start_date
197 ,p_effective_end_date => l_effective_end_date
198 ,p_acty_base_rt_id => p_acty_base_rt_id
199 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
200 ,p_ordr_num => p_ordr_num
201 ,p_business_group_id => p_business_group_id
202 ,p_avr_attribute_category => p_avr_attribute_category
203 ,p_avr_attribute1 => p_avr_attribute1
204 ,p_avr_attribute2 => p_avr_attribute2
205 ,p_avr_attribute3 => p_avr_attribute3
206 ,p_avr_attribute4 => p_avr_attribute4
207 ,p_avr_attribute5 => p_avr_attribute5
208 ,p_avr_attribute6 => p_avr_attribute6
209 ,p_avr_attribute7 => p_avr_attribute7
210 ,p_avr_attribute8 => p_avr_attribute8
211 ,p_avr_attribute9 => p_avr_attribute9
212 ,p_avr_attribute10 => p_avr_attribute10
213 ,p_avr_attribute11 => p_avr_attribute11
214 ,p_avr_attribute12 => p_avr_attribute12
215 ,p_avr_attribute13 => p_avr_attribute13
216 ,p_avr_attribute14 => p_avr_attribute14
217 ,p_avr_attribute15 => p_avr_attribute15
218 ,p_avr_attribute16 => p_avr_attribute16
219 ,p_avr_attribute17 => p_avr_attribute17
220 ,p_avr_attribute18 => p_avr_attribute18
221 ,p_avr_attribute19 => p_avr_attribute19
222 ,p_avr_attribute20 => p_avr_attribute20
223 ,p_avr_attribute21 => p_avr_attribute21
224 ,p_avr_attribute22 => p_avr_attribute22
225 ,p_avr_attribute23 => p_avr_attribute23
226 ,p_avr_attribute24 => p_avr_attribute24
227 ,p_avr_attribute25 => p_avr_attribute25
228 ,p_avr_attribute26 => p_avr_attribute26
229 ,p_avr_attribute27 => p_avr_attribute27
230 ,p_avr_attribute28 => p_avr_attribute28
231 ,p_avr_attribute29 => p_avr_attribute29
232 ,p_avr_attribute30 => p_avr_attribute30
233 ,p_object_version_number => l_object_version_number
234 ,p_effective_date => trunc(p_effective_date)
235 );
236 exception
237 when hr_api.cannot_find_prog_unit then
238 hr_api.cannot_find_prog_unit_error
239 (p_module_name => 'CREATE_acty_vrbl_rate'
240 ,p_hook_type => 'AP'
241 );
242 --
243 -- End of API User Hook for the after hook of create_acty_vrbl_rate
244 --
245 end;
246 --
247 Open c1;
248 fetch c1 into l_c1;
249 close c1;
250 if l_c1.pgm_id is not null then
251 ben_derivable_rate.rate_prfl_handler('CREATE','ben_pgm_f','pgm_id',l_c1.pgm_id);
252 elsif l_c1.pl_id is not null then
253 ben_derivable_rate.rate_prfl_handler('CREATE','ben_pl_f','pl_id',l_c1.pl_id);
254 elsif l_c1.plip_id is not null then
255 ben_derivable_rate.rate_prfl_handler('CREATE','ben_plip_f','plip_id',l_c1.plip_id);
256 elsif l_c1.ptip_id is not null then
257 ben_derivable_rate.rate_prfl_handler('CREATE','ben_ptip_f','ptip_id',l_c1.ptip_id);
258 elsif l_c1.oipl_id is not null then
259 ben_derivable_rate.rate_prfl_handler('CREATE','ben_oipl_f','oipl_id',l_c1.oipl_id);
260 elsif l_c1.opt_id is not null then
261 --
262 --START Option Level Rates
263 for l_oipl in c_oipl(l_c1.opt_id) loop
264 ben_derivable_rate.rate_prfl_handler('CREATE','ben_oipl_f','oipl_id',l_oipl.oipl_id);
265 end loop ;
266 --END Option Level Rates
267 --
268 end if;
269 --
270 hr_utility.set_location(l_proc, 60);
271 --
272 -- When in validation only mode raise the Validate_Enabled exception
273 --
274 if p_validate then
275 raise hr_api.validate_enabled;
276 end if;
277 --
278 -- Set all output arguments
279 --
280 p_acty_vrbl_rt_id := l_acty_vrbl_rt_id;
281 p_effective_start_date := l_effective_start_date;
282 p_effective_end_date := l_effective_end_date;
283 p_object_version_number := l_object_version_number;
284 --
285 hr_utility.set_location(' Leaving:'||l_proc, 70);
286 --
287 exception
288 --
289 when hr_api.validate_enabled then
290 --
291 -- As the Validate_Enabled exception has been raised
292 -- we must rollback to the savepoint
293 --
294 ROLLBACK TO create_acty_vrbl_rate;
295 --
296 -- Only set output warning arguments
297 -- (Any key or derived arguments must be set to null
298 -- when validation only mode is being used.)
299 --
300 p_acty_vrbl_rt_id := null;
301 p_effective_start_date := null;
302 p_effective_end_date := null;
303 p_object_version_number := null;
304 hr_utility.set_location(' Leaving:'||l_proc, 80);
305 --
306 when others then
310 ROLLBACK TO create_acty_vrbl_rate;
307 --
308 -- A validation or unexpected error has occured
309 --
311 --
312 -- NOCOPY changes.
313 --
314 p_acty_vrbl_rt_id := null;
315 p_effective_start_date := null;
316 p_effective_end_date := null;
317 p_object_version_number := null;
318 --
319 raise;
320 --
321 end create_acty_vrbl_rate;
322 -- ----------------------------------------------------------------------------
323 -- |------------------------< update_acty_vrbl_rate >--- ------------------|
324 -- ----------------------------------------------------------------------------
325 --
326 procedure update_acty_vrbl_rate
327 (p_validate in boolean default false
328 ,p_acty_vrbl_rt_id in number
329 ,p_effective_start_date out nocopy date
330 ,p_effective_end_date out nocopy date
331 ,p_acty_base_rt_id in number default hr_api.g_number
332 ,p_vrbl_rt_prfl_id in number default hr_api.g_number
333 ,p_ordr_num in number default hr_api.g_number
334 ,p_business_group_id in number default hr_api.g_number
335 ,p_avr_attribute_category in varchar2 default hr_api.g_varchar2
336 ,p_avr_attribute1 in varchar2 default hr_api.g_varchar2
337 ,p_avr_attribute2 in varchar2 default hr_api.g_varchar2
338 ,p_avr_attribute3 in varchar2 default hr_api.g_varchar2
339 ,p_avr_attribute4 in varchar2 default hr_api.g_varchar2
340 ,p_avr_attribute5 in varchar2 default hr_api.g_varchar2
341 ,p_avr_attribute6 in varchar2 default hr_api.g_varchar2
342 ,p_avr_attribute7 in varchar2 default hr_api.g_varchar2
343 ,p_avr_attribute8 in varchar2 default hr_api.g_varchar2
344 ,p_avr_attribute9 in varchar2 default hr_api.g_varchar2
345 ,p_avr_attribute10 in varchar2 default hr_api.g_varchar2
346 ,p_avr_attribute11 in varchar2 default hr_api.g_varchar2
347 ,p_avr_attribute12 in varchar2 default hr_api.g_varchar2
348 ,p_avr_attribute13 in varchar2 default hr_api.g_varchar2
349 ,p_avr_attribute14 in varchar2 default hr_api.g_varchar2
350 ,p_avr_attribute15 in varchar2 default hr_api.g_varchar2
351 ,p_avr_attribute16 in varchar2 default hr_api.g_varchar2
352 ,p_avr_attribute17 in varchar2 default hr_api.g_varchar2
353 ,p_avr_attribute18 in varchar2 default hr_api.g_varchar2
354 ,p_avr_attribute19 in varchar2 default hr_api.g_varchar2
355 ,p_avr_attribute20 in varchar2 default hr_api.g_varchar2
356 ,p_avr_attribute21 in varchar2 default hr_api.g_varchar2
357 ,p_avr_attribute22 in varchar2 default hr_api.g_varchar2
358 ,p_avr_attribute23 in varchar2 default hr_api.g_varchar2
359 ,p_avr_attribute24 in varchar2 default hr_api.g_varchar2
360 ,p_avr_attribute25 in varchar2 default hr_api.g_varchar2
361 ,p_avr_attribute26 in varchar2 default hr_api.g_varchar2
362 ,p_avr_attribute27 in varchar2 default hr_api.g_varchar2
363 ,p_avr_attribute28 in varchar2 default hr_api.g_varchar2
364 ,p_avr_attribute29 in varchar2 default hr_api.g_varchar2
365 ,p_avr_attribute30 in varchar2 default hr_api.g_varchar2
366 ,p_object_version_number in out nocopy number
367 ,p_effective_date in date
368 ,p_datetrack_mode in varchar2
369 ) is
370 --
371 -- Declare cursors and local variables
372 --
373 l_proc varchar2(72) := g_package||'update_acty_vrbl_rate';
374 l_object_version_number ben_acty_vrbl_rt_f.object_version_number%TYPE;
375 l_effective_start_date ben_acty_vrbl_rt_f.effective_start_date%TYPE;
376 l_effective_end_date ben_acty_vrbl_rt_f.effective_end_date%TYPE;
377 --
378 begin
379 --
380 hr_utility.set_location('Entering:'|| l_proc, 10);
381 --
382 -- Issue a savepoint if operating in validation only mode
383 --
384 savepoint update_acty_vrbl_rate;
385 --
386 hr_utility.set_location(l_proc, 20);
387 --
388 -- Process Logic
389 --
390 l_object_version_number := p_object_version_number;
391 --
392 begin
393 --
394 -- Start of API User Hook for the before hook of update_acty_vrbl_rate
395 --
396 ben_acty_vrbl_rate_bk2.update_acty_vrbl_rate_b
397 (
398 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
399 ,p_acty_base_rt_id => p_acty_base_rt_id
400 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
401 ,p_ordr_num => p_ordr_num
402 ,p_business_group_id => p_business_group_id
403 ,p_avr_attribute_category => p_avr_attribute_category
404 ,p_avr_attribute1 => p_avr_attribute1
405 ,p_avr_attribute2 => p_avr_attribute2
406 ,p_avr_attribute3 => p_avr_attribute3
407 ,p_avr_attribute4 => p_avr_attribute4
408 ,p_avr_attribute5 => p_avr_attribute5
409 ,p_avr_attribute6 => p_avr_attribute6
410 ,p_avr_attribute7 => p_avr_attribute7
411 ,p_avr_attribute8 => p_avr_attribute8
415 ,p_avr_attribute12 => p_avr_attribute12
412 ,p_avr_attribute9 => p_avr_attribute9
413 ,p_avr_attribute10 => p_avr_attribute10
414 ,p_avr_attribute11 => p_avr_attribute11
416 ,p_avr_attribute13 => p_avr_attribute13
417 ,p_avr_attribute14 => p_avr_attribute14
418 ,p_avr_attribute15 => p_avr_attribute15
419 ,p_avr_attribute16 => p_avr_attribute16
420 ,p_avr_attribute17 => p_avr_attribute17
421 ,p_avr_attribute18 => p_avr_attribute18
422 ,p_avr_attribute19 => p_avr_attribute19
423 ,p_avr_attribute20 => p_avr_attribute20
424 ,p_avr_attribute21 => p_avr_attribute21
425 ,p_avr_attribute22 => p_avr_attribute22
426 ,p_avr_attribute23 => p_avr_attribute23
427 ,p_avr_attribute24 => p_avr_attribute24
428 ,p_avr_attribute25 => p_avr_attribute25
429 ,p_avr_attribute26 => p_avr_attribute26
430 ,p_avr_attribute27 => p_avr_attribute27
431 ,p_avr_attribute28 => p_avr_attribute28
432 ,p_avr_attribute29 => p_avr_attribute29
433 ,p_avr_attribute30 => p_avr_attribute30
434 ,p_object_version_number => p_object_version_number
435 ,p_effective_date => trunc(p_effective_date)
436 ,p_datetrack_mode => p_datetrack_mode
437 );
438 exception
439 when hr_api.cannot_find_prog_unit then
440 hr_api.cannot_find_prog_unit_error
441 (p_module_name => 'UPDATE_acty_vrbl_rate'
442 ,p_hook_type => 'BP'
443 );
444 --
445 -- End of API User Hook for the before hook of update_acty_vrbl_rate
446 --
447 end;
448 --
449 ben_avr_upd.upd
450 (
451 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
452 ,p_effective_start_date => l_effective_start_date
453 ,p_effective_end_date => l_effective_end_date
454 ,p_acty_base_rt_id => p_acty_base_rt_id
455 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
456 ,p_ordr_num => p_ordr_num
457 ,p_business_group_id => p_business_group_id
458 ,p_avr_attribute_category => p_avr_attribute_category
459 ,p_avr_attribute1 => p_avr_attribute1
460 ,p_avr_attribute2 => p_avr_attribute2
461 ,p_avr_attribute3 => p_avr_attribute3
462 ,p_avr_attribute4 => p_avr_attribute4
463 ,p_avr_attribute5 => p_avr_attribute5
464 ,p_avr_attribute6 => p_avr_attribute6
465 ,p_avr_attribute7 => p_avr_attribute7
466 ,p_avr_attribute8 => p_avr_attribute8
467 ,p_avr_attribute9 => p_avr_attribute9
468 ,p_avr_attribute10 => p_avr_attribute10
469 ,p_avr_attribute11 => p_avr_attribute11
470 ,p_avr_attribute12 => p_avr_attribute12
471 ,p_avr_attribute13 => p_avr_attribute13
472 ,p_avr_attribute14 => p_avr_attribute14
473 ,p_avr_attribute15 => p_avr_attribute15
474 ,p_avr_attribute16 => p_avr_attribute16
475 ,p_avr_attribute17 => p_avr_attribute17
476 ,p_avr_attribute18 => p_avr_attribute18
477 ,p_avr_attribute19 => p_avr_attribute19
478 ,p_avr_attribute20 => p_avr_attribute20
479 ,p_avr_attribute21 => p_avr_attribute21
480 ,p_avr_attribute22 => p_avr_attribute22
481 ,p_avr_attribute23 => p_avr_attribute23
482 ,p_avr_attribute24 => p_avr_attribute24
483 ,p_avr_attribute25 => p_avr_attribute25
484 ,p_avr_attribute26 => p_avr_attribute26
485 ,p_avr_attribute27 => p_avr_attribute27
486 ,p_avr_attribute28 => p_avr_attribute28
487 ,p_avr_attribute29 => p_avr_attribute29
488 ,p_avr_attribute30 => p_avr_attribute30
489 ,p_object_version_number => l_object_version_number
490 ,p_effective_date => trunc(p_effective_date)
491 ,p_datetrack_mode => p_datetrack_mode
492 );
493 --
494 begin
495 --
496 -- Start of API User Hook for the after hook of update_acty_vrbl_rate
497 --
498 ben_acty_vrbl_rate_bk2.update_acty_vrbl_rate_a
499 (
500 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
501 ,p_effective_start_date => l_effective_start_date
502 ,p_effective_end_date => l_effective_end_date
503 ,p_acty_base_rt_id => p_acty_base_rt_id
504 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
505 ,p_ordr_num => p_ordr_num
506 ,p_business_group_id => p_business_group_id
507 ,p_avr_attribute_category => p_avr_attribute_category
508 ,p_avr_attribute1 => p_avr_attribute1
509 ,p_avr_attribute2 => p_avr_attribute2
510 ,p_avr_attribute3 => p_avr_attribute3
511 ,p_avr_attribute4 => p_avr_attribute4
512 ,p_avr_attribute5 => p_avr_attribute5
516 ,p_avr_attribute9 => p_avr_attribute9
513 ,p_avr_attribute6 => p_avr_attribute6
514 ,p_avr_attribute7 => p_avr_attribute7
515 ,p_avr_attribute8 => p_avr_attribute8
517 ,p_avr_attribute10 => p_avr_attribute10
518 ,p_avr_attribute11 => p_avr_attribute11
519 ,p_avr_attribute12 => p_avr_attribute12
520 ,p_avr_attribute13 => p_avr_attribute13
521 ,p_avr_attribute14 => p_avr_attribute14
522 ,p_avr_attribute15 => p_avr_attribute15
523 ,p_avr_attribute16 => p_avr_attribute16
524 ,p_avr_attribute17 => p_avr_attribute17
525 ,p_avr_attribute18 => p_avr_attribute18
526 ,p_avr_attribute19 => p_avr_attribute19
527 ,p_avr_attribute20 => p_avr_attribute20
528 ,p_avr_attribute21 => p_avr_attribute21
529 ,p_avr_attribute22 => p_avr_attribute22
530 ,p_avr_attribute23 => p_avr_attribute23
531 ,p_avr_attribute24 => p_avr_attribute24
532 ,p_avr_attribute25 => p_avr_attribute25
533 ,p_avr_attribute26 => p_avr_attribute26
534 ,p_avr_attribute27 => p_avr_attribute27
535 ,p_avr_attribute28 => p_avr_attribute28
536 ,p_avr_attribute29 => p_avr_attribute29
537 ,p_avr_attribute30 => p_avr_attribute30
538 ,p_object_version_number => l_object_version_number
539 ,p_effective_date => trunc(p_effective_date)
540 ,p_datetrack_mode => p_datetrack_mode
541 );
542 exception
543 when hr_api.cannot_find_prog_unit then
544 hr_api.cannot_find_prog_unit_error
545 (p_module_name => 'UPDATE_acty_vrbl_rate'
546 ,p_hook_type => 'AP'
547 );
548 --
549 -- End of API User Hook for the after hook of update_acty_vrbl_rate
550 --
551 end;
552 --
553 hr_utility.set_location(l_proc, 60);
554 --
555 -- When in validation only mode raise the Validate_Enabled exception
556 --
557 if p_validate then
558 raise hr_api.validate_enabled;
559 end if;
560 --
561 -- Set all output arguments
562 --
563 p_object_version_number := l_object_version_number;
564 p_effective_start_date := l_effective_start_date;
565 p_effective_end_date := l_effective_end_date;
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 70);
568 --
569 exception
570 --
571 when hr_api.validate_enabled then
572 --
573 -- As the Validate_Enabled exception has been raised
574 -- we must rollback to the savepoint
575 --
576 ROLLBACK TO update_acty_vrbl_rate;
577 --
578 -- Only set output warning arguments
579 -- (Any key or derived arguments must be set to null
580 -- when validation only mode is being used.)
581 --
582 hr_utility.set_location(' Leaving:'||l_proc, 80);
583 --
584 when others then
585 --
586 -- A validation or unexpected error has occured
587 --
588 ROLLBACK TO update_acty_vrbl_rate;
589 --
590 -- NOCOPY changes
591 --
592 p_effective_start_date := null;
593 p_effective_end_date := null;
594 p_object_version_number := l_object_version_number;
595 --
596 raise;
597 --
598 end update_acty_vrbl_rate;
599 -- ----------------------------------------------------------------------------
600 -- |------------------------< delete_acty_vrbl_rate >----------------------|
601 -- ----------------------------------------------------------------------------
602 --
603 procedure delete_acty_vrbl_rate
604 (p_validate in boolean default false
605 ,p_acty_vrbl_rt_id in number
606 ,p_effective_start_date out nocopy date
607 ,p_effective_end_date out nocopy date
608 ,p_object_version_number in out nocopy number
609 ,p_effective_date in date
610 ,p_datetrack_mode in varchar2
611 ) is
612 --
613 -- Declare cursors and local variables
614 cursor c1 is select pgm_id,pl_id,ptip_id,plip_id,oipl_id,opt_id
615 from ben_acty_base_rt_f abr, ben_acty_vrbl_rt_f avr
616 where abr.acty_base_rt_id = avr.acty_base_rt_id and
617 avr.acty_vrbl_rt_id = p_acty_vrbl_rt_id;
618
619 --
620 l_c1 c1%ROWTYPE;
621 --
622 -- Start Option Level Rates
623 --
624 cursor c_oipl(p_opt_id number) is
625 select distinct oipl_id
626 from ben_oipl_f oipl
627 where oipl.opt_id = p_opt_id
628 and not exists ( select abr.oipl_id
629 from ben_acty_base_rt_f abr
630 where abr.oipl_id = oipl.oipl_id
631 and p_effective_date between abr.effective_start_date
632 and abr.effective_end_date ) ;
633 --
634 -- End Option Level Rates
635 --
636 l_proc varchar2(72) := g_package||'update_acty_vrbl_rate';
637 l_object_version_number ben_acty_vrbl_rt_f.object_version_number%TYPE;
638 l_effective_start_date ben_acty_vrbl_rt_f.effective_start_date%TYPE;
642 --
639 l_effective_end_date ben_acty_vrbl_rt_f.effective_end_date%TYPE;
640 --
641 begin
643 hr_utility.set_location('Entering:'|| l_proc, 10);
644 --
645 -- Issue a savepoint if operating in validation only mode
646 --
647 savepoint delete_acty_vrbl_rate;
648 --
649 hr_utility.set_location(l_proc, 20);
650 open c1;
651 fetch c1 into l_c1;
652 close c1;
653 --
654 -- Process Logic
655 --
656 l_object_version_number := p_object_version_number;
657 --
658 --
659 begin
660 --
661 -- Start of API User Hook for the before hook of delete_acty_vrbl_rate
662 --
663 ben_acty_vrbl_rate_bk3.delete_acty_vrbl_rate_b
664 (
665 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
666 ,p_object_version_number => p_object_version_number
667 ,p_effective_date => trunc(p_effective_date)
668 ,p_datetrack_mode => p_datetrack_mode
669 );
670 exception
671 when hr_api.cannot_find_prog_unit then
672 hr_api.cannot_find_prog_unit_error
673 (p_module_name => 'DELETE_acty_vrbl_rate'
674 ,p_hook_type => 'BP'
675 );
676 --
677 -- End of API User Hook for the before hook of delete_acty_vrbl_rate
678 --
679 end;
680 --
681 ben_avr_del.del
682 (
683 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
684 ,p_effective_start_date => l_effective_start_date
685 ,p_effective_end_date => l_effective_end_date
686 ,p_object_version_number => l_object_version_number
687 ,p_effective_date => p_effective_date
688 ,p_datetrack_mode => p_datetrack_mode
689 );
690 --
691 begin
692 --
693 -- Start of API User Hook for the after hook of delete_acty_vrbl_rate
694 --
695 ben_acty_vrbl_rate_bk3.delete_acty_vrbl_rate_a
696 (
697 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
698 ,p_effective_start_date => l_effective_start_date
699 ,p_effective_end_date => l_effective_end_date
700 ,p_object_version_number => l_object_version_number
701 ,p_effective_date => trunc(p_effective_date)
702 ,p_datetrack_mode => p_datetrack_mode
703 );
704 exception
705 when hr_api.cannot_find_prog_unit then
706 hr_api.cannot_find_prog_unit_error
707 (p_module_name => 'DELETE_acty_vrbl_rate'
708 ,p_hook_type => 'AP'
709 );
710 --
711 -- End of API User Hook for the after hook of delete_acty_vrbl_rate
712 --
713 end;
714 --
715 if l_c1.pgm_id is not null then
716 ben_derivable_rate.rate_prfl_handler('DELETE','ben_pgm_f','pgm_id',l_c1.pgm_id);
717 elsif l_c1.pl_id is not null then
718 ben_derivable_rate.rate_prfl_handler('DELETE','ben_pl_f','pl_id',l_c1.pl_id);
719 elsif l_c1.plip_id is not null then
720 ben_derivable_rate.rate_prfl_handler('DELETE','ben_plip_f','plip_id',l_c1.plip_id);
721 elsif l_c1.ptip_id is not null then
722 ben_derivable_rate.rate_prfl_handler('DELETE','ben_ptip_f','ptip_id',l_c1.ptip_id);
723 elsif l_c1.oipl_id is not null then
724 ben_derivable_rate.rate_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_c1.oipl_id);
725 elsif l_c1.opt_id is not null then
726 --
727 for l_oipl in c_oipl(l_c1.opt_id) loop
728 --
729 ben_derivable_rate.rate_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_oipl.oipl_id);
730 --
731 end loop ;
732 --
733 end if;
734 --
735 hr_utility.set_location(l_proc, 60);
736 --
737 -- When in validation only mode raise the Validate_Enabled exception
738 --
739 if p_validate then
740 raise hr_api.validate_enabled;
741 end if;
742 --
743 hr_utility.set_location(' Leaving:'||l_proc, 70);
744 --
745 exception
746 --
747 when hr_api.validate_enabled then
748 --
749 -- As the Validate_Enabled exception has been raised
750 -- we must rollback to the savepoint
751 --
752 ROLLBACK TO delete_acty_vrbl_rate;
753 --
754 -- Only set output warning arguments
755 -- (Any key or derived arguments must be set to null
756 -- when validation only mode is being used.)
757 --
758 p_effective_start_date := null;
759 p_effective_end_date := null;
760 --
761 when others then
762 --
763 -- A validation or unexpected error has occured
764 --
765 ROLLBACK TO delete_acty_vrbl_rate;
766 --
767 -- NOCOPY changes
768 --
769 p_effective_start_date := null;
770 p_effective_end_date := null;
771 p_object_version_number := l_object_version_number;
772 --
773 raise;
774 --
775 end delete_acty_vrbl_rate;
776 --
777 -- ----------------------------------------------------------------------------
778 -- |-------------------------------< lck >------------------------------------|
779 -- ----------------------------------------------------------------------------
780 --
781 procedure lck
782 (
783 p_acty_vrbl_rt_id in number
784 ,p_object_version_number in number
788 ,p_validation_end_date out nocopy date
785 ,p_effective_date in date
786 ,p_datetrack_mode in varchar2
787 ,p_validation_start_date out nocopy date
789 ) is
790 --
791 --
792 -- Declare cursors and local variables
793 --
794 l_proc varchar2(72) := g_package||'lck';
795 l_validation_start_date date;
796 l_validation_end_date date;
797 --
798 begin
799 --
800 hr_utility.set_location('Entering:'|| l_proc, 10);
801 --
802 ben_avr_shd.lck
803 (
804 p_acty_vrbl_rt_id => p_acty_vrbl_rt_id
805 ,p_validation_start_date => l_validation_start_date
806 ,p_validation_end_date => l_validation_end_date
807 ,p_object_version_number => p_object_version_number
808 ,p_effective_date => p_effective_date
809 ,p_datetrack_mode => p_datetrack_mode
810 );
811 --
812 hr_utility.set_location(' Leaving:'||l_proc, 70);
813 --
814 end lck;
815 --
816 end ben_acty_vrbl_rate_api;