Skip Headers
Oracle® Fusion Applications Developer's Guide
11g Release 4 (11.1.4)

Part Number E15524-09
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

8 Managing Reference Data with SetIDs

This chapter describes how to share reference data across organizations by using setIDs to partition the data into different sets of values. Each organization can then maintain its data in a common table, using a set of values specific to that organization.

This chapter includes the following sections:

8.1 Introduction to SetIDs

Different organizations within a single company often need to use different sets of reference data to serve the same purpose. For example, the job codes for one country might be different from the job codes for another country. Different Oracle Fusion Applications customers should be able to make their own decisions about how to define the job codes, and be able to define a separate set for each organizational section of the enterprise. They should also be able to define a common set or sets and instruct the system which set should be used by which organizations. For example, job codes for software engineers might be MTS, SMTS, PMTS; job codes for managers might be M1, M2, M3. SetIDs enable them to accomplish this easily.

For information about set-enabling lookups, see Chapter 10, "Implementing Lookups".

SetID Implementation

Once you have completed the development process as discussed in this chapter, and delivered your application with the ability to use set-enabled reference data, application implementers and administrators must then be able to define and maintain reference data sets and set assignments that are appropriate to the organization which will use the application. They can accomplish these tasks using the Manage Reference Data Sets and Manage Reference Data Set Assignments applications, respectively.

You make these setup applications available to implementers and administrators by incorporating their task flows into Oracle Functional Setup Manager. For more information, see Section 8.3, "Integrating SetID Task Flows into Oracle Fusion Functional Setup Manager".

For information about how to use the setID setup applications, see the Oracle Fusion Applications Common Implementation Guide

8.1.1 Partitioning by SetID

SetIDs enable you to share a set of reference data across many organizations. Sharing reference data is a method of limiting the set of available values to those that are appropriate for a validated attribute. Some benefits of this include:

  • The list of values for a field in a user interface is reduced

  • An attribute passed into an API is validated against the limited set of values

The end goal is to save customers some effort in maintaining reference data by enabling it to be shared between different parts of the organization that implements applications. Reference data should not need to be maintained in multiple places at multiple times. Reference data is data in tables that you do not regard as transactional and high volume; for example, payment terms that can be used on a customer invoice.

By dividing the reference data into partitions appropriate to the organizational entities that will use the data, setIDs enable you to share control table information and processing options among business units. The goal is to minimize redundant data and system maintenance tasks. For example, you can define a group of common job codes that are shared between several business units. Each business unit that shares the job codes is assigned the same setID for that record group.

SetIDs can be thought of as a striping technology to partition referenced data. All shared reference tables can be striped with a setID column to enable partitions (or sets). This does not require you to change the tables' primary keys.

With partitioning, a customer can choose to have reference data sets specific to each organizational unit mapped one-to-one, or have several different organizational units use the same set of reference data. Customers, rather than development, will have the choice in determining what level of sharing or exclusivity they would like to maintain in the reference data.

A setID is the means by which applications can filter reference data into subsets when they are referenced by different transactional entities. The filtering is driven, indirectly, by contextual values available in the referring transactional entity.

8.1.2 SetID Determinant Types

Use of the shared data partitions is facilitated by a context setting called the determinant, which is usually a column on the referring transactional entity. The purpose of the determinant is to identify an organizational subset; you use it to specify which reference data is valid for use in a given business context. The determinant is the value of a transactional column that is one of several designated determinant types. If at least one column of the transactional table is a setID determinant type, data sharing may make sense for the transaction.

For example, different business units may use the same office supply vendor, but have different requirements for which supplies can be purchased. The determinant type and value provide part of the criteria for selecting the appropriate office supply reference data set.

In addition to the presence of a determinant on the transactional entity, the data that you want to reference must be set-enabled as described later in this documentation.

