Results for “trx_reference”

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

Overview

OKL_STREAM_TRX_DATA is a transaction-level staging and persistence table within the Oracle E-Business Suite Leasing and Finance Management (OKL) module. It holds the XML payload exchanged with an external pricing engine, capturing both outbound request data and inbound response data for each pricing transaction. In Oracle EBS 12.1.1 and 12.2.2, this table functions as the durable landing zone where the leasing application records the full round-trip communication with the stream pricing service, enabling audit, replay, and troubleshooting of pricing calls.

The table resides in the OKL schema and is classified as VALID. Its documented structure comprises 13 columns. Under a heuristic Data Vault classification derived from the foreign key structure, OKL_STREAM_TRX_DATA is modeled as a link — it associates two parent entities (OKL_STREAM_INTERFACES and OKL_SIF_RETS) while carrying descriptive payload attributes of its own. This classification is a modeling suggestion rather than a functional constraint; the table also exhibits satellite-like characteristics because it stores the XML content and status attributes describing the transaction over time.

Key Information Stored

The table's documented primary key is the unique index OKL_STD_UK, defined on the TRANSACTION_NUMBER column, which serves as the business key for a pricing transaction. A second unique index, OKL_STREAM_TRX_DATA_U1, is defined on ID, which acts as the surrogate primary key candidate and is the more typical join key for internal relationships. The remaining columns capture the substance of each pricing exchange.

• ID — surrogate identifier for the transaction row; the target of the unique index OKL_STREAM_TRX_DATA_U1.
• TRANSACTION_NUMBER — the business-key transaction identifier enforced by unique index OKL_STD_UK.
• SIF_ID — foreign key to OKL_STREAM_INTERFACES, identifying the stream interface definition under which the transaction was executed.
• SIR_ID — foreign key to OKL_SIF_RETS, linking the transaction to the stream interface return/response record.
• OUT_XML — the outbound XML request transmitted to the external pricing engine.
• IN_XML — the inbound XML response received from the external pricing engine.
• STATUS — processing status of the transaction exchange.
• TRANSACTION_STATE and LAST_TRX_STATE — the current and most recent prior state values, supporting state-transition tracking and retry logic.
• KHR_ID and ORIG_KHR_ID — identifiers relating the transaction to the originating business entity and its original reference, useful for tracing a transaction back to its source record.
• COMMENTS — free-text annotations captured by the application or user.
• TRX_REFERENCE — an external or cross-system reference value for the transaction.

Common Use Cases and Queries

Typical usage centers on audit, reconciliation, and error diagnosis of pricing engine interactions. Common query patterns include retrieving the XML payload for a specific transaction, checking transactions stuck in a non-terminal state, and reconciling requests against responses.

• Retrieve the request and response payload for a business-key transaction: SELECT TRANSACTION_NUMBER, OUT_XML, IN_XML FROM OKL.OKL_STREAM_TRX_DATA WHERE TRANSACTION_NUMBER = :trx_num;
• Report transactions by status for operational monitoring: SELECT STATUS, COUNT(*) FROM OKL.OKL_STREAM_TRX_DATA GROUP BY STATUS;
• Join to interface and return definitions to attribute a transaction to its configuration: SELECT t.TRANSACTION_NUMBER, i.INTERFACE_NAME FROM OKL.OKL_STREAM_TRX_DATA t, OKL.OKL_STREAM_INTERFACES i WHERE t.SIF_ID = i.ID; (column names in the joined table should be verified against OKL_STREAM_INTERFACES).
• Diagnose state transitions: SELECT TRANSACTION_NUMBER, TRANSACTION_STATE, LAST_TRX_STATE FROM OKL.OKL_STREAM_TRX_DATA WHERE TRANSACTION_STATE <> LAST_TRX_STATE;

Because the XML columns are large objects, reporting queries should avoid selecting IN_XML or OUT_XML unless the payload is specifically required, particularly in high-volume extracts.

Related Objects

The documented foreign keys establish the primary relationships. OKL_STREAM_TRX_DATA.SIF_ID references OKL_STREAM_INTERFACES, and OKL_STREAM_TRX_DATA.SIR_ID references OKL_SIF_RETS. These two parent tables define the interface configuration and the return/response context for each pricing transaction. Reporting and integration logic can join on SIF_ID and SIR_ID respectively, or use the ID and TRANSACTION_NUMBER unique keys as lookup anchors. Additional OKL objects that persist or consume pricing XML — such as stream interface and return processing programs — typically depend on this table when reconstructing a complete pricing conversation, though the metadata documents only the two foreign-key relationships above and no views or PL/SQL APIs directly.

When querying for the business key OKL_STD_UK, note that this unique index is enforced on TRANSACTION_NUMBER. Searches by TRANSACTION_NUMBER are optimally served by this index, while searches by ID are served by OKL_STREAM_TRX_DATA_U1.