Search Results site_primary_code

Overview

AMS_ACCT_INTEREST_V is a user-facing view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its documented purpose is to serve Discoverer Business Area "List generation" folders, presenting a consolidated, denormalized picture of customers and customer sites together with their interest classifications. In practical terms, the view flattens the TCA (Trading Community Architecture) party model and joins it to the Oracle Marketing interest model so that list generation, segmentation, and ad-hoc reporting can be performed without navigating the underlying normalized tables manually.

The view has been catalogued as VALID in both EBS 12.1.1 and 12.2.2. Because it is a view rather than a table, it holds no data of its own; its cost profile is governed entirely by the base objects over which it is defined, and its behaviour is consistent across the two releases given that the same base synonyms and views remain present.

Underlying Base Objects

The documented base objects underlying AMS_ACCT_INTEREST_V are:

• AS_INTERESTS_ALL (SYNONYM) – the assignment of interest codes to entities such as parties, accounts, and sites.
• AS_INTEREST_CODES_VL (VIEW) – the primary and secondary interest code definitions.
• AS_INTEREST_TYPES_VL (VIEW) – the interest type definitions.
• HZ_PARTIES (SYNONYM) – the party record, supplying PARTY_ID, PARTY_NAME, and PARTY_NUMBER.
• HZ_CUST_ACCOUNTS (SYNONYM) – the customer account, supplying revenue, employee count, category, and status attributes.
• HZ_CUST_ACCT_SITES_ALL (SYNONYM) – the account site usage records, supplying ADDRESS_ID, KEY_ACCOUNT_FLAG, and SITE status.
• HZ_PARTY_SITES (SYNONYM) and HZ_LOCATIONS (SYNONYM) – the address hierarchy providing city, state, postal code, county, and country.
• FND_TERRITORIES_VL (VIEW) – territory short name used as COUNTRY_NAME.
• FND_USER_AP_PKG (PACKAGE) – a package function invoked to resolve the last-updated-by user ID into a display name.
• HR_OPERATING_UNITS (VIEW) – the operating unit holding the site, exposed as ORG_NAME.

The view therefore joins the TCA customer/party model to the Oracle Marketing (AS) interest model, producing one row per customer site and its associated interest classification.

Key Columns

• NUM_OF_EMPLOYEES – mapped from CUS.EMPLOYEES_TOTAL on HZ_CUST_ACCOUNTS; this is the column most frequently sought in list-generation reporting and the term the user searched for.
• CUSTOMER_ID / CUSTOMER_NAME / CUSTOMER_NUMBER – party identifiers from HZ_PARTIES.
• ANNUAL_REVENUE – the current fiscal-year potential revenue on the customer account.
• CUSTOMER_CATEGORY_CODE, CUSTOMER_PROSPECT_CODE, CUSTOMER_ACTIVE – derived attributes: prospect vs. customer is inferred from TOTAL_NUM_OF_ORDERS; active is derived from STATUS.
• REFERENCE, COMPETITOR, PARTNER, OK_TO_MAIL – flag columns normalized to 'Y'/'N' via NVL and DECODE.
• ADDRESS_ID, KEY_SITE, SITE_ACTIVE – site-level identity and status.
• CITY, STATE, POSTAL_CODE, COUNTY, COUNTRY, COUNTRY_NAME – geographic attributes from HZ_LOCATIONS and FND_TERRITORIES_VL.
• ORG_ID, ORG_NAME – operating unit context for the site.
• SITE_INTEREST_TYPE, SITE_PRIMARY_INT_CODE, SITE_SECONDARY_INT_CODE, SITE_PRIMARY_CODE, SITE_SECONDARY_CODE – the interest classification, concatenated from interest type and code values.
• SIT_LAST_UPDATED_*, CUS_LAST_UPDATED_* – audit columns plus resolved user names via FND_USER_AP_PKG.

Common Use Cases and Queries

The primary use case is list generation and customer segmentation in Oracle Marketing, particularly through Discoverer folders that consume the view directly. Typical queries retrieve customers filtered by employee count, revenue band, geography, or interest classification.

Example: list active customers in a given country with more than 500 employees.

SELECT customer_name,
       customer_number,
       num_of_employees,
       annual_revenue,
       city,
       country_name
FROM   ams_acct_interest_v
WHERE  customer_active = 'ACTIVE'
AND    num_of_employees > 500
AND    country_name = 'UNITED STATES';

Example: retrieve all sites for a specific primary interest code, with the operating unit context.

SELECT customer_id,
       customer_name,
       address_id,
       org_name,
       site_primary_code,
       site_interest_type
FROM   ams_acct_interest_v
WHERE  site_primary_code = 'TECHNOLOGY'
AND    site_active = 'ACTIVE';

Example: identify mail-eligible prospects with a key account site.

SELECT customer_name,
       address_id,
       ok_to_mail,
       key_site
FROM   ams_acct_interest_v
WHERE  customer_prospect_code = 'PROSPECT'
AND    ok_to_mail = 'Y'
AND    key_site = 'Y';

Because the view is defined over the TCA and interest base objects, queries benefit from standard indexes on HZ_PARTIES, HZ_CUST_ACCOUNTS, and HZ_CUST_ACCT_SITES_ALL. Row counts multiply by site and interest assignment, so aggregations should be applied with care when NUM_OF_EMPLOYEES is used at the customer level. The view remains valid and available in both EBS 12.1.1 and 12.2.2, making it a stable foundation for cross-release reporting.