Search Results ref_doc_number




Overview
Explore in AI Chat

Overview

JTF_ESC_UWQ_V is a read-only database view owned by the APPS schema within the JTF – CRM Foundation product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is clearly delimited in the ETRM metadata: it exposes escalation details for tasks that are surfaced in the Universal Work Queue (UWQ). UWQ is the CRM Foundation delivery mechanism through which tasks, leads, opportunities, and other work items are pushed to agents and service personnel. Escalation records represent tasks whose escalation level has been set, indicating that a work item has breached, or is approaching breach of, an internal service threshold.

Because this is a view rather than a table, it holds no data of its own. It resolves relationships across the task, task status, lookup, and task reference tables at query time, applying language-sensitive joins through USERENV('LANG') so that each user sees descriptions and status names in their session language. The view is therefore best understood as a reporting and integration surface rather than a transactional object. It is frequently consumed by UWQ dashboard regions, by concurrent programs that build escalation worklists, and by custom reports and extracts that need a flattened, human-readable representation of escalated tasks.

A notable characteristic of this view is its restrictive WHERE clause. It filters to TASK_TYPE_ID = 22, excludes deleted, closed, cancelled, completed, and rejected statuses, and requires the task reference with REFERENCE_CODE = 'ESC'. The result set is consequently limited to open, active escalations only.

Underlying Base Objects

The view is defined over a set of JTF task and lookup objects, joined primarily on TASK_ID and status identifiers. The documented referenced base objects are:

In ETRM 12.2.2 several of these are recorded as synonyms rather than base tables, reflecting the standard APPS synonym layer.

Key Columns

The projection begins with task identity and lifecycle attributes: TASK_ID, TASK_NUMBER, DESCRIPTION, TASK_STATUS, ACTUAL_START_DATE, PLANNED_END_DATE, and ACTUAL_END_DATE. Ownership is exposed through OWNER_TYPE_CODE and OWNER_ID, with JTF_TASK_UTL.GET_OWNER deriving a display value in the OWNER column.

Escalation-specific columns include ESCALATION_LEVEL (the internal code stored on the task) and ESCALATION_LEVEL_NAME (the translated meaning from FND_LOOKUPS). Customer context is provided by CUSTOMER_NAME, computed via JTF_TASK_UTL.GET_CUSTOMER_NAME; CUST_ACCOUNT_NUMBER is selected as a literal NULL and is therefore never populated.

The escalated document reference is central to this view. REF_DOC_NUMBER maps to REFS_B.OBJECT_NAME, and REF_DOC_ID maps to REFS_B.OBJECT_ID. This is the column most relevant to the originating search term "ref_doc_number": it returns the name of the object against which the escalation reference was recorded. The associated OBJECT_CODE, NAME, OBJECT_PARAMETERS, and OBJECT_FUNCTION columns are also exposed, along with RESOURCE_ID and RESOURCE_TYPE, which mirror the owner identifiers for UWQ resource routing. A requester name column is produced by JTF_EC_UTIL.GET_REQUESTER_NAME but is not aliased in the view text.

Common Use Cases and Queries

The principal use case is populating UWQ escalation lists for a given resource, and reporting on which open tasks are escalated and to what level. A typical query filters by owner and orders by the planned end date:

  • SELECT task_number, description, escalation_level_name, ref_doc_number, planned_end_date FROM jtf_esc_uwq_v WHERE resource_id = :owner_id ORDER BY planned_end_date;
  • SELECT escalation_level_name, COUNT(*) FROM jtf_esc_uwq_v GROUP BY escalation_level_name;
  • SELECT task_number, ref_doc_number, customer_name FROM jtf_esc_uwq_v WHERE ref_doc_number LIKE :pattern;
  • SELECT object_code, object_function, COUNT(*) FROM jtf_esc_uwq_v GROUP BY object_code, object_function;

Because the view applies function calls per row, queries returning large volumes should be filtered aggressively on resource or escalation level. Users requiring closed or deleted escalations must query the underlying JTF_TASKS_B tables directly, as the view cannot return them.