Search Results csr_trips_v

Overview

CSR_TRIPS_V is a reporting view in the Oracle E-Business Suite CSR (Customer Service Representative) Scheduler module. It exposes scheduled trip and task-assignment data by joining task, assignment, task-type, task-status, party-site, and location entities, and by applying several CSR_SCHEDULER_PVT helper functions to derive scheduling-relevant attributes. The view is designed to present a flattened, scheduler-ready representation of trips: planned versus actual versus scheduled timelines, travel distance and duration (normalized to minutes), geographic locus information, timezone, and schedulability flags. It is not a transactional entity; rather, it is a query surface used by scheduling logic and reporting to determine which tasks are assignable, in progress, completed, or cancelled.

Although the ETRM metadata states the view is "not implemented in this database," the view text is documented, indicating the definition is available for reference even where the object is absent from the current instance. The user's search term "jtf_task_statuses_b" is directly relevant, since that table appears three times in the view definition (aliased TS, TSA, and TSA2), making it central to how trip status and assignment status are resolved.

Underlying Base Objects

The view is defined over the following base tables:

• JTF_TASK_ASSIGNMENTS (TA) — assignment-level scheduling, resource, travel, and shift-construct data.
• JTF_TASKS_B (T) — the task header, providing planned/actual/scheduled dates, bound mode, soft-bound flag, task type, status, and object version number.
• JTF_TASK_TYPES_B (TT) — task type attributes, notably the schedule flag.
• JTF_TASK_STATUSES_B (TS, TSA, TSA2) — status definitions and flags including schedulable, cancelled, and completed indicators.
• HZ_PARTY_SITES (PAR) and HZ_LOCATIONS (LOC) — party-site and location geometry used for locus derivation.

The joins connect assignments to tasks, tasks to types and statuses, assignments to their own status record (TSA), and tasks to location data. A scalar subquery counts non-cancelled assignments per task using JTF_TASK_ASSIGNMENTS and JTF_TASK_STATUSES_B.

Key Columns

• Task and assignment identifiers — TASK_ID, TASK_TYPE_ID, TASK_ASSIGNMENT_ID (aliased TA_TASK_ASSIGNMENT_ID).
• Dates — ACTUAL_START_DATE, ACTUAL_END_DATE, SCHEDULED_START_DATE, SCHEDULED_END_DATE, PLANNED_START_DATE, PLANNED_END_DATE.
• Effort and travel — CSR_SCHEDULER_PVT.CONVERT_TO_MINUTES(PLANNED_EFFORT, PLANNED_EFFORT_UOM), SCHED_TRAVEL_DISTANCE, and converted SCHED_TRAVEL_DURATION.
• Status and flags — TASK_STATUS_ID, ASSIGNMENT_STATUS_ID, BOUND_MODE_CODE (defaulting to 'BTS'), SOFT_BOUND_FLAG, and NVL-derived SCHEDULABLE_FLAG from both task and assignment statuses.
• Location/geometry — LOCATION_ID plus GET_LOCUS_SEGMENTID, GET_LOCUS_SIDE, GET_LOCUS_SPOT, GET_LOCUS_LAT, GET_LOCUS_LON, LOCATION_FINDER_USED, and GET_TIMEZONE(ADDRESS_ID).
• Resource — RESOURCE_ID, RESOURCE_TYPE_CODE, SHIFT_CONSTRUCT_ID.

Common Use Cases and Queries

Typical uses include identifying schedulable tasks, reconciling planned versus actual effort, and reporting travel burden. A representative query:

SELECT task_id, scheduled_start_date, scheduled_end_date, assignment_status_id, resource_id FROM csr_trips_v WHERE NVL(schedulable_flag,'N') = 'Y' AND scheduled_start_date >= SYSDATE ORDER BY scheduled_start_date;

Another common pattern filters by soft-bound tasks or by non-cancelled assignments, leveraging the embedded status logic:

SELECT task_id, task_assignment_id, NVL(bound_mode_code,'BTS') bound_mode, soft_bound_flag FROM csr_trips_v WHERE soft_bound_flag = 'Y';

Because status filtering depends on JTF_TASK_STATUSES_B aliases, any tuning or extension should preserve those join conditions to avoid inflating row counts through cancelled or completed records.