[Oracle]實際出貨日(Actual Departure Date)

參考資料：http://somebabytina.pixnet.net/blog/post/23231167-%7B%E5%AD%B8%E7%BF%92%7D%E9%97%9C%E6%96%BC%3C%E5%AF%A6%E9%9A%9B%E5%87%BA%E8%B2%A8%E6%97%A5%3E 今天有個需求，需要由訂單抓到實際出貨日，原本要參考課長的客製Table，發現太複雜，開始問Google大神如何串接，終於被我查到 重點如下 當ULTIMATE SHIP TO DATE(ULTIMATE_DROPOFF_DATE )小於SHIP CONFIRM的ACTUAL DEPATURE DATE時，系統會保留原值 當ULTIMATE SHIP TO DATE(ULTIMATE_DROPOFF_DATE )大於SHIP CONFIRM的ACTUAL DEPARTURE DATE時，系統會將他UPDATE為實際出貨日 而且WSH_NEW_DELIVERIES.中的（INITIAL_PICKUP_DATE）一定會被更新成實際出貨日。 以下是檢查程式

SELECT WDD.SOURCE_CODE,
       WND.NAME,
       WDD.ITEM_DESCRIPTION,
       REQUESTED_QUANTITY,
       OOHA.ORDER_NUMBER,
       OOLA.LINE_NUMBER,
       OOLA.ACTUAL_SHIPMENT_DATE,
       WND.ULTIMATE_DROPOFF_DATE,
       WND.INITIAL_PICKUP_DATE
  FROM OE_ORDER_HEADERS_ALL     OOHA,
       OE_ORDER_LINES_ALL       OOLA,
       WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND
 WHERE 1 = 1
   AND WDD.SOURCE_CODE = 'OE'
   AND OOHA.ORG_ID = 2
   AND OOHA.HEADER_ID = OOLA.HEADER_ID
   AND WDD.SOURCE_LINE_ID = OOLA.LINE_ID
   AND WDD.SOURCE_HEADER_ID = OOHA.HEADER_ID
   AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
   AND WDA.DELIVERY_ID = WND.DELIVERY_ID
   AND OOLA.ACTUAL_SHIPMENT_DATE <> WND.INITIAL_PICKUP_DATE

  但是檢查程式發現公司竟然有不一致的狀況！＠＃！＠＄＠！，詢問J課才知道原來2012年前，當系統發生問題時，常常進行DataFix，造成系統問題，2013年開始才慢慢沒有問題，所以J課選擇由INV模組哪邊找出貨紀錄，下次研究看看 補充

SELECT DISTINCT 
       MMT.SOURCE_CODE,
       OOHA.ORDER_NUMBER,
       OOHA.ORDERED_DATE,
       MMT.TRANSACTION_DATE INV_TRAN_DATE, 
       OOLA.ACTUAL_SHIPMENT_DATE OM_SHIP_DATE,
       WND.INITIAL_PICKUP_DATE OM_PICK_DATE,
       MMT.TRANSACTION_QUANTITY,
       MMT.ORGANIZATION_ID,
       MMT.SUBINVENTORY_CODE,
       MMT.TRANSACTION_TYPE_ID,
       MMT.SHIPMENT_NUMBER
  FROM MTL_MATERIAL_TRANSACTIONS MMT,
       OE_ORDER_LINES_ALL        OOLA,
       OE_ORDER_HEADERS_ALL      OOHA,
       WSH_DELIVERY_DETAILS WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES WND   
 WHERE 1 = 1
   --AND MMT.TRX_SOURCE_LINE_ID = 1940546
   AND MMT.TRANSACTION_TYPE_ID IN (33)
   AND OOHA.HEADER_ID = OOLA.HEADER_ID
   AND MMT.TRX_SOURCE_LINE_ID = OOLA.LINE_ID
   AND OOLA.ACTUAL_SHIPMENT_DATE <> MMT.TRANSACTION_DATE
   
   AND WDD.SOURCE_HEADER_ID = OOHA.HEADER_ID
   AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
   AND WDA.DELIVERY_ID = WND.DELIVERY_ID
   ORDER BY OOHA.ORDERED_DATE DESC
/*
   SELECT MTT.TRANSACTION_TYPE_ID, MTT.TRANSACTION_TYPE_NAME, MTT.DESCRIPTION
  FROM MTL_TRANSACTION_TYPES MTT
 WHERE 1 = 1
   AND MTT.DISABLE_DATE IS NULL
   AND MTT.TRANSACTION_TYPE_ID IN (11,14,30,33,15,16)
 ORDER BY MTT.TRANSACTION_TYPE_ID
*/

INV模組過去會有分批出貨的問題，先紀錄一下