create or replace view v_ragsperwearer as
select
  ragi.ragtimestamp, GETDATEONLY(ragi.ragtimestamp) ragDate, cu.customernumber, cu.name customerName, address.city
, de.departmentnumber, de.description departmentDesc, we.wearernumber, we.searchname wearerSearchname, we.fullname wearerFullname
, we.customeremployeenumber AdminNumber
, cast(CONCATENATEWITHBLANK(lk.bank, lk.locker) as varchar2(255)) banklocker
, gd.code garmentdispenser
, wef.code wearerFunctionCode, wef.description wearerFunctionDesc
, wemp.sequencenumber Employment, we.dateactive wearerDateActive, we.dateinactive wearerDateInactive
, GETDATEDIFF(GETCURRENTDATE(), we.dateactive)*GETDATEDIFF(we.dateinactive, GETCURRENTDATE()) wearerActive
, cc.code costcenterCode, cc.description costcenterDesc
, case when length(ragi.primaryid) = 24 and nvl((select ss.value from systemsetting ss where ss.plant_id = bu.plant_id and ss.name='USEURICODE'),0) = 1
    then pack_epc_sgtin96.decode(ragi.primaryid)
    else ragi.primaryid
  end primaryid
, prd.code productCode
, ( select product_desc.description from product_desc where product_desc.product_id = prd.product_id
  and product_desc.language_id = languages.language_id ) productDesc
, sz.code sizeCode
, ( select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sz.sizedefinition_id
  and sdd.language_id = languages.language_id ) sizeDesc
, ragi.firstissuedate, ragi.lastissuedate, ragi.washestotal, ragi.repairstotal, ragi.rewashtotal
, ragi.daysincirculation, GETROUNDTOINTEGER(ragi.daysincirculation/7) weeksInCirculation
, ( select count(*) from wearerinventorymodification weim, wearerinventory weinv
    where ragi.weareremployment_link_id = weinv.weareremployment_id
    and ragi.product_link_id = weinv.product_id
        and ragi.sizedefinition_link_id = weinv.sizedefinition_id
    and weim.wearerinventory_id = weinv.wearerinventory_id) modifications
, ragi.customerowned, reasoncode.Code reasonCode, ragreason.code ragreasonCode
, ( select ragreason_desc.description from ragreason_desc where ragreason_desc.ragreason_id = ragreason.ragreason_id
  and ragreason_desc.language_id = languages.language_id ) ragreasonDesc, ragi.residualvalue RIresidualvalue
, cu.language_id customerlanguage_id, languages.language_id, languages.code languageCode
, ragi.businessunit_id
, coalesce((select max(ochy.tobechargedpercentage) from outofcirculationhistory ochy, uniqueitem_link uil
   where ochy.weareremployment_link_id = wel.weareremployment_link_id and ochy.reasoncode_id = reasoncode.reasoncode_id and
  ochy.customer_link_id = cu.customer_id and ochy.distributionpoint_link_id = dip.distributionpoint_id and
  ochy.product_link_id = prd.product_id and ochy.ragreason_id = ragreason.ragreason_id and
  ochy.sizedefinition_link_id = sz.sizedefinition_id and
  ochy.uniqueitem_link_id = uil.uniqueitem_link_id and uil.raggeditem_id = ragi.raggeditem_id), 0) residualvalue
, ragi.timestamp
, (select su.username from systemuser su where su.systemuser_id = ragi.systemuser_id) System_User
from
  raggeditem ragi
    left outer join reasoncode on ragi.reasoncode_id = reasoncode.reasoncode_id,
    weareremployment_link wel left outer join weareremployment wemp on wel.weareremployment_id=wemp.weareremployment_id
    left outer join garmentdispenser gd on wemp.garmentdispenser_id = gd.garmentdispenser_id
    left outer join locker lk on wemp.locker_id = lk.locker_id
    left outer join wearerfunction wef on wemp.wearerfunction_id = wef.wearerfunction_id
, wearer we, customer cu, distributionpoint dip, department de, costcenter cc,
  product prd, sizedefinition sz, ragreason, address
, languages
, businessunit bu
where ragi.poolitem = 2
and ragi.weareremployment_link_id =wel.weareremployment_link_id
and wel.wearernumber=we.wearernumber
and wel.distributionpoint_link_id=dip.distributionpoint_id
and nvl(wemp.wearer_id,we.wearer_id) = we.wearer_id    
and nvl(we.customer_id,cu.customer_id) = cu.customer_id
and cu.visitaddress_id = address.address_id
and ragi.distributionpoint_link_id = dip.distributionpoint_id
and dip.department_id = de.department_id
and de.customer_id=cu.customer_id
and dip.costcenter_id = cc.costcenter_id
and cc.customer_id=cu.customer_id
and ragi.product_link_id = prd.product_id
and ragi.sizedefinition_link_id = sz.sizedefinition_id
and ragi.ragreason_id = ragreason.ragreason_id
and bu.businessunit_id = cu.defaultbusinessunit_id;
/