HWR_TEAM_GOAL_OTBI

Details

Schema: FUSION
Object owner: HWR
Object type: VIEW

Columns

Name
PERSON_ID TEAM_NAME GOAL_TYPE START_DT_TRUNC END_DT_TRUNC ROLE MEMBER_ACHIEVEMENT_RATE TEAM_ACHIEVEMENT_RATE ASSOC_ID TEAM_ID

Name

PERSON_ID

TEAM_NAME

GOAL_TYPE

START_DT_TRUNC

END_DT_TRUNC

ROLE

MEMBER_ACHIEVEMENT_RATE

TEAM_ACHIEVEMENT_RATE

ASSOC_ID

TEAM_ID

Query

SQL_Statement
SELECT /PARALLEL(512)/DISTINCT FINAL_GOALS.PERSON_ID, FINAL_GOALS.TEAM_NAME, FINAL_GOALS.GOAL_TYPE, FINAL_GOALS.START_DT_TRUNC, FINAL_GOALS.END_DT_TRUNC, FINAL_GOALS.ROLE, ROUND(FINAL_GOALS.MEMBER_ACHIEVEMENT_RATE) MEMBER_ACHIEVEMENT_RATE, CASE WHEN FINAL_GOALS.DAYS != 0 THEN ROUND((COUNT(distinct (case when TEAM_SUCCESS_DAYS = 1 then STOP_TIME end)) OVER (PARTITION BY FINAL_GOALS.ASSOC_ID)/FINAL_GOALS.DAYS) * 100) ELSE 0 END TEAM_ACHIEVEMENT_RATE, FINAL_GOALS.ASSOC_ID, FINAL_GOALS.TEAM_ID FROM ( SELECT GOALS.TEAM_ID, GOALS.PERSON_ID, GOALS.TEAM_NAME, GOALS.GOAL_TYPE, GOALS.START_DT_TRUNC, GOALS.END_DT_TRUNC, GOALS.STOP_TIME, GOALS.ROLE, CASE WHEN DAYS != 0 THEN ( SUM(MEMBER_SUCCESS_DAYS) OVER( PARTITION BY PERSON_ID, GOALS.ASSOC_ID ) / DAYS ) * 100 else 0 end MEMBER_ACHIEVEMENT_RATE, GOALS.ASSOC_ID, CASE WHEN GOALS.TEAM_VALUE /GOALS.MEMBER_COUNT >= GOALS.DAILY_TARGET_VALUE THEN 1 ELSE 0 END TEAM_SUCCESS_DAYS, GOALS.DAILY_TARGET_VALUE, GOALS.DAYS FROM (SELECT /PARALLEL(512)/DISTINCT TEAM_MEMBER.TEAM_ID TEAM_ID , TEAM_MEMBER.PERSON_ID PERSON_ID, TEAM.TEAM_NAME TEAM_NAME, TEAM_MEMBER.STATUS STATUS, TEAM_MEMBER.ROLE ROLE, TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) as LEAVE_DATE, TEAM_MEMBER.MEMBER_LAST_LEAVING_DATE MEMBER_LAST_LEAVING_DATE, GOAL.GOAL_TYPE GOAL_TYPE, GOAL.GOAL_ID, GOAL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE, TRUNC(GOAL.EFFECTIVE_START_DATE) as START_DT_TRUNC, GOAL.EFFECTIVE_END_DATE EFFECTIVE_END_DATE, TRUNC(GOAL.EFFECTIVE_END_DATE) as END_DT_TRUNC, GOAL.DAILY_TARGET_VALUE DAILY_TARGET_VALUE, GOAL.DAILY_TARGET_UNIT DAILY_TARGET_UNIT, NVL( TRUNC(GA.STOP_TIME_CONVERTED),TRUNC(GOAL.EFFECTIVE_START_DATE)) STOP_TIME, NVL( TRUNC(GA.STOP_TIME_CONVERTED),TRUNC(GOAL.EFFECTIVE_START_DATE))STOP_TIME_TRUNC, GA.MEASURE_TYPE, NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY PERSON_ID,TRUNC(GA.STOP_TIME_CONVERTED),GOAL.GOAL_ID,TEAM.TEAM_ID),0) MEMBER_VALUE, CASE WHEN NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY PERSON_ID,TRUNC(GA.STOP_TIME_CONVERTED),GOAL.GOAL_ID,TEAM.TEAM_ID),0) >= GOAL.DAILY_TARGET_VALUE THEN 1 ELSE 0 END MEMBER_SUCCESS_DAYS, ASSOC.TEAM_GOAL_ASSOC_ID ASSOC_ID,TEAM_MEMBER.userTimeZone, NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY TRUNC(GA.STOP_TIME_CONVERTED),ASSOC.TEAM_GOAL_ASSOC_ID),0) Team_VALUE, ( SELECT COUNT(DISTINCT PERSON_ID) FROM HWR_TEAM_MEMBER WHERE TEAM_ID = TEAM.TEAM_ID AND TRUNC(NVL(MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GOAL.EFFECTIVE_START_DATE) ) MEMBER_COUNT, (( (CASE WHEN TRUNC(GOAL.EFFECTIVE_END_DATE) < TRUNC(systimestamp AT TIME ZONE TEAM_MEMBER.userTimeZone) THEN TRUNC(GOAL.EFFECTIVE_END_DATE) ELSE TRUNC(systimestamp AT TIME ZONE TEAM_MEMBER.userTimeZone) END)-TRUNC(GOAL.EFFECTIVE_START_DATE))+1) days FROM (SELECT MEMBER.STATUS, MEMBER.PERSON_ID, MEMBER.TEAM_ID, MEMBER.MEMBER_LEAVING_DATE, MEMBER.MEMBER_LAST_LEAVING_DATE, MEMBER.ROLE, NVL( (SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID IN (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS_B WHERE PROFILE_OPTION_NAME IN ('FND_TIMEZONE')) AND LEVEL_NAME = 'USER' AND LEVEL_VALUE = (SELECT USER_GUID FROM PER_USERS WHERE PERSON_ID=MEMBER.PERSON_ID)), (SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID IN (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS_B WHERE PROFILE_OPTION_NAME IN ('FND_TIMEZONE')) AND LEVEL_NAME = 'SITE')) userTimeZone FROM FUSION.HWR_TEAM_MEMBER MEMBER) TEAM_MEMBER, HWR_TEAM_GOAL_ASSOC ASSOC, HWR_TEAM_GOAL GOAL, HWR_TEAM TEAM, HWR_TEAM_GOAL_ACTIVITY_VL GA WHERE TEAM_MEMBER.TEAM_ID = ASSOC.TEAM_ID AND ASSOC.GOAL_ID = GOAL.GOAL_ID AND ASSOC.TEAM_ID = TEAM.TEAM_ID AND TEAM_MEMBER.PERSON_ID = GA.USER_ID (+) AND ASSOC.GOAL_ID = GA.GOAL_ID(+) AND ASSOC.IS_DELETED = '0' AND ((TEAM_MEMBER.STATUS = 'ACTIVE') OR (TEAM_MEMBER.STATUS ='INACTIVE' AND (TRUNC(TEAM_MEMBER.MEMBER_LEAVING_DATE) BETWEEN TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(GOAL.EFFECTIVE_END_DATE)))) ) GOALS ) FINAL_GOALS

