Search Results cs_sr_contacts_ext_audit




Overview
Explore in AI Chat

Overview

CS_SR_CONTACTS_EXT_AUDIT is an audit (history) table owned by the CS schema within the Oracle E-Business Suite Service (Customer Service) module. Its purpose, as documented in the ETRM 12.2.2 metadata, is to track all changes made to the party role contacts extensible attributes. Whenever an extensible attribute on a service request contact record is inserted, updated, or deleted, this table preserves the prior and current values, allowing full historical reconstruction of attribute state over time.

The table is populated and maintained internally by the Service application rather than being a transactional entry point for users. It is therefore a read-mostly object from a development perspective, useful for auditing, diagnostics, reconciliation, and data lineage reporting. The ETRM documentation confirms the table is VALID and lists a physical schema of 266 columns, reflecting the very wide extensible attribute model (character, numeric, date, and unit-of-measure attributes) plus audit control columns.

Under the heuristic Data Vault classification mined from the foreign key structure, this object is best modeled as a link. This is a modeling suggestion: the table connects an audit record to its originating incident and extension context, with the audit extension identifier acting as the driving key. In dimensional terms it behaves like a change-tracking satellite attached to the party role contact extension, retaining the OLD_ prefixed versions of each attribute alongside the current values.

Key Information Stored

The table combines three logical groups of columns: audit identity and control fields, the extensible attribute pairs (current and prior), and contact/party context fields. The most significant columns are:

  • AUDIT_EXTENSION_ID — the surrogate primary key, enforced by CS_SR_CONTACTS_EXT_AUDIT_PK and also covered by the unique index CS_SR_CONTACTS_EXT_AUDIT_U1. This is the business-key candidate for uniquely identifying each audit row.
  • EXTENSION_ID — the identifier of the extension record whose changes are being audited; documented as a foreign key column.
  • INCIDENT_ID — the service request (incident) associated with the contact extension; a foreign key to CS_INCIDENTS_ALL_B.
  • CONTEXT and ATTR_GROUP_ID — identify the attribute context and the attribute group to which the extensible attributes belong.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit/WHO columns recording when and by whom the audit row itself was created or updated.
  • C_EXT_ATTRn / OLD_C_EXT_ATTRn — paired character extensible attribute values (current and previous) across up to 50 slots.
  • N_EXT_ATTRn / OLD_N_EXT_ATTRn — paired numeric extensible attribute values across up to 25 slots.
  • D_EXT_ATTRn / OLD_D_EXT_ATTRn — paired date extensible attribute values across up to 25 slots.
  • UOM_EXT_ATTRn / OLD_UOM_EXT_ATTRn — units of measure associated with the numeric attributes across up to 25 slots.
  • PARTY_ID / OLD_PARTY_ID — the party (contact) to which the extension belongs, with its prior value.
  • CONTACT_TYPE and PARTY_ROLE_CODE — classify the contact and its role at the time of the change.
  • EXT_ATTR_MODIFIED_ON and EXT_ATTR_MODIFIED_BY — the timestamp and user responsible for the attribute modification being recorded.

Common Use Cases and Queries

Typical scenarios include auditing who changed a service request contact attribute and when, reconstructing the prior state of an attribute after a data dispute, and feeding change-history extracts into reporting or compliance dashboards.

To list the change history for a given incident, joining to CS_INCIDENTS_ALL_B on INCIDENT_ID:

  • SELECT a.AUDIT_EXTENSION_ID, a.INCIDENT_ID, a.CONTACT_TYPE, a.PARTY_ROLE_CODE, a.EXT_ATTR_MODIFIED_ON, a.EXT_ATTR_MODIFIED_BY FROM CS.CS_SR_CONTACTS_EXT_AUDIT a WHERE a.INCIDENT_ID = :incident_id ORDER BY a.EXT_ATTR_MODIFIED_ON DESC;

To compare a current and prior character attribute for a specific extension:

  • SELECT e.EXTENSION_ID, e.C_EXT_ATTR1 AS current_value, e.OLD_C_EXT_ATTR1 AS prior_value FROM CS.CS_SR_CONTACTS_EXT_AUDIT e WHERE e.EXTENSION_ID = :extension_id AND e.C_EXT_ATTR1 <> e.OLD_C_EXT_ATTR1;

To identify all contacts modified by a particular user within a date range, filter on EXT_ATTR_MODIFIED_BY and EXT_ATTR_MODIFIED_ON. Reporting extracts frequently de-duplicate the OLD_ and current pairs to produce a net-change timeline, and DBAs may use the table for reconciliation when a party role contact extension appears inconsistent with its source record.

Related Objects

The following objects are the most significant dependencies and reference points documented for this table:

  • CS_INCIDENTS_ALL_B — the base service request (incident) table, joined via CS_SR_CONTACTS_EXT_AUDIT.INCIDENT_ID = CS_INCIDENTS_ALL_B.INCIDENT_ID.
  • CS_SR_CONTACTS_EXT — the operational extension table whose attribute changes this audit table records, joined through EXTENSION_ID.
  • AUDIT_EXTENSION_ID foreign key relationship — documented as referencing the extension identifier, linking each audit row to its extension context.
  • CS_SR_CONTACTS_EXT_AUDIT_PK / CS_SR_CONTACTS_EXT_AUDIT_U1 — the primary key constraint and unique index on AUDIT_EXTENSION_ID that enforce row uniqueness.
  • Party and contact role entities — referenced indirectly through PARTY_ID, PARTY_ROLE_CODE, and CONTACT_TYPE, which tie the audit record to the contact being changed.
  • CS Service module setup and extensibility metadata — the attribute context and group definitions (CONTEXT, ATTR_GROUP_ID) that determine which extensible attributes are applicable.