Results for “lns_amortization_schedules_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The LNS_AMORTIZATION_SCHEDULES_V view in the Oracle E-Business Suite Loans (LNS) module provides consolidated billing and payment history information for every loan recorded in the system. Residing in the APPS schema, the view joins amortization schedule data from the loan header and schedule tables with receivable-level payment activity sourced from Oracle Receivables. Its primary role is to expose, in a single reporting structure, the amounts billed, applied, credited, and adjusted against each scheduled loan installment.

From an integration and reporting perspective, the view is typically consumed by custom reports, dashboards, and reconciliation extracts that need to reconcile loan amortization schedules against actual cash applications in AR. Because it bridges LNS loan data with AR payment schedules, it is particularly useful when loan installments are invoiced through AutoInvoice and settled through standard AR receipt application. The view is read-only and is defined over base tables and synonyms rather than maintaining its own storage.

Underlying Base Objects

The documented base objects referenced by the view include:

• LNS_LOAN_HEADERS_ALL — supplies the loan header (LOAN_ID, ORG_ID) and drives the join to AR through the operating unit context.
• LNS_AMORTIZATION_SCHEDS — provides the amortization schedule lines: payment number, due date, and the principal, interest, and fee components.
• AR_PAYMENT_SCHEDULES_ALL — correlated through PRINCIPAL_TRX_ID and INTEREST_TRX_ID to return AMOUNT_APPLIED, AMOUNT_CREDITED, and AMOUNT_ADJUSTED for each installment.
• AR_RECEIVABLE_APPLICATIONS_ALL — referenced for receivable application detail that underlies cash application activity.
• FND_GLOBAL — package used to resolve session context such as organization/operating unit and user.
• FND_LOOKUPS and LNS_LOOKUPS — views supplying lookup meanings (for example, status or type code translations) joined to the schedule data.

The view therefore represents a denormalized projection of loan schedules enriched with AR settlement metrics, with each AR lookup keyed on the operating unit to ensure multi-org isolation.

Key Columns

The most significant columns exposed are:

• LOAN_ID — identifies the parent loan; joins to LNS_LOAN_HEADERS_ALL.
• AMORTIZATION_SCHEDULE_ID — unique identifier of the schedule line.
• PAYMENT_NUMBER — ordinal of the installment within the loan.
• DUE_DATE — the scheduled due date for the installment.
• PRINCIPAL_AMOUNT, INTEREST_AMOUNT, FEE_AMOUNT — the billed components of the installment.
• Amount applied columns — amounts applied for the principal and interest transactions, derived from AR_PAYMENT_SCHEDULES_ALL.AMOUNT_APPLIED.
• Amount credited columns — AMOUNT_CREDITED values for the principal and interest transactions.
• Amount adjusted columns — AMOUNT_ADJUSTED values for the principal and interest transactions.

Note the DECODE pattern used throughout: when the corresponding PRINCIPAL_TRX_ID or INTEREST_TRX_ID is null, the view returns 0, otherwise it returns the NVL'd AR value (defaulting to 0). Users searching for amount_due_remaining will not find a column of that name in this view; the residual balance is generally derived as the difference between the billed component and the applied, credited, and adjusted amounts returned here.

Common Use Cases and Queries

Typical scenarios include loan billing reconciliation, aged installment analysis, and cash application verification. A representative query derives the outstanding amount per installment:

• Reporting installments with no AR payment activity (null transaction IDs).
• Reconciling LNS scheduled principal and interest against AR applied amounts for a given operating unit.
• Identifying overdue installments by comparing DUE_DATE with the current date.

Illustrative SQL:

SELECT loan_id, payment_number, due_date, principal_amount, interest_amount, fee_amount FROM apps.lns_amortization_schedules_v WHERE loan_id = :loan_id ORDER BY payment_number;

Because residual amounts are computed rather than stored, callers should aggregate the applied, credited, and adjusted columns against the billed components to determine the amount still due on each scheduled installment.