Results for “cac_sr_tmpl_details”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CAC_SR_TMPL_DETAILS is a JTF (CRM Foundation) table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores the non-translated data of a detail line belonging to a Schedule Template. Schedule Templates are used by the CRM scheduling infrastructure to define reusable patterns of working periods, and each template can be composed of multiple detail lines. This table captures the operational attributes of those detail lines, including sequence, daily start and stop boundaries, and references to a child period or a nested child template. It therefore acts as the structural backbone that links a parent schedule template to its constituent periods or to other templates.

From a Data Vault modeling perspective, the FK structure mined from the metadata suggests a link classification. The table predominantly records associations — a template to a period, and a template to a child template — rather than descriptive history, which is characteristic of a link entity. This is offered as a modeling suggestion and not a prescriptive DBA requirement.

Key Information Stored

The primary surrogate key is TEMPLATE_DETAIL_ID, which uniquely identifies each detail line and is enforced by the unique index CAC_SR_TMPL_DTLS_B_U1. Distinct from the surrogate, this index also serves as the business-key candidate for the row.

• TEMPLATE_DETAIL_ID — surrogate primary key and business-key candidate for the detail line.
• TEMPLATE_ID — foreign key to CAC_SR_TEMPLATES_B; identifies the parent schedule template.
• TEMPLATE_DETAIL_SEQ — sequence number ordering the detail line within its parent template.
• DAY_START — the start time boundary for the detail line's daily working window.
• DAY_STOP — the stop time boundary for the detail line's daily working window.
• CHILD_PERIOD_ID — foreign key to CAC_SR_PERIODS_B; references the period used by this detail line.
• CHILD_TEMPLATE_ID — foreign key to CAC_SR_TEMPLATES_B; references a nested child template.
• CREATED_BY, CREATION_DATE — standard audit columns capturing row creation metadata.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns capturing the last modification of the row.

Common Use Cases and Queries

Typical usage involves retrieving all detail lines for a given schedule template, resolving the referenced period or child template, and reporting on daily availability windows. A common join pattern is:

• Join CAC_SR_TMPL_DETAILS.TEMPLATE_ID to CAC_SR_TEMPLATES_B.TEMPLATE_ID to obtain the parent template's context.
• Join CAC_SR_TMPL_DETAILS.CHILD_PERIOD_ID to CAC_SR_PERIODS_B.PERIOD_ID to resolve the period definition.
• Join CAC_SR_TMPL_DETAILS.CHILD_TEMPLATE_ID to CAC_SR_TEMPLATES_B.TEMPLATE_ID (a self-referencing template lookup) to resolve nested templates.

A representative query orders detail lines by TEMPLATE_DETAIL_SEQ for a specific template and selects DAY_START and DAY_STOP to reconstruct the scheduling pattern. Reporting use cases include schedule validation, identifying templates with missing child references, and auditing start/stop boundaries for operational calendars. Filtering on LAST_UPDATE_DATE supports incremental extraction for data warehouse loads.

Related Objects

The following objects are significant to CAC_SR_TMPL_DETAILS based on documented foreign key relationships:

• CAC_SR_TEMPLATES_B — referenced twice, via TEMPLATE_ID (parent template) and CHILD_TEMPLATE_ID (nested child template).
• CAC_SR_PERIODS_B — referenced via CHILD_PERIOD_ID, supplying the period definition used by the detail line.
• CAC_SR_TMPL_DTLS_TL — the translated counterpart table holding language-specific detail line text, linked through TEMPLATE_DETAIL_ID.
• JTF scheduling APIs — the CRM Foundation scheduling routines that read template details when generating available time slots consume this table.
• Concurrent and reporting views — inquiry and extract views over schedule templates commonly join this table to present composite template structures.

Collectively, these relationships establish CAC_SR_TMPL_DETAILS as the connective layer between schedule templates and their periods, and between parent and child templates within the CRM Foundation scheduling model.