create or replace view v_notscanneditems_line as
select 1 linetype, uq.uniqueitem_id, uq.businessunit_id
, cu.customernumber, cu.name customerName, we.wearernumber, we.fullname wearerName, department.departmentnumber
, department.description departmentDescription, distributionpoint.distributionpointnumber
, distributionpoint.description distributionpointDescription, cu.language_id customerLanguageID
, lo.locker, lo.bank
, F_ROUTEFILTER(cu.customer_id, distributionpoint.deliverypoint_id, rp.number_value1, rp.number_value2) routefilter
, rp.reportparam_id, rp.number_value3 ResidualValue
, (select ch.code from chain ch where ch.chain_id = cu.chain_id) chain_code
from
  uniqueitem uq, customer cu
  , wearer we, weareremployment wemp
  left outer join locker lo on wemp.locker_id = lo.locker_id
  , distributionpoint, department, uniqueitemnonpool uqnoo, reportparam rp
where
    uq.poolitem = 2
and uq.uniqueitem_id = uqnoo.uniqueitem_id
    and uqnoo.weareremployment_id = wemp.weareremployment_id
             and wemp.distributionpoint_id = distributionpoint.distributionpoint_id
                 and distributionpoint.department_id = department.department_id
                     and department.customer_id = cu.customer_id
        and wemp.wearer_id = we.wearer_id and uq.uniqueitem_id = rp.number_value
      --      and we.customer_id = cu.customer_id
UNION ALL
select 2 linetype, uq.uniqueitem_id, uq.businessunit_id
, cu.customernumber, cu.name customerName, -1 wearernumber, null wearerName, department.departmentnumber
, department.description departmentDescription, distributionpoint.distributionpointnumber
, distributionpoint.description distributionpointDescription, cu.language_id customerLanguageID
--, lo.locker, lo.bank
, '' locker, '' bank
, F_ROUTEFILTER(cu.customer_id, -1, rp.number_value1, rp.number_value2) routefilter
, rp.reportparam_id, rp.number_value3 ResidualValue
, (select ch.code from chain ch where ch.chain_id = cu.chain_id) chain_code
from
  uniqueitem uq, customer cu
  --left outer join locker lo on cu.customer_id = lo.customer_id
  , distributionpoint, department --, wearer we
  , reportparam rp
where
    uq.poolitem = 1
and uq.distributionpoint_id is not null
and uq.distributionpoint_id = distributionpoint.distributionpoint_id
    and distributionpoint.department_id = department.department_id
        and department.customer_id = cu.customer_id and rp.number_value = -1--uq.uniqueitem_id
            /*and cu.customer_id = we.customer_id*/
UNION ALL
select 3 linetype, uq.uniqueitem_id, uq.businessunit_id
, cu.customernumber, cu.name customerName, -1 wearernumber, null wearerName, null departmentnumber
, null departmentDescription, null distributionpointnumber
, null distributionpointDescription, cu.language_id customerLanguageID
--, lo.locker, lo.bank
, '' locker, '' bank
, 1 routefilter
, rp.reportparam_id, rp.number_value3 ResidualValue
, (select ch.code from chain ch where ch.chain_id = cu.chain_id) chain_code
from
  uniqueitem uq, customer cu
 -- left outer join locker lo on cu.customer_id = lo.customer_id
  /*, wearer we , distributionpoint, department*/
  , reportparam rp
where
    uq.poolitem = 1
and uq.distributionpoint_id is null
AND uq.relatedcustomer_id = cu.customer_id and rp.number_value = -1--uq.uniqueitem_id
    /*and cu.customer_id = department.customer_id
        and department.department_id = distributionpoint.department_id
            and cu.customer_id = we.customer_id*/
UNION ALL
select 4 linetype, uq.uniqueitem_id, uq.businessunit_id
, -1 customernumber, '' customerName, -1 wearernumber, '' wearerName, -1 departmentnumber
, '' departmentDescription, -1 distributionpointnumber, '' distributionpointDescription
, -1 customerLanguageID
, '' locker, '' bank
, 1 routefilter
, rp.reportparam_id, rp.number_value3 ResidualValue
, '' chain_code
from
  uniqueitem uq, reportparam rp
where
    uq.poolitem = 0 and rp.number_value = -1--uq.uniqueitem_id
;
/