OM模块数据流

 

--订单录入
SELECT * FROM OE_ORDER_HEADERS_ALL A WHERE A.ORDER_NUMBER = '863060875';
SELECT * FROM OE_ORDER_LINES_ALL B WHERE B.HEADER_ID = 2720129;
SELECT * FROM MTL_SALES_ORDERS C WHERE C.SEGMENT1 = '863060875';

SELECT *
  FROM OE_ORDER_HEADERS_ALL OOH
      ,OE_ORDER_LINES_ALL   OOL
      ,MTL_SALES_ORDERS     MSO
 WHERE OOH.HEADER_ID = OOL.HEADER_ID
   AND OOH.ORDER_NUMBER = MSO.SEGMENT1
   AND OOH.ORDER_NUMBER = '863060875';

--订单登记
SELECT * FROM WSH_DELIVERY_DETAILS E WHERE E.SOURCE_HEADER_ID = 2720129;

SELECT *
  FROM WSH_DELIVERY_ASSIGNMENTS F
 WHERE F.DELIVERY_DETAIL_ID IN
       (SELECT DELIVERY_DETAIL_ID
          FROM WSH_DELIVERY_DETAILS E
         WHERE E.SOURCE_HEADER_ID = 2720129);

SELECT WDA.PARENT_DELIVERY_DETAIL_ID
      ,WDA.DELIVERY_ASSIGNMENT_ID
      ,WDA.DELIVERY_ID
      ,WDA.DELIVERY_DETAIL_ID
      ,WDD.*
  FROM WSH_DELIVERY_DETAILS     WDD
      ,WSH_DELIVERY_ASSIGNMENTS WDA
 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
   AND WDD.SOURCE_HEADER_ID IN
       (SELECT HEADER_ID
          FROM OE_ORDER_HEADERS_ALL A
         WHERE A.ORDER_NUMBER = '863060875');
--订单挑库
SELECT *
  FROM WSH_NEW_DELIVERIES
 WHERE DELIVERY_ID IN
       (SELECT DELIVERY_ID
          FROM WSH_DELIVERY_ASSIGNMENTS F
         WHERE F.DELIVERY_DETAIL_ID IN
               (SELECT DELIVERY_DETAIL_ID
                  FROM WSH_DELIVERY_DETAILS E
                 WHERE E.SOURCE_HEADER_ID = 2720129));
--调库事务处理
SELECT MMT.TRANSACTION_ID          MMT_TRANSACTION_ID
      ,MMT.TRANSACTION_SOURCE_ID   MMT_TRANSACTION_SOURCE_ID
      ,MMT.TRX_SOURCE_LINE_ID      MMT_TRX_SOURCE_LINE_ID
      ,MMT.TRANSFER_TRANSACTION_ID MMT_TRANSFER_TRANSACTION_ID
      ,MMT.TRANSACTION_SET_ID      MMT_TRANSACTION_SET_ID
      ,MMT.SOURCE_LINE_ID          MMT_STORUCE_LINE_ID
      ,MMT.MOVE_TRANSACTION_ID     MMT_MOVE_TRANSACTION_ID
      ,MMT.MOVEMENT_ID             MMT_MOVEMENT_ID
      ,MMT.MOVE_ORDER_LINE_ID      MMT_MOVE_ORDER_LINE_ID
      ,MMT.PICKING_LINE_ID
      ,WDD.*
  FROM MTL_MATERIAL_TRANSACTIONS MMT
      ,WSH_DELIVERY_DETAILS      WDD
 WHERE MMT.TRANSACTION_ID = WDD.TRANSACTION_ID
   AND WDD.SOURCE_HEADER_ID = 2720129
   AND MMT.TRANSACTION_TYPE_ID = 52; --挑库（sales order pick）＝发放订单即处理物料A仓到B仓相当于子库转移，同时将转移到B仓的物料做保留（MTL_RESERVATIONS）
--发运确认
SELECT MMT.TRANSACTION_ID          MMT_TRANSACTION_ID
      ,MMT.TRANSACTION_SOURCE_ID   MMT_TRANSACTION_SOURCE_ID
      ,MMT.TRX_SOURCE_LINE_ID      MMT_TRX_SOURCE_LINE_ID
      ,MMT.TRANSFER_TRANSACTION_ID MMT_TRANSFER_TRANSACTION_ID
      ,MMT.TRANSACTION_SET_ID      MMT_TRANSACTION_SET_ID
      ,MMT.SOURCE_LINE_ID          MMT_STORUCE_LINE_ID
      ,MMT.MOVE_TRANSACTION_ID     MMT_MOVE_TRANSACTION_ID
      ,MMT.MOVEMENT_ID             MMT_MOVEMENT_ID
      ,MMT.MOVE_ORDER_LINE_ID      MMT_MOVE_ORDER_LINE_ID
      ,MMT.TRANSACTION_REFERENCE   MMT_TRANSFER_REFERENCE
      ,WDD.SOURCE_HEADER_ID
      ,WDD.DELIVERY_DETAIL_ID
      ,MMT.PICKING_LINE_ID         MMT_PICKING_LINE_ID
      ,WDD.*
  FROM MTL_MATERIAL_TRANSACTIONS MMT
      ,WSH_DELIVERY_DETAILS      WDD
 WHERE MMT.PICKING_LINE_ID = WDD.DELIVERY_DETAIL_ID
   AND WDD.SOURCE_HEADER_ID = 2720129
   AND MMT.TRANSACTION_TYPE_ID = 33; --发放(sales order issue)＝发运确认，从B仓发出物料

 

SELECT *
  FROM OE_ORDER_HEADERS_ALL     OOH
      ,OE_ORDER_LINES_ALL       OOL
      ,WSH_DELIVERY_DETAILS     WDD
      ,WSH_DELIVERY_ASSIGNMENTS WDA
      ,WSH_NEW_DELIVERIES       WND
 WHERE OOH.ORDER_NUMBER = '8003141739'
   AND OOH.HEADER_ID = OOL.HEADER_ID
   AND OOL.LINE_ID = WDD.SOURCE_LINE_ID
   AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
   AND WDA.DELIVERY_ID = WND.DELIVERY_ID(+);