Search Results contact_number




Overview
Explore in AI Chat

Overview

AMS_P_CONTACT_V is a marketing-oriented view in the Oracle E-Business Suite (EBS) AMS (Marketing) module. As documented in ETRM metadata for release 12.2.2, the view returns all contacts belonging to an organization and is intended primarily to support list generation within the marketing application. Rather than storing data itself, it presents a consolidated read-only projection of contact and party information drawn from underlying Oracle Trading Community Architecture (TCA) and marketing tables.

Its role in reporting and integration is significant because it exposes contact attributes together with the associated organization party identifier and contact party identifier in a single, denormalized structure. This allows marketing users, list builders, and downstream integrations to retrieve contact details such as name, department, title, and contact number without needing to join the underlying TCA hierarchy manually. The view should be treated as a query-only object; it does not persist data and cannot be used for direct DML.

Underlying Base Objects

The documented view text selects from a contact table (aliased C) and a party table (aliased P), joined through the organization contact identifier. The contact-side columns such as ORG_CONTACT_ID, DEPARTMENT_CODE, DEPARTMENT, TITLE, JOB_TITLE, MAIL_STOP, DECISION_MAKER_FLAG, JOB_TITLE_CODE, MANAGED_BY, REFERENCE_USE_FLAG, and RANK derive from the marketing contact entity. The organization side, aliased O, supplies PARTY_ID as ORGANIZATION_ID.

The party-side columns are extensive and clearly originate from the TCA HZ_PARTIES table. These include PARTY_ID, PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, VALIDATED_FLAG, and a large set of standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and related audit fields), plus ATTRIBUTE1–20 and GLOBAL_ATTRIBUTE columns. Despite the documentation noting that referenced base objects are "none documented" and implementation status as "Not implemented in this database," the view text confirms reliance on the marketing organization contact table and the TCA party table. The relationship is a contact-to-party linkage, where each contact row is associated with a parent organization party and the contact's own person or party record.

Key Columns

  • AMS_CONTACT_ID — Aliased from C.ORG_CONTACT_ID; the primary contact identifier used by the marketing module.
  • ORGANIZATION_ID — O.PARTY_ID; identifies the organization that owns the contact.
  • CONTACT_NUMBER — P.PARTY_NUMBER; the unique party number for the contact. This is the column most frequently referenced by users searching on "contact_number."
  • CONTACT_NAME — P.PARTY_NAME; the display name of the contact.
  • PARTY_ID / PARTY_TYPE / VALIDATED_FLAG — TCA identifiers and status for the contact party record.
  • DEPARTMENT, DEPARTMENT_CODE, TITLE, JOB_TITLE, JOB_TITLE_CODE — Marketing-relevant professional attributes.
  • DECISION_MAKER_FLAG, RANK, REFERENCE_USE_FLAG — Qualification and priority indicators used during list generation.
  • MAIL_STOP, MANAGED_BY — Administrative routing and ownership fields.
  • Standard WHO and ATTRIBUTE1–20 columns — Audit and flexfield support inherited from HZ_PARTIES.

Common Use Cases and Queries

The principal use case is marketing list generation, where contacts for a target organization are extracted to build campaign audiences. A second use case is integration, where external systems need a flattened contact-plus-party extract. A representative query retrieving contacts by organization is:

SELECT ams_contact_id, organization_id, contact_number, contact_name, department, job_title FROM ams_p_contact_v WHERE organization_id = :p_org_id;

To locate a contact by its party number, the search on contact_number becomes:

SELECT organization_id, ams_contact_id, contact_name, contact_number FROM ams_p_contact_v WHERE contact_number = :p_party_number;

Filtering decision makers for targeted campaigns uses the DECISION_MAKER_FLAG column. Because the view is not implemented in every database, all queries should be validated against the target instance before deployment in concurrent programs or reports.