[Oracle]How to Retire Asset

參考資料http://www.shareoracleapps.com/2022/12/api-to-retire-asset-in-oracle-apps-R12-FARETIREMENTPUB.html

DECLARE
  LE_API_ERROR EXCEPTION;

  -- ASSET TO BE RETIRED
  LV_ASSET_NUMBER   VARCHAR2(100) DEFAULT 'TEST0912-3';
  LV_BOOK_TYPE_CODE VARCHAR2(15) DEFAULT 'TJ资产帐'; --帳本 TW资产帐、TJ资产帐

  LN_ASSET_ID         NUMBER;
  LN_USER_ID          NUMBER := 0; --SYSADMIN
  LN_COST_RETIRED     NUMBER;
  LN_PROCEEDS_OF_SALE NUMBER := 0; --實際收入預設0
  LN_COST_OF_REMOVAL  NUMBER := 0; --拆遷成本預設0
  LN_REQUEST_ID       NUMBER;
  LR_TRANS_REC        FA_API_TYPES.TRANS_REC_TYPE;
  LR_DIST_TRANS_REC   FA_API_TYPES.TRANS_REC_TYPE;
  LR_ASSET_HDR_REC    FA_API_TYPES.ASSET_HDR_REC_TYPE;
  LR_ASSET_RETIRE_REC FA_API_TYPES.ASSET_RETIRE_REC_TYPE;
  LT_ASSET_DIST_TBL   FA_API_TYPES.ASSET_DIST_TBL_TYPE;
  LT_SUBCOMP_TBL      FA_API_TYPES.SUBCOMP_TBL_TYPE;
  LT_INV_TBL          FA_API_TYPES.INV_TBL_TYPE;
  LN_API_VERSION      NUMBER := 1;
  LV_INIT_MSG_LIST    VARCHAR2(1) := FND_API.G_FALSE;
  LV_COMMIT           VARCHAR2(1) := FND_API.G_TRUE;
  LV_VALIDATION_LEVEL NUMBER := FND_API.G_VALID_LEVEL_FULL;
  LV_CALLING_FUNC     VARCHAR2(80) := 'Shareoracleapps Wrapper';
  LV_RETURN_STS       VARCHAR2(1) := FND_API.G_FALSE;
  LN_MSG_CNT          NUMBER := 0;
  LV_MESSAGE          VARCHAR2(512);
  LN_COUNT            NUMBER;
  I                   NUMBER := 0;
  LV_DUMMY            VARCHAR2(512);
  LN_MESSAGE_COUNT    NUMBER;

  CURSOR CUR_FA_ADDITION IS
    SELECT A.ASSET_NUMBER, A.ASSET_ID, B.COST
      FROM FA_ADDITIONS_B A, FA_BOOKS_V B
     WHERE A.ASSET_ID = B.ASSET_ID
       AND A.ASSET_NUMBER = LV_ASSET_NUMBER
       --AND A.CREATION_DATE > SYSDATE - 300
       AND B.BOOK_TYPE_CODE = LV_BOOK_TYPE_CODE
       --AND ROWNUM < 5
       AND NOT EXISTS
     (SELECT 1 FROM FA_RETIREMENTS FR WHERE FR.ASSET_ID = A.ASSET_ID);

