Search Results oki_expired_lines_n1

Overview

The OKI.OKI_EXPIRED_LINES table is a Contracts Intelligence staging and reporting structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases. It stores a denormalized snapshot of contract lines that reached their end date and were not renewed. Rather than capturing transactional contract data at the point of entry, this table is populated and maintained by the Load Expired Lines, Contracts Intelligence concurrent program, which extracts qualifying line records from the core contracts schema into this purpose-built repository for analytical reporting.

Because the table is populated by a batch process rather than through online transactions, it functions as a reporting satellite rather than a transactional base table. Its columns closely mirror attributes of contract lines, contract headers, pricing, and General Ledger period indicators, which allows downstream reporting to avoid repeated joins across the high-volume operational contract tables. A heuristic Data Vault classification suggests this object behaves as a standalone satellite: its primary key, CLE_ID, is an internal contract line identifier (a business key imported from the source system rather than a locally generated surrogate), and the table carries no outgoing foreign keys other than SECURITY_GROUP_ID, which references FND_SECURITY_GROUPS for row-level security.

Key Information Stored

The table contains roughly fifty-three documented columns. The most significant include:

• CLE_ID — Internal contract line identifier, defined as the primary key (OKI_EXPIRED_LINES_PK) and enforced by the unique index OKI_EXPIRED_LINES_U1. This is the principal business-key candidate and the most reliable join key to source line data.
• CHR_ID — The contract header identifier for the contract containing the line. A nonunique index, OKI_EXPIRED_LINES_N1, is defined on this column to support header-level reporting and joins.
• CONTRACT_NUMBER, CONTRACT_NUMBER_MODIFIER, COMPLETE_CONTRACT_NUMBER — The human-readable contract number, its modifier, and the concatenated form used for display and lookup in reports.
• SCS_CODE — The contract category (sub-class) code.
• LINE_NUMBER — The external line number as visible to the user.
• START_DATE, END_DATE — The effective date range of the line; END_DATE is the basis for identifying the expired population.
• STS_CODE, TRN_CODE, DATE_TERMINATED, DATE_RENEWED — Line status, termination reason, and the dates the line was terminated or renewed. DATE_RENEWED distinguishes lines that were renewed from those that truly lapsed.
• END_PERIOD_NUM, END_PERIOD_NAME, END_QUARTER, END_YEAR — General Ledger period attributes for the period in which the line ends, enabling accounting-period-based analysis.
• UNIT_PRICE, UNIT_PRICE_PERCENT, PRICE_NEGOTIATED — Pricing attributes, along with the SOB_* and BASE_* variants that carry functional (set of books) and base currency amounts.
• SECURITY_GROUP_ID — Enforces organizational data security and is the sole documented foreign key reference.

Common Use Cases and Queries

The primary use case is expired-contract reporting: identifying lines that lapsed for renewal follow-up, forecasting revenue attrition, or reconciling expirations by accounting period. A typical query lists expired lines by contract and period:

SELECT complete_contract_number, line_number, end_date,
       end_period_name, end_year, unit_price
FROM   oki.oki_expired_lines
WHERE  end_period_name = :period
ORDER BY complete_contract_number, line_number;

Because CHR_ID is indexed, header-level aggregation is efficient:

SELECT chr_id, COUNT(*) expired_lines, SUM(unit_price)
FROM   oki.oki_expired_lines
GROUP BY chr_id;

Security-driven queries filter on SECURITY_GROUP_ID to restrict visibility by operating unit or responsibility. Analysts also use the table to compare negotiated price versus renewed price to assess renewal economics.

Related Objects

• OKI_CONTRACT_HEADERS — Joined on CHR_ID to retrieve header-level contract attributes.
• OKI_CONTRACT_LINES — Source of the expired line data, linked by CLE_ID.
• OKI_EXPIRED_LINES_U1 / OKI_EXPIRED_LINES_N1 — Unique and nonunique indexes supporting lookup and analytical access.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID; enforces row-level security.
• Contracts Intelligence concurrent program (Load Expired Lines) — The batch process that populates and refreshes this table.