MATERIALIZED VIEW: APPS.POA_POD_002_MV

Source

select /* 12.0: bug#4526784 */
grouping_id(time_id,
  org_id,
  commodity_id,
  base_uom,
  supplier_id,
  category_id,
  po_item_id,
  com.parent_company_id,
  com.next_level_company_id,
  cc.parent_cc_id,
  cc.next_level_cc_id,
  contract_type) grp_id,
f.time_id,
f.org_id,
f.supplier_id,
f.category_id,
f.po_item_id,
com.parent_company_id,
com.next_level_company_id company_id,
cc.parent_cc_id,
cc.next_level_cc_id cost_center_id,
sum(f.purchase_amt_b) purchase_amt_b,
sum(f.purchase_amt_g) purchase_amt_g,
sum(f.purchase_amt_sg) purchase_amt_sg,
sum(f.p_contract_amt_b) p_contract_amt_b,
sum(f.p_contract_amt_g) p_contract_amt_g,
sum(f.p_contract_amt_sg) p_contract_amt_sg,
sum(f.n_contract_amt_b) n_contract_amt_b,
sum(f.n_contract_amt_g) n_contract_amt_g,
sum(f.n_contract_amt_sg) n_contract_amt_sg,
sum(f.p_savings_amt_b) p_savings_amt_b,
sum(f.p_savings_amt_g) p_savings_amt_g,
sum(f.p_savings_amt_sg) p_savings_amt_sg,
count(f.purchase_amt_b) c_purchase_amt_b,
count(f.purchase_amt_g) c_purchase_amt_g,
count(f.purchase_amt_sg) c_purchase_amt_sg,
count(f.p_contract_amt_b) c_p_contract_amt_b,
count(f.p_contract_amt_g) c_p_contract_amt_g,
count(f.p_contract_amt_sg) c_p_contract_amt_sg,
count(f.n_contract_amt_b) c_n_contract_amt_b,
count(f.n_contract_amt_g) c_n_contract_amt_g,
count(f.n_contract_amt_sg) c_n_contract_amt_sg,
count(f.p_savings_amt_b) c_p_savings_amt_b,
count(f.p_savings_amt_g) c_p_savings_amt_g,
count(f.p_savings_amt_sg) c_p_savings_amt_sg,
count(*) c_total,
f.commodity_id,
f.base_uom,
sum(f.quantity) quantity,
sum(f.contract_amt_b) contract_amt_b,
sum(f.contract_amt_g) contract_amt_g,
sum(f.contract_amt_sg) contract_amt_sg,
sum(f.above_contract_amt_b) above_contract_amt_b,
sum(f.above_contract_amt_g) above_contract_amt_g,
sum(f.above_contract_amt_sg) above_contract_amt_sg,
sum(f.below_contract_amt_b) below_contract_amt_b,
sum(f.below_contract_amt_g) below_contract_amt_g,
sum(f.below_contract_amt_sg) below_contract_amt_sg,
count(f.quantity) c_quantity,
count(f.contract_amt_b) c_contract_amt_b,
count(f.contract_amt_g) c_contract_amt_g,
count(f.contract_amt_sg) c_contract_amt_sg,
count(f.above_contract_amt_b) c_above_contract_amt_b,
count(f.above_contract_amt_g) c_above_contract_amt_g,
count(f.above_contract_amt_sg) c_above_contract_amt_sg,
count(f.below_contract_amt_b) c_below_contract_amt_b,
count(f.below_contract_amt_g) c_below_contract_amt_g,
count(f.below_contract_amt_sg) c_below_contract_amt_sg,
f.contract_type,
grouping_id(org_id) aggregation_level
from poa_pod_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,
com.next_level_company_id,
cc.parent_cc_id,
cc.next_level_cc_id,
f.time_id,
f.category_id,
f.commodity_id,
rollup((
f.org_id,
f.supplier_id,
f.po_item_id,
f.base_uom,
f.contract_type))