Search Results ams_dm_party_details_time_u1

Overview

AMS.AMS_DM_PARTY_DETAILS_TIME is a summary table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Advanced Marketing (AMS) module. It belongs to the OMO (Oracle Marketing Online) data mining infrastructure and stores time-based behavioral and transactional metrics aggregated at the party level. The table is populated by the OMO Mining Collection concurrent process, drawing generic party details from the AMS_DM_GEN_STG staging table derived from AMS_DM_DRV_STG. Notably, this table is truncated after each mining run, meaning it functions as a transient analytical work table rather than a persistent transactional store. Its physical storage resides in the APPS_TS_SUMMARY tablespace with a PCT Free of 10, reflecting its role as an aggregation surface rather than an OLTP table.

From a Data Vault modeling perspective, the heuristic classification is satellite-leaning. This is consistent with the table's design: it carries descriptive, time-derived attributes keyed to a single business entity (PARTY_ID) and therefore resembles a satellite surrounding a party hub, with the foreign key to AMS_DM_PARTY_DETAILS anchoring that relationship.

Key Information Stored

The table is keyed on PARTY_ID, which serves as the documented surrogate primary key via AMS_DM_PARTY_DETAILS_TIME_PK. A second unique index, AMS_DM_PARTY_DETAILS_TIME_U1, is also defined on PARTY_ID, confirming the column as the sole business-key candidate for the object. The 92 columns store a rich set of marketing, service, and financial metrics. The most important include:

• PARTY_ID — Primary identifier linking each row to a party in the AMS_DM_PARTY_DETAILS table.
• SECURITY_GROUP_ID — Supports hosted/multi-tenant environments; foreign key to FND_SECURITY_GROUPS.
• AGE and DAYS_SINCE_LAST_SCHOOL — Demographic and engagement recency indicators.
• DAYS_SINCE_LAST_EVENT — Recency of the party's last event attendance.
• NUM_TIMES_TARGETED and TIMES_TARGETED_MONTH / _3 / _6 / _9 / _12_MONTHS — Targeting frequency across rolling windows.
• LAST_TARGETED_CHANNEL_CODE and DAYS_SINCE_LAST_TARGETED — Channel attribution and recency of marketing outreach.
• AVG_DISC_OFFERED and NUM_TYPES_DISC_OFFERED — Promotional intensity and variety.
• NUM_OF_SR_YEAR / _6_MONTHS / _3_MONTHS / _1_MONTH — Service request volume trend.
• TOT_ORDER_AMOUNT_YEAR and TOT_NUM_ORDERS_YEAR — Rolling revenue and order counts.
• AVG_ORDER_AMOUNT, AVG_UNITS_PER_ORDER, and SALES — Order economics.
• PROFITABILITY, COGS, and CONTRACT_AMT — Margin and contract value measures.
• FIRST_CALL_CL_RATE, PCT_FIRST_CLOSED_SRS, and AVG_RESOLVE_DAYS_* — Service quality metrics.

Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER (used for optimistic locking) round out the metadata footprint.

Common Use Cases and Queries

Because the table is truncated after each mining cycle, queries typically run within a single mining window. A common pattern retrieves high-value targets for a campaign:

• Campaign targeting — Select parties with low DAYS_SINCE_LAST_TARGETED and high TOT_ORDER_AMOUNT_YEAR to prioritize outreach.
• Churn-risk reporting — Filter on DAYS_SINCE_ACCT_TERM or DAYS_SINCE_ACCT_SUSPENDED alongside NUM_OF_COMPLAINTS.
• Rolling window analytics — Compare TIMES_TARGETED_MONTH against TIMES_TARGETED_3_MONTHS to detect frequency fatigue.
• Profitability segmentation — Rank parties by PROFITABILITY combined with PCT_FIRST_CLOSED_SRS.

A representative query against the unique index AMS_DM_PARTY_DETAILS_TIME_U1:

SELECT party_id, num_times_targeted, tot_order_amount_year, profitability
FROM ams.ams_dm_party_details_time
WHERE days_since_last_targeted > 30
ORDER BY profitability DESC;

Related Objects

• AMS.AMS_DM_PARTY_DETAILS — Parent party master referenced via PARTY_ID foreign key; provides persistent party attributes absent from the time summary.
• AMS.AMS_DM_GEN_STG — Staging table from which generic party details are sourced during the OMO Mining Collection run.
• AMS.AMS_DM_DRV_STG — Upstream derived staging table feeding AMS_DM_GEN_STG.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID for hosted-environment security partitioning.
• AMS.AMS_DM_PARTY_DETAILS_TIME_PK / _U1 — Primary key and unique index enforcing record uniqueness on PARTY_ID.
• HZ_PARTIES — Foundational Trading Community Architecture party entity that ultimately identifies the party behind PARTY_ID.
• OMO Mining Collection concurrent program — The process that populates and truncates this table on each cycle.

Together these objects form the AMS data mining pipeline that transforms raw party, order, service, and contract activity into the time-based analytical summary consumers rely on for campaign optimization.