Results for “bil_dimv_terr_hier”

13 results




Overview
Explore in AI Chat

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

Overview

BIL_DIMV_TERR_HIER is a read-only dimension view owned by the APPS schema within the Oracle E-Business Suite Sales Intelligence product family (BIL). Its documented purpose is to expose the sales territory hierarchy in a flattened, denormalized form suitable for dimensional reporting, data warehousing extracts, and integration with territory-based analytics. The view is registered as VALID in ETRM for both 12.1.1 and 12.2.2, and its structure is deliberately minimal — three columns only — so that it can serve as a lightweight hierarchy dimension rather than a full territory master.

Because the view is defined with the WITH READ ONLY clause, no DML is permitted against it. Consumers must treat it purely as a query surface. Its consistent three-column shape across releases makes it stable for use in BI Publisher reports, Discoverer worksheets, OBIEE/Essbase dimensions, and custom concurrent program extracts.

Underlying Base Objects

The view text is a UNION ALL of two branches:

No additional base objects are documented in the ETRM metadata beyond these two. The denormalized table already stores the flattened ancestor relationships, which is why the view does not need to recursively walk a parent-child structure at query time.

Key Columns

  • TERR_ID (NUMBER) — the territory identifier for the row being described. Every territory returned from JTF_TERR_DENORM appears as a TERR_ID, and the '-999' pseudo-row also appears as a TERR_ID.
  • PARENT_TERR_ID (NUMBER) — the parent territory identifier associated with the row. Because the source is denormalized, a single TERR_ID will typically appear multiple times, once for each level of its ancestry, with a different PARENT_TERR_ID each time. The '-999' row points to itself, providing a self-referencing root anchor.
  • IMMEDIATE_PARENT_FLAG (VARCHAR2) — indicates whether the PARENT_TERR_ID on this row is the immediate (direct) parent of TERR_ID. A value of 'Y' means the pair represents a direct parent-child link; any other value (including the 'N' emitted for the synthetic row) means the pair represents a more distant ancestor relationship.

The synthetic '-999 / -999 / N' row functions as a conventional "unknown" or "root" member for the hierarchy, allowing outer joins and level-based reporting to avoid orphaned territory rows.

Common Use Cases and Queries

Typical usage includes territory roll-up reporting, alignment to sales hierarchy dimensions, and reconstructing the immediate parent for a given territory. A direct-child lookup uses the IMMEDIATE_PARENT_FLAG filter:

  • SELECT TERR_ID, PARENT_TERR_ID FROM APPS.BIL_DIMV_TERR_HIER WHERE IMMEDIATE_PARENT_FLAG = 'Y' AND PARENT_TERR_ID = :p_territory_id; — returns the immediate children of a territory.
  • SELECT TERR_ID, PARENT_TERR_ID FROM APPS.BIL_DIMV_TERR_HIER WHERE TERR_ID = :p_territory_id; — returns all ancestors of a territory (one row per level).
  • SELECT DISTINCT PARENT_TERR_ID FROM APPS.BIL_DIMV_TERR_HIER WHERE PARENT_TERR_ID <> -999; — produces the distinct set of territories that act as parents anywhere in the hierarchy.

For building a full hierarchy extract for a data warehouse, a self-join on TERR_ID = PARENT_TERR_ID with the IMMEDIATE_PARENT_FLAG = 'Y' restriction yields the clean parent-child edge list, while the unfiltered view supplies closure-table style ancestry rows. Because the view is read-only and built on denormalized storage, queries are inexpensive relative to recursive CONNECT BY approaches over JTF_TERR_B, making it the preferred access path for territory hierarchy reporting in EBS 12.1.1 and 12.2.2.