Search Results related_status

Overview

The APPS.AHL_MR_RELATIONSHIPS_V view is a reporting and integration object within the Oracle E-Business Suite AHL (Complex Maintenance Repair and Overhaul) product. It exposes the parent-child linkages stored in the AHL_MR_RELATIONSHIPS base table and enriches each row with descriptive attributes for both the parent and the child Maintenance/Repair (MR) header records. Specifically, it joins AHL_MR_RELATIONSHIPS to AHL_MR_HEADERS_VL to retrieve the MR titles and revision numbers of the parent and child MRs, along with status, type, program type, effective dates, downtime, and descriptive flexfield context.

The view holds a VALID status in the APPS schema and is available in both EBS 12.1.1 and 12.2.2. Its primary role is to present a denormalized, human-readable representation of MR relationships so that forms, concurrent programs, OAF pages, and custom reports do not need to resolve the multiple lookup codes and header attributes themselves. Because the column names for parent and child records are largely duplicated (for example TITLE appears once for the parent and once for the child in the source join, disambiguated through aliases in the actual view definition), the view is a convenient single source for relationship-centric reporting.

Underlying Base Objects

The documented base objects referenced by the view are AHL_MR_HEADERS_B, AHL_MR_HEADERS_TL, AHL_MR_RELATIONSHIPS, FND_LOOKUP_VALUES, and the FND_PROFILE package. The AHL_MR_HEADERS_B table stores the header-level, language-independent attributes of each MR (including PROGRAM_TYPE_CODE, TYPE_CODE, MR_STATUS_CODE, REVISION, EFFECTIVE_FROM, EFFECTIVE_TO, and UOM_CODE), while AHL_MR_HEADERS_TL provides the translated TITLE and DESCRIPTION columns used to build the AHL_MR_HEADERS_VL view. AHL_MR_RELATIONSHIPS holds the actual relationship rows, keyed by MR_RELATIONSHIP_ID, that link an MR_HEADER_ID to a RELATED_MR_HEADER_ID through a RELATIONSHIP_CODE.

The view text shows the AHL_MR_HEADERS_APP_V and AHL_MR_RELATIONSHIPS_APP_V views being referenced (these are the runtime counterparts layered over the B/TL tables). FND_LOOKUP_VALUES_VL supplies the decoded MEANING values for MR status, MR type, program type, and downtime unit of measure, using lookup types such as AHL_FMP_REVISION_STATUS, AHL_FMP_MR_TYPE, AHL_FMP_MR_PROGRAM_TYPE, and AHL_FMP_PM_DOWNTIME_UOM. All lookup joins are outer joins (denoted by the (+) operator), so relationship rows are never dropped when a code has no matching lookup value. The FND_PROFILE package reference supports profile-driven behavior in the surrounding application logic.

Key Columns

• MR_RELATIONSHIP_ID – Primary identifier of the relationship record.
• RELATIONSHIP_CODE – Classifies the nature of the link between parent and child MRs.
• MR_HEADER_ID / RELATED_MR_HEADER_ID – Foreign keys identifying the parent and child MR headers respectively.
• PROGRAM_TYPE_CODE – The attribute users most commonly search on; it appears for both the parent (P.PROGRAM_TYPE_CODE) and the child (C.PROGRAM_TYPE_CODE), decoded via the AHL_FMP_MR_PROGRAM_TYPE lookup.
• TITLE, REVISION, DESCRIPTION – Human-readable identity of the parent and child MRs, sourced from the header views.
• MR_STATUS_CODE and MEANING – Current status of each MR, decoded through the AHL_FMP_REVISION_STATUS lookup.
• TYPE_CODE and MEANING – MR type classification decoded through AHL_FMP_MR_TYPE.
• EFFECTIVE_FROM / EFFECTIVE_TO – Date range during which each MR revision is valid.
• DOWN_TIME, UOM_CODE, and UOM MEANING – Downtime quantity and its unit of measure.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – Descriptive flexfield context and segments carried from the relationship record.
• Standard who-columns (OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) – Audit and optimistic locking information.

Because both parent and child attributes are projected, report authors must alias columns carefully to distinguish the two sides of the relationship.

Common Use Cases and Queries

Typical scenarios include identifying all child MRs belonging to a given parent, locating relationships filtered by program type, and building pick lists or hierarchy reports for maintenance programs.

To find all relationships for a specific parent MR program type:

• SELECT mr_relationship_id, relationship_code, title, revision, program_type_code FROM apps.ahl_mr_relationships_v WHERE program_type_code = :p_program_type;

To list children of a parent identified by MR_HEADER_ID:

• SELECT related_mr_header_id, title, revision, mr_status_code, effective_from, effective_to FROM apps.ahl_mr_relationships_v WHERE mr_header_id = :p_mr_header_id;

To join the view to related detail tables, constrain on MR_RELATIONSHIP_ID or the header IDs and restrict by EFFECTIVE_FROM/EFFECTIVE_TO to obtain only currently effective relationships. Because all lookup joins are outer joins, filtering on decoded MEANING columns will exclude rows whose codes have no lookup entry; filter on the underlying code columns when completeness is required.