CREATE OR REPLACE VIEW V_GARMENTSPERWEARER AS
select 1 linetype,
 prd.code productcode
, sz.code sizecode
, ( SELECT pd.description FROM product_desc pd WHERE pd.product_id = prd.product_id
  AND pd.LANGUAGE_ID = languages.LANGUAGE_ID) productdesc
, ( SELECT sdd.description FROM sizedefinition_desc sdd WHERE sdd.sizedefinition_id = sz.sizedefinition_id
  AND sdd.LANGUAGE_ID = languages.LANGUAGE_ID) sizedesc
, wf.code functioncode, wf.description functiondesc, wey.sequencenumber employment
, cast(CONCATENATEWITHBLANK(lk.bank, lk.locker) as varchar2(255)) banklocker
, gd.code garmentdispenser, uq.primaryid idcode, uq.idcodesequencenumber seqnumber
, uqfx.lastissuedate issuedate, uq.washestotal washno, uq.repairstotal repairno, uq.rewashtotal rewashno
, ( SELECT COUNT(*) FROM UNIQUEITEMFLAG uf WHERE uf.UNIQUEITEM_ID = uq.UNIQUEITEM_ID) flag
, ( SELECT max(fg.code) FROM UNIQUEITEMFLAG uf, flag fg WHERE uf.UNIQUEITEM_ID = uq.UNIQUEITEM_ID and uf.flag_id = fg.flag_id) uiflagcode
, ( select count(*) from uniqueitemmodification um where um.uniqueitem_id=uq.uniqueitem_id) modifications
, r.code reason, uq.customerowned, uqnp.freeextragarment
, GETDATEONLY(uq.lastscandate) lastscandate, GETDATEONLY(uq.lastoutscandate) lastoutscandate, GETDATEONLY(uq.lastinscandate) lastinscan
, fm.code finmethod
, (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id = fm.finishingmethod_id
     and fmd.language_id = languages.language_id) finmethoddesc
,currency.invoiceamountdecimals, uq.residualvalueAmount residualvalue, flag.code flagcode
, ( SELECT cast(sty.description as varchar2(10)) FROM stay_desc sty WHERE sty.stay_id = stay.stay_id
  AND sty.LANGUAGE_ID = languages.LANGUAGE_ID) stay, stay.staynr
, ( SELECT description FROM status_desc sd WHERE sd.status_id = uq.status_id
  AND sd.LANGUAGE_ID = languages.LANGUAGE_ID) status
