Search Results csp_rep_hierarchies




Overview
Explore in AI Chat

Overview

The CSP_REP_HIERARCHIES table is a core data structure within the Oracle E-Business Suite Spares Management (CSP) module, present in both the 12.1.1 and 12.2.2 releases. It serves as the master repository for defining and storing reporting hierarchies. These hierarchical structures are essential for organizing and aggregating spares-related data—such as inventory, supply, demand, and usage—across different organizational levels for analytical and operational reporting. The table's self-referential design, evidenced by its foreign keys to itself, indicates its purpose in modeling parent-child relationships, enabling the creation of multi-level, tree-like reporting frameworks that can mirror organizational, geographical, or functional groupings.

Key Information Stored

While the provided metadata does not list all columns, the primary and foreign key relationships reveal the critical structural elements of the hierarchy. The primary key, HIERARCHY_NODE_ID, uniquely identifies each node within a hierarchy. The PARENT_NODE_ID column references the HIERARCHY_NODE_ID of a node's immediate parent, establishing the hierarchical relationship. The TOP_NODE_ID column references the HIERARCHY_NODE_ID of the root node for the entire hierarchy, allowing for efficient traversal and aggregation from any node to the top. Typical columns in such a table would also include attributes like NODE_NAME, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, and SEQUENCE_NUMBER to manage the display and validity of nodes.

Common Use Cases and Queries

The primary use case is to roll up transactional data for spares management reporting. For instance, usage histories or inventory levels recorded at a specific node can be aggregated to a regional or global level for executive dashboards. A common query pattern involves hierarchical SQL (using CONNECT BY or recursive WITH clauses) to fetch a complete tree or subtree. For example, to find all descendant nodes under a specific top node for a roll-up report:

  • SELECT hierarchy_node_id FROM csp_rep_hierarchies START WITH hierarchy_node_id = :p_top_node_id CONNECT BY PRIOR hierarchy_node_id = parent_node_id;

Another critical use is data validation and hierarchy maintenance, ensuring no circular references exist and that all nodes correctly link to a valid top node. Reporting integrations often join this table to transactional tables like CSP_USAGE_HISTORIES to summarize data by hierarchy level.

Related Objects

The CSP_REP_HIERARCHIES table is central to the Spares Management data model, with several key relationships documented in the ETRM.

  • Primary Key: CSP_REP_HIERARCHIES_PK on HIERARCHY_NODE_ID.
  • Self-Referencing Foreign Keys (This table references itself):
    • CSP_REP_HIERARCHIES.TOP_NODE_ID references CSP_REP_HIERARCHIES(HIERARCHY_NODE_ID)
    • CSP_REP_HIERARCHIES.PARENT_NODE_ID references CSP_REP_HIERARCHIES(HIERARCHY_NODE_ID)
  • Foreign Keys (Other tables reference this table):
    • CSP_CURR_SUP_DEM_SUMS.HIERARCHY_NODE_ID references this table, linking hierarchy nodes to current supply and demand summary data.
    • CSP_SEC_INVENTORIES.HIERARCHY_NODE_ID references this table, associating secondary inventory organizations with a reporting node.
    • CSP_USAGE_HISTORIES.HIERARCHY_NODE_ID references this table, categorizing historical parts usage within the hierarchy.