Search Results cz_psnode_propval_v




Overview
Explore in AI Chat

Overview

CZ_PSNODE_PROPVAL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the CZ (Configurator) product family. As documented in the ETRM repository, its stated purpose is to list all properties and values associated with PS nodes (property-set nodes). The view was last revised on 2005-04-28, as recorded in the literal VIEWREV column embedded in its defining SQL, and it remains marked VALID in the data dictionary across both 12.1.1 and 12.2.2 releases.

Functionally, the view flattens the Configurator property model into a single row-per-property-per-node projection. It resolves each property value from three potential origins — an explicit property-set value, an item-level value, or the property default — and labels the winning source through the VALUESOURCE column. This makes the view a convenient single point of access for reporting on configuration property assignments without replicating the multi-table NVL/DECODE resolution logic in custom SQL.

Underlying Base Objects

The ETRM metadata documents six referenced base objects, all accessed through APPS synonyms: CZ_PROPERTIES, CZ_ITEM_TYPE_PROPERTIES, CZ_ITEM_PROPERTY_VALUES, CZ_PS_PROP_VALS, CZ_PS_NODES, and CZ_ITEM_MASTERS. In the view text these appear as the aliases PROP, ITP, IPV, PSP, and an inline PS-node subquery built from CZ_PS_NODES, with ITM_ITEM_ID linking to the item context via CZ_ITEM_MASTERS.

CZ_PROPERTIES supplies the property definitions (NAME, DATA_TYPE, SRC_APPLICATION_ID, DEF_VALUE, DEF_NUM_VALUE, ORIG_SYS_REF). CZ_PS_PROP_VALS stores property-set-specific overrides. CZ_ITEM_PROPERTY_VALUES and CZ_ITEM_TYPE_PROPERTIES provide item-instance and item-type-level values respectively. CZ_PS_NODES provides the PS node identity, parent hierarchy, node name, node type, and development project reference. Because the value resolution is expressed through DECODE against PSP.PROPERTY_ID and IPV.PROPERTY_ID, the view performs an implicit outer-join style resolution rather than an INNER JOIN across the value sources.

Key Columns

  • PS_NODE_ID, PARENT_ID, PS_NODE_NAME, PS_NODE_TYPE — identity, hierarchy, and classification of the property-set node.
  • PROPERTY_ID, PROPERTY_NAME, DATA_TYPE — the property definition being reported; DATA_TYPE = 4 signals a numeric property, which drives the dual DECODE logic in the VIEWSOURCE expression.
  • PROPERTY_VALUE / PROPERTY_NUM_VALUE — the resolved value, applying precedence PSP over IPV over the property default (DEF_VALUE / DEF_NUM_VALUE).
  • VALUESOURCE — the origin of the resolved value: PSVALUE or PSDFLT for property-set sources, ITEM or ITEMDFLT for item-level sources, and ITMTYP when the item-type definition wins.
  • PROP_ATTACHES — SINGLE or OVERLAPPED, derived by counting how many of PSP, ITP, and IPV contribute a value for the property on the node.
  • INHERITED_FLAG — '1' where an item-type property entry exists (ITP.PROPERTY_ID not null), otherwise '0'.
  • ITEM_ID, ITEM_TYPE_ID — the item and item-type context associated with the property row.
  • ORIG_SYS_REF, VALUE_ORIG_SYS_REF, PROPERTY_ORIG_SYS_REF — cross-system origin references for the node value, the resolved value, and the property definition, used in distributed/imported configuration data.
  • DEVL_PROJECT_ID, VIEWREV, DEFAULT_VALUE, DEF_NUM_VALUE — development project association, the fixed 2005-04-28 view revision marker, and the raw property defaults.

Common Use Cases and Queries

The view is typically used to audit which property values are explicitly set on a PS node versus inherited from the item type or defaulted, and to verify cross-system origin data after configuration imports.

-- All properties for a given PS node
SELECT ps_node_id, parent_id, ps_node_name, property_name,
       data_type, property_value, valuesource, prop_attaches
FROM   apps.cz_psnode_propval_v
WHERE  ps_node_id = :ps_node_id
ORDER  BY property_name;

-- Properties whose value resolves to the property default
SELECT ps_node_name, property_name, property_value
FROM   apps.cz_psnode_propval_v
WHERE  valuesource IN ('PSDFLT','ITEMDFLT','ITMTYP')
AND    ps_node_type = :node_type;

-- Numeric properties with inherited item-type definitions
SELECT ps_node_id, property_name, property_num_value, orig_sys_ref
FROM   apps.cz_psnode_propval_v
WHERE  data_type = 4
AND    inherited_flag = '1';

-- Overlapping attachments requiring review
SELECT ps_node_id, property_name, prop_attaches
FROM   apps.cz_psnode_propval_v
WHERE  prop_attaches = 'OVERLAPPED';

Because the view exposes no direct ORG_ID or operating-unit column, queries should be scoped through the PS node or item context. Standard EBS security and MOAC profiles on the underlying tables continue to apply; the view itself is a definer-rights object under APPS and should be queried with that in mind.