Search Results item_revision_dp

Overview

EDW_ITEM_ITEMREV_LCV is an Oracle E-Business Suite view owned by the APPS schema that exposes item revision data in a denormalized, flattened form suitable for extract, transform, and load operations into the Oracle EBS Enterprise Data Warehouse (EDW). The "LCV" suffix denotes a "Loadable Column View" — an ETL-oriented construct designed to present a uniform column interface to downstream warehouse staging and dimension-loading processes rather than to serve as a transactional user-facing object.

The view presents a single record per item revision, combining surrogate keys, descriptive attributes, effective dating, audit columns, and an operation code used by warehouse loading programs to determine whether a row should be inserted, updated, or deleted in the target star-schema dimension. It is not a master data maintenance object; it is a read-only reporting and integration surface, and it should never be used for DML against item revision records.

Underlying Base Objects

The view is defined over a single documented base object, EDWBV_ITEM_ITEMREV_LCV, which itself belongs to the Oracle EDW product family of "base views." The ETRM metadata for EDW_ITEM_ITEMREV_LCV lists no other referenced base tables, and the view definition performs no joins — every column is projected directly from EDWBV_ITEM_ITEMREV_LCV, with no filtering, grouping, or aggregation applied.

Because EDW_ITEM_ITEMREV_LCV references only the base view, all cardinality, change-detection logic, and key derivation — including the surrogate ITEM_REVISION_PK and the operation code — originate in EDWBV_ITEM_ITEMREV_LCV. The wrapper exists primarily to enforce a stable public column interface so that changes in the underlying EDW source views do not break downstream ETL mappings.

Key Columns

• ITEM_REVISION_PK — Surrogate primary key for the item revision dimension row, used by warehouse loaders to identify the target record.
• INSTANCE — Identifies the source EBS instance, allowing multi-instance consolidation in the warehouse.
• ITEM_ORG_FK — Foreign key to the item/organization combination with which the revision is associated.
• ITEM_REVISION_DP — The item revision description column; this is the column matched by the user's search term "item_revision_dp" and is the descriptive (DP) attribute of the revision, distinct from the revision code itself.
• NAME — Exposed as SUBSTRB(NAME, 1, 320), truncating the item name to 320 bytes to respect warehouse column-width constraints.
• ITEM_REVISION — The revision label or code (for example, "A", "B", "REV-01") assigned to the item.
• EFFECTIVE_DATE — Date from which the revision becomes effective for the item.
• CREATION_DATE / LAST_UPDATE_DATE — Standard audit columns; LAST_UPDATE_DATE is typically the driver for incremental change capture.
• OPERATION_CODE — Loader control flag indicating the change operation to apply in the target dimension.
• Six trailing NULL columns — Placeholders reserving column positions aligned with other members of the EDW item LCV family, ensuring a consistent union-compatible projection.

Common Use Cases and Queries

The principal use case is incremental extraction of item revision data for the EDW item dimension. A typical incremental pull filters on LAST_UPDATE_DATE and projects the documented columns:

• SELECT item_revision_pk, instance, item_org_fk, item_revision_dp, substrb(name,1,320) item_name, item_revision, effective_date, operation_code FROM apps.edw_item_itemrev_lcv WHERE last_update_date >= :p_since_date;

Because the view projects only the base view's columns with no transformations, it can be used to validate the underlying EDW base view:

• SELECT operation_code, COUNT(*) FROM apps.edw_item_itemrev_lcv GROUP BY operation_code; — confirms the distribution of insert/update/delete flags before a warehouse load.

A third pattern joins the view to item master data to audit revision descriptions across organizations using ITEM_ORG_FK, while a fourth uses the surrogate key for idempotent MERGE statements into the warehouse dimension. Query tuning generally requires attention to LAST_UPDATE_DATE indexing in the underlying base view, since the wrapper imposes no predicate pushdown of its own.