Search Results transaction_status_lkup

Overview

WSH_FTE_TXN_HISTORY_V is a reporting and inquiry view within the Oracle E-Business Suite Shipping Execution (WSH) module, specifically part of the Freight Transportation Exchange (FTE) integration framework. FTE enables the exchange of shipment and delivery transaction data with external carriers, freight forwarders, and third-party logistics partners through Oracle's ECX messaging infrastructure. The view exposes the transactional audit trail captured in the FTE transaction history, presenting one row per outbound or inbound FTE message together with decoded lookup meanings for the transaction's categorical attributes.

The object is documented as a view (not a database table) and is not implemented in every database; its availability depends on whether the FTE feature set has been installed and configured. In Oracle EBS 12.1.1 and 12.2.2, it serves primarily as a read-only reporting surface for administrators and support analysts investigating message flow, acknowledgement status, and error conditions. Because the view resolves lookup codes to their displayable meanings, it is well suited for ad hoc queries, BI Publisher reports, and diagnostic extracts without requiring the consumer to join the FND lookup tables manually. This is directly relevant to a search for "transaction_status," which is one of the view's central columns and the primary indicator of message lifecycle state.

Underlying Base Objects

The view is defined over the base table WSH_FTE_TRANSACTION_HISTORY (aliased WFTH), which stores the transactional history rows themselves, together with four references to FND_LOOKUP_VALUES_VL (aliased FLV1 through FLV4) that decode the descriptive flexfield-style attribute codes. The join conditions are as follows:

• FLV1 on lookup type WSH_FTE_TXN_ACTION_TYPE, matched to WFTH.ACTION_TYPE.
• FLV2 on lookup type WSH_FTE_TXN_STATUS, matched to WFTH.TRANSACTION_STATUS.
• FLV3 on lookup type WSH_FTE_TXN_TYPE, outer-joined to WFTH.TRANSACTION_SUBTYPE (the (+) operator marks this as optional, so rows without a matching subtype code are still returned).
• FLV4 on lookup type WSH_FTE_TXN_DIRECTION, matched to WFTH.TRANSACTION_DIRECTION.

The principal base table, WSH_FTE_TRANSACTION_HISTORY, stores the FTE message tracking records generated when delivery and shipment transactions are transmitted to or received from trading partners. The ECX columns (ECX_MESSAGE_ID, ECX_TP_ID) tie each history row back to the Oracle e-Commerce Exchange messaging layer, while FTE_JOB_ID and DELIVERY_ID link the transaction to its originating delivery and scheduled concurrent job. No other base objects are documented in the ETRM metadata.

Key Columns

TRANSACTION_ID is the primary identifier of the FTE history record. TRANSACTION_STATUS holds the coded message state, decoded into TRANSACTION_STATUS_LKUP through the WSH_FTE_TXN_STATUS lookup type; values typically distinguish pending, sent, acknowledged, failed, and error conditions. TRANSACTION_SUBTYPE and its decoded TRANSACTION_SUBTYPE_LKUP identify the message category via WSH_FTE_TXN_TYPE. ACTION_TYPE with ACTION_TYPE_LKUP indicates the operation performed under WSH_FTE_TXN_ACTION_TYPE, while TRANSACTION_DIRECTION and TRANSACTION_DIRECTION_LKUP record whether the message was outbound or inbound per WSH_FTE_TXN_DIRECTION.

Timing columns include SEND_RECEIVE_DATE (when the message was transmitted or received), LAST_SEND_DATE, and PROCESSED_DATE. TRANSACTION_SEQUENCE orders messages within a logical conversation. CORRELATION_ID supports end-to-end message tracking across systems. The fifteen ATTRIBUTE columns plus ATTRIBUTE_CATEGORY provide descriptive flexfield storage. ECX_MESSAGE_ID and ECX_TP_ID identify the ECX message and trading partner, and FTE_JOB_ID and DELIVERY_ID associate the record with its concurrent request and delivery.

Common Use Cases and Queries

Typical usage includes monitoring FTE message throughput, diagnosing failed or unacknowledged transmissions, and auditing carrier interactions for a given delivery. The following query lists recent transactions with decoded status and direction:

• SELECT transaction_id, delivery_id, transaction_status_lkup, action_type_lkup, transaction_direction_lkup, send_receive_date FROM wsh_fte_txn_history_v WHERE transaction_status = 'ERROR' ORDER BY send_receive_date DESC;
• SELECT transaction_status, COUNT(*) FROM wsh_fte_txn_history_v GROUP BY transaction_status;
• SELECT h.transaction_id, h.correlation_id, h.ecx_message_id, h.ecx_tp_id FROM wsh_fte_txn_history_v h WHERE h.delivery_id = :delivery_id ORDER BY h.transaction_sequence;

Because the TRANSACTION_STATUS column stores the raw lookup code, filtering on coded values is preferred for indexing efficiency; the TRANSACTION_STATUS_LKUP column should be used for display or user-facing report output. Analysts should confirm that the FTE lookup types are populated before relying on the decoded columns, since unmatched codes return null in the respective _LKUP columns.