Search Results total_num_of_orders




Overview
Explore in AI Chat

Overview

APPS.AMS_LT_PERSON_DETAILS_V is a reporting and integration view in Oracle E-Business Suite that exposes a flattened, denormalized projection of person-type party records held in the Trading Community Architecture (TCA) registry. The view is owned by the APPS schema and is documented within the ETRM (E-Business Suite Technical Reference Manual) metadata set covering releases 12.1.1 and 12.2.2. Its principal purpose is to present person parties in a shape that is convenient for downstream Oracle Marketing, Oracle Advanced Marketing, and customer-facing transactional reporting, without requiring the consumer to join the underlying TCA tables or interpret the party model directly.

The view is restricted by definition to active person records only. Because of this filter, it should be regarded as a current-state snapshot rather than a full historical ledger of party data. It is read-only by nature and is intended for queries, not for DML.

Underlying Base Objects

The view is defined over a single documented base object, HZ_PARTIES, referenced through a synonym. HZ_PARTIES is the master TCA table that stores both person and organization party records, keyed by PARTY_ID and classified by PARTY_TYPE. The view text applies a predicate of STATUS = 'A' and PARTY_TYPE = 'PERSON', so it returns only persons whose party record is active.

Although the ETRM metadata lists only HZ_PARTIES as the referenced base object, several of the columns exposed (including TOTAL_NUM_OF_ORDERS, TOTAL_ORDERED_AMOUNT, LAST_ORDERED_DATE, CURR_FY_POTENTIAL_REVENUE, NEXT_FY_POTENTIAL_REVENUE, and ANALYSIS_FY) are typically populated in the TCA model by related attribute and profile tables. In this view they are surfaced directly from the party row, which is why the view is often described as a convenience layer over the person party record.

Key Columns

Common Use Cases and Queries

Typical usage includes marketing list generation, customer recency and frequency analysis, and extract feeds into external CRM or analytics platforms. The following sample returns persons ordered by most recent purchase activity:

SELECT party_id, party_name, email_address, total_num_of_orders, total_ordered_amount, last_ordered_date FROM apps.ams_lt_person_details_v WHERE last_ordered_date >= SYSDATE - 365 ORDER BY last_ordered_date DESC;

A second pattern identifies persons who have never ordered, using the LAST_ORDERED_DATE column as the discriminator:

SELECT party_id, party_name, country FROM apps.ams_lt_person_details_v WHERE last_ordered_date IS NULL AND reference_use_flag = 'Y';

Because the view filters to STATUS = 'A' and PARTY_TYPE = 'PERSON', queries against it are naturally scoped to active individuals, eliminating the need for callers to apply those predicates themselves.