Results for “cac_sr_templates_tl”

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

Overview

In Oracle E-Business Suite 12.1.1 and 12.2.2, the object APPS.CAC_SR_TEMPLATES_VL is a database view owned by the APPS schema and registered under the FND — Application Object Library product. It belongs to the ETRM (Enterprise Territory and Resource Management) family of objects and presents service resource template definitions in a translated, user-facing form. The _VL suffix is a long-standing EBS convention indicating a "view of the base and translation tables," in which the base table supplies language-independent attributes and the translation table supplies the language-dependent name and description columns.

The user search that surfaced this object was for cac_sr_templates_b, the base table over which the view is constructed. The view therefore functions as the logical, language-resolved access point for SR template data, while the _B table holds the underlying physical rows. A key characteristic, derived directly from the view text, is the restriction JSTTL.LANGUAGE = USERENV('LANG'). This means the view returns exactly one row per template, resolved to the language of the current session, which preserves a one-to-one relationship between template identifiers and returned records. In Oracle EBS 12.1.1 and 12.2.2 the view is documented as VALID, and the 12.2.2 ETRM metadata lists its referenced base objects as CAC_SR_TEMPLATES_B (SYNONYM) and CAC_SR_TEMPLATES_TL (SYNONYM), resolving in the runtime data dictionary to the APPS synonyms over those tables.

Underlying Base Objects

CAC_SR_TEMPLATES_VL is defined as an equijoin between two objects. The first, CAC_SR_TEMPLATES_B (aliased JSTB in the view text), supplies the base, language-independent columns. The second, CAC_SR_TEMPLATES_TL (aliased JSTTL), supplies the translated name and description. The join predicate is JSTTL.TEMPLATE_ID = JSTB.TEMPLATE_ID combined with the session-language filter JSTTL.LANGUAGE = USERENV('LANG'). Because the _TL table is expected to hold one translation per installed language for each template, the join and language filter together yield a single, deterministic result row per template per session.

In the runtime dictionary the view references the base tables through APPS synonyms, which is the standard EBS pattern that hides the underlying schema ownership from the application. The view likewise exposes JSTB.ROWID as ROW_ID, a construct typically introduced to give the view an addressable pseudo-row identifier when used by the Forms or OA Framework layers.

Key Columns

• ROW_ID — the rowid of the base CAC_SR_TEMPLATES_B row, providing a stable pseudo-key.
• TEMPLATE_ID — the primary identifier linking the base and translation rows.
• TEMPLATE_NAME and TEMPLATE_DESC — translated, language-dependent values sourced from CAC_SR_TEMPLATES_TL.
• TEMPLATE_TYPE — classifies the template.
• TEMPLATE_LENGTH_DAYS — duration or span associated with the template.
• START_DATE_ACTIVE and END_DATE_ACTIVE — effective dating for the template.
• DELETED_DATE — soft-delete timestamp; non-null indicates a logically deleted template.
• OBJECT_VERSION_NUMBER — optimistic locking column managed by the EBS framework.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns carried from the base table.

Common Use Cases and Queries

The view is most frequently used for reporting and integration where template identifiers and translated captions must be presented together, for example in concurrent program extracts, BI Publisher reports, or inbound/outbound interfaces that need language-resolved labels. A representative query selecting active, non-deleted templates is:

• SELECT template_id, template_name, template_desc, template_type, template_length_days, start_date_active, end_date_active FROM apps.cac_sr_templates_vl WHERE deleted_date IS NULL AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE + 1) ORDER BY template_name;
• SELECT template_id, template_name FROM apps.cac_sr_templates_vl WHERE template_type = :p_type;
• SELECT v.template_id, v.template_name, b.created_by, b.creation_date FROM apps.cac_sr_templates_vl v, apps.cac_sr_templates_b b WHERE v.template_id = b.template_id;

Because the view applies USERENV('LANG'), consumers should be aware that results vary with the session language; reports requiring a fixed locale must set the session language explicitly or query the _TL table directly with an explicit LANGUAGE predicate.