Search Results descendent_count

Overview

AHL.AHL_APPLICABLE_MRS is a global temporary table (GTT) owned by the AHL schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is registered as FND Design Data under the same name and holds a status of VALID. The table is used by Oracle Complex Maintenance, Repair, and Overhaul (CMRO) and Enterprise Asset Management flows to stage the set of Maintenance Requirements (MRs) that are applicable to a given item instance during a planning or scheduling run. Because it is a GTT with a SYS$TRANSACTION data duration, rows are visible only to the session that inserted them and are purged at transaction commit or rollback. This design makes the table transient scratch space rather than a persistent repository.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. In modeling terms, this object does not behave as a hub or link in a persistent sense; it is better treated as a transient staging or work table rather than a durable satellite. Its two declared foreign keys (MR_HEADER_ID → AHL_MR_HEADERS_B and MR_EFFECTIVITY_ID → AHL_MR_EFFECTIVITIES) are informational references to persistent master data, but the row lifetime is bounded by the session transaction.

Key Information Stored

The table has 17 documented columns. The most significant are:

• CSI_ITEM_INSTANCE_ID (NUMBER) — identifies the specific item instance (asset) for which applicable MRs are being computed. Appears as part of the composite leading column of index AHL_APPLICABLE_MRS_N2 and is a business-key candidate.
• MR_HEADER_ID (NUMBER) — the primary MR definition; FK to AHL_MR_HEADERS_B. Together with CSI_ITEM_INSTANCE_ID it forms the leading composite of index AHL_APPLICABLE_MRS_N1, indicating it is the principal business key combination.
• MR_EFFECTIVITY_ID (NUMBER) — FK to AHL_MR_EFFECTIVITIES, identifying the effectivity range applicable at the time of evaluation.
• PRECEDING_MR_HEADER_ID (NUMBER) — self-referencing predecessor MR, supporting sequencing and dependency chains.
• PROGRAM_MR_HEADER_ID (NUMBER) — program-level MR header; indexed independently by AHL_APPLICABLE_MRS_N3 alongside PM_SCHEDULE_EXISTS.
• DESCENDENT_COUNT (NUMBER) — the count of descendant MRs derived from the parent/child MR hierarchy; this is the column associated with the search term.
• REPETITIVE_FLAG, SHOW_REPETITIVE_CODE, WHICHEVER_FIRST_CODE, COPY_ACCOMPLISHMENT_CODE (VARCHAR2) — control flags governing how repetitive work orders and accomplishment copying are treated.
• IMPLEMENT_STATUS_CODE (VARCHAR2, length 30) — the implementation status of the MR.
• SERVICE_LINE_ID, COVERAGE_IMP_LEVEL (NUMBER) — service line and coverage/implementation level attributes.
• CONTRACT_START_DATE, CONTRACT_END_DATE, PROGRAM_END_DATE (DATE) — date bounds used for applicability filtering.
• PM_SCHEDULE_EXISTS (VARCHAR2) — flag indicating whether a preventive-maintenance schedule exists for the MR.

No surrogate single-column primary key is documented; the de facto business key is the combination of CSI_ITEM_INSTANCE_ID and MR_HEADER_ID, evidenced by index AHL_APPLICABLE_MRS_N1. Three non-unique indexes (N1, N2, N3) support filtering and joins.

Common Use Cases and Queries

Typical usage falls into three categories: populating the GTT during MR applicability processing, reporting on repetitive and descendant MR hierarchies, and debugging sequence or effectivity issues. A representative query joining back to the persistent headers is:

SELECT a.CSI_ITEM_INSTANCE_ID,
       a.MR_HEADER_ID,
       a.DESCENDENT_COUNT,
       a.REPETITIVE_FLAG,
       a.PM_SCHEDULE_EXISTS
FROM   AHL.AHL_APPLICABLE_MRS a
WHERE  a.MR_HEADER_ID = :mr_header_id;

Because the table is GTT with SYS$TRANSACTION duration, any query must be run in the same session and transaction that populated the rows. Reporting against historical applicability must instead target the persistent MR and effectivity tables.

Related Objects

• AHL.AHL_MR_HEADERS_B — joined on MR_HEADER_ID; supplies persistent header attributes.
• AHL.AHL_MR_EFFECTIVITIES — joined on MR_EFFECTIVITY_ID; supplies effectivity ranges.
• APPS.AHL_APPLICABLE_MRS — the APPS synonym commonly referenced by concurrent programs and PL/SQL packages.
• CSI_ITEM_INSTANCE — referenced through CSI_ITEM_INSTANCE_ID for asset context.
• AHL.AHL_MR_HEADERS_TL — translation table typically consulted alongside AHL_MR_HEADERS_B for display names.
• PM_SCHEDULES related objects — surfaced through PM_SCHEDULE_EXISTS and PROGRAM_MR_HEADER_ID.