create or replace view V_GMDGarmentCollectionList as
select 1 linetype, --POOL
       dl.deliveryload_id, dl.code deliveryload_code, dl.deliverydate, 
       pld.Code PlantDep_code, pld.Description PlantDep_desc, 
       cu.CustomerNumber, cu.Name CustomerName, w.wearernumber, w.fullname WearerName,
       dp.Code deliverypoint_code, dp.Description deliverypoint_desc, 
       pr.code Product_Code, ps.sequencenumber, sd.code Size_Code, null staytype, null staynr, 
       (select prd.description from product_desc prd where prd.product_id = pr.product_id and prd.language_id = lan.language_id) Product_Desc, 
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sd.sizedefinition_id and sdd.language_id = lan.language_id) Size_Desc, 
       null Stay_Desc, 
       DLd.Todeliverquantity, DLd.PersonalizedAvailableQty, null primaryid, 
       lan.code language_code, lan.language_id
from DeliveryLoad DL
     inner join DeliveryLoadDetail DLD 
           inner join WearerInventory WI 
                 inner join WearerEmployment WE 
                       inner join Wearer w on we.wearer_id = w.wearer_id
                 on wi.weareremployment_id = we.weareremployment_id
                 inner join product pr on wi.product_id = pr.product_id
                 inner join productsize ps on wi.product_id = ps.product_id and wi.sizedefinition_id = ps.sizedefinition_id
                 inner join sizedefinition sd on wi.sizedefinition_id = sd.sizedefinition_id
           on dld.wearerinventory_id = wi.wearerinventory_id
     on dl.deliveryload_id = dld.deliveryload_id
     inner join deliverypoint dp 
           inner join customer cu on dp.customer_id = cu.customer_id
     on dl.deliverypoint_id = dp.deliverypoint_id
     left outer join plantdepartment pld on dl.plantdepartment_id = pld.plantdepartment_id
, languages lan     
where DLD.TODELIVERQUANTITY > DLD.PersonalizedAvailableQty     
union all  --Personalized Garments
select 2 linetype, dl.deliveryload_id, dl.code deliveryload_code, dl.deliverydate, 
       pld.Code PlantDep_code, pld.Description PlantDep_desc, 
       cu.CustomerNumber, cu.Name CustomerName, w.wearernumber, w.fullname WearerName,
       dp.Code deliverypoint_code, dp.Description deliverypoint_desc, 
       pr.code Product_Code, ps.sequencenumber, sd.code Size_Code, st.staytype, st.staynr, 
       (select prd.description from product_desc prd where prd.product_id = pr.product_id and prd.language_id = lan.language_id) Product_Desc, 
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = sd.sizedefinition_id and sdd.language_id = lan.language_id) Size_Desc, 
       (select std.description from Stay_Desc std where std.stay_id = st.stay_id and std.language_id = lan.language_id) Stay_Desc, 
       DLd.Todeliverquantity, DLd.PersonalizedAvailableQty, ui.primaryid, 
       lan.code language_code, lan.language_id
from DeliveryLoad DL
     inner join DeliveryLoadDetail DLD 
           inner join WearerInventory WI 
                 inner join WearerEmployment WE 
                       inner join Wearer w on we.wearer_id = w.wearer_id
                 on wi.weareremployment_id = we.weareremployment_id
                 inner join product pr on wi.product_id = pr.product_id
                 inner join productsize ps on wi.product_id = ps.product_id and wi.sizedefinition_id = ps.sizedefinition_id
                 inner join sizedefinition sd on wi.sizedefinition_id = sd.sizedefinition_id
                 inner join UniqueItemNonPool UINP 
                      inner join UniqueItem UI 
                           inner join stay st on ui.stay_id = st.stay_id
                      on UINP.uniqueitem_id = ui.uniqueitem_id
                 on WI.WEARERINVENTORY_ID = UINP.Wearerinventory_Id
           on dld.wearerinventory_id = wi.wearerinventory_id
     on dl.deliveryload_id = dld.deliveryload_id
     inner join deliverypoint dp 
           inner join customer cu on dp.customer_id = cu.customer_id
     on dl.deliverypoint_id = dp.deliverypoint_id
     left outer join plantdepartment pld on dl.plantdepartment_id = pld.plantdepartment_id
, languages lan     
where DLD.TODELIVERQUANTITY > DLD.PersonalizedAvailableQty     
      and st.staynr in (10, 31, 32, 36, 40)--Stockroom, Rewash, Repair, Late, OnShelf
/
