Making a list of values context-sensitive using forms personalization

In Oracle EBS there are lots of list of values. Most of the times you can change these values by editing the lookup values. In some cases you want to show a subset of the lookup-values based on another field in the same form. In this blog-entry there will be an example how to do this based on the fields user status and employment category for contingent workers in Oracle HR. The case is that we only want to show the values in the list of values for the employment category that are eligible with the chosen user status.

Step 1:
Create a flexfield for the reference-field CWK_ASG_CATEGORY. The flexfield can be defined in the descriptive flexfield ‘Common Lookups’.Navigate to System Administrator – Application – Flexfield – Descriptive – Segments. Query the flexfield title ‘Common Lookups’ and defreeze the flexfield. Create a new record in the block Context field values.

Code:	CWK_ASG_CATEGORY
Name:	free field for you to fill-in
Description:	free field for you to fill-in

Click on the button Segments.
Create a new record with the following data:

Number:	10
Name:	free field for you to fill-in
Window prompt:	free field for you to fill-in
Column:	Attribute1 (or another free attribute field)
Valueset:	150 Characters Optional

You can also create a valueset based on the assignment status, so you can choose the available assignment status options in your environment.
Save and freeze the descriptive flexfield. 

Step 2:
Fill in the corresponding assignment status in the created descriptive flexfield.Navigate to NL HRMS Manager – Other Definitions – Application Utilities Lookup. Query the type CWK_ASG_CATEGORY. Every record has an enabled flexfield now. In this flexfield you can fill in the associated user status. 

Step 3:
Create the right personalization in the forms.Navigate to the right form.In this case a new function has been created on the standard People and assignment form.Click on Help – Diagnostics – Custom Code – Personalize
Create a new record on the top of this screen.

Seq:	unique number
Description:	free field for you to fill-in
Level:	Dependent if you are using the standard screen or a function based on the custom screen. It is advisable to make personalisations on function level.
Enabled:	Yes

Tab Condition:  

Trigger Event:	When-new-item-instance
Trigger Object:	ASSGT.EMPLOYMENT_CATEGORY_MEANING
Condition:	can be empty.

By settings these conditions this personalization will be set when the field employment category is touched.
Tab Actions: We are going to create two actions:

1. Defining the custom list of values
2. Attach the custom list of values to the field employment category.

Action 1:

Seq:	10
Type:	Builtin
Description:	free field for you to fill-in
Language:	All
Enabled:	Yes
Builtin Type:	Create record group from query
Argument:	Here you can define the query on which the list of values is based. In this example the query is: SELECT l.lookup_code, l.meaning employment_category FROM hr_leg_lookups l,    fnd_lookup_values flv WHERE ((l.lookup_type = ‘EMP_CAT’ AND :assgt.assignment_type = ‘E’) OR (l.lookup_type = ‘CWK_ASG_CATEGORY’ AND :assgt.assignment_type = ‘C’)) AND l.enabled_flag = ‘Y’ and l.lookup_type = flv.lookup_type AND l.lookup_code = flv.lookup_code AND FLV.LANGUAGE = ‘NL’ and flv.attribute1 LIKE :ASSGT.USER_STATUS AND :ctl_globals.session_date BETWEEN NVL(l.start_date_active,:ctl_globals.session_date) AND NVL(l.end_date_active,:ctl_globals.session_date) ORDER BY l.meaning
Group name:	XX_LOV_SUBCAT

Oracle uses his own queries to fill the standaard list of values. These queries have a specific layout. This means that the fields in the select-clause have to be the same as Oracle has in the standaard list of values. To get the standard query you will have to open the form in forms builder. Please check with the technical consultants to get the ‘standard’ query. 
Action 2:

Seq:	11
Type:	Property
Description:	free field for you to fill-in
Language:	All
Enabled:	Yes
Object type:	LOV
Target Object:	EMPLOYMENT_CATEGORIES
Property Name:	GROUP_NAME
Value:	XX_LOV_SUBCAT

To test this personalization you have to save the personalizations, close the people and assignments form and open it again. 
There is just one problem left. It is still possible to change the user status, and leave the employment category intact. To prevent this we create a third personalization. 
Step 4:
Create a new record under the first personalization record.

Seq:	unique number
Description:	free field for you to fill-in
Level:	Dependent if you are using the standard screen or a function based on the custom screen. It is advisable to make personalisation’s on function level.
Enabled:	Yes

Tab Condition:

Trigger Event:	WHEN-VALIDATE-RECORD
Trigger Object:	ASSGT
Condition:	:ASSGT.EMPLOYMENT_CATEGORY_MEANING not in (select meaning from fnd_lookup_values where lookup_type = ‘CWK_ASG_CATEGORY’ and language = ‘NL’ and attribute1 = :ASSGT.USER_STATUS)

Tab Actions:

Seq:	10
Type:	Message
Description:	free field for you to fill-in
Language:	All
Enabled:	Yes
Message type:	Warn
Message text:	Here you can fill-in the warning message.

Seq:	11
Type:	Property
Description:	free field for you to fill-in
Language:	All
Enabled:	Yes
Object Type:	Item
Target Object:	ASSGT.EMPLOYMENT_CATEGORY_MEANING
Property Name:	Value
Value:	(empty)

 These personalizations give you a warning when the employment category is not valid in relation to the user status and will blank the employment category field.