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 2^nd 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.