create or replace view v_notscanneditems_aux as
select uq.uniqueitem_id, 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, uq.lastscandate 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
from
  uniqueitem uq
, product prd, uniqueitemfixedinfo uqfix, sizedefinition sizedef, status, stay, languages
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
;
/