Results for “inst_routing_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AHL_VISIT_INST_ROUTINGS is a transactional table in the AHL (Complex Maintenance Repair and Overhaul) product schema of Oracle E-Business Suite, holding one row per routable instance record associated with a maintenance visit. It captures the linkage between a defined visit (AHL_VISITS_B) and the specific instance-level routing records that drive the sequencing of maintenance, repair, or overhaul activities performed during that visit. In EBS 12.1.1 and 12.2.2 the table resides in the AHL schema and is reported as VALID, with 27 documented columns in the ETRM 12.2.2 physical schema.

From a Data Vault modeling perspective, the FK heuristics classify this object as standalone rather than a hub, link, or satellite. That classification is a modeling suggestion only; functionally the table behaves as a child detail entity of the visit header, since its foreign keys resolve to AHL_VISITS_B and FND_SECURITY_GROUPS. Readers should treat the standalone label as an indication that the FK graph does not extend far beyond those two references, not as a definitive design statement from Oracle.

Key Information Stored

The surrogate primary key is INST_ROUTING_ID, enforced by the unique index AHL_VISIT_INST_ROUTINGS_U1. A second unique index, AHL_VISIT_INST_ROUTINGS_U2, spans (VISIT_ID, INSTANCE_ID) and therefore identifies the true business-key candidate: within a given visit, an instance appears at most once in the routing set. Note that both INSTANCE_ID and INST_ROUTING_CODE appear in the documented column list, but only INSTANCE_ID participates in a documented unique constraint. The remaining important columns are:

• VISIT_ID — foreign key to AHL_VISITS_B; identifies the parent visit.
• INSTANCE_ID — the routed instance; paired with VISIT_ID in the business-key unique index.
• INST_ROUTING_CODE — the routing code or sequence identifier for the instance.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing Multi-Org / data security partitioning.
• OBJECT_VERSION_NUMBER — optimistic locking column maintained by the EBS framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF (Descriptive Flexfield) columns for customer-defined extensions.

Common Use Cases and Queries

Typical usage includes listing all routed instances for a visit, verifying that every required instance has a routing row, and reconstructing the visit-to-instance mapping for work-order reporting.

• Visit detail: SELECT INST_ROUTING_ID, VISIT_ID, INSTANCE_ID, INST_ROUTING_CODE FROM AHL_VISIT_INST_ROUTINGS WHERE VISIT_ID = :p_visit_id ORDER BY INST_ROUTING_CODE;
• Orphan / gap detection: compare AHL_VISIT_INST_ROUTINGS against the expected instance set to find visits with missing routings.
• Audit and change tracking: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to show recently modified routing rows.
• Security-scoped reports: join FND_SECURITY_GROUPS on SECURITY_GROUP_ID to restrict output to a responsibility's security group.
• DFF extraction: select ATTRIBUTE_CATEGORY and the relevant ATTRIBUTE columns for customer-specific compliance reporting.

Related Objects

The documented FK relationships identify the immediate dependencies:

• AHL_VISITS_B — joined on AHL_VISIT_INST_ROUTINGS.VISIT_ID = AHL_VISITS_B.VISIT_ID; the parent visit header.
• FND_SECURITY_GROUPS — joined on AHL_VISIT_INST_ROUTINGS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.

In practice, related AHL objects such as visit instance tables, routing definition tables, and the visit instance APIs in the AHL module are commonly joined or queried alongside this table, though the supplied metadata documents only the two foreign keys above. Any join beyond those two should be validated against the actual instance before use in production reporting.