4 Replies Latest reply on Apr 18, 2011 5:49 PM by 706771

    Retrieving Multi-Org Op. Units

    706771
      Dear Members:

      For a report, I would like p_org_id (LOV parameter), populated with list of Operating Units (MOAC) assigned to logged-in user responsibility. Is the following SQL snippet correct or there's better way of doing it - if you could please verify - appreciate it.

      select per_sec_org.organization_name, per_sec_org.organization_id
      from
      fnd_responsibility_vl fnd_resp,
      FND_PROFILE_OPTIONS_VL fnd_prof_opt,
      fnd_profile_option_values fnd_prof_vals,
      per_security_organizations_v per_sec_org
      where
      fnd_resp.RESPONSIBILITY_NAME='XYZ' -- will get from $PROFILE$
      AND fnd_prof_opt.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'
      AND fnd_prof_vals.LEVEL_VALUE=fnd_resp.RESPONSIBILITY_ID
      AND fnd_prof_vals.level_value_application_id= fnd_resp.application_id
      AND fnd_prof_vals.profile_option_id=fnd_prof_opt.profile_option_id
      AND fnd_prof_vals.profile_option_value=per_sec_org.security_profile_id

      Thanks very much.