Search Results instance_pk1_value

Overview

FND_LOOKUP_ASSIGNMENTS is a table owned by the APPLSYS schema in the FND — Application Object Library product of Oracle E-Business Suite 12.1.1 and 12.2.2. As its ETRM description states, the table is used for categorization purposes and functions as an intersection table between FND_LOOKUP_VALUES and any product-team table. In effect, it allows an application or product team to attach a specific lookup value to a specific instance of an entity, tying the flexible lookup framework to concrete business records.

Because the table is structurally standalone — the ETRM data-vault classification heuristic reports no foreign-key relationships to other tables — it does not participate in a classic parent/child dependency chain. Under a data-vault modeling suggestion, it would be treated as a standalone construct rather than a hub, link, or satellite, since no FK-derived relationships were mined. In practice, it behaves as a lightweight association (link) table whose meaning is carried entirely in its business-key columns, which is why the surrogate key and the multi-part unique index coexist.

Key Information Stored

The table contains 16 documented columns. The most significant are:

• LOOKUP_ASSIGNMENT_ID — the surrogate primary key, enforced by FND_LOOKUP_ASSIGNMENTS_PK and also present in the unique index FND_LOOKUP_ASSIGNMENTS_U1 (paired with ZD_EDITION_NAME).
• LOOKUP_TYPE — identifies the lookup type being assigned; the first component of the business-key unique index FND_LOOKUP_ASSIGNMENTS_U2.
• LOOKUP_CODE — identifies the specific lookup value within that type.
• OBJ_NAME — the object or product-team entity name to which the lookup is being assigned.
• INSTANCE_PK1_VALUE through INSTANCE_PK5_VALUE — five columns holding the primary-key components of the target entity instance, allowing the assignment to point at a specific row in the associated product-team table.
• DISPLAY_SEQUENCE — controls ordering of assigned lookups during display.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns providing audit and concurrency information.
• ZD_EDITION_NAME — the editioning column used in 12.2.2 for online patching and edition-based redefinition.

The distinction between the surrogate key (LOOKUP_ASSIGNMENT_ID) and the business-key candidates is important: U2 combines LOOKUP_TYPE, LOOKUP_CODE, OBJ_NAME, the five INSTANCE_PK values, and ZD_EDITION_NAME, which together define the logical uniqueness of a single assignment.

Common Use Cases and Queries

Typical usage revolves around retrieving the lookup values assigned to a given business object or instance. A representative query joining the assignment to the underlying lookup values is:

SELECT a.LOOKUP_TYPE, a.LOOKUP_CODE, a.OBJ_NAME, a.DISPLAY_SEQUENCE
FROM   APPLSYS.FND_LOOKUP_ASSIGNMENTS a
WHERE  a.OBJ_NAME = :obj_name
AND    a.INSTANCE_PK1_VALUE = :pk1
ORDER BY a.DISPLAY_SEQUENCE;

Reporting scenarios include auditing which lookup categories are attached to each entity instance, validating that no lookup is assigned twice to the same object, and extracting the ordered list of assigned codes for display in a form or concurrent program. Because INSTANCE_PK1–5 are generic, applications frequently constrain the query by OBJ_NAME and the relevant INSTANCE_PK columns matching their own primary key. Edition-aware queries in 12.2.2 should include ZD_EDITION_NAME when running against editioned data.

Related Objects

The table is documented as an intersection between lookup values and product-team tables, so the following objects are the most significant counterparts:

• FND_LOOKUP_VALUES — joined on LOOKUP_TYPE and LOOKUP_CODE; supplies the value, meaning, and enabled status for each assignment.
• FND_LOOKUP_TYPES — joined on LOOKUP_TYPE; provides the lookup type definition and owning application.
• FND_APPLICATION — identifies the product that owns the lookup type.
• Application-specific instance tables — the product-team table referenced by OBJ_NAME and INSTANCE_PK1–5 (for example, an inventory or order entity table), joined by mapping its primary key to those columns.
• FND_LOOKUP_ASSIGNMENTS_PK / _U1 / _U2 — the primary key and unique indexes that enforce integrity on the table itself.

As the ETRM metadata records no formal foreign keys, these relationships are logical and are driven by the LOOKUP_TYPE, LOOKUP_CODE, OBJ_NAME, and INSTANCE_PK values rather than by database-enforced constraints.