[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_last_update_date in date
,p_allow_access in varchar2 default null
,p_user_guid in raw default null
,p_visitor_resp_key in varchar2 default null
,p_visitor_resp_appl_id in number default null
,p_security_group_key in varchar2 default null
,p_pending_data_id out nocopy number
) is
--
-- Declare cursors and local variables
--
l_pending_data_id number(15);
l_last_update_date date;
select fnd_user_pkg.GetReEncryptedPassword(user_name,'LOADER') as encrypted_user_password,
user_guid
from fnd_user
where user_name = upper(p_email_address);
l_last_update_date := trunc(p_last_update_date);
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_pending_data_id => l_pending_data_id
,p_user_guid => l_user_guid
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
procedure UPDATE_PENDING_DATA
(p_validate in boolean default false
,p_pending_data_id in number
,p_email_address in varchar2 default hr_api.g_varchar2
,p_last_name in varchar2 default hr_api.g_varchar2
,p_vacancy_id in number default hr_api.g_number
,p_first_name in varchar2 default hr_api.g_varchar2
,p_user_password in varchar2 default hr_api.g_varchar2
,p_resume_file_name in varchar2 default hr_api.g_varchar2
,p_resume_description in varchar2 default hr_api.g_varchar2
,p_resume_mime_type in varchar2 default hr_api.g_varchar2
,p_source_type in varchar2 default hr_api.g_varchar2
,p_job_post_source_name in varchar2 default hr_api.g_varchar2
,p_posting_content_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_processed in varchar2 default hr_api.g_varchar2
,p_sex in varchar2 default hr_api.g_varchar2
,p_date_of_birth in date default hr_api.g_date
,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_error_message in varchar2 default hr_api.g_varchar2
,p_creation_date in date default hr_api.g_date
,p_last_update_date in date default hr_api.g_date
,p_allow_access in varchar2 default hr_api.g_varchar2
,p_user_guid in raw default null
,p_visitor_resp_key in varchar2 default hr_api.g_varchar2
,p_visitor_resp_appl_id in number default hr_api.g_number
,p_security_group_key in varchar2 default hr_api.g_varchar2
) is
--
-- Declare cursors and local variables
--
l_date_of_birth date;
l_last_update_date date;
l_proc varchar2(72) := g_package||'UPDATE_PENDING_DATA';
savepoint UPDATE_PENDING_DATA;
l_last_update_date := trunc(p_last_update_date);
IRC_PENDING_DATA_BK2.UPDATE_PENDING_DATA_b
(p_email_address => p_email_address
,p_last_name => p_last_name
,p_vacancy_id => p_vacancy_id
,p_first_name => p_first_name
,p_user_password => p_user_password
,p_resume_file_name => p_resume_file_name
,p_resume_description => p_resume_description
,p_resume_mime_type => p_resume_mime_type
,p_source_type => p_source_type
,p_job_post_source_name => p_job_post_source_name
,p_posting_content_id => p_posting_content_id
,p_person_id => p_person_id
,p_processed => p_processed
,p_sex => p_sex
,p_date_of_birth => l_date_of_birth
,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_error_message => p_error_message
,p_creation_date => l_creation_date
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
(p_module_name => 'UPDATE_PENDING_DATA'
,p_hook_type => 'BP'
);
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_user_guid => p_user_guid
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
IRC_PENDING_DATA_BK2.UPDATE_PENDING_DATA_a
(p_email_address => p_email_address
,p_last_name => p_last_name
,p_vacancy_id => p_vacancy_id
,p_first_name => p_first_name
,p_user_password => p_user_password
,p_resume_file_name => p_resume_file_name
,p_resume_description => p_resume_description
,p_resume_mime_type => p_resume_mime_type
,p_source_type => p_source_type
,p_job_post_source_name => p_job_post_source_name
,p_posting_content_id => p_posting_content_id
,p_person_id => p_person_id
,p_processed => p_processed
,p_sex => p_sex
,p_date_of_birth => l_date_of_birth
,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_error_message => p_error_message
,p_creation_date => l_creation_date
,p_last_update_date => l_last_update_date
,p_allow_access => p_allow_access
,p_visitor_resp_key => p_visitor_resp_key
,p_visitor_resp_appl_id => p_visitor_resp_appl_id
,p_security_group_key => p_security_group_key
);
(p_module_name => 'UPDATE_PENDING_DATA'
,p_hook_type => 'AP'
);
rollback to UPDATE_PENDING_DATA;
rollback to UPDATE_PENDING_DATA;
end UPDATE_PENDING_DATA;
procedure DELETE_PENDING_DATA
(p_validate in boolean default false
,p_pending_data_id in number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'DELETE_PENDING_DATA';
savepoint DELETE_PENDING_DATA;
IRC_PENDING_DATA_BK3.DELETE_PENDING_DATA_b
(p_pending_data_id => p_pending_data_id
);
(p_module_name => 'DELETE_PENDING_DATA'
,p_hook_type => 'BP'
);
IRC_PENDING_DATA_BK3.DELETE_PENDING_DATA_a
(p_pending_data_id => p_pending_data_id
);
(p_module_name => 'DELETE_PENDING_DATA'
,p_hook_type => 'AP'
);
rollback to DELETE_PENDING_DATA;
rollback to DELETE_PENDING_DATA;
end DELETE_PENDING_DATA;
select user_id
from fnd_user
where user_name= upper(p_user_name);
select employee_id
from fnd_user
where user_name= upper(p_user_name);
select responsibility_id,application_id
from fnd_responsibility
where responsibility_key = p_resp_key;
select security_group_id
from fnd_security_groups
where security_group_key = p_sec_group_key;
select node_id
from fnd_nodes
where lower(node_name)=lower(p_server_name);
select ipd.resume_file_name
,ipd.resume_description
,ipd.resume_mime_type
,ipd.resume
,ido.document_id
,ido.object_version_number
,ido.person_id
,ido.party_id
,ido.assignment_id
from irc_pending_data ipd, irc_documents ido
where ipd.person_id = ido.person_id(+)
and ipd.resume_file_name = ido.file_name(+)
and ido.type(+) = 'RESUME'
and ido.end_date(+) is null
and ipd.resume_file_name is not null
and ipd.pending_data_id = p_pending_data_id;
select ppf.person_id
,ppf.object_version_number
,ppf.employee_number
from per_all_people_f ppf
where trunc(sysdate) between
ppf.effective_start_date and ppf.effective_end_date
and ppf.party_id in (select party_id from per_all_people_f
where person_id=p_person_id
and trunc(sysdate) between
effective_start_date and effective_end_date)
and ppf.business_group_id in
(select business_group_id from per_all_vacancies
where vacancy_id=p_vacancy_id);
select ipd.pending_data_id
,ipd.email_address
,ipd.vacancy_id
,ipd.last_name
,ipd.first_name
,ipd.user_password
,ipd.posting_content_id
,ipd.sex
,ipd.date_of_birth
,ipd.per_information_category
,ipd.per_information1
,ipd.per_information2
,ipd.per_information3
,ipd.per_information4
,ipd.per_information5
,ipd.per_information6
,ipd.per_information7
,ipd.per_information8
,ipd.per_information9
,ipd.per_information10
,ipd.per_information11
,ipd.per_information12
,ipd.per_information13
,ipd.per_information14
,ipd.per_information15
,ipd.per_information16
,ipd.per_information17
,ipd.per_information18
,ipd.per_information19
,ipd.per_information20
,ipd.per_information21
,ipd.per_information22
,ipd.per_information23
,ipd.per_information24
,ipd.per_information25
,ipd.per_information26
,ipd.per_information27
,ipd.per_information28
,ipd.per_information29
,ipd.per_information30
,ipd.creation_date
,usr.user_id
,usr.employee_id
,ipd.allow_access
,ipd.user_guid
,ipd.visitor_resp_key
,ipd.visitor_resp_appl_id
,ipd.security_group_key
from irc_pending_data ipd, fnd_user usr
where upper(ipd.email_address) = usr.user_name(+)
and ipd.processed is null
order by ipd.creation_date asc;
select 1
from per_all_assignments_f asg,
per_assignment_status_types_v ast,
per_all_people_f ppf,
irc_assignment_statuses ias,
per_all_people_f linkppf,
per_assignment_status_types_v ast1
where asg.vacancy_id = p_vacancy_id
and asg.effective_start_date=(select max(effective_start_date)
from per_assignments_f asg2
where asg.assignment_id=asg2.assignment_id
and asg2.effective_start_date<=sysdate+1)
and asg.person_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and linkppf.person_id = p_person_id
and asg.assignment_id = ias.assignment_id
and asg.assignment_status_type_id = ast1.assignment_status_type_id
and (ias.assignment_status_id = (select max(inn.assignment_status_id)
from irc_assignment_statuses inn
where inn.assignment_id = asg.assignment_id)
or ias.assignment_status_id is null)
and ias.assignment_status_type_id = ast.assignment_status_type_id
and ppf.party_id = linkppf.party_id
and trunc(sysdate) between linkppf.effective_start_date and linkppf.effective_end_date;
select 1 from per_all_vacancies pav, per_recruitment_activities pra,
per_recruitment_activity_for prf, irc_all_recruiting_sites ias
where pav.vacancy_id = prf.vacancy_id AND
prf.recruitment_activity_id = pra.recruitment_activity_id AND
trunc(p_application_date) between PRA.date_start and nvl(PRA.date_end,trunc(p_application_date))
AND pra.recruiting_site_id = ias.recruiting_site_id and ias.internal='Y'
and pav.vacancy_id = p_vacancy_id;
select employee_id, user_id into l_person_id, l_new_user_id
from fnd_user
where user_name = upper(l_data.email_address);
select employee_id into l_person_id
from fnd_user
where user_name = upper(l_data.email_address);
update irc_pending_data
set person_id = l_person_id
,last_update_date = trunc(sysdate)
where pending_data_id = l_data.pending_data_id;
Fnd_file.put_line(FND_FILE.LOG,'Updated irc_pending_data with person_id:'||l_person_id);
irc_document_api.update_document_track
(p_effective_date => l_data.creation_date
,p_document_id => l_document_id
,p_mime_type => l_resume_mime_type
,p_type => 'RESUME'
,p_file_name => l_resume_file_name
,p_description => l_resume_description
,p_person_id => l_doc_person_id
,p_party_id => l_doc_party_id
,p_end_date => l_end_date
,p_assignment_id => l_assignment_id
,p_object_version_number => l_doc_ovn
,p_new_doc_id => l_new_doc_id
);
Fnd_file.put_line(FND_FILE.LOG,'Updated Resume document for the Candidate:'||l_document_id);
update irc_documents set binary_doc=l_resume
where document_id = l_new_doc_id;
select per_people_s.nextval into l_applicant_person_id from dual;
Vacancy BG and then update person record with EEO information
after applying the processed flag is changed to 'A' so that if process
run again 'A' rows won't be picked up.
these API calls are present in PerAllPeopleFEOImpl.java */
select per_assignments_s.nextval into l_applicant_assg_id from dual;
hr_person_api.update_person
(p_effective_date => l_data.creation_date
,p_person_id => l_applicant_person_id
,p_datetrack_update_mode => 'CORRECTION'
,p_object_version_number => l_per_ovn
,p_last_name => l_data.last_name
,p_date_of_birth => nvl(l_data.date_of_birth,hr_api.g_date)
,p_email_address => l_data.email_address
,p_first_name => l_data.first_name
,p_sex => nvl(l_data.sex,hr_api.g_varchar2)
,p_employee_number => l_emp_number
,p_per_information_category => nvl(l_data.per_information_category,hr_api.g_varchar2)
,p_per_information1 => nvl(l_data.per_information1,hr_api.g_varchar2)
,p_per_information2 => nvl(l_data.per_information2,hr_api.g_varchar2)
,p_per_information3 => nvl(l_data.per_information3,hr_api.g_varchar2)
,p_per_information4 => nvl(l_data.per_information4,hr_api.g_varchar2)
,p_per_information5 => nvl(l_data.per_information5,hr_api.g_varchar2)
,p_per_information6 => nvl(l_data.per_information6,hr_api.g_varchar2)
,p_per_information7 => nvl(l_data.per_information7,hr_api.g_varchar2)
,p_per_information8 => nvl(l_data.per_information8,hr_api.g_varchar2)
,p_per_information9 => nvl(l_data.per_information9,hr_api.g_varchar2)
,p_per_information10 => nvl(l_data.per_information10,hr_api.g_varchar2)
,p_per_information11 => nvl(l_data.per_information11,hr_api.g_varchar2)
,p_per_information12 => nvl(l_data.per_information12,hr_api.g_varchar2)
,p_per_information13 => nvl(l_data.per_information13,hr_api.g_varchar2)
,p_per_information14 => nvl(l_data.per_information14,hr_api.g_varchar2)
,p_per_information15 => nvl(l_data.per_information15,hr_api.g_varchar2)
,p_per_information16 => nvl(l_data.per_information16,hr_api.g_varchar2)
,p_per_information17 => nvl(l_data.per_information17,hr_api.g_varchar2)
,p_per_information18 => nvl(l_data.per_information18,hr_api.g_varchar2)
,p_per_information19 => nvl(l_data.per_information19,hr_api.g_varchar2)
,p_per_information20 => nvl(l_data.per_information20,hr_api.g_varchar2)
,p_per_information21 => nvl(l_data.per_information21,hr_api.g_varchar2)
,p_per_information22 => nvl(l_data.per_information22,hr_api.g_varchar2)
,p_per_information23 => nvl(l_data.per_information23,hr_api.g_varchar2)
,p_per_information24 => nvl(l_data.per_information24,hr_api.g_varchar2)
,p_per_information25 => nvl(l_data.per_information25,hr_api.g_varchar2)
,p_per_information26 => nvl(l_data.per_information26,hr_api.g_varchar2)
,p_per_information27 => nvl(l_data.per_information27,hr_api.g_varchar2)
,p_per_information28 => nvl(l_data.per_information28,hr_api.g_varchar2)
,p_per_information29 => nvl(l_data.per_information29,hr_api.g_varchar2)
,p_per_information30 => nvl(l_data.per_information30,hr_api.g_varchar2)
,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_file.put_line(FND_FILE.LOG,'Updated person record with EEO information');
update irc_pending_data
set processed='A'
,last_update_date = trunc(sysdate)
where pending_data_id = l_data.pending_data_id;
update irc_pending_data ipd
set ipd.processed='F'
,ipd.error_message=l_msg
,ipd.last_update_date=trunc(sysdate)
where ipd.pending_data_id=l_data.pending_data_id;
select ipd.pending_data_id
,ipd.posting_content_id
,ipd.email_address
,ipd.processed
,ipd.error_message
,ipd.resume_file_name
,pav.name
from irc_pending_data ipd
,per_all_vacancies pav
where ipd.vacancy_id = pav.vacancy_id(+)
and ipd.processed in ('A','R','E','F')
order by ipd.email_address, ipd.creation_date desc;
select ipd.user_password
from irc_pending_data ipd
where ipd.email_address = p_email_address
and ipd.user_password is not null;
select ipc.job_title
from irc_posting_contents_vl ipc
where ipc.posting_content_id = p_posting_content_id;
l_success_vacancies_list.delete;
l_success_vac_comments_list.delete;
l_failed_vacancies_list.delete;
l_failed_vac_comments_list.delete;
update irc_pending_data
set processed='S'
,last_update_date=trunc(sysdate)
where pending_data_id=l_applied_data.pending_data_id;
Fnd_file.put_line(FND_FILE.LOG,'Updated the processed flag to S');