CREATE OR REPLACE VIEW V_ISSUEDBARCODES AS
select  -1 year, -1 week,
 subsel.departmentnumber, subsel.departmentdesc,
 subsel.wearernumber, subsel.wearername,
 subsel.productcode,
 (select z.description from product_link_desc z where z.product_link_id = subsel.product_link_id
  and z.language_id = subsel.language_id) productdesc, subsel.sizedefinitionCode,
 (select k.description from sizedefinition_link_desc k where k.sizedefinition_link_id = subsel.sizedefinition_link_id
 and k.language_id = subsel.language_id ) sizedefinitionDesc,
 subsel.quantity, subsel.prepcharge,subsel.invoiceheader_id,
 subsel.incircdate,
 subsel.bu_code, subsel.bu_desc, subsel.customernumber, subsel.customername,
 subsel.primaryid,
 coalesce((select rc.code from workorder wo, reasoncode rc where subsel.issuedbyworkorder_id=wo.workorder_id and wo.reasoncode_id = rc.reasoncode_id),
         (select rc.code from reasoncode rc where subsel.reasoncode_id = rc.reasoncode_id)) reasoncode,
 coalesce((select rd.description from workorder wo, reasoncode_desc rd where subsel.issuedbyworkorder_id=wo.workorder_id and wo.reasoncode_id = rd.reasoncode_id and rd.language_id = subsel.language_id),
         (select rd.description from reasoncode_desc rd where subsel.reasoncode_id = rd.reasoncode_id and rd.language_id = subsel.language_id)) reasondesc,
 (select max(ttd.description) from transactiontype_desc ttd, scan sc where sc.transactiontype_id = ttd.transactiontype_id and ttd.language_id = subsel.language_id and subsel.uniqueitem_id = sc.uniqueitem_link_id
 and sc.scantimestamp = MaxScanTime.scantimestamp) transactiontypedesc,
 MaxScanTime.scantimestamp,
(SELECT ch.code FROM chain ch, customer c WHERE c.customer_id = subsel.customer_id AND ch.chain_id = c.chain_id) chain_code,
 subsel.languagecode
FROM
(
select e.departmentnumber, e.description departmentdesc,
 w.wearernumber, w.wearername,
 p.code productcode, a.product_link_id, languages.language_id, s.code sizedefinitionCode,
 s.sizedefinition_link_id,
 a.quantity, --coalesce(a.prepcharge, 0) prepcharge, a.invoiceheader_id,
 0 prepcharge, -1 invoiceheader_id, 
 coalesce((select uif.lastissuedate from uniqueitemfixedinfo uif where uif.uniqueitem_id = ui.uniqueitem_id), a.incircdate) incircdate,
 bu.code bu_code, bu.description bu_desc, f.customernumber, f.name customername,
 ui.primaryid, coalesce(a.workorder_id,-1) issuedbyworkorder_id, a.reasoncode_id,
 ui.uniqueitem_id,
 f.customer_id,
 languages.code languagecode
from issuehistory a
 left outer join weareremployment_link w
 on a.weareremployment_link_id = w.weareremployment_link_id,
 distributionpoint_link d, department_link e, customer_link f, product_link p,
 languages, sizedefinition_link s, businessunit bu, uniqueitem ui
where a.distributionpoint_link_id = d.distributionpoint_link_id
 and d.department_link_id = e.department_link_id
 and a.product_link_id = p.product_link_id
 and a.sizedefinition_link_id = s.sizedefinition_link_id-- and a.issuestatus = 0 -- todo 3876
 and a.processbusinessunit_id = bu.businessunit_id
 and a.customer_link_id = f.customer_link_id
 and ui.uniqueitem_id = a.uniqueitem_link_id
union
select e.departmentnumber, e.description departmentdesc,
 w.wearernumber, w.wearername,
 p.code productcode, p.product_link_id, languages.language_id, s.code sizedefinitionCode,
 s.sizedefinition_link_id,
 1 quantity, 0 prepcharge, -1 invoiceheader_id,
 uif.lastissuedate incircdate,
 bu.code bu_code, bu.description bu_desc, f.customernumber, f.name customername,
 ui.primaryid, coalesce(uif.issuedbyworkorder_id,-1) issuedbyworkorder_id, uif.reasoncode_id,
 ui.uniqueitem_id,
 f.customer_id,
 languages.code languagecode
from weareremployment_link w
     inner join uniqueitemnonpool unp
     on unp.weareremployment_id = w.weareremployment_link_id,
 distributionpoint_link d, department_link e, customer_link f, product_link p,
 languages, sizedefinition_link s, businessunit bu, uniqueitem ui, uniqueitemfixedinfo uif
where w.distributionpoint_link_id = d.distributionpoint_link_id
 and d.department_link_id = e.department_link_id
 and ui.product_id = p.product_link_id
 and ui.sizedefinition_id = s.sizedefinition_link_id
 and ui.businessunit_id = bu.businessunit_id
 and e.customer_link_id = f.customer_link_id
 and ui.uniqueitem_id = unp.uniqueitem_id
 and uif.uniqueitem_id = ui.uniqueitem_id
 ) subsel,
 (select sc2.uniqueitem_link_id, max(sc2.scantimestamp) scantimestamp
  from scan sc2 group by sc2.uniqueitem_link_id) MaxScanTime
where subsel.uniqueitem_id = MaxScanTime.uniqueitem_link_id
;
/