create or replace view v_outstandingworkorders as
select 1 linetype,
wo.businessunit_id, wo.deliverfrom, wo.weareremployment_link_id, wo.origin,
cu.customernumber, cu.name customername, de.departmentnumber, de.description Dep_desc,
dp.distributionpointnumber, dp.description DistribPoint_desc,---1 distributionpointnumber, '' DistribPoint_desc,
w.wearernumber, w.fullname, w.firstname, w.searchname, wo.workordernumber, wo.requestdate, wo.neededdate,
wol.quantityordered, wol.quantitycancelled, wol.quantityfilled,
case when (wol.quantityordered < wol.quantitycancelled + wol.quantityfilled)
then 0
else (wol.quantityordered - wol.quantitycancelled - wol.quantityfilled)
end Outstanding,
(select std.description from status_desc std where st.status_id = std.status_id and std.language_id = lan.language_id) Status_desc,
st.code Status_Code, pr.code Prod_code, sd.code Size_code,
(select prd.description from product_desc prd where pr.product_id = prd.product_id and prd.language_id = lan.language_id) Prod_desc,
(select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sd.sizedefinition_id and sdd.language_id = lan.language_id) Size_desc,
rc.code Reason_code,wo.referencenumber referencenumber,
(select rcd.description from reasoncode_desc rcd where rc.reasoncode_id = rcd.reasoncode_id and rcd.language_id = lan.language_id) Reason_desc,
lan.code Lan_code, wo.remark,
worefnr.referencenumber woreferencenr, worefnr.description worefnr_desc,
coalesce((select st.managedbybu_id from  stockroom st where wo.processingstockroom_id=st.stockroom_id),wo.businessunit_id) procbuid,
f_getprocbuid(wo.workorder_id)needforbuid
,(select b.code from businessunit  b where b.businessunit_id=f_getprocbuid(wo.workorder_id)) needforbucode
,coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)) intprocessbuid
,(select b.code from businessunit  b where b.businessunit_id=coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)))intprocessbucode
from workorder wo
     left outer join woreferencenumber worefnr on wo.woreferencenumber_id = worefnr.woreferencenumber_id,
weareremployment we, wearer w, customer cu, department de, distributionpoint dp,
workorderline wol
left outer join product pr on wol.product_link_id = pr.product_id
left outer join sizedefinition sd on wol.sizedefinition_link_id = sd.sizedefinition_id,
status st, reasoncode rc, languages lan
where wol.workorder_id = wo.workorder_id
and wo.status_id = st.status_id and st.statustype = 'WO'
and wo.weareremployment_link_id = we.weareremployment_id --is an union instead of left join because of 2 ways to determine customer
and we.wearer_id = w.wearer_id and w.customer_id = cu.customer_id
and cu.customer_id = de.customer_id and dp.department_id = de.department_id and dp.distributionpoint_id = we.distributionpoint_id
and rc.reasoncode_id = wo.reasoncode_id
union all
select 2 linetype,
wo.businessunit_id, wo.deliverfrom, wo.weareremployment_link_id, wo.origin,
cu.customernumber, cu.name customername, de.departmentnumber, de.description Dep_desc, dp.distributionpointnumber, dp.description DistribPoint_desc,
-1 wearernumber, '' fullname, '' firstname, '' searchname, wo.workordernumber, wo.requestdate, wo.neededdate,
wol.quantityordered, wol.quantitycancelled, wol.quantityfilled,
case when (wol.quantityordered < wol.quantitycancelled + wol.quantityfilled)
then 0
else (wol.quantityordered - wol.quantitycancelled - wol.quantityfilled)
end Outstanding,
(select std.description from status_desc std where st.status_id = std.status_id and std.language_id = lan.language_id) Status_desc,
st.code Status_Code, pr.code Prod_code, sd.code Size_code,
(select prd.description from product_desc prd where pr.product_id = prd.product_id and prd.language_id = lan.language_id) Prod_desc,
(select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sd.sizedefinition_id and sdd.language_id = lan.language_id) Size_desc,
rc.code Reason_code,wo.referencenumber referencenumber,
(select rcd.description from reasoncode_desc rcd where rc.reasoncode_id = rcd.reasoncode_id and rcd.language_id = lan.language_id) Reason_desc,
lan.code Lan_code, wo.remark,
worefnr.referencenumber woreferencenr, worefnr.description worefnr_desc,
coalesce((select st.managedbybu_id from  stockroom st where wo.processingstockroom_id=st.stockroom_id),wo.businessunit_id) procbuid,
f_getprocbuid(wo.workorder_id)needforbuid
,(select b.code from businessunit  b where b.businessunit_id=f_getprocbuid(wo.workorder_id)) needforbucode
,coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)) intprocessbuid
,(select b.code from businessunit  b where b.businessunit_id=coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)))intprocessbucode
from workorder wo
left outer join distributionpoint dp
     join department de
          join customer cu on de.customer_id = cu.customer_id
     on dp.department_id = de.department_id
