Search Results ahl_unit_effectivities_app_v




Overview
Explore in AI Chat

Overview

APPS.AHL_UNIT_EFFECTIVITIES_APP_V is a reporting and integration view within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environment, owned by the APPS schema and delivered as part of the AHL (Complex Maintenance Repair and Overhaul) product family. Unit effectivities represent the scheduled maintenance events generated against a specific unit or asset instance, and this view provides an application-usage-filtered projection of that data. Its functional role is to expose unit effectivity records that are relevant to the current application usage context, as determined by the AHL_APPLN_USAGE profile option, rather than to expose the entire multi-application dataset resident in the underlying base tables.

The view is documented with a VALID status in the ETRM metadata for 12.2.2 and is built directly on top of the translated view AHL_UNIT_EFFECTIVITIES_VL and the FND_PROFILE package. Because it filters on the runtime value of the profile option, the row set returned by AHL_UNIT_EFFECTIVITIES_APP_V is context-sensitive: two sessions operating under different AHL_APPLN_USAGE settings may legitimately see different subsets of effectivity records. This behavior is important for both reporting consumers and integration interfaces that must respect the separation between the maintenance and other usage classifications supported by the AHL module.

Underlying Base Objects

The documented base objects for this view are AHL_UNIT_EFFECTIVITIES_VL (itself a VIEW) and the FND_PROFILE package. The view text selects from AHL_UNIT_EFFECTIVITIES_VL, aliased as UE, and applies the predicate:

WHERE APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')))

The VL view is the translated view layered over the base table AHL_UNIT_EFFECTIVITIES_B and the translation table AHL_UNIT_EFFECTIVITIES_TL. The _B table stores the language-independent columns, including identifiers, dates, status, and the descriptive flexfield attribute columns, while the _TL table supplies the language-dependent translated columns. AHL_UNIT_EFFECTIVITIES_VL joins these two structures so that consumers receive base and translated content in a single row. AHL_UNIT_EFFECTIVITIES_APP_V therefore sits two levels above the physical base table, and its behavior is inherited from the VL view. The FND_PROFILE.VALUE call evaluated in the WHERE clause introduces a dependency on the profile option cache maintained by Oracle Application Object Library.

Key Columns

The view exposes the complete set of unit effectivity columns. Notable columns include:

Common Use Cases and Queries

Typical consumers include maintenance scheduling reports, due-date exception dashboards, and integration extracts that feed external planning systems. A simple query retrieving open effectivities with their due dates:

SELECT unit_effectivity_id, csi_item_instance_id, status_code, due_date, latest_due_date FROM ahl_unit_effectivities_app_v WHERE status_code = 'SCHEDULED' AND due_date < SYSDATE + 30;

A join from effectivity to the maintenance requirement header for descriptive reporting:

SELECT ue.unit_effectivity_id, ue.due_date, mrh.mr_header_id FROM ahl_unit_effectivities_app_v ue, ahl_mr_headers_vl mrh WHERE ue.mr_header_id = mrh.mr_header_id;

Because the view re-evaluates FND_PROFILE.VALUE at query time, callers in SQL*Plus or concurrent programs must ensure the AHL_APPLN_USAGE profile is appropriately set for the session's responsibility, otherwise no rows or an unintended usage subset may be returned. When a specific usage code must be forced regardless of profile configuration, querying AHL_UNIT_EFFECTIVITIES_VL directly with an explicit APPLICATION_USG_CODE predicate is the preferred alternative.