FA - Query to Get Fixed Asset Retirement Details

SELECT ret.retirement_id,

       ret.book_type_code,

       ret.asset_id,

       ret.transaction_header_id_in,

       ret.date_retired,

       ret.date_effective,

       ret.cost_retired,

       ret.status,

       ret.last_update_date,

       ret.last_updated_by,

       ret.retirement_prorate_convention,

       ret.transaction_header_id_out,

       ret.units,

       ret.cost_of_removal,

       ret.nbv_retired,

       ret.gain_loss_amount,

       ret.proceeds_of_sale,

       ret.gain_loss_type_code

  FROM fa_retirements ret,

       fa_additions_b ad,

       fa_books bks,

       fa_book_controls bc,

       fa_fiscal_year fy,

       fa_asset_history ah,

       fa_additions_b trade_in,

       fa_additions_tl adt,

       fa_transaction_headers th

 WHERE     ad.asset_id = ret.asset_id

       AND bks.book_type_code = ret.book_type_code

       AND bks.asset_id = ret.asset_id

       AND bks.transaction_header_id_out = ret.transaction_header_id_in

       AND bks.date_ineffective > ah.date_effective

       AND bks.date_ineffective <= NVL (ah.date_ineffective, SYSDATE)

       AND ah.asset_id = ret.asset_id

       AND trade_in.asset_id(+) = ret.trade_in_asset_id

       AND th.transaction_header_id = ret.transaction_header_id_in

       AND bc.book_type_code = ret.book_type_code

       AND fy.fiscal_year_name = bc.fiscal_year_name

       AND fy.fiscal_year = bc.current_fiscal_year

       AND adt.asset_id(+) = trade_in.asset_id

       AND adt.language(+) = USERENV ('LANG');