FA : Query to fetch Asset Books - Cost History

Here is query to get the Cost History fotr particular asset
select
    fb.book_type_code               "Book",
    fa.asset_number                 "Asset Number",
    fb.transaction_header_id_in     "Reference Number",
    fth.transaction_type_code       "Transaction Type",
    fth.transaction_date_entered    "Transaction Date",
    fth.date_effective              "Date Effective",
    fb.cost                         "Cost",
   fa.asset_id
from
    fa_additions_b fa,
    fa_books fb,
    fa_transaction_headers fth
where
    1=1
and fb.asset_id = fa.asset_id
and fth.transaction_header_id (+) = fb.transaction_header_id_in
and fth.asset_id (+) = fb.asset_id
and fa.asset_number ='asset number'
order by
    1,2, fb.transaction_header_id_in desc

Read More

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'

Read More