create or replace view v_garmentsreadytobesorted as
select /*+ index(ur XIF4UNIQUEITEMROUTE) index(u XPKUNIQUEITEM)
          index(un XPKUNIQUEITEMNONPOOL)
          index(we XPKWEAREREMPLOYMENT)
          index(dp XPKDISTRIBUTIONPOINT) index(w XPKWEARER)
          index(t XPKDEPARTMENT) index(delp XPKDELIVERYPOINT)
       */
 pt.code plantcode, ur.plannedoutdate routedate, r.routenumber, r.drivingday_id daynumber,
 u.primaryid idcode, d.code depotcode,
 nvl((select rs.stopnumber   -- HVH To do 8178 make stop# 999999 in off week for EOW customers
   from   routestop rs
   where  rs.route_id = r.route_id
   and    rs.routestop_id =
          pack_sort.getroutestopforcustomer(r.plant_id, ur.plannedoutdate, w.customer_id,
                                             delp.deliverypoint_id)
   and rs.dateactive <=  ur.plannedoutdate --order 502746
   and ur.plannedoutdate  < rs.dateinactive
 ),999999) stopnumber,
 c.customernumber,
 w.wearernumber,
 p.code productcode,
 (pack_sort.buildsortstring(p.code, s.code, c.customernumber, c.customer_id, w.wearernumber,
                             w.fullname,
                             (select lpad(nvl(lo.bank, ' '), 6, ' ') ||
                                       lpad(nvl(lo.locker, ' '), 6, ' ')
                               from   locker lo
                               where  lo.locker_id = we.locker_id), t.departmentnumber,
                             we.sequencenumber, w.sex,
                             (select rpad(nvl(g.code, ' '), 6, ' ')
                               from   garmentdispenser g
                               where  g.garmentdispenser_id = we.garmentdispenser_id), delp.code,
                             nvl((select rs.stopnumber
                               from   routestop rs
                               where  rs.route_id = r.route_id
                               and    rs.routestop_id =
                                      pack_sort.getroutestopforcustomer(r.plant_id, ur.plannedoutdate,
                                                                         w.customer_id,
                                                                         delp.deliverypoint_id)),999999),
                             r.plant_id, u.customerowned, 0)) sortstring,
 (pack_sort.buildstackchangeinfo(p.code, s.code, c.customernumber, c.customer_id, w.wearernumber,
                                  w.fullname,
                                  (select lpad(nvl(lo.bank, ' '), 6, ' ') ||
                                            lpad(nvl(lo.locker, ' '), 6, ' ')
                                    from   locker lo
                                    where  lo.locker_id = we.locker_id), t.departmentnumber,
                                  we.sequencenumber, w.sex,
                                  (select rpad(nvl(g.code, ' '), 6, ' ')
                                    from   garmentdispenser g
                                    where  g.garmentdispenser_id = we.garmentdispenser_id), delp.code,
                                  nvl((select rs.stopnumber
                                    from   routestop rs
                                    where  rs.route_id = r.route_id
                                    and    rs.routestop_id =
                                           pack_sort.getroutestopforcustomer(r.plant_id,
                                                                              ur.plannedoutdate,
                                                                              w.customer_id,
                                                                              delp.deliverypoint_id)),999999),
                                  r.plant_id, u.customerowned, 0)) stackchangestring,
 (select sf.sortaddress
   from   sortaddressperfinishingmeth sf
   where  sf.finishingmethod_id = u.finishingmethod_id
   and    sf.plant_id = r.plant_id) destination,
   fgetuniqueitemflags(u.uniqueitem_id,r.plant_id,we.wearer_id,un.weareremployment_id) flags
from   route r, uniqueitemroute ur, uniqueitem u, uniqueitemnonpool un, weareremployment we,
       distributionpoint dp, wearer w, deliverypoint delp, customer c, department t, depot d,
       plant pt, product p, sizedefinition s
where  ur.plannedoutroute_id = r.route_id
and    u.uniqueitem_id = ur.uniqueitem_id
and    un.uniqueitem_id = u.uniqueitem_id
and    un.weareremployment_id = we.weareremployment_id
and    we.distributionpoint_id = dp.distributionpoint_id
and    we.wearer_id = w.wearer_id
and    w.customer_id = c.customer_id
and    dp.department_id = t.department_id
and   delp.deliverypoint_id=coalesce(we.deliverypoint_id,(select l.deliverypoint_id from locker l where we.locker_id=l.locker_id),
(select gd.deliverypoint_id from garmentdispenser gd where we.garmentdispenser_id=gd.garmentdispenser_id))
and    t.customer_id = w.customer_id
and    t.customer_id = delp.customer_id
and    r.depot_id = d.depot_id(+)
and    r.plant_id = pt.plant_id
and    u.product_id = p.product_id
and    u.sizedefinition_id = s.sizedefinition_id
and    exists (select st.status_id
        from   status st
        where  st.status_id = u.status_id
        and    st.statustype = 'UI'
        and    st.code = '20')
and    exists (select sy.stay_id
        from   stay sy
        where  sy.stay_id = u.stay_id
        and    sy.staytype = 1
        and    sy.staynr in (30,33))  -- HVH Todo 8177 Include checkpoint scan Nr 33;
/
