CREATE OR REPLACE VIEW VR_COMPLETENESSCUSTOMERDETAILS AS
select 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,
 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,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
 ck.code sizedefinitioncode,
 (SELECT COUNT(*) FROM UNIQUEITEMFLAG Z, flag fl WHERE Z.UNIQUEITEM_ID = b.UNIQUEITEM_ID
   and z.flag_id = fl.flag_id
   and fl.includeincompletnesscheck = 'Y') FLAG,
 0 FLAG_NO,
 h.wearernumber, h.fullname wearername,
 cg.customernumber, cg.name customername, ch.departmentnumber, ch.description departmentdesc, -1 day_id,
 ci.street customeraddres
, cast(CONCATENATEWITHBLANK(ci.zipcode, ci.city) as varchar2(255)) customeraddres2,
  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,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , 1  NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
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
   left outer join finishingmethod j on (b.finishingmethod_id = j.finishingmethod_id)
   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 ce, customer cg, department ch, address ci, languages, sizedefinition ck
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 = 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.sizedefinition_id = ck.sizedefinition_id
 and (( (select count(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) > 0)
     or (( (select count(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) > 0)
        or ( (select /*+index(rc XIE7ROUTECONTROL)*/ count(1) from routecontrol rc, stop   where stop.customer_link_id=cg.customer_id and nvl(stop.routestop_id,0) <> 0
        and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1) > 0)))
 and c.staynr not in (20,22,23,24,25)
 and (b.planneddeliverydate <= rp.date_value1 or c.staynr = 36)
 and (( rp.number_value2=1 and c.staynr <>'37') or rp.number_value2=0)
-- and cg.customer_id=rp.number_value
 and cg.customernumber >= rp.number_value8 and cg.customernumber <= rp.number_value9
  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 ( select count(1) from vr_uniqueitemflag uf where  uf.uniqueitem_id = b.uniqueitem_id and uf.uniqueitemflagdate < rp.date_value1) = 0
union all
 SELECT 2 linetype,
  rp.reportparam_id,
  ds.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, 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,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = b.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
  ck.code sizedefinitioncode,
 (SELECT COUNT(*) FROM UNIQUEITEMFLAG Z, flag fl WHERE Z.UNIQUEITEM_ID = b.UNIQUEITEM_ID
   and z.flag_id = fl.flag_id
   and fl.includeincompletnesscheck = 'Y') FLAG,
 0 FLAG_NO,
 h.wearernumber, h.fullname wearername,
 cg.customernumber, cg.name customername, ch.departmentnumber, ch.description departmentdesc,
 - 1 day_id,
 ci.street customeraddres
, cast(CONCATENATEWITHBLANK(ci.zipcode, ci.city) as varchar2(255)) customeraddres2,
 0 MAXCOMPLETENESSDAYS,
 ds.internalprocessingbu_id internalBu,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 , 1 NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
 FROM PRODUCT p, 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,
   DELIVERYSCAN ds left outer join WEAREREMPLOYMENT f
                inner join  WEARER h on f.WEARER_ID = h.WEARER_ID
   on  ds.WEAREREMPLOYMENT_LINK_ID = f.WEAREREMPLOYMENT_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.scantimestamp = b.lastscandate
 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 coalesce(h.CUSTOMER_ID, cg.customer_id) = cg.customer_id
 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 (( (select count(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) > 0)
       or (( (select count(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) > 0)
         or ( (select /*+index(rc XIE7ROUTECONTROL)*/ count(1) from routecontrol rc, stop   where stop.customer_link_id=cg.customer_id and nvl(stop.routestop_id,0) <> 0
           and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1) > 0)))
-- 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
     )
union all
select 3 linetype, rp.reportparam_id, b.businessunit_id 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,
 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,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
 ck.code sizedefinitioncode,
 0 FLAG,
 0 FLAG_NO,
 -1 wearernumber, '' wearername,
 cg.customernumber, cg.name customername, ch.departmentnumber, ch.description departmentdesc, -1 day_id,
 ci.street customeraddres
, cast(CONCATENATEWITHBLANK(ci.zipcode, ci.city) as varchar2(255)) customeraddres2,
  0 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,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 ,dv.amount  NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from  deliveryvariations dv,  distributionpoint g,reportparam rp,
  product ce, customer cg, department ch, address ci, languages, sizedefinition ck,finishingmethod j, businessunit b
         left outer join systemsetting sst on sst.plant_id = b.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
where dv.distributionpoint_id=g.distributionpoint_id
 and g.department_id=ch.department_id
 and ch.customer_id=cg.customer_id
 and cg.visitaddress_id = ci.address_id
 and dv.product_id = ce.product_id
 and dv.sizedefinition_id = ck.sizedefinition_id
 and dv.finishingmethod_id=j.finishingmethod_id
-- and cg.customer_id=rp.number_value
 and cg.customernumber >= rp.number_value8 and cg.customernumber <= rp.number_value9
 and (dv.packingnote_id is null or (select count(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) > 0)
 and dv.origin  in (1, 5)
 and (select min(ca.processbusinessunit_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 )= b.businessunit_id
 and ((  (select count(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) > 0)
     or (( (select count(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) > 0)
        or ( (select /*+index(rc XIE7ROUTECONTROL)*/ count(1) from routecontrol rc, stop   where stop.customer_link_id=cg.customer_id and nvl(stop.routestop_id,0) <> 0
        and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1) > 0)))
 and dv.deliverydate <= rp.date_value1
 and ( (rp.number_value5 > 0 and rp.number_value5=dv.plantdepartment_id )
     or ( rp.number_value6 > 0 and rp.number_value6= ( select a.plant_id from plantdepartment a where a.plantdepartment_id=dv.plantdepartment_id ) )
     or ( rp.number_value7 > 0 and rp.number_value7 <> ( select a.plant_id from plantdepartment a where a.plantdepartment_id=dv.plantdepartment_id ) )
     or ( rp.number_value5 = -1 and rp.number_value6=-1 and rp.number_value7 = -1)--all pldep
     )
     and sst.value = '0' and dv.amount > 0
union all
 select 4 linetype, rp.reportparam_id, b.businessunit_id businessunit_id,
 7 stay, ''  idcode, 0 uniqueitem_id,
 languages.code languagecode,
cc.deliverydate planneddeliverydate, null lastinscan_date, cc.deliverydate lastscan_date,
to_char(cc.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastinscandate,
to_char(cc.deliverydate, 'MM/DD/YYYY HH:MI:SS AM') lastscandate,
 -1 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,
 (SELECT description FROM finishingmethod_DESC y WHERE y.finishingmethod_id = j.finishingmethod_id
  AND y.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) finishingmethoddesc,
 ck.code sizedefinitioncode,
 0 FLAG,
 0 FLAG_NO,
 -1 wearernumber, '' wearername,
 cg.customernumber, cg.name customername, ch.departmentnumber, ch.description departmentdesc, -1 day_id,
 ci.street customeraddres
, cast(CONCATENATEWITHBLANK(ci.zipcode, ci.city) as varchar2(255)) customeraddres2,
  0 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,
 cast(sst.value as Varchar2(1)) COMPCHECKNONUNIQUE
 ,(cc.quantity+cc.quantitycog)  NonUniqueQty
 , g.distributionpointnumber, g.description distributionpoint_desc
from  cleancountdelivery cc,  distributionpoint g,reportparam rp,
  product ce, customer cg, department ch, address ci, languages, sizedefinition ck,finishingmethod j, businessunit b
         left outer join systemsetting sst on sst.plant_id = b.plant_id and sst.name = 'COMPCHECKNONUNIQUE'
where cc.distributionpoint_id=g.distributionpoint_id
 and g.department_id=ch.department_id
 and ch.customer_id=cg.customer_id
 and cg.visitaddress_id = ci.address_id
 and cc.product_id = ce.product_id
 and cc.sizedefinition_id = ck.sizedefinition_id
 and cc.finishingmethod_id=j.finishingmethod_id
-- and cg.customer_id=rp.number_value
 and cg.customernumber >= rp.number_value8 and cg.customernumber <= rp.number_value9
 and (cc.packingnote_id is null or (select count(1) from packingnote pn, status stat where cc.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) > 0)
 and (select min(ca.processbusinessunit_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 )= b.businessunit_id
 and ((  (select count(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) > 0)
     or ((  (select count(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) > 0)
        or ( (select /*+index(rc XIE7ROUTECONTROL)*/ count(1) from routecontrol rc, stop   where stop.customer_link_id=cg.customer_id and nvl(stop.routestop_id,0) <> 0
        and stop.stoptype=3 and stop.routecontrol_id=rc.routecontrol_id and rc.drivingdate=rp.date_value1 and rp.number_value4=1) > 0)))
 and cc.deliverydate <= rp.date_value1
      and sst.value = '0'
;
/