Search Results edw_geography_pkg




Overview
Explore in AI Chat

Overview

EDW_GEOGRAPHY_PKG is an APPS-owned PL/SQL package body in Oracle E-Business Suite Release 12.1.1 and 12.2.2 that supports the Enterprise Data Warehouse (EDW) geography dimension. Its role is to resolve and standardize geographic reference keys — locations, postal codes, cities, and site identifiers — so that transactional records from Receivables, Order Management, and related modules can be conformed into a single geographic dimension for reporting and analytics. The package is classified as OTHER in the ETRM metadata, indicating that it is not a public, supported business API but an internal utility used primarily by ETL and data warehouse extraction routines. It is strongly coupled to the Trading Community Architecture (TCA) model, since customer, supplier, and party sites all resolve their addresses through TCA entities. Because the package body is VALID and referenced by 17 other packages, it functions as a shared foundation object rather than a standalone deliverable, exposing foreign-key resolution functions that other EDW packages consume during dimension load processing.

Key Procedures and Functions

The ETRM documentation lists five procedures and functions within the package body. Their naming convention follows an EDW surrogate-key resolution pattern, in which each routine returns or derives a warehouse key for a specific TCA address context.

  • HR_LOCATION_FK — Resolves the geography foreign key associated with an HR (Human Resources) location, mapping an internal HR location identifier to the EDW geography dimension key.
  • HZ_POSTCODE_CITY_FK — Derives a geography key from postal code and city attributes held in TCA location records, supporting geographic rollups where only postal-level detail is available.
  • CUSTOMER_SITE_LOCATION_FK — Resolves the location key for a customer site, drawing on customer account site and site use assignments to identify the correct address.
  • SUPPLIER_SITE_LOCATION_FK — Performs the equivalent resolution for supplier sites, which are modeled in TCA through party sites rather than customer account structures.
  • PARTY_SITE_LOCATION_FK — Resolves the location key for a party site, providing the common denominator used by both customer and supplier site lookups.

All five routines are read-only lookups; no documented procedure in the package performs inserts, updates, or deletes against transactional data.

Tables Accessed

The package body references four base tables through APPS synonyms, in addition to EDW_LOCAL_INSTANCE and the package itself. HZ_LOCATIONS supplies the raw address attributes — postal code, city, state, and country — from which geographic keys are constructed. HZ_CUST_ACCT_SITES_ALL provides the association between a customer account and its physical address locations, while HZ_CUST_SITE_USES_ALL identifies the business purpose (bill-to, ship-to) of each customer site, allowing the package to select the appropriate location when multiple uses exist. EDW_LOCAL_INSTANCE supplies warehouse-instance context, ensuring that key generation is scoped correctly for the target EDW environment. Reads are limited to these tables; the package does not write to any of them.

Usage Notes

EDW_GEOGRAPHY_PKG is invoked indirectly rather than from standard EBS forms or concurrent programs. Its callers are the 17 dependent packages documented in ETRM, which typically execute during nightly or periodic data warehouse refresh cycles to populate geography dimension tables. Custom ETL code that extracts Receivables, Purchasing, or HR data into an external warehouse can call these functions to obtain consistent geography keys, but doing so introduces a dependency on an unsupported internal package, so the interface should be treated as subject to change on patching or upgrade. Because the routines only read TCA location data, they are safe to call repeatedly within a load window; however, they do not cache results, so high-volume extracts should minimize per-row invocations. No reverse dependencies exist, meaning no other database object relies on this package beyond the 17 documented callers.