Asset Category table in Oracle Apps

Asset Category table in Oracle Apps

Hi friends, We are going to discuss about the Asset Category table in Oracle Apps. We will share the Fixed asset category tables which helps to store the assets category informations in oracle apps. We will also share some other important fixed asset tables too along with category which helps to store other assets details. Using these Asset category tables we can develop the asset sql queries which helps to extract the asset category details in oracle apps. As we have many asset related tables in oracle apps but we will some of the important tables which helps to store most important informations for fixed assets. These are the asset tables which we commonly used in the oracle apps working. Please find below the complete detail about Asset Category table in Oracle Apps.

Asset Category table in Oracle Apps

10 Most Important Asset table in Oracle Apps

Here below we are sharing some of the important tables about Fixed Assets in oracle apps.

1.fa_asset_invoices

2.fa_transaction_headers

3.fa_categories_b

4.fa_additions_b

5.fa_books

6.fa_asset_keywords

7.fa_convention_types

8.fa_distribution_history

9.fa_deprn_summary

10.fa_book_controls

SQL Query to extract the Asset Category details in Oracle Apps

Here below , we are sharing some of the important sql query which helps to extract the complete asset details including the asset category details in oracle apps. You can refer this sql query to find about the which asset category assigned to which asset in oracle apps. Including Asset Category , You will also get the Asset Invoice amount & and asset total cost and any other critical information of assets in oracle apps.

SELECT DISTINCT fab.asset_id Asset_id

                , fb.book_type_code Asset_Category_book

                , fth.transaction_name Asset_transaction_name

                , fab.asset_number FA_Asset_number

                , REPLACE (fatl.description, ',', ' ') Description 

                , NULL tag_number

                , fab.manufacturer_name Asset_Manufacturer

                , fab.serial_number Asset_serial_number

                , REPLACE (fab.model_number, ',', '') Asset_model

                , fab.asset_type asset_type

                , TO_CHAR (fb.cost, 'fm999999999.90') Asset_cost 

                , TO_CHAR (fb.date_placed_in_service, 'RRRR/MM/DD') Asset_date_service

                , fct.prorate_convention_code prorate_convention

                , fab.current_units asset_qty

                , fcb.segment1 asset_category_segment1

                , fcb.segment2 asset_category_segment2

                , fcb.segment3 asset_category_segment3

                , fcb.segment4 asset_category_segment4

                , fcb.segment5 asset_category_segment5

                , fcb.segment6 asset_category_segment6

                , fcb.segment7 asset_category_segment7

                , NULL posting_status -- CHECK

                , NULL queue_name

                , fai.feeder_system_name feeder_system

                , (SELECT fab1.asset_number

                     FROM apps.fa_additions_b fab1

                    WHERE asset_id = fab.parent_asset_id)

                     parent_asset

                , fak.segment1 asset_key_segment1

                , fab.property_type_code property_type

                , fab.property_1245_1250_code property_class

                , TO_CHAR (fds.ytd_deprn, 'fm999999999.90') ytd_depreciation

                , TO_CHAR (fds.deprn_reserve, 'fm999999999.90') depreciation_reserve

                , fm.method_code depreciation_method

                , fm.life_in_months life_in_months

                , TO_CHAR (SUM (fai.payables_cost), 'fm9999999.90') Asset_invoice_cost

    FROM apps.fa_additions_b fab

       , (SELECT * FROM apps.fa_additions_tl WHERE language = 'US') fatl

       , apps.fa_books fb

       , apps.fa_transaction_headers fth

       , apps.fa_categories_b fcb

       , apps.fa_asset_keywords fak

       , apps.fa_methods fm

       , apps.fa_asset_invoices fai

       , apps.fa_convention_types fct

       , apps.fa_distribution_history fdh

       --   , apps.fa_deprn_summary fds

       , (SELECT t.* FROM (SELECT fds.* , RANK () OVER (PARTITION BY fds.asset_id ORDER BY fds.deprn_run_date DESC)  latest  FROM apps.fa_deprn_summary fds) t WHERE t.latest = 1) fds

       , apps.fa_book_controls fbc

       , apps.gl_code_combinations gcc

       , apps.gl_ledgers gl

   WHERE     1 = 1

         AND fb.asset_id NOT IN (SELECT v.asset_id

                                   FROM apps.fa_transaction_history_trx_v v

                                  WHERE v.transaction_type_code = 'FULL RETIREMENT')

         AND fab.asset_category_id = fcb.category_id

         AND fab.asset_id = fatl.asset_id

         AND fb.asset_id = fatl.asset_id

         AND fab.asset_id = fb.asset_id

         AND fb.asset_id = fth.asset_id

         AND fb.BOOK_TYPE_CODE =:P_BOOK_TYPE_CODE

         AND fb.book_type_code = fth.book_type_code

         AND fbc.book_type_code = fth.book_type_code

         AND fb.transaction_header_id_in = fth.transaction_header_id

         AND fab.asset_key_ccid = fak.code_combination_id(+)

         AND fb.deprn_method_code = fm.method_code(+)

         AND fb.life_in_months = fm.life_in_months(+)

         AND fb.asset_id = fai.asset_id(+) --fab

         AND fb.prorate_convention_code = fct.prorate_convention_code

         AND fb.asset_id = fdh.asset_id(+) -- fab

         AND fb.book_type_code = fdh.book_type_code(+)

         AND fdh.code_combination_id = gcc.code_combination_id(+)

         AND fab.asset_id = fds.asset_id

         AND fb.asset_id = fds.asset_id

         AND fb.book_type_code = fds.book_type_code(+)

         AND fb.book_type_code = fbc.book_type_code

         AND fbc.set_of_books_id = gl.ledger_id

         AND fb.date_ineffective IS NULL

         AND fai.date_ineffective IS NULL

         AND fdh.asset_id = fab.asset_id

         AND fdh.retirement_id IS NULL

GROUP BY fab.asset_id

       , fb.book_type_code

       , fth.transaction_name

       , fab.asset_number

       , fatl.description

       , fab.manufacturer_name

       , fab.serial_number

       , fab.model_number

       , fab.asset_type

       , fb.cost

       , fb.date_placed_in_service

       , fct.prorate_convention_code

       , fab.current_units

       , fcb.segment1

       , fcb.segment2

       , fcb.segment3

       , fcb.segment4

       , fcb.segment5

       , fcb.segment6

       , fcb.segment7

       , fai.feeder_system_name

       , fak.segment1

       , fak.segment2

       , fak.segment3

       , fak.segment4

       , fak.segment5

       , fak.segment6

       , fak.segment7

       , fak.segment8

       , fak.segment9

       , fak.segment10

       , fab.property_type_code

       , fab.property_1245_1250_code

       , fab.owned_leased

       , fb.depreciate_flag

       , fm.method_code

       , fm.life_in_months

       , gcc.segment1

       , gcc.segment2

       , gcc.segment3

       , fb.original_deprn_start_date

       , fab.parent_asset_id

       , gl.currency_code

       , fds.ytd_deprn

       , fds.deprn_reserve

Asset Category table in Oracle Apps