CREATE OR REPLACE VIEW V_REPLACEMENTLIST AS
select ufi.enddatefullrent, wr.dateactive, wr.dateinactive
       , sa.code SalesArea_code, cu.customernumber, dpt.departmentnumber, wr.wearernumber
       , pg.code ProdGroup_code, pr.code Prod_code
       , we.sequencenumber, sd.code Size_code, ui.primaryid, cu.name, dpt.description Department_desc
       , dip.distributionpointnumber, dip.description DistribPoint_desc
       , (select prd.description from product_desc prd where pr.product_id = prd.product_id and prd.language_id = lan.language_id) Prod_desc
       , wr.fullname, wr.searchname, wr.firstname, lo.bank, lo.locker, ufi.lastissuedate
       , pcu.decimals
       , case when pcu.currency_id <> su.currency_id then scu.exchangerate
         else 1
         end exchangerate
       , case when rp.string_value = 'Y'
         then /*(SELECT spp.purchaseprice
               FROM supplierproductprice spp, productsizepersupproduct psp, supplierproduct sp
               WHERE psp.product_id = pr.product_id and psp.sizedefinition_id = sd.sizedefinition_id and psp.supplierproduct_id = sp.supplierproduct_id
                     AND spp.supplierproduct_id = sp.supplierproduct_id and sp.supplier_id = pr.supplier_id
                     AND spp.startdate IN (SELECT MAX (spp.startdate)
                                           FROM supplierproductprice spp, productsizepersupproduct psp, supplierproduct sp
                                           WHERE psp.product_id = pr.product_id and psp.sizedefinition_id = sd.sizedefinition_id and psp.supplierproduct_id = sp.supplierproduct_id
                                           AND spp.supplierproduct_id = sp.supplierproduct_id and sp.supplier_id = pr.supplier_id
                                           AND spp.startdate < rp.date_value1))*/
              F_PURCHASEPRICE(pr.supplier_id, pr.product_id, sd.sizedefinition_id, rp.date_value1)
         else 0
         end purchaseprice
       , lan.code Language_code, bu.businessunit_id
       , rp.reportparam_id
from uniqueitem ui, uniqueitemnonpool unp, uniqueitemfixedinfo ufi, wearer wr, weareremployment we
     LEFT OUTER JOIN locker lo ON we.locker_id = lo.locker_id,
     department dpt, distributionpoint dip, customer cu, salesarea sa, productgroup pg, product pr, sizedefinition sd,
     supplier su, currency scu, currency pcu, plant pl, country co,
     languages lan, businessunit bu,
     reportparam rp
where unp.weareremployment_id = we.weareremployment_id and we.wearer_id = wr.wearer_id and wr.customer_id = cu.customer_id
      and cu.salesarea_id = sa.salesarea_id and dip.distributionpoint_id = we.distributionpoint_id and dip.department_id = dpt.department_id
      and ui.uniqueitem_id = unp.uniqueitem_id and ui.product_id = pr.product_id
      and pr.productgroup_id = pg.productgroup_id and ui.sizedefinition_id = sd.sizedefinition_id
      AND pr.supplier_id = su.supplier_id and su.currency_id = scu.currency_id
      AND ufi.uniqueitem_id = ui.uniqueitem_id
      and ufi.enddatefullrent >= rp.date_value1 and ufi.enddatefullrent <= rp.date_value2
      and bu.plant_id = pl.plant_id and pl.country_id = co.country_id and co.defaultcurrency_id = pcu.currency_id
      and rp.number_value = bu.businessunit_id
      and ((rp.number_value = cu.defaultbusinessunit_id) or
          exists (select * from customeractivities caa where caa.customer_id=cu.customer_id and caa.processbusinessunit_id = rp.number_value));
/