Search Results last_installment_overdue_date

Overview

APPS.LNS_BILLING_UTIL_PUB is a public PL/SQL utility package in the Oracle E-Business Suite Loans (LNS) module, the subsystem that supports loan, lease, and mortgage servicing within Oracle Receivables. It declares AUTHID CURRENT_USER, so its SQL statements execute with the privileges of the calling schema rather than the definer, and it is documented in ETRM as a PUB-classified API with 22 procedures and functions. The package encapsulates the billing and payment-status computations that drive loan repayment reporting: amounts paid in the current and prior periods, amounts and counts of overdue installments, most-recent and oldest payment activity, amortization-driven balances, and validation of GL dates for loan transactions. Rather than requiring callers to reconstruct billing history from raw transaction and amortization data, it exposes a stable functional interface keyed on a loan identifier, allowing forms, concurrent programs, and custom extensions to obtain consistent figures for statements, delinquency tracking, and accounting validation. The header of the source file dates to 2003, indicating a long-lived, stable component of the LNS billing layer.

Key Procedures and Functions

The documented entry points group into several functional areas:

• Year-to-date and prior-period payment measurement: AMOUNT_PAID_YTD and AMOUNT_PAID_LAST_YEAR return amounts paid for a given loan and line type. The header comments note that these calculate amount paid YTD and principal paid last year, respectively.
• Payment and installment position: NEXT_PAYMENT_DUE calculates the next amount due; LAST_PAYMENT_NUMBER and its EXT variants (LAST_PAYMENT_NUMBER_EXT, _EXT_1, _EXT_2, _EXT_3) determine the most recent payment number for a loan, with the extended variants supporting additional caller requirements.
• Delinquency and aging: AMOUNT_OVERDUE and NUMBER_OVERDUE_BILLS quantify overdue exposure; LAST_OVERDUE_DATE, OLDEST_OVERDUE_DATE, and LAST_INSTALLMENT_OVERDUE_DATE identify aging reference dates.
• Payment activity details: LAST_PAYMENT_AMOUNT and LAST_PAYMENT_DATE return the most recent payment's value and timing.
• Balance and amortization: LAST_AMORTIZATION_SCHED, LOAN_BALANCE_BY_DATE, and GET_LOAN_REMAIN_AMOUNT derive scheduled and remaining loan balances.
• Date validation: VALIDATE_AND_DEFAULT_GL_DATE validates a supplied accounting date and derives a default; GET_TRX_DATE returns the transaction date.

Each of these is documented as a public function; the user search term p_loan_id corresponds to the loan identifier parameter that is the common first argument across nearly all of these signatures.

Tables Accessed

The package reads and, where applicable, writes through APPS synonyms. Core loan data resides in LNS_LOAN_HEADERS and LNS_LOAN_HEADERS_ALL, which supply loan-level attributes and bill-to context. Amortization detail comes from LNS_AMORTIZATION_SCHEDS, which underpins LAST_AMORTIZATION_SCHED, LOAN_BALANCE_BY_DATE, and GET_LOAN_REMAIN_AMOUNT. Fee billing data is drawn from LNS_FEES_ALL and LNS_FEE_SCHEDULES. Payment application history is read from AR_RECEIVABLE_APPLICATIONS_ALL, which is essential to AMOUNT_PAID_YTD, AMOUNT_PAID_LAST_YEAR, LAST_PAYMENT_AMOUNT, and LAST_PAYMENT_DATE. Overdue computation depends on payment terms defined in RA_TERMS and RA_TERMS_LINES. LNS_SYSTEM_OPTIONS provides module-level system settings that influence defaults and calculations.

Usage Notes

LNS_BILLING_UTIL_PUB is designed for invocation from LNS billing and servicing forms, from concurrent programs that generate loan statements, delinquency reports, and aging extracts, and from custom PL/SQL that requires loan payment or balance figures without replicating billing logic. Because it is a PUB-classified package, it may be referenced directly by customer extensions, but callers should treat the signatures as stable rather than frozen, given the existence of multiple LAST_PAYMENT_NUMBER_EXT variants that indicate iterative extension rather than replacement. Eleven other packages reference this API, so changes to it propagate widely within the LNS module. Implementations consuming these functions should pass a valid p_loan_id and a recognized line type, and should expect numeric return values suited to formula and reporting contexts. Organizations should also verify that the APPS schema has the required synonyms in place, since the package relies on them for all table access.