50: --
51: -- {End Of Comments}
52: -- ----------------------------------------------------------------------------
53: Procedure dt_delete_dml
54: (p_rec in out nocopy per_sps_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
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: per_sps_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 per_spinal_point_steps_f
72: where step_id = p_rec.step_id
73: and effective_start_date = p_validation_start_date;
74: --
75: per_sps_shd.g_api_dml := false; -- Unset the api dml status
76: Else
77: per_sps_shd.g_api_dml := true; -- Set the api dml status
78: --
79: -- Delete the row(s) where the effective start date is greater than
73: and effective_start_date = p_validation_start_date;
74: --
75: per_sps_shd.g_api_dml := false; -- Unset the api dml status
76: Else
77: per_sps_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 per_spinal_point_steps_f
83: where step_id = p_rec.step_id
84: and effective_start_date >= p_validation_start_date;
85: --
86: per_sps_shd.g_api_dml := false; -- Unset the api dml status
87: End If;
88: --
89: hr_utility.set_location(' Leaving:'||l_proc, 20);
90: --
89: hr_utility.set_location(' Leaving:'||l_proc, 20);
90: --
91: Exception
92: When Others Then
93: per_sps_shd.g_api_dml := false; -- Unset the api dml status
94: Raise;
95: --
96: End dt_delete_dml;
97: --
98: -- ----------------------------------------------------------------------------
99: -- |------------------------------< delete_dml >------------------------------|
100: -- ----------------------------------------------------------------------------
101: Procedure delete_dml
102: (p_rec in out nocopy per_sps_shd.g_rec_type
103: ,p_effective_date in date
104: ,p_datetrack_mode in varchar2
105: ,p_validation_start_date in date
106: ,p_validation_end_date in date
155: --
156: -- {End Of Comments}
157: -- ----------------------------------------------------------------------------
158: Procedure dt_pre_delete
159: (p_rec in out nocopy per_sps_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
221: --
222: If (p_datetrack_mode <> hr_api.g_zap) then
223: --
224: p_rec.effective_start_date
225: := per_sps_shd.g_old_rec.effective_start_date;
226: --
227: If (p_datetrack_mode = hr_api.g_delete) then
228: p_rec.effective_end_date := p_validation_start_date - 1;
229: Else
231: End If;
232: --
233: -- Update the current effective end date record
234: --
235: per_sps_shd.upd_effective_end_date
236: (p_effective_date => p_effective_date
237: ,p_base_key_value => p_rec.step_id
238: ,p_new_effective_end_date => p_rec.effective_end_date
239: ,p_validation_start_date => p_validation_start_date
280: --
281: -- {End Of Comments}
282: -- ----------------------------------------------------------------------------
283: Procedure pre_delete
284: (p_rec in out nocopy per_sps_shd.g_rec_type
285: ,p_effective_date in date
286: ,p_datetrack_mode in varchar2
287: ,p_validation_start_date in date
288: ,p_validation_end_date in date
340: --
341: -- {End Of Comments}
342: -- ----------------------------------------------------------------------------
343: Procedure post_delete
344: (p_rec in per_sps_shd.g_rec_type
345: ,p_effective_date in date
346: ,p_datetrack_mode in varchar2
347: ,p_validation_start_date in date
348: ,p_validation_end_date in date
369: => p_rec.effective_start_date
370: ,p_effective_end_date
371: => p_rec.effective_end_date
372: ,p_effective_start_date_o
373: => per_sps_shd.g_old_rec.effective_start_date
374: ,p_effective_end_date_o
375: => per_sps_shd.g_old_rec.effective_end_date
376: ,p_business_group_id_o
377: => per_sps_shd.g_old_rec.business_group_id
371: => p_rec.effective_end_date
372: ,p_effective_start_date_o
373: => per_sps_shd.g_old_rec.effective_start_date
374: ,p_effective_end_date_o
375: => per_sps_shd.g_old_rec.effective_end_date
376: ,p_business_group_id_o
377: => per_sps_shd.g_old_rec.business_group_id
378: ,p_spinal_point_id_o
379: => per_sps_shd.g_old_rec.spinal_point_id
373: => per_sps_shd.g_old_rec.effective_start_date
374: ,p_effective_end_date_o
375: => per_sps_shd.g_old_rec.effective_end_date
376: ,p_business_group_id_o
377: => per_sps_shd.g_old_rec.business_group_id
378: ,p_spinal_point_id_o
379: => per_sps_shd.g_old_rec.spinal_point_id
380: ,p_grade_spine_id_o
381: => per_sps_shd.g_old_rec.grade_spine_id
375: => per_sps_shd.g_old_rec.effective_end_date
376: ,p_business_group_id_o
377: => per_sps_shd.g_old_rec.business_group_id
378: ,p_spinal_point_id_o
379: => per_sps_shd.g_old_rec.spinal_point_id
380: ,p_grade_spine_id_o
381: => per_sps_shd.g_old_rec.grade_spine_id
382: ,p_sequence_o
383: => per_sps_shd.g_old_rec.sequence
377: => per_sps_shd.g_old_rec.business_group_id
378: ,p_spinal_point_id_o
379: => per_sps_shd.g_old_rec.spinal_point_id
380: ,p_grade_spine_id_o
381: => per_sps_shd.g_old_rec.grade_spine_id
382: ,p_sequence_o
383: => per_sps_shd.g_old_rec.sequence
384: ,p_request_id_o
385: => per_sps_shd.g_old_rec.request_id
379: => per_sps_shd.g_old_rec.spinal_point_id
380: ,p_grade_spine_id_o
381: => per_sps_shd.g_old_rec.grade_spine_id
382: ,p_sequence_o
383: => per_sps_shd.g_old_rec.sequence
384: ,p_request_id_o
385: => per_sps_shd.g_old_rec.request_id
386: ,p_program_application_id_o
387: => per_sps_shd.g_old_rec.program_application_id
381: => per_sps_shd.g_old_rec.grade_spine_id
382: ,p_sequence_o
383: => per_sps_shd.g_old_rec.sequence
384: ,p_request_id_o
385: => per_sps_shd.g_old_rec.request_id
386: ,p_program_application_id_o
387: => per_sps_shd.g_old_rec.program_application_id
388: ,p_program_id_o
389: => per_sps_shd.g_old_rec.program_id
383: => per_sps_shd.g_old_rec.sequence
384: ,p_request_id_o
385: => per_sps_shd.g_old_rec.request_id
386: ,p_program_application_id_o
387: => per_sps_shd.g_old_rec.program_application_id
388: ,p_program_id_o
389: => per_sps_shd.g_old_rec.program_id
390: ,p_program_update_date_o
391: => per_sps_shd.g_old_rec.program_update_date
385: => per_sps_shd.g_old_rec.request_id
386: ,p_program_application_id_o
387: => per_sps_shd.g_old_rec.program_application_id
388: ,p_program_id_o
389: => per_sps_shd.g_old_rec.program_id
390: ,p_program_update_date_o
391: => per_sps_shd.g_old_rec.program_update_date
392: ,p_information1_o
393: => per_sps_shd.g_old_rec.information1
387: => per_sps_shd.g_old_rec.program_application_id
388: ,p_program_id_o
389: => per_sps_shd.g_old_rec.program_id
390: ,p_program_update_date_o
391: => per_sps_shd.g_old_rec.program_update_date
392: ,p_information1_o
393: => per_sps_shd.g_old_rec.information1
394: ,p_information2_o
395: => per_sps_shd.g_old_rec.information2
389: => per_sps_shd.g_old_rec.program_id
390: ,p_program_update_date_o
391: => per_sps_shd.g_old_rec.program_update_date
392: ,p_information1_o
393: => per_sps_shd.g_old_rec.information1
394: ,p_information2_o
395: => per_sps_shd.g_old_rec.information2
396: ,p_information3_o
397: => per_sps_shd.g_old_rec.information3
391: => per_sps_shd.g_old_rec.program_update_date
392: ,p_information1_o
393: => per_sps_shd.g_old_rec.information1
394: ,p_information2_o
395: => per_sps_shd.g_old_rec.information2
396: ,p_information3_o
397: => per_sps_shd.g_old_rec.information3
398: ,p_information4_o
399: => per_sps_shd.g_old_rec.information4
393: => per_sps_shd.g_old_rec.information1
394: ,p_information2_o
395: => per_sps_shd.g_old_rec.information2
396: ,p_information3_o
397: => per_sps_shd.g_old_rec.information3
398: ,p_information4_o
399: => per_sps_shd.g_old_rec.information4
400: ,p_information5_o
401: => per_sps_shd.g_old_rec.information5
395: => per_sps_shd.g_old_rec.information2
396: ,p_information3_o
397: => per_sps_shd.g_old_rec.information3
398: ,p_information4_o
399: => per_sps_shd.g_old_rec.information4
400: ,p_information5_o
401: => per_sps_shd.g_old_rec.information5
402: ,p_information6_o
403: => per_sps_shd.g_old_rec.information6
397: => per_sps_shd.g_old_rec.information3
398: ,p_information4_o
399: => per_sps_shd.g_old_rec.information4
400: ,p_information5_o
401: => per_sps_shd.g_old_rec.information5
402: ,p_information6_o
403: => per_sps_shd.g_old_rec.information6
404: ,p_information7_o
405: => per_sps_shd.g_old_rec.information7
399: => per_sps_shd.g_old_rec.information4
400: ,p_information5_o
401: => per_sps_shd.g_old_rec.information5
402: ,p_information6_o
403: => per_sps_shd.g_old_rec.information6
404: ,p_information7_o
405: => per_sps_shd.g_old_rec.information7
406: ,p_information8_o
407: => per_sps_shd.g_old_rec.information8
401: => per_sps_shd.g_old_rec.information5
402: ,p_information6_o
403: => per_sps_shd.g_old_rec.information6
404: ,p_information7_o
405: => per_sps_shd.g_old_rec.information7
406: ,p_information8_o
407: => per_sps_shd.g_old_rec.information8
408: ,p_information9_o
409: => per_sps_shd.g_old_rec.information9
403: => per_sps_shd.g_old_rec.information6
404: ,p_information7_o
405: => per_sps_shd.g_old_rec.information7
406: ,p_information8_o
407: => per_sps_shd.g_old_rec.information8
408: ,p_information9_o
409: => per_sps_shd.g_old_rec.information9
410: ,p_information10_o
411: => per_sps_shd.g_old_rec.information10
405: => per_sps_shd.g_old_rec.information7
406: ,p_information8_o
407: => per_sps_shd.g_old_rec.information8
408: ,p_information9_o
409: => per_sps_shd.g_old_rec.information9
410: ,p_information10_o
411: => per_sps_shd.g_old_rec.information10
412: ,p_information11_o
413: => per_sps_shd.g_old_rec.information11
407: => per_sps_shd.g_old_rec.information8
408: ,p_information9_o
409: => per_sps_shd.g_old_rec.information9
410: ,p_information10_o
411: => per_sps_shd.g_old_rec.information10
412: ,p_information11_o
413: => per_sps_shd.g_old_rec.information11
414: ,p_information12_o
415: => per_sps_shd.g_old_rec.information12
409: => per_sps_shd.g_old_rec.information9
410: ,p_information10_o
411: => per_sps_shd.g_old_rec.information10
412: ,p_information11_o
413: => per_sps_shd.g_old_rec.information11
414: ,p_information12_o
415: => per_sps_shd.g_old_rec.information12
416: ,p_information13_o
417: => per_sps_shd.g_old_rec.information13
411: => per_sps_shd.g_old_rec.information10
412: ,p_information11_o
413: => per_sps_shd.g_old_rec.information11
414: ,p_information12_o
415: => per_sps_shd.g_old_rec.information12
416: ,p_information13_o
417: => per_sps_shd.g_old_rec.information13
418: ,p_information14_o
419: => per_sps_shd.g_old_rec.information14
413: => per_sps_shd.g_old_rec.information11
414: ,p_information12_o
415: => per_sps_shd.g_old_rec.information12
416: ,p_information13_o
417: => per_sps_shd.g_old_rec.information13
418: ,p_information14_o
419: => per_sps_shd.g_old_rec.information14
420: ,p_information15_o
421: => per_sps_shd.g_old_rec.information15
415: => per_sps_shd.g_old_rec.information12
416: ,p_information13_o
417: => per_sps_shd.g_old_rec.information13
418: ,p_information14_o
419: => per_sps_shd.g_old_rec.information14
420: ,p_information15_o
421: => per_sps_shd.g_old_rec.information15
422: ,p_information16_o
423: => per_sps_shd.g_old_rec.information16
417: => per_sps_shd.g_old_rec.information13
418: ,p_information14_o
419: => per_sps_shd.g_old_rec.information14
420: ,p_information15_o
421: => per_sps_shd.g_old_rec.information15
422: ,p_information16_o
423: => per_sps_shd.g_old_rec.information16
424: ,p_information17_o
425: => per_sps_shd.g_old_rec.information17
419: => per_sps_shd.g_old_rec.information14
420: ,p_information15_o
421: => per_sps_shd.g_old_rec.information15
422: ,p_information16_o
423: => per_sps_shd.g_old_rec.information16
424: ,p_information17_o
425: => per_sps_shd.g_old_rec.information17
426: ,p_information18_o
427: => per_sps_shd.g_old_rec.information18
421: => per_sps_shd.g_old_rec.information15
422: ,p_information16_o
423: => per_sps_shd.g_old_rec.information16
424: ,p_information17_o
425: => per_sps_shd.g_old_rec.information17
426: ,p_information18_o
427: => per_sps_shd.g_old_rec.information18
428: ,p_information19_o
429: => per_sps_shd.g_old_rec.information19
423: => per_sps_shd.g_old_rec.information16
424: ,p_information17_o
425: => per_sps_shd.g_old_rec.information17
426: ,p_information18_o
427: => per_sps_shd.g_old_rec.information18
428: ,p_information19_o
429: => per_sps_shd.g_old_rec.information19
430: ,p_information20_o
431: => per_sps_shd.g_old_rec.information20
425: => per_sps_shd.g_old_rec.information17
426: ,p_information18_o
427: => per_sps_shd.g_old_rec.information18
428: ,p_information19_o
429: => per_sps_shd.g_old_rec.information19
430: ,p_information20_o
431: => per_sps_shd.g_old_rec.information20
432: ,p_information21_o
433: => per_sps_shd.g_old_rec.information21
427: => per_sps_shd.g_old_rec.information18
428: ,p_information19_o
429: => per_sps_shd.g_old_rec.information19
430: ,p_information20_o
431: => per_sps_shd.g_old_rec.information20
432: ,p_information21_o
433: => per_sps_shd.g_old_rec.information21
434: ,p_information22_o
435: => per_sps_shd.g_old_rec.information22
429: => per_sps_shd.g_old_rec.information19
430: ,p_information20_o
431: => per_sps_shd.g_old_rec.information20
432: ,p_information21_o
433: => per_sps_shd.g_old_rec.information21
434: ,p_information22_o
435: => per_sps_shd.g_old_rec.information22
436: ,p_information23_o
437: => per_sps_shd.g_old_rec.information23
431: => per_sps_shd.g_old_rec.information20
432: ,p_information21_o
433: => per_sps_shd.g_old_rec.information21
434: ,p_information22_o
435: => per_sps_shd.g_old_rec.information22
436: ,p_information23_o
437: => per_sps_shd.g_old_rec.information23
438: ,p_information24_o
439: => per_sps_shd.g_old_rec.information24
433: => per_sps_shd.g_old_rec.information21
434: ,p_information22_o
435: => per_sps_shd.g_old_rec.information22
436: ,p_information23_o
437: => per_sps_shd.g_old_rec.information23
438: ,p_information24_o
439: => per_sps_shd.g_old_rec.information24
440: ,p_information25_o
441: => per_sps_shd.g_old_rec.information25
435: => per_sps_shd.g_old_rec.information22
436: ,p_information23_o
437: => per_sps_shd.g_old_rec.information23
438: ,p_information24_o
439: => per_sps_shd.g_old_rec.information24
440: ,p_information25_o
441: => per_sps_shd.g_old_rec.information25
442: ,p_information26_o
443: => per_sps_shd.g_old_rec.information26
437: => per_sps_shd.g_old_rec.information23
438: ,p_information24_o
439: => per_sps_shd.g_old_rec.information24
440: ,p_information25_o
441: => per_sps_shd.g_old_rec.information25
442: ,p_information26_o
443: => per_sps_shd.g_old_rec.information26
444: ,p_information27_o
445: => per_sps_shd.g_old_rec.information27
439: => per_sps_shd.g_old_rec.information24
440: ,p_information25_o
441: => per_sps_shd.g_old_rec.information25
442: ,p_information26_o
443: => per_sps_shd.g_old_rec.information26
444: ,p_information27_o
445: => per_sps_shd.g_old_rec.information27
446: ,p_information28_o
447: => per_sps_shd.g_old_rec.information28
441: => per_sps_shd.g_old_rec.information25
442: ,p_information26_o
443: => per_sps_shd.g_old_rec.information26
444: ,p_information27_o
445: => per_sps_shd.g_old_rec.information27
446: ,p_information28_o
447: => per_sps_shd.g_old_rec.information28
448: ,p_information29_o
449: => per_sps_shd.g_old_rec.information29
443: => per_sps_shd.g_old_rec.information26
444: ,p_information27_o
445: => per_sps_shd.g_old_rec.information27
446: ,p_information28_o
447: => per_sps_shd.g_old_rec.information28
448: ,p_information29_o
449: => per_sps_shd.g_old_rec.information29
450: ,p_information30_o
451: => per_sps_shd.g_old_rec.information30
445: => per_sps_shd.g_old_rec.information27
446: ,p_information28_o
447: => per_sps_shd.g_old_rec.information28
448: ,p_information29_o
449: => per_sps_shd.g_old_rec.information29
450: ,p_information30_o
451: => per_sps_shd.g_old_rec.information30
452: ,p_information_category_o
453: => per_sps_shd.g_old_rec.information_category
447: => per_sps_shd.g_old_rec.information28
448: ,p_information29_o
449: => per_sps_shd.g_old_rec.information29
450: ,p_information30_o
451: => per_sps_shd.g_old_rec.information30
452: ,p_information_category_o
453: => per_sps_shd.g_old_rec.information_category
454: ,p_object_version_number_o
455: => per_sps_shd.g_old_rec.object_version_number
449: => per_sps_shd.g_old_rec.information29
450: ,p_information30_o
451: => per_sps_shd.g_old_rec.information30
452: ,p_information_category_o
453: => per_sps_shd.g_old_rec.information_category
454: ,p_object_version_number_o
455: => per_sps_shd.g_old_rec.object_version_number
456: );
457: --
451: => per_sps_shd.g_old_rec.information30
452: ,p_information_category_o
453: => per_sps_shd.g_old_rec.information_category
454: ,p_object_version_number_o
455: => per_sps_shd.g_old_rec.object_version_number
456: );
457: --
458: exception
459: --
473: -- ----------------------------------------------------------------------------
474: Procedure del
475: (p_effective_date in date
476: ,p_datetrack_mode in varchar2
477: ,p_rec in out nocopy per_sps_shd.g_rec_type
478: ,p_called_from_del_grd_scale in boolean --bug 4096238
479: ) is
480: --
481: l_proc varchar2(72) := g_package||'del';
498: ,p_called_from_del_grd_scale => p_called_from_del_grd_scale -- bug 4096238
499: );
500: -- We must lock the row which we need to delete.
501: --
502: per_sps_shd.lck
503: (p_effective_date => p_effective_date
504: ,p_datetrack_mode => p_datetrack_mode
505: ,p_step_id => p_rec.step_id
506: ,p_object_version_number => p_rec.object_version_number
569: ,p_effective_end_date out nocopy date
570: ,p_called_from_del_grd_scale in boolean --bug 4096238
571: ) is
572: --
573: l_rec per_sps_shd.g_rec_type;
574: l_proc varchar2(72) := g_package||'del';
575: --
576: Begin
577: hr_utility.set_location('Entering:' || l_proc, 5);