Results for “location_hierarchy_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIM_DIMV_GEOGRAPHY is a dimensional view owned by the APPS schema in Oracle E-Business Suite, defined within the BIM (Marketing Intelligence) product family. As reflected in the ETRM metadata, the product is marked obsolete, meaning the view is retained for backward compatibility and reference but is not part of current development or support commitments in either release 12.1.1 or 12.2.2. The view exposes location data at the postal code level, presenting a denormalized geography hierarchy that spans country, state, city, and postal code. Its role is that of a reporting and integration surface: rather than querying the underlying BIM_GEOGRAPHY synonym directly, downstream reports, extract programs, and marketing analytics components reference this view to obtain a consistent, hierarchy-aware set of geographic dimension attributes. An important structural characteristic is the UNION ALL with FND_LOOKUPS, which injects a sentinel row carrying the value '-999' across every descriptive column. This row represents an unknown or unclassified member, a standard dimensional modeling convention that allows fact records with missing geography to resolve against a placeholder rather than producing null joins. Because the object is a view rather than a table, it stores no data itself; all content is derived at runtime from its base objects, and its status is VALID in the documented environment.

Underlying Base Objects

The view is defined over three documented referenced objects. The primary source is BIM_GEOGRAPHY, exposed as a SYNONYM, which supplies all substantive geographic rows and contributes the columns LOCATION_HIERARCHY_CODE, LAST_UPDATE_DATE, COUNTRY, STATE, CITY, and POSTAL_CODE. The second source is FND_LOOKUPS, an Oracle EBS view over the application lookup tables, filtered to LOOKUP_TYPE = 'BIM_VALUE_TYPE' and LOOKUP_CODE = '-999'. This filter isolates the single lookup row that provides the MEANING values used to populate the unknown-member record in the UNION ALL branch. The third referenced object is FND_GLOBAL, documented as a PACKAGE. FND_GLOBAL is the standard EBS runtime context package; although the stored view text supplied in the metadata does not visibly invoke it, the dependency is recorded, which typically indicates reliance on session context such as ORG_ID, USER_ID, or RESP_ID resolved through the package during execution or through the security predicates applied to the underlying synonym. The UNION ALL construct combines the geographic rows and the sentinel row into a single result set with identical column shapes, ensuring column count and datatype alignment across both branches.

Key Columns

• LOCATION_HIERARCHY_CODE — The user's searched term; the primary hierarchy identifier sourced from BIM_GEOGRAPHY.LOCATION_HIERARCHY_CODE, representing the top-level location hierarchy to which the geography record belongs.
• LAST_UPDATE_DATE — The last modification timestamp of the source geography record; populated with TO_DATE(NULL) on the sentinel row.
• COUNTRY — Descriptive country name; the sentinel row substitutes the FND_LOOKUPS MEANING value.
• COUNTRY_CODE — NVL-wrapped country value defaulting to '-999' when the country is null, providing a non-null join key for reporting.
• STATE — Descriptive state or province name.
• STATE_CODE — NVL-wrapped state value defaulting to '-999'.
• CITY — Descriptive city name.
• CITY_CODE — NVL-wrapped city value defaulting to '-999'.
• POSTAL_CODE — Descriptive postal code; the finest granularity presented by the view.
• POSTAL_CODE_VALUE — NVL-wrapped postal code defaulting to '-999', used as the surrogate value in joins to fact data.

The parallel descriptive and *_CODE columns follow a common dimensional pattern: the raw attribute is preserved for display, while the NVL-protected code column guarantees a non-null key for star-schema joins.

Common Use Cases and Queries

Typical usage involves resolving geography attributes for marketing analytics, segmenting customers or transactions by postal code, and producing geography dimension extracts. The sentinel row supports outer-style reconciliation of facts lacking location data. A representative query follows:

SELECT LOCATION_HIERARCHY_CODE,
       COUNTRY, COUNTRY_CODE,
       STATE, STATE_CODE,
       CITY, CITY_CODE,
       POSTAL_CODE, POSTAL_CODE_VALUE
FROM   APPS.BIM_DIMV_GEOGRAPHY
WHERE  LOCATION_HIERARCHY_CODE = :p_hierarchy
ORDER  BY COUNTRY_CODE, STATE_CODE, CITY_CODE, POSTAL_CODE_VALUE;

To isolate the unknown-member row for reconciliation, filter on the sentinel value: WHERE POSTAL_CODE_VALUE = '-999'. A common aggregation pattern joins the view to fact tables on the *_CODE columns to count records by geography level. Because the view is obsolete, it should be treated as read-only and not extended; new development should target supported geography sources, while existing reports depending on BIM_DIMV_GEOGRAPHY continue to execute unchanged across 12.1.1 and 12.2.2.