How to Restrict LOV Using Oracle Form Personalization

Overview

·         The Form Personalization feature allows you to declaratively alter the behavior of Forms-based screens, including changing properties, executing built-ins, displaying messages, and adding menu entries.

·         For each function (a form running in a particular context based on parameters passed to it), you can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

·         Rules can be specified as acting either at the Function level (the default) or at the Form level. In this latter case they are applied for all functions that run the form. When creating rules at the Form level, pay close attention to the Sequence number and how it will blend with other rules at the function level for the same form.

·         An Event is a trigger point within a form, such as startup (WHEN-NEW-FORM-INSTANCE),or when focus moves to a new record (WHEN-NEW-RECORD-INSTANCE). There are standard events that almost every form sends, and certain forms send additional product-specific events.

·         The Scope is evaluated based on the current runtime context to determine if a Rule should be processed or not. The Scope can be at the Site, Responsibility, User, or Industry level. Each Rule can have one or more Scopes associated with it.

NOTE: the scope of 'Industry' is reserved for future use.

·         The Condition is an optional SQL code fragment that is evaluated when the Event occurs; if it evaluates to TRUE then the Actions are processed.

·         Each Action consists of one of the following:

o   setting a Property, such as making a field Required or hiding a Tab page

o   executing a Built-in, such as GO_BLOCK, DO_KEY or FND_FUNCTION.EXECUTE

o   displaying Message

o   enabling a menu entry

·         Once Rules are defined, when the target function is run then the Rules are automatically applied as events occur within that form.

·         Although the Form Personalization feature is declarative, the intended audience is a person familiar with Oracle Forms including the PL/SQL programming language, and the Oracle E-Business Suite Developer’s Guide. Additionally, any change made could interfere with the base code of a form (the code that Oracle ships), thus the Support statements discussed later in this chapter must be followed diligently.

Using the Personalization Form

To create personalization for a particular function, first invoke that function from the Navigation menu.

·         Run the form that you want to modify

·         Select Help > Diagnostics > Custom Code > Personalize from the pull down menu

Profiles

o   ‘Hide Diagnostics menu entry’ will hide or show ‘Diagnostics’ sub-menu in help menu set it to ‘No’

o   ‘Utilities:Diagnostics’ set ‘Yes’, oracle will prompt for APPS pwd when you select diagnostics Examine or Personalization

How to Restrict Customer Name or Customer Number LOV in Sales order form

Purpose: OM Sales Order Form display global customer list instead of Customer have ship to or Bill to in current Operating Unit and also Customer Class is not shown in Customer LOV.

Solution: Extracted stand Customer LOV query ( Please check below Note to extract LOV Query) and modified Where Clause to restrict list and concatenated Customer Class details along with other column in LOV

1.       Go to the Forms Personalization screen from the sales order form (Help>Diagnostics>Custom Code>Personalize).

2.       Enter the personalization name in header and select level >> Form and check ‘Enabled’ check box then enter following in the Condition tab.

Trigger Event = WHEN-NEW-ITEM-INSTANCE
Trigger Object = ORDER.CUSTOMER_NUMBER or ORDER.SOLD_TO (customer name)
   *Note this example is for the Customer Number field, not the Customer field.

Context (if restricting to a specific user) for testing
Level = User
Value = <User Name>

3.       Enter the following in the Actions tab. Here we have two sequence lines. We are restricting the Customer Number or Customer LOV to only show customer have site setup in current operating unit.

Seq: 10 Type: Builtin Description: xxxxx Builtin Type: Create Record Group from Query Argument: Enter query to restrict the value Group Name: TEST_FOR_CUST_OEXOEORD

Seq: 20  Type: Property  Object Type: LOV  Target Object: CUSTOMER_NUMBER  Property Name: GROUP_NAME  Value: TEST_FOR_OEXOEORD

Sample Query for Customer Number

SELECT ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER,
ACCT.CUSTOMER_CLASS_CODE||'-'||ACCT.ACCOUNT_NAME ACCOUNT_DESCRIPTION,

PARTY.PARTY_NUMBER REGISTRY_ID, PARTY.PARTY_NAME PARTY_NAME,

PARTY.PARTY_TYPE, ACCT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,

PARTY.EMAIL_ADDRESS EMAIL_ADDRESS, NVL(PARTY.GSA_INDICATOR_FLAG,'N') GSA_INDICATOR
FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT WHERE (ACCT.PARTY_ID=PARTY.PARTY_ID AND ACCT.STATUS='A' ) AND ACCT.CUST_ACCOUNT_ID IN (SELECT RAL.CUSTOMER_ID FROM APPS.RA_SITE_USES_ALL RSU, APPS.RA_ADDRESSES_ALL RAL

WHERE RAL.ADDRESS_ID = RSU.ADDRESS_ID AND RSU.ORG_ID=:ORDER.ORG_ID) ORDER BY PARTY_NAME

Sample Query for Customer Name

SELECT ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, ACCT.CUSTOMER_CLASS_CODE||'-'||ACCT.ACCOUNT_NAME ACCOUNT_DESCRIPTION, PARTY.PARTY_NUMBER REGISTRY_ID, PARTY.PARTY_NAME PARTY_NAME, PARTY.PARTY_TYPE, ACCT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS, NVL(PARTY.GSA_INDICATOR_FLAG,'N') GSA_INDICATOR FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT WHERE (ACCT.PARTY_ID=PARTY.PARTY_ID AND ACCT.STATUS='A' AND ACCT.CUST_ACCOUNT_ID IN (SELECT RAL.CUSTOMER_ID FROM APPS.RA_SITE_USES_ALL RSU, APPS.RA_ADDRESSES_ALL RAL WHERE RAL.ADDRESS_ID = RSU.ADDRESS_ID  AND RSU.ORG_ID=:ORDER.ORG_ID)) ORDER BY PARTY_NAME

*Note -  There is no semicolon at the end of the statement and where condition can be modified as per business requirement.

In order to build this statement, we first took a trace (Help>Diagnostics>Trace>Trace with Binds) of the action to invoke the Customer Number LOV 'before' setting up the Forms Personalization. We created this sql statement in the same way as the statement seen in the trace file, but replacing the WHERE condition for the account_number with an actual value that we wanted to restrict the LOV. We found that if the sql entered in the Argument field was missing one of the selected columns, for example, 'nvl(party.gsa_indicator_flag,'N') gsa_indicator', the LOV restriction did not happen. Hence, if the setup in this note is not working, it is recommended to take similar troubleshooting steps by generating a trace file.