Search Results resource_audit_id

Overview

JTF_RS_RESOURCE_EXTN_AUD is an audit shadow table in the JTF (CRM Foundation) schema that captures historical change data for Resources in Oracle E-Business Suite Release 12.1.1 and 12.2.2. Where JTF_RS_RESOURCE_EXTNS holds the current definition of a resource, this table stores the before-and-after image of each modification, enabling administrators and reporting tools to reconstruct what a resource record looked like prior to any given update. The table is owned by JTF and carries a VALID status in the ETRM repository.

The heuristic Data Vault classification mined from its foreign key structure is link. In Data Vault terms, this reflects the table's role in joining a resource to its historical state and to several Hub references — parties, party sites, contacts, security groups, and source organizations — through narrowly typed relationships rather than storing descriptive attributes as a standalone satellite. Practitioners modeling the JTF CRM Foundation subject area may treat the audit rows as an effectivity-dated bridge connecting a resource to the transactional context in which each change occurred.

Key Information Stored

The table is physically wide, with 134 documented columns in the 12.2.2 schema, almost all of them arranged in NEW_/OLD_ pairs that bracket each change. The most significant columns are:

• RESOURCE_AUDIT_ID — the surrogate primary key, enforced by JTF_RS_RESOURCE_EXTN_AUD_PK and by the unique index JTF_RS_RESOURCE_EXTN_AUD_U1. This is the only documented unique business-key candidate; it is a system-generated identifier, not a natural key.
• RESOURCE_ID — foreign key to JTF_RS_RESOURCE_EXTNS, identifying which resource the audit row describes.
• NEW_CATEGORY / OLD_CATEGORY — resource category before and after the change.
• NEW_RESOURCE_NUMBER / OLD_RESOURCE_NUMBER — the resource's business-facing number.
• NEW_RESOURCE_NAME / OLD_RESOURCE_NAME — the display name of the resource.
• NEW_USER_ID / OLD_USER_ID — the associated application user.
• NEW_SUPPORT_SITE_ID / OLD_SUPPORT_SITE_ID — foreign keys to HZ_PARTY_SITES, capturing support-site reassignment.
• NEW_CONTACT_ID / OLD_CONTACT_ID — foreign keys to HZ_ORG_CONTACTS, tracking contact reassignment.
• NEW_MANAGING_EMPLOYEE_ID / OLD_MANAGING_EMPLOYEE_ID — supervisory relationship changes.
• NEW_SOURCE_ID / OLD_SOURCE_ID, NEW_SOURCE_ORG_ID / OLD_SOURCE_ORG_ID, NEW_SOURCE_ORG_NAME / OLD_SOURCE_ORG_NAME — denormalized snapshot of the resource's source record and organization.
• NEW_START_DATE_ACTIVE / OLD_START_DATE_ACTIVE and NEW_END_DATE_ACTIVE / OLD_END_DATE_ACTIVE — date-range effectivity of the resource.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS governing multi-org/multi-tenant visibility of the audit row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns identifying who performed the audited operation and when.

Two of the documented unique indexes, SYS_IL0000084703C00066$$ and its siblings, are LOB (Large Object) indexes, indicating that certain address or source fields are stored as LOB segments.

Common Use Cases and Queries

The principal use case is auditing resource changes for compliance, dispute resolution, and data-quality reconciliation. A typical query reconstructs the full change history of a single resource:

• SELECT a.resource_audit_id, a.resource_id, a.old_resource_name, a.new_resource_name, a.old_support_site_id, a.new_support_site_id, a.last_updated_by, a.last_update_date FROM jtf.jtf_rs_resource_extn_aud a WHERE a.resource_id = :resource_id ORDER BY a.last_update_date;
• Detecting reassignments of support sites or contacts over a period by filtering on the NEW_/OLD_ pairs that differ.
• Reconstructing the state of a resource as of a past date by ordering audit rows by LAST_UPDATE_DATE and taking the NEW_ image of the latest row preceding the date of interest.
• Joining to JTF_RS_RESOURCE_EXTNS to compare the current record against its previous image and surface drift, for example where NEW_CATEGORY differs from the live CATEGORY value.
• Reporting by security group, using SECURITY_GROUP_ID to restrict output to the operating unit or business group in scope.

Related Objects

The audit table is tightly coupled to the following objects through the documented foreign keys:

• JTF_RS_RESOURCE_EXTNS — the base resource table; join on JTF_RS_RESOURCE_EXTN_AUD.RESOURCE_ID = JTF_RS_RESOURCE_EXTNS.RESOURCE_ID.
• HZ_PARTY_SITES — referenced twice, via OLD_SUPPORT_SITE_ID and NEW_SUPPORT_SITE_ID, for support-site history.
• HZ_ORG_CONTACTS — referenced twice, via OLD_CONTACT_ID and NEW_CONTACT_ID, for contact history.
• FND_SECURITY_GROUPS — via SECURITY_GROUP_ID, controlling row-level access.
• JTF_RS_RESOURCE_EXTN_AUD_PK and JTF_RS_RESOURCE_EXTN_AUD_U1 — the enforcing primary-key constraint and unique index on RESOURCE_AUDIT_ID.
• Related resource sub-entities such as JTF_RS_RESOURCE_EXTNS and the CRM Foundation resource APIs that perform the writes which populate this audit trail.