Results for “oks_instance_history”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OKS_INSTANCE_HISTORY table resides in the OKS schema, the database owner for the Service Contracts module in Oracle E-Business Suite. It functions as an audit and transaction history repository for service contract instances, recording discrete events and state changes applied to contract line instances over their lifecycle. Each row captures a single transaction against an instance, preserving a chronological trail that supports auditability, troubleshooting, and historical reporting independent of the current-state data held in the core instance tables.

Mined from its foreign key structure, the object is classified heuristically as a standalone table. Under a Data Vault modeling suggestion, this classification implies the table is not tightly coupled as a dependent satellite to a single parent within the mined relationship set; it should instead be treated as an independent historical record, keyed by its own surrogate identifier and optionally filtered by security group. This modeling note is advisory only and reflects the documented FK topology rather than a definitive design recommendation.

Key Information Stored

The table is documented in ETRM 12.2.2 with 14 columns. The most operationally significant columns are:

• ID — Surrogate primary key, enforced by OKS_INSTANCE_HISTORY_PK and also serving as the business-key candidate via unique index OKS_INSTANCE_HISTORY_U1. It uniquely identifies each history row.
• INSTANCE_ID — References the service contract instance to which the historical transaction pertains; the principal join key back to instance data.
• TRANSACTION_TYPE — Categorizes the nature of the recorded event or change applied to the instance.
• TRANSACTION_DATE — Timestamp of the transaction, providing the chronological ordering used in audit and trend reporting.
• REFERENCE_NUMBER — External or internal document reference associated with the transaction, useful for cross-referencing business documents.
• PARAMETERS — Free-form or structured payload capturing transaction-specific details.
• BATCH_ID — Groups related history rows generated by a common processing run, valuable for reconciliation.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org and data-access partitioning.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework to detect concurrent updates.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture who created and last modified each row.

Common Use Cases and Queries

Typical scenarios include reconstructing the transaction timeline for a contract instance, auditing who changed what and when, and reconciling batch-driven updates. A common query pattern retrieves the full history for a given instance in chronological order:

SELECT h.transaction_type, h.transaction_date, h.reference_number, h.parameters FROM oks.oks_instance_history h WHERE h.instance_id = :instance_id ORDER BY h.transaction_date DESC;

Reporting use cases often aggregate by transaction type or batch to detect processing anomalies, or join to FND_SECURITY_GROUPS to enforce organizational access. Point-in-time analysis leverages TRANSACTION_DATE ranges, while BATCH_ID grouping supports run-level reconciliation of automated processes.

Related Objects

• OKS_INSTANCE_HISTORY_PK / OKS_INSTANCE_HISTORY_U1 — Primary and unique indexes on ID.
• FND_SECURITY_GROUPS — Joined on SECURITY_GROUP_ID for data-access enforcement.
• Core OKS instance tables — Joined on INSTANCE_ID to correlate historical events with current instance state.
• OKS Service Contracts base tables and APIs — Source of the transactions recorded here through contract processing logic.
• FND audit/security concurrent programs — Consume history rows for batch and security-aware reporting.