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

ZSF_DS_METRIC_BI_VIEW

Details

  • Schema: FUSION

  • Object owner: ZSF

  • Object type: VIEW

Columns

Name

METRIC_BI_ID

METRIC_SRC_CODE

PERIOD_ID

ROOT_PARTICIPANT_ID

METRIC_DIM1_KEY

DISTANCE

EXPECTED_REVENUE

ADJUSTED_TEAM_ROLLUP

OPPORTUNITY_ROLLUP

BESTCASE

COMMIT

UNFORECASTED

WON

Query

SQL_Statement

SELECT

period_id

|| root_participant_id

|| metric_dim_key metric_bi_id,

metric_src_code metric_src_code,

period_id period_id,

root_participant_id root_participant_id,

metric_dim_key metric_dim1_key,

decode(root_participant_id, metric_dim_key, 0, 1) distance,

nvl(mgr_adjusted_expected_revenue, nvl(adjusted_expected_revenue, nvl(expected_revenue, 0))) expected_revenue,

nvl(adjusted_team_rollup, nvl(un_adjusted_team_rollup, 0)) adjusted_team_rollup,

nvl(expected_revenue, 0) opportunity_rollup,

nvl(mgr_adjusted_bestcase, nvl(adjusted_bestcase, nvl(bestcase, 0))) bestcase,

nvl(mgr_adjusted_commit, nvl(adjusted_commit, nvl(commit, 0))) commit,

nvl(pipeline, 0) unforecasted,

nvl(won, 0) won

FROM

