Search Results oki_expired_lines_pk

Overview

OKI_EXPIRED_LINES is a Contracts Intelligence (OKI) transactional table in Oracle E-Business Suite that stores information about contract lines that have expired without renewal. It belongs to the OKI product family, which is documented as obsolete in the ETRM reference and is not implemented in the referenced database. The table is owned by the OKI schema and, in the documented 12.1.1 physical schema, contains 53 columns with a single primary key, OKI_EXPIRED_LINES_PK, defined on the column CLE_ID.

The Data Vault classification derived heuristically from the FK structure is standalone. In Data Vault modeling terms, this suggests the table behaves as an independent hub-like entity keyed by CLE_ID, with no downstream dependent links or satellites documented within the OKI schema itself. The single foreign key, OKI_EXPIRED_LINES.SECURITY_GROUP_ID → FND_SECURITY_GROUPS, is a security/access reference rather than a functional parent-child relationship, which supports the standalone classification.

Key Information Stored

The surrogate primary key is CLE_ID, which is also the sole unique business-key candidate via the unique index OKI_EXPIRED_LINES_U1. Beyond the key, the most significant columns fall into several groupings:

• Contract identity: CHR_ID, CONTRACT_NUMBER, CONTRACT_NUMBER_MODIFIER, COMPLETE_CONTRACT_NUMBER, SCS_CODE, and LINE_NUMBER identify the parent contract and the specific line within it.
• Lifecycle dates and periods: START_DATE, END_DATE, DATE_TERMINATED, DATE_RENEWED, END_PERIOD_NUM, END_PERIOD_NAME, END_QUARTER, and END_YEAR capture when the line was active and the period in which it expired.
• Status and transaction context: STS_CODE and TRN_CODE record the line status and transaction type at expiry.
• Pricing and currency: UNIT_PRICE, UNIT_PRICE_PERCENT, PRICE_NEGOTIATED, CURRENCY_CODE, PRICE_NEGOTIATED_RENEWED, and CURRENCY_CODE_RENEWED hold the original and renewed commercial terms.
• Reporting/ledger amounts: SOB_PRICE_UNIT, SOB_PRICE_NEGOTIATED, SOB_PRICE_NEGOTIATED_RENEWED, SOB_CURRENCY_CODE, BASE_PRICE_UNIT, BASE_PRICE_NEGOTIATED, BASE_PRICE_NEGOTIATED_RENEWED, and BASE_CURRENCY_CODE provide set-of-books and base-currency valuations.
• Organization and audit: AUTHORING_ORG_ID, ORGANIZATION_NAME, INVENTORY_ORGANIZATION_ID, CREATION_DATE, LAST_UPDATE_DATE, SECURITY_GROUP_ID, and the ATTRIBUTE_CATEGORY/ATTRIBUTE1–15 descriptive flexfield columns.

Common Use Cases and Queries

Reporting on expired-without-renewal contract lines is the primary use case. Analysts typically aggregate expired lines by authoring organization, period, or currency to support renewal tracking, revenue-at-risk analysis, and contract compliance reporting.

A representative pattern filters expired lines for the current accounting year and groups by organization:

• SELECT ORGANIZATION_NAME, END_PERIOD_NAME, COUNT(*), SUM(SOB_PRICE_NEGOTIATED) FROM OKI_EXPIRED_LINES WHERE END_YEAR = :year GROUP BY ORGANIZATION_NAME, END_PERIOD_NAME.
• Discrepancy checks comparing PRICE_NEGOTIATED against PRICE_NEGOTIATED_RENEWED to identify lines that expired without capturing renewal pricing.
• Security-scoped extracts joining SECURITY_GROUP_ID to FND_SECURITY_GROUPS to restrict visibility to authorized groups.

Related Objects

• FND_SECURITY_GROUPS — joined on OKI_EXPIRED_LINES.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID for row-level security scoping.
• OKI contract header tables — referenced via CHR_ID to retrieve parent contract attributes.
• OKI contract line tables — the source lines from which expired-line records are derived.
• OKI_CONTRACT_NUMBERS / complete-number views — related through COMPLETE_CONTRACT_NUMBER for consolidated reporting.
• HR/ORG organization views — resolved through AUTHORING_ORG_ID and INVENTORY_ORGANIZATION_ID.
• OKI Contracts Intelligence concurrent programs and APIs — populate and refresh expired-line data.

Because OKI Contracts Intelligence is documented as obsolete and not implemented in the current database, these relationships should be validated against the actual ETRM data model for the target 12.1.1 or 12.2.2 instance before use.