Results for “ahl_vwp_stages_b”

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

Overview

AHL_VWP_STAGES_B is a base (B) table in the AHL schema, the database owner for Oracle Complex Maintenance Repair and Overhaul (CMRO). The table stores information about stages, which are the sequenced lifecycle milestones attached to a visit record within a maintenance, repair, and overhaul execution. In the Oracle EBS 12.1.1 and 12.2.2 data model, the "_B" suffix denotes the language-independent base table holding the authoritative rows, with translated descriptive text maintained separately in the companion table AHL_VWP_STAGES_TL. The table is registered as VALID in the ETRM repository with 32 documented columns at release 12.2.2.

Heuristically, the foreign key topology suggests a hub-leaning classification in a Data Vault model. The table carries a single surrogate primary key (STAGE_ID) with no composite business key of its own, and it references a higher-level entity through VISIT_ID. That pattern is consistent with a hub or a strongly typed core entity surrounded by descriptive and audit attributes. Under this reading, VISIT_ID would be modeled as a link to the visit hub, while scheduling and status columns would be candidates for a satellite.

Key Information Stored

The primary key is STAGE_ID, enforced by the constraint AHL_VWP_STAGES_B_PK and by the unique index AHL_VWP_STAGES_B_U1. Because STAGE_ID is a system-generated surrogate, it is not a business key in the usual sense; the unique index exists primarily to guarantee uniqueness of the surrogate and to support FK lookups.

• STAGE_ID — surrogate identifier for the stage row; parent key referenced by AHL_VISIT_TASKS_B and AHL_VWP_STAGES_TL.
• VISIT_ID — foreign key to AHL_VISITS_B, identifying the visit to which the stage belongs.
• STAGE_NUM and PREV_STAGE_NUM — the ordinal position of the stage within the visit sequence and the ordinal of its immediate predecessor, used to reconstruct the stage flow.
• STAGE_STATUS_CODE — current lifecycle state of the stage (for example, not started, in progress, complete), the principal driver for progress reporting.
• DURATION — planned or actual length of the stage.
• EARLIEST_START_DATE, PLANNED_START_DATE, PLANNED_END_DATE — scheduling constraints and planned window for the stage.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level access in a multi-org or secured environment.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS flexible attribute columns available for customer extensions.
• OBJECT_VERSION_NUMBER — optimistic locking control used by the OAF/BC4J middle tier.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — who-audit columns populated by the standard EBS who-column mechanism.

Common Use Cases and Queries

The most frequent reporting requirement is to list the stages of a visit in sequence, together with their status and schedule. A representative query is:

• Stage sequencing: SELECT STAGE_ID, STAGE_NUM, PREV_STAGE_NUM, STAGE_STATUS_CODE FROM AHL.AHL_VWP_STAGES_B WHERE VISIT_ID = :p_visit_id ORDER BY STAGE_NUM;
• Translated output: join AHL_VWP_STAGES_TL on STAGE_ID, filtering on the session language, to retrieve the stage name and description.
• Work content rollup: join AHL_VISIT_TASKS_B to AHL_VWP_STAGES_B on STAGE_ID to compute how many visit tasks fall under each stage and their completion status.
• Schedule variance: compare PLANNED_START_DATE and PLANNED_END_DATE with actual execution dates to identify stages that have slipped.
• Bottleneck analysis: aggregate by STAGE_STATUS_CODE across many VISIT_ID values to determine where work accumulates.

Because the table is an AHL base table rather than an interface or open-interface table, direct DML is not recommended. Modifications should be performed through the CMRO forms or the supported AHL APIs, which maintain OBJECT_VERSION_NUMBER and the who-columns correctly.

Related Objects

• AHL_VISITS_B — parent entity joined on AHL_VWP_STAGES_B.VISIT_ID = AHL_VISITS_B.VISIT_ID. Defines the visit under which the stage executes.
• AHL_VWP_STAGES_TL — translation table joined on STAGE_ID, supplying language-specific stage names and descriptions.
• AHL_VISIT_TASKS_B — child table whose STAGE_ID column references AHL_VWP_STAGES_B.STAGE_ID, linking individual tasks to their stage.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID to enforce access control.
• AHL_VWP_STAGES_B_PK / AHL_VWP_STAGES_B_U1 — the primary key constraint and unique index that define row identity and support indexed access paths.

Together these objects form the visit-to-task hierarchy in CMRO, with AHL_VWP_STAGES_B occupying the intermediate level between the visit header and the executable task detail.