Results for “hierarchy_code”

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

Overview

AMS_HIERARCHIES_ALL_B is the base (language-independent) table in the Oracle Marketing (AMS) module that stores the master definition of every hierarchy supported by Oracle Marketing. Hierarchies in AMS form the structural backbone used to organize marketing entities such as customers, prospects, products, and organizational units into parent-child relationships that drive segmentation, campaign targeting, and metric aggregation. The "_ALL" suffix indicates the table is partitioned by operating unit (ORG_ID), making it multi-org enabled, while the "_B" suffix designates it as the base table of a translatable pair whose descriptive columns reside in the companion table AMS_HIERARCHIES_ALL_TL.

Physically, the table resides in the AMS schema and is documented with 13 columns in the ETRM 12.2.2 schema. From a Data Vault modeling perspective, the mined FK structure classifies AMS_HIERARCHIES_ALL_B as hub-leaning: it holds a stable, unique business concept (the hierarchy) keyed by a surrogate identifier and referenced by multiple downstream tables, with descriptive and audit attributes attached. This is a heuristic suggestion rather than a physical implementation.

Key Information Stored

The table is anchored by the surrogate primary key HIERARCHY_ID (enforced through index AMS_HIERARCHIES_ALL_B_PK and unique index AMS_HIERARCHIES_ALL_B_U1). The principal business-key candidate is HIERARCHY_CODE, enforced by unique index AMS_HIERARCHIES_ALL_B_U2. Because HIERARCHY_CODE is the column most often referenced by users and external integrations, it functions as the human-readable identifier for a hierarchy, whereas HIERARCHY_ID is the internal, immutable key used across all foreign-key relationships. The two are treated as distinct: HIERARCHY_ID drives referential integrity, and HIERARCHY_CODE drives ad-hoc lookup and search.

The 10–15 most significant columns are:

• HIERARCHY_ID — surrogate primary key; the internal identifier used by all child tables.
• HIERARCHY_CODE — unique business identifier for the hierarchy, the primary search column.
• SYS_HIERARCHY_ID — reference to a system (seeded) hierarchy definition, distinguishing delivered hierarchies from user-defined ones.
• ENABLED_FLAG — controls whether the hierarchy is active and selectable in Marketing.
• APPLICATION_ID — foreign key to FND_APPLICATION, identifying the owning application/product.
• ORG_ID — operating unit identifier, enabling multi-org data partitioning.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling record-level access.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the framework during updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns that track creation and modification of each row.

Common Use Cases and Queries

The most frequent access pattern is retrieval of a hierarchy by its business code, which directly matches the user search term:

SELECT h.hierarchy_id, h.hierarchy_code, h.enabled_flag,
       t.name, t.description
  FROM ams.ams_hierarchies_all_b  h,
       ams.ams_hierarchies_all_tl t
 WHERE h.hierarchy_id = t.hierarchy_id
   AND t.language = USERENV('LANG')
   AND h.hierarchy_code = :p_hierarchy_code;

Reporting scenarios typically resolve downstream metrics and activity definitions tied to a hierarchy:

SELECT m.metric_id, m.hierarchy_id
  FROM ams.ams_metrics_all_b m
 WHERE m.hierarchy_id = :p_hierarchy_id;

Integration and validation routines use HIERARCHY_CODE to confirm a hierarchy exists and is enabled before loading data that references it. Data-validation queries commonly join to FND_APPLICATION to filter by owning product, and to FND_SECURITY_GROUPS when enforcing access restrictions. Extraction programs select SYS_HIERARCHY_ID to separate seeded hierarchies from customer-defined ones.

Related Objects

The following objects are the most significant references to AMS_HIERARCHIES_ALL_B, based on the documented foreign-key relationships:

• AMS_HIERARCHIES_ALL_TL — translation table joined on HIERARCHY_ID; stores language-specific name and description.
• AMS_METRICS_ALL_B — metrics table referencing HIERARCHY_ID; defines the metrics computed over a hierarchy.
• AMS_ACT_METRICS_ALL — activity-metric association referencing HIERARCHY_ID; links metrics to marketing activities.
• FND_APPLICATION — referenced via APPLICATION_ID; identifies the owning application.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; controls record-level security.

Together these objects form the hierarchy data model in Oracle Marketing: the base table holds the definition, the TL table holds the display text, and the metric tables consume the hierarchy for measurement and reporting.