1 Package Body ben_ler_del as
2 /* $Header: belerrhi.pkb 120.2 2006/11/03 10:34:58 vborkar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ler_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_ler_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_ler_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_ler_f
72 where ler_id = p_rec.ler_id
73 and effective_start_date = p_validation_start_date;
74 --
75 delete from ben_ler_f_tl
76 where ler_id = p_rec.ler_id
77 and effective_start_date = p_validation_start_date;
78 --
79 ben_ler_shd.g_api_dml := false; -- Unset the api dml status
80 Else
81 hr_utility.set_location(l_proc, 15);
82 ben_ler_shd.g_api_dml := true; -- Set the api dml status
83 --
84 -- Delete the row(s) where the effective start date is greater than
85 -- or equal to the validation start date.
86 --
87 delete from ben_ler_f
88 where ler_id = p_rec.ler_id
89 and effective_start_date >= p_validation_start_date;
90 --
91 delete from ben_ler_f_tl
92 where ler_id = p_rec.ler_id
93 and effective_start_date >= p_validation_start_date;
94 --
95 ben_ler_shd.g_api_dml := false; -- Unset the api dml status
96 End If;
97 --
98 hr_utility.set_location(' Leaving:'||l_proc, 20);
99 --
100 Exception
101 When Others Then
102 ben_ler_shd.g_api_dml := false; -- Unset the api dml status
103 Raise;
104 End dt_delete_dml;
105 --
106 -- ----------------------------------------------------------------------------
107 -- |------------------------------< delete_dml >------------------------------|
108 -- ----------------------------------------------------------------------------
109 Procedure delete_dml
110 (p_rec in out nocopy ben_ler_shd.g_rec_type,
111 p_effective_date in date,
112 p_datetrack_mode in varchar2,
113 p_validation_start_date in date,
114 p_validation_end_date in date) is
115 --
116 l_proc varchar2(72) := g_package||'delete_dml';
117 --
118 Begin
119 hr_utility.set_location('Entering:'||l_proc, 5);
120 --
121 dt_delete_dml(p_rec => p_rec,
122 p_effective_date => p_effective_date,
123 p_datetrack_mode => p_datetrack_mode,
124 p_validation_start_date => p_validation_start_date,
125 p_validation_end_date => p_validation_end_date);
126 --
127 hr_utility.set_location(' Leaving:'||l_proc, 10);
128 End delete_dml;
129 --
130 -- ----------------------------------------------------------------------------
131 -- |----------------------------< dt_pre_delete >-----------------------------|
132 -- ----------------------------------------------------------------------------
133 -- {Start Of Comments}
134 --
135 -- Description:
136 -- The dt_pre_delete process controls the execution of dml
137 -- for the datetrack modes: DELETE, FUTURE_CHANGE
138 -- and DELETE_NEXT_CHANGE only.
139 --
140 -- Prerequisites:
141 -- This is an internal procedure which is called from the pre_delete
142 -- procedure.
143 --
144 -- In Parameters:
145 -- A Pl/Sql record structre.
146 --
147 -- Post Success:
148 -- Processing continues.
149 --
150 -- Post Failure:
151 -- If an error has occurred, an error message and exception will be raised
152 -- but not handled.
153 --
154 -- Developer Implementation Notes:
155 -- This is an internal procedure which is required by Datetrack. Don't
156 -- remove or modify.
157 --
158 -- Access Status:
159 -- Internal Row Handler Use Only.
160 --
161 -- {End Of Comments}
162 -- ----------------------------------------------------------------------------
163 Procedure dt_pre_delete
164 (p_rec in out nocopy ben_ler_shd.g_rec_type,
165 p_effective_date in date,
166 p_datetrack_mode in varchar2,
167 p_validation_start_date in date,
168 p_validation_end_date in date) is
169 --
170 l_proc varchar2(72) := g_package||'dt_pre_delete';
171 --
172 Begin
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 --
175 If (p_datetrack_mode <> 'ZAP') then
176 --
177 p_rec.effective_start_date := ben_ler_shd.g_old_rec.effective_start_date;
178 --
179 If (p_datetrack_mode = 'DELETE') then
180 p_rec.effective_end_date := p_validation_start_date - 1;
181 Else
182 p_rec.effective_end_date := p_validation_end_date;
183 End If;
184 --
185 -- Update the current effective end date record
186 --
187 ben_ler_shd.upd_effective_end_date
188 (p_effective_date => p_effective_date,
189 p_base_key_value => p_rec.ler_id,
190 p_new_effective_end_date => p_rec.effective_end_date,
191 p_validation_start_date => p_validation_start_date,
192 p_validation_end_date => p_validation_end_date,
193 p_object_version_number => p_rec.object_version_number);
194 Else
195 p_rec.effective_start_date := null;
196 p_rec.effective_end_date := null;
197 End If;
198 hr_utility.set_location(' Leaving:'||l_proc, 10);
199 End dt_pre_delete;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |------------------------------< pre_delete >------------------------------|
203 -- ----------------------------------------------------------------------------
204 -- {Start Of Comments}
205 --
206 -- Description:
207 -- This private procedure contains any processing which is required before
208 -- the delete dml.
209 --
210 -- Prerequisites:
211 -- This is an internal procedure which is called from the del procedure.
212 --
213 -- In Parameters:
214 -- A Pl/Sql record structre.
215 --
216 -- Post Success:
217 -- Processing continues.
218 --
219 -- Post Failure:
220 -- If an error has occurred, an error message and exception will be raised
221 -- but not handled.
222 --
223 -- Developer Implementation Notes:
224 -- Any pre-processing required before the delete dml is issued should be
225 -- coded within this procedure. It is important to note that any 3rd party
226 -- maintenance should be reviewed before placing in this procedure. The call
227 -- to the dt_delete_dml procedure should NOT be removed.
228 --
229 -- Access Status:
230 -- Internal Row Handler Use Only.
231 --
232 -- {End Of Comments}
233 -- ----------------------------------------------------------------------------
234 Procedure pre_delete
235 (p_rec in out nocopy ben_ler_shd.g_rec_type,
236 p_effective_date in date,
237 p_datetrack_mode in varchar2,
238 p_validation_start_date in date,
239 p_validation_end_date in date) is
240 --
241 l_proc varchar2(72) := g_package||'pre_delete';
242 --
243 --
244 --
245 Begin
246 hr_utility.set_location('Entering:'||l_proc, 5);
247 --
248 --
249 --
250 dt_pre_delete
251 (p_rec => p_rec,
252 p_effective_date => p_effective_date,
253 p_datetrack_mode => p_datetrack_mode,
254 p_validation_start_date => p_validation_start_date,
255 p_validation_end_date => p_validation_end_date);
256 --
257 hr_utility.set_location(' Leaving:'||l_proc, 10);
258 End pre_delete;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |-----------------------------< post_delete >------------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- This private procedure contains any processing which is required after the
267 -- delete dml.
268 --
269 -- Prerequisites:
270 -- This is an internal procedure which is called from the del procedure.
271 --
272 -- In Parameters:
273 -- A Pl/Sql record structre.
274 --
275 -- Post Success:
276 -- Processing continues.
277 --
278 -- Post Failure:
279 -- If an error has occurred, an error message and exception will be raised
280 -- but not handled.
281 --
282 -- Developer Implementation Notes:
283 -- Any post-processing required after the delete dml is issued should be
284 -- coded within this procedure. It is important to note that any 3rd party
285 -- maintenance should be reviewed before placing in this procedure.
286 --
287 -- Access Status:
288 -- Internal Row Handler Use Only.
289 --
290 -- {End Of Comments}
291 -- ----------------------------------------------------------------------------
292 Procedure post_delete
293 (p_rec in ben_ler_shd.g_rec_type,
294 p_effective_date in date,
295 p_datetrack_mode in varchar2,
296 p_validation_start_date in date,
297 p_validation_end_date in date) is
298 --
299 l_proc varchar2(72) := g_package||'post_delete';
300 --
301 Begin
302 hr_utility.set_location('Entering:'||l_proc, 5);
303 --
304 --
305 -- Start of API User Hook for post_delete.
306 --
307 begin
308 --
309 ben_ler_rkd.after_delete
310 (
311 p_ler_id =>p_rec.ler_id
312 ,p_datetrack_mode =>p_datetrack_mode
313 ,p_validation_start_date =>p_validation_start_date
314 ,p_validation_end_date =>p_validation_end_date
315 ,p_effective_start_date =>p_rec.effective_start_date
316 ,p_effective_end_date =>p_rec.effective_end_date
317 ,p_effective_start_date_o =>ben_ler_shd.g_old_rec.effective_start_date
318 ,p_effective_end_date_o =>ben_ler_shd.g_old_rec.effective_end_date
319 ,p_name_o =>ben_ler_shd.g_old_rec.name
320 ,p_business_group_id_o =>ben_ler_shd.g_old_rec.business_group_id
321 ,p_typ_cd_o =>ben_ler_shd.g_old_rec.typ_cd
322 ,p_lf_evt_oper_cd_o =>ben_ler_shd.g_old_rec.lf_evt_oper_cd
323 ,p_short_name_o =>ben_ler_shd.g_old_rec.short_name
324 ,p_short_code_o =>ben_ler_shd.g_old_rec.short_code
325 ,p_ptnl_ler_trtmt_cd_o =>ben_ler_shd.g_old_rec.ptnl_ler_trtmt_cd
326 ,p_ck_rltd_per_elig_flag_o =>ben_ler_shd.g_old_rec.ck_rltd_per_elig_flag
327 ,p_ler_eval_rl_o =>ben_ler_shd.g_old_rec.ler_eval_rl
328 ,p_cm_aply_flag_o =>ben_ler_shd.g_old_rec.cm_aply_flag
329 ,p_ovridg_le_flag_o =>ben_ler_shd.g_old_rec.ovridg_le_flag
330 ,p_qualg_evt_flag_o =>ben_ler_shd.g_old_rec.qualg_evt_flag
331 ,p_whn_to_prcs_cd_o =>ben_ler_shd.g_old_rec.whn_to_prcs_cd
332 ,p_desc_txt_o =>ben_ler_shd.g_old_rec.desc_txt
333 ,p_tmlns_eval_cd_o =>ben_ler_shd.g_old_rec.tmlns_eval_cd
334 ,p_tmlns_perd_cd_o =>ben_ler_shd.g_old_rec.tmlns_perd_cd
335 ,p_tmlns_dys_num_o =>ben_ler_shd.g_old_rec.tmlns_dys_num
336 ,p_tmlns_perd_rl_o =>ben_ler_shd.g_old_rec.tmlns_perd_rl
337 ,p_ocrd_dt_det_cd_o =>ben_ler_shd.g_old_rec.ocrd_dt_det_cd
338 ,p_ler_stat_cd_o =>ben_ler_shd.g_old_rec.ler_stat_cd
339 ,p_slctbl_slf_svc_cd_o =>ben_ler_shd.g_old_rec.slctbl_slf_svc_cd
340 ,p_ss_pcp_disp_cd_o =>ben_ler_shd.g_old_rec.ss_pcp_disp_cd
341 ,p_ler_attribute_category_o =>ben_ler_shd.g_old_rec.ler_attribute_category
342 ,p_ler_attribute1_o =>ben_ler_shd.g_old_rec.ler_attribute1
343 ,p_ler_attribute2_o =>ben_ler_shd.g_old_rec.ler_attribute2
344 ,p_ler_attribute3_o =>ben_ler_shd.g_old_rec.ler_attribute3
345 ,p_ler_attribute4_o =>ben_ler_shd.g_old_rec.ler_attribute4
346 ,p_ler_attribute5_o =>ben_ler_shd.g_old_rec.ler_attribute5
347 ,p_ler_attribute6_o =>ben_ler_shd.g_old_rec.ler_attribute6
348 ,p_ler_attribute7_o =>ben_ler_shd.g_old_rec.ler_attribute7
349 ,p_ler_attribute8_o =>ben_ler_shd.g_old_rec.ler_attribute8
350 ,p_ler_attribute9_o =>ben_ler_shd.g_old_rec.ler_attribute9
351 ,p_ler_attribute10_o =>ben_ler_shd.g_old_rec.ler_attribute10
352 ,p_ler_attribute11_o =>ben_ler_shd.g_old_rec.ler_attribute11
353 ,p_ler_attribute12_o =>ben_ler_shd.g_old_rec.ler_attribute12
354 ,p_ler_attribute13_o =>ben_ler_shd.g_old_rec.ler_attribute13
355 ,p_ler_attribute14_o =>ben_ler_shd.g_old_rec.ler_attribute14
356 ,p_ler_attribute15_o =>ben_ler_shd.g_old_rec.ler_attribute15
357 ,p_ler_attribute16_o =>ben_ler_shd.g_old_rec.ler_attribute16
358 ,p_ler_attribute17_o =>ben_ler_shd.g_old_rec.ler_attribute17
359 ,p_ler_attribute18_o =>ben_ler_shd.g_old_rec.ler_attribute18
360 ,p_ler_attribute19_o =>ben_ler_shd.g_old_rec.ler_attribute19
361 ,p_ler_attribute20_o =>ben_ler_shd.g_old_rec.ler_attribute20
362 ,p_ler_attribute21_o =>ben_ler_shd.g_old_rec.ler_attribute21
363 ,p_ler_attribute22_o =>ben_ler_shd.g_old_rec.ler_attribute22
364 ,p_ler_attribute23_o =>ben_ler_shd.g_old_rec.ler_attribute23
365 ,p_ler_attribute24_o =>ben_ler_shd.g_old_rec.ler_attribute24
366 ,p_ler_attribute25_o =>ben_ler_shd.g_old_rec.ler_attribute25
367 ,p_ler_attribute26_o =>ben_ler_shd.g_old_rec.ler_attribute26
368 ,p_ler_attribute27_o =>ben_ler_shd.g_old_rec.ler_attribute27
369 ,p_ler_attribute28_o =>ben_ler_shd.g_old_rec.ler_attribute28
370 ,p_ler_attribute29_o =>ben_ler_shd.g_old_rec.ler_attribute29
371 ,p_ler_attribute30_o =>ben_ler_shd.g_old_rec.ler_attribute30
372 ,p_object_version_number_o =>ben_ler_shd.g_old_rec.object_version_number
373 );
374 --
375 exception
376 --
377 when hr_api.cannot_find_prog_unit then
378 --
379 hr_api.cannot_find_prog_unit_error
380 (p_module_name => 'ben_ler_f'
381 ,p_hook_type => 'AD');
382 --
383 end;
384 --
385 -- End of API User Hook for post_delete.
386 --
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 10);
389 End post_delete;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------------< del >----------------------------------|
393 -- ----------------------------------------------------------------------------
394 Procedure del
395 (
396 p_rec in out nocopy ben_ler_shd.g_rec_type,
397 p_effective_date in date,
398 p_datetrack_mode in varchar2
399 ) is
400 --
401 l_proc varchar2(72) := g_package||'del';
402 l_validation_start_date date;
403 l_validation_end_date date;
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Ensure that the DateTrack delete mode is valid
409 --
410 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
411 --
412 -- We must lock the row which we need to delete.
413 --
414 ben_ler_shd.lck
415 (p_effective_date => p_effective_date,
416 p_datetrack_mode => p_datetrack_mode,
417 p_ler_id => p_rec.ler_id,
418 p_object_version_number => p_rec.object_version_number,
419 p_validation_start_date => l_validation_start_date,
420 p_validation_end_date => l_validation_end_date);
421 --
422 -- Call the supporting delete validate operation
423 --
424 ben_ler_bus.delete_validate
425 (p_rec => p_rec,
426 p_effective_date => p_effective_date,
427 p_datetrack_mode => p_datetrack_mode,
428 p_validation_start_date => l_validation_start_date,
429 p_validation_end_date => l_validation_end_date);
430 --
431 -- Call the supporting pre-delete operation
432 --
433 pre_delete
434 (p_rec => p_rec,
435 p_effective_date => p_effective_date,
436 p_datetrack_mode => p_datetrack_mode,
437 p_validation_start_date => l_validation_start_date,
438 p_validation_end_date => l_validation_end_date);
439 --
440 -- Delete the row.
441 --
442 delete_dml
443 (p_rec => p_rec,
444 p_effective_date => p_effective_date,
445 p_datetrack_mode => p_datetrack_mode,
446 p_validation_start_date => l_validation_start_date,
447 p_validation_end_date => l_validation_end_date);
448 --
449 -- Call the supporting post-delete operation
450 --
451 post_delete
452 (p_rec => p_rec,
453 p_effective_date => p_effective_date,
454 p_datetrack_mode => p_datetrack_mode,
455 p_validation_start_date => l_validation_start_date,
456 p_validation_end_date => l_validation_end_date);
457 End del;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |---------------------------------< del >----------------------------------|
461 -- ----------------------------------------------------------------------------
462 Procedure del
463 (
464 p_ler_id in number,
465 p_effective_start_date out nocopy date,
466 p_effective_end_date out nocopy date,
467 p_object_version_number in out nocopy number,
468 p_effective_date in date,
469 p_datetrack_mode in varchar2
470 ) is
471 --
472 l_rec ben_ler_shd.g_rec_type;
473 l_proc varchar2(72) := g_package||'del';
474 --
475 Begin
476 hr_utility.set_location('Entering:'||l_proc, 5);
477 --
478 -- As the delete procedure accepts a plsql record structure we do need to
479 -- convert the arguments into the record structure.
480 -- We don't need to call the supplied conversion argument routine as we
481 -- only need a few attributes.
482 --
483 l_rec.ler_id := p_ler_id;
484 l_rec.object_version_number := p_object_version_number;
485 --
486 -- Having converted the arguments into the ben_ler_rec
487 -- plsql record structure we must call the corresponding entity
488 -- business process
489 --
490 del(l_rec, p_effective_date, p_datetrack_mode);
491 --
492 -- Set the out arguments
493 --
494 p_object_version_number := l_rec.object_version_number;
495 p_effective_start_date := l_rec.effective_start_date;
496 p_effective_end_date := l_rec.effective_end_date;
497 --
498 hr_utility.set_location(' Leaving:'||l_proc, 10);
499 End del;
500 --
501 end ben_ler_del;
[Home] [Help]