Results for “un_number”

50+ results




Overview
Explore in AI Chat

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

Overview

The POR_UN_NUMBER_LOV_V view is a public Oracle E-Business Suite database object owned by the APPS schema and registered under the ICX (Oracle iProcurement) product family. It is defined as a list of values (LOV) view for hazard identification numbers, supplying the valid United Nations (UN) numbers and their associated hazard classification codes that may be presented to users during requisition entry, item definition, or hazardous material handling within iProcurement and related procurement flows.

The view is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its role is primarily presentational: rather than exposing the complete hazard configuration tables, it filters for currently active records and denormalizes the relationship between a UN number and its hazard class into a two-column result set suitable for a value-set-backed LOV. Because it is a view, no data is physically stored; all rows are resolved at query time against the underlying base objects. Systems that query this object in reports or integrations receive only active hazard entries, which simplifies downstream logic by removing the need to re-apply inactivation predicates.

Underlying Base Objects

According to the documented ETRM metadata, the view is defined over two synonyms: PO_HAZARD_CLASSES and PO_UN_NUMBERS. These synonyms resolve to the corresponding base tables in the purchasing schema. The relationship is keyed on HAZARD_CLASS_ID: each UN number row in PO_UN_NUMBERS may reference a hazard class in PO_HAZARD_CLASSES through that column.

The view text applies two inactivation filters. Records are included only when SYSDATE is less than NVL(INACTIVE_DATE, SYSDATE+1), meaning rows with a null inactive date are treated as permanently active. The same predicate is applied independently to both the hazard class and the UN number, so a row is returned only when both sides remain active. The definition is a UNION of two branches:

  • The first branch joins PO_HAZARD_CLASSES to PO_UN_NUMBERS on HAZARD_CLASS_ID, returning the UN number together with its hazard class.
  • The second branch selects UN numbers whose HAZARD_CLASS_ID is null (tested via NVL(..., -9999) = -9999) and returns an empty string for the hazard class.

This design ensures that UN numbers lacking an assigned hazard class are still available in the LOV, with a placeholder value in the hazard class column.

Key Columns

  • UN_NUMBER — The United Nations hazard identification number drawn from PO_UN_NUMBERS.UN_NUMBER. This is the primary value presented to the user and is the natural key for selecting a hazardous material identifier.
  • HAZARD_CLASS — The hazard classification drawn from PO_HAZARD_CLASSES.HAZARD_CLASS. For UN numbers with no associated hazard class, the view returns an empty string rather than null, preserving a consistent two-column shape for LOV rendering.

Because only two columns are exposed, the view is intentionally narrow. It does not surface identifiers, dates, or descriptive attributes from the base tables, which keeps the LOV payload small and focused on user selection.

Common Use Cases and Queries

Typical scenarios include populating a hazard number LOV on a purchasing or iProcurement form, validating a user-supplied UN number during interface loading, and driving reports that must list active hazardous materials with their classifications. A representative query follows:

  • SELECT un_number, hazard_class FROM apps.por_un_number_lov_v ORDER BY un_number; — retrieves the full active LOV contents.
  • SELECT un_number, hazard_class FROM apps.por_un_number_lov_v WHERE un_number = :p_un_number; — validates a specific hazard number.
  • SELECT hazard_class, COUNT(*) FROM apps.por_un_number_lov_v GROUP BY hazard_class; — summarizes UN numbers by hazard class, including the empty-string group for unclassified entries.
  • SELECT un_number FROM apps.por_un_number_lov_v WHERE hazard_class = :p_class; — lists identifiers belonging to a chosen hazard classification.

Consumers should note that the view reflects SYSDATE at execution time, so results change automatically as records are inactivated or reactivated. Joins back to the base tables should use UN_NUMBER or HAZARD_CLASS cautiously, since these values are not guaranteed unique across both UNION branches.