1. Sales Questions and Answers
  2. Which tables have audit data for accounts?

Which tables have audit data for accounts?

The audit table that stores information about sales accounts is ZCA_SALES_ACCOUNTS.

You can also use the HZ_ORGANIZATION_PROFILES table, which stores the audit information specific to an organization.

The audit table has all the columns that the base tables have. For example, the columns available in the base tables, ZCA_SALES_ACCOUNTS and HZ_ORGANIZATION_PROFILES, are available in ZCA_SALES_ACCOUNTS and HZ_ORGANIZATION_PROFILES, respectively.

Get Audit Logs for Sales Accounts Using SQL

Use the SQL queries in this section to query the audit information.

Basic Audit Log Query

This query lists recent audit entries for sales accounts, showing key details. Details are for all sales account changes in the past 7 days and include:

  • Account identifier and name
  • Action type (insert/update/delete)
  • Who performed the action
  • Time stamp of the action
SELECT SALES_ACCOUNT_ID, SALES_ACCOUNT_NAME, 
AUDIT_ACTION_TYPE_, LAST_UPDATED_BY, LAST_UPDATE_DATE
FROM FUSION.ZCA_SALES_ACCOUNTS_
WHERE LAST_UPDATE_DATE >= SYSDATE - 7
ORDER BY LAST_UPDATE_DATE DESC;

You can adjust the time frame in the WHERE clause as needed.

Filter by Action or User

This query filters for audit records of type UPDATE made by a specific user in the given date range. For example, to get only updates made by a specific user within a date range, use this query:

SELECT SALES_ACCOUNT_ID, SALES_ACCOUNT_NAME, 
LAST_UPDATED_BY, LAST_UPDATE_DATE, AUDIT_ACTION_TYPE_
FROM FUSION.ZCA_SALES_ACCOUNTS_
WHERE AUDIT_ACTION_TYPE_ = 'UPDATE'
AND LAST_UPDATED_BY = '<username>' 
AND LAST_UPDATE_DATE BETWEEN :start_date AND :end_date
ORDER BY LAST_UPDATE_DATE;

Replace <username> with the actual user identifier (the values in LAST_UPDATED_BY are typically the user's sign-in credentials or Oracle user ID).

Retrieve Details of the Changes

The audit table contains the new state of each record. To see what changed, you can compare an audit record to the previous one for the same SALES_ACCOUNT_ID. For example, you might self-join the table on SALES_ACCOUNT_ID and use LAST_UPDATE_DATE to find the prior entry, or use the AUDIT_CHANGE_BIT_MAP to identify changed columns.

A simplified approach is to select the two most recent versions of a specific account and manually compare the field values. This query returns the latest two audit records for the given sales account, which you can examine to see differences in column values (the older record versus the newer record). The AUDIT_CHANGE_BIT_MAP column will contain a string of ones and zeros, indicating which columns changed in the latest update. Each position in the bitmap corresponds to a column in the base table schema.

SELECT *
FROM FUSION.ZCA_SALES_ACCOUNTS_
WHERE SALES_ACCOUNT_ID = :account_id
ORDER BY LAST_UPDATE_DATE DESC
FETCH FIRST 2 ROWS ONLY;

Always ensure that auditing is enabled for the Sales Account object (using Manage Audit Policies in Setup and Maintenance) before expecting data in these tables. If auditing hasn't been enabled, then the audit tables won't contain entries for that object.

See What fields can I audit for Sales? for more information.

For more information about the ZCA_SALES_ACCOUNTS table, see the Tables and Views for Sales, Fusion Service, and Field Service guide on Oracle Help Center.