Search Results amw_lookups




Overview
Explore in AI Chat

Overview

AMW_LOOKUPS is a database view in Oracle E-Business Suite associated with the product AMW – Internal Controls Manager, a module now classified as obsolete. The view presents a filtered selection of lookup values drawn from the FND_LOOKUP_VALUES table, restricted specifically to those lookup codes that belong to the ICM product. In the Oracle EBS reporting and integration model, lookup views of this kind serve as a controlled projection layer over the shared FND_LOOKUP_VALUES repository, allowing module-specific code values to be consumed without exposing the full global lookup set. The view returns only rows whose VIEW_APPLICATION_ID matches the literal value '242', which corresponds to the AMW application. It is documented as not implemented in the reference database, and no owner or referenced base objects are documented in the ETRM metadata for version 12.2.2. Despite its obsolete status, the view remains relevant for understanding legacy ICM customizations and for interpreting historical reports that referenced AMW lookup codes in both 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view is defined over a single documented base object: FND_LOOKUP_VALUES, the standard Oracle Applications table that stores all lookup codes, meanings, descriptions, tags, and effective dating attributes across the E-Business Suite. The definition applies three filters to that table. First, it restricts rows by LANGUAGE using the USERENV('LANG') session value, returning only the lookup values in the language of the current user session. Second, it restricts rows by VIEW_APPLICATION_ID = '242', isolating lookups owned by the ICM product. Third, it applies a SECURITY_GROUP_ID predicate using SUBSTRB parsing of the USERENV('CLIENT_INFO') session string, decoding the client information to obtain the active security group identifier. Because the view references FND_LOOKUP_VALUES, it inherits that table's structure and its dependence on the shared lookup maintenance framework.

Key Columns

The view exposes eight columns. LOOKUP_TYPE identifies the lookup category to which a code belongs. LOOKUP_CODE is the internal value stored in transactional and setup data. MEANING is the user-facing display value for the code. DESCRIPTION provides optional additional context. ENABLED_FLAG indicates whether the lookup value is currently active. START_DATE_ACTIVE and END_DATE_ACTIVE define the effective date range of the value, supporting time-bound lookup usage. TAG is a free-form attribute available for module-specific tagging of lookup entries. Together these columns mirror the relevant subset of FND_LOOKUP_VALUES needed for display and validation logic within the ICM product.

Common Use Cases and Queries

Typical usage includes reporting on active ICM lookup codes, joining lookup meanings to ICM transaction data, and validating whether a lookup code was effective as of a given date. A representative query lists the enabled, currently effective ICM lookups for a specific type:

  • SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION FROM AMW_LOOKUPS WHERE LOOKUP_TYPE = :type AND ENABLED_FLAG = 'Y' AND TRUNC(SYSDATE) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE);
  • SELECT LOOKUP_CODE, MEANING FROM AMW_LOOKUPS WHERE LOOKUP_TYPE = 'AMW_SOME_TYPE' ORDER BY LOOKUP_CODE;

Because the view is documented as not implemented and the AMW module is obsolete, these queries are principally of historical and migration interest. Where the view is unavailable in a given instance, equivalent results can be obtained directly from FND_LOOKUP_VALUES by filtering on VIEW_APPLICATION_ID = '242' and the appropriate language and security group.