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