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 OWNER_RESOURCE_ID METRIC_DIM1_KEY QUOTA DISTANCE EXPECTED_REVENUE TOTAL_BESTCASE ADJUSTED_TEAM_ROLLUP OPPORTUNITY_ROLLUP BESTCASE COMMIT UNFORECASTED WON OVERLAY_EXPECTED_REVENUE OVERLAY_TOTAL_BESTCASE OVERLAY_ADJ_TEAM_ROLLUP OVERLAY_OPPORTUNITY_ROLLUP OVERLAY_BESTCASE OVERLAY_COMMIT OVERLAY_UNFORECASTED OVERLAY_WON

Query

SQL_Statement

SELECT met.period_id || met.root_participant_id || met.metric_dim_key metric_bi_id, met.metric_src_code metric_src_code, met.period_id period_id, met.root_participant_id root_participant_id, rfo.owner_resource_id owner_resource_id, met.metric_dim_key metric_dim1_key, NVL(q.corp_metric_value,0) quota, decode(met.root_participant_id, met.metric_dim_key, 0, 1) distance, nvl(adjusted_expected_revenue, nvl(expected_revenue, 0)) expected_revenue, nvl(adjusted_total_bestcase, nvl(total_bestcase, 0)) total_bestcase, nvl(adjusted_team_rollup, nvl(un_adjusted_team_rollup, 0)) adjusted_team_rollup, nvl(expected_revenue, 0) opportunity_rollup, nvl(adjusted_bestcase, nvl(bestcase, 0)) bestcase, nvl(adjusted_commit, nvl(commit, 0)) commit, nvl(pipeline, 0) unforecasted, nvl(won, 0) won, nvl(adjusted_nr_expected_revenue, nvl(nr_expected_revenue, 0)) overlay_expected_revenue, nvl(adjusted_nr_total_bestcase, nvl(nr_total_bestcase, 0)) overlay_total_bestcase, nvl(adjusted_nr_team_rollup, nvl(un_adjusted_nr_team_rollup, 0)) overlay_adj_team_rollup, nvl(nr_expected_revenue, 0) overlay_opportunity_rollup, nvl(adjusted_nr_bestcase, nvl(nr_bestcase, 0)) overlay_bestcase, nvl(adjusted_nr_commit, nvl(nr_commit, 0)) overlay_commit, nvl(nr_pipeline, 0) overlay_unforecasted, nvl(nr_won, 0) overlay_won FROM ( SELECT metric_src_code, metric_dim1_code, period_id, root_participant_id, metric_dim_key, SUM(CASE WHEN metric_code = 'BESTCASE' THEN metric_value ELSE NULL END) AS bestcase, SUM(CASE WHEN metric_code = 'COMMIT' THEN metric_value ELSE NULL END) AS commit, SUM(CASE WHEN metric_code = 'EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS expected_revenue, SUM(CASE WHEN metric_code = 'PIPELINE' THEN metric_value ELSE NULL END) AS pipeline, SUM(CASE WHEN metric_code = 'WON' THEN metric_value ELSE NULL END) AS won, SUM(CASE WHEN metric_code = 'TOTAL_BESTCASE' THEN metric_value ELSE NULL END) AS total_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:COMMIT' THEN metric_value ELSE NULL END) AS adjusted_commit, SUM(CASE WHEN metric_code = 'ADJUSTED:BESTCASE' THEN metric_value ELSE NULL END) AS adjusted_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS adjusted_expected_revenue, SUM(CASE WHEN metric_code = 'ADJUSTED:TOTAL_BESTCASE' THEN metric_value ELSE NULL END) AS adjusted_total_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:SUB:EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS adjusted_team_rollup, SUM(CASE WHEN metric_code = 'SUB:EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS un_adjusted_team_rollup, SUM(CASE WHEN metric_code = 'NR_BESTCASE' THEN metric_value ELSE NULL END) AS nr_bestcase, SUM(CASE WHEN metric_code = 'NR_COMMIT' THEN metric_value ELSE NULL END) AS nr_commit, SUM(CASE WHEN metric_code = 'NR_EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS nr_expected_revenue, SUM(CASE WHEN metric_code = 'NR_PIPELINE' THEN metric_value ELSE NULL END) AS nr_pipeline, SUM(CASE WHEN metric_code = 'NR_WON' THEN metric_value ELSE NULL END) AS nr_won, SUM(CASE WHEN metric_code = 'NR_TOTAL_BESTCASE' THEN metric_value ELSE NULL END) AS nr_total_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:NR_COMMIT' THEN metric_value ELSE NULL END) AS adjusted_nr_commit, SUM(CASE WHEN metric_code = 'ADJUSTED:NR_BESTCASE' THEN metric_value ELSE NULL END) AS adjusted_nr_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:NR_EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS adjusted_nr_expected_revenue, SUM(CASE WHEN metric_code = 'ADJUSTED:NR_TOTAL_BESTCASE' THEN metric_value ELSE NULL END) AS adjusted_nr_total_bestcase, SUM(CASE WHEN metric_code = 'ADJUSTED:SUB:NR_EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS adjusted_nr_team_rollup, SUM(CASE WHEN metric_code = 'SUB:NR_EXPECTED_REVENUE' THEN metric_value ELSE NULL END) AS un_adjusted_nr_team_rollup FROM ( SELECT dn.metric_src_code, dn.metric_dim1_code AS metric_dim1_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/*+index(rf zsf_ds_metric_dim_rf_dn_n5)*/ s.metric_src_code, s.metric_dim1_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_parent_key IN ( SELECT DECODE(column_value, -99999, rf.metric_dim_parent_key, column_value) FROM TABLE ( zsf_ds_metrics_util.get_secure_metric_dim_key_list) ) ) dn, ( SELECT period_id, period_type, param_value, start_date, end_date FROM ( SELECT p.period_id, p.period_type, pm1.param_value, p.start_date, p.end_date 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.snapshot_id 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 ) GROUP BY metric_src_code ,metric_dim1_code ,period_id ,root_participant_id ,metric_dim_key ) met, zsf_ds_metric_dim_rf_dn rfo, zsf_ds_quota q, ( SELECT period_id, period_type, start_date, end_date FROM zsf_ds_period ) pd where rfo.metric_dim_code = met.metric_dim1_code AND rfo.metric_dim_key = met.metric_dim_key AND rfo.dim_level = 1 AND rfo.owner_resource_id = q.metric_dim1_key(+) AND pd.period_id = met.period_id AND pd.start_date <= q.effective_date(+) AND pd.end_date >= q.effective_date(+) AND (nvl(q.effective_date, trunc(sysdate)) = (select nvl(max(q2.effective_date), trunc(sysdate)) from zsf_ds_quota q2 where q2.metric_dim1_key =rfo.owner_resource_id and q2.effective_date between pd.start_date and pd.end_date) )