Thursday, March 27, 2008

Query Serial Number for an Order in Oracle

SELECT wl.meaning release_status
     , wdd.released_status
     , wdd.source_line_id
     , wdd.source_code
     , wser.fm_serial_number
     , wser.TO_SERIAL_NUMBER
     , wdd.CREATION_DATE
     , oelines.header_id
     , oeheader.sold_to_org_id
     , wdd.shipped_quantity    
     , wdd.delivery_detail_id
  FROM wsh_delivery_details wdd
     , apps.wsh_lookups wl
     , OE_ORDER_LINES_ALL oelines
     , oe_order_headers_all oeheader
     , wsh_serial_numbers wser
WHERE 1 = 1
   AND wl.lookup_type = 'PICK_STATUS'
   AND wl.lookup_code = wdd.released_status
   AND wdd.source_code = 'OE'
   AND wdd.source_line_id=oelines.line_id
   and oelines.header_id = oeheader.header_id
   AND WDD.DELIVERY_DETAIL_ID=wser.delivery_detail_id