Search Results hier_id




Overview
Explore in AI Chat

Overview

APPS.EDW_HIERARCHIES_MD_V is a reporting view in the Oracle E-Business Suite (EBS) data warehouse layer, exposed under the APPS schema in both release 12.1.1 and 12.2.2. The object presents hierarchy metadata — that is, the definitions and descriptive attributes of analytical hierarchies used by the Enterprise Data Warehouse (EDW) / ETRM (Enterprise Territory and Resource Management, the successor branding of the former Daily Business Intelligence hierarchy infrastructure). Rather than storing transactional data, the view supplies the dimensional scaffolding that reporting and analytics engines use to resolve territory, organization, and resource rollups.

Its primary role is to make hierarchy dimension identifiers, names, and prefixes available to BI Publisher reports, OBIEE / Oracle BI Applications extracts, and custom concurrent programs. Because the view is defined over a base metadata table, it acts as a stable, read-only projection that decouples downstream reporting from the physical table structure, allowing Oracle to evolve the underlying storage without breaking customer queries.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view text is a straight column projection over a single base object:

  • EDW_HIERARCHIES_MD — the physical metadata table holding one row per hierarchy dimension.

The view body is:

SELECT "DIM_ID", "DIM_NAME", "HIER_ID", "HIER_NAME",
       "HIER_PREFIX", "HIER_LONG_NAME"
FROM EDW_HIERARCHIES_MD

No additional joins, filters, or aggregation are applied. This means the view is a one-to-one alias of the table: each row in EDW_HIERARCHIES_MD appears exactly once. No base objects other than EDW_HIERARCHIES_MD are documented for this view.

Key Columns

  • DIM_ID — Numeric identifier of the dimension to which the hierarchy belongs (e.g., territory, organization, resource).
  • DIM_NAME — Descriptive name of that dimension.
  • HIER_ID — Numeric identifier for the specific hierarchy within the dimension.
  • HIER_NAME — Short name of the hierarchy, used in report prompts and column labels.
  • HIER_PREFIX — The column-prefix string applied to hierarchy level columns in associated fact/extract tables. This is the column most commonly queried, since applications need the prefix to construct dynamic SQL or to map logical levels to physical columns.
  • HIER_LONG_NAME — A verbose, human-readable hierarchy label suitable for display headings and parameter lists.

Common Use Cases and Queries

Typical scenarios include validating which hierarchies are loaded before running an extract, driving dynamic column-name construction in PL/SQL, and populating LOVs in custom reports.

List all hierarchies with their prefixes:

SELECT dim_name, hier_name, hier_prefix, hier_long_name
FROM   apps.edw_hierarchies_md_v
ORDER  BY dim_name, hier_name;

Resolve the prefix for a specific hierarchy by name:

SELECT hier_prefix
FROM   apps.edw_hierarchies_md_v
WHERE  hier_name = :p_hier_name;

Retrieve the long display name for a given HIER_ID:

SELECT hier_long_name
FROM   apps.edw_hierarchies_md_v
WHERE  hier_id = :p_hier_id;

Because the view performs no filtering, these queries execute with the same cost as querying EDW_HIERARCHIES_MD directly, and standard APPS schema grants apply.