A List of Values (LOV) within a form can be modified using Forms Personalization. By LOV we mean that LOV that is embedded within the form. This LOV was created within the development phase.
Let us first look at the Invoice form.
Navigate to the Payables Invoice form in an AP responsibility
Click on the Supplier field and the LOV opens. Search for all suppliers by searching for %.
All the suppliers are displayed in the LOV. We can select a supplier from this list and proceed working on Invoice form.
We would like to make a modification to this list. The idea is to modify the list based on the responsibility so that different responsibilities get access to different suppliers. So we have decided to show all suppliers whose names start with A if the Oracle user has logged into the responsibility that starts with EY
On the AP Invoice form click on Help > Diagnostics > Custom Code > Personalize
Enter a new personalization as shown below
The triggering condition is when the user clicks on the Supplier Name field on the form. Hence the Trigger Event is WHEN-NEW-ITEM-INSTANCE and the Trigger Object is INV_SUM_FOLDER.VENDOR_NAME. The condition of personalization checks whether in the logged in responsibility starts with EY or not.
Action 1: Create a new data set for the LOV
The query used is,
SELECT vendor_name, vendor_number, vendor_id, vendor_type_lookup_code, num_1099, type_1099, vat_registration_num, employee_id, awt_group_id, allow_awt_flag, hold_all_payments_flag, num_active_pay_sites FROM po_vendors_ap_v WHERE active_flag = 'Y' AND enabled_flag = 'Y' AND num_active_pay_sites > 0 AND vendor_name LIKE 'A%' ORDER BY UPPER (vendor_name)
Action 2: Set the new data set to the LOV
The previously created data set is now attached to the LOV which is called from the Supplier Name field.
How do we know the query of the record group that needs to be changed? The structure of the query has to be perfect else the form will throw an error each time we try to access the LOV.
To find the original structure of the query we need to open the form in Forms builder. We have come to know the name of the form from Personalization form itself. It is APXINWKB.fmb. You will find the form under $AU_TOP/forms/US folder on the server.
We need to know the name of the original record group being used on the form. On Action 2 of the personalization step above we shall remove the Value as shown below,
Save the data on the form. Close the form and reopen it and come to this step. Now click on Get Value button.
You will get the name of the record group, i.e. ACTIVE_VENDORS, used in the form. This value is overwritten to suit the requirement. Now the value can be changed to XX_VENDORS_NAME so that the personalization remains intact. We shall extract the query of this record group from the form and then modify the query as per the SQL structure.
Now download and open the form, APXINWKB.fmb, in Forms Builder
Open the record group
The query of the record group is,
SELECT vendor_name, vendor_number, vendor_id, vendor_type_lookup_code, num_1099, type_1099, vat_registration_num, employee_id, awt_group_id, allow_awt_flag, hold_all_payments_flag, num_active_pay_sites FROM po_vendors_ap_v WHERE active_flag = 'Y' AND enabled_flag = 'Y' AND num_active_pay_sites > 0 ORDER BY UPPER (vendor_name)
This query has been modified and saved in personaliztion in Action 1 above.
Test the form
Reopen the Payables Invoice form.
Open the Supplier LOV and search for all suppliers by typing % in the search field. The LOV automatically changes the condition to A% and displays the list of all suppliers whose names start with A.
Now you have overwritten the seeded LOV using personalization.
- How to set a form to auto-query on opening (oraclemaniac.com)
- Personalize OAF Pages: Modify the label on a Text box (oraclemaniac.com)
- Personalize OAF Pages: Disable editable property for a field (oraclemaniac.com)
- Dynamically wrap PLSQL code (oraclemaniac.com)
- Restrict users from creating or deleting records on a form (oraclemaniac.com)
- Restrict users to view specific records on Oracle Apps forms (oraclemaniac.com)