Search Results reversed_code




Overview
Explore in AI Chat

Overview

LNS_AM_SCHEDS_DTL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the LNS (Loans) product family. It exposes amortization schedule detail lines for loan instruments, joining each scheduled payment row in LNS_AMORTIZATION_SCHEDS to the transaction accounting detail held in AR_PAYMENT_SCHEDULES_ALL. The view presents, in a single flat structure, the principal, interest, and fee components of a scheduled loan payment together with the amounts applied, credited, adjusted, and still outstanding against each component.

Because the view flattens the relationship between the loan header, the amortization schedule line, and up to three separate payment schedule records (one each for the principal, interest, and fee transaction), it is the primary vehicle for report, inquiry, and downstream integration queries that need amortization-level detail without manually joining those tables. In ETRM 12.2.2 the view is documented as VALID and is referenced by the Loans product; its structure is consistent between the 12.1.1 and 12.2.2 application releases, so queries written against it remain portable across both versions.

Underlying Base Objects

The view is defined over the following documented base objects:

  • LNS_LOAN_HEADERS_ALL (synonym) — the loan master row, joined on LOAN_ID and used to supply the LOAN_ID column.
  • LNS_AMORTIZATION_SCHEDS (synonym) — the amortization schedule lines that drive the view's grain of one row per scheduled payment number and due date.
  • AR_PAYMENT_SCHEDULES_ALL (synonym) — referenced three times, aliased PSA_PRIN, PSA_INT, and PSA_FEE, to retrieve the applied, credited, adjusted, and remaining amounts associated with the principal, interest, and fee transactions.
  • LNS_LOOKUPS (view) — joined on LOOKUP_TYPE = 'BILL_TYPE' to translate the schedule's bill type code into a user-facing meaning.
  • FND_GLOBAL (package) — a standard multi-org reference used in the view's definition.

All three AR_PAYMENT_SCHEDULES_ALL joins are outer-keyed through DECODE logic, so a schedule line that has no associated principal, interest, or fee transaction still returns a row, with zero substituted for the missing amount columns. The view also filters out reamortization parent rows through the condition on AM.REAMORTIZATION_AMOUNT IS NULL.

Key Columns

The view exposes identifying keys (LOAN_ID, AMORTIZATION_SCHEDULE_ID, PAYMENT_NUMBER, DUE_DATE) followed by three parallel groups of financial columns. For each of principal, interest, and fee, the view returns an AMOUNT_APPLIED, AMOUNT_CREDITED, AMOUNT_ADJUSTED, and AMOUNT_DUE_REMAINING value drawn from the corresponding AR_PAYMENT_SCHEDULES_ALL record. A total row amount is computed as the sum of PRINCIPAL_AMOUNT, INTEREST_AMOUNT, and FEE_AMOUNT. Additional columns include REVERSED_FLAG (defaulting to 'N'), a derived SCHEDULED/MANUAL indicator based on the presence of PARENT_AMORTIZATION_ID, the BILL_TYPE MEANING from LNS_LOOKUPS, and PHASE (defaulting to 'TERM'). The bill type meaning is the column most frequently sought by users searching on "bill_type", as it identifies whether a scheduled line represents, for example, a scheduled payment, a manual adjustment, or another lookup-defined category.

Common Use Cases and Queries

Typical usage includes amortization register reporting, loan payment due-date analysis, reconciliation of scheduled versus applied amounts, and feeds into Treasury or risk reporting. The following query lists amortization lines for a given loan with their bill type:

  • SELECT loan_id, payment_number, due_date, principal_amount, interest_amount, fee_amount, meaning FROM lns_am_scheds_dtl_v WHERE loan_id = :p_loan_id ORDER BY payment_number;
  • SELECT meaning, COUNT(*), SUM(principal_amount + interest_amount + fee_amount) FROM lns_am_scheds_dtl_v GROUP BY meaning;
  • SELECT * FROM lns_am_scheds_dtl_v WHERE reversed_flag = 'Y' AND due_date BETWEEN :p_from AND :p_to;

Because the view is a read-only reporting object, it should be used for inquiry and extraction rather than transactional updates; all underlying maintenance occurs in the LNS and AR base tables.