Results for “ams_p_do_not_mail_v”

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

Overview

AMS_P_DO_NOT_MAIL_V is a public (P) view in the Oracle Advanced Marketing (AMS) module. Its documented purpose is to return organization contacts who are not to be mailed, and it is used primarily during list generation for marketing campaigns. In practical terms, the view defines the suppression set for direct-mail activities: any party appearing in this view should be excluded from a mailing list. This is part of Oracle's contact-preference and contact-restriction framework, which lets organizations honor do-not-mail, do-not-call, and similar contact preferences stored against parties and contacts.

The user query referenced contact_restriction_id, which is both a column returned by this view and the primary key of the underlying restriction table. In an EBS 12.1.1 / 12.2.2 context, this column is the natural join key between the restriction record and the marketing suppression logic.

Underlying Base Objects

The view text identifies a single underlying base table: HZ_CONTACT_RESTRICTIONS, aliased as C. The ETRM metadata lists "referenced base objects: none documented," but the view SQL confirms HZ_CONTACT_RESTRICTIONS is the only table accessed. No joins to HZ_PARTIES are performed in the view itself; instead, the subject table is filtered to 'HZ_PARTIES' via a WHERE predicate, so the restriction is scoped to party-level records rather than person-level or contact-level records.

Note that the ETRM entry records "Not implemented in this database" for implementation/DBA data, meaning the view is delivered by the AMS/HZ schema set but may not be present in every environment or may be a documented-but-inactive artifact depending on patch level and licensing.

Key Columns

• DO_NOT_MAIL — A literal 'Y', confirming the semantics of every row returned by the view.
• CONTACT_RESTRICTION_ID — The restriction record identifier; also used in the correlated subquery to select the most recent restriction per subject.
• CONTACT_TYPE — Restricted to 'MAIL' in the view definition.
• RESTRICT_START_DATE / RESTRICT_END_DATE — Effective window of the restriction. The view only returns rows where SYSDATE falls between these values (using NVL defaults of SYSDATE when either is null, so an open-ended restriction is always active).
• RESTRICT_REASON — Free-text or coded reason for the restriction.
• SUBJECT_TABLE / SUBJECT_ID — The restricted entity; SUBJECT_TABLE is fixed to 'HZ_PARTIES', and SUBJECT_ID is the party identifier.
• PARTY_ID — An alias of SUBJECT_ID, provided for convenience so callers can join directly to party-based queries.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, WH_UPDATE_DATE.

Common Use Cases and Queries

The primary use case is suppression logic during marketing list generation: a campaign list build queries this view and excludes any PARTY_ID present. A secondary use is auditing active do-not-mail restrictions.

To retrieve all currently active do-not-mail parties:

SELECT party_id
FROM   ams_p_do_not_mail_v;

To identify parties whose do-not-mail status was recently set:

SELECT party_id, contact_restriction_id, restrict_start_date, restrict_reason
FROM   ams_p_do_not_mail_v
WHERE  restrict_start_date >= SYSDATE - 30;

To exclude suppressed parties from a candidate list:

SELECT p.party_id, p.party_name
FROM   hz_parties p
WHERE  NOT EXISTS (SELECT 1
                   FROM   ams_p_do_not_mail_v d
                   WHERE  d.party_id = p.party_id);

Because the view applies the "latest restriction per subject" correlated subquery on CONTACT_RESTRICTION_ID, callers do not need to deduplicate multiple restriction records; the view returns at most one row per party for active MAIL restrictions.