Search Results ams_dm_party_details_pk

Overview

AMS_DM_PARTY_DETAILS is a staging table in the AMS (Marketing) schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It functions as a container for generic demographic, firmographic, and credit-related attributes associated with a party, and it holds detail rows that correspond to records in AMS_DM_DRV_STG. The table is consumed by the mining collection pack, a component of Oracle Marketing's data mining and customer intelligence framework, and is classified by ETRM as a staging object rather than a transactional master. Its status is VALID, and it is owned by the AMS schema.

Because the table stores descriptive attributes keyed to a single party identifier, a heuristic Data Vault assessment suggests it behaves as a hub-leaning structure. The primary key, AMS_DM_PARTY_DETAILS_PK, is defined on PARTY_ID, and a unique index, AMS_DM_PARTY_DETAILS_U1, also enforces uniqueness on PARTY_ID. The physical schema documented for 12.2.2 contains 170 columns, which is unusually wide for a staging table and reflects the breadth of third-party data typically loaded from external marketing or credit data providers.

Key Information Stored

PARTY_ID is the surrogate primary key and the column that links this staging record to a party in the broader Trading Community Architecture (TCA) model. The remaining columns fall into several thematic groups:

• Demographic attributes: GENDER, ETHNICITY, MARITAL_STATUS, DEGREE_RECEIVED, SCHOOL_TYPE, and PRESENCE_OF_CHILDREN describe the individual or household.
• Household and lifestyle indicators: HOH_FLAG, HOUSEHOLD_INCOME, HOUSEHOLD_SIZE, APARTMENT_FLAG, RENT_FLAG, RESIDENCE_TYPE, and RESIDENT_LENGTH capture living circumstances.
• Interest and behavioral flags: INTEREST_ART_FLAG, INTEREST_BOOKS_FLAG, INTEREST_MUSIC_FLAG, INTEREST_TRAVEL_FLAG, LEVEL_OF_INTEREST, and INTEREST_TYPE_CODE support segmentation and targeting.
• Financial and credit data: GROSS_ANNUAL_INCOME, NET_WORTH, TOTAL_ASSETS, TOT_DEBT_OUTSTANDING, NUM_CREDIT_LINES, DNB_SCORE, CREDIT_RATING, CREDIT_HOLD_FLAG, and BANKRUPTCY_FLAG describe creditworthiness and risk.
• Firmographic attributes: NUM_OF_EMPLOYEES, YEAR_ESTABLISHED, LINE_OF_BUSINESS, SIC_CODE1, SIC_CODE2, LEGAL_STATUS, and ENTITY_TYPE characterize commercial parties.
• Location and contact: ADDRESS1, ADDRESS2, CITY, STATE, PROVINCE, COUNTY, ZIP_CODE, COUNTRY, and EMAIL_ADDRESS provide geographic and communication details.
• Security and audit: SECURITY_GROUP_ID (a foreign key to FND_SECURITY_GROUPS), CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER support multi-org security and concurrency control.

Common Use Cases and Queries

Because the object is a staging table, the principal use case is extraction and enrichment of party-level intelligence before it is promoted to production marketing tables or used within mining models. Typical SQL patterns include filtering high-value or high-risk parties, for example selecting PARTY_ID, HOUSEHOLD_INCOME, and DNB_SCORE where BANKRUPTCY_FLAG = 'Y' or CREDIT_HOLD_FLAG = 'Y'. Marketers commonly join this table to AMS_DM_DRV_STG on PARTY_ID to align staged details with derived mining attributes. Reporting queries frequently aggregate on GENDER, HOUSEHOLD_SIZE, or SIC_CODE1 to profile segments.

Related Objects

• AMS_DM_DRV_STG: The derived staging table whose rows correspond to records in this table via PARTY_ID.
• AMS_DM_PARTY_DETAILS_TIME: A companion time-based table referencing this table through PARTY_ID, holding historical or period-specific party attributes.
• FND_SECURITY_GROUPS: Referenced by SECURITY_GROUP_ID, enforcing multi-org access control.
• AMS_DM_PARTY_DETAILS (self-reference): The documented foreign keys show PARTY_ID referencing a party record, reinforcing the hub-style key model.
• AMS_DM_PARTY_DETAILS_PK / AMS_DM_PARTY_DETAILS_U1: The primary key constraint and the unique index that enforce PARTY_ID uniqueness and underpin joins to TCA-based party tables.