Search Results oks_template_set




Overview
Explore in AI Chat

Overview

OKS_TEMPLATE_SET is a table in the OKS schema belonging to the Service Contracts (OKS) module in Oracle E-Business Suite 12.1.1 and 12.2.2. As its ETRM description states, the table stores template set information used by Service Contracts to group and organize templates applied to contract authoring, quoting, and correspondence. A template set functions as a reusable container that governs which report templates, quote letters, and cover letters are available for a given business context, typically scoped by operating unit via ORG_ID and by date range via START_DATE and END_DATE.

From a Data Vault modeling perspective, the mined foreign-key structure classifies OKS_TEMPLATE_SET as hub-leaning. This is a heuristic suggestion rather than a documented Oracle construct: the table behaves as a business-concept hub because it holds a stable, uniquely identified entity (a template set) that other objects reference, rather than serving purely as a transactional link or a descriptive satellite. Modeling exercises should treat ID as the durable hub key, with the remaining descriptive attributes suitable for satellite placement.

Key Information Stored

The table is documented with 16 physical columns in the ETRM 12.2.2 schema. The most significant are:

  • ID — Surrogate primary key, enforced by OKS_TEMPLATE_SET_PK. This is the column referenced by downstream foreign keys and should be treated as the immutable technical identifier.
  • NAME — Business-facing identifier for the template set; the primary business-key candidate for user-facing lookups and reporting filters.
  • DESCRIPTION — Free-text explanation of the template set's purpose or scope.
  • ORG_ID — Operating unit / multi-org partitioning column that scopes the template set to a specific organization.
  • START_DATE and END_DATE — Effective dating that controls when the template set is active; standard EBS date-range validation applies.
  • QUOTE_LETTER and COVER_LETTER — References to the letter or template content used when generating quote and cover correspondence.
  • STS_CODE — Status code indicating the lifecycle state of the template set (for example, active or inactive).
  • TEMPLATE_SOURCE — Indicates the origin or source classification of the template set.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/BC4J framework to detect concurrent updates.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit (WHO) columns capturing creation and last-modification metadata.

NAME is the natural business-key candidate, though the metadata documents only the surrogate PK constraint explicitly; uniqueness of NAME is typically enforced within ORG_ID by application logic rather than a documented unique index.

Common Use Cases and Queries

Typical uses include determining which template set applies to a contract or quote in a given operating unit, reporting on active versus expired template sets, and tracing which report templates belong to a set. A representative query joining the child table is:

  • SELECT ts.id, ts.name, ts.org_id, rt.report_template_id FROM oks.oks_template_set ts, oks.oks_report_templates rt WHERE rt.template_set_id = ts.id AND ts.org_id = :org_id AND SYSDATE BETWEEN ts.start_date AND NVL(ts.end_date, SYSDATE);
  • Filtering on STS_CODE to return only active template sets for a picklist or LOV.
  • Auditing: selecting CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE to review configuration change history.
  • Date-effectiveness reporting to identify template sets that have expired or are not yet effective.

Related Objects

The documented foreign-key relationship links this table to the report template definitions that depend on it:

  • OKS_REPORT_TEMPLATES — Child table; its TEMPLATE_SET_ID column references OKS_TEMPLATE_SET, forming the primary documented dependency. This is the principal join path for enumerating templates within a set.
  • OKS_TEMPLATE_SET_PK — Primary key constraint on ID, referenced by the foreign key above.
  • Service Contracts authoring and quoting APIs that consume template sets for letter generation (QUOTE_LETTER, COVER_LETTER).
  • Multi-org views and security constructs that filter OKS_TEMPLATE_SET by ORG_ID.

Together these objects form the template configuration layer of Service Contracts, with OKS_TEMPLATE_SET acting as the hub that organizes dependent report templates.