DBA Data[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;