Results for “ahl_prodtype_zones_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view APPS.AHL_PRODTYPE_ZONES_V is a reporting and integration construct within the Oracle E-Business Suite Complex Maintenance Repair and Overhaul (AHL) product. Its principal purpose is to expose the association between product types and the maintenance zones (and sub-zones) that apply to them, while simultaneously resolving all coded values into their human-readable meanings and descriptions. The base transaction data resides in the entity table AHL_PRODTYPE_ZONES, which stores internal lookup codes only. Rather than forcing downstream consumers to join repeatedly against the lookup views, AHL_PRODTYPE_ZONES_V performs those joins once and surfaces descriptive text columns such as ZONE_DESCRIPTION directly.

The view is defined with the owner/schema APPS and holds a VALID status in release 12.1.1 and 12.2.2. Its presence in the ETRM dictionary confirms it is a supported, documented object suitable for use in custom reports, concurrent programs, and integration interfaces. Because it joins the lookup view FND_LOOKUP_VALUES_VL three times, it is read-only by design; it carries no DML semantics and should never be treated as a data-entry surface.

Underlying Base Objects

The view is defined over the following documented objects:

• AHL_PRODTYPE_ZONES (SYNONYM) — the principal entity table holding product-type-to-zone assignments, along with standard WHO columns (creation/update audit fields) and descriptive flexfield attribute columns.
• FND_LOOKUP_VALUES_VL (VIEW) — the standard Oracle lookup view, referenced three times with the aliases PRODTY, ZONE, and SUBZONE to translate the PRODUCT_TYPE_CODE, ZONE_CODE, and SUB_ZONE_CODE columns respectively.

Critically, all three lookup joins are implemented as outer joins (denoted by the (+) operator). Lookup types joined are ITEM_TYPE for the product type, AHL_ZONE for the zone, and AHL_SUB_ZONE for the sub-zone. This guarantees that a row in AHL_PRODTYPE_ZONES is never dropped merely because a corresponding lookup definition is missing or has been disabled; the code column will still be populated while the descriptive columns remain null.

Key Columns

• PRODTYPE_ZONE_ID — primary key of the underlying association record.
• OBJECT_VERSION_NUMBER — optimistic locking token inherited from the base table.
• PRODUCT_TYPE_CODE — stored code referencing lookup type ITEM_TYPE.
• PROD_TYPE_MEANING / PROD_TYPE_DESCRIPTION — meaning and description resolved from FND_LOOKUP_VALUES_VL.
• ZONE_CODE — stored code referencing lookup type AHL_ZONE.
• ZONE_MEANING / ZONE_DESCRIPTION — resolved zone text; ZONE_DESCRIPTION is the column most commonly searched by users.
• SUB_ZONE_CODE — stored code referencing lookup type AHL_SUB_ZONE.
• SUB_ZONE_MEANING / SUB_ZONE_DESCRIPTION — resolved sub-zone text.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield segments passed through unchanged.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.

Note that the view text aliases PRODTY.MEANING and PRODTY.DESCRIPTION to PROD_TYPE_MEANING and PROD_TYPE_DESCRIPTION, while the ETRM column listing shows normalized short forms (PRODUCT_TYPE, PRODUCT_TYPE_DESC, ZONE, ZONE_DESC, SUB_ZONE, SUB_ZONE_DESC). Developers should confirm the exact column names against their instance's dictionary before hard-coding references.

Common Use Cases and Queries

The most frequent requirement is to report product-type assignments with readable zone and sub-zone text. A representative query is:

• SELECT product_type_code, prod_type_description, zone_code, zone_description, sub_zone_code, sub_zone_description FROM apps.ahl_prodtype_zones_v ORDER BY product_type_code, zone_code;

To return only zones of a particular product type:

• SELECT zone_code, zone_description FROM apps.ahl_prodtype_zones_v WHERE product_type_code = :p_product_type;

To audit records whose descriptive text failed to resolve (indicating a missing or disabled lookup):

• SELECT prodtype_zone_id, zone_code FROM apps.ahl_prodtype_zones_v WHERE zone_description IS NULL;

Because the join to FND_LOOKUP_VALUES_VL is an outer join, the last query is safe and will surface genuine configuration gaps rather than hiding them. When building integrations, filter on LAST_UPDATE_DATE to extract incremental changes reliably.