Results for “ams_iba_tracking_all”

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

Overview

The AMS_IBA_TRACKING_ALL table is an Oracle Marketing (AMS) transactional table that records tracking information for actions performed against postings. In Oracle E-Business Suite 12.1.1 and 12.2.2, it resides in the AMS schema and is registered as a valid, documented object within the ETRM repository. The table supports the interaction and campaign execution framework by capturing the lifecycle of actions tied to interaction action items, providing an audit-style record of what occurred, against which object, and under which organizational and security context.

Because the table is identified in the supplied relationship metadata as standalone, a heuristic Data Vault classification suggests modeling it as a hub candidate, keyed on its unique surrogate identifier, with links outward to action items and security groups. This classification is a modeling suggestion only; in native EBS terms the table operates as a conventional transactional detail table.

Key Information Stored

The table contains 15 documented columns. The central identifier is TRACKING_ID, which is enforced by the primary key constraint AMS_IBA_TRACKING_ALL_PK and by the unique index AMS_IBA_TRACKING_ALL_U1. This column serves simultaneously as the surrogate primary key and the principal business-key candidate, meaning it is the primary join and lookup attribute for the table.

The most significant remaining columns fall into identifiable functional groups:

• Object identification: OBJECT_ID and OBJECT_TYPE record the entity being tracked and its type, allowing the same tracking mechanism to serve multiple posting object categories.
• Party and deliverable context: PARTY_ID ties the tracking record to a customer or prospect party, while DELIVERABLE_ID associates it with a specific marketing deliverable.
• Action context: ACTION_ID and ACTION_ITEM_ID identify the action performed and the underlying action item. The latter is constrained by a foreign key to JTF_IH_ACTION_ITEMS_B.
• Organizational and security context: ORG_ID supports multi-organization data segregation, and SECURITY_GROUP_ID enforces the Marketing security model, with a foreign key to FND_SECURITY_GROUPS.
• Standard who-columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER provide auditing and optimistic locking behavior consistent with the EBS _ALL table convention.

Common Use Cases and Queries

Primary usage centers on auditing and reporting the actions recorded against postings, particularly for marketing campaign execution and interaction history analysis. A typical query retrieves all tracking records for a given action item:

SELECT tracking_id, object_id, object_type, party_id,
       action_id, action_item_id, org_id, creation_date
FROM   ams.ams_iba_tracking_all
WHERE  action_item_id = :p_action_item_id
AND    org_id = :p_org_id;

Because the table carries both OBJECT_TYPE and OBJECT_ID, reporting can aggregate tracking volumes by object type, by party, or by deliverable. Security-aware queries should filter on SECURITY_GROUP_ID consistent with the Marketing responsibility's security profile. Data extraction for warehouse loads commonly keys on LAST_UPDATE_DATE for incremental pulls, using TRACKING_ID as the natural load key.

Related Objects

The FK metadata documents two direct dependencies, and additional related objects follow from the AMS Marketing schema:

• JTF_IH_ACTION_ITEMS_B — referenced via ACTION_ITEM_ID; provides the base action item definition that the tracking record measures.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; resolves the security group governing record visibility.
• AMS_IBA_TRACKING_ALL_PK and AMS_IBA_TRACKING_ALL_U1 — the primary key constraint and unique index on TRACKING_ID, defining access paths.
• AMS postings and interaction tables — parent posting and campaign execution entities referenced indirectly through OBJECT_ID, OBJECT_TYPE, DELIVERABLE_ID, and ACTION_ID.
• FND multi-org views — the _ALL designation implies an operating-unit-filtered view, typically exposed through a corresponding ORG_ID-restricted synonym for reporting responsibilities.