API分配物料类别集

 前提:所在系统中允许此物料允许多个类别分配，否则只能通过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;