Results for “ozf_terr_levels_all_u1”

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

Overview

OZF.OZF_TERR_LEVELS_ALL is an Oracle E-Business Suite table that belongs to the Oracle Trade Management and Territory Management (ETRM) schema, OZF. It stores the levels that correspond to territory types in JTF after the territory concurrent program has been run. In practical terms, the table materializes the hierarchical structure of territories, capturing each territory's position, depth, parent relationship, and associated hierarchy at a given point in time. It is a transactional data table housed in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes reside in APPS_TS_TX_IDX. The object carries a VALID status and is registered under FND Design Data as OZF.OZF_TERR_LEVELS_ALL.

Because the table records dependencies between territories and their parent-child relationships within hierarchy structures, it functions as an associative structure linking territories, territory types, and hierarchies. A reasonable Data Vault modeling suggestion is to treat it as a link table, since its core columns—TERRITORY_ID, PARENT_TERRITORY_ID, HEIRARCHY_ID, and TERR_TYPE_ID—express relationships among business entities rather than descriptive attributes. This classification is heuristic and should be validated against the intended analytical model.

Key Information Stored

The primary surrogate key is TERR_LEVEL_ID, a NUMBER column that serves as the unique identifier and is enforced by the unique index OZF_TERR_LEVELS_ALL_U1. This index is the documented business-key candidate for the table. Note that the physical primary key constraint is named AMS_TERR_LEVELS_ALL_PK, reflecting the table's lineage in the AMS (Marketing) module.

• TERR_LEVEL_ID — Surrogate unique identifier for each territory level record.
• TERRITORY_ID — Identifies the territory to which the level belongs; indexed non-uniquely via OZF_TERR_LEVELS_ALL_N2.
• PARENT_TERRITORY_ID — References the parent territory, enabling reconstruction of the territory tree.
• TERR_TYPE_ID — Foreign key to JTF_TERR_TYPES_ALL; indexed non-uniquely via OZF_TERR_LEVELS_ALL_N3.
• HEIRARCHY_ID — Identifies the hierarchy containing the territory; indexed non-uniquely via OZF_TERR_LEVELS_ALL_N1.
• HIERARCHY_NAME — Descriptive name of the hierarchy.
• LEVEL_DEPTH — Depth of the territory measured from the root territory.
• ORG_ID — Operating unit identifier supporting multi-org access control.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS controlling record visibility.
• ENABLED_FLAG and ACTIVE_FLAG — Status indicators governing whether the level is enabled or currently active.
• END_DATE_ACTIVE — Date on which the level ceases to be active.
• OBJECT_VERSION_NUMBER — Optimistic locking identifier used by HTML-based screens.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard WHO audit columns.
• CONTEXT, ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield columns for customer-defined extensions.
• PROGRAM_ID, REQUEST_ID, PROGRAM_UPDATE_DATE — Concurrent program tracking columns showing which request populated the row.

Common Use Cases and Queries

The most frequent use case is reconstructing the territory hierarchy for reporting or for diagnosing the output of the territory concurrent program. A recursive query is commonly used to traverse the tree:

• Reconstruct hierarchy from root: SELECT TERRITORY_ID, PARENT_TERRITORY_ID, LEVEL_DEPTH FROM OZF_TERR_LEVELS_ALL WHERE HEIRARCHY_ID = :hierarchy_id ORDER BY LEVEL_DEPTH.
• Find children of a given territory: SELECT TERRITORY_ID FROM OZF_TERR_LEVELS_ALL WHERE PARENT_TERRITORY_ID = :territory_id.
• Filter active levels for a hierarchy and operating unit: SELECT * FROM OZF_TERR_LEVELS_ALL WHERE ORG_ID = :org_id AND ACTIVE_FLAG = 'Y' AND END_DATE_ACTIVE IS NULL.
• Enumerate territory types in use: SELECT DISTINCT TERR_TYPE_ID FROM OZF_TERR_LEVELS_ALL.

Reporting scenarios include validating that the concurrent program produced a complete tree, reconciling territory counts by type, and auditing levels by enabled status or end date. Because SECURITY_GROUP_ID and ORG_ID are present, queries must observe multi-org and security group constraints to avoid data leakage across organizations.

Related Objects

The following objects are the most significant related to OZF_TERR_LEVELS_ALL, based on the documented foreign key relationships:

• JTF.JTF_TERR_TYPES_ALL — Referenced by TERR_TYPE_ID; defines the territory types that the levels correspond to.
• FND.FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID; governs record-level security.
• AMS_TERR_LEVELS_ALL_PK — The physical primary key constraint on TERR_LEVEL_ID, indicating lineage to the AMS territory tables.
• OZF_TERR_LEVELS_ALL_U1 — Unique index on TERR_LEVEL_ID, the documented business-key candidate.
• OZF_TERR_LEVELS_ALL_N1 — Non-unique index on HEIRARCHY_ID, supporting hierarchy-based access.
• OZF_TERR_LEVELS_ALL_N2 — Non-unique index on TERRITORY_ID, supporting territory-based lookups.
• OZF_TERR_LEVELS_ALL_N3 — Non-unique index on TERR_TYPE_ID, supporting type-based filtering.
• Territory concurrent program — The process that populates this table; its REQUEST_ID and PROGRAM_ID values are stored in the row.
• JTF territory hierarchy tables — The JTF territory model that this table mirrors after the concurrent program completes.

These relationships make OZF_TERR_LEVELS_ALL a central reference when analyzing and reporting on territory structures within Oracle EBS 12.1.1 and 12.2.2.