[Home] [Help]
PACKAGE BODY: APPS.PQP_VAL_DEL
Source
1 Package Body pqp_val_del as
2 /* $Header: pqvalrhi.pkb 120.0.12010000.3 2008/08/08 07:22:41 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_val_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 structure.
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 pqp_val_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
59 ) is
60 --
61 l_proc varchar2(72) := g_package||'dt_delete_dml';
62 --
63 Begin
64 hr_utility.set_location('Entering:'||l_proc, 5);
65 If (p_datetrack_mode = hr_api.g_delete_next_change) then
66 --
67 --
68 -- Delete the where the effective start date is equal
69 -- to the validation end date.
70 --
71 delete from pqp_vehicle_allocations_f
72 where vehicle_allocation_id = p_rec.vehicle_allocation_id
73 and effective_start_date = p_validation_start_date;
74 --
75 --
76 Else
77 --
78 --
79 -- Delete the row(s) where the effective start date is greater than
80 -- or equal to the validation start date.
81 --
82 delete from pqp_vehicle_allocations_f
83 where vehicle_allocation_id = p_rec.vehicle_allocation_id
84 and effective_start_date >= p_validation_start_date;
85 --
86 --
87 End If;
88 --
89 hr_utility.set_location(' Leaving:'||l_proc, 20);
90 --
91 End dt_delete_dml;
92 --
93 -- ----------------------------------------------------------------------------
94 -- |------------------------------< delete_dml >------------------------------|
95 -- ----------------------------------------------------------------------------
96 Procedure delete_dml
97 (p_rec in out nocopy pqp_val_shd.g_rec_type
98 ,p_effective_date in date
99 ,p_datetrack_mode in varchar2
100 ,p_validation_start_date in date
101 ,p_validation_end_date in date
102 ) is
103 --
104 l_proc varchar2(72) := g_package||'delete_dml';
105 --
106 Begin
107 hr_utility.set_location('Entering:'||l_proc, 5);
108 --
109 pqp_val_del.dt_delete_dml
110 (p_rec => p_rec
111 ,p_effective_date => p_effective_date
112 ,p_datetrack_mode => p_datetrack_mode
113 ,p_validation_start_date => p_validation_start_date
114 ,p_validation_end_date => p_validation_end_date
115 );
116 --
117 hr_utility.set_location(' Leaving:'||l_proc, 10);
118 End delete_dml;
119 --
120 -- ----------------------------------------------------------------------------
121 -- |----------------------------< dt_pre_delete >-----------------------------|
122 -- ----------------------------------------------------------------------------
123 -- {Start Of Comments}
124 --
125 -- Description:
126 -- The dt_pre_delete process controls the execution of dml
127 -- for the datetrack modes: DELETE, FUTURE_CHANGE
128 -- and DELETE_NEXT_CHANGE only.
129 --
130 -- Prerequisites:
131 -- This is an internal procedure which is called from the pre_delete
132 -- procedure.
133 --
134 -- In Parameters:
135 -- A Pl/Sql record structure.
136 --
137 -- Post Success:
138 -- Processing continues.
139 --
140 -- Post Failure:
141 -- If an error has occurred, an error message and exception will be raised
142 -- but not handled.
143 --
144 -- Developer Implementation Notes:
145 -- This is an internal procedure which is required by Datetrack. Don't
146 -- remove or modify.
147 --
148 -- Access Status:
149 -- Internal Row Handler Use Only.
150 --
151 -- {End Of Comments}
152 -- ----------------------------------------------------------------------------
153 Procedure dt_pre_delete
154 (p_rec in out nocopy pqp_val_shd.g_rec_type
155 ,p_effective_date in date
156 ,p_datetrack_mode in varchar2
157 ,p_validation_start_date in date
158 ,p_validation_end_date in date
159 ) is
160 --
161 l_proc varchar2(72) := g_package||'dt_pre_delete';
162 --
163 Begin
164 hr_utility.set_location('Entering:'||l_proc, 5);
165 --
166 If (p_datetrack_mode <> hr_api.g_zap) then
167 --
168 p_rec.effective_start_date
169 := pqp_val_shd.g_old_rec.effective_start_date;
170 --
171 If (p_datetrack_mode = hr_api.g_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 pqp_val_shd.upd_effective_end_date
180 (p_effective_date => p_effective_date
181 ,p_base_key_value => p_rec.vehicle_allocation_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 );
187 Else
188 p_rec.effective_start_date := null;
189 p_rec.effective_end_date := null;
190 End If;
191
192 hr_utility.set_location(' Leaving:'||l_proc, 10);
193 End dt_pre_delete;
194 --
195 -- ----------------------------------------------------------------------------
196 -- |------------------------------< pre_delete >------------------------------|
197 -- ----------------------------------------------------------------------------
198 -- {Start Of Comments}
199 --
200 -- Description:
201 -- This private procedure contains any processing which is required before
202 -- the delete dml.
203 --
204 -- Prerequisites:
205 -- This is an internal procedure which is called from the del procedure.
206 --
207 -- In Parameters:
208 -- A Pl/Sql record structure.
209 --
210 -- Post Success:
211 -- Processing continues.
212 --
213 -- Post Failure:
214 -- If an error has occurred, an error message and exception will be raised
215 -- but not handled.
216 --
217 -- Developer Implementation Notes:
218 -- Any pre-processing required before the delete dml is issued should be
219 -- coded within this procedure. It is important to note that any 3rd party
220 -- maintenance should be reviewed before placing in this procedure. The call
221 -- to the dt_delete_dml procedure should NOT be removed.
222 --
223 -- Access Status:
224 -- Internal Row Handler Use Only.
225 --
226 -- {End Of Comments}
227 -- ----------------------------------------------------------------------------
228 Procedure pre_delete
229 (p_rec in out nocopy pqp_val_shd.g_rec_type
230 ,p_effective_date in date
231 ,p_datetrack_mode in varchar2
232 ,p_validation_start_date in date
233 ,p_validation_end_date in date
234 ) is
235 --
236 l_proc varchar2(72) := g_package||'pre_delete';
237 l_legislation_code varchar2(10);
238 --
239 cursor c_get_extra_info
240 (cp_vehicle_allocation_id number) is
241 select pva.veh_alloc_extra_info_id
242 ,pva.object_version_number
243 from pqp_veh_alloc_extra_info pva
244 where pva.vehicle_allocation_id = cp_vehicle_allocation_id;
245 --
246 Begin
247 hr_utility.set_location('Entering:'||l_proc, 5);
248 --
249 pqp_val_del.dt_pre_delete
250 (p_rec => p_rec
251 ,p_effective_date => p_effective_date
252 ,p_datetrack_mode => p_datetrack_mode
253 ,p_validation_start_date => p_validation_start_date
254 ,p_validation_end_date => p_validation_end_date
255 );
256 --
257 l_legislation_code := pqp_vre_bus.get_legislation_code
258 (pqp_val_shd.g_old_rec.business_group_id);
259 --
260 If l_legislation_code = 'GB' Then
261 Begin
262 pqp_val_bus.del_ni_car_entry
263 (p_business_group_id => pqp_val_shd.g_old_rec.business_group_id
264 ,p_assignment_id => pqp_val_shd.g_old_rec.assignment_id
265 ,p_allocation_id => p_rec.vehicle_allocation_id
266 ,p_effective_date => p_effective_date
267 );
268 Exception
269 When others Then
270 Null;
271 End ;
272 End if;
273 --
274 -- Purge the child rows from the table pqp_veh_alloc_extra_info table
275 -- for the corresponding vehicle allocation id.
276 --
277 If p_datetrack_mode = hr_api.g_zap Then
278 Begin
279 For vxi_rec in c_get_extra_info (p_rec.vehicle_allocation_id)
280 Loop
281 pqp_veh_alloc_extra_info_api.delete_veh_alloc_extra_info
282 (p_veh_alloc_extra_info_id => vxi_rec.veh_alloc_extra_info_id
283 ,p_object_version_number => vxi_rec.object_version_number
284 );
285 End Loop;
286 Exception
287 When Others Then
288 hr_utility.set_location(' exception:'||l_proc, 9);
289 Null;
290 End;
291 End If;
292 --
293 hr_utility.set_location(' Leaving:'||l_proc, 10);
294 End pre_delete;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |----------------------------< post_delete >-------------------------------|
298 -- ----------------------------------------------------------------------------
299 -- {Start Of Comments}
300 --
301 -- Description:
302 -- This private procedure contains any processing which is required after
303 -- the delete dml.
304 --
305 -- Prerequisites:
306 -- This is an internal procedure which is called from the del procedure.
307 --
308 -- In Parameters:
309 -- A Pl/Sql record structure.
310 --
311 -- Post Success:
312 -- Processing continues.
313 --
314 -- Post Failure:
315 -- If an error has occurred, an error message and exception will be raised
316 -- but not handled.
317 --
318 -- Developer Implementation Notes:
319 -- Any post-processing required after the delete dml is issued should be
320 -- coded within this procedure. It is important to note that any 3rd party
321 -- maintenance should be reviewed before placing in this procedure.
322 --
323 -- Access Status:
324 -- Internal Row Handler Use Only.
325 --
326 -- {End Of Comments}
327 -- ----------------------------------------------------------------------------
328 Procedure post_delete
329 (p_rec in pqp_val_shd.g_rec_type
330 ,p_effective_date in date
331 ,p_datetrack_mode in varchar2
332 ,p_validation_start_date in date
333 ,p_validation_end_date in date
334 ) is
335 --
336 l_proc varchar2(72) := g_package||'post_delete';
337
338 --
339 Begin
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 begin
342 --
343 pqp_val_rkd.after_delete
344 (p_effective_date
345 => p_effective_date
346 ,p_datetrack_mode
347 => p_datetrack_mode
348 ,p_validation_start_date
349 => p_validation_start_date
350 ,p_validation_end_date
351 => p_validation_end_date
352 ,p_vehicle_allocation_id
353 => p_rec.vehicle_allocation_id
354 ,p_effective_start_date
355 => p_rec.effective_start_date
356 ,p_effective_end_date
357 => p_rec.effective_end_date
358 ,p_effective_start_date_o
359 => pqp_val_shd.g_old_rec.effective_start_date
360 ,p_effective_end_date_o
361 => pqp_val_shd.g_old_rec.effective_end_date
362 ,p_assignment_id_o
363 => pqp_val_shd.g_old_rec.assignment_id
364 ,p_business_group_id_o
365 => pqp_val_shd.g_old_rec.business_group_id
366 ,p_across_assignments_o
367 => pqp_val_shd.g_old_rec.across_assignments
368 ,p_vehicle_repository_id_o
369 => pqp_val_shd.g_old_rec.vehicle_repository_id
370 ,p_usage_type_o
371 => pqp_val_shd.g_old_rec.usage_type
372 ,p_capital_contribution_o
373 => pqp_val_shd.g_old_rec.capital_contribution
374 ,p_private_contribution_o
375 => pqp_val_shd.g_old_rec.private_contribution
376 ,p_default_vehicle_o
377 => pqp_val_shd.g_old_rec.default_vehicle
378 ,p_fuel_card_o
379 => pqp_val_shd.g_old_rec.fuel_card
380 ,p_fuel_card_number_o
381 => pqp_val_shd.g_old_rec.fuel_card_number
382 ,p_calculation_method_o
383 => pqp_val_shd.g_old_rec.calculation_method
384 ,p_rates_table_id_o
385 => pqp_val_shd.g_old_rec.rates_table_id
386 ,p_element_type_id_o
387 => pqp_val_shd.g_old_rec.element_type_id
388 ,p_private_use_flag_o
389 => pqp_val_shd.g_old_rec.private_use_flag
390 ,p_insurance_number_o
391 => pqp_val_shd.g_old_rec.insurance_number
392 ,p_insurance_expiry_date_o
393 => pqp_val_shd.g_old_rec.insurance_expiry_date
394 ,p_val_attribute_category_o
395 => pqp_val_shd.g_old_rec.val_attribute_category
396 ,p_val_attribute1_o
397 => pqp_val_shd.g_old_rec.val_attribute1
398 ,p_val_attribute2_o
399 => pqp_val_shd.g_old_rec.val_attribute2
400 ,p_val_attribute3_o
401 => pqp_val_shd.g_old_rec.val_attribute3
402 ,p_val_attribute4_o
403 => pqp_val_shd.g_old_rec.val_attribute4
404 ,p_val_attribute5_o
405 => pqp_val_shd.g_old_rec.val_attribute5
406 ,p_val_attribute6_o
407 => pqp_val_shd.g_old_rec.val_attribute6
408 ,p_val_attribute7_o
409 => pqp_val_shd.g_old_rec.val_attribute7
410 ,p_val_attribute8_o
411 => pqp_val_shd.g_old_rec.val_attribute8
412 ,p_val_attribute9_o
413 => pqp_val_shd.g_old_rec.val_attribute9
414 ,p_val_attribute10_o
415 => pqp_val_shd.g_old_rec.val_attribute10
416 ,p_val_attribute11_o
417 => pqp_val_shd.g_old_rec.val_attribute11
418 ,p_val_attribute12_o
419 => pqp_val_shd.g_old_rec.val_attribute12
420 ,p_val_attribute13_o
421 => pqp_val_shd.g_old_rec.val_attribute13
422 ,p_val_attribute14_o
423 => pqp_val_shd.g_old_rec.val_attribute14
424 ,p_val_attribute15_o
425 => pqp_val_shd.g_old_rec.val_attribute15
426 ,p_val_attribute16_o
427 => pqp_val_shd.g_old_rec.val_attribute16
428 ,p_val_attribute17_o
429 => pqp_val_shd.g_old_rec.val_attribute17
430 ,p_val_attribute18_o
431 => pqp_val_shd.g_old_rec.val_attribute18
432 ,p_val_attribute19_o
433 => pqp_val_shd.g_old_rec.val_attribute19
434 ,p_val_attribute20_o
435 => pqp_val_shd.g_old_rec.val_attribute20
436 ,p_val_information_category_o
437 => pqp_val_shd.g_old_rec.val_information_category
438 ,p_val_information1_o
439 => pqp_val_shd.g_old_rec.val_information1
440 ,p_val_information2_o
441 => pqp_val_shd.g_old_rec.val_information2
442 ,p_val_information3_o
443 => pqp_val_shd.g_old_rec.val_information3
444 ,p_val_information4_o
445 => pqp_val_shd.g_old_rec.val_information4
446 ,p_val_information5_o
447 => pqp_val_shd.g_old_rec.val_information5
448 ,p_val_information6_o
449 => pqp_val_shd.g_old_rec.val_information6
450 ,p_val_information7_o
451 => pqp_val_shd.g_old_rec.val_information7
452 ,p_val_information8_o
453 => pqp_val_shd.g_old_rec.val_information8
454 ,p_val_information9_o
455 => pqp_val_shd.g_old_rec.val_information9
456 ,p_val_information10_o
457 => pqp_val_shd.g_old_rec.val_information10
458 ,p_val_information11_o
459 => pqp_val_shd.g_old_rec.val_information11
460 ,p_val_information12_o
461 => pqp_val_shd.g_old_rec.val_information12
462 ,p_val_information13_o
463 => pqp_val_shd.g_old_rec.val_information13
464 ,p_val_information14_o
465 => pqp_val_shd.g_old_rec.val_information14
466 ,p_val_information15_o
467 => pqp_val_shd.g_old_rec.val_information15
468 ,p_val_information16_o
469 => pqp_val_shd.g_old_rec.val_information16
470 ,p_val_information17_o
471 => pqp_val_shd.g_old_rec.val_information17
472 ,p_val_information18_o
473 => pqp_val_shd.g_old_rec.val_information18
474 ,p_val_information19_o
475 => pqp_val_shd.g_old_rec.val_information19
476 ,p_val_information20_o
477 => pqp_val_shd.g_old_rec.val_information20
478 ,p_object_version_number_o
479 => pqp_val_shd.g_old_rec.object_version_number
480 ,p_fuel_benefit_o
481 => pqp_val_shd.g_old_rec.fuel_benefit
482 ,p_sliding_rates_info_o
483 => pqp_val_shd.g_old_rec.sliding_rates_info
484
485 );
486 --
487 exception
488 --
489 when hr_api.cannot_find_prog_unit then
490 --
491 hr_api.cannot_find_prog_unit_error
492 (p_module_name => 'PQP_VEHICLE_ALLOCATIONS_F'
493 ,p_hook_type => 'AD');
494 --
495 end;
496 hr_utility.set_location(' Leaving:'||l_proc, 10);
497 End post_delete;
498 --
499 -- ----------------------------------------------------------------------------
500 -- |---------------------------------< del >----------------------------------|
501 -- ----------------------------------------------------------------------------
502 Procedure del
503 (p_effective_date in date
504 ,p_datetrack_mode in varchar2
505 ,p_rec in out nocopy pqp_val_shd.g_rec_type
506 ) is
507 --
508 l_proc varchar2(72) := g_package||'del';
509 l_validation_start_date date;
510 l_validation_end_date date;
511 --
512 Begin
513 hr_utility.set_location('Entering:'||l_proc, 5);
514 --
515 -- Ensure that the DateTrack delete mode is valid
516 --
517 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
518 --
519 -- We must lock the row which we need to delete.
520 --
521 pqp_val_shd.lck
522 (p_effective_date => p_effective_date
523 ,p_datetrack_mode => p_datetrack_mode
524 ,p_vehicle_allocation_id => p_rec.vehicle_allocation_id
525 ,p_object_version_number => p_rec.object_version_number
526 ,p_validation_start_date => l_validation_start_date
527 ,p_validation_end_date => l_validation_end_date
528 );
529 --
530 -- Call the supporting delete validate operation
531 --
532 pqp_val_bus.delete_validate
533 (p_rec => p_rec
534 ,p_effective_date => p_effective_date
535 ,p_datetrack_mode => p_datetrack_mode
536 ,p_validation_start_date => l_validation_start_date
537 ,p_validation_end_date => l_validation_end_date
538 );
539
540
541 --
542 -- Call to raise any errors on multi-message list
543 hr_multi_message.end_validation_set;
544
545
546
547 --
548 -- Call the supporting pre-delete operation
549 --
550 pqp_val_del.pre_delete
551 (p_rec => p_rec
552 ,p_effective_date => p_effective_date
553 ,p_datetrack_mode => p_datetrack_mode
554 ,p_validation_start_date => l_validation_start_date
555 ,p_validation_end_date => l_validation_end_date
556 );
557
558
559 --
560 -- Delete the row.
561 --
562
563
564
565 pqp_val_del.delete_dml
566 (p_rec => p_rec
567 ,p_effective_date => p_effective_date
568 ,p_datetrack_mode => p_datetrack_mode
569 ,p_validation_start_date => l_validation_start_date
570 ,p_validation_end_date => l_validation_end_date
571 );
572
573
574 -- Call the supporting post-delete operation
575 --
576 pqp_val_del.post_delete
577 (p_rec => p_rec
578 ,p_effective_date => p_effective_date
579 ,p_datetrack_mode => p_datetrack_mode
580 ,p_validation_start_date => l_validation_start_date
581 ,p_validation_end_date => l_validation_end_date
582 );
583
584
585 --
586 -- Call to raise any errors on multi-message list
587 hr_multi_message.end_validation_set;
588 --
589 hr_utility.set_location(' Leaving:'||l_proc, 5);
590
591 End del;
592 --
593 -- ----------------------------------------------------------------------------
594 -- |--------------------------------< del >-----------------------------------|
595 -- ----------------------------------------------------------------------------
596 Procedure del
597 (p_effective_date in date
598 ,p_datetrack_mode in varchar2
599 ,p_vehicle_allocation_id in number
600 ,p_object_version_number in out nocopy number
601 ,p_effective_start_date out nocopy date
602 ,p_effective_end_date out nocopy date
603 ) is
604 --
605 l_rec pqp_val_shd.g_rec_type;
606 l_proc varchar2(72) := g_package||'del';
607 --
608 Begin
609 hr_utility.set_location('Entering:'||l_proc, 5);
610 --
611 -- As the delete procedure accepts a plsql record structure we do need to
612 -- convert the arguments into the record structure.
613 -- We don't need to call the supplied conversion argument routine as we
614 -- only need a few attributes.
615 --
616 l_rec.vehicle_allocation_id := p_vehicle_allocation_id;
617 l_rec.object_version_number := p_object_version_number;
618 --
619 -- Having converted the arguments into the pqp_val_rec
620 -- plsql record structure we must call the corresponding entity
621 -- business process
622 --
623 pqp_val_del.del
624 (p_effective_date
625 ,p_datetrack_mode
626 ,l_rec
627 );
628 --
629 --
630 -- Set the out arguments
631 --
632 p_object_version_number := l_rec.object_version_number;
633 p_effective_start_date := l_rec.effective_start_date;
634 p_effective_end_date := l_rec.effective_end_date;
635 --
636 hr_utility.set_location(' Leaving:'||l_proc, 10);
637 End del;
638 --
639 end pqp_val_del;