VIEW: APPS.JTF_AGENDA_TA_V

Source

View Text - Preformatted

SELECT a.rowid row_id , a.task_assignment_id , a.created_by , a.creation_date , a.last_updated_by , a.last_update_date , a.last_update_login , a.task_id , a.resource_type_code , a.resource_id , a.actual_effort , a.actual_effort_uom , a.schedule_flag , a.alarm_type_code , a.alarm_contact , a.sched_travel_distance , a.sched_travel_duration , a.sched_travel_duration_uom , a.actual_travel_distance , a.actual_travel_duration , a.actual_travel_duration_uom , a.actual_start_date , a.actual_end_date , a.palm_flag , a.wince_flag , a.laptop_flag , a.device1_flag , a.device2_flag , a.device3_flag , a.attribute1 , a.attribute2 , a.attribute3 , a.attribute4 , a.attribute5 , a.attribute6 , a.attribute7 , a.attribute8 , a.attribute9 , a.attribute10 , a.attribute11 , a.attribute12 , a.attribute13 , a.attribute14 , a.attribute15 , a.attribute_category , a.object_version_number , a.resource_territory_id , a.assignment_status_id , a.shift_construct_id , t.task_number , t.task_type_id , t.task_status_id , t.task_priority_id , t.owner_type_code , t.owner_id , t.assigned_by_id , t.cust_account_id , t.customer_id , t.address_id , t.planned_start_date , t.planned_end_date , t.scheduled_start_date , t.scheduled_end_date , t.timezone_id , t.source_object_type_code , t.source_object_id , t.source_object_name , t.duration , t.duration_uom , t.planned_effort , t.planned_effort_uom , t.percentage_complete , t.reason_code , t.private_flag , t.publish_flag , t.restrict_closure_flag , t.multi_booked_flag , t.milestone_flag , t.holiday_flag , t.billable_flag , t.bound_mode_code , t.soft_bound_flag , t.workflow_process_id , t.escalation_level , t.notification_flag , t.notification_period , t.notification_period_uom , t.parent_task_id , t.alarm_start , t.alarm_start_uom , t.alarm_on , t.alarm_count , t.alarm_fired_count , t.alarm_interval , t.alarm_interval_uom , t.recurrence_rule_id , t.costs , t.currency_code , t.org_id , jtf_agenda_calculations.get_shift_start ( a.shift_construct_id , a.resource_id , a.resource_type_code , t.scheduled_start_date ) shift_start , jtf_agenda_calculations.get_shift_end ( a.shift_construct_id , a.resource_id , a.resource_type_code , t.scheduled_start_date) shift_end FROM jtf_tasks_b t, jtf_task_assignments a WHERE t.task_id = a.task_id and nvl(t.deleted_flag, 'N') <> 'Y' and t.scheduled_end_date >= t.scheduled_start_date and ( nvl(a.actual_start_date,a.actual_end_date) is not null or ( exists ( select null from jtf_task_statuses_b s where s.task_status_id = t.task_status_id and sysdate between nvl(s.start_date_active,sysdate) and nvl(s.end_date_active,sysdate) and nvl(s.cancelled_flag,'N') <> 'Y' ) and exists ( select null from jtf_task_statuses_b s where s.task_status_id = a.assignment_status_id and sysdate between nvl(s.start_date_active,sysdate) and nvl(s.end_date_active,sysdate) and nvl(s.cancelled_flag,'N') <> 'Y' ) ) )

View Text - HTML Formatted

