Search Results hz_loc




Overview
Explore in AI Chat

Overview

APPS.IBY_XML_HZ_ADDR_1_0_V is a database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a conventional relational view; rather it is an XML-generating view purpose-built for the Oracle Payments (IBY) funds disbursement and payment instruction extraction framework. The view materialises HZ party site addresses as standards-conformant XML fragments, each element named to match the payment message schema (for example AddressLine1 through AddressLine4, City, PostalCode, and ISO3DigitCountry). Its central design feature is the use of XMLConcat and XMLElement to emit a self-describing address block for each location, which downstream XML extract generators consume when formatting payment files, remittance advices, and disbursement instructions.

Because the view returns XMLTYPE content, it is typically queried in the context of funds capture and disbursement extraction rather than for ad hoc tabular reporting. The trailing hz_loc.location_id in the SELECT list is retained as a scalar correlating key so that callers can join the XML payload back to the originating HZ_LOCATIONS record.

Underlying Base Objects

The ETRM metadata documents four referenced base objects:

  • HZ_LOCATIONS (referenced through a synonym) — the primary source of address attributes: LOCATION_ID, ADDRESS1 through ADDRESS4, CITY, COUNTY, STATE, PROVINCE, COUNTRY, and POSTAL_CODE.
  • FND_TERRITORIES_VL (view) — supplies the ISO territory code and territory short name via the join on HZ_LOCATIONS.COUNTRY = FND_TERRITORIES_VL.TERRITORY_CODE.
  • IBY_FD_EXTRACT_GEN_PVT (package) — the Funds Disbursement Extract Generation private package, whose FORMAT_HZ_ADDRESS function is invoked twice: once to produce a concatenated address and once with the 'POSTAL_ADDR' argument to produce a mailing-formatted address.
  • XMLTYPE (type) — the datatype constructor underpinning the returned XML document.

The join to FND_TERRITORIES_VL is effectively inner, meaning any location whose country code is not present in the territories lookup is excluded from the result set.

Key Columns

  • AddressInternalID — the HZ_LOCATIONS.LOCATION_ID, exposed both inside the XML and as the scalar trailing column.
  • AddressLine1 – AddressLine4 — the street address components carried verbatim from HZ_LOCATIONS.
  • City, County, State, Province, PostalCode — geographic attributes mapped directly to their HZ_LOCATIONS counterparts.
  • Country — the raw country code on the location record.
  • ISO3DigitCountry — the numeric/ISO territory code from FND_TERRITORIES_VL.
  • CountryName — the territory short name, suitable for printed output.
  • PreFormattedConcatenatedAddress — output of IBY_FD_EXTRACT_GEN_PVT.FORMAT_HZ_ADDRESS for the location, applying the current style/formatting rules.
  • PreFormattedMailingAddress — the same function invoked with 'POSTAL_ADDR', yielding a mailing-oriented rendering.

Common Use Cases and Queries

Typical consumers are the IBY payment extraction concurrent programs and any custom XML output that must mirror Oracle Payments address formatting. A representative query retrieves the XML address block for a set of locations:

  • SELECT location_id, x.* FROM apps.iby_xml_hz_addr_1_0_v x WHERE x.location_id = :p_location_id;
  • SELECT x.location_id, EXTRACTVALUE(x.column_value, '/AddressLine1') FROM TABLE(...) — used where the XML must be decomposed into flat columns for reporting.
  • Join patterns frequently correlate the view back to HZ_PARTY_SITES and HZ_CUST_ACCOUNT_SITES to obtain a party site's formatted address for a supplier or customer payment.

Note that the view's WHERE clause restricts output to locations having a matching territory, and the two FORMAT_HZ_ADDRESS calls are PL/SQL context invocations, so query performance depends heavily on the row filter applied to LOCATION_ID. The view is read-only; no DML is permitted against it.