MATERIALIZED VIEW: APPS.FII_AP_HCAT_B_MV

Source

select /* 12.0: bug#4526784 */ f.rowid rowid_1,
         'H' marker,
         f.org_id org_id,
         f.supplier_id supplier_id,
         f.time_id time_id,
         1 period_type_id,
         decode(hold_category, 'VARIANCE', 1, 0) variance_count,
         decode(hold_category, 'PO MATCHING', 1, 0) po_matching_count,
         decode(hold_category, 'INVOICE', 1, 0) invoice_count,
         decode(hold_category, 'USER DEFINED', 1, 0) user_defined_count,
         (case when hold_category <> 'VARIANCE' and hold_category <> 'PO MATCHING'
          and hold_category <> 'INVOICE' and hold_category <> 'USER DEFINED' then 1 else 0 end) other_count
  from FII.FII_AP_INV_HOLDS_B f
  where hold_date is not null
  union all
  select /* 12.0: bug#4526784 */ f.rowid rowid_1,
         'R' marker,
         f.org_id org_id,
         f.supplier_id supplier_id,
         to_number(to_char(f.release_date,'j')) time_id,
         1 period_type_id,
         decode(hold_category, 'VARIANCE', -1, 0) variance_count,
         decode(hold_category, 'PO MATCHING', -1, 0) po_matching_count,
         decode(hold_category, 'INVOICE', -1, 0) invoice_count,
         decode(hold_category, 'USER DEFINED', -1, 0) user_defined_count,
         (case when hold_category <> 'VARIANCE' and hold_category <> 'PO MATCHING'
          and hold_category <> 'INVOICE' and hold_category <> 'USER DEFINED' then -1 else 0 end) other_count
  from FII.FII_AP_INV_HOLDS_B f
  where release_date is not null