Results for “cac_sr_exceptions_tl”

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

Overview

The CAC_SR_EXCEPTIONS_VL view is a translated (VL, or "view language") database object owned by the APPS schema in Oracle E-Business Suite, belonging to the JTF — CRM Foundation product family. It presents service resource exception data, capturing periods during which a resource, such as a field service technician or CRM agent, is unavailable according to a defined schedule. In ETRM 12.2.2 the object is documented with a status of VALID, meaning the compiled view exists and resolves correctly against its base objects.

The VL designation is significant. Oracle EBS uses a "_B" (base) and "_TL" (translated) table pair pattern to store multilingual data. The VL view joins these two tables and filters the translation rows by the session language, so each query returns the description and name in the language of the current user environment. This makes the view the preferred access point for reporting, concurrent programs, OAF pages, and integration interfaces that require exception records with human-readable, localized text rather than raw identifiers.

Underlying Base Objects

The view is defined over two synonyms referenced in the ETRM metadata, which resolve to the underlying tables CAC_SR_EXCEPTIONS_B and CAC_SR_EXCEPTIONS_TL. The view text confirms the join structure:

• CAC_SR_EXCEPTIONS_B — supplies the transactional and structural attributes, aliased JSEB in the view definition, including row identity, date/time ranges, flags, audit columns, and calendar references.
• CAC_SR_EXCEPTIONS_TL — supplies the translatable text, aliased JSETL, notably EXCEPTION_NAME and EXCEPTION_DESC.

The join predicate is JSETL.EXCEPTION_ID = JSEB.EXCEPTION_ID combined with JSETL.LANGUAGE = USERENV('LANG'). This language restriction ensures that only the current session's language translation row is returned, preventing duplicate rows across languages. The view exposes ROW_ID from the base table to support row-level addressing.

Key Columns

The view exposes the following documented columns:

• ROW_ID — the physical row identifier of the base record.
• EXCEPTION_ID — primary identifier of the exception, used to join back to base tables.
• OBJECT_VERSION_NUMBER — optimistic locking version counter.
• EXCEPTION_TYPE and PERIOD_CATEGORY_ID — classify the nature of the exception.
• TEMPLATE_ID — associates the exception with a scheduling template.
• START_DATE_TIME, END_DATE_TIME, WHOLE_DAY_FLAG — define the exception window and whether it spans full days.
• EXCEPTION_NAME and EXCEPTION_DESC — the localized translatable text, where EXCEPTION_DESC is the column most commonly referenced by users searching on "exception_desc."
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.
• HR_CAL_EVENT_TYPE, HR_CAL_EVENT_ID — link the exception to HR calendar events.

Common Use Cases and Queries

Typical uses include availability reporting, calendar integration, and interfaces that must display exception descriptions in the user's language. A basic query returns the localized description for a given exception:

• SELECT exception_id, exception_name, exception_desc FROM cac_sr_exceptions_vl WHERE exception_id = :p_id;
• SELECT exception_desc, start_date_time, end_date_time FROM cac_sr_exceptions_vl WHERE whole_day_flag = 'Y';

Because the view applies the language filter automatically, it is recommended over querying the _TL table directly, which would require an explicit LANGUAGE predicate. Note that the view is read-oriented; DML should target the base tables.