Results for “po_un_numbers_b”

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

Overview

PO_UN_NUMBERS_B is the Purchasing (PO) module base table that stores United Nations (UN) numbers in Oracle E-Business Suite 12.1.1 and 12.2.2. UN numbers are four-digit identifiers assigned to hazardous substances and articles for transport classification. Within EBS they provide the regulatory code recorded against items and purchasing documents so that hazardous-material handling, transportation, and customs documentation can be generated correctly.

The table is owned by the PO schema and is documented as VALID in ETRM 12.2.2 with 28 columns. It participates in a strict parent-child relationship: it holds the foreign key HAZARD_CLASS_ID to PO_HAZARD_CLASSES_B, and it is referenced by a wide set of transactional and interface tables. From a Data Vault modeling perspective, the mined FK structure suggests treating this object as a hub-leaning candidate: the primary key UN_NUMBER_ID is a stable surrogate identifier, the hazard class membership is a small set of attribute-like references, and the descriptive columns behave as reference data rather than high-volume transactional events.

Key Information Stored

The table is keyed by UN_NUMBER_ID, which is enforced through two documented unique constraints: the primary key PO_UN_NUMBERS_B_PK and the unique index PO_UN_NUMBERS_B_U1. Both are defined on UN_NUMBER_ID, so this column is simultaneously the surrogate primary key and the only documented business-key candidate. The most significant columns are:

• UN_NUMBER_ID — surrogate primary key and the value propagated to all referencing tables.
• HAZARD_CLASS_ID — foreign key to PO_HAZARD_CLASSES_B, linking the UN number to its hazard classification.
• INACTIVE_DATE — effective end date used to retire a UN number without deleting the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS audit trail columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context that identifies the request and program that last modified the record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns, available for client-specific hazardous-material attributes.

The descriptive UN number value itself is stored in the _TL (translated) companion table; PO_UN_NUMBERS_B holds only the identifier, classification, and control attributes.

Common Use Cases and Queries

Typical usage falls into three areas. First, validating and resolving a UN number when purchasing hazardous items. Second, reporting hazardous-material content across purchase orders, requisitions, and items. Third, data migration through the interface tables.

A common lookup joins the base table to its translated table and hazard class:

• SELECT b.un_number_id, t.un_number, b.hazard_class_id FROM po_un_numbers_b b, po_un_numbers_tl t, po_hazard_classes_b h WHERE b.un_number_id = t.un_number_id AND b.hazard_class_id = h.hazard_class_id AND b.inactive_date IS NULL;
• To find all purchasing documents referencing a given UN number: SELECT pl.po_header_id, pl.po_line_id, pl.un_number_id FROM po_lines_all pl WHERE pl.un_number_id = :un_number_id;
• To identify active hazardous items: SELECT msi.inventory_item_id, msi.un_number_id FROM mtl_system_items_b msi WHERE msi.un_number_id IS NOT NULL;

The UN_NUMBER_ID is also carried on PO_REQUISITION_LINES_ALL, PO_LINES_INTERFACE, and po_lines_draft_all, making it straightforward to trace a UN number from requisition through draft and archive to the final order line.

Related Objects

The following objects have the most significant dependencies on PO_UN_NUMBERS_B:

• PO_HAZARD_CLASSES_B — referenced by PO_UN_NUMBERS_B.HAZARD_CLASS_ID; parent of the hazard classification.
• PO_LINES_ALL — references UN_NUMBER_ID; carries the UN number on order lines.
• PO_REQUISITION_LINES_ALL — references UN_NUMBER_ID; requisition-level hazardous material.
• PO_LINES_INTERFACE and PO_REQUISITIONS_INTERFACE_ALL — reference UN_NUMBER_ID; used for inbound data loading and validation.
• MTL_SYSTEM_ITEMS_B — references UN_NUMBER_ID; item master hazardous classification.
• PO_LINES_DRAFT_ALL and PO_LINES_ARCHIVE_ALL — reference UN_NUMBER_ID; draft and archived line storage.
• EGO_MTL_SY_ITEMS_CHG_B and MTL_SYSTEM_ITEMS_INTERFACE — reference UN_NUMBER_ID; item change and interface processing.

Because so many procurement and inventory objects reference this table, changes to a UN number's hazard class or active status can propagate widely, and any update should be coordinated with dependent transactional data.