Query to get Retired Fixed Assets

SELECT ret.ROWID ret_rowid,
          ret.retirement_id,
          ret.book_type_code,
          ret.asset_id,
          ret.transaction_header_id_in,
          ret.date_retired,
          ret.date_effective,
          ret.cost_retired,
          ret.status,
          ret.last_update_date,
          ret.last_updated_by,
          ret.retirement_prorate_convention,
          ret.transaction_header_id_out,
          ret.units,
          ret.cost_of_removal,
          ret.nbv_retired,
          ret.gain_loss_amount,
          ret.proceeds_of_sale,
          ret.gain_loss_type_code,
          ret.retirement_type_code,
          ret.itc_recaptured,
          ret.itc_recapture_id,
          ret.reference_num,
          ret.sold_to,
          ret.trade_in_asset_id,
          ret.stl_method_code,
          ret.stl_life_in_months,
          ret.stl_deprn_amount,
          ret.created_by,
          ret.creation_date,
          ret.last_update_login,
          ret.attribute1 ret_attribute1,
          ret.attribute2 ret_attribute2,
          ret.attribute3 ret_attribute3,
          ret.attribute4 ret_attribute4,
          ret.attribute5 ret_attribute5,
          ret.attribute6 ret_attribute6,
          ret.attribute7 ret_attribute7,
          ret.attribute8 ret_attribute8,
          ret.attribute9 ret_attribute9,
          ret.attribute10 ret_attribute10,
          ret.attribute11 ret_attribute11,
          ret.attribute12 ret_attribute12,
          ret.attribute13 ret_attribute13,
          ret.attribute14 ret_attribute14,
          ret.attribute15 ret_attribute15,
          ret.attribute_category_code ret_attribute_category_code,
          ret.reval_reserve_retired,
          ret.unrevalued_cost_retired,
          ad.asset_number asset_number,
          bks.cost cost,
          ah.units current_units,
          trade_in.asset_number trade_in_asset_number,
          adt.description trade_in_asset_desc,
          th.transaction_name,
          th.attribute1,
          th.attribute2,
          th.attribute3,
          th.attribute4,
          th.attribute5,
          th.attribute6,
          th.attribute7,
          th.attribute8,
          th.attribute9,
          th.attribute10,
          th.attribute11,
          th.attribute12,
          th.attribute13,
          th.attribute14,
          th.attribute15,
          th.attribute_category_code,
          bc.current_fiscal_year,
          bc.fiscal_year_name,
          fy.start_date fy_start_date,
          fy.end_date fy_end_date,
          th.invoice_transaction_id,
          bks.group_asset_id group_asset_id,
          ret.recognize_gain_loss recognize_gain_loss,
          ret.recapture_reserve_flag recapture_reserve_flag,
          ret.limit_proceeds_flag limit_proceeds_flag,
          ret.terminal_gain_loss terminal_gain_loss,
          ret.reduction_rate reduction_rate,
          ret.eofy_reserve eofy_reserve,
          ret.reserve_retired reserve_retired,
          ret.recapture_amount recapture_amount,
          th.date_effective transaction_date_effective,fc.segment1 CATEGORY,ad.ASSET_CATEGORY_ID  asset_category_id
     FROM fa_retirements ret,
          fa_additions_b ad, fa_categories fc,
          fa_books bks,
          fa_book_controls bc,
          fa_fiscal_year fy,
          fa_asset_history ah,
          fa_additions_b trade_in,
          fa_additions_tl adt,
          fa_transaction_headers th
    WHERE ad.asset_id = ret.asset_id
        AND  ad.ASSET_CATEGORY_ID=fc.category_id
      AND bks.book_type_code = ret.book_type_code
      AND bks.asset_id = ret.asset_id
      AND bks.transaction_header_id_out = ret.transaction_header_id_in
      AND bks.date_ineffective > ah.date_effective
      AND bks.date_ineffective <= NVL (ah.date_ineffective, SYSDATE)
      AND ah.asset_id = ret.asset_id
      AND trade_in.asset_id(+) = ret.trade_in_asset_id
      AND th.transaction_header_id = ret.transaction_header_id_in
      AND bc.book_type_code = ret.book_type_code
      AND fy.fiscal_year_name = bc.fiscal_year_name
      AND fy.fiscal_year = bc.current_fiscal_year
      AND adt.asset_id(+) = trade_in.asset_id
      AND adt.language(+) = USERENV ('LANG') 

Query to get reclassified Fixed Asset

SELECT                                                                              --ACCT_FLEX_BAL_SEG              COMP_CODE,
           th.book_type_code,
            dhcc.segment1 comp_code,
            DECODE (fah.asset_type, 'CIP', fcb.cip_cost_acct, fcb.asset_cost_acct) fr_as_account,
            DECODE (fah.asset_type, 'CIP', ' ', fcb.deprn_reserve_acct) fr_re_account,
            ---CAT_FLEX_ALL_SEG_FR                 FR_CATEGORY,
            fcat.segment1 fr_category,
            DECODE (tah.asset_type, 'CIP', tcb.cip_cost_acct, tcb.asset_cost_acct) to_as_account,
            DECODE (tah.asset_type, 'CIP', ' ', tcb.deprn_reserve_acct) to_re_account,
            ---CAT_FLEX_ALL_SEG_TO                TO_CATEGORY,
            tcat.segment1 to_category,
            ad.asset_number asset_number,
            AVG (DECODE (cost_adj.debit_credit_flag,  'DR', 1,  'CR', -1) * cost_adj.adjustment_amount) cost_adj,
            AVG (DECODE (res_adj.debit_credit_flag,  'DR', -1,  'CR', 1) * NVL (res_adj.adjustment_amount, 0)) res_adj,
            th.transaction_header_id th_id,
            th.transaction_date_entered,
            th.asset_id
