1 Package Body pay_ivl_del as
2 /* $Header: pyivlrhi.pkb 120.0 2005/05/29 06:04:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ivl_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 pay_ivl_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 pay_ivl_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_input_values_f
72 where input_value_id = p_rec.input_value_id
73 and effective_start_date = p_validation_start_date;
74 --
75 pay_ivl_shd.g_api_dml := false; -- Unset the api dml status
76 Else
77 pay_ivl_shd.g_api_dml := true; -- Set the api dml status
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 pay_input_values_f
83 where input_value_id = p_rec.input_value_id
84 and effective_start_date >= p_validation_start_date;
85 --
86 pay_ivl_shd.g_api_dml := false; -- Unset the api dml status
87 End If;
88 --
89 hr_utility.set_location(' Leaving:'||l_proc, 20);
90 --
91 Exception
92 When Others Then
93 pay_ivl_shd.g_api_dml := false; -- Unset the api dml status
94 Raise;
95 --
96 End dt_delete_dml;
97 --
98 -- ----------------------------------------------------------------------------
99 -- ----------------------< delete_app_ownerships >----------------------------|
100 -- ----------------------------------------------------------------------------
101 --
102 -- Description:
103 -- Deletes row(s) from hr_application_ownerships depending on the mode that
104 -- the row handler has been called in.
105 --
106 -- ----------------------------------------------------------------------------
107 PROCEDURE delete_app_ownerships(p_pk_column IN varchar2
108 ,p_pk_value IN varchar2
109 ,p_datetrack_mode IN varchar2) IS
110 --
111 BEGIN
112 --
113 IF ((hr_startup_data_api_support.return_startup_mode
114 IN ('STARTUP','GENERIC')) AND
115 p_datetrack_mode = hr_api.g_zap) THEN
116 --
117 DELETE FROM hr_application_ownerships
118 WHERE key_name = p_pk_column
119 AND key_value = p_pk_value;
120 --
121 END IF;
122 END delete_app_ownerships;
123 --
124 -- ----------------------------------------------------------------------------
125 -- ----------------------< delete_app_ownerships >----------------------------|
126 -- ----------------------------------------------------------------------------
127 PROCEDURE delete_app_ownerships(p_pk_column IN varchar2
128 ,p_pk_value IN number
129 ,p_datetrack_mode IN varchar2) IS
130 --
131 BEGIN
132 delete_app_ownerships(p_pk_column, to_char(p_pk_value), p_datetrack_mode);
133 END delete_app_ownerships;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |------------------------------< delete_dml >------------------------------|
137 -- ----------------------------------------------------------------------------
138 Procedure delete_dml
139 (p_rec in out nocopy pay_ivl_shd.g_rec_type
140 ,p_effective_date in date
141 ,p_datetrack_mode in varchar2
142 ,p_validation_start_date in date
143 ,p_validation_end_date in date
144 ) is
145 --
146 l_proc varchar2(72) := g_package||'delete_dml';
147 --
148 Begin
149 hr_utility.set_location('Entering:'||l_proc, 5);
150 --
151 pay_ivl_del.dt_delete_dml
152 (p_rec => p_rec
153 ,p_effective_date => p_effective_date
154 ,p_datetrack_mode => p_datetrack_mode
155 ,p_validation_start_date => p_validation_start_date
156 ,p_validation_end_date => p_validation_end_date
157 );
158 --
159 hr_utility.set_location(' Leaving:'||l_proc, 10);
160 End delete_dml;
161 --
162 -- ----------------------------------------------------------------------------
163 -- |----------------------------< dt_pre_delete >-----------------------------|
164 -- ----------------------------------------------------------------------------
165 -- {Start Of Comments}
166 --
167 -- Description:
168 -- The dt_pre_delete process controls the execution of dml
169 -- for the datetrack modes: DELETE, FUTURE_CHANGE
170 -- and DELETE_NEXT_CHANGE only.
171 --
172 -- Prerequisites:
173 -- This is an internal procedure which is called from the pre_delete
174 -- procedure.
175 --
176 -- In Parameters:
177 -- A Pl/Sql record structure.
178 --
179 -- Post Success:
180 -- Processing continues.
181 --
182 -- Post Failure:
183 -- If an error has occurred, an error message and exception will be raised
184 -- but not handled.
185 --
186 -- Developer Implementation Notes:
187 -- This is an internal procedure which is required by Datetrack. Don't
188 -- remove or modify.
189 --
190 -- Access Status:
191 -- Internal Row Handler Use Only.
192 --
193 -- {End Of Comments}
194 -- ----------------------------------------------------------------------------
195 Procedure dt_pre_delete
196 (p_rec in out nocopy pay_ivl_shd.g_rec_type
197 ,p_effective_date in date
198 ,p_datetrack_mode in varchar2
199 ,p_validation_start_date in date
200 ,p_validation_end_date in date
201 ) is
202 --
203 l_proc varchar2(72) := g_package||'dt_pre_delete';
204 --
205 Begin
206 hr_utility.set_location('Entering:'||l_proc, 5);
207 --
208 If (p_datetrack_mode <> hr_api.g_zap) then
209 --
210 p_rec.effective_start_date
211 := pay_ivl_shd.g_old_rec.effective_start_date;
212 --
213 If (p_datetrack_mode = hr_api.g_delete) then
214 p_rec.effective_end_date := p_validation_start_date - 1;
215 Else
216 p_rec.effective_end_date := p_validation_end_date;
217 End If;
218 --
219 -- Update the current effective end date record
220 --
221 pay_ivl_shd.upd_effective_end_date
222 (p_effective_date => p_effective_date
223 ,p_base_key_value => p_rec.input_value_id
224 ,p_new_effective_end_date => p_rec.effective_end_date
225 ,p_validation_start_date => p_validation_start_date
226 ,p_validation_end_date => p_validation_end_date
227 ,p_object_version_number => p_rec.object_version_number
228 );
229 Else
230 p_rec.effective_start_date := null;
231 p_rec.effective_end_date := null;
232 End If;
233 hr_utility.set_location(' Leaving:'||l_proc, 10);
234 End dt_pre_delete;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |------------------------------< pre_delete >------------------------------|
238 -- ----------------------------------------------------------------------------
239 -- {Start Of Comments}
240 --
241 -- Description:
242 -- This private procedure contains any processing which is required before
243 -- the delete dml.
244 --
245 -- Prerequisites:
246 -- This is an internal procedure which is called from the del procedure.
247 --
248 -- In Parameters:
249 -- A Pl/Sql record structure.
250 --
251 -- Post Success:
252 -- Processing continues.
253 --
254 -- Post Failure:
255 -- If an error has occurred, an error message and exception will be raised
256 -- but not handled.
257 --
258 -- Developer Implementation Notes:
259 -- Any pre-processing required before the delete dml is issued should be
260 -- coded within this procedure. It is important to note that any 3rd party
261 -- maintenance should be reviewed before placing in this procedure. The call
262 -- to the dt_delete_dml procedure should NOT be removed.
263 --
264 -- Access Status:
265 -- Internal Row Handler Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269 Procedure pre_delete
270 (p_rec in out nocopy pay_ivl_shd.g_rec_type
271 ,p_effective_date in date
272 ,p_datetrack_mode in varchar2
273 ,p_validation_start_date in date
274 ,p_validation_end_date in date
275 ) is
276 --
277 l_proc varchar2(72) := g_package||'pre_delete';
278 --
279 --
280 --
281 Begin
282 hr_utility.set_location('Entering:'||l_proc, 5);
283 --
284 --
285 --
286 pay_ivl_del.dt_pre_delete
287 (p_rec => p_rec
288 ,p_effective_date => p_effective_date
289 ,p_datetrack_mode => p_datetrack_mode
290 ,p_validation_start_date => p_validation_start_date
291 ,p_validation_end_date => p_validation_end_date
292 );
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 10);
295 End pre_delete;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |----------------------------< post_delete >-------------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This private procedure contains any processing which is required after
304 -- the delete dml.
305 --
306 -- Prerequisites:
307 -- This is an internal procedure which is called from the del procedure.
308 --
309 -- In Parameters:
310 -- A Pl/Sql record structure.
311 --
312 -- Post Success:
313 -- Processing continues.
314 --
315 -- Post Failure:
316 -- If an error has occurred, an error message and exception will be raised
317 -- but not handled.
318 --
319 -- Developer Implementation Notes:
320 -- Any post-processing required after the delete dml is issued should be
321 -- coded within this procedure. It is important to note that any 3rd party
322 -- maintenance should be reviewed before placing in this procedure.
323 --
324 -- Access Status:
325 -- Internal Row Handler Use Only.
326 --
327 -- {End Of Comments}
328 -- ----------------------------------------------------------------------------
329 Procedure post_delete
330 (p_rec in pay_ivl_shd.g_rec_type
331 ,p_effective_date in date
332 ,p_datetrack_mode in varchar2
333 ,p_validation_start_date in date
334 ,p_validation_end_date in date
335 ) is
336 --
337 l_proc varchar2(72) := g_package||'post_delete';
338 --
339 Begin
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 begin
342 --
343 -- Delete ownerships if applicable
344 delete_app_ownerships
345 ('INPUT_VALUE_ID'
346 ,p_rec.input_value_id
347 ,p_datetrack_mode
348 );
349 --
350 pay_ivl_rkd.after_delete
351 (p_effective_date
352 => p_effective_date
353 ,p_datetrack_mode
354 => p_datetrack_mode
355 ,p_validation_start_date
356 => p_validation_start_date
360 => p_rec.input_value_id
357 ,p_validation_end_date
358 => p_validation_end_date
359 ,p_input_value_id
361 ,p_effective_start_date
362 => p_rec.effective_start_date
363 ,p_effective_end_date
364 => p_rec.effective_end_date
365 ,p_effective_start_date_o
366 => pay_ivl_shd.g_old_rec.effective_start_date
367 ,p_effective_end_date_o
368 => pay_ivl_shd.g_old_rec.effective_end_date
369 ,p_element_type_id_o
370 => pay_ivl_shd.g_old_rec.element_type_id
371 ,p_lookup_type_o
372 => pay_ivl_shd.g_old_rec.lookup_type
373 ,p_business_group_id_o
374 => pay_ivl_shd.g_old_rec.business_group_id
375 ,p_legislation_code_o
376 => pay_ivl_shd.g_old_rec.legislation_code
377 ,p_formula_id_o
378 => pay_ivl_shd.g_old_rec.formula_id
379 ,p_value_set_id_o
380 => pay_ivl_shd.g_old_rec.value_set_id
381 ,p_display_sequence_o
382 => pay_ivl_shd.g_old_rec.display_sequence
383 ,p_generate_db_items_flag_o
384 => pay_ivl_shd.g_old_rec.generate_db_items_flag
385 ,p_hot_default_flag_o
386 => pay_ivl_shd.g_old_rec.hot_default_flag
387 ,p_mandatory_flag_o
388 => pay_ivl_shd.g_old_rec.mandatory_flag
389 ,p_name_o
390 => pay_ivl_shd.g_old_rec.name
391 ,p_uom_o
392 => pay_ivl_shd.g_old_rec.uom
393 ,p_default_value_o
394 => pay_ivl_shd.g_old_rec.default_value
395 ,p_legislation_subgroup_o
396 => pay_ivl_shd.g_old_rec.legislation_subgroup
397 ,p_max_value_o
398 => pay_ivl_shd.g_old_rec.max_value
399 ,p_min_value_o
400 => pay_ivl_shd.g_old_rec.min_value
401 ,p_warning_or_error_o
402 => pay_ivl_shd.g_old_rec.warning_or_error
403 ,p_object_version_number_o
404 => pay_ivl_shd.g_old_rec.object_version_number
405 );
406 --
407 exception
408 --
409 when hr_api.cannot_find_prog_unit then
410 --
411 hr_api.cannot_find_prog_unit_error
412 (p_module_name => 'PAY_INPUT_VALUES_F'
413 ,p_hook_type => 'AD');
414 --
415 end;
416 --
417 hr_utility.set_location(' Leaving:'||l_proc, 10);
418 End post_delete;
419 --
420 -- ----------------------------------------------------------------------------
421 -- |---------------------------------< del >----------------------------------|
422 -- ----------------------------------------------------------------------------
423 Procedure del
424 (p_effective_date in date
425 ,p_datetrack_mode in varchar2
426 ,p_rec in out nocopy pay_ivl_shd.g_rec_type
427 ,p_balance_feeds_warning out nocopy boolean
428 ) is
429 --
430 l_proc varchar2(72) := g_package||'del';
431 l_validation_start_date date;
432 l_validation_end_date date;
433 --
434 Begin
435 hr_utility.set_location('Entering:'||l_proc, 5);
436 --
437 -- Ensure that the DateTrack delete mode is valid
438 --
439 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
440
441
442 dt_api.validate_dt_mode
443 (p_effective_date => p_effective_date
444 ,p_datetrack_mode => p_datetrack_mode
445 ,p_base_table_name => 'pay_input_values_f'
446 ,p_base_key_column => 'input_value_id'
447 ,p_base_key_value => p_rec.input_value_id
448 ,p_parent_table_name1 => 'pay_element_types_f'
449 ,p_parent_key_column1 => 'element_type_id'
450 ,p_parent_key_value1 => pay_ivl_shd.g_old_rec.element_type_id
451 ,p_enforce_foreign_locking => false
452 ,p_validation_start_date => l_validation_start_date
453 ,p_validation_end_date => l_validation_end_date
454 );
455
456
457
458 -------------------------------------------------------------------------------
459 -- cascade deletion to balance feeds,link input values and db items
460 -------------------------------------------------------------------------------
461
462 p_balance_feeds_warning := TRUE;
463
464 hr_balance_feeds.del_bf_input_value ( p_rec.input_value_id,
465 p_datetrack_mode,
466 l_validation_start_date,
467 l_validation_end_date );
468
469 pay_link_input_values_pkg.parent_deleted (p_rec.input_value_id,
470 p_effective_date,
471 l_validation_start_date,
472 l_validation_end_date,
473 p_datetrack_mode,
474 'PAY_INPUT_VALUES_F');
475
476
477 --
478 -- delete DB items if zapping the input value
479 --
480 If p_datetrack_mode = hr_api.g_zap Then
481 --
482 hrdyndbi.delete_input_value_dict (p_rec.input_value_id);
483 --
484 End If;
485 -------------------------------------------------------------------------------
486
487 --
488 -- We must lock the row which we need to delete.
489 --
490 pay_ivl_shd.lck
491 (p_effective_date => p_effective_date
492 ,p_datetrack_mode => p_datetrack_mode
493 ,p_input_value_id => p_rec.input_value_id
494 ,p_object_version_number => p_rec.object_version_number
495 ,p_validation_start_date => l_validation_start_date
496 ,p_validation_end_date => l_validation_end_date
497 );
498
499
500
501 --
502 -- Call the supporting delete validate operation
503 --
504 pay_ivl_bus.delete_validate
505 (p_rec => p_rec
506 ,p_effective_date => p_effective_date
507 ,p_datetrack_mode => p_datetrack_mode
508 ,p_validation_start_date => l_validation_start_date
509 ,p_validation_end_date => l_validation_end_date
510 );
511 --
512 -- Call to raise any errors on multi-message list
513 hr_multi_message.end_validation_set;
514 --
515 -- Call the supporting pre-delete operation
516 --
517 pay_ivl_del.pre_delete
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 -- Delete the row.
526 --
527 pay_ivl_del.delete_dml
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 -- Call the supporting post-delete operation
535 --
536 pay_ivl_del.post_delete
537 (p_rec => p_rec
538 ,p_effective_date => p_effective_date
539 ,p_datetrack_mode => p_datetrack_mode
540 ,p_validation_start_date => l_validation_start_date
541 ,p_validation_end_date => l_validation_end_date
542 );
543
544 --
545 -- Call to raise any errors on multi-message list
546 hr_multi_message.end_validation_set;
547 --
548 hr_utility.set_location(' Leaving:'||l_proc, 5);
549 End del;
550 --
551 -- ----------------------------------------------------------------------------
552 -- |--------------------------------< del >-----------------------------------|
553 -- ----------------------------------------------------------------------------
554 Procedure del
555 (p_effective_date in date
556 ,p_datetrack_mode in varchar2
557 ,p_input_value_id in number
558 ,p_object_version_number in out nocopy number
559 ,p_effective_start_date out nocopy date
560 ,p_effective_end_date out nocopy date
561 ,p_balance_feeds_warning out nocopy boolean
562 ) is
563 --
564 l_rec pay_ivl_shd.g_rec_type;
565 l_proc varchar2(72) := g_package||'del';
566 --
567 Begin
571 -- convert the arguments into the record structure.
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 -- As the delete procedure accepts a plsql record structure we do need to
572 -- We don't need to call the supplied conversion argument routine as we
573 -- only need a few attributes.
574 --
575 l_rec.input_value_id := p_input_value_id;
576 l_rec.object_version_number := p_object_version_number;
577 --
578 -- Having converted the arguments into the pay_ivl_rec
579 -- plsql record structure we must call the corresponding entity
580 -- business process
581 --
582 pay_ivl_del.del
583 (p_effective_date
584 ,p_datetrack_mode
585 ,l_rec
586 ,p_balance_feeds_warning
587 );
588 --
589 --
590 -- Set the out arguments
591 --
592 p_object_version_number := l_rec.object_version_number;
593 p_effective_start_date := l_rec.effective_start_date;
594 p_effective_end_date := l_rec.effective_end_date;
595 --
596 if p_datetrack_mode = hr_api.g_zap then
597 pay_ivt_del.del_tl
598 (p_input_value_id => p_input_value_id);
599 end if;
600 --
601 hr_utility.set_location(' Leaving:'||l_proc, 10);
602 End del;
603 --
604 end pay_ivl_del;
[Home] [Help]