Results for “ams_org_site_uses_v”

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

Overview

AMS_ORG_SITE_USES_V is a Marketing (AMS) product view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a filtered, denormalized projection of organization party site usage data drawn from the Oracle Trading Community Architecture (TCA) model. Specifically, the view exposes only those party site uses that are flagged as primary for their site use type (PRIMARY_PER_TYPE = 'Y'), belong to active organizations (party type 'ORGANIZATION', status 'A'), and relate to identifying addresses that were manually entered by a user. This makes the view a convenient source of canonical, primary organization site information for Marketing reporting and integration, sparing callers from repeatedly reconstructing the same multi-table join and filter predicate set against the underlying TCA tables.

Underlying Base Objects

The view is defined over three TCA base objects, each referenced through a synonym in the APPS schema: HZ_PARTIES, HZ_PARTY_SITES, and HZ_PARTY_SITE_USES. The join path is hierarchical. HZ_PARTY_SITE_USES (alias HPSU) links to HZ_PARTY_SITES (alias HPS) on PARTY_SITE_ID, and HZ_PARTY_SITES links to HZ_PARTIES (alias HP) on PARTY_ID. The WHERE clause imposes five filters: HPS.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'; HP.STATUS = 'A'; HP.PARTY_TYPE = 'ORGANIZATION'; HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'; and HPSU.PRIMARY_PER_TYPE = 'Y'. The view therefore returns one row per qualifying primary site use. Because it reads live TCA data, it is not a snapshot or materialized object and reflects current party and site records at query time.

Key Columns

• PARTY_SITE_USE_ID — Primary key of the underlying party site use record; uniquely identifies each returned row.
• PARTY_SITE_ID — Foreign key to the party site (address) to which the use applies.
• SITE_USE_TYPE — Classifies the purpose of the site use, such as billing, shipping, or marketing.
• BEGIN_DATE / END_DATE — Effective date range for the site use, supporting date-sensitive reporting.
• PRIMARY_PER_TYPE — Indicates whether the use is the primary one of its type; always 'Y' in this view.
• REGION — Region component from the party site address.
• MAILSTOP — Mail stop information associated with the site.
• LANGUAGE — Language attribute carried on the party site record.

Common Use Cases and Queries

Marketing and CRM developers use this view to retrieve a single authoritative organization address per site use type without writing the TCA join manually. Common scenarios include driving campaign mailings, populating contact/lead integrations, and validating primary addresses in custom concurrent programs. A typical query retrieves primary billing or shipping sites for an organization:

SELECT party_site_id, site_use_type, region, mailstop, language FROM apps.ams_org_site_uses_v WHERE party_site_use_id = :p_site_use_id;

To filter by use type and effective dates:

SELECT party_site_id, site_use_type, begin_date, end_date FROM apps.ams_org_site_uses_v WHERE site_use_type = 'BILL_TO' AND SYSDATE BETWEEN begin_date AND NVL(end_date, SYSDATE);

Because the view pre-filters on PRIMARY_PER_TYPE = 'Y', callers need not add that predicate; they should, however, join back to HZ_PARTIES or HZ_PARTY_SITES when the party name or full formatted address is required, as these are not exposed as columns here.