EBS-HR人员基本信息、分配信息和地址信息SQL

1、主SQL获取信息

SELECT papf.employee_number employee_number,
       papf.last_name last_name, --员工姓名
       papf.sex gender, --性别
       papf.national_identifier national_identifier, --省份证号码
       hl.meaning marital_status, --婚姻状况
       papf.email_address email_address, --email地址,
       fdfct.descriptive_flex_context_name || '.' ||
       pa.address_line1 || '.' ||
       pa.address_line2 || '.' ||
       hla.meaning      || '.' ||
       pa.postal_code   || '.' ||
       pa.telephone_number_1 || '.' ||
       pa.telephone_number_2 employee_address,
       papf.effective_start_date per_effective_start_date, --人员日期自
       papf.effective_end_date per_effective_end_date, --人员日期至
       haou.name organization_name, --组织名称
       paaf.primary_flag primary_flag, --主分配标识
       paaf.assignment_number assignment_number, --员工分配编号
       pjt.name job_name, --职务名称
       hapft.name position_name, --职位名称
       paaf.effective_start_date ass_effective_start_date, --分配日期自
       paaf.effective_end_date ass_effective_end_date --分配日期至
  FROM per_all_people_f           papf, --员工基本信息表
       per_all_assignments_f      paaf, --员工分配表
       per_jobs                   pj, --员工职务
       per_jobs_tl                pjt, --员工职务多语言表
       per_all_positions          pap, --员工职位表
       hr_all_positions_f_tl      hapft, --员工职位多语言表
       hr_lookups                 hl, --代码
       hr_all_organization_units  haou, --组织信息,
       fnd_territories_tl         ftt, --地区
       fnd_descr_flex_contexts_tl fdfct, --弹性域内容
       hr_lookups                 hla, --地址代码
       per_addresses              pa --地址信息
 WHERE papf.person_id            = paaf.person_id(+)
   AND paaf.job_id               = pj.job_id(+)
   AND pj.job_id                 = pjt.job_id(+)
   AND pjt.language(+)           = userenv('LANG')
   AND paaf.position_id          = pap.position_id(+)
   AND pap.position_id           = hapft.position_id(+)
   AND hapft.language(+)         = userenv('LANG')
   AND paaf.primary_flag         = 'Y'
   AND papf.marital_status       = hl.lookup_code(+)
   AND hl.lookup_type(+)         = 'MAR_STATUS'
   AND paaf.organization_id      = haou.organization_id(+)
   AND (SYSDATE BETWEEN papf.effective_start_date AND
       papf.effective_end_date)
   AND (SYSDATE BETWEEN paaf.effective_start_date AND
       paaf.effective_end_date)
   AND papf.person_id            = pa.person_id(+)
   AND pa.country                = ftt.territory_code(+)
   AND pa.style                  = fdfct.descriptive_flex_context_code(+)
   AND fdfct.application_id(+)   = 800
   AND fdfct.descriptive_flexfield_name(+) = 'Address Structure'
   AND fdfct.language(+)         = userenv('LANG')
   AND hla.lookup_type(+)        = 'ADDRESS_TYPE'
   AND pa.address_type           = hla.lookup_code(+)
   AND ftt.language(+)           = userenv('LANG');

2、根据OU获取职位

SELECT j.name
      INTO l_job_name
      FROM per_jobs j, per_assignments_f a, per_people_f p
     WHERE 1 = 1
       AND trunc(SYSDATE) BETWEEN p.effective_start_date AND
           p.effective_end_date
       AND trunc(SYSDATE) BETWEEN a.effective_start_date AND
           a.effective_end_date
       AND p.person_id = a.person_id
       AND j.job_id = a.job_id
       AND a.organization_id = 101
       AND p.person_id = 319;

-----------------------------------------------------

  FUNCTION get_person_job(p_person_id IN NUMBER, p_org_id IN NUMBER)
    RETURN VARCHAR2 IS
    l_job_name VARCHAR2(400);
  BEGIN
    SELECT j.name
      INTO l_job_name
      FROM per_jobs j, per_assignments_f a, per_people_f p
     WHERE 1 = 1
       AND trunc(SYSDATE) BETWEEN p.effective_start_date AND
           p.effective_end_date
       AND trunc(SYSDATE) BETWEEN a.effective_start_date AND
           a.effective_end_date
       AND p.person_id = a.person_id
       AND j.job_id = a.job_id
       AND a.organization_id = p_org_id
       AND p.person_id = p_person_id;
    RETURN l_job_name;
  EXCEPTION
    WHEN OTHERS THEN
      l_job_name := NULL;
      RETURN l_job_name;
  END;

3、默认获取主分配

  FUNCTION get_person_job(p_person_id IN NUMBER) RETURN VARCHAR2 IS
    l_job_name VARCHAR2(400);
  BEGIN
    SELECT j.name
      INTO l_job_name
      FROM per_jobs j, per_assignments_f a, per_people_f p
     WHERE 1 = 1
       AND trunc(SYSDATE) BETWEEN p.effective_start_date AND
           p.effective_end_date
       AND trunc(SYSDATE) BETWEEN a.effective_start_date AND
           a.effective_end_date
       AND p.person_id = a.person_id
       AND j.job_id = a.job_id
       AND a.primary_flag = 'Y'
       AND p.person_id = p_person_id;
    RETURN l_job_name;
  EXCEPTION
    WHEN OTHERS THEN
      l_job_name := NULL;
      RETURN l_job_name;
  END;