Search Results duplicate_settlement

Overview

APPS.IBY_TRXN_EXT_SETTLEMENTS_V is a reporting and integration view in the Oracle Payments (IBY) module of Oracle E-Business Suite. It consolidates payment settlement activity across multiple payment instrument types — credit cards, purchase cards, pinless debit cards, and bank account transfers — into a single, normalized result set. The view exposes the settlement status of payment transactions alongside the associated payment channel, instrument assignment, and the batch-level settlement operation that produced the outcome.

The view is significant because Oracle EBS stores the raw settlement status as a numeric code in the summary table. This view translates that code into a human-readable string through a DECODE expression, using canonical values such as SETTLEMENT_SUCCESS, COMMUNICATION_ERROR, DUPLICATE_SETTLEMENT, GENERAL_INVALID_PARAM, PAYMENT_SYS_REJECT, SETTLEMENT_PENDING, and GENERAL_SYS_ERROR. This makes the view particularly useful for operational monitoring, reconciliation reporting, and interfaces that must surface settlement outcomes without embedding the internal status-code mapping in application logic.

The label SETTLEMENT_PENDING is derived from a set of status codes — 11, 12, 13, 18, 100, and 111 — indicating that settlement has been submitted but no terminal response has yet been received. This provides a single, unambiguous indicator for transactions that require follow-up polling or re-query against the payment processor.

Underlying Base Objects

The view is defined over three synonyms in the APPS schema, each resolving to an Oracle Payments base table:

• IBY_TRXN_SUMMARIES_ALL — the source of transaction-level settlement data (STATUS, UPDATEDATE, BEPCODE, BEPMESSAGE, TANGIBLEID, PAYEEID, MBATCHID).
• IBY_FNDCPT_TX_OPERATIONS — links the transaction extension and the transaction summary, providing the transaction identifier used to join the two sides of the query.
• IBY_FNDCPT_TX_EXTENSIONS — supplies the payment extension details, including the payment channel, instrument assignment, origin application, and the initiator extension identifier.

The joins are: IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSION_ID = IBY_FNDCPT_TX_OPERATIONS.TRXN_EXTENSION_ID and IBY_FNDCPT_TX_OPERATIONS.TRANSACTIONID = IBY_TRXN_SUMMARIES_ALL.TRANSACTIONID. Filter predicates restrict the result set to relevant instrument and request type combinations: credit card and purchase card captures (request type ORAPMTCAPTURE), pinless debit card requests (ORAPMTREQ), and bank account transactions where TRXNTYPEID IS NULL.

Key Columns

• TRANSACTIONID — the payment transaction identifier, common across all three sources.
• TRXN_EXTENSION_ID — the transaction extension key for the payment operation.
• MBATCHID — the settlement batch identifier assigned by the payment system.
• PAYMENT_CHANNEL_CODE — the payment channel through which the transaction was processed.
• INSTR_ASSIGNMENT_ID — the instrument assignment used to identify the payer's card or account.
• STATUS (derived) — the decoded settlement status string described above.
• UPDATEDATE — the timestamp of the last settlement update.
• BEPCODE / BEPMESSAGE — the backend payment processor response code and message, used for diagnostics when settlement fails or is rejected.
• TANGIBLEID / PAYEEID — the payee identifiers associated with the settlement.
• ORG_TYPE / ORG_ID — the organization context used for multi-org security filtering.
• INITIATOR_EXTENSION_ID / ORIGIN_APPLICATION_ID — identify which application and process initiated the payment.

Common Use Cases and Queries

The most frequent use case is identifying transactions awaiting settlement completion. The following query returns pending settlements for the current organization:

• SELECT transactionid, mbatchid, payment_channel_code, status, updatedate, bepcode, bepmessage FROM apps.iby_trxn_ext_settlements_v WHERE status = 'SETTLEMENT_PENDING' AND org_id = :p_org_id ORDER BY updatedate;

Additional practical applications include reconciliation reporting, where successes and rejections are compared by batch and date, and troubleshooting of processor failures using BEPCODE and BEPMESSAGE.

• Counting outcomes by status: SELECT status, COUNT(*) FROM apps.iby_trxn_ext_settlements_v GROUP BY status;
• Investigating a specific batch: SELECT * FROM apps.iby_trxn_ext_settlements_v WHERE mbatchid = :p_batch_id;
• Tracing origin application activity: SELECT origin_application_id, status, COUNT(*) FROM apps.iby_trxn_ext_settlements_v GROUP BY origin_application_id, status;

Because the view is read-only and does not enforce Multi-Org security automatically in all query tools, callers should apply ORG_ID predicates or rely on the appropriate MOAC initialization when running reports.