Search Results iby_xml_hr_addr_1_0_v




Overview
Explore in AI Chat

Overview

The IBY_XML_HR_ADDR_1_0_V view is a Payments (IBY) module database object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to generate a structured XML representation of a Human Resources (HR) address record maintained in the HR_LOCATIONS_ALL table. Rather than exposing rows of discrete address columns in the traditional relational manner, the view returns an XMLTYPE value constructed through SQL/XML functions.

The view is part of Oracle Payments' address formatting infrastructure used during funds capture and disbursement processing. It is referenced by the extract generation logic that produces payment files, remittance advice, and other outbound XML payloads that must carry a payee, payor, or party address. By encapsulating address assembly into a re-usable view, Oracle Payments ensures consistent element naming and formatting across the various XML message versions (note the "1_0" suffix indicating the version of the XML schema the view targets).

Underlying Base Objects

The view is defined over four documented base objects:

  • HR_LOCATIONS_ALL (SYNONYM) — the primary source of address data. Each row in this table represents an HR location, identified by LOCATION_ID, holding street lines, city, region, postal code, and country.
  • FND_TERRITORIES_VL (VIEW) — an outer-joined reference to obtain the territory short name (TERRITORY_SHORT_NAME) corresponding to the location's COUNTRY code. The outer join ((+)) preserves locations whose country code has no matching territory.
  • IBY_FD_EXTRACT_GEN_PVT (PACKAGE) — a Payments PL/SQL package invoked as part of the view's SELECT list. The function FORMAT_HR_ADDRESS returns pre-formatted, concatenated address strings.
  • XMLTYPE (TYPE) — the datatype of the composed XML document returned by XMLCONCAT.

The view consequently depends on both HR reference data and Payments formatting logic, reflecting the cross-module nature of its role.

Key Columns

The view exposes two columns:

  • LOCATION_ID — the numeric key inherited from HR_LOCATIONS_ALL.LOCATION_ID, allowing the XML payload to be correlated back to the source location record.
  • ADDRESS — an XMLTYPE document produced by XMLCONCAT. Its child elements include ADDRESSINTERNALID, ADDRESSLINE1, ADDRESSLINE2, ADDRESSLINE3, CITY, COUNTY, STATE, COUNTRY, COUNTRYNAME, POSTALCODE, PREFORMATTEDCONCATENATEDADDRESS, and PREFORMATTEDMAILINGADDRESS. The two preformatted elements are populated by calls to IBY_FD_EXTRACT_GEN_PVT.FORMAT_HR_ADDRESS, the second passing the 'POSTAL_ADDR' style hint to yield a mailing-oriented format.

Common Use Cases and Queries

This view is typically consumed by the Payments extract generation process rather than by end users directly. Developers and support analysts query it to inspect, debug, or validate the XML that will appear in payment messages, and to dry-run address formatting for a given location.

  • Validating XML output for a specific location: SELECT LOCATION_ID, XMLSERIALIZE(CONTENT ADDRESS AS CLOB) AS ADDR_XML FROM IBY_XML_HR_ADDR_1_0_V WHERE LOCATION_ID = :loc_id;
  • Extracting a single formatted element: SELECT LOCATION_ID, EXTRACTVALUE(ADDRESS,'/ADDRESSLINE1') FROM IBY_XML_HR_ADDR_1_0_V;
  • Confirming the preformatted mailing string: SELECT LOCATION_ID, EXTRACTVALUE(ADDRESS,'/PREFORMATTEDMAILINGADDRESS') FROM IBY_XML_HR_ADDR_1_0_V WHERE LOCATION_ID = :loc_id;

Because formatting is delegated to IBY_FD_EXTRACT_GEN_PVT, querying the view also exercises the same logic Oracle Payments uses in production, making it valuable for confirming that country-specific address styles resolve as expected.