Results for “sub_key_id”

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

Overview

IBY_TRXN_SUMMARIES_ALL is the principal transaction summary table within the Oracle Payments (IBY) module. It stores one row per payment request, capturing the header-level information required to initiate, route, authorize, settle, and reconcile a payment transaction, whether that transaction is processed through an online flow or submitted through an offline batch interface. The table is deliberately generic: its column set is designed to accommodate the full range of payment instruments supported by Oracle Payments, including checks, electronic funds transfers, wires, ACH, bills receivable, credit cards, and debit authorizations, plus the payer/payee attributes that vary by instrument type.

In Oracle EBS 12.1.1 and 12.2.2, the table resides in the IBY schema and is documented with 91 columns. It carries standard Oracle EBS who-columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), an OBJECT_VERSION_NUMBER for optimistic locking, ORG_ID for multi-org access control, and SECURITY_GROUP_ID for multi-tenant isolation. A heuristic Data Vault classification of this table, mined from its foreign key structure, suggests a satellite-leaning assertion: the table holds descriptive state and status attributes about a transaction, with its identity and structural relationships largely driven by upstream hub and link constructs.

Key Information Stored

The surrogate primary key is TRXNMID, enforced by IBY_TRXN_SUMMARIES_ALL_PK; a unique index, IBY_TRXN_SUMM_TRXNMID_U1, confirms TRXNMID as the sole business-key candidate documented for this object. The most consequential columns include:

• TRANSACTIONID — the originating source-system transaction reference, used to correlate the payment request back to the document that generated it.
• PAYEEID, MPAYEEID, MPAYEEID-related MPAYEEID, and PAYERID — identifiers for the supplier or third-party payee, the master payee record, and the payer party.
• PAYEEINSTRID and PAYERINSTRID — references to the payee and payer payment instrument records used in the transaction.
• AMOUNT and CURRENCYNAMECODE — the transaction value and its currency, essential for reconciliation and accounting.
• STATUS and OVERALL_SCORE — the transaction's lifecycle state and a confidence or risk score assigned during processing.
• BEPID, BEPCODE, BEPMESSAGE, and BEPKEY — linkage to the payment system BEP record and any diagnostic or error codes returned.
• INSTRNUMBER, INSTRTYPE, and INSTRSUBTYPE — the instrument number, type, and subtype when the request is captured online.
• BATCHID and MBATCHID — batch identifiers for online and offline grouping, with MBATCHID referencing IBY_BATCHES_ALL.
• REQDATE, REQTYPE, and REQSEQ — request date, request type, and sequence for offline submission handling.
• SETTLEDATE, PROCESS_PROFILE_CODE, and PAYMENT_CHANNEL_CODE — settlement timing, processing rule set, and the channel through which the payment is routed.
• LEGAL_ENTITY_ID and ORG_ID — legal entity and operating unit context, driving accounting and security.

Common Use Cases and Queries

The most frequent use of this table is payment status inquiry and reconciliation reporting. A typical pattern joins IBY_TRXN_SUMMARIES_ALL to IBY_TRXN_CORE on TRXNMID to obtain the instrument-specific detail row for each summary:

• SELECT ts.TRXNMID, ts.TRANSACTIONID, ts.AMOUNT, ts.CURRENCYNAMECODE, ts.STATUS, ts.PROCESS_PROFILE_CODE FROM IBY_TRXN_SUMMARIES_ALL ts WHERE ts.ORG_ID = :p_org_id AND ts.STATUS = 'PROCESSED';
• Diagnostic queries join BEPID to IBY_BEPINFO, or read BEPCODE and BEPMESSAGE directly, to identify transactions that failed in the payment system.
• Batch analysis joins BATCHID or MBATCHID to IBY_BATCHES_ALL to report on settlement volumes by batch.
• Instrument resolution joins PAYEEID or MPAYEEID to IBY_PAYEE to return payee names and remit-to details.
• Multi-org and security-filtered extracts apply ORG_ID, LEGAL_ENTITY_ID, and SECURITY_GROUP_ID predicates.

Related Objects

IBY_TRXN_SUMMARIES_ALL acts as a parent to several dependent IBY tables and as a child to reference and setup tables. Key relationships include:

• IBY_TRXN_CORE, IBY_TRXN_EXTENDED, IBY_TRXN_DOCUMENTS, IBY_TRXN_FI, and IBY_TRXN_EXTENSIBILITY all reference TRXNMID, providing instrument-specific, extended-attribute, document, financial-institution, and extensibility detail rows.
• IBY_F_T_CRDT_APPS_ALL_B and IBY_G_T_MESSAGES reference TRXN_SUMMARY_ID, linking funding/credit application and payment gateway message records.
• IBY_PAYEE is referenced through PAYEEID and MPAYEEID; IBY_PAYEE_SUBKEYS is referenced through SUB_KEY_ID.
• IBY_PMT_INSTR_USES_ALL is referenced through PAYER_INSTR_ASSIGNMENT_ID, and IBY_BATCHES_ALL through MBATCHID.
• IBY_BEPINFO (BEPID), IBY_TANGIBLE (MTANGIBLEID), IBY_CC_ISSUER_RANGES (CC_ISSUER_RANGE_ID), and IBY_TRXN_SUMMARIES_ALL via INSTRNUM_SEC_SEGMENT_ID support payment system, tangible, card issuer range, and secured instrument number processing.
• HZ_CUST_ACCOUNTS (CUST_ACCOUNT_ID), HZ_PARTIES (SALES_REP_PARTY_ID), FV_LEGAL_ENTITIES (LEGAL_ENTITY_ID), and FND_SECURITY_GROUPS (SECURITY_GROUP_ID) provide customer, party, legal entity, and security context.