Search Results ce_payment_transactions_u1

Overview

The CE.CE_PAYMENT_TRANSACTIONS table is a core Cash Management (CE) object in Oracle E-Business Suite 12.1.1 and 12.2.2, serving as the repository for bank account transfer transactions and other outbound payment instructions initiated within the Cash Management module. Per the ETRM documentation, its stated purpose is "Table for storing Bank Account Transfer," which positions it as the staging and instruction table for internal fund movements, wire transfers, and related treasury payment activity. Rows are stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, reflecting a transactional, write-intensive access profile.

Because the metadata indicates no outbound foreign keys originating from other tables and the object is classified as a standalone Data Vault entity, a reasonable modeling interpretation is that CE_PAYMENT_TRANSACTIONS functions as a hub-like table: it holds the durable identity of each payment transaction keyed by TRXN_REFERENCE_NUMBER, with descriptive attributes attached directly rather than fully normalized into separate satellite structures. The inclusion of the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER confirms it participates in Oracle's standard audit and optimistic locking framework.

It is worth noting that although the relationship data lists two FK references (DOC_SEQUENCE_ID to FND_DOCUMENT_SEQUENCES and EXT_BANK_ACCOUNT_ID to IBY_EXT_BANK_ACCOUNTS), the vault classification remains listed as standalone — meaning the table is not a pure link between two hubs but rather an entity carrying its own business identity.

Key Information Stored

The table is documented with 60 columns. The most significant are summarized below.

• TRXN_REFERENCE_NUMBER (NUMBER(15)) — the Payment Transaction Identifier. This is the primary candidate key and, combined with TRXN_STATUS_CODE, forms the unique index CE_PAYMENT_TRANSACTIONS_U1 on tablespace APPS_TS_TX_IDX. This pairing is the principal business-key lookup path, which explains the widespread reference to "ce_payment_transactions_u1" in queries joining or filtering payment transactions.
• TRXN_STATUS_CODE (VARCHAR2) — status of the transaction (the second column of the unique index). Because uniqueness is enforced across reference number and status, a single reference number may appear with multiple statuses over its lifecycle.
• TRANSACTION_TYPE (VARCHAR2(30)) and TRXN_SUBTYPE_CODE_ID (VARCHAR2(15)) — classify the transaction, e.g., bank account transfer versus other payment types.
• TRANSACTION_DATE, ANTICIPATED_VALUE_DATE — the effective transaction date and expected value date.
• PAYMENT_CURRENCY_CODE (VARCHAR2(15)), PAYMENT_AMOUNT (NUMBER) — the currency and monetary amount to be paid.
• SOURCE_PARTY_ID, SOURCE_LEGAL_ENTITY_ID, SOURCE_BANK_ACCOUNT_ID — the paying side; SOURCE_BANK_ACCOUNT_ID links to the internal bank accounts table.
• DESTINATION_PARTY_ID, DESTINATION_LEGAL_ENTITY_ID, DESTINATION_BANK_ACCOUNT_ID, DESTINATION_PARTY_SITE_ID — the receiving side, again linking to internal bank accounts when applicable.
• PAYMENT_METHOD_CODE, PAYMENT_REASON_CODE, REMITTANCE_MESSAGE1–3 — payment method, reason, and remittance text carried to the payment instruction.
• DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE, DOC_CATEGORY_CODE — document sequencing and numbering, with DOC_SEQUENCE_ID referencing FND_DOCUMENT_SEQUENCES.
• EXT_BANK_ACCOUNT_ID — references IBY_EXT_BANK_ACCOUNTS, allowing external bank account details.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the flexible descriptive flexfield (DFF) columns used for customer-specific extensions.
• WHO columns and OBJECT_VERSION_NUMBER — audit and concurrency control.

Common Use Cases and Queries

Typical uses include reporting on pending and settled bank account transfers, reconciling instructions against bank statement lines, and auditing document-sequence assignment.

To retrieve a specific transaction by its business key:

SELECT trxn_reference_number, trxn_status_code, transaction_date,
       payment_currency_code, payment_amount
  FROM ce.ce_payment_transactions
 WHERE trxn_reference_number = :p_ref_number
   AND trxn_status_code      = :p_status_code;

Because the unique index CE_PAYMENT_TRANSACTIONS_U1 covers both columns, this predicate satisfies the index efficiently. To list all transactions for a given status and date range, filter on TRANSACTION_DATE, which is a common reporting column though not itself a leading index column. Transfers tied to a specific internal account can be returned via SOURCE_BANK_ACCOUNT_ID or DESTINATION_BANK_ACCOUNT_ID, and remittance lines (REMITTANCE_MESSAGE1–3) support payment advice printing. External reconciliation queries join on EXT_BANK_ACCOUNT_ID to IBY_EXT_BANK_ACCOUNTS, while document-sequence audits join DOC_SEQUENCE_ID to FND_DOCUMENT_SEQUENCES.

Related Objects

• FND_DOCUMENT_SEQUENCES — referenced by CE_PAYMENT_TRANSACTIONS.DOC_SEQUENCE_ID; validates the assigned document sequence.
• IBY_EXT_BANK_ACCOUNTS — referenced by CE_PAYMENT_TRANSACTIONS.EXT_BANK_ACCOUNT_ID; external payer/payee bank account source.
• CE_BANK_ACCOUNTS / internal bank account tables — implied by SOURCE_BANK_ACCOUNT_ID and DESTINATION_BANK_ACCOUNT_ID.
• HZ_PARTIES and HZ_CUST_ACCOUNTS — implied by SOURCE_PARTY_ID, DESTINATION_PARTY_ID, and DESTINATION_PARTY_SITE_ID (TCA identifiers).
• CE_STATEMENT_LINES — related via CREATE_FROM_STMTLINE_ID, linking transactions to originating bank statement lines.
• XLA / GL accounting tables (e.g., GL_CODE_COMBINATIONS) — referenced via PAYMENT_OFFSET_CCID and RECEIPT_OFFSET_CCID for offset account determination.
• IBY payment instruction and CE cashpool structures — associated through CASHPOOL_ID and the generic payment columns.
• CE_PAYMENT_TRANSACTIONS_U1 index — the unique index used for business-key lookups.