MATERIALIZED VIEW: APPS.FII_AP_INV_B_MV

Source

select /* 12.0: bug#4526784 */ inv.org_id org_id,
       inv.supplier_id supplier_id,
       inv.invoice_id invoice_id,
       inv.invoice_number invoice_number,
       inv.invoice_type invoice_type,
       inv.invoice_date invoice_date,
       inv.entered_date entered_date,
       MIN(f.due_date) due_date,
       trunc(sysdate)-MIN(f.due_date) days_past_due,
       inv.invoice_currency_code trx_currency_code,
       inv.invoice_amount invoice_amt_t,
       inv.base_amount invoice_amt_b,
       inv.prim_amount invoice_amt_prim_g,
       inv.sec_amount invoice_amt_sec_g,
       sum(f.amount_remaining_b) unpaid_amt_b,
       sum(f.prim_amount_remaining) unpaid_amt_prim_g,
       sum(f.sec_amount_remaining) unpaid_amt_sec_g,
       sum(f.past_due_amount_b) past_due_amt_b,
       sum(f.prim_past_due_amount) past_due_amt_prim_g,
       sum(f.sec_past_due_amount) past_due_amt_sec_g,
       nvl(hold.on_hold,'N') on_hold,
       nvl(hold1.days_on_hold,0) days_on_hold,
       sum(f.discount_lost_b) discount_lost_b,
       sum(f.prim_discount_lost) discount_lost_prim_g,
       sum(f.sec_discount_lost) discount_lost_sec_g,
       inv.terms_id terms_id
  from FII.FII_AP_INVOICE_B inv, FII.FII_AP_PAY_SCHED_B f,
  (select invoice_id, 'Y' on_hold
   from fii_ap_hhist_ib_mv
   where hold_date <= trunc(sysdate) and (release_date > trunc(sysdate) or release_date is null)
  ) hold,
  (select invoice_id, sum(days_on_hold) days_on_hold
   from fii_ap_hhist_ib_mv
   group by invoice_id
  ) hold1
  where (inv.fully_paid_date is null or inv.fully_paid_date > trunc(sysdate))
  and inv.invoice_type <> 'PREPAYMENT'
  and inv.invoice_id = f.invoice_id
  and inv.invoice_id = hold.invoice_id (+)
  and inv.invoice_id = hold1.invoice_id (+)
  having sum(f.past_due_amount) <> 0
  group by inv.org_id,
       inv.supplier_id,
       inv.invoice_id,
       inv.invoice_number,
       inv.invoice_type,
       inv.invoice_date,
       inv.entered_date,
       inv.invoice_currency_code,
       inv.invoice_amount,
       inv.base_amount,
       inv.prim_amount,
       inv.sec_amount,
       nvl(hold.on_hold,'N'),
       nvl(hold1.days_on_hold,0),
       inv.terms_id