Results for “ams_persons_v”

18 results




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

Overview

AMS_PERSONS_V is a marketing-facing view owned by the APPS schema in Oracle E-Business Suite, defined in the AMS (Marketing) product. It exposes a denormalized, read-only presentation of person-type parties, joining the Trading Community Architecture (TCA) party record in HZ_PARTIES to the corresponding primary phone contact point in HZ_CONTACT_POINTS. Because the view restricts output to parties where PARTY_TYPE = 'PERSON' and STATUS = 'A' (active), it represents an active-person directory suitable for reporting, list generation, campaign audience selection, and lightweight integration scenarios. It is not a transactional entity; it does not store data itself and is rebuilt on each query from its base tables. The view is documented as VALID in ETRM metadata for 12.2.2, and its column list aligns with the 12.1.1 implementation. The user search term last_order_date corresponds to the view column documented as LAST_ORDER_DATE, which maps to the LAST_ORDERED_DATE column on HZ_PARTIES.

Underlying Base Objects

The view text selects from two documented base objects, both exposed in APPS as synonyms:

  • HZ_PARTIES (aliased PRSN) — the primary TCA party master row. It supplies person name components, party status, address elements, email, and the last-ordered date.
  • HZ_CONTACT_POINTS (aliased CPPH) — the TCA contact point table, joined on OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID with OWNER_TABLE_NAME = 'HZ_PARTIES', CONTACT_POINT_TYPE = 'PHONE', and PRIMARY_FLAG = 'Y'. All join predicates to HZ_CONTACT_POINTS use Oracle outer-join syntax (+), so persons without a designated primary phone still return a row with null phone columns.

The join is one-to-at-most-one: only the primary phone contact point is retrieved. A single HZ_PARTIES person row can therefore appear only once unless data integrity permits multiple flagged primary phones.

Key Columns

Common Use Cases and Queries

Typical usage includes recency analysis for marketing segmentation (who ordered most recently), audience extraction for campaigns, and reconciliation between TCA party data and downstream reports.

Active persons who have ordered within a window:

  • SELECT party_id, person_name, email_address, last_order_date FROM ams_persons_v WHERE last_order_date >= SYSDATE - 90 ORDER BY last_order_date DESC;
  • SELECT city, country, COUNT(*) FROM ams_persons_v WHERE last_order_date IS NOT NULL GROUP BY city, country;
  • SELECT person_name, concatenated_phone FROM ams_persons_v WHERE phone_number IS NULL;

When the last_order_date is used for reporting, note that it reflects the value maintained on HZ_PARTIES and may be null for persons with no order history. Because the view filters on active status and person party type, counts will not match the full HZ_PARTIES population.