[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
contact_relationship_id,
contact_type,
comments,
primary_contact_flag,
third_party_pay_flag,
bondholder_flag,
date_start,
start_life_reason_id,
date_end,
end_life_reason_id,
rltd_per_rsds_w_dsgntr_flag,
personal_flag,
sequence_number,
dependent_flag,
beneficiary_flag,
cont_attribute_category,
cont_attribute1,
cont_attribute2,
cont_attribute3,
cont_attribute4,
cont_attribute5,
cont_attribute6,
cont_attribute7,
cont_attribute8,
cont_attribute9,
cont_attribute10,
cont_attribute11,
cont_attribute12,
cont_attribute13,
cont_attribute14,
cont_attribute15,
cont_attribute16,
cont_attribute17,
cont_attribute18,
cont_attribute19,
cont_attribute20,
CONT_INFORMATION_CATEGORY,
CONT_INFORMATION1 ,
CONT_INFORMATION2 ,
CONT_INFORMATION3 ,
CONT_INFORMATION4 ,
CONT_INFORMATION5 ,
CONT_INFORMATION6 ,
CONT_INFORMATION7 ,
CONT_INFORMATION8 ,
CONT_INFORMATION9 ,
CONT_INFORMATION10 ,
CONT_INFORMATION11 ,
CONT_INFORMATION12 ,
CONT_INFORMATION13 ,
CONT_INFORMATION14 ,
CONT_INFORMATION15 ,
CONT_INFORMATION16 ,
CONT_INFORMATION17 ,
CONT_INFORMATION18 ,
CONT_INFORMATION19 ,
CONT_INFORMATION20 ,
pcr.object_version_number
FROM
per_contact_relationships pcr
,per_all_people_f pap
,hr_comments hc
WHERE pcr.contact_relationship_id = p_contact_relationship_id
AND pcr.contact_person_id = pap.person_id
AND p_eff_date BETWEEN pap.effective_start_date and pap.effective_end_date
AND hc.comment_id (+) = pap.comment_id;
SELECT addr.address_id
FROM per_contact_relationships pcr
,per_addresses addr
WHERE pcr.contact_relationship_id = p_contact_relationship_id
and pcr.contact_person_id = addr.person_id
and trunc(sysdate) between addr.date_from
and nvl(addr.date_to, trunc(sysdate))
and addr.primary_flag = 'Y'
--
-- Bug 2652114 : Do not delete the address if contact is already a employee
-- otherwise his payroll will get affected.
--
and not exists
(select null
from per_all_assignments_f asg
where trunc(sysdate) between asg.effective_start_date
and asg.effective_end_date
and asg.person_id = addr.person_id
and asg.primary_flag = 'Y'
and asg.assignment_type = 'E'
);
delete from per_addresses
where address_id = l_cont_primary_addr.address_id;
/* 999 Delete before arcsin
-- ----------------------------------------------------------------------------
-- |------------------------------< get_varchar2_value >------------------------|
-- ----------------------------------------------------------------------------
function get_varchar2_value
(p_transaction_step_id in number
,p_name in varchar2
) return varchar2 is
-- --------------------------------------------------------------------------
-- declare local variables
-- --------------------------------------------------------------------------
l_proc varchar2(72) := g_package||'get_varchar2_value';
l_insert boolean := false;
select hatv.varchar2_value
from hr_api_transaction_values hatv
where hatv.transaction_step_id = p_transaction_step_id
and hatv.name = l_name;
l_insert boolean := false;
select hatv.number_value
from hr_api_transaction_values hatv
where hatv.transaction_step_id = p_transaction_step_id
and hatv.name = l_name;
SELECT last_name, first_name
from per_all_people_f
WHERE person_id = p_contact_person_id
AND trunc(sysdate) BETWEEN effective_start_date and effective_end_date;
fix sansingh if l_contact_operation is still null ,it means that only address region was get updated
where the same values is saved as P_CONTACT_OR_PERSON ,
so obtain l_contact_operation from here
*/
if l_contact_operation is null then
l_contact_operation := hr_transaction_api.get_varchar2_value
(p_transaction_step_id => l_trans_step_ids(ln_index)
,p_name => 'P_CONTACT_OR_PERSON');
if only phone or address region is updated , p_contact_person_id
would not be populated , it would be actually stored as p_person_id in that case for the transaction step
so populating p_contact_person_id with the same
*/
if (p_contact_person_id is null) then
p_contact_person_id := l_contact_person_id;
,p_attribute_update_mode out nocopy varchar2
,P_CONTACT_RELATIONSHIP_ID out nocopy NUMBER
,P_CONTACT_TYPE out nocopy VARCHAR2
,P_COMMENTS out nocopy VARCHAR2
,P_PRIMARY_CONTACT_FLAG out nocopy VARCHAR2
,P_THIRD_PARTY_PAY_FLAG out nocopy VARCHAR2
,p_bondholder_flag out nocopy varchar2
,p_date_start out nocopy date
,p_start_life_reason_id out nocopy number
,p_date_end out nocopy date
,p_end_life_reason_id out nocopy number
,p_rltd_per_rsds_w_dsgntr_flag out nocopy varchar2
,p_personal_flag out nocopy varchar2
,p_sequence_number out nocopy number
,p_dependent_flag out nocopy varchar2
,p_beneficiary_flag out nocopy varchar2
,p_cont_attribute_category out nocopy varchar2
,p_cont_attribute1 out nocopy varchar2
,p_cont_attribute2 out nocopy varchar2
,p_cont_attribute3 out nocopy varchar2
,p_cont_attribute4 out nocopy varchar2
,p_cont_attribute5 out nocopy varchar2
,p_cont_attribute6 out nocopy varchar2
,p_cont_attribute7 out nocopy varchar2
,p_cont_attribute8 out nocopy varchar2
,p_cont_attribute9 out nocopy varchar2
,p_cont_attribute10 out nocopy varchar2
,p_cont_attribute11 out nocopy varchar2
,p_cont_attribute12 out nocopy varchar2
,p_cont_attribute13 out nocopy varchar2
,p_cont_attribute14 out nocopy varchar2
,p_cont_attribute15 out nocopy varchar2
,p_cont_attribute16 out nocopy varchar2
,p_cont_attribute17 out nocopy varchar2
,p_cont_attribute18 out nocopy varchar2
,p_cont_attribute19 out nocopy varchar2
,p_cont_attribute20 out nocopy varchar2
,P_CONT_INFORMATION_CATEGORY out nocopy varchar2
,P_CONT_INFORMATION1 out nocopy varchar2
,P_CONT_INFORMATION2 out nocopy varchar2
,P_CONT_INFORMATION3 out nocopy varchar2
,P_CONT_INFORMATION4 out nocopy varchar2
,P_CONT_INFORMATION5 out nocopy varchar2
,P_CONT_INFORMATION6 out nocopy varchar2
,P_CONT_INFORMATION7 out nocopy varchar2
,P_CONT_INFORMATION8 out nocopy varchar2
,P_CONT_INFORMATION9 out nocopy varchar2
,P_CONT_INFORMATION10 out nocopy varchar2
,P_CONT_INFORMATION11 out nocopy varchar2
,P_CONT_INFORMATION12 out nocopy varchar2
,P_CONT_INFORMATION13 out nocopy varchar2
,P_CONT_INFORMATION14 out nocopy varchar2
,P_CONT_INFORMATION15 out nocopy varchar2
,P_CONT_INFORMATION16 out nocopy varchar2
,P_CONT_INFORMATION17 out nocopy varchar2
,P_CONT_INFORMATION18 out nocopy varchar2
,P_CONT_INFORMATION19 out nocopy varchar2
,P_CONT_INFORMATION20 out nocopy varchar2
,p_object_version_number out nocopy number
-- 9999 What is this parameter.
,p_review_proc_call out nocopy varchar2
)is
l_transaction_step_id number default null;
,p_attribute_update_mode => p_attribute_update_mode
,P_CONTACT_RELATIONSHIP_ID => P_CONTACT_RELATIONSHIP_ID
,P_CONTACT_TYPE => P_CONTACT_TYPE
,P_COMMENTS => P_COMMENTS
,P_PRIMARY_CONTACT_FLAG => P_PRIMARY_CONTACT_FLAG
,P_THIRD_PARTY_PAY_FLAG => P_THIRD_PARTY_PAY_FLAG
,p_bondholder_flag => p_bondholder_flag
,p_date_start => p_date_start
,p_start_life_reason_id => p_start_life_reason_id
,p_date_end => p_date_end
,p_end_life_reason_id => p_end_life_reason_id
,p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
,p_personal_flag => p_personal_flag
,p_sequence_number => p_sequence_number
,p_dependent_flag => p_dependent_flag
,p_beneficiary_flag => p_beneficiary_flag
,p_cont_attribute_category => p_cont_attribute_category
,p_cont_attribute1 => p_cont_attribute1
,p_cont_attribute2 => p_cont_attribute2
,p_cont_attribute3 => p_cont_attribute3
,p_cont_attribute4 => p_cont_attribute4
,p_cont_attribute5 => p_cont_attribute5
,p_cont_attribute6 => p_cont_attribute6
,p_cont_attribute7 => p_cont_attribute7
,p_cont_attribute8 => p_cont_attribute8
,p_cont_attribute9 => p_cont_attribute9
,p_cont_attribute10 => p_cont_attribute10
,p_cont_attribute11 => p_cont_attribute11
,p_cont_attribute12 => p_cont_attribute12
,p_cont_attribute13 => p_cont_attribute13
,p_cont_attribute14 => p_cont_attribute14
,p_cont_attribute15 => p_cont_attribute15
,p_cont_attribute16 => p_cont_attribute16
,p_cont_attribute17 => p_cont_attribute17
,p_cont_attribute18 => p_cont_attribute18
,p_cont_attribute19 => p_cont_attribute19
,p_cont_attribute20 => p_cont_attribute20
,p_object_version_number => p_object_version_number
,P_CONT_INFORMATION_CATEGORY => P_CONT_INFORMATION_CATEGORY
,P_CONT_INFORMATION1 => P_CONT_INFORMATION1
,P_CONT_INFORMATION2 => P_CONT_INFORMATION2
,P_CONT_INFORMATION3 => P_CONT_INFORMATION3
,P_CONT_INFORMATION4 => P_CONT_INFORMATION4
,P_CONT_INFORMATION5 => P_CONT_INFORMATION5
,P_CONT_INFORMATION6 => P_CONT_INFORMATION6
,P_CONT_INFORMATION7 => P_CONT_INFORMATION7
,P_CONT_INFORMATION8 => P_CONT_INFORMATION8
,P_CONT_INFORMATION9 => P_CONT_INFORMATION9
,P_CONT_INFORMATION10 => P_CONT_INFORMATION10
,P_CONT_INFORMATION11 => P_CONT_INFORMATION11
,P_CONT_INFORMATION12 => P_CONT_INFORMATION12
,P_CONT_INFORMATION13 => P_CONT_INFORMATION13
,P_CONT_INFORMATION14 => P_CONT_INFORMATION14
,P_CONT_INFORMATION15 => P_CONT_INFORMATION15
,P_CONT_INFORMATION16 => P_CONT_INFORMATION16
,P_CONT_INFORMATION17 => P_CONT_INFORMATION17
,P_CONT_INFORMATION18 => P_CONT_INFORMATION18
,P_CONT_INFORMATION19 => P_CONT_INFORMATION19
,P_CONT_INFORMATION20 => P_CONT_INFORMATION20
,p_review_proc_call => p_review_proc_call
);
,p_attribute_update_mode out nocopy varchar2
,P_CONTACT_RELATIONSHIP_ID out nocopy NUMBER
,P_CONTACT_TYPE out nocopy VARCHAR2
,P_COMMENTS out nocopy VARCHAR2
,P_PRIMARY_CONTACT_FLAG out nocopy VARCHAR2
,P_THIRD_PARTY_PAY_FLAG out nocopy VARCHAR2
,p_bondholder_flag out nocopy varchar2
,p_date_start out nocopy date
,p_start_life_reason_id out nocopy number
,p_date_end out nocopy date
,p_end_life_reason_id out nocopy number
,p_rltd_per_rsds_w_dsgntr_flag out nocopy varchar2
,p_personal_flag out nocopy varchar2
,p_sequence_number out nocopy number
,p_dependent_flag out nocopy varchar2
,p_beneficiary_flag out nocopy varchar2
,p_cont_attribute_category out nocopy varchar2
,p_cont_attribute1 out nocopy varchar2
,p_cont_attribute2 out nocopy varchar2
,p_cont_attribute3 out nocopy varchar2
,p_cont_attribute4 out nocopy varchar2
,p_cont_attribute5 out nocopy varchar2
,p_cont_attribute6 out nocopy varchar2
,p_cont_attribute7 out nocopy varchar2
,p_cont_attribute8 out nocopy varchar2
,p_cont_attribute9 out nocopy varchar2
,p_cont_attribute10 out nocopy varchar2
,p_cont_attribute11 out nocopy varchar2
,p_cont_attribute12 out nocopy varchar2
,p_cont_attribute13 out nocopy varchar2
,p_cont_attribute14 out nocopy varchar2
,p_cont_attribute15 out nocopy varchar2
,p_cont_attribute16 out nocopy varchar2
,p_cont_attribute17 out nocopy varchar2
,p_cont_attribute18 out nocopy varchar2
,p_cont_attribute19 out nocopy varchar2
,p_cont_attribute20 out nocopy varchar2
,P_CONT_INFORMATION_CATEGORY out nocopy varchar2
,P_CONT_INFORMATION1 out nocopy varchar2
,P_CONT_INFORMATION2 out nocopy varchar2
,P_CONT_INFORMATION3 out nocopy varchar2
,P_CONT_INFORMATION4 out nocopy varchar2
,P_CONT_INFORMATION5 out nocopy varchar2
,P_CONT_INFORMATION6 out nocopy varchar2
,P_CONT_INFORMATION7 out nocopy varchar2
,P_CONT_INFORMATION8 out nocopy varchar2
,P_CONT_INFORMATION9 out nocopy varchar2
,P_CONT_INFORMATION10 out nocopy varchar2
,P_CONT_INFORMATION11 out nocopy varchar2
,P_CONT_INFORMATION12 out nocopy varchar2
,P_CONT_INFORMATION13 out nocopy varchar2
,P_CONT_INFORMATION14 out nocopy varchar2
,P_CONT_INFORMATION15 out nocopy varchar2
,P_CONT_INFORMATION16 out nocopy varchar2
,P_CONT_INFORMATION17 out nocopy varchar2
,P_CONT_INFORMATION18 out nocopy varchar2
,P_CONT_INFORMATION19 out nocopy varchar2
,P_CONT_INFORMATION20 out nocopy varchar2
,p_object_version_number out nocopy number
-- 9999 What is this parameter.
,p_review_proc_call out nocopy varchar2
)is
--
--
l_proc varchar2(72) := g_package||'get_contact_relationship_tt';
P_ATTRIBUTE_UPDATE_MODE := 'update';
p_attribute_update_mode :=
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE_UPDATE_MODE');
hr_utility.set_location('P_ATTRIBUTE_UPDATE_MODE', 2222);
|| PROCEDURE: update_contact_relationship
||---------------------------------------------------------------------------
||
|| Description:
|| Description:
|| This procedure will call the actual API -
|| hr_contact_rel_api.update_contact_relationship()
||
|| Pre Conditions:
||
|| In Arguments:
|| Contains entire list of parameters that are defined in the actual
|| API. For details see peaddapi.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_contact_relationship
(p_validate in varchar2 default 'Y'
,p_cont_effective_date in date
,p_contact_relationship_id in number
,p_contact_type in varchar2 default hr_api.g_varchar2
,p_ctr_comments in clob default hr_api.g_varchar2 -- Bug#13362792
,p_primary_contact_flag in varchar2 default hr_api.g_varchar2
,p_third_party_pay_flag in varchar2 default hr_api.g_varchar2
,p_bondholder_flag in varchar2 default hr_api.g_varchar2
,p_date_start in date default hr_api.g_date
,p_start_life_reason_id in number default hr_api.g_number
,p_date_end in date default hr_api.g_date
,p_end_life_reason_id in number default hr_api.g_number
,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default hr_api.g_varchar2
,p_personal_flag in varchar2 default hr_api.g_varchar2
,p_sequence_number in number default hr_api.g_number
,p_dependent_flag in varchar2 default hr_api.g_varchar2
,p_beneficiary_flag in varchar2 default hr_api.g_varchar2
,p_cont_attribute_category in varchar2 default hr_api.g_varchar2
,p_cont_attribute1 in varchar2 default hr_api.g_varchar2
,p_cont_attribute2 in varchar2 default hr_api.g_varchar2
,p_cont_attribute3 in varchar2 default hr_api.g_varchar2
,p_cont_attribute4 in varchar2 default hr_api.g_varchar2
,p_cont_attribute5 in varchar2 default hr_api.g_varchar2
,p_cont_attribute6 in varchar2 default hr_api.g_varchar2
,p_cont_attribute7 in varchar2 default hr_api.g_varchar2
,p_cont_attribute8 in varchar2 default hr_api.g_varchar2
,p_cont_attribute9 in varchar2 default hr_api.g_varchar2
,p_cont_attribute10 in varchar2 default hr_api.g_varchar2
,p_cont_attribute11 in varchar2 default hr_api.g_varchar2
,p_cont_attribute12 in varchar2 default hr_api.g_varchar2
,p_cont_attribute13 in varchar2 default hr_api.g_varchar2
,p_cont_attribute14 in varchar2 default hr_api.g_varchar2
,p_cont_attribute15 in varchar2 default hr_api.g_varchar2
,p_cont_attribute16 in varchar2 default hr_api.g_varchar2
,p_cont_attribute17 in varchar2 default hr_api.g_varchar2
,p_cont_attribute18 in varchar2 default hr_api.g_varchar2
,p_cont_attribute19 in varchar2 default hr_api.g_varchar2
,p_cont_attribute20 in varchar2 default hr_api.g_varchar2
,p_person_id in number
,p_login_person_id in number default hr_api.g_number
,p_cont_object_version_number in out nocopy number
,p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in number
,p_action in varchar2 -- this is p_action_type
,p_process_section_name in varchar2
,p_review_page_region_code in varchar2 default hr_api.g_varchar2
-- Update_person parameters
,p_per_effective_date in date
,p_datetrack_update_mode in varchar2
,p_cont_person_id in number
,p_per_object_version_number in out nocopy number
,p_person_type_id in number default hr_api.g_number
,p_last_name in varchar2 default hr_api.g_varchar2
,p_applicant_number in varchar2 default hr_api.g_varchar2
,p_per_comments in varchar2 default hr_api.g_varchar2
,p_date_employee_data_verified in date default hr_api.g_date
,p_date_of_birth in date default hr_api.g_date
,p_email_address in varchar2 default hr_api.g_varchar2
,p_employee_number in out nocopy varchar2
,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
,p_first_name in varchar2 default hr_api.g_varchar2
,p_known_as in varchar2 default hr_api.g_varchar2
,p_marital_status in varchar2 default hr_api.g_varchar2
,p_middle_names in varchar2 default hr_api.g_varchar2
,p_nationality in varchar2 default hr_api.g_varchar2
,p_national_identifier in varchar2 default hr_api.g_varchar2
,p_previous_last_name in varchar2 default hr_api.g_varchar2
,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
,p_sex in varchar2 default hr_api.g_varchar2
,p_title in varchar2 default hr_api.g_varchar2
,p_vendor_id in number default hr_api.g_number
,p_work_telephone in varchar2 default hr_api.g_varchar2
,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_attribute21 in varchar2 default hr_api.g_varchar2
,p_attribute22 in varchar2 default hr_api.g_varchar2
,p_attribute23 in varchar2 default hr_api.g_varchar2
,p_attribute24 in varchar2 default hr_api.g_varchar2
,p_attribute25 in varchar2 default hr_api.g_varchar2
,p_attribute26 in varchar2 default hr_api.g_varchar2
,p_attribute27 in varchar2 default hr_api.g_varchar2
,p_attribute28 in varchar2 default hr_api.g_varchar2
,p_attribute29 in varchar2 default hr_api.g_varchar2
,p_attribute30 in varchar2 default hr_api.g_varchar2
,p_per_information_category in varchar2 default hr_api.g_varchar2
,p_per_information1 in varchar2 default hr_api.g_varchar2
,p_per_information2 in varchar2 default hr_api.g_varchar2
,p_per_information3 in varchar2 default hr_api.g_varchar2
,p_per_information4 in varchar2 default hr_api.g_varchar2
,p_per_information5 in varchar2 default hr_api.g_varchar2
,p_per_information6 in varchar2 default hr_api.g_varchar2
,p_per_information7 in varchar2 default hr_api.g_varchar2
,p_per_information8 in varchar2 default hr_api.g_varchar2
,p_per_information9 in varchar2 default hr_api.g_varchar2
,p_per_information10 in varchar2 default hr_api.g_varchar2
,p_per_information11 in varchar2 default hr_api.g_varchar2
,p_per_information12 in varchar2 default hr_api.g_varchar2
,p_per_information13 in varchar2 default hr_api.g_varchar2
,p_per_information14 in varchar2 default hr_api.g_varchar2
,p_per_information15 in varchar2 default hr_api.g_varchar2
,p_per_information16 in varchar2 default hr_api.g_varchar2
,p_per_information17 in varchar2 default hr_api.g_varchar2
,p_per_information18 in varchar2 default hr_api.g_varchar2
,p_per_information19 in varchar2 default hr_api.g_varchar2
,p_per_information20 in varchar2 default hr_api.g_varchar2
,p_per_information21 in varchar2 default hr_api.g_varchar2
,p_per_information22 in varchar2 default hr_api.g_varchar2
,p_per_information23 in varchar2 default hr_api.g_varchar2
,p_per_information24 in varchar2 default hr_api.g_varchar2
,p_per_information25 in varchar2 default hr_api.g_varchar2
,p_per_information26 in varchar2 default hr_api.g_varchar2
,p_per_information27 in varchar2 default hr_api.g_varchar2
,p_per_information28 in varchar2 default hr_api.g_varchar2
,p_per_information29 in varchar2 default hr_api.g_varchar2
,p_per_information30 in varchar2 default hr_api.g_varchar2
,p_date_of_death in date default hr_api.g_date
,p_background_check_status in varchar2 default hr_api.g_varchar2
,p_background_date_check in date default hr_api.g_date
,p_blood_type in varchar2 default hr_api.g_varchar2
,p_correspondence_language in varchar2 default hr_api.g_varchar2
,p_fast_path_employee in varchar2 default hr_api.g_varchar2
,p_fte_capacity in number default hr_api.g_number
,p_hold_applicant_date_until in date default hr_api.g_date
,p_honors in varchar2 default hr_api.g_varchar2
,p_internal_location in varchar2 default hr_api.g_varchar2
,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
,p_last_medical_test_date in date default hr_api.g_date
,p_mailstop in varchar2 default hr_api.g_varchar2
,p_office_number in varchar2 default hr_api.g_varchar2
,p_on_military_service in varchar2 default hr_api.g_varchar2
,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
,p_projected_start_date in date default hr_api.g_date
,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
,p_resume_exists in varchar2 default hr_api.g_varchar2
,p_resume_last_updated in date default hr_api.g_date
,p_second_passport_exists in varchar2 default hr_api.g_varchar2
,p_student_status in varchar2 default hr_api.g_varchar2
,p_work_schedule in varchar2 default hr_api.g_varchar2
,p_rehire_reason in varchar2 default hr_api.g_varchar2
,p_suffix in varchar2 default hr_api.g_varchar2
,p_benefit_group_id in number default hr_api.g_number
,p_receipt_of_death_cert_date in date default hr_api.g_date
,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
,p_dpdnt_adoption_date in date default hr_api.g_date
,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
,p_original_date_of_hire in date default hr_api.g_date
,p_adjusted_svc_date in date default hr_api.g_date
,p_town_of_birth in varchar2 default hr_api.g_varchar2
,p_region_of_birth in varchar2 default hr_api.g_varchar2
,p_country_of_birth in varchar2 default hr_api.g_varchar2
,p_global_person_id in varchar2 default hr_api.g_varchar2
,p_business_group_id in number default hr_api.g_number
,p_contact_operation in varchar2 default hr_api.g_varchar2
,p_emrg_cont_flag in varchar2 default hr_api.g_varchar2
,p_dpdnt_bnf_flag in varchar2 default hr_api.g_varchar2
,p_save_mode in varchar2 default null
,P_CONT_INFORMATION_CATEGORY in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION1 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION2 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION3 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION4 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION5 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION6 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION7 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION8 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION9 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION10 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION11 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION12 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION13 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION14 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION15 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION16 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION17 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION18 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION19 in varchar2 default hr_api.g_varchar2
,P_CONT_INFORMATION20 in varchar2 default hr_api.g_varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_comment_id out nocopy number
,p_name_combination_warning out nocopy varchar2
,p_assign_payroll_warning out nocopy varchar2
,p_orig_hire_warning out nocopy varchar2
,p_ni_duplicate_warn_or_err out nocopy varchar2
,p_orig_rel_type in varchar2 default null
) IS
cursor get_emrg_primary_cont_flag(p_contact_relationship_id number
,p_contact_person_id number
,p_person_id number)
is
select primary_contact_flag --'N'
from PER_CONTACT_RELATIONSHIPS
where person_id = p_person_id
and contact_person_id = p_contact_person_id
and contact_type = 'EMRG'
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end)) ;
l_attribute_update_mode varchar2(100) default null;
l_proc varchar2(72) := g_package||'update_contact_relationship';
select contact_relationship_id,
object_version_number,
primary_contact_flag,
person_id,
date_start,
date_end
from PER_CONTACT_RELATIONSHIPS
where person_id = p_person_id
and contact_person_id = p_cont_person_id
and contact_type = 'EMRG'
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end));
select 'Y'
from per_contact_relationships
where person_id = p_person_id
and primary_contact_flag = 'Y'
and (nvl(date_end,hr_general.end_of_time)
> nvl(z_date_start,hr_general.start_of_time)
and nvl(date_start,hr_general.start_of_time)
< nvl(z_date_end,hr_general.end_of_time))
and contact_type = 'EMRG';
hr_utility.set_location('Entering hr_contact_rel_api.update_contact_relationship', 5);
l_attribute_update_mode := g_attribute_update;
l_attribute_update_mode := g_attribute_correct;
hr_utility.set_location('l_attribute_update_mode=' ||
l_attribute_update_mode, 10);
SAVEPOINT before_entering_into_update ;
hr_utility.set_location('Calling hr_contact_rel_api.update_contact_relationship', 25);
hr_contact_rel_api.update_contact_relationship(
p_validate => l_validate
,p_effective_date => p_cont_effective_date
,p_contact_relationship_id => l_contact_relationship_id
,p_contact_type => p_contact_type
,p_comments => p_ctr_comments
-- ,p_primary_contact_flag => l_emrg_primary_cont_flag
,p_third_party_pay_flag => p_third_party_pay_flag
,p_bondholder_flag => p_bondholder_flag
,p_date_start => p_date_start
,p_start_life_reason_id => l_start_life_reason_id
,p_date_end => p_date_end
,p_end_life_reason_id => l_end_life_reason_id
,p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
,p_personal_flag => p_personal_flag
-- ,p_sequence_number => l_sequence_number
,p_dependent_flag => p_dependent_flag
,p_beneficiary_flag => p_beneficiary_flag
,p_cont_attribute_category => p_cont_attribute_category
,p_cont_attribute1 => p_cont_attribute1
,p_cont_attribute2 => p_cont_attribute2
,p_cont_attribute3 => p_cont_attribute3
,p_cont_attribute4 => p_cont_attribute4
,p_cont_attribute5 => p_cont_attribute5
,p_cont_attribute6 => p_cont_attribute6
,p_cont_attribute7 => p_cont_attribute7
,p_cont_attribute8 => p_cont_attribute8
,p_cont_attribute9 => p_cont_attribute9
,p_cont_attribute10 => p_cont_attribute10
,p_cont_attribute11 => p_cont_attribute11
,p_cont_attribute12 => p_cont_attribute12
,p_cont_attribute13 => p_cont_attribute13
,p_cont_attribute14 => p_cont_attribute14
,p_cont_attribute15 => p_cont_attribute15
,p_cont_attribute16 => p_cont_attribute16
,p_cont_attribute17 => p_cont_attribute17
,p_cont_attribute18 => p_cont_attribute18
,p_cont_attribute19 => p_cont_attribute19
,p_cont_attribute20 => p_cont_attribute20
,P_CONT_INFORMATION_CATEGORY => P_CONT_INFORMATION_CATEGORY
,P_CONT_INFORMATION1 => P_CONT_INFORMATION1
,P_CONT_INFORMATION2 => P_CONT_INFORMATION2
,P_CONT_INFORMATION3 => P_CONT_INFORMATION3
,P_CONT_INFORMATION4 => P_CONT_INFORMATION4
,P_CONT_INFORMATION5 => P_CONT_INFORMATION5
,P_CONT_INFORMATION6 => P_CONT_INFORMATION6
,P_CONT_INFORMATION7 => P_CONT_INFORMATION7
,P_CONT_INFORMATION8 => P_CONT_INFORMATION8
,P_CONT_INFORMATION9 => P_CONT_INFORMATION9
,P_CONT_INFORMATION10 => P_CONT_INFORMATION10
,P_CONT_INFORMATION11 => P_CONT_INFORMATION11
,P_CONT_INFORMATION12 => P_CONT_INFORMATION12
,P_CONT_INFORMATION13 => P_CONT_INFORMATION13
,P_CONT_INFORMATION14 => P_CONT_INFORMATION14
,P_CONT_INFORMATION15 => P_CONT_INFORMATION15
,P_CONT_INFORMATION16 => P_CONT_INFORMATION16
,P_CONT_INFORMATION17 => P_CONT_INFORMATION17
,P_CONT_INFORMATION18 => P_CONT_INFORMATION18
,P_CONT_INFORMATION19 => P_CONT_INFORMATION19
,P_CONT_INFORMATION20 => P_CONT_INFORMATION20
,p_object_version_number => p_cont_object_version_number
);
hr_utility.set_location('api error exists hr_contact_rel_api.update_contact_relationship', 30);
ROLLBACK to before_entering_into_update ;
hr_utility.set_location('No changes found hr_contact_rel_api.update_contact_relationship', 35);
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_rehire_reason => p_rehire_reason
,p_benefit_group_id => l_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
-- ,p_adjusted_svc_date => p_adjusted_svc_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_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
);
hr_person_api.update_person(
p_validate => l_validate
,p_effective_date => p_per_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => p_cont_person_id
,p_object_version_number => l_per_ovn -- p_per_object_version_number
--,p_person_type_id => l_person_type_id
,p_last_name => p_last_name
,p_applicant_number => p_applicant_number
,p_comments => p_per_comments
,p_date_employee_data_verified => p_date_employee_data_verified
,p_date_of_birth => p_date_of_birth
,p_email_address => p_email_address
,p_employee_number => l_employee_number
,p_expense_check_send_to_addres => p_expense_check_send_to_addres
,p_first_name => p_first_name
,p_known_as => p_known_as
,p_marital_status => p_marital_status
,p_middle_names => p_middle_names
,p_nationality => p_nationality
,p_national_identifier => p_national_identifier
,p_previous_last_name => p_previous_last_name
,p_registered_disabled_flag => p_registered_disabled_flag
,p_sex => p_sex
,p_title => p_title
,p_vendor_id => p_vendor_id
,p_work_telephone => p_work_telephone
,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_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_per_information_category => p_per_information_category
,p_per_information1 => p_per_information1
,p_per_information2 => p_per_information2
,p_per_information3 => p_per_information3
,p_per_information4 => p_per_information4
,p_per_information5 => p_per_information5
,p_per_information6 => p_per_information6
,p_per_information7 => p_per_information7
,p_per_information8 => p_per_information8
,p_per_information9 => p_per_information9
,p_per_information10 => p_per_information10
,p_per_information11 => p_per_information11
,p_per_information12 => p_per_information12
,p_per_information13 => p_per_information13
,p_per_information14 => p_per_information14
,p_per_information15 => p_per_information15
,p_per_information16 => p_per_information16
,p_per_information17 => p_per_information17
,p_per_information18 => p_per_information18
,p_per_information19 => p_per_information19
,p_per_information20 => p_per_information20
,p_per_information21 => p_per_information21
,p_per_information22 => p_per_information22
,p_per_information23 => p_per_information23
,p_per_information24 => p_per_information24
,p_per_information25 => p_per_information25
,p_per_information26 => p_per_information26
,p_per_information27 => p_per_information27
,p_per_information28 => p_per_information28
,p_per_information29 => p_per_information29
,p_per_information30 => p_per_information30
,p_date_of_death => p_date_of_death
,p_background_check_status => p_background_check_status
,p_background_date_check => p_background_date_check
,p_blood_type => p_blood_type
,p_correspondence_language => p_correspondence_language
,p_fast_path_employee => p_fast_path_employee
,p_fte_capacity => p_fte_capacity
,p_hold_applicant_date_until => p_hold_applicant_date_until
,p_honors => p_honors
,p_internal_location => p_internal_location
,p_last_medical_test_by => p_last_medical_test_by
,p_last_medical_test_date => p_last_medical_test_date
,p_mailstop => p_mailstop
,p_office_number => p_office_number
,p_on_military_service => p_on_military_service
,p_pre_name_adjunct => p_pre_name_adjunct
,p_projected_start_date => p_projected_start_date
,p_rehire_authorizor => p_rehire_authorizor
,p_rehire_recommendation => p_rehire_recommendation
,p_resume_exists => p_resume_exists
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_rehire_reason => p_rehire_reason
,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
-- ,p_adjusted_svc_date => bb
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,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
);
hr_utility.set_location('api error exists hr_process_person_ss.update_person', 40);
ROLLBACK to before_entering_into_update ;
hr_utility.set_location('No changes found in hr_process_person_ss.update_person', 45);
ROLLBACK to before_entering_into_update ;
l_transaction_table(l_count).param_name :=upper('p_datetrack_update_mode');
l_transaction_table(l_count).param_value := p_datetrack_update_mode;
l_transaction_table(l_count).param_name :=upper('p_resume_last_updated');
l_transaction_table(l_count).param_value := to_char(p_resume_last_updated,
hr_transaction_ss.g_date_format);
hr_utility.set_location('Leaving hr_process_contact_ss.update_contact_relationship', 80);
hr_utility.set_message(800, 'HR_UPDATE_NOT_ALLOWED');
end update_contact_relationship;
,p_datetrack_update_mode in varchar2 default hr_api.g_correction -- 9999
,p_applicant_number in varchar2 default null
,p_background_check_status in varchar2 default null
,p_background_date_check in date default null
,p_benefit_group_id in number default null
,p_blood_type in varchar2 default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_date_employee_data_verified in date default null
,p_date_of_death in date default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default null
,p_employee_number in out nocopy varchar2
,p_expense_check_send_to_addres in varchar2 default null
,p_fast_path_employee in varchar2 default null
,p_fte_capacity in number default null
,p_global_person_id in varchar2 default null
,p_hold_applicant_date_until in date default null
,p_internal_location in varchar2 default null
,p_last_medical_test_by in varchar2 default null
,p_last_medical_test_date in date default null
,p_mailstop in varchar2 default null
,p_office_number in varchar2 default null
,p_on_military_service in varchar2 default null
,p_original_date_of_hire in date default null
,p_projected_start_date in date default null
,p_receipt_of_death_cert_date in date default null
,p_region_of_birth in varchar2 default null
,p_rehire_authorizor in varchar2 default null
,p_rehire_recommendation in varchar2 default null
,p_rehire_reason in varchar2 default null
,p_resume_exists in varchar2 default null
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_town_of_birth in varchar2 default null
,p_uses_tobacco_flag in varchar2 default null
,p_vendor_id in number default null
,p_work_schedule in varchar2 default null
,p_contact_operation in varchar2 default null
,p_emrg_cont_flag in varchar2 default 'N'
,p_dpdnt_bnf_flag in varchar2 default 'N'
,p_save_mode in varchar2 default null
-- Added new paramets
,P_CONT_INFORMATION_CATEGORY in varchar2 default null
,P_CONT_INFORMATION1 in varchar2 default null
,P_CONT_INFORMATION2 in varchar2 default null
,P_CONT_INFORMATION3 in varchar2 default null
,P_CONT_INFORMATION4 in varchar2 default null
,P_CONT_INFORMATION5 in varchar2 default null
,P_CONT_INFORMATION6 in varchar2 default null
,P_CONT_INFORMATION7 in varchar2 default null
,P_CONT_INFORMATION8 in varchar2 default null
,P_CONT_INFORMATION9 in varchar2 default null
,P_CONT_INFORMATION10 in varchar2 default null
,P_CONT_INFORMATION11 in varchar2 default null
,P_CONT_INFORMATION12 in varchar2 default null
,P_CONT_INFORMATION13 in varchar2 default null
,P_CONT_INFORMATION14 in varchar2 default null
,P_CONT_INFORMATION15 in varchar2 default null
,P_CONT_INFORMATION16 in varchar2 default null
,P_CONT_INFORMATION17 in varchar2 default null
,P_CONT_INFORMATION18 in varchar2 default null
,P_CONT_INFORMATION19 in varchar2 default null
,P_CONT_INFORMATION20 in varchar2 default null
--bug 4634855
,P_MIRROR_CONT_INFORMATION_CAT in varchar2 default null
,P_MIRROR_CONT_INFORMATION1 in varchar2 default null
,P_MIRROR_CONT_INFORMATION2 in varchar2 default null
,P_MIRROR_CONT_INFORMATION3 in varchar2 default null
,P_MIRROR_CONT_INFORMATION4 in varchar2 default null
,P_MIRROR_CONT_INFORMATION5 in varchar2 default null
,P_MIRROR_CONT_INFORMATION6 in varchar2 default null
,P_MIRROR_CONT_INFORMATION7 in varchar2 default null
,P_MIRROR_CONT_INFORMATION8 in varchar2 default null
,P_MIRROR_CONT_INFORMATION9 in varchar2 default null
,P_MIRROR_CONT_INFORMATION10 in varchar2 default null
,P_MIRROR_CONT_INFORMATION11 in varchar2 default null
,P_MIRROR_CONT_INFORMATION12 in varchar2 default null
,P_MIRROR_CONT_INFORMATION13 in varchar2 default null
,P_MIRROR_CONT_INFORMATION14 in varchar2 default null
,P_MIRROR_CONT_INFORMATION15 in varchar2 default null
,P_MIRROR_CONT_INFORMATION16 in varchar2 default null
,P_MIRROR_CONT_INFORMATION17 in varchar2 default null
,P_MIRROR_CONT_INFORMATION18 in varchar2 default null
,P_MIRROR_CONT_INFORMATION19 in varchar2 default null
,P_MIRROR_CONT_INFORMATION20 in varchar2 default null
,p_contact_relationship_id out nocopy number
,p_ctr_object_version_number out nocopy number
,p_per_person_id out nocopy number
,p_per_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_con_name_combination_warning out nocopy varchar2
,p_per_name_combination_warning out nocopy varchar2
,p_con_orig_hire_warning out nocopy varchar2
,p_per_orig_hire_warning out nocopy varchar2
,p_per_assign_payroll_warning out nocopy varchar2
,p_ni_duplicate_warn_or_err out nocopy varchar2
)
IS
l_count INTEGER := 0;
l_attribute_update_mode varchar2(100) default null;
select count(hats.transaction_step_id)
into l_primary_contact_added
from hr_api_transaction_steps hats,
hr_api_transaction_values hatv
where hats.item_type = p_item_type
and hats.item_key = p_item_key
and hats.api_name = 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API'
and hats.transaction_step_id = hatv.transaction_step_id
and hatv.varchar2_value = 'Y'
and hatv.name = upper('p_primary_contact_flag');
l_attribute_update_mode := g_attribute_update;
l_attribute_update_mode := g_attribute_correct;
hr_utility.set_location('Setting l_attribute_update_mode ', 20);
select min(p.date_of_birth) , min(p.effective_start_date)
into l_main_per_date_of_birth , l_main_per_eff_start_date
from per_people_f p
where p.person_id = l_person_id;
select nvl(max(hats1.transaction_step_id),0)
into l_validate_g_per_step_id
from hr_api_transaction_steps hats1
where hats1.item_type = 'HRSSA'
and hats1.item_key = p_item_key
and hats1.api_name in( 'HR_PROCESS_PERSON_SS.PROCESS_API', 'BEN_PROCESS_COBRA_PERSON_SS.PROCESS_API');
select min(date_start)
into l_start_date
from per_contact_relationships
where contact_person_id = p_contact_person_id;
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_town_of_birth => p_town_of_birth
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_vendor_id => p_vendor_id
,p_work_schedule => p_work_schedule ) ;
CURSOR c_pap IS SELECT employee_number FROM per_all_people_f
WHERE person_id = p_per_person_id
AND p_start_date BETWEEN
effective_start_date AND effective_end_date ;
hr_utility.set_location('Calling hr_person_api.update_person '||l_proc,55);
hr_person_api.update_person (
p_validate => l_validate_mode
,p_effective_date => l_start_date
--p_start_date --9999p_effective_date
,p_datetrack_update_mode => hr_api.g_correction
-- 9999 p_datetrack_update_mode
,p_person_id => p_per_person_id
,p_object_version_number => p_per_object_version_number
-- ,p_person_type_id => p_person_type_id
-- ,p_last_name => p_last_name
,p_applicant_number => p_applicant_number
--,p_comments => p_ctr_comments
,p_date_employee_data_verified => p_date_employee_data_verified
-- ,p_date_of_birth => p_date_of_birth
-- ,p_email_address => p_email_address
,p_employee_number => p_employee_number --in out nocopy Param
,p_expense_check_send_to_addres => p_expense_check_send_to_addres
-- ,p_first_name => p_first_name
-- ,p_known_as => p_known_as
-- ,p_marital_status => p_marital_status
-- ,p_middle_names => p_middle_names
-- ,p_nationality => p_nationality
-- ,p_national_identifier => p_national_identifier
-- ,p_previous_last_name => p_previous_last_name
-- ,p_registered_disabled_flag => p_registered_disabled_flag
-- ,p_sex => p_sex
-- ,p_title => p_title
,p_vendor_id => p_vendor_id
-- ,p_work_telephone => p_work_telephone
,p_date_of_death => p_date_of_death
,p_background_check_status => p_background_check_status
,p_background_date_check => p_background_date_check
,p_blood_type => p_blood_type
-- ,p_correspondence_language => p_correspondence_language
,p_fast_path_employee => p_fast_path_employee
,p_fte_capacity => p_fte_capacity
,p_hold_applicant_date_until => p_hold_applicant_date_until
-- ,p_honors => p_honors
,p_internal_location => p_internal_location
,p_last_medical_test_by => p_last_medical_test_by
,p_last_medical_test_date => p_last_medical_test_date
,p_mailstop => p_mailstop
,p_office_number => p_office_number
,p_on_military_service => p_on_military_service
-- ,p_pre_name_adjunct => p_pre_name_adjunct
,p_projected_start_date => p_projected_start_date
,p_rehire_authorizor => p_rehire_authorizor
,p_rehire_recommendation => p_rehire_recommendation
,p_resume_exists => p_resume_exists
,p_resume_last_updated => p_resume_last_updated
,p_second_passport_exists => p_second_passport_exists
,p_student_status => p_student_status
,p_work_schedule => p_work_schedule
,p_rehire_reason => p_rehire_reason
-- ,p_suffix => p_suffix
,p_benefit_group_id => p_benefit_group_id
,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
,p_uses_tobacco_flag => p_uses_tobacco_flag
,p_dpdnt_adoption_date => p_dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
,p_original_date_of_hire => p_original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => p_town_of_birth
,p_region_of_birth => p_region_of_birth
,p_country_of_birth => p_country_of_birth
,p_global_person_id => p_global_person_id
-- Out Parameters
,p_effective_start_date => p_per_effective_start_date
,p_effective_end_date => p_per_effective_end_date
,p_full_name => p_full_name
,p_comment_id => p_per_comment_id
,p_name_combination_warning => l_per_name_combination_warning
,p_assign_payroll_warning => l_per_assign_payroll_warning
,p_orig_hire_warning => l_per_orig_hire_warning
);
hr_utility.set_location('api error exists hr_person_api.update_person ', 67);
l_transaction_table(l_count).param_name :=upper('p_datetrack_update_mode');
l_transaction_table(l_count).param_value := p_datetrack_update_mode;
l_transaction_table(l_count).param_name :=upper('p_resume_last_updated');
l_transaction_table(l_count).param_value := to_char(p_resume_last_updated , hr_transaction_ss.g_date_format);
hr_utility.set_message(800, 'HR_UPDATE_NOT_ALLOWED');
,p_resume_last_updated in date default hr_api.g_date
,p_second_passport_exists in varchar2 default hr_api.g_varchar2
,p_student_status in varchar2 default hr_api.g_varchar2
,p_town_of_birth in varchar2 default hr_api.g_varchar2
,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
,p_vendor_id in number default hr_api.g_number
,p_work_schedule in varchar2 default hr_api.g_varchar2 )
return boolean is
--
l_rec_changed boolean default FALSE;
IF p_resume_last_updated <> hr_api.g_date
THEN
l_rec_changed := TRUE;
select 'Y'
from per_contact_relationships
where person_id = l_person_id
and primary_contact_flag = 'Y'
and (nvl(date_end,hr_general.end_of_time)
> nvl(z_date_start,hr_general.start_of_time)
and nvl(date_start,hr_general.start_of_time)
< nvl(z_date_end,hr_general.end_of_time));
CURSOR c_pap IS SELECT employee_number FROM per_all_people_f
WHERE person_id = L_PER_PERSON_ID
AND l_effective_date BETWEEN
effective_start_date AND effective_end_date ;
hr_person_api.update_person (
p_validate => p_validate
,p_effective_date => l_effective_date
,p_datetrack_update_mode => hr_api.g_correction
-- 9999 p_datetrack_update_mode
,p_person_id => L_PER_PERSON_ID
,p_object_version_number => L_PER_OBJECT_VERSION_NUMBER
,p_employee_number => l_employee_number
-- Bug 2652114
/*
,p_adjusted_svc_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_adjusted_svc_date'))
*/
--
,p_applicant_number =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_applicant_number'))
--
,p_background_check_status =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_background_check_status'))
--
,p_background_date_check =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_background_date_check'))
--
,p_benefit_group_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_benefit_group_id'))
--
,p_blood_type =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_blood_type'))
--
,p_coord_ben_med_pln_no =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_coord_ben_med_pln_no'))
--
,p_coord_ben_no_cvg_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_coord_ben_no_cvg_flag'))
--
,p_country_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_country_of_birth'))
--
,p_date_employee_data_verified =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_date_employee_data_verified'))
--
,p_date_of_death =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_date_of_death'))
--
,p_dpdnt_adoption_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_dpdnt_adoption_date'))
--
,p_dpdnt_vlntry_svce_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_dpdnt_vlntry_svce_flag'))
--
,p_expense_check_send_to_addres =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_expense_check_send_to_addres'))
--
,p_fast_path_employee =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_fast_path_employee'))
--
,p_fte_capacity =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_fte_capacity'))
--
,p_global_person_id =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_global_person_id'))
--
,p_hold_applicant_date_until =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_hold_applicant_date_until'))
--
,p_internal_location =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_internal_location'))
--
,p_last_medical_test_by =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_last_medical_test_by'))
--
,p_last_medical_test_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_last_medical_test_date'))
--
,p_mailstop =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_mailstop'))
--
,p_office_number =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_office_number'))
--
,p_on_military_service =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_on_military_service'))
--
-- Bug 2652114
/*
,p_original_date_of_hire =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_original_date_of_hire'))
*/
--
,p_projected_start_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_projected_start_date'))
--
,p_receipt_of_death_cert_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_receipt_of_death_cert_date'))
--
,p_region_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_region_of_birth'))
--
,p_rehire_authorizor =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_authorizor'))
--
,p_rehire_recommendation =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_recommendation'))
--
,p_rehire_reason =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_reason'))
--
,p_resume_exists =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_resume_exists'))
--
,p_resume_last_updated =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_resume_last_updated'))
--
,p_second_passport_exists =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_second_passport_exists'))
--
,p_student_status =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_student_status'))
--
,p_town_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_town_of_birth'))
--
,p_uses_tobacco_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_uses_tobacco_flag'))
--
,p_vendor_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_vendor_id'))
--
,p_work_schedule =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_work_schedule'))
--
,p_effective_start_date => l_per_effective_start_date
,p_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_comment_id => l_per_comment_id
,p_name_combination_warning => l_per_name_combination_warning
,p_assign_payroll_warning => l_per_assign_payroll_warning
,p_orig_hire_warning => l_per_orig_hire_warning
);
select contact_relationship_id,
object_version_number,
primary_contact_flag,
person_id,
date_start,
date_end
from PER_CONTACT_RELATIONSHIPS
where person_id = p_person_id
and contact_person_id = p_contact_person_id
and contact_type = 'EMRG'
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end));
select contact_relationship_id,
object_version_number
from PER_CONTACT_RELATIONSHIPS
where person_id = p_person_id
and contact_person_id = p_contact_person_id
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end));
select 'Y'
from per_contact_relationships
where person_id = z_person_id
and primary_contact_flag = 'Y'
and (nvl(date_end,hr_general.end_of_time)
> nvl(z_date_start,hr_general.start_of_time)
and nvl(date_start,hr_general.start_of_time)
< nvl(z_date_end,hr_general.end_of_time));
l_datetrack_update_mode varchar2(100);
SAVEPOINT update_cont_relationship;
if l_process_section = 'DELETE_CONTACTS' then
--
hr_utility.set_location('DELETE_CONTACTS:'||l_proc, 10);
else -- l_process_section is not 'DELETE_CONTACTS'
--
-- SFL changes
is_address_updated(
P_CONTACT_RELATIONSHIP_ID => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
,P_DATE_START => hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_START')
,p_transaction_step_id => p_transaction_step_id
,p_CONTACT_PERSON_ID => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_PERSON_ID')
, p_PERSON_ID => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PERSON_ID'));
l_datetrack_update_mode :=
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_datetrack_update_mode'));
if l_datetrack_update_mode = 'CORRECT' then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_person_api.update_person(
p_validate => p_validate
--
,p_effective_date => l_effective_date
/*
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_effective_date'))
*/
--
,p_datetrack_update_mode => l_datetrack_update_mode
-- 9999
-- hr_transaction_api.get_varchar2_value
-- (p_transaction_step_id => p_transaction_step_id
-- ,p_name =>upper('p_datetrack_update_mode'))
--
,p_person_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_cont_person_id'))
--
,p_object_version_number => l_per_ovn
--
---------------------------------------------------------------------------
-- Bug 1937643 Fix Begins - 08/04/2002
-- With the PTU model, the per_all_people_f.person_type_id stores only the
-- default user flavor of the system_person_type. The true user flavor
-- for the system_person_type is stored in per_person_type_usages_f table.
-- Since the current Personal Information Contacts region
-- does not allow a user to choose any user flavor, so we do not pass in
-- p_person_type_id when calling the hr_person_api.update_person. That way,
-- the api will understand that the person_type is not changed and will not
-- update the person_type_id in per_person_type_usages_f table as is. If we
-- pass the per_all_people_f.person_type_id to the api,the person_type_id in
-- per_person_type_usages_f table will be updated with that value which will
-- overwrite the true user flavor of the system person type with the
-- default user flavor person type. This may not be desirable.
-- When we allow a user to select user flavors of person type in Contacts
-- region, the commented out code needs to change and the whole package
-- of this hrconwrs.pkb needs to get the true person_type_id from the
-- per_person_type_usages_f table.
---------------------------------------------------------------------------
/*
,p_person_type_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_person_type_id'))
*/
--
,p_last_name =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_last_name'))
--
,p_applicant_number =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_applicant_number'))
--
,p_comments =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_comments'))
--
,p_date_employee_data_verified =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_date_employee_data_verified'))
--
,p_date_of_birth =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_date_of_birth'))
--
,p_email_address =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_email_address'))
--
,p_employee_number => l_employee_number
--
,p_expense_check_send_to_addres =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_expense_check_send_to_addres'))
--
,p_first_name =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_first_name'))
--
,p_known_as =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_known_as'))
--
,p_marital_status =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_marital_status'))
--
,p_middle_names =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_middle_names'))
--
,p_nationality =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_nationality'))
--
,p_national_identifier =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_national_identifier'))
--
,p_previous_last_name =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_previous_last_name'))
--
,p_registered_disabled_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_registered_disabled_flag'))
--
,p_sex =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_sex'))
--
,p_title =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_title'))
--
,p_vendor_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_vendor_id'))
--
,p_work_telephone =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_work_telephone'))
--
,p_attribute_category =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute_category'))
--
,p_attribute1 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute1'))
--
,p_attribute2 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute2'))
--
,p_attribute3 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute3'))
--
,p_attribute4 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute4'))
--
,p_attribute5 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute5'))
--
,p_attribute6 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute6'))
--
,p_attribute7 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute7'))
--
,p_attribute8 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute8'))
--
,p_attribute9 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute9'))
--
,p_attribute10 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute10'))
--
,p_attribute11 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute11'))
--
,p_attribute12 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute12'))
--
,p_attribute13 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute13'))
--
,p_attribute14 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute14'))
--
,p_attribute15 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute15'))
--
,p_attribute16 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute16'))
--
,p_attribute17 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute17'))
--
,p_attribute18 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute18'))
--
,p_attribute19 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute19'))
--
,p_attribute20 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute20'))
--
,p_attribute21 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute21'))
--
,p_attribute22 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute22'))
--
,p_attribute23 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute23'))
--
,p_attribute24 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute24'))
--
,p_attribute25 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute25'))
--
,p_attribute26 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute26'))
--
,p_attribute27 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute27'))
--
,p_attribute28 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute28'))
--
,p_attribute29 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute29'))
--
,p_attribute30 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_attribute30'))
--
,p_per_information_category =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information_category'))
--
,p_per_information1 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information1'))
--
,p_per_information2 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information2'))
--
,p_per_information3 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information3'))
--
,p_per_information4 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information4'))
--
,p_per_information5 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information5'))
--
,p_per_information6 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information6'))
--
,p_per_information7 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information7'))
--
,p_per_information8 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information8'))
--
,p_per_information9 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information9'))
--
,p_per_information10 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information10'))
--
,p_per_information11 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information11'))
--
,p_per_information12 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information12'))
--
,p_per_information13 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information13'))
--
,p_per_information14 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information14'))
--
,p_per_information15 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information15'))
--
,p_per_information16 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information16'))
--
,p_per_information17 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information17'))
--
,p_per_information18 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information18'))
--
,p_per_information19 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information19'))
--
,p_per_information20 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information20'))
--
,p_per_information21 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information21'))
--
,p_per_information22 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information22'))
--
,p_per_information23 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information23'))
--
,p_per_information24 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information24'))
--
,p_per_information25 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information25'))
--
,p_per_information26 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information26'))
--
,p_per_information27 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information27'))
--
,p_per_information28 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information28'))
--
,p_per_information29 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information29'))
--
,p_per_information30 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_per_information30'))
--
,p_date_of_death =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_date_of_death'))
--
,p_background_check_status =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_background_check_status'))
--
,p_background_date_check =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_background_date_check'))
--
,p_blood_type =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_blood_type'))
--
,p_correspondence_language =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_correspondence_language'))
--
,p_fast_path_employee =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_fast_path_employee'))
--
,p_fte_capacity =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_fte_capacity'))
--
,p_hold_applicant_date_until =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_hold_applicant_date_until'))
--
,p_honors =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_honors'))
--
,p_internal_location =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_internal_location'))
--
,p_last_medical_test_by =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_last_medical_test_by'))
--
,p_last_medical_test_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_last_medical_test_date'))
--
,p_mailstop =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_mailstop'))
--
,p_office_number =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_office_number'))
--
,p_on_military_service =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_on_military_service'))
--
,p_pre_name_adjunct =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_pre_name_adjunct'))
--
,p_projected_start_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_projected_start_date'))
--
,p_rehire_authorizor =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_authorizor'))
--
,p_rehire_recommendation =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_recommendation'))
--
,p_resume_exists =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_resume_exists'))
--
,p_resume_last_updated =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_resume_last_updated'))
--
,p_second_passport_exists =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_second_passport_exists'))
--
,p_student_status =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_student_status'))
--
,p_work_schedule =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_work_schedule'))
--
,p_rehire_reason =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_rehire_reason'))
--
,p_suffix =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_suffix'))
--
,p_benefit_group_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_benefit_group_id'))
--
,p_receipt_of_death_cert_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_receipt_of_death_cert_date'))
--
,p_coord_ben_med_pln_no =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_coord_ben_med_pln_no'))
--
,p_coord_ben_no_cvg_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_coord_ben_no_cvg_flag'))
--
,p_uses_tobacco_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_uses_tobacco_flag'))
--
,p_dpdnt_adoption_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_dpdnt_adoption_date'))
--
,p_dpdnt_vlntry_svce_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_dpdnt_vlntry_svce_flag'))
--
/*
-- Bug 2652114 : As the java code passes null value do not pass the null value
-- to api. SS contacts module is not allowed to modify adjusted service date
-- and original_date_of_hire
-- So not necessary to pass the parameter.
--
,p_original_date_of_hire =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_original_date_of_hire'))
--
,p_adjusted_svc_date =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_adjusted_svc_date'))
*/
--
,p_town_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_town_of_birth'))
--
,p_region_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_region_of_birth'))
--
,p_country_of_birth =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_country_of_birth'))
--
,p_global_person_id =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_global_person_id'))
--
,p_effective_start_date => l_effective_start_date
--
,p_effective_end_date => l_effective_end_date
--
,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
--
);
if l_action = 'UPDATE' then
if l_orig_rel_type <> 'EMRG' then
---bug 5894873
---if earlier start date of contact is greater than or equals to the new date, then delete the contact,
---and create a new one. Else update the contact, and then create one.
l_old_contact_relationship_id :=
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID');
select nvl(date_start,trunc(sysdate))
into l_start_contact_date
from per_contact_relationships
where contact_relationship_id = l_old_contact_relationship_id
and object_version_number = l_ovn;
hr_contact_rel_api.delete_contact_relationship method is commented out as this method
did not support when contact is only one, i.e, it is not dual maintained. So from now
PER_CONTACT_RELATIONSHIPS_PKG.Update_Row is called as core HR team is use it for the same pourpose.
This row handler makes all necessary changes of the per_all_people_f and per_contact_relationships table
One new check is add here with the help of the variable of skip_contact_create_flg
This is, do not create a contact if already did so with this row handler method and has not set
the end date of that row
***********************************************************************************************
*/
/*hr_contact_rel_api.delete_contact_relationship(
p_validate => p_validate
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
,p_object_version_number => l_ovn
);
select rowid
into p_rowid
from per_contact_relationships
where CONTACT_RELATIONSHIP_ID = l_CONTACT_RELATIONSHIP_ID_1;
hr_utility.set_location('before call PER_CONTACT_RELATIONSHIPS_PKG.Update_Row:X_Contact_Relationship_Id:' || l_CONTACT_RELATIONSHIP_ID_1 , 5);
PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
X_Rowid => p_rowid
, X_Contact_Relationship_Id => l_CONTACT_RELATIONSHIP_ID_1
, X_Business_Group_Id => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BUSINESS_GROUP_ID')
, X_Person_Id => hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_person_id'))
, X_Contact_Person_Id => hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name =>upper('p_cont_person_id'))
, X_Contact_Type => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_TYPE')
, X_Comments => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CTR_COMMENTS')
, X_Bondholder_Flag => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BONDHOLDER_FLAG')
, X_Third_Party_Pay_Flag => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_THIRD_PARTY_PAY_FLAG')
, X_Primary_Contact_Flag => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PRIMARY_CONTACT_FLAG')
, X_Cont_Attribute_Category => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE_CATEGORY')
, X_Cont_Attribute1 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE1')
, X_Cont_Attribute2 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE2')
, X_Cont_Attribute3 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE3')
, X_Cont_Attribute4 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE4')
, X_Cont_Attribute5 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE5')
, X_Cont_Attribute6 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE6')
, X_Cont_Attribute7 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE7')
, X_Cont_Attribute8 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE8')
, X_Cont_Attribute9 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE9')
, X_Cont_Attribute10 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE10')
, X_Cont_Attribute11 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE11')
, X_Cont_Attribute12 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE12')
, X_Cont_Attribute13 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE13')
, X_Cont_Attribute14 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE14')
, X_Cont_Attribute15 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE15')
, X_Cont_Attribute16 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE16')
, X_Cont_Attribute17 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE17')
, X_Cont_Attribute18 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE18')
, X_Cont_Attribute19 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE19')
, X_Cont_Attribute20 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE20')
, X_Cont_Information_Category => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PER_INFORMATION_CATEGORY')
, X_Cont_Information1 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION1')
, X_Cont_Information2 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION2')
, X_Cont_Information3 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION3')
, X_Cont_Information4 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION4')
, X_Cont_Information5 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION5')
, X_Cont_Information6 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION6')
, X_Cont_Information7 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION7')
, X_Cont_Information8 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION8')
, X_Cont_Information9 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION9')
, X_Cont_Information10 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION10')
, X_Cont_Information11 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION11')
, X_Cont_Information12 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION12')
, X_Cont_Information13 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION13')
, X_Cont_Information14 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION14')
, X_Cont_Information15 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION15')
, X_Cont_Information16 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION16')
, X_Cont_Information17 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION17')
, X_Cont_Information18 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION18')
, X_Cont_Information19 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION19')
, X_Cont_Information20 => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION20')
, X_Session_Date => null --- this session_date is not used in PER_CONTACT_RELATIONSHIPS_PKG.update_row method, so null is pass here
, X_Date_Start => hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_START')
, X_Start_Life_Reason_Id => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_START_LIFE_REASON_ID')
, X_Date_End => hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_END')
, X_End_Life_Reason_Id => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_END_LIFE_REASON_ID')
, X_Rltd_Per_Rsds_W_Dsgntr_Flag => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_RLTD_PER_RSDS_W_DSGNTR_FLAG')
, X_Personal_Flag => hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PERSONAL_FLAG')
, X_Sequence_Number => hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SEQUENCE_NUMBER')
, X_Dependent_Flag => hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DEPENDENT_FLAG')
, X_Beneficiary_Flag => hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BENEFICIARY_FLAG')
);
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
,P_EFFECTIVE_DATE =>l_effective_date
,p_object_version_number => l_ovn
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
,p_date_end => l_date_end
);
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
,P_EFFECTIVE_DATE =>l_effective_date
,p_object_version_number => l_ovn
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
,p_date_end => l_date_end
);
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
,P_EFFECTIVE_DATE =>l_effective_date
,p_object_version_number => l_emrg_ovn
,P_CONTACT_RELATIONSHIP_ID => l_emrg_relid
,P_PRIMARY_CONTACT_FLAG => l_primary_contact_flag
,P_DATE_START => z_date_start
);
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
,P_EFFECTIVE_DATE =>l_effective_date
,p_object_version_number => l_emrg_ovn
,P_CONTACT_RELATIONSHIP_ID => l_emrg_relid
,P_PRIMARY_CONTACT_FLAG => l_primary_contact_flag
,P_DATE_START => z_date_start
);
hr_contact_rel_api.update_contact_relationship(
P_VALIDATE => p_validate
--
,P_EFFECTIVE_DATE => l_effective_date
/* SFL changes
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_EFFECTIVE_DATE')
*/
--
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
--
,P_CONTACT_TYPE =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_TYPE')
--
,P_COMMENTS =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CTR_COMMENTS')
--
-- Bug 3617667 : Not passign Primary Cont flag.Its only availaible in Emerg region.
-- ,P_PRIMARY_CONTACT_FLAG => l_primary_contact_flag
--
,P_THIRD_PARTY_PAY_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_THIRD_PARTY_PAY_FLAG')
--
,P_BONDHOLDER_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BONDHOLDER_FLAG')
--
,P_DATE_START =>
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_START')
--
,P_START_LIFE_REASON_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_START_LIFE_REASON_ID')
--
,P_DATE_END =>
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_END')
--
,P_END_LIFE_REASON_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_END_LIFE_REASON_ID')
--
,P_RLTD_PER_RSDS_W_DSGNTR_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_RLTD_PER_RSDS_W_DSGNTR_FLAG')
--
,P_PERSONAL_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PERSONAL_FLAG')
--
/* ,P_SEQUENCE_NUMBER =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SEQUENCE_NUMBER') */
--
,P_DEPENDENT_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DEPENDENT_FLAG')
--
,P_BENEFICIARY_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BENEFICIARY_FLAG')
--
,P_CONT_ATTRIBUTE_CATEGORY =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE_CATEGORY')
--
,P_CONT_ATTRIBUTE1 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE1')
--
,P_CONT_ATTRIBUTE2 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE2')
--
,P_CONT_ATTRIBUTE3 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE3')
--
,P_CONT_ATTRIBUTE4 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE4')
--
,P_CONT_ATTRIBUTE5 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE5')
--
,P_CONT_ATTRIBUTE6 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE6')
--
,P_CONT_ATTRIBUTE7 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE7')
--
,P_CONT_ATTRIBUTE8 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE8')
--
,P_CONT_ATTRIBUTE9 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE9')
--
,P_CONT_ATTRIBUTE10 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE10')
--
,P_CONT_ATTRIBUTE11 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE11')
--
,P_CONT_ATTRIBUTE12 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE12')
--
,P_CONT_ATTRIBUTE13 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE13')
--
,P_CONT_ATTRIBUTE14 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE14')
--
,P_CONT_ATTRIBUTE15 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE15')
--
,P_CONT_ATTRIBUTE16 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE16')
--
,P_CONT_ATTRIBUTE17 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE17')
--
,P_CONT_ATTRIBUTE18 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE18')
--
,P_CONT_ATTRIBUTE19 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE19')
--
,P_CONT_ATTRIBUTE20 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE20')
--
,P_CONT_INFORMATION_CATEGORY =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION_CATEGORY')
--
,P_CONT_INFORMATION1 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION1')
--
,P_CONT_INFORMATION2 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION2')
--
,P_CONT_INFORMATION3 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION3')
--
,P_CONT_INFORMATION4 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION4')
--
,P_CONT_INFORMATION5 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION5')
--
,P_CONT_INFORMATION6 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION6')
--
,P_CONT_INFORMATION7 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION7')
--
,P_CONT_INFORMATION8 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION8')
--
,P_CONT_INFORMATION9 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION9')
--
,P_CONT_INFORMATION10 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION10')
--
,P_CONT_INFORMATION11 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION11')
--
,P_CONT_INFORMATION12 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION12')
--
,P_CONT_INFORMATION13 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION13')
--
,P_CONT_INFORMATION14 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION14')
--
,P_CONT_INFORMATION15 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION15')
--
,P_CONT_INFORMATION16 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION16')
--
,P_CONT_INFORMATION17 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION17')
--
,P_CONT_INFORMATION18 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION18')
--
,P_CONT_INFORMATION19 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION19')
--
,P_CONT_INFORMATION20 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION20')
--
,P_OBJECT_VERSION_NUMBER => l_ovn
--
);
hr_contact_rel_api.update_contact_relationship(
P_VALIDATE => p_validate
--
,P_EFFECTIVE_DATE => l_effective_date
,P_CONTACT_RELATIONSHIP_ID => l_other_relid
--
,P_CONTACT_TYPE =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_TYPE')
--
,P_COMMENTS =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CTR_COMMENTS')
,P_THIRD_PARTY_PAY_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_THIRD_PARTY_PAY_FLAG')
--
,P_BONDHOLDER_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BONDHOLDER_FLAG')
--
,P_DATE_START =>
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_START')
--
-- bug 4775133 ,P_START_LIFE_REASON_ID =>
-- hr_transaction_api.get_NUMBER_value
-- (p_transaction_step_id => p_transaction_step_id
-- ,p_name => 'P_START_LIFE_REASON_ID')
--
,P_DATE_END =>
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATE_END')
--
,P_END_LIFE_REASON_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_END_LIFE_REASON_ID')
--
,P_RLTD_PER_RSDS_W_DSGNTR_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_RLTD_PER_RSDS_W_DSGNTR_FLAG')
--
,P_PERSONAL_FLAG => 'Y'
--
,P_DEPENDENT_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DEPENDENT_FLAG')
--
,P_BENEFICIARY_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BENEFICIARY_FLAG')
--
,P_CONT_ATTRIBUTE_CATEGORY =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE_CATEGORY')
--
,P_CONT_ATTRIBUTE1 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE1')
--
,P_CONT_ATTRIBUTE2 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE2')
--
,P_CONT_ATTRIBUTE3 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE3')
--
,P_CONT_ATTRIBUTE4 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE4')
--
,P_CONT_ATTRIBUTE5 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE5')
--
,P_CONT_ATTRIBUTE6 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE6')
--
,P_CONT_ATTRIBUTE7 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE7')
--
,P_CONT_ATTRIBUTE8 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE8')
--
,P_CONT_ATTRIBUTE9 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE9')
--
,P_CONT_ATTRIBUTE10 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE10')
--
,P_CONT_ATTRIBUTE11 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE11')
--
,P_CONT_ATTRIBUTE12 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE12')
--
,P_CONT_ATTRIBUTE13 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE13')
--
,P_CONT_ATTRIBUTE14 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE14')
--
,P_CONT_ATTRIBUTE15 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE15')
--
,P_CONT_ATTRIBUTE16 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE16')
--
,P_CONT_ATTRIBUTE17 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE17')
--
,P_CONT_ATTRIBUTE18 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE18')
--
,P_CONT_ATTRIBUTE19 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE19')
--
,P_CONT_ATTRIBUTE20 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_ATTRIBUTE20')
--
,P_CONT_INFORMATION_CATEGORY =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION_CATEGORY')
--
,P_CONT_INFORMATION1 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION1')
--
,P_CONT_INFORMATION2 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION2')
--
,P_CONT_INFORMATION3 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION3')
--
,P_CONT_INFORMATION4 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION4')
--
,P_CONT_INFORMATION5 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION5')
--
,P_CONT_INFORMATION6 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION6')
--
,P_CONT_INFORMATION7 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION7')
--
,P_CONT_INFORMATION8 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION8')
--
,P_CONT_INFORMATION9 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION9')
--
,P_CONT_INFORMATION10 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION10')
--
,P_CONT_INFORMATION11 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION11')
--
,P_CONT_INFORMATION12 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION12')
--
,P_CONT_INFORMATION13 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION13')
--
,P_CONT_INFORMATION14 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION14')
--
,P_CONT_INFORMATION15 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION15')
--
,P_CONT_INFORMATION16 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION16')
--
,P_CONT_INFORMATION17 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION17')
--
,P_CONT_INFORMATION18 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION18')
--
,P_CONT_INFORMATION19 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION19')
--
,P_CONT_INFORMATION20 =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONT_INFORMATION20')
--
,P_OBJECT_VERSION_NUMBER => l_other_ovn
--
);
ROLLBACK TO update_cont_relationship;
end if; -- l_process_section = 'DELETE_CONTACTS'
ROLLBACK TO update_cont_relationship;
procedure is_address_updated
(P_CONTACT_RELATIONSHIP_ID in number
,P_DATE_START in date
,p_transaction_step_id IN NUMBER
,p_contact_person_id in number
,p_person_id in number) is
l_date_start date := hr_api.g_date;
select 'Y'
from per_addresses
where person_id = c_person_id
and date_from = c_date_from
and primary_flag='Y';
select 'Y' from hr_api_transaction_steps where transaction_id =
(select transaction_id from hr_api_transaction_steps where transaction_step_id =
c_transaction_step_id) and api_name='HR_PROCESS_ADDRESS_SS.PROCESS_API';
select date_start into l_date_start from per_contact_relationships where
contact_relationship_id = p_contact_relationship_id;
select max(date_start) into l_pds_date_start from per_periods_of_service
where person_id = p_person_id;
select min(effective_start_date) into l_cont_start_date from per_all_people_f
where person_id=p_contact_person_id;
hr_process_contact_ss.g_is_address_updated := true;
end is_address_updated;
|| hr_contact_rel_api.update_contact_relationship()
||
|| Pre Conditions:
||
|| In Arguments:
|| Contains entire list of parameters that are defined in the actual
|| API. For details see peaddapi.pkb file.
||
|| out nocopy Arguments:
||
|| In out nocopy Arguments:
||
|| Post Success:
|| Executes the API call.
||
|| Post Failure:
|| Raises an exception
||
|| Access Status:
|| Public.
||
||===========================================================================
*/
PROCEDURE end_contact_relationship
(p_validate in number default 0
,p_effective_date in date
,p_contact_relationship_id in number
,p_contact_type in varchar2 default hr_api.g_varchar2
,p_comments in clob default hr_api.g_varchar2 -- Bug#13362792
,p_primary_contact_flag in varchar2 default hr_api.g_varchar2
,p_third_party_pay_flag in varchar2 default hr_api.g_varchar2
,p_bondholder_flag in varchar2 default hr_api.g_varchar2
,p_date_start in date default hr_api.g_date
,p_start_life_reason_id in number default hr_api.g_number
,p_date_end in date default hr_api.g_date
,p_end_life_reason_id in number default hr_api.g_number
,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default hr_api.g_varchar2
,p_personal_flag in varchar2 default hr_api.g_varchar2
,p_sequence_number in number default hr_api.g_number
,p_dependent_flag in varchar2 default hr_api.g_varchar2
,p_beneficiary_flag in varchar2 default hr_api.g_varchar2
,p_cont_attribute_category in varchar2 default hr_api.g_varchar2
,p_cont_attribute1 in varchar2 default hr_api.g_varchar2
,p_cont_attribute2 in varchar2 default hr_api.g_varchar2
,p_cont_attribute3 in varchar2 default hr_api.g_varchar2
,p_cont_attribute4 in varchar2 default hr_api.g_varchar2
,p_cont_attribute5 in varchar2 default hr_api.g_varchar2
,p_cont_attribute6 in varchar2 default hr_api.g_varchar2
,p_cont_attribute7 in varchar2 default hr_api.g_varchar2
,p_cont_attribute8 in varchar2 default hr_api.g_varchar2
,p_cont_attribute9 in varchar2 default hr_api.g_varchar2
,p_cont_attribute10 in varchar2 default hr_api.g_varchar2
,p_cont_attribute11 in varchar2 default hr_api.g_varchar2
,p_cont_attribute12 in varchar2 default hr_api.g_varchar2
,p_cont_attribute13 in varchar2 default hr_api.g_varchar2
,p_cont_attribute14 in varchar2 default hr_api.g_varchar2
,p_cont_attribute15 in varchar2 default hr_api.g_varchar2
,p_cont_attribute16 in varchar2 default hr_api.g_varchar2
,p_cont_attribute17 in varchar2 default hr_api.g_varchar2
,p_cont_attribute18 in varchar2 default hr_api.g_varchar2
,p_cont_attribute19 in varchar2 default hr_api.g_varchar2
,p_cont_attribute20 in varchar2 default hr_api.g_varchar2
,p_person_id in number
,p_object_version_number in out nocopy number
,p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in number
,p_action in varchar2
,p_process_section_name in varchar2
,p_review_page_region_code in varchar2 default hr_api.g_varchar2
,p_save_mode in varchar2 default null
-- SFL needs it bug #2082333
,p_login_person_id in number
,p_contact_person_id in number
-- Bug 2723267
,p_contact_operation in varchar2
-- Bug 3152505
,p_end_other_rel in varchar2
,p_other_rel_id in number
) IS
--
cursor get_other_rel_ovn(p_contact_relationship_id number)
is
select object_version_number
from per_contact_relationships
where contact_relationship_id = p_contact_relationship_id
and trunc(sysdate) between nvl(date_start, trunc(sysdate))
and nvl(date_end, trunc(sysdate));
hr_utility.set_location('Calling hr_contact_rel_api.update_contact_relationship', 10);
hr_contact_rel_api.update_contact_relationship(
p_validate => hr_java_conv_util_ss.get_boolean (
p_number => p_validate)
,p_effective_date => p_effective_date
,p_contact_relationship_id => p_contact_relationship_id
,p_contact_type => p_contact_type
,p_comments => p_comments
,p_primary_contact_flag => p_primary_contact_flag
,p_third_party_pay_flag => p_third_party_pay_flag
,p_bondholder_flag => p_bondholder_flag
,p_date_start => p_date_start
,p_start_life_reason_id => p_start_life_reason_id
,p_date_end => p_date_end
,p_end_life_reason_id => p_end_life_reason_id
,p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
,p_personal_flag => p_personal_flag
,p_sequence_number => p_sequence_number
,p_dependent_flag => p_dependent_flag
,p_beneficiary_flag => p_beneficiary_flag
,p_cont_attribute_category => p_cont_attribute_category
,p_cont_attribute1 => p_cont_attribute1
,p_cont_attribute2 => p_cont_attribute2
,p_cont_attribute3 => p_cont_attribute3
,p_cont_attribute4 => p_cont_attribute4
,p_cont_attribute5 => p_cont_attribute5
,p_cont_attribute6 => p_cont_attribute6
,p_cont_attribute7 => p_cont_attribute7
,p_cont_attribute8 => p_cont_attribute8
,p_cont_attribute9 => p_cont_attribute9
,p_cont_attribute10 => p_cont_attribute10
,p_cont_attribute11 => p_cont_attribute11
,p_cont_attribute12 => p_cont_attribute12
,p_cont_attribute13 => p_cont_attribute13
,p_cont_attribute14 => p_cont_attribute14
,p_cont_attribute15 => p_cont_attribute15
,p_cont_attribute16 => p_cont_attribute16
,p_cont_attribute17 => p_cont_attribute17
,p_cont_attribute18 => p_cont_attribute18
,p_cont_attribute19 => p_cont_attribute19
,p_cont_attribute20 => p_cont_attribute20
,p_object_version_number => p_object_version_number
);
hr_contact_rel_api.update_contact_relationship(
p_validate => hr_java_conv_util_ss.get_boolean (
p_number => p_validate)
,p_effective_date => p_effective_date
-- Bug 3152505 : Passing the other contact relationship id
,p_contact_relationship_id => p_other_rel_id
,p_contact_type => p_contact_type
,p_comments => p_comments
,p_primary_contact_flag => p_primary_contact_flag
,p_third_party_pay_flag => p_third_party_pay_flag
,p_bondholder_flag => p_bondholder_flag
,p_date_start => p_date_start
,p_start_life_reason_id => p_start_life_reason_id
,p_date_end => p_date_end
,p_end_life_reason_id => p_end_life_reason_id
,p_rltd_per_rsds_w_dsgntr_flag => p_rltd_per_rsds_w_dsgntr_flag
,p_personal_flag => p_personal_flag
,p_sequence_number => p_sequence_number
,p_dependent_flag => p_dependent_flag
,p_beneficiary_flag => p_beneficiary_flag
,p_cont_attribute_category => p_cont_attribute_category
,p_cont_attribute1 => p_cont_attribute1
,p_cont_attribute2 => p_cont_attribute2
,p_cont_attribute3 => p_cont_attribute3
,p_cont_attribute4 => p_cont_attribute4
,p_cont_attribute5 => p_cont_attribute5
,p_cont_attribute6 => p_cont_attribute6
,p_cont_attribute7 => p_cont_attribute7
,p_cont_attribute8 => p_cont_attribute8
,p_cont_attribute9 => p_cont_attribute9
,p_cont_attribute10 => p_cont_attribute10
,p_cont_attribute11 => p_cont_attribute11
,p_cont_attribute12 => p_cont_attribute12
,p_cont_attribute13 => p_cont_attribute13
,p_cont_attribute14 => p_cont_attribute14
,p_cont_attribute15 => p_cont_attribute15
,p_cont_attribute16 => p_cont_attribute16
,p_cont_attribute17 => p_cont_attribute17
,p_cont_attribute18 => p_cont_attribute18
,p_cont_attribute19 => p_cont_attribute19
,p_cont_attribute20 => p_cont_attribute20
,p_object_version_number => l_other_rel_object_ver_no
);
select max(BLF.NAME) End_Relation_Reason
into l_end_life_reason
from BEN_LER_F BLF
where BLF.LER_ID = P_END_LIFE_REASON_ID;
hr_utility.set_location('Leaving hr_contact_rel_api.update_contact_relationship', 40);
hr_utility.set_message(800, 'HR_UPDATE_NOT_ALLOWED');
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
--
,P_EFFECTIVE_DATE =>l_effective_date
/*
hr_transaction_api.get_DATE_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_EFFECTIVE_DATE')
*/
--
,p_object_version_number => l_ovn
--
-- 9999 What value to pass on.
/* ,p_attribute_update_mode => hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE_UPDATE_MODE')
*/
--
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_RELATIONSHIP_ID')
--
/*
,P_CONTACT_TYPE =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CONTACT_TYPE')
--
,P_COMMENTS =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_COMMENTS')
--
,P_PRIMARY_CONTACT_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PRIMARY_CONTACT_FLAG')
--
,P_THIRD_PARTY_PAY_FLAG =>
hr_transaction_api.get_VARCHAR2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_THIRD_PARTY_PAY_FLAG')
--
,p_bondholder_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_bondholder_flag')
--
,p_date_start =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_date_start')
--
,p_start_life_reason_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_start_life_reason_id')
--
*/
,p_date_end =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_date_end')
--
--bug# 2080032
,p_end_life_reason_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_end_life_reason_id')
--
/*
,p_rltd_per_rsds_w_dsgntr_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_rltd_per_rsds_w_dsgntr_flag')
--
,p_personal_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_personal_flag')
--
,p_sequence_number =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_sequence_number')
--
,p_dependent_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_dependent_flag')
--
,p_beneficiary_flag =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_beneficiary_flag')
--
,p_cont_attribute_category =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute_category')
--
,p_cont_attribute1 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute1')
--
,p_cont_attribute2 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute2')
--
,p_cont_attribute3 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute3')
--
,p_cont_attribute4 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute4')
--
,p_cont_attribute5 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute5')
--
,p_cont_attribute6 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute6')
--
,p_cont_attribute7 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute7')
--
,p_cont_attribute8 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute8')
--
,p_cont_attribute9 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute9')
--
,p_cont_attribute10 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute10')
--
,p_cont_attribute11 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute11')
--
,p_cont_attribute12 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute12')
--
,p_cont_attribute13 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute13')
--
,p_cont_attribute14 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute14')
--
,p_cont_attribute15 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute15')
--
,p_cont_attribute16 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute16')
--
,p_cont_attribute17 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute17')
--
,p_cont_attribute18 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute18')
--
,p_cont_attribute19 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute19')
--
,p_cont_attribute20 =>
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_cont_attribute20')
--
*/
);
hr_contact_rel_api.update_contact_relationship(
p_validate => p_validate
,P_EFFECTIVE_DATE =>l_effective_date
,p_object_version_number => l_other_rel_ovn
,P_CONTACT_RELATIONSHIP_ID =>
hr_transaction_api.get_NUMBER_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_OTHER_REL_ID')
,p_date_end =>
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_date_end')
,p_end_life_reason_id =>
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'p_end_life_reason_id')
);
PROCEDURE delete_transaction_steps(
p_item_type IN varchar2,
p_item_key IN varchar2,
p_actid IN varchar2,
p_login_person_id IN varchar2) IS
l_trans_step_ids hr_util_web.g_varchar2_tab_type;
l_proc varchar2(72) := g_package||'delete_transaction_steps';
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_trans_step_ids(i)
,p_object_version_number => l_trans_obj_vers_num(i)
,p_login_person_id => p_login_person_id);
END delete_transaction_steps;
PROCEDURE delete_transaction_steps(
p_item_type IN varchar2,
p_item_key IN varchar2,
p_actid IN varchar2,
p_login_person_id IN varchar2,
p_mode IN varchar2) IS
l_trans_step_ids hr_util_web.g_varchar2_tab_type;
l_proc varchar2(72) := g_package||'delete_transaction_steps';
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_trans_step_ids(i)
,p_object_version_number => l_trans_obj_vers_num(i)
,p_login_person_id => p_login_person_id);
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_trans_step_ids(i)
,p_object_version_number => l_trans_obj_vers_num(i)
,p_login_person_id => p_login_person_id);
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_trans_step_ids(i)
,p_object_version_number => l_trans_obj_vers_num(i)
,p_login_person_id => p_login_person_id);
END delete_transaction_steps;
procedure update_object_version
(p_transaction_step_id in number
,p_login_person_id in number) is
/*
cursor csr_new_object_number(p_asg_id in number) is
select object_version_number
from per_all_assignments_f
where assignment_id = p_asg_id
and assignment_type = 'E'
order by object_version_number desc;
l_proc varchar2(72) := g_package||'update_object_version';
end update_object_version;
select 'Y'
from per_contact_relationships
where person_id = p_person_id
and primary_contact_flag = 'Y'
and (nvl(date_end,hr_general.end_of_time)
> nvl(p_date_start,hr_general.start_of_time)
and nvl(date_start,hr_general.start_of_time)
< nvl(p_date_end,hr_general.end_of_time));
select count(pcr.contact_relationship_id)
from PER_CONTACT_RELATIONSHIPS pcr
where person_id = (select person_id
from PER_CONTACT_RELATIONSHIPS
where contact_person_id = p_contact_person_id
and contact_relationship_id = P_contact_relationship_id
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end)))
and exists (select 1
from per_contact_relationships con
where con.contact_type = 'EMRG'
and con.contact_person_id = p_contact_person_id
and con.person_id = pcr.person_id
and trunc(sysdate) >= decode(con.date_start,null,trunc(sysdate),trunc(con.date_start))
and trunc(sysdate) < decode(con.date_end,null,trunc(sysdate)+1,trunc(con.date_end)))
and pcr.contact_person_id = p_contact_person_id
and pcr.contact_type <> 'EMRG'
and pcr.personal_flag = 'Y'
and trunc(sysdate) >= decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
and trunc(sysdate) < decode(pcr.date_end,null,trunc(sysdate)+1,trunc(pcr.date_end));
select contact_relationship_id,
HR_GENERAL.DECODE_LOOKUP('CONTACT', contact_type) relationship
from PER_CONTACT_RELATIONSHIPS
where person_id = (select person_id
from PER_CONTACT_RELATIONSHIPS
where contact_person_id = p_contact_person_id
and contact_relationship_id = P_contact_relationship_id
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end)))
and contact_person_id = p_contact_person_id
and contact_type = 'EMRG'
and trunc(sysdate) >= decode(date_start,null,trunc(sysdate),trunc(date_start))
and trunc(sysdate) < decode(date_end,null,trunc(sysdate)+1,trunc(date_end));
select HR_GENERAL.DECODE_LOOKUP('CONTACT', pcr.contact_type) relationship
from PER_CONTACT_RELATIONSHIPS pcr
where pcr.contact_person_id = p_contact_person_id
and pcr.contact_relationship_id = p_contact_relationship_id
and trunc(sysdate) >= decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
and trunc(sysdate) < decode(pcr.date_end,null,trunc(sysdate)+1,trunc(pcr.date_end));
select min(p.date_of_birth) , min(p.effective_start_date)
into l_main_per_date_of_birth , l_main_per_eff_start_date
from per_people_f p
where p.person_id = p_person_id;
select nvl(max(hats1.transaction_step_id),0)
into l_validate_g_per_step_id
from hr_api_transaction_steps hats1
where hats1.item_type = 'HRSSA'
and hats1.item_key = p_item_key
and hats1.api_name in( 'HR_PROCESS_PERSON_SS.PROCESS_API', 'BEN_PROCESS_COBRA_PERSON_SS.PROCESS_API');
SELECT
primary_contact_flag,
pcr.object_version_number ovn
FROM
per_contact_relationships pcr
,per_all_people_f pap
,hr_comments hc
WHERE pcr.contact_relationship_id = p_contact_relationship_id
AND pcr.contact_person_id = pap.person_id
AND p_eff_date BETWEEN pap.effective_start_date and pap.effective_end_date
AND hc.comment_id (+) = pap.comment_id;
hr_contact_rel_api.update_contact_relationship(
p_validate => false
,P_EFFECTIVE_DATE => sysdate
,p_object_version_number => l_emerg_contact_data.ovn
,P_CONTACT_RELATIONSHIP_ID => l_emerg_cont_rel_id
,p_date_start => sysdate
,P_PRIMARY_CONTACT_FLAG => p_primary_contact_flag
);