Results for “ahl_unit_effectivities_b”

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

Overview

AHL_UNIT_EFFECTIVITIES_B is a core transactional table in the Oracle E-Business Suite 12.1.1 / 12.2.2 Complex Maintenance Repair and Overhaul (AHL) module. It stores the applicable maintenance requirements for all units and their components — meaning each row represents a single maintenance requirement instance, or "unit effectivity," tied to a specific maintainable unit or component (such as an aircraft, engine, or rotable part). This is the central entity that links a physical unit to its governing maintenance program, its scheduled due dates, and its accomplishment or deferral history.

The table is the base (non-translated) table of a base/translation pair, with AHL_UNIT_EFFECTIVITIES_TL holding the language-dependent descriptive columns. Under a Data Vault modeling heuristic, the metadata classifies AHL_UNIT_EFFECTIVITIES_B as a hub, with UNIT_EFFECTIVITY_ID as the primary key. The workload classification is a suggestive model rather than a definitive architectural mandate; in practice the table also carries considerable descriptive and scheduled-date attributes typical of a satellite, but the hub designation reflects its role as the anchor for numerous dependent relationships.

Key Information Stored

The table contains 68 documented columns. The surrogate primary key is UNIT_EFFECTIVITY_ID, protected by the unique index AHL_UNIT_EFFECTIVITIES_B_U1. A secondary business-key candidate, AHL_UNIT_EFFECTIVITIES_B_UK1, is defined on LOG_SERIES_NUMBER, supporting log-series and flight-number tracking.

• Unit and program links: CSI_ITEM_INSTANCE_ID (the physical unit instance from CSI), MR_HEADER_ID and PROGRAM_MR_HEADER_ID (the maintenance requirement definition), MR_INTERVAL_ID, and MR_EFFECTIVITY_ID.
• Scheduling and due information: STATUS_CODE, DUE_DATE, DUE_COUNTER_VALUE, EARLIEST_DUE_DATE, LATEST_DUE_DATE, SET_DUE_DATE, FORECAST_SEQUENCE, and REPETITIVE_MR_FLAG.
• Accomplishment and deferral: ACCOMPLISHED_DATE, DEFER_FROM_UE_ID, ORIG_DEFERRAL_UE_ID, and PRECEDING_UE_ID (self-referencing).
• Aviation and counter context: COUNTER_ID, LOG_SERIES_CODE, LOG_SERIES_NUMBER, FLIGHT_NUMBER, MEL_CDL_TYPE_CODE, ATA_CODE, and POSITION_PATH_ID.
• Contract and incident context: SERVICE_LINE_ID (OKC contract line), CS_INCIDENT_ID, and QA_COLLECTION_ID.
• Work execution and location: CLEAR_STATION_ORG_ID, CLEAR_STATION_DEPT_ID, ORIGINATING_WO_ID, UNIT_CONFIG_HEADER_ID, and ACCOMPLISH_TRIGGER_TYPE.
• Standard audit and descriptive columns: OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, CREATED_BY, SECURITY_GROUP_ID, and ATTRIBUTE1 through ATTRIBUTE15.

Common Use Cases and Queries

Typical scenarios include generating due-list and forecast reports for a fleet, tracking deferred maintenance carried against a unit, and reconciling accomplished requirements against a service contract. A due-date query might select open effectivities for a unit:

SELECT u.unit_effectivity_id, u.status_code, u.due_date, u.due_counter_value, h.mr_header_id
FROM ahl.ahl_unit_effectivities_b u, ahl.ahl_mr_headers_b h
WHERE u.mr_header_id = h.mr_header_id
AND u.csi_item_instance_id = :p_item_instance_id
AND u.status_code = 'OPEN'
ORDER BY u.due_date;

A deferral trace joins AHL_UNIT_DEFERRALS_B to the base table on UNIT_EFFECTIVITY_ID, while an accomplishment query joins AHL_UNIT_ACCOMPLISHMNTS. Reporting commonly surfaces MEL_CDL_TYPE_CODE and ATA_CODE for airworthiness tracking, and LOG_SERIES_NUMBER for chronological sequencing. Because both hub and satellite attributes are combined here, joins to AHL_UNIT_EFFECTIVITIES_TL are required whenever translated descriptions are displayed.

Related Objects

• CSI_ITEM_INSTANCES — joined on CSI_ITEM_INSTANCE_ID; identifies the physical maintained unit.
• AHL_MR_HEADERS_B — joined on MR_HEADER_ID and PROGRAM_MR_HEADER_ID; the maintenance requirement definition.
• AHL_MR_INTERVALS and AHL_MR_EFFECTIVITIES — joined on MR_INTERVAL_ID and MR_EFFECTIVITY_ID.
• AHL_UNIT_EFFECTIVITIES_TL — translation table joined on UNIT_EFFECTIVITY_ID.
• AHL_UNIT_ACCOMPLISHMNTS — joined on UNIT_EFFECTIVITY_ID; accomplishment records.
• AHL_UNIT_DEFERRALS_B — joined on UNIT_EFFECTIVITY_ID; deferral records.
• AHL_UE_RELATIONSHIPS — joined on UE_ID and RELATED_UE_ID; links related effectivities.
• AHL_SCHEDULE_MATERIALS and AHL_VISIT_TASKS_B — joined on UNIT_EFFECTIVITY_ID.
• OKC_K_LINES_B — joined on SERVICE_LINE_ID; the originating contract line.
• CS_INCIDENTS_ALL_B and CS_COUNTERS — joined on CS_INCIDENT_ID and COUNTER_ID; incident and usage-counter context.