create or replace view v_scanningspercustomer as
select cu.customer_id, cu.customernumber,
       deb.customernumber debtornumber,
       cu.masteraccount,
       macc.customernumber masteraccountnumber,
       cu.name customername,
       coalesce((select de.departmentnumber
                 from   department de, distributionpoint di
                 where  de.department_id = di.department_id
                 and    di.distributionpoint_id = scan.distributionpoint_link_id), -1) departmentnumber,
       (select de.description
         from   department de, distributionpoint di
         where  de.department_id = di.department_id
         and    di.distributionpoint_id = scan.distributionpoint_link_id) departmentdesc,
       address.city, cu.phoneno, cu.faxno, 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) productdescription,
 transactiontype.transactiontype_id transactiontype,  scan.scantimestamp scandate, TRUNC(scan.scantimestamp) scandatetrunc,
       languages.code languagecode, scan.businessunit_id, cu.defaultbusinessunit_id,
 (select bus.code from businessunit bus where bus.businessunit_id=scan.businessunit_id) bu_code
       , case when length(scan.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(scan.primaryid)
              else scan.primaryid
         end primaryid
       , we.wearernumber, we.wearername,cast( (select ss.value from systemsetting ss, businessunit bu where cu.defaultbusinessunit_id=bu.businessunit_id and ss.plant_id=bu.plant_id and  ss.name='PHONEFAXLENGTH' ) as varchar2(16))phonelen
from customer cu, customer deb, customer macc, address, product prd, stay, transactiontype, scan
       left outer join weareremployment_link we on scan.weareremployment_link_id = we.weareremployment_link_id
       , languages, businessunit bu
where cu.mailaddress_id = address.address_id
and cu.customer_id = scan.customer_link_id
and prd.product_id = scan.product_link_id
and stay.stay_id = scan.stay_id
and (stay.staynr <> 21 and stay.staynr <> 22 and stay.staynr <> 23)
and transactiontype.transactiontype_id = scan.transactiontype_id
and (transactiontype.transactiontype_id <> 10 and transactiontype.transactiontype_id <> 11 and
    transactiontype.transactiontype_id <> 30 and transactiontype.transactiontype_id <> 31 and
    transactiontype.transactiontype_id <> 32 and transactiontype.transactiontype_id <> 33 and
    transactiontype.transactiontype_id <> 40 and transactiontype.transactiontype_id <> 41 and
    transactiontype.transactiontype_id <> 50 and transactiontype.transactiontype_id <> 51 and
    transactiontype.transactiontype_id <> 52 and transactiontype.transactiontype_id <> 53 and
    transactiontype.transactiontype_id <> 60 and transactiontype.transactiontype_id <> 80 and
    transactiontype.transactiontype_id <> 81 and transactiontype.transactiontype_id <> 0)
and deb.customer_id = cu.debtor_id
and macc.customer_id = cu.masteraccount_id
and bu.businessunit_id = cu.defaultbusinessunit_id
/
