Search Results product_categ_dp

Overview

EDW_ITEM_PROD_CATG_LCV is a reporting view owned by the APPS schema in Oracle EBS 12.1.1 and 12.2.2, delivered as part of the Engineering (ENG) product family. It exposes product category information derived from Oracle Advanced Supply Chain Planning interest code and interest type definitions, presented in a flattened, denormalized form suitable for extract, transform, and load (ETL) consumption by the Oracle E-Business Suite Data Warehouse and related analytical reporting layers. The "EDW_" prefix indicates the view is intended as a staging construct for the enterprise data warehouse, while the "_LCV" suffix denotes a "Local Collection View" style object used for incremental or snapshot-style extraction.

A distinctive characteristic of this view is the presence of the search term intr_type in the composite key column PRODUCT_LINE_FK. This column is generated by concatenating INTEREST_TYPE_ID, the instance code from EDW_LOCAL_INSTANCE, and the literal string "INTR_TYPE", producing a surrogate key that maps product categories to their parent product line in the warehouse model.

Underlying Base Objects

The view is defined over three sources:

• AS_INTEREST_CODES_V (alias CD) — the primary source supplying interest code (product category) attributes such as code, description, primary code ID, master enabled flag, and audit dates.
• AS_INTEREST_TYPES_V (alias INTYP) — supplies interest type data, notably the INTEREST_TYPE_ID and INTEREST_TYPE description embedded in the category display value.
• EDW_LOCAL_INSTANCE (alias INST) — supplies the INSTANCE_CODE used to qualify surrogate keys, ensuring records are instance-scoped in multi-org or multi-instance warehouse deployments.

The join predicates link CD.INTEREST_TYPE_ID to INTYP.INTEREST_TYPE_ID and restrict to rows where CD.PARENT_INTEREST_CODE_ID IS NULL (top-level categories only) and INTYP.EXPECTED_PURCHASE_FLAG = 'Y'. A final filter excludes records whose LAST_UPDATE_DATE on either side is not later than 1000/01/01, effectively screening out uninitialized or placeholder date values.

Key Columns

• PRODUCT_CATEG_PK — surrogate primary key: INTEREST_CODE_ID || '-' || INSTANCE_CODE || '-PRIM_CODE'.
• PRODUCT_LINE_FK — foreign key referencing the parent product line: INTEREST_TYPE_ID || '-' || INSTANCE_CODE || '-INTR_TYPE'. This is the column most closely associated with the "intr_type" search term.
• PRODUCT_CATEG_DP — display value combining the category code and interest type, e.g. "CODE(INTEREST_TYPE)".
• NAME / DESCRIPTION — category code and descriptive text from AS_INTEREST_CODES_V.
• PRIMARY_CODE_ID / ENABLED_FLAG — the underlying interest code ID and master enabled flag.
• INSTANCE_CODE — identifies the source instance for the record.
• CREATION_DATE / LAST_UPDATE_DATE — audit timestamps; LAST_UPDATE_DATE is the GREATEST of the two source views' update dates, supporting incremental extraction.
• USER_ATTRIBUTE1–5 and DELETION_DATE — placeholders returned as NULL, provided for schema conformance with the warehouse target.

Common Use Cases and Queries

Typical uses include loading product category dimensions into the data warehouse, validating category-to-product-line hierarchies, and driving incremental refreshes keyed on LAST_UPDATE_DATE.

Example — list active top-level categories with their product line:

• SELECT product_categ_pk, product_line_fk, name, description FROM apps.edw_item_prod_catg_lcv ORDER BY name;

Example — incremental extraction for a defined window:

• SELECT product_categ_pk, product_line_fk, last_update_date FROM apps.edw_item_prod_catg_lcv WHERE last_update_date > :last_run_date;

Example — grouping categories by their product line foreign key (including the INTR_TYPE segment):

• SELECT product_line_fk, COUNT(*) FROM apps.edw_item_prod_catg_lcv GROUP BY product_line_fk;

Because the base objects are documented as views, users should not assume they can be updated; this object is strictly read-only and should be queried only for reporting or ETL purposes.