Monday, 29 January 2018

SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle Apps R12 for Specific Operating Unit




SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle apps R12 For Specific Operating Unit





SELECT fu.user_name,
  (SELECT FULL_NAME FROM PER_PEOPLE_F
   WHERE PERSON_ID=FU.EMPLOYEE_ID
   AND EFFECTIVE_END_DATE>SYSDATE) EMPLOYEE_NAME
      ,fr.responsibility_name
  FROM apps.fnd_user_resp_groups_all furg
      ,apps.fnd_user fu
      ,apps.fnd_responsibility_vl fr
      ,apps.fnd_profile_option_values fpov
      ,apps.fnd_profile_options_vl fpo
 WHERE fu.user_id= furg.user_id
   AND fu.user_name=nvl(:P_USER,fu.user_name)
   AND fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)
   AND fr.responsibility_id= furg.responsibility_id
   AND (furg.end_date >sysdate OR furg.end_date IS NULL)
   AND fpov.level_value=fr.responsibility_id
   AND fpov.profile_option_id= fpo.profile_option_id
   AND fpov.application_id=fpo.application_id
   AND FU.END_DATE IS NULL
   AND FR.END_DATE IS NULL
   AND fpo.user_profile_option_name='MO: Operating Unit'
   AND fpov.profile_option_value=189
   ORDER BY 1

SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle Apps R12 for Specific Operating Unit

0 comments:

Post a Comment