MOO_REVN_METRICS_ROLLUP_V

Details

• Schema: FUSION

• Object owner: MOO

• Object type: VIEW

Columns

Name
FCST_HEADER_ID TERRITORY_ID PERIOD_ID FORECASTED_PIPELINE_AMT UNFORECASTED_PIPELINE_AMT FORECASTED_CLOSED_AMT UNFORECASTED_CLOSED_AMT

Query

SQL_Statement

SELECT T.fcst_header_id AS FCST_HEADER_ID, T.territory_id AS TERRITORY_ID, T.PERIOD_ID AS PERIOD_ID , SUM(T.FORECASTED_PIPELINE_AMT) FORECASTED_PIPELINE_AMT, SUM(T.UNFORECASTED_PIPELINE_AMT) UNFORECASTED_PIPELINE_AMT, CAST(0 AS NUMBER) FORECASTED_CLOSED_AMT, CAST(0 AS NUMBER) UNFORECASTED_CLOSED_AMT FROM ( SELECT hdr.fcst_header_id AS FCST_HEADER_ID, mv.territory_id AS TERRITORY_ID, ap.fcst_adjust_period_id AS PERIOD_ID , MV.REVN_METRIC_VALUE AS FORECASTED_PIPELINE_AMT , CAST(0 AS NUMBER) AS UNFORECASTED_PIPELINE_AMT FROM moo_revn_met_terr_rollup_v mv, zsf_fcst_header hdr, zsf_fcst_adjust_period ap WHERE mv.period_name = ap.period_name AND (( mv.sales_credit_type_code = 'QUOTA' AND hdr.forecast_type = 'REVENUE' ) OR ( mv.sales_credit_type_code = 'NONQUOTA' AND hdr.forecast_type = 'NON_REVENUE')) AND hdr.fcst_header_id = ap.fcst_header_id AND hdr.fcst_status_qualifier_code IN ('ACTIVE','NEXT') AND NVL(mv.revn_metric_type ,'OPEN') ='OPEN' AND mv.commit_flag = 'Y' UNION ALL SELECT hdr.fcst_header_id AS FCST_HEADER_ID, mv.territory_id AS TERRITORY_ID, ap.fcst_adjust_period_id AS PERIOD_ID , CAST(0 AS NUMBER) AS FORECASTED_PIPELINE_AMT , MV.REVN_METRIC_VALUE AS UNFORECASTED_PIPELINE_AMT FROM moo_revn_met_terr_rollup_v mv, zsf_fcst_header hdr, zsf_fcst_adjust_period ap WHERE mv.period_name = ap.period_name AND (( mv.sales_credit_type_code = 'QUOTA' AND hdr.forecast_type = 'REVENUE' ) OR ( mv.sales_credit_type_code = 'NONQUOTA' AND hdr.forecast_type = 'NON_REVENUE')) AND hdr.fcst_header_id = ap.fcst_header_id AND hdr.fcst_status_qualifier_code IN ('ACTIVE','NEXT') AND NVL(mv.revn_metric_type ,'OPEN') ='OPEN' AND mv.commit_flag = 'N' ) T GROUP BY T.fcst_header_id, T.territory_id, T.PERIOD_ID