Tuesday 13 March 2012

Query to List all the responsibilities,application and organization attached to a User

SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date,
       fu.email_address, fa.application_name, fr.responsibility_id,
       fa.application_id, hou.NAME
  FROM fnd_profile_option_values fpov,
       fnd_profile_options fpo,
       hr_operating_units hou,
       fnd_responsibility_tl fr,
       fnd_application_tl fa,
       fnd_user_resp_groups_direct furg,
       fnd_user fu
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpov.application_id = fpo.application_id
   AND fpo.profile_option_name = 'ORG_ID'
   AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
   AND fpov.level_value = fr.responsibility_id
   AND fa.application_id = fr.application_id
   AND fpov.level_id = 10003
   AND fpov.level_value = fr.responsibility_id
   AND furg.user_id = fu.user_id
   AND furg.responsibility_id = fr.responsibility_id
   AND furg.start_date <= NVL(:todate,sysdate)

No comments:

Post a Comment