HHR_VLTR_TEAM_VOL_SUMMARY

Details

Schema: FUSION
Object owner: HHR
Object type: VIEW

Columns

Name
TEAM_ID PROJECT_ID VOLUNTEER_EMPLOYEES VOLUNTEER_GUESTS TEAM_VOLUNTEERED_HOURS CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN

Name

TEAM_ID

PROJECT_ID

VOLUNTEER_EMPLOYEES

VOLUNTEER_GUESTS

TEAM_VOLUNTEERED_HOURS

CREATED_BY

CREATION_DATE

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

Query

SQL_Statement
SELECT TEAM_INFO.TEAM_ID, TEAM_VOL_INFO.PROJECT_ID, TEAM_VOL_INFO.VOLUNTEER_EMPLOYEES, TEAM_VOL_INFO.VOLUNTEER_GUESTS, TEAM_VOL_INFO.TEAM_VOLUNTEERED_HOURS, TEAM_INFO.CREATED_BY, TEAM_INFO.CREATION_DATE, TEAM_INFO.LAST_UPDATE_DATE, TEAM_INFO.LAST_UPDATED_BY, TEAM_INFO.LAST_UPDATE_LOGIN FROM HWR_TEAM TEAM_INFO, (SELECT TEAM_ID, PROJECT_ID, COUNT(MEMBER_PERSON_ID) VOLUNTEER_EMPLOYEES, SUM(GUEST_COUNT) VOLUNTEER_GUESTS, SUM(NO_OF_HOURS) TEAM_VOLUNTEERED_HOURS FROM ( SELECT TEAM.TEAM_ID, TEAM_MEMBER.PERSON_ID MEMBER_PERSON_ID, VOL_PROJ_REL.PROJECT_ID, VOL_PROJ_REL.SUBSCRIPTION_DATE, VOL_HOURS.NO_OF_HOURS, PROJ_GUEST.GUEST_COUNT FROM HWR_TEAM TEAM, HWR_TEAM_MEMBER TEAM_MEMBER, HWR_VLTR_VOL_PROJ_REL_VL VOL_PROJ_REL, HWR_VLTR_ACTIVITY_B VOL_HOURS, (SELECT PROJECT_ID, HCM_PERSON_ID, COUNT(GUEST_ID) GUEST_COUNT FROM HHR_VLTR_PROJECT_GUEST_B GROUP BY PROJECT_ID, HCM_PERSON_ID) PROJ_GUEST WHERE TEAM.STATUS = 'ACTIVE' AND TEAM.TEAM_ID = TEAM_MEMBER.TEAM_ID AND TEAM_MEMBER.IS_DELETED = 0 AND TEAM_MEMBER.STATUS = 'ACTIVE' AND TEAM_MEMBER.PERSON_ID = VOL_PROJ_REL.HCM_PERSON_ID AND VOL_PROJ_REL.STATUS = 'VOLUNTEERED' AND VOL_PROJ_REL.HCM_PERSON_ID = VOL_HOURS.HCM_PERSON_ID(+) AND VOL_PROJ_REL.PROJECT_ID = VOL_HOURS.PROJECT_ID(+) AND VOL_PROJ_REL.HCM_PERSON_ID = PROJ_GUEST.HCM_PERSON_ID(+) AND VOL_PROJ_REL.PROJECT_ID = PROJ_GUEST.PROJECT_ID(+)) GROUP BY TEAM_ID, PROJECT_ID) TEAM_VOL_INFO WHERE TEAM_INFO.TEAM_ID = TEAM_VOL_INFO.TEAM_ID

SQL_Statement

SELECT TEAM_INFO.TEAM_ID, TEAM_VOL_INFO.PROJECT_ID, TEAM_VOL_INFO.VOLUNTEER_EMPLOYEES, TEAM_VOL_INFO.VOLUNTEER_GUESTS, TEAM_VOL_INFO.TEAM_VOLUNTEERED_HOURS, TEAM_INFO.CREATED_BY, TEAM_INFO.CREATION_DATE, TEAM_INFO.LAST_UPDATE_DATE,

TEAM_INFO.LAST_UPDATED_BY,

TEAM_INFO.LAST_UPDATE_LOGIN

FROM HWR_TEAM TEAM_INFO,

(SELECT TEAM_ID, PROJECT_ID, COUNT(MEMBER_PERSON_ID) VOLUNTEER_EMPLOYEES, SUM(GUEST_COUNT) VOLUNTEER_GUESTS, SUM(NO_OF_HOURS) TEAM_VOLUNTEERED_HOURS FROM (

SELECT TEAM.TEAM_ID, TEAM_MEMBER.PERSON_ID MEMBER_PERSON_ID, VOL_PROJ_REL.PROJECT_ID, VOL_PROJ_REL.SUBSCRIPTION_DATE, VOL_HOURS.NO_OF_HOURS, PROJ_GUEST.GUEST_COUNT

FROM HWR_TEAM TEAM, HWR_TEAM_MEMBER TEAM_MEMBER, HWR_VLTR_VOL_PROJ_REL_VL VOL_PROJ_REL, HWR_VLTR_ACTIVITY_B VOL_HOURS,

(SELECT PROJECT_ID, HCM_PERSON_ID, COUNT(GUEST_ID) GUEST_COUNT FROM HHR_VLTR_PROJECT_GUEST_B GROUP BY PROJECT_ID, HCM_PERSON_ID) PROJ_GUEST WHERE

TEAM.STATUS = 'ACTIVE'

AND TEAM.TEAM_ID = TEAM_MEMBER.TEAM_ID

AND TEAM_MEMBER.IS_DELETED = 0

AND TEAM_MEMBER.STATUS = 'ACTIVE'

AND TEAM_MEMBER.PERSON_ID = VOL_PROJ_REL.HCM_PERSON_ID

AND VOL_PROJ_REL.STATUS = 'VOLUNTEERED'

AND VOL_PROJ_REL.HCM_PERSON_ID = VOL_HOURS.HCM_PERSON_ID(+)

AND VOL_PROJ_REL.PROJECT_ID = VOL_HOURS.PROJECT_ID(+)

AND VOL_PROJ_REL.HCM_PERSON_ID = PROJ_GUEST.HCM_PERSON_ID(+)

AND VOL_PROJ_REL.PROJECT_ID = PROJ_GUEST.PROJECT_ID(+))

GROUP BY TEAM_ID, PROJECT_ID) TEAM_VOL_INFO

WHERE TEAM_INFO.TEAM_ID = TEAM_VOL_INFO.TEAM_ID