Oracle Fixed Assets Queries - Retirement, Reclass, Transfer, Addition, Depreciation

--Retirement

SELECT DISTINCT fad.asset_number,

                fal.segment1,

                fal.segment2,

                fadj.adjustment_amount,

                fnu.user_name

  FROM fa_additions fad,

       fa_distribution_history fdh,

       fa_locations fal,

       fa_deprn_periods fdp,

       fa_adjustments fadj,

       fnd_user fnu

 WHERE     fdh.asset_id = fad.asset_id

       AND fdh.book_type_code = fdp.book_type_code

       AND fdh.last_update_date > fdp.period_open_date

       AND fdp.period_close_date IS NULL

       AND fdh.retirement_id IS NOT NULL

       AND fdh.location_id = fal.location_id

       AND fdh.distribution_id = fadj.distribution_id

       AND fadj.adjustment_type = 'COST'

       AND fdh.transaction_header_id_out = fadj.transaction_header_id

       AND fdh.last_updated_by = fnu.user_id;

--Reclass

SELECT DISTINCT fad.asset_number,

                fal.segment1,

                fal.segment2,

                fadj.adjustment_amount,

                fnu.user_name

  FROM fa_additions fad,

       fa_distribution_history fdh,

       fa_locations fal,

       fa_deprn_periods fdp,

       fa_adjustments fadj,

       fnd_user fnu

 WHERE     fdh.asset_id = fad.asset_id

       AND fdh.book_type_code = fdp.book_type_code

       AND fdh.last_update_date > fdp.period_open_date

       AND fdp.period_close_date IS NULL

       AND fdh.last_updated_by = fnu.user_id

       AND fdh.location_id = fal.location_id

       AND fdh.distribution_id = fadj.distribution_id

       AND fadj.adjustment_type = 'COST'

       AND fdh.asset_id = fadj.asset_id

       AND fdh.book_type_code = fadj.book_type_code

       AND fdh.transaction_header_id_in = fadj.transaction_header_id

       AND fadj.source_type_code = 'RECLASS';

--Transfer

SELECT DISTINCT fad.asset_number,

                fal.segment1,

                fal.segment2,

                fadj.adjustment_amount,

                fnu.user_name

  FROM fa_additions fad,

       fa_distribution_history fdh,

       fa_locations fal,

       fa_deprn_periods fdp,

       fa_adjustments fadj,

       fnd_user fnu

 WHERE     fdh.asset_id = fad.asset_id

       AND fdh.book_type_code = fdp.book_type_code

       AND fdh.last_update_date > fdp.period_open_date

       AND fdp.period_close_date IS NULL

       AND fdh.last_updated_by = fnu.user_id

       AND fdh.location_id = fal.location_id

       AND fdh.distribution_id = fadj.distribution_id

       AND fadj.adjustment_type = 'COST'

       AND fdh.asset_id = fadj.asset_id

       AND fdh.book_type_code = fadj.book_type_code

       AND fdh.transaction_header_id_in = fadj.transaction_header_id

       AND fadj.source_type_code = 'TRANSFER';

--Addition (only finds original user addition as long as no other update made to the asset by another user)

SELECT DISTINCT fad.asset_number,

                fal.segment1,

                fal.segment2,

                fadj.adjustment_amount,

                fnu.user_name

  FROM fa_additions fad,

       fa_distribution_history fdh,

       fa_locations fal,

       fa_deprn_periods fdp,

       fa_adjustments fadj,

       fnd_user fnu

 WHERE     fdh.asset_id = fad.asset_id

       AND fdh.book_type_code = fdp.book_type_code

       AND fad.last_update_date > fdp.period_open_date

       AND fdp.period_close_date IS NULL

       AND fdh.location_id = fal.location_id

       AND fdh.distribution_id = fadj.distribution_id

       AND fadj.adjustment_type = 'COST'

       AND fdh.book_type_code = fadj.book_type_code

       AND fdh.transaction_header_id_in = fadj.transaction_header_id

       AND fadj.source_type_code = 'ADDITION'

       AND fdh.last_updated_by = fnu.user_id;

--Depreciation

SELECT DISTINCT fad.asset_number,

                fal.segment1,

                fal.segment2,

                fdd.distribution_id,

                fdd.deprn_amount,

                fnu.user_name

  FROM fa_additions fad,

       fa_distribution_history fdh,

       fa_locations fal,

       fa_deprn_periods fdp,

       fa_deprn_detail fdd,

       fnd_user fnu

 WHERE     fdh.asset_id = fad.asset_id

       AND fdh.book_type_code = fdp.book_type_code

       AND fdh.last_update_date > fdp.period_open_date

       AND fdp.period_counter = fdd.period_counter

       AND fdh.last_updated_by = fnu.user_id

       AND fdh.location_id = fal.location_id

       AND fdh.distribution_id = fdd.distribution_id

       AND fdh.book_type_code = fdd.book_type_code;