APPS.EDW_HR_ASSGNMNT_M_SIZING SQL Statements

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

Line: 14

  SELECT count(*) total
  FROM
   per_all_assignments_f       asg
  /* Generates list of earliest date tracked start dates by assignment */
  ,(select assignment_id, effective_start_date
  from per_all_assignments_f asg
  minus
  /* Remove any assignment start date which has an earlier start date */
  select assignment_id, effective_start_date
  from per_all_assignments_f asg
  where exists (select 1 from per_all_assignments_f asg1
                where asg1.assignment_id = asg.assignment_id
                and asg1.effective_start_date < asg.effective_start_date)
  )asg_start
  WHERE
  asg_start.assignment_id = asg.assignment_id
  AND    asg_start.effective_start_date = asg.effective_start_date
  AND NVL(asg.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
  BETWEEN p_from_date AND p_to_date;

Line: 88

  x_last_update_date		NUMBER := x_date;

Line: 93

/* Select the length of the instance code */
  CURSOR inst_cur IS
  SELECT avg(nvl( vsize(instance_code),0 ))
  FROM edw_local_instance;

Line: 99

 SELECT
   avg(nvl(vsize(assignment_number),0))
  ,avg(nvl(vsize(assignment_id),0))
  ,avg(nvl(vsize(title),0))
  ,avg(nvl(vsize(normal_hours),0))
  ,avg(nvl(vsize(time_normal_start),0))
  ,avg(nvl(vsize(time_normal_finish),0))
  ,avg(nvl(vsize(probation_period),0))
  ,avg(nvl(vsize(perf_review_period),0))
  ,avg(nvl(vsize(sal_review_period),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO', primary_flag)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', frequency)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('EMP_APL', assignment_type)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO', manager_flag)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', probation_unit)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', perf_review_period_frequency)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', sal_review_period_frequency)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('HOURLY_SALARIED_CODE', hourly_salaried_code)),0))
 FROM per_all_assignments_f
 WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 121

  SELECT
    avg(nvl(vsize(assignment_status_type_id),0))
  FROM per_assignment_status_types
  WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 127

  SELECT avg(nvl(vsize(people_group_id),0))
  FROM pay_people_groups
 WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 132

  SELECT avg(nvl(vsize(name),0))
  FROM hr_all_organization_units
 WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 210

		     + NVL(ceil(x_last_update_date + 1), 0)
 		     + NVL(ceil(x_creation_date + 1), 0);