MATERIALIZED VIEW: APPS.POA_API_001_MV

Source

select /* 12.0: bug#4526784, bug 5248132 */
grouping_id(
  org_id,
  supplier_id,
  commodity_id,
  category_id,
  po_item_id,
  base_uom,
  company_id,
  cost_center_id,
  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,
grouping_id(category_id, po_item_id) aggregation_level,
org_id,
supplier_id,
commodity_id,
category_id,
po_item_id,
base_uom,
company_id,
cost_center_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
company_id,
cost_center_id,
rollup((commodity_id, category_id, supplier_id, org_id), (po_item_id, base_uom)),
rollup(ent_period_id, week_id, day_id),
ent_qtr_id