Results for “date_renewed”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OKL_CS_RELATED_CONTRACT_UV is an APPS-owned database view in Oracle E-Business Suite Release 12.1.1 and 12.2.2, delivered as part of the OKL – Leasing and Finance Management product family (also documented under the ETRM/OKC contracts schema). The view exposes a flattened, language-resolved list of contracts that are grouped as "related contracts" — that is, contract header records linked through a parent grouping construct rather than through direct parent/child foreign keys. Its purpose is to present, in a single queryable row, the identifying, status, classification and lifecycle attributes of each contract that participates in a relationship group, together with the parent group identifier that ties the members together.

Because the OKC contract tables are highly normalized — headers split across a base table and a translated table, statuses and subclasses stored as codes requiring translation lookups — the view provides reporting and integration layers with a denormalized access path. It is commonly consumed by concurrent programs, Oracle Reports/BI Publisher data models, and custom integrations that need to enumerate related contracts without hand-coding joins to the five underlying tables. The view returns exactly one row per related contract header, keyed by ROW_ID from the base header table, making it suitable for both tabular reporting and programmatic record processing.

Underlying Base Objects

The view is defined over five documented base objects, all referenced through APPS synonyms:

• OKC_K_GRPINGS (aliased CGP) — the grouping table that establishes the parent relationship via CGP_PARENT_ID and links members through INCLUDED_CHR_ID.
• OKC_K_HEADERS_B (aliased CHRB) — the base contract header table, supplying all transactional and date-bearing columns.
• OKC_K_HEADERS_TL (aliased CHRT) — the translated header table, supplying SHORT_DESCRIPTION, COMMENTS, DESCRIPTION and COGNOMEN.
• OKC_STATUSES_TL (aliased STS) — resolves STS_CODE into a translated STATUS_MEANING.
• OKC_SUBCLASSES_TL (aliased SC) — resolves SCS_CODE into a translated SUBCLASS_MEANING.

The join condition CGP.INCLUDED_CHR_ID = CHRB.ID drives the relationship, while the translated tables are joined on ID/CODE with the additional predicate LANGUAGE = USERENV('LANG') so that descriptions and meanings are returned in the session language. The header text table is joined by ID only (CHRT.ID = CHRB.ID). The view carries the ORDERED and USE_NL(CHRB, CHRT) hints, indicating the optimizer is directed to drive from the grouping table and nested-loop into the header tables.

Key Columns

• ROW_ID / CONTRACT_ID — the row identifier and primary key of the underlying contract header.
• PARENT_GROUP_ID — the grouping identifier (CGP_PARENT_ID) that relates the returned contract to its group; central to any "related contracts" query.
• DATE_SIGNED — the date the contract was signed, carried from OKC_K_HEADERS_B. This is the column users most frequently target when filtering or reporting on contract execution dates, and it is exposed here without the need to join the header base table directly.
• DATE_APPROVED, DATE_TERMINATED, DATE_RENEWED, DATETIME_CANCELLED — the remaining lifecycle dates for the contract.
• STATUS_CODE / STATUS_MEANING — the contract status code and its language-resolved meaning.
• SUBCLASS_CODE / SUBCLASS_MEANING — the contract subclass code and translated meaning.
• CONTRACT_NUMBER / CONTRACT_NUMBER_MODIFIER — the human-readable contract identifier and its modifier.
• CONTRACT_AMOUNT / CURRENCY_CODE — the estimated contract amount and its currency.
• START_DATE, END_DATE, AUTO_RENEW_DAYS — term and renewal attributes.
• AUDIT columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus OBJECT_VERSION_NUMBER for optimistic locking.
• Descriptive columns — SHORT_DESCRIPTION, DESCRIPTION, COMMENTS, KNOWN_AS.

Common Use Cases and Queries

Typical scenarios include listing all sibling contracts under a given relationship group, auditing unsigned or pending contracts, and feeding related-contract data into downstream billing or reporting processes.

To retrieve related contracts for a specific parent group, filtering on the grouping identifier:

• SELECT contract_number, status_meaning, subclass_meaning, date_signed, start_date, end_date FROM okl_cs_related_contract_uv WHERE parent_group_id = :p_group_id ORDER BY contract_number;

To isolate contracts signed within a date range, using the column that motivated this search:

• SELECT contract_id, contract_number, date_signed, status_meaning FROM okl_cs_related_contract_uv WHERE date_signed BETWEEN :p_from_date AND :p_to_date;

To identify unsigned contracts still open (DATE_SIGNED null and an active status):

• SELECT contract_id, contract_number, parent_group_id, status_meaning FROM okl_cs_related_contract_uv WHERE date_signed IS NULL;

Because DATE_SIGNED is sourced from OKC_K_HEADERS_B and surfaced directly, these queries avoid an explicit join to the header base table, reducing both development effort and risk of mismatched join predicates. All queries should filter with bind variables or selective predicates on CONTRACT_ID, PARENT_GROUP_ID or the date columns to limit the nested-loop access cost implied by the view's USE_NL hints.