The setID determinant type can be one of the following existing fields:

  • Asset Book — A book that contains assets belonging to a business unit or ledger. It holds information about the asset's acquisition, depreciation, and retirement. An asset may be assigned to one or more books; for example, the corporate, tax and budget books.

  • Business Unit — This roughly corresponds to a department or organization. For example, Virgin might have an airline, a store, and a recording label as different business units.

  • Cost Organization — A cost organization groups inventory organizations within a legal entity to achieve the following:

    • Establish the cost accounting policies for the inventory organizations.

    • Support cost accounting reporting.

    • Allow the definition of defaults.

    • Allow multiple inventory organizations to share cost calculation.

    • Restrict role access to costing data.

    Cost organization will likely map into a company's enterprise structure as a cost department.

  • Project Unit — A logical organization within a company created to ensure and enforce consistent project management practices.

  • Reference Data Set — For cases where shared reference data has references to other shared reference entities.

Some Criteria for Selecting a Determinant Type

To help decide what determinant type to use for a given application, consider the following:

  • If you cannot change the reference data for different parts of a deploying enterprise, the reference data is global and partitioning is not required.

    Examples of data suitable for partitioning include (but are not limited to) units of measure, currency codes, country codes, or anything else governed by a standard.

  • If the values for the reference data will be decided by the general manager, the best reference data set determinant is likely to be the Business Unit.

For more information about setID determinant types, see Section 8.2.3, "How to Annotate Transactional Entity Objects for SetID".

8.1.3 Understanding SetID Machinery

SetID Machinery is the collection of Applications Core Technology software elements that act in concert to facilitate the use of setIDs to partition, access and maintain reference data. At a high level, the machinery is comprised of:

  • SetID configuration tables

  • SetID metadata for business objects and extensions to ADF Business Components middleware

  • SetID design-time extensions

  • SetID summary tables

The following sections introduce the elements of setID machinery and the ways in which they can be used to implement data sharing. Partitioning Patterns

There are three setID partitioning patterns. Choose one of these patterns based on your business requirements:

  • Row striping (ROWSTRIPE) — This is the simplest pattern, and the default. In this pattern the SET_ID column is just a striping column, and is not part of the set of unique keys for the table. You can filter as follows:

    WHERE SET_ID = :1
  • Row striping with common rows (COMMON) — This is exactly the same as the row striping pattern, with the addition of a COMMON partition. You filter as follows:

    WHERE SET_ID IN (:1, 0)


    The set with setID of 0 is seeded as the common set. This set will be available for assignment only if you select Row Striping With Common Rows for the reference entity.

  • SetID subscription (SUBSCRIPTION) — The drawback of the first two patterns is that if reference data needs to be in two different partitions (other than the common one), it has to be copied and placed in both sets. To avoid that, a setID subscription table can be introduced and used to list which sets include each row. This will allow the same reference data to be in two different sets without the need to copy the data for each set. You join your reference entity with the setID subscription table and filter as follows:


For more information about partitioning patterns, see Section 8.2.1, "How to Annotate Reference Entity Objects for Sharing". Reference Groups

In addition to tables, other sources of reference data such as lookup types and views can also be shared using setIDs. These are all generically referred to as reference entities. Reference entities are generally considered to be setup data, and they may be implementing business policies and legal rules. Reference entities in your application are grouped into logical units called reference groups, based on the functional area and the partitioning requirements they have in common. For example, all tables and views that define Sales Order Type details might be part of the same reference group.

Figure 8-1 illustrates an example of a Worker Assignment transaction table with two set-enabled references: a reference to Salary Codes with partitions determined by Business Unit, and another reference to Labor Agreement with partitions determined by Cost Organization.

Figure 8-1 Example of a Table with Two SetID Reference Groups

Example of a table with two set ID reference groups Set Configuration Tables

There are five types of set configuration tables:

  • Sets

  • Reference groups

  • SetID assignments

  • Reference entities

Sets Table

The sets table, FND_SETID_SETS, lists all of the sets defined for Oracle Applications, plus any new sets that you define. It includes the columns SET_ID and SET_NAME, which enable you to select the proper SET_CODE.

Sets listed in this table include:

  • The two default seeded sets, COMMON and ENTERPRISE.

  • Default sets that map to existing transaction data, created as an upgrade to Applications Unlimited.

  • New sets created by customers, to implement set-enabled reference entities specific to their organizations.

