Results for “ams_act_geo_areas”

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

Overview

AMS_ACT_GEO_AREAS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the geographical regions in which a campaign or an event is active. Each row associates an activity — a campaign, event, or similar marketing construct — with a specific geographic area, defined through a location type and an optional location hierarchy. This enables marketers to target and report on activities by territory, region, country, or other geographic segmentation.

The table resides in the AMS schema and is owned by the Marketing product family. It operates as a child of campaign definition data (AMS_CAMPAIGNS_ALL_B) and as a parent to activity metric fact tables, forming the bridge between campaign scope and geographic performance measurement. From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification, with the primary key ACTIVITY_GEO_AREA_ID acting as a hub-style surrogate identifier and the geographic attributes forming descriptive satellite content keyed to the parent activity.

Key Information Stored

The table contains 28 documented columns. The most significant are:

• ACTIVITY_GEO_AREA_ID — Surrogate primary key (AMS_ACT_GEO_AREAS_PK) and also documented as a unique business-key candidate via AMS_ACT_GEO_AREAS_U1.
• ACT_GEO_AREA_USED_BY_ID — Foreign key to AMS_CAMPAIGNS_ALL_B, identifying the campaign or activity that owns this geographic area.
• GEO_AREA_TYPE_CODE — Foreign key to JTF_LOC_TYPES_B, defining the type of geographic area (e.g., country, state, city).
• GEO_HIERARCHY_ID — Foreign key to JTF_LOC_HIERARCHIES_B, identifying the location hierarchy used to resolve the geographic region.
• ARC_ACT_GEO_AREA_USED_BY — Archive-related reference column supporting the used-by relationship.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security group access control.
• OBJECT_VERSION_NUMBER — Optimistic locking column for concurrent update control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and modified the row and when.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The standard EBS DFF (descriptive flexfield) column set for extensible, customer-defined attributes.

Common Use Cases and Queries

Typical reporting scenarios include listing the geographic areas per campaign, mapping campaigns to location hierarchies, and joining geographic scope to activity metrics.

• Retrieve all geographic areas for a given campaign:

  SELECT g.ACTIVITY_GEO_AREA_ID, g.GEO_AREA_TYPE_CODE, g.GEO_HIERARCHY_ID
  FROM   AMS.AMS_ACT_GEO_AREAS g
  WHERE  g.ACT_GEO_AREA_USED_BY_ID = :campaign_id;
  

• Resolve the location type description via JTF_LOC_TYPES_B:

  SELECT g.ACTIVITY_GEO_AREA_ID, t.LOCATION_TYPE
  FROM   AMS.AMS_ACT_GEO_AREAS g, JTF.JTF_LOC_TYPES_B t
  WHERE  g.GEO_AREA_TYPE_CODE = t.LOCATION_TYPE;
  

• Aggregate activity metrics by geographic area using AMS_ACT_METRIC_FACTS_ALL:

  SELECT g.ACTIVITY_GEO_AREA_ID, SUM(m.METRIC_VALUE)
  FROM   AMS.AMS_ACT_GEO_AREAS g, AMS.AMS_ACT_METRIC_FACTS_ALL m
  WHERE  g.ACTIVITY_GEO_AREA_ID = m.ACTIVITY_GEO_AREA_ID
  GROUP BY g.ACTIVITY_GEO_AREA_ID;
  

Because the primary key is also a unique index, joins to metric fact tables are reliably one-to-many from this table to AMS_ACT_METRIC_FACTS_ALL.

Related Objects

• AMS_CAMPAIGNS_ALL_B — Parent campaign/activity table; joined via ACT_GEO_AREA_USED_BY_ID.
• JTF_LOC_TYPES_B — Location type reference; joined via GEO_AREA_TYPE_CODE.
• JTF_LOC_HIERARCHIES_B — Location hierarchy reference; joined via GEO_HIERARCHY_ID.
• AMS_ACT_METRIC_FACTS_ALL — Activity metric facts referencing this table via ACTIVITY_GEO_AREA_ID.
• OZF_ACT_METRIC_FACTS_ALL — Additional metric fact table in the trade management space referencing ACTIVITY_GEO_AREA_ID.
• FND_SECURITY_GROUPS — Security group reference enforcing access control via SECURITY_GROUP_ID.

These relationships confirm the table's role as a geographic scope repository bridging campaign definitions and performance metrics across the AMS and JTF schemas.