CREATE OR REPLACE VIEW VR_COMPLETENESSROUTEDETAILS AS
select /*VR_COMPLETENESSROUTEDETAILS version 1.0,extra union split for packingnote*/
--unique items inscans
1 linetype, rp.reportparam_id, b.businessunit_id businessunit_id,
 CASE
   WHEN c.staynr = 30 and (st.code = '20' or st.code = '30') THEN 1
   WHEN c.staynr = 32 and (st.code = '20' or st.code = '30') THEN 2
   WHEN c.staynr = 31 and (st.code = '20' or st.code = '30') THEN 3
   WHEN(c.staynr between 30 and 99 and c.staynr <> 36) and (st.code = '20' or st.code = '30') THEN 4
   WHEN c.staynr = 36 and (st.code = '20' or st.code = '30') THEN 5
   WHEN c.staynr = 10 and st.code = '30' THEN 6
 END "STAY",
 b.primaryid idcode, b.uniqueitem_id,
 languages.code languagecode,
 case
      when c.staynr = 36 then GETABSMINDATE()
   else b.planneddeliverydate
 end planneddeliverydate, b.lastinscandate lastinscan_date, b.lastscandate lastscan_date,
 to_char(b.lastinscandate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
 to_char(b.lastscandate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 b.status_id, re.code productcode,
(SELECT description FROM product_DESC w WHERE w.product_id = re.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 ro.routenumber, ro.description routedesc,
 (SELECT description FROM finishingmethod_DESC y,finishingmethod j WHERE y.finishingmethod_id = j.finishingmethod_id
  AND b.finishingmethod_id = j.finishingmethod_id and y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
 rk.code sizedefinitioncode,
 coalesce((SELECT max(fl.flag_id) FROM UNIQUEITEMFLAG Z, flag fl WHERE Z.UNIQUEITEM_ID = b.UNIQUEITEM_ID
   and z.flag_id = fl.flag_id and fl.includeincompletnesscheck = 'Y'),0) FLAG,
 0 FLAG_NO ,
 h.wearernumber, h.fullname wearername, rg.customernumber, rg.name customername, rh.departmentnumber, rh.description departmentdesc,
 ri.street customeraddres
, cast(CONCATENATEWITHBLANK(ri.zipcode, ri.city) as varchar2(255)) customeraddres2,
 ro.route_id, ro.drivingday_id day_id,
 GETDATEDIFF(GETCURRENTDATE(), B.LASTINSCANDATE) MAXCOMPLETENESSDAYS, coalesce((select ca.internalprocessingbu_id from customeractivities ca, productgroup pg
 where ca.customer_id=rg.customer_id and re.productgroup_id=pg.productgroup_id
 and pg.activity_id=ca.activity_id and b.businessunit_id=ca.processbusinessunit_id) ,-1)internalBu ,rc.drivingdate,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , 1 NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from stay c, uniqueitemnonpool e,
 weareremployment f
   left outer join flag f2 on f2.flag_id = f.flag_id
     and f2.includeincompletnesscheck = 'N',
 distributionpoint g, wearer h
   left outer join flag f1 on f1.flag_id = h.flag_id
     and f1.includeincompletnesscheck = 'N',
 uniqueitem b
   inner join businessunit bu
         left outer join systemsetting sst on sst.plant_id = bu.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
   on bu.businessunit_id = b.businessunit_id,status st,
 product re, customer rg, department rh, address ri, languages, sizedefinition rk, route ro, routecontrol rc
 ,STOP LEFT OUTER JOIN DELIVERYPOINT ON STOP.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  LEFT outer JOIN ROUTESTOP ON STOP.ROUTESTOP_ID = ROUTESTOP.ROUTESTOP_ID,
  reportparam rp
where b.poolitem = 2
 and b.stay_id = c.stay_id
 and c.staytype = 1
 and b.uniqueitem_id = e.uniqueitem_id
 and e.weareremployment_id = f.weareremployment_id
 and f.distributionpoint_id = g.distributionpoint_id
 and f.wearer_id = h.wearer_id
 and b.status_id = st.status_id
 and st.statustype = 'UI'
 and b.product_id = re.product_id
 and b.sizedefinition_id=rk.sizedefinition_id
 and h.customer_id = rg.customer_id
 and g.department_id = rh.department_id
 and rg.visitaddress_id = ri.address_id
 and (b.planneddeliverydate <= rc.drivingdate or c.staynr = 36)
 and ro.active = 'Y'
 and c.staynr not in (20,22,23,24,25)
 and ( f1.flag_id is null or h.flagstartdate > rc.drivingdate)
 and ( f2.flag_id is null or f.flagstartdate > rc.drivingdate)
 and not exists ( select 1 from vr_uniqueitemflag uf where  uf.uniqueitem_id = b.uniqueitem_id and uf.uniqueitemflagdate < rp.date_value1)
 and (( rp.number_value2 = 1 and c.staynr <>'37') or rp.number_value2 = 0)
    and STOP.CUSTOMER_LINK_ID = rg.CUSTOMER_ID
  and (nvl(STOP.DELIVERYPOINT_ID, 0) = 0 OR
         (( select locker.DELIVERYPOINT_ID from locker where locker.locker_id = f.locker_id)  = STOP.DELIVERYPOINT_ID) OR
        f.DELIVERYPOINT_ID = STOP.DELIVERYPOINT_ID)
   AND STOP.ROUTECONTROL_ID = rc.routecontrol_id
   and stop.customer_link_id=h.customer_id
   and rc.route_id=ro.route_id
   AND STOP.STATUS IN (0, 3)
   and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=ro.drivingday_id and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=ro.drivingday_id))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=rg.customer_id and stop.routestop_id is not null
          and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1))))
