Results for “loan_number”

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

Overview

LNS_LOAN_INFO is an APPS-owned, VALID database view within the Oracle E-Business Suite Loans (LNS) module. It is documented as the "Loans Accounting Events Informational View," a read-only reporting construct that consolidates loan header, customer, loan type, and disbursement attributes into a single denormalized row per loan disbursement. Unlike the transactional base tables in the Loans schema, LNS_LOAN_INFO is designed for informational and reporting consumption, particularly in support of accounting event generation and downstream financial reconciliation. The view performs no inserts, updates, or deletes; it is strictly a query surface. Because it is owned by APPS and depends on public synonyms, it is accessible to standard EBS responsibilities and is commonly referenced by custom reports, Oracle Discoverer workbooks, and BI Publisher data templates. The view sits upstream of accounting event processing, giving implementers a convenient lens into loan state and disbursement status without navigating the normalized LNS data model directly. It is present and structurally consistent across Oracle EBS 12.1.1 and 12.2.2, making it a stable integration point for both releases.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, LNS_LOAN_INFO is defined over the following base objects, all referenced through public SYNONYMs except where noted:

• LNS_LOAN_HEADERS_ALL (SYNONYM) — the primary driving table, supplying loan_id, loan_number, loan_start_date, funded_amount, loan_status, loan_class_code, loan_type_id, and cust_account_id.
• LNS_LOAN_TYPES_VL (VIEW) — provides loan_type_name; note this is a view over the loan type base table, not a table itself.
• LNS_DISB_HEADERS (SYNONYM) — joined with an outer (+) operator, supplying disbursement header, number, status, and payment request date; absence of a disbursement yields NVL(disb_header_id, -1).
• HZ_CUST_ACCOUNTS (SYNONYM) — supplies customer account_number.
• HZ_PARTIES (SYNONYM) — supplies party_name (customer name).
• LNS_UTILITY_PUB (PACKAGE) — invoked three times to translate lookup codes (LOAN_STATUS, LOAN_CLASS, FUNDING_STATUS) into their display meanings.

The metadata also lists LNS_LOAN_AMOUNT_ADJS and LNS_LOAN_LINES as referenced objects, indicating dependency links used during view compilation or by related logic. The join structure is a star-like pattern anchored on LNS_LOAN_HEADERS_ALL, with the outer join to LNS_DISB_HEADERS preserving loans that have no disbursement activity.

Key Columns

The view exposes thirteen columns. LOAN_ID is the primary loan identifier and the natural join key to LNS_LOAN_HEADERS_ALL. CUSTOMER_NAME and CUSTOMER_NUMBER resolve from HZ_PARTIES.PARTY_NAME and HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER respectively, enabling customer-level reporting. LOAN_NUMBER and LOAN_START_DATE identify the facility and its inception. FUNDED_AMOUNT conveys the funded principal. LOAN_STATUS is the most searched attribute: although it is derived via LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_STATUS', TRX.LOAN_STATUS), the underlying stored value is the lookup code in LNS_LOAN_HEADERS_ALL.LOAN_STATUS, and the view returns the human-readable meaning. LOAN_CLASS and LOAN_TYPE provide classification via the LOAN_CLASS lookup and LOAN_TYPES_VL. DISB_HEADER_ID, DISBURSEMENT_STATUS (derived from the FUNDING_STATUS lookup), DISBURSEMENT_NUMBER, and DISBURSEMENT_REQUEST_DATE describe the associated disbursement. Because disbursement attributes come from an outer join, rows for undisbursed loans carry a DISB_HEADER_ID of -1 and null disbursement fields.

Common Use Cases and Queries

The most frequent use case is filtering or grouping loans by their displayed status, which is why "loan_status" leads to this view. Typical scenarios include outstanding loan reports, accounting event reconciliation, and disbursement tracking.

• List loans by status:

  SELECT loan_number, customer_name, loan_status, funded_amount
  FROM   apps.lns_loan_info
  WHERE  loan_status = 'ACTIVE';

• Aggregate funded amount by class:

  SELECT loan_class, SUM(funded_amount) total_funded
  FROM   apps.lns_loan_info
  GROUP  BY loan_class;

• Find loans lacking disbursements:

  SELECT loan_number, customer_name
  FROM   apps.lns_loan_info
  WHERE  disb_header_id = -1;

• Disbursement pipeline:

  SELECT loan_number, disbursement_number, disbursement_status,
         disbursement_request_date
  FROM   apps.lns_loan_info
  WHERE  disbursement_status IS NOT NULL;

Because status columns are lookup-derived, filters should target the translated meaning (for example, 'ACTIVE') rather than the numeric code. Queries should prefix APPS or rely on the public synonym, and performance is best when filters target LOAN_STATUS or LOAN_ID, as the view joins several large HZ and LNS objects.