员工，用户名，岗位，职责，职责应用产品(转)

最新推荐文章于 2024-10-03 11:11:18 发布

cuanluo3817

最新推荐文章于 2024-10-03 11:11:18 发布

阅读量116

点赞数

SELECT fu.user_id,
       fu.user_name ebs用户名,
       ppf.last_name 姓名,
       hou.NAME 部门,
       hr_general.decode_position_latest_name(paaf.position_id) 岗位,
       ppf.employee_number 员工号,
       nvl(ppf.email_address, fu.email_address) email,
       furg.responsibility_id,
       frv.responsibility_name 职责,
       fa.application_short_name,
       fat.application_name
FROM fnd_user                    fu,
       per_people_f                ppf,
       per_all_assignments_f       paaf,
       fnd_user_resp_groups_direct furg,
       fnd_responsibility_vl       frv,
       fnd_application_tl          fat,
       fnd_application             fa,
       hr_organization_units       hou
WHERE fu.employee_id = ppf.person_id
      -- 员工有效期
   AND ppf.effective_start_date <= SYSDATE
   AND (ppf.effective_end_date IS NULL OR ppf.effective_end_date >= SYSDATE)
   AND ppf.person_id = paaf.person_id
      -- 岗位有效期
   AND (paaf.effective_end_date IS NULL OR
       paaf.effective_end_date >= SYSDATE)
      -- EBS用户有效期
   AND fu.start_date <= SYSDATE
   AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
   AND fu.user_id = furg.user_id
      -- 职责有效期
   AND furg.start_date <= SYSDATE
   AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
   AND furg.responsibility_id = frv.responsibility_id
      -- 职责应用产品
   AND frv.application_id = fat.application_id
   AND frv.application_id = fa.application_id
      -- Language
   AND fat.LANGUAGE = 'ZHS'
      -- Do not include sysadmin
   AND ppf.last_name != 'SYSADMIN'
   AND paaf.organization_id = hou.organization_id
   AND frv.responsibility_name LIKE ‘%HOM%';

-- 请求组，并发程序，职责
SELECT frg.request_group_name,
       fcp.concurrent_program_name,
       fcp.user_concurrent_program_name,
       frt.responsibility_name
FROM fnd_request_groups         frg,
       fnd_request_group_units    frgu,
       fnd_concurrent_programs_vl fcp,
       fnd_responsibility_tl      frt,
       fnd_responsibility         fr
WHERE frg.application_id = frgu.application_id
   AND frg.request_group_id = frgu.request_group_id
   AND frgu.unit_application_id = fcp.application_id
   AND frgu.request_unit_id = fcp.concurrent_program_id
   AND fr.responsibility_id = frt.responsibility_id
   AND fr.application_id = frt.application_id
   AND frg.application_id = fr.group_application_id
   AND frg.request_group_id = fr.request_group_id
   AND frt.LANGUAGE = 'ZHS'
   --AND frt.responsibility_name LIKE ’%INV%'
   AND fcp.user_concurrent_program_name = '成本管理器';