Results for “campaign_status_code”

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

Overview

The AST_GRP_CAMPAIGNS_V view is a TeleSales (AST) reporting and integration artifact owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents the intersection between resource groups defined in the CRM Resource Manager and marketing campaigns defined in Oracle Marketing (AMS), exposing only those group-to-campaign assignments that are currently active. Because the view joins across three product families — TeleSales, Marketing, and CRM Foundation — it allows call center administrators, campaign managers, and downstream integrations to treat group membership and campaign metadata as a single denormalized result set.

For users searching on campaign_status_code, this view is the appropriate access point. The source column STATUS_CODE on AMS_P_CAMPAIGNS_V is exposed here as CAMPAIGN_STATUS_CODE, allowing the operational state of a campaign (for example active versus expired) to be filtered or reported alongside the group responsible for executing it.

Underlying Base Objects

The view is defined over three documented base objects:

• AST_GRP_CAMPAIGNS (referenced through a synonym) — supplies the assignment grain: GROUP_CAMPAIGN_ID, GROUP_ID, CAMPAIGN_ID, the assignment start and end dates, and the ENABLED_FLAG.
• AMS_P_CAMPAIGNS_V (VIEW) — supplies campaign attributes, including CAMPAIGN_NAME, DESCRIPTION, STATUS_CODE, SOURCE_CODE, inbound script and channel flags, and inbound email/phone contact points.
• JTF_RS_GROUPS_VL (VIEW) — supplies the resource group identity and its translated descriptive columns: GROUP_NUMBER, START_DATE_ACTIVE, END_DATE_ACTIVE, GROUP_NAME, and GROUP_DESC.

Because the join conditions also filter on ENABLED_FLAG = 'Y' and on TRUNC(SYSDATE) falling between the assignment's start and end dates (using NVL so that null bounds are treated as open-ended), the view returns a date-effective snapshot rather than all historical assignments.

Key Columns

• GROUP_CAMPAIGN_ID — primary key of the underlying assignment row; the unique identifier for a group-to-campaign link.
• GROUP_ID, CAMPAIGN_ID — foreign keys into the resource group and campaign entities respectively.
• ASSIGN_START_DATE, ASSIGN_END_DATE, ASSIGN_ENABLED_FLAG — the alias form of the assignment validity columns; the view returns only rows where the flag is Y and the system date falls within the interval.
• CAMPAIGN_NAME, CAMPAIGN_DESCRIPTION — human-readable campaign identifiers.
• CAMPAIGN_STATUS_CODE — the campaign's operational status, sourced from AMS_P_CAMPAIGNS_V.STATUS_CODE. This is the column most frequently referenced by lookup-driven reporting and by integrations that gate activity on an active campaign.
• CAMPAIGN_SOURCE_CODE, CAMPAIGN_INBOUND_SCRIPT, CAMPAIGN_OUTBOUND_FLAG, CAMPAIGN_INBOUND_FLAG, CAMPAIGN_INBOUND_EMAIL, CAMPAIGN_INBOUND_PHONE — channel and scripting attributes used during call handling.
• GROUP_NUMBER, GROUP_NAME, GROUP_DESCRIPTION, GROUP_START_DATE_ACTIVE, GROUP_END_DATE_ACTIVE — the group attributes used for assignment and reporting of telemarketing resources.

Common Use Cases and Queries

Typical scenarios include identifying which resource group owns a currently active campaign, populating agent-facing campaign selection lists, and reconciling marketing spend or call volumes by campaign status.

• List active campaigns with their owning group:

  SELECT group_number, group_name,
         campaign_name, campaign_status_code
  FROM   ast_grp_campaigns_v
  WHERE  campaign_status_code = 'ACTIVE';

• Determine all campaigns assigned to a specific group:

  SELECT campaign_name, campaign_status_code,
         assign_start_date, assign_end_date
  FROM   ast_grp_campaigns_v
  WHERE  group_id = :p_group_id
  ORDER  BY campaign_name;

• Report channel coverage for inbound campaigns:

  SELECT campaign_name, campaign_inbound_flag,
         campaign_inbound_phone, campaign_inbound_email
  FROM   ast_grp_campaigns_v
  WHERE  campaign_status_code = 'ACTIVE'
  AND    campaign_inbound_flag = 'Y';

Because the view embeds a SYSDATE predicate and joins two translated views, queries against it are best restricted by group_id or campaign_id where possible. Note that ROW_ID is exposed for the assignment row, but the view remains non-updatable and should be treated strictly as a read-only reporting source.