Search Results edw_item_itemorgpf_lcv

Overview

EDW_ITEM_ITEMORGPF_LCV is an Oracle E-Business Suite (EBS) view owned by the APPS schema and registered under the Engineering (ENG) product module. It is a reporting and integration artifact that exposes product family item information drawn from the Enterprise Data Warehouse (EDW) layer. The name itself reflects its role: the LCV suffix denotes a "lite current view" typically used by the EDW extraction and load process to present denormalized item master and item organization data in a flattened, query-friendly form. The view consolidates item-level attributes with organization-level attributes, bridging the inventory item definition and its assignment to a specific inventory organization. In EBS 12.1.1 and 12.2.2, this object is typically consumed by downstream reporting, business intelligence, and integration processes rather than by the transactional application itself. It provides a single, stable access point for item attributes such as controls, planning flags, and financial characteristics, allowing extract programs to avoid joining several normalized base tables directly.

Underlying Base Objects

The ETRM metadata documents the view as a direct SELECT over a single underlying object: EDWBV_ITEM_ITEMORGPF_LCV. The EDWBV_ prefix indicates a base EDW view, which in turn resolves to the underlying item and item-organization tables (MTL_SYSTEM_ITEMS_B, MTL_ITEM_ORG_ASSIGNMENTS or MTL_SYSTEM_ITEMS_B joined to MTL_PARAMETERS, and related product family and category tables). The metadata states there are no separately documented referenced base objects for this wrapper view, meaning the dependency chain is encapsulated by EDWBV_ITEM_ITEMORGPF_LCV. The view text includes the constant INSTANCE and trailing NULL placeholders, a common EDW pattern to guarantee column position alignment across source systems.

Key Columns

• ITEM_ORG_PK — Surrogate primary key representing the item-organization combination in the EDW.
• INSTANCE — Source instance identifier, used to distinguish data across multiple EBS instances.
• INVENTORY_ITEM_ID / ORGANIZATION_ID — The native EBS identifiers for the item and its inventory organization.
• ITEM_NUMBER / DESCRIPTION / SEGMENT1 — Descriptive item identification attributes.
• PROD_FAMILY_FK / CATSET_CATEGORY_FK — Foreign keys to product family and category set category definitions.
• EFFECTIVITY_CONTROL — Indicates whether the item is under date-effectivity control (values correspond to the MTL_SYSTEM_ITEMS_B.EFFECTIVITY_CONTROL lookup, e.g., date or revision based), governing how the item is versioned in engineering and manufacturing.
• LOT_CONTROL, SERIAL_CONTROL, LOCATOR_CONTROL, REVISION_CONTROL — Item control flags determining inventory tracking behavior.
• MAKE_OR_BUY_FLAG, PURCHASABLE_FLAG, STOCKABLE_FLAG, INTERNAL_ORD_FLAG — Sourcing and planning indicators.
• UNIT_LIST_PRICE, MARKET_PRICE, TAXABLE_FLAG, TAX_CODE, PRICE_TOL_PERCENT — Pricing and tax attributes.
• BUYER_FK, PLANNER_FK, APPROVED_SUPPLIER — References to buying and planning responsibilities.
• USER_ATTRIBUTE1–5 — Flexible descriptive attributes captured from the source item.

Common Use Cases and Queries

This view is typically queried by EDW extraction scripts and reporting tools that need product family item data with effectivity control information. A common scenario is auditing which items are effectivity-controlled and how the control type varies by organization.

SELECT ITEM_ORG_PK, ITEM_NUMBER, ORGANIZATION_ID,
       PROD_FAMILY_FK, EFFECTIVITY_CONTROL,
       LOT_CONTROL, SERIAL_CONTROL
FROM   APPS.EDW_ITEM_ITEMORGPF_LCV
WHERE  EFFECTIVITY_CONTROL IS NOT NULL;

Another use case is joining to planning or purchasing reference data via the foreign keys exposed by the view:

SELECT v.ITEM_NUMBER, v.DESCRIPTION, v.MRP_PLN_METHOD,
       v.PLANNER_FK, v.BUYER_FK
FROM   APPS.EDW_ITEM_ITEMORGPF_LCV v
WHERE  v.PURCHASABLE_FLAG = 'Y'
AND    v.PROD_FAMILY_FK IS NOT NULL;

Because the view includes CREATION_DATE and LAST_UPDATE_DATE, it also supports incremental extraction patterns, filtering on update timestamps to load only changed item-organization records into a data warehouse or data marts.