Search Results product_flag




Overview
Explore in AI Chat

Overview

CZ_PS_NODES is the core structure table for the Oracle Configurator (CZ) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the nodes that make up a configuration model's project structure — the hierarchical tree of components, features, options, and subassemblies that define what a configurable product can contain. Every model definition authored in Oracle Configurator Developer (CZ_DEVL_PROJECTS) is ultimately decomposed into a set of rows in this table, where each row represents a single node in the model's structure and holds the node's type, ordering, quantity constraints, BOM treatment, and pointers to related definitions.

From a dimensional modeling perspective, the FK structure suggests classifying CZ_PS_NODES as a hub. It carries a natural surrogate key (PS_NODE_ID) and is referenced by an unusually large number of dependent tables — configuration headers, items, inputs, messages, UI nodes, rules, pricing structures, and expression nodes all point back to this table. The self-referencing foreign key on PARENT_ID reinforces its role as the spine of a hierarchy rather than a transactional fact.

Key Information Stored

The primary key is PS_NODE_ID, defined through CZ_PS_NODES_PK. Three additional unique indexes act as business-key candidates that combine DELETED_FLAG with structural columns: CZ_PS_NODES_U2 (DELETED_FLAG, PS_NODE_ID, PARENT_ID, COMPONENT_ID), CZ_PS_NODES_U3 (DELETED_FLAG, COMPONENT_ID, PERSISTENT_NODE_ID, PARENT_ID, PS_NODE_ID), and CZ_PS_NODES_U4 (DELETED_FLAG, PARENT_ID, PS_NODE_ID, TREE_SEQ). These indexes indicate that a node is uniquely identified within its parent and tree position once deleted rows are excluded. The most consequential columns include:

Common Use Cases and Queries

The most frequent query pattern is hierarchy traversal — resolving a node's children or its ancestors. A typical reporting query joins CZ_PS_NODES to CZ_DEVL_PROJECTS and filters on DELETED_FLAG = 'N' to list active nodes per model. Recursive CTEs (CONNECT BY in Oracle EBS) are used to expand a parent node into its full subtree, and to compute depth for documentation or impact analysis.

  • Enumerating all nodes for a project: SELECT PS_NODE_ID, NAME, PS_NODE_TYPE, PARENT_ID, TREE_SEQ FROM CZ_PS_NODES WHERE DEVL_PROJECT_ID = :project_id AND DELETED_FLAG = 'N' ORDER BY PARENT_ID, TREE_SEQ;
  • Walking the hierarchy: SELECT PS_NODE_ID, PARENT_ID, LEVEL FROM CZ_PS_NODES START WITH PS_NODE_ID = :root CONNECT BY PRIOR PS_NODE_ID = PARENT_ID;
  • Finding nodes that reference a specific item: join CZ_PS_NODES.ITEM_ID to CZ_ITEM_MASTERS.ITEM_ID.
  • Auditing BOM-relevant nodes: filter BOM_TREATMENT and BOM_REQUIRED_FLAG for items surfaced to manufacturing.
  • Extracting effectivity-bound nodes by joining to CZ_EFFECTIVITY_SETS on EFFECTIVITY_SET_ID.

Related Objects

CZ_PS_NODES sits at the center of the Configurator schema. The most significant related objects, with their documented join columns, are:

Together these relationships confirm CZ_PS_NODES as the structural hub of Oracle Configurator model data, and any extraction, migration, or reporting effort targeting configuration models must anchor on its primary key and its self-referencing hierarchy.