Oracle eBS經驗分享

如果你想把料號的屬性清空，你可以參考這篇文章

=== Oracle Inventory ===

(ERP Version : 11.5.10.2)

Goal

Updating Item Attributes to NULL

 The method to update these columns to NULL is to use the following values:

· for Numeric fields: insert -999999

· for Character fields: insert '!'

· for Date fields: the above list does not include any updateable date fields.

PL/SQL Script

DECLARE
   P_ORGANIZATION_ID   MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE   := 100;
   P_USER_ID           FND_USER.USER_ID%TYPE;
   P_SET_PROCESS_ID    MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE    := 4000;

   CURSOR CUR2 IS
      SELECT DISTINCT TRIM (A.SEGMENT1) SEGMENT1
                    , NVL (TRIM (A.MOQ), -999999) MOQ
                    , NVL (TRIM (A.MAX_ORDER_QTY), -999999) MAX_ORDER_QTY
                    , A.ORGANIZATION_CODE
                 FROM DII_ITEM_MOQ_UD_200912 A;
BEGIN
   SELECT USER_ID
     INTO P_USER_ID
     FROM FND_USER
    WHERE USER_NAME = 'MIKECHEN';

   DBMS_OUTPUT.PUT_LINE ('Update MOQ ' || TO_CHAR (SYSDATE, 'yyyy/mm/dd hh24:mi:ss') );

   -- before item import , we need clear all item interface
   -- Reference from Notes:106812.1 in metalink
   DELETE MTL_SYSTEM_ITEMS_INTERFACE;

   DELETE MTL_ITEM_REVISIONS_INTERFACE;

   DELETE MTL_ITEM_CATEGORIES_INTERFACE;

   DELETE MTL_INTERFACE_ERRORS;

   DELETE MTL_ITEM_CATEGORIES_INTERFACE;

   COMMIT;

-------------------------------------------
-- ex. update MINIMUM_ORDER_QUANTITY and MAXIMUM_ORDER_QUANTITY
-------------------------------------------

   FOR REC2 IN CUR2 LOOP
      INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
                  (
                   PROCESS_FLAG
                 , SET_PROCESS_ID
                 , TRANSACTION_TYPE
                 , ORGANIZATION_CODE
                 , SEGMENT1
                 , CREATION_DATE
                 , CREATED_BY
                 , LAST_UPDATE_DATE
                 , LAST_UPDATED_BY
                 , LAST_UPDATE_LOGIN
                 , MINIMUM_ORDER_QUANTITY
                 , MAXIMUM_ORDER_QUANTITY
                  )
           VALUES (
                   1
                 , P_SET_PROCESS_ID
                 , 'UPDATE'
                 , REC2.ORGANIZATION_CODE
                 , REC2.SEGMENT1
                 , SYSDATE
                 , P_USER_ID
                 , SYSDATE
                 , P_USER_ID
                 , -1
                 , REC2.MOQ
                 , REC2.MAX_ORDER_QTY
                  );
   END LOOP;

   COMMIT;
   DBMS_OUTPUT.PUT_LINE (' Complete at ' || TO_CHAR (SYSDATE, 'yyyy/mm/dd hh24:mi:ss') );
END;