Search Results sys_il0000406253c00020

Overview

LNS.LNS_AMORTIZATION_SCHEDS is the core installment schedule table within the Oracle E-Business Suite Lease and Loan Management (LNS) module. Its documented purpose is to record installments due for a loan as billing generates them, and it additionally stores any reversal and re-amortization information. In functional terms, each row represents one scheduled payment (principal, interest, fees, and other amounts) for a given loan, together with the billing transaction that fulfilled it and any adjustment history applied to it.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is classified as VALID under the LNS schema in both Oracle EBS 12.1.1 and 12.2.2. Its design reflects a transactional, high-volume schedule store rather than a static configuration object. Under the heuristic Data Vault classification supplied in the metadata, the object is modeled as a link — it connects loan headers, rate schedules, and receivables transactions through foreign keys. In a Data Vault build, this would typically be implemented as a link table joining the loan hub and the billing/transaction hubs, with the payment and reversal attributes carried alongside.

Key Information Stored

The documented physical schema for 12.2.2 contains 53 columns. The most significant are:

• AMORTIZATION_SCHEDULE_ID — surrogate primary key, defined as a NUMBER(15). It is also the column of the unique index LNS_AMORTIZATION_SCHEDS_U1, making it the business-key candidate in the index metadata.
• LOAN_ID — NUMBER(15) foreign key to LNS_LOAN_HEADERS_ALL.LOAN_ID, identifying the parent loan.
• PAYMENT_NUMBER — numeric installment sequence; indexed (non-uniquely) together with LOAN_ID via LNS_AMORTIZATION_SCHEDS_N1.
• DUE_DATE and LATE_DATE — the scheduled due date and the date after which the payment is considered late.
• PRINCIPAL_AMOUNT, INTEREST_AMOUNT, FEE_AMOUNT, OTHER_AMOUNT — the amount breakdown of the installment.
• REVERSED_FLAG and REVERSED_DATE — indicate whether and when a payment was reversed.
• RATE_ID — NUMBER(15) foreign key to LNS_RATE_SCHEDULES.RATE_ID, tying the installment to the applicable rate schedule.
• PARENT_AMORTIZATION_ID, REAMORTIZATION_AMOUNT, REAMORTIZE_FROM_INSTALLMENT, REAMORTIZE_TO_INSTALLMENT — the re-amortization lineage and scope fields.
• PRINCIPAL_TRX_ID, INTEREST_TRX_ID, FEE_TRX_ID — foreign keys to RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID linking the installment to its generated receivables invoices.
• STATEMENT_XML — a CLOB holding the XML document representing the generated invoice; it is the basis of the SYS_IL0000406253C00020$$ LOB index.
• PRINCIPAL_BALANCE, FUNDED_AMOUNT, DEFERRED_INT_AMOUNT, CAP_INT_AMOUNT, CURR_CAP_INT_AMOUNT — balance and capitalization attributes maintained across the schedule lifecycle.

Standard EBS WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), the 20 descriptive ATTRIBUTE flexfields, and OBJECT_VERSION_NUMBER complete the row, confirming the table uses Oracle's multi-org-consistent audit and optimistic locking conventions.

Common Use Cases and Queries

The dominant use case is loan amortization reporting and reconciliation between the LNS schedule and the RA invoice stream. A typical query retrieves the full installment schedule for a loan in payment order:

• Installment listing: select AMORTIZATION_SCHEDULE_ID, PAYMENT_NUMBER, DUE_DATE, PRINCIPAL_AMOUNT, INTEREST_AMOUNT from LNS_AMORTIZATION_SCHEDS where LOAN_ID = :loan_id order by PAYMENT_NUMBER — this path benefits from the LNS_AMORTIZATION_SCHEDS_N1 index.
• Reversal analysis: filter on REVERSED_FLAG and REVERSED_DATE to identify reversed installments within a period.
• Invoice tie-out: join PRINCIPAL_TRX_ID, INTEREST_TRX_ID, and FEE_TRX_ID to RA_CUSTOMER_TRX_ALL to confirm that each billed installment produced the expected receivable.
• Re-amortization tracking: use REAMORTIZE_FROM_INSTALLMENT and REAMORTIZE_TO_INSTALLMENT against PARENT_AMORTIZATION_ID to reconstruct the chain of schedule modifications.
• Statement extraction: read STATEMENT_XML to reproduce the generated invoice document; note that the CLOB and its LOB index reside in APPS_TS_TX_DATA, so full scans over this column should be avoided in favor of primary-key lookups.

Related Objects

The documented relationships establish LNS_AMORTIZATION_SCHEDS as a central link between loan master data and receivables billing:

• LNS_LOAN_HEADERS_ALL — joined on LOAN_ID; the parent loan record.
• LNS_RATE_SCHEDULES — joined on RATE_ID; supplies the interest rate basis for each installment.
• RA_CUSTOMER_TRX_ALL — referenced three times, via PRINCIPAL_TRX_ID, INTEREST_TRX_ID, and FEE_TRX_ID, linking installments to generated invoices.
• LNS_AMORTIZATION_SCHEDS itself — self-referencing through PARENT_AMORTIZATION_ID to model re-amortization history.
• The unique index LNS_AMORTIZATION_SCHEDS_U1 and the composite index LNS_AMORTIZATION_SCHEDS_N1 (LOAN_ID, PAYMENT_NUMBER) are the primary access paths for retrieving schedules by loan.

In summary, LNS_AMORTIZATION_SCHEDS is the transactional schedule repository of the LNS module, mediating between loan definitions, rate schedules, and Oracle Receivables transactions, while preserving reversal and re-amortization history for audit and reporting.