Search Results edw_lookup_codes_lcv




Overview
Explore in AI Chat

Overview

The EDW_LOOKUP_CODES_LCV view is a lookup code level collection view owned by the APPS schema in Oracle E-Business Suite. It belongs to the PO (Purchasing) product family and is catalogued under the ETRM (E-Business Suite Technical Reference Manual) as a valid database object. The name itself signals its design intent: the LCV suffix denotes "Level Collection View," a construct commonly used by Oracle's Enterprise Data Warehouse (EDW) and Business Intelligence extract layers to flatten, normalize, and conform reference data into a consistent shape for downstream reporting and ETL consumption.

Its central purpose is to unify lookup value data spanning Purchasing and Manufacturing lookup sources into a single, query-friendly result set. Each row represents one lookup code, decorated with a surrogate key, a display value, activation dates, an originating table code, and an instance identifier. This makes the view a convenient dimensional source for lookup code reference data rather than requiring report authors to query FND_LOOKUP_VALUES and manufacturing lookups directly.

Underlying Base Objects

The view text reveals that EDW_LOOKUP_CODES_LCV is defined as a UNION ALL of two branches, each reading from a different lookup repository:

  • FND_LOOKUP_VALUES — the standard Oracle Applications lookup table, aliased PLC, filtered by VIEW_APPLICATION_ID = 201 (Purchasing), LANGUAGE = USERENV('LANG'), and SECURITY_GROUP_ID = 0. This branch supplies Purchasing-specific lookup types such as 'RCV TRANSACTION TYPE', 'INSPECTION STATUS', 'PRICE TYPE', 'ORDER TYPE', 'SHIPMENT TYPE', 'FREIGHT TERMS', 'FOB', and 'PURCHASE CLASSIFICATION'. It is tagged with TABLE_CODE 'PO'.
  • A manufacturing lookup table (aliased MFL, referenced in the second UNION ALL branch) — tagged with TABLE_CODE 'MFG' to differentiate manufacturing-sourced lookup codes from the Purchasing set.
  • EDW_LOCAL_INSTANCE — aliased ELI and joined in each branch to populate the INSTANCE column with the local instance code, supporting multi-instance or consolidated-warehouse scenarios.

Although the documented ETRM metadata lists no base objects under "Referenced base objects," the view's SELECT text clearly establishes these dependencies. The union enforces a shared column signature across both sources.

Key Columns

  • LOOKUP_CODE_PK — a concatenated business key built as UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'PO' (or 'MFG'). This forms the surrogate primary key used in the warehouse model.
  • LOOKUP_TYPE and LOOKUP_CODE — the standard lookup classification pair from the source tables.
  • LOOKUP_ID — projected as TO_NUMBER(NULL) in the documented branches, effectively a placeholder column preserving schema compatibility for consumers expecting a numeric identifier.
  • LOOKUP_CODES_DP and NAME — both derived from SUBSTRB(MEANING, 1, 80), providing an 80-byte display value.
  • DESCRIPTION — the lookup code description.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — activation windows from the source lookup rows.
  • TABLE_CODE — indicates origin ('PO' or 'MFG').
  • INSTANCE — instance code from EDW_LOCAL_INSTANCE.
  • USER_ATTRIBUTE1–5 — reserved as NULL in the documented branches.

Common Use Cases and Queries

Typical uses include populating lookup-code dimensions in an EDW, driving parameter lists in custom reports, and conforming purchasing and manufacturing reference data for cross-domain analytics.

SELECT LOOKUP_CODE_PK, LOOKUP_TYPE, LOOKUP_CODE, NAME
FROM   APPS.EDW_LOOKUP_CODES_LCV
WHERE  LOOKUP_TYPE = 'ORDER TYPE'
AND    TABLE_CODE = 'PO';

SELECT TABLE_CODE, LOOKUP_TYPE, COUNT(*)
FROM   APPS.EDW_LOOKUP_CODES_LCV
GROUP  BY TABLE_CODE, LOOKUP_TYPE
ORDER  BY TABLE_CODE, LOOKUP_TYPE;