Users and responsibilities list in oracle ebs(Application)

--Users and responsibilities list in oracle ebs(Application).
--user with employee
SELECT fu.user_name,
       ppf.last_name,
       hr_general.Decode_organization(paaf.organization_id) orgnization,
       fu.start_date,
       fu.end_date,
       frt.responsibility_name,
       furg.start_date,
       furg.end_date
FROM   fnd_user fu,
       per_people_f ppf,
       per_all_assignments_f paaf,
       fnd_user_resp_groups furg,
       fnd_responsibility_tl frt
WHERE  fu.employee_id = ppf.person_id
       AND ppf.person_id = paaf.person_id
       AND fu.user_id = furg.user_id
       AND furg.responsibility_id = frt.responsibility_id
       AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
       AND paaf.primary_flag = 'Y'
UNION ALL
--user without employee
SELECT fu.user_name,
       NULL,
       NULL,
       fu.start_date,
       fu.end_date,
       frt.responsibility_name,
       furg.start_date,
       furg.end_date
FROM   fnd_user fu,
       fnd_user_resp_groups furg,
       fnd_responsibility_tl frt
WHERE  fu.user_id = furg.user_id
       AND furg.responsibility_id = frt.responsibility_id
       AND fu.employee_id IS NULL
ORDER  BY user_name

posted @ 2010-07-19 14:28  dbblog  阅读(295)  评论(0编辑  收藏  举报