CREATE OR REPLACE VIEW VR_COMPLETENESSCUSTOMER AS
select rp.reportparam_id,b.businessunit_id businessunit_id,
       languages.code    languagecode,
         case
          when c.staynr = 36 then
          getabsmindate()
         else
          b.planneddeliverydate
       end planneddeliverydate,
       b.lastinscandate,
       b.status_id,
       ce.code productcode,
       (select description
          from product_desc w
         where w.product_id = ce.product_id
           and w.language_id = languages.language_id) productdesc,
       1 routenumber,
       '' routedesc,
       cg.customernumber,
       cg.name customername,
       ch.departmentnumber,
       ch.description departmentdesc,
      -1 day_id,
       cast(concatenatewithblank3(ci.street, ci.zipcode, ci.city) as
            varchar2(255)) customeraddres,
       b.primaryid idcode,
       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 33 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",
       0 flag_no,
        getdatediff(getcurrentdate(), b.lastinscandate) maxcompletenessdays,
       coalesce((select ca.internalprocessingBu_id from customeractivities ca, productgroup pg
 where ca.customer_id=cg.customer_id and ce.productgroup_id=pg.productgroup_id
 and pg.activity_id=ca.activity_id and b.businessunit_id=ca.processbusinessunit_id) ,-1)internalBu
  from stay c,reportparam rp, 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,
 status st, product ce,
 department ch, address ci, customer cg, languages
  where b.poolitem = 2
   and b.stay_id = c.stay_id
   and c.staytype = 1
   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 = ce.product_id
   and h.customer_id = cg.customer_id
   and g.department_id = ch.department_id
   and cg.visitaddress_id = ci.address_id
   and b.uniqueitem_id = e.uniqueitem_id
   and (b.planneddeliverydate <= rp.date_value1 or c.staynr = 36)
   and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=cg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=rp.number_value1 and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=cg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=rp.number_value1))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=cg.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 c.staynr not in (20,22, 23, 24, 25)
  and (( rp.number_value2=1 and c.staynr <>'37') or rp.number_value2=0)
--  and rp.number_value=cg.customer_id
 and cg.customernumber >= rp.number_value8 and cg.customernumber <= rp.number_value9
  and rp.date_value1>= coalesce(b.planneddeliverydate, b.lastscandate)
  and ( f1.flag_id is null or h.flagstartdate > rp.date_value1)
  and ( f2.flag_id is null or f.flagstartdate > rp.date_value1)
  and not exists ( select 1 from vr_uniqueitemflag uf where  uf.uniqueitem_id = b.uniqueitem_id and uf.uniqueitemflagdate < rp.date_value1)
union
  SELECT
  rp.reportparam_id,  ds.businessunit_id, languages.code languagecode,
 GETABSMINDATE()planneddeliverydate , b.lastinscandate lastinscan_date,b.status_id, p.code productcode,
 (SELECT description FROM product_DESC w WHERE w.product_id = p.product_id
  AND w.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdesc,
 1 routenumber, '' routedesc,
  cg.customernumber, cg.name customername, ch.departmentnumber, ch.description departmentdesc,
  -1 day_id,
   ci.street customeraddres,b.primaryid idcode,  7, 0 flag_no,
    0 maxcompletenessdays,
  ds.internalprocessingbu_id internalBu
 FROM PRODUCT p, UNIQUEITEM  b,
  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 
  ,distributionpoint g,customer cg, department ch, address ci,    stay c,sizedefinition ck,languages, reportparam rp
 WHERE p.PRODUCT_ID = b.PRODUCT_ID
 and b.sizedefinition_id=ck.sizedefinition_id
 and  b.Stay_Id=c.stay_id and   c.staynr = 20
 and ds.uniqueitem_link_id = b.uniqueitem_id
 and  ds.outscannodelivery in(0,2)
 and ( NVL(ds.PACKINGNOTE_ID, 0) IN
             (SELECT 0 from DUAL   UNION ALL
              SELECT PN2.PACKINGNOTE_ID FROM PACKINGNOTE PN2
              WHERE PN2.ORIGIN = 2
                    AND PN2.STATUS_ID = 6
                    AND PN2.DISTRIBUTIONPOINT_ID = ds.DISTRIBUTIONPOINT_LINK_ID
                    AND PN2.DELIVERYPOINT_ID = ds.DELIVERYPOINT_LINK_ID
                    and PN2.DELIVERYDATE=rp.date_value1)
      )
 and (h.CUSTOMER_ID = cg.customer_id or h.customer_id is null)
 and ds.customer_link_id=cg.customer_id
 and cg.visitaddress_id = ci.address_id
 and ds.distributionpoint_link_id=g.distributionpoint_id
 and g.department_id=ch.department_id
 and ch.customer_id=cg.customer_id
 and (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=cg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
        and cdd.day_id=rp.number_value1 and rp.number_value4=0))
     or (( exists (select 1 from customerdeliveryday cdd where cdd.customer_id=cg.customer_id and cdd.deliverytype <> 'P' and cdd.typeofmerchadise = 2
                  and cdd.day_id=rp.number_value1))
        or (exists (select 1 from routecontrol rc, stop   where stop.customer_link_id=cg.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=cg.customer_id
 and cg.customernumber >= rp.number_value8 and cg.customernumber <= rp.number_value9
 and coalesce(ds.planneddeliverydate,rp.date_value1)<=rp.date_value1
 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
     )
;
/