Results for “csp_rep_hierarchies”

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

Overview

CSP_REP_HIERARCHIES is a core table within the CSP (Spares Management) module of Oracle E-Business Suite, available in both the 12.1.1 and 12.2.2 releases. The table stores reporting hierarchies — the organizational or structural trees used to aggregate, roll up, and present spare parts supply and demand data across the enterprise. In the context of the Spares Management product, these hierarchies typically represent depot, inventory organization, or customer-service reporting structures that allow planners and analysts to summarize usage, supply, and demand metrics at multiple levels of granularity.

From a data-modeling perspective, the ETRM metadata classifies CSP_REP_HIERARCHIES heuristically as a hub. This classification is a modeling suggestion: the table functions as a central node that other CSP tables reference by way of the HIERARCHY_NODE_ID surrogate key. Because the table also contains self-referencing foreign keys to TOP_NODE_ID and PARENT_NODE_ID, it additionally plays the role of the adjacency structure that defines the tree itself, combining hub-like identity semantics with hierarchical parent-child linkage.

Key Information Stored

The table is owned by the CSP schema and contains 28 documented columns. The most significant of these are:

• HIERARCHY_NODE_ID — the surrogate primary key, enforced by CSP_REP_HIERARCHIES_PK. It uniquely identifies each node in a reporting hierarchy and is the column referenced by dependent tables.
• NODE_NAME — the business-recognizable name of the node. It is protected by the unique index CSP_REP_HIERARCHIES_U2, making it a business-key candidate.
• NODE_TYPE — indicates the category or level of the node (for example, root versus leaf, or organization versus grouping), driving how the hierarchy is interpreted during reporting.
• TOP_NODE_ID — self-referencing foreign key identifying the root node of the hierarchy to which the current row belongs.
• PARENT_NODE_ID — self-referencing foreign key identifying the immediate parent of the node, forming the adjacency list for tree traversal.
• ORGANIZATION_ID — ties the node to an inventory organization, restricting hierarchy content by operating unit or warehouse.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant or data-security partitioning of reporting structures.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive-flexfield columns, reserved for site-specific extensions without schema modification.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO-audit columns recording row creation and modification provenance.

The unique index CSP_REP_HIERARCHIES_U1 (HIERARCHY_NODE_ID) reinforces the primary key, while CSP_REP_HIERARCHIES_U2 (NODE_NAME) establishes the business-key candidate that prevents duplicate node names.

Common Use Cases and Queries

Typical uses of CSP_REP_HIERARCHIES include building rollup reports for spare-parts usage and supply/demand summaries, resolving drill-down navigation from a top node to its descendants, and enforcing security scoping so that users see only their organization's nodes. A representative subtree query uses CONNECT BY on the self-referencing join:

• SELECT hierarchy_node_id, node_name, parent_node_id FROM csp.csp_rep_hierarchies START WITH top_node_id = :top CONNECT BY PRIOR hierarchy_node_id = parent_node_id;
• Joining to dependent facts, for example: SELECT h.node_name, s.* FROM csp.csp_rep_hierarchies h, csp.csp_curr_sup_dem_sums s WHERE h.hierarchy_node_id = s.hierarchy_node_id;
• Filtering by organization for a specific warehouse's reporting tree: WHERE organization_id = :org_id.

Reports frequently aggregate the sums and usage-history tables by NODE_NAME or NODE_TYPE after resolving the hierarchy, enabling management-level views of spares performance.

Related Objects

Several CSP tables depend on CSP_REP_HIERARCHIES through the HIERARCHY_NODE_ID foreign key, and the table also references itself:

• CSP_CURR_SUP_DEM_SUMS.HIERARCHY_NODE_ID — links current supply and demand summary rows to their reporting node.
• CSP_SEC_INVENTORIES.HIERARCHY_NODE_ID — associates secured inventory records with hierarchy nodes.
• CSP_USAGE_HISTORIES.HIERARCHY_NODE_ID — ties historical usage transactions to reporting nodes.
• CSP_REP_HIERARCHIES.TOP_NODE_ID and PARENT_NODE_ID — self-referencing joins that define the root and parent traversal of the tree.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, governing access to hierarchy definitions.

Together these relationships make CSP_REP_HIERARCHIES the structural backbone for aggregation and security in Spares Management reporting.