Results for “ahl_operation_phases_pk”

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

Overview

AHL_OPERATION_PHASES is a table in the AHL schema (Oracle Complex Maintenance Repair and Overhaul) that stores the significant phases associated with a maintenance, repair, or overhaul operation. Within Oracle EBS 12.1.1 and 12.2.2, AHL supports the definition and execution of complex work — routings, operations, and the discrete phases that make up an operation — and this table captures the phase-level detail that is attached to a parent operation. Its role is to normalize the many-to-one relationship between operations and their constituent phases, allowing an operation to be broken into ordered, coded segments that drive execution and reporting.

From a modeling perspective, the mined FK structure suggests a satellite-leaning classification under a heuristic Data Vault reading. The table holds descriptive, descriptive-attribute data about an operation rather than acting as a pure hub or link, though it does carry a forward reference to AHL_OPERATIONS_B. This classification is offered as a modeling suggestion, not a physical constraint.

Key Information Stored

The table is physically documented with 27 columns in ETRM 12.2.2. The most significant are summarized below.

• OPERATION_PHASE_ID — the surrogate primary key (index AHL_OPERATION_PHASES_PK), uniquely identifying each phase record.
• OPERATION_ID — foreign key to AHL_OPERATIONS_B, identifying the parent operation to which the phase belongs.
• PHASE_CODE — the business code that names or classifies the phase; together with OPERATION_ID it forms a unique business key (AHL_OPERATION_PHASES_U2).
• REVISION_NUMBER — supports revisioned phase definitions.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security partitioning.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the underlying AOL framework for concurrent update control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns present on nearly all EBS transactional tables.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DESC flexfield, providing 15 extensible attribute slots and a category discriminator for customer-defined phase data.

Note that OPERATION_PHASE_ID also appears in a separate unique index (AHL_OPERATION_PHASES_U1), reinforcing it as a candidate business key in addition to the primary key. The business key for the entity is properly the combination of OPERATION_ID and PHASE_CODE.

Common Use Cases and Queries

Typical uses of this table include operational reporting (listing all phases for a given operation), integration extracts that pull phase-level routings into external MES or planning systems, and validation of phase definitions against approved codes. Because the table is a child of AHL_OPERATIONS_B, join patterns almost always start from the operation and walk down.

Example — retrieve all phases for a given operation:

SELECT p.operation_phase_id,
       p.operation_id,
       p.phase_code,
       p.revision_number
FROM   ahl.ahl_operation_phases p
WHERE  p.operation_id = :operation_id
ORDER  BY p.phase_code;

Example — join phases to their parent operation for reporting:

SELECT o.operation_id,
       o.operation_code,
       p.phase_code
FROM   ahl.ahl_operations_b      o,
       ahl.ahl_operation_phases  p
WHERE  o.operation_id = p.operation_id
AND    p.security_group_id = :org_security_group;

Common reporting scenarios include phase-count-per-operation metrics, revision audits (using REVISION_NUMBER and the history table), and flexfield queries against ATTRIBUTE_CATEGORY/ATTRIBUTE1–15 for customer-specific phase categorizations.

Related Objects

• AHL_OPERATIONS_B — parent operation; joined on OPERATION_ID (FK from this table).
• AHL_OPERATION_PHASES_H — history/audit table; references this table via OPERATION_PHASE_ID, capturing revisioned changes to phase definitions.
• FND_SECURITY_GROUPS — security partitioning; joined on SECURITY_GROUP_ID.
• AHL_OPERATION_PHASES_PK / _U1 / _U2 — the primary, alternate unique, and business-key unique indexes that enforce integrity and support lookups.
• AHL_OPERATIONS_TL and other AHL operation-related tables — commonly joined for translated operation descriptions when reporting phases.

Together these objects form the operation-phase cluster within AHL, with AHL_OPERATION_PHASES acting as the detail satellite for phase-level execution and definitional data.