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

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’

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…

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')