Results for “market_qualifier_reference”

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

Overview

The AMS_PARTY_MARKET_SEGMENTS table is a Marketing (AMS) module data object within Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM metadata, it serves as an intersection table that allows a single party to be associated with multiple market segments. Market segments in AMS are represented by cells stored in AMS_CELLS_ALL_B, and this table resolves the many-to-many relationship between those segments and the parties held in HZ_PARTIES. Because it carries foreign keys, context columns, and effective-dating attributes, it functions as the central association record used by Marketing segmentation, targeting, and campaign execution processes.

Under a heuristic Data Vault classification mined from its foreign key structure, this object is best modeled as a link table. It connects the party hub to the market segment (cell) hub while retaining descriptive and qualifier attributes that support the association itself.

Key Information Stored

The table is documented with 26 columns in the 12.2.2 schema. The most operationally significant are listed below. The surrogate primary key is AMS_PARTY_MARKET_SEGMENT_ID, which is enforced by AMS_PARTY_MARKET_SEGMENTS_PK and additionally backed by the unique index AMS_PARTY_MARKET_SEGMENTS_U1.

• AMS_PARTY_MARKET_SEGMENT_ID — Surrogate primary key and documented business-key candidate.
• PARTY_ID — Foreign key to HZ_PARTIES; identifies the associated party.
• MARKET_SEGMENT_ID — Foreign key to AMS_CELLS_ALL_B; identifies the assigned market segment or cell.
• MARKET_SEGMENT_FLAG — Flag denoting segment membership or status for the party.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Effective-date range controlling when the association is active.
• CUST_ACCOUNT_ID — Foreign key to HZ_CUST_ACCOUNTS, linking the segment to a customer account.
• CUST_ACCT_SITE_ID — Foreign key to HZ_CUST_ACCT_SITES_ALL, referencing a specific account site.
• SITE_USE_ID and BILL_TO_SITE_USE_ID — Site use references that qualify the association.
• SITE_USE_CODE — Code identifying the site use purpose for the association.
• TERRITORY_ID — Territory reference associated with the segment assignment.
• MARKET_QUALIFIER_TYPE and MARKET_QUALIFIER_REFERENCE — Qualifier type and reference value refining the segment assignment.
• ROLLUP_PARTY_ID — Rollup party reference supporting hierarchical party grouping.
• ORG_ID and SECURITY_GROUP_ID — Multi-org and security group context; SECURITY_GROUP_ID references FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — Optimistic locking column, together with the standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical usage includes identifying which parties belong to a given segment, reporting active segment membership for a date range, and joining to customer accounts and sites for campaign targeting.

SELECT p.party_id, m.market_segment_id, m.start_date_active, m.end_date_active
FROM   ams.ams_party_market_segments m
JOIN   hz.hz_parties p ON p.party_id = m.party_id
WHERE  m.market_segment_id = :segment_id
AND    SYSDATE BETWEEN m.start_date_active AND NVL(m.end_date_active, SYSDATE);

A second pattern returns segment membership for a customer account, joining to HZ_CUST_ACCOUNTS through CUST_ACCOUNT_ID and honoring ORG_ID for multi-org security. Reporting queries commonly filter on MARKET_SEGMENT_FLAG and restrict by effective dates to reflect current eligibility.

Related Objects

• HZ_PARTIES — Joined on AMS_PARTY_MARKET_SEGMENTS.PARTY_ID = HZ_PARTIES.PARTY_ID.
• AMS_CELLS_ALL_B — Joined on AMS_PARTY_MARKET_SEGMENTS.MARKET_SEGMENT_ID = AMS_CELLS_ALL_B.MARKET_SEGMENT_ID.
• FND_SECURITY_GROUPS — Joined on AMS_PARTY_MARKET_SEGMENTS.SECURITY_GROUP_ID.
• HZ_CUST_ACCOUNTS — Joined on AMS_PARTY_MARKET_SEGMENTS.CUST_ACCOUNT_ID.
• HZ_CUST_ACCT_SITES_ALL — Joined on AMS_PARTY_MARKET_SEGMENTS.CUST_ACCT_SITE_ID.
• OZF_PARTY_MARKET_SEGMENTS_T — Referencing table via AMS_PARTY_MARKET_SEGMENT_ID, supporting the Trade Management/order capture layer.