VIEW: APPS.CSR_TRIP_TASKS_V

Source

View Text - Preformatted

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 , DECODE( loc.geometry_status_code , 'GOOD', csf_locus_pub.get_locus_segmentid(loc.geometry) , 'NOEXACTMATCH', csf_locus_pub.get_locus_segmentid(loc.geometry) , 'MULTIMATCH', csf_locus_pub.get_locus_segmentid(loc.geometry) , -9999 ) locus_segmentid , DECODE(loc.geometry_status_code , 'GOOD', csf_locus_pub.get_locus_side(loc.geometry) ,'NOEXACTMATCH', csf_locus_pub.get_locus_side(loc.geometry) , 'MULTIMATCH', csf_locus_pub.get_locus_side(loc.geometry) , -9999) locus_side , DECODE(loc.geometry_status_code , 'GOOD', csf_locus_pub.get_locus_spot(loc.geometry) , 'NOEXACTMATCH', csf_locus_pub.get_locus_spot(loc.geometry) , 'MULTIMATCH', csf_locus_pub.get_locus_spot(loc.geometry) , -9999) locus_spot , DECODE(loc.geometry_status_code , 'GOOD', csf_locus_pub.should_call_lf(loc.geometry) , 'NOEXACTMATCH', csf_locus_pub.should_call_lf(loc.geometry) , 'MULTIMATCH', csf_locus_pub.should_call_lf(loc.geometry) , 'T') should_call_lf , DECODE(loc.geometry_status_code , 'GOOD', csf_locus_pub.get_locus_lat(loc.geometry) , 'NOEXACTMATCH', csf_locus_pub.get_locus_lat(loc.geometry) , 'MULTIMATCH', csf_locus_pub.get_locus_lat(loc.geometry) , -9999) locus_lat , DECODE(loc.geometry_status_code, 'GOOD' , csf_locus_pub.get_locus_lon(loc.geometry) , 'NOEXACTMATCH', csf_locus_pub.get_locus_lon(loc.geometry) , 'MULTIMATCH',csf_locus_pub.get_locus_lon(loc.geometry) , -9999) 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 , oc.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 , (SELECT 'Y' FROM jtf_task_depends WHERE task_id = t.task_id AND ROWNUM = 1) has_parent_dependency , (SELECT 'Y' FROM jtf_task_depends WHERE dependent_on_task_id = t.task_id AND ROWNUM = 1) has_child_dependency , (SELECT 'Y' FROM csp_requirement_headers crh WHERE crh.task_id = t.task_id AND ROWNUM = 1) spares_required_flag , csf_util_pvt.convert_to_minutes(ta.actual_effort, ta.actual_effort_uom) actual_effort , CASE t.source_object_type_code WHEN 'SR' THEN 'N' ELSE 'Y' END personal_task_flag , csf_util_pvt.convert_to_minutes(ta.actual_travel_duration, ta.actual_travel_duration_uom) actual_travel_duration , ta.actual_travel_distance actual_travel_distance , csf_locus_pub.get_locus_srid(loc.geometry) locus_srid , tp.importance_level task_priority , loc.country country_code , csr_scheduler_pvt.get_third_party_res_role(ta.resource_id, ta.resource_type_code) third_party_role 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 , csf_access_hours_b ah , cac_sr_object_capacity oc , jtf_task_priorities_b tp 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 loc.location_id (+) = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id) and t.task_id = ah.task_id (+) AND ta.resource_id = oc.object_id AND ta.resource_type_code = oc.object_type AND ( (ta.object_capacity_id IS NOT NULL AND ta.object_capacity_id = oc.object_capacity_id) OR (ta.object_capacity_id IS NULL AND ta.booking_start_date < oc.end_date_time AND ta.booking_end_date > oc.start_date_time) ) 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) ) ) and t.task_priority_id = tp.task_priority_id

