HWR_TEAM_GOAL_ACTIVITY_VL

Details

• Schema: FUSION

• Object owner: HWR

• Object type: VIEW

Columns

Name
USER_ID ACTIVITY_ID VALUE GOAL_ID URI GOAL_TYPE MEASURE_TYPE STOP_TIME_CONVERTED STOP_TIME DAILY_TARGET_UNIT DAILY_TARGET_VALUE

Query

SQL_Statement

SELECT DISTINCT ACTIVITY.USER_ID, ACTIVITY.ACTIVITY_ID, DECODE (GOAL.GOAL_TYPE ,'DAILY_DISTANCE',ACTIVITY.DISTANCE,'DAILY_STEPS',ACTIVITY.STEPS,'DAILY_ACTIVE_TIME',ACTIVITY.ACTIVE_TIME) VALUE, GOAL.GOAL_ID, ACTIVITY.URI, GOAL.GOAL_TYPE, DECODE (GOAL.GOAL_TYPE ,'DAILY_DISTANCE','DISTANCE','DAILY_STEPS','STEPS','DAILY_ACTIVE_TIME','ACTIVE') MEASURE_TYPE, (ACTIVITY.START_TIME AT TIME ZONE ACTIVITY.USERTIMEZONE) STOP_TIME_CONVERTED, ACTIVITY.START_TIME STOP_TIME, GOAL.DAILY_TARGET_UNIT, GOAL.DAILY_TARGET_VALUE FROM (SELECT AA.ACTIVITY_ID, AA.URI, AA.USER_ID, AA.START_TIME, AA.STOP_TIME, AA.DISTANCE DISTANCE, AA.STEPS STEPS, AA.ACTIVE_TIME ACTIVE_TIME, 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 = AA.USER_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 HWR_ACT_ACTIVITY AA, HWR_EHW_SOURCES_VL SOURCE WHERE AA.source_id = source.source_id (+) AND ((source.TYPE = 'ORA_MANUAL' AND AA.URI != 'oracle:apps:hcm:hwr:cardea:activity:summary') OR (( NVL(source.TYPE,'ADMIN') <> 'ORA_MANUAL')AND AA.URI='oracle:apps:hcm:hwr:cardea:activity:summary')) ) ACTIVITY, (SELECT G.GOAL_ID, G.EFFECTIVE_START_DATE, G.EFFECTIVE_END_DATE, G.DAILY_TARGET_UNIT, G.DAILY_TARGET_VALUE, G.GOAL_TYPE, TGA.TEAM_ID, TM.PERSON_ID FROM HWR_TEAM_GOAL G, HWR_TEAM_GOAL_ASSOC TGA, HWR_TEAM_MEMBER TM WHERE G.GOAL_ID = TGA.GOAL_ID AND TGA.TEAM_ID = TM.TEAM_ID ) GOAL WHERE ACTIVITY.USER_ID = GOAL.PERSON_ID AND TRUNC(ACTIVITY.START_TIME AT TIME ZONE ACTIVITY.USERTIMEZONE) >= TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(ACTIVITY.STOP_TIME AT TIME ZONE ACTIVITY.USERTIMEZONE) <= TRUNC(GOAL.EFFECTIVE_END_DATE)