Results for “route_no”

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

Overview

AHL_ROUTES_H_B is the history (audit) table for AHL_ROUTES_B within the AHL – Complex Maintenance Repair and Overhaul (CMRO) module of Oracle E-Business Suite. In EBS 12.1.1 and 12.2.2, AHL supports the definition and execution of complex maintenance, repair, and overhaul routings, work orders, and visit-level planning for asset-intensive industries such as aerospace and defense, utilities, and heavy equipment. The AHL_ROUTES_B table stores the current or base definition of a route, while AHL_ROUTES_H_B preserves prior revisions and dated versions of that route definition so that historical route configurations remain queryable independently of the active record.

The table resides in the AHL schema and is documented as VALID in ETRM 12.2.2 with 44 physical columns. Its primary key is AHL_ROUTES_H_B_PK, defined on the single column ROUTES_H_ID. A heuristic Data Vault classification mined from the foreign key structure identifies this object as satellite-leaning: it carries descriptive, time-variant attributes that hang off an underlying business key (ROUTE_ID) rather than acting as an independent hub or an associative link.

Key Information Stored

The surrogate primary key ROUTES_H_ID uniquely identifies each historical version row. The business-key relationship to the operational entity is carried by ROUTE_ID, a foreign key pointing to AHL_ROUTES_B; AHL_ROUTES_H_B_U1 is documented as a unique index on ROUTES_H_ID. The most significant columns are:

• ROUTE_ID – foreign key to AHL_ROUTES_B; ties each history row to its parent route definition.
• ROUTE_NO – the user-visible route number used to identify the routing on forms and reports.
• REVISION_NUMBER / REVISION_STATUS_CODE – the revision identity and lifecycle status of the route at the time the history row was captured.
• ACTIVE_START_DATE / ACTIVE_END_DATE – the effective date range during which this version of the route was valid.
• ROUTE_CATEGORY_CODE / ROUTE_TYPE_CODE / PRODUCT_TYPE_CODE – classification attributes that drive how the route is used.
• WORK_CENTER_CODE, ZONE_CODE, SYSTEM_CODE1–3, PROCESS_CODE – organizational and process context for the routing.
• OPERATOR_CODE / OPERATOR_PARTY_ID – the operator or party associated with the route.
• RTPRIM_SKILL_TYPE_CODE / SKILL_COMPETENCE_ID – primary skill and competence requirements.
• TIME_SPAN – scheduled or expected duration for the routing.
• SECURITY_GROUP_ID – foreign key to FND_SECURITY_GROUPS, enforcing multi-org/data security.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – standard EBS WHO columns providing concurrency control and auditability.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – the standard EBS descriptive flexfield columns used for customer-specific extensions.

Common Use Cases and Queries

The primary use case is historical route reporting: retrieving the configuration of a route as it existed on a given date, auditing who changed route classification or skill requirements, and reconstructing revisions for compliance or maintenance-record retention. A typical pattern joins the history table to AHL_ROUTES_B by ROUTE_ID, and to AHL_ROUTES_H_TL by ROUTES_H_ID for translated name and description text.

Example: list all historical versions of a given route, ordered by revision and effective dates.

SELECT h.ROUTES_H_ID, h.ROUTE_NO, h.REVISION_NUMBER, h.REVISION_STATUS_CODE,
  h.ACTIVE_START_DATE, h.ACTIVE_END_DATE, t.NAME
FROM AHL.AHL_ROUTES_H_B h, AHL.AHL_ROUTES_H_TL t
WHERE h.ROUTES_H_ID = t.ROUTES_H_ID
  AND h.ROUTE_ID = :route_id
  AND t.LANGUAGE = USERENV('LANG')
ORDER BY h.REVISION_NUMBER, h.ACTIVE_START_DATE;

Example: point-in-time reconstruction of a route's effective classification.

SELECT * FROM AHL.AHL_ROUTES_H_B
WHERE ROUTE_ID = :route_id
  AND :as_of_date BETWEEN ACTIVE_START_DATE AND NVL(ACTIVE_END_DATE, :as_of_date);

Related Objects

• AHL_ROUTES_B – the base route definition table; joined via AHL_ROUTES_H_B.ROUTE_ID = AHL_ROUTES_B.ROUTE_ID.
• AHL_ROUTES_H_TL – translated (language) history table; FK AHL_ROUTES_H_TL.ROUTES_H_ID references AHL_ROUTES_H_B.ROUTES_H_ID.
• FND_SECURITY_GROUPS – referenced via AHL_ROUTES_H_B.SECURITY_GROUP_ID for data security and multi-org access control.
• AHL_ROUTES_H_B_PK – primary key constraint on ROUTES_H_ID.
• AHL_ROUTES_H_B_U1 – documented unique index on ROUTES_H_ID, supporting business-key lookups.
• AHL_ROUTES_B_TL / AHL_ROUTES_TL – base translated route tables, typically joined through ROUTE_ID for current versus historical text comparison.
• AHL_OBJECTS / AHL_WORKORDERS – downstream CMRO objects that consume route definitions and may reference route history for audit reporting.

Together these objects form the route-definition cluster within the AHL schema, with AHL_ROUTES_H_B serving as the historical satellite that preserves revision-level attribute state for the AHL_ROUTES_B entity.