View Text - HTML Formatted

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
, DECODE( LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.GET_LOCUS_SEGMENTID(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SEGMENTID(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SEGMENTID(LOC.GEOMETRY)
, -9999 ) LOCUS_SEGMENTID
, DECODE(LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.GET_LOCUS_SIDE(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SIDE(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SIDE(LOC.GEOMETRY)
, -9999) LOCUS_SIDE
, DECODE(LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.GET_LOCUS_SPOT(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SPOT(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_SPOT(LOC.GEOMETRY)
, -9999) LOCUS_SPOT
, DECODE(LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.SHOULD_CALL_LF(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.SHOULD_CALL_LF(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.SHOULD_CALL_LF(LOC.GEOMETRY)
, 'T') SHOULD_CALL_LF
, DECODE(LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.GET_LOCUS_LAT(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_LAT(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_LAT(LOC.GEOMETRY)
, -9999) LOCUS_LAT
, DECODE(LOC.GEOMETRY_STATUS_CODE
, 'GOOD'
, CSF_LOCUS_PUB.GET_LOCUS_LON(LOC.GEOMETRY)
, 'NOEXACTMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_LON(LOC.GEOMETRY)
, 'MULTIMATCH'
, CSF_LOCUS_PUB.GET_LOCUS_LON(LOC.GEOMETRY)
, -9999) 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
, OC.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
, (SELECT 'Y'
FROM JTF_TASK_DEPENDS
WHERE TASK_ID = T.TASK_ID
AND ROWNUM = 1) HAS_PARENT_DEPENDENCY
, (SELECT 'Y'
FROM JTF_TASK_DEPENDS
WHERE DEPENDENT_ON_TASK_ID = T.TASK_ID
AND ROWNUM = 1) HAS_CHILD_DEPENDENCY
, (SELECT 'Y'
FROM CSP_REQUIREMENT_HEADERS CRH
WHERE CRH.TASK_ID = T.TASK_ID
AND ROWNUM = 1) SPARES_REQUIRED_FLAG
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(TA.ACTUAL_EFFORT
, TA.ACTUAL_EFFORT_UOM) ACTUAL_EFFORT
, CASE T.SOURCE_OBJECT_TYPE_CODE WHEN 'SR' THEN 'N' ELSE 'Y' END PERSONAL_TASK_FLAG
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(TA.ACTUAL_TRAVEL_DURATION
, TA.ACTUAL_TRAVEL_DURATION_UOM) ACTUAL_TRAVEL_DURATION
, TA.ACTUAL_TRAVEL_DISTANCE ACTUAL_TRAVEL_DISTANCE
, CSF_LOCUS_PUB.GET_LOCUS_SRID(LOC.GEOMETRY) LOCUS_SRID
, TP.IMPORTANCE_LEVEL TASK_PRIORITY
, LOC.COUNTRY COUNTRY_CODE
, CSR_SCHEDULER_PVT.GET_THIRD_PARTY_RES_ROLE(TA.RESOURCE_ID
, TA.RESOURCE_TYPE_CODE) THIRD_PARTY_ROLE
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
, CSF_ACCESS_HOURS_B AH
, CAC_SR_OBJECT_CAPACITY OC
, JTF_TASK_PRIORITIES_B TP
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 LOC.LOCATION_ID (+) = CSF_TASKS_PUB.GET_TASK_LOCATION_ID(T.TASK_ID
, T.ADDRESS_ID
, T.LOCATION_ID)
AND T.TASK_ID = AH.TASK_ID (+)
AND TA.RESOURCE_ID = OC.OBJECT_ID
AND TA.RESOURCE_TYPE_CODE = OC.OBJECT_TYPE
AND ( (TA.OBJECT_CAPACITY_ID IS NOT NULL
AND TA.OBJECT_CAPACITY_ID = OC.OBJECT_CAPACITY_ID) OR (TA.OBJECT_CAPACITY_ID IS NULL
AND TA.BOOKING_START_DATE < OC.END_DATE_TIME
AND TA.BOOKING_END_DATE > OC.START_DATE_TIME) )
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) ) )
AND T.TASK_PRIORITY_ID = TP.TASK_PRIORITY_ID