[Oracle]How to Mass Inactive Inventory item

--尋找兩年內沒有異動料號

SELECT COUNT(*) FROM YOLIN_TEMP WHERE RPT_ID = 'ITEM_CHECK'
--清空資料
DELETE YOLIN_TEMP WHERE RPT_ID = 'ITEM_CHECK'

--整理久未異動料號
DECLARE
  CURSOR CHECK_ITEM IS
    SELECT MSI.INVENTORY_ITEM_ID
      FROM MTL_SYSTEM_ITEMS_B MSI
     WHERE 1 = 1
       AND MSI.ORGANIZATION_ID = 137
       AND MSI.INVENTORY_ITEM_STATUS_CODE <> 'Inactive'
          --AND MSI.INVENTORY_ITEM_ID = 9161201
       AND TRUNC(MSI.CREATION_DATE) <= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -12)) + 1) --排除近一年建立
       AND MSI.INVENTORY_ITEM_ID NOT IN (SELECT MS.ITEM_ID FROM MTL_SUPPLY MS) --排除有供需料號 
       AND MSI.INVENTORY_ITEM_ID NOT IN (SELECT DISTINCT ITEM_ID
                                           FROM (SELECT BOM.ASSEMBLY_ITEM_ID ITEM_ID
                                                   FROM BOM_BILL_OF_MATERIALS BOM
                                                 UNION ALL
                                                 SELECT BIC.COMPONENT_ITEM_ID ITEM_ID
                                                   FROM BOM_INVENTORY_COMPONENTS BIC)); --排除BOM 料號

  P_MMT_COUNT NUMBER;
  P_MONTH     NUMBER := -36; --三年MMT沒有紀錄
BEGIN
  FOR C1 IN CHECK_ITEM LOOP
    SELECT COUNT(*)
      INTO P_MMT_COUNT
      FROM MTL_MATERIAL_TRANSACTIONS MMT
     WHERE 1 = 1
       AND MMT.INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID
       AND MMT.ORGANIZATION_ID = 137
       AND TRUNC(MMT.TRANSACTION_DATE) BETWEEN TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, P_MONTH)) + 1) AND TRUNC(SYSDATE);
    
    IF P_MMT_COUNT = 0 THEN
      INSERT INTO YOLIN_TEMP (RPT_ID, LINE_NO) VALUES ('ITEM_CHECK', C1.INVENTORY_ITEM_ID);
    END IF;
  
  END LOOP;
END;

--資料備份
CREATE TABLE MTL_SYSTEM_ITEMS_B_230915 AS
SELECT * FROM MTL_SYSTEM_ITEMS_B MSI

