Search Results settlement_result_code

Overview

IBY_TRXN_EXT_SETTLEMENTS_V is a read-only PL/SQL view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the IBY (Payments) product family and serves as the presentation layer for settlement outcomes returned by external payment systems for electronic payment transactions. The view consolidates settlement status information originating from the Oracle Payments payment engine, translating numeric status codes stored in the base tables into human-readable result codes such as SETTLEMENT_SUCCESS, PAYMENT_SYS_REJECT, COMMUNICATION_ERROR, DUPLICATE_SETTLEMENT, GENERAL_INVALID_PARAM, and SETTLEMENT_PENDING. Because it joins transaction extensions, operations, and summaries, the view is typically used for reporting, reconciliation, and troubleshooting of credit card, pinless debit card, and bank account settlements processed through Oracle Payments. The object status is VALID in the documented ETRM repository.

Underlying Base Objects

The view is defined over three base synonyms, each resolving to tables in the IBY schema:

• IBY_FNDCPT_TX_EXTENSIONS — holds transaction extension records that link a payment transaction to channel-specific attributes such as payment channel code and instrument assignment.
• IBY_FNDCPT_TX_OPERATIONS — stores payment operations associated with a transaction extension.
• IBY_TRXN_SUMMARIES_ALL — contains the settlement summary rows, including status, batch identifiers, instrument type, request type, and error details.

The join condition links X.TRXN_EXTENSION_ID = O.TRXN_EXTENSION_ID and O.TRANSACTIONID = S.TRANSACTIONID. The view further restricts results to specific instrument/request combinations: credit card captures (INSTRTYPE = 'CREDITCARD' AND REQTYPE = 'ORAPMTCAPTURE'), pinless debit card requests (INSTRTYPE = 'PINLESSDEBITCARD' AND REQTYPE = 'ORAPMTREQ'), and bank account transactions where TRXNTYPEID IS NULL.

Key Columns

The documented column list exposes the following significant attributes (internal names mapped to their business meaning):

• SETTLEMENT_ID — derived from S.TRANSACTIONID; unique identifier of the settlement transaction.
• TRXN_EXTENSION_ID — foreign key to the payment transaction extension.
• SETTLEMENT_GROUP_ID — derived from S.MBATCHID; groups settlements by processing batch.
• PAYMENT_CHANNEL_CODE — channel through which the payment was routed.
• INSTR_ASSIGNMENT_ID — instrument assignment associated with the settlement.
• SETTLEMENT_RESULT_CODE — decoded status (from S.STATUS) providing the readable outcome.
• SETTLEMENT_DATE — derived from S.UPDATEDATE; last update timestamp of the settlement.
• PMT_SYS_ERR_CODE / PMT_SYS_ERR_MSG — from S.BEPCODE and S.BEPMESSAGE; back-end processor error code and message.
• PAYMENT_SYSTEM_ORDER_NUMBER — from S.TANGIBLEID; order reference returned by the payment system.
• PAYEE_ID, ORG_TYPE, ORG_ID — payee and operating unit context for multi-org reporting.

Common Use Cases and Queries

Typical scenarios include reconciliation of settlement batches, monitoring pending or rejected settlements, and diagnosing communication failures with the payment processor. A simple query retrieving successful settlements for a payee is:

SELECT settlement_id,
       settlement_group_id,
       payment_channel_code,
       settlement_result_code,
       settlement_date,
       pmt_sys_err_code,
       pmt_sys_err_msg
FROM   apps.iby_trxn_ext_settlements_v
WHERE  settlement_result_code = 'SETTLEMENT_SUCCESS'
AND    payee_id = :p_payee_id
ORDER BY settlement_date DESC;

To isolate failures requiring operational follow-up:

SELECT settlement_id,
       settlement_group_id,
       settlement_result_code,
       pmt_sys_err_code,
       pmt_sys_err_msg
FROM   apps.iby_trxn_ext_settlements_v
WHERE  settlement_result_code IN ('PAYMENT_SYS_REJECT','COMMUNICATION_ERROR')
AND    settlement_date >= SYSDATE - 7;

Because the view performs a decode on status, it is well suited to ad-hoc reporting and to integration extracts where downstream systems require standardized settlement result labels rather than numeric codes.