Thursday, November 7, 2013

Unprocessed Shipping Transactions Query

Your Ad Here



SELECT 'Unprocessed Shipping Transactions'||'~'||
      o.organization_code ||'~'||
      to_char(wdd.last_update_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
      decode(trunc(sysdate)-trunc(wdd.last_update_date),0,'',1,'',2,'','Escalate')||'~'||
      wdd.source_header_id ||'~'||
      ooh.order_number||'~'||
      wnd.delivery_id||'~'||
      wnd.name ||'~'||
      wdd.delivery_detail_id ||'~'||
      wdl.pick_up_stop_id ||'~'||
      wdd.inv_interfaced_flag||'~'||
      wts.actual_departure_date||'~'||
      ool.ship_from_org_id l_out
FROM wsh_delivery_details wdd,
     wsh_delivery_assignments wda,
     wsh_new_deliveries wnd,
     wsh_delivery_legs wdl,
     wsh_trip_stops wts,
     oe_order_headers_all ooh,
     oe_order_lines_all ool,
     org_organization_definitions o
WHERE wdd.source_code = 'OE'
  AND wdd.released_status = 'C'
  AND o.organization_id = ool.ship_from_org_id
  AND (wdd.inv_interfaced_flag IN ('N' ,'P')
      OR wdd.oe_interfaced_flag IN ('N' ,'P'))
  AND wdd.organization_id = nvl(p_org_id,wdd.organization_id)
  AND wda.delivery_detail_id = wdd.delivery_detail_id
  AND wnd.delivery_id = wda.delivery_id
  AND wnd.status_code IN ('CL','IT')
  AND wdl.delivery_id = wnd.delivery_id
  AND wdl.pick_up_stop_id = wts.stop_id
  AND wdd.source_header_id = ooh.header_id
  AND wdd.source_line_id = ool.line_id
  AND wdd.inv_interfaced_flag != 'X'
  and (:p_minutes is null or wdd.last_update_date < SYSDATE - (:p_minutes/(24*60)) )
  order by o.organization_code, wdd.last_update_date;

Your Ad Here

1 comment: