Results for “alr_lookups”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ALR_LOOKUPS is the Oracle Alert (ALR) module lookup table that stores the lookup types and lookup codes used throughout the Alert application. In Oracle EBS 12.1.1 and 12.2.2, the ALR schema maintains this table as a standalone reference object that governs the valid values available to Alert setup screens and concurrent programs. Because it is an Alert-specific lookup table rather than the global FND_LOOKUPS table, its contents are limited to values required by Alert functionality, such as alert processing and input parameter validation.

Functionally, ALR_LOOKUPS follows the standard Oracle Application Object Library lookup design: each row pairs a lookup type with a lookup code, and the row carries a meaning, description, enabled flag, and effective dating columns. The ETRM metadata classifies this table heuristically as standalone under the Data Vault model. That classification suggests the table is best modeled as a reference or hub-like object rather than as a link or satellite, since its structure is a self-contained set of controlled vocabulary values that other Alert objects may consume but do not structurally depend upon.

Key Information Stored

The primary key of ALR_LOOKUPS is ALR_LOOKUPS_PK, defined on the composite of LOOKUP_TYPE and LOOKUP_CODE. In Oracle EBS terminology, these two columns are the true business-key candidates, since together they uniquely identify each lookup row in the operational table. The documented unique index ALR_LOOKUPS_U1 extends that business key to include ZD_EDITION_NAME (LOOKUP_TYPE, LOOKUP_CODE, ZD_EDITION_NAME), which supports the edition-based redefinition strategy used in Oracle EBS 12.2.2.

• LOOKUP_TYPE — the category or group of lookup values (for example, an alert-specific enumeration); part of the primary key.
• LOOKUP_CODE — the individual code within the lookup type; part of the primary key.
• MEANING — the user-facing display value associated with the lookup code.
• DESCRIPTION — a longer free-text explanation of the lookup code's purpose.
• ENABLED_FLAG — indicates whether the lookup code is active and selectable in Alert setup.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective dating range during which the lookup code is valid.
• SECURITY_GROUP_ID — the security group owning the row; the metadata documents a foreign key from ALR_LOOKUPS.SECURITY_GROUP_ID to FND_SECURITY_GROUPS.
• ZD_EDITION_NAME — the edition identifier used by Edition-Based Redefinition, which is part of the unique index in 12.2.2.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN track who created and last modified each lookup row.

Common Use Cases and Queries

The most frequent interaction with ALR_LOOKUPS is validation and reporting: confirming which lookup codes are enabled for a given lookup type, and joining these values to Alert setup tables to resolve codes to their display meanings. A typical pattern filters on active dates and the enabled flag so that only currently valid values are returned.

A representative query retrieves all enabled codes for a lookup type:

SELECT lookup_code, meaning, description
FROM   alr.alr_lookups
WHERE  lookup_type = :p_lookup_type
AND    enabled_flag = 'Y'
AND    TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
                          AND NVL(end_date_active, SYSDATE)
ORDER BY meaning;

Reporting scenarios include auditing lookup values across editions (using ZD_EDITION_NAME), reconciling the Alert-specific lookup set against FND_LOOKUPS for consistency, and validating security-group membership through the join to FND_SECURITY_GROUPS. Because the table is read-mostly reference data, custom code should treat it as a query target rather than an extension point; custom lookup types should generally be added through supported Alert or Application Object Library setup rather than direct DML.

Related Objects

The ETRM metadata documents a single foreign-key relationship from ALR_LOOKUPS, but in practice the table participates in several reference-data and Alert setup relationships.

• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID to resolve the owning security group.
• FND_LOOKUPS — the global AOL lookup table; ALR_LOOKUPS is the Alert-local counterpart, and the two are often compared or synchronized.
• ALR_ALERTS — the core Alert definition table, whose setup screens consume Alert lookup values for validation and display.
• ALR_ALERT_INPUTS — Alert input-parameter definitions that may reference Alert lookup codes for accepted values.
• FND_LOOKUP_TYPES / FND_LOOKUP_VALUES — related AOL reference objects that mirror the type/code structure of ALR_LOOKUPS.

Because ALR_LOOKUPS is classified as standalone, it has few inbound structural dependencies; most related objects reference it logically through LOOKUP_TYPE and LOOKUP_CODE rather than through enforced foreign keys.