[PO.Receipt.ISSUE] PO 已收料入庫 Supply Demand 仍掛有數量

PO 已收料入庫 Supply Demand 仍掛有數量

已結案. 所以只記錄處理過程SQL.

[說明]
在Supply / Demand中產生了一筆 "Shipment"  
(未完...)

[檢查PO/RCV資料]
-- PO HEADER
SELECT DECODE(H.SEGMENT1,'900205095','>>') A, H.PO_HEADER_ID HID
     , H.SEGMENT1 PO , SUBSTR(H.COMMENTS,1,20) COMMENTS , H.AUTHORIZATION_STATUS , H.CLOSED_DATE , H.CLOSED_CODE
     ,'||',  H.*
FROM PO_HEADERS_ALL H WHERE H.SEGMENT1 BETWEEN '900205093' AND '900205098'

-- PO LINES
SELECT TO_CHAR(H.CLOSED_DATE,'YYYY/MM/DD') D
     , H.PO_HEADER_ID HID , DECODE(H.SEGMENT1,'900205095','>>') A
     , H.SEGMENT1 PO
     , L.PO_LINE_ID LID, L.LINE_NUM , L.ITEM_ID, L.QUANTITY, L.ITEM_DESCRIPTION
FROM PO_HEADERS_ALL H , PO_LINES_ALL L
WHERE H.PO_HEADER_ID = L.PO_HEADER_ID
  AND H.SEGMENT1 BETWEEN '900205093' AND '900205098'

-- PO LINE LOCATION * 
SELECT TO_CHAR(H.CLOSED_DATE,'YYYY/MM/DD') D
     , H.PO_HEADER_ID HID , DECODE(H.SEGMENT1,'900205095','>>') A
     , H.SEGMENT1 PO
     , L.PO_LINE_ID LID, L.LINE_NUM , L.ITEM_ID, L.QUANTITY QTY
     , DECODE(H.SEGMENT1,'900205095','>>') A    
     , C.LINE_LOCATION_ID
     , C.QUANTITY
     , C.SHIPMENT_NUM , C.QUANTITY_SHIPPED
     , C.QUANTITY_RECEIVED , C.QUANTITY_ACCEPTED , C.QUANTITY_BILLED
     , C.SHIP_TO_LOCATION_ID , C.LAST_ACCEPT_DATE , C.ENCUMBERED_FLAG , C.PRICE_DISCOUNT
     , C.CANCEL_FLAG , C.FIRM_STATUS_LOOKUP_CODE
FROM PO_HEADERS_ALL H , PO_LINES_ALL L , PO_LINE_LOCATIONS_ALL C
WHERE H.PO_HEADER_ID = L.PO_HEADER_ID
  AND L.PO_LINE_ID = C.PO_LINE_ID
  AND H.SEGMENT1 BETWEEN '900205094' AND '900205098'

-- PO DISTRIBUTION
SELECT TO_CHAR(H.CLOSED_DATE,'YYYY/MM/DD') D
     , H.SEGMENT1 PO , H.PO_HEADER_ID HID , DECODE(H.SEGMENT1,'900205095','>>') A
     , L.PO_LINE_ID LID , L.LINE_NUM LNO , L.ITEM_ID, L.QUANTITY QTY        
     , C.LINE_LOCATION_ID LOCA_ID
     , D.PO_DISTRIBUTION_ID
     , DECODE(H.SEGMENT1,'900205095','>>') A
     , C.QUANTITY
     , C.SHIPMENT_NUM SHIP_NUM , C.QUANTITY_SHIPPED QTY_SHIP
     , C.QUANTITY_RECEIVED , C.QUANTITY_ACCEPTED , C.QUANTITY_BILLED      
     , C.SHIP_TO_LOCATION_ID , C.LAST_ACCEPT_DATE , C.ENCUMBERED_FLAG , C.PRICE_DISCOUNT
     , C.CANCEL_FLAG , C.FIRM_STATUS_LOOKUP_CODE
FROM PO_HEADERS_ALL H , PO_LINES_ALL L , PO_LINE_LOCATIONS_ALL C , PO_DISTRIBUTIONS_ALL D
WHERE H.PO_HEADER_ID = L.PO_HEADER_ID
  AND L.PO_LINE_ID = C.PO_LINE_ID
  AND C.LINE_LOCATION_ID = D.LINE_LOCATION_ID
  AND H.SEGMENT1 BETWEEN '900205094' AND '900205098'

