create or replace view v_poolitemsatcustomer as
select 0 grp, 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, ui.lastscandate,
       getcurrentdate() - getdateonly(ui.staychangedate) days,
       (select sdd.description
          from sizedefinition_desc sdd
         where sdd.sizedefinition_id = sz.sizedefinition_id
           and sdd.language_id = lan.language_id) sizedesc,
       cs.customernumber, cs.name customername, ad.city, dp.departmentnumber,
       dp.description departmentdesc, di.distributionpointnumber,
       di.description distributionpointdesc, 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 
       join distributionpoint di on ui.distributionpoint_id = di.distributionpoint_id
       join department dp on di.department_id = dp.department_id
       join customer cs on dp.customer_id = cs.customer_id
       join address ad on cs.visitaddress_id = ad.address_id, 
       languages lan
 where ui.poolitem = 1 -- pool
   and s.staytype = 1
   and st.code = 20 -- in circulation
   and s.staynr >= 20
   and s.staynr < 30 
/
