Search Results child_territory_name

Overview

BIS_TERRITORY_HIERARCHIES_V is an APPS-owned database view within the BIS (Applications BIS) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents the parent–child relationships that define the sales territory hierarchy maintained by Oracle Territory Manager (also referred to as the Territory Management / ETRM engine). Each row represents one directed edge in the hierarchy: a parent territory linked to a child territory, together with the effective dating and audit columns required for reporting and integration.

The view is primarily a reporting and integration artifact. It denormalizes the territory hierarchy so that consumers do not need to resolve territory codes against a separate territory name lookup. Because it exposes resolved PARENT_TERRITORY_NAME and CHILD_TERRITORY_NAME values alongside the raw codes and types, it is well suited to ad hoc SQL, BI Publisher reports, OBIEE/Essbase extracts, custom concurrent programs, and inbound/outbound interface routines that must flatten or traverse the territory tree.

Underlying Base Objects

The view is defined over three documented referenced objects:

• BIS_TERRITORY_HIERARCHIES (SYNONYM) — aliased as BTH, this is the driving table holding the parent/child territory pairs, their types, active date ranges, and standard WHO audit columns.
• BIS_TERRITORIES_V (VIEW) — joined twice, as aliases BTV1 (parent side) and BTV2 (child side), to resolve each territory code to its descriptive name.
• FND_GLOBAL (PACKAGE) — the standard EBS global context package, referenced by the territory views to restrict rows to the effective operating unit / responsibility context.

The join predicates are strict two-key matches: BTH.PARENT_TERRITORY_CODE = BTV1.TERRITORY_CODE AND BTH.PARENT_TERRITORY_TYPE = BTV1.TYPE, with the identical pattern applied to the child side (BTV2). Because the territory type participates in the join, a territory code that exists under multiple types is resolved to the correct name. The view text also carries BTH.ROWID as ROW_ID to guarantee row uniqueness for tooling that requires a pseudo-key.

Key Columns

• ROW_ID — the underlying ROWID of the hierarchy row; useful as a surrogate identifier in reports and extracts.
• PARENT_TERRITORY_CODE / PARENT_TERRITORY_TYPE — the identifying code and type of the higher-level territory in the relationship.
• PARENT_TERRITORY_NAME — the descriptive name of the parent territory, resolved from BIS_TERRITORIES_V. This is the column users most commonly search for.
• CHILD_TERRITORY_CODE / CHILD_TERRITORY_TYPE — the code and type of the subordinate territory.
• CHILD_TERRITORY_NAME — the descriptive name of the child territory.
• START_DATE / END_DATE — the active date range of the parent–child association, aliased from START_DATE_ACTIVE and END_DATE_ACTIVE.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO audit columns.

Common Use Cases and Queries

Typical uses include validating hierarchy integrity, listing all children of a given territory, and driving recursive traversal to flatten multi-level trees.

• All direct children of a parent name:

  SELECT child_territory_code, child_territory_name, start_date, end_date
  FROM   apps.bis_territory_hierarchies_v
  WHERE  parent_territory_name = '&parent_name'
  AND    (end_date IS NULL OR end_date >= SYSDATE);

• Full hierarchy edge listing for extraction to a data warehouse, filtered on active associations.
• Recursive CONNECT BY PRIOR traversal from a root territory using child_territory_code = PRIOR parent_territory_code to produce a flattened tree.
• Orphan / integrity checks — detecting child codes that never appear as a parent, or associations whose date ranges do not overlap the parent’s own active range.

Because the view depends on FND_GLOBAL context for row filtering, queries should generally be executed under a responsibility or after setting the org context, to ensure results reflect the intended operating unit.