Search Results oki_exp_not_renewed




Overview
Explore in AI Chat

Overview

OKI_EXP_NOT_RENEWED is a table owned by the OKI schema, the database account behind the Oracle Contracts Intelligence module in Oracle E-Business Suite. As its name and documented description indicate, the table stores summarized information about contract lines that have expired without being renewed. It functions as an analytical or reporting summary object rather than a transactional entity: each row represents a rolled-up view of expired, non-renewed contract activity for a given combination of period, operating unit, customer, and contract classification. This makes it particularly useful for renewal-risk analysis, churn reporting, and sales performance measurement in the Contracts Intelligence dashboards and extracts.

From a Data Vault modeling perspective, the mined FK structure classifies this table as standalone — a heuristic suggestion. In practice this means the table can be treated as a self-contained satellite-style summary, with no parent hubs or links declared in the documented relationships other than its reference to FND_SECURITY_GROUPS. Analysts working with ETRM or a downstream warehouse can therefore treat OKI_EXP_NOT_RENEWED as a conformed summary source keyed against the standard Oracle multi-org and security model.

Key Information Stored

The table contains 18 documented columns. The surrogate primary key is ID, enforced through the OKI_EXP_NOT_RENEWED_PK constraint and reinforced by the unique index OKI_EXP_NOT_RENEWED_U1. A second unique index, OKI_EXP_NOT_RENEWED_U2, defines the composite business key candidate: PERIOD_NAME, PERIOD_SET_NAME, AUTHORING_ORG_ID, CUSTOMER_PARTY_ID, SCS_CODE, and CONTACT_ID. This composite key indicates that a summary row is uniquely identified by a period, the accounting period set, the authoring (operating unit) organization, the customer party, a service/contract classification code (SCS_CODE), and the contact.

  • PERIOD_NAME, PERIOD_SET_NAME, PERIOD_TYPE — the accounting period and calendar context for the summary.
  • AUTHORING_ORG_ID, AUTHORING_ORG_NAME — the operating unit that authored the contract lines, with denormalized name.
  • CUSTOMER_PARTY_ID, CUSTOMER_NAME — the customer party identifier and its descriptive name.
  • CONTACT_ID, SALESREP_NAME — the contact associated with the contract and the owning sales representative.
  • SCS_CODE — the service/contract classification used to segment expired lines.
  • CONTRACT_COUNT — the number of expired, non-renewed contract lines in the grouping.
  • BASE_LOST_AMOUNT — the monetary value of the base amount lost to non-renewal, the primary churn metric.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the process that populated the row.
  • SECURITY_GROUP_ID — the security group used for multi-org access control, referencing FND_SECURITY_GROUPS.

Common Use Cases and Queries

The dominant use case is renewal/churn reporting: quantifying expired contract value by customer, operating unit, sales representative, or period. A typical query aggregates lost base amount for a given period.

SELECT customer_name, authoring_org_name, SUM(base_lost_amount) lost, SUM(contract_count) lines FROM oki.oki_exp_not_renewed WHERE period_name = :p_period AND period_set_name = :p_set GROUP BY customer_name, authoring_org_name ORDER BY lost DESC;

A second pattern ranks sales representatives by renewal loss, useful for incentive and coaching analysis. A third joins the summary back to FND_SECURITY_GROUPS through SECURITY_GROUP_ID to enforce row-level access for multi-org deployments. Because the table is pre-summarized, it is well suited to BI Publisher reports and OBIEE/OTBI extracts, avoiding the cost of aggregating the underlying contract line detail. Filtering on PROGRAM_UPDATE_DATE allows incremental extracts of only the latest run's rows.

Related Objects

The documented FK relationship ties SECURITY_GROUP_ID to FND_SECURITY_GROUPS, so that table is the primary dependent reference for row-level security joins. The composite business key columns resolve to standard EBS master data: AUTHORING_ORG_ID against HR_OPERATING_UNITS, CUSTOMER_PARTY_ID and CONTACT_ID against HZ_PARTIES and HZ_CUST_ACCOUNTS/HZ_ORG_CONTACTS, and PERIOD_NAME against the GL period tables (GL_PERIODS). The row is populated by a concurrent program, so FND_CONCURRENT_REQUESTS is related via REQUEST_ID and CONCURRENT_PROGRAM_ID. Operational detail feeding these summaries typically resides in the OKI contract-line and Contracts Intelligence staging tables (for example OKC contract tables and OKI summary objects), which serve as the source lineage for this standalone summary table. For 12.1.1 through 12.2.2 the schema is consistent; the OKI module remains optional and must be licensed, so validate availability in the target environment before relying on the table in reports.