Search Results jtf_loc_hierarchies_b




Overview
Explore in AI Chat

Overview

The JTF_LOC_HIERARCHIES_B table is a core geographical data structure within the Oracle E-Business Suite CRM Foundation (JTF) module. It serves as a master repository for storing a flattened, denormalized representation of all geographical hierarchies. Its primary role is to provide a comprehensive and efficient lookup mechanism for geographical relationships, enabling applications across EBS, particularly in CRM and Marketing (AMS), to associate business data with precise locations. The table is fundamental for territory management, address validation, and geographic reporting, acting as the central hub that links various geographical area identifiers into a single, queryable record.

Key Information Stored

The table's structure is defined by its primary key, LOCATION_HIERARCHY_ID, which uniquely identifies each geographical hierarchy record. The table's significance lies in its numerous foreign key columns that link to the JTF_LOC_AREAS_B table, each representing a different level in a geographical hierarchy. Key columns include COUNTRY_ID, STATE_ID, CITY_ID, and POSTAL_CODE_ID, which define the core address components. Additionally, it stores AREA1_ID and AREA2_ID for custom geographical groupings, and COUNTRY_REGION_ID and STATE_REGION_ID for regional classifications. The LOCATION_TYPE_CODE, referencing JTF_LOC_TYPES_B, categorizes the hierarchy type, and CREATED_BY_APPLICATION_ID tracks the originating EBS application.

Common Use Cases and Queries

A primary use case is resolving a complete geographical context from a single identifier, such as finding the country and state for a given city. This is essential for data validation in forms and for generating location-based reports. Another critical use is supporting territory management engines where sales territories are defined based on geographic hierarchies. A common query pattern involves joining this table to customer or site addresses to enrich data with higher-level geographic names for reporting.

  • Resolving Hierarchy from a City: SELECT country_id, state_id, city_id FROM jtf_loc_hierarchies_b WHERE city_id = :p_city_id AND location_type_code = 'ADDRESS';
  • Finding All Postal Codes in a State: SELECT postal_code_id FROM jtf_loc_hierarchies_b WHERE state_id = :p_state_id;
  • Reporting Joined with Area Names: Queries typically join JTF_LOC_HIERARCHIES_B to JTF_LOC_AREAS_B multiple times (via aliases) to retrieve the descriptive names for each ID column (e.g., country_name, state_name).

Related Objects

The JTF_LOC_HIERARCHIES_B table maintains extensive relationships with other EBS objects, primarily within the JTF and AMS schemas. As documented, it references the following tables via foreign keys:

  • JTF_LOC_TYPES_B: Via LOCATION_TYPE_CODE.
  • JTF_LOC_AREAS_B: Via POSTAL_CODE_ID, AREA1_ID, AREA2_ID, COUNTRY_ID, STATE_ID, COUNTRY_REGION_ID, STATE_REGION_ID, and CITY_ID.
  • FND_APPLICATION: Via CREATED_BY_APPLICATION_ID.

It is referenced by the following tables, indicating its data is consumed by higher-level application functions:

  • AMS_ACT_GEO_AREAS: The GEO_HIERARCHY_ID column references LOCATION_HIERARCHY_ID, linking geographic hierarchies to marketing activity areas.
  • AMS_CAMPAIGNS_ALL_B: The CITY_ID column references LOCATION_HIERARCHY_ID, associating campaigns with specific geographic locations.