Search Results ibe_payment_detail_v




Overview
Explore in AI Chat

Overview

IBE_PAYMENT_DETAIL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the IBE (iStore) product family. It exposes payment application detail at the level of individual receivable application lines, joining Accounts Receivable payment schedules, receivable applications, cash receipts, and iStore-relevant lookup translations. The view is used by iStore and related order-to-cash reporting to present applied payments, discounts taken, invoice balances, and receipt context in a single denormalized result set. In practice it answers the question, "which receipts were applied to which transactions, for how much, on what date, and with what remaining balance?" A search for "ps_inv" typically reflects a troubleshooting or extension exercise referencing this view, because the view text uses PS_INV as the alias for AR_PAYMENT_SCHEDULES_ALL, the transaction-side base object that carries invoice number, class, currency, status, and balances.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over the following referenced base objects:

The join is driven by APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID and restricted by APP.DISPLAY = 'Y', ensuring only active, displayed applications are returned. Lookup joins are constrained to the lookup types INV/CM, PAYMENT_TYPE (with lookup code ACC), and INVOICE_TRX_STATUS, which keeps each join cardinality to one meaning value. The HZ_CUST_ACCOUNTS join is an inner join and therefore requires a valid customer account record.

Key Columns

  • CASH_RECEIPT_ID and RECEIPT_NUMBER — identity of the applied cash receipt.
  • TRX_NUMBER — DECODE logic returns the L_ACC meaning (the "ACC" payment type meaning) when APPLIED_PAYMENT_SCHEDULE_ID equals -1 (an on-account, unapplied application), otherwise PS_INV.TRX_NUMBER.
  • TRX_CLASS_NAME — the INV/CM lookup meaning, returned as NULL for on-account applications.
  • AMOUNT_APPLIED — amount applied from the receipt to the transaction.
  • DISCOUNT — sum of earned and unearned discount taken, forced to NULL for on-account rows.
  • APPLY_DATE — the accounting/reporting date of the application.
  • AMOUNT_DUE_ORIGINAL and AMOUNT_DUE_REMAINING — original transaction amount and remaining balance; for on-account entries the remaining value is derived as AMOUNT_APPLIED multiplied by -1.
  • CUSTOMER_TRX_ID, STATUS, STATUS_MEANING, CURRENCY_CODE, ORG_ID — transaction identifier, raw and translated status, invoice currency, and operating unit.

Common Use Cases and Queries

Typical uses include reconciling receipts to invoices, reporting unapplied and on-account cash, validating discount taken, and supporting iStore payment-status displays. Because ORGANIZATION_ID filtering is performed in the consuming query via ORG_ID, multi-org security must be applied by the caller.

Example: applications against a given invoice number:

  • SELECT trx_number, receipt_number, amount_applied, discount, apply_date, amount_due_remaining FROM ibe_payment_detail_v WHERE trx_number = :invoice_number;

Example: on-account and unapplied receipt lines for an operating unit:

  • SELECT receipt_number, trx_number, amount_applied, currency_code FROM ibe_payment_detail_v WHERE org_id = :org_id AND trx_class_name IS NULL ORDER BY receipt_number;

Example: aging of remaining balances by status:

  • SELECT status_meaning, currency_code, SUM(amount_due_remaining) FROM ibe_payment_detail_v WHERE org_id = :org_id GROUP BY status_meaning, currency_code;

Because DISCOUNT and AMOUNT_DUE_REMAINING are computed with DECODE and TO_NUMBER, consumers should treat on-account and unapplied rows separately from invoice distributions, and should expect TRX_NUMBER to carry a payment-type meaning rather than a transaction number in those cases.