MATERIALIZED VIEW: APPS.POA_MID_BS_MV

Source

select /* 12.0: bug#4526784 */
grouping_id(
  org_id,
  supplier_id,
  supplier_site_id,
  inv_d_created_by,
  ent_qtr_id,
  ent_period_id,
  week_id,
  day_id
) grp_id,
decode(
  grouping_id(ent_qtr_id,ent_period_id,week_id,day_id),
  0,day_id,
  1,week_id,
  3,ent_period_id,
  7,ent_qtr_id
) time_id,
decode(
  grouping_id(ent_qtr_id, ent_period_id,week_id,day_id),
  0,1,
  1,16,
  3,32,
  7,64) period_type_id,
grouping_id(supplier_id, inv_d_created_by) aggregation_level,
org_id,
supplier_id,
supplier_site_id,
inv_d_created_by inv_d_created_by,
ent_qtr_id,
ent_period_id,
week_id,
day_id day_id,
sum(dist_count) dist_count,
count(dist_count) c_dist_count,
sum(DECODE(SOURCE, 'INVOICE GATEWAY', dist_count,
		  'Manual Invoice Entry', dist_count,
		  'XpenseXpress', dist_count, 0)) manual_dist_count,
count(DECODE(SOURCE, 'INVOICE GATEWAY', dist_count,
		  'Manual Invoice Entry', dist_count,
		  'XpenseXpress', dist_count, 0)) c_manual_dist_count,
count(*) c_total
from
poa_mid_bs_j_mv
group by
org_id,
rollup(supplier_id, (inv_d_created_by, supplier_site_id)),
rollup(ent_period_id, week_id, day_id),
ent_qtr_id