Search Results cz_pricing_structures_u1

Overview

CZ.CZ_PRICING_STRUCTURES is a transient staging table owned by the CZ schema that bridges the Oracle Configurator engine and the Oracle Pricing module. Its purpose is twofold: it communicates the configuration and item information required by the pricing engine for a configured model, and it stores the priced results returned by that engine for subsequent display in the Configurator user interface. Each row represents a single pricing request record tied to an active configuration session.

The table resides in the APPS_TS_ARCHIVE tablespace, which reflects its role as a working or archival staging store rather than a high-throughput transactional entity. Under the heuristic Data Vault classification mined from its foreign key structure, this object is satellite-leaning. In a Data Vault model, it would most naturally be treated as a satellite hanging off the configuration session and the configuration item, meaning it captures descriptive, time-bound attributes (prices, quantities, messages) surrounding those business keys rather than defining relationships between hubs.

Key Information Stored

The table documents twelve columns. The most consequential are listed below, with surrogate and business-key designations noted where the metadata provides them.

• CONFIGURATOR_SESSION_KEY (VARCHAR2, 50) — Groups all pricing request records belonging to a single Configurator session. This is the leading column of both unique indexes.
• SEQ_NBR — Provides ordering across pricing records and forms the second component of the surrogate primary key CZ_PRICING_STRUCTURES_PK (CONFIGURATOR_SESSION_KEY, SEQ_NBR).
• CONFIG_ITEM_ID — Associates a pricing record with a specific configuration item; combined with CONFIGURATOR_SESSION_KEY it forms the second unique business key, CZ_PRICING_STRUCTURES_U1.
• PS_NODE_ID — Associates the row with a Model structure node (psnode); this is the foreign key to CZ_PS_NODES.
• PARENT_CONFIG_ITEM_ID — Identifies the parent configuration item, allowing hierarchical reconstruction of a configured BOM.
• ITEM_KEY (VARCHAR2, 2000) — A string identifier for the item to be priced, whose format is governed by ITEM_KEY_TYPE.
• ITEM_KEY_TYPE — Numeric code: value 1 indicates the 'orig_sys_ref' format (CC:EX:ORG:ITEM), value 2 indicates a ps_node_id reference.
• QUANTITY and UOM_CODE — The quantity requested and its unit of measure, typically "Ea".
• LIST_PRICE — The list price applicable to the item in this record.
• SELLING_PRICE — The calculated selling price returned for the requested item and quantity.
• MSG_DATA (VARCHAR2, 2000) — Diagnostic and status information returned from the pricing engine; per documentation it is generally unused.

Note that CONFIGURATOR_SESSION_KEY and SEQ_NBR constitute the surrogate primary key, while the same session key paired with CONFIG_ITEM_ID is the unique business-key candidate.

Common Use Cases and Queries

Typical usage revolves around retrieving priced configuration output for a given session, auditing the relationship between requested and returned prices, and diagnosing the messages returned by the pricing engine.

• Retrieve all pricing lines for a session, ordered by the natural sequence:

  SELECT config_item_id, ps_node_id, item_key, quantity, list_price, selling_price
  FROM cz.cz_pricing_structures
  WHERE configurator_session_key = :session_key
  ORDER BY seq_nbr;

• Reconcile the business key by joining against configuration items via CONFIG_ITEM_ID and CONFIGURATOR_SESSION_KEY when validating the unique index U1.
• Compare LIST_PRICE and SELLING_PRICE to report discounts or price overrides applied by the pricing engine.
• Trace model structure by walking PARENT_CONFIG_ITEM_ID or by joining PS_NODE_ID to CZ_PS_NODES.
• Inspect MSG_DATA when returns appear incomplete, though the column is documented as generally unused.

Because the table is session-scoped, most reporting filters on CONFIGURATOR_SESSION_KEY and often on a small set of CONFIG_ITEM_ID values.

Related Objects

The most significant related object is the parent entity of the primary foreign key relationship:

• CZ.CZ_PS_NODES — Referenced through CZ_PRICING_STRUCTURES.PS_NODE_ID, providing the model structure node definition for each pricing record.
• CZ_PRICING_STRUCTURES_PK and CZ_PRICING_STRUCTURES_U1 — The unique indexes on (CONFIGURATOR_SESSION_KEY, SEQ_NBR) and (CONFIGURATOR_SESSION_KEY, CONFIG_ITEM_ID), which enforce the table's business identity and support retrieval by session.
• The Oracle Configurator UI layer, which reads SELLING_PRICE, LIST_PRICE, and MSG_DATA to display priced results.
• The Oracle Pricing engine, which consumes the request rows and writes back computed selling prices.
• Configuration item data referenced by CONFIG_ITEM_ID and PARENT_CONFIG_ITEM_ID, used to reconstruct the configured item hierarchy.

Consumers of this table should be aware that ownership is restricted to the CZ schema and that all documented access flows through the Configurator and Pricing integration rather than through direct application transactions.