HR人员基本信息、分配信息和地址信息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')