--RC
SELECT RT.TRANSACTION_ID
     , RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID
     , RT.QUANTITY_BILLED
     , RT.TRANSACTION_TYPE, RT.QUANTITY
     , RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID
     , RT.DESTINATION_TYPE_CODE, RT.PRIMARY_QUANTITY
     , RT.UOM_CODE, RT.EMPLOYEE_ID, RT.PARENT_TRANSACTION_ID
FROM RCV_TRANSACTIONS RT WHERE RT.PO_HEADER_ID = 446215

--SHIPMENT HEADER
SELECT RT.TRANSACTION_ID
     , RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID
     , RT.QUANTITY
     , RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID
     , RH.SHIPMENT_HEADER_ID, RH.SHIPMENT_NUM
     , RH.*

FROM RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RH
WHERE RT.PO_HEADER_ID = 446215  AND RT.TRANSACTION_TYPE = 'DELIVER'
  AND RT.SHIPMENT_HEADER_ID = RH.SHIPMENT_HEADER_ID


-- SHIPMENT LINE * //FULLY RECEIVED / PARTIALLY RECEIVED
SELECT RT.TRANSACTION_ID
     , RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID
     , RT.QUANTITY
     , RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID
     , RH.SHIPMENT_HEADER_ID, RH.SHIPMENT_NUM
     , RL.SHIPMENT_LINE_ID, RL.QUANTITY_SHIPPED, RL.QUANTITY_RECEIVED, RL.SHIPMENT_LINE_STATUS_CODE
     , RL.*
FROM RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RH , RCV_SHIPMENT_LINES RL
WHERE RT.PO_HEADER_ID = 446215  AND RT.TRANSACTION_TYPE = 'DELIVER'
  AND RT.SHIPMENT_HEADER_ID = RH.SHIPMENT_HEADER_ID
  AND RT.SHIPMENT_LINE_ID = RL.SHIPMENT_LINE_ID
ORDER BY RH.SHIPMENT_HEADER_ID, RL.LINE_NUM

[檢查Suuply/Demand資料]
/*
Table  MTL_SUPPLY
Table  MTL_SUPPLY_DEMAND_TEMP
Table  MTL_USER_SUPPLY  >> 空白的
*/
SELECT * FROM MTL_SYSTEM_ITEMS_B MSI WHERE MSI.SEGMENT1 = '1440P45000'

SELECT * FROM MTL_SUPPLY_DEMAND_TEMP T WHERE T.INVENTORY_ITEM_ID = 295537

SELECT * FROM MTL_SUPPLY S WHERE S.ITEM_ID = 295537
FOR UPDATE

MTL_SUPPLY.SUPPLY_TYPE_CODE = 'SHIPMENT'
MTL_SUPPLY.SUPPLY_SOURCE_ID = 849219 >> SHIPMENT LINE ID
REQ_HEADER_ID, PO_DISTRIBUTION_ID, SHIPMENT_LINE_ID or RCV_TRANSACTION_ID depending on SUPPLY_TYPE_CODE

SELECT S.PO_HEADER_ID, S.*
FROM MTL_SUPPLY S
   , PO_HEADERS_ALL H
WHERE H.PO_HEADER_ID = S.PO_HEADER_ID
  AND H.SEGMENT1 IN ('900205095','900209476')

--CREATE一個存放原資料TABLE
CREATE TABLE MTL_SUPPLY_T130819 AS SELECT * FROM MTL_SUPPLY S WHERE S.ITEM_ID = 295537
SELECT * FROM MTL_SUPPLY_T130819

[Data Fix (解)]
--Data FIX ------------------------------------------------------------------------------------------------
-- SHIPMENT LINE *
-- SHIPMENT LINE * //FULLY RECEIVED / PARTIALLY RECEIVED
SELECT RT.TRANSACTION_ID
     , RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID
     , RT.QUANTITY
     , RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID
     , RH.SHIPMENT_HEADER_ID, RH.SHIPMENT_NUM
     , RL.SHIPMENT_LINE_ID, RL.QUANTITY_SHIPPED, RL.QUANTITY_RECEIVED, RL.SHIPMENT_LINE_STATUS_CODE
