[Oracle]How to Transfer Asset

--參考資料 https://docs.oracle.com/cd/E18727_01/doc.121/e13586/T293142T472088.htm --有多筆資料都會修正為新的保管人

DECLARE
  L_TRANS_REC      FA_API_TYPES.TRANS_REC_TYPE;
  L_ASSET_HDR_REC  FA_API_TYPES.ASSET_HDR_REC_TYPE;
  L_ASSET_DIST_TBL FA_API_TYPES.ASSET_DIST_TBL_TYPE;

  L_RETURN_STATUS VARCHAR2(1);
  L_MESG_COUNT    NUMBER;
  L_MESG          VARCHAR2(512);

  P_ASSET_ID NUMBER;
  CURSOR CUR_FA_ADDITION IS
    SELECT FA.ASSET_ID,
           FDH.DISTRIBUTION_ID,
           FDH.BOOK_TYPE_CODE,
           FDH.UNITS_ASSIGNED,
           FDH.ASSIGNED_TO,
           FDH.CODE_COMBINATION_ID,
           FDH.LOCATION_ID
      FROM FA_ADDITIONS FA, FA_DISTRIBUTION_HISTORY FDH
     WHERE 1 = 1
       AND FA.ASSET_ID = FDH.ASSET_ID
       AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
       AND FA.ASSET_NUMBER = '90-BA-22-002';

BEGIN
  FND_PROFILE.PUT('PRINT_DEBUG', 'Y');
  DBMS_OUTPUT.ENABLE(1000000);

  FA_SRVR_MSG.INIT_SERVER_MESSAGE;
  FA_DEBUG_PKG.INITIALIZE;
  FOR C1 IN CUR_FA_ADDITION LOOP
    -- FILL IN ASSET INFORMATION
    L_ASSET_HDR_REC.ASSET_ID := C1.ASSET_ID;
    -- FILL IN DISTRIBUTION DATA FOR EXISTING DISTRIBUTION LINES
    -- AFFECTED BY THIS TRANSFER TXN. NOTE: YOU NEED TO FILL IN 
    -- ONLY AFFECTED DISTRIBUTION LINES. 
    --
    -- FOR SOURCE DISTRIBUTION, YOU MUST FILL IN EITHER EXISTING 
    -- DISTRIBUTION ID OR 2 COLUMNS(EXPENSE_CCID,LOCATION_CCID) OR
    -- 3-TUPLE COLUMNS(ASSIGNED_TO,EXPENSE_CCID, AND LOCATION_CCID)
    -- DEPENDING ON THE MAKEUP OF THE PARTICULAR DISTRIBUTION
    -- OF THE ASSET. 
    L_ASSET_DIST_TBL(1).TRANSACTION_UNITS := C1.UNITS_ASSIGNED * -1;
    -- SOURCE
    -- OPTION A - KNOWN DIST ID
    L_ASSET_DIST_TBL(1).DISTRIBUTION_ID := C1.DISTRIBUTION_ID;
    -- OR 
    -- OPTION B - KNOWN DIST ATTRIBUTES
    L_ASSET_DIST_TBL(1).ASSIGNED_TO := C1.ASSIGNED_TO;
    L_ASSET_DIST_TBL(1).EXPENSE_CCID := C1.CODE_COMBINATION_ID;
    L_ASSET_DIST_TBL(1).LOCATION_CCID := C1.LOCATION_ID;
    -- DESTINATION
    -- FILL IN DIST INFO FOR ONE OR MORE DESTINATION DISTRIBUTION (START WITH 2..(3,4,..))
    L_ASSET_DIST_TBL(2).TRANSACTION_UNITS := C1.UNITS_ASSIGNED;
    L_ASSET_DIST_TBL(2).ASSIGNED_TO := 18484; --SELECT PERSON_ID FROM PER_PEOPLE_V7
    L_ASSET_DIST_TBL(2).EXPENSE_CCID := C1.CODE_COMBINATION_ID;
    L_ASSET_DIST_TBL(2).LOCATION_CCID := C1.LOCATION_ID;
  
    FA_TRANSFER_PUB.DO_TRANSFER(
                                -- STD PARAMETERS
                                P_API_VERSION      => 1.0,
                                P_INIT_MSG_LIST    => FND_API.G_FALSE,
                                P_COMMIT           => FND_API.G_FALSE,
                                P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
                                P_CALLING_FN       => NULL,
                                X_RETURN_STATUS    => L_RETURN_STATUS,
                                X_MSG_COUNT        => L_MESG_COUNT,
                                X_MSG_DATA         => L_MESG,
                                -- API PARAMETERS
                                PX_TRANS_REC      => L_TRANS_REC,
                                PX_ASSET_HDR_REC  => L_ASSET_HDR_REC,
                                PX_ASSET_DIST_TBL => L_ASSET_DIST_TBL);
  
    --DUMP MESSAGES
    L_MESG_COUNT := FND_MSG_PUB.COUNT_MSG;
  
    IF L_MESG_COUNT > 0 THEN
      L_MESG := CHR(10) || SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST, FND_API.G_FALSE), 1, 250);
      DBMS_OUTPUT.PUT_LINE(L_MESG);
    
      FOR I IN 1 .. (L_MESG_COUNT - 1) LOOP
        L_MESG := SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE), 1, 250);
      
        DBMS_OUTPUT.PUT_LINE(L_MESG);
      END LOOP;
    
      FND_MSG_PUB.DELETE_MSG();
    
    END IF;
  
    IF (L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
      DBMS_OUTPUT.PUT_LINE('FAILURE');
    ELSE
      DBMS_OUTPUT.PUT_LINE('SUCCESS');
      DBMS_OUTPUT.PUT_LINE('THID' || TO_CHAR(L_TRANS_REC.TRANSACTION_HEADER_ID));
    END IF;
  END LOOP;
END;