R12 Assets Query

 SELECT DISTINCT fab.asset_id ASSET_ID

                , 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

         AND FAB.ASSET_ID IN (70,112          )

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

ORDER BY 1