Search Results iby_trxn_extended




Overview
Explore in AI Chat

Overview

IBY_TRXN_EXTENDED is a table owned by the IBY schema within the Oracle Payments (IBY) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2 with VALID status. It stores the details of a payment request that are specific to the extended set functionality for credit cards. In practice, this means the table captures supplementary attributes required when a payment instruction or transaction is processed through card networks and payment processors that demand richer data than the standard IBY transaction model provides — for example, authorization codes, settlement batch identifiers, split shipment information, and terminal identifiers.

The table is positioned as a child of IBY_TRXN_SUMMARIES_ALL, joined through the TRXNMID column, which is the sole column in the primary key constraint IBY_TRANSACTIONS_EXTENDED_UK and the unique index IBY_TRXN_EXTENDED_TRXNMID_U1. Because IBY_TRXN_EXTENDED depends on the summary table for its key and stores descriptive, transaction-specific attributes, the supplied Data Vault classification is satellite-leaning: it is best modeled as a satellite attached to the transaction hub represented by IBY_TRXN_SUMMARIES_ALL, rather than as an independent hub or link.

Key Information Stored

The most significant columns fall into several functional groups:

  • TRXNMID — the surrogate primary key and foreign key back to IBY_TRXN_SUMMARIES_ALL; every extended row is uniquely identified by it through IBY_TRXN_EXTENDED_TRXNMID_U1.
  • SETTRXNID, SPLITID, PREVSPLITID, SPLITSHIPMENT — settlement transaction identity and split shipment/settlement tracking, supporting extended set processing where one authorization spans multiple shipments or settlement records.
  • APPROVALCODE, COMPLETIONCODE, SUBAUTHIND, SUBSAUTHTYPE — card authorization outcomes, including approval and completion codes and sub-authorization indicators and types.
  • TERMINALID, CARDBIN, CUSTREFNUM, BATCHSEQNUM, BATCHTRXNSTATUS — processor-facing identifiers: terminal, card BIN, customer reference, and settlement batch sequence and status.
  • AUTHCURRENCY, AUTHPRICE, LOCALTAXPRICE, LOCALTAXCURRENCY, INSTALLTOTALTRXNS, RECURRINGFREQ, RECURRINGEXPDATE — monetary and recurring-payment attributes, including authorization amounts, local tax amounts, installment counts, and recurring frequency or expiry.
  • CREDITCOUNTER, DESTPOSTALCODE, SETREQTYPE — supporting counters, destination postal code validation data, and the extended set request type.
  • OBJECT_VERSION_NUMBER, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, SECURITY_GROUP_ID — standard EBS audit, concurrency control, and multi-tenant security columns. SECURITY_GROUP_ID also carries a foreign key to FND_SECURITY_GROUPS.

Only TRXNMID carries a documented unique index and therefore qualifies as the business-key candidate; the substantive, descriptive attributes are non-key.

Common Use Cases and Queries

Typical reporting scenarios include reconciling card authorizations to settlement batches, tracing extended set transactions for a payment, and auditing recurring and installment payment behavior. A representative query joining the table to its parent summary is:

  • SELECT e.TRXNMID, e.APPROVALCODE, e.BATCHTRXNSTATUS, s.* FROM IBY.IBY_TRXN_EXTENDED e, IBY.IBY_TRXN_SUMMARIES_ALL s WHERE e.TRXNMID = s.TRXNMID;
  • Authorization reconciliation: filter on APPROVALCODE and BATCHTRXNSTATUS to isolate approved versus settled card transactions.
  • Split shipment analysis: group by SPLITID and SPLITSHIPMENT to identify multi-shipment settlements.
  • Recurring payment review: select RECURRINGFREQ and RECURRINGEXPDATE for subscriptions approaching expiry.
  • Batch monitoring: aggregate by BATCHSEQNUM and BATCHTRXNSTATUS to report on settlement batch health.

Related Objects

  • IBY_TRXN_SUMMARIES_ALL — parent table; joined via IBY_TRXN_EXTENDED.TRXNMID = IBY_TRXN_SUMMARIES_ALL.TRXNMID.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for multi-org security filtering.
  • IBY_TRXN_SUMMARIES and related payment summary tables — provide the broader transaction context.
  • IBY_PAYMENT_INSTRUCTIONS — upstream payment instruction records that drive transaction creation.
  • IBY_TRXN_EXTENDED-related payment APIs (IBY payment transaction APIs) — populate and consume these rows during card processing.