Search Results ahl_prog_type_subtypes_u2

Overview

The AHL.AHL_PROG_TYPE_SUBTYPES table is a seed-data configuration table within the Oracle E-Business Suite Product Lifecycle Management (PLM) module. It stores the hierarchical association between program types and program subtypes used throughout the Oracle Complex Maintenance, Repair, and Overhaul (CMRO) and Enterprise Asset Management (EAM) program definitions. In Oracle EBS 12.1.1 and 12.2.2, this table functions as a controlled vocabulary that constrains which subtype values are valid for a given program type, ensuring referential consistency when users define maintenance, repair, and overhaul programs.

The table resides in the APPS_TS_SEED tablespace, which is characteristic of reference and setup data distributed with the application. Its status is documented as VALID, and it carries the FND Design Data identifier AHL.AHL_PROG_TYPE_SUBTYPES. From a data-modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone: the table has no outgoing foreign keys to other transactional hubs or links except a single reference to FND_SECURITY_GROUPS via SECURITY_GROUP_ID. This suggests the table behaves primarily as a reference or lookup structure rather than a transactional hub, link, or satellite, though it can be modeled as a small reference hub if desired.

Key Information Stored

The table contains 26 documented columns. The most significant are outlined below, distinguishing the surrogate primary key from business-key candidates defined by unique indexes.

• PROG_TYPE_SUBTYPE_ID — Surrogate primary key for the table, enforced by the primary key constraint AHL_PROG_TYPE_SUBTYPES_PK and the unique index AHL_PROG_TYPE_SUBTYPES_U1.
• PROGRAM_TYPE_CODE — Character code (VARCHAR2(30)) identifying the program type; part of the composite business key.
• PROGRAM_SUBTYPE_CODE — Character code (VARCHAR2(30)) identifying the program subtype; paired with PROGRAM_TYPE_CODE to form the composite unique business key enforced by AHL_PROG_TYPE_SUBTYPES_U2.
• OBJECT_VERSION_NUMBER — Stores the optimistic locking sequence used by the ORM layer to detect concurrent updates.
• SECURITY_GROUP_ID — Used in application hosting and multi-tenant deployments; references FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing audit and accountability metadata.
• ATTRIBUTE_CATEGORY — Descriptive flexfield structure-defining column.
• ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield segment columns (VARCHAR2(150) each) reserved for customer-specific extensions.

Common Use Cases and Queries

Typical usage centers on validating and listing valid subtype values for a given program type, populating dependent LOVs in CMRO or EAM forms, and auditing configuration changes.

To retrieve all valid subtypes for a program type:

• SELECT PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE FROM AHL.AHL_PROG_TYPE_SUBTYPES WHERE PROGRAM_TYPE_CODE = :p_type_code;

To confirm uniqueness constraints are intact:

• SELECT PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE, COUNT(*) FROM AHL.AHL_PROG_TYPE_SUBTYPES GROUP BY PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE HAVING COUNT(*) > 1;

To audit recent configuration changes:

• SELECT PROG_TYPE_SUBTYPE_ID, PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY FROM AHL.AHL_PROG_TYPE_SUBTYPES ORDER BY LAST_UPDATE_DATE DESC;

Reporting use cases include configuration baselines, migration validation between instances, and descriptive flexfield attribute usage analysis.

Related Objects

The following objects are significant in relation to this table, based on the documented foreign-key relationship and typical PLM/CMRO dependencies:

• FND_SECURITY_GROUPS — Referenced by AHL_PROG_TYPE_SUBTYPES.SECURITY_GROUP_ID; governs multi-tenant hosting visibility.
• AHL_PROG_TYPES — Parent program type definition table, joined on PROGRAM_TYPE_CODE.
• AHL_PROGRAM_SUBTYPES — Subtype definition table, joined on PROGRAM_SUBTYPE_CODE.
• AHL_MAINT_PROGRAMS — Consumes valid type/subtype combinations when programs are defined.
• AHL_PROG_TYPE_SUBTYPES_U1 and AHL_PROG_TYPE_SUBTYPES_U2 — Unique indexes enforcing the surrogate and business keys respectively.
• FND_DESCR_FLEX_COL_USAGE — Metadata source for descriptive flexfield behavior on ATTRIBUTE_CATEGORY and ATTRIBUTE1..15.