Search Results parent_measure_code

Overview

BIC_MEASURE_HIERARCHY is a table owned by the BIC schema within Oracle E-Business Suite, introduced by the Customer Intelligence (BIC) product family. The BIC module is documented as obsolete, meaning the object persists in the database primarily for backward compatibility and historical reporting rather than for active functional use in Oracle EBS 12.1.1 or 12.2.2. The table defines a hierarchy of measures, relating sub-measures to parent (main) measures, and therefore functions as the structural backbone for organizing analytical metrics into multi-level groupings.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure suggests this object behaves as a link. Its role is to associate existing measure definitions: it connects a child measure to its parent measure and resolves those references against the BIC_MEASURE_ATTRIBS attribute table. The table is not a transactional fact table; it is a lightweight relationship construct that enables parent-child traversal across measures.

Key Information Stored

The table contains 11 documented columns. The most significant columns and their roles are as follows:

• PARENT_MEASURE_CODE — Identifies the parent (main) measure in the hierarchy. Together with MEASURE_CODE it forms the composite primary key BIC_MEASURE_HIERARCHY_PK.
• MEASURE_CODE — Identifies the sub-measure or child measure being related to its parent.
• SEQUENCE_NUMBER — Controls the ordering of child measures beneath the same parent.
• OPERATION_CODE — Encodes the operation or aggregation semantics associated with the hierarchy relationship.
• SECURITY_GROUP_ID — Enables multi-tenant or multi-organization data segregation, with a foreign key to FND_SECURITY_GROUPS.
• ZD_EDITION_NAME — The editioning column supporting Oracle EBS online patching (Edition-Based Redefinition) in 12.2.x.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns capturing who changed the hierarchy relationship and when.
• CREATION_DATE, CREATED_BY — Audit columns identifying initial creation of the relationship row.

The composite primary key BIC_MEASURE_HIERARCHY_PK (PARENT_MEASURE_CODE, MEASURE_CODE) acts as the surrogate/business key for the relationship. A unique index, BIC_MEASURE_HIERARCHY_U1 (MEASURE_CODE, PARENT_MEASURE_CODE, ZD_EDITION_NAME), serves as the edition-aware business-key candidate, ensuring that a child measure cannot be mapped to the same parent more than once within an edition.

Common Use Cases and Queries

Typical usage involves resolving the measure hierarchy for reporting, drilling down from a main measure to its sub-measures, or reconstructing parent paths. A representative query joining the hierarchy to measure attributes is:

SELECT h.PARENT_MEASURE_CODE, h.MEASURE_CODE, h.SEQUENCE_NUMBER, a.MEASURE_NAME
FROM BIC.BIC_MEASURE_HIERARCHY h, BIC.BIC_MEASURE_ATTRIBS a
WHERE h.MEASURE_CODE = a.MEASURE_CODE
AND h.PARENT_MEASURE_CODE = a.MEASURE_CODE
ORDER BY h.PARENT_MEASURE_CODE, h.SEQUENCE_NUMBER;

Analysts may also filter by SECURITY_GROUP_ID to restrict results to a given security domain, or use recursive hierarchical queries to expand an entire measure tree. Because the module is obsolete, such queries are most often executed for data migration, archival extraction, or historical reconciliation rather than for live customer-intelligence dashboards.

Related Objects

The following objects are most significant to BIC_MEASURE_HIERARCHY, based on documented relationships:

• BIC_MEASURE_ATTRIBS — Referenced twice: on MEASURE_CODE and on PARENT_MEASURE_CODE. This is the primary lookup table supplying measure definitions.
• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID to enforce security partitioning.
• BIC_MEASURE_HIERARCHY_PK — The composite primary key constraint (PARENT_MEASURE_CODE, MEASURE_CODE).
• BIC_MEASURE_HIERARCHY_U1 — The unique, edition-aware index on (MEASURE_CODE, PARENT_MEASURE_CODE, ZD_EDITION_NAME).

Together these objects support the definition, ordering, and security scoping of measure hierarchies within the legacy BIC Customer Intelligence schema.