Populate a custom enumeration with all users assigned to specific role?

Good question. As far as I can tell by looking at CUSTOM_ENUMERATiONS and CUSTOM_FIELD_ENUM_VALUES, the lists are static. There is no spot for a custom query.

I could be wrong though, or not thinking of the right Custom Object to use for what you need.

One thing you could do is to create a Custom event on New or Modify for the LU where this field would be used. You could validate that the entered value was at least something from the list, as well as making it required, if desired.

I actually think querying permissions might be a bit interesting. Checking GRANTs is more of a DBA-ish query (DDL vs DML), so I am not sure a simple SELECT will work. I’ll play with that in a Quick Report though to see how that might work…

Thanks,

Joe Kaufman

Follow up… The following query works to see who has been grated a certain role:

SELECT Grantee FROM SYS.Dba_Role_Privs WHERE Granted_Role = 'BL_REPORTS_PO' ORDER BY Grantee

The problem is, the above uses the SYS schema, which a normal user (non-App Owner) does not have access to (I think App Owner is OK because I was able to run the query in SQL Developer when logged in as IFSAPP). The above query does not work in a Quick Report when running under my own username, for example. So, I am not sure how it would work in a Custom Event (am betting it would not).

You may need to resort to a custom field on whatever LU houses your list of people (Person?) and put a certain category in there. Then query that CFV for usernames of that category to validate free-form entry. Not as elegant as a dynamic Custom Enum, but as far as I can tell you would need to use a custom field to link to the right people anyway due to not being able to access the GRANT list. If you do this a lot for several different enums, that category could be your enumeration driver.

Thanks,

Joe Kaufman

You can use the IFS view FND_USER_ROLE_RUNTIME for this. It contains the User ID and all roles granted to this user (including indirect grants, i. e. when a role contains another role grant).

Thank you both for your quick responses. It appears as if my answer lies in a hybrid solution using both of your suggestions. I will repost when I have the solution in place. 

durette wrote:

Rather than use an Enumeration, you may want to use a Reference.

 

My screenshots are from Apps 9.

 

Then, in the List of Values dialog, you can use a saved search to narrow your options. In the base profile, you can make that saved search the default when the LOV pops up.

Ultimately, we went with this idea and everyone loved it, but the LOV view they told me to use seems to be a built-in list of all users, displaying a LOV with 936 choices. Is there a way to filter the list without creating a new view? 

I can see the SELECT statement it generates when I preview the list, but it appears to be read only. It won’t let me edit it.
 

First, clear your personal profile--just yours, in a nonproduction environment. (This is to make your profile less cluttered so you can find individual branches.)

Then, log in as yourself and navigate to that screen.

Enable the field in the layout if necessary.

Go to any record. Go to your new field and hit List of Values. (I called my example custom field “User Responsible”.)

Click the button on the right to do a search.

If you use the FndUser example, here's an advanced search to find users granted a particular permission set:
 

identity IN (
   SELECT identity
     FROM ifsapp.fnd_user_role_runtime
    WHERE role = 'YOUR_PERMISSION_SET_HERE')

Perhaps you only want active users.

identity IN (
   SELECT furr.identity
     FROM ifsapp.fnd_user_role_runtime furr
     JOIN ifsapp.fnd_user fu
       ON fu.identity = furr.identity
    WHERE fu.active = 'TRUE'
      AND furr.role = 'YOUR_PERMISSION_SET_HERE')

Save that and then hit Search. I called my example “My Saved Search”.

Now the top of the form will show your search in use.

Right-mouse-click in the area with the data, like where I’ve shaded gray, and hit Properties.

The default startup behavior is to Populate.

Change that to use your saved search.

Apply. OK.

Save your personal profile.

Log out and log back in.

Go to your personal proifle.

In my example, I used the screen Engineering Part Revision as my playground for the custom field.

There are two important areas of your profile it touched.

First, you have the saved search itself:

Second, you have where it configured this saved search as the default startup behavior.

To deploy this to your other users in that development environment, you’ll right-mouse on each of these layout branches and hit “Copy Values to Profiles...”

From here, it’s a matter of how you’ve managed your base profiles.

Even better, you can hit “Export Branch” on each of those and produce XML files. You can then deploy those XMLs into your TEST and PROD environments and copy out the branches  to the other base profiles from there.

Durrette, I was able to implement everything you explained except copying it to the other users’ profiles. I either do not have access to that or I’m simply looking in the wrong place, but fortunately, there are only 3 or 4 users that have the proper access to use this feature, so it was easy to replicate the saved search for each of them individually. It works like a charm. Thanks!