Results for “preference_topic_type_code”

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

Overview

AMS_CONTACT_RESTRICTIONS_V is a VALID database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. The view presents a filtered, reporting-oriented projection of contact preference records that represent active "do not contact" restrictions. It is defined in both Oracle EBS 12.1.1 and 12.2.2 and is documented in the ETRM repository under object type VIEW.

The defining characteristic of the view is that it narrows the broader contact preference dataset to a specific, business-relevant subset. The view text applies three WHERE predicates to its source: PREFERENCE_CODE = 'DO_NOT', CONTACT_LEVEL_TABLE = 'HZ_PARTIES', and PREFERENCE_END_DATE IS NULL. The result is a set of currently effective, non-expired restrictions recorded at the party level. Because the view does not join or denormalize additional attributes, it functions primarily as a convenience and security filter over a single TCA (Trading Community Architecture) table rather than as a complex analytical construct. It serves reporting, integration, and marketing suppression scenarios where a consumer needs the population of parties that must not be contacted.

Underlying Base Objects

The view is defined over a single referenced base object, documented in the ETRM metadata as HZ_CONTACT_PREFERENCES (SYNONYM). In the APPS schema, HZ_CONTACT_PREFERENCES resolves through a synonym to the underlying TCA table, which stores contact preference and restriction records for parties, contacts, and related entities. The view aliases this source as HCPREF in its query text.

No joins, unions, or aggregations are present: every column exposed by the view maps one-to-one to a column of the same name in the base table. The single-filter design means the view inherits the base table's partitioning, indexing, and grants, and that any row-level security or MOAC behavior applied to the base table is reflected without additional logic. The CONTACT_LEVEL_TABLE predicate fixes the restriction granularity to HZ_PARTIES, so records stored for other contact levels such as HZ_CONTACTS are deliberately excluded.

Key Columns

Because the view projects the complete column list of the base table, the most significant columns for understanding and using it are:

• CONTACT_PREFERENCE_ID — Primary key of the underlying preference record; the stable identifier used in joins and API calls.
• CONTACT_LEVEL_TABLE — The entity level at which the preference applies. When queried through this view it is always HZ_PARTIES.
• CONTACT_LEVEL_TABLE_ID — The identifier of the specific record in the level table named by CONTACT_LEVEL_TABLE. In this view it is the party identifier. This column is the object of the user's search term "contact_level_table_id."
• CONTACT_TYPE and PREFERENCE_CODE — Classify the preference; PREFERENCE_CODE is always DO_NOT in this view.
• PREFERENCE_TOPIC_TYPE, PREFERENCE_TOPIC_TYPE_ID, and PREFERENCE_TOPIC_TYPE_CODE — Describe the topic or channel to which the restriction applies.
• PREFERENCE_START_DATE, PREFERENCE_END_DATE, and the start/end time hour and minute columns — Define the effective window. PREFERENCE_END_DATE is always NULL here, denoting an open-ended restriction.
• MAX_NO_OF_INTERACTIONS and MAX_NO_OF_INTERACT_UOM_CODE — Express volume-based caps where applicable.
• STATUS, OBJECT_VERSION_NUMBER, APPLICATION_ID, and CREATED_BY_MODULE — Standard TCA attributes supporting concurrency control and multi-product ownership.
• Audit columns such as CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE.

Common Use Cases and Queries

The view is typically used to drive marketing suppression, compliance checks, and integration feeds that must honor party-level "do not contact" instructions. A representative query retrieving current restrictions for a party is:

• SELECT contact_preference_id, contact_level_table_id, preference_code, preference_topic_type_code, preference_start_date FROM ams_contact_restrictions_v WHERE contact_level_table_id = :party_id;

A common anti-join pattern excludes restricted parties from a target audience:

• SELECT p.party_id FROM hz_parties p WHERE NOT EXISTS (SELECT 1 FROM ams_contact_restrictions_v r WHERE r.contact_level_table_id = p.party_id);

Because the view is not parameterized or secured beyond the base table, callers joining on CONTACT_LEVEL_TABLE_ID must ensure the correlated key is a party identifier rather than a contact identifier. Consumers should also note that a future-dated restriction with a NULL end date is not excluded by the view, so date-sensitive logic should be applied by the caller when only currently effective restrictions are required.