Search Results lns_pay_sum_v




Overview
Explore in AI Chat

Overview

APPS.LNS_PAY_SUM_V is a reporting view within the Oracle Loans (LNS) product of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. Its stated purpose is to provide a "Payment Summary View," consolidating payment-level amounts for loans that are in an active servicing state. The view aggregates principal, interest, and fee values drawn from the loan amortization schedule and applied payment distributions, returning a single summarized row per loan.

The view is scoped by design: most of its conditional logic only returns values when LOAN_STATUS is ACTIVE, DEFAULT, or DELINQUENT. Loans in other statuses (closed, cancelled, or otherwise retired) are effectively nulled or zeroed. This makes the view a focused instrument for monitoring outstanding and in-progress loan obligations rather than a full historical ledger.

Although the user query referenced the term principal_balance, the view does not expose a column of that literal name. It does expose aggregated principal components — principal amounts due on the latest scheduled payment and principal amounts actually applied against the loan — which are the closest analogues to a principal balance indicator within this object.

Underlying Base Objects

The documented base objects for LNS_PAY_SUM_V are:

The view therefore sits at the intersection of Loans servicing data and Receivables payment application data. It joins the amortization schedule to the loan header to identify the latest scheduled payment and the latest amortization line, then correlates those to applied amounts in the AR payment schedules.

Key Columns

  • LOAN_ID — the loan identifier; the grouping key for all aggregates.
  • LAST_AMORTIZATION_ID — returned only for active/default/delinquent loans; identifies the most recent amortization schedule reference on the loan.
  • LAST_PAYMENT_NUMBER — the most recent payment number on the loan, also gated by status.
  • DUE_DATE — MAX(DUE_DATE) across qualifying amortization rows.
  • Principal, interest, and fee aggregates for the latest payment number, each computed as SUM(NVL(amount,0)).
  • A total scheduled amount column summing principal, interest, and fee for the latest payment.
  • Applied principal (PSA_PRIN.AMOUNT_APPLIED), applied interest (PSA_INT.AMOUNT_APPLIED), and applied fees (PSA_FEE.AMOUNT_APPLIED), accumulated across payments at or before the last payment number.

Common Use Cases and Queries

Typical uses include loan servicing dashboards, delinquency monitoring, and reconciliation between scheduled and applied amounts.

  • Outstanding loan summary — list all active, default, and delinquent loans with their latest payment amounts.
  • Collection and delinquency analysis — compare scheduled versus applied principal to detect shortfalls.
  • Reconciliation — verify applied distributions against amortization schedule expectations.

Illustrative query:

SELECT loan_id,
       last_payment_number,
       due_date,
       principal_amount,
       interest_amount,
       fee_amount,
       total_amount
FROM   apps.lns_pay_sum_v
WHERE  loan_id = :p_loan_id;

Because the view is defined in the APPS schema and references LNS and AR synonyms, it should be queried with standard MOAC and security considerations applied at the calling layer. All aggregations and status gating are performed internally, so callers receive a single pre-summarized row per qualifying loan.