create or replace view v_poolitemsinlaundry as
select ui.uniqueitem_id, bu.code businessunitcode, bu.description businessunitdesc, s.staynr,
       (select sd.description
          from stay_desc sd
         where s.stay_id = sd.stay_id
           and sd.language_id = lan.language_id) staydesc, pr.code productcode,
       (select prd.description
          from product_desc prd
         where prd.product_id = pr.product_id
           and prd.language_id = lan.language_id) productdesc, sz.code sizecode, ui.primaryid, ui.staychangedate,
       getcurrentdate() - getdateonly(ui.staychangedate) days,
       (select szd.description
          from sizedefinition_desc szd
         where szd.sizedefinition_id = sz.sizedefinition_id
           and szd.language_id = lan.language_id) sizedesc, lan.code languagecode, 
       coalesce((select max(1)
                   from uniqueitemloss uil, invoiceheader ih 
                  where uil.uniqueitem_link_id = ui.uniqueitem_id and uil.lossstatus = 1 
                    and uil.lossinvoiceheader_id = ih.invoiceheader_id and ih.invoicenumber <> 0), 0) isInvoiced
  from uniqueitem ui
       join businessunit bu on ui.businessunit_id = bu.businessunit_id
       join product pr on ui.product_id = pr.product_id
       join sizedefinition sz on ui.sizedefinition_id = sz.sizedefinition_id
       join stay s on ui.stay_id = s.stay_id
       join status st on ui.status_id = st.status_id, --  and st.statustype = 'UI'  -- unique item 
       languages lan
 where ui.poolitem = 1 -- pool
   and st.code = 20 -- in circulation
   and s.staytype = 1 
/
