FA Projections Report (Depreciation Projection Report) - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/10/fa-projections-report-depreciation.html

The Below PLSQL Query is useful for Depreciation Forecasting

Please run Projections before running the below Query. Navigation Depreciation>Projections

SELECT

 pi.BOOK_TYPE_CODE,
 ad.ASSET_NUMBER,
 pi.period_name,
 pi.fiscal_year,
 cc.SEGMENT1 || '-' || cc.SEGMENT2 || '-' || cc.SEGMENT3 || '-' ||
 cc.SEGMENT4 Expense_Account,
 sum(pi.DEPRECIATION)

  FROM fa_system_controls,   
       fa_proj_interim_rpt pi,   
       fa_additions ad, 
       gl_code_combinations cc

 WHERE pi.code_combination_id = cc.code_combination_id   
   AND pi.asset_id = ad.asset_id   
   AND pi.request_id in (select max(request_id) from fa_proj_interim_rpt)   
   AND pi.BOOK_TYPE_CODE = &Book

 GROUP BY pi.BOOK_TYPE_CODE,
          ad.ASSET_NUMBER,   
          pi.fiscal_year,     
          pi.period_index,
          pi.period_name,
          cc.SEGMENT1,
          cc.SEGMENT2,
          cc.SEGMENT3,
          cc.SEGMENT4

 ORDER BY pi.fiscal_year, 
          pi.period_index,     
          pi.period_name

Journal Entry Reserve Ledger Report / Fixed Assets Register - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/10/journal-entry-reserve-ledger-report-or.html


The Below PLSQL Query is Useful to get Asset Cost, Accumulated Depreciation, NBV and Charge Account of Assets.


Please run Standard Oracle Report "Journal Entry Reserve Ledger Report" in Fixed Assets before running the below Query


