Results for “jtf_ec_statuses_vl”

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

Overview

JTF_EC_STATUSES_VL is a seeded, read-only view owned by the APPS schema in Oracle E-Business Suite, supplied by the CRM Foundation (JTF) product family. It presents the set of task statuses that are valid for the Escalation usage, exposing both the translatable (TL) and base (B) attributes of each status in a single flattened row. The view is the language-aware (VL) representation of escalation statuses, joining the base status definition to its name and description in the session's current language.

The view is significant in reporting and integration because it captures the full lifecycle state machine of an escalation — including the ON_HOLD_FLAG, which the user searched for. This column identifies whether the status represents a "held" state, i.e., whether a task or escalation assigned to this status should be treated as paused. Rather than hard-coding status flags, applications query this view to determine which status codes, names, and transition permissions apply during the escalation lifecycle.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, JTF_EC_STATUSES_VL is defined over two base objects, both referenced as synonyms in the APPS schema:

• JTF_TASK_STATUSES_B — the base table storing the non-translatable status attributes, including all lifecycle flags, primary keys, audit columns, and descriptive flexfield attributes.
• JTF_TASK_STATUSES_TL — the translation table holding the language-specific NAME and DESCRIPTION for each status.

The join is performed on TASK_STATUS_ID, with the TL table filtered by T.LANGUAGE = USERENV('LANG') so the view returns rows only in the caller's language. The view further restricts results to B.USAGE = 'ESCALATION', meaning it returns only statuses configured for escalation workflows, not the broader set of task statuses. The B.ROWID is exposed as ROW_ID for row-level identification.

Key Columns

• TASK_STATUS_ID — the primary identifier for the status; the join key between the base and translation tables.
• NAME / DESCRIPTION — the translated, user-facing label and description of the status.
• ON_HOLD_FLAG — indicates whether this status corresponds to a held/paused state in the escalation lifecycle, which is the column referenced in the user's search.
• CLOSED_FLAG — identifies terminal statuses that close the escalation.
• ASSIGNED_FLAG, WORKING_FLAG, APPROVED_FLAG, COMPLETED_FLAG, CANCELLED_FLAG, REJECTED_FLAG, ACCEPTED_FLAG — lifecycle milestone indicators used by the escalation engine to validate transitions.
• SCHEDULABLE_FLAG — denotes whether the status permits scheduling actions.
• DELETE_ALLOWED_FLAG — controls whether records in this status may be deleted.
• SEEDED_FLAG — distinguishes Oracle-seeded statuses from user-defined ones.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range during which the status is usable.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield columns for extensibility.
• OBJECT_VERSION_NUMBER — optimistic locking column supporting concurrent updates.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage includes resolving the human-readable name of a status from its ID, verifying whether a given status is a hold state, and building pick lists of valid escalation statuses. The following query retrieves all escalation statuses and highlights those flagged as on-hold:

SELECT task_status_id,
       name,
       on_hold_flag,
       closed_flag,
       schedulable_flag
FROM   apps.jtf_ec_statuses_vl
WHERE  sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
ORDER BY name;

To isolate only held statuses, filter on the flag directly:

SELECT task_status_id, name
FROM   apps.jtf_ec_statuses_vl
WHERE  on_hold_flag = 'Y';

Because the view enforces the USERENV('LANG') predicate, it should be queried directly rather than through custom joins for translation, ensuring consistent, language-appropriate names in reports and integrations. Access is typically granted via APPS to reporting users and custom code.