MATERIALIZED VIEW: APPS.POA_API_BS_MV

Source

select /* 12.0: bug#4526784 */
grouping_id(
  org_id,
  supplier_id,
  supplier_site_id,
  inv_d_created_by,
  commodity_id,
  category_id,
  po_item_id,
  buyer_id,
  ship_to_org_id,
  base_uom,
  ent_year_id,
  ent_qtr_id,
  ent_period_id,week_id,day_id
) grp_id,
decode(
  grouping_id(ent_year_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,
  15,ent_year_id
) time_id,
decode(
  grouping_id(ent_year_id,ent_qtr_id,ent_period_id,week_id,day_id),
  0,1,1,16,3,32,7,64,15,128) period_type_id,
grouping_id(category_id, po_item_id) aggregation_level,
org_id,
supplier_id,
supplier_site_id,
inv_d_created_by inv_d_created_by,
commodity_id,
category_id,
po_item_id,
buyer_id,
ship_to_org_id,
base_uom,
ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
day_id day_id,
sum(amount_b) amount_b,
sum(amount_g) amount_g,
sum(amount_sg) amount_sg,
count(amount_b) c_amount_b,
count(amount_g) c_amount_g,
count(amount_sg) c_amount_sg,
count(*) c_total
from
poa_api_bs_j_mv
where approved_flag = 'A'
AND   invoice_type <> 'EXPENSE REPORT'
AND   line_type_lookup_code in ( 'ITEM', 'ACCRUAL', 'ERV', 'IPV')
group by
org_id,
commodity_id,
rollup(category_id,
      (supplier_id, supplier_site_id,
               inv_d_created_by,
               po_item_id, buyer_id,
               ship_to_org_id,base_uom)),
rollup(ent_qtr_id,ent_period_id, week_id, day_id),
ent_year_id