create or replace view V_SCANSPERWEARER AS 
select--+ INDEX(scan XSPEEDSCAN) INDEX(dilk XPKDISTRIBUTIONPOINT_LINK) INDEX(prdlk XPKPRODUCT_LINK)
  cu.customernumber,  cu.name customerName, cu.customer_id, delk.departmentnumber, delk.description departmentDesc
, w.wearernumber, w.fullname wearername, w.firstname wearerfirstname, w.searchname wearerlastname, scan.primaryid
, prdlk.code ProductCode, szlk.code SizeCode
, (select product_desc.description from product_desc where product_desc.product_id = prdlk.product_id
  and product_desc.language_id = languages.language_id ) ProductDesc
, (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = szlk.sizedefinition_link_id
  and sdd.language_id = languages.language_id ) SizeDesc
, scan.year, scan.week, scan.dayno
, (select day_desc.code from day_desc where day_desc.day_id = scan.dayno
  and day_desc.language_id = languages.language_id) dayCode--, ((scan.year*100)+scan.week) YEARWEEK
, to_char(scan.scantimestamp, 'HH24.MI.SS') scantimestamp--, TO_TIMESTAMP(to_char(scan.scantimestamp, 'DD-MON-YY HH.MI.SS AM')) scantimestamp
, trunc(scan.scantimestamp) ScanDate, transactiontype.transactiontype_id
, (select transactiontype_desc.description from transactiontype_desc where transactiontype_desc.transactiontype_id = transactiontype.transactiontype_id
  and transactiontype_desc.language_id = languages.language_id ) transactiontypeDesc
, transactiontype.factorydescription, languages.code languageCode
, rc.code reason_code
, case when transactiontype.transactiontype_id = 2 then
       coalesce((select pn.notenumber from deliveryscan ds, packingnote pn, uniqueitem ui
         where ds.uniqueitem_link_id = scan.uniqueitem_link_id
               and ds.uniqueitem_link_id = ui.uniqueitem_id
               and ds.scantimestamp = ui.lastoutscandate
               and pn.packingnote_id = ds.packingnote_id), -1)
  else null
  end pnnotenumber
, rp.reportparam_id
, scan.businessunit_id, rp.number_value9,rp.number_value
from
  scan left outer join reasoncode rc on scan.reasoncode_id = rc.reasoncode_id, customer cu, distributionpoint_link dilk, department_link delk, weareremployment we join wearer w on w.wearer_id=we.wearer_id
, product_link prdlk, sizedefinition_link szlk, transactiontype
, languages, reportparam rp
WHERE scan.customer_link_id = cu.customer_id and
    scan.weareremployment_link_id = we.weareremployment_id
         and we.distributionpoint_id = dilk.distributionpoint_link_id
             and dilk.department_link_id = delk.department_link_id
                 and delk.customer_link_id = cu.customer_id
and scan.product_link_id = prdlk.product_link_id
and scan.sizedefinition_link_id = szlk.sizedefinition_link_id
and scan.transactiontype_id = transactiontype.transactiontype_id
   and (transactiontype.transactiontype_id <> 0
         and transactiontype.transactiontype_id <> 10
         and transactiontype.transactiontype_id <> 11
         and transactiontype.transactiontype_id <> 40
         and transactiontype.transactiontype_id <> 41
         and transactiontype.transactiontype_id <> 50
         and transactiontype.transactiontype_id <> 53
         and transactiontype.transactiontype_id <> 60
         and transactiontype.transactiontype_id <> 70
         and transactiontype.transactiontype_id <> 71
         and transactiontype.transactiontype_id <> 72
         and transactiontype.transactiontype_id <> 73
         and transactiontype.transactiontype_id <> 99)
and cu.customernumber >= rp.number_value1 and cu.customernumber <= rp.number_value2
and delk.departmentnumber >= rp.number_value3 and delk.departmentnumber <= rp.number_value4
and w.wearernumber >= rp.number_value5 and w.wearernumber <= rp.number_value6
and transactiontype.transactiontype_id >= rp.number_value7 and transactiontype.transactiontype_id <= rp.number_value8
and languages.code = rp.string_value
and prdlk.code >= rp.string_value1 and prdlk.code <= rp.string_value2
and ((rc.code >= rp.string_value3 and rc.code <= rp.string_value4) or rc.code is null)
and trunc(scan.scantimestamp) >= rp.date_value1 and trunc(scan.scantimestamp) <= rp.date_value2
and decode(rp.number_value,0 ,rp.number_value9,scan.businessunit_id)=scan.businessunit_id
and (cu.defaultbusinessunit_id = rp.number_value9 or
     exists (select 1 from customeractivities ca where ca.customer_id = cu.customer_id and (ca.processbusinessunit_id = rp.number_value9 or
     ca.internalprocessingbu_id = rp.number_value9 ))
    )