SQL_Statement

SELECT /*PARALLEL(512)*/DISTINCT

FINAL_GOALS.PERSON_ID,

FINAL_GOALS.TEAM_NAME,

FINAL_GOALS.GOAL_TYPE,

FINAL_GOALS.START_DT_TRUNC,

FINAL_GOALS.END_DT_TRUNC,

FINAL_GOALS.ROLE,

ROUND(FINAL_GOALS.MEMBER_ACHIEVEMENT_RATE) MEMBER_ACHIEVEMENT_RATE,

CASE WHEN FINAL_GOALS.DAYS != 0 THEN

ROUND((COUNT(distinct (case when TEAM_SUCCESS_DAYS = 1 then STOP_TIME end)) OVER (PARTITION BY FINAL_GOALS.ASSOC_ID)/FINAL_GOALS.DAYS) * 100)

ELSE 0 END

TEAM_ACHIEVEMENT_RATE,

FINAL_GOALS.ASSOC_ID,

FINAL_GOALS.TEAM_ID

FROM

(

SELECT

GOALS.TEAM_ID,

GOALS.PERSON_ID,

GOALS.TEAM_NAME,

GOALS.GOAL_TYPE,

GOALS.START_DT_TRUNC,

GOALS.END_DT_TRUNC,

GOALS.STOP_TIME,

GOALS.ROLE,

CASE WHEN DAYS != 0 THEN

( SUM(MEMBER_SUCCESS_DAYS) OVER(

PARTITION BY PERSON_ID, GOALS.ASSOC_ID

) / DAYS ) * 100 else 0 end MEMBER_ACHIEVEMENT_RATE,

GOALS.ASSOC_ID,

CASE

WHEN GOALS.TEAM_VALUE /GOALS.MEMBER_COUNT >= GOALS.DAILY_TARGET_VALUE THEN

1

ELSE

0

END TEAM_SUCCESS_DAYS,

GOALS.DAILY_TARGET_VALUE,

GOALS.DAYS

FROM

(SELECT /*PARALLEL(512)*/DISTINCT

TEAM_MEMBER.TEAM_ID TEAM_ID ,

TEAM_MEMBER.PERSON_ID PERSON_ID,

TEAM.TEAM_NAME TEAM_NAME,

TEAM_MEMBER.STATUS STATUS,

TEAM_MEMBER.ROLE ROLE,

TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) as LEAVE_DATE,

TEAM_MEMBER.MEMBER_LAST_LEAVING_DATE MEMBER_LAST_LEAVING_DATE,

GOAL.GOAL_TYPE GOAL_TYPE,

GOAL.GOAL_ID,

GOAL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE,

TRUNC(GOAL.EFFECTIVE_START_DATE) as START_DT_TRUNC,

GOAL.EFFECTIVE_END_DATE EFFECTIVE_END_DATE,

TRUNC(GOAL.EFFECTIVE_END_DATE) as END_DT_TRUNC,

GOAL.DAILY_TARGET_VALUE DAILY_TARGET_VALUE,

GOAL.DAILY_TARGET_UNIT DAILY_TARGET_UNIT,

NVL( TRUNC(GA.STOP_TIME_CONVERTED),TRUNC(GOAL.EFFECTIVE_START_DATE)) STOP_TIME,

NVL( TRUNC(GA.STOP_TIME_CONVERTED),TRUNC(GOAL.EFFECTIVE_START_DATE))STOP_TIME_TRUNC,

GA.MEASURE_TYPE,

NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY PERSON_ID,TRUNC(GA.STOP_TIME_CONVERTED),GOAL.GOAL_ID,TEAM.TEAM_ID),0) MEMBER_VALUE,

