MATERIALIZED VIEW: APPS.BIL_BI_FST_PG_MV

Source

(
    SELECT /*+ ORDERED FULL(VCT) USE_HASH(VCT) */
      smry1.txn_time_id,
      smry1.txn_period_type_id,
      smry1.effective_time_id ,
      smry1.effective_period_type_id,
      smry1.parent_sales_group_id,
      smry1.sales_group_id,
      smry1.salesrep_id,
      vbh.parent_id product_category_id,
      (CASE WHEN vbh.parent_id = vbh.child_id THEN 1 ELSE 0 END) assign_to_cat,
      smry1.credit_type_id,
      SUM(forecast_amt) forecast_amt,
      SUM(forecast_amt_s) forecast_amt_s,
      SUM(forecast_amt_sub) forecast_amt_sub,
      SUM(forecast_amt_sub_s) forecast_amt_sub_s,
      COUNT(forecast_amt) forecast_amt_xcnt,
      COUNT(forecast_amt_s) forecast_amt_xcnt_s,
      COUNT(forecast_amt_sub) forecast_amt_sub_xcnt,
      COUNT(forecast_amt_sub_s) forecast_amt_sub_xcnt_s,
      COUNT(*)  all_xcnt,
			SUM(opp_forecast_amt) opp_forecast_amt,
			SUM(opp_forecast_amt_s) opp_forecast_amt_s,
			SUM(opp_forecast_amt_sub) opp_forecast_amt_sub,
			SUM(opp_forecast_amt_sub_s) opp_forecast_amt_sub_s,
			COUNT(opp_forecast_amt) opp_forecast_amt_xcnt,
			COUNT(opp_forecast_amt_s) opp_forecast_amt_s_xcnt,
			COUNT(opp_forecast_amt_sub) opp_forecast_amt_sub_xcnt,
			COUNT(opp_forecast_amt_sub_s) opp_forecast_amt_sub_s_xcnt
    FROM
      INV.MTL_DEFAULT_CATEGORY_SETS vct,
      ENI.ENI_DENORM_HIERARCHIES vbh,
      bil_bi_fst1_pg_mv smry1
    WHERE
      smry1.product_category_id = vbh.child_id
      AND vbh.object_type = 'CATEGORY_SET'
      AND vbh.object_id = vct.category_set_id
      AND vbh.dbi_flag = 'Y'
      AND vct.functional_area_id = 11
    GROUP BY
      txn_time_id,
      txn_period_type_id,
      effective_time_id,
      effective_period_type_id,
      vbh.parent_id,
      (CASE WHEN vbh.parent_id = vbh.child_id THEN 1 ELSE 0 END),
      credit_type_id,
      parent_sales_group_id,
      sales_group_id,
      salesrep_id)