MATERIALIZED VIEW: APPS.BIL_BI_FST1_PG_MV

Source

(
    SELECT /* 12.0: bug#4526784 */
      'REPFST' umarker,
      smry.txn_time_id,
      smry.txn_period_type_id,
      smry.forecast_time_id effective_time_id,
      smry.forecast_period_type_id effective_period_type_id,
      DECODE(smry.salesrep_id,NULL,rgmv.parent_parent_group_id,smry.sales_group_id) parent_sales_group_id,
      smry.sales_group_id,
      smry.salesrep_id,
      COUNT(*) all_xcnt,
      NULL forecast_amt_xcnt,
      NULL forecast_amt_xcnt_s,
      (smry.forecast_amt) forecast_amt,
      (smry.forecast_amt_s) forecast_amt_s,
      NULL forecast_amt_sub,
      NULL forecast_amt_sub_s,
      smry.product_category_id product_category_id,
      smry.credit_type_id,
      SUM(smry.opp_forecast_amt) opp_forecast_amt,
      SUM(smry.opp_forecast_amt_s) opp_forecast_amt_s,
      COUNT(smry.opp_forecast_amt) opp_forecast_amt_xcnt,
      COUNT(smry.opp_forecast_amt_s) opp_forecast_amt_s_xcnt,
      NULL opp_forecast_amt_sub,
      NULL opp_forecast_amt_sub_s,
      NULL opp_forecast_amt_sub_xcnt,
      NULL opp_forecast_amt_sub_s_xcnt
    FROM
      BIL.BIL_BI_FST_DTL_F smry,
      bil_bi_rs_grp_mv rgmv
    WHERE
      smry.sales_group_id = rgmv.sales_group_id
      AND rgmv.sales_group_id = rgmv.parent_group_id
    GROUP BY
      smry.txn_time_id,
      smry.txn_period_type_id,
      smry.forecast_time_id,
      smry.forecast_period_type_id,
      DECODE(smry.salesrep_id,NULL,rgmv.parent_parent_group_id,smry.sales_group_id),
      smry.sales_group_id,
      smry.salesrep_id,
      smry.forecast_amt,
      smry.forecast_amt_s,
      smry.product_category_id,
      smry.credit_type_id
    UNION ALL
    SELECT
      'GRPFST' umarker,
      smry.txn_time_id,
      smry.txn_period_type_id,
      smry.forecast_time_id effective_time_id,
      smry.forecast_period_type_id effective_period_type_id,
      rgmva.parent_parent_group_id parent_sales_group_id,
      rgmv.parent_parent_group_id sales_group_id,
      NULL salesrep_id,
      COUNT(*) all_xcnt,
      COUNT(forecast_amt) forecast_amt_xcnt,
      COUNT(forecast_amt_s) forecast_amt_xcnt_s,
      NULL forecast_amt,
      NULL forecast_amt_s,
      SUM(forecast_amt) forecast_amt_sub,
      SUM(forecast_amt_s) forecast_amt_sub_s,
      smry.product_category_id product_category_id,
      smry.credit_type_id,
      NULL opp_forecast_amt,
      NULL opp_forecast_amt_s,
      NULL opp_forecast_amt_xcnt,
      NULL opp_forecast_amt_s_xcnt,
      SUM(smry.opp_forecast_amt) opp_forecast_amt_sub,
      SUM(smry.opp_forecast_amt_s) opp_forecast_amt_sub_s,
      COUNT(smry.opp_forecast_amt) opp_forecast_amt_sub_xcnt,
      COUNT(smry.opp_forecast_amt_s) opp_forecast_amt_sub_s_xcnt
    FROM
      BIL.BIL_BI_FST_DTL_F smry,
      bil_bi_rs_grp_mv rgmv,
      bil_bi_rs_grp_mv rgmva
    WHERE
      smry.sales_group_id = rgmv.sales_group_id
      AND rgmv.parent_group_id = rgmv.sales_group_id
      AND rgmv.parent_parent_group_id = rgmva.sales_group_id
      AND rgmva.parent_group_id = rgmva.sales_group_id
      AND smry.salesrep_id IS NULL
    GROUP BY
      txn_time_id,
      txn_period_type_id,
      forecast_time_id,
      forecast_period_type_id,
      product_category_id,
      credit_type_id,
      rgmv.parent_parent_group_id,
      rgmva.parent_parent_group_id
    UNION ALL
    SELECT
      'SUBREPST' umarker,
      smry.txn_time_id,
      smry.txn_period_type_id,
      smry.forecast_time_id effective_time_id,
      smry.forecast_period_type_id effective_period_type_id,
      rgmv.parent_parent_group_id parent_sales_group_id,
      smry.sales_group_id,
      NULL salesrep_id,
      COUNT(*) all_xcnt,
      COUNT(forecast_amt) forecast_amt_xcnt,
      COUNT(forecast_amt_s) forecast_amt_xcnt_s,
      NULL forecast_amt,
      NULL forecast_amt_s,
      SUM(forecast_amt) forecast_amt_sub,
      SUM(forecast_amt_s) forecast_amt_sub_s,
      smry.product_category_id product_category_id,
      smry.credit_type_id,
      NULL opp_forecast_amt,
      NULL opp_forecast_amt_s,
      NULL opp_forecast_amt_xcnt,
      NULL opp_forecast_amt_s_xcnt,
      SUM(smry.opp_forecast_amt) opp_forecast_amt_sub,
      SUM(smry.opp_forecast_amt_s) opp_forecast_amt_sub_s,
      COUNT(smry.opp_forecast_amt) opp_forecast_amt_sub_xcnt,
      COUNT(smry.opp_forecast_amt_s) opp_forecast_amt_sub_s_xcnt
    FROM
      BIL.BIL_BI_FST_DTL_F smry,
      BIL_BI_RS_GRP_MV rgmv
    WHERE
      smry.sales_group_id = rgmv.sales_group_id
      AND rgmv.parent_group_id = rgmv.sales_group_id
      AND smry.salesrep_id IS NOT NULL
    GROUP BY
      txn_time_id,
      txn_period_type_id,
      forecast_time_id,
      forecast_period_type_id,
      product_category_id,
      credit_type_id,
      rgmv.parent_parent_group_id,
      smry.sales_group_id)