CASE WHEN NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY PERSON_ID,TRUNC(GA.STOP_TIME_CONVERTED),GOAL.GOAL_ID,TEAM.TEAM_ID),0) >= GOAL.DAILY_TARGET_VALUE THEN 1 ELSE 0

END MEMBER_SUCCESS_DAYS,

ASSOC.TEAM_GOAL_ASSOC_ID ASSOC_ID,TEAM_MEMBER.userTimeZone,

NVL(SUM(CASE WHEN TRUNC(NVL(TEAM_MEMBER.MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GA.STOP_TIME_CONVERTED) THEN GA.VALUE END) OVER(PARTITION BY TRUNC(GA.STOP_TIME_CONVERTED),ASSOC.TEAM_GOAL_ASSOC_ID),0) Team_VALUE,

(

SELECT

COUNT(DISTINCT PERSON_ID)

FROM

HWR_TEAM_MEMBER

WHERE

TEAM_ID = TEAM.TEAM_ID

AND TRUNC(NVL(MEMBER_LEAVING_DATE,GOAL.EFFECTIVE_END_DATE)) >= TRUNC(GOAL.EFFECTIVE_START_DATE)

) MEMBER_COUNT,

(( (CASE WHEN TRUNC(GOAL.EFFECTIVE_END_DATE) < TRUNC(systimestamp AT TIME ZONE TEAM_MEMBER.userTimeZone) THEN TRUNC(GOAL.EFFECTIVE_END_DATE) ELSE TRUNC(systimestamp AT TIME ZONE TEAM_MEMBER.userTimeZone) END)-TRUNC(GOAL.EFFECTIVE_START_DATE))+1) days

FROM

(SELECT MEMBER.STATUS,

MEMBER.PERSON_ID,

MEMBER.TEAM_ID,

MEMBER.MEMBER_LEAVING_DATE,

MEMBER.MEMBER_LAST_LEAVING_DATE,

MEMBER.ROLE,

NVL( (SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE

PROFILE_OPTION_ID IN (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS_B WHERE PROFILE_OPTION_NAME IN ('FND_TIMEZONE'))

AND

LEVEL_NAME = 'USER' AND LEVEL_VALUE = (SELECT USER_GUID FROM PER_USERS WHERE PERSON_ID=MEMBER.PERSON_ID)), (SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE

PROFILE_OPTION_ID IN (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS_B WHERE PROFILE_OPTION_NAME IN ('FND_TIMEZONE'))

AND

LEVEL_NAME = 'SITE')) userTimeZone

FROM

FUSION.HWR_TEAM_MEMBER MEMBER) TEAM_MEMBER,

HWR_TEAM_GOAL_ASSOC ASSOC,

HWR_TEAM_GOAL GOAL,

HWR_TEAM TEAM,

HWR_TEAM_GOAL_ACTIVITY_VL GA

WHERE

TEAM_MEMBER.TEAM_ID = ASSOC.TEAM_ID

AND ASSOC.GOAL_ID = GOAL.GOAL_ID

AND ASSOC.TEAM_ID = TEAM.TEAM_ID

AND TEAM_MEMBER.PERSON_ID = GA.USER_ID (+)

AND ASSOC.GOAL_ID = GA.GOAL_ID(+)

AND ASSOC.IS_DELETED = '0'

AND ((TEAM_MEMBER.STATUS = 'ACTIVE') OR (TEAM_MEMBER.STATUS ='INACTIVE' AND (TRUNC(TEAM_MEMBER.MEMBER_LEAVING_DATE) BETWEEN TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(GOAL.EFFECTIVE_END_DATE))))

) GOALS

) FINAL_GOALS