Search Results oe_payments_u1

Overview

ONT.OE_PAYMENTS is a transactional table within the Oracle E-Business Suite Order Management (ONT) schema that stores commitment payment information associated with order headers and lines. It captures the payment instruments, authorization details, and monetary amounts applied against order commitments, serving as the persistence layer behind the payment and credit card processing flows exposed through Order Management and iPayment integration. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are placed in APPS_TS_TX_IDX, separating data and index storage for performance and administration purposes.

Under the heuristic Data Vault classification derived from its foreign key structure, the object is modeled as standalone — that is, it functions as an independent entity rather than as a hub, link, or satellite. This classification reflects the absence of strong hierarchical foreign-key dependencies within the mined relationship set and suggests the table may be treated as a self-contained fact-style entity in analytical models, joined to dimension tables rather than decomposed into Data Vault constructs.

Key Information Stored

The table contains 53 documented columns. The most operationally significant include the following.

• PAYMENT_TRX_ID — the payment identifier, serving as a primary key candidate and referenced by the nonunique index OE_PAYMENTS_N1. The comments describe this column as "Payment id."
• HEADER_ID and LINE_ID — foreign keys to the order header and order line respectively, anchoring each payment record to a specific order context.
• PAYMENT_NUMBER — the payment sequence or reference number; together with HEADER_ID and LINE_ID it forms the unique business key.
• PAYMENT_TYPE_CODE and PAYMENT_LEVEL_CODE — classification codes identifying the payment instrument and the level (header or line) at which the payment applies.
• PAYMENT_AMOUNT and PREPAID_AMOUNT — the monetary values tendered and any prepaid component.
• COMMITMENT_APPLIED_AMOUNT and COMMITMENT_INTERFACED_AMOUNT — amounts applied against and interfaced to commitment accounting.
• CREDIT_CARD_NUMBER, CREDIT_CARD_CODE, CREDIT_CARD_HOLDER_NAME, CREDIT_CARD_EXPIRATION_DATE — credit card instrument details.
• CREDIT_CARD_APPROVAL_CODE and CREDIT_CARD_APPROVAL_DATE — authorization results returned by the payment processor.
• RECEIPT_METHOD_ID — foreign key to AR_RECEIPT_METHODS, identifying the receipt method used.
• TRXN_EXTENSION_ID — foreign key to IBY_FNDCPT_TX_EXTENSIONS, linking to payment transaction extension data.
• Standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

The unique index OE_PAYMENTS_U1 (HEADER_ID, LINE_ID, PAYMENT_NUMBER) constitutes the business-key candidate, while OE_PAYMENTS_N1 (PAYMENT_TRX_ID, PAYMENT_TYPE_CODE) supports nonunique lookups by payment transaction.

Common Use Cases and Queries

Typical reporting and reconciliation scenarios include retrieving all payments for a given order, identifying credit card authorizations, and reconciling commitment amounts interfaced to General Ledger. A representative query joining to the order header follows:

• SELECT p.payment_number, p.payment_type_code, p.payment_amount, p.credit_card_approval_code FROM ont.oe_payments p WHERE p.header_id = :header_id ORDER BY p.payment_number;
• Credit card authorization lookups filtering on PAYMENT_TYPE_CODE and requiring non-null CREDIT_CARD_APPROVAL_CODE.
• Commitment reconciliation by summing COMMITMENT_APPLIED_AMOUNT and COMMITMENT_INTERFACED_AMOUNT grouped by HEADER_ID.
• Concurrent program audit by filtering on REQUEST_ID or PROGRAM_ID to trace batch payment processing events.

Related Objects

• AR_RECEIPT_METHODS — join on OE_PAYMENTS.RECEIPT_METHOD_ID = AR_RECEIPT_METHODS.RECEIPT_METHOD_ID to resolve the receipt method.
• IBY_FNDCPT_TX_EXTENSIONS — join on OE_PAYMENTS.TRXN_EXTENSION_ID = IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSION_ID for extended payment transaction attributes.
• OE_ORDER_HEADERS_ALL — joined via HEADER_ID to obtain order-level context.
• OE_ORDER_LINES_ALL — joined via LINE_ID to obtain line-level context.
• OE_PAYMENT_TYPES or equivalent lookup objects resolve PAYMENT_TYPE_CODE values in reports.