This chapter includes the following sections:
Lookups in applications are used to represent a set of codes and their translated meanings. For example, a product team might store the values 'Y
' and 'N
' in a column in a table, but when displaying those values they would want to display "Yes" or "No" (or their translated equivalents) instead. Each set of related codes is identified as a lookup type. There are many different examples of these across multiple applications.
Lookups Implementation
When you have completed the development process as discussed in this chapter and have delivered your application with the ability to use lookups, application implementers and administrators must then be able to define and maintain lookups that are appropriate to the organization that will use the application. They can accomplish these tasks using the Manage Standard Lookups, Manage Set-Enabled Lookups and Manage Common Lookups applications.
You make these setup applications available to implementers and administrators by incorporating their task flows into Oracle Fusion Functional Setup Manager. For more information, see About Integrating Lookups Task Flows into .
Lookups are codes that are defined in the global FND_LOOKUP_VALUES table, which is striped into multiple virtual tables using a VIEW_APPLICATION_ID column. Each of these virtual tables is thus identified as a view application. Each view application is exposed as a database view, and all have separate ADF Business Components. It is the responsibility of the team who owns a particular view application to provide both the database view and the necessary ADF Business Components objects. Only these view definitions, and any validation code supporting them, should access the underlying lookups tables directly. All other code that references lookups should always go through the database views and their supporting ADF Business Components objects, never directly referencing either the lookups tables or their base classes.
Note:
If you have a custom view application, you need to prepare a custom lookup view. For more information, see How to Prepare Custom Lookup Views.
Lookup codes are identified in an application by the following keys:
A lookup view, which defines a distinct set of lookup types.
Each lookup view is accessed through its own view, and may have different attributes or different validation, almost as if it were a separate table.
A lookup type, which is a string identifier of a type that groups certain codes together; for example, COLORS
.
Within each lookup type, multiple lookup codes can be defined. Example 10-1 shows sample code for defining multiple lookup codes.
A lookup code, which is a string identifier for a code within a type; for example, RED
.
A set or setID (for set-enabled lookups), which identifies the reference data set to which the lookup code belongs.
For more information about setIDs, see Managing Reference Data with SetIDs.
The FND_LOOKUP_TYPES table defines the lookup types available.
Note:
When you register a lookup view application, you set a SET_ENABLED flag to indicate that the lookup view is set enabled. For this to be valid, every lookup type within that lookup view must have a reference group defined. The reference group is part of the lookup definition, and was defined when the lookup was defined. How that happens is beyond the scope of this documentation.
A reference to a non set-enabled lookup can be implemented exactly like any other foreign key reference, by specifying the lookup type in the view criteria. For set enabled lookups, you must specify the following additional properties, but only to add the indirection through the setID metadata:
Indicate the view application ID and lookup type for lookup code attributes.
Indicate the determinant attribute and determinant type, if the lookup type is set-enabled.
The use of setID metadata allows for the use of generic lookup entity objects, because the lookup type is automatically bound based on the metadata that you provide.
Example 10-1 Defining Multiple Lookup Codes
View Application = 0 (FND_LOOKUPS) Lookup Type = COLORS "Colors" Lookup Code = RED "Red" Lookup Code = YELLOW "Yellow" Lookup Code = GREEN "Green" View Application = 3 (FND_COMMON_LOOKUPS) Lookup Type = COLORS "Colors" Lookup Code = MAGENTA "Magenta" Lookup Code = CHARTREUSE "Chartreuse" Lookup Code = AQUAMARINE "Aquamarine"
All lookups business objects exist in the publicEntity subpackage of the oracle.apps.fnd.applcore.lookups.model package. They can be imported into any Oracle JDeveloper application through Lookups-Model.jar
. They are as follows:
Lookup Types
Entity Object: LookupTypePEO
View Object: LookupTypePVO
Base Table/View: FND_LOOKUP_TYPES_VL
Each lookup type defines a set of lookup codes, and describes the intended usage of that set of codes. Note the FND_LOOKUP_TYPES_VL
table and ADF Business Components objects are only meaningful when a VIEW_APPLICATION_ID
is specified to choose the view application. You should never use either the table or the view without supplying the VIEW_APPLICATION_ID
.
Product teams that own a view application must expose a pre-defined view for lookup types, exposing only the lookup types appropriate to their view application.
Note:
Product teams that own a view application also are responsible for providing the service, the loader, the UI, and the database view.
If your product has no special validation requirements, you can place your lookups in one of the central lookup views such as FND_LOOKUPS. However, if you define your own view application, you must supply a database view to match it.
Lookup Values
Entity Object: LookupValuePEO
View Object: none
Base Table/View: FND_LOOKUP_VALUES_VL
The FND_LOOKUP_VALUES_B
table (along with FND_LOOKUP_VALUES_TL
) is the primary table that stores all the different lookup codes.
The FND_LOOKUP_VALUES_VL
view is extended by the views in the three following listings (FND_LOOKUPS
, FND_COMMON_LOOKUPS
, and FND_SETID_LOOKUPS
). If you want to define your own product specific lookups, you should extend this view as well. This object contains the subset of columns that are expected to be common to all views that extend from this, with any additional columns required being added on an as-needed basis.
These objects should only be referenced by lookup view application owners when defining their own views and ADF Business Components objects. All other references should go through the objects created for that lookup view. The three standard ones that Oracle ships are FND lookups, common lookups, and setID lookups. If other products have lookup views, you should use the entity objects and view objects provided for them by the owning team.
(FND) Lookups
Entity Object: LookupPEO
View Object: LookupPVO
Base Table/View: FND_LOOKUPS
The naming of the lookup objects can get confusing; the Lookups object is intended to refer specifically to FND lookups. The Lookup Values object in the previous listing is the generic object. The FND_LOOKUPS
view is primarily used to store FND-specific lookup values but is also used to store lookup values that are common across multiple applications. For example. the "Yes/No" example given in the overview might be used by multiple product teams, so to avoid duplication that code can be stored centrally in FND_LOOKUPS
.
This view extends from the FND_LOOKUP_VALUES_VL
view, but only selects rows that have VIEW_APPLICATION_ID = 0
and SET_ID = 0
.
Common Lookups
Entity Object: CommonLookupPEO
View Object: CommonLookupPVO
Base Table/View: FND_COMMON_LOOKUPS
Note:
This view also was used to store lookup codes that were common to multiple applications, but it now exists only for backward compatibility.
This view extends from the FND_LOOKUP_VALUES_VL
view, but only selects rows that have VIEW_APPLICATION_ID = 3
and SET_ID = 0
.
SetID Lookups
Entity Object: SetIdLookupPEO
View Object: SetIdLookupPVO
Base Table/View: FND_SETID_LOOKUPS
This view is used to store lookup codes that are set-enabled. The meanings corresponding to the given lookup code will vary depending on the value of the setID determinant.
This view extends from the FND_LOOKUP_VALUES_VL
view, but only selects rows that have VIEW_APPLICATION_ID = 2
.
Customization levels are defined on lookup types and can be used to enforce pre-defined data security policies that restrict how and by whom lookup types and their codes can be edited.
Valid values for CUSTOMIZATION_LEVEL are defined in the standard lookup type 'CUSTOMIZATION_LEVEL'. Table 10-1 lists these values.
Table 10-1 CUSTOMIZATION_LEVEL Lookup Codes
Lookup Code | Description |
---|---|
U |
User |
E |
Extensible |
S |
System |
At runtime, the customization levels are interpreted as follows:
User
Insertion of new codes is allowed
Updating of start date, end date, and enabled fields is allowed
Deletion of codes is allowed
Updating of tag is allowed
Extensible
Deletion of lookup type is not allowed
Insertion of new codes is allowed
Updating of start date, end date, enabled fields, and tag is allowed only if the code is not 'seed data'
Deletion of codes is allowed only if the code is not 'seed data'
Updating of module is not allowed
System
Deletion of lookup type is not allowed
Insertion of new codes is not allowed
Updating of start date, end date, and enabled fields is not allowed
Deletion of codes is not allowed
Updating of tag is not allowed
Updating of module is not allowed
In each of these scenarios, 'seed data' means LAST_UPDATED_BY = 'SEED_DATA_FROM_APPLICATION'. Also, to allow seed data to be edited, these rules are not enforced if the current user is 'SEED_DATA_FROM_APPLICATION'.
It is expected that the owner of a lookup view will produce entity objects and view objects based on the entity objects for standard lookups database objects; for example, HR_LOOKUPS, GL_LOOKUPS, OE_LOOKUPS and so on. These view objects will typically be used for lookup validation as well as LOVs. If you put your lookups in the standard lookup views, you do not have to define anything, but simply reference the objects that are already provided.
Additionally, multiple ViewCriteria may be exposed on the lookups view object to take care of date ranging the lookup by supplying bind parameters for start and end active dates.
For a description of lookups tables and views provided by Oracle Fusion Middleware Extensions for Applications and their corresponding public business objects, see About Standard_ Set-Enabled_ and Common Lookup Views.
If you have a simple lookup with no special requirements, you are free to define it in the centrally provided lookup views. You do not have to create your own lookup view just because you have lookups. However, if you have special validation requirements that are not satisfied by the central lookup views, you might want to create a private lookup view. If you do choose to create your own lookup view, you must take responsibility for the additional work required to support your lookup view as described in the following sections.
In preparing lookup views, you must perform several decision-based tasks.
To prepare lookup views, do the following:
You must create view accessors for all lookups data sources (FND_COMMON_LOOKUPS, FND_SETID_LOOKUPS, HR_LOOKUPS, and so on) that are referenced in the entity object.
You must create a validator for every foreign reference in an entity object. For set-enabled reference entities, the validator must be created at the entity object level, not at the attribute level, because it has dependencies on other attribute values such as the setID determinant attribute.
The type of validator to use depends on the expected size of the rowset for a given lookup type:
For a lookup definition where the rowset returned for a lookup type or lookup code is expected to be less than approximately 100 rows, use a list validator. See How to Define a List Validator.
For a lookup definition where the rowset returned for a lookup type or lookup code is expected to significantly exceed 100 rows, use a key exists validator. See About Defining a Key Exists Validator.
Caution:
If an attribute in your transactional entity was defined with null
values allowed, the validator that you create will skip that attribute, and the end user will receive no indication of any problem. To ensure that the attribute is validated, you must edit the attribute and select the Mandatory checkbox in the attribute properties.
You define a list validator for lookup definitions where the rowset returned for a lookup type or lookup code is expected to be less than 100 rows.
To define a list validator, do the following:
Open the entity object for editing.
On the Validators tab, add a validation rule for the entity. The Edit Validation Rule page appears, as shown in Figure 10-1.
At the top of the page, select a Rule Type of List
.
Figure 10-1 Lookups List Validator Rule Definition
On the Rule Definition tab, do the following:
Select the foreign reference column that is the lookup code (for example, SalaryCode
) as the attribute.
Select In
as the operator.
Select View Accessor Attribute
as the list type.
From the Select View Accessor Attribute list, select LookupCode
as the view accessor validation target lookup code attribute.
Select the Validation Execution tab, as shown in Figure 10-2.
Because the validation should be executed every time the determinant value changes, it should be specified as a triggering attribute.
Figure 10-2 Lookups List Validator Execution
Note:
The foreign key attributes that were mapped on the Rule Definition tab are by default added as triggering attributes.
In the Triggering Attributes section, select the determinant attribute from the left-hand list and shuttle it to the right-hand list.
Optionally, on the Failure Handling tab, specify a failure error message.
Click OK to create the list validator for this lookup.
The key exists validator will include the mapping of the foreign key attributes in the transactional entity to the corresponding attributes in the reference view accessor. There must be a foreign key attribute on the transactional entity for each primary key attribute on the reference entity. First, you must provide missing foreign key attributes in the form of transient attributes. Next, you can create the validator that uses those attributes.
These properties are used only for set-enabled lookups, and only to do setID indirection. The setID lookup type LOV will show only those lookup types that are defined in your specified view application ID.
Do the following to annotate lookup code reference attributes:
Every Oracle application registers task flows with a product called Oracle Fusion Functional Setup Manager. Functional Setup Manager provides a single, unified user interface that enables implementers and administrators to configure all multiple applications by defining custom configuration templates or tasks based on their business needs.
The Functional Setup Manager UI enables customers and implementers to select the business processes or products that they want to implement. For example, an HR application can register setup activities like "Create Employees" and "Manage Employee Tree Structure" with Functional Setup Manager.
There are application task flows for managing common lookups, set-enabled lookups, and standard lookups. To make these task flows available to application developers, implementers or administrators, you can register the appropriate task flow with Functional Setup Manager, using the parameters listed for each task flow in Table 10-2. These taskflows can be used to manage lookups in the centrally defined lookup views (FND_LOOKUPS, FND_COMMON_LOOKUPS, and FND_SETID_LOOKUPS). All other lookup views (and any associated taskflows) are owned by applications (as determined by the VIEW_APPLICATION_ID
). Contact the owning application for instructions on managing lookups in their lookup views.
Table 10-2 Lookups Task Flows and Parameters
Task Flow Name | Task Flow XML | Parameters Passed | Behavior |
---|---|---|---|
Manage Standard Lookups |
/WEB-INF/oracle/apps/fnd/applcore /lookups/publicUi/flow/ ManageStandardLookupsTF.xml# ManageStandardLookupsTF |
To invoke search mode to query and edit lookup types and their codes in the Standard Lookups view: mode='search' To restrict search mode to Standard lookups belonging to a particular product module: mode='search' moduleType='moduletype' moduleKey='modulekey' To invoke edit mode for a single lookup type and its lookup codes:
mode='edit'
lookupType='lookuptype'
To optionally specify a page heading for the task flow:
pageTitle='titlestring'
|
This task flow enables you to create and edit lookups in the centrally owned Standard view (view application = |
Manage Set-Enabled Lookups |
/WEB-INF/oracle/apps/fnd/applcore /lookups/publicUi/flow/ ManageSetEnabledLookupsTF.xml# ManageSetEnabledLookupsTF |
To invoke search mode to query and edit lookup types and their codes in the Set Enabled Lookups view: mode='search' To restrict search mode to Set Enabled lookups belonging to a particular product module: mode='search' moduleType='moduletype' moduleKey='modulekey' To invoke edit mode for a single lookup type and its lookup codes:
mode='edit'
lookupType='lookuptype'
To optionally specify a page heading for the task flow:
pageTitle='titlestring'
|
This task flow enables you to create and edit lookups in the centrally owned Set Enabled view (view application = |
Manage Common Lookups |
/WEB-INF/oracle/apps/fnd/applcore /lookups/publicUi/flow/ ManageCommonLookupsTF.xml# ManageCommonLookupsTF |
To invoke search mode to query and edit lookup types and their codes in the Common Lookups view: mode='search' To restrict search mode to Common lookups belonging to a particular product module: mode='search' moduleType='moduletype' moduleKey='modulekey' To invoke edit mode for a single lookup type and its lookup codes:
mode='edit'
lookupType='lookuptype'
To optionally specify a page heading for the task flow:
pageTitle='titlestring'
|
This task flow enables you to create and edit lookups in the centrally owned Common Lookups view (view application = |
For information about implementing your specific product family, do the following:
Access the Oracle Fusion Applications library.
See the Implementing Common Features guides for your product family.