BEGIN
  FND_GLOBAL.APPS_INITIALIZE(USER_ID      => 0, --SYSADMIN
                             RESP_ID      => 50244, --ACETJ_FA_KeyUser
                             RESP_APPL_ID => 140);

  FOR I IN CUR_FA_ADDITION LOOP
    BEGIN
      FA_SRVR_MSG.INIT_SERVER_MESSAGE;
      FA_DEBUG_PKG.SET_DEBUG_FLAG(DEBUG_FLAG => 'YES');
      DBMS_OUTPUT.PUT_LINE('Asset_id :' || I.ASSET_ID);
    
      LN_ASSET_ID     := I.ASSET_ID;
      LN_COST_RETIRED := I.COST; --處分成本 = 成本
    
      -- GET STANDARD WHO INFO
      LN_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
      FND_PROFILE.GET('LOGIN_ID', LR_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN);
      FND_PROFILE.GET('USER_ID', LR_TRANS_REC.WHO_INFO.LAST_UPDATED_BY);
      IF (LR_TRANS_REC.WHO_INFO.LAST_UPDATED_BY IS NULL) THEN
        LR_TRANS_REC.WHO_INFO.LAST_UPDATED_BY := -1;
      END IF;
    
      IF (LR_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN IS NULL) THEN
        LR_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN := -1;
      END IF;
    
      LR_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE := SYSDATE;
      LR_TRANS_REC.WHO_INFO.CREATION_DATE    := SYSDATE;
      LR_TRANS_REC.WHO_INFO.CREATED_BY       := LR_TRANS_REC.WHO_INFO.LAST_UPDATED_BY;
      LR_ASSET_HDR_REC.ASSET_ID              := LN_ASSET_ID;
      LR_ASSET_HDR_REC.BOOK_TYPE_CODE        := LV_BOOK_TYPE_CODE;
      LR_TRANS_REC.TRANSACTION_TYPE_CODE     := NULL;
    
      -- THIS WILL BE DETERMINED INSIDE API
      LR_TRANS_REC.TRANSACTION_DATE_ENTERED             := NULL;
      LR_ASSET_HDR_REC.PERIOD_OF_ADDITION               := NULL;
      LR_ASSET_RETIRE_REC.RETIREMENT_PRORATE_CONVENTION := NULL;
      LR_ASSET_RETIRE_REC.DATE_RETIRED                  := NULL;
      -- WILL BE CURRENT PERIOD BY DEFAULT
      LR_ASSET_RETIRE_REC.UNITS_RETIRED        := 1;
      LR_ASSET_RETIRE_REC.COST_RETIRED         := LN_COST_RETIRED;
      LR_ASSET_RETIRE_REC.PROCEEDS_OF_SALE     := LN_PROCEEDS_OF_SALE;
      LR_ASSET_RETIRE_REC.COST_OF_REMOVAL      := LN_COST_OF_REMOVAL;
      LR_ASSET_RETIRE_REC.RETIREMENT_TYPE_CODE := 'SALE'; --SELECT * FROM FA_LOOKUPS_VL WHERE LOOKUP_TYPE = 'RETIREMENT'
      LR_ASSET_RETIRE_REC.TRADE_IN_ASSET_ID    := NULL;
      LR_ASSET_RETIRE_REC.CALCULATE_GAIN_LOSS  := FND_API.G_FALSE;
      FND_PROFILE.PUT('USER_ID', LN_USER_ID);
    
      LT_ASSET_DIST_TBL.DELETE;
      DBMS_OUTPUT.PUT_LINE('CALL API');
      FA_RETIREMENT_PUB.DO_RETIREMENT(P_API_VERSION       => LN_API_VERSION,
                                      P_INIT_MSG_LIST     => LV_INIT_MSG_LIST,
                                      P_COMMIT            => LV_COMMIT,
                                      P_VALIDATION_LEVEL  => LV_VALIDATION_LEVEL,
                                      P_CALLING_FN        => LV_CALLING_FUNC,
                                      X_RETURN_STATUS     => LV_RETURN_STS,
                                      X_MSG_COUNT         => LN_MSG_CNT,
                                      X_MSG_DATA          => LV_MESSAGE,
                                      PX_TRANS_REC        => LR_TRANS_REC,
                                      PX_DIST_TRANS_REC   => LR_DIST_TRANS_REC,
                                      PX_ASSET_HDR_REC    => LR_ASSET_HDR_REC,
                                      PX_ASSET_RETIRE_REC => LR_ASSET_RETIRE_REC,
                                      P_ASSET_DIST_TBL    => LT_ASSET_DIST_TBL,
                                      P_SUBCOMP_TBL       => LT_SUBCOMP_TBL,
                                      P_INV_TBL           => LT_INV_TBL);
    
      IF LV_RETURN_STS = FND_API.G_FALSE THEN
        RAISE LE_API_ERROR;
      ELSE
        DBMS_OUTPUT.PUT_LINE('lv_return_sts :' || LV_RETURN_STS);
      END IF;
    
      DBMS_OUTPUT.PUT_LINE('Asset Retirement Done: id: ' || LR_ASSET_RETIRE_REC.RETIREMENT_ID);
    
      IF (FA_DEBUG_PKG.PRINT_DEBUG) THEN
        FA_DEBUG_PKG.WRITE_DEBUG_LOG;
      END IF;
      FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => LV_CALLING_FUNC,
                              NAME       => 'FA_SHARED_END_SUCCESS',
                              TOKEN1     => 'PROGRAM',
                              VALUE1     => 'RETIREMENT_API');
    
      LN_MESSAGE_COUNT := FND_MSG_PUB.COUNT_MSG;
      IF (LN_MESSAGE_COUNT > 0) THEN
        LV_DUMMY := FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);
        DBMS_OUTPUT.PUT_LINE('dump: ' || LV_DUMMY);
        FOR I IN 1 .. (LN_MESSAGE_COUNT - 1) LOOP
          LV_DUMMY := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
          DBMS_OUTPUT.PUT_LINE('dump: ' || LV_DUMMY);
        END LOOP;
      
      ELSE
        DBMS_OUTPUT.PUT_LINE('dump: NO MESSAGE !');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error :' || SQLERRM);
    END;
  END LOOP;

