Search Results cac_sr_schedules_vl




Overview
Explore in AI Chat

Overview

The view APPS.CAC_SR_SCHEDULES_VL is a seeded, valid database object in Oracle E-Business Suite releases 12.1.1 and 12.2.2, owned by the APPS schema and catalogued under the JTF – CRM Foundation product family. It exposes schedule definitions maintained by the CRM Service Request scheduling framework (the CAC_SR_SCHEDULES entity set). The _VL suffix denotes a "translated" or language view: the view joins the base table and its translation table using the session language, returning the translated SCHEDULE_NAME and SCHEDULE_DESC attributes for the current user environment. Because it resolves multilanguage (MLS) text at query time, the view is the preferred reporting and integration surface for schedule metadata; it abstracts the underlying multi-table structure into a single row per schedule with no language join required by the caller.

Underlying Base Objects

The view is defined over two synonyms:

  • CAC_SR_SCHEDULES_B — the base table holding language-independent schedule attributes (identifiers, template references, category, activation dates, audit columns).
  • CAC_SR_SCHEDULES_TL — the translation table holding language-dependent text, keyed by SCHEDULE_ID and LANGUAGE.

The view text joins them on JSSTL.SCHEDULE_ID = JSSB.SCHEDULE_ID and constrains the translation row with JSSTL.LANGUAGE = USERENV('LANG'), so each query returns exactly one row per schedule for the caller's session language. The view exposes the base table's ROWID as ROW_ID, and passes through the object version numbers (OBJECT_VERSION_NUMBER, TEMPLATE_OVN) used for optimistic locking.

Key Columns

  • ROW_ID — the row identifier from the base table, useful for updates and diagnostics.
  • SCHEDULE_ID — primary key of the schedule and the join key between base and translation tables.
  • SCHEDULE_NAME — the translated, user-facing name of the schedule.
  • SCHEDULE_DESC — the translated description; this is the column users most commonly search for as "schedule_desc".
  • TEMPLATE_ID / TEMPLATE_OVN — reference to the schedule template and its version.
  • SCHEDULE_CATEGORY — classification of the schedule within the scheduling framework.
  • START_DATE_ACTIVE, END_DATE_ACTIVE, DELETED_DATE — effective-dating and soft-delete controls; rows with a non-null DELETED_DATE or an inactive date range should normally be excluded from active-schedule reporting.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical uses include LOV and validation queries in Oracle Forms-based CRM screens, concurrent program extracts, and custom reports that must display schedule text in the run-time language. A basic listing of active schedules is:

  • SELECT SCHEDULE_ID, SCHEDULE_NAME, SCHEDULE_DESC FROM APPS.CAC_SR_SCHEDULES_VL WHERE DELETED_DATE IS NULL AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE, SYSDATE) AND NVL(END_DATE_ACTIVE, SYSDATE);
  • SELECT SCHEDULE_ID, SCHEDULE_DESC FROM APPS.CAC_SR_SCHEDULES_VL WHERE UPPER(SCHEDULE_DESC) LIKE UPPER('%' || :p_search || '%'); — a description-driven search, matching the "schedule_desc" lookup pattern.
  • SELECT SCHEDULE_CATEGORY, COUNT(*) FROM APPS.CAC_SR_SCHEDULES_VL GROUP BY SCHEDULE_CATEGORY; — a category distribution report.

Because translated text is resolved through USERENV('LANG'), results vary by the session's NLS_LANG setting, and callers should not apply language predicates themselves. The view is read-only; DML must target the base and translation tables through the supported CRM APIs.