1 Package Body pay_prt_del as
2 /* $Header: pyprtrhi.pkb 115.13 2003/02/28 15:52:21 alogue noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_prt_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_prt_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 -- Delete the where the effective start date is equal
68 -- to the validation end date.
69 --
70 delete from pay_run_types_f
71 where run_type_id = p_rec.run_type_id
72 and effective_start_date = p_validation_start_date;
73 --
74 Else
75 --
76 -- Delete the row(s) where the effective start date is greater than
77 -- or equal to the validation start date.
78 --
79 delete from pay_run_types_f
80 where run_type_id = p_rec.run_type_id
81 and effective_start_date >= p_validation_start_date;
82 --
83 End If;
84 --
85 hr_utility.set_location(' Leaving:'||l_proc, 20);
86 --
87 End dt_delete_dml;
88 -- ----------------------------------------------------------------------------
89 -- ----------------------< delete_app_ownerships >----------------------------|
90 -- ----------------------------------------------------------------------------
91 --
92 -- Description:
93 -- Deletes row(s) from hr_application_ownerships depending on the mode that
94 -- the row handler has been called in.
95 --
96 -- ----------------------------------------------------------------------------
97 PROCEDURE delete_app_ownerships(p_pk_column IN varchar2
98 ,p_pk_value IN varchar2
99 ,p_datetrack_mode IN varchar2) IS
100 --
101 BEGIN
102 --
103 IF ((hr_startup_data_api_support.return_startup_mode
104 IN ('STARTUP','GENERIC')) AND
105 p_datetrack_mode = hr_api.g_zap) THEN
106 --
107 DELETE FROM hr_application_ownerships
108 WHERE key_name = p_pk_column
109 AND key_value = p_pk_value;
110 --
111 END IF;
112 END delete_app_ownerships;
113 --
114 -- ----------------------------------------------------------------------------
115 -- ----------------------< delete_app_ownerships >----------------------------|
116 -- ----------------------------------------------------------------------------
117 PROCEDURE delete_app_ownerships(p_pk_column IN varchar2
118 ,p_pk_value IN number
119 ,p_datetrack_mode IN varchar2) IS
120 --
121 BEGIN
122 delete_app_ownerships(p_pk_column, to_char(p_pk_value), p_datetrack_mode);
123 END delete_app_ownerships;
124 --
125 -- ----------------------------------------------------------------------------
126 -- |------------------------------< delete_dml >------------------------------|
127 -- ----------------------------------------------------------------------------
128 Procedure delete_dml
129 (p_rec in out nocopy pay_prt_shd.g_rec_type
130 ,p_effective_date in date
131 ,p_datetrack_mode in varchar2
132 ,p_validation_start_date in date
133 ,p_validation_end_date in date
134 ) is
135 --
136 l_proc varchar2(72) := g_package||'delete_dml';
137 --
138 Begin
139 hr_utility.set_location('Entering:'||l_proc, 5);
140 --
141 pay_prt_del.dt_delete_dml
142 (p_rec => p_rec
143 ,p_effective_date => p_effective_date
144 ,p_datetrack_mode => p_datetrack_mode
145 ,p_validation_start_date => p_validation_start_date
146 ,p_validation_end_date => p_validation_end_date
147 );
148 --
149 hr_utility.set_location(' Leaving:'||l_proc, 10);
150 End delete_dml;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |----------------------------< dt_pre_delete >-----------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- The dt_pre_delete process controls the execution of dml
159 -- for the datetrack modes: DELETE, FUTURE_CHANGE
160 -- and DELETE_NEXT_CHANGE only.
161 --
162 -- Prerequisites:
163 -- This is an internal procedure which is called from the pre_delete
164 -- procedure.
165 --
166 -- In Parameters:
167 -- A Pl/Sql record structure.
168 --
169 -- Post Success:
170 -- Processing continues.
171 --
172 -- Post Failure:
173 -- If an error has occurred, an error message and exception will be raised
174 -- but not handled.
175 --
176 -- Developer Implementation Notes:
177 -- This is an internal procedure which is required by Datetrack. Don't
178 -- remove or modify.
179 --
180 -- Access Status:
181 -- Internal Row Handler Use Only.
182 --
183 -- {End Of Comments}
184 -- ----------------------------------------------------------------------------
185 Procedure dt_pre_delete
186 (p_rec in out nocopy pay_prt_shd.g_rec_type
187 ,p_effective_date in date
188 ,p_datetrack_mode in varchar2
189 ,p_validation_start_date in date
190 ,p_validation_end_date in date
191 ) is
192 --
193 l_proc varchar2(72) := g_package||'dt_pre_delete';
194 --
195 Begin
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 --
198 If (p_datetrack_mode <> hr_api.g_zap) then
199 --
200 p_rec.effective_start_date
201 := pay_prt_shd.g_old_rec.effective_start_date;
202 --
203 If (p_datetrack_mode = hr_api.g_delete) then
204 p_rec.effective_end_date := p_validation_start_date - 1;
205 Else
206 p_rec.effective_end_date := p_validation_end_date;
207 End If;
208 --
209 -- Update the current effective end date record
210 --
211 pay_prt_shd.upd_effective_end_date
212 (p_effective_date => p_effective_date
213 ,p_base_key_value => p_rec.run_type_id
214 ,p_new_effective_end_date => p_rec.effective_end_date
215 ,p_validation_start_date => p_validation_start_date
216 ,p_validation_end_date => p_validation_end_date
217 ,p_object_version_number => p_rec.object_version_number
218 );
219 Else
220 p_rec.effective_start_date := null;
221 p_rec.effective_end_date := null;
222 End If;
223 hr_utility.set_location(' Leaving:'||l_proc, 10);
224 End dt_pre_delete;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |------------------------------< pre_delete >------------------------------|
228 -- ----------------------------------------------------------------------------
229 -- {Start Of Comments}
230 --
231 -- Description:
232 -- This private procedure contains any processing which is required before
233 -- the delete dml.
234 --
235 -- Prerequisites:
236 -- This is an internal procedure which is called from the del procedure.
237 --
238 -- In Parameters:
239 -- A Pl/Sql record structure.
240 --
241 -- Post Success:
242 -- Processing continues.
243 --
244 -- Post Failure:
245 -- If an error has occurred, an error message and exception will be raised
246 -- but not handled.
247 --
248 -- Developer Implementation Notes:
249 -- Any pre-processing required before the delete dml is issued should be
250 -- coded within this procedure. It is important to note that any 3rd party
251 -- maintenance should be reviewed before placing in this procedure. The call
252 -- to the dt_delete_dml procedure should NOT be removed.
253 --
254 -- Access Status:
255 -- Internal Row Handler Use Only.
256 --
257 -- {End Of Comments}
258 -- ----------------------------------------------------------------------------
259 Procedure pre_delete
260 (p_rec in out nocopy pay_prt_shd.g_rec_type
261 ,p_effective_date in date
262 ,p_datetrack_mode in varchar2
263 ,p_validation_start_date in date
264 ,p_validation_end_date in date
265 ) is
266 --
267 l_proc varchar2(72) := g_package||'pre_delete';
268 --
269 --
270 --
271 Begin
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 --
275 --
276 pay_prt_del.dt_pre_delete
277 (p_rec => p_rec
278 ,p_effective_date => p_effective_date
279 ,p_datetrack_mode => p_datetrack_mode
280 ,p_validation_start_date => p_validation_start_date
281 ,p_validation_end_date => p_validation_end_date
282 );
283 --
284 hr_utility.set_location(' Leaving:'||l_proc, 10);
285 End pre_delete;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |----------------------------< post_delete >-------------------------------|
289 -- ----------------------------------------------------------------------------
290 -- {Start Of Comments}
291 --
292 -- Description:
293 -- This private procedure contains any processing which is required after the
294 -- delete dml.
295 --
296 -- Prerequisites:
297 -- This is an internal procedure which is called from the del procedure.
298 --
299 -- In Parameters:
300 -- A Pl/Sql record structure.
301 --
302 -- Post Success:
303 -- Processing continues.
304 --
305 -- Post Failure:
306 -- If an error has occurred, an error message and exception will be raised
307 -- but not handled.
308 --
309 -- Developer Implementation Notes:
310 -- Any post-processing required after the delete dml is issued should be
311 -- coded within this procedure. It is important to note that any 3rd party
312 -- maintenance should be reviewed before placing in this procedure.
313 --
314 -- Access Status:
315 -- Internal Row Handler Use Only.
316 --
317 -- {End Of Comments}
318 -- ----------------------------------------------------------------------------
319 Procedure post_delete
320 (p_rec in pay_prt_shd.g_rec_type
321 ,p_effective_date in date
322 ,p_datetrack_mode in varchar2
323 ,p_validation_start_date in date
324 ,p_validation_end_date in date
325 ) is
326 --
327 l_proc varchar2(72) := g_package||'post_delete';
328 --
329 Begin
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 begin
332 --
333 -- RET added call to delete ownerships
334 --
335 -- Delete ownerships if applicable
336 --
337 delete_app_ownerships
338 ('RUN_TYPE_ID'
339 ,p_rec.run_type_id
340 ,p_datetrack_mode);
341 --
342 pay_prt_rkd.after_delete
343 (p_effective_date
344 => p_effective_date
345 ,p_datetrack_mode
346 => p_datetrack_mode
347 ,p_validation_start_date
348 => p_validation_start_date
349 ,p_validation_end_date
350 => p_validation_end_date
351 ,p_run_type_id
352 => p_rec.run_type_id
353 ,p_effective_start_date
354 => p_rec.effective_start_date
355 ,p_effective_end_date
356 => p_rec.effective_end_date
357 ,p_object_version_number
358 => p_rec.object_version_number
359 ,p_run_type_name_o
360 => pay_prt_shd.g_old_rec.run_type_name
361 ,p_run_method_o
362 => pay_prt_shd.g_old_rec.run_method
363 ,p_effective_start_date_o
364 => pay_prt_shd.g_old_rec.effective_start_date
365 ,p_effective_end_date_o
366 => pay_prt_shd.g_old_rec.effective_end_date
367 ,p_business_group_id_o
368 => pay_prt_shd.g_old_rec.business_group_id
369 ,p_legislation_code_o
370 => pay_prt_shd.g_old_rec.legislation_code
371 ,p_shortname_o
372 => pay_prt_shd.g_old_rec.shortname
373 ,p_srs_flag_o
374 => pay_prt_shd.g_old_rec.srs_flag
375 ,p_run_information_category_o
376 => pay_prt_shd.g_old_rec.run_information_category
377 ,p_run_information1_o
378 => pay_prt_shd.g_old_rec.run_information1
379 ,p_run_information2_o
380 => pay_prt_shd.g_old_rec.run_information2
381 ,p_run_information3_o
382 => pay_prt_shd.g_old_rec.run_information3
383 ,p_run_information4_o
384 => pay_prt_shd.g_old_rec.run_information4
385 ,p_run_information5_o
386 => pay_prt_shd.g_old_rec.run_information5
387 ,p_run_information6_o
388 => pay_prt_shd.g_old_rec.run_information6
389 ,p_run_information7_o
390 => pay_prt_shd.g_old_rec.run_information7
391 ,p_run_information8_o
392 => pay_prt_shd.g_old_rec.run_information8
393 ,p_run_information9_o
394 => pay_prt_shd.g_old_rec.run_information9
395 ,p_run_information10_o
399 ,p_run_information12_o
396 => pay_prt_shd.g_old_rec.run_information10
397 ,p_run_information11_o
398 => pay_prt_shd.g_old_rec.run_information11
400 => pay_prt_shd.g_old_rec.run_information12
401 ,p_run_information13_o
402 => pay_prt_shd.g_old_rec.run_information13
403 ,p_run_information14_o
404 => pay_prt_shd.g_old_rec.run_information14
405 ,p_run_information15_o
406 => pay_prt_shd.g_old_rec.run_information15
407 ,p_run_information16_o
408 => pay_prt_shd.g_old_rec.run_information16
409 ,p_run_information17_o
410 => pay_prt_shd.g_old_rec.run_information17
411 ,p_run_information18_o
412 => pay_prt_shd.g_old_rec.run_information18
413 ,p_run_information19_o
414 => pay_prt_shd.g_old_rec.run_information19
415 ,p_run_information20_o
416 => pay_prt_shd.g_old_rec.run_information20
417 ,p_run_information21_o
418 => pay_prt_shd.g_old_rec.run_information21
419 ,p_run_information22_o
420 => pay_prt_shd.g_old_rec.run_information22
421 ,p_run_information23_o
422 => pay_prt_shd.g_old_rec.run_information23
423 ,p_run_information24_o
424 => pay_prt_shd.g_old_rec.run_information24
425 ,p_run_information25_o
426 => pay_prt_shd.g_old_rec.run_information25
427 ,p_run_information26_o
428 => pay_prt_shd.g_old_rec.run_information26
429 ,p_run_information27_o
430 => pay_prt_shd.g_old_rec.run_information27
431 ,p_run_information28_o
432 => pay_prt_shd.g_old_rec.run_information28
433 ,p_run_information29_o
434 => pay_prt_shd.g_old_rec.run_information29
435 ,p_run_information30_o
436 => pay_prt_shd.g_old_rec.run_information30
437 ,p_object_version_number_o
438 => pay_prt_shd.g_old_rec.object_version_number
439 );
440 --
441 exception
442 --
443 when hr_api.cannot_find_prog_unit then
444 --
445 hr_api.cannot_find_prog_unit_error
446 (p_module_name => 'PAY_RUN_TYPES_F'
447 ,p_hook_type => 'AD');
448 --
449 end;
450 --
451 hr_utility.set_location(' Leaving:'||l_proc, 10);
452 End post_delete;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |---------------------------------< del >----------------------------------|
456 -- ----------------------------------------------------------------------------
457 Procedure del
458 (p_effective_date in date
459 ,p_datetrack_mode in varchar2
460 ,p_rec in out nocopy pay_prt_shd.g_rec_type
461 ) is
462 --
463 l_proc varchar2(72) := g_package||'del';
464 l_validation_start_date date;
465 l_validation_end_date date;
466 --
467 Begin
468 hr_utility.set_location('Entering:'||l_proc, 5);
469 --
470 -- Ensure that the DateTrack delete mode is valid
471 --
472 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
473 --
474 -- We must lock the row which we need to delete.
475 --
476 pay_prt_shd.lck
477 (p_effective_date => p_effective_date
478 ,p_datetrack_mode => p_datetrack_mode
479 ,p_run_type_id => p_rec.run_type_id
480 ,p_object_version_number => p_rec.object_version_number
481 ,p_validation_start_date => l_validation_start_date
482 ,p_validation_end_date => l_validation_end_date
483 );
484 --
485 -- Call the supporting delete validate operation
486 --
487 pay_prt_bus.delete_validate
488 (p_rec => p_rec
489 ,p_effective_date => p_effective_date
490 ,p_datetrack_mode => p_datetrack_mode
491 ,p_validation_start_date => l_validation_start_date
492 ,p_validation_end_date => l_validation_end_date
493 );
494 --
495 -- Call the supporting pre-delete operation
496 --
497 pay_prt_del.pre_delete
498 (p_rec => p_rec
499 ,p_effective_date => p_effective_date
500 ,p_datetrack_mode => p_datetrack_mode
501 ,p_validation_start_date => l_validation_start_date
502 ,p_validation_end_date => l_validation_end_date
503 );
504 --
505 -- Delete the row.
506 --
507 pay_prt_del.delete_dml
508 (p_rec => p_rec
509 ,p_effective_date => p_effective_date
510 ,p_datetrack_mode => p_datetrack_mode
511 ,p_validation_start_date => l_validation_start_date
512 ,p_validation_end_date => l_validation_end_date
513 );
514 -- Call the supporting post-delete operation
515 --
516 pay_prt_del.post_delete
517 (p_rec => p_rec
518 ,p_effective_date => p_effective_date
519 ,p_datetrack_mode => p_datetrack_mode
520 ,p_validation_start_date => l_validation_start_date
521 ,p_validation_end_date => l_validation_end_date
522 );
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 5);
525 End del;
526 --
527 -- ----------------------------------------------------------------------------
528 -- |--------------------------------< del >-----------------------------------|
529 -- ----------------------------------------------------------------------------
530 Procedure del
531 (p_effective_date in date
532 ,p_datetrack_mode in varchar2
533 ,p_run_type_id in number
534 ,p_object_version_number in out nocopy number
535 ,p_effective_start_date out nocopy date
536 ,p_effective_end_date out nocopy date
537 ) is
538 --
539 l_rec pay_prt_shd.g_rec_type;
540 l_proc varchar2(72) := g_package||'del';
541 --
542 Begin
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 -- As the delete procedure accepts a plsql record structure we do need to
546 -- convert the arguments into the record structure.
547 -- We don't need to call the supplied conversion argument routine as we
548 -- only need a few attributes.
549 --
550 l_rec.run_type_id := p_run_type_id;
551 l_rec.object_version_number := p_object_version_number;
552 --
553 -- Having converted the arguments into the pay_prt_rec
554 -- plsql record structure we must call the corresponding entity
555 -- business process
556 --
557 pay_prt_del.del
558 (p_effective_date
559 ,p_datetrack_mode
560 ,l_rec
561 );
562 --
563 -- Set the out arguments
564 --
565 p_object_version_number := l_rec.object_version_number;
566 p_effective_start_date := l_rec.effective_start_date;
567 p_effective_end_date := l_rec.effective_end_date;
568 --
569 hr_utility.set_location(' Leaving:'||l_proc, 10);
570 End del;
571 --
572 end pay_prt_del;