Search Results ahl_fmp_mr_relationship

Overview

The APPS.AHL_SR_UE_ASSOCIATIONS_V view is a reporting and integration object within the Oracle E-Business Suite Advanced Service Management (formerly Enterprise Asset Management / Field Service) module. It exposes the associations that link Service Requests (incidents) to Unit Effectivity records, and from those Unit Effectivity records to their related Manufacturing Routing (MR) headers and parent Unit Effectivity definitions. In practical terms, the view answers the question: "For a given service request and a given serialized item instance, which Unit Effectivity definitions and MR routings apply, and in what parent/child relationship?"

The view name is often searched in connection with the term ahl_unit_effectivity_status, which is the seeded lookup type (AHL_UNIT_EFFECTIVITY_STATUS) that supplies the decoded status meaning and description for the Unit Effectivity record. This makes the view a natural entry point for users who need to translate the coded STATUS_CODE stored on Unit Effectivity into its human-readable STATUS_MEANING. The view is owned by the APPS schema and is intended to be consumed read-only by reports, OAF pages, and custom integrations.

Underlying Base Objects

The view is defined over a combination of views, synonyms, and lookup views:

• AHL_UNIT_EFFECTIVITIES_APP_V (VIEW) — aliased twice as SR_UE and UE, providing the Unit Effectivity records for both the service-request side and the parent-side of the association.
• AHL_UE_RELATIONSHIPS (SYNONYM) — aliased UER, supplying the explicit parent/child link between two Unit Effectivity rows.
• AHL_MR_HEADERS_VL (VIEW) — aliased MR, supplying the manufacturing routing header title, version, description, and program type.
• CS_INCIDENTS_ALL_B (SYNONYM) — aliased CS, providing the source service request (incident) record.
• FND_LOOKUP_VALUES_VL (VIEW) — referenced three times as derived lookup subqueries F1, F2, and F3 for status, relationship, and program type decoding.
• FND_PROFILE (PACKAGE) — documented as a referenced object, typically supporting profile-option based filtering within the unit effectivity application view.

The join logic links a service-request Unit Effectivity (SR_UE) to its related Unit Effectivity (UE) through AHL_UE_RELATIONSHIPS, keeping only relationships where RELATIONSHIP_CODE = 'PARENT'. Service-request Unit Effectivity rows with a NULL status or a status other than DEFERRED or EXCEPTION are retained.

Key Columns

• UNIT_EFFECTIVITY_ID / OBJECT_VERSION_NUMBER — primary identifier and optimistic-locking version of the parent-side Unit Effectivity record.
• CSI_ITEM_INSTANCE_ID — the serialized item instance to which the effectivity applies.
• STATUS_CODE / STATUS_MEANING / STATUS_DESCRIPTION — the decoded lookup of AHL_UNIT_EFFECTIVITY_STATUS, the column set users search for under ahl_unit_effectivity_status.
• MR_HEADER_ID, MR_TITLE, MR_VERSION, MR_DESCRIPTION — the associated manufacturing routing header details.
• PROGRAM_TYPE_CODE / PROGRAM_TYPE_MEANING / PROGRAM_TYPE_DESCRIPTION — decoded from AHL_FMP_MR_PROGRAM_TYPE.
• CS_INCIDENT_ID — the service request (incident) identifier linking the association back to CS_INCIDENTS_ALL_B.
• UE_RELATIONSHIP_ID, RELATIONSHIP_CODE — identifier and code of the Unit Effectivity relationship (restricted to PARENT).
• RELATIONSHIP_MEANING / RELATIONSHIP_DESCRIPTION — decoded from AHL_FMP_MR_RELATIONSHIP.

Common Use Cases and Queries

The principal use case is status reporting: retrieving the decoded effectivity status for the Unit Effectivity records attached to a service request. A typical query filters by the incident identifier and joins back to the item instance:

• Status decoding report — group by STATUS_MEANING to count Unit Effectivity records by status across a portfolio of service requests.
• Routing applicability — list MR_TITLE and MR_VERSION for a given CS_INCIDENT_ID to determine which routings are in effect.
• Instance traceability — filter on CSI_ITEM_INSTANCE_ID to trace all service requests and routings for a specific serialized unit.

A representative statement:

SELECT status_meaning, mr_title, mr_version, cs_incident_id
FROM apps.ahl_sr_ue_associations_v
WHERE csi_item_instance_id = :p_instance_id
ORDER BY cs_incident_id, unit_effectivity_id;

Because the view already restricts to PARENT relationships and excludes DEFERRED and EXCEPTION service-request effectivities, consumers should not reapply those filters unless the intent is to broaden the result set. The view should be treated as read-only and is best consumed with bind parameters on CS_INCIDENT_ID or CSI_ITEM_INSTANCE_ID to limit execution cost against the underlying AHL and CS base objects.