HR_SOFT_CODING_KEYFLEX hs
与之对应的联系键是SOFT_CODING_KEYFLEX_ID
与之关联的表是：per_all_assignments_f
印证了我昨天的猜测：这个法定信息不在员工基本分配表里，则肯定有个一与之联系的外键指向保存法定信息的这个表，这个法定信息表就是：HR_SOFT_CODING_KEYFLEX。
从中还得到一个启发：在员工基本分配信息表【per_all_assignments_f】中有一个字段 ‘SOFT_CODING_KEYFLEX_ID’ 与法定信息表【HR_SOFT_CODING_KEYFLEX】 的名字很相似：后面多了一个‘_ID’,前面少了一个‘_HR’。
还有，我找到这个字段的方法也值得我思考和学习：
我需要找到摊缴地区，纳税地区，雇主等信息所在的表以及与员工基本分配信息表【per_all_assignments_f】之间的联系。
首先，我从DISCOVERER ADMINISTRATION EDITION 中 用人力资源超级用户职责 打开‘人力资源Seed(V2.6.1)’，然后找到‘HR_员工工作分配’文件夹，在里面找到了‘摊缴地区’这个字段。然后右键点击该文件夹 选择‘Properties’，然后选择‘Custom SQL’就得到了这个文件夹的sql源码。然后分析该源码就找到了需要的表，字段，以及之间的联系。
select distinct PAAF.PERSON_ID,
PAAF.ASSIGNMENT_ID,
PAAF.ORGANIZATION_ID,
PAAF.GRADE_ID,
PAPF.LAST_NAME 员工姓名,
to_number(PAPF.EMPLOYEE_NUMBER) 员工编号,
PPT.USER_PERSON_TYPE 员工类型,
PAAF.ASSIGNMENT_NUMBER 分配编号,
NVL(AMDTL.USER_STATUS, STTL.USER_STATUS) 分配状态,
hou.name 所属部门,
hou.attribute20 部门简称,
ppt.group_name 人员组,
substr(pj.name, 1, instr(pj.name, '.') - 1) 职务分类,
substr(pj.name, instr(pj.name, '.') + 1) 职务名称,
pscode.pscode 职位分类代码,
substr(pp.name, 1, instr(pp.name, '.') - 1) 职位分类,
substr(pp.name, instr(pp.name, '.') + 1) 职位名称,
pg.sequence 职等排序,
pg.name 职等,
pays.payroll_name 工资单名称,
pays.payroll_type 工资单类型,
decode(paaf.primary_flag, 'Y', '是') 主要分配,
ab.meaning 摊缴地区,
paaf.Last_Update_Date 分配信息最后更新日期,
s01.last_name 分配信息最后更新人员,
hal.location_code 地点,
h13.meaning 分配类别,
h14.meaning 员工类别,
PAAF.EFFECTIVE_START_DATE "分配有效时段-从",
PAAF.EFFECTIVE_END_DATE "分配有效时段-至"
from per_people_f papf,
per_assignments_f paaf,
pay_people_groups ppt,
per_assignment_status_types past,
PER_ASSIGNMENT_STATUS_TYPES_TL STTL,
PER_ASS_STATUS_TYPE_AMENDS AMD,
PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL,
hr_organization_units hou,
per_jobs pj,
per_positions pp,
per_grades pg,
per_person_types ppt,
per_person_type_usages_f pptuf,
hr_lookups h13,
hr_lookups h14,
HR_SOFT_CODING_KEYFLEX hs,
FND_LOOKUP_VALUES ab,
pay_all_payrolls_f pays,
(select distinct t.user_id, tf.last_name
from fnd_user t, per_all_people_f tf
where t.employee_id = tf.person_id(+)
AND trunc(sysdate) between tf.effective_start_date and
tf.effective_end_date) s01,
(
select c.flex_value_meaning pscode, trim(c.description) psdesc
from fnd_flex_values a, fnd_flex_value_sets b, fnd_flex_values_tl c
where a.flex_value_set_id = b.flex_value_set_id
and a.flex_value_id = c.flex_value_id
and b.flex_value_set_name = 'CAS_POSITION_ATTRIBUTE_CODE'
and c.language = 'ZHS'
and a.enabled_flag = 'Y'
) pscode ,
hr_locations_all hal
where PAAF.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
AND past.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
AND STTL.LANGUAGE = USERENV('LANG')
AND pays.payroll_id(+) = paaf.PAYROLL_ID
and AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID(+)
AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', AMDTL.LANGUAGE) =
DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', USERENV('LANG'))
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID(+)
AND paaf.person_id(+) = papf.person_id
and paaf.PEOPLE_GROUP_ID=ppt.people_group_id(+)
--and paaf.primary_flag = 'Y'
and paaf.assignment_status_type_id = past.assignment_status_type_id
and paaf.organization_id = hou.organization_id
and paaf.job_id = pj.job_id(+)
and paaf.position_id = pp.position_id(+)
and paaf.grade_id = pg.grade_id(+)
and trunc(sysdate) between papf.effective_start_date and
papf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and
paaf.effective_end_date
-- and trunc(sysdate) between ppt.start_date_active and ppt.end_date_active
and h14.lookup_type(+) = 'EMPLOYEE_CATG'
AND h14.lookup_code(+) = paaf.employee_category
and h13.lookup_type(+) = 'EMP_CAT'
AND h13.lookup_code(+) = paaf.EMPLOYMENT_CATEGORY
and ppt.system_person_type = 'EMP'
AND papf.person_id = pptuf.person_id
AND pptuf.person_type_id = ppt.person_type_id
AND paaf.last_updated_by = s01.user_id(+)
and paaf.location_id = hal.location_id(+)
and hs.soft_coding_keyflex_id=paaf.SOFT_CODING_KEYFLEX_ID
and hs.segment21=ab.lookup_code --
--and ab.lookup_type='CN_SIC_AREA' and ab.language='ZHS'
and trim(substr(pp.name, 1, instr(pp.name, '.') - 1))=pscode.psdesc(+)
and trunc(sysdate) between pptuf.effective_start_date and
pptuf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN NVL(PG.DATE_FROM,TO_DATE ('0001/01/01','YYYY/MM/DD'))
AND NVL(PG.DATE_TO,TO_DATE ('4712/12/31','YYYY/MM/DD'))
ORDER BY 员工编号
分析思路是这样的：
首先在select部分(红色标识)找到我需要的字段：‘ab.meaning 摊缴地区 ’从中得到该字段来源于表‘ab’；
然后再在from部分（绿色标志）中寻找缩写为‘ab’的表 得到实际表名‘FND_LOOKUP_VALUES’（‘ab’为‘FND_LOOKUP_VALUES’的缩写），而该表只是存储代码和实际值对的一个表，而不是我们真正要寻找的存放个人法定信息的表；
然后在where部分（蓝色标识）顺藤摸瓜找到了 hs.segment21=ab.lookup_code 与该表联系的是缩写为‘hs’的表。从from部分得到对应的是表HR_SOFT_CODING_KEYFLEX， 通过查询该表而确立了存储法定信息的表就是HR_SOFT_CODING_KEYFLEX ，
但是现在还不能确定个人信息中法定信息具体内容，而与个人分配信息相关的是per_all_assignments_f ，而在where部分也找到了对于代码hs.soft_coding_keyflex_id=paaf.SOFT_CODING_KEYFLEX_ID ，从而确定了hs表和paaf表示通过外键SOFT_CODING_KEYFLEX_ID 进行联系的。
至此，我们找到了个人法定信息的存放表：HR_SOFT_CODING_KEYFLEX
以及个人分配信息表per_all_assignments_f与法定信息表HR_SOFT_CODING_KEYFLEX 之间的联系键SOFT_CODING_KEYFLEX_ID 。
从而，如果我只想取出法定信息的代码如下：
select papf.last_name,papf.employee_number,papf.person_id,paaf.assignment_id,hsck.segment1, hsck.segment20, hsck.segment21
from hr_soft_coding_keyflex hsck,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.person_id = paaf.person_id and
paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id and
papf.employee_number = '1759' and
sysdate between papf.effective_start_date and papf.effective_end_date and
sysdate between paaf.effective_start_date and paaf.effective_end_date
