add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, September 29

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:

Snapshot%202009 01 15%2022 21 49 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 22 39 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 25 45 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 26 25 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 26 47 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 27 07 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

space Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form 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:

Snapshot 2009 01 15 22 35 33 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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

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

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

howto restrict the responsibilitiest list to be assigned 02 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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.

Snapshot 2009 01 15 22 30 58 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

Snapshot 2009 01 15 22 31 17 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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

Snapshot 2009 01 15 22 31 29 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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

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.

howto restrict the responsibilitiest list to be assigned 07 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

howto restrict the responsibilitiest list to be assigned 08 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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)

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

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.

howto restrict the responsibilitiest list to be assigned 09 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

howto restrict the responsibilitiest list to be assigned 10 Form Personalization: Restrict the Responsibilities LoV without Disabling the End Date Field on the form FNDSCAUS

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

No comments: