MATERIALIZED VIEW: APPS.FII_AR_DISPUTES_BASE_MV

Source

SELECT /*+ use_hash(ag) use_hash(fday) use_hash(mv) */
      fday.ent_year_id          ent_year_id,
      fday.ent_qtr_id           ent_qtr_id,
      fday.ent_period_id        ent_period_id,
      fday.week_id              ent_week_id,
      fday.report_date_julian   report_date_julian,
      GROUPING_ID(mv.party_id,
                  mv.party_id,
                  ag.bill_to_customer_id,
                  mv.collector_id,
                  ag.org_id,
                  mv.class_code,
                  mv.class_category,
                  fday.ent_year_id,
                  fday.ent_qtr_id,
                  fday.ent_period_id,
                  fday.week_id,
                  fday.report_date_julian) gid,
      DECODE(GROUPING_ID(fday.ent_year_id, fday.ent_qtr_id,
             fday.ent_period_id,fday.week_id, fday.report_date_julian),
             0, fday.report_date_julian,
             1, fday.week_id,
             3, fday.ent_period_id,
             7, fday.ent_qtr_id,
            15, fday.ent_year_id) time_id,
      DECODE(GROUPING_ID(fday.ent_year_id, fday.ent_qtr_id,
             fday.ent_period_id,fday.week_id, fday.report_date_julian),
             0, 1,
             1, 16,
             3, 32,
             7, 64,
            15, 128)                         period_type_id,
      mv.party_id                            parent_party_id,
      mv.party_id                            party_id,
      ag.bill_to_customer_id                 cust_account_id,
      mv.collector_id                        collector_id,
      ag.org_id                              org_id,
      mv.class_code                          class_code,
      mv.class_category                      class_category,
      SUM(ag.current_dispute_amount_func)    current_dispute_amount_func,
      SUM(ag.current_dispute_amount_prim)    current_dispute_amount_prim,
      SUM(ag.current_dispute_amount_sec)     current_dispute_amount_sec,
      SUM(ag.current_dispute_count)          current_dispute_count,
      SUM(ag.past_due_dispute_amount_func)   past_due_dispute_amount_func,
      SUM(ag.past_due_dispute_amount_prim)   past_due_dispute_amount_prim,
      SUM(ag.past_due_dispute_amount_sec)    past_due_dispute_amount_sec,
      SUM(ag.past_due_dispute_count)         past_due_dispute_count,
      SUM(ag.current_dispute_amount_func
         +ag.past_due_dispute_amount_func)   total_dispute_amount_func,
      SUM(ag.current_dispute_amount_prim
         +ag.past_due_dispute_amount_prim)   total_dispute_amount_prim,
      SUM(ag.current_dispute_amount_sec
         +ag.past_due_dispute_amount_sec)    total_dispute_amount_sec,
      COUNT(ag.current_dispute_amount_func)  current_dispute_amt_func_ct,
      COUNT(ag.current_dispute_amount_prim)  current_dispute_amt_prim_ct,
      COUNT(ag.current_dispute_amount_sec)   current_dispute_amt_sec_ct,
      COUNT(ag.current_dispute_count)        current_dispute_count_ct,
      COUNT(ag.past_due_dispute_amount_func) past_due_dispute_amt_func_ct,
      COUNT(ag.past_due_dispute_amount_prim) past_due_dispute_amt_prim_ct,
      COUNT(ag.past_due_dispute_amount_sec)  past_due_dispute_amt_sec_ct,
      COUNT(ag.past_due_dispute_count)       past_due_dispute_count_ct,
      COUNT(ag.current_dispute_amount_func
           +ag.past_due_dispute_amount_func) total_dispute_amt_func_ct,
      COUNT(ag.current_dispute_amount_prim
           +ag.past_due_dispute_amount_prim) total_dispute_amt_prim_ct,
      COUNT(ag.current_dispute_amount_sec
           +ag.past_due_dispute_amount_sec)  total_dispute_amt_sec_ct,
      COUNT(*)                               ct
  FROM FII.FII_AR_AGING_DISPUTES ag,
       FII.FII_TIME_DAY fday,
	   APPS.FII_AR_DIMENSIONS_MV mv
  WHERE ag.time_id = fday.report_date_julian
  AND   ag.bill_to_customer_id = mv.cust_account_id
  AND   ag.bill_to_site_use_id = mv.site_use_id
  GROUP BY ag.bill_to_customer_id,
           ag.org_id,
           mv.party_id,
           mv.collector_id,
           mv.class_code,
           mv.class_category,
           ROLLUP (fday.ent_year_id,
                   fday.ent_qtr_id,
                   fday.ent_period_id,
                   fday.week_id,
                   fday.report_date_julian)