(

SELECT

*

FROM

(

SELECT

dn.metric_src_code,

dn.metric_dim_parent_key AS root_participant_id,

pd.period_id,

dn.metric_dim_key,

rv.metric_code,

rv.metric_value

FROM

(

select

s.metric_src_code,

rf.metric_dim_key,

rf.metric_dim_parent_key

from

zsf_ds_metric_dim_rf_dn rf,

zsf_ds_metric_src s,

zsf_ds_param p

where

rf.dim_level <= 2

AND rf.metric_dim_code = s.metric_dim1_code

AND p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

AND rf.metric_dim_key in (

select

metric_dim_key

from

zsf_ds_metric_dim_rf_dn

where

dim_level >= 1

AND metric_dim_code = s.metric_dim1_code

AND owner_resource_id = zsf_ds_metrics_util.get_user_partyid)

AND rf.metric_dim_parent_key in (

select

metric_dim_key

from

zsf_ds_metric_dim_rf_dn

where

dim_level >= 1

AND metric_dim_code = s.metric_dim1_code

AND owner_resource_id = zsf_ds_metrics_util.get_user_partyid)

) dn,

(

SELECT

period_id,

period_type,

param_value

FROM

(

SELECT

p.period_id,

p.period_type,

pm1.param_value

FROM

zsf_ds_period p,

zsf_ds_param pm,

zsf_ds_param pm1

WHERE

trunc(sysdate) <= p.end_date

AND pm.param_name = 'FORECAST_PERIOD_TYPE'

AND pm1.param_name = 'ACTIVE_PERIODS'

AND p.period_type = pm.param_value

ORDER BY

start_date

)

WHERE

ROWNUM <= param_value

) pd,

(

SELECT

m.period_id,

m.metric_dim1_key,

decode(m.metric_level, 'TOTAL', m.metric_code, 'ADJUSTED:TOTAL', 'ADJUSTED:' || m.metric_code,

m.metric_level

|| ':'

|| m.metric_code) metric_code,

m.metric_value

FROM

zsf_ds_metric m,

(

SELECT

metric_dim1_code,

metric_dim2_column,

metric_dim3_column,

metric_dim4_column,

metric_dim5_column

FROM

zsf_ds_metric_src s,

zsf_ds_param p

WHERE

p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

) dim

WHERE

metric_level IN ( 'TOTAL', 'ADJUSTED:TOTAL', 'ADJUSTED:SUB', 'SUB' )

AND nvl2(dim.metric_dim2_column, dim2_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim3_column, dim3_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim4_column, dim4_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim5_column, dim5_root_flag, 'Y') = 'Y'

) rv

WHERE

rv.metric_dim1_key (+) = dn.metric_dim_key

AND rv.period_id (+) = pd.period_id

UNION ALL

SELECT

dim.metric_src_code AS metric_src_code,

adj.dim1_parent_key AS root_participant_id,

adj.period_id AS period_id,

adj.dim1_key AS metric_dim1_key,

'MGR:ADJUSTED:' || adj.metric_code AS metric_code,

adj.adjusted_metric_value AS metric_value

FROM

zsf_ds_adjustment_calc adj,

(

SELECT

metric_src_code,

metric_dim1_code,

metric_dim2_column,

metric_dim3_column,

metric_dim4_column,

metric_dim5_column

FROM

zsf_ds_metric_src s,

zsf_ds_param p

WHERE

p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

) dim,

(

SELECT

period_id,

period_type,

param_value

FROM

(

SELECT

p.period_id,

p.period_type,

pm1.param_value

FROM

zsf_ds_period p,

zsf_ds_param pm,

zsf_ds_param pm1

WHERE

trunc(sysdate) <= p.end_date

AND pm.param_name = 'FORECAST_PERIOD_TYPE'

AND pm1.param_name = 'ACTIVE_PERIODS'

AND p.period_type = pm.param_value

ORDER BY

start_date

)

WHERE

ROWNUM <= param_value

) pd

WHERE

adj.metric_src_pk1 IS NULL

AND adj.metric_level = 'TOTAL'

AND adj.operation_code = 'ADJUST'

AND adj.process_status IS NULL

AND adj.adjusted_by_dim1_key <> adj.dim1_key

AND adj.adjusted_by_dim1_key = adj.dim1_parent_key

AND nvl2(dim.metric_dim2_column, dim2_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim3_column, dim3_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim4_column, dim4_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim5_column, dim5_root_flag, 'Y') = 'Y'

AND adj.period_id = pd.period_id

AND adj.adjusted_by_dim1_key in ( select

metric_dim_key

from

zsf_ds_metric_dim_rf_dn

where

metric_dim_code = dim.metric_dim1_code

AND owner_resource_id = zsf_ds_metrics_util.get_user_partyid )

UNION ALL

SELECT

metric_src_code metric_src_code,

root_participant_id root_participant_id,

period_id period_id,

metric_dim1_key metric_dim1_key,

'MGR:ADJUSTED:' || metric_code metric_code,

SUM(metric_value) metric_value

FROM

(

SELECT

unadj.metric_src_code metric_src_code,

unadj.period_id period_id,

nvl2(adcj.metric_code, 1, 0) is_adjusted,

nvl(adcj.metric_code, unadj.metric_code) metric_code,

unadj.dim1_parent_key metric_dim1_key,

unadj.root_participant_id root_participant_id,

decode(adcj.adjusted_metric_value, - 1, 0, NULL, unadj.metric_value,

adcj.adjusted_metric_value) metric_value

FROM

( select

calc.period_id,

calc.metric_code,

dim.metric_src_code metric_src_code,

calc.dim1_anc_path,

dn.metric_dim_parent_key root_participant_id,

calc.dim1_parent_key,

calc.dim2_parent_key,

calc.dim3_parent_key,

calc.dim4_parent_key,

calc.dim5_parent_key,

calc.metric_src_pk1,

calc.metric_src_pk2,

calc.metric_src_pk3,

calc.metric_value

from

zsf_ds_metric_calc calc,

( select

rf.metric_dim_key,

rf.metric_dim_parent_key

from

zsf_ds_metric_dim_rf_dn rf,

zsf_ds_metric_src s,

zsf_ds_param p

where

rf.dim_level = 2

AND rf.metric_dim_code = s.metric_dim1_code

AND p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

AND rf.metric_dim_key in (

select

metric_dim_key

from

zsf_ds_metric_dim_rf_dn

where

dim_level > 1

AND metric_dim_code = s.metric_dim1_code

AND owner_resource_id = zsf_ds_metrics_util.get_user_partyid)

) dn,

(

SELECT

metric_src_code,

metric_dim1_code,

metric_dim2_column,

metric_dim3_column,

metric_dim4_column,

metric_dim5_column

FROM

zsf_ds_metric_src s,

zsf_ds_param p

WHERE

p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

) dim,

(

SELECT

period_id,

period_type,

param_value

FROM

(

SELECT

p.period_id,

p.period_type,

pm1.param_value

FROM

zsf_ds_period p,

zsf_ds_param pm,

zsf_ds_param pm1

WHERE

trunc(sysdate) <= p.end_date

AND pm.param_name = 'FORECAST_PERIOD_TYPE'

AND pm1.param_name = 'ACTIVE_PERIODS'

AND p.period_type = pm.param_value

ORDER BY

start_date

)

WHERE

ROWNUM <= param_value

) pd

where

calc.period_id = pd.period_id

AND calc.dim1_parent_key = dn.metric_dim_key

AND nvl2(dim.metric_dim2_column, calc.dim2_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim3_column, calc.dim3_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim4_column, calc.dim4_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim5_column, calc.dim5_root_flag, 'Y') = 'Y'

) unadj,

( select

adcj1.period_id,

adcj1.metric_code,

adcj1.metric_level,

dn.metric_dim_key root_participant_id,

adcj1.adjusted_by_dim1_anc_path,

adcj1.dim1_anc_path,

adcj1.dim1_parent_key,

adcj1.dim2_parent_key,

adcj1.dim3_parent_key,

adcj1.dim4_parent_key,

adcj1.dim5_parent_key,

adcj1.metric_src_pk1,

adcj1.metric_src_pk2,

adcj1.metric_src_pk3,

adcj1.adjusted_metric_value

from

zsf_ds_adjustment_calc adcj1,

(

select

metric_dim_key

from

zsf_ds_metric_dim_rf_dn rf,

zsf_ds_metric_src s,

zsf_ds_param p

where

rf.metric_dim_code = s.metric_dim1_code

AND p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

AND owner_resource_id = zsf_ds_metrics_util.get_user_partyid

and exists (

select

1

from

zsf_ds_metric_dim_rf_dn rf1

where

rf1.dim_level = 2

and rf1.metric_dim_parent_key = rf.metric_dim_key )

) dn,

(

SELECT

metric_src_code,

metric_dim1_code,

metric_dim2_column,

metric_dim3_column,

metric_dim4_column,

metric_dim5_column

FROM

zsf_ds_metric_src s,

zsf_ds_param p

WHERE

p.param_name = 'ACTIVE_METRIC_SOURCE'

AND p.param_value = s.metric_src_code

) dim,

(

SELECT

period_id,

period_type,

param_value

FROM

(

SELECT

p.period_id,

p.period_type,

pm1.param_value

FROM

zsf_ds_period p,

zsf_ds_param pm,

zsf_ds_param pm1

WHERE

trunc(sysdate) <= p.end_date

AND pm.param_name = 'FORECAST_PERIOD_TYPE'

AND pm1.param_name = 'ACTIVE_PERIODS'

AND p.period_type = pm.param_value

ORDER BY

start_date

)

WHERE

ROWNUM <= param_value

) pd

WHERE

0 = 0

AND adcj1.metric_src_pk1 IS NOT NULL

AND adcj1.operation_code = 'ADJUST'

AND adcj1.period_id = pd.period_id

AND adcj1.metric_level = 'TOTAL'

AND adcj1.dim1_parent_key = dn.metric_dim_key

AND nvl2(dim.metric_dim2_column, adcj1.dim2_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim3_column, adcj1.dim3_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim4_column, adcj1.dim4_root_flag, 'Y') = 'Y'

AND nvl2(dim.metric_dim5_column, adcj1.dim5_root_flag, 'Y') = 'Y'

) adcj

WHERE

unadj.period_id = adcj.period_id (+)

AND unadj.root_participant_id = adcj.root_participant_id (+)

AND unadj.metric_src_pk1 = adcj.metric_src_pk1 (+)

AND nvl(unadj.metric_src_pk2, 0) = nvl(adcj.metric_src_pk2(+), 0)

AND nvl(unadj.metric_src_pk3, 0) = nvl(adcj.metric_src_pk3(+), 0)

AND nvl(unadj.dim2_parent_key, 0) = nvl(adcj.dim2_parent_key(+), 0)

AND nvl(unadj.dim3_parent_key, 0) = nvl(adcj.dim3_parent_key(+), 0)

AND nvl(unadj.dim4_parent_key, 0) = nvl(adcj.dim4_parent_key(+), 0)

AND nvl(unadj.dim5_parent_key, 0) = nvl(adcj.dim5_parent_key(+), 0)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adci

WHERE

adci.dim1_parent_key = adcj.dim1_parent_key

AND adci.metric_src_pk1 IS NOT NULL

AND adci.metric_level = 'TOTAL'

AND adci.period_id = adcj.period_id

AND adci.operation_code = 'ADJUST'

AND length(adcj.adjusted_by_dim1_anc_path) > length(adci.adjusted_by_dim1_anc_path)

AND adci.metric_src_pk1 = adcj.metric_src_pk1

AND nvl(adci.metric_src_pk2, 0) = nvl(adcj.metric_src_pk2, 0)

AND nvl(adci.metric_src_pk3, 0) = nvl(adcj.metric_src_pk3, 0)

AND nvl(adci.dim2_parent_key, 0) = nvl(adcj.dim2_parent_key, 0)

AND nvl(adci.dim3_parent_key, 0) = nvl(adcj.dim3_parent_key, 0)

AND nvl(adci.dim4_parent_key, 0) = nvl(adcj.dim4_parent_key, 0)

AND nvl(adci.dim5_parent_key, 0) = nvl(adcj.dim5_parent_key, 0)

)

AND NOT EXISTS (

SELECT

1

FROM

(

SELECT

metric_defn_id,

metric_code,

regexp_substr(dep_list, '[^,]+', 1, level) AS dep_list

FROM

(

SELECT

defn.metric_defn_id,

defn.metric_code,

defn.depnd_metric_code_list dep_list

FROM

zsf_ds_metric_defn defn,

zsf_ds_metric_src s,

zsf_ds_param p

WHERE

p.param_name = 'ACTIVE_METRIC_SOURCE'

AND s.metric_src_code = p.param_value

AND s.metric_src_code = defn.metric_src_code

AND defn.status_code = 'ENABLED'

AND defn.depnd_metric_code_list IS NOT NULL

)

WHERE

nvl(PRIOR metric_code, metric_code) = metric_code

AND nvl(PRIOR metric_defn_id, metric_defn_id) = metric_defn_id

CONNECT BY

regexp_substr(dep_list, '[^,]+', 1, level) IS NOT NULL

) dep_codes

WHERE

unadj.metric_code = dep_codes.dep_list

AND adcj.metric_code IS NOT NULL

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE

0 = 0

AND adc.dim1_parent_key = unadj.root_participant_id

AND adc.operation_code = 'ADJUST'

AND adc.metric_src_pk1 IS NULL

AND unadj.metric_code = adc.metric_code

AND adc.metric_level = 'TOTAL'

AND adc.period_id = unadj.period_id

AND adc.dim1_parent_key != adc.dim1_key

AND instr(unadj.dim1_anc_path, adc.dim1_anc_path) > 0

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE

0 = 0

AND adc.dim1_parent_key = adcj.dim1_parent_key

AND adc.operation_code = 'ADJUST'

AND adc.metric_src_pk1 IS NULL

AND adcj.metric_code IS NOT NULL

AND adcj.metric_code = adc.metric_code

AND adcj.metric_level = 'TOTAL'

AND adc.period_id = adcj.period_id

AND adc.dim1_parent_key != adc.dim1_key

AND instr(adcj.dim1_anc_path, adc.dim1_anc_path) > 0

)

)

GROUP BY

metric_src_code,

root_participant_id,

period_id,

metric_dim1_key,

metric_code

HAVING

SUM(is_adjusted) <> 0

) PIVOT (

MAX ( metric_value )

FOR metric_code

IN ( 'BESTCASE' AS bestcase, 'COMMIT' AS commit, 'EXPECTED_REVENUE' AS expected_revenue, 'PIPELINE' AS pipeline, 'WON' AS won,

'ADJUSTED:COMMIT' AS adjusted_commit, 'ADJUSTED:BESTCASE' AS adjusted_bestcase, 'ADJUSTED:EXPECTED_REVENUE' AS adjusted_expected_revenue,

'ADJUSTED:SUB:EXPECTED_REVENUE' AS adjusted_team_rollup, 'SUB:EXPECTED_REVENUE' AS un_adjusted_team_rollup, 'MGR:ADJUSTED:EXPECTED_REVENUE'

AS mgr_adjusted_expected_revenue, 'MGR:ADJUSTED:COMMIT' AS mgr_adjusted_commit, 'MGR:ADJUSTED:BESTCASE' AS mgr_adjusted_bestcase )

)

)