View: CSR_TRIPS_V

View Text

SELECT T.ACTUAL_END_DATE
, T.ACTUAL_START_DATE
, CSR_SCHEDULER_PVT.CONVERT_TO_MINUTES(T.PLANNED_EFFORT
, T.PLANNED_EFFORT_UOM)
, CSR_SCHEDULER_PVT.GET_UOM_MINUTES
, TA.SCHED_TRAVEL_DISTANCE
, CSR_SCHEDULER_PVT.CONVERT_TO_MINUTES(TA.SCHED_TRAVEL_DURATION
, TA.SCHED_TRAVEL_DURATION_UOM)
, CSR_SCHEDULER_PVT.GET_UOM_MINUTES
, T.SCHEDULED_END_DATE
, T.SCHEDULED_START_DATE
, TA.TASK_ASSIGNMENT_ID TA_TASK_ASSIGNMENT_ID
, T.TASK_ID
, T.TASK_TYPE_ID
, T.PLANNED_END_DATE
, T.PLANNED_START_DATE
, NVL(T.BOUND_MODE_CODE
, 'BTS')
, T.SOFT_BOUND_FLAG
, T.OBJECT_VERSION_NUMBER
, T.TASK_STATUS_ID
, CSR_SCHEDULER_PVT.GET_LOCUS_SEGMENTID (LOC.GEOMETRY)
, CSR_SCHEDULER_PVT.GET_LOCUS_SIDE (LOC.GEOMETRY)
, CSR_SCHEDULER_PVT.GET_LOCUS_SPOT (LOC.GEOMETRY)
, CSR_SCHEDULER_PVT.LOCATION_FINDER_USED (LOC.GEOMETRY)
, LOC.LOCATION_ID
, CSR_SCHEDULER_PVT.GET_TIMEZONE (T.ADDRESS_ID)
, TA.OBJECT_VERSION_NUMBER
, NVL(TS.SCHEDULABLE_FLAG
, 'N')
, NVL(TSA.SCHEDULABLE_FLAG
, 'N')
, T.CREATION_DATE
, (SELECT COUNT(*)
FROM JTF_TASK_ASSIGNMENTS TA2
, JTF_TASK_STATUSES_B TSA2
WHERE TA2.ASSIGNMENT_STATUS_ID = TSA2.TASK_STATUS_ID
AND TA2.TASK_ID = T.TASK_ID
AND NVL(TSA2.CANCELLED_FLAG
, 'N') = 'N')
, TA.ASSIGNMENT_STATUS_ID
, TA.RESOURCE_ID
, TA.RESOURCE_TYPE_CODE
, NVL(TT.SCHEDULE_FLAG
, 'N')
, CSR_SCHEDULER_PVT.GET_LOCUS_LAT (LOC.GEOMETRY)
, CSR_SCHEDULER_PVT.GET_LOCUS_LON (LOC.GEOMETRY)
, TA.SHIFT_CONSTRUCT_ID
FROM JTF_TASK_ASSIGNMENTS TA
, JTF_TASKS_B T
, JTF_TASK_TYPES_B TT
, JTF_TASK_STATUSES_B TS
, HZ_PARTY_SITES PAR
, HZ_LOCATIONS LOC
, JTF_TASK_STATUSES_B TSA
WHERE TA.TASK_ID = T.TASK_ID
AND T.TASK_TYPE_ID = TT.TASK_TYPE_ID
AND TA.ASSIGNMENT_STATUS_ID = TSA.TASK_STATUS_ID
AND T.TASK_STATUS_ID = TS.TASK_STATUS_ID
AND T.SCHEDULED_END_DATE >= T.SCHEDULED_START_DATE
AND NVL(T.DELETED_FLAG
, 'N') = 'N'
AND ( NVL(TA.ACTUAL_START_DATE
, TA.ACTUAL_END_DATE) IS NOT NULL OR ( NVL(TSA.CANCELLED_FLAG
, 'N') = 'N'
AND NVL(TSA.COMPLETED_FLAG
, 'N') = 'N'
AND NVL(TSA.CLOSED_FLAG
, 'N') = 'N'))
AND T.ADDRESS_ID=PAR.PARTY_SITE_ID (+)
AND PAR.LOCATION_ID=LOC.LOCATION_ID (+)

Columns