[Home] [Help]
PACKAGE BODY: APPS.PER_CTC_DEL
Source
1 Package Body per_ctc_del as
2 /* $Header: pectcrhi.pkb 120.1 2010/11/29 06:24:00 ghshanka ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ctc_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 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 Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure dt_delete_dml
54 (p_rec in out nocopy per_ctc_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 --
67 -- Delete the where the effective start date is equal
68 -- to the validation end date.
69 --
70 delete from per_contracts_f
71 where contract_id = p_rec.contract_id
72 and effective_start_date = p_validation_start_date;
73 --
74 Else
75 hr_utility.set_location(l_proc, 15);
76 --
77 -- Delete the row(s) where the effective start date is greater than
78 -- or equal to the validation start date.
79 --
80 delete from per_contracts_f
81 where contract_id = p_rec.contract_id
82 and effective_start_date >= p_validation_start_date;
83 --
84 End If;
85 --
86 hr_utility.set_location(' Leaving:'||l_proc, 20);
87 --
88 Exception
89 When Others Then
90 Raise;
91 End dt_delete_dml;
92 --
93 -- ----------------------------------------------------------------------------
94 -- |------------------------------< delete_dml >------------------------------|
95 -- ----------------------------------------------------------------------------
96 Procedure delete_dml
97 (p_rec in out nocopy per_ctc_shd.g_rec_type,
98 p_effective_date in date,
99 p_datetrack_mode in varchar2,
100 p_validation_start_date in date,
101
102 p_validation_end_date in date) 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 dt_delete_dml(p_rec => p_rec,
110 p_effective_date => p_effective_date,
111 p_datetrack_mode => p_datetrack_mode,
112 p_validation_start_date => p_validation_start_date,
113 p_validation_end_date => p_validation_end_date);
114 --
115 hr_utility.set_location(' Leaving:'||l_proc, 10);
116 End delete_dml;
117 --
118 -- ----------------------------------------------------------------------------
119 -- |----------------------------< dt_pre_delete >-----------------------------|
120 -- ----------------------------------------------------------------------------
121 -- {Start Of Comments}
122 --
123 -- Description:
124 -- The dt_pre_delete process controls the execution of dml
125 -- for the datetrack modes: DELETE, FUTURE_CHANGE
126 -- and DELETE_NEXT_CHANGE only.
127 --
128 -- Prerequisites:
129 -- This is an internal procedure which is called from the pre_delete
130 -- procedure.
131 --
132 -- In Parameters:
133 -- A Pl/Sql record structre.
134 --
135 -- Post Success:
136 -- Processing continues.
137 --
138 -- Post Failure:
139 -- If an error has occurred, an error message and exception will be raised
140 -- but not handled.
141 --
142 -- Developer Implementation Notes:
143 -- This is an internal procedure which is required by Datetrack. Don't
144 -- remove or modify.
145 --
146 -- Access Status:
147 -- Internal Row Handler Use Only.
148 --
149 -- {End Of Comments}
150 -- ----------------------------------------------------------------------------
151 Procedure dt_pre_delete
152 (p_rec in out nocopy per_ctc_shd.g_rec_type,
153 p_effective_date in date,
154
155 p_datetrack_mode in varchar2,
156 p_validation_start_date in date,
157 p_validation_end_date in date) is
158 --
159 l_proc varchar2(72) := g_package||'dt_pre_delete';
160 --
161 Begin
162 hr_utility.set_location('Entering:'||l_proc, 5);
163 --
164 If (p_datetrack_mode <> 'ZAP') then
165 --
166 p_rec.effective_start_date := per_ctc_shd.g_old_rec.effective_start_date;
167 --
168 If (p_datetrack_mode = 'DELETE') then
169 p_rec.effective_end_date := p_validation_start_date - 1;
170 Else
171 p_rec.effective_end_date := p_validation_end_date;
172 End If;
173 --
174 -- Update the current effective end date record
175 --
176 per_ctc_shd.upd_effective_end_date
177 (p_effective_date => p_effective_date,
178 p_base_key_value => p_rec.contract_id,
179 p_new_effective_end_date => p_rec.effective_end_date,
180 p_validation_start_date => p_validation_start_date,
181
182 p_validation_end_date => p_validation_end_date,
183 p_object_version_number => p_rec.object_version_number);
184 Else
185 p_rec.effective_start_date := null;
186 p_rec.effective_end_date := null;
187 End If;
188 hr_utility.set_location(' Leaving:'||l_proc, 10);
189 End dt_pre_delete;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |------------------------------< pre_delete >------------------------------|
193 -- ----------------------------------------------------------------------------
194 -- {Start Of Comments}
195 --
196 -- Description:
197 -- This private procedure contains any processing which is required before
198 -- the delete dml.
199 --
200 -- Prerequisites:
201 -- This is an internal procedure which is called from the del procedure.
202 --
203 -- In Parameters:
204 -- A Pl/Sql record structre.
205 --
206 -- Post Success:
207 -- Processing continues.
208 --
209 -- Post Failure:
210 -- If an error has occurred, an error message and exception will be raised
211 -- but not handled.
212 --
213 -- Developer Implementation Notes:
214 -- Any pre-processing required before the delete dml is issued should be
215 -- coded within this procedure. It is important to note that any 3rd party
216 -- maintenance should be reviewed before placing in this procedure. The call
217 -- to the dt_delete_dml procedure should NOT be removed.
218 --
219 -- Access Status:
220 -- Internal Row Handler Use Only.
221 --
222 -- {End Of Comments}
223 -- ----------------------------------------------------------------------------
224 Procedure pre_delete
225 (p_rec in out nocopy per_ctc_shd.g_rec_type,
226 p_effective_date in date,
227 p_datetrack_mode in varchar2,
228 p_validation_start_date in date,
229 p_validation_end_date in date) is
230 --
231 l_proc varchar2(72) := g_package||'pre_delete';
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 --
236 --
237 --
238 dt_pre_delete
239 (p_rec => p_rec,
240 p_effective_date => p_effective_date,
241 p_datetrack_mode => p_datetrack_mode,
242 p_validation_start_date => p_validation_start_date,
243 p_validation_end_date => p_validation_end_date);
244 --
245
246 hr_utility.set_location(' Leaving:'||l_proc, 10);
247 End pre_delete;
248 --
249 -- ----------------------------------------------------------------------------
250 -- |-----------------------------< post_delete >------------------------------|
251 -- ----------------------------------------------------------------------------
252 -- {Start Of Comments}
253 --
254 -- Description:
255 -- This private procedure contains any processing which is required after the
256 -- delete dml.
257 --
258 -- Prerequisites:
259 -- This is an internal procedure which is called from the del procedure.
260 --
261 -- In Parameters:
262 -- A Pl/Sql record structre.
263 --
264 -- Post Success:
265 -- Processing continues.
266 --
267 -- Post Failure:
268 -- If an error has occurred, an error message and exception will be raised
269 -- but not handled.
270 --
271 -- Developer Implementation Notes:
272 -- Any post-processing required after the delete dml is issued should be
273 -- coded within this procedure. It is important to note that any 3rd party
274 -- maintenance should be reviewed before placing in this procedure.
275 --
276 -- Access Status:
277 -- Internal Row Handler Use Only.
278 --
279 -- {End Of Comments}
280 -- ----------------------------------------------------------------------------
281 Procedure post_delete
282 (p_rec in per_ctc_shd.g_rec_type,
283 p_effective_date in date,
284 p_datetrack_mode in varchar2,
285 p_validation_start_date in date,
286 p_validation_end_date in date) is
287 --
288 l_proc varchar2(72) := g_package||'post_delete';
289 --
290 Begin
291 hr_utility.set_location('Entering:'||l_proc, 5);
292 --
293 -- Start of API User Hook for post_delete.
294 --
295 begin
296 --
297 per_ctc_rkd.after_delete
298 (
299 p_effective_date => p_effective_date,
300 p_datetrack_mode => p_datetrack_mode,
301 p_validation_start_date => p_validation_start_date,
302 p_validation_end_date => p_validation_start_date,
303 p_contract_id => p_rec.contract_id,
304 p_effective_start_date => p_rec.effective_start_date,
305 p_effective_end_date => p_rec.effective_end_date,
306 p_object_version_number => p_rec.object_version_number,
307 p_effective_start_date_o => per_ctc_shd.g_old_rec.effective_start_date,
308 p_effective_end_date_o => per_ctc_shd.g_old_rec.effective_end_date,
309 p_business_group_id_o => per_ctc_shd.g_old_rec.business_group_id,
310 p_person_id_o => per_ctc_shd.g_old_rec.person_id,
311 p_reference_o => per_ctc_shd.g_old_rec.reference,
312 p_type_o => per_ctc_shd.g_old_rec.type,
313 p_status_o => per_ctc_shd.g_old_rec.status,
314 p_status_reason_o => per_ctc_shd.g_old_rec.status_reason,
315 p_doc_status_o => per_ctc_shd.g_old_rec.doc_status,
316 p_doc_status_change_date_o => per_ctc_shd.g_old_rec.doc_status_change_date,
317 p_description_o => per_ctc_shd.g_old_rec.description,
318 p_duration_o => per_ctc_shd.g_old_rec.duration,
319 p_duration_units_o => per_ctc_shd.g_old_rec.duration_units,
320 p_contractual_job_title_o => per_ctc_shd.g_old_rec.contractual_job_title,
321 p_parties_o => per_ctc_shd.g_old_rec.parties,
322 p_start_reason_o => per_ctc_shd.g_old_rec.start_reason,
323 p_end_reason_o => per_ctc_shd.g_old_rec.end_reason ,
324 p_number_of_extensions_o => per_ctc_shd.g_old_rec.number_of_extensions,
325 p_extension_reason_o => per_ctc_shd.g_old_rec.extension_reason,
326 p_extension_period_o => per_ctc_shd.g_old_rec.extension_period,
327 p_extension_period_units_o => per_ctc_shd.g_old_rec.extension_period_units,
328 p_ctr_information_category_o => per_ctc_shd.g_old_rec.ctr_information_category,
329 p_ctr_information1_o => per_ctc_shd.g_old_rec.ctr_information1,
330 p_ctr_information2_o => per_ctc_shd.g_old_rec.ctr_information2,
331 p_ctr_information3_o => per_ctc_shd.g_old_rec.ctr_information3,
332 p_ctr_information4_o => per_ctc_shd.g_old_rec.ctr_information4,
333 p_ctr_information5_o => per_ctc_shd.g_old_rec.ctr_information5,
334 p_ctr_information6_o => per_ctc_shd.g_old_rec.ctr_information6,
335 p_ctr_information7_o => per_ctc_shd.g_old_rec.ctr_information7,
336 p_ctr_information8_o => per_ctc_shd.g_old_rec.ctr_information8,
337 p_ctr_information9_o => per_ctc_shd.g_old_rec.ctr_information9,
338 p_ctr_information10_o => per_ctc_shd.g_old_rec.ctr_information10,
339 p_ctr_information11_o => per_ctc_shd.g_old_rec.ctr_information11,
340 p_ctr_information12_o => per_ctc_shd.g_old_rec.ctr_information12,
341 p_ctr_information13_o => per_ctc_shd.g_old_rec.ctr_information13,
342 p_ctr_information14_o => per_ctc_shd.g_old_rec.ctr_information14,
343 p_ctr_information15_o => per_ctc_shd.g_old_rec.ctr_information15,
344 p_ctr_information16_o => per_ctc_shd.g_old_rec.ctr_information16,
345 p_ctr_information17_o => per_ctc_shd.g_old_rec.ctr_information17,
346 p_ctr_information18_o => per_ctc_shd.g_old_rec.ctr_information18,
347 p_ctr_information19_o => per_ctc_shd.g_old_rec.ctr_information19,
348 p_ctr_information20_o => per_ctc_shd.g_old_rec.ctr_information20,
349 p_attribute_category_o => per_ctc_shd.g_old_rec.attribute_category,
350 p_attribute1_o => per_ctc_shd.g_old_rec.attribute1,
351 p_attribute2_o => per_ctc_shd.g_old_rec.attribute2,
352 p_attribute3_o => per_ctc_shd.g_old_rec.attribute3,
353 p_attribute4_o => per_ctc_shd.g_old_rec.attribute4,
354 p_attribute5_o => per_ctc_shd.g_old_rec.attribute5,
355 p_attribute6_o => per_ctc_shd.g_old_rec.attribute6,
356 p_attribute7_o => per_ctc_shd.g_old_rec.attribute7,
357 p_attribute8_o => per_ctc_shd.g_old_rec.attribute8,
358 p_attribute9_o => per_ctc_shd.g_old_rec.attribute9,
359 p_attribute10_o => per_ctc_shd.g_old_rec.attribute10,
360 p_attribute11_o => per_ctc_shd.g_old_rec.attribute11,
361 p_attribute12_o => per_ctc_shd.g_old_rec.attribute12,
362 p_attribute13_o => per_ctc_shd.g_old_rec.attribute13,
363 p_attribute14_o => per_ctc_shd.g_old_rec.attribute14,
364 p_attribute15_o => per_ctc_shd.g_old_rec.attribute15,
365 p_attribute16_o => per_ctc_shd.g_old_rec.attribute16,
366 p_attribute17_o => per_ctc_shd.g_old_rec.attribute17,
367 p_attribute18_o => per_ctc_shd.g_old_rec.attribute18,
368 p_attribute19_o => per_ctc_shd.g_old_rec.attribute19,
369 p_attribute20_o => per_ctc_shd.g_old_rec.attribute20,
370 p_object_version_number_o => per_ctc_shd.g_old_rec.object_version_number
371 );
372 --
373 exception
374 --
375 when hr_api.cannot_find_prog_unit then
376 --
377 hr_api.cannot_find_prog_unit_error
378 (p_module_name => 'per_contracts_f'
379 ,p_hook_type => 'AD');
380 --
381 end;
382 --
383 -- End of API User Hook for post_delete.
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 10);
386 End post_delete;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |---------------------------------< del >----------------------------------|
390
391 -- ----------------------------------------------------------------------------
392 Procedure del
393 (
394 p_rec in out nocopy per_ctc_shd.g_rec_type,
395 p_effective_date in date,
396 p_datetrack_mode in varchar2
397 ) is
398 --
399 l_proc varchar2(72) := g_package||'del';
400 l_validation_start_date date;
401 l_validation_end_date date;
402 --
403 Begin
404 hr_utility.set_location('Entering:'||l_proc, 5);
405 --
406 -- Ensure that the DateTrack delete mode is valid
407 --
408 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
409 --
410 -- We must lock the row which we need to delete.
411 --
412 per_ctc_shd.lck
413 (p_effective_date => p_effective_date,
414 p_datetrack_mode => p_datetrack_mode,
415 p_contract_id => p_rec.contract_id,
416 p_object_version_number => p_rec.object_version_number,
417 p_validation_start_date => l_validation_start_date,
418 p_validation_end_date => l_validation_end_date);
419 --
420 -- Call the supporting delete validate operation
421 --
422 per_ctc_bus.delete_validate
423 (p_rec => p_rec,
424 p_effective_date => p_effective_date,
425 p_datetrack_mode => p_datetrack_mode,
426 p_validation_start_date => l_validation_start_date,
427 p_validation_end_date => l_validation_end_date);
428 --
429 -- Call the supporting pre-delete operation
430 --
431 pre_delete
432 (p_rec => p_rec,
433 p_effective_date => p_effective_date,
434 p_datetrack_mode => p_datetrack_mode,
435 p_validation_start_date => l_validation_start_date,
436 p_validation_end_date => l_validation_end_date);
437 --
438 -- Delete the row.
439 --
440 delete_dml
441 (p_rec => p_rec,
442 p_effective_date => p_effective_date,
443
444 p_datetrack_mode => p_datetrack_mode,
445 p_validation_start_date => l_validation_start_date,
446 p_validation_end_date => l_validation_end_date);
447 --
448 -- Call the supporting post-delete operation
449 --
450 post_delete
451 (p_rec => p_rec,
452 p_effective_date => p_effective_date,
453 p_datetrack_mode => p_datetrack_mode,
454 p_validation_start_date => l_validation_start_date,
455 p_validation_end_date => l_validation_end_date);
456 End del;
457 --
458 -- ----------------------------------------------------------------------------
459 -- |---------------------------------< del >----------------------------------|
460 -- ----------------------------------------------------------------------------
461 Procedure del
462 (
463 p_contract_id in number,
464 p_effective_start_date out nocopy date,
465 p_effective_end_date out nocopy date,
466 p_object_version_number in out nocopy number,
467 p_effective_date in date,
468 p_datetrack_mode in varchar2
469 ) is
470 --
471 l_rec per_ctc_shd.g_rec_type;
472 l_proc varchar2(72) := g_package||'del';
473 --
474 Begin
475 hr_utility.set_location('Entering:'||l_proc, 5);
476 --
477 -- As the delete procedure accepts a plsql record structure we do need to
478 -- convert the arguments into the record structure.
479 -- We don't need to call the supplied conversion argument routine as we
480 -- only need a few attributes.
481 --
482 l_rec.contract_id := p_contract_id;
483 l_rec.object_version_number := p_object_version_number;
484 --
485 -- Having converted the arguments into the per_ctc_rec
486 -- plsql record structure we must call the corresponding entity
487 -- business process
488 --
489 del(l_rec, p_effective_date, p_datetrack_mode);
490 --
491 -- Set the out arguments
492 --
493 p_object_version_number := l_rec.object_version_number;
494 p_effective_start_date := l_rec.effective_start_date;
495 p_effective_end_date := l_rec.effective_end_date;
496 --
497 hr_utility.set_location(' Leaving:'||l_proc, 10);
498 End del;
499 --
500 -- ----------------------------------------------------------------------------
501 -- |-----------------------< delete_row >-------------------------------------|
502 -- ----------------------------------------------------------------------------
503 --
504 -- This is called from maintain_contracts, used when hire date or application
505 -- date changes.
506 --
507 procedure delete_row
508 (
509 p_contract_id in per_contracts_f.contract_id%TYPE,
510 p_effective_date in date,
511 p_object_version_number in per_contracts_f.object_version_number%TYPE
512 ) is
513 --
514 l_proc varchar2(72) := g_package||'delete_row';
515 --
516 begin
517 --
518 hr_utility.set_location('Entering:'||l_proc, 5);
519 --
520 -- mandatory argument checking
521 --
522 hr_api.mandatory_arg_error
523 (p_api_name => l_proc,
524 p_argument => 'contract_id',
525 p_argument_value => p_contract_id);
526
527 hr_api.mandatory_arg_error
528 (p_api_name => l_proc,
529 p_argument => 'effective_date',
530 p_argument_value => p_effective_date);
531
532 hr_api.mandatory_arg_error
533 (p_api_name => l_proc,
534 p_argument => 'object_version_number',
535 p_argument_value => p_object_version_number);
536 --
537 --
538 -- We must lock the row which we need to delete.
539 --
540 per_ctc_shd.lock_record
541 (p_contract_id,
542 p_effective_date,
543 p_object_version_number
544 );
545 --
546 -- delete row
547 --
548 delete from per_contracts_f
549 where contract_id = p_contract_id and
550 p_effective_date between effective_start_date
551 and effective_end_date;
552 --
553 exception when others then
554 --
555 -- An unhandled or unexpected error has occurred which
556 -- we must report
557 --
558 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
559 hr_utility.set_message_token('PROCEDURE', l_proc);
560 hr_utility.set_message_token('STEP','5');
561 hr_utility.raise_error;
562 --
563 end delete_row;
564 --
565 end per_ctc_del;