[Home] [Help]
PACKAGE BODY: APPS.PAY_ELE_DEL
Source
1 Package Body pay_ele_del as
2 /* $Header: pyelerhi.pkb 120.1 2005/05/30 05:19:19 rajeesha noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ele_del.'; -- Global package name
9 g_counter number;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------------< dt_delete_dml >-----------------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure controls the actual dml delete logic for the datetrack
18 -- delete modes: ZAP, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE. The
19 -- execution is as follows:
20 -- 1) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 2) If the delete mode is DELETE_NEXT_CHANGE then delete where the
23 -- effective start date is equal to the validation start date.
24 -- 3) If the delete mode is not DELETE_NEXT_CHANGE then delete
25 -- all rows for the entity where the effective start date is greater
26 -- than or equal to the validation start date.
27 -- 4) To raise any errors.
28 --
29 -- Prerequisites:
30 -- This is an internal private procedure which must be called from the
31 -- delete_dml procedure.
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structure.
35 --
36 -- Post Success:
37 -- The specified row will be delete from the schema.
38 --
39 -- Post Failure:
40 -- On the delete dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- This is an internal private procedure which must be called from the
47 -- delete_dml procedure.
48 --
49 -- Access Status:
50 -- Internal Row Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure dt_delete_dml
55 (p_rec in out nocopy pay_ele_shd.g_rec_type
56 ,p_effective_date in date
57 ,p_datetrack_mode in varchar2
58 ,p_validation_start_date in date
59 ,p_validation_end_date in date
60 ) is
61 --
62 l_proc varchar2(72) := g_package||'dt_delete_dml';
63 --
64 Begin
65 hr_utility.set_location('Entering:'||l_proc, 5);
66 If (p_datetrack_mode = hr_api.g_delete_next_change) then
67 pay_ele_shd.g_api_dml := true; -- Set the api dml status
68 --
69 -- Delete the where the effective start date is equal
70 -- to the validation end date.
71 --
72 delete from pay_element_entries_f
73 where element_entry_id = p_rec.element_entry_id
74 and effective_start_date = p_validation_start_date;
75 --
76 pay_ele_shd.g_api_dml := false; -- Unset the api dml status
77 Else
78 pay_ele_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 pay_element_entries_f
84 where element_entry_id = p_rec.element_entry_id
85 and effective_start_date >= p_validation_start_date;
86 --
87 pay_ele_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 pay_ele_shd.g_api_dml := false; -- Unset the api dml status
95 Raise;
96 --
97 End dt_delete_dml;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |------------------------------< delete_dml >------------------------------|
101 -- ----------------------------------------------------------------------------
102 Procedure delete_dml
103 (p_rec in out nocopy pay_ele_shd.g_rec_type
104 ,p_effective_date in date
105 ,p_datetrack_mode in varchar2
106 ,p_validation_start_date in date
107 ,p_validation_end_date in date
108 ) is
109 --
110 l_proc varchar2(72) := g_package||'delete_dml';
111 --
112 Begin
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 pay_ele_del.dt_delete_dml
116 (p_rec => p_rec
117 ,p_effective_date => p_effective_date
118 ,p_datetrack_mode => p_datetrack_mode
119 ,p_validation_start_date => p_validation_start_date
120 ,p_validation_end_date => p_validation_end_date
121 );
122 --
123 hr_utility.set_location(' Leaving:'||l_proc, 10);
124 End delete_dml;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |----------------------------< dt_pre_delete >-----------------------------|
128 -- ----------------------------------------------------------------------------
129 -- {Start Of Comments}
130 --
131 -- Description:
132 -- The dt_pre_delete process controls the execution of dml
133 -- for the datetrack modes: DELETE, FUTURE_CHANGE
134 -- and DELETE_NEXT_CHANGE only.
135 --
136 -- Prerequisites:
137 -- This is an internal procedure which is called from the pre_delete
138 -- procedure.
139 --
140 -- In Parameters:
141 -- A Pl/Sql record structure.
142 --
143 -- Post Success:
144 -- Processing continues.
145 --
146 -- Post Failure:
147 -- If an error has occurred, an error message and exception will be raised
148 -- but not handled.
149 --
150 -- Developer Implementation Notes:
151 -- This is an internal procedure which is required by Datetrack. Don't
152 -- remove or modify.
153 --
154 -- Access Status:
155 -- Internal Row Handler Use Only.
156 --
157 -- {End Of Comments}
158 -- ----------------------------------------------------------------------------
159 Procedure dt_pre_delete
160 (p_rec in out nocopy pay_ele_shd.g_rec_type
161 ,p_effective_date in date
162 ,p_datetrack_mode in varchar2
163 ,p_validation_start_date in date
164 ,p_validation_end_date in date
165 ) is
166 --
167 l_proc varchar2(72) := g_package||'dt_pre_delete';
168 --
169 Begin
170 hr_utility.set_location('Entering:'||l_proc, 5);
171 --
172 If (p_datetrack_mode <> hr_api.g_zap) then
173 --
174 p_rec.effective_start_date
175 := pay_ele_shd.g_old_rec.effective_start_date;
176 --
177 If (p_datetrack_mode = hr_api.g_delete) then
178 p_rec.effective_end_date := p_validation_start_date - 1;
179 Else
180 p_rec.effective_end_date := p_validation_end_date;
181 End If;
182 --
183 -- Update the current effective end date record
184 --
185 pay_ele_shd.upd_effective_end_date
186 (p_effective_date => p_effective_date
187 ,p_base_key_value => p_rec.element_entry_id
188 ,p_new_effective_end_date => p_rec.effective_end_date
189 ,p_validation_start_date => p_validation_start_date
190 ,p_validation_end_date => p_validation_end_date
191 ,p_object_version_number => p_rec.object_version_number
192 );
193 Else
194 p_rec.effective_start_date := null;
195 p_rec.effective_end_date := null;
196 End If;
197 hr_utility.set_location(' Leaving:'||l_proc, 10);
198 End dt_pre_delete;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------------------------------< pre_delete >------------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This private procedure contains any processing which is required before
207 -- the delete dml.
208 --
209 -- Prerequisites:
210 -- This is an internal procedure which is called from the del procedure.
211 --
212 -- In Parameters:
213 -- A Pl/Sql record structure.
214 --
215 -- Post Success:
216 -- Processing continues.
217 --
218 -- Post Failure:
219 -- If an error has occurred, an error message and exception will be raised
220 -- but not handled.
221 --
222 -- Developer Implementation Notes:
223 -- Any pre-processing required before the delete dml is issued should be
224 -- coded within this procedure. It is important to note that any 3rd party
225 -- maintenance should be reviewed before placing in this procedure. The call
226 -- to the dt_delete_dml procedure should NOT be removed.
227 --
228 -- Access Status:
229 -- Internal Row Handler Use Only.
230 --
231 -- {End Of Comments}
232 -- ----------------------------------------------------------------------------
233 Procedure pre_delete
234 (p_rec in out nocopy pay_ele_shd.g_rec_type
235 ,p_effective_date in date
236 ,p_datetrack_mode in varchar2
237 ,p_validation_start_date in date
238 ,p_validation_end_date in date
239 ) is
240 --
241 l_proc varchar2(72) := g_package||'pre_delete';
242 --
243 -- Cursor C_Sel1 select comments to be deleted
244 --
245 Cursor C_Sel1 is
246 select t1.comment_id
247 from pay_element_entries_f t1
248 where t1.comment_id is not null
249 and t1.element_entry_id = p_rec.element_entry_id
250 and t1.effective_start_date <= p_validation_end_date
251 and t1.effective_end_date >= p_validation_start_date
252 and not exists
253 (select 1
254 from pay_element_entries_f t2
255 where t2.comment_id = t1.comment_id
256 and t2.element_entry_id = t1.element_entry_id
257 and (t2.effective_start_date > p_validation_end_date
258 or t2.effective_end_date < p_validation_start_date));
259 --
260 --
261 Begin
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 --
265 -- Delete any possible comments
266 --
267 For Comm_Del In C_Sel1 Loop
268 hr_comm_api.del(p_comment_id => Comm_Del.comment_id);
269 End Loop;
270 --
271 --
272 pay_ele_del.dt_pre_delete
273 (p_rec => p_rec
274 ,p_effective_date => p_effective_date
275 ,p_datetrack_mode => p_datetrack_mode
276 ,p_validation_start_date => p_validation_start_date
277 ,p_validation_end_date => p_validation_end_date
278 );
279 --
280 hr_utility.set_location(' Leaving:'||l_proc, 10);
281 End pre_delete;
282 --
283 -- ----------------------------------------------------------------------------
284 -- |----------------------------< post_delete >-------------------------------|
285 -- ----------------------------------------------------------------------------
286 -- {Start Of Comments}
287 --
288 -- Description:
289 -- This private procedure contains any processing which is required after the
290 -- delete dml.
291 --
292 -- Prerequisites:
293 -- This is an internal procedure which is called from the del procedure.
294 --
295 -- In Parameters:
296 -- A Pl/Sql record structure.
297 --
298 -- Post Success:
299 -- Processing continues.
300 --
301 -- Post Failure:
302 -- If an error has occurred, an error message and exception will be raised
303 -- but not handled.
304 --
305 -- Developer Implementation Notes:
306 -- Any post-processing required after the delete dml is issued should be
307 -- coded within this procedure. It is important to note that any 3rd party
308 -- maintenance should be reviewed before placing in this procedure.
309 --
310 -- Access Status:
311 -- Internal Row Handler Use Only.
312 --
313 -- {End Of Comments}
314 -- ----------------------------------------------------------------------------
315 Procedure post_delete
316 (p_rec in pay_ele_shd.g_rec_type
317 ,p_effective_date in date
318 ,p_datetrack_mode in varchar2
319 ,p_validation_start_date in date
320 ,p_validation_end_date in date
321 ) is
322 --
323 l_proc varchar2(72) := g_package||'post_delete';
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 begin
328 --
329 pay_ele_rkd.after_delete
330 (p_effective_date
331 => p_effective_date
332 ,p_datetrack_mode
333 => p_datetrack_mode
334 ,p_validation_start_date
335 => p_validation_start_date
336 ,p_validation_end_date
337 => p_validation_end_date
338 ,p_element_entry_id
339 => p_rec.element_entry_id
340 ,p_effective_start_date
341 => p_rec.effective_start_date
342 ,p_effective_end_date
343 => p_rec.effective_end_date
344 ,p_effective_start_date_o
345 => pay_ele_shd.g_old_rec.effective_start_date
346 ,p_effective_end_date_o
347 => pay_ele_shd.g_old_rec.effective_end_date
348 ,p_cost_allocation_keyflex_id_o
349 => pay_ele_shd.g_old_rec.cost_allocation_keyflex_id
353 => pay_ele_shd.g_old_rec.updating_action_id
350 ,p_assignment_id_o
351 => pay_ele_shd.g_old_rec.assignment_id
352 ,p_updating_action_id_o
354 ,p_updating_action_type_o
355 => pay_ele_shd.g_old_rec.updating_action_type
356 ,p_element_link_id_o
357 => pay_ele_shd.g_old_rec.element_link_id
358 ,p_original_entry_id_o
359 => pay_ele_shd.g_old_rec.original_entry_id
360 ,p_creator_type_o
361 => pay_ele_shd.g_old_rec.creator_type
362 ,p_entry_type_o
363 => pay_ele_shd.g_old_rec.entry_type
364 ,p_comment_id_o
365 => pay_ele_shd.g_old_rec.comment_id
366 ,p_comments_o
367 => pay_ele_shd.g_old_rec.comments
368 ,p_creator_id_o
369 => pay_ele_shd.g_old_rec.creator_id
370 ,p_reason_o
371 => pay_ele_shd.g_old_rec.reason
372 ,p_target_entry_id_o
373 => pay_ele_shd.g_old_rec.target_entry_id
374 ,p_attribute_category_o
375 => pay_ele_shd.g_old_rec.attribute_category
376 ,p_attribute1_o
377 => pay_ele_shd.g_old_rec.attribute1
378 ,p_attribute2_o
379 => pay_ele_shd.g_old_rec.attribute2
380 ,p_attribute3_o
381 => pay_ele_shd.g_old_rec.attribute3
382 ,p_attribute4_o
383 => pay_ele_shd.g_old_rec.attribute4
384 ,p_attribute5_o
385 => pay_ele_shd.g_old_rec.attribute5
386 ,p_attribute6_o
387 => pay_ele_shd.g_old_rec.attribute6
388 ,p_attribute7_o
389 => pay_ele_shd.g_old_rec.attribute7
390 ,p_attribute8_o
391 => pay_ele_shd.g_old_rec.attribute8
392 ,p_attribute9_o
393 => pay_ele_shd.g_old_rec.attribute9
394 ,p_attribute10_o
395 => pay_ele_shd.g_old_rec.attribute10
396 ,p_attribute11_o
397 => pay_ele_shd.g_old_rec.attribute11
398 ,p_attribute12_o
399 => pay_ele_shd.g_old_rec.attribute12
400 ,p_attribute13_o
401 => pay_ele_shd.g_old_rec.attribute13
402 ,p_attribute14_o
403 => pay_ele_shd.g_old_rec.attribute14
404 ,p_attribute15_o
405 => pay_ele_shd.g_old_rec.attribute15
406 ,p_attribute16_o
407 => pay_ele_shd.g_old_rec.attribute16
408 ,p_attribute17_o
409 => pay_ele_shd.g_old_rec.attribute17
410 ,p_attribute18_o
411 => pay_ele_shd.g_old_rec.attribute18
412 ,p_attribute19_o
413 => pay_ele_shd.g_old_rec.attribute19
414 ,p_attribute20_o
415 => pay_ele_shd.g_old_rec.attribute20
416 -- --
417 ,--Altered next 30 lines, 20-FEB-03 changed p_rec -> pay_ele_shd.g_old_rec
418 --as looked like a cut-n-paste accident
419 p_entry_information_category_o => pay_ele_shd.g_old_rec.entry_information_category,
420 p_entry_information1_o => pay_ele_shd.g_old_rec.entry_information1,
421 p_entry_information2_o => pay_ele_shd.g_old_rec.entry_information2,
422 p_entry_information3_o => pay_ele_shd.g_old_rec.entry_information3,
423 p_entry_information4_o => pay_ele_shd.g_old_rec.entry_information4,
424 p_entry_information5_o => pay_ele_shd.g_old_rec.entry_information5,
425 p_entry_information6_o => pay_ele_shd.g_old_rec.entry_information6,
426 p_entry_information7_o => pay_ele_shd.g_old_rec.entry_information7,
427 p_entry_information8_o => pay_ele_shd.g_old_rec.entry_information8,
428 p_entry_information9_o => pay_ele_shd.g_old_rec.entry_information9,
429 p_entry_information10_o => pay_ele_shd.g_old_rec.entry_information10,
430 p_entry_information11_o => pay_ele_shd.g_old_rec.entry_information11,
431 p_entry_information12_o => pay_ele_shd.g_old_rec.entry_information12,
432 p_entry_information13_o => pay_ele_shd.g_old_rec.entry_information13,
433 p_entry_information14_o => pay_ele_shd.g_old_rec.entry_information14,
434 p_entry_information15_o => pay_ele_shd.g_old_rec.entry_information15,
435 p_entry_information16_o => pay_ele_shd.g_old_rec.entry_information16,
436 p_entry_information17_o => pay_ele_shd.g_old_rec.entry_information17,
437 p_entry_information18_o => pay_ele_shd.g_old_rec.entry_information18,
438 p_entry_information19_o => pay_ele_shd.g_old_rec.entry_information19,
439 p_entry_information20_o => pay_ele_shd.g_old_rec.entry_information20,
440 p_entry_information21_o => pay_ele_shd.g_old_rec.entry_information21,
441 p_entry_information22_o => pay_ele_shd.g_old_rec.entry_information22,
442 p_entry_information23_o => pay_ele_shd.g_old_rec.entry_information23,
443 p_entry_information24_o => pay_ele_shd.g_old_rec.entry_information24,
444 p_entry_information25_o => pay_ele_shd.g_old_rec.entry_information25,
445 p_entry_information26_o => pay_ele_shd.g_old_rec.entry_information26,
446 p_entry_information27_o => pay_ele_shd.g_old_rec.entry_information27,
447 p_entry_information28_o => pay_ele_shd.g_old_rec.entry_information28,
448 p_entry_information29_o => pay_ele_shd.g_old_rec.entry_information29,
449 p_entry_information30_o => pay_ele_shd.g_old_rec.entry_information30
450 ,p_subpriority_o
451 => pay_ele_shd.g_old_rec.subpriority
452 ,p_personal_payment_method_id_o
453 => pay_ele_shd.g_old_rec.personal_payment_method_id
454 ,p_date_earned_o
455 => pay_ele_shd.g_old_rec.date_earned
456 ,p_object_version_number_o
457 => pay_ele_shd.g_old_rec.object_version_number
458 ,p_source_id_o
459 => pay_ele_shd.g_old_rec.source_id
460 ,p_balance_adj_cost_flag_o
464 => pay_ele_shd.g_old_rec.all_entry_values_null
461 => pay_ele_shd.g_old_rec.balance_adj_cost_flag
462 ,p_element_type_id_o => pay_ele_shd.g_old_rec.element_type_id
463 ,p_all_entry_values_null_o
465 );
466 --
467 exception
468 --
469 when hr_api.cannot_find_prog_unit then
470 --
471 hr_api.cannot_find_prog_unit_error
472 (p_module_name => 'PAY_ELEMENT_ENTRIES_F'
473 ,p_hook_type => 'AD');
474 --
475 end;
476 --
477 -- 11-NOV-03
478 -- Hard calls to DYT_PKG removed, perfomed in pyentapi.pkb
479
480 hr_utility.set_location('Leaving:'||l_proc, 900);
481 --
482 End post_delete;
483 --
484 -- ----------------------------------------------------------------------------
485 -- |---------------------------------< del >----------------------------------|
486 -- ----------------------------------------------------------------------------
487 Procedure del
488 (p_effective_date in date
489 ,p_datetrack_mode in varchar2
490 ,p_rec in out nocopy pay_ele_shd.g_rec_type
491 ) is
492 --
493 l_proc varchar2(72) := g_package||'del';
494 l_validation_start_date date;
495 l_validation_end_date date;
496 --
497 Begin
498 hr_utility.set_location('Entering:'||l_proc, 5);
499 --
500 -- Ensure that the DateTrack delete mode is valid
501 --
502 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
503 --
504 -- We must lock the row which we need to delete.
505 --
506 pay_ele_shd.lck
507 (p_effective_date => p_effective_date
508 ,p_datetrack_mode => p_datetrack_mode
509 ,p_element_entry_id => p_rec.element_entry_id
510 ,p_object_version_number => p_rec.object_version_number
511 ,p_validation_start_date => l_validation_start_date
512 ,p_validation_end_date => l_validation_end_date
513 );
514 --
515 -- Call the supporting delete validate operation
516 --
517 pay_ele_bus.delete_validate
518 (p_rec => p_rec
519 ,p_effective_date => p_effective_date
520 ,p_datetrack_mode => p_datetrack_mode
521 ,p_validation_start_date => l_validation_start_date
522 ,p_validation_end_date => l_validation_end_date
523 );
524 --
525 -- Call the supporting pre-delete operation
526 --
527 pay_ele_del.pre_delete
528 (p_rec => p_rec
529 ,p_effective_date => p_effective_date
530 ,p_datetrack_mode => p_datetrack_mode
531 ,p_validation_start_date => l_validation_start_date
532 ,p_validation_end_date => l_validation_end_date
533 );
534 --
535 -- Delete the row.
536 --
537 pay_ele_del.delete_dml
538 (p_rec => p_rec
539 ,p_effective_date => p_effective_date
540 ,p_datetrack_mode => p_datetrack_mode
541 ,p_validation_start_date => l_validation_start_date
542 ,p_validation_end_date => l_validation_end_date
543 );
544 -- Call the supporting post-delete operation
545 --
546 pay_ele_del.post_delete
547 (p_rec => p_rec
548 ,p_effective_date => p_effective_date
549 ,p_datetrack_mode => p_datetrack_mode
550 ,p_validation_start_date => l_validation_start_date
551 ,p_validation_end_date => l_validation_end_date
552 );
553 --
554 hr_utility.set_location(' Leaving:'||l_proc, 5);
555 End del;
556 --
557 -- ----------------------------------------------------------------------------
558 -- |--------------------------------< del >-----------------------------------|
559 -- ----------------------------------------------------------------------------
560 Procedure del
561 (p_effective_date in date
562 ,p_datetrack_mode in varchar2
563 ,p_element_entry_id in number
564 ,p_object_version_number in out nocopy number
565 ,p_effective_start_date out nocopy date
566 ,p_effective_end_date out nocopy date
567 ) is
568 --
569 l_rec pay_ele_shd.g_rec_type;
570 l_proc varchar2(72) := g_package||'del';
571 --
572 Begin
573 hr_utility.set_location('Entering:'||l_proc, 5);
574 --
575 -- As the delete procedure accepts a plsql record structure we do need to
576 -- convert the arguments into the record structure.
577 -- We don't need to call the supplied conversion argument routine as we
578 -- only need a few attributes.
579 --
580 l_rec.element_entry_id := p_element_entry_id;
581 l_rec.object_version_number := p_object_version_number;
582 --
583 -- Having converted the arguments into the pay_ele_rec
584 -- plsql record structure we must call the corresponding entity
585 -- business process
586 --
587 pay_ele_del.del
588 (p_effective_date
589 ,p_datetrack_mode
590 ,l_rec
591 );
592 --
593 -- Set the out arguments
594 --
598 --
595 p_object_version_number := l_rec.object_version_number;
596 p_effective_start_date := l_rec.effective_start_date;
597 p_effective_end_date := l_rec.effective_end_date;
599 hr_utility.set_location(' Leaving:'||l_proc, 10);
600 End del;
601 --
602 end pay_ele_del;