1. Tables and Views for HCM
  2. HHR_VLTR_TEAM_VOL_INFO_VL

HHR_VLTR_TEAM_VOL_INFO_VL

Details

  • Schema: FUSION

  • Object owner: HHR

  • Object type: VIEW

Columns

Name

TEAM_ID

PROJECT_ID

MEMBER_COUNT

VOLUNTEER_EMPLOYEES

VOLUNTEER_GUESTS

TEAM_VOLUNTEERED_HOURS

Query

SQL_Statement

SELECT PROJ_TEAMS.TEAM_ID, PROJ_TEAMS.PROJECT_ID, COUNT(TEAM_MEMBER.PERSON_ID) MEMBER_COUNT,

SUM(CASE WHEN PROJ_VOL_INFO.ID IS NOT NULL THEN 1 ELSE 0 END) VOLUNTEER_EMPLOYEES, SUM(NVL(GUEST_COUNT, 0)) VOLUNTEER_GUESTS,

SUM(NVL(NO_OF_HOURS, 0.0)) TEAM_VOLUNTEERED_HOURS

FROM HHR_VLTR_TEAM_VOL_INFO PROJ_TEAMS, HWR_TEAM TEAM, HWR_TEAM_MEMBER TEAM_MEMBER, HHR_VLTR_PROJ_VOL_INFO_VL PROJ_VOL_INFO,

HWR_VLTR_PROJECT_VL PROJECT

WHERE

PROJ_TEAMS.TEAM_ID = TEAM.TEAM_ID

AND (TEAM.STATUS = 'ACTIVE' OR (TEAM.STATUS = 'INACTIVE' AND TEAM.LAST_UPDATE_DATE > PROJECT.Project_start_date))

AND TEAM.TEAM_ID = TEAM_MEMBER.TEAM_ID

AND (TEAM_MEMBER.STATUS = 'ACTIVE'

OR (TEAM_MEMBER.STATUS = 'INACTIVE' AND TEAM_MEMBER.IS_DELETED = 1 AND

TEAM_MEMBER.MEMBER_LAST_LEAVING_DATE >= PROJECT.Project_end_date

))

AND TRUNC(TEAM_MEMBER.MEMBER_JOINING_DATE) <= TRUNC(PROJECT.Project_end_date)

AND PROJ_TEAMS.PROJECT_ID = PROJ_VOL_INFO.PROJECT_ID(+)

AND TEAM_MEMBER.PERSON_ID = PROJ_VOL_INFO.HCM_PERSON_ID(+)

AND PROJ_TEAMS.PROJECT_ID = PROJECT.PROJECT_ID

GROUP BY PROJ_TEAMS.TEAM_ID, PROJ_TEAMS.PROJECT_ID