Query to extract FA details based on the asset book

  SELECT
fp.period_name, 
adt.asset_number,
adt.tag_number,
bks.book_type_code,
ltrim(rtrim(cat.segment1)) ||'-'|| ltrim(rtrim(cat.segment2)) ||'-'|| ltrim(rtrim(cat.segment3)) category,
bks.date_placed_in_service,
bks.original_cost,
adt.description,
adt.context subject_to_property_tax,
adt.attribute1 property_tax_code,
dn.deprn_reserve,
nvl(bks.original_cost,0) - nvl(dn.deprn_reserve,0) net_book_value,
dn.ytd_deprn,
(select  dhcc.segment1||'.'||dhcc.segment2||'.'||dhcc.segment3||'.'||dhcc.segment4||'.'||dhcc.segment5||'.'||dhcc.segment6||'.'||dhcc.segment7||'.'||dhcc.segment8||'.'||dhcc.segment9   from gl_code_combinations dhcc,fa_distribution_history dh where dhcc.code_combination_id(+) = dh.code_combination_id and dh.book_type_code = :BOOK_NAME and adt.asset_id = dh.asset_id ) EXPENSE_ACCOUNT,
(select fl.segment1||'.'||fl.segment2||'.'||fl.segment3||'.'||fl.segment4||'.'||fl.segment5||'.'||fl.segment6||'.'||fl.segment7 from fa_locations fl,fa_distribution_history dh where  fl.location_id=dh.location_id and adt.asset_id = dh.asset_id) "Asset Location",
bks.life_in_months,
bks.prorate_convention_code,
bks.prorate_date,
bks.deprn_method_code,
bks.depreciate_flag,
dn.deprn_amount AS "MTD DEPRECIATION"
FROM

fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_books bks, 
fa_deprn_summary dn,
fa_deprn_periods fp

WHERE
fp.book_type_code =:BOOK_NAME
and dn.book_type_code =:BOOK_NAME
and dn.period_counter =
( select dp.period_counter from fa_deprn_periods dp where dp.book_type_code =:BOOK_NAME
and dp.period_counter =
( select max(dpz.period_counter) from fa_deprn_summary dsz, fa_deprn_periods dpz
where dpz.book_type_code =:BOOK_NAME
and dpz.period_counter <=fp.period_counter
and dsz.book_type_code =:BOOK_NAME
and dsz.period_counter = dpz.period_counter
and dsz.asset_id = dn.asset_id ))
and bks.book_type_code =:BOOK_NAME
and bks.asset_id = dn.asset_id 
and nvl(bks.period_counter_fully_retired, fp.period_counter) in
( select dpy.period_counter
from fa_deprn_periods dpy
where dpy.book_type_code =:BOOK_NAME
and dpy.fiscal_year = fp.fiscal_year)
and adt.asset_id = dn.asset_id
and adt.ASSET_CATEGORY_ID = cat.category_id 
and fp.period_name =:PERIOD_NAME
and ah.asset_id = adt.asset_id
and bks.transaction_header_id_out is null
                           
GROUP BY
fp.period_name,
adt.asset_number,
adt.tag_number,
ltrim(rtrim(cat.segment1))||'-'||ltrim(rtrim(cat.segment2))||'-'||ltrim(rtrim(cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost, 
adt.context,
adt.attribute1,
dn.ytd_deprn,
dn.deprn_reserve,
bks.life_in_months,
bks.life_in_months,
bks.prorate_convention_code,
bks.depreciate_flag,
bks.deprn_method_code,
bks.prorate_date,
dn.deprn_amount,
bks.book_type_code,
adt.asset_id;