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  ili.processingbusinessunit_id=b.processingbusinessunit_id  and ili.extend='Y')paythisamount,
  (select sum(ili.amount) from invoiceline ili where ili.invoiceheader_id=b.invoiceheader_id
  and ili.invoicelinetype_id<>18 and ili.processingbusinessunit_id=b.processingbusinessunit_id
  and ili.extend='Y')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;
/