create or replace view v_circulationspeedgarment as
select /*+ index(sqh XIE1STATUSQUOHEADER) index(sqi XIF1SQITEMINCIRCULATION)*/
       rp2.reportparam_id,
       f_amountdelivered(sqi.customer_link_id, sqi.product_link_id, sqi.sizedefinition_link_id, sqi.finishingmethod_id, rp2.reportparam_id) amountdelivered,
       (coalesce(sum(sqi.totalcircinv) * 100, 0) +
        coalesce(count(distinct sqi.yearweek), 0)) totalcircinv,
       c2.customernumber, c2.name customername,
       (select ad2.city
         from   address ad2, customer c2
         where  sqi.customer_link_id = c2.customer_id and
                c2.visitaddress_id = ad2.address_id) city,
       (select pr2.code from product pr2 where sqi.product_link_id = pr2.product_id) productcode,
       (select prd.description
         from   product_desc prd
         where  sqi.product_link_id = prd.product_id and
                languages.language_id = prd.language_id) productdesc,
       (select sz2.code
         from   sizedefinition sz2
         where  sqi.sizedefinition_link_id = sz2.sizedefinition_id) sizecode,
       (select f2.code
         from   finishingmethod f2
         where  sqi.finishingmethod_id = f2.finishingmethod_id) finishingmethodcode,
       languages.code languagecode
from   sqitemincirculation sqi, statusquoheader sqh, customer c2, reportparam rp2, languages
where  sqi.statusquoheader_id = sqh.statusquoheader_id and sqi.totalcircinv > 0 and
       sqi.customer_link_id = c2.customer_id and
       sqh.statusquodate between rp2.date_value1 and rp2.date_value2
       and sqh.subtype in (0, 2)
group  by rp2.reportparam_id, sqi.customer_link_id, c2.customernumber, c2.name, sqi.product_link_id,
          sqi.sizedefinition_link_id, sqi.finishingmethod_id, languages.language_id,
          languages.code;
/
