Tuesday, January 9, 2018

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect