Loading

Thursday, May 6, 2010

Query providing Order,Delivery and its Move Order information (R12)

Below query joins the Order, Delivery and Move order tables.

This query is tested in R12.1.1.

SELECT
   mhl.request_number
  ,l.line_number
  ,h.order_number
  ,mrl.line_number
  ,mrl.line_id
  ,mrl.from_subinventory_code
  ,mrl.lot_number
  ,mrl.serial_number_start
  ,mrl.serial_number_end
  ,mrl.uom_code
  ,mrl.quantity
  ,mrl.quantity_delivered
  ,mrl.quantity_detailed
  ,wdd.source_header_number
  ,wdd.source_header_id
  ,wdd.source_line_id
  ,wdd.shipping_instructions
  ,wdd.inventory_item_id
  ,wdd.requested_quantity_uom
  ,wdd.ship_method_code
  ,wdd.subinventory
  ,wdd.shipment_priority_code
  ,wdd.organization_id
  ,wdd.released_status
  ,wdd.source_code
  ,wnd.delivery_id
  ,wnd.name
  ,wnd.initial_pickup_location_id
  ,wnd.creation_date
  ,msib.segment1
  ,msib.description
  ,msib.revision_qty_control_code
FROM
   mtl_txn_request_lines mrl
  ,mtl_txn_request_headers mhl
  ,wsh_delivery_details wdd
  ,wsh_new_deliveries wnd
  ,wsh_delivery_assignments wda
  ,oe_order_lines_all l
  ,oe_order_headers_all h
  ,mtl_system_items_b msib
WHERE
    mhl.header_id          =mrl.header_id
AND mrl.line_id            =wdd.move_order_line_id
AND wda.delivery_id        =wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id   =l.header_id
AND wdd.source_line_id     =l.line_id
AND h.header_id            =l.header_id
AND wdd.organization_id    =msib.organization_id(+)
AND wdd.inventory_item_id  =msib.inventory_item_id(+)
AND wda.delivery_id        ='&delivery_id' ;

Quote for the Post:
"If you don’t have time to do it right, 
when will you have time to do it over?"
 
- John Wooden 

0 Responses to “Query providing Order,Delivery and its Move Order information (R12)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.