Search Results cus_last_update_date

Overview

APPS.AMS_ACCT_INTEREST_V is a reporting view owned by the APPS schema and registered within the Oracle E-Business Suite under the AMS (Marketing) product family. It presents a denormalized, read-only projection that joins Oracle Marketing interest records to their associated customer, party, site, location, and operating unit attributes. Its purpose is to expose marketing interest tracking data in a form suitable for concurrent-program extracts, Discoverer or BI Publisher reporting, and inbound/outbound integration with external campaign and CRM systems.

The view has no FND Design Data table of its own; it is delivered as a view-only object identified by the design data key AMS.AMS_ACCT_INTEREST_V and is marked VALID in the ETRM 12.2.2 repository. Because it carries the ORG_ID column, it operates in a multi-org context and is filtered by the operating unit of the querying responsibility.

Underlying Base Objects

The documentation identifies AS_INTERESTS (or its synonym AS_INTERESTS_ALL) as the driving base object, with additional joins to:

• HZ_CUST_ACCOUNTS, HZ_PARTIES — customer name, number, and party-level attributes
• HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES, HZ_LOCATIONS — address and site information
• AS_INTEREST_TYPES_VL, AS_INTEREST_CODES_VL — interest type and code decode values
• HR_OPERATING_UNITS — operating unit name
• FND_TERRITORIES_VL — country name resolution
• FND_USER_AP_PKG — resolution of last-updated user names

The view therefore flattens the hierarchical HZ (Trading Community Architecture) customer model against the AS interest subsystem, which is why several columns carry name prefixes (SIT_ for site, CUS_ for customer) to disambiguate otherwise overlapping audit fields.

Key Columns

• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER — the customer account identity and display name.
• ADDRESS_ID, KEY_SITE, SITE_ACTIVE — the customer site key. KEY_SITE identifies the primary or "key" site of the customer account, and is the column most frequently referenced when consumers need to constrain the view to a single representative site per account.
• CITY, STATE, POSTAL_CODE, COUNTY, COUNTRY, COUNTRY_NAME — location attributes derived from HZ_LOCATIONS.
• ORG_ID, ORG_NAME — operating unit context for multi-org security.
• SITE_INTEREST_TYPE, SITE_PRIMARY_INT_CODE, SITE_SECONDARY_INT_CODE, SITE_PRIMARY_CODE, SITE_SECONDARY_CODE — the decoded interest classification for the site.
• CUSTOMER_ACTIVE, CUSTOMER_PROSPECT_CODE, ANNUAL_REVENUE, NUM_OF_EMPLOYEES, OK_TO_MAIL — customer-level marketing attributes.
• SIT_LAST_UPDATE_DATE / CUS_LAST_UPDATE_DATE and their corresponding *_UPDATED_NAME columns — audit trail resolved through FND_USER_AP_PKG.

Common Use Cases and Queries

The most common usage is to extract the interest profile of key sites for a marketing campaign list. Restricting to the key site prevents duplicate rows when a customer has multiple addresses:

SELECT CUSTOMER_ID, CUSTOMER_NAME, KEY_SITE, CITY, STATE,
       SITE_INTEREST_TYPE, SITE_PRIMARY_INT_CODE
FROM   APPS.AMS_ACCT_INTEREST_V
WHERE  ORG_ID = :p_org_id
AND    KEY_SITE = 'Y'
AND    SITE_ACTIVE = 'Y';

A second pattern joins the view back to AS_INTERESTS_ALL when write-back or detail-level interest history is required, since the view is not updatable. A third pattern uses the location columns to regionalize campaign targeting:

SELECT COUNTRY_NAME, SITE_PRIMARY_INT_CODE, COUNT(*)
FROM   APPS.AMS_ACCT_INTEREST_V
WHERE  KEY_SITE = 'Y'
GROUP  BY COUNTRY_NAME, SITE_PRIMARY_INT_CODE;

Because KEY_SITE and SITE_ACTIVE are stored as single-character flags, filter literals should match the delivered Y/N convention. All queries should include an ORG_ID predicate to respect operating unit security and avoid cross-org leakage in multi-org deployments.