FA : Query to get Retired Assets

Full retirement asset

select asset_id,
a.book_type_code
from fa_transaction_headers a
where transaction_type_code = 'FULL RETIREMENT'
and not exists (select 1 from
                fa_transaction_headers b
                where transaction_type_code = 'REINSTATEMENT'
                and b.transaction_header_id > a.transaction_header_id
                and a.asset_id =  b.asset_id
                and a.book_type_code = b.book_type_code)

partial retired assets

 select a.asset_id,
a.book_type_code
from fa_transaction_headers a,
fa_retirements b
where transaction_type_code = 'PARTIAL RETIREMENT'
and   a.transaction_header_id = b.transaction_header_id_in
and b.status <> 'DELETE'

both partial and fully retired assets

select a.asset_id,
a.book_type_code
from fa_transaction_headers a,
fa_retirements b
where transaction_type_code in ('PARTIAL RETIREMENT',FULL RETIREMENT)
and   a.transaction_header_id = b.transaction_header_id_in
and b.status <> 'DELETE'