APPS.HRI_EDW_FCT_WRK_ACTVTY SQL Statements

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

Line: 8

/* applicant change reason gets updated into the employee assignment and     */
/* causes errors when it tries to be resolved as an employee change reason   */
/*****************************************************************************/
FUNCTION check_reason( p_change_reason  IN VARCHAR2,
                       p_instance       IN VARCHAR2 )
                        RETURN VARCHAR2 IS

  l_reason_fk       VARCHAR2(80);  -- Reason foreign key

Line: 19

  SELECT lookup_code
  FROM hr_lookups
  WHERE lookup_code = p_change_reason
  AND lookup_type = 'EMP_ASSIGN_REASON';

Line: 51

/* Selects the start date for the effective period of service */
  CURSOR hire_cur IS
  SELECT MAX(date_start)
  FROM per_periods_of_service
  WHERE person_id = p_person_id
  AND date_start <= p_effective_date;

Line: 86

  SELECT MAX(asg2.effective_start_date)
  FROM
   per_all_assignments_f  asg1
  ,per_all_assignments_f  asg2
  WHERE
      asg1.assignment_id = p_assignment_id
  AND asg2.assignment_id = p_assignment_id
  AND asg1.effective_end_date + 1 = asg2.effective_start_date
  AND asg1.organization_id <> asg2.organization_id
  AND asg2.effective_start_date < p_change_date;

Line: 99

  SELECT MIN(asg.effective_start_date)
  FROM per_all_assignments_f asg
  WHERE asg.assignment_id = p_assignment_id
  AND asg.assignment_type = 'E';

Line: 144

  SELECT MAX(asg2.effective_start_date)
  FROM
   per_all_assignments_f  asg1
  ,per_all_assignments_f  asg2
  WHERE
      asg1.assignment_id = p_assignment_id
  AND asg2.assignment_id = p_assignment_id
  AND asg1.effective_end_date + 1 = asg2.effective_start_date
  AND NVL(asg1.job_id, -1) <> NVL(asg2.job_id, -1)
  AND asg2.effective_start_date < p_change_date;

Line: 157

  SELECT MIN(asg.effective_start_date)
  FROM per_all_assignments_f asg
  WHERE asg.assignment_id = p_assignment_id
  AND asg.assignment_type = 'E';

Line: 202

  SELECT MAX(asg2.effective_start_date)
  FROM
   per_all_assignments_f  asg1
  ,per_all_assignments_f  asg2
  WHERE
      asg1.assignment_id = p_assignment_id
  AND asg2.assignment_id = p_assignment_id
  AND asg1.effective_end_date + 1 = asg2.effective_start_date
  AND NVL(asg1.position_id, -1) <> NVL(asg2.position_id, -1)
  AND asg2.effective_start_date < p_change_date;

Line: 215

  SELECT MIN(asg.effective_start_date)
  FROM per_all_assignments_f asg
  WHERE asg.assignment_id = p_assignment_id
  AND asg.assignment_type = 'E';

Line: 260

  SELECT MAX(asg2.effective_start_date)
  FROM
   per_all_assignments_f  asg1
  ,per_all_assignments_f  asg2
  WHERE
      asg1.assignment_id = p_assignment_id
  AND asg2.assignment_id = p_assignment_id
  AND asg1.effective_end_date + 1 = asg2.effective_start_date
  AND NVL(asg1.grade_id, -1) <> NVL(asg2.grade_id, -1)
  AND asg2.effective_start_date < p_change_date;

Line: 273

  SELECT MIN(asg.effective_start_date)
  FROM per_all_assignments_f asg
  WHERE asg.assignment_id = p_assignment_id
  AND asg.assignment_type = 'E';

Line: 318

  SELECT MAX(asg2.effective_start_date)
  FROM
   per_all_assignments_f  asg1
  ,per_all_assignments_f  asg2
  WHERE
      asg1.assignment_id = p_assignment_id
  AND asg2.assignment_id = p_assignment_id
  AND asg1.effective_end_date + 1 = asg2.effective_start_date
  AND NVL(asg1.location_id, -1) <> NVL(asg2.location_id, -1)
  AND asg2.effective_start_date < p_change_date;

Line: 331

  SELECT MIN(asg.effective_start_date)
  FROM per_all_assignments_f asg
  WHERE asg.assignment_id = p_assignment_id
  AND asg.assignment_type = 'E';