Search Results ams_dm_party_details_n1

Overview

AMS.AMS_DM_PARTY_DETAILS is a staging table within the Oracle E-Business Suite Advanced Marketing (AMS) schema. It is a member of the Customer Data Management (CDM) and marketing data-mining infrastructure and is populated by the OMO Mining Collection concurrent program. The table is truncated after each collection run; consequently, it should be treated as transient working storage rather than a persistent system of record. Its purpose is to hold generic demographic, financial, lifestyle, and business profiling attributes for parties that are also represented in AMS_DM_DRV_STG.

The table resides in the APPS_TS_SUMMARY tablespace with a PCT Free of 10. It contains 170 documented columns and is available in both Oracle EBS 12.1.1 and 12.2.2. Under the heuristic Data Vault classification mined from its foreign-key structure, the table leans toward a hub pattern, keyed on PARTY_ID. This classification is offered as a modeling suggestion only; the physical implementation is a conventional staging table with a unique constraint and a non-unique supporting index.

Key Information Stored

The table combines a standard WHO audit block (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), an OBJECT_VERSION_NUMBER for optimistic locking, and a SECURITY_GROUP_ID used in hosted environments, which references FND_SECURITY_GROUPS. The most significant descriptive columns include:

• PARTY_ID — numeric party identifier; surrogate primary key and the single column in the AMS_DM_PARTY_DETAILS_U1 unique index, making it the documented business-key candidate.
• PARTY_TYPE — classification of the party (for example, person or organization); also indexed non-uniquely via AMS_DM_PARTY_DETAILS_N1.
• GENDER, ETHNICITY, MARITAL_STATUS — core person demographics used for segmentation.
• PERSONAL_INCOME, HOUSEHOLD_INCOME, HOUSEHOLD_SIZE, HOH_FLAG — household and affluence measures.
• INTEREST_*_FLAG columns — lifestyle indicators covering art, books, movies, music, theater, travel, drink, and smoke.
• OCCUPATION, EMPLOYED_FLAG, YEARS_EMPLOYED, WORK_CLASS_NAME — employment and occupational attributes.
• GROSS_ANNUAL_INCOME, NET_WORTH, TOTAL_ASSETS, TOT_DEBT_OUTSTANDING, DEBT_TO_INCOME_RATIO — financial profiling for consumer records.
• DUNS-related and firmographic columns — NUM_OF_EMPLOYEES, YEAR_ESTABLISHED, LEGAL_STATUS, SIC_CODE1/SIC_CODE2, and related business descriptors.
• PARTY_NAME, EMAIL_ADDRESS, ADDRESS1, ADDRESS2, CITY, STATE, COUNTRY, ZIP_CODE — identity and geographic detail.

Common Use Cases and Queries

Because the table is refreshed by the OMO Mining Collection pack and truncated after each run, its dominant use case is ETL within the marketing data-mining flow: joining staged party attributes to driven-segment records, enriching external analytical models, and feeding campaign targeting. A representative join follows.

• Segment by party: SELECT p.PARTY_ID, p.PARTY_TYPE, p.HOUSEHOLD_INCOME FROM AMS.AMS_DM_PARTY_DETAILS p WHERE p.PARTY_TYPE = :type;
• Correlate with the driven stage: SELECT p.PARTY_ID, d.SEGMENT_ID FROM AMS.AMS_DM_PARTY_DETAILS p, AMS.AMS_DM_DRV_STG d WHERE p.PARTY_ID = d.PARTY_ID;
• Trend by party over time: join AMS_DM_PARTY_DETAILS_TIME on PARTY_ID.
• Tenant-scoped extraction for hosted deployments: filter on SECURITY_GROUP_ID.

Reports typically join PARTY_ID to customer or trading-partner master data, then aggregate on PARTY_TYPE, demographics, or firmographics. Custom code should avoid treating the table as durable; persist derived results to a managed table before the next collection cycle truncates the source.

Related Objects

• AMS.AMS_DM_DRV_STG — primary consumer of this table's rows; joined on PARTY_ID.
• AMS.AMS_DM_PARTY_DETAILS_TIME — references AMS_DM_PARTY_DETAILS.PARTY_ID, providing time-oriented party history.
• AMS.AMS_DM_GEN_STG — staging table populated by the same mining collection pack and truncated after each run.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for multi-tenant security.
• HZ_PARTIES — the Trading Community Architecture master entity corresponding to PARTY_ID.
• AMS_DM_PARTY_DETAILS_PK / AMS_DM_PARTY_DETAILS_U1 — primary key constraint and unique index on PARTY_ID.
• AMS_DM_PARTY_DETAILS_N1 — non-unique index on PARTY_TYPE supporting type-based filtering.