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;
