Search Results po_un_numbers




Overview
Explore in AI Chat

Overview

APPS.POR_UN_NUMBER_LOV_V is a Purchasing (PO) module view that exposes a validated list of United Nations (UN) hazardous material numbers, paired where applicable with their corresponding hazard classification. Its primary function is to serve as the data source behind the UN Number list of values (LOV) in the Oracle E-Business Suite Purchasing and Receiving user interfaces, most notably on the shipment and delivery hazard information forms. Although it is catalogued as a view rather than a concurrent program, it plays a comparable role in supporting reporting and integration: any custom inquiry, Discoverer workbook, or interface that must present or validate a UN number can query it directly instead of re-implementing the underlying filter logic.

The view is owned by the APPS schema and is documented as a synonym-based definition in ETRM 12.2.2, and the same object is present in 12.1.1. Because it performs the selection of active (non-inactive-dated) UN numbers, it shields the caller from having to apply the date and hazard-class join conditions that the parent tables require.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as APPS synonyms:

  • PO_UN_NUMBERS — the master list of UN numbers, including each record's optional hazard class association and its INACTIVE_DATE.
  • PO_HAZARD_CLASSES — the master hazard classification definitions, also governed by an INACTIVE_DATE.

The view text is a UNION of two branches. The first branch joins PO_UN_NUMBERS to PO_HAZARD_CLASSES on HAZARD_CLASS_ID, returning the UN number together with the Hazard Class description for those records where both the hazard class and the UN number are still active as of the current system date. The second branch selects UN numbers whose HAZARD_CLASS_ID is null (tested against the sentinel value -9999), returning the UN number with a blank hazard class. Both branches apply the same activity test, SYSDATE < NVL(INACTIVE_DATE, SYSDATE+1), which effectively treats a null INACTIVE_DATE as permanently active.

Key Columns

  • UN_NUMBER — the unique numeric identifier assigned by the United Nations for a regulated hazardous substance or article; this is the value surfaced to the user in the LOV.
  • HAZARD_CLASS — the descriptive hazard classification associated with the UN number, drawn from PO_HAZARD_CLASSES. It is populated only when a valid, active hazard class link exists; otherwise the view returns an empty string for this column.

No other columns are exposed, so consumers requiring the internal primary keys (UN_NUMBER_ID or HAZARD_CLASS_ID) must join back to the underlying tables. The absence of those identifiers is by design, as the view exists to drive a user-facing selection rather than to act as a fully normalized reporting source.

Common Use Cases and Queries

Typical scenarios include custom hazard detail reports, data conversion validation for shipment lines, and ad hoc lookups during controlled-substance analysis. A representative query is:

  • SELECT un_number, hazard_class FROM apps.por_un_number_lov_v WHERE un_number = :p_un_number;
  • SELECT un_number, hazard_class FROM apps.por_un_number_lov_v ORDER BY un_number;
  • SELECT v.un_number, v.hazard_class FROM apps.por_un_number_lov_v v, apps.po_line_locations_all l WHERE l.un_number = v.un_number AND l.po_header_id = :p_header_id;

Because the view filters on SYSDATE at execution time, results can change as UN numbers or hazard classes are inactivated. Reports intended to reproduce historical shipment data should therefore join to the base tables and apply their own effective-dating logic rather than relying on this view alone.