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

ZCA_GOAL_KPI_BREAKDOWN_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

GOAL_ID

GOAL_NUMBER

GOAL_NAME

KPI_ID

FREQUENCY

KPI_NUMBER

KPI_NAME

KPI_OBJECT

KPI_UOM_CODE

KPI_CATEGORY_CODE_COL

TARGET_START_DATE

TARGET_END_DATE

RESOURCE_ID

RESOURCE_NAME

RESORUCE_NUMBER

KPI_CATEGORY_NAME

KPI_CATEGORY_CD

KPI_AGGREGATION

ROLLUP_KPI_VALUE

CUMULATIVE_ROLLUP_KPI_VALUE

INDIVIDUAL_KPI_VALUE

CUMULATIVE_INDIVIDUAL_KPI_VAL

X_ROLLUP_KPI_VALUE

X_CUM_ROLLUP_KPI_VALUE

X_INDIVIDUAL_KPI_VALUE

X_CUM_INDIVIDUAL_KPI_VAL

Y_ROLLUP_KPI_VALUE

Y_CUM_ROLLUP_KPI_VALUE

Y_INDIVIDUAL_KPI_VALUE

Y_CUM_INDIVIDUAL_KPI_VAL

Query

SQL_Statement

SELECT

g.goal_id,

g.goal_number,

g.goal_name,

g.kpi_id,

g.frequency,

k.kpi_number,

k.kpi_name,

k.kpi_object,

k.kpi_uom_code,

k.kpi_category_code_col,

ga.target_start_date,

ga.target_end_date,

rh.parent_resource_id resource_id,

mgr.party_unique_name resource_name,

mgr.party_number resoruce_number,

NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd) kpi_category_name,

kpif.kpi_category_cd,

k.kpi_aggregation,

CASE

WHEN kpif.split_category='NONE' THEN

SUM(kpif.kpi_value)

ELSE 0

END rollup_kpi_value,

CASE

WHEN kpif.split_category='NONE' THEN

SUM(SUM(kpif.kpi_value)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END cumulative_rollup_kpi_value,

CASE

WHEN kpif.split_category='NONE' THEN

SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)

ELSE 0

END individual_kpi_value,

CASE

WHEN kpif.split_category='NONE' THEN

SUM(SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END cumulative_individual_kpi_val,

CASE

WHEN kpif.split_category='NUMER' THEN

SUM(kpif.kpi_value)

ELSE 0

END x_rollup_kpi_value,

CASE

WHEN kpif.split_category='NUMER' THEN

SUM(SUM(kpif.kpi_value)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END x_cum_rollup_kpi_value,

CASE

WHEN kpif.split_category='NUMER' THEN

SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)

ELSE 0

END x_individual_kpi_value,

CASE

WHEN kpif.split_category='NUMER' THEN

SUM(SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END x_cum_individual_kpi_val,

CASE

WHEN kpif.split_category='DENOM' THEN

SUM(kpif.kpi_value)

ELSE 0

END y_rollup_kpi_value,

CASE

WHEN kpif.split_category='DENOM' THEN

SUM(SUM(kpif.kpi_value)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END y_cum_rollup_kpi_value,

CASE

WHEN kpif.split_category='DENOM' THEN

SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)

ELSE 0

END y_individual_kpi_value,

CASE

WHEN kpif.split_category='DENOM' THEN

SUM(SUM(

CASE

WHEN kpif.credit_recipient_resource_id = rh.parent_resource_id THEN

kpif.kpi_value

ELSE

0

END

)) over (

partition by g.goal_id,rh.parent_resource_id,NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd)

order by ga.target_start_date

rows between unbounded preceding and current row

)

ELSE 0

END y_cum_individual_kpi_val

FROM

zca_kpi_transactions kpif,

zca_kpi k,

zca_goals g,

zca_goal_actuals ga,

hz_parties pcpt,

jtf_rs_rep_managers rh,

fnd_tree_version tv,

hz_parties mgr,

fnd_lookup_values_tl lt

WHERE

kpif.kpi_txn_time < sys_extract_utc(systimestamp)

AND g.goal_id = ga.goal_id

AND g.kpi_id = k.kpi_id

AND k.kpi_id = kpif.kpi_id

AND kpif.kpi_txn_time BETWEEN ga.target_start_date AND ga.target_end_date

AND ga.resource_id = kpif.credit_recipient_resource_id

AND ga.resource_id = pcpt.party_id

AND ga.resource_id = rh.resource_id

AND tv.tree_version_id = rh.tree_version_id

AND tv.status = 'ACTIVE'

AND rh.tree_structure_code = 'RESOURCE_ORG_TREE_STRUCTURE'

AND rh.tree_code = 'GLOBAL_SALES_MARKETING'

AND trunc(sysdate) BETWEEN trunc(tv.effective_start_date) AND trunc(tv.effective_end_date)

AND trunc(sysdate) BETWEEN trunc(rh.start_date_active) AND trunc(rh.end_date_active)

AND mgr.party_id = rh.parent_resource_id

AND lt.lookup_type (+) = k.kpi_category_type

AND lt.lookup_code (+) = nvl(kpif.kpi_category_cd, 'EMPTY')

AND (lt.view_application_id (+) = 2

OR (lt.view_application_id (+) = 0 AND lt.lookup_type (+) != 'MKL_LEAD_CHANNEL_SETID'))

AND lt.language (+) = userenv('LANG')

GROUP BY

g.goal_id,

g.goal_number,

g.goal_name,

g.kpi_id,

g.frequency,

k.kpi_number,

k.kpi_name,

k.kpi_object,

k.kpi_uom_code,

k.kpi_category_code_col,

ga.target_start_date,

ga.target_end_date,

rh.parent_resource_id,

mgr.party_unique_name,

mgr.party_number,

NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd),

kpif.kpi_category_cd,

k.kpi_aggregation,

kpif.split_category