Search Results oki_exp_not_renewed_u2

Overview

OKI.OKI_EXP_NOT_RENEWED is a summarization table within the Oracle E-Business Suite Contracts (OKI) schema. It stores aggregated information about the value and count of contract lines that reached expiration without being renewed, organized along multiple analytical dimensions including accounting period, operating unit, customer, contract category, and contact. Rather than holding transactional contract data, the table serves as a pre-aggregated reporting store, allowing programmatic and inquiry-based reporting on renewal loss without requiring real-time aggregation across the underlying contract line tables.

The object is owned by the OKI schema, resides in the APPS_TS_TX_DATA tablespace, and carries a status of VALID. Its FND Design Data reference is OKI.OKI_EXP_NOT_RENEWED. The table is populated through concurrent program processing, evidenced by the presence of standard Concurrent Manager audit columns. Under a heuristic Data Vault classification, this table resembles a satellite structure: it captures descriptive and measured attributes (counts, amounts, names, codes) that describe a business event — contract expiration without renewal — keyed by a set of dimensional business identifiers rather than by a purely surrogate identity.

Key Information Stored

The table contains eighteen documented columns. The most significant for analysis and integration are:

• ID — The surrogate primary key, a NUMBER that uniquely identifies each summarized row. It is enforced by the unique index OKI_EXP_NOT_RENEWED_U1 and by the primary key constraint OKI_EXP_NOT_RENEWED_PK.
• PERIOD_NAME — The General Ledger period name associated with the summarized data.
• PERIOD_SET_NAME — The GL period set used to determine valid periods.
• PERIOD_TYPE — The GL period type (for example, month, quarter, or year) governing the period definition.
• AUTHORING_ORG_ID — The internal identifier of the operating unit in which the contract was authored.
• AUTHORING_ORG_NAME — The denormalized operating unit name, supporting direct reporting.
• CUSTOMER_PARTY_ID — The party identifier of the contract customer.
• CUSTOMER_NAME — The customer name, stored for reporting convenience.
• CONTACT_ID — The identifier for the contract contact.
• SCS_CODE — The category (sub-class) code for the contract.
• CONTRACT_COUNT — The number of contracts that expired and were not renewed for the given dimensional combination.
• BASE_LOST_AMOUNT — The monetary value lost, expressed in the base currency, for the non-renewed contracts.
• SALESREP_NAME — The name of the associated sales representative.

The unique index OKI_EXP_NOT_RENEWED_U2 covers the composite business key of PERIOD_NAME, PERIOD_SET_NAME, AUTHORING_ORG_ID, CUSTOMER_PARTY_ID, SCS_CODE, and CONTACT_ID. This composite uniquely defines the grain of the summary — one row per period, period set, operating unit, customer, contract category, and contact combination. The remaining columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are Concurrent Manager audit columns identifying the program run that generated the row.

Common Use Cases and Queries

Typical usage centers on renewal-loss analysis: identifying customers, operating units, contract categories, or sales representatives with high non-renewal counts or lost amounts during specific GL periods. Because the table is pre-aggregated, queries are efficient for dashboards and period-over-period trend reporting.

A representative query aggregates lost value by operating unit for a period:

SELECT authoring_org_name,
       SUM(contract_count)     AS contracts_lost,
       SUM(base_lost_amount)   AS amount_lost
FROM   oki.okl_exp_not_renewed
WHERE  period_name = :p_period
GROUP  BY authoring_org_name
ORDER  BY amount_lost DESC;

Another common pattern surfaces customers with the greatest lost value across a period set, joining to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to enforce operating-unit security. The table is also useful for comparing category (SCS_CODE) performance and for drilling from summary metrics into detail contract data using the dimensional identifiers.

Related Objects

The following objects are the most significant related entities for this table:

• FND_SECURITY_GROUPS — Referenced through the documented foreign key OKI_EXP_NOT_RENEWED.SECURITY_GROUP_ID → FND_SECURITY_GROUPS, used to apply operating-unit security to queries.
• OKI_EXP_NOT_RENEWED_PK — The primary key constraint on ID.
• OKI_EXP_NOT_RENEWED_U1 — The unique index on ID.
• OKI_EXP_NOT_RENEWED_U2 — The composite unique index defining the summary grain.
• OKC_CONTRACTS — Source of contract definitions used as the basis for renewal-loss population and drill-down.
• OKC_CONTRACT_LINES — Source of contract line details that drive the count and value of non-renewed lines.
• HZ_PARTIES — The party master supplying CUSTOMER_PARTY_ID interpretation and customer-level joins.
• GL_PERIODS — The General Ledger period reference corresponding to PERIOD_NAME, PERIOD_SET_NAME, and PERIOD_TYPE.

These relationships support both security enforcement and drill-down reporting from the summary layer into the underlying Contracts transaction schema.