Query to find retired assets in oracle apps

Query to find retired assets in oracle apps

In this post , we will be discuss about the sql query to find the retired assets in oracle apps. Retired asset is the process in oracle apps , when the asset is no longer use in the organization or we can say asset is disposed or we have sold this asset to some other supplier or organization so in the system we did the retirement of that asset to show in our reports that this asset is no longer using in our organization. In retired asset retirement process , We do mentioned all informations , to find the actual gain or loss after selling this  asset to some organization.I will share the detail query which helps to extract the complete informations about the retired assets in oracle apps. We will get the list of retired asset using this query. 

Important Tables Using in the Query to find retired assets in oracle apps

1.fa_additions

2.fa_retirements

3.fa_lookups_tl

Detail SQL query to find the retired assets in oracle apps

Here below is the sql query which will extract the list of all retired assets in oracle apps. we will get all the informations about asset retirements in oracle apps like date of retirement , Cost Retired , cost of removal and Retirement Status and many more.

SELECT fa_add.asset_number,

       fa_add.tag_number,

       fa_add.description,

       fa_retrmnt.retirement_prorate_convention,

       fa_retrmnt.cost_of_removal,

       fa_retrmnt.nbv_retired,

       fa_retrmnt.proceeds_of_sale

       fa_retrmnt.retirement_type_code,

       flb.description retirement_type_description,

       fa_retrmnt.book_type_code,

       fa_retrmnt.date_retired,

       fa_retrmnt.date_effective,

       fa_retrmnt.cost_retired,

       fa_retrmnt.status

  FROM       fa_retirements fa_retrmnt,

fa_additions   fa_add_add,

       fa_lookups_tl  flb

 WHERE fa_retrmnt.asset_id = fa_add.asset_id

   AND fa_add.asset_number =:P_ASSET_NO

   AND flb.lookup_code = fa_retrmnt.retirement_type_code

   AND flb.lookup_type = 'RETIREMENT'