[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select org_information8
from hr_organization_information
where organization_id=p_business_group_id
and ORG_INFORMATION_CONTEXT='BG Recruitment';
hr_person_api.update_person(p_effective_date => l_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_party_id => p_party_id
,p_employee_number => l_employee_number /* CHECK THIS*/
,p_object_version_number => l_object_version_number
,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
);
select legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select org_information8
from hr_organization_information
where organization_id=p_business_group_id
and ORG_INFORMATION_CONTEXT='BG Recruitment';
procedure update_registered_user
(p_validate IN boolean default false
,p_effective_date IN date
,p_person_id IN number
,p_first_name IN varchar2 default hr_api.g_varchar2
,p_last_name IN varchar2 default hr_api.g_varchar2
,p_date_of_birth IN date default hr_api.g_date
,p_title IN varchar2 default hr_api.g_varchar2
,p_gender IN varchar2 default hr_api.g_varchar2
,p_marital_status IN varchar2 default hr_api.g_varchar2
,p_previous_last_name IN varchar2 default hr_api.g_varchar2
,p_middle_name IN varchar2 default hr_api.g_varchar2
,p_name_suffix IN varchar2 default hr_api.g_varchar2
,p_known_as IN varchar2 default hr_api.g_varchar2
,p_first_name_phonetic IN varchar2 default hr_api.g_varchar2
,p_last_name_phonetic 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
) IS
--
l_proc varchar2(72) := g_package||'update_registered_user';
select effective_start_date,object_version_number,employee_number
from per_all_people_f
where person_id = p_person_id
and l_effective_date between effective_start_date and effective_end_date;
savepoint UPDATE_REGISTERED_USER;
irc_party_bk2.update_registered_user_b
(
p_effective_date => l_effective_date
,p_person_id => p_person_id
,p_first_name => p_first_name
,p_last_name => p_last_name
,p_date_of_birth => l_date_of_birth
,p_title => p_title
,p_gender => p_gender
,p_marital_status => p_marital_status
,p_previous_last_name => p_previous_last_name
,p_middle_name => p_middle_name
,p_name_suffix => p_name_suffix
,p_known_as => p_known_as
,p_first_name_phonetic => p_first_name_phonetic
,p_last_name_phonetic => p_last_name_phonetic
,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_module_name => 'UPDATE_REGISTERED_USER'
,p_hook_type => 'BP'
);
hr_person_api.update_person
(p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => p_person_id
,p_employee_number => l_employee_number
,p_object_version_number => l_object_version_number
,p_last_name => p_last_name
,p_previous_last_name => p_previous_last_name
,p_date_of_birth => l_date_of_birth
,p_first_name => p_first_name
,p_known_as => p_known_as
,p_sex => p_gender
,p_marital_status => l_marital_status
,p_middle_names => p_middle_name
,p_suffix => p_name_suffix
,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_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_person_api.update_person
(p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => p_person_id
,p_employee_number => l_employee_number
,p_object_version_number => l_object_version_number
,p_last_name => p_last_name
,p_previous_last_name => p_previous_last_name
,p_date_of_birth => l_date_of_birth
,p_first_name => p_first_name
,p_known_as => p_known_as
,p_sex => p_gender
,p_marital_status => l_marital_status
,p_middle_names => p_middle_name
,p_suffix => p_name_suffix
,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 => l_last_name_phonetic
,p_per_information2 => p_first_name_phonetic
,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_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_person_api.update_person
(p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => p_person_id
,p_employee_number => l_employee_number
,p_object_version_number => l_object_version_number
,p_last_name => p_last_name
,p_previous_last_name => p_previous_last_name
,p_date_of_birth => l_date_of_birth
,p_first_name => p_first_name
,p_known_as => p_known_as
,p_sex => p_gender
,p_marital_status => l_marital_status
,p_middle_names => p_middle_name
,p_suffix => p_name_suffix
,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_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
);
irc_party_bk2.update_registered_user_a
(
p_effective_date => l_effective_date
,p_person_id => p_person_id
,p_first_name => p_first_name
,p_last_name => p_last_name
,p_date_of_birth => l_date_of_birth
,p_title => p_title
,p_gender => p_gender
,p_marital_status => l_marital_status
,p_previous_last_name => p_previous_last_name
,p_middle_name => p_middle_name
,p_name_suffix => p_name_suffix
,p_known_as => p_known_as
,p_first_name_phonetic => p_first_name_phonetic
,p_last_name_phonetic => p_last_name_phonetic
,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_module_name => 'UPDATE_REGISTERED_USER'
,p_hook_type => 'AP'
);
rollback to UPDATE_REGISTERED_USER;
rollback to UPDATE_REGISTERED_USER;
end update_registered_user;
select count(*)
from per_all_assignments_f paaf
, IRC_AGENCY_VACANCIES AGV1
,per_all_people_f per1
,irc_notification_preferences prefs
where paaf.vacancy_id = p_vacancy_id and paaf.assignment_type = 'A'
and p_effective_date between PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
and paaf.VACANCY_ID = AGV1.VACANCY_ID
and AGV1.AGENCY_ID = FND_PROFILE.VALUE('IRC_AGENCY_NAME')
and per1.person_id=paaf.person_id
and p_effective_date between per1.effective_start_date and per1.effective_end_date
and per1.party_id=prefs.party_id
and prefs.agency_id=agv1.agency_id
and p_effective_date between nvl(agv1.start_date,p_effective_date)
and nvl(agv1.end_date,p_effective_date);
select AGV.MAX_ALLOWED_APPLICANTS
from IRC_AGENCY_VACANCIES AGV
where agv.vacancy_id = p_vacancy_id
and AGV.AGENCY_ID = FND_PROFILE.VALUE('IRC_AGENCY_NAME');
l_party_last_update_date date;
select per.person_id
,per.object_version_number
,per.current_employee_flag
,per.current_applicant_flag
,per.current_npw_flag
,per.applicant_number
from per_all_people_f per
where per.party_id = p_party_id
and p_effective_date between per.effective_start_date and per.effective_end_date
and business_group_id=p_business_group_id;
select null
from per_all_people_f per
where per.party_id = p_party_id
and business_group_id=p_business_group_id;
select last_update_date
,party_id
,last_name
,first_name
,date_of_birth
,title
,sex
,marital_status
,previous_last_name
,middle_names
,suffix
,known_as
,per_information1
,per_information2
,per_information18
,per_information19
from per_all_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select business_group_id
,organization_id
,job_id
,position_id
,grade_id
,people_group_id
,location_id
,recruiter_id
,manager_id
from per_all_vacancies
where vacancy_id = p_vacancy_id;
select assignment_id,object_version_number
from per_all_assignments_f
where person_id=p_person_id
and effective_start_date = p_effective_date
and assignment_type='A';
SELECT pos.TIME_NORMAL_START
, pos.TIME_NORMAL_FINISH
, pos.working_hours
, pos.frequency
FROM hr_all_positions_f pos
WHERE pos.position_id = p_position_id
AND p_effective_date between effective_start_date and effective_end_date;
SELECT org.org_information1
, org.org_information2
, fnd_number.canonical_to_number(org.org_information3) normal_hours
, org.org_information4
FROM HR_ORGANIZATION_INFORMATION org
WHERE org.organization_id = p_organization_id
AND org.org_information_context(+) = 'Work Day Information';
SELECT bus.default_start_time
, bus.default_end_time
, fnd_number.canonical_to_number(bus.working_hours)
, bus.frequency
FROM per_business_groups bus
WHERE bus.business_group_id = p_business_group_id;
fetch csr_party_details into l_party_last_update_date
,l_party_id
,l_last_name
,l_first_name
,l_date_of_birth
,l_title
,l_gender
,l_marital_status
,l_previous_last_name
,l_middle_name
,l_name_suffix
,l_known_as
,l_per_information1
,l_per_information2
,l_per_information18
,l_per_information19;
hr_assignment_api.update_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_posting_content_id => p_posting_content_id
,p_supervisor_id => l_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_people_group_id => l_people_group_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_assignment_api.update_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_vacancy_id => p_vacancy_id
,p_posting_content_id => p_posting_content_id
,p_organization_id => nvl(l_organization_id,l_business_group_id)
,p_job_id => l_job_id
,p_position_id => l_position_id
,p_grade_id => l_grade_id
,p_people_group_id => l_people_group_id
,p_location_id => l_location_id
,p_recruiter_id => l_recruiter_id
,p_supervisor_id => l_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_assignment_api.update_apl_asg
(p_effective_date => l_application_received_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_vacancy_id => p_vacancy_id
,p_posting_content_id => p_posting_content_id
,p_organization_id => nvl(l_organization_id,l_business_group_id)
,p_job_id => l_job_id
,p_position_id => l_position_id
,p_grade_id => l_grade_id
,p_people_group_id => l_people_group_id
,p_location_id => l_location_id
,p_recruiter_id => l_recruiter_id
,p_supervisor_id => l_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select sys_guid() into l_guid from dual;
fnd_grants_pkg.insert_row
(
X_ROWID => srowId,
X_GRANT_GUID => l_guid,
X_GRANTEE_TYPE => 'USER',
X_GRANTEE_KEY => upper(p_user_name),
X_MENU_ID => p_menu_id,
X_START_DATE => trunc(sysdate),
X_END_DATE => null,
X_OBJECT_ID => '-1',
X_INSTANCE_TYPE => 'GLOBAL',
X_INSTANCE_SET_ID => null,
X_INSTANCE_PK1_VALUE => '*NULL*',
X_INSTANCE_PK2_VALUE => '*NULL*',
X_INSTANCE_PK3_VALUE => '*NULL*',
X_INSTANCE_PK4_VALUE => '*NULL*',
X_INSTANCE_PK5_VALUE => '*NULL*',
X_PROGRAM_NAME => 'IRC_API',
X_PROGRAM_TAG => null,
X_CREATION_DATE => sysdate,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id,
X_CTX_SECGRP_ID => p_sec_group_id,
X_CTX_RESP_ID => p_resp_id,
X_CTX_RESP_APPL_ID => p_resp_appl_id,
X_NAME => p_grant_name,
X_DESCRIPTION => p_description
);
select menu_id from fnd_menus where menu_name=upper(p_menu_name);
fnd_user_resp_groups_api.Insert_Assignment
(user_id => l_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
select nls_language
from fnd_languages
where language_code=p_language;
select encrypted_foundation_password
from fnd_user
where user_name = p_guestUserName
and trunc(sysdate) between start_date and
nvl(end_date,trunc(sysdate));
procedure update_user (
p_user_name in varchar2,
p_owner in varchar2,
p_unencrypted_password in varchar2 default null,
p_encrypted_user_password in varchar2 default null,
p_session_number in number default null,
p_start_date in date default null,
p_end_date in date default null,
p_last_logon_date in date default null,
p_description in varchar2 default null,
p_password_date in date default null,
p_password_accesses_left in number default null,
p_password_lifespan_accesses in number default null,
p_password_lifespan_days in number default null,
p_employee_id in number default null,
p_email_address in varchar2 default null,
p_fax in varchar2 default null,
p_customer_id in number default null,
p_supplier_id in number default null,
p_old_password in varchar2 default null) is
l_new_user_name fnd_user.user_name%type;
select upper(email_address)
from fnd_user
where user_name = p_user_name;
fnd_user_pkg.UpdateUser (
x_user_name => p_user_name,
x_owner => p_owner,
x_unencrypted_password => p_unencrypted_password,
x_session_number => p_session_number,
x_start_date => p_start_date,
x_end_date => p_end_date,
x_last_logon_date => p_last_logon_date,
x_description => p_description,
x_password_date => p_password_date,
x_password_accesses_left => p_password_accesses_left,
x_password_lifespan_accesses => p_password_lifespan_accesses,
x_password_lifespan_days => p_password_lifespan_days,
x_employee_id => p_employee_id,
x_email_address => p_email_address,
x_fax => p_fax,
x_customer_id => p_customer_id,
x_supplier_id => p_supplier_id);
end update_user;
select 1 from fnd_user
where user_name=upper(p_current_email_address);
select per1.party_id,count(*)
from per_all_people_f per1
where UPPER(per1.last_name)=UPPER(p_last_name)
and ( UPPER(per1.first_name)=UPPER(nvl(p_first_name,per1.first_name))
or (per1.first_name is null and p_first_name is null))
and ( UPPER(per1.middle_names)= UPPER(nvl(p_middle_names,per1.middle_names))
or (per1.middle_names is null and p_middle_names is null))
and ( UPPER(per1.previous_last_name)= UPPER(nvl(p_previous_last_name,per1.previous_last_name))
or (per1.previous_last_name is null and p_previous_last_name is null))
and ( UPPER(per1.employee_number)= UPPER(nvl(p_employee_number,per1.employee_number))
or (per1.employee_number is null and p_employee_number is null))
and ( UPPER(per1.national_identifier)= UPPER(nvl(p_national_identifier,per1.national_identifier))
or (per1.national_identifier is null and p_national_identifier is null))
and (per1.date_of_birth=nvl(l_date_of_birth,per1.date_of_birth)
or (per1.date_of_birth is null and p_date_of_birth is null))
and ( UPPER(per1.email_address)= UPPER(nvl(p_email_address,per1.email_address))
or (per1.email_address is null and p_email_address is null))
and (p_home_phone_number is null
or exists (select 1 from per_phones phns
where phns.parent_id=per1.person_id
and phns.parent_table='PER_ALL_PEOPLE_F'
and phns.phone_type in ('H1','H2','H3')
and phns.phone_number = p_home_phone_number))
and (p_work_phone_number is null
or exists (select 1 from per_phones phns
where phns.parent_id=per1.person_id
and phns.parent_table='PER_ALL_PEOPLE_F'
and phns.phone_type in ('W1','W2','W3')
and phns.phone_number = p_work_phone_number))
and (p_address_line_1 is null
or exists (select 1 from per_addresses addr
where addr.person_id=per1.person_id
and addr.address_type in ('H','REC','HOME')))
and (p_manager_last_name is null
or exists (select 1 from per_all_people_f per2
,per_all_assignments_f asg2
where asg2.person_id=per1.person_id
and asg2.assignment_type in ('E','C')
and asg2.supervisor_id=per2.person_id
and asg2.effective_start_date between per2.effective_start_date and per2.effective_end_date
and UPPER(per2.last_name)=UPPER(p_manager_last_name)))
and exists(SELECT 1
FROM per_person_types typ
,per_person_type_usages_f ptu
WHERE typ.system_person_type = 'EX_EMP'
AND typ.person_type_id = ptu.person_type_id
AND sysdate BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = per1.person_id)
group by per1.party_id;
select 1
from per_all_people_f per1
where per1.party_id=p_party_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
and current_employee_flag='Y';
select per1.person_id
from per_all_people_f per1
where per1.party_id=p_party_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
and per1.business_group_id = fnd_profile.value('IRC_REGISTRATION_BG_ID');
select notif.person_id
from irc_notification_preferences notif
where notif.party_id=p_party_id;
select prefs.object_id
from irc_search_criteria prefs
where prefs.object_id=p_person_id
and prefs.object_type='WPREF';
select per1.person_id
from per_all_people_f per1
where per1.party_id=p_party_id
and per1.current_employee_flag='Y'
and per1.effective_start_date
select per1.person_id
from per_all_people_f per1
where per1.party_id=p_party_id
and per1.effective_start_date
select per1.business_group_id
,per1.object_version_number
,per1.employee_number
,per1.effective_start_date
from per_all_people_f per1
where per1.person_id=p_person_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
select 1 from per_person_type_usages_f ptuf
where ptuf.person_id=p_person_id
and ptuf.person_type_id=p_person_type_id
and trunc(sysdate) between ptuf.effective_start_date and ptuf.effective_end_date;
select fusr.user_name
from fnd_user fusr
where fusr.employee_id=p_person_id
and trunc(sysdate) between fusr.start_date and
nvl(fusr.end_date,trunc(sysdate))
union
select fusr.user_name
from fnd_user fusr
where fusr.customer_id=p_party_id
and trunc(sysdate) between fusr.start_date and
nvl(fusr.end_date,trunc(sysdate));
select 1
from fnd_user fusr
where fusr.user_name=p_user_name
and fusr.employee_id is not null;
select fusr.user_id
from fnd_user fusr
where fusr.user_name=p_user_name;
select menu_id from fnd_menus where menu_name=upper(p_menu_name);
select fusr.person_party_id
from fnd_user fusr
where fusr.user_name=p_user_name;
select nls_language
from fnd_languages
where language_code=p_language;
l_dt_mode:='UPDATE';
hr_person_api.update_person
(p_validate => p_validate
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_dt_mode
,p_person_id => l_person_id
,p_employee_number => l_employee_number
,p_email_address => p_current_email_address
,p_object_version_number => l_person_ovn
,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
);
fnd_user_pkg.UpdateUserParty(
x_user_name => p_user_name,
x_owner => 'CUST',
x_person_party_id => l_party_id);
fnd_user_pkg.UpdateUser(
x_user_name => p_user_name,
x_owner => 'CUST',
x_employee_id => l_person_id);
fnd_user_resp_groups_api.Insert_Assignment
(user_id => l_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
fnd_user_resp_groups_api.Update_Assignment
(user_id => l_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
select nls_language
from fnd_languages
where language_code=p_language;
select user_id, employee_id, person_party_id
from fnd_user
where user_name=upper(p_user_name);
select nvl(fu.email_address, hzp.email_address)
from fnd_user fu, hz_parties hzp
where user_name=upper(p_user_name)
and fu.person_party_id=hzp.party_id(+);
select notification_preference_id
from irc_notification_preferences
where party_id=p_party_id;
select per1.business_group_id,per1.object_version_number,per1.employee_number
,per1.effective_start_date
from per_all_people_f per1
where per1.person_id=p_person_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
select 1 from per_person_type_usages_f ptuf
where ptuf.person_id=p_person_id
and ptuf.person_type_id=p_person_type_id
and trunc(sysdate) between ptuf.effective_start_date and ptuf.effective_end_date;
select menu_id from fnd_menus where menu_name=upper(p_menu_name);
fnd_user_pkg.UpdateUser
(x_user_name => p_user_name
,x_owner => 'CUST'
,x_employee_id => l_person_id);
l_dt_mode:='UPDATE';
hr_person_api.update_person
(p_validate => false
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_dt_mode
,p_person_id => l_person_id
,p_object_version_number => l_person_ovn
,p_employee_number => l_employee_number
,p_email_address => l_email_address
,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
);
fnd_user_resp_groups_api.Insert_Assignment
(user_id => l_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
fnd_user_resp_groups_api.Update_Assignment
(user_id => l_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
select user_id, employee_id, person_party_id
from fnd_user
where user_name=upper(p_user_name);
select per1.business_group_id
from per_all_people_f per1
where per1.person_id=p_person_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
select 1 from per_person_type_usages_f ptuf
where ptuf.person_id=p_person_id
and ptuf.person_type_id=p_person_type_id
and trunc(sysdate) between ptuf.effective_start_date and ptuf.effective_end_date;
select responsibility_id
from fnd_user_resp_groups
where user_id=p_user_id and responsibility_id=p_responsibility_id
and responsibility_application_id = p_resp_appl_id
and security_group_id = p_security_group_id;
select notification_preference_id
from irc_notification_preferences
where party_id=p_party_id;
password_update_failed exception;
raise PASSWORD_UPDATE_FAILED;
select employee_id, person_party_id
from fnd_user
where user_id=p_user_id;
fnd_user_resp_groups_api.Insert_Assignment
(user_id => p_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
fnd_user_resp_groups_api.Update_Assignment
(user_id => p_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_security_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
fnd_user_resp_groups_api.Insert_Assignment
(user_id => p_user_id
,responsibility_id => p_resp_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_sec_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
fnd_user_resp_groups_api.Update_Assignment
(user_id => p_user_id
,responsibility_id => p_resp_id
,responsibility_application_id => p_resp_appl_id
,security_group_id => p_sec_group_id
,start_date => trunc(sysdate)
,end_date => null
,description => ' ' -- ### description was supposed to default
-- to null... but does not look like it has
);
select menu_id from fnd_menus where menu_name=upper(p_menu_name);
UPDATE fnd_user
SET password_date=NULL
WHERE user_name=upper(p_user_name);
select nls_language
from fnd_languages
where language_code=p_language;
select fu.user_name
from fnd_user fu
where fu.person_party_id=p_party_id
and trunc(sysdate) between fu.start_date and nvl(fu.end_date,trunc(sysdate));
select 1
from per_all_people_f ppf,
per_all_assignments_f paf
where ppf.party_id = p_party_id
and paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date;
select fu.user_name
from fnd_user fu
where fu.person_party_id=p_party_id
and trunc(sysdate) between fu.start_date and nvl(fu.end_date,trunc(sysdate));
select paf.assignment_id
from per_all_people_f ppf,
per_all_assignments_f paf,
irc_offers iof,
irc_offer_status_history history
where ppf.party_id = p_party_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and paf.person_id = ppf.person_id
and paf.assignment_type = 'O'
and paf.ASSIGNMENT_ID = iof.OFFER_ASSIGNMENT_ID
and iof.LATEST_OFFER = 'Y'
AND history.offer_id = iof.offer_id
and not exists
(SELECT 1
FROM irc_offer_status_history iosh1
WHERE iosh1.offer_id = HISTORY.offer_id
AND iosh1.status_change_date > HISTORY.status_change_date
)
AND HISTORY.offer_status_history_id =
(SELECT MAX(iosh2.offer_status_history_id)
FROM irc_offer_status_history iosh2
WHERE iosh2.offer_id = HISTORY.offer_id
AND iosh2.status_change_date = HISTORY.status_change_date
)
and (iof.OFFER_STATUS <> 'CLOSED' OR (iof.OFFER_STATUS = 'CLOSED' AND HISTORY.change_reason ='APL_ACCEPTED'));
select count(pav.vacancy_id)
from per_all_people_f ppf,
per_all_assignments_f paf,
per_all_vacancies pav
where ppf.party_id = p_party_id
and paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and paf.vacancy_id = pav.vacancy_id;
select count(pav.vacancy_id)
from per_people_f ppf,
per_assignments_f2 paf,
per_vacancies pav
where ppf.party_id = p_party_id
and paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and paf.vacancy_id = pav.vacancy_id;
select 1
from per_all_people_f ppf_source
, per_all_people_f ppf_target
where ppf_source.party_id = p_souce_party_id
and ppf_target.party_id = p_target_party_id
and ppf_target.effective_start_date > ppf_source.effective_start_date
and ppf_target.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_target.party_id)
and ppf_source.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_source.party_id);
select paf.assignment_id
from per_all_people_f ppf,
per_all_assignments_f paf
where ppf.party_id = p_target_party_id
and paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and exists (select null
from per_all_people_f ppf1,
per_all_assignments_f paf1
where ppf1.party_id = p_souce_party_id
and paf1.person_id = ppf1.person_id
and trunc(sysdate) between paf1.effective_start_date and paf1.effective_end_date
and paf.vacancy_id = nvl(paf1.vacancy_id, -99));
select person_id
from per_all_people_f
where party_id = c_party_id
and trunc(sysdate) between effective_start_date and effective_end_date;
select pet.system_person_type
from per_all_people_f per,
per_person_types pet
where per.person_type_id = pet.person_type_id
and per.person_id = c_person_id
and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
select person_type_usage_id
from per_person_type_usages_f
where person_id = c_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
Select 1
From PER_ALL_PEOPLE_F ppf
Where trunc(sysdate) = (select min(effective_start_date) from per_all_people_f where party_id=ppf.party_id)
AND ppf.party_id=p_party_id;
l_party_last_update_date date;
select per.person_id
,per.object_version_number
,per.current_employee_flag
,per.current_applicant_flag
,per.current_npw_flag
,per.applicant_number
from per_all_people_f per
where per.party_id = p_party_id
and p_effective_date between per.effective_start_date and per.effective_end_date
and business_group_id = p_business_group_id;
select null
from per_all_people_f per
where per.party_id = p_party_id
and business_group_id=p_business_group_id;
select last_update_date
,party_id
,last_name
,first_name
,date_of_birth
,title
,sex
,marital_status
,previous_last_name
,middle_names
,suffix
,known_as
,per_information1
,per_information2
,per_information18
,per_information19
from per_all_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select assignment_id,object_version_number
from per_all_assignments_f
where person_id = p_person_id
and effective_start_date = p_effective_date
and assignment_type='A';
SELECT org.org_information1
, org.org_information2
, fnd_number.canonical_to_number(org.org_information3) normal_hours
, org.org_information4
FROM HR_ORGANIZATION_INFORMATION org
WHERE org.organization_id = p_organization_id
AND org.org_information_context(+) = 'Work Day Information';
SELECT bus.default_start_time
, bus.default_end_time
, fnd_number.canonical_to_number(bus.working_hours)
, bus.frequency
FROM per_business_groups bus
WHERE bus.business_group_id = p_business_group_id;
fetch csr_party_details into l_party_last_update_date
,l_party_id
,l_last_name
,l_first_name
,l_date_of_birth
,l_title
,l_gender
,l_marital_status
,l_previous_last_name
,l_middle_name
,l_name_suffix
,l_known_as
,l_per_information1
,l_per_information2
,l_per_information18
,l_per_information19;
hr_utility.set_location('Calling hr_assignment_api.update_apl_asg ', 110);
hr_assignment_api.update_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_supervisor_id => p_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_people_group_id => l_people_group_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location('Calling hr_assignment_api.update_apl_asg ', 130);
hr_assignment_api.update_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_supervisor_id => p_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_people_group_id => l_people_group_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location('Calling hr_assignment_api.update_apl_asg ', 150);
hr_assignment_api.update_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_organization_id => nvl(p_organization_id,p_business_group_id)
,p_job_id => l_job_id
,p_position_id => l_position_id
,p_grade_id => l_grade_id
,p_people_group_id => l_people_group_id
,p_location_id => l_location_id
,p_recruiter_id => l_recruiter_id
,p_supervisor_id => p_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select pbg.legislation_code into l_legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
hr_utility.set_location('Calling hr_assignment_api.update_apl_asg ',220);
hr_assignment_api.update_apl_asg
(p_effective_date => l_application_received_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_organization_id => nvl(p_organization_id,p_business_group_id)
,p_job_id => l_job_id
,p_position_id => l_position_id
,p_grade_id => l_grade_id
,p_people_group_id => l_people_group_id
,p_location_id => l_location_id
,p_recruiter_id => l_recruiter_id
,p_supervisor_id => p_manager_id
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments=> l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_frequency => l_frequency
,p_normal_hours => l_normal_hours
,p_time_normal_finish => l_time_normal_finish
,p_time_normal_start => l_time_normal_start
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
insert into per_vac_linked_assignments(
VAC_ASG_LINK_ID ,
VACANCY_ID ,
SRC_APL_ASG_ID ,
TGT_APL_ASG_ID ,
START_DATE ,
END_DATE,
OBJECT_VERSION_NUMBER
) values( l_link_id
,p_vacancy_id
,p_src_apl_asg_id
,p_tgt_apl_asg_id
,sysdate
,sysdate
,1);