APPS.PER_IREC_ICD_ENRLL_PROCESS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 15

  SELECT
    vac.VACANCY_ID,paaf.person_id,paaf.assignment_id,
    PIL.PER_IN_LER_ID, PAAF.BUSINESS_GROUP_ID
  FROM
    PER_ALL_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
  order by
    vac.vacancy_id,paaf.person_id;

Line: 45

  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
     paaf.vacancy_id = pVacancyId 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;

Line: 77

      select DATE_START
        from PER_PERIODS_OF_SERVICE
       where person_id = p_person_id;

Line: 99

      select full_name
        from per_all_people_f
       where person_id = p_person_id
       AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Line: 123

      select name
        from per_all_vacancies
       where vacancy_id = p_vacancy_id;

Line: 146

  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;

Line: 194

         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;

Line: 259

    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;

Line: 509

         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;

Line: 591

    select prtt_enrt_rslt_id
    from ben_prtt_enrt_rslt_f
    where person_id = pPersonId;

Line: 621

    update ben_per_in_ler
    set per_in_ler_stat_cd = 'PROCD',
        PROCD_DT           = Trunc(sysdate)
    where per_in_ler_id    = pPerInLerId;