Thursday, 13 June 2013

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)

No comments:

Post a Comment