Search Results ams_dm_party_details_time_pk

Overview

AMS_DM_PARTY_DETAILS_TIME is a staging table owned by the AMS schema within the Oracle E-Business Suite Marketing (AMS) module. It functions as a container for time-based, generic party details and holds derived attribute rows that correspond to records in AMS_DM_DRV_STG. The table is populated and consumed by the mining collection pack, which is the component of Oracle Marketing responsible for building and refreshing customer intelligence used by segmentation, list generation, and campaign targeting.

As a staging table, AMS_DM_PARTY_DETAILS_TIME is not a transactional source of truth. It is a transient landing area where computed party-level metrics are assembled before being promoted into downstream mining models or data marts. In the 12.2.2 documented schema the table contains 92 columns, and in 12.1.1 the same structure is present; only the file system or online patching mechanics differ between the two releases, not the table definition. Each row is keyed to a single party.

Applying Data Vault classification heuristics to the documented foreign key structure yields a satellite-leaning classification. The table is anchored by PARTY_ID, which references the parent party dimension, and it carries descriptive, time-windowed measures rather than acting as a hub or a many-to-many link. This suggests the object is best modeled as a satellite attached to a party hub, with SECURITY_GROUP_ID providing the multi-tenant or operating-unit discriminator.

Key Information Stored

The table stores party-level behavioral and interaction metrics. The most operationally significant columns include:

• PARTY_ID — surrogate primary key (AMS_DM_PARTY_DETAILS_TIME_PK) and the join key to the parent party dimension; also repeated in unique index AMS_DM_PARTY_DETAILS_TIME_U1.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and governs record visibility across operating units.
• AGE and DAYS_SINCE_LAST_SCHOOL — demographic timing attributes used in lifecycle segmentation.
• NUM_TIMES_TARGETED and windowed variants such as TIMES_TARGETED_MONTH, TIMES_TARGETED_3_MONTHS through TIMES_TARGETED_12_MONTHS — contact frequency measures.
• DAYS_SINCE_LAST_TARGETED and LAST_TARGETED_CHANNEL_CODE — recency and channel of the most recent contact.
• AVG_ORDER_AMOUNT, TOT_ORDER_AMOUNT_3_MONTHS and yearly equivalents, TOT_NUM_ORDERS_YEAR — revenue and order volume rollups.
• NUM_OF_SR_YEAR, OPEN_SRS, ESCALATED_SRS, AVG_RESOLVE_DAYS_YEAR — service-request history indicators.
• TOT_CALLS, AVG_TALK_TIME, CALL_LENGTH, NUM_OF_INTERACTIONS — telemarketing and interaction telemetry.
• PROFITABILITY and CONTRACT_AMT — financial value markers.

Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER, LAST_UPDATE_LOGIN) are present, consistent with OAF/BC4J-managed staging objects.

Common Use Cases and Queries

The primary use case is scoring and prioritization of parties for target lists. A typical query selects high-value, recently inactive parties:

• SELECT PARTY_ID, PROFITABILITY, DAYS_SINCE_LAST_TARGETED FROM AMS.AMS_DM_PARTY_DETAILS_TIME WHERE TOT_ORDER_AMOUNT_YEAR > :threshold AND DAYS_SINCE_LAST_TARGETED > 90;
• Frequency capping: filter on NUM_TIMES_TARGETED_3_MONTHS to suppress over-contacted parties.
• Channel balance analysis: group by LAST_TARGETED_CHANNEL_CODE and compare against NUM_TIMES_TARGETED_EMAIL, NUM_TIMES_TARGETED_TELEMKT, and NUM_TIMES_TARGETED_DIRECT.
• Service-experience segmentation: correlate AVG_RESOLVE_DAYS_YEAR and ESCALATED_SRS with profitability tiers.

Because this is a staging artifact, reports should join to AMS_DM_PARTY_DETAILS for stable descriptive attributes rather than treating measures here as permanently retained history.

Related Objects

• AMS_DM_PARTY_DETAILS — parent table; join on AMS_DM_PARTY_DETAILS_TIME.PARTY_ID = AMS_DM_PARTY_DETAILS.PARTY_ID.
• AMS_DM_DRV_STG — the driving staging table whose rows these details describe.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for access control.
• AMS_DM_PARTY_DETAILS_TIME_PK / _U1 — primary key and unique indexes enforcing single-row-per-party.
• Mining collection pack concurrent programs — the processes that populate and consume this table.