Search Results post_postcode_pk_key

Overview

POA.EDW_GEOGRAPHY_M is a summary-layer table in the Oracle EBS Enterprise Data Warehouse (EDW), owned by the POA schema and registered under FND Design Data as BIS.EDW_GEOGRAPHY_M. It stores the Geography dimension used by Oracle Business Intelligence and Daily Business Intelligence (DBI) reporting. Each row describes a fully denormalized geography hierarchy — from the "All" top level down through world areas, country, region, state, state-region, city, postcode-city, and postcode — anchored on a general location (GLOC). In Oracle EBS 12.1.1 and 12.2.2 the object resides in the APPS_TS_SUMMARY tablespace and is marked VALID, with the standard Oracle restriction that it is for internal use only and must be accessed from standard Oracle Applications programs rather than by direct custom SQL.

The documented metadata does not declare foreign keys, and the heuristic Data Vault classification is standalone. In modeling terms, this object behaves more like a flattened dimension/satellite than a hub or link; the suggested interpretation is a consolidated geography satellite, since it carries descriptive and user-attribute columns alongside its surrogate keys rather than relational join structure.

Key Information Stored

The table is documented with 109 columns spanning ten geography levels, each following a consistent pattern of a *_PK business identifier, a *_PK_KEY system-generated surrogate, a *_DP display name, an *_NAME, an *_INSTANCE, and five user-defined attributes. The most significant columns include:

• GLOC_LOCATION_PK_KEY — the primary key (EDW_GEOGRAPHY_M_PK) and system-generated unique identifier for the general location, also the sole column of unique index EDW_GEOGRAPHY_M_U2.
• GLOC_LOCATION_PK — the business-key candidate for the location; with GLOC_LOCATION_PK_KEY it forms unique index EDW_GEOGRAPHY_M_U1.
• GLOC_NAME, GLOC_LOCATION_DP, and address lines GLOC_ADDRESS_LINE_1 through GLOC_ADDRESS_LINE_4 — the location name, display value, and street address.
• CITY_CITY_PK_KEY — surrogate key for the city level (the column the user searched for), paired with CITY_CITY_PK (business key), CITY_CITY_DP, CITY_NAME, and CITY_INSTANCE.
• COUN_COUNTRY_PK_KEY / COUN_COUNTRY_PK / COUN_NAME — country-level surrogate, business key, and name.
• STAT_STATE_PK_KEY / STAT_STATE_PK / STAT_NAME and REGN_REGION_PK_KEY / REGN_NAME — state and region levels.
• POST_POSTCODE_PK_KEY / POST_POSTCODE_PK and PCCY_POSTCODE_CITY_PK_KEY — postcode and postcode-city levels.
• CREATION_DATE and LAST_UPDATE_DATE — standard audit columns.

Common Use Cases and Queries

Because this is a dimensional summary object, its dominant use is geography-level reporting and filtering — grouping transactions or balances by country, region, state, or city. A representative pattern resolves a city key to its hierarchy parents:

• SELECT CITY_NAME, STAT_NAME, COUN_NAME, REGN_NAME FROM POA.EDW_GEOGRAPHY_M WHERE CITY_CITY_PK_KEY = :p_city_key;
• Aggregating a fact by country: GROUP BY COUN_COUNTRY_PK_KEY after joining on the location surrogate.
• Postcode-level geography rollups for territory or sales analysis using POST_POSTCODE_PK_KEY.
• Validating that a location resolves to exactly one city/country pair via the unique index on GLOC_LOCATION_PK_KEY.

Reporting use cases include DBI geography dashboards, multi-level sales-by-region analysis, and reconciling EBS location master data against the warehouse dimension. Access should be routed through supported Oracle reporting programs.

Related Objects

The documented metadata records no foreign keys (standalone classification), so related objects are identified conceptually rather than by enforced constraints:

• EDW_GEOGRAPHY_M_PK and the unique indexes EDW_GEOGRAPHY_M_U1 and EDW_GEOGRAPHY_M_U2 — defined on this table itself, on GLOC_LOCATION_PK_KEY and GLOC_LOCATION_PK.
• Warehouse fact tables in the POA EDW that carry GLOC_LOCATION_PK_KEY as a geography dimension reference.
• The EBS HR_LOCATIONS_ALL / location master tables, the operational source that GLOC_* columns summarize.
• Geography hierarchy source objects supplying the ARE1, ARE2, COUN, REGN, STAT, SREG, CITY, PCCY, and POST levels.
• Oracle BI/DBI reporting views that join this dimension to fact summaries on GLOC_LOCATION_PK_KEY.

No authoritative FK relationships are documented for this object, so all joins should be confirmed against the deployment's actual data model before use.