/*
庫齡表重新開發
部分交易型態需歸類到最後區間，非所有都先進先出
*/
DECLARE
P_ORG_ID NUMBER := 137;
P_DATE VARCHAR2(10) := '2023-03-31';
--P_ITEM VARCHAR2(100) := '4G1R-CHECK-VALVE';
P_COUNT NUMBER;
--取得庫存、料號相關資訊
CURSOR ON_HAND_INFO IS
SELECT MMT.ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
--MMT.SUBINVENTORY_CODE,
SUM(MMT.PRIMARY_QUANTITY) ON_HAND_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_SECONDARY_INVENTORIES SUBINV
WHERE 1 = 1
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.SUBINVENTORY_CODE = SUBINV.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = SUBINV.ORGANIZATION_ID
--AND MSI.SEGMENT1 = P_ITEM
AND MMT.ORGANIZATION_ID = P_ORG_ID
AND TRUNC(MMT.TRANSACTION_DATE) <= TO_DATE(P_DATE, 'YYYY-MM-DD') --庫存計算截止時間
AND SUBINV.ASSET_INVENTORY <> 2 --排除費用倉
AND MSI.INVENTORY_ITEM_ID NOT IN (201795, 201796, 201797, 14137273) --原程式排除料號 CHARGE、DEPOSIT、REPAIR、SERVICE FEE
GROUP BY MMT.ORGANIZATION_ID, MMT.INVENTORY_ITEM_ID, MSI.SEGMENT1 --,MMT.SUBINVENTORY_CODE
HAVING SUM(MMT.PRIMARY_QUANTITY) <> 0;
--取得交易資料
CURSOR MMT(P_ORGANIZATION_ID NUMBER, P_INVENTORY_ITEM_ID NUMBER) IS
SELECT MMT.TRANSACTION_ID,
MMT.TRANSACTION_TYPE_ID,
MMT.ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID,
MMT.PRIMARY_QUANTITY TRANSACTION_QTY,
MMT.TRANSACTION_DATE ORIGINAL_TRANSACTION_DATE,
--決定交易是否再最後區間
CASE
WHEN MMT.TRANSACTION_TYPE_ID IN (18, 44, 1350) THEN --抓取原本MMT時間
CASE
WHEN MMT.TRANSACTION_TYPE_ID = 1350 AND
MMT.ATTRIBUTE10 IS NOT NULL THEN
TO_DATE(MMT.ATTRIBUTE10, 'YYYY-MM-DD')
ELSE
TRUNC(MMT.TRANSACTION_DATE)
END
ELSE
DATE '1900-01-01'
END NEW_TRANSACTION_DATE
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE 1 = 1
AND MMT.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRUNC(MMT.TRANSACTION_DATE) <= TO_DATE(P_DATE, 'YYYY-MM-DD') --交易截止時間
AND MMT.PRIMARY_QUANTITY > 0 --應該只看增加的交易
AND MMT.TRANSACTION_TYPE_ID NOT IN (10008, 52) --排除 10008 銷貨成本認列 52 Salse Order Pick
AND MMT.TRANSACTION_TYPE_ID NOT IN
(15, 42, 43, 1326, 1328, 1336, 5536) --測試發現排除
--AND MMT.TRANSACTION_TYPE_ID NOT IN (15, 42, 43, 1328, 1336, 5536) --20230425 測試
AND MMT.TRANSACTION_ACTION_ID NOT IN (2, 24, 30) --原本程式就排除 2 倉庫移轉 24 成本更新、30 工單報廢
ORDER BY MMT.TRANSACTION_DATE DESC;
P_ON_HAND_QTY NUMBER;
P_TRANSACTION_QTY NUMBER;
P_LAST_QUANTITY NUMBER;
P_MMT_ON_HAND NUMBER;
BEGIN
DELETE TEMP_TABLE;
P_ON_HAND_QTY := 0;
P_TRANSACTION_QTY := 0;
FOR C1 IN ON_HAND_INFO LOOP
P_ON_HAND_QTY := C1.ON_HAND_QTY;
FOR C2 IN MMT(C1.ORGANIZATION_ID, C1.INVENTORY_ITEM_ID) LOOP
--假設庫存量 <= 交易量，直接取交易時間，可能第一筆直接滿足或某一筆才滿足
IF 0 < P_ON_HAND_QTY AND P_ON_HAND_QTY <= C2.TRANSACTION_QTY THEN
INSERT INTO TEMP_TABLE
(TRANSACTION_ID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ON_HAND_QTY,
TRANSACTION_QTY,
ORIGINAL_TRANSACTION_DATE,
NEW_TRANSACTION_DATE,
CHECK_FLAG)
VALUES
(C2.TRANSACTION_ID,
C2.TRANSACTION_TYPE_ID,
C2.ORGANIZATION_ID,
C2.INVENTORY_ITEM_ID,
C1.ON_HAND_QTY,
C2.TRANSACTION_QTY,
C2.ORIGINAL_TRANSACTION_DATE,
C2.NEW_TRANSACTION_DATE,
NULL);
P_ON_HAND_QTY := P_ON_HAND_QTY - C2.TRANSACTION_QTY; --減少庫存
--可能P_ON_HAND_QTY會被減到變成負數
IF P_ON_HAND_QTY < 0 THEN
UPDATE TEMP_TABLE
SET CHECK_FLAG = 'ERROR'
WHERE TRANSACTION_ID = C2.TRANSACTION_ID;
END IF;
END IF;
--假設庫存量 > 交易量，要判斷
IF P_ON_HAND_QTY > C2.TRANSACTION_QTY THEN
--先把當下交易寫入
INSERT INTO TEMP_TABLE
(TRANSACTION_ID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ON_HAND_QTY,
TRANSACTION_QTY,
ORIGINAL_TRANSACTION_DATE,
NEW_TRANSACTION_DATE,
CHECK_FLAG)
VALUES
(C2.TRANSACTION_ID,
C2.TRANSACTION_TYPE_ID,
C2.ORGANIZATION_ID,
C2.INVENTORY_ITEM_ID,
C1.ON_HAND_QTY,
C2.TRANSACTION_QTY,
C2.ORIGINAL_TRANSACTION_DATE,
C2.NEW_TRANSACTION_DATE,
NULL);
P_ON_HAND_QTY := P_ON_HAND_QTY - C2.TRANSACTION_QTY; --減少庫存
END IF;
END LOOP;
END LOOP;
--特殊處理_因為排除部分交易造成
--為了程式好閱讀另外處理，並讓以後好除錯
--有庫存但是沒有交易資料可抓取
--原程式都被歸到最後一類，202303檢查都是"05B型號調整(+)"造成，代表料號PC8-02
P_COUNT := 0;
FOR C1 IN ON_HAND_INFO LOOP
SELECT COUNT(*)
INTO P_COUNT
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE 1 = 1
AND MMT.INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = C1.ORGANIZATION_ID
AND TRUNC(MMT.TRANSACTION_DATE) <= TO_DATE(P_DATE, 'YYYY-MM-DD') --交易截止時間
AND MMT.PRIMARY_QUANTITY > 0 --應該只看增加的交易
AND MMT.TRANSACTION_TYPE_ID NOT IN (10008, 52) --排除 10008 銷貨成本認列 52 Salse Order Pick
AND MMT.TRANSACTION_TYPE_ID NOT IN
(15, 42, 43, 1326, 1328, 1336, 5536) --測試發現排除
--AND MMT.TRANSACTION_TYPE_ID NOT IN (15, 42, 43, 1328, 1336, 5536) --20230425 測試
AND MMT.TRANSACTION_ACTION_ID NOT IN (2, 24, 30) --原本程式就排除 2 倉庫移轉 24 成本更新、30 工單報廢
ORDER BY MMT.TRANSACTION_DATE DESC;
IF C1.ON_HAND_QTY > 0 AND P_COUNT = 0 THEN
INSERT INTO TEMP_TABLE
(TRANSACTION_ID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ON_HAND_QTY,
TRANSACTION_QTY,
ORIGINAL_TRANSACTION_DATE,
NEW_TRANSACTION_DATE,
CHECK_FLAG)
VALUES
(NULL,
NULL,
C1.ORGANIZATION_ID,
C1.INVENTORY_ITEM_ID,
C1.ON_HAND_QTY,
C1.ON_HAND_QTY,
NULL,
TO_DATE('1900-01-01', 'YYYY-MM-DD'),
NULL);
END IF;
END LOOP;
/*
調整交易數量
狀況一(需處理)、沒有CHECK_FLAG 沒有ERROR，但因排除交易，造成庫存數 <> 交易庫存數
狀況二(需處理)、ROW_COUNT = 1(交易資料只有一筆) + 庫存數與交易數不一樣
狀況三(不需處理)、ROW_COUNT = 1(交易資料只有一筆) + 庫存數與交易數一樣
狀況四(需處理)、ROW_COUNT <>1(交易資料多筆) + 庫存數與交易數不一樣
*/
DECLARE
CURSOR CHECK_ERROR IS
SELECT DISTINCT INVENTORY_ITEM_ID
FROM TEMP_TABLE
WHERE CHECK_FLAG = 'ERROR';
CURSOR CHECK_NO_ERROR IS
SELECT ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ON_HAND_QTY,
SUM(TRANSACTION_QTY) MMT_ON_HAND_QTY
FROM TEMP_TABLE
GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_ID, ON_HAND_QTY;
P_MMT_ROW_COUNT NUMBER;
P_TRANSACTION_QTY NUMBER;
BEGIN
FOR C1 IN CHECK_ERROR LOOP
SELECT COUNT(*)
INTO P_MMT_ROW_COUNT
FROM TEMP_TABLE
WHERE INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID;
--處理狀況二
IF P_MMT_ROW_COUNT = 1 THEN
UPDATE TEMP_TABLE A
SET A.TRANSACTION_QTY = A.ON_HAND_QTY, A.CHECK_FLAG = 'OK'
WHERE CHECK_FLAG = 'ERROR'
AND INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID;
--處理狀況四
ELSE
SELECT A.ON_HAND_QTY - SUM(A.TRANSACTION_QTY)
INTO P_TRANSACTION_QTY
FROM TEMP_TABLE A
WHERE CHECK_FLAG IS NULL
AND INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID
GROUP BY A.ON_HAND_QTY;
UPDATE TEMP_TABLE A
SET A.TRANSACTION_QTY = P_TRANSACTION_QTY, A.CHECK_FLAG = 'OK'
WHERE CHECK_FLAG = 'ERROR'
AND INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID;
END IF;
END LOOP;
FOR C2 IN CHECK_NO_ERROR LOOP
--處理狀況一
IF C2.ON_HAND_QTY <> C2.MMT_ON_HAND_QTY THEN
INSERT INTO TEMP_TABLE
(TRANSACTION_ID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ON_HAND_QTY,
TRANSACTION_QTY,
ORIGINAL_TRANSACTION_DATE,
NEW_TRANSACTION_DATE,
CHECK_FLAG)
VALUES
(NULL,
NULL,
C2.ORGANIZATION_ID,
C2.INVENTORY_ITEM_ID,
C2.ON_HAND_QTY,
C2.ON_HAND_QTY - C2.MMT_ON_HAND_QTY,
NULL,
TO_DATE('1900-01-01', 'YYYY-MM-DD'),
NULL);
END IF;
END LOOP;
END;
COMMIT;
END;
/*
庫齡結果
SELECT MMT.ORGANIZATION_ID,
MMT.TRANSACTION_ID,
MMT.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MMT.TRANSACTION_QTY,
CASE
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) <= 270 THEN
'A_0_270'
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) BETWEEN 271 AND 365 THEN
'B_271__365'
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) BETWEEN 366 AND 730 THEN
'C_366_730'
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) BETWEEN 731 AND 1095 THEN
'D_731_1095'
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) BETWEEN 1096 AND 1460 THEN
'E_1096_1460'
WHEN TO_DATE('20230331', 'YYYYMMDD') - TRUNC(MMT.NEW_TRANSACTION_DATE) > 1460 THEN
'F_1460UP'
END STOCK_AGE
FROM TEMP_TABLE MMT, MTL_SYSTEM_ITEMS_B MSI
WHERE 1 = 1
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
--AND MMT.INVENTORY_ITEM_ID = 13040420
CREATE TABLE TEMP_TABLE
(
TRANSACTION_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
ORGANIZATION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
ON_HAND_QTY NUMBER,
TRANSACTION_QTY NUMBER,
ORIGINAL_TRANSACTION_DATE DATE,
NEW_TRANSACTION_DATE DATE,
CHECK_FLAG VARCHAR2(10)
)
*/
2023-05-12
[Oracle]Stock age report
訂閱：
張貼留言 (Atom)
沒有留言:
張貼留言