--            apps_fa_util_api.get_deprn_period_name_by_date (th.book_type_code, th.date_effective) trans_period_name
       FROM fa_deprn_periods dp,
            fa_deprn_periods start_dp,
            fa_deprn_periods end_dp,
            fa_additions ad,
            gl_code_combinations dhcc,
            fa_categories fcat,
            fa_categories tcat,
            fa_category_books fcb,
            fa_category_books tcb,
            fa_transaction_headers th,
            fa_adjustments cost_adj,
            fa_adjustments res_adj,
            fa_asset_history fah,
            fa_asset_history tah,
            fa_distribution_history dh
      WHERE                                                                                      --start_dp.period_name = :p_period1
            --AND end_dp.period_name = :p_period2
            -- start_dp.book_type_code = UPPER (:p_book)
            end_dp.book_type_code = start_dp.book_type_code
        AND dp.book_type_code = start_dp.book_type_code
        AND dp.period_counter >= start_dp.period_counter
        AND dp.period_counter <= NVL (end_dp.period_counter, dp.period_counter)
        ---AND th.book_type_code = :p_book
        AND th.book_type_code = dp.book_type_code
        AND th.transaction_type_code = 'RECLASS'
        AND th.date_effective >= dp.period_open_date
        AND th.date_effective <= NVL (dp.period_close_date, SYSDATE)
        --AND fah.book_type_code = th.book_type_code
        AND ad.asset_id = th.asset_id
        AND fah.asset_id = th.asset_id
        AND fah.date_ineffective = th.date_effective
        AND tah.asset_id = th.asset_id
        AND tah.date_effective = th.date_effective
        AND tcat.category_id = tah.category_id
        AND fcat.category_id = fah.category_id
        ---AND tcb.book_type_code = UPPER (:p_book)
        --AND tcb.book_type_code = tah.book_type_code
        AND tcb.book_type_code = fcb.book_type_code
        AND tcb.category_id = tah.category_id
        ---AND fcb.book_type_code = UPPER (:p_book)
        AND fcb.category_id = fah.category_id
        AND cost_adj.transaction_header_id = th.transaction_header_id
        AND cost_adj.book_type_code = th.book_type_code
        ---AND cost_adj.book_type_code = :p_book
        AND cost_adj.source_type_code = 'RECLASS'
        AND cost_adj.adjustment_type IN ('COST', 'CIP COST')
        AND cost_adj.period_counter_created >= start_dp.period_counter
        AND cost_adj.period_counter_created <= NVL (end_dp.period_counter, dp.period_counter)
        AND res_adj.transaction_header_id(+) = cost_adj.transaction_header_id
        AND res_adj.asset_id(+) = cost_adj.asset_id
        AND res_adj.distribution_id(+) = cost_adj.distribution_id
        ---AND res_adj.book_type_code(+) = UPPER (:p_book)
        AND res_adj.book_type_code(+) = cost_adj.book_type_code
        AND res_adj.source_type_code(+) = 'RECLASS'
        AND res_adj.adjustment_type(+) = 'RESERVE'
        AND res_adj.period_counter_created(+) = cost_adj.period_counter_created
        AND res_adj.adjustment_amount(+) != 0
        --AND dh.book_type_code = :p_book
        AND dh.book_type_code = th.book_type_code
        AND dh.asset_id = th.asset_id
        AND dh.transaction_header_id_in = NVL (th.source_transaction_header_id, th.transaction_header_id)
        AND dh.distribution_id = cost_adj.distribution_id
        AND dhcc.code_combination_id = dh.code_combination_id
   GROUP BY                                                                                                     --ACCT_FLEX_BAL_SEG,
           th.book_type_code,
            dhcc.segment1,
            DECODE (fah.asset_type, 'CIP', fcb.cip_cost_acct, fcb.asset_cost_acct),
            DECODE (fah.asset_type, 'CIP', ' ', fcb.deprn_reserve_acct),
            --CAT_FLEX_ALL_SEG_FR,
            fcat.segment1,
            DECODE (tah.asset_type, 'CIP', tcb.cip_cost_acct, tcb.asset_cost_acct),
            DECODE (tah.asset_type, 'CIP', ' ', tcb.deprn_reserve_acct),
            --CAT_FLEX_ALL_SEG_TO,
            tcat.segment1,
            ad.asset_number,
            th.transaction_header_id,
            th.transaction_date_entered,
            th.asset_id
--            apps_fa_util_api.get_deprn_period_name_by_date (th.book_type_code, th.date_effective)
   ORDER BY 1, LPAD (DECODE (fah.asset_type, 'CIP', fcb.cip_cost_acct, fcb.asset_cost_acct), 25, '0'), ad.asset_number