前提:所在系统中允许此物料允许多个类别分配,否则只能通过interface表的更新功能更新物料类别。
DECLARE
X_RETURN_STATUS VARCHAR2(1);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
X_ERRORCODE VARCHAR2(30);
L_CUR_MFG_ORG_ID NUMBER :=107; --Current Inv Organization
L_USER_ID NUMBER := 11236; --User ID, Sysadmin here
BEGIN
--Initialize first, or create_by will be -1
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 11236
,RESP_ID => 50606
,RESP_APPL_ID => 401
);
--删除已经创建的类别 --某些情况下会不成功,会报必须的物料类别集不允许删除
/*
INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
P_API_VERSION => '1.0'
,P_INIT_MSG_LIST => FND_API.G_TRUE
,P_COMMIT => FND_API.G_FALSE
,X_RETURN_STATUS => X_RETURN_STATUS
,X_ERRORCODE => X_ERRORCODE
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
,P_CATEGORY_ID =>2129 -- 147430
,P_CATEGORY_SET_ID => 1
,P_INVENTORY_ITEM_ID => 14737358
,P_ORGANIZATION_ID => L_CUR_MFG_ORG_ID
);
*/
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
P_API_VERSION => '1.0'
,P_INIT_MSG_LIST => FND_API.G_TRUE
,P_COMMIT => FND_API.G_FALSE
,X_RETURN_STATUS => X_RETURN_STATUS
,X_ERRORCODE => X_ERRORCODE
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
,P_CATEGORY_ID => 147430
,P_CATEGORY_SET_ID => 1
, --1 Inventory
P_INVENTORY_ITEM_ID => 14737358
,P_ORGANIZATION_ID => L_CUR_MFG_ORG_ID
);
IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
ROLLBACK;
dbms_output.PUT_LINE('error code : ' || X_ERRORCODE);
FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
dbms_output.PUT_LINE('error count : ' || TO_CHAR(X_MSG_COUNT));
dbms_output.PUT_LINE('X_MSG_DATA'||REPLACE(X_MSG_DATA, CHR(0), ' '));
FOR I IN 2 .. X_MSG_COUNT LOOP
X_MSG_DATA := FND_MSG_PUB.GET;
dbms_output.PUT_LINE(REPLACE(X_MSG_DATA, CHR(0), ' '));
END LOOP;
IF (X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
ELSIF (X_RETURN_STATUS = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
END IF;
ELSE
COMMIT;
dbms_output.PUT_LINE('Successfully.');
END IF;
END;