Search Results po_un_numbers_b_pk

Overview

The PO_UN_NUMBERS_B table is a core reference table within the Oracle E-Business Suite Purchasing (PO) module, versions 12.1.1 and 12.2.2. It serves as the master repository for United Nations (UN) numbers, which are internationally recognized four-digit codes used to identify hazardous substances and articles during transportation. This table's primary role is to provide a standardized, validated list of UN numbers that can be associated with items and purchasing documents to ensure compliance with safety regulations and accurate material classification for logistics and handling purposes.

Key Information Stored

While the provided metadata does not list specific columns beyond the keys, the table's structure is designed to catalog UN number definitions. The primary column is UN_NUMBER_ID, which serves as the unique internal primary key (PO_UN_NUMBERS_B_PK). A critical attribute is the HAZARD_CLASS_ID, a foreign key linking to the PO_HAZARD_CLASSES_B table, which classifies the type of hazard associated with the UN number (e.g., flammable liquids, toxic substances). The table likely contains columns for the UN number code itself (e.g., '1263' for Paint), a description, and standard audit columns such as CREATION_DATE and LAST_UPDATE_DATE. This structure centralizes hazardous material identification data for consistent application across the supply chain.

Common Use Cases and Queries

A primary use case is the association of a UN number with an item in the Inventory module, enabling automatic propagation of hazard information to purchasing and shipping documents. Common queries involve joining this table to item or purchase order data for compliance reporting. For instance, to list all inventory items with their associated UN number and hazard class, a query would join MTL_SYSTEM_ITEMS_B to PO_UN_NUMBERS_B and then to PO_HAZARD_CLASSES_B. Another critical scenario is validating UN numbers during the requisition or purchase order entry process via the interface tables (PO_REQUISITIONS_INTERFACE_ALL, PO_LINES_ALL). Sample SQL to find UN numbers for active purchase order lines would be: SELECT pol.line_num, pol.item_description, un.* FROM po_lines_all pol JOIN po_un_numbers_b un ON pol.un_number_id = un.un_number_id WHERE pol.po_header_id = :p_po_id;

Related Objects

The PO_UN_NUMBERS_B table is integral to the hazardous material data model, with documented relationships to several key tables. It references the PO_HAZARD_CLASSES_B table via its HAZARD_CLASS_ID column. More significantly, it is referenced as a parent table by multiple core transaction tables, establishing its role as a master source. These referencing tables and their join columns are:

• MTL_SYSTEM_ITEMS_B.UN_NUMBER_ID
• PO_LINES_ARCHIVE_ALL.UN_NUMBER_ID
• PO_LINES_ALL.UN_NUMBER_ID
• PO_REQUISITIONS_INTERFACE_ALL.UN_NUMBER_ID
• PO_REQUISITION_LINES_ALL.UN_NUMBER_ID

This network of foreign keys ensures that the UN number data is consistently applied from item master records through requisitions, purchase orders, and their archival copies, maintaining data integrity for regulatory tracking.