LOV Change - R12 Forms Personalization

One of my buddy pinged me and asked me if we can change / modify the LOV query in R12-Forms using personalization.

At a first glance, I felt may be I cannot.
Later I did a Google and found couple of posts indicating few possibilities with certain limitations (that they are for future release).

I thought as we were on 12.0.6(RUP6) , lets make a try.

For a quick overview of Personalization in forms go through this Metalink Document.

Summary:

• Step1 we will create a record group from query using a built in
• Step2 Apply this record group to the existing LOV.

Limitations:

We cannot use the LOV for WHEN-NEW-ITEM-INSTANCE trigger of that LOV item.
The new record group query structure should actually mimic the existing record group query structure, say column names etc.

Use Case:

Users Definition form: Restrict the suppliers LOV to show only one supplier.

Before personalization when i clicked the LOV for suppliers, i could see a long list of suppliers.



Start Personalization.
Open the form which requires personalization in the application via.
Help > Diagnostics > Custom code > Personalize

A new personalization form opens


Enter Sequence, Description, Level, Enabled Flag.
Select the condition as : When-New-Form-Instance
Condition : Not applicable here
Context Level : Site



Define Actions.
Two Step proces.
a. Define Record Group using the built in-Create Group from Query
b. Assign Record Group to the LOV.

a) Define Sequence 1 for a record group,
Type : Built in
Built in Type : Create Record group from Query
Group Name : XX_(STD GROUP NAME)
here the std group name was SUPPLIER_GROUP hence i used XX_SUPPLIER_GROUP
Argument : Here comes our Query, I want to show only one Supplier in the LOV.

select full_name, supplier_name, supplier_location, supplier_contact_id from icx_supplier_contact_lov where supplier_name = 'COMPUCOM SYSTEMS INC'
order by full_name, supplier_name, supplier_location



b) Define Sequence 2, Assign new Record group to the existing LOV name. Type : Property Object Type : LOV Target Object: SUPPLIER_LOV
( I got this info by actually opening the form in form builder)
Also we can click the list item to see the available lov's
Property_Name: GROUP_NAME
Value : Previous step record group name i.e. XX_SUPPLIER_GROUP

Validate them and Apply.

Test your LOV from the actual form.
When i click on the supplier lov now i see only supplier i.e. COMPUCOM SYSTEMS.

Hope you enjoyed this.

Thanks
Siva.