如果你想把料號的屬性清空,你可以參考這篇文章
=== 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;