Results for “cz_ps_prop_vals”

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

Overview

CZ_PS_PROP_VALS is a Configurator (CZ) module table in the Oracle E-Business Suite database, owned by the CZ schema. Its documented description is "Project node property values," which identifies it as the repository for the property-value assignments attached to individual project nodes within an Oracle Configurator model. Oracle Configurator uses project structure nodes (PS nodes) to model the hierarchical structure of a configurable product or project, and properties are reusable attribute definitions applied to those nodes. CZ_PS_PROP_VALS stores the intersection of the two: the concrete value a given property holds on a given node. It also carries effectivity, soft-delete, and multi-source reference attributes, which indicates it participates in Configurator's effective-dated and import/export-aware persistence layer.

Based on the foreign key structure mined from the ETRM metadata, the table resolves as a link in a Data Vault modeling sense. It contains no natural hub semantics of its own; instead it connects the CZ_PROPERTIES property definitions to the CZ_PS_NODES project nodes it qualifies. The composite primary key reinforces this interpretation, since the key is composed entirely of the two foreign references rather than a lone surrogate identifier.

Key Information Stored

The table is documented with 16 columns. The most significant are:

• PROPERTY_ID — Foreign key to CZ_PROPERTIES; identifies which property definition the value belongs to.
• PS_NODE_ID — Foreign key to CZ_PS_NODES; identifies the project node carrying the property.
• DATA_VALUE — The character-based property value for the node.
• DATA_NUM_VALUE — A numeric representation of the value, used for numeric or range-oriented properties.
• EFF_FROM / EFF_TO — Effectivity start and end dates bounding the value's validity period.
• DELETED_FLAG — Soft-delete indicator allowing logical removal of a value without physical deletion.
• SECURITY_MASK / EFF_MASK — Masking columns used by Configurator's publication and security filtering mechanisms.
• ORIG_SYS_REF — Reference back to the originating source system, supporting import and cross-release propagation.
• CHECKOUT_USER — Indicates the user holding the record checked out for modification.
• CREATION_DATE / CREATED_BY / LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — Standard EBS audit columns recording creation and last-modification identity and timing.

The primary key CZ_PS_PROP_VALS_PK is defined on the column pair PROPERTY_ID and PS_NODE_ID. This is a composite surrogate key rather than a single business key; the table stores no independent unique identifier of its own, so uniqueness is enforced only by the combination of the two foreign keys.

Common Use Cases and Queries

The primary practical use of CZ_PS_PROP_VALS is reporting and diagnostics on configured models: determining what values a configuration carried at a point in time, which properties affect which nodes, and which values were imported from external systems. Typical query patterns join the table to both parents to render readable results:

• Values for a node: SELECT property_id, data_value FROM cz_ps_prop_vals WHERE ps_node_id = :node_id AND NVL(deleted_flag,'N') = 'N'.
• Value with property name: join CZ_PS_PROP_VALS to CZ_PROPERTIES on PROPERTY_ID to resolve the property's descriptive name alongside DATA_VALUE.
• Effective-dated lookup: filter on EFF_FROM and EFF_TO, for example WHERE :as_of BETWEEN NVL(eff_from, :as_of) AND NVL(eff_to, :as_of).
• Numeric reporting: use DATA_NUM_VALUE for aggregation and range checks where properties are numeric, since DATA_VALUE is stored as text.
• Source-system reconciliation: group by ORIG_SYS_REF to trace values propagated from external systems.

Because the table is a link with a composite key, de-duplication and existence checks should always be performed on the PROPERTY_ID plus PS_NODE_ID pair rather than on either column alone.

Related Objects

The following objects are the most significant dependencies, based on the documented foreign key relationships:

• CZ_PROPERTIES — Parent of the PROPERTY_ID column; defines the properties whose values are stored here.
• CZ_PS_NODES — Parent of the PS_NODE_ID column; defines the project structure nodes to which values are attached.
• CZ_PS_PROP_VALS_PK — The composite primary key constraint on PROPERTY_ID and PS_NODE_ID, which enforces one value row per node and property combination.
• CZ_PS_PROP_VALS foreign key constraints — The two documented constraints referencing CZ_PROPERTIES and CZ_PS_NODES.
• Configurator publication and import processes — Consume the SECURITY_MASK, EFF_MASK, ORIG_SYS_REF, and CHECKOUT_USER columns when moving configuration data between environments.

Together with its two parents, CZ_PS_PROP_VALS forms the core property-valuation triad in the Oracle Configurator data model, and it should be queried exclusively through joins on PROPERTY_ID and PS_NODE_ID to preserve referential accuracy.