Search Results def_status_code

Overview

The AHL_UE_DEFERRAL_DETAILS_V view is an APPS-owned database view in the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environments, delivered as part of the AHL (Complex Maintenance Repair and Overhaul) product family. Its stated purpose is to retrieve deferral and unit effectivity details, combining maintenance requirement execution data with deferral approval context and quality assurance inspection references. In practice, the view serves as a consolidated reporting and integration surface for maintenance programs executing against a unit effectivity record, where a maintenance task may have been deferred to a later interval rather than completed on schedule.

The view is not a base table. It is a read-only projection intended for inquiry, reporting, and interface extraction. Status is marked VALID in the ETRM metadata, confirming that the underlying dependency chain resolves cleanly at the documented release level. Because the view joins transactional maintenance data with lookup meanings, incident records, and QA plan data, it is best treated as a presentation layer rather than a source of record for any single fact.

The column ORIG_DEFERRAL_UE_ID is exposed directly and also drives several derived columns. Its presence is central to how the view interprets deferral lineage: a unit effectivity record that has an originating deferral can be traced back through this identifier to the deferral that produced it.

Underlying Base Objects

The documented referenced objects include the following:

• AHL_MR_HEADERS_V (VIEW) — supplies maintenance requirement header attributes such as title, revision, description, version number, and quality inspection type code.
• AHL_UNIT_DEFERRALS_B (SYNONYM) — the deferral base table, contributing approval status code via the DEF alias.
• AHL_UNIT_EFFECTIVITIES_APP_V (VIEW) — the primary unit effectivity source providing status, due dates, and originating deferral linkage (UE alias).
• AHL_VISITS_B and AHL_VISIT_TASKS_B (SYNONYMS) — used in a correlated subquery to resolve the organization context for QA plan selection.
• CSI_ITEM_INSTANCES (SYNONYM) — provides the instance number of the maintained asset.
• CS_INCIDENTS_ALL_VL (VIEW) — provides incident number and summary for service incidents tied to the effectivity.
• FND_LOOKUP_VALUES_VL (VIEW) — supplies lookup meanings F1, F2, and F3 used in the status decoding logic.
• QA_PLANS_VAL_V, QA_PLAN_TRANSACTIONS, QA_PLAN_COLLECTION_TRIGGERS, and QA_RESULTS — resolve the appropriate QA plan for the inspection type and collection trigger.
• FND_GLOBAL and FND_PROFILE (PACKAGES) — provide session and profile context within the view definition.

Key Columns

• UNIT_EFFECTIVITY_ID — the primary key of the unit effectivity record being reported.
• OBJECT_VERSION_NUMBER — optimistic locking token for the effectivity row.
• CSI_ITEM_INSTANCE_ID and INSTANCE_NUMBER — the maintained item instance and its human-readable instance identifier.
• MR_HEADER_ID, TITLE, VERSION_NUMBER, DESCRIPTION, REVISION, QA_INSPECTION_TYPE_CODE — maintenance requirement definition attributes.
• EARLIEST_DUE_DATE, DUE_DATE, LATEST_DUE_DATE — the scheduling window for the effectivity.
• STATUS_CODE and MEANING — the base unit effectivity status and its lookup meaning.
• UMP_STATUS_CODE — a computed status. When the effectivity is ACCOMPLISHED, DEFERRED, or TERMINATED, the literal status is returned. Otherwise, if an originating deferral exists, the originating deferral approval status is used; failing that, the current deferral approval status; otherwise the effectivity status.
• DEF_STATUS_CODE and DEF_STATUS — decoded deferral status, resolved through the originating deferral when present, otherwise through the current deferral, with fallback to the effectivity status and its meaning.
• ORIG_DEFERRAL_UE_ID — the unit effectivity identifier of the originating deferral, forming the deferral lineage chain.
• QA_COLLECTION_ID and the QA plan resolution — identifies the QA collection and plan applicable to the inspection.
• CS_INCIDENT_ID, CS_INCIDENT_NUMBER, CS_INCIDENT_SUMMARY — service incident context.
• REPETITIVE_FLAG — indicates whether the maintenance requirement is repetitive.

Common Use Cases and Queries

The view is typically queried to report the current disposition of deferred maintenance tasks, to trace the originating deferral for a given effectivity, and to surface approved-versus-pending deferrals in dashboards or extracts. Because ORIG_DEFERRAL_UE_ID is both selected and consumed in the decode logic, analysts investigating deferral chains frequently filter or group on this column.

Example: locate all unit effectivities generated from an originating deferral.

SELECT unit_effectivity_id, instance_number, orig_deferral_ue_id, status_code, ump_status_code, def_status, due_date
FROM apps.ahl_ue_deferral_details_v
WHERE orig_deferral_ue_id IS NOT NULL
ORDER BY due_date;

Example: report deferral status for a specific asset instance.

SELECT instance_number, title, revision, earliest_due_date, due_date, latest_due_date, def_status
FROM apps.ahl_ue_deferral_details_v
WHERE instance_number = :p_instance_number;

Example: identify deferrals pending approval and their related QA inspection plans.

SELECT unit_effectivity_id, mr_header_id, def_status_code, qa_inspection_type_code, qa_collection_id
FROM apps.ahl_ue_deferral_details_v
WHERE def_status_code NOT IN ('ACCOMPLISHED','DEFERRED','TERMINATED');

Consumers should note that the view performs correlated subqueries against QA plan and visit structures, so performance depends on the selectivity of the driving predicates. Restricting by INSTANCE_NUMBER, MR_HEADER_ID, or UNIT_EFFECTIVITY_ID is recommended over unrestricted full scans.