Search Results item_org_fk




Overview
Explore in AI Chat

Overview

The view EDW_ITEM_ITEMREV_LCV is a reporting and integration construct within the Oracle E-Business Suite (EBS) Engineering (ENG) module. Its documented purpose is to expose item revision level information — that is, the revision definitions associated with items, including the date from which each revision is effective. The "_LCV" suffix denotes a "Lightweight Collection View," a naming convention used by Oracle's Enterprise Data Warehouse (EDW) and ETRM reporting layers to denote a simplified, denormalized projection of base operational data intended for extraction, downstream loading, and analytical reporting rather than for transactional use.

The view is not implemented in the source database, which indicates it is a metadata-defined object delivered as part of the ETRM/EDW reference model. In practice it serves as a stable contract for ETL processes that need item revision data without navigating the full normalized Engineering schema.

Underlying Base Objects

The view text reveals that EDW_ITEM_ITEMREV_LCV is defined entirely over a single object: EDWBV_ITEM_ITEMREV_LCV. The prefix "EDWBV" identifies it as an EDW base view, i.e., a lower-level collection view that this public view wraps and extends. The ETRM metadata records no referenced base tables for this object, which is consistent with a layered view design: the adapter view supplies the column list and positional placeholders, while the underlying EDWBV view resolves to the actual Engineering item revision tables at runtime.

The exposed column list contains five trailing NULL placeholders (USER_ATTRIBUTE1 through USER_ATTRIBUTE5), a standard EDW technique for conforming heterogeneous sources to a fixed column footprint across products.

Key Columns

  • ITEM_REVISION_PK — Primary key of the item revision record. Used as the unique identifier for joins and incremental loads.
  • INSTANCE — Identifies the source EBS instance, enabling multi-instance consolidation in the warehouse.
  • ITEM_ORG_FK — Foreign key to the item/organization combination. This is the column surfaced by the user's item_org_fk search and is the principal join path to item master and organization data.
  • ITEM_REVISION_DP — Descriptive flexfield (DFF) context or descriptor associated with the revision.
  • NAME — Name of the item to which the revision belongs.
  • ITEM_REVISION — The revision label or code (for example, A, B, or 1.0).
  • EFFECTIVE_DATE — Date on which the revision becomes effective.
  • CREATION_DATE / LAST_UPDATE_DATE — Standard audit columns supporting delta extraction.
  • OPERATION_CODE — Change indicator (INSERT/UPDATE/DELETE) used by the EDW incremental load process.
  • USER_ATTRIBUTE1–5 — Reserved placeholders, returned as NULL in this view.

Common Use Cases and Queries

The most frequent scenario is joining revisions back to items through ITEM_ORG_FK, as in the following example:

SELECT r.ITEM_REVISION_PK,
       r.ITEM_ORG_FK,
       r.NAME,
       r.ITEM_REVISION,
       r.EFFECTIVE_DATE
FROM   EDW_ITEM_ITEMREV_LCV r
WHERE  r.EFFECTIVE_DATE >= :p_from_date
ORDER  BY r.ITEM_ORG_FK, r.EFFECTIVE_DATE;

Because ITEM_ORG_FK links revisions to a specific item-organization pair, analysts typically resolve it against the item-org dimension view to obtain organization codes and item numbers. Revision history and effectivity analysis queries filter on EFFECTIVE_DATE and order by ITEM_REVISION to reconstruct the revision sequence. Incremental ETL loads use LAST_UPDATE_DATE and OPERATION_CODE to identify changed rows. All queries should treat the object as read-only and rely on the documented column set, since the view is delivered as EDW metadata rather than a locally implemented database object.