APPS.HRI_OPL_RECRUITMENT SQL Statements

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

Line: 36

  SELECT
   ias.status_change_date
  ,ast.per_system_status
  ,ias.status_change_reason
  FROM
   irc_assignment_statuses      ias
  ,per_assignment_status_types  ast
  WHERE ias.assignment_status_type_id = ast.assignment_status_type_id
  AND ias.assignment_id = p_assignment_id;

Line: 48

  SELECT
   asg.effective_start_date   status_change_date
  ,ast.per_system_status
  ,asg.change_reason          status_change_reason
  FROM
   per_all_assignments_f        asg
  ,per_assignment_status_types  ast
  WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
  AND asg.assignment_id = p_assignment_id;

Line: 61

  SELECT 'Y', asg.change_reason, asg.assignment_id
  FROM per_all_assignments_f asg
  WHERE asg.person_id = cp_person_id
  AND asg.effective_start_date = cp_end_date + 1
  AND (asg.assignment_id = cp_assignment_id
    OR asg.primary_flag = 'Y')
  AND asg.assignment_type = 'E'
  ORDER BY DECODE(asg.assignment_id, cp_assignment_id, 1, 2);

Line: 315

  SELECT 1
  FROM
   irc_vacancy_considerations  ivc
  ,per_all_people_f            peo
  WHERE ivc.party_id = peo.party_id
  AND peo.person_id = p_person_id
  AND p_effective_date
        BETWEEN peo.effective_start_date AND peo.effective_end_date;

Line: 339

  SELECT
   AVG(DECODE(aac.current_status_code,
               'END_SCCSS', aac.hire_date - p_date_from,
              to_number(null)))                 days_to_recruit
  ,AVG(DECODE(aac.current_status_code,
               'END_SCCSS', aac.hire_date - aac.accepted_date,
              to_number(null)))                 days_accept_to_hire
  ,AVG(aac.accepted_date - p_date_from)         days_to_accept
  ,COUNT(aac.assignment_id)                     vacancy_apl_count
  FROM hri_mb_apl_activity_v aac
  WHERE fk_vacancy_id = p_vacancy_id; -- bug 4992287 performance enh.