Search Results is_exp_not_renewed_yn

Overview

OKI.OKI_COV_PRD_LINES is a transactional table in the Oracle EBS Contracts for Service (Service Contracts) module. It stores information about covered product lines in service contracts and the specific products covered under those lines. The table is populated and maintained by the Load Covered Product Lines, Contracts Intelligence concurrent manager job, which extracts and stages contract coverage data for reporting and analytics purposes rather than serving as the primary transactional source of record.

The table resides in the OKI schema, is stored in the APPS_TS_TX_DATA tablespace, and carries 91 documented columns in the ETRM 12.1.1 physical schema. Under a heuristic Data Vault classification derived from the foreign-key structure, the object is modelled as standalone. In practice it behaves like a satellite attached to the service contract line business key, since it records descriptive and derived coverage attributes (dates, pricing, expiration and renewal flags) keyed by an internal line identifier. Organizations implementing Data Vault modelling may therefore treat OKI_COV_PRD_LINES as a satellite on a contract-line hub rather than as a hub in its own right.

Key Information Stored

The surrogate primary key of the table is CLE_ID, defined by the unique index OKI_COV_PRD_LINES_PK and the unique index OKI_COV_PRD_LINES_U1, both on CLE_ID. CLE_ID is the internal contract line identifier and the only documented business-key candidate; the remaining indexes are non-unique and support query access paths. The most significant columns include:

• CLE_ID — Internal contract line identifier; surrogate primary key.
• CHR_ID — Contract header identifier of the contract containing this line (index N1).
• PARENT_CLE_ID — Internal line identifier of the parent top line of the covered product line (index N4).
• CONTRACT_NUMBER / CONTRACT_NUMBER_MODIFIER / COMPLETE_CONTRACT_NUMBER — Human-readable contract identifiers, with the concatenated form indexed (N2, N6).
• COVERED_PRODUCT_ID and CUSTOMER_PRODUCT_ITEM_ID — The covered product definition and the customer's product instance (index N3).
• START_DATE / END_DATE — Effective date range of the covered line (indexes N5, N8).
• DATE_TERMINATED / DATE_RENEWED — Dates on which the coverage was terminated or renewed.
• IS_EXP_NOT_RENEWED_YN — Flag indicating whether the line expired without being renewed; indexed by OKI_COV_PRD_LINES_N9 to support expiration reporting.
• SYSTEM_NAME and SYSTEM_ID — Source instance or system identifier for the covered product (index N7).
• STS_CODE / STE_CODE / TRN_CODE / SCS_CODE — Status, service type, transaction and subclass code values.
• SERIAL_NUMBER, CONCATENATED_SEGMENTS, REFERENCE_NUMBER — Product instance identification attributes.
• END_PERIOD_NUM, END_PERIOD_NAME, END_QUARTER, END_YEAR — Denormalized period attributes used for expiry reporting.

Common Use Cases and Queries

Typical uses centre on contracts intelligence reporting: identifying covered products that have expired without renewal, quantifying coverage by contract or customer, and analyzing renewal and termination trends across periods.

• Find expired, non-renewed coverage lines for a contract:
  SELECT CLE_ID, CONTRACT_NUMBER, COMPLETE_CONTRACT_NUMBER, COVERED_PRODUCT_ID, END_DATE FROM OKI.OKI_COV_PRD_LINES WHERE IS_EXP_NOT_RENEWED_YN = 'Y' AND END_DATE BETWEEN :start_date AND :end_date;
• Retrieve all covered product lines for a contract header:
  SELECT CLE_ID, CHR_ID, LINE_NUMBER, COVERED_PRODUCT_ID, COV_PROD_QUANTITY FROM OKI.OKI_COV_PRD_LINES WHERE CHR_ID = :chr_id;
• Drill into the hierarchy of a top line and its covered lines:
  SELECT CLE_ID, PARENT_CLE_ID, LINE_NUMBER, START_DATE, END_DATE FROM OKI.OKI_COV_PRD_LINES WHERE CLE_ID = :cle_id OR PARENT_CLE_ID = :cle_id;
• Segment expired coverage by contract number, with the indexed COMPLETE_CONTRACT_NUMBER and IS_EXP_NOT_RENEWED_YN columns driving the plan for performance.
• Dashboard metrics on renewals versus expirations, using DATE_RENEWED, DATE_TERMINATED and the END_PERIOD_name/quarter/year attributes to group results by reporting period.

Related Objects

The documented FK/PK metadata classifies the table as standalone, meaning no explicit foreign keys are declared; joins are effectively expressed through the indexed surrogate identifiers. The most significant related objects and join columns are:

• OKI_COV_PRD_LINES parent-child self join via PARENT_CLE_ID = CLE_ID.
• Contract header objects joined through CHR_ID (indexed by N1), which link each covered line to its contract header.
• Covered product master objects joined through COVERED_PRODUCT_ID (indexed by N3).
• Customer product / installed base objects linked via CUSTOMER_PRODUCT_ITEM_ID and SERIAL_NUMBER.
• Service contract line objects linked via CLE_ID, the internal contract line identifier shared with the Contracts for Service transactional line tables.
• The Load Covered Product Lines concurrent program, which populates and refreshes the table and is the principal dependency for data currency.

Because the table is a load-maintained staging structure, it should be treated as read-only for reporting purposes; direct DML is not supported and all changes flow through the Contracts Intelligence load process.