| Oracle® Fusion Applications Developer's Guide 11g Release 1 (11.1.2) Part Number E15524-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Applications Developer's Guide 11g Release 1 (11.1.2) Part Number E15524-02 |
|
|
PDF · Mobi · ePub |
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.
This chapter includes the following sections:
Section 10.5, "Annotating Lookup Code Reference Attributes for Set-Enabled Lookups"
Section 10.6, "Integrating Lookups Task Flows into Oracle Fusion Functional Setup Manager"
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.
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 Section 10.6, "Integrating Lookups Task Flows into Oracle Fusion Functional Setup Manager".
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 Section 10.2.1, "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.
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"
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 Chapter 8, "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.
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.
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.
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 Section 10.1.2, "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:
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:
begin
fnd_lookups_util.register_lookup_views(
p_view_application_short_name => 'FND',
p_set_enabled => 'N',
p_lookup_type_view => 'FND_STANDARD_LOOKUP_TYPES',
p_lookup_code_view => 'FND_LOOKUPS');
end;
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.
For more information, see Section 10.1.2, "Standard, Set-Enabled, and Common Lookup Views".
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.
Lookups that are referenced in the entity object must have view accessors.
To reference lookups:
Import the lookups standard view objects into your project and make sure they are referenceable.
Open the entity object for editing.
On the View Accessors tab, add a view accessor. The View Accessors page appears.
Select a view object from the left-hand list and shuttle it to the right-hand list, then specify an accessor name.
Select the new view accessor and click Edit. The Edit View Accessor page appears.
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.
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 Section 10.4.1, "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 Section 10.4.2, "How to Define a Key Exists Validator".
Caution:
If an attribute in your transactional entity was defined withnull 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:
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.
On the Rule Definition tab, 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.
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.
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.
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.
On the Rule Definition tab, select a Validation Target Type of View Accessor.
Select the entity object lookup code attribute on the left-hand list, and the corresponding view accessor validation target lookup code attribute on the right-hand list.
Click Add to include the attribute pair on the mapping list.
Select the entity object transient lookup type attribute on the left-hand list, and the corresponding view accessor validation target lookup type attribute on the right-hand list.
Click Add to include the attribute pair on the mapping list.
Select the entity object transient setID attribute on the left-hand list, and the corresponding view accessor validation target setID attribute on the right-hand list.
Click Add to include the attribute pair on the mapping list.
Select the Validation Execution tab, as shown in Figure 10-5.
Because the validation should be executed every time the determinant value changes, it should be specified as a triggering attribute.
Note:
The foreign key attributes (including transient atributes) 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 Message.
Click OK to generate the key exists validator for this lookup.
Save your project.
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:
Edit your set-enabled transaction table entity object.
Annotate each lookup code reference with setID machinery metadata, as shown in Figure 10-6.
To specify an attribute for use as a lookup code reference, select the attribute in the entity object editor. On the Applications tab of the Property Inspector.
For a set-enabled lookup type, specify which determinant attribute on the entity object drives the setID of this lookup reference.
For a set-enabled foreign key, you should also specify the setID determinant attribute that drives the foreign key reference.
Specify the SetID View Application Id and SetId LookUp Type properties.
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.
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 more information about task flows, see the Oracle Fusion Applications Common Implementation Guide.
|
 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
