Query to Get List of Asset Books by Operating Unit

SELECT hou.name "Operating Unit" ,

  fbc.book_type_code "Book Code" ,

  fbc.book_type_name "Book Name" ,

  fbc.book_class "Class" ,

  (SELECT COUNT(1)

  FROM fa_additions fma ,

    fa_books bks

  WHERE fma.asset_id        = bks.asset_id

  AND bks.book_type_code    = fbc.book_type_code

  AND bks.date_ineffective IS NULL

  ) "Assets" ,

  fbc.last_deprn_run_date "Last Depreciation Run"

FROM gl_code_combinations_kfv gcc ,

  fa_book_controls fbc ,

  gl_sets_of_books sob ,

  fnd_id_flex_structures_tl ifs ,

  fa_deprn_periods fdp ,

  hr_all_organization_units hou

WHERE fbc.set_of_books_id         = sob.set_of_books_id

AND fbc.accounting_flex_structure = ifs.id_flex_num

AND ifs.id_flex_code              = 'GL#'

AND fbc.org_id                    = hou.organization_id (+)

AND fbc.last_period_counter       = fdp.period_counter (+)

AND fbc.book_type_code            = fdp.book_type_code (+)

AND fbc.flexbuilder_defaults_ccid = gcc.code_combination_id (+)

ORDER BY 1,2