eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EDW_HR_PERSON_M_SIZING SQL Statements

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

Line: 13


  /* Selects count of rows from each union of collection view */
  CURSOR row_count_cur IS
  SELECT SUM(total)
  FROM (
    SELECT COUNT(*) total
    FROM ( SELECT DISTINCT peo.person_id
           FROM per_all_people_f peo
           ,hr_all_organization_units bgr
           WHERE peo.business_group_id = bgr.organization_id
           AND greatest( NVL(peo.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
                         NVL(bgr.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
           BETWEEN p_from_date AND p_to_date )
    UNION ALL
    SELECT  COUNT(*) total
    FROM ra_salesreps_all rs
    ,hr_all_organization_units org
    WHERE rs.org_id = org.organization_id (+)
    AND greatest( NVL( rs.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
                  NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
    BETWEEN p_from_date AND p_to_date
    UNION ALL
    SELECT COUNT(*)
    FROM mtl_planners mp
    ,hr_all_organization_units org
    WHERE mp.organization_id = org.organization_id
    AND greatest( NVL(mp.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
                  NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
    BETWEEN p_from_date AND p_to_date
  );

Line: 75


  x_last_update_date      NUMBER := x_date;

Line: 88


  x_resume_updated_date   NUMBER := x_date;

Line: 128


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

Line: 139


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

Line: 145


  SELECT
   avg(nvl(vsize(person_id),0))
  ,avg(nvl(vsize(last_name),0))
  ,avg(nvl(vsize(NVL(applicant_number,employee_number)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',NVL(current_employee_flag,'N'))),0))
  ,avg(nvl(vsize(email_address),0))
  ,avg(nvl(vsize(first_name),0))
  ,avg(nvl(vsize(full_name),0))
  ,avg(nvl(vsize(known_as),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('MAR_STATUS',marital_status)),0))
  ,avg(nvl(vsize(middle_names),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('NATIONALITY',nationality)),0))
  ,avg(nvl(vsize(national_identifier),0))
  ,avg(nvl(vsize(previous_last_name),0))
  ,avg(nvl(vsize(sex),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('TITLE',title)),0))
  ,avg(nvl(vsize(fte_capacity),0))
  ,avg(nvl(vsize(internal_location),0))
  ,avg(nvl(vsize(mailstop),0))
  ,avg(nvl(vsize(pre_name_adjunct),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('STUDENT_STATUS',student_status)),0))
  ,avg(nvl(vsize(suffix),0))
  ,avg(nvl(vsize(town_of_birth),0))
  ,avg(nvl(vsize(region_of_birth),0))
  ,avg(nvl(vsize(country_of_birth),0))
  ,avg(nvl(vsize(global_person_id),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',rehire_recommendation)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',registered_disabled_flag)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',resume_exists)),0))
  ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',fast_path_employee)),0))
  ,count(person_id)
  FROM
   per_all_people_f peo
  WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 182


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

Line: 192


  SELECT
    avg(nvl(vsize(salesrep_id),0))
   ,avg(nvl(vsize(salesrep_number),0))
   ,avg(nvl(vsize(name),0))
   ,avg(nvl(vsize(org_id),0))
   ,count(salesrep_id)
  FROM ra_salesreps_all
  WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 207


  SELECT
   avg(nvl(vsize(planner_code),0))
  ,avg(nvl(vsize(description),0))
  ,avg(nvl(vsize(organization_id),0))
  ,count(planner_code)
  FROM mtl_planners
  WHERE last_update_date BETWEEN p_from_date AND p_to_date;

Line: 329


  + NVL(ceil(x_last_update_date + 1), 0)
  + NVL(ceil(x_name + 1), 0)
  + NVL(ceil(x_start_date + 1), 0)
  + NVL(ceil(x_national_identifier + 1), 0)
  + NVL(ceil(x_person_dp + 1), 0)
  + NVL(ceil(x_person_id + 1), 0)
  + NVL(ceil(x_person_num + 1), 0)
  + NVL(ceil(x_planner_code + 1), 0)
  + NVL(ceil(x_planner_flag + 1), 0)
  + NVL(ceil(x_previous_last_name + 1), 0)
  + NVL(ceil(x_region_of_birth + 1), 0)
  + NVL(ceil(x_rehire_rcmmndtn + 1), 0)
  + NVL(ceil(x_resume_exists + 1), 0)
  + NVL(ceil(x_resume_updated_date + 1), 0)
  + NVL(ceil(x_salesrep_id + 1), 0)
  + NVL(ceil(x_sales_rep_flag + 1), 0)
  + NVL(ceil(x_student_status + 1), 0)
  + NVL(ceil(x_sys_gen_flag + 1), 0)
  + NVL(ceil(x_title + 1), 0)
  + NVL(ceil(x_town_of_birth + 1), 0)
  + NVL(ceil(x_buyer_flag + 1), 0)
  + NVL(ceil(x_country_of_birth + 1), 0)
  + NVL(ceil(x_crrspndnc_language + 1), 0)
  + NVL(ceil(x_date_emp_data_vrfd + 1), 0)
  + NVL(ceil(x_date_of_birth + 1), 0)
  + NVL(ceil(x_disability_flag + 1), 0)
  + NVL(ceil(x_effective_end_date + 1), 0)
  + NVL(ceil(x_effective_start_date + 1), 0)
  + NVL(ceil(x_email_address + 1), 0)
  + NVL(ceil(x_fast_path_employee + 1), 0)
  + NVL(ceil(x_first_name + 1), 0)
  + NVL(ceil(x_fte_capacity + 1), 0)
  + NVL(ceil(x_full_name + 1), 0)
  + NVL(ceil(x_gender + 1), 0)
  + NVL(ceil(x_global_person_id + 1), 0)
  + NVL(ceil(x_internal_location + 1), 0)
  + NVL(ceil(x_known_as + 1), 0)
  + NVL(ceil(x_last_name + 1), 0)
  + NVL(ceil(x_mailstop + 1), 0)
  + NVL(ceil(x_marital_status + 1), 0)
  + NVL(ceil(x_middle_names + 1), 0)
  + NVL(ceil(x_name_prefix + 1), 0)
  + NVL(ceil(x_name_suffix + 1), 0)
  + NVL(ceil(x_nationality + 1), 0)
  + NVL(ceil(x_employee_flag + 1), 0)
  + NVL(ceil(x_applicant_flag + 1), 0) );