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.








8 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
  3. BlueHost is definitely the best website hosting company for any hosting services you require.

    ReplyDelete
  4. This article is really very informative. Please share more article like this Thank you for sharing.
    Oracle Financials Training in Ameerpet

    ReplyDelete
  5. Thanks for sharing very good information about OCI Punchout.
    OCI Punchout

    ReplyDelete
  6. Thanks for sharing article about Benefits of OCI Punchout, Open Catalog Interface Punchout
    Benefits of OCI Punchout

    ReplyDelete