[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| FUNCTION: update_object_version
|| DESCRIPTION: Update the object version number in the transaction step
|| to pass the invalid object api error for Save for Later.
||=======================================================================
*/
PROCEDURE update_object_version
(p_transaction_step_id in number
,p_login_person_id in number) IS
CURSOR csr_new_object_number(p_period_of_service_id in number) is
SELECT object_version_number
FROM per_periods_of_service pps
where period_of_service_id = p_period_of_service_id;
l_proc constant varchar2(100) := g_package || ' update_object_version';
END update_object_version;
PROCEDURE update_per_details(
p_validate in number default 0
,p_effective_date in date
,p_period_of_service_id in number
,p_actual_termination_date in date
,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
,p_rehire_reason in varchar2 default hr_api.g_varchar2
) IS
l_person_id per_all_people_f.person_id%TYPE;
l_proc varchar2(100) := g_package ||'update_per_details';
select per.person_id
, per.employee_number
, per.object_version_number
from per_all_people_f per
, per_business_groups bus
, per_periods_of_service pds
, per_person_types pet
where pds.period_of_service_id = p_period_of_service_id
and bus.business_group_id = pds.business_group_id
and per.person_id = pds.person_id
and p_actual_termination_date + 1 between per.effective_start_date
and per.effective_end_date
and pet.person_type_id = per.person_type_id;
SAVEPOINT update_person_details;
hr_person_api.update_person (
p_validate => l_validate
,p_effective_date => p_effective_date + 1
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_rehire_recommendation => p_rehire_recommendation
,p_rehire_reason => p_rehire_reason
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
ROLLBACK TO update_person_details;
rollback TO update_person_details;
END update_per_details;
SELECT ppf.person_id
FROM per_all_people_f ppf -- Bug 2130066 fix
,per_all_assignments_f paf
,per_periods_of_service ppos
WHERE paf.supervisor_id = l_number_value
AND paf.person_id = ppf.person_id
AND ppf.person_id = ppos.person_id
AND ppf.current_employee_flag = 'Y'
AND l_effective_date between ppf.effective_start_date
and ppf.effective_end_date
-- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
AND paf.assignment_type = 'E'
AND l_effective_date between paf.effective_start_date
and paf.effective_end_date
AND l_effective_date between ppos.date_start
and nvl(ppos.actual_termination_date
,l_effective_date)
UNION -- CWK Phase III Changes.
SELECT ppf.person_id
FROM per_all_people_f ppf -- Bug 2130066 fix
,per_all_assignments_f paf
,per_periods_of_placement ppop
WHERE paf.supervisor_id = l_number_value
AND paf.person_id = ppf.person_id
AND ppf.person_id = ppop.person_id
AND ppf.current_npw_flag = 'Y'
AND l_effective_date between ppf.effective_start_date
and ppf.effective_end_date
-- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
AND paf.assignment_type = 'C'
AND l_effective_date between paf.effective_start_date
and paf.effective_end_date
AND l_effective_date between ppop.date_start
and nvl(ppop.actual_termination_date
,l_effective_date);
update_pds_details_err exception;
select business_group_id
into l_business_group_id
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
hr_utility.trace('Calling hr_termination_ss.update_pds_details ' || l_proc);
hr_termination_ss.update_pds_details
(p_validate => 0
,p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_object_version_number => p_object_version_number
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_pds_information_category => l_pds_information_category -- bug 2881583
,p_pds_information1 => p_pds_information1
,p_pds_information2 => p_pds_information2
,p_pds_information3 => p_pds_information3
,p_pds_information4 => p_pds_information4
,p_pds_information5 => p_pds_information5
,p_pds_information6 => p_pds_information6
,p_pds_information7 => p_pds_information7
,p_pds_information8 => p_pds_information8
,p_pds_information9 => p_pds_information9
,p_pds_information10 => l_pds_information10 -- bug 2881583
,p_pds_information11 => p_pds_information11
,p_pds_information12 => p_pds_information12
,p_pds_information13 => p_pds_information13
,p_pds_information14 => p_pds_information14
,p_pds_information15 => p_pds_information15
,p_pds_information16 => p_pds_information16
,p_pds_information17 => p_pds_information17
,p_pds_information18 => p_pds_information18
,p_pds_information19 => p_pds_information19
,p_pds_information20 => p_pds_information20
,p_pds_information21 => p_pds_information21
,p_pds_information22 => p_pds_information22
,p_pds_information23 => p_pds_information23
,p_pds_information24 => p_pds_information24
,p_pds_information25 => p_pds_information25
,p_pds_information26 => p_pds_information26
,p_pds_information27 => p_pds_information27
,p_pds_information28 => p_pds_information28
,p_pds_information29 => p_pds_information29
,p_pds_information30 => p_pds_information30
);
hr_utility.trace('Calling hr_termination_ss.update_per_details ' || l_proc);
update_per_details(
p_validate => 0, -- false
p_effective_date => p_effective_date,
p_period_of_service_id => p_period_of_service_id,
p_actual_termination_date => p_actual_termination_date,
p_rehire_recommendation => p_rehire_recommendation,
p_rehire_reason => p_rehire_reason);
|| PROCEDURE: update_pds_details
||---------------------------------------------------------------------------
||
|| Description:
|| This procedure will call the actual API -
|| hr_periods_of_service_api.update_pds_details
||
|| Pre Conditions:
||
|| In Arguments:
|| Contains entire list of parameters that are defined in the actual
|| API. For details see pepdsapi.pkb file.
||
|| out nocopy Arguments:
||
|| In out nocopy Arguments:
||
|| Post Success:
|| Executes the API call.
||
|| Post Failure:
|| Raises an exception
||
|| Access Status:
|| Public.
||
||===========================================================================
*/
PROCEDURE update_pds_details
(p_validate in number default 0
,p_effective_date in date
,p_period_of_service_id in number
,p_termination_accepted_person in number default hr_api.g_number
,p_accepted_termination_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_comments in varchar2 default hr_api.g_varchar2
,p_leaving_reason in varchar2 default hr_api.g_varchar2
,p_notified_termination_date in date default hr_api.g_date
,p_projected_termination_date in date default hr_api.g_date
,p_attribute_category in varchar2 default hr_api.g_varchar2
,p_attribute1 in varchar2 default hr_api.g_varchar2
,p_attribute2 in varchar2 default hr_api.g_varchar2
,p_attribute3 in varchar2 default hr_api.g_varchar2
,p_attribute4 in varchar2 default hr_api.g_varchar2
,p_attribute5 in varchar2 default hr_api.g_varchar2
,p_attribute6 in varchar2 default hr_api.g_varchar2
,p_attribute7 in varchar2 default hr_api.g_varchar2
,p_attribute8 in varchar2 default hr_api.g_varchar2
,p_attribute9 in varchar2 default hr_api.g_varchar2
,p_attribute10 in varchar2 default hr_api.g_varchar2
,p_attribute11 in varchar2 default hr_api.g_varchar2
,p_attribute12 in varchar2 default hr_api.g_varchar2
,p_attribute13 in varchar2 default hr_api.g_varchar2
,p_attribute14 in varchar2 default hr_api.g_varchar2
,p_attribute15 in varchar2 default hr_api.g_varchar2
,p_attribute16 in varchar2 default hr_api.g_varchar2
,p_attribute17 in varchar2 default hr_api.g_varchar2
,p_attribute18 in varchar2 default hr_api.g_varchar2
,p_attribute19 in varchar2 default hr_api.g_varchar2
,p_attribute20 in varchar2 default hr_api.g_varchar2
,p_pds_information_category in varchar2 default hr_api.g_varchar2
,p_pds_information1 in varchar2 default hr_api.g_varchar2
,p_pds_information2 in varchar2 default hr_api.g_varchar2
,p_pds_information3 in varchar2 default hr_api.g_varchar2
,p_pds_information4 in varchar2 default hr_api.g_varchar2
,p_pds_information5 in varchar2 default hr_api.g_varchar2
,p_pds_information6 in varchar2 default hr_api.g_varchar2
,p_pds_information7 in varchar2 default hr_api.g_varchar2
,p_pds_information8 in varchar2 default hr_api.g_varchar2
,p_pds_information9 in varchar2 default hr_api.g_varchar2
,p_pds_information10 in varchar2 default hr_api.g_varchar2
,p_pds_information11 in varchar2 default hr_api.g_varchar2
,p_pds_information12 in varchar2 default hr_api.g_varchar2
,p_pds_information13 in varchar2 default hr_api.g_varchar2
,p_pds_information14 in varchar2 default hr_api.g_varchar2
,p_pds_information15 in varchar2 default hr_api.g_varchar2
,p_pds_information16 in varchar2 default hr_api.g_varchar2
,p_pds_information17 in varchar2 default hr_api.g_varchar2
,p_pds_information18 in varchar2 default hr_api.g_varchar2
,p_pds_information19 in varchar2 default hr_api.g_varchar2
,p_pds_information20 in varchar2 default hr_api.g_varchar2
,p_pds_information21 in varchar2 default hr_api.g_varchar2
,p_pds_information22 in varchar2 default hr_api.g_varchar2
,p_pds_information23 in varchar2 default hr_api.g_varchar2
,p_pds_information24 in varchar2 default hr_api.g_varchar2
,p_pds_information25 in varchar2 default hr_api.g_varchar2
,p_pds_information26 in varchar2 default hr_api.g_varchar2
,p_pds_information27 in varchar2 default hr_api.g_varchar2
,p_pds_information28 in varchar2 default hr_api.g_varchar2
,p_pds_information29 in varchar2 default hr_api.g_varchar2
,p_pds_information30 in varchar2 default hr_api.g_varchar2
)
IS
l_proc constant varchar2(100) := g_package || ' update_pds_details';
hr_periods_of_service_api.update_pds_details
(p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate)
,p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_object_version_number => p_object_version_number
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_pds_information_category => p_pds_information_category
,p_pds_information1 => p_pds_information1
,p_pds_information2 => p_pds_information2
,p_pds_information3 => p_pds_information3
,p_pds_information4 => p_pds_information4
,p_pds_information5 => p_pds_information5
,p_pds_information6 => p_pds_information6
,p_pds_information7 => p_pds_information7
,p_pds_information8 => p_pds_information8
,p_pds_information9 => p_pds_information9
,p_pds_information10 => p_pds_information10
,p_pds_information11 => p_pds_information11
,p_pds_information12 => p_pds_information12
,p_pds_information13 => p_pds_information13
,p_pds_information14 => p_pds_information14
,p_pds_information15 => p_pds_information15
,p_pds_information16 => p_pds_information16
,p_pds_information17 => p_pds_information17
,p_pds_information18 => p_pds_information18
,p_pds_information19 => p_pds_information19
,p_pds_information20 => p_pds_information20
,p_pds_information21 => p_pds_information21
,p_pds_information22 => p_pds_information22
,p_pds_information23 => p_pds_information23
,p_pds_information24 => p_pds_information24
,p_pds_information25 => p_pds_information25
,p_pds_information26 => p_pds_information26
,p_pds_information27 => p_pds_information27
,p_pds_information28 => p_pds_information28
,p_pds_information29 => p_pds_information29
,p_pds_information30 => p_pds_information30);
END update_pds_details;
|| : and call the APIs in update mode
||=======================================================================
*/
PROCEDURE process_api (
p_validate IN BOOLEAN DEFAULT FALSE,
p_transaction_step_id IN NUMBER DEFAULT NULL,
p_effective_date IN VARCHAR2 DEFAULT NULL
)
IS
lrt_termination hr_termination_ss.rt_termination;
select business_group_id
into l_business_group_id
from per_periods_of_service
where period_of_service_id = ln_period_of_service_id;
hr_periods_of_service_api.update_pds_details (
p_validate => FALSE,
p_effective_date => ld_actual_term_date,
p_period_of_service_id => ln_period_of_service_id,
p_object_version_number => ln_object_version_number,
p_comments => ll_term_comments,
p_leaving_reason => lv_term_reason,
p_notified_termination_date => ld_notified_term_date,
p_projected_termination_date => ld_projected_termination_date,
p_attribute_category => lv_attribute_category,
p_attribute1 => lt_term_flex(1),
p_attribute2 => lt_term_flex(2),
p_attribute3 => lt_term_flex(3),
p_attribute4 => lt_term_flex(4),
p_attribute5 => lt_term_flex(5),
p_attribute6 => lt_term_flex(6),
p_attribute7 => lt_term_flex(7),
p_attribute8 => lt_term_flex(8),
p_attribute9 => lt_term_flex(9),
p_attribute10 => lt_term_flex(10),
p_attribute11 => lt_term_flex(11),
p_attribute12 => lt_term_flex(12),
p_attribute13 => lt_term_flex(13),
p_attribute14 => lt_term_flex(14),
p_attribute15 => lt_term_flex(15),
p_attribute16 => lt_term_flex(16),
p_attribute17 => lt_term_flex(17),
p_attribute18 => lt_term_flex(18),
p_attribute19 => lt_term_flex(19),
p_attribute20 => lt_term_flex(20),
---- DDF Enhancement : Save DDF Segments data as well.
p_pds_information_category => lv_pds_information_category,
p_pds_information1 => lt_term_flex(21),
p_pds_information2 => lt_term_flex(22),
p_pds_information3 => lt_term_flex(23),
p_pds_information4 => lt_term_flex(24),
p_pds_information5 => lt_term_flex(25),
p_pds_information6 => lt_term_flex(26),
p_pds_information7 => lt_term_flex(27),
p_pds_information8 => lt_term_flex(28),
p_pds_information9 => lt_term_flex(29),
p_pds_information10 => lt_term_flex(30),
p_pds_information11 => lt_term_flex(31),
p_pds_information12 => lt_term_flex(32),
p_pds_information13 => lt_term_flex(33),
p_pds_information14 => lt_term_flex(34),
p_pds_information15 => lt_term_flex(35),
p_pds_information16 => lt_term_flex(36),
p_pds_information17 => lt_term_flex(37),
p_pds_information18 => lt_term_flex(38),
p_pds_information19 => lt_term_flex(39),
p_pds_information20 => lt_term_flex(40),
p_pds_information21 => lt_term_flex(41),
p_pds_information22 => lt_term_flex(42),
p_pds_information23 => lt_term_flex(43),
p_pds_information24 => lt_term_flex(44),
p_pds_information25 => lt_term_flex(45),
p_pds_information26 => lt_term_flex(46),
p_pds_information27 => lt_term_flex(47),
p_pds_information28 => lt_term_flex(48),
p_pds_information29 => lt_term_flex(49),
p_pds_information30 => lt_term_flex(50)
);
update_per_details(
p_validate => 0, -- false
p_effective_date => ld_actual_term_date,
p_period_of_service_id => ln_period_of_service_id,
p_actual_termination_date => ld_actual_term_date,
p_rehire_recommendation => lv_rehire_recommendation,
p_rehire_reason => lv_rehire_reason
);