Search Results hr_locations_no_join




Overview
Explore in AI Chat

Overview

HR_LOCATIONS_NO_JOIN is an APPS-owned database view in the PER (Human Resources) product family, delivered as a VALID object in Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to support the Oracle Forms user interface, most notably the location-based lov (list of values) and inquiry screens within Oracle HRMS that allow users to select and review a business group's locations and their site attributes. Unlike its better-known sibling HR_LOCATIONS_ALL (a synonym/join over the HR_ALL_LOCATIONS and HR_LOCATIONS tables), HR_LOCATIONS_NO_JOIN deliberately avoids a multi-table join, projecting the combined column set of the location entity from a single row source while restricting output to the caller's current business group. This design choice yields a simpler, faster query plan for UI population and makes the view a convenient, low-overhead read surface for reports and integrations that only need location master data and its associated site flags.

Underlying Base Objects

The documented view text shows it is defined over the synonym HR_LOCATIONS_ALL, returning the standard column set of the location entity (attributed DFF columns, global DFF columns, address lines, telephony, territory data, and audit/WHO columns). The only filter is the predicate:

This predicate invokes the documented function HR_GENERAL.GET_BUSINESS_GROUP_ID (the HR_GENERAL package) to resolve the current session's business group, and the -99 sentinel prevents the query from returning rows when no business group is resolvable in context. The projection also contains a notable expression, BUSINESS_GROUP_ID+0, which emits the business group column as a numeric literal expression, disabling query rewrite to the base synonym and ensuring the view materializes in the specified execution context. The documented referenced objects are therefore HR_GENERAL (PACKAGE) and HR_LOCATIONS_ALL (SYNONYM).

Key Columns

  • LOCATION_ID — primary key of the location; foreign key target for HR and distribution entities.
  • LOCATION_CODE — the user-visuble short code for a location, used within a business group.
  • BUSINESS_GROUP_ID — owning business group; the view's NVL predicate constrains it to the session business group.
  • DESCRIPTION — free-text location description.
  • SHIP_TO_LOCATION_ID / SHIP_TO_SITE_FLAG — related ship-to address and indicator that the location is usable as a ship-to site.
  • RECEIVING_SITE_FLAG — flag indicating whether goods can be received at the location; the column most relevant to the search term.
  • BILL_TO_SITE_FLAG / OFFICE_SITE_FLAG / IN_ORGANIZATION_FLAG — site-purpose indicators used across procurement, HR, and inventory.
  • DESIGNATED_RECEIVER_ID / INVENTORY_ORGANIZATION_ID — default receiver and owning inventory organization.
  • TAX_NAME, INACTIVE_DATE, STYLE — tax registration, effective end date, and address style.
  • Address, telephony, DFF/global DFF, and WHO audit columns complete the projection.

Common Use Cases and Queries

Typical uses include reporting on receiving or shipping sites, validating site flags before receiving transactions, and populating LOVs in custom forms or concurrent programs. Because RECEIVING_SITE_FLAG is exposed directly, it is frequently queried as follows:

  • SELECT location_id, location_code, receiving_site_flag FROM hr_locations_no_join WHERE receiving_site_flag = 'Y'
  • SELECT location_id, location_code, ship_to_site_flag FROM hr_locations_no_join WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
  • SELECT location_id, location_code, address_line_1, town_or_city FROM hr_locations_no_join WHERE inventory_organization_id = :inv_org_id

Because the view filters on the caller's business group, queries executed outside the HRMS context (or for a different business group) may legitimately return no rows; applications should set the business group context before relying on the view's output.