Search Results program_subtype




Overview
Explore in AI Chat

Overview

APPS.AHL_MR_PC_NODES_V is a reporting and integration view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema. It exposes header-level information for Oracle Quality management ("MR") programs, joined to product configuration ("PC") nodes. The view is central to identifying which quality program definitions are effective at the current point in time and how those programs are attached to specific product configuration nodes in the configuration hierarchy.

For users searching on the term "program_subtype," the view is significant because it surfaces the PROGRAM_SUBTYPE_CODE column alongside a decoded PROGRAM_SUBTYPE meaning value. This allows reports and interfaces to distinguish primary program types from their more granular subtypes without requiring callers to separately resolve lookup codes from the FND_LOOKUP_VALUES_VL lookup table.

Underlying Base Objects

The view is defined over four documented objects:

  • AHL_MR_EFFECTIVITIES (SYNONYM) — supplies the PC_NODE_ID and links a header to its configuration node via MR_HEADER_ID.
  • AHL_MR_HEADERS_APP_V (VIEW) — the source of most header attributes, including title, version, status, program type, program subtype, effective dates, revision, and description.
  • FND_LOOKUP_VALUES_VL (VIEW) — the translated lookup values view, joined multiple times to resolve lookup codes into meanings.
  • FND_PROFILE (PACKAGE) — referenced by the view stack for profile-dependent behavior, though not directly joined in the visible text.

The view text joins AHL_MR_HEADERS_APP_V to three aliases of FND_LOOKUP_VALUES_VL: PROGTYPE (lookup type AHL_FMP_MR_PROGRAM_TYPE), SUBTYPE (lookup type AHL_FMP_MR_PROGRAM_SUBTYPE), and STATUS (lookup type AHL_FMP_REVISION_STATUS). It then joins to AHL_MR_EFFECTIVITIES on MR_HEADER_ID to retrieve the PC node reference.

Key Columns

The view returns the following columns:

  • MR_HEADER_ID — primary identifier of the quality program header.
  • OBJECT_VERSION_NUMBER — optimistic locking / concurrency control column.
  • TITLE, VERSION_NUMBER, REVISION, DESCRIPTION — descriptive attributes of the program.
  • MR_STATUS_CODE and STATUS — the coded status and its decoded meaning from the AHL_FMP_REVISION_STATUS lookup.
  • PROGRAM_TYPE_CODE and PROGRAM_TYPE — the coded program type and its decoded meaning.
  • PROGRAM_SUBTYPE_CODE and PROGRAM_SUBTYPE — the coded subtype and its decoded meaning from AHL_FMP_MR_PROGRAM_SUBTYPE. This is the column pair most relevant to searches on "program_subtype." Note that the subtype join is an outer join (the (+) operator appears on the SUBTYPE side), so headers without a subtype still appear with a null meaning.
  • EFFECTIVE_FROM and EFFECTIVE_TO — date range for which the program is effective.
  • PC_NODE_ID — identifier of the associated product configuration node.

Common Use Cases and Queries

The most common use is a filtered listing of currently effective quality programs grouped by subtype or linked to configuration nodes. Because the view enforces the predicate SYSDATE <= NVL(MR.EFFECTIVE_TO, SYSDATE), only programs that have not yet expired are returned. The following query lists active programs of a given subtype attached to configuration nodes:

  • SELECT MR_HEADER_ID, TITLE, PROGRAM_TYPE, PROGRAM_SUBTYPE, PC_NODE_ID, EFFECTIVE_FROM, EFFECTIVE_TO FROM APPS.AHL_MR_PC_NODES_V WHERE PROGRAM_SUBTYPE_CODE = :p_subtype ORDER BY EFFECTIVE_FROM DESC;
  • SELECT PROGRAM_TYPE, PROGRAM_SUBTYPE, COUNT(*) FROM APPS.AHL_MR_PC_NODES_V GROUP BY PROGRAM_TYPE, PROGRAM_SUBTYPE;
  • SELECT MR_HEADER_ID, TITLE, PC_NODE_ID FROM APPS.AHL_MR_PC_NODES_V WHERE MR_STATUS_CODE = :p_status;

Because the view is fully decoded, it is well suited to ad-hoc reporting, concurrent program data sources, and integration feeds that publish quality program metadata to downstream configuration systems. The outer join on subtype makes it safe for reports that must include programs lacking a subtype assignment.