CREATE OR REPLACE VIEW V_WORKORDERISSUES AS
select ih.workorder_id, ih.assigndate, ih.assignyear, ih.assignweek, ih.assignday
, ih.incircdate, ih.incircyear, ih.incircweek, ih.incircday
, ih.amount, ih.quantity
, (select pr.code from product pr where pr.product_id = ih.product_link_id) ProdNr
, (select sd.code from sizedefinition sd where sd.sizedefinition_id = ih.sizedefinition_link_id) SizeCode
, (SELECT pd.description FROM product_desc pd WHERE pd.product_id = ih.product_link_id
          AND pd.language_id = lan.language_id) ProdDescription
, (select qg.code from qualitygrade qg where qg.qualitygrade_id = ih.issuegrade_id) issuegrade
, lan.code languagecode, lan.language_id
from issuehistory ih, languages lan
union all --needed to see also Issued for WO from stock with Origin 8-9 Transferred
select wo.workorder_id, trunc(wost.timestamp) assigndate
, year(trunc(wost.timestamp)) assignyear, cast(getweeknumber(trunc(wost.timestamp)) as numeric) assignweek, EXTRACT(day FROM trunc(wost.timestamp)) assignday
, trunc(wost.timestamp) incircdate, year(trunc(wost.timestamp)) incircyear, cast(getweeknumber(trunc(wost.timestamp)) as numeric) incircweek, EXTRACT(day FROM trunc(wost.timestamp)) incircday
, (-1)*sum(wost.quantity) amount, (-1)*sum(wost.quantity) quantity
, (select pr.code from product pr where pr.product_id = wol.product_link_id) ProdNr
, (select sd.code from sizedefinition sd where sd.sizedefinition_id = wol.sizedefinition_link_id) SizeCode
, (SELECT pd.description FROM product_desc pd WHERE pd.product_id = wol.product_link_id
          AND pd.language_id = lan.language_id) ProdDescription
, (select qg.code from qualitygrade qg where qg.qualitygrade_id = srg.qualitygrade_id) issuegrade
, lan.code languagecode, lan.language_id
from workorder wo, workorderline wol, workorderstocktransaction wost,stocktransaction st, 
stockreservationpergrade srg, languages lan
where wo.workorder_id=wol.workorder_id
      and wol.workorderline_id=wost.workorderline_id
      and wo.origin in (8,9)
      and wost.stocktransaction_id=st.stocktransaction_id
      and st.stockreservationpergrade_id=srg.stockreservationpergrade_id
group by wo.workorder_id, wol.workorderline_id, trunc(wost.timestamp), wol.product_link_id
, wol.sizedefinition_link_id, srg.qualitygrade_id, lan.language_id, lan.code
;
