1. Tables and Views for Sales and Fusion Service
  2. ZCA_GOAL_METRICS_V

ZCA_GOAL_METRICS_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

GOAL_ID

GOAL_NUMBER

GOAL_NAME

START_DATE

END_DATE

KPI_ID

KPI_NUMBER

KPI_NAME

KPI_AGGREGATION

KPI_OBJECT

TARGET_START_DATE

TARGET_END_DATE

GOAL_PARTICIPANT_ID

IS_GOAL_PARTICIPANT

IS_RESOURCE_FROM_MY_ORG

RESOURCE_ID

RESOURCE_NUMBER

PERSON_ID

RESOURCE_NAME

MANAGER_ID

MANAGER_NUMBER

EXCLUDE_ROOT_RESOURCE

TARGET

KPI_ACTUAL

KPI_NUMER_ACTUAL

KPI_DENOM_ACTUAL

PROGRESS

ROLLUP_TARGET

SUM_NUMER_ACTUAL

SUM_DENOM_ACTUAL

ROLLUP_KPI_ACTUAL

ROLLUP_PROGRESS

CUMULATIVE_TARGET

CUMULATIVE_ACTUAL

X_CUMULATIVE_ACTUAL

Y_CUMULATIVE_ACTUAL

ROLLUP_CUMULATIVE_TARGET

ROLLUP_CUMULATIVE_ACTUAL

X_ROLLUP_CUMULATIVE_ACTUAL

Y_ROLLUP_CUMULATIVE_ACTUAL

CUMULATIVE_PROGRESS

ROLLUP_CUMULATIVE_PROGRESS

NO_OF_GOAL_PERIODS

IS_FIRST

IS_LAST

CURRENCY_CODE

Query

SQL_Statement

select

g.goal_id,

g.goal_number,

g.goal_name,

g.start_date,

g.end_date,

k.kpi_id,

k.kpi_number,

k.kpi_name,

k.kpi_aggregation,

act_batch.kpi_object,

act_batch.target_start_date,

act_batch.target_end_date,

(Select goal_participant_id

from zca_goal_participants gp1

where gp1.goal_id=g.goal_id

and gp1.resource_id = rh.parent_resource_id) goal_participant_id,

(case when

exists ( select 1 from zca_goal_participants gp1

where gp1.goal_id=g.goal_id

and gp1.resource_id = rh.parent_resource_id

)

then 'Y' else 'N'

end) "IS_GOAL_PARTICIPANT",

(case when

exists ( select 1 from jtf_rs_rep_managers_v t1

where t1.parent_resource_id = HZ_SESSION_UTIL.GET_USER_PARTYID

and t1.resource_id= rh.parent_resource_id

)

then 'Y' else 'N'

end) "IS_RESOURCE_FROM_MY_ORG",

rh.parent_resource_id resource_id,

hz.party_number "RESOURCE_NUMBER",

per.person_id "PERSON_ID",

hz.party_unique_name "RESOURCE_NAME",

rm.parent_resource_id manager_id,

hz_mgr.party_number "MANAGER_NUMBER",

rm.reports_to_flag exclude_root_resource,

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)"TARGET",

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.KPI_ACTUAL,0)

ELSE 0

END

) KPI_ACTUAL,

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.X_ACTUAL,0)

ELSE 0

END

) KPI_NUMER_ACTUAL,

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.Y_ACTUAL,0)

ELSE 0

