DBA Data[Home] [Help]

PACKAGE BODY: APPS.BEN_VMR_DEL

Source


1 Package Body ben_vmr_del as
2 /* $Header: bevmrrhi.pkb 120.0.12010000.2 2008/08/05 15:37:25 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  ben_vmr_del.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_delete_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 --   This procedure controls the actual dml delete logic for the datetrack
17 --   delete modes: ZAP, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE. The
18 --   execution is as follows:
19 --   1) To set and unset the g_api_dml status as required (as we are about to
20 --      perform dml).
21 --   2) If the delete mode is DELETE_NEXT_CHANGE then delete where the
22 --      effective start date is equal to the validation start date.
23 --   3) If the delete mode is not DELETE_NEXT_CHANGE then delete
24 --      all rows for the entity where the effective start date is greater
25 --      than or equal to the validation start date.
26 --   4) To raise any errors.
27 --
28 -- Prerequisites:
29 --   This is an internal private procedure which must be called from the
30 --   delete_dml procedure.
31 --
32 -- In Parameters:
33 --   A Pl/Sql record structre.
34 --
35 -- Post Success:
36 --   The specified row will be delete from the schema.
37 --
38 -- Post Failure:
39 --   On the delete dml failure it is important to note that we always reset the
40 --   g_api_dml status to false.
41 --   If any other error is reported, the error will be raised after the
42 --   g_api_dml status is reset.
43 --
44 -- Developer Implementation Notes:
45 --   This is an internal private procedure which must be called from the
46 --   delete_dml procedure.
47 --
48 -- Access Status:
49 --   Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure dt_delete_dml
54 	(p_rec 			 in out nocopy ben_vmr_shd.g_rec_type,
55 	 p_effective_date	 in	date,
56 	 p_datetrack_mode	 in	varchar2,
57 	 p_validation_start_date in	date,
58 	 p_validation_end_date	 in	date) is
59 --
60   l_proc	varchar2(72) := g_package||'dt_delete_dml';
61 --
62 Begin
63   hr_utility.set_location('Entering:'||l_proc, 5);
64   If (p_datetrack_mode = 'DELETE_NEXT_CHANGE') then
65     hr_utility.set_location(l_proc, 10);
66     ben_vmr_shd.g_api_dml := true;  -- Set the api dml status
67     --
68     -- Delete the where the effective start date is equal
69     -- to the validation end date.
70     --
71     delete from ben_vrbl_mtchg_rt_f
72     where       vrbl_mtchg_rt_id = p_rec.vrbl_mtchg_rt_id
73     and	  effective_start_date = p_validation_start_date;
74     --
75     ben_vmr_shd.g_api_dml := false;   -- Unset the api dml status
76   Else
77     hr_utility.set_location(l_proc, 15);
78     ben_vmr_shd.g_api_dml := true;  -- Set the api dml status
79     --
80     -- Delete the row(s) where the effective start date is greater than
81     -- or equal to the validation start date.
82     --
83     delete from ben_vrbl_mtchg_rt_f
84     where        vrbl_mtchg_rt_id = p_rec.vrbl_mtchg_rt_id
85     and	  effective_start_date >= p_validation_start_date;
86     --
87     ben_vmr_shd.g_api_dml := false;   -- Unset the api dml status
88   End If;
89   --
90   hr_utility.set_location(' Leaving:'||l_proc, 20);
91 --
92 Exception
93   When Others Then
94     ben_vmr_shd.g_api_dml := false;   -- Unset the api dml status
95     Raise;
96 End dt_delete_dml;
97 --
98 -- ----------------------------------------------------------------------------
99 -- |------------------------------< delete_dml >------------------------------|
100 -- ----------------------------------------------------------------------------
101 Procedure delete_dml
102 	(p_rec 			 in out nocopy ben_vmr_shd.g_rec_type,
103 	 p_effective_date	 in	date,
104 	 p_datetrack_mode	 in	varchar2,
105 	 p_validation_start_date in	date,
106 	 p_validation_end_date	 in	date) is
107 --
108   l_proc	varchar2(72) := g_package||'delete_dml';
109 --
110 Begin
111   hr_utility.set_location('Entering:'||l_proc, 5);
112   --
113   dt_delete_dml(p_rec			=> p_rec,
114 		p_effective_date	=> p_effective_date,
115 		p_datetrack_mode	=> p_datetrack_mode,
116        		p_validation_start_date	=> p_validation_start_date,
117 		p_validation_end_date	=> p_validation_end_date);
118   --
119   hr_utility.set_location(' Leaving:'||l_proc, 10);
120 End delete_dml;
121 --
122 -- ----------------------------------------------------------------------------
123 -- |----------------------------< dt_pre_delete >-----------------------------|
124 -- ----------------------------------------------------------------------------
125 -- {Start Of Comments}
126 --
127 -- Description:
128 --   The dt_pre_delete process controls the execution of dml
129 --   for the datetrack modes: DELETE, FUTURE_CHANGE
130 --   and DELETE_NEXT_CHANGE only.
131 --
132 -- Prerequisites:
133 --   This is an internal procedure which is called from the pre_delete
134 --   procedure.
135 --
136 -- In Parameters:
137 --   A Pl/Sql record structre.
138 --
139 -- Post Success:
140 --   Processing continues.
141 --
142 -- Post Failure:
143 --   If an error has occurred, an error message and exception will be raised
144 --   but not handled.
145 --
146 -- Developer Implementation Notes:
147 --   This is an internal procedure which is required by Datetrack. Don't
148 --   remove or modify.
149 --
150 -- Access Status:
151 --   Internal Row Handler Use Only.
152 --
153 -- {End Of Comments}
154 -- ----------------------------------------------------------------------------
155 Procedure dt_pre_delete
156 	(p_rec 			 in out nocopy ben_vmr_shd.g_rec_type,
157 	 p_effective_date	 in	date,
158 	 p_datetrack_mode	 in	varchar2,
159 	 p_validation_start_date in	date,
160 	 p_validation_end_date	 in	date) is
161 --
162   l_proc	varchar2(72) := g_package||'dt_pre_delete';
163 --
164 Begin
165   hr_utility.set_location('Entering:'||l_proc, 5);
166   --
167   If (p_datetrack_mode <> 'ZAP') then
168     --
169     p_rec.effective_start_date := ben_vmr_shd.g_old_rec.effective_start_date;
170     --
171     If (p_datetrack_mode = 'DELETE') then
172       p_rec.effective_end_date := p_validation_start_date - 1;
173     Else
174       p_rec.effective_end_date := p_validation_end_date;
175     End If;
176     --
177     -- Update the current effective end date record
178     --
179     ben_vmr_shd.upd_effective_end_date
180       (p_effective_date	        => p_effective_date,
181        p_base_key_value	        => p_rec.vrbl_mtchg_rt_id,
182        p_new_effective_end_date => p_rec.effective_end_date,
183        p_validation_start_date  => p_validation_start_date,
184        p_validation_end_date	=> p_validation_end_date,
185        p_object_version_number  => p_rec.object_version_number);
186   Else
187     p_rec.effective_start_date := null;
188     p_rec.effective_end_date   := null;
189   End If;
190   hr_utility.set_location(' Leaving:'||l_proc, 10);
191 End dt_pre_delete;
192 --
193 -- ----------------------------------------------------------------------------
194 -- |------------------------------< pre_delete >------------------------------|
195 -- ----------------------------------------------------------------------------
196 -- {Start Of Comments}
197 --
198 -- Description:
199 --   This private procedure contains any processing which is required before
200 --   the delete dml.
201 --
202 -- Prerequisites:
203 --   This is an internal procedure which is called from the del procedure.
204 --
205 -- In Parameters:
206 --   A Pl/Sql record structre.
207 --
208 -- Post Success:
209 --   Processing continues.
210 --
211 -- Post Failure:
212 --   If an error has occurred, an error message and exception will be raised
213 --   but not handled.
214 --
215 -- Developer Implementation Notes:
216 --   Any pre-processing required before the delete dml is issued should be
217 --   coded within this procedure. It is important to note that any 3rd party
218 --   maintenance should be reviewed before placing in this procedure. The call
219 --   to the dt_delete_dml procedure should NOT be removed.
220 --
221 -- Access Status:
222 --   Internal Row Handler Use Only.
223 --
224 -- {End Of Comments}
225 -- ----------------------------------------------------------------------------
226 Procedure pre_delete
227 	(p_rec 			 in out nocopy ben_vmr_shd.g_rec_type,
228 	 p_effective_date	 in	date,
229 	 p_datetrack_mode	 in	varchar2,
230 	 p_validation_start_date in	date,
231 	 p_validation_end_date	 in	date) is
232 --
233   l_proc	varchar2(72) := g_package||'pre_delete';
234 --
235   --
236 --
237 Begin
238   hr_utility.set_location('Entering:'||l_proc, 5);
239   --
240   --
241   --
242   dt_pre_delete
243     (p_rec 		     => p_rec,
244      p_effective_date	     => p_effective_date,
245      p_datetrack_mode	     => p_datetrack_mode,
246      p_validation_start_date => p_validation_start_date,
247      p_validation_end_date   => p_validation_end_date);
248   --
249   hr_utility.set_location(' Leaving:'||l_proc, 10);
250 End pre_delete;
251 --
252 -- ----------------------------------------------------------------------------
253 -- |-----------------------------< post_delete >------------------------------|
254 -- ----------------------------------------------------------------------------
255 -- {Start Of Comments}
256 --
257 -- Description:
258 --   This private procedure contains any processing which is required after the
259 --   delete dml.
260 --
261 -- Prerequisites:
262 --   This is an internal procedure which is called from the del procedure.
263 --
264 -- In Parameters:
265 --   A Pl/Sql record structre.
266 --
267 -- Post Success:
268 --   Processing continues.
269 --
270 -- Post Failure:
271 --   If an error has occurred, an error message and exception will be raised
272 --   but not handled.
273 --
274 -- Developer Implementation Notes:
275 --   Any post-processing required after the delete dml is issued should be
276 --   coded within this procedure. It is important to note that any 3rd party
277 --   maintenance should be reviewed before placing in this procedure.
278 --
279 -- Access Status:
280 --   Internal Row Handler Use Only.
281 --
282 -- {End Of Comments}
283 -- ----------------------------------------------------------------------------
284 Procedure post_delete
285 	(p_rec 			 in ben_vmr_shd.g_rec_type,
286 	 p_effective_date	 in date,
287 	 p_datetrack_mode	 in varchar2,
288 	 p_validation_start_date in date,
289 	 p_validation_end_date	 in date) is
290 --
291   l_proc	varchar2(72) := g_package||'post_delete';
292 --
293 Begin
294   hr_utility.set_location('Entering:'||l_proc, 5);
295 --
296   --
297   -- Start of API User Hook for post_delete.
298   --
299   begin
300     --
301     ben_vmr_rkd.after_delete
302       (
303   p_vrbl_mtchg_rt_id              =>p_rec.vrbl_mtchg_rt_id
304  ,p_datetrack_mode                =>p_datetrack_mode
305  ,p_validation_start_date         =>p_validation_start_date
306  ,p_validation_end_date           =>p_validation_end_date
307  ,p_effective_start_date          =>p_rec.effective_start_date
308  ,p_effective_end_date            =>p_rec.effective_end_date
309  ,p_effective_end_date_o          =>ben_vmr_shd.g_old_rec.effective_end_date
310  ,p_effective_start_date_o        =>ben_vmr_shd.g_old_rec.effective_start_date
311  ,p_no_mx_pct_of_py_num_flag_o    =>ben_vmr_shd.g_old_rec.no_mx_pct_of_py_num_flag
312  ,p_to_pct_val_o                  =>ben_vmr_shd.g_old_rec.to_pct_val
313  ,p_no_mx_amt_of_py_num_flag_o    =>ben_vmr_shd.g_old_rec.no_mx_amt_of_py_num_flag
314  ,p_mx_pct_of_py_num_o            =>ben_vmr_shd.g_old_rec.mx_pct_of_py_num
315  ,p_no_mx_mtch_amt_flag_o         =>ben_vmr_shd.g_old_rec.no_mx_mtch_amt_flag
316  ,p_ordr_num_o                    =>ben_vmr_shd.g_old_rec.ordr_num
317  ,p_pct_val_o                     =>ben_vmr_shd.g_old_rec.pct_val
318  ,p_mx_mtch_amt_o                 =>ben_vmr_shd.g_old_rec.mx_mtch_amt
319  ,p_mx_amt_of_py_num_o            =>ben_vmr_shd.g_old_rec.mx_amt_of_py_num
320  ,p_mn_mtch_amt_o                 =>ben_vmr_shd.g_old_rec.mn_mtch_amt
321  ,p_mtchg_rt_calc_rl_o            =>ben_vmr_shd.g_old_rec.mtchg_rt_calc_rl
322 ,p_cntnu_mtch_aftr_max_rl_fla_o =>ben_vmr_shd.g_old_rec.cntnu_mtch_aftr_max_rl_flag
323  ,p_from_pct_val_o                =>ben_vmr_shd.g_old_rec.from_pct_val
324  ,p_vrbl_rt_prfl_id_o             =>ben_vmr_shd.g_old_rec.vrbl_rt_prfl_id
325  ,p_business_group_id_o           =>ben_vmr_shd.g_old_rec.business_group_id
326  ,p_vmr_attribute_category_o      =>ben_vmr_shd.g_old_rec.vmr_attribute_category
327  ,p_vmr_attribute1_o              =>ben_vmr_shd.g_old_rec.vmr_attribute1
328  ,p_vmr_attribute2_o              =>ben_vmr_shd.g_old_rec.vmr_attribute2
329  ,p_vmr_attribute3_o              =>ben_vmr_shd.g_old_rec.vmr_attribute3
330  ,p_vmr_attribute4_o              =>ben_vmr_shd.g_old_rec.vmr_attribute4
331  ,p_vmr_attribute5_o              =>ben_vmr_shd.g_old_rec.vmr_attribute5
332  ,p_vmr_attribute6_o              =>ben_vmr_shd.g_old_rec.vmr_attribute6
333  ,p_vmr_attribute7_o              =>ben_vmr_shd.g_old_rec.vmr_attribute7
334  ,p_vmr_attribute8_o              =>ben_vmr_shd.g_old_rec.vmr_attribute8
335  ,p_vmr_attribute9_o              =>ben_vmr_shd.g_old_rec.vmr_attribute9
336  ,p_vmr_attribute10_o             =>ben_vmr_shd.g_old_rec.vmr_attribute10
337  ,p_vmr_attribute11_o             =>ben_vmr_shd.g_old_rec.vmr_attribute11
338  ,p_vmr_attribute12_o             =>ben_vmr_shd.g_old_rec.vmr_attribute12
339  ,p_vmr_attribute13_o             =>ben_vmr_shd.g_old_rec.vmr_attribute13
340  ,p_vmr_attribute14_o             =>ben_vmr_shd.g_old_rec.vmr_attribute14
341  ,p_vmr_attribute15_o             =>ben_vmr_shd.g_old_rec.vmr_attribute15
342  ,p_vmr_attribute16_o             =>ben_vmr_shd.g_old_rec.vmr_attribute16
343  ,p_vmr_attribute17_o             =>ben_vmr_shd.g_old_rec.vmr_attribute17
344  ,p_vmr_attribute18_o             =>ben_vmr_shd.g_old_rec.vmr_attribute18
345  ,p_vmr_attribute19_o             =>ben_vmr_shd.g_old_rec.vmr_attribute19
346  ,p_vmr_attribute20_o             =>ben_vmr_shd.g_old_rec.vmr_attribute20
347  ,p_vmr_attribute21_o             =>ben_vmr_shd.g_old_rec.vmr_attribute21
348  ,p_vmr_attribute22_o             =>ben_vmr_shd.g_old_rec.vmr_attribute22
349  ,p_vmr_attribute23_o             =>ben_vmr_shd.g_old_rec.vmr_attribute23
350  ,p_vmr_attribute24_o             =>ben_vmr_shd.g_old_rec.vmr_attribute24
351  ,p_vmr_attribute25_o             =>ben_vmr_shd.g_old_rec.vmr_attribute25
352  ,p_vmr_attribute26_o             =>ben_vmr_shd.g_old_rec.vmr_attribute26
353  ,p_vmr_attribute27_o             =>ben_vmr_shd.g_old_rec.vmr_attribute27
354  ,p_vmr_attribute28_o             =>ben_vmr_shd.g_old_rec.vmr_attribute28
355  ,p_vmr_attribute29_o             =>ben_vmr_shd.g_old_rec.vmr_attribute29
356  ,p_vmr_attribute30_o             =>ben_vmr_shd.g_old_rec.vmr_attribute30
357  ,p_object_version_number_o       =>ben_vmr_shd.g_old_rec.object_version_number
358       );
359     --
360   exception
361     --
362     when hr_api.cannot_find_prog_unit then
363       --
364       hr_api.cannot_find_prog_unit_error
365         (p_module_name => 'ben_vrbl_mtchg_rt_f'
366         ,p_hook_type   => 'AD');
367       --
368   end;
369   --
370   -- End of API User Hook for post_delete.
371   --
372   --
373   hr_utility.set_location(' Leaving:'||l_proc, 10);
374 End post_delete;
375 --
376 -- ----------------------------------------------------------------------------
377 -- |---------------------------------< del >----------------------------------|
378 -- ----------------------------------------------------------------------------
379 Procedure del
380   (
381   p_rec			in out nocopy 	ben_vmr_shd.g_rec_type,
382   p_effective_date	in 	date,
383   p_datetrack_mode	in 	varchar2
384   ) is
385 --
386   l_proc			varchar2(72) := g_package||'del';
387   l_validation_start_date	date;
388   l_validation_end_date		date;
389 --
390 Begin
391   hr_utility.set_location('Entering:'||l_proc, 5);
392   --
393   -- Ensure that the DateTrack delete mode is valid
394   --
395   dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
396   --
397   -- We must lock the row which we need to delete.
398   --
399   ben_vmr_shd.lck
400 	(p_effective_date	 => p_effective_date,
401       	 p_datetrack_mode	 => p_datetrack_mode,
402       	 p_vrbl_mtchg_rt_id	 => p_rec.vrbl_mtchg_rt_id,
403       	 p_object_version_number => p_rec.object_version_number,
404       	 p_validation_start_date => l_validation_start_date,
405       	 p_validation_end_date	 => l_validation_end_date);
406   --
407   -- Call the supporting delete validate operation
408   --
409   ben_vmr_bus.delete_validate
410 	(p_rec			 => p_rec,
411 	 p_effective_date	 => p_effective_date,
412 	 p_datetrack_mode	 => p_datetrack_mode,
413 	 p_validation_start_date => l_validation_start_date,
414 	 p_validation_end_date	 => l_validation_end_date);
415   --
416   -- Call the supporting pre-delete operation
417   --
418   pre_delete
419 	(p_rec			 => p_rec,
420 	 p_effective_date	 => p_effective_date,
421 	 p_datetrack_mode	 => p_datetrack_mode,
422 	 p_validation_start_date => l_validation_start_date,
423 	 p_validation_end_date	 => l_validation_end_date);
424   --
425   -- Delete the row.
426   --
427   delete_dml
428 	(p_rec			 => p_rec,
429 	 p_effective_date	 => p_effective_date,
430 	 p_datetrack_mode	 => p_datetrack_mode,
431 	 p_validation_start_date => l_validation_start_date,
432 	 p_validation_end_date	 => l_validation_end_date);
433   --
434   -- Call the supporting post-delete operation
435   --
436   post_delete
437 	(p_rec			 => p_rec,
438 	 p_effective_date	 => p_effective_date,
439 	 p_datetrack_mode	 => p_datetrack_mode,
440 	 p_validation_start_date => l_validation_start_date,
441 	 p_validation_end_date	 => l_validation_end_date);
442 End del;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |---------------------------------< del >----------------------------------|
446 -- ----------------------------------------------------------------------------
447 Procedure del
448   (
449   p_vrbl_mtchg_rt_id	  in 	 number,
450   p_effective_start_date     out nocopy date,
451   p_effective_end_date	     out nocopy date,
452   p_object_version_number in out nocopy number,
453   p_effective_date	  in     date,
454   p_datetrack_mode  	  in     varchar2
455   ) is
456 --
457   l_rec		ben_vmr_shd.g_rec_type;
458   l_proc	varchar2(72) := g_package||'del';
459 --
460 Begin
461   hr_utility.set_location('Entering:'||l_proc, 5);
462   --
463   -- As the delete procedure accepts a plsql record structure we do need to
464   -- convert the  arguments into the record structure.
465   -- We don't need to call the supplied conversion argument routine as we
466   -- only need a few attributes.
467   --
468   l_rec.vrbl_mtchg_rt_id		:= p_vrbl_mtchg_rt_id;
469   l_rec.object_version_number 	:= p_object_version_number;
470   --
471   -- Having converted the arguments into the ben_vmr_rec
472   -- plsql record structure we must call the corresponding entity
473   -- business process
474   --
475   del(l_rec, p_effective_date, p_datetrack_mode);
476   --
477   -- Set the out arguments
478   --
479   p_object_version_number := l_rec.object_version_number;
480   p_effective_start_date  := l_rec.effective_start_date;
481   p_effective_end_date    := l_rec.effective_end_date;
482   --
483   hr_utility.set_location(' Leaving:'||l_proc, 10);
484 End del;
485 --
486 end ben_vmr_del;