Results for “ams_iba_post_sum_all”

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

Overview

AMS_IBA_POST_SUM_ALL is an Oracle E-Business Suite table owned by the AMS (Marketing) schema. It stores summary information about the posting of iMarketing / iStore interactive business application (IBA) content, aggregating impression and clickthrough metrics against a specific deliverable and activity attachment. In EBS 12.1.1 and 12.2.2 the table is registered as a VALID database object within the Marketing product family and is used by the online marketing execution components that track how campaign content performs once published to a target page location.

The ETRM heuristic Data Vault classification for this object is standalone. In Data Vault modeling terms this suggests the table behaves as an isolated satellite-like structure rather than a hub or link, since it carries its own surrogate key and does not participate in mined foreign-key relationships to other AMS fact tables, aside from a reference to FND_SECURITY_GROUPS. Modelers should therefore treat POST_SUMMARY_ID as a locally generated business key rather than a true enterprise hub key. Its grain is one summary row per posting context, combining a deliverable, an activity attachment, and a page location.

Key Information Stored

The table contains 14 documented columns. The most significant are summarized below.

• POST_SUMMARY_ID — surrogate primary key, enforced by the unique index AMS_IBA_POST_SUM_ALL_PK and by AMS_IBA_POST_SUM_ALL_U1, which also identifies it as the business-key candidate.
• DELIVERABLE_ID — identifies the marketing deliverable whose posting activity is being summarized.
• ACTIVITY_ATTACHMENT_ID — links the summary to the specific activity attachment used in the posting.
• PAGE_LOCATION_CODE — the page or placement code where the content was rendered.
• TOTAL_IMPRESSION_COUNT — cumulative number of impressions recorded for the posting.
• TOTAL_CLICKTHROUGH_COUNT — cumulative number of clickthrough responses recorded.
• ORG_ID — operating unit identifier supporting Multi-Org access control and reporting.
• SECURITY_GROUP_ID — security group discriminator, referencing FND_SECURITY_GROUPS.
• Audit and concurrency columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER — follow standard EBS WHO-column conventions and support row versioning through the OAF/ADF framework.

Common Use Cases and Queries

Typical usage centers on campaign performance reporting. Marketing users and analysts aggregate impression and clickthrough totals by deliverable, activity attachment, or page location to measure response rates. The ORG_ID and SECURITY_GROUP_ID columns make the table suitable for Multi-Org and security-group filtered queries.

A representative query joining the summary to its security group context:

• SELECT p.POST_SUMMARY_ID, p.DELIVERABLE_ID, p.PAGE_LOCATION_CODE, p.TOTAL_IMPRESSION_COUNT, p.TOTAL_CLICKTHROUGH_COUNT FROM AMS.AMS_IBA_POST_SUM_ALL p WHERE p.ORG_ID = :org_id ORDER BY p.TOTAL_CLICKTHROUGH_COUNT DESC;

A clickthrough-rate calculation using the two counters:

• SELECT DELIVERABLE_ID, TOTAL_CLICKTHROUGH_COUNT / NULLIF(TOTAL_IMPRESSION_COUNT,0) AS CTR FROM AMS.AMS_IBA_POST_SUM_ALL WHERE CREATION_DATE >= :start_date;

Because the table holds cumulative counters rather than per-event rows, it is well suited to dashboards and summary extracts. Incremental change detection can use LAST_UPDATE_DATE, and extracted records can be aligned to a warehouse load by POST_SUMMARY_ID plus OBJECT_VERSION_NUMBER.

Related Objects

• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID; the documented foreign key from AMS_IBA_POST_SUM_ALL.SECURITY_GROUP_ID into FND_SECURITY_GROUPS.
• AMS_IBA_POST_SUM_ALL_PK / AMS_IBA_POST_SUM_ALL_U1 — the primary-key and unique indexes that enforce uniqueness of POST_SUMMARY_ID.
• AMS deliverables and activity attachment objects — DELIVERABLE_ID and ACTIVITY_ATTACHMENT_ID associate the summary with the marketing deliverable and attachment definitions maintained elsewhere in the AMS schema.
• AMS IBA / iMarketing posting and execution components — the runtime processes that populate TOTAL_IMPRESSION_COUNT and TOTAL_CLICKTHROUGH_COUNT and insert summary rows.

As a standalone object with only the security-group relationship documented, AMS_IBA_POST_SUM_ALL should be treated as a summary satellite in reporting models, joined by its own keys and by ORG_ID rather than through dependent child tables.