https://fixedasstets.blogspot.com/2024/11/asset-categories-with-accounts-sql.html
select a.segment1 , a.segment2, b.book_type_code,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.asset_cost_account_ccid) Asset_Cost_Account,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.asset_clearing_account_ccid) Asset_clearing_Account,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.deprn_expense_account_ccid) Depreciation_expense,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.reserve_account_ccid) Accumulated_depreciation,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.bonus_expense_account_ccid) Bonus_expense,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.bonus_reserve_acct_ccid) bonus_reserve,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.wip_cost_account_ccid) cip_cost,
(select c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7
from gl_code_combinations c where c.code_combination_id = b.wip_clearing_account_ccid) cip_clearing,
d.depreciate_flag, d.deprn_method , d.life_in_months , d.prorate_convention_code , d.retirement_prorate_convention, d.percent_salvage_value, d.capital_gain_threshold, d.recognize_gain_loss , d.terminal_gain_loss
from FA_CATEGORIES_VL a, FA_CATEGORY_BOOKS b , FA_CATEGORY_BOOK_DEFAULTS d
where a.category_id = b.category_id
and b.category_id = d.category_id
and b.book_type_code= d.book_type_code
--and b.book_type_code = ''
--and a.segment1 = 'FURNITURE & FIXTURE'
