Search Results ams_org_acct_v




Overview
Explore in AI Chat

Overview

AMS_ORG_ACCT_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the AMS (Marketing) product family. Its documented purpose is to expose customer account information in a flattened, denormalized form suitable for marketing operations, list generation, campaign targeting, and downstream integration. The view consolidates attributes from the Oracle Trading Community Architecture (TCA) customer model — accounts, parties, party sites, locations, and site uses — into a single queryable structure. In 12.1.1 and 12.2.2 the object is documented as VALID, and its metadata is catalogued through ETRM with the referenced base synonyms listed below. The view is widely consumed by AMS modules such as lists, campaigns, and one-to-one marketing, where account and party attributes are needed without joining the full TCA hierarchy at runtime.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced through APPS synonyms: FND_TERRITORIES_TL, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTIES, and HZ_PARTY_SITES. HZ_CUST_ACCOUNTS supplies the account header (account number, status, customer type, class, credit and tax attributes). HZ_PARTIES and HZ_PERSON/ORGANIZATION data supply party name, party number, and person name components. HZ_PARTY_SITES and HZ_CUST_ACCT_SITES_ALL link parties to physical addresses, while HZ_LOCATIONS holds the address lines, city, state, postal code, province, county, and country. HZ_CUST_SITE_USES_ALL contributes site-use context such as the identifying address flag. FND_TERRITORIES_TL is joined to resolve the country code to a territory short name for the COUNTRY_NAME column. The joins are performed on CUST_ACCOUNT_ID, PARTY_ID, PARTY_SITE_ID, and location identifiers, producing one row per account site-use combination.

Key Columns

Common Use Cases and Queries

Typical uses include building AMS list membership sources, campaign audience extracts, and account reconciliation reports. Because ACCT_LIFE_CYCLE_STATUS is NULL, code that filters on lifecycle status must fall back to STATUS or join HZ_CUST_ACCOUNTS directly.

Sample query to retrieve active accounts with address and lifecycle context:

  • SELECT cust_account_id, account_number, party_name, city, state, country_name, status, account_established_date FROM ams_org_acct_v WHERE status = 'A' ORDER BY party_name;
  • SELECT account_number, party_number, customer_type, customer_class_code, primary_salesrep_id FROM ams_org_acct_v WHERE identifying_address_flag = 'Y';
  • SELECT party_id, party_name, email_address, country_name FROM ams_org_acct_v WHERE cust_account_id = :account_id;

Because the view joins multiple TCA tables, queries benefit from filters on CUST_ACCOUNT_ID, PARTY_ID, or STATUS to limit the row set. Reporting layers should treat the NULL-valued columns as placeholders and avoid depending on them for lifecycle or balance logic in both 12.1.1 and 12.2.2.