HWR_PROGRAM_GOAL_ACTIVITY

Details

Schema: FUSION
Object owner: HWR
Object type: VIEW

Columns

Name
USER_ID ACTIVITY_ID VALUE UNITS GOAL_ID URI GOAL_METRIC MEASURE_TYPE STOP_TIME

Name

USER_ID

ACTIVITY_ID

VALUE

UNITS

GOAL_ID

URI

GOAL_METRIC

MEASURE_TYPE

STOP_TIME

Query

SQL_Statement
SELECT ACTIVITY.USER_ID, ACTIVITY.ACTIVITY_ID, ACTIVITY.VALUE, ACTIVITY.UNITS, GOAL.GOAL_ID, ACTIVITY.URI, GOAL.GOAL_METRIC, 'DISTANCE' MEASURE_TYPE, ACTIVITY.STOP_TIME FROM ( SELECT AA.ACTIVITY_ID, AA.URI, AA.USER_ID, AA.START_TIME, AA.STOP_TIME, AMD.VALUE, AMD.UNITS FROM HWR_ACT_ACTIVITY AA, HWR_ACT_MSR_DISTANCE AMD WHERE AA.ACTIVITY_ID = AMD.ACTIVITY_ID AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary' ) ACTIVITY, ( SELECT GF.GOAL_ID, GUX.USER_ID, GF.EFFECTIVE_START_DATE, GF.EFFECTIVE_END_DATE, GF.GOAL_METRIC, GF.CATEGORY FROM HWR_GOAL_F GF, HWR_GOAL_USER_XREF GUX WHERE GF.GOAL_ID = GUX.GOAL_ID AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program' AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:distance' ) GOAL WHERE ACTIVITY.USER_ID = GOAL.USER_ID AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE) AND ACTIVITY.VALUE > 0 UNION SELECT ACTIVITY.USER_ID, ACTIVITY.ACTIVITY_ID, ACTIVITY.VALUE, ACTIVITY.UNITS, GOAL.GOAL_ID, ACTIVITY.URI, GOAL.GOAL_METRIC, 'STEPS' MEASURE_TYPE, ACTIVITY.STOP_TIME FROM ( SELECT AA.ACTIVITY_ID, AA.URI, AA.USER_ID, AA.START_TIME, AA.STOP_TIME, AMS.VALUE, AMS.UNITS FROM HWR_ACT_ACTIVITY AA, HWR_ACT_MSR_STEPS AMS WHERE AA.ACTIVITY_ID = AMS.ACTIVITY_ID AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary' ) ACTIVITY, ( SELECT GF.GOAL_ID, GUX.USER_ID, GF.EFFECTIVE_START_DATE, GF.EFFECTIVE_END_DATE, GF.GOAL_METRIC, GF.CATEGORY FROM HWR_GOAL_F GF, HWR_GOAL_USER_XREF GUX WHERE GF.GOAL_ID = GUX.GOAL_ID AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program' AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:steps' ) GOAL WHERE ACTIVITY.USER_ID = GOAL.USER_ID AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE) AND ACTIVITY.VALUE > 0 UNION SELECT ACTIVITY.USER_ID, ACTIVITY.ACTIVITY_ID, ACTIVITY.VALUE, ACTIVITY.UNITS, GOAL.GOAL_ID, ACTIVITY.URI, GOAL.GOAL_METRIC, 'ACTIVE' MEASURE_TYPE, ACTIVITY.STOP_TIME FROM ( SELECT AA.ACTIVITY_ID, AA.URI, AA.USER_ID, AA.START_TIME, AA.STOP_TIME, AMA.VALUE, AMA.UNITS FROM HWR_ACT_ACTIVITY AA, HWR_ACT_MSR_ACTIVE AMA WHERE AA.ACTIVITY_ID = AMA.ACTIVITY_ID AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary' ) ACTIVITY, ( SELECT GF.GOAL_ID, GUX.USER_ID, GF.EFFECTIVE_START_DATE, GF.EFFECTIVE_END_DATE, GF.GOAL_METRIC, GF.CATEGORY FROM HWR_GOAL_F GF, HWR_GOAL_USER_XREF GUX WHERE GF.GOAL_ID = GUX.GOAL_ID AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program' AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:high_activity_time' ) GOAL WHERE ACTIVITY.USER_ID = GOAL.USER_ID AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE) AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE) AND ACTIVITY.VALUE > 0

SQL_Statement

SELECT ACTIVITY.USER_ID,

ACTIVITY.ACTIVITY_ID,

ACTIVITY.VALUE,

ACTIVITY.UNITS,

GOAL.GOAL_ID,

ACTIVITY.URI,

GOAL.GOAL_METRIC,

'DISTANCE' MEASURE_TYPE,

ACTIVITY.STOP_TIME

FROM (

SELECT AA.ACTIVITY_ID,

AA.URI,

AA.USER_ID,

AA.START_TIME,

AA.STOP_TIME,

AMD.VALUE,

AMD.UNITS

FROM HWR_ACT_ACTIVITY AA,

HWR_ACT_MSR_DISTANCE AMD

WHERE AA.ACTIVITY_ID = AMD.ACTIVITY_ID

AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary'

) ACTIVITY,

(

SELECT GF.GOAL_ID,

GUX.USER_ID,

GF.EFFECTIVE_START_DATE,

GF.EFFECTIVE_END_DATE,

GF.GOAL_METRIC,

GF.CATEGORY

FROM HWR_GOAL_F GF,

HWR_GOAL_USER_XREF GUX

WHERE GF.GOAL_ID = GUX.GOAL_ID

AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program'

AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:distance'

) GOAL

WHERE ACTIVITY.USER_ID = GOAL.USER_ID

AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE)

AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE)

AND ACTIVITY.VALUE > 0

UNION

SELECT ACTIVITY.USER_ID,

ACTIVITY.ACTIVITY_ID,

ACTIVITY.VALUE,

ACTIVITY.UNITS,

GOAL.GOAL_ID,

ACTIVITY.URI,

GOAL.GOAL_METRIC,

'STEPS' MEASURE_TYPE,

ACTIVITY.STOP_TIME

FROM (

SELECT AA.ACTIVITY_ID,

AA.URI,

AA.USER_ID,

AA.START_TIME,

AA.STOP_TIME,

AMS.VALUE,

AMS.UNITS

FROM HWR_ACT_ACTIVITY AA,

HWR_ACT_MSR_STEPS AMS

WHERE AA.ACTIVITY_ID = AMS.ACTIVITY_ID

AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary'

) ACTIVITY,

(

SELECT GF.GOAL_ID,

GUX.USER_ID,

GF.EFFECTIVE_START_DATE,

GF.EFFECTIVE_END_DATE,

GF.GOAL_METRIC,

GF.CATEGORY

FROM HWR_GOAL_F GF,

HWR_GOAL_USER_XREF GUX

WHERE GF.GOAL_ID = GUX.GOAL_ID

AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program'

AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:steps'

) GOAL

WHERE ACTIVITY.USER_ID = GOAL.USER_ID

AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE)

AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE)

AND ACTIVITY.VALUE > 0

UNION

SELECT ACTIVITY.USER_ID,

ACTIVITY.ACTIVITY_ID,

ACTIVITY.VALUE,

ACTIVITY.UNITS,

GOAL.GOAL_ID,

ACTIVITY.URI,

GOAL.GOAL_METRIC,

'ACTIVE' MEASURE_TYPE,

ACTIVITY.STOP_TIME

FROM (

SELECT AA.ACTIVITY_ID,

AA.URI,

AA.USER_ID,

AA.START_TIME,

AA.STOP_TIME,

AMA.VALUE,

AMA.UNITS

FROM HWR_ACT_ACTIVITY AA,

HWR_ACT_MSR_ACTIVE AMA

WHERE AA.ACTIVITY_ID = AMA.ACTIVITY_ID

AND AA.URI = 'oracle:apps:hcm:hwr:cardea:activity:summary'

) ACTIVITY,

(

SELECT GF.GOAL_ID,

GUX.USER_ID,

GF.EFFECTIVE_START_DATE,

GF.EFFECTIVE_END_DATE,

GF.GOAL_METRIC,

GF.CATEGORY

FROM HWR_GOAL_F GF,

HWR_GOAL_USER_XREF GUX

WHERE GF.GOAL_ID = GUX.GOAL_ID

AND GF.CATEGORY = 'oracle:apps:hcm:hwr:cardea:goalcategory:Program'

AND GF.GOAL_METRIC = 'oracle:apps:hcm:hwr:cardea:activitymeasure:high_activity_time'

) GOAL

WHERE ACTIVITY.USER_ID = GOAL.USER_ID

AND TRUNC(ACTIVITY.START_TIME) >= TRUNC(GOAL.EFFECTIVE_START_DATE)

AND TRUNC(ACTIVITY.STOP_TIME) <= TRUNC(GOAL.EFFECTIVE_END_DATE)

AND ACTIVITY.VALUE > 0