Search Results dependant_title

Overview

APPS.AHL_UNIT_EFFECTIVITIES_V is a reporting and integration view within the Oracle E-Business Suite Complex Maintenance Repair and Overhaul (AHL) module. It consolidates unit effectivity records produced by the Fleet Maintenance Program against item instances held in Oracle Install Base, presenting the calculated due dates, counter readings, tolerance indicators, and program metadata required to evaluate whether a given asset position (node) is compliant, approaching due, or overdue for a maintenance requirement. Rather than forcing downstream consumers to join the unit effectivity base and translation tables to maintenance requirement headers, intervals, Install Base instances, item masters, and service contracts, the view performs that consolidation once and exposes a single denormalized result set. This makes it a natural source for maintenance forecasting dashboards, work order generation routines, extracts to external scheduling systems, and operational reports in Oracle Discoverer or BI Publisher. The view itself does not store data; it derives all content at runtime from the AHL and Install Base transaction tables.

Underlying Base Objects

The view is defined over a mixture of AHL transaction tables, Oracle Install Base and inventory objects, and shared lookup and contract tables. The documented base objects are AHL_MR_HEADERS_B and AHL_MR_HEADERS_TL (maintenance requirement header and its translations), AHL_MR_INTERVALS (interval definitions used to schedule requirements), AHL_UNIT_EFFECTIVITIES_B and AHL_UNIT_EFFECTIVITIES_TL (the unit effectivity fact and translation tables), AHL_UE_RELATIONSHIPS (predecessor/successor links between effectivities), CSI_ITEM_INSTANCES and CSI_COUNTER_TEMPLATE_VL (asset instances and meter templates in Install Base), MTL_SYSTEM_ITEMS_KFV (the concatenated-segment item key flexfield for part numbers), FND_LOOKUP_VALUES (the AHL_UNIT_EFFECTIVITY_STATUS, AHL_UMP_MESSAGE, AHL_YES_NO_TYPE and program-type lookup sets), OKC_K_HEADERS_ALL_B and OKC_K_LINES_B (service contract headers and lines), CS_INCIDENTS_ALL_B, CS_INCIDENTS_ALL_TL, CS_INCIDENT_TYPES_B and CS_INCIDENT_TYPES_TL (service request and incident-type context), and the AHL_UMP_UTIL_PKG package together with the FND_PROFILE package. The view joins these objects primarily on UNIT_EFFECTIVITY_ID, MR_INTERVAL_ID, MR_HEADER_ID, and CSI_ITEM_INSTANCE_ID, and calls AHL_UMP_UTIL_PKG.GET_UNITNAME to resolve the unit name for each instance.

Key Columns

The effectivity identity columns include UNIT_EFFECTIVITY_ID, MR_INTERVAL_ID, MR_EFFECTIVITY_ID, MR_HEADER_ID, PROGRAM_MR_HEADER_ID, PRECEDING_UE_ID, and FORECAST_SEQUENCE, together with the standard WHO audit columns. Asset context is supplied by CSI_ITEM_INSTANCE_ID, UNIT_NAME, PART_NUMBER, SERIAL_NUMBER, and INSTANCE_NUMBER. Scheduling information is carried by EARLIEST_DUE_DATE, DUE_DATE, LATEST_DUE_DATE, SET_DUE_DATE, ACCOMPLISHED_DATE, DATE_RUN, DUE_COUNTER_VALUE, COUNTER_NAME, and NET_READING, while state is expressed through STATUS_CODE and its decoded STATUS, MESSAGE_CODE and MESSAGE, TOLERANCE_FLAG and TOLERANCE, CANCEL_REASON_CODE, and REPETITIVE_MR_FLAG. Requirement metadata comes from TITLE, VERSION_NUMBER, DESCRIPTION, PROGRAM_TYPE_CODE and PROGRAM_TYPE, CATEGORY_CODE and CATEGORY, REVISION, IMPLEMENT_STATUS_CODE and IMPLEMENT_STATUS, plus SERVICE_LINE_ID, LINE_NUMBER, PROGRAM_TITLE, CONTRACT_NUMBER, and CONTRACT_NUMBER_MODIFIER. IMPLEMENT_STATUS is the decoded lookup meaning for MR.IMPLEMENT_STATUS_CODE and indicates whether the source maintenance requirement is implemented, as opposed to the runtime STATUS of the effectivity record itself.

Common Use Cases and Queries

Typical uses include identifying assets whose due dates fall inside a planning horizon, listing all effectivities for a serialized unit, and filtering to requirement programs by implementation status before generating work orders.

• Overdue or imminent effectivities: SELECT unit_effectivity_id, unit_name, part_number, serial_number, due_date, status, implement_status FROM ahl_unit_effectivities_v WHERE due_date <= SYSDATE + 30 AND status_code <> 'ACCOMPLISHED';
• Filtering by requirement implementation status: SELECT mr_header_id, title, implement_status, status, due_date FROM ahl_unit_effectivities_v WHERE implement_status = 'Implemented';
• Unit history by counter: SELECT unit_name, counter_name, due_counter_value, net_reading, tolerance FROM ahl_unit_effectivities_v WHERE csi_item_instance_id = :instance_id ORDER BY forecast_sequence;
• Contract-scoped program reporting: SELECT contract_number, program_title, category, status, latest_due_date FROM ahl_unit_effectivities_v WHERE service_line_id IS NOT NULL;

Because the view performs numerous outer joins and scalar lookup subqueries, queries should filter on indexed identifiers such as CSI_ITEM_INSTANCE_ID, MR_HEADER_ID, or STATUS_CODE where possible to keep response times acceptable on large Install Base populations.