[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
fnd.employee_id as applicant_id
from
fnd_user fnd
where fnd.person_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
);
select
usr.employee_id
from
fnd_user usr
where usr.user_id = p_user_id;
select max(ioh.status_change_date)
from irc_offer_status_history ioh
where ioh.offer_id = p_offer_id
and ioh.offer_status ='EXTENDED';
select iof.applicant_assignment_id
,vac.name as vacancy_name
,vac.manager_id as manager_id
,asg.recruiter_id as recruiter_id
,job.name as job_title
,asg.person_id as applicant_id
,ppf.full_name as applicant_name
,iof.created_by as creator_id
,ipc.name as job_posting_title
,ipc.job_title as ipc_job_title
from irc_offers iof
,per_all_vacancies vac
,per_jobs_vl job
,per_all_assignments_f asg
,per_all_people_f ppf
,irc_posting_contents_vl ipc
where
iof.offer_status = 'EXTENDED'
and iof.vacancy_id = vac.vacancy_id
and vac.job_id = job.job_id(+)
and asg.assignment_id = iof.applicant_assignment_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = asg.person_id
and iof.offer_id = p_offer_id
and vac.primary_posting_id = ipc.posting_content_id;
select vac.name as vacancy_name
,asg.supervisor_id as manager_id
,asg.recruiter_id as recruiter_id
,iof.created_by as creator
,job.name as job_title
,asg.person_id as applicant_id
,ppf.full_name as applicant_name
,ipc.name as job_posting_title
,ipc.job_title as ipc_job_title
,iof.last_updated_by as last_updated
from irc_offers iof
,per_all_vacancies vac
,per_jobs_vl job
,per_all_assignments_f asg
,per_all_people_f ppf
,irc_posting_contents_vl ipc
where
iof.offer_status = 'CLOSED'
and iof.vacancy_id = vac.vacancy_id
and vac.job_id = job.job_id(+)
and asg.assignment_id = iof.offer_assignment_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 <= trunc(sysdate))
and trunc(sysdate) between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_id = asg.person_id
and iof.offer_id = p_offer_id
and ipc.posting_content_id(+) = vac.primary_posting_id;
select iof.offer_id
,nvl((iof.offer_extended_method),
(fnd_profile.VALUE('IRC_OFFER_SEND_METHOD'))) extended_method
,iof.applicant_assignment_id
,vac.name as vacancy_name
,vac.manager_id as manager_id
,job.name as job_title
,asg.person_id as applicant_id
,ppf.full_name as applicant_name
,iof.expiry_date
,iof.created_by as creator_id
,ipc.name as job_posting_title
,ipc.job_title as ipc_job_title
from irc_offers iof
,per_all_vacancies vac
,per_jobs_vl job
,per_all_assignments_f asg
,per_all_people_f ppf
,irc_posting_contents_vl ipc
where
iof.offer_status = 'EXTENDED'
and iof.latest_offer = 'Y'
and iof.vacancy_id = vac.vacancy_id
and vac.job_id = job.job_id(+)
and asg.assignment_id = iof.applicant_assignment_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = asg.person_id
and (iof.expiry_date between trunc(sysdate) + 0 and
trunc(sysdate) + p_number_of_days)
and vac.primary_posting_id = ipc.posting_content_id;
select iof.offer_id
,vac.name as vacancy_name
,vac.manager_id as manager_id
,job.name as job_title
,asg.person_id as applicant_id
,ppf.full_name as applicant_name
,iof.created_by as creator_id
,ipc.name as job_posting_title
,ipc.job_title as ipc_job_title
from irc_offers iof
,per_all_vacancies vac
,per_jobs_vl job
,per_all_assignments_f asg
,per_all_people_f ppf
,irc_posting_contents_vl ipc
where
iof.vacancy_id = vac.vacancy_id
and iof.offer_status = 'EXTENDED'
and iof.latest_offer = 'Y'
and vac.job_id = job.job_id(+)
and asg.assignment_id = iof.applicant_assignment_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = asg.person_id
and iof.expiry_date < trunc(sysdate) + 0
and vac.primary_posting_id = ipc.posting_content_id;
select vac.name as vacancy_name
,iof.created_by as creator_id
,job.name as job_title
,ppf.full_name as applicant_name
from irc_offers iof
,per_all_vacancies vac
,per_jobs_vl job
,per_all_assignments_f asg
,per_all_people_f ppf
where
iof.offer_status = 'HOLD'
and iof.vacancy_id = vac.vacancy_id
and vac.job_id = job.job_id(+)
and asg.assignment_id = iof.applicant_assignment_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = asg.person_id
and iof.offer_id = p_offer_id;
select
usr.user_name
from
fnd_user usr
where usr.user_id = p_user_id;
select applicant_assignment_id
from irc_offers
where offer_id = p_offer_id;
select NLS_LANGUAGE
from fnd_languages_vl
where language_code = p_dft_lang;
select FND_PROFILE.value_specific('ICX_LANGUAGE',usr.user_id)
from fnd_user usr
where EMPLOYEE_ID = l_applicant_id;
select application_id
from fnd_application
where application_short_name = apl_short_name;
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = resp_key
AND application_id = apl_id;
select src_apl_asg_id from per_vac_linked_assignments
where tgt_apl_asg_id = p_apl_asg_id
and sysdate between nvl(start_date,sysdate) and
nvl(end_date,sysdate) ;
select function_id from fnd_form_functions
where function_name = p_function_name;
select src_apl_asg_id from per_vac_linked_assignments
where tgt_apl_asg_id = p_apl_asg_id
and sysdate between nvl(start_date,sysdate) and
nvl(end_date,sysdate) ;
select note.note_text
from irc_notes note
where note.offer_status_history_id = p_offer_status_history_id
order by creation_date desc;
l_last_updated_by number;
select note.note_text
from irc_notes note
where note.offer_status_history_id = p_offer_status_history_id
order by creation_date desc;
select meaning
from hr_lookups
where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
lookup_code = p_decline_reason;
select iri.source_person_id
from irc_referral_info iri,
irc_offers iof
where iri.object_id = iof.APPLICANT_ASSIGNMENT_ID
and iri.object_type = 'APPLICATION'
and iof.offer_id = p_offer_id;
fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;
l_last_updated_by number;
l_last_updated_emp_id number;
select note.note_text
from irc_notes note
where note.offer_status_history_id = p_offer_status_history_id
order by creation_date desc;
select meaning
from hr_lookups
where lookup_type = 'IRC_OFFER_WITHDRAWAL_REASON' and
lookup_code = p_withdrawal_reason;
select offer_status,change_reason
from irc_offer_status_history
where offer_id = p_offer_id
and status_change_date = (select max(status_change_date)
from irc_offer_status_history
where offer_id = p_offer_id
and offer_status_history_id <> p_offer_status_history_id);
select ppf.full_name
from per_all_people_f ppf
where ppf.person_id = p_person_id
and sysdate between effective_start_date and effective_end_date;
fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;
hr_utility.set_location('l_last_updated_by' || l_last_updated_by, 20);
open csr_get_user_employee_id(l_last_updated_by);
fetch csr_get_user_employee_id into l_last_updated_emp_id;
hr_utility.set_location('l_last_updated_emp_id' || l_last_updated_emp_id, 20);
open csr_get_name(l_last_updated_emp_id);
l_last_updated_by number;
select note.note_text
from irc_notes note
where note.offer_status_history_id = p_offer_status_history_id
order by creation_date desc;
select meaning
from hr_lookups
where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
lookup_code = p_decline_reason;
select offer_status,change_reason
from irc_offer_status_history
where offer_id = p_offer_id
and status_change_date = (select max(status_change_date)
from irc_offer_status_history
where offer_id = p_offer_id
and offer_status_history_id <> p_offer_status_history_id);
select ppf.full_name
from per_all_people_f ppf
where ppf.person_id = p_person_id
and sysdate between effective_start_date and effective_end_date;
fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;