ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 寻找员工分配法定信息表

寻找员工分配法定信息表

原创 Linux操作系统 作者:blueshine07 时间:2012-07-05 15:48:29 0 删除 编辑

[HR_SOFT_CODING_KEYFLEX]以及与主分配表之间的联系

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25103190/viewspace-734694/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-26

  • 博文量
    55
  • 访问量
    150863