How to change a LOV query Using Forms Personalization
We can change the LOV query using form personalization. Sample provided below (Payment form).
Requirement:
We have to change the “type” field lov query for populating the custom values.
To achieve this we have to follow below process.
Step-1:
Login into application and switch the Payable Manager Responsibility .
Nav->Payment->Entry->Payments.
Consider the TYPE field (PAY_SUM_FOLDER. PAYMENT_TYPE)
Here Type LOV currently shows 3 values . Instead we have the requirement to populate these values in the LOV. (Manual, Netting, Payment Process Request, Quick, Refund).
Step-2: Identify the form .fmb name (NAV:- Help->about oracle Applications)
Step-3:
Connect to application server and download the .fmb (APXPAWKB.fmb).
Open the form in form builder and check the LOV name for “type” field.
Step-4: Check the record group for attached LOV and take the sql statement.
(select displayed_field, lookup_code from ap_lookup_codes where lookup_type = ‘PAYMENT TYPE’ and lookup_code not in (‘A’,’N’) order by upper(displayed_field))
Step-5: Go to the payment form in application and navigate to form personalization form .
Nav:- help->Diagnostics-> Custom code ->Personalize
1. Seq :10
2. Description : Payment Type LOV
3. Level : Function
4. Enabled check box enable
5. Click on condition and assigned below properties.
6. Condition : WHEN-NEW-FORM-INSTANCE
7. Processing Mode : Not In Enter-Query Mode
Step-6: Click on Actions button and assigned the below properties.
1. Seq :10
2. Type :Builtin
3. Description : LOV (Optional)
4. Builtin Type : Create Record Group from Query.
5. Argument : Placed custom select statement (SELECT displayed_field , lookup_code FROM ap_lookup_codes WHERE lookup_type = 'PAYMENT TYPE' ORDER BY UPPER (displayed_field))
6. Group Name : XX_PAYMENT_TYPE (Customer Record group name)
Step-7: Click on action and create 2nd action and assigned below values.
1. Seq : 20
2. Type :Property
3. Language : All
4. Object Type : LOV
5. Target Object : PAY_VALID_PAYMENT_TYPES (lov Name)
6. Property name : GROUP NAME
7. Value : XX_PAYMENT_TYPE (Here assigned the custom group into existing LOV)
Step-8: Click on action and create 3rd action and assigned below values.
1. Seq : 30
2. Type :Property
3. Language : All
4. Object Type : Item
5. Target Object PAY_SUM_FOLDER.PAYMENT_TYPE (Column Name)
6. Property name : VALUE
7. Value :Manual
Step-9: Save the all work and close the form and re-open the form then check the changes.
