Search Results get_region_code

Overview

HRI_OLTP_VIEW_GEOGRAPHY is a public PL/SQL package body owned by the APPS schema within Oracle E-Business Suite. It forms part of the Human Resources Intelligence (HRI) OLTP view layer, a set of database objects originally designed to expose denormalized, warehouse-friendly representations of operational HR and geography data. The package functions as a thin translation and lookup façade: rather than performing complex set-based logic of its own, it wraps lower-level geography business logic exposed by the HRI_BPL_GEOGRAPHY package and provides direct, scalar access to descriptive territory information stored in the FND_TERRITORIES_TL table. Its primary business value is presenting human-readable geographic descriptors — region codes and country names — to reporting, analytics, and integration consumers without forcing those consumers to understand the underlying geographies model or the multi-language territory structures. The header comment in the source identifies the package's intent as returning specific geographic attributes for a given location or territory. The script carries an internal 2006 revision marker, confirming it as a long-standing component of the EBS data model rather than a recent addition. Under the documented API classification of OTHER, it is not treated as a full public business API requiring the same governance as interfaces such as the HR or Financials open interfaces, but it remains a stable, referenced object within the shipped product. The package is referenced by two other packages, indicating that it serves as a dependency within an internal call hierarchy rather than being a terminal, standalone utility.

Key Procedures and Functions

• GET_REGION_CODE — Returns the specific region code associated with a location. It accepts a location identifier as its input and returns a VARCHAR2 region code. The implementation delegates directly to hri_bpl_geography.get_region_code, so the actual resolution logic resides in the underlying business logic package. An exception handler traps all errors and returns a NULL character value, ensuring callers receive a null rather than a propagated exception.
• GET_COUNTRY_NAME — Returns the display name of a country for a supplied country code. This is the function associated with the user search term get_country_name. It accepts a country (territory) code as input and returns a VARCHAR2 country name. Internally it opens an explicit cursor over fnd_territories_tl that selects the territory_short_name where the territory code matches the input and the row's LANGUAGE equals userenv('LANG'). This language filter is significant: it returns the country name in the language of the current session, thereby localizing the output automatically. The fetch result is assigned to a local VARCHAR2(90) variable and returned. As with GET_REGION_CODE, a WHEN OTHERS handler converts any error into a null return.

Both functions are documented as returning VARCHAR2 and both suppress exceptions, meaning callers should handle NULL returns defensively rather than relying on raised errors to detect missing or invalid geography data.

Tables Accessed

The documented table reference for this package is FND_TERRITORIES_TL, accessed through an APPS synonym. This is the translated (TL) territories table in the Application Object Library, which stores territory or country codes alongside language-specific short and long names. GET_COUNTRY_NAME reads from it to retrieve the territory_short_name in the session language. No INSERT, UPDATE, or DELETE operations are documented, confirming the package is strictly read-only. GET_REGION_CODE does not query a table directly; its data access occurs inside HRI_BPL_GEOGRAPHY, consistent with the delegation pattern described above.

Usage Notes

HRI_OLTP_VIEW_GEOGRAPHY is typically invoked from the HRI OLTP view and reporting layer, from other PL/SQL packages (the metadata records two referencing packages), and from custom code requiring a convenient geography name lookup. It is well suited to report queries that must display a localized country name for a stored territory code, and to forms or concurrent programs that need a region code for a location identifier. Because GET_COUNTRY_NAME filters on userenv('LANG'), results differ by session language; deployments and test cases must account for NLS settings. Given the silent exception handling, callers should expect NULL for invalid codes or missing translations rather than an error, and should validate inputs where data integrity matters. The package is identical in behavior across EBS 12.1.1 and 12.2.2, as it is a shared HRI component with no compatibility-specific variants documented.