SELECT

 AD.ASSET_NUMBER,
 AD.DESCRIPTION,
 DATE_PLACED_IN_SERVICE,
 FC.SEGMENT1,
 fc.SEGMENT2,
 fc.SEGMENT3,

 (select GCC1.SEGMENT1 || '-' || GCC1.SEGMENT2 || '-' || GCC1.SEGMENT3 || '-' ||
         GCC1.SEGMENT4 || '-' || GCC1.SEGMENT5 || '-' || GCC1.SEGMENT6 || '-' ||
         GCC1.SEGMENT7 || '-' || GCC1.SEGMENT8
    from GL_CODE_COMBINATIONS gcc1
   where gcc1.CODE_COMBINATION_ID = FCB.ASSET_COST_ACCOUNT_CCID) CATG_ASSET_COST_ACCOUNT,

 (select GCC2.SEGMENT1 || '-' || GCC2.SEGMENT2 || '-' || GCC2.SEGMENT3 || '-' ||
         GCC2.SEGMENT4 || '-' || GCC2.SEGMENT5 || '-' || GCC2.SEGMENT6 || '-' ||
         GCC2.SEGMENT7 || '-' || GCC2.SEGMENT8
    from GL_CODE_COMBINATIONS gcc2
   where gcc2.CODE_COMBINATION_ID = FCB.ASSET_CLEARING_ACCOUNT_CCID) CATG_ASSET_CLEARING_ACCOUNT,

 (select GCC3.SEGMENT1 || '-' || GCC3.SEGMENT2 || '-' || GCC3.SEGMENT3 || '-' ||
         GCC3.SEGMENT4 || '-' || GCC3.SEGMENT5 || '-' || GCC3.SEGMENT6 || '-' ||
         GCC3.SEGMENT7 || '-' || GCC3.SEGMENT8
    from GL_CODE_COMBINATIONS gcc3
   where GCC3.CODE_COMBINATION_ID = FCB.DEPRN_EXPENSE_ACCOUNT_CCID) CATG_DEPRN_EXPENSE_ACCOUNT,

 (select GCC4.SEGMENT1 || '-' || GCC4.SEGMENT2 || '-' || GCC4.SEGMENT3 || '-' ||
         GCC4.SEGMENT4 || '-' || GCC4.SEGMENT5 || '-' || GCC4.SEGMENT6 || '-' ||
         GCC4.SEGMENT7 || '-' || GCC4.SEGMENT8
    from GL_CODE_COMBINATIONS gcc4
   where gcc4.CODE_COMBINATION_ID = FCB.RESERVE_ACCOUNT_CCID) CATG_DEPRN_RESERVE_ACCT,

 (select GCC5.SEGMENT1 || '-' || GCC5.SEGMENT2 || '-' || GCC5.SEGMENT3 || '-' ||
         GCC5.SEGMENT4 || '-' || GCC5.SEGMENT5 || '-' || GCC5.SEGMENT6 || '-' ||
         GCC5.SEGMENT7 || '-' || GCC5.SEGMENT8
    from FA_DISTRIBUTION_HISTORY dh, gl_code_combinations GCC5
   where dh.CODE_COMBINATION_ID = GCC5.CODE_COMBINATION_ID
     and dh.ASSET_ID = AD.ASSET_ID
     and dh.DATE_INEFFECTIVE is null
     and dh.BOOK_TYPE_CODE = &Book) DIST_DEPRN_EXPENSE_ACCOUNT,
 METHOD_CODE METHOD,
 RSV.LIFE LIFE,
 RSV.RATE ADJ_RATE,
 DS.BONUS_RATE,
 RSV.CAPACITY,
 SUM(decode(transaction_type, 'B', NULL, COST)) COST,
 SUM(RSV.DEPRN_AMOUNT) DEPRN_AMOUNT,
 SUM(RSV.YTD_DEPRN) YTD_DEPRN,
 SUM(RSV.DEPRN_RESERVE) DEPRN_RESERVE,
 SUM(decode(transaction_type, 'B', NULL, COST)) - SUM(RSV.DEPRN_RESERVE) NBV,
 sum(decode(transaction_type, 'B', NULL, nvl(PERCENT, 0))) PERCENT,
 TRANSACTION_TYPE T_TYPE

  FROM fa_reserve_ledger_itf RSV,
       fa_deprn_periods      g,
       FA_ADDITIONS          AD,
       GL_CODE_COMBINATIONS  CC,
       fa_deprn_summary      DS,
       FA_CATEGORY_BOOKS     FCB,
       FA_CATEGORIES         FC

 WHERE RSV.ASSET_ID = AD.ASSET_ID
   AND RSV.DH_CCID = CC.CODE_COMBINATION_ID
   AND g.PERIOD_COUNTER = RSV.PERIOD_COUNTER
   AND g.BOOK_TYPE_CODE = '&BOOK'
   AND DS.PERIOD_COUNTER(+) = RSV.PERIOD_COUNTER
   AND DS.BOOK_TYPE_CODE(+) = &Book
   AND DS.ASSET_ID(+) = RSV.ASSET_ID
   AND FCB.CATEGORY_ID = AD.ASSET_CATEGORY_ID
   AND FC.CATEGORY_ID = FCB.CATEGORY_ID
   AND FC.CATEGORY_ID = AD.ASSET_CATEGORY_ID
   AND FCB.BOOK_TYPE_CODE = '&Book'
   AND RSV.REQUEST_ID in
       (select max(s.request_id) from fa_reserve_ledger_itf s)

 GROUP BY FCB.ASSET_COST_ACCOUNT_CCID,
          FCB.ASSET_CLEARING_ACCOUNT_CCID,
          FCB.DEPRN_EXPENSE_ACCOUNT_CCID,
          FCB.RESERVE_ACCOUNT_CCID,
          AD.ASSET_ID,
          FC.SEGMENT1,
          fc.SEGMENT2,
          fc.SEGMENT3,
          AD.ASSET_NUMBER,
          AD.DESCRIPTION,
          DATE_PLACED_IN_SERVICE,
          METHOD_CODE,
          RSV.LIFE,
          RSV.RATE,
          RSV.CAPACITY,
          DS.BONUS_RATE,
          TRANSACTION_TYPE

 ORDER BY AD.ASSET_NUMBER

