Results for “ams_dm_agg_stg”

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

Overview

AMS_DM_AGG_STG is a staging table owned by the AMS (Marketing) schema in Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It functions as a container for the aggregated details of a party, holding pre-computed summary rows that support the rows held in AMS_DM_DRV_STG. The table is consumed by the mining collection pack, a component of the Oracle Marketing data-mining and customer-intelligence feature set, and it is explicitly documented as a staging object rather than a transactional or master-data entity. As staging data, its contents are typically populated by batch collection programs, used for subsequent scoring or segmentation processing, and periodically purged or refreshed.

The ETRM relationship metadata classifies this object heuristically as standalone within the Data Vault model, meaning no foreign-key dependencies to parent hubs or links were mined from the physical schema. Taken as a modeling suggestion rather than a definitive rule, this indicates the table behaves as a self-contained aggregate keyed on a single party identifier, rather than a hub, link, or satellite in a normalized vault design. The documented primary key is AMS_DM_AGG_STG_PK on PARTY_ID, reinforced by unique index AMS_DM_AGG_STG_U1, also on PARTY_ID — establishing one aggregate row per party. The physical schema comprises 25 columns.

Key Information Stored

All rows are uniquely identified by the surrogate primary key PARTY_ID, which is simultaneously the sole business-key candidate via the unique index. Among the 25 documented columns, the most significant fall into several categories:

• Demographic and lifecycle attributes: AGE, DAYS_SINCE_LAST_SCHOOL, and DAYS_SINCE_LAST_EVENT capture party-level characteristics used for segmentation.
• Targeting frequency metrics: NUM_TIMES_TARGETED, TIMES_TARGETED_MONTH, TIMES_TARGETED_3_MONTHS, TIMES_TARGETED_6_MONTHS, and TIMES_TARGETED_12_MONTHS — rolling-window counts of campaign contacts.
• Targeting recency and channel: DAYS_SINCE_LAST_TARGETED and LAST_TARGETED_CHANNEL_CODE indicate when and how the party was most recently contacted.
• Offer analysis: AVG_DISC_OFFERED and NUM_TYPES_DISC_OFFERED summarize the discount treatment applied to the party.
• Account lifecycle measures: DAYS_SINCE_FIRST_CONTACT, DAYS_SINCE_ACCT_ESTABLISHED, DAYS_SINCE_ACCT_TERM, DAYS_SINCE_ACCT_ACTIVATION, and DAYS_SINCE_ACCT_SUSPENDED.
• Channel-specific targeting counts: NUM_TIMES_TARGETED_EMAIL, NUM_TIMES_TARGETED_TELEMKT, and NUM_TIMES_TARGETED_DIRECT.
• Offer-type targeting counts: NUM_TGT_BY_OFFR_TYP1 through NUM_TGT_BY_OFFR_TYP4.

Common Use Cases and Queries

Because AMS_DM_AGG_STG aggregates party-level behavior for the mining collection pack, it is primarily read by Oracle Marketing data-mining programs and by custom reports that profile customers for campaign targeting. Typical queries filter by recency and frequency thresholds to isolate high-value or at-risk segments:

• Selecting parties targeted more than a given threshold in the last twelve months: SELECT PARTY_ID, NUM_TIMES_TARGETED_12_MONTHS FROM AMS.AMS_DM_AGG_STG WHERE NUM_TIMES_TARGETED_12_MONTHS > :n;
• Ranking parties by recency of contact: SELECT PARTY_ID, DAYS_SINCE_LAST_TARGETED FROM AMS.AMS_DM_AGG_STG ORDER BY DAYS_SINCE_LAST_TARGETED;
• Channel-preference reporting using NUM_TIMES_TARGETED_EMAIL, NUM_TIMES_TARGETED_TELEMKT, and NUM_TIMES_TARGETED_DIRECT.
• Joining to party or account tables to enrich the aggregate with names and account status.

Related Objects

The metadata documents no foreign-key relationships, so related objects are those inferred from AMS schema context and the documented linkage to AMS_DM_DRV_STG:

• AMS_DM_DRV_STG — the driving staging table whose rows are aggregated into this container; the documented relationship is one of source detail to party-level summary.
• HZ_PARTIES — join on PARTY_ID to resolve party names and identifiers.
• HZ_CUST_ACCOUNTS — join on PARTY_ID to retrieve account-level context behind the DAYS_SINCE_ACCT_* measures.
• AMS_DM_AGG_STG_PK / AMS_DM_AGG_STG_U1 — the primary key constraint and unique index enforcing one row per PARTY_ID.
• Mining collection pack concurrent programs in the AMS module that populate and consume this staging table.