EXCEPTION
  WHEN LE_API_ERROR THEN
    ROLLBACK WORK;
    FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => LV_CALLING_FUNC,
                            NAME       => 'FA_SHARED_PROGRAM_FAILED',
                            TOKEN1     => 'PROGRAM',
                            VALUE1     => LV_CALLING_FUNC);
  
    LN_MESSAGE_COUNT := FND_MSG_PUB.COUNT_MSG;
    
    IF (LN_MESSAGE_COUNT > 0) THEN
      LV_DUMMY := FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);
      DBMS_OUTPUT.PUT_LINE('DUMP: ' || LV_DUMMY);
      
      FOR I IN 1 .. (LN_MESSAGE_COUNT - 1) LOOP
        LV_DUMMY := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
        DBMS_OUTPUT.PUT_LINE('DUMP: ' || LV_DUMMY);
      END LOOP;
    
    ELSE
      DBMS_OUTPUT.PUT_LINE('dump: NO MESSAGE !');
    END IF;
  
    COMMIT;
END;
/

/*
觀念:當執行折舊或關閉期間時會觸發請求集"折舊執行請求集 (財務帳本 - 將期間關帳)"
計算損益(Calculate Gains and Losses)
折舊執行(Depreciation Run)
日記帳分錄準備分類帳報表(Journal Entry Reserve Ledger Report)
處理暫緩交易(Process Pending Transactions)
*/
DECLARE
  LV_BOOK_TYPE_CODE VARCHAR2(15) := 'TJ资产帐'; --帳本 TW资产帐、TJ资产帐
  LN_REQUEST_ID     NUMBER;
BEGIN
  FND_GLOBAL.APPS_INITIALIZE(USER_ID      => 0, --SYSADMIN
                             RESP_ID      => 50244, --ACETJ_FA_KeyUser
                             RESP_APPL_ID => 140);

  LN_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('OFA',
                                              'FARET', --計算損益/Calculate Gains and Losses
                                              NULL,
                                              NULL,
                                              FALSE,
                                              LV_BOOK_TYPE_CODE);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE(LN_REQUEST_ID);
END;
/