Search Results ams_act_campaigns_v




Overview
Explore in AI Chat

Overview

AMS_ACT_CAMPAIGNS_V is a reporting view owned by the APPS schema in the Oracle E-Business Suite Marketing (AMS) module. Its documented purpose is to return all marketing campaigns that promote marketing events. In the broader TCA/AMS object-association model, a campaign is treated as a "using object" that is associated with a "master object" (typically an event or another parent entity). The view flattens that many-to-many relationship into a single denormalized row per campaign-to-master-object association, making it suitable for operational reports, concurrent-program extracts, and integration interfaces that must retrieve campaign context alongside the object it promotes.

The view is status VALID in ETRM 12.2.2 (and is present in 12.1.1), and is documented as Oracle Proprietary, Confidential Information. Because it joins only to other APPS views and a synonym, it is a read-only presentation object; no DML should be issued against it.

Underlying Base Objects

The view text is defined over three documented referenced objects plus the synonym layer:

  • AMS_OBJECT_ASSOCIATIONS (SYNONYM, aliased OBJ) — supplies the association identifier, usage type, master object identity, using object identity, object type codes, and the object version number used for optimistic locking.
  • AMS_CAMPAIGNS_V (VIEW, aliased CAM) — supplies campaign attributes: campaign ID and name, purpose code and purpose, owner user ID, and actual plan start/end dates.
  • AMS_LOOKUPS (VIEW, aliased LKUP1/LKUP2/LKUP3) — outer-joined three times to decode USAGE_TYPE ('AMS_OBJECT_USAGE_TYPE'), MASTER_OBJECT_TYPE ('AMS_MASTER_OBJECT_TYPE'), and USING_OBJECT_TYPE ('AMS_USING_OBJECT_TYPE') into their meanings. Because the joins are outer (+), missing lookups return NULL rather than dropping the campaign row.
  • AMS_UTILITY_PVT (PACKAGE) — documented as a referenced object, supporting the underlying view definitions rather than being joined directly.

The driving predicate is OBJ.USING_OBJECT_TYPE = 'CAMP' combined with OBJ.USING_OBJECT_ID = CAM.CAMPAIGN_ID, which restricts the result set to campaigns and links each campaign to the master object(s) it promotes.

Key Columns

Common Use Cases and Queries

Typical uses include campaign-to-event reporting, populating custom BI Publisher or OBIEE extracts, and inbound/outbound integration feeds that must carry campaign context. Because MASTER_OBJECT_ID is not indexed in the view itself, query predicates should filter on association or campaign identifiers where possible.

Retrieve all campaigns promoting a specific master object:

SELECT campaign_id, campaign_name, master_object_id, master_object_type_meaning, usage_type_meaning
FROM   apps.ams_act_campaigns_v
WHERE  master_object_id = :p_master_object_id;

List associations for a given campaign:

SELECT master_object_id, master_object_type_meaning, campaign_start_date, campaign_end_date
FROM   apps.ams_act_campaigns_v
WHERE  campaign_id = :p_campaign_id
ORDER  BY campaign_start_date;

Filter by usage type to isolate relationship categories:

SELECT campaign_name, usage_type, usage_type_meaning
FROM   apps.ams_act_campaigns_v
WHERE  usage_type = :p_usage_type
AND    campaign_owner = :p_user_id;