Monday, August 8, 2016

FND Responsibilities attached to Users as Directly or Indirectly

/* Formatted on 2016/08/08 14:38 (Formatter Plus v4.8.8) */
SELECT   ppf.full_name, fu.user_name racfid, loc.location_code,
         frt.responsibility_name
    FROM fnd_user_resp_groups_indirect furg,
         applsys.fnd_user fu,
         applsys.fnd_responsibility_tl frt,
         applsys.fnd_responsibility fr,
         applsys.fnd_application_tl fat,
         applsys.fnd_application fa,
         per_all_people_f ppf,
         per_assignments_f pax,
         hr_locations_all loc
   WHERE furg.user_id = fu.user_id
     AND furg.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.LANGUAGE = USERENV ('LANG')
     AND fr.end_date IS NULL
     AND fu.employee_id = ppf.person_id
     AND pax.person_id = ppf.person_id
     AND SYSDATE BETWEEN ppf.effective_start_date
                     AND NVL (ppf.effective_end_date, SYSDATE)
     AND SYSDATE BETWEEN pax.effective_start_date
                     AND NVL (pax.effective_end_date, SYSDATE)
     AND pax.location_id = loc.location_id
UNION
SELECT   ppf.full_name, fu.user_name racfid, loc.location_code,
         frt.responsibility_name
    FROM fnd_user_resp_groups_direct furg,
         applsys.fnd_user fu,
         applsys.fnd_responsibility_tl frt,
         applsys.fnd_responsibility fr,
         applsys.fnd_application_tl fat,
         applsys.fnd_application fa,
         per_all_people_f ppf,
         per_assignments_f pax,
         hr_locations_all loc
   WHERE furg.user_id = fu.user_id
     AND furg.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.LANGUAGE = USERENV ('LANG')
     AND fr.end_date IS NULL
     AND fu.employee_id = ppf.person_id
     AND pax.person_id = ppf.person_id
     AND SYSDATE BETWEEN ppf.effective_start_date
                     AND NVL (ppf.effective_end_date, SYSDATE)
     AND SYSDATE BETWEEN pax.effective_start_date
                     AND NVL (pax.effective_end_date, SYSDATE)
     AND pax.location_id = loc.location_id
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY 1,3,4;

No comments:

Post a Comment