Search Results lns_loan_types_vl

Overview

The APPS.LNS_LOAN_INFO view is a consolidated reporting object within the Oracle E-Business Suite (EBS) Loans (LNS) module, part of the Enterprise Treasury and Risk Management (ETRM) product family. It provides a denormalized, flattened perspective of loan master data joined with customer, loan type, disbursement, and loan adjustment information. Because the view is defined over header-level loan records and their associated disbursements and adjustments, it is commonly referenced in operational reports, custom concurrent programs, and integration extracts that require a single loan-and-related-entity representation.

The defining characteristic of LNS_LOAN_INFO is its use of UNION ALL across three constituent query blocks. Each block returns the loan header attributes (loan number, start date, funded amount, status, class, type) alongside a distinct category of subsidiary record: no child record, a disbursement record, or a loan amount adjustment record. Unused columns in each block are populated with -1 or NULL placeholders, producing a stable, uniform column signature that supports downstream reporting without requiring outer joins. The placeholder values also simplify conditional logic and null handling in reports.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced through APPS synonyms except where noted:

• LNS_LOAN_HEADERS_ALL — the primary loan header table (aliased trx).
• LNS_LOAN_TYPES_VL — the loan type view providing descriptive type names.
• LNS_DISB_HEADERS — the disbursement header table (aliased disb), joined by trx.loan_id = disb.loan_id.
• LNS_LOAN_AMOUNT_ADJS — the loan amount adjustment table (aliased ladj).
• LNS_LOAN_LINES — a referenced base object for loan line detail.
• HZ_CUST_ACCOUNTS and HZ_PARTIES — Trading Community Architecture tables supplying account number and party name.
• LNS_UTILITY_PUB — the utility package providing get_lookup_meaning(), used to translate status and class codes into descriptive text.

The joins require a valid customer account (linking to party), a valid loan type, and — in the disbursement and adjustment blocks — a matching child record in LNS_DISB_HEADERS or LNS_LOAN_AMOUNT_ADJS.

Key Columns

• loan_id, loan_number — the loan header identifier and user-visible number.
• party_name, account_number — the borrowing customer's name and account number.
• loan_start_date, funded_amount — the loan's inception date and funded principal.
• LOAN_STATUS, LOAN_CLASS — descriptive meanings resolved via lns_utility_pub.get_lookup_meaning for the loan_status and loan_class_code columns.
• loan_type_name — the descriptive loan type.
• DISB_HEADER_ID — disbursement header identifier (or -1 when not applicable).
• STATUS — the funding status meaning for the disbursement (FUNDING_STATUS lookup).
• disbursement_number — the user-visible disbursement identifier, populated only in the disbursement block. This is the primary column returned when a report requires disbursement-level detail.
• payment_request_date — the date the disbursement payment was requested.
• loan_amount_adj_id, loan_adjustment_description — adjustment identifiers and descriptions from LNS_LOAN_AMOUNT_ADJS.
• loan_line_id, reference_description — loan line identifiers and reference descriptions.

Common Use Cases and Queries

A typical use case is extracting all loans and their disbursements for reconciliation against treasury payment records. The following query filters the disjoint UNION ALL sub-sets produced by the view:

SELECT loan_number,
       party_name,
       account_number,
       disbursement_number,
       payment_request_date,
       STATUS
  FROM apps.lns_loan_info
 WHERE disbursement_number IS NOT NULL
 ORDER BY loan_number, disbursement_number;

For adjustment reporting, the same view is filtered on the adjustment identifier:

SELECT loan_number,
       account_number,
       loan_amount_adj_id,
       loan_adjustment_description
  FROM apps.lns_loan_info
 WHERE loan_amount_adj_id > 0;

Because the view resolves status and class codes to descriptive meanings via LNS_UTILITY_PUB, it is well suited to ad-hoc reporting and business-intelligence extracts where code-level translation is undesirable. Reports should account for the synthetic placeholder values (-1, NULL) present in columns irrelevant to each source block, and should apply the appropriate IS NOT NULL or > 0 predicates when targeting a specific child entity such as a disbursement.

Additional practical scenarios include loan portfolio dashboards, customer exposure listings, and integration feeds into external treasury or general ledger systems that require a unified loan-to-disbursement or loan-to-adjustment row shape.