Search Results lns_amortization_scheds_pk

Overview

LNS.LNS_AMORTIZATION_SCHEDS is the core amortization schedule table within the Oracle E-Business Suite Loans (LNS) module. It records the individual installments due for a loan as the billing process generates them, and it also preserves reversal and re-amortization history for each installment. In practical terms, this table is the bridge between a loan instrument (owned by LNS) and the receivables transactions (owned by Oracle Receivables, RA_CUSTOMER_TRX_ALL) that billing produces. Each row represents one scheduled payment period for a loan, carrying the principal, interest, and fee components broken out separately.

The object is classified as VALID under the LNS schema and exposes 53 documented columns in ETRM 12.2.2. A Data Vault modeling heuristic derived from its foreign-key structure classifies this table as a link — it associates independent business entities (loans, receivables transactions, and interest rates) rather than acting as a standalone hub or a pure descriptive satellite. This classification is a modeling suggestion only; the table itself is a transactional installments register.

Key Information Stored

The surrogate primary key is AMORTIZATION_SCHEDULE_ID, enforced by LNS_AMORTIZATION_SCHEDS_PK and also backed by unique index LNS_AMORTIZATION_SCHEDS_U1. The most significant columns include:

• LOAN_ID — the parent loan to which the installment belongs.
• PAYMENT_NUMBER — the installment sequence within the loan.
• DUE_DATE and LATE_DATE — the scheduled due date and the late-date threshold for the installment.
• PRINCIPAL_AMOUNT, INTEREST_AMOUNT, FEE_AMOUNT, OTHER_AMOUNT — the monetary breakdown of the installment.
• PRINCIPAL_TRX_ID, INTEREST_TRX_ID, FEE_TRX_ID — foreign keys into RA_CUSTOMER_TRX_ALL, linking each billing component to its receivables transaction.
• PRINCIPAL_BALANCE and FUNDED_AMOUNT — running principal balance and funded principal.
• REVERSED_FLAG, REVERSED_DATE, PARENT_AMORTIZATION_ID — reversal tracking and lineage to the original schedule row.
• REAMORTIZATION_AMOUNT, REAMORTIZE_FROM_INSTALLMENT, REAMORTIZE_TO_INSTALLMENT — re-amortization parameters.
• RATE_ID — reference to the interest rate applied to the installment.
• STATEMENT_XML — the statement payload, stored in an LOB column (index SYS_IL0000406253C00020$$).
• PHASE and the standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER).

Common Use Cases and Queries

Reporting commonly retrieves the current outstanding installment schedule for a loan, the receivables transactions generated for principal/interest/fees, or the reversal and re-amortization history. A representative join to Receivables is:

• Retrieve all installments for a loan: SELECT * FROM LNS_AMORTIZATION_SCHEDS WHERE LOAN_ID = :loan_id ORDER BY PAYMENT_NUMBER;
• Link installments to their receivable transactions: SELECT a.AMORTIZATION_SCHEDULE_ID, a.PRINCIPAL_TRX_ID, a.INTEREST_TRX_ID, a.FEE_TRX_ID, t.TRX_NUMBER FROM LNS_AMORTIZATION_SCHEDS a, RA_CUSTOMER_TRX_ALL t WHERE a.PRINCIPAL_TRX_ID = t.CUSTOMER_TRX_ID;
• Isolate reversed installments: SELECT * FROM LNS_AMORTIZATION_SCHEDS WHERE REVERSED_FLAG = 'Y';
• Trace re-amortization lineage using PARENT_AMORTIZATION_ID to walk from a re-amortized row back to its original installment.

Because the three *_TRX_ID columns point to the same parent table, reporting queries should alias RA_CUSTOMER_TRX_ALL separately for principal, interest, and fees to avoid ambiguity.

Related Objects

The following represent the most significant relationships documented in the ETRM metadata:

• RA_CUSTOMER_TRX_ALL — referenced three times, via PRINCIPAL_TRX_ID, INTEREST_TRX_ID, and FEE_TRX_ID. This is the primary integration point with Oracle Receivables and the object most closely associated with the user's search.
• LNS_AMORTIZATION_SCHEDS (self-referencing) — via PARENT_AMORTIZATION_ID and LOAN_ID, supporting installment hierarchy and loan grouping.
• LNS_AMORTIZATION_SCHEDS_PK / LNS_AMORTIZATION_SCHEDS_U1 — primary key and unique index on AMORTIZATION_SCHEDULE_ID.
• Loan master data in the LNS schema, joined on LOAN_ID.
• Rate definitions referenced by RATE_ID.

For receivables-oriented investigation, the recurring join path is LNS_AMORTIZATION_SCHEDS.PRINCIPAL_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID, with parallel paths for interest and fee transaction identifiers.