create or replace view v_revenuepercustomer as
select ih.invoicedate,
cr.code currencycode, ih.deltocity, ih.city billtocity,
  dcu.customernumber deltocustomernumber, dcu.name deltocustomername,
  bcu.customernumber billtocustomernumber, bcu.name billtocustomername,
  (select sum(ih1.paythisamount) from invoiceheader ih1
     where ih1.invoicedate between rp.date_value1 and rp.date_value2
       and ih1.deltocustomer_link_id = ih.deltocustomer_link_id
       and ih1.status_id = ih.status_id
       AND ih1.documenttype = 1
       and ih1.deltocity = ih.deltocity
       and ih1.invoicebusinessunit_id = ih.invoicebusinessunit_id) dc_totalamount,
  (select sum(ih1.totalamountexclvat) from invoiceheader ih1
     where ih1.invoicedate between rp.date_value1 and rp.date_value2
       and ih1.deltocustomer_link_id = ih.deltocustomer_link_id
       and ih1.status_id = ih.status_id
       AND ih1.documenttype = 1
       and ih1.deltocity = ih.deltocity
       and ih1.invoicebusinessunit_id = ih.invoicebusinessunit_id) dc_totalinvoiceamountexclvat,
  (select sum(ih1.paythisamount) from invoiceheader ih1
     where ih1.invoicedate between rp.date_value1 and rp.date_value2
       and ih1.billtocustomer_link_id = ih.billtocustomer_link_id
       and ih1.status_id = ih.status_id
       AND ih1.documenttype = 1
       and ih1.deltocity = ih.deltocity
       and ih1.invoicebusinessunit_id = ih.invoicebusinessunit_id) bc_totalamount,
  (select sum(ih1.totalamountexclvat) from invoiceheader ih1
     where ih1.invoicedate between rp.date_value1 and rp.date_value2
       and ih1.billtocustomer_link_id = ih.billtocustomer_link_id
       and ih1.status_id = ih.status_id
       AND ih1.documenttype = 1
       and ih1.deltocity = ih.deltocity
       and ih1.invoicebusinessunit_id = ih.invoicebusinessunit_id) bc_totalinvoiceamountexclvat,
  --ih.invoiceyear inv_year, ih.invoiceweek inv_week,
  YEAR(ih.invoicedate) inv_year, getisoweek(ih.invoicedate) inv_week,
	MONTH(ih.invoicedate) inv_month,
  ih.paythisamount, ih.totalamountexclvat,
  rp.reportparam_id
  , (select a.zipcode from address a where dcu.visitaddress_id=a.address_id) DeliveryZipCode --todo 4675
  , ih.parentinvoiceheader_id, ih.invoiceheader_id --todo 22165
  , ms.code mscode
from invoiceheader ih, customer dcu, customer bcu, currency cr, status st,
  marketsegment ms, salesarea sa, reportparam rp
where ih.deltocustomer_link_id = dcu.customer_id
  and ih.billtocustomer_link_id = bcu.customer_id
  and dcu.currency_id = cr.currency_id
  and dcu.marketsegment_id = ms.marketsegment_id
  and dcu.salesarea_id = sa.salesarea_id
  and ih.status_id = st.status_id
  and st.code = '2'
  AND ih.documenttype = 1 --invoice
  and ih.invoicedate between rp.date_value1 and rp.date_value2
  and ih.invoicebusinessunit_id = rp.number_value1
  and sa.code between rp.string_value1 and rp.string_value2
  --and ms.code between rp.string_value3 and rp.string_value4 --commented with todo 22140
  and ih.billtocustomernumber between rp.number_value2 and rp.number_value4
;
/