create or replace view v_lockerlabel as
select lr.locker, lr.customer_id, lr.lockerstatus,lr.bank, wr.wearernumber, wr.fullname, wr.firstname, wr.searchname
, C.Customernumber, C.Name customername
, case
  when (select max(cast(lpad(replace(pcv.version, '.', ''), 3) as numeric)) from product_component_version pcv) >= 112
  then case when (select max(dbs.code) from databasesettings dbs) = 'CIW'
       then (select ListAgg((castnumberasvarchar(wi.changesperweek) || '   ' || --coalesce(pr.legacyproductcode, pr.code) || ' ' ||
                    pr.shortdescription),', ')
               WITHIN GROUP (order by wi.changesperweek--, pr.code
                            , pr.shortdescription)
               from WearerInventory Wi, product pr
               where wi.weareremployment_id = we.weareremployment_id and wi.product_id = pr.product_id and wi.changesperweek <> 0)
       else (select ListAgg((castnumberasvarchar(wi.changesperweek) || '   ' || --pr.code || ' ' ||
                    pr.shortdescription),', ')
               WITHIN GROUP (order by wi.changesperweek--, pr.code
                            , pr.shortdescription)
               from WearerInventory Wi, product pr
               where wi.weareremployment_id = we.weareremployment_id and wi.product_id = pr.product_id and wi.changesperweek <> 0)
       end
else 'Only from Oracle version 11 Release 2' end changesPerWeekProdDesc
, x.mycount
, 0 skippedsections
from Locker LR left outer join WearerEmployment WE
            inner join Wearer WR on wr.wearer_id = we.wearer_id
     on WE.Locker_ID = LR.Locker_ID
     join Customer C on LR.Customer_Id=C.Customer_Id ,
     (select rownum mycount
                  from invoiceheader--all_objects--
                 where rownum <= 100) x
union all
select ' ' locker, 0 customer_id, 0 lockerstatus, ' ' bank, 0 wearernumber, ' ' fullname, ' ' firstname, ' ' searchname
, 0 Customernumber, ' '  customername
, ' ' changesPerWeekProdDesc
, 0 mycount
, x.mycount skippedsections
from (select rownum mycount
                  from invoiceheader--all_objects--
                 where rownum <= 100) x                 
;
/