Product: | JTF - CRM Foundation |
---|---|
Description: | Base view for the JTF_AGENDA_TA_V |
Implementation/DBA Data: | APPS.JTF_AGENDA_TA_V |
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' ) ) )