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

最新推荐文章于 2025-09-16 15:37:33 发布

原创最新推荐文章于 2025-09-16 15:37:33 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

技术杂绘专栏收录该内容

20 篇文章

订阅专栏

本文提供了一个复杂的SQL查询示例，用于从多个表中提取员工的个人信息、分配信息及地址信息。通过连接不同的表来获取员工的基本信息、职务、职位、组织归属及详细地址等。

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