VIEW: APPS.CSR_PLAN_TASKS_V

Source

View Text - Preformatted

SELECT ta.actual_end_date actual_end_date , ta.actual_start_date actual_start_date , csf_util_pvt.convert_to_minutes(t.planned_effort, t.planned_effort_uom) planned_effort , csf_util_pvt.get_uom_minutes planned_effort_uom , t.scheduled_end_date scheduled_end_date , t.scheduled_start_date scheduled_start_date , t.task_id task_id , t.task_type_id task_type_id , t.planned_end_date planned_end_date , t.planned_start_date planned_start_date , nvl( t.bound_mode_code, 'BTS') bound_mode_code , t.soft_bound_flag soft_bound_flag , t.creation_date creation_date , t.object_version_number object_version_number , nvl(ts.schedulable_flag,'N') schedulable_flag , t.task_status_id task_status_id , 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 , loc.location_id location_id , csr_scheduler_pvt.get_timezone (t.address_id) timezone_id , ta.task_assignment_id ta_task_assignment_id , ta.sched_travel_distance ta_sched_travel_distance , csf_util_pvt.convert_to_minutes(ta.sched_travel_duration, ta.sched_travel_duration_uom) sched_travel_duration , csf_util_pvt.get_uom_minutes sched_travel_duration_uom , ta.object_version_number ta_object_version_number , ta.schedulable_flag ta_schedulable_flag , ta.resource_id ta_resource_id , ta.resource_type_code ta_resource_type_code , ta.assignment_status_id ta_assignment_status_id , nvl( ty.schedule_flag,'N') ty_schedule_flag , csf_locus_pub.get_locus_lat (loc.geometry) locus_lat , csf_locus_pub.get_locus_lon (loc.geometry) locus_lon , (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 object_capacity_id , t.parent_task_id parent_task_id , t.task_confirmation_status task_confirmation_status , t.task_confirmation_counter task_confirmation_counter , ah.accesshour_required access_hours_required , ah.after_hours_flag 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 , 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_locus_pub.get_locus_srid (loc.geometry) locus_srid from jtf_tasks_b t , jtf_task_statuses_b ts , (select a.task_assignment_id task_assignment_id , a.task_id task_id , a.object_version_number object_version_number , a.shift_construct_id shift_construct_id , a.object_capacity_id object_capacity_id , a.actual_start_date actual_start_date , a.actual_end_date actual_end_date , a.sched_travel_distance sched_travel_distance , a.sched_travel_duration sched_travel_duration , a.sched_travel_duration_uom sched_travel_duration_uom , a.resource_id resource_id , a.resource_type_code resource_type_code , s.task_status_id assignment_status_id , nvl(s.schedulable_flag,'N') schedulable_flag , a.actual_effort actual_effort , a.actual_effort_uom actual_effort_uom from jtf_task_assignments a , jtf_task_statuses_b s where a.assignment_status_id = s.task_status_id and s.usage = 'TASK' and (s.closed_flag = 'N' or s.closed_flag is null) and (s.completed_flag = 'N' or s.completed_flag is null) and (s.cancelled_flag = 'N' or s.cancelled_flag is null) and a.assignee_role = 'ASSIGNEE' ) ta , jtf_task_types_b ty , hz_locations loc , csf_access_hours_b ah WHERE t.task_status_id = ts.task_status_id and t.task_id = ta.task_id (+) and t.task_type_id = ty.task_type_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 ts.usage = 'TASK'

View Text - HTML Formatted

SELECT TA.ACTUAL_END_DATE ACTUAL_END_DATE
, TA.ACTUAL_START_DATE ACTUAL_START_DATE
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(T.PLANNED_EFFORT
, T.PLANNED_EFFORT_UOM) PLANNED_EFFORT
, CSF_UTIL_PVT.GET_UOM_MINUTES PLANNED_EFFORT_UOM
, T.SCHEDULED_END_DATE SCHEDULED_END_DATE
, T.SCHEDULED_START_DATE SCHEDULED_START_DATE
, T.TASK_ID TASK_ID
, T.TASK_TYPE_ID TASK_TYPE_ID
, T.PLANNED_END_DATE PLANNED_END_DATE
, T.PLANNED_START_DATE PLANNED_START_DATE
, NVL( T.BOUND_MODE_CODE
, 'BTS') BOUND_MODE_CODE
, T.SOFT_BOUND_FLAG SOFT_BOUND_FLAG
, T.CREATION_DATE CREATION_DATE
, T.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, NVL(TS.SCHEDULABLE_FLAG
, 'N') SCHEDULABLE_FLAG
, T.TASK_STATUS_ID TASK_STATUS_ID
, 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
, LOC.LOCATION_ID LOCATION_ID
, CSR_SCHEDULER_PVT.GET_TIMEZONE (T.ADDRESS_ID) TIMEZONE_ID
, TA.TASK_ASSIGNMENT_ID TA_TASK_ASSIGNMENT_ID
, TA.SCHED_TRAVEL_DISTANCE TA_SCHED_TRAVEL_DISTANCE
, CSF_UTIL_PVT.CONVERT_TO_MINUTES(TA.SCHED_TRAVEL_DURATION
, TA.SCHED_TRAVEL_DURATION_UOM) SCHED_TRAVEL_DURATION
, CSF_UTIL_PVT.GET_UOM_MINUTES SCHED_TRAVEL_DURATION_UOM
, TA.OBJECT_VERSION_NUMBER TA_OBJECT_VERSION_NUMBER
, TA.SCHEDULABLE_FLAG TA_SCHEDULABLE_FLAG
, TA.RESOURCE_ID TA_RESOURCE_ID
, TA.RESOURCE_TYPE_CODE TA_RESOURCE_TYPE_CODE
, TA.ASSIGNMENT_STATUS_ID TA_ASSIGNMENT_STATUS_ID
, NVL( TY.SCHEDULE_FLAG
, 'N') TY_SCHEDULE_FLAG
, CSF_LOCUS_PUB.GET_LOCUS_LAT (LOC.GEOMETRY) LOCUS_LAT
, CSF_LOCUS_PUB.GET_LOCUS_LON (LOC.GEOMETRY) LOCUS_LON
, (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 OBJECT_CAPACITY_ID
, T.PARENT_TASK_ID PARENT_TASK_ID
, T.TASK_CONFIRMATION_STATUS TASK_CONFIRMATION_STATUS
, T.TASK_CONFIRMATION_COUNTER TASK_CONFIRMATION_COUNTER
, AH.ACCESSHOUR_REQUIRED ACCESS_HOURS_REQUIRED
, AH.AFTER_HOURS_FLAG 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
, 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_LOCUS_PUB.GET_LOCUS_SRID (LOC.GEOMETRY) LOCUS_SRID
FROM JTF_TASKS_B T
, JTF_TASK_STATUSES_B TS
, (SELECT A.TASK_ASSIGNMENT_ID TASK_ASSIGNMENT_ID
, A.TASK_ID TASK_ID
, A.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, A.SHIFT_CONSTRUCT_ID SHIFT_CONSTRUCT_ID
, A.OBJECT_CAPACITY_ID OBJECT_CAPACITY_ID
, A.ACTUAL_START_DATE ACTUAL_START_DATE
, A.ACTUAL_END_DATE ACTUAL_END_DATE
, A.SCHED_TRAVEL_DISTANCE SCHED_TRAVEL_DISTANCE
, A.SCHED_TRAVEL_DURATION SCHED_TRAVEL_DURATION
, A.SCHED_TRAVEL_DURATION_UOM SCHED_TRAVEL_DURATION_UOM
, A.RESOURCE_ID RESOURCE_ID
, A.RESOURCE_TYPE_CODE RESOURCE_TYPE_CODE
, S.TASK_STATUS_ID ASSIGNMENT_STATUS_ID
, NVL(S.SCHEDULABLE_FLAG
, 'N') SCHEDULABLE_FLAG
, A.ACTUAL_EFFORT ACTUAL_EFFORT
, A.ACTUAL_EFFORT_UOM ACTUAL_EFFORT_UOM
FROM JTF_TASK_ASSIGNMENTS A
, JTF_TASK_STATUSES_B S
WHERE A.ASSIGNMENT_STATUS_ID = S.TASK_STATUS_ID
AND S.USAGE = 'TASK'
AND (S.CLOSED_FLAG = 'N' OR S.CLOSED_FLAG IS NULL)
AND (S.COMPLETED_FLAG = 'N' OR S.COMPLETED_FLAG IS NULL)
AND (S.CANCELLED_FLAG = 'N' OR S.CANCELLED_FLAG IS NULL)
AND A.ASSIGNEE_ROLE = 'ASSIGNEE' ) TA
, JTF_TASK_TYPES_B TY
, HZ_LOCATIONS LOC
, CSF_ACCESS_HOURS_B AH
WHERE T.TASK_STATUS_ID = TS.TASK_STATUS_ID
AND T.TASK_ID = TA.TASK_ID (+)
AND T.TASK_TYPE_ID = TY.TASK_TYPE_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 TS.USAGE = 'TASK'