Search Results bim_geography_u1

Overview

BIM.BIM_GEOGRAPHY is a reference dimension table in the Oracle E-Business Suite Business Intelligence Management (BIM) module. It stores the dimension values that describe geographic locations used across the enterprise reporting model, with a level of granularity fixed at the postal code level. Each row represents a single geographic location within a location hierarchy, identified by a hierarchical code. The table resides in the APPS_TS_ARCHIVE tablespace and is populated by the BIM_GEO_PKG concurrent program, which derives and maintains the geographic dimension values consumed by downstream analytics.

In Oracle EBS 12.1.1 and 12.2.2, the object is owned by the BIM schema and is flagged as FND design data (BIM.BIM_GEOGRAPHY), confirming its role as a seeded, upgrade-managed object rather than a customer extension. The relationship metadata classifies the table heuristically as standalone, with no outgoing foreign keys to other BIM tables; as a modeling suggestion, this positions it as a candidate hub surrounded by descriptive location attributes, layered with standard EBS auditing and concurrency columns. It is not registered as a dependent child of any other documented object.

Key Information Stored

The most significant columns are those that define the location identity and its business context:

• LOCATION_HIERARCHY_CODE (VARCHAR2, 240, mandatory) — the primary key and the sole surrogate-level identifier for a geographic location. It is also the leading column of unique index BIM_GEOGRAPHY_U1.
• COUNTRY (VARCHAR2, 60) — country name for the location.
• STATE (VARCHAR2, 60) — state or province within the country.
• CITY (VARCHAR2, 60) — city or locality.
• POSTAL_CODE (VARCHAR2, 60) — postal code, the finest granularity supported by the table.
• PERIOD_START_DATE and PERIOD_END_DATE — effective date boundaries that establish the period validity of each geographic record.
• SECURITY_GROUP_ID — used in hosted environments to partition data by security group.

The four columns COUNTRY, STATE, CITY, and POSTAL_CODE together form the composite business-key candidate enforced by unique index BIM_GEOGRAPHY_U2. Together with LOCATION_HIERARCHY_CODE, these constitute the documented unique constraints on the table. The remaining columns are standard EBS WHO and concurrent-program bookkeeping fields: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE. These track who created and last modified each row and which concurrent program touched it.

Common Use Cases and Queries

Typical uses include populating geographic hierarchies in reporting, validating postal-code-level location assignments, and joining location codes to fact or transaction tables. A common validation query confirms uniqueness of the business key:

SELECT COUNTRY, STATE, CITY, POSTAL_CODE, COUNT(*) FROM BIM.BIM_GEOGRAPHY GROUP BY COUNTRY, STATE, CITY, POSTAL_CODE HAVING COUNT(*) > 1;

Retrieving a location by its hierarchy code:

SELECT LOCATION_HIERARCHY_CODE, COUNTRY, STATE, CITY, POSTAL_CODE FROM BIM.BIM_GEOGRAPHY WHERE LOCATION_HIERARCHY_CODE = :p_code;

Period-filtered reporting uses PERIOD_START_DATE and PERIOD_END_DATE to select records valid for a given date range, while hosted multi-tenant environments filter on SECURITY_GROUP_ID. Reporting queries frequently pivot on COUNTRY and STATE to aggregate at higher geographic levels, then drill to CITY and POSTAL_CODE.

Related Objects

• FND_SECURITY_GROUPS — referenced by BIM_GEOGRAPHY.SECURITY_GROUP_ID; the FK relationship enforces that security groups used here exist in the foundation security group table, supporting hosted deployments.
• BIM_GEO_PKG — the concurrent program package that populates and refreshes this table.
• BIM_GEOGRAPHY_U1 and BIM_GEOGRAPHY_U2 — unique indexes enforcing the surrogate and business keys respectively.
• BIM.BIM_GEOGRAPHY_PK — the primary key constraint on LOCATION_HIERARCHY_CODE.

The documented metadata records no additional dependent BIM tables referencing this object, consistent with its standalone classification. Downstream fact and dimension consumers join on LOCATION_HIERARCHY_CODE to resolve geographic attributes.