Search Results jtf_task_statuses_vl

Overview

CSR_TASK_ASSIGNMENTS_V is a reporting and integration view within the CSR (Scheduler) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes a scheduler-oriented projection of task assignment data, joining assignment records to their associated task statuses and filtering out statuses that are closed, completed, or cancelled. In this way the view presents only "open" or otherwise actionable assignments, which is the natural dataset for resource scheduling, dispatch, and field service workload analysis.

The view is documented in the ETRM repository as a "View on task assignments." It is not implemented as a stored database object in every environment; the metadata records "Not implemented in this database," meaning the definition is a documented template rather than a guaranteed physical object. Consumers should therefore verify its presence in their specific instance before binding reports, concurrent programs, or integration interfaces to it. The view is owned by the Scheduler product context and is intended for read-only querying; no DML support is provided.

Underlying Base Objects

The view is defined over two underlying sources:

• JTF_TASK_ASSIGNMENTS (aliased TA) — the base assignment table holding task assignment identifiers, travel metrics, object version numbers, resource identifiers, resource type codes, and assignment status references.
• JTF_TASK_STATUSES_VL (aliased ST) — the translated task status lookup, providing the status flags used to filter out non-actionable assignments.

The join predicate is TA.ASSIGNMENT_STATUS_ID = ST.TASK_STATUS_ID. Additional predicates retain only rows where CLOSED_FLAG, COMPLETED_FLAG, and CANCELLED_FLAG are each either 'N' or null. The view also invokes two PL/SQL helpers from CSR_SCHEDULER_PVT: CONVERT_TO_MINUTES, which normalizes the scheduled travel duration against its unit of measure, and GET_UOM_MINUTES, which returns the minute conversion factor for the UOM. Note that the documented ETRM metadata lists no referenced base objects, so the dependencies above are derived from the published view text.

Key Columns

• TASK_ASSIGNMENT_ID — primary identifier of the assignment record.
• TASK_ID — foreign key to the parent task.
• SCHED_TRAVEL_DISTANCE — scheduled travel distance for the assignment.
• SCHED_TRAVEL_DURATION — travel duration converted to minutes via CONVERT_TO_MINUTES.
• SCHED_TRAVEL_DURATION_UOM — original unit of measure for the duration.
• OBJECT_VERSION_NUMBER — optimistic locking / row versioning column.
• SCHEDULABLE_FLAG — derived via NVL(ST.SCHEDULABLE_FLAG,'N'), indicating whether the status permits scheduling.
• RESOURCE_ID — identifier of the assigned resource.
• RESOURCE_TYPE_CODE — the type of resource assigned (the column the user searched for); this distinguishes resource categories such as person, party, or organization in the CSR scheduling model.
• ASSIGNMENT_STATUS_ID — status reference resolved against JTF_TASK_STATUSES_VL.

Common Use Cases and Queries

Typical uses include resource workload reports, open-assignment dashboards, travel-time analysis, and integration extracts feeding external dispatch systems. Because the view pre-filters closed, completed, and cancelled statuses, ad-hoc queries can focus on active scheduling data without reimplementing the status logic.

Example — list open assignments with resource type:

SELECT task_assignment_id,
       task_id,
       resource_id,
       resource_type_code,
       sched_travel_distance,
       sched_travel_duration,
       schedulable_flag
  FROM csr_task_assignments_v
 WHERE resource_type_code = :p_resource_type_code
 ORDER BY task_id;

Example — aggregate scheduled travel minutes by resource:

SELECT resource_id,
       SUM(sched_travel_duration) AS total_travel_minutes
  FROM csr_task_assignments_v
 GROUP BY resource_id;

Queries should always verify the view's existence in the target instance, since the ETRM metadata states it may not be implemented in a given database.