APP-OFA-47333: You Cannot Perform this transaction because the asset is fully retired. Choose a book in which you have not yet retired the asset, or reinstate the asset, and then perform the transaction. If you wish to cancel or undo the retirement, Please query it first.

https://fixedasstets.blogspot.com/2017/10/app-ofa-47333-you-cannot-perform-this.html

ERROR

APP-OFA-47333: You Cannot Perform this transaction because the asset is fully retired. Choose a book in which you have not yet retired the asset, or reinstate the asset, and then perform the transaction. If you wish to cancel or undo the retirement, Please query it first.

SOLUTION

Step 1. Navigate to Inquiry > Transaction History.

Step 2. Enter the Book & Asset Number.

Step 3. Press the Find button.

Step 4. Click the Transaction type field for the Retired Asset.

Step 5. Note the Reference Number.

Step 6. Go to the Asset Workbench and query the Asset.

Step 7. Click the Retirements button.

Step 8. As the Retirements screen is displayed:
             a. Press F11 to query or select Query>Enter on the menu bar.
             b. Enter the Reference Number noted above.
             c. Press CTRL F11 to perform the query or select Query>Run from the menu bar.

Step 9. As information appears on the screen, verify if it is correct and press the Reinstate button.

Step 10. Run the Calculate Gains and Losses program.

Asset Category Listing - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/09/asset-category-listing.html


The Below PLSQL Query is useful to get Asset Categories Accounts, Methods and Calendars attached to a Category

