MATERIALIZED VIEW: APPS.POA_API_002_MV

Source

select /* 12.0: bug#4526784 */
grouping_id(
  f.org_id,
  f.supplier_id,
  f.commodity_id,
  f.category_id,
  f.po_item_id,
  f.base_uom,
  com.parent_company_id,
  cc.parent_cc_id,
  com.next_level_company_id,
  cc.next_level_cc_id,
  f.time_id
) grp_id,
f.time_id,
f.org_id,
f.supplier_id,
f.commodity_id,
f.category_id,
f.po_item_id,
f.base_uom,
com.parent_company_id,
cc.parent_cc_id,
com.next_level_company_id company_id,
cc.next_level_cc_id cost_center_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,
grouping_id(org_id) aggregation_level
from poa_api_001_mv f,
FII.FII_COMPANY_HIERARCHIES com,
FII.FII_COST_CTR_HIERARCHIES cc
where f.aggregation_level = 0
and f.company_id = com.child_company_id
and f.cost_center_id = cc.child_cc_id
and com.is_leaf_flag              = 'N'
and com.aggregate_next_level_flag = 'Y'
and cc.is_leaf_flag              = 'N'
and cc.aggregate_next_level_flag = 'Y'
group by
com.parent_company_id,
cc.parent_cc_id,
com.next_level_company_id,
cc.next_level_cc_id,
f.time_id,
f.commodity_id,
f.category_id,
rollup((f.po_item_id,
f.base_uom,
f.org_id,
f.supplier_id))