Search Results itm_item_id

Overview

The view APPS.CZ_PSNODE_PROPVAL_V is a property-resolution view within the Oracle EBS product development / item management schema (the CZ_ family of objects). It presents a unified, flattened representation of property values that apply to product structure nodes (PS nodes). Property values in this model may originate from several different sources, and this view consolidates them into a single row-per-property-per-node layout while explicitly tagging the origin of each value.

Its principal role is reporting and integration. Rather than requiring the caller to join and reconcile item-type properties, item-level property values, and product-structure-level property overrides manually, the view resolves the effective value through a nested NVL/DECODE cascade — preferring an explicit PS node property value, then an item property value, and finally falling back to the property default. This makes it a convenient foundation for BOM/product-structure reporting, configuration, and downstream integrations that must know the effective value of a property and where that value came from.

Underlying Base Objects

The view is defined over six documented base objects, all referenced through APPS synonyms:

• CZ_PS_NODES — the product structure nodes, providing node identity, parent, name, type, and development project.
• CZ_PS_PROP_VALS — property values attached directly at the PS node level (the highest-priority source).
• CZ_ITEM_PROPERTY_VALUES — property values defined at the item level.
• CZ_ITEM_TYPE_PROPERTIES — property assignments at the item-type level, which also carry the inherited flag.
• CZ_PROPERTIES — the property definition (name, data type, default value, source application, originating system reference).
• CZ_ITEM_MASTERS — item master records that tie the node to an item.

The FROM clause joins CZ_PROPERTIES, CZ_ITEM_TYPE_PROPERTIES, CZ_ITEM_PROPERTY_VALUES, and CZ_PS_PROP_VALS against an inline subquery that produces the node/item key columns (the psn_ and itm_ aliases). This inline query is what exposes ITM_ITEM_ID as item_id in the outer projection.

Key Columns

• ps_node_id — surrogate identifier of the product structure node (psn_ps_node_id).
• parent_id — parent node, supporting structure hierarchies.
• ps_node_name, ps_node_type — the node name and type classification.
• item_id — the item master identifier (itm_item_id), the column most central to the user's search term. Also item_type_id from CZ_ITEM_TYPE_PROPERTIES.
• property_id, property_name, data_type — the property definition. data_type = 4 drives the numeric branch of the value-resolution logic.
• property_value / property_num_value — the resolved effective value (character or numeric), computed through the DECODE/NVL cascade.
• default_value / def_num_value — the property's own default.
• ValueSource — an explicit provenance tag: 'ItmTyp', 'ItemDflt', 'Item', 'PsDflt', or 'PsValue'.
• inherited_flag — '1' when the value is inherited from the item-type property, else '0'.
• prop_attaches — 'single' or 'overlapped', describing how many layers (PS, item-type, item) supply the property.
• orig_sys_ref, value_orig_sys_ref, property_orig_sys_ref — originating-system references for integration lineage.
• viewrev — revision literal ('2005-04-28').

Common Use Cases and Queries

Typical uses include effective-property reporting for a BOM node, auditing which layer supplies a value, and integration extraction filtered by the originating system reference.

SELECT ps_node_id,
       item_id,
       property_name,
       property_value,
       ValueSource,
       inherited_flag
  FROM apps.cz_psnode_propval_v
 WHERE item_id = :item_id
 ORDER BY ps_node_id, property_name;

SELECT property_name, ValueSource, COUNT(*)
  FROM apps.cz_psnode_propval_v
 WHERE ps_node_id = :node_id
 GROUP BY property_name, ValueSource;

To find only overridden node-level values:

SELECT ps_node_id, item_id, property_name, property_value
  FROM apps.cz_psnode_propval_v
 WHERE ValueSource = 'PsValue';