Search Results ece_external_levels_pk

Overview

ECE_EXTERNAL_LEVELS is an Oracle E-Business Suite table owned by the EC schema, which supports the e-Commerce Gateway product. The table stores information about the external levels defined for a given transaction and map. In e-Commerce Gateway processing, transactions such as purchase orders, invoices, and shipping notices are exchanged with trading partners using map definitions that translate between Oracle EBS's internal data model and the flat-file or EDI structure expected by the external partner. External levels represent the hierarchical tiers within those external documents — for example, a header level, a line level, or a detail level — for a specific transaction type and map combination.

Each row ties an external level to a map and a transaction type, and the level can itself have a parent level via the PARENT_LEVEL column, reflecting the nesting structure of the external document. The ETRM metadata classifies this object heuristically as standalone under the Data Vault modeling heuristic, meaning it does not have documented foreign key dependencies to its parent tables. As a modeling suggestion, this could be treated as a reference or setup entity rather than a hub, link, or satellite in a strict Data Vault sense, but if integrated into a Data Vault model it would most naturally be a satellite or reference table describing level configuration.

Key Information Stored

The table contains 13 documented columns in the 12.2.2 physical schema. The most significant are:

• EXTERNAL_LEVEL_ID — the surrogate primary key, defined by the ECE_EXTERNAL_LEVELS_PK constraint. It uniquely identifies each external level record.
• EXTERNAL_LEVEL — the identifier or name of the external level within the transaction document hierarchy.
• PARENT_LEVEL — references the level that contains this level in the document structure, establishing the nesting relationship.
• START_ELEMENT — identifies the starting element within the external document associated with this level, useful for parsing or generating the flat file.
• DESCRIPTION — a descriptive text for the level, aiding administrators in understanding its purpose.
• MAP_ID — identifies the map to which this level belongs, linking the level to a specific mapping definition.
• TRANSACTION_TYPE — the transaction type for which the level is defined, such as a specific EDI document or business transaction.
• ENABLED_FLAG — indicates whether the level is active and available for use in processing.
• CREATED_BY, CREATION_DATE — standard audit columns recording the creating user and creation timestamp.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns recording the most recent update user, date, and login session.

The surrogate primary key EXTERNAL_LEVEL_ID is distinct from any business-key candidate; the metadata does not document a unique index beyond the primary key, but MAP_ID and TRANSACTION_TYPE together with EXTERNAL_LEVEL and PARENT_LEVEL would typically serve as the logical business key defining a level within a map context.

Common Use Cases and Queries

Administrators and technical consultants use ECE_EXTERNAL_LEVELS to inspect and troubleshoot the level structure of e-Commerce Gateway maps. A common query lists all levels for a given map and transaction type, ordered by their hierarchical relationship:

• SELECT external_level_id, external_level, parent_level, description, enabled_flag FROM ece_external_levels WHERE map_id = :map_id AND transaction_type = :transaction_type ORDER BY parent_level, external_level;
• Identifying top-level records where parent_level IS NULL to find the root of the external document hierarchy.
• Joining with map definition tables to report which levels are enabled for a given trading partner's outgoing or incoming document.
• Auditing changes by filtering on last_update_date and last_updated_by to track configuration modifications over time.

Because the object is a setup/configuration table, query volumes are typically low, and reporting is usually operational or diagnostic rather than high-volume transactional reporting.

Related Objects

The ETRM metadata documents ECE_EXTERNAL_LEVELS as standalone, with no foreign keys to other tables. Based on the e-Commerce Gateway data model and the columns present, the following related objects are significant:

• ECE_MAP_DEFINITIONS or the map definition table referenced by MAP_ID, which defines the map the level belongs to.
• The transaction type reference, typically sourced from ECE_TRANSACTION_TYPES or equivalent, joined via TRANSACTION_TYPE.
• ECE_EXTERNAL_ELEMENTS or similar element-level tables that further decompose a level into individual data elements, joined via EXTERNAL_LEVEL_ID.
• ECE_MAPPING_VIEW or mapping view tables that associate external levels with internal columns.
• Standard audit-related views such as FND_USER, referenced via CREATED_BY and LAST_UPDATED_BY to resolve user names.

Because no foreign keys are documented, joins to parent objects should be validated against the actual EC schema configuration in each environment.