Results for “cz_ps_nodes_u3”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CZ.CZ_PS_NODES table is a core structure within the Oracle E-Business Suite configurator and product model schema, owned by the CZ (Configurator) module. It stores the complete hierarchical structure of a product model, functioning as the repository for every node that defines a configurable product, feature, option, or component. Each product model project has a root product node, and subordinate nodes mirror the parent-child relationships inherited from an imported Oracle Bill of Materials structure. Nodes of type REFERENCE allow a separate project (model) to be embedded into another project tree, enabling modular reuse of product definitions across models.

Physically, the table resides in the APPS_TS_SEED tablespace with a PCT Free of 10, and contains 91 documented columns in the ETRM 12.2.2 schema. Under a heuristic Data Vault classification, CZ_PS_NODES is modeled as a hub, reflecting its role as the central anchor for configuration node identity around which many dependent satellites and link-style associations revolve. This classification is a modeling suggestion, not a native EBS designation.

Key Information Stored

The table’s structure separates surrogate identification from business-key candidates and descriptive attributes.

• PS_NODE_ID — surrogate primary key, defined by the CZ_PS_NODES_PK unique index. Every node in a model is uniquely identified by this column.
• Business-key candidates — the unique indexes 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) establish the meaningful uniqueness of nodes within a project tree.
• PARENT_ID — self-referencing foreign key that defines the hierarchical parent of each node.
• COMPONENT_ID — associates a node with the component it represents.
• DEVL_PROJECT_ID — identifies the development project (product model) that owns the node.
• ITEM_ID and COMPONENT_SEQUENCE_ID — link nodes to inventory items and imported BOM component sequences.
• PS_NODE_TYPE and REFERENCE_ID — distinguish node types, including REFERENCE nodes that pull in a separate model via CZ_DEVL_PROJECTS.
• NAME, DISPLAYNAME_TEXT_ID, NOTES_TEXT_ID — descriptive identity attributes presented to users.
• DELETED_FLAG — logical delete indicator used across nearly every unique and non-unique index.
• TREE_SEQ — ordering sequence for nodes within the tree.
• PERSISTENT_NODE_ID — stable identifier preserved across structure revisions.
• MINIMUM and MAXIMUM (with MINIMUM_SELECTED/MAXIMUM_SELECTED) — selection constraints applied to optional and feature nodes.
• EFFECTIVITY_SET_ID and EFF_FROM/EFF_TO — date and set effectivity governing node validity.
• VIRTUAL_FLAG and UI_OMIT — flags controlling whether a node is virtual or suppressed from the user interface.

Common Use Cases and Queries

Typical usage centers on traversing and reporting on model structure. A hierarchical query resolving the tree rooted at a project uses the self-referencing PARENT_ID:

• Model tree traversal — SELECT PS_NODE_ID, PARENT_ID, NAME, PS_NODE_TYPE FROM CZ.CZ_PS_NODES WHERE DEVL_PROJECT_ID = :project_id AND DELETED_FLAG = 'N' START WITH PARENT_ID IS NULL CONNECT BY PRIOR PS_NODE_ID = PARENT_ID;
• Active node counts by type — aggregating PS_NODE_TYPE to profile feature, option, and component composition per project.
• Reference resolution — joining REFERENCE_ID to CZ_DEVL_PROJECTS to identify models embedded into another model tree.
• Item linkage reporting — joining ITEM_ID to CZ_ITEM_MASTERS to reconcile model nodes against inventory items.
• BOM alignment — using COMPONENT_SEQUENCE_ID to trace nodes back to imported Oracle Bills of Materials components.

All reporting should filter on DELETED_FLAG to exclude logically removed nodes and observe effectivity columns where date-driven model versions are in scope.

Related Objects

CZ_PS_NODES is heavily referenced across the configurator schema. The most significant dependent objects include:

• CZ_PS_NODES (self) — PARENT_ID references PS_NODE_ID for hierarchy.
• CZ_DEVL_PROJECTS — referenced via DEVL_PROJECT_ID and REFERENCE_ID, defining the owning and embedded models.
• CZ_ITEM_MASTERS — referenced via ITEM_ID, linking nodes to items.
• CZ_CONFIG_HDRS (COMPONENT_ID), CZ_CONFIG_ITEMS (PS_NODE_ID), and CZ_CONFIG_INPUTS (PS_NODE_ID) — configuration runtime and input records per node.
• CZ_RULES (COMPONENT_ID) and CZ_EXPRESSION_NODES (PS_NODE_ID) — rule and expression logic attached to nodes.
• CZ_UI_DEFS (COMPONENT_ID) and CZ_UI_NODES (PS_NODE_ID, CONTAINER_ID, COMPONENT_ID) — user interface layout and container definitions.
• CZ_PRICING_STRUCTURES (PS_NODE_ID) — pricing associations.
• CZ_PS_PROP_VALS (PS_NODE_ID) and CZ_PSNODE_PROPCOMPAT_GENS (PS_NODE_ID) — property values and property compatibility generation.
• CZ_IMP_PS_NODES (PS_NODE_ID) — import staging records, relevant when the searched index CZ_PS_NODES_U2 is exercised during structure import and validation.