Results for “preference_start_date”

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

Overview

AMS_PERSON_SITES_V is a Marketing (AMS) schema view owned by the APPS account and defined over Oracle Trading Community Architecture (TCA) entities. Its documented purpose is to link a person to a physical location, exposing party site attributes alongside the contact preference records associated with those sites. In the Oracle E-Business Suite 12.1.1 and 12.2.2 codelines, the view is used primarily for reporting and integration scenarios where downstream AMS processes require a consolidated picture of a person's active party sites and the associated "DO" preference windows.

Because the view joins HZ_PARTY_SITES, HZ_PARTIES, and HZ_CONTACT_PREFERENCES, it functions as a filtered projection rather than a raw table extract. It is flagged VALID in ETRM, and the presence of the HZ_CONTACT_PREFERENCES columns (PREFERENCE_START_DATE, PREFERENCE_START_TIME_HR, PREFERENCE_START_TIME_MI, PREFERENCE_END_DATE, and the corresponding end time components) is the reason a search for "preference_start_date" resolves to this object.

Underlying Base Objects

The view is defined over four documented TCA base objects, all referenced through APPS synonyms:

• HZ_PARTY_SITES — the driving table, aliased HPS, providing party site identity, site name/number, region, mailstop, language, addressee, and address flags.
• HZ_PARTIES — aliased HP, supplying the party status and party type used to restrict the result set to active persons.
• HZ_CONTACT_PREFERENCES — aliased HCPREF, joined externally via CONTACT_LEVEL_TABLE = 'HZ_PARTY_SITES' and CONTACT_LEVEL_TABLE_ID = PARTY_SITE_ID.
• HZ_LOCATIONS — the underlying location entity referenced for the physical address; LOCATION_ID is surfaced on the view through the party site.

Filter predicates enforce that only active party sites (HPS.STATUS = 'A'), active persons (HP.STATUS = 'A' and HP.PARTY_TYPE = 'PERSON'), and user-entered content sources (HPS.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED') appear. The contact preference join is outer (+) and restricted to the preference code 'DO' with an open-ended window (PREFERENCE_END_DATE (+) IS NULL), so a person site is retained even when no current preference row exists.

Key Columns

• PARTY_SITE_ID / PARTY_ID / LOCATION_ID — the primary keys linking a person to a physical site and location.
• PARTY_SITE_NAME, PARTY_SITE_NUMBER — the business-facing identifiers for the site.
• IDENTIFYING_ADDRESS_FLAG — indicates whether the site is the identifying address for the party.
• REGION, MAILSTOP, LANGUAGE, ADDRESSEE — descriptive site metadata used in direct-marketing output and correspondence formatting.
• STATUS — the party site status; restricted to 'A' (active) by the view definition.
• PREFERENCE_START_DATE, PREFERENCE_START_TIME_HR, PREFERENCE_START_TIME_MI — the effective start of the current "DO" contact preference window; these are the columns that match a search for preference_start_date.
• PREFERENCE_END_DATE, PREFERENCE_END_TIME_HR, PREFERENCE_END_TIME_MI — the end of the preference window, currently null for open-ended preferences.

Common Use Cases and Queries

Typical usage includes marketing list validation, direct-mail selection, and reconciliation of person-to-site address data against active DO preferences. A standard query retrieves the person's site information together with the active preference window:

• SELECT party_id, party_site_id, party_site_name, preference_start_date, preference_end_date FROM ams_person_sites_v WHERE party_id = :p_party_id;
• SELECT party_site_id, mailstop, language, preference_start_date FROM ams_person_sites_v WHERE identifying_address_flag = 'Y';
• SELECT COUNT(*) FROM ams_person_sites_v WHERE preference_start_date >= :p_from_date;

Because the outer join guarantees one row per active person site regardless of preference state, callers should not assume PREFERENCE_START_DATE is populated. Consumers should also account for the view's implicit filters, since its result set will not include inactive sites, organizations, or non-USER_ENTERED content, which differ from direct queries against HZ_PARTY_SITES.