Search Results ams_campaigns_all_b

Overview

The AMS_CAMPAIGNS_ALL_B table is the core base table for storing campaign definitions within the Oracle E-Business Suite Marketing (AMS) module. It serves as the primary repository for the fundamental attributes and master data of a marketing campaign, forming the central node in a complex data model that supports campaign planning, execution, and analysis. Its role is critical for managing the campaign lifecycle, from creation and budgeting to tracking performance against objectives and metrics. The table's "ALL" suffix and multi-organization primary key (CAMPAIGN_ID) indicate it is a multi-org enabled table designed to support data partitioning across operating units, a standard architectural pattern in Oracle EBS applications for versions 12.1.1 and 12.2.2.

Key Information Stored

The table's primary identifier is the system-generated CAMPAIGN_ID. A unique key is also enforced on the SOURCE_CODE column, which typically holds a user-defined identifier for the campaign. The table stores essential campaign details, including hierarchical relationships via PARENT_CAMPAIGN_ID for multi-level campaign structures. It links campaigns to other critical EBS entities through foreign key columns: it associates a campaign with a JTF_TASKS_B record for task management, an OZF_FUNDS_ALL_B record for funding source (FUND_SOURCE_ID), and a JTF_RS_RESOURCE_EXTNS record for the campaign owner (OWNER_USER_ID). Furthermore, it defines campaign context through references to media (MEDIA_ID), channels (CHANNEL_ID), geographic location (CITY_ID), and system status (USER_STATUS_ID). The APPLICATION_ID column ties the campaign to a specific EBS application via FND_APPLICATION.

Common Use Cases and Queries

This table is central to numerous marketing operations. Common use cases include generating master campaign lists, building hierarchical campaign roll-up reports, and serving as the source for data in campaign management user interfaces. For reporting, it is frequently joined with its related transactional tables to analyze campaign performance. A typical query pattern involves selecting campaign headers and joining to lookup tables for descriptive values. For example, to list active campaigns with their owners and statuses, one might use a query such as:

• SELECT cam.campaign_id, cam.source_code, cam.name, res.resource_name, sts.status_name FROM ams_campaigns_all_b cam, jtf_rs_resource_extns res, ams_user_statuses_b sts WHERE cam.owner_user_id = res.resource_id AND cam.user_status_id = sts.user_status_id AND cam.end_date >= SYSDATE;

Another critical use case is integrating campaign data with budgeting (AMS_ACT_BUDGETS) and forecasting (AMS_ACT_FORECASTS_ALL) modules, where the CAMPAIGN_ID serves as the BUDGET_SOURCE_ID or ACT_FCAST_USED_BY_ID.

Related Objects

As the central campaign entity, AMS_CAMPAIGNS_ALL_B has extensive relationships. It is the parent table for a significant portion of the AMS transactional schema. Key documented foreign key relationships include:

• Self-Referential: AMS_CAMPAIGNS_ALL_B.PARENT_CAMPAIGN_ID references AMS_CAMPAIGNS_ALL_B.CAMPAIGN_ID for campaign hierarchies.
• Task Management: TASK_ID references JTF_TASKS_B.TASK_ID.
• Funding: FUND_SOURCE_ID references OZF_FUNDS_ALL_B.FUND_ID.
• Campaign Components: The CAMPAIGN_ID is referenced as a foreign key by numerous AMS activity tables, including:
  • AMS_ACT_BUDGETS (BUDGET_SOURCE_ID, ACT_BUDGET_USED_BY_ID)
  • AMS_ACT_FORECASTS_ALL (ACT_FCAST_USED_BY_ID)
  • AMS_ACT_METRICS_ALL (ACT_METRIC_USED_BY_ID)
  • AMS_ACT_OFFERS (ACT_OFFER_USED_BY_ID)
  • AMS_ACT_OBJECTIVES (ACT_OBJECTIVE_USED_BY_ID)
  • AMS_ACT_ACCESS (ACT_ACCESS_TO_OBJECT_ID)
• Reference Data: Foreign keys to JTF_LOC_HIERARCHIES_B (CITY_ID), AMS_MEDIA_B (MEDIA_ID), AMS_USER_STATUSES_B (USER_STATUS_ID), and FND_APPLICATION (APPLICATION_ID).