Query to get Asset Details in Oracle Fusion

SELECT asset_number

      ,fab.asset_id

      ,fat.description

      ,fab.asset_type

      ,fcb.segment1||'-'||fcb.segment2 category

      ,fab.tag_number

      ,fb.book_type_code

      ,fabc.book_class

      ,fb.cost

      ,fb.recoverable_cost

      ,(SELECT SUM(deprn_reserve)

  FROM fa_deprn_detail

         WHERE asset_id = fab.asset_id

   AND deprn_run_date = (SELECT MAX(deprn_run_date)

   FROM fa_deprn_detail

  WHERE asset_id = fab.asset_id

) depriciation_cost

  FROM fa_additions_b fab

      ,fa_additions_tl fat

      ,fa_categories_b fcb

      ,fa_books fb

      ,fa_book_controls fabc

 WHERE 1=1

   AND fab.asset_id = fat.asset_id 

   AND fat.language = USERENV('LANG')

   AND fcb.category_id=fab.asset_category_id

   AND fb.asset_id=fab.asset_id

   AND fb.depreciate_flag = 'NO'

   AND fab.asset_number = 123456789

Query to get Asset Categories with GL Codes in Oracle APPS

SELECT fcb.book_type_code

       ,fct.segment1||'.'||fct.segment2 category

       ,fct.enabled_flag enabled

       ,fct.description

       ,fct.capitalize_flag  capitalize

       ,flt.meaning category_type

       ,fct.inventorial physical_inventory

       ,flt1.meaning ownership 

       ,fct.property_type_code property_type

       ,gcck.concatenated_segments asset_cost

       ,gcck1.concatenated_segments asset_clearing

       ,gcck2.concatenated_segments depreciation_expense

       ,gcck3.concatenated_segments accumulated_depreciation

       ,gcck4.concatenated_segments bonus_expense

       ,gcck5.concatenated_segments bonus_reserve

       ,gcck6.concatenated_segments revaluation_reserve

       ,gcck7.concatenated_segments revaluation_amortization

       ,gcck8.concatenated_segments cip_cost

       ,gcck9.concatenated_segments cip_clearing

       ,gcck10.concatenated_segments impairment_expense

       ,gcck11.concatenated_segments accumulated_impairment

       ,gcck12.concatenated_segments unplanned_depreciatn_exp

       ,gcck13.concatenated_segments alternate_asset_cost

       ,gcck14.concatenated_segments write_off_expense

       ,fcbd.start_dpis placed_in_service_from

       ,fcbd.end_dpis placed_in_service_to

       ,fcbd.depreciate_flag depreciate

       ,fcbd.deprn_method 

       ,fcbd.life_in_months

       ,FLOOR(fcbd.life_in_months/12) life_in_years

       ,fcbd.bonus_rule

       ,fcbd.prorate_convention_code

       ,fcbd.retirement_prorate_convention

       ,fcbd.percent_salvage_value

       ,fcbd.ceiling_name

       ,fcbd.price_index_name

       ,fcbd.subcomponent_life_rule

       ,fcbd.minimum_life_in_months

       ,FLOOR(fcbd.minimum_life_in_months/12) minimum_life_in_years

       ,fcbd.use_stl_retirements_flag

       ,fcbd.stl_method_code 

       ,FLOOR(fcbd.stl_life_in_months/12) slr_life_years

       ,fcbd.use_deprn_limits_flag

       ,fcbd.allowed_deprn_limit

       ,fcbd.special_deprn_limit_amount

       ,FLOOR(fcbd.capital_gain_threshold/12) cgt_in_years

       ,MOD(fcbd.capital_gain_threshold,12) cgt_in_months

       ,fcbd.itc_eligible_flag       

       ,fcbd.use_itc_ceilings_flag

       ,fcbd.mass_property_flag

       ,fcbd.group_asset_id group_asset

       ,fcbd.recognize_gain_loss

       ,fcbd.terminal_gain_loss

       ,fcbd.recapture_reserve_flag 

       ,fcbd.limit_proceeds_flag

       ,fcbd.tracking_method

       ,fcbd.allocate_to_fully_rsv_flag

       ,fcbd.excess_allocation_option distribute_or_reduce_excess

   FROM apps.fa_categories_vl fct

       ,apps.fa_lookups_tl flt

       ,apps.fa_lookups_tl flt1

       ,apps.fa_category_books fcb

       ,apps.gl_code_combinations_kfv gcck

       ,apps.gl_code_combinations_kfv gcck1

       ,apps.gl_code_combinations_kfv gcck2

       ,apps.gl_code_combinations_kfv gcck3

       ,apps.gl_code_combinations_kfv gcck4

       ,apps.gl_code_combinations_kfv gcck5

       ,apps.gl_code_combinations_kfv gcck6

       ,apps.gl_code_combinations_kfv gcck7

       ,apps.gl_code_combinations_kfv gcck8

       ,apps.gl_code_combinations_kfv gcck9

       ,apps.gl_code_combinations_kfv gcck10

       ,apps.gl_code_combinations_kfv gcck11

       ,apps.gl_code_combinations_kfv gcck12

       ,apps.gl_code_combinations_kfv gcck13

       ,apps.gl_code_combinations_kfv gcck14

       ,apps.fa_category_book_defaults fcbd

  WHERE 1=1

    AND fct.category_type = flt.lookup_code(+)

    AND flt.lookup_type(+) = 'CATEGORY TYPE'

    AND flt1.lookup_code(+) = fct.owned_leased

    AND flt1.lookup_type(+) ='OWNLEASE'

    AND fct.category_id = fcb.category_id

    AND gcck.code_combination_id(+) = fcb.asset_cost_account_ccid

    AND gcck1.code_combination_id(+) = fcb.asset_clearing_account_ccid

    AND gcck2.code_combination_id(+) = fcb.deprn_expense_account_ccid

    AND gcck3.code_combination_id(+) = fcb.reserve_account_ccid

    AND gcck4.code_combination_id(+) = fcb.bonus_expense_account_ccid

    AND gcck5.code_combination_id(+) = fcb.bonus_reserve_acct_ccid

    AND gcck6.code_combination_id(+) = fcb.reval_reserve_account_ccid

    AND gcck7.code_combination_id(+) = fcb.reval_amort_account_ccid

    AND gcck8.code_combination_id(+) = fcb.wip_cost_account_ccid

    AND gcck9.code_combination_id(+) = fcb.wip_clearing_account_ccid

    AND gcck10.code_combination_id(+) = fcb.impair_expense_account_ccid

    AND gcck11.code_combination_id(+) = fcb.impair_reserve_account_ccid

    AND gcck12.code_combination_id(+) = fcb.unplan_expense_account_ccid

    AND gcck13.code_combination_id(+) = fcb.alt_cost_account_ccid

    AND gcck14.code_combination_id(+) = fcb.write_off_account_ccid

    AND fcb.category_id = fcbd.category_id

    AND fct.category_id = fcb.category_id

    AND fcb.book_type_code = fcbd.book_type_code

    AND fcb.book_type_code = :p_book_type_code

    

Asset Retirements in Oracle APPS

• Retire an asset when it's no longer in service, for example, if it was stolen, lost, damaged, sold, or returned.
• When retiring assets you can retire an asset completely if the entire asset is no longer in service. You can retire part of an asset if only a portion of the asset is no longer in service.

Asset Retirement Types in Oracle APPS

Type	Description	Rules
Unit retirement	Retire assets by unit, either all units or some units of a multiple-unit asset. The cost retired is automatically calculated for each unit retired.	Not allowed in tax books.
Cost retirement	Retire assets by cost. The units remain unchanged and the retired cost is spread evenly among the units.	Allowed in both corporate and tax books.
Source line retirement	Retire an asset that was imported as a source line by retiring the asset cost based on the source line.	Allowed for both partial and full retirements.

 

Asset Retirement Methods in Oracle APPS

Method	Description	Rules
Full retirement	Retire all the units of a multiple-unit asset Retire the entire asset cost	Full cost retirements: allowed for CIP assets. Full unit retirements: not allowed for CIP assets.
Partial retirement	Retire a specified number of units of a multiple-unit asset Retire a portion of the asset cost.	Partial cost retirements: the units remain unchanged and the retired cost is spread evenly among all assignment lines. Partial unit retirements: Oracle Fusion Assets automatically calculates the retired cost. Partial retirements of CIP assets: not allowed.

 

Asset Queue Status in Oracle APPS

Queue Name	Definition
New	New mass addition line created but not yet reviewed.
On Hold or user-defined hold queue	Mass addition line updated or put on hold.
Split	Mass addition line already split into multiple lines.
Merged	Mass addition line already merged into another line.
Cost Adjustment	Mass addition line to be added to an existing asset; ready for posting.
Post	Mass addition line ready to become an asset.
Posted	Mass addition line already posted.
Delete	Mass addition line to be deleted.