MATERIALIZED VIEW: APPS.POA_IDL_001_MV

Source

select /* 12.0: bug#4526784 */
grouping_id(
  f.org_id,
  f.supplier_id,
  f.inv_d_created_by,
  map.parent_company_dim_id,
  map.parent_cost_center_dim_id,
  f.ent_qtr_id,
  f.ent_period_id,
  f.week_id,
  f.day_id
) grp_id,
decode(
  grouping_id(f.ent_qtr_id,f.ent_period_id,f.week_id,f.day_id),
  0,f.day_id,
  1,f.week_id,
  3,f.ent_period_id,
  7,f.ent_qtr_id
) time_id,
grouping_id(f.supplier_id) aggregation_level,
f.org_id,
f.supplier_id,
f.inv_d_created_by,
map.parent_company_dim_id company_id,
map.parent_cost_center_dim_id cost_center_id,
f.ent_qtr_id,
f.ent_period_id,
f.week_id,
f.day_id day_id,
sum(amount_b) amount_b,
sum(amount_g) amount_g,
sum(amount_sg) amount_sg,
sum(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_b), amount_b)) leakage_amount_b,
sum(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_g), amount_g)) leakage_amount_g,
sum(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_sg), amount_sg)) leakage_amount_sg,
count(amount_b) c_amount_b,
count(amount_g) c_amount_g,
count(amount_sg) c_amount_sg,
count(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_b), amount_b)) c_leakage_amount_b,
count(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_g), amount_g)) c_leakage_amount_g,
count(decode(po_matched_flag, 'Y', 0, 'N', decode(receipt_required_flag, null,0, amount_sg), amount_sg)) c_leakage_amount_sg,
count(*) c_total
from
poa_idl_bs_j_mv f,
FII.FII_COM_CC_DIM_MAPS map
where f.company_id = map.child_company_id
and f.cost_center_id = map.child_cost_center_id
group by
map.parent_company_dim_id, map.parent_cost_center_dim_id,
grouping sets((f.supplier_id, f.org_id, f.inv_d_created_by),()),
rollup(f.ent_period_id, f.week_id, f.day_id), ent_qtr_id