--    and rp.number_value=rc.routecontrol_id
 and ro.routenumber >= rp.number_value8 and ro.routenumber <= rp.number_value9
    and rp.date_value1=rc.originaldrivingdate
union all --unique items outscans, NVL(ds.PACKINGNOTE_ID,0) =0
 select 2 linetype,
 rp.reportparam_id,ds.businessunit_id businessunit_id,
  7 "STAY", b.primaryid idcode,b.uniqueitem_id,
 languages.code languagecode,
  GETABSMINDATE() planneddeliverydate, b.lastinscandate lastinscan_date, b.lastscandate lastscan_date,
to_char(b.lastinscandate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
to_char(b.lastscandate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 b.status_id, re.code productcode,
 (SELECT description FROM product_DESC w WHERE w.product_id = re.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 ro.routenumber, ro.description routedesc,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
  rk.code sizedefinitioncode,
 coalesce((SELECT max(fl.flag_id) FROM UNIQUEITEMFLAG Z, flag fl WHERE Z.UNIQUEITEM_ID = b.UNIQUEITEM_ID
   and z.flag_id = fl.flag_id and fl.includeincompletnesscheck = 'Y'),0) FLAG,
 0 FLAG_NO,
 h.wearernumber, h.fullname wearername, rg.customernumber, rg.name customername, rh.departmentnumber, rh.description departmentdesc,
 ri.street customeraddres
, cast(CONCATENATEWITHBLANK(ri.zipcode, ri.city) as varchar2(255)) customeraddres2,
 ro.route_id, ro.drivingday_id day_id,
 0 MAXCOMPLETENESSDAYS, coalesce(ds.internalprocessingbu_id ,-1)internalBu, rc.drivingdate,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , 1 NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from uniqueitem b
   inner join businessunit bu
         left outer join systemsetting sst on sst.plant_id = bu.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
   on bu.businessunit_id = b.businessunit_id, distributionpoint g , 
  product re, department rh, address ri, languages, customer rg,sizedefinition rk,finishingmethod j, route ro,routecontrol rc
 , deliveryscan ds  left outer join  weareremployment f on  ds.weareremployment_link_id=f.weareremployment_id
                left outer join wearer h on f.wearer_id = h.wearer_id  
 ,routecontrol rc1,STOP
  LEFT OUTER JOIN DELIVERYPOINT ON STOP.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  LEFT outer JOIN ROUTESTOP ON STOP.ROUTESTOP_ID = ROUTESTOP.ROUTESTOP_ID,
  reportparam rp
where  b.stay_id = (select c.stay_id from stay c where  c.staytype = 1 and c.staynr = 20)
 and (f.distributionpoint_id = g.distributionpoint_id or (h.customer_id is null and b.poolitem<> 2))
 and b.status_id in ( select st.status_id from status st  where st.statustype='UI' and (st.code = '20' or st.code = '30'))
 and b.product_id = re.product_id
 and b.finishingmethod_id = j.finishingmethod_id
 and b.sizedefinition_id = rk.sizedefinition_id
 and (h.customer_id = rg.customer_id or( h.customer_id is null and b.poolitem<> 2))
 and g.department_id = rh.department_id
 and rg.visitaddress_id = ri.address_id
 and ro.active = 'Y'
 and ds.uniqueitem_link_id = b.uniqueitem_id
 and ds.businessunit_id = b.businessunit_id
 and ds.distributionpoint_link_id=g.distributionpoint_id
 and rc1.routecontrol_id = ds.routecontrol_id
 and  ( nvl( f.deliverypoint_id,0)=0 or ds.deliverypoint_link_id=f.deliverypoint_id or (h.customer_id is null and b.poolitem<> 2 and g.deliverypoint_id=ds.deliverypoint_link_id))
 and ds.customer_link_id=rg.customer_id
 and rc1.drivingdate<=rc.drivingdate
 and rc.route_id=ro.route_id
 and NVL(ds.PACKINGNOTE_ID,0) =0
 and STOP.CUSTOMER_LINK_ID = rg.CUSTOMER_ID
  and (nvl(STOP.DELIVERYPOINT_ID, 0) = 0 OR
         (( select locker.DELIVERYPOINT_ID from locker where locker.locker_id = f.locker_id)  = STOP.DELIVERYPOINT_ID) OR
        f.DELIVERYPOINT_ID = STOP.DELIVERYPOINT_ID or  (h.customer_id is null and b.poolitem<> 2 and STOP.DELIVERYPOINT_ID=ds.deliverypoint_link_id))
 and STOP.ROUTECONTROL_ID = rc.routecontrol_id
 and stop.customer_link_id=rg.customer_id
 and rc.route_id=ro.route_id
 and STOP.STATUS IN (0, 3)
 and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=ro.drivingday_id and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=ro.drivingday_id))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=rg.customer_id and stop.routestop_id is not null
          and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1))))
 and ds.outscannodelivery in (0,2 )
-- and rp.number_value=rc.routecontrol_id
 and ro.routenumber >= rp.number_value8 and ro.routenumber <= rp.number_value9
 and rp.date_value1=rc.drivingdate
 and ( (rp.number_value5 > 0 and rp.number_value5=ds.plantdepartment_id )
 or ( rp.number_value6 > 0 and rp.number_value6=( select a.plant_id from plantdepartment a where a.plantdepartment_id=ds.plantdepartment_id ) )
 or ( rp.number_value7 > 0 and rp.number_value7 < > ( select a.plant_id from plantdepartment a where a.plantdepartment_id=ds.plantdepartment_id ) )
 or ( rp.number_value5=-1 and rp.number_value6=-1 and rp.number_value7=-1)--all pldep
 )
 union all --unique items outscans, NVL(ds.PACKINGNOTE_ID,0) =packingnote_id in progress
 select 2 linetype,
 rp.reportparam_id,ds.businessunit_id businessunit_id,
  7 "STAY", b.primaryid idcode,b.uniqueitem_id,
 languages.code languagecode,
  GETABSMINDATE() planneddeliverydate, b.lastinscandate lastinscan_date, b.lastscandate lastscan_date,
to_char(b.lastinscandate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
to_char(b.lastscandate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 b.status_id, re.code productcode,
 (SELECT description FROM product_DESC w WHERE w.product_id = re.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 ro.routenumber, ro.description routedesc,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
  rk.code sizedefinitioncode,
 coalesce((SELECT max(fl.flag_id) FROM UNIQUEITEMFLAG Z, flag fl WHERE Z.UNIQUEITEM_ID = b.UNIQUEITEM_ID
   and z.flag_id = fl.flag_id and fl.includeincompletnesscheck = 'Y'),0) FLAG,
 0 FLAG_NO,
 h.wearernumber, h.fullname wearername, rg.customernumber, rg.name customername, rh.departmentnumber, rh.description departmentdesc,
 ri.street customeraddres
, cast(CONCATENATEWITHBLANK(ri.zipcode, ri.city) as varchar2(255)) customeraddres2,
 ro.route_id, ro.drivingday_id day_id,
 0 MAXCOMPLETENESSDAYS, coalesce(ds.internalprocessingbu_id ,-1)internalBu, rc.drivingdate,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , 1 NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from uniqueitem b
   inner join businessunit bu
         left outer join systemsetting sst on sst.plant_id = bu.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
   on bu.businessunit_id = b.businessunit_id, distributionpoint g , 
  product re, department rh, address ri, languages, customer rg,sizedefinition rk,finishingmethod j, route ro,routecontrol rc
 , deliveryscan ds  left outer join  weareremployment f on  ds.weareremployment_link_id=f.weareremployment_id
                left outer join wearer h on f.wearer_id = h.wearer_id,routecontrol rc1,STOP
  LEFT OUTER JOIN DELIVERYPOINT ON STOP.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  LEFT outer JOIN ROUTESTOP ON STOP.ROUTESTOP_ID = ROUTESTOP.ROUTESTOP_ID,
  reportparam rp
where  b.stay_id = (select c.stay_id from stay c where  c.staytype = 1 and c.staynr = 20)
 and (f.distributionpoint_id = g.distributionpoint_id or (h.customer_id is null and b.poolitem<> 2))
 and b.status_id in ( select st.status_id from status st  where st.statustype='UI' and (st.code = '20' or st.code = '30'))
 and b.product_id = re.product_id
 and b.finishingmethod_id = j.finishingmethod_id
 and b.sizedefinition_id = rk.sizedefinition_id
 and (h.customer_id = rg.customer_id or( h.customer_id is null and b.poolitem<> 2))
 and g.department_id = rh.department_id
 and rg.visitaddress_id = ri.address_id
 and ro.active = 'Y'
 and ds.uniqueitem_link_id = b.uniqueitem_id
 and ds.businessunit_id = b.businessunit_id
 and ds.distributionpoint_link_id=g.distributionpoint_id
 and rc1.routecontrol_id = ds.routecontrol_id
 and  ( nvl( f.deliverypoint_id,0)=0 or ds.deliverypoint_link_id=f.deliverypoint_id or (h.customer_id is null and b.poolitem<> 2 and g.deliverypoint_id=ds.deliverypoint_link_id))
 and ds.customer_link_id=rg.customer_id
 and rc1.drivingdate<=rc.drivingdate
 and rc.route_id=ro.route_id
 and NVL(ds.PACKINGNOTE_ID,0) in ( select pn.packingnote_id  from packingnote pn, status s
                                 where  pn.routecontrol_id=rc.routecontrol_id
                                 and pn.status_id=s.status_id and s.statustype='PN' and s.code ='20')
 and STOP.CUSTOMER_LINK_ID = rg.CUSTOMER_ID
  and (nvl(STOP.DELIVERYPOINT_ID, 0) = 0 OR
         (( select locker.DELIVERYPOINT_ID from locker where locker.locker_id = f.locker_id)  = STOP.DELIVERYPOINT_ID) OR
        f.DELIVERYPOINT_ID = STOP.DELIVERYPOINT_ID or  (h.customer_id is null and b.poolitem<> 2 and STOP.DELIVERYPOINT_ID=ds.deliverypoint_link_id))
 and STOP.ROUTECONTROL_ID = rc.routecontrol_id
 and stop.customer_link_id=rg.customer_id
 and rc.route_id=ro.route_id
 and STOP.STATUS IN (0, 3)
 and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=ro.drivingday_id and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=ro.drivingday_id))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=rg.customer_id and stop.routestop_id is not null
          and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1))))
 and ds.outscannodelivery in (0,2 )
