Results for “product_type_code”

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

Overview

In Oracle E-Business Suite Release 12.1.1 and 12.2.2, AHL.AHL_PRODTYPE_ZONES is a transactional configuration table owned by the AHL schema, the product family for Complex Maintenance Repair and Overhaul (CMRO). It defines the permitted association between a product type, a maintenance zone, and an optional sub zone, forming the routing or applicability matrix that governs where work can be executed for a given asset type. In effect, the table answers the question: for this class of repairable unit, which shop zones and sub zones are valid?

The table is registered in ETRM as a VALID base table with 29 documented columns. Its heuristic Data Vault classification is standalone, meaning the mined foreign-key structure does not resolve it into a hub, link, or satellite pattern. This should be read as a modeling suggestion rather than a normative fact; functionally the row identifier behaves like a hub-style surrogate, while the descriptive and audit attributes behave like satellite columns attached to it.

Key Information Stored

The table has a single-column surrogate primary key, PRODTYPE_ZONE_ID, defined by constraint AHL_PRODTYPE_ZONES_PK and backed by unique index AHL_PRODTYPE_ZONES_U1. Two business-key candidates are documented: the primary constraint above and the unique index AHL_PRODTYPE_ZONES_U2 over (PRODUCT_TYPE_CODE, ZONE_CODE, SUB_ZONE_CODE), which enforces that a given product type cannot be mapped twice to the same zone and sub zone.

• PRODTYPE_ZONE_ID — surrogate primary key; the column most often referenced by dependent tables and used as the lookup target.
• PRODUCT_TYPE_CODE — identifies the class or category of the item being repaired; part of the U2 business key.
• ZONE_CODE — the maintenance zone or shop area where the product type is permitted to be routed.
• SUB_ZONE_CODE — the finer-grained location within a zone; optional in many configurations and part of U2.
• ACTIVE_START_DATE and ACTIVE_END_DATE — the effective-dating window controlling whether an association is currently in force, enabling date-effective resolution of routing rules.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org / data-security filtering.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the AHL forms and OAF pages on update.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF/descriptive flexfield segment columns reserved for customer-defined extensions.

Common Use Cases and Queries

The most frequent access pattern is resolving the active zone/sub zone for a product type, typically joined to zone and product type descriptive tables. A representative query is:

SELECT pz.zone_code, pz.sub_zone_code
FROM ahl_prodtype_zones pz
WHERE pz.product_type_code = :product_type
AND TRUNC(SYSDATE) BETWEEN NVL(pz.active_start_date, TRUNC(SYSDATE))
AND NVL(pz.active_end_date, TRUNC(SYSDATE))
AND pz.security_group_id IN (:security_groups);

Reporting usages include validating that setup matrices contain no gaps or overlaps across effective dates, auditing zone utilisation by product type, and driving routing decisions in CMRO work-order and visit scheduling screens. Because the table is effective-dated, developers should filter on the active window rather than assuming one row per product type.

Related Objects

• FND_SECURITY_GROUPS — referenced via AHL_PRODTYPE_ZONES.SECURITY_GROUP_ID; enforces data-security visibility in multi-org environments.
• AHL_ZONES / AHL_ZONE_B — the zone definition tables providing the zone and sub zone reference data validated by ZONE_CODE and SUB_ZONE_CODE.
• AHL_PRODUCT_TYPES / AHL_PRODUCT_TYPES_B — source of PRODUCT_TYPE_CODE values used as the validated lookup.
• CMRO work-order and visit tables (for example AHL_WORKORDERS and associated visit/operation entities) — consume the product-type-to-zone matrix when routing maintenance activity.
• AHL_PRODTYPE_ZONES_PK / AHL_PRODTYPE_ZONES_U1 / AHL_PRODTYPE_ZONES_U2 — the constraints and unique indexes that guarantee row identity and business-key integrity.

Together these objects make AHL_PRODTYPE_ZONES a compact but central configuration artefact whose integrity directly affects CMRO routing accuracy.