[Home] [Help]
PACKAGE BODY: APPS.PAY_PPM_DEL
Source
1 Package Body pay_ppm_del as
2 /* $Header: pyppmrhi.pkb 120.3.12010000.2 2008/08/06 08:14:13 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ppm_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 -- Pre Conditions:
29 -- This is an internal private procedure which must be called from the
30 -- delete_dml procedure.
31 --
32 -- In Arguments:
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 Table Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure dt_delete_dml
54 (p_rec in out nocopy pay_ppm_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 pay_ppm_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 pay_personal_payment_methods_f
72 where personal_payment_method_id = p_rec.personal_payment_method_id
73 and effective_start_date = p_validation_start_date;
74 --
75 pay_ppm_shd.g_api_dml := false; -- Unset the api dml status
76 Else
77 hr_utility.set_location(l_proc, 15);
78 pay_ppm_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_personal_payment_methods_f
84 where personal_payment_method_id = p_rec.personal_payment_method_id
85 and effective_start_date >= p_validation_start_date;
86 --
87 pay_ppm_shd.g_api_dml := false; -- Unset the api dml status
88 End If;
89 --
90 -- If we are doing a 'ZAP' then we must set the effective start and end
91 -- dates to null
92 --
93 hr_utility.set_location(' Leaving:'||l_proc, 20);
94 --
95 Exception
96 When Others Then
97 pay_ppm_shd.g_api_dml := false; -- Unset the api dml status
98 Raise;
99 End dt_delete_dml;
100 --
101 -- ----------------------------------------------------------------------------
102 -- |------------------------------< delete_dml >------------------------------|
103 -- ----------------------------------------------------------------------------
104 Procedure delete_dml
105 (p_rec in out nocopy pay_ppm_shd.g_rec_type,
106 p_effective_date in date,
107 p_datetrack_mode in varchar2,
108 p_validation_start_date in date,
109 p_validation_end_date in date) is
110 --
111 l_proc varchar2(72) := g_package||'delete_dml';
112 --
113 Begin
114 hr_utility.set_location('Entering:'||l_proc, 5);
115 --
116 dt_delete_dml(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 hr_utility.set_location(' Leaving:'||l_proc, 10);
123 End delete_dml;
124 --
125 -- ----------------------------------------------------------------------------
126 -- |----------------------------< dt_pre_delete >-----------------------------|
127 -- ----------------------------------------------------------------------------
128 -- {Start Of Comments}
129 --
130 -- Description:
131 -- The dt_pre_delete process controls the execution of dml
132 -- for the datetrack modes: DELETE, FUTURE_CHANGE
133 -- and DELETE_NEXT_CHANGE only.
134 --
135 -- Pre Conditions:
136 -- This is an internal procedure which is called from the pre_delete
137 -- procedure.
138 --
139 -- In Arguments:
140 -- A Pl/Sql record structre.
141 --
142 -- Post Success:
143 -- Processing continues.
144 --
145 -- Post Failure:
146 -- If an error has occurred, an error message and exception will be raised
147 -- but not handled.
148 --
149 -- Developer Implementation Notes:
150 -- This is an internal procedure which is required by Datetrack. Don't
151 -- remove or modify.
152 --
153 -- Access Status:
154 -- Internal Table Handler Use Only.
155 --
156 -- {End Of Comments}
157 -- ----------------------------------------------------------------------------
158 Procedure dt_pre_delete
159 (p_rec in out nocopy pay_ppm_shd.g_rec_type,
160 p_effective_date in date,
161 p_datetrack_mode in varchar2,
162 p_validation_start_date in date,
163 p_validation_end_date in date) is
164 --
165 l_proc varchar2(72) := g_package||'dt_pre_delete';
166 --
167 Begin
168 hr_utility.set_location('Entering:'||l_proc, 5);
169 --
170 If (p_datetrack_mode <> 'ZAP') then
171 --
172 p_rec.effective_start_date :=
173 pay_ppm_shd.g_old_rec.effective_start_date;
174 --
175 If (p_datetrack_mode = 'DELETE') then
176 p_rec.effective_end_date := p_validation_start_date - 1;
177 Else
178 p_rec.effective_end_date := p_validation_end_date;
179 End If;
180 --
181 -- Update the current effective end date record
182 --
183 pay_ppm_shd.upd_effective_end_date
184 (p_effective_date => p_effective_date,
185 p_base_key_value => p_rec.personal_payment_method_id,
186 p_new_effective_end_date => p_rec.effective_end_date,
187 p_validation_start_date => p_validation_start_date,
188 p_validation_end_date => p_validation_end_date,
189 p_object_version_number => p_rec.object_version_number);
190 Else
191 p_rec.effective_start_date := null;
192 p_rec.effective_end_date := null;
193 End If;
194 hr_utility.set_location(' Leaving:'||l_proc, 10);
195 End dt_pre_delete;
196 --
197 -- ----------------------------------------------------------------------------
198 -- |------------------------------< pre_delete >------------------------------|
199 -- ----------------------------------------------------------------------------
200 -- {Start Of Comments}
201 --
202 -- Description:
203 -- This private procedure contains any processing which is required before
204 -- the delete dml.
205 --
206 -- Pre Conditions:
207 -- This is an internal procedure which is called from the del procedure.
208 --
209 -- In Arguments:
210 -- A Pl/Sql record structre.
211 --
212 -- Post Success:
213 -- Processing continues.
214 --
215 -- Post Failure:
216 -- If an error has occurred, an error message and exception will be raised
217 -- but not handled.
218 --
219 -- Developer Implementation Notes:
220 -- Any pre-processing required before the delete dml is issued should be
221 -- coded within this procedure. It is important to note that any 3rd party
222 -- maintenance should be reviewed before placing in this procedure. The call
223 -- to the dt_delete_dml procedure should NOT be removed.
224 --
225 -- Access Status:
226 -- Internal Table Handler Use Only.
227 --
228 -- {End Of Comments}
229 -- ----------------------------------------------------------------------------
230 Procedure pre_delete
231 (p_rec in out nocopy pay_ppm_shd.g_rec_type,
232 p_effective_date in date,
233 p_datetrack_mode in varchar2,
234 p_validation_start_date in date,
235 p_validation_end_date in date) is
236 --
237 l_proc varchar2(72) := g_package||'pre_delete';
238 --
239 -- Cursor C_Sel1 select comments to be deleted
240 --
241 Cursor C_Sel1 is
242 select t1.comment_id
243 from pay_personal_payment_methods_f t1
244 where t1.comment_id is not null
245 and t1.personal_payment_method_id = p_rec.personal_payment_method_id
246 and t1.effective_start_date <= p_validation_end_date
247 and t1.effective_end_date >= p_validation_start_date
248 and not exists
249 (select 1
250 from pay_personal_payment_methods_f t2
251 where t2.comment_id = t1.comment_id
252 and t2.personal_payment_method_id = t1.personal_payment_method_id
253
254 and (t2.effective_start_date > p_validation_end_date
255 or t2.effective_end_date < p_validation_start_date));
256 --
257 --
258 Begin
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 --
262 -- Delete any possible comments
263 --
264 For Comm_Del In C_Sel1 Loop
265 hr_comm_api.del(p_comment_id => Comm_Del.comment_id);
266 End Loop;
267 --
268 dt_pre_delete
269 (p_rec => p_rec,
270 p_effective_date => p_effective_date,
271 p_datetrack_mode => p_datetrack_mode,
272 p_validation_start_date => p_validation_start_date,
273 p_validation_end_date => p_validation_end_date);
274 --
275 hr_utility.set_location(' Leaving:'||l_proc, 10);
276 End pre_delete;
277 --
278 -- ----------------------------------------------------------------------------
279 -- |-----------------------------< post_delete >------------------------------|
280 -- ----------------------------------------------------------------------------
281 -- {Start Of Comments}
282 --
283 -- Description:
284 -- This private procedure contains any processing which is required after the
285 -- delete dml.
286 --
287 -- Pre Conditions:
288 -- This is an internal procedure which is called from the del procedure.
289 --
290 -- In Arguments:
291 -- A Pl/Sql record structre.
292 --
293 -- Post Success:
294 -- Processing continues.
295 --
296 -- Post Failure:
297 -- If an error has occurred, an error message and exception will be raised
298 -- but not handled.
299 --
300 -- Developer Implementation Notes:
301 -- Any post-processing required after the delete dml is issued should be
302 -- coded within this procedure. It is important to note that any 3rd party
303 -- maintenance should be reviewed before placing in this procedure.
304 --
305 -- Access Status:
306 -- Internal Table Handler Use Only.
307 --
308 -- {End Of Comments}
309 -- ----------------------------------------------------------------------------
310 Procedure post_delete
311 (p_rec in pay_ppm_shd.g_rec_type,
312 p_effective_date in date,
313 p_datetrack_mode in varchar2,
314 p_validation_start_date in date,
315 p_validation_end_date in date) is
316 --
317 l_proc varchar2(72) := g_package||'post_delete';
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 -- Start of API User Hook for post_delete.
323 begin
324 pay_ppm_rkd.after_delete
325 (p_effective_date => p_effective_date
326 ,p_datetrack_mode => p_datetrack_mode
327 ,p_validation_start_date => p_validation_start_date
328 ,p_validation_end_date => p_validation_end_date
329 ,p_personal_payment_method_id => p_rec.personal_payment_method_id
330 ,p_effective_start_date => p_rec.effective_start_date
331 ,p_effective_end_date => p_rec.effective_end_date
332 ,p_object_version_number => p_rec.object_version_number
333 ,p_effective_start_date_o
334 => pay_ppm_shd.g_old_rec.effective_start_date
335 ,p_effective_end_date_o
336 => pay_ppm_shd.g_old_rec.effective_end_date
337 ,p_business_group_id_o
338 => pay_ppm_shd.g_old_rec.business_group_id
339 ,p_external_account_id_o
340 => pay_ppm_shd.g_old_rec.external_account_id
341 ,p_assignment_id_o
342 => pay_ppm_shd.g_old_rec.assignment_id
343 ,p_org_payment_method_id_o
344 => pay_ppm_shd.g_old_rec.org_payment_method_id
345 ,p_amount_o
346 => pay_ppm_shd.g_old_rec.amount
347 ,p_comment_id_o
348 => pay_ppm_shd.g_old_rec.comment_id
349 ,p_percentage_o
350 => pay_ppm_shd.g_old_rec.percentage
351 ,p_priority_o
352 => pay_ppm_shd.g_old_rec.priority
353 ,p_attribute_category_o
354 => pay_ppm_shd.g_old_rec.attribute_category
355 ,p_attribute1_o
356 => pay_ppm_shd.g_old_rec.attribute1
357 ,p_attribute2_o
358 => pay_ppm_shd.g_old_rec.attribute2
359 ,p_attribute3_o
360 => pay_ppm_shd.g_old_rec.attribute3
361 ,p_attribute4_o
362 => pay_ppm_shd.g_old_rec.attribute4
363 ,p_attribute5_o
364 => pay_ppm_shd.g_old_rec.attribute5
365 ,p_attribute6_o
366 => pay_ppm_shd.g_old_rec.attribute6
367 ,p_attribute7_o
368 => pay_ppm_shd.g_old_rec.attribute7
369 ,p_attribute8_o
370 => pay_ppm_shd.g_old_rec.attribute8
371 ,p_attribute9_o
372 => pay_ppm_shd.g_old_rec.attribute9
373 ,p_attribute10_o
374 => pay_ppm_shd.g_old_rec.attribute10
375 ,p_attribute11_o
376 => pay_ppm_shd.g_old_rec.attribute11
377 ,p_attribute12_o
378 => pay_ppm_shd.g_old_rec.attribute12
379 ,p_attribute13_o
380 => pay_ppm_shd.g_old_rec.attribute13
381 ,p_attribute14_o
382 => pay_ppm_shd.g_old_rec.attribute14
383 ,p_attribute15_o
384 => pay_ppm_shd.g_old_rec.attribute15
385 ,p_attribute16_o
386 => pay_ppm_shd.g_old_rec.attribute16
387 ,p_attribute17_o
388 => pay_ppm_shd.g_old_rec.attribute17
389 ,p_attribute18_o
390 => pay_ppm_shd.g_old_rec.attribute18
391 ,p_attribute19_o
392 => pay_ppm_shd.g_old_rec.attribute19
393 ,p_attribute20_o
394 => pay_ppm_shd.g_old_rec.attribute20
395 ,p_object_version_number_o
396 => pay_ppm_shd.g_old_rec.object_version_number
397 ,p_payee_type_o
398 => pay_ppm_shd.g_old_rec.payee_type
399 ,p_payee_id_o
400 => pay_ppm_shd.g_old_rec.payee_id
401 ,p_ppm_information_category_o
402 => pay_ppm_shd.g_old_rec.ppm_information_category
403 ,p_ppm_information1_o
404 => pay_ppm_shd.g_old_rec.ppm_information1
405 ,p_ppm_information2_o
406 => pay_ppm_shd.g_old_rec.ppm_information2
407 ,p_ppm_information3_o
408 => pay_ppm_shd.g_old_rec.ppm_information3
409 ,p_ppm_information4_o
410 => pay_ppm_shd.g_old_rec.ppm_information4
411 ,p_ppm_information5_o
412 => pay_ppm_shd.g_old_rec.ppm_information5
413 ,p_ppm_information6_o
414 => pay_ppm_shd.g_old_rec.ppm_information6
415 ,p_ppm_information7_o
416 => pay_ppm_shd.g_old_rec.ppm_information7
417 ,p_ppm_information8_o
418 => pay_ppm_shd.g_old_rec.ppm_information8
419 ,p_ppm_information9_o
420 => pay_ppm_shd.g_old_rec.ppm_information9
421 ,p_ppm_information10_o
422 => pay_ppm_shd.g_old_rec.ppm_information10
423 ,p_ppm_information11_o
424 => pay_ppm_shd.g_old_rec.ppm_information11
425 ,p_ppm_information12_o
426 => pay_ppm_shd.g_old_rec.ppm_information12
427 ,p_ppm_information13_o
428 => pay_ppm_shd.g_old_rec.ppm_information13
429 ,p_ppm_information14_o
430 => pay_ppm_shd.g_old_rec.ppm_information14
431 ,p_ppm_information15_o
432 => pay_ppm_shd.g_old_rec.ppm_information15
433 ,p_ppm_information16_o
434 => pay_ppm_shd.g_old_rec.ppm_information16
435 ,p_ppm_information17_o
436 => pay_ppm_shd.g_old_rec.ppm_information17
437 ,p_ppm_information18_o
438 => pay_ppm_shd.g_old_rec.ppm_information18
439 ,p_ppm_information19_o
440 => pay_ppm_shd.g_old_rec.ppm_information19
441 ,p_ppm_information20_o
442 => pay_ppm_shd.g_old_rec.ppm_information20
443 ,p_ppm_information21_o
444 => pay_ppm_shd.g_old_rec.ppm_information21
445 ,p_ppm_information22_o
446 => pay_ppm_shd.g_old_rec.ppm_information22
447 ,p_ppm_information23_o
448 => pay_ppm_shd.g_old_rec.ppm_information23
449 ,p_ppm_information24_o
450 => pay_ppm_shd.g_old_rec.ppm_information24
451 ,p_ppm_information25_o
452 => pay_ppm_shd.g_old_rec.ppm_information25
453 ,p_ppm_information26_o
454 => pay_ppm_shd.g_old_rec.ppm_information26
455 ,p_ppm_information27_o
456 => pay_ppm_shd.g_old_rec.ppm_information27
457 ,p_ppm_information28_o
458 => pay_ppm_shd.g_old_rec.ppm_information28
459 ,p_ppm_information29_o
460 => pay_ppm_shd.g_old_rec.ppm_information29
461 ,p_ppm_information30_o
462 => pay_ppm_shd.g_old_rec.ppm_information30
463 );
464 exception
465 when hr_api.cannot_find_prog_unit then
466 hr_api.cannot_find_prog_unit_error
467 (p_module_name => 'PAY_PERSONAL_PAYMENT_METHODS_F'
468 ,p_hook_type => 'AD'
469 );
470 end;
471 -- End of API User Hook for post_delete.
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End post_delete;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------------< del >----------------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure del
480 (
481 p_rec in out nocopy pay_ppm_shd.g_rec_type,
482 p_effective_date in date,
483 p_datetrack_mode in varchar2,
484 p_validate in boolean default false
485 ) is
486 --
487 l_proc varchar2(72) := g_package||'del';
488 l_validation_start_date date;
489 l_validation_end_date date;
490 --
491 Begin
492 hr_utility.set_location('Entering:'||l_proc, 5);
493 --
494 -- Ensure that the DateTrack delete mode is valid
495 --
496 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
497 --
498 -- Determine if the business process is to be validated.
499 --
500 If p_validate then
501 --
502 -- Issue the savepoint.
503 --
504 SAVEPOINT del_pay_ppm;
505 End If;
506 --
507 -- We must lock the row which we need to delete.
508 --
509 pay_ppm_shd.lck
510 (p_effective_date => p_effective_date,
511 p_datetrack_mode => p_datetrack_mode,
512 p_personal_payment_method_id => p_rec.personal_payment_method_id,
513 p_object_version_number => p_rec.object_version_number,
514 p_validation_start_date => l_validation_start_date,
515 p_validation_end_date => l_validation_end_date);
516 --
517 -- Return the effective end date
518 --
519 l_validation_end_date := pay_ppm_bus.return_effective_end_date
520 (p_datetrack_mode => p_datetrack_mode
521 ,p_effective_date => p_effective_date
522 ,p_org_payment_method_id =>
523 pay_ppm_shd.g_old_rec.org_payment_method_id
524 ,p_business_group_id =>
525 pay_ppm_shd.g_old_rec.business_group_id
526 ,p_personal_payment_method_id =>
527 pay_ppm_shd.g_old_rec.personal_payment_method_id
528 ,p_assignment_id =>
529 pay_ppm_shd.g_old_rec.assignment_id
530 ,p_run_type_id =>
531 pay_ppm_shd.g_old_rec.run_type_id
532 ,p_priority =>
533 pay_ppm_shd.g_old_rec.priority
534 ,p_validation_start_date => l_validation_start_date
535 ,p_validation_end_date => l_validation_end_date
536 );
537 --
538 -- Call the supporting delete validate operation
539 --
540 pay_ppm_bus.delete_validate
541 (p_rec => p_rec,
542 p_effective_date => p_effective_date,
543 p_datetrack_mode => p_datetrack_mode,
544 p_validation_start_date => l_validation_start_date,
545 p_validation_end_date => l_validation_end_date);
546 --
547 -- Call the supporting pre-delete operation
548 --
549 pre_delete
550 (p_rec => p_rec,
551 p_effective_date => p_effective_date,
552 p_datetrack_mode => p_datetrack_mode,
553 p_validation_start_date => l_validation_start_date,
554 p_validation_end_date => l_validation_end_date);
555 --
556 -- Delete the row.
557 --
558 delete_dml
559 (p_rec => p_rec,
560 p_effective_date => p_effective_date,
561 p_datetrack_mode => p_datetrack_mode,
562 p_validation_start_date => l_validation_start_date,
563 p_validation_end_date => l_validation_end_date);
564 --
565 -- Call the supporting post-delete operation
566 --
567 post_delete
568 (p_rec => p_rec,
569 p_effective_date => p_effective_date,
570 p_datetrack_mode => p_datetrack_mode,
571 p_validation_start_date => l_validation_start_date,
572 p_validation_end_date => l_validation_end_date);
573 --
574 -- If we are validating then raise the Validate_Enabled exception
575 --
576 If p_validate then
577 Raise HR_Api.Validate_Enabled;
578 End If;
579 --
580 hr_utility.set_location(' Leaving:'||l_proc, 10);
581 Exception
582 When HR_Api.Validate_Enabled Then
583 --
584 -- As the Validate_Enabled exception has been raised
585 -- we must rollback to the savepoint
586 --
587 ROLLBACK TO del_pay_ppm;
588 End del;
589 --
590 -- ----------------------------------------------------------------------------
591 -- |---------------------------------< del >----------------------------------|
592 -- ----------------------------------------------------------------------------
593 Procedure del
594 (
595 p_personal_payment_method_id in number,
596 p_effective_start_date out nocopy date,
597 p_effective_end_date out nocopy date,
598 p_object_version_number in out nocopy number,
599 p_effective_date in date,
600 p_datetrack_mode in varchar2,
601 p_validate in boolean default false
602 ) is
603 --
604 l_rec pay_ppm_shd.g_rec_type;
605 l_proc varchar2(72) := g_package||'del';
606 --
607 Begin
608 hr_utility.set_location('Entering:'||l_proc, 5);
609 --
610 -- As the delete procedure accepts a plsql record structure we do need to
611 -- convert the arguments into the record structure.
612 -- We don't need to call the supplied conversion argument routine as we
613 -- only need a few attributes.
614 --
615 l_rec.personal_payment_method_id := p_personal_payment_method_id;
616 l_rec.object_version_number := p_object_version_number;
617 --
618 -- Having converted the arguments into the pay_ppm_rec
619 -- plsql record structure we must call the corresponding entity
620 -- business process
621 --
622 del(l_rec, p_effective_date, p_datetrack_mode, p_validate);
623 --
624 -- Set the out arguments
625 --
626 p_object_version_number := l_rec.object_version_number;
627 p_effective_start_date := l_rec.effective_start_date;
628 p_effective_end_date := l_rec.effective_end_date;
629 --
630 hr_utility.set_location(' Leaving:'||l_proc, 10);
631 End del;
632 --
633 end pay_ppm_del;