Search Results ece_tran_stage_data




Overview
Explore in AI Chat

Overview

EC.ECE_TRAN_STAGE_DATA is a configuration and seed-data table within the Oracle E-Business Suite e-Commerce Gateway (EC) module. As documented in the ETRM metadata for releases 12.1.1 and 12.2.2, the table "contains the seed data for the dynamic execution of the inbound/outbound transactions." In practical terms, it is the metadata-driven rule engine that instructs the e-Commerce Gateway how to transform, stage, and map transaction data during translation between flat-file/EDI structures and Oracle application interface tables. It is not a transactional staging area for business documents themselves; rather, it holds the declarative instructions (variables, sequence names, procedures, and WHERE clauses) that the Gateway executes at runtime.

The documented physical schema in ETRM 12.2.2 records the table under the EC schema with 19 columns and a primary key constraint named ECE_TRAN_STAGE_DATA_PK on the surrogate column TRANSTAGE_ID. The relationship classification supplied in the metadata is "standalone," which in Data Vault terms is a heuristic modeling suggestion: this object behaves most like a satellite-style reference/configuration table rather than a true hub or link, since it carries descriptive attributes keyed by a surrogate identifier and no documented foreign-key dependencies to other EC tables. Business-key uniqueness would typically be governed by the natural combination of transaction type, level, and stage, though the metadata documents only the surrogate primary key constraint.

Key Information Stored

The most operationally significant columns, drawn from the documented 19-column schema, fall into four logical groups:

  • Transaction identification: TRANSACTION_TYPE and TRANSACTION_LEVEL identify which inbound or outbound document (for example an outbound invoice or inbound purchase order) and which processing level the row governs. STAGE and SEQ_NUMBER establish the ordered processing step this record belongs to.
  • Variable and default logic: VARIABLE_LEVEL, VARIABLE_NAME, VARIABLE_VALUE, and DEFAULT_VALUE define the runtime variable to be populated and its fallback when no explicit value is supplied. PREVIOUS_VARIABLE_LEVEL and PREVIOUS_VARIABLE_NAME maintain the chaining context between successive variables.
  • Execution instructions: ACTION_TYPE, CUSTOM_PROCEDURE_NAME, FUNCTION_NAME, SEQUENCE_NAME, DATA_TYPE, NEXT_VARIABLE_NAME, and WHERE_CLAUSE drive the dynamic execution — which procedure or function to invoke, the data type involved, the sequence to draw from, the SQL restriction predicate, and the next variable in the chain.
  • Keys: TRANSTAGE_ID is the documented surrogate primary key (ECE_TRAN_STAGE_DATA_PK). No unique business-key index is documented in the metadata, but the natural composite of TRANSACTION_TYPE, TRANSACTION_LEVEL, STAGE, and SEQ_NUMBER is the de facto business identifier. MAP_ID associates the row with its corresponding mapping definition.

Common Use Cases and Queries

Typical usage centres on diagnosing why a specific e-Commerce Gateway translation fails, auditing configured variable logic for a transaction, or migrating configuration between instances. A representative diagnostic query retrieves all execution steps for one transaction and stage:

  • SELECT SEQ_NUMBER, VARIABLE_NAME, VARIABLE_VALUE, DEFAULT_VALUE, ACTION_TYPE, CUSTOM_PROCEDURE_NAME, FUNCTION_NAME, WHERE_CLAUSE FROM EC.ECE_TRAN_STAGE_DATA WHERE TRANSACTION_TYPE = :type AND TRANSACTION_LEVEL = :level AND STAGE = :stage ORDER BY SEQ_NUMBER;
  • Compare seed data between two environments or after a patch by selecting the full row set ordered by MAP_ID and SEQ_NUMBER.
  • Identify customised rows by filtering where CUSTOM_PROCEDURE_NAME or FUNCTION_NAME is populated.
  • Audit variable chaining by tracing PREVIOUS_VARIABLE_NAME to VARIABLE_NAME to NEXT_VARIABLE_NAME.

Related Objects

The metadata classifies the table as standalone, so relationships are logical rather than enforced by documented foreign keys. The most significant related objects are:

  • EC.ECE_MAP_ID / mapping definitions — joined via the documented MAP_ID column, linking stage instructions to their parent mapping.
  • EC.ECE_TRAN_STAGE — the companion staging definition keyed by transaction type, level, and stage.
  • EC.ECE_TP_* trading-partner tables — supply the partner-specific context for outbound and inbound runs.
  • Gateway concurrent programs and the e-Commerce Gateway translation engine — consume the CUSTOM_PROCEDURE_NAME and FUNCTION_NAME entries at runtime.
  • Interface staging tables (for example order and invoice open interface tables) — the eventual targets that the executed staging logic populates.

Because the table carries Oracle proprietary seed data, direct modifications in a production instance should be avoided; changes are normally applied through patching or the supported e-Commerce Gateway configuration interfaces. The user's search term, "BTP-ECC-Infrastructure Setup-V1.xlsx," suggests this object was reviewed as part of an e-Commerce Gateway infrastructure or transport setup workbook, where ECE_TRAN_STAGE_DATA would be profiled as foundational configuration that must be preserved consistently across environments.