FROM RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RH , RCV_SHIPMENT_LINES RL
WHERE RT.PO_HEADER_ID = 446215  AND RT.TRANSACTION_TYPE = 'DELIVER'
  AND RT.SHIPMENT_HEADER_ID = RH.SHIPMENT_HEADER_ID
  AND RT.SHIPMENT_LINE_ID = RL.SHIPMENT_LINE_ID
ORDER BY RH.SHIPMENT_HEADER_ID, RL.LINE_NUM

--========================================================================
-- Data Fix                                                                  
========================================================================
--(1) RCV_SHIPMENT_LINES (SHIPMENT_LINE_ID = 849219)                          
--(1.1) QUANTITY_SHIPPED : 6 >> 4
--(1.2) SHIPMENT_LINE_STATUS_CODE : PARTIALLY RECEIVED >> FULLY RECEIVED
SELECT RL.SHIPMENT_LINE_ID, RL.QUANTITY_SHIPPED, RL.SHIPMENT_LINE_STATUS_CODE
FROM  RCV_SHIPMENT_LINES RL WHERE RL.SHIPMENT_LINE_ID = 849219
--FIX =============================
UPDATE RCV_SHIPMENT_LINES RL
   SET RL.QUANTITY_SHIPPED = 4
     , RL.SHIPMENT_LINE_STATUS_CODE = 'FULLY RECEIVED'
WHERE RL.SHIPMENT_LINE_ID = 849219  
--FIX =============================

--rollback
UPDATE RCV_SHIPMENT_LINES RL
   SET RL.QUANTITY_SHIPPED = 6
     , RL.SHIPMENT_LINE_STATUS_CODE = 'PARTIALLY RECEIVED'
WHERE RL.SHIPMENT_LINE_ID = 849219

--(2) PO_LINE_LOCATIONS_ALL (C.LINE_LOCATION_ID IN (820869, 825702))  
--(2.1) LINE_LOCATION_ID = 820869
--      QUANTITY_SHIPPED 2 >> 0  
--      ENCUMBERED_FLAG = N >> NULL
--(2.2) LINE_LOCATION_ID = 825702
--      ENCUMBERED_FLAG = N >> NULL
SELECT C.LINE_LOCATION_ID, C.QUANTITY_SHIPPED , C.ENCUMBERED_FLAG
FROM PO_LINE_LOCATIONS_ALL C
WHERE C.LINE_LOCATION_ID IN (820869, 825702)

--FIX =============================
UPDATE PO_LINE_LOCATIONS_ALL C
   SET C.QUANTITY_SHIPPED = 0
     , C.ENCUMBERED_FLAG = null
 WHERE C.LINE_LOCATION_ID = 820869

UPDATE PO_LINE_LOCATIONS_ALL C
   SET C.ENCUMBERED_FLAG = null
 WHERE C.LINE_LOCATION_ID = 825702
--FIX =============================

--roll back
UPDATE PO_LINE_LOCATIONS_ALL C
   SET C.QUANTITY_SHIPPED = 2
     , C.ENCUMBERED_FLAG = 'N'
 WHERE C.LINE_LOCATION_ID = 820869

UPDATE PO_LINE_LOCATIONS_ALL C
   SET C.ENCUMBERED_FLAG = 'N'
 WHERE C.LINE_LOCATION_ID = 825702

--FIX =============================
--(3) MTL_SUPPLY
--CREATE一個存放原資料TABLE
CREATE TABLE MTL_SUPPLY_T130819 AS SELECT * FROM MTL_SUPPLY S WHERE S.ITEM_ID = 295537
SELECT * FROM MTL_SUPPLY_T130819

SELECT * FROM MTL_SUPPLY S WHERE S.ITEM_ID = 295537 AND S.SUPPLY_SOURCE_ID = 849219
--FIX =============================
DELETE FROM MTL_SUPPLY S WHERE S.ITEM_ID = 295537 AND S.SUPPLY_SOURCE_ID = 849219
--FIX =============================
--Data FIX ------------------------------------------------------------------------------------------------