Search Results assignment_status

Overview

CSF_CT_TASK_ASSIGNMENTS is a VALID Oracle Application Object Library view owned by the APPS schema within the CSF (Field Service) product family. It serves as the base view for the "Task Assignments" block in the Control Tower, Oracle EBS Field Service's centralized dispatch and monitoring console. Rather than storing data itself, the view projects a filtered, enriched perspective over the task assignment records that drive field service scheduling and execution.

The defining characteristic of this view is its strict filter on the ASSIGNEE_ROLE column. The WHERE clause restricts output to rows where A.ASSIGNEE_ROLE = 'ASSIGNEE', meaning the view deliberately excludes other role-based assignment rows (such as owner or reviewer roles) that share the same underlying assignment table. This makes it the authoritative source for identifying the resource actually dispatched to perform a task. A second predicate further qualifies the result set: a row qualifies only if it has an actual start or end date, or if its associated status is not flagged as cancelled. The view therefore presents live, materially progressed assignments rather than purely planned or cancelled records, and is commonly consumed by Control Tower UI regions, OAF pages, and downstream reporting or integration extracts.

Underlying Base Objects

The view is defined over four documented referenced objects, as recorded in the ETRM 12.2.2 metadata:

• JTF_TASK_ASSIGNMENTS (SYNONYM) — the primary driving table, aliased A. All persistent assignment attributes, including RESOURCE_ID, ASSIGNEE_ROLE, and the actual/scheduled effort and travel measures, originate here.
• JTF_TASK_STATUSES_VL (VIEW) — aliased S, joined on S.TASK_STATUS_ID = A.ASSIGNMENT_STATUS_ID. This supplies the translated assignment status name and the CANCELLED_FLAG used in the view's filter.
• CSF_UTIL_PVT (PACKAGE) — invoked as CSF_UTIL_PVT.GET_OBJECT_NAME(A.RESOURCE_TYPE_CODE, A.RESOURCE_ID) to resolve the display name of the assigned resource.
• CSF_RESOURCE_PUB (PACKAGE) — invoked as CSF_RESOURCE_PUB.GET_RESOURCE_TYPE_NAME(A.RESOURCE_TYPE_CODE) to translate the resource type code into a readable type name.

Because two of the referenced objects are PL/SQL packages, the view performs function calls at query time, which has performance implications discussed below.

Key Columns

• TASK_ASSIGNMENT_ID — primary key of the underlying assignment row; ROW_ID exposes the physical ROWID.
• TASK_ID — foreign key linking the assignment to its parent task.
• ASSIGNEE_ROLE — the discriminator column; always 'ASSIGNEE' in this view's output. This is the column most relevant to the user's search.
• RESOURCE_TYPE_CODE / RESOURCE_ID — the assigned resource's type and identifier; RESOURCE_NAME and RESOURCE_TYPE_NAME expose the resolved, human-readable equivalents.
• ASSIGNMENT_STATUS_ID / ASSIGNMENT_STATUS / STATUS_CANCELLED_FLAG — status identifier, translated status name, and cancellation indicator.
• ACTUAL_START_DATE / ACTUAL_END_DATE — realized execution window; central to the view's row filter.
• ACTUAL_EFFORT / ACTUAL_EFFORT_UOM — effort consumed and its unit of measure.
• SCHED_TRAVEL_DISTANCE, SCHED_TRAVEL_DURATION, ACTUAL_TRAVEL_DISTANCE, ACTUAL_TRAVEL_DURATION — planned and actual travel measures with associated UOM columns.
• SCHEDULE_FLAG, SHOW_ON_CALENDAR, CATEGORY_ID, RESOURCE_TERRITORY_ID, SHIFT_CONSTRUCT_ID, OBJECT_VERSION_NUMBER — scheduling, categorization, territory, and concurrency control attributes.

Common Use Cases and Queries

Typical scenarios include Control Tower dispatch grids, field technician workload analysis, and travel-versus-effort reporting. Because ASSIGNEE_ROLE is already filtered, queries do not need to repeat that predicate.

Retrieve assignments with resolved resource names for a given task:

• SELECT task_assignment_id, task_id, resource_name, resource_type_name, assignment_status, actual_start_date, actual_end_date FROM csf_ct_task_assignments WHERE task_id = :p_task_id;

Analyze actual effort and travel by resource:

• SELECT resource_id, resource_name, SUM(actual_effort) total_effort, SUM(actual_travel_distance) total_distance FROM csf_ct_task_assignments GROUP BY resource_id, resource_name;

List open (non-cancelled, in-progress) assignments:

• SELECT task_assignment_id, task_id, assignment_status, actual_start_date FROM csf_ct_task_assignments WHERE status_cancelled_flag <> 'Y';

Performance note: the embedded calls to CSF_UTIL_PVT.GET_OBJECT_NAME and CSF_RESOURCE_PUB.GET_RESOURCE_TYPE_NAME execute per row, so restricting the result set with selective predicates on TASK_ID, ASSIGNMENT_STATUS_ID, or date ranges is advisable before consuming the derived name columns.