[Oracle]How to assign item to organization

 DROP TABLE TEST_20160930;

--CREATE DATA TABLE
CREATE TABLE  TEST_20160930 AS
SELECT MSI.INVENTORY_ITEM_ID,
       MSI.ORGANIZATION_ID,
       MSI.SEGMENT1,
       MSI.DESCRIPTION,
       MSI. PRIMARY_UOM_CODE,
       MSI.EXPENSE_ACCOUNT
  FROM MTL_SYSTEM_ITEMS_B MSI
 WHERE 1 = 0
   AND MSI.SEGMENT1 = 'A-621600'
   AND MSI.ORGANIZATION_ID = 3;
  
SELECT * FROM TEST_20160930 FOR UPDATE;


--UPDATE MSI.EXPENSE_ACCOUNT
DECLARE
  CURSOR P_DATE IS
    SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, EXPENSE_ACCOUNT
      FROM TEST_20160930;
  --FOR UPDATE;
BEGIN
  FOR C1 IN P_DATE LOOP
    UPDATE MTL_SYSTEM_ITEMS_B MSI
       SET MSI.EXPENSE_ACCOUNT = C1.EXPENSE_ACCOUNT
     WHERE MSI.INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID
       AND MSI.ORGANIZATION_ID = C1.ORGANIZATION_ID;
  END LOOP;
  COMMIT;
END;
/


--ASSIGN_ITEM_TO_ORG
DECLARE
  G_USER_ID           FND_USER.USER_ID%TYPE := NULL;
  L_APPL_ID           FND_APPLICATION.APPLICATION_ID%TYPE;
  L_RESP_ID           FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID%TYPE;
  L_API_VERSION       NUMBER := 1.0;
  L_INIT_MSG_LIST     VARCHAR2(2) := FND_API.G_FALSE;
  L_COMMIT            VARCHAR2(2) := FND_API.G_FALSE;
  X_MESSAGE_LIST      ERROR_HANDLER.ERROR_TBL_TYPE;
  X_RETURN_STATUS     VARCHAR2(2);
  X_MSG_COUNT         NUMBER := 0;
  L_SEGMENT1          MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
  L_PRIMARY_UOM_CODE  MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE%TYPE;
  L_ORIGINAL_ORG      MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE := 3; --00 ORG_ID這個部份要修改
  L_ORGANIZATION_ID   MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE := 385; --要Assign的ORG_ID，這個部份要修改
  L_ORGANIZATION_CODE ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_CODE%TYPE;

  CURSOR P_DATE IS
    SELECT INVENTORY_ITEM_ID, SEGMENT1 FROM TEST_20160930; --客製Table要塞入資料
  --SELECT INVENTORY_ITEM_ID, SEGMENT1 FROM TEST_20160930 FOR UPDATE; 

BEGIN
  SELECT FA.APPLICATION_ID
    INTO L_APPL_ID
    FROM FND_APPLICATION FA
   WHERE FA.APPLICATION_SHORT_NAME = 'INV';

  SELECT FR.RESPONSIBILITY_ID
    INTO L_RESP_ID
    FROM FND_APPLICATION FA, FND_RESPONSIBILITY_TL FR
   WHERE FA.APPLICATION_SHORT_NAME = 'INV'
     AND FA.APPLICATION_ID = FR.APPLICATION_ID
     AND UPPER(FR.RESPONSIBILITY_NAME) = 'INVENTORY';

  FND_GLOBAL.APPS_INITIALIZE(G_USER_ID, L_RESP_ID, L_APPL_ID);

  SELECT OOD.ORGANIZATION_CODE
    INTO L_ORGANIZATION_CODE
    FROM ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE OOD.ORGANIZATION_ID = L_ORGANIZATION_ID;

  FOR C1 IN P_DATE LOOP
    SELECT MSI.SEGMENT1, MSI.PRIMARY_UOM_CODE
      INTO L_SEGMENT1, L_PRIMARY_UOM_CODE
      FROM MTL_SYSTEM_ITEMS_B MSI
     WHERE MSI.INVENTORY_ITEM_ID = C1.INVENTORY_ITEM_ID
       AND MSI.ORGANIZATION_ID = L_ORIGINAL_ORG;
  
    EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(P_API_VERSION       => L_API_VERSION,
                                    P_INIT_MSG_LIST     => L_INIT_MSG_LIST,
                                    P_COMMIT            => L_COMMIT,
                                    P_INVENTORY_ITEM_ID => C1.INVENTORY_ITEM_ID,
                                    P_ITEM_NUMBER       => L_SEGMENT1,
                                    P_ORGANIZATION_ID   => L_ORGANIZATION_ID,
                                    P_ORGANIZATION_CODE => L_ORGANIZATION_CODE,
                                    P_PRIMARY_UOM_CODE  => L_PRIMARY_UOM_CODE,
                                    X_RETURN_STATUS     => X_RETURN_STATUS,
                                    X_MSG_COUNT         => X_MSG_COUNT);
    DBMS_OUTPUT.PUT_LINE('STATUS: ' || X_RETURN_STATUS);
  
  END LOOP;
  IF (X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
    DBMS_OUTPUT.PUT_LINE('ERROR MESSAGES :');
    ERROR_HANDLER.GET_MESSAGE_LIST(X_MESSAGE_LIST => X_MESSAGE_LIST);
    FOR J IN 1 .. X_MESSAGE_LIST.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(X_MESSAGE_LIST(J).MESSAGE_TEXT);
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION OCCURED :');
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;
/