create or replace view v_stockroomtransactions as          
select   cu.customernumber, cu.name customername, deb.customernumber debtornumber, deb.name debname, 
         invad.street, invad.zipcode, invad.city, invad.state, cu.siccode, 
         trunc(scan.scantimestamp) scandate, prd.code productcode, rc.code reasoncode,
         (select prde.description from product_desc prde
           where prde.product_id = prd.product_id and prde.language_id = lan.language_id) productdesc,
         (select rcd.description from reasoncode_desc rcd
           where rcd.reasoncode_id = rc.reasoncode_id and rcd.language_id = lan.language_id) reasondesc,
         wl.wearernumber wearernumber, wl.wearername wearername,
         d.departmentnumber departmentnumber, d.description departmentdesc, pg.code productgroup,
         (select ms.code from marketsegment ms
           where ms.marketsegment_id = cu.marketsegment_id) marketsegment,
         scan.transactiontype_id transactiontype, lan.code languagecode, lan.language_id,
         cu.language_id customerlanguage_id,
         nvl(scan.primaryid,
             (select ri.primaryid from raggeditem ri where ri.raggeditem_id = scan.uniqueitem_link_id)) primaryid,
         bu.code bu_code, p.code plant_code, p.name plant_name, 1 numberofscans, sd.code sizecode,
         (select prsz.sequencenumber from productsize prsz where prsz.product_id = prd.product_id
                                          and prsz.sizedefinition_id = sd.sizedefinition_id) sizeseq
from scan
     left outer join reasoncode rc on scan.reasoncode_id = rc.reasoncode_id
     left outer join weareremployment_link wl 
          inner join weareremployment w
                inner join wearer we on w.wearer_id = we.wearer_id
          on wl.weareremployment_id = w.weareremployment_id
     on scan.weareremployment_link_id = wl.weareremployment_link_id
     left outer join distributionpoint_link l 
          inner join department d on l.department_link_id = d.department_id
     on scan.distributionpoint_link_id = l.distributionpoint_link_id
     inner join product prd
           inner join productgroup pg on prd.productgroup_id = pg.productgroup_id
     on scan.product_link_id = prd.product_id
     inner join sizedefinition sd on scan.sizedefinition_link_id = sd.sizedefinition_id
     inner join businessunit bu 
           inner join plant p on bu.plant_id = p.plant_id
     on scan.businessunit_id = bu.businessunit_id
     inner join customer cu 
           inner join customer deb
                 inner join address invad on deb.invoiceaddress_id = invad.address_id
           on cu.debtor_id = deb.customer_id
     on scan.customer_link_id = cu.customer_id, 
     languages lan
where nvl(d.customer_id, cu.customer_id) = cu.customer_id
      and nvl(we.customer_id, cu.customer_id) = cu.customer_id
/
