Search Results okl_cs_related_contract_uv




Overview
Explore in AI Chat

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:

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

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.