Results for “po_hazard_classes_tl”

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

Overview

PO_HAZARD_CLASSES_TL is the translation (TL) table for hazardous material classifications used within the Oracle Purchasing (PO) module of Oracle E-Business Suite. In EBS releases 12.1.1 and 12.2.2, hazardous classes identify regulated substances and dangerous goods that must be tracked on purchase order lines, shipments, and receipts for transportation, safety, and compliance purposes. The base table PO_HAZARD_CLASSES_B stores the language-independent definition of each hazard class, while PO_HAZARD_CLASSES_TL stores the language-dependent descriptive text. Records are joined on the pair (HAZARD_CLASS_ID, LANGUAGE) following the standard Oracle Multi-Language Support (MLS) pattern. From a heuristic Data Vault modeling perspective, this object most closely resembles a satellite table, since it carries descriptive, versioned-in-language attributes attached to a hub key (HAZARD_CLASS_ID). It is classified as standalone in the documented relationship data, with no foreign key dependencies recorded among the ETRM metadata.

Key Information Stored

The table contains ten documented columns, organized around a surrogate-across-language primary key and a business-key candidate. The most important are:

• HAZARD_CLASS_ID — Surrogate identifier linking each translated row back to the base hazard class definition.
• LANGUAGE — The NLS language code (e.g., US, DE, FR) in which the translated row is stored.
• SOURCE_LANG — The language of the source row from which a translation was created; used by the MLS/translation tooling.
• HAZARD_CLASS — The translated hazard class name or code displayed to users.
• DESCRIPTION — The translated descriptive text for the hazard class, used in reports and purchase documents.
• CREATION_DATE, CREATED_BY — Audit fields recording initial insert.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Audit fields capturing the most recent modification and the login that performed it.

PO_HAZARD_CLASSES_TL_PK is defined on (HAZARD_CLASS_ID, LANGUAGE), guaranteeing one translated row per class-language pair. A unique index, PO_HAZARD_CLASSES_TL_U1, is also defined on (HAZARD_CLASS_ID, LANGUAGE), reinforcing the same pairing. The business-key candidate documented as PO_HAZARD_CLASSES_TL_UK1 is (HAZARD_CLASS, LANGUAGE), which enforces that a given translated hazard class name is unique within a language. Because HAZARD_CLASS is the natural business identifier, it is the candidate that most closely parallels the primary key. The combination HAZARD_CLASS_ID and LANGUAGE is thus both the physical PK and the logical join key to the base table.

Common Use Cases and Queries

The primary reporting use case is retrieving translated hazard descriptions for purchase documents and regulatory reports. A typical query joins the base and translation tables and filters by the session language or a specific requested language:

• SELECT b.hazard_class_id, t.hazard_class, t.description FROM po.po_hazard_classes_b b, po.po_hazard_classes_tl t WHERE b.hazard_class_id = t.hazard_class_id AND t.language = USERENV('LANG');
• Lookups to validate that a hazard class entered on a purchase order line exists and has a translation in the user's language.
• Extracts for dangerous-goods declarations, shipping documentation, and MSDS references, using DESCRIPTION to populate printed forms.
• Data-quality checks identifying classes that have no translation for a required language: SELECT b.hazard_class_id FROM po_hazard_classes_b b WHERE NOT EXISTS (SELECT 1 FROM po_hazard_classes_tl t WHERE t.hazard_class_id = b.hazard_class_id AND t.language = 'US');
• Audit queries comparing CREATION_DATE and LAST_UPDATE_DATE to track when translations were added or revised.

Because hazard class data changes infrequently, these queries are generally low-volume and suitable for concurrent-program or report-level extraction.

Related Objects

The most significant related objects follow from the MLS pairing and the surrounding Purchasing hazard functionality:

• PO_HAZARD_CLASSES_B — The base (language-independent) table joined on HAZARD_CLASS_ID.
• PO_HAZARD_CLASSES_TL — Self-reference via SOURCE_LANG and LANGUAGE for translation management.
• PO_LINE_LOCATIONS_ALL — Purchase order shipment lines that may reference hazard classes.
• PO_HEADERS_ALL / PO_LINES_ALL — Purchase order header and line tables where hazardous indicators appear.
• RCV_SHIPMENT_HEADERS / RCV_SHIPMENT_LINES — Receiving transactions carrying hazard information.
• MTL_SYSTEM_ITEMS_B — Item definitions that can carry hazardous material classification attributes.
• FND_LANGUAGES — Reference table for valid LANGUAGE and SOURCE_LANG values.
• PO_HAZARD_CLASSES_TL_UK1 / PO_HAZARD_CLASSES_TL_U1 — Unique indexes supporting lookup and duplicate prevention.

Together these objects support the entry, translation, and enforcement of hazardous material data across purchasing and receiving flows.