, we.customeremployeenumber adminnumber,  cc.code costcentercode, cc.description costcenterdesc
, de.departmentnumber, de.description departmentdesc
, cu.customernumber, cu.name customername,
 coalesce((select max(ca.processbusinessunit_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) processbusinessunit_id,
coalesce((select max(ca.internalprocessingbu_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) intprocessbusinessunit_id,
address.city, cu.defaultbusinessunit_id
, we.wearernumber, we.fullname wearername, we.searchname wearersearchname, we.firstname wearerfirstname
, GETDATEDIFF(GETCURRENTDATE(), we.dateactive)*GETDATEDIFF(we.dateinactive, GETCURRENTDATE()) weareractive
, we.dateactive WearerDateActive, we.dateinactive WearerDateInactive
, languages.code languagecode, languages.language_id, cu.language_id customerlanguage_id
, case when uq.substituteforproduct_id is null then 'N' else 'Y' end substituteYN
, coalesce(qg.code, '') QualityGrade_Code
, uq.leaseweeks leaseWeeks
from
  customer cu
, wearer we
    left outer join flag on we.flag_id = flag.flag_id
, weareremployment wey
    left outer join garmentdispenser gd on gd.garmentdispenser_id = wey.garmentdispenser_id
    left outer join locker lk on lk.locker_id = wey.locker_id
    left outer join wearerfunction wf on wey.wearerfunction_id = wf.wearerfunction_id
, uniqueitemnonpool uqnp
, uniqueitem uq
    left outer join stay on uq.stay_id = stay.stay_id
    left outer join finishingmethod fm on fm.finishingmethod_id = uq.finishingmethod_id
, uniqueitemfixedinfo uqfx
    left outer join qualitygrade qg on uqfx.qualitygrade_id=qg.qualitygrade_id
, reasoncode r
, product prd,  sizedefinition sz
, distributionpoint dip, department de, costcenter cc
, address, currency, languages
where
    cu.customer_id = we.customer_id
and we.wearer_id = wey.wearer_id
and wey.weareremployment_id = uqnp.weareremployment_id
and uqnp.uniqueitem_id = uq.uniqueitem_id
    and uq.uniqueitem_id = uqfx.uniqueitem_id
        and uqfx.reasoncode_id = r.reasoncode_id
and uq.product_id = prd.product_id
and uq.sizedefinition_id = sz.sizedefinition_id
and wey.distributionpoint_id = dip.distributionpoint_id
    and dip.department_id = de.department_id
        and dip.costcenter_id = cc.costcenter_id
and cu.mailaddress_id = address.address_id
and cu.currency_id = currency.currency_id
Union all
select 2 linetype,
  coalesce(subtPrd.Code, prd.code) productcode
, coalesce(subtSz.Code, sz.code) sizecode
, coalesce(
  ( SELECT description FROM product_desc WHERE product_desc.product_id = subtPrd.product_id
  AND product_desc.LANGUAGE_ID = languages.LANGUAGE_ID )
  , ( SELECT description FROM product_desc pd WHERE pd.product_id = prd.product_id
  AND pd.LANGUAGE_ID = languages.LANGUAGE_ID)
  ) productdesc
, ( SELECT sdd.description FROM sizedefinition_desc sdd WHERE sdd.sizedefinition_id = coalesce(subtSz.sizedefinition_id, sz.sizedefinition_id)
  AND sdd.LANGUAGE_ID = languages.LANGUAGE_ID) sizedesc
, wf.code functioncode, wf.description functiondesc, wey.sequencenumber employment
, cast(CONCATENATEWITHBLANK(lk.bank, lk.locker) as varchar2(255)) banklocker
, gd.code garmentdispenser, null idcode, nuq.sequencenumber seqnumber
, nuq.issuedate issuedate, null washno, null repairno, null rewashno, null flag, null uiflagcode
, ( SELECT count(*) FROM wearerinventorymodification wim WHERE wim.wearerinventory_id = weinv.wearerinventory_id) modifications
, rc.code reason, 'N' customerowned, 'X' freeextragarment
, null lastscandate, null lastoutscandate, null lastinscandate
, fm.code finmethod
, (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id = fm.finishingmethod_id
     and fmd.language_id = languages.language_id) finmethoddesc
, currency.invoiceamountdecimals, 0 residualvalue, flag.code flagcode
, null stay, null staynr, null status
, we.customeremployeenumber adminnumber, cc.code costcentercode, cc.description costcenterdesc
, de.departmentnumber, de.description departmentdesc
, cu.customernumber, cu.name customername,
coalesce((select max(ca.processbusinessunit_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) processbusinessunit_id,
coalesce((select max(ca.internalprocessingbu_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) intprocessbusinessunit_id,
 address.city, cu.defaultbusinessunit_id
, we.wearernumber, we.fullname wearername, we.searchname wearersearchname, we.firstname wearerfirstname
, GETDATEDIFF(GETCURRENTDATE(), we.dateactive)*GETDATEDIFF(we.dateinactive, GETCURRENTDATE()) weareractive
, we.dateactive WearerDateActive, we.dateinactive WearerDateInactive
, languages.code languagecode, languages.language_id, cu.language_id customerlanguage_id
, case when nuq.substituteforproduct_id is null then 'N' else 'Y' end substituteYN
, '' QualityGrade_Code
, null leaseWeeks
from
  customer cu
, wearer we
    left outer join flag  on we.flag_id = flag.flag_id
, weareremployment wey
    left outer join garmentdispenser gd on gd.garmentdispenser_id = wey.garmentdispenser_id
    left outer join locker lk on lk.locker_id = wey.locker_id
    left outer join wearerfunction wf on wey.wearerfunction_id = wf.wearerfunction_id
, wearerinventory weinv
, nonuniqueitem nuq
    left outer join product subtPrd on subtPrd.product_id = nuq.substituteforproduct_id
    left outer join sizedefinition subtSz on subtSz.Sizedefinition_Id = nuq.substituteforsizedefinition_id
, product prd, sizedefinition sz, finishingmethod fm
, reasoncode rc
, distributionpoint dip, department de, costcenter cc
, address, currency, languages
where
    cu.customer_id = we.customer_id
and we.wearer_id = wey.wearer_id
and wey.weareremployment_id = weinv.weareremployment_id
AND weinv.product_id = prd.product_id
and weinv.sizedefinition_id = sz.sizedefinition_id
and weinv.finishingmethod_id = fm.finishingmethod_id
and weinv.wearerinventory_id = nuq.wearerinventory_id
    and nuq.reasoncode_id = rc.reasoncode_id
and wey.distributionpoint_id = dip.distributionpoint_id
    and dip.department_id = de.department_id
        and dip.costcenter_id = cc.costcenter_id
and cu.mailaddress_id = address.address_id
    and cu.currency_id = currency.currency_id
Union all --20013161 Show also garments that need to be replaced or returned.
select 3 linetype,
 prd.code productcode
, sz.code sizecode
, ( SELECT description FROM product_desc pd WHERE pd.product_id = prd.product_id
  AND pd.LANGUAGE_ID = languages.LANGUAGE_ID) productdesc
, ( SELECT sdd.description FROM sizedefinition_desc sdd WHERE sdd.sizedefinition_id = sz.sizedefinition_id
  AND sdd.LANGUAGE_ID = languages.LANGUAGE_ID) sizedesc
, wf.code functioncode, wf.description functiondesc, wey.sequencenumber employment
, cast(CONCATENATEWITHBLANK(lk.bank, lk.locker) as varchar2(255)) banklocker
, gd.code garmentdispenser, uq.primaryid idcode, uq.idcodesequencenumber seqnumber
, uqfx.lastissuedate issuedate, uq.washestotal washno, uq.repairstotal repairno, uq.rewashtotal rewashno
, ( SELECT COUNT(*) FROM UNIQUEITEMFLAG uf, automaticflag af WHERE uf.UNIQUEITEM_ID = uq.UNIQUEITEM_ID and af.flag_id = uf.flag_id
           and uf.startdate <= GETCURRENTDATE() and af.automaticflag_id in (12, 4, 5, 7, 2)) flag
, ( SELECT max(fg.code) FROM UNIQUEITEMFLAG uf, flag fg, automaticflag af WHERE uf.UNIQUEITEM_ID = uq.UNIQUEITEM_ID and uf.flag_id = fg.flag_id and af.flag_id = uf.flag_id
           and uf.startdate <= GETCURRENTDATE() and af.automaticflag_id in (12, 4, 5, 7, 2)) uiflagcode
, ( select count(*) from uniqueitemmodification um where um.uniqueitem_id=uq.uniqueitem_id) modifications
, r.code reason, uq.customerowned, uqnp.freeextragarment
, GETDATEONLY(uq.lastscandate) lastscandate, GETDATEONLY(uq.lastoutscandate) lastoutscandate,  GETDATEONLY(uq.lastinscandate) lastinscandate
, fm.code finmethod
, (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id = fm.finishingmethod_id
     and fmd.language_id = languages.language_id) finmethoddesc
,currency.invoiceamountdecimals, uq.residualvalueAmount residualvalue, flag.code flagcode
, ( SELECT cast(sty.description as varchar2(10)) FROM stay_desc sty WHERE sty.stay_id = stay.stay_id
  AND sty.LANGUAGE_ID = languages.LANGUAGE_ID) stay, stay.staynr
, ( SELECT description FROM status_desc sd WHERE sd.status_id = uq.status_id
  AND sd.LANGUAGE_ID = languages.LANGUAGE_ID) status
, we.customeremployeenumber adminnumber,  cc.code costcentercode, cc.description costcenterdesc
, de.departmentnumber, de.description departmentdesc
, cu.customernumber, cu.name customername,
 coalesce((select max(ca.processbusinessunit_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) processbusinessunit_id,
coalesce((select max(ca.internalprocessingbu_id) from productgroup pg,  customeractivities ca
where prd.productgroup_id=pg.productgroup_id and pg.activity_id=ca.activity_id and ca.customer_id=cu.customer_id),cu.defaultbusinessunit_id) intprocessbusinessunit_id,
address.city, cu.defaultbusinessunit_id
, we.wearernumber, we.fullname wearername, we.searchname wearersearchname, we.firstname wearerfirstname
, (-1)*GETDATEDIFF(GETCURRENTDATE(), we.dateactive)*GETDATEDIFF(we.dateinactive, GETCURRENTDATE()) weareractive--select only active wearers
, we.dateactive WearerDateActive, we.dateinactive WearerDateInactive
, languages.code languagecode, languages.language_id, cu.language_id customerlanguage_id
, case when uq.substituteforproduct_id is null then 'N' else 'Y' end substituteYN
, coalesce(qg.code, '') QualityGrade_Code
, uq.leaseweeks leaseWeeks
from
  customer cu
, wearer we
    left outer join flag on we.flag_id = flag.flag_id
, weareremployment wey
    left outer join garmentdispenser gd on gd.garmentdispenser_id = wey.garmentdispenser_id
    left outer join locker lk on lk.locker_id = wey.locker_id
    left outer join wearerfunction wf on wey.wearerfunction_id = wf.wearerfunction_id
, uniqueitemnonpool uqnp
, uniqueitem uq
    left outer join stay on uq.stay_id = stay.stay_id
    left outer join finishingmethod fm on fm.finishingmethod_id = uq.finishingmethod_id
, uniqueitemfixedinfo uqfx
    left outer join qualitygrade qg on uqfx.qualitygrade_id = qg.qualitygrade_id
, reasoncode r
, product prd,  sizedefinition sz
, distributionpoint dip, department de, costcenter cc
, address, currency, languages
where
    cu.customer_id = we.customer_id
and we.wearer_id = wey.wearer_id
and wey.weareremployment_id = uqnp.weareremployment_id
and uqnp.uniqueitem_id = uq.uniqueitem_id
    and uq.uniqueitem_id = uqfx.uniqueitem_id
        and uqfx.reasoncode_id = r.reasoncode_id
and uq.product_id = prd.product_id
and uq.sizedefinition_id = sz.sizedefinition_id
and wey.distributionpoint_id = dip.distributionpoint_id
    and dip.department_id = de.department_id
        and dip.costcenter_id = cc.costcenter_id
and cu.mailaddress_id = address.address_id
and cu.currency_id = currency.currency_id
and exists (SELECT 1 FROM UNIQUEITEMFLAG uf, automaticflag af WHERE uf.UNIQUEITEM_ID = uq.UNIQUEITEM_ID and af.flag_id = uf.flag_id
           and uf.startdate <= GETCURRENTDATE() and af.automaticflag_id in (12, 4, 5, 7, 2) )
;
/