create or replace view v_quantitiesrestockingnew as
select wus.websession_id,
  wc.webuser_id, WD.DEPARTMENT_ID WEBUSERDEPARTMENT_ID, dl.DEPARTMENT_ID,
  cl.name customername, cl.customernumber,
  dl.description departmentdesc, dl.departmentnumber,
  dpl.description distributionpointdesc, dpl.distributionpointnumber,
  dh.amountdelivered,
  (case when (select ps.theoreticalweight from productsize ps where ps.product_id = p.product_id
              and ps.sizedefinition_id = sl.sizedefinition_id) = 0
        then  p.theoreticalweight
        else (select ps.theoreticalweight from productsize ps where ps.product_id = p.product_id
              and ps.sizedefinition_id = sl.sizedefinition_id)
   end) theoreticalweight,
  (select pld.description from product_link_desc pld
    where pld.product_link_id = pl.product_link_id and pld.language_id = languages.language_id) productdesc,
  pl.product_id PRODUCT_ID, pl.code productcode, pg.productgroup_id PRODUCTGROUP_ID, pl.productgroupcode,
  sl.code sizecode,
  (select sld.description from sizedefinition_desc sld where sld.sizedefinition_id = sl.sizedefinition_id
    and sld.language_id = languages.language_id) sizedesc,
  f.code finishingmethodcode, a.activity_id ACTIVITY_ID, a.code activitycode,
  (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id = f.finishingmethod_id
    and fmd.language_id = languages.language_id) finishingmethoddesc,
  F_RESTOCKINGPRICE_ANG(cl.customer_id, pl.product_id, sl.sizedefinition_id, dh.finishingmethod_id, rp.date_value1, p.typeofmerchandise) price,
  dh.pieceweight,
  F_GETPOUNDPRICE(p.product_id, f.finishingmethod_id, sl.sizedefinition_link_id, cu.customer_id, p.weighingcategory_id, rp.date_value1) poundPrice,
  coalesce(cr.invoiceamountdecimals, 0) decimals, CR.CODE CURRENCYCODE,
  languages.code languagecode, rp.reportparam_id,
  coalesce(
    F_GETCOSTREPORTPRICE(DH.CUSTOMER_LINK_ID, p.product_id, SL.SIZEDEFINITION_LINK_ID, f.finishingmethod_id, rp.date_value1,
    p.typeofmerchandise, p.weighingcategory_id, cr.decimals),0) costprice,
    (select plpc.budgetrelevant
          from productlistpercustomer plpc
         where plpc.customer_id = cu.customer_id
           and plpc.product_id = dh.product_link_id
           and plpc.sizedefinition_id = dh.sizedefinition_link_id
           and plpc.finishingmethod_id = dh.finishingmethod_id) budgetrelevant
from deliveryhistory dh,
  product_link pl
    left outer join (product p
      left outer join (productgroup pg
        left outer join activity a on a.activity_id = pg.activity_id)
        on pg.productgroup_id = p.productgroup_id)
      on p.product_id = pl.product_id,
  finishingmethod f, sizedefinition_link sl, department_link dl, distributionpoint_link dpl,
  customer_link cl
    left outer join (customer cu
      left outer join currency cr on cu.currency_id = cr.currency_id)
    on cl.customer_id = cu.customer_id,
  reportparam rp, languages,
  webusercustomer wc
    left outer join webuserdepartment wd on (wc.webusercustomer_id = wd.webusercustomer_id),
  webusersession wus
where dh.product_link_id = pl.product_link_id
  and dh.sizedefinition_link_id = sl.sizedefinition_link_id
  and dh.customer_link_id = cl.customer_link_id
  and dh.finishingmethod_id = f.finishingmethod_id
  and dh.department_link_id = dl.department_link_id
  and dh.distributionpoint_link_id = dpl.distributionpoint_link_id
  and dh.deliverydate <= rp.date_value1
  and dh.deliverydate >= rp.date_value2
  and wc.customer_id = cl.customer_id
  and wus.customer_id = cl.customer_id
  and dh.packindication <> 2
/