create or replace view v_residualvalueperwearerprod as
select
  cu.customernumber, cu.name customerName, address.street, address.zipcode, address.city, weinv.wearerinventory_id
, we.wearernumber, we.fullname wearerfullname, we.firstname wearerfirstname, we.searchname wearersearchname
, prd.product_id, prd.code productCode, sz.code sizeCode, weinv.dateactive, weinv.dateinactive
, ( select product_desc.description from product_desc where product_desc.product_id = prd.product_id
  and product_desc.language_id = languages.language_id ) productDesc
, weinv.qtytobereturnedreplacement
, coalesce(weinv.qtytobereturneddecreasemax,0) +
    coalesce((select count(*)
    from uniqueitemflag uif, automaticflag afl
    where uqnp.uniqueitem_id=uif.uniqueitem_id and uif.flag_id=afl.flag_id
       and afl.event='Return to stock because of decrease max inventory'),0) qtytobereturneddecreasemax
, UQ.UNIQUEITEM_ID, uq.primaryid, uqfx.lastissuedate, uqfx.daysincircprevissue, uq.lastscandate
, currency.invoiceamountdecimals, languages.code languageCode
from
  customer cu, wearer we, weareremployment wey, wearerinventory weinv
, uniqueitemnonpool uqnp, uniqueitem uq, uniqueitemfixedinfo uqfx
, product prd, sizedefinition sz, address, currency, languages
where
    cu.customer_id = we.customer_id
and we.wearer_id = wey.wearer_id
    and wey.weareremployment_id = weinv.weareremployment_id
        --and weinv.product_id = prd.product_id
        --and weinv.sizedefinition_id = sz.sizedefinition_id
            and weinv.wearerinventory_id = uqnp.wearerinventory_id
            and weinv.weareremployment_id = uqnp.weareremployment_id
                and uqnp.uniqueitem_id = uq.uniqueitem_id
                    and uq.customerowned = 'N'
                    and uq.uniqueitem_id = uqfx.uniqueitem_id
        and uq.product_id = prd.product_id
        and uq.sizedefinition_id = sz.sizedefinition_id
and cu.visitaddress_id = address.address_id
and cu.currency_id = currency.currency_id
;
/