Search Results psa_mf_balances_view




Overview
Explore in AI Chat

Overview

PSA_MF_BALANCES_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the PSA (Public Sector Financials) product family. Its documented purpose is to show balances per Transaction Distribution. In practice, the view consolidates Accounts Receivable transaction distribution amounts and translates them into a set of balance categories: original amount due, amount applied, amount adjusted, amount credited, amount discounted, and the residual amount due remaining. This makes the view a natural building block for receivable aging, reconciliation, and collection reporting where balances must be reported at the level of the individual GL distribution line of a customer transaction.

The view returns one row per qualifying transaction distribution, keyed by CUSTOMER_TRX_ID and CUST_TRX_LINE_GL_DIST_ID. It is read-only and is intended for query and reporting use rather than as a transaction entry surface. Because it depends on the PSA multi-fund distribution tables (PSA_MF_RCT_DIST_ALL and PSA_MF_ADJ_DIST_ALL), it is most relevant to installations that use the Public Sector Financials multi-fund accounting features alongside standard Oracle Receivables.

Underlying Base Objects

The view text drives off RA_CUST_TRX_LINE_GL_DIST_ALL (aliased A), and filters out rows whose ACCOUNT_CLASS is 'REC' so that only non-receivable distribution lines are summarized. The documented referenced base objects are:

  • RA_CUST_TRX_LINE_GL_DIST_ALL — the transaction distribution lines that form the driving row set and the source of the original distribution amount.
  • PSA_MF_RCT_DIST_ALL — PSA multi-fund receipt distribution amounts, correlated to receivable applications.
  • AR_RECEIVABLE_APPLICATIONS_ALL — standard Receivables applications used to link receipt distributions to transactions and to distinguish cash receipts from credit memos via CASH_RECEIPT_ID.
  • PSA_MF_ADJ_DIST_ALL — PSA multi-fund adjustment distributions, providing the amount adjusted.
  • RA_CUSTOMER_TRX_LINES_ALL — the transaction line table referenced through the documented object list, linking distribution lines to their transaction lines.

All are presented in ETRM as synonyms under the APPS schema. The heavy use of scalar correlated subqueries means the view effectively performs a per-distribution-line aggregation across receipt, adjustment, and discount facts.

Key Columns

  • CUSTOMER_TRX_ID — identifier of the customer transaction to which the distribution line belongs.
  • CUST_TRX_LINE_GL_DIST_ID — identifier of the transaction line GL distribution, the granularity of each row.
  • AMOUNT_DUE_ORIGINAL — the original distribution amount from RA_CUST_TRX_LINE_GL_DIST_ALL.
  • AMOUNT_APPLIED — the sum of receipt distribution amounts applied to the line where a cash receipt exists (RCV_APP.CASH_RECEIPT_ID IS NOT NULL).
  • AMOUNT_ADJUSTED — the sum of adjustment distribution amounts for the line.
  • AMOUNT_CREDITED — the sum of receipt distribution amounts applied to the line where no cash receipt exists (i.e., credit memo style applications).
  • AMOUNT_DISCOUNTED — the sum of discount and earned discount amounts from receipt distributions.
  • AMOUNT_DUE_REMAINING — computed as AMOUNT_DUE_ORIGINAL − AMOUNT_APPLIED + AMOUNT_ADJUSTED + AMOUNT_CREDITED − AMOUNT_DISCOUNTED, representing the open balance on the distribution line.

Common Use Cases and Queries

The view is typically used to report open balances at distribution-line level, to reconcile PSA multi-fund receipts and adjustments against original transaction distributions, and to feed downstream aging or collections extracts. A representative query joining back to the transaction header for customer-level reporting follows:

SELECT b.CUSTOMER_TRX_ID, b.CUST_TRX_LINE_GL_DIST_ID, b.AMOUNT_DUE_ORIGINAL, b.AMOUNT_APPLIED, b.AMOUNT_ADJUSTED, b.AMOUNT_CREDITED, b.AMOUNT_DISCOUNTED, b.AMOUNT_DUE_REMAINING FROM APPS.PSA_MF_BALANCES_VIEW b WHERE b.AMOUNT_DUE_REMAINING <> 0 ORDER BY b.CUSTOMER_TRX_ID;

To obtain a transaction-level open balance, the view is aggregated by CUSTOMER_TRX_ID using SUM(AMOUNT_DUE_REMAINING), and joined to RA_CUSTOMER_TRX_ALL for customer and currency context. For multi-fund analysis, the output is grouped by fund attributes sourced from the PSA tables through the distribution identifiers. Because the view hides all receipt, adjustment, and discount aggregation logic in correlated subqueries, it offers a convenient single object for balance inquiries while remaining dependent on the PSA multi-fund subledger tables being populated.