1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query To Display Fixed Assets Categories List

Discussion in 'Oracle Apps Technical' started by jagadekara, Sep 13, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Navigation
    -----------

    Fixed Assets Manager Resp-->Setup-->Asset System-->Asset categories

    Code (SQL):
    --Asset Category Listing
    SELECT fct.segment1||'.'||fct.segment2||'.'||fct.segment3||'.'||fct.segment4||'.'||fct.segment5 category
          ,fct.enabled_flag enabled
          ,fct.description
          ,decode(fct.capitalize_flag,'YES','Y','N') capitalize
          ,flt.meaning category_type
          ,decode(fct.inventorial,'YES','Y','N') in_physical_inventory
          ,flt1.meaning ownership
          ,fct.property_type_code property_type
          ,fct.property_1245_1250_code property_class
          ,fcb.book_type_code book
          ,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
          ,decode(fcbd.depreciate_flag,'YES','Y','N') depreciate
          ,fcbd.deprn_method method
          ,FLOOR(fcbd.life_in_months/12) life_years
          ,(CASE WHEN MOD(fcbd.life_in_months,12)!=0 THEN MOD(fcbd.life_in_months,12) END) months
          ,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
          ,FLOOR(fcbd.minimum_life_in_months/12) minimum_years
          ,(CASE WHEN MOD(fcbd.minimum_life_in_months,12)!=0 THEN MOD(fcbd.minimum_life_in_months,12) END) subcomponent_months
          ,fcbd.use_stl_retirements_flag
          ,fcbd.stl_method_code
          ,FLOOR(fcbd.stl_life_in_months/12) slr_life_years
          ,(CASE WHEN MOD(fcbd.stl_life_in_months,12)!=0 THEN MOD(fcbd.stl_life_in_months,12) END) slr_months
          ,decode(fcbd.use_deprn_limits_flag,'YES','Y','NO','N','') use_depreciation_limit
          ,fcbd.allowed_deprn_limit UDL_percent
          ,fcbd.special_deprn_limit_amount UDL_limit
          ,FLOOR(fcbd.capital_gain_threshold/12) CGT_years
          ,MOD(fcbd.capital_gain_threshold,12) CGT_months
          ,decode(fcbd.itc_eligible_flag,'YES','Y','NO','N','') itc_eligible
          ,decode(fcbd.use_itc_ceilings_flag,'YES','Y','NO','N','') use_itc_ceilings
          ,fcbd.mass_property_flag mass_property_eligible
          ,fcbd.group_asset_id group_asset
          ,fcbd.recognize_gain_loss
          ,fcbd.terminal_gain_loss
          ,fcbd.recapture_reserve_flag recapture_excess_reserve
          ,fcbd.limit_proceeds_flag limit_net_proceeds_to_cost
          ,fcbd.tracking_method
          ,fcbd.allocate_to_fully_rsv_flag allcat_2_ful_rtrd_n_rsrvd_asts
          ,fcbd.excess_allocation_option distribute_or_reduce_excess
      FROM fa_categories_vl fct
          ,fa_lookups_tl flt
          ,fa_lookups_tl flt1
          ,fa_category_books fcb
          ,gl_code_combinations_kfv gcck
          ,gl_code_combinations_kfv gcck1
          ,gl_code_combinations_kfv gcck2
          ,gl_code_combinations_kfv gcck3
          ,gl_code_combinations_kfv gcck4
          ,gl_code_combinations_kfv gcck5
          ,gl_code_combinations_kfv gcck6
          ,gl_code_combinations_kfv gcck7
          ,gl_code_combinations_kfv gcck8
          ,gl_code_combinations_kfv gcck9
          ,gl_code_combinations_kfv gcck10
          ,gl_code_combinations_kfv gcck11
          ,gl_code_combinations_kfv gcck12
          ,gl_code_combinations_kfv gcck13
          ,gl_code_combinations_kfv gcck14
          ,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
    ;