Results for “ahl_vst_typ_stg_typ_asoc”

22 results




Overview
Explore in AI Chat

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

Overview

AHL_VST_TYP_STG_TYP_ASOC is a transactional configuration table in the AHL schema, which supports the Complex Maintenance Repair and Overhaul (CMRO) module of Oracle E-Business Suite. The table stores the association between visit types and their applicable stage types, defining which maintenance stages are permitted or expected within a given visit type. In EBS 12.1.1 and 12.2.2, this object is registered as VALID and is owned exclusively by the AHL schema; it is delivered as part of the Oracle-designed maintenance execution data model rather than an end-user extensible entity.

The table contains 25 documented columns and one unique index, AHL_VST_TYP_STG_TYP_ASOC_U1, built on VISIT_TYP_STAGE_TYP_ASOC_ID. The only documented foreign key, SECURITY_GROUP_ID, references FND_SECURITY_GROUPS, which reinforces the multi-tenant, row-level security behavior typical of AHL configuration tables.

Based on the mined foreign-key structure, the heuristic Data Vault classification for this object is standalone. From a dimensional modeling perspective, this suggests treating AHL_VST_TYP_STG_TYP_ASOC as an independent reference or setup table rather than as a satellite attached to a visit-type hub; modelers should validate this suggestion against actual data volumes and load patterns before finalizing a Data Vault design.

Key Information Stored

The most significant columns in this table are:

  • VISIT_TYP_STAGE_TYP_ASOC_ID — surrogate primary key and the column underlying the unique index AHL_VST_TYP_STG_TYP_ASOC_U1; uniquely identifies each visit-type-to-stage-type association.
  • VISIT_TYPE_STAGE_ID — business-key candidate linking the association to a specific visit type stage.
  • STAGE_TYPE_CODE — the stage type code applied to the visit type; the second business-key candidate for the association.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, restricting visibility of the row to the appropriate operating unit or security context.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording insert and update provenance.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns, which allow implementers to capture additional visit-type/stage-type configuration attributes without schema changes.

Common Use Cases and Queries

Typical use cases center on maintenance setup validation and reporting. Administrators query the table to verify which stages are configured for a given visit type, and reporting extracts join the association to visit-type setup tables to produce configuration matrices. A representative SQL pattern is:

  • SELECT assoc.visit_typ_stage_typ_asoc_id, assoc.visit_type_stage_id, assoc.stage_type_code FROM ahl.ahl_vst_typ_stg_typ_asoc assoc WHERE assoc.security_group_id = :p_security_group_id;
  • Joins to visit type and stage type setup tables via VISIT_TYPE_STAGE_ID and STAGE_TYPE_CODE to resolve descriptive names.
  • Validation queries that detect orphaned or duplicate associations before CMRO visit execution.
  • Audit reports using CREATION_DATE, LAST_UPDATE_DATE, and the WHO columns to track configuration changes.

Related Objects

The following objects are most significant in relation to this table:

  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; controls row-level visibility.
  • AHL_VISIT_TYPES (visit type setup) — joined indirectly through VISIT_TYPE_STAGE_ID.
  • AHL_STAGE_TYPES (stage type setup) — joined through STAGE_TYPE_CODE.
  • AHL_VST_TYP_STG_ASOC (visit type/stage associations) — a closely related AHL configuration table in the same functional family.
  • AHL_VISIT_STAGES — consumes the visit-type/stage-type configuration when maintenance visits are created and progressed.

No public PL/SQL APIs are documented as direct dependents; maintenance activities for this table are performed through the Oracle CMRO setup forms delivered with the AHL module.