Search Results ahl_prog_type_subtypes_pk

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.

• PROG_TYPE_SUBTYPE_ID — the surrogate primary key, enforced by AHL_PROG_TYPE_SUBTYPES_PK and duplicated by unique index AHL_PROG_TYPE_SUBTYPES_U1. This is the column referenced by downstream child tables.
• PROGRAM_TYPE_CODE and PROGRAM_SUBTYPE_CODE — the two business-key candidate columns, jointly enforced by unique index AHL_PROG_TYPE_SUBTYPES_U2. Together they define the valid pairing of a program type with an allowed subtype, which is the core semantic content of the row.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, providing multi-tenant / responsibility-based data segregation of the type-subtype definitions.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Application Framework and BC4J to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns that record who created and last modified each definition and when.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield segments, available for client-specific extensions to the type/subtype association without schema change.

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.