Order Management Useful Scripts

Listed some useful queries scripts for Oracle Order Management Flow.

(For Order Management Detailed Flow, You may check my previous post: Oracle EBS订单的流程(Order->AR): http://blog.csdn.net/pan_tian/article/details/7693447 )

Sales Order Info

Order Header

SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = '65020';

Order Lines

SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020');

Inventory

MTL_SALES_ORDERS

SELECT S.SALES_ORDER_ID,S.SEGMENT1, S.SEGMENT2,S.SEGMENT3,H.ORDER_NUMBER,H.FLOW_STATUS_CODE,H.SOLD_TO_ORG_ID,H.OPEN_FLAG
FROM MTL_SALES_ORDERS S,OE_ORDER_HEADERS_ALL H
WHERE S.SEGMENT1 = TO_CHAR(H.ORDER_NUMBER)
AND  H.ORDER_NUMBER = '65020'

(MTL_SALES_ORDERS exists for the purpose of mapping sales orders between other applications and Inventory,Once Sales Order get booked, There will be record go into mtl_sales_orders.refer:link)

Reservations

SELECT MR.* FROM MTL_RESERVATIONS MR,OE_ORDER_LINES_ALL OOLA
WHERE MR.DEMAND_SOURCE_LINE_ID = OOLA.LINE_ID
AND OOLA.LINE_ID = &ORDER_LINE_ID;
(Each record is a reservation that ties an item/organization combination with a demand source and a supply source.NOTE:MR.DEMAND_SOURCE_HEADER_ID point to Mtl_Sales_Orders.Sales_Order_ID, NOT OE_ORDER_HEADERS_ALL.HEADER_ID)

Allocation

SELECT * FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRX_SOURCE_LINE_ID IN
      (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
       WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020'));

Move Order

SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID IN
       (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
       WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER