Search Results mr_revision

Overview

APPS.AHL_MR_RELATIONSHIPS_V is a reporting and integration view in the Oracle Enterprise Asset Management (eAM) / Enterprise Asset Management Maintenance Management module. It presents the relationships defined between Maintenance Requirement (MR) headers — the master records that describe maintenance programs and their associated work definitions. The underlying table AHL_MR_RELATIONSHIPS stores the parent-to-child association between two MR headers, including the relationship code that describes the nature of the linkage (e.g., predecessor, successor, or dependency). The view enriches this raw association by joining both the primary MR header and the related MR header to their base and translation tables, and by resolving descriptive lookup codes into user-facing meanings.

The view is exposed under the APPS schema and is typically consumed by Oracle EBS reporting tools (Oracle Reports, BI Publisher, OAF pages) and by interface programs that export or reconcile maintenance requirement relationships between EBS instances or external systems. Because it exposes both the MR identifier columns and their translated meanings, it is well suited to ad-hoc queries and to feed downstream analytics without additional joins to FND_LOOKUP_VALUES or the translation tables.

Underlying Base Objects

The view is defined over the following documented base objects:

• AHL_MR_RELATIONSHIPS (SYNONYM) — the driving table holding MR_RELATIONSHIP_ID, RELATIONSHIP_CODE, MR_HEADER_ID and RELATED_MR_HEADER_ID.
• AHL_MR_HEADERS_B (SYNONYM) — the base (non-translated) MR header table, aliased twice (P for the primary header, C for the related header) to supply status, program type, effective dates, revision, downtime, UOM, repetitive flag and version number.
• AHL_MR_HEADERS_TL (SYNONYM) — the translated header table, also aliased twice (P_TL and C_TL), supplying the DESCRIPTION column in the user's language.
• FND_LOOKUP_VALUES (SYNONYM) — used in inline subqueries and derived tables to resolve lookup codes into meanings for the UOM (lookup type AHL_FMP_PM_DOWNTIME_UOM), program type (AHL_FMP_MR_PROG...), status, and the repetitive yes/no flag (AHL_YES_NO_TYPE), filtered by USERENV('LANG').
• FND_PROFILE (PACKAGE) — referenced by the profile machinery that supplies the user's current language/environment context for the lookup resolution.

The translation and lookup joins are performed with a language filter driven by the FND_PROFILE package so that meanings and descriptions are returned in the session language.

Key Columns

• MR_RELATIONSHIP_ID, OBJECT_VERSION_NUMBER — the relationship surrogate key and the optimistic-locking version.
• RELATIONSHIP_CODE — the code describing the relationship between the two MR headers.
• MR_HEADER_ID, MR_TITLE, MR_STATUS_CODE, STATUS, PROGRAM_TYPE_CODE, PROGRAM_TYPE, EFFECTIVE_FROM, EFFECTIVE_TO, MR_REVISION, DESCRIPTION, TYPE_CODE, TYPE — attributes of the primary MR header, with status/program type/type translated to meanings.
• RELATED_MR_HEADER_ID, RELATED_MR_TITLE, RELATED_MR_STATUS_CODE, RELATED_STATUS, RELATED_PROGRAM_TYPE_CODE, RELATED_PROGRAM_TYPE, RELATED_EFFECTIVE_FROM, RELATED_EFFECTIVE_TO, RELATED_MR_REVISION, RELATED_DESCRIPTION, RELATED_TYPE_CODE, RELATED_TYPE — the equivalent attributes for the related MR header.
• DOWNTIME, UOM_CODE, UOM — downtime quantity and its unit of measure (UOM resolved via AHL_FMP_PM_DOWNTIME_UOM).
• MR_VERSION, RELATED_MR_VERSION — the header version numbers.
• REPETITIVE_FLAG, REPETITIVE, RELATED_REPETITIVE_FLAG, RELATED_REPETITIVE — the repetitive indicator and its AHL_YES_NO_TYPE meaning for each header.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns.
• LAST_UPDATED_DATE, LAST_UPDATE_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — audit columns remapped from the base table.

Common Use Cases and Queries

The view is most often used to report how MRs relate to one another, to validate relationship codes during data migration, and to drive dashboards showing maintenance program dependencies. A typical query listing all relationships for a given program type is:

SELECT MR_TITLE, RELATIONSHIP_CODE, RELATED_MR_TITLE,
       STATUS, RELATED_STATUS, EFFECTIVE_FROM, EFFECTIVE_TO
FROM   APPS.AHL_MR_RELATIONSHIPS_V
WHERE  PROGRAM_TYPE_CODE = :p_program_type
ORDER  BY MR_TITLE, RELATED_MR_TITLE;

A query to find repeated MRs and their related records might use the resolved repetitive meaning:

SELECT MR_TITLE, REPETITIVE, RELATED_MR_TITLE, RELATED_REPETITIVE
FROM   APPS.AHL_MR_RELATIONSHIPS_V
WHERE  REPETITIVE_FLAG = 'Y'
   OR  RELATED_REPETITIVE_FLAG = 'Y';

In data-migration or reconciliation scenarios the view is joined back to the base table on MR_RELATIONSHIP_ID to compare the translated meanings returned by the view against the raw lookup codes, confirming that FND_LOOKUP_VALUES is populated for the active language. Because all descriptive joins are already embedded, the view is also a convenient source for interface extracts where only the relationship code and the two MR identifiers are written to a staging table.