Results for “ahl_visits_b”

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

Overview

AHL_VISITS_B is the base table in the Oracle E-Business Suite AHL (Complex Maintenance Repair and Overhaul) module that stores information about maintenance visits. In the context of Oracle EBS 12.1.1 and 12.2.2, a "visit" represents a scheduled maintenance event in which a customer asset is received at a service facility, work is performed against it, and the asset is subsequently released. The table acts as the central transactional anchor for visit-driven maintenance planning, space assignment, work order generation, and costing within the AHL product family.

From a Data Vault modeling perspective, the mined foreign key structure classifies this object heuristically as a hub. The metadata explicitly identifies VISIT_ID as the primary key (AHL_VISITS_B_PK), which is the natural hub key for a visit business entity. Downstream tables that reference VISIT_ID represent satellites and links that attach descriptive and transactional context to this hub. This is offered as a modeling suggestion rather than a physical implementation.

Key Information Stored

The documented physical schema for 12.2.2 contains 63 columns. The following are the most significant for functional and integration purposes:

• VISIT_ID — the surrogate primary key, uniquely identifying each visit record.
• VISIT_NUMBER — the user-facing business identifier; it is one of the two unique business-key candidates, captured by unique index AHL_VISITS_B_U1. VISIT_ID itself is also backed by a unique index, AHL_VISITS_B_U2.
• STATUS_CODE — the current lifecycle state of the visit (for example, open, in-progress, or closed).
• VISIT_TYPE_CODE and AUTO_VISIT_TYPE_FLAG — classify the visit and indicate whether the type was system-derived.
• START_DATE_TIME and CLOSE_DATE_TIME — the planned or actual start and closure timestamps of the visit.
• ITEM_INSTANCE_ID — identifies the customer asset (from CSI_ITEM_INSTANCES) that is the subject of the visit.
• INVENTORY_ITEM_ID and ITEM_ORGANIZATION_ID — define the item and inventory organization context.
• DEPARTMENT_ID — the performing department (from BOM_DEPARTMENTS).
• PROJECT_ID and PROJECT_FLAG — link the visit to a project in PA_PROJECTS_ALL when project-driven costing or billing applies.
• SERVICE_REQUEST_ID — the originating service request or incident (from CS_INCIDENTS_ALL_B).
• SIMULATION_PLAN_ID and SIMULATION_DELETE_FLAG — connect the visit to simulation planning in AHL_SIMULATION_PLANS_B.
• UNIT_SCHEDULE_ID — associates the visit with a unit schedule.
• ESTIMATED_PRICE, ACTUAL_PRICE, and PRICE_LIST_ID — carry cost and pricing information.
• SECURITY_GROUP_ID — enforces multi-tenant security through FND_SECURITY_GROUPS.
• ORGANIZATION_ID — the operating unit or organization context, consistent with the standard EBS MULTI_ORG pattern.

Standard EBS WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) and the 15 generic ATTRIBUTE columns are also present, along with flags such as TEMPLATE_FLAG, OUT_OF_SYNC_FLAG, FIRMED_FLAG, LOCKED_FLAG, and PAST_DATED_VISIT_FLAG that govern visit behavior.

Common Use Cases and Queries

Typical reporting and integration scenarios for AHL_VISITS_B include open visit dashboards, visit history by asset, workload analysis by department, and project-linked maintenance cost reporting. A common query pattern joins the base table to its translation table for the user-facing description:

• Open visits by asset: SELECT v.VISIT_NUMBER, v.START_DATE_TIME, t.DESCRIPTION FROM AHL_VISITS_B v, AHL_VISITS_TL t WHERE v.VISIT_ID = t.VISIT_ID AND v.ITEM_INSTANCE_ID = :p_item_instance_id AND v.STATUS_CODE NOT IN ('CLOSED').
• Visit-to-workorder drilldown: join AHL_WORKORDERS on VISIT_ID to list all work orders generated for a visit.
• Space utilization: join AHL_SPACE_ASSIGNMENTS on VISIT_ID to report facility usage per visit.
• Project costing: join PA_PROJECTS_ALL on PROJECT_ID where PROJECT_FLAG is set to attribute maintenance cost to a capital or service project.
• Visit volume trending: aggregate COUNT(VISIT_ID) grouped by START_DATE_TIME and DEPARTMENT_ID or VISIT_TYPE_CODE for capacity planning.

Because the table carries ORGANIZATION_ID and SECURITY_GROUP_ID, reporting should always apply multi-org and security-group predicates to comply with EBS access controls.

Related Objects

AHL_VISITS_B is both heavily referenced and itself a referencer. The most significant related objects documented in the ETRM metadata are:

• AHL_VISITS_TL — the translation table joined on VISIT_ID, supplying language-specific visit descriptions.
• AHL_WORKORDERS — references VISIT_ID; the primary operational link for execution of maintenance work.
• AHL_VISIT_TASKS_B — references VISIT_ID; defines the task list to be performed during the visit.
• AHL_SPACE_ASSIGNMENTS — references VISIT_ID; tracks physical space assigned to the visit.
• AHL_SNAPSHOTS — references VISIT_ID; captures visit state snapshots for planning comparison.
• AHL_VWP_STAGES_B — references VISIT_ID; records visit workflow stage progression.
• CSI_ITEM_INSTANCES — referenced by ITEM_INSTANCE_ID; identifies the serviced asset.
• CS_INCIDENTS_ALL_B — referenced by SERVICE_REQUEST_ID; the originating service request.
• PA_PROJECTS_ALL — referenced by PROJECT_ID; links the visit to project accounting.
• MTL_SYSTEM_ITEMS_B and BOM_DEPARTMENTS — referenced by INVENTORY_ITEM_ID/ITEM_ORGANIZATION_ID and DEPARTMENT_ID respectively, providing item and department master data.

Additional referencing objects in the metadata include AHL_SCHEDULE_MATERIALS, AHL_SIMULATION_PLANS_B, and several IBW materialized views (IBW_PRODUCT_MV, IBW_PRODUCT_TIME_MV, IBW_PAGE_SA_TIME_MV) used for Business Intelligence reporting.