Results for “ams_act_resources_v”

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

Overview

AMS_ACT_RESOURCES_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its purpose is to expose the associations between resources and marketing objects such as Events and Campaigns. In ETRM terms, a "resource" is typically a party, contact, or employee assigned to a marketing activity, and this view flattens the relationship into a single readable record per activity-resource assignment. The view is marked VALID in both Oracle EBS 12.1.1 and 12.2.2, and its definition is stable across those releases.

The view is intended for read-only consumption. It joins the transactional assignment table to reference data (party names, user statuses, and lookup meanings) so that report writers and integrators do not have to replicate the join logic themselves. Because it resolves the ROLE_CD into a human-readable meaning, it is particularly useful for reporting on the role a resource plays on an event or campaign.

Underlying Base Objects

The view is defined over four documented base objects:

• AMS_ACT_RESOURCES (SYNONYM) — the primary transactional table holding each activity-resource assignment, aliased AAR.
• AMS_USER_STATUSES_V (VIEW) — supplies the user status name, aliased AUS.
• HZ_PARTIES (SYNONYM) — the TCA party master, providing PARTY_NAME, aliased HZ.
• AMS_LOOKUPS (VIEW) — the AMS lookup view, supplying lookup MEANING for the role code, aliased LK.

The joins are: AAR.USER_STATUS_ID = AUS.USER_STATUS_ID, AAR.RESOURCE_ID = HZ.PARTY_ID, and AAR.ROLE_CD = LK.LOOKUP_CODE with LK.LOOKUP_TYPE fixed to 'AMS_EVENT_ROLE'. The lookup restriction is significant: only role codes that exist as lookup codes under the AMS_EVENT_ROLE lookup type will resolve to a meaning, and the inner join means assignments whose ROLE_CD is missing from that lookup type will be excluded from the result set.

Key Columns

• ACTIVITY_RESOURCE_ID — primary identifier of the assignment record.
• OBJECT_VERSION_NUMBER — optimistic locking/versioning column.
• ACT_RESOURCE_USED_BY_ID and ARC_ACT_RESOURCE_USED_BY — identify the marketing object (for example an event or campaign) that consumes the resource.
• RESOURCE_ID / RESOURCE_NAME — the party identifier and its resolved HZ_PARTIES party name.
• ROLE_RELATE_ID — related role association identifier.
• ROLE_CD / ROLE_TYPE — the raw role code and its resolved lookup meaning; ROLE_CD is the value most commonly searched by users.
• USER_STATUS_ID / USER_STATUS — the status identifier and its descriptive name.
• SYSTEM_STATUS_CODE — system-level status indicator.
• START_DATE_TIME / END_DATE_TIME / DESCRIPTION — effective period and free-text notes for the assignment.

Common Use Cases and Queries

Typical uses include event resource rosters, campaign staffing reports, and integration extracts feeding downstream systems. The most frequent filter is on ROLE_CD, often paired with the resolved meaning for readability.

Sample query listing resources by role for a given marketing object:

SELECT activity_resource_id,
       resource_id,
       resource_name,
       role_cd,
       role_type,
       user_status,
       start_date_time,
       end_date_time
FROM   apps.ams_act_resources_v
WHERE  act_resource_used_by_id = :p_activity_id
AND    role_cd = :p_role_cd
ORDER  BY resource_name;

A second common pattern aggregates counts of resources by role:

SELECT role_cd, role_type, COUNT(*) resource_count
FROM   apps.ams_act_resources_v
WHERE  start_date_time >= :p_from_date
GROUP  BY role_cd, role_type;

Because the join to HZ_PARTIES and AMS_LOOKUPS is an inner join, queries that expect orphaned assignments or unregistered role codes should target AMS_ACT_RESOURCES directly rather than this view.