VIEW: APPS.CSD_TASK_RESOURCES_V

Source

View Text - Preformatted

SELECT grp.GROUP_NAME name, 'GROUP_RESOURCE' resource_type FROM JTF_RS_GROUPS_VL grp, JTF_RS_GROUP_USAGES usg WHERE trunc(sysdate) between nvl(trunc(grp.START_DATE_ACTIVE), trunc(sysdate)) and nvl(trunc(grp.END_DATE_ACTIVE),trunc(sysdate)) AND usg.USAGE = 'SUPPORT' AND grp.GROUP_ID = usg.GROUP_ID UNION SELECT team.TEAM_NAME name , 'TEAM_RESOURCE' resource_type FROM JTF_RS_TEAMS_VL team, JTF_RS_TEAM_USAGES usg WHERE trunc(sysdate) between nvl(trunc(team.START_DATE_ACTIVE),trunc(sysdate)) and nvl(trunc(team.END_DATE_ACTIVE),trunc(sysdate)) AND usg.USAGE = 'SUPPORT' AND team.TEAM_ID = usg.TEAM_ID UNION SELECT PPL.FULL_NAME name , 'EMPLOYEE_RESOURCE' resource_type FROM JTF_RS_RESOURCE_EXTNS RSC , PER_ALL_PEOPLE_F PPL , PER_ASSIGNMENTS_F ASG WHERE ASG.PERSON_ID = PPL.PERSON_ID AND TRUNC(SYSDATE)BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND PPL.EMPLOYEE_NUMBER IS NOT NULL AND RSC.CATEGORY = 'EMPLOYEE' AND RSC.SOURCE_ID = PPL.PERSON_ID AND PPL.BUSINESS_GROUP_ID = ASG.BUSINESS_GROUP_ID AND ASG.PRIMARY_FLAG = 'Y'AND ASG.ASSIGNMENT_TYPE = 'E' AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(START_DATE_ACTIVE),TRUNC(SYSDATE)) AND NVL(TRUNC(END_DATE_ACTIVE),TRUNC(SYSDATE)) order by 2 desc

View Text - HTML Formatted

SELECT GRP.GROUP_NAME NAME
, 'GROUP_RESOURCE' RESOURCE_TYPE
FROM JTF_RS_GROUPS_VL GRP
, JTF_RS_GROUP_USAGES USG
WHERE TRUNC(SYSDATE) BETWEEN NVL(TRUNC(GRP.START_DATE_ACTIVE)
, TRUNC(SYSDATE))
AND NVL(TRUNC(GRP.END_DATE_ACTIVE)
, TRUNC(SYSDATE))
AND USG.USAGE = 'SUPPORT'
AND GRP.GROUP_ID = USG.GROUP_ID UNION SELECT TEAM.TEAM_NAME NAME
, 'TEAM_RESOURCE' RESOURCE_TYPE
FROM JTF_RS_TEAMS_VL TEAM
, JTF_RS_TEAM_USAGES USG
WHERE TRUNC(SYSDATE) BETWEEN NVL(TRUNC(TEAM.START_DATE_ACTIVE)
, TRUNC(SYSDATE))
AND NVL(TRUNC(TEAM.END_DATE_ACTIVE)
, TRUNC(SYSDATE))
AND USG.USAGE = 'SUPPORT'
AND TEAM.TEAM_ID = USG.TEAM_ID UNION SELECT PPL.FULL_NAME NAME
, 'EMPLOYEE_RESOURCE' RESOURCE_TYPE
FROM JTF_RS_RESOURCE_EXTNS RSC
, PER_ALL_PEOPLE_F PPL
, PER_ASSIGNMENTS_F ASG
WHERE ASG.PERSON_ID = PPL.PERSON_ID
AND TRUNC(SYSDATE)BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PPL.EMPLOYEE_NUMBER IS NOT NULL
AND RSC.CATEGORY = 'EMPLOYEE'
AND RSC.SOURCE_ID = PPL.PERSON_ID
AND PPL.BUSINESS_GROUP_ID = ASG.BUSINESS_GROUP_ID
AND ASG.PRIMARY_FLAG = 'Y'AND ASG.ASSIGNMENT_TYPE = 'E'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(START_DATE_ACTIVE)
, TRUNC(SYSDATE))
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE)) ORDER BY 2 DESC