Search Results oks_subscr_elements_pk




Overview
Explore in AI Chat

Overview

OKS.OKS_SUBSCR_ELEMENTS is a transactional table within the Oracle E-Business Suite Service Contracts (OKS) module, documented as "Used for Subscription Elements Information." It stores the individual line-level elements that comprise a subscription, functioning as the child detail table beneath the subscription header. In Oracle EBS 12.1.1 and 12.2.2, the table belongs to the OKS schema and holds 21 documented columns. Its status is VALID across both releases.

From a dimensional modeling perspective, the FK topology (a mandated parent reference to OKS_SUBSCR_HEADER_B plus a security-group reference to FND_SECURITY_GROUPS) suggests a satellite-leaning classification. The table's grain is one row per subscription element, with business attributes (amounts, dates, quantities) describing the parent subscription. This heuristic classification is a modeling suggestion rather than an EBS-imposed rule; OKS predates formal Data Vault constructs, but the structure maps cleanly to a satellite attached to a subscription hub.

Key Information Stored

The table's surrogate primary key is ID, enforced by the constraint OKS_SUBSCR_ELEMENTS_PK and the unique index OKS_SUBSCR_ELEMENTS_U1. The most significant columns include:

  • OSH_ID — foreign key to OKS_SUBSCR_HEADER_B; identifies the parent subscription header. This is the primary join path to subscription-level data.
  • DNZ_CHR_ID and DNZ_CLE_ID — denormalized references to the contract header and contract line, enabling rapid linkage to the underlying service contract without an extra hop.
  • AMOUNT — monetary value associated with the element.
  • START_DATE, END_DATE — the effective period for the element; critical for interval-based reporting.
  • QUANTITY and UOM_CODE — the billable quantity and unit of measure.
  • ORDER_HEADER_ID, ORDER_LINE_ID — references to the originating OM order and line that generated the element.
  • SEQ_NO — sequencing of elements within a subscription.
  • LINKED_FLAG — indicates whether the element is linked to another construct.
  • OM_INTERFACE_DATE — timestamp of the order-management interface event.
  • OBJECT_VERSION_NUMBER — optimistic locking column standard in EBS 12.2 OAF-based tables.
  • SECURITY_GROUP_ID — FK to FND_SECURITY_GROUPS, supporting multi-tenant data segregation.
  • Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The most frequent reporting pattern joins elements to their parent header to produce a subscription-level financial and schedule view. A representative query:

  • SELECT e.ID, e.OSH_ID, h.SUBSCRIPTION_NUMBER, e.AMOUNT, e.QUANTITY, e.UOM_CODE, e.START_DATE, e.END_DATE FROM OKS.OKS_SUBSCR_ELEMENTS e JOIN OKS.OKS_SUBSCR_HEADER_B h ON h.ID = e.OSH_ID WHERE e.START_DATE >= :p_start;

This supports revenue recognition analysis, subscription renewal forecasting, and quantity/UOM reconciliation against the originating OM order. Analysts also use ORDER_HEADER_ID and ORDER_LINE_ID to trace element origin back to OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL, and DNZ_CHR_ID to pivot to the service contract for combined contract-plus-subscription reporting. Because SECURITY_GROUP_ID is present, queries in a multi-org or multi-tenant context should filter by security group when row-level isolation is required.

Related Objects

The following objects are most significant to OKS_SUBSCR_ELEMENTS:

  • OKS_SUBSCR_HEADER_B — parent header; joined on OKS_SUBSCR_ELEMENTS.OSH_ID = OKS_SUBSCR_HEADER_B.ID (documented FK). All subscription-level context resolves through this table.
  • FND_SECURITY_GROUPS — joined on OKS_SUBSCR_ELEMENTS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID (documented FK).
  • OKS_SUBSCR_HEADER_TL — translation table for subscription header descriptions, reached through the header.
  • OKS_K_HEADERS_B / OKS_K_HEADERS_TL — service contract header tables reachable via DNZ_CHR_ID.
  • OKS_K_LINES_B — service contract lines reachable via DNZ_CLE_ID.
  • OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL — originating order objects referenced by ORDER_HEADER_ID and ORDER_LINE_ID.

These relationships position OKS_SUBSCR_ELEMENTS as the detail anchor for subscription reporting, bridging header, contract, order, and security-group contexts within a single FK-rich structure.