Search Results ahl_status_order_rules




Overview
Explore in AI Chat

Overview

The AHL_STATUS_ORDER_RULES table is an Oracle E-Business Suite base table owned by the AHL schema and delivered with the Complex Maintenance Repair and Overhaul (CMRO) product family. Its documented purpose is to store status order rules for any ASO (Asset Status Order) object, governing the permitted sequence in which operational or maintenance statuses may transition during the lifecycle of a recoverable asset, component, or maintenance work unit. The table is present in both EBS 12.1.1 and 12.2.2 and is flagged as VALID in the ETRM data dictionary, with a documented physical schema of twelve columns in release 12.2.2.

From a heuristic Data Vault modeling perspective, this table can be provisionally classified as a standalone hub-like entity: it is not joined to a parent transactional table by foreign key relationships other than the security group, and its identity is carried entirely by its own surrogate primary key. The absence of substantive outbound foreign keys supports treating it as a reference or validation structure rather than a transactional child.

Key Information Stored

The table's surrogate primary key is STATUS_ORDER_RULE_ID, enforced by the AHL_STATUS_ORDER_RULES_PK constraint. This identifier uniquely distinguishes each defined transition rule and is the column most commonly used in joins from consuming modules. Business-meaningful content is concentrated in three rule-definition columns:

  • SYSTEM_STATUS_TYPE — identifies the ASO status system or status family to which the rule belongs, allowing multiple independent status hierarchies to coexist under one maintenance model.
  • CURRENT_STATUS_CODE — the originating (source) status in the transition being governed.
  • NEXT_STATUS_CODE — the target (destination) status that is permitted, or ranked as next, from the current status.

Standard EBS WHO columns are present for audit and concurrency: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER, the last being used by the ORM framework for optimistic locking. SECURITY_GROUP_ID references FND_SECURITY_GROUPS and supports multi-organization access control. ZD_EDITION_NAME is the editioning column supporting EBS Online Patching in 12.2.x.

Common Use Cases and Queries

Typical usage involves validating or rendering the allowed status progression for an asset in a CMRO workflow. A common query pattern resolves the permitted next statuses for a given current status:

  • SELECT next_status_code FROM ahl_status_order_rules WHERE current_status_code = :p_current AND system_status_type = :p_type AND security_group_id = :p_sg;
  • Reporting the complete ordered status matrix for a system status type, ordered by rule identifier, to populate status LOVs and workflow definitions.
  • Audit queries on LAST_UPDATED_BY and LAST_UPDATE_DATE to trace configuration changes to transition rules, supporting change control and validation testing.

Joining to FND_SECURITY_GROUPS on SECURITY_GROUP_ID restricts results to the appropriate security partition in multi-org or multi-tenant deployments.

Related Objects

The documented relationship data identifies the following associations:

  • FND_SECURITY_GROUPS — joined on AHL_STATUS_ORDER_RULES.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID, enforcing access partitioning.
  • AHL_STATUS_ORDER_RULES_PK — the primary key constraint on STATUS_ORDER_RULE_ID, referenced by consuming ASO status logic.
  • ASO-related status definition and asset status tables in the AHL schema, which consume CURRENT_STATUS_CODE and NEXT_STATUS_CODE values to validate state transitions.
  • Standard WHO audit objects (FND_USER) resolvable via LAST_UPDATED_BY and CREATED_BY for audit reporting.