Oracle r12 Fixed Assets Queries

Oracle r12 Fixed Assets Queries

Hi Friends, We are going to discuss about Oracle r12 Fixed Assets Queries. We will share the important fixed assets queries which helps to extract the fixed assets data. We will share the different types of fixed assets queries which will be extract the fixed assets transaction data and others parts of fixed assets informations in oracle. Using these fixed assets queries , we can develop the custom oracle r12 FA related reports. We are also sharing the important tables related to these fixed assets queries. Here below is the detail about Oracle r12 Fixed Assets Queries.

Oracle r12 Fixed Assets Queries

15 Important table in Oracle r12 Fixed Assets Queries

4 Fixed Assets Depreciation Tables 

1.fa_deprn_detail

2.fa_deprn_summary

3.fa_deprn_periods

4.fa_additions

11 General Fixed Asset Details Tables

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_book_controls

11.gl_code_combination

2 Fixed Assets Retirement Detail Tables.

1.fa_retirements

2.fa_lookups_tl

3 Important Oracle r12 Fixed Assets Queries

Fixed Assets Query for Depreciation

SELECT fa.asset_number,

       fa.asset_id,

       fdd.period_counter,

       fdp.period_name,

       fdp.fiscal_year,

       fdp.period_num,

       fdd.book_type_code,

       fdd.distribution_id,

       fdd.deprn_run_date,

       fdd.deprn_amount,

       fdd.ytd_deprn,

       fdd.deprn_reserve,

       fdd.cost,

       fdd.deprn_adjustment_amount

  FROM fa_additions       fa,

       fa_deprn_periods   fdp,

       fa.fa_deprn_detail fdd

 WHERE fa.asset_id = fdd.asset_id

   AND fdp.period_counter = fdd.period_counter

   AND fdp.book_type_code = fdd.book_type_code

   AND fa.asset_number = :P_ASSET_NO

   AND fdp.period_name = :P_PERIOD_NAME

Fixed Assets Query to Extract Assets Details

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

Fixed Asset Query for retired assets.

SELECT fa_add.asset_number,

       fa_add.tag_number,

       fa_add.description,

       fa_retrmnt.retirement_prorate_convention,

       fa_retrmnt.cost_of_removal,

       fa_retrmnt.nbv_retired,

       fa_retrmnt.proceeds_of_sale

       fa_retrmnt.retirement_type_code,

       flb.description retirement_type_description,

       fa_retrmnt.book_type_code,

       fa_retrmnt.date_retired,

       fa_retrmnt.date_effective,

       fa_retrmnt.cost_retired,

       fa_retrmnt.status

  FROM       fa_retirements fa_retrmnt,

fa_additions   fa_add_add,

       fa_lookups_tl  flb

 WHERE fa_retrmnt.asset_id = fa_add.asset_id

   AND fa_add.asset_number =:P_ASSET_NO

   AND flb.lookup_code = fa_retrmnt.retirement_type_code

   AND flb.lookup_type = 'RETIREMENT'

Oracle r12 Fixed Assets Queries