Results for “ecx_tran_stage_data”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ECX_TRAN_STAGE_DATA is a core configuration table owned by the ECX schema (XML Gateway product) in Oracle E-Business Suite 12.1.1 and 12.2.2. Despite the "stage data" naming, the documented ETRM description identifies this table as holding transaction action definitions — the rule, mapping, condition, and operand metadata that XML Gateway uses when transforming inbound and outbound business documents. Each row defines an action that is executed against a transaction stage, including variable assignments, conditional expressions, function or procedure calls, and sequencing logic.

The table is physical, VALID, and contains 76 documented columns. Its primary key is ECX_TRAN_STAGE_DATA_PK, defined on TRANSTAGE_ID. A unique index, ECX_TRAN_STAGE_DATA_U1, spans MAP_ID, TRANSTAGE_ID, and ZD_EDITION_NAME, making these the business-key candidates for the table. From a Data Vault modeling perspective, the ETRM metadata heuristic classifies this object as hub-leaning: TRANSTAGE_ID acts as a durable surrogate identifier, while ECX_PROC_MAPPINGS.TRANSTAGE_ID references it as a foreign key, reinforcing its role as a referenced definition anchor rather than a transactional fact.

Key Information Stored

The most operationally significant columns fall into several functional groups:

• Identity and mapping: TRANSTAGE_ID (surrogate primary key), MAP_ID, ZD_EDITION_NAME (together the unique business key), plus SOURCE_LEVEL_ID, OBJECTLEVEL_ID, and OBJECT_DIRECTION.
• Action definition: ACTION_TYPE, ACTION_POS, STAGE, SEQ_NUMBER, SEQUENCE_NAME, and VARIABLE_DIRECTION define how and when the action fires.
• Variable assignment: VARIABLE_LEVEL, VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_POS, DEFAULT_VALUE, and DATA_TYPE describe the target variable and its value semantics.
• Conditional logic: COND_LOGICAL_OPERATOR, COND_OPERATOR1 and COND_OPERATOR2, along with COND_VAR1_* / COND_VAR2_* and COND_VAL1_* / COND_VAL2_* columns, express the predicate that gates the action.
• Operands and invocation: OPERAND1_* through OPERAND6_* (level, name, position, direction, constant) plus OPERAND1_START_POS, OPERAND1_LEN, FUNCTION_NAME, CUSTOM_PROCEDURE_NAME, and WHERE_CLAUSE support function/procedure calls and data extraction.

The surrogate key (TRANSTAGE_ID) should be distinguished from the business key (MAP_ID, TRANSTAGE_ID, ZD_EDITION_NAME); the former provides row stability, the latter enforces uniqueness across editions and maps.

Common Use Cases and Queries

Typical uses include auditing XML Gateway action configuration, diagnosing document transformation failures, and reporting on mapping rule complexity. A diagnostic query retrieves all actions for a given map:

• SELECT TRANSTAGE_ID, ACTION_TYPE, STAGE, SEQ_NUMBER, VARIABLE_NAME, VARIABLE_VALUE FROM ECX.ECX_TRAN_STAGE_DATA WHERE MAP_ID = :map_id ORDER BY SEQ_NUMBER;
• To list conditional actions: filter on COND_OPERATOR1 IS NOT NULL and inspect COND_VAR1_NAME / COND_VAR1_CONSTANT.
• To find custom code references: WHERE CUSTOM_PROCEDURE_NAME IS NOT NULL OR FUNCTION_NAME IS NOT NULL.
• To trace usage by downstream mappings: join to ECX_PROC_MAPPINGS on TRANSTAGE_ID.

These queries are commonly used for impact analysis before applying patches, and for capturing configuration baselines across ZD_EDITION_NAME values.

Related Objects

• ECX_PROC_MAPPINGS — references ECX_TRAN_STAGE_DATA via TRANSTAGE_ID (foreign key column ECX_PROC_MAPPINGS.TRANSTAGE_ID). This is the principal dependent object.
• ECX_TRAN_STAGE_DATA_PK — the primary key index on TRANSTAGE_ID.
• ECX_TRAN_STAGE_DATA_U1 — the unique index on MAP_ID, TRANSTAGE_ID, and ZD_EDITION_NAME.
• Related XML Gateway configuration objects (maps, transaction types, agents, and document definitions) are referenced indirectly through MAP_ID and action metadata, and should be reviewed alongside this table during analysis.