Search Results ahl_prog_type_subtypes




Overview
Explore in AI Chat

Overview

The table AHL_PROG_TYPE_SUBTYPES resides in the AHL schema and belongs to the Oracle Complex Maintenance, Repair and Overhaul (CMRO) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store the association between program types and program subtypes — the classification constructs used throughout CMRO to organize maintenance programs, work scope definitions, and execution structures for complex assets such as aircraft, vessels, and high-value equipment.

The object is registered as a valid TABLE in the ETRM repository with a documented physical schema of 26 columns. Based on the foreign key structure mined from the data model, the object is classified heuristically as a standalone entity within a Data Vault style model. In practice this classification is best read as a modeling suggestion: the table most closely resembles a reference or lookup construct (a small hub-like container of type/subtype pairs) rather than a transactional link or a descriptive satellite, because it carries its own surrogate key and a business-key uniqueness constraint rather than being driven purely by foreign key relationships to parent hubs.

Key Information Stored

The 26 documented columns follow the standard Oracle EBS table pattern: an identifying key, a set of descriptive business columns, an ownership/security column, and the WHO audit and DFF (descriptive flexfield) attribute columns.

Common Use Cases and Queries

The table is typically queried to validate or resolve a program type/subtype combination, to drive LOVs and value sets in CMRO setup screens, and to join subtype descriptions to maintenance program transaction data for reporting.

Resolving the surrogate key for a known pairing:

  • SELECT prog_type_subtype_id FROM ahl.ahl_prog_type_subtypes WHERE program_type_code = :p_type AND program_subtype_code = :p_subtype;

Validating the permitted subtype set for a given program type:

  • SELECT program_subtype_code FROM ahl.ahl_prog_type_subtypes WHERE program_type_code = :p_type ORDER BY program_subtype_code;

Reporting with descriptive flexfield content while honoring security group segmentation:

  • SELECT t.program_type_code, t.program_subtype_code, t.attribute1 FROM ahl.ahl_prog_type_subtypes t WHERE t.security_group_id = :g_id;

Because the object is a low-volume reference table, queries are inexpensive and are frequently embedded in the FROM clause of larger CMRO program and visit reports.

Related Objects

The documented relationship data identifies one outbound foreign key. The most significant related objects are:

  • FND_SECURITY_GROUPS — referenced through AHL_PROG_TYPE_SUBTYPES.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.
  • AHL_PROG_TYPE_SUBTYPES_PK and AHL_PROG_TYPE_SUBTYPES_U1 — primary and unique indexes on PROG_TYPE_SUBTYPE_ID.
  • AHL_PROG_TYPE_SUBTYPES_U2 — unique index on (PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE).
  • Downstream CMRO tables that reference PROG_TYPE_SUBTYPE_ID as a foreign key, providing subtype context on maintenance program definitions and execution records.
  • CMRO setup and value-set definitions that consume PROGRAM_TYPE_CODE and PROGRAM_SUBTYPE_CODE to constrain user entry.

Only the SECURITY_GROUP_ID relationship is confirmed in the supplied metadata; other joins should be verified against the current AHL data model before use in custom code.