create or replace view V_AgeAnalysisPoolItems as
select q.reportparam_id,--q.businessunit_id, q.businessunit_code, q.businessunit_desc, 
       pg.code productgroupcode, 
       (select pgd.description
          from productgroup_desc pgd
         where pgd.productgroup_id = pg.productgroup_id
           and pgd.language_id = languages.language_id) productgroupdesc,
       q.product_id,
       p.code productcode,
       (select product_desc.description
          from product_desc
         where product_desc.product_id = q.product_id
           and product_desc.language_id = languages.language_id) productdesc,
       s.code sizecode,
       (select size_desc.description
          from sizedefinition_desc size_desc
         where size_desc.sizedefinition_id = q.sizedefinition_id
           and size_desc.language_id = languages.language_id) sizedesc,
       q.column1,
       q.column2,
       q.column3,
       q.column4,
       q.column5,
       q.column6,
       q.column7,
       q.column8,
       q.column9,
       q.total_weeks_in_circ,
       q.total_washes,
       q.total_stock,
       languages.code languagecode, q.customernumber, q.customername
          from (select x.reportparam_id,--x.businessunit_id, x.businessunit_code, x.businessunit_desc, 
                       x.product_id,
                       x.sizedefinition_id,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 1 then 1 else 0 end
                                else case when x.washescolumnnr =1 then 1 else 0 end end) column1,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 2 then 1 else 0 end
                                else case when x.washescolumnnr =2 then 1 else 0 end end) column2,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 3 then 1 else 0 end
                                else case when x.washescolumnnr =3 then 1 else 0 end end) column3,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 4 then 1 else 0 end
                                else case when x.washescolumnnr =4 then 1 else 0 end end) column4,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 5 then 1 else 0 end
                                else case when x.washescolumnnr =5 then 1 else 0 end end) column5,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 6 then 1 else 0 end
                                else case when x.washescolumnnr =6 then 1 else 0 end end) column6,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 7 then 1 else 0 end
                                else case when x.washescolumnnr =7 then 1 else 0 end end) column7,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 8 then 1 else 0 end
                                else case when x.washescolumnnr =8 then 1 else 0 end end) column8,
                       sum(case when x.ShowAgeByWeeks = 'Y' 
                                then case when x.weekscolumnnr = 9 then 1 else 0 end
                                else case when x.washescolumnnr =9 then 1 else 0 end end) column9,
                       count(*) total_stock,
                       sum(x.weeks_in_circ) total_weeks_in_circ,
                       sum(x.washestotal) total_washes, x.customernumber, x.customername
                  from (select rp.reportparam_id, rp.string_value8 ShowAgeByWeeks, --uq.businessunit_id, bu.code businessunit_code, bu.description businessunit_desc, 
                               uq.product_id, uq.sizedefinition_id, uq.uniqueitem_id,
                               ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) weeks_in_circ,
                               case
                                 when floor(ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) / rp.number_value3) + 1 > 9
                                   then 9
                                   else floor(ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) / rp.number_value3) + 1
                               end weekscolumnnr,
                               uq.washestotal,
                               case
                                 when floor(uq.washestotal / rp.number_value3) + 1 > 9
                                   then 9
                                   else floor(uq.washestotal / rp.number_value3) + 1
                               end washescolumnnr, -1 customernumber, '' customername
                          from uniqueitem        uq,
                               uniqueitemfixedinfo uf,
                               businessunit      bu,
                               product           prd,
                               productgroup      pg,
                               systemuser        su,
                               reportparam       rp
                         where rp.number_value5 = -1 and uq.customerowned = 'N'
                           and uq.poolitem = 1
                           and uf.uniqueitem_id = uq.uniqueitem_id
                           and prd.product_id = uq.product_id
                           and prd.uniquepoolitem = 'Y'
                           and pg.productgroup_id = prd.productgroup_id
                           and uq.status_id in
                               (select s1.status_id
                                  from status s1
                                 where s1.statustype = 'UI'
                                   and s1.code in ('20', '30'))
                           and bu.businessunit_id = uq.businessunit_id
                           and bu.code >= rp.string_value1
                           and bu.code <= rp.string_value2
                           and pg.code >= rp.string_value3
                           and pg.code <= rp.string_value4
                           and prd.code >= rp.string_value5
                           and prd.code <= rp.string_value6
                           and (trunc(sysdate) - trunc(uq.lastscandate)) <= rp.number_value4
                           and su.systemuser_id = rp.number_value
                           and bu.businessunit_id in (select bug.businessunit_id from businessunitsperusergroup bug where bug.usergroup_id = su.usergroup_id)
                           union all --Related To Customer
                           select rp.reportparam_id, rp.string_value8 ShowAgeByWeeks, --uq.businessunit_id, bu.code businessunit_code, bu.description businessunit_desc, 
                               uq.product_id, uq.sizedefinition_id, uq.uniqueitem_id,
                               ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) weeks_in_circ,
                               case
                                 when floor(ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) / rp.number_value3) + 1 > 9
                                   then 9
                                   else floor(ceil((uf.daysincircprevissue + (trunc(sysdate) - uf.lastissuedate))/7) / rp.number_value3) + 1
                               end weekscolumnnr,
                               uq.washestotal,
                               case
                                 when floor(uq.washestotal / rp.number_value3) + 1 > 9
                                   then 9
                                   else floor(uq.washestotal / rp.number_value3) + 1
                               end washescolumnnr, cu.customernumber, cu.name customername
                          from uniqueitem        uq,
                               uniqueitemfixedinfo uf,
                               businessunit      bu,
                               product           prd,
                               productgroup      pg,
                               systemuser        su,
                               reportparam       rp, 
                               distributionpoint dp, department de, customer cu                               
                         where rp.number_value5 = cu.customernumber 
                           and uq.relatedcustomer_id is not null
                           and uq.distributionpoint_id = dp.distributionpoint_id 
                           and dp.department_id = de.department_id 
                           and de.customer_id = cu.customer_id
                           and uq.poolitem = 1
                           and uf.uniqueitem_id = uq.uniqueitem_id
                           and prd.product_id = uq.product_id
                           and prd.uniquepoolitem = 'Y'
                           and pg.productgroup_id = prd.productgroup_id
                           and uq.status_id in
                               (select s1.status_id
                                  from status s1
                                 where s1.statustype = 'UI'
                                   and s1.code in ('20', '30'))
                           and bu.businessunit_id = uq.businessunit_id
                           and bu.code >= rp.string_value1
                           and bu.code <= rp.string_value2
                           and pg.code >= rp.string_value3
                           and pg.code <= rp.string_value4
                           and prd.code >= rp.string_value5
                           and prd.code <= rp.string_value6
                           and (trunc(sysdate) - trunc(uq.lastscandate)) <= rp.number_value4
                           and su.systemuser_id = rp.number_value
                           and bu.businessunit_id in (select bug.businessunit_id from businessunitsperusergroup bug where bug.usergroup_id = su.usergroup_id)
                           ) x
                 group by x.reportparam_id, x.ShowAgeByWeeks, --x.businessunit_id, x.businessunit_code, x.businessunit_desc, 
                          x.product_id, x.sizedefinition_id, x.customernumber, x.customername) q,
       sizedefinition s,
       product p, productgroup pg,
       languages
 where p.product_id = q.product_id
   and pg.productgroup_id = p.productgroup_id
   and s.sizedefinition_id = q.sizedefinition_id
/
