Search Results ecx_lookup_values




Overview
Explore in AI Chat

Overview

ECX_LOOKUP_VALUES is a reporting and integration view owned by the APPS schema within the ECX (XML Gateway) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, the view "Stores XML Lookup Values" and is designed to expose the set of lookup codes that define behavior for the XML Gateway, the EBS component responsible for exchanging business documents (orders, invoices, receipts, and similar transactions) with external trading partners.

Physically, ECX_LOOKUP_VALUES holds no data of its own. It is a filtered projection of the generic Oracle EBS lookups table, FND_LOOKUP_VALUES, restricted to the XML Gateway application context. Because it is a view, its status is documented as VALID and it inherits its content dynamically each time it is queried. For report developers and integration architects, the view provides a stable, pre-filtered window onto the lookup values that govern XML Gateway configuration, removing the need to reconstruct the same filter conditions manually.

Underlying Base Objects

The ETRM metadata documents two referenced base objects: the synonym FND_LOOKUP_VALUES and the package FND_GLOBAL. The view text shows how both are used:

The WHERE clause applies five documented restrictions. LANGUAGE = USERENV('LANG') limits rows to the session's language. VIEW_APPLICATION_ID = 174 identifies the XML Gateway application. SECURITY_GROUP_ID must equal the value returned by FND_GLOBAL.LOOKUP_SECURITY_GROUP. ENABLED_FLAG is fixed to 'Y', so only active lookups are exposed. The underlying VIEW_APPLICATION_ID column itself is not projected out of the view, which is why it does not appear in the listed columns.

Key Columns

  • LOOKUP_TYPE — the lookup category, such as a message type or document direction, that groups related codes.
  • LOOKUP_CODE — the individual value within a lookup type; the code actually stored on transaction records.
  • MEANING — the user-facing, translatable label for the code.
  • DESCRIPTION — an optional longer explanation of the code's purpose.
  • ENABLED_FLAG — the enablement indicator. Because the view already filters on ENABLED_FLAG = 'Y', every row returned will always show 'Y'; querying this column is therefore redundant for filtering.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range during which the lookup is valid.

Common Use Cases and Queries

Typical scenarios include reporting on configured XML Gateway lookup values, validating lookup codes before populating an interface table, and driving dynamic behavior in custom integrations. A common point of confusion, given the search term "enabled_flag," is that filtering by this column returns all rows, since the view is already enabled-only.

  • List all enabled XML Gateway lookups for a specific type:
    SELECT lookup_type, lookup_code, meaning, description FROM apps.ecx_lookup_values WHERE lookup_type = :p_type ORDER BY lookup_code;
  • Find the description of a single code:
    SELECT meaning, description, start_date_active, end_date_active FROM apps.ecx_lookup_values WHERE lookup_type = :p_type AND lookup_code = :p_code;
  • Avoid the redundant filter:
    SELECT * FROM apps.ecx_lookup_values WHERE enabled_flag = 'Y'; — returns the same rows as an unfiltered query.