-- and rp.number_value=rc.routecontrol_id
 and ro.routenumber >= rp.number_value8 and ro.routenumber <= rp.number_value9
 and rp.date_value1=rc.drivingdate
 and ( (rp.number_value5 > 0 and rp.number_value5=ds.plantdepartment_id )
 or ( rp.number_value6 > 0 and rp.number_value6=( select a.plant_id from plantdepartment a where a.plantdepartment_id=ds.plantdepartment_id ) )
 or ( rp.number_value7 > 0 and rp.number_value7 < > ( select a.plant_id from plantdepartment a where a.plantdepartment_id=ds.plantdepartment_id ) )
 or ( rp.number_value5=-1 and rp.number_value6=-1 and rp.number_value7=-1)--all pldep
 )
union all --NonUnique items in scans
select 3 linetype, rp.reportparam_id, bu.businessunit_id,
 1 "STAY",
 '' idcode, 0 uniqueitem_id,
 languages.code languagecode,
 dv.deliverydate planneddeliverydate, null lastinscan_date, dv.deliverydate lastscan_date,
 to_char(dv.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
 to_char(dv.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 -1 status_id, re.code productcode,
(SELECT description FROM product_DESC w WHERE w.product_id = re.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 ro.routenumber, ro.description routedesc,
 (SELECT y.description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  and y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
 rk.code sizedefinitioncode,
 0 FLAG,
 0 FLAG_NO ,
 -1 wearernumber, '' wearername, rg.customernumber, rg.name customername, rh.departmentnumber, rh.description departmentdesc,
 ri.street customeraddres
, cast(CONCATENATEWITHBLANK(ri.zipcode, ri.city) as varchar2(255)) customeraddres2,
 ro.route_id, ro.drivingday_id day_id,
 0 MAXCOMPLETENESSDAYS, coalesce((select ca.internalprocessingbu_id from customeractivities ca, productgroup pg
 where ca.customer_id=rg.customer_id and re.productgroup_id=pg.productgroup_id
 and pg.activity_id=ca.activity_id and bu.businessunit_id=ca.processbusinessunit_id) ,-1)internalBu ,rc.drivingdate,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , dv.amount NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from DeliveryVariations dv,
 distributionpoint g,
 product re, customer rg, department rh, address ri, languages, sizedefinition rk,finishingmethod j, route ro, routecontrol rc
 ,STOP LEFT OUTER JOIN DELIVERYPOINT ON STOP.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  LEFT outer JOIN ROUTESTOP ON STOP.ROUTESTOP_ID = ROUTESTOP.ROUTESTOP_ID,
  reportparam rp,businessunit bu
         left outer join systemsetting sst on sst.plant_id = bu.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
where dv.distributionpoint_id = g.distributionpoint_id
 and dv.product_id = re.product_id
 and dv.sizedefinition_id=rk.sizedefinition_id
 and g.department_id = rh.department_id
 and rh.customer_id = rg.customer_id
 and rg.visitaddress_id = ri.address_id
 and ro.active = 'Y'
 and STOP.CUSTOMER_LINK_ID = rg.CUSTOMER_ID
   AND STOP.ROUTECONTROL_ID = rc.routecontrol_id
   and rc.route_id=ro.route_id
   AND STOP.STATUS IN (0, 3)
   and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=ro.drivingday_id and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=ro.drivingday_id))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=rg.customer_id and stop.routestop_id is not null
          and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1))))
