Search Results cac_sr_templates_b




Overview
Explore in AI Chat

Overview

In Oracle EBS 12.1.1 and 12.2.2, CAC_SR_TEMPLATES_B is the base table that stores general information about a Schedule Template within the JTF – CRM Foundation product family, also documented under the CAC (Common Application Components) naming convention. Physically owned by the JTF schema, the table is a valid, persistent relational object and is central to how recurring service, resource, and activity scheduling patterns are modeled in the CRM scheduling infrastructure.

Each row in the table represents one schedule template — a reusable definition that governs the length, type, and active lifecycle of a scheduling pattern. Templates act as the parent construct against which exceptions, sub-schedules, translated descriptions, and template details are defined.

From a Data Vault modeling perspective, the mined FK structure classifies CAC_SR_TEMPLATES_B as hub-leaning. This is a heuristic suggestion: the table holds a stable business entity key (TEMPLATE_ID) that numerous dependent tables reference, consistent with a hub accommodating descriptive attributes. Modelers may choose to represent it as a hub with an associated satellite capturing the descriptive and audit columns.

Key Information Stored

The table contains 12 documented columns. The most significant are:

  • TEMPLATE_ID — The surrogate primary key, defined by constraint CAC_SR_TEMPLATES_B_PK. It is also the single documented business-key candidate, enforced by unique index CAC_SR_TEMPLATES_B_U1, and serves as the join key across all dependent tables.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the EBS framework to detect concurrent updates.
  • TEMPLATE_TYPE — Classifies the template, determining how the schedule is interpreted and applied.
  • TEMPLATE_LENGTH_DAYS — The duration of the template expressed in days; a core scheduling attribute.
  • START_DATE_ACTIVE — The date from which the template becomes effective and usable.
  • END_DATE_ACTIVE — The date on which the template ceases to be effective.
  • DELETED_DATE — Soft-delete marker; a populated value indicates a logically deleted (not physically removed) template.
  • CREATED_BY / CREATION_DATE — Standard WHO audit columns recording record creation.
  • LAST_UPDATED_BY / LAST_UPDATE_DATE — Standard WHO audit columns recording the most recent modification.
  • LAST_UPDATE_LOGIN — Identifies the login/session associated with the last change.

The surrogate key (TEMPLATE_ID) is distinct from any descriptive business identifier; the unique index on TEMPLATE_ID reinforces that the surrogate is also the enforced uniqueness anchor of the table.

Common Use Cases and Queries

Schedule templates support recurring scheduling and exception handling. Typical operational and reporting scenarios include:

  • Retrieving all active templates effective on a given date.
  • Listing soft-deleted or inactive templates for audit and cleanup.
  • Aggregating template length by type for capacity planning.

Representative SQL patterns:

  • SELECT TEMPLATE_ID, TEMPLATE_TYPE, TEMPLATE_LENGTH_DAYS FROM JTF.CAC_SR_TEMPLATES_B WHERE TRUNC(SYSDATE) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)) AND DELETED_DATE IS NULL;
  • SELECT TEMPLATE_TYPE, COUNT(*) FROM JTF.CAC_SR_TEMPLATES_B WHERE DELETED_DATE IS NULL GROUP BY TEMPLATE_TYPE;
  • SELECT TEMPLATE_ID FROM JTF.CAC_SR_TEMPLATES_B WHERE DELETED_DATE IS NOT NULL;

Because translations and details are stored in related tables, reporting joins typically consolidate the base, TL, and detail tables for a complete template picture.

Related Objects

FK metadata documents the following dependent objects, all joined on TEMPLATE_ID unless noted:

  • CAC_SR_TEMPLATES_TL — Translation table joined via CAC_SR_TEMPLATES_TL.TEMPLATE_ID, holding language-specific template text.
  • CAC_SR_TMPL_DETAILS — Template details, joined on both CAC_SR_TMPL_DETAILS.TEMPLATE_ID and CAC_SR_TMPL_DETAILS.CHILD_TEMPLATE_ID, supporting parent-child template composition.
  • CAC_SR_EXCEPTIONS_B — Exception definitions, joined via CAC_SR_EXCEPTIONS_B.TEMPLATE_ID.
  • CAC_SR_SCHEDULES_B — Generated schedules, joined via CAC_SR_SCHEDULES_B.TEMPLATE_ID.

These relationships make CAC_SR_TEMPLATES_B the anchor entity for template translation, detail, exception, and schedule data in the JTF/CRM scheduling model.