--大量失效料號
DECLARE
  L_ITEM_TABLE    EGO_ITEM_PUB.ITEM_TBL_TYPE;
  X_ITEM_TABLE    EGO_ITEM_PUB.ITEM_TBL_TYPE;
  X_MESSAGE_LIST  ERROR_HANDLER.ERROR_TBL_TYPE;
  X_RETURN_STATUS VARCHAR2(300);
  X_MSG_COUNT     NUMBER;
  X_MSG_DATA      VARCHAR2(4000);

  P_COUNTER NUMBER := 0; --紀錄失效筆數

  CURSOR ITEMS IS
    --排除委外加工料號、BOM 料號
    SELECT MSI.ORGANIZATION_ID,
           MSI.INVENTORY_ITEM_ID,
           MSI.SEGMENT1,
           FLV.MEANING ITEM_TYPE,
           MSI.DESCRIPTION,
           MSIV.LONG_DESCRIPTION,
           MSI.INVENTORY_ITEM_STATUS_CODE,
           QTY.ON_HAND_QTY,
           INVMC.SEGMENT1 || '.' || INVMC.SEGMENT2 || '.' || INVMC.SEGMENT3 || '.' || INVMC.SEGMENT4 || '.' || INVMC.SEGMENT5 || '.' || INVMC.SEGMENT6 INV_CATEGORY,
           (SELECT FLV.TAG 
              FROM FND_LOOKUP_TYPES_TL FLTT,
                   FND_LOOKUP_TYPES    FLT,
                   FND_LOOKUP_VALUES   FLV
             WHERE FLT.LOOKUP_TYPE = FLTT.LOOKUP_TYPE
               AND FLT.SECURITY_GROUP_ID = FLTT.SECURITY_GROUP_ID
               AND FLT.VIEW_APPLICATION_ID = FLTT.VIEW_APPLICATION_ID
               AND FLTT.LOOKUP_TYPE = FLV.LOOKUP_TYPE
               AND FLTT.DESCRIPTION = '產品處與產品對應表'
               AND FLV.LANGUAGE = 'ZHT'
               AND FLTT.LANGUAGE = 'ZHT'
               AND FLV.LOOKUP_CODE =  INVMC.SEGMENT1 || '.' || INVMC.SEGMENT2 || '.' || INVMC.SEGMENT3 || '.' || INVMC.SEGMENT4 || '.' || INVMC.SEGMENT5 || '.' || INVMC.SEGMENT6
            ) PM
      FROM YOLIN_TEMP Y,
           MTL_ITEM_CATEGORIES  INVMIC,
           MTL_CATEGORIES_V     INVMC,
           MTL_SYSTEM_ITEMS_B MSI,
           MTL_SYSTEM_ITEMS_FVL MSIV,
           FND_LOOKUP_VALUES FLV,
           (SELECT MMT.ORGANIZATION_ID,
                   MMT.INVENTORY_ITEM_ID,
                   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 = 'MM-24K'
                  --AND MSI.INVENTORY_ITEM_ID = 13040420
               AND MMT.ORGANIZATION_ID = 137
               AND TRUNC(MMT.TRANSACTION_DATE) <= TRUNC(SYSDATE --庫存計算截止時間
                  --AND MMT.TRANSACTION_TYPE_ID NOT IN (10008, 26, 52, 80) 錯誤觀念也要計算
               AND SUBINV.ASSET_INVENTORY <> 2 --排除費用倉
             GROUP BY MMT.ORGANIZATION_ID, MMT.INVENTORY_ITEM_ID
            --HAVING SUM(MMT.PRIMARY_QUANTITY) <> 0
            ) QTY
     WHERE 1 = 1
       AND Y.RPT_ID = 'ITEM_CHECK'
       AND MSI.ORGANIZATION_ID = 137
       AND MSI.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
       AND MSI.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
       AND Y.LINE_NO = MSI.INVENTORY_ITEM_ID
       AND FLV.LOOKUP_TYPE = 'ITEM_TYPE'
       AND NVL(MSI.ITEM_TYPE,'CLASS') = FLV.LOOKUP_CODE(+)
       AND FLV.LANGUAGE = 'ZHT'
       AND MSI.ORGANIZATION_ID = QTY.ORGANIZATION_ID(+)
       AND MSI.INVENTORY_ITEM_ID = QTY.INVENTORY_ITEM_ID(+)
       AND MSI.INVENTORY_ITEM_ID = INVMIC.INVENTORY_ITEM_ID
       AND MSI.ORGANIZATION_ID = INVMIC.ORGANIZATION_ID
       AND INVMIC.CATEGORY_ID = INVMC.CATEGORY_ID
       AND INVMIC.CATEGORY_SET_ID = 1
       AND FLV.MEANING <> '委外加工料號' --排除委外加工料號
       --AND INVMC.SEGMENT1 = '50'
       
    
BEGIN

  FND_GLOBAL.APPS_INITIALIZE(USER_ID      => 0,
                             RESP_ID      => 50532,
                             RESP_APPL_ID => 401);

  FOR C1 IN ITEMS LOOP
    P_COUNTER := P_COUNTER + 1;
    L_ITEM_TABLE(P_COUNTER).TRANSACTION_TYPE := 'UPDATE';
    L_ITEM_TABLE(P_COUNTER).INVENTORY_ITEM_ID := C1.INVENTORY_ITEM_ID;
    L_ITEM_TABLE(P_COUNTER).ORGANIZATION_ID := C1.ORGANIZATION_ID;
    L_ITEM_TABLE(P_COUNTER).INVENTORY_ITEM_STATUS_CODE := 'Inactive';
  
    EGO_ITEM_PUB.PROCESS_ITEMS(P_API_VERSION   => 1.0,
                               P_INIT_MSG_LIST => FND_API.G_TRUE,
                               P_COMMIT        => FND_API.G_TRUE,
                               P_ITEM_TBL      => L_ITEM_TABLE,
                               X_ITEM_TBL      => X_ITEM_TABLE,
                               X_RETURN_STATUS => X_RETURN_STATUS,
                               X_MSG_COUNT     => X_MSG_COUNT);
  
    DBMS_OUTPUT.PUT_LINE(X_ITEM_TABLE.COUNT);
    
    IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) THEN
      DBMS_OUTPUT.PUT_LINE('ORG_ID :' || C1.ORGANIZATION_ID ||'，ITEM_ID :' || C1.INVENTORY_ITEM_ID);
    ELSE
      DBMS_OUTPUT.PUT_LINE('ERROR-' || 'ORG_ID :' || C1.ORGANIZATION_ID || '，ITEM_ID :' || C1.INVENTORY_ITEM_ID);
      ERROR_HANDLER.GET_MESSAGE_LIST(X_MESSAGE_LIST => X_MESSAGE_LIST);
      FOR I IN 1 .. X_MESSAGE_LIST.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(X_MESSAGE_LIST(I).MESSAGE_TEXT);
      END LOOP;
    END IF;
  END LOOP;
  
  COMMIT;
END;


--比對目前失效狀況
SELECT A.ORGANIZATION_ID,
       A.INVENTORY_ITEM_ID,
       A.SEGMENT1,
       A.INVENTORY_ITEM_STATUS_CODE,
       B.INVENTORY_ITEM_STATUS_CODE
  FROM MTL_SYSTEM_ITEMS_B_230915 A , 
       MTL_SYSTEM_ITEMS_B B
 WHERE 1=1
   AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
   AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
   AND B.ORGANIZATION_ID = 137
   AND A.INVENTORY_ITEM_STATUS_CODE <> B.INVENTORY_ITEM_STATUS_CODE