create or replace view v_notscanneditemsrv as
select  --showing ID code, detailed layout
1 linetype, vl.customernumber, vl.customerName, vl.wearernumber, vl.wearerName, vl.departmentnumber
, vl.departmentDescription, vl.distributionpointnumber, vl.distributionpointDescription
, vl.customerLanguageID, vl.businessunit_id, vl.BU_code, vl.BU_desc
, prd.code productCode
, (select product_desc.description from product_desc where product_desc.product_id = prd.product_id
and product_desc.language_id = languages.language_id) productDescription
, sizedef.code sizedefinitionCode
, (select sizedefinition_desc.description from sizedefinition_desc
where sizedefinition_desc.sizedefinition_id = sizedef.sizedefinition_id
and sizedefinition_desc.language_id = languages.language_id) sizedefinitionDescription
, uq.primaryid, 1 IDCounter
, uq.lastscandate, uqfix.lastissuedate issuedate
, status.code statusCode
, (select status_desc.description from status_desc where status_desc.status_id = status.status_id
and status_desc.language_id = languages.language_id) statusdescription
, stay.staynr stayNumber
, (select stay_desc.description from stay_desc where stay_desc.stay_id = stay.stay_id
and stay_desc.language_id = languages.language_id ) StayDescription
, languages.language_id languageLanguageID, languages.code languageCode, vl.washestotal
, finishingmethod.code finishingmethodCode
, (select finishingmethod_desc.description from finishingmethod_desc
where finishingmethod_desc.finishingmethod_id =  finishingmethod.finishingmethod_id
and finishingmethod_desc.language_id = languages.language_id) finishingmethodDescription
, rp.number_value3 residualvalue, rp.number_value5 rvpercentage, vl.chain_code
, (select sp.sequencenumber from sizespersizetype sp
          where sp.sizetype_id = prd.sizetype_id and sp.sizedefinition_id = sizedef.sizedefinition_id) SizeSequenceNr
, vl.bank, vl.locker, rp.reportparam_id
from REPORTPARAM rp, uniqueitem uq
   left outer join finishingmethod  on finishingmethod.finishingmethod_id = uq.finishingmethod_id
   left outer join (uniqueitemflag uqf join flag on uqf.flag_id = flag.flag_id    )
                  on uq.uniqueitem_id = uqf.uniqueitem_id
, product prd, uniqueitemfixedinfo uqfix, sizedefinition sizedef, status, stay, languages
, v_notscanneditemsRV_line vl
where uq.product_id = prd.product_id
and uq.uniqueitem_id = uqfix.uniqueitem_id
and uq.sizedefinition_id = sizedef.sizedefinition_id
and uq.status_id = status.status_id
and status.statustype = 'UI'
and uq.stay_id = stay.stay_id
and vl.uniqueitem_id = uq.uniqueitem_id
and uq.uniqueitem_id = rp.number_value
and ((rp.string_value1 = ' ' and rp.string_value2 = 'ZZZZZZ')
      or
    (flag.code >= rp.string_value1 and flag.code <= rp.string_value2))
union all --not show ID code, condensed layout
select 2 linetype, vl.customernumber, vl.customerName, vl.wearernumber, vl.wearerName, vl.departmentnumber
, vl.departmentDescription, vl.distributionpointnumber, vl.distributionpointDescription
, vl.customerLanguageID, vl.businessunit_id, vl.BU_code, vl.BU_desc
, prd.code productCode
, (select product_desc.description from product_desc where product_desc.product_id = prd.product_id
and product_desc.language_id = languages.language_id) productDescription
, sizedef.code sizedefinitionCode
, (select sizedefinition_desc.description from sizedefinition_desc
where sizedefinition_desc.sizedefinition_id = sizedef.sizedefinition_id
and sizedefinition_desc.language_id = languages.language_id) sizedefinitionDescription
, '' primaryid, count(uq.primaryid) IDCounter
, uq.lastscandate, sysdate issuedate
, status.code statusCode
, (select status_desc.description from status_desc where status_desc.status_id = status.status_id
and status_desc.language_id = languages.language_id) statusdescription
, 1 stayNumber
, ' ' StayDescription
, languages.language_id languageLanguageID, languages.code languageCode, sum(vl.washestotal) washestotal
, finishingmethod.code finishingmethodCode
, (select finishingmethod_desc.description from finishingmethod_desc
where finishingmethod_desc.finishingmethod_id =  finishingmethod.finishingmethod_id
and finishingmethod_desc.language_id = languages.language_id) finishingmethodDescription
, sum(rp.number_value3) residualvalue, sum(rp.number_value5) rvpercentage, vl.chain_code
, (select sp.sequencenumber from sizespersizetype sp
          where sp.sizetype_id = prd.sizetype_id and sp.sizedefinition_id = sizedef.sizedefinition_id) SizeSequenceNr
, vl.bank, vl.locker, rp.reportparam_id
from REPORTPARAM rp, uniqueitem uq
   left outer join finishingmethod  on finishingmethod.finishingmethod_id = uq.finishingmethod_id
   left outer join (uniqueitemflag uqf join flag on uqf.flag_id = flag.flag_id    )
                  on uq.uniqueitem_id = uqf.uniqueitem_id
, product prd, uniqueitemfixedinfo uqfix, sizedefinition sizedef, status, stay, languages
, v_notscanneditemsRV_line vl
where uq.product_id = prd.product_id
and uq.uniqueitem_id = uqfix.uniqueitem_id
and uq.sizedefinition_id = sizedef.sizedefinition_id
and uq.status_id = status.status_id
and status.statustype = 'UI'
and uq.stay_id = stay.stay_id
and vl.uniqueitem_id = uq.uniqueitem_id
and uq.uniqueitem_id = rp.number_value
and ((rp.string_value1 = ' ' and rp.string_value2 = 'ZZZZZZ')
      or
    (flag.code >= rp.string_value1 and flag.code <= rp.string_value2))  
group by vl.customernumber, vl.customerName, vl.wearernumber, vl.wearerName, vl.departmentnumber
, vl.departmentDescription, vl.distributionpointnumber, vl.distributionpointDescription
, vl.customerLanguageID, vl.businessunit_id, vl.BU_code, vl.BU_desc
, prd.code, prd.product_id, languages.language_id
, sizedef.code, sizedef.sizedefinition_id--, uq.primaryid, uqfix.lastissuedate, rp.number_value3, vl.washestotal
, uq.lastscandate, status.code, status.status_id
--, stay.staynr, stay.stay_id, rp.number_value5
, languages.code, finishingmethod.code, finishingmethod.finishingmethod_id
, vl.chain_code
, prd.sizetype_id, vl.bank, vl.locker, rp.reportparam_id;
/