END

) KPI_DENOM_ACTUAL,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id AND nvl(gp.overriden_target,g.common_target) > 0 THEN ( nvl(act_batch.KPI_ACTUAL,0))*100/nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(

CASE

WHEN nvl(gp.overriden_target,g.common_target) > 0 AND nvl(act_batch.Y_ACTUAL,0) > 0 AND gp.resource_id = rh.parent_resource_id

THEN (nvl(act_batch.X_ACTUAL,0)/act_batch.Y_ACTUAL)*100*100/nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

END PROGRESS,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(nvl(gp.overriden_target,g.common_target))

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

AVG(nvl(gp.overriden_target,g.common_target))

END rollup_target,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(nvl(act_batch.X_ACTUAL,0))

END

SUM_NUMER_ACTUAL,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(nvl(act_batch.Y_ACTUAL,0))

END

SUM_DENOM_ACTUAL,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(nvl(act_batch.KPI_ACTUAL,0))

END rollup_KPI_ACTUAL,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

decode(SUM(nvl(gp.overriden_target,g.common_target)), 0, 0, (SUM(nvl(act_batch.KPI_ACTUAL,0)))*100/SUM(nvl(gp.overriden_target,g.common_target)))

ELSE 0

END rollup_PROGRESS,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

AVG(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

ELSE 0

END Cumulative_Target,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.KPI_ACTUAL,0)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END Cumulative_Actual,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.X_ACTUAL,0)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END X_Cumulative_Actual,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.Y_ACTUAL,0)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END Y_Cumulative_Actual,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(

SUM(nvl(gp.overriden_target,g.common_target) )

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

AVG(

AVG(nvl(gp.overriden_target,g.common_target) )

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

ELSE 0

END Rollup_Cumulative_Target,

CASE

WHEN k.kpi_aggregation='ORA_COUNT' or k.kpi_aggregation='ORA_SUM' THEN

SUM(

SUM(nvl(act_batch.KPI_ACTUAL,0))

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END Rollup_Cumulative_Actual,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(

SUM(nvl(act_batch.X_ACTUAL,0))

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END X_Rollup_Cumulative_Actual,

CASE

WHEN k.kpi_aggregation='ORA_PERCENTAGE' THEN

SUM(

SUM(nvl(act_batch.Y_ACTUAL,0))

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)

END Y_Rollup_Cumulative_Actual,

decode(( SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)), 0, 0, ( SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(act_batch.KPI_ACTUAL,0)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)) * 100 / ( SUM(

SUM(

CASE

WHEN gp.resource_id = rh.parent_resource_id THEN nvl(gp.overriden_target,g.common_target)

ELSE 0

END

)

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row))) CUMULATIVE_PROGRESS,

decode(( SUM(

SUM(nvl(gp.overriden_target,g.common_target) )

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)), 0, 0, ( SUM(

SUM(nvl(act_batch.KPI_ACTUAL,0))

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row)) * 100 / ( SUM(

SUM(nvl(gp.overriden_target,g.common_target) )

) over ( partition by g.goal_number,hz.party_number order by act_batch.target_start_date rows between unbounded preceding and current row))) rollup_CUMULATIVE_PROGRESS,

COUNT(*) over (partition by g.goal_number,hz.party_number) no_of_goal_periods,

(CASE WHEN act_batch.target_start_date = g.start_date THEN 1 ELSE 0 END) IS_FIRST,

(CASE WHEN act_batch.target_end_date = g.end_date THEN 1 ELSE 0 END) IS_LAST,

(CASE WHEN k.kpi_uom_code = 'AMOUNT' THEN FND_PROFILE.VALUE('ZCA_COMMON_CORPORATE_CURRENCY') END) CURRENCY_CODE

FROM

hz_parties hz,

jtf_rs_rep_managers_v rh,

jtf_rs_rep_managers_v rm,

per_users per,

hz_parties hz_mgr,

zca_goals g,

zca_kpi k,

zca_goal_participants gp,

zca_goal_actuals act_batch

where ( 1 = 1 )

and hz.party_id = rh.parent_resource_id

AND hz.user_guid = per.user_guid(+)

and rh.resource_id = act_batch.resource_id

and act_batch.goal_id = g.goal_id

and g.goal_id = gp.goal_id

AND act_batch.resource_id = gp.resource_id

and g.kpi_id = k.kpi_id

and rh.parent_resource_id = rm.resource_id

and rm.reports_to_flag in ('Y','T')

and rm.parent_resource_id = hz_mgr.party_id

AND rm.tree_code = rh.tree_code

GROUP BY

g.goal_id,

g.goal_number,

g.goal_name,

g.start_date,

g.end_date,

k.kpi_id,

k.kpi_number,

k.kpi_name,

k.kpi_uom_code,

k.kpi_aggregation,

act_batch.kpi_object,

rh.parent_resource_id,

hz.party_number,

per.person_id,

hz.party_unique_name,

rm.parent_resource_id,

hz_mgr.party_number,

rm.reports_to_flag,

act_batch.target_start_date,

act_batch.target_end_date