View: CSR_TRIP_TASKS_V

Product:	CSR - Scheduler
Description:	View selecting all tasks in all trips that scheduler can handle
Implementation/DBA Data:	APPS.CSR_TRIP_TASKS_V

View Text

SELECT TA.ACTUAL_START_DATE ACTUAL_START_DATE
, TA.ACTUAL_END_DATE ACTUAL_END_DATE
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(T.PLANNED_EFFORT
, T.PLANNED_EFFORT_UOM) PLANNED_EFFORT
, CSF_UTIL_PVT.GET_UOM_MINUTES SCHED_TRAVEL_DURATION_UOM
, CSF_UTIL_PVT.GET_UOM_MINUTES PLANNED_EFFORT_UOM
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(TA.SCHED_TRAVEL_DURATION
, TA.SCHED_TRAVEL_DURATION_UOM) TA_SCHED_TRAVEL_DURATION
, TA.SCHED_TRAVEL_DISTANCE TA_SCHED_TRAVEL_DISTANCE
, T.SCHEDULED_START_DATE SCHEDULED_START_DATE
, T.SCHEDULED_END_DATE SCHEDULED_END_DATE
, TA.TASK_ASSIGNMENT_ID TA_TASK_ASSIGNMENT_ID
, T.TASK_ID TASK_ID
, T.TASK_TYPE_ID TASK_TYPE_ID
, T.PLANNED_START_DATE PLANNED_START_DATE
, T.PLANNED_END_DATE PLANNED_END_DATE
, T.SOFT_BOUND_FLAG SOFT_BOUND_FLAG
, T.TASK_STATUS_ID TASK_STATUS_ID
, CSR_SCHEDULER_PVT.GET_TIMEZONE (T.ADDRESS_ID) TIMEZONE_ID
, TA.OBJECT_VERSION_NUMBER TA_OBJECT_VERSION_NUMBER
, T.CREATION_DATE CREATION_DATE
, TA.ASSIGNMENT_STATUS_ID TA_ASSIGNMENT_STATUS_ID
, TA.RESOURCE_ID TA_RESOURCE_ID
, TA.RESOURCE_TYPE_CODE TA_RESOURCE_TYPE_CODE
, TA.SHIFT_CONSTRUCT_ID TA_SHIFT_CONSTRUCT_ID
, T.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, NVL(TT.SCHEDULE_FLAG
, 'N') TY_SCHEDULE_FLAG
, NVL(TS.SCHEDULABLE_FLAG
, 'N') SCHEDULABLE_FLAG
, NVL(TSA.SCHEDULABLE_FLAG
, 'N') TA_SCHEDULABLE_FLAG
, (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'
AND TA2.ASSIGNEE_ROLE = 'ASSIGNEE') ASSIGNMENT_COUNT
, CSF_LOCUS_PUB.GET_LOCUS_SEGMENTID (LOC.GEOMETRY) LOCUS_SEGMENTID
, CSF_LOCUS_PUB.GET_LOCUS_SIDE (LOC.GEOMETRY) LOCUS_SIDE
, CSF_LOCUS_PUB.GET_LOCUS_SPOT (LOC.GEOMETRY) LOCUS_SPOT
, CSF_LOCUS_PUB.SHOULD_CALL_LF (LOC.GEOMETRY) SHOULD_CALL_LF
, CSF_LOCUS_PUB.GET_LOCUS_LAT (LOC.GEOMETRY) LOCUS_LAT
, CSF_LOCUS_PUB.GET_LOCUS_LON (LOC.GEOMETRY) LOCUS_LON
, LOC.LOCATION_ID LOCATION_ID
, NVL(T.BOUND_MODE_CODE
, 'BTS') BOUND_MODE_CODE
, (SELECT MAX(NVL(CRL.ARRIVAL_DATE
, CRL.CREATION_DATE))
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
WHERE CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND CRH.TASK_ID = TA.TASK_ID
AND CRH.TASK_ASSIGNMENT_ID = TA.TASK_ASSIGNMENT_ID ) AVAILABILITY_DATE
, TA.OBJECT_CAPACITY_ID TRIP_ID
, T.PARENT_TASK_ID PARENT_TASK_ID
, T.TASK_CONFIRMATION_STATUS TASK_CONFIRMATION_STATUS
, T.TASK_CONFIRMATION_COUNTER TASK_CONFIRMATION_COUNTER
, NVL(AH.ACCESSHOUR_REQUIRED
, 'N') ACCESS_HOURS_REQUIRED
, NVL(AH.AFTER_HOURS_FLAG
, 'N') AFTER_HOURS_FLAG
, AH.MONDAY_FIRST_START MONDAY_FIRST_START
, AH.MONDAY_FIRST_END MONDAY_FIRST_END
, AH.MONDAY_SECOND_START MONDAY_SECOND_START
, AH.MONDAY_SECOND_END MONDAY_SECOND_END
, AH.TUESDAY_FIRST_START TUESDAY_FIRST_START
, AH.TUESDAY_FIRST_END TUESDAY_FIRST_END
, AH.TUESDAY_SECOND_START TUESDAY_SECOND_START
, AH.TUESDAY_SECOND_END TUESDAY_SECOND_END
, AH.WEDNESDAY_FIRST_START WEDNESDAY_FIRST_START
, AH.WEDNESDAY_FIRST_END WEDNESDAY_FIRST_END
, AH.WEDNESDAY_SECOND_START WEDNESDAY_SECOND_STAR
, AH.WEDNESDAY_SECOND_END WEDNESDAY_SECOND_END
, AH.THURSDAY_FIRST_START THURSDAY_FIRST_START
, AH.THURSDAY_FIRST_END THURSDAY_FIRST_END
, AH.THURSDAY_SECOND_START THURSDAY_SECOND_START
, AH.THURSDAY_SECOND_END THURSDAY_SECOND_END
, AH.FRIDAY_FIRST_START FRIDAY_FIRST_START
, AH.FRIDAY_FIRST_END FRIDAY_FIRST_END
, AH.FRIDAY_SECOND_START FRIDAY_SECOND_START
, AH.FRIDAY_SECOND_END FRIDAY_SECOND_END
, AH.SATURDAY_FIRST_START SATURDAY_FIRST_START
, AH.SATURDAY_FIRST_END SATURDAY_FIRST_END
, AH.SATURDAY_SECOND_START SATURDAY_SECOND_START
, AH.SATURDAY_SECOND_END SATURDAY_SECOND_END
, AH.SUNDAY_FIRST_START SUNDAY_FIRST_START
, AH.SUNDAY_FIRST_END SUNDAY_FIRST_END
, AH.SUNDAY_SECOND_START SUNDAY_SECOND_START
, AH.SUNDAY_SECOND_END SUNDAY_SECOND_END
, T.TASK_SPLIT_FLAG
, OC.STATUS OC_TRIP_STATUS
, NVL(TS.ASSIGNED_FLAG
, 'N') TS_ASSIGNED_FLAG
, NVL(TS.WORKING_FLAG
, 'N') TS_WORKING_FLAG
, NVL(TS.APPROVED_FLAG
, 'N') TS_APPROVED_FLAG
, NVL(TS.COMPLETED_FLAG
, 'N') TS_COMPLETED_FLAG
, NVL(TS.CANCELLED_FLAG
, 'N') TS_CANCELLED_FLAG
, NVL(TS.CLOSED_FLAG
, 'N') TS_CLOSED_FLAG
, NVL(TS.REJECTED_FLAG
, 'N') TS_REJECTED_FLAG
, NVL(TS.ACCEPTED_FLAG
, 'N') TS_ACCEPTED_FLAG
, NVL(TS.ON_HOLD_FLAG
, 'N') TS_ON_HOLD_FLAG
, NVL(TS.DELETE_ALLOWED_FLAG
, 'N') TS_DELETE_ALLOWED_FLAG
, T.TASK_NUMBER TASK_NUMBER
FROM JTF_TASKS_B T
, JTF_TASK_ASSIGNMENTS TA
, HZ_LOCATIONS LOC
, JTF_TASK_TYPES_B TT
, JTF_TASK_STATUSES_B TS
, JTF_TASK_STATUSES_B TSA
, HZ_PARTY_SITES PAR
, CSF_ACCESS_HOURS_B AH
, CAC_SR_OBJECT_CAPACITY OC
WHERE T.TASK_ID = TA.TASK_ID
AND T.TASK_TYPE_ID = TT.TASK_TYPE_ID
AND T.TASK_STATUS_ID = TS.TASK_STATUS_ID
AND TA.ASSIGNMENT_STATUS_ID = TSA.TASK_STATUS_ID
AND T.ADDRESS_ID = PAR.PARTY_SITE_ID (+)
AND PAR.LOCATION_ID = LOC.LOCATION_ID (+)
AND T.TASK_ID = AH.TASK_ID (+)
AND TA.OBJECT_CAPACITY_ID = OC.OBJECT_CAPACITY_ID
AND T.SCHEDULED_END_DATE >= T.SCHEDULED_START_DATE
AND (T.DELETED_FLAG = 'N' OR T.DELETED_FLAG IS NULL)
AND ( NVL(TA.ACTUAL_START_DATE
, TA.ACTUAL_END_DATE) IS NOT NULL OR ( (TSA.CANCELLED_FLAG = 'N' OR TSA.CANCELLED_FLAG IS NULL)
AND (TS.CANCELLED_FLAG = 'N' OR TS.CANCELLED_FLAG IS NULL) ) )

Columns