Results for “ams_p_do_not_contact_v”

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

Overview

AMS_P_DO_NOT_CONTACT_V is a public view in the Oracle E-Business Suite Marketing (AMS) module that returns organization contacts who must not be contacted. It exists to serve list generation processes within Oracle Marketing, allowing campaign and list-building logic to exclude restricted parties from outreach activities. The view is documented in ETRM at the 12.2.2 level and applies equally to the 12.1.1 and 12.2.2 code lines, since the underlying Oracle Trading Community Architecture (TCA) contact restriction model is common to both releases. The view is a read-only construct; it holds no data of its own and derives all of its content from the TCA contact restriction tables. A distinctive characteristic documented in the metadata is that the object is not implemented in the database in the reference environment, meaning the view definition exists in the repository but the object may not be physically deployed or may have been superseded by alternative filtering logic in a particular instance. Where it is deployed, it is typically referenced by Marketing list generation programs, suppression list maintenance, and any integration that needs to identify parties carrying an active do-not-contact restriction.

Underlying Base Objects

The view is defined exclusively over HZ_CONTACT_RESTRICTIONS, the TCA table that stores restrictions applied to parties and contacts. The metadata documents no other base objects, and the SELECT statement confirms HZ_CONTACT_RESTRICTIONS as the single source, including a correlated subquery against the same table aliased as C1. The WHERE clause applies four filtering conditions: CONTACT_TYPE must equal 'CONTACT'; the current system date must fall between NVL(RESTRICT_START_DATE, SYSDATE) and NVL(RESTRICT_END_DATE, SYSDATE); SUBJECT_TABLE must equal 'HZ_PARTIES'; and the restriction row must be the one with the maximum CONTACT_RESTRICTION_ID for that subject. This last condition ensures only the most recently created active restriction per party is returned, preventing duplicate rows for parties with multiple historical restrictions. Because the view depends on SYSDATE, results are date-sensitive and change as restrictions expire or become effective.

Key Columns

• DO_NOT_CONTACT — A literal 'Y' flag indicating the row represents a party that must be suppressed from contact.
• CONTACT_RESTRICTION_ID — Primary identifier of the restriction record; also used to select the latest restriction per party.
• CONTACT_TYPE — Restriction scope; the view returns only rows where this equals 'CONTACT'.
• RESTRICT_START_DATE / RESTRICT_END_DATE — Effective window of the restriction; NULL values are treated as open-ended via NVL.
• RESTRICT_REASON — Free-text or coded reason for the restriction.
• SUBJECT_TABLE / SUBJECT_ID / PARTY_ID — Identify the restricted entity. SUBJECT_ID and PARTY_ID expose the same value, constrained to SUBJECT_TABLE = 'HZ_PARTIES'.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and WH_UPDATE_DATE support traceability and warehouse synchronization.

Common Use Cases and Queries

The principal use case is suppression list generation: a Marketing list build performs an anti-join against this view to exclude restricted parties. A basic retrieval of all currently restricted parties is:

• SELECT party_id, restrict_reason, restrict_start_date, restrict_end_date FROM ams_p_do_not_contact_v;
• SELECT c.party_id FROM hz_parties c WHERE NOT EXISTS (SELECT 1 FROM ams_p_do_not_contact_v d WHERE d.party_id = c.party_id); — returns parties with no active restriction and is the typical list-generation filter.
• SELECT party_id FROM ams_p_do_not_contact_v WHERE restrict_reason = 'CUSTOMER_REQUEST'; — segments suppressed parties by reason for compliance reporting.

Because the view is not always implemented, integrations should verify object existence in ALL_VIEWS before referencing it, and fall back to querying HZ_CONTACT_RESTRICTIONS directly where the view is absent. Queries should also account for the SYSDATE dependency so that scheduled list generation reflects the restriction state at run time.