Search Results strm_type_purpose

Overview

APPS.OKL_SEC_INVESTOR_DTLS_UV is a reporting view in the Oracle E-Business Suite Lease and Finance Management (formerly ETRM) module. It consolidates investor-side securitization and syndication agreement details together with the stream types (rent, residual, and loan payment streams) that flow through investor-owned pools. The view presents a denormalized, user-readable result set by resolving internal codes to their translated meanings through FND_LOOKUPS and by joining contract headers, pool structures, and stream type definitions. Its primary role is to support inquiries, reports, and integration extracts that must reconcile an investor agreement with the pool contents and the stream subclasses and purposes assigned to those streams.

The user search term strm_type_subclass maps directly to the FNDS lookup join in this view, where the lookup column FNDS.MEANING is aliased as STRM_TYPE_SUBCLASS and is restricted to the lookup type OKL_STREAM_TYPE_SUBCLASS.

Underlying Base Objects

The view is defined over the following documented base objects: OKC_K_HEADERS_ALL_B, OKC_STATUSES_TL, OKL_K_HEADERS, OKL_POOLS, OKL_POOL_CONTENTS, OKL_STRM_TYPE_B, and FND_LOOKUPS, with a dependency on the FND_GLOBAL package (used via USERENV('LANG') for language context).

• OKC_K_HEADERS_ALL_B — referenced twice: as CHRA (the pool owner contract) and as CHRB (the investor agreement). CHRB is filtered on SCS_CODE = 'INVESTOR'.
• OKL_K_HEADERS — supplies securitization type via SECURITIZATION_TYPE, decoded into SALE/SYNDICATION/SECURITIZATION classifications.
• OKL_POOLS — provides the pool number and lease center display flag.
• OKL_POOL_CONTENTS — links contracts to pools and stream types.
• OKL_STRM_TYPE_B — defines streams, restricted to subclasses RENT, RESIDUAL, and LOAN_PAYMENT.
• OKC_STATUSES_TL — supplies the translated status meaning, language-restricted.
• FND_LOOKUPS — referenced three times (FNDT, FNDS, FNDP) for securitization type, stream subclass, and stream purpose meanings.

The view is a UNION ALL of two branches: the first returns full investor agreement context, while the second returns pool-only rows where POL.KHR_ID IS NULL, populating the agreement columns with NULL and exposing the stream subclass and purpose for unassigned pools.

Key Columns

• KHR_ID — the contract/pool header identifier (from CHRA.ID in branch one, POC.KHR_ID in branch two).
• INVESTOR_AGRMENT_NUMBER — the investor agreement contract number (CHRB.CONTRACT_NUMBER).
• INVESTOR_AGRMENT_STATUS — translated status of the investor agreement.
• INVESTOR_AGRMENT_TYPE — derived from securitization type via the OKL_SECURITIZATION_TYPE lookup.
• STRM_TYPE_SUBCLASS — the stream subclass meaning (RENT, RESIDUAL, LOAN_PAYMENT).
• STRM_TYPE_PURPOSE — the stream purpose meaning from OKL_STREAM_TYPE_PURPOSE.
• POOL_NUMBER — the pool identifier.
• DISPLAY_IN_LEASE_CENTER — flag controlling pool visibility in the lease center UI.

Common Use Cases and Queries

Typical scenarios include investor reporting, pool composition analysis, and reconciling securitized streams to their parent agreements.

SELECT khr_id, investor_agrmnt_number, strm_type_subclass,
       strm_type_purpose, pool_number
FROM   apps.okl_sec_investor_dtls_uv
WHERE  strm_type_subclass = 'RENT';

To find residual streams across all pools:

SELECT investor_agrmnt_number, pool_number, strm_type_subclass
FROM   apps.okl_sec_investor_dtls_uv
WHERE  strm_type_subclass = 'RESIDUAL'
ORDER BY pool_number;

To isolate investor agreements by type:

SELECT investor_agrmnt_number, investor_agrmnt_type, pool_number
FROM   apps.okl_sec_investor_dtls_uv
WHERE  investor_agrmnt_type = 'SYNDICATION';

Because the view applies DISTINCT and a UNION ALL, queries joining it to other large tables should filter on subclass or pool number early to limit the result set.