--    and rp.number_value=rc.routecontrol_id
 and ro.routenumber >= rp.number_value8 and ro.routenumber <= rp.number_value9
    and rp.date_value1=rc.originaldrivingdate
    and dv.distributionpoint_id = g.distributionpoint_id and dv.product_id = re.product_id and dv.sizedefinition_id =rk.sizedefinition_id
      and dv.finishingmethod_id = j.finishingmethod_id
      and (dv.packingnote_id is null or exists (select 1 from packingnote pn, status stat where dv.packingnote_id = pn.packingnote_id
          and pn.status_id = stat.status_id and stat.statustype = 'PN' and stat.code = '20' and pn.deliverydate <= rp.date_value1
          and pn.routecontrol_id = rc.routecontrol_id and pn.stop_id = stop.stop_id and pn.route_id = ro.route_id))
      and dv.origin  in (1, 5) --Soil Count & Delivery Note
      and dv.deliverydate <= rp.date_value1
    and sst.value = '0' and dv.amount > 0
union all --Non Unique items outscans
 select 4 linetype,
 rp.reportparam_id, bu.businessunit_id,
  7 "STAY", '' idcode, 0 uniqueitem_id,
 languages.code languagecode,
  ccd.deliverydate planneddeliverydate, null lastinscan_date, ccd.deliverydate lastscan_date,
