Results for “csp_rep_hierarchies_v”

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

Overview

CSP_REP_HIERARCHIES_V is a reporting view owned by the APPS schema within the CSP (Spares Management) product family of Oracle E-Business Suite. It is documented for both EBS 12.1.1 and 12.2.2 with a status of VALID and is described in the electronic technical reference manual as "Reporting Hierarchy Details." The view presents a denormalized, read-only projection of the reporting hierarchy structures used by Spares Management to organize depots, organizations, and inventory locations into parent-child relationships.

The view is not a stored object: it is a pass-through SELECT defined over the single base table CSP_REP_HIERARCHIES. Its primary purpose is to expose hierarchy node data — including the top node, parent node, and organization linkage — to reporting tools such as Oracle Reports, Discoverer, and BI Publisher, and to integration interfaces that need a stable, queryable name distinct from the underlying transactional table. Because the view is owned by APPS and references the base table through the APPS synonym, it is accessible to any responsibility whose data grants include APPS objects, without requiring direct grants on CSP_REP_HIERARCHIES.

Underlying Base Objects

The view is defined exclusively over one base object: the synonym CSP_REP_HIERARCHIES, which resolves to the CSP application table storing reporting hierarchy definitions. The view text is a straightforward column-by-column projection:

• SELECT B.ROWID, B.HIERARCHY_NODE_ID, … FROM CSP_REP_HIERARCHIES B

No joins, filters, or aggregations are applied. This means every row and column in the base table is visible through the view, including the audit columns and the fifteen descriptive flexfield attribute columns. The view's ROWID is the base table's physical row identifier, which allows direct row-level access and can be used in correlated updates or deletes executed by privileged processes. Because there is no WHERE clause, the view does not itself enforce organization-level or operating-unit security; any access control must be implemented through the calling form, concurrent program, or query predicate.

Key Columns

The following columns are most significant for query and integration purposes:

• HIERARCHY_NODE_ID — Unique identifier for each node in the reporting hierarchy.
• TOP_NODE_ID — Identifies the root node of the hierarchy to which the current node belongs. This is the column referenced in the user's search term and serves as the primary grouping key when reconstructing a full hierarchy from a flat node list.
• PARENT_NODE_ID — Identifies the immediate parent of the current node, enabling recursive traversal of the hierarchy.
• NODE_TYPE — Classifies the node (for example, root, intermediate, or leaf level) and controls how the node participates in reporting roll-ups.
• NODE_NAME — Descriptive label displayed in hierarchy reports and user interfaces.
• ORGANIZATION_ID — Links the hierarchy node to a specific inventory organization, supporting organization-scoped reporting.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — Standard EBS who-columns for change tracking.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield segments for customer-defined extensions.

Common Use Cases and Queries

The view supports hierarchy reconstruction, organization roll-up reporting, and integration extraction. A typical query retrieving all descendants of a given top node is:

• SELECT hierarchy_node_id, parent_node_id, node_name, node_type FROM csp_rep_hierarchies_v START WITH parent_node_id IS NULL CONNECT BY PRIOR hierarchy_node_id = parent_node_id;

To list every node belonging to a specific top node, the TOP_NODE_ID column provides a simple filter:

• SELECT hierarchy_node_id, node_name, organization_id FROM csp_rep_hierarchies_v WHERE top_node_id = :p_top_node_id ORDER BY node_name;

Finding the leaf organizations for a reporting roll-up uses NODE_TYPE combined with ORGANIZATION_ID, while integration interfaces frequently extract the audit columns and flexfield attributes to synchronize hierarchy definitions with external warehouse or planning systems. Because the view performs no filtering, queries should always include an appropriate predicate on TOP_NODE_ID or ORGANIZATION_ID to limit the result set and avoid full-table scans.