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

01.       SELECT papf.employee_number employee_number,  
02.       papf.last_name last_name, --员工姓名  
03.       papf.sex gender, --性别  
04.       papf.national_identifier national_identifier, --省份证号码  
05.       hl.meaning marital_status, --婚姻状况  
06.       papf.email_address email_address, --email地址,  
07.       fdfct.descriptive_flex_context_name || '.' ||  
08.       pa.address_line1 || '.' ||  
09.       pa.address_line2 || '.' ||  
10.       hla.meaning      || '.' ||  
11.       pa.postal_code   || '.' ||  
12.       pa.telephone_number_1 || '.' ||  
13.       pa.telephone_number_2 employee_address,  
14.       papf.effective_start_date per_effective_start_date, --人员日期自  
15.       papf.effective_end_date per_effective_end_date, --人员日期至  
16.       haou.name organization_name, --组织名称  
17.       paaf.primary_flag primary_flag, --主分配标识  
18.       paaf.assignment_number assignment_number, --员工分配编号  
19.       pjt.name job_name, --职务名称  
20.       hapft.name position_name, --职位名称  
21.       paaf.effective_start_date ass_effective_start_date, --分配日期自  
22.       paaf.effective_end_date ass_effective_end_date --分配日期至  
23.  FROM per_all_people_f           papf, --员工基本信息表  
24.       per_all_assignments_f      paaf, --员工分配表  
25.       per_jobs                   pj, --员工职务  
26.       per_jobs_tl                pjt, --员工职务多语言表  
27.       per_all_positions          pap, --员工职位表  
28.       hr_all_positions_f_tl      hapft, --员工职位多语言表  
29.       hr_lookups                 hl, --代码  
30.       hr_all_organization_units  haou, --组织信息,  
31.       fnd_territories_tl         ftt, --地区  
32.       fnd_descr_flex_contexts_tl fdfct, --弹性域内容  
33.       hr_lookups                 hla, --地址代码  
34.       per_addresses              pa --地址信息  
35. WHERE papf.person_id            = paaf.person_id(+)  
36.   AND paaf.job_id               = pj.job_id(+)  
37.   AND pj.job_id                 = pjt.job_id(+)  
38.   AND pjt.language(+)           = userenv('LANG')  
39.   AND paaf.position_id          = pap.position_id(+)  
40.   AND pap.position_id           = hapft.position_id(+)  
41.   AND hapft.language(+)         = userenv('LANG')  
42.   AND paaf.primary_flag         = 'Y'  
43.   AND papf.marital_status       = hl.lookup_code(+)  
44.   AND hl.lookup_type(+)         = 'MAR_STATUS'  
45.   AND paaf.organization_id      = haou.organization_id(+)  
46.   AND (SYSDATE BETWEEN papf.effective_start_date AND  
47.       papf.effective_end_date)  
48.   AND (SYSDATE BETWEEN paaf.effective_start_date AND  
49.       paaf.effective_end_date)  
50.   AND papf.person_id            = pa.person_id(+)  
51.   AND pa.country                = ftt.territory_code(+)  
52.   AND pa.style                  = fdfct.descriptive_flex_context_code(+)  
53.   AND fdfct.application_id(+)   = 800  
54.   AND fdfct.descriptive_flexfield_name(+) = 'Address Structure'  
55.   AND fdfct.language(+)         = userenv('LANG')  
56.   AND hla.lookup_type(+)        = 'ADDRESS_TYPE'  
57.   AND pa.address_type           = hla.lookup_code(+)  
58.   AND ftt.language(+)           = userenv('LANG')