View: JTF_AM_TASK_DTLS_V

Product:	JTF - CRM Foundation
Description:	This is a view to get task details for Assignments
Implementation/DBA Data:	APPS.JTF_AM_TASK_DTLS_V

View Text

SELECT A.ROWID ROW_ID
, A.RESOURCE_TYPE_CODE
, A.RESOURCE_ID
, A.TASK_ID
, A.TASK_ASSIGNMENT_ID
, T.SCHEDULED_START_DATE
, T.SCHEDULED_END_DATE
, A.SCHED_TRAVEL_DURATION
, T.SOURCE_OBJECT_TYPE_CODE
, T.SOURCE_OBJECT_ID
, T.SOURCE_OBJECT_NAME
, T.TASK_NUMBER
, T.TASK_TYPE_ID
, T.TASK_STATUS_ID
, A.ASSIGNMENT_STATUS_ID
, T.TASK_PRIORITY_ID
, T.TASK_NAME
, A.CREATED_BY
, A.CREATION_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_DATE
, A.LAST_UPDATE_LOGIN
, DECODE(T.ESCALATION_LEVEL
, 'DE'
, 'N'
, 'NE'
, 'N'
, DECODE(T.TASK_TYPE_ID||R.REFERENCE_CODE||NVL(S.CLOSED_FLAG
, 'N')||NVL(S.COMPLETED_FLAG
, 'N')|| NVL(S.CANCELLED_FLAG
, 'N')||NVL(S.REJECTED_FLAG
, 'N')||NVL(T.DELETED_FLAG
, 'N')
, '22ESCNNNNN'
, 'Y'
, 'N') ) ESCALATION_FLAG
FROM JTF_TASKS_VL T
, JTF_TASK_ASSIGNMENTS A
, JTF_TASKS_B T1
, JTF_TASK_STATUSES_B S
, JTF_TASK_REFERENCES_B R
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' ) ) )
AND R.OBJECT_ID (+)= A.TASK_ID
AND R.REFERENCE_CODE (+)= 'ESC'
AND R.OBJECT_TYPE_CODE (+)= 'TASK'
AND T1.TASK_ID (+)= R.TASK_ID
AND S.TASK_STATUS_ID (+)= T.TASK_STATUS_ID

Columns