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
- PERSON_ID / PARTY_ID — The TCA party identifier. Both columns are mapped to PRSN.PARTY_ID and are effectively interchangeable.
- PERSON_NAME, LAST_NAME, FIRST_NAME, MIDDLE_NAME, PREFERRED_NAME — Name attributes sourced from PARTY_NAME, PERSON_LAST_NAME, PERSON_FIRST_NAME, PERSON_MIDDLE_NAME, and KNOWN_AS.
- STATUS_CODE — Always resolves to 'A' (active) because of the view predicate on PRSN.STATUS.
- CONCATENATED_PHONE, PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION, PHONE_TYPE — Phone components from the primary phone contact point, plus a derived formatted phone string.
- EMAIL_ADDRESS — Party email address from HZ_PARTIES.
- CONCATENATED_ADDRESS, ADDRESS, ADDRESS1..ADDRESS4, STATE, CITY, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — Address elements, with two derived display formats.
- LAST_ORDER_DATE — Exposes HZ_PARTIES.LAST_ORDERED_DATE, the date on which the person last placed an order. This is the column matching the user search term last_order_date.
- CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — Standard audit columns from HZ_PARTIES.
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.
-
View: AMS_PERSONS_V 12.2.2
Information about parties such as people.
APPS.AMS_PERSONS_V·↳ HZ_CONTACT_POINTS·↳ HZ_PARTIES·Explore AMS module →
-
View: AMS_PERSONS_V 12.1.1
Information about parties such as people.
APPS.AMS_PERSONS_V·↳ HZ_CONTACT_POINTS·↳ HZ_PARTIES·Explore AMS module →
-
VIEW: APPS.AMS_PERSONS_V 12.2.2
-
VIEW: APPS.AMS_PERSONS_V 12.1.1
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2
-
SYNONYM: APPS.HZ_PARTIES 12.2.2
-
SYNONYM: APPS.HZ_PARTIES 12.1.1
-
12.2.2 DBA Data 12.2.2
-
eTRM - AMS Tables and Views 12.2.2
This table is used to store tracking data for web advertisement and offer type schedules
-
12.1.1 DBA Data 12.1.1
-
eTRM - AMS Tables and Views 12.1.1
This table is used to store tracking data for web advertisement and offer type schedules
-
eTRM - AMS Tables and Views 12.2.2
This table is used to store tracking data for web advertisement and offer type schedules
-
eTRM - AMS Tables and Views 12.1.1
This table is used to store tracking data for web advertisement and offer type schedules