Search Results hz_postcode_city_fk




Overview
Explore in AI Chat

Overview

EDW_GEOGRAPHY_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supplies geography-resolution utility functions for the Enterprise Data Warehouse (EDW) and ETRM reporting layers. Its central purpose is to translate a stored foreign-key identifier — a location identifier, a site-use identifier, a vendor site identifier, or a party site identifier — into a human-readable geography string. This pattern is common in EBS data-extraction and ETL contexts, where a fact table carries only a numeric key and the warehouse requires a denormalized descriptive value for reporting, dimensional modeling, or downstream publication.

The package is classified as OTHER in ETRM, meaning it is not a business-document validation API in the sense of an interface table processor. It is read-only in behavior, exposes no DML entry points, and each function returns a VARCHAR2 geography descriptor. The package was introduced under header version control in 2005 (poafkge.pkh 120.1), which places it in the 11.5.x lineage, and it remains present in 12.1.1 and 12.2.2. The comment entered for Party_Site_Location_fk notes explicitly that it supports the "new customer model, used by CRM," confirming a deliberate 11.5-era extension to the Trading Community Architecture (TCA) party model.

Key Procedures and Functions

  • HR_LOCATION_FK — Resolves an HR location identifier to a geography string. This is the function associated with the "hr_location_fk" search term and is the most frequently referenced entry point for Human Resources location lookups.
  • HZ_Postcode_City_fk — Returns a postcode-and-city geography string for a given location identifier, drawing on the TCA/HZ postal address model.
  • Customer_Site_Location_fk — Resolves a customer site-use identifier into a geography descriptor, serving the receivables and order-to-cash reporting paths.
  • Supplier_Site_Location_fk — Resolves a vendor site identifier, scoped by operating unit, into a geography descriptor for procurement and payables reporting.
  • Party_Site_Location_fk — Resolves a party site identifier to a geography string under the TCA "new customer model."

Every function carries a PRAGMA RESTRICT_REFERENCES declaration specifying WNDS, WNPS, and RNPS. These purity guarantees certify that the functions write no database state, write no package state, and read no package state, which permits their safe invocation from SQL statements — including within SELECT lists, views, and ETL queries. Optional instance_code parameters on several functions indicate multi-instance or multi-organization awareness.

Tables Accessed

  • HZ_LOCATIONS — The primary TCA location table, supplying address and geography components for all resolution functions.
  • HZ_CUST_ACCT_SITES_ALL — Customer account site assignments, used to link a customer site-use to its underlying location.
  • HZ_CUST_SITE_USES_ALL — Site-use records that connect customer site usage to account sites for Customer_Site_Location_fk.
  • EDW_LOCAL_INSTANCE — The warehouse instance registry, supporting the instance_code parameter used to disambiguate data across multiple EBS instances consolidated into a single warehouse.

All access is through APPS synonyms, and the package performs read-only lookups without modifying any documented table.

Usage Notes

EDW_GEOGRAPHY_PKG is referenced by seventeen other packages, indicating broad dependency across the reporting and warehouse extraction layer. It is typically invoked from custom PL/SQL ETL routines, warehouse materialized view definitions, and BI Publisher or Discoverer data models that require a readable geography label rather than a numeric foreign key. Because the functions satisfy WNDS/RNPS/WNPS purity rules, they may be called directly inside SQL. The HR_LOCATION_FK function in particular is the standard mechanism for converting HR location foreign keys into report-friendly geography text in Oracle EBS 12.1.1 and 12.2.2 environments. Implementers should note that HZ_LOCATIONS and the HZ customer site tables are date-effective and multi-org sensitive, so callers retrieving historical geography should validate the effective context of the underlying location record.