Search Results oks_subscr_patterns




Overview
Explore in AI Chat

Overview

OKS_SUBSCR_PATTERNS is a Service Contracts (OKS) module table in Oracle E-Business Suite, present and valid in both 12.1.1 and 12.2.2. It stores subscription pattern information — the recurring scheduling definitions that drive when subscription-based service entitlements, billing events, or coverage periods are generated for a subscription header. Each row defines a temporal cadence (year, month, week, weekday, and day) attached to a subscription record, allowing Oracle Service Contracts to model periodic commitments such as monthly maintenance windows, quarterly service intervals, or weekly on-site visits.

From a Data Vault modeling perspective, the metadata classifies this object as satellite-leaning. This is a modeling suggestion rather than a physical attribute: the table carries a surrogate key (ID), a foreign key to a parent header (OSH_ID), descriptive pattern attributes, and a security group reference. That structure is characteristic of a satellite hanging off the subscription header hub, holding descriptive and temporal detail rather than defining a new business entity or resolving a many-to-many link. The column position indicators (SEQ_NO, WEEK, WEEK_DAY, DAY, MONTH, YEAR) reinforce the satellite interpretation, as they describe qualifiers on the parent subscription rather than independent entities.

Key Information Stored

The table is documented with 17 columns in the 12.2.2 physical schema. The most operationally significant are the following.

  • ID — the surrogate primary key, enforced by OKS_SUBSCR_PATTERNS_PK and also uniquely indexed by OKS_SUBSCR_PATTERNS_U1. It is the business-key candidate as documented, though it functions as a system-generated identifier.
  • OSH_ID — foreign key to OKS_SUBSCR_HEADER_B, tying every pattern row to its owning subscription header. This is the primary join column for subscription reporting.
  • SEQ_NO — sequence number ordering multiple patterns under a single subscription header.
  • YEAR, MONTH, WEEK, WEEK_DAY, DAY — the temporal components that define the recurrence cadence of the pattern. Together these attributes determine when the subscription period or service event recurs.
  • DNZ_CHR_ID, DNZ_CLE_ID — denormalized identifier columns maintained for date-effectivity or calendar processing support.
  • OBJECT_VERSION_NUMBER — the standard EBS optimistic locking column used by the Oracle Application Framework (OAF) to detect concurrent updates.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns present on nearly every transactional EBS table.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org and security-group partitioning where enabled.

Common Use Cases and Queries

Typical usage centers on reporting and validating subscription recurrence definitions. A common join retrieves all patterns for a subscription header:

  • SELECT p.id, p.osh_id, p.seq_no, p.year, p.month, p.week, p.week_day, p.day FROM oks_subscr_patterns p WHERE p.osh_id = :osh_id ORDER BY p.seq_no;

Analysts frequently combine this table with OKS_SUBSCR_HEADER_B to surface subscription-level context such as the customer or contract number alongside the cadence detail. Reconciliation queries verify that every active subscription header has at least one pattern, and that SEQ_NO values are contiguous without gaps. Because of the WHO audit columns, change-tracking reports filter on LAST_UPDATE_DATE to identify recently modified patterns. Security-conscious queries add a predicate on SECURITY_GROUP_ID to restrict output to the caller's authorized group. Data migration and interface scripts insert pattern rows after the parent header is created, satisfying the OSH_ID foreign key constraint, and OAF-based update flows rely on OBJECT_VERSION_NUMBER for locking.

Related Objects

The most significant related objects, based on documented foreign key relationships, are:

  • OKS_SUBSCR_HEADER_B — parent table; joined via OKS_SUBSCR_PATTERNS.OSH_ID = OKS_SUBSCR_HEADER_B.ID. This is the dominant relationship and the one most queries depend on.
  • FND_SECURITY_GROUPS — joined via OKS_SUBSCR_PATTERNS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID, governing row-level security filtering.
  • OKS_SUBSCR_PATTERNS_PK / OKS_SUBSCR_PATTERNS_U1 — the primary key constraint and unique index on ID that enforce row integrity and support indexed lookups.
  • OKS Service Contracts subscription APIs and OAF pages — the subscription maintenance UI and programmatic interfaces that create, update, and delete pattern rows, and that consume OBJECT_VERSION_NUMBER for concurrency control.

Because the metadata documents only two foreign keys, related-object analysis should be extended through the OKS subscription header and related contract tables when building complete lineage or reporting models.