MATERIALIZED VIEW: APPS.POA_IDL_BS_MV

Source

select /* 12.0: bug#4526784,5143675 */
grouping_id(
  org_id,
  supplier_id,
  supplier_site_id,
  inv_d_created_by,
  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(supplier_id, inv_d_created_by) aggregation_level,
org_id,
supplier_id,
supplier_site_id,
inv_d_created_by inv_d_created_by,
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,
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
group by
org_id,
rollup(supplier_id, (inv_d_created_by, supplier_site_id)),
rollup(ent_period_id, week_id, day_id),
ent_qtr_id