Search Results ece_external_levels_upg




Overview
Explore in AI Chat

Overview

ECE_EXTERNAL_LEVELS_UPG is a table owned by the EC schema within the Oracle e-Commerce Gateway module. It stores the definition of external transaction levels used by the gateway's inbound and outbound processing engine, together with the mapping rules that translate those levels into Oracle EBS internal structure. In the context of Oracle EBS 12.1.1 and 12.2.2, it functions as a configuration and setup repository rather than a transient or transactional table: rows persist until the corresponding level definition is changed or removed.

The heuristic Data Vault classification mined from its foreign-key structure is standalone. From a modeling perspective, this suggests the object behaves as a reference or configuration entity with no documented parent link in the ETRM relationship data. The EXTERNAL_LEVEL_ID column is the single-column surrogate primary key, enforced through the constraint identified as OCO_18586770.

Key Information Stored

The table documents thirteen physical columns. The most operationally significant are:

  • EXTERNAL_LEVEL_ID — surrogate primary key; the unique identifier for each external level record.
  • EXTERNAL_LEVEL — the name or code of the external transaction level as presented to the trading partner.
  • PARENT_LEVEL — the parent level in the hierarchy, enabling nested level structures to be defined.
  • MAP_ID — the mapping identifier that links the external level to its corresponding internal mapping definition.
  • TRANSACTION_TYPE — the e-Commerce Gateway transaction type (for example an inbound or outbound document class) to which the level applies.
  • START_ELEMENT — the starting element or record position that marks the beginning of the level within the transaction stream.
  • ENABLED_FLAG — indicates whether the level definition is active and eligible for gateway processing.
  • DESCRIPTION — free-text explanation of the level's purpose.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS audit columns capturing record creation and modification history.

EXTERNAL_LEVEL_ID serves as the surrogate key; EXTERNAL_LEVEL combined with TRANSACTION_TYPE and PARENT_LEVEL is the most plausible business-key candidate, although no unique index beyond the primary key is documented in the ETRM metadata.

Common Use Cases and Queries

Typical uses include reviewing which levels are active for a given transaction type, tracing hierarchical relationships between levels, and diagnosing mapping failures where inbound data does not resolve to a configured level.

  • Listing enabled levels for a transaction type:
    SELECT EXTERNAL_LEVEL_ID, EXTERNAL_LEVEL, PARENT_LEVEL FROM ECE_EXTERNAL_LEVELS_UPG WHERE TRANSACTION_TYPE = :type AND ENABLED_FLAG = 'Y';
  • Reconstructing the hierarchy:
    SELECT child.EXTERNAL_LEVEL, parent.EXTERNAL_LEVEL AS PARENT FROM ECE_EXTERNAL_LEVELS_UPG child, ECE_EXTERNAL_LEVELS_UPG parent WHERE child.PARENT_LEVEL = parent.EXTERNAL_LEVEL;
  • Auditing recent configuration changes using LAST_UPDATE_DATE and LAST_UPDATED_BY.
  • Cross-checking that every active level has a valid MAP_ID before running conversion or gateway programs.

Because the table is a setup object, queries are predominantly read-only reporting and validation patterns rather than high-volume transactions.

Related Objects

The ETRM metadata classifies this table as standalone, meaning no foreign keys are mined to other objects. The most relevant related objects are those joined through shared business columns:

  • Mapping definition tables referenced by MAP_ID — the mapping tables holding the internal side of the level translation.
  • Transaction type reference tables keyed by TRANSACTION_TYPE — used to validate the level's owning transaction class.
  • e-Commerce Gateway interface and staging tables that consume the level definitions during inbound processing and outbound extraction.
  • Self-referencing hierarchy joins through PARENT_LEVEL when displaying or validating level nesting.
  • Standard EBS audit and concurrent request tables that trace changes made by gateway setup or upgrade programs.

Given the limited documented relationships, dependency analysis should be confirmed against the actual 12.1.1 or 12.2.2 data dictionary in the target environment rather than relying on FK metadata alone.