Search Results hr_loc




Overview
Explore in AI Chat

Overview

The view APPS.IBY_XML_HR_ADDR_1_0_V belongs to the Oracle Payments (IBY) schema and serves as an XML-generation layer that projects Human Resources location records into a structured XML document conforming to an address format version "1_0". It is one of the address-extraction views used by the Payments funds disbursement and XML publishing infrastructure to produce standardized address payloads for payees, suppliers, and other parties whose addresses originate from the HR locations repository (HR_LOCATIONS_ALL). The view's purpose is to transform relational address columns — address lines, city, county, state, country, postal code — into named XML elements via XMLElement and XMLConcat.

In the context of Oracle EBS 12.1.1 and 12.2.2, this view is part of the IBY XML address view family (variants include HR, HZ, and POS address sources). It is typically referenced by the Payments XML publishing framework rather than being queried directly by end users, although developers and report writers frequently query it when diagnosing address formatting issues.

Underlying Base Objects

According to the documented ETRM metadata, the view is defined over the following referenced base objects:

  • HR_LOCATIONS_ALL (referenced through a synonym, hr_loc) — the primary source of address data. All textual address fields, the location_id, location_code, and country code derive from this table.
  • FND_TERRITORIES_VL (view, aliased te) — supplies territory-level descriptive attributes such as ISO_TERRITORY_CODE and TERRITORY_SHORT_NAME. It is joined with an outer join (country = territory_code(+)) so that locations without a matching territory still return rows.
  • IBY_FD_EXTRACT_GEN_PVT (package) — provides the PL/SQL function format_hr_address, invoked to build the pre-formatted concatenated and mailing addresses.
  • XMLTYPE (type) — the Oracle XML DB datatype underlying the generated XML output.

Key Columns

The view emits a single XML document column plus the raw Location_ID. The XML document contains elements such as:

  • AddressInternalID — maps to HR_LOC.LOCATION_ID, the unique location identifier.
  • AddressLine1 / AddressLine2 / AddressLine3 — the hierarchical street address lines.
  • City — from TOWN_OR_CITY.
  • County / State — derived via DECODE on country; both are suppressed for Canada ('CA'), reflecting Canadian address conventions where region_1/region_2 are not used as county/state.
  • Country, ISO3DigitCountry, CountryName — the country code and its territory descriptions joined from FND_TERRITORIES_VL.
  • PostalCode — the postal or ZIP code.
  • PreFormattedConcatenatedAddress — full address rendered by IBY_FD_EXTRACT_GEN_PVT.format_hr_address(location_id).
  • PreFormattedMailingAddress — mailing-oriented rendering using the same function with the 'POSTAL_ADDR' style argument.
  • AddressName — the HR location code.

Common Use Cases and Queries

Typical scenarios include verifying how a supplier or payee address will render in a payment XML file, and troubleshooting mismatched or missing country descriptions. A representative query joining the view to its HR location source follows:

SELECT v.location_id,
       EXTRACTVALUE(v.column_value, '/AddressLine1') addr1,
       EXTRACTVALUE(v.column_value, '/City') city,
       EXTRACTVALUE(v.column_value, '/CountryName') country
 FROM (SELECT IBY_XML_HR_ADDR_1_0_V.* FROM IBY_XML_HR_ADDR_1_0_V) v
 WHERE v.location_id = :p_location_id;

Analysts also use the view to compare the pre-formatted mailing address against the concatenated address, confirming that country-specific formatting rules applied by IBY_FD_EXTRACT_GEN_PVT behave correctly. Because the outer join to FND_TERRITORIES_VL is preserved, records with unrecognized country codes return XML with null country-name elements, which is a common diagnostic finding.