Friday, 9 January 2015

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.








2 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  2. Thank you!!! Very helpful!

    ReplyDelete