Search Results ahl_visit_types_b




Overview
Explore in AI Chat

Overview

In Oracle E-Business Suite Release 12.1.1 and 12.2.2, the table AHL.AHL_VISIT_TYPES_B resides in the AHL schema, which supports the Complex Maintenance Repair and Overhaul (CMRO) product family. It serves as the base (or "B") definition table for visit types — the classification codes used during maintenance, repair, and overhaul visit planning and execution. Each row represents a distinct type of visit that can be scheduled against a maintainable asset or work order, providing the master reference list from which visit records are instantiated. The table is not owned by Oracle Manufacturing; it is a sub-module object specific to the AHL CMRO application.

The ETRM metadata classifies this object with a heuristic Data Vault classification of standalone, mined from its FK structure. In Data Vault modeling terms, this suggests the table behaves as a hub-like reference object (carrying its own business key) rather than a link or satellite, since it does not act as an associative between two entities. The single foreign key — SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS — is a data-security reference rather than a modeling association.

Key Information Stored

The documented physical schema for 12.2.2 lists 32 columns. The most functionally significant columns are:

  • VISIT_TYPE_ID — Surrogate primary key. It is the sole candidate for the unique index AHL_VISIT_TYPES_B_U1, and is the column other AHL tables use to reference a visit type.
  • VISIT_TYPE_CODE — The user-facing business identifier for the visit type. Unlike VISIT_TYPE_ID, no unique index is documented on this column, though it functions as the natural business-key candidate.
  • SERVICE_CATEGORY_CODE — Categorizes the visit type by the service line it belongs to, driving default behavior during visit creation.
  • ESTIMATED_DURATION — The planned duration of the visit type, used for scheduling and capacity planning.
  • LINKED_VISIT_TYPE_ID — A self-referencing identifier that lets one visit type be associated with another (for example, a follow-up visit related to a primary one).
  • TRANSIT_TYPE_FLAG — Indicates whether the visit type represents transit or travel time as opposed to on-site work.
  • COMPONENT_VISIT_FLAG — Distinguishes visit types that apply at the component level from those that apply at the asset level.
  • SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org and data-security partitioning.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns, allowing customer-specific extensions without schema changes.
  • LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE#1 — Standard WHO-audit columns tracking row provenance.
  • OBJECT_VERSION_NUMBER#1, STATUS_CODE#1, MC_ID#1 — Columns reflecting multi-language or multi-currency/operating-unit processing (the #1 suffix indicates the numbered column set generated by the AHL design).

Common Use Cases and Queries

Typical uses center on validation, lookup enrichment, and reporting. A common pattern joins the base definition to the security groups to confirm the visit type is available to the current operating unit:

SELECT vt.visit_type_id, vt.visit_type_code, vt.service_category_code, vt.estimated_duration
FROM ahl_visit_types_b vt
WHERE vt.security_group_id = :p_security_group_id;

Listing valid visit types for a picklist is equally common, filtering on the active status and ordering by code. When estimating workload, reporting queries aggregate ESTIMATED_DURATION by SERVICE_CATEGORY_CODE, and can join LINKED_VISIT_TYPE_ID back to the same table to show follow-up relationships:

SELECT v.visit_type_code, v.estimated_duration, l.visit_type_code AS linked_code
FROM ahl_visit_types_b v
LEFT JOIN ahl_visit_types_b l ON l.visit_type_id = v.linked_visit_type_id;

Because the table holds no transactional history, it is almost always a lookup participant rather than a fact source in reporting.

Related Objects

  • FND_SECURITY_GROUPS — Referenced by AHL_VISIT_TYPES_B.SECURITY_GROUP_ID; the only documented outbound FK.
  • AHL_VISIT_TYPES_TL — Translation table keyed by VISIT_TYPE_ID, holding language-specific names and descriptions.
  • AHL_VISITS — Stores actual visit records that reference a visit type via VISIT_TYPE_ID.
  • AHL_WORKORDERS — Work orders that may be associated with a visit type during planning.
  • AHL_MAINT_VISITS / AHL_SCHEDULES — Scheduling objects that consume visit types when building maintenance plans.
  • AHL_VISIT_TYPES_VL — The view commonly used in forms and reports that joins the _B and _TL tables.

All join predicates on these related objects occur through VISIT_TYPE_ID, the unique key documented as AHL_VISIT_TYPES_B_U1.