Search Results product_group_dp

Overview

EDW_ITEM_PROD_GRP_LCV is an Oracle Engineering (ENG) reporting view that exposes product group information in a denormalized, warehouse-friendly form. The suffix "LCV" follows Oracle's EBS convention for "Localized/Collection View" objects used by the Enterprise Data Warehouse (EDW) extraction layer, indicating that the view is designed to flatten hierarchical parent/child relationships into a single dimension-style record suitable for downstream ETL loads and analytical reporting.

The view is not a transactional object. Its documented description, "This view holds product group information," and its "Not implemented in this database" implementation note confirm that it exists as a metadata-driven definition that is materialized only in environments where the EDW collection layer has been deployed. Within an EBS 12.1.1 or 12.2.2 instance, it would typically be referenced by OWB (Oracle Warehouse Builder) or equivalent extraction mappings, not by end-user forms.

Underlying Base Objects

The view text references four objects:

• AS_INTEREST_CODES_V (aliased twice as SCD and PCD) — the primary source for secondary and parent interest codes in Oracle Advanced Product Catalog / Engineering. This is the object associated with the user's search term "as_interest_codes_v."
• AS_INTEREST_TYPES_V (alias INTYP) — supplies the interest type classification and the EXPECTED_PURCHASE_FLAG used as a filter.
• EDW_LOCAL_INSTANCE (alias INST) — provides the INSTANCE_CODE used to build compound keys and to identify the source instance in a multi-instance or multi-org extraction.

No base tables are documented as directly referenced; the view is layered over the "_V" views, which themselves resolve to the underlying AS_INTEREST_CODES and AS_INTEREST_TYPES tables. The join logic ties a secondary (child) interest code to its parent interest code, and both to a common interest type.

Key Columns

• PRODUCT_GROUP_PK — Surrogate key formed as INTEREST_CODE_ID-INSTANCE_CODE-SECN_CODE, uniquely identifying the product group per instance.
• PRODUCT_CATEG_FK — Foreign key to the parent product category, built as INTEREST_CODE_ID-INSTANCE_CODE-PRIM_CODE.
• PRODUCT_GROUP_DP — Descriptive display value concatenating secondary code, parent code, and interest type.
• NAME / DESCRIPTION — The secondary interest code and its description.
• SECONDARY_CODE_ID — The INTEREST_CODE_ID of the child record.
• ENABLED_FLAG — Mapped from MASTER_ENABLED_FLAG, indicating whether the code is active.
• INSTANCE_CODE — Source instance identifier.
• CREATION_DATE / LAST_UPDATE_DATE — LAST_UPDATE_DATE is the GREATEST of the three source update dates.
• USER_ATTRIBUTE1–5, DELETION_DATE — Placeholders/defaults for extensibility and soft-delete handling.

Common Use Cases and Queries

Typical usage is dimensional extraction for product-group hierarchies in the warehouse. A representative query:

SELECT PRODUCT_GROUP_PK, PRODUCT_CATEG_FK, PRODUCT_GROUP_DP,
       NAME, DESCRIPTION, ENABLED_FLAG, INSTANCE_CODE, LAST_UPDATE_DATE
FROM   EDW_ITEM_PROD_GRP_LCV
WHERE  ENABLED_FLAG = 'Y';

Because the view filters on EXPECTED_PURCHASE_FLAG = 'Y' and on non-null LAST_UPDATE_DATE values, it returns only purchasable interest types that have been touched since the epoch date, keeping the extract set lean for incremental loads.