Search Results ahl_fmp_mr_program_subtype




Overview
Explore in AI Chat

Overview

APPS.AHL_PROG_TYPE_SUBTYPES_V is a reporting and integration view in the Oracle E-Business Suite Maintenance (Enterprise Asset Management) product family. It exposes the relationship between program types and program sub-types used by the Failure Management / Maintenance program configuration, resolving the underlying code values into their descriptive meanings. The view joins the intersection entity AHL_PROG_TYPE_SUBTYPES against the Oracle Application Object Library lookups view FND_LOOKUP_VALUES_VL twice, once for the program type and once for the program sub-type. The lookup types involved are AHL_FMP_MR_PROGRAM_TYPE and AHL_FMP_MR_PROGRAM_SUBTYPE. Because the view presents decoded meanings and descriptions alongside the raw codes, it is the preferred access point for reports, concurrent programs, OAF pages, and outbound integration interfaces that need human-readable program type and sub-type information without performing their own lookup resolution.

Underlying Base Objects

According to the documented ETRM metadata for 12.2.2, the view is owned by APPS and is defined over two referenced base objects:

  • AHL_PROG_TYPE_SUBTYPES (referenced as a SYNONYM) — the base intersection table holding one row per program type / program sub-type combination, including the surrogate key PROG_TYPE_SUBTYPE_ID, the OBJECT_VERSION_NUMBER for optimistic locking, the code columns, and a set of descriptive flexfield attribute columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15).
  • FND_LOOKUP_VALUES_VL (VIEW) — the multilingual lookup values view used for code-to-meaning translation, aliased twice: PT for program type and ST for program sub-type.

The join predicates are straightforward and restrict each lookup alias to its own lookup type. PT.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE' with PT.LOOKUP_CODE = REL.PROGRAM_TYPE_CODE supplies the type meaning and description, while ST.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_SUBTYPE' with ST.LOOKUP_CODE = REL.PROGRAM_SUBTYPE_CODE supplies the sub-type meaning and description. The view therefore inherits the language sensitivity of FND_LOOKUP_VALUES_VL.

Key Columns

  • PROG_TYPE_SUBTYPE_ID — primary identifier of the type/sub-type relationship row.
  • OBJECT_VERSION_NUMBER — concurrency control column carried through from the base table.
  • PROGRAM_TYPE_CODE and PROGRAM_SUBTYPE_CODE — the stored lookup codes used in the join.
  • PT.MEANING / ST.MEANING — the user-facing meaning of the program type and sub-type lookups respectively.
  • PT.DESCRIPTION / ST.DESCRIPTION — the longer descriptions of each lookup value.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield context and segments defined on the base relationship record.

Note that because both meanings are exposed without aliasing prefixes, consumers should qualify columns when joining the view to other lookup sources.

Common Use Cases and Queries

Typical uses include populating LOVs and report parameters, validating configuration during data migration, and enriching failure/maintenance analytics with readable program classifications. The following query lists each type/sub-type combination with its descriptions:

  • SELECT PROGRAM_TYPE_CODE, MEANING, DESCRIPTION, PROGRAM_SUBTYPE_CODE, PROG_TYPE_SUBTYPE_ID FROM APPS.AHL_PROG_TYPE_SUBTYPES_V ORDER BY PROGRAM_TYPE_CODE, PROGRAM_SUBTYPE_CODE;
  • Filtering by a specific type: SELECT * FROM APPS.AHL_PROG_TYPE_SUBTYPES_V WHERE PROGRAM_TYPE_CODE = :p_type;
  • Retrieving sub-types for an LOV: SELECT PROGRAM_SUBTYPE_CODE, MEANING FROM APPS.AHL_PROG_TYPE_SUBTYPES_V WHERE PROGRAM_TYPE_CODE = :p_type;

Where the descriptive flexfield segments are required, select the relevant ATTRIBUTE columns, taking care that ATTRIBUTE_CATEGORY identifies the context under which those segments are populated.