create or replace view v_residualvaluepoolitems as
select cu.customer_id, cu.customernumber, cu.name customername, cad.street, cad.zipcode, cad.city, dp.department_id, dp.departmentnumber,
       dp.description departmentdesc, prd.product_id, prd.code productcode, sz.code sizecode,
       (select sdd.description
          from sizedefinition_desc sdd
         where sdd.sizedefinition_id = sz.sizedefinition_id
           and sdd.language_id = lan.language_id) sizedesc,
       (select prde.description
          from product_desc prde
         where prde.product_id = prd.product_id
           and prde.language_id = lan.language_id) productdesc, 
       uq.uniqueitem_id, uq.primaryid, uqfx.lastissuedate, uqfx.daysincircprevissue,
       uq.lastscandate, uq.washestotal, cur.invoiceamountdecimals, lan.code languagecode, rp.reportparam_id,
       (select p.code
           from (select fl.code, uif.uniqueitem_id, uif.startdate
                   from uniqueitemflag uif, flag fl
                  where fl.flag_id = uif.flag_id
                  order by fl.priority, fl.code) p
          where rownum = 1
            and p.uniqueitem_id = uq.uniqueitem_id
            and p.startdate >= rp.date_value1) display_flag, 
       ceil((uqfx.daysincircprevissue + (rp.date_value1 - uqfx.lastissuedate)) / 7) weeksincirc,
       case
         when rsc.calculationbasis = 1 -- 1=weeks in use, 2=washes
          then
          get_depreciation_percent(rsc.residualvaluescheme_id, ceil((uqfx.daysincircprevissue + (rp.date_value1 - uqfx.lastissuedate)) / 7))
         else
          get_depreciation_percent(rsc.residualvaluescheme_id, uq.washestotal)
       end depreciation,
       pack_price.replacement_price(cu.customer_id, prd.product_id, sz.sizedefinition_id, fm.finishingmethod_id, rp.date_value1) replacementprice, 
       coalesce((select max(1)
                   from uniqueitemloss uil, invoiceheader ih 
                  where uil.uniqueitem_link_id = uq.uniqueitem_id and uil.lossstatus = 1 
                    and uil.lossinvoiceheader_id = ih.invoiceheader_id and ih.invoicenumber <> 0), 0) isInvoiced
  from uniqueitem uq
       left outer join finishingmethod fm on fm.finishingmethod_id = uq.finishingmethod_id
       join distributionpoint cp on uq.distributionpoint_id = cp.distributionpoint_id
       join department dp on cp.department_id = dp.department_id
       join customer cu on dp.customer_id = cu.customer_id
       join address cad on cu.visitaddress_id = cad.address_id
       join currency cur on cu.currency_id = cur.currency_id
       join uniqueitemfixedinfo uqfx on uq.uniqueitem_id = uqfx.uniqueitem_id
       join product prd on uq.product_id = prd.product_id
       join sizedefinition sz on uq.sizedefinition_id = sz.sizedefinition_id, 
       residualvaluescheme rsc, reportparam rp, languages lan
 where uq.customerowned = 'N'
   and uq.poolitem = 1
   and prd.uniquepoolitem = 'Y'
   and uq.status_id in (select s1.status_id
                          from status s1
                         where s1.statustype = 'UI'
                           and s1.code in ('20', '30'))
   and uq.stay_id in (select s2.stay_id
                        from stay s2
                       where s2.staytype = 1
                         and s2.staynr in (20, 27))
   and cu.customernumber = rp.number_value
   and lan.code = rp.string_value
   and dp.departmentnumber >= rp.number_value1
   and dp.departmentnumber <= rp.number_value2
   and prd.code >= rp.string_value1
   and prd.code <= rp.string_value2
   and ((rp.string_value3 = ' ' and rp.string_value4 = 'ZZZZZZ') --no filter on Fin Meth
       or (fm.code >= rp.string_value3 and fm.code <= rp.string_value4))
   and rsc.residualvaluescheme_id = coalesce((select max(plpc.residualvaluescheme_id)
                                               from productlistpercustomer plpc
                                              where plpc.customer_id = cu.customer_id
                                                and plpc.product_id = prd.product_id
                                                and (plpc.sizedefinition_id = sz.sizedefinition_id or
                                                    plpc.sizedefinition_id = (select sdi.sizedefinition_id from sizedefinition sdi where sdi.code = '*'))
                                                and plpc.finishingmethod_id = fm.finishingmethod_id
                                                and plpc.dateactive <= rp.date_value1
                                                and plpc.dateinactive >= rp.date_value1), prd.defaultresidualvaluescheme_id)
   and rsc.code >= rp.string_value7
   and rsc.code <= rp.string_value8
   and (((rp.string_value5 = ' ') and (rp.string_value6 = 'ZZZZZZ')) -- if all flags then no filter on items with a flag
       or ((rp.string_value5 <> ' ') or (rp.string_value6 <> 'ZZZZZZ')) and -- if not all flags then only select uniqueitems that have a flag in the selected range
       exists (select 1
                 from uniqueitemflag uif, flag f
                where uif.uniqueitem_id = uq.uniqueitem_id
                  and f.flag_id = uif.flag_id
                  and uif.startdate >= rp.date_value1
                  and f.code >= rp.string_value5
                  and f.code <= rp.string_value6)) 
/
