Search Results psa_prin




Overview
Explore in AI Chat

Overview

APPS.LNS_AM_SCHEDS_DTL_V is a reporting view within the Oracle E-Business Suite Loans (LNS) module, part of the Financial Services/Leasing application stack. It presents the amortization schedule detail for loan contracts, combining the contractual (scheduled) payment components generated by the LNS amortization engine with the actual amounts applied, credited, adjusted, and remaining, as recorded on the Accounts Receivable payment schedules. The view is designed to give a single row per amortization payment line, exposing principal, interest, and fee amounts side by side with their transactional counterparts.

Because it joins Loan headers, amortization schedules, AR payment schedules, and lookup meanings, the view is typically consumed by custom reports, reconciliation queries, and integration extracts that need a consolidated, already-decoded picture of a loan's schedule versus its receivables activity. The user search term "psa_prin" refers directly to one of the AR payment schedule aliases embedded in the view definition.

Underlying Base Objects

The documented base objects referenced by ETRM metadata are AR_PAYMENT_SCHEDULES_ALL (synonym), FND_GLOBAL (package), LNS_AMORTIZATION_SCHEDS (synonym), LNS_LOAN_HEADERS_ALL (synonym), and LNS_LOOKUPS (view). The view definition itself references:

  • LNS_LOAN_HEADERS_ALL loan — the loan master, joined on loan_id = am.loan_id.
  • LNS_AMORTIZATION_SCHEDS am — the driving amortization schedule table; rows are filtered where REAMORTIZATION_AMOUNT is null.
  • AR_PAYMENT_SCHEDULES_ALL psa_prin, psa_int, psa_fee — three outer-joined instances of the receivables payment schedule table, one each for principal, interest, and fee transactions. These are matched on customer_trx_id to the corresponding principal_trx_id, interest_trx_id, and fee_trx_id columns of the schedule.
  • LNS_LOOKUPS lok2 — resolves the BILL_TYPE lookup code derived from PARENT_AMORTIZATION_ID ('SCHEDULED' when null, otherwise 'MANUAL') to its MEANING.

FND_GLOBAL is listed in the metadata as a package dependency, consistent with standard EBS views that rely on multi-org or user context. The AR joins are all outer joins, so schedule lines without a generated transaction still appear with zeroed transactional amounts.

Key Columns

  • LOAN_ID, AMORTIZATION_SCHEDULE_ID, PAYMENT_NUMBER, DUE_DATE — loan and schedule identifiers with the payment sequence and due date.
  • PRINCIPAL_AMOUNT, INTEREST_AMOUNT, FEE_AMOUNT — the contractual amounts from the amortization schedule.
  • Principal columns — derived from psa_prin: AMOUNT_APPLIED (absolute value, zero when principal_trx_id is null), AMOUNT_CREDITED, AMOUNT_ADJUSTED, and AMOUNT_DUE_REMAINING. Two placeholder constants (0,0) separate applied, credited, and adjusted groupings.
  • Interest columns — the same pattern sourced from psa_int via interest_trx_id.
  • Fee columns — the same pattern sourced from psa_fee via fee_trx_id.
  • Total amount — an expression summing PRINCIPAL_AMOUNT, INTEREST_AMOUNT, and FEE_AMOUNT with NVL.
  • REVERSED_FLAG — decoded to 'N' when null.
  • Schedule type — 'SCHEDULED' when PARENT_AMORTIZATION_ID is null, otherwise 'MANUAL'.
  • MEANING — the BILL_TYPE lookup meaning corresponding to the schedule type.
  • PHASE — NVL(am.PHASE, 'TERM'), indicating the loan phase for the schedule line.

Common Use Cases and Queries

Typical uses include loan schedule reports, receivables-to-schedule reconciliation, collections analysis of amounts due remaining, and data extracts for downstream systems. A basic query by loan:

  • SELECT loan_id, payment_number, due_date, principal_amount, interest_amount, fee_amount FROM apps.lns_am_scheds_dtl_v WHERE loan_id = :p_loan_id ORDER BY payment_number;
  • Reconciliation of applied versus scheduled principal: SELECT amortization_schedule_id, principal_amount, amount_applied FROM apps.lns_am_scheds_dtl_v WHERE reversed_flag = 'N' AND loan_id = :p_loan_id;
  • Outstanding amounts by loan: SELECT loan_id, SUM(principal_amount) sched_principal, SUM(amount_due_remaining) outstanding FROM apps.lns_am_scheds_dtl_v GROUP BY loan_id;
  • Distinguishing manual from scheduled lines using the MEANING column, filtered on lok 'MANUAL'.

Because the AR joins are outer joins, results include scheduled lines that have not yet produced a transaction, which is essential for accurate aging and due remaining analysis. Queries should filter on REVERSED_FLAG and, where relevant, on PHASE to avoid mixing term and non-term schedule lines.