Search Results get_region_id

Overview

FTE_REGION_ZONE_LOADER is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema and classified as an "OTHER" API in the E-Business Suite Technical Reference Manual (ETRM). It belongs to the Oracle Transportation Management / Freight and Transportation Execution (FTE) product family and, more specifically, supports the R12 Bulk Loader infrastructure. The header comment embedded in the source file ("Contains Zone and Region functions for R12 Bulk Loader") confirms that its purpose is to load, derive, and persist zone and region identifier data used by transportation planning and scheduling logic.

Zones and regions form the geographic foundation for freight rating, routing, transit time calculation, and carrier service selection. This package provides the programmatic plumbing that bulk-loading concurrent programs require in order to translate external source data (country, state, city, postal code ranges) into the internal zone and region identifiers stored in the WSH schema. It also generates new surrogate keys when source data does not already map to an existing region or zone, and it facilitates the association of trading partners to regions.

The excerpt also shows strong internal-use patterns: package-level constants such as G_PKG_NAME and G_USER_ID (assigned from FND_GLOBAL.USER_ID), user-defined PL/SQL collection types for lane IDs, region IDs, country/state/city codes, languages, and postal code ranges, all INDEX BY BINARY_INTEGER. These collections are used to bulk-process the interface rows instead of performing single-row lookups.

Key Procedures and Functions

• GET_NEXT_REGION_ID — a function that returns the next available region identifier, used when source data must be inserted as a new WSH_REGIONS record.
• GET_ZONE_ID — a function that resolves or derives the zone identifier for a given region/geographic combination. This is the object returned when end users search for "get_zone_id", and it is the primary lookup entry point for zone resolution in the loader.
• GET_REGION_ID — a function that retrieves or resolves the region identifier corresponding to a country, state, city, or postal code range combination.
• ADD_ZONE — a procedure that inserts a new zone record into the zone tables, presumably after GET_ZONE_ID or GET_NEXT_REGION_ID has determined that one is required.
• INSERT_PARTY_REGION — a procedure that creates the association between a trading party and a region, writing to the party-region mapping used by transportation planning.
• PROCESS_ZONE — a procedure that orchestrates zone-level processing for the bulk loader, invoking the lookup and insert logic per group of interface rows.
• PROCESS_REGION — a procedure that orchestrates region-level processing for the bulk loader, including language-specific region descriptions.
• PROCESS_DATA — the top-level driver procedure of the package, invoked by the bulk loader concurrent program to iterate over the staging data and dispatch work to PROCESS_ZONE and PROCESS_REGION.

Tables Accessed

The package reads and writes through APPS synonyms of the following base tables:

• WSH_REGIONS_INTERFACE, WSH_REGIONS_INTERFACE_S, WSH_REGIONS_TL_INTERFACE — staging tables that hold incoming region definitions, their descriptive/summary attributes, and translated region text (country, state, city, postal code from/to, language).
• WSH_REGIONS, WSH_REGIONS_S, WSH_REGIONS_TL — the destination region tables where validated and translated region records are finally persisted.
• WSH_ZONE_REGIONS, WSH_ZONE_REGIONS_S — mapping tables that link zones to regions, which GET_ZONE_ID and ADD_ZONE resolve or populate.
• FTE_LANES — the transportation lane definitions whose lane identifiers participate in zone/region grouping; it is used to build the LANE_ID_TBL collection type declared in the package.
• DUAL — used for scalar expressions and single-row lookups.
• PLITBLM — the standard FND PL/SQL index-by table logging/bulk utility referenced by many EBS loader packages.

Usage Notes

FTE_REGION_ZONE_LOADER is internal loader code, not an end-user API. It is invoked by the R12 Bulk Loader concurrent programs that import zone and region data into the WSH geographical model. Because it is referenced by six other packages, it functions as a shared utility within the FTE/WSH bulk loading chain, and its procedures are typically called in the order PROCESS_DATA, then PROCESS_ZONE and PROCESS_REGION, with GET_ZONE_ID, GET_REGION_ID, GET_NEXT_REGION_ID, ADD_ZONE, and INSERT_PARTY_REGION providing the lookup and write primitives along the way.

Customizations should avoid direct modification of this package body. Extensions should instead call the documented procedures from a wrapper concurrent program or custom PL/SQL, taking care to honor the package-level state (G_USER_ID, G_PKG_NAME) and the interface-table staging contract. On EBS 12.1.1 and 12.2.2, the underlying WSH_REGIONS and WSH_ZONE_REGIONS tables are identical in structure, so the same loader usage applies across both releases; the only differences are the standard Online Patching (adop) considerations in 12.2.2.