Search Results po_hazard_classes




Overview
Explore in AI Chat

Overview

The POR_HAZARD_CLASS_LOV_V view is a documented dictionary object in the APPS schema of Oracle E-Business Suite, owned by the Applications product set and catalogued under the ICX — Oracle iProcurement module. As its name and ETRM description indicate, it supplies a list of values (LOV) for hazard classes used in purchasing and iProcurement flows. Hazard classes are regulatory groupings applied to items and shipments that carry dangerous goods, and UN numbers identify the specific substance or article within a hazard class. This view joins those two attributes into a single, query-ready row so that self-service requisitioning, catalog search, and receiving pages can present valid, current hazard class and UN number combinations to end users.

Because it is a view rather than a table, POR_HAZARD_CLASS_LOV_V holds no data of its own. Its value lies in the filtering logic embedded in its definition: only active hazard classes and active UN numbers are surfaced to the caller. This makes it suitable both for user-facing LOVs and for integration or reporting queries that need a compact, always-current enumeration of hazard class records. Within EBS 12.1.1 and 12.2.2 the object has VALID status in the APPS schema, and its columns are referenced in the ETRM dictionary as HAZARD_CLASS and UN_NUMBER.

Underlying Base Objects

The view is defined over two base objects, both exposed to APPS through synonyms:

The two tables are joined on PHC.HAZARD_CLASS_ID = PUN.HAZARD_CLASS_ID, establishing a one-to-many relationship in which each hazard class may be associated with multiple UN numbers. The join is an inner join, so a hazard class will not appear unless it has at least one matching UN number record. Filtering is applied symmetrically to both sides:

SYSDATE < NVL(PHC.INACTIVE_DATE, SYSDATE+1) AND SYSDATE < NVL(PUN.INACTIVE_DATE, SYSDATE+1)

Because INACTIVE_DATE is nullable, the NVL expression substitutes SYSDATE+1 when no inactivation date is recorded. This effectively treats rows with a null INACTIVE_DATE as perpetually active, while rows that have been logically retired drop out of the view as soon as system date passes the recorded date. The design is a common EBS pattern for soft-deleted or versioned reference data.

Key Columns

Only two columns are projected, reflecting the view's narrow LOV purpose:

  • HAZARD_CLASS — the descriptive name or code of the hazard classification (for example, a regulatory class label). This is the user-visible value presented to the operator and the natural search key.
  • UN_NUMBER — the United Nations identification number assigned to the hazardous substance or article associated with that hazard class. In a typical LOV, this is the value returned to the calling form or page for downstream validation.

Neither the surrogate HAZARD_CLASS_ID nor either INACTIVE_DATE is exposed. Consumers who require the identifier for foreign-key lookup must query the base tables directly or join back to PO_HAZARD_CLASSES. The intentional omission keeps the LOV lean and prevents accidental dependency on internal keys.

Common Use Cases and Queries

The primary scenario is populating a hazard class selection field during iProcurement requisition entry, catalogue administration, or receiving of hazardous items. Because the view already filters inactive records, callers do not need to duplicate that logic. A straightforward lookup of all active hazard class and UN number pairs is:

SELECT hazard_class, un_number
FROM apps.por_hazard_class_lov_v
ORDER BY hazard_class, un_number;

To constrain a specific classification — for example, when a requisition line is tied to a known hazard class — a bind variable can be applied to the first column:

SELECT hazard_class, un_number
FROM apps.por_hazard_class_lov_v
WHERE hazard_class = :p_hazard_class;

Reporting users sometimes need to reconstruct the validation performed by the view, including the inactive-date logic, to audit which records were suppressed on a given date. In that case the base tables should be queried with the same predicates. It is also common to use the view as the driving set in a more complex join, for instance linking UN numbers to item master or shipping attributes, because the view guarantees that both the hazard class and its UN number are presently valid. In all deployments the view should be referenced through the APPS synonym, and because its definition relies on SYSDATE, results vary with the effective database date.