Results for “hierarchy_type_code”

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

Overview

The AHL_AUTOVISIT_HIERARCHY table resides in the AHL schema, which supports the Oracle Complex Maintenance, Repair and Overhaul (CMRO) module within Oracle E-Business Suite 12.1.1 and 12.2.2. This table stores hierarchical definition data used by the AutoVisit (automated visit/execution) framework, which governs how maintenance visit programs are structured, sequenced, and applied against Maintainable Configurations (MCs) and their associated snapshots. It acts as a structural blueprint that links hierarchy snapshots, program type/subtype classifications, and sequence ordering, with an AutoVisit flag controlling whether automated visit logic is applied to a given hierarchy node.

From a modeling perspective, the metadata classifies this object as standalone under a heuristic Data Vault analysis. In Data Vault terms, this suggests the table behaves more like a satellite than a hub or link, since it does not participate in outbound foreign key relationships beyond a security group reference; its identity is defined locally via a single-column unique index rather than through a composite business key joined to other entities. This is best treated as a modeling suggestion rather than a definitive classification, as the surrounding AHL constellation may expose additional relationships not reflected in the FK catalog.

Key Information Stored

The table exposes 31 documented columns. The most operationally significant are summarized below, distinguishing the surrogate key from candidate business keys.

• HIERARCHY_ID — The surrogate primary key and the sole column backing the unique index AHL_AUTOVISIT_HIERARCHY_U1, making it the definitive business-key candidate for row lookup.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the AHL framework to detect concurrent edits.
• SNAPSHOT_ID — Foreign reference to the MC snapshot the hierarchy is bound to, anchoring hierarchy to a point-in-time configuration state.
• HIERARCHY_TYPE_CODE — Classifies the hierarchy (e.g., program versus configuration structural types).
• MC_ID — Identifies the Maintainable Configuration the hierarchy applies to.
• PROGRAM_TYPE_CODE / PROGRAM_SUBTYPE_CODE — Define which maintenance program category and subcategory the hierarchy governs.
• SEQUENCE — Determines ordering of nodes within the hierarchy traversal.
• AUTOVISIT_FLAG — The central control flag governing whether automated visit generation applies at this node.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS, enforcing multi-org/security-group data segregation.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The standard EBS DFF (Descriptive Flexfield) block for customer extensibility.

Common Use Cases and Queries

Typical use cases center on determining which MCs are subject to automated visit generation, reviewing the hierarchy ordering for a given snapshot, and auditing configuration-to-program mappings.

Retrieving all AutoVisit-enabled nodes for a configuration snapshot:

SELECT h.hierarchy_id, h.mc_id, h.hierarchy_type_code,
       h.program_type_code, h.sequence, h.autovisit_flag
FROM   ahl.ahl_autovisit_hierarchy h
WHERE  h.snapshot_id = :snapshot_id
  AND  h.autovisit_flag = 'Y'
ORDER  BY h.sequence;

Joining to the security group to validate organization-level access:

SELECT h.hierarchy_id, h.mc_id, g.security_group_name
FROM   ahl.ahl_autovisit_hierarchy h,
       fnd.fnd_security_groups g
WHERE  h.security_group_id = g.security_group_id;

Reporting scenarios include AutoVisit coverage analysis, hierarchy change auditing via LAST_UPDATE_DATE, and DFF-driven custom attribute extraction from ATTRIBUTE1 through ATTRIBUTE15.

Related Objects

• FND_SECURITY_GROUPS — The only documented FK target; joined on SECURITY_GROUP_ID. Governs security-group scoping of hierarchy rows.
• AHL_MC_HEADERS / AHL_SNAPSHOTS — Logical parents referenced via MC_ID and SNAPSHOT_ID; supply configuration context even though not enumerated as formal FKs in the metadata.
• AHL_PROGRAM_TYPES / AHL_PROGRAM_SUBTYPES — Lookup sources for PROGRAM_TYPE_CODE and PROGRAM_SUBTYPE_CODE.
• AHL_VISITS and AHL_VISIT_TASKS — Downstream consumers that instantiate actual visit records from AutoVisit-flagged hierarchy definitions.
• AHL_AUTOVISIT_HIERARCHY_U1 — The unique index supporting direct HIERARCHY_ID lookups and enforcing key integrity.

Because the table is heuristic-standalone, no other AHL tables document a formal FK to it; relationships are largely logical rather than enforced, which is common in CMRO configuration metadata.