Results for “trx_currency”

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

Overview

The view APPS.LNS_REC_ACT_CASH_CM_V is a Loans (LNS) module reporting object that consolidates receivable activity arising from cash receipts and credit memos applied against loan-related transactions. Within Oracle E-Business Suite 12.1.1 and 12.2.2, the LNS product handles loan origination, amortization, and the associated accounting and receivables events. This view, described in the ETRM repository as the "Receipts and Credit Memo Receivable Activities View," exposes the linkage between loan amortization schedules, their corresponding AR invoices, and the applications (cash receipts or credit memos) that settle or adjust those invoices.

The view is read-only and is defined in the APPS schema. Its principal role is to support reconciliation and audit reporting: for a given loan, it returns each amortization payment along with the receivable transaction that was applied against it, the application type, amount, currency, and the reason associated with a credit memo. Because it references AR_RECEIVABLE_APPLICATIONS_ALL, it inherently captures both cash receipt applications and credit memo applications, distinguished by the APPLICATION_TYPE value. The view filters on REC.DISPLAY = 'Y', ensuring only applications flagged for display are surfaced.

Underlying Base Objects

The view is constructed over ten documented base objects spanning the LNS and AR schemas:

• LNS_LOAN_HEADERS_ALL — the loan master record supplying LOAN_ID, ORG_ID, and LEGAL_ENTITY_ID.
• LNS_AMORTIZATION_SCHEDS — the amortization schedule header, providing AMORTIZATION_SCHEDULE_ID, PAYMENT_NUMBER, DUE_DATE, and PHASE.
• LNS_AMORTIZATION_LINES — the individual schedule lines that carry LINE_TYPE and the customer transaction/line references.
• AR_PAYMENT_SCHEDULES_ALL — joined twice (aliases TRX_PSA and REC_PSA) to resolve the transaction-side and receipt-side payment schedules respectively.
• AR_RECEIVABLE_APPLICATIONS_ALL — the core application record containing the amount applied, application date, application type, and cash receipt identifier.
• RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL — the AR transaction header and lines, supplying the transaction number, currency, line description, and the credit memo reason code.
• AR_LOOKUPS (joined twice) — resolves lookup meanings for the credit class and the credit memo reason code.
• LNS_LOOKUPS — resolves the amortization line type into a meaningful PAYMENT_APPLICATION_TYPE description.
• FND_GLOBAL — referenced for session context (organization/legal entity resolution).

All joins are equi-joins except the link to RA_CUSTOMER_TRX_ALL, which is an outer join (TRX1.CUSTOMER_TRX_ID(+)) so that cash receipt applications without an associated credit memo reason are still returned.

Key Columns

• LOAN_ID, AMORTIZATION_SCHEDULE_ID, PAYMENT_NUMBER, DUE_DATE — identify the loan and the specific scheduled installment.
• CUSTOMER_TRX_ID, PAYMENT_SCHEDULE_ID, TRX_NUMBER — identify the AR transaction (invoice) to which activity applies.
• LINE_TYPE / MEANING — the amortization line classification decoded through LNS_LOOKUPS.
• Credit class and reason — a concatenation of ARL_CLA.MEANING with the credit memo reason meaning (ARL_CLA1.MEANING), which is directly relevant to the credit_memo_reason search. This column surfaces the reason code populated on the credit memo transaction.
• AMOUNT_APPLIED — negated (-REC.AMOUNT_APPLIED) to present applications as reductions against the receivable.
• APPLY_DATE, RECEIVABLE_APPLICATION_ID, CASH_RECEIPT_ID — application timing and identifiers.
• APPLICATION_TYPE-derived columns — conditional DECODE logic exposes the applied-from amount, receipt currency, and the receipt-to-transaction exchange rate only when the application type is 'CASH'; otherwise NULL.
• ORG_ID, LEGAL_ENTITY_ID — multi-org and legal entity context.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage centers on reconciliation of loan installments against AR activity and analysis of credit memo reasons. A representative query retrieving all activity for a loan is:

• SELECT loan_id, payment_number, due_date, trx_number, amount_applied, apply_date, cash_receipt_id FROM apps.lns_rec_act_cash_cm_v WHERE loan_id = :loan_id ORDER BY due_date, payment_number;

To isolate credit memo activity and inspect the reason column:

• SELECT trx_number, amount_applied, apply_date FROM apps.lns_rec_act_cash_cm_v WHERE loan_id = :loan_id AND amount_applied IS NOT NULL;

Because the reason meaning is concatenated into a single column, users filter via a pattern match on that text to locate specific credit memo reasons. The view is frequently embedded in custom LNS reconciliation reports and period-end analysis of receivable applications tied to amortization schedules.