Results for “lns_receivable_activities_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The LNS_RECEIVABLE_ACTIVITIES_V view is an APPS-owned, read-only database object within the Oracle E-Business Suite Loans (LNS) module. It consolidates payment and receivable activity for every installment of every loan recorded in the system. Rather than requiring report authors or integration developers to join the loan amortization tables to the Oracle Receivables application tables directly, this view presents a pre-joined, denormalized result set that associates each loan installment with its corresponding customer transaction, payment schedule, and receivable application.

The view is commonly used as a reporting and integration layer in EBS 12.1.1 and 12.2.2. It supports operational reporting on loan repayment status, cash application analysis, and reconciliation between the LNS loan subledger and Oracle Receivables. Because it exposes standard Receivables identifiers such as CUSTOMER_TRX_ID, PAYMENT_SCHEDULE_ID, and RECEIVABLE_APPLICATION_ID, it is frequently used as a bridge between the Loans module and AR-centric downstream processes.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view resolves against the following base objects: AR_ADJUSTMENTS_ALL, AR_LOOKUPS, AR_PAYMENT_SCHEDULES_ALL, AR_RECEIVABLES_TRX_ALL, AR_RECEIVABLE_APPLICATIONS_ALL, FND_GLOBAL, LNS_AMORTIZATION_LINES, LNS_AMORTIZATION_SCHEDS, LNS_LOAN_HEADERS_ALL, LNS_LOOKUPS, RA_CUSTOMER_TRX_ALL, and RA_CUSTOMER_TRX_LINES_ALL (most as synonyms within APPS).

The Loans core is represented by LNS_LOAN_HEADERS_ALL (the loan master), LNS_AMORTIZATION_SCHEDS (the amortization/installment schedule), and LNS_AMORTIZATION_LINES (the individual principal and interest lines within each schedule). These are joined to Oracle Receivables objects: RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL carry the billing detail, AR_PAYMENT_SCHEDULES_ALL carries both transaction and receipt payment schedules, and AR_RECEIVABLE_APPLICATIONS_ALL captures the application of cash to the transaction. AR_LOOKUPS and LNS_LOOKUPS supply decode meanings for status and classification. FND_GLOBAL provides session context such as organization and user identifiers.

Key Columns

• LOAN_ID — Identifies the parent loan in LNS_LOAN_HEADERS_ALL.
• AMORTIZATION_SCHEDULE_ID / PAYMENT_NUMBER / DUE_DATE — Identify the specific installment and its contractual due date.
• CUSTOMER_TRX_ID — The Receivables transaction generated for the installment.
• PAYMENT_SCHEDULE_ID (TRX_PSA) — The payment schedule of the transaction.
• CUST_TRX_LINE_ID / LINE_TYPE — The transaction line and its classification (principal, interest, fees).
• AMOUNT_APPLIED (negated as -REC.AMOUNT_APPLIED) — The cash applied against the installment.
• APPLY_DATE — The date the receipt was applied.
• RECEIVABLE_APPLICATION_ID / CASH_RECEIPT_ID — Identify the receivable application and receipt.
• TRX_NUMBER / REC_PSA.TRX_NUMBER — Transaction and cash receipt numbers.
• PHASE — Loan phase (defaulted to 'TERM' via NVL).
• ORG_ID / LEGAL_ENTITY_ID — Multi-org and legal entity context.

The view also computes a running principal balance using a correlated subquery that sums cash applications against the loan's PRINCIPAL_TRX_ID, which is the specific column users search when reconciling principal repayment activity.

Common Use Cases and Queries

Typical scenarios include loan repayment reporting, AR-to-LNS reconciliation, and cash application audits. A representative query retrieving applied activity for a loan follows:

• SELECT loan_id, payment_number, due_date, trx_number, amount_applied, apply_date FROM lns_receivable_activities_v WHERE loan_id = :p_loan_id ORDER BY due_date;
• SELECT * FROM lns_receivable_activities_v WHERE cash_receipt_id = :p_receipt_id;
• SELECT loan_id, phase, org_id, SUM(amount_applied) FROM lns_receivable_activities_v GROUP BY loan_id, phase, org_id;

Because it is a view (not a table), no DML is permitted; consumers should treat it strictly as a query source. Performance depends on the underlying AR and LNS indexes, so filtering by LOAN_ID or CASH_RECEIPT_ID is recommended for large volumes.