Results for “item_org_fk”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view APPS.EDWBV_ITEM_ITEMREV_LCV is an Oracle E-Business Suite engineering and reporting view owned by the APPS schema. It belongs to the ENG (Engineering) product family and is documented with a status of VALID in the ETRM 12.1.1 / 12.2.2 metadata repository. The "EDW" prefix positions it as part of the Oracle Enterprise Data Warehouse / Business Intelligence extract layer rather than as an operational transaction view, while the "BV" component denotes a business view and "LCV" denotes a localized (language-dependent or locale-formatted) variant. Its stated purpose is concise: it contains item revisions.

In practice, the view consolidates revision information for inventory items across inventory organizations and presents it in a denormalized, descriptor-friendly form suitable for extraction into reporting and analytics environments. The presence of a multi-part composite key, a localized display prefix, and a _DF (descriptive flexfield) marker column indicates that the object is designed for inbound integration into EDW/BI staging tables, where downstream ETL populates descriptive flexfield mappings.

Underlying Base Objects

Although the documented metadata lists no referenced base objects, the published view text establishes a four-table join across the following operational entities:

  • MTL_ITEM_REVISIONS (MTR) — the driving table, supplying revision code, effective date, and audit columns.
  • MTL_SYSTEM_ITEMS_KFV (MTI) — the key flexfield view of system items, providing the concatenated item segment description.
  • MTL_PARAMETERS (MP) — the inventory organization definitions, supplying the organization code.
  • EDW_LOCAL_INSTANCE (INST) — an EDW control table supplying the instance code used to qualify records by source instance.

Joins are made on INVENTORY_ITEM_ID and ORGANIZATION_ID between revisions and items, and on ORGANIZATION_ID between revisions and parameters. The view is declared WITH READ ONLY, confirming it is non-updatable and intended strictly for query and extraction. The _DF:INV:MTL_ITEM_REVISIONS:MTR token in the OPERATION_CODE column ties the extract back to descriptive flexfield population logic on the source table.

Key Columns

  • ITEM_REVISION_PK — a composite unique key formed from revision, inventory item ID, organization ID, and instance code, used as the EDW primary key.
  • INSTANCE — the source instance code, enabling multi-instance consolidation.
  • ITEM_ORG_FK — a foreign key linking item and organization, expressed as a concatenated value.
  • ITEM_REVISION_DP — the descriptive-prefix column, truncated to 240 characters, combining revision, item segments, and organization code.
  • NAME — an 80-character localized display name for the revision.
  • ITEM_REVISION — the full 240-character descriptive revision label.
  • EFFECTIVE_DATE, CREATION_DATE, LAST_UPDATE_DATE — revision effectivity and audit timestamps.
  • OPERATION_CODE — the descriptive flexfield operation token used during ETL.

Common Use Cases and Queries

Typical usage covers revision history reporting, engineering change analysis, and EDW staging loads. A representative extraction query is:

  • SELECT ITEM_REVISION_PK, ITEM_REVISION_DP, EFFECTIVE_DATE FROM APPS.EDWBV_ITEM_ITEMREV_LCV WHERE INSTANCE = :p_instance;
  • SELECT ITEM_ORG_FK, ITEM_REVISION, LAST_UPDATE_DATE FROM APPS.EDWBV_ITEM_ITEMREV_LCV WHERE EFFECTIVE_DATE >= :p_date;
  • Full-refresh or incremental staging via insert-select into EDW target tables, filtered on LAST_UPDATE_DATE.

Because the view is read-only and localized, it should be queried directly rather than extended, and extraction jobs should parameterize on instance and date to bound data volumes.