SELECT

 AD.ASSET_NUMBER,
 AD.DESCRIPTION,
 DATE_PLACED_IN_SERVICE,
 FC.SEGMENT1,
 fc.SEGMENT2,
 fc.SEGMENT3,

 (select GCC1.SEGMENT1 || '-' || GCC1.SEGMENT2 || '-' || GCC1.SEGMENT3 || '-' ||
         GCC1.SEGMENT4 || '-' || GCC1.SEGMENT5 || '-' || GCC1.SEGMENT6 || '-' ||
         GCC1.SEGMENT7 || '-' || GCC1.SEGMENT8
    from GL_CODE_COMBINATIONS gcc1
   where gcc1.CODE_COMBINATION_ID = FCB.ASSET_COST_ACCOUNT_CCID) CATG_ASSET_COST_ACCOUNT,

 (select GCC2.SEGMENT1 || '-' || GCC2.SEGMENT2 || '-' || GCC2.SEGMENT3 || '-' ||
         GCC2.SEGMENT4 || '-' || GCC2.SEGMENT5 || '-' || GCC2.SEGMENT6 || '-' ||
         GCC2.SEGMENT7 || '-' || GCC2.SEGMENT8
    from GL_CODE_COMBINATIONS gcc2
   where gcc2.CODE_COMBINATION_ID = FCB.ASSET_CLEARING_ACCOUNT_CCID) CATG_ASSET_CLEARING_ACCOUNT,

 (select GCC3.SEGMENT1 || '-' || GCC3.SEGMENT2 || '-' || GCC3.SEGMENT3 || '-' ||
         GCC3.SEGMENT4 || '-' || GCC3.SEGMENT5 || '-' || GCC3.SEGMENT6 || '-' ||
         GCC3.SEGMENT7 || '-' || GCC3.SEGMENT8
    from GL_CODE_COMBINATIONS gcc3
   where GCC3.CODE_COMBINATION_ID = FCB.DEPRN_EXPENSE_ACCOUNT_CCID) CATG_DEPRN_EXPENSE_ACCOUNT,

 (select GCC4.SEGMENT1 || '-' || GCC4.SEGMENT2 || '-' || GCC4.SEGMENT3 || '-' ||
         GCC4.SEGMENT4 || '-' || GCC4.SEGMENT5 || '-' || GCC4.SEGMENT6 || '-' ||
         GCC4.SEGMENT7 || '-' || GCC4.SEGMENT8
    from GL_CODE_COMBINATIONS gcc4
   where gcc4.CODE_COMBINATION_ID = FCB.RESERVE_ACCOUNT_CCID) CATG_DEPRN_RESERVE_ACCT,

 (select GCC5.SEGMENT1 || '-' || GCC5.SEGMENT2 || '-' || GCC5.SEGMENT3 || '-' ||
         GCC5.SEGMENT4 || '-' || GCC5.SEGMENT5 || '-' || GCC5.SEGMENT6 || '-' ||
         GCC5.SEGMENT7 || '-' || GCC5.SEGMENT8
    from FA_DISTRIBUTION_HISTORY dh, gl_code_combinations GCC5
   where dh.CODE_COMBINATION_ID = GCC5.CODE_COMBINATION_ID
     and dh.ASSET_ID = AD.ASSET_ID
     and dh.DATE_INEFFECTIVE is null
     and dh.BOOK_TYPE_CODE = &Book) DIST_DEPRN_EXPENSE_ACCOUNT,
 METHOD_CODE METHOD,
 RSV.LIFE LIFE,
 RSV.RATE ADJ_RATE,
 DS.BONUS_RATE,
 RSV.CAPACITY,
 SUM(decode(transaction_type, 'B', NULL, COST)) COST,
 SUM(RSV.DEPRN_AMOUNT) DEPRN_AMOUNT,
 SUM(RSV.YTD_DEPRN) YTD_DEPRN,
 SUM(RSV.DEPRN_RESERVE) DEPRN_RESERVE,
 SUM(decode(transaction_type, 'B', NULL, COST)) - SUM(RSV.DEPRN_RESERVE) NBV,
 sum(decode(transaction_type, 'B', NULL, nvl(PERCENT, 0))) PERCENT,
 TRANSACTION_TYPE T_TYPE

  FROM fa_reserve_ledger_itf RSV,
       fa_deprn_periods      g,
       FA_ADDITIONS          AD,
       GL_CODE_COMBINATIONS  CC,
       fa_deprn_summary      DS,
       FA_CATEGORY_BOOKS     FCB,
       FA_CATEGORIES         FC

 WHERE RSV.ASSET_ID = AD.ASSET_ID
   AND RSV.DH_CCID = CC.CODE_COMBINATION_ID
   AND g.PERIOD_COUNTER = RSV.PERIOD_COUNTER
   AND g.BOOK_TYPE_CODE = '&BOOK'
   AND DS.PERIOD_COUNTER(+) = RSV.PERIOD_COUNTER
   AND DS.BOOK_TYPE_CODE(+) = &Book
   AND DS.ASSET_ID(+) = RSV.ASSET_ID
   AND FCB.CATEGORY_ID = AD.ASSET_CATEGORY_ID
   AND FC.CATEGORY_ID = FCB.CATEGORY_ID
   AND FC.CATEGORY_ID = AD.ASSET_CATEGORY_ID
   AND FCB.BOOK_TYPE_CODE = '&Book'
   AND RSV.REQUEST_ID in
       (select max(s.request_id) from fa_reserve_ledger_itf s)

 GROUP BY FCB.ASSET_COST_ACCOUNT_CCID,
          FCB.ASSET_CLEARING_ACCOUNT_CCID,
          FCB.DEPRN_EXPENSE_ACCOUNT_CCID,
          FCB.RESERVE_ACCOUNT_CCID,
          AD.ASSET_ID,
          FC.SEGMENT1,
          fc.SEGMENT2,
          fc.SEGMENT3,
          AD.ASSET_NUMBER,
          AD.DESCRIPTION,
          DATE_PLACED_IN_SERVICE,
          METHOD_CODE,
          RSV.LIFE,
          RSV.RATE,
          RSV.CAPACITY,
          DS.BONUS_RATE,
          TRANSACTION_TYPE

 ORDER BY AD.ASSET_NUMBER