[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select VACANCY_ID, person_id,assignment_id,
PER_IN_LER_ID, BUSINESS_GROUP_ID
from
(
SELECT
vac.VACANCY_ID,paaf.person_id,paaf.assignment_id,
PIL.PER_IN_LER_ID, PAAF.BUSINESS_GROUP_ID
FROM
PER_VACANCIES vac, per_all_assignments_f paaf,
BEN_PER_IN_LER pil, ben_ler_f ler
WHERE
vac.BUSINESS_GROUP_ID = pBGId and
paaf.BUSINESS_GROUP_ID = vac.BUSINESS_GROUP_ID and
pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
paaf.vacancy_id = vac.Vacancy_Id and
paaf.assignment_type = 'E' AND
paaf.primary_flag='Y' and
sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
where paaf.person_id = ben.person_id) AND
ler.typ_cd = 'IREC' AND
pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
ler.ler_id = pil.ler_id AND
pil.person_id=paaf.person_id AND
pil.PER_IN_LER_STAT_CD='STRTD' AND
pil.assignment_id = paaf.assignment_id
--
union
--including recruiting-area linked assignments
SELECT
pav.vacancy_id,paaf.person_id,paaf.assignment_id,
PIL.PER_IN_LER_ID, PAAF.BUSINESS_GROUP_ID
FROM
per_all_assignments_f paaf,
BEN_PER_IN_LER pil, ben_ler_f ler,
per_all_vacancies pav,
per_vac_linked_assignments pvla
WHERE
pvla.tgt_apl_asg_id = paaf.assignment_id and
sysdate between nvl(pvla.start_date,sysdate) and nvl(pvla.end_date,sysdate) and
pav.vacancy_id = pvla.vacancy_id and
paaf.BUSINESS_GROUP_ID = pBGId and
pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
paaf.assignment_type = 'E' AND
paaf.primary_flag='Y' and
sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
where paaf.person_id = ben.person_id) AND
ler.typ_cd = 'IREC' AND
pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
ler.ler_id = pil.ler_id AND
pil.person_id=paaf.person_id AND
pil.PER_IN_LER_STAT_CD='STRTD' AND
pil.assignment_id = paaf.assignment_id
)
order by
vacancy_id,person_id;
SELECT
paaf.person_id, paaf.assignment_id, PIL.PER_IN_LER_ID,
PAAF.BUSINESS_GROUP_ID
FROM
per_all_assignments_f paaf,BEN_PER_IN_LER pil, ben_ler_f ler
WHERE
pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
pVacancyId = nvl(paaf.vacancy_id,
(select vacancy_id
from per_vac_linked_assignments
where tgt_apl_asg_id = paaf.assignment_id
and sysdate between nvl(start_date,sysdate)
and nvl(end_date,sysdate) and rownum<2
)
) and
paaf.assignment_type = 'E' AND
paaf.primary_flag='Y' and
sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
where paaf.person_id = ben.person_id) and
ler.typ_cd = 'IREC' AND
pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
ler.ler_id = pil.ler_id AND
pil.person_id=paaf.person_id AND
pil.PER_IN_LER_STAT_CD='STRTD' AND
pil.assignment_id = paaf.assignment_id
order by
paaf.person_id;
select min(EFFECTIVE_START_DATE)
from PER_ALL_ASSIGNMENTS_F
where ASSIGNMENT_ID = p_assignment_id
and ASSIGNMENT_TYPE = 'E'
and PRIMARY_FLAG = 'Y';
select full_name
from per_all_people_f
where person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
select name
from per_all_vacancies
where vacancy_id = p_vacancy_id;
SELECT haou.NAME
FROM HR_ORGANIZATION_UNITS haou, HR_ORGANIZATION_INFORMATION haoi
WHERE
haou.organization_id = haoi.organization_id AND
haoi.org_information_context = 'CLASS' AND
haoi.org_information1 = 'HR_BG' AND
haoi.org_information2 = 'Y' AND
not exists (select 1 from HR_ORGANIZATION_INFORMATION haoi2
where haou.organization_id=haoi2.organization_id
and haoi2.org_information_context='BG Recruitment'
and haoi2.org_information9='Y') AND
BUSINESS_GROUP_ID=p_bg_id;
select * from(
select ofrhis.offer_status, ofrhis.change_reason, ofrhis.decline_reason
from irc_offer_status_history ofrhis, irc_offers ofr, per_all_assignments_f paaf
where paaf.assignment_id = pAssignmentId and ofr.applicant_assignment_id = paaf.assignment_id
and ofrhis.offer_id = ofr.offer_id and ofr.latest_offer='Y'
and sysdate between paaf.effective_start_date and paaf.effective_end_date order by status_change_date desc)
where rownum<2;
SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = pPersonId and
ppf.effective_end_date > To_Date('30-12-4712','dd-mm-yyyy') and
ppf.current_employee_flag IS null;
select PAAF.assignment_id,PAAF.BUSINESS_GROUP_ID,PIL.PER_IN_LER_ID
from per_all_assignments_f PAAF, BEN_PER_IN_LER pil, ben_ler_f ler
where PAAF.person_id = pPersonId and PAAF.assignment_type='E' and
paaf.primary_flag='Y' and
pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
SYSDATE BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date AND
NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
where paaf.person_id = ben.person_id AND ROWNUM = 1) AND
ler.typ_cd = 'IREC' AND
pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
ler.ler_id = pil.ler_id AND pil.person_id=paaf.person_id AND
pil.PER_IN_LER_STAT_CD='STRTD' AND
pil.assignment_id = paaf.assignment_id;
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f
where person_id = pPersonId;
update ben_per_in_ler
set per_in_ler_stat_cd = 'PROCD',
PROCD_DT = Trunc(sysdate)
where per_in_ler_id = pPerInLerId;