on wo.distributionpoint_link_id = dp.distributionpoint_id
left outer join woreferencenumber worefnr on wo.woreferencenumber_id = worefnr.woreferencenumber_id
, workorderline wol
left outer join product pr on wol.product_link_id = pr.product_id
left outer join sizedefinition sd on wol.sizedefinition_link_id = sd.sizedefinition_id,
status st, reasoncode rc, languages lan
where wol.workorder_id = wo.workorder_id
and wo.status_id = st.status_id and st.statustype = 'WO'
and rc.reasoncode_id = wo.reasoncode_id
and wo.weareremployment_link_id is null
and wo.distributionpoint_link_id is not null
union
select 3 linetype,
wo.businessunit_id, wo.deliverfrom, wo.weareremployment_link_id, wo.origin,
cu.customernumber, cu.name customername,  -1 departmentnumber, '' Dep_desc, -1 distributionpointnumber, '' DistribPoint_desc,
-1 wearernumber, '' fullname, '' firstname, '' searchname, wo.workordernumber, wo.requestdate, wo.neededdate,
wol.quantityordered, wol.quantitycancelled, wol.quantityfilled,
case when (wol.quantityordered < wol.quantitycancelled + wol.quantityfilled)
then 0
else (wol.quantityordered - wol.quantitycancelled - wol.quantityfilled)
end Outstanding,
(select std.description from status_desc std where st.status_id = std.status_id and std.language_id = lan.language_id) Status_desc,
st.code Status_Code, pr.code Prod_code, sd.code Size_code,
(select prd.description from product_desc prd where pr.product_id = prd.product_id and prd.language_id = lan.language_id) Prod_desc,
(select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sd.sizedefinition_id and sdd.language_id = lan.language_id) Size_desc,
rc.code Reason_code,wo.referencenumber referencenumber,
(select rcd.description from reasoncode_desc rcd where rc.reasoncode_id = rcd.reasoncode_id and rcd.language_id = lan.language_id) Reason_desc,
lan.code Lan_code, wo.remark,
worefnr.referencenumber woreferencenr, worefnr.description worefnr_desc,
f_getprocbuid(wo.workorder_id) procbuid,
f_getprocbuid(wo.workorder_id)needforbuid
,(select b.code from businessunit  b where b.businessunit_id=f_getprocbuid(wo.workorder_id)) needforbucode
,coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)) intprocessbuid
,(select b.code from businessunit  b where b.businessunit_id=coalesce(wo.internalprocessingbu_id,f_getintprocbuid(wo.workorder_id)))intprocessbucode
from workorder wo
          join customer cu on wo.customer_link_id = cu.customer_id
left outer join woreferencenumber worefnr on wo.woreferencenumber_id = worefnr.woreferencenumber_id
, workorderline wol
left outer join product pr on wol.product_link_id = pr.product_id
left outer join sizedefinition sd on wol.sizedefinition_link_id = sd.sizedefinition_id,
status st, reasoncode rc, languages lan
where wol.workorder_id = wo.workorder_id
and wo.status_id = st.status_id and st.statustype = 'WO'
and rc.reasoncode_id = wo.reasoncode_id
and wo.weareremployment_link_id is null
and wo.distributionpoint_link_id is null
;
/
