create or replace view v_salesmonitorpercustomer as
select b.INVOICEHEADER_ID, b.PARENTINVOICEHEADER_ID, b.INVOICENUMBER,
  b.isconsolidateinv,
  b.year, b.week, b.FREQUENCY,
  (select sum(
          case
           when ili.adjustmentreason_id is not null then
              ili.adjustedamount
            else
              ili.amount
          end) 
  from invoiceline ili where ili.invoiceheader_id = b.invoiceheader_id
  and  coalesce(ili.processingbusinessunit_id, b.processingbusinessunit_id) = b.processingbusinessunit_id  and ili.extend = 'Y') paythisamount,
  nvl((select sum(ili.amount) from invoiceline ili where ili.invoiceheader_id = b.invoiceheader_id
  and ili.invoicelinetype_id <> 18 and coalesce(ili.processingbusinessunit_id, b.processingbusinessunit_id) = b.processingbusinessunit_id
  and ili.extend = 'Y'), 0) totalamountexclvat,
  b.processingbusinessunit_id,
  b.procbucode, b. procbudesc,
  b.plant, b.plantname,b.invoicingBU_id,
  b.businessunit, b.businessunitdesc,
  b.invoicedate,
  b.depot, b.depotdesc,
  b.routenumber, b.routedesc,
  b.state,  b.marketsegment,
  b. marketsegmentdesc,
  b.siccode, b.masteraccountcust,
  b.masteraccountnumber, b.masteraccount,
  b.chain, b.chainname,
  b.customernumber,b.customername, b.LANGUAGECODE
from V_SALESMONITORPERCUSTOMER_aux b
group by b.INVOICEHEADER_ID, b.PARENTINVOICEHEADER_ID, b.INVOICENUMBER,b.isconsolidateinv,
  b.year, b.week, b.FREQUENCY,
  b.processingbusinessunit_id,
  b.procbucode, b. procbudesc,
  b. plant, b.plantname,b.invoicingBU_id,
  b.businessunit, b.businessunitdesc,
  b.invoicedate,
  b.depot, b.depotdesc,
  b.routenumber, b.routedesc,b.state,  b.marketsegment,
  b. marketsegmentdesc,
  b.siccode, b.masteraccountcust,
  b.masteraccountnumber, b.masteraccount,
  b.chain, b.chainname,
  b.customernumber,b.customername, b.LANGUAGECODE
/