10 Implementing Lookups

This chapter discusses how to use lookups for providing lists of values (LOVs) for application end users to select from, and for performing validation of newly entered data. It also discusses how to share lookup data across organizations by using setIDs to partition the data into different sets of LOVs. Each organization can then maintain its lookups in a common table, using LOVs specific to that organization.

10.1 Introduction to Lookups

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 Oracle Fusion applications.

Lookups Implementation

Once you have completed the development process as discussed in this chapter, and 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.

10.1.1 Overview of Lookups

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.

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 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.

10.1.2 Standard, Set-Enabled, and Common Lookup Views

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 the purpose of 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.

10.1.3 Lookup Customization Levels

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.

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'.

10.2 Preparing Entities and Views for Lookups

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.

10.2.1 How to Prepare Custom 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:

Decide whether you really need a private lookup view.

If you have no need for special attributes, special validation, or a private namespace for lookup types, you can use one of the centrally defined lookup views (FND_LOOKUPS, FND_COMMON_LOOKUPS, and FND_SETID_LOOKUPS). All of these lookup views are available for any product to use. If none of the central views meet your needs, you may define your own.

Note:

If you are using any of the three central lookup types (FND_LOOKUPS, FND_COMMON_LOOKUPS, and FND_SETID_LOOKUPS), you can skip the rest of this section.

Lookup views are owned by applications (as determined by the view_application_id). There can be only one lookup view per view_application_id. It is up to the owner of the lookup view to make the view available for other applications to use, or to designate the lookup view as private.

Decide whether your lookup view should be set enabled.

If so, you must expose set_id as part of the "primary key" of your lookup view, and all references to it will have to include, either directly or indirectly, the set_id to use.

Define a database view to expose the lookup types included in your lookup view.

At a minimum your view must select from the base FND_LOOKUP_TYPES_VL view, expose the internal name and the display name, and include "where VIEW_APPLICATION_ID =my_application_id" in the where clause. In addition, if your view is set enabled, the lookup types view must include the REFERENCE_GROUP_NAME column. You are free to join additional tables, add additional attributes, or add additional filters to the where clause as desired. A template for the view might be:

select LOOKUP_TYPE,
MEANING DISPLAY_NAME,
REFERENCE_GROUP_NAME, /* Only if set enabled */
...
from FND_LOOKUP_TYPES_VL
where VIEW_APPLICATION_ID = my_application_id
and ...

Define a database view to expose the lookup codes included in your lookup view.

At a minimum your view must select from the base FND_LOOKUP_VALUES_VL view, expose the lookup type, the lookup code internal name, and the lookup code display name, and include "where VIEW_APPLICATION_ID =my_application_id" in the where clause. In addition, if your view is set enabled, the lookup values view must include the SET_ID column as part of the primary key. You are free to join additional tables, add additional attributes, or add additional filters to the where clause as desired. A template for the view might be:

select LOOKUP_TYPE,
LOOKUP_CODE,
SET_ID, /* Only if set enabled */
MEANING,
...
from FND_LOOKUP_VALUES_VL
where VIEW_APPLICATION_ID = my_application_id
and SET_ID = 0 /* Only if not set enabled */
and ...

Register your lookup view application and database views.

All view applications and the views used to reference them must be registered in the FND_LOOKUP_VIEWS metadata table. To register your lookup views, write a SQL script that calls the FND_LOOKUPS_UTIL.REGISTER_LOOKUP_VIEWS PL/SQL API. For example:

This script registers required seed data, and must be run on every database instance.

Create ADF Business Components objects for your lookup view.

Each lookup view should have a separate entity object and view object (or PEO and PVO) for both lookup types and lookup codes, extending from the base entity object and view object provided for FND_LOOKUP_TYPES and FND_LOOKUP_VALUES.

Select the view criteria to use (if available), specify an order-by, and provide the bind parameter value.

Note:

All set-enabled view accessors are row sensitive (the determinant on the master or transactional row affects the query); therefore the Row-level bind values exist check box must always be selected for set-enabled view accessors. For example, view accessors to FND_SETID_LOOKUPS (set-enabled lookups cases) must have Row-level bind values exist selected because the setID value may change row by row and affect the validation result. Hence, the ViewAccessor Row Set will need to be refreshed row by row.

Click OK twice to finish creating the view accessor.

10.4 Defining Validators for Lookups

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:

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.

10.4.1 How to Define a List Validator

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:

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, select the foreign reference column that is the lookup code (for example, SalaryCode) as the attribute.

10.4.2 How to Define a Key Exists Validator

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.

To define a transient lookup type:

Open the transactional entity object for editing.

Create a new transient lookup type attribute to map to the LookupType attribute on the reference entity, as shown in Figure 10-3.

Figure 10-3 New Transient Lookup Type Entity Attribute

Set the Type to String.

Set the Value Type to Expression, and provide a constant value for the attribute.

Deselect the Persistent checkbox.

In the Updatable section, select Never, then click OK to create the transient attribute.

To create a key exists validator:

On the Validators tab, add a validation rule for the transactional entity. The Edit Validation Rule page appears, as shown in Figure 10-4.

At the top of the page, select a Rule Type of Key Exists.

Figure 10-4 Lookups Key Exists Validator Rule Definition

On the Rule Definition tab, select a Validation Target Type of View Accessor.

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.

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 Oracle Fusion 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.