Search Results sys_il0000084703c00066

Overview

JTF.JTF_RS_RESOURCE_EXTN_AUD is the audit trail table for the Oracle E-Business Suite Resource Manager (JTF_RS) schema. It captures the before-and-after image of every insert, update, and delete performed against resource extension records, which are the descriptive attributes attached to a resource — an employee, partner, or other party acting as a service agent, salesperson, or team member. The table is registered under FND Design Data as JTF.JTF_RS_RESOURCE_EXTN_AUD and is reported VALID in both EBS 12.1.1 and 12.2.2. It resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and its 134 documented columns are stored in a paired NEW_/OLD_ pattern that makes it straightforward to reconstruct the exact state of a resource before and after a change.

Because each row records a single effective change to a resource, the table is best treated as a change-data capture structure rather than a current-state entity. Mined foreign-key relationships suggest a link classification in a Data Vault style model, since the object binds a resource identifier to surrounding party, contact, site, and security-group references at a point in time. That classification is a modeling heuristic; applications should treat the table as an append-only audit journal.

Key Information Stored

The surrogate primary key is RESOURCE_AUDIT_ID, which is also the sole column of the unique index JTF_RS_RESOURCE_EXTN_AUD_U1. Each row additionally carries RESOURCE_ID, the foreign key back to the live resource extension record, so audit history for any resource can be retrieved by filtering on that column. The most operationally significant columns are:

• RESOURCE_AUDIT_ID — surrogate identifier and primary key for the audit row.
• RESOURCE_ID — the resource whose attributes were changed; links to JTF_RS_RESOURCE_EXTNS.
• NEW_RESOURCE_NUMBER / OLD_RESOURCE_NUMBER — resource number before and after the transaction.
• NEW_CATEGORY / OLD_CATEGORY — resource category reassignment.
• NEW_RESOURCE_NAME / OLD_RESOURCE_NAME — display name change.
• NEW_USER_ID / OLD_USER_ID — the FND user associated with the resource.
• NEW_ADDRESS_ID / OLD_ADDRESS_ID and NEW_CONTACT_ID / OLD_CONTACT_ID — party address and contact changes, the latter referencing HZ_ORG_CONTACTS.
• NEW_SUPPORT_SITE_ID / OLD_SUPPORT_SITE_ID — support site (HZ_PARTY_SITES) reassignment.
• NEW_COST_PER_HR / OLD_COST_PER_HR and NEW_COMPENSATION_CURRENCY_CODE / OLD_COMPENSATION_CURRENCY_CODE — cost and currency updates.
• NEW_TIME_ZONE / OLD_TIME_ZONE, NEW_PRIMARY_LANGUAGE / OLD_PRIMARY_LANGUAGE — localization attributes.
• NEW_OBJECT_VERSION_NUMBER / OLD_OBJECT_VERSION_NUMBER — the Weblogic/OAF optimistic locking token at each stage.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — audit who-and-when columns.
• SECURITY_GROUP_ID — the operating unit/security context for the change.
• NEW_PARTY_ID / OLD_PARTY_ID and NEW_SOURCE_ORG_ID / OLD_SOURCE_ORG_ID — party and source organization references tying the resource to the Trading Community Model.

Several LOB segments are stored inline in APPS_TS_TX_DATA (SYS_IL0000084703C00066$$ and C00067$$, C00074$$ and C00075$$), reflecting the large descriptive address and party columns kept in the audit record. The remaining ~120 columns follow the same OLD/NEW convention for source, address, and organizational attributes.

Common Use Cases and Queries

The primary use case is point-in-time reconstruction of a resource record: identifying who changed a resource's cost, address, user, or category, and when. A typical query sequences the audit trail for one resource:

• Change history for a resource: SELECT resource_audit_id, creation_date, last_updated_by, new_resource_number, old_resource_number, new_category, old_category FROM jtf_rs_resource_extn_aud WHERE resource_id = :id ORDER BY creation_date DESC;
• Identify a specific changed field: filter on the presence of a delta, e.g. WHERE new_cost_per_hr <> old_cost_per_hr or WHERE new_category <> old_category.
• Audit by operator or date range: WHERE last_updated_by = :user AND creation_date BETWEEN :from AND :to.
• Security-scoped reporting: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to enforce data access in multi-org reporting extracts.
• Data warehouse load: feed resource master changes into a dimension-processed fact history using the NEW_ values and the audit timestamp; check NEW_OBJECT_VERSION_NUMBER to discard duplicate captures.

Because there is no natural business key beyond the surrogate ID, deduplication should key on RESOURCE_ID together with CREATION_DATE and the OLD_/NEW_ value pair.

Related Objects

The documented foreign keys tie this table to the core EBS resource and trading community model. Significant related objects and their join columns are:

• JTF.JTF_RS_RESOURCE_EXTNS — the live resource-extension master; join on RESOURCE_ID = JTF_RS_RESOURCE_EXTNS.RESOURCE_ID.
• HZ_PARTY_SITES — support site reference; join NEW_SUPPORT_SITE_ID or OLD_SUPPORT_SITE_ID to HZ_PARTY_SITES.PARTY_SITE_ID.
• HZ_ORG_CONTACTS — contact reference; join NEW_CONTACT_ID or OLD_CONTACT_ID to HZ_ORG_CONTACTS.ORG_CONTACT_ID.
• FND_SECURITY_GROUPS — security context; join SECURITY_GROUP_ID to FND_SECURITY_GROUPS.SECURITY_GROUP_ID.
• JTF.JTF_RS_RESOURCES and JTF_RS_TEAMS — resource team membership, useful when correlating resource changes with team assignments.
• JTF_RS_RESOURCE_EXTNS_TL — the translatable resource name/language table, used to resolve NEW_RESOURCE_NAME values.
• FND_USER — operator identity; join CREATED_BY or LAST_UPDATED_BY to USER_ID.

Application programming interfaces that drive changes captured here are the Resource Manager APIs (JTF_RS_RESOURCE_EXTNS_PUB and related PL/SQL packages); changes applied through those entry points are the principal source of rows in this audit table.