Showing posts with label Query to check all the Picked Delivery Deatils where Reservation not exists. Show all posts
Showing posts with label Query to check all the Picked Delivery Deatils where Reservation not exists. Show all posts

Saturday, 16 April 2011

Query to check all the Picked Delivery Deatils where Reservation not exists

Query to check all the Picked Delivery Deatils where Reservation not exists

 Select ool.line_id,
 wdd.delivery_detail_id,wdd.source_line_id,wdd.ship_from_location_id,wdd.organi
 zation_id,wdd.src_requested_quantity,
  wdd.shipped_quantity,wdd.move_order_line_id,wdd.customer_item_id,wdd.split_fro
 m_delivery_detail_id,
   wdd.source_header_number,wdd.source_line_number,wdd.picked_quantity
  from wsh_delivery_details wdd , oe_order_lines_all ool
  where wdd.source_line_id  = ool.line_id
        and ool.open_flag ='Y'
        and wdd.source_code ='OE'
        and Picked_quantity > 0
        and released_status= 'Y'
        and ool.header_id = &enter_Sales_Order_Header_id
        and not exists
        (
        select 1 from mtl_reservations mr
         where mr.demand_source_line_id = wdd.source_line_id
            and mr.inventory_item_id = wdd.inventory_item_id) 

Above sample script is with Order_Header_id as a Parameter. If you want to check for all the Orders , then remove the parameter Condition.