Results for “city_state_zip”

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

Overview

ICX_PO_LOCATION_DETAIL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the ICX product family — Oracle iProcurement — and its documented description is "Location Detail View." The view exists to present a pre-formatted, denormalized representation of an employee location record, collapsing the individual address components held on the HR_LOCATIONS entity into a single, presentation-ready tuple. Rather than requiring a reporting or integration layer to reconstruct a city/state/postal string from raw columns, the view exposes the derived element CITY_STATE_ZIP alongside the discrete address lines and country.

The object is registered in ETRM with a status of VALID. Its principal consumers are Oracle iProcurement screens, concurrent programs, and third-party integrations that need to display or transmit a ship-to, deliver-to, or billing location in a compact format. Because the view is defined over HR_LOCATIONS, it is restricted to locations that are valid within the HR location hierarchy, which means employees' organization locations and internal addresses are the natural scope of the data. The view is read-only by construction; it carries no DML semantics and imposes no materialized storage.

Underlying Base Objects

The documented referenced base objects are HR_LOCATIONS (VIEW) and HR_GENERAL (PACKAGE). The view text references only HR_LOCATIONS directly, aliased as HRL in the SELECT statement. HR_GENERAL is a package in the HR schema and is registered in the dependency chain, reflecting the fact that HR_LOCATIONS is itself a view defined over HR tables with a security/effective-dating predicate applied via HR_GENERAL. Consequently, ICX_PO_LOCATION_DETAIL_V inherits the row-level filtering that HR_LOCATIONS applies, so the same rows visible in HR_LOCATIONS are the rows returned here.

The projection is a simple, non-aggregated SELECT over the single source view, meaning the cost of the query is dominated by the HR_LOCATIONS access path and any security predicate evaluated within it. There is no join, no group by, and no union.

Key Columns

• LOCATION_ID — Surrogate primary key of the location; the join key back to HR_LOCATIONS, PO_LOCATION_ASSOCIATIONS, and related sourcing entities.
• LOCATION_CODE — The user-assigned short code for the location, typically the value shown in iProcurement location pickers.
• ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3 — The three discrete street address lines. In the view text these are exposed as ADDRESS_LINE_1/2/3, though ETRM's column listing records them without the underscore between LINE and the digit; implementations should verify the actual column name against ALL_TAB_COLUMNS in their patch level.
• CITY_STATE_ZIP — The derived concatenation: TOWN_OR_CITY followed by a comma, then a state code, then a comma, then POSTAL_CODE. The state component is resolved using a DECODE on HRL.STYLE: when STYLE equals 'US', REGION_2 is used; otherwise REGION_1 is used. This makes the column format-appropriate for US and non-US addresses within a single string, and is the reason users search for "city_state_zip".
• COUNTRY — The country code from HR_LOCATIONS, useful for filtering or for rendering the final line of a full address block.

Common Use Cases and Queries

The most frequent use is populating a formatted address on a requisition, purchase order, or receipt document without duplicating the DECODE logic in application code. A typical lookup by code is:

SELECT location_id, location_code, city_state_zip, country FROM apps.icx_po_location_detail_v WHERE location_code = 'HQ01';

A second pattern joins the view to transaction tables to derive a display address for a shipping destination, for example joining LOCATION_ID to PO_LOCATION_ASSOCIATIONS or to HR_LOCATION_ASSOCIATIONS for a given inventory organization. A third pattern is a validation feed that exports all active locations with their formatted city/state/ZIP for downstream systems, ordered by country and location code, in which case an outer SELECT with ORDER BY COUNTRY, LOCATION_CODE is appropriate because the view itself provides no ordering. Because the view is unpaginated and unfiltered, report queries should always constrain by LOCATION_ID or LOCATION_CODE to avoid full scans of HR_LOCATIONS.