Reference Groups Table

The reference groups table, FND_SETID_REFERENCE_GROUPS_B, captures the default determinant type for all reference entities in each group. This table uses the primary key of REFERENCE_GROUP_NAME. It also includes the APPLICATION_ID column, which is used for filtering and managing ownership.

The available reference groups defined in the reference groups table will be populated before you start creating entity objects. Reference group definitions are owned by the application that owns the reference entities in that group. Application development teams are ultimately responsible for defining and delivering reference groups.


Only reference entities that might be referenced as setID targets need to be captured here; this is not intended to be an exhaustive inventory of all tables in the applications. For more information about reference groups, see Section, "Reference Groups".

SetID Assignments Table

A transactional entity may have multiple sets of reference data that are treated in the same manner. For this reason, reference data sets are assigned to a reference group, then the setID assignment is configured for each determinant value, determinant type, and reference group.

The setID assignments table, FND_SETID_ASSIGNMENTS, records which set to use in every reference table for every determinant value. It is a SQL-joinable entity that can be used to convert available context information into a setIDentifier suitable for filtering rows from referenced entities. The context information serves as the table's primary keys:




Based on these keys, you can determine a setID.


Although development may seed this table with default values, it will be accessed by customers to implement set-enabled reference entities specific to their organizations.

Reference Entities Table

The reference entities table, FND_SETID_REFERENCE_ENTITIES, contains the list of all setID enabled non-lookup reference entities. The SET_ID_PATTERN column indicates which setID pattern is being used by each reference entity. If the value of this field is SUBSCRIBE (setID subscription), the column SET_ID_CHILD_TABLE will be populated with the setID subscription table name.


For customers, this table is read-only. SetID PL/SQL Utilities

The setID PL/SQL utilities are APIs that include the following packages:

Fnd_setid_sets_pkg package

This package contains table handlers for fnd_setid_sets table.

Fnd_setid_assignments package

This package contains table handlers for fnd_setid_assignments table.

Fnd_setid_reference_groups package

This package contains table handlers for fnd_setid_reference_groups table.

Fnd_setid_ref_entities_pkg package

This package contains table handlers for fnd_setid_reference_entities table.

Fnd_setid_set_groups package

This package contains table handlers for fnd_setid_set_groups and fnd_setid_set_group_members tables.

Fnd_setid_utility package

This package contains the following utilities:

  • isValid

     * Returns true if the given parameters are valid, false if not.
     * @param referenceGroupName The reference group name
     * @param setIdDeterminantType The determinant type which could be:
     *                                  BU, RR, LE...
     * @param setIdDeterminantValue The determinant value.
     * @param setId The setid value.
     * @return true or false.
    function isValid(X_REFERENCE_GROUP_NAME in varchar2,
                     X_DETERMINANT_TYPE in varchar2,
                     X_DETERMINANT_VALUE in varchar2,
                     X_SET_ID in number) return boolean;
  • getSetId

     * Returns the set ID corresponding to the specified determinant value, type,
     * and reference group name. This method implements an LRU cache to speed
     * up the lookup.
     * @param setIdDeterminantValue The determinant value
     * @param setIdDeterminantType The determinant type which could be: 
     *                                  BU, RR, LE...
     * @param referenceGroupName the reference group name.
     * @return the corresponding set ID value.
    function getSetId(X_REFERENCE_GROUP_NAME in varchar2,
                      X_DETERMINANT_TYPE in varchar2,
                      X_DETERMINANT_VALUE in varchar2) return number;
  • getReferenceGroupName

     * Returns the reference group name based on a given reference entity name.
     * @param referenceEntityName The name of the table to obtain 
     *      the reference group for
     * @return the corresponding reference group name
    function getReferenceGroupName(X_REFERENCE_ENTITY_NAME in varchar2) return varchar2;
  • isValidSet

     * Returns true if the set ID exists in the FND_SETID_SETS table, 
     * false if not
     * @param setId The setId value
     * @return Boolean
    function isValidSet(X_SET_ID in number) return Boolean;

8.2 Implementing SetID on Entity Objects

