SQL query to get asset categories details in oracle apps r12

SQL query to get asset categories details in oracle apps r12

Hi friends, we are going to discuss about SQL query to get asset categories details in oracle apps r12. We will share the complete sql query which helps to extract the complete asset categories details in oracle apps. Using this query ,we will be able to extract the values for different asset category segments in oracle apps. This is one of the important sql query related to fixed assets in oracle apps. Using this query we can able to develop the custom fixed asset reports in oracle apps which can extract the details related to fixed assets categories. Please find below the complete detail about SQL query to get asset categories details in oracle apps r12.

SQL query to get asset categories details in oracle apps r12

5 Important Tables about asset categories in oracle apps r12

1.fa_categories_vl

2.fa_lookups_tl

3.fa_category_books

4.gl_code_combinations_kfv

5.fa_category_book_defaults

Detail SQL query to get asset categories details in oracle apps r12

Here below is the detail sql query which helps to extract the complete informations about fixed asset categories in oracle apps.

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

       ,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 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 fcb.book_type_code='EMR CORPORATE'

    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

SQL query to get asset categories details in oracle apps r12