fixed assets tables in oracle apps r12

fixed assets tables in oracle apps r12

In this post , we will be discuss about fixed assets tables in oracle apps r12. Fixed  assets tables help to extract the fixed asset informations in oracle apps r12. These fixed assets tables do stores different type of fixes assets inflammations. Using these tables , We can develop the custom reports related to fixed assets modules. These Fixed assets table list , is one of the most important tables which generally do uses in the fixed asset module. Please find below the detail explanation of fixed assets tables in oracle apps r12.

12 Most Important fixed assets tables in oracle apps r12

1.fa_additions_b

2.fa_books

3.fa_transaction_headers

4.fa_categories_b

5.fa_asset_keywords

6.fa_methods

7.fa_asset_invoices

8.fa_convention_types

9.fa_distribution_history

10.fa_deprn_summary

11.fa_book_controls

12.gl_code_combinations

SQL Query using fixed assets tables in oracle apps r12

SELECT DISTINCT fab.asset_id interface_line_number

                , fb.book_type_code asset_book -- *

                , fth.transaction_name transaction_name

                , fab.asset_number asset_number

                , REPLACE (fatl.description, ',', ' ') asset_description --*

                , NULL tag_number

                , fab.manufacturer_name manufacturer

                , fab.serial_number serial_number

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

                , fab.asset_type asset_type

                , TO_CHAR (fb.cost, 'fm999999999.90') fb_cost -- *

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

                , fct.prorate_convention_code prorate_convention

                , fab.current_units asset_units -- *

                , 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') 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