Results for “okx_locations_v”

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

Overview

OKX_LOCATIONS_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. It belongs to the OKX - Contracts Integration product family and serves as the canonical source for work location definitions used by Oracle Contracts integration flows. The view presents a denormalized, presentation-ready listing of human resources locations, exposing address components, organizational attributes, and lifecycle status indicators in a single queryable structure.

Its principal design objective is to reconcile two divergent naming conventions: the operational HR location tables, which use columns such as LOCATION_CODE and TOWN_OR_CITY, and integration consumers that expect a generic identifier/name/description pattern. The view therefore remaps LOCATION_ID to ID1, LOCATION_CODE to NAME, and constructs a formatted multi-line DESCRIPTION from the underlying address columns, making it directly consumable by Contracts integration interfaces without additional transformation.

Underlying Base Objects

The view is defined over three documented base objects:

• HR_LOCATIONS_ALL (synonym) — the primary source of address and organizational data, including ADDRESS_LINE_1 through ADDRESS_LINE_3, TOWN_OR_CITY, COUNTRY, POSTAL_CODE, BUSINESS_GROUP_ID, INVENTORY_ORGANIZATION_ID, INACTIVE_DATE, and IN_ORGANIZATION_FLAG.
• HR_LOCATIONS_ALL_TL (synonym) — the translation table supplying language-specific LOCATION_CODE and DESCRIPTION values. The join is constrained by LOT.LANGUAGE = USERENV('LANG'), ensuring each session returns location names in its own language context.
• ARP_ADDR_LABEL_PKG (package) — the Receivables address formatting package, invoked as ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS to compose a single, locale-aware address string truncated to 80 characters, which is published as the DESCRIPTION column.

The two HR tables are joined on LOCATION_ID, forming an equi-join that yields one row per location per active language.

Key Columns

• NAME — the location code from HR_LOCATIONS_ALL_TL, acting as the human-readable identifier for the work location.
• LOCATION_DESCRIPTION — the translated long description of the location.
• ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 — the street address components passed through unmodified from HR_LOCATIONS_ALL. These are the most frequently referenced columns by external consumers and are the fields most commonly searched by name.
• TOWN_OR_CITY, COUNTRY, POSTAL_CODE — geographic attributes retained verbatim for filtering and display.
• BUSINESS_GROUP_ID, ORGANIZATION_ID — the owning business group and, where applicable, the inventory organization associated with the location.
• IN_ORGANIZATION_FLAG — indicates whether the location is tied to an inventory organization.
• START_DATE_ACTIVE / END_DATE_ACTIVE — derived date range keyed to the location's effective period, with END_DATE_ACTIVE set to the day before INACTIVE_DATE.
• STATUS — a computed single-character flag ('A' active, 'I' inactive) based on whether the location's inactive date has passed.
• DESCRIPTION — the ARP-formatted address string limited to 80 characters.

Common Use Cases and Queries

Typical usages include populating location lists of values in Contracts integration, validating that a contract work location carries a complete address before interface submission, and auditing inactive locations still referenced by open agreements.

SELECT name, address_line_1, town_or_city, postal_code, status
FROM   apps.okx_locations_v
WHERE  status = 'A'
ORDER  BY name;

SELECT id1, name, address_line_1, address_line_2, description
FROM   apps.okx_locations_v
WHERE  business_group_id = :p_bg_id
AND    address_line_1 IS NOT NULL;

Because the view derives DESCRIPTION by calling an ARP formatting function on every row, queries benefit from applying selective predicates—particularly on STATUS or BUSINESS_GROUP_ID—before the function executes, and from avoiding SELECT * when only address columns are required.