Results for “do_not_mail”

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

Overview

AMS_SITE_DONOT_MAIL_V is a Marketing (AMS) module database view in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to return the party sites that are not to be mailed, and it is used primarily during list generation in marketing campaign execution. When a campaign or list is built, the list generation process must exclude contacts who have placed a "do not mail" restriction on themselves. This view supplies exactly that set of suppressed sites, allowing the list build to filter out records that must not receive printed correspondence.

The view presents a single logical row per restricted party site, returning a literal "Y" flag in the DO_NOT_MAIL column together with the contact restriction attributes and the underlying subject identifier. Because the column DO_NOT_MAIL always evaluates to 'Y', the view is best understood as a membership set: any row returned represents a site that is currently suppressed from mailing. The view is read-only and is not itself implemented as a stored database object in the documented environment; its definition is supplied as a query against the contact restrictions table.

Underlying Base Objects

The view is defined over a single documented base table: HZ_CONTACT_RESTRICTIONS, aliased as C in the view text. The ETRM metadata documents no other referenced base objects. HZ_CONTACT_RESTRICTIONS is the Trading Community Architecture (TCA) table that stores restrictions applied to parties, party sites, and contacts, including "do not mail," "do not call," and similar contact preference controls.

The view text applies several selective predicates against this table. It filters on CONTACT_TYPE = 'MAIL', restricts the result to restrictions that are currently in force using SYSDATE BETWEEN NVL(RESTRICT_START_DATE, SYSDATE) AND NVL(RESTRICT_END_DATE, SYSDATE), and limits the subject to C.SUBJECT_TABLE = 'HZ_PARTY_SITES'. A correlated subquery further constrains the result to the latest restriction for each subject by matching CONTACT_RESTRICTION_ID against the maximum ID from HZ_CONTACT_RESTRICTIONS for the same SUBJECT_ID under the same conditions. In this way only the most recent active mail restriction per party site is returned.

Key Columns

• DO_NOT_MAIL — Literal 'Y'; indicates the site is suppressed from mailing.
• CONTACT_RESTRICTION_ID — Primary identifier of the restriction record in HZ_CONTACT_RESTRICTIONS.
• CONTACT_TYPE — Restriction channel; fixed to 'MAIL' in this view.
• RESTRICT_START_DATE / RESTRICT_END_DATE — Effective period of the restriction.
• RESTRICT_REASON — Reason the restriction was recorded.
• SUBJECT_TABLE — Source entity type; fixed to 'HZ_PARTY_SITES'.
• SUBJECT_ID — Identifier of the restricted subject (the party site).
• PARTY_SITE_ID — Alias of SUBJECT_ID, exposing the party site identifier directly.
• 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 exclusion of suppressed sites during marketing list generation. A typical query returns the party site identifiers to be excluded from a target list:

• SELECT party_site_id, restrict_reason FROM ams_site_donot_mail_v;
• SELECT party_site_id FROM ams_site_donot_mail_v WHERE TRUNC(restrict_start_date) <= TRUNC(SYSDATE);
• Anti-join usage: SELECT s.party_site_id FROM hz_party_sites s WHERE s.party_site_id NOT IN (SELECT party_site_id FROM ams_site_donot_mail_v);

Because the view already filters on active and current restrictions, consumers need not re-apply the date logic. Note that list generation should verify the SUBJECT_TABLE filter, since only 'HZ_PARTY_SITES' restrictions are surfaced; restrictions recorded at the party or contact level are handled by related views.