You define the following information to implement shared (that is, set-enabled) reference entities:

Before you begin:

Following are the activities that you should complete before you engage in set-enabling references or lookups:

8.2.1 How to Annotate Reference Entity Objects for Sharing


These annotations are required only for set-enabled non-lookup reference entities. Lookup references and set-enabled lookup references use a predefined lookups pattern; the reference group name is retrieved directly from database by the lookup code and the view application ID that are set on the foreign key reference of the transactional reference.

After building an entity object for a shared reference entity, you annotate the entity object.

To annotate the reference entity object:

  1. Double-click the entity object to access its properties, as shown in Figure 8-2.

    Figure 8-2 Entity Object SetID Properties

    Entity object set ID properties
  2. In the Applications section of the Property Inspector, specify the name of the setID reference group to which the entity object belongs.

    For more information, see Section, "Reference Groups".

  3. Specify the setID pattern that the reference entity should use. There are three setID partitioning patterns. Choose one of these patterns based on your business requirements:

    • Row Striping (this is the default value)

    • Striping With Common Rows

    • Subscription

      When you select the SUBSCRIPTION pattern, the SetID Reference Table Pattern field appears. Specify the subscription table to use.


      The primary key columns of the setID subscription table must be named exactly the same as those in the reference entity, and the setID column must be named SET_ID.

    For more information about these options, see Section, "Partitioning Patterns".

  4. In the entity object attributes, ensure that the entity object setID attribute that corresponds to the SET_ID database column is named SetId, as shown in Figure 8-3.

    Figure 8-3 Entity Object SET_ID Attribute

    Entity object SET_ID attribute


    If you cannot use the SET_ID column as your setID attribute, you must ensure that the attribute you use is named SetId, even if the database column is named differently. This applies to both entity objects and view objects.

8.2.2 How to Build Entity Associations for All Foreign References

After building an entity object for a transactional entity, you must create entity associations for all foreign references, including FND lookups. Because SET_ID must not be part of the primary key for any shared reference table (except FND_LOOKUP_VALUES), there is nothing unusual about associations for shared references.

Follow these guidelines when creating the associations:

  • Make sure a destination accessor is generated for the association. ADF Business Components will not honor the association if you do not generate a destination accessor.

  • Because these reference entities are non-composite, you should not generate source accessors.

8.2.3 How to Annotate Transactional Entity Objects for SetID

After you create entity associations for foreign references, you annotate the transactional entity object.

To annotate the transactional entity object:

  1. Double-click the entity object to access its properties, as shown in Figure 8-4.

    Figure 8-4 Business Unit as the SetID Determinant Type

    Business unit as the set ID determinant type
  2. In the Applications section of the Property Inspector, designate which attributes are setID determinants for the table. For more information, see Section 8.1.2, "SetID Determinant Types".

    For every attribute that you want to use as a setID determinant, specify the corresponding determinant type.

    To access setID determinant types programatically, use the following codes:

    Table 8-1 SetID Determinant Type Codes

    Code Determinant Type


    Asset Book


    Business Unit


    Cost Organization


    Project Unit


    Reference Data Set


    • The attribute you need to use as a determinant might not exist on the parent record where its value can be retrieved from some context. In that case, you must create a transient attribute to represent the determinant, set the SetId Determinant Type property for it, then override the getter method of the transient attribute to get the value from wherever it has been stored.

    • Once a SetId Determinant code is defined, you can change the code in the EO.xml file.

  3. For foreign keys that are setID enabled, specify the determinant attribute that drives each foreign key reference, as shown in Figure 8-5.

    The default value of the setID determinant attribute is the default determinant type of the reference entity group targeted by the association that is defined for the foreign key.


    If the determinant value is not directly available on the transaction table, you must create a transient attribute to model it, and ensure that the attribute is correctly populated.

    Figure 8-5 SetID Determinant Attribute for a Foreign Key

    Set ID determinant attribute for a foreign key


    If the reference data has a composite key, you must specify the SetId Determinant Attribute property for the first attribute of the composite key.

8.2.4 How to Define View Accessors for Shared Reference Entities

Create a view accessor from the transaction entity to the reference entity.

8.2.5 How to Define a Key Exists Validator for Shared Reference Entities

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.


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.

To define a key exists validator:

  1. Open the entity object for editing.

  2. On the Attributes tab, select the foreign key attribute and add a validation rule. The Edit Validation Rule page appears, as shown in Figure 8-6.

  3. At the top of the page, select a rule type of Key Exists.

    Figure 8-6 Foreign Reference Validation Rule Definition

    Foreign reference validation rule definition
  4. On the Rule Definition tab, select ViewAccessor as the validation target type.

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

  6. Click Add to include the attribute pair on the mapping list.

  7. Select the Validation Execution tab, as shown in Figure 8-7.

    Because the validation should be executed every time the determinant value changes, it should be specified as a triggering attribute.

    Figure 8-7 Foreign Reference Validation Triggering Attributes

    Foreign reference validation triggering attributes


    The foreign key attributes that were mapped on the Rule Definition tab are by default added as triggering attributes.

  8. In the Triggering Attributes section, select the determinant attribute from the left-hand list and shuttle it to the right-hand list.

  9. Optionally, on the Failure Handling tab, specify a failure error message.

  10. Click OK to create the key exists validator.

To create a transient setID attribute:

  1. Create a new transient setID attribute to map to the SetId attribute on the reference entity, as shown in Figure 8-8.

    Figure 8-8 New Transient SetID Entity Attribute

    New transient set ID entity attribute
  2. Set the Type to Long.

  3. Deselect the Persistent checkbox.

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

  5. On the Java tab, generate or edit a Java class for the transaction entity object.

  6. Because the setID value is computed at runtime based on the values of the reference group name, determinant type and determinant value, you must modify the transaction entity object's Java code to return the setID value at runtime.

    Open the transactional EOImpl class and edit the getter method of your transient setID attribute to pass in the corresponding foreign key attribute name. For example:

    /** This method gets the attribute value for TransientSetIdAttr, using the alias name TransientSetIdAttr.
    public Long getTransientSetIdAttr() {
        return this.getSetId("SalaryCode");
        //"SalaryCode" is the foreign key attribute name on the WorkerAssignments transactional entity

    In this example, you open the class and edit getTransientSetIdAttr() to pass in the attribute name "SalaryCode" so its value will be returned at runtime.

8.2.6 How to Create LOVs for Shared Reference Entities

ADF Business Components supports defining LOVs at the attribute level in view objects.

To build a lookups LOV for a set-enabled reference entity:

  1. Create an LOV on the foreign key attribute in the attribute wizard.

    The default LOV name is typically kept as LOV_attribute_name, as shown in Figure 8-9.

    Figure 8-9 LOV Definition for a Set-Enabled Reference Entity

    LOV definition for a set-enabled reference entity
  2. Choose a view accessor from the list of available view accessors.

    Typically you will choose the same view accessor which was defined for the underlying entity object and used for the Key Exists validator.

  3. Select an attribute from the view accessor to validate against, and ADF Business Components will automatically add that attribute to the list of return values.

  4. Optionally, you can specify additional attributes to be returned to the master row when an LOV entry is selected.

  5. Optionally, you can customize the LOVs UI hints by clicking Edit List UI Hints to access the List UI Hints dialog.

8.3 Integrating SetID Task Flows into Oracle Fusion Functional Setup Manager

Every 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 is an application task flow for managing reference data sets, and one for managing reference data set assignments. 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 8-2.

Table 8-2 SetID Task Flows and Parameters

Task Flow Name Task Flow XML Parameters Passed Behavior

Manage Reference Data Sets


To optionally specify a page heading for the task flow:


This task flow enables you to create and update reference data sets (setIDs and codes).

Manage Reference Data Set Assignments


To invoke the task flow:


To optionally restrict the page to assignments for a single reference group:


To optionally specify a page heading for the task flow:


This task flow enables you to manage reference data set assignments for a particular determinant type.

For more information about task flows, see the Oracle Fusion Applications Common Implementation Guide.