Search Results lns_pay_sum_overdue_v




Overview
Explore in AI Chat

Overview

The LNS_PAY_SUM_OVERDUE_V view is a reporting object within the Oracle E-Business Suite Loans (LNS) module, owned by the APPS schema and validated as of release 12.2.2. It is defined as an "Overdue Payment Summary View," and its purpose is to consolidate, at the loan level, the aggregate amounts that remain outstanding and past due across principal, interest, and fee components of a borrower's amortization schedule. Rather than exposing individual payment schedule rows, the view rolls each qualifying loan into a single summary row containing the summed balances due and a set of indicators describing the count and date range of overdue installments.

Because LNS is closely integrated with Oracle Receivables — loan receivables are represented through AR payment schedules — this view serves as a bridge between the AR payment schedule records that carry the monetary amounts due and the LNS loan header records that carry the loan's lifecycle status. It is typically consumed in delinquency dashboards, loan portfolio reporting, aging analyses, and downstream integrations that require a single, pre-aggregated figure for a loan's overdue exposure. Only loans whose status is one of ACTIVE, DEFAULT, or DELINQUENT contribute non-zero amounts; all other statuses are forced to zero within the CASE expressions.

Underlying Base Objects

The documented base objects referenced by the view are:

The view therefore relates a single loan (one row per LOAN_ID) to its schedule and receivable detail through these joins, collapsing potentially many installment lines into one summarized record.

Key Columns

The view exposes the following aggregate columns, all computed conditionally on loan status:

  • LOAN_ID — the grouping key, identifying the loan.
  • Summed principal (AMOUNT_DUE_REMAINING from PSA_PRIN) — total principal past due.
  • Summed interest (from PSA_INT) — total interest past due.
  • Summed fees (from PSA_FEE) — total fee amounts past due.
  • Combined overdue total — the sum of principal, interest, and fee amounts due remaining.
  • Count of overdue schedules — a COUNT over AMORTIZATION_SCHEDULE_ID where the combined balance exceeds zero.
  • Maximum overdue due date (AM.DUE_DATE) — latest overdue installment date.
  • Minimum overdue due date (AM.DUE_DATE) — earliest overdue installment date, useful for aging and bucket determination.

NVL is applied throughout so that missing component amounts resolve to zero, while the count and date metrics only include schedules where the combined overdue amount is strictly greater than zero.

Common Use Cases and Queries

The view supports portfolio delinquency reporting, loan-level overdue exposure summaries, and integration feeds. A representative query retrieves loans with any overdue balance and orders them by total exposure:

SELECT LOAN_ID, SUM_PRINCIPAL, SUM_INTEREST, SUM_FEE, SUM_TOTAL, OVERDUE_COUNT, MIN_DUE_DATE, MAX_DUE_DATE FROM APPS.LNS_PAY_SUM_OVERDUE_V WHERE SUM_TOTAL > 0 ORDER BY SUM_TOTAL DESC;

Aging-style analysis can join the minimum due date back to the loan or schedule tables, and case-management workloads frequently filter on the overdue installment count. Because the view is pre-aggregated and status-filtered, it is well suited for dashboard regions, concurrent-program extracts, and downstream interfaces that require one summarized overdue figure per loan.