SELECT A.ROWID ROW_ID
, A.TASK_ASSIGNMENT_ID
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, A.TASK_ID
, A.RESOURCE_TYPE_CODE
, A.RESOURCE_ID
, A.ACTUAL_EFFORT
, A.ACTUAL_EFFORT_UOM
, A.SCHEDULE_FLAG
, A.ALARM_TYPE_CODE
, A.ALARM_CONTACT
, A.SCHED_TRAVEL_DISTANCE
, A.SCHED_TRAVEL_DURATION
, A.SCHED_TRAVEL_DURATION_UOM
, A.ACTUAL_TRAVEL_DISTANCE
, A.ACTUAL_TRAVEL_DURATION
, A.ACTUAL_TRAVEL_DURATION_UOM
, A.ACTUAL_START_DATE
, A.ACTUAL_END_DATE
, A.PALM_FLAG
, A.WINCE_FLAG
, A.LAPTOP_FLAG
, A.DEVICE1_FLAG
, A.DEVICE2_FLAG
, A.DEVICE3_FLAG
, A.ATTRIBUTE1
, A.ATTRIBUTE2
, A.ATTRIBUTE3
, A.ATTRIBUTE4
, A.ATTRIBUTE5
, A.ATTRIBUTE6
, A.ATTRIBUTE7
, A.ATTRIBUTE8
, A.ATTRIBUTE9
, A.ATTRIBUTE10
, A.ATTRIBUTE11
, A.ATTRIBUTE12
, A.ATTRIBUTE13
, A.ATTRIBUTE14
, A.ATTRIBUTE15
, A.ATTRIBUTE_CATEGORY
, A.OBJECT_VERSION_NUMBER
, A.RESOURCE_TERRITORY_ID
, A.ASSIGNMENT_STATUS_ID
, A.SHIFT_CONSTRUCT_ID
, T.TASK_NUMBER
, T.TASK_TYPE_ID
, T.TASK_STATUS_ID
, T.TASK_PRIORITY_ID
, T.OWNER_TYPE_CODE
, T.OWNER_ID
, T.ASSIGNED_BY_ID
, T.CUST_ACCOUNT_ID
, T.CUSTOMER_ID
, T.ADDRESS_ID
, T.PLANNED_START_DATE
, T.PLANNED_END_DATE
, T.SCHEDULED_START_DATE
, T.SCHEDULED_END_DATE
, T.TIMEZONE_ID
, T.SOURCE_OBJECT_TYPE_CODE
, T.SOURCE_OBJECT_ID
, T.SOURCE_OBJECT_NAME
, T.DURATION
, T.DURATION_UOM
, T.PLANNED_EFFORT
, T.PLANNED_EFFORT_UOM
, T.PERCENTAGE_COMPLETE
, T.REASON_CODE
, T.PRIVATE_FLAG
, T.PUBLISH_FLAG
, T.RESTRICT_CLOSURE_FLAG
, T.MULTI_BOOKED_FLAG
, T.MILESTONE_FLAG
, T.HOLIDAY_FLAG
, T.BILLABLE_FLAG
, T.BOUND_MODE_CODE
, T.SOFT_BOUND_FLAG
, T.WORKFLOW_PROCESS_ID
, T.ESCALATION_LEVEL
, T.NOTIFICATION_FLAG
, T.NOTIFICATION_PERIOD
, T.NOTIFICATION_PERIOD_UOM
, T.PARENT_TASK_ID
, T.ALARM_START
, T.ALARM_START_UOM
, T.ALARM_ON
, T.ALARM_COUNT
, T.ALARM_FIRED_COUNT
, T.ALARM_INTERVAL
, T.ALARM_INTERVAL_UOM
, T.RECURRENCE_RULE_ID
, T.COSTS
, T.CURRENCY_CODE
, T.ORG_ID
, JTF_AGENDA_CALCULATIONS.GET_SHIFT_START ( A.SHIFT_CONSTRUCT_ID
, A.RESOURCE_ID
, A.RESOURCE_TYPE_CODE
, T.SCHEDULED_START_DATE ) SHIFT_START
, JTF_AGENDA_CALCULATIONS.GET_SHIFT_END ( A.SHIFT_CONSTRUCT_ID
, A.RESOURCE_ID
, A.RESOURCE_TYPE_CODE
, T.SCHEDULED_START_DATE) SHIFT_END
FROM JTF_TASKS_B T
, JTF_TASK_ASSIGNMENTS A
WHERE T.TASK_ID = A.TASK_ID
AND NVL(T.DELETED_FLAG
, 'N') <> 'Y'
AND T.SCHEDULED_END_DATE >= T.SCHEDULED_START_DATE
AND ( NVL(A.ACTUAL_START_DATE
, A.ACTUAL_END_DATE) IS NOT NULL OR ( EXISTS ( SELECT NULL
FROM JTF_TASK_STATUSES_B S
WHERE S.TASK_STATUS_ID = T.TASK_STATUS_ID
AND SYSDATE BETWEEN NVL(S.START_DATE_ACTIVE
, SYSDATE)
AND NVL(S.END_DATE_ACTIVE
, SYSDATE)
AND NVL(S.CANCELLED_FLAG
, 'N') <> 'Y' )
AND EXISTS ( SELECT NULL
FROM JTF_TASK_STATUSES_B S
WHERE S.TASK_STATUS_ID = A.ASSIGNMENT_STATUS_ID
AND SYSDATE BETWEEN NVL(S.START_DATE_ACTIVE
, SYSDATE)
AND NVL(S.END_DATE_ACTIVE
, SYSDATE)
AND NVL(S.CANCELLED_FLAG
, 'N') <> 'Y' ) ) )