to_char(ccd.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
to_char(ccd.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 -1 status_id, re.code productcode,
 (SELECT description FROM product_DESC w WHERE w.product_id = re.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 ro.routenumber, ro.description routedesc,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
  rk.code sizedefinitioncode,
 0 FLAG,
 0 FLAG_NO,
 -1 wearernumber, '' wearername, rg.customernumber, rg.name customername, rh.departmentnumber, rh.description departmentdesc,
 ri.street customeraddres
, cast(CONCATENATEWITHBLANK(ri.zipcode, ri.city) as varchar2(255)) customeraddres2,
 ro.route_id, ro.drivingday_id day_id,
 0 MAXCOMPLETENESSDAYS, coalesce((select ca.internalprocessingBu_id from customeractivities ca, productgroup pg
 where ca.customer_id=rg.customer_id and re.productgroup_id=pg.productgroup_id
 and pg.activity_id=ca.activity_id and bu.businessunit_id=ca.processbusinessunit_id) ,-1) internalBu, rc.drivingdate,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , (ccd.quantity+ccd.quantitycog) NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from businessunit bu
         left outer join systemsetting sst on sst.plant_id = bu.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
 , distributionpoint g, product re, department rh, address ri, languages, customer rg,sizedefinition rk,finishingmethod j, route ro,routecontrol rc
 , routecontrol rc1, STOP
  LEFT OUTER JOIN DELIVERYPOINT ON STOP.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  LEFT outer JOIN ROUTESTOP ON STOP.ROUTESTOP_ID = ROUTESTOP.ROUTESTOP_ID,
  reportparam rp, Cleancountdelivery ccd
where  ccd.distributionpoint_id = g.distributionpoint_id
 and ccd.product_id = re.product_id
 and ccd.finishingmethod_id = j.finishingmethod_id
 and ccd.sizedefinition_id = rk.sizedefinition_id
 and g.department_id = rh.department_id
 and rh.customer_id = rg.customer_id
 and rg.visitaddress_id = ri.address_id
 and ro.active = 'Y'
 and rc1.drivingdate<=rc.drivingdate
 and rc.route_id=ro.route_id
 and STOP.CUSTOMER_LINK_ID = rg.CUSTOMER_ID
 and STOP.ROUTECONTROL_ID = rc.routecontrol_id
 and rc.route_id=ro.route_id
 and STOP.STATUS IN (0, 3)
 and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=ro.drivingday_id and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=rg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=ro.drivingday_id))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=rg.customer_id and stop.routestop_id is not null
          and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1))))
-- and rp.number_value=rc.routecontrol_id
 and ro.routenumber >= rp.number_value8 and ro.routenumber <= rp.number_value9
 and rp.date_value1=rc.drivingdate
 and (ccd.packingnote_id is null or exists (select 1 from packingnote pn, status stat where ccd.packingnote_id = pn.packingnote_id
          and pn.status_id = stat.status_id and stat.statustype = 'PN' and stat.code = '20' and pn.deliverydate <= rp.date_value1
          and pn.routecontrol_id = rc.routecontrol_id and pn.stop_id = stop.stop_id and pn.route_id = ro.route_id))
  and sst.value = '0'
;
/