Search Results fa_lookups_b_pk

Overview

FA_LOOKUPS_B is the base Multi-Lingual Support (MLS) table in the Oracle Assets (OFA) module that stores seeded and user-defined QuickCode values. QuickCodes provide a flexible, extensible mechanism for enumerating valid values used throughout Oracle Assets, such as asset categories, depreciation conventions, prorate conventions, and other lookup-driven attributes. As the base table in the MLS pair, FA_LOOKUPS_B holds the language-independent lookup definitions, while its associated translation table (FA_LOOKUPS_TL) stores the language-specific display text.

The table resides in the FA schema and is owned by the Oracle Assets product. Its primary key, FA_LOOKUPS_B_PK, is defined on the composite of LOOKUP_TYPE and LOOKUP_CODE, ensuring that each lookup code is unique within its lookup type. A single foreign key constrains LOOKUP_TYPE to FA_LOOKUP_TYPES_B, enforcing that all lookup codes belong to a valid, pre-defined lookup type. From a Data Vault modeling perspective, the mined foreign key structure suggests a satellite-leaning classification: the table qualifies and describes values that are parented by the lookup type, rather than acting as an independent hub or as a pure linking construct.

Key Information Stored

The table's 27 documented columns include a mix of keys, descriptive attributes, and audit columns. The most functionally significant are:

• LOOKUP_TYPE — Identifies the QuickCode category to which the value belongs. This is both part of the primary key and the foreign key to FA_LOOKUP_TYPES_B.
• LOOKUP_CODE — The unique code value within the lookup type. Together with LOOKUP_TYPE, it forms the composite primary key FA_LOOKUPS_B_PK.
• ENABLED_FLAG — Controls whether the lookup value is currently active and available for selection in Oracle Assets.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Define the effective date range during which the lookup code is valid.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified each record and when.
• ATTRIBUTE_CATEGORY_CODE and ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield (DFF) columns allowing users to capture additional, implementation-specific metadata against each lookup value.
• ZD_EDITION_NAME — Participates in the unique index FA_LOOKUPS_B_U1 (LOOKUP_TYPE, LOOKUP_CODE, ZD_EDITION_NAME), which serves as a business-key candidate and supports edition-based data management.

The primary key is a composite business key rather than a system-generated surrogate. The unique index FA_LOOKUPS_B_U1 extends the primary key by including the edition name, providing an alternate uniqueness constraint for edition-aware operations.

Common Use Cases and Queries

FA_LOOKUPS_B is frequently queried to enumerate valid values for Oracle Assets setup and reporting. Typical scenarios include validating configuration during implementation, generating lookup value reports for audit, and joining to the translation table to produce user-facing descriptions.

A representative query retrieving enabled lookup codes for a given type:

• SELECT lookup_type, lookup_code, enabled_flag, start_date_active, end_date_active FROM fa.fa_lookups_b WHERE lookup_type = :p_type AND enabled_flag = 'Y';
• Joining to the translation table for display text: SELECT b.lookup_type, b.lookup_code, t.meaning FROM fa.fa_lookups_b b, fa.fa_lookups_tl t WHERE b.lookup_type = t.lookup_type AND b.lookup_code = t.lookup_code AND t.language = USERENV('LANG');
• Identifying expired lookups: SELECT lookup_type, lookup_code FROM fa.fa_lookups_b WHERE end_date_active < SYSDATE AND enabled_flag = 'Y';

Reporting use cases include lookup usage audits, migration validation between environments, and impact analysis before disabling or modifying QuickCode values.

Related Objects

• FA_LOOKUP_TYPES_B — Referenced via the foreign key FA_LOOKUPS_B.LOOKUP_TYPE → FA_LOOKUP_TYPES_B. Defines the parent lookup type for every lookup code.
• FA_LOOKUPS_TL — The MLS translation table storing language-specific meaning and description text; joined on LOOKUP_TYPE and LOOKUP_CODE.
• FA_LOOKUP_TYPES_TL — Translation table for lookup types, used alongside the base type table when building complete QuickCode hierarchies.
• FA_LOOKUPS_B_PK — The primary key constraint enforcing uniqueness of (LOOKUP_TYPE, LOOKUP_CODE).
• FA_LOOKUPS_B_U1 — Unique index on (LOOKUP_TYPE, LOOKUP_CODE, ZD_EDITION_NAME) serving as an edition-aware business-key candidate.

These objects collectively form the QuickCode framework within Oracle Assets, enabling both seeded and customer-extended lookup values to be maintained consistently across the application.