Search Results oki_exp_not_renewed_v

Overview

The OKI_EXP_NOT_RENEWED_V view is a reporting object within the Oracle E-Business Suite Contracts Intelligence (OKI) module. It exposes summarized information about contract lines that have expired without being renewed, providing a consolidated picture of lost recurring revenue and the customers, sales representatives, and authoring organizations associated with that attrition. In ETRM 12.1.1 and 12.2.2, the object is registered in the APPS schema with a status of VALID, making it available to reporting tools, concurrent programs, and custom integrations that operate against the APPS database user.

Because Contracts Intelligence is oriented toward analytical visibility into contract portfolios, this view serves as a pre-aggregated presentation layer rather than a transactional entity. It is intended to be queried directly for ad hoc analysis, embedded in Oracle Business Intelligence or Discoverer reports, or used as a source for downstream extracts. Its period-oriented structure makes it suitable for trend reporting on non-renewal activity across accounting or reporting periods.

Underlying Base Objects

The view is defined over a single base object, the table OKI_EXP_NOT_RENEWED, aliased as ENR in the view text. The view performs no joins, unions, or transformations; it is a straightforward projection of every column from the base table. Consequently, the row population, summarization, and refresh of the underlying data are the responsibility of the OKI concurrent program that populates OKI_EXP_NOT_RENEWED, not of the view itself.

From a dependency perspective, the view inherits the security and data scope of the base table. Any query against OKI_EXP_NOT_RENEWED_V returns exactly the rows present in OKI_EXP_NOT_RENEWED at the time of execution. The presence of REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE columns confirms that the base table is loaded by a standard concurrent program, and these audit columns allow users to trace the provenance and currency of the summarized data.

Key Columns

• ID — Surrogate identifier for each summarized row in the base table.
• PERIOD_NAME, PERIOD_SET_NAME, PERIOD_TYPE — Identify the accounting or reporting period to which the non-renewal summary applies, including the period set and type for calendar interpretation.
• AUTHORING_ORG_ID, AUTHORING_ORG_NAME — The operating unit or organization that authored the expired contract lines, enabling organizational rollups.
• CUSTOMER_PARTY_ID, CUSTOMER_NAME, CONTACT_ID — The customer and contact associated with the expired contracts, supporting customer-level attrition analysis.
• SALESREP_NAME — The sales representative credited with the lapsed business.
• SCS_CODE — The service or contract classification code used to categorize the expired contract lines.
• CONTRACT_COUNT — The number of contract lines that expired without renewal within the summarized grouping.
• BASE_LOST_AMOUNT — The monetary value of the base revenue lost as a result of non-renewal.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit columns indicating the process that generated the row and when it was last updated.

Common Use Cases and Queries

Typical usage centers on renewal-attrition analysis. Analysts filter by period to compare lost amounts across time, group by authoring organization to identify units with the highest non-renewal rates, or group by sales representative to assess retention performance. Because rows are already summarized, querying the view is efficient for dashboards and scheduled reports.

Sample query for periodic lost revenue by organization:

• SELECT period_name, authoring_org_name, SUM(contract_count) AS expired_contracts, SUM(base_lost_amount) AS lost_amount FROM oki_exp_not_renewed_v WHERE period_name = :p_period GROUP BY period_name, authoring_org_name ORDER BY lost_amount DESC;

To identify customers with the largest non-renewal exposure in a given period:

• SELECT customer_name, salesrep_name, SUM(contract_count) AS contracts, SUM(base_lost_amount) AS lost_amount FROM oki_exp_not_renewed_v WHERE period_name = :p_period GROUP BY customer_name, salesrep_name HAVING SUM(base_lost_amount) > 0 ORDER BY lost_amount DESC;

For data freshness checks, querying MAX(program_update_date) and the associated request_id confirms when the summarization concurrent program last ran. Standard EBS security applies: responsibility-level organization access may restrict visible rows when the view is exposed through a secured Oracle Reports or OBIEE layer.