Form Personalization: Restrict the Responsibilities LoV without Disabling the End-Date Field on the form FNDSCAUS

Posted by in Oracle EBS
7 Comments

Purpose
The Purpose of this Form Personalization is to restrict the list of responsibilities that a user can assign using the ‘Users’ function that access the form FNDSCAUS.

Description
The implementation of this Form Personalization is divided in 3 steps:
1. Restrict the List of Values of the Responsibilities.
2. Restrict the Direct Responsibility List.
3. Restrict the Usernames List.

Form Personalization Function Definition
Responsibility: System Administrator
Navigate: Application -> Function
Define a new function that is a copy of the existing ‘Users’ Function (FND_FNDSCAUS). This new function will be called XX_FND_FNDSCAUS:

Once the function is created, it should be added to one of the existing responsibility.

Form Personalization – Main Screen
The standard form FNDSCAUS before the Form Personalization looks like this:

The form FNDSCAUS after the Form Personalization wont show any change.

The standard List of Values (LoV) before the Form Personalization looks like this:

The List of Values (LoV) after the Form Personalization will show less responsibilities.

Restrict the List of Values of the Responsibilities
Before to start with the Form Personalization, the following profile options should be set to the user making the Form Personalization:
o ‘FND: Diagnostics’ set to ‘Yes’
o ‘Hide Diagnostics menu entry’ set to ‘No’
o ‘Utilities:Diagnostics’ set to ‘Yes’

From the horizontal menu select: Help -> Diagnostics -> Custom Code -> Personalize

In order to restrict the list of responsibilities to be showed in the LoV, we will need to tune the query that generate that LoV modifying the conditions specified in the WHERE clause.

Argument:
SELECT R.RESPONSIBILITY_NAME, A.APPLICATION_NAME, R.RESPONSIBILITY_ID, R.APPLICATION_ID FROM FND_APPLICATION_VL A, FND_RESPONSIBILITY_VL R WHERE ( R.APPLICATION_ID = A.APPLICATION_ID AND (R.VERSION = '4' OR R.VERSION = 'W' OR R.VERSION= 'M' OR R.VERSION = 'H') AND (R.END_DATE IS NULL OR (TRUNC(SYSDATE) BETWEEN R.START_DATE AND R.END_DATE)) ) AND (R.RESPONSIBILITY_NAME LIKE '%GB') ORDER BY RESPONSIBILITY_NAME

To customize the list of responsibilities in the LoV, the WHERE clause that should be modified is
AND (R.RESPONSIBILITY_NAME LIKE '%GB')

Restrict the Direct Responsibility List
To prevent a user from removing the END_DATE of responsibilities already assigned to a user account, it would be needed to modify the list of the responsibilities showed after an username is queried.

Value:

(responsibility_id, responsibility_application_id) in (select responsibility_id, application_id from fnd_responsibility where (version = '4' or version = 'W' or version = 'M' or version = 'H') ) and (responsibility_id != 50304 and responsibility_id != 20419 and responsibility_id != 20420 and responsibility_id != 20872 and responsibility_id != 25623 and responsibility_id != 50237 and responsibility_id != 50278 and responsibility_id != 50303 and responsibility_id != 50573 and responsibility_id != 51788 and responsibility_id != 51808 and responsibility_id != 51809 and responsibility_id != 51810 and responsibility_id != 51811 and responsibility_id != 51828)

Restrict the Usernames List
To prevent a user from resetting the password of an admin user account (like SYSADMIN) it would be needed to modify the list of usernames to be queried.

Value:
user_name not in ('ANONYMOUS', 'AUTOINSTALL', 'INITIAL SETUP', 'FEEDER SYSTEM', 'CONCURRENT MANAGER', 'STANDALONE BATCH PROCESS', 'SYSADMIN', 'MOBILEADM', 'ASGADM', 'BOL-OPS', 'BOL-SETUP', 'IBEGUEST')

If it helps leave your comment or feedback! Thank you!

# # #

7 thoughts

  1. Hi,

    I was wondering how you get the original select script for a LOV? Using forms personalization, I am trying to limit a LOV. I followed your steps but created my own SQL for the LOV. I have read that the SQL must be the same as the original LOV’s sql. How to I get the original sql?

  2. Basically you have 2 ways.

    First make sure that you have these profile options set for your user
    ‘FND: Diagnostics’ set to ‘Yes’
    ‘Hide Diagnostics menu entry’ set to ‘No’
    ‘Utilities:Diagnostics’ set to ‘Yes’
    Don’t set them absolutely at SITE level.

    1.
    Access the form you want to personalize and query the LoV.
    Help -> Diagnostics -> Examine
    Block: SYSTEM
    Field: LAST_QUERY
    Click OK

    2.
    Help -> Diagnostics -> Trace -> Regular Trace
    You will see a Note message that will contain a file.trc path. Copy that path somewhere.

    Access the form you want to personalize and open the LoV.
    Help -> Diagnostics -> Trace -> No Trace

    Open the file.trc on your server. It should contain the default query you need. But the trace files will contain quite few rows…

    Have fun!

  3. can you please tell me how to handle the funtions in form personalization

    thanks,
    Amarnath

  4. Hi Amarnath, could you be more specific with your question? Thanks!

  5. Saurabh

    January 1, 2014

    Very nice article.Helped me a lot.
    Thanks Danilo!

  6. I tried to follow the steps you mentioned to restrict LOV data in the FAH Mapping set find screen, but its not working.Can you share your thoughts plz

  7. i have a requirement to change the password expiration to 30 days once the admin responsibilities are activated for the user

Reply