Results for “geo_type_code”

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

Overview

AMS_ACT_GEO_AREAS_V is a marketing module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the geographic targeting information associated with Marketing activities, answering the question of which geographic areas a given campaign or activity is available in or used by. The view is read-only and is populated dynamically from the underlying geography assignment table joined to Oracle's location hierarchy and location type reference views.

From a reporting and integration standpoint, the view abstracts the recursive naming logic of the geographic hierarchy. Rather than requiring report authors or interface developers to resolve a GEO_HIERARCHY_ID against JTF_LOC_HIERARCHIES_VL and decode area types manually, the view presents a ready-to-use GEO_AREA_NAME and a fully qualified GEO_LOCATION string. This makes it a convenient source for marketing collateral, campaign coverage reports, territory analysis, and downstream data extracts. Because the complexity is encapsulated in the view definition, consumers benefit from consistent naming behavior across all integrations. Users searching for "geo_area_name" arrive at this view precisely because GEO_AREA_NAME is the human-readable label of the targeted geography.

Underlying Base Objects

The view is defined over three documented base objects:

• AMS_ACT_GEO_AREAS (synonym to the base activity geography table) — supplies the association between a marketing activity and a geographic hierarchy node, including ACT_GEO_AREA_ID, OBJECT_VERSION_NUMBER, usage codes, and GEO_AREA_TYPE_CODE.
• JTF_LOC_HIERARCHIES_VL (view) — the location hierarchy master, providing the descriptive names for each level (country, country region, state, state region, city, area, postal code).
• JTF_LOC_TYPES_VL (view) — the location type reference, providing LOCATION_TYPE_NAME to translate GEO_AREA_TYPE_CODE into a display label.

Joins are performed on GEO_AREA_TYPE_CODE = LOCATION_TYPE_CODE and GEO_HIERARCHY_ID = LOCATION_HIERARCHY_ID. Consequently, a record is only returned when a matching hierarchy node and location type exist, and the view inherits the translation (VL) semantics of the underlying JTF views.

Key Columns

• ACTIVITY_GEO_AREA_ID / ACT_GEO_AREA_ID — primary identifier linking the geography assignment to its activity.
• OBJECT_VERSION_NUMBER / OBJECT_VERSION_NO — optimistic locking column used by the forms and APIs that maintain the record.
• ARC_ACT_GEO_AREA_USED_BY / ACT_GEO_AREA_USER and the corresponding _ID — indicate which entity (activity, campaign, etc.) uses or is available in the geography.
• GEO_AREA_TYPE_CODE / GEO_TYPE_CODE — the coded level of the geography (for example AREA1, AREA2, COUNTRY, CREGION, STATE, SREGION, CITY, POSTAL_CODE).
• GEO_TYPE_NAME — the decoded location type name from JTF_LOC_TYPES_VL.
• GEO_HIERARCHY_ID — the surrogate key identifying the geography record in JTF_LOC_HIERARCHIES_VL.
• GEO_AREA_NAME — the decoded, level-appropriate name produced by the DECODE expression in the view text; this is the column most frequently queried by report authors.
• GEO_LOCATION — a hierarchical qualification of the area, concatenating the relevant parent names (city, state, country region, country) to provide geographic context.

Common Use Cases and Queries

Typical uses include campaign coverage reporting, verifying that a marketing activity is mapped to the intended territory, and feeding geography labels into downstream extracts or analytics. A basic query selecting the area name for a given activity:

• SELECT activity_geo_area_id, geo_area_type_code, geo_area_name, geo_location FROM apps.ams_act_geo_areas_v WHERE arc_act_geo_area_used_by = :activity_id;

To list all marketing coverage by location type:

• SELECT geo_type_name, geo_area_name, geo_location FROM apps.ams_act_geo_areas_v ORDER BY geo_type_name, geo_area_name;

To restrict to a specific level, filter on GEO_AREA_TYPE_CODE (for example, 'CITY' or 'COUNTRY'). Because the view performs the junction and DECODE internally, queries remain simple and portable across EBS 12.1.1 and 12.2.2 environments, making it a reliable integration point for marketing geography data.