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!




Other Posts
Comments

This entry was posted on Friday, April 3rd, 2009 at 6:00 am and is filed under Oracle EBS. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
7 Comments so far

  1. Richard on November 26, 2009 3:06 pm

    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. Danilo Vizzarro on November 27, 2009 2:02 pm

    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. amar on July 5, 2013 2:56 pm

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

    thanks,
    Amarnath

  4. admin on July 17, 2013 9:55 am

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

  5. Saurabh on January 1, 2014 2:11 pm

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

  6. Joe on May 11, 2015 11:43 pm

    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. avinash on February 24, 2016 8:08 pm

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

Name (required)

Email (required)

Website

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Share your wisdom