Results for “po_un_numbers_val_v”

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

Overview

PO_UN_NUMBERS_VAL_V is an APPS-owned database view in the Oracle E-Business Suite Purchasing (PO) module. Its ETRM description carries the notation "10SC ONLY - Retrofitted," indicating that this object originated in an earlier Oracle release (10SC) and was retained and retrofitted into the 12.1.1 and 12.2.2 code lines for backward compatibility rather than being a newly engineered 12.x object. The view presents validated United Nations (UN) numbers — the standardized hazardous-material identifiers assigned to dangerous goods — together with their associated hazard classification where one exists.

The name suffix "VAL_V" signals that the view returns only "valid" or currently effective records. The WHERE predicates filter out any UN number whose INACTIVE_DATE has already passed, using the idiom SYSDATE < NVL(PUN.INACTIVE_DATE, SYSDATE + 1), so rows with a NULL inactive date are always treated as active. Its role in reporting and integration is to provide a single, filtered lookup source that external interfaces, shipping or dangerous-goods documents, and custom concurrent programs can query to obtain the current set of UN numbers and, where available, the hazardous class description. In practice it functions as a validation/value-set style view alongside the base maintenance table PO_UN_NUMBERS.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms in the ETRM metadata: PO_UN_NUMBERS (SYNONYM) and PO_HAZARD_CLASSES (SYNONYM). PO_UN_NUMBERS is the primary maintenance table holding the UN number, description, hazard class reference, and active/inactive dates. PO_HAZARD_CLASSES supplies the hazard class code and its identifier.

The view text is a UNION of two branches. The first branch performs an inner join between PO_UN_NUMBERS (aliased PUN) and PO_HAZARD_CLASSES (aliased POH) on HAZARD_CLASS_ID, and requires PUN.HAZARD_CLASS_ID to be populated. The second branch reads PO_UN_NUMBERS alone for records where HAZARD_CLASS_ID IS NULL, returning a NULL for the hazard class column. Both branches apply the same SYSDATE/INACTIVE_DATE currency filter. This structure guarantees that every active UN number appears exactly once — once with a resolved hazard class, or once with a NULL hazard class when no classification is assigned.

Key Columns

• UN_NUMBER — The UN identifier code for the hazardous material, the primary business key of the row.
• DESCRIPTION — The descriptive text for the UN number, taken from PO_UN_NUMBERS.
• HAZARD_CLASS — The hazard class code sourced from PO_HAZARD_CLASSES; returns NULL when the UN number has no HAZARD_CLASS_ID.
• HAZARD_CLASS_ID — The foreign key to the hazard class definition. In the joined branch it equals POH.HAZARD_CLASS_ID; in the unclassified branch it echoes PO_UN_NUMBERS.HAZARD_CLASS_ID (NULL).
• UN_NUMBER_ID — The system-generated primary key from PO_UN_NUMBERS, useful for joins back to the base table.

Common Use Cases and Queries

The view is typically used to populate lists of currently available UN numbers on shipping or dangerous-goods entry forms, and by concurrent programs that must validate a UN number at the time of transaction. Because it already excludes inactive records, consumer code need not repeat the date logic.

Retrieve all active UN numbers with their hazard class:

SELECT un_number, description, hazard_class, hazard_class_id
FROM   apps.po_un_numbers_val_v
ORDER  BY un_number;

Find active UN numbers that lack a hazard classification:

SELECT un_number, description
FROM   apps.po_un_numbers_val_v
WHERE  hazard_class IS NULL;

Look up a specific UN number during validation, joining back to the base table if additional attributes are needed:

SELECT v.un_number, v.description, v.hazard_class, p.inactive_date
FROM   apps.po_un_numbers_val_v v,
       apps.po_un_numbers     p
WHERE  v.un_number_id = p.un_number_id
AND    v.un_number    = :un_number;

As with any "retrofitted" legacy object, consumers should confirm availability in the specific applied patch set before depending on it in new customizations.