Results for “route_category_code”

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

Overview

In Oracle E-Business Suite 12.1.1 and 12.2.2, AHL_ROUTES_H_VL is a documented, valid view owned by the APPS schema within the AHL (Complex Maintenance Repair and Overhaul) product family. Its name follows the standard Oracle convention for a "_VL" view — a "translated view" that joins base (non-translated) data with its language-specific translations. The description provided in the ETRM metadata states explicitly that it is "based on the table AHL_ROUTES_H_B and the translated columns from the table AHL_ROUTES_H_TL."

This view presents route header information — master routing records used in MRO (Maintenance, Repair and Overhaul) and depot-level repair processes — including routing identity, revision, status, scheduling dates, work-center and process classification, plus user-attribute flexfield columns. Its principal role in reporting and integration is to expose a single, language-aware record per route that application code, concurrent programs, BI Publisher reports, and interface programs can query without manually joining the base and translation tables. Because it resolves the current session language via USERENV('LANG'), one row is returned per route for the active language, which simplifies query logic and avoids duplicate-row issues common to direct multi-language joins.

Underlying Base Objects

The view is defined over two referenced base objects, both exposed to the APPS schema as synonyms (as recorded in the ETRM 12.2.2 metadata):

• AHL_ROUTES_H_B — the base table holding all language-independent route header columns (alias AHRO in the view definition).
• AHL_ROUTES_H_TL — the translation table holding language-specific text for the route (alias AHROTL), filtered to the session language.

The join predicate is AHRO.ROUTES_H_ID = AHROTL.ROUTES_H_ID AND AHROTL.LANGUAGE = USERENV('LANG'). Only two columns are sourced from the translation table — TITLE and NOTE — while all remaining columns originate from the base table. This split reflects the Oracle design pattern in which descriptive, user-facing text is translatable whereas codes, flags, dates, and identifiers are not.

Key Columns

Frequently significant columns include:

• ROUTES_H_ID — the primary internal identifier of the route header; the join key between the base and translation tables.
• ROUTE_ID, ROUTE_NO, REVISION_NUMBER — the business-facing route identity and revision. REVISION_STATUS_CODE records the revision lifecycle state.
• ACTIVE_START_DATE, ACTIVE_END_DATE — the effective date window of the route.
• TITLE, NOTE — translatable descriptive text (from the _TL table).
• PRODUCT_TYPE_CODE, ZONE_CODE, ROUTE_CATEGORY_CODE, ROUTE_TYPE_CODE, WORK_CENTER_CODE — classification and organizational attributes controlling where and how the route applies.
• SYSTEM_CODE1–3, PROCESS_CODE, OPERATOR_PARTY_ID, SKILL_COMPETENCE_ID, TIME_SPAN — operational parameters describing execution requirements.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard 15-column descriptive flexfield (DFF) segment set.
• OBJECT_VERSION_NUMBER, CREATION_* and LAST_UPDATE_* audit columns — concurrency control and change tracking.

Common Use Cases and Queries

Typical reporting scenarios include listing active routes for a work center, retrieving route headers with their translated titles for a given language session, and feeding route definitions into integration extracts. Because the view filters on session language, reports generally require no additional language predicate.

Example — active routes with translated titles:

SELECT ROUTE_NO, REVISION_NUMBER, TITLE, WORK_CENTER_CODE
FROM   APPS.AHL_ROUTES_H_VL
WHERE  REVISION_STATUS_CODE = 'ACTIVE';

Example — routes for a specific work center and date range:

SELECT ROUTE_ID, ROUTE_NO, REVISION_NUMBER, TITLE,
       ACTIVE_START_DATE, ACTIVE_END_DATE, TIME_SPAN
FROM   APPS.AHL_ROUTES_H_VL
WHERE  WORK_CENTER_CODE = :p_work_center
AND    :p_effective_date BETWEEN ACTIVE_START_DATE AND NVL(ACTIVE_END_DATE, :p_effective_date);

For integration, the DFF attributes and system codes are commonly selected alongside the business key to populate downstream MRO or planning systems. Note that when querying outside an EBS session context, USERENV('LANG') may not resolve as expected, so the session language should be set deliberately.