Search Results okc_rep_con_approvals_u1

Overview

OKC.OKC_REP_CON_APPROVALS is a transactional table in the Oracle Contracts (OKC) schema that stores a contract's approval history. Each row captures a single approver response to an approval notification raised against a contract version. It answers the operational question of who acted on a contract approval, when, and with what disposition — 'Approve', 'Reject', or 'Reassign' — together with the approver's free-text comments. In Oracle EBS 12.1.1 and 12.2.2, the underlying table structure is shared; 12.2.2 Online Patching is served by the presence of OBJECT_VERSION_NUMBER, which enables the row-version check that Editioning and AD_ZD-based patching rely on.

From a Data Vault modeling perspective, the mined relationship structure classifies this object as satellite-leaning. It is not a pure link because its grain is a contract version plus an approver action event, and it does not introduce new hubs of its own. It functions as a descriptive, historically-appended satellite hanging off the contract hub, recording the outcome of the contract approval workflow. The volatile part of the record — the response, the date, and the notes — is separated from the stable contract identity, which is a hallmark satellite pattern.

The table resides in the APPS_TS_TX_DATA tablespace with PCT_FREE of 10, while its indexes are held in APPS_TS_TX_IDX, reflecting standard Oracle EBS separation of transaction data from index segments.

Key Information Stored

CONTRACT_ID is the contract identifier and is the leading column of both indexes and the foreign key to OKC_REP_CONTRACTS_ALL. CONTRACT_VERSION_NUM records the specific version of the contract against which the approval was performed, allowing multiple approval cycles across successive versions to coexist.

The four columns that form the unique business key — enforced by OKC_REP_CON_APPROVALS_U1 (CONTRACT_ID, USER_ID, ACTION_CODE, ACTION_DATE) — are the composite fingerprint of a single approval event:

• USER_ID — the user who responded to the approval notification.
• ACTION_CODE — the approver's response; values include 'Approve', 'Reject', and 'Reassign'.
• ACTION_DATE — the timestamp when the approver responded.

NOTES stores up to 2000 characters of approver comments submitted with the response, and is the primary free-text audit trail of the approval. OBJECT_VERSION_NUMBER is initialized to 1 on insert and incremented on every update, giving APIs a concurrency-check token. The standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — provide creation and maintenance audit information. The secondary index OKC_REP_CON_APPROVALS_N1 (CONTRACT_ID, CONTRACT_VERSION_NUM) supports version-scoped retrieval and is the natural access path for reconstructing the approval sequence of a particular contract version.

Common Use Cases and Queries

The dominant use case is contract approval history reporting. A typical query retrieves the ordered chain of actions for a specific contract:

• SELECT user_id, action_code, action_date, notes FROM okc.okc_rep_con_approvals WHERE contract_id = :contract_id AND contract_version_num = :version ORDER BY action_date;
• Counting rejections per contract to identify bottlenecks: SELECT contract_id, COUNT(*) FROM okc.okc_rep_con_approvals WHERE action_code = 'Reject' GROUP BY contract_id;
• Audit and accountability reports joining USER_ID to PER_ALL_PEOPLE_F or FND_USER to resolve the approver's name.
• Cycle-time analysis comparing CREATION_DATE to ACTION_DATE to measure approval latency.
• Extracting NOTES for approval comment consolidation in contract repository or clause deviation reporting.
• Reconciliation of the contract approval workflow by cross-referencing rows against WF_NOTIFICATIONS and the contract's current approval status.

Because the unique key includes ACTION_DATE, rapid successive actions by the same user are tolerated as distinct events, though duplicates within the same timestamp are constrained. Reporting should filter by contract version when historical cycles are relevant.

Related Objects

The table sits within the OKC contract data model. The principal relationships are:

• OKC_OKC_REP_CONTRACTS_ALL — the parent contract entity, joined on CONTRACT_ID; this is the documented foreign key.
• OKC_REP_CONTRACTS_ALL (contract header) — provides contract identity and status context for approval history.
• OKC_REP_CONTRACT_LINES_ALL — contract line detail, useful when approval scope must be traced to line-level changes.
• OKC_K_HEADERS_ALL / OKC_K_HEADERS_B — contract header tables that support the same approval workflow across the broader contracts model.
• FND_USER and PER_ALL_PEOPLE_F — approver identity resolution via USER_ID.
• WF_NOTIFICATIONS — Oracle Workflow notification records that raise the approval action this table records.
• OKC_REP_CON_APPROVALS appears as a database object referenced by the APPS schema synonym OKC_REP_CON_APPROVALS, confirming it is exposed to application code.

No documented database object references OKC_REP_CON_APPROVALS other than the APPS synonym, indicating it is primarily a consumer of contract identity rather than a parent to further child tables.