Search Results jtf_rs_resource_extn_aud




Overview
Explore in AI Chat

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:

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.