CREATE OR REPLACE VIEW V_DELIVERYSCAN_ALL AS
SELECT --+index(ds XIF11DELIVERYSCAN) index(ri_link XSPEEDUNIQUEITEM_LINK)
       bu.code BU_CODE, bu.description BU_DESC,
       p.code product_code, p.legacyproductcode,
       (SELECT description FROM PRODUCT_DESC A WHERE p.product_id = A.PRODUCT_ID
       AND A.LANGUAGE_ID = l.language_id) productdescription,
       s.code size_code, s.legacysizecode,
       ds.fromstock, ds.packingnote_id, ds.scantimestamp, ds.chargewashing,
       ri.primaryid, --r.code reason_code,
       (select rd.description from reasoncode_desc rd where rd.reasoncode_id = r.reasoncode_id
        and rd.language_id = l.language_id) reason_code,
       w_link.wearernumber, w_link.wearername, w_link.wearersearchname, w_link.sequencenumber employment_nr,
       prod.typeofmerchandise,
       locker.bank cupboard, locker.locker, locker.lockerstatus,
       locker.remark locker_remark, locker.sortaddress locker_sortaddress
       , ds.weareremployment_link_id, l.code languagecode, w.firstname, w.searchname
  FROM deliveryscan ds
       LEFT OUTER JOIN reasoncode r ON r.reasoncode_id = ds.reasoncode_id
       LEFT OUTER JOIN (weareremployment_link w_link left outer join
                  (weareremployment we left outer join locker on locker.locker_id = we.locker_id)
                  on we.weareremployment_id = w_link.weareremployment_id)
            on w_link.weareremployment_link_id = ds.weareremployment_link_id,
       product_link p LEFT OUTER JOIN product prod ON p.product_id = prod.product_id,
       businessunit bu, sizedefinition_link s,
       uniqueitem_link ri_link, raggeditem ri, languages l, wearer w
 WHERE bu.businessunit_id = ds.businessunit_id and
       p.product_link_id = ds.product_link_id and
       s.sizedefinition_link_id = ds.sizedefinition_link_id and
       ri_link.uniqueitem_link_id = ds.uniqueitem_link_id AND
       ri.raggeditem_id = ri_link.raggeditem_id and
       we.wearer_id = w.wearer_id
UNION ALL
-- UNIQUE ITEMS
SELECT --+index(ds XIF11DELIVERYSCAN)
       bu.code BU_CODE, bu.description BU_DESC,
       p.code product_code, p.legacyproductcode,
       (SELECT description FROM PRODUCT_DESC A WHERE p.product_id = A.PRODUCT_ID
  AND A.LANGUAGE_ID = l.language_id) productdescription,
   s.code size_code, s.legacysizecode,
       ds.fromstock, ds.packingnote_id, ds.scantimestamp, ds.chargewashing,
       ui.primaryid, --r.code reason_code,
       (select rd.description from reasoncode_desc rd where rd.reasoncode_id = r.reasoncode_id
        and rd.language_id = l.language_id) reason_code,
       w_link.wearernumber, w_link.wearername, w_link.wearersearchname, w_link.sequencenumber employment_nr,
       prod.typeofmerchandise,
       locker.bank cupboard, locker.locker, locker.lockerstatus,
       locker.remark locker_remark, locker.sortaddress locker_sortaddress
       , ds.weareremployment_link_id, l.code languagecode, w.firstname, w.searchname
  FROM deliveryscan ds
       LEFT OUTER JOIN reasoncode r ON r.reasoncode_id = ds.reasoncode_id
       LEFT OUTER JOIN (weareremployment_link w_link left outer join
                  (weareremployment we left outer join locker on locker.locker_id = we.locker_id)
                  on we.weareremployment_id = w_link.weareremployment_id)
            on w_link.weareremployment_link_id = ds.weareremployment_link_id,
       product_link p LEFT OUTER JOIN product prod ON p.product_id = prod.product_id,
       businessunit bu, sizedefinition_link s, uniqueitem_link ui_link, uniqueitem ui, languages l, wearer w
 WHERE bu.businessunit_id = ds.businessunit_id and
       p.product_link_id = ds.product_link_id and
       s.sizedefinition_link_id = ds.sizedefinition_link_id and
       ui_link.uniqueitem_link_id = ds.uniqueitem_link_id and
       ui.uniqueitem_id = ui_link.uniqueitem_id and
       we.wearer_id = w.wearer_id
union all -- return to stock from raggeditem
SELECT --+index(o XIE3OUTOFCIRCULATIONHISTORY)  index(ri_link XSPEEDUNIQUEITEM_LINK)  index(p XIF311PRODUCT_LINK)
       distinct bu.code BU_CODE, bu.description BU_DESC,
       p.code product_code, p.legacyproductcode,
       (SELECT description FROM PRODUCT_DESC A WHERE p.product_id = A.PRODUCT_ID
  AND A.LANGUAGE_ID = l.language_id) productdescription,
   s.code size_code, s.legacysizecode,
       'Z' fromstock, o.packingnote_id, o.outofcircdate scantimestamp, null chargewashing,
       ri.primaryid, --r.code reason_code,
      (select rd.description from ragreason_desc rd where rd.ragreason_id = r.ragreason_id
        and rd.language_id = l.language_id) reason_code,
       w_link.wearernumber, w_link.wearername, w_link.wearersearchname, w_link.sequencenumber employment_nr,
       prod.typeofmerchandise,
       locker.bank cupboard, locker.locker, locker.lockerstatus,
       locker.remark locker_remark, locker.sortaddress locker_sortaddress
       , o.weareremployment_link_id, l.code languagecode, w.firstname, w.searchname
  FROM outofcirculationhistory o
       LEFT OUTER JOIN (weareremployment_link w_link left outer join
                  (weareremployment we left outer join locker on locker.locker_id = we.locker_id)
                  on we.weareremployment_id = w_link.weareremployment_id)
            on w_link.weareremployment_link_id = o.weareremployment_link_id,
       product_link p LEFT OUTER JOIN product prod ON p.product_id = prod.product_id,
       businessunit bu, sizedefinition_link s,
       uniqueitem_link ri_link, raggeditem ri, ragreason r, languages l, wearer w
 WHERE bu.businessunit_id = o.processbusinessunit_id and
       p.product_link_id = o.product_link_id and
       s.sizedefinition_link_id = o.sizedefinition_link_id and
       ri_link.uniqueitem_link_id = o.uniqueitem_link_id AND
       ri.raggeditem_id = ri_link.raggeditem_id and
       r.ragreason_id = o.ragreason_id and
       we.wearer_id = w.wearer_id
union all -- return to stock from uniqueitem
SELECT --+index(o XIE3OUTOFCIRCULATIONHISTORY)
       distinct bu.code BU_CODE, bu.description BU_DESC,
       p.code product_code, p.legacyproductcode,
       (SELECT description FROM PRODUCT_DESC A WHERE p.product_id = A.PRODUCT_ID
  AND A.LANGUAGE_ID = l.language_id) productdescription,
   s.code size_code, s.legacysizecode,
       'Z' fromstock, o.packingnote_id, o.outofcircdate scantimestamp, null chargewashing,
       ui.primaryid, --null reason_code,
       (select rd.description from reasoncode_desc rd where rd.reasoncode_id = r.reasoncode_id
         and rd.language_id = l.language_id) reason_code,
       w_link.wearernumber, w_link.wearername, w_link.wearersearchname, w_link.sequencenumber employment_nr,
       prod.typeofmerchandise,
       locker.bank cupboard, locker.locker, locker.lockerstatus,
       locker.remark locker_remark, locker.sortaddress locker_sortaddress
       , o.weareremployment_link_id, l.code languagecode, w.firstname, w.searchname
  FROM outofcirculationhistory o
       LEFT OUTER JOIN reasoncode r ON r.reasoncode_id = o.reasoncode_id
       LEFT OUTER JOIN (weareremployment_link w_link left outer join
                  (weareremployment we left outer join locker on locker.locker_id = we.locker_id)
                  on we.weareremployment_id = w_link.weareremployment_id)
            on w_link.weareremployment_link_id = o.weareremployment_link_id,
       product_link p LEFT OUTER JOIN product prod ON p.product_id = prod.product_id,
       businessunit bu, sizedefinition_link s, uniqueitem_link ui_link, uniqueitem ui, languages l, wearer w
 WHERE bu.businessunit_id = o.processbusinessunit_id and
       p.product_link_id = o.product_link_id and
       s.sizedefinition_link_id = o.sizedefinition_link_id and
       ui_link.uniqueitem_link_id = o.uniqueitem_link_id and
       ui.uniqueitem_id = ui_link.uniqueitem_id and
       we.wearer_id = w.wearer_id and
       o.ragreason_id is null
;
/