Search Results un_number_id




Overview
Explore in AI Chat

Overview

The PO_UN_NUMBERS_TL table is a core translation table within the Oracle E-Business Suite Purchasing (PO) module. It stores translated descriptions for United Nations (UN) numbers, which are standardized numerical identifiers for hazardous substances and articles in international transport. This table enables the EBS application to present UN number descriptions in the language of the user, supporting global deployments. Its role is to provide a localized reference for the hazardous goods classification system used in procurement and shipping documentation, ensuring regulatory compliance and clear communication across multilingual environments.

Key Information Stored

The table's structure is designed to support multilingual content with a unique key combining a substance identifier and a language code. The primary columns, as defined by the documented primary and unique keys, are:

  • UN_NUMBER_ID: The unique identifier for the UN number record, serving as the foreign key link to the base data table (e.g., PO_UN_NUMBERS_B).
  • UN_NUMBER: The standardized numerical code (e.g., 1203 for Motor spirit or Gasoline).
  • DESCRIPTION: The translated textual description of the hazardous substance or article corresponding to the UN_NUMBER.
  • LANGUAGE: The ISO code for the language of the translated DESCRIPTION (e.g., 'US' for American English).

The table's primary key constraint (PO_UN_NUMBERS_TL_PK) is on UN_NUMBER_ID and LANGUAGE, ensuring one translation per language per ID. A unique key constraint (PO_UN_NUMBERS_TL_UK1) on UN_NUMBER, DESCRIPTION, and LANGUAGE ensures data integrity for the translated values themselves.

Common Use Cases and Queries

This table is primarily accessed for generating localized reports, user interface displays, and compliance documents. A common scenario is retrieving the description of a specific UN number for a user session's language within a shipping or purchasing document. Sample SQL to achieve this involves joining the translation table with its base table, filtered by the session language.

Sample Query: To fetch all UN numbers and their descriptions for a specific language (e.g., French 'F'):

SELECT b.un_number, tl.description
FROM po_un_numbers_b b,
     po_un_numbers_tl tl
WHERE b.un_number_id = tl.un_number_id
  AND tl.language = USERENV('LANG')
ORDER BY b.un_number;

Reporting use cases include creating multi-language safety data sheets, hazardous material manifests, and supplier communication regarding regulated goods.

Related Objects

PO_UN_NUMBERS_TL is intrinsically linked to other Purchasing tables. Its most critical relationship is with the base table PO_UN_NUMBERS_B, which holds the language-independent UN number data. The UN_NUMBER_ID column is the foreign key to this base table. This translation table is also referenced by any EBS entity that requires a localized UN number description, such as items classified as hazardous (via MTL_SYSTEM_ITEMS_B), purchase order lines, and shipping transaction interfaces. The data is typically accessed via public APIs or standard EBS forms rather than through direct DML on the table.