Oracle: Using Forms Personalisations to restrict a List of Values

In this scenario there is a requirement for end users in the Payables Department to be able to amend and create Flex Values (using the seeded Segment Values form). As there are many Flex Values that these users should not be given access to, Forms Personalisations is used to restrict the form (this personalisation can be adapted to any LOV in any form).

The value set is called XXAPPS_CUST_ID, and the form to be personalised is Segment Values (FNDFFMSV).

As we will be modifying the seeded Record Group attached to the LOV the first step is to download the FMB file from the application server and open it in Forms Developer to identify the query used in the record group. The record group attached to the LOV is FLEX_VALUE_SET_NAME_QF and the query is:

select v.validation_type, v.flex_value_set_name,
l.meaning, v.parent_flex_value_set_id, v.flex_value_set_id
from fnd_flex_value_sets v,
fnd_lookups l,
fnd_flex_validation_tables t
where v.flex_value_set_id = t.flex_value_set_id (+)
and ((v.validation_type in ('D','I','X','Y')) or
(v.validation_type = 'F' and t.summary_allowed_flag = 'Y'))
and l.lookup_code = v.validation_type
and l.lookup_type = 'SEG_VAL_TYPES'
order by v.flex_value_set_name

Within Forms Personalisations create a new personalisation with a Trigger Event of WHEN-NEW-FORM-INSTANCE and the following Actions;

Action Type: Builtin
Builtin Type: Create Record Group from Query
Argument:
select v.validation_type, v.flex_value_set_name,
l.meaning, v.parent_flex_value_set_id, v.flex_value_set_id
from fnd_flex_value_sets v,
fnd_lookups l,
fnd_flex_validation_tables t
where v.flex_value_set_id = t.flex_value_set_id (+)
and ((v.validation_type in ('D','I','X','Y')) or
(v.validation_type = 'F' and t.summary_allowed_flag = 'Y'))
and l.lookup_code = v.validation_type
and l.lookup_type = 'SEG_VAL_TYPES'
and v.flex_value_set_name in ('XXAPPS_CUST_ID')
order by v.flex_value_set_name
Group Name: XXAPPS_LOV
This creates a new Record Group (based on the seeded query but restricted to only the XXAPPS_CUST_ID value set).

Action Type: Property
Object Type: LOV
Target Object: FLEX_VALUE_SET_NAME_QF
Property Name: GROUP_NAME
Value: XXAPPS_LOV
This assigns the Records Group to the List Of Values.

These steps are all that is required to restrict the LOV, however to fully restrict the form we also need to amend the DEFAULT_WHERE (to stop users running ‘non-restricted’ queries IE: CTRL+F11) and also disable the other Radio Buttons in the Form.

To amend the DEFAULT_WHERE create the following action:

Action Type: Property
Object Type: Block
Target Object: VSET
Property Name: DEFAULT_WHERE
Click in the Value field and select 'Get Value' – this will return the seeded value, this can then be modified to restrict the results:
((validation_type IN ('I', 'D', 'X', 'Y') OR (validation_type = 'F' AND summary_allowed_flag = 'Y')) and parent_value_set_name in ('XXAPPS_CUST_ID'))

To disable the other Radio Buttons create actions to set the Property ENABLED to False for the following items:
QUERY_FIND.REGION_POPUP.KEY
QUERY_FIND.REGION_POPUP.DESCR
QUERY_FIND.REGION_POPUP.CONC
CONTROL.REGION_POPUP.KEY
CONTROL.REGION_POPUP.DESCR
CONTROL.REGION_POPUP.CONC


Now when the form is opened the only available option is Values Sets and the LOV and Default Where is restricted to the XXAPPS_CUST_ID value.