Search Results ahl_ump_message

Overview

APPS.AHL_UNIT_EFFECTIVITIES_V is a reporting and inquiry view within the Oracle E-Business Suite Enterprise Asset Management (eAM) and Complex Maintenance, Repair, and Overhaul (CMRO) product family. It presents unit effectivities — the scheduled occurrences of a maintenance program against a specific installed unit — in a fully denormalized, presentation-ready form. Rather than requiring the caller to join the unit effectivity base and translation tables to maintenance program headers, intervals, counters, customer assets, contracts, and lookup meanings, the view assembles all of these into a single result set.

Functionally, the view serves as the data source behind the Maintenance Program Scheduling and unit effectivity inquiry screens in the Oracle Asset Lifecycle (AHL) module, as well as the interface used by concurrent programs and integrations that need to extract due and forecasted maintenance events. Because the view exposes MESSAGE_CODE and its decoded MESSAGE, it is also the natural query target for diagnostics involving unit effectivity warning messages, including messaging maintained through the AHL_UMP_MESSAGE mechanism used by the Unit Maintenance Planner.

Underlying Base Objects

The view is owned by APPS and is defined over the following documented base objects:

• AHL_UNIT_EFFECTIVITIES_B and AHL_UNIT_EFFECTIVITIES_TL — the primary unit effectivity tables supplying dates, status, counter values, and descriptive attributes (aliased B).
• AHL_MR_HEADERS_B and AHL_MR_HEADERS_TL — maintenance program headers providing title, revision, version number, category, program type, and implementation status.
• AHL_MR_INTERVALS — interval definitions referenced through MR_INTERVAL_ID.
• AHL_UE_RELATIONSHIPS — relationships between unit effectivities, supporting preceding and deferral chains.
• AHL_UMP_UTIL_PKG — the Unit Maintenance Planner utility package, invoked as AHL_UMP_UTIL_PKG.GET_UNITNAME to resolve the unit name from CSI_ITEM_INSTANCE_ID.
• CSI_ITEM_INSTANCES — installed base records supplying serial number and instance number.
• CS_INCIDENTS_ALL_B, CS_INCIDENTS_ALL_TL, CS_INCIDENT_TYPES_B, and CS_INCIDENT_TYPES_TL — service request and incident data correlated to the effectivity.
• CSI_COUNTER_TEMPLATE_VL — counter template values yielding COUNTER_NAME.
• MTL_SYSTEM_ITEMS_KFV — key flexfield view supplying PART_NUMBER from CONCATENATED_SEGMENTS.
• OKC_K_HEADERS_ALL_B and OKC_K_LINES_B — contract headers and lines providing contract number, modifier, and service line.
• FND_LOOKUP_VALUES — decoded meanings for status, message, tolerance flag, program type, implementation status, and category.
• FND_PROFILE — profile option access used in runtime derivations.

Two scalar subqueries join back into AHL_MR_HEADERS_B and AHL_UNIT_EFFECTIVITIES_B to produce DEPENDANT_TITLE and DEFER_FROM_MR_TITLE, resolving the titles of predecessor and deferral program records.

Key Columns

• UNIT_EFFECTIVITY_ID — primary identifier for the unit effectivity record.
• UNIT_NAME — resolved via AHL_UMP_UTIL_PKG.GET_UNITNAME.
• PART_NUMBER, SERIAL_NUMBER, INSTANCE_NUMBER — item and installed base identification.
• EARLIEST_DUE_DATE, DUE_DATE, LATEST_DUE_DATE — the scheduling tolerance window for the maintenance event.
• STATUS_CODE / STATUS — effectivity status and its decoded meaning.
• MESSAGE_CODE / MESSAGE — the warning or error message associated with the effectivity and its lookup meaning.
• TOLERANCE_FLAG / TOLERANCE — tolerance indicator and decoded value.
• DATE_RUN, DUE_COUNTER_VALUE, COUNTER_NAME — counter-based scheduling attributes.
• PRECEDING_UE_ID, DEPENDANT_TITLE, DEFER_FROM_UE_ID, DEFER_FROM_MR_TITLE — dependency and deferral context.
• TITLE, VERSION_NUMBER, REVISION, PROGRAM_TYPE_CODE / PROGRAM_TYPE, IMPLEMENT_STATUS_CODE, CATEGORY_CODE — maintenance program header attributes.
• CONTRACT_NUMBER, CONTRACT_NUMBER_MODIFIER, LINE_NUMBER — contract linkage where applicable.
• NET_READING, CS_INCIDENT_ID, CS_INCIDENT_NUMBER, CS_INCIDENT — projected as NULL placeholders in this view definition, reserved for consuming contexts.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield columns for customer extensions.

Common Use Cases and Queries

The view is typically queried to list upcoming and overdue maintenance for a set of units, to trace a unit effectivity back to its program and contract, or to diagnose why a scheduling message was raised.

• Due and overdue effectivities for a unit:

SELECT unit_effectivity_id, unit_name, part_number, serial_number,
       due_date, latest_due_date, status, message
  FROM apps.ahl_unit_effectivities_v
 WHERE serial_number = :p_serial
   AND due_date <= SYSDATE + 30
 ORDER BY due_date;

• Diagnosing effectivities carrying a warning message:

SELECT unit_effectivity_id, unit_name, title, message, status
  FROM apps.ahl_unit_effectivities_v
 WHERE message_code IS NOT NULL
   AND status NOT IN ('ACCOMPLISHED','CANCELLED');

• Program and contract context for an effectivity:

SELECT v.unit_effectivity_id, v.title, v.revision,
       v.program_type, v.contract_number, v.line_number
  FROM apps.ahl_unit_effectivities_v v
 WHERE v.unit_effectivity_id = :p_ue_id;

Because the view performs numerous outer joins and invokes AHL_UMP_UTIL_PKG per row, queries should be constrained by UNIT_EFFECTIVITY_ID, CSI_ITEM_INSTANCE_ID, or date ranges to avoid excessive row-by-row function calls. Read access is generally granted through the APPS schema or equivalent responsibility-level permissions in EBS 12.1.1 and 12.2.2.