Search Results cancel_code

Overview

EDW_LOOKUP_CODE_FKV is a database view owned by the APPS schema and registered in the E-Business Suite Technical Reference Manual (ETRM) under the Purchasing (PO) product family. Its documented purpose is the "Lookup Code Key View," and it functions as a denormalized key repository that consolidates lookup code information sourced from the Oracle Application Object Library table FND_LOOKUP_VALUES. The view is designed to produce a synthetic, normalized primary key value — LOOKUP_CODE_PK — that uniquely identifies each lookup code by concatenating its code, its lookup type, and a source-system indicator. This construct is characteristic of Oracle EBS enterprise data warehouse (EDW) and extract, transform, load (ETL) staging views, which present conformed keys for downstream reporting, analytics, and cross-module integration. In the context of Oracle EBS 12.1.1 and 12.2.2, the view serves as an abstraction layer over lookup data spanning multiple products (Purchasing, Manufacturing, and Process Manufacturing), allowing reports and warehouse models to resolve lookup values without directly querying FND_LOOKUP_VALUES with its dimensional attributes.

Underlying Base Objects

The ETRM metadata documents no separate base-object list for this view, but the view text itself references a single core dictionary table: FND_LOOKUP_VALUES (aliased LV), qualified as APPS.FND_LOOKUP_VALUES. This is the Oracle Application Object Library table that stores all seeded and user-defined lookup code values used throughout EBS. The view is constructed from a UNION ALL of several slices of that table, each slice filtered by LOOKUP_TYPE, VIEW_APPLICATION_ID, LANGUAGE, and SECURITY_GROUP_ID. The Purchasing-oriented slice restricts VIEW_APPLICATION_ID = 201 (Purchasing). A Manufacturing slice restricts VIEW_APPLICATION_ID = 700 for a specific set of WIP, MRP, and inventory lookup types. A third slice isolates QA_INSPECTION_POINT using VIEW_APPLICATION_ID = 3. A further branch derives OPI_OPM_COST_METHOD values from a cost method column, tagged with the 'OPM' table code. Because the view resolves lookups only for the runtime language (USERENV('LANG')) and security group zero, it returns the appropriate translated description set for the current session.

Key Columns

• LOOKUP_CODE_PK — The synthetic primary key. For Purchasing rows it is formed as UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-PO'; for Manufacturing rows the suffix is '-MFG', and for Process Manufacturing rows the code component is the cost method code with the '-OPM' suffix. This concatenation disambiguates codes that share the same value across different lookup types.
• LOOKUP_TYPE — The lookup type identifier (for example 'ORDER TYPE', 'SHIPMENT TYPE', 'FREIGHT TERMS', 'WIP_JOB_STATUS', 'QA_INSPECTION_POINT').
• LOOKUP_CODE — The individual code value within a given lookup type.
• TABLE_CODE — A source-system tag indicating the origin application: 'PO' for Purchasing, 'MFG' for Manufacturing, and 'OPM' for Process Manufacturing.

Common Use Cases and Queries

This view is typically consumed by EDW load routines, OBIEE/BI Publisher reports, and integration extracts that require a stable lookup key or need to enumerate valid lookup codes across PO, MFG, and OPM in a single query. A representative query retrieving all Purchasing lookup keys follows:

SELECT lookup_code_pk, lookup_type, lookup_code, table_code
FROM   apps.edw_lookup_code_fkv
WHERE  table_code = 'PO'
ORDER  BY lookup_type, lookup_code;

Because LOOKUP_CODE_PK is deterministic, it can be joined to fact and dimension tables that store that surrogate key, avoiding repeated lookups against FND_LOOKUP_VALUES in large analytical queries. Analysts commonly filter by LOOKUP_TYPE to enumerate permissible values for a specific field, or aggregate by